SES Company Directory Invalid Search Results due to ‘Address Type’ Omitted from Joins in ‘Address’ View
Last updated on OCTOBER 19, 2017
Applies to:PeopleSoft Enterprise HCM Human Resources - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.
This article includes the information previously included on Doc ID 21654181.1, which has been deleted.
This relates to the delivered query and views that sit behind the Company Directory search definition. The underlying ‘address’ view has issues in two places. The ‘address type’ has been omitted from the joins.
This results in people who are not returned for the relevant search being performed.
The replication steps deal with the data set-up only, not the configuration and set-up of the ‘Company Directory’ search definition.
This can be replicated for either ‘half’ of the view, ie those with business addresses and those without. The replication steps concentrate on those with business addresses.
The first step is to identify people who have the following A business address -
Another type of address with a later effective date than that of the latest business - address
Use the following SQL to find these, or set them up yourself.
Any changes to the data will require a re-crawl of the Company Directory search definition.
--COUNT(*) COUNT1, MIN(A.EMPLID), MAX(A.EMPLID) FROM PS_ADDRESSES A
WHERE EXISTS (SELECT 1
FROM PS_ADDRESSES A1
WHERE A1.EMPLID = A.EMPLID
AND A1.ADDRESS_TYPE = 'BUSN'
AND A1.EFFDT = (SELECT MAX(A2.EFFDT) FROM PS_ADDRESSES A2 WHERE A2.EMPLID = A1.EMPLID AND A2.ADDRESS_TYPE = A1.ADDRESS_TYPE)
AND A1.EFFDT != (SELECT MAX(A2.EFFDT) FROM PS_ADDRESSES A2 WHERE A2.EMPLID = A1.EMPLID)
Look at the address details for person, current business address. Searching on this, no results are returned. The person has address details returned from their Job Data location.
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