Section 13-Done
-- Beginning Balance (BB) fields
CASE
WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_IN_LATEST_HC
ELSE TO_CHAR(
NVL(
SUM(
CASE
WHEN BP.PAY_PERIOD_ID <> BP.PAY_DATE THEN BP.PAY_AMT_PAY_CURR
ELSE 0
END
),
0
),
'FM9999999990.0000'
)
END AS BeginningBalance_c,
-- BB in USD
CASE
WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_IN_USD
ELSE TO_CHAR(
NVL(
SUM(
CASE
WHEN BP.PAY_PERIOD_ID <> BP.PAY_DATE THEN
BP.PAY_AMT_PAY_CURR *
NVL(
(CASE
WHEN BP.HOME_CURRENCY_CODE = 'USD' THEN 1
ELSE CR_USD.CONVERSION_RATE
END),
1
)
ELSE 0
END
),
0
),
'FM9999999990.0000'
)
END AS BeginningBalanceUSD_c,
-- BB Multi-Currency Flag
NVL(MC_FLAGS.BBMultiCurrencyFlag, 'N') AS BBMultiCurrencyFlag_c,
-- BB Home Currencies (for multi-currency cases)
CASE
WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_HOME_CURRENCIES
ELSE NULL
END AS BBHomeCurrencies_c,
-- BB Breakdown by Currency
CASE
WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.BB_BREAKDOWN_BY_CURRENCY
ELSE NULL
END AS BBBreakdownByCurrency_c,
-- Old Pay Period IDs (where BB originated)
CASE
WHEN MC_FLAGS.BBMultiCurrencyFlag = 'Y' THEN BB_AMT.OLD_PAY_PERIOD_IDS
ELSE LISTAGG(
DISTINCT
CASE
WHEN BP.PAY_PERIOD_ID <> BP.PAY_DATE THEN TO_CHAR(BP.PAY_PERIOD_ID)
END,
', '
) WITHIN GROUP (ORDER BY BP.PAY_PERIOD_ID)
END AS OldPayPeriodIds_c,
Comments
Post a Comment