only BB MC condition
WHERE EXISTS (
SELECT 1
FROM (
SELECT
pay_1.PAYRUN_ID,
pay_1.PAYMENT_BATCH_PERIOD,
pay_1.PAYEE_PARTICIPANT_ID
FROM (
SELECT
P.PAYRUN_ID,
P.PAYMENT_BATCH_PERIOD,
P.PAYEE_PARTICIPANT_ID,
P.HOME_CURRENCY_CODE
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
WHERE 1 = 1
GROUP BY
P.PAYMENT_BATCH_PERIOD,
P.PAYEE_PARTICIPANT_ID,
P.PAYRUN_ID,
P.HOME_CURRENCY_CODE
) pay_1
INNER JOIN (
SELECT
PAYRUN_ID,
PAYMENT_BATCH_PERIOD,
PAYEE_PARTICIPANT_ID
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
WHERE PAY_PERIOD_ID <> PAYMENT_BATCH_PERIOD
GROUP BY
PAYMENT_BATCH_PERIOD,
PAYEE_PARTICIPANT_ID,
PAYRUN_ID
) 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(DISTINCT pay_1.HOME_CURRENCY_CODE) > 1
) bb_MultiC
Comments
Post a Comment