Archive for the ‘PeopleSoft’ Category

There is no delivered process to synchronize the Business Email Address on the Operator Id and the Business Email Address for the Person in PeopleSoft HCM. Workflow uses the Operator Id (Primary) Email Address and the hover users the Business Email Address from the Person.

Normally the Operator Id Business Email Address is maintained, possibly even by LDAP and some Id Provisioning technique. What is not normally maintained it the Person Business Email Address unless a business process informs HR to make this change.

To automate this synchronization a process can be scheduled to run to maintain these two email address in sync. This can be done as a batch process or as an add-on to the sign-on PeopleCode. Here’s the SQL for your reference:

/* Sync existing Business Email Addresses */
UPDATE PS_EMAIL_ADDRESSES
SET EMAIL_ADDR = (
SELECT MIN(X2.EMAILID)
FROM PSOPRDEFN X1
, PSUSEREMAIL X2
WHERE X2.OPRID = X1.OPRID
AND X1.EMPLID = PS_EMAIL_ADDRESSES.EMPLID
AND X2.EMAILTYPE = ‘BUS’)
WHERE E_ADDR_TYPE = ‘BUSN’
AND EXISTS (
SELECT ‘X’
FROM PSOPRDEFN X1
, PSUSEREMAIL X2
WHERE X2.OPRID = X1.OPRID
AND X1.EMPLID = PS_EMAIL_ADDRESSES.EMPLID
AND X2.EMAILTYPE = ‘BUS’ )
AND NOT EXISTS (
SELECT ‘X’
FROM PSOPRDEFN X1
, PSUSEREMAIL X2
WHERE X2.OPRID = X1.OPRID
AND X1.EMPLID = PS_EMAIL_ADDRESSES.EMPLID
AND X2.EMAILTYPE = ‘BUS’
AND X2.EMAILID = PS_EMAIL_ADDRESSES.EMAIL_ADDR )
AND NOT EXISTS (
SELECT ‘X’
FROM PSOPRDEFN X1
, PSUSEREMAIL X2
WHERE X2.OPRID = X1.OPRID
AND X1.EMPLID = PS_EMAIL_ADDRESSES.EMPLID
AND X2.EMAILTYPE = ‘BUS’
AND X2.EMAILID = ‘ ‘ )

/* To Insert Business Email Address as Primary Email Address where none exists */
INSERT INTO PS_EMAIL_ADDRESSES (EMPLID
, E_ADDR_TYPE
, EMAIL_ADDR
, PREF_EMAIL_FLAG)
SELECT A.EMPLID
, ‘BUSN’
, B.EMAILID
, ‘Y’
FROM PSOPRDEFN A
, PSUSEREMAIL B
, PS_PERSON C
WHERE C.EMPLID = A.EMPLID
AND B.OPRID = A.OPRID
AND B.EMAILTYPE = ‘BUS’
AND B.EMAILID <> ‘ ‘
AND NOT EXISTS (
SELECT ‘X’
FROM PS_EMAIL_ADDRESSES X
WHERE X.EMPLID = A.EMPLID
AND X.E_ADDR_TYPE = ‘BUSN’)
AND NOT EXISTS (
SELECT ‘X’
FROM PS_EMAIL_ADDRESSES X
WHERE X.EMPLID = A.EMPLID
AND X.PREF_EMAIL_FLAG = ‘Y’)

/* To Insert Business Email Address as Alternate Email Address */
INSERT INTO PS_EMAIL_ADDRESSES (EMPLID
, E_ADDR_TYPE
, EMAIL_ADDR
, PREF_EMAIL_FLAG)
SELECT A.EMPLID
, ‘BUSN’
, B.EMAILID
, ‘N’
FROM PSOPRDEFN A
, PSUSEREMAIL B
, PS_PERSON C
WHERE C.EMPLID = A.EMPLID
AND B.OPRID = A.OPRID
AND B.EMAILTYPE = ‘BUS’
AND B.EMAILID <> ‘ ‘
AND NOT EXISTS (
SELECT ‘X’
FROM PS_EMAIL_ADDRESSES X
WHERE X.EMPLID = A.EMPLID
AND X.E_ADDR_TYPE = ‘BUSN’)