-- Interface Module: PriceChange_Tx
-- Interface: Price_Change_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE price_change_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: PRICE_CHANGE_OUT
-- Table to hold price change price event information.

CREATE TABLE PRICE_CHANGE_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT price_change_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',
  -- The id of the record.
  rec_id NUMBER(10) NOT NULL,
  -- The record type.  Valid Values: Create/Update/Delete
  record_type VARCHAR2(50) NOT NULL,
  -- The price change ID.
  price_change_id NUMBER(15) NOT NULL,
  -- The Item id
  item VARCHAR2(25),
  -- The location id.
  location NUMBER(10),
  -- Location Type.  Valid values: S(Store)/W(Warehouse)
  location_type VARCHAR2(30),
  -- Effective date of price change
  effective_date TIMESTAMP(2),
  -- The retail with for the item and location based on the price change.
  retail NUMBER(20,4),
  -- The retail Unit Of Measure
  uom VARCHAR2(4),
  -- The currency for the location.
  currency VARCHAR2(3),
  -- Indicates whether the retail changed with this price change.
  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,
  -- Number of multi units
  multi_units NUMBER(12,4),
  -- The Multi Unit Retail value
  multi_unit_retail NUMBER(20,4),
  -- The Multi Unit Retail Selling UOM
  multi_unit_selling_uom VARCHAR2(4),
  -- The Multi Unit Retail Currency
  multi_unit_retail_currency VARCHAR2(3)
);

COMMENT ON TABLE PRICE_CHANGE_OUT IS 'Table to hold price change price event information.';
COMMENT ON COLUMN PRICE_CHANGE_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN PRICE_CHANGE_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN PRICE_CHANGE_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN PRICE_CHANGE_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN PRICE_CHANGE_OUT.rec_id IS 'The id of the record.';
COMMENT ON COLUMN PRICE_CHANGE_OUT.record_type IS 'The record type.  Valid Values: Create/Update/Delete';
COMMENT ON COLUMN PRICE_CHANGE_OUT.price_change_id IS 'The price change ID.';
COMMENT ON COLUMN PRICE_CHANGE_OUT.item IS 'The Item id';
COMMENT ON COLUMN PRICE_CHANGE_OUT.location IS 'The location id.';
COMMENT ON COLUMN PRICE_CHANGE_OUT.location_type IS 'Location Type.  Valid values: S(Store)/W(Warehouse)';
COMMENT ON COLUMN PRICE_CHANGE_OUT.effective_date IS 'Effective date of price change';
COMMENT ON COLUMN PRICE_CHANGE_OUT.retail IS 'The retail with for the item and location based on the price change.';
COMMENT ON COLUMN PRICE_CHANGE_OUT.uom IS 'The retail Unit Of Measure';
COMMENT ON COLUMN PRICE_CHANGE_OUT.currency IS 'The currency for the location.';
COMMENT ON COLUMN PRICE_CHANGE_OUT.retail_change_ind IS 'Indicates whether the retail changed with this price change.';
COMMENT ON COLUMN PRICE_CHANGE_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 PRICE_CHANGE_OUT.multi_units IS 'Number of multi units';
COMMENT ON COLUMN PRICE_CHANGE_OUT.multi_unit_retail IS 'The Multi Unit Retail value';
COMMENT ON COLUMN PRICE_CHANGE_OUT.multi_unit_selling_uom IS 'The Multi Unit Retail Selling UOM';
COMMENT ON COLUMN PRICE_CHANGE_OUT.multi_unit_retail_currency IS 'The Multi Unit Retail Currency';


-- Add BDI primary key constraint

ALTER TABLE PRICE_CHANGE_OUT ADD CONSTRAINT pk_price_change_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE PRICE_CHANGE_OUT ADD CONSTRAINT chk_type_price_change_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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