-- Interface Module: UomConversion_Fnd
-- Interface: Uom_Conversion_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE uom_conversion_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: UOM_CONVERSION_OUT
-- This table is used to integrate UOM Conversion information.

CREATE TABLE UOM_CONVERSION_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT uom_conversion_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 from which the conversion is to occur.
  from_uom VARCHAR2(4) NOT NULL,
  -- Contains a string that uniquely identifies the unit of measure to which the conversion is to occur.
  to_uom VARCHAR2(4) NOT NULL,
  -- Contains a number that is the conversion factor from the from_UOM to the to_UOM.
  factor NUMBER(20,10) NOT NULL,
  -- Contains a character that identifies the operation required to convert  from the from_UOM to the to_UOM. Valid Values are: M = Multiply and D= Divide.
  operator VARCHAR2(1) NOT NULL
);

COMMENT ON TABLE UOM_CONVERSION_OUT IS 'This table is used to integrate UOM Conversion information.';
COMMENT ON COLUMN UOM_CONVERSION_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN UOM_CONVERSION_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN UOM_CONVERSION_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN UOM_CONVERSION_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN UOM_CONVERSION_OUT.from_uom IS 'Contains a string that uniquely identifies the unit of measure from which the conversion is to occur.';
COMMENT ON COLUMN UOM_CONVERSION_OUT.to_uom IS 'Contains a string that uniquely identifies the unit of measure to which the conversion is to occur.';
COMMENT ON COLUMN UOM_CONVERSION_OUT.factor IS 'Contains a number that is the conversion factor from the from_UOM to the to_UOM.';
COMMENT ON COLUMN UOM_CONVERSION_OUT.operator IS 'Contains a character that identifies the operation required to convert  from the from_UOM to the to_UOM. Valid Values are: M = Multiply and D= Divide.';


-- Add BDI primary key constraint

ALTER TABLE UOM_CONVERSION_OUT ADD CONSTRAINT pk_uom_conversion_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE UOM_CONVERSION_OUT ADD CONSTRAINT chk_type_uom_conversion_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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