Establishing the Parent/Child Relationship
By Harry Lotrowski
To best harness the power of the Financial Reporting Platform, you must meet three major requirements: Organize your data to make the parent of any entry available on that entry’s record, make sure no child exists more than once, define the topmost level as a child with an empty parent field.
Making the parent of any entry available on that entry’s record may be called a "recursive" structure (Figure 1) in that the parent value will exist as a child value on another record. Furthermore, by joining a parent value to the record where that value is the child record, the next higher level parent can be
determined. This record also must contain the caption that will be displayed on the report. These fields, which will carry the properties PARENT_OF and CAPTION, may either be physically on the record or defined within the synonym.
| CHILD |
PARENT |
Caption |
| ASSETS |
|
Assets |
| CURR_ASSETS |
ASSETS |
Current Assets |
| CASH_AND_EQV |
CURR_ASSETS |
Cash and Cash Equivalent |
| SHRT_TRM_INV |
CURR_ASSETS |
Short-Term Investments |
| ACCTS_REC |
CURR_ASSETS |
Accounts Receivable |
| NET_PAT_RECV |
ACCTS_REC |
Net Receivable |
| GRS_PAT_RECV |
NET_PAT_RECV |
Gross Receivable |
| CONTRAC_ALLW |
NET_PAT_RECV |
Contractual Allowances |
| BAD_DBT_ALLW |
NET_PAT_RECV |
Allowance for Bad Debt |
| RSC_RECV |
ACCTS_REC |
Regional Srvc Cntr Receivable |
| AFFIL_RECV |
ACCTS_REC |
Affiliate Receivable |
| INTERZONE_RC |
ACCTS_REC |
Interzone Receivables |
Figure 1
As for defining the topmost level as a child with an empty parent field, you must create a process to extract and transform the existing chart of accounts in your system, since most systems do not have this inherently available. You may write these processes with WebFOCUS, SQL or almost any programming language. The
resulting file may be stored in any format or it can be generated on the fly for concurrence at run time. While it is difficult to present the methodology for all accounting systems, I will address a few in this article.
Some systems, such as Lawson Software, carry the hierarchy in one file and detail entries in another. The hierarchy is viewable based on its depth and sequence number, as shown in Figure 2.
| Account |
Description |
Dep |
Seq |
ID |
| ASSETS |
Assets |
01 |
000001 |
000002 |
| CURR_ASSETS |
Current Assets |
02 |
000002 |
000003 |
| CASH_AND_EQV |
Cash and Cash Equivalent |
03 |
000003 |
000004 |
| SHRT_TRM_INV |
Short-Term Investments |
03 |
000004 |
000005 |
| ACCTS_REC |
Accounts Receivable |
03 |
000005 |
000006 |
| NET_PAT_RECV |
Net Receivable |
04 |
000006 |
000007 |
| GRS_PAT_RECV |
Gross Receivable |
05 |
000007 |
000008 |
| CONTRAC_ALLW |
Contractual Allowances |
05 |
000008 |
000009 |
| BAD_DBT_ALLW |
Allowance for Bad Debt |
05 |
000009 |
000010 |
| RSC_RECV |
Regional Srvc Cntr Receivable |
04 |
000010 |
000312 |
| AFFIL_RECV |
Affiliate Receivable |
04 |
000011 |
000011 |
| INTERZONE_RC |
Interzone Receivables |
04 |
000012 |
000313 |
Figure 2
To process this into a parent/child relationship, we need to use the value found on the previous record for the lesser depth value. The code to accomplish this could look something like Figure 3. A set of fields, SA0-SA7, is set up to hold the last occurrence of the entry for the particular depth. WKSUM is then
calculated to hold the value of the key in the last row with a lesser depth. This is the parent value.
TABLE FILE GLCHARTSUM
PRINT CHART_NAME CHART_SECTION SUMRY_ACCT_ID DESCRIPTION
COMPUTE SA0/A12 = ' ';
SA1/A12 = IF DEPTH EQ 1 THEN SUMMARY_ACCT ELSE LAST SA1;
SA2/A12 = IF DEPTH EQ 2 THEN SUMMARY_ACCT ELSE LAST SA2;
SA3/A12 = IF DEPTH EQ 3 THEN SUMMARY_ACCT ELSE LAST SA3;
SA4/A12 = IF DEPTH EQ 4 THEN SUMMARY_ACCT ELSE LAST SA4;
SA5/A12 = IF DEPTH EQ 5 THEN SUMMARY_ACCT ELSE LAST SA5;
SA6/A12 = IF DEPTH EQ 6 THEN SUMMARY_ACCT ELSE LAST SA6;
SA7/A12 = IF DEPTH EQ 7 THEN SUMMARY_ACCT ELSE LAST SA7;
COMPUTE WKSUM/A12 = IF DEPTH GT LAST DEPTH THEN LAST SUMMARY_ACCT ELSE
IF DEPTH EQ LAST DEPTH THEN LAST WKSUM ELSE
IF DEPTH EQ 1 THEN SA0 ELSE
IF DEPTH EQ 2 THEN SA1 ELSE
IF DEPTH EQ 3 THEN SA2 ELSE
IF DEPTH EQ 4 THEN SA3 ELSE
IF DEPTH EQ 5 THEN SA4 ELSE
IF DEPTH EQ 6 THEN SA5 ELSE
IF DEPTH EQ 7 THEN SA6 ELSE
LAST WKSUM;
ACHILD/A14=SUMMARY_ACCT;
APARENT/A14=WKSUM;
BY SEQ_NUMBER
BY DEPTH
ON TABLE HOLD AS LAWTMP1 FORMAT FOCUS
END
|
Figure 3
The Lawson model joins this data to the detail accounts based on a common key, thereby establishing the hierarchy.
Typically, you may have to use a star schema with multiple levels of the hierarchy on the same record to produce the chart of accounts. Also common is the use of a snowflake schema, or a collection of tables, to construct the parent/child relationships. The approach to both of these is pretty much the same. You
construct the levels of the hierarchy one at a time, extracting the parent, the child and the child description and saving it to a hold file. You may then concatenate the hold files and produce a single chart of accounts file.
Most ERP systems use a variation of the code I have shown you. Some already have the capability while some of the others require additional processing.
J.D. Edwards uses a single file to store the chart of accounts. Based upon the level indicator and the sequential account number, the parent may be derived by its predecessor’s account number and level.
Essbase required no additional processing. All dimensions have a "flattened" hierarchy definition within the synonym and are created with the parent and caption properties already defined.
While MS Analysis Services already has a parent/child dimension available, that part of the hierarchy may depend on the measures being available. An alternative, if required, is to use the underlying table.
Peoplesoft requires a more complex process. Information Builders has developed a SQL-stored procedure to create the needed structure. This procedure traverses a point-in-time view of the TREE_NODE_TBL to create rollups. It is parameterized so that any required customization is relatively
simple. This procedure is available upon request.
In SAP we use a transaction to export an image of the chart of accounts to a sequential file. Then, using a process based upon the indentations and sequence within the chart, the required format is created.
To summarize, all ERP systems already have a process to display the chart of accounts either by itself or as part of financial reporting. The key is to avail yourself of this formatted and/or sequentially structured data to assign the parent account value to the child account.
 
|
