sql- with child condition
select * from (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 ) MCChild
where exists
(
-- Subquery to identify multi-currency participants
SELECT PARTICIPANT_ID
,PAYRUN_ID
,ORG_ID
,PERIOD_ID
,MULTI_CURR_YN
FROM (
SELECT MULTI_CURR.PARTICIPANT_ID
,MULTI_CURR.PAYRUN_ID
,MULTI_CURR.ORG_ID
,MULTI_CURR.PERIOD_ID
,CASE WHEN COUNT(MULTI_CURR.HOME_CURRENCY_CODE) > 1
THEN 'Y'
ELSE 'N'
END AS MULTI_CURR_YN
FROM (
SELECT PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID
,PD.CURRENCY_CODE AS HOME_CURRENCY_CODE
,PART_PAY.PAYRUN_ID
,PART_PAY.ORG_ID
,PART_PAY.PAYMENT_BATCH_PERIOD AS PERIOD_ID
,SUM(ROUND(PART_PAY.COMM_AMT_CALC_CURR, 2)) AS TotalAmt
FROM CN_TP_PARTICIPANT_PAY_EXP_V PART_PAY
INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD
ON PD.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID
AND PART_PAY.SOURCE_EVENT_DATE IS NOT NULL
AND PART_PAY.SOURCE_EVENT_DATE BETWEEN PD.START_DATE
AND NVL(PD.END_DATE, PART_PAY.SOURCE_EVENT_DATE)
WHERE 1 = 1
-- Add filters here if needed:
-- AND PART_PAY.CREDITED_PARTICIPANT_ID IN (134107, 134136)
GROUP BY PART_PAY.CREDITED_PARTICIPANT_ID
,PD.CURRENCY_CODE
,PART_PAY.PAYRUN_ID
,PART_PAY.ORG_ID
,PART_PAY.PAYMENT_BATCH_PERIOD
HAVING SUM(ROUND(PART_PAY.COMM_AMT_CALC_CURR, 2)) <> 0
) MULTI_CURR
GROUP BY MULTI_CURR.PARTICIPANT_ID
,MULTI_CURR.PAYRUN_ID
,MULTI_CURR.ORG_ID
,MULTI_CURR.PERIOD_ID
) PRD_ML1
WHERE PRD_ML1.MULTI_CURR_YN = 'Y'
ON MCChild.PAYEE_PARTICIPANT_ID = PRD_ML1.PARTICIPANT_ID
AND MCChild.PAYRUN_ID = PRD_ML1.PAYRUN_ID
AND MCChild.PAYMENT_BATCH_PERIOD = PRD_ML1.PERIOD_ID
) PRD_ML
Comments
Post a Comment