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.
Page 1 of 3123 > >>
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
    (3490 points)
    Veteran Member
    Posts:708


    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
    (3490 points)
    Veteran Member
    Posts:708


    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

    You are not authorized to post a reply.
    Page 1 of 3123 > >>