--GENERAL PURCHASE
DELETE FROM FIRM_ADDLESS_DETAIL WHERE FormName='GENERAL PURCHASE'

---------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
DECLARE @EntNo as NVARCHAR(256)
DECLARE Entry_Cursor CURSOR FOR SELECT SrNo FROM GENERAL_PURCHASE WHERE SrNo Not IN (SELECT FormSrNo FROM FIRM_ADDLESS_DETAIL WHERE FormName='GENERAL PURCHASE') Order By SrNo
OPEN Entry_Cursor;  
FETCH NEXT FROM Entry_Cursor INTO @EntNo;  
WHILE @@FETCH_STATUS = 0  
   BEGIN  
      INSERT INTO FIRM_ADDLESS_DETAIL SELECT @EntNo, FormName, SrNo, FldName, FldPerc, FldAddLess, FldPercMulti, FldRound, FldFormula, AccEff, EffAcc, 0 FROM ADDLESS_DETAIL WHERE FormName='GENERAL PURCHASE'
      FETCH NEXT FROM Entry_Cursor INTO @EntNo;  
   END;  
CLOSE Entry_Cursor;  
DEALLOCATE Entry_Cursor;  
---------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
UPDATE FIRM_ADDLESS_DETAIL SET FldPerc=T1.F2, FldAmount = T1.F3 FROM (SELECT SrNo AS F1, AVG(IGSTPerc) AS F2, SUM(IGSTAmt) AS F3 FROM GENERAL_PURCHASE_ITEM_DETAIL GROUP BY SrNo) AS T1 WHERE FormName = 'GENERAL PURCHASE' AND T1.F1 = FormSrNo AND FldName = 'IGST'
UPDATE FIRM_ADDLESS_DETAIL SET FldPerc=T1.F2, FldAmount = T1.F3 FROM (SELECT SrNo AS F1, AVG(CGSTPerc) AS F2, SUM(CGSTAmt) AS F3 FROM GENERAL_PURCHASE_ITEM_DETAIL GROUP BY SrNo) AS T1 WHERE FormName = 'GENERAL PURCHASE' AND T1.F1 = FormSrNo AND FldName = 'CGST'
UPDATE FIRM_ADDLESS_DETAIL SET FldPerc=T1.F2, FldAmount = T1.F3 FROM (SELECT SrNo AS F1, AVG(SGSTPerc) AS F2, SUM(SGSTAmt) AS F3 FROM GENERAL_PURCHASE_ITEM_DETAIL GROUP BY SrNo) AS T1 WHERE FormName = 'GENERAL PURCHASE' AND T1.F1 = FormSrNo AND FldName = 'SGST'
UPDATE FIRM_ADDLESS_DETAIL SET FldPerc=T1.F2, FldAmount = T1.F3 FROM (SELECT SrNo AS F1, AVG(UTGSTPerc) AS F2, SUM(UTGSTAmt) AS F3 FROM GENERAL_PURCHASE_ITEM_DETAIL GROUP BY SrNo) AS T1 WHERE FormName = 'GENERAL PURCHASE' AND T1.F1 = FormSrNo AND FldName = 'UTGST'
UPDATE FIRM_ADDLESS_DETAIL SET FldPerc=T1.F2, FldAmount = T1.F3 FROM (SELECT SrNo AS F1, AVG(CESSPerc) AS F2, SUM(CESSAmt) AS F3 FROM GENERAL_PURCHASE_ITEM_DETAIL GROUP BY SrNo) AS T1 WHERE FormName = 'GENERAL PURCHASE' AND T1.F1 = FormSrNo AND FldName = 'CESS'
UPDATE FIRM_ADDLESS_DETAIL SET FldPerc=T1.F2, FldAmount = T1.F3 FROM (SELECT SrNo AS F1, AVG(ItemDiscPerc) AS F2, SUM(ItemDiscAmt) AS F3 FROM GENERAL_PURCHASE_ITEM_DETAIL GROUP BY SrNo) AS T1 WHERE FormName = 'GENERAL PURCHASE' AND T1.F1 = FormSrNo AND FldName = 'DISCOUNT'
UPDATE FIRM_ADDLESS_DETAIL SET FldPerc=T1.F2, FldAmount = T1.F3 FROM (SELECT SrNo AS F1, AVG(ItemAddPerc) AS F2, SUM(ItemAddAmt) AS F3 FROM GENERAL_PURCHASE_ITEM_DETAIL GROUP BY SrNo) AS T1 WHERE FormName = 'GENERAL PURCHASE' AND T1.F1 = FormSrNo AND FldName = 'ADD'

