Step 1 ---Get the header for the bills

SELECT
B.BILLID,
D.DCSDISPLAYCODE,
D.DCSNAME,
B.BILLDATE,
B.BILLNO,
B.RATEDISPLAYONBILL,
B.BILLAMOUNT,
F.BANKNAME,
T.ACCOUNTNO
FROM FA_DCSBILL_T B
inner join PI_DCS_M D on D.DCSCODE = B.DCSCODE
inner join PI_DCSBANK_T T on T.DCSCODE = D.DCSCODE
inner join FA_BANK_M F on T.BANKID = F.BANKID
where B.BILLFROMDATE = '01-06-2022' -– PARAMETER BY USER
and B.BILLTILLDATE = '10-06-2022' -– PARAMETER BY USER
and T.BANKUSAGEID = 'Z011'--CURRENT ACCOUNT
and D.DCSDISPLAYCODE = '8014' -– PARAMETER BY USER

step 2 – from the BILLID of step 1 get details-
select MILKDATE,
SHIFT,
QUALITY,
QUANTITY,
FATPERCENTAGE as FAT,
SNFPERCENTAGE as SNF,
CALCULATEDRATE AS RATE,
CALCULATEDAMOUNT AS AMOUNT
from FA_DCSBILLDETAILS_T

where BILLID = 1057689
order by MILKDATE
----output 1 (E) and 0 (M)
01-06-22 1 (E) SWEET 58 6.6 8.6 51.5 2987
01-06-22 0 (M) SWEET 104 6.2 8.6 48.5 5044
02-06-22 1 SWEET 64 6.7 8.6 52.25 3344
02-06-22 0 SWEET 106 6.2 8.6 48.5 5141
03-06-22 1 SWEET 59 7.1 8.6 55.25 3259.75
03-06-22 0 SWEET 99 6 8.6 47 4653
04-06-22 1 SWEET 54 7.3 8.6 56.75 3064.5
04-06-22 0 SWEET 112 6.4 8.6 50 5600
05-06-22 1 SWEET 37 7 8.7 54.5 2016.5
05-06-22 0 SWEET 86 6.2 8.6 48.5 4171
06-06-22 0 SWEET 89 6.2 8.6 48.5 4316.5
06-06-22 1 SWEET 54 6.9 8.6 53.75 2902.5
07-06-22 1 SWEET 61 7.2 8.6 56 3416
07-06-22 0 SWEET 86 6.1 8.6 47.75 4106.5
08-06-22 1 SWEET 48 6.9 8.6 53.75 2580
08-06-22 0 SWEET 92 6.3 8.6 49.25 4531
09-06-22 1 SWEET 63 6.7 8.6 52.25 3291.75
09-06-22 0 SWEET 82 6.5 8.6 50.75 4161.5
10-06-22 1 SWEET 53 6.9 8.6 53.75 2848.75
10-06-22 0 SWEET 79 6.1 8.6 47.75 3772.25

Step 3
Summation of Quantity and Amount from columns – Quantity and Amount

Step 4
PAYEMENTS
In the payments section
1. Milk Cost Amount

select sum (CALCULATEDAMOUNT)
from FA_DCSBILLDETAILS_T
where BILLID = 3776348– bill id from step 1
order by MILKDATE
2. Summation of Headload
select sum (HEADLOADAMOUNT)
from FA_DCSBILLDETAILS_T
where BILLID = '3623597' – bill id from step 1
order by MILKDATE
3. select * from CI_PAYMENTADVICE_T T
inner join PI_DCS_M D on D.DCSCODE = T.DCSID
where D.DCSDISPLAYCODE = '8014'
and T. PAYMENTADVICEDATE BETWEEN '01-06-2022' and '10-06-2022'

Step 5
DEDUCTIONS
1. Get the standard deductions
select T.DEDUCTIONTRANSNAME, T.DEDUCTIONTRANSAMT from FA_DEDUCTION_T T
inner join PI_DCS_M D on D.DCSCODE = T.DCSCODE
where DEDFROMDATE = '01-06-2022'
and DEDTODATE = '10-06-2022'
and D.DCSDISPLAYCODEz` = '8014'

2. Get the other deductions (cattlefeed, ghee, etc.)
select D.DCSDISPLAYCODE,
T.BILLFROMDATE,
T.BILLTILLDATE,
T.AMOUNT,
M.DEDUCTIONHEADNAME
from FA_CIDEDUCTION_T T
inner join PI_DCS_M D on D.DCSCODE = T.DCSCODE

inner join CI_DEDUCTIONHEAD_M M on T.DEDUCTIONHEADID = M.DEDUCTIONHEADID
where
T.BILLFROMDATE = '01-06-2022' and
T.BILLTILLDATE = '10-06-2022' and
D.DCSDISPLAYCODE = '8014'
order by BILLFROMDATE

Outstanding deductions: from previous cycles
SELECT T.BILLFROMDATE, T.BILLTILLDATE,T.DEDUCTIONHEADNAME,T.DEDUCTEDAMOUNT
FROM FA_DCSWISEDEDHEADWISE_TEMP T
INNER JOIN PI_DCS_M M on M.DCSCODE = T.DCSID
WHERE T.BILLFROMDATE = '11-05-22'
and T.BILLTILLDATE = '20-05-22'
and M.DCSDISPLAYCODE = '8014'
Step 6
select PRICEDIFFPERIODICALAMT from FA_PRICEDIFFPERIODICAL_T T
inner join PI_DCS_M D on D.DCSCODE = T.DCSCODE
where D.DCSDISPLAYCODE = '8014'
and T.MILKFROMDATE = '11-05-2022'
and T.MILKTODATE = '20-05-2022'

Step 7
Additional Payment Status….
select M.SERVICENAME, T.PAYMENTADVICEAMOUNT from CI_PAYMENTADVICE_T T
inner join PI_DCS_M D on D.DCSCODE = T.DCSID
inner join CI_SERVICE_M M on T.SERVICEID = M.SERVICEID
where D.DCSDISPLAYCODE = '8014'
and T.PAYMENTADVICEDATE BETWEEN '01-05-23' and '10-05-23'
order by T.CREATIONDATE
--PAYMENTADVICEAMOUNT
Step 8

CALCULATE the NET PAYMENT after adding