PeopleSoft integration with External Restful Webservice via SOA Suite

Nowadays the cloud solutions are covering everywhere in daily lives by thousands of providers - daily expenses, property management, online stores, project management, customer relationship management, or even smart house technologies.

The advantages of using cloud solutions are so obvious - cost efficiency, (almost) unlimited storage, easy accessibility, instant deployment, etc.. It becomes a phenomenon despite of some disadvantages such as data management delegation to third party, security (mobile phone), and some confusions (cloud services are usually mutually independent between providers).

From the other side of the door there are so many topics quarrelling between the cloud solutions and existing ERP systems and that's it. However I do remember a coffee talk with a client around how inconvenient it is to submit business travel expense reports, and Friday afternoon timesheets - set a reminder somewhere, every time, go to the office, logon to the system on intranet, and submit data before manager or accounting team calls up.

It would be a bit fancier to use iPhone/Android apps on bus or train to submit these kinds of raw data if there are not that critical as the processed data (Payroll, General Ledger, you name it) - please correct my opinion if I'm wrong.

Our ERP systems are usually powerful enough to provide such mobile solutions however probably it won't be very easy to be approved by in-house security managers. Leave the port open to external world will be a HUGE risk because the critical data are usually hosted by the same database. The idea came to my mind is, probably, to segregate these types of pre-processing data transactions from ERP mainframe, to the easy fancy cloud services (which usually have their own mobile apps delivered).

So, with the idea in my mind, recently I tried a free-to-use cloud service and probably (just because it's free) I can use it to present my idea in this post. It does have mobile apps but I just used its web page version for taking screen shots easily.

Below is the screen shot of one example Expense record. Nothing shining too much.

On the market cloud services are providing their own API. They do have different levels of security settings. Since I'm using the FREE version, probably the AUTHTOKEN method is the only thing I can get.

This API is based on RESTful web service, which is extremely popular these days but just NOT too well supported by PeopleSoft before. BTW the recent PeopleTools versions (especially PT8.54) are covering RESTful though.

Below is a response message (in JSON format, typically) from the cloud service provider  (SoapUI is a good friend). The right panel shows the data brought back by the JSON message.

The cloud service is open to the whole world. Everyone with the provided security policy will have the access to the data directly and easily, as well as our ERP system (and other hackers too). It won't be a good idea to have any core system exposed to the external, so there should be an agent who can do the job for the core system.

To bridge our ERP system to external cloud service, the agent should 1) hide our core system from external risks; 2) transfer the message between cloud services and our core; 3) transform the message if required; 4) trigger some workflow if required, etc. Here I'll leave the security topic to security experts and continue this demo.

There are many other competitive integration tools such as TIBCO, MuleSoft on the market. I used Oracle's SOA Suite to be this agent, just because it is easy for me to download, install and use.

Below is the screenshot of one SOA application built by JDeveloper 12c, which (finally) has the REST adapter packaged. Very quickly I made a BPEL process which provides 2 ports, one for our ERP system (in this example, PeopleSoft), and another one is to call the cloud service out there. In this design the process has only ONE direction that only our SOA can call the cloud service but not the other way around. The SOAPService is based on WSDL, which has been well used by PeopleSoft since ages ago.

Because the cloud service is based on HTTPS, I need to make a CA cert for SOA Suite's web server (JDeveloper 12c's integrated weblogic, in my case).

Now the communication channel between SOA Suite and external cloud service has been built successfully. Tested the Web Service and got the good response message (200 OK).

My very expensive Host machine "Tarzan" is assigned with IP address 192.168.56.1. The JDeveloper 12c integrated Weblogic is running with port 7102 listening to HTTPS and 7101 to HTTP.

In real life we will consider to follow the security standards - such as using HTTPS to build up the communication between PeopleSoft and SOA Suite webservice (the "SOAPService" mentioned above). To simplify this demonstration, I just used HTTP protocol - port 7101.

So instead of using the original URL "https://Tarzan:7102/soa-infra/services/default/RestfulWS/SOAPService?WSDL", now I'm using "http://192.168.56.1:7101/soa-infra/services/default/RestfulWS/SOAPService?wsdl" to complete my PeopleSoft side demonstration.

Yes, it's Integration Broker here.

By now a PeopleSoft service SOAPSERVICE has been successfully built.

The following work will be on PeopleSoft side - digest the WSDL message with PeopleCode, call Component Interface to save the data (to Time&Labor TRC or Travel&Expenses entry or Payroll Earning/Deduction or Absences or Journal Entry or ...), process the data and then keep the BAU running.

And this is just a not-certified idea to have cloud services work with existing ERP system.

Done.

PeopleSoft - send attachment by email

When developing BI Publisher report, I usually use the script below to send the generated XML to my email address - which is much easier to exam the generated XML file.
-------------------------------------------------------------------------------------------------------

import PT_MCF_MAIL:MCFOutboundEmail;

Local string &str_FilePath, &str_FileName;
Local boolean &bSuccess;

/* Email the output */
Local PT_MCF_MAIL:MCFOutboundEmail &email = create PT_MCF_MAIL:MCFOutboundEmail();
&email.Recipients = "recipient1@zzzzzzz.yyy.xx;recipient2@xxxx.zzz.yy";
&email.Subject = "Subject Text";

&email.Text = "Email Body Text";
&email.AddAttachment(&str_FilePath | &str_FileName, %FilePath_Absolute, &str_FileName, "Invoice", "", "");

/* temp mail overrides */
rem &email.SMTPServer = "mail.company.com";
rem  &email.SMTPPort = 25;

Local number &rcEmail = &email.Send();
If &rcEmail = 1 Then
 /* email was successful */
 &bSuccess = True;
