-- Interface Module: DiffGrp_Fnd
-- Interface: Diff_Grp_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE diff_grp_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: DIFF_GRP_OUT
-- This table is used to integrate diff group header information.  Diff groups are sets of related diffs (e.g. Fall 2017 Sweater Pallete).

CREATE TABLE DIFF_GRP_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT diff_grp_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 a unique number id for the differentiator group.  Data will always be present in this field.
  diff_group_id VARCHAR2(10) NOT NULL,
  -- Description of the differentiator group (for example: Mens Shirt Sizes, Womens Shoe Sizes, Girls Dress Sizes, Shower Gel Scents, Yogurt Flavors, etc.).  Description data is only sent in the primary integration language of the system.
  diff_group_desc VARCHAR2(120) NOT NULL,
  -- This field will hold a value of the types of differentiators contained in this differentiator group, including but not limited to: S (size), C (color), F (flavor), E (scent), P (pattern).
  diff_type_id VARCHAR2(6) NOT NULL,
  -- Contains the description of the differentiator type.  For Example:  Size, Color, Flavor, Scent, Pattern.  Description data is only sent in the primary integration language of the system.
  diff_type_desc VARCHAR2(120) NOT NULL
);

COMMENT ON TABLE DIFF_GRP_OUT IS 'This table is used to integrate diff group header information.  Diff groups are sets of related diffs (e.g. Fall 2017 Sweater Pallete).';
COMMENT ON COLUMN DIFF_GRP_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN DIFF_GRP_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN DIFF_GRP_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN DIFF_GRP_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN DIFF_GRP_OUT.diff_group_id IS 'This field will hold a unique number id for the differentiator group.  Data will always be present in this field.';
COMMENT ON COLUMN DIFF_GRP_OUT.diff_group_desc IS 'Description of the differentiator group (for example: Mens Shirt Sizes, Womens Shoe Sizes, Girls Dress Sizes, Shower Gel Scents, Yogurt Flavors, etc.).  Description data is only sent in the primary integration language of the system.';
COMMENT ON COLUMN DIFF_GRP_OUT.diff_type_id IS 'This field will hold a value of the types of differentiators contained in this differentiator group, including but not limited to: S (size), C (color), F (flavor), E (scent), P (pattern).';
COMMENT ON COLUMN DIFF_GRP_OUT.diff_type_desc IS 'Contains the description of the differentiator type.  For Example:  Size, Color, Flavor, Scent, Pattern.  Description data is only sent in the primary integration language of the system.';


-- Add BDI primary key constraint

ALTER TABLE DIFF_GRP_OUT ADD CONSTRAINT pk_diff_grp_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE DIFF_GRP_OUT ADD CONSTRAINT chk_type_diff_grp_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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