SQL to find Rules and associated Application Engine Sections

Get the AE Section name from the Rule ID.

select AE_SECTION
from PS_TL_RULE_DEFN
where TL_RULE_ID = 'XXX'

Get the Rule ID from the AE Section name.

select TL_RULE_ID
from PS_TL_RULE_DEFN
where AE_SECTION = 'XXX'

Nationality in PeopleSoft

I was recently asked how you could store Nationality in PeopleSoft. Some companies use birth country to hold an employees nationality, which may or may not be the same, but PeopleSoft now allows you to hold this information under Citizenship.

Home > Workforce Administration > Personal Information > Citizenship, Identification Data > Citizenship/Passport

Under citizenship you can hold country and details of a persons passport.  As an employee can have multiple  countries on this page it also caters for the possibility of dual nationality.

Associated Tables: PS_CITIZENSHIP and PS_CITIZEN_PSSPRT

Note that the same tables are also used to hold citizenship details for dependants.

Current XLAT Values for a Field

The following field selects current active values for a field from the XLAT table.

-- ---------------
-- Dump XLAT Table
-- ---------------
select XLAT.FIELDVALUE,
       XLAT.XLATLONGNAME,
       XLAT.XLATSHORTNAME
  from XLATTABLE_VW XLAT
 where XLAT.FIELDNAME    = 'EMPL_STATUS'
   and XLAT.EFFDT = (select max(XLAT1.EFFDT)
                       from XLATTABLE_VW XLAT1                    
                      where XLAT1.FIELDNAME  = XLAT.FIELDNAME
                        and XLAT1.FIELDVALUE = XLAT.FIELDVALUE
                        and XLAT1.EFFDT <= SYSDATE)
   and XLAT.EFF_STATUS = 'A'

Finding The Current Database Name

Often you want to find out what the database is that you are logged in to. This can be useful when spooling the output of a SQL script to a log file or checking you are in the right environment before executing a script.

Oracle
In Oracle you can get the current database name by querying the table v_$database

i.e.

select name
 from sys.v_$database

SQL Server
The equivalent way of finding it in SQL Server is to use the function DB_Name.

e.g.

select DB_Name()

Finding a table name in SQL Server

To find a table name in SQL Server query the table sysobjects.

For example to list all PeopleSoft tables on the system, you could use: -

select *
from sysobjects
where name like 'PS_%'

PeopleSoft Payroll Interface Timeout Issue

I recently came across a problem where a vanilla payroll interface was failing due to a timeout occurring. The error message given was: -

13884 08.28.58 300.014 ErrorReturn-> -1 - Child program F:\psoft8\HR88\HR8DEV\CBLBINA\PIPINRUN REMOTEA/MICROSFT/HR8DEV/ASTOLDT/////0/people/%PSWD%/9aec1b63-2af2-11dc-86cf-cbed13adba68 (5568) did not complete in allowed time period (300 seconds). Killing process. (2,-1) At PI_INRUN.2EXPORT.GBL.default.1900-01-01.Step01.OnExecute PCPC:263 Statement:1

On investigation I found that the solution was to change two timeout values in two configuration tables.

psappsrv.cfg
[PSAPPSRV]
;=========================================================================
; Settings for PSAPPSRV
;=========================================================================

;-------------------------------------------------------------------------
; UBBGEN settings
Min Instances=1
Max Instances=3
Service Timeout=300

psprcs.cfg
[PSAPPSRV]
;=========================================================================
; Settings for PSAPPSRV
;=========================================================================

;-------------------------------------------------------------------------
; UBBGEN settings
Min Instances=1
Max Instances=3
Service Timeout=300

In both instances I changed the timeout value from 300 to 0 (no limit) before using PSADMIN to stop and restart the Application Server and Process Scheduler. The net result was that the interface ran to a successful completion when re-run.

Huzzah!!!

PeopleSoft Payroll Interface SQL

/****************************************/
/*                                      */
/* Dump the Payroll Interface Run Table */
/*                                      */
/****************************************/
select *
from PS_PI_RUN_TBL
order by PI_RUN_NUM desc

/*************************/
/*                       */
/* Dump PI Partic table. */
/*                       */
/*************************/
select *
from PS_PI_PARTIC

/*************************************/
/*                                   */
/* Dump PI Partic table, latest run. */
/*                                   */
/*************************************/
select PIP.*
from PS_PI_RUN_TBL PUN,
     PS_PI_PARTIC  PIP
