GM-X ERP for Blockchain - The PARTY subsystem

By Tony Marston

31st July 2007
Amended 5th May 2023

Introduction

1. Party, Organisation and Person

2. Organisation Types and Subtypes

3. Party Classes and Subclasses

4. Party Roles

5. Party Relationships

6. Postal Addresses and Geographic Areas

7. Party Contact Mechanism

8. Party Extra Values

9. Party Identification

10. Party Status

11. Emails

12. Party Terms

13. Calendars

14. Party Control Data

15. Initial Values

Amendment History

Introduction

The most frequent business information need is to ask questions about people and organisations, and to be able to rely on accurate answers to these questions. For instance:

• What are the attributes or characteristics of the people and organisations that are involved in the course of conducting business?
• What relationships exist between various people, between various organisations, and between people and organisations?
• What are the addresses, phone numbers, and other contact mechanisms of people and organisations, and how can they be contacted?
• What types of communication or contacts have occurred between various parties, and what is necessary to effectively follow up on these communications?

Most data models maintain organisational information in various entities that are portrayed as completely separate entities. For instance, there may be a customer entity, supplier entity and department entity. Each application within an enterprise has its own needs, therefore the data modeler will usually base the model on the needs of a particular application. For example, a sales order processing system deals with customers, a purchase order processing system deals with suppliers, and a human resources system deals with departments within which the employees work.

The problem is that an organisation may play several different roles depending on the particular circumstances. For instance, in larger companies, internal organisations may sell to each other. The property management division may be a supplier to the product sales division. The property management division may also be customer of the product sales division. In this case there would normally be both a customer and supplier record, with redundant data, for each of these divisions. Not only would there be a customer and supplier record, but there could also be many additional records for the organisation depending on how many roles the organisation plays within the enterprise.

When an organisation's information changes - such as a change in address - the information might be updated in only one of the many systems where organisation information is stored. This, of course, results in inconsistent information within the enterprise. It may also result in major frustration on the part of managers, customers, suppliers, and anyone else who may want to generate a correct mailing list.

The solution to this redundancy problem is to have a single entity which can store information about any type of organisation, with details of their various roles and relationships maintained on separate entities.

Just as most data models show separate entities for for various types of organisation, they also show separate entities for various types of people such as employees, contractors, supplier contacts, and customer contacts. The problem with keeping this information in separate entities is that people may also have different jobs and roles that change over time. Most systems will record redundant information about a person because they store a different record each time the person's role changes. It is also possible for a person to have several different roles at the same time.

The solution to this redundancy problem is to have a single entity which can store information about any type of person, with details of their various roles and relationships maintained on separate entities.

It then becomes obvious that there are several similarities in how the data is held for organisations and people which result in duplicate data structures. When you couple this with the fact that in some circumstances it is necessary to link to an entity which could be either an organisation or a person, great savings could be made by using a single structure for both. It is for this reason that this data model has a PARTY entity which holds common data for both organisations and people, with specific data for each held on separate tables. All classifications, roles and relationships can therefore be maintained in a single data structure which links back to PARTY instead of having one structure for organisations and another for people.

Party, Organisation and Person

A PARTY can be either an ORGANISATION or a PERSON. The PARTY table holds the unique identity and name of every organisation or person, while specific information is held on either the ORGANISATION table or PERSON table depending on the party type. Other parts of the system which hold a reference to either an organisation or a person always link back to the PARTY table.

Figure 1 - Party, Organisation and Person

PARTY table

This holds the identity, name and type of every party within the system. It will have a corresponding entry on either the ORGANISATION or PERSON table depending on the party type.

The value for party_id will be generated internally by the system, but there may be a requirement to provide a alternative id as used by an external system. This facility is provided on the PARTY_IDENTIFICATION table.

Here are some examples:

ORGANISATION table

This entity holds the basic details of those parties which are organisations rather than people.

PERSON table

