-- Interface Module: ItSupCtry_Fnd
-- Interface: Item_Supp_Country_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE item_supp_country_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


-- Create BDI Outbound Table: ITEM_SUPP_COUNTRY_OUT
-- This table is used to integrate Item Supplier Country information.

CREATE TABLE ITEM_SUPP_COUNTRY_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT item_supp_country_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 value that identifies the item.
  item VARCHAR2(25) NOT NULL,
  -- The unique identifier for the supplier.
  supplier NUMBER(10,0) NOT NULL,
  -- The country where the item was manufactured or significantly altered.
  origin_country_id VARCHAR2(3) NOT NULL,
  -- This field contains the current corporate unit cost for the SKU from the supplier/origin country.  This field is stored in the suppliers currency.  This value will match the cost field for the primary location on item_supp_country_loc.  This field may be edited while the item is in worksheet status.  If edited, the user will have the choice of changing the cost of all locations, or only the primary location.  This cost is the cost that will be written to item_supp_country_loc when new locations are added to an item.
  unit_cost NUMBER(20,4) NOT NULL,
  -- 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.  This field is defaulted from the default lead time set at the supplier level.
  lead_time NUMBER(4,0),
  -- Contains time it takes to get the item from the Supplier to the Initial Receiving Location.  This value will be defaulted to the item_supp_country_loc pickup lead time field.  The ordering dialog will reference the item/supplier/country/location pickup lead time as the value may vary by location.
  pickup_lead_time NUMBER(4,0),
  -- This field contains the quantity that orders must be placed in multiples of for the supplier for the item.
  supp_pack_size NUMBER(12,4) NOT NULL,
  -- This field contains the units of an item contained in an inner pack supplied by the supplier.
  inner_pack_size NUMBER(12,4) NOT NULL,
  -- This column will be used to determine how order quantities will be rounded to Case, Layer and Pallet.
  round_lvl VARCHAR2(6) NOT NULL,
  -- This column will hold the Inner Rounding Threshold value.  During rounding, this value is used to determine whether to round partial Inner quantities up or down.  If the Inner-fraction in question is less than the Threshold proportion, it is rounded down; if not, it is rounded up.  For instance, with an Inner size of 10 and a Threshold of 80%, Inner quantities such as 18, 29 and 8 would be rounded up to 20, 30 and 10 respectively, while quantities of 12, 27 and 35 would be rounded down to 10, 20 and 30 respectively.  Quantities are never rounded down to zero; a quantity of 7, in the example above, would be rounded up to 10.  This column will be maintained simply for the purpose of defaulting to the Item/Supplier/Country/Location level.
  round_to_inner_pct NUMBER(12,4) NOT NULL,
  -- This column will hold the Case Rounding Threshold value.  During rounding, this value is used to determine whether to round partial Case quantities up or down.  If the Case-fraction in question is less than the Threshold proportion, it is rounded down; if not, it is rounded up.  For instance, with an Case size of 10 and a Threshold of 80%, Case quantities such as 18, 29 and 8 would be rounded up to 20, 30 and 10 respectively, while quantities of 12, 27 and 35 would be rounded down to 10, 20 and 30 respectively.  Quantities are never rounded down to zero; a quantity of 7, in the example above, would be rounded up to 10.  This column will be maintained simply for the purpose of defaulting to the Item/Supplier/Country/Location level
  round_to_case_pct NUMBER(12,4) NOT NULL,
  -- This column will hold the Layer Rounding Threshold value.  During rounding, this value is used to determine whether to round partial Layer quantities up or down.  If the Layer-fraction in question is less than the Threshold proportion, it is rounded down; if not, it is rounded up.
  round_to_layer_pct NUMBER(12,4) NOT NULL,
  -- This column will hold the Pallet Rounding Threshold value. During rounding, this value is used to determine whether to round partial Pallet quantities up or down.  If the Pallet -fraction in question is less than the Threshold proportion, it is rounded down; if not, it is rounded up.  For instance, with an Pallet size of 10 and a Threshold of 80%, Pallet quantities such as 18, 29 and 8 would be rounded up to 20, 30 and 10 respectively, while quantities of 12, 27 and 35 would be rounded down to 10, 20 and 30 respectively.  Quantities are never rounded down to zero; a quantity of 7, in the example above, would be rounded up to 10.  This column will be maintained simply for the purpose of defaulting to the Item/Supplier/Country/Location level.
  round_to_pallet_pct NUMBER(12,4) NOT NULL,
  -- This field contains the minimum quantity that can be ordered at one time from the supplier for the item.
  min_order_qty NUMBER(12,4),
  -- This field contains the maximum quantity that can be ordered at one time from the supplier for the item.
  max_order_qty NUMBER(12,4),
  -- This field indicates whether the packing method of the item in the container is Flat or Hanging.  Values for this field are store in the PKMT code.
  packing_method VARCHAR2(6),
  -- This field indicates whether this supplier is the primary supplier for the Item.  Each Item can have one and only one primary supplier.  This field is stored on this table for performance purposes only.  Valid values are Y or N.
  primary_supp_ind VARCHAR2(1) NOT NULL,
  -- This field indicates whether this country is the primary country for the item/supplier.  Each item/supplier combination must have one and only one primary country.  Valid values are Y or N.
  primary_country_ind VARCHAR2(1) NOT NULL,
  -- Contains the default unit of purchase for the item/supplier/country.  Valid values include: Standard Units of Measure C for Case  P for Pallet
  default_uop VARCHAR2(6),
  -- 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) NOT NULL,
  -- 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) NOT NULL,
  -- Identifies partner type of supplier hierarchy level 1.This field will always have the partner type S1 which indicates manufacturer.
  supp_hier_type_1 VARCHAR2(6),
  -- Highest level of supplier hierarchy which is uder to indicate a partner, such as a manufacturer, in the supply chain that gives rebates to the retailer. This information is stored on item_supp_country for defaulting into item_supp_country_loc.
  supp_hier_lvl_1 VARCHAR2(10),
  -- Identifies partner type of supplier hierarchy level 2 .  This field will always have the partner type S2 which indicates distributor.
  supp_hier_type_2 VARCHAR2(6),
  -- Second highest level of supplier hierarchy  which is uder to indicate a partner, such as a distributor in the supply chain that gives rebates to the retailer. This information is stored on item_supp_country for defaulting into item_supp_country_loc.
  supp_hier_lvl_2 VARCHAR2(10),
  -- Identifies partner type of supplier hierarchy level 3.   This field will always have the partner type S3 which indicates wholesaler.
  supp_hier_type_3 VARCHAR2(6),
  -- Third highest level of supplier hierarchy which is uder to indicate a partner, such as a wholesaler in the supply chain that gives rebates to the retailer.  This information is stored on item_supp_country for defaulting into item_supp_country_loc.
  supp_hier_lvl_3 VARCHAR2(10),
  -- Date/time stamp of when the record was created.  This date/time will be used in export processing.  This value should only be populated on insert  - it should never be updated.
  create_datetime TIMESTAMP NOT NULL,
  -- Holds the date time stamp of the most recent update by the last_update_id.  This field is required by the database.
  last_update_datetime TIMESTAMP NOT NULL,
  -- Holds the Oracle user-id of the user who most recently updated this record.  This field is required by the database.
  last_update_id VARCHAR2(30) NOT NULL,
  -- A cost UOM is held to allow costs to be managed in a separate UOM to the standard UOM.
  cost_uom VARCHAR2(4) NOT NULL,
  -- The unit of the tolerances for catch weight simple packs. Valid values are:  A - actual P - percent
  tolerance_type VARCHAR2(6),
  -- The maximum tolerance value for the catch weight simple pack.
  max_tolerance NUMBER(12,4),
  -- The minimum tolerance value for a catch weight simple pack.
  min_tolerance NUMBER(12,4),
  -- This will hold the supplier negotiated item cost for the primary delivery country of the item. Once a location is associated with the item, the primary locations negotiated item cost will be stored in this field.
  negotiated_item_cost NUMBER(20,4),
  -- This will hold the extended base cost for the primary delivery country of the item. Once a location is associated with the item, the primary locations extended base cost will be stored in this field. Extended base cost is the cost inclusive of all the taxes that affect the WAC. In case of GTAX , Extended Base Cost = Base Cost + Non-recoverable taxes. In case of VAT, Extended Base Cost = Base Cost.
  extended_base_cost NUMBER(20,4),
  -- This will hold the inclusive cost for the primary delivery country of the item. Once a location is associated with the item, the primary locations inclusive cost will be stored in this field. This cost will have both the recoverable and non recoverable taxes included. In case of GTAX , Inclusive Cost = Base Cost + Non-recoverable taxes + Recoverable Taxes. In case of VAT,  Inclusive Cost = Base Cost + VAT
  inclusive_cost NUMBER(20,4),
  -- This field will hold the tax exclusive cost of the item.
  base_cost NUMBER(20,4),
  -- Contains a code to indicate whether the item is normal merchandise (i.e. owned by the retailer), consignment stock or a concession item. Valid values are 0 - Owned, 1 - Consignment, 2 - Concession.
  purchase_type NUMBER(1,0),
  -- This field will indicate if the cost for the consignment/concession item will be managed either based on cost per unit or as a percentage of retail. Valid values are C - Cost per Unit, P - Purchase Rate.
  calculation_basis VARCHAR2(1),
  -- Contains the percentage of the retail price which will determine the cost paid to the supplier for a consignment or concession item.
  purchase_rate NUMBER(12,4)
);

