Pat Clarke
Member
I need to simulate the Aged Debtors report (R03B4201B) with an "AS OF" Date via SQL to ensure that the debtor open amounts remain the same before and after archiving. In the SQL below I am using an as of date of 31st Dec 2018 (118365).
select rpan8,rpdct,rpdoc,rpkco,rpag as "Original Amt" ,sum(rzpaap+(rzadsa+rzaaaj+rzecba)) as "Open Amount",count(*)
from proddta.F03B11 left outer join proddta.F03B14 on RPKCO = RZKCO AND RPDCT = RZDCT AND RPDOC = RZDOC AND RPSFX = RZSFX
where rpdgj<=118365 and rpjcl>118365 group by rpkco,rpan8,rpdct,rpdoc,rpkco,rpag order by rpkco,rpan8,rpdoc
However I am not sure about how to calculate the "Open Amount". I don't care about aging buckets , just the open amounts as of a specific date. Anyone out there with knowledge of how open amount should be calculated ?
select rpan8,rpdct,rpdoc,rpkco,rpag as "Original Amt" ,sum(rzpaap+(rzadsa+rzaaaj+rzecba)) as "Open Amount",count(*)
from proddta.F03B11 left outer join proddta.F03B14 on RPKCO = RZKCO AND RPDCT = RZDCT AND RPDOC = RZDOC AND RPSFX = RZSFX
where rpdgj<=118365 and rpjcl>118365 group by rpkco,rpan8,rpdct,rpdoc,rpkco,rpag order by rpkco,rpan8,rpdoc
However I am not sure about how to calculate the "Open Amount". I don't care about aging buckets , just the open amounts as of a specific date. Anyone out there with knowledge of how open amount should be calculated ?