New child

select ,sum(spd.PAY_AMT_PAY_CURR) "PTD Earning LC" ,spd.HOME_CURRENCY_CODE ,nvl(max(CASE when SPD.HOME_CURRENCY_CODE = 'USD' then 1 else conversion.CONVERSION_RATE END),1) "Conversion Rate (LC_to_USD)" ,sum(spd.PAY_AMT_PAY_CURR)*max(CASE when SPD.HOME_CURRENCY_CODE = 'USD' then 1 else conversion.CONVERSION_RATE END) "PTD Earning (USD)" ,pd.CURRENCY_CODE latest_currency_code /* ,to_char((max(CASE when pd.CURRENCY_CODE = 'USD' then 1 else 1/conversion2.CONVERSION_RATE END)),'0000000000000.0000000000000') "Conversion Rate (USD_to_Latest_LC)" ,to_char(sum(spd.PAY_AMT_PAY_CURR)*(max(CASE when SPD.HOME_CURRENCY_CODE = 'USD' then 1 else conversion.CONVERSION_RATE END)) *(max(CASE when pd.CURRENCY_CODE = 'USD' then 1 else 1/conversion2.CONVERSION_RATE END)), '0000000000000.0000000000000') "PTD Earning (Latest HC)" */ ,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 ,max( PS.PMT_TXN_AMT_DUE) "MC Earnings" from CN_TP_PARTICIPANT_PAY_EXP_V spd inner join CN_SRP_PARTICIPANT_DETAILS_ALL pd on spd.PAYEE_PARTICIPANT_ID = pd.PARTICIPANT_ID and spd.pay_date between pd.start_date and nvl(pd.end_date, spd.pay_date) INNER JOIN CN_TP_PAYSHEETS_ALL PS ON PS.PARTICIPANT_ID = spd.CREDITED_PARTICIPANT_ID AND PS.PAYRUN_ID = spd.PAYRUN_ID AND PS.QUOTA_ID IS NULL LEFT JOIN -- lc_to_usd (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) LEFT JOIN --usd_to_latest_lc (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') conversion2 ON (SPD.HOME_CURRENCY_CODE != 'USD' AND conversion2.FROM_CURRENCY = pd.CURRENCY_CODE AND TRUNC(SPD.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE AND conversion2.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 spd.HOME_CURRENCY_CODE ,spd.PAY_CURRENCY_CODE ,spd.PAYEE_PARTICIPANT_ID --,SOURCE_EVENT_DATE --,PAY_PERIOD_ID --,spd.PLAN_COMPONENT_ID --,spd.PLAN_COMPONENT_NAME ,spd.payment_batch_period ,spd.payrun_id ,pd.CURRENCY_CODE

Comments

Popular posts from this blog

Child query new

child query latest -

final simple where