dodil/erp-brick-ar

DODIL ERP brick: accounts receivable

src/brick_ar/migrations/00004_v4b/up.sql

-- 00004_v4b (v0.1.3): spec v4 slice B: the e-invoice send and its statuses, deferred revenue releases, the doubtful-debt provision and its rates,
-- cost centres on lines and the customer's e-invoice channel and default cost centre. ADD COLUMN is DISRUPTIVE: migrate the test copy first, the live one at a quiet hour.

ALTER TABLE ar_invoices ADD COLUMN einvoice_message_id VARCHAR;

ALTER TABLE ar_invoice_lines ADD COLUMN cost_centre VARCHAR;

ALTER TABLE ar_credit_note_lines ADD COLUMN cost_centre VARCHAR;

ALTER TABLE ar_customer ADD COLUMN einvoice_channel VARCHAR;

ALTER TABLE ar_customer ADD COLUMN peppol_participant_id VARCHAR;

ALTER TABLE ar_customer ADD COLUMN default_cost_centre VARCHAR;

ALTER TABLE ar_sends ADD COLUMN message_id VARCHAR;

ALTER TABLE ar_sends ADD COLUMN statuses_json VARCHAR;

CREATE TABLE IF NOT EXISTS ar_deferral_release (
    invoice_id   VARCHAR NOT NULL,
    line_no      INTEGER NOT NULL,
    period_id    VARCHAR NOT NULL,
    kind         VARCHAR,
    amount       NUMERIC(18,2),
    journal_date DATE,
    released_at  TIMESTAMP,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (invoice_id, line_no, period_id)
);

CREATE TABLE IF NOT EXISTS ar_provision_run (
    run_id          VARCHAR NOT NULL,
    company_code    VARCHAR,
    period_id       VARCHAR,
    lifecycle       VARCHAR,
    as_at           DATE,
    lines_json      VARCHAR,
    total           NUMERIC(18,2),
    previous_total  NUMERIC(18,2),
    movement        NUMERIC(18,2),
    proposed_by     VARCHAR,
    proposed_by_sub VARCHAR,
    approved_by     VARCHAR,
    approved_at     TIMESTAMP,
    rejected_reason VARCHAR,
    journal_id      BIGINT,
    version         INTEGER,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (run_id)
);

CREATE TABLE IF NOT EXISTS ar_provision_run_versions (
    run_id   VARCHAR NOT NULL,
    version  INTEGER NOT NULL,
    saved_at TIMESTAMP NOT NULL,
    PRIMARY KEY (run_id, version)
);

CREATE TABLE IF NOT EXISTS ar_provision_rate (
    bucket     VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    rate_pct   NUMERIC(9,4),
    set_by     VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (bucket, valid_from)
);

CREATE TABLE IF NOT EXISTS ar_einvoice_message (
    message_id    VARCHAR NOT NULL,
    document_id   VARCHAR,
    document_kind VARCHAR,
    provider      VARCHAR,
    sent_at       TIMESTAMP,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (message_id)
);

CREATE TABLE IF NOT EXISTS ar_einvoice_status (
    message_id VARCHAR NOT NULL,
    status     VARCHAR NOT NULL,
    status_at  TIMESTAMP,
    reason     VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (message_id, status)
);