PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 11/16/2016 12:07 PM by  TimC
Handle work email vs other emails
 14 Replies
Sort:
You are not authorized to post a reply.
Author Messages
DSawyer
Database Administrator
Private
New Member
(4 points)
New Member
Posts:2


Send Message:

--
02/25/2016 12: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
    Private
    Private
    Veteran Member
    (3469 points)
    Veteran Member
    Posts:701


    Send Message:

    --
    02/25/2016 12: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!
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    Woozy
    Private
    Private
    Veteran Member
    (3469 points)
    Veteran Member
    Posts:701


    Send Message:

    --
    02/25/2016 12: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).
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    Tim Cochrane
    Private
    Private
    Veteran Member
    (450 points)
    Veteran Member
    Posts:154


    Send Message:

    --
    02/26/2016 10:04 AM
    @DSawyer - curious what mechanism you going to use once you have the data?? ProcessFlow, manually build "loaduser.xml", etc??
    Tim Cochrane - Principal LM/IPA Consultant
    DSawyer
    Database Administrator
    Private
    New Member
    (4 points)
    New Member
    Posts:2


    Send Message:

    --
    02/26/2016 2: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
    Systems Analyst
    Private
    Veteran Member
    (220 points)
    Veteran Member
    Posts:84


    Send Message:

    --
    11/07/2016 2: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
    Private
    Private
    Veteran Member
    (3469 points)
    Veteran Member
    Posts:701


    Send Message:

    --
    11/07/2016 2: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.
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    TimC
    Systems Analyst
    Private
    Veteran Member
    (220 points)
    Veteran Member
    Posts:84


    Send Message:

    --
    11/08/2016 7:37 AM
    Hi Kelly,
    Ok, as I suspected. So, the UseForPayroll and UseForMailing contain the IDs of the associated EmployeeAddress?

    Thanx!
    Tim
    TimC
    Systems Analyst
    Private
    Veteran Member
    (220 points)
    Veteran Member
    Posts:84


    Send Message:

    --
    11/08/2016 8:53 AM
    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
    Private
    Private
    Veteran Member
    (3469 points)
    Veteran Member
    Posts:701


    Send Message:

    --
    11/08/2016 9:15 AM
    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)
    

    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    TimC
    Systems Analyst
    Private
    Veteran Member
    (220 points)
    Veteran Member
    Posts:84


    Send Message:

    --
    11/11/2016 10:33 AM
    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
    Systems Analyst
    Private
    Veteran Member
    (220 points)
    Veteran Member
    Posts:84


    Send Message:

    --
    11/16/2016 10:11 AM
    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
    Private
    Private
    Veteran Member
    (3469 points)
    Veteran Member
    Posts:701


    Send Message:

    --
    11/16/2016 10:34 AM
    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
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    Woozy
    Private
    Private
    Veteran Member
    (3469 points)
    Veteran Member
    Posts:701


    Send Message:

    --
    11/16/2016 10:44 AM
    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.
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    TimC
    Systems Analyst
    Private
    Veteran Member
    (220 points)
    Veteran Member
    Posts:84


    Send Message:

    --
    11/16/2016 12: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.
    You are not authorized to post a reply.