BB query for USD and LC
-- Step 1: Convert Beginning Balance to USD
BB_AMT_USD AS (
SELECT
P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID,
P.ORG_ID,
PB.PAY_PERIOD_ID,
-- Sum all BB amounts converted to USD
SUM(
P.PAY_AMT_PAY_CURR *
NVL(
(CASE
WHEN P.HOME_CURRENCY_CODE = 'USD' THEN 1
ELSE conversion.CONVERSION_RATE
END),
1
)
) AS BB_AMOUNT_USD,
-- Capture the latest home currency for conversion back
MAX(PD.CURRENCY_CODE) AS LATEST_HOME_CURRENCY,
-- Capture PAY_DATE for the second conversion step
MAX(PB.PAY_DATE) AS PAY_DATE
FROM CN_TP_PARTICIPANT_PAY_ALL P
INNER JOIN CN_TP_PAYRUNS_ALL PB
ON PB.PAYRUN_ID = P.PAYRUN_ID
AND PB.ORG_ID = P.ORG_ID
INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD
ON P.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID
AND P.ORG_ID = PD.ORG_ID
-- Join conversion rates (Home Currency → USD)
INNER JOIN CONVERSION_RATES_USD conversion
ON TRUNC(
CASE
WHEN P.SOURCE_EVENT_DATE IS NOT NULL THEN P.SOURCE_EVENT_DATE
ELSE PB.PAY_DATE
END
) BETWEEN conversion.CONVERSION_START_DATE
AND conversion.CONVERSION_END_DATE
AND conversion.FROM_CURRENCY = P.HOME_CURRENCY_CODE
WHERE (
(P.SOURCE_EVENT_DATE IS NOT NULL
AND P.SOURCE_EVENT_DATE BETWEEN PD.START_DATE
AND NVL(PD.END_DATE, P.SOURCE_EVENT_DATE))
OR
(P.SOURCE_EVENT_DATE IS NULL
AND PB.PAY_DATE BETWEEN PD.START_DATE
AND NVL(PD.END_DATE, PB.PAY_DATE))
)
-- Key filter: BB = transactions from PRIOR periods (PAY_PERIOD_ID <> current period)
AND P.PAY_PERIOD_ID <> PB.PAY_PERIOD_ID
-- Apply parameter filters
AND P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID)
AND P.CREDITED_PARTICIPANT_ID = NVL(:participant_id, P.CREDITED_PARTICIPANT_ID)
AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID)
GROUP BY
P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID,
P.ORG_ID,
PB.PAY_PERIOD_ID
),
-- Step 2: Convert Beginning Balance Back to Latest Home Currency
BB_AMT AS (
SELECT
bb_usd.PAYRUN_ID,
bb_usd.CREDITED_PARTICIPANT_ID,
bb_usd.ORG_ID,
bb_usd.PAY_PERIOD_ID,
bb_usd.BB_AMOUNT_USD,
bb_usd.LATEST_HOME_CURRENCY,
-- Convert USD back to Latest Home Currency (formatted string)
TO_CHAR(
bb_usd.BB_AMOUNT_USD * (
1 / (
NVL(
(CASE
WHEN bb_usd.LATEST_HOME_CURRENCY = 'USD' THEN 1
ELSE conversion2.CONVERSION_RATE
END),
1
)
)
),
'00000000.000000000'
) AS B_BALANCE_LC,
-- Also keep numeric version for calculations
bb_usd.BB_AMOUNT_USD * (
1 / (
NVL(
(CASE
WHEN bb_usd.LATEST_HOME_CURRENCY = 'USD' THEN 1
ELSE conversion2.CONVERSION_RATE
END),
1
)
)
) AS B_BALANCE_LC_NUM
FROM BB_AMT_USD bb_usd
-- Join conversion rates (Latest Home Currency → USD) for inverse calculation
INNER JOIN CONVERSION_RATES_USD conversion2
ON TRUNC(bb_usd.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE
AND conversion2.CONVERSION_END_DATE
AND conversion2.FROM_CURRENCY = bb_usd.LATEST_HOME_CURRENCY
)
===
-- Join BB_AMT (includes both USD and LC)
LEFT JOIN BB_AMT
ON BB_AMT.CREDITED_PARTICIPANT_ID = BG.CREDITED_PARTICIPANT_ID
AND BB_AMT.ORG_ID = BG.ORG_ID
AND BB_AMT.PAY_PERIOD_ID = BG.PAY_PERIOD_ID
AND BB_AMT.PAYRUN_ID = BG.PAYRUN_ID
==
-- Beginning Balance (BB) - NOW CALCULATED
BB_AMT.B_BALANCE_LC AS "BEGINNING_BALANCE_LC",
BB_AMT.BB_AMOUNT_USD AS "BEGINNING_BALANCE_USD",
-- BB % of HR TI - NOW CALCULATED
CASE
WHEN parDetails.ATTRIBUTE_NUMBER1 IS NOT NULL
AND parDetails.ATTRIBUTE_NUMBER1 <> 0
THEN TO_CHAR(
(BB_AMT.B_BALANCE_LC_NUM / parDetails.ATTRIBUTE_NUMBER1) * 100,
'990.00'
) || '%'
ELSE 'N/A'
END AS "BB % of HR TI",
Comments
Post a Comment