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

Popular posts from this blog

Child query new

child query latest -

final simple where