📄 pm_enter_sspoinfo3.pas
字号:
+' union '
+' select ssid,ss.ItemCode,ssSysInfoflag, '
+' Orderno,Orderlineno,ssdate, '
+' PmType, '
+' sScheck, '
+' OrderLineStatus, '
+' DeptVendorCode, '
+' duedate, '
+' ssqty, '
+' GrossRequireQty, '
+' PlanInvQty, '
+' ReMainQty, '
+' ssreMainqty=0 from ss with(index(ss_idx_PmType )) '
+' where ssSysInfoflag=4 '
+' and exists(select pono from poline where Orderno=poline.pono) '
+' union '
+' select ssid,ss.ItemCode,ssSysInfoflag, '
+' Orderno,Orderlineno,ssdate, '
+' PmType, '
+' sScheck, '
+' OrderLineStatus, '
+' DeptVendorCode, '
+' duedate, '
+' ssqty=case when ss.reMainqty<poline.poqty then poline.poqty-ss.reMainqty '
+' else 0 '
+ ' end, '
+' GrossRequireQty, '
+' PlanInvQty, '
+' ReMainQty, '
+' ssreMainqty=0 '
+' from ss,poline '
+' where ssSysInfoflag=2 '
+' and Orderlinestatus<>7 '
+' and exists(select pono from poline where Orderno=poline.pono) '
+' 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;}
SqLText:='select top 0 ss.ItemCode,ssSysInfoflag, '
+' Orderno,Orderlineno,ssdate, '
+' PmType, '
+' sScheck, '
+' OrderLineStatus, '
+' DeptVendorCode, '
+' duedate, '
+' ssqty, '
+' GrossRequireQty, '
+' PlanInvQty, '
+' ReMainQty, '
+' ssreMainqty=ReMainQty into #ss1 from ss '
+' alter table #ss1 Add ssid integer ';
ExecuteCmd(AdOCmd_Mrp,SQlText);
SqlText:=' insert into #ss1 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)) ';
ExecuteCmd(AdOCmd_Mrp,SQlText);
//+' union '
SqlText:='insert into #ss1 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 ';
ExecuteCmd(AdOCmd_Mrp,SQlText);
// +' union '
SQlText:='insert into #ss1 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))
ExecuteCmd(AdOCmd_Mrp,SQlText);
//+' union '
SqlText:='insert Into #ss1 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) ';
ExecuteCmd(AdOCmd_Mrp,SQlText);
//+' union '
SqlText:='insert #ss1 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 ';
ExecuteCmd(AdOCmd_Mrp,SQlText);
SqlText:= ' Select '
+' SSId,#Ss1.ItemCode,SSSysInfoFlag,OrderNo,OrderLineNo,'
+' SSDate,SSQty,convert(integer,PmType) as PmType,SScheck, convert(float,GrossRequireQty) as GrossRequireQty, convert(float,PlanInvQty) as PlanInvQty,'
+' OrderLineStatus, convert(varchAr,DeptVendorCode) as DeptVendorCode,duedate,ReMainQty,'
+' ssReMainQty =case when (ssReMainQty=0) then null '
+' else'
+' ssReMainQty '
+' end ,'
+'Vendor.VendorCode,Vendor.VendorName,Item.MnldTime,Item.ItemName,Item.UomCode,Uom.UomName,Item.PmCode,Item.Qclt, '
+' Vendor.VendorCode+'''+' '+'''+Vendor.VendorName As VendorFlag,'
+' 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) Then '
+' (Select Top 1 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) '
+' And Item.EmployeeCode='''+UserCode+''''
+' Join Uom On Item.UomCode=Uom.UomCode '
+' Join SysSsInfo On #Ss1.SSSysInfoFlag=SysSsInfo.SSCode '
+' Left Join Employee On Item.EmployeeCode=Employee.EmployeeCode '
+' Left Join Vendor On #Ss1.DeptVendorCode=Vendor.VendorCode '
+' 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)) '
+' And (((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.SsQty<>0)) or (#Ss1.SsSysInfoFlag=1 and (#ss1.PmType=9))';
ExecuteCmd(AdOCmd_Mrp,SQlText);
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -