EAR 9.2: PS_PG_PENDST_TAO Temporary Table Instances 1, 2, 3, And 4 Are Not Properly Cleaned Up After AR Update Completes Via Batch or Online (Doc ID 2484183.1)

Last updated on JUNE 20, 2021

Applies to:

Symptoms

ISSUE:

The existing Temporary Table PS_PG_PENDST_TAO (Pending Item Distr w/o CF) includes 4 delivered instances:

    - PS_PG_PENDST_TAO1
    - PS_PG_PENDST_TAO2
    - PS_PG_PENDST_TAO3
    - PS_PG_PENDST_TAO4

While the first 3 instances are being used by AR_PGG_SERV AE Program when executed at the Accounting Entries tab (click on the displayed lightning bolt) when creating a new Pending Item Group transaction, the 4th one is being used by AR_UPDATE AE Program when triggered in Batch Mode.

Having said this, it has been detected that instances 1, 2, and 3 are never cleaned up after the Accounting Entries are properly generated online. The transaction data is kept stored, and increased with every new Pending Item getting its Accounting Entries generated online. With the pass of time, these 3 instances can eventually accumulate millions upon millions of unnecessary transaction rows.

Something slightly different is designed for the 4th instance (PS_PG_PENDST_TAO4). While it gets fully purged at the beginning of the AR Update execution, once it finishes, all the Accounting Entry rows generated by that run on all handled Groups will be left over in this Temporary Instance, waiting to be purged on the next AR Update run. This seems also not to follow any logic either.

REPLICATION STEPS:

    1.- Log into the FSCM Online Application as a Receivables User
    2.- Navigate to: Accounts Receivable > Pending Items > Online Items > Group Entry
    3.- Define a new Group, with one Item, and fill in all required fields to ensure it is properly balanced (Business Unit, Customer ID, Amount, etc...)
    4.- Go to the Accounting Entries tab
    5.- Click on the Lightning Bolt icon to automatically generate the needed Accounting Entries
    6.- Query Temporary Records PS_PG_PENDST_TAO1, PS_PG_PENDST_TAO2, and PS_PG_PENDST_TAO3, to confirm that the Accounting Entry rows of this new Group ID and Item are present in at least one of them
    7.- Set the Pending Item Group into a Post Action of Batch Standard
    8.- Navigate to: Accounts Receivable > Receivables Update > Request Receivables Update
    9.- Define a new Run Control ID for the Business Unit, and Accounting Date range, selecting the 'Pending Items' flag
    10.- Launch the AE Program AR_UPDATE to completion
    11.- Query Temporary Table PS_PG_PENDST_TAO4 and confirm that the Item's Accounting Entries are present in this table
    12.- Query Temporary Records PS_PG_PENDST_TAO1, PS_PG_PENDST_TAO2, and PS_PG_PENDST_TAO3, to confirm that the Accounting Entry rows of this Group ID and Item are still present in at least one of them
    13.- Navigate to: Accounts Receivable > Pending Items > Online Items > Group Entry
    14.- Define a second new Group, with one Item, and fill in all required fields to ensure it is properly balanced (Business Unit, Customer ID, Amount, etc...)
    15.- Go to the Accounting Entries tab
    16.- Click on the Lightning Bolt icon to automatically generate the needed Accounting Entries
    17.- Query Temporary Records PS_PG_PENDST_TAO1, PS_PG_PENDST_TAO2, and PS_PG_PENDST_TAO3, to confirm that the Accounting Entry rows of both the first, and now second Group IDs are present in at least one of them
    18.- Set this second Pending Item Group into a Post Action of Batch Standard
    19.- Navigate to: Accounts Receivable > Receivables Update > Request Receivables Update
    20.- Define a new Run Control ID for the Business Unit, and Accounting Date range, selecting the 'Pending Items' flag
    21.- Launch the AE Program AR_UPDATE to completion
    22.- Query Temporary Table PS_PG_PENDST_TAO4, and confirm that only the second Item's Accounting Entries are present in this table, while the previous Item's rows have been cleared
    23.- Query Temporary Records PS_PG_PENDST_TAO1, PS_PG_PENDST_TAO2, and PS_PG_PENDST_TAO3, to confirm that the Accounting Entry rows of both Group IDs are still present in at least one of them (They are never being cleared up)

To gather more information concerning this scenario and its related problem, refer to the available Replication Steps Word Document here linked containing the complete configuration and the replication steps necessary to reproduce the issue.

ACTUAL RESULT:

Over the years, the Temporary Table Instances 1 through 3 from Record PS_PG_PENDST_TAO have accumulated millions of rows, and is affecting the performance, as well as storage capacity of the database.

EXPECTED BEHAVIOR:

As with any Temporary Table, Instances 1 through 4 from Record PS_PG_PENDST_TAO should be cleared once all processes completed using such data (being it AR_UPDATE in Batch Mode, or AR_PGG_SERV via Online execution).

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.