Application Run Controls versus Process Run Controls

Please see this on Oracle support Site : (http://support.oracle.com or http://supporthtml.oracle.com for non flash based browsers.

E-PRCS: Master Note: Process Scheduler [ID 1266607.1]

Application Run Controls versus Process Run Controls 
There are two types of "Run Control" stored in different database tables.

Process Run Control (created by the system)
Application Run Control ( created by the Application)

Process Run Control
Process Run Controls are created automatically by the system when a user creates a process request. The process run controls are stored in multiple tables. User ID and Run Control ID are keys to each tables:
PS_PRCSRUNCNTL - Stores user's language code and language option
PSPRCSRUNCNTLS - Stores server name and time zone
PS_PRCSRUNCNTLEOPT - Stores parameters for process request runs to email such as email address, subject, body text…etc.
PS_PRCSRUNCNTLDTL - Stores run control details such as output type, output format, output destination, folder name, email output option for per process type and process name
PS_PRCSRUNCNTLDIST - Stores distribution list

Application Run Control
Application Run Controls are created by user when he/she submits a process to run. It holds parameters required and specific to the process/program. When the Application Run Control is created, it triggers the system to create the Process Run Controls as well using the same Run Control ID as the key.

From the end-user perspective, there's no distinction between Process and Application Run Controls. The Process Run Control is assigned the exact same name/id as the Application Run Control.
However, it's important for application developers to distinguish between the two when creating new application process/program.
Process run control and application run control are stored in different tables.
PeopleTools delivers the table to store only the process run control.
Application developer is responsible for creating the application run control table to store the application run control information.
If a user creates a new application run control to run a report, then the corresponding process run control will be given the same name.

Run Control information is stored in two PeopleTools tables (at least):
PSPRCSRUNCNTLS (stores information from the Process Scheduler request panel) and
PS_PRCSRUNCNTL (stores the language code),
and at least one Application Run Control Table.

Note: From 8.4x PSPRCSRUNCNTLS is introduced which is used in place of PSPRCSRUNCNTL.

Component Interface Does Not Validate Record Edits (Against Prompt table) When Using Create

If you are creating Component interface by default it will not enforce the prompt values specified on Add search record and will let you input any values even though they are not valid. This is an issue when inserting new rows using ExcelToCI as it allows the user to enter invalid values without giving any error message.

To avoid this issue, open up the Add mode Search record for the component and go to Record field properties for the search fields and check Search Edit check box.  This will enforce the use of valid values when adding a new row using Component interface used in ExcelToCI. By default Search Edit is not checked. 

Here is the description of this field in PeopleBooks.

Search Edit    Enabled only if Search Key is selected. Selecting this option enforces the required property and table edits on the search page. It also enforces these edits under circumstances where the search page would normally be bypassed. With this option, the user no longer has the ability to perform partial searches on this field.

See the Resolution

E-CI: Component Interface Does Not Validate Record Edits (Against Prompt table) When Using Create [ID 664377.1]

If you want the partial search for this field to be enabled, then do the following.

Add the Search field as a read/write property in CI.

SQLExec : Return: 8015 - Bind value is too long

You get this error in an online page or while running a Application engine program. This error happens when you try to insert more than 254 characters in a long field using sqlexec and do not use %TextIn meta sql.

Resolution

Use %TextIn meta-sql for the bind variable that is used for inserting into a long field. For e.g. %TextIn(:1)

%TextIn is documented in peoplebooks and is mandatory for all insertions/update of LongChar fields using sqlexec for all database platforms.

Here are some resolutions that discusses this issue in Metalink – Oracle support site.

E-AE Application Engine PeopleCode Step with SQLExec Receives Error; return code 8015 "Bind value is too long" [ID 889806.1]

E-PC:"Bind value is too long" Error When Using SQLExec to Insert into Long Char Field [ID 620874.1]

Portal Content Reference Navigation Path

Very often you need to find out what is the Navigation Path for a given component name in PeopleSoft portal or HRMS or Financials or CRM?

If you are using 8.50 and 9.1 then you can find this information using Enterprise Components –> Find Object Navigation. You can search by component name, page name, Secondary Page name or Content Reference Name. Please note that you must be using application version 9.1 or higher. Just upgrading peopletools to 8.50 or higher will not enable this functionality.

 

Another alternative way is to use SQL. However this requires creating a PL/SQL function and is only applicable for ORACLE database only. Other database platforms may need to write their own functions to implement this functionality.

PL/SQL source code for the Function:

CREATE OR replace FUNCTION fx_get_portal_map (l_portal_name    VARCHAR2,
                                              l_portal_reftype VARCHAR2,
                                              l_portal_objname VARCHAR2,
                                              l_level          NUMBER,
                                              l_type           VARCHAR2,
l_count_max      INTEGER DEFAULT 10)
RETURN VARCHAR2
IS
  pl_count              INTEGER := 0;
  pl_portal_objname     psprsmdefn.portal_objname%TYPE := l_portal_objname;
  pl_portal_seq_num     psprsmdefn.portal_seq_num%TYPE := 0;
  pl_portal_label       psprsmdefn.portal_label%TYPE := ' ';
  pl_portal_prntobjname psprsmdefn.portal_prntobjname%TYPE := ' ';
  CURSOR cur_1 IS
    SELECT portal_prntobjname,
           portal_label,
           portal_seq_num
    FROM   psprsmdefn
    WHERE  portal_name = l_portal_name
           AND portal_reftype = l_portal_reftype
           AND portal_objname = pl_portal_objname;
BEGIN
  WHILE pl_count <> l_level LOOP
      pl_count := pl_count + 1;

      EXIT WHEN pl_count > l_count_max;

      OPEN cur_1;

      FETCH cur_1 INTO pl_portal_prntobjname, pl_portal_label, pl_portal_seq_num
      ;

      IF cur_1%found THEN
        pl_portal_objname := pl_portal_prntobjname;
      ELSE
        pl_portal_label := ' ';

        pl_portal_seq_num := 0;

        EXIT WHEN cur_1%notfound;
      END IF;

      CLOSE cur_1;
  END LOOP;

  IF l_type = 'S' THEN
    RETURN pl_portal_seq_num;
  ELSE
    RETURN pl_portal_label;
  END IF;
END; -- Function FX_GET_PORTAL_MAP

SQL Query to Get the Navigation. Note you can uncomment the portal_uri_seg2 to query for a specific component.

SELECT a.portal_objname,
       a.portal_linkobjname,
       a.portal_seq_num                              seq,
       Ltrim(Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L')
             ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L')
       ||
       Decode(
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L'), ' ', '',
                                                                  ' > ')
       || Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'L')
       || Decode(a.portal_label, ' ', '',
                                 ' > '
                                 || a.portal_label)) navigation,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,1,'L') LABEL1,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,2,'L') LABEL2,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,3,'L') LABEL3,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,4,'L') LABEL4,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,5,'L') LABEL5,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,6,'L') LABEL6,
       --FX_GET_PORTAL_MAP(A.PORTAL_NAME,'F',A.PORTAL_PRNTOBJNAME,7,'L') LABEL7,
       a.portal_uri_seg1                             menuname,
       a.portal_uri_seg2                             component,
       a.portal_uri_seg3                             market,
       a.portal_urltext
