bb amount usd - home currency handle case

select nvl(max(conversion.CONVERSION_RATE),1) ,sum(spd.PAY_AMT_PAY_CURR) "PTD Earning LC" ,sum(spd.PAY_AMT_PAY_CURR)*max(conversion.CONVERSION_RATE) "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 , (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 conversion.TO_CURRENCY = 'USD') conversion 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) AND (Case when SPD.HOME_CURRENCY_CODE == 'USD' then '-1' else TRUNC(SPD.PAY_DATE) end case) BETWEEN (Case when SPD.HOME_CURRENCY_CODE == 'USD' then '-2' else conversion.CONVERSION_START_DATE end case ) AND (Case when SPD.HOME_CURRENCY_CODE == 'USD' then '0' else conversion.CONVERSION_END_DATE end case) AND (Case when SPD.HOME_CURRENCY_CODE == 'USD' then '-1' else conversion.FROM_CURRENCY end case) = (Case when SPD.HOME_CURRENCY_CODE == 'USD' then '-1' else SPD.HOME_CURRENCY_CODE end case) 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