mrp_enter_editpassmo.pas

一个MRPII系统源代码版本

      +'                                    Where InvBillNo=@Billno            '
      +'                                     and mono='+quotedstr(mono)
      +'                                     and MoLineno='+inttostr(MoLineno)
      +'  select @InBillid=@@identity        '
      +'  Select @InBillid as Billid,identity(int,1,1) as ss,ItemCode,  '
      +'                                         BatchNo,InvBillQty     '
      +'  into #tmpBillline2                                '
      +'   From InvOutBill with (index(InvOutBillno)),InvOutBillLine with (index(Relation_10096_FK))                 '
      +'   where InvOutBill.InvBillid=InvOutBillline.InvBillid  '
      +'      and InvOutBill.InvBillNo=@Billno         '
      +'      and InvOutBill.mono='+quotedstr(mono)
      +'      and InvOutBill.MoLineno='+inttostr(MoLineno)
      +'        Insert InvInBillLine(InvBillid,InvBillLineNo,ItemCode, '
      +'                BatchNo,InvBillQty)       '
      +'  select * from #tmpBillline2             '
      +'   drop table #tmpBillline2               '
          //产生货位移入单表体
      +'        Update CurrentInv Set OnHandInv=OnhandInv+InvInBillLine.InvBillQty '
      +'         from Item,InvInBillline,InvInBill                               '
      +'                Where CurrentInv.ItemCode=InvInBillline.ItemCode          '
      +'                  and InvInBillline.InvBillid=@InBillid                  '
      +'                  And CurrentInv.WHCode=InvInBill.whCode                  '
      +'                  and  InvInBill.InvBillid=@InBillid                      '
      +'                  And CurrentInv.WhPositionCode=InvInBill.WhPositionCode  '
          //加CurrentInv中相应拉式货位OnHand的值
      +'  if  dateDiff(mm,@CloseMonth,@toMonth)=1  '
      +'        Update CurrentperiodInv Set OnHandInv=OnhandInv+InvInBillLine.InvBillQty '
      +'         from Item,InvInBillline,InvInBill                               '
      +'                Where CurrentperiodInv.ItemCode=InvInBillline.ItemCode          '
      +'                  and InvInBillline.InvBillid=@InBillid                  '
      +'                  And CurrentperiodInv.WHCode=InvInBill.whCode                  '
      +'                  and InvInBill.InvBillid=@InBillid                      '
      +'                  And CurrentperiodInv.WhPositionCode=InvInBill.WhPositionCode  '
          //加CurrentperiodInv中相应拉式货位OnHand的值

    //  +'   if not exists(select CurrentInv.* from CurrentInv,InvOutBillline       '
    //  +'                   where whCode=@strwhCode                               '
    //  +'                     and InvOutBillline.InvBillid=@Billid                '
   //   +'                   and CurrentInv.ItemCode=InvOutBillline.ItemCode       '
  //    +'                  and WhPositionCode=@StrBackFluchWhPositionCode )       '
      +'       Insert CurrentInv(WHCode,ItemCode,OnHandInv,WhPositionCode)       '
      +'               Select InvOutBill.whCode,InvOutBillLine.ItemCode,InvOutBillLine.InvBillQty,'
      +'                      InvOutBill.OWhPositionCode As WhPositionCode       '
      +'                    From InvOutBillLine                                  '
      +'                          Join InvOutBill On InvOutBillLine.InvBillid=InvOutBill.InvBillid '
      +'                      and InvOutBill.InvBillid=@Billid                   '
      +'               where InvOutBill.whCode+InvOutBill.oWhPositionCode+InvOutBillline.ItemCode  '
      +'                       not in (select whCode+WhPositionCode+ItemCode from CurrentInv)   '

         +'  if  dateDiff(mm,@CloseMonth,@toMonth)=1  '
    //  +'   if not exists(select CurrentperiodInv.* from CurrentperiodInv,InvOutBillline       '
    //  +'                   where whCode=@strwhCode                               '
    //  +'                     and InvOutBillline.InvBillid=@Billid                '
    //  +'                   and CurrentperiodInv.ItemCode=InvOutBillline.ItemCode       '
    //  +'                  and WhPositionCode=@StrBackFluchWhPositionCode )       '
      +'       Insert CurrentperiodInv(WHCode,ItemCode,OnHandInv,WhPositionCode)       '
      +'               Select InvOutBill.whCode,InvOutBillLine.ItemCode,InvOutBillLine.InvBillQty,'
      +'                      InvOutBill.OWhPositionCode As WhPositionCode       '
      +'                    From InvOutBillLine                                  '
      +'                          Join InvOutBill On InvOutBillLine.InvBillid=InvOutBill.InvBillid '
      +'                      and InvOutBill.InvBillid=@Billid                   '
      +'               where InvOutBill.whCode+InvOutBill.oWhPositionCode+InvOutBillline.ItemCode  '
      +'                       not in (select whCode+WhPositionCode+ItemCode from CurrentperiodInv)   '
      +'             Update #TmPmNItemList Set MOCtrlQty=MOCtrlQty-InvOutBillLine.InvBillQty '
      +'          from #tmPmnItemList,InvOutBillline                            '
      +'                Where #tmPmnItemList.ItemCode=InvOutBillline.ItemCode  '
      +'                  and InvOutBillline.InvBillid=@Billid                 '
      +'      drop table #tmpCurrentInv,#tmPmaxrecord  '
      +' end      ';
  Executesql(AdoQry,sqltext,1);