Else
 /* email failed */
 If &email <> Null Then
&MESSAGE_SET_NBR = &email.MessageSetNumber;
&MESSAGE_NBR = &email.MessageNumber;
&INVALID_EMAILID = &email.InvalidAddresses;
 End-If;
End-If;

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 *> 

T&L Inactive Time Reporter appears twice on calendar timesheet views (HRMS8.9/9.0)

On the Daily, Weekly and Monthly Time Calendar View the Inactive Time Reporters have two duplicate rows.

When Employee is Active in Job Data, but Inactivate as Time Reporter there are two rows displayed in the Calendar View.

When Employee is Inactivated in Job Data and Inactive as Time Reporter, too there is only one row displayed in Calendar Views.


The conclusion can be: when an employee has been inactivated the reactivate in TL_EMPL_DATA multiple rows with doubled hours appear in the calendar view. Employees that have never had an inactive row in TL_EMPL_DATA only display one row with the correct hours.

This issue was fixed in METALINK's incident #1386651000 - Monthly Time Calendar displaying multiple rows for employees. The fix was delivered in HRMS 8.9 Bundle #8 on 06/12/2006 and in HRMS 9.0 Bundle #2 on 07/09/07.

How To : E-PB: How To Get Verity for PeopleTools 8.50

E-PB: How To Get Verity for PeopleTools 8.50 [ID 949230.1]


In version 8.50, Verity is not included within PeopleTools' package. PeopleSoft PeopleTools and PeopleBooks use Verity to implement the application's inner searching.

Install Verity after installing PeopleTools, and before the database is created.  Install Verity on all of the machines where the Application Server, Batch Server, and the Web Server have been setup.

Download Verity for PT 8.50  “PeopleSoft Enterprise PeopleTools 8.50 Verity” from Oracle’s e-delivery at http://edelivery.oracle.com

Or
Download it from the PeopleSoft FTP site at ftp://ftp.peoplesoft.com/outgoing/ptools/verity/ver621/

And

Please refer to the “Task 4-4: Installing the Verity Integration Kit” in PeopleTools_8.50_installation_Oracle.pdf.

Download the "PeopleTools_8.50_installation_Oracle.pdf." from the http://edelivery.oracle.com site.

When installing, use install keys from here:

http://licensecodes.oracle.com/ent_keys_by_prod.html

The install document you will be using is PeopleTools_8.50_Installation_Oracle.pdf.  It gives excellent step-by-step instructions on everything you need to do to install PeopleTools as well as the HRMS application.  It is somewhat of a generic document, meant for PeopleTools as well as many other applications you might want to install.  HRMS is only one of the applications it talks about.

Download the PDF from the Oracle EDelivery site.  When you see that it is a 700 page install document, you’ll realize what you’ve gotten yourself into.  You will have to go through most of it.

Ok, now here’s a high-level summary of suggested steps :

Install Oracle database

Install PeopleTools CD

Install Application CD (i.e. HRMS & Campus Solutions. Make sure that install HRMS & Campus Solutions into the same folder as PeopleTools.)

Create Oracle DB for PeopleSoft

Install BEA Tuxedo CD

Install BEA Weblogic CD

Configure App Server

Install and Configure PIA

Login to PIA

Filelayout - ReadRowset Example

