Description:
POST TO WIP: A query in the Stored Procedure PAC_PRC_POSTTOWIPS_SP performs full table scan so take a long to execute when there are many transactions. The query that takes most of the time is in 'PAC_PRC_POSTTOWIPS_SP' stored procedure. For about 1,870 records it take about 11 minutes to post.
Steps to Reproduce:
- Create about 500,000 Financial transactions so they are ready to be Posted to WIP
- Navigate to Post To WIP page
- Using Date filters, filter for say 1,870 transactions
- Click on 'Post' button to move these transaction to PPA_WIP and PPA_WIP_VALUES table
Expected Result: Expect transactions to get posted under 4 minutes.
Actual Result: It takes about 11 minutes for the Post To WIP procedure to complete.
Solution:
WORKAROUND:
Contact CA Technical Support for review and possible workaround in v13 or upgrade to v14.1.
When you open a case please provide the following for analysis so that we can confirm if you are running into this issue:
- Full Background Log files.
- Any Database Logs showing slow performing queries.
- Does it show a Full Table Scan is occurring?
- If using Oracle, get an AWR.
- Outcomes of trying to run the job in smaller batches as identified in the steps above.
- When processing the smaller batches what are the timing statistics?
- Provide a copy of the database stored procedure named 'PAC_PRC_POSTTOWIPS_SP' from your database schema.
- Check if there is an existing index definition for PPA_TRANSCONTROLAPINFO_ID and provide the details from your database schema.
STATUS/RESOLUTION:
CLRT-73754
Resolved in CA PPM 14.1