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

Popular posts from this blog

Child query new

child query latest -

final simple where