fixed
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,
-- Perform the final conversion to Local Currency (LC)
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 (
-- LEVEL 1: Aggregate earnings to USD and determine the LATEST currency code
SELECT
P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID,
P.ORG_ID,
P.PAY_PERIOD_ID,
PB.PAY_DATE, -- Carried forward for the outer join to conversion2
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,
-- Correctly identify the latest currency code based on the filtered PD records
MAX(PD.CURRENCY_CODE) KEEP (DENSE_RANK LAST ORDER BY PD.START_DATE) 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 (
-- Inner conversion to get everything into USD first
SELECT CR.FROM_CURRENCY, CR.CONVERSION_RATE, CR.CONVERSION_START_DATE, CR.CONVERSION_END_DATE
FROM CN_CONVERSION_RATES CR
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'
) conv_inner
ON TRUNC(NVL(P.SOURCE_EVENT_DATE, PB.PAY_DATE)) 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.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID)
AND P.CREDITED_PARTICIPANT_ID = NVL(:participant_id, P.CREDITED_PARTICIPANT_ID)
AND P.PAY_PERIOD_ID = NVL(:period_id, P.PAY_PERIOD_ID)
GROUP BY
P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID,
P.ORG_ID,
P.PAY_PERIOD_ID,
PB.PAY_DATE
) ptd_usd
-- LEVEL 2: Join with conversion rates again to convert the total USD back to the LATEST Local Currency
LEFT JOIN (
SELECT CR.FROM_CURRENCY, CR.CONVERSION_RATE, CR.CONVERSION_START_DATE, CR.CONVERSION_END_DATE
FROM CN_CONVERSION_RATES CR
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'
) 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
Comments
Post a Comment