dodil/erp-brick-sales-order

DODIL ERP brick: sales order

src/brick_sales_order/migrations/00006_slice_c/up.sql

-- 00006_slice_c — additive only. Returns and inspection (SO-41, SO-49), credit and debit requests (SO-42), free of charge (SO-43), milestones (SO-46),
-- deposits deducted per deposit (SO-47), third-party lines (SO-48): the claim tables, the milestone and inspection rows, the return reasons, and the
-- columns an order, a line, an invoice request and a customer policy gained.
CREATE TABLE IF NOT EXISTS so_billing_milestone (
    sales_order_id VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    milestone_no INTEGER NOT NULL,
    pct NUMERIC(19,4),
    event VARCHAR,
    name VARCHAR,
    due_on DATE,
    released_on DATE,
    released_by VARCHAR,
    amount NUMERIC(18,2),
    billed_request_no INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (sales_order_id, line_no, milestone_no)
);

CREATE TABLE IF NOT EXISTS so_credit_claim (
    claim_key VARCHAR NOT NULL,
    seq INTEGER NOT NULL,
    kind VARCHAR,
    sales_order_id VARCHAR,
    request_no INTEGER,
    amount NUMERIC(18,2),
    quantity NUMERIC(19,4),
    claimed_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (claim_key, seq)
);

CREATE TABLE IF NOT EXISTS so_deposit_deduction (
    deposit_invoice_id VARCHAR NOT NULL,
    seq INTEGER NOT NULL,
    kind VARCHAR,
    sales_order_id VARCHAR,
    request_no INTEGER,
    amount NUMERIC(18,2),
    tax_category VARCHAR,
    claimed_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (deposit_invoice_id, seq)
);

CREATE TABLE IF NOT EXISTS so_return_inspection (
    sales_order_id VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    inspection_ref VARCHAR NOT NULL,
    received_quantity NUMERIC(19,4),
    accepted_quantity NUMERIC(19,4),
    rejected_quantity NUMERIC(19,4),
    decision VARCHAR,
    reason VARCHAR,
    inspected_on DATE,
    recorded_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (sales_order_id, line_no, inspection_ref)
);

CREATE TABLE IF NOT EXISTS so_return_reason (
    return_reason_code VARCHAR NOT NULL,
    name VARCHAR,
    inspect BOOLEAN,
    restocking_fee_pct NUMERIC(19,4),
    credit_on VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (return_reason_code)
);

ALTER TABLE so_order ADD COLUMN deposit_deduction_mode VARCHAR;
ALTER TABLE so_order ADD COLUMN owner_sub VARCHAR;
ALTER TABLE so_order ADD COLUMN approved_by_email VARCHAR;
ALTER TABLE so_order ADD COLUMN approved_by_sub VARCHAR;
ALTER TABLE so_order ADD COLUMN approved_at TIMESTAMP;
ALTER TABLE so_line ADD COLUMN ref_sales_order_id VARCHAR;
ALTER TABLE so_line ADD COLUMN ref_line_no INTEGER;
ALTER TABLE so_line ADD COLUMN ref_delivery_ref VARCHAR;
ALTER TABLE so_line ADD COLUMN ref_invoice_id VARCHAR;
ALTER TABLE so_line ADD COLUMN ref_invoice_line_no INTEGER;
ALTER TABLE so_line ADD COLUMN instalments INTEGER;
ALTER TABLE so_line ADD COLUMN statistical_value NUMERIC(18,2);
ALTER TABLE so_line ADD COLUMN purchase_request_id VARCHAR;
ALTER TABLE so_invoice_request ADD COLUMN deductions_json VARCHAR;
ALTER TABLE so_invoice_request ADD COLUMN source_ref VARCHAR;
ALTER TABLE so_customer_policy ADD COLUMN deposit_deduction_mode VARCHAR;