dodil/erp-brick-payroll

DODIL ERP brick: payroll

src/brick_payroll/migrations/00007_ytd/up.sql

-- 00007_ytd - slice B item 5: the previous provider's year to date (PRL-24): a run per load (loaded -> reconciled -> signed | discarded) and one opening row per
-- employment and tax year. Additive only.

CREATE TABLE IF NOT EXISTS prl_ytd_opening (
    ytd_ref             VARCHAR NOT NULL,
    payroll_id          VARCHAR,
    tax_year            VARCHAR,
    paye_ref            VARCHAR,
    run_id              VARCHAR,
    tax_code            VARCHAR,
    basis               VARCHAR,
    gross_amount        NUMERIC(18,2),
    taxable_pay_amount  NUMERIC(18,2),
    tax_amount          NUMERIC(18,2),
    ee_ni_amount        NUMERIC(18,2),
    er_ni_amount        NUMERIC(18,2),
    ni_by_letter_json   VARCHAR,
    statutory_json      VARCHAR,
    pension_amount      NUMERIC(18,2),
    source_file_key     VARCHAR,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    PRIMARY KEY (ytd_ref)
);

CREATE TABLE IF NOT EXISTS prl_ytd_run (
    run_id          VARCHAR NOT NULL,
    tax_year        VARCHAR,
    paye_ref        VARCHAR,
    company_code    VARCHAR,
    schedule_code   VARCHAR,
    cutover_date    DATE,
    source_ref      VARCHAR,
    status          VARCHAR,
    fps_json        VARCHAR,
    checks_json     VARCHAR,
    loaded_by       VARCHAR,
    signed_by       VARCHAR,
    signed_sub      VARCHAR,
    signed_on       DATE,
    discarded_by    VARCHAR,
    discard_reason  VARCHAR,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (run_id)
);