final query

select distinct rules.* , -- BBAction_c DECODE(rules.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_1051', -- Pre-approved zero BB for active sale rep NULL ) AS "BBAction_c", -- BBReasonCode_c DECODE(rules.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',-- Pre-approved by the system NULL ) AS "BBReasonCode_c", -- BBMPAComment_c (NEW BB/MPA Comment) DECODE(rules.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%) or is Austria rep. PENDING analyst to review and action.', NULL ) AS "BBMPAComment_c", -- MPAReasonCode_c (MPA reason code) DECODE(rules.Rule_Number, 1, NULL, 3, NULL, 11, NULL, 'MAV_DIR_MPA_CODE_1008' ) AS "MPAReasonCode_c", -- MPAGenerated_c (MPA Auto prepopulate) DECODE(rules.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, 11, NULL, 99, (-1)*BeginningBalance_c, NULL ) AS "MPAGenerated_c", -- ExpectedPaymentAmount_c (Expected Payout Amount) DECODE(rules.Rule_Number, 1, NULL, 3, NULL, 11, NULL, 99, TO_NUMBER(RULES.PTDEarnings_c), -- Assuming you have PTDEarnings_c column NULL -- Zero for all other rules (2,4,5,6,7,8,9,10) ) AS "ExpectedPaymentAmount_c", -- MPAStatus_c (SCP/SPIF manager's approval) DECODE(rules.Rule_Number, 1, NULL, 3, NULL, 11, NULL, 'Auto approved' ) AS "MPAStatus_c", -- RecoverableFlagV1_c (Recoverable MPA? Y/N) DECODE(rules.Rule_Number, 1, NULL, 3, NULL, 11, NULL, 'Y' ) AS "RecoverableFlagV1_c" -- , Rule_Number 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 -0.05 AND 0.05 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 = '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) BETWEEN 0 AND 10 AND BASE.IncentiveType_c = 'COMMISSION' AND BASE.EmploymentCountry_c = 'AT' 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 /*TO_NUMBER(BASE.BeginningBalance_c) != 0 AND*/ BASE.IncentiveType_c IN ('COMMISSION', 'BONUS') THEN 99 ELSE 0 END AS Rule_Number from ( WITH -- Materialize conversion rates once /* CONVERSION_RATES_USD Function: Fetches corporate conversion rates to USD (type '-1001'). Critical Join: Joins CN_CONVERSION_RATES with CN_CONVERSION_TYPES_TL to ensure only USD rates in the 'US' language are retrieved. */ 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 /* Function: Maps participant IDs to HR person numbers (badge IDs) and names. Critical Join: Joins CN_SRP_PARTICIPANTS_ALL to HZ_PERSON_PROFILES and PER_ALL_PEOPLE_F to link the commission system participant to their HR record. Filters: Applied multiselect filters for :participant_id and :P_BADGE. */ PERSON_NUMBERS AS ( SELECT cspl.participant_id, papf.person_number, --hpp.PERSON_NAME PARTICIPANT_NAME 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) AND ( papf.person_number IN (:P_BADGE) OR COALESCE(:P_BADGE, 'ALL') = 'ALL')*/ --AND (P.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL') ( cspl.participant_id IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL') AND ( cspl.participant_id IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL') -- AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD) ), -- Materialize participant details once - FILTERED /* Function: Extracts and caches participant-specific attributes (Country, TI, Status, Sales Rep flag, Currency) for the reporting period. Critical Join: Self-joins CN_SRP_PARTICIPANTS_ALL to CN_SRP_PARTICIPANT_DETAILS_ALL. */ 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, b.ATTRIBUTE_CHAR33 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) --AND (P.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL') ( b.PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL') AND ( b.PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL') -- AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD) ), -- Materialize multi-currency detection - FILTERED /* BB_MULTI_CURRENCY Function: Identifies if a participant has Beginning Balance (BB) entries in multiple currencies within the same payrun/period. Critical Join: An INNER JOIN between two subqueries (pay_1 and pay_2) that both scan CN_TP_PARTICIPANT_PAY_EXP_V to detect mismatching currencies. Note: Contains multiselect logic for :payrun_id, :participant_id, and :P_BADGE. */ 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 IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL') AND (P.PAYEE_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL') AND (P.PAYEE_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL') 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 ( nvl(p.HR_PRIMARY_WORKER_NUMBER, :P_BADGE) IN (:P_BADGE) OR COALESCE(:P_BADGE, 'ALL') = 'ALL') AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD)*/ AND (P.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL') AND (P.PAYEE_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL') AND (P.PAYEE_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL') 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 /* Function: Detects if current period earnings involve multiple home currencies by checking the participant's history. Critical Join: Joins CN_TP_PARTICIPANT_PAY_EXP_V with CN_SRP_PARTICIPANT_DETAILS_ALL using a date range check on SOURCE_EVENT_DATE. */ 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 ( nvl(p.HR_PRIMARY_WORKER_NUMBER, :P_BADGE) IN (:P_BADGE) OR COALESCE(:P_BADGE, 'ALL') = 'ALL') AND PART_PAY.PAYMENT_BATCH_PERIOD = NVL(:period_id, PART_PAY.PAYMENT_BATCH_PERIOD)*/ AND (PART_PAY.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL') AND (PART_PAY.CREDITED_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL') AND (PART_PAY.CREDITED_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL') AND PART_PAY.PAY_PERIOD_ID = NVL(:period_id, PART_PAY.PAY_PERIOD_ID) 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 ), /*Function: Consolidates multi-currency detection from both BB and current earnings into a single COMBINED_MULTI_CURR_FLAG. Critical Join: A FULL OUTER JOIN between BB_MULTI_CURRENCY and PERIOD_MULTI_CURRENCY to capture cases from either source. */ 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_CUR_YN, -- New Field: Returns 'Y' if either flag is 'Y', otherwise 'N' CASE WHEN nvl(bb_MultiC.BBMultiCurrencyFlag, 'N') = 'Y' OR NVL(PRD_ML.MULTI_CURR_YN, 'N') = 'Y' THEN 'Y' ELSE 'N' END AS COMBINED_MULTI_CURR_FLAG 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 /* Function: The primary data source for payment transactions (Bonus, Commission, PTD). Critical Join: Joins CN_TP_PAYSHEETS_ALL, CN_TP_PAYRUNS_ALL, and CN_TP_PARTICIPANT_PAY_EXP_V. It ensures payments are linked to the correct participant details based on SOURCE_EVENT_DATE. */ BASE_PAYMENTS AS ( SELECT PS.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, PS.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_PAYSHEETS_ALL PS INNER JOIN CN_TP_PAYRUNS_ALL PB ON PB.PAYRUN_ID = PS.PAYRUN_ID AND PB.ORG_ID = PS.ORG_ID INNER JOIN CN_TP_PARTICIPANT_PAY_EXP_V P 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 ( nvl(p.HR_PRIMARY_WORKER_NUMBER, :P_BADGE) IN (:P_BADGE) OR COALESCE(:P_BADGE, 'ALL') = 'ALL') AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID)*/ AND (PB.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL') AND (P.CREDITED_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL') AND (P.CREDITED_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL') AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID) ), -- Materialize participant detail for date range - FILTERED /* Function: Provides a point-in-time snapshot of the participant's status and analyst assignment. Critical Join: Joins details (PART_DTLS) to the header table (PART_HDR) and includes a scalar subquery to find the Analyst's PARTY_ID. */ 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) */ -- AND (PB.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL') (PART_DTLS.PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL') AND (PART_DTLS.PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL') -- AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID ), -- Aggregate to USD /* Function: Aggregates all local currency amounts and converts them into a standardized USD value. Critical Join: LEFT JOIN to the conversion subquery on TRUNC(PAY_DATE) to ensure the currency rate from the specific day of payment is used. */ 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_EXP_V 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.PAY_DATE BETWEEN PDX.START_DATE AND NVL(PDX.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 ( nvl(P.HR_PRIMARY_WORKER_NUMBER, :P_BADGE) IN (:P_BADGE) OR COALESCE(:P_BADGE, 'ALL') = 'ALL') AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID)*/ AND (PB.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL') AND (P.CREDITED_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL') AND (P.CREDITED_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL') 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 /* Function: Converts the consolidated USD totals back into the participant's Latest Home Currency. Critical Join: Joins PAYMENTS_USD back to CONVERSION_RATES_USD using an inverse conversion rate (1 / CONVERSION_RATE). */ 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 ) ), 'FM9999999999990.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 ) ) ), 'FM99999999999990.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 ) ) ), 'FM99999999999990.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 /* Function: Identifies the most recent plan component and role for a participant to determine their incentive type (Commission vs. Bonus). Critical Join: Uses DENSE_RANK() OVER (PARTITION BY ... ORDER BY START_DATE DESC) to select exactly one "latest" record.*/ LATEST_PLAN_COMPONENT AS ( SELECT PARTICIPANT_ID, PAYRUN_NAME, ORG_ID, PAYRUN_ID, PERIOD_ID, PLAN_COMPONENT_ID, PLAN_COMPONENT_name, INCENTIVE_TYPE, PB_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, NVL(PB.REPORT_GROUP,'ALL') PB_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_EXP_V 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)*/ (PB.PAYRUN_ID IN (:payrun_id) OR COALESCE(:payrun_id,'ALL') = 'ALL') AND (PART_PAY.CREDITED_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL') AND (PART_PAY.CREDITED_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL') AND PB.PAY_PERIOD_ID = NVL(:period_id,PB.PAY_PERIOD_ID ) ) WHERE LATEST_PC_RECORD = 1 ), -- Materialize calendar data PAY_CAL AS ( SELECT UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR010) "PAYMENT_BATCH_NAME", UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR009) "PAYGROUP_NAME", --PAYRUN_CAL.EXTN_ATTRIBUTE_NUMBER006 "PERIOD_ID", PAYRUN_CAL.EXTN_ATTRIBUTE_NUMBER001 "PERIOD_ID", PAYRUN_CAL.CURRENCY_CODE "CURRENCY_CODE", UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR008) "INCENTIVE_TYPE", UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR007) COUNTRY, PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP004 "MPA_CUTOFF_START_DT", PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP003 "MPA_CUTOFF_END_DT", PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP011 "PAYSHEET_CUTOFF_START_DT", PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP010 "PAYSHEET_CUTOFF_DT", PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP008 "PAYROLL_SUBMISSION_END_CST" FROM MOO_REF_ENTITIES PAYRUN_CAL WHERE ATTRIBUTE_CATEGORY = 'MTQ_ICM_PC_OBJ_c' AND (:period_id IS NULL OR PAYRUN_CAL.EXTN_ATTRIBUTE_NUMBER001 = :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, --P.HOME_CURRENCY_CODE, 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, -- multicurrency 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) KEEP (DENSE_RANK LAST ORDER BY PD.START_DATE) 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 IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL') AND (p.PAYEE_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL') AND (p.PAYEE_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL') AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD) -- 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, 'FM9999999999990.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), 'FM9999999999990.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), 'FM9999999999990.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, -- Multicuurrecy 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, PB.PAY_DATE, SUM( P.PAY_AMT_PAY_CURR * NVL( (CASE WHEN P.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE conv_inner.CONVERSION_RATE END), 1 ) ) AS PTD_EARNINGS_USD, MAX(PD.CURRENCY_CODE) KEEP (DENSE_RANK LAST ORDER BY PD.START_DATE) AS LATEST_HOME_CURRENCY FROM CN_TP_PARTICIPANT_PAY_EXP_V 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 conv_inner ON TRUNC( CASE WHEN P.SOURCE_EVENT_DATE IS NOT NULL THEN P.SOURCE_EVENT_DATE ELSE PB.PAY_DATE END ) BETWEEN conv_inner.CONVERSION_START_DATE AND conv_inner.CONVERSION_END_DATE AND conv_inner.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.INCENTIVE_TYPE_CODE NOT IN ('MANUAL_PAY_ADJ', 'PMTPLN') -- AND P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID) AND (P.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL') -- AND P.CREDITED_PARTICIPANT_ID = NVL(:participant_id, P.CREDITED_PARTICIPANT_ID) AND (p.CREDITED_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL') AND (p.CREDITED_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL') 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, PB.pay_date ), --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, ptd_usd.LATEST_HOME_CURRENCY, TO_CHAR( ptd_usd.PTD_EARNINGS_USD * ( 1 / ( NVL( (CASE WHEN ptd_usd.LATEST_HOME_CURRENCY = 'USD' THEN 1 ELSE conversion2.CONVERSION_RATE END), 1 ) ) ), 'FM9999999990.0000' ) AS PTD_EARNINGS_LC FROM PTD_EARNINGS_USD ptd_usd LEFT JOIN CONVERSION_RATES_USD conversion2 ON TRUNC(ptd_usd.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE AND conversion2.CONVERSION_END_DATE AND conversion2.FROM_CURRENCY = ptd_usd.LATEST_HOME_CURRENCY ), -- 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.PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL') AND SPFM.PERIOD_ID = NVL(:period_id, SPFM.PERIOD_ID) ) WHERE LATEST_TI_RECORD = 1 ) 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) )*/ 'REGULAR' || '-' || PN.person_number || '-' || DECODE( UPPER(LATEST_PLAN_COMPONENT.PB_INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(LATEST_PLAN_COMPONENT.PB_INCENTIVE_TYPE) ) ) AS "RecordName", 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) )*/ 'REGULAR' || '-' || PN.person_number || '-' || DECODE( UPPER(LATEST_PLAN_COMPONENT.PB_INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(LATEST_PLAN_COMPONENT.PB_INCENTIVE_TYPE) ) ) AS "RecordNumber", TO_CHAR(BG.PAYRUN_ID) AS "PayrunID_c", TO_CHAR(BG.PAYSHEET_ID) AS "PaysheetId_c", --'Regularcycle' AS "PaymentCycle_c", to_char(BG.PAY_PERIOD_ID) AS "PayPeriodID_c", to_char(cast(BG.PAY_DATE as date), 'MM/DD/YYYY') AS "PayDate_c", PN.person_number AS "BadgeIDWorkerNumber_c", TO_CHAR(BG.CREDITED_PARTICIPANT_ID, 'FM999999999999999999') AS "ParticipantId_c", to_char(LATEST_PLAN_COMPONENT.START_DATE, 'MM/DD/YYYY') AS "RoleStartDate_c", to_char(LATEST_PLAN_COMPONENT.END_DATE, 'MM/DD/YYYY') AS "RoleEndDate_c", BG.PAYRUN_NAME AS "PaymentBatch_c", BG.PAY_GROUP_NAME AS "PayGroup_c", NVL(TO_CHAR( NVL(TO_NUMBER(BB_AMT.BB_IN_LATEST_HC), 0) + NVL(TO_NUMBER(PTD_EARNINGS_AMT.PTD_EARNINGS_LC), 0), 'FM999999999990.0000' ), '0') AS "TotalPaysheetAmountInLHC_c", NVL(TO_CHAR(BG.paysheet_payout_LC, 'FM9999999990.0000'), '0') AS "TotalPaysheetAmount_c", BG.LATEST_HOME_CURRENCY AS "CurrencyCode", TO_CHAR(LATEST_PLAN_COMPONENT.PLAN_COMPONENT_ID, 'FM999999999999999999') AS "PlanComponentId_c", NVL(TO_CHAR(PM_TI.Prorated_MonthlyTI, 'FM9999999990.0000'), '0') AS "ProratedTI_c", NVL(TO_CHAR(PTD_EARNINGS_AMT.PTD_EARNINGS_USD, 'FM9999999990.0000'), '0') AS "PTDUSD_c", NVL(BB_AMT.BB_IN_USD, '0') AS "BBUSD_c", PRD_ML.COMBINED_MULTI_CURR_FLAG AS "MultiCurrency_c", TO_CHAR(pay_cal.PAYSHEET_CUTOFF_DT, 'MM/DD/YYYY HH:MI:SS AM') AS "PaysheetAutomatedRefreshEndCST_c", TO_CHAR(PAY_CAL.PAYSHEET_CUTOFF_START_DT, 'MM/DD/YYYY HH:MM:SS PM') "PaysheetAutomatedRefreshStartCST_c", NVL(TO_CHAR(BG.PAY_AMT_PAY_CURR_USD, 'FM9999999990.0000'), '0') AS "TotalPaysheetAmountUSD_c", TO_CHAR(( SELECT DISTINCT HR_PRIMARY_WORKER_NUMBER FROM CN_SRP_PARTICIPANTS_ALL ANALYST WHERE ANALYST.ORG_ID = BG.ORG_ID AND ANALYST.PARTICIPANT_ID = parDetails.ANALYST_ID ), 'FM999999999999999999') AS "BadgeIdOfSCPAnalyst_c", NVL(TO_CHAR(( SELECT DISTINCT B.RESOURCE_PROFILE_ID FROM CN_SRP_PARTICIPANTS_ALL ANALYST JOIN JTF_RS_RESOURCE_PROFILES B ON ANALYST.PARTY_ID = B.PARTY_ID WHERE ANALYST.ORG_ID = BG.ORG_ID AND ANALYST.PARTICIPANT_ID = parDetails.ANALYST_ID ), 'FM999999999999999999'), '0') AS "ResProfileIdOfSCPAnalyst_c", BG.conversionrate AS ConversionRateHomeToUSD_c, LATEST_PLAN_COMPONENT.plan_component_name AS "PlanComponentName_c", ( SELECT DISTINCT B.EMAIL_ADDRESS FROM CN_SRP_PARTICIPANTS_ALL ANALYST JOIN JTF_RS_RESOURCE_PROFILES B ON ANALYST.PARTY_ID = B.PARTY_ID WHERE ANALYST.ORG_ID = BG.ORG_ID AND ANALYST.PARTICIPANT_ID = parDetails.ANALYST_ID ) AS "OwnerEmailId_c", LATEST_PLAN_COMPONENT.ROLE_NAME AS RoleName_c, parDetails.ATTRIBUTE_CHAR2 AS HRStatus_c, parDetails.COUNTRY_CODE AS EmploymentCountry_c, -- BB columns - FIXED NVL usage -- NVL(BB_AMT.BB_HOME_CURRENCIES, '0') AS "BB Home Currencies", -- NVL(BB_AMT.BB_HOME_CURRENCIES_WITH_AMOUNTS, '0') AS "BB Home Currencies with Amounts", -- NVL(BB_AMT.BB_BREAKDOWN_BY_CURRENCY, '0') AS "BB Breakdown by Currency", -- NVL(TO_CHAR(BB_AMT.BB_TOTAL_HOME_CURR_SUM, 'FM9999999990.0000'), '0') AS "BB Total (Mixed Currencies Sum)", NVL(TO_CHAR(BB_AMT.BB_IN_LATEST_HC, 'FM9999999990.0000'), '0') AS BeginningBalance_c, NVL(TO_CHAR( (NVL(TO_NUMBER(BB_AMT.BB_IN_LATEST_HC), 0) + NVL(TO_NUMBER(PTD_EARNINGS_AMT.PTD_EARNINGS_LC), 0)) - NVL(BG.paysheet_payout_LC, 0), 'FM999999999990.0000' ), '0') AS MCAdjustment_c, -- BB_AMT.LATEST_HOME_CURRENCY AS "BB Latest Home Currency", -- BB_AMT.BB_CONVERSION_RATE_AVG AS "BB Avg Conversion Rate", -- NVL(TO_CHAR(BB_AMT.BB_TOTAL_HOME_CURR_SUM, 'FM999999999990.0000'), '0') AS "BEGINNING_BALANCE_LC", NVL(TO_CHAR( NVL(TO_NUMBER(BB_AMT.BB_IN_LATEST_HC), 0) + NVL(TO_NUMBER(PTD_EARNINGS_AMT.PTD_EARNINGS_LC), 0), 'FM999999999990.0000' ), '0') AS CED_c, NVL(TO_CHAR(parDetails.ATTRIBUTE_NUMBER1, 'FM9999999990.0000'), '0') AS HRTI_c, NVL(CASE WHEN parDetails.ATTRIBUTE_NUMBER1 IS NOT NULL AND parDetails.ATTRIBUTE_NUMBER1 <> 0 THEN TO_CHAR( (NVL(TO_NUMBER(BB_AMT.BB_IN_LATEST_HC), 0) / parDetails.ATTRIBUTE_NUMBER1) , 'FM9999999990.0000' ) ELSE '0' END, '0') AS BBOfHRTI_c, LATEST_PLAN_COMPONENT.PB_INCENTIVE_TYPE AS IncentiveType_c, NVL(TO_CHAR( (NVL(TO_NUMBER(BB_AMT.BB_IN_USD), 0) + NVL(PTD_EARNINGS_AMT.PTD_EARNINGS_USD, 0)), 'FM9999999990.0000' ), '0') AS CEDUSD_c, NVL(TO_CHAR(NVL(TO_NUMBER(PTD_EARNINGS_AMT.PTD_EARNINGS_LC), 0), 'FM9999999990.0000'), '0') AS PTDEarnings_c, PN.PARTICIPANT_NAME AS "ParticipantName_c", -- parDetails.COUNTRY_CODE AS "HR Country", /* TO_CHAR(( SELECT PARTY_ID FROM CN_SRP_PARTICIPANTS_ALL ANALYST WHERE ANALYST.ORG_ID = BG.ORG_ID AND ANALYST.PARTICIPANT_ID = parDetails.ANALYST_ID ), 'FM999999999999999999') AS "PartyID_c",*/ parDetails.ATTRIBUTE_CHAR17 AS SalesRP_c, parDetails.ATTRIBUTE_CHAR33 AS ActionCode_c, -- TO_CHAR(pay_cal.PAYROLL_SUBMISSION_END_CST, 'MM/DD/YYYY HH:MI:SS AM') AS "PayrollSubmissionEndCST_c", TO_CHAR((SELECT DISTINCT B.RESOURCE_PROFILE_ID FROM CN_SRP_PARTICIPANTS_ALL ANALYST JOIN JTF_RS_RESOURCE_PROFILES B ON ANALYST.PARTY_ID = B.PARTY_ID WHERE ANALYST.ORG_ID = BG.ORG_ID AND ANALYST.PARTICIPANT_ID = parDetails.ANALYST_ID ), 'FM999999999999999999') as "OraZcxOwner_Id_c" -- TO_CHAR(PAY_CAL.PAYSHEET_CUTOFF_START_DT, 'MM/DD/YYYY HH:MI:SS AM') AS PAYSHEET_CUTOFF_START_DT, -- TO_CHAR(PAY_CAL.PAYSHEET_CUTOFF_DT, 'MM/DD/YYYY HH:MI:SS AM') AS PAYSHEET_CUTOFF_DT, -- TO_CHAR(PAY_CAL.MPA_CUTOFF_START_DT, 'MM/DD/YYYY HH:MI:SS AM') AS "MPACutoffStartCST_c", -- TO_CHAR(PAY_CAL.MPA_CUTOFF_END_DT, 'MM/DD/YYYY HH:MI:SS AM') AS "MPACutoffEndCST_c", -- PRD_ML.BBMultiCurrencyFlag, -- NVL(TO_CHAR(BG.finalPayout, 'FM9999999990.0000'), '0') AS final_payout_LC, -- NVL(TO_CHAR(BG.finalPayout_paysheet, 'FM9999999990.0000'), '0') AS final_payout_PS__LC FROM BG INNER JOIN MULTI_CURRENCY_FLAGS PRD_ML ON BG.PAYRUN_ID = PRD_ML.PAYRUN_ID AND BG.CREDITED_PARTICIPANT_ID = PRD_ML.PARTICIPANT_ID AND BG.PAY_PERIOD_ID = PRD_ML.PERIOD_ID INNER JOIN LATEST_PLAN_COMPONENT ON LATEST_PLAN_COMPONENT.PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID AND LATEST_PLAN_COMPONENT.ORG_ID = BG.ORG_ID AND LATEST_PLAN_COMPONENT.PERIOD_ID = BG.PAY_PERIOD_ID AND LATEST_PLAN_COMPONENT.PAYRUN_ID = BG.PAYRUN_ID LEFT JOIN PTD_EARNINGS_USD ON PTD_EARNINGS_USD.PAYRUN_ID = BG.PAYRUN_ID AND PTD_EARNINGS_USD.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID AND PTD_EARNINGS_USD.ORG_ID = BG.ORG_ID AND PTD_EARNINGS_USD.PAY_PERIOD_ID = BG.PAY_PERIOD_ID LEFT JOIN PTD_EARNINGS_AMT ON PTD_EARNINGS_AMT.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID AND PTD_EARNINGS_AMT.ORG_ID = BG.ORG_ID AND PTD_EARNINGS_AMT.PAY_PERIOD_ID = BG.PAY_PERIOD_ID AND PTD_EARNINGS_AMT.PAYRUN_ID = BG.PAYRUN_ID LEFT JOIN BB_AMT ON BB_AMT.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID AND BB_AMT.PAY_PERIOD_ID = BG.PAY_PERIOD_ID AND BB_AMT.PAYRUN_ID = BG.PAYRUN_ID LEFT JOIN PAY_CAL ON PAY_CAL.PAYMENT_BATCH_NAME = UPPER(LATEST_PLAN_COMPONENT.PAYRUN_NAME) AND PAY_CAL.PAYGROUP_NAME = UPPER(LATEST_PLAN_COMPONENT.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(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE)) 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 INNER JOIN PARTICIPANT_DETAILS_CACHE parDetails ON BG.PAY_DATE BETWEEN parDetails.START_DATE AND NVL(parDetails.END_DATE, BG.PAY_DATE) AND BG.CREDITED_PARTICIPANT_ID = parDetails.PARTICIPANT_ID INNER JOIN PERSON_NUMBERS PN ON PN.participant_id = BG.CREDITED_PARTICIPANT_ID WHERE 1 = 1 -- AND BG.payrun_id = NVL(:payrun_id, BG.payrun_id) AND (BG.PAYRUN_ID IN (:PAYRUN_ID) OR COALESCE(:PAYRUN_ID,'ALL') = 'ALL') -- AND BG.CREDITED_PARTICIPANT_ID = NVL(:participant_id, BG.CREDITED_PARTICIPANT_ID) AND (BG.CREDITED_PARTICIPANT_ID IN (:participant_id) OR COALESCE(:participant_id,'ALL') = 'ALL') AND BG.PAY_PERIOD_ID = NVL(:period_id, BG.PAY_PERIOD_ID) AND (BG.CREDITED_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL') ) base ) rules

Comments

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika