Records (Sun Identity Manager 8.1 Resources Reference)

Sun Identity Manager 8.1 Resources Reference

Records

There are three records (two views and one table) that must be defined within the Application Designer. The following record descriptions illustrate a typical implementation. The records can be customized to the needs of the implementation by adding or changing fields.

AUDIT_EFFDT_LH View

The AUDIT_EFFDT_LH view is polled by the PeopleSoft Active Sync resource adapter. Identity Manager uses the following fields to query for events that have not yet been processed:

AUDIT_PROC_ORDER. This field must specify the Key, Search Key, List Box Item, and From Search Field keys.
AUDIT_PROC_END. This field must specify the Key, Search Key, List Box Item, and Through Search Field fields.
EMPLID and EMPL_RCD. These are required non-key properties that are used by an Identity Manager query to fetch employee data.

All other fields in the AUDIT_EFFDT_LH table are optional.

The following table describes the Use Display characteristics of the AUDIT_EFFDT_LH view:

Field Name	Type	Key	Ordr	Dir	Srch	List	Sys	Default
`AUDIT_PROC_ORDER`	Char	Key	1	Asc	Yes	Yes	No
`AUDIT_PROC_END`	Char	Key		Asc	Yes	Yes	No
`AUDIT_STAMP`	DtTm				No	No	No
`EFFDT`	Date				No	No	No	%date
`AUDIT_OPRID`	Char				No	No	No
`AUDIT_ACTN`	Char				No	No	No
`AUDIT_RECNAME`	Char				No	No	No
`EMPLID`	Char				No	No	No	”NEW’
`EMPL_RCD`	Nbr				No	No	No

Information in the last audit entry is stored in Identity Manager as a “lastProcessed” configuration object to be used (and updated) on subsequent searches of the AUDIT_EFFDT_LH view. Maintenance of the lastProcessed Configuration object by the PeopleSoft Active Sync resource adapter prevents records from being processed more than once.

The following SQL code is used to generate the AUDIT_EFFDT_LH view:

SELECT audit1.AUDIT_PROC_ORDER AS AUDIT_PROC_ORDER
,audit1.AUDIT_PROC_ORDER AS AUDIT_PROC_END
,audit1.AUDIT_STAMP AS AUDIT_STAMP
,audit1.EFFDT AS EFFDT
,audit1.AUDIT_OPRID AS AUDIT_OPRID
,audit1.AUDIT_ACTN AS AUDIT_ACTN
,audit1.AUDIT_RECNAME AS AUDIT_RECNAME
,audit1.EMPLID AS EMPLID
,CAST(audit1.EMPL_RCD AS INTEGER) AS EMPL_RCD FROM PS_AUDIT_PRS_DATA audit1
WHERE audit1.AUDIT_PROC_DATE <= %CurrentDateIn
AND NOT EXISTS (
SELECT * FROM PS_AUDIT_PRS_DATA audit2
WHERE audit2.AUDIT_PROC_DATE <= %CurrentDateIn
AND audit2.AUDIT_PROC_ORDER > audit1.AUDIT_PROC_ORDER
AND (audit2.EMPLID = audit1.EMPLID AND audit2.EMPL_RCD = audit1.EMPL_RCD) );

The final line in this SQL code sample prevents Identity Manager from seeing operations with effective dates until the effective date has arrived.

AUDIT_PRS_DATA Table

The AUDIT_PRS_DATA table must contain the following fields:

AUDIT_PROC_ORDER. This field must specify the Key, Search Key, List Box Item, and From Search field keys. In addition, this field must be set to Required so that PeopleSoft puts a non-null integrity constraint on the database column.
AUDIT_PROC_DATE. This field must specify the Alternate Search Key, List Box Item. In addition, this field must be set to Required so that PeopleSoft puts a non-null integrity constraint on the database column.
EMPLID and EMPL_RCD. These are required non-key properties that are used by an Identity Manager query to fetch employee data.

All other fields in the AUDIT_PRS_DATA table are optional.

The following table describes the Use Display characteristics of the AUDIT_PRS_DATA view:

