E-PC: %EffDtCheck Meta-sql Is Expanding With A Join To EFFSEQ For JOB Record (Doc ID 1981464.1)

Last updated on SEPTEMBER 28, 2015

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.53 and later
Information in this document applies to any platform.

Symptoms

On : 8.53.12 version, PeopleCode

ACTUAL BEHAVIOR
---------------
%EffDtCheck meta-sql is expanding with a join to EFFSEQ for JOB record.

When using the META-SQL object %EffDtCheck, it is including the EFFSEQ field as part of the join in the sub-select. This is causing incorrect rows to be returned.

EXPECTED BEHAVIOR
-----------------------
As per documentation at: http://docs.oracle.com/cd/E41633_01/pt853pbh1/eng/pt/tpcl/langref_Meta-SQLReference-073bd4.html#TPCR05_00026

%EffDtCheck is supposed to behave as: "It does not take effective sequence numbers (EFFSEQ) into account."

STEPS
-----------------------
The issue can be reproduced at will with the following steps:

1. The following SQL is being created with a "createsql" statement:

Select 'X'
  from ps_position_data a
 where %EffDtCheck(position_data B, A, %DateIn(:1))
  and position_nbr = :2
  and eff_status = 'A'
  and exists (select 'x'
  from ps_job b
  where b.reports_to = a.position_nbr
  and %EffDtCheck(job c, b, %DateIn(:3))
  and effseq = (select max(effseq)
  from ps_job c
  where c.emplid = b.emplid
  and c.empl_rcd = b.empl_rcd
  and c.effdt = b.effdt)
  and hr_status = 'A')

2. Due to errors with results seen, an SQL trace file was created. Within the SQL trace file, this is what was noticed as being the translated version of the SQL statement:

SELECT 'X'
  from ps_position_data a
 where A.EFFDT = (SELECT MAX(EFFDT)
  FROM PS_POSITION_DATA B
  WHERE B.POSITION_NBR = A.POSITION_NBR
  AND B.EFFDT   and position_nbr = :2
  and eff_status = 'A'
  and exists (select 'x'
  from ps_job b
  where b.reports_to = a.position_nbr
  and b.EFFDT = (SELECT MAX(EFFDT)
  FROM PS_JOB c
  WHERE c.EMPLID = b.EMPLID
  AND c.EMPL_RCD = b.EMPL_RCD
  AND c.EFFSEQ = b.EFFSEQ
  AND c.EFFDT   and effseq = (select max(effseq)
  from ps_job c
  where c.emplid = b.emplid
  and c.empl_rcd = b.empl_rcd
  and c.effdt = b.effdt)
  and hr_status = 'A')

3. The Sub-Select join for B.EFFDT should NOT have a join between C.EFFSEQ and B.EFFSEQ. According to the 8.53 documentation, the EFFSEQ field is supposed to be ignored. Also, the EFFSEQ field follows the EFFDT field, and that would be a double whamo for being ignored.


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
The Meta-Sql object %EffDtCheck is now in question. Any SQL that is using that for a record that contains an EFFSEQ field (JOB etc) could be returning incorrect results.

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