GM-X ERP for Blockchain - The ORDER subsystem

By Tony Marston

13th August 2007
Amended 12th April 2023

Introduction

1. Orders, Items and Item Features

2. Order Roles and Item Roles

3. Order Contact Mechanisms

4. Order Status and Item Status

5. Order Terms and Item Terms

6. Quantity Breaks and Value Breaks

7. Order Adjustments and Item Adjustments

8. Order Payments

9. Reserved Items

10. Non-Conformance Reports

11. Building Purchase Orders from Sales Orders

12. Links to other entities

13. Order Extra Values

14. Order Item Extra Values

15. Saved Shopping Basket

16. Order Control Data

17. Initial Values

Amendment History

Introduction

Orders are the mechanism by which one party requests products from another party, and can be one of the following:

• Sales Orders - requests made upon the enterprise from customers.
• Purchase Orders - requests made by the enterprise on suppliers.
• Transfer Orders - requests to move inventory from one facility within the organisation to another.

While there are differences between these order types, there are enough similarities for them to share the same model.

There can be a great deal of information associated with each order, such as:

• Who placed the order?
• What products were ordered, and in what quantities?
• Do any products require optional features?
• To whom is the order being shipped?
• Who is responsible for paying for the order?
• When is the expected delivery time, and are there consequences for late delivery?
• What people and organisations are involved in the order?
• What is the price for each product in the order? Is it a standard price or a specially negotiated price?
• Are there any discounts or surcharges which must be taken into consideration?

Orders, Items and Item Features

Each order has one or more order items, and each order item identifies a product and a quantity. An order item may have any number of features, and may be related to other order items.

Figure 1 - Orders, Items and Item Features

SALE_TYPE table

This is used to identify when different methods of selling are possible so that reports by sale type can be produced. This can also be used to provide a separate PRICE_COMPONENT for each sale type.

Here are some examples:

ORDER_HEADER table

This holds information about the order as a whole.

When a sales order is created the following steps are performed:

1. When the customer is identified the following lookups are made using the PARTY_CONTACT_MECHANISM and PARTY_CONTACT_MECH_PURPOSE tables:

   • Look for a contact mechanism which has been marked as the "Primary Address".
   • Look for a contact mechanism which has been marked as the "Billing Address".
   • Look for a contact mechanism which has been marked as the "Delivery Address".
   • Look for a contact mechanism which has been marked as the "Confirmation Address".

   Although default contact mechanisms for each of these purposes may have been defined in the database, each one can be overridden to provide alternative values for that particular order.

2. After the details have been written to the ORDER_HEADER table the following records are also added:

   • A record is added to the ORDER_STATUS_HIST table which sets the order status to "pending".

   • A lookup is made on the PRICE_COMPONENT table for any order_adjustments which satisfy the following criteria:

     • The associated ORDER_ADJUSTMENT_TYPE must have is_manual set to "AUTOMATIC".
     • Any entries which have an area_id which includes the customer's primary address. This is typically for sales tax, so if no primary address has been defined it will not be possible to determine if any sales tax is applicable on this order.
     • Any entries which have customer_id set to the current customer, or, if none are found, any entries which have a party_subclass_id which is being used by the current customer.
     • Any entries which are not linked to area_id, customer_id or party_subclass_id.

     All entries which are found in any of these categories will be written to the ORDER_ADJUSTMENT table.

ORDER_ITEM table

This holds information on individual items with an order. Each item specifies a product and a quantity, and may have related records on ORDER_ITEM_FEATURE and ORDER_ITEM_ADJUSTMENT.

When an item is added to a sales order the following steps are performed:

1. Look for a unit_price for the product on the PRICE_COMPONENT table in the following sequence:
   • An entry which has customer_id set to the current customer.
   • An entry which has party_subclass_id set to a value being used by the current customer.
   • An entry which is not limited by customer_id or party_subclass_id.

   The first entry which is found will be used.

2. Look on the PROD_FEATURE_APPLICABILITY table for any features marked as "standard" or "required", and add them to the ORDER_ITEM_FEATURE table.
3. A lookup is made on the PRICE_COMPONENT table for any item adjustments which satisfy the following criteria:
   • The associated ITEM_ADJUSTMENT_TYPE must have is_manual set to "AUTOMATIC".
   • Any entries which have an area_id which includes the customer's primary address.
   • Any entries which have customer_id set to the current customer, or, if none are found, any entries which have a party_subclass_id which is being used by the current customer.
   • Any entries which are not linked to area_id, customer_id or party_subclass_id.

   All entries which are found in any of these categories will be written to the ORDER_ITEM_ADJUSTMENT table.

4. A new screen is activated which will show any product features which have been marked as "optional" or "required".

ORDER_ITEM_FEATURE table

A product may have any number of features, optional or otherwise, which may be incorporated into an order. Some features may adjust the price of a product by applying either a surcharge or a discount.

When a feature is added to an order item the following steps are performed:

1. Look for a price for this feature on the PRICE_COMPONENT table in the following sequence:
   • An entry which has both feature_id and product_id set.
   • An entry which has feature_id set but not product_id.

   The first entry which is found will be used. A positive amount is a surcharge, but it is also possible to have a discount, which will be a negative amount.

ORDER_ITEM_FEATURE_CUSTOMISATION table

This table holds all the customised values for a feature which has been selected for an order item. Some features within a feature category may be able to be customised, such as engraving options for jewellery will need the text which is to be engraved.

The total number of customisation options is identified on the PROD_FEAT_CAT_CUSTOMISATION table. Their allowability (optional or required) is identified on the PROD_FEATURE_CUSTOMISATION table.

RELATED_ORDER_ITEM table

In some cases it may be useful to record that one order item is related to another order item.

If order S-1234 is related to order P-5678 is does not matter which is identified as order #1 and order #2.

Order Roles and Item Roles

Many parties can be involved in an order. A sales order has a CUSTOMER while a purchase order has a SUPPLIER. It may be useful to record other parties associated with an order, such as:

• The party who entered the order.
• The party that will receive the delivery of the order.
• The party that will pay for the order.
• The party for whom the product is installed and/or set up.
• The people who were involved in various roles regarding the order such as the person quality assuring the order, managing the order, and so on.

Figure 2 - Order Roles and Item Roles

ORDER_ROLE_TYPE table

This identifies the types of role with which parties can be related to an order.

Here are some examples:

• Placing Party
• Placing Buyer
• Order Entry Person
• Salesperson
• Order Authoriser

ORDER_ROLE table

This records the roles that various parties have with an order. A party may have more than one role with the same order, and a role may have more than one party.

ITEM_ROLE_TYPE table

This identifies the types of role with which parties can be related to an individual order item.

Here are some examples:

• Installer
• Installer Customer Contact

ORDER_ITEM_ROLE table

This records the roles that various parties have with an order item.

Order Contact Mechanisms

As well as the various parties associated with an order, it may be useful to record the contact mechanisms (addresses, telephone numbers) for those parties. Note that these are in addition to the various contact_mech_id_??? values which are held on the ORDER_HEADER table.

Figure 3 - Order Contact Mechanisms

ORDER_CONTACT_MECHANISM table

This identifies contact mechanisms which apply to an order.

ITEM_CONTACT_MECHANISM table

This identifies contact mechanisms which apply to items within an order.

Order Status and Item Status

An order will be in different states at different points in time, and instead of just recording the current state it may be more useful to maintain a history of each change in state. The same is also true for individual items within an order.

Different organisations may have different states through which an order may progress, so the number of status values is maintained on a database table, and can be added to as required. It may also be necessary to define rules which allow/prohibit a change in status from one value to another.

Figure 4 - Order and Order Item Status

ORDER_STATUS_TYPE table

This identifies all the possible states through which an order may be progressed.

Not all status values apply to all types of order (purchase, sales, transfer), so a series of switches identifies what is applicable where. This ensures that the dropdown list for order status shows only those values which are relevant.

Here are some examples:

ORDER_STATUS_RULES table

This provides a method of preventing a change in order status from anything to anything. Before a function is allowed to change the status it has to check to see if that change is valid. The current status is referred to as the FROM value while the proposed status is the TO value. If the rule table contains a record with that particular combination of FROM and TO values then it is valid and allowed to proceed. If there is no record with that combination of values then the change is not valid and not allowed to proceed.

Here are some examples:

You should note that for a sales order there are 2 routes between "pending" and "awaiting approval":

1. pending -> awaiting approval
2. pending -> pro forma sent -> confirmation received -> awaiting approval

Some customers may want to confirm an order before it is approved while others may not, so the choice of route is governed by the existence of a value for contact_mech_id_confirmation in the ORDER_HEADER record:

• If a value for contact_mech_id_confirmation does not exist then route #1 must be taken.
• If a value for contact_mech_id_confirmation exists then route #2 must be taken.

You should also note that 2 methods have been provided to change status values:

• A task made available as a navigation button, and therefore subject to the standard security mechanisms, which will change the status to a predetermined value as well as performing any processing which goes with that change (such as creating entries on the picklist). This will check the RULES table beforehand, and abort if the change is not valid.
• In the update screens the status is shown as a dropdown list, and the user can change from any value to any value without any validation or associated processing. Because of the potential consequences, such changes should not be made by anyone other than a supervisor or a manager.

ORDER_STATUS_HIST table

This keeps a history of all changes in status for an order. The entry with the highest sequence number is the latest.

ORDER_ITEM_STATUS_TYPE table

This identifies all the possible states through which an order item may go.

Here are some examples:

ORDER_ITEM_STATUS_HIST table

This keeps a history of all changes in status for an order item. The entry with the highest sequence number is the latest.

ORDER_STATUS_TYPE_ATTACHMENT table

This identifies when outgoing emails which are sent when an order changes status need to have attachments automatically added.

Order Terms and Item Terms

The parties involved in an order may agree on many arrangements or terms such as delivery terms, exchange or refund policies, and penalties for non-performance. Each order or order item may have one or more terms which are categorised by TERM_TYPE.

Figure 5 - Order Terms

ORDER_TERMS table

This identifies the terms which have been added to an order.

ORDER_ITEM_TERMS table

This identifies the terms which have been added to an order item.

Quantity Breaks and Value Breaks

These are used to define discounts which are applied during the compilation of sales orders. Quantity Breaks are for individual products while Value Breaks are for the order as a whole.

Figure 6 - Quantity Breaks and Value Breaks

QUANTITY_BREAK table

This is used to identify discounts against the price of a product based on the quantity purchased, with a different discount for a different range of quantities. The actual discount values are defined in the PRICE_COMPONENT table.

Here are some examples:

VALUE_BREAK table

This is used to identify discounts against the total goods value of a sales order, with a different discount for a different range of values. The actual discount values are defined in the PRICE_COMPONENT table.

Here are some examples:

Order Adjustments and Item Adjustments

As well as the price of individual products an order may contain a variety of discounts or surcharges which affect its total value. Some of these adjustments may apply to individual products, such as quantity breaks, while others apply to the whole order, such as sales tax.

Figure 7 - Order/Item Adjustments

ORDER_ADJUSTMENT_TYPE table

These are used to categorise various discounts and surcharges which may be applied to the total value of a sales order. The actual values or percentages are defined in the PRICE_COMPONENT table.

These adjustments are always processed in the sequence - (1) discounts, (2) surcharges, (3) taxes.

Here are some examples:

NOTE 1: all codes for automatic Delivery/Shipping must begin with 'DELIVERY' as they require special processing. They will use the delivery address instead of the order's primary address to determine the charge amount.

NOTE 2: all codes for automatic Sales Tax must begin with 'TAX' as they must be processed AFTER any non-tax surcharges. If either the order's billing address or delivery address lie within the enterprise's home area (the organisation's country) then the tax rate which applies to the home area will be used instead of that for the billing address.

DISCOUNT_CODE table

An organisation may decide to have a sales promotion involving discount vouchers. A range of voucher numbers may be issued, either individually to specific customers, or printed in trade magazines or other promotional material. If a customer supplies a valid voucher number with his next order he will be eligible for the discount that applies to that voucher.

Discount vouchers have the following properties:

• The discount that goes with each voucher may be defined either as a fixed monetary amount or a percentage of the order total.
• Each voucher can be given a date range during which it is valid. Attempts to use the voucher outside of this date range will be rejected.
• Each voucher can be given an optional usage limit so that it can only be used a certain number of times. Once the limit is reached the voucher is no longer valid.

ORDER_ADJUSTMENT table

This identifies those adjustments (surcharges or discounts) which apply to an order. When an order is first created the PRICE_COMPONENT table is scanned for eligible records, and if any are found they are added to this table automatically. Manual amendments can be made afterwards.

When any part of an order is amended the contents of this table is scanned to determine if any adjustments need to be made. Any effect that an individual adjustment record has is indicated in that entry's details, and the sum of all adjustments will be shown in the ORDER_HEADER details.

Here are some examples:

ITEM_ADJUSTMENT_TYPE table

These are used to categorise various discounts and surcharges which may be applied to individual product values when they are added to sales orders. The actual values are defined in the PRICE_COMPONENT table.

Here are some examples:

ORDER_ITEM_ADJUSTMENT table

This identifies those adjustments (surcharges or discounts) which apply to an order item. When an item is added to an order the PRICE_COMPONENT table is scanned for eligible records, and if any are found they are added to this table automatically. Manual amendments can be made afterwards.

When any part of an order item is amended the contents of this table is scanned to determine if any adjustments need to be made. The sum of all adjustments will be shown in the ORDER_ITEM details.

Here are some examples:

It should be noted that it is possible to have more than one item for the same product in the same order, perhaps with different features, so any quantity discounts have to operate on the total quantity for that product across all items within the order, and not just the quantity on the current item.

Order Payments

Some customers may pay by cheque, while others will want to pay by credit or debit card. The enterprise may have a web site through which customers may view their products and purchase them online, or they may place orders over the telephone or via email in which case the card payments will be entered on their behalf.

In order to process card payments online it is necessary for the organisation to have an account with a payment gateway. Requests can then be sent to the payment gateway via HTTPS, and their responses can be recorded in the system. The payment gateway will transfer funds from the customer's card to the organisation's account.

Note that the customer's card number is not stored in the database - it is transmitted to the payment gateway over an encrypted connection in order to authorise the payment, then it is discarded.

If the associated sales order uses a foreign currency then all payment transactions must be in the same currency.

Figure 8 - Online Payments

ORDER_PAYMENT table

This is used to record each payment transaction against an order. For credit card transactions this records the request sent to the payment gateway, and the response to that request.

ORDER_PAYMENT_GATEWAY table

This is used to identify various payment methods.

Here are some examples:

ORDER_PAYMENT_STATUS table

This is used to identify the range of values which may be used as selection criteria in the search screen. It is not use as a foreign key field as the values are under the control of the payment service provider.

Reserved Items

In some circumstances it may be a good idea to reserve stock for a sales order so that it is still available at the item issuance stage and not issued to someone else. Reserved quantities reduce the value in qty_on_hand to indicate that the stock is not available to others.

Entries on this table may be created at any of the following stages:

• When the order is taken.
• At the authorisation stage when the picklist entries are created. If a reservation already exists it will be reused and cannot be changed.

Entries are deleted when the items are picked from inventory at the issuance stage.

Figure 9 - Reserved Items

RESERVED_ITEM table

This identifies where inventory has been reserved for an order item. The quantity is used to reduce the value of qty_on_hand on the INVENTORY_ITEM record.

Non-Conformance Report

If an item received from a supplier is not of the required quality then corrective action has to be taken. This action is initiated with the creation of a Non Conformance Report (NCR) which requests a response from the supplier.

Figure 10 - Non-Conformance Reports

NON_CONFORMANCE_STATUS table

This identifies all the possible states through which a non-conformance report may be progressed.

Here are some examples:

• Issued
• Response Received
• Response Required
• Closed

NON_CONFORMANCE_REPORT table

This identifies when a problem is found with an item received from a supplier. It may be because the item was faulty, or the item did not match the specifications, or the wrong item was delivered.

Building Purchase Orders from Sales Orders

In some cases an item on a sales order may need to be custom made by an external supplier, in which case it will be necessary to raise a purchase order and receive the item from that supplier before it can be shipped to the customer. During the approval process for the sales order there is the option to raise a purchase order on a particular supplier instead of picking it from stock. This does not create a purchase order immediately, it simply adds a request to the PURCHASE_ORDER_BUILD table so that they can be processed later.

Once a sufficient number of requests have been assembled a second process will take all the requests for a nominated suppler, consolidate them into a single purchase order, then delete the requests. This process will also create an entry on the RELATED_ORDER_ITEM table so that it will be possible to see the relationship between sales orders and purchase orders.

Figure 11 - Building Purchase Orders

PURCHASE_ORDER_BUILD table

This identifies items on sales orders which need to be turned into purchase orders so that items can be obtained from suppliers before they can be despatched to customers.

Entries will only exist on this table until they have been added to a purchase order, at which point they will be deleted.

Links to other entities

It is possible for an order to be linked to other entities within the system, such as invoices, requests, requirements and shipments.

Figure 12 - Links to other entities

ORDER_QUOTE_LINK table

This links an Order to a Quotation.

ORDER_REQUEST_LINK table

This links an Order to a Request.

ORDER_REQUIREMENT_LINK table

This links an Order to a Requirement.

ORDER_SHIPMENT_LINK table

This links an Order to a Shipment.

Order 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 order. 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 order.

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. They can, however, be dealt with in any custom plugins.

Figure 14 - Order Extra Values

ORDER_EXTRA_NAMES table

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

ORDER_EXTRA_VALUES table

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

Order Item 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 order item. 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 order item.

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. They can, however, be dealt with in any custom plugins.

Figure 15 - Order Item Extra Values

ORDER_ITEM_EXTRA_NAMES table

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

ORDER_ITEM_EXTRA_VALUES table

This holds the values for any extra fields for each order item.

Saved Shopping Basket

Sometimes a customer visiting the front-end website may wish to save his/her shopping basket for later instead of purchasing the items straight away. This can be done by pressing the 'Save Basket' button on the website instead of the 'Purchase' button. If this facility is used the customer will be emailed with a hyperlink which will show the saved basket and all its items. This hyperlink can be shared with others. The 'Saved Shopping Basket' screen will have an 'Add to Cart' button which will copy the items to the 'real' shopping cart so that they may be purchased.

Figure 16 - Saved Shopping Basket

BASKET table

This identifies where a customer has saved his/her shopping basket in the front-end website.

BASKET_ITEM table

This identifies the items within a saved shopping basket.

BASKET_ITEM_FEATURE table

This identifies any features for an item within a saved shopping basket..

BASKET_ITEM_FEATURE_CUSTOMISATION table

This table holds all the customised values for a feature which has been selected for an item in the shopping basket. Some features within a feature category may be able to be customised, such as engraving options for jewellery will need the text which is to be engraved.

Order Control Data

This information exists within a shared table in the MENU database and not a separate table within the ORDER database. It holds values which can be varied to suit that particular installation. The current values are as follows:

Initial Values

There may be cases where a transaction requires user input and a particular value or values remains constant for all instances of that transaction. In order to avoid having to manually input the same value(s) each time the transaction is run it is possible to define each of those values in the database and have those values automatically included in the user input. For maximum flexibility the following tables in the MENU database are used:

Here are some typical cases:

Date created: 13th August 2007

Amendment history:

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