-- Interface Module: ItemTicket_Fnd
-- Interface: Item_Ticket_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE item_ticket_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: ITEM_TICKET_OUT
-- This table is used to integrate Item Ticket information.

CREATE TABLE ITEM_TICKET_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT item_ticket_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 contains the unique alphanumeric value that identifies the item which is associated with the ticket type.
  item VARCHAR2(25) NOT NULL,
  -- This field contains a character string which uniquely identifies the ticket or label type.
  ticket_type_id VARCHAR2(4) NOT NULL,
  -- This field contains a description of the ticket or label type.
  ticket_type_desc VARCHAR2(120) NOT NULL,
  -- This field indicates if the ticket type is a shelf edge label.
  sel_ind VARCHAR2(1) NOT NULL,
  -- This field contains a character string which uniquely identifies an attribute which will appear on a ticket or label such as retail price or price per unit of measure. Valid values are defined as member of TCKT code type.
  ticket_item_id VARCHAR2(4),
  -- This field contains the description of the ticket item id.
  ticket_item_id_desc VARCHAR2(250),
  -- This field contains a number which uniquely identifies a user-defined attribute which is to be printed on this ticket type.
  uda_id NUMBER(5,0),
  -- This field indicates when the ticket type for the given item should be printed by default, upon the approval or receipt of the purchase order. Valid values are: A and R.
  po_print_type VARCHAR2(1),
  -- This field indicates whether or not this type of ticket should be printed for this item when a permanent price change goes into effect. Valid values: Y - Yes and N - No.
  print_on_pc_ind VARCHAR2(1) NOT NULL,
  -- This field contains a percent which indicates the amount of tickets which should be printed for a given event. If the event causing tickets to be printed is approving or receiving a purchase order, then this field indicates the quantity of tickets which should be printed greater than the quantity of the purchase order. If the event causing the tickets to be printed is a permanent price change, this field would indicate the quantity of tickets which should be printed greater than the stock on hand.
  ticket_over_pct NUMBER(12,4)
);

COMMENT ON TABLE ITEM_TICKET_OUT IS 'This table is used to integrate Item Ticket information.';
COMMENT ON COLUMN ITEM_TICKET_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN ITEM_TICKET_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN ITEM_TICKET_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN ITEM_TICKET_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN ITEM_TICKET_OUT.item IS 'This field contains the unique alphanumeric value that identifies the item which is associated with the ticket type.';
COMMENT ON COLUMN ITEM_TICKET_OUT.ticket_type_id IS 'This field contains a character string which uniquely identifies the ticket or label type.';
COMMENT ON COLUMN ITEM_TICKET_OUT.ticket_type_desc IS 'This field contains a description of the ticket or label type.';
COMMENT ON COLUMN ITEM_TICKET_OUT.sel_ind IS 'This field indicates if the ticket type is a shelf edge label.';
COMMENT ON COLUMN ITEM_TICKET_OUT.ticket_item_id IS 'This field contains a character string which uniquely identifies an attribute which will appear on a ticket or label such as retail price or price per unit of measure. Valid values are defined as member of TCKT code type.';
COMMENT ON COLUMN ITEM_TICKET_OUT.ticket_item_id_desc IS 'This field contains the description of the ticket item id.';
COMMENT ON COLUMN ITEM_TICKET_OUT.uda_id IS 'This field contains a number which uniquely identifies a user-defined attribute which is to be printed on this ticket type.';
COMMENT ON COLUMN ITEM_TICKET_OUT.po_print_type IS 'This field indicates when the ticket type for the given item should be printed by default, upon the approval or receipt of the purchase order. Valid values are: A and R.';
COMMENT ON COLUMN ITEM_TICKET_OUT.print_on_pc_ind IS 'This field indicates whether or not this type of ticket should be printed for this item when a permanent price change goes into effect. Valid values: Y - Yes and N - No.';
COMMENT ON COLUMN ITEM_TICKET_OUT.ticket_over_pct IS 'This field contains a percent which indicates the amount of tickets which should be printed for a given event. If the event causing tickets to be printed is approving or receiving a purchase order, then this field indicates the quantity of tickets which should be printed greater than the quantity of the purchase order. If the event causing the tickets to be printed is a permanent price change, this field would indicate the quantity of tickets which should be printed greater than the stock on hand.';


-- Add BDI primary key constraint

ALTER TABLE ITEM_TICKET_OUT ADD CONSTRAINT pk_item_ticket_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE ITEM_TICKET_OUT ADD CONSTRAINT chk_type_item_ticket_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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