Peoplesoft - howto find the navigation to components using SQL

This record is used in setup manager - PS_PTLT_COMP_NAV  -  which defines Components Navigation. The field PTLT_TASK_CODE reflects component name, but there's ".GBL", or other Global extension like ".AUS". It doesn’t work for the transaction components like "PERSONAL_DATA".

SELECT * from PS_PTLT_COMP_NAV;

For the transaction components, a platform-independent SQL snippet is found here: 

/* Provide navigation location for component */
SELECT a.portal_name,
       e.portal_label AS parent4_folder,
       d.portal_label AS parent3_folder,
       c.portal_label AS parent2_folder,
       b.portal_label AS parent_folder,
       a.portal_label AS component
FROM psprsmdefn a
LEFT JOIN psprsmdefn b ON b.portal_name = a.portal_name
AND b.portal_objname = a.portal_prntobjname
LEFT JOIN psprsmdefn c ON c.portal_name = b.portal_name
AND c.portal_objname = b.portal_prntobjname
LEFT JOIN psprsmdefn d ON d.portal_name = c.portal_name
AND d.portal_objname = c.portal_prntobjname
LEFT JOIN psprsmdefn e ON e.portal_name = d.portal_name
AND e.portal_objname = d.portal_prntobjname
WHERE a.portal_reftype = 'C'
  AND a.portal_uri_seg2 = 'ADM_APPL_MAINTNCE' -- Parameter to set:  (In this case I used the component - ADM_APPL_MAINTNCE)

Without too much effort, here I found another (my favorite) SQL:

SELECT DISTINCT rtrim(reverse (sys_connect_by_path(reverse (portal_label), ' > ')), ' > ') path
FROM psprsmdefn
WHERE portal_name = 'EMPLOYEE'
  AND portal_prntobjname = 'PORTAL_ROOT_OBJECT' START WITH portal_uri_seg2 = :1
 CONNECT BY
  PRIOR portal_prntobjname = portal_objname

PeopleSoft GP rule package comparing result by SQL

There are more than 20 times that my staged result got cleared from front end by some other people (I’m not telling who they are).

One good part is, if the data are not flushed in database, we can still see the result.

SELECT *
FROM PS_GP_PKG_ELEMENTS ELE, PS_GP_PINTYPE_VW PINTYPE, PSXLATITEM XLAT
  WHERE ELE.GP_PKG_ID        = '<PACKAGE_NAME>'
    AND XLAT.fieldname         = 'GP_PKG_FAIL_REASON'
    AND ELE.PIN_TYPE = PINTYPE.PIN_TYPE
    AND ELE.GP_PKG_FAIL_REASON = XLAT.FIELDVALUE;

--
select count(*)
from PS_GP_PKG_ELEMENTS ELE
  where ELE.GP_PKG_ID = '<PACKAGE_NAME>';

How to get trace enabled for COBOL Programs executed from PeopleSoft in 2 tier and/or 3 tier?

[source: hmmm… my apologies to the original writer, I really can’t recall where I took this note from… apologies again.]

 

PeopleSoft trace supports only objects only within PeopleTools, so we cannot trace COBOL SQLs. Is this true?
NO.
It is possible to generate COBOL SQL(cobsql.) Trace along with bind values. And it is possible to generate cobsql.trc files in both TWO TIER and THREE tier modes.

To generate cobsql.trc file for COBOL programs in THREE TIER(Application Server) do the following:
1)  Shutdown the application server
2)  Open the psappsrv.cfg
3)  Set the RCCBL Redirect to 1
4)  Uncomment the line Log Directory (in the domain settings section) <== This is a necessary step (If it is on unix, make sure the "\" is changed to "/")
5)  Set the TraceSQL to 255.
6)  Save psappsrv.cfg
7)  Shut down and reboot the application server (it will take some time for the app. server to boot, as you have turned on tracing)
8)  Click on any remotecall cobol and then check both the temp and <pshome>/appserv/logs folders for the following:
  a) you will have <COBOL PROGRAM>_<OPRID>.out /.err files appearing in the <pshome>/appserv/logs folder.
  b) you will also get sql trace of the panels involving sql
  c) you will also get cobsql_<datetime>.trc  (THIS IS THE TRACE FILE YOU WANT TO SEE)

