Find your Component!

This SQL helped me a lot and I still use it. Finding Page in PIA is a lot easier now if you have the new releases of PeopleSoft that has SES. But I still find my component using this;

SELECT E.PORTAL_LABEL, D.PORTAL_LABEL, C.PORTAL_LABEL, B.PORTAL_LABEL,
        A.PORTAL_LABEL FROM PSPRSMDEFN A
LEFT JOIN PSPRSMDEFN B ON A.PORTAL_PRNTOBJNAME = B.PORTAL_OBJNAME
        AND A.PORTAL_NAME = B.PORTAL_NAME
LEFT JOIN PSPRSMDEFN C ON B.PORTAL_PRNTOBJNAME = C.PORTAL_OBJNAME
        AND B.PORTAL_NAME = C.PORTAL_NAME
LEFT JOIN PSPRSMDEFN D ON C.PORTAL_PRNTOBJNAME = D.PORTAL_OBJNAME
        AND C.PORTAL_NAME = D.PORTAL_NAME
LEFT JOIN PSPRSMDEFN E ON D.PORTAL_PRNTOBJNAME = E.PORTAL_OBJNAME
        AND D.PORTAL_NAME = E.PORTAL_NAME WHERE A.PORTAL_OBJNAME LIKE '%PROCESSMONITOR%'

Running Dynamic SQL Object in PeopleCode

When coding page PeopleCode, developers uses function SQLExec once or twice, and even more than 10. It looks messy and sometimes you have to deal with lenghty SQL statements. What I have are codes taken from a delivered program that simplify large amount of SQLExec coding (I modified some of it according to my needs). Transfer all SQL statements from peoplecode to SQL objects and named it appropriately (e.g. HR_PERS_INSERT_01 when Insert statement , and HR_PERS_UPDATE_01 when Update statement). These SQL Object names are stored in PeopleSoft table, so I created a view that query PSSQLDEFN. The SQL statement for that view would look like these:

SELECT SQLID
FROM PSSQLDEFN
WHERE SQLID LIKE 'HR_PERS%'
AND SQLTYPE = '0'

As you can see the SQLTYPE = '0' represents stand alone SQL objects. The requirements changes when a particular condition is true the program must use inserts statements and if false it uses update statements. Here's the code;

&RS_SQL = CreateRowset(Record.HR_PERS_VW);
If &DataInsert = true
&RS_SQL.Fill("WHERE SQLID LIKE 'HR_PERS_INS%'");
else
&RS_SQL.Fill("WHERE SQLID LIKE 'HR_PERS_UPD%'");
End-If;
&RS_SQL.Sort(HR_PERS_VW.SQLID, "A"); /*A for Ascending and D for Descending*/
For &I = 1 To &RS_SQL.ActiveRowCount
&SQLName = &RS_SQL.GetRow(&I).HR_PERS_VW.SQLID.Value;
SQLExec(@("SQL." &SQLName), &DeptID);
End-For;

If you expand the SQLExec code, it will look like this;
SQLExec("Update PS_Table Set FLAG = 'Y' where DEPT_ID =:1", &DeptID);