latest one 4-final query for BB MC and multi currency flag

SELECT NVL(bb_MultiC.PAYRUN_ID, PRD_ML.PAYRUN_ID) AS PAYRUN_ID, NVL(bb_MultiC.PAYMENT_BATCH_PERIOD, PRD_ML.PERIOD_ID) AS PERIOD_ID, NVL(bb_MultiC.PAYEE_PARTICIPANT_ID, PRD_ML.PARTICIPANT_ID) AS PARTICIPANT_ID, bb_MultiC.BBMultiCurrencyFlag, PRD_ML.MULTI_CURR_YN FROM ( -- Subquery to identify multi-currency scenarios in batch payments SELECT pay_1.PAYRUN_ID ,pay_1.PAYMENT_BATCH_PERIOD ,pay_1.PAYEE_PARTICIPANT_ID ,'Y' AS BBMultiCurrencyFlag 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 PAYEE_PARTICIPANT_ID = 106000 -- AND 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 HAVING COUNT(1) > 1 ) bb_MultiC FULL OUTER JOIN ( -- Subquery to identify multi-currency participants SELECT PARTICIPANT_ID ,PAYRUN_ID ,ORG_ID ,PERIOD_ID ,MULTI_CURR_YN 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 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 INNER 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 -- Add filters here if needed: -- 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 ,MULTI_CURR.PAYRUN_ID ,MULTI_CURR.ORG_ID ,MULTI_CURR.PERIOD_ID ) PRD_ML1 WHERE PRD_ML1.MULTI_CURR_YN = 'Y' ) PRD_ML ON bb_MultiC.PAYEE_PARTICIPANT_ID = PRD_ML.PARTICIPANT_ID AND bb_MultiC.PAYRUN_ID = PRD_ML.PAYRUN_ID AND bb_MultiC.PAYMENT_BATCH_PERIOD = PRD_ML.PERIOD_ID

Comments

Popular posts from this blog

Child query new

child query latest -

final simple where