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

Popular posts from this blog

Child query new

child query latest -

final simple where