FROM   psprsmdefn a
WHERE  a.portal_name = 'EMPLOYEE'
       AND a.portal_reftype = 'C'
       AND a.portal_prntobjname <> ' '
--AND A.PORTAL_URI_SEG2 = 'COMPONENT_NAME'
ORDER  BY Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'S')
          ||
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 1, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 2, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 3, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 4, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 5, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 6, 'L'),
Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'S')
|| Fx_get_portal_map(a.portal_name, 'F', a.portal_prntobjname, 7, 'L') 

Application Engine program remains in processing status forever.

Process Scheduler Server Agent PSUNX is below the Log Space Threshold
The Log/Output Directory /xyz/psft/pt/8.50/appserv/prcs/xyz/log_output for the Process Scheduler Server Agent PSUNX
in server xyz.com for database xyz has 8 MB of free space.
This is below the disk threshold value of 10 MB in the Log Space Threshold
found in the Process Scheduler Configuration file. The system is suspending
the server agent until more disk becomes available.  Until then, no queued
process requests will be processed in this Process Scheduler Server Agent.

 

This happened because, one of the user modified the Application engine Process definition, override options as follows and ran the process using process scheduler.

-debug Y –trace 3

 

This resulted in a trace file that keeps growing very fast and consumes the entire available disk space on the appserver.

Removing the above parameters (-debug Y) and deleting the huge log file resolved the issue.

Following sql can be used to determine if there are any app engines that have this defined.

SELECT lastupdoprid,
       lastupddttm,
       prcsname,
       parmlist,
       descrlong
FROM   ps_prcsdefn
WHERE  parmlist <> ' '
       AND prcstype = 'Application Engine'
       AND prcsname <> 'PSCONQRS'; 

 

-debug Y should only be used from psae command line for interactive debugging. It should not be used in Process definition.

Scheduled Crystal Process remains in initiated status when the account is locked

