74 lines
2.6 KiB
Transact-SQL
74 lines
2.6 KiB
Transact-SQL
--UpdateSalOrderAmount
|
||
--Select * From T_TempModelPath
|
||
--Insert into T_TempModelPath Values('SX-1','D:\模板\SX系统模板（含调试）.docx'),('SX-2','D:\模板\SX系统模板（不含调试）.docx')
|
||
--Update T_TempModelPath Set FTempPath = 'D:\模板\SX系统模板（不含调试）.docx' Where FKeyId = 'SX-2';
|
||
go
|
||
--sp_helptext UpdateSalOrderAmount
|
||
Alter proc UpdateSalOrderAmount
|
||
(
|
||
@fid BIGINT
|
||
)
|
||
AS
|
||
BEGIN
|
||
--修改销售订单累计核销金额
|
||
UPDATE T_AutoWrireRecordEntry SET FALLAMOUNT2=FALLAMOUNT22
|
||
WHERE fid=@fid
|
||
|
||
|
||
UPDATE T_AutoWrireRecordEntry2 SET FALLAMOUNT3=FALLAMOUNT33
|
||
WHERE fid=@fid
|
||
|
||
UPDATE C SET F_Amount =c.F_Amount+ISNULL( d.核销金额,0)
|
||
FROM T_AutoWrireRecord A
|
||
INNER JOIN T_AutoWrireRecordEntry B ON A.FID = B.FID
|
||
INNER JOIN T_SAL_ORDER C ON C.FBILLNO = B.FBILLNO2
|
||
LEFT JOIN
|
||
(
|
||
SELECT FBILLNO2,SUM(CONVERT(DECIMAL(18,2), FALLAMOUNT2)) AS '核销金额' FROM T_AutoWrireRecord Aa
|
||
INNER JOIN T_AutoWrireRecordEntry B ON Aa.FID = B.FID
|
||
WHERE Aa.FID =@FID
|
||
GROUP BY B.FBILLNO2
|
||
) d ON d.FBILLNO2 =b.FBILLNO2
|
||
WHERE a.FID =@FID ;
|
||
|
||
|
||
--修改应收单累计核销金额
|
||
UPDATE C SET F_Amount =c.F_Amount+ISNULL( d.核销金额,0)
|
||
FROM T_AutoWrireRecord A
|
||
INNER JOIN T_AutoWrireRecordEntry B ON A.FID = B.FID
|
||
INNER JOIN T_AR_RECEIVABLE C ON C.FBILLNO = B.FBILLNO2
|
||
LEFT JOIN
|
||
(
|
||
SELECT FBILLNO2,SUM(CONVERT(DECIMAL(18,2), FALLAMOUNT2)) AS '核销金额' FROM T_AutoWrireRecord Aa
|
||
INNER JOIN T_AutoWrireRecordEntry B ON Aa.FID = B.FID
|
||
WHERE Aa.FID =@FID
|
||
GROUP BY B.FBILLNO2
|
||
) d ON d.FBILLNO2 =b.FBILLNO2
|
||
WHERE a.FID =@FID ;
|
||
--修改收款单累计核销金额
|
||
UPDATE C SET F_Amount =c.F_Amount+ISNULL( d.核销金额,0) ,C.F_CONTRACTNUMBER=CASE WHEN C.F_CONTRACTNUMBER ='' THEN b.FCONTRACTNUMBER2 ELSE C.F_CONTRACTNUMBER +';'+ b.FCONTRACTNUMBER2 END
|
||
FROM T_AutoWrireRecord A
|
||
INNER JOIN T_AutoWrireRecordEntry2 B ON A.FID = B.FID
|
||
INNER JOIN T_AR_RECEIVEBILL C ON C.FBILLNO = B.FBILLNO3
|
||
LEFT JOIN
|
||
(
|
||
SELECT FBILLNO3,SUM(CONVERT(DECIMAL(18,2), FALLAMOUNT3)) AS '核销金额' FROM T_AutoWrireRecord Aa
|
||
INNER JOIN T_AutoWrireRecordEntry2 B ON Aa.FID = B.FID
|
||
WHERE Aa.FID =@FID
|
||
GROUP BY B.FBILLNO3
|
||
) d ON d.FBILLNO3 =b.FBILLNO3
|
||
WHERE a.FID =@FID ;
|
||
end
|
||
|
||
|
||
|
||
--SKD00000003
|
||
--SELECT FBILLNO3,SUM(CONVERT(decimal(18,2), FALLAMOUNT3)) AS '核销金额' FROM T_AutoWrireRecord A
|
||
-- INNER JOIN T_AutoWrireRecordEntry2 B ON A.FID = B.FID
|
||
-- WHERE A.FDOCUMENTSTATUS = 'C'
|
||
-- GROUP BY B.FBILLNO3
|
||
|
||
|
||
go
|
||
--Select * From T_SAL_ORDER Where FBILLNO = 'PL-XSDD20250200007'
|
||
--Update T_SAL_ORDER Set FDOCUMENTSTATUS = 'D' Where FBILLNO = 'PL-XSDD20250200007' |