final BB query step 1
-- 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,
P.HOME_CURRENCY_CODE, -- Group by home currency
-- Aggregate amounts in home currency
SUM(P.PAY_AMT_PAY_CURR) AS BB_AMOUNT_HOME_CURR,
-- Convert to USD
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,
-- Get latest home currency (same for all rows of same participant)
MAX(PD.CURRENCY_CODE) AS LATEST_HOME_CURRENCY,
MAX(P.PAY_DATE) AS PAY_DATE,
-- Collect all old period IDs for reference
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)
LEFT JOIN CONVERSION_RATES_USD CR_USD
ON P.HOME_CURRENCY_CODE != 'USD'
AND CR_USD.FROM_CURRENCY = P.HOME_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 -- BB filter: old periods only
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 -- Group by HOME_CURRENCY_CODE
)
Comments
Post a Comment