PeopleSoft Administrator Role

Using the PeopleSoft Administrator Role:

The PeopleSoft Administrator role gives full access to all menus and pages in the PSAUTHITEM table.

The PeopleSoft Administrator role cannot be viewed, edited, modified, or cloned because it is not defined as other roles are defined. The PeopleSoft Administrator role is hard-coded into every application. You will not find this role if you search for it in the roles component.

Note: The PeopleSoft Administrator role does not have access to data. Data security is granted through the primary and row-level permission lists assigned directly to a user profile.​

Check if component is changed

If ComponentChanged() Then
   &status = "y"
Else
   &status = "N"
End-If;

Integration broker debug with SQL

​​PeopleSoft delivers an application engine workflow solution to send notifications to a user if a message is in an error status. Simply schedule the delivered app engine program PT_AMM_WF as a recurring job.

Check meesages in error state:

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

0 - Error, 1 - New , 2 - Started, 3 - Working,
4 - Done, 5 - Retry, 6 - Timeout, 7 - Edited, 8 - Canceled

select * FROM PSIBERRP WHERE MESSAGE_PARM LIKE '%1000076%'
select * from PSAPMSGSUBCON
select * from PSAPMSGPUBHDR
select * from PSAPMSGPUBDATA
select * from PSAPMSGPUBCON

select * from PSAPMSGPUBCON where STATUSSTRING='ERROR'

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

select distinct STATUSSTRING from PSAPMSGPUBCON

select *

from PSAPMSGPUBHDR

where PUBSTATUS in (0, 5, 6)

Here's a simplified example of the where clauses. You'll need 3 of them. I added status 5, as I recall it's the timeout status. I took these directly from the app engine pt_amm_wf.

To get errored message instances before a contract is created:
select field list
from psapmsgpubhdr
where pubstatus in (0, 5, 6)
and your date criteria

To get errored pub contracts:
select field list
from psapmsgpubcon a,
psapmmsgpubhdr b
where a.pubnode = b.pubnode
and a.chnlname = b.chnlname
and a.pubid = b.pubid
and a.pubconstatus in (0, 5, 6)
and your date criteria

To get errored sub contracts:
select field list
from psapmsgsubcon
where subconstatus in (0, 5, 6)
and your date criteria​​

Component Interface Meta data

Recently I was analyzing a number of Component Interface meta data and below are few simple SQL queries I formed and thought would be useful. However, all these queries are based on PSBCDEFN and PSBCITEM tables.

SQL Query to find search records in a CI:
 SELECT SEARCHRECNAME, ADDSRCHRECNAME
 FROM PSBCDEFN
 WHERE BCNAME = :1 -- Component Interface name

SQL Query to find out the records exposed by a CI:
 SELECT distinct recname
 FROM PSBCITEM
 WHERE BCNAME = :1 -- Component Interface name
This query also returns the search record.

SQL Query to find out the Component associated with a Component Interface:
 SELECT BCPGNAME,MARKET,MENUNAME
 FROM PSBCDEFN
 WHERE BCNAME = :1 -- Component Interface name

SQLRT: Operator ID not allowed to submit COBOL from client

The problem is that the PTPSQLRT program is issuing the following SELECT where the CLASSID field is the PROCESS PROFILE on the userid's User Profile setup. If the program does not find a PSAUTHITEM record with this CLASSID and a menu name of CLIENTPROCESS the 9989 error 'Operator ID not allowed to submit Cobol from the client' is issued by the program and the process abends.

Solution
1: Verify that the following record exist for the Process Profile for the User Profile experiencing the issue.
SELECT CLASSID FROM PSAUTHITEM WHERE CLASSID = 'ALLPNLS' AND MENUNAME = 'CLIENTPROCESS'
2: If the record does not exist the user will get the 9989 error Operator ID not allowed to submit Cobol from the client.   Insert the following record to resolve the problem:
INSERT INTO PSAUTHITEM VALUES ('ALLPNLS','CLIENTPROCESS',' ',' ',' ',0,0)

SQL 'LIKE' with 'IN' clause

SELECT Tblb.RECNAME, Tbla.SQLID, Tbla.SQLTYPE, Tbla.SQLTEXT FROM PSSQLTEXTDEFN Tbla
INNER JOIN PS_TEMP_ABC Tblb ON
Tbla.SQLTEXT Like '%'+Tblb.RECNAME+'%'

No Auto Select' check box

There was a page which was taking too long to load. The search page was bypassed. The page after load had a search button to display data in grid.

This performance issue was resolved by selecting 'No Auto Select' check box of grid.