COMMENT ON TABLE ITEM_SUPP_COUNTRY_OUT IS 'This table is used to integrate Item Supplier Country information.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.item IS 'Alphanumeric value that identifies the item.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.supplier IS 'The unique identifier for the supplier.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.origin_country_id IS 'The country where the item was manufactured or significantly altered.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.unit_cost IS 'This field contains the current corporate unit cost for the SKU from the supplier/origin country.  This field is stored in the suppliers currency.  This value will match the cost field for the primary location on item_supp_country_loc.  This field may be edited while the item is in worksheet status.  If edited, the user will have the choice of changing the cost of all locations, or only the primary location.  This cost is the cost that will be written to item_supp_country_loc when new locations are added to an item.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.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.  This field is defaulted from the default lead time set at the supplier level.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.pickup_lead_time IS 'Contains time it takes to get the item from the Supplier to the Initial Receiving Location.  This value will be defaulted to the item_supp_country_loc pickup lead time field.  The ordering dialog will reference the item/supplier/country/location pickup lead time as the value may vary by location.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.supp_pack_size IS 'This field contains the quantity that orders must be placed in multiples of for the supplier for the item.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.inner_pack_size IS 'This field contains the units of an item contained in an inner pack supplied by the supplier.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.round_lvl IS 'This column will be used to determine how order quantities will be rounded to Case, Layer and Pallet.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.round_to_inner_pct IS 'This column will hold the Inner Rounding Threshold value.  During rounding, this value is used to determine whether to round partial Inner quantities up or down.  If the Inner-fraction in question is less than the Threshold proportion, it is rounded down; if not, it is rounded up.  For instance, with an Inner size of 10 and a Threshold of 80%, Inner quantities such as 18, 29 and 8 would be rounded up to 20, 30 and 10 respectively, while quantities of 12, 27 and 35 would be rounded down to 10, 20 and 30 respectively.  Quantities are never rounded down to zero; a quantity of 7, in the example above, would be rounded up to 10.  This column will be maintained simply for the purpose of defaulting to the Item/Supplier/Country/Location level.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.round_to_case_pct IS 'This column will hold the Case Rounding Threshold value.  During rounding, this value is used to determine whether to round partial Case quantities up or down.  If the Case-fraction in question is less than the Threshold proportion, it is rounded down; if not, it is rounded up.  For instance, with an Case size of 10 and a Threshold of 80%, Case quantities such as 18, 29 and 8 would be rounded up to 20, 30 and 10 respectively, while quantities of 12, 27 and 35 would be rounded down to 10, 20 and 30 respectively.  Quantities are never rounded down to zero; a quantity of 7, in the example above, would be rounded up to 10.  This column will be maintained simply for the purpose of defaulting to the Item/Supplier/Country/Location level';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.round_to_layer_pct IS 'This column will hold the Layer Rounding Threshold value.  During rounding, this value is used to determine whether to round partial Layer quantities up or down.  If the Layer-fraction in question is less than the Threshold proportion, it is rounded down; if not, it is rounded up.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.round_to_pallet_pct IS 'This column will hold the Pallet Rounding Threshold value. During rounding, this value is used to determine whether to round partial Pallet quantities up or down.  If the Pallet -fraction in question is less than the Threshold proportion, it is rounded down; if not, it is rounded up.  For instance, with an Pallet size of 10 and a Threshold of 80%, Pallet quantities such as 18, 29 and 8 would be rounded up to 20, 30 and 10 respectively, while quantities of 12, 27 and 35 would be rounded down to 10, 20 and 30 respectively.  Quantities are never rounded down to zero; a quantity of 7, in the example above, would be rounded up to 10.  This column will be maintained simply for the purpose of defaulting to the Item/Supplier/Country/Location level.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.min_order_qty IS 'This field contains the minimum quantity that can be ordered at one time from the supplier for the item.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.max_order_qty IS 'This field contains the maximum quantity that can be ordered at one time from the supplier for the item.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.packing_method IS 'This field indicates whether the packing method of the item in the container is Flat or Hanging.  Values for this field are store in the PKMT code.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.primary_supp_ind IS 'This field indicates whether this supplier is the primary supplier for the Item.  Each Item can have one and only one primary supplier.  This field is stored on this table for performance purposes only.  Valid values are Y or N.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.primary_country_ind IS 'This field indicates whether this country is the primary country for the item/supplier.  Each item/supplier combination must have one and only one primary country.  Valid values are Y or N.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.default_uop IS 'Contains the default unit of purchase for the item/supplier/country.  Valid values include: Standard Units of Measure C for Case  P for Pallet';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.ti IS '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 ITEM_SUPP_COUNTRY_OUT.hi IS '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 ITEM_SUPP_COUNTRY_OUT.supp_hier_type_1 IS 'Identifies partner type of supplier hierarchy level 1.This field will always have the partner type S1 which indicates manufacturer.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.supp_hier_lvl_1 IS 'Highest level of supplier hierarchy which is uder to indicate a partner, such as a manufacturer, in the supply chain that gives rebates to the retailer. This information is stored on item_supp_country for defaulting into item_supp_country_loc.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.supp_hier_type_2 IS 'Identifies partner type of supplier hierarchy level 2 .  This field will always have the partner type S2 which indicates distributor.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.supp_hier_lvl_2 IS 'Second highest level of supplier hierarchy  which is uder to indicate a partner, such as a distributor in the supply chain that gives rebates to the retailer. This information is stored on item_supp_country for defaulting into item_supp_country_loc.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.supp_hier_type_3 IS 'Identifies partner type of supplier hierarchy level 3.   This field will always have the partner type S3 which indicates wholesaler.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.supp_hier_lvl_3 IS 'Third highest level of supplier hierarchy which is uder to indicate a partner, such as a wholesaler in the supply chain that gives rebates to the retailer.  This information is stored on item_supp_country for defaulting into item_supp_country_loc.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.create_datetime IS 'Date/time stamp of when the record was created.  This date/time will be used in export processing.  This value should only be populated on insert  - it should never be updated.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.last_update_datetime IS 'Holds the date time stamp of the most recent update by the last_update_id.  This field is required by the database.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.last_update_id IS 'Holds the Oracle user-id of the user who most recently updated this record.  This field is required by the database.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.cost_uom IS 'A cost UOM is held to allow costs to be managed in a separate UOM to the standard UOM.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.tolerance_type IS 'The unit of the tolerances for catch weight simple packs. Valid values are:  A - actual P - percent';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.max_tolerance IS 'The maximum tolerance value for the catch weight simple pack.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.min_tolerance IS 'The minimum tolerance value for a catch weight simple pack.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.negotiated_item_cost IS 'This will hold the supplier negotiated item cost for the primary delivery country of the item. Once a location is associated with the item, the primary locations negotiated item cost will be stored in this field.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.extended_base_cost IS 'This will hold the extended base cost for the primary delivery country of the item. Once a location is associated with the item, the primary locations extended base cost will be stored in this field. Extended base cost is the cost inclusive of all the taxes that affect the WAC. In case of GTAX , Extended Base Cost = Base Cost + Non-recoverable taxes. In case of VAT, Extended Base Cost = Base Cost.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.inclusive_cost IS 'This will hold the inclusive cost for the primary delivery country of the item. Once a location is associated with the item, the primary locations inclusive cost will be stored in this field. This cost will have both the recoverable and non recoverable taxes included. In case of GTAX , Inclusive Cost = Base Cost + Non-recoverable taxes + Recoverable Taxes. In case of VAT,  Inclusive Cost = Base Cost + VAT';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.base_cost IS 'This field will hold the tax exclusive cost of the item.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.purchase_type IS 'Contains a code to indicate whether the item is normal merchandise (i.e. owned by the retailer), consignment stock or a concession item. Valid values are 0 - Owned, 1 - Consignment, 2 - Concession.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.calculation_basis IS 'This field will indicate if the cost for the consignment/concession item will be managed either based on cost per unit or as a percentage of retail. Valid values are C - Cost per Unit, P - Purchase Rate.';
COMMENT ON COLUMN ITEM_SUPP_COUNTRY_OUT.purchase_rate IS 'Contains the percentage of the retail price which will determine the cost paid to the supplier for a consignment or concession item.';


-- Add BDI primary key constraint

ALTER TABLE ITEM_SUPP_COUNTRY_OUT ADD CONSTRAINT [REDACTED]PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE ITEM_SUPP_COUNTRY_OUT ADD CONSTRAINT chk_type_item_supp_country_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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