ExcelToCI Error occurred in routine sendSOAPRequest_SubmitToDB: The operation timed out.

When trying to upload about 7000 rows using ExcelToCI for a custom component, users are getting the following error.

Error occurred in routine sendSOAPRequest_SubmitToDB:
Error:
Number: -2147012894
Description: The operation timed out

After doing my investigation, checking the web server and appserver access log I found the following issues, and here is what i did to resolve the issue. This is not documented anywhere. Please create a resolution if possible to help others.

1. Found that ExcelToCI uses SERVERXMLHTTP MSXML 6.0 object to send the SOAP request to the webserver,
   Set xHTTP = CreateObject("MSXML2.SERVERXMLHTTP.6.0")
   This request has a default 30 second timeout for receiving a packet of response data from the target server,
   See link : http://msdn.microsoft.com/en-us/library/ms760403 for more details on timeouts.

2. After checking the webserver access log (PIA_Access.log), determined that some of the POST request are taking about 59 seconds to complete and therefore we were receiving the error.

To resolve this issue, change the VB Macro code in ExcelToCI spreadsheet as follows

=======================================================

Add this line before xHTTP.send xDoc.xml in StagingAndSubmission Module under function sendSOAPRequest_SubmitToDB
Dim lResolve, lConnect, lSend, lReceive As Long
lResolve = 60 * CLng(1000)
lConnect = 90 * CLng(1000)
lSend = 90 * CLng(1000)
lReceive = 120 * CLng(1000)

xHTTP.setTimeouts lResolve, lConnect, lSend, lReceive
=======================================================

After changing this value, the ExcelToCI was still failing. Once again I checked the web Server and App server log and found that after loading about 50 to 60 rows, request is taking more than 5 minutes to respond and appserver is killing the appserv thread and therefore error was received saying that The Operation time out.

I had two options
1. Increase the XMLHTTP timeout and also increase the appserver timeout or find out why a request is taking more than 5 minutes to complete. Used Precise i3 performance monitoring tool and also the live Oracle
session and determined the following issue.
1. CI was firing sql to fetch location code description using a view that was not correctly joined with other large table using location code.
2. A Save Edit Peoplecode was written at Level 2 to fetch some data from the Oracle table using SQL Exec. This was causing this sql to fire 160,000 times as it was firing for every row in the scroll as new rows are being added.

Following improved the performance.
1.  Remove the related display field from the page. Alternative option is to tune the sql for related display field.
2.  Moved the save edit code to field edit, so that it fires only for the newly inserted rows. Other option is to write conditional logic to see if row is changed.

After these, 2 changes, the process finished inserting 7100 rows in 23 minutes. Only two request exceeded  default timeout. One took 32 and other 35 seconds to respond.

I have also noticed, that if Chunking factor is reduced to 1 to have smaller number of rows processed, PSAPPSRV is restarting due to recycle count of 5000 being reached, and this results in a login Error and HTML (invalid XML) data is being sent to the Excel. Is this the expected behavior?  Only solution to this issue is to increase the recycle count temporarily and change it back to original. Fortunately this parameter is dynamic and does not require restart of the appserver.

Changing the Userid when triggering the subscription peoplecode.

You can use the following IntBroker Class method : SwitchAsyncEventUserContext to change the context of the peoplecode run inside subscription peoplecode. This is to be used by IB only (there are checks to make sure that is the case) and can only be used for IB events that are fired asynchronously (OnRoute, OnSend, OnNotification, etc..). One use case is if you are submitting a process request from a self service user id and you do not want to give access to query security to each user who is triggering the message. This method is added in PT 8.50 and not available for lower tools release.

For more info use : E-IB: User Security required on target db for async messages in 8.48.0x [ID 654592.1]

PeopleBook definition of this method is as follows.

SwitchAsyncEventUserContext

Syntax
SwitchAsyncEventUserContext(UserID, LanguageCode)
Description
Use the SwitchAsyncEventUserContext method to switch the user context within an Integration Broker asynchronous event.
Parameters
UserID
Specify the user ID, as a string, to which you want to switch the context.
LanguageCode
Specify the language code, as a string, for the user ID.
Returns
A Boolean value: true if the switch user was successful, false otherwise.
Example
&returnValue = %IntBroker.SwitchAsyncEventUserContext("VP1", "ENG");

Which Run Status values are considered "Active" by the Process Scheduler

Question:  Which Run Status values are considered "Active" by the Process Scheduler ?
Answer: The Process Scheduler considers: Initiated, Blocked, and Processing as active slots.

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')