This entity holds the basic details of those parties which are people rather than organisations.

PARTY_NOTES table

This holds all contact notes for each party. A note may also be associated with other objects, such as orders, invoices or non-conformance reports.

Organisation Types and Subtypes

Organisations can be categorised by TYPES and SUBTYPES. An organisation can have only one type/subtype.

Figure 2 - Organisation Types and Subtypes

ORGANISATION_TYPE table

Organisations fall into broadly into one of two categories or types - formal (legal) and informal. Each of these types can be broken down further into subtypes which are held on the ORGANISATION_SUBTYPE table.

Here are some examples:

• Formal (legal)
• Informal

ORGANISATION_SUBTYPE table

This is an extension of the ORGANISATION_TYPE table which allows entries on the ORGANISATION table to be classified.

Here are some examples:

Party Classes and Subclasses

Parties, both organisations and people, can be classified in many different ways. There is the broad PARTY_CLASS and the narrower PARTY_SUBCLASS. Each party can be linked with any number of classes/subclasses by using the PARTY_SUBCLASS_LINK table, and each link has its own start and end date to maintain a history of changes over time..

Figure 3 - Party Classes and Subclasses

PARTY_CLASS table

These are a series of broad classifications which can be applied to parties. Each class is broken down into its own group of subclasses.

Here are some examples:

• Industry Classification
• Size Classification
• Income Classification
• Minority Classification
• Employment Equal Opportunities Commission
• Tax Classification

PARTY_SUBCLASS table

This breaks down each PARTY_CLASS into a series of narrower subclasses.

Here are some examples:

PARTY_SUBCLASS_LINK table

Any PARTY can be categorised in any number of ways by linking it to entries on the PARTY_SUBCLASS table. Each entry is only valid for a particular time period, so it is possible to have different values for different dates.

Note that it is not possible to have more than entry with the same party_class_id active with the same party_id on the same date.

Here are some examples:

Party Roles

Parties, both organisations and people, can have any number of roles, There is the broad ROLE_TYPE and the narrower ROLE_SUBTYPE. Each party can be linked with any number of role-types/role-subtypes by using the PARTY_ROLE_LINK table, and each link has its own start and end date to maintain a history of changes over time.

Figure 4 - Party Roles

ROLE_TYPE table

This identifies the roles that a party may play in the context of the enterprise's environment, as defined in the PARTY_ROLE_LINK table. Each type is further divided into a series of SUBTYPES. Some types may be relevant to organisations only, or people only, while others may be relevant to both.

Here are some examples:

• Customer Role - both
• Supplier Role - both
• Organisation Role - organisation
• Prospect - both
• Shareholder - both
• Person Role - person
• Family Role - person

ROLE_SUBTYPE table

This breaks down each ROLE_TYPE into a series of subtypes.

Here are some examples:

PARTY_ROLE_LINK table

The roles which a PARTY may have are identified by linking it to entries on the ROLE_SUBTYPE table. Each entry is only valid for a particular time period, so it is possible to have different values for different dates.

Here are some examples:

Party Relationships

Any relationship between two parties can be stored on the PARTY_RELATIONSHIP table. Each party in the relationship has a different role, and there can be several different relationships between the same two parties. Each relationship has its own start and end date so that a history of changes over time can be maintained.

Figure 5 - Party Relationships

RELATIONSHIP_TYPE table

This holds all the possible types for use on the PARTY_RELATIONSHIP table.

Here are some examples:

• Customer relationship
• Supplier relationship
• Agent relationship
• Supplier Contact relationship
• Customer Contact relationship
• Organisation Rollup
• Family relationship

RELATIONSHIP_PRIORITY table

This holds all the possible priorities for use on the PARTY_RELATIONSHIP table.

Here are some examples:

RELATIONSHIP_STATUS table

This holds all the possible status values for use on the PARTY_RELATIONSHIP table.

Here are some examples:

• Active
• Inactive
• Suspended

