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
Post a Comment