Big query 2

-- 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: Base payment data converted to USD base_payments_usd AS ( SELECT spd.org_id, spd.payrun_id, spd.credited_participant_id, spd.payrun_name, spd.pay_period_id, spd.pay_date, spd.pay_group_id, spd.pay_group_name, spd.paysheet_id, spd.paysheet_status, spd.pay_amt_pay_curr * conv.conversion_rate AS pay_amt_usd, pd.latest_home_currency FROM ( -- Core payment transactions 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)) ) ) spd INNER JOIN usd_conversion_rates conv ON TRUNC(spd.pay_date) BETWEEN conv.conversion_start_date AND conv.conversion_end_date AND conv.from_currency = spd.home_currency_code INNER JOIN participant_details pd ON spd.credited_participant_id = pd.participant_id AND spd.org_id = pd.org_id AND TRUNC(spd.pay_date) BETWEEN pd.start_date AND NVL(pd.end_date, DATE '9999-12-31') AND spd.home_currency_code = pd.latest_home_currency ), -- Step 5: Aggregate payments by paysheet aggregated_payments AS ( SELECT org_id, payrun_id, credited_participant_id, payrun_name, pay_period_id, pay_date, pay_group_id, pay_group_name, paysheet_id, paysheet_status, latest_home_currency, SUM(pay_amt_usd) AS pay_amt_pay_curr_usd FROM base_payments_usd GROUP BY org_id, payrun_id, credited_participant_id, payrun_name, pay_period_id, pay_date, pay_group_id, pay_group_name, paysheet_id, paysheet_status, latest_home_currency ), -- Step 6: Convert back to local currency final_payments AS ( SELECT ap.org_id, ap.payrun_id, ap.credited_participant_id, ap.payrun_name, ap.pay_period_id, ap.pay_date, ap.pay_group_id, ap.pay_group_name, ap.paysheet_id, ap.paysheet_status, ap.pay_amt_pay_curr_usd, ap.latest_home_currency, TO_CHAR(1 / conv.conversion_rate, '00000000.000000000') AS conversionrate, TO_CHAR(ap.pay_amt_pay_curr_usd * (1 / conv.conversion_rate), '00000000.000000000') AS final_payout FROM aggregated_payments ap INNER JOIN usd_conversion_rates conv ON TRUNC(ap.pay_date) BETWEEN conv.conversion_start_date AND conv.conversion_end_date AND conv.from_currency = ap.latest_home_currency ), -- Step 7: Identify multi-currency participants multi_currency_participants AS ( SELECT participant_id, payrun_id, org_id, period_id, CASE WHEN COUNT(DISTINCT 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 total_amt 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) 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 participant_id, payrun_id, org_id, period_id ), -- Step 8: Get 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 9: 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 10: 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 SELECT statement SELECT -- Record identifiers TO_CHAR( bg.pay_period_id || '-' || SUBSTR( bg.payrun_name, 1 + INSTR(bg.payrun_name, '_', -1, 2), (INSTR(bg.payrun_name, '_', -1, 1) - INSTR(bg.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( bg.pay_period_id || '-' || SUBSTR( bg.payrun_name, 1 + INSTR(bg.payrun_name, '_', -1, 2), (INSTR(bg.payrun_name, '_', -1, 1) - INSTR(bg.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 bg.payrun_id AS "Payrun ID", 'Regular cycle' AS "Payment Cycle", bg.credited_participant_id AS "Participant Id", bg.payrun_name AS "Payment Batch", bg.pay_period_id AS "Pay Period ID", bg.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", bg.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 bg.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", -- Earnings placeholders 'tbd' AS "PTD_EARNINGS_LC", bg.paysheet_status AS "PrePayHub Status", 'tbd' AS "Total Paysheet Amount", -- Currency information prd_ml.multi_curr_yn, bg.conversionrate AS usd_to_local, '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 bg.paysheet_id, bg.pay_amt_pay_curr_usd, bg.final_payout AS final_payout_lc, -- Badge ID pwn.person_number AS badge_id FROM final_payments bg -- Join multi-currency flag INNER JOIN multi_currency_participants prd_ml ON bg.org_id = prd_ml.org_id AND bg.payrun_id = prd_ml.payrun_id AND bg.credited_participant_id = prd_ml.participant_id AND bg.pay_period_id = prd_ml.period_id AND prd_ml.multi_curr_yn = 'Y' -- Join participant details INNER JOIN participant_details pd ON bg.credited_participant_id = pd.participant_id AND bg.pay_date BETWEEN pd.start_date AND NVL(pd.end_date, bg.pay_date) -- Join worker numbers LEFT JOIN participant_worker_numbers pwn ON bg.credited_participant_id = pwn.participant_id -- Join latest plan component INNER JOIN latest_plan_component lpc ON lpc.participant_id = bg.credited_participant_id AND lpc.org_id = bg.org_id AND lpc.period_id = bg.pay_period_id AND lpc.payrun_id = bg.payrun_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 = bg.pay_period_id AND pay_cal.currency_code = bg.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 = bg.credited_participant_id AND pm_ti.org_id = bg.org_id AND pm_ti.period_id = bg.pay_period_id;

Comments

Popular posts from this blog

Child query new

child query latest -

final simple where