dodil/erp-brick-sales-order

DODIL ERP brick: sales order

src/brick_sales_order/migrations/00005_slice_b/up.sql

-- 00005_slice_b — additive only. Rebate settlements, the posting log and the agreement claim (SO-33, SO-34); the accrual's tax category, journal and
-- posting status.
CREATE TABLE IF NOT EXISTS so_posting (
    document_id VARCHAR NOT NULL,
    event VARCHAR NOT NULL,
    journal_id VARCHAR,
    status VARCHAR,
    reason VARCHAR,
    attempts INTEGER,
    rebate_agreement_id VARCHAR,
    payload_json VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (document_id, event)
);

CREATE TABLE IF NOT EXISTS so_rebate_claim (
    rebate_agreement_id VARCHAR NOT NULL,
    seq INTEGER NOT NULL,
    operation VARCHAR,
    claimed_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (rebate_agreement_id, seq)
);

CREATE TABLE IF NOT EXISTS so_rebate_settlement (
    rebate_agreement_id VARCHAR NOT NULL,
    settlement_no INTEGER NOT NULL,
    kind VARCHAR,
    basis_amount NUMERIC(18,2),
    rate_pct NUMERIC(19,4),
    earned_amount NUMERIC(18,2),
    settled_before NUMERIC(18,2),
    amount NUMERIC(18,2),
    true_up_amount NUMERIC(18,2),
    requested_by_email VARCHAR,
    requested_by_sub VARCHAR,
    approved_by_email VARCHAR,
    approved_by_sub VARCHAR,
    credit_id VARCHAR,
    status VARCHAR,
    reason VARCHAR,
    lines_json VARCHAR,
    requested_on DATE,
    approved_on DATE,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (rebate_agreement_id, settlement_no)
);

ALTER TABLE so_send ADD COLUMN schedule_version INTEGER;
ALTER TABLE so_rebate_accrual ADD COLUMN tax_category VARCHAR;
ALTER TABLE so_rebate_accrual ADD COLUMN journal_id VARCHAR;
ALTER TABLE so_rebate_accrual ADD COLUMN posting_status VARCHAR;
CREATE TABLE IF NOT EXISTS so_customer_partner (
    sold_to_bp_id BIGINT NOT NULL,
    function VARCHAR NOT NULL,
    partner_bp_id BIGINT NOT NULL,
    valid_from DATE NOT NULL,
    is_default BOOLEAN,
    transit_days_json VARCHAR,
    status VARCHAR,
    set_by VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (sold_to_bp_id, function, partner_bp_id, valid_from)
);

CREATE TABLE IF NOT EXISTS so_incompletion_rule (
    order_type_code VARCHAR NOT NULL,
    field VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    blocks VARCHAR,
    condition VARCHAR,
    reason_text VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (order_type_code, field, valid_from)
);

CREATE TABLE IF NOT EXISTS so_block_reason (
    block_reason_code VARCHAR NOT NULL,
    kind VARCHAR,
    name VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (block_reason_code)
);

CREATE TABLE IF NOT EXISTS so_order_block (
    sales_order_id VARCHAR NOT NULL,
    block_no INTEGER NOT NULL,
    kind VARCHAR,
    reason_code VARCHAR,
    event VARCHAR,
    note VARCHAR,
    by_email VARCHAR,
    by_sub VARCHAR,
    at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (sales_order_id, block_no)
);

ALTER TABLE so_order ADD COLUMN bill_to_bp_id BIGINT;
ALTER TABLE so_order ADD COLUMN payer_bp_id BIGINT;
ALTER TABLE so_order ADD COLUMN requested_delivery_on DATE;
ALTER TABLE so_order ADD COLUMN priority VARCHAR;
ALTER TABLE so_order ADD COLUMN partial_delivery VARCHAR;
ALTER TABLE so_order ADD COLUMN max_partials INTEGER;
ALTER TABLE so_order ADD COLUMN under_tolerance_pct NUMERIC(19,4);
ALTER TABLE so_order ADD COLUMN over_tolerance_pct NUMERIC(19,4);
ALTER TABLE so_order ADD COLUMN combine_allowed BOOLEAN;
ALTER TABLE so_order ADD COLUMN min_shelf_life_days INTEGER;
ALTER TABLE so_order ADD COLUMN return_reason VARCHAR;
ALTER TABLE so_customer_policy ADD COLUMN partial_delivery VARCHAR;
ALTER TABLE so_customer_policy ADD COLUMN max_partials INTEGER;
ALTER TABLE so_customer_policy ADD COLUMN under_tolerance_pct NUMERIC(19,4);
ALTER TABLE so_customer_policy ADD COLUMN over_tolerance_pct NUMERIC(19,4);
ALTER TABLE so_customer_policy ADD COLUMN combine_allowed BOOLEAN;
ALTER TABLE so_customer_policy ADD COLUMN min_shelf_life_days INTEGER;

CREATE TABLE IF NOT EXISTS so_schedule_line (
    sales_order_id VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    schedule_version INTEGER NOT NULL,
    seq INTEGER NOT NULL,
    requested_on DATE,
    requested_quantity NUMERIC(19,6),
    confirmed_on DATE,
    material_available_on DATE,
    confirmed_quantity NUMERIC(19,6),
    confirmed_base NUMERIC(19,6),
    reason VARCHAR,
    at TIMESTAMP,
    trigger_key VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (sales_order_id, line_no, schedule_version, seq)
);

CREATE TABLE IF NOT EXISTS so_task (
    sales_order_id VARCHAR NOT NULL,
    kind VARCHAR NOT NULL,
    raised_on DATE NOT NULL,
    owner VARCHAR,
    text VARCHAR,
    closed_by VARCHAR,
    closed_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (sales_order_id, kind, raised_on)
);