Data Model Definition

Topics:

Important: This data model is used as the base for Supplier reporting built into WebFOCUS. Any modifications made to this model will impact any related reporting. Only additions should be made to this data model. Labels can be created in WebFOCUS to change any attribute name.

The Omni-Gen™ for Supplier data model includes the following subjects:

SUPPLIER

The SUPPLIER subject identifies basic master data about suppliers such as name, trade prohibition indicator, ownership type, and social security number/tax identification number.

SUPPLIER subject attributes include:

Name

Description

first_name

The first name for a supplier party.

middle_name

The middle name for a supplier party.

last_name

The last name for a supplier party.

full_name

The full name for a vendor party, such as when a person is providing services as a supplier.

company_name

The legally identified name for a supplier organization.

dba_name

The doing business as name for a supplier organization.

trade_prohibi_ind

A Y/N indicator that identifies a trade prohibition for a given supplier such as statutory or administrative debarment.

primary_geographic_area

The primary geographic area (e.g., region, branch, store, sales region) assigned to the supplier.

credit_rating

The classification of a vendor into a range of credit ratings (for example, High Risk, Moderate Risk, or Low Risk).

vendor_pay_priority

The priority ranking for a given vendor in relation to their invoice payment. For example, High, Standard, or Low.

ownership_type

The classification of a vendor into an ownership type, such as women owned, minority owned, or Indian owned.

legal_class_type

The type of business, such as nonprofit, limited partnership, trust, or sole proprietor.

prim_sales_org

The primary sales organization assigned to a supplier.

SUPPLIER ALTERNATE ID

The SUPPLIER ALTERNATE ID subject identifies any alternate identifiers for the supplier.

SUPPLIER ALTERNATE ID subject attributes include:

Name

Description

supplier_iden_type

The type of alternate identifier assigned to the entire supplier, such as Commercial and Government Entity (CAGE), NATO Supply Code For Manufacturers (NSCM), or Data Universal Number System (DUNS). For an individual supplier, this could be their passport number or driver's license.

alt_identifier_id

The alternate identifier for the supplier.

issuing_country

The country issuing the alternate identifier.

supplier_iden_active

A Y/N indicator identifying whether the alternate identifier is active.

SUPPLIER ITEM

The SUPPLIER ITEM subject maps the item identifier of the enterprise to the item identifier and item name of the supplier.

SUPPLIER ITEM subject attributes include:

Name

Description

item_id

The item identifier of the enterprise.

sup_item_id

The item identifier of the supplier for the item of the enterprise.

sup_item_name

The name for the item of the supplier.

supp_item_start_dt

The start date for the item of the supplier.

supp_item_end_dt

The end date for the item of the supplier.

ADDRESS

The ADDRESS subject identifies address data including address type, time zone, and geographic area. The geographic area can represent a division, branch, department, or other geographical grouping of a company used for reporting purposes.

ADDRESS subject attributes include:

Name

Description

address_type

The type of address, such as corporate, sold to, ship to, or bill to.

primary_ind

A Y/N indicator identifying the primary address for a supplier.

address_status

The current status for an address.

attn_to_contact

The name of the attention to contact portion of the address.

street_address1

The first line of the street address portion of the address associated to a supplier party.

street_address2

The second line of the street address portion of the address associated to a supplier party.

city

The city portion of the address associated to a supplier party.

state_territory

The state or territory portion of the address associated to a supplier party.

county

The county portion of the address associated to a supplier party.

country

The country portion of the address associated to a supplier party.

postal_code

The zip or postal code portion of the address associated to a supplier party.

time_zone

The time zone associated with the address.

address_geo_area

The geographic area associated with the address. For example, the address may be located in a specific sales region.

latitude

The geospatial latitude for an address.

longitude

The geospatial longitude for an address.

CAGE_cd

Commercial and Governmental Entity code assigned to the location or address of the supplier.

ADDRESS_DUNS

The ADDRESS_DUNS subject identifies the DUNS numbers associated with an address. Note that the DUNS number can be assigned to the supplier, address, or address DUNS levels of the model.

ADDRESS_DUNS subject attributes include:

Name

Description

CAGE_cd

Commercial and Governmental Entity code assigned to the DUNS number of the supplier.

DUNS_Number

The Dun and Bradstreet Data Universal numbering system number assigned to a supplier.

VENDOR PURCHASE ORDER

The VENDOR PURCHASE ORDER subject identifies general master data about the vendor purchase order such as bill to customer, sales order date, and order priority. Several attributes are available for comparison such as requested ship to address, ship date, order quantity, and committed/actuals. Metrics are available for the order fill rate and return rate.

VENDOR PURCHASE ORDER subject attributes include:

Name

Description

fulfill_supp_id

The unique identifier for the fulfilling supplier.

vendor_po_type

