-- Interface Module: PriceHist_Fnd
-- Interface: Price_Hist_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE price_hist_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: PRICE_HIST_OUT
-- This table contains a history of all price changes that have ever been applied within the system.  History is kept on this table forever by SKU/store combination.

CREATE TABLE PRICE_HIST_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT price_hist_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',
  -- Contains a code number which indicates the type of transaction which caused the price change.  Valid values are:   0 = New item added   2 = Unit cost was change   4 = Single unit retail was changed     8 = Single unit retail was changed in Clearance    10 =  Multi-unit retail was changed   11 = Single unit retail and Multi-unit retail were changed  99 = Item was deleted from file.
  tran_type NUMBER(2) NOT NULL,
  -- Contains the reason for the price change.  If the record is written as a result of the creation of a new item, then the reason code is zero.  Otherwise, the values come from the mkd_reason table and are the same as the reason code used on the price change event..
  reason NUMBER(6),
  -- This field identifies the unique alphanumeric value for the transaction level item.
  item VARCHAR2(25) NOT NULL,
  -- Contains the number that uniquely identifies the location.
  loc NUMBER(10) NOT NULL,
  -- Identifies the location as a Store or a Warehouse.
  loc_type VARCHAR2(1),
  -- This field holds the primary supplier cost, i.e. a record is written to this table when the primary supplier cost is changed.
  unit_cost NUMBER(20,4),
  -- Contains the current single unit retail in the standard unit of measure.  If the record is being written as a result of a change in the single unit retail, then this field contains the new single unit retail.  This field is stored in the local currency.
  unit_retail NUMBER(20,4),
  -- Contains the current single unit retail in the selling unit of measure.  If the record is being written as a result of a change in the single unit retail, then this field contains the new single unit retail.  This field is stored in the local currency.
  selling_unit_retail NUMBER(20,4),
  -- Contains the selling unit of measure for an items single-unit retail.
  selling_uom VARCHAR2(4),
  -- Contains the date on which the price change went effect.
  action_date TIMESTAMP NOT NULL,
  -- Contains the current multi-units.  If the record is being written as a result of a change in the multi-unit retail, then this field contains the new multi-units.
  multi_units NUMBER(12,4),
  -- Contains the current multi-unit retail in the selling unit of measure.  If the record is being written as a result of a change in the multi-unit retail, then this field contains the new multi-unit retail.  This field is stored in the local currency.
  multi_unit_retail NUMBER(20,4),
  -- Contains the selling unit of measure for an items multi-unit retail.
  multi_selling_uom VARCHAR2(4),
  -- The POST_DATE column will store that date that a record is inserted or updated in the PRICE_HIST table.
  post_date TIMESTAMP,
  -- This column holds the User id of the user who created the record.
  create_id VARCHAR2(30) NOT NULL,
  -- This column holds the record creation date.
  create_datetime TIMESTAMP NOT NULL
);

COMMENT ON TABLE PRICE_HIST_OUT IS 'This table contains a history of all price changes that have ever been applied within the system.  History is kept on this table forever by SKU/store combination.';
COMMENT ON COLUMN PRICE_HIST_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN PRICE_HIST_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN PRICE_HIST_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN PRICE_HIST_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN PRICE_HIST_OUT.tran_type IS 'Contains a code number which indicates the type of transaction which caused the price change.  Valid values are:   0 = New item added   2 = Unit cost was change   4 = Single unit retail was changed     8 = Single unit retail was changed in Clearance    10 =  Multi-unit retail was changed   11 = Single unit retail and Multi-unit retail were changed  99 = Item was deleted from file.';
COMMENT ON COLUMN PRICE_HIST_OUT.reason IS 'Contains the reason for the price change.  If the record is written as a result of the creation of a new item, then the reason code is zero.  Otherwise, the values come from the mkd_reason table and are the same as the reason code used on the price change event..';
COMMENT ON COLUMN PRICE_HIST_OUT.item IS 'This field identifies the unique alphanumeric value for the transaction level item.';
COMMENT ON COLUMN PRICE_HIST_OUT.loc IS 'Contains the number that uniquely identifies the location.';
COMMENT ON COLUMN PRICE_HIST_OUT.loc_type IS 'Identifies the location as a Store or a Warehouse.';
COMMENT ON COLUMN PRICE_HIST_OUT.unit_cost IS 'This field holds the primary supplier cost, i.e. a record is written to this table when the primary supplier cost is changed.';
COMMENT ON COLUMN PRICE_HIST_OUT.unit_retail IS 'Contains the current single unit retail in the standard unit of measure.  If the record is being written as a result of a change in the single unit retail, then this field contains the new single unit retail.  This field is stored in the local currency.';
COMMENT ON COLUMN PRICE_HIST_OUT.selling_unit_retail IS 'Contains the current single unit retail in the selling unit of measure.  If the record is being written as a result of a change in the single unit retail, then this field contains the new single unit retail.  This field is stored in the local currency.';
COMMENT ON COLUMN PRICE_HIST_OUT.selling_uom IS 'Contains the selling unit of measure for an items single-unit retail.';
COMMENT ON COLUMN PRICE_HIST_OUT.action_date IS 'Contains the date on which the price change went effect.';
COMMENT ON COLUMN PRICE_HIST_OUT.multi_units IS 'Contains the current multi-units.  If the record is being written as a result of a change in the multi-unit retail, then this field contains the new multi-units.';
COMMENT ON COLUMN PRICE_HIST_OUT.multi_unit_retail IS 'Contains the current multi-unit retail in the selling unit of measure.  If the record is being written as a result of a change in the multi-unit retail, then this field contains the new multi-unit retail.  This field is stored in the local currency.';
COMMENT ON COLUMN PRICE_HIST_OUT.multi_selling_uom IS 'Contains the selling unit of measure for an items multi-unit retail.';
COMMENT ON COLUMN PRICE_HIST_OUT.post_date IS 'The POST_DATE column will store that date that a record is inserted or updated in the PRICE_HIST table.';
COMMENT ON COLUMN PRICE_HIST_OUT.create_id IS 'This column holds the User id of the user who created the record.';
COMMENT ON COLUMN PRICE_HIST_OUT.create_datetime IS 'This column holds the record creation date.';


-- Add BDI primary key constraint

ALTER TABLE PRICE_HIST_OUT ADD CONSTRAINT pk_price_hist_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE PRICE_HIST_OUT ADD CONSTRAINT chk_type_price_hist_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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