latest one 4-final query for BB MC and multi currency flag
SELECT
NVL(bb_MultiC.PAYRUN_ID, PRD_ML.PAYRUN_ID) AS PAYRUN_ID,
NVL(bb_MultiC.PAYMENT_BATCH_PERIOD, PRD_ML.PERIOD_ID) AS PERIOD_ID,
NVL(bb_MultiC.PAYEE_PARTICIPANT_ID, PRD_ML.PARTICIPANT_ID) AS PARTICIPANT_ID,
bb_MultiC.BBMultiCurrencyFlag,
PRD_ML.MULTI_CURR_YN
FROM
(
-- Subquery to identify multi-currency scenarios in batch payments
SELECT pay_1.PAYRUN_ID
,pay_1.PAYMENT_BATCH_PERIOD
,pay_1.PAYEE_PARTICIPANT_ID
,'Y' AS BBMultiCurrencyFlag
FROM
(
-- Get distinct paAyment records with home currency
SELECT P.PAYRUN_ID
,PAYMENT_BATCH_PERIOD
,PAYEE_PARTICIPANT_ID
,HOME_CURRENCY_CODE
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
WHERE 1 = 1
-- Add filters here if needed:
-- AND PAYEE_PARTICIPANT_ID = 106000
-- AND PAYRUN_ID = 300000494369243
GROUP BY PAYMENT_BATCH_PERIOD
,PAYEE_PARTICIPANT_ID
,PAYRUN_ID
,HOME_CURRENCY_CODE
) pay_1
INNER JOIN
(
-- Get payments where pay period differs from batch period
SELECT PAYRUN_ID
,PAYMENT_BATCH_PERIOD
,PAYEE_PARTICIPANT_ID
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
WHERE PAY_PERIOD_ID <> PAYMENT_BATCH_PERIOD
-- Add filters here if needed:
-- AND PAYEE_PARTICIPANT_ID = 106000
-- AND PAYRUN_ID = 300000494369243
GROUP BY PAYMENT_BATCH_PERIOD
,PAYEE_PARTICIPANT_ID
,PAYRUN_ID
) pay_2
ON pay_1.PAYRUN_ID = pay_2.PAYRUN_ID
AND pay_1.PAYMENT_BATCH_PERIOD = pay_2.PAYMENT_BATCH_PERIOD
AND pay_1.PAYEE_PARTICIPANT_ID = pay_2.PAYEE_PARTICIPANT_ID
GROUP BY pay_1.PAYRUN_ID
,pay_1.PAYMENT_BATCH_PERIOD
,pay_1.PAYEE_PARTICIPANT_ID
HAVING COUNT(1) > 1
) bb_MultiC
FULL OUTER JOIN
(
-- 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'
) PRD_ML
ON bb_MultiC.PAYEE_PARTICIPANT_ID = PRD_ML.PARTICIPANT_ID
AND bb_MultiC.PAYRUN_ID = PRD_ML.PAYRUN_ID
AND bb_MultiC.PAYMENT_BATCH_PERIOD = PRD_ML.PERIOD_ID
Comments
Post a Comment