bb

SELECT * FROM ( -- MCChild: Main query with payment details and conversions SELECT spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAYEE_PARTICIPANT_ID, spd.HOME_CURRENCY_CODE, spd.PAY_CURRENCY_CODE, pd.CURRENCY_CODE AS latest_currency_code, SUM(spd.PAY_AMT_PAY_CURR) AS "BB PTD Earning LC", NVL( MAX( CASE WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END ), 1 ) AS "BB Conversion Rate (LC_to_USD)", SUM(spd.PAY_AMT_PAY_CURR) * MAX( CASE WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END ) AS "BB PTD Earning (USD)", TO_CHAR( MAX( CASE WHEN pd.CURRENCY_CODE = 'USD' THEN 1 ELSE 1 / conversion2.CONVERSION_RATE END ), '0000000000000.0000000000000' ) AS "BB Conversion Rate (USD_to_Latest_LC)", TO_CHAR( SUM(spd.PAY_AMT_PAY_CURR) * MAX( CASE WHEN SPD.HOME_CURRENCY_CODE = 'USD' THEN 1 ELSE conversion.CONVERSION_RATE END ) * MAX( CASE WHEN pd.CURRENCY_CODE = 'USD' THEN 1 ELSE 1 / conversion2.CONVERSION_RATE END ), '0000000000000.0000000000000' ) AS "BB PTD Earning (Latest HC)", MAX(PS.PMT_TXN_AMT_DUE) AS "BB MC Earnings" FROM CN_TP_PARTICIPANT_PAY_EXP_V spd -- Join participant details INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL pd ON spd.PAYEE_PARTICIPANT_ID = pd.PARTICIPANT_ID AND spd.PAY_DATE BETWEEN pd.START_DATE AND NVL(pd.END_DATE, spd.PAY_DATE) -- Join paysheets INNER JOIN CN_TP_PAYSHEETS_ALL PS ON PS.PARTICIPANT_ID = spd.CREDITED_PARTICIPANT_ID AND PS.PAYRUN_ID = spd.PAYRUN_ID AND PS.QUOTA_ID IS NULL -- Left join for LC to USD conversion LEFT JOIN ( SELECT CR.FROM_CURRENCY, CR.TO_CURRENCY, CR.CONVERSION_START_DATE, CR.CONVERSION_END_DATE, CR.CONVERSION_RATE FROM CN_CONVERSION_RATES CR INNER JOIN CN_CONVERSION_TYPES_TL CT ON CT.CONVERSION_TYPE_ID = CR.CONVERSION_TYPE_ID WHERE CT.CONVERSION_TYPE_ID = '-1001' AND CT.LANGUAGE = 'US' AND CR.TO_CURRENCY = 'USD' ) conversion ON SPD.HOME_CURRENCY_CODE != 'USD' AND conversion.FROM_CURRENCY = SPD.HOME_CURRENCY_CODE AND TRUNC(SPD.PAY_DATE) BETWEEN conversion.CONVERSION_START_DATE AND conversion.CONVERSION_END_DATE -- Left join for USD to latest LC conversion LEFT JOIN ( SELECT CR.FROM_CURRENCY, CR.TO_CURRENCY, CR.CONVERSION_START_DATE, CR.CONVERSION_END_DATE, CR.CONVERSION_RATE FROM CN_CONVERSION_RATES CR INNER JOIN CN_CONVERSION_TYPES_TL CT ON CT.CONVERSION_TYPE_ID = CR.CONVERSION_TYPE_ID WHERE CT.CONVERSION_TYPE_ID = '-1001' AND CT.LANGUAGE = 'US' AND CR.TO_CURRENCY = 'USD' ) conversion2 ON pd.CURRENCY_CODE != 'USD' AND conversion2.FROM_CURRENCY = pd.CURRENCY_CODE AND TRUNC(SPD.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE AND conversion2.CONVERSION_END_DATE WHERE 1 = 1 AND spd.PAYRUN_ID = NVL(:payrun_id, spd.PAYRUN_ID) AND spd.PAYEE_PARTICIPANT_ID = NVL(:participant_id, spd.PAYEE_PARTICIPANT_ID) AND spd.PAYMENT_BATCH_PERIOD = NVL(:period_id, spd.PAYMENT_BATCH_PERIOD) GROUP BY spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAYEE_PARTICIPANT_ID, spd.HOME_CURRENCY_CODE, spd.PAY_CURRENCY_CODE, pd.CURRENCY_CODE ) MCChild -- EXISTS clause to filter only multi-currency participants (Beginning Balance scenario) WHERE EXISTS ( SELECT 1 FROM ( -- Subquery to identify multi-currency scenarios in batch payments -- This identifies participants with multiple home currencies where pay period differs from batch period SELECT pay_1.PAYRUN_ID, pay_1.PAYMENT_BATCH_PERIOD, pay_1.PAYEE_PARTICIPANT_ID FROM ( -- Get distinct payment records with home currency SELECT P.PAYRUN_ID, P.PAYMENT_BATCH_PERIOD, P.PAYEE_PARTICIPANT_ID, P.HOME_CURRENCY_CODE FROM CN_TP_PARTICIPANT_PAY_EXP_V P WHERE 1 = 1 GROUP BY P.PAYMENT_BATCH_PERIOD, P.PAYEE_PARTICIPANT_ID, P.PAYRUN_ID, P.HOME_CURRENCY_CODE ) pay_1 INNER JOIN ( -- Get payments where pay period differs from batch period SELECT PAYRUN_ID, PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID FROM CN_TP_PARTICIPANT_PAY_EXP_V P WHERE PAY_PERIOD_ID <> PAYMENT_BATCH_PERIOD GROUP BY PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID, PAYRUN_ID ) pay_2 ON pay_1.PAYRUN_ID = pay_2.PAYRUN_ID AND pay_1.PAYMENT_BATCH_PERIOD = pay_2.PAYMENT_BATCH_PERIOD AND pay_1.PAYEE_PARTICIPANT_ID = pay_2.PAYEE_PARTICIPANT_ID GROUP BY pay_1.PAYRUN_ID, pay_1.PAYMENT_BATCH_PERIOD, pay_1.PAYEE_PARTICIPANT_ID HAVING COUNT(DISTINCT pay_1.HOME_CURRENCY_CODE) > 1 ) bb_MultiC WHERE MCChild.PAYEE_PARTICIPANT_ID = bb_MultiC.PAYEE_PARTICIPANT_ID AND MCChild.PAYRUN_ID = bb_MultiC.PAYRUN_ID AND MCChild.PAYMENT_BATCH_PERIOD = bb_MultiC.PAYMENT_BATCH_PERIOD );

Comments

Popular posts from this blog

Child query new

child query latest -

final simple where