-- Interface Module: Diff_Fnd
-- Interface: Diff_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE diff_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: DIFF_OUT
-- This table is used to integrate differentiators (diffs).  Diffs are special purpose attributes used to define concepts that differentiate items that are closely related, but not the same.  Diffs are used to define colors, sizes, patterns, scents and flavors.

CREATE TABLE DIFF_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT diff_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',
  -- Contains the unique ID of the diff.  Data will always be present in this field.
  diff_id VARCHAR2(10) NOT NULL,
  -- Contains the type code for the diff.  All diffs belong to one any only one type.
  diff_desc VARCHAR2(120) NOT NULL,
  -- This field will hold a value of the types of differentiators contained in this differentiator group, such as S - size, C - color, F - flavor, E - scent, P - pattern. Valid values are stored in the DIFF_TYPE table.
  diff_type VARCHAR2(6) NOT NULL,
  -- Contains the description of the diff.  Data will always exist in this field.
  diff_type_desc VARCHAR2(120) NOT NULL,
  -- Optionally can hold the unique code used by industry standards to identify the differentiator. For example, in the US, the National Retail Federation defines a standard Color and Size Codes that gives retailers, vendors and manufacturers a common language for product color and size identification for EDI purposes. For example, mens pants size combination 32x32 has a NRF code number 10492.
  industry_code VARCHAR2(10),
  -- Optionally can hold a sub-grouping code used by industry standards to further identify the differentiator. For example, in the US, the National uses a subgroup for colors (e.g. purple is defined as 500; dark purple represents a range from 501 - 509, medium purple represents a range from 510 - 519, bright purple represents a range from 520 - 529, etc.).
  industry_subgroup VARCHAR2(10)
);

COMMENT ON TABLE DIFF_OUT IS 'This table is used to integrate differentiators (diffs).  Diffs are special purpose attributes used to define concepts that differentiate items that are closely related, but not the same.  Diffs are used to define colors, sizes, patterns, scents and flavors.';
COMMENT ON COLUMN DIFF_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN DIFF_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN DIFF_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN DIFF_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN DIFF_OUT.diff_id IS 'Contains the unique ID of the diff.  Data will always be present in this field.';
COMMENT ON COLUMN DIFF_OUT.diff_desc IS 'Contains the type code for the diff.  All diffs belong to one any only one type.';
COMMENT ON COLUMN DIFF_OUT.diff_type IS 'This field will hold a value of the types of differentiators contained in this differentiator group, such as S - size, C - color, F - flavor, E - scent, P - pattern. Valid values are stored in the DIFF_TYPE table.';
COMMENT ON COLUMN DIFF_OUT.diff_type_desc IS 'Contains the description of the diff.  Data will always exist in this field.';
COMMENT ON COLUMN DIFF_OUT.industry_code IS 'Optionally can hold the unique code used by industry standards to identify the differentiator. For example, in the US, the National Retail Federation defines a standard Color and Size Codes that gives retailers, vendors and manufacturers a common language for product color and size identification for EDI purposes. For example, mens pants size combination 32x32 has a NRF code number 10492.';
COMMENT ON COLUMN DIFF_OUT.industry_subgroup IS 'Optionally can hold a sub-grouping code used by industry standards to further identify the differentiator. For example, in the US, the National uses a subgroup for colors (e.g. purple is defined as 500; dark purple represents a range from 501 - 509, medium purple represents a range from 510 - 519, bright purple represents a range from 520 - 529, etc.).';


-- Add BDI primary key constraint

ALTER TABLE DIFF_OUT ADD CONSTRAINT pk_diff_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE DIFF_OUT ADD CONSTRAINT chk_type_diff_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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