Company Setup: Client G/L 2 — Coding a G/L

Overview

General Ledger (GL) rules apply data to fields in the Labor Distribution table. These fields are gl1, gl2, gl3, gl4, gl5, gl6, xgl1, xgl2, xgl3, xgl4, xgl5 and xgl6. Use the following chart to control which payroll entries will be treated as a Debit or Credit on the various GL Reports.

DET Code / Base GL Rule	GL Entries (gl1 – gl6)	XGL Entries (xgl1 – xgl6)
Earnings (det = 'E')	Debit	Credit
Deductions (det = 'D')	Credit	Debit
EE Taxes (det = 'T')*	Credit	Debit
ER Taxes (det = 'T')*	Debit	Credit

* When working with taxes, typically additional conditions are applied to the Base GL Rule to separate the employee and employer taxes and assign them accordingly. See samples in the Advanced GL Instructions section below.

As previously outlined in Client G/L: Basic Introduction, GL Rules are comprised of four parts: Sequence, Condition, Action and Description.

Sequence: Numeric entry that determines the order in which the rules are applied.
Condition: Defines what data is considered by this rule.
Action: Assigns the gl and xgl values to this sequence.
Description: Informational field (Optional). Use to identify what part of the GL the sequence applies to.

Depending on the size and complexity of the client's accounts, use the available six gl and xgl options as necessary to accommodate the client's needs. Before beginning to build the GL Rules, review their account structure carefully. You may find you can take advantage of some of the "power commands" to save time in coding.

note

The fields gl1 through gl6 are 20 positions each.

Examples

The GL Account includes data from the company structure (Company Setup > Department tab). Use the command cc# =. Replace the # in cc# with the appropriate level number from the Departments tab (cc1, cc2, cc3, cc4, cc5).

Typical Examples:

Account number should be 1000 plus the value of cc1. Populate the Action field with gl1 = '1000', gl2 = cc1. The result is gl1 = '1000' and gl2 = the cc1 value for Labor Distribution.
Account number should be 1000 plus the value of cc1 + 00 + cc2 value: gl1 = '1000', gl2 = cc1, gl3 = '00', gl4 = cc2.

In the CalcGL job, the Sequence number controls the order in which the GL Rules are applied. It is possible to have instructions that set a GL value, then have another rule with a higher sequence number reset the values assigned by the previous rule. The value assigned is the value from the rule that last sets the data. Sequence your instructions so broad stroke instructions are earlier in the sequence. Follow these by more specific commands in higher sequence numbers.

Broad stroke sequence example

In our example, sequence 2000 is a broad stroke instruction where ALL earnings codes are set to gl1 = 1200. Sequence 2010 is not a broad stroke command and only earnings coded OT are set to gl1 of 1220. If the command for OT had a sequence number lower than the 2000 sequence, OT would be incorrectly assigned gl1 = 1200.

When building your G/L Rules, best practice recommends that you group the commands by their DET value.

Grouped sequence example

In our sample, all Earnings instructions are in the 2000 series, Deductions in the 4000 series and Taxes in the 6000 series.

This makes reviewing your G/L for corrections easier as like commands are bunched together.

The sequence numbers are gapped by 10 to allow you to add new instructions without renumbering existing sequences.

Using the Condition function

The Condition function lets you group together items that should be assigned the same GL values. Use this field to control what items are assigned to the account number defined by the Action field.

Not every sequence needs a condition entered. A blank condition will apply the instructions from the Action field to every entry for the payroll.

Common Examples:

Parenthesis in Conditions:

det = 'E' and (detcode = '1' or detcode = '2')

Using the In Command:

det = 'E' and detcode in ('1', '2')

Using not equal to:

det = 'E' and detcode <> '1'

You can apply additional conditions to these rules as needed:

det = 'E' and detcode in ('1', '2') and cc1 in ('100', '200')
det = 'E' and detcode = '1' and cc1 <> '100' and cc1 <> '200'

Using the Action function

The Action function will assign gl and xgl values to any items that meet the Condition for this sequence.

You can assign gl and xgl values in one Action. However, you do not need to assign both gl and xgl values in each sequence.

Common Examples:

