Earnings in Custom Analysis Showing Duplicated for Some Participants (Doc ID 2531941.1)

Last updated on MARCH 12, 2021

Applies to:

Symptoms

On : 11.13.18.10.0 version, Analytics

ACTUAL BEHAVIOR
---------------
Custom report showing double values on Earning Amount

EXPECTED BEHAVIOR
-----------------------
Expected to see what is showing in UI

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Built a custom report
2. Results for some participants shows double

Customer noticed that if the participant has 1 transaction only, value shows ok. But if there are more translations, it seems that the value is doing times x transactions

CUSTOM Analysis:

SELECT
  0 s_0,
  "Incentive Compensation - Earnings Real Time"."Plan Component"."Display Name" s_1,
  "Incentive Compensation - Participant Balances Real Time"."- Compensation Plan"."Compensation Plan Name" s_2,
  "Incentive Compensation - Participant Balances Real Time"."- Plan Component"."Plan Component Name" s_3,
  "Incentive Compensation - Participant Balances Real Time"."Participant"."CN_PARTICIPANTS_DFF_CLAVE_AGENTE_" s_4,
  "Incentive Compensation - Participant Balances Real Time"."Participant"."CN_PARTICIPANTS_DFF_CLAVE_SUPERVISOR_" s_5,
  "Incentive Compensation - Participant Balances Real Time"."Participant"."CN_PARTICIPANTS_DFF_EJECUTIVO_" s_6,
  "Incentive Compensation - Participant Balances Real Time"."Participant"."CN_PARTICIPANTS_DFF_FECHA_INGRESO_" s_7,
  "Incentive Compensation - Participant Balances Real Time"."Participant"."CN_PARTICIPANTS_DFF_OFICINA_" s_8,
  "Incentive Compensation - Participant Balances Real Time"."Participant"."CN_PARTICIPANTS_DFF_SUPERVISOR_" s_9,
  "Incentive Compensation - Participant Balances Real Time"."Participant"."CN_PARTICIPANTS_DFF_ZONA_" s_10,
  "Incentive Compensation - Participant Balances Real Time"."Participant"."Participant ID" s_11,
  "Incentive Compensation - Participant Balances Real Time"."Participant"."Participant Name" s_12,
  "Incentive Compensation - Participant Balances Real Time"."- Plan Component Period Balances"."Balance2 Debit To Date" s_13,
  "Incentive Compensation - Participant Balances Real Time"."- Plan Component Year To Date Balances"."YTD Earnings" s_14,
  "Incentive Compensation - Participant Balances Real Time"."- Plan Component Year To Date Balances"."YTD Earnings"/"Incentive Compensation - Participant Balances Real Time"."- Plan Component Year To Date Balances"."Target Incentive" s_15,
  REPORT_SUM("Incentive Compensation - Participant Balances Real Time"."- Plan Component Period Balances"."Balance2 Debit To Date" BY "Incentive Compensation - Participant Balances Real Time"."- Compensation Plan"."Compensation Plan Name","Incentive Compensation - Participant Balances Real Time"."- Plan Component"."Plan Component Name","Incentive Compensation - Earnings Real Time"."Plan Component"."Display Name","Incentive Compensation - Participant Balances Real Time"."Participant"."Participant Name","Incentive Compensation - Participant Balances Real Time"."Participant"."CN_PARTICIPANTS_DFF_ZONA_","Incentive Compensation - Participant Balances Real Time"."Participant"."CN_PARTICIPANTS_DFF_OFICINA_","Incentive Compensation - Participant Balances Real Time"."Participant"."CN_PARTICIPANTS_DFF_EJECUTIVO_","Incentive Compensation - Participant Balances Real Time"."Participant"."CN_PARTICIPANTS_DFF_CLAVE_AGENTE_","Incentive Compensation - Participant Balances Real Time"."Participant"."CN_PARTICIPANTS_DFF_FECHA_INGRESO_","Incentive Compensation - Participant Balances Real Time"."Participant"."CN_PARTICIPANTS_DFF_SUPERVISOR_","Incentive Compensation - Participant Balances Real Time"."Participant"."CN_PARTICIPANTS_DFF_CLAVE_SUPERVISOR_") s_16
FROM "Incentive Compensation - Participant Balances Real Time"
WHERE
(("Calendar"."Period Year" = 2019) AND (DESCRIPTOR_IDOF("Incentive Compensation - Participant Balances Real Time"."Calendar"."Period Name") = 2019001)
AND ("Participant"."Participant Name" = 'PARTICIPANT_NAME')
AND ("Incentive Compensation - Earnings Real Time"."Plan Component"."Display Name" IN ('A) Value1', 'B) Value2', 'C) Value3')) AND ("Incentive Compensation - Earnings Real Time"."Compensation Plan"."Compensation Plan Name" = 'COMP Plan'))

BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot see correct amounts

Cause

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