-- Interface Module: CodeDetail_Fnd
-- Interface: Code_Detail_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE code_detail_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: CODE_DETAIL_OUT
-- This table is used to integrate Code Detail information.

CREATE TABLE CODE_DETAIL_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT code_detail_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 contain a valid code type for the row.  The valid code types are defined on the CODE_HEAD table.
  code_type VARCHAR2(4) NOT NULL,
  -- This field contains the code used in Oracle Retail which must be decoded for display in the on-line forms.
  code VARCHAR2(6) NOT NULL,
  -- This field contains the description associated with the code and code type.
  code_desc VARCHAR2(250) NOT NULL,
  -- This field indicates whether or not the code is required.  If the code is specified as required, then the user will not be able to delete the rows in the table for that code.
  required_ind VARCHAR2(1) NOT NULL,
  -- This is a number used to order the elements so that they appear consistently when using them to populate a list.
  code_seq NUMBER(4,0) NOT NULL
);

COMMENT ON TABLE CODE_DETAIL_OUT IS 'This table is used to integrate Code Detail information.';
COMMENT ON COLUMN CODE_DETAIL_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN CODE_DETAIL_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN CODE_DETAIL_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN CODE_DETAIL_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN CODE_DETAIL_OUT.code_type IS 'This field will contain a valid code type for the row.  The valid code types are defined on the CODE_HEAD table.';
COMMENT ON COLUMN CODE_DETAIL_OUT.code IS 'This field contains the code used in Oracle Retail which must be decoded for display in the on-line forms.';
COMMENT ON COLUMN CODE_DETAIL_OUT.code_desc IS 'This field contains the description associated with the code and code type.';
COMMENT ON COLUMN CODE_DETAIL_OUT.required_ind IS 'This field indicates whether or not the code is required.  If the code is specified as required, then the user will not be able to delete the rows in the table for that code.';
COMMENT ON COLUMN CODE_DETAIL_OUT.code_seq IS 'This is a number used to order the elements so that they appear consistently when using them to populate a list.';


-- Add BDI primary key constraint

ALTER TABLE CODE_DETAIL_OUT ADD CONSTRAINT pk_code_detail_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE CODE_DETAIL_OUT ADD CONSTRAINT chk_type_code_detail_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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