Action	Result
`gl1 = '1000', xgl1 = 'Cash'`	gl1 = 1000 and xgl1 = Cash
`gl1 = '1010', gl2 = cc1`	gl1 = 1010 and gl2 = the Labor Distribution value from cc1
`gl1 = Left(cc2, 3), gl2 = cc2`	gl1 = left 3 positions from cc2 and gl2 = the Labor Distribution value from cc2
`gl1 = '2000-00-100'`	gl1 = 2000-00-100

Advanced GL Instructions

Left / Right and Mid functions

The Left and Right commands allow you to apply actions to items with similar structures.

The Left command selects data from the left of the field for the number of positions you define. Format is Left(FieldName, number of positions to examine).

If the client has a number of departments (cc1) that begin with 200, you can select them all as follows:

Left(cc1,3) = '200'

Direct Deposit entries all start with PPD then two numbers. To capture all Direct Deposits, use the following:

det = 'D' and Left(detcode,3) = 'PPD'

Capture all deductions beginning with 125:

det = 'D' and Left(detcode,3) = '125'

The Right command works in the same manner as the Left command except it reads values from the Right of the field:

Right(cc1,4) = '2000'

The Mid command is not supported. The workaround for the MID function is the SUBSTRING command. SUBSTRING commands can be used in a Condition or an Action.

The format of the SUBSTRING command is:

SUBSTRING(fieldname, starting position in the field, number of positions to examine)

Condition Example: Find all cc1 entries where positions 4 and 5 of cc1 = 50:

SUBSTRING(cc1, 4, 2) = '50'

Field Name is cc1 / starting position is 4 / number of positions to examine is 2.

You can also use SUBSTRING in multiple condition scenarios:

SUBSTRING(cc1, 4, 2) = '50' or SUBSTRING(cc1, 3, 2) = '90'

Action Example: Use the SUBSTRING function to format a cc1 value to match the desired GL Account Number format. If the department number is 01023000, the GL number needs to be 01-02-30-00:

SUBSTRING(cc1, 1, 2) + '-' + SUBSTRING(cc1, 3, 2) + '-' + SUBSTRING(cc1, 5, 2) + '-' + SUBSTRING(cc1, 7, 2)

Employee and Employer Taxes

This section provides sample Condition Statements for coding Employee/Employer Taxes. Use these commands so you do not need to enter Taxes by Code. These can be very helpful for large multi-state clients.

ALL ER SUI TAXES:

det = 'T' AND detcode in (select tcode from ctax where eetax = 0 and taxtype = 'SUI')

ALL EE SITW TAXES:

det = 'T' AND detcode in (select tcode from ctax where eetax = 1 and taxtype = 'SITW')

ALL ER TAXES:

det = 'T' AND detcode in (select tcode from ctax where eetax = 0)

ALL 941 TAXES:

det = 'T' AND detcode in (select tcode from ctax where eetax = 1 and depositWith = 'FITW')

ALL LOCAL TAXES:

det = 'T' AND detcode in (select tcode from ctax where taxtype = 'LOCAL')

Using Employee ID fields

As a Condition. Your conditions would look something like this if you were going to reference if the item is an Earning, and that the employee's id was either 10 or 20.

(select id from epayhist where elabordist.trans = epayhist.trans) in ('10','20') and Det = 'E'

As an Action. Using employee id numbers as account numbers.

note

The employee id numbers will be displayed in the account number positionally within the six spaces allowed for the field. If the id number is one character, it will be preceded with five spaces; two characters, four leading spaces, etc.

gl1 = (select id from epayhist where trans = elabordist.trans and co = elabordist.co)

Using Employee Name as the Account Number

The employee name can be used in account numbers in various formats.

gl1 = Last Name:

gl1 = (SELECT LastName FROM EInfo WHERE id = (SELECT id FROM EPayHist WHERE ElaborDist.trans = EPayHist.trans) AND EInfo.co = ELaborDist.co)

gl1 = last name, first name:

gl1 = (SELECT LastName + ", " + FirstName FROM EInfo WHERE id = (SELECT id FROM EPayHist WHERE ElaborDist.trans = EPayHist.trans) AND EInfo.co = ELaborDist.co)

gl1 = firstname lastname:

gl1 = (SELECT FirstName + " " + LastName FROM EInfo WHERE id = (SELECT id FROM EPayHist WHERE ElaborDist.trans = EPayHist.trans) AND EInfo.co = ELaborDist.co)

Using other Employee Data Fields in GL

