BB Multi Currency
-- Step 3: Aggregate all currencies into single row per participant
BB_AMT AS (
SELECT
PAYRUN_ID,
CREDITED_PARTICIPANT_ID,
PAY_PERIOD_ID,
-- Aggregate home currencies (codes only)
LISTAGG(HOME_CURRENCY_CODE, ', ') WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS BB_HOME_CURRENCIES,
-- Aggregate home currencies with amounts (NEW)
LISTAGG(
HOME_CURRENCY_CODE || ':' || TO_CHAR(BB_AMOUNT_HOME_CURR, 'FM999999999.00'),
', '
) WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS BB_HOME_CURRENCIES_WITH_AMOUNTS,
-- Latest home currency
MAX(LATEST_HOME_CURRENCY) AS LATEST_HOME_CURRENCY,
-- Old period IDs
MAX(OLD_PAY_PERIOD_IDS) AS OLD_PAY_PERIOD_IDS,
-- Total BB in USD (sum across all currencies)
SUM(BB_AMOUNT_USD) AS BB_AMOUNT_USD,
-- Formatted BB in USD
TO_CHAR(
SUM(BB_AMOUNT_USD),
'0000000000000.0000000000000'
) AS BB_IN_USD,
-- Total BB in Latest Home Currency (sum across all currencies)
SUM(BB_IN_LATEST_HC_NUM) AS BB_IN_LATEST_HC_NUM,
-- Formatted total
TO_CHAR(
SUM(BB_IN_LATEST_HC_NUM),
'0000000000000.0000000000000'
) AS BB_IN_LATEST_HC,
-- Average conversion rate (for reference)
TO_CHAR(
AVG(CONVERSION_RATE_USD_TO_LC),
'0000000000000.0000000000000'
) AS BB_CONVERSION_RATE_AVG,
-- Breakdown by currency (for debugging) - using pipe separator
LISTAGG(
HOME_CURRENCY_CODE || ':' || TO_CHAR(BB_AMOUNT_HOME_CURR, 'FM999999999.00'),
' | '
) 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
)
SELECT
BG.PAYRUN_ID,
BG.PAYMENT_BATCH_PERIOD,
BG.PAYEE_PARTICIPANT_ID,
-- BB columns
BB_AMT.OLD_PAY_PERIOD_IDS AS "BB Old Pay Period IDs",
BB_AMT.BB_HOME_CURRENCIES AS "BB Home Currencies",
BB_AMT.BB_HOME_CURRENCIES_WITH_AMOUNTS AS "BB Home Currencies with Amounts", -- ✅ NEW
BB_AMT.BB_BREAKDOWN_BY_CURRENCY AS "BB Breakdown by Currency",
BB_AMT.BB_IN_USD AS "BB in USD",
BB_AMT.BB_IN_LATEST_HC AS "BB in Latest Home Currency",
BB_AMT.LATEST_HOME_CURRENCY AS "BB Latest Home Currency",
BB_AMT.BB_CONVERSION_RATE_AVG AS "BB Avg Conversion Rate"
FROM your_base_query BG
LEFT JOIN BB_AMT
ON BB_AMT.CREDITED_PARTICIPANT_ID = BG.PAYEE_PARTICIPANT_ID
AND BB_AMT.PAY_PERIOD_ID = BG.PAYMENT_BATCH_PERIOD
AND BB_AMT.PAYRUN_ID = BG.PAYRUN_ID
-- Add this to BB_AMT CTE
SUM(BB_AMOUNT_HOME_CURR) AS BB_TOTAL_HOME_CURR_SUM, -- Simple sum (not currency-aware!)
-- In final SELECT
BB_AMT.BB_TOTAL_HOME_CURR_SUM AS "BB Total (Mixed Currencies Sum)"
Comments
Post a Comment