Calculation Performance Due to User Defined Query UDQ with Custom Value Set Query Used in Formula Expression
(Doc ID 2619769.1)
Last updated on MAY 20, 2022
Applies to:
Oracle Fusion Incentive Compensation Cloud Service - Version N/A and laterGeneric (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 |