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.

E-PRCS: PeopleTools 8.50 After DST change processes run one hour ahead/behind scheduled time

After the recent DST time change in USA on November 7th 2010, Scheduled processes are running one hour behind in our case. Our base time zone is EST. This happens due to a bug in the current Tools version less than 8.50.13.  This issue is fixed in 8.50.13 and 8.51.

Workaround

To resolve this issue, restart all your batch server domains in DEV/QA/PROD. Both NT and unix server domains are affected by this issue.

 

For more detail see  : E-PRCS: 8.50 After DST change processes run one hour ahead/behind scheduled time (Doc ID 1265111.1)

References NOTE:1163113.1 - E-PRCS Processes Are in "Queued" Status when Process Scheduler Resides in a Different Time Zone

Root Cause

This happens due to inability of process scheduler to detect change in sessiontimezone after the DST change and it’s use of the following SQL in 8.50

select ..... from PSPRCSQUE........where RUNDTTM <= SYSTIMESTAMP

By understanding the Oracle DBMS concept of SYSTIMESTAMP, the session time zone also takes effect when a TIMESTAMP value is converted to the TIMESTAMP WITH TIME ZONE or TIMESTAMP WITH LOCAL TIME ZONE datatype.

Therefore, for this conversion to take place : RUNDTTM <= SYSTIMESTAMP, the user's session timezone is returned and is not the same timezone as the database server.

Note: This only impacts those customers using Oracle and PT 8.50.12 or less. It does not impact non Oracle or lower tools release customers.

E-QR: Wrong Output Is Generated When Running PSQuery Using Email Type

If you have  files in the following folder $PS_CFG_HOME/appserv/prcs/<dbname>/files with extension xls, cxv , pdf and you try to schedule a query using Schedule Query (PSQUERY), and select email option, you may get incorrect file emailed to you.

 

Workaround: Delete the files in $PS_CFG_HOME/appserv/prcs/<dbname>/files folder that has extension xls, csv or pdf.

This is a bug introduced in 8.50.08 and as of 8.50.12 not yet resolved.

See My Oracle Support resolution ID: 1186374.1

PS resolution says this happens when you have PS_FILEDIR is setup in the Process scheduler box, but this was not the case in our environment.

Error getting report repository location. (63,85)

If you are getting this message when running a new Report, and all the configuration settings are correct then the issue is due to incorrect initialization of psreports or SchedulerTransfer servlet. This issue was resolved in 8.48.16 but appeared again in 8.50.10. To fix this simply do the following.

 

From the IE browser, just paste the following URL.

If using NON SSL

http://xyz.com:port/psreports/ps/

if using SSL

https://xyz.com:port/psreports/ps/

Replace xyz.com with the actual Report repository web server name and port with actual port number used. Also if you use non default site, then replace ps with the actual pia site name.

See the Metalink resolution :

E-RD: Reports do not post initially when the Web Server is bounced [Video] [ID 660253.1]  

Other Option is to open a previously successful report that invokes the psreports servlet.

This could be specific to WebLogic version and Patch applied.

WebLogic 10.3.2 32 Bit on Red Hat Linux 5.3 64 bit

JrockIT JRE

java version "1.6.0_17"

Java(TM) SE Runtime Environment (build 1.6.0_17-b04)

BEA JRockit(R) (build R27.6.6-28_o-125824-1.6.0_17-20091214-2104-linux-ia32, compiled mode)

PeopleTools : 8.50.10

All the patches applied using WebLogic Smart Update Utility: bsu

PeopleTools 8.50 Known Issues - Master Note [ID 1114793.1]

Oracle has posted a Master Resolution on Metalink (Flash based: http://support.oracle.com) or Non Flash based (http://supporthtml.oracle.com).

Resolution ID: 1114793.1

It has broken the incidents into following categories.

1. Application Development
2. Install/Updates
3. Integration Tools
4. Reporting Tools
5. Server Tools
6. Open Incidents (ALL components)

 

This document will be updated frequently. Please check this document to see if your issue is already listed in it.

List of Batch Processes in queued status

The following sql will generate a list of batch processes in queued status in Process Monitor.

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,
       lastupddttm,
       servernamerqst
FROM   psprcsrqst b
WHERE  runstatus IN ( 5 )
ORDER  BY rundttm DESC 

Invalid signon time for user PS@xyx.com

when user tries to signon they get the following message on the Login Page.

Invalid signon time for user

 

This message is thrown due to a  blank classid row in PSROLECLASS table. Use the following SQL to determine the Cause.
select * from psroleclass where  classid = ' '

To fix this, run this SQL.
delete from psroleclass where  classid = ' '

No Appserver or Webserver bounce was required.

This was tested in 8.50.10

PeopleTools 8.4x/8.1 ERD Diagram for Security tables

On Metalink Go to Knowledgebase article : 611947.1 : PeopleTools 8.x Security Relationship Diagrams

The above document provides the ERD diagram for peopletools security table.

Run the PeopleTools 8.50 Installation in GUI mode for Linux/Unix servers

In PeopleTools 8.50, PeopleSoft does not document the command line parameter needed to run the installation in GUI mode for Linux/Unix servers. The installation defaults to console mode. To get the same GUI experience, you can use the following command line flag.

-i swing

So of you have downloaded the PeopleTools installation media to PS_INSTALL, you can run the following.

 

PS_INSTALL/Disk1/setup.sh –tempdir $HOME/tmp –i swing

(Note tempdir is a documented command line parameter to use the folder other than /tmp. It requires at least 2GB free space in temp folder).

 

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

Usage: setup [-f <path_to_installer_properties_file> | -options]
            (to execute the installer)

where options include:
    -?
            show this help text
    -i [swing | console | silent]
            specify the user interface mode for the installer
    -D<name>=<value>
            specify installer properties

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

 

I will be curious, if there is a way to do silent install by providing all the needed input in a properties file. I do not see this documented any where.

Note: This method applies to other installation type for e.g. Verity Install.