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
Post a Comment