Case Note Email page Add Recipients Search by First Name and Last Name Not Returning any Results (Doc ID 2041277.1)

Last updated on AUGUST 31, 2015

Applies to:

PeopleSoft Enterprise CRM HelpDesk - Version 9.2 and later
Information in this document applies to any platform.

Symptoms

When attempting to email a Higher Education(HE) case note and click Add Recipient link on the Email page, search by First Name and Last Name is not returning any results but works ok when Person Id criteria is used.

Steps to Reproduce
1. Logon to CRM 9.2
2. Make sure you have a worker with Terminated Job but with active consumer role in CRM.
3. Open the HE Case created for the terminated worker prior to termination.
4. Go to Notes tab
5. Select a note and click email
6. Click Add Recipients link
7. Search using FirstName and LastName - No Search Results returned

Here's the SQL ran behind the scene:
SELECT A.PERSON_ID, B.FIRST_NAME, B.LAST_NAME, A.CONTACT_FLAG,
A.PREF_WF_ROUTE_MTHD, A.BO_ID, B.COUNTRY_NM_FORMAT, 'A' FROM PS_RD_PERSON A,
PS_BO_NAME B WHERE B.PRIMARY_IND = 'Y' AND B.FIRST_NAME_SRCH LIKE :1 AND
B.LAST_NAME_SRCH LIKE :2 AND A.BO_ID = B.BO_ID AND NOT EXISTS(SELECT 'X' FROM
PS_RD_WRKR_JOB E WHERE A.PERSON_ID = E.PERSON_ID AND E.EFFDT = (Select
MAX(L.EFFDT) FROM PS_RD_WRKR_JOB L WHERE E.PERSON_ID = L.PERSON_ID AND
L.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))) UNION SELECT
A.PERSON_ID, B.FIRST_NAME, B.LAST_NAME, A.CONTACT_FLAG, A.PREF_WF_ROUTE_MTHD,
A.BO_ID, B.COUNTRY_NM_FORMAT, C.EMPL_STATUS FROM PS_RD_PERSON A, PS_BO_NAME
B, PS_RD_WRKR_JOB C WHERE B.PRIMARY_IND = 'Y' AND B.FIRST_NAME_SRCH LIKE :3
AND B.LAST_NAME_SRCH LIKE :4 AND A.BO_ID = B.BO_ID AND A.PERSON_ID =
C.PERSON_ID AND C.EFFDT = (Select MAX(L.EFFDT) FROM PS_RD_WRKR_JOB L WHERE
C.PERSON_ID = L.PERSON_ID AND L.EFFDT <=
TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) AND C.EMPL_STATUS = 'A'
Bind-1 type=2 length=4 value=AMY%
Bind-2 type=2 length=4 value=123%
Bind-3 type=2 length=4 value=AMY%
Bind-4 type=2 length=4 value=123%
----

8. Use Email Address criteria to search:
 
Returned a row but with blank Name and Id.
Here's the SQL generated behind the scene when email address is used to search:
--
select A.PERSON_ID from PS_RD_PERSON A, PS_RD_PERSON_NAME B WHERE A.BO_ID =
:1 AND A.PERSON_ID = B.PERSON_ID AND B.PRIMARY_IND = 'Y'
Bind-1 type=19 length=26 value=10775050972513324751876770
--

9. Use Person Id criteria to search -  works perfectly.  Returned a row with all fields populated  (Name, Email Address and Id)

Here's the SQL generated in the trace when Person Id is used as criteria to search:
SELECT A.PERSON_ID, B.FIRST_NAME, B.LAST_NAME,
B.BO_NAME_DISPLAY,A.CONTACT_FLAG, A.PREF_WF_ROUTE_MTHD, A.BO_ID,
B.COUNTRY_NM_FORMAT, 'A' FROM PS_RD_PERSON A, PS_BO_NAME B WHERE A.PERSON_ID
= :1  AND A.BO_ID = B.BO_ID AND B.PRIMARY_IND = 'Y' AND NOT EXISTS (SELECT
'X' FROM PS_RD_WRKR_JOB E WHERE A.PERSON_ID = E.PERSON_ID  AND E.EFFDT =
(Select MAX(L.EFFDT) FROM PS_RD_WRKR_JOB L WHERE E.PERSON_ID = L.PERSON_ID
AND L.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) AND
E.EFFSEQ = (SELECT MAX(C2. EFFSEQ) FROM PS_RD_WRKR_JOB C2 WHERE E.PERSON_ID =
C2.PERSON_ID AND C2.EFFDT = E.EFFDT) AND E.HR_STATUS='A') UNION SELECT
A.PERSON_ID, B.FIRST_NAME, B.LAST_NAME, B.BO_NAME_DISPLAY,A.CONTACT_FLAG,
A.PREF_WF_ROUTE_MTHD,A.BO_ID, B.COUNTRY_NM_FORMAT, C.EMPL_STATUS FROM
PS_RD_PERSON A, PS_BO_NAME B ,PS_RD_WRKR_JOB C ,PS_RD_WRKR_ASGN D  WHERE
A.PERSON_ID =:2 AND A.BO_ID = B.BO_ID AND A.PERSON_ID = C.PERSON_ID AND
C.PERSON_ID = D.PERSON_ID AND C.WRKR_ASGN_SEQ = D.WRKR_ASGN_SEQ AND
D.PRIMARY_IND = 'Y' AND C.EFFDT = (Select MAX(L.EFFDT) FROM PS_RD_WRKR_JOB L
WHERE C.PERSON_ID = L.PERSON_ID AND C.WRKR_ASGN_SEQ = L.WRKR_ASGN_SEQ AND
L.EFFDT <= TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')) AND C.EFFSEQ
= (SELECT MAX(C2. EFFSEQ) FROM PS_RD_WRKR_JOB C2 WHERE C.PERSON_ID =
C2.PERSON_ID AND C.WRKR_ASGN_SEQ = C2.WRKR_ASGN_SEQ AND C.EFFDT = C2.EFFDT)
AND B.PRIMARY_IND = 'Y'  AND C.EMPL_STATUS = 'A'
Bind-1 type=2 length=6 value=400874
Bind-2 type=2 length=6 value=400874

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms