-- Interface Module: Uda_Fnd
-- Interface: Uda_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE uda_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: UDA_OUT
-- This table is used to integrate UDA information.

CREATE TABLE UDA_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT uda_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',
  -- This field contains a unique number identifying the User Defined Attribute.
  uda_id NUMBER(5,0) NOT NULL,
  -- This field contains a description of the User-Defined Attribute.
  uda_desc VARCHAR2(120) NOT NULL,
  -- This field identifies which module the UDA belongs to.  Valid value is:  ITEM
  module VARCHAR2(20) NOT NULL,
  -- This field is used to store the display type (how the UDA values will be displayed to the user) for the given UDA. The valid values are DT-Date, FF - Free From, LV - List of values
  display_type VARCHAR2(2) NOT NULL,
  -- This field contains the data type of any valid values for the UDA.  This is an optional field used only if specific processing is needed to validate the data type of the UDA values.  Valid values are: Number, Alphanumeric and Date
  data_type VARCHAR2(12),
  -- This field contains the data length of any valid values for the UDA.  This is an optional field used only if specific processing is needed to validate the data length of the UDA values.
  data_length NUMBER(3,0),
  -- This field indicates whether or not the UDA should be constrained to having at most one value.
  single_value_ind VARCHAR2(1) NOT NULL,
  -- This field contains the ID of the Organizational Hierarchy that the UDA is assigned to. This field will be used to control the UDAs a user can see in the UDA LOV when the link between the user and the Organizational Hierarchy has been established.  The level of the organizational hierarchy held in this field is based on the UDA_ORG_LEVEL_CODE on the SECURITY_CONFIG_OPTIONS table.
  filter_org_id NUMBER(10,0),
  -- This field contains the ID of the Merchandise Hierarchy that the UDA is assigned to. This field will be used to control the UDAs a user can see in the UDA LOV when the link between the user and the Merchandise Hierarchy has been established.
  filter_merch_id NUMBER(4,0),
  -- This field contains the class ID of the Merchandise Hierarchy that the UDA is assigned to.
  filter_merch_id_class NUMBER(4,0),
  -- This field contains the subclass ID of the Merchandise Hierarchy that the UDA is assigned to.
  filter_merch_id_subclass NUMBER(4,0)
);

COMMENT ON TABLE UDA_OUT IS 'This table is used to integrate UDA information.';
COMMENT ON COLUMN UDA_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN UDA_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN UDA_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN UDA_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN UDA_OUT.uda_id IS 'This field contains a unique number identifying the User Defined Attribute.';
COMMENT ON COLUMN UDA_OUT.uda_desc IS 'This field contains a description of the User-Defined Attribute.';
COMMENT ON COLUMN UDA_OUT.module IS 'This field identifies which module the UDA belongs to.  Valid value is:  ITEM';
COMMENT ON COLUMN UDA_OUT.display_type IS 'This field is used to store the display type (how the UDA values will be displayed to the user) for the given UDA. The valid values are DT-Date, FF - Free From, LV - List of values';
COMMENT ON COLUMN UDA_OUT.data_type IS 'This field contains the data type of any valid values for the UDA.  This is an optional field used only if specific processing is needed to validate the data type of the UDA values.  Valid values are: Number, Alphanumeric and Date';
COMMENT ON COLUMN UDA_OUT.data_length IS 'This field contains the data length of any valid values for the UDA.  This is an optional field used only if specific processing is needed to validate the data length of the UDA values.';
COMMENT ON COLUMN UDA_OUT.single_value_ind IS 'This field indicates whether or not the UDA should be constrained to having at most one value.';
COMMENT ON COLUMN UDA_OUT.filter_org_id IS 'This field contains the ID of the Organizational Hierarchy that the UDA is assigned to. This field will be used to control the UDAs a user can see in the UDA LOV when the link between the user and the Organizational Hierarchy has been established.  The level of the organizational hierarchy held in this field is based on the UDA_ORG_LEVEL_CODE on the SECURITY_CONFIG_OPTIONS table.';
COMMENT ON COLUMN UDA_OUT.filter_merch_id IS 'This field contains the ID of the Merchandise Hierarchy that the UDA is assigned to. This field will be used to control the UDAs a user can see in the UDA LOV when the link between the user and the Merchandise Hierarchy has been established.';
COMMENT ON COLUMN UDA_OUT.filter_merch_id_class IS 'This field contains the class ID of the Merchandise Hierarchy that the UDA is assigned to.';
COMMENT ON COLUMN UDA_OUT.filter_merch_id_subclass IS 'This field contains the subclass ID of the Merchandise Hierarchy that the UDA is assigned to.';


-- Add BDI primary key constraint

ALTER TABLE UDA_OUT ADD CONSTRAINT pk_uda_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE UDA_OUT ADD CONSTRAINT chk_type_uda_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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