CA_LIMITS.UpdLMT2.LMT2UPDB update LMT_LAST_TXN_DT_B.CA_LMT_TXN incorrectly when multiple rows exist, And It has Performance issue When CA_LIMITS Is Run Alone With The Recalculate Totals
(Doc ID 1610365.1)
Last updated on SEPTEMBER 30, 2016
Applies to:PeopleSoft Enterprise FIN Contracts - Version 9.1 to 9.2 [Release 9]
Information in this document applies to any platform.
CA_LIMITS.UpdLMT2.LMT2UPDB step does not update rows in the CA_LMT_TXN table with the proper date (LMT_LAST_TXN_DT_B) if more than one row exists in CA_LMT_TXN. The step uses the max(TRANS_DT) value from related Billing transactions to populate LMT_LAST_TXN_DT_B date on the CA_LMT_TXN table. The step currently only uses CONTRACT_NUM and CONTRACT_LINE_NUM when updating the date. Expectation: It should also use TXN_LMT_TRANS_ID to link back to the corresponding RESOURCE_TYPE, RESOURCE_CATEGORY, or RESOURCE_SUB_CAT value(s) on the billing transactions.
- Setup Customer Contract with Rate Based Contract Line
- Rate Based Contract Line should have 2 different Transaction Limits Defined. One Transaction Limit (Identifier) should point to LABOR Source Type value. Second Transaction Limit (Identifier) should point to MATER Source Type value
- Setup a Billing Rate Set for these 2 different source types.
- Enter, process, and price ACT row for LABOR source type via Add Transactions in Project Costing. Use Transaction Date = 01/01/2013 on ACT row. Review the LMT_LAST_TXN_DT_B value on LABOR rows in the CA_LMT_TXN table - it updated with 01/01/2013
- Enter, process, and price ACT row via Add Transactions in Project Costing. Use Transaction Date = 04/01/2013 on ACT row. Review the LMT_LAST_TXN_DT_B value on both rows in the CA_LMT_TXN table for the Contract. Both will reference 04/01/2013 when you would expect the LABOR line to reference 01/01/2013 and MATER line to reference 04/01/2013
In addition CA_LIMITS.UpdLMT2.LMT2UPDB has performance issue when CA_LIMITS is run alone with the Recalculate Totals.
The biggest problem with the SQL is the one to many join between PS_CA_LMT2_TAO11 and PS_PROJ_RESOURCE.
call count cpu elapsed disk query current rows
Parse 1 0.00 0.00 0 0 0 0
Execute 1 4041.00 4351.23 133554 151311295 29755 14582
Fetch 0 0.00 0.00 0 0 0 0
total 2 4041.01 4351.24 133554 151311295 29755 14582
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!