dodil/erp-brick-sales-order

DODIL ERP brick: sales order

src/brick_sales_order/migrations/00004_slice_a2/up.sql

-- 00004_slice_a2 — additive only. Order types, item categories and their dated rules, condition types, access sequences, the pricing procedure and
-- condition records (SO-29, SO-30); a line's and an order's condition rows (SO-30, SO-31); rebate agreements and their accruals (SO-32); the new
-- columns on the order, its lines and the customer policy.
CREATE TABLE IF NOT EXISTS so_access_sequence (
    access_sequence_code VARCHAR NOT NULL,
    step INTEGER NOT NULL,
    key_fields VARCHAR,
    exclusive BOOLEAN,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (access_sequence_code, step)
);

CREATE TABLE IF NOT EXISTS so_condition_record (
    condition_type_code VARCHAR NOT NULL,
    key_fields VARCHAR NOT NULL,
    key_value VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE,
    bp_id BIGINT,
    price_group VARCHAR,
    product_id VARCHAR,
    category_code VARCHAR,
    ship_to_country VARCHAR,
    rate NUMERIC(19,4),
    minimum NUMERIC(18,2),
    scale_json VARCHAR,
    currency VARCHAR,
    set_by VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (condition_type_code, key_fields, key_value, valid_from)
);

CREATE TABLE IF NOT EXISTS so_condition_type (
    condition_type_code VARCHAR NOT NULL,
    calculation VARCHAR,
    level VARCHAR,
    header_spread VARCHAR,
    sign VARCHAR,
    access_sequence_code VARCHAR,
    manual_allowed BOOLEAN,
    manual_limit_pct NUMERIC(19,4),
    statistical BOOLEAN,
    scale_basis VARCHAR,
    account_purpose VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (condition_type_code)
);

CREATE TABLE IF NOT EXISTS so_header_condition (
    sales_order_id VARCHAR NOT NULL,
    condition_type_code VARCHAR NOT NULL,
    revision INTEGER NOT NULL,
    step INTEGER,
    rate NUMERIC(19,4),
    amount NUMERIC(18,2),
    source VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (sales_order_id, condition_type_code, revision)
);

CREATE TABLE IF NOT EXISTS so_item_category (
    item_category_code VARCHAR NOT NULL,
    fulfilment VARCHAR,
    billing_relevant BOOLEAN,
    pricing VARCHAR,
    schedule_lines BOOLEAN,
    returns BOOLEAN,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (item_category_code)
);

CREATE TABLE IF NOT EXISTS so_item_category_rule (
    order_type_code VARCHAR NOT NULL,
    product_type_code VARCHAR NOT NULL,
    usage VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    item_category_code VARCHAR,
    allowed_alternatives VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (order_type_code, product_type_code, usage, valid_from)
);

CREATE TABLE IF NOT EXISTS so_line_condition (
    sales_order_id VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    step INTEGER NOT NULL,
    revision INTEGER NOT NULL,
    condition_type_code VARCHAR,
    rate NUMERIC(19,4),
    amount NUMERIC(18,2),
    basis NUMERIC(18,2),
    source VARCHAR,
    statistical BOOLEAN,
    header_share_of VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (sales_order_id, line_no, step, revision)
);

CREATE TABLE IF NOT EXISTS so_order_type (
    order_type_code VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    name VARCHAR,
    series_id VARCHAR,
    direction VARCHAR,
    billing VARCHAR,
    delivery_relevant BOOLEAN,
    reference VARCHAR,
    default_block VARCHAR,
    pricing_procedure_code VARCHAR,
    workflow VARCHAR,
    priority VARCHAR,
    credit_check BOOLEAN,
    special_stock VARCHAR,
    approval_limit VARCHAR,
    status VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (order_type_code, valid_from)
);

CREATE TABLE IF NOT EXISTS so_pricing_procedure (
    pricing_procedure_code VARCHAR NOT NULL,
    step INTEGER NOT NULL,
    condition_type_code VARCHAR,
    from_step INTEGER,
    to_step INTEGER,
    subtotal_name VARCHAR,
    required BOOLEAN,
    statistical BOOLEAN,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (pricing_procedure_code, step)
);

CREATE TABLE IF NOT EXISTS so_rebate_accrual (
    rebate_agreement_id VARCHAR NOT NULL,
    document_id VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    kind VARCHAR,
    issue_date DATE,
    basis_amount NUMERIC(18,2),
    accrual_amount NUMERIC(18,2),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (rebate_agreement_id, document_id, line_no)
);

CREATE TABLE IF NOT EXISTS so_rebate_agreement (
    rebate_agreement_id VARCHAR NOT NULL,
    bp_id BIGINT,
    price_group VARCHAR,
    basis VARCHAR,
    basis_values_json VARCHAR,
    valid_from DATE,
    valid_to DATE,
    scale_json VARCHAR,
    retroactive BOOLEAN,
    accrual_rate_pct NUMERIC(19,4),
    currency VARCHAR,
    lifecycle VARCHAR,
    set_by_email VARCHAR,
    set_by_sub VARCHAR,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (rebate_agreement_id)
);

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

ALTER TABLE so_order ADD COLUMN order_type_code VARCHAR;
ALTER TABLE so_order ADD COLUMN ship_to_bp_id BIGINT;
ALTER TABLE so_order ADD COLUMN ship_to_country VARCHAR;
ALTER TABLE so_order ADD COLUMN price_group VARCHAR;
ALTER TABLE so_order ADD COLUMN ref_sales_order_id VARCHAR;
ALTER TABLE so_order ADD COLUMN ref_line_no INTEGER;
ALTER TABLE so_order ADD COLUMN ref_invoice_id VARCHAR;
ALTER TABLE so_order ADD COLUMN warnings_json VARCHAR;
ALTER TABLE so_order ADD COLUMN freight_unknown VARCHAR;
ALTER TABLE so_line ADD COLUMN category_code VARCHAR;
ALTER TABLE so_line ADD COLUMN product_type_code VARCHAR;
ALTER TABLE so_line ADD COLUMN usage VARCHAR;
ALTER TABLE so_line ADD COLUMN item_category_code VARCHAR;
ALTER TABLE so_line ADD COLUMN gross_weight NUMERIC(19,6);
ALTER TABLE so_line ADD COLUMN weight_unit_code VARCHAR;
ALTER TABLE so_line ADD COLUMN measure_valid_from DATE;
ALTER TABLE so_line ADD COLUMN procedural BOOLEAN;
ALTER TABLE so_customer_policy ADD COLUMN price_group VARCHAR;