-- Interface Module: Supplier_Fnd
-- Interface: Sups_Out

-- Create sequence for bdi_seq_id column

CREATE SEQUENCE sups_out_seq ORDER CACHE 20 MAXVALUE 9999999999999999999;


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

CREATE TABLE SUPS_OUT (
  -- bdi internal column
  bdi_seq_id NUMBER DEFAULT sups_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',
  -- Unique identifying number for a supplier within the system.  The user determines this number when a new supplier is first added to the system.
  supplier NUMBER(10,0) NOT NULL,
  -- Contains the suppliers trading name.
  sup_name VARCHAR2(240) NOT NULL,
  -- Secondary name of the supplier.
  sup_name_secondary VARCHAR2(240),
  -- PARENT_SUPPLIER field will store supplier number for the supplier sites. For Suppliers, this field will be NULL.
  supplier_parent NUMBER(10,0),
  -- Contains the name of the suppliers representative contact.
  contact_name VARCHAR2(120) NOT NULL,
  -- Contains a telephone number for the suppliers representative contact.
  contact_phone VARCHAR2(20) NOT NULL,
  -- Contains a fax number for the suppliers representative contact.
  contact_fax VARCHAR2(20),
  -- Contains the number for the pager of the suppliers representative contact.
  contact_pager VARCHAR2(20),
  -- Determines whether the supplier is currently active.   Valid values include:  A for an active supplier or I for an inactive supplier.  The status of a supplier will be checked when an order is being created to make certain the supplier is active.
  sup_status VARCHAR2(1) NOT NULL,
  -- Determines whether orders from this supplier will default as requiring quality control.   A value of Y  means that all orders from this supplier will require quality control unless overridden by the user when the order is created.  An N in this field means that quality control will not be required unless indicated by the user during order creation.
  qc_ind VARCHAR2(1) NOT NULL,
  -- Indicates the percentage of items per receipt that will be marked for quality checking.
  qc_pct NUMBER(12,4),
  -- Indicates the frequency in which items per receipt will be marked for quality checking.
  qc_freq NUMBER(2,0),
  -- Determines whether orders from this supplier will default as requiring vendor control.   A value of Y  means that all orders from this supplier will require vendor control. An N in this field means that vendor control will not be required.
  vc_ind VARCHAR2(1) NOT NULL,
  -- Indicates the percentage of items per receipt that will be marked for vendor checking.
  vc_pct NUMBER(12,4),
  -- Indicates the frequency in which items per receipt will be marked for vendor checking.
  vc_freq NUMBER(2,0),
  -- Contains a code identifying the currency the supplier uses for business transactions.
  currency_code VARCHAR2(3) NOT NULL,
  -- This field contains the suppliers preferred language.  This field is provided for custom purchase orders in a specified language.
  lang NUMBER(6,0),
  -- Indicator identifying the sales terms that will default when an order is created for the supplier.  These terms specify when payment is due and if any discounts exist for early payment.
  terms VARCHAR2(15) NOT NULL,
  -- Indicator that references what freight terms will default when a order is created for the supplier.
  freight_terms VARCHAR2(30) NOT NULL,
  -- Indicates whether or not the supplier will accept returns.  Valid values are Y and N.
  ret_allow_ind VARCHAR2(1) NOT NULL,
  -- Indicates if returns must be accompanied by an authorization number when sent back to the vendor.   Valid values are Y and N.
  ret_auth_req VARCHAR2(1) NOT NULL,
  -- Contains a value if the supplier requires a minimum dollar amount to be returned in order to accept the return.  Returns of less than this amount will not be processed by the system.  This field is stored in the suppliers currency.
  ret_min_dol_amt NUMBER(20,4),
  -- Contains the name of the courier that should be used for all returns to the supplier.
  ret_courier VARCHAR2(250),
  -- Percentage multiplied by the total order cost to determine the handling cost for the return.
  handling_pct NUMBER(12,4),
  -- Indicates whether purchase orders will be sent to the supplier via Electronic Data Interchange.   Valid values are Y and N.
  edi_po_ind VARCHAR2(1) NOT NULL,
  -- Indicates whether purchase order changes will be sent to the supplier via Electronic Data Interchange.   Valid values are Y and N.
  edi_po_chg VARCHAR2(1) NOT NULL,
  -- Indicates whether this supplier will send acknowledgment of a purchase orders sent via Electronic Data Interchange.   Valid values are Y and N.
  edi_po_confirm VARCHAR2(1) NOT NULL,
  -- Indicates whether this supplier will send Advance Shipment Notifications electronically.   Valid values are Y and N.
  edi_asn VARCHAR2(1) NOT NULL,
  -- This field contains the EDI sales report frequency for this supplier. Valid values are:  D - Sales and stock information will be downloaded daily    W - Sales and stock information will be downloaded weekly
  edi_sales_rpt_freq VARCHAR2(1),
  -- This field indicates whether the supplier will send availability via EDI.
  edi_supp_available_ind VARCHAR2(1) NOT NULL,
  -- This field indicates whether contracts will be sent to the supplier via EDI.
  edi_contract_ind VARCHAR2(1) NOT NULL,
  -- Indicates whether invoices, debit memos and credit note requests will be sent to/from the supplier via Electronic Data Interchange.   Valid values are Y and N.
  edi_invc_ind VARCHAR2(1) NOT NULL,
  -- If the supplier is an EDI supplier and supports vendor initiated ordering, this field will contain the channel ID for the channel to which all inventory for these types of orders will flow.  This field is used when a vendor initiated order is created for a physical warehouse to determine the virtual warehouse within the physical warehouse to which the inventory will flow.  The virtual warehouse belonging to the indicated channel will be used.  This will only be used in a multichannel environment
  edi_channel_id NUMBER(4,0),
  -- This field contains the cost change variance by percent.  If an EDI cost change is accepted and falls within these boundaries, it will be approved when inserted into the cost change dialogue.
  cost_chg_pct_var NUMBER(12,4),
  -- This field contains the cost change variance by amount.  If an EDI cost change is accepted and falls within these boundaries, it will be approved when inserted into the cost change dialogue.
  cost_chg_amt_var NUMBER(20,4),
  -- Indicates whether contract orders for the supplier should be created in Approved status.   Valid values are Y and N.
  replen_approval_ind VARCHAR2(1) NOT NULL,
  -- The method used to ship the items on the purchase order from the country of origin to the country of import. Valid values are: 10 (Vessel, Non-container) 11 (Vessel, Container) 12 (Border Water-borne (Only Mexico and Canada)) 20 (Rail, Non-container)   21 (Rail, Container)    30 (Truck, Non-container)     31 (Truck, Container)   32 (Auto)   33 (Pedestrian)   34 (Road, other, includes foot and animal borne)   40 (Air, Non-container)    41 (Air, Container)  50 (Mail)  60 (Passenger, Hand carried) 70 (Fixed Transportation Installation) 80 (Not used at this time)
  ship_method VARCHAR2(6),
  --    LC(Letter of Credit)
  payment_method VARCHAR2(6),
  -- This field contains the telex number of the partner or suppliers representative contact.
  contact_telex VARCHAR2(20),
  -- This field contains the email address of the partner or suppliers representative contact.
  contact_email VARCHAR2(100),
  -- This field indicates which payment process method is used for this supplier.  Valid values are:   E - Evaluated Receipts Settlement (ERS)   N - Not Applicable Choosing a settlement code of type E will cause an Accounts Payable transaction to be written for an item received from this supplier.
  settlement_code VARCHAR2(1) NOT NULL,
  -- This field indicates whether or not the supplier has agreed to break an order into separate boxes (and mark them) that can be shipped directly to the stores.   Valid values are Y and N.
  pre_mark_ind VARCHAR2(1) NOT NULL,
  -- Indicates whether or not the suppliers invoice matches can be automatically approved for payment.  Valid values are Y or N.  This field will only be populated if invoice matching is installed.
  auto_appr_invc_ind VARCHAR2(1) NOT NULL,
  -- Indicates when a debit memo will be sent to the supplier to resolve a discrepancy.  Valid values are Y if debit memos are always to be sent, L if debit memos are used only if a credit note is not sent by the invoice due date, or N if debit memos are never to be sent.  This field will only be populated if invoice matching is installed. Debit memo codes will be held on the codes table under the code type  IMDC.
  dbt_memo_code VARCHAR2(1),
  -- Indicates if a supplier is allowed to charge freight costs to the client.  This field will only be populated if invoice matching is installed.  Valid values are Y and N.
  freight_charge_ind VARCHAR2(1) NOT NULL,
  -- Indicates whether or not debit memos sent to the supplier can be automatically approved on creation.  Valid values are Y or N.  This field will only be populated if invoice matching is installed.
  auto_appr_dbt_memo_ind VARCHAR2(1) NOT NULL,
  -- Indicates whether or not all invoices for the supplier can be considered pre-paid invoices.  This field will only be populated if invoice matching is installed.  Valid values are Y and N.
  prepay_invc_ind VARCHAR2(1) NOT NULL,
  -- Indicates if backorders or partial shipments will be accepted.
  backorder_ind VARCHAR2(1) NOT NULL,
  -- Contains the unique identifying number for the VAT region in the system.
  vat_region NUMBER(4,0),
  -- Indicator that determines whether supplier inventory management information can be set up at the supplier/department level or just at the supplier level.  Also determines if orders created through replenishment for this supplier should be split by department or not. If the supplier is returns-only, this field will be null.   Otherwise, this field must have value. Values include: D - Department.  Split orders by department. S - Supplier. Split orders by supplier.
  inv_mgmt_lvl VARCHAR2(6),
  -- Indicates if the suppliers services (e.g. shelf stocking) must be confirmed as performed before paying an invoice from that supplier.  Valid values are Y (all service non-merchandise lines on an invoice from this supplier must be confirmed before the invoice can be paid) and N (services do not need to be confirmed).
  service_perf_req_ind VARCHAR2(1) NOT NULL,
  -- Indicates where invoices from this supplier are paid - at the store or centrally through corporate accounting.  Valid values are S (paid at the store) and C (paid centrally).  This field should only be S if using ReSA to accept payment at the store, and only populated when using invoice matching.
  invc_pay_loc VARCHAR2(6),
  -- Indicates where invoices from this supplier are received - at the store or centrally through corporate accounting.  Valid values are S (received at the store) and C (received centrally).  This field should only be populated when using invoice matching.
  invc_receive_loc VARCHAR2(6),
  -- Indicates if the supplier invoice lists items at gross cost instead of net (off-invoice discounts subtracted). Valid values are G (invoices at gross) or N (invoices at net). These are stored on the code table under a code type of INGN.
  addinvc_gross_net VARCHAR2(6),
  -- Contains the delivery policy of the supplier.  Next Day indicates that the if a location is closed, the supplier will deliver on the next day.  Next Valid Delivery Day indicates that the supplier will wait until the next scheduled delivery day before delivering.  Valid values come from the DLVY code on code_head/code_detail.
  delivery_policy VARCHAR2(6) NOT NULL,
  -- Any miscellaneous comments associated with the supplier.
  comment_desc VARCHAR2(2000),
  -- Holds the default lead time for the supplier.  The lead time is the time the supplier needs between receiving an order and having the order ready to ship.  This value will be  defaulted to item/supplier relationships.
  default_item_lead_time NUMBER(4,0),
  -- This field holds the Dun and Bradstreet number to identify the supplier.
  duns_number VARCHAR2(9),
  -- This field holds the Dun and Bradstreet number to identify the location of the supplier.
  duns_loc VARCHAR2(4),
  -- This field will determine if the supplier uses bracket costing pricing structures.  Valid values:  Y = Yes, N = No.
  bracket_costing_ind VARCHAR2(1) NOT NULL,
  -- This column determines the status in which any inbound POs from this supplier are created.  A NULL value indicates that the supplier is not a VMI supplier.  Orders from these suppliers will be still be created in worksheet status.
  vmi_order_status VARCHAR2(6),
  -- Indicates whether the supplier can ship direct to store.  Valid values are Y and N.
  dsd_ind VARCHAR2(1) NOT NULL,
  -- Depending upon the value in this column, scaling is done for AIP orders. Default value is N.
  scale_aip_orders VARCHAR2(1) NOT NULL,
  -- Supplier can ship to final destination as per allocation or not.
  final_dest_ind VARCHAR2(1) NOT NULL,
  -- This will hold the level at which quantity is ordered at. Valid values are CA for cases; EA for eaches. Default value if NULL is EA
  sup_qty_level VARCHAR2(6) NOT NULL,
  -- This column holds the ID for the supplier used in the external financial system. It is populated by the integration that brings suppliers from external financial systems into RMS. This ID and the supplier site ID can be used to join to information about the supplier in the external system.
  external_ref_id VARCHAR2(32),
  -- Column indicates that Supplier is inactivated from RMS. Suppliers inactivated in RMS will have this column updated as Y. Otherwise this column will be null.
  status_upd_by_rms VARCHAR2(1)
);

