Don't want PS_ before record name?

While creating a new record, on the record type tab in application designer, fill in the name you want in the Non-standard SQL table name.

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

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.

Upgrade PSQuery after change in Record structure

Open every query, we would be prompted with a message that the record structure has undergone changes and we may just have to follow as recommended.

To test compare the result of old query vs new query.

Avoid using the SqlExec statements in the PeopleCode

Some of the issues with SQL Exec are: 

1. It can select only single row of data. 

2. It surpass the component processor and interacts directly with Database server. 

3. It has to be used in only limited number of events and that too you can cause data consistency problems if not timed properly. 

 #1 is precisely the reason why you'll use SQLExec if your requirements demand it. SQLExec outputs only the first row to its output variables. Any subsequent rows are discarded. This means if you only want to fetch a single row, SQLExec can perform better than the other SQL functions, because only a single row is fetched.

SQL objects for insert/update/delete for all of the above and they are well documented within PeopleBooks. The record object has methods called Update(), Delete(), Insert(). It also has methods called SelectByKey() and SelectByKeyEffdt() which can be used for selecting into the record object.

Disabling paste on an input field (not verified)

A colleague of mine recently had a requirement to place an email address field on a page, and then just below it a ‘confirm email address’ – however the user should not be permitted to copy and paste from the first field to the second, they should retype their email address.

We didn’t think that there was a way of disabling paste via PeopleCode, so resorted to JavaScript. The solution was actually quite straightforward in the end:

1) Place the two input fields on the page and connect them to the records you wish to save to, as usual.

2) Place an HTML Area on the page, making sure it’s beneath the other fields (both on this screen and the order tab).

3) Make the HTML Area contents static, and paste in the following:

<SCRIPT language=JavaScript>

var message = "Paste disabled. Please re-key.";

function disablepaste(){

alert(message);

return false;

}

document.getElementById('DERIVED_TEST_TEXT2')

.onpaste=disablepaste;

</SCRIPT>

4) Swap DERIVED_TEST_TEXT2 in the above code snippet for you Record/Field name (i.e. it’s the record name, then an underscore, then the fieldname. Look in View Source or Chrome Dev Tools/Firebug if you’re not sure.)

5) You might also want to make the alert message a little more user friendly too.

UNION vs UNION ALL

Both UNION and UNION ALL concatenate the result of two different SQLs. They differ in the way they handle duplicates.
-UNION performs a DISTINCT on the result set, eliminating any duplicate rows.

-UNION ALL does not remove duplicates, and it therefore faster than UNION.

In ORACLE: UNION does not support BLOB (or CLOB) column types, UNION ALL does.

SQLEXEC () in RowInit PeopleCode

We cannot perform INSERT,UPDATE statement's through SQLEXEC( ) in Rowinit PeopleCode event.

Below is the alternative to perform insertion and updation in rowinit .

Creating a stroed procedure, that perform's the insertion or updation.
Execute the procedure using SQLEXEC( ) in rowinit.

Example:

1.Creating the stored procedures in Oracle

CREATE OR REPLACE PROCEDURE CH_SAMPLE_PROCEDURE
AS 
BEGIN
    UPDATE PS_CH_TEST_TBL
            SET  COUNT=100;
    COMMIT;
END CH_SAMPLE_PROCEDURE;

2.Execute the procedure
 
EXECUTE CH_SAMPLE_PROCEDURE;

Sample code in ROWINIT:

1.Create a sql object,that contains the sql for creating stored procedure.
    SQLEXEC(SQL.CH_TESTPROCEDURE_SQL);
   Here CH_TESTPROCEDURE_SQL contains a sql for creating stored procedure.
2.Execute procedure.
    SQLEXEC("EXECUTE CH_SAMPLE_PROCEDURE");

Note:
1. To create a stored procedure , you need to have permissions in database level.
2. If you want to check whether the stored procedure is created or not use the below sql.
SELECT * FROM ALL_PROCEDURES WHERE OBJECT_NAME='CH_SAMPLE_PROCEDURE'

An extra empty row gets added while saving grid data

Whenever I try saving data in the grid with same record at level 0 and level 1, an extra row gets added in the database having only the key field of level 0 as values in it. All other column fields in the including the key field of grid are empty.

If level 1 field is mandatory, an error message will throw like 'field is mandatory'.

Resolution:
The answer is simple. First, Component Processor saved Level1 and then moved to save level0. It had the same record and hence tried entering again. To avoid this, you can use a read only field on Level0. This way, no SAVE will be fired on Level0, avoiding this problem.                                              

Tracking PeopleSoft usage with Google Analytics

One way is to place Google analytic code in HTML Object PT_HNAV_TEMPLATE.

Reference: http://www.erpassociates.com/peoplesoft-corner-weblog/peoplesoft/tracking-peoplesoft-usage-with-google-analytics.html

Question: What if PS is being accessed from Internet? Will reference to Web link not throw error?

Oracle SQL - Working with long columns (long datatype)

The LONG and LONG RAW datatypes have been deprecated in favour of LOBs for many Oracle versions, yet they still exist in the data dictionary and legacy systems.The Oracle documentation describes the LONG datatype's main restrictions as follows:

The use of LONG values is subject to these restrictions:

• A table can contain only one LONG column.
• You cannot create an object type with a LONG attribute.
• LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints).
• LONG columns cannot be indexed.
• LONG data cannot be specified in regular expressions.
• A stored function cannot return a LONG value.
• You can declare a variable or argument of a PL/SQL program unit using the LONG data type. However, you cannot then call the program unit from SQL.
• Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.
• LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.
• If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.

In addition, LONG columns cannot appear in these parts of SQL statements:

• GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements
• The UNIQUE operator of a SELECT statement
• The column list of a CREATE CLUSTER statement
• The CLUSTER clause of a CREATE MATERIALIZED VIEW statement
• SQL built-in functions, expressions, or conditions
• SELECT lists of queries containing GROUP BY clauses
• SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators
• SELECT lists of CREATE TABLE ... AS SELECT statements
• ALTER TABLE ... MOVE statements
• SELECT lists in subqueries in INSERT statements

Despite the size of this list, we'll find that most of the time we are blocked by the two restrictions highlighted above.
--------------------

Issue simulation:
SELECT * FROM MYTABLE WHERE UPPER(MY_LONG_COLUMN) LIKE UPPER('%my_search_word%');

ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Advice:
1) Convert the column to a CLOB

SQL> alter table MYTABLE modify (MY_LONG_COLUMN CLOB) ;

2) Write some PL/SQL to do your searching (limited upto 32K)

SQL> declare
    cursor text is select text from all_views where rownum<100;
    v varchar2(4000);
begin
    for c in text loop
        v:=c.text;
        if ( v like '%ZZ%') then
            dbms_output.put_line( 'text is '||substr(v,1,50));
        end if;
    end loop;
end;