The type of purchase order such as standard, blanket, or standing.

vendor_po_date

The date the purchase order was placed.

req _st_street

The purchase orders requested ship to street address.

req_sh_city

The purchase orders requested ship to city.

req_sh_state

The purchase orders requested ship to state/territory.

req_sh_zip

The purchase orders requested ship to zip/postal code.

req_sh_country

The purchase orders requested ship to country.

rec_st_street

The actual ship to street address used for shipment of a purchase order's goods.

rec_st_city

The actual ship to city used for shipment of a purchase order's goods.

rec_st_state

The actual ship to state/territory used for shipment of a purchase order's goods.

rec_st_zip

The actual ship to zip/postal code used for shipment of a purchase order's goods.

rec_st_country

The actual ship to country used for shipment of a purchase order's goods.

req_ship_dt

The requested ship date of the purchase order.

cmt_ship_dt

The ship date committed to by the purchase order's vendor.

act_ship_dt

The actual date that goods shipped for the purchase order.

order_priority

The order priority assigned to a purchase order.

order_status

The status assigned to a purchase order.

sold_sales_org

The sales organization that will be responsible for this purchase.

related_contract

The contract that covers all purchases made under a vendor purchase order.

VENDOR PURCHASE ORDER LINE

The VENDOR PURCHASE ORDER LINE identifies the line items of the purchase order. This details the item ordered, quantity, delivery date requested, and expected unit price. There are several attributes provided for reporting, such as being able to compare requested, committed, and actual delivery dates, delivered versus returned item quantities, and expected, invoiced, and paid amounts.

VENDOR PURCHASE ORDER LINE subject attributes include:

Name

Description

requested_item_id

The item requested on a purchase order line.

requested_item_qty

The requested quantity of an item.

committed_item_id

The item committed to be provided on a purchase order. This can differ from the requested product.

committed_item_qty

The committed quantity of an item.

requested_delivery_dt

The date requested for delivery.

committed_delivery_dt

The committed date for delivery of the enterprise.

actual_delivery_dt

The actual date of the delivery.

delivered_item_id

The item actually delivered on a purchase order line. This can differ from the committed item.

delivered_item_qty

The delivered quantity of an item.

returned_item_id

The returned item applied against this purchase order line. Depending on the return policy of the enterprise, this item can differ from the delivered item.

returned_item_qty

The returned quantity of an item.

po_ln_uom

The unit of measure for quantities on the purchase order line.

unit_pri_amt

The unit price expected for the requested item.

invoiced_unit_pri_amt

The invoiced unit price amount.

paid_unit_amt

The amount actually paid per unit for an item.

r_sales_org_id

The sales organization responsible for the purchase order line.

vendor_po_ln_dt

The creation date for the purchase order line.

po_ln_status

The status of the purchase order line.

po_ln_contract

The contract that covers the item on the purchase order line.

ITEM

The ITEM table identifies the products and/or services that can be provided by a supplier.

ITEM subject attributes include:

Name

Description

item_name

The type of phone number, such as home, office, FAX, or mobile.

item_short_desc

The short description for an item.

item_long_desc

The long description for an item.

key_item_ind

A Yes/No flag identifying whether an item is included in the major products tracked by the company.

item_base_uom

The standard/base unit of measure for an item. For a service, this will identify the billing unit of measure such as hour, day, or per job.

item_base_cost

The base cost amount for an item.

item_base_price

The base price for a base unit of measure of an item

item_sku

The stock keeping unit for an item.

item_barcode

The barcode for an item.

item_gtin

The global trade item number for an item.

item_start_dt

The date that an item became effective.

item_end_dt

The end date for an item.

item_eol_dt

The end of life date for an item.

item_type

The type of item such as Product or Service. Groupings or hierarchy levels below this are maintained in Omni Grouping.

ITEM ALTERNATE ID

The ITEM ALTERNATE ID table identifies any alternate identifiers for the item.

ITEM ALTERNATE ID subject attributes include:

Name

Description

item_alt_id_type

The type of identifier such a global product identifier, CAS number, or EINECS number.

item_alt_id_value

The alternate identifier value of the item.

item_alt_id_active_ind

A Y/N indicator identifying whether the alternate ID of the item is active

SALES ORG

The SALES ORG table identifies any level sales organization of the enterprise such as a division, branch, or region.

SALES ORG subject attributes include:

Name

Description

sales_org_name

The name for the sales organization.

sales_org_desc

The description of the sales organization.

sales_org_start_dt

The start date for a sales organization.

sales_org_end_dt

The end date for a sales organization.

primary_geo_area

The primary geographical area assigned to a sales organization.

CONTRACT

The CONTRACT table identifies any contract or agreement that is related to purchasing of items and/or services.

CONTRACT subject attributes include:

Name

Description

contract_signed_dt

