📄 inv_monthsum2.pas
字号:
' set InvInQty=a.Qty '+
' from '+
' (select sum(bl.InvBillQty) as qty,'+
' b.VendorCode,'+
' bl.ItemCode,'+
' b.InvBillMonth '+
' from InvOutBillLine bl,InvOutBill b '+
' where b.InvBillMonth='+quotedstr(CalcMonth)+
' and bl.InvBillId=b.InvBillId and bl.InvBillqty>=0 '+
' and (b.BillTypeCode=''0202'' or (b.BillTypeCode=''0299'' and b.OPBill=1))'+
' group by b.InvBillMonth,b.VendorCode,bl.ItemCode) a'+
' where a.VendorCode=OpMonthSum.VendorCode'+
' and a.InvBillMonth=OpMonthSum.InvMonth'+
' and a.ItemCode=OpMonthSum.ItemCode');
AdoCmdExecSql
('正在进行委外加工材料发出方数量汇总......',
'insert OpMonthSum '+
' (InvMonth,VendorCode,ItemCode)'+
' select a.InvBillMonth,a.VendorCode,a.ItemCode'+
' from'+
' (select distinct o.ItemCode,b.InvBillMonth,b.VendorCode'+
' from opBill o'+
' Join InvInBill b'+
' on b.InvBillID=o.InvBillID '+
' and b.InvBillMonth='+quotedstr(CalcMonth)+
' and (b.BillTypeCode=''0103'' or (b.BillTypeCode=''0199'' and b.opBill=1) or b.BillTypeCode=''1202'') '+
' Join InvInBillLine bl'+
' on bl.InvBillID=b.InvBillID'+
' and bl.InvBillLineNo=o.InvBillLineNo) a'+
' Left Join OpMonthSum OM'+
' on OM.InvMonth=a.InvBillMonth'+
' and OM.VendorCode=a.VendorCode'+
' and OM.ItemCode=a.ItemCode'+
' and OM.InvMonth='+quotedstr(CalcMonth)+
' where OM.InvMonth is Null');
AdoCmdExecSql
///wxp，将InvOutBillLINE中InvBillQTY<0的记录作为供应商的发出
('正在进行委外加工材料发出方数量汇总......',
'Insert OpMonthSum '+
' (InvMonth,VendorCode,ItemCode) '+
' Select a.InvBillMonth,a.VendorCode,a.ItemCode '+
' from '+
' (select distinct b.InvBillMonth,b.VendorCode,bl.ItemCode'+
' from InvOutBill b,InvOutBillLine bl'+
' where b.InvBillMonth='+quotedstr(CalcMonth)+
' and b.InvBillid=bl.InvBillid and b.AmountAdjust=0 and bl.InvBillqty<0 '+
' and (b.BillTypeCode=''0202'' or (b.BillTypeCode=''0299'' and b.OpBill=1))) a '+
' Left Join OpMonthSum OPm'+
' on OPm.InvMonth=a.InvBillMonth'+
' and OPm.VendorCode=a.VendorCode'+
' and OPm.ItemCode=a.ItemCode'+
' and OPm.InvMonth='+quotedstr(CalcMonth)+
' where OPm.InvMonth is Null');
AdoCmdExecSql
('正在进行委外加工材料发出方数量汇总......',
'update OpMonthSum '+
' set InvOutQty=a.Qty'+
' from'+
' (select sum(op.OpBillQty) as Qty,'+
' b.VendorCode,op.ItemCode,b.InvBillMonth '+
' from InvInBillLine bl,InvInBill b,OPBill OP '+
' where b.InvBillMonth='+quotedstr(CalcMonth)+
' and OP.InvBillId=b.InvBillId'+
' and op.InvBillId=bl.InvBillId'+
' and OP.InvBillLineNo=bl.InvBillLineNo '+
' group by op.ItemCode,b.VendorCode,b.InvBillMonth) a '+
' where a.VendorCode=OpMonthSum.VendorCode'+
' and a.InvBillMonth=OpMonthSum.InvMonth'+
' and a.ItemCode=OpMonthSum.ItemCode');
AdoCmdExecSql
//wxp，还应该加上InvOutBillLINE中InvBillQTY<0的数据应为QTY<0,所以用减号
('正在进行委外加工材料发出方数量汇总......',
'update OpMonthSum '+
' set InvoutQty=InvoutQty-(a.Qty) '+
' from '+
' (select sum(bl.InvBillQty) as qty,'+
' b.VendorCode,'+
' bl.ItemCode,'+
' b.InvBillMonth '+
' from InvOutBillLine bl,InvOutBill b '+
' where b.InvBillMonth='+quotedstr(CalcMonth)+
' and bl.InvBillId=b.InvBillId and b.AmountAdjust=0 and bl.InvBillqty<0 '+
' and (b.BillTypeCode=''0202'' or (b.BillTypeCode=''0299'' and b.OPBill=1))'+
' group by b.InvBillMonth,b.VendorCode,bl.ItemCode) a'+
' where a.VendorCode=OpMonthSum.VendorCode'+
' and a.InvBillMonth=OpMonthSum.InvMonth'+
' and a.ItemCode=OpMonthSum.ItemCode');
AdoCmdExecSql
('正在进行委外加工材料结存数量处理......',
'Update OpMonthSum '+
' set InvBlncQty=InvLMQty+InvInQty-InvOutQty '+
' where InvMonth='+quotedstr(CalcMonth));
end;
//计划价核算仓库单据价格替换
procedure TFrm_Inv_MonthSum2.SPWhPriceConvert;
begin
AdoCmdExecSql
('正在处理按计划价核算仓库的单据价格、金额......',
'update InvInBillLine '+
' set InvBillNoTaxPrice=0,'+
' InvBillNoTaxAmount=0,'+
' InvBillPrice=0,'+
' InvBillAmount=0 '+
' from InvInBill B,Warehouse W '+
' where b.InvBillMonth='+quotedstr(CalcMonth)+
' and b.InvBillId=InvInBillLine.InvBillId'+
' and B.WHCode=W.WHCode'+
' and b.BillTypeCode not in (''0205'',''0206'') '+
' and W.PriceType=1');
AdoCmdExecSql
('正在处理按计划价核算仓库的单据价格、金额......',
'update InvInBillLine '+
' set InvBillNoTaxPrice=SP.SPPrice,'+
' InvBillNoTaxAmount=Convert(decimal(12,2),InvBillQty*SP.SPPrice),'+
' InvBillPrice=SP.SPPrice,'+
' InvBillAmount=Convert(decimal(12,2),InvBillQty*SP.SPPrice) '+
' from InvInBill B,StandardPrice SP,Warehouse W '+
' where b.InvBillMonth='+quotedstr(CalcMonth)+
' and b.InvBillId=InvInBillLine.InvBillId'+
' and B.WHCode=W.WHCode'+
' and W.PriceType=1'+
' and b.BillTypeCode not in (''0205'',''0206'') '+
' and SP.SPStArtMonth='+quotedstr(CalcMonth)+
' and SP.ItemCode=InvInBillLine.ItemCode ');
AdoCmdExecSql
('正在处理按计划价核算仓库的单据价格、金额......',
'update InvOutBillLine '+
' set InvBillNoTaxPrice=0,'+
' InvBillNoTaxAmount=0,'+
' InvBillPrice=0,'+
' InvBillAmount=0 '+
' from InvOutBill B,Warehouse W '+
' where b.InvBillMonth='+quotedstr(CalcMonth)+
' and b.InvBillId=InvOutBillLine.InvBillId'+
' and b.AmountAdjust=0 '+
' and b.BillTypeCode not in (''0205'',''0206'') '+
' and B.WHCode=W.WHCode'+
' and W.PriceType=1');
AdoCmdExecSql
('正在处理按计划价核算仓库的单据价格、金额......',
'update InvOutBillLine '+
' set InvBillNoTaxPrice=SP.SPPrice,'+
' InvBillNoTaxAmount=Convert(decimal(12,2),InvBillQty*SP.SPPrice),'+
' InvBillPrice=SP.SPPrice,'+
' InvBillAmount=Convert(decimal(12,2),InvBillQty*SP.SPPrice) '+
' from InvOutBill B,StandardPrice SP,Warehouse W '+
' where b.InvBillMonth='+quotedstr(CalcMonth)+
' and b.InvBillId=InvOutBillLine.InvBillId'+
' and b.AmountAdjust=0 '+
' and B.WHCode=W.WHCode'+
' and W.PriceType=1'+
' and b.BillTypeCode not in (''0205'',''0206'') '+
' and SP.SPStArtMonth='+quotedstr(CalcMonth)+
' and SP.ItemCode=InvOutBillLine.ItemCode ');
end;
procedure TFrm_Inv_MonthSum2.InSum;
begin
//1101货位移动，只是在仓库内部进行移动
//0104、0105半成品、成品入库，未经仓库核定的应该剔除在汇总之外
AdoCmdExecSql
('正在计算本厂仓库收入汇总数据......',
'Insert InvMonthSum '+
' (InvMonth,WhCode,ItemCode)'+
' select a.InvBillMonth,a.WhCode,a.ItemCode'+
' from'+
' (select distinct b.InvBillMonth,b.WhCode,bl.ItemCode'+
' from InvInBill b,InvInBillLine bl'+
' where b.InvBillMonth='+quotedstr(CalcMonth)+
' and b.InvBillID=bl.InvBillID'+
' and b.BillTypeCode <> ''1101'''+
' and (b.BillTypeCode not in(''0104'',''0105'') or b.InvBillWhChck=1)) a '+
' Left Join InvMonthSum IM'+
' on IM.InvMonth=a.InvBillMonth'+
' and IM.WhCode=a.WhCode'+
' and IM.ItemCode=a.ItemCode'+
' and IM.InvMonth='+quotedstr(CalcMonth)+
' where IM.InvMonth is Null');
AdoCmdExecSql
('正在计算本厂仓库收入汇总数据......',
'update InvMonthSum '+
' set InvInQty=a.Qty,'+
' InvInAmount=a.NoTaxAmount,'+
' InvInPrice=case when a.qty=0 then 0 else a.NoTaxAmount/a.qty end'+
' from'+
' (select sum(bl.InvBillQty) as Qty,'+
' sum(bl.InvBillNoTaxAmount) as NoTaxAmount,'+
' b.WHCode,bl.ItemCode,b.InvBillMonth '+
' from InvInBillLine bl,InvInBill b '+
' where b.InvBillMonth='+quotedstr(CalcMonth)+
' and b.InvBillId=bl.InvBillId'+
' and b.BillTypeCode <> ''1101'''+
' and ((b.BillTypeCode not in (''0104'',''0105'') or b.InvBillWHChck=1)) '+
' group by b.WhCode,bl.ItemCode,b.InvBillMonth) a '+
' where a.WhCode=InvMonthSum.WhCode'+
' and a.InvBillMonth=InvMonthSum.InvMonth'+
' and a.ItemCode=InvMonthSum.ItemCode ');
AdoCmdExecSql
('正在计算本厂仓库货位收入汇总数据......',
'Insert WhPMonthSum '+
' (InvMonth,WhCode,ItemCode,WhPositionCode)'+
' select a.InvBillMonth,a.WhCode,a.ItemCode,a.WhPositionCode'+
' from'+
' (select distinct b.InvBillMonth,b.WhCode,bl.ItemCode,b.WhPositionCode'+
' from InvInBill b,InvInBillline bl'+
' where b.InvBillMonth='+quotedstr(CalcMonth)+
' and b.InvBillid=bl.InvBillid'+
' and (b.BillTypeCode not in(''0104'',''0105'') or b.InvBillWhChck=1)) a'+
' Left Join WhPMonthSum IM'+
' on IM.InvMonth=a.InvBillMonth'+
' and IM.WhCode=a.WhCode'+
' and IM.ItemCode=a.ItemCode'+
' and IM.WhPositionCode=a.WhPositionCode'+
' and IM.InvMonth='+quotedstr(CalcMonth)+
' where IM.InvMonth is Null');
AdoCmdExecSql
('正在计算本厂仓库货位收入汇总数据......',
'update WhPMonthSum '+
' set InvInQty=a.Qty '+
' from'+
' (select sum(bl.InvBillQty)as Qty,'+
' b.WHCode,bl.ItemCode,b.WhPositionCode,b.InvBillMonth '+
' from InvInBillLine bl,InvInBill b '+
' where b.InvBillMonth='+quotedstr(CalcMonth)+
' and bl.InvBillId=b.InvBillId'+
' and (b.BillTypeCode not in (''0104'',''0105'') or b.InvBillWHChck=1) '+
' group by bl.ItemCode,b.WhCode,bl.ItemCode,b.WhPositionCode,b.InvBillMonth) a '+
' where a.WhCode=WhPMonthSum.WhCode'+
' and a.InvBillMonth=WhPMonthSum.InvMonth'+
' and a.ItemCode=WhPMonthSum.ItemCode'+
' and a.WhPositionCode=WhPMonthSum.WhPositionCode');
end;
procedure TFrm_Inv_MonthSum2.OutSum;
begin
//1101货位移动，只是在仓库内部进行移动
//0201、0203、0204生产领料、销售材料、成品，未经仓库核定的应该剔除在汇总之外
AdoCmdExecSql
('正在计算本厂仓库发出汇总数据......',
'Insert InvMonthSum '+
' (InvMonth,WhCode,ItemCode)'+
' select a.InvBillMonth,a.WhCode,a.ItemCode'+
' from'+
' (select distinct b.InvBillMonth,b.WhCode,bl.ItemCode'+
' from InvOutBill b,InvOutBillLine bl'+
' where b.InvBillMonth='+quotedstr(CalcMonth)+
' and b.InvBillID=bl.InvBillID'+
' and b.BillTypeCode <> ''1101'''+
' and b.BillTypeCode not in (''0205'',''0206'') '+ // 02.11.19 by zks;
' and (b.BillTypeCode not in(''0201'',''0203'',''0204'') or b.InvBillWhChck=1)) a '+
' Left Join InvMonthSum IM'+
' on IM.InvMonth=a.InvBillMonth'+
' and IM.WhCode=a.WhCode'+
' and IM.ItemCode=a.ItemCode'+
' and IM.InvMonth='+quotedstr(CalcMonth)+
' where IM.InvMonth is Null');
AdoCmdExecSql
('正在计算本厂仓库发出汇总数据......',
'update InvMonthSum '+
' set InvOutQty=a.Qty,'+
' InvOutAmount=a.NoTaxAmount '+
' from'+
' (select sum(bl.InvBillQty) as Qty,'+
' sum(bl.InvBillNoTaxAmount) as NoTaxAmount,'+
' b.WHCode,bl.ItemCode,b.InvBillMonth '+
' from InvOutBillLine bl,InvOutBill b '+
' where b.InvBillMonth='+quotedstr(CalcMonth)+
' and b.InvBillId=bl.InvBillId'+
' and b.AmountAdjust=0 '+
' and b.BillTypeCode <> ''1101'''+
' and b.BillTypeCode not in (''0205'',''0206'') '+ // 02.11.19 by zks;
' and ((b.BillTypeCode not in (''0201'',''0203'',''0204'') or b.InvBillWHChck=1)) '+
' group by b.WhCode,bl.ItemCode,b.InvBillMonth) a '+
' where a.WhCode=InvMonthSum.WhCode'+
' and a.InvBillMonth=InvMonthSum.InvMonth'+
' and a.ItemCode=InvMonthSum.ItemCode ');
AdoCmdExecSql
('正在计算本厂仓库货位发出汇总数据......',
'Insert WhPMonthSum '+
' (InvMonth,WhCode,ItemCode,WhPositionCode)'+
' select a.InvBillMonth,a.WhCode,a.ItemCode,a.WhPositionCode'+
' from'+
' (select distinct b.InvBillMonth,b.WhCode,bl.ItemCode,b.WhPositionCode'+
' from InvOutBill b,InvOutBillLine bl'+
' where b.InvBillMonth='+quotedstr(CalcMonth)+
' and b.InvBillid=bl.InvBillid '+
' and b.BillTypeCode not in (''0205'',''0206'') '+ // 02.11.19 by zks;
' and (b.BillTypeCode not in(''0201'',''0203'',''0204'') or b.InvBillWhChck=1)) a'+
' Left Join WhPMonthSum IM'+
' on IM.InvMonth=a.InvBillMonth'+
' and IM.WhCode=a.WhCode'+
' and IM.ItemCode=a.ItemCode'+
' and IM.WhPositionCode=a.WhPositionCode'+
' and IM.InvMonth='+quotedstr(CalcMonth)+
' where IM.InvMonth is Null');
AdoCmdExecSql
('正在计算本厂仓库货位发出汇总数据......',
'update WhPMonthSum '+
' set InvOutQty=a.Qty '+
' from'+
' (select sum(bl.InvBillQty)as Qty,'+
' b.WHCode,bl.ItemCode,b.WhPositionCode,b.InvBillMonth '+
' from InvOutBillLine bl,InvOutBill b '+
' where b.InvBillMonth='+quotedstr(CalcMonth)+
' and b.AmountAdjust=0 '+
' and bl.InvBillId=b.InvBillId'+
' and b.BillTypeCode not in (''0205'',''0206'') '+ // 02.11.19 by zks;
' and (b.BillTypeCode not in (''0201'',''0203'',''0204'') or b.InvBillWHChck=1) '+
' group by bl.ItemCode,b.WhCode,bl.ItemCode,b.WhPositionCode,b.InvBillMonth) a '+
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -