SES Company Directory Invalid Search Results due to ‘Address Type’ Omitted from Joins in ‘Address’ View (Doc ID 2077558.1)

Last updated on AUGUST 08, 2016

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.

Symptoms

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.

STEPS

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.

SELECT

DISTINCT A.EMPLID

--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.

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