-- Interface Module: ItemHdrTl_Fnd
-- Interface: Item_Hdr_Tl_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE item_hdr_tl_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: ITEM_HDR_TL_OUT
-- This table is used to integrate the translations of the item description field.

CREATE TABLE ITEM_HDR_TL_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT item_hdr_tl_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 will hold the language in which the translated text is maintained.
  lang NUMBER(6,0) NOT NULL,
  -- This field will hold the description or name for the language.
  description VARCHAR2(120) NOT NULL,
  -- This field will hold the ISO code associated with the given language.
  iso_code VARCHAR2(6) NOT NULL,
  -- This field will hold the item id.
  item VARCHAR2(25) NOT NULL,
  -- This field will hold the translated text of the primary description of the item.
  item_desc VARCHAR2(250) NOT NULL,
  -- This field will hold the translated text of the secondary description of the item.
  item_desc_secondary VARCHAR2(250),
  -- This field will hold the translated text of the shortened description of the item.
  short_desc VARCHAR2(120) NOT NULL
);

COMMENT ON TABLE ITEM_HDR_TL_OUT IS 'This table is used to integrate the translations of the item description field.';
COMMENT ON COLUMN ITEM_HDR_TL_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN ITEM_HDR_TL_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN ITEM_HDR_TL_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN ITEM_HDR_TL_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN ITEM_HDR_TL_OUT.lang IS 'This field will hold the language in which the translated text is maintained.';
COMMENT ON COLUMN ITEM_HDR_TL_OUT.description IS 'This field will hold the description or name for the language.';
COMMENT ON COLUMN ITEM_HDR_TL_OUT.iso_code IS 'This field will hold the ISO code associated with the given language.';
COMMENT ON COLUMN ITEM_HDR_TL_OUT.item IS 'This field will hold the item id.';
COMMENT ON COLUMN ITEM_HDR_TL_OUT.item_desc IS 'This field will hold the translated text of the primary description of the item.';
COMMENT ON COLUMN ITEM_HDR_TL_OUT.item_desc_secondary IS 'This field will hold the translated text of the secondary description of the item.';
COMMENT ON COLUMN ITEM_HDR_TL_OUT.short_desc IS 'This field will hold the translated text of the shortened description of the item.';


-- Add BDI primary key constraint

ALTER TABLE ITEM_HDR_TL_OUT ADD CONSTRAINT pk_item_hdr_tl_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE ITEM_HDR_TL_OUT ADD CONSTRAINT chk_type_item_hdr_tl_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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