Updated query

WITH -- 1. Identify participants with balances from previous periods BB_MULTI_CURRENCY AS ( SELECT P.PAYRUN_ID, P.PAYMENT_BATCH_PERIOD, P.PAYEE_PARTICIPANT_ID FROM CN_TP_PARTICIPANT_PAY_EXP_V P WHERE P.PAY_PERIOD_ID <> P.PAYMENT_BATCH_PERIOD AND P.PAYRUN_ID = NVL(:payrun_id, P.PAYRUN_ID) AND P.PAYEE_PARTICIPANT_ID = NVL(:participant_id, P.PAYEE_PARTICIPANT_ID) AND P.PAYMENT_BATCH_PERIOD = NVL(:period_id, P.PAYMENT_BATCH_PERIOD) GROUP BY P.PAYMENT_BATCH_PERIOD, P.PAYEE_PARTICIPANT_ID, P.PAYRUN_ID HAVING COUNT(DISTINCT P.HOME_CURRENCY_CODE) > 1 ), -- 2. Identify participants with multiple currencies in the current period PERIOD_MULTI_CURRENCY AS ( SELECT PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID, PART_PAY.PAYRUN_ID, PART_PAY.PAYMENT_BATCH_PERIOD AS PERIOD_ID FROM CN_TP_PARTICIPANT_PAY_EXP_V PART_PAY INNER JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD ON PD.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID AND PART_PAY.SOURCE_EVENT_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, PART_PAY.SOURCE_EVENT_DATE) WHERE PART_PAY.PAYRUN_ID = NVL(:payrun_id, PART_PAY.PAYRUN_ID) AND PART_PAY.PAYMENT_BATCH_PERIOD = NVL(:period_id, PART_PAY.PAYMENT_BATCH_PERIOD) GROUP BY PART_PAY.CREDITED_PARTICIPANT_ID, PART_PAY.PAYRUN_ID, PART_PAY.PAYMENT_BATCH_PERIOD HAVING COUNT(DISTINCT PD.CURRENCY_CODE) > 1 ), -- 3. Materialize conversion rates for USD CONVERSION_RATES_USD AS ( SELECT CR.FROM_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' ), -- 4. Get Latest Plan Component for Incentive Type labeling LATEST_PLAN_COMPONENT AS ( SELECT PARTICIPANT_ID, PAYRUN_ID, PERIOD_ID, INCENTIVE_TYPE_CODE, PB_INCENTIVE_TYPE FROM ( SELECT DISTINCT PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID, PB.PAYRUN_ID, PB.PAY_PERIOD_ID AS PERIOD_ID, NVL(PB.REPORT_GROUP,'ALL') PB_INCENTIVE_TYPE, DECODE(UPPER(PC.INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(PC.INCENTIVE_TYPE)) AS INCENTIVE_TYPE_CODE, DENSE_RANK() OVER (PARTITION BY PART_PAY.CREDITED_PARTICIPANT_ID, PB.PAY_PERIOD_ID, PB.PAYRUN_ID ORDER BY PART_PAY.SOURCE_EVENT_DATE DESC) AS LATEST_PC_RECORD FROM CN_TP_PARTICIPANT_PAY_ALL PART_PAY INNER JOIN CN_TP_PAYRUNS_ALL PB ON PB.PAYRUN_ID = PART_PAY.PAYRUN_ID INNER JOIN CN_PLAN_COMPONENTS_ALL_VL PC ON PART_PAY.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID WHERE PB.PAYRUN_ID = NVL(:payrun_id, PB.PAYRUN_ID) AND PB.PAY_PERIOD_ID = NVL(:period_id, PB.PAY_PERIOD_ID) ) WHERE LATEST_PC_RECORD = 1 ), -- 5. Helper Caches PERSON_NUMBERS AS ( SELECT DISTINCT cspl.participant_id, papf.person_number FROM CN_SRP_PARTICIPANTS_ALL cspl INNER JOIN hz_person_profiles hpp ON cspl.party_id = hpp.party_id INNER JOIN PER_ALL_PEOPLE_F papf ON papf.person_id = hpp.hr_person_id ), PARTICIPANT_DETAILS_CACHE AS ( SELECT PARTICIPANT_ID, CURRENCY_CODE, START_DATE, END_DATE FROM CN_SRP_PARTICIPANT_DETAILS_ALL ), PAYSHEETS_CACHE AS ( SELECT PS.PARTICIPANT_ID, PS.PAYRUN_ID, PS.PMT_TXN_AMT_DUE FROM CN_TP_PAYSHEETS_ALL PS WHERE PS.QUOTA_ID IS NULL ), PAYRUNS_CACHE AS ( SELECT PAYRUN_ID, PAYRUN_NAME FROM CN_TP_PAYRUNS_ALL ), -- 6. Base Payment Data with Correct Scenario Split BASE_PAYMENTS_ALL AS ( SELECT spd.*, CASE WHEN spd.PAY_PERIOD_ID <> spd.PAYMENT_BATCH_PERIOD THEN 'BB_OLD' ELSE 'BB_CURRENT' END AS PERIOD_TYPE FROM CN_TP_PARTICIPANT_PAY_EXP_V spd WHERE spd.PAYRUN_ID = NVL(:payrun_id, spd.PAYRUN_ID) AND spd.PAYMENT_BATCH_PERIOD = NVL(:period_id, spd.PAYMENT_BATCH_PERIOD) ), BB_PTD_AGGREGATES AS ( SELECT spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAYEE_PARTICIPANT_ID, spd.HOME_CURRENCY_CODE, SUM(CASE WHEN spd.PERIOD_TYPE = 'BB_OLD' THEN spd.PAY_AMT_PAY_CURR ELSE 0 END) AS BB_TOTAL_EARNINGS, SUM(CASE WHEN spd.PERIOD_TYPE = 'BB_CURRENT' THEN spd.PAY_AMT_PAY_CURR ELSE 0 END) AS PTD_TOTAL_EARNINGS FROM BASE_PAYMENTS_ALL spd GROUP BY spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAYEE_PARTICIPANT_ID, spd.HOME_CURRENCY_CODE ) SELECT ParentRecordNumber, RecordName, RecordNumber, BBInMultiCurrency_c, BBHomeCurrency_c, BBInHomeCurrency_c, BBInLatestHomeCurrency_c, BBLatestHomeCurrency_c, BBConversionFactorHCToLHC_c, PTDInMulticurrency_c, PTDEarningsHomeCurrency_c, PTDEarningsInHomeCurrency_c, PTDEarningsInLatestHomeCurrency_c, PTDEarningsLatestHomeCurrency_c, PTDEarningsConversionFactorHCToLHC_c, PAYRUN_ID, PAYMENT_BATCH_PERIOD, Period_c, PAYEE_PARTICIPANT_ID, MultiCurrencyScenario_c FROM ( -- BRANCH 1: PRIOR PERIOD (BB MC) SELECT spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAY_PERIOD_ID AS Period_c, spd.PAYEE_PARTICIPANT_ID, 'BB MC' AS MultiCurrencyScenario_c, spd.HOME_CURRENCY_CODE AS BBHomeCurrency_c, -- Parent & Child IDs (spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR(pr.PAYRUN_NAME, INSTR(pr.PAYRUN_NAME, '_', -1, 2)+1, (INSTR(pr.PAYRUN_NAME, '_', -1, 1)-INSTR(pr.PAYRUN_NAME, '_', -1, 2)-1)) || '-' || pn.person_number || '-' || DECODE(UPPER(lpc.PB_INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(lpc.PB_INCENTIVE_TYPE))) AS ParentRecordNumber, (spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR(pr.PAYRUN_NAME, INSTR(pr.PAYRUN_NAME, '_', -1, 2)+1, (INSTR(pr.PAYRUN_NAME, '_', -1, 1)-INSTR(pr.PAYRUN_NAME, '_', -1, 2)-1)) || '-' || pn.person_number || '-BBMC-' || spd.HOME_CURRENCY_CODE) AS RecordName, (spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR(pr.PAYRUN_NAME, INSTR(pr.PAYRUN_NAME, '_', -1, 2)+1, (INSTR(pr.PAYRUN_NAME, '_', -1, 1)-INSTR(pr.PAYRUN_NAME, '_', -1, 2)-1)) || '-' || pn.person_number || '-BBMC-' || spd.HOME_CURRENCY_CODE) AS RecordNumber, -- BB Logic TO_CHAR(ROUND(agg.BB_TOTAL_EARNINGS, 4), 'FM9999999990.0000') AS BBInMultiCurrency_c, TO_CHAR(ROUND(agg.BB_TOTAL_EARNINGS, 4), 'FM9999999990.0000') AS BBInHomeCurrency_c, -- Conversion TO_CHAR(ROUND(agg.BB_TOTAL_EARNINGS * NVL(conversion.CONVERSION_RATE,1) * (1/NVL(conversion2.CONVERSION_RATE,1)), 4), 'FM9999999990.0000') AS BBInLatestHomeCurrency_c, pd.CURRENCY_CODE AS BBLatestHomeCurrency_c, TO_CHAR(ROUND(NVL(conversion.CONVERSION_RATE,1) * (1/NVL(conversion2.CONVERSION_RATE,1)), 4), 'FM9999999990.0000') AS BBConversionFactorHCToLHC_c, -- Null out PTD for this branch NULL AS PTDInMulticurrency_c, NULL AS PTDEarningsHomeCurrency_c, NULL AS PTDEarningsInHomeCurrency_c, NULL AS PTDEarningsInLatestHomeCurrency_c, NULL AS PTDEarningsLatestHomeCurrency_c, NULL AS PTDEarningsConversionFactorHCToLHC_c FROM BASE_PAYMENTS_ALL spd INNER JOIN BB_PTD_AGGREGATES agg ON agg.PAYRUN_ID = spd.PAYRUN_ID AND agg.PAYMENT_BATCH_PERIOD = spd.PAYMENT_BATCH_PERIOD AND agg.PAYEE_PARTICIPANT_ID = spd.PAYEE_PARTICIPANT_ID AND agg.HOME_CURRENCY_CODE = spd.HOME_CURRENCY_CODE INNER JOIN PAYRUNS_CACHE pr ON pr.PAYRUN_ID = spd.PAYRUN_ID INNER JOIN PERSON_NUMBERS pn ON pn.participant_id = spd.CREDITED_PARTICIPANT_ID INNER JOIN LATEST_PLAN_COMPONENT lpc ON lpc.PARTICIPANT_ID = spd.CREDITED_PARTICIPANT_ID AND lpc.PAYRUN_ID = spd.PAYRUN_ID AND lpc.PERIOD_ID = spd.PAYMENT_BATCH_PERIOD INNER JOIN PARTICIPANT_DETAILS_CACHE 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) LEFT JOIN CONVERSION_RATES_USD conversion ON conversion.FROM_CURRENCY = spd.HOME_CURRENCY_CODE AND TRUNC(spd.PAY_DATE) BETWEEN conversion.CONVERSION_START_DATE AND conversion.CONVERSION_END_DATE LEFT JOIN CONVERSION_RATES_USD conversion2 ON conversion2.FROM_CURRENCY = pd.CURRENCY_CODE AND TRUNC(spd.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE AND conversion2.CONVERSION_END_DATE WHERE spd.PERIOD_TYPE = 'BB_OLD' UNION ALL -- BRANCH 2: CURRENT PERIOD (PTD MC) SELECT spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAY_PERIOD_ID AS Period_c, spd.PAYEE_PARTICIPANT_ID, 'PTD MC' AS MultiCurrencyScenario_c, NULL AS BBHomeCurrency_c, -- Parent & Child IDs (spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR(pr.PAYRUN_NAME, INSTR(pr.PAYRUN_NAME, '_', -1, 2)+1, (INSTR(pr.PAYRUN_NAME, '_', -1, 1)-INSTR(pr.PAYRUN_NAME, '_', -1, 2)-1)) || '-' || pn.person_number || '-' || DECODE(UPPER(lpc.PB_INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(lpc.PB_INCENTIVE_TYPE))) AS ParentRecordNumber, (spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR(pr.PAYRUN_NAME, INSTR(pr.PAYRUN_NAME, '_', -1, 2)+1, (INSTR(pr.PAYRUN_NAME, '_', -1, 1)-INSTR(pr.PAYRUN_NAME, '_', -1, 2)-1)) || '-' || pn.person_number || '-PDMC-' || spd.HOME_CURRENCY_CODE) AS RecordName, (spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR(pr.PAYRUN_NAME, INSTR(pr.PAYRUN_NAME, '_', -1, 2)+1, (INSTR(pr.PAYRUN_NAME, '_', -1, 1)-INSTR(pr.PAYRUN_NAME, '_', -1, 2)-1)) || '-' || pn.person_number || '-PDMC-' || spd.HOME_CURRENCY_CODE) AS RecordNumber, -- BB Logic Null NULL AS BBInMultiCurrency_c, NULL AS BBInHomeCurrency_c, NULL AS BBInLatestHomeCurrency_c, NULL AS BBLatestHomeCurrency_c, NULL AS BBConversionFactorHCToLHC_c, -- PTD Logic TO_CHAR(ROUND(agg.PTD_TOTAL_EARNINGS, 4), 'FM9999999990.0000') AS PTDInMulticurrency_c, spd.HOME_CURRENCY_CODE AS PTDEarningsHomeCurrency_c, TO_CHAR(ROUND(agg.PTD_TOTAL_EARNINGS, 4), 'FM9999999990.0000') AS PTDEarningsInHomeCurrency_c, TO_CHAR(ROUND(agg.PTD_TOTAL_EARNINGS * NVL(conversion.CONVERSION_RATE,1) * (1/NVL(conversion2.CONVERSION_RATE,1)), 4), 'FM9999999990.0000') AS PTDEarningsInLatestHomeCurrency_c, pd.CURRENCY_CODE AS PTDEarningsLatestHomeCurrency_c, TO_CHAR(ROUND(NVL(conversion.CONVERSION_RATE,1) * (1/NVL(conversion2.CONVERSION_RATE,1)), 4), 'FM9999999990.0000') AS PTDEarningsConversionFactorHCToLHC_c FROM BASE_PAYMENTS_ALL spd INNER JOIN BB_PTD_AGGREGATES agg ON agg.PAYRUN_ID = spd.PAYRUN_ID AND agg.PAYMENT_BATCH_PERIOD = spd.PAYMENT_BATCH_PERIOD AND agg.PAYEE_PARTICIPANT_ID = spd.PAYEE_PARTICIPANT_ID AND agg.HOME_CURRENCY_CODE = spd.HOME_CURRENCY_CODE INNER JOIN PAYRUNS_CACHE pr ON pr.PAYRUN_ID = spd.PAYRUN_ID INNER JOIN PERSON_NUMBERS pn ON pn.participant_id = spd.CREDITED_PARTICIPANT_ID INNER JOIN LATEST_PLAN_COMPONENT lpc ON lpc.PARTICIPANT_ID = spd.CREDITED_PARTICIPANT_ID AND lpc.PAYRUN_ID = spd.PAYRUN_ID AND lpc.PERIOD_ID = spd.PAYMENT_BATCH_PERIOD INNER JOIN PARTICIPANT_DETAILS_CACHE 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) LEFT JOIN CONVERSION_RATES_USD conversion ON conversion.FROM_CURRENCY = spd.HOME_CURRENCY_CODE AND TRUNC(spd.PAY_DATE) BETWEEN conversion.CONVERSION_START_DATE AND conversion.CONVERSION_END_DATE LEFT JOIN CONVERSION_RATES_USD conversion2 ON conversion2.FROM_CURRENCY = pd.CURRENCY_CODE AND TRUNC(spd.PAY_DATE) BETWEEN conversion2.CONVERSION_START_DATE AND conversion2.CONVERSION_END_DATE WHERE spd.PERIOD_TYPE = 'BB_CURRENT' ) FINAL_RESULT GROUP BY ParentRecordNumber, RecordName, RecordNumber, BBInMultiCurrency_c, BBHomeCurrency_c, BBInHomeCurrency_c, BBInLatestHomeCurrency_c, BBLatestHomeCurrency_c, BBConversionFactorHCToLHC_c, PTDInMulticurrency_c, PTDEarningsHomeCurrency_c, PTDEarningsInHomeCurrency_c, PTDEarningsInLatestHomeCurrency_c, PTDEarningsLatestHomeCurrency_c, PTDEarningsConversionFactorHCToLHC_c, PAYRUN_ID, PAYMENT_BATCH_PERIOD, Period_c, PAYEE_PARTICIPANT_ID, MultiCurrencyScenario_c;

Comments

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika