-- Interface Module: ItemSupplier_Fnd
-- Interface: Item_Supplier_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE item_supplier_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


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

CREATE TABLE ITEM_SUPPLIER_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT item_supplier_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,
  -- This field contains the number of the supplier of the item.
  supplier NUMBER(10,0) NOT NULL,
  -- This field contains the trading name of the supplier.
  supplier_name VARCHAR2(240) NOT NULL,
  -- This field Indicates whether this supplier is the primary supplier for the item..  Each item can have one and only one primary supplier.  Valid values are Y or N.  This field is meaningless for sub-transaction level items.
  primary_supp_ind VARCHAR2(1) NOT NULL,
  -- This field contains the Vendor Product Number associated with this item.
  vpn VARCHAR2(30),
  -- This field will hold the supplier laber for an item (Parent/Child)
  supp_label VARCHAR2(15),
  -- This field contains the consignment rate for this item for the supplier.
  consignment_rate NUMBER(12,4),
  -- This field contains the first supplier differentiator and/or description.  This field may only contain a value for items with an item_parent.
  supp_diff_1 VARCHAR2(120),
  -- This field contains the second supplier differentiator and/or description.  This field may only contain a value for items with an item_parent.
  supp_diff_2 VARCHAR2(120),
  -- This field contains the third supplier differentiator and/or description.  This field may only contain a value for items with an item_parent.
  supp_diff_3 VARCHAR2(120),
  -- This field contains the fourth supplier differentiator and/or description.  This field may only contain a value for items with an item_parent.
  supp_diff_4 VARCHAR2(120),
  -- Code referencing the name used by supplier to refer to the pallet.  Valid codes are defined in the PALN code type.  Examples are flat, pallet.
  pallet_name VARCHAR2(6) NOT NULL,
  -- Code referencing the name used by supplier to refer to the case.  Valid codes are defined in the CASN code type.  Examples are pack, box, bag.
  case_name VARCHAR2(6) NOT NULL,
  -- Code referencing the name used by supplier to refer to the inner.  Valid codes are defined in the INRN code type.  Examples are sub-case, sub-pack.
  inner_name VARCHAR2(6) NOT NULL,
  -- Date which the supplier discontinues an item.  The retailor should be aware that the supplier is able to reuse a UPC after 30 months and should work to ensure that no data exists in RMS for a UPC 30 months after it has been discontinued.
  supp_discontinue_date TIMESTAMP,
  -- This field will  contain a value of Yes to indicate that any item asssociated with this supplier is eligible for a direct shipment from the supplier to the customer.
  direct_ship_ind VARCHAR2(1) NOT NULL,
  -- The concession rate is the margin that a particular supplier receives for the sale of a concession item.
  concession_rate NUMBER(12,4),
  -- Used only if AIP is interfaced. Indicates the primary case size for the item supplier when an orderable item is configured for informal case types.
  primary_case_size VARCHAR2(6)
);

COMMENT ON TABLE ITEM_SUPPLIER_OUT IS 'This table is used to integrate Item Supplier information.';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.item IS 'Alphanumeric value that identifies the item';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.supplier IS 'This field contains the number of the supplier of the item.';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.supplier_name IS 'This field contains the trading name of the supplier.';
COMMENT ON COLUMN ITEM_SUPPLIER_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.  Valid values are Y or N.  This field is meaningless for sub-transaction level items.';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.vpn IS 'This field contains the Vendor Product Number associated with this item.';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.supp_label IS 'This field will hold the supplier laber for an item (Parent/Child)';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.consignment_rate IS 'This field contains the consignment rate for this item for the supplier.';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.supp_diff_1 IS 'This field contains the first supplier differentiator and/or description.  This field may only contain a value for items with an item_parent.';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.supp_diff_2 IS 'This field contains the second supplier differentiator and/or description.  This field may only contain a value for items with an item_parent.';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.supp_diff_3 IS 'This field contains the third supplier differentiator and/or description.  This field may only contain a value for items with an item_parent.';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.supp_diff_4 IS 'This field contains the fourth supplier differentiator and/or description.  This field may only contain a value for items with an item_parent.';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.pallet_name IS 'Code referencing the name used by supplier to refer to the pallet.  Valid codes are defined in the PALN code type.  Examples are flat, pallet.';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.case_name IS 'Code referencing the name used by supplier to refer to the case.  Valid codes are defined in the CASN code type.  Examples are pack, box, bag.';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.inner_name IS 'Code referencing the name used by supplier to refer to the inner.  Valid codes are defined in the INRN code type.  Examples are sub-case, sub-pack.';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.supp_discontinue_date IS 'Date which the supplier discontinues an item.  The retailor should be aware that the supplier is able to reuse a UPC after 30 months and should work to ensure that no data exists in RMS for a UPC 30 months after it has been discontinued.';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.direct_ship_ind IS 'This field will  contain a value of Yes to indicate that any item asssociated with this supplier is eligible for a direct shipment from the supplier to the customer.';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.concession_rate IS 'The concession rate is the margin that a particular supplier receives for the sale of a concession item.';
COMMENT ON COLUMN ITEM_SUPPLIER_OUT.primary_case_size IS 'Used only if AIP is interfaced. Indicates the primary case size for the item supplier when an orderable item is configured for informal case types.';


-- Add BDI primary key constraint

ALTER TABLE ITEM_SUPPLIER_OUT ADD CONSTRAINT pk_item_supplier_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE ITEM_SUPPLIER_OUT ADD CONSTRAINT chk_type_item_supplier_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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