Convert Excel csv File to JE - GLUTRIIJ

Use this generic Journal Entry upload utility to load journal entries from a file.

This utility executes the process that converts a previously created interface file into a Journal Entry Set file. This mask may be used for client-specific rollovers. Refer to common code GLAC/TABLE for account conversion options.

Create JEINT Set Files

Create JE Sets using GLJEUB or create your own Set file. GLUTRIIJ may be used as an interface to convert your Set files into a JE Set file. This Set file should be placed in the data directory, or use the remote file upload functionality.

Set files used as input may be in one of three different formats:

1. GLUTRIIJ: Mask is used to accept a comma-delimited .csv or non-comp or comp-formatted input file. Mask only accepts input file if padded to 512 bytes in csv format.  All fields should be formatted as text, including values and dates, to minimize formatting features of Excel. The file name should be in all caps and less than nine characters long.

2. GLUTRIIN: Mask only accepts input file if padded to 282 bytes in non-comp format.

3. GLUTRIIC: Mask only accepts input file if padded to 252 bytes in comp format.

After GLUTRIIJ has been completed, a Set proof (GLJEBP) may be run on the resulting file, or the file may be further edited using GLJEUB before it is distributed using GLJEDS. GLUTRIIJ import aborts posted Sets and displays the message: Set ID is Already Distributed in the tail sheet.

Question Prompts

Journal Entries Set type to be created: The type of JE Set to be created. There are 5 possible JE types:

• UB: Standard

• ML: Multi-Ledger, used with classic batches only

• WA: Wide Account Format, used with classic batches only

• UI: Inter-Fund, used with classic batches only

• UN: Intra-Fund, used with classic batches only

Please enter the name of the Interface File: Select [browse] and find the file saved as a .csv file on your local or network drive. 

Please enter the name of the JE Set: The name to give the JE Set that you create. The name should match column A (glt_batch_id) in the spreadsheet. If you enter a name for the JE set, but SYSTEM is used in column A of the spreadsheet, GLUTRIIJ will use the SYSTEM-derived JE Set instead of the set name you selected, and the tail sheets will display the Set name with this message: System Generated Set ID is.

Would you like the JE Set Sorted? Answer Yes to sort the JE Set.

Do you want like entries summarized? Answer Yes to summarize like entries.

Line Printer Copies, Name, Priority (Pri.): Select a printer from the list, then select the following options:

• Priority: Select a job priority from the list.

• Number of Copies: Select a number of copies from the list.

Optional Questions

Would you like to suppress default mapping? If the default mapping option flag on the Presentation tab of GLUPGN is set to Y, default mapping is performed on the data. To prevent this, set this option to suppress default mapping to Y.

How should offsets be handled? Select from the listed options. GLJE/OFFSET is a common code, and the offset defaults are in the short description. Offsets are used to generate automatic offsetting entries.

• 00 - Use values 'as is' from Set

• 01 - Replace all with GLJE-OFFSET

• 02 - Replace all with 'YY'

• 03 - Replace all with 'NN'

• 04 - Replace all with 'NY'

• 05 - Replace all with 'YN'

• 06 - If blank use GLJE-OFFSET code

• 07 - If blank use 'YY'

• 08 - If blank use 'NN'

• 09 - If blank use 'NY'

• 10 - If blank use 'YN'

Would you like to use an Account Conversion Table? See common code GLAC/TABLE. Select the checkbox to answer Yes, leave blank to answer No.

Number of header lines to skip: If you have helpful headings at the top of the input file that shows you what each column represents, you can use this question to skip the header lines.

One-character field delimiter to use for delimited files: Usually this defaults to , (comma) so that you can input a comma-delimited file. If you are using a different single character, you can enter that here.

Map to new PEIDs using OLDID Association Assignments? Select the checkbox to answer Yes, leave blank to answer No.

Job Priority: Select a job priority from the list.

Job Run Schedule Date: Enter a date or use the calendar to select a date.

Job Run Schedule Time: Enter a time as HH:MM AM or PM, or use the clock to select a time.

Column W

The column W in the .csv file of the GLUTRIIJ import job takes only single character input from the following valid values:

• B, I, O, K, or L: It moves B, I, O, K, L to the Hit Other field respectively.

• E: It moves EN to the Hit Other field.

• Y: It moves GL to the Hit Other field.

• Any other value: It moves N to the Hit Other field.

JE Interface File Layout - GLUTRIIJ

To create a JE batch, the interface file should be created using a spreadsheet. The file should be saved in comma-delimited format.

Field Definitions

The following is a listing of each of the columns with a comprehensive description of each. Each description is prefixed with the data item name, GLJEUB field name, and a COBOL Picture Clause. This is followed by a verbose description and justification details (LJ or RJ), up shift (UP), required or optional (REQ or OPT) characteristics:

