-- Interface Module: UomClass_Fnd
-- Interface: Uom_Class_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE uom_class_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: UOM_CLASS_OUT
-- This table is used to integrate UOM Class information.

CREATE TABLE UOM_CLASS_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT uom_class_out_seq.nextval NOT NULL,
  -- bdi internal column
  bdi_app_name VARCHAR2(50) DEFAULT sys_context('userenv', 'current_schema') NOT NULL,
  -- bdi internal column
  bdi_dataset_type VARCHAR2(20) DEFAULT 'FULL',
  -- bdi internal column
  bdi_dataset_action VARCHAR2(20) DEFAULT 'REPLACE',
  -- Contains a string that uniquely identifies the unit of measure. Example:  LBS for pounds.
  uom VARCHAR2(4) NOT NULL,
  -- Contains the unit of measure type used as a grouping mechanism for the many UOM options.  When converting from one UOM to another, the class is used to determine how the system proceeds with the conversion, whether it is an in-class or across-class conversion.
  uom_class VARCHAR2(6) NOT NULL
);

COMMENT ON TABLE UOM_CLASS_OUT IS 'This table is used to integrate UOM Class information.';
COMMENT ON COLUMN UOM_CLASS_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN UOM_CLASS_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN UOM_CLASS_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN UOM_CLASS_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN UOM_CLASS_OUT.uom IS 'Contains a string that uniquely identifies the unit of measure. Example:  LBS for pounds.';
COMMENT ON COLUMN UOM_CLASS_OUT.uom_class IS 'Contains the unit of measure type used as a grouping mechanism for the many UOM options.  When converting from one UOM to another, the class is used to determine how the system proceeds with the conversion, whether it is an in-class or across-class conversion.';


-- Add BDI primary key constraint

ALTER TABLE UOM_CLASS_OUT ADD CONSTRAINT pk_uom_class_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE UOM_CLASS_OUT ADD CONSTRAINT chk_type_uom_class_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

ALTER TABLE UOM_CLASS_OUT ADD CONSTRAINT chk_actn_uom_class_out CHECK (bdi_dataset_action IN ('REPLACE', 'CREATE', 'UPDATE', 'DELETE'));