Unable to run Processes in HE 7 76

Give self Class ALLPANLS. Make Primary Class.

SQL - Finding all child records for a given record

Using the keys of the parent find all the child records:


-- Tables that are associated with a publication.
SELECT DISTINCT RECNAME
FROM sysadm.PSRECDEFN A
WHERE RECNAME LIKE 'GUR%'
AND RECTYPE = 0
-- Key 1.
AND EXISTS
(SELECT 'X'
FROM sysadm.PSKEYDEFN B
WHERE B.RECNAME = A.RECNAME
AND B.FIELDNAME = 'GUR_PBLCTN_REF_NR')
-- Key 2.
AND EXISTS
(SELECT 'X'
FROM sysadm.PSKEYDEFN B
WHERE B.RECNAME = A.RECNAME
AND B.FIELDNAME = 'EFFDT')


SQL - Multiple rows into one. Transpose SQL Results.

I've got Table.Person and Table.Pet. Each person has multiple pets.

How do I return one row for each person with all their pets comma delimited in one column?

SELECT
PERSON_ID
, TRIM(',' FROM TRIM(' ' FROM
MAX(DECODE(SEQ, 1, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 2, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 3, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 4, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 5, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 6, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 7, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 8, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 9, PET_NAME || ', ')) ||
MAX(DECODE(SEQ, 10, PET_NAME || ', '))
)) AS PET_NAMES
FROM
(SELECT
A.PERSON_ID
, A.NAME AS PERSON_NAME
, B.NAME AS PET_NAME
, ROW_NUMBER() OVER (PARTITION BY PERSON_ID ORDER BY PERSON_ID DESC) SEQ
FROM
PERSON A
, PET B
WHERE B.PERSON_ID = A.PERSON_ID)
GROUP BY PERSON_ID

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.