How to customize the Account Analysis Report (XLAAARPT) for AR Aged Trial Balance Report ?

How to customize the Account Analysis Report (XLAAARPT) for AR Aged Trial Balance Report ?

Introduction : 

       When we were doing an R12 re-implementation from release 11.5.10, we thought of migrating the custom 11i reports to R12, by just changing the outdated tables and the policy context. Hoping that, this should be enough for getting the desired results.Then, when we actually test the result with 11i, they were not matching. 

Finally we decided to take the standard R12 Account Analysis Report and customize it.

AR Aged Trial Balance Report : 

Now, why would we need the AR Aged Trial Balance Report ? How will this be used by a business user ?

Sample AR Trial Balance Report

Above is a sample report, if we see this report, we can ascertain, how many customers have outstanding payment and how long they have not paid. In other words, it represents the number of days of average charges that are yet to be collected from each customer. Not only that, it can also give us the summary based on Customers, Accounts and Ledger.

Account Analysis Report(XLAAARPT) : 

The standard Account Analysis Report(XLAAARPT), has got the enough information for us to customize the report to AR Aged Trial Balance Report.

• The report is an XML publisher report with Data template and RTF template.
• The data template has a call to XLA_ACCT_ANALYSIS_RPT_PKG, which will be used to construct the xml data file.
• We need to rename all the relevant procedure calls to custom procedure calls names.

Steps:

1. Copy the existing Account Analysis Report(XLAAARPT) to a custom program name , say XXXXLAAARPT.
   
2. Switch Responsibility to XML Publisher Administrator -->Home-->Data Definition -->Query for XLAAARPT.
   
3. Create a new Data Definition similar to XLAAARPT, say XXXXLAAARPT.
   
4. Similarly create a new template for Data definition: Account Analysis Report, say XX Account Analysis Report.
5. Now for getting the aging buckets, we need the  ar_payment_schedules_all.DUE_DATE, this needs to be included in the xml data template. 

To include this column, we need to customize the XLA_ACCT_ANALYSIS_RPT_PKG. The variable C_TB_SOURCE_SQL holds the SQL query. 

Append in the select query the following :

     ,(select due_date from ar_payment_schedules_all where trx_number =tbg.transaction_number) DUE_DATE     
     , CASE 
          WHEN ROUND((sysdate - (select due_date from ar_payment_schedules_all where trx_number =tbg.transaction_number))/30)<= 0 THEN tbg.entered_rounded_orig_amount
          ELSE 0 
       END BUCKET0
     , CASE 
          WHEN ROUND((sysdate - (select due_date from ar_payment_schedules_all where trx_number =tbg.transaction_number))/30)  = 1 THEN tbg.entered_rounded_orig_amount
          ELSE 0 
       END BUCKET1
     , CASE 
          WHEN ROUND((sysdate - (select due_date from ar_payment_schedules_all where trx_number =tbg.transaction_number))/30)  = 2 THEN tbg.entered_rounded_orig_amount
          ELSE 0 
       END BUCKET2
     , CASE 
          WHEN ROUND((sysdate - (select due_date from ar_payment_schedules_all where trx_number =tbg.transaction_number))/30)  = 3 THEN tbg.entered_rounded_orig_amount
          ELSE 0 
       END BUCKET3
     , CASE 
          WHEN ROUND((sysdate - (select due_date from ar_payment_schedules_all where trx_number =tbg.transaction_number))/30)  = 4 THEN tbg.entered_rounded_orig_amount
          ELSE 0 
       END BUCKET4
     , CASE 
          WHEN ROUND((sysdate - (select due_date from ar_payment_schedules_all where trx_number =tbg.transaction_number))/30) = 5 THEN tbg.entered_rounded_orig_amount
          ELSE 0 
       END BUCKET5
     , CASE 
          WHEN ROUND((sysdate - (select due_date from ar_payment_schedules_all where trx_number =tbg.transaction_number))/30) >= 6 THEN tbg.entered_rounded_orig_amount
          ELSE 0                

       END BUCKET6 

The new column names need to be included in the XML data template as well.

Customize the rtf, by including the bucket range with the corresponding total.

AP Aged Trial Balance Report : 

Now, why would we need the AP Aged Trial Balance Report ? How will this be used by a business user ?

Similar to the AR Aged Trial Balance Report, we need to know how much we owe to our Suppliers. We need to know, to which supplier, we are supposed to pay for more than 100 or 180 days. 

Not only that,the standard report can also give the outstanding based on Supplier, Account and Ledger.

Open Account AP Balances Listing(XLAAPRPT) : 

Similar to AR, the standard report Open Account AP Balances Listing, has rich information, enough to create the AP Aged Trial Balance Report. We need to create custom program keeping the Open Account AP Balances Listing as a baseline.

Customizing Open Account AP Balances Listing :

To customize just follow the steps we did for AR. Finally fill the customized RTF template with the bucket information as below

Template for AP Aged Trial Balance Report

I will upload the code shortly, which will be a plug and play :)

Conclusion: 

Aging Reports in R12, is no longer a complex development, if we use the XML publisher, instead of the conventional way of developing in RDF. 

Cheers and Happy Coding :) !!!