-- Interface Module: RelatedItem_Fnd
-- Interface: Related_Item_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE related_item_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: RELATED_ITEM_OUT
-- This table is used to integrate related item header information.  Related item header information includes the primary item and relationship type. 

CREATE TABLE RELATED_ITEM_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT related_item_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',
  -- Unique identifier for each relationship header.  Data will always exist in this field.
  relationship_id NUMBER(20,0) NOT NULL,
  -- Item for which the relationships are defined.  Data will always exist in this field.
  item VARCHAR2(25) NOT NULL,
  -- Description of the relationship.  Data will always exist in this field.  
  relationship_name VARCHAR2(255) NOT NULL,
  -- Describes the type of relationship.Valid values include: CRSL (Cross Sell), SUBS (Substitution), UPSL (Up-sell).
  relationship_type VARCHAR2(6) NOT NULL,
  -- This field indicates whether the relationship should be mandatory.  For example, an item like a laptop may have a mandatory cross sell relationship.  The related items could be different power cords for the US, UK, Mainland Europe, India, etc. When the laptop is sold, it should be mandatory that one of the related power cords also be selected.   Generally, only cross sell relationships are mandatory.  Substitution and upsell relationships can be defined as mandatory, but in those cases, the definition of mandatory is at the discretion of the client and generally means that substitution or upsell must, as business process, be offered to consumers.
  mandatory_ind VARCHAR2(1) NOT NULL
);

COMMENT ON TABLE RELATED_ITEM_OUT IS 'This table is used to integrate related item header information.  Related item header information includes the primary item and relationship type. ';
COMMENT ON COLUMN RELATED_ITEM_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN RELATED_ITEM_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN RELATED_ITEM_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN RELATED_ITEM_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN RELATED_ITEM_OUT.relationship_id IS 'Unique identifier for each relationship header.  Data will always exist in this field.';
COMMENT ON COLUMN RELATED_ITEM_OUT.item IS 'Item for which the relationships are defined.  Data will always exist in this field.';
COMMENT ON COLUMN RELATED_ITEM_OUT.relationship_name IS 'Description of the relationship.  Data will always exist in this field.  ';
COMMENT ON COLUMN RELATED_ITEM_OUT.relationship_type IS 'Describes the type of relationship.Valid values include: CRSL (Cross Sell), SUBS (Substitution), UPSL (Up-sell).';
COMMENT ON COLUMN RELATED_ITEM_OUT.mandatory_ind IS 'This field indicates whether the relationship should be mandatory.  For example, an item like a laptop may have a mandatory cross sell relationship.  The related items could be different power cords for the US, UK, Mainland Europe, India, etc. When the laptop is sold, it should be mandatory that one of the related power cords also be selected.   Generally, only cross sell relationships are mandatory.  Substitution and upsell relationships can be defined as mandatory, but in those cases, the definition of mandatory is at the discretion of the client and generally means that substitution or upsell must, as business process, be offered to consumers.';


-- Add BDI primary key constraint

ALTER TABLE RELATED_ITEM_OUT ADD CONSTRAINT pk_related_item_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE RELATED_ITEM_OUT ADD CONSTRAINT chk_type_related_item_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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