with left join

select nvl(max(CASE when SPD.HOME_CURRENCY_CODE = 'USD' then 1 else conversion.CONVERSION_RATE END),1) "Conversion Rate" ,sum(spd.PAY_AMT_PAY_CURR) "PTD Earning LC" ,sum(spd.PAY_AMT_PAY_CURR)*max(CASE when SPD.HOME_CURRENCY_CODE = 'USD' then 1 else conversion.CONVERSION_RATE END) "PTD Earning (USD)" --,spd.BONUS_PTD "PTD Earning MC" ,spd.HOME_CURRENCY_CODE ,spd.PAY_CURRENCY_CODE ,spd.PAYEE_PARTICIPANT_ID --,spd.SOURCE_EVENT_DATE ,spd.PLAN_COMPONENT_ID ,spd.PLAN_COMPONENT_NAME ,spd.payrun_id ,spd.payment_batch_period from CN_TP_PARTICIPANT_PAY_EXP_V spd LEFT JOIN (SELECT CR.FROM_CURRENCY "FROM_CURRENCY", CR.TO_CURRENCY "TO_CURRENCY", CR.CONVERSION_START_DATE "CONVERSION_START_DATE", CR.CONVERSION_END_DATE "CONVERSION_END_DATE", CR.CONVERSION_RATE "CONVERSION_RATE" FROM CN_CONVERSION_RATES CR, CN_CONVERSION_TYPES_TL CT where CT.CONVERSION_TYPE_ID = CR.CONVERSION_TYPE_ID AND CT.CONVERSION_TYPE_ID = '-1001' AND CT.LANGUAGE='US' AND CR.TO_CURRENCY = 'USD') conversion ON (SPD.HOME_CURRENCY_CODE != 'USD' AND conversion.FROM_CURRENCY = SPD.HOME_CURRENCY_CODE AND TRUNC(SPD.PAY_DATE) BETWEEN conversion.CONVERSION_START_DATE AND conversion.CONVERSION_END_DATE) where 1= 1 and spd.payrun_id = nvl(:payrun_id, spd.payrun_id) and spd.PAYEE_PARTICIPANT_ID = nvl(:participant_id, spd.PAYEE_PARTICIPANT_ID) and spd.payment_batch_period = nvl(:period_id,spd.payment_batch_period) group by HOME_CURRENCY_CODE ,PAY_CURRENCY_CODE ,PAYEE_PARTICIPANT_ID --,SOURCE_EVENT_DATE --,PAY_PERIOD_ID ,PLAN_COMPONENT_ID ,PLAN_COMPONENT_NAME ,payment_batch_period ,spd.payrun_id

Comments

Popular posts from this blog

Child query new

child query latest -

final simple where