By Hernan Pardo
Theoretically, the IBMSYS can simulate any ERP on the market, at least in terms of producing the same transactional evidence, its DB could contain all the companies in the world by the unlimited tree of the Companies entity. If the latter is impossible, the simulation of the most used ERPs can generate many university research groups that enable students of a master’s degree in ERPs to face the future work as consultants of the ERP simulated or for the implementation or migration to IBMSYS, especially if it is available as SaaS in a cloud robust like Alibaba Cloud. I’m going to prove it.
IBMSYS has the following subsystems:
- Persons, individuals, legal entities and business relationships.
- Login, unique control of access to the system of the users and their roles.
- Things, Products and services related to systematized companies.
- Accounts, Account Plans, Assets, Liabilities, Equity, Income, Expenses and all kinds of Contingencies necessary in business management.
- Plugins, variable format information dependent on tuples of information that do not require more relationships.
- Processes, dependent on Norms, for the entry and consultation of information and definition of economic transactions and CRUD (Create, read, update and delete tuples from the database tables).
- Transactions, preparation, registration and production of evidence.
Each subsystem will be a subproject, if it has more than one option, a submenu must be added for the user to choose, but it must be possible to go directly to an option.
There will be a library of abstract classes of the database tables, with the variables and methods for handling those variables and CRUD operations, (create (Create), read (Read), update (Udate) and delete (Delete)) tuples. All the classes that refer to a table and its handling must be inherited from those of this library.
The Plugins subsystem contains the methods and variables for managing the database and the other subsystems must invoke them. In other words, only the Plugins subsystem will have the possibility to consult and update the database.
This programming design complements the first article: Design of the Intelligent Business Management System (IBMSYS)
To build the formats of the forms and reports, and to report the status of the management of each role in Dashboards, its notifications and alerts, the npm jsReport package will be used.
Managing People and Relationships
People, natural and legal subsystem and their business relationships.
This subsystem is used to create, modify and delete tuples from the People, Locations and Companies tables.
Let’s see the relationships between these 3 tables:
There cannot be a tuple in the People table without at least one other tuple in the Companies table, when creating a new Person it is assumed that it has a commercial interest. In Colombia, a Colombian natural person of legal age has as identification the citizenship card, if this person establishes a commercial or employment relationship, they must obtain an additional document called the Unique Tax Registry (RUT) or personal tax identification number, in this case the number is equal to that of the ID with one digit, additional check.
The subsystem must give several options:
1) Create, modify or delete Locations. This table is of tree type, for each new tuple it must be determined if it belongs to a higher group, it must be allowed to navigate through the groups starting with the upper level, the one that does not belong to a group, selected the tuple of this level the levels are displayed dependents, if you have them, and so on until you find the location you want to give the new tuple.
2) Create, modify or delete a Person and its corresponding Company, being possible that the new tuple in Companies is dependent on a group, or conglomerate, therefore, it must be possible to navigate in its tree structure until it is found.
3) Receive in a file a group of Locations, verify that all the data and the corresponding dependencies are present and proceed to update sequentially, when the user confirms.
4) Receive in a file a group of People and their corresponding Companies, verify that all the data and the corresponding dependencies are present and proceed to update it sequentially, when the user confirms.
Each update session of any of the 4 options will form a logical transaction and will be recorded in the transactions table, according to the process detailed in the tuple of the Processes table related to the menu option from where these options are activated. This recording and the corresponding log are made by the plugin subsystem, the People subsystem should only send the logical transaction start signal, each update step and the end, or undo, signal to the database handling functions of the Plugins subsystem.
Login subsystem, unique control of access to the system of users and their roles.
Let’s see the relationships between the necessary tables:
This subsystem has 3 options:
1) It allows creating, modifying and deleting tuples from the Roles, Users and Menus tables, it also allows assigning roles to users and options, from the atomic tuples of the Processes table, to roles.
2) Control access to the system by displaying a form to request a user and password, if the data supplied by the user does not correspond to any of the registered, the entry will be rejected, clearly stating the reason.
3) Create the plugin, pointed to by the plugin id column, to report the status of the role’s management in Dashboards, its notifications and alerts, using the technique to create reports that is detailed in the Plugins subsystem.
Things, Products and services subsystem related to systematized companies.
This subsystem handles CRUD operations on the Things, UNThingsCatalog, Tariff and Prices tables. The Things, Tariff and UNThingsCatalog tables have a tree structure. Tuples that group others in the Stuff table and are related to Tariff and UNThingsCatalog atomic tuples, inherit this relationship from dependent tuples that cannot be assigned a different relationship. Only in the case of not inheriting a higher level relation a tuple from the table Things should be related to atomic tuples of Tariff and UNThingsCatalog.
Let’s look at the relationships between these necessary tables.
This subsystem has the following options:
1) It allows creating, modifying and deleting tuples from the Tariff and UNThingsCatalog tables, the 2 tables have a tree structure. tuples that do not belong to a group, those of the highest level, must have a group id equal to zero, for the others the group is searched gradually, starting with tuples that do not have a group so that the user selects and subsequently the tuples belonging to that group until the lowest level group to which they belong is located. Tuples grouping others cannot be deleted unless the user orders to cascade delete and there is no dependency on other tables with tuples.
2) Option to sequentially create tuples of the Tariff and UNThingsCatalog tables, from files that comply with a predefined structure, verifying that the dependencies to higher groups are satisfied.
3) Option to create, modify and delete tuples from the Things table that has a tree structure, so it is necessary to gradually assign superior group to the dependent tuples and dependencies to the Tariff tables, which can be optional, and UNThingsCatalog that it is mandatory.
4) Option to sequentially create tuples in the Things table from a file, verifying that the dependencies are met.
5) Option to create, modify and delete tuples from the Prices table, allowing to look for dependency with the Things table gradually.
6) Option to sequentially create tuples in the Prices table from a file, verifying that the dependencies are met.
Accounts Subsystem, Account Plans, Assets, Liabilities, Equity, Income, Expenses and all kinds of Contingencies necessary in business management.
This subsystem handles CRUD operations in the Accounts, AccountPlans, EquivalentAccounts, Categories and CategoriesAccounts tables.
The Accounts table must inherit the structure of AccountPlans, all of its tuples will be atomic and must belong to a single type of chart of accounts (equal value in the idtipoplan variable), they do not have their own tree structure.
In order to manage more than one type of chart of accounts, the EquivalentAccounts table is used, which links sets of account plan tuples with the corresponding account tuples. An AccountPlans tuple can relate to one or more EquivalentAccounts tuples and a EquivalentAccounts tuple can relate to one or more Account tuples. The level of the Account tuples must be foreseen so that it can be correctly related to the lower level of other account plans that will be used.
Let’s look at the relationships between these tables.
The categories allow cross-sectional analysis of accounting information. They can have one-to-many and many-to-one relationships with Accounts. For example: a type of category can group the base information to produce financial statements, in some cases it may group several accounts, in others it may correspond one by one and in others an atomic account may be decomposed into several atomic categories. In the latter case, the Imputations must be broken down to the lowest level that is categories, if there are no other mandatory details to break down more by people or things. The only cases where Imputation tuples have a nonzero value in idcategoriacuenta are when the account category relationship is multiple categories for an account.
This subsystem has the following options:
1) It allows creating, modifying and deleting tuples from the AccountPlans and Categories tables, the 2 tables have a tree structure. tuples that do not belong to a group, those of the highest level, must have a group id equal to zero, for the others the group is searched gradually, starting with tuples that do not have a group so that the user selects and subsequently the tuples belonging to that group until the lowest level group to which they belong is located. Tuples grouping others cannot be deleted unless the user orders to cascade delete and there are no dependencies on tuples from other tables.
2) Option to sequentially create tuples of the AccountPlans and Categories tables, from files that comply with a predefined structure, verifying that the dependencies to higher groups are satisfied.
3) Option to create, modify and delete tuples from the Accounts table that does not have a tree structure. Dependencies to the Accounts Plans table is mandatory and is assigned gradually from the top group to an atomic tuple. The relationship with the categories table is optional and can be several to many through the Accounts category table, it must be possible to assign several tuples from the accounts table to an atomic category, that is, it is not a group, and also several atomic tuples of the categories table to a tuple of the Accounts table.
4) Option to sequentially create tuples in the Accounts table from a file, verifying that the dependencies are met.
Plugins subsystem, variable format information dependent on information tuples that do not require more relationships. In addition, it includes handling of logical transactions to the database.
This subsystem can generate plugins to tuples of IBMSYS database tables, as dynamic variable data. In other words, a tuple can store additional data according to a structure defined in the same plugins, the tuple will have its plugin in a relative direction in “large objects”. The first 2 data of the plugin are the address of its structure and its size, at the end of the data it can be followed by an address, if it is not zeros. This subsystem allows new data structures to be added, modified, or deactivated, without stopping the database services. This allows the tuples in a table to have differently structured data so that different types of transactions, for example: scheduling a production or construction, a class of employment contract, or an accounting adjustment of 2 imputations, can share the common data in the Transactions, Imputations and Inforce tables, supplemented with the specific data by type of transaction.
This variable data is handled with Postgresql’s “Large Object” facility, it exists under another name in Microsoft SQL Server. Complementary data must be included in the logical transaction of the database, that is, they are part of an automated transaction or a role option of a user.
The Plugins subsystem includes all other changes to the database, starting with the start of the logical transaction, each creation, modification, or removal of table tuples, variable data, the cancellation of the transaction or Rollback of partial progress and the end of the transaction or Commit.
The other subsystems must use functions defined in the Plugins subsystem to: block other modifications to tuples that are likely to be modified, the start of the logical transaction, each of the CRUD operations, the possible cancellation of the transaction or the commit of the same. Plugins allocates memory (Malloc) to store each step and at the end it records the set as a complement to the Transuser table and makes the modification of the database effective. The Transuser table is a detailed log of database modifications and is used for supplemental recovery in cases of system crash occurring after saving the tuple and its plugins and before committing the database. This allows, in these cases, an additional recovery, from the last completed transactions, to the recovery that Posgresql makes. It also allows a batch recovery from the last reliable copy, applying the transactions that have occurred from this copy, for this reason it is recommended that the copies and the Transuser tables of the IBMSYS and Large Object of Postgresql be physically located in a different volume or FileSystem than the one rest, foreseeing that a server destroyed by a disaster can be recovered.
The Plugins subsystem includes operations to all the relational tables in the database and to the complementary data it handles in “Large Objects”.
Processes subsystem, dependent on rules for entering and consulting information, and definition of economic transactions and CRUD.
This subsystem allows creating the different production and administrative processes at the desired level because the Processes table has a tree structure. Atomic processes become the options assigned to users through the role menus. At some level the processes are related to the norm that legitimizes them.
Options can be to obtain specific information or to prepare and then order a transaction. Initially, minimum atomic processes will be created for CRUD operations and for 2-imputation transactions that do not affect People or Things, these minimum processes will form an initial library that is fed with the new processes created. The creation of other new atomic processes must inherit from an already defined process 4 elements: the form, the report, the first complement on the handling of the form and the second complement on the handling of the report and the update.
Let’s look at the relationships between the necessary tables.
Atomic processes have pointers to the form format in which the information is presented to be reviewed by the user. The form can be only display if the option is a query.
When it comes to preparing a transaction the form will display some already known data, allow the user to record others, and possibly display others based on the data supplied by the user. The data can be primary if they appear at the bottom of the form, without any matrix frame, or can be contained in a frame, secondary, or a sub-frame, tertiary, etc., which means the possibility of having rows each of which may contain already registered data, register new data and deduct others when the user supplies certain data. The form will contain fields for the different types mentioned, their character and the order in which they appear is determined by the first complement of the tuple, that is, the one indicated by idcomplemento, which contains an occurrence for each type of data indicating whether it is to be simply displayed, if it is editable to include, change or delete a certain type of value or if it must be deduced based on other data already registered, in which case its formulation or origin is also found in Plugins. There may be invisible data that is required to obtain others.
Each unit field will have the following properties predefined:
- Length, if it is not inherent to the data type.
- Origin that can be: 1) from the database determined by the table, a key or index determined by an already registered data and the name of the column. Optionally, it will be indicated if it is necessary to block the tuple that contains the data so that other transactions in process cannot modify it or deactivate it while the current transaction is in progress 2) calculated as a result of mathematical operations applied to other data already registered, other new data from the database and constants, all this presented as a formula with variables, which can be another formula, constants and operands.
- Presentation mask.
- Font, color, size, if different from the general assumptions of the form or the frame.
- If it is visible. Etc.
If the data type is a frame, the following are processed: columns, maximum number of rows, how to continue, etc.
The order of occurrences of this complement must be consistent so that the required data have the necessary information to be able to read or calculate them.
Atomic processes have pointers to the report format in which evidence for each transaction is shown or recorded, unless the report is a query option. In the 2 cases, the information to report must already be known and a second complement, pointed out with the last data of the first complement, will indicate the origin of each of the fields of the report that may be data from the form or other deduced with the same technique of those deducted in the form. Additionally, the information will be determined to create the logical transaction of the database, made up of the creation of new tuples, and modification of others since it cannot be deleted, but must be deactivated.
In this process subsystem, the creation of new tuples from the Process table must be supported, modify if they do not have related transactions and deactivate.
In the transaction subsystem, the tuple is used to prepare transactions, corresponding to the process chosen by a user through the menu corresponding to the role he decides to play.
Transactions subsystem, preparation, registration and production of evidence.
This subsystem must receive a generating event to start the preparation process and, if the information is complete and there is authorization, execute a transaction that can be the registration of a business economic movement or a simple CRUD operation. It must act as a transactional expert system.
When a user decides to prepare a transaction, using a menu option of the role they are playing, or when it is time to prepare a scheduled transaction because the date / time of the automated transaction in a tuple of the ScheduleTransauto table is equal to or less than calendar, a generating event is fired that calls this subsystem passing the corresponding idprocess and other data already known as the dependency of the company that may be affected, cost center, workstation, etc.
In the cases in which the generating event is automatic, if the transaction does not require the approval of a user, the subsystem will prepare the imputations, see flowchart, if it does require approval, it generates a notification for the user who can activate the transaction from preparing the form to view. The subsystem must read and store the data for the preparation of the form in temporary memory using the id of the tuple of the corresponding Processes table, display it and interact with the user until the data is complete and consistent and the user orders the transaction to be executed.
The process continues automatically, in the step prepare imputations of the flowchart, reading the report that corresponds to the idreporte of the tuple of Processes and the complement to generate the report and update the transaction, as explained in the processes subsystem. After preparing the imputations, which generates creation of tuples in the Imputations table and optionally in the Inforce table creating the relational links with the Accounts table and optionally with the tables People, PeopleAccounts, Things, ThingsAccounts, CategoriesAccounts and if the imputation affects a previous imputation, it is linked to this one. The Imputations must be balanced according to the imputation scope, defined by the ambitoimputa column of the tuple of the AccountPlans table related to the tuple of the Accounts table corresponding to the imputation. The imputation set values for each imputation scope must add up to zero (0).
Let’s look at the relationships between the necessary tables.
The IBMSYS programming must be as simple as possible because it is about facilitating its understanding for any developer who wants to study it and for those who want to contribute to its maintenance. Clarity of program coding is essential to allow auditing of the system and for implementers to obtain technical assistance from many different sources so that no one becomes indispensable.
Without sacrificing the good presentation we must apply the principle “what does not work hinders”, we will not have unnecessary decorations. Simplification is the basic goal, starting with the synthesis achieved to define the form of registration and the process of system transactions.
Business management systems have come a long way full of complexities that ensured the difficulty of their implementation and maintenance but that paradoxically enriched many consulting companies and made them almost indispensable, fostering monopolies. The main objective of the simplification is to ensure that the administrators of the companies have the capacity to implement the IBMSYS in their respective areas with the support of the Quality Management and the computer experts who support the other office applications.
A foundation dedicated to the maintenance and regulation of the IBMSYS must be established to provide seriousness, reliability, ease of updating and permanent availability, in order to become a widely used system, until to help governments promote business efficiency.