COMMENT ON TABLE SUPS_OUT IS 'This table is used to integrate Supplier information.';
COMMENT ON COLUMN SUPS_OUT.bdi_seq_id IS 'bdi internal column';
COMMENT ON COLUMN SUPS_OUT.bdi_app_name IS 'bdi internal column';
COMMENT ON COLUMN SUPS_OUT.bdi_dataset_type IS 'bdi internal column';
COMMENT ON COLUMN SUPS_OUT.bdi_dataset_action IS 'bdi internal column';
COMMENT ON COLUMN SUPS_OUT.supplier IS 'Unique identifying number for a supplier within the system.  The user determines this number when a new supplier is first added to the system.';
COMMENT ON COLUMN SUPS_OUT.sup_name IS 'Contains the suppliers trading name.';
COMMENT ON COLUMN SUPS_OUT.sup_name_secondary IS 'Secondary name of the supplier.';
COMMENT ON COLUMN SUPS_OUT.supplier_parent IS 'PARENT_SUPPLIER field will store supplier number for the supplier sites. For Suppliers, this field will be NULL.';
COMMENT ON COLUMN SUPS_OUT.contact_name IS 'Contains the name of the suppliers representative contact.';
COMMENT ON COLUMN SUPS_OUT.contact_phone IS 'Contains a telephone number for the suppliers representative contact.';
COMMENT ON COLUMN SUPS_OUT.contact_fax IS 'Contains a fax number for the suppliers representative contact.';
COMMENT ON COLUMN SUPS_OUT.contact_pager IS 'Contains the number for the pager of the suppliers representative contact.';
COMMENT ON COLUMN SUPS_OUT.sup_status IS 'Determines whether the supplier is currently active.   Valid values include:  A for an active supplier or I for an inactive supplier.  The status of a supplier will be checked when an order is being created to make certain the supplier is active.';
COMMENT ON COLUMN SUPS_OUT.qc_ind IS 'Determines whether orders from this supplier will default as requiring quality control.   A value of Y  means that all orders from this supplier will require quality control unless overridden by the user when the order is created.  An N in this field means that quality control will not be required unless indicated by the user during order creation.';
COMMENT ON COLUMN SUPS_OUT.qc_pct IS 'Indicates the percentage of items per receipt that will be marked for quality checking.';
COMMENT ON COLUMN SUPS_OUT.qc_freq IS 'Indicates the frequency in which items per receipt will be marked for quality checking.';
COMMENT ON COLUMN SUPS_OUT.vc_ind IS 'Determines whether orders from this supplier will default as requiring vendor control.   A value of Y  means that all orders from this supplier will require vendor control. An N in this field means that vendor control will not be required.';
COMMENT ON COLUMN SUPS_OUT.vc_pct IS 'Indicates the percentage of items per receipt that will be marked for vendor checking.';
COMMENT ON COLUMN SUPS_OUT.vc_freq IS 'Indicates the frequency in which items per receipt will be marked for vendor checking.';
COMMENT ON COLUMN SUPS_OUT.currency_code IS 'Contains a code identifying the currency the supplier uses for business transactions.';
COMMENT ON COLUMN SUPS_OUT.lang IS 'This field contains the suppliers preferred language.  This field is provided for custom purchase orders in a specified language.';
COMMENT ON COLUMN SUPS_OUT.terms IS 'Indicator identifying the sales terms that will default when an order is created for the supplier.  These terms specify when payment is due and if any discounts exist for early payment.';
COMMENT ON COLUMN SUPS_OUT.freight_terms IS 'Indicator that references what freight terms will default when a order is created for the supplier.';
COMMENT ON COLUMN SUPS_OUT.ret_allow_ind IS 'Indicates whether or not the supplier will accept returns.  Valid values are Y and N.';
COMMENT ON COLUMN SUPS_OUT.ret_auth_req IS 'Indicates if returns must be accompanied by an authorization number when sent back to the vendor.   Valid values are Y and N.';
COMMENT ON COLUMN SUPS_OUT.ret_min_dol_amt IS 'Contains a value if the supplier requires a minimum dollar amount to be returned in order to accept the return.  Returns of less than this amount will not be processed by the system.  This field is stored in the suppliers currency.';
COMMENT ON COLUMN SUPS_OUT.ret_courier IS 'Contains the name of the courier that should be used for all returns to the supplier.';
COMMENT ON COLUMN SUPS_OUT.handling_pct IS 'Percentage multiplied by the total order cost to determine the handling cost for the return.';
COMMENT ON COLUMN SUPS_OUT.edi_po_ind IS 'Indicates whether purchase orders will be sent to the supplier via Electronic Data Interchange.   Valid values are Y and N.';
COMMENT ON COLUMN SUPS_OUT.edi_po_chg IS 'Indicates whether purchase order changes will be sent to the supplier via Electronic Data Interchange.   Valid values are Y and N.';
COMMENT ON COLUMN SUPS_OUT.edi_po_confirm IS 'Indicates whether this supplier will send acknowledgment of a purchase orders sent via Electronic Data Interchange.   Valid values are Y and N.';
COMMENT ON COLUMN SUPS_OUT.edi_asn IS 'Indicates whether this supplier will send Advance Shipment Notifications electronically.   Valid values are Y and N.';
COMMENT ON COLUMN SUPS_OUT.edi_sales_rpt_freq IS 'This field contains the EDI sales report frequency for this supplier. Valid values are:  D - Sales and stock information will be downloaded daily    W - Sales and stock information will be downloaded weekly';
COMMENT ON COLUMN SUPS_OUT.edi_supp_available_ind IS 'This field indicates whether the supplier will send availability via EDI.';
COMMENT ON COLUMN SUPS_OUT.edi_contract_ind IS 'This field indicates whether contracts will be sent to the supplier via EDI.';
COMMENT ON COLUMN SUPS_OUT.edi_invc_ind IS 'Indicates whether invoices, debit memos and credit note requests will be sent to/from the supplier via Electronic Data Interchange.   Valid values are Y and N.';
COMMENT ON COLUMN SUPS_OUT.edi_channel_id IS 'If the supplier is an EDI supplier and supports vendor initiated ordering, this field will contain the channel ID for the channel to which all inventory for these types of orders will flow.  This field is used when a vendor initiated order is created for a physical warehouse to determine the virtual warehouse within the physical warehouse to which the inventory will flow.  The virtual warehouse belonging to the indicated channel will be used.  This will only be used in a multichannel environment';
COMMENT ON COLUMN SUPS_OUT.cost_chg_pct_var IS 'This field contains the cost change variance by percent.  If an EDI cost change is accepted and falls within these boundaries, it will be approved when inserted into the cost change dialogue.';
COMMENT ON COLUMN SUPS_OUT.cost_chg_amt_var IS 'This field contains the cost change variance by amount.  If an EDI cost change is accepted and falls within these boundaries, it will be approved when inserted into the cost change dialogue.';
COMMENT ON COLUMN SUPS_OUT.replen_approval_ind IS 'Indicates whether contract orders for the supplier should be created in Approved status.   Valid values are Y and N.';
COMMENT ON COLUMN SUPS_OUT.ship_method IS 'The method used to ship the items on the purchase order from the country of origin to the country of import. Valid values are: 10 (Vessel, Non-container) 11 (Vessel, Container) 12 (Border Water-borne (Only Mexico and Canada)) 20 (Rail, Non-container)   21 (Rail, Container)    30 (Truck, Non-container)     31 (Truck, Container)   32 (Auto)   33 (Pedestrian)   34 (Road, other, includes foot and animal borne)   40 (Air, Non-container)    41 (Air, Container)  50 (Mail)  60 (Passenger, Hand carried) 70 (Fixed Transportation Installation) 80 (Not used at this time)';
COMMENT ON COLUMN SUPS_OUT.payment_method IS '   LC(Letter of Credit)';
COMMENT ON COLUMN SUPS_OUT.contact_telex IS 'This field contains the telex number of the partner or suppliers representative contact.';
COMMENT ON COLUMN SUPS_OUT.contact_email IS 'This field contains the email address of the partner or suppliers representative contact.';
COMMENT ON COLUMN SUPS_OUT.settlement_code IS 'This field indicates which payment process method is used for this supplier.  Valid values are:   E - Evaluated Receipts Settlement (ERS)   N - Not Applicable Choosing a settlement code of type E will cause an Accounts Payable transaction to be written for an item received from this supplier.';
COMMENT ON COLUMN SUPS_OUT.pre_mark_ind IS 'This field indicates whether or not the supplier has agreed to break an order into separate boxes (and mark them) that can be shipped directly to the stores.   Valid values are Y and N.';
COMMENT ON COLUMN SUPS_OUT.auto_appr_invc_ind IS 'Indicates whether or not the suppliers invoice matches can be automatically approved for payment.  Valid values are Y or N.  This field will only be populated if invoice matching is installed.';
COMMENT ON COLUMN SUPS_OUT.dbt_memo_code IS 'Indicates when a debit memo will be sent to the supplier to resolve a discrepancy.  Valid values are Y if debit memos are always to be sent, L if debit memos are used only if a credit note is not sent by the invoice due date, or N if debit memos are never to be sent.  This field will only be populated if invoice matching is installed. Debit memo codes will be held on the codes table under the code type  IMDC.';
COMMENT ON COLUMN SUPS_OUT.freight_charge_ind IS 'Indicates if a supplier is allowed to charge freight costs to the client.  This field will only be populated if invoice matching is installed.  Valid values are Y and N.';
COMMENT ON COLUMN SUPS_OUT.auto_appr_dbt_memo_ind IS 'Indicates whether or not debit memos sent to the supplier can be automatically approved on creation.  Valid values are Y or N.  This field will only be populated if invoice matching is installed.';
COMMENT ON COLUMN SUPS_OUT.prepay_invc_ind IS 'Indicates whether or not all invoices for the supplier can be considered pre-paid invoices.  This field will only be populated if invoice matching is installed.  Valid values are Y and N.';
COMMENT ON COLUMN SUPS_OUT.backorder_ind IS 'Indicates if backorders or partial shipments will be accepted.';
COMMENT ON COLUMN SUPS_OUT.vat_region IS 'Contains the unique identifying number for the VAT region in the system.';
COMMENT ON COLUMN SUPS_OUT.inv_mgmt_lvl IS 'Indicator that determines whether supplier inventory management information can be set up at the supplier/department level or just at the supplier level.  Also determines if orders created through replenishment for this supplier should be split by department or not. If the supplier is returns-only, this field will be null.   Otherwise, this field must have value. Values include: D - Department.  Split orders by department. S - Supplier. Split orders by supplier.';
COMMENT ON COLUMN SUPS_OUT.service_perf_req_ind IS 'Indicates if the suppliers services (e.g. shelf stocking) must be confirmed as performed before paying an invoice from that supplier.  Valid values are Y (all service non-merchandise lines on an invoice from this supplier must be confirmed before the invoice can be paid) and N (services do not need to be confirmed).';
COMMENT ON COLUMN SUPS_OUT.invc_pay_loc IS 'Indicates where invoices from this supplier are paid - at the store or centrally through corporate accounting.  Valid values are S (paid at the store) and C (paid centrally).  This field should only be S if using ReSA to accept payment at the store, and only populated when using invoice matching.';
COMMENT ON COLUMN SUPS_OUT.invc_receive_loc IS 'Indicates where invoices from this supplier are received - at the store or centrally through corporate accounting.  Valid values are S (received at the store) and C (received centrally).  This field should only be populated when using invoice matching.';
COMMENT ON COLUMN SUPS_OUT.addinvc_gross_net IS 'Indicates if the supplier invoice lists items at gross cost instead of net (off-invoice discounts subtracted). Valid values are G (invoices at gross) or N (invoices at net). These are stored on the code table under a code type of INGN.';
COMMENT ON COLUMN SUPS_OUT.delivery_policy IS 'Contains the delivery policy of the supplier.  Next Day indicates that the if a location is closed, the supplier will deliver on the next day.  Next Valid Delivery Day indicates that the supplier will wait until the next scheduled delivery day before delivering.  Valid values come from the DLVY code on code_head/code_detail.';
COMMENT ON COLUMN SUPS_OUT.comment_desc IS 'Any miscellaneous comments associated with the supplier.';
COMMENT ON COLUMN SUPS_OUT.default_item_lead_time IS 'Holds the default lead time for the supplier.  The lead time is the time the supplier needs between receiving an order and having the order ready to ship.  This value will be  defaulted to item/supplier relationships.';
COMMENT ON COLUMN SUPS_OUT.duns_number IS 'This field holds the Dun and Bradstreet number to identify the supplier.';
COMMENT ON COLUMN SUPS_OUT.duns_loc IS 'This field holds the Dun and Bradstreet number to identify the location of the supplier.';
COMMENT ON COLUMN SUPS_OUT.bracket_costing_ind IS 'This field will determine if the supplier uses bracket costing pricing structures.  Valid values:  Y = Yes, N = No.';
COMMENT ON COLUMN SUPS_OUT.vmi_order_status IS 'This column determines the status in which any inbound POs from this supplier are created.  A NULL value indicates that the supplier is not a VMI supplier.  Orders from these suppliers will be still be created in worksheet status.';
COMMENT ON COLUMN SUPS_OUT.dsd_ind IS 'Indicates whether the supplier can ship direct to store.  Valid values are Y and N.';
COMMENT ON COLUMN SUPS_OUT.scale_aip_orders IS 'Depending upon the value in this column, scaling is done for AIP orders. Default value is N.';
COMMENT ON COLUMN SUPS_OUT.final_dest_ind IS 'Supplier can ship to final destination as per allocation or not.';
COMMENT ON COLUMN SUPS_OUT.sup_qty_level IS 'This will hold the level at which quantity is ordered at. Valid values are CA for cases; EA for eaches. Default value if NULL is EA';
COMMENT ON COLUMN SUPS_OUT.external_ref_id IS 'This column holds the ID for the supplier used in the external financial system. It is populated by the integration that brings suppliers from external financial systems into RMS. This ID and the supplier site ID can be used to join to information about the supplier in the external system.';
COMMENT ON COLUMN SUPS_OUT.status_upd_by_rms IS 'Column indicates that Supplier is inactivated from RMS. Suppliers inactivated in RMS will have this column updated as Y. Otherwise this column will be null.';


-- Add BDI primary key constraint

ALTER TABLE SUPS_OUT ADD CONSTRAINT pk_sups_out PRIMARY KEY (bdi_app_name, bdi_seq_id);


-- Add check constraint for bdi_dataset_type column

ALTER TABLE SUPS_OUT ADD CONSTRAINT chk_type_sups_out CHECK (bdi_dataset_type IN ('FULL', 'PARTIAL'));


-- Add check constraint for bdi_dataset_action column

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