UPDATE FIRM_ADDLESS_DETAIL SET FldAddLess='-', FldAmount = FldAmount-FldAmount-FldAmount WHERE FldAmount<0
---------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
IF OBJECTPROPERTY (OBJECT_ID('TblSetNetAmt'),'ISTABLE') = 1 DROP TABLE TblSetNetAmt
CREATE TABLE TblSetNetAmt (TmpEntNo Varchar(255),TmpNetAmt Money,ROffNetAmt Money,ROffAmt Money)
INSERT INTO TblSetNetAmt SELECT SrNo, GrossAmt, GrossAmt, 0 FROM GENERAL_PURCHASE
UPDATE TblSetNetAmt SET TmpNetAmt = TmpNetAmt + G1.L1 FROM (SELECT SUM(T1.F1) AS L1, T1.F2 AS L2 FROM (SELECT Sum(FldAmount) AS F1, FormSrNo AS F2 FROM FIRM_ADDLESS_DETAIL WHERE AccEff='NO' AND FormName='GENERAL PURCHASE' AND FldAmount<>0 AND FldAddLess='+' GROUP BY FormSrNo UNION ALL SELECT -Sum(FldAmount) AS F1, FormSrNo AS F2 FROM FIRM_ADDLESS_DETAIL WHERE AccEff='NO' AND FormName='GENERAL PURCHASE' AND FldAmount<>0 AND FldAddLess='-' GROUP BY FormSrNo) AS T1 GROUP BY T1.F2) AS G1 WHERE G1.L2 COLLATE DATABASE_DEFAULT=TmpEntNo
UPDATE TblSetNetAmt SET ROffNetAmt = ROffNetAmt + G1.L1 FROM (SELECT SUM(T1.F1) AS L1, T1.F2 AS L2 FROM (SELECT Sum(FldAmount) AS F1, FormSrNo AS F2 FROM FIRM_ADDLESS_DETAIL WHERE FormName='GENERAL PURCHASE' AND FldAmount<>0 AND FldAddLess='+' GROUP BY FormSrNo UNION ALL SELECT -Sum(FldAmount) AS F1, FormSrNo AS F2 FROM FIRM_ADDLESS_DETAIL WHERE FormName='GENERAL PURCHASE' AND FldAmount<>0 AND FldAddLess='-' GROUP BY FormSrNo) AS T1 GROUP BY T1.F2) AS G1 WHERE G1.L2 COLLATE DATABASE_DEFAULT=TmpEntNo
UPDATE TblSetNetAmt SET ROffAmt= ROUND(ROffNetAmt,0)
UPDATE TblSetNetAmt SET ROffAmt= ROffAmt - ROffNetAmt

UPDATE FIRM_ADDLESS_DETAIL SET FldAddLess='-', FldAmount =T1.F2 FROM (SELECT TmpEntNo AS F1, ROffAmt-ROffAmt-ROffAmt AS F2 FROM TblSetNetAmt WHERE ROffAmt<0) AS T1 WHERE FormName = 'GENERAL PURCHASE' AND T1.F1 = FormSrNo AND FldName = 'ROUNDING OFF'
UPDATE FIRM_ADDLESS_DETAIL SET FldAddLess='+', FldAmount =T1.F2 FROM (SELECT TmpEntNo AS F1, ROffAmt AS F2 FROM TblSetNetAmt WHERE ROffAmt>0) AS T1 WHERE FormName = 'GENERAL PURCHASE' AND T1.F1 = FormSrNo AND FldName = 'ROUNDING OFF'
---------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------- MATCHING
SELECT TmpEntNo,TmpNetAmt  FROM TblSetNetAmt
SELECT SrNo, NetAmt+TDSAmt FROM GENERAL_PURCHASE WHERE SrNo IN (SELECT TmpEntNo FROM TmpNetAmt)