dodil/erp-brick-payroll

DODIL ERP brick: payroll

src/brick_payroll/migrations/00003_statutory_rows/up.sql

-- 00003_statutory_rows - data only (slice B item 1): the pay elements for statutory sick and maternity pay and the dated SSP / SMP / lower earnings limit rows,
-- each flagged to_confirm (module.yaml's own figure, or UNVERIFIED where the builder supplied it: see AGENTS.md). Plain INSERTs: the migration journal runs this once.

INSERT INTO prl_element (company_code, element_code, valid_from, name, kind, taxable, niable, pensionable, gl_purpose, maps_from, created_at, updated_at) VALUES
    ('*', 'SICK_DEDUCTION', DATE '2000-01-01', 'Sickness (salary not paid)', 'earning', TRUE, TRUE, TRUE, 'gross_pay', 'tof:sick', NOW(), NOW()),
    ('*', 'SSP', DATE '2000-01-01', 'Statutory sick pay', 'statutory', TRUE, TRUE, TRUE, 'gross_pay', 'tof:ssp', NOW(), NOW()),
    ('*', 'MATERNITY_DEDUCTION', DATE '2000-01-01', 'Maternity leave (salary not paid)', 'earning', TRUE, TRUE, TRUE, 'gross_pay', 'tof:statutory_maternity', NOW(), NOW()),
    ('*', 'SMP', DATE '2000-01-01', 'Statutory maternity pay', 'statutory', TRUE, TRUE, TRUE, 'gross_pay', 'tof:smp', NOW(), NOW());

INSERT INTO prl_statutory_rate (rate_key, region, frequency, valid_from, value, unit, source, to_confirm, set_by, created_at, updated_at) VALUES
    ('ssp_weekly_rate', 'uk', 'weekly', DATE '2026-04-06', 123.2500, 'amount', 'payroll module.yaml PRL-07 / PRL-09 (to confirm against HMRC)', TRUE, 'seed', NOW(), NOW()),
    ('ssp_waiting_days', 'uk', 'any', DATE '2000-01-01', 3.0000, 'count', 'UNVERIFIED: supplied by the builder 2026-10-26, check against HMRC (to confirm): 3 waiting days before 6 April 2026', TRUE, 'seed', NOW(), NOW()),
    ('ssp_waiting_days', 'uk', 'any', DATE '2026-04-06', 0.0000, 'count', 'payroll module.yaml PRL-07 / PRL-09 (to confirm against HMRC): payable from the first qualifying day', TRUE, 'seed', NOW(), NOW()),
    ('ssp_lel_test', 'uk', 'any', DATE '2000-01-01', 1.0000, 'flag', 'UNVERIFIED: supplied by the builder 2026-10-26, check against HMRC (to confirm): SSP needs average earnings at or above the LEL before 6 April 2026', TRUE, 'seed', NOW(), NOW()),
    ('ssp_lel_test', 'uk', 'any', DATE '2026-04-06', 0.0000, 'flag', 'UNVERIFIED: supplied by the builder 2026-10-26, check against HMRC (to confirm): no LEL test from 6 April 2026 (the 80% cap instead)', TRUE, 'seed', NOW(), NOW()),
    ('ssp_earnings_cap_pct', 'uk', 'any', DATE '2026-04-06', 80.0000, 'percent', 'payroll module.yaml PRL-07 / PRL-09 (to confirm against HMRC): the lower of the rate and 80% of average weekly earnings', TRUE, 'seed', NOW(), NOW()),
    ('ni_lower_earnings_limit', 'uk', 'weekly', DATE '2026-04-06', 129.0000, 'amount', 'UNVERIFIED: supplied by the builder 2026-10-26, check against HMRC (to confirm)', TRUE, 'seed', NOW(), NOW()),
    ('smp_weekly_flat_rate', 'uk', 'weekly', DATE '2026-04-06', 194.3200, 'amount', 'payroll module.yaml PRL-07 / PRL-09 (to confirm against HMRC)', TRUE, 'seed', NOW(), NOW()),
    ('smp_higher_pct', 'uk', 'any', DATE '2026-04-06', 90.0000, 'percent', 'payroll module.yaml PRL-07 / PRL-09 (to confirm against HMRC)', TRUE, 'seed', NOW(), NOW()),
    ('smp_higher_weeks', 'uk', 'any', DATE '2026-04-06', 6.0000, 'count', 'payroll module.yaml PRL-07 / PRL-09 (to confirm against HMRC)', TRUE, 'seed', NOW(), NOW()),
    ('smp_total_weeks', 'uk', 'any', DATE '2026-04-06', 39.0000, 'count', 'UNVERIFIED: supplied by the builder 2026-10-26, check against HMRC (to confirm): 39 weeks of SMP', TRUE, 'seed', NOW(), NOW()),
    ('smp_recovery_pct', 'uk', 'any', DATE '2026-04-06', 92.0000, 'percent', 'payroll module.yaml PRL-07 / PRL-09 (to confirm against HMRC)', TRUE, 'seed', NOW(), NOW()),
    ('ssp_requires_piw', 'uk', 'any', DATE '2000-01-01', 1.0000, 'flag', 'UNVERIFIED: supplied by the builder 2026-10-26, check against HMRC (to confirm): SSP is paid only in a period of incapacity for work (4 or more days); PRL-07''s example pays 3 days: set 0 to follow it', TRUE, 'seed', NOW(), NOW()),
    ('ssp_max_weeks', 'uk', 'any', DATE '2026-04-06', 28.0000, 'count', 'UNVERIFIED: supplied by the builder 2026-10-26, check against HMRC (to confirm): SSP is limited to 28 weeks in a linked period', TRUE, 'seed', NOW(), NOW()),
    ('ssp_self_certification_days', 'uk', 'any', DATE '2026-04-06', 7.0000, 'count', 'UNVERIFIED: supplied by the builder 2026-10-26, check against HMRC (to confirm): a fit note is needed from the 8th calendar day', TRUE, 'seed', NOW(), NOW()),
    ('smp_small_employer_recovery_pct', 'uk', 'any', DATE '2026-04-06', 109.0000, 'percent', 'gov.uk Rates and thresholds for employers 2026 to 2027, checked 2026-10-26', TRUE, 'seed', NOW(), NOW());