Expression With UDQ Invalid With ORA-00904: "SUM_VALUE_SET": Invalid Identifier -Failure Message (Doc ID 2929337.1)

Last updated on FEBRUARY 19, 2023

Applies to:

Symptoms

Expression with UDQ is invalid

ERROR

ORA-00904: "SUM_VALUE_SET": invalid identifier -Failure


STEPS
1. Create UDQ / Value Set via Setup and Maintenance > Manage Incentive Value Sets
   Value Set Code = TEST_GET_EARN_BY_DATE
   Module = Incentive Compensation
   Value Type = Table
   Value Data Type = Number
   From Clause = cn_tp_measure_results_all ctmra ,CN_FORMULAS_ALL_VL cfav
   Value Column Name = ctmra.OUTPUT_ACHIEVED
  WHERE Clause =
             1=1
            /*Parameter 1 (Participant) Participant ID*/
       and ctmra.credited_participant_id = :1 /*NUMBER*/
           /*Parameter 2 (Participant) SRP Comp Plan ID*/
       and ctmra.SRP_COMP_PLAN_ID = :2 /*NUMBER*/
            /*Parameter 3 (Credit) Credit Date*/
       and ctmra.source_event_date < :3 /*DATE*/
            /*Parameter 4 (Credit) Org ID*/
       and ctmra.org_id = :4 /*NUMBER*/
            /*Parameter 5 (Constant) Formula Name*/
       and cfav.formula_name = :5 /*VARCHAR2*/
       and cfav.formula_id = ctmra.formula_id

2. Add to lookup code
  Lookup Code: TEST_GET_EARN_BY_DATE
  Lookup Meaning: Test Get Earnings by Date

3. Create Expression
    Name: Test Get Earnings by Date
    Expression Detail :
SUM_VALUE_SET ( CHOICE ( IS_NULL ( Test Get Earnings by Date ( Participant.Participant ID (Metric) , Participant.SRP Comp Plan ID (Metric) , Credit.Source Event Date , Credit.Org ID , 'FY23 BK Calc Agent Level Earn LA West 230210' ) ) , 0 , Test Get Earnings by Date ( Participant.Participant ID (Metric) , Participant.SRP Comp Plan ID (Metric) , Credit.Source Event Date , Credit.Org ID , 'FY23 BK Calc Agent Level Earn LA West 230210' ) ) )

Cause

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