parent query new fiex 14 11

/* Multi-Currency Paysheet Report - OPTIMIZED WITH FILTERS Aggregates payment data with currency conversions (Home <-> USD) Filters: :payrun_id, :participant_id, :period_id (all optional) */ 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 cspl.participant_id, papf.person_number 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.ATTRIBUTE_CHAR40 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 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(partipant_detail.LATEST_HOME_CURRENCY) AS LATEST_HOME_CURRENCY FROM BASE_PAYMENTS SPD INNER JOIN CONVERSION_RATES_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 LEFT JOIN PARTICIPANT_DETAIL_DATERANGE partipant_detail ON TRUNC(SPD.PAY_DATE) BETWEEN partipant_detail.PD_START_DATE AND NVL(partipant_detail.PD_END_DATE, DATE '9999-12-31') AND SPD.CREDITED_PARTICIPANT_ID = partipant_detail.PARTICIPANT_ID AND SPD.ORG_ID = partipant_detail.PD_ORG_ID AND SPD.HOME_CURRENCY_CODE = partipant_detail.LATEST_HOME_CURRENCY 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 ), -- 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.0000000000' ) 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.0000000000' ) 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.0000000000' ) AS finalPayout_paysheet FROM PAYMENTS_USD after_group INNER 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 PART_PAY.SOURCE_EVENT_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 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, P.HOME_CURRENCY_CODE, -- Group by home currency -- Aggregate amounts in home currency SUM(P.PAY_AMT_PAY_CURR) AS BB_AMOUNT_HOME_CURR, -- Convert to USD 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, -- Get latest home currency (same for all rows of same participant) MAX(PD.CURRENCY_CODE) AS LATEST_HOME_CURRENCY, MAX(P.PAY_DATE) AS PAY_DATE, -- Collect all old period IDs for reference 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) LEFT JOIN CONVERSION_RATES_USD CR_USD ON P.HOME_CURRENCY_CODE != 'USD' AND CR_USD.FROM_CURRENCY = P.HOME_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 -- BB filter: old periods only 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 -- Group by 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, -- Convert USD to Latest Home Currency 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, -- Conversion rate from USD to Latest HC 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, -- Aggregate home currencies (codes only) LISTAGG(HOME_CURRENCY_CODE, ', ') WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS BB_HOME_CURRENCIES, -- Aggregate home currencies with amounts (NEW) LISTAGG( HOME_CURRENCY_CODE || ':' || TO_CHAR(BB_AMOUNT_HOME_CURR, 'FM9999999990.0000000000'), ', ' ) WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS BB_HOME_CURRENCIES_WITH_AMOUNTS, -- Latest home currency MAX(LATEST_HOME_CURRENCY) AS LATEST_HOME_CURRENCY, -- Old period IDs MAX(OLD_PAY_PERIOD_IDS) AS OLD_PAY_PERIOD_IDS, -- Total BB in USD (sum across all currencies) SUM(BB_AMOUNT_USD) AS BB_AMOUNT_USD, -- Formatted BB in USD TO_CHAR( SUM(BB_AMOUNT_USD), 'FM9999999990.0000000000' ) AS BB_IN_USD, -- Total BB in Latest Home Currency (sum across all currencies) SUM(BB_IN_LATEST_HC_NUM) AS BB_IN_LATEST_HC_NUM, -- Formatted total TO_CHAR( SUM(BB_IN_LATEST_HC_NUM), 'FM9999999990.0000000000' ) AS BB_IN_LATEST_HC, -- Average conversion rate (for reference) TO_CHAR( AVG(CONVERSION_RATE_USD_TO_LC), 'FM9999999990.0000000000' ) AS BB_CONVERSION_RATE_AVG, -- Add this to BB_AMT CTE SUM(BB_AMOUNT_HOME_CURR) AS BB_TOTAL_HOME_CURR_SUM, -- Simple sum (not currency-aware!) -- Breakdown by currency (for debugging) - using pipe separator LISTAGG( HOME_CURRENCY_CODE || ':' || TO_CHAR(BB_AMOUNT_HOME_CURR, 'FM9999999990.0000000000'), ' | ' ) 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 all PTD earnings converted to USD 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, -- Capture the latest home currency for conversion back 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 -- Join conversion rates (Home Currency → USD) INNER 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)) ) -- Key filter: PTD = same period transactions AND P.PAY_PERIOD_ID = PB.PAY_PERIOD_ID -- Exclude manual adjustments and payment plans AND P.INCENTIVE_TYPE_CODE NOT IN ('MANUAL_PAY_ADJ', 'PMTPLN') -- Apply parameter filters 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, ptd_usd.LATEST_HOME_CURRENCY, -- Convert USD back to 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.0000000000' ) 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 INNER JOIN CONVERSION_RATES_USD conversion2 ON TRUNC(PB.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.PERIOD_ID = NVL(:period_id, SPFM.PERIOD_ID) ) WHERE LATEST_TI_RECORD = 1 ) SELECT -- Build unique record identifier: PeriodID-PayrunName-BadgeID-IncentiveType 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 || '-' || DECODE( UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE) ) ) AS "RecordName", -- Duplicate of Record Name for reference 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 || '-' || DECODE( UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(LATEST_PLAN_COMPONENT.INCENTIVE_TYPE) ) ) AS "RecordNumber", -- Core payment identifiers BG.PAYRUN_ID AS "PayrunID_c", -- BG.PAYSHEET_ID AS "PayrunID_c", -- COMMENTED OUT: Duplicate column 'Regularcycle' AS "PaymentCycle_c", BG.PAY_PERIOD_ID AS "PayPeriodID_c", TO_DATE(BG.PAY_DATE, 'MM/DD/YYYY') AS "PayDate_c", -- Get badge ID from HR system PN.person_number AS "BadgeIDWorkerNumber_c", BG.CREDITED_PARTICIPANT_ID AS "ParticipantId_c", -- Role and employment info LATEST_PLAN_COMPONENT.ROLE_NAME AS "RoleName_c", parDetails.ATTRIBUTE_CHAR2 AS "HRStatus_c", parDetails.ATTRIBUTE_CHAR40 AS "EmploymentCountry_c", TO_DATE(LATEST_PLAN_COMPONENT.START_DATE, 'MM/DD/YYYY') AS "RoleStartDate_c", TO_DATE(LATEST_PLAN_COMPONENT.END_DATE, 'MM/DD/YYYY') AS "RoleEndDate_c", -- Payment batch details BG.PAYRUN_NAME AS "PaymentBatch_c", BG.PAY_GROUP_NAME AS "PayGroup_c", -- BB columns BB_AMT.BB_HOME_CURRENCIES AS "BB Home Currencies", BB_AMT.BB_HOME_CURRENCIES_WITH_AMOUNTS AS "BB Home Currencies with Amounts", BB_AMT.BB_BREAKDOWN_BY_CURRENCY AS "BB Breakdown by Currency", -- In final SELECT TO_CHAR(BB_AMT.BB_TOTAL_HOME_CURR_SUM, 'FM9999999990.0000000000') AS "BB Total (Mixed Currencies Sum)", -- BB columns BB_AMT.BB_IN_LATEST_HC AS "BeginningBalance_c", BB_AMT.LATEST_HOME_CURRENCY AS "BB Latest Home Currency", BB_AMT.BB_CONVERSION_RATE_AVG AS "BB Avg Conversion Rate", -- Financial amounts in local currency TO_CHAR(BB_AMT.BB_TOTAL_HOME_CURR_SUM, 'FM9999999990.0000000000') AS "BEGINNING_BALANCE_LC", TO_CHAR(NVL(PTD_EARNINGS_AMT.PTD_EARNINGS_LC, 0), 'FM9999999990.0000000000') AS "PTDEarnings_c", TO_CHAR( (NVL(BB_AMT.BB_TOTAL_HOME_CURR_SUM, 0) + NVL(TO_NUMBER(PTD_EARNINGS_AMT.PTD_EARNINGS_LC), 0)), 'FM9999999990.0000000000' ) AS "CED_c", TO_CHAR(BG.paysheet_payout_LC, 'FM9999999990.0000000000') AS "TotalPaysheetAmount_c", BG.LATEST_HOME_CURRENCY AS "CurrencyCode", -- Plan component and TI LATEST_PLAN_COMPONENT.PLAN_COMPONENT_ID AS "PlanComponentId_c", TO_CHAR(PM_TI.Prorated_MonthlyTI, 'FM9999999990.0000000000') AS "ProratedTI_c", TO_CHAR(parDetails.ATTRIBUTE_NUMBER1, 'FM9999999990.0000000000') AS "HRTI_c", -- BB % of HR TI - NOW CALCULATED CASE WHEN parDetails.ATTRIBUTE_NUMBER1 IS NOT NULL AND parDetails.ATTRIBUTE_NUMBER1 <> 0 THEN TO_CHAR( (TO_NUMBER(BB_AMT.BB_IN_LATEST_HC) / parDetails.ATTRIBUTE_NUMBER1) * 100, 'FM9999999990.0000000000' ) || '%' ELSE 'N/A' END AS "BBOfHRTI_c", -- USD amounts TO_CHAR( (NVL(TO_NUMBER(BB_AMT.BB_IN_USD), 0) + NVL(PTD_EARNINGS_AMT.PTD_EARNINGS_USD, 0)), 'FM9999999990.0000000000' ) AS "CEDUSD_c", TO_CHAR(PTD_EARNINGS_AMT.PTD_EARNINGS_USD, 'FM9999999990.0000000000') AS "PTDUSD_c", BB_AMT.BB_IN_USD AS "BBUSD_c", -- Incentive classification LATEST_PLAN_COMPONENT.INCENTIVE_TYPE AS "IncentiveType_c", 'MAV_DIR_MPA_CODE_1008' AS "MPAReasonCode_c", -- Multi-currency flags PRD_ML.MULTI_CURR_YN AS "MultiCurrency_c", -- Calendar cutoffs 'TBD' AS "PaysheetAutomatedRefreshEndCST_c", TO_CHAR(BG.PAY_AMT_PAY_CURR_USD, 'FM9999999990.0000000000') AS "TotalPaysheetAmountUSD_c", -- Analyst info ( 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 ) AS "BadgeIdOfSCPAnalyst_c", ( 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 ) AS "ResProfileIdOfSCPAnalyst_c", -- Conversion rate: home currency to USD BG.conversionrate AS ConversionRateHomeToUSD_c, -- Worker details PN.person_number AS "Worker Number", parDetails.COUNTRY_CODE AS "HR Country", ( SELECT PARTY_ID FROM CN_SRP_PARTICIPANTS_ALL ANALYST WHERE ANALYST.ORG_ID = BG.ORG_ID AND ANALYST.PARTICIPANT_ID = parDetails.ANALYST_ID ) AS "PartyID_c", parDetails.ATTRIBUTE_CHAR17 AS "SalesRP_c", -- Additional details LATEST_PLAN_COMPONENT.plan_component_name AS "PlanComponentName_c", TO_DATE(pay_cal.PAYROLL_SUBMISSION_END_CST, 'MM/DD/YYYY') AS "PayrollSubmissionEndCST_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", -- Paysheet cutoff dates PAY_CAL.PAYSHEET_CUTOFF_START_DT, PAY_CAL.PAYSHEET_CUTOFF_DT, TO_DATE(PAY_CAL.MPA_CUTOFF_START_DT, 'MM/DD/YYYY') AS "MPACutoffStartCST_c", TO_DATE(PAY_CAL.MPA_CUTOFF_END_DT, 'MM/DD/YYYY') AS "MPACutoffEndCST_c", -- Multi-currency flags (detailed) PRD_ML.BBMultiCurrencyFlag, -- Final payout amounts in local currency BG.finalPayout AS final_payout_LC, BG.finalPayout_paysheet 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 -- PTD USD 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.CREDITED_PARTICIPANT_ID = NVL(:participant_id, BG.CREDITED_PARTICIPANT_ID) AND BG.PAY_PERIOD_ID = NVL(:period_id, BG.PAY_PERIOD_ID);

Comments

Popular posts from this blog

Child query new

child query latest -

final simple where