Handle work email vs other emails

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1369
Roger French	1315
mark.cook	1244

DSawyer

New Member

Posts: 2

2/25/2016 5:33 PM

I am working on a project to sync the work emails between my LTM and S3 environments. However, one problem I'm running into is that in LTM, there's no real way to determine which email is the work email versus any of the other emails unless you set the flag IsWorkEmail. however I can find this flag in the background SQL database which is the tool I'm using to perform this sync. Anyone have any methods to help identify work emails from all others and ways to access them directly from the backend database?

Thanks in advance

Dwayne

Woozy

Veteran Member

Posts: 709

2/25/2016 5:52 PM

Hi DSawyer - You just have to join EmployeeContact to Employee. The Employee.UseForWorkEmail is the EmployeeContact.EmployeeContact for the work email address. Here's a sample query (we use DB2 database, so you may have to adapt a little if you're on MSSQL):

Select
emp.HROrganization
,emp.employee
,emp.useforworkemail
,ec.cdemailaddress as work_email
from
{schema}.employee as emp
inner join {schema}.employeecontact as ec
on ec.hrorganization = emp.hrorganization
and ec.employee = emp.employee
and ec.employeecontact = emp.useforworkemail
and ec.active = 1
and hex(ec.deleteflag) = repeat('0',32)
where
emp.hrorganization = {whatever}

Good Luck!

Woozy

Veteran Member

Posts: 709

2/25/2016 5:56 PM

By the way, this is the same way you can get the work phone (Employee.UseForWorkPhone and join to EmployeeContact) and mailing address (Employee.UseForMailing and Employee.UseForPayroll and join to EmployeeAddress).

Tim Cochrane

Veteran Member

Posts: 154

2/26/2016 3:04 PM

@DSawyer - curious what mechanism you going to use once you have the data?? ProcessFlow, manually build "loaduser.xml", etc??

DSawyer

New Member

Posts: 2

2/26/2016 7:16 PM

Thanks Woozy. I'll give that a try. I can't believe I glanced over those fields quite a few times and didn't see that field. Nice to have this place to bounce these questions off of.

@Time - I'm just providing adhoc queries with T-SQL/PL-SQL and transferring information between MSSQL and our Oracle system.

Thanks for the assistance

Dwayne

TimC

Veteran Member

Posts: 84

11/7/2016 7:18 PM

Hello Woozy,
Trying to get the MailingAddress for an employee. What join operation do I do to flag whether an EMPLOYEEADDRESS is mailing or residence?

Thx.

Woozy

Veteran Member

Posts: 709

11/7/2016 7:26 PM

Hi TimC - Employee.UseForPayroll is typically the street address, while Employee.UseForMailing is the postal address. This is assuming, of course, that your organization populates the addresses in that way.

TimC

Veteran Member

Posts: 84

11/8/2016 12:37 PM

Hi Kelly,
Ok, as I suspected. So, the UseForPayroll and UseForMailing contain the IDs of the associated EmployeeAddress?

Thanx!
Tim

TimC

Veteran Member

Posts: 84

11/8/2016 1:53 PM

So, I see the join operation as:
SELECT DISTINCT e.EMPLOYEE,e.NGIVENNAME,e.NFAMILYNAME,ra.PADAADDRESSLINE1 AS ResAddr1, ra.PADAADDRESSLINE2 AS ResAddr2, ra.PAMUNICIPALITY AS ResCity,ra.PASTATEPROVINCE, ra.PAPOSTALCODE AS ResZip,
ma.PADAADDRESSLINE1 AS MailAddr1, ma.PADAADDRESSLINE2 AS MailAddr2, ma.PAMUNICIPALITY AS MailCity,ra.PASTATEPROVINCE AS MailState, ma.PAPOSTALCODE AS MailZip
FROM LANDMARK.LTMTRAIN.EMPLOYEE e
LEFT OUTER JOIN LANDMARK.LTMTRAIN.EMPLOYEEADDRESS ra ON e.EMPLOYEE=ra.EMPLOYEE
AND e.USEFORPAYROLL = ra.EMPLOYEEADDRESS
LEFT OUTER JOIN LANDMARK.LTMTRAIN.EMPLOYEEADDRESS ma ON e.EMPLOYEE=ma.EMPLOYEE
AND e.USEFORMAILING = ma.EMPLOYEEADDRESS

Woozy

Veteran Member

Posts: 709

11/8/2016 2:15 PM

