-- Interface Module: ReplItemLoc_Fnd
-- Interface: Repl_Item_Loc_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE repl_item_loc_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: REPL_ITEM_LOC_OUT
-- This table is used to integrate Replenishment Item Location information.

CREATE TABLE REPL_ITEM_LOC_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT repl_item_loc_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',
  -- Alphanumeric identifier for the item on replenishment.
  item VARCHAR2(25) NOT NULL,
  -- Location that is on replenishment. Will hold only vritual warehouse in the multi-channel environment.
  location NUMBER(10,0) NOT NULL,
  -- Contains the type of the location on replenishment.  Valid values include Store, Warehouse.
  loc_type VARCHAR2(6) NOT NULL,
  -- The parent/group of the item on replenishment.
  item_parent VARCHAR2(25),
  -- The grandparent/master group of the item on replenishment.
  item_grandparent VARCHAR2(25),
  -- Contains the numeric identifier of the supplier from which the specified location will source the replenishment demand for the specified item location.  Used for items on Direct to Store/Warehouse or Cross-docked replenishment.
  primary_repl_supplier NUMBER(10,0),
  -- Contains the country code of the supplier country that will be used to supply the replenishment demand for the specified item location.  Used for items on Direct to Store/Warehouse or Cross-docked replenishment.
  origin_country_id VARCHAR2(3),
  -- Contains the number representing when the specified item location will be reviewed for replenishment.  Valid values are 0-14.  A 0 represents a weekly review cycle, a 1 represents a daily review cycle, a 2 represents a review cycle of every 2 weeks, a 3 represents a review cycle of every 3 weeks, etc.
  review_cycle VARCHAR2(6) NOT NULL,
  -- Contains the sourcing strategy for the item/location relationship.  Valid values are Direct to store (supplier to store), Cross-docked  (supplier to store flowing through the warehouse),  Warehouse stocked (warehouse to store), L for WH/Cross Link (warehouse to store and supplier to warehouse if insufficient warehouse stock).
  stock_cat VARCHAR2(6) NOT NULL,
  -- Determines if the replenishment process will create an actual order/transfer line item for the item location if there is a need for the item location or if only a record is written to the Replenishment Results table.  Valid values are Manual (a record is written to the Replenishment Results table - no order/transfer line item is created), Semi-Automatic (an order/transfer line item is created - the order line item will be added to an order in Worksheet status, the transfer line item will be added to a transfer in Approved status with a freight type of Hold), Automatic (an order/transfer line item is created - the order line item will be added to an order in Approved status, the transfer line item will be added to a transfer in Approved status), Buyer Worksheet (a record is written to the Replenishment Results table and can be added to a purchase order on the Buyer Worksheet form/a transfer line item is added to a transfer in Approved status with a freight type of Hold).
  repl_order_ctrl VARCHAR2(6) NOT NULL,
  -- Contains the numeric identifier of the warehouse through which the specified item will crossdock to the specified store for a stock category of Cross-docked; or the numeric identifier of the warehouse from which the specified item will be sourced for the specified store for a stock category of Warehouse Stocked.
  source_wh NUMBER(10,0),
  -- Contains the date on which the item location will start to be reviewed for replenishment.
  activate_date TIMESTAMP NOT NULL,
  -- Contains the date at which time the item location will no longer be reviewed for replenishment.
  deactivate_date TIMESTAMP,
  -- Contains the minimum amount of stock that needs to be on store shelves.  The presentation stock is nor expected to satisfy consumer demand but is expected to remain in the fixture.
  pres_stock NUMBER(12,4),
  -- Contains the amount of stock that cannot be sold as new and is not counted as part of inventory in the replenishment calculations.  The value in this field is the amount of stock used strictly for demonstration purposes.
  demo_stock NUMBER(12,4),
  -- Contains the character code for the algorithm that will be used to calculate the recommended order quantity for the item location.  Valid values include Constant, Min/Max, Floating point,  Time Supply, Dynamic, SO Store Orders.
  repl_method VARCHAR2(6) NOT NULL,
  -- Contains the required minimum number of units available for sale.  Used in the Min/Max replenishment method algorithm.
  min_stock NUMBER(12,4),
  -- Contains the required maximum number of units available for sale.  Used in the Constant, Min/Max and Floating Point replenishment method algorithms.
  max_stock NUMBER(12,4),
  -- Contains the percentage by which the min and max stock levels will be multiplied when calculating the recommended order quantity.  Ex. Actual max stock=max stock*increment percent.
  incr_pct NUMBER(12,4),
  -- Contains the minimum number of days of supply of stock to maintain.  Used in the Time Supply replenishment method algorithm.
  min_supply_days NUMBER(3,0),
  -- Contains the maximum number of days of supply of stock to maintain.  Used in the Time Supply replenishment method algorithm.
  max_supply_days NUMBER(3,0),
  -- Contains the number of days over which an average sales rate is calculated to be used in the Time Supply replenishment method algorithm.
  time_supply_horizon NUMBER(3,0),
  -- Contains the number of required days of on hand inventory to satisfy demand.  Used in the Dynamic replenishment method algorithm.
  inv_selling_days NUMBER(3,0),
  -- Contains the required measure of probability that demand is satisfied from on hand inventory.  Used in the Dynamic replenishment method algorithm.
  service_level NUMBER(5,2),
  -- Contains the percentage of sales that could have occurred if inventory had been available through the order lead time.  Used in the Dynamic replenishment method algorithm.
  lost_sales_factor NUMBER(5,2),
  -- Contains an indicator that determines if uploaded store orders should be rejected.  If the indicator is N, then store orders for all need dates are valid.  If Y, store orders with needs date on or after the NEXT_DELIVERY_DATE are valid.
  reject_store_ord_ind VARCHAR2(1),
  -- Indicator used to determine if the item/location should be exempt from scaling during the order scaling process during the replenishment process. Y - Yes. The item/locations order quantity will not be adjusted during the order scaling process. N - No. The item/locations order quantity will be adjusted during the order scaling process.
  non_scaling_ind VARCHAR2(1) NOT NULL,
  -- Contains the limit up to which order scaling can increase the order quantity for the item/location during the replenishment process.  If the replenishment method is C (constant), M (min/max), F (floating point) this value will be a quantity (ex. 1,000 eaches).  If the replenishment method is a dynamic or time supply method this value will be a number of days.  A 0 (zero) in this field indicates no max scaling value.  This field is intended to prevent run-away scaling processes as well as to protect items from being ordered to locations in such amounts that would result in perishable or obsolescence issues.
  max_scale_value NUMBER(12,4),
  -- Contains the expected number of days required to ship the item from the supplier to the initial receiving location, which may be a store or warehouse defined on this record or the cross-dock warehouse through which the item will flow to get to the store defined on this record.
  pickup_lead_time NUMBER(4,0),
  -- Contains the expected number of days required to move the item from the warehouse to the store defined on this record.
  wh_lead_time NUMBER(4,0),
  -- Contains the desired stock on hand for the item location when the end of season is reached.  Used in the Seasonal Dynamic and Seasonal Time Supply replenishment methods.
  terminal_stock_qty NUMBER(12,4),
  -- Contains the numeric identifier of the season for which this item location is being replenished.  Used in the Seasonal Dynamic and Seasonal Time Supply replenishment methods.
  season_id NUMBER(3,0),
  -- Contains the numeric identifier of the phase within the season for which this item location is being replenished.  Used in the Seasonal Dynamic and Seasonal Time Supply replenishment methods.
  phase_id NUMBER(3,0),
  -- Contains the date on which the item location was last reviewed.
  last_review_date TIMESTAMP,
  -- Contains the date on which the item location will be reviewed next.
  next_review_date TIMESTAMP,
  -- Contains the numeric identifier of the primary simple pack that will be used to replenish the item.  If the simple_pack_in = Yes and this field is NULL, this indicates that multiple simple packs are being used to replenish the item.  If the simple_pack_ind = Yes and this field is NOT NULL, this indicates that this simple pack will be used to replenish the item.  This field is used for performance purposes during the batch replenishment process.
  primary_pack_no VARCHAR2(25),
  -- Contains the quantity of the item in the simple pack if a primary replenishment pack is being used to replenish the item.  This field is used for performance purposes during the batch replenishment process.
  primary_pack_qty NUMBER(12,4),
  -- The allowable unit change to order quantities generated from replenishment.
  unit_tolerance NUMBER(12,4),
  -- The allowable percent change to order quantities generated from replenishment.
  pct_tolerance NUMBER(12,4),
  -- This field will related to the item_season_seq_no for the associated item_seasons record.
  item_season_seq_no NUMBER(4,0),
  -- Indicates whether or not unit and percent tolerances will be used.
  use_tolerance_ind VARCHAR2(1) NOT NULL,
  -- Contains the last delivery date that replenishment was run for.  If replenishment is run on Monday and an order is created that will arrive on Friday, this field will be updated with Fridays date.
  last_delivery_date TIMESTAMP,
  -- Contains the next delivery date calculated for the next review cycle.  If, for the next review cycle, an order will be created that is calculated to arrive on Thursday, this field will be updated with Thursdays date.
  next_delivery_date TIMESTAMP,
  -- This field is only populated if the item on replenishment is using the Warehouse Stocked/Cross-Docked stock category.   It will store the portion of the recommended order quantity that cannot be fulfilled by warehouse stock.  This quantity will be the quantity that is ordered when an order is created.
  mbr_order_qty NUMBER(12,4),
  -- Pickup_lead_time for MBR cross-link line items after reqext processes them.
  adj_pickup_lead_time NUMBER(4,0),
  -- Supp_lead_time for MBR cross-link line items after reqext processes them.
  adj_supp_lead_time NUMBER(4,0),
  -- A reference number to link the item on the transfer to any purchase orders that have been created to allow the from location (i.e. warehouse) on the transfer to fulfill the transfer quantity to the to location (i.e store) on the transfer.
  tsf_po_link_no NUMBER(12,0),
  -- This column will contain the last recommended order quantity created by Vendor Replenishment Extraction (rplext.pc).  It will be used by the investment buy opportunity calculation (ibcalc.pc) in calculating future available quantity for the item/location.
  last_roq NUMBER(12,4),
  -- Status of the item.  Valid values are: W = Worksheet, S = Submitted, A = Approved. This field is used for performance purposes during the batch replenishment process.
  status VARCHAR2(1),
  -- Contains the number of the department in which the item is located.  This field is used for performance purposes during the batch replenishment process.
  dept NUMBER(4,0),
  -- Contains the number of the class in which the item is located.  This field is used for performance purposes during the batch replenishment process.
  class NUMBER(4,0),
  -- Contains the number of the class in which the item is located.  This field is used for performance purposes during the batch replenishment process.
  subclass NUMBER(4,0),
  -- Contains the pack size level at which the item (or primary replenishment pack) is shipped between warehouses and stores.  Valid values are Case, Inner, Each.  If the item is being replenished using a primary replenishment pack (i.e. primary_pack_no is not NULL), the store order multiple will represent the packs store order multiple.  Otherwise, the field will contain the items store order multiple.  This field is used for performance purposes during the batch replenishment process.
  store_ord_mult VARCHAR2(6),
  -- This field contains the unit cost for the item (or simple pack if a replenishment simple pack is being used) for the replenishment supplier/country.  This field is stored in the suppliers currency.
  unit_cost NUMBER(20,4),
  -- This field contains the number of days that will elapse between the date an order is written and the delivery to the store or warehouse from the supplier.
  supp_lead_time NUMBER(4,0),
  -- This field contains the break pack size for the item for the supplier
  inner_pack_size NUMBER(12,4),
  -- This field contains the quantity that orders must be placed in multiples of for the supplier of the item.
  supp_pack_size NUMBER(12,4),
  -- This field contains the number of shipping units (cases) that make up one tier of a pallet.  Multiply TI x HI to get total number of units (cases) for a pallet.
  ti NUMBER(12,4),
  -- This field contains the number of tiers that make up a complete pallet (height).  Multiply TI x HI to get total number of units (cases) for a pallet.
  hi NUMBER(12,4),
  -- This field determines how order quantities will be rounded to Case, Layer and Pallet.
  round_lvl VARCHAR2(6),
  -- This field contains the Inner Rounding Threshold value.  During rounding, this value is used to determine whether to round partial Inner quantities up or down.
  round_to_inner_pct NUMBER(12,4),
  -- This field contains the Case Rounding Threshold value.  During rounding, this value is used to determine whether to round partial Case quantities up or down.
  round_to_case_pct NUMBER(12,4),
  -- This field contains the Layer Rounding Threshold value.  During rounding, this value is used to determine whether to round partial Layer quantities up or down
  round_to_layer_pct NUMBER(12,4),
  -- This field contains the Pallet Rounding Threshold value.  During rounding, this value is used to determine whether to round partial Pallet quantities up or down.
  round_to_pallet_pct NUMBER(12,4),
  -- Service Level Type that will drive the safety stock calculation algorithm, valid values are Simple Sales and Standard.
  service_level_type VARCHAR2(2),
  -- This indicates if a transfer should be created even though the warehouse does not have enough stock on hand. Default value is N
  tsf_zero_soh_ind VARCHAR2(1) NOT NULL,
  -- This indicates if an item can be replenished multiple times per day at the location. Default value is N.
  mult_runs_per_day_ind VARCHAR2(1) NOT NULL,
  -- This indicator will determine if the supplier lead time will be considered in the calculation of time supply order points and order up to point.
  add_lead_time_ind VARCHAR2(1)
);

COMMENT ON TABLE REPL_ITEM_LOC_OUT IS 'This table is used to integrate Replenishment Item Location information.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.item IS 'Alphanumeric identifier for the item on replenishment.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.location IS 'Location that is on replenishment. Will hold only vritual warehouse in the multi-channel environment.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.loc_type IS 'Contains the type of the location on replenishment.  Valid values include Store, Warehouse.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.item_parent IS 'The parent/group of the item on replenishment.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.item_grandparent IS 'The grandparent/master group of the item on replenishment.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.primary_repl_supplier IS 'Contains the numeric identifier of the supplier from which the specified location will source the replenishment demand for the specified item location.  Used for items on Direct to Store/Warehouse or Cross-docked replenishment.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.origin_country_id IS 'Contains the country code of the supplier country that will be used to supply the replenishment demand for the specified item location.  Used for items on Direct to Store/Warehouse or Cross-docked replenishment.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.review_cycle IS 'Contains the number representing when the specified item location will be reviewed for replenishment.  Valid values are 0-14.  A 0 represents a weekly review cycle, a 1 represents a daily review cycle, a 2 represents a review cycle of every 2 weeks, a 3 represents a review cycle of every 3 weeks, etc.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.stock_cat IS 'Contains the sourcing strategy for the item/location relationship.  Valid values are Direct to store (supplier to store), Cross-docked  (supplier to store flowing through the warehouse),  Warehouse stocked (warehouse to store), L for WH/Cross Link (warehouse to store and supplier to warehouse if insufficient warehouse stock).';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.repl_order_ctrl IS 'Determines if the replenishment process will create an actual order/transfer line item for the item location if there is a need for the item location or if only a record is written to the Replenishment Results table.  Valid values are Manual (a record is written to the Replenishment Results table - no order/transfer line item is created), Semi-Automatic (an order/transfer line item is created - the order line item will be added to an order in Worksheet status, the transfer line item will be added to a transfer in Approved status with a freight type of Hold), Automatic (an order/transfer line item is created - the order line item will be added to an order in Approved status, the transfer line item will be added to a transfer in Approved status), Buyer Worksheet (a record is written to the Replenishment Results table and can be added to a purchase order on the Buyer Worksheet form/a transfer line item is added to a transfer in Approved status with a freight type of Hold).';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.source_wh IS 'Contains the numeric identifier of the warehouse through which the specified item will crossdock to the specified store for a stock category of Cross-docked; or the numeric identifier of the warehouse from which the specified item will be sourced for the specified store for a stock category of Warehouse Stocked.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.activate_date IS 'Contains the date on which the item location will start to be reviewed for replenishment.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.deactivate_date IS 'Contains the date at which time the item location will no longer be reviewed for replenishment.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.pres_stock IS 'Contains the minimum amount of stock that needs to be on store shelves.  The presentation stock is nor expected to satisfy consumer demand but is expected to remain in the fixture.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.demo_stock IS 'Contains the amount of stock that cannot be sold as new and is not counted as part of inventory in the replenishment calculations.  The value in this field is the amount of stock used strictly for demonstration purposes.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.repl_method IS 'Contains the character code for the algorithm that will be used to calculate the recommended order quantity for the item location.  Valid values include Constant, Min/Max, Floating point,  Time Supply, Dynamic, SO Store Orders.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.min_stock IS 'Contains the required minimum number of units available for sale.  Used in the Min/Max replenishment method algorithm.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.max_stock IS 'Contains the required maximum number of units available for sale.  Used in the Constant, Min/Max and Floating Point replenishment method algorithms.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.incr_pct IS 'Contains the percentage by which the min and max stock levels will be multiplied when calculating the recommended order quantity.  Ex. Actual max stock=max stock*increment percent.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.min_supply_days IS 'Contains the minimum number of days of supply of stock to maintain.  Used in the Time Supply replenishment method algorithm.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.max_supply_days IS 'Contains the maximum number of days of supply of stock to maintain.  Used in the Time Supply replenishment method algorithm.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.time_supply_horizon IS 'Contains the number of days over which an average sales rate is calculated to be used in the Time Supply replenishment method algorithm.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.inv_selling_days IS 'Contains the number of required days of on hand inventory to satisfy demand.  Used in the Dynamic replenishment method algorithm.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.service_level IS 'Contains the required measure of probability that demand is satisfied from on hand inventory.  Used in the Dynamic replenishment method algorithm.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.lost_sales_factor IS 'Contains the percentage of sales that could have occurred if inventory had been available through the order lead time.  Used in the Dynamic replenishment method algorithm.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.reject_store_ord_ind IS 'Contains an indicator that determines if uploaded store orders should be rejected.  If the indicator is N, then store orders for all need dates are valid.  If Y, store orders with needs date on or after the NEXT_DELIVERY_DATE are valid.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.non_scaling_ind IS 'Indicator used to determine if the item/location should be exempt from scaling during the order scaling process during the replenishment process. Y - Yes. The item/locations order quantity will not be adjusted during the order scaling process. N - No. The item/locations order quantity will be adjusted during the order scaling process.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.max_scale_value IS 'Contains the limit up to which order scaling can increase the order quantity for the item/location during the replenishment process.  If the replenishment method is C (constant), M (min/max), F (floating point) this value will be a quantity (ex. 1,000 eaches).  If the replenishment method is a dynamic or time supply method this value will be a number of days.  A 0 (zero) in this field indicates no max scaling value.  This field is intended to prevent run-away scaling processes as well as to protect items from being ordered to locations in such amounts that would result in perishable or obsolescence issues.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.pickup_lead_time IS 'Contains the expected number of days required to ship the item from the supplier to the initial receiving location, which may be a store or warehouse defined on this record or the cross-dock warehouse through which the item will flow to get to the store defined on this record.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.wh_lead_time IS 'Contains the expected number of days required to move the item from the warehouse to the store defined on this record.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.terminal_stock_qty IS 'Contains the desired stock on hand for the item location when the end of season is reached.  Used in the Seasonal Dynamic and Seasonal Time Supply replenishment methods.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.season_id IS 'Contains the numeric identifier of the season for which this item location is being replenished.  Used in the Seasonal Dynamic and Seasonal Time Supply replenishment methods.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.phase_id IS 'Contains the numeric identifier of the phase within the season for which this item location is being replenished.  Used in the Seasonal Dynamic and Seasonal Time Supply replenishment methods.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.last_review_date IS 'Contains the date on which the item location was last reviewed.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.next_review_date IS 'Contains the date on which the item location will be reviewed next.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.primary_pack_no IS 'Contains the numeric identifier of the primary simple pack that will be used to replenish the item.  If the simple_pack_in = Yes and this field is NULL, this indicates that multiple simple packs are being used to replenish the item.  If the simple_pack_ind = Yes and this field is NOT NULL, this indicates that this simple pack will be used to replenish the item.  This field is used for performance purposes during the batch replenishment process.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.primary_pack_qty IS 'Contains the quantity of the item in the simple pack if a primary replenishment pack is being used to replenish the item.  This field is used for performance purposes during the batch replenishment process.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.unit_tolerance IS 'The allowable unit change to order quantities generated from replenishment.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.pct_tolerance IS 'The allowable percent change to order quantities generated from replenishment.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.item_season_seq_no IS 'This field will related to the item_season_seq_no for the associated item_seasons record.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.use_tolerance_ind IS 'Indicates whether or not unit and percent tolerances will be used.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.last_delivery_date IS 'Contains the last delivery date that replenishment was run for.  If replenishment is run on Monday and an order is created that will arrive on Friday, this field will be updated with Fridays date.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.next_delivery_date IS 'Contains the next delivery date calculated for the next review cycle.  If, for the next review cycle, an order will be created that is calculated to arrive on Thursday, this field will be updated with Thursdays date.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.mbr_order_qty IS 'This field is only populated if the item on replenishment is using the Warehouse Stocked/Cross-Docked stock category.   It will store the portion of the recommended order quantity that cannot be fulfilled by warehouse stock.  This quantity will be the quantity that is ordered when an order is created.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.adj_pickup_lead_time IS 'Pickup_lead_time for MBR cross-link line items after reqext processes them.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.adj_supp_lead_time IS 'Supp_lead_time for MBR cross-link line items after reqext processes them.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.tsf_po_link_no IS 'A reference number to link the item on the transfer to any purchase orders that have been created to allow the from location (i.e. warehouse) on the transfer to fulfill the transfer quantity to the to location (i.e store) on the transfer.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.last_roq IS 'This column will contain the last recommended order quantity created by Vendor Replenishment Extraction (rplext.pc).  It will be used by the investment buy opportunity calculation (ibcalc.pc) in calculating future available quantity for the item/location.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.status IS 'Status of the item.  Valid values are: W = Worksheet, S = Submitted, A = Approved. This field is used for performance purposes during the batch replenishment process.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.dept IS 'Contains the number of the department in which the item is located.  This field is used for performance purposes during the batch replenishment process.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.class IS 'Contains the number of the class in which the item is located.  This field is used for performance purposes during the batch replenishment process.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.subclass IS 'Contains the number of the class in which the item is located.  This field is used for performance purposes during the batch replenishment process.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.store_ord_mult IS 'Contains the pack size level at which the item (or primary replenishment pack) is shipped between warehouses and stores.  Valid values are Case, Inner, Each.  If the item is being replenished using a primary replenishment pack (i.e. primary_pack_no is not NULL), the store order multiple will represent the packs store order multiple.  Otherwise, the field will contain the items store order multiple.  This field is used for performance purposes during the batch replenishment process.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.unit_cost IS 'This field contains the unit cost for the item (or simple pack if a replenishment simple pack is being used) for the replenishment supplier/country.  This field is stored in the suppliers currency.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.supp_lead_time IS 'This field contains the number of days that will elapse between the date an order is written and the delivery to the store or warehouse from the supplier.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.inner_pack_size IS 'This field contains the break pack size for the item for the supplier';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.supp_pack_size IS 'This field contains the quantity that orders must be placed in multiples of for the supplier of the item.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.ti IS 'This field contains the number of shipping units (cases) that make up one tier of a pallet.  Multiply TI x HI to get total number of units (cases) for a pallet.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.hi IS 'This field contains the number of tiers that make up a complete pallet (height).  Multiply TI x HI to get total number of units (cases) for a pallet.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.round_lvl IS 'This field determines how order quantities will be rounded to Case, Layer and Pallet.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.round_to_inner_pct IS 'This field contains the Inner Rounding Threshold value.  During rounding, this value is used to determine whether to round partial Inner quantities up or down.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.round_to_case_pct IS 'This field contains the Case Rounding Threshold value.  During rounding, this value is used to determine whether to round partial Case quantities up or down.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.round_to_layer_pct IS 'This field contains the Layer Rounding Threshold value.  During rounding, this value is used to determine whether to round partial Layer quantities up or down';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.round_to_pallet_pct IS 'This field contains the Pallet Rounding Threshold value.  During rounding, this value is used to determine whether to round partial Pallet quantities up or down.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.service_level_type IS 'Service Level Type that will drive the safety stock calculation algorithm, valid values are Simple Sales and Standard.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.tsf_zero_soh_ind IS 'This indicates if a transfer should be created even though the warehouse does not have enough stock on hand. Default value is N';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.mult_runs_per_day_ind IS 'This indicates if an item can be replenished multiple times per day at the location. Default value is N.';
COMMENT ON COLUMN REPL_ITEM_LOC_OUT.add_lead_time_ind IS 'This indicator will determine if the supplier lead time will be considered in the calculation of time supply order points and order up to point.';


-- Add BDI primary key constraint

ALTER TABLE REPL_ITEM_LOC_OUT ADD CONSTRAINT pk_repl_item_loc_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE REPL_ITEM_LOC_OUT ADD CONSTRAINT chk_type_repl_item_loc_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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