-- Interface Module: MerchHier_Fnd
-- Interface: Merch_Hier_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE merch_hier_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: MERCH_HIER_OUT
-- This table is used to integrate merchandise hierarchy information. 

CREATE TABLE MERCH_HIER_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT merch_hier_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 information identifies the level of the merchandise hierarchy that is described by this record.  Value is always DIVISION, GROUP, DEPT, CLASS, SUBCLASS. This field can not be null.
  hierarchy_level VARCHAR2(10) NOT NULL,
  -- This information identifies the the node of the merchanidise hierarchy that is described by this record. This field can not be null.  HierarchyNodeId is only unique within an HierarchyLevel (meaning it is possible, for example, that there is both a DIVISION 1 and a GROUP 1 in the full merchandise hierarchy).
  hierarchy_node_id NUMBER(10,0) NOT NULL,
  -- Name of the merchandise hierarchy entity.  Description data is only sent in the primary integration language of the system.
  hierarchy_node_name VARCHAR2(150) NOT NULL,
  -- Level of the merchandise hierarchy above the current node. Both ParentLevel and ParentId are should be evaluated to correctly traverse the hierarchy
  parent_level VARCHAR2(10),
  -- Id of the level of the merchandise hierarchy above the current node. Both ParentLevel and ParentNodeId are should be evaluated to correctly traverse the hierarchy.
  parent_node_id NUMBER(10,0),
  -- Only populated for SUBCLASS entities.  For subclasses, this column will hold the department ID used for display purposes in RMS (department is the grandparent of subclass).  Note that in RMS, dept, class and subclass display IDs are combined to form a composite unique key.  Every department can have a class 1.  Every class in Department 1000 can have a subclass 1.  Looking only at the display ids, all three values are required for uniqueness.  Node that for subclasses, the HierarchyNodeId is unique.  It is a non-displayed, unique value that emilinates the need for a composite key.
  grandparent_merch_display_id NUMBER(4,0),
  -- Only populated for CLASS and SUBCLASS entities.  For classes, this column holds the department ID used for display purposes in RMS (department is the parent of class)  For subclasses, this column holds the class ID used for display.  Note that in RMS, dept, class and subclass display IDs are combined to form a composite unique key.  Every department can have a class 1.  Every class in Department 1000 can have a subclass 1.  Looking only at the display ids, all three values are required for uniqueness.  Node that for subclasses and classes, the HierarchyNodeId is unique.  It is a non-displayed, unique value that emilinates the need for a composite key.
  parent_merch_display_id NUMBER(4,0),
  -- Holds the merchandise hierarchy id for division, group, department, class, and subclass.  For departments, this column holds the department display id.  For subclasses, this column holds the subclass display id.  Note that in RMS, dept, class and subclass display IDs are combined to form a composite unique key.  Every department can have a class 1.  Every class in Department 1000 can have a subclass 1.  Looking only at the display ids, all three values are required for uniqueness.
  merch_display_id NUMBER(4,0) NOT NULL,
  -- Contains a code which indicates whether items in this department are normal merchandise, consignment stock or concession items. Valid values are: 0 = Normal Merchandise, 1 = Consignment Stock, 2 = Concession Items.
  purchase_type NUMBER(1),
  -- Contains the number of the buyer associated with the division.
  buyer NUMBER(4),
  -- Contains the number of the merchandiser associated with the division.
  merch NUMBER(4),
  -- Contains a number which indicates whether profit will be calculated by Direct Cost or by Retail Inventory.  Valid values are:1 = Direct Cost, 2 = Retail Inventory.
  profit_calc_type NUMBER(1)
);

