--SALE BILL
DELETE FROM FIRM_ADDLESS_DETAIL WHERE FormName='SALE BILL'

---------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
DECLARE @EntNo as NVARCHAR(256)
DECLARE Entry_Cursor CURSOR FOR SELECT BillNo FROM BILL_DETAIL WHERE BillNo Not IN (SELECT FormSrNo FROM FIRM_ADDLESS_DETAIL WHERE FormName='SALE BILL') Order By BillNo
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='SALE BILL'
      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 BillNo AS F1, AVG(IGSTPerc) AS F2, SUM(IGSTAmt) AS F3 FROM BILL_ITEM_DETAIL GROUP BY BillNo) AS T1 WHERE FormName = 'SALE BILL' AND T1.F1 = FormSrNo AND FldName = 'IGST'
UPDATE FIRM_ADDLESS_DETAIL SET FldPerc=T1.F2, FldAmount = T1.F3 FROM (SELECT BillNo AS F1, AVG(CGSTPerc) AS F2, SUM(CGSTAmt) AS F3 FROM BILL_ITEM_DETAIL GROUP BY BillNo) AS T1 WHERE FormName = 'SALE BILL' AND T1.F1 = FormSrNo AND FldName = 'CGST'
UPDATE FIRM_ADDLESS_DETAIL SET FldPerc=T1.F2, FldAmount = T1.F3 FROM (SELECT BillNo AS F1, AVG(SGSTPerc) AS F2, SUM(SGSTAmt) AS F3 FROM BILL_ITEM_DETAIL GROUP BY BillNo) AS T1 WHERE FormName = 'SALE BILL' AND T1.F1 = FormSrNo AND FldName = 'SGST'
UPDATE FIRM_ADDLESS_DETAIL SET FldPerc=T1.F2, FldAmount = T1.F3 FROM (SELECT BillNo AS F1, AVG(UTGSTPerc) AS F2, SUM(UTGSTAmt) AS F3 FROM BILL_ITEM_DETAIL GROUP BY BillNo) AS T1 WHERE FormName = 'SALE BILL' AND T1.F1 = FormSrNo AND FldName = 'UTGST'
UPDATE FIRM_ADDLESS_DETAIL SET FldPerc=T1.F2, FldAmount = T1.F3 FROM (SELECT BillNo AS F1, AVG(CESSPerc) AS F2, SUM(CESSAmt) AS F3 FROM BILL_ITEM_DETAIL GROUP BY BillNo) AS T1 WHERE FormName = 'SALE BILL' AND T1.F1 = FormSrNo AND FldName = 'CESS'
UPDATE FIRM_ADDLESS_DETAIL SET FldPerc=T1.F2, FldAmount = T1.F3 FROM (SELECT BillNo AS F1, AVG(ItemDiscPerc) AS F2, SUM(ItemDiscAmt) AS F3 FROM BILL_ITEM_DETAIL GROUP BY BillNo) AS T1 WHERE FormName = 'SALE BILL' AND T1.F1 = FormSrNo AND FldName = 'DISCOUNT'
UPDATE FIRM_ADDLESS_DETAIL SET FldPerc=T1.F2, FldAmount = T1.F3 FROM (SELECT BillNo AS F1, AVG(ItemAddPerc) AS F2, SUM(ItemAddAmt) AS F3 FROM BILL_ITEM_DETAIL GROUP BY BillNo) AS T1 WHERE FormName = 'SALE BILL' AND T1.F1 = FormSrNo AND FldName = 'ADD'
UPDATE FIRM_ADDLESS_DETAIL SET FldAmount = T1.F2 FROM (SELECT BillNo AS F1, SUM(PackCharge) AS F2 FROM BILL_ITEM_DETAIL GROUP BY BillNo) AS T1 WHERE FormName = 'SALE BILL' AND T1.F1 = FormSrNo AND FldName = 'PACKING CHARGE'
UPDATE FIRM_ADDLESS_DETAIL SET FldAmount = T1.F2 FROM (SELECT BillNo AS F1, SUM(TransCharge) AS F2 FROM BILL_ITEM_DETAIL GROUP BY BillNo) AS T1 WHERE FormName = 'SALE BILL' AND T1.F1 = FormSrNo AND FldName = 'TRANSPORT CHARGE'
UPDATE FIRM_ADDLESS_DETAIL SET FldAmount = T1.F2 FROM (SELECT BillNo AS F1, SUM(RDAmt) AS F2 FROM BILL_ITEM_DETAIL GROUP BY BillNo) AS T1 WHERE FormName = 'SALE BILL' AND T1.F1 = FormSrNo AND FldName = 'RATE DIFFERENCE'
UPDATE FIRM_ADDLESS_DETAIL SET FldAddLess='+', FldPerc=T1.F2, FldAmount = T1.F3 FROM (SELECT BillNo AS F1, AVG(AddLess1Perc) AS F2, SUM(AddLess1Amt) AS F3 FROM BILL_ITEM_DETAIL GROUP BY BillNo HAVING SUM(AddLess1Amt)>0 ) AS T1 WHERE FormName = 'SALE BILL' AND T1.F1 = FormSrNo AND SrNo = 1
UPDATE FIRM_ADDLESS_DETAIL SET FldAddLess='+', FldPerc=T1.F2, FldAmount = T1.F3 FROM (SELECT BillNo AS F1, AVG(AddLess2Perc) AS F2, SUM(AddLess2Amt) AS F3 FROM BILL_ITEM_DETAIL GROUP BY BillNo HAVING SUM(AddLess2Amt)>0) AS T1 WHERE FormName = 'SALE BILL' AND T1.F1 = FormSrNo AND SrNo = 3
UPDATE FIRM_ADDLESS_DETAIL SET FldAddLess='-', FldPerc=T1.F2, FldAmount = T1.F3 FROM (SELECT BillNo AS F1, AVG(AddLess1Perc) AS F2, SUM(AddLess1Amt) AS F3 FROM BILL_ITEM_DETAIL GROUP BY BillNo HAVING SUM(AddLess1Amt)<0) AS T1 WHERE FormName = 'SALE BILL' AND T1.F1 = FormSrNo AND SrNo = 1
UPDATE FIRM_ADDLESS_DETAIL SET FldAddLess='-', FldPerc=T1.F2, FldAmount = T1.F3 FROM (SELECT BillNo AS F1, AVG(AddLess2Perc) AS F2, SUM(AddLess2Amt) AS F3 FROM BILL_ITEM_DETAIL GROUP BY BillNo HAVING SUM(AddLess2Amt)<0) AS T1 WHERE FormName = 'SALE BILL' AND T1.F1 = FormSrNo AND SrNo = 3

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 BillNo, GrossAmt, GrossAmt, 0 FROM BILL_DETAIL
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='SALE BILL' 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='SALE BILL' 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='SALE BILL' AND FldAmount<>0 AND FldAddLess='+' GROUP BY FormSrNo UNION ALL SELECT -Sum(FldAmount) AS F1, FormSrNo AS F2 FROM FIRM_ADDLESS_DETAIL WHERE FormName='SALE BILL' 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 = 'SALE BILL' 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 = 'SALE BILL' AND T1.F1 = FormSrNo AND FldName = 'ROUNDING OFF'
---------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------- MATCHING
SELECT TmpEntNo,TmpNetAmt  FROM TblSetNetAmt
SELECT BillNo, NetAmt FROM BILL_DETAIL WHERE BillNo IN (SELECT TmpEntNo FROM TmpNetAmt)




------------IF ITEM DETAIL IS DOUBLE ORE ADDLESS WILL BE DOUBLE  
UPDATE BILL_DETAIL SET GROSSAMT = T1.F1 FROM (SELECT SUM(ITEMAMOUNT) AS F1, BILLNO AS F2 FROM BILL_ITEM_DETAIL GROUP BY BILLNO) AS T1 WHERE T1.F2 = BILLNO
UPDATE BILL_DETAIL SET NETAMT = GROSSAMT 
UPDATE BILL_DETAIL SET NETAMT = GrossAmt+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='SALE BILL' 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='SALE BILL' AND FldAmount<>0 AND FldAddLess='-' GROUP BY FormSrNo) AS T1 GROUP BY T1.F2) AS G1 WHERE G1.L2 COLLATE DATABASE_DEFAULT=BillNo