E9.2 Simulating Aged Debtors report R03B4201B

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 ?
 

Larry_Jones

Legendary Poster
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 ?
A different purpose but similar need I needed a way for non-JDE reports to show quick A/R Aging KPIs as well as top 20 debtors, etc.
Rather than try to duplicate all that logic in SQL or a reporting tool (there's a lot of logic) I modified the UBE to write the data into a F4403BAGE table .
1. Define/Create Table
2. In UBE at appropriate Level break insert/write the existing data into this custom table.

Done.
 

Pat Clarke

Member
A different purpose but similar need I needed a way for non-JDE reports to show quick A/R Aging KPIs as well as top 20 debtors, etc.
Rather than try to duplicate all that logic in SQL or a reporting tool (there's a lot of logic) I modified the UBE to write the data into a F4403BAGE table .
1. Define/Create Table
2. In UBE at appropriate Level break insert/write the existing data into this custom table.

Done.
Thanks Larry,
unfortunately I only have access to the database and not the JDE application , therefore I have to put the logic into SQL.

regards
Pat
 
Top