GM-X ERP for Blockchain - The PRODUCT subsystem

By Tony Marston

6th August 2007
Amended 30th March 2024

Introduction

1. Products

2. Product Categories

3. Product Identification

4. Product Features

5. Units of Measure

6. Product Pricing and Price Components

a Base Prices

b Order Adjustments

c Item Adjustments

d Feature Prices

7. Product Supplier

8. Product Extra Values

9. Compatible Products

10. Product Components (BOM) and Revisions

11. Phantom Products

12. Product Costing

13. Emission Factors

14. Product Controls

Amendment History

Introduction

Products are defined as goods or services that were, are, or will be sold by the enterprise. Goods are products that are more tangible in nature and generally created in advance for sale. Services are products that involve the use of parties' time and are less tangible in nature. Every organisation needs to know a great deal of product information on a regular basis, such as the following:

• What inventory is needed at each location to meet the needs of customers?
• What are the prices, costs, and profitability for the goods or services that are offered?
• Where can the enterprise purchase the best services and goods at the best prices?

Just as the PARTY model stores common information about parties independent of their roles, the product model stores common product information regardless of whose products they are. This product model is therefore more flexible, stable and understandable because product information is modeled only once, regardless of whether it is the enterprise's products, competitor's products, or supplier's products. This document models information on the following product areas:

• Product definition
• Product category
• Product identification
• Product feature
• Suppliers and manufacturers of products
• Product pricing

Product inventory is described in a separate document.

Products

Products include both tangible offerings, which are called GOODs, and non-tangible offerings, which are called SERVICEs. GOODs are physical objects which can be stored, packed and shipped, such as pens, furniture and equipment, whereas SERVICEs are for the sale of people's time or expertise, such as consulting services, management or legal services.

Figure 1 - Products

PRODUCT table

This holds the details of every product, good or service, which is available in the system.

When a product is first created it will have its CATEGORY CLASS set to 'NOTFORSALE'. This entry should not be removed until all data, such as applicable features and price components, have been established so that new products are not offered for sale prematurely.

PRODUCT_SEO table

This holds SEO (Search Engine Optimisation) data for each product which may be used in a public-facing front-end website.

Product Categories

The classification of products is a key aspect of maintaining product information. Products are often classified in many ways - by product line, by model, by product grade, by industry segments, or by various other product categories.

Figure 2 - Product Categories

PROD_CAT_LEVEL table

This can be used to organise product categories into groups or levels, so that all those which belong in a particular group or level can be easily identified. This may be used, for example, when products displayed in a web front-end (which is separate from the Radicore back-end) need to be arranged into sections, sub-sections, et cetera.

Here are some examples:

• Section
• Category
• Sub Category

The PROD_CAT_ROLLUP table should be used to identify category hierarchies, such as which "categories" belong in which "section", and which "sub-categories" belong in which "category".

PRODUCT_CATEGORY table

This holds all the possible classifications or categories which may be applied to any product.

Here are some examples:

PRODUCT_CATEGORY_SEO table

This holds SEO (Search Engine Optimisation) data for each product category which may be used in a public-facing front-end website.

PRODUCT_CATEGORY_PARTY table

This table allows product categories to be linked with parties. It can be used, for example, to identify which suppliers can provide products in a certain category.

PROD_CAT_CLASS table

This is the PRODUCT CATEGORY CLASSIFICATION table. It identifies which product categories apply to which products. A product can have any number of these classification entries. Each entry is only valid for a particular time period, so it is possible to have different entries for different dates.

Note that it is possible to categorise a product under multiple categories, but this could produce misleading results when performing certain queries. For example, if there were a sales analysis report by category, the total sales amount would be overstated as some product sales would be counted numerous times for each of their categories. The primary_flag can be used to ensure that a product's details are included for one and only one category.

Here are some examples:

PROD_CAT_ROLLUP table

This is the PRODUCT CATEGORY ROLLUP table. It is a recursive entity which allows a parent/senior category to be linked with, or divided into, any number of child/junior categories, and each of those child categories can have children of their own. This produces a hierarchy of parent-to-child/senior-to-junior categories which may go down to any number of levels. While it is possible for a parent/senior category to have any number of child/junior categories, it is not possible for a child/junior category to be linked to more than one parent/senior within the same hierarchy. If it is necessary for a child/junior category to be linked to more than one parent/senior category then you must specify a different value for hierarchy_id. This will enable multiple hierarchies to be defined, and to be able to differentiate between one hierarchy and another.

Here are some examples:

This information allows the product search screen to be given a broad category, such as "office supplies", which will automatically include all those products which belong to a child category, or a child category's children down to any number of levels.

PROD_CAT_EXTRA_NAMES table

This identifies the names of extra fields which can be held for each product category.

PROD_CAT_EXTRA_VALUES table

This holds the values for any extra fields for each product category.

Product Identification

Goods may have various ids that are used as a standard means of identification. A single code may uniquely identify goods and/or services, however, some goods may have more than one means of identification. As well as the internal value, a product acquired from outside may also have a manufacturer's id and/or a universal product code (UPC).

Global Trade Item Numbers (GTINs) are another way of identifying products and their manufacturer using identities which are assigned by a central body. These are commonly used on barcodes.

Figure 3 - Product Identification

PRODUCT_IDENTITY_TYPE table

This holds the various types of identity that can be applied to products.

Here are some examples:

PRODUCT_IDENTIFICATION table

For each product this holds all the alternative identification values.

Here are some examples:

GTIN table

This holds all the GTINs (Global Trade Item Numbers) used within the application. GTIN values are assigned by a central body and are recognised in all countries. They are normally attached to products as barcodes. They are 14 digit numbers which identify an organisation and a product, which combine to form a 13 digit number, with the last digit being a check digit. This table allows a GTIN to be translated into an internal product code and, for those products which are produced by other organisations, the identity of that organisation.

Product Features

Products may have features that could be called characteristics, options, variations, or modifiers, allowing either customisation of a product or describing the characteristics of a product. By having all this information on a single table instead of a separate table per type it then becomes possible to deal with the situation where something which is a standard feature on one product may be an optional feature on another product.

Not all features are relevant for all products, so the PROD_FEATURE_APPLICABILITY table is used to identify which features apply to which products. If all the features within a category are valid then the PROD_FEATURE_CAT_APPLIC table can be used instead.

Note that features can only be defined for those products with a product_subtype of 'G' (Goods) and not those with a product_subtype of 'S' (Service).

Product features are split into a number of different categories. Features within some categories may require additional measurement information which may be used in the pricing algorithm. For example, in a jewellery application wedding rings have a size which is expressed in millimeters, and diamonds have a size which is expressed in carats. In these cases the label and unit of measure are identified on the PRODUCT_FEATURE_CATEGORY_UOM table while the actual measurement or each feature is held on the PRODUCT_FEATURE_UOM table.

Features within some categories may have customisation options. For example, in a jewellery application a wedding ring may have an engraving option, and when that option is added to a sales order the text which needs to be engraved must also be supplied. In these cases the label and data type are identified on the PRODUCT_FEATURE_CATEGORY_CUSTOMISATION table while the PRODUCT_FEATURE_CUSTOMISATION table is used to identify whether a particular option is required or not. When a sales order is created the relevant customisation text is stored in the ORDER_ITEM_FEATURE_CUSTOMISATION table.

Figure 4 - Product Features

PRODUCT_FEATURE_CATEGORY table

This identifies all the different categories of product feature which may be used in the system. Every feature must belong to one category.

Here are some examples:

Note that there are multiple entries for Software Feature and Hardware Feature, each having a different feature_type as described in PROD_FEATURE_APPLICABILITY. This is because every feature within that category must be displayed with the same HTML control within the feature selection screen, as shown in Figure 4a.

PRODUCT_FEATURE_CATEGORY_UOM table

This identifies where the features within a category have one or more associated measurements. These measurements may be required in the price calculation algorithm.

Note that the actual measurements for individual features are defined on the PROD_FEATURE_UOM table.

Here are some examples:

PRODUCT_FEATURE_CATEGORY_CUSTOMISATION table

This identifies where a feature within a category which can be added to a product requires some customisation when it is added to a sales order. For example, an engraving service will require the text which is to be engraved.

Note that the actual customised values for individual features are defined on the PROD_FEATURE_CUSTOMISATION table.

Here are some examples:

PRODUCT_FEATURE table

This identifies all the various features, options, and variations that may be applied to any product in the system.

Note also that is possible to define a PRICE_COMPONENT for each product feature, which can be either a surcharge or a discount.

If you change the start_date and/or end_date on a PRODUCT_FEATURE record you may also affect other tables:

• Any entry on the PROD_FEATURE_APPLICABILITY table which has dates outside of this period will be updated accordingly.
• When retrieving a product's features via the PROD_FEATURE_CAT_APPLIC table, only those features which have current dates will be included.

Here are some examples:

Although it is possible to have a single product with a number of different features, such as:

• Johnson fine grade 8½ by 11 bond paper available in grey, cream, or white

it is also possible to have a separate product with each feature built in, such as:

• Johnson fine grade 8½ by 11 grey bond paper
• Johnson fine grade 8½ by 11 cream bond paper
• Johnson fine grade 8½ by 11 white bond paper

It is up to the enterprise to decide which level of specificity is required - multiple features or multiple products.

PROD_FEATURE_UOM table

This identifies where a feature has one or more measurements associated with it. These measurements may be required in the price calculations. A measurement value can only be added to a feature if its category has an entry defined on the PRODUCT_FEATURE_CATEGORY_UOM table.

PROD_FEATURE_CUSTOMISATION table

For each entry on the PROD_FEAT_CAT_CUSTOMISATION table this identifies if a customisation value for this label is required or not for a particular feature.

When a sales order which includes an item with this feature is created the actual customisation values will be written to the ORDER_ITEM_FEATURE_CUSTOMISATION table.

DEFAULT_FEATURE table

This identifies all the default features which apply to a product.

A product may have a several applicable features within a category, and one of them may be indicated as the default, in which case it will be added automatically to the relevant ORDER_ITEM_FEATURE or INVOICE_ITEM_FEATURE table.

Note that in a category only one feature is allowed to be defined as the default.

PROD_FEATURE_APPLICABILITY table

This identifies which particular features apply to which particular products, and whether the feature is standard, optional or required. Each entry is only valid for a particular time period, so it is possible to have different entries for different dates.

Note that features can only be defined for those products with a product_subtype of 'G' (Goods) and not those with a product_subtype of 'S' (Service).

Here are some examples:

Please note that for a product every feature within the same CATEGORY must have the same feature_type. This is because every feature within that category must be displayed in the same way in the product feature selection screen, and it is the feature_type which identifies which HTML control is to be used as follows:

An example of how each of these types is displayed is shown in Figure 4a below:

Figure 4a - Choosing Product Features

PROD_FEATURE_INTERACTION table

The applicability of certain product features may depend on other features which may have been selected. A pair of features may be dependent on each other, or they may be incompatible with each other.

Here are some examples:

For this product it means that the Extra Glossy Finish is available with any colour except Green, and Extra Fine Grade is only available if the colour is White.

PROD_FEATURE_CAT_APPLIC table

In some cases it may be that a feature category contains a large number of features, and that all these features apply to a product. Instead of having to allocate the features one by one it is possible to use this table to allocate an entire category. At runtime all the available features within the category will be treated as if they were on the PROD_FEATURE_APPLICABILITY table.

Note that features can only be defined for those products with a product_subtype of 'G' (Goods) and not those with a product_subtype of 'S' (Service).

PROD_CAT_FEATURE_APPLICABILITY table

This identifies which particular product features apply to which particular product categories. Each entry is only valid for a particular time period, so it is possible to have different entries for different dates. This is used when the front-end website wants a mechanism to show a list of product features for each product category.

PROD_FEATURE_COMPONENT table

This identifies where one feature is comprised of a collection of other features. For example, some features may be single metals such as yellow gold, white gold and rose gold, while another feature is comprised of a combination of all three.

Units of Measure

Different products may come in different units, such as 'each', 'litres', 'gallons', 'reams' or whatever, and some people may regard this as just another feature. Most organisations will conclude that if the same type of product is sold in different units of measure then it really is a different product. For instance, a ream of paper is different from a sheet of paper or a box of 5 reams, and each would be regarded as a separate product. It is therefore important for enterprises to be able to determine inventory for products that are identical except for the unit of measure.

Figure 5 - Units of Measure

UOM_CATEGORY table

The various units of measurement can be broken down into specific categories such as weight, length or volume, etc.

Here are some examples:

• Area
• Length
• Volume
• Weight
• Currency Codes

UNIT_OF_MEASURE table (and CURRENCY_CODE table)

This also serves as the CURRENCY_CODE table where uom_cat_id = 'CURR'

Each unit of measure has a description and an abbreviation.

Here are some examples:

UOM_CONVERSION table (and EXCHANGE_RATE table)

This provides the means to convert a quantity in one unit of measure to the equivalent quantity in a different unit of measure. For example, there may be several products of "Henry #2 pencils" that have different units of measure such as "each", "small box" and "large box". In many cases organisations need to show total inventories, costs and sales for all of a product regardless of its unit of measure. By defining a common unit of measure, such as "each", and including a conversion factor (e.g. 6 for "small box" and 24 for "large box"), it is possible to determine the total amount of "Henry #2 pencils" there are in inventory and how many have been sold.

The ability to specify a different conversion factor for a different set of dates is intended for use with currencies as the exchange rates will fluctuate over time. For all other units of measure a single factor without an end date will suffice.

Here are some examples:

Here are some examples of currency conversions:

Product Pricing and Price Components

Every organisation seems to have different mechanisms for pricing its products, and it is a challenge to produce a single data model which can cater for all the possible variations.

For most organisations there are several aspects to pricing a product:

• the base price at which the organisation sells the product
• various discounts applied against the base price such as quantity breaks
• surcharges such as freight and handling charges
• the manufacturer's suggested price

The entity is named PRICE_COMPONENT and not PRODUCT_PRICE as it is also used for other circumstances, such as agreement or contract pricing, and for discounts and surcharges (such as government taxes) which are applied at the order level instead of the product/item level.

Figure 6 - Price Components

PRICE_COMPONENT table

This holds all the price components (product prices, discounts and surcharges) which are used in the compilation of sales orders. Each entry is only valid for a particular time period, so it is possible to have different entries for different dates.

Note that this table does not specify any currency codes for monetary values as every price is deemed to be in the currency of the Functional Unit which created it. If a document requires values to be expressed in a foreign currency this is handled differently depending on the subsystem:

• In the Order Processing system all monetary values are entered in foreign/transaction currency and stored in the <value>_tx column. Each value will be converted to home/functional currency immediately and stored in the <value> column. The identity of the transaction currency will be stored in the currency_code column, and the exchange rate stored in the exchange_rate column. The identity of the home/functional currency is taken as that which belongs to the user's parent organisation.
• In the Invoice subsystem all monetary values are entered in foreign/transaction currency and stored in the <value>_tx column. Each value will be converted to home/functional currency immediately and stored in the <value> column. The identity of the transaction currency will be stored in the currency_code_tx column, and the exchange rate stored in the exchange_rate column. The identity of the home/functional currency will be stored in the currency_code_fn column.

Price components can be divided into the following categories:

• Base prices - the starting price for a product.
• Order adjustments - discounts or surcharges which apply to a whole sales order.
• Item adjustments - discounts or surcharges which apply to particular products within a sales order.
• Feature Prices - for optional item features which are selected in a sales order.

Although it is possible to have many combinations of identifiers on each price component record it should be noted that the following validation rules apply:

• Each price component has its own range of dates for for which that value or percentage applies. A new record with the same combination of identifiers can be used to define a change in value or percentage, but that record cannot have dates which overlap any other records with the same identifiers. On any given date only one record with a particular set of identifiers can be valid.
• Each record must have one, and only one, of the following: value, percent, free_units, or product_id_free.
• If price_type is set to "Base Price" then value and product_id must be specified.
• If price_type is set to "MRP" then value and product_id must be specified.
• free_units is only valid with a quantity break.
• product_id_free is only valid with a quantity break or a value break.
• Cannot use both product_id and prod_cat_id.
• Can only use one customer_id, supplier_id and party_subclass_id.
• If price_type is set to "Discount" or "Surcharge" then either order_adjustment_type_id, item_adjustment_type_id or product_feature_id must be specified.
• If price_frequency is set to "One Time Charge" then uom_id must be empty.
• If price_frequency is set to "Recurring Charge" or "Utilisation Charge" then uom_id must not be empty.
• If order_adjustment_type_id is not empty then the following rules apply:
  • price_type must match the value in discount_or_surcharge for the adjustment type
  • item_adjustment_type_id must be empty
  • product_id must be empty
  • prod_cat_id is optional (used to identify the tax category)
  • prod_feature_id must be empty
  • quantity_break_id must be empty
  • supplier_id must be empty
  • product_id must be empty
• If item_adjustment_type_id is not empty then the following rules apply:
  • price_type must match the value in discount_or_surcharge for the adjustment type
  • order_adjustment_type_id must be empty
  • Either product_id OR prod_cat_id must be supplied
  • prod_feature_id must be empty
  • value_break_id must be empty
  • supplier_id must be empty
• If prod_feature_id is not empty then the following rules apply:
  • product_id is optional
  • price_type must be either "Discount" or "Surcharge"
  • price_frequency must be "One Time Charge"
  • value must not be empty
  • order_adjustment_type_id must be empty
  • item_adjustment_type_id must be empty
  • prod_cat_id must be empty
  • party_subclass_id must be empty
  • supplier_id must be empty
  • customer_id must be empty
  • sale_type_id must be empty
