%EffDtCheck PeopleCode Built-in Function

Your rating: None Average: 3.7 (7 votes)

Effective dated quires are used often in PeopleSoft and a great build-in function that will save you time is the %EffDtCheck. Lets take a closer look!

Syntax

%EffDtCheck(recordname [correlation_id1], correlation_id2, as_of_date)

The %EffDtCheck construct expands into an effective date subquery suitable for a Where clause.

recordname: Specify the record name to use as the record in the effective-date checking. This can be bind variable, a record object, or a record name in the form recname.
correlation_id1: (Optional) Specify the letter used inside the effective-dating subselect. If this parameter isn’t specified, recordname is used.
correlation_id2: Specify the letter already assigned to the main record in the From clause of the SQL statement.
as_of_date:
Specify the date to use in the effective date. This can be a bind variable, a variable, or a hard-coded date. The value for as_of_date is automatically wrapped in%DateIn unless as_of_date is already wrapped in %DateIn or refers to other database columns.

Here are some examples from PeopleSoft books:

The following is a generic code sample:

SELECT. . .
FROM. . .
WHERE %EffDtCheck(recordname correlation_id, as_of_date)

The example code resolves into the following:

SELECT . . .
FROM. . .
WHERE correlation_id.EFFDT = (SELECT MAX(EFFDT) FROM recordname
WHERE recordname.KEYFIELD1 = correlation_id.KEYFIELD1
AND recordname.KEYFIELD2 = correlation_id.KEYFIELD2
AND. . .
AND recordname.EFFDT <= %DATEIN(as_of_date))

In the following example, &Date has the value of 01/02/1998. The example &Rec object has an EFFDT key field.

SQLExec("SELECT FNUM FROM PS_REC A WHERE %EffDtCheck(:1, A, :2)", &Rec, &Date);
This example code resolves into the following:
"Select FNUM from PS_REC A where EFFDT = (select MAX(EFFDT)
from PS_REC
where PS_REC.FNUM = A.FNUM
and PS_REC.EFFDT <= %DateIn(’1998-01-02’) )"

The following example uses correlation IDs:

SELECT A.DEPTID
FROM %Table(DEPT_TBL) A
WHERE
%EffDtCheck(DEPT_TBL B, A, %CurrentDateIn)
AND A.EFF_STATUS = ’A’

This example code resolves into the following:

SELECT A.DEPTID
FROM %Table(DEPT_TBL) A
WHERE
A.EFFDT =
(SELECT MAX(B.EFFDT)
FROM DEPT_TBL B
WHERE
A.SETID = B.SETID
AND A.DEPTID = B.DEPTID
AND B.EFFDT <=%CurrentDateIn)
AND A.EFF_STATUS = ’A’

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
Guest's picture
Guest (not verified)
how to create a subpage

send me the solution how to create a newpage in peoplesoft application designer

CompShack's picture
User offline. Last seen 4 years 38 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Check the forum

The same question has been asked on the fourm - you can find the answer here.

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Guest's picture
Guest
Re: %EffDtCheck PeopleCode Built-in Function

Helpful. Thanks for taking the time.