The date on which a contract was signed.

contract_st_dt

The start date for a contract.

contract_end_dt

The actual end date for a contract.

contract_renew_dt

The next renewal date for a contract.

expected_expire_dt

The estimated expiration date for a contract.

prim_supplier

The primary supplier providing products or services through the contract.

contract_status

The current status of a contract.

contract_type

The type of contract such as cooperative merchandising agreement, subscription, warranty, service contract, or purchasing agreement.

CONTRACT LINE

The CONTRACT LINE table identifies each of the line items on a contract. The line item level identifies the items or services covered by the contract and any associated minimum, maximum, or mandatory quantities to be purchased.

CONTRACT LINE attributes include:

Name

Description

contract_ln_st_dt

The start date for a contract line.

contract_ln_end_dt

The end date for a contract line.

contract_item_id

The identifier of an enterprise for the item covered on a contract line.

contract_item_price

The expected price for a single unit of measure of the ordered item.

contract_item_uom

The unit of measure for the quantities of the item. For a Service, this could be the per hour, day, or job unit of measure for example.

min_purchase_qty

The minimum purchase quantity for a contract line item.

max_purchase_qty

The maximum purchase quantity for a contract line item.

item_list_price

The list price for an item at the time of the contract line creation.

list_price_discount_percent

The discount percentage associated with a contract line.

term_text

The textual description of the terms of the contract line such as Net 30, 2/10 Net 30, EOM, or 15 MFI.

contract_sla_text

The textual description of the service level agreement for the contract such as meantime to recover, turnaround time or first call resolution time.

contract_ln_status

The current status of the contract line.

mandatory_purchase_qty

The mandatory amount of an item that is required to be purchased during the effective period of the contract line.

INVOICE

The INVOICE table identifies any invoice received from a supplier. Any overall charges, such as shipping and handling charges, can be tracked at this header level of the invoice along with any overall discount that is applied to the entire invoice.

INVOICE attributes include:

Name

Description

invoice_dt

The date on which an invoice was issued.

invoice_contract_id

The contract that covers the items on an invoice.

invoice_status

The current status of an invoice.

invoice_supplier

The supplier submitting the invoice.

invoice_type

The type of invoice such as Draft, Prepayment, or Standard.

invoice_shipment_amt

The amount charged for shipment costs related to the invoice.

invoice_handling_amt

The amount charged for handling costs related to the invoice.

invoice_discount_amt

The discount amount allowed for an entire invoice.

invoice_level_payment_amt

Any payment amount that was posted to the entire invoice. Payments can also be posted at the line item level.

invoice_related po

The vendor purchase order related to an invoice.

INVOICE LINE

The INVOICE LINE table identifies each of the line items on an invoice. The line item level identifies the items or services covered by the invoice and the associated quantities and prices. It can also be used to track and compare the billed, adjustments, discounts, and actual payment amounts.

INVOICE LINE attributes include:

Name

Description

invoice_item_id

The item that is billed for on the invoice line.

invoice_item _qty

The quantity of an item that is covered by an invoice line.

invoice_item_uom

The unit of measure to be used for the invoice line quantity.

invoice_item_price

The price per unit to be used for the invoice line quantity.

item_list_price

The list price effective for an item on an invoice line.

invoice_item_adjust_amt

The amount of adjustments taken against an invoice line.

invoice_item_discount_amt

The amount of discounts taken against an invoice line.

invoice_item_paid_amt

The amount paid against an invoice line.

invoice_adjust_reason

The reason that an adjustment was taken against the invoice line.

invoice_discount_reason

The reason that a discount was taken against an invoice line.

invoice_ln_status

The status of the invoice line.

invoice_ln_related_po

The vendor purchase order related directly to an invoice line.

invoice_ln_contract

The contract whose terms and conditions cover the invoice line item.

ACCOUNT

The ACCOUNT table identifies any financial accounts associated with the supplier. This part of the model can be used to track dates, credit limits, and credit ratings associated with each account.

ACCOUNT attributes include:

Name

Description

account_type

The item that is billed for on the invoice line.

account_name

The quantity of an item that is covered by an invoice line.

account_open_dt

The unit of measure to be used for the invoice line quantity.

account_renew_dt

The price per unit to be used for the invoice line quantity.

account_close_dt

The list price effective for an item on an invoice line.

credit_limitt

The amount of adjustments taken against an invoice line.

credit_rating

The amount of discounts taken against an invoice line.

credit_terms

The amount paid against an invoice line.

ACCOUNT TEAM

The ACCOUNT TEAM table identifies any members of the enterprise which are assigned to the supplier.

ACCOUNT TEAM attributes include:

Name

Description

current_ind

A Y/N indicator identifying whether this team is the most current one assigned.

credit_rep

The person assigned as credit representative for the supplier.

direct_sales_rep

