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.

Symptoms

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.

REPLICATION STEPS:

  1. Setup Customer Contract with Rate Based Contract Line
  2. 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
  3. Setup a Billing Rate Set for these 2 different source types.
  4. 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
  5. 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

 

 

 

Changes

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms