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