[From Peoplebook http://download.oracle.com/docs/cd/E15645_01/pt850pbr0/eng/psbooks/tpcr/book.htm?File=tpcr/htm/tpcr19.htm%23g037ee99c9453fb39_ef90c_10c791ddc07__1429]

ReadRowset Example

The following program reads all the rowsets from a file and updates a work scroll with that data. The work scroll isn’t hidden on the page: it’s created in the Component buffer from existing records using CreateRowset. The structure of the work scroll and the file layout are identical: that is, they’re composed of two records, and the names of the records in the file layout are exactly the same as the names of the record definitions.

Local File &CHARTINPUT_F;
Local Rowset &INPUT_ROWSET, &TEMP_RS, &WORK_DATA;
Local Record &WRK_DATA;

&filename = "c:\temp\test.txt";
If FileExists(&filename, %FilePath_Absolute) Then
   &CHARTINPUT_F = GetFile(&filename, "R", "A", %FilePath_Absolute);
Else
   Exit;
End-If;

&CHARTINPUT_F.SetFileLayout(FileLayout.CHART_INFO);

/* Create rowset to be read into
NOTE that you have to start at LOWEST level of rowset */

&TEMP_RS = CreateRowset(RECORD.CHART_ITEM);
&WORK_DATA = CreateRowset(RECORD.CHART_DATA, &TEMP_RS);
&INPUT_ROWSET = CreateRowset(RECORD.CHART, &WORK_DATA);

While &INPUT_ROWSET <> Null
   &INPUT_ROWSET = &CHARTINPUT_F.ReadRowset();
   &INPUT_ROWSET.CopyTo(&WORK_DATA);
   /* do processing -- Though file may contain more than one level zero
   Component processor only allows one level zero at a time */
End-While;

Refreshing Security Join Tables

To refresh security join tables, use the Nightly SJT Refresh Process component (SCRTY_SJTDLY_RC), Refresh Trans. SJT tables component (SCRTY_SJT_RC), the Refresh SJT_CLASS_ALL component (SCRTY_OPR_RC), and the Refresh SJT_OPR_CLS component (SCRTY_OPRCLS_RC).

PeopleBook (PeopleSoft Enterprise HRMS 9.1 Application Fundamentals PeopleBook > Setting Up and Administering HRMS Security) describes when to use the refresh processes and discusses how to:

* Run the nightly refresh process.
* Run the transaction security join table refresh process.
* Run the Refresh SJT_CLASS_ALL process.
* Run the Refresh SJT_OPR_CLS process.

PS_PTLT_COMP_NAV

http://evmultimedia.ro/peoplenet/?p=334

PS_PTLT_COMP_NAV – Define Components Navigation Setup record (in PT>=8.4xx). It is a very useful table especially when you are given a certain PeopleSoft link or Component and you don’t know how to identify the navigation path. The record stores the PTLT_TASK_CODE which is the component name followed by the market name “.GBL”/Global extension, the PTLT_FEATURES_LIST which represents the general features that the component/page encloses (eg: for the component -BUS_UNIT_TBL_BI.GBL – Set Up Financials/Supply Chain > Business Unit Related > Billing > Billing Definition the enclosed features are “”BI Business Unit Billing General Promotions Management General” ), the product code lists (PTLT_PROD_CD_LIST) which stores a list of relevant module/functional abreviations for the specific component (eg:AM,BI,AR,etc – Asset Management,BI – Business Intelligence, AR – Accounts Receivable), and finally the Navigation path to the current page (NAVIGATION) (eg: for USERMAINT.GBL – Navigation field is PeopleTools > Security > User Profiles > User Profiles). If the component has no corresponding Portal Navigation path the field stores “No Portal Navigation Available”.

To determine the path to a certain component in PeopleSoft one can issue the following SQL statement:

SELECT NAVIGATION FROM PS_PTLT_COMP_NAV WHERE PTLT_TASK_CODE=’COMPONENT_NAME.GBL’

To determine the type of access,components/menus and navigation paths on which a permission list grants rights one can run the following query:

select DISTINCT N.NAVIGATION , N.PTLT_TASK_CODE COMPONENT,b.menuname MENU, a.CLASSID PERMISSION_LIST,
DECODE(a.DISPLAYONLY,1,’TRUE’,0,’FALSE’) DISPLAYONLY,
(CASE
WHEN a.AUTHORIZEDACTIONS=1 THEN ‘ADD’
WHEN a.AUTHORIZEDACTIONS=2 THEN ‘UPDATE,DISPLAY’
WHEN a.AUTHORIZEDACTIONS=3 THEN ‘ADD,UPDATE, DISPLAY’
WHEN a.AUTHORIZEDACTIONS=4 THEN ‘UPDATE/DISPLAY ONLY’
WHEN a.AUTHORIZEDACTIONS=5 THEN ‘ADD,UPDATE/DISPLAY ALL’
WHEN a.AUTHORIZEDACTIONS=6 THEN ‘UPDATE,DISPLAY,UPDATE/DISPLAY ALL’
WHEN a.AUTHORIZEDACTIONS=7 THEN ‘ADD,UPDATE,DISPLAY,UPDATE/DISPLAY ALL’
WHEN a.AUTHORIZEDACTIONS=8 THEN ‘CORRECTION’
WHEN a.AUTHORIZEDACTIONS=9 THEN ‘ADD,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=10 THEN ‘UPDATE,DISPLAY,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=11 THEN ‘ADD, UPDATE/DISPLAY ALL, CORRECTION’
WHEN a.AUTHORIZEDACTIONS=12 THEN ‘UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=13 THEN ‘ADD, UPDATE, DISPLAY,UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=14 THEN ‘UPDATE,DISPLAY,UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=15 THEN ‘ADD,UPDATE,DISPLAY,UPDATE/DISPLAY ALL,CORRECTION’
WHEN a.AUTHORIZEDACTIONS=128 THEN ‘DATA ENTRY’
ELSE ‘UNKNOWN’
END ) AUTHORIZEDACTIONS
FROM PS_PTLT_COMP_NAV N
inner join PSMENUITEM b ON N.PTLT_TASK_CODE=b.PNLGRPNAME ||’.GBL’
inner join PSAUTHITEM a ON a.menuname = b.menuname AND a.baritemname = b.itemname
where a.CLASSID=’YourPermissionList’

SendMail PeopleCode function

You can use the SendMail PeopleCode function to send emails from within PeopleCode. You can also call this function from an Application Engine.

Note: Make sure your SMTP server is configured properly or the SendMail function will fail.

--------------------------------------------------------------------------
Local string &MAIL_CC, &MAIL_TO, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TITLES, &MAIL_TEXT, &MAIL_FILES, &MAIL_FROM, &REPLYTO, &SENDER;
Local number &MAIL_FLAGS;

&MAIL_FLAGS = 0;
&MAIL_TO = "email-address-message-going-to";
&MAIL_CC = "";
&MAIL_BCC = "";
&MAIL_SUBJECT = "Test email";
&MAIL_TEXT = "Sending an email from PeopleCode.";
&MAIL_FILES = "";
&MAIL_TITLES = "";
&MAIL_FROM = "email-address-message-is-from";
&MAIL_SEP = ";";
&CONTTYPE = "";
&REPLYTO = "";
&SENDER = "";

&RET = SendMail(&MAIL_FLAGS, &MAIL_TO, &MAIL_CC, &MAIL_BCC, &MAIL_SUBJECT, &MAIL_TEXT, &MAIL_FILES, &MAIL_TITLES, &MAIL_FROM, &MAIL_SEP, &CONTTYPE, &REPLYTO, &SENDER);

If &RET <> 0 Then
MessageBox(0, "", 0, 0, "Return code from SendMail= " | &RET);
/*Do error processing here*/
End-If;
--------------------------------------------------------------------------



Your PeopleCode will have an SQLExec function to get the OPERATOR's email address.

--------------------------------------------------------------------------
SELECT emailid
FROM psoprdefn
WHERE oprid = (SELECT supervisor_id
FROM ps_employees
WHERE emplid = 'user-id-submitting-request');
--------------------------------------------------------------------------

Scheduled PSJob distribution list

When you want to detect the distribution list within a scheduled job, the two tables will be fancy to see:

select * from PS_SCHDLITEM -- All scheduled items
select * from PS_SCHDLRPTDIST -- All distribution list

PeopleSoft Batch Server

PeopleSoft Batch Server
From [http://visualsuccess.com/mediawiki/index.php/PeopleSoft_Batch_Server]


The Peoplesoft Batch Server, generally known as the PeopleSoft Process Scheduler, polls the database (PSPRCSRQST table) at regular intervals (intervals defined as Heartbeat and Sleep Intervals) performs maintenance (like determining if it is supposed to shutdown) and polls for actionable process requests (example: run Application Engine Programs, Cobol Programs, SQR’s, or other batch proccesses). The PeopleSoft Process Scheduler Server (also known as Process Scheduler Agent) can be managed using the PSADMIN command located in the PS_HOME/APPSERV directory. The Process scheduler agent is responsible for initiating most batch and report processes in PeopleTools. Process Scheduler tables can grow quickly – affecting system performance and space, it is important to purge them on a regular basis; use delivered PRCSPURG.sqr or the new PSTools 8.4+ app engine program PRCSYSPURGE. The process scheduler is configured (including trace file location) using the psprcss.cfg and psprcsssrv.* files (typically located %PS_HOME%\\appserv\prcs\. Peoplesoft 8.4+ added a master process scheduler which acts as a load balancer for other process schedulers.
Key Tables

* PSPRCSRQST Process Request table; Process Scheduler executes and updates with status information. Key field RUNSTATUS 1=cancel; 2=delete; 3=error; 4=hold; 5=queued; 6=initiated; 7=processing; 8=cancelled; 9=success; 10=notSuccess; 11=posted; 12=unableToPost; 13=resend; 14=posting; 15=content generated; 16=pending; 17=successWarning; 18=blocked; 19=restart
* PS_PRCSSEQUENCE Process Sequence Number
* PS_PRCSRUNCNTLDTL Destination Options
* PS_BATRUNCNTL Process Specific Run Control
* PS_PRCSRQSTDIST Distribution Requests
* PSPRCSPARMS Process Request Parameters
** PS_PMN_PRCSLIST Process Monitor list (five stars!)

Troubleshooting Peoplesoft Batch Server

* Poor Performance - (1) Navigate to PeopleTools/ Process Scheduler Manager/ Servers/ Server Definition; consider increasing Sleep Time and Heartbeat Time – this will increase request start time but decrease database load. (2) Consider changing process definition to restrict number of processes allowed to run concurrently; or add to Server Definition a new Process Type that limits max concurrent runs, or add a maximum processing time to the process definition. (3) Consider (on operating system) lowering or raising process scheduler priority

After being a deadlock victim, rerun VAT1001 SQR report

User just suffered from a pain:

"Transaction (Process ID 93) was deadlocked on lock resources with another process and has been chosen as the deadlock victim".

Simply re-issuing the report will cause another problem - in VAT1001 SQR Report (even Process Monitor showed "Success" and "Posted"), it just shows:

PROCESS_INSTANCE = 123456789
No rows found to round in PS_VAT_TXN_TMP_TBL

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

Oracle Metalink has an article explains how to run the VAT report, which is:
EGL: How to Set Up And Run a VAT Report ? [ID 620419.1]

However, from VAT1001.SQR, we can see more:

begin-procedure Main-Processing
    #debugp do Debug-Msg('Main-Processing')

    do Mark-Txn-Lines
    do Insert-vat-txn-tmp-tbl
    #ifdef MICROSOFT
       do Update-Statistics
    #else
       #ifdef INFORMIX
         do Update-Statistics
       #else
          #ifdef ORACLE
             do Update-Statistics
          #end-if
       #end-if
    #end-if
    do delete-frm-vat-txn-tbl
    do Fill-Line-Info
    do Get-Report-Defn-Header
    do Get-Home-Country
    do Create-VAT-Rpt-Inst1
    do Process-Report-Lines
    do Insert-vat-rpt-err1
    do Check-for-Unselected
    do Update-In-Process-Flag
    do insert-vat-txn-tbl
    do delete-vat-txn-tmp-tbl
    do Process-Scheduler-Closing

    #debugp display 'Leaving Main-Processing'
end-procedure ! Main-Processing

In the process Mark-Txn-Lines, we can see, the SQR marks the lines by some criteria, but here I just want to point out 3 conditions for the lines being reported:

• In table PS_VAT_TXN_TBL, field IN_PROCESS_FLG is set as 'N';

• No result should be found by the SQL, if found, delete the lines having PROCESS_INSTANCE = [## the failed PI##] the from table PS_VAT_RPT_INST2; or, change their PROCESS_INSTANCE to a easy number to trace back (say, -1122334455?) :

SELECT * FROM PS_VAT_TXN_TBL, PS_VAT_RPT_INST2 B, PS_VAT_RPT_INST1 C
  WHERE  PS_VAT_TXN_TBL.PROCESS_INSTANCE = [## the failed PI##]
  AND B.PROCESS_INSTANCE = C.PROCESS_INSTANCE
  AND B.VAT_RPT_ID = C.VAT_RPT_ID
  AND PS_VAT_TXN_TBL.VAT_ENTITY = B.VAT_ENTITY
  AND PS_VAT_TXN_TBL.COUNTRY_VAT_RPTG = B.COUNTRY_VAT_RPTG
  AND PS_VAT_TXN_TBL.VAT_DCMNT_ID = B.VAT_DCMNT_ID
  AND PS_VAT_TXN_TBL.LINE_NBR = B.LINE_NBR
  --AND C2.VAT_RPT_TYPE = $rqst_vat_rpt_type;

• Cross your finger to wish NO result be found by the SQL below (otherwise, you need to check what caused a previous reporting error - check PeopleBooks???):

SELECT * FROM PS_VAT_TXN_TBL, PS_VAT_RPT_ERR2 B2, PS_VAT_RPT_INST1 C2
  WHERE B2.PROCESS_INSTANCE = C2.PROCESS_INSTANCE
  AND PS_VAT_TXN_TBL.VAT_ENTITY = B2.VAT_ENTITY
  AND PS_VAT_TXN_TBL.COUNTRY_VAT_RPTG = B2.COUNTRY_VAT_RPTG
  AND PS_VAT_TXN_TBL.VAT_DCMNT_ID = B2.VAT_DCMNT_ID
  AND PS_VAT_TXN_TBL.LINE_NBR = B2.LINE_NBR
  --AND C2.VAT_RPT_TYPE = $rqst_vat_rpt_type
  AND B2.VAT_RPT_ASSOC_FLG IN ('T', 'A')   

Then re-run VAT1001.

PSAUTHITEM.AUTHORIZEDACTIONS Codes indicating the Authorized Actions

In Table PSAUTHITEM

AUTHORIZEDACTIONS Codes : the Authorized Actions
1: Add
2: Update Display
3: Add/Update/Display
4: Update/Display All
5: Add/Update/Display All
6: Update/Display/Update/Display All
7: Add/Update/Display/Update/Display All
8: Correction
9: Add/Correction
10: Update/Display / Correction
11: Add/Update/Display/Correction
12: Update/Display All/Correction
13: Add/Update/Display All/Correction
14: Update/Display/Update/Display All/Correction
15: Add/Update/Display/Update/Display All/Correction
128: Data Entry

For all the authorized actions for a given menu item for a given operator, these numbers are added together and stored in the authorizedactions column.

For example, if the value for authorizedactions column is 143, the operator is authorized for all the actions. (1+2+4+8+128) .

As another example, if the value is 7, the operator is authorized for the actions Add, Update/Display, Update/Display All (1+2+4).

Howto get the names of ROLE with a specified Permission List

It will be a very boring work if you want to search for the Roles one by one to see which role has the Permission List wanted.

SQL helps:

select ROLENAME from PSROLECLASS where CLASSID='[the_permission_list]';

Then we can grant user the role from the result.

insert into PSROLEUSER values ('[user_id]', '[role_name]', '[dynamic_sw]')

======== To overview all permission lists assigned to a USER. ========

select RU.ROLEUSER, RU.ROLENAME, RC.CLASSID from PSROLEUSER RU, PSROLECLASS RC where RU.ROLENAME=RC.ROLENAME and RU.ROLEUSER='PS'

Monitoring Integration Broker Messages

To monitor these PUB messages, we can have a look in the tables below:

• PSAPMSGPUBCON
• PSAPMSGSUBCON
• PSIBLOGHDR
• PSAPMSGPUBHDR

The SQLs I am using to detect failed messages are:

select * from PSAPMSGPUBCON where STATUSSTRING='ERROR'

or, if want to see more:

select * from PSAPMSGPUBHDR HDR, PSAPMSGPUBCON CON where HDR.IBTRANSACTIONID=CON.IBPUBTRANSACTID and CON.STATUSSTRING not in ('DONE', 'CNCLD')

The STATUSSTRING can be replaced by PUBSTATUS, with its Status Codes:
0 - Error, 1 - New , 2 - Started, 3 - Working,
4 - Done, 5 - Retry, 6 - Timeout, 7 - Edited, 8 - Canceled


I believe it is not hard at all to jump from PUB messages to SUB messages. :)

PeopleSoft Meta-Tables

[original: http://www.compshack.com/wiki/peoplesoft/peopletools/peoplesoft-peopletools-meta-tables]

This is an attempt to list PeopleSoft meta-tables along with some kind of description for every table. The list will be broken into categories (pages, records, components, and so forth). Please feel free to add to or correct the list, this is a wiki page, so jump in!

PeopleSoft Projects

PSPROJECTDEFN table stores information about projects created in Application Designer.
Try it out:

SELECT * FROM PSPROJECTDEFN
WHERE PROJECTNAME = 'Your_Project_name';

PSPROJECTITEM table stores objects inserted into your Application Designer project.
Try it out:

SELECT * FROM PSPROJECTITEM
WHERE PROJECTNAME = 'Your_Project_name';

Portal Structure

PSPRSMDEFN is a Portal Structure Definition table. A good example is to use this table to find portal path for a specific component. Take a closer look on how this is done!.
PSPRSMPERM: Shows the permission lists that are assigned to a portal registry structure (content reference). The permission list name is under field PORTAL_PERMNAME.

XLAT Tables

XLATTABLE: Stores translate values (PeopleSoft version prior to 8.4).
PSXLATDEFN: Stores all fields that have Xlat values. This table does not store any Xlat values.
PSXLATITEM: Stores fields with their actual translate values (PeopleSoft version 8.4 and above).

Record & Field Tables

PSRECDEFN: Stores informations about tables. One row for each table. Field count and record type are two fields that are stored on this table.

CASE RECTYPE
        WHEN 0 THEN 'Table'
        WHEN 1 THEN 'View'
        WHEN 2 THEN 'Derived'
        WHEN 3 THEN 'Sub Record'
        WHEN 5 THEN 'Dynamic View'
        WHEN 6 THEN 'Query View'
        WHEN 7 THEN 'Temporary Table'
        ELSE TO_CHAR(RECTYPE)
END CASE

PSRECFIELD: Stores records with all their fields (sub-records are not expanded)
PSRECFIELDALL: Stores records with all their fields (sub-records are expanded)
PSINDEXDEFN: Contains 1 row per index defined for a table.
PSKEYDEFN: Containes 1 row per key field defined for an index.
PSDBFIELD: You got it, stores information about fields.

CASE FIELDTYPE
                WHEN 0 THEN 'Character'
                WHEN 1 THEN 'Long Character'
                WHEN 2 THEN 'Number'
                WHEN 3 THEN 'Signed Number'
                WHEN 4 THEN 'Date'
                WHEN 5 THEN 'Time'
                WHEN 6 THEN 'DateTime'
                WHEN 8 THEN 'Image'
                WHEN 9 THEN 'Image Reference'
                ELSE TO_CHAR(FIELDTYPE)
        END CASE

PSDBFLDLABL: Stores field label information.

Process Definition Table(s)

PS_PRCSDEFNPNL: Stores the process definition name, process type(sqr report, application engine...), and the component name associated with the process definition.
PS_PRCSDEFN: Process definitions table. The record stores processes that can run within the Process Scheduler. Security information such as components and process groups are also stored on this table.

Message Catalog Tables

PSMSGCATDEFN: Stores information about PeopleSoft message catalogs such as message set number, message number and the actual message text.
PSMSGCATLANG: language table.

-- Example
SELECT * FROM PSMSGCATDEFN
WHERE LAST_UPDATE_DTTM  > TO_DATE('03-DEC-07', 'DD-MON-YY')
AND LAST_UPDATE_DTTM  < TO_DATE('05-DEC-07', 'DD-MON-YY')
ORDER BY MESSAGE_SET_NBR, MESSAGE_NBR;
-- This will return messages that has been last update/added between 2 specific dates.

Previous PeopleSoft message catalog tables:
PS_MESSAGE_CATALOG: Stores information about PeopleSoft message catalogs such as message set number, message number and the actual message text.
MESSAGE_SET_TBL: Message set description table.

-- Example
SELECT * FROM PS_MESSAGE_CATALOG
WHERE LAST_UPDATE_DTTM  > TO_DATE('03-DEC-07', 'DD-MON-YY')
AND LAST_UPDATE_DTTM  < TO_DATE('05-DEC-07', 'DD-MON-YY')
ORDER BY MESSAGE_SET_NBR, MESSAGE_NBR;
-- This will return messages that has been last update/added between 2 specific dates.

Menu Tables

PSMENUDEFN: Store Menu related information. No related component info on this table.
PSMENUITEM: List the menu with all components attached to it.

Component Tables

PSPNLGRPDEFN: Stores component related information only.
PSPNLGROUP: This table will give you information regarding a specific component along with the names of pages attached to it.

Pages

PSPNLDEFN: Stores pages definitions.
PSPNLFIELD: Stores all items used by each page definition.

Security

PSPRSMPERM: Portal Structure Permissions.
PSAUTHITEM: Page Permissions. This table stores the information about the page level access for a permission list.
PSROLECLASS: Role Classes table. A many to many relationship table between Roles and Permission Lists.
PSROLEDEFN: This table stores information about Peoplesoft Role definitions. Users get permissions to PeopleSoft objects through Roles, which are assigned Permission Lists.
PSROLEUSER: This table stores information about the Users in Peoplesoft and the roles assigned to them.
PSCLASSDEFN: Permissions List definitions table. Permission list name can be found under Field Name CLASSID.
PSOPRDEFN: Users/Operator definition table. This table stores information about PeopleSoft users. This is the core table for User Profile Manager.
PSOPRCLS: Users/Operator and Perm list mapping Table. This table stores information about PeopleSoft users and the permission lists attached to those users.
A User gets these permission lists indirectly through the roles which are attached to the user
Here is an example query post that uses all of the above security tables!

URL Definitions

PSURLDEFN: Stores URL definitions. Here is the path to create URL definitions in PeopleSoft Root >> PeopleTools >> Utilities >> Administration >> URLs

Application Classes

PSAPPCLASSDEFN: Application Class Definitions table. You can use field PACKAGEROOT to search for a specific Application Package.

PeopleSoft Query Tables

PSQRYDEFN: Stores query related info.
PSQRYFIELD: Stores all fields used in a query (both the fields in the Select and Where clause).
PSQRYCRITERIA: Stores criteria query fields. You can get the name of the fields by joining the PSQRYFIELD table.
PSQRYEXPR: Stores query expressions.
PSQRYBIND: Stores query bind variables.
PSQRYRECORD: Stores all records used in all aspects of query creation
PSQRYSELECT: Stores all SELECT requirements by select type. Example would be sub select, join, ect.
PSQRYLINK: Stores the relationships to child queries.
PSQRYEXECLOG: Query run time log table that stores (only 8.4x and higher)
PSQRYSTATS: Query run time statistics table such as count of query execution, and date time of last execution (only in 8.4x and higher).

SQL Objects

PSSQLDEFN: Stores SQL object definitions.
PSSQLDESCR: Stores SQL objects descriptions, and description long.
PSSQLTEXTDEFN: Stores actual SQL text. You can filter by SQLTYPE field to get SQL objects of interest such as Views SQLs and Application Engine SQLs.

-- When SQL type is:
0 = Stand alone SQL objects
1 = Application engine SQL
2 = Views SQLs

Application Engines

PSAEAPPLDEFN: Table that stores Application Engine program definitions.
PSAEAPPLSTATE: Stores application engine STATE records and a flag to indicate if the record is the default STATE record.
PSAESECTDEFN: Application engine section information and also stores last user id to update a specific section.
PSAESECTDTLDEFN: AE section along with descriptions and wither the section is active or not.
PSAEAPPLTEMPTBL: If your application engine uses Temp tables it will show on this record.
PSAESTEPDEFN: Steps in application engines are stored in this table.
PSAESTMTDEFN: Stores your application engine actions and along with their types, such as "Do Select" and so on.
PSAESTEPMSGDEFN: Application engine message action definition table.
AEREQUESTTBL: Application Engine request table behind the AE run control page.
AEREQUESTPARM: Application Engine request parameters table behind the AE run control page.

PeopleCode Tables

PSPCMNAME: PeopleCode Reference table.
PSPCMPROG: Store actual PeopleCode programs (actual code behind PeopleCode events).

Process Request Tables

PSPRCSQUE: This record contains the process request information to run a process request.
PSPRCSRQST: This record contains the process request information to run a process request.
PS_PMN_PRCSLIST: A view to list all process requests in the Process Monitor except for "Delete" (runstatus = 2) process requests.

Other Useful Tables

PSSTATUS: Stores PeopleSoft information such as PS Tools release version and the UNICODE_ENABLED boolean flag where a value of 1 indicates the DB is to be treated by Tools as a UNICODE DB.
PSCHGCTLLOCK: Description as explained by PeopleSoft "This table contains a a row for every object that is currently locked by any user. When the user requests to lock an object in the Application Designer, first this table is searched to see if the object is locked by another user. If it is not found, a row is inserted into the table. When the user requests to unlock an object, the row in this table is deleted."
Visit this post to see how could you make use of this table.
PSMAPFIELD: Stores Field mapping of Activity
PS_PRCSRUNCNTL: Run Control record stores Run Control IDs created online.

HRMS Security Update

 In order to apply security settings for newly created account, run the following three AE's -

SCRTY_CLSUPD - Refresh SJT_CLASS_ALL
SCRTY_OPRCLS
SCRTY_SJTUPD

We created a job which runs the following nightly. It is my
understanding that they need to run in this sequence.



Since these deal with row-level security, and since they rebuild the SJT
tables, if they are run while people are on the system, those people
will temporarily lose their ability to look at employees.

----------------- more -----------

HR_PERSDATA Application Engine - Updates the PERSONAL_DATA snapshot table with future dated entries as they become effective.

NAME_DISPLAY Application Engine - Refreshes the NAME_DISPLAY and NAME_FORMAL fields in all records with the NAME_GBL_SBR. Use when the criteria used to change these fields is changed.

PER099 Application Engine - Fill EMPLOYEES Table.

PTAF_NEM Application Engine - PTAF_NEM - Approval Framework Escalation App Engine.           AWE Workflow escalation process.

SCRTY_OPRCLS Application Engine - SCRTY_OPRCLS - Updates the SJT_OPR_CLS security Join Table.

SCRTY_SJTDLY Application Engine -  Nightly update for SJT Records -  Update of all effdt rows that have become current as of this data.

HRS_AM Application Engine - Applicant Search Auto-Match -Recruiter Applicant Search auto-match process.

HRS_DUPE_PRC Application Engine - Find Applicant Duplicates - Finds applicant duplicates given certain criteria.

HRS_JOB_AGNT Application Engine - Job Agent - Run the Job Posting Search Agent process.

HRS_JSCH_IDX Application Engine -Verity based Job Posting Index build process.

HRS035 SQR Report - Application Analysis (USA).

PER506 SQR Report - Department Tbl & Departmental - This report lists any departments that you have created in the Department Tree but have not yet added to the departmental security tree, It also lists any changes in departments since the effective date of the current security tree.

PER900 SQR Report - Core HR Data Integrity Audit. - Audit of the CORE HR Tables for Data Relationship Integrity.

PSBARUN - Cobal Job - For the Benefit Administration - scheduled to run twice each night with some custom pre and post processes.

Troubleshooting PT 8.48 PT 8.49 Integration Broker

http://iambic.homeip.net/site/doku.php/tech/psoft/ib_troubleshooting
====== Troubleshooting PT 8.48 PT 8.49 Integration Broker ======
Troubleshooting guidance for publication process, subscription process, and other possible problems.

===== Publication Process Problems =====
 Possible publication process problems include:\\
  * Publication contract is not created.
  * Publication contract is in NEW status.
  * Publication contract stays in RETRY status.
  * Publication contract is in WORKING status.
  * Publication contract is in TIMEOUT status.
  * Publication contract is in ERROR status.

==== Pub Contract Is Not Created ====
  * No publication PeopleCode exists.
  * Publication PeopleCode is incorrect.
  * No outbound routings exists for the service operation.

==== Pub Contract Is in NEW Status ====
  * Sending queue is paused.
  * Publication Dispatcher crashed or was brought down.
  * Sending node is paused.
  * Previous service operation had a status of Retry, Error, or Timeout.
  * Sending domain is not active.
  * After an unsuccessful ping, a row may be added to the table PSNODESDOWN; which will hold up the message queue; query the table PSNODESDOWN.

==== Pub Contract Stays in RETRY Status ====
  * The remote node cannot be pinged successfully; the publication contract will be processed when the remote node comes back up.
  * No publication handler is available, either because it crashed or it was brought down.
  * Receiving Node URL is incorrect in integrationGateway.properties file.

==== Pub Contract Is in WORKING Status ====
  * The publication handler processing the contract is on another machine and either the machine or the domain is down. Processing should continue when the pub/sub system on the other machine comes back up.
  * Single threading on the application server is slowing processing.

==== Pub Contract Is in TIMEOUT Status ====
  * An exception occurred on the target application server (look in APPSRV.LOG file for details); verify that:
  * The reply is incorrectly routed; check Gateway for correct machine address of target node.
  * Bad XML syntax.

==== Pub Contract Is in ERROR Status ====
  * Receiving node user profile not authorized to service operation.
  * Inbound routing is not set up on the receiving system.
  * Service operation is not active on the receiving system.
  * Service operation has not been granted security access on the receiving system.
  * The source node is not defined in the target database.
  * Handler PeopleCode is bad.
  * Remote application server is down.
  * Receiving Node is not defined in the integrationGateway.properties file.
  * Receiving PeopleSoft node has not been added to single signon.
  * Service operation version on target is not active.

===== Subscription Process Problems =====

Possible subscription process problems include:\\
  * Subscription contract is not created.
  * Subscription contract is in NEW status.
  * Subscription contract is in STARTED status.
  * Subscription contract is in WORKING status.
  * Subscription contract is in ERROR status.
  * Subscription contract is in EDIT status.

==== Sub Contract Is not Created ====
  * No handler exists for the service operation.
  * Service operation handler is missing method.
  * Queue routing rules not set up properly.

==== Sub Contract Is in NEW Status ====
  * Application Server down.
  * Pub/Sub processes not configured on Application Server domain.
  * The Subscription Dispatcher has crashed or has been brought down.
  * Receiving queue is paused.
  * Receiving node is paused.
  * Previous service operation had errors or timed out.
  * No row was inserted into PSAPMSGSUBPRCID, To insert a row enter the following SQL statement in your Query tool use the following statement:
insert into PSAPMSGSUBPRCID values(0)

==== Sub Contract Is in STARTED Status ====
  * Subscription Handler is down.
  * Target component is not valid.

==== Sub Contract Is in WORKING Status ====
  * Subscription Handler crashed while processing message.

==== Sub Contract Is in ERROR Status ====
  * Queue property if Ordered enables subscription contracts to go in random order, which causes FULLSYNC service operations to error out  when the transaction is subscribed before the header.
  * Service operation handler PeopleCode errors exist.
  * Application data errors exist.

==== Sub Contract Is in EDIT Status ====
Possible cause is that the XML was edited and is not yet resubmitted for processing.

===== Other Possible Problems =====
Other possible problems include:\\
  * Cannot find service operation in Service Operation Monitor.
  * Service operations are being processed in an incorrect order.
  * Service operation is not created.
  * Service operation instance stays in NEW status.
  * Service operation instance stays in STARTED status.
  * Service operation instance stays in WORKING status.
  * Unable to ping a node.
  * Queue is PAUSED.

==== Cannot Find Service Operation in Service Operation Monitor ====
  * Possible causes is that filtering is set in the Service Operation Monitor.

==== Service Operations are Being Processed in an Incorrect Order ====
  * Possible cause is that the queue was partitioned and the resulting subqueues do not match what was assumed for the ordering of the service operations.

==== Service Operation Instance not Created ====
  * Possible cause is that the service operation is inactive.

==== Service Operation Instance Stays in NEW Status ====
  * The Application server is down.
  * Pub/sub services are not configured on the Application Server domain.
  * The Message Dispatcher crashed or was brought down.
  * The item is not at the top of the queue; all service operations with the same queue or subqueue are in the same queue.

==== Service Operation Instance Stays in STARTED Status ====
  *  All Message Handlers crashed or were brought down; processing will resume when Message Handlers come back up.
  * The Message dispatcher processing the message is on another machine, and either the machine or the application  server domain is down.

==== Service Operation Instance Stays in WORKING Status ====
  * Message Broker Handler crashed.
  * The Message Handler processing the message is on another machine, and either the machine or the application server domain is down.
  * The Message Handler working on the message is blocked. The service will time out, and the Message Dispatcher will retry the message.

==== Unable to Ping a Node ====
  * The web server for the Gateway is down.
  * The Gateway is not configured properly.
  * The application server for the node is down.
  * The Gateway URL is incorrect; verify that the URL is correct.
  * Copy URL in browser address; you should see: \\
   
    PeopleSoft Integration Gateway
    PeopleSoft Listening Connector
    Status: ACTIVE
   
==== Queue Is PAUSED ====
Possible cause is that some queues are delivered as paused. Change the status to Run for the service operations to process from New to Working.

===== Unable to find a Routing corresponding to the incoming request message =====
You are sending an async service operation from node A to target node B. The nodes are pingable.  The routing on target database and source database exist and are correct.  The same service operation works fine if you use and "ANY TO LOCAL" routing.

The "ANY TO LOCAL" routing type uses a virtual node ANY when node passwords fail and eliminates node authentication from the equation.  If "ANY TO LOCAL" works, but a standard point to point routing fails, reset the node authentication option to NONE rather than password and try again. If this works, then the node passwords were incorrect. Resetting these should resolve the problem.

Note: You can also see this error if the Message name does not match the //externalservice// operation name. **They have to Match.**

===== Unable to ping nodes. "Issuing node is not a trusted node" or "PSFT Authentication token failed for Node PSFT_HR" =====
Application server log shows:

PSAPPSRV.6475 (32) [10/17/06 22:09:45 VP1@JavaClient IntegrationSvc](3) PeopleSoft Token authentication failed: issuing node PSFT_HR is not a trusted node : PS@JavaClient
Starting with Peopletools 8.48 Integration Broker requires that nodes be //trusted// on both sides before pinging or sending messages.
To fix this, navigate to  Peopletools --> Security Objects --> Single Signon --> "Trust Authentication Tokens issued by these Nodes"  and add the appropriate node names on each side.