SQL & PL/SQL

I am sorry this is long but I am trying to be detailed!

I am trying to help one of my users reconcile AR for Banking purposes. Oracle offers a very nice way to do this with the AR Reconciliation Report (ARXRECON.rdf). The problem we have is that we need to differentiate from Domestic and Export customers as the bank considers Export transactions as “High risk” and will not allow those transactions to be considered as a factor in our ability to borrow.

This report does not do this. The report is based off of built in procedures and thus I can’t/don’t know how to modify. The solution I have devised is to reproduce the report using queries as Oracle as detailed how the report works (below). The first step is to come up with the exact numbers Oracle details in the report and then once I have the SQL to do that I can make modifications to only pick up Domestic or Export customers.

***Quote***
THE AR RECONCILAITION REPORT
-----------------------------

The AR Reconciliation Report shows the summary amounts of various AR
reports, that are used to reconcile your AR.

AR is reconciled when the "Aging on the beginning of the Period" + "The
Total Activity in the Period" is equal to the "Aging on the end of the
Period".

The Total Activity in a period is calculated as follows:

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.
***End Quote***

I have had no issues in writing SQL statements for the Items which make up the period activity. However I cannot seem to get the “Beginning” / “Ending” Balances.

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_TO
Select 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_FROM
Select 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!