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

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika