Some useful SQLs in PeopleSoft

--the permission lists associated to a user
SELECT A.OPRID, D.ROLEUSER, B.ROLENAME, B.CLASSID, C.ClASSDEFNDESC
FROM PSOPRDEFN A, PSROLECLASS B, PSCLASSDEFN C, PSROLEUSER D
WHERE A.OPRID=D.ROLEUSER AND B.CLASSID=C.CLASSID AND B.ROLENAME=D.ROLENAME AND A.OPRID= 'SPNR0000'
ORDER BY B.CLASSID;

-- The Value corresponding to a Process Run Status
SELECT XLATLONGNAME, FIELDVALUE FROM PSXLATITEM WHERE FIELDNAME = 'RUNSTATUS';

-- To get the processing time
SELECT
PRCSINSTANCE,
PRCSNAME,
OPRID,
RUNSTATUSDESCR,
DECODE (FLOOR(TO_CHAR((enddttm-begindttm)*24*60))||':'||LPAD(FLOOR(((TO_CHAR((enddttm-begindttm)*24*60))- FLOOR(TO_CHAR((enddttm-begindttm)*24*60)))*60) ,2,0), ':',' ',FLOOR(TO_CHAR((enddttm- begindttm)*24*60))||':'||LPAD(FLOOR(((TO_CHAR((enddttm-begindttm)*24*60))-FLOOR(TO_CHAR((enddttm-begindttm)*24*60)))*60) ,2,0)) AS MINSEC
FROM PS_PMN_PRCSLIST
WHERE runstatusdescr ='Success';

Sending Mails Without SMTP Server

MCF class does not need app server SMTP server and Port settings

import PT_MCF_MAIL:SMTPSession;
import PT_MCF_MAIL:MCFEmail;
import PT_MCF_MAIL:MCFMultipart;
import PT_MCF_MAIL:MCFBodyPart;
import PT_MCF_MAIL:MCFPart;
import PT_MCF_MAIL:*;

&OuboundEmail = create PT_MCF_MAIL:MCFOutboundEmail(); &OuboundEmail.smtpserver = "";
&OuboundEmail.SMTPPort = 25;
&OuboundEmail.from = "xyz@abc.com";
&OuboundEmail.recipients = 'pqr@lmn.com';
&OuboundEmail.subject = "Test Subject";
&OuboundEmail.text='' ;
&ret = &OuboundEmail.send();

Hide The Grid Using PeopleCode

Local Rowset &Rowset = GetLevel0()(1).GetRowset(Scroll.AA_OVRD_WHERE);
rem &Rowset.HideAllRows();

[or]

For &I = 1 To &Rowset.ActiveRowCount
&Rowset.GetRow(&I).Visible = False;
End-For;

Number to Word

Converting number to words...

1.

select CASE WHEN LENGTH(1234567892) >= 10 THEN initcap(to_char(to_date(substr(ROUND(1234567892),1,3),'J'),'JSP')) ' Crore ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-6,2),'J'),'JSP')) ' Lakh ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-4,5),'J'),'JSP')) WHEN LENGTH(ROUND(1234567892)) = 9 THEN initcap(to_char(to_date(substr(ROUND(1234567892),1,2),'J'),'JSP')) ' Crore ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-6,2),'J'),'JSP')) ' Lakh ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-4,5),'J'),'JSP')) WHEN LENGTH(ROUND(1234567892)) = 8 THEN initcap(to_char(to_date(substr(ROUND(1234567892),1,1),'J'),'JSP')) ' Crore ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-6,2),'J'),'JSP')) ' Lakh ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-4,5),'J'),'JSP')) WHEN LENGTH(ROUND(1234567892)) = 7 THEN initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-6,2),'J'),'JSP')) ' Lakh ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-4,5),'J'),'JSP')) WHEN LENGTH(ROUND(1234567892)) = 6 THEN initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-6,1),'J'),'JSP')) ' Lakh ' initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-4,5),'J'),'JSP')) WHEN LENGTH(ROUND(1234567892)) < class="blsp-spelling-error" id="SPELLING_ERROR_47">initcap(to_char(to_date(substr(ROUND(1234567892),length(ROUND(1234567892))-4,5),'J'),'JSP')) END as words FROM DUAL

2.

select initcap(to_char(to_date(1234567,'J'),'JSP')) from dual
This would produce the following output:
One Million Two Hundred Thirty-Four Thousand Five Hundred Sixty-Seven
This usual approach matches with Indian system till tens of thousands.

About PeopleSoft DataBase

Peoplesoft Database separated into 3 parts....

1.System Catalog
2.PeopleTools
3.Application Data

System Catalog records the information about the database and its content. means Table Names, Column Names and Index Names Etc...
it contaions Database Metadata. it tracks all objects in entie database.
Ex:-DBA_OBJECTS.

PeopleTool record the information about the defination thats make up our peopleSoft Appliacation , which means its stores record names, page names annd component names etc.... it contains metadata about the peopleSoft application installed on the database.

Application Data records the information which is entered by the customers.

Webserver presents the Data.

Appserver validates the Business Logic.

Database Server stores and updates the data.

Role Of Component Processor In Peoplesoft

If I had to tell someone what the most important concept they need to understand as a PeopleSoft developer, I would quickly answer. It is the Component Processor and how it allocates Buffer space, how it places information in the buffer, and how to ensure that you maximize its use.
The PeopleSoft application is an extremely powerful and highly configurable product that runs many different applications. Every application that is accessed online through the PeopleSoft Internet Architecture is part of a component. When you navigate to a particular content reference or menu item to access a particular page, the Component Processor is what performs all of the activity including executing SQL, PeopleCode, loading the component/page, and filling the buffer with the necessary data that was requested. More importantly, all of these activities happen in a specific order.
It is this overall process that every PeopleSoft developer and analyst needs to understand thoroughly. If you understand how the component processor works and how it allocates buffer space, than debugging, performance tuning, designing, and developing your PeopleSoft applications will be simple for you. However, if you miss this concept or don’t fully understand it, your design and development process and your online performance will be inadequate. In addition, it will become more difficult for you to debug your applications without understanding the component processor.
The Component Processor is the core engine of the PeopleSoft PIA and is responsible for the following:
• Builds and displays the search page so the user can select a high level key to retrieve the data.
• Performs search processing, where it will retrieve and save the search key values that were selected for the component.
• Makes calls to the database to retrieve the metadata that is needed to build the component.
• Builds the component, including all of its pages, fields, PeopleCode programs, and data, creating buffers for the component data.
• Performs any additional processing for the component or the page. • Displays the component and waits for user action.
If you build PeopleSoft applications and write PeopleCode, than you must fully understand the component processor and how it allocated buffer space and places objects and data into the buffer. Every time you access a component, the component processor queries the database and moves relevant data from the database to the application server. The Component processor manages this data according to special rules. Therefore, your Component design has a crucial impact and it is extremely important to understand how the buffers are allocated and maintained by the component processor.
There is a lot more detail to discuss in regards to the component processor and the component buffer. Too much to place in a single blog/article.

PeopleSoft Homepage Greeting

Have you ever wanted to display the database and username on every page like Peoplesoft used to do in version 7.x?
Add the following peoplecode in the application package PT_BRANDING under brandingbase to display user name/database name on every page.
Replace all occurrences of
&Greeting = &Portal.Homepage.Greeting
with
&Greeting = &Portal.Homepage.Greeting / " " / %UserId / " " / "(" / %DbName / ")"