PTD_EARNINGS_AMT MC
PTD_EARNINGS_AMT AS (
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,
-- Convert USD back to Latest Home Currency
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
)
)
),
'00000000.000000000'
) AS PTD_EARNINGS_LC
FROM PTD_EARNINGS_USD ptd_usd
INNER JOIN CN_TP_PAYRUNS_ALL PB
ON PB.PAYRUN_ID = ptd_usd.PAYRUN_ID
AND PB.ORG_ID = ptd_usd.ORG_ID
INNER JOIN CONVERSION_RATES_USD conversion2
ON TRUNC(PB.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE
AND conversion2.CONVERSION_END_DATE
AND conversion2.FROM_CURRENCY = ptd_usd.LATEST_HOME_CURRENCY
)
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
PTD_EARNINGS_AMT.PTD_EARNINGS_LC AS "PTD earnings LC",
PTD_EARNINGS_AMT.PTD_EARNINGS_USD AS "PTD earnings USD",
Comments
Post a Comment