-- Interface Module: UdaItemFF_Fnd
-- Interface: Uda_Item_Ff_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE uda_item_ff_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: UDA_ITEM_FF_OUT
-- This table is used to integrate Uda Item Free Form information.

CREATE TABLE UDA_ITEM_FF_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT uda_item_ff_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 unique alphanumeric identifier for the item.
  item VARCHAR2(25) NOT NULL,
  -- This field contains a number uniquely identifying the User-Defined Attribute.
  uda_id NUMBER(5,0) NOT NULL,
  -- This field contains the text value of the Used Defined attribute for the item.
  uda_text VARCHAR2(250) NOT NULL,
  -- This field is is a virtual column and will have a value same UDA_TEXT.
  uda_text_desc VARCHAR2(250)
);

COMMENT ON TABLE UDA_ITEM_FF_OUT IS 'This table is used to integrate Uda Item Free Form information.';
COMMENT ON COLUMN UDA_ITEM_FF_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN UDA_ITEM_FF_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN UDA_ITEM_FF_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN UDA_ITEM_FF_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN UDA_ITEM_FF_OUT.item IS 'This field contains unique alphanumeric identifier for the item.';
COMMENT ON COLUMN UDA_ITEM_FF_OUT.uda_id IS 'This field contains a number uniquely identifying the User-Defined Attribute.';
COMMENT ON COLUMN UDA_ITEM_FF_OUT.uda_text IS 'This field contains the text value of the Used Defined attribute for the item.';
COMMENT ON COLUMN UDA_ITEM_FF_OUT.uda_text_desc IS 'This field is is a virtual column and will have a value same UDA_TEXT.';


-- Add BDI primary key constraint

ALTER TABLE UDA_ITEM_FF_OUT ADD CONSTRAINT pk_uda_item_ff_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE UDA_ITEM_FF_OUT ADD CONSTRAINT chk_type_uda_item_ff_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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