dodil/erp-brick-ar

DODIL ERP brick: accounts receivable

src/brick_ar/migrations/00003_v4a/up.sql

-- 00003_v4a (v0.1.2): spec v4 slice A: payment requests, deposit deductions with claims, settlement discounts, statutory interest,
-- intercompany (company_code, trading_partner) and the hand-over. ADD COLUMN is DISRUPTIVE: migrate the test copy first, the live one at a quiet hour.

ALTER TABLE ar_invoices ADD COLUMN company_code VARCHAR;

ALTER TABLE ar_invoices ADD COLUMN trading_partner VARCHAR;

ALTER TABLE ar_invoices ADD COLUMN settlement_discount_pct NUMERIC(9,4);

ALTER TABLE ar_invoices ADD COLUMN settlement_discount_until DATE;

ALTER TABLE ar_invoice_lines ADD COLUMN pinned_from VARCHAR;

ALTER TABLE ar_invoice_lines ADD COLUMN interest_on_invoice_id VARCHAR;

ALTER TABLE ar_invoice_lines ADD COLUMN interest_days INTEGER;

ALTER TABLE ar_invoice_lines ADD COLUMN interest_rate_pct NUMERIC(9,4);

ALTER TABLE ar_credit_notes ADD COLUMN payment_id VARCHAR;

ALTER TABLE ar_credit_notes ADD COLUMN company_code VARCHAR;

ALTER TABLE ar_credit_note_lines ADD COLUMN pinned_from VARCHAR;

ALTER TABLE ar_customer ADD COLUMN intercompany_company_code VARCHAR;

ALTER TABLE ar_terms ADD COLUMN early_discount_days INTEGER;

CREATE TABLE IF NOT EXISTS ar_payment_request (
    request_id          VARCHAR NOT NULL,
    bp_id               BIGINT,
    company_code        VARCHAR,
    lifecycle           VARCHAR,
    kind                VARCHAR,
    sales_order_id      VARCHAR,
    currency            VARCHAR,
    tax_category        VARCHAR,
    net_amount          NUMERIC(18,2),
    tax_amount_display  NUMERIC(18,2),
    total               NUMERIC(18,2),
    request_no          VARCHAR,
    issue_date          DATE,
    due_date            DATE,
    deposit_invoice_ids VARCHAR,
    cancel_reason       VARCHAR,
    created_by          VARCHAR,
    version             INTEGER,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    PRIMARY KEY (request_id)
);

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

CREATE TABLE IF NOT EXISTS ar_request_settlement (
    payment_id         VARCHAR NOT NULL,
    request_id         VARCHAR,
    n                  INTEGER,
    received_on        DATE,
    amount             NUMERIC(18,2),
    deposit_invoice_id VARCHAR,
    settled_at         TIMESTAMP,
    created_at         TIMESTAMP,
    updated_at         TIMESTAMP,
    PRIMARY KEY (payment_id)
);

CREATE TABLE IF NOT EXISTS ar_deposit_claim (
    deposit_invoice_id VARCHAR NOT NULL,
    n                  INTEGER NOT NULL,
    final_invoice_id   VARCHAR,
    net                NUMERIC(18,2),
    claimed_at         TIMESTAMP,
    created_at         TIMESTAMP,
    updated_at         TIMESTAMP,
    PRIMARY KEY (deposit_invoice_id, n)
);

CREATE TABLE IF NOT EXISTS ar_settlement_discount (
    payment_id VARCHAR NOT NULL,
    invoice_id VARCHAR,
    credit_id  VARCHAR,
    claimed_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (payment_id)
);

CREATE TABLE IF NOT EXISTS ar_interest_rate (
    valid_from         DATE NOT NULL,
    reference_rate_pct NUMERIC(9,4),
    margin_pct         NUMERIC(9,4),
    source             VARCHAR,
    set_by             VARCHAR,
    created_at         TIMESTAMP,
    updated_at         TIMESTAMP,
    PRIMARY KEY (valid_from)
);

CREATE TABLE IF NOT EXISTS ar_ic_handover (
    document_id     VARCHAR NOT NULL,
    bill_id         VARCHAR,
    partner_company VARCHAR,
    handed_at       TIMESTAMP,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (document_id)
);