-- Interface Module: ItemImage_Fnd
-- Interface: Item_Image_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE item_image_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: ITEM_IMAGE_OUT
-- This table is used to integrate Item Image information.

CREATE TABLE ITEM_IMAGE_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT item_image_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 the unique alphanumeric identifier for the item, the image is for.
  item VARCHAR2(25) NOT NULL,
  -- This field contains the name of the image of the item.
  image_name VARCHAR2(120) NOT NULL,
  -- This field contains the actual path where the file of the image of the item is stored.
  image_addr VARCHAR2(255) NOT NULL,
  -- This field contains the description associated with the image of the item.
  image_desc VARCHAR2(40) NOT NULL,
  -- This field contains the type of the image of the item.  Valid values are defined as member of IITD code type.
  image_type VARCHAR2(6) NOT NULL,
  -- This field will indicate whether this record is the primary image of the item or not.  Valid values are Y(es) and N(o) only.  Default to N value if left blank or set as NULL.
  primary_ind VARCHAR2(1) NOT NULL,
  -- This field will specify the display sequence order of images associated to the item per priority.
  display_priority NUMBER(4,0) NOT NULL
);

COMMENT ON TABLE ITEM_IMAGE_OUT IS 'This table is used to integrate Item Image information.';
COMMENT ON COLUMN ITEM_IMAGE_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN ITEM_IMAGE_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN ITEM_IMAGE_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN ITEM_IMAGE_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN ITEM_IMAGE_OUT.item IS 'This field contains the unique alphanumeric identifier for the item, the image is for.';
COMMENT ON COLUMN ITEM_IMAGE_OUT.image_name IS 'This field contains the name of the image of the item.';
COMMENT ON COLUMN ITEM_IMAGE_OUT.image_addr IS 'This field contains the actual path where the file of the image of the item is stored.';
COMMENT ON COLUMN ITEM_IMAGE_OUT.image_desc IS 'This field contains the description associated with the image of the item.';
COMMENT ON COLUMN ITEM_IMAGE_OUT.image_type IS 'This field contains the type of the image of the item.  Valid values are defined as member of IITD code type.';
COMMENT ON COLUMN ITEM_IMAGE_OUT.primary_ind IS 'This field will indicate whether this record is the primary image of the item or not.  Valid values are Y(es) and N(o) only.  Default to N value if left blank or set as NULL.';
COMMENT ON COLUMN ITEM_IMAGE_OUT.display_priority IS 'This field will specify the display sequence order of images associated to the item per priority.';


-- Add BDI primary key constraint

ALTER TABLE ITEM_IMAGE_OUT ADD CONSTRAINT pk_item_image_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE ITEM_IMAGE_OUT ADD CONSTRAINT chk_type_item_image_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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