new big query

/* Multi-Currency Paysheet Report - OPTIMIZED WITH FILTERS AND IMPROVED LOGIC Aggregates payment data with currency conversions (Home <-> USD) Filters: :payrun_id, :participant_id, :period_id (all optional) Improvements: Merged redundant CTEs, added PTD multi-currency breakdowns, moved subqueries to CTEs for performance. Fix: Resolved ORA-00934 by removing aggregate functions from JOIN ON clause and fixing double-counting in SUM. */ WITH -- Materialize conversion rates once CONVERSION_RATES_USD AS ( SELECT CR.FROM_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 (merged from PARTICIPANT_DETAIL_DATERANGE) PARTICIPANT_DETAILS 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, a.HR_PRIMARY_WORKER_NUMBER 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 (merged BASE_PAYMENTS and PAYMENTS_USD) PAYMENTS_AGGREGATED 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.PAYSHEET_ID, P.PAYSHEET_STATUS, SUM( P.PAY_AMT_PAY_CURR * NVL( (CASE WHEN PD.CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END), 1 ) ) AS PAY_AMT_PAY_CURR_USD, MAX( P.PMT_TXN_AMT_DUE * NVL( (CASE WHEN PD.CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END), 1 ) ) AS paysheet_payout_usd, MAX(P.PMT_TXN_AMT_DUE) AS paysheet_payout_LC, 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_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 LEFT JOIN CONVERSION_RATES_USD conversion ON TRUNC(PB.PAY_DATE) BETWEEN conversion.CONVERSION_START_DATE AND conversion.CONVERSION_END_DATE AND conversion.FROM_CURRENCY = PD.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 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) GROUP BY 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.PAYSHEET_ID, P.PAYSHEET_STATUS, PD.CURRENCY_CODE ), -- 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_AGGREGATED 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 (unchanged, but added comment for clarity) 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 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 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 ), 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 ), 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 (improved: added per-currency aggregation for multi-currency consistency) PTD_EARNINGS_USD AS ( SELECT P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAY_PERIOD_ID, PD.CURRENCY_CODE AS HOME_CURRENCY_CODE, SUM( P.PAY_AMT_PAY_CURR * NVL( (CASE WHEN PD.CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END), 1 ) ) AS PTD_EARNINGS_USD_PER_CURRENCY, SUM( P.PAY_AMT_PAY_CURR * NVL( (CASE WHEN PD.CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END), 1 ) ) OVER (PARTITION BY P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAY_PERIOD_ID) AS PTD_EARNINGS_USD_TOTAL, MAX(PD.CURRENCY_CODE) OVER (PARTITION BY P.PAYRUN_ID, P.CREDITED_PARTICIPANT_ID, P.ORG_ID, PB.PAY_PERIOD_ID) 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 = PD.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, PD.CURRENCY_CODE ), -- PTD earnings MC (improved: aggregate to single row with lists for multi-currency; fixed ORA-00934 and double-counting) PTD_EARNINGS_AMT AS ( SELECT PAYRUN_ID, CREDITED_PARTICIPANT_ID, ORG_ID, PAY_PERIOD_ID, LISTAGG(HOME_CURRENCY_CODE, ', ') WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS PTD_HOME_CURRENCIES, LISTAGG( HOME_CURRENCY_CODE || ':' || TO_CHAR(PTD_EARNINGS_USD_PER_CURRENCY, 'FM9999999990.0000'), ', ' ) WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS PTD_HOME_CURRENCIES_WITH_AMOUNTS, MAX(LATEST_HOME_CURRENCY) AS LATEST_HOME_CURRENCY, MAX(PTD_EARNINGS_USD_TOTAL) AS PTD_EARNINGS_USD_TOTAL, -- Fixed: Use MAX to avoid double-counting TO_CHAR( MAX(PTD_EARNINGS_USD_TOTAL) * ( -- Fixed: Use MAX CASE WHEN MAX(LATEST_HOME_CURRENCY) = 'USD' THEN 1 ELSE 1 / NVL( (SELECT CONVERSION_RATE FROM CONVERSION_RATES_USD WHERE FROM_CURRENCY = MAX(LATEST_HOME_CURRENCY) AND TRUNC(SYSDATE) BETWEEN CONVERSION_START_DATE AND CONVERSION_END_DATE), 1 ) END ), 'FM9999999990.0000' ) AS PTD_EARNINGS_LC FROM PTD_EARNINGS_USD GROUP BY PAYRUN_ID, CREDITED_PARTICIPANT_ID, ORG_ID, PAY_PERIOD_ID ), -- 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 ), -- Analyst details (moved from subqueries for performance) ANALYST_DETAILS AS ( SELECT DISTINCT ANALYST.ORG_ID, ANALYST.PARTICIPANT_ID AS ANALYST_PARTICIPANT_ID, ANALYST.HR_PRIMARY_WORKER_NUMBER, B.RESOURCE_PROFILE_ID, B.EMAIL_ADDRESS FROM CN_SRP_PARTICIPANTS_ALL ANALYST LEFT JOIN JTF_RS_RESOURCE_PROFILES B ON ANALYST.PARTY_ID = B.PARTY_ID ) SELECT 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", 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", BG.PAYRUN_ID AS "PayrunID_c", 'Regularcycle' AS "PaymentCycle_c", BG.PAY_PERIOD_ID AS "PayPeriodID_c", TO_CHAR(BG.PAY_DATE, 'MM/DD/YYYY') AS "PayDate_c", PN.person_number AS "BadgeIDWorkerNumber_c", BG.CREDITED_PARTICIPANT_ID AS "ParticipantId_c", LATEST_PLAN_COMPONENT.ROLE_NAME AS "RoleName_c", parDetails.ATTRIBUTE_CHAR2 AS "HRStatus_c", parDetails.COUNTRY_CODE AS "EmploymentCountry_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(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(BB_AMT.BB_IN_LATEST_HC, '0') AS "BeginningBalance_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(PTD_EARNINGS_AMT.PTD_EARNINGS_LC), 0), 'FM9999999990.0000'), '0') AS "PTDEarnings_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 "CED_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( 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", NVL(TO_CHAR(BG.paysheet_payout_LC, 'FM9999999990.0000'), '0') AS "TotalPaysheetAmount_c", BG.LATEST_HOME_CURRENCY AS "CurrencyCode", LATEST_PLAN_COMPONENT.PLAN_COMPONENT_ID AS "PlanComponentId_c", NVL(TO_CHAR(PM_TI.Prorated_MonthlyTI, 'FM9999999990.0000'), '0') AS "ProratedTI_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) * 100, 'FM9999999990.0000' ) ELSE '0' END, '0') AS "BBOfHRTI_c", NVL(TO_CHAR( (NVL(TO_NUMBER(BB_AMT.BB_IN_USD), 0) + NVL(PTD_EARNINGS_AMT.PTD_EARNINGS_USD_TOTAL, 0)), 'FM9999999990.0000' ), '0') AS "CEDUSD_c", NVL(TO_CHAR(PTD_EARNINGS_AMT.PTD_EARNINGS_USD_TOTAL, 'FM9999999990.0000'), '0') AS "PTDUSD_c", NVL(BB_AMT.BB_IN_USD, '0') AS "BBUSD_c", LATEST_PLAN_COMPONENT.INCENTIVE_TYPE AS "IncentiveType_c", PRD_ML.MULTI_CURR_YN AS "MultiCurrency_c", TO_CHAR(pay_cal.PAYSHEET_CUTOFF_DT, 'MM/DD/YYYY HH:MI:SS AM') AS "PaysheetAutomatedRefreshEndCST_c", NVL(TO_CHAR(BG.PAY_AMT_PAY_CURR_USD, 'FM9999999990.0000'), '0') AS "TotalPaysheetAmountUSD_c", ANALYST.HR_PRIMARY_WORKER_NUMBER AS "BadgeIdOfSCPAnalyst_c", ANALYST.RESOURCE_PROFILE_ID AS "ResProfileIdOfSCPAnalyst_c", BG.conversionrate AS ConversionRateHomeToUSD_c, PN.person_number AS "Worker Number", parDetails.COUNTRY_CODE AS "HR Country", parDetails.ANALYST_ID AS "PartyID_c", -- Simplified; adjust if PARTY_ID is needed parDetails.ATTRIBUTE_CHAR17 AS "SalesRP_c", LATEST_PLAN_COMPONENT.PLAN_COMPONENT_NAME AS "PlanComponentName_c", TO_CHAR(pay_cal.PAYROLL_SUBMISSION_END_CST, 'MM/DD/YYYY HH:MI:SS AM') AS "PayrollSubmissionEndCST_c", ANALYST.EMAIL_ADDRESS AS "OwnerEmailId_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, -- New PTD multi-currency columns for consistency NVL(PTD_EARNINGS_AMT.PTD_HOME_CURRENCIES, '0') AS "PTD Home Currencies", NVL(PTD_EARNINGS_AMT.PTD_HOME_CURRENCIES_WITH_AMOUNTS, '0') AS "PTD Home Currencies with Amounts" 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_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 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 LEFT JOIN ANALYST_DETAILS ANALYST ON ANALYST.ORG_ID = BG.ORG_ID AND ANALYST.ANALYST_PARTICIPANT_ID = parDetails.ANALYST_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

dev7 from eror

EPA

Unified Currency Report- Krithika