This article discusses iScala ERP 2.3. I am not an iScala specialist but worked on some internals for a while.
There are a lot of things to talk about in iScala; it is an ERP and thus naturally sophisticated. From the business perspective the specialized “functional” modules (e.g. accounting, logistics and others) are what are important but as a software developer I will focus in my blog on technical topics. This post will be an overview and serve as an introduction to other future posts.
I explain some general background information about the nature of the tables and databases representing iScala companies and will also maintain a list of other posts that I write about the subject here.
One can easily explore iScala’s technical internals by using SQL Server Management Studio (SSMS). My assumption here is that the iScala implementation is using SQL Server and not some other DBMS. SQL Server Profiler tracing feature can also help greatly if you want to see how the various iScala screens work and which tables they affect.
There are two kinds of databases within iScala ERP:
- Administration database: ScaSystemDB
- There is only one ScaSystemDB per iScala server.
- Information about iScala companies and settings, such as company database and server and authentication information, are also stored in ScaSystemDB.
- This is also where user information and their configuration, such as permissions, are stored.
- The iScala Administration Console works with ScaSystemDB and the configuration accessed there is stored in ScaSystemDB.
- From the iScala Administration Console, right clicking on the “Companies” node and choosing “New Company…” opens the “Add Company” dialog. There you choose the database name and server and the unique 2-character Company Code. The Company Code is used in table names as I explain below.
- There is a database for each iScala company and these databases contain the various tables for business information such as finance and logistics. Some of these are year dependent: they change every year and most commonly a new fresh table is created when a new year is set up in iScala. I discuss these in the next section “Business Tables”.
- Each company database also contains user defined tables that could be made company and year specific.
Business tables are those that store business information such as stock info, logistics, accounting, and other functional information. To a newcomer, table names might be a little confusing as they are not descriptive (for example the purchase orders table is not called PurchaseOrders) but rather a combination of codes. Once you have the naming convention in mind, understanding what a table is for will be much easier.
A table name is 8 characters long and consists of the following:
MODULE + SERIAL + COMPANY + YEAR
To illustrate, “PC” is used to refer to the purchase order module. Let’s say my ‘Flying Rabbits’ company was assigned code FR and the year is 2015. The serial number is just an incrementing number from 01 to 99. The module code and serial number identify the purpose of the table and I call them the “table identifier” but it does not even indicate company or year. The Purchase Order Head table will have the name PC01FR00. The year is 00 because PC is not year dependent. Knowing that this table is not year dependent is important especially if the year in question is 2000 or 2100. 00 here does not indicate those years! An example of a year dependent table would be the General Ledger Transactions table GL06.
Each of these elements is two digits and so most table names are 8 characters. You can use a dictionary that describes the various module tables, their codes and even description of the columns. You can find such a dictionary here.
Table column names are named in a serial nonsensical manner such as PC01001, PC01002, PC01003, etc. The column name is prefixed with the table identifier (the first 4 characters, i.e. Module + Serial). Many tables have their primary key or primary identifying piece of information as the first column. For example a purchase order number is stored in the first column in purchase order related tables (i.e., PC01001 . Column PC01001, PC03001 and PC04001 in all of the tables PC01, PC03 and PC04 represent the order number and the primary key respectively.
Table Identifier = Module + Serial
Column Name = Table Identifier + Column Serial
As for understanding the columns, there is no practical way to know the purpose of the PC0023 column without a dictionary such as the one mentioned above.
Understanding all this helps a lot of you are looking into the database directly. Understanding this is vital for the effectiveness of writing procedures that read or write data from the database. For example it helps one realize the suitability of using dynamic SQL queries when interacting with company tables because you can make the company name and year a parameter and then construct the desired table name dynamically.
In this post I talked about iScala from a partial SQL Server perspective. I just talked about the databases, tables, and columns and only mentioned stored procedures as a possibility. In future posts I’ll elaborate more about how you can write stored procedures that interact with the iScala database both from an administrative and functional points of view.