fixed BB numbers query

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 TO_DATE('1900-01-01','YYYY-MM-DD') else TRUNC(SPD.PAY_DATE) END) BETWEEN (CASE when SPD.HOME_CURRENCY_CODE = 'USD' then TO_DATE('1900-01-01','YYYY-MM-DD') else conversion.CONVERSION_START_DATE END) AND (CASE when SPD.HOME_CURRENCY_CODE = 'USD' then TO_DATE('9999-12-31','YYYY-MM-DD') else conversion.CONVERSION_END_DATE END) AND (CASE when SPD.HOME_CURRENCY_CODE = 'USD' then 'USD' else conversion.FROM_CURRENCY END) = (CASE when SPD.HOME_CURRENCY_CODE = 'USD' then 'USD' else SPD.HOME_CURRENCY_CODE END) 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