Full query

SELECT rules.*, -- BBAction_c DECODE(Rule_Number, 1, 'MAV_DIR_BB_ACTION_1000', 2, 'MAV_DIR_BB_ACTION_1003', 3, 'MAV_DIR_BB_ACTION_1001', 4, 'MAV_DIR_BB_ACTION_1003', 5, 'MAV_DIR_BB_ACTION_1003', 6, 'MAV_DIR_BB_ACTION_1003', 7, 'MAV_DIR_BB_ACTION_1003', 8, 'MAV_DIR_BB_ACTION_1003', 9, 'MAV_DIR_BB_ACTION_1003', 10, 'MAV_DIR_BB_ACTION_1003', 99, 'MAV_DIR_BB_ACTION_1002', 11, 'MAV_DIR_BB_ACTION_1002', NULL ) AS "BBAction_c", -- BBReasonCode_c DECODE(Rule_Number, 1, 'MVA_DIR_BB_REASON_1018', 2, 'MVA_DIR_BB_REASON_1001', 3, 'MVA_DIR_BB_REASON_1018', 4, 'MVA_DIR_BB_REASON_1001', 5, 'MVA_DIR_BB_REASON_1001', 6, 'MVA_DIR_BB_REASON_1001', 7, 'MVA_DIR_BB_REASON_1001', 8, 'MVA_DIR_BB_REASON_1001', 9, 'MVA_DIR_BB_REASON_1001', 10, 'MVA_DIR_BB_REASON_1001', 99, 'MVA_DIR_BB_REASON_1001', 11, 'MVA_DIR_BB_REASON_1027', NULL ) AS "BBReasonCode_c", -- BBMPAComment_c DECODE(Rule_Number, 1, 'Full BB is pre-approved by system as the rep is active sale rep within the defined threshold limits (BB % HR TI ≥ -5% and ≤ 5%). Pre-approved not applicable for Austria country.', 2, 'Full CED put on Hold by system as the rep is on LOA with positive or negative CED amount. PENDING analyst to review and action.', 3, 'Full CED is pre-approved by system as the rep is terminated within the defined threshold limits of CED amount (CED > 0 and ≤ 10 USD). Pre-approved not applicable for Austria country.', 4, 'Full CED put on Hold by system as the rep is terminated within the defined threshold limits of CED amount (CED > 0 and ≤ 10 USD). PENDING analyst to review and action.', 5, 'Full CED put on Hold by system as the rep is terminated within the defined threshold limits of CED amount (CED > 10 USD). PENDING analyst to review and action.', 6, 'Full CED put on Hold by system as the rep is terminated with negative CED amount. PENDING analyst to review and action. Payroll collection not applicable for Romania country.', 7, 'Full CED put on Hold by system as the rep is terminated with negative CED amount. PENDING analyst to review and action. Consider Payroll collection, if negative CED amount is final.', 8, 'Full CED put on Hold by system as the rep moved to non-sales role with positive CED amount. PENDING analyst to review and action.', 9, 'Full CED put on Hold by system as the rep moved to non-sales role with negative CED amount. PENDING analyst to review and action. Payroll collection not applicable for Romania country.', 10, 'Full CED put on Hold by system as the rep moved to non-sales role with negative CED amount. PENDING analyst to review and action. Consider Payroll collection if negative CED amount is final. Rep''s consent NEEDS to be attached for such case.', 11, 'Zero BB is pre-approved by system for active sale rep', 99, 'Full BB put on Hold by system as the rep is active sale rep OVER the defined pre-approved BB threshold limits (BB % HR TI ≥ -5% and ≤ 5%). PENDING analyst to review and action.', NULL ) AS "BBMPAComment_c", -- MPAReasonCode_c DECODE(Rule_Number, 1, NULL, 3, NULL, 'MAV_DIR_MPA_CODE_1008' ) AS "MPAReasonCode_c", -- MPAGenerated_c DECODE(Rule_Number, 1, NULL, 2, (-1) * CED_c, 3, NULL, 4, (-1) * CED_c, 5, (-1) * CED_c, 6, (-1) * CED_c, 7, (-1) * CED_c, 8, (-1) * CED_c, 9, (-1) * CED_c, 10, (-1) * CED_c, 99, (-1) * BeginningBalance_c, NULL ) AS "MPAGenerated_c", -- ExpectedPaymentAmount_c DECODE(Rule_Number, 1, NULL, 3, NULL, 99, TO_NUMBER(RULES.PTDEarnings_c), 0 ) AS "ExpectedPaymentAmount_c", -- MPAStatus_c DECODE(Rule_Number, 1, NULL, 3, NULL, 'Auto approved' ) AS "MPAStatus_c", -- RecoverableFlagV1_c DECODE(Rule_Number, 1, NULL, 3, NULL, 'Y' ) AS "RecoverableFlagV1_c" FROM (SELECT base.*, -- Evaluate complex conditions ONLY ONCE CASE WHEN base.HRStatus_c = 'A' AND base.EmploymentCountry_c != 'AT' AND UPPER(base.SalesRP_c) = 'Y' AND TO_NUMBER(base.HRTI_c) != 0 AND TO_NUMBER(base.BBOfHRTI_c) BETWEEN -5 AND 5 AND TO_NUMBER(base.BeginningBalance_c) != 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 1 WHEN base.HRStatus_c = 'L' AND UPPER(base.SalesRP_c) = 'Y' AND TO_NUMBER(base.CED_c) != 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 2 WHEN base.HRStatus_c = 'T' AND base.EmploymentCountry_c != 'AT' AND UPPER(base.SalesRP_c) = 'Y' AND TO_NUMBER(base.CEDUSD_c) BETWEEN 0 AND 10 AND base.IncentiveType_c = 'COMMISSION' THEN 3 WHEN base.HRStatus_c = 'T' AND UPPER(base.SalesRP_c) = 'Y' AND TO_NUMBER(base.CEDUSD_c) BETWEEN 0 AND 10 AND base.IncentiveType_c = 'BONUS' THEN 4 WHEN base.HRStatus_c = 'T' AND UPPER(base.SalesRP_c) = 'Y' AND TO_NUMBER(base.CEDUSD_c) > 10 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 5 WHEN base.HRStatus_c = 'T' AND base.EmploymentCountry_c = 'RO' AND UPPER(base.SalesRP_c) = 'Y' AND TO_NUMBER(base.CED_c) < 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 6 WHEN base.HRStatus_c = 'T' AND base.EmploymentCountry_c != 'RO' AND UPPER(base.SalesRP_c) = 'Y' AND TO_NUMBER(base.CED_c) < 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 7 WHEN base.HRStatus_c = 'A' AND (UPPER(base.SalesRP_c) = 'N' OR base.SalesRP_c IS NULL) AND TO_NUMBER(base.CED_c) > 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 8 WHEN base.HRStatus_c = 'A' AND base.EmploymentCountry_c = 'RO' AND (UPPER(base.SalesRP_c) = 'N' OR base.SalesRP_c IS NULL) AND TO_NUMBER(base.CED_c) < 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 9 WHEN base.HRStatus_c = 'A' AND base.EmploymentCountry_c != 'RO' AND (UPPER(base.SalesRP_c) = 'N' OR base.SalesRP_c IS NULL) AND TO_NUMBER(base.CED_c) < 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 10 WHEN base.HRStatus_c = 'A' AND (UPPER(base.SalesRP_c) = 'Y') AND TO_NUMBER(base.BeginningBalance_c) = 0 AND base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 11 WHEN base.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 99 ELSE 0 END AS Rule_Number FROM ( WITH -- Materialize conversion rates once CONVERSION_RATES_USD AS ( SELECT CR.FROM_CURRENCY, CR.TO_CURRENCY, CR.CONVERSION_START_DATE, CR.CONVERSION_END_DATE, CR.CONVERSION_RATE FROM CN_CONVERSION_RATES CR INNER JOIN CN_CONVERSION_TYPES_TL CT ON CT.CONVERSION_TYPE_ID = CR.CONVERSION_TYPE_ID WHERE CT.CONVERSION_TYPE_ID = '-1001' AND CT.LANGUAGE = 'US' AND CR.TO_CURRENCY = 'USD' ), -- Materialize person numbers once - FILTERED PERSON_NUMBERS AS ( SELECT DISTINCT cspl.participant_id, papf.person_number, cspl.PARTICIPANT_NAME FROM CN_SRP_PARTICIPANTS_ALL cspl INNER JOIN hz_person_profiles hpp ON cspl.party_id = hpp.party_id INNER JOIN PER_ALL_PEOPLE_F papf ON papf.person_id = hpp.hr_person_id WHERE cspl.participant_id = NVL(:participant_id, cspl.participant_id) ), -- Materialize participant details once - FILTERED PARTICIPANT_DETAILS_CACHE AS ( SELECT a.analyst_id, b.PARTICIPANT_ID, b.PARTICIPANT_DETAIL_ID, b.ORG_ID, b.START_DATE, b.END_DATE, b.COUNTRY_CODE, b.ATTRIBUTE_CHAR40, b.ATTRIBUTE_NUMBER1, b.CURRENCY_CODE, b.ATTRIBUTE_CHAR2, b.ATTRIBUTE_CHAR17 FROM CN_SRP_PARTICIPANTS_ALL a INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL b ON a.participant_id = b.participant_id WHERE b.PARTICIPANT_ID = NVL(:participant_id, b.PARTICIPANT_ID) ), -- Materialize multi-currency detection - FILTERED BB_MULTI_CURRENCY AS ( SELECT pay_1.PAYRUN_ID, pay_1.PAYMENT_BATCH_PERIOD, pay_1.PAYEE_PARTICIPANT_ID, 'Y' AS BBMultiCurrencyFlag FROM ( SELECT P.PAYRUN_ID, PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID, HOME_CURRENCY_CODE FROM CN_TP_PARTICIPANT_PAY_EXP_V P WHERE P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID) AND P.PAYEE_PARTICIPANT_ID = NVL(:participant_id, P.PAYEE_PARTICIPANT_ID) AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD) GROUP BY PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID, PAYRUN_ID, HOME_CURRENCY_CODE ) pay_1 INNER JOIN ( SELECT PAYRUN_ID, PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID FROM CN_TP_PARTICIPANT_PAY_EXP_V P WHERE PAY_PERIOD_ID <> PAYMENT_BATCH_PERIOD AND P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID) AND P.PAYEE_PARTICIPANT_ID = NVL(:participant_id, P.PAYEE_PARTICIPANT_ID) AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD) GROUP BY PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID, PAYRUN_ID ) pay_2 ON pay_1.PAYRUN_ID = pay_2.PAYRUN_ID AND pay_1.PAYMENT_BATCH_PERIOD = pay_2.PAYMENT_BATCH_PERIOD AND pay_1.PAYEE_PARTICIPANT_ID = pay_2.PAYEE_PARTICIPANT_ID GROUP BY pay_1.PAYRUN_ID, pay_1.PAYMENT_BATCH_PERIOD, pay_1.PAYEE_PARTICIPANT_ID HAVING COUNT(1) > 1 ), -- FILTERED PERIOD_MULTI_CURRENCY AS ( SELECT MULTI_CURR.PARTICIPANT_ID, MULTI_CURR.PAYRUN_ID, MULTI_CURR.ORG_ID, MULTI_CURR.PERIOD_ID, 'Y' AS MULTI_CURR_YN FROM ( SELECT PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID, PD.CURRENCY_CODE AS HOME_CURRENCY_CODE, PART_PAY.PAYRUN_ID, PART_PAY.ORG_ID, PART_PAY.PAYMENT_BATCH_PERIOD AS PERIOD_ID, SUM(ROUND(PART_PAY.COMM_AMT_CALC_CURR, 2)) AS TotalAmt FROM CN_TP_PARTICIPANT_PAY_EXP_V PART_PAY INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD ON PD.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID AND PART_PAY.SOURCE_EVENT_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, PART_PAY.SOURCE_EVENT_DATE) WHERE PART_PAY.SOURCE_EVENT_DATE IS NOT NULL AND PART_PAY.PAYRUN_ID = NVL(:payrun_id, PART_PAY.PAYRUN_ID) AND PART_PAY.CREDITED_PARTICIPANT_ID = NVL(:participant_id, PART_PAY.CREDITED_PARTICIPANT_ID) AND PART_PAY.PAYMENT_BATCH_PERIOD = NVL(:period_id, PART_PAY.PAYMENT_BATCH_PERIOD) GROUP BY PART_PAY.CREDITED_PARTICIPANT_ID, PD.CURRENCY_CODE, PART_PAY.PAYRUN_ID, PART_PAY.ORG_ID, PART_PAY.PAYMENT_BATCH_PERIOD HAVING SUM(ROUND(PART_PAY.COMM_AMT_CALC_CURR, 2)) <> 0 ) MULTI_CURR GROUP BY MULTI_CURR.PARTICIPANT_ID, MULTI_CURR.PAYRUN_ID, MULTI_CURR.ORG_ID, MULTI_CURR.PERIOD_ID HAVING COUNT(MULTI_CURR.HOME_CURRENCY_CODE) > 1 ), MULTI_CURRENCY_FLAGS AS ( SELECT NVL(bb_MultiC.PAYRUN_ID, PRD_ML.PAYRUN_ID) AS PAYRUN_ID, NVL(bb_MultiC.PAYMENT_BATCH_PERIOD, PRD_ML.PERIOD_ID) AS PERIOD_ID, NVL(bb_MultiC.PAYEE_PARTICIPANT_ID, PRD_ML.PARTICIPANT_ID) AS PARTICIPANT_ID, NVL(bb_MultiC.BBMultiCurrencyFlag, 'N') AS BBMultiCurrencyFlag, NVL(PRD_ML.MULTI_CURR_YN, 'N') AS MULTI_CURR_YN FROM BB_MULTI_CURRENCY bb_MultiC FULL OUTER JOIN PERIOD_MULTI_CURRENCY PRD_ML ON bb_MultiC.PAYEE_PARTICIPANT_ID = PRD_ML.PARTICIPANT_ID AND bb_MultiC.PAYRUN_ID = PRD_ML.PAYRUN_ID AND bb_MultiC.PAYMENT_BATCH_PERIOD = PRD_ML.PERIOD_ID WHERE NVL(PRD_ML.MULTI_CURR_YN, 'N') = 'Y' OR NVL(bb_MultiC.BBMultiCurrencyFlag, 'N') = 'Y' ), -- Materialize base payment data - FILTERED BASE_PAYMENTS AS ( SELECT P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAYRUN_NAME, PB.PAY_PERIOD_ID, PB.PAY_DATE, PG.PAY_GROUP_ID, PG.PAY_GROUP_NAME, P.PLAN_COMPONENT_ID, PC.INCENTIVE_TYPE, PS.PAYSHEET_ID, PS.BONUS_PTD, PS.PMT_TXN_AMT_DUE, ( SELECT meaning FROM cn_lookups WHERE lookup_type = 'CN_PAYSHEET_STATUS' AND lookup_code = PS.OBJECT_STATUS AND enabled_flag = 'Y' AND lookup_code != 'REVIEWED' ) AS PAYSHEET_STATUS, P.PAY_AMT_PAY_CURR, P.HOME_CURRENCY_CODE, P.PAY_CURRENCY_CODE, P.SOURCE_EVENT_DATE FROM CN_TP_PARTICIPANT_PAY_ALL P INNER JOIN CN_TP_PAYRUNS_ALL PB ON PB.PAYRUN_ID = P.PAYRUN_ID AND PB.ORG_ID = P.ORG_ID INNER JOIN CN_PAY_GROUPS_ALL_VL PG ON PG.PAY_GROUP_ID = PB.PAY_GROUP_ID AND PG.ORG_ID = P.ORG_ID INNER JOIN CN_PLAN_COMPONENTS_ALL_VL PC ON P.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD ON P.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID INNER JOIN CN_TP_PAYSHEETS_ALL PS ON PS.PARTICIPANT_ID = P.CREDITED_PARTICIPANT_ID AND PS.PAYRUN_ID = P.PAYRUN_ID AND PS.QUOTA_ID IS NULL WHERE ( (P.SOURCE_EVENT_DATE IS NOT NULL AND P.SOURCE_EVENT_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, P.SOURCE_EVENT_DATE)) OR (P.SOURCE_EVENT_DATE IS NULL AND PB.PAY_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, PB.PAY_DATE)) ) AND PB.PAYRUN_ID = NVL(:payrun_id, PB.PAYRUN_ID) AND P.CREDITED_PARTICIPANT_ID = NVL(:participant_id, P.CREDITED_PARTICIPANT_ID) AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID) ), -- Materialize participant detail for date range - FILTERED PARTICIPANT_DETAIL_DATERANGE AS ( SELECT PART_DTLS.PARTICIPANT_ID, PART_HDR.HR_PRIMARY_WORKER_NUMBER, PART_DTLS.PARTICIPANT_DETAIL_ID, PART_DTLS.ORG_ID AS PD_ORG_ID, PART_DTLS.START_DATE AS PD_START_DATE, PART_DTLS.END_DATE AS PD_END_DATE, PART_DTLS.COUNTRY_CODE AS HR_Country, PART_DTLS.COUNTRY_CODE AS Employment_Country, ( SELECT PARTY_ID FROM CN_SRP_PARTICIPANTS_ALL ANALYST WHERE ANALYST.ORG_ID = PART_HDR.ORG_ID AND ANALYST.PARTICIPANT_ID = PART_HDR.ANALYST_ID ) AS ANALYST_PARTY_ID, PART_DTLS.ATTRIBUTE_NUMBER1 AS PD_HR_TI, PART_DTLS.CURRENCY_CODE AS LATEST_HOME_CURRENCY, PART_DTLS.ATTRIBUTE_CHAR2 AS HR_Status, PART_DTLS.ATTRIBUTE_CHAR17 AS SalesRep FROM CN_SRP_PARTICIPANT_DETAILS_ALL PART_DTLS INNER JOIN CN_SRP_PARTICIPANTS_ALL PART_HDR ON PART_DTLS.PARTICIPANT_ID = PART_HDR.PARTICIPANT_ID AND PART_DTLS.ORG_ID = PART_HDR.ORG_ID WHERE PART_DTLS.PARTICIPANT_ID = NVL(:participant_id, PART_DTLS.PARTICIPANT_ID) ), -- Aggregate to USD PAYMENTS_USD AS ( SELECT NUMBER_in_USD.ORG_ID, NUMBER_in_USD.PAYRUN_ID, NUMBER_in_USD.CREDITED_PARTICIPANT_ID, NUMBER_in_USD.PAYRUN_NAME, NUMBER_in_USD.PAY_PERIOD_ID, NUMBER_in_USD.PAY_DATE, NUMBER_in_USD.PAY_GROUP_ID, NUMBER_in_USD.PAY_GROUP_NAME, NUMBER_in_USD.PAYSHEET_ID, NUMBER_in_USD.PAYSHEET_STATUS, NUMBER_in_USD.PAY_AMT_PAY_CURR_USD, NUMBER_in_USD.paysheet_payout_usd, NUMBER_in_USD.paysheet_payout_LC, pd.CURRENCY_CODE LATEST_HOME_CURRENCY FROM ( SELECT SPD.ORG_ID, SPD.PAYRUN_ID, SPD.CREDITED_PARTICIPANT_ID, SPD.PAYRUN_NAME, SPD.PAY_PERIOD_ID, SPD.PAY_DATE, SPD.PAY_GROUP_ID, SPD.PAY_GROUP_NAME, SPD.PAYSHEET_ID, SPD.PAYSHEET_STATUS, SUM( SPD.PAY_AMT_PAY_CURR * NVL( (CASE WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END), 1 ) ) AS PAY_AMT_PAY_CURR_USD, MAX( SPD.PMT_TXN_AMT_DUE * NVL( (CASE WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END), 1 ) ) AS paysheet_payout_usd, MAX(SPD.PMT_TXN_AMT_DUE) AS paysheet_payout_LC, MAX(HOME_CURRENCY_CODE) AS LATEST_HOME_CURRENCY FROM ( SELECT P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAYRUN_NAME, PB.PAY_PERIOD_ID, PB.PAY_DATE, PG.PAY_GROUP_ID, PG.PAY_GROUP_NAME, P.PLAN_COMPONENT_ID, PC.INCENTIVE_TYPE, PS.PAYSHEET_ID, PS.BONUS_PTD, PS.PMT_TXN_AMT_DUE, ( SELECT meaning FROM cn_lookups WHERE lookup_type = 'CN_PAYSHEET_STATUS' AND lookup_code = PS.OBJECT_STATUS AND enabled_flag = 'Y' AND lookup_code != 'REVIEWED' ) AS PAYSHEET_STATUS, P.PAY_AMT_PAY_CURR, PD.CURRENCY_CODE HOME_CURRENCY_CODE, P.PAY_CURRENCY_CODE, P.SOURCE_EVENT_DATE FROM CN_TP_PARTICIPANT_PAY_ALL P INNER JOIN CN_TP_PAYRUNS_ALL PB ON PB.PAYRUN_ID = P.PAYRUN_ID AND PB.ORG_ID = P.ORG_ID INNER JOIN CN_PAY_GROUPS_ALL_VL PG ON PG.PAY_GROUP_ID = PB.PAY_GROUP_ID AND PG.ORG_ID = P.ORG_ID INNER JOIN CN_PLAN_COMPONENTS_ALL_VL PC ON P.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD ON P.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID INNER JOIN CN_TP_PAYSHEETS_ALL PS ON PS.PARTICIPANT_ID = P.CREDITED_PARTICIPANT_ID AND PS.PAYRUN_ID = P.PAYRUN_ID AND PS.QUOTA_ID IS NULL WHERE ( (P.SOURCE_EVENT_DATE IS NOT NULL AND P.SOURCE_EVENT_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, P.SOURCE_EVENT_DATE)) OR (P.SOURCE_EVENT_DATE IS NULL AND PB.PAY_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, PB.PAY_DATE)) ) AND PB.PAYRUN_ID = NVL(:payrun_id, PB.PAYRUN_ID) AND P.CREDITED_PARTICIPANT_ID = NVL(:participant_id, P.CREDITED_PARTICIPANT_ID) AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID) ) SPD LEFT JOIN ( SELECT CR.FROM_CURRENCY, CR.TO_CURRENCY, CR.CONVERSION_START_DATE, CR.CONVERSION_END_DATE, CR.CONVERSION_RATE FROM CN_CONVERSION_RATES CR INNER JOIN CN_CONVERSION_TYPES_TL CT ON CT.CONVERSION_TYPE_ID = CR.CONVERSION_TYPE_ID WHERE CT.CONVERSION_TYPE_ID = '-1001' AND CT.LANGUAGE = 'US' AND CR.TO_CURRENCY = 'USD' ) conversion ON TRUNC(SPD.PAY_DATE) BETWEEN conversion.CONVERSION_START_DATE AND conversion.CONVERSION_END_DATE AND conversion.FROM_CURRENCY = SPD.HOME_CURRENCY_CODE GROUP BY SPD.ORG_ID, SPD.PAYRUN_ID, SPD.CREDITED_PARTICIPANT_ID, SPD.PAYRUN_NAME, SPD.PAY_PERIOD_ID, SPD.PAY_DATE, SPD.PAY_GROUP_ID, SPD.PAY_GROUP_NAME, SPD.PAYSHEET_ID, SPD.PAYSHEET_STATUS ) NUMBER_in_USD INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD ON TRUNC(NUMBER_in_USD.PAY_DATE) BETWEEN PD.START_DATE AND NVL(PD.END_DATE, DATE '9999-12-31') AND NUMBER_in_USD.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID AND NUMBER_in_USD.ORG_ID = PD.ORG_ID ), -- Convert back to local currency BG AS ( SELECT after_group.ORG_ID, after_group.PAYRUN_ID, after_group.CREDITED_PARTICIPANT_ID, after_group.PAYRUN_NAME, after_group.PAY_PERIOD_ID, after_group.PAY_DATE, after_group.PAY_GROUP_ID, after_group.PAY_GROUP_NAME, after_group.PAYSHEET_ID, after_group.PAY_AMT_PAY_CURR_USD, after_group.paysheet_payout_LC, after_group.LATEST_HOME_CURRENCY, after_group.PAYSHEET_STATUS, TO_CHAR( 1 / ( NVL( (CASE WHEN after_group.LATEST_HOME_CURRENCY = 'USD' THEN 1 ELSE conversion2.CONVERSION_RATE END), 1 ) ), 'FM9999999990.0000' ) AS conversionrate, TO_CHAR( after_group.PAY_AMT_PAY_CURR_USD * ( 1 / ( NVL( (CASE WHEN after_group.LATEST_HOME_CURRENCY = 'USD' THEN 1 ELSE conversion2.CONVERSION_RATE END), 1 ) ) ), 'FM9999999990.0000' ) AS finalPayout, TO_CHAR( after_group.paysheet_payout_usd * ( 1 / ( NVL( (CASE WHEN after_group.LATEST_HOME_CURRENCY = 'USD' THEN 1 ELSE conversion2.CONVERSION_RATE END), 1 ) ) ), 'FM9999999990.0000' ) AS finalPayout_paysheet FROM PAYMENTS_USD after_group LEFT JOIN CONVERSION_RATES_USD conversion2 ON TRUNC(after_group.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE AND conversion2.CONVERSION_END_DATE AND conversion2.FROM_CURRENCY = after_group.LATEST_HOME_CURRENCY ), -- Materialize latest plan component - FILTERED LATEST_PLAN_COMPONENT AS ( SELECT PARTICIPANT_ID, PAYRUN_NAME, ORG_ID, PAYRUN_ID, PERIOD_ID, PLAN_COMPONENT_ID, PLAN_COMPONENT_name, INCENTIVE_TYPE, ROLE_NAME, PAY_GROUP_NAME, START_DATE, END_DATE FROM ( SELECT DISTINCT PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID, PB.PAYRUN_NAME, PART_PAY.ORG_ID, PB.PAYRUN_ID, PB.PAY_PERIOD_ID AS PERIOD_ID, PART_PAY.PLAN_COMPONENT_ID, PC.INCENTIVE_TYPE, PC.Plan_component_name, roles.ROLE_NAME, PG.PAY_GROUP_NAME, TRUNC(SRP_CP.START_DATE) AS START_DATE, TRUNC(SRP_CP.END_DATE) AS END_DATE, DENSE_RANK() OVER ( PARTITION BY PART_PAY.CREDITED_PARTICIPANT_ID, PART_PAY.ORG_ID, PB.PAY_PERIOD_ID, PB.PAYRUN_ID ORDER BY SRP_CP.START_DATE DESC ) AS LATEST_PC_RECORD FROM CN_TP_PARTICIPANT_PAY_ALL PART_PAY INNER JOIN CN_TP_PAYRUNS_ALL PB ON PB.PAYRUN_ID = PART_PAY.PAYRUN_ID AND PB.ORG_ID = PART_PAY.ORG_ID AND PART_PAY.SOURCE_EVENT_DATE <= PB.PAY_DATE AND PART_PAY.INCENTIVE_TYPE_CODE IN ('COMMISSION', 'BONUS') AND PART_PAY.EARNING_ID IS NOT NULL INNER JOIN CN_PAY_GROUPS_ALL_TL PG ON PB.PAY_GROUP_ID = PG.PAY_GROUP_ID AND PG.LANGUAGE = 'US' INNER JOIN CN_PLAN_COMPONENTS_ALL_VL PC ON PART_PAY.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID INNER JOIN CN_SRP_PLAN_COMPONENTS_ALL SRP_PC ON SRP_PC.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID AND SRP_PC.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID INNER JOIN CN_SRP_COMP_PLANS_ALL SRP_CP ON SRP_PC.SRP_COMP_PLAN_ID = SRP_CP.SRP_COMP_PLAN_ID AND SRP_CP.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID INNER JOIN CN_PARTICIPANT_ROLES cpr ON SRP_CP.SRP_RULE_ID = cpr.PARTICIPANT_ROLE_ID AND SRP_CP.PARTICIPANT_ID = cpr.PARTICIPANT_ID AND SRP_CP.SRP_RULE_TYPE_CODE = 'ROLE' INNER JOIN CN_ROLES_TL roles ON cpr.ROLE_ID = roles.ROLE_ID AND roles.LANGUAGE = 'US' WHERE PB.PAYRUN_ID = NVL(:payrun_id, PB.PAYRUN_ID) AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID) AND PART_PAY.CREDITED_PARTICIPANT_ID = NVL(:participant_id, PART_PAY.CREDITED_PARTICIPANT_ID) ) WHERE LATEST_PC_RECORD = 1 ), -- Materialize calendar data PAY_CAL AS ( SELECT UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR007) AS PAYMENT_BATCH_NAME, UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR008) AS PAYGROUP_NAME, PAYRUN_CAL.EXTN_ATTRIBUTE_NUMBER006 AS PERIOD_ID, PAYRUN_CAL.CURRENCY_CODE, UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR011) AS INCENTIVE_TYPE, UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR012) AS COUNTRY, TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP009) AS MPA_CUTOFF_START_DT, TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP010) AS MPA_CUTOFF_END_DT, TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP007) AS PAYSHEET_CUTOFF_START_DT, TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP012) AS PAYROLL_SUBMISSION_END_CST, TRUNC(PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP008) AS PAYSHEET_CUTOFF_DT FROM ZCA_REF_ENTITIES PAYRUN_CAL WHERE ATTRIBUTE_CATEGORY = 'MTQ_ICM_PC_OBJ_c' AND (:period_id IS NULL OR PAYRUN_CAL.EXTN_ATTRIBUTE_NUMBER006 = :period_id) ), --BB -- Step 1: Convert BB to USD (grouped by HOME_CURRENCY_CODE across all old periods) BB_AMT_USD AS ( SELECT P.PAYRUN_ID, P.PAYEE_PARTICIPANT_ID AS CREDITED_PARTICIPANT_ID, P.PAYMENT_BATCH_PERIOD AS PAY_PERIOD_ID, LISTAGG(DISTINCT P.HOME_CURRENCY_CODE, ', ') WITHIN GROUP (ORDER BY P.PAY_PERIOD_ID) AS HOME_CURRENCY_CODE, SUM(P.PAY_AMT_PAY_CURR) AS BB_AMOUNT_HOME_CURR, SUM( P.PAY_AMT_PAY_CURR * CASE WHEN P.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE NVL(CR_USD.CONVERSION_RATE, 1) END ) AS BB_AMOUNT_USD, MAX(PD.CURRENCY_CODE) AS LATEST_HOME_CURRENCY, MAX(P.PAY_DATE) AS PAY_DATE, LISTAGG(DISTINCT P.PAY_PERIOD_ID, ', ') WITHIN GROUP (ORDER BY P.PAY_PERIOD_ID) AS OLD_PAY_PERIOD_IDS FROM CN_TP_PARTICIPANT_PAY_EXP_V P INNER JOIN PARTICIPANT_DETAILS_CACHE PD ON P.PAYEE_PARTICIPANT_ID = PD.PARTICIPANT_ID AND P.PAY_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, P.PAY_DATE) INNER JOIN PARTICIPANT_DETAILS_CACHE PDX ON P.PAYEE_PARTICIPANT_ID = PDX.PARTICIPANT_ID AND ( (P.SOURCE_EVENT_DATE IS NOT NULL AND P.SOURCE_EVENT_DATE BETWEEN PDX.START_DATE AND NVL(PDX.END_DATE, (P.SOURCE_EVENT_DATE))) OR (P.SOURCE_EVENT_DATE IS NULL AND P.PAY_DATE BETWEEN PDX.START_DATE AND NVL(PDX.END_DATE, P.PAY_DATE)) ) LEFT JOIN CONVERSION_RATES_USD CR_USD ON P.HOME_CURRENCY_CODE != 'USD' AND CR_USD.FROM_CURRENCY = PDX.CURRENCY_CODE AND TRUNC(P.PAY_DATE) BETWEEN CR_USD.CONVERSION_START_DATE AND CR_USD.CONVERSION_END_DATE WHERE P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID) AND P.PAYEE_PARTICIPANT_ID = NVL(:participant_id, P.PAYEE_PARTICIPANT_ID) AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD) AND P.PAY_PERIOD_ID <> P.PAYMENT_BATCH_PERIOD AND EXISTS ( SELECT 1 FROM BB_MULTI_CURRENCY bb WHERE bb.PAYEE_PARTICIPANT_ID = P.PAYEE_PARTICIPANT_ID AND bb.PAYRUN_ID = P.PAYRUN_ID AND bb.PAYMENT_BATCH_PERIOD = P.PAYMENT_BATCH_PERIOD ) GROUP BY P.PAYRUN_ID, P.PAYEE_PARTICIPANT_ID, P.PAYMENT_BATCH_PERIOD, P.HOME_CURRENCY_CODE ), -- Step 2: Convert each currency's BB from USD to Latest Home Currency BB_AMT_BY_CURRENCY AS ( SELECT BB.PAYRUN_ID, BB.CREDITED_PARTICIPANT_ID, BB.PAY_PERIOD_ID, BB.HOME_CURRENCY_CODE, BB.BB_AMOUNT_HOME_CURR, BB.BB_AMOUNT_USD, BB.LATEST_HOME_CURRENCY, BB.OLD_PAY_PERIOD_IDS, BB.BB_AMOUNT_USD * CASE WHEN BB.LATEST_HOME_CURRENCY = 'USD' THEN 1 ELSE 1 / NVL(CR_LC.CONVERSION_RATE, 1) END AS BB_IN_LATEST_HC_NUM, CASE WHEN BB.LATEST_HOME_CURRENCY = 'USD' THEN 1 ELSE 1 / NVL(CR_LC.CONVERSION_RATE, 1) END AS CONVERSION_RATE_USD_TO_LC FROM BB_AMT_USD BB LEFT JOIN CONVERSION_RATES_USD CR_LC ON BB.LATEST_HOME_CURRENCY != 'USD' AND CR_LC.FROM_CURRENCY = BB.LATEST_HOME_CURRENCY AND TRUNC(BB.PAY_DATE) BETWEEN CR_LC.CONVERSION_START_DATE AND CR_LC.CONVERSION_END_DATE ), -- Step 3: Aggregate all currencies into single row per participant BB_AMT AS ( SELECT PAYRUN_ID, CREDITED_PARTICIPANT_ID, PAY_PERIOD_ID, LISTAGG(HOME_CURRENCY_CODE, ', ') WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS BB_HOME_CURRENCIES, LISTAGG( HOME_CURRENCY_CODE || ':' || TO_CHAR(BB_AMOUNT_HOME_CURR, 'FM9999999990.0000'), ', ' ) WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS BB_HOME_CURRENCIES_WITH_AMOUNTS, MAX(LATEST_HOME_CURRENCY) AS LATEST_HOME_CURRENCY, MAX(OLD_PAY_PERIOD_IDS) AS OLD_PAY_PERIOD_IDS, SUM(BB_AMOUNT_USD) AS BB_AMOUNT_USD, TO_CHAR( SUM(BB_AMOUNT_USD), 'FM9999999990.0000' ) AS BB_IN_USD, SUM(BB_IN_LATEST_HC_NUM) AS BB_IN_LATEST_HC_NUM, TO_CHAR( SUM(BB_IN_LATEST_HC_NUM), 'FM9999999990.0000' ) AS BB_IN_LATEST_HC, TO_CHAR( AVG(CONVERSION_RATE_USD_TO_LC), 'FM9999999990.0000' ) AS BB_CONVERSION_RATE_AVG, SUM(BB_AMOUNT_HOME_CURR) AS BB_TOTAL_HOME_CURR_SUM, LISTAGG( HOME_CURRENCY_CODE || ':' || TO_CHAR(BB_AMOUNT_HOME_CURR, 'FM9999999990.0000'), ' | ' ) WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS BB_BREAKDOWN_BY_CURRENCY FROM BB_AMT_BY_CURRENCY GROUP BY PAYRUN_ID, CREDITED_PARTICIPANT_ID, PAY_PERIOD_ID ), -- PTD USD PTD_EARNINGS_USD AS ( SELECT P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAY_PERIOD_ID, SUM( P.PAY_AMT_PAY_CURR * NVL( (CASE WHEN P.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END), 1 ) ) AS PTD_EARNINGS_USD, MAX(PD.CURRENCY_CODE) AS LATEST_HOME_CURRENCY FROM CN_TP_PARTICIPANT_PAY_ALL P INNER JOIN CN_TP_PAYRUNS_ALL PB ON PB.PAYRUN_ID = P.PAYRUN_ID AND PB.ORG_ID = P.ORG_ID INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD ON P.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID AND P.ORG_ID = PD.ORG_ID LEFT JOIN CONVERSION_RATES_USD conversion ON TRUNC( CASE WHEN P.SOURCE_EVENT_DATE IS NOT NULL THEN P.SOURCE_EVENT_DATE ELSE PB.PAY_DATE END ) BETWEEN conversion.CONVERSION_START_DATE AND conversion.CONVERSION_END_DATE AND conversion.FROM_CURRENCY = P.HOME_CURRENCY_CODE WHERE ( (P.SOURCE_EVENT_DATE IS NOT NULL AND P.SOURCE_EVENT_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, P.SOURCE_EVENT_DATE)) OR (P.SOURCE_EVENT_DATE IS NULL AND PB.PAY_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, PB.PAY_DATE)) ) AND P.PAY_PERIOD_ID = PB.PAY_PERIOD_ID AND P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID) AND P.CREDITED_PARTICIPANT_ID = NVL(:participant_id, P.CREDITED_PARTICIPANT_ID) AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID) GROUP BY P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAY_PERIOD_ID ), --PTD earnings MC PTD_EARNINGS_AMT AS ( SELECT ptd_usd.PAYRUN_ID, ptd_usd.CREDITED_PARTICIPANT_ID, ptd_usd.ORG_ID, ptd_usd.PAY_PERIOD_ID, ptd_usd.PTD_EARNINGS_USD, PDX.CURRENCY_CODE LATEST_HOME_CURRENCY, TO_CHAR( ptd_usd.PTD_EARNINGS_USD * ( 1 / ( NVL( (CASE WHEN PDX.CURRENCY_CODE = 'USD' THEN 1 ELSE conversion2.CONVERSION_RATE END), 1 ) ) ), 'FM9999999990.0000' ) AS PTD_EARNINGS_LC FROM PTD_EARNINGS_USD ptd_usd INNER JOIN CN_TP_PAYRUNS_ALL PB ON PB.PAYRUN_ID = ptd_usd.PAYRUN_ID AND PB.ORG_ID = ptd_usd.ORG_ID LEFT JOIN ( SELECT * FROM ( SELECT PB.PAYRUN_ID, PB.PAY_DATE, PDX.PARTICIPANT_ID, PDX.ORG_ID, PDX.START_DATE, PDX.END_DATE, CPV.start_date period_start_date, CPV.end_date period_end_date, PDX.CURRENCY_CODE, PB.PAY_PERIOD_ID, RANK() OVER (ORDER BY PDX.start_date DESC) rank FROM CN_TP_PAYRUNS_ALL PB, CN_SRP_PARTICIPANT_DETAILS_ALL PDX, CN_PERIODS_VL CPV WHERE PB.ORG_ID = PDX.ORG_ID AND PB.PAYRUN_ID = NVL(:payrun_id, PB.PAYRUN_ID) AND PDX.PARTICIPANT_ID = NVL(:participant_id, pdx.PARTICIPANT_ID) AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID) AND PB.pay_period_id = CPV.period_id AND PDX.START_DATE >= CPV.start_date ) a WHERE rank = 1 ) PDX ON ptd_usd.PAYRUN_ID = PDX.PAYRUN_ID AND ptd_usd.CREDITED_PARTICIPANT_ID = PDX.PARTICIPANT_ID AND ptd_usd.ORG_ID = PDX.Org_ID AND ptd_usd.PAY_PERIOD_ID = PDX.PAY_PERIOD_ID LEFT JOIN CONVERSION_RATES_USD conversion2 ON TRUNC(PB.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE AND conversion2.CONVERSION_END_DATE AND conversion2.FROM_CURRENCY = PDX.CURRENCY_CODE ), -- Materialize prorated TI - FILTERED PM_TI AS ( SELECT PARTICIPANT_ID, ORG_ID, PERIOD_ID, START_DATE, END_DATE, Prorated_MonthlyTI FROM ( SELECT SPFM.PARTICIPANT_ID, SPFM.ORG_ID, SPFM.PERIOD_ID, SRP_CP.START_DATE, SRP_CP.END_DATE, SPFM.OUTPUT_ACHIEVED_ITD AS Prorated_MonthlyTI, ROW_NUMBER() OVER ( PARTITION BY SPFM.PARTICIPANT_ID, PERIOD.ORG_ID, PERIOD.PERIOD_ID ORDER BY NVL(SRP_CP.END_DATE, DATE '9999-12-31') DESC ) AS LATEST_TI_RECORD FROM CN_SRP_PER_FORM_METRICS_ALL SPFM INNER JOIN CN_FORMULAS_ALL_B Monthly_TI ON Monthly_TI.FORMULA_ID = SPFM.FORMULA_ID AND Monthly_TI.ATTRIBUTE9 = 'EmployeeEvents' AND Monthly_TI.ATTRIBUTE10 = 'MonthlyTI' INNER JOIN CN_SRP_COMP_PLANS_ALL SRP_CP ON SRP_CP.PARTICIPANT_ID = SPFM.PARTICIPANT_ID AND SPFM.SRP_COMP_PLAN_ID = SRP_CP.SRP_COMP_PLAN_ID AND SPFM.ORG_ID = SRP_CP.ORG_ID INNER JOIN CN_PERIOD_DIMS_ALL_TL PERIOD ON PERIOD.ORG_ID = SPFM.ORG_ID AND PERIOD.PERIOD_TYPE = 'Monthly' AND PERIOD.PERIOD_ID = SPFM.PERIOD_ID WHERE ( PERIOD.END_DATE BETWEEN SRP_CP.START_DATE AND NVL(SRP_CP.END_DATE, DATE '9999-12-31') OR NVL(SRP_CP.END_DATE, DATE '9999-12-31') BETWEEN PERIOD.START_DATE AND PERIOD.END_DATE ) AND SPFM.PARTICIPANT_ID = NVL(:participant_id, SPFM.PARTICIPANT_ID) AND SPFM.PERIOD_ID = NVL(:period_id, SPFM.PERIOD_ID) ) WHERE LATEST_TI_RECORD = 1 ), -- NEW CTE: Dual Snapshot for HR Status and Sales Rep PARTICIPANT_DUAL_SNAPSHOT AS ( SELECT BG.PAYRUN_ID, BG.CREDITED_PARTICIPANT_ID, BG.ORG_ID, BG.PAY_DATE, PAY_CAL.PAYSHEET_CUTOFF_DT, -- Snapshot at PAY_DATE PD_PAY.ATTRIBUTE_CHAR2 AS HR_Status_PayDt, PD_PAY.ATTRIBUTE_CHAR17 AS SalesRep_PayDt, PD_PAY.COUNTRY_CODE AS Employment_Country_PayDt, -- Snapshot at PAYSHEET_CUTOFF_DT PD_CUTOFF.ATTRIBUTE_CHAR2 AS HR_Status_CutoffDt, PD_CUTOFF.ATTRIBUTE_CHAR17 AS SalesRep_CutoffDt, PD_CUTOFF.COUNTRY_CODE AS Employment_Country_CutoffDt, -- Apply NVL logic here (prioritize cutoff date) NVL(PD_CUTOFF.ATTRIBUTE_CHAR2, PD_PAY.ATTRIBUTE_CHAR2) AS HRStatus_c, NVL(PD_CUTOFF.ATTRIBUTE_CHAR17, PD_PAY.ATTRIBUTE_CHAR17) AS SalesRP_c, NVL(PD_CUTOFF.COUNTRY_CODE, PD_PAY.COUNTRY_CODE) AS EmploymentCountry_c FROM BG -- Join for PAY_DATE snapshot LEFT JOIN PARTICIPANT_DETAILS_CACHE PD_PAY ON TRUNC(BG.PAY_DATE) BETWEEN PD_PAY.START_DATE AND NVL(PD_PAY.END_DATE, DATE '9999-12-31') AND BG.CREDITED_PARTICIPANT_ID = PD_PAY.PARTICIPANT_ID AND BG.ORG_ID = PD_PAY.ORG_ID -- Join to get PAYSHEET_CUTOFF_DT LEFT JOIN LATEST_PLAN_COMPONENT LPC_TEMP ON LPC_TEMP.PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID AND LPC_TEMP.ORG_ID = BG.ORG_ID AND LPC_TEMP.PERIOD_ID = BG.PAY_PERIOD_ID AND LPC_TEMP.PAYRUN_ID = BG.PAYRUN_ID LEFT JOIN PAY_CAL ON PAY_CAL.PAYMENT_BATCH_NAME = UPPER(LPC_TEMP.PAYRUN_NAME) AND PAY_CAL.PAYGROUP_NAME = UPPER(LPC_TEMP.PAY_GROUP_NAME) AND PAY_CAL.PERIOD_ID = BG.PAY_PERIOD_ID AND PAY_CAL.CURRENCY_CODE = BG.LATEST_HOME_CURRENCY AND TO_CHAR(PAY_CAL.INCENTIVE_TYPE) = UPPER(TO_CHAR(LPC_TEMP.INCENTIVE_TYPE)) -- Join for PAYSHEET_CUTOFF_DT snapshot LEFT JOIN PARTICIPANT_DETAILS_CACHE PD_CUTOFF ON TRUNC(PAY_CAL.PAYSHEET_CUTOFF_DT) BETWEEN PD_CUTOFF.START_DATE AND NVL(PD_CUTOFF.END_DATE, DATE '9999-12-31') AND BG.CREDITED_PARTICIPANT_ID = PD_CUTOFF.PARTICIPANT_ID AND BG.ORG_ID = PD_CUTOFF.ORG_ID ) SELECT -- Build unique record identifier TO_CHAR( BG.PAY_PERIOD_ID || '-' || SUBSTR( BG.PAYRUN_NAME, 1 + INSTR(BG.PAYRUN_NAME, '_', -1, 2), (INSTR(BG.PAYRUN_NAME, '_', -1, 1) - INSTR(BG.PAYRUN_NAME, '_', -1, 2) - 1) ) || '-' || PN.person_number || '-' || BG.PAYSHEET_ID ) AS RecordId_c, -- Basic identifiers BG.PAYRUN_ID AS PayRunId_c, BG.PAYSHEET_ID AS PaysheetId_c, BG.PAY_PERIOD_ID AS PeriodId_c, PN.person_number AS PersonNumber_c, PN.PARTICIPANT_NAME AS ParticipantName_c, BG.CREDITED_PARTICIPANT_ID AS ParticipantId_c, -- Dates TO_CHAR(BG.PAY_DATE, 'YYYY-MM-DD') AS PayDate_c, TO_CHAR(PAY_CAL.PAYSHEET_CUTOFF_DT, 'YYYY-MM-DD') AS PaysheetCutoffDate_c, TO_CHAR(PAY_CAL.MPA_CUTOFF_START_DT, 'YYYY-MM-DD') AS MPACutoffStartDate_c, TO_CHAR(PAY_CAL.MPA_CUTOFF_END_DT, 'YYYY-MM-DD') AS MPACutoffEndDate_c, TO_CHAR(PAY_CAL.PAYROLL_SUBMISSION_END_CST, 'YYYY-MM-DD') AS PayrollSubmissionEndDate_c, -- Plan and role information LPC.PLAN_COMPONENT_ID AS PlanComponentId_c, LPC.PLAN_COMPONENT_name AS PlanComponentName_c, LPC.INCENTIVE_TYPE AS IncentiveType_c, LPC.ROLE_NAME AS RoleName_c, BG.PAY_GROUP_NAME AS PayGroupName_c, -- Currency information BG.LATEST_HOME_CURRENCY AS HomeCurrency_c, -- **MODIFIED: Use DUAL_SNAP instead of parDetails** DUAL_SNAP.HRStatus_c AS HRStatus_c, DUAL_SNAP.SalesRP_c AS SalesRP_c, DUAL_SNAP.EmploymentCountry_c AS EmploymentCountry_c, -- HR Country and Analyst parDetails.COUNTRY_CODE AS HRCountry_c, ( SELECT PARTY_NAME FROM HZ_PARTIES WHERE PARTY_ID = parDetails.ANALYST_PARTY_ID ) AS AnalystName_c, -- HR TI (Target Incentive) TO_CHAR(parDetails.PD_HR_TI, 'FM9999999990.0000') AS HRTI_c, -- Prorated Monthly TI TO_CHAR( NVL(PM_TI.Prorated_MonthlyTI, 0), 'FM9999999990.0000' ) AS ProratedMonthlyTI_c, -- Beginning Balance (BB) fields CASE WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_IN_LATEST_HC ELSE TO_CHAR( NVL( SUM( CASE WHEN BP.PAY_PERIOD_ID <> BP.PAY_DATE THEN BP.PAY_AMT_PAY_CURR ELSE 0 END ), 0 ), 'FM9999999990.0000' ) END AS BeginningBalance_c, -- BB in USD CASE WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_IN_USD ELSE TO_CHAR( NVL( SUM( CASE WHEN BP.PAY_PERIOD_ID <> BP.PAY_DATE THEN BP.PAY_AMT_PAY_CURR * NVL( (CASE WHEN BP.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE CR_USD.CONVERSION_RATE END), 1 ) ELSE 0 END ), 0 ), 'FM9999999990.0000' ) END AS BeginningBalanceUSD_c, -- BB Multi-Currency Flag NVL(MC_FLAGS.BBMultiCurrencyFlag, 'N') AS BBMultiCurrencyFlag_c, -- BB Home Currencies (for multi-currency cases) CASE WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_HOME_CURRENCIES ELSE NULL END AS BBHomeCurrencies_c, -- BB Breakdown by Currency CASE WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_BREAKDOWN_BY_CURRENCY ELSE NULL END AS BBBreakdownByCurrency_c, -- Old Pay Period IDs (where BB originated) CASE WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.OLD_PAY_PERIOD_IDS ELSE LISTAGG( DISTINCT CASE WHEN BP.PAY_PERIOD_ID <> BP.PAY_DATE THEN TO_CHAR(BP.PAY_PERIOD_ID) END, ', ' ) WITHIN GROUP (ORDER BY BP.PAY_PERIOD_ID) END AS OldPayPeriodIds_c, -- PTD Earnings (Period-to-Date) CASE WHEN MC_FLAGS.MULTI_CURR_YN = 'Y' THEN PTD_AMT.PTD_EARNINGS_LC ELSE BG.finalPayout END AS PTDEarnings_c, -- PTD Earnings in USD CASE WHEN MC_FLAGS.MULTI_CURR_YN = 'Y' THEN TO_CHAR(PTD_AMT.PTD_EARNINGS_USD, 'FM9999999990.0000') ELSE TO_CHAR(BG.PAY_AMT_PAY_CURR_USD, 'FM9999999990.0000') END AS PTDEarningsUSD_c, -- Period Multi-Currency Flag NVL(MC_FLAGS.MULTI_CURR_YN, 'N') AS PeriodMultiCurrencyFlag_c, -- Current Earnings Distribution (CED) = PTD - BB TO_CHAR( TO_NUMBER( CASE WHEN MC_FLAGS.MULTI_CURR_YN = 'Y' THEN PTD_AMT.PTD_EARNINGS_LC ELSE BG.finalPayout END ) - TO_NUMBER( CASE WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_IN_LATEST_HC ELSE TO_CHAR( NVL( SUM( CASE WHEN BP.PAY_PERIOD_ID <> BP.PAY_DATE THEN BP.PAY_AMT_PAY_CURR ELSE 0 END ), 0 ), 'FM9999999990.0000' ) END ), 'FM9999999990.0000' ) AS CED_c, -- CED in USD TO_CHAR( TO_NUMBER( CASE WHEN MC_FLAGS.MULTI_CURR_YN = 'Y' THEN TO_CHAR(PTD_AMT.PTD_EARNINGS_USD, 'FM9999999990.0000') ELSE TO_CHAR(BG.PAY_AMT_PAY_CURR_USD, 'FM9999999990.0000') END ) - TO_NUMBER( CASE WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_IN_USD ELSE TO_CHAR( NVL( SUM( CASE WHEN BP.PAY_PERIOD_ID <> BP.PAY_DATE THEN BP.PAY_AMT_PAY_CURR * NVL( (CASE WHEN BP.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE CR_USD.CONVERSION_RATE END), 1 ) ELSE 0 END ), 0 ), 'FM9999999990.0000' ) END ), 'FM9999999990.0000' ) AS CEDUSD_c, -- BB as % of HR TI TO_CHAR( CASE WHEN NVL(parDetails.PD_HR_TI, 0) = 0 THEN 0 ELSE ( TO_NUMBER( CASE WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_IN_LATEST_HC ELSE TO_CHAR( NVL( SUM( CASE WHEN BP.PAY_PERIOD_ID <> BP.PAY_DATE THEN BP.PAY_AMT_PAY_CURR ELSE 0 END ), 0 ), 'FM9999999990.0000' ) END ) / parDetails.PD_HR_TI ) * 100 END, 'FM9999999990.00' ) AS BBOfHRTI_c, -- Paysheet Status BG.PAYSHEET_STATUS AS PaysheetStatus_c, -- Conversion Rate BG.conversionrate AS ConversionRate_c, -- Paysheet Payout (Local Currency) BG.finalPayout_paysheet AS PaysheetPayoutLC_c, -- Additional debugging/tracking fields TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS QueryExecutionTime_c, -- Snapshot dates for debugging TO_CHAR(DUAL_SNAP.PAY_DATE, 'YYYY-MM-DD') AS SnapshotPayDate_c, TO_CHAR(DUAL_SNAP.PAYSHEET_CUTOFF_DT, 'YYYY-MM-DD') AS SnapshotCutoffDate_c FROM BG -- **NEW JOIN: Add PARTICIPANT_DUAL_SNAPSHOT** LEFT JOIN PARTICIPANT_DUAL_SNAPSHOT DUAL_SNAP ON DUAL_SNAP.PAYRUN_ID = BG.PAYRUN_ID AND DUAL_SNAP.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID AND DUAL_SNAP.ORG_ID = BG.ORG_ID -- Person Numbers LEFT JOIN PERSON_NUMBERS PN ON PN.participant_id = BG.CREDITED_PARTICIPANT_ID -- Latest Plan Component LEFT JOIN LATEST_PLAN_COMPONENT LPC ON LPC.PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID AND LPC.ORG_ID = BG.ORG_ID AND LPC.PERIOD_ID = BG.PAY_PERIOD_ID AND LPC.PAYRUN_ID = BG.PAYRUN_ID -- Calendar LEFT JOIN PAY_CAL ON PAY_CAL.PAYMENT_BATCH_NAME = UPPER(BG.PAYRUN_NAME) AND PAY_CAL.PAYGROUP_NAME = UPPER(BG.PAY_GROUP_NAME) AND PAY_CAL.PERIOD_ID = BG.PAY_PERIOD_ID AND PAY_CAL.CURRENCY_CODE = BG.LATEST_HOME_CURRENCY AND TO_CHAR(PAY_CAL.INCENTIVE_TYPE) = UPPER(TO_CHAR(LPC.INCENTIVE_TYPE)) -- Participant Details (for date range) LEFT JOIN PARTICIPANT_DETAIL_DATERANGE parDetails ON TRUNC(BG.PAY_DATE) BETWEEN parDetails.PD_START_DATE AND NVL(parDetails.PD_END_DATE, DATE '9999-12-31') AND BG.CREDITED_PARTICIPANT_ID = parDetails.PARTICIPANT_ID -- Multi-Currency Flags LEFT JOIN MULTI_CURRENCY_FLAGS MC_FLAGS ON MC_FLAGS.PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID AND MC_FLAGS.PAYRUN_ID = BG.PAYRUN_ID AND MC_FLAGS.PERIOD_ID = BG.PAY_PERIOD_ID -- Beginning Balance Amount (Multi-Currency) LEFT JOIN BB_AMT ON BB_AMT.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID AND BB_AMT.PAYRUN_ID = BG.PAYRUN_ID AND BB_AMT.PAY_PERIOD_ID = BG.PAY_PERIOD_ID -- PTD Earnings Amount (Multi-Currency) LEFT JOIN PTD_EARNINGS_AMT PTD_AMT ON PTD_AMT.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID AND PTD_AMT.PAYRUN_ID = BG.PAYRUN_ID AND PTD_AMT.ORG_ID = BG.ORG_ID AND PTD_AMT.PAY_PERIOD_ID = BG.PAY_PERIOD_ID -- Prorated Monthly TI LEFT JOIN PM_TI ON PM_TI.PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID AND PM_TI.ORG_ID = BG.ORG_ID AND PM_TI.PERIOD_ID = BG.PAY_PERIOD_ID -- Base Payments (for single currency BB calculation) LEFT JOIN BASE_PAYMENTS BP ON BP.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID AND BP.PAYRUN_ID = BG.PAYRUN_ID AND BP.ORG_ID = BG.ORG_ID -- Conversion Rates (for single currency BB to USD) LEFT JOIN CONVERSION_RATES_USD CR_USD ON BP.HOME_CURRENCY_CODE != 'USD' AND CR_USD.FROM_CURRENCY = BP.HOME_CURRENCY_CODE AND TRUNC(BP.PAY_DATE) BETWEEN CR_USD.CONVERSION_START_DATE AND CR_USD.CONVERSION_END_DATE GROUP BY BG.PAYRUN_ID, BG.PAYSHEET_ID, BG.PAY_PERIOD_ID, BG.PAYRUN_NAME, BG.PAY_DATE, BG.CREDITED_PARTICIPANT_ID, BG.ORG_ID, BG.PAY_GROUP_NAME, BG.LATEST_HOME_CURRENCY, BG.PAYSHEET_STATUS, BG.conversionrate, BG.finalPayout, BG.finalPayout_paysheet, BG.PAY_AMT_PAY_CURR_USD, PN.person_number, PN.PARTICIPANT_NAME, LPC.PLAN_COMPONENT_ID, LPC.PLAN_COMPONENT_name, LPC.INCENTIVE_TYPE, LPC.ROLE_NAME, PAY_CAL.PAYSHEET_CUTOFF_DT, PAY_CAL.MPA_CUTOFF_START_DT, PAY_CAL.MPA_CUTOFF_END_DT, PAY_CAL.PAYROLL_SUBMISSION_END_CST, parDetails.COUNTRY_CODE, parDetails.ANALYST_PARTY_ID, parDetails.PD_HR_TI, DUAL_SNAP.HRStatus_c, DUAL_SNAP.SalesRP_c, DUAL_SNAP.EmploymentCountry_c, DUAL_SNAP.PAY_DATE, DUAL_SNAP.PAYSHEET_CUTOFF_DT, MC_FLAGS.BBMultiCurrencyFlag, MC_FLAGS.MULTI_CURR_YN, BB_AMT.BB_IN_LATEST_HC, BB_AMT.BB_IN_USD, BB_AMT.BB_HOME_CURRENCIES, BB_AMT.BB_BREAKDOWN_BY_CURRENCY, BB_AMT.OLD_PAY_PERIOD_IDS, PTD_AMT.PTD_EARNINGS_LC, PTD_AMT.PTD_EARNINGS_USD, PM_TI.Prorated_MonthlyTI ) base ) rules;

Comments

Popular posts from this blog

Section 15-Done

Section 16-Done

Section 13-Done