My Oracle Support Banner

Calculation Performance Due to User Defined Query UDQ with Custom Value Set Query Used in Formula Expression (Doc ID 2619769.1)

Last updated on DECEMBER 19, 2019

Applies to:

Oracle Fusion Incentive Compensation Cloud Service - Version N/A and later
Generic (Platform Independent)

Symptoms

Review Calculation performance and the longest running jobs are from child Calculation Phase 2 ( CALCPHASE2 ) processes.
The long running sql query is from the User Defined Query ( UDQ ) used in the measure formula expression.

STEPS

1. Create Value Set
    Setup and Maintenance > Manage Incentive Value Sets
         Value Set Code = CHECK_MOBILE_ACT
         Module = Incentive Compensation
         Value Type = Table
         Value Data Type = Number
         From Clause = cn_tp_transactions_all
         Value Column Name = 1
         WHERE Clause =
                     ATTRIBUTE81 = :1 /*Varchar2*/
              AND TRANSACTION_TYPE= 'ACT'
              AND ATTRIBUTE10 =:2 /*Varchar2*/
              AND OBJECT_STATUS = 'CREDITED'
              AND rownum < 2
2. Add to lookup code
    Setup and Maintenance > Manage Incentive Lookups
         Lookup Type = ORA_CN_USR_DEFN_EXPRSN_VLE_SET
         Lookup Code = CHECK_MOBILE_ACT
3. Create Expression
    Compensation Plans > Manage Expression > Create
          Rate Table Rate * Credit.Credit Amount * ( DECODE ( CHECK_MOBILE_ACT ( Credit.ID_CHIAVE_LINEA , 'LIS443' ) , 1 , 1 , 0 ) + DECODE ( CHECK_MOBILE_ACT ( Credit.ID_CHIAVE_LINEA , 'LIS431' ) , 1 , 1 , 0 ) + DECODE ( CHECK_MOBILE_ACT ( Credit.ID_CHIAVE_LINEA , 'LIS445' ) , 1 , 1 , 0 ) ) * TO_NUMBER ( DECODE ( NVL ( Measure.AT5 (Cluster) , 0 ) , 0 , 0 , 1 ) ) * CHOICE ( IS_EQUAL ( NVL ( CHECK_PIANI_ESCLUSI ( Credit.PIANO_TARIFFARIO , '20190111' , Credit.SOURCE_EVENT_DATE ) , 0 ) , 0 ) , 1 , 0 )
4.  Create Performance Measure and use the expression
5.  Add Performance Measure to plan component with Calculation Phase = 2
6.  Load volume transactions and Calculate
7.  Review calculation performance timing

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution


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