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
Post a Comment