Dev 6- child updated query

WITH -- Materialize the multi-currency detection queries once - FILTERED BB_MULTI_CURRENCY AS ( SELECT pay_1.PAYRUN_ID, pay_1.PAYMENT_BATCH_PERIOD, pay_1.PAYEE_PARTICIPANT_ID FROM ( 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 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, P.HOME_CURRENCY_CODE ) pay_1 INNER JOIN ( SELECT PAYRUN_ID, PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID FROM CN_TP_PARTICIPANT_PAY_EXP_V P WHERE PAY_PERIOD_ID <> 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 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 ), PERIOD_MULTI_CURRENCY AS ( SELECT MULTI_CURR.PARTICIPANT_ID, MULTI_CURR.PAYRUN_ID, MULTI_CURR.PERIOD_ID FROM ( SELECT PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID, PD.CURRENCY_CODE AS HOME_CURRENCY_CODE, PART_PAY.PAYRUN_ID, PART_PAY.PAYMENT_BATCH_PERIOD AS PERIOD_ID, SUM(ROUND(PART_PAY.COMM_AMT_CALC_CURR, 2)) AS TotalAmt 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.SOURCE_EVENT_DATE IS NOT NULL AND PART_PAY.PAYRUN_ID = NVL(:payrun_id, PART_PAY.PAYRUN_ID) AND PART_PAY.CREDITED_PARTICIPANT_ID = NVL(:participant_id, PART_PAY.CREDITED_PARTICIPANT_ID) AND PART_PAY.PAYMENT_BATCH_PERIOD = NVL(:period_id, PART_PAY.PAYMENT_BATCH_PERIOD) GROUP BY PART_PAY.CREDITED_PARTICIPANT_ID, PD.CURRENCY_CODE, PART_PAY.PAYRUN_ID, PART_PAY.PAYMENT_BATCH_PERIOD HAVING SUM(ROUND(PART_PAY.COMM_AMT_CALC_CURR, 2)) <> 0 ) MULTI_CURR GROUP BY MULTI_CURR.PARTICIPANT_ID, MULTI_CURR.PAYRUN_ID, MULTI_CURR.PERIOD_ID HAVING COUNT(DISTINCT MULTI_CURR.HOME_CURRENCY_CODE) > 1 ), 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' ), -- FIX 1: Tightened Ranking to prevent duplicate Plan Component rows LATEST_PLAN_COMPONENT AS ( SELECT PARTICIPANT_ID, PAYRUN_ID, PERIOD_ID, INCENTIVE_TYPE_CODE FROM ( SELECT DISTINCT PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID, PB.PAYRUN_ID, PB.PAY_PERIOD_ID AS PERIOD_ID, DECODE( UPPER(PC.INCENTIVE_TYPE), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(PC.INCENTIVE_TYPE) ) AS INCENTIVE_TYPE_CODE, ROW_NUMBER() OVER ( -- Changed from DENSE_RANK to ROW_NUMBER for absolute uniqueness PARTITION BY PART_PAY.CREDITED_PARTICIPANT_ID, PART_PAY.ORG_ID, PB.PAY_PERIOD_ID, PB.PAYRUN_ID ORDER BY PART_PAY.SOURCE_EVENT_DATE DESC, PART_PAY.PAY_ID DESC -- Added PAY_ID as tie-breaker ) 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 AND PB.ORG_ID = PART_PAY.ORG_ID AND PART_PAY.SOURCE_EVENT_DATE <= PB.PAY_DATE 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) AND PART_PAY.CREDITED_PARTICIPANT_ID = NVL(:participant_id, PART_PAY.CREDITED_PARTICIPANT_ID) ) WHERE LATEST_PC_RECORD = 1 ), -- FIX 2: Resolved duplication from PER_ALL_PEOPLE_F (Effective Dating) PERSON_NUMBERS AS ( SELECT participant_id, person_number FROM ( SELECT cspl.participant_id, papf.person_number, ROW_NUMBER() OVER (PARTITION BY cspl.participant_id ORDER BY papf.effective_start_date DESC) as rnk 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 WHERE cspl.participant_id = NVL(:participant_id, cspl.participant_id) ) WHERE rnk = 1 ), PARTICIPANT_DETAILS_CACHE AS ( SELECT PARTICIPANT_ID, CURRENCY_CODE, START_DATE, END_DATE FROM CN_SRP_PARTICIPANT_DETAILS_ALL WHERE PARTICIPANT_ID = NVL(:participant_id, PARTICIPANT_ID) ), 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 AND PS.PAYRUN_ID = NVL(:payrun_id, PS.PAYRUN_ID) AND PS.PARTICIPANT_ID = NVL(:participant_id, PS.PARTICIPANT_ID) ), PAYRUNS_CACHE AS ( SELECT PAYRUN_ID, PAYRUN_NAME FROM CN_TP_PAYRUNS_ALL WHERE PAYRUN_ID = NVL(:payrun_id, PAYRUN_ID) ), BASE_PAYMENTS_ALL AS ( SELECT spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAY_PERIOD_ID, spd.PAYEE_PARTICIPANT_ID, spd.CREDITED_PARTICIPANT_ID, spd.PAY_CURRENCY_CODE, spd.HOME_CURRENCY_CODE, spd.PAY_DATE, spd.PAY_AMT_PAY_CURR, -- FIX 3: Scoped EXISTS check to prevent global scenario application CASE WHEN EXISTS ( SELECT 1 FROM BB_MULTI_CURRENCY bb WHERE bb.PAYEE_PARTICIPANT_ID = spd.PAYEE_PARTICIPANT_ID AND bb.PAYRUN_ID = spd.PAYRUN_ID AND bb.PAYMENT_BATCH_PERIOD = spd.PAYMENT_BATCH_PERIOD ) THEN 'BB' WHEN EXISTS ( SELECT 1 FROM PERIOD_MULTI_CURRENCY prd WHERE prd.PARTICIPANT_ID = spd.PAYEE_PARTICIPANT_ID AND prd.PAYRUN_ID = spd.PAYRUN_ID AND prd.PERIOD_ID = spd.PAYMENT_BATCH_PERIOD ) THEN 'PERIOD' ELSE NULL END AS SCENARIO_TYPE, CASE WHEN spd.PAY_PERIOD_ID <> spd.PAYMENT_BATCH_PERIOD THEN 'BB_OLD' WHEN spd.PAY_PERIOD_ID = spd.PAYMENT_BATCH_PERIOD THEN 'BB_CURRENT' ELSE NULL END AS PERIOD_TYPE FROM CN_TP_PARTICIPANT_PAY_EXP_V spd WHERE 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) ), 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 WHERE spd.SCENARIO_TYPE = 'BB' GROUP BY spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAYEE_PARTICIPANT_ID, spd.HOME_CURRENCY_CODE ) SELECT "Parent Record Number" AS "ParentRecordNumber", "Record Name" AS "RecordName", "Record Number" AS "RecordNumber", CASE WHEN BB_HOME_CURRENCY_CODE IS NOT NULL THEN TO_CHAR(ROUND("BB MC Earnings", 4), 'FM9999999990.0000') ELSE NULL END AS "BBInMultiCurrency_c", BB_HOME_CURRENCY_CODE AS "BBHomeCurrency_c", CASE WHEN "BB PTD Earning LC" IS NOT NULL THEN TO_CHAR(ROUND("BB PTD Earning LC", 4), 'FM9999999990.0000') ELSE NULL END AS "BBInHomeCurrency_c", "BB PTD Earning (Latest HC)" AS "BBInLatestHomeCurrency_c", CASE WHEN BB_HOME_CURRENCY_CODE IS NOT NULL THEN latest_currency_code ELSE NULL END AS "BBLatestHomeCurrency_c", "BB Conversion Rate (HC_to_Latest_HC)" AS "BBConversionFactorHCToLHC_c", CASE WHEN PTD_HOME_CURRENCY_CODE IS NOT NULL THEN TO_CHAR(ROUND("PTD MC Earnings", 4), 'FM9999999990.0000') ELSE NULL END AS "PTDInMulticurrency_c", PTD_HOME_CURRENCY_CODE AS "PTDEarningsHomeCurrency_c", CASE WHEN "PTD Earning LC" IS NOT NULL THEN TO_CHAR(ROUND("PTD Earning LC", 4), 'FM9999999990.0000') ELSE NULL END AS "PTDEarningsInHomeCurrency_c", "PTD Earning (Latest HC)" AS "PTDEarningsInLatestHomeCurrency_c", CASE WHEN PTD_HOME_CURRENCY_CODE IS NOT NULL THEN latest_currency_code ELSE NULL END AS "PTDEarningsLatestHomeCurrency_c", "Conversion Rate (HC_to_Latest_HC)" AS "PTDEarningsConversionFactorHCToLHC_c", PAYRUN_ID, PAYMENT_BATCH_PERIOD, PAY_PERIOD_ID AS "Period_c", PAYEE_PARTICIPANT_ID, MC_SCENARIO_TYPE AS "MultiCurrencyScenario_c" FROM ( -- UNION Block 1: BB OLD SELECT spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAY_PERIOD_ID, spd.PAYEE_PARTICIPANT_ID, spd.PAY_CURRENCY_CODE, pd.CURRENCY_CODE AS latest_currency_code, 'BB MC' AS MC_SCENARIO_TYPE, spd.HOME_CURRENCY_CODE AS BB_HOME_CURRENCY_CODE, NULL AS PTD_HOME_CURRENCY_CODE, TO_CHAR(spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR(pr.PAYRUN_NAME, 1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2), (INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1)) || '-' || pn.person_number || '-' || lpc.INCENTIVE_TYPE_CODE) AS "Parent Record Number", TO_CHAR(spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR(pr.PAYRUN_NAME, 1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2), (INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1)) || '-' || pn.person_number || '-BBMC-' || spd.HOME_CURRENCY_CODE) AS "Record Name", TO_CHAR(spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR(pr.PAYRUN_NAME, 1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2), (INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1)) || '-' || pn.person_number || '-BBMC-' || spd.HOME_CURRENCY_CODE) AS "Record Number", SUM(spd.PAY_AMT_PAY_CURR) AS "BB PTD Earning LC", TO_CHAR(ROUND(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), 4), 'FM9999999990.0000') AS "BB PTD Earning (Latest HC)", TO_CHAR(ROUND(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), 4), 'FM9999999990.0000') AS "BB Conversion Rate (HC_to_Latest_HC)", NULL AS "PTD Earning LC", NULL AS "PTD Earning (Latest HC)", NULL AS "Conversion Rate (HC_to_Latest_HC)", MAX(agg.BB_TOTAL_EARNINGS) AS "BB MC Earnings", NULL AS "PTD MC Earnings" FROM BASE_PAYMENTS_ALL spd 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) INNER JOIN PAYRUNS_CACHE pr ON pr.PAYRUN_ID = spd.PAYRUN_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 PERSON_NUMBERS pn ON pn.participant_id = spd.CREDITED_PARTICIPANT_ID 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 LEFT JOIN CONVERSION_RATES_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 CONVERSION_RATES_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 spd.SCENARIO_TYPE = 'BB' AND spd.PERIOD_TYPE = 'BB_OLD' GROUP BY spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAY_PERIOD_ID, spd.PAYEE_PARTICIPANT_ID, spd.HOME_CURRENCY_CODE, spd.PAY_CURRENCY_CODE, pd.CURRENCY_CODE, pr.PAYRUN_NAME, pn.person_number, lpc.INCENTIVE_TYPE_CODE UNION ALL -- UNION Block 2: BB CURRENT SELECT spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAY_PERIOD_ID, spd.PAYEE_PARTICIPANT_ID, spd.PAY_CURRENCY_CODE, pd.CURRENCY_CODE AS latest_currency_code, 'BB MC' AS MC_SCENARIO_TYPE, NULL AS BB_HOME_CURRENCY_CODE, spd.HOME_CURRENCY_CODE AS PTD_HOME_CURRENCY_CODE, TO_CHAR(spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR(pr.PAYRUN_NAME, 1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2), (INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1)) || '-' || pn.person_number || '-' || lpc.INCENTIVE_TYPE_CODE) AS "Parent Record Number", TO_CHAR(spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR(pr.PAYRUN_NAME, 1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2), (INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1)) || '-' || pn.person_number || '-BBMC-' || spd.HOME_CURRENCY_CODE) AS "Record Name", TO_CHAR(spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR(pr.PAYRUN_NAME, 1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2), (INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1)) || '-' || pn.person_number || '-BBMC-' || spd.HOME_CURRENCY_CODE) AS "Record Number", NULL AS "BB PTD Earning LC", NULL AS "BB PTD Earning (Latest HC)", NULL AS "BB Conversion Rate (HC_to_Latest_HC)", SUM(spd.PAY_AMT_PAY_CURR) AS "PTD Earning LC", TO_CHAR(ROUND(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), 4), 'FM9999999990.0000') AS "PTD Earning (Latest HC)", TO_CHAR(ROUND(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), 4), 'FM9999999990.0000') AS "Conversion Rate (HC_to_Latest_HC)", NULL AS "BB MC Earnings", MAX(agg.PTD_TOTAL_EARNINGS) AS "PTD MC Earnings" FROM BASE_PAYMENTS_ALL spd 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) INNER JOIN PAYRUNS_CACHE pr ON pr.PAYRUN_ID = spd.PAYRUN_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 PERSON_NUMBERS pn ON pn.participant_id = spd.CREDITED_PARTICIPANT_ID 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 LEFT JOIN CONVERSION_RATES_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 CONVERSION_RATES_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 spd.SCENARIO_TYPE = 'BB' AND spd.PERIOD_TYPE = 'BB_CURRENT' GROUP BY spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAY_PERIOD_ID, spd.PAYEE_PARTICIPANT_ID, spd.HOME_CURRENCY_CODE, spd.PAY_CURRENCY_CODE, pd.CURRENCY_CODE, pr.PAYRUN_NAME, pn.person_number, lpc.INCENTIVE_TYPE_CODE UNION ALL -- UNION Block 3: PERIOD MC (PDMC Suffix) SELECT spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAY_PERIOD_ID, spd.PAYEE_PARTICIPANT_ID, spd.PAY_CURRENCY_CODE, pd.CURRENCY_CODE AS latest_currency_code, 'PTD MC' AS MC_SCENARIO_TYPE, NULL AS BB_HOME_CURRENCY_CODE, spd.HOME_CURRENCY_CODE AS PTD_HOME_CURRENCY_CODE, TO_CHAR(spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR(pr.PAYRUN_NAME, 1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2), (INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1)) || '-' || pn.person_number || '-' || lpc.INCENTIVE_TYPE_CODE) AS "Parent Record Number", TO_CHAR(spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR(pr.PAYRUN_NAME, 1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2), (INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1)) || '-' || pn.person_number || '-PDMC-' || spd.HOME_CURRENCY_CODE) AS "Record Name", TO_CHAR(spd.PAYMENT_BATCH_PERIOD || '-' || SUBSTR(pr.PAYRUN_NAME, 1 + INSTR(pr.PAYRUN_NAME, '_', -1, 2), (INSTR(pr.PAYRUN_NAME, '_', -1, 1) - INSTR(pr.PAYRUN_NAME, '_', -1, 2) - 1)) || '-' || pn.person_number || '-PDMC-' || spd.HOME_CURRENCY_CODE) AS "Record Number", NULL AS "BB PTD Earning LC", NULL AS "BB PTD Earning (Latest HC)", NULL AS "BB Conversion Rate (HC_to_Latest_HC)", SUM(spd.PAY_AMT_PAY_CURR) AS "PTD Earning LC", TO_CHAR(ROUND(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), 4), 'FM9999999990.0000') AS "PTD Earning (Latest HC)", TO_CHAR(ROUND(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), 4), 'FM9999999990.0000') AS "Conversion Rate (HC_to_Latest_HC)", NULL AS "BB MC Earnings", MAX(PS.PMT_TXN_AMT_DUE) AS "PTD MC Earnings" FROM BASE_PAYMENTS_ALL spd 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) INNER JOIN PAYSHEETS_CACHE PS ON PS.PARTICIPANT_ID = spd.CREDITED_PARTICIPANT_ID AND PS.PAYRUN_ID = spd.PAYRUN_ID INNER JOIN PAYRUNS_CACHE pr ON pr.PAYRUN_ID = spd.PAYRUN_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 PERSON_NUMBERS pn ON pn.participant_id = spd.CREDITED_PARTICIPANT_ID LEFT JOIN CONVERSION_RATES_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 CONVERSION_RATES_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 spd.SCENARIO_TYPE = 'PERIOD' GROUP BY spd.PAYRUN_ID, spd.PAYMENT_BATCH_PERIOD, spd.PAY_PERIOD_ID, spd.PAYEE_PARTICIPANT_ID, spd.HOME_CURRENCY_CODE, spd.PAY_CURRENCY_CODE, pd.CURRENCY_CODE, pr.PAYRUN_NAME, pn.person_number, lpc.INCENTIVE_TYPE_CODE ) FINAL_RESULT WHERE NOT ( NVL("PTD Earning LC", 0) = 0 AND NVL(TO_NUMBER(REPLACE("PTD Earning (Latest HC)", ',', '')), 0) = 0 AND NVL("BB PTD Earning LC", 0) = 0 AND NVL(TO_NUMBER(REPLACE("BB PTD Earning (Latest HC)", ',', '')), 0) = 0 );

Comments

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika