-- Interface Module: ItemHdr_Fnd
-- Interface: Item_Hdr_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE item_hdr_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: ITEM_HDR_OUT
-- This table is used to integrate approved items.  This data includes all nodes of item families (e.g. styles/above tran level items, UPCs/below tran level items).  This information also includes packs (collections of items), non-sellable items, non-physical items (downloads, warrenties, etc) and other special putpose items.

CREATE TABLE ITEM_HDR_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT item_hdr_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',
  -- ID of item.  Data will always be present in this field.
  item VARCHAR2(25) NOT NULL,
  -- ID identifies the item/group at the level above the item. This value must exist as an item in another row on the item_master table.
  item_parent VARCHAR2(25),
  -- identifies the item/group two levels above the item. This value must exist as both an item and an item parent in another row on the item_master table.
  item_grandparent VARCHAR2(25),
  -- This field indicates if the Item is a pack.  A pack is a type of Item that acts as a collection of other Items.  Packs can either be sold together (e.g. pack goumey chef gift basket consisting of: 1 bottle organic olive oil, 1 bottle truffle oil, 1lb organic coffee, 1 box speciality herbal tea, ...) and/or ordered together (kelly sun 2015 holiday pajama assortment is pack that consists of 8 sets of girls pajamas, each of which has a different design; these items are bought as a pack but sold as units).  Packs will have a Y value. Regular items will have a N value.
  pack_ind VARCHAR2(1) NOT NULL,
  -- This field indicates if the pack item is a simple pack.  A simple pack is a collection that consists of multiple of the same item (e.g. it is possible to buy either one unit of a granola bar, or the display box of 20 granola bars - the display box is a pack item itself with a unique UPC; often the full box has a slightly different price than the individual items * qty).  Only simple packs will have a Y value.  Both complex packs (which consist of a mixture of different items) and regular items will have a N value.
  simple_pack_ind VARCHAR2(1) NOT NULL,
  -- Number indicating which of the three levels the item resides. The item level determines if the item stands alone or if it is part of a family of related items.  The concept is best explained with typical (although not exhaustive) examples.  Staple items generally have a item level = 1 UPCs for Staple items generally have an item level = 2 (and the staple item will be the UPCs parent item).  Fashion styles generally have an item level = 1.  Fashion skus generally have an item level = 2.  If UPCs for fashion skus generally have an item level = 3.  Valid values are 1, 2 and 3.  This field will always have data.
  item_level NUMBER(1,0) NOT NULL,
  -- Number indicating which of the three levels transactions occur for the items group. The transaction level is the level at which the items inventory is tracked in the system. The transaction level item will be inventoried, counted, transferred, shipped, etc.  Only one level of the hierarchy of an item family may contain transaction level items.  The concept is best explained with typical (although not exhaustive) examples.  Staple items generally have a TranLevel = 1.  UPCs for Staple items generally have an TranLevel = 1 (inventory txns occur at the staple sku level; sales of the item roll up to the parent staple sku).  Fashion styles generally have a TranLevel = 2 (the style itself is not sold/inventoried).  Fashion skus generally have an TranLevel = 2 (the fashion sku is sold/inventoried).  If UPCs for fashion skus generally have an TranLevel = 2 (the fashion sku is sold/inventoried).  There are some rare cases in vendor managed inventory where the TranLevel = 3.  Valid values are 1, 2 and 3.   This field will always have data.
  tran_level NUMBER(1,0) NOT NULL,
  -- This indicator is used to determine if inventory is held for the item/item family.  Inventory is held for most items.  However, inventory is not held (value = N) in some special cases, such as: Concession items (items that are sold by independent in location concessions),  Consignment items (items are are not owned by the retailer; financial and inventory processing occurs after the item is sold to a consumer), Containers sold then returned for deposit and some items that are transformed for sale.  Valid values are Y and N. This field will always have data.
  inventory_ind VARCHAR2(1) NOT NULL,
  -- This field will contain either ID or GROUP, based on whether the diff_1 is a group diff or a diff id.
  diff_1_level VARCHAR2(6),
  -- This field will hold a value of the types of differentiators contained in this differentiator group, such as S - size, C - color, F - flavor, E - scent, P - pattern. Valid values are stored in the DIFF_TYPE table.
  diff_1_type VARCHAR2(6),
  -- Diff_group or diff_id that differentiates the current item from its item_parent. For an item that is a parent, this field may be either a group (i.e. Mens pant sizes) or a value (6 oz).
  diff_1 VARCHAR2(10),
  -- This field will hold the description of this differentiator.
  diff_1_desc VARCHAR2(120),
  -- This field will contain either ID or GROUP, based on whether the diff_2 is a group diff or a diff id.
  diff_2_level VARCHAR2(6),
  -- This field will hold a value of the types of differentiators contained in this differentiator group, such as S - size, C - color, F - flavor, E - scent, P - pattern. Valid values are stored in the DIFF_TYPE table.
  diff_2_type VARCHAR2(6),
  -- Diff_group or diff_id that differentiates the current item from its item_parent. For an item that is a parent, this field may be either a group (i.e. Mens pant sizes) or a value (6 oz).
  diff_2 VARCHAR2(10),
  -- This field will hold the description of this differentiator.
  diff_2_desc VARCHAR2(120),
  -- This field will contain either ID or GROUP, based on whether the diff_3 is a group diff or a diff id.
  diff_3_level VARCHAR2(6),
  -- This field will hold a value of the types of differentiators contained in this differentiator group, such as S - size, C - color, F - flavor, E - scent, P - pattern. Valid values are stored in the DIFF_TYPE table.
  diff_3_type VARCHAR2(6),
  -- Diff_group or diff_id that differentiates the current item from its item_parent. For an item that is a parent, this field may be either a group (i.e. Mens pant sizes) or a value (6 oz).
  diff_3 VARCHAR2(10),
  -- This field will hold the description of this differentiator.
  diff_3_desc VARCHAR2(120),
  -- This field will contain either ID or GROUP, based on whether the diff_4 is a group diff or a diff id.
  diff_4_level VARCHAR2(6),
  -- This field will hold a value of the types of differentiators contained in this differentiator group, such as S - size, C - color, F - flavor, E - scent, P - pattern. Valid values are stored in the DIFF_TYPE table.
  diff_4_type VARCHAR2(6),
  -- Diff_group or diff_id that differentiates the current item from its item_parent. For an item that is a parent, this field may be either a group (i.e. Mens pant sizes) or a value (6 oz).
  diff_4 VARCHAR2(10),
  -- This field will hold the description of this differentiator.
  diff_4_desc VARCHAR2(120),
  -- Number identifying the department in the merchandise hierarchy to which the item belongs.  If the item has a parent, the parent and item will belong to the same department.  This data is required
  dept NUMBER(4,0) NOT NULL,
  -- Number uniquely identifying the class node which the item belongs to.  Class is not unique ID the merchandise hierarchy.  The combination of Dept/Class is unique, but requires use of a composite key.  The composite key is generally used in user interfaces.  The unique ID can be used in back end processing or in systems that can not have a composite key for a node in the merchandise hierarchy.
  unique_class NUMBER(10,0) NOT NULL,
  -- Number identifying the class in the merchandise hierarchy to which the item belongs.  If the item has a parent, the parent and item will belong to the same department and class.  Class is not a unique ID (e.g. every department can have a class 1).  This data is required.
  class NUMBER(4,0) NOT NULL,
  -- Number uniquely identifying the subclass node which the item belongs to.  Subclass is not unique ID  the merchandise hierarchy.  The combination of Dept/Class/Subclass is unique, but requires use of a composite key.  The composite key is generally used in user interfaces.  The unique ID can be used in back end processing or in systems that can not have a composite key for a node in the merchandise hierarchy.
  unique_subclass NUMBER(10,0) NOT NULL,
  -- Number identifying the subclass in the merchandise hierarchy to which the item belongs.  If the item has a parent, the parent and item will belong to the same department, class and subclass.  Subclass is not a unique ID (e.g. every department/class can have a subclass 1).  This data is required.
  subclass NUMBER(4,0) NOT NULL,
  -- Item life cycle status of the item.  At this time, this field will always contain the character A as only approved items are exported.
  status VARCHAR2(1) NOT NULL,
  -- Primary description of the item in the integration langauge of the system.  This value is required.
  description VARCHAR2(250) NOT NULL,
  -- Secondary descriptions of the item. This field can only be populated when system_options.secondary_desc_ind = Y.
  secondary_item_desc VARCHAR2(250),
  -- Shortened item description.  This value is required.
  short_desc VARCHAR2(120) NOT NULL,
  -- Description of the item in upper case.
  desc_up VARCHAR2(250) NOT NULL,
  -- This field contains the brand associated to an item.
  brand_name VARCHAR2(30),
  -- This field contains the description of the brand associated to an item.
  brand_description VARCHAR2(120),
  -- Indicates if the item is a merchandise item (Y, N).  Merchandise items are generally physical items (things that must be shipped/received and of which there is an inventory).  Non merchandise items are often items which do not have inventory.  Common examples include extra fees for service (extended warranties, alterations) or endlessly available items (downloads, in app purchases of bonus content, subscriptions).  All items, both merchandise and non-merchandise are exported from RMS.  This value is required.
  merchandise_ind VARCHAR2(1) NOT NULL,
  -- Indicates if the sub-transation level item is designated as the primary sub-transaction level item.  For transaction level items and above the value in this field will be N.  For sub transaction level items, this field may be either Y (if the current record is a primary UPC) or N (if the current record is not the primary UPC).
  primary_ref_item_ind VARCHAR2(1) NOT NULL,
  -- Cost zone group associated with the item. This field is only required when elc_ind (landed cost indicator) is set to Y on the system_options table.
  cost_zone_group_id NUMBER(4,0),
  -- Unit of measure in which stock and or financials of the item is tracked at a corporate level.  Unit of measure reference values may have to be manually synced between the systems as this is foundation data that is not currently bulk integrated out of Merch. This value is required.
  standard_uom VARCHAR2(4) NOT NULL,
  -- Conversion factor between an Each and the standard_uom when the standard_uom is not in the quantity class (e.g. if standard_uom = lb and 1 lb = 10 eaches, this factor will be 10).
  uom_conv_factor NUMBER(20,4),
  -- Holds the size of the product printed on any packaging (i.e. 24 ounces).
  package_size NUMBER(12,4),
  -- Holds the unit of measure associated with the package size.
  package_uom VARCHAR2(4),
  -- Merchandise shipped from the warehouses to the stores must be specified in this unit type. Valid values are: C = Cases I = Inner E = Eaches
  store_order_multiple VARCHAR2(1),
  -- Indicates if sales forecasts should be produced for this item.  Valid values are:Y, N.  Data is required in this field
  forecast_ind VARCHAR2(1) NOT NULL,
  -- The currency code of the for retail values.  This value is required.
  currency_code VARCHAR2(3) NOT NULL,
  -- The original retail price of the item per unit. This field is stored in the primary currency.
  original_unit_retail NUMBER(20,4),
  -- Manufacturers recommended retail price for the item. Used for informational purposes only. This field is stored in the primary currency.
  mfg_rec_retail NUMBER(20,4),
  -- This field indicates any special lable type assoctiated with an item (i.e. pre-priced or cents off).
  retail_label_type VARCHAR2(6),
  -- This field represents the value associated with the retail label type.
  retail_label_value NUMBER(20,4),
  -- This field holds an indicator of whether the retailer wants to aggregate inventory and sales for the item with the inventory and sales of other child items.  For staple items, this indicator is always set to N.  For fashion items, this indicator may be Y.  If this indicator is Y, the retailer may also define which diff positions should be aggregated into item/diff combinations.   Aggregated sales and inventory planning data can be used for inventory decision making and other reporting.  Data is required in this field
  item_aggregate_ind VARCHAR2(1) NOT NULL,
  -- This field holds an indicator (Y/N) of whether the retailer wants to aggregate inventory and sales for the item by the first Diff defined for the item (e.g Style/Color).  Aggregated sales and inventory planning data can be used for inventory decision making and other reporting.  Data is required in this field.
  diff_1_aggregate_ind VARCHAR2(1) NOT NULL,
  -- This field holds an indicator (Y/N) of whether the retailer wants to aggregate inventory and sales for the item by the second Diff defined for the item (e.g Style/Waist Size).  Aggregated sales and inventory planning data can be used for inventory decision making and other reporting.  Data is required in this field.
  diff_2_aggregate_ind VARCHAR2(1) NOT NULL,
  -- This field holds an indicator (Y/N) of whether the retailer wants to aggregate inventory and sales for the item by the third Diff defined for the item (e.g Style/Inseam).  Aggregated sales and inventory planning data can be used for inventory decision making and other reporting.  Data is required in this field.
  diff_3_aggregate_ind VARCHAR2(1) NOT NULL,
  -- This field holds an indicator (Y/N) of whether the retailer wants to aggregate inventory and sales for the item by the fourth Diff defined for the item (e.g Style/Pattern).  Aggregated sales and inventory planning data can be used for inventory decision making and other reporting.  Data is required in this field.
  diff_4_aggregate_ind VARCHAR2(1) NOT NULL,
  -- This field holds a code descriptor of the type of item number.  Valid values for this field include but are not limited to: ITEM  (Oracle Retail Item Number), UPC-A  (UCC12), UPC-AS  (UCC12 with Supplement), UPC-E  (UCC8), UPC-ES  (UCC8 with Supplement), EAN8   (EAN/UCC-8), EAN13  (EAN/UCC-13), EAN13S   (EAN/UCC-13 with Supplement), ISBN10 (ISBN-10), ISBN13 (ISBN-13), NDC  (NDC/NHRIC National Drug Code), PLU (PLU), VPLU (Variable Weight PLU), SSCC (SSCC Shipper Carton), UCC14 (EAN/UCC-14), MANL (Manually Created Item Number).  This information is required.
  item_number_type VARCHAR2(6) NOT NULL,
  -- This field will hold the format ID that corresponds to the items variable UPC. This value is only used for items with variable UPCs.
  format_id VARCHAR2(6),
  -- This column holds the prefix for variable weight UPCs.
  prefix NUMBER(2,0),
  -- Holds the temperature information associated with the item.
  rec_handling_temp VARCHAR2(6),
  -- Holds the description of the temperature.
  handling_temp_desc VARCHAR2(250),
  -- Holds the sensitivity information associated with the item.
  rec_handling_sens VARCHAR2(6),
  -- Holds the description of the sensitivity.
  handling_sensitivity_desc VARCHAR2(250),
  -- A grocery item attribute used to indicate whether an item is perishable or not.
  perishable_ind VARCHAR2(1),
  -- Identifies the wastage type as either sales or spoilage wastage.  Valid values are: SP = Spoilage SL = Sales Wastage is not applicable to pack items.
  waste_type VARCHAR2(6),
  -- Holds the description of the wastage type.
  waste_type_desc VARCHAR2(250),
  -- Average percent of wastage for the item over its shelf life. Used in inflating the retail price for wastage items.
  waste_pct VARCHAR2(6),
  -- Default daily wastage percent for spoilage type wastage items.
  default_waste_pct NUMBER(12,4),
  -- Indicates that the dimensions of the product are always the same, regardless of the supplier. If this field is Y, the dimensions for all suppliers will be defaulted to the primary supplier/primary country dimensions.  This field will always have data.
  constant_dim_ind VARCHAR2(1) NOT NULL,
  -- Indicates if pack item contains inner packs. Vendor packs will never contain inner packs and this field will be defaulted to N. This field will only be available if the item is a pack item. Valid values are: Y = Yes, this pack contains inner packs N = No, this pack does not contain inner packs.
  contains_inner_ind VARCHAR2(1) NOT NULL,
  -- Indicates if item may be sold as a unit. If it is Y then the item will have its own unique unit retail. If it is N then the item will not have a unit retail.  This field will always have data.
  sellable_ind VARCHAR2(1) NOT NULL,
  -- Indicates if item may be ordered as a unit.  Valid values are:Y = Yes, this item/pack may be ordered from a single supplier; N = No, this item/pack may not be ordered from a single supplier.  Non orderable items might be transformed or produced (in house bakery items) by the retailer.  Examples might include (but are not limited to): ground beef item is not orderable because the retailer orders a side of beef and transforms it into ground beef or in house bakery items are not orderable because the retailer produces them in house.  This field will always have data.
  orderable_ind VARCHAR2(1) NOT NULL,
  -- Indicates if pack item is a vendor pack or a buyer pack.
  pack_type VARCHAR2(1),
  -- Indicates if pack item is receivable at the component level or at the pack level (for a buyer pack only).
  order_as_type VARCHAR2(1),
  -- Holds a value that restricts the type of shipment methods that RCOM can select for an item.
  item_service_level VARCHAR2(6),
  -- This field will contain a value of Y if the item is eligible to be gift wrapped.  If not explicitly defined, this field will default to N.  This field will always have data records.
  gift_wrap_ind VARCHAR2(1) NOT NULL,
  -- This field will contain a value of Y if the item must be shipped alone to consumers.  If not explicitly defined, this field will default to N.  This field will always have data records.
  ship_alone_ind VARCHAR2(1) NOT NULL,
  -- This field will contain a value of Y if the item is associated with an item transformation. The item will be either the sellable item or orderable item in the transformation process.  Transformations include a side of beef (orderable transformation item) cut into steaks/ground beef etc (sellable transformation items). This field will always have data.
  item_form_ind VARCHAR2(1) NOT NULL,
  -- Indicates whether the item should be weighed when it arrives at a location. Valid values for this field are Y and N.  This field will always have data
  catch_weight_ind VARCHAR2(1) NOT NULL,
  -- This column will hold catch weight type for a simple pack catch weight item.
  catch_weight_type VARCHAR2(1),
  -- This determines how catch weight items are ordered. Valid values are: F - fixed weight V - Variable weight Valid values are held on the CODE_DETAIL table with a code type = ORDT
  catch_weight_order_type VARCHAR2(6),
  -- This indicates the method of how catch weight items are sold in store locations.
  catch_weight_sale_type VARCHAR2(6),
  -- UOM for Catchweight Items.
  catch_weight_uom VARCHAR2(4),
  -- This information determines how to pack customer orders : such as to determine products that may not be able to be packaged together. Classifications are set up as codes in RMS. Examples of classifications may include fragile, toxic, heavy, etc.
  product_classification VARCHAR2(6),
  -- Holds the description of the product classification.
  prod_classification_desc VARCHAR2(250),
  -- This is the deposit item component type. A NULL value in this field indicates that this item is not part of a deposit item relationship.
  deposit_item_type VARCHAR2(6),
  -- This holds the container item number for a contents item. This field is only populated and required if the DEPOSIT_ITEM_TYPE = E.
  container_item VARCHAR2(25),
  -- This field indicates if the deposit amount is included in the price per UOM calculation for a contents item ticket.
  deposit_in_price_per_uom VARCHAR2(6),
  -- This field indicates if stock on hand inquiries from downstream systems should be allowed at the pack level for this item.  It will default to N for non pack items.  This field will always have data.
  soh_inquiry_at_pack_ind VARCHAR2(1) NOT NULL,
  -- If this indicator is Y, SIM and other downstream systems should track pack inventory at the pack level. If the indicator is N, SIM and other downstream systems will track inventory at the component level.  It will default to N for non pack items.  This field will always have data.
  notional_pack_ind VARCHAR2(1) NOT NULL,
  -- Holds any comments associated with the item.
  comments VARCHAR2(2000)
);

COMMENT ON TABLE ITEM_HDR_OUT IS 'This table is used to integrate approved items.  This data includes all nodes of item families (e.g. styles/above tran level items, UPCs/below tran level items).  This information also includes packs (collections of items), non-sellable items, non-physical items (downloads, warrenties, etc) and other special putpose items.';
COMMENT ON COLUMN ITEM_HDR_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN ITEM_HDR_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN ITEM_HDR_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN ITEM_HDR_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN ITEM_HDR_OUT.item IS 'ID of item.  Data will always be present in this field.';
COMMENT ON COLUMN ITEM_HDR_OUT.item_parent IS 'ID identifies the item/group at the level above the item. This value must exist as an item in another row on the item_master table.';
COMMENT ON COLUMN ITEM_HDR_OUT.item_grandparent IS 'identifies the item/group two levels above the item. This value must exist as both an item and an item parent in another row on the item_master table.';
COMMENT ON COLUMN ITEM_HDR_OUT.pack_ind IS 'This field indicates if the Item is a pack.  A pack is a type of Item that acts as a collection of other Items.  Packs can either be sold together (e.g. pack goumey chef gift basket consisting of: 1 bottle organic olive oil, 1 bottle truffle oil, 1lb organic coffee, 1 box speciality herbal tea, ...) and/or ordered together (kelly sun 2015 holiday pajama assortment is pack that consists of 8 sets of girls pajamas, each of which has a different design; these items are bought as a pack but sold as units).  Packs will have a Y value. Regular items will have a N value.';
COMMENT ON COLUMN ITEM_HDR_OUT.simple_pack_ind IS 'This field indicates if the pack item is a simple pack.  A simple pack is a collection that consists of multiple of the same item (e.g. it is possible to buy either one unit of a granola bar, or the display box of 20 granola bars - the display box is a pack item itself with a unique UPC; often the full box has a slightly different price than the individual items * qty).  Only simple packs will have a Y value.  Both complex packs (which consist of a mixture of different items) and regular items will have a N value.';
COMMENT ON COLUMN ITEM_HDR_OUT.item_level IS 'Number indicating which of the three levels the item resides. The item level determines if the item stands alone or if it is part of a family of related items.  The concept is best explained with typical (although not exhaustive) examples.  Staple items generally have a item level = 1 UPCs for Staple items generally have an item level = 2 (and the staple item will be the UPCs parent item).  Fashion styles generally have an item level = 1.  Fashion skus generally have an item level = 2.  If UPCs for fashion skus generally have an item level = 3.  Valid values are 1, 2 and 3.  This field will always have data.';
COMMENT ON COLUMN ITEM_HDR_OUT.tran_level IS 'Number indicating which of the three levels transactions occur for the items group. The transaction level is the level at which the items inventory is tracked in the system. The transaction level item will be inventoried, counted, transferred, shipped, etc.  Only one level of the hierarchy of an item family may contain transaction level items.  The concept is best explained with typical (although not exhaustive) examples.  Staple items generally have a TranLevel = 1.  UPCs for Staple items generally have an TranLevel = 1 (inventory txns occur at the staple sku level; sales of the item roll up to the parent staple sku).  Fashion styles generally have a TranLevel = 2 (the style itself is not sold/inventoried).  Fashion skus generally have an TranLevel = 2 (the fashion sku is sold/inventoried).  If UPCs for fashion skus generally have an TranLevel = 2 (the fashion sku is sold/inventoried).  There are some rare cases in vendor managed inventory where the TranLevel = 3.  Valid values are 1, 2 and 3.   This field will always have data.';
COMMENT ON COLUMN ITEM_HDR_OUT.inventory_ind IS 'This indicator is used to determine if inventory is held for the item/item family.  Inventory is held for most items.  However, inventory is not held (value = N) in some special cases, such as: Concession items (items that are sold by independent in location concessions),  Consignment items (items are are not owned by the retailer; financial and inventory processing occurs after the item is sold to a consumer), Containers sold then returned for deposit and some items that are transformed for sale.  Valid values are Y and N. This field will always have data.';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_1_level IS 'This field will contain either ID or GROUP, based on whether the diff_1 is a group diff or a diff id.';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_1_type IS 'This field will hold a value of the types of differentiators contained in this differentiator group, such as S - size, C - color, F - flavor, E - scent, P - pattern. Valid values are stored in the DIFF_TYPE table.';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_1 IS 'Diff_group or diff_id that differentiates the current item from its item_parent. For an item that is a parent, this field may be either a group (i.e. Mens pant sizes) or a value (6 oz).';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_1_desc IS 'This field will hold the description of this differentiator.';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_2_level IS 'This field will contain either ID or GROUP, based on whether the diff_2 is a group diff or a diff id.';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_2_type IS 'This field will hold a value of the types of differentiators contained in this differentiator group, such as S - size, C - color, F - flavor, E - scent, P - pattern. Valid values are stored in the DIFF_TYPE table.';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_2 IS 'Diff_group or diff_id that differentiates the current item from its item_parent. For an item that is a parent, this field may be either a group (i.e. Mens pant sizes) or a value (6 oz).';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_2_desc IS 'This field will hold the description of this differentiator.';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_3_level IS 'This field will contain either ID or GROUP, based on whether the diff_3 is a group diff or a diff id.';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_3_type IS 'This field will hold a value of the types of differentiators contained in this differentiator group, such as S - size, C - color, F - flavor, E - scent, P - pattern. Valid values are stored in the DIFF_TYPE table.';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_3 IS 'Diff_group or diff_id that differentiates the current item from its item_parent. For an item that is a parent, this field may be either a group (i.e. Mens pant sizes) or a value (6 oz).';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_3_desc IS 'This field will hold the description of this differentiator.';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_4_level IS 'This field will contain either ID or GROUP, based on whether the diff_4 is a group diff or a diff id.';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_4_type IS 'This field will hold a value of the types of differentiators contained in this differentiator group, such as S - size, C - color, F - flavor, E - scent, P - pattern. Valid values are stored in the DIFF_TYPE table.';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_4 IS 'Diff_group or diff_id that differentiates the current item from its item_parent. For an item that is a parent, this field may be either a group (i.e. Mens pant sizes) or a value (6 oz).';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_4_desc IS 'This field will hold the description of this differentiator.';
COMMENT ON COLUMN ITEM_HDR_OUT.dept IS 'Number identifying the department in the merchandise hierarchy to which the item belongs.  If the item has a parent, the parent and item will belong to the same department.  This data is required';
COMMENT ON COLUMN ITEM_HDR_OUT.unique_class IS 'Number uniquely identifying the class node which the item belongs to.  Class is not unique ID the merchandise hierarchy.  The combination of Dept/Class is unique, but requires use of a composite key.  The composite key is generally used in user interfaces.  The unique ID can be used in back end processing or in systems that can not have a composite key for a node in the merchandise hierarchy.';
COMMENT ON COLUMN ITEM_HDR_OUT.class IS 'Number identifying the class in the merchandise hierarchy to which the item belongs.  If the item has a parent, the parent and item will belong to the same department and class.  Class is not a unique ID (e.g. every department can have a class 1).  This data is required.';
COMMENT ON COLUMN ITEM_HDR_OUT.unique_subclass IS 'Number uniquely identifying the subclass node which the item belongs to.  Subclass is not unique ID  the merchandise hierarchy.  The combination of Dept/Class/Subclass is unique, but requires use of a composite key.  The composite key is generally used in user interfaces.  The unique ID can be used in back end processing or in systems that can not have a composite key for a node in the merchandise hierarchy.';
COMMENT ON COLUMN ITEM_HDR_OUT.subclass IS 'Number identifying the subclass in the merchandise hierarchy to which the item belongs.  If the item has a parent, the parent and item will belong to the same department, class and subclass.  Subclass is not a unique ID (e.g. every department/class can have a subclass 1).  This data is required.';
COMMENT ON COLUMN ITEM_HDR_OUT.status IS 'Item life cycle status of the item.  At this time, this field will always contain the character A as only approved items are exported.';
COMMENT ON COLUMN ITEM_HDR_OUT.description IS 'Primary description of the item in the integration langauge of the system.  This value is required.';
COMMENT ON COLUMN ITEM_HDR_OUT.secondary_item_desc IS 'Secondary descriptions of the item. This field can only be populated when system_options.secondary_desc_ind = Y.';
COMMENT ON COLUMN ITEM_HDR_OUT.short_desc IS 'Shortened item description.  This value is required.';
COMMENT ON COLUMN ITEM_HDR_OUT.desc_up IS 'Description of the item in upper case.';
COMMENT ON COLUMN ITEM_HDR_OUT.brand_name IS 'This field contains the brand associated to an item.';
COMMENT ON COLUMN ITEM_HDR_OUT.brand_description IS 'This field contains the description of the brand associated to an item.';
COMMENT ON COLUMN ITEM_HDR_OUT.merchandise_ind IS 'Indicates if the item is a merchandise item (Y, N).  Merchandise items are generally physical items (things that must be shipped/received and of which there is an inventory).  Non merchandise items are often items which do not have inventory.  Common examples include extra fees for service (extended warranties, alterations) or endlessly available items (downloads, in app purchases of bonus content, subscriptions).  All items, both merchandise and non-merchandise are exported from RMS.  This value is required.';
COMMENT ON COLUMN ITEM_HDR_OUT.primary_ref_item_ind IS 'Indicates if the sub-transation level item is designated as the primary sub-transaction level item.  For transaction level items and above the value in this field will be N.  For sub transaction level items, this field may be either Y (if the current record is a primary UPC) or N (if the current record is not the primary UPC).';
COMMENT ON COLUMN ITEM_HDR_OUT.cost_zone_group_id IS 'Cost zone group associated with the item. This field is only required when elc_ind (landed cost indicator) is set to Y on the system_options table.';
COMMENT ON COLUMN ITEM_HDR_OUT.standard_uom IS 'Unit of measure in which stock and or financials of the item is tracked at a corporate level.  Unit of measure reference values may have to be manually synced between the systems as this is foundation data that is not currently bulk integrated out of Merch. This value is required.';
COMMENT ON COLUMN ITEM_HDR_OUT.uom_conv_factor IS 'Conversion factor between an Each and the standard_uom when the standard_uom is not in the quantity class (e.g. if standard_uom = lb and 1 lb = 10 eaches, this factor will be 10).';
COMMENT ON COLUMN ITEM_HDR_OUT.package_size IS 'Holds the size of the product printed on any packaging (i.e. 24 ounces).';
COMMENT ON COLUMN ITEM_HDR_OUT.package_uom IS 'Holds the unit of measure associated with the package size.';
COMMENT ON COLUMN ITEM_HDR_OUT.store_order_multiple IS 'Merchandise shipped from the warehouses to the stores must be specified in this unit type. Valid values are: C = Cases I = Inner E = Eaches';
COMMENT ON COLUMN ITEM_HDR_OUT.forecast_ind IS 'Indicates if sales forecasts should be produced for this item.  Valid values are:Y, N.  Data is required in this field';
COMMENT ON COLUMN ITEM_HDR_OUT.currency_code IS 'The currency code of the for retail values.  This value is required.';
COMMENT ON COLUMN ITEM_HDR_OUT.original_unit_retail IS 'The original retail price of the item per unit. This field is stored in the primary currency.';
COMMENT ON COLUMN ITEM_HDR_OUT.mfg_rec_retail IS 'Manufacturers recommended retail price for the item. Used for informational purposes only. This field is stored in the primary currency.';
COMMENT ON COLUMN ITEM_HDR_OUT.retail_label_type IS 'This field indicates any special lable type assoctiated with an item (i.e. pre-priced or cents off).';
COMMENT ON COLUMN ITEM_HDR_OUT.retail_label_value IS 'This field represents the value associated with the retail label type.';
COMMENT ON COLUMN ITEM_HDR_OUT.item_aggregate_ind IS 'This field holds an indicator of whether the retailer wants to aggregate inventory and sales for the item with the inventory and sales of other child items.  For staple items, this indicator is always set to N.  For fashion items, this indicator may be Y.  If this indicator is Y, the retailer may also define which diff positions should be aggregated into item/diff combinations.   Aggregated sales and inventory planning data can be used for inventory decision making and other reporting.  Data is required in this field';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_1_aggregate_ind IS 'This field holds an indicator (Y/N) of whether the retailer wants to aggregate inventory and sales for the item by the first Diff defined for the item (e.g Style/Color).  Aggregated sales and inventory planning data can be used for inventory decision making and other reporting.  Data is required in this field.';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_2_aggregate_ind IS 'This field holds an indicator (Y/N) of whether the retailer wants to aggregate inventory and sales for the item by the second Diff defined for the item (e.g Style/Waist Size).  Aggregated sales and inventory planning data can be used for inventory decision making and other reporting.  Data is required in this field.';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_3_aggregate_ind IS 'This field holds an indicator (Y/N) of whether the retailer wants to aggregate inventory and sales for the item by the third Diff defined for the item (e.g Style/Inseam).  Aggregated sales and inventory planning data can be used for inventory decision making and other reporting.  Data is required in this field.';
COMMENT ON COLUMN ITEM_HDR_OUT.diff_4_aggregate_ind IS 'This field holds an indicator (Y/N) of whether the retailer wants to aggregate inventory and sales for the item by the fourth Diff defined for the item (e.g Style/Pattern).  Aggregated sales and inventory planning data can be used for inventory decision making and other reporting.  Data is required in this field.';
COMMENT ON COLUMN ITEM_HDR_OUT.item_number_type IS 'This field holds a code descriptor of the type of item number.  Valid values for this field include but are not limited to: ITEM  (Oracle Retail Item Number), UPC-A  (UCC12), UPC-AS  (UCC12 with Supplement), UPC-E  (UCC8), UPC-ES  (UCC8 with Supplement), EAN8   (EAN/UCC-8), EAN13  (EAN/UCC-13), EAN13S   (EAN/UCC-13 with Supplement), ISBN10 (ISBN-10), ISBN13 (ISBN-13), NDC  (NDC/NHRIC National Drug Code), PLU (PLU), VPLU (Variable Weight PLU), SSCC (SSCC Shipper Carton), UCC14 (EAN/UCC-14), MANL (Manually Created Item Number).  This information is required.';
COMMENT ON COLUMN ITEM_HDR_OUT.format_id IS 'This field will hold the format ID that corresponds to the items variable UPC. This value is only used for items with variable UPCs.';
COMMENT ON COLUMN ITEM_HDR_OUT.prefix IS 'This column holds the prefix for variable weight UPCs.';
COMMENT ON COLUMN ITEM_HDR_OUT.rec_handling_temp IS 'Holds the temperature information associated with the item.';
COMMENT ON COLUMN ITEM_HDR_OUT.handling_temp_desc IS 'Holds the description of the temperature.';
COMMENT ON COLUMN ITEM_HDR_OUT.rec_handling_sens IS 'Holds the sensitivity information associated with the item.';
COMMENT ON COLUMN ITEM_HDR_OUT.handling_sensitivity_desc IS 'Holds the description of the sensitivity.';
COMMENT ON COLUMN ITEM_HDR_OUT.perishable_ind IS 'A grocery item attribute used to indicate whether an item is perishable or not.';
COMMENT ON COLUMN ITEM_HDR_OUT.waste_type IS 'Identifies the wastage type as either sales or spoilage wastage.  Valid values are: SP = Spoilage SL = Sales Wastage is not applicable to pack items.';
COMMENT ON COLUMN ITEM_HDR_OUT.waste_type_desc IS 'Holds the description of the wastage type.';
COMMENT ON COLUMN ITEM_HDR_OUT.waste_pct IS 'Average percent of wastage for the item over its shelf life. Used in inflating the retail price for wastage items.';
COMMENT ON COLUMN ITEM_HDR_OUT.default_waste_pct IS 'Default daily wastage percent for spoilage type wastage items.';
COMMENT ON COLUMN ITEM_HDR_OUT.constant_dim_ind IS 'Indicates that the dimensions of the product are always the same, regardless of the supplier. If this field is Y, the dimensions for all suppliers will be defaulted to the primary supplier/primary country dimensions.  This field will always have data.';
COMMENT ON COLUMN ITEM_HDR_OUT.contains_inner_ind IS 'Indicates if pack item contains inner packs. Vendor packs will never contain inner packs and this field will be defaulted to N. This field will only be available if the item is a pack item. Valid values are: Y = Yes, this pack contains inner packs N = No, this pack does not contain inner packs.';
COMMENT ON COLUMN ITEM_HDR_OUT.sellable_ind IS 'Indicates if item may be sold as a unit. If it is Y then the item will have its own unique unit retail. If it is N then the item will not have a unit retail.  This field will always have data.';
COMMENT ON COLUMN ITEM_HDR_OUT.orderable_ind IS 'Indicates if item may be ordered as a unit.  Valid values are:Y = Yes, this item/pack may be ordered from a single supplier; N = No, this item/pack may not be ordered from a single supplier.  Non orderable items might be transformed or produced (in house bakery items) by the retailer.  Examples might include (but are not limited to): ground beef item is not orderable because the retailer orders a side of beef and transforms it into ground beef or in house bakery items are not orderable because the retailer produces them in house.  This field will always have data.';
COMMENT ON COLUMN ITEM_HDR_OUT.pack_type IS 'Indicates if pack item is a vendor pack or a buyer pack.';
COMMENT ON COLUMN ITEM_HDR_OUT.order_as_type IS 'Indicates if pack item is receivable at the component level or at the pack level (for a buyer pack only).';
COMMENT ON COLUMN ITEM_HDR_OUT.item_service_level IS 'Holds a value that restricts the type of shipment methods that RCOM can select for an item.';
COMMENT ON COLUMN ITEM_HDR_OUT.gift_wrap_ind IS 'This field will contain a value of Y if the item is eligible to be gift wrapped.  If not explicitly defined, this field will default to N.  This field will always have data records.';
COMMENT ON COLUMN ITEM_HDR_OUT.ship_alone_ind IS 'This field will contain a value of Y if the item must be shipped alone to consumers.  If not explicitly defined, this field will default to N.  This field will always have data records.';
COMMENT ON COLUMN ITEM_HDR_OUT.item_form_ind IS 'This field will contain a value of Y if the item is associated with an item transformation. The item will be either the sellable item or orderable item in the transformation process.  Transformations include a side of beef (orderable transformation item) cut into steaks/ground beef etc (sellable transformation items). This field will always have data.';
COMMENT ON COLUMN ITEM_HDR_OUT.catch_weight_ind IS 'Indicates whether the item should be weighed when it arrives at a location. Valid values for this field are Y and N.  This field will always have data';
COMMENT ON COLUMN ITEM_HDR_OUT.catch_weight_type IS 'This column will hold catch weight type for a simple pack catch weight item.';
COMMENT ON COLUMN ITEM_HDR_OUT.catch_weight_order_type IS 'This determines how catch weight items are ordered. Valid values are: F - fixed weight V - Variable weight Valid values are held on the CODE_DETAIL table with a code type = ORDT';
COMMENT ON COLUMN ITEM_HDR_OUT.catch_weight_sale_type IS 'This indicates the method of how catch weight items are sold in store locations.';
COMMENT ON COLUMN ITEM_HDR_OUT.catch_weight_uom IS 'UOM for Catchweight Items.';
COMMENT ON COLUMN ITEM_HDR_OUT.product_classification IS 'This information determines how to pack customer orders : such as to determine products that may not be able to be packaged together. Classifications are set up as codes in RMS. Examples of classifications may include fragile, toxic, heavy, etc.';
COMMENT ON COLUMN ITEM_HDR_OUT.prod_classification_desc IS 'Holds the description of the product classification.';
COMMENT ON COLUMN ITEM_HDR_OUT.deposit_item_type IS 'This is the deposit item component type. A NULL value in this field indicates that this item is not part of a deposit item relationship.';
COMMENT ON COLUMN ITEM_HDR_OUT.container_item IS 'This holds the container item number for a contents item. This field is only populated and required if the DEPOSIT_ITEM_TYPE = E.';
COMMENT ON COLUMN ITEM_HDR_OUT.deposit_in_price_per_uom IS 'This field indicates if the deposit amount is included in the price per UOM calculation for a contents item ticket.';
COMMENT ON COLUMN ITEM_HDR_OUT.soh_inquiry_at_pack_ind IS 'This field indicates if stock on hand inquiries from downstream systems should be allowed at the pack level for this item.  It will default to N for non pack items.  This field will always have data.';
COMMENT ON COLUMN ITEM_HDR_OUT.notional_pack_ind IS 'If this indicator is Y, SIM and other downstream systems should track pack inventory at the pack level. If the indicator is N, SIM and other downstream systems will track inventory at the component level.  It will default to N for non pack items.  This field will always have data.';
COMMENT ON COLUMN ITEM_HDR_OUT.comments IS 'Holds any comments associated with the item.';


-- Add BDI primary key constraint

ALTER TABLE ITEM_HDR_OUT ADD CONSTRAINT pk_item_hdr_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE ITEM_HDR_OUT ADD CONSTRAINT chk_type_item_hdr_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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