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;

How to identify bundles that have been applied?

Bundles Query:
SELECT A.UPDATE_ID,
  A.DESCR,
  A.DTTM_IMPORTED,
  A.FIXOPRID,
  A.APPLYOPRID,
  A.PRODUCT_LINE,
  A.RELEASEDTTM,
  A.RELEASELABEL,
  A.MAINTLOGTYPE,
  A.DESCRLONG
FROM PS_MAINTENANCE_LOG A
WHERE upper(A.DESCRLONG) LIKE '%BUNDLE%'
ORDER BY 2 DESC;

Tax Update Query:
SELECT A.UPDATE_ID,
  A.DESCR,
  A.DTTM_IMPORTED,
  A.FIXOPRID,
  A.APPLYOPRID,
  A.PRODUCT_LINE,
  A.RELEASEDTTM,
  A.RELEASELABEL,
  A.MAINTLOGTYPE,
  A.DESCRLONG
FROM PS_MAINTENANCE_LOG A
WHERE upper(A.DESCRLONG) LIKE '%TAX UPDATE%'
ORDER BY 2 DESC;

CI - Accessing a PSMessages Collection

Use the PSMessages collection to return all of the messages that occur during the session. The following example finds all the messages listed in the PSMessages collections when the Component Interface Save methods fails. The example assumes that &CI is the Component Interface object reference.

If Not &CI.Save() Then
   /* save didn't complete */
   &COLL = &MYSESSION.PSMessages;
   For &I = 1 to &COLL.Count
      &ERROR = &COLL.Item(&I);
      &TEXT = &ERROR.Text;

      /* do message processing */

   End-For;
&COLL.DeleteAll(); /* Delete all messages in queue */
End-if;

As you evaluate each message, delete it from the PSMessages collection. Or, delete all the messages in the queue at once using DeleteAll.

If there are multiple errors when saving a Component Interface, all errors are logged to the PSMessages collection, not just the first occurrence of an error.

CI - General Considerations

This section discusses general considerations for component interface programs.

WinMessage Unavailable
You cannot use the WinMessage API in a component that will be used to build a component interface. Use the MsgGet() function instead.

Email from a Component Interface
To use a component interface to send email, use the TriggerBusinessEvent PeopleCode event, not SendMail.

Related Display
Related display fields are not available for use in a component interface because they are not held in the buffer context that the component interface uses.

Row Inserts
If row insert functionality has been disabled for a page, you must take care when calling inserts against the corresponding component interface. Any PeopleCode associated with buttons used on the page to add rows will not be invoked by the component interface when an insert is done.

Note.
1. If a component has logic that inserts rows on using the RowInsert event, the component interface
cannot identify the change and locate the rows that were inserted by the application code. Generic interfaces such as Excel to Component Interfaces utility and the WSDLToCI will not function correctly when using this type of dynamic insert.

2. The same field is in different scrolls but they do not have the same value
For example, at scroll 0 you might have the field EFFDT set to 01/01/2009. You want the EFFDT field to get the same value in scroll 1, so you write code to set it from the value in level 0 but it isn't working. If this is the case, check the field name in the component interface, you may find that PeopleSoft has automatically renamed it. For example, it might be EFFDT at level 0 but something like EFFDT_1 in level 1 of the component interface. 

CI - Create a user-defined method

Create a user-defined method
1. Right-click anywhere in the component interface view.
2. Select View PeopleCode from the menu.
3. Write the required PeopleCode functions.
4. Set permissions for the methods that you created.

Exporting User-Defined Methods
If you want a user-defined component interface to be exportable, meaning used by code that instantiates the
component interface, the method PeopleCode definition must include a Doc statement. It is in the form of:
Doc <documentation for method>
where <documentation for method> describes what the method does

Example
Function MyFooBar(int foo) returns boolean
Doc  'test'
if (foo >0) then
return True;
else
return False;
end-if;
end-function; 

PS Campus Solution Time table - 'Resource in Use' Error

"The resource you are trying to schedule is currently in use. Try your save again. If after 3 attempts you still cannot save, contact your system administrator."
There is a "Resource Queue" in PeopleSoft's class scheduling system. This Queue is meant to prevent users from double booking facilities. When two people attempt to schedule facilities at the same time, the data is locked in the Resource Queue by the first user. This lock should normally be resolved in a few minutes when the first user completes his work.
If the lock lingers more than five minutes, it is probably 'stuck.' To manually remove the lock:
    Log into ISIS (EAST agents have been authorized).
    Follow the links:
    Home
    -> Curriculum Management
    -> Schedule of Classes
    -> Resource Queue Cleanup
    Click on Search.
    If a lock is found and five or more minutes have elapsed from the time listed, click on Unlock Resource.

SQL to get a process historical run status

select
    PQ.SERVERNAMERUN,  
    PQ.PRCSINSTANCE,
    PQ.PRCSTYPE,
    PQ.PRCSJOBNAME,
    PQ.PRCSNAME,
    PD.DESCR,
    PQ.OPRID,
    PQ.RUNCNTLID,
    (select  XLATSHORTNAME
        from    PSXLATITEM
        where   FIELDNAME = 'RUNSTATUS'
        and     FIELDVALUE = PQ.RUNSTATUS
    ) as RUNSTATUS,
    PQ.RUNDTTM,
    PQ.RQSTDTTM,
    PQ.ENDDTTM,
    PQ.BEGINDTTM
from
    PSPRCSRQST PQ inner join PS_PRCSDEFN PD
    on PQ.PRCSNAME = PD.PRCSNAME
where
    PQ.PRCSNAME = 'SYSAUDIT'
order by PRCSINSTANCE desc;