dw1
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.PAYRUN_ID,
P.PAYMENT_BATCH_PERIOD,
P.PAYEE_PARTICIPANT_ID,
P.HOME_CURRENCY_CODE
),
-- ==============================================================
-- 2. Participants where pay period ≠ batch period
-- Flag = 2 only if two distinct home currencies exist
-- ==============================================================
pay_2 AS (
SELECT
P.PAYRUN_ID,
P.PAYMENT_BATCH_PERIOD,
P.PAYEE_PARTICIPANT_ID,
CASE
WHEN COUNT(DISTINCT P.HOME_CURRENCY_CODE) = 2 THEN 2
ELSE 1
END AS pay_2
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
WHERE P.PAY_PERIOD_ID <> P.PAYMENT_BATCH_PERIOD
GROUP BY
P.PAYRUN_ID,
P.PAYMENT_BATCH_PERIOD,
P.PAYEE_PARTICIPANT_ID
),
-- ==============================================================
-- 3. Identify participants with multi-currency 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
WHERE pay_2.pay_2 = 2 -- ✅ only pick dual-currency cases
),
-- ==============================================================
-- 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
),
-- ==============================================================
-- 5. Mark participants as multi-currency if >1 currency code
-- ==============================================================
prd_ml AS (
SELECT
PARTICIPANT_ID,
PAYRUN_ID,
ORG_ID,
PERIOD_ID,
CASE
WHEN COUNT(DISTINCT 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
)
-- ==============================================================
-- 6. Final output: Only show records with any multi-currency flag
-- ==============================================================
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
WHERE NVL(bb.BBMultiCurrencyFlag, 'N') = 'Y'
OR NVL(prd.MULTI_CURR_YN, 'N') = 'Y'
ORDER BY
NVL(bb.PAYRUN_ID, prd.PAYRUN_ID),
NVL(bb.PAYMENT_BATCH_PERIOD, prd.PERIOD_ID),
NVL(bb.PAYEE_PARTICIPANT_ID, prd.PARTICIPANT_ID);
Comments
Post a Comment