Hi Tim - I'm something of a SQL perfectionist, so these recommendations come from that perspective.

Although your query would probably return what you want, I'd suggest the below query instead for the following reasons:
- Distinct should not be necessary, as there should only be one record per EMPLOYEE, and the joins will restrict the other two queries to one record (max) each
- The HROrganization should always be included in SQL joins and the WHERE statement because it is a key field in almost every table. If you don't include it, the query will be slower and you may get Infor sample records
- I ALWAYS exclude deleted records from every table - Landmark doesn't actually "delete" anything, it just marks the records as deleted, so if you don't exclude the records, they may appear even though you can't see them in Landmark. (Note that the query below uses the DB2 syntax, so yours may be different)
- you may want to only include active address records, but that depends on your data-entry methods (do addresses get marked as inactive, or are they deleted?)

Anyway, here is the query I would use:


SELECT
e.EMPLOYEE
,e.NGIVENNAME
,e.NFAMILYNAME
,ra.PADAADDRESSLINE1 AS ResAddr1
,ra.PADAADDRESSLINE2 AS ResAddr2
,ra.PAMUNICIPALITY AS ResCity
,ra.PASTATEPROVINCE
,ra.PAPOSTALCODE AS ResZip
,ma.PADAADDRESSLINE1 AS MailAddr1
,ma.PADAADDRESSLINE2 AS MailAddr2
,ma.PAMUNICIPALITY AS MailCity
,ra.PASTATEPROVINCE AS MailState
,ma.PAPOSTALCODE AS MailZip
FROM
LANDMARK.LTMTRAIN.EMPLOYEE e
LEFT OUTER JOIN LANDMARK.LTMTRAIN.EMPLOYEEADDRESS ra
ON e.HRORGANIZATION=ra.HRORGANIZATION
AND e.EMPLOYEE=ra.EMPLOYEE
AND e.USEFORPAYROLL = ra.EMPLOYEEADDRESS
AND ra.ACTIVE = 1  -- only use this if your data entry folks are correctly marking the records as "Active"
AND HEX(ra.DELETEFLAG)=REPEAT('0',32)  -- this excludes any deleted records
LEFT OUTER JOIN LANDMARK.LTMTRAIN.EMPLOYEEADDRESS ma
ON e.HRORGANIZATION=MA.HRORGANIZATION
ON e.EMPLOYEE=ma.EMPLOYEE
AND e.USEFORMAILING = ma.EMPLOYEEADDRESS
AND ma.ACTIVE = 1  -- only use this if your data entry folks are correctly marking the records as "Active"
AND HEX(ma.DELETEFLAG)=REPEAT('0',32)  -- this excludes any deleted records
WHERE
e.HRORGANZATION = {whatever}
and HEX(e.DELETEFLAG)=REPEAT('0',32)

TimC

Veteran Member

Posts: 84

11/11/2016 3:33 PM

Thanks Kelly,
We're on the same page. I like the HEX comparison operator as I do want the ones not flagged for delete and Active!
More complete query. Not sure about the Active rules as I'm not a user. So, I feel our assumptions about it's use would be in tandem. Much appreciated!

TimC

Veteran Member

Posts: 84

11/16/2016 3:11 PM

Hello Kelly,
1. How do I find the "name" of a work location?
2. How do I find the "Hiring Manger" name of a job requisition?

Woozy

Veteran Member

Posts: 709

11/16/2016 3:34 PM

Hi TimC,

Maybe I'm misunderstanding your question, but basically you would have to query the associated business classes. You'd want to return HRLocation.Description and Employee.{whatever name fields you want} using the appropriate keys for those business classes.

HRLocation.HROrganization = WorkAssignment.HROrganiztion
HRLocation.HRLocation = WorkAssignment.Location

Employee.HROrganization = JobRequisition.HROrganization
Employee.Employee = JobRequisition.HiringManager

Kelly

Woozy

Veteran Member

Posts: 709

11/16/2016 3:44 PM

Note that if you were trying to find both from JobRequsition, then you'd use HRLocation.HRLocation = JobRequisition.Location rather than HRLocation.HRLocation = WorkAssignment.Location.

TimC

Veteran Member

Posts: 84

11/16/2016 5:07 PM

THANK YOU!!!!
I'm query SQL Server directly for an external Crystal Report.
OK, I have the Employee table. I need the JOBReq's Hiring MGR. I think I also need to relate the jobReq for the current EE. Then I join the jobreq to an additional table to get that HM's name.