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 -- ... (other CTEs unchanged) ... -- 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 ), -- ... (rest of the query unchanged) ...

Comments

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika