The REQUEST subsystem

An order occurs because a party has a need for something. Some enterprises may track these needs, and some may not. The enterprise may track its customer needs as well as its own needs. An example of a customer need is capturing a requirement to help build a system for a customer. An example of an internal need is a requirement to purchase certain office supplies.

Requirements

A requirement is an organisation's need for anything. Each requirement may be a customer requirement or an internal requirement, and each may be a product requirement or a work requirement.

Requirements may be related to other requirements, hence the recursive relationship. For example, there may be a requirement to purchase office supplies, and this may be further broken down into more specific requirements to buy pencils, pens, paper, etc.

REQUIREMENT_TYPE table

Field	Type	Description
requirement_type_id	string	Identity
requirement_type_desc	string	Description

REQUIREMENT_PARTY_TYPE table

Field	Type	Description
requirement_party_type_id	string	Identity
requirement_party_type_desc	string	Description

REQUIREMENT table

This identifies an organisation's need for something. For example, if an ITEM_ISSUANCE causes an item's on-hand quantity to fall below a certain level then a requirement record will automatically be generated to bring that fact to someone's attention.

Field	Type	Description
requirement_id	numeric	Identity number which is generated automatically by the system.
requirement_type_id	string	Links to an entry on the REQUIREMENT_TYPE table.
requirement_party_type_id	string	Links to an entry on the REQUIREMENT_PARTY_TYPE table.
requirement_desc	string	Requirement description
requirement_status_type_id	string	Links to an entry on the REQUIREMENT_STATUS_TYPE table.
required_by_date	date	Optional. The date by which this requirement should be fulfilled.
estimated_budget	numeric	Optional. The estimated budget for this requirement.
requirement_quantity	numeric	Optional. The quantity required.
requirement_reason	string	Optional text.
requirement_id_snr	numeric	Optional. Links to a senior entry on the REQUIREMENT table. This is used to create a requirement hierarchy.
facility_id	numeric	Optional. Links to an entry on the FACILITY table.
product_id	string	Optional. Links to an entry on the PRODUCT table.
revision_id	string	Optional. Links to an entry on the PRODUCT_REVISION table.

REQUIREMENT_REQUEST table

It is possible for a REQUIREMENT to be related to a REQUEST_ITEM, so this table provides the linking mechanism.

Field	Type	Description
request_id	numeric	Links to an entry on the REQUEST_ITEM table.
req_item_seq_id	numeric	Links to an entry on the REQUEST_ITEM table.
requirement_id	numeric	Links to an entry on the REQUIREMENT table.

REQUIREMENT_ROLE_TYPE table

This identifies the different types of role that parties may play in a requirement.

Field	Type	Description
role_type_id	string	Identity
role_type_desc	string	Description

REQUIREMENT_ROLE table

Field	Type	Description
requirement_id	numeric	Links to an entry on the REQUIREMENT table.
role_type_id	string	Links to an entry on the REQUIREMENT_ROLE_TYPE table.
party_id	numeric	Links to an entry on the PARTY table.
contact_mech_id_blockchain	numeric	Links to an entry on the CONTACT_MECHANISM table. This holds the blockchain address.

REQUIREMENT_STATUS_TYPE table

Field	Type	Description
requirement_status_type_id	string	Identity.
requirement_status_type_name	string	Short name.
requirement_status_type_desc	string	Long description.
sort_seq	numeric	Used for sorting the entries in the dropdown list.

REQUIREMENT_STATUS_HIST table

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

Field	Type	Description
requirement_id	numeric	Links to an entry on the REQUIREMENT table.
seq_no	numeric	Sequence number which is generated by the system.
requirement_status_type_id	string	Links to an entry on the REQUIREMENT_STATUS_TYPE table.
status_date	date	The date on which this change was made.

REQUIREMENT_COMPONENT table

This identifies where the required product is comprised of other components or sub-assemblies, in which case the requirement will actually be for those components.

Field	Type	Description
requirement_id	numeric	Links to an entry on the REQUIREMENT table.
product_id	string	This identifies a component of the required product. Links to an entry on the PRODUCT table.
seq_no	number	A unique number assigned by the system.
revision_id	string	Optional. Points to an entry on the PRODUCT_REVISION table for product_id_snr.
quantity	number	The quantity of this component product which is relevant to this requirement.
cmp_comment	string	Optional. A comment on this entry.

Requests

Instead of immediately ordering products on a requirement, sometimes a process of requesting and receiving quotes is used. A request is a means of asking vendors for bids, quotes, or responses to the requirement. The request could be sent to the enterprise, or it could be sent out from the enterprise to solicit responses from suppliers.

REQUEST_TYPE table

Field	Type	Description
request_type_id	string	Identity
request_type_desc	string	Description

REQUEST table

This holds all the requests for the enterprise. A request may have one or more items, and may have any number of parties playing different roles.

Field	Type	Description
request_id	numeric	Identity number which is generated automatically by the system.
request_type_id	string	Links to an entry on the REQUEST_TYPE table.
request_status_type_id	string	Links to an entry on the REQUEST_STATUS_TYPE table.
party_id_issuer	numeric	Links to an entry on the PARTY table. This identifies the party who is raising this Request. It will initially be taken from the Functional Unit which was identified in the LOGON screen.
request_date	date	Optional. The date of this request.
acknowledgement_date	date	Optional. The date by which the receipt of this request should be acknowledged.
clarification_date	date	Optional. The date by which all requests for clarification should be submitted.
response_required_date	date	Optional. The date by which a response to this request is required.
request_desc	string	Description
request_ref	string	Optional. A seperate identity which can be used by external parties.

REQUEST_ITEM table

This identifies the various items that form a request. There can be one or more items in a request

Field	Type	Description
request_id	numeric	Links to an entry on the REQUEST table.
req_item_seq_id	numeric	Sequence number which is generated automatically by the system.
req_item_desc	string	Item description
product_id	string	Optional. Links to an entry on the PRODUCT table
revision_id	string	Optional. Links to an entry on the PRODUCT_REVISION table.
required_by_date	date	The date by which this item is required.
request_quantity	numeric	Optional. The quantity required.
max_quantity	numeric	Optional. The maximum quantity required.

REQUEST_NOTES table

Field	Type	Description
request_id	numeric	Links to an entry on the REQUEST table.
seq_no	numeric	Sequence number which is generated automatically by the system.
note_text	string	Free format text.
party_id	number	Optional. Links to an entry on the PARTY table. This is used to indicate that this note is private to this party and should not be visible to other parties.

REQUEST_ROLE_TYPE table

Field	Type	Description
role_type_id	string	Identity
role_type_desc	string	Description

REQUEST_ROLE table

Field	Type	Description
request_id	numeric	Links to an entry on the REQUEST table.
role_type_id	string	Links to an entry on the REQUEST_ROLE_TYPE table.
party_id	numeric	Links to an entry on the PARTY table.
contact_mech_id_blockchain	numeric	Links to an entry on the CONTACT_MECHANISM table. This holds the blockchain address.

REQUEST_STATUS_TYPE table

Field	Type	Description
request_status_type_id	string	Identity.
request_status_type_name	string	Short name.
request_status_type_desc	string	Long description.
sort_seq	numeric	Used for sorting the entries in the dropdown list.

REQUEST_STATUS_HIST table

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

Field	Type	Description
request_id	numeric	Links to an entry on the REQUEST table.
seq_no	numeric	Sequence number which is generated by the system.
request_status_type_id	string	Links to an entry on the REQUEST_STATUS_TYPE table.
status_date	date	The date on which this change was made.

RESPONDING_PARTY table

This maintains information about the parties that may be responding to a request, along with their contact mechanisms.

Field	Type	Description
request_id	numeric	Links to an entry on the REQUEST table.
resp_party_seq_id	numeric	Sequence number which is generated automatically by the system.
party_id	numeric	Links to an entry on the PARTY table.
contact_mechanism_id	numeric	Links to an entry on the CONTACT_MECHANISM table. This holds the postal address.
contact_mech_id_blockchain	numeric	Links to an entry on the CONTACT_MECHANISM table. This holds the blockchain address.
has_responded	boolean	Indicates if this party has responed yet.
quote_id	numeric	Links to an entry on the QUOTE_HEADER table..
response_date	date	Indicates the date on which this party responded.

Quotations

A quote is a response to a request and is synonymous with a bid or proposal. The quote provides the pricing and terms associated with products that fill the need for the request.

QUOTE_TYPE table

Field	Type	Description
quote_type_id	string	Identity
quote_type_desc	string	Description.

QUOTE_HEADER table

Field	Type	Description
quote_id	numeric	Identity number which is generated automatically by the system.
quote_type_id	string	Links to an entry on the QUOTE_TYPE table.
quote_desc	string	Description.
quote_status_type_id	string	Links to an entry on the QUOTE_STATUS_TYPE table.
issue_date	date	The date on which this entry was issued.
valid_from_date	date	The date from which this entry is valid.
valid_to_date	date	The date after which this entry is no longer valid.
quote_value	numeric	The total value from all items in this quote, obtained by multiplying unit_price by quote_quantity.
item_count	numeric	The number of items in this quote.
quote_ref	string	Optional. A seperate identity which can be used by external parties.
	The following fields are used to deal with transactions which are carried out in a currency which is different from that of the functional unit. Please see FAQ24 for details.
currency_code	string	Links to an entry on the CURRENCY_CODE table. This identifies the transaction currency. It defaults to the operating/home currency of the receiving party as identified on the QUOTE_ROLE table, but may be changed by the user.
exchange_rate	numeric	Optional. The exchange rate which allows values to be converted from functional currency to transaction currency. This is only required if the two currencies are different.
quote_value_tx	numeric	This is the same as quote_value, but expressed in transaction currency.

QUOTE_ITEM table

Each quotation will have a HEADER record and any number of items. If a product has different prices for different quantity bands these can be shown by creating multiple items for the same product with the quantity bands shown by using ascending values for quote_quantity and max_quantity

Field	Type	Description
quote_id	numeric	Links to an entry on the QUOTE_HEADER table.
quote_item_seq_no	numeric	Sequence number which is generated automatically by the system.
quote_item_desc	string	Description.
quote_item_status_type_id	string	Links to an entry on the QUOTE_ITEM_STATUS_TYPE table.
product_id	string	Optional. Links to an entry on the PRODUCT table.
revision_id	string	Optional. Links to an entry on the PRODUCT_REVISION table.
request_id	numeric	Optional. Links to an entry on the REQUEST_ITEM table.
req_item_seq_id	numeric	Optional. Links to an entry on the REQUEST_ITEM table.
estimated_delivery_date	date	Estimated delivery date.
quote_quantity	numeric	Quantity.
max_quantity	numeric	Optional. The maximum quantity for which this price applies.
unit_price	numeric	Unit price in home/functional currency (the issuer's currency).
feature_price	numeric	The accumulated price of any optional entries on the QUOTE_ITEM_FEATURE table, expressed in home/functional currency.
adjusted_price	numeric	This is the sum of unit_price and feature_price, expressed in home/functional currency.
	The following fields are used to deal with transactions which are carried out in a currency which is different from that of the functional unit. Please see FAQ24 for details.
unit_price_tx	numeric	This is the same as unit_price, but expressed in transaction currency.
feature_tx	numeric	This is the same as feature_price, but expressed in transaction currency.
adjusted_price_tx	numeric	This is the same as adjusted_price, but expressed in transaction currency.

QUOTE_ITEM_FEATURE table

This identifies any product features which have been attached to a quote item. This is only valid when the item contains a value for product_id.

Field	Type	Description
quote_id	numeric	Links to an entry on the QUOTE_ITEM table.
quote_item_seq_no	numeric	Links to an entry on the QUOTE_ITEM table.
prod_feature_id	numeric	Links to an entry on the PRODUCT_FEATURE table.
feature_price	numeric	Feature price in home/functional currency.
	The following fields are used to deal with transactions which are carried out in a currency which is different from that of the functional unit. Please see FAQ24 for details.
feature_price_tx	numeric	This is the same as feature_price, but expressed in transaction currency.

QUOTE_ROLE_TYPE table

Field	Type	Description
role_type_id	string	Identity
role_type_desc	string	Description.

QUOTE_ROLE table

Field	Type	Description
quote_id	numeric	Links to an entry on the QUOTE_HEADER table.
role_type_id	string	Links to an entry on the QUOTE_ROLE_TYPE table.
party_id	numeric	Links to an entry on the PARTY table.
contact_mech_id_blockchain	numeric	Links to an entry on the CONTACT_MECHANISM table. This holds the blockchain address.

TERM_TYPE table

Field	Type	Description
term_type_id	string	Identity
term_type_desc	string	Description.
is_value_required	boolean	The term text may require a value to be specified separately when the term is linked with a quote or a quote item. This will be inserted into the text when the term text is formatted for output.

QUOTE_TERMS table

Field	Type	Description
quote_id	numeric	Links to an entry on the QUOTE_HEADER table.
seq_no	numeric	Sequence number which is generated automatically by the system.
term_type_id	numeric	Links to an entry on the TERM_TYPE table.
value	numeric	Optional or required depending on the value for is_value_required on the TERM_TYPE table.

QUOTE_ITEM_TERMS table

Field	Type	Description
quote_id	numeric	Links to an entry on the QUOTE_ITEM table.
quote_item_seq_no	numeric	Links to an entry on the QUOTE_ITEM table.
seq_no	numeric	Sequence number which is generated automatically by the system.
term_type_id	numeric	Links to an entry on the TERM_TYPE table.
value	numeric	Optional or required depending on the value for is_value_required on the TERM_TYPE table.

QUOTE_ITEM_ORDER_ITEM table

Field	Type	Description
quote_id	numeric	Links to an entry on the QUOTE_ITEM table.
quote_item_seq_no	numeric	Links to an entry on the QUOTE_ITEM table.
order_type	string	Links to an entry on the ORDER_ITEM table.
order_id	numeric	Links to an entry on the ORDER_ITEM table.
order_item_seq_no	numeric	Links to an entry on the ORDER_ITEM table.

Quotation Status

Quotes and individual quote items will have a different status depending on where they currently are in the processing cycle. The starting status will always be 'Pending'. Each quote and quote item will always contain its current status, but all the previous values will be recorded in a history file.

QUOTE_STATUS_TYPE table

Field	Type	Description
quote_status_type_id	string	Identity
quote_status_type_name	string	Short name.
quote_status_type_desc	string	Long description.
sort_seq	numeric	Identifies the sequence in which entries should be sorted for loading into the dropdown list.
is_vendor_selectable	boolean	Identifies whether this status can be selected by a vendor/supplier.

Id	Name	Seq	Vendor Selectable?
PEND	Pending	1	Y
SUB1	Submitted	2	Y
SUB2	Acknowledged	3	N
SUB3	In Review	4	N
RTRN	Returned	5	N
HOLD	On Hold	6	N
RJCT	Rejected	7	N
ACPT	Accepted	8	N

QUOTE_STATUS_HIST table

This shows a history of all the different status values that have been applied to a quote.

Field	Type	Description
quote_id	numeric	Links to an entry on the QUOTE_HEADER table.
seq_no	numeric	Sequence number which is generated automatically by the system.
quote_status_type_id	string	Links to an entry on the QUOTE_STATUS_TYPE table.
status_date	date	The date on which the status was applied to the quote.

QUOTE_ITEM_STATUS_TYPE table

This identifies the different status values that may be applied to a quote item.

Field	Type	Description
quote_item_status_type_id	string	Identity
quote_item_status_type_name	string	Short name.
quote_item_status_type_desc	string	Long description.

QUOTE_ITEM_STATUS_HIST table

This shows a history of all the different status values that have been applied to a quote item.

Field	Type	Description
quote_id	numeric	Links to an entry on the QUOTE_ITEM table.
quote_item_seq_no	numeric	Links to an entry on the QUOTE_ITEM table.
seq_no	numeric	Sequence number which is generated automatically by the system.
quote_item_status_type_id	string	Links to an entry on the QUOTE_ITEM_STATUS_TYPE table.
status_date	date	The date on which the status was applied to the quote.

Extra Names and Extra Values

While the database structure incorporates standard fields which are usually sufficient, it is possible that individual clients will want additional fields to hold extra pieces of information. Instead of changing the database structure for individual clients, or holding the extra data in separate tables or in a separate database, it is possible to identify the names of any additional fields on the HEADER/ITEM_EXTRA_NAMES tables, and then to assign specific values on the HEADER/ITEM_EXTRA_VALUES tables.

HEADER_EXTRA_NAMES table

This identifies the names of extra values that may be added to a Quote or a Request.

Field	Type	Description
extra_id	string	Identity
extra_name	string	Short Name
extra_desc	string	Optional. Long Description
extra_type	string	Validation type. Allowable values are: string (the default) boolean date number(P) - a number with P digits number(P,S) - a number with P digits, including S decimals

QUOTE_HEADER_EXTRA_VALUES table

Field	Type	Description
quote_id	numeric	Links to an entry on the QUOTE_HEADER table.
extra_id	string	Links to an entry on the HEADER_EXTRA_NAMES table.
extra_value	string	This holds the value for this Extra Name with this quote.

REQUEST_EXTRA_VALUES table

Field	Type	Description
request_id	numeric	Links to an entry on the REQUEST table.
extra_id	string	Links to an entry on the HEADER_EXTRA_NAMES table.
extra_value	string	This holds the value for this Extra Name with this quote.

ITEM_EXTRA_NAMES table

This identifies the names of extra values that may be added to a Quote item or a Request item.

Field	Type	Description
extra_id	string	Identity
extra_name	string	Short Name
extra_desc	string	Optional. Long Description
extra_type	string	Validation type. Allowable values are: string (the default) boolean date number(P) - a number with P digits number(P,S) - a number with P digits, including S decimals

QUOTE_ITEM_EXTRA_VALUES table

Field	Type	Description
quote_id	numeric	Links to an entry on the QUOTE_ITEM table.
quote_item_seq_no	numeric	Links to an entry on the QUOTE_ITEM table.
extra_id	string	Links to an entry on the ITEM_EXTRA_NAMES table.
extra_value	string	This holds the value for this Extra Name with this quote item.

REQUEST_ITEM_EXTRA_VALUES table

This holds the values for any extra fields which can be added to a Request item.

Field	Type	Description
request_id	numeric	Links to an entry on the REQUEST_ITEM table.
req_item_seq_id	numeric	Links to an entry on the REQUEST_ITEM table.
extra_id	string	Links to an entry on the ITEM_EXTRA_NAMES table.
extra_value	string	This holds the value for this Extra Name with this quote item.

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:

Table	Description
mnu_task_field	Identifies the field names within each task for which initial values can be defined. Entries must be created here for each task+field before any initial values can be defined for a task+field.
mnu_initial_value_role	This defines the initial values which are to be used when users within this role execute this task.
mnu_initial_value_user	This defines the initial values which are to be used when this user executes this task. Note that USER values take precedence over ROLE values.

Task	Field	Description
req_request(add1) (create request)	party_id_mgr	The party_id of the manager of this request.
party_id_origin	The party_id of the originator of this request.
party_id_prep	The party_id of the preparer of this request.
party_id_qa	The party_id of the quality assurer of this request.

12th Apr 2023	Updated Figure1 to include links to the ORDER_REQUIREMENT_LINK and ORDER_HEADER tables. Updated Figure1 to include links to the ORDER_REQUEST_LINK and ORDER_HEADER tables Updated Figure1 to include links to the ORDER_QUOTE_LINK and ORDER_HEADER tables
19th May 2018	Updated the QUOTE_ITEM table to include the feature_price and adjusted_price columns.
30th Dec 2017	Updated the QUOTE_ROLE table to include the contact_mech_id_blockchain column. Updated the REQUIREMENT_ROLE table to include the contact_mech_id_blockchain column. Updated the REQUEST_ROLE table to include the contact_mech_id_blockchain column. Updated the RESPONDING_PARTY table to include the contact_mech_id_blockchain column.
28th Sep 2015	Updated the QUOTE_ITEM table to include the max_quantity column.
9th Jul 2015	Updated the RESPONDING_PARTY table to include the quote_id column.
21st Mar 2015	Updated the REQUEST_ITEM table to include the revision_id column. Updated the QUOTE_ITEM table to include the revision_id column. Added the REQUIREMENT_COMPONENT table. Updated the REQUEST table to include the request_status_type_id column. Updated the REQUIREMENT table to include the requirement_status_type_id column. Updated the QUOTE_STATUS_TYPE table to include the is_vendor_selectable column.
21st Mar 2015	Updated the REQUEST table to include the acknowledgement_date and clarification_date columns. Updated the REQUEST_NOTES table to include the party_id column.
24th Jul 2014	Updated the REQUIREMENT table to include the revision_id column.
20th Jun 2014	Added Extra Names and Extra Values.
14th Jun 2014	Added Quotations and Quotation Status.
18th Mar 2008	Added the REQUEST_STATUS_TYPE and REQUEST_STATUS_HIST tables. Added the REQUIREMENT_STATUS_TYPE and REQUIREMENT_STATUS_HIST tables. Added columns facility_id and product_id to the REQUIREMENT table.
27th Feb 2008	Added columns has_responded and response_date to the RESPONDING_PARTY table. Added the REQUEST_NOTES table.

GM-X ERP for Blockchain - The REQUEST subsystem

By Tony Marston