Derived records and its limitations

"Derived records" are useful PeopleSoft objects, but sometimes there is some confusion about them.

If you try to define a scroll area or grid consisting only of derived record fields, the page will not be valid. These objects must contain at least one "real" database field. Sometimes, though, it is necessary to create a scroll or grid that doesn't relate directly to a table or view. One easy way around this problem is to create a dummy view containing just one field that returns one blank space or other unused value. Put that field into your scroll or grid, mark the scroll/grid as "no auto select," and you can now fill and manipulate the scroll/grid without any complaints from the system. (I have also used a dummy view as a component's search record when I wanted to do all of my data selection in code.). Dynamic views can be used as dummy view with no Sql in its definition. This avoids us from creating any database object.

CallAppEngine Function

Use the CallAppEngine function only in events that allow database updates, because, generally, if you are calling Application Engine, you intend to perform database updates. This category of events includes the following PeopleCode events:

•SavePreChange (Page)

•SavePostChange (Page)

•Workflow

•FieldChange 

CallAppEngine cannot be used in a Application Engine PeopleCode action. If you need to access one Application Engine program from another Application Engine program, use the CallSection action.

SetComponentChanged() Function

The SetComponentChanged() built-in PeopleCode function does not work in Page Activate.

SetComponentChanged();

The SetComponentChanged() built-in function is only applicable after the Page Activate event has run.

After PreBuild, PostBuild, Activate event runs, the changed status will be set to false. so if user made some change during above events, they will be ignored after those events.

Transfer and message

When a Transfer, TransferExact, or RedirectURL function follows a WinMessage or Messagebox that is not a think-time function, the message box dialog pop-up is not be displayed.

When MessageBox or WinMessage functions are called with Style 0, in other words, %MsgStyle_OK, the style shows a single 'OK' button and does not act as a Think-Time function.

Solution - Change the MessageBox style to %MsgStyle_OKCancel

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+'%'