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
Post a Comment