pm_qry_normalsspoinfo.pas

一个MRPII系统源代码版本

          +'  where ssSysInfoflag=2     '
          +'  and Orderlinestatus<>7    '
          +'  and exists(select pono from poline   where Orderno=poline.pono) '//with(index(RELATION_1999_FK))
          +'  and ss.Orderno=poline.pono          '
          +'  and ss.Orderlineno=poline.polineno  ';
  ExecuteCmd(AdOCmd_Mrp,SQlText);
  SQlText:= ' Select poline.poqty,poline.poinqty,'
                +' SSId,#Ss1.ItemCode,SSSysInfoFlag,OrderNo,OrderLineNo,'
                +' SSDate,#Ss1.SSQty,convert(integer,PmType) as PmType,SScheck, convert(float,GrossRequireQty) as GrossRequireQty, convert(float,PlanInvQty) as PlanInvQty,'
                +' OrderLineStatus, convert(varchAr,DeptVendorCode) as DeptVendorCode,Vendor.VendorName,duedate,ReMainQty,'
                +' ssReMainQty =case   when (ssReMainQty=0) then null '
                +' else'
                +'   ssReMainQty '
                +' end ,'
                +' Item.MnldTime,Item.ItemName,Item.UomCode,Uom.UomName,Item.PmCode,Item.Qclt,'
                +' Case When ((Select Count(distinct PcLine.PcNo) As ICount From PcLine '
                +' Where #Ss1.ItemCode=PcLine.ItemCode '
                +' And #Ss1.SsDate>=PcStArtDate And #Ss1.SsDate<=PcendDate '
                +' And ((#Ss1.SsQty>=PcStArtQty And #Ss1.SsQty<PcendQty) Or '
                +' (PcStArtQty=0 And PcendQty=0))'
                +' And PcLine.PcLineStatus=1)>=1) And (#Ss1.SsSysInfoFlag=1) And (#Ss1.DeptVendorCode is Not Null) Then '
                +' (Select Top 1 PcLine.PcNo From PcLine,Pc '
                +' Where PcLine.PcNo=Pc.PcNo '
                +' And #Ss1.ItemCode=PcLine.ItemCode '
                +' And #Ss1.SsDate>=PcStArtDate And #Ss1.SsDate<=PcendDate '
                +' And ((#Ss1.SsQty>=PcStArtQty And #Ss1.SsQty<PcendQty) '
                +' Or (PcStArtQty=0 And PcendQty=0))'
                +' And (PcLine.PcLineStatus=1) And Pc.VendorCode=#Ss1.DeptVendorCode) '

                +' When ((Select Count(distinct PcLine.PcNo) As ICount From PcLine '
                +' Where #Ss1.ItemCode=PcLine.ItemCode '
                +' And #Ss1.SsDate>=PcStArtDate And #Ss1.SsDate<=PcendDate '
                +' And ((#Ss1.SsQty>=PcStArtQty And #Ss1.SsQty<PcendQty) Or '
                +' (PcStArtQty=0 And PcendQty=0))'
                +' And PcLine.PcLineStatus=1)=1) And (#Ss1.SsSysInfoFlag=1) And (#Ss1.DeptVendorCode is Null) Then '
                +' (Select PcLine.PcNo From PcLine '
                +' Where #Ss1.ItemCode=PcLine.ItemCode '
                +' And #Ss1.SsDate>=PcStArtDate And #Ss1.SsDate<=PcendDate '
                +' And ((#Ss1.SsQty>=PcStArtQty And #Ss1.SsQty<PcendQty) '
                +' Or (PcStArtQty=0 And PcendQty=0))'
                +' And (PcLine.PcLineStatus=1)) '

                +' When  ((Select Count(PcLine.PcNo) As ICount From PcLine '
                +' Where #Ss1.ItemCode=PcLine.ItemCode '
                +' And #Ss1.SsDate >=PcStArtDate And #Ss1.SsDate< PcendDate '
                +' And ((#Ss1.SsQty>=PcStArtQty And #Ss1.SsQty< PcendQty ) Or '
                +' (PcStArtQty=0 And PcendQty=0))'
                +' And PcLine.PcLineStatus=1)>1) '
                +' And (#Ss1.DeptVendorCode is Null) '
                +' And (#Ss1.SsSysInfoFlag=1) '
                +' and (#ss1.PmType<>9)'
                +' Then '
                +''''+'待分配'+''''
                +' When '
                +' #Ss1.SsSysInfoFlag=1 and (#ss1.PmType=9) Then'
                +''''+'替代件待分配'+''''
                +' Else '
                +' Null '
                +' end PcNo,'
                +' ConVert(varchAr(20),SysSsInfo.SSCode)+'''+' '+'''+SysSsInfo.SsName As SsName, '
                +' Item.ItemCode+'''+' '+'''+Item.ItemName As ItemFlag, '
                +' Item.EmployeeCode,Employee.EmployeeName, '
                +' Employee.EmployeeCode+'''+' '+'''+Employee.EmployeeName As EmployeeFlag '
                +' into #ss From #SS1 '
                +' Join Item On #Ss1.ItemCode=Item.ItemCode '
                +' And (PmType=1 Or PmType=2 Or PmType=9) '
                +' Join Uom On Item.UomCode=Uom.UomCode '
                +' left Join SysSsInfo On #Ss1.SSSysInfoFlag=SysSsInfo.SSCode '
                +' left Join Employee On Item.EmployeeCode=Employee.EmployeeCode '
                +' Left Join Vendor on #ss1.DeptVendorCode=Vendor.VendorCode'
                +' left join poline with(index(pk_Poline)) on (#ss1.Orderno=Poline.pono) and (#ss1.Orderlineno=poline.polineno)'
                +' where ((#Ss1.SsSysInfoFlag=1 '
                      +' And Item.LongPurchLT=0 And #Ss1.DueDate-(select MrpParamValueN from MrpParam '
                      +' where MrpParamCode='''+'SSForwArdDay'+''')<='''+DateTimeToStr(Date())+''')'
                      +' Or (#Ss1.SsSysInfoFlag=1 And Item.LongPurchLT=1)Or (#Ss1.SsSysInfoFlag<>1 )) or (#Ss1.SsSysInfoFlag=1 and (#ss1.PmType=9))';
   ExecuteCmd(AdOCmd_Mrp,SQlText);
{  SqLText:='select top 0 ss.ItemCode,ssSysInfoflag, '
          +'       Orderno,Orderlineno,ssdate, '
          +'        PmType,             '
          +'        sScheck,            '
          +'        OrderLineStatus,    '
          +'        DeptVendorCode,     '
          +'        duedate,            '
          +'        ssqty,  '
          +'        GrossRequireQty,    '
          +'        PlanInvQty,         '
          +'        ReMainQty,          '
          +' ssreMainqty=ReMainQty into #ss from ss '
          +' alter table #ss Add ssid integer ';
          ExecuteSql(AdoQry_tmp,SQlText,1);

  SqlText:=' insert into #ss select ss.ItemCode,ssSysInfoflag, '
          +'       Orderno,Orderlineno,ssdate, '
          +'        PmType,             '
          +'        sScheck,            '
          +'        OrderLineStatus,    '
          +'        DeptVendorCode=(select  VendorCode from pc with(index(Pk_Pc))  where pcno= Case When ((Select Count(distinct PcLine.PcNo) As ICount From PcLine '
                +' Where Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate>=PcStArtDate And Ss.SsDate<=PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty<PcendQty) Or '
                +' (PcStArtQty=0 And PcendQty=0))'
                +' And PcLine.PcLineStatus=1)>=1) And (Ss.SsSysInfoFlag=1) And (Ss.DeptVendorCode is Not Null) Then '
                +' (Select Top 1 PcLine.PcNo From PcLine with(index(Pk_PcLine)),Pc with(index(Pk_Pc)) '
                +' Where PcLine.PcNo=Pc.PcNo '
                +' And Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate>=PcStArtDate And Ss.SsDate<=PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty<PcendQty) '
                +' Or (PcStArtQty=0 And PcendQty=0))'
                +' And (PcLine.PcLineStatus=1) And Pc.VendorCode=Ss.DeptVendorCode) '

                +' When ((Select Count(distinct PcLine.PcNo) As ICount From PcLine '
                +' Where Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate>=PcStArtDate And Ss.SsDate<=PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty<PcendQty) Or '
                +' (PcStArtQty=0 And PcendQty=0))'
                +' And PcLine.PcLineStatus=1)=1) And (Ss.SsSysInfoFlag=1) And (Ss.DeptVendorCode is Null) Then '
                +' (Select PcLine.PcNo From PcLine '
                +' Where Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate>=PcStArtDate And Ss.SsDate<=PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty<PcendQty) '
                +' Or (PcStArtQty=0 And PcendQty=0))'
                +' And (PcLine.PcLineStatus=1)) '

                +' When  ((Select Count(PcLine.PcNo) As ICount From PcLine '
                +' Where Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate >=PcStArtDate And Ss.SsDate< PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty< PcendQty ) Or '
                +' (PcStArtQty=0 And PcendQty=0))'
                +' And PcLine.PcLineStatus=1)>1) '
                +' And (Ss.DeptVendorCode is Null) '
                +' And (Ss.SsSysInfoFlag=1) '
                +' Then '
                +''''+'待分配'+''''
                +' Else '
                +' Null '
                +' end '
            +'),     '
          +'        duedate,            '
          +'        ssqty,              '
          +'        GrossRequireQty,    '
          +'        PlanInvQty,         '
          +'        ReMainQty,          '
          +'   ssreMainqty=reMainqty,ssid from ss with(index(ss_idx_PmType ))  '
          +' where ssSysInfoflag=1 '
          +' and ((PmType=1) or (PmType=2) or (PmType=3)) ';
          ExecuteSql(AdoQry_tmp,SQlText,1);
          //+' union '
         SqlText:='insert into #ss select ss.ItemCode,ssSysInfoflag, '
          +'       Orderno,Orderlineno,ssdate, '
          +'        PmType,             '
          +'        sScheck,            '
          +'        OrderLineStatus,    '
          +'        DeptVendorCode,'
          {+' (select  VendorCode from pc with(index(Pk_Pc))  where pcno= Case When ((Select Count(distinct PcLine.PcNo) As ICount From PcLine '
                +' Where Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate>=PcStArtDate And Ss.SsDate<=PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty<PcendQty) Or '
                +' (PcStArtQty=0 And PcendQty=0))'
                +' And PcLine.PcLineStatus=1)>=1) And (Ss.SsSysInfoFlag=1) And (Ss.DeptVendorCode is Not Null) Then '
                +' (Select Top 1 PcLine.PcNo From PcLine with(index(Pk_PcLine)),Pc with(index(Pk_Pc)) '
                +' Where PcLine.PcNo=Pc.PcNo '
                +' And Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate>=PcStArtDate And Ss.SsDate<=PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty<PcendQty) '
                +' Or (PcStArtQty=0 And PcendQty=0))'
                +' And (PcLine.PcLineStatus=1) And Pc.VendorCode=Ss.DeptVendorCode) '

                +' When ((Select Count(distinct PcLine.PcNo) As ICount From PcLine '
                +' Where Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate>=PcStArtDate And Ss.SsDate<=PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty<PcendQty) Or '
                +' (PcStArtQty=0 And PcendQty=0))'
                +' And PcLine.PcLineStatus=1)=1) And (Ss.SsSysInfoFlag=1) And (Ss.DeptVendorCode is Null) Then '
                +' (Select PcLine.PcNo From PcLine '
                +' Where Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate>=PcStArtDate And Ss.SsDate<=PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty<PcendQty) '
                +' Or (PcStArtQty=0 And PcendQty=0))'
                +' And (PcLine.PcLineStatus=1)) '

                +' When  ((Select Count(PcLine.PcNo) As ICount From PcLine '
                +' Where Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate >=PcStArtDate And Ss.SsDate< PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty< PcendQty ) Or '
                +' (PcStArtQty=0 And PcendQty=0))'
                +' And PcLine.PcLineStatus=1)>1) '
                +' And (Ss.DeptVendorCode is Null) '
                +' And (Ss.SsSysInfoFlag=1) '
                +' Then '
                +''''+'替代件待分配'+''''
                +' Else '
                +' Null '
                +' end '
            +'),     '                   }
 {         +'        duedate,            '
          +'        ssqty,              '
          +'        GrossRequireQty,    '
          +'        PlanInvQty,         '
          +'        ReMainQty,          '
          +'   ssreMainqty=reMainqty,ssid   from ss with(index(ss_idx_PmType ))   '
          +' where ssSysInfoflag=1 '
          +' and (PmType=9) '
          +' And ReMainQty>0 ';
          ExecuteSql(AdoQry_tmp,SQlText,1);
        //  +' union '

          SQlText:='insert into #ss select  ss.ItemCode,ssSysInfoflag, '
          +'       Orderno,Orderlineno,ssdate, '
          +'        PmType,             '
          +'        sScheck,            '
          +'        OrderLineStatus,    '
          +'        DeptVendorCode,     '
          +'        duedate,            '
          +'        ssqty,  '
          +'        GrossRequireQty,    '
          +'        PlanInvQty,         '
          +'        ReMainQty,          '
          +' ssreMainqty=0,ssid from ss with(index(ss_idx_PmType )) '
          +' where ssSysInfoflag=3 '
          +' and Orderlinestatus<>7 '
          +' and exists(select pono from  poline  where Orderno=poline.pono) ';//with(index(RELATION_1999_FK))
          ExecuteSql(AdoQry_tmp,SQlText,1);
          //+' union '
          SqlText:='insert Into #ss select  ss.ItemCode,ssSysInfoflag, '
          +'       Orderno,Orderlineno,ssdate, '
          +'        PmType,             '
          +'        sScheck,            '
          +'        OrderLineStatus,    '
          +'        DeptVendorCode,     '
          +'        duedate,            '
          +'        ssqty,  '
          +'        GrossRequireQty,    '
          +'        PlanInvQty,         '
          +'        ReMainQty,          '
          +' ssreMainqty=0,ssid from ss with(index(ss_idx_PmType )) '
          +' where ssSysInfoflag=4 '
          +' and exists(select pono from  poline  where Orderno=poline.pono) ';
          ExecuteSql(AdoQry_tmp,SQlText,1);

          //+' union '
          SqlText:='insert #ss select ss.ItemCode,ssSysInfoflag, '
          +'       Orderno,Orderlineno,ssdate, '
          +'        PmType,             '
          +'        sScheck,            '
          +'        OrderLineStatus,    '
          +'        DeptVendorCode,     '
          +'        duedate,            '
          +'        ssqty=case  when ss.reMainqty<poline.poqty then poline.poqty-ss.reMainqty '
          //+'             else ss.ssqty  '
          +'             else 0  '
          + '             end,          '
          +'        GrossRequireQty,    '
          +'        PlanInvQty,         '
          +'        ReMainQty,          '
          +'        ssreMainqty=0,ssid       '
          +'  from ss with(index(ss_idx_PmType )),poline with(index(poline_idx_Pono))            '
          +'  where ssSysInfoflag=2     '
          +'  and Orderlinestatus<>7    '
          +'  and exists(select pono from poline   where Orderno=poline.pono) '//with(index(RELATION_1999_FK))
          +'  and ss.Orderno=poline.pono          '
          +'  and ss.Orderlineno=poline.polineno  ';
  AdoQry_Tmp.Close;
  AdoQry_Tmp.SQL.clear;
  AdoQry_Tmp.SQL.Text := SqlText;
  AdoQry_Tmp.ExecSQL;}

  Initselect;


{  SelectFromSql:=' Select SS.*,Item.MnldTime,Item.ItemName,Item.UomCode,Uom.UomName,Item.PmCode,'
                +' Case When ((Select Count(distinct PcLine.PcNo) As ICount From PcLine '
                +' Where Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate>=PcStArtDate And Ss.SsDate<PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty<PcendQty) Or '
                +' (PcStArtQty=0 And PcendQty=0))'
                +' And PcLine.PcLineStatus=1)>=1) And (Ss.SsSysInfoFlag=1) And (Ss.DeptVendorCode is Not Null) Then '
                +' (Select Top 1 PcLine.PcNo From PcLine,Pc '
                +' Where PcLine.PcNo=Pc.PcNo '
                +' And Ss.ItemCode=PcLine.ItemCode '
                +' And Ss.SsDate>=PcStArtDate And Ss.SsDate<PcendDate '
                +' And ((Ss.SsQty>=PcStArtQty And Ss.SsQty<PcendQty) '
                +' Or (PcStArtQty=0 And PcendQty=0))'
                +' And (PcLine.PcLineStatus=1) And Pc.VendorCode=Ss.DeptVendorCode) '