Btwe

  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,

case when count(distinct 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.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

Popular posts from this blog

Child query new

child query latest -

final simple where