Meta tables for Integration Broker

****************
IB Tables
******************
-- Service
PSOPERATION; --all services
PSOPERATIONLANG; -- language specific descriptions

-- Service Operation Versions
PSOPRVERDFN; -- all the service operations
PSOPRVERDFNLANG; -- language specific descriptions
PSOPRVERDFNPARM; -- holds request / response message information , queue

-- Service Operation Handlers
PSOPRHDLR; -- all the handlers
PSOPRHDLRLANG; -- language specific descriptions
PSOPERATIONAC; -- handler details referring to DMS application class
PSOPERATIONDMS; -- handler details referring to DMS script
PSOPERATIONCI; -- handler details referring to CI's

-- Service Operation Routings
PSIBRTNGDEFN; -- all routings
PSRTNGDFNLANG; -- language specific descriptions
PSIBRTNGSUBDEFN; -- routing alias information
PSRTNGDFNPARM; -- routing transformation information
PSRTNGDFNCONPRP; -- routings specific connector override information
PSOPRGENRTGPARM; -- holds list of auto generated routings

Secondry pages and do modal

A small point about DoModal fucntion and Secondary page.
You know that Secondary page can be called from a hyperlink/Push Button either by 'PeopleCode Command' or by 'Secondary page directly'

1. When we use Secondary page option in hyperlink, it suppreses the FieldChange/FieldEdit events and if we need to do any changes we cannot do. When we are using the Secondary page option, we dont need to insert the secondary page on the main page at the corresponding level it called from. As the Modal page knows where it has to comeback, we dont need to specify it.

2. When we use PeopleCode Command option in hyperlink, as we will be using DoModal PeopleCode and assume that the secondary page is not inserted on the page, the modal page doesnt know where to return its results. So, its mandatory that we need to include the secondary page on the main page where we have this hyperlink (on which the PeopleCode is written). Since, its based on PeopleCode, we can make use of FieldChange and FieldEdits of the hyperlink.

SQL to check Table lock

select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine,
b.program,
b.type,
b.client_info
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;

Find Defintion References

Impact analysis of the existing object plays a major role during development or customization of the PeopleTools objects. Developers make use of the “Find Definition Reference” utility in Application designer to identify the list of impacted / dependent objects. However, this Application designer utility has its own limitations. For E.g. When a record is referenced inside a SQLExec function will not be traced by this utility.

1.There are two records (HEX_TEST & HEX_TEST_REC) in the project (HEX_TEST_PRJ). The HEX_TEST record is referenced inside the SQLExec function in the HEX_TEST_REC.EMPLID.FieldDefault PeopleCode. In such cases when the ‘Find Definition Reference’ for HEX_TEST runs, the HEX_TEST_REC will not be listed as impacted or dependency objects. This may lead to incomplete impact analysis of the record reference. (It can be taken cared by the ‘Find In’ utility but it ends in huge volume of time for Impact analysis)

In such cases, the PeopleCode can be rewritten to pass the record name as parameters to the SQLExec function. This helps the developers to list the record in SQLExec function as impacted object when “Find Definition Reference” runs through the application designer

Who Modified the code last

SELECT A.OBJECTVALUE1 RECORD, A.OBJECTVALUE2 FIELD, A.OBJECTVALUE3 EVENT, TO_CHAR(A.LASTUPDDTTM,’YYYY-MM-DD-
HH24.MI.SS.”000000?‘),A.LASTUPDOPRID FROM PSPCMPROG A
WHERE A.OBJECTVALUE1 = ‘&RECNAME’
AND OBJECTVALUE2 = ‘&FIELDNAME’
AND UPPER(OBJECTVALUE3) = UPPER(‘&EVENTNAME’)

Monitor Process Scheduler

There are many approaches to monitor the PeopleSoft process scheduler. One of the options that come to mind is to have a script running on the OS hosting the process scheduler to monitor the OS process. But having process schedulers running on multiple OS (PSUNX and PSNT) will need development of scripts running on both servers.

My preferred method is to use the below SQL to monitor all the process schedulers running on the database.

SELECT A.SERVERNAME, B.XLATSHORTNAME,B.FIELDNAME,B.FIELDVALUE,TO_CHAR(B.EFFDT,’YYYY-MM-DD’)
  FROM PSSERVERSTAT A, PSXLATITEM B
  WHERE B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PSXLATITEM B_ED
        WHERE B.FIELDNAME = B_ED.FIELDNAME
          AND B.FIELDVALUE = B_ED.FIELDVALUE
          AND B_ED.EFFDT <= SYSDATE)
     AND B.FIELDNAME = ‘SERVERSTATUS’
    AND B.FIELDVALUE = A.SERVERSTATUS

DEBUG statement in SQRs

Consider a scenario where you write a code which needs to be executed only when you are trying to debug the program. For e.g. you may want to create a log file in SQR about the rows processed to check the validity of the Program. You may not want this log file to be generated in normal processing. When you want to debug the data you need to be executed.
In such cases SQR provides‘#DEBUG’ commands to perform the action. The Syntax for ‘#DEBUG’ command is:
#DEBUG[x...] SQR_Command

Where ‘x’ represents any letter or digit.

You can write different debug sets in a single SQR program. For E.g. you want only certain part of code to be executed in certain situation. You want some other set of codes need to be executed in other conditions. In such case group the related set of SQR commands with the debug command
Example
In a SQR, Consider the following set of commands. If my SQR errors out and I want to generate the list of Employee Id, Employee Records and Effective Date in log file, I will run the program again with the debug mode of ‘A’. In this case the SQR commands starting with ‘DEBUGA’ will get executed. If I want to trap all the Employee Id, Employee Records and Effective Date in Temp Log table, I will run the program in debug mode of ‘B’. In this case all the errors will be loaded in the temp file and log file will not be generated. If I want to run the SQR program to generate log file and load into Temp table, I can run the program in both mode at same time. #DEBUGA show ‘Emplid :’ $Emplid
#DEBUGA show ‘Empl Rcd :’ $Empl_Rcd
#DEBUGA show ‘Effdt :’ $Effdt



#DEBUGB Begin-Sql

#DEBUGB Insert into PS_Temp_log values (‘Error’,$Emplid,$Empl_Rcd,$Effdt);

#DEBUGB End-Sql



How to run the program I Debug Mode?

In the Processes navigation (‘Home > PeopleTools > Process Scheduler > Processes’) for the specified process, under ‘Override Options’ tab set the Parameter list value as ‘-DEBUG[x]’ with ‘Append’ mode.