final bb query step 3
-- 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
LISTAGG(HOME_CURRENCY_CODE, ', ') WITHIN GROUP (ORDER BY HOME_CURRENCY_CODE) AS BB_HOME_CURRENCIES,
-- Latest home currency (same for all rows)
MAX(LATEST_HOME_CURRENCY) AS LATEST_HOME_CURRENCY,
-- Old period IDs
MAX(OLD_PAY_PERIOD_IDS) AS OLD_PAY_PERIOD_IDS,
-- 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)
LISTAGG(
HOME_CURRENCY_CODE || ':' || TO_CHAR(BB_AMOUNT_HOME_CURR, '999999999.99'),
' | '
) 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