While there is limited practical application, you can use ANY field in EInfo as part of a G/L account number. Simply replace FIELDNAME in the following command with the desired EInfo fieldname. Date fields should be avoided due to format.

Workstate, sex, empstatus, ssn, cc1-5, state, address1/2, firstname, and lastname can all be used if there is a need.

gl1 = (Select FIELDNAME FROM EInfo WHERE id = (SELECT id FROM EPayHist WHERE ElaborDist.trans = EPayHist.trans) AND EInfo.co = ELaborDist.co)

Using the Employee Clock Number in the account number fields

gl1 = (SELECT clock FROM EInfo WHERE id = (SELECT id FROM EPayHist WHERE ElaborDist.trans = EPayHist.trans) AND EInfo.co = ELaborDist.co)

Using fields from the EINFO Table for G/L Conditions

You can use the values in fields found in the EInfo Table when coding GL Rules. The EInfo table contains fields found in the Demographics, Miscellaneous and Status/Positions tabs in Employee Maintenance.

note

Due to the way data is stored, you must always include the company code in the condition.

Our example uses the field EmpType:

det = 'E' and trans in (SELECT EPayHist.Trans FROM EInfo INNER JOIN
EPayHist ON EInfo.co = EPayHist.co AND EInfo.id = EPayHist.id
WHERE (EInfo.co = 'XXXX') AND (EInfo.empType = 'FT'))

You can replace EmpType with EmpStatus, cc1 – 5, misccheck1 – 5, etc.

Using Check Number, Voucher Number and Check Date

Use the following statement to capture the check number for inclusion in your General Ledger:

gl1 = (select checknumber from epayhist where elabordist.trans = epayhist.trans and checknumber <> '0')

This statement will also work for voucher numbers. Simply replace "checknumber" with "vouchernumber":

gl1 = (select vouchernumber from epayhist where elabordist.trans = epayhist.trans and vouchernumber <> '0')

The following command will display the checkdate in mm/dd/yyyy format:

gl1 = (Select CONVERT(VARCHAR, EPayHist.checkDate, 101) FROM EPayHist WHERE EPayHist.Trans = ELaborDist.Trans)

The following command will display the checkdate in Month dd yyyy format. Month will be the abbreviated alpha equivalent:

gl1 = (select left(checkdate, 11) from epayhist where elabordist.trans = epayhist.trans)

Example: 11/01/2015 would be displayed as Nov 01 2015.

Charging Hourly and Salary employees to different accounts

Salaried Employees — replace the co code NNNN in the following statement with the correct company code:

det = 'e' and trans in (select trans from epayhist where id in (select id from erate where co = 'NNNN' and ratecode = 'base' and salary > 0 and enddate >= getdate() and startdate <= getdate()))

Post to an account if the employee is 'FT' and the code is an earning. Replace the XXXX with the correct company code:

DET = 'E' AND trans IN (SELECT trans FROM EPayHist INNER JOIN EInfo ON EPayHist.co = EInfo.co AND EPayHist.id = EInfo.id WHERE empType = 'FT' AND EInfo.co = 'XXXX')

Hourly Employees — replace the co code NNNN in the following statement with the correct company code:

det = 'e' and not(trans in (select trans from epayhist where id in (select id from erate where co = 'abc' and ratecode = 'base' and salary > 0 and enddate >= getdate() and startdate <= getdate())))

Charging specific check types to a different account

Assign entries by check type to different accounts using commands similar to the following. The examples are for Manual checks. Replace Manual to address other check types (Bonus, Commission, etc).

det = 'E' and trans in (select trans from epayhist where checkattributes = 'Manual')
det = 'E' and trans in (select trans from epayhist where checkattributes <> 'Manual')

Department Description instead of the Department Code

gl1 = (Select Left(CDept1.name, 20) FROM CDept1 WHERE CDept1.cc1 = ELaborDist.cc1 AND CDept1.co = ELaborDist.co)

Replace CDept1 with CDept2, CDept3, CDept4 or CDept5 as necessary.

Force Deductions to the Employee's Home Department

gl1 = (Select CC1 FROM EInfo WHERE id = (SELECT id FROM EPayHist WHERE ElaborDist.trans = EPayHist.trans) AND EInfo.co = ELaborDist.co)

This example assumes the Department is stored in CC1. Replace CC1 with CC2, CC3, CC4 or CC5 as necessary.

