Hello SAGE Citizens and welcome to volume 4. I’ve still had no feedback from anyone on my posts. I can only assume my articles are so detailed and so exciting that I have created this image of being all knowing and unapproachable. Let me assure you, I am completely down to earth and love to interact with the people I help. Also, if you have any questions or want me to go more in depth on anything, let me know.

Today we will be talking about the logical bridge that can be created between HRMS (10x) data and Employee Self-service. There are a lot of reasons why someone might want to build custom reporting or queries to analyze and report off of info that isn’t in just one side or the other and to that end I intend to edify. Let us start with a common hypothetical instance. Let us say that you are starting to run into your license count limit in ESS. You are only licensed to have 500 login. You normally only have 300 active employee at any given time but because of high turnover there are a ton of accounts you need to go in there and delete to free up space. The problem is that the software does not have any special report already that tells you what logins are tied to terminated employees and that are just taking up space. To that end you will need to create a crystal report or an SQL query , or a SQL view that you can link a crystal report up to. For the purposes of today’s conversation we will focus on the concept of the query because this gives you all the info you need.

The first step is determining what tables are necessary to get this job done and how they relate.

[selfservicedbname].dbo.tusers: (tusers for short). This table is native to ess and stores info like

• Useridno: this is the unique ee reference in ess that allows you to link to other ess tables.
• UserAbraSuiteLogicalPrimaryKey: this is the linkage that tells you the employees logins related empno and company code in HRMS.
• Username: this is the ess username linked to this login account.

[hrmsdbname].dbo.hrpersnl: this is the primary backbone of HRMS and is where all relevant info about employee is stored such as Name, social, empno, company, DOB, active status etc.

• P_empno: employee number
• P_company: company code
• P_lname: employee last name
• P_fname: employee first name
• P_snn: employee social
• P_active: employee active status (A,T,L,N)
• P_termdate: termination date for employee

The second step is determining how these tables link to each other.

In this case

• The rightmost 3 characters from tusers. UserAbraSuiteLogicalPrimaryKey are linked to P_company in hrpersnl.
• The leftmost 9 character from tusers. UserAbraSuiteLogicalPrimaryKey are linked to p_empno in hrpersnl.

The third step is to determine the exclusionary criteria that defines what records you are looking for to come back in your query. In this case we are looking for terminated employee who have logins so in this case it’s just p_actve=’T’.

Finally it all needs to be brought together into one unified script. Keep in mind the names of the databases can very as this is configurable by the users so in my example my database names just happen to be abraemployeselfservice for my ess database and sagehrms_live for the hrms data. You also should be aware that the collation between the two databases are dispirit so you will need to qualify P_empno, p_company as collate SQL_Latin1_General_CP1_CI_AS.

select

P_empno,

p_company,

LEFT(UserAbraSuiteLogicalPrimaryKey,9) as empno,

right(UserAbraSuiteLogicalPrimaryKey,3) as company,

p_active,

p_lname,

p_fname,

p_termdate

from

AbraEmployeeSelfService.dbo.tUSERS,

sagehrms_live.dbo.hrpersnl

where

LEFT(UserAbraSuiteLogicalPrimaryKey,9)=p_empno collate SQL_Latin1_General_CP1_CI_AS and

right(UserAbraSuiteLogicalPrimaryKey,3)=p_company collate SQL_Latin1_General_CP1_CI_AS and

p_active='T'

This will return data that resembles the following.

You can now use this grid to determine what employee logins are on the table for delete from login maintenance.

For example 2 let’s say your goal is to query the system for all pending time off requests for active employee’s for a future date that are approved or pending approval.

1. Determine the data needed.

[hrmsdatabasename].dbo.hrpersnl: covered in prior example.

[essdatabasename].dbo.Tusers: covered in prior example.

[essdatabasename].dbo.ttimeoffrequests: This is where all ESS time off request info is stored.

• UserIdNo                                                                            This is the userid of the employee who made the request
• TimeOffRequestPlanLogicalPrimaryKey                 this is the attendance plan the transaction should count against
• TimeOffRequestFirstDayOff                                       first day off of the request
• TimeOffRequestTotalHours                                        total requested hours
• TimeOffRequestStatusIdNo                                        -10600 pending, -10601 approved, -10602 processed,-10604 rejected
• TimeOffRequestReasonCode                                     reason code of time off request
• TimeOffRequestTimestamp                                        when the request was actually placed.

2. How do these tables inter relate.

Hrpersnl->tusers (already discussed)

Tusers->ttimeoffrequests:     tusers.useridno=ttimeoffrequests.useridno

3. What type of exclusionary criteria applies.

Hrpersnl.p_active=’A’

Ttimeoffrequests.timeoffrequestfirstdayoff>getdate()

Ttimeoffreqeusts.timeoffreqeustsstatusidno in (-10600,-10601)

4. Now we piece the syntax together. Be careful this time. You know have two fields in two different databases that now have the exact same name (useridno). They MUST be qualified.

select

P_empno,

p_company,

p_active,

p_lname,

p_fname,

TimeOffRequestPlanLogicalPrimaryKey as 'plan',

TimeOffRequestFirstDayOff firstday,

TimeOffRequestTotalHours as hours,

TimeOffRequestStatusIdNo as status_code,

TimeOffRequestReasonCode reason_code,

TimeOffRequestTimestamp as date_requested

from

AbraEmployeeSelfService.dbo.tUSERS,

sagehrms_live.dbo.hrpersnl,

AbraEmployeeSelfService.dbo.tTIME_OFF_REQUESTS

where

LEFT(UserAbraSuiteLogicalPrimaryKey,9)=p_empno collate SQL_Latin1_General_CP1_CI_AS and

right(UserAbraSuiteLogicalPrimaryKey,3)=p_company collate SQL_Latin1_General_CP1_CI_AS and

tusers.UserIdNo=tTIME_OFF_REQUESTS.useridno and

p_active='A' and

TimeOffRequestIdNo in (-10601,-10600) and

TimeOffRequestFirstDayOff>GETDATE()

This should give you a comprehensive list of all employees who are active and have time off requests in the future that are either pending or approved.

I hope this discussion and practical examples is helpful and that you have found the topic enjoyable and even to some point exciting. If you have any questions please feel free to comment and I promise I will get back to you. Thanks and have a great day SAGE Citizens.