• If quantity_break_id is not empty then the following rules apply:
  • price_type must be set to "Discount"
  • item_adjustment_type_id must not be empty
  • product_id must not be empty
  • customer_id must be empty
  • party_subclass_id must be empty
  • prod_feature_id must be empty
  • area_id must be empty
• If value_break_id is not empty then the following rules apply:
  • price_type must be set to "Discount"
  • order_adjustment_type_id must not be empty
  • party_subclass_id must be empty
  • customer_id must be empty
  • prod_cat_id must be empty
  • product_id must be empty
  • prod_feature_id must be empty
  • area_id must be empty
• If area_id is not empty then the following rules apply:
  • party_subclass_id must be empty
  • supplier_id must be empty
  • customer_id must be empty
  • prod_cat_id must be empty
  • product_id must be empty unless item_adjustment_type_id is not empty
  • prod_feature_id must be empty

Base Prices

These identify the starting price for a product before the addition of any Feature Prices or Item Adjustments. Several variations are possible.

1. The default price for a product

   field	value
   price_type	Set to "Base Price".
   price_frequency	Set to "One Time Charge".
   product_id	The product identity
   value	The base price, in home currency (or the currency of the Functional Unit).
   sale_type_id	Optional. See SALE_TYPE for a possible range of values.
   start_date	The date on which this price comes into effect.

2. The default price for a product for a customer (agreement pricing or contract pricing)

   field	value
   price_type	Set to "Base Price".
   price_frequency	Set to "One Time Charge".
   product_id	The product identity
   value	The agreed price for this customer, in home currency (or the currency of the Functional Unit).
   customer_id	The customer (party) identity.
   sale_type_id	Optional. See SALE_TYPE for a possible range of values.
   start_date	The date on which this price comes into effect.

3. The default price for a product for a customer class

   field	value
   price_type	Set to "Base Price".
   price_frequency	Set to "One Time Charge".
   product_id	The product identity
   value	The base price, in home currency (or the currency of the Functional Unit).
   party_subclass_id	The party classification.
   sale_type_id	Optional. See SALE_TYPE for a possible range of values.
   start_date	The date on which this price comes into effect.

It is therefore possible to set up different base prices for the following:

• A single, specific customer (this is known as agreement pricing or contract pricing).
• Every customer who has a particular classification.
• The default price for every customer.
• All of the above can also be qualified by sale type.

When a product is added to a sales order a search is made for the base price which applies on the current date in the following sequence, with the search terminating at the first successful lookup:

1. Lookup using product_id, customer_id, and sale_type_id (if one has been supplied).
2. Lookup using product_id, customer_id, but without sale_type_id.
3. Lookup using product_id, party_subclass_id, and sale_type_id (if one has been supplied).
4. Lookup using product_id, party_subclass_id, but without sale_type_id.
5. Lookup using product_id, and sale_type_id (if one has been supplied).
6. Lookup using product_id, but without sale_type_id.

Order Adjustments

These are discounts or surcharges which are applied to the total net value of a sales order, and not just an individual item within that order. A discount or surcharge may be defined either as a fixed amount or a percentage. A discount may also be be defined as one or more value breaks where each entry should be for a different and non-overlapping range of values. Discounts and surcharges will be applied unconditionally, whereas a value break will only be applied when the total value for that order falls within the range on that entry.

For discounts and surcharges one of the following must be specified:

• EITHER: a fixed amount in home currency
• OR: a percentage to be applied to the net value

For value breaks one of the following must be specified:

• EITHER: a fixed amount in home currency
• OR: a percentage to be applied to the net value
• OR: a single free unit of a different product

These adjustments will be applied in the following order:

1. Discounts
2. Surcharges
3. Sales Taxes

Note that the following rules apply to entries where order_adjustment_type_id begins with 'TAX':

• If the country_id the functional unit is in the USA and the customer is an organisation then sales taxes are NOT applicable.
• The country_id_party will be detemined as follows:
  • For Purchase Orders it will be the country_id of the supplier's address
  • For Sales Orders it will be the country_id of the customer's delivery address.
• If the country_id of the functional unit is the same as the country_id_party then sales taxes ARE applicable.
• If both the functional unit and the customer reside in the European Union then sales taxes ARE applicable.
• If the functional unit and the customer are in different countries then sales taxes are NOT applicable.
• The lookup will be performed using the tax_address which will be determined as follows:
  • For Purchase Orders it will be the country_id of the supplier's address
  • For Sales Orders it will be the country_id of the customer's delivery address.

Note that the following rules apply to entries where order_adjustment_type_id begins with 'DELIVERY':

• The lookup will be performed using the area_list from the country_id of the delivery address.

Note that entries with the following price_types will be handled separately:

• 'E' - Early Settlement Discounts
• 'W' - Withholding Tax
• 'C' - Commission

For the UK there are currently four tax categories - Standard rate, Reduced rate, Zero rated and Exempt - and the tax amount for each category will be calculated on the net total for products within that category. All products will default to the Standard rate (VAT-S) unless they have been assigned to a different category.

1. A discount to be applied to the total value of a order

   field	value
   order_adjustment_type_id	Required. Select one that has discount_or_surcharge set to "Discount".
   price_type	Set to "Discount".
   price_frequency	Set to "One Time Charge".
   prod_cat_id	Must be empty.
   value_break_id	Optional. This identifies the range of values for which this discount applies.
   value	Optional. Either value or percent must be supplied.
   percent	Optional. Either value or percent must be supplied.
   area_id	Optional. Cannot be used with customer_id or party_subclass_id.
   customer_id	Optional. Cannot be used with area_id or party_subclass_id.
   party_subclass_id	Optional. Cannot be used with area_id or customer_id.
   sale_type_id	Optional. See SALE_TYPE for a possible range of values.
   start_date	The date on which this discount comes into effect.

   There is another type of order discount known as a Discount Code (or Voucher). This is only added to an order upon the input of a valid Discount Code. These codes are only valid for a particular time period or up to a particular quantity, and may be published in various promotional materials.

