-- Interface Module: Wh_Fnd
-- Interface: Wh_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE wh_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: WH_OUT
-- This table is used to integrate wh information.

CREATE TABLE WH_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT wh_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 the number which uniquely identifies the warehouse. The wh table stores all warehouses in the system.  Both virtual and physical warehouses will be stored on this table.  The addition of the new column, physical_wh, helps determine which warehouses are physical and which are virtual.  All physical warehouses will have a physical_wh column value equal to their wh number.  Virtual warehouses will have a valid physical warehouse in this column.
  wh_id NUMBER(10,0) NOT NULL,
  -- Contains the name of the warehouse which, along with the warehouse number, identifies the warehouse.
  wh_name VARCHAR2(150) NOT NULL,
  -- Secondary name of the warehouse.
  wh_name_secondary VARCHAR2(150),
  -- Holds the email address for the location
  email VARCHAR2(100),
  -- warehouse is located.
  vat_region NUMBER(4,0),
  -- Contains the organization type that will be used in reporting purposes for the warehouse.  The type comes from the organizational hierarchy.   Valid values are:1 = Company 10 = Chain 20 = Area 30 = Region 40 = District 50 = Store
  org_hier_type NUMBER(4,0),
  -- Contains the code associated with the specific organizational hierarchy type.  Valid values include the company number, chain number, area number, etc.
  org_hier_value NUMBER(10,0),
  -- This field contains the currency code under which the warehouse operates.
  currency_code VARCHAR2(3) NOT NULL,
  -- This column will contain the number of the physical warehouse that is assigned to the virtual warehouse.
  physical_wh NUMBER(10,0) NOT NULL,
  -- This field holds the virtual warehouse that will used as the basis for all transactions for which only a physical warehouse and not a virtual warehouse has not been specified.
  primary_vwh NUMBER(10,0),
  -- This column will contain the channel for which the virtual warehouse will be assigned.
  channel_id NUMBER(4,0),
  -- This column will indicate if the warehouse is a stock holding location.  In a non-multichannel environment, this will always be Y.  In a multichannel environment it will be N for a physical warehouse and Y for a virtual warehouse.
  stockholding_ind VARCHAR2(1) NOT NULL,
  -- Indicates whether or not the warehouse is capable of distributing less than the supplier case quantity.   Valid values are : Y or N.
  break_pack_ind VARCHAR2(1) NOT NULL,
  -- Indicates that the warehouse is a Re-Distribution warehouse.  Used as a location on Purchase Orders in place of actual locations that are unknown at the time of Purchase Order creation and approval.  This value allows the Redistribution Report to identify orders requiring redistribution.  A Warehouse with this indicator will not be limited in any RMS transactions.   Valid values are Y or N.
  redist_wh_ind VARCHAR2(1) NOT NULL,
  -- Contains the delivery policy of the warehouse.  Next Day indicates that the if a location is closed, the warehouse will deliver on the next day.  Next Valid Delivery Day indicates that the warehouse will wait until the next scheduled delivery day before delivering. Valid values come from the DLVY code on code_head/code_detail.
  delivery_policy VARCHAR2(6) NOT NULL,
  -- Indicator used to restrict virtual warehouses from receiving stock during an inbound type transaction (ex. positive SOH inventory adjustment, PO over-receipt) when stock needs to be prorated across virtual warehouses within a physical warehouse because a virtual warehouse in the physical warehouse has not been identified for the transaction.  The indicator will restrict the virtual warehouse from receiving stock unless all the valid virtual warehouses determined by the system are restricted, then the stock will be distributed across those restricted virtual warehouses. This indicator will only be used in a multi-channel environment.  It is always set to No in a single channel environment.
  restricted_ind VARCHAR2(1) NOT NULL,
  -- Indicator used to determine if the virtual warehouse is affected last in transactions where inventory is removed or affected first in short-shipment type transactions where inventory is being added.  The indicator will be used in any outbound or inventory removal type transactions (ex. RTVs, negative SOH inventory adjustments, etc.) when the system has to distribute the transaction quantity across virtual warehouses within a physical warehouse either because a virtual warehouse has not been specified or couldnt be derived or if a virtual warehouse doesnt have enough stock to cover the transaction quantity and stock needs to be pulled from other virtual warehouse within the physical warehouse.  The indicator will also be used for inbound type transactions where there is some sort of short-shipment ex. a short-shipment for a PO).  The indicator will determine which virtual warehouses will have their order quantity fulfilled first with the receipt quantity.  Note that this indicator does not guarantee that stock will not be pulled from the virtual warehouse, it is only used to ensure that the virtual warehouse is affected last. This indicator will only be used in a multi-channel environment.  It is always set to No in a single channel environment.
  protected_ind VARCHAR2(1) NOT NULL,
  -- This indicator determines if a warehouse is forecastable. The intent of this indicator is to restrict the information being sent to RDF against which to generate forecasts.
  forecast_wh_ind VARCHAR2(1) NOT NULL,
  -- This column determines which virtual warehouses within a physical warehouse should be rounded together as well as determining which virtual warehouse receives the additional stock or decreased stock due to rounding. This value will be a virtual warehouse number. All warehouses will the same rounding seq number will be rounded together with the warehouse that equals the rounding seq receiving any extra stock.
  rounding_seq NUMBER(10,0),
  -- This indicator determines if a warehouse is replenishable.
  repl_ind VARCHAR2(1) NOT NULL,
  -- This field holds the replenishable warehouse that is linked to this virtual warehouse. This link implies that the virtual warehouse is included in the net inventory calculations for the replenishable warehouse.
  repl_wh_link NUMBER(10,0),
  -- This field contains the order from which the inventory is sourced from the linked warehouses.
  repl_src_ord NUMBER(1,0),
  -- This field indicates if the warehouse is an investment buy warehouse.
  ib_ind VARCHAR2(1) NOT NULL,
  -- This field contains the investment buy warehouse that is linked to the virtual warehouse. This link implies that the virtual warehouse is included in the net inventory calculations for the investment buy warehouse.
  ib_wh_link NUMBER(10,0),
  -- This indicator determines if the investment buys inventory should be automatically transferred to the turn (replenishable) warehouse when an order is received by the turn warehouse.
  auto_ib_clear VARCHAR2(1) NOT NULL,
  -- This field holds the Dun and Bradstreet number to identify the warehouse
  duns_number VARCHAR2(9),
  -- This field holds the Dun and Bradstreet number to identify the location
  duns_loc VARCHAR2(4),
  -- ID of the transfer entity with which this warehouse is associated. Valid values are found on the TSF_ENTITY table. A transfer entity is a group of locations that share legal requirements around product management.
  tsf_entity_id NUMBER(10,0),
  -- Yes/No value which indicates if this virtual warehouse is an internal finisher.
  finisher_ind VARCHAR2(1) NOT NULL,
  -- Warehouse inbound handling days are defined as the number of days that the warehouse requires to receive any item and get it to the shelf so that it is ready to pick.
  inbound_handling_days NUMBER(2,0) NOT NULL,
  -- this column will hold the oracle oraganizational unit id value.
  org_unit_id NUMBER(15,0),
  -- This attribute will be included in the location downloads to AIP.
  vwh_type VARCHAR2(6),
  -- This is the new column that will specify if the warehouse is a legal entity  (Importer, Exporter) or a regular warehouse. Valid values are: R - regular warehouse (including finisher); M - importer; X - exporter. Default value is R.
  org_entity_type VARCHAR2(1) NOT NULL,
  -- This is the name of the channel.
  channel_name VARCHAR2(120),
  -- This column determines whether the location is customer order location or not.
  customer_order_loc_ind VARCHAR2(1),
  -- Contains the default warehouse linked to the virtual warehouse.
  default_wh NUMBER(10,0),
  -- This column determines whether the location will support gift wrapping or not.
  gift_wrapping_ind VARCHAR2(1) NOT NULL,
  -- This column determines whether the location will support shipping to the customer for customer orders. Valid values are Y (Yes), N (No).
  customer_order_ship_ind VARCHAR2(1) NOT NULL
);