where PUN.PI_RUN_NUM = (select max(PUN1.PI_RUN_NUM)
                         from PS_PI_RUN_TBL PUN1)
 and PUN.PI_RUN_NUM = PIP.PI_RUN_NUM

/*******************************/
/*                             */
/* Dump PI PARTIC detail table */
/*                             */
/*******************************/
select *
from PS_PI_PARTIC_EXPT

/********************************************/
/*                                          */
/* Dump PI Partic detail table, latest run. */
/*                                          */
/********************************************/
select PIE.*
from PS_PI_RUN_TBL PUN,
     PS_PI_PARTIC_EXPT PIE
where PUN.PI_RUN_NUM = (select max(PUN1.PI_RUN_NUM)
                         from PS_PI_RUN_TBL PUN1)
 and PUN.PI_RUN_NUM = PIE.PI_RUN_NUM

/***************************************************/
/*                                                 */
/* Dump Pay Calendars associated with a particular */
/* Payroll Interface run.                          */
/*                                                 */
/***************************************************/
select RUN.PI_RUN_NUM,
     CAL.*
from PS_PI_RUN_TBL RUN,
     PS_PAY_CALENDAR CAL
where RUN.PI_RUN_NUM = 999
 and RUN.RUN_ID     = CAL.RUN_ID

/******************************/
/*                            */
/* Dump the PI Empl ID Table. */
/*                            */
/******************************/
select *
from PS_PI_EMPLID_TBL

/********************************************/
/*                                          */
/* List fields in a Payroll Interface File. */
/*                                          */
/********************************************/
select DEF.PI_FIELD_SEQ,
     DEF.PI_FIELD_ID,
     FLD.DESCR,
     FLD.PI_FIELD_LEN,
     FLD.PS_FIELD1_NM,
     DEF.PI_KEY_FIELD,
     DEF.PI_GROUP_ID
from PS_PI_DEFN_FIELD DEF,
     PS_PI_FIELD_TBL  FLD
where DEF.PI_FILE_ID = 'XXX'
 and DEF.EFFDT        = (select max(DEF1.EFFDT)
                           from PS_PI_DEFN_FIELD DEF1
                          where DEF.PI_FILE_ID = DEF1.PI_FILE_ID
                            and DEF1.EFFDT    <= SYSDATE)    and DEF.PI_SYSTEM_ID = FLD.PI_SYSTEM_ID    and DEF.PI_FIELD_ID  = FLD.PI_FIELD_ID    and FLD.EFFDT        = (select max(FLD1.EFFDT)                              from PS_PI_FIELD_TBL FLD1                             where FLD.PI_SYSTEM_ID = FLD1.PI_SYSTEM_ID                               and FLD.PI_FIELD_ID  = FLD1.PI_FIELD_ID                               and FLD1.EFFDT      <= SYSDATE)

/***************************************************/ 
/*                                                 */
/* List interface files that contain a given Field */
/*                                                 */
/***************************************************/
select DEF.PI_FILE_ID,
        DEF.PI_FIELD_SEQ,
        DEF.PI_FIELD_ID,
        FLD.DESCR,
        FLD.PI_FIELD_LEN,
        FLD.PS_FIELD1_NM, 
        DEF.PI_KEY_FIELD,
        DEF.PI_GROUP_ID
   from PS_PI_DEFN_FIELD DEF,
        PS_PI_FIELD_TBL  FLD
  where DEF.PI_SYSTEM_ID = 'CHR'
    and DEF.PI_FIELD_ID IN ('E001')
    and DEF.EFFDT        = (select max(DEF1.EFFDT)
                              from PS_PI_DEFN_FIELD DEF1
                             where DEF.PI_FILE_ID = DEF1.PI_FILE_ID
                               and DEF1.EFFDT    <= SYSDATE)
    and DEF.PI_SYSTEM_ID = FLD.PI_SYSTEM_ID
    and DEF.PI_FIELD_ID  = FLD.PI_FIELD_ID
    and FLD.EFFDT        = (select max(FLD1.EFFDT)
                              from PS_PI_FIELD_TBL FLD1
                             where FLD.PI_SYSTEM_ID = FLD1.PI_SYSTEM_ID
                               and FLD.PI_FIELD_ID  = FLD1.PI_FIELD_ID
                               and FLD1.EFFDT      <= SYSDATE)