Transaction Register for the Period
Applied Receipts Register for the Period
Un-Applied Receipts Register for the Period
Adjustments Register for the Period
Invoice Exceptions for the Period
Rounding Differences for the Period (*)
Credit Memo gain/loss for the Period(*)
(*) These can occur only in the case of foreign currency transactions.
The documentation details the following on how they derive the beginning and ending balances is here:
***--Quote--***
Beginning and Ending Balance:
To get the beginning and ending balances the report uses the same procedure. The As of Date for the Beginning period is GL Date Low-1 where as the As Of date for the Ending balance is GL Date High.
The report selects all transactions from AR_PAYMENTS_SCHEDULES_ALL table which are not receipts and have a GL Date equal to or less than the As Of date, AND a GL Date Closed which is greater than the As Of date.
For all records returned which are not credit memos, the report calculates and sums up all the receipt and credit memo applications which have a GL date of greater than the As Of date from the AR_RECEIVABLE_APPLICATIONS_ALL table. This total is termed as Accounted_Amount_Applied_To.
For all credit memos, the report calculates and sums up all the applications which have a GL date of greater than the AS Of date from the AR_RECEIVABLE_APPLICATIONS_ALL table. This is called the Accounted_Amount_Applied_From.
Any approved adjustments tied to these transactions are also calculated
and summed up that have a GL date of greater than the As Of date.
The report also sums up the Accounted Amount Due Remaining for all
transactions.
The final value the report calculates to get the Beginning/Ending Balance is the sum of all Unapplied, On-Account and Unidentified Receipts which were closed after the As Of date.
The Beginning and Ending balance is calculated using the following
equation:
Beginning/Ending Amount = Sum of all Accounted Amount Due Remaining
+ sum of Accounted_Amount_Applied_To
- sum of Accounted_Amount_Applied_From
- sum of all approved adjustements
+ ( - sum of all unapplied receipts)
***--End Quote--***
I am a novice when it comes to understanding the database structure of Oracle 11i and the above details are a little spotty on where and what fields are being summed up. So far I have written these (again note that I am trying to tie to the report…adding the functionality to select Domestic verses Export will come later and is not an issue) SQL statements which should give me an individual number for each part for which I can do calculations with to get the actual balance:
--
AR Beginning Balance Queries --Sum ACCTD_AMOUNT_DUE_REMAINING
Select Sum(AR_PAYMENT_SCHEDULES_ALL.ACCTD_AMOUNT_DUE_REMAINING) as ACCTD_AMOUNT_DUE_REMAINING
From AR_PAYMENT_SCHEDULES_ALL
Where AR_PAYMENT_SCHEDULES_ALL.GL_DATE <= :date1
and AR_PAYMENT_SCHEDULES_ALL.GL_DATE_CLOSED > :date1
and AR_PAYMENT_SCHEDULES_ALL.org_id = :org
;
--
Sum ACCTD_AMOUNT_APPLIED_TOSelect Sum(AR_RECEIVABLE_APPLICATIONS_ALL.ACCTD_AMOUNT_APPLIED_TO) as ACCTD_AMOUNT_APPLIED_TO
From AR_RECEIVABLE_APPLICATIONS_ALL
Where AR_RECEIVABLE_APPLICATIONS_ALL.GL_DATE > :date1
and AR_RECEIVABLE_APPLICATIONS_ALL.APPLICATION_TYPE 'CM'
and org_id = :org
;
--
Sum ACCT_AMOUNT_APPLIED_FROMSelect Sum(AR_RECEIVABLE_APPLICATIONS_ALL.ACCTD_AMOUNT_APPLIED_FROM) as ACCTD_AMOUNT_APPLIED_FROM
From AR_RECEIVABLE_APPLICATIONS_ALL
Where AR_RECEIVABLE_APPLICATIONS_ALL.GL_DATE > :date1
and AR_RECEIVABLE_APPLICATIONS_ALL.APPLICATION_TYPE = 'CM'
and org_id = :org
;
--
AR Beginning Balance Queries (End) I am not sure how to calculate the “approved adjustments” or what they consider “unapplied receipts” can someone please help me? Also if you believe my above queries aren't pulling the correct totals please elaborate on that as well.
Thanks in advance!
UPDATE:
I think that I have figured out the “approved adjustments” & the “unapplied receipts” as the balances I am getting with those queires (below) are the same balances as the "Adjustment Register" and the "Unapplied Receipts Register" so that leads me to think one of the other queries I have above is wrong...can some one help me with that...pretty please!
--
Adjustment Register Subtotal
Select Sum(AR_ADJUSTMENTS_ALL.ACCTD_AMOUNT) as Adjustment_Register_Subtotal
From AR_ADJUSTMENTS_ALL
Where AR_ADJUSTMENTS_ALL.GL_DATE between :date1 and :date2
and org_id = :org
;
--
Unapplied Receipts Register Subtotal
Select Sum(AR_RECEIVABLE_APPLICATIONS_ALL.ACCTD_AMOUNT_APPLIED_FROM) as Unapplied_Rec_Reg_Subtotal
From AR_RECEIVABLE_APPLICATIONS_ALL
Where AR_RECEIVABLE_APPLICATIONS_ALL.GL_DATE between :date1 and :date2
and AR_RECEIVABLE_APPLICATIONS_ALL.STATUS in ('ACC', 'UNAPP', 'UNID')
and AR_RECEIVABLE_APPLICATIONS_ALL.APPLICATION_TYPE 'CM'
and org_id = :org
