Double oter join
select
bb_MultiC.*
,PRD_ML.MULTI_CURR_YN
from
(
select pay_1.PAYRUN_ID
,pay_1.PAYMENT_BATCH_PERIOD
, pay_1.PAYEE_PARTICIPANT_ID
, 'Y' BBMultiCurrencyFlag
from
(SELECT P.PAYRUN_ID
,PAYMENT_BATCH_PERIOD
, PAYEE_PARTICIPANT_ID
, HOME_CURRENCY_CODE
from
CN_TP_PARTICIPANT_PAY_EXP_V P
where 1 =1
--PAYEE_PARTICIPANT_ID = 106000
--and PAYRUN_ID = 300000494369243
group by
PAYMENT_BATCH_PERIOD
,PAYEE_PARTICIPANT_ID
,PAYRUN_ID
,HOME_CURRENCY_CODE
--having count(HOME_CURRENCY_CODE) >1
) pay_1
,
(SELECT PAYRUN_ID
,PAYMENT_BATCH_PERIOD
,PAYEE_PARTICIPANT_ID
from
CN_TP_PARTICIPANT_PAY_EXP_V P
where
--P.CREDITED_PARTICIPANT_ID = 106000
PAY_PERIOD_ID <> PAYMENT_BATCH_PERIOD
--and PAYRUN_ID = 300000494369243
group by
PAYMENT_BATCH_PERIOD
,PAYEE_PARTICIPANT_ID
,PAYRUN_ID
) pay_2
where 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 ,
---prd_mc
(
select * 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 "PARTICIPANT_ID"
, Pd.CURRENCY_CODE "HOME_CURRENCY_CODE"
,PART_PAY.PAYRUN_ID "PAYRUN_ID"
,PART_PAY.ORG_ID
,PART_PAY.PAYMENT_BATCH_PERIOD "PERIOD_ID"
,SUM(round(PART_PAY.COMM_AMT_CALC_CURR,2)) "TotalAmt"
FROM CN_TP_PARTICIPANT_PAY_EXP_V PART_PAY
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
--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,
====
WITH
-- ==============================================================
-- 1️⃣ Base: Participants with payrun / batch / currency combinations
-- ==============================================================
pay_1 AS (
SELECT
P.PAYRUN_ID,
P.PAYMENT_BATCH_PERIOD,
P.PAYEE_PARTICIPANT_ID,
P.HOME_CURRENCY_CODE
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
GROUP BY
P.PAYMENT_BATCH_PERIOD,
P.PAYEE_PARTICIPANT_ID,
P.PAYRUN_ID,
P.HOME_CURRENCY_CODE
),
-- ==============================================================
-- 2️⃣ Participants where pay period ≠ batch period
-- ==============================================================
pay_2 AS (
SELECT
P.PAYRUN_ID,
P.PAYMENT_BATCH_PERIOD,
P.PAYEE_PARTICIPANT_ID
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
WHERE P.PAY_PERIOD_ID <> P.PAYMENT_BATCH_PERIOD
GROUP BY
P.PAYMENT_BATCH_PERIOD,
P.PAYEE_PARTICIPANT_ID,
P.PAYRUN_ID
),
-- ==============================================================
-- 3️⃣ Identify multi-period anomalies
-- ==============================================================
bb_MultiC AS (
SELECT
pay_1.PAYRUN_ID,
pay_1.PAYMENT_BATCH_PERIOD,
pay_1.PAYEE_PARTICIPANT_ID,
'Y' AS BBMultiCurrencyFlag
FROM pay_1
JOIN 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
),
-- ==============================================================
-- 4️⃣ Participants with multi-currency transactions
-- ==============================================================
multi_curr_base AS (
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
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)
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
),
prd_ml AS (
SELECT
PARTICIPANT_ID,
PAYRUN_ID,
ORG_ID,
PERIOD_ID,
CASE
WHEN COUNT(HOME_CURRENCY_CODE) > 1 THEN 'Y'
ELSE 'N'
END AS MULTI_CURR_YN
FROM multi_curr_base
GROUP BY
PARTICIPANT_ID,
PAYRUN_ID,
ORG_ID,
PERIOD_ID
)
-- ==============================================================
-- 5️⃣ Final output — include all from both bb_MultiC & prd_ml
-- ==============================================================
SELECT
NVL(bb.PAYRUN_ID, prd.PAYRUN_ID) AS PAYRUN_ID,
NVL(bb.PAYMENT_BATCH_PERIOD, prd.PERIOD_ID) AS PERIOD_ID,
NVL(bb.PAYEE_PARTICIPANT_ID, prd.PARTICIPANT_ID) AS PARTICIPANT_ID,
bb.BBMultiCurrencyFlag,
prd.MULTI_CURR_YN
FROM bb_MultiC bb
FULL OUTER JOIN prd_ml prd
ON prd.PARTICIPANT_ID = bb.PAYEE_PARTICIPANT_ID
AND prd.PAYRUN_ID = bb.PAYRUN_ID
AND prd.PERIOD_ID = bb.PAYMENT_BATCH_PERIOD
ORDER BY 1, 2, 3;
MULTI_CURR.PAYRUN_ID,
MULTI_CURR.ORG_ID,
MULTI_CURR.PERIOD_ID
) PRD_ML1
where PRD_ML1.MULTI_CURR_YN = 'Y'
) PRD_ML
where PRD_ML.PARTICIPANT_ID(+) = bb_MultiC.PAYEE_PARTICIPANT_ID(+)
and PRD_ML.PAYRUN_ID(+) = bb_MultiC.PAYRUN_ID(+)
--PRD_ML.ORG_ID,
and PRD_ML.PERIOD_Id(+) = bb_MultiC.PAYMENT_BATCH_PERIOD(+)
Comments
Post a Comment