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.

Dynamic section calling in App engines

The execution of a PeopleSoft Application Engine starts with the Main section and flows down to other sections which are called from the main section.

For calling a section from within one, we use the call section action. To do this, in the call-section action, we specify the name of the App engine and the section we wish to call. If the section is in the same App engine, then, providing the name of the App engine is optional.

How to use Dynamic Call-Section in App engine?

Often, business logic requires us to call different sections based on occurrence of certain conditions in different scenarios and that too from the same call-section. To enable this kind of logic, we will have to make the call-section dynamic. This is how we can do it.

Dynamic Call-Section State Record

To enable dynamic call-section, we need to have a state record that can support it. The state record, in this case, should have two extra fields – AE_APPLID and AE_SECTION. If you intent to make a dynamic call to sections that are in the same App engine as the calling section, then the state record would be good to go with just AE_SECTION.

PeopleCode for Dynamic Call-Section

Once you have the state record in place, it’s time to set the values for the extra fields – AE_APPLID and AE_SECTION. This should be done before the dynamic call section. The simplest way to implement this is to use a if-else as shown below:
if condition then
AE_APPLID = "AE_ABC_TEST";
AE_SECTION = "SEC_STATE";
else
AE_APPLID = "AE_ABC_TEST";
AE_SECTION = "SEC_CITY";
end-if;

Call-Section Action

This is the final step. Insert a Call-section action into the App engine. Check the Dynamic check-box that states that the call-section is dynamic. On finding the dynamic check-box checked, the processor looks for the values of AE_APPLID and AE_SECTION in the state record and calls the section mentioned in AE_SECTION from the app engine that is mentioned in the AE_APPLID.

If the AE_APPLID is blank, the processor calls the section mentioned in AE_SECTION from the current application engine.

Multiple Reports in SQR

Generating multiple reports in SQR is common these days. Writing SQRs that produce multiple reports have many advantages over the other approach of having multiple SQRs do this job. Here are some of them.

Advantages

Multiple reports in one SQR approach reduces database trips thereby making the reports faster. This is especially true when all the reports are based on the same set of data. However, be cautious not to get totally different SQR reports into one – this can complicate things.
Easily possible to direct multiple reports to multiple printers. Since we are free to have different layouts and printers for different reports, we can easily direct some reports to one printer while some other reports to a different printer.
This results in fewer SQRs which in turn reduces maintenance costs. Well, this one needs no further explanation.
Having seen the advantages, you would be curious to see how we can generate multiple reports in SQRs. This can essentially be achieved in a three step approach.

Declare-Report
When generating multiple reports, SQR mandates us to declare all the reports that we wish to generate. This is done within the Setup section of the SQR. We can use different printers / layouts for different reports. If you are happy with the default printer and layout, just ignore these in your report definition.

In the sample code below, we have declared two reports, both of which use the default layout and printer for simplicity sake.

Begin-Setup

declare-report TEST1
end-declare

declare-report TEST2
end-declare

End-Setup

For-Reports
Standard SQRs that generate just one report would have only one Heading / Footing. However, we need to have a mechanism to print different headings / footings on different reports. This can be achieved using the For-Reports parameter. This is how we use it.

Begin-Heading 1 for-reports=(TEST1)
print ’Test Report One’ (1) center
End-Heading

Begin-Footing 1 for-reports=(TEST1)
page-number (1,1) ’Page ’
last-page () ’ of ’
End-Footing

Begin-Heading 1 for-reports=(TEST2)
print ’Test Report Two’ (1) center
End-Heading

Begin-Footing 1 for-reports=(TEST2)
page-number (1,1) ’Page ’
last-page () ’ of ’
End-Footing

Use-Report
We have reached the final stage – printing the actual report. Before we can print anything, we would need to inform the processor about the report to which we are printing. We use the Use-Report command to set the printing context. This is how we do it.

Begin-Program

use-report TEST1
print 'This text goes into report TEST1' (,1)

use-report TEST2
print 'This text goes into report TEST2' (,1)

End-Program