-- Interface Module: ExtPrice_Tx
-- Interface: Ext_Price_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE ext_price_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: EXT_PRICE_OUT
-- This table contains item prices coming from an external pricing application

CREATE TABLE EXT_PRICE_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT ext_price_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 identifies the unique alphanumeric value for the transaction level item.
  item_id VARCHAR2(25) NOT NULL,
  -- Contains the number that uniquely identifies the location.
  store_id NUMBER(10) NOT NULL,
  -- Contains the date on which the price change went effect.
  effective_date TIMESTAMP NOT NULL,
  -- Contains the date on which the price change event end.
  end_date TIMESTAMP,
  -- The item price type. Valid values: 200- Clearance, 201- Promotional, 202-Regular
  price_type NUMBER(3) NOT NULL,
  -- The promotion name.
  promotion_name VARCHAR2(160),
  -- Contains the current single unit retail in the selling unit of measure. 
  selling_unit_retail NUMBER(20,4),
  -- Contains the selling unit retail currency.
  selling_unit_retail_currency VARCHAR2(3),
  -- Contains the selling unit of measure for an items single-unit retail.
  selling_uom VARCHAR2(4),
  -- Contains the current multi-units.
  multi_units NUMBER(12,4),
  -- Contains the current multi-unit retail in the selling unit of measure. 
  multi_unit_retail NUMBER(20,4),
  -- Contains the multi-unit retail currency.
  multi_unit_retail_currency VARCHAR2(3),
  -- Contains the selling unit of measure for an items multi-unit retail.
  multi_unit_selling_uom VARCHAR2(4),
  -- This column holds the record creation date.
  create_datetime TIMESTAMP NOT NULL,
  -- The id of the record.
  rec_id NUMBER(15) NOT NULL,
  -- Indicates whether the retail changed with this price change.Valid values are: 0 - retail price not changed; 1 - retail price changed.
  retail_change_ind NUMBER(6),
  -- Indicates if the Price Change has impact to Multi Unit retail.  Valid value are AU - Multi Unit information is added or updated; R - Multi Unit information is removed; N - Multi unit information is not changed.
  multi_unit_impact VARCHAR2(4) NOT NULL,
  -- The id of the price event.
  price_event_id NUMBER(15)
);

COMMENT ON TABLE EXT_PRICE_OUT IS 'This table contains item prices coming from an external pricing application';
COMMENT ON COLUMN EXT_PRICE_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN EXT_PRICE_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN EXT_PRICE_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN EXT_PRICE_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN EXT_PRICE_OUT.item_id IS 'This field identifies the unique alphanumeric value for the transaction level item.';
COMMENT ON COLUMN EXT_PRICE_OUT.store_id IS 'Contains the number that uniquely identifies the location.';
COMMENT ON COLUMN EXT_PRICE_OUT.effective_date IS 'Contains the date on which the price change went effect.';
COMMENT ON COLUMN EXT_PRICE_OUT.end_date IS 'Contains the date on which the price change event end.';
COMMENT ON COLUMN EXT_PRICE_OUT.price_type IS 'The item price type. Valid values: 200- Clearance, 201- Promotional, 202-Regular';
COMMENT ON COLUMN EXT_PRICE_OUT.promotion_name IS 'The promotion name.';
COMMENT ON COLUMN EXT_PRICE_OUT.selling_unit_retail IS 'Contains the current single unit retail in the selling unit of measure. ';
COMMENT ON COLUMN EXT_PRICE_OUT.selling_unit_retail_currency IS 'Contains the selling unit retail currency.';
COMMENT ON COLUMN EXT_PRICE_OUT.selling_uom IS 'Contains the selling unit of measure for an items single-unit retail.';
COMMENT ON COLUMN EXT_PRICE_OUT.multi_units IS 'Contains the current multi-units.';
COMMENT ON COLUMN EXT_PRICE_OUT.multi_unit_retail IS 'Contains the current multi-unit retail in the selling unit of measure. ';
COMMENT ON COLUMN EXT_PRICE_OUT.multi_unit_retail_currency IS 'Contains the multi-unit retail currency.';
COMMENT ON COLUMN EXT_PRICE_OUT.multi_unit_selling_uom IS 'Contains the selling unit of measure for an items multi-unit retail.';
COMMENT ON COLUMN EXT_PRICE_OUT.create_datetime IS 'This column holds the record creation date.';
COMMENT ON COLUMN EXT_PRICE_OUT.rec_id IS 'The id of the record.';
COMMENT ON COLUMN EXT_PRICE_OUT.retail_change_ind IS 'Indicates whether the retail changed with this price change.Valid values are: 0 - retail price not changed; 1 - retail price changed.';
COMMENT ON COLUMN EXT_PRICE_OUT.multi_unit_impact IS 'Indicates if the Price Change has impact to Multi Unit retail.  Valid value are AU - Multi Unit information is added or updated; R - Multi Unit information is removed; N - Multi unit information is not changed.';
COMMENT ON COLUMN EXT_PRICE_OUT.price_event_id IS 'The id of the price event.';


-- Add BDI primary key constraint

ALTER TABLE EXT_PRICE_OUT ADD CONSTRAINT pk_ext_price_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE EXT_PRICE_OUT ADD CONSTRAINT chk_type_ext_price_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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