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.