//以下为批次处理：
  sqltext:='Select mnItemList.ItemCode,BatchCtrl,Sum(MoCtrlQty) As MOCtrlQty  '
           +'  into #tmPmnItemList1 '
           +'              From MNItemList,Item     '
           +'              Where MoNO='+quotedstr(mono)
           +'                and MoLineno='+inttostr(MoLineno)
           +'                and mnItemList.ItemCode=Item.ItemCode '
           +'                and Item.BatchCtrl=1     '
           +'              Group By mnItemList.ItemCode,Item.BatchCtrl '
         +' while exists(select * from #tmPmnItemList1 where moCtrlqty>0) '
            +' begin                    '
            +'    Select Batch.ItemCode,BatchCurrentInv.Batchno,BatchCurrentInv.WHCode,BatchCurrentInv.WhPositionCode,BatchCurrentInv.InvQty as OnHandInv    '
            +'      into #tmpbatCurrentInv'
             +'      From BatchCurrentInv                                 '
             +'       join Batchline on BatchCurrentInv.Batchno=Batchline.Batchno '
             +'       join Batch on Batchline.Batchid=Batch.Batchid '
             +'    Where WHCode In (  Select WHCode                  '
             +'                    From Warehouse                    '
             +'                     Where WHCode IN (Select Distinct WHCode From WhPosition Where BackFlushWHP=1 And WhPositionType=0 And DeptCode='+quotedstr(getDeptCode(mono))+'))'
             +'      And Batch.ItemCode IN (Select ItemCode From #TmPmNItemList1 Where MOCtrlQty>0)                                      '
             +'      And whCode+WhPositionCode Not In (Select Distinct whCode+WhPositionCode From WhPosition Where BackFlushWHP=1) '
             +'       And InvQty>0 '
         +'  Select WHCode,WhPositionCode,Count(*) As TotalRecord   '
         +'     Into  #TmPmaxRecord                '
         +'     From #TMPbatCurrentInv                '
         +'    Group By WHCode,WhPositionCode      '
  //选出每个可用货位所拥有的要建立货位移动清单的记录条数
         +'   DeclAre @StrWHCode varchAr(14)        '
          //要建立货位移动的清单的仓库
         +'   DeclAre @StrWhPositionCode varchAr(14) '
          //要建立货位移动的清单的仓库的货位
         +'   DeclAre @StrBackFluchWhPositionCode varchAr(14) '
          //要建立货位移动的清单的仓库的拉式领料货位
         +'   DeclAre @Billno varchAr(15) '//货位移动单单据流水号
         +'   declAre @Batchno varchAr(30) '//批号
         +'   declAre @tmpInvstatus int   '
         +'   declAre @tmp  varchAr(20)   '
         +'   declAre @Billid int         '
         +'   declAre @InBillno varchAr(15) '
         +'   declAre @InBillid int      '
         +'   declAre @Billlineno int    '
         +'   Select Top 1 @StrWHCode=WHCode,@StrWhPositionCode=WhPositionCode,@Batchno=Batchno '
         +'     From #TmPmaxRecord        '
         +'     Where TotalRecord=(Select Max(TotalRecord)  '
         +'                        From ( Select *          '
         +'                                      From #TmPmaxRecord ) TmPmaxRecord1 )  '
         +'     Select @StrBackFluchWhPositionCode=WhPositionCode  '
         +'       From WhPosition                                  '
         +'       Where BackFlushWHP=1 And WhPositionType=0        '
         +'         And WHCode=@StrWHCode  And DeptCode='+quotedstr(getDeptCode(mono))
         +'   select @strwhCode              '
         +'   select @strWhPositionCode      '
         +'   select @strBackfluchWhPositionCode       '
           //列出拥有的要建立货位移动清单的最大记录条数的货位(只有一个)
         +'    select @tmp=@strwhCode+''c''+substring(dateName(yy,getdate()),3,2)+dateName(mm,getdate()) '
         +'    select @tmpInvstatus=Invstatus from Invstatus          '
         +'     where whCode+InvstatusName=@strwhCode+''c''+substring(dateName(yy,getdate()),3,2)+dateName(mm,getdate())  '
         +'            select @tmpInvstatus  '
         +'       if  @tmpInvstatus is null   '
         +'            begin                  '
         +'           insert into Invstatus(InvstatusName,Invstatus,whCode) '
         +'             Values(substring(@tmp,  '
         +'               len(@tmp)-4,5),       '
         +'               0,@strwhCode)         '
         +'            select @Billno=''00001''   '
         +'            end             '
         +'          else select @Billno=substring(convert(varchAr,100000+@tmpInvstatus+1),2,5) '
         +'       update Invstatus       '
         +'      set Invstatus=@Billno   '
         +'      where whCode+InvstatusName=@strwhCode+''c''+substring(dateName(yy,getdate()),3,2)+dateName(mm,getdate()) '
         +'    select @Billno=substring(@tmp,len(@tmp)-4,5)+@Billno  '
           //产生货位移动单单据流水号@Billno
         +'   Insert InvOutBill(InvBillNo,WHCode,InvBillDate,InvBillMonth, '
         +'         BillTypeCode,EmployeeCode,Mono,MoLineno,         '
         +'         WhPositionCode,OWhPositionCode)Values(@Billno,@strwhCode,dateName(yy,getdate())+''.''+dateName(mm,getdate())+''.''+dateName(dd,getdate()),dateName(yy,getdate())+''.''+dateName(mm,getdate()),''1101'', '
         +quotedstr(userCode)+','+quotedstr(mono)+','+inttostr(MoLineno)+','+'@StrWhPositionCode,@StrBackFluchWhPositionCode)  '
          //产生货位移出单表头
         +'  select @Billid=@@identity   '
         +'  select @Billid              '
         +'   Select @Billid as Billid,identity(int,1,1) as ss,#TmpbatCurrentInv.ItemCode,#tmpbatCurrentInv.Batchno,'
         +'       Case        '
         +'          When OnHandInv>=#TmPmNItemList1.MoCtrlQty then #TmPmNItemList1.MoCtrlQty'
         +'         Else  OnHandInv     '
         +'        end As InvBillQty    '
         +'    into #tmpBillline        '
         +'     From #TmpbatCurrentInv      '
         +'      Join #TmPmNItemList1 On  #TmpbatCurrentInv.ItemCode=#TmPmNItemList1.ItemCode   '
         +'     Where WHCode+WhPositionCode=@strwhCode+@strWhPositionCode                     '
         +'    Insert InvOutBillLine(InvBillid,InvBilllineno,ItemCode,     '
         +'             BatchNo,InvBillQty)          '
         +'      select * from #tmpBillline          '
         +'       drop table #tmpBillline            '
         +'     select * from InvOutBillline where InvBillid=@Billid   '
          //产生货位移出单表体
         +'     Update Item Set BackFlushAllocInv=BackFlushAllocInv+InvOutBillLine.InvBillQty   '
         +'       from Item,InvOutBillline    '
         +'       Where Item.ItemCode=InvOutBillline.ItemCode  '
         +'          and InvOutBillline.InvBillid=@Billid      '
           //增加Item中BackFlushAllocInv的值
         +'     Update CurrentInv Set OnHandInv=OnhandInv-InvOutBillLine.InvBillQty  '
         +'      from Item,InvOutBillline,InvOutBill                                 '
         +'             Where CurrentInv.ItemCode=InvOutBillline.ItemCode            '
         +'               and InvOutBillline.InvBillid=@Billid                       '
         +'               And CurrentInv.WHCode=InvOutBill.whCode                    '
         +'               and  InvOutBill.InvBillid=@Billid                          '
         +'               And CurrentInv.WhPositionCode=InvOutBill.WhPositionCode    '
           //减CurrentInv中相应货位OnHand的值
         +'  declAre @CloseMonth varchAr(10),     '
         +'          @toMonth    varchAr(10)      '
         +'  select  @toMonth=dateName(yy,getdate())+''.''+dateName(mm,getdate())+''.01'' '
         +'  select @toMonth         '
         +'  select @CloseMonth=substring(convert(varchAr,Invstatus),1,7)+''.01'' from Invstatus where InvstatusName=''clsperiod'' '
         +'  select @CloseMonth     '
         +'  if  dateDiff(mm,@CloseMonth,@toMonth)=1  '
         +'     Update CurrentperiodInv Set OnHandInv=OnhandInv-InvOutBillLine.InvBillQty  '
         +'      from Item,InvOutBillline,InvOutBill                                 '
         +'             Where CurrentperiodInv.ItemCode=InvOutBillline.ItemCode            '
         +'               and InvOutBillline.InvBillid=@Billid                       '
         +'               And CurrentperiodInv.WHCode=InvOutBill.whCode                    '
         +'               and  InvOutBill.InvBillid=@Billid                          '
         +'               And CurrentperiodInv.WhPositionCode=InvOutBill.WhPositionCode    '

         +'     Update BatchCurrentInv Set InvQty=InvQty-InvOutBillLine.InvBillQty  '
         +'      from Item,InvOutBillline,InvOutBill,Batch,Batchline,BatchCurrentInv                                 '
         +'             Where Batch.ItemCode=InvOutBillline.ItemCode            '
         +'               and BatchCurrentInv.Batchno=Batchline.Batchno  '
         +'               and BatchCurrentInv.Batchno=InvOutBillline.Batchno '
         +'               and Batch.Batchid=Batchline.Batchid  '
         +'               and InvOutBillline.InvBillid=@Billid                       '
         +'               And BatchCurrentInv.WHCode=InvOutBill.whCode                    '
         +'               and  InvOutBill.InvBillid=@Billid                          '
         +'               And BatchCurrentInv.WhPositionCode=InvOutBill.WhPositionCode    '
           //减BatchCurrentInv中相应货位OnHand的值
         +'    select @tmp=@strwhCode+''c''+substring(dateName(yy,getdate()),3,2)+dateName(mm,getdate())  '
         +'    select @tmpInvstatus=Invstatus from Invstatus   '
         +'     where whCode+InvstatusName=@strwhCode+''c''+substring(dateName(yy,getdate()),3,2)+dateName(mm,getdate()) '
         +'       if  @tmpInvstatus is null     '
         +'            begin                    '
         +'           insert into Invstatus(InvstatusName,Invstatus,whCode)  '
         +'             Values(substring(@tmp,          '
         +'               len(@tmp)-4,5),               '
         +'               0,@strwhCode)                 '
         +'            select @InBillno=''00001''         '
         +'            end                               '
         +'          else select @InBillno=substring(convert(varchAr,100000+@tmpInvstatus+1),2,5) '
         +'       update Invstatus           '
         +'      set Invstatus=@InBillno     '
         +'      where whCode+InvstatusName=@strwhCode+''c''+substring(dateName(yy,getdate()),3,2)+dateName(mm,getdate()) '
         +'      select @InBillno=substring(@tmp,len(@tmp)-4,5)+@InBillno     '
         +'   select @InBillno                                                '
         +'   Insert InvInBill(InvBillNo,WHCode,InvBillDate,InvBillMonth,     '
         +'        BillTypeCode,EmployeeCode,Mono,MoLineno,WhPositionCode,    '
         +'         OWhPositionCode)                                          '
      +' Select @InBillno,WHCode,InvBillDate,InvBillMonth,                    '
      +'                                    BillTypeCode,EmployeeCode,Mono,MoLineno, '
      +'                                    oWhPositionCode,                    '
      +'                                   WhPositionCode                     '
      +'                                    From InvOutBill   with(index(InvOutBillno))                 '
      +'                                    Where InvBillNo=@Billno            '
      +'                                     and mono='+quotedstr(mono)
      +'                                     and MoLineno='+inttostr(MoLineno)
      +'  select @InBillid=@@identity        '
      +'  Select @InBillid as Billid,identity(int,1,1) as ss,ItemCode,  '
      +'                                         BatchNo,InvBillQty     '
      +'  into #tmpBillline2                                '
      +'   From InvOutBill with (index(InvOutBillno)),InvOutBillLine with (index(Relation_10096_FK))                 '
      +'   where InvOutBill.InvBillid=InvOutBillline.InvBillid  '
      +'      and InvOutBill.InvBillNo=@Billno         '
      +'      and InvOutBill.mono='+quotedstr(mono)
      +'      and InvOutBill.MoLineno='+inttostr(MoLineno)
      +'        Insert InvInBillLine(InvBillid,InvBillLineNo,ItemCode, '
      +'                BatchNo,InvBillQty)       '
      +'  select * from #tmpBillline2             '
      +'   drop table #tmpBillline2               '
          //产生货位移入单表体
      +'        Update CurrentInv Set OnHandInv=OnhandInv+InvInBillLine.InvBillQty '
      +'         from Item,InvInBillline,InvInBill                               '
      +'                Where CurrentInv.ItemCode=InvInBillline.ItemCode          '
      +'                  and InvInBillline.InvBillid=@InBillid                  '
      +'                  And CurrentInv.WHCode=InvInBill.whCode                  '
      +'                  and  InvInBill.InvBillid=@InBillid                      '
      +'                  And CurrentInv.WhPositionCode=InvInBill.WhPositionCode  '
          //加CurrentInv中相应拉式货位OnHand的值
         +'  if  dateDif