Installing PeopleSoft Client

Usually install PeopleSoft files under C:\Oracle\PeopleSoft. Copy from server everything under:

• bin
• tuxedo
• setup (except PSCrystal)

Converts ASCII code to a string

Char() or Code() function: One converts ASCII code to a string and the other converts a string character to ASCII.

Split new line character

&newLineChar = Char(13);

&AS_LineTwo = Split(&pg_Variable_Long, &newLineChar);

&pg_Var_Part1 = &AS_LineTwo [1];

&pg_Var_Part2 = &AS_LineTwo [2];

Scheduling app engine through PeopleCode

Local ProcessRequest &MYRQST;
 
   &MyAppName = "CSGC_OL_RVW";
   &MYRQST = CreateProcessRequest("Application Engine", &MyAppName);
   &MYRQST.RunControlID = "TEST";
   &MYRQST.RunLocation = "PSUNX";    
   &MYRQST.Schedule();
 
  If &MYRQST.Status = 0 Then /* if Schedule status is success */

  End-If;

%CurrentDateIn MetaSQL prevents Oracle CBO from correctly evaluating selectivity of predicate.

The cost based optimiser chooses a poor execution plan for a particular critical SQL statement in CRM because the expansion of the %CurrentDateIn macro is excessively complicated. The problem occurs in a delivered view PS_RBC_PACKAGE_VW and a related custom view PS_XX_RBCPKCLTR_VW.

The views both contain a pair of date conditions, which are coded in line with PeopleSoft standards

AND A.FROM_DATE <= %CurrentDateIn

AND A.TO_DATE >= %CurrentDateIn

AND E.FROM_DATE <= %CurrentDateIn

AND E.TO_DATE >= %CurrentDateIn

On an Oracle RDBMS, this expands to

AND A.FROM_DATE <= TO_DATE(TO_CHAR(SYSDATE,’YYYY-MM-DD’),’YYYY-MM-DD’)

AND A.TO_DATE >= TO_DATE(TO_CHAR(SYSDATE,’YYYY-MM-DD’),’YYYY-MM-DD’)

AND E.FROM_DATE <= TO_DATE(TO_CHAR(SYSDATE,’YYYY-MM-DD’),’YYYY-MM-DD’)

AND E.TO_DATE >= TO_DATE(TO_CHAR(SYSDATE,’YYYY-MM-DD’),’YYYY-MM-DD’)

with the result that this statement took over 15 seconds to execute. However, if the view is recoded as follows

AND A.FROM_DATE <= TRUNC(SYSDATE)

AND A.TO_DATE >= TRUNC(SYSDATE)

AND E.FROM_DATE <= TRUNC(SYSDATE)  

AND E.TO_DATE >= TRUNC(SYSDATE)

Then the execution time fell to less than 1 second.

There is, of course, a simple workaround. TRUNC(SYSDATE) is functionally equivalent to

TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD')

and can simply be coded into the application as a customisation where necessary.   

http://www.go-faster.co.uk/PSFT.metasql.20060425.pdf

Create an excel file through application engine

This code will create an excel file through application engine. Can be called from a push button too.