All Scroll Levels & Records that are in a PeopleSoft Component.

This SQL returns all Scroll Levels and Records for a given PeopleSoft component. Handy.

DEFINE componentName = 'your_component';

SELECT DISTINCT B.PNLNAME, B.RECNAME, B.OCCURSLEVEL, B.FIELDNUM
FROM
  PSPNLGROUP A
, PSPNLFIELD B
, PSRECDEFN C
WHERE A.PNLGRPNAME = &componentName
AND B.PNLNAME = A.PNLNAME
AND B.RECNAME <> ' '
AND B.ASSOCFIELDNUM = 0
AND B.FIELDNUM =
(SELECT MIN(D.FIELDNUM)
FROM PSPNLFIELD D
WHERE D.PNLNAME = B.PNLNAME
AND D.RECNAME = B.RECNAME
AND D.OCCURSLEVEL = B.OCCURSLEVEL)
AND C.RECNAME = B.RECNAME
AND C.RECTYPE = 0
UNION
SELECT DISTINCT B.PNLNAME, B.RECNAME, B.OCCURSLEVEL + D.OCCURSLEVEL, B.FIELDNUM
FROM
  PSPNLFIELD B
, PSRECDEFN C
, (SELECT E.SUBPNLNAME, E.OCCURSLEVEL
FROM PSPNLFIELD E
WHERE E.SUBPNLNAME <> ' '
AND EXISTS
(SELECT 'X'
FROM PSPNLGROUP F
WHERE F.PNLGRPNAME = &componentName
AND F.PNLNAME = E.PNLNAME)) D
WHERE B.RECNAME <> ' '
AND B.ASSOCFIELDNUM = 0
AND B.FIELDNUM =
(SELECT MIN(D.FIELDNUM)
FROM PSPNLFIELD D
WHERE D.PNLNAME = B.PNLNAME
AND D.RECNAME = B.RECNAME
AND D.OCCURSLEVEL = B.OCCURSLEVEL)
AND C.RECNAME = B.RECNAME
AND C.RECTYPE = 0
AND D.SUBPNLNAME = B.PNLNAME ORDER BY 1,3,4

How to compare objects between projects

Sometimes you just want to see the differences between 2 projects, what objects are in one and not the other or what objects are in both. Here is some SQL to do just that:

select * from (
SELECT
a.projectname,
A.OBJECTVALUE1 || decode(A.OBJECTVALUE2,' ','','.'||A.OBJECTVALUE2) || decode(A.OBJECTVALUE3,' ','','.')|| A.OBJECTVALUE3 || decode(A.OBJECTVALUE4,' ','','.') || A.OBJECTVALUE4 as Object,
decode(a.objecttype,
'0', 'Record',
'1', 'Index',
'2', 'Field',
'3', 'Field Format',
'4', 'Translate value',
'5', 'Page',
'6', 'Menu',
'7', 'Component',
'8', 'Record Peoplecode',
'9', 'Unknown Object Type',
'10','Query',
'11','Tree Structure',
'12','Tree',
'13','Access Group',
'14','Colours',
'15','Style',
'16','Unknown Object Type',
'17','Business Processes',
'18','Activities',
'19','Roles',
'20','Process Defintions',
'21','Server Definition',
'22','Process Type Definitions',
'23','Job Definitions',
'24','Recurrence Definition',
'25','Message Catalog Entries',
'26','Dimensions',
'27','Cube Definitions',
'28','Cube Instance Definitions',
'29','Business Interlink',
'30','Record SQL',
'31','File Layout Definitions',
'32','Component Interface',
'33','Application Engine Programs',
'34','Application Engine Sections',
'35','Message Node',
'36','Message Channels',
'37','Messages',
'38','Approval Rule Sets',
'39','Unknown Object Type',
'40','Unknown Object Type',
'41','Unknown Object Type',
'42','Unknown Object Type',
'43','Unknown Object Type',
'44','Page Peoplecode',
'45','Unknown Object Type',
'46','Component Peoplecode',
'47','Unknown Object Type',
'48','Component Rec Fld Peoplecode',
'49','Images',
'50','Style Sheet',
'51','HTML',
'52','File Reference',
'53','Permission Lists',
'54','Portal Registry Definitions',
'55','Portal Registry Structures',
'56','URL Definition',
'57','Application Packages',
'58','Unknown Object Type',
'59','Unknown Object Type',
'60','Unknown Object Type',
'61','Archive Template',
'62','Unknown Object Type',
'63','Portal registry User Favourites',
'64','Mobile page',
'PeopleSoft are being smart again with - ' || a.objecttype ) as Type,
decode(a.UPGRADEACTION,0,'Copy',1,'Delete',2,'Manual',3,'Copy Properties') as Action
from sysadm.PSPROJECTITEM A
where a.objecttype <> '52'
and not (a.objecttype=0 and a.objectid2 = 2)
and not (a.objecttype=2 and a.objectid2 = 102)
-- objects from this project...
and a.projectname = 'GUR_ETHICS_ALL'
-- ... that do/do not exist...
and not exists
(select 'x'
from sysadm.PSPROJECTITEM B
where a.projectname <> b.projectname
and A.OBJECTVALUE1 || decode(A.OBJECTVALUE2,' ','','.'||A.OBJECTVALUE2) || decode(A.OBJECTVALUE3,' ','','.') || A.OBJECTVALUE3 || decode(A.OBJECTVALUE4,' ','','.') || A.OBJECTVALUE4 = b.OBJECTVALUE1 || decode(b.OBJECTVALUE2,' ','','.'||b.OBJECTVALUE2) || decode(b.OBJECTVALUE3,' ','','.') || b.OBJECTVALUE3 || decode(b.OBJECTVALUE4,' ','','.') || b.OBJECTVALUE4
and a.objecttype = b.objecttype
-- ...in this project.
and b.projectname = 'GUR_RESEARCH_ALL')
)
order by 3,2

Help! No Matching Buffer Error - How to start.

Definately the most irritating error I come across, the "No matching buffer" error. I find this especially irritating as PeopleTools has found the error but refuses to give you any clue as to which scroll/data combination is causing it.

Sometimes the problem can be quickly identified by looking through the data in the component, for example a drop down box may be showing 'invalid value', which could lead you quickly to the offending data.

But how to actually get into the component to take a look around? That damn error message is stopping you from opening the component.

Ctrl + J. Pressing this combination displays information about the component. The magic bit is that by clicking 'continue', or Ctrl + J again, the component will open complete with the data that is valid.

A simple, obvious but useful start to tracking down the cause of the dreaded "no matching buffer" error. If you can quickly identify where the problem may be by looking for clues in the component you can save a lot of time, frustration and especially boredom by not having to troll through a potentially large and complex component, page-by-page, scroll-by-scroll.