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

Popular posts from this blog

Child query new

child query latest -

final simple where