PARTY_RELATIONSHIP table

This is used to record any relationship which may exist between two parties, and each party's role in the relationship. There may be more than one relationship between two parties, and each entry is only valid for a particular time period, so it is possible to have different values for different dates.

It does not matter in which order the two parties are specified, which means that when searching for relationships with a particular party the system will search both PARTY_1 and PARTY_2 and show the details of the other party.

Examples of organisation-to-organisation relationships:

Examples of person-to-organisation relationships:

Examples of person-to-person relationships:

Postal Addresses and Geographic Areas

Each POSTAL_ADDRESS is identified by a series of GEOGRAPHIC_AREAS each of which is a different GEOGRAPHIC_AREA_TYPE such as POSTCODE, CITY/TOWN, COUNTY or COUNTRY. Some area types may not be for postal addresses but for other purposes such as sales areas, delivery zones or tax areas.

Each country has its own POSTAL_ADDRESS_STRUCTURE which identifies which GEOGRAPHIC_AREA_TYPES are valid for that country. So where the UK has "county" the USA will have "state", for example.

The GEOGRAPHIC_AREA_HIERARCHY table is used to identify which smaller areas are enclosed within which larger areas. As well as being able to identify which cities are in which counties, this also identifies which addresses are in which sales areas or delivery zones.

Figure 6 - Postal Addresses and Geographic Areas

GEOGRAPHIC_AREA_TYPE table

This is used to categorise each GEOGRAPHIC_AREA by type. Each country has its own POSTAL_ADDRESS_STRUCTURE which identifies which types are valid for that country.

Here are some examples:

By having separate types for each country it becomes easier to distinguish entries with the same name which exist in different countries, such as Birmingham (UK) and Birmingham (Alabama), or Kingston (Surrey) and Kingston (Jamaica).

GEOGRAPHIC_AREA table

Each geographical area is recorded once with a unique identification number, then referenced by this number.

Here are some examples:

You may notice that there are two entries for a UK City/Town called Hayes - with id's if 254 and 257. That is because there are two different towns with the same name - Hayes (Middlesex) and Hayes (Kent). There are also two towns called Birmingham, but they have different types which signify that they belong in different countries.

GEOGRAPHIC_AREA_ALIAS table

Some geographical areas may have alternative spellings or even alternative names. This table allows a geographical area to be linked with any number of aliases.

Note that a particular combination of area_type_id and area_name cannot exist on both the GEOGRAPHIC_AREA and GEOGRAPHIC_AREA_ALIAS tables.

Here are some examples:

• 'Cardiganshire', a county in Wales, is also known as 'Ceredigion'.
• 'Angus', a county in Scotland, is also known as 'Forfarshire'.
• 'Dunbartonshire', a county in Scotland, may also be spelt as 'Dumbartonshire'.
• 'West Yorkshire', a county in England, may also be spelt as 'W. Yorkshire' or 'W Yorkshire'.

Where an alias may be spelt either with or without any periods, such as for 'W. Yorkshire' and 'W Yorkshire', it is only necessary to create a single alias with periods as the search will automatically include an additional lookup with any periods removed.

GEOGRAPHIC_AREA_HIERARCHY table

This table is used to identify which areas enclose other areas, such as which towns belong in which counties, or which postcodes belong in which towns.

Here are some examples:

By traversing up this hierarchy from junior to senior it is possible to reconstruct a postal address. For example, with 258 as the starting point we get KK1 1KK, Hayes, Kent, England and with 261 we get MM1 1MM, Hayes, Middlesex, England. Note also that while both Kent and Middlesex belong in the country of England, they also belong in the same Southern Sales Area (216).

By traversing down his hierarchy from senior to junior it is possible to identify which smaller areas exist within any given larger area. For example, with 63 as the starting point we can list all the English counties, then the towns that exist within each county, and so on.