2. A surcharge to be applied to the total value of a order.

   field	value
   order_adjustment_type_id	Required. Select one that has discount_or_surcharge set to "Surcharge".
   price_type	Set to "Surcharge".
   price_frequency	Set to "One Time Charge".
   prod_cat_id	Must be empty.
   value_break_id	Must be empty.
   value	Optional. Either value or percent must be supplied.
   percent	Optional. Either value or percent must be supplied.
   area_id	Optional. Cannot be used with customer_id or party_subclass_id.
   customer_id	Optional. Cannot be used with area_id or party_subclass_id.
   party_subclass_id	Optional. Cannot be used with area_id or customer_id.
   sale_type_id	Optional. See SALE_TYPE for a possible range of values.
   start_date	The date on which this surcharge comes into effect.

3. A Sales Tax to be applied to the total value of a order (may have a different entry for each tax category)

   field	value
   order_adjustment_type_id	Required. Select "Sales Tax".
   price_type	Set to "Surcharge".
   price_frequency	Set to "One Time Charge".
   prod_cat_id	Required. Select one of the product categories which has been set up for Sales Tax. Please refer to FAQ #1 for details.
   value_break_id	Must be empty.
   value	Optional. Either value or percent must be supplied.
   percent	Optional. Either value or percent must be supplied.
   area_id	Required.
   customer_id	Must be empty.
   party_subclass_id	Must be empty
   sale_type_id	Optional. See SALE_TYPE for a possible range of values.
   start_date	The date on which this surcharge comes into effect.

When a sales order is created a search is made for any order adjustments which are applicable on the current date. This search has to take into account a combination of the following:

• Only those entries from the ORDER_ADJUSTMENT_TYPE table which have is_manual set to 'N'.
• Customer:
  • A single customer identified in the customer_id column.
  • A group of customers who share the same party_subclass_id column.
  • A group of customers who share the same address which contains an geographical area identified in the area_id column. This may be as large as an entire country, a county/state/region or as small as a city/town.
  • All customers, where customer_id, party_subclass_id and area_id are empty.
• Sale Type:
  • A single type identified in the sale_type_id column.
  • All sale types, where sale_type_id is empty.

Each combination of these values will involve a separate database lookup which will be given a priority number with the narrowest of selections having lower numbers than the broadest. Thus a value for a particular product and particular customer will have a higher priority than a value for all products and all customers. This priority number will be allocated as follows:

1. Deal with adjustments which are not TAX and not DELIVERY
   1. All adjustments for a specific customer_id.
   2. All adjustments for customers within a particular area_id.
   3. All adjustments for customers with a particular party_subclass_id.
   4. All adjustments for all customers.
2. Deal with TAX adjustments
   1. All adjustments for customers whose delivery address is within a particular area_id.
3. Deal with DELIVERY adjustments
   1. All adjustments for customers whose delivery address is within a particular area_id.

Each of these lookups will be performed twice - once with a sale_type_id and once without.

If multiple entries for the same order_adjustment_type_id are found with different priorities then only those entries with the lowest priority will be added to the ORDER_ADJUSTMENT table.

Note also that if any price component record has related entries on the PARTY_PRICE_EXCLUSION table then any customer who has any of the same party_subclass_id entries will be excluded from that adjustment.

Item Adjustments

These are discounts or surcharges which apply to a particular product (item) within a sales order. If the discount/surcharge is a value or a percentage it will be applied to the unit price (which may also include any feature prices) before being multiplied by the order quantity to give the extended price (item value). A discount may also be be defined as one or more quantity breaks where each entry should be for a different and non-overlapping range of quantities. Discounts and surcharges will be applied unconditionally, whereas a quantity break will only be applied when the total quantity for that product_id falls within the range on that entry.

For discounts and surcharges one of the following must be specified:

• EITHER: a fixed amount in home currency
• OR: a percentage to be applied to the (unit_price + feature_price)

For quantity breaks one of the following must be specified:

• EITHER: a fixed amount in home currency
• OR: a percentage to be applied to the (unit_price + feature_price)
• OR: a number of free_units of the same product
• OR: a single free unit of a different product

1. A surcharge to be applied to an order item

   field	value
   item_adjustment_type_id	Required. Select one that has discount_or_surcharge set to "Surcharge".
   price_type	Set to "Surcharge".
   price_frequency	Set to "One Time Charge".
   EITHER: product_id	A single product identity or '*' to signify ALL products.
   OR: prod_cat_id	To signify ALL products which have this prod_cat_id on the PROD_CAT_CLASS table.
   quantity_break_id	Must be empty.
   value	Optional. Either value or percent must be supplied.
   percent	Optional. Either value or percent must be supplied.
   area_id	Optional.
   customer_id	Optional. Cannot be used with area_id or party_subclass_id.
   party_subclass_id	Optional. Cannot be used with area_id or customer_id.
   sale_type_id	Optional. See SALE_TYPE for a possible range of values.
   start_date	The date on which this surcharge comes into effect.

2. A discount to be applied to an order item

   field	value
   item_adjustment_type_id	Required. Select one that has discount_or_surcharge set to "Discount".
   price_type	Set to "Discount".
   price_frequency	Set to "One Time Charge".
   EITHER: product_id	A single product identity or '*' to signify ALL products.
   OR: prod_cat_id	To signify ALL products which have this prod_cat_id on the PROD_CAT_CLASS table.
   quantity_break_id	Must be empty.
   value	Optional. Either value or percent must be supplied.
   percent	Optional. Either value or percent must be supplied.
   area_id	Optional.
   customer_id	Optional. Cannot be used with area_id or party_subclass_id.
   party_subclass_id	Optional. Cannot be used with area_id or customer_id.
   sale_type_id	Optional. See SALE_TYPE for a possible range of values.
   start_date	The date on which this discount comes into effect.

