Indewpendent CTE

-- Incentive Compensation Payment Report -- Retrieves payment details for participants with multi-currency transactions WITH -- Step 1: Get USD conversion rates usd_conversion_rates AS ( 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' ), -- Step 2: Get participant details with HR information participant_details AS ( SELECT pd.participant_id, pd.participant_detail_id, pd.org_id, pd.start_date, pd.end_date, pd.country_code AS hr_country, pd.attribute_char40 AS employment_country, pd.attribute_number1 AS hr_ti, pd.currency_code AS latest_home_currency, pd.attribute_char2 AS hr_status, pd.attribute_char17 AS salesrep, ph.hr_primary_worker_number, ph.analyst_id, (SELECT party_id FROM cn_srp_participants_all analyst WHERE analyst.org_id = ph.org_id AND analyst.participant_id = ph.analyst_id ) AS analyst_party_id FROM cn_srp_participant_details_all pd INNER JOIN cn_srp_participants_all ph ON pd.participant_id = ph.participant_id AND pd.org_id = ph.org_id ), -- Step 3: Get worker numbers for participants participant_worker_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 ), -- Step 4: Core payment transactions core_payment_transactions AS ( SELECT p.payrun_id, p.credited_participant_id, p.org_id, pb.payrun_name, pb.pay_period_id, pb.pay_date, pg.pay_group_id, pg.pay_group_name, p.plan_component_id, pc.incentive_type, ps.paysheet_id, ps.bonus_ptd, (SELECT meaning FROM cn_lookups WHERE lookup_type = 'CN_PAYSHEET_STATUS' AND lookup_code = ps.object_status AND enabled_flag = 'Y' AND lookup_code != 'REVIEWED' ) AS paysheet_status, p.pay_amt_pay_curr, p.home_currency_code, p.pay_currency_code 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_pay_groups_all_vl pg ON pg.pay_group_id = pb.pay_group_id AND pg.org_id = p.org_id INNER JOIN cn_plan_components_all_vl pc ON p.plan_component_id = pc.plan_component_id INNER JOIN cn_srp_participant_details_all pd ON p.credited_participant_id = pd.participant_id INNER JOIN cn_tp_paysheets_all ps ON ps.participant_id = p.credited_participant_id AND ps.payrun_id = p.payrun_id AND ps.quota_id IS NULL 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)) ) ), -- Step 5: Beginning Balance Multi-Currency Flag bb_multi_currency AS ( SELECT pay_1.PAYRUN_ID, pay_1.PAYMENT_BATCH_PERIOD, pay_1.PAYEE_PARTICIPANT_ID, 'Y' AS BBMultiCurrencyFlag FROM ( -- Get distinct payment records with home currency SELECT P.PAYRUN_ID, PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID, HOME_CURRENCY_CODE FROM CN_TP_PARTICIPANT_PAY_EXP_V P WHERE 1 = 1 GROUP BY PAYMENT_BATCH_PERIOD, PAYEE_PARTICIPANT_ID, PAYRUN_ID, 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(1) > 1 ), -- Step 6: Period Multi-Currency Flag period_multi_currency AS ( SELECT PARTICIPANT_ID, PAYRUN_ID, ORG_ID, PERIOD_ID, MULTI_CURR_YN FROM ( SELECT MULTI_CURR.PARTICIPANT_ID, MULTI_CURR.PAYRUN_ID, MULTI_CURR.ORG_ID, MULTI_CURR.PERIOD_ID, CASE WHEN COUNT(DISTINCT MULTI_CURR.HOME_CURRENCY_CODE) > 1 THEN 'Y' ELSE 'N' END AS MULTI_CURR_YN FROM ( SELECT PART_PAY.CREDITED_PARTICIPANT_ID AS PARTICIPANT_ID, PD.CURRENCY_CODE AS HOME_CURRENCY_CODE, PART_PAY.PAYRUN_ID, PART_PAY.ORG_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 IS NOT NULL AND PART_PAY.SOURCE_EVENT_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE, PART_PAY.SOURCE_EVENT_DATE) WHERE 1 = 1 GROUP BY PART_PAY.CREDITED_PARTICIPANT_ID, PD.CURRENCY_CODE, PART_PAY.PAYRUN_ID, PART_PAY.ORG_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.ORG_ID, MULTI_CURR.PERIOD_ID ) WHERE MULTI_CURR_YN = 'Y' ), -- Step 7: Latest plan component per participant/period latest_plan_component AS ( SELECT participant_id, payrun_name, org_id, payrun_id, period_id, plan_component_id, incentive_type, role_name, pay_group_name, start_date, end_date FROM ( SELECT DISTINCT part_pay.credited_participant_id AS participant_id, pb.payrun_name, part_pay.org_id, pb.payrun_id, pb.pay_period_id AS period_id, part_pay.plan_component_id, pc.incentive_type, roles.role_name, pg.pay_group_name, TRUNC(srp_cp.start_date) AS start_date, TRUNC(srp_cp.end_date) AS end_date, DENSE_RANK() OVER ( 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 ) 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_pay_groups_all_tl pg ON pb.pay_group_id = pg.pay_group_id AND pg.language = 'US' INNER JOIN cn_plan_components_all_vl pc ON part_pay.plan_component_id = pc.plan_component_id INNER JOIN cn_srp_plan_components_all srp_pc ON srp_pc.plan_component_id = pc.plan_component_id AND srp_pc.participant_id = part_pay.credited_participant_id INNER JOIN cn_srp_comp_plans_all srp_cp ON srp_pc.srp_comp_plan_id = srp_cp.srp_comp_plan_id AND srp_cp.participant_id = part_pay.credited_participant_id INNER JOIN cn_participant_roles cpr ON srp_cp.srp_rule_id = cpr.participant_role_id AND srp_cp.participant_id = cpr.participant_id AND srp_cp.srp_rule_type_code = 'ROLE' INNER JOIN cn_roles_tl roles ON cpr.role_id = roles.role_id AND roles.language = 'US' ) WHERE latest_pc_record = 1 ), -- Step 8: Get payrun calendar details payrun_calendar AS ( SELECT UPPER(extn_attribute_char007) AS payment_batch_name, UPPER(extn_attribute_char008) AS paygroup_name, extn_attribute_number006 AS period_id, currency_code, UPPER(extn_attribute_char011) AS incentive_type, UPPER(extn_attribute_char012) AS country, TRUNC(extn_attribute_timestamp009) AS mpa_cutoff_start_dt, TRUNC(extn_attribute_timestamp010) AS mpa_cutoff_end_dt, TRUNC(extn_attribute_timestamp007) AS paysheet_cutoff_start_dt, TRUNC(extn_attribute_timestamp008) AS paysheet_cutoff_dt FROM zca_ref_entities WHERE attribute_category = 'MTQ_ICM_PC_OBJ_c' ), -- Step 9: Get prorated monthly TI prorated_monthly_ti AS ( SELECT participant_id, org_id, period_id, start_date, end_date, prorated_monthly_ti FROM ( SELECT spfm.participant_id, spfm.org_id, spfm.period_id, srp_cp.start_date, srp_cp.end_date, spfm.output_achieved_itd AS prorated_monthly_ti, ROW_NUMBER() OVER ( PARTITION BY spfm.participant_id, period.org_id, period.period_id ORDER BY NVL(srp_cp.end_date, DATE '9999-12-31') DESC ) AS latest_ti_record FROM cn_srp_per_form_metrics_all spfm INNER JOIN cn_formulas_all_b monthly_ti ON monthly_ti.formula_id = spfm.formula_id AND monthly_ti.attribute9 = 'EmployeeEvents' AND monthly_ti.attribute10 = 'MonthlyTI' INNER JOIN cn_srp_comp_plans_all srp_cp ON srp_cp.participant_id = spfm.participant_id AND spfm.srp_comp_plan_id = srp_cp.srp_comp_plan_id AND spfm.org_id = srp_cp.org_id INNER JOIN cn_period_dims_all_tl period ON period.org_id = spfm.org_id AND period.period_type = 'Monthly' AND period.period_id = spfm.period_id WHERE ( period.end_date BETWEEN srp_cp.start_date AND NVL(srp_cp.end_date, DATE '9999-12-31') OR NVL(srp_cp.end_date, DATE '9999-12-31') BETWEEN period.start_date AND period.end_date ) ) WHERE latest_ti_record = 1 ) -- ============================================================================ -- MAIN QUERY - All CTEs joined here -- ============================================================================ SELECT -- Record identifiers TO_CHAR( cpt.pay_period_id || '-' || SUBSTR( cpt.payrun_name, 1 + INSTR(cpt.payrun_name, '_', -1, 2), (INSTR(cpt.payrun_name, '_', -1, 1) - INSTR(cpt.payrun_name, '_', -1, 2) - 1) ) || '-' || pwn.person_number || '-' || DECODE( UPPER(lpc.incentive_type), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(lpc.incentive_type) ) ) AS "Record Name", TO_CHAR( cpt.pay_period_id || '-' || SUBSTR( cpt.payrun_name, 1 + INSTR(cpt.payrun_name, '_', -1, 2), (INSTR(cpt.payrun_name, '_', -1, 1) - INSTR(cpt.payrun_name, '_', -1, 2) - 1) ) || '-' || pwn.person_number || '-' || DECODE( UPPER(lpc.incentive_type), 'COMMISSION', 'COM', 'BONUS', 'SPIF', UPPER(lpc.incentive_type) ) ) AS "Record Number", -- Payment details cpt.payrun_id AS "Payrun ID", 'Regular cycle' AS "Payment Cycle", cpt.credited_participant_id AS "Participant Id", cpt.payrun_name AS "Payment Batch", cpt.pay_period_id AS "Pay Period ID", cpt.pay_date AS "Pay Date", -- Participant information pwn.person_number AS "Worker Number", pd.hr_country AS "HR Country", pd.employment_country AS "Employment Country", pd.analyst_party_id AS "ANALYST_PARTY_ID", pd.hr_ti AS "HR TI", pd.latest_home_currency, pd.hr_status AS "HR Status", pd.salesrep AS "SalesRep", -- Role information lpc.role_name AS "Role Name", lpc.start_date AS "Role Start Date", lpc.end_date AS "Role End Date", pm_ti.prorated_monthly_ti AS "Prorated TI", -- Pay group and plan component cpt.pay_group_name AS "Pay group", lpc.plan_component_id AS "Plan component Id", lpc.incentive_type, -- Calendar dates pay_cal.mpa_cutoff_start_dt, pay_cal.mpa_cutoff_end_dt, pay_cal.paysheet_cutoff_start_dt, pay_cal.paysheet_cutoff_dt AS "PAYSHEET_CUTOFF_END_DT", -- Paysheet status cpt.paysheet_status AS "PrePayHub Status", -- Currency information prd_ml.multi_curr_yn, bb_mc.BBMultiCurrencyFlag, -- Conversion and amounts TO_CHAR(1 / conv_local.conversion_rate, '00000000.000000000') AS usd_to_local, cpt.pay_amt_pay_curr AS pay_amt_local_curr, cpt.pay_amt_pay_curr * conv_usd.conversion_rate AS pay_amt_usd, TO_CHAR( (cpt.pay_amt_pay_curr * conv_usd.conversion_rate) * (1 / conv_local.conversion_rate), '00000000.000000000' ) AS final_payout_lc, -- Placeholders 'TBD' AS "PTD_EARNINGS_LC", 'TBD' AS "Total Paysheet Amount", 'TBD' AS "Current Earnings Due (CED)_LC", 'TBD' AS "BEGINNING_BALANCE_USD", 'TBD' AS "PTD_EARNINGS_USD", 'TBD' AS "Current Earnings Due (CED)_USD", 'TBD' AS "BB % of HR TI", -- Payment amounts cpt.paysheet_id, -- Badge ID pwn.person_number AS badge_id FROM core_payment_transactions cpt -- Join USD conversion (home currency to USD) INNER JOIN usd_conversion_rates conv_usd ON TRUNC(cpt.pay_date) BETWEEN conv_usd.conversion_start_date AND conv_usd.conversion_end_date AND conv_usd.from_currency = cpt.home_currency_code -- Join participant details INNER JOIN participant_details pd ON cpt.credited_participant_id = pd.participant_id AND cpt.org_id = pd.org_id AND TRUNC(cpt.pay_date) BETWEEN pd.start_date AND NVL(pd.end_date, DATE '9999-12-31') AND cpt.home_currency_code = pd.latest_home_currency -- Join local currency conversion (USD to local) INNER JOIN usd_conversion_rates conv_local ON TRUNC(cpt.pay_date) BETWEEN conv_local.conversion_start_date AND conv_local.conversion_end_date AND conv_local.from_currency = pd.latest_home_currency -- Join worker numbers LEFT JOIN participant_worker_numbers pwn ON cpt.credited_participant_id = pwn.participant_id -- Join latest plan component INNER JOIN latest_plan_component lpc ON lpc.participant_id = cpt.credited_participant_id AND lpc.org_id = cpt.org_id AND lpc.period_id = cpt.pay_period_id AND lpc.payrun_id = cpt.payrun_id -- Join period multi-currency flag LEFT JOIN period_multi_currency prd_ml ON prd_ml.participant_id = cpt.credited_participant_id AND prd_ml.org_id = cpt.org_id AND prd_ml.period_id = cpt.pay_period_id AND prd_ml.payrun_id = cpt.payrun_id -- Join beginning balance multi-currency flag LEFT JOIN bb_multi_currency bb_mc ON bb_mc.PAYEE_PARTICIPANT_ID = cpt.credited_participant_id AND bb_mc.PAYRUN_ID = cpt.payrun_id AND bb_mc.PAYMENT_BATCH_PERIOD = cpt.pay_period_id -- Join payrun calendar LEFT JOIN payrun_calendar pay_cal ON pay_cal.payment_batch_name = UPPER(lpc.payrun_name) AND pay_cal.paygroup_name = UPPER(lpc.pay_group_name) AND pay_cal.period_id = cpt.pay_period_id AND pay_cal.currency_code = pd.latest_home_currency AND TO_CHAR(pay_cal.incentive_type) = UPPER(TO_CHAR(lpc.incentive_type)) -- Join prorated monthly TI LEFT JOIN prorated_monthly_ti pm_ti ON pm_ti.participant_id = cpt.credited_participant_id AND pm_ti.org_id = cpt.org_id AND pm_ti.period_id = cpt.pay_period_id -- Optional filter for multi-currency only WHERE NVL(prd_ml.multi_curr_yn, 'N') = 'Y' OR NVL(bb_mc.BBMultiCurrencyFlag, 'N') = 'Y';

Comments

Popular posts from this blog

Child query new

child query latest -

final simple where