Using this structure it is possible to create any hierarchy of geographical areas that you can imagine, so any area can belong in any number of other areas (although only one path should produce a postal address). It is also possible to reflect the fact that while the United Kingdom is a country, it is comprised of England, Scotland and Wales which are also countries.

POSTAL_ADDRESS_STRUCTURE table

This is only valid for those GEOGRAPHIC_AREAS which have a GEOGRAPHIC_AREA_TYPE of "country" as it identifies the address structure that is to be used for that country, and how its addresses are to be printed.

This is where the creation of different GEOGRAPHIC_AREA_TYPES for each different country become useful. Once the country has been identified it is possible to restrict each component of the address to a particular type, so that if a UK County is requested, for example, it is not possible to select a US state.

Here are some examples:

POSTAL_ADDRESS table

Each postal address is actually comprised of data which is held on two tables - the textual part which is held on this table, and the links to GEOGRAPHIC_AREAS which are held on the POSTAL_ADDRESS_AREA table.

Here are some examples:

POSTAL_ADDRESS_AREA table

This provides the identities of the GEOGRAPHIC_AREAS which, when combined with the contents of the POSTAL_ADDRESS table, will produce a full postal address.

Here are some examples:

When the full address is required for printing in can quickly be assembled into the following:

Flat 37
Tall Building
Station Road
Hayes
Middlesex
MM1 1MM 

This structure also allows all those addresses which belong in any GEOGRAPHIC_AREA to be quickly identified without having to traverse the GEOGRAPHIC_AREA_HIERARCHY table.

Party Contact Mechanism

A CONTACT_MECHANISM is a method of communicating with a PARTY and may be any one of various TYPES, which includes a POSTAL_ADDRESS.

A CONTACT_MECHANISM can be shared by more than one PARTY, and a PARTY may have more than one CONTACT_MECHANISM, so all the possible combinations are held on a separate PARTY_CONTACT_MECHANISM table. Each of these combinations may also have a ROLE and any number of PURPOSES.

Some contact mechanisms may be related to each other, such as a postal addresses having related telephone numbers, or a telephone number having an alternative number should it be busy. These relationships can be recorded on the CONTACT_MECHANISM_LINK table.

Figure 7 - Party Contact Mechanism

CONTACT_MECHANISM_TYPE table

This is used to categorise each CONTACT_MECHANISM by type.

Here are some examples:

• Postal address
• Telephone number
• Fax number
• Mobile number
• E-mail address
• Internet address (URL)
• Blockchain address

CONTACT_MECHANISM table

This holds all the possible mechanisms which may be used to contact parties. The actual party (or parties) to which each mechanism is linked is held on the PARTY_CONTACT_MECHANISM table.

Here are some examples:

Note that in the case of telephone numbers the contact string is constructed from the country code, area code, number and extension.

If a postal address is selected the contact string will show the complete address in the format specified on the ADDRESS_STRUCTURE table for that country.

CONTACT_MECHANISM_LINK table

This can be used to indicate when one mechanism is related to, or can be used as a substitute for, another mechanism.

PARTY_CONTACT_MECHANISM table

This is used to indicate which CONTACT_MECHANISMS are used by which PARTIES. Note that the same mechanism can be used by more than one party. Each entry is only valid for a particular time period, so it is possible to have different values for different dates.

The optional ROLE attribute can be used to signify that the particular mechanism should only be used for a particular role. For example, an organisation may provide address information only in their role as a customer, and this may not be applicable to any other role.

Each entry may also be used for one or more CONTACT_MECH_PURPOSE_TYPE, and this information is held on a separate PARTY_CONTACT_MECH_PURPOSE table.

Here are some examples:

CONTACT_MECH_PURPOSE_TYPE table

This is used to identify the purposes for which a PARTY_CONTACT_MECHANISM may be used.

Here are some examples:

• Primary address
• Secondary address
• Billing address
• Billing enquiries
• Delivery address
• Headquarters
• Main office number
• Primary fax number
• Secondary fax number
• Emergency number
• General enquiries

