Chart of Accounts Rollover Using GLUTRIRL
The GL Keys/Objects Rollover (GLUTRIRLxx) utility uploads keys, key parts, objects, object groups from flat file. It is a chart of accounts loading utility. This function allows the user to create new BusinessPlus Org Keys, Key Parts, Object Codes or Object Groups in set format. The utility will take a comma delimited file created by the user (columnar formatted files can also be used to create a new, separate file that can be loaded into BusinessPlus.
Spreadsheet Format
Most input files come from a spreadsheet. The order of the spreadsheet column from left to right must correspond to the field order defined in the Rollover Conversion from top to bottom.
For example, if the KY menu option is being used, the left-most column on the spreadsheet should have the value to be moved into the GL-GR field in the data set GLK-KEY-MSTR. The next column to the right should only have the value to be moved into the GLK-KEY field, column three will be moved into GLK-GRP-PART 1, column four to GLK-GRP-PART 2, etc. The other menu options work in the same fashion.
The org. key Start Date and End Date fields should be in the format MMDDYYYY. Make sure to format the date columns as text; otherwise, the leading zero from the month will be deleted by Excel, and the dates will not load.
To keep Excel from dropping leading zeros on keys, objects, key part values, and object group values, set those columns to Text format in Excel. Leading zeros are important to keys and objects, since they are stored as character fields within the dataset. After the file has been loaded, check it to make sure it has not dropped the leading zeros.
GLK-KEY-MSTR
In the GLK-KEY-MSTR file:
- Every key must have a valid key part value for the key parts which you have defined in GLUPGN.
- It would be a good idea to fill in the medium description field.
- The parent key (control key for this key) should be put into GLK-OVER-KEY.
- Consider filling in the Require Assoc field with "N" (GLK-REQ-ASSOC) if not planning to require an association.
- The GLK-DERIVED field should be filled in with "N" for all keys that are not control keys.
- The GLK-ACCEPT-TR field should be filled in with "Y" except for control keys.
- The GLK-REQUIRE-JL needs to be filled in. It should be "N" unless you do want to require a JL account to be used when this key is used.
- The Inception to Date (ITD) budgeting flag is the GLK-KEY-FLAG(1).
Layout
| GL-GR Pic X(2) | GLK-KEY Pic X(10) | GLK-GRP-PART(1) Pic X(8) | GLK-GRP-PART(2) Pic X(8) | GLK-GRP-PART(3) Pic X(8) | GLK-GRP-PART(4) Pic X(8) | GLK-GRP-PART(5) Pic X(8) | GLK-GRP-PART(6) Pic X(8) | GLK-GRP-PART(7) Pic X(8) | GLK-GRP-PART(8) Pic X(8) | GLK-TITLE-DM Pic X(16) | GLK-TITLE-DL Pic X(30) | GLK-DIR Pic X(30) | GLK-STATUS Pic X(1) | GLK-TYPE Pic X(1) | GLK-GROUP Pic X(2) | GLK-BUDG-CL-CD Pic X(4) | GLK-ALLOC-BW Allocation Pic X(1) | GLK-ALLOC-LEV Allocation Pic X(8) | GLK-OVER-KEY Pic X(10) | GLK-BLOCK-WARN Block/Warn Flag Pic X(1) | GLK-BUDG-LEV Block/Warn Flag Pic X(8) | GLK-OVER-VERS Pic X(2) | GLK-REQ-ASSOC Pic X(2) | GLK-OBJ-TYPES(1) Pic X(2) | GLK-OBJ-TYPES(2) Pic X(2) | GLK-OBJ-TYPES(3) Pic X(2) | GLK-OBJ-TYPES(4) Pic X(2) | GLK-OBJ-TYPES(5) Pic X(2) | GLK-OBJ-TYPES(6) Pic X(2) | GLK-DERIVED Pic X(2) | GLK-ACCEPT-TR Pic X(2) | GLK-REQUIRE-JL Pic X(2) | GLK-MISC(1) Pic X(10) | GLK-MISC(2) Pic X(10) | GLK-MISC(3) Pic X(10) | GLK-MISC(4) Pic X(10) | GLK-MISC(5) Pic X(10) | GLK-MISC(6) Pic X(10) | GLK-MISC(7) Pic X(10) | GLK-MISC(8) Pic X(10) | GLK-COPY-KEY Pic X(10) | GLK-START-DT Pic X(8) | GLK-END-DT Pic X(8) | GLK-KEY-FLAG(1) Pic X(2) | GLK-KEY-FLAG(2) Pic X(2) | GLK-KEY-FLAG(3) Pic X(2) | GLK-KEY-FLAG(4) Pic X(2) | GLK-KEY-FLAG(5) Pic X(2) | GLK-KEY-FLAG(6) Pic X(2) | GLK-KEY-FLAG(7) Pic X(2) | GLK-KEY-FLAG(8) Pic X(2) | GLK-KEY-FLAG(9) Pic X(2) | GLK-KEY-FLAG(10) Pic X(2) | GLK-OLD-KEY Pic X(10) | GLK-SEL-CODE(1) Pic X(10) | GLK-SEL-CODE(2) Pic X(10) | GLK-SEL-CODE(3) Pic X(10) | GLK-SEL-CODE(4) Pic X(10) | GLK-SEL-CODE(5) Pic X(10) | GLK-SEL-CODE(6) Pic X(10) | GLK-SEL-CODE(7) Pic X(10) | GLK-SEL-CODE(8) Pic X(10) | CREATE-WHO Pic X(12) | CREATE-WHEN Pic X(22) | UPDATE-WHO Pic X(12) | UPDATE-WHEN Pic X(22) | GLK-SEC-PART(1) Pic X(8) | GLK-SEC-PART(2) Pic X(8) | GLK-SEC-PART(3) Pic X(8) | GLK-SEC-PART(4) Pic X(8) | GLK-SEC-PART(5) Pic X(8) | GLK-SEC-PART(6) Pic X(8) | GLK-SEC-PART(7) Pic X(8) | GLK-SEC-PART(8) Pic X(8) | GLK-SEC-PART(9) Pic X(8) | GLK-SEC-PART(10) Pic X(8) | GLK-SEC-PART(11) Pic X(8) | GLK-SEC-PART(12) Pic X(8) | GLK-SEC-PART(13) Pic X(8) | GLK-SEC-PART(14) Pic X(8) | GLK-SEC-PART(15) Pic X(8) | GLK-SEC-PART(16) Pic X(8) | GLK-SEC-PART(17) Pic X(8) | GLK-SEC-PART(18) Pic X(8) | GLK-SEC-PART(19) Pic X(8) | GLK-SEC-PART(20) Pic X(8) | GLK-SEC-PART(21) Pic X(8) | GLK-SEC-PART(22) Pic X(8) | GLK-SEC-PART(23) Pic X(8) | GLK-SEC-PART(24) Pic X(8) | GLK-SEC-PART(25) Pic X(8) | GLK-SEC-PART(26) Pic X(8) | GLK-SEC-PART(27) Pic X(8) | GLK-SEC-PART(28) Pic X(8) | GLK-SEC-PART(29) Pic X(8) | GLK-SEC-PART(30) Pic X(8) | GLK-SEC-PART(31) Pic X(8) | GLK-SEC-PART(32) Pic X(8) | GLK-FQA Pic X(40) | GLK-PLAN-START Pic X(8) | GLK-PLAN-END Pic X(8) |
GLO-OBJ-MSTR
In the GLO-OBJ-MSTR file:
- Every object must have a valid object group value for the object groups which you have defined in GLUPGN. Consider putting in the short description. Also, fill in the Require Assoc field with "N" if you don't plan to require association.
- The GLO-TYPE must be filled out. Type can be: "AS" for Assets, "LI" for Liabilities, "FB" for Fund Balance, "RV" for Revenue, "XP" for Expenses, "TI" for Transfers In, "TO" for Transfers Out, and "NA" for not applicable.
- The GLO-BAL-TYPE must be filled out. It can be DR for debit type or CR for credit type.
- The GLO-ALLOW-SUBS fields need to be filled in with "Y" for every subsystem that the object code can be used with.
- The GLO-STATUS field needs to be filled in with A or I.
- The GLO-REQUIRE-JL needs to be filled in. It should be "N" unless you do want to require a JL account to be used when this object is used.
Layout
| GL-GR Pic X(2) | GLO-OBJ Pic X(8) | GLO-GRP-PART(1) Pic X(8) | GLO-GRP-PART(2) Pic X(8) | GLO-GRP-PART(3) Pic X(8) | GLO-GRP-PART(4) Pic X(8) | GLO-GRP-PART(5) Pic X(8) | GLO-GRP-PART(6) Pic X(8) | GLO-GRP-PART(7) Pic X(8) | GLO-GRP-PART(8) Pic X(8) | GLO-OBJ-DM Pic X(16) | GLO-OBJ-DL Pic X(30) | GLO-DIR Pic X(30) | GLO-OBJ-FLAG01 Pic X(2) | GLO-OBJ-FLAG02 Pic X(2) | GLO-OBJ-FLAG03 Pic X(2) | GLO-OBJ-FLAG04 Pic X(2) | GLO-OBJ-FLAG05 Pic X(2) | GLO-OBJ-FLAG06 Pic X(2) | GLO-OBJ-FLAG07 Pic X(2) | GLO-OBJ-FLAG08 Pic X(2) | GLO-OBJ-FLAG09 Pic X(2) | GLO-OBJ-FLAG10 Pic X(2) | GLO-TYPE Pic X(2) | GLO-BAL-TYPE Pic X(2) | GLO-START-DT Pic X(8) | GLO-END-DT Pic X(8) | GLO-ALLOW-SUBS(1) Pic X(1) | GLO-ALLOW-SUBS(2) Pic X(1) | GLO-ALLOW-SUBS(3) Pic X(1) | GLO-ALLOW-SUBS(4) Pic X(1) | GLO-ALLOW-SUBS(5) Pic X(1) | GLO-ALLOW-SUBS(6) Pic X(1) | GLO-ALLOW-SUBS(7) Pic X(1) | GLO-ALLOW-SUBS(8) Pic X(1) | GLO-ALLOW-SUBS(9) Pic X(1) | GLO-ALLOW-SUBS(10) Pic X(1) | GLO-ALLOW-SUBS(11) Pic X(1) | GLO-ALLOW-SUBS(12) Pic X(1) | GLO-ALLOW-SUBS(13) Pic X(1) | GLO-ALLOW-SUBS(14) Pic X(1) | GLO-ALLOW-SUBS(15) Pic X(1) | GLO-ALLOW-SUBS(16) Pic X(1) | GLO-ALLOW-SUBS(17) Pic X(1) | GLO-ALLOW-SUBS(18) Pic X(1) | GLO-ALLOW-SUBS(19) Pic X(1) | GLO-ALLOW-SUBS(20) Pic X(1) | GLO-OLD-NO Pic X(8) | GLO-CURR-CD Pic X(4) | GLO-RECALC-FLAG Pic X(2) | GLO-STATUS Pic X(2) | GLO-REQUIRE-JL Pic X(2) | CREATE-WHO Pic X(12) | CREATE-WHEN Pic X(22) | UPDATE-WHO Pic X(12) | UPDATE-WHEN Pic X(22) | GLO-SEC-PART(1) Pic X(8) | GLO-SEC-PART(2) Pic X(8) | GLO-SEC-PART(3) Pic X(8) | GLO-SEC-PART(4) Pic X(8) | GLO-SEC-PART(5) Pic X(8) | GLO-SEC-PART(6) Pic X(8) | GLO-SEC-PART(7) Pic X(8) | GLO-SEC-PART(8) Pic X(8) | GLO-SEC-PART(9) Pic X(8) | GLO-SEC-PART(10) Pic X(8) | GLO-SEC-PART(11) Pic X(8) | GLO-SEC-PART(12) Pic X(8) | GLO-SEC-PART(13) Pic X(8) | GLO-SEC-PART(14) Pic X(8) | GLO-SEC-PART(15) Pic X(8) | GLO-SEC-PART(16) Pic X(8) | GLO-SEC-PART(17) Pic X(8) | GLO-SEC-PART(18) Pic X(8) | GLO-SEC-PART(19) Pic X(8) | GLO-SEC-PART(20) Pic X(8) | GLO-SEC-PART(21) Pic X(8) | GLO-SEC-PART(22) Pic X(8) | GLO-SEC-PART(23) Pic X(8) | GLO-SEC-PART(24) Pic X(8) | GLO-SEC-PART(25) Pic X(8) | GLO-SEC-PART(26) Pic X(8) | GLO-SEC-PART(27) Pic X(8) | GLO-SEC-PART(28) Pic X(8) | GLO-SEC-PART(29) Pic X(8) | GLO-SEC-PART(30) Pic X(8) | GLO-SEC-PART(31) Pic X(8) | GLO-SEC-PART(32) Pic X(8) |
GLK-GRP-MSTR
Layout
| GL-GR Pic X(2) | GLK-GRP-ID Pic X(4) | GLK-GRP Pic X(8) | GLK-GRP-DM Pic X(16) | GKO-GRP-DIR Pic X(30) | GLK-GRP-DL Pic X(30) | GLK-GRP-FLAG(1) Pic X(2) | GLK-GRP-FLAG(2) Pic X(2) | GLK-GRP-FLAG(3) Pic X(2) | GLK-GRP-FLAG(4) Pic X(2) | GLK-GRP-FLAG(5) Pic X(2) | GLK-GRP-FLAG(6) Pic X(2) | GLK-GRP-FLAG(7) Pic X(2) | GLK-GRP-FLAG(8) Pic X(2) | GLK-GRP-FLAG(9) Pic X(2) | GLK-GRP-FLAG(10) Pic X(2) | AUDIT Pic X(10) | UPDATE Pic X(8) |
GLO-GRP-MSTR
Layout
| GL-GR Pic X(2) | GLO-GRP-ID Pic X(4) | GLO-GRP Pic X(8) | GLO-GRP-DM Pic X(16) | GLO-GRP-DL Pic X(30) | GLO-GRP-DIR Pic X(30) | GLO-GRP-FLAG(1) Pic X(2) | GLO-GRP-FLAG(2) Pic X(2) | GLO-GRP-FLAG(3) Pic X(2) | GLO-GRP-FLAG(4) Pic X(2) | GLO-GRP-FLAG(5) Pic X(2) | GLO-GRP-FLAG(6) Pic X(2) | GLO-GRP-FLAG(7) Pic X(2) | GLO-GRP-FLAG(8) Pic X(2) | GLO-GRP-FLAG(9) Pic X(2) | GLO-GRP-FLAG(10) Pic X(2) | AUDIT Pic X(10) | UPDATE Pic X(8) |
Saving and Downloading the File
Save the spreadsheet file to comma-delimited format. Before you do this, make sure to delete the headings). Use a simple text editor to verify that the comma delimited field order matches the spreadsheet order. Ensure there were no hidden columns in the spreadsheet before it was saved as a comma-delimited file. This input file should have a name which is 8 characters or less, is in uppercase, consists only of alphanumeric characters, and starts with a alphabetic character (for example, "KEYINFIL"). The input file must have a width of 2048 bytes. Question GL7D should accommodate this during the remote upload process.
Running GLUTRIRL
The following question and menu will be displayed when this function is chosen:
Which file conversion utility? The following provides a more complete description of each of the file conversion utilities:
Code | Convert to | Description | Mask |
|---|---|---|---|
KP | Key Parts | This utility will create a new file with the converted Key Parts in it. The file will be in GLK-GRP-MSTR-REC format. | |
KY | Org Keys | This utility will create a new file with the converted Org Keys in it. The file will be in GLK-KEY-MSTR-REC format. | |
OB | Object Codes | This utility will create a new file with the converted Object Codes in it. The file will be in GLO-OBJ-MSTR-REC format. | |
OG | Object Groups | This utility will create a new file with the converted Object Groups in it. The file will be in GLO-GRP-MSTR-REC format. |
Each utility holds similar questions, yet convert data as specified above. the will ask Enter the name of your input file, for example, "KEYINFIL."
"Enter the name of the file to be converted:" | Features a remote file lookup |
Filter out duplicates? | Defaults to "No." |
Test mode only? | Defaults to "Yes" so utility provides a proof before committing to uploading the file. Update checkbox to "No" to commit to the job. |
Each record in the data set must be unique by these fields (concatenated):
- GLK-KEY-MSTR (Org Keys) GL-GR, GLK-KEY
- GLK-GRP-MSTR (Key Parts) GL-GR, GLK-GRP-ID, GLK-GRP
- GLO-OBJ-MSTR (Object Codes) GL-GR, GLO-OBJ
- GLO-GRP-MSTR (Object Groups) GL-GR, GLO-GRP-ID, GLO-GRP
If the tail sheet shows an error like "ERROR: File MKEYGRP.RDF does not exist," that means that the program could not find one of the RDF files used to show input/output format. It could be looking at the admin account when you are in the bsi account. The file might not exist on your site, in which case it needs to be created or loaded. The tail sheets will show a section that will look like:
********************************************
Start Time: 14:48:30
Current Time: 14:50:30
Records in Data File: 407
Current Data Record: 407
Records Written: 407
Percent Complete: 100.00%
********************************************
The Percent Complete should be 100%. Unless you have duplicates, the Records in Data File and the Records Written should be the same.