3. A quantity break to be applied to an order item

   field	value
   item_adjustment_type_id	Required. Select one that has discount_or_surcharge set to "Discount".
   price_type	Set to "Discount".
   price_frequency	Set to "One Time Charge".
   EITHER: product_id	A single product identity or '*' to signify ALL products.
   OR: prod_cat_id	To signify ALL products which have this prod_cat_id on the PROD_CAT_CLASS table.
   quantity_break_id	Required. This identifies the range of quantities for which this discount applies.
   value	Optional. Either value, percent, free_units or product_id_free must be supplied.
   percent	Optional. Either value, percent, free_units or product_id_free must be supplied.
   free_units	Optional. Either value, percent, free_units or product_id_free must be supplied.
   product_id_free	Optional. Either value, percent, free_units or product_id_free must be supplied.
   area_id	Optional.
   customer_id	Optional. Cannot be used with area_id or party_subclass_id.
   party_subclass_id	Optional. Cannot be used with area_id or customer_id.
   sale_type_id	Optional. See SALE_TYPE for a possible range of values.
   start_date	The date on which this discount comes into effect.

When a product is added to a sales order a search is made for any item adjustments which are applicable on the current date. This search has to take into account a combination of the following:

• Only those entries from the ITEM_ADJUSTMENT_TYPE table which have is_manual set to 'N'.
• Customer:
  • A single customer identified in the customer_id column.
  • A group of customers who share the same party_subclass_id column.
  • A group of customers who share the same primary_address which contains an geographical area identified in the area_id column. This may be as large as an entire country, a county/state/region or as small as a city/town.
  • All customers, where customer_id, party_subclass_id and area_id are empty.
• Product:
  • A single product identified in the product_id column (but not '*').
  • A group of products who share the same prod_cat_id column.
  • All products, using the value '*' which exists as a dummy product.
• Sale Type:
  • A single type identified in the sale_type_id column.
  • All sale types, where sale_type_id is empty.

Each combination of these values will involve a separate database lookup which will be given a priority number with the narrowest of selections having lower numbers than the broadest. Thus a value for a particular product and particular customer will have a higher priority than a value for all products and all customers. This priority number will be allocated as follows:

1. A specific product for a specific customer_id.
2. A specific product for customers within a particular area_id.
3. A specific product for customers with a particular party_subclass_id.
4. A specific product for all customers.
5. All products within a particular prod_cat_id for a specific customer_id.
6. All products within a particular prod_cat_id for customers within a particular area_id.
7. All products within a particular prod_cat_id for customers with a particular party_subclass_id.
8. All products within a particular prod_cat_id for all customers.
9. All products for a specific customer_id.
10. All products for customers within a particular area_id.
11. All products for customers with a particular party_subclass_id.
12. All products for all customers.

Each of these lookups will be performed twice - once with a sale_type_id and once without.

Note also that if any price component record has related entries on the PARTY_PRICE_EXCLUSION table then any customer who has any of the same party_subclass_id entries will be excluded from that adjustment.

If multiple entries for the same item_adjustment_type_id are found with different priorities then only those entries with the lowest priority will be added to the ORDER_ITEM_ADJUSTMENT table.

Feature Prices

A product may come with a number of features which may be selected in a sales order, and these will be treated as free unless a feature price has been defined. A feature price may be either a discount or a surcharge, and will be added to the unit price for that product before the effects of any Item adjustments are calculated. Several variations are possible.

1. The price for a feature with a particular product

   field	value
   item_adjustment_type_id	Set to "Feature Charge".
   price_type	Set to discount_or_surcharge value on associated ITEM_ADJUSTMENT_TYPE table for this item_adjustment_type_id.
   price_frequency	Set to "One Time Charge".
   product_id	The product identity. Must not be '*'.
   prod_feature_id	The feature identity.
   value	The feature price, in home currency (or the currency of the Functional Unit).
   start_date	The date on which this price comes into effect.

2. The price for a feature regardless of product

   field	value
   item_adjustment_type_id	Set to "Feature Charge".
   price_type	Set to discount_or_surcharge value on associated ITEM_ADJUSTMENT_TYPE table for this item_adjustment_type_id.
   price_frequency	Set to "One Time Charge".
   product_id	Set to '*' to mean "all products".
   prod_feature_id	The feature identity.
   value	The feature price, in home currency (or the currency of the Functional Unit).
   start_date	The date on which this price comes into effect.

When a feature is added to a product in a sales order a search is made for any price adjustment which applies on the current date in the following sequence, with the search terminating at the first successful lookup:

1. Lookup using prod_feature_id and product_id.
2. Lookup using prod_feature_id and product_id = '*'.

PARTY_PRICE_EXCLUSION table

This identifies all those classes of party who are excluded from particular price components (order adjustments or item adjustments) when a sales order is being compiled. Each entry is only valid for a particular time period, so it is possible to have different entries for different dates.

A customer (party) can be classified with any number of different PARTY_SUBCLASS_LINK entries. When order adjustments or item adjustments are defined on the PRICE_COMPONENT table they automatically apply to every customer. However, it may be required to exclude particular parties from certain price adjustments. This can be done as follows:

• Create an entry on the PARTY_SUBCLASS table which can be used for a particular reason for exclusion.
• Create entries on the PARTY_SUBCLASS_LINK table to identify the parties who qualify for this exclusion.
• Create entries on the PARTY_PRICE_EXCLUSION table to identify which PARTY_SUBCLASS entries apply to which PRICE_COMPONENT entries.

When looking for either order adjustments or item adjustments during the compilation of sales orders, these will automatically apply to every customer except those who share a party classification which has been defined on the exclusion table.

Product Supplier

This shows which suppliers are identified with which product, with their lead times. A product may be available from more than one supplier, so entries can be sorted by preference.

Figure 7 - Product Supplier

PREFERENCE_TYPE table

This identifies a preference type or priority that may be linked with product replenishment details.

Here are some examples:

• Preferred
• 1st Alternate
• 2nd Alternate
• 3rd Alternate

RATING_TYPE table

This is used to rate the overall performance of a product which is obtained from a particular supplier.

Here are some examples:

• Poor
• Fair
• Good
• Outstanding

PRODUCT_SUPPLIER table

This identifies which products are available from which suppliers.

Product Extra Values

