Handle work email vs other emails

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!

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).

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

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

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.

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.

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

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

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:

[code] 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) [/code]

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!

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?

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

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.

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.