Field Name	Type	Key	Ordr	Dir	Srch	List	Sys	Default
`AUDIT_PROC_ORDER`	Char	Key	1	Asc	Yes	Yes	No
`AUDIT_PROC_DATE`	Date	Alt		Asc	No	No	No
`AUDIT_STAMP`	DtTm				No	No	No	%date
`AUDIT_OPRID`	Char				No	No	No	”ANON’
`AUDIT_ACTN`	Char				No	No	No	”C’
`AUDIT_RECNAME`	Char				No	No	No	”ANON’
`EMPLID`	Char				No	No	No	”NEW’
`EFFDT`	Date				No	No	No	%date
`EMPL_RCD`	Nbr				No	No	No

PERS_SRCH_LH View

The PERS_SRCH_LH view must contain the EMPLID and EMPL_RCD fields, with the Key, Search Key, and List Box Item keys selected. All other fields provide the data that is synchronized with Identity Manager. It is up to the PeopleSoft Active Sync form to map this data into the Identity Manager user account.

The following table describes the Use Display characteristics of the PERS_SRCH_LH view:

Field Name	Type	Key	Ordr	Dir	Srch	List	Sys
`EMPLID`	Char	Key	1	Asc	Yes	Yes	No
`EMPL_RCD`	Nbr	Key	2	Asc	Yes	Yes	No
`NAME`	Char				No	Yes	No
`LAST_NAME_SRCH`	Char				No	Yes	No
`SETID_DEPT`	Char				No	Yes	No
`DEPTID`	Char				No	Yes	No
`ADDRESS1`	Char				No	Yes	No
`EMPL_STATUS`	Char				No	Yes	No
`FIRST_NAME`	Char				No	Yes	No
`LAST_NAME`	Char				No	Yes	No
`MIDDLE_NAME`	Char				No	Yes	No
`REPORTS_TO`	Char				No	Yes	No
`JOBCODE`	Char				No	Yes	No
`COMPANY`	Char				No	Yes	No
`NAME_INITIALS`	Char				No	Yes	No
`COUNTRY`	Char				No	Yes	No
`PHONE`	Char				No	Yes	No
`CITY`	Char				No	Yes	No
`STATE`	Char				No	Yes	No
`POSTAL`	Char				No	Yes	No

The following SQL code is used to generate the PERS_SRCH_LH view:

Note –

For your convenience, the peoplesoft/idm.zip file on the installation media contains an SQL script file named pers_srch_lh.sql that duplicates the following SQL code.

SELECT P.EMPLID
 ,A.EMPL_RCD
 ,P.NAME
 ,P.LAST_NAME_SRCH
 ,A.SETID_DEPT
 ,A.DEPTID
 ,P.ADDRESS1
 ,A.EMPL_STATUS
 ,P.FIRST_NAME
 ,P.LAST_NAME
 ,P.MIDDLE_NAME
 ,A.REPORTS_TO
 ,A.JOBCODE
 ,A.COMPANY
 ,P.NAME_INITIALS
 ,P.COUNTRY
 ,P.PHONE
 ,P.CITY
 ,P.STATE
 ,P.POSTAL
 FROM PS_Job A
 , PS_PERSONAL_DATA P
 WHERE A.EMPLID = P.EMPLID
 AND A.EffDt = (
 SELECT MAX(C.EffDt)
 FROM PS_Job C
 WHERE C.EmplID = A.EmplID
 AND C.EMPL_RCD = A.EMPL_RCD
 AND C.EffDt <= %CurrentDateIn)
 AND A.EffSeq = (
 SELECT MAX(D.EffSeq)
 FROM PS_Job D
 WHERE D.EmplID = A.EmplID
 AND D.EMPL_RCD = A.EMPL_RCD
 AND D.EffDt = A.EffDt)

The WHERE clause returns the current employee record for the given employee ID. PeopleSoft allows multiple records for a given employee, each of which has its own effective date/effective sequence. This clause returns the record whose effective date/effective sequence pair is the latest out of all those that are already effective (whose effective date has occurred).

The WHERE clause returns null for an employee whose sunrise date is in the future.