PARTY_CONTACT_MECH_PURPOSE table

A PARTY_CONTACT_MECHANISM may be used for more than one PURPOSE, which is why this information is held on a separate table. Each entry is only valid for a particular time period, so it is possible to have different values for different dates.

Party 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 party. 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 party.

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 - Party Extra Values

PARTY_EXTRA_NAMES table

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

PARTY_EXTRA_VALUES table

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

Party Identification

It is possible that this database could be supplied with party details from external systems which have their own form of identity, and this external identity would otherwise be lost when it is replaced with the internally generated identity (party_id). These tables provide the means of identifying any number of external identity types and recording their values against each party.

An example of where an external identity may be used can be found in the purchasing part of a web front-end where a customer elects to pay via an external payment processor (such as PayPal) in order to avoid having to reveal a credit card number. The customer logs on to this payment processor and authorises the payment, after which the application is given an authorisation code and the customer identity (as known to the external system). The application will use this data to create a new PARTY record, with a new party_id, but it would also be a good idea to record the external identity.

Figure 9 - Party Identification

PARTY_IDENTITY_TYPE table

This holds the various types of identity that can be applied to parties, such as from external sources.

Here are some examples:

PARTY_IDENTIFICATION table

For each party this holds all the alternative identification values.

Here are some examples:

Party Status

Customers or suppliers for an organisation may need to have a variety of different status values in order to be granted different privileges. All parties start with the 'Pending' status, and additional values can be defined at will.

Figure 10 - Party Status

PARTY_STATUS table

This identifies all the possible status values for a party.

Here are some possible examples:

PARTY_STATUS_HIST table

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

Emails

This part of the application deals with individual messages and not bulk messages as may be used in marketing campaigns. It is designed to be used for tracking all incoming and outgoing messages concerned with particular parties, orders or invoices.

In addition there is a TEMPLATE table which can provide the standard text for outbound messages when certain events happen in the system, and a SCHEDULE table to hold messages which are to be sent out at some time in the future.

Received/Inbound messages are obtained by running a batch task which connects to one or more email accounts on a mail server (either IMAP or POP) and reads all available messages. Note that this does not access every account on the email server, just those which have been specified (such as those that deal with customer service), so that unrelated emails in other accounts will be left untouched. It will then transfer all new messages to the EMAIL_MSG table and delete them from the mail server. Any attachments will be added to the EMAIL_ATTACHMENT table. Although this batch task can be run manually on demand from an online menu, it would normally be run in the background on a predetermined schedule, such as every 15 minutes or so.

When incoming messages are received and transferred to the EMAIL_MSG table the following steps are taken in order to link them with the appropriate party, order or invoice:

• If the incoming message is a reply to an outgoing message which also exists on this database table then the links on the previous outgoing message are copied to the incoming message.
• A lookup is made on the sender's email address on the PARTY_CONTACT_MECHANISM table, and if one is found then the message can be linked to that PARTY.

Sent/Outbound messages can be generated from two possible sources:

• Automatically - it is possible to configure the application to send out emails automatically whenever the order status or order item status changes. These emails will not be copied to the database unless the Copy emails to database switch on the Order Controls screen is turned on.
• Manually - any message which is generated through the application, either by using the COMPOSE, REPLY or FORWARD buttons, will be copied to the database. These messages will use the user's signature and email address as defined on the EMAIL_SIGNATURE table. Any messages NOT generated through this application, such as via an external email client, will NOT be copied to the database.

Certain outgoing emails may be required to have attachments automatically added. The attachments can be defined on the EMAIL_AUTO_ATTACHMENT, and the outgoing emails can be identified on the EMAIL_SIGNATURE_ATTACHMENT and ORDER_STATUS_TYPE_ATTACHMENT tables.

Queues and Cases are different ways of grouping messages together, and they are handled in the following ways:

Figure 11 - Emails