This database may be used by other systems, such as a web front end, which may have a requirement for extra fields to be held for each product. There is a NAMES table which holds the names of any such extra fields, with their validation types, and a VALUES table which holds a corresponding value for each product.

Although these values can be held in the database they cannot be considered when processing any business rules as their names and meanings are not part of the standard software.

Figure 8 - Product Extra Values

PRODUCT_EXTRA_NAMES table

This identifies the names of extra fields which can be held for each product.

PRODUCT_EXTRA_VALUES table

This holds the values for any extra fields for each product.

Compatible Products

There may be circumstances where you wish to identify where one product is compatible with another. This will enable you, for example, to suggest one or more compatible products whenever a customer makes a purchase.

When two products are associated in this way the association need only be made once, yet it will be available whichever of the two records is selected. Thus if you link 'product-A' with 'product-B' it will also serve as a link between 'product-B' and 'product-A'.

Figure 9 - Compatible Products

COMPATIBLE_PRODUCTS table

This identifies where one product is compatible with another.

Product Components (BOM) and Revisions

As well as selling individual products, an organisation may sell products which are collections of or assembled from other products. This may be something as simple as a gift box which contains a mixture of items, or it may be used in a manufacturing environment to indicate which items are needed in order to assemble a finished product. These are sometimes known as Bill of Materials or BOM.

It is possible for a product to have different types of BOM for different purposes.

Certain products may also be the subject of various revisions during their lifetime, in which case a history of the various revision numbers and their effective dates will need to be maintained.

Figure 10 - Product Components and Revisions

BOM_TYPE table

This identifies the different Bill Of Material (BOM) types which are available. At least one entry must exist in this table otherwise the system will not allow PRODUCT_COMPONENT entries to be created. If only one entry exists then the system will automatically treat it as the default, whether or not it is so marked.

Note that it is possible for a single PRODUCT to have multiple BILL OF MATERIAL TYPEs, depending upon business requirements. For example a single PRODUCT could have an engineering bill of material as well as a manufacturing bill of material. If that PRODUCT is revision controlled then multiple versions of either its engineering bill or material or its manufacturing bill of material could exist.

Here are some examples:

PRODUCT_BOM_TYPE table

This identifies which BILL OF MATERIAL TYPEs are allowed for each PRODUCT.

When new PRODUCT COMPONENTs are added for a product, only the PRODUCT BILL OF MATERIAL TYPEs for the senior product can be selected. If one of the available BILL OF MATERIAL TYPEs is marked as the default, it will be pre-selected. The user may select a different BILL OF MATERIAL TYPE before the PRODUCT COMPONENT is added. Otherwise the system will add the default BILL OF MATERIAL TYPE to the PRODUCT BILL OF MATERIAL TYPE table if it does not already exist.

PRODUCT_COMPONENT table

This identifies where one product is comprised of other components.

CONFIG_CONDITION_TYPE table

This identifies the different product configuration condition types which may exist.

Here are some examples:

• Adaptation
• Basic
• Complementary

CONFIG_CONDITION_SUBTYPE table

This identifies the different product configuration condition subtypes which may exist.

PRODUCT_REVISION table

This identifies the different revisions or versions through which a product may progress during its lifetime. Entries on this table are only valid if the is_revision_controlled flag on the PRODUCT table is set to 'Yes'. Note that more than one revision can be valid at any one time, thus allowing a period of overlap between one revision and another. However, each revision must have a different effective_date otherwise it will not be possible to identify which one is the latest.

PROD_REV_STATUS table

This identifies the different revision status values that may exist.

Here are some examples:

• Pending
• Approved
• Superseded

PROD_REV_STATUS_HIST table

This identifies the history of status changes for each product revision.

Phantom Products

According to APICS The Association for Operations Management (section 5.5.7.1), Phantom bills are a coding and structuring technique used primarily for transient sub assemblies. The phantom bill represents an item that may not exist physically but is treated as an accounting unit.

A phantom bill of materials is a logical BOM representing:

• A group of products for planning purposes; or
• A modular collection of components to be included in other BOMs and consumed, as if it were a kit, when the senior products of those other BOMs are made. This technique can greatly simplify BOM maintenance when a single collection is needed for many different manufacturing, assembly, kitting, assortment, configurable or service BOMs, or recipes, but that collection is never produced or procured separately. A material requirements planning (MRP) system will treat the phantom components as if they were part of the BOM for the senior product, rather than treating the phantom as a separate product that needs to be produced or procured.

As such, phantom items never actually exist.

The GM-X ERP application uses the following rules for phantom products:

• Always zero lead time days
• Inventory type is always 'No Tracking'
• No inventories
• No product suppliers
• Cannot be applied to price components
• Cannot be ordered via purchase, sales or transfer orders; and cannot be included as a free good on those orders
• Cannot be billed via a customer or vendor invoice, credit note or debit note
• Cannot have requirements
• Cannot be included on requests or quotes
• Cannot apply product features, feature categories and feature interactions
• Products having the product_subtype 'S' (Service) cannot be components of BOMs for senior products which are phantoms

These rules are enforced for phantom products; and also whenever at attempt is made to change the product_subtype from 'G' (Good) or 'S' (Service) to 'P' (Phantom), except that changes from 'G' (Good) or 'S' (Service) to 'P' (Phantom) are allowed even if price component records exist for the affected product but in this case those price component records will not be utilised for any subsequent orders or invoices.

Otherwise, the GM-X ERP application allows phantom products to be used in the same way as any other product:

• Phantom products can be revision-controlled
• Each phantom product can have multiple BOMs of any BOM type
• Phantom products can be components of any BOM, including BOMs for senior products which are also phantoms
• All product costing capabilities are available for phantom products, although only prior-level costs are relevant

Product Costing

Introduction

A product costing system (also called a cost accounting system or costing system) is a framework used by firms to estimate the cost of their products for profitability analysis, inventory valuation and cost control.

Estimating the accurate cost of products is critical for profitable operations. A firm must know which products are profitable and which ones are not, and this can be ascertained only when the costs of each product are estimated correctly. Further, a product costing system helps in estimating the closing value of materials inventory, work-in-progress and finished goods inventory for the purpose of financial statement preparation.

