Section 9- DOne

--BB -- Step 1: Convert BB to USD (grouped by HOME_CURRENCY_CODE across all old periods) BB_AMT_USD AS ( SELECT P.PAYRUN_ID, P.PAYEE_PARTICIPANT_ID AS CREDITED_PARTICIPANT_ID, P.PAYMENT_BATCH_PERIOD AS PAY_PERIOD_ID, LISTAGG(DISTINCT P.HOME_CURRENCY_CODE, ', ') WITHIN GROUP (ORDER BY P.PAY_PERIOD_ID) AS HOME_CURRENCY_CODE, SUM(P.PAY_AMT_PAY_CURR) AS BB_AMOUNT_HOME_CURR, SUM( P.PAY_AMT_PAY_CURR * CASE WHEN P.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE NVL(CR_USD.CONVERSION_RATE, 1) END ) AS BB_AMOUNT_USD, MAX(PD.CURRENCY_CODE) AS LATEST_HOME_CURRENCY, MAX(P.PAY_DATE) AS PAY_DATE, LISTAGG(DISTINCT P.PAY_PERIOD_ID, ', ') WITHIN GROUP (ORDER BY P.PAY_PERIOD_ID) AS OLD_PAY_PERIOD_IDS FROM CN_TP_PARTICIPANT_PAY_EXP_V P INNER JOIN PARTICIPANT_DETAILS_CACHE PD ON P.PAYEE_PARTICIPANT_ID = PD.PARTICIPANT_ID AND P.PAY_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, P.PAY_DATE) INNER JOIN PARTICIPANT_DETAILS_CACHE PDX ON P.PAYEE_PARTICIPANT_ID = PDX.PARTICIPANT_ID AND ( (P.SOURCE_EVENT_DATE IS NOT NULL AND P.SOURCE_EVENT_DATE BETWEEN PDX.START_DATE AND NVL(PDX.END_DATE, (P.SOURCE_EVENT_DATE))) OR (P.SOURCE_EVENT_DATE IS NULL AND P.PAY_DATE BETWEEN PDX.START_DATE AND NVL(PDX.END_DATE, P.PAY_DATE)) ) LEFT JOIN CONVERSION_RATES_USD CR_USD ON P.HOME_CURRENCY_CODE != 'USD' AND CR_USD.FROM_CURRENCY = PDX.CURRENCY_CODE AND TRUNC(P.PAY_DATE) BETWEEN CR_USD.CONVERSION_START_DATE AND CR_USD.CONVERSION_END_DATE WHERE P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID) AND P.PAYEE_PARTICIPANT_ID = NVL(:participant_id, P.PAYEE_PARTICIPANT_ID) AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD) AND P.PAY_PERIOD_ID <> P.PAYMENT_BATCH_PERIOD AND EXISTS ( SELECT 1 FROM BB_MULTI_CURRENCY bb WHERE bb.PAYEE_PARTICIPANT_ID = P.PAYEE_PARTICIPANT_ID AND bb.PAYRUN_ID = P.PAYRUN_ID AND bb.PAYMENT_BATCH_PERIOD = P.PAYMENT_BATCH_PERIOD ) GROUP BY P.PAYRUN_ID, P.PAYEE_PARTICIPANT_ID, P.PAYMENT_BATCH_PERIOD, P.HOME_CURRENCY_CODE ), -- Step 2: Convert each currency's BB from USD to Latest Home Currency BB_AMT_BY_CURRENCY AS ( SELECT BB.PAYRUN_ID, BB.CREDITED_PARTICIPANT_ID, BB.PAY_PERIOD_ID, BB.HOME_CURRENCY_CODE, BB.BB_AMOUNT_HOME_CURR, BB.BB_AMOUNT_USD, BB.LATEST_HOME_CURRENCY, BB.OLD_PAY_PERIOD_IDS, BB.BB_AMOUNT_USD * CASE WHEN BB.LATEST_HOME_CURRENCY = 'USD' THEN 1 ELSE 1 / NVL(CR_LC.CONVERSION_RATE, 1) END AS BB_IN_LATEST_HC_NUM, CASE WHEN BB.LATEST_HOME_CURRENCY = 'USD' THEN 1 ELSE 1 / NVL(CR_LC.CONVERSION_RATE, 1) END AS CONVERSION_RATE_USD_TO_LC FROM BB_AMT_USD BB LEFT JOIN CONVERSION_RATES_USD CR_LC ON BB.LATEST_HOME_CURRENCY != 'USD' AND CR_LC.FROM_CURRENCY = BB.LATEST_HOME_CURRENCY AND TRUNC(BB.PAY_DATE) BETWEEN CR_LC.CONVERSION_START_DATE AND CR_LC.CONVERSION_END_DATE ), -- Step 3: Aggregate all currencies into single row per participant BB_AMT AS ( SELECT PAYRUN_ID, CREDITED_PARTICIPANT_ID, PAY_PERIOD_ID, LISTAGG(HOME_CURRENCY_CODE, ', ') WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS BB_HOME_CURRENCIES, LISTAGG( HOME_CURRENCY_CODE || ':' || TO_CHAR(BB_AMOUNT_HOME_CURR, 'FM9999999990.0000'), ', ' ) WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS BB_HOME_CURRENCIES_WITH_AMOUNTS, MAX(LATEST_HOME_CURRENCY) AS LATEST_HOME_CURRENCY, MAX(OLD_PAY_PERIOD_IDS) AS OLD_PAY_PERIOD_IDS, SUM(BB_AMOUNT_USD) AS BB_AMOUNT_USD, TO_CHAR( SUM(BB_AMOUNT_USD), 'FM9999999990.0000' ) AS BB_IN_USD, SUM(BB_IN_LATEST_HC_NUM) AS BB_IN_LATEST_HC_NUM, TO_CHAR( SUM(BB_IN_LATEST_HC_NUM), 'FM9999999990.0000' ) AS BB_IN_LATEST_HC, TO_CHAR( AVG(CONVERSION_RATE_USD_TO_LC), 'FM9999999990.0000' ) AS BB_CONVERSION_RATE_AVG, SUM(BB_AMOUNT_HOME_CURR) AS BB_TOTAL_HOME_CURR_SUM, LISTAGG( HOME_CURRENCY_CODE || ':' || TO_CHAR(BB_AMOUNT_HOME_CURR, 'FM9999999990.0000'), ' | ' ) WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS BB_BREAKDOWN_BY_CURRENCY FROM BB_AMT_BY_CURRENCY GROUP BY PAYRUN_ID, CREDITED_PARTICIPANT_ID, PAY_PERIOD_ID ),

Comments

Popular posts from this blog

Section 15-Done

Section 16-Done

Section 13-Done