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

Popular posts from this blog

Child query new

child query latest -

final simple where