dtq
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 not equal to 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
),
-- ==============================================================
-- 5. Flag multi-currency participants
-- ==============================================================
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
)
-- ==============================================================
-- 6. Final output including all from both sides
-- ==============================================================
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
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