COMMENT ON TABLE WH_OUT IS 'This table is used to integrate wh information.';
COMMENT ON COLUMN WH_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN WH_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN WH_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN WH_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN WH_OUT.wh_id IS 'Contains the number which uniquely identifies the warehouse. The wh table stores all warehouses in the system.  Both virtual and physical warehouses will be stored on this table.  The addition of the new column, physical_wh, helps determine which warehouses are physical and which are virtual.  All physical warehouses will have a physical_wh column value equal to their wh number.  Virtual warehouses will have a valid physical warehouse in this column.';
COMMENT ON COLUMN WH_OUT.wh_name IS 'Contains the name of the warehouse which, along with the warehouse number, identifies the warehouse.';
COMMENT ON COLUMN WH_OUT.wh_name_secondary IS 'Secondary name of the warehouse.';
COMMENT ON COLUMN WH_OUT.email IS 'Holds the email address for the location';
COMMENT ON COLUMN WH_OUT.vat_region IS 'warehouse is located.';
COMMENT ON COLUMN WH_OUT.org_hier_type IS 'Contains the organization type that will be used in reporting purposes for the warehouse.  The type comes from the organizational hierarchy.   Valid values are:1 = Company 10 = Chain 20 = Area 30 = Region 40 = District 50 = Store';
COMMENT ON COLUMN WH_OUT.org_hier_value IS 'Contains the code associated with the specific organizational hierarchy type.  Valid values include the company number, chain number, area number, etc.';
COMMENT ON COLUMN WH_OUT.currency_code IS 'This field contains the currency code under which the warehouse operates.';
COMMENT ON COLUMN WH_OUT.physical_wh IS 'This column will contain the number of the physical warehouse that is assigned to the virtual warehouse.';
COMMENT ON COLUMN WH_OUT.primary_vwh IS 'This field holds the virtual warehouse that will used as the basis for all transactions for which only a physical warehouse and not a virtual warehouse has not been specified.';
COMMENT ON COLUMN WH_OUT.channel_id IS 'This column will contain the channel for which the virtual warehouse will be assigned.';
COMMENT ON COLUMN WH_OUT.stockholding_ind IS 'This column will indicate if the warehouse is a stock holding location.  In a non-multichannel environment, this will always be Y.  In a multichannel environment it will be N for a physical warehouse and Y for a virtual warehouse.';
COMMENT ON COLUMN WH_OUT.break_pack_ind IS 'Indicates whether or not the warehouse is capable of distributing less than the supplier case quantity.   Valid values are : Y or N.';
COMMENT ON COLUMN WH_OUT.redist_wh_ind IS 'Indicates that the warehouse is a Re-Distribution warehouse.  Used as a location on Purchase Orders in place of actual locations that are unknown at the time of Purchase Order creation and approval.  This value allows the Redistribution Report to identify orders requiring redistribution.  A Warehouse with this indicator will not be limited in any RMS transactions.   Valid values are Y or N.';
COMMENT ON COLUMN WH_OUT.delivery_policy IS 'Contains the delivery policy of the warehouse.  Next Day indicates that the if a location is closed, the warehouse will deliver on the next day.  Next Valid Delivery Day indicates that the warehouse will wait until the next scheduled delivery day before delivering. Valid values come from the DLVY code on code_head/code_detail.';
COMMENT ON COLUMN WH_OUT.restricted_ind IS 'Indicator used to restrict virtual warehouses from receiving stock during an inbound type transaction (ex. positive SOH inventory adjustment, PO over-receipt) when stock needs to be prorated across virtual warehouses within a physical warehouse because a virtual warehouse in the physical warehouse has not been identified for the transaction.  The indicator will restrict the virtual warehouse from receiving stock unless all the valid virtual warehouses determined by the system are restricted, then the stock will be distributed across those restricted virtual warehouses. This indicator will only be used in a multi-channel environment.  It is always set to No in a single channel environment.';
COMMENT ON COLUMN WH_OUT.protected_ind IS 'Indicator used to determine if the virtual warehouse is affected last in transactions where inventory is removed or affected first in short-shipment type transactions where inventory is being added.  The indicator will be used in any outbound or inventory removal type transactions (ex. RTVs, negative SOH inventory adjustments, etc.) when the system has to distribute the transaction quantity across virtual warehouses within a physical warehouse either because a virtual warehouse has not been specified or couldnt be derived or if a virtual warehouse doesnt have enough stock to cover the transaction quantity and stock needs to be pulled from other virtual warehouse within the physical warehouse.  The indicator will also be used for inbound type transactions where there is some sort of short-shipment ex. a short-shipment for a PO).  The indicator will determine which virtual warehouses will have their order quantity fulfilled first with the receipt quantity.  Note that this indicator does not guarantee that stock will not be pulled from the virtual warehouse, it is only used to ensure that the virtual warehouse is affected last. This indicator will only be used in a multi-channel environment.  It is always set to No in a single channel environment.';
COMMENT ON COLUMN WH_OUT.forecast_wh_ind IS 'This indicator determines if a warehouse is forecastable. The intent of this indicator is to restrict the information being sent to RDF against which to generate forecasts.';
COMMENT ON COLUMN WH_OUT.rounding_seq IS 'This column determines which virtual warehouses within a physical warehouse should be rounded together as well as determining which virtual warehouse receives the additional stock or decreased stock due to rounding. This value will be a virtual warehouse number. All warehouses will the same rounding seq number will be rounded together with the warehouse that equals the rounding seq receiving any extra stock.';
COMMENT ON COLUMN WH_OUT.repl_ind IS 'This indicator determines if a warehouse is replenishable.';
COMMENT ON COLUMN WH_OUT.repl_wh_link IS 'This field holds the replenishable warehouse that is linked to this virtual warehouse. This link implies that the virtual warehouse is included in the net inventory calculations for the replenishable warehouse.';
COMMENT ON COLUMN WH_OUT.repl_src_ord IS 'This field contains the order from which the inventory is sourced from the linked warehouses.';
COMMENT ON COLUMN WH_OUT.ib_ind IS 'This field indicates if the warehouse is an investment buy warehouse.';
COMMENT ON COLUMN WH_OUT.ib_wh_link IS 'This field contains the investment buy warehouse that is linked to the virtual warehouse. This link implies that the virtual warehouse is included in the net inventory calculations for the investment buy warehouse.';
COMMENT ON COLUMN WH_OUT.auto_ib_clear IS 'This indicator determines if the investment buys inventory should be automatically transferred to the turn (replenishable) warehouse when an order is received by the turn warehouse.';
COMMENT ON COLUMN WH_OUT.duns_number IS 'This field holds the Dun and Bradstreet number to identify the warehouse';
COMMENT ON COLUMN WH_OUT.duns_loc IS 'This field holds the Dun and Bradstreet number to identify the location';
COMMENT ON COLUMN WH_OUT.tsf_entity_id IS 'ID of the transfer entity with which this warehouse is associated. Valid values are found on the TSF_ENTITY table. A transfer entity is a group of locations that share legal requirements around product management.';
COMMENT ON COLUMN WH_OUT.finisher_ind IS 'Yes/No value which indicates if this virtual warehouse is an internal finisher.';
COMMENT ON COLUMN WH_OUT.inbound_handling_days IS 'Warehouse inbound handling days are defined as the number of days that the warehouse requires to receive any item and get it to the shelf so that it is ready to pick.';
COMMENT ON COLUMN WH_OUT.org_unit_id IS 'this column will hold the oracle oraganizational unit id value.';
COMMENT ON COLUMN WH_OUT.vwh_type IS 'This attribute will be included in the location downloads to AIP.';
COMMENT ON COLUMN WH_OUT.org_entity_type IS 'This is the new column that will specify if the warehouse is a legal entity  (Importer, Exporter) or a regular warehouse. Valid values are: R - regular warehouse (including finisher); M - importer; X - exporter. Default value is R.';
COMMENT ON COLUMN WH_OUT.channel_name IS 'This is the name of the channel.';
COMMENT ON COLUMN WH_OUT.customer_order_loc_ind IS 'This column determines whether the location is customer order location or not.';
COMMENT ON COLUMN WH_OUT.default_wh IS 'Contains the default warehouse linked to the virtual warehouse.';
COMMENT ON COLUMN WH_OUT.gift_wrapping_ind IS 'This column determines whether the location will support gift wrapping or not.';
COMMENT ON COLUMN WH_OUT.customer_order_ship_ind IS 'This column determines whether the location will support shipping to the customer for customer orders. Valid values are Y (Yes), N (No).';


-- Add BDI primary key constraint

ALTER TABLE WH_OUT ADD CONSTRAINT pk_wh_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE WH_OUT ADD CONSTRAINT chk_type_wh_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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