-- Interface Module: PckitemBrkout_Fnd
-- Interface: Packitem_Breakout_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE packitem_breakout_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: PACKITEM_BREAKOUT_OUT
-- This table is used to integrate pack item breakout information.

CREATE TABLE PACKITEM_BREAKOUT_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT packitem_breakout_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 alphanumeric value that uniquely identifies the pack item.
  pack_no VARCHAR2(25) NOT NULL,
  -- Contains the unique identifier of the component item.
  item VARCHAR2(25) NOT NULL,
  -- Contains the quantity of each item in the pack item.
  pack_qty NUMBER(12,4) NOT NULL
);

COMMENT ON TABLE PACKITEM_BREAKOUT_OUT IS 'This table is used to integrate pack item breakout information.';
COMMENT ON COLUMN PACKITEM_BREAKOUT_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN PACKITEM_BREAKOUT_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN PACKITEM_BREAKOUT_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN PACKITEM_BREAKOUT_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN PACKITEM_BREAKOUT_OUT.pack_no IS 'Contains the alphanumeric value that uniquely identifies the pack item.';
COMMENT ON COLUMN PACKITEM_BREAKOUT_OUT.item IS 'Contains the unique identifier of the component item.';
COMMENT ON COLUMN PACKITEM_BREAKOUT_OUT.pack_qty IS 'Contains the quantity of each item in the pack item.';


-- Add BDI primary key constraint

ALTER TABLE PACKITEM_BREAKOUT_OUT ADD CONSTRAINT pk_packitem_breakout_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE PACKITEM_BREAKOUT_OUT ADD CONSTRAINT chk_type_packitem_breakout_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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