use t202223

UPDATE T23B001.dbo.BILL_ITEM_DETAIL SET Charak=T1.L3 FROM (
SELECT G1.F1 AS L1, SUM(G1.F3) AS L3 FROM (
SELECT RECEIVED_ADDLESS_DETAIL.RecSrNo AS F1, RECEIVED_ADDLESS_DETAIL.PaidAmt AS F3 FROM RECEIVED_ADDLESS_DETAIL INNER JOIN RECEIVED_DETAIL ON RECEIVED_ADDLESS_DETAIL.RecSrNo = RECEIVED_DETAIL.RecSrNo INNER JOIN PURCHASE_DETAIL ON RECEIVED_DETAIL.RecSuppCode = PURCHASE_DETAIL.VouSuppCode AND RECEIVED_DETAIL.RecCustCode = PURCHASE_DETAIL.VouCustCode AND RECEIVED_ADDLESS_DETAIL.RecBillNo = PURCHASE_DETAIL.SuppBillNo
UNION
SELECT RECEIVED_ADDLESS_DETAIL.RecSrNo AS F1, RECEIVED_ADDLESS_DETAIL.PaidAmt AS F3 FROM RECEIVED_ADDLESS_DETAIL INNER JOIN RECEIVED_DETAIL ON RECEIVED_ADDLESS_DETAIL.RecSrNo = RECEIVED_DETAIL.RecSrNo INNER JOIN OPENING_PURCHASE_DETAIL ON RECEIVED_DETAIL.RecSuppCode = OPENING_PURCHASE_DETAIL.VouSuppCode AND RECEIVED_DETAIL.RecCustCode = OPENING_PURCHASE_DETAIL.VouCustCode AND RECEIVED_ADDLESS_DETAIL.RecBillNo = OPENING_PURCHASE_DETAIL.SuppBillNo
) AS G1 GROUP BY G1.F1
) AS T1 WHERE T1.L1 = IFSrNo


UPDATE T23B001.dbo.BILL_ITEM_DETAIL SET SuppRate=Charak-round((Charak * T1.F1)/100,0) FROM (
select GSTPerc AS F1, right('000000'+BillNo+'/',7) AS F2 from T202223.DBO.BROKERAGE_DETAIL ) AS T1 WHERE T1.F2 = BillNo