BB qyert eror
SELECT pay_1.PAYRUN_ID
,pay_1.PAYMENT_BATCH_PERIOD
,pay_1.PAYEE_PARTICIPANT_ID
,'Y' AS BBMultiCurrencyFlag
,sum(pay_1.PAY_AMT_PAY_CURR) "PTD Earning LC"
,pay_1.HOME_CURRENCY_CODE
FROM
(
-- Get distinct paAyment records with home currency
SELECT P.PAYRUN_ID
,PAYMENT_BATCH_PERIOD
,PAYEE_PARTICIPANT_ID
,HOME_CURRENCY_CODE
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
WHERE 1 = 1
-- Add filters here if needed:
AND P.PAYEE_PARTICIPANT_ID = 106000
AND P.PAYRUN_ID = 300000494369243
GROUP BY PAYMENT_BATCH_PERIOD
,PAYEE_PARTICIPANT_ID
,PAYRUN_ID
,HOME_CURRENCY_CODE
) pay_1
INNER JOIN
(
-- Get payments where pay period differs from batch period
SELECT PAYRUN_ID
,PAYMENT_BATCH_PERIOD
,PAYEE_PARTICIPANT_ID
FROM CN_TP_PARTICIPANT_PAY_EXP_V P
WHERE PAY_PERIOD_ID <> PAYMENT_BATCH_PERIOD
-- Add filters here if needed:
AND PAYEE_PARTICIPANT_ID = 106000
AND PAYRUN_ID = 300000494369243
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
,pay_1.HOME_CURRENCY_CODE
HAVING COUNT(1) > 1
Comments
Post a Comment