The person assigned as the direct sales representative for the supplier.

account_exec

The person assigned as the account executive for the supplier.

account_team_start_dt

The start date for the account team assignment.

account_team_end_dt

The end date for the account team assignment.

CONTACT

The CONTACT table identifies any significant contacts associated with the supplier. The person name and primary contact type can be tracked.

CONTACT attributes include:

Name

Description

primary_ind

A Y/N indicator identifying whether this is the primary contact for the supplier.

contact_type

The primary type associated to the contact such as buyer, accounts receivable, or delivery receipt.

title

The title for the contact such as Mr., Dr., Mrs., or Ms.

first_name

The first name of the contact

middle_name

The middle name of the contact.

last_name

The last name of the contact.

full_name

The full name of the contact. This is usually a combination of the first name, middle name and last name.

PHONE

The PHONE subject identifies the phone number for the supplier such as home, office, FAX, or mobile.

PHONE subject attributes include:

Name

Description

primary_ind

A Y/N indicator identifying the primary phone number for a supplier.

active_ind

A Y/N indicator identifying the phone number is currently active.

phone_type

The type of phone number, such as home, office, FAX, or mobile.

phone_number

The actual digits of the phone number.

extension

The extension portion of the phone number.

comm_status

The current status of the phone number such as unconfirmed, opt-in, or opt-out.

EMAIL

The EMAIL subject identifies the email address for the supplier, such as personal or business.

EMAIL subject attributes include:

Name

Description

primary_ind

A Y/N indicator identifying whether this is the primary email for a supplier.

email_type

The actual value of the email address.

comm_status

The type of email address, such as personal or business.

email_address

The status of an email address such as unconfirmed, opt-in, or opt-out.

Reference Tables

Omni-Gen™ for Supplier also includes the following reference tables.

These reference tables contain the acceptable list of values for the codes that appear on the main tables in the data model. Each reference table has sample values included.

  • Account types. Identifies the acceptable values for the status of an account such as unconfirmed, credit hold, active, or closed.
  • Address Status types. Identifies the acceptable values for the status of an address such as unconfirmed, active, or inactive.
  • Address types. Identifies the acceptable values for the supplier's address type such as sold to, ship to, bill to, and corporate.
  • Alternate Item ID types. Identifies the acceptable values for the type of alternate item identifier such as Global Product ID.
  • Comm Status types. Identifies the acceptable values for the status of an email or phone number such as unconfirmed, opt-in, or opt-out.
  • Contact Types. Identifies the acceptable values for the supplier's contacts such as buyer, accounts receivable, or delivery receipt.
  • Contract Types. Identifies the acceptable values for the types of contracts such as the cooperative merchandising agreement or purchasing agreement.
  • Contract Status types. Identifies the acceptable values for the status of a contract such as pending, open, or closed.
  • Credit Rating types. Identifies the acceptable values for the supplier's credit rating such as Prime, High, Speculative, Default Imminent, or In Default.
  • Email Types. Identifies the acceptable values for the supplier's email type such a home, cell, or business.
  • Geographic Area types. Identifies the acceptable values for the supplier's geographic area or region as derived from their address such as the Southern, Western, Eastern, or Northern.
  • Invoice Adjustment Reason types. Identifies the acceptable values for the type of an adjustment that was taken against an invoice.
  • Invoice Discount Reason types. Identifies the acceptable values for the type of a discount taken against an invoice.
  • Invoice Status types. Identifies the acceptable values for the status of an invoice such as received, open, closed, or approved for payment.
  • Invoice types. Identifies the acceptable values for the types of invoice such as Standard, Prepayment, or Draft.
  • Item types. Identifies the acceptable values for the types of an item such as products or services.
  • Legal Class types. Identifies the acceptable values for the legal classification of the supplier such as non-profit, trust, or sole proprietor.
  • Phone types. Identifies the acceptable values for the supplier's phone types such as home or business.
  • Order Line Status types. Identifies the acceptable values for the status of a purchase order line such as open, closed, or partially shipped.
  • Order Status types. Identifies the acceptable values for the status of a purchase order such as open, closed or partially shipped.
  • Order types. Identifies the acceptable values for a purchase order's types such as Blanket, Standing, or Standard.
  • Order Priority types. Identifies the acceptable values for the supplier's order priority such as Top 10, Customer Expedited, High, Standard, or Low.
  • Ownership types. Identifies the acceptable values for the supplier's ownership type such as woman, minority, or Indian owned.
  • Supplier Identifier types. Identifies the acceptable values for the type of alternate identifiers assigned to a supplier such as active or inactive.
  • Unit of Measure types. Identifies the acceptable values for units of measure such as tons, each, carton, or case.
  • Vendor's Payment Priority types. Identifies the acceptable values for the supplier's assigned payment priority such as High, Standard, or Low.