USE T202627

DROP TABLE PurchasePending 

CREATE TABLE PurchasePending (SuppCode Varchar(255), CustCode Varchar(255), BillNo Varchar(255), FirmCode Varchar(255), BillAmt Money, PaidAmt Money, GRAmt Money, GRLess Money, BalanceAmt Money, SuppBillDate DateTime,VouNo Varchar(255),VouSrNo Varchar(255))

INSERT INTO PurchasePending SELECT VouSuppCode, VouCustCode, SuppBillNo, FirmCode, VouNetAmt, 0, RtnGds, 0, 0, SuppBillDate, VouNo, VouSrNo FROM OPENING_PURCHASE_DETAIL WHERE (SuppBillDate <= '2025-03-31') AND (VouStatus <> 'FULL')

UPDATE PurchasePending SET GRAmt = T1.F1 FROM (SELECT SUM(RETURN_GOODS_ADDLESS_DETAIL.RtnGRAmt) AS F1, RETURN_GOODS_DETAIL.RtnSuppCode AS F2, RETURN_GOODS_DETAIL.RtnCustCode AS F3, RETURN_GOODS_DETAIL.RtnFirmCode AS F4, RETURN_GOODS_ADDLESS_DETAIL.RtnBillNo AS F5 FROM RETURN_GOODS_DETAIL INNER JOIN RETURN_GOODS_ADDLESS_DETAIL ON RETURN_GOODS_DETAIL.RtnSrNo = RETURN_GOODS_ADDLESS_DETAIL.RtnSrNo GROUP BY RETURN_GOODS_DETAIL.RtnSuppCode, RETURN_GOODS_DETAIL.RtnCustCode, RETURN_GOODS_DETAIL.RtnFirmCode, RETURN_GOODS_ADDLESS_DETAIL.RtnBillNo) AS T1 WHERE T1.F2 = SuppCode AND T1.F3 = CustCode AND T1.F4 = FirmCode AND T1.F5 = BillNo
UPDATE PurchasePending SET GRLess = T1.F0, PaidAmt = T1.F1 FROM (SELECT SUM(RECEIVED_ADDLESS_DETAIL.GRLess) AS F0, SUM(RECEIVED_ADDLESS_DETAIL.PaidAmt)+SUM((RECEIVED_ADDLESS_DETAIL.AddIntAmt + RECEIVED_ADDLESS_DETAIL.OtherAdd) - (RECEIVED_ADDLESS_DETAIL.DiscAmt + RECEIVED_ADDLESS_DETAIL.RateDiff + RECEIVED_ADDLESS_DETAIL.ClaimLess + RECEIVED_ADDLESS_DETAIL.BankComm + RECEIVED_ADDLESS_DETAIL.PackTrans + RECEIVED_ADDLESS_DETAIL.OtherLess)) AS F1, RECEIVED_DETAIL.RecSuppCode AS F2, RECEIVED_DETAIL.RecCustCode AS F3, RECEIVED_DETAIL.RecFirmCode AS F4, RECEIVED_ADDLESS_DETAIL.RecBillNo AS F5 FROM RECEIVED_DETAIL INNER JOIN RECEIVED_ADDLESS_DETAIL ON RECEIVED_DETAIL.RecSrNo = RECEIVED_ADDLESS_DETAIL.RecSrNo GROUP BY RECEIVED_DETAIL.RecSuppCode, RECEIVED_DETAIL.RecCustCode, RECEIVED_DETAIL.RecFirmCode, RECEIVED_ADDLESS_DETAIL.RecBillNo) AS T1 WHERE T1.F2 = SuppCode AND T1.F3 = CustCode AND T1.F4 = FirmCode AND T1.F5 = BillNo

--UPDATE PurchasePending SET GRAmt = GRLess WHERE GRAmt<=GRLess

UPDATE PurchasePending SET BalanceAmt = BillAmt - PaidAmt - GRAmt

select * from PurchasePending

INSERT INTO RECEIVED_DETAIL SELECT '9' + RIGHT(VouNo, 9) AS Expr1, CustCode, 'CASH' AS Expr2, BillAmt, DATEADD(DAY, 90, SuppBillDate) AS Expr3, '' AS Expr4, '' AS Expr5, SuppCode, '' AS Expr6, 'NO' AS Expr7, FirmCode, '0102' + RIGHT(VouSrNo, 6) AS Expr8, FirmCode, DATEADD(DAY, 90, SuppBillDate) AS Expr9, CONVERT(VARCHAR(8), GETDATE(), 108) AS Expr10, '' AS Uname, 0 AS Expr11, 0 AS Expr12, 0 AS Expr13, '0102' + RIGHT(VouSrNo, 6) AS Expr14, 0 AS Expr15, '' AS Expr16, 0 AS Expr17, 0 AS Expr18, 0 AS Expr19, 5 AS Expr20, 'CHEQUE AMOUNT' AS Expr21, 'SUPPLIER' AS Expr22 FROM PurchasePending
INSERT INTO RECEIVED_ADDLESS_DETAIL SELECT '9' + RIGHT(VouNo, 9) AS Expr1, BillNo, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, BillAmt, 'FULL', FirmCode FROM PurchasePending

UPDATE OPENING_PURCHASE_DETAIL SET VouStatus = 'FULL' WHERE (SuppBillDate <= '2025-03-31')

select * from RECEIVED_ADDLESS_DETAIL