dodil/erp-brick-sales-order

DODIL ERP brick: sales order

src/brick_sales_order/schema.sql

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_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_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_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_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_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_customer_policy (
    bp_id BIGINT NOT NULL,
    valid_from DATE NOT NULL,
    po_required BOOLEAN,
    deposit_pct NUMERIC(19,4),
    delivery_terms VARCHAR,
    set_by VARCHAR,
    price_group VARCHAR,
    partial_delivery VARCHAR,
    max_partials INTEGER,
    under_tolerance_pct NUMERIC(19,4),
    over_tolerance_pct NUMERIC(19,4),
    combine_allowed BOOLEAN,
    min_shelf_life_days INTEGER,
    deposit_deduction_mode VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (bp_id, valid_from)
);

CREATE TABLE IF NOT EXISTS so_delivery (
    sales_order_id VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    delivery_ref VARCHAR NOT NULL,
    quantity NUMERIC(19,4),
    kind VARCHAR,
    corrects_ref VARCHAR,
    delivered_on DATE,
    recorded_by VARCHAR,
    source VARCHAR,
    quantity_base NUMERIC(19,4),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (sales_order_id, line_no, delivery_ref)
);

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_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_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_invoice_request (
    sales_order_id VARCHAR NOT NULL,
    request_no INTEGER NOT NULL,
    kind VARCHAR,
    lines_json VARCHAR,
    invoice_id VARCHAR,
    requested_at TIMESTAMP,
    requested_by VARCHAR,
    amount NUMERIC(18,2),
    deducted NUMERIC(18,2),
    status VARCHAR,
    deductions_json VARCHAR,
    source_ref VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (sales_order_id, request_no)
);

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 (
    sales_order_id VARCHAR NOT NULL,
    line_no INTEGER NOT NULL,
    product_id VARCHAR,
    description VARCHAR,
    unit_code VARCHAR,
    quantity NUMERIC(19,4),
    unit_numerator BIGINT,
    unit_denominator BIGINT,
    list_price NUMERIC(19,4),
    unit_price NUMERIC(19,4),
    discount_pct NUMERIC(19,4),
    priced_on DATE,
    priced_from VARCHAR,
    tax_category VARCHAR,
    tax_code VARCHAR,
    net_amount NUMERIC(18,2),
    tax_amount NUMERIC(18,2),
    invoicing_policy VARCHAR,
    billing_kind VARCHAR,
    billing_plan VARCHAR,
    quote_ref VARCHAR,
    odoo_line_id VARCHAR,
    pricing_quantity BIGINT,
    pricing_unit_code VARCHAR,
    pricing_numerator BIGINT,
    pricing_denominator BIGINT,
    stocked BOOLEAN,
    reserved_base NUMERIC(19,4),
    short_base NUMERIC(19,4),
    category_code VARCHAR,
    product_type_code VARCHAR,
    usage VARCHAR,
    item_category_code VARCHAR,
    gross_weight NUMERIC(19,6),
    weight_unit_code VARCHAR,
    measure_valid_from DATE,
    procedural BOOLEAN,
    ref_sales_order_id VARCHAR,
    ref_line_no INTEGER,
    ref_delivery_ref VARCHAR,
    ref_invoice_id VARCHAR,
    ref_invoice_line_no INTEGER,
    instalments INTEGER,
    statistical_value NUMERIC(18,2),
    purchase_request_id VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (sales_order_id, line_no)
);

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_migration_map (
    odoo_db VARCHAR NOT NULL,
    odoo_model VARCHAR NOT NULL,
    odoo_id BIGINT NOT NULL,
    sales_order_id VARCHAR,
    line_no INTEGER,
    delivery_ref VARCHAR,
    migrated_at TIMESTAMP,
    amount NUMERIC(18,2),
    tax_amount NUMERIC(18,2),
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (odoo_db, odoo_model, odoo_id)
);

CREATE TABLE IF NOT EXISTS so_number_series (
    series_id VARCHAR NOT NULL,
    high_water_mark INTEGER,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (series_id)
);

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

CREATE TABLE IF NOT EXISTS so_order (
    sales_order_id VARCHAR NOT NULL,
    order_no VARCHAR,
    bp_id BIGINT,
    owner VARCHAR,
    currency VARCHAR,
    price_list_id VARCHAR,
    lifecycle VARCHAR,
    revision INTEGER,
    customer_po_ref VARCHAR,
    ordered_on DATE,
    confirmed_at TIMESTAMP,
    delivery_terms VARCHAR,
    payment_terms_id VARCHAR,
    deposit_pct NUMERIC(19,4),
    subtotal NUMERIC(18,2),
    tax_total NUMERIC(18,2),
    total NUMERIC(18,2),
    source_kind VARCHAR,
    source_id VARCHAR,
    version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    confirmed_on DATE,
    credit_override_by VARCHAR,
    credit_override_reason VARCHAR,
    cancel_reason VARCHAR,
    order_type_code VARCHAR,
    ship_to_bp_id BIGINT,
    ship_to_country VARCHAR,
    price_group VARCHAR,
    ref_sales_order_id VARCHAR,
    ref_line_no INTEGER,
    ref_invoice_id VARCHAR,
    warnings_json VARCHAR,
    freight_unknown VARCHAR,
    bill_to_bp_id BIGINT,
    payer_bp_id BIGINT,
    requested_delivery_on DATE,
    priority VARCHAR,
    partial_delivery VARCHAR,
    max_partials INTEGER,
    under_tolerance_pct NUMERIC(19,4),
    over_tolerance_pct NUMERIC(19,4),
    combine_allowed BOOLEAN,
    min_shelf_life_days INTEGER,
    return_reason VARCHAR,
    deposit_deduction_mode VARCHAR,
    owner_sub VARCHAR,
    approved_by_email VARCHAR,
    approved_by_sub VARCHAR,
    approved_at TIMESTAMP,
    ship_to_address_lines VARCHAR,
    ship_to_address_city VARCHAR,
    ship_to_address_postcode VARCHAR,
    ship_to_address_region VARCHAR,
    PRIMARY KEY (sales_order_id)
);

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)
);

CREATE TABLE IF NOT EXISTS so_order_no_claim (
    order_no VARCHAR NOT NULL,
    sales_order_id VARCHAR,
    claimed_at TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (order_no)
);

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_order_versions (
    sales_order_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP,
    PRIMARY KEY (sales_order_id, version)
);

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_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),
    tax_category VARCHAR,
    journal_id VARCHAR,
    posting_status VARCHAR,
    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,
    PRIMARY KEY (rebate_agreement_id, version)
);

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)
);

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)
);

CREATE TABLE IF NOT EXISTS so_revision (
    sales_order_id VARCHAR NOT NULL,
    revision INTEGER NOT NULL,
    reason VARCHAR,
    changed_lines_json VARCHAR,
    approved_by VARCHAR,
    approved_at TIMESTAMP,
    status VARCHAR,
    proposed_by VARCHAR,
    proposed_at TIMESTAMP,
    proposed_sub VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (sales_order_id, revision)
);

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_send (
    sales_order_id VARCHAR NOT NULL,
    sent_at TIMESTAMP NOT NULL,
    channel VARCHAR,
    address VARCHAR,
    revision INTEGER,
    sent_by VARCHAR,
    schedule_version INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (sales_order_id, sent_at)
);

CREATE TABLE IF NOT EXISTS so_settings (
    setting_key VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    value VARCHAR,
    set_by VARCHAR,
    set_at TIMESTAMP,
    changes VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (setting_key, valid_from)
);

CREATE TABLE IF NOT EXISTS so_suggestion (
    sales_order_id VARCHAR NOT NULL,
    step VARCHAR NOT NULL,
    suggestion_no INTEGER NOT NULL,
    model VARCHAR,
    inputs_ref VARCHAR,
    output_json VARCHAR,
    decision VARCHAR,
    decided_by VARCHAR,
    decided_at TIMESTAMP,
    decisions_json VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (sales_order_id, step, suggestion_no)
);

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)
);