[Process Scheduler]
Log Directory=%PS_SERVDIR%/logs
To generate cobsql.trc file for COBOL programs in TWO TIER do the following:
1) Open configuration manager -- Trace Tab.  
2) Check all or selected boxes on the left-hand side (except for the Sybase Information)  on the Trace Tab  
3) Check the Re-direct output box on the Process Scheduler tab of Configuration Manager also.  
The cobsql trace file will reside under %TEMP%\PS\DBName in the format cobsql_program-name_datetime.trc.

PeopleSoft Time and Labor Tips: tracing TRC and migrating rules

HOWTO: trace TL_TIMEADMIN

By following the Application Engine program convention, either we can override or append a Parameter List, say “-TRACE 135 -TOOLSTRACESQL 31”:

Main Menu > PeopleTools  > Process Scheduler > Processes

Process Name: TL_TIMEADMIN

When debugging PeopleSoft T&L rules, the SQL below can be used to trace a specified TRC (take “SH125” as my example):

select * from PS_TL_RULE_PGM_DTL A, PS_TL_RULE_STEPS B, PS_TL_SQL_EXPRESSN C where A.RULE_PGM_ID='AGEP' and A.TL_RULE_ID=B.TL_RULE_ID and B.SQL_ID = C.SQL_ID
and C.EXPRESSIONTEXT like '%SH125%' /*<<<<< the TRC focused*/
order by A.PRIORITY, B.TL_RULE_STEP;

Time and Labor rules can be copied across from one environment to the other by running DMS scripts. Oracle support site has a complete DMS script listed for the same purpose. For quick reference I just list my script here. Do remember to put on 'WHERE' clauses for each statement, if necessary.

EXPORT TL_RULE_DEFN  [WHERE…]
EXPORT TL_RULE_STEPS
EXPORT TL_RULE_CONDS
EXPORT TL_TMPLT_RULE
EXPORT TL_TMPLT_RULE2
EXPORT TL_SQL_BINDS
EXPORT TL_SQL_INSERT
EXPORT TL_SQL_JOIN
EXPORT TL_SQL_JOINFLDS
EXPORT TL_SQL_OBJECT
EXPORT TL_SQL_SEL_FLDS
EXPORT TL_SQL_TABLES
EXPORT TL_SQL_UPDATE
EXPORT TL_SQL_UPDBINDS
EXPORT TL_SQL_WHERE
EXPORT TL_SQL_WHRBINDS
EXPORT TL_SQL_EXPRESSN
EXPORT TL_RULE_PGM
EXPORT TL_RULE_PGM_DTL

This can be done by non-rule package as well.

Note: In version 9.1 PeopleSoft has come up with a utility to export and import rules between environments. Using that will eliminate to either memorise or execute the DMS scripts manually and riskily.

[PeopleSoft] Get output file directory using PeopleCode

In PT8.2x, simply add (on the bottom of this blog I’ll discuss about the file path separator) :

&FileStr = GetEnv("PSPRCSLOGDIR")|"\yourFile.txt";

&POSI_FILE = GetFile(&FileStr, "W", %FilePath_Absolute);

 

Use %FilePath_Absolute (instead of %FilePath_Relative) .

As per Oracle:

With Application Engine only .log, .trc, .AET, .out can be used. No other extensions work other than these 4 extensions, none of the files will make it to the Report Repository (.err is another story, though). Please note that there must not be multiple “.” in file names.

As some developer mentioned it is possible with PT 8.4x which allows user to picked up other extensions by process types, for example, .txt - Couple things to look into:

1. Make sure the txt file is 'relative' with %FilePath_Relative' while opening the file.
2. Make sure the .txt file is setup to be carried over. Here is the navigation (PT 8.48): PeopleTools>Process Scheduler>System Setting .
   Check the third tab Process Output Format and the 5th tab Distribution File Option, make sure the .txt is set or checked.
3. Make sure the 'Type' is 'Web' and the format is TXT after you click the Run button.

