select GLL."Trn_Ref", GLL."Trn_Date", GLL."Inv_Type", GLL."Pmt_Num", GLL."Trn_Store",
(select BM."Amt_Due" from "Bill_Main" BM where BM."Ref_Number" = GLL."Trn_Ref" and GLL."Trn_Source" = 'D') "Left to Pay",
case when GLL."Pmt_Num" = 3 and "Inv_Type" <> 12 then GLL."Credit_Amt" else null END "Debit",
case when GLL."Pmt_Num" = 3 and "Inv_Type" = 12 then GLL."Credit_Amt" else null END "Debit Acc_Fee",
case when GLL."Pmt_Num" = 11 then GLL."Credit_Amt" else null END "Credit"
from "General_Ledger_Log" GLL
where GLL."Cln_Num" = :Client/*Client*/
and GLL."Trn_Date" >= :Date/*Date*/
and GLL."Pmt_Num" in (3,11)
and GLL."Trn_Source" = 'D'
order by GLL."Trn_Date"
//Export this to txt then convert to excel.
//Add Running balance column
//Add a carried forward balance on second line with balance carried forward you are reconciling from
//Add function to calculate running balance: =J2+G3+H3-I3
//Once all credits applied to owing
//Total of Left to Pay column should equal final running balance IF the date in your export has gone back far enough to include all owing bills.
//Carried forward balance + Total Debit + Total Debit Acc Fee - Credit should equal Running balance.
//Running balance should equal owing amount on client record.