COMMENT ON TABLE MERCH_HIER_OUT IS 'This table is used to integrate merchandise hierarchy information. ';
COMMENT ON COLUMN MERCH_HIER_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN MERCH_HIER_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN MERCH_HIER_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN MERCH_HIER_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN MERCH_HIER_OUT.hierarchy_level IS 'This information identifies the level of the merchandise hierarchy that is described by this record.  Value is always DIVISION, GROUP, DEPT, CLASS, SUBCLASS. This field can not be null.';
COMMENT ON COLUMN MERCH_HIER_OUT.hierarchy_node_id IS 'This information identifies the the node of the merchanidise hierarchy that is described by this record. This field can not be null.  HierarchyNodeId is only unique within an HierarchyLevel (meaning it is possible, for example, that there is both a DIVISION 1 and a GROUP 1 in the full merchandise hierarchy).';
COMMENT ON COLUMN MERCH_HIER_OUT.hierarchy_node_name IS 'Name of the merchandise hierarchy entity.  Description data is only sent in the primary integration language of the system.';
COMMENT ON COLUMN MERCH_HIER_OUT.parent_level IS 'Level of the merchandise hierarchy above the current node. Both ParentLevel and ParentId are should be evaluated to correctly traverse the hierarchy';
COMMENT ON COLUMN MERCH_HIER_OUT.parent_node_id IS 'Id of the level of the merchandise hierarchy above the current node. Both ParentLevel and ParentNodeId are should be evaluated to correctly traverse the hierarchy.';
COMMENT ON COLUMN MERCH_HIER_OUT.grandparent_merch_display_id IS 'Only populated for SUBCLASS entities.  For subclasses, this column will hold the department ID used for display purposes in RMS (department is the grandparent of subclass).  Note that in RMS, dept, class and subclass display IDs are combined to form a composite unique key.  Every department can have a class 1.  Every class in Department 1000 can have a subclass 1.  Looking only at the display ids, all three values are required for uniqueness.  Node that for subclasses, the HierarchyNodeId is unique.  It is a non-displayed, unique value that emilinates the need for a composite key.';
COMMENT ON COLUMN MERCH_HIER_OUT.parent_merch_display_id IS 'Only populated for CLASS and SUBCLASS entities.  For classes, this column holds the department ID used for display purposes in RMS (department is the parent of class)  For subclasses, this column holds the class ID used for display.  Note that in RMS, dept, class and subclass display IDs are combined to form a composite unique key.  Every department can have a class 1.  Every class in Department 1000 can have a subclass 1.  Looking only at the display ids, all three values are required for uniqueness.  Node that for subclasses and classes, the HierarchyNodeId is unique.  It is a non-displayed, unique value that emilinates the need for a composite key.';
COMMENT ON COLUMN MERCH_HIER_OUT.merch_display_id IS 'Holds the merchandise hierarchy id for division, group, department, class, and subclass.  For departments, this column holds the department display id.  For subclasses, this column holds the subclass display id.  Note that in RMS, dept, class and subclass display IDs are combined to form a composite unique key.  Every department can have a class 1.  Every class in Department 1000 can have a subclass 1.  Looking only at the display ids, all three values are required for uniqueness.';
COMMENT ON COLUMN MERCH_HIER_OUT.purchase_type IS 'Contains a code which indicates whether items in this department are normal merchandise, consignment stock or concession items. Valid values are: 0 = Normal Merchandise, 1 = Consignment Stock, 2 = Concession Items.';
COMMENT ON COLUMN MERCH_HIER_OUT.buyer IS 'Contains the number of the buyer associated with the division.';
COMMENT ON COLUMN MERCH_HIER_OUT.merch IS 'Contains the number of the merchandiser associated with the division.';
COMMENT ON COLUMN MERCH_HIER_OUT.profit_calc_type IS 'Contains a number which indicates whether profit will be calculated by Direct Cost or by Retail Inventory.  Valid values are:1 = Direct Cost, 2 = Retail Inventory.';


-- Add BDI primary key constraint

ALTER TABLE MERCH_HIER_OUT ADD CONSTRAINT pk_merch_hier_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE MERCH_HIER_OUT ADD CONSTRAINT chk_type_merch_hier_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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