/*----------------
Select b.FFINORGID as FORGID,MAX(a.FCHCKOUTDATE)as FCLOSEDATE
Into #TempSCTL20240830--结账日期临时表
From T_HS_OUTACCTG a --结账表
inner join T_HS_CALDIMENSIONS b on a.FDIMENSIONID =b.FDIMENSIONID --结账子表
inner join T_ORG_ORGANIZATIONS c on b.FFINORGID =c.FORGID --组织表
inner join T_ORG_ACCOUNTSYSTEM d on b.FACCTSYSTEMID=d.FACCTSYSTEMID --会计核算体系
inner join T_FA_ACCTPOLICY e on b.FACCTPOLICYID=e.FACCTPOLICYID --会计政策编码
group by b.FFINORGID
--如果要以结账日期做关联取这里的<结账日期临时表>
----------------*/
--创建生产退料零成本记录表,预防万一,可以做数据恢复
Create table
T_SCTL_Record
(FID INT,FENTRYID INT,FMATERIALID INT,FBILLNO NVARCHAR(255),
FNUMBER NVARCHAR(800),FPrice decimal(24,4),FAmount decimal(24,4),FDATE date)
-----------------------------------------------------------------------------------------------------------------------------
--创建处理的存储过程
Create Procedure Proc_Update_RETURNMTRLENTRY_Price
as
Begin
declare @count int
insert into T_SCTL_Record --记录原来0成本的生产退料明细
Select distinct T1.FID,T1.FENTRYID,T1.FMATERIALID,T0.FBILLNO,W1.FNUMBER,T1.FPrice,T1.FAmount,T0.FDATE
From T_PRD_RETURNMTRLENTRY T1
inner join T_PRD_PPBOM T2 on T2.FBILLNO =T1.FPPBOMBILLNO
inner join T_PRD_PICKMTRLDATA T3 on T2.FBILLNO = T3.FPPBOMBILLNO And T3.FMATERIALID = T1.FMATERIALID
inner join T_PRD_RETURNMTRLENTRY_C T01 on T1.FENTRYID = T01.FENTRYID
inner join T_PRD_RETURNMTRL T0 on T1.FID = T0.FID
left join T_BAS_Voucher T12 on T12.FSOURCEBILLNO = T0.FBILLNO
--left join T_SCTL_Record re on re.FENTRYID = T1.FENTRYID
inner join T_BD_MATERIAL W1 on W1.FMATERIALID = T1.FMATERIALID
inner join T_PRD_PICKMTRL L0 ON T3.FID = L0.FID
inner join (Select FORGID,MAX(FCLOSEDATE) as FCLOSEDATE From T_STK_CLOSEPROFILE group by FORGID)T13 on T13.FORGID = T0.FSTOCKORGID
Where T1.FPrice = 0 And T01.FPrice = 0 And T0.FDATE > T13.FCLOSEDATE
And T12.FSOURCEBILLNO is null
And L0.FDATE > T13.FCLOSEDATE
And T3.FPPBOMBILLNO = T1.FPPBOMBILLNO
Set @count = (Select @@rowcount) --返回受影响的行
-------------------------------------------------------------------------------------------------------------------------------
--更新生产退料单币别
update T0 Set T0.FCURRID = T30.FCURRID
From T_PRD_RETURNMTRLENTRY T1
inner join T_PRD_PPBOM T2 on T2.FBILLNO =T1.FPPBOMBILLNO
inner join T_PRD_PICKMTRLDATA T3 on T2.FBILLNO = T3.FPPBOMBILLNO And T3.FMATERIALID = T1.FMATERIALID
inner join T_PRD_PICKMTRL T30 ON T3.FID=T30.FID
inner join T_PRD_RETURNMTRLENTRY_C T01 on T1.FENTRYID = T01.FENTRYID
inner join T_PRD_RETURNMTRL T0 on T1.FID = T0.FID
left join T_BAS_Voucher T12 on T12.FSOURCEBILLNO = T0.FBILLNO
inner join T_BD_MATERIAL W1 on W1.FMATERIALID = T1.FMATERIALID
--left join T_SCTL_Record re on re.FENTRYID = T1.FENTRYID
inner join T_PRD_PICKMTRL L0 ON T3.FID = L0.FID
inner join(Select FORGID,MAX(FCLOSEDATE) as FCLOSEDATE From T_STK_CLOSEPROFILE group by FORGID)T13 on T13.FORGID=T0.FSTOCKORGID --关联存货核算期末结账最新关账日期
Where T1.FPrice = 0 And T01.FPrice = 0 And T0.FDATE > T13.FCLOSEDATE
And T12.FSOURCEBILLNO is null
And T3.FPrice > 0
And L0.FDATE > T13.FCLOSEDATE
And T3.FPPBOMBILLNO = T1.FPPBOMBILLNO
--更新生产退料明细成本
Update T1 Set T1.FPrice=T3.FPrice,T1.FAmount=T3.FPrice*T1.Fqty From T_PRD_RETURNMTRLENTRY T1 --生产退料明细
inner join T_PRD_PPBOM T2 on T2.FBILLNO=T1.FPPBOMBILLNO--用料清单
inner join T_PRD_PICKMTRLDATA T3 on T2.FBILLNO=T3.FPPBOMBILLNO And T3.FMATERIALID=T1.FMATERIALID--生产领料
inner join T_PRD_RETURNMTRLENTRY_C T01 on T1.FENTRYID=T01.FENTRYID--生产退料明细CE表
inner join T_PRD_RETURNMTRL T0 on T1.FID=T0.FID--生产退料单
left join T_BAS_Voucher T12 on T12.FSOURCEBILLNO=T0.FBILLNO--业务凭证
--left join T_SCTL_Record re on re.FENTRYID = T1.FENTRYID
inner join T_PRD_PICKMTRL L0 ON T3.FID = L0.FID
inner join (Select FORGID,MAX(FCLOSEDATE) as FCLOSEDATE From T_STK_CLOSEPROFILE group by FORGID)T13 on T13.FORGID=T0.FSTOCKORGID --关联存货核算期末关结账最新关账日期
Where T1.FPrice = 0 And T01.FPrice = 0 And T0.FDATE > T13.FCLOSEDATE
And T12.FSOURCEBILLNO is null--不关联业务凭证
And L0.FDATE > T13.FCLOSEDATE
And T3.FPPBOMBILLNO = T1.FPPBOMBILLNO
--更新C表成本
Update T01 set T01.FISACCTGBILL = 1,--更新 _C 表的固定成本标识
T01.FAMOUNT_LC =T1.FAmount,T01.FPrice=T1.FPrice
From T_PRD_RETURNMTRLENTRY T1 --生产退料明细
inner join T_PRD_PPBOM T2 on T2.FBILLNO=T1.FPPBOMBILLNO--用料清单
inner join T_PRD_PICKMTRLDATA T3 on T2.FBILLNO=T3.FPPBOMBILLNO And T3.FMATERIALID=T1.FMATERIALID--生产领料
inner join T_PRD_RETURNMTRLENTRY_C T01 on T1.FENTRYID=T01.FENTRYID--生产退料明细C表
inner join T_PRD_RETURNMTRL T0 on T1.FID=T0.FID--生产退料单
left join T_BAS_Voucher T12 on T12.FSOURCEBILLNO=T0.FBILLNO--业务凭证
--left join T_SCTL_Record re on re.FENTRYID = T1.FENTRYID
inner join T_PRD_PICKMTRL L0 ON T3.FID = L0.FID
inner join(Select FORGID,MAX(FCLOSEDATE) as FCLOSEDATE From T_STK_CLOSEPROFILE group by FORGID)T13 on T13.FORGID=T0.FSTOCKORGID --关联存货核算期末结账最新关账日期
Where T1.FPrice = 0 And T01.FPrice = 0 And T0.FDATE > T13.FCLOSEDATE
And T12.FSOURCEBILLNO is null--不关联业务凭证
And L0.FDATE > T13.FCLOSEDATE
And T3.FPPBOMBILLNO = T1.FPPBOMBILLNO
--更新CE表成本
Update T01 set T01.FAMOUNT_LC = T1.FAmount
From T_PRD_RETURNMTRLENTRY T1 --生产退料明细
inner join T_PRD_PPBOM T2 on T2.FBILLNO=T1.FPPBOMBILLNO--用料清单
inner join T_PRD_PICKMTRLDATA T3 on T2.FBILLNO=T3.FPPBOMBILLNO And T3.FMATERIALID=T1.FMATERIALID--生产领料
inner join T_PRD_RETURNMTRLENTRY_CE T01 on T1.FENTRYID=T01.FENTRYID--生产退料明细CE表
inner join T_PRD_RETURNMTRL T0 on T1.FID=T0.FID--生产退料单
left join T_BAS_Voucher T12 on T12.FSOURCEBILLNO=T0.FBILLNO--业务凭证
--left join T_SCTL_Record re on re.FENTRYID = T1.FENTRYID
inner join T_PRD_PICKMTRL L0 ON T3.FID = L0.FID
inner join(Select FORGID,MAX(FCLOSEDATE) as FCLOSEDATE From T_STK_CLOSEPROFILE group by FORGID)T13 on T13.FORGID=T0.FSTOCKORGID --关联存货核算期末结账最新关账日期
Where T1.FPrice = 0 And T01.FAMOUNT_LC = 0 And T0.FDATE > T13.FCLOSEDATE
And T12.FSOURCEBILLNO is null--不关联业务凭证
And L0.FDATE > T13.FCLOSEDATE
And T3.FPPBOMBILLNO = T1.FPPBOMBILLNO
--结束
Select @count as FColumn --返回insert的新增行数
--drop table #TempSCTL20240830 --删除结账日期临时表
End