-- Interface Module: PromotionOffer_Tx
-- Interface: Promo_Offer_In

-- Create BDI Inbound Table: PROMO_OFFER_IN
-- This table holds the payload information for promotion offers.


CREATE TABLE PROMO_OFFER_IN (
  -- bdi internal column
  bdi_seq_id NUMBER NOT NULL,
  -- bdi internal column
  bdi_app_name VARCHAR2(50) NOT NULL,
  -- bdi internal column
  bdi_dataset_type VARCHAR2(20),
  -- bdi internal column
  bdi_dataset_action VARCHAR2(20),
  -- The payload ID of the promotion offer.
  promo_offer_id NUMBER(10) NOT NULL,
  -- The message payload ID.
  payload_id NUMBER(10) NOT NULL,
  -- The promo ID.
  promo_id NUMBER(10) NOT NULL,
  -- The offer ID.
  offer_id NUMBER(10) NOT NULL,
  -- Offer description.
  offer_desc VARCHAR2(1000),
  -- The customer description of the offer.
  offer_cust_desc VARCHAR2(1000),
  -- The level of the offer.  Valid values are: 0 - Item, 1 - Transaction.
  level_code NUMBER(2),
  -- The type of the offer.  Valid values are: 0 Item Simple, 1 Transaction Simple, 2 - Transaction Buy Get, 3 - Item Buy Get, 4 - Item Gift With Purchae
  type_code NUMBER(2),
  -- The template of the offer.  Valid values are: 0 - Get Discount, 1 - Buy X, Get Discount, 2 - Spend X, Get Discount, 3 - Get Y for Discount, 4 - Buy X, Get Discount, 5 - Spend X, Get Discount, 6 - Buy X, Get Y for Discount, 7 - Spend X, Get Y for Discount, 8 - Buy X and Y, Get Discount, 9 - Buy X and Y, Get Cheapest Free, 10 - Buy X and Y, Get Z for Discount, 11 - Buy X, Get Gift with Purchase, 12 - Spend Y, Get Gift with Purchase
  template_id NUMBER(2),
  -- The start date and time of the offer.
  start_date TIMESTAMP,
  -- The end date and time of the offer.
  end_date TIMESTAMP,
  -- The comments for the offer.
  comments VARCHAR2(4000),
  -- The coupon code for the offer.
  coupon_code VARCHAR2(160),
  -- Indicates if the coupon code is required.
  coupon_code_req_ind NUMBER(1),
  -- The distribution rule code.  Valid Values are B - Buy Items; G - Get Items; BG - Both Buy and Get Items
  distrib_rule_code VARCHAR2(6),
  -- Indicates whether or not this offer is exclusive of other discounts.  Valid Values: 0 - not exclusive; 1 exclusive
  exclusive_discount_ind NUMBER(1)
);

COMMENT ON TABLE PROMO_OFFER_IN IS 'This table holds the payload information for promotion offers.';
COMMENT ON COLUMN PROMO_OFFER_IN.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN PROMO_OFFER_IN.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN PROMO_OFFER_IN.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN PROMO_OFFER_IN.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN PROMO_OFFER_IN.promo_offer_id IS 'The payload ID of the promotion offer.';
COMMENT ON COLUMN PROMO_OFFER_IN.payload_id IS 'The message payload ID.';
COMMENT ON COLUMN PROMO_OFFER_IN.promo_id IS 'The promo ID.';
COMMENT ON COLUMN PROMO_OFFER_IN.offer_id IS 'The offer ID.';
COMMENT ON COLUMN PROMO_OFFER_IN.offer_desc IS 'Offer description.';
COMMENT ON COLUMN PROMO_OFFER_IN.offer_cust_desc IS 'The customer description of the offer.';
COMMENT ON COLUMN PROMO_OFFER_IN.level_code IS 'The level of the offer.  Valid values are: 0 - Item, 1 - Transaction.';
COMMENT ON COLUMN PROMO_OFFER_IN.type_code IS 'The type of the offer.  Valid values are: 0 Item Simple, 1 Transaction Simple, 2 - Transaction Buy Get, 3 - Item Buy Get, 4 - Item Gift With Purchae';
COMMENT ON COLUMN PROMO_OFFER_IN.template_id IS 'The template of the offer.  Valid values are: 0 - Get Discount, 1 - Buy X, Get Discount, 2 - Spend X, Get Discount, 3 - Get Y for Discount, 4 - Buy X, Get Discount, 5 - Spend X, Get Discount, 6 - Buy X, Get Y for Discount, 7 - Spend X, Get Y for Discount, 8 - Buy X and Y, Get Discount, 9 - Buy X and Y, Get Cheapest Free, 10 - Buy X and Y, Get Z for Discount, 11 - Buy X, Get Gift with Purchase, 12 - Spend Y, Get Gift with Purchase';
COMMENT ON COLUMN PROMO_OFFER_IN.start_date IS 'The start date and time of the offer.';
COMMENT ON COLUMN PROMO_OFFER_IN.end_date IS 'The end date and time of the offer.';
COMMENT ON COLUMN PROMO_OFFER_IN.comments IS 'The comments for the offer.';
COMMENT ON COLUMN PROMO_OFFER_IN.coupon_code IS 'The coupon code for the offer.';
COMMENT ON COLUMN PROMO_OFFER_IN.coupon_code_req_ind IS 'Indicates if the coupon code is required.';
COMMENT ON COLUMN PROMO_OFFER_IN.distrib_rule_code IS 'The distribution rule code.  Valid Values are B - Buy Items; G - Get Items; BG - Both Buy and Get Items';
COMMENT ON COLUMN PROMO_OFFER_IN.exclusive_discount_ind IS 'Indicates whether or not this offer is exclusive of other discounts.  Valid Values: 0 - not exclusive; 1 exclusive';


-- Add BDI primary key constraint

ALTER TABLE PROMO_OFFER_IN ADD CONSTRAINT pk_promo_offer_in PRIMARY KEY (bdi_app_name, bdi_seq_id);