At runtime by running this query it will get the current output directory where “.out” is going, and create the file here.

Struggling with finding the output directory? May the SQL below help:

select PRCSOUTPUTDIR from PSPRCSPARMS where PRCSINSTANCE = {SOMETABLE_AET}.PROCESS_INSTANCE

Below is the code example:

SQLExec("SELECT PRCSOUTPUTDIR FROM PSPRCSPARMS WHERE PRCSINSTANCE = :1", {SOMETABLE_AET}.PROCESS_INSTANCE, &path);

/* Assume that the NT server’s name is "PSNT". */

SQLExec("SELECT A.SERVERNAMERUN FROM PSPRCSRQST A WHERE A.PRCSINSTANCE = :1", {SOMETABLE_AET}.PROCESS_INSTANCE, &serverrun);

If &serverrun = "PSNT" Then

   &DirSep = "\";

Else

   &DirSep = "/";

End-If;

&outputFileName = RTrim(&path | &DirSep | {OUTPUTFILENAME} | ".txt");

&fileHandler = GetFile(&outputFileName, "W", %FilePath_Absolute);

Note that when open file in 'A' - append mode it may not generate any output file. But 'W' - write mode works in my case.

Another way to get the file path separator character is:

/* Get Env Specific directory separator */
&FilePath = GetEnv("PS_SERVDIR");
&DirSep = "/";
If Substring(&FilePath, 1, 1) <> "/" Then
   &DirSep = "\";
End-If;

If Right(&FilePath, 1) <> "/" Or
      Right(&FilePath, 1) <> "\" Then
   &FilePath = &FilePath | &DirSep;
End-If;
&FilePath = &FilePath | "files" | &DirSep;

[PeopleSoft] File layout converting spaces to blanks - UseSpaceForNull

[source: http://peoplesofthrms.blogspot.com.au/search/label/File%20Layouts ]

Ran into a peculiar problem while writing to a file using File Layouts today. Fields that had a space in the database was being converted to null while writing to the file. Did all the obvious checks like ensuring the Trim Spaces and Strip White Spaces properties of the FileLayout were unchecked.

Then ran into the File Class property - UseSpaceForNull. This property was introduced in Peopletools 8.48 and does the neat job of ensuring that spaces are printed in the output file instead of nulls when attempting to print fields that indeed had a space in database. It is a read-write property and has to be used immediately after instantiating the file layout as shown below:

<* Example starts *> 

If &FILE1.IsOpen Then

If &FILE1.SetFileLayout(FileLayout.FL_NAME) Then

/* has to be used immediately after instantiating the file layout */

&FILE1.UseSpaceForNull = True;

end-if;

end-if;

<* Example ends *> 

HowTo achieve process instance of scheduled process

[source: http://peoplesoft.ittoolbox.com/groups/technical-functional/peopletools-l/peoplecode-to-discover-process-instance-of-scheduled-process-892219]

In 8.44, when you schedule a process, you are returned a ProcessRequest object which has as a property - ProcessInstance. Here is the entry from PeopleBooks:

--- ProcessInstance Description ---
This property is a system-generated identification number. PeopleSoft Process Scheduler assigns a ProcessInstance at runtime to each process or job it successfully schedules.

This property is read-write. 

<* Example starts *> 

&RQST = CreateProcessRequest(); 

&rec = GetRecord();

/* Set all the Required Properties */ 

&RQST.RunControlID = ; 

&RQST.ProcessType = "Application Engine"; 

&RQST.ProcessName = ; 

&RQST.RunDateTime = &rec.STARTDATETIME.Value; 

/* Schedule the Process - get the instance and change the runstatus to HOLD*/ 

&RQST.Schedule(); 

If &RQST.Status = 0 then 

    /* process successfully scheduled, ***get the instance*** */ 

    &ProcInst = &RQST.ProcessInstance; 

    /* change the runstatus to HOLD */

    If All(&rec.CS_DEPEND_NAME.Value) Then 

        &RQST.RunStatus = "4";

        &RQST.UpdateRunStatus(); 

    End-If; 

Else 

    /* do error processing */
End-If; 

<* Example Ends *>