-- 00004_v4_slice_b: additive only. Product spec v4, slice B (PRD-34..39): product types and their code series, relative unit
-- conversions on templates, characteristics and classification, site data, rounding profiles.
ALTER TABLE prod_product ADD COLUMN IF NOT EXISTS product_type_code VARCHAR;
ALTER TABLE prod_product ADD COLUMN IF NOT EXISTS stocked_default BOOLEAN;
ALTER TABLE prod_product ADD COLUMN IF NOT EXISTS commodity_code VARCHAR;
ALTER TABLE prod_product ADD COLUMN IF NOT EXISTS country_of_origin VARCHAR;
ALTER TABLE prod_template ADD COLUMN IF NOT EXISTS product_type_code VARCHAR;
ALTER TABLE prod_template ADD COLUMN IF NOT EXISTS commodity_code VARCHAR;
ALTER TABLE prod_template ADD COLUMN IF NOT EXISTS country_of_origin VARCHAR;
ALTER TABLE prod_product_unit ADD COLUMN IF NOT EXISTS relative_unit_code VARCHAR;
ALTER TABLE prod_product_unit ADD COLUMN IF NOT EXISTS used_ref VARCHAR;
-- v4 slice B (PRD-35): what kind of product this is for every module. Configuration, seeded; retired, never deleted.
CREATE TABLE IF NOT EXISTS prod_product_type (
product_type_code VARCHAR NOT NULL,
name VARCHAR,
kind VARCHAR,
stocked_default BOOLEAN,
sellable BOOLEAN,
purchasable BOOLEAN,
required_roles VARCHAR,
code_format VARCHAR,
valuation_class VARCHAR,
production_fields BOOLEAN,
status VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (product_type_code)
);
-- A type's code series: one claim row per number, a plain INSERT in the add's own transaction (never reused, no gap).
CREATE TABLE IF NOT EXISTS prod_code_series (
product_type_code VARCHAR NOT NULL,
number INTEGER NOT NULL,
product_id VARCHAR,
claimed_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (product_type_code, number)
);
-- A document or stock used this product: its type is fixed from then on.
CREATE TABLE IF NOT EXISTS prod_product_use (
product_id VARCHAR NOT NULL,
used_at TIMESTAMP,
used_ref VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (product_id)
);
-- v4 slice B (PRD-36): characteristics, classes, and a product's class and values FROM A DATE.
CREATE TABLE IF NOT EXISTS prod_characteristic (
characteristic_code VARCHAR NOT NULL,
name VARCHAR,
data_type VARCHAR,
unit_code VARCHAR,
allowed_values VARCHAR,
multi_value BOOLEAN,
decimals INTEGER,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (characteristic_code)
);
CREATE TABLE IF NOT EXISTS prod_class (
class_code VARCHAR NOT NULL,
name VARCHAR,
characteristics VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (class_code)
);
CREATE TABLE IF NOT EXISTS prod_classification (
owner VARCHAR NOT NULL,
class_code VARCHAR NOT NULL,
characteristic_code VARCHAR NOT NULL,
valid_from DATE NOT NULL,
value VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (owner, class_code, characteristic_code, valid_from)
);
-- v4 slice B (PRD-37): how one product is procured, planned and allowed at one site, FROM A DATE after today. Base-unit quantities.
CREATE TABLE IF NOT EXISTS prod_site (
product_id VARCHAR NOT NULL,
site_id VARCHAR NOT NULL,
valid_from DATE NOT NULL,
site_status VARCHAR,
procurement_type VARCHAR,
supplying_site_id VARCHAR,
planned_delivery_days INTEGER,
gr_processing_days INTEGER,
planning_method VARCHAR,
reorder_point NUMERIC(19,4),
safety_stock NUMERIC(19,4),
max_stock NUMERIC(19,4),
lot_size_method VARCHAR,
fixed_lot_size NUMERIC(19,4),
rounding_profile_code VARCHAR,
mrp_controller VARCHAR,
planning_time_fence_days INTEGER,
lot_size_procedure VARCHAR,
production_version VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (product_id, site_id, valid_from)
);
-- v4 slice B (PRD-38): pallet and layer rounding for system-proposed quantities.
CREATE TABLE IF NOT EXISTS prod_rounding_profile (
rounding_profile_code VARCHAR NOT NULL,
unit_code VARCHAR,
steps VARCHAR,
status VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (rounding_profile_code)
);
INSERT INTO prod_product_type (product_type_code, name, kind, stocked_default, sellable, purchasable, required_roles, code_format, valuation_class, production_fields, status)
SELECT v.c, v.n, v.k, v.st, v.se, v.pu, v.r, v.f, v.vc, FALSE, 'active'
FROM (VALUES ('TRADING', 'Trading goods', 'goods', TRUE, TRUE, TRUE, '["sales", "purchase"]', 'TRD-{seq:5}', 'trading_goods'),
('FINISHED', 'Finished goods', 'goods', TRUE, TRUE, FALSE, '[]', 'typed', 'finished_goods'),
('RAW', 'Raw materials', 'goods', TRUE, FALSE, TRUE, '[]', 'typed', 'raw_materials'),
('SERVICE', 'Services', 'service', FALSE, TRUE, TRUE, '[]', 'typed', ''),
('NONSTOCK', 'Non-stock goods bought for use', 'goods', FALSE, FALSE, TRUE, '[]', 'typed', ''),
('PACKAGING', 'Packaging', 'goods', TRUE, FALSE, TRUE, '[]', 'typed', 'packaging'))
AS v(c, n, k, st, se, pu, r, f, vc)
WHERE v.c NOT IN (SELECT product_type_code FROM prod_product_type);
INSERT INTO prod_unit_conversion (from_unit_code, to_unit_code, numerator, denominator)
SELECT v.f, v.t, v.n, v.d
FROM (VALUES ('MTR', 'CMT', 100, 1), ('MTR', 'MMT', 1000, 1), ('CMT', 'MMT', 10, 1)) AS v(f, t, n, d)
WHERE NOT EXISTS (SELECT 1 FROM prod_unit_conversion c WHERE c.from_unit_code = v.f AND c.to_unit_code = v.t);