Rebuild AP Trial Balance in R12

Rebuild AP Trial Balance in R12

To rebuild xla_trial_balances table or create a new report definition. 

For better performance of Data Manager run, increase the number of workers to 16,32 or 64 

Navigate : Subledger Accounting Setup > Open Account Balances Listing Definitions > Process Options button and for the ledger update the number of processors to 16,32 or 64. 

The original/upgraded report definition can also be rebuilt. However, for the purposes of testing and comparison, this note rebuilds a new/copied definition. If the rebuild of the new/copied report definition worked, the rebuild of the original/upgraded report definition should work also. Make sure whatever option is selected to be used for PROD, that option is tested first! 

An upgraded/original report definition can be rebuilt in following ways: 

a) Enable/disable report definition. 
b) Open report definition and merely click on apply button.This will repopulate xla_trial_balance table. 

And a new/copied definition can be rebuilt in following ways. 

a) Enable/disable report definition. 
b) Open report definition and merely click on apply button. This will repopulate xla_trial_balance table. 
c) Add/remove accounts. 



To Create a new report definition : 

Navigate: Payables Responsibility > Setup > Accounting Setups > Subledger Accounting 
Setups > Open Account Balances Listing Definitions 

Query the Report Definition Code 

Click on Duplicate to Copy the definition. 

Enter name and code for Copy and Apply the changes. 

Reset the number of workers.

Navigate: Sub Ledger Accounting Setup > Open Account Balances Listing Definitions > Process Options button and reset the number of processors back to 1 for the ledger.

Once the above is done.. Revisit the calculations and ensure Payables Activity are reconciled.

IMPORTANT:  Rebuilding Trial Balance definition data deletes all prior data for that definition and does not back that data up.  It rebuilds the data based on the current data in xla_ae_lines and the current report definition setup.  The data could be significantly different after the rebuild and finding the differences difficult, since the original data is deleted.  It is strongly recommended that all rebuild attempts be completed and verified on a TEST instance first.  And for testing purposes, you should also consider creating a copy of the definition.  It is also important to note that making any change to a report definition will cause it to be rebuilt.  Please make sure you review all of the steps in this rebuild section before you attempt to change/rebuild Trial Balance report definition data.

Performance Note: The steps below include instructions to minimize the chance of performance issues during the rebuild. If you follow all the steps while testing the rebuild and still encounter performance issues. Or you know you have large volumes of data and suspect performance issues, depending on the reason you are doing the rebuild, one of the following options may be a better option for you:
1. If you are adding a new liability account to be included in the definition, you may want to consider running the “Accounts Payables Trial Balance Rebuild” concurrent program. For more details on that program, please review Doc ID 1463703.1.
2. If you need to rebuild due to incorrect/corrupt data, review Doc Id 1947736.1 for an option to rebuild the report definition data for specific event_ids.

Verify the data was rebuilt: Run the following query to verify the data was rebuilt.  The rebuilt definition code data should have a minimum creation date equal to the date the data was rebuilt

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT xtb.definition_code,
        xeh.ledger_id,
        decode(xeh.upg_batch_id,null,'NOT UPGRADED', 'UPGRADED') Upgraded,
        min(xtb.creation_date),
       count(*)
 FROM xla_trial_balances xtb, xla_ae_headers xeh
 WHERE xtb.ae_header_id = xeh.ae_header_id (+)
 GROUP BY xtb.definition_code, xeh.ledger_id,
          decode(xeh.upg_batch_id, null,'NOT UPGRADED','UPGRADED')
 ORDER BY xeh.ledger_id, xtb.definition_code;

Ref: Doc ID 758531.1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s