If the company has multiple levels, you can set multiple GL values as follows:

gl1 = (Select CC1 FROM EInfo WHERE id = (Select id FROM EPayHist WHERE ElaborDist.trans = EPayHist.trans) AND EInfo.co = ELaborDist.co), gl2 = (Select CC2 FROM EInfo WHERE id = (Select id FROM EPayHist WHERE ElaborDist.trans = EPayHist.trans) AND EInfo.co = ELaborDist.co)

Assign account number using company level Earn and Ded Types

Use when a company has multiple earnings codes using the same earn type. The following example selects all earnings for earn type of OT. Replace 'OT' with any valid M3 Earn Type entry ('Reg', 'GTL', 'Cash Tips', etc.):

det='e' and detcode in (select ecode from cearn where earntype = 'OT' and cearn.co = elabordist.co)

For Ded Types:

det='d' and detcode in (select dcode from cded where dedtype = '125' and cded.co = elabordist.co)

Replace the ded type of 125 with any valid Ded Type code.

Using the Case Function

The Case function allows you to assign multiple GL values in the Action field. This is helpful when the client assigns multiple accounts to one earnings, deduction or tax code. This is defined as one condition with multiple actions.

Example: For deduction codes V125, D125, AFLAC, Colonial and COLONIAL P, the account number is 57200 plus a description based on the deduction code. So gl1 is a constant and gl2 varies depending on the deduction code. Our Action will be as follows:

gl1='57200', gl2=case when detcode='V125' then 'VISION'
                     when detcode = 'D125' then 'DENTAL'
                     when detcode = 'COLONIAL' then 'COLONIAL'
                     when detcode = 'COLONIAL P' then 'COLONIAL P'
                     else 'AFLAC' end

warning

Case statements must have the word end as the last entry.

In this example, the value for gl1 is set based on the value in cc1. The condition for this action can be for any deduction, earnings or tax code. Let's assume our condition is det = 'E' and detcode = 'Reg':

gl1=case when cc1 = 'ACCTG' or cc1 = 'Admin' or cc1 = 'HMNRS' or cc1 = 'LOGIS' then '1005100'
         when cc1 = 'SADMN' or cc1 = 'WHSE' then '1005000'
         when cc1 = 'CRUSH' then '1002100'
         when cc1 = 'ELEC' then '1004500'
         when cc1 = 'ENVIR' then '1009000'
         when cc1 = 'EXPLO' then '1007000'
         when cc1 = 'GEOG' then '1006000'
         when cc1 = 'LAB' then '1003000'
         when cc1 = 'LEACH' then '1002200'
         when cc1 = 'MAINT' then '1004000'
         when cc1 = 'MNOPS' then '1001000'
         when cc1 = 'PROPS' then '1002000'
         when cc1 = 'Safe' then '1008000' end, gl2 = '500420'

note

Note the placement of the end statement. It is placed after the final instruction for gl1. It is followed by the gl2 = '500420'. This sets gl2 to 500420 for all the items listed for gl1.

The results for E-Reg for cc1 of CRUSH will be gl1 = 1002100 and gl2 of 500420.

Overview​

Examples​

Using the Condition function​

Using the Action function​

Advanced GL Instructions​

Left / Right and Mid functions​

Employee and Employer Taxes​

Using Employee ID fields​

Using Employee Name as the Account Number​

Using other Employee Data Fields in GL​

Using the Employee Clock Number in the account number fields​

Using fields from the EINFO Table for G/L Conditions​

Using Check Number, Voucher Number and Check Date​

Charging Hourly and Salary employees to different accounts​

Charging specific check types to a different account​

Department Description instead of the Department Code​

Force Deductions to the Employee's Home Department​

Assign account number using company level Earn and Ded Types​

Using the Case Function​

Overview

Examples

Using the Condition function

Using the Action function

Advanced GL Instructions

Left / Right and Mid functions

Employee and Employer Taxes

Using Employee ID fields

Using Employee Name as the Account Number

Using other Employee Data Fields in GL

Using the Employee Clock Number in the account number fields

Using fields from the EINFO Table for G/L Conditions

Using Check Number, Voucher Number and Check Date

Charging Hourly and Salary employees to different accounts

Charging specific check types to a different account

Department Description instead of the Department Code

Force Deductions to the Employee's Home Department

Assign account number using company level Earn and Ded Types

Using the Case Function