-- Interface Module: OrgHier_Fnd
-- Interface: Org_Hier_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE org_hier_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: ORG_HIER_OUT
-- This table is used to integrate org hierarchy. 

CREATE TABLE ORG_HIER_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT org_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 organizational hierarchy that is described by this record.Value is always COMPANY, CHAIN, AREA, REGION, DISTRICT, STORE or WAREHOUSE. This field can not be null.
  hierarchy_level VARCHAR2(10) NOT NULL,
  -- This information identifies the the node of the organizational 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 DISTRICT 1 and a REGION 1 in the full organizational hierarchy).
  hierarchy_node_id NUMBER(10,0) NOT NULL,
  -- Name of the organizational hierarchy entity. This field can not be null.  Description data is only sent in the primary integration language of the system.
  hierarchy_node_name VARCHAR2(150) NOT NULL,
  -- This information identifies the parent level of the organizational hierarchy that is described by this record
  parent_level VARCHAR2(10),
  -- This information identifies the id of parent organizational hierarchy.
  parent_id NUMBER(10,0),
  -- Description Not Available
  mgr_name VARCHAR2(120),
  -- Description Not Available
  currency_code VARCHAR2(3)
);

COMMENT ON TABLE ORG_HIER_OUT IS 'This table is used to integrate org hierarchy. ';
COMMENT ON COLUMN ORG_HIER_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN ORG_HIER_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN ORG_HIER_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN ORG_HIER_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN ORG_HIER_OUT.hierarchy_level IS 'This information identifies the level of the organizational hierarchy that is described by this record.Value is always COMPANY, CHAIN, AREA, REGION, DISTRICT, STORE or WAREHOUSE. This field can not be null.';
COMMENT ON COLUMN ORG_HIER_OUT.hierarchy_node_id IS 'This information identifies the the node of the organizational 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 DISTRICT 1 and a REGION 1 in the full organizational hierarchy).';
COMMENT ON COLUMN ORG_HIER_OUT.hierarchy_node_name IS 'Name of the organizational hierarchy entity. This field can not be null.  Description data is only sent in the primary integration language of the system.';
COMMENT ON COLUMN ORG_HIER_OUT.parent_level IS 'This information identifies the parent level of the organizational hierarchy that is described by this record';
COMMENT ON COLUMN ORG_HIER_OUT.parent_id IS 'This information identifies the id of parent organizational hierarchy.';
COMMENT ON COLUMN ORG_HIER_OUT.mgr_name IS '';
COMMENT ON COLUMN ORG_HIER_OUT.currency_code IS '';


-- Add BDI primary key constraint

ALTER TABLE ORG_HIER_OUT ADD CONSTRAINT pk_org_hier_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE ORG_HIER_OUT ADD CONSTRAINT chk_type_org_hier_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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