EMAIL_QUEUE table

This identifies the available queues for email processing. There may be queues for different business areas, such as 'sales' and 'purchasing', or even separate queues for individual users.

Incoming emails will initially be assigned to the queue specified on the EMAIL_MAILBOX entry. Replies to emails will be assigned to the same queue as the original email.

Messages can be moved from mailbox queues to personal queues while they are being processed, then back again once a reply has been generated.

When an email is received the in-reply-to field in the header will indicate if the message is a reply to a previous outgoing message or a brand new message. If this is a reply then this message will be attached to the same case as the email identified in the in-reply-to field, otherwise the following will happen:

• A new EMAIL_CASE will be created, and this email will be linked to that case.
• If the email_acknowledgement field is not empty, and the sender's address is not on the EMAIL_BLACKLIST table, then an acknowledgement will be sent out automatically.

EMAIL_MAILBOX table

This identifies the email account(s) that will be scanned for incoming emails.

Here are some examples:

EMAIL_CASE table

This identifies a group of emails which share the same "topic" or "thread" or "conversation". A new case is automatically created upon the receipt of a new message, while any replies, both sent and received, will share the same case_id as the original.

EMAIL_MSG table

This has a separate entry for each message, either sent or received.

EMAIL_ATTACHMENT table

An email may consist of just a message body, or it may have one or more attached files. In the case where an HTML email was received the message body will be shown in plain text while the HTML representation will be available as an attachment.

EMAIL_SIGNATURE table

It is usual for each email message to contain a signature which helps identify who sent the message. This signature may contain just a name, or it may include a job title, address and/or telephone number. This table can hold a personalised signature for each user which is added to the message text whenever that user creates an email message. This also holds the user's email address (addr_from) and an optional reply_to address if this is different.

Note that it is possible to specify a different signature for each queue. The entry without a specific queue_id is the default, and will be used if a signature for the specified queue does not exist.

EMAIL_AUTO_ATTACHMENT table

There may be a requirement for certain outgoing emails to have one or more attachments automatically added. For example, German law requires that all outgoing emails contain a copy of the T&C's (Terms and Conditions) and the Returns Policy. These can be defined as automatic attachments, and then linked with the required email signatures.

EMAIL_SIGNATURE_ATTACHMENT table

This identifies when outgoing emails which are created using this signature need to have attachments automatically added.

EMAIL_BLACKLIST table

This identifies email addresses for which auto-acknowledgements for incoming emails should not be sent out. This could be because the sender is not a customer with a genuine query.

EMAIL_TEMPLATE table

This identifies the text to be used for email messages which are sent out in response to certain system events such as "account created" or "sales order received".

EMAIL_SCHEDULE table

This is used when an outbound email needs to be followed up with another email, either the same one or a different one, at a future date. This is controlled by the values for delay_period, repeat_count and template_id_next on the parent EMAIL_TEMPLATE record.

Party Terms

Parties with whom the organisation does business may have terms to cover different aspects of that business. Each party may have one or more terms which are categorised by TERM_TYPE.

Figure 12 - Party Terms

TERM_TYPE table

This identifies the category or type of term which is available for parties.

Here are some examples:

PARTY_TERMS table

This identifies the terms which have been added to a party.

Calendars

Calendars are used to identify when a date is a working day or not. Different organisations may have their own rules on what is or is not a working day which may or may not take into account weekends and national holidays. A different calendar can be created for each different set of rules.

Figure 13 - Calendars

CALENDAR_HEADER table

This identifies the different calendars which may be used within the system. Each calendar has a different mechanism for identifying what is or is not a working day. This information is used, for example, to calculate the expected delivery date for a product based upon its lead time.

CALENDAR_DATE table

This identifies the properties of each date within each calendar year for a particular calendar. A new set of default values is generated manually for each year, after which the details for individual dates can be amended as required.

Party 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: 31st July 2007

Amendment history:

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