glt_batch_id – Batch ID – Pic X(16): Up to sixteen characters representing the Set ID or control code which uniquely identify this set of Journal Entries, for example JE010196. If desired, this field can be set to the name of the new Journal Entry set OR derived from the seed value in the SYNO/BTCHIDJE Common Code. Specify the method to be used in the Edit/Conversion Rule section. LJ REQ UP

glt_ref – JE ID – Pic X(16): Up to 16 characters which uniquely identify a balanced set of journal entries. This becomes the Primary Reference (JE ID) of the resulting transaction that is posted to the General Ledger. If desired, this field can be determined from the seed value in the SYNO/JEID Common Code. Specify the method to be used in the Edit/Conversion Rule section. LJ REQ UP

If this JE is to affect the Encumbrance database, and the APDS/POSWITCH Common Code is active, then an Encumbrance reference (PO or PR) must be entered in this field.

glt_desc – Description – Pic X(30): Up to 30 characters describing this Journal Entry. If this field is left blank, the interface process will fill it with JE INTERFACE MM/DD/YY (where MM is the current month, DD the current day, and YY the current year). OPT

glt_ref2 – Secondary Reference – Pic X(16): An optional second Reference Code of up to 16 characters. This element can be used to store the Purchase Order number if this is an Accounts Payable Journal Entry, otherwise this element is left blank during the interface process. If this JE is to affect the Encumbrance database, this field must contain an Encumbrance reference (PO or PR). If using APDS/POSWITCH Common Code, the Encumbrance reference (PO or PR) should be in the JEID field and this field could be blank. OPT UP

glt_pe_id – PE ID – Pic X(12): Up to 12 characters which uniquely identify the Person/Entity (Vendor, Employee, or Customer) to be associated with this Journal Entry. This field may not be NULL. This field will be set to N/A..N/A unless otherwise noted. OPT

glt_pedb_cd – Pic X(2): (Not displayed on the JE screen) Up to two characters which identify the Person Entity database which holds the record for this Vendor, Employee, or Customer which has incurred this Journal Entry. This element will be converted to contain a P meaning BusinessPlus Person/Entity database, unless otherwise noted. OPT

glt_ck_id – Check ID – Pic X(2): Up to two characters which identify the Check Stock ID used with this Journal Entry. Check Stock IDs must be defined in Nucleus Common Codes file under the code category CKID. The Code value is the two-character Check Stock ID. This field is left blank unless otherwise noted. OPT

glt_ck_no – Check Number – Pic X(8): Up to eight characters which identify the check number this Journal Entry is referencing. This field is left blank unless otherwise noted. OPT

glt_date – Secondary Date – Pic X(8): The Journal Entry date, in YYYYMMDD form. This field becomes the Secondary Date in the Journal Entry set (GLJEUB). When posted to the General Ledger using GLJEDS it is replaced by the date in glt_date2 in this file. When posting using GLJEDM this field will become the Secondary Date in the General Ledger transactions file and glt_date2 will become the Primary Date in the General Ledger transaction file. REQ

glt_gl_key – Account (GL Key name) – Pic X(10): Up to 10 characters which indicate the GL Org Key to which this Journal Entry should be associated. The value in this field must be defined in the glk_key_mstr (see GLUPKY screen) for the Ledger Code specified in glt_gl_gr field. REQ LJ UP
Optionally, for districts using Fully Qualified Accounts (FQA), the glt_gl_key field may contain the FQA and the glt_gl_obj field would be left blank.

glt_gl_obj – Account (GL Obj name) – Pic X(8): Up to eight characters which indicate the GL Object Code to which this Journal Entry should be associated. The value in this field must be defined in the glo_obj_mstr (see GLUPOB screen) for the Ledger Code specified in glt_gl_gr field. REQ LJ UP

glt_jl_key – Account (JL Key name) – Pic X(10): Up to 10 characters which indicate the JL Org. Key to which this Journal Entry should be associated. The value in this field must be defined in the glk_key_mstr (see GLUPKY screen) for the Ledger Code specified in glt_jl_gr field. This field will be set a default value if no JL side account is referenced. OPT LJ UP

glt_jl_obj – Account (JL Obj name) – Pic X(8): Up to 8 characters which indicate the JL Object Code to which this Journal Entry should be associated. The value in this field must be defined in the glo_obj_mstr (see GLUPOB screen) for the Ledger Code specified in glt_jl_gr field. This field will be set to a default value if no JL side account is referenced. OPT LJ UP

glt_offset_intra, glt_offset_inter – Intrafund Offset, Interfund Offset – Pic X(2): A two-character field describing which automatic offsetting entries should be made at distribution time. Valid values for this field are YY, YN, NY, NN, MA, MB, MC, MD, or blank. The first character position is the Intrafund Offset code, the second character position is the Inter Offset code. If no value or conversion rule is specified, this field will be set the value contained in the GLJE/OFFSET Common Code. This field must be set to NN if glt_hit_en is set to E or B. OPT

glt_misc – Misc – Pic X(4): Up to four characters to indicate a miscellaneous code associated with this Journal Entry. This may be used for any type of historical analysis, data extraction, or reporting that may be desired. This field is left blank unless otherwise noted. OPT UP

glt_gl_gr – Account (GL Ledger) – Pic X(2): A two-character code which indicates the General Ledger to which this Journal Entry relates. Ledger Codes in this field must be defined in the glg_gen_mstr (See GLUPGN screen). This field is set to the USER-GL-CODE (which is the primary General Ledger the user is set up to use) unless otherwise noted. REQ UP

glt_jl_gr – Account (JL Ledger) – Pic X(2): A two-character code which indicates the Job Ledger to which this Journal Entry relates. Ledger Codes in this field must be defined in the GLG-GEN-MSTR (See GLUPGN screen). This field is set to the USER-JL-CODE (which is the primary Job Ledger the user is set up to use) unless otherwise noted. If there are no Job Ledgers defined it will be set to --. REQ UP

glt_type – Pic X(2): (Not displayed on GLJEUB screen) Up to two characters representing the type of Journal Entry. Journal Entry Types include ST for Standard Transaction, CL Year End Closing Transaction, AJ Audit Adjustment Entries, AC Accrual Entry. The Journal Entry will be converted with a transaction type of ST unless otherwise noted. REQ UP

glt_units – Units – Pic X(18): The number of units purchased for this particular Journal Entry. This element is stored as 5 decimal places. For example, a value of 5 will end up as 5.00000. 6.78 will become 6.78000. Include the decimal place but no commas unless you encapsulate the value in double quotes.

– Units of Measure – : NOT CURRENTLY USED.

glt_dr – Debit – Pic X(18): The debit amount, in dollars and cents, to be distributed to the given GL and/or JL account reference. If the amount placed in this field is negative, the amount will be treated as a credit. Include the decimal place but no commas unless you encapsulate the value in double quotes.

glt_cr – Credit – Pic X(18): The credit amount, in dollars and cents, to be distributed to the given GL and/or JL account reference. If the amount placed in this field is negative, the amount will be treated as a debit. Include the decimal place but no commas unless you encapsulate the value in double quotes.

glt_hit_en – Hit Other – Pic X(1): Used in conjunction with the glt_en_type, this field flags the transaction to update another module. Review explanations in the table. OPT UP

glt_hit_en – Hit Other – Pic X(1): Used in conjunction with the glt_en_type, this field flags the transaction to update another module. Review explanations in the table. OPT UP

glt_en_type – Other Type – Pic X(2): Used in conjunction with the Hit Other (glt_hit_en), this field contains a value used to update a field in another table. This field is left blank unless otherwise noted. OPT UP

glt_budget_over – Budget Override – Pic X(1): If users creating Journal Entries have the required Budget Override security defined in Nucleus Database Security (NUUPUS), a Y can be placed in this field to override budget blocks when using the interactive Journal Entry editing screens and allow exit from and thus creation of an over-budget Journal Entry. This field is set to N unless otherwise noted. OPT UP

This flag only allows updates to the Journal Entries. Correction of the over-budget condition is still required before the Journal Entries will post. 

glt_wo – Account (WO) – Pic X(12): Up to 12 characters which identify the work order number associated with this Journal Entry. Work Orders numbers must be defined in the wo_mstr (See the WOUP Screen). OPT

glt_prep_id – Prepared By – Pic X(8): An eight-character code used to indicate the Nucleus User ID of the individual who is responsible for the preparation of this Journal Entry. If left blank, this field will be set to the User ID of the current user who runs the interface. OPT LJ UP

glt_contract_no – Contract No – Pic X(16): Up to 16 characters which identify the primary contract reference that is associated with this JE. The contract number can be entered to track activity against a contract. The contract number may be used to correct Accounts Payable entries in the General Ledger only. If the Hit Other (glt_hit_en) field is set to B, then this field must contain the Budget Fiscal Year (positions 1-4), Budget Version (positions 5-6), Level (positions 7-8), Reason Code (positions 9-12), and Fund Type (positions 13-14). OPT

glt_date2 – Secondary Date – Pic X(8): Up to eight characters which identify the primary date of the Journal Entry, in YYYYMMDD format. OPT

– JEID Aprvl Class –: NOT CURRENTLY USED.

– TRNS Aprvl Class –: NOT CURRENTLY USED.

glt_view_a, glt_view_c, glt_view_m, glt_view_u – View Flags – Pic X(4): 4 one-character codes that indicate the accounting views to which these transactions will relate. Enter Y or N in each position to indicate the views you would like to see. REQ UP

Position View
1 Accrual (Y by default)
2 Cash (Y by default)
3 Modified Accrual (N by default)
4 User Defined (N by default)