Many firms select "standard cost" as the valuation type for product costing. A standard cost system determines cost per unit based on some reasonable historical or expected cost. Studies of past and estimated future cost data can then provide the basis for standard costs. Because the results of such studies will fluctuate over time, many firms also differentiate between:

• Current standard cost: this is the standard cost per unit reflecting the most recent study of past or estimated future costs; and
• Frozen standard cost: also known as "budgetary standard cost", this is the standard cost per unit which the firm has adopted as its benchmark for analysing cost performance over a particular time frame, such as the current quarter or current fiscal year. It normally reflects a snapshot of current standard cost per unit at the beginning of the time frame, and remains frozen until a decision is made to change it.

The "actual cost" is a valuation type which reflects the actual out-of-pocket expense per unit incurred for procuring or making a batch or lot of a product (such as the average cost per unit of a purchase or production run), or an individual product unit. The actual cost to produce a product may differ from the estimated standard cost. For management control, the actual cost is compared to the standard cost for a specific item and differences, or "variances", are identified and analysed.

Product costs are better understood and controlled when they are broken down according to "cost elements" which are the factors of production or activities that are the fundamental drivers of cost performance. In their most basic form, these are:

• Material: also known as "direct material", this element reflects any costs and variances associated with the procurement of products or material which is either re-sold by distributors, or consumed by manufacturers;
• Labour: also known as "direct labour", this element reflects any costs and variances associated with the expenditure of human effort needed to carry out specific production activities; and
• Overhead: this element reflects any costs and variances associated with the consumption of capital resources such as machinery and equipment which are needed to carry out specific production activities ("direct overhead"). It may also reflect the consumption of additional capital resources (such as factory buildings, energy or contract manufacturing services) as well as human effort (such as managerial, supervisory, administrative or maintenance activities) which are needed for production but are not definitely related to specific production activities ("indirect overhead").
• Yield Loss: this element reflects any costs and variances associated with losses attributable to production yields or component scrap.

In the GM-X ERP application, these basic elements are known as "cost element types" and are system-defined. Users may identify any number of cost elements reflecting material consumption, yield losses, material overhead consumption, labour expenditure, machinery and equipment utilisation and labour overhead consumption which can be defined as production factors or as activity cost pools for activity-based costing. Each such cost element is linked to a cost element type.

Manufacturers, and firms which perform kitting of purchased products or engage contract manufacturers to make semi-finished and finished products, will utilise bills of material (BOMs) which allow costs to be further broken down by level:

• Prior level: material, labour and overhead costs of materials that are consumed when making semi-finished and finished products; and
• This level: labour and overhead costs incurred to make semi-finished and finished products, or costs incurred to purchase saleable products, raw materials and other BOM components.

When rolling up costs through a BOM structure, some firms find it useful to combine labour and overhead with material costs at prior levels, whilst others do not. An indicator for this purpose is defined in the Product Controls task. Note that in this context "prior" level means a "lower" level in the BOM hierarchy.

Organisational Structure

The PARTY and ORGANISATION tables contain entries for every organisation which is required to be known to the GM-X application.

At least one of these parties is an organisation which is the "client" and it is the one for which the application is being run. This organisation is known as the "functional unit" and can be identified by adding it to the PARTY and ORGANISATION tables using a single party_id. This data provides the identity of the internal party in various transactions, such as sales orders and invoices.

It is also possible for a GM-X installation to handle the business for a group of organisations which do business under separate names, possibly using different functional currencies. Those organisations can be added as functional units to the PARTY and ORGANISATION tables. Each of them is identified in those tables by its own party_id. However, because all these organisations share the same product data, they will also share the same standard costs which, in turn, must be denominated in a common currency. The currency in which GM-X product costs are denominated is defined in the Product Controls task.

The GM-X application allows revision control to be enforced for specific products. Total cost per unit could vary for each revision of the same product due to BOM differences, as well as process differences that affect resource utilisation, yields or scrap.

The GM-X application also allows alternate BOM types to be defined for each product, or product revision. Total cost per unit could vary for each BOM type due to BOM differences, and those BOM differences might in turn introduce process differences.

Figure 11 - Product Costing

COST_ELEMENT table

This identifies cost elements reflecting material consumption, yield or scrap losses, material overhead consumption, labour expenditure, machinery and equipment utilisation and labour overhead consumption which can be defined as production factors or as activity cost pools for activity-based costing.

PRODUCT_COST table

This identifies the history of status changes for each product revision.

The PRODUCT_COST table also an alternate unique index comprising the product_id, bom_type_id, revision_id, valuation_type and cost_element_id to include the optional bom_type_id and revision_id elements.

Emission Factors

EMISSION_FACTOR table

This table identifies the emission factors for carbon dioxide (CO2), methane (CH4), and nitrous oxide (N2O) greenhouse gases (GHG) associated with a particular PRODUCT (for fuels), VEHICLE (for transport vehicles), or FACILITY (for energy consuming facilities).

An emission factor (EF) is a representative value that relates the quantity of a pollutant released to the atmosphere with an activity associated with the release of that pollutant. For GHG, EFs are typically expressed as the mass of a gas per unit of the emissions-producing activity or material input, such as kilograms of carbon dioxide (CO2) emitted per tonne of bituminous coal combusted.

To estimate emissions, an EF is multiplied by the corresponding activity data such as the energy contained in a mass of fuel combusted, or the amount of electricity consumed. Activity data need to be converted to match the units used by the EF to calculate GHG emissions. The basic formula is:

    Activity data x Emission factor = GHG emissions

Product Controls

Various configuration options may sometimes be required for which there is no suitable place to hold them. In this case they are held within the MNU_CONTROL table in the MENU database which acts a as KEY-VALUE store.

PRODUCT_CONTROLS table

This holds various configuration settings.

Date created: 6th August 2007

Amendment history:

Copyright © 1999-2024 by Geoprise Technologies Licensing, All Rights Reserved.