If you have implemented password controls that locks the users account after x no of invalid attempts and the user has a crystal report scheduled, it makes crystal report process to remain in initiated status forever. It also generates an ever increasing log file, which has a potential of consuming entire available disk space and disrupting the other batch processes.

Following Trace files are generated.
CRW_XRFWIN_12345.log
pssqltrace[1].trc

It appears that Crystal Report  repeatedly calls the database sql statements and never comes out of it.

Steps to reproduce the issue.
1. Create a testid testps and assign roles PeopleTools and PeopleSoft User.
2. Schedule XRFWIN Crystal Report to run within next 5 minutes.
3. Update the testps user profile and lock the account.
4. Go back to process monitor and observe that process remains in initiated status.
Also if you go back to server and check the log_output folder for xrfwin you will see that log file and trace file size keeps on increasing.

Only workaround is to Run a SQL to detect this situation and cancel the process. Unlocking the user account also fixes the issue. The fix is targeted in next Tools release 8.51. We observed this behavior in only PT 8.50 and Crystal Report 2008 SP1. We are currently using patch 8.50.10.

SQL to determine the Processes in initiated status.

SELECT 'Processes in Initiated Status ',
       prcsinstance,
       oprid,
       prcsname,
       servernamerqst,
       servernamerun,
       rundttm,
       lastupddttm
FROM   psprcsrqst
WHERE  runstatus = 6
       AND ( SYSDATE - Cast(lastupddttm AS DATE) ) * 24 * 60 > 15 

SQL to determine Scheduled Processes and user account is locked.

SELECT b.prcsinstance,
       b.prcstype,
       b.prcsname,
       (SELECT e.descr
        FROM   ps_prcsdefn e
        WHERE  e.prcstype = b.prcstype
               AND e.prcsname = b.prcsname)     descr,
       b.oprid,
       (SELECT a.oprdefndesc
        FROM   psoprdefn a
        WHERE  a.oprid = b.oprid)               NAME,
       runcntlid,
       (SELECT f.qryname
               || ', '
               || f.descr
        FROM   ps_query_run_cntrl f
        WHERE  f.oprid = b.oprid
               AND f.run_cntl_id = b.runcntlid) qryname,
       recurname,
       runstatus,
       (SELECT xlatshortname
        FROM   psxlatitem c
        WHERE  c.fieldname = 'RUNSTATUS'
               AND c.fieldvalue = b.runstatus)  rundescr,
       diststatus,
       (SELECT d.xlatshortname
        FROM   psxlatitem d
        WHERE  d.fieldname = 'DISTSTATUS'
               AND d.fieldvalue = b.diststatus) distdescr,
       pt_retentiondays,
       rundttm,
       rqstdttm,
       b.lastupddttm,
       servernamerqst
FROM   psprcsrqst b,
       psoprdefn z
WHERE  b.runstatus IN ( 5 )
       AND b.oprid = z.oprid
       AND z.acctlock = 1
ORDER  BY rundttm DESC 

You are not authorized to run process type XRFWIN and process name Crystal. (65,8)

This happens if the user’s Primary Permission List or Process Profile Permission list is blank and user is trying to submit a Process request using Process Scheduler.

To fix this, make sure that user profile has valid primary permission list and Process profile permission list defined.

You can use the following sql to find out which users does not have primary permission list or process profile permission list.

Primary Permission List SQL

select * from psoprdefn where oprclass = ' ';

Process Profile Permission List SQL
select * from psoprdefn where prcsprflcls = ' ';

You get the following Message if Primary permission list is blank.

---------------------------

Windows Internet Explorer

---------------------------

You are not authorized to run process type XRFWIN and process name Crystal. (65,8) PRCSRQSTDLG_WRK.LOADPRCSRQSTDLGPB.FieldFormula Name:LaunchAndRunProcessRequest PCPC:97211 Statement:1113

Called from:PRCSRQSTDLG_WRK.LOADPRCSRQSTDLGPB.FieldFormula Name:LaunchProcessRequestDlg Statement:1133

Called from:PRCSRQSTDLG_WRK.LOADPRCSRQSTDLGPB.FieldChange Statement:1

You must be specifically authorized to run this process. Authorization is granted using the Process Definition table and Maintain Security

---------------------------

You get the following message, if the Process Profile Permission list is blank.

Windows Internet Explorer

---------------------------

Process Profile defined for user ID %2 is invalid (65,111)

The process profile assigned to the user ID is either not valid or blank. Update the User Profile for the user ID in the Maintain Security component with a valid process profile.

---------------------------

You also get this message in addition to above.