Peoplesoft Page is Not Showing in the Menu

One of the common problem that PS developers encounter is the PS page not showing in the menu after component registration. Here are the few steps that I am doing when this issue happens:

1. Make sure that it is successfully registered. In the application designer, you can see the logs whether it encountered an error or the component has been successfully registered. Another way to check is to verify it in PIA under the Structure and Content page. Navigation is Menu > PeopleTools > Portal > Structure and Content. Click to the folders/sub-folder until you find the component you registered. From there, you can verify the node, menu, permission list and if it is still valid (there are fields there indicating the validity of the portal). If Valid to date is null, this means that there's no expiration for this portal. If the page cannot be found in Structure and content, it is either not registered successfully or you do not have access to view it.

2. If you find all configurations or setup from Portal Structure and Content are correct and the link from the menu is still not visible, then you need to check the permission list. You can use the SQL below to verify if the specific component is attached to a permission list. You can also use this to see if a specific user has an access to it. You can play around with the criteria.

SELECT DISTINCT d.roleuser, 
                a.rolename, 
                b.classid                  AS PERMISSION_LIST, 
                b.menuname, 
                b.baritemname              AS "COMPONENT NAME", 
                b.pnlitemname, 
                c.descr                    AS "PAGE DESCRIPTION", 
                Decode(b.displayonly, 1, 'Y', 
                                      'N') AS "DISPLAY ONLY?", 
                e.navigation 
FROM   sysadm.psroleclass a, 
       sysadm.psauthitem b, 
       sysadm.pspnldefn c, 
       sysadm.psroleuser d, 
       (SELECT LEVEL0.portal_label 
               || ' > ' 
               || LEVEL1.portal_label 
               || ' > ' 
               || LEVEL2.portal_label 
               || ' > ' 
               || level3.portal_label AS navigation, 
               level3.portal_uri_seg2 AS component 
        FROM   psprsmdefn level3, 
               psprsmdefn level2, 
               psprsmdefn level1, 
               psprsmdefn LEVEL0 
        WHERE  level3.portal_prntobjname = level2.portal_objname 
               AND level2.portal_prntobjname = level1.portal_objname 
               AND level1.portal_prntobjname = LEVEL0.portal_objname 
               AND level3.portal_name = level2.portal_name 
               AND level2.portal_name = level1.portal_name 
               AND level1.portal_name = LEVEL0.portal_name) e 
WHERE  a.classid = b.classid 
       AND b.pnlitemname = c.pnlname 
       AND a.rolename = d.rolename 
       --AND d.roleuser = 'USER' 
       --AND b.baritemname = 'COMPONENT NAME' 
       --AND d.rolename = 'ROLENAME' 
       --AND b.classid = 'MENU NAME' 
       AND e.component = b.baritemname 
ORDER  BY 1, 
          2, 
          3, 
          4, 
          5, 

          6; 


3. Run the portal security synch process. Navigation is: Menu > PeopleTools > Portal > Portal Security Synch. Normally, PS Admin is the one who has access to run this process.

4. Clear server cache. Sometimes, we encounter this issue due to caching issue. We normally request for server bounce (Web server and App server).

Note: When you register a new component, make sure that all new folders used are included in the migration plus the existing folder where these new folders or new portal registry is stored.

PS Query: Execute only the criteria populated and ignore those that are blank/selective bind executed

Here's a simple tip for PS Query users. Have you ever had a problem where you need to show several prompts as criteria in your PS Query but user has an option to only populate few of them and ignore others?

For example:

I have set three fields as criteria (via prompt) in my PS Query namely EMPLID, HR_STATUS and DEPTID. If the user only populate any of these and retain other as blank, it should still pull the correct data. For instance, if the user only populated the EMPLID prompt, the query should only show all rows with this EMPLID. If user populated DEPTID, it will pull all data with this DEPTID regardless of the EMPLID and HR_STATUS. It's like the PS Query is ignoring the criteria without value entered by the user in the prompt field.

Here's an example of query on how to ignore those criteria that were not populated.

SELECT * 

FROM   PS_JOB 

WHERE  ( EMPLID = :1 

          OR TRIM(:1) IS NULL ) 

       AND ( HR_STATUS = :2 

              OR TRIM(:2) IS NULL ) 

       AND ( DEPTID = :3 

              OR TRIM(:3) IS NULL )

...MAX effdt

...MAX effseq 

Did it work? Please provide feedback if it worked or if you encounter problem.

Special Characters in XML for BI Publisher/XMLP

Have you already encountered the following error when generating XMLP report?

Caught exception: Error generating report output: (235,2309) PSXP_RPTDEFNMANAGER.ReportDefn.OnExecute Name:ProcessReport 

One of the reason why you are encountering this error is that there is a special character in the XML generated by your program that is being used by the BI Publisher.

Since PeopleSoft system currently supports only the UTF-8 for XML which does not accept special characters, here's a trick that you can use when generating the XML for XMLP/BI Publisher.

One encoding type for XML that accepts special character is  ISO-8859-1. Unfortunately, even if you set this as your encoding type, PS will still change it by default to UTF-8. If you are building your XML via peoplecode (CreateXML), the header of the XML will be just <?xml version="1.0"?> even if you put encoding type there.

The question now is how do you make the XML's encoding type to ISO-8859-1 so that it will accept the special characters and to avoid the error mentioned above?

What you can do is use the function GenFormatterXMLString() and replace manually the header from "<?xml version=""1.0""?>" TO "<?xml version=""1.0"" encoding=""ISO-8859-1""?>". Since your XML object has been converted into string, you can now use a string function to change the header of the XML. Once changed, you can now write that whole string (XML) to your file to generate the XML file. If you will open the file generated, it will now have the header <?xml version="1.0" encoding="ISO-8859-"?> which accepts special characters.

Sample: Local string &strXML = Substitute(&xmlDoc.GenFormattedXmlString(), "<?xml version=""1.0""?>", "<?xml version=""1.0"" encoding=""ISO-8859-1""?>"); &FILE.WriteString(&strXML); 

Peoplesoft: Project's Objects Audit SQL

Here's an SQL to audit objects from a project. You can also play around with the criteria if you are searching for a specific object only.

SELECT B.projectname, 

       objecttype, 

       ( CASE objecttype 

           WHEN 0 THEN 'Record' 

           WHEN 1 THEN 'Index' 

           WHEN 2 THEN 'Field' 

           WHEN 3 THEN 'Field Format' 

           WHEN 4 THEN 'Translate Value' 

           WHEN 5 THEN 'Pages' 

           WHEN 6 THEN 'Menus' 

           WHEN 7 THEN 'Components' 

           WHEN 8 THEN 'Record PeopleCode' 

           WHEN 9 THEN 'Menu PeopleCode' 

           WHEN 10 THEN 'Query' 

           WHEN 11 THEN 'Tree Structures' 

           WHEN 12 THEN 'Trees' 

           WHEN 13 THEN 'Access group' 

           WHEN 14 THEN 'Color' 

           WHEN 15 THEN 'Style' 

           WHEN 16 THEN 'N/A' 

           WHEN 17 THEN 'Business process' 

           WHEN 18 THEN 'Activity' 

           WHEN 19 THEN 'Role' 

           WHEN 20 THEN 'Process Definition' 

           WHEN 21 THEN 'Server Definition' 

           WHEN 22 THEN 'Process Type Definition' 

           WHEN 23 THEN 'Job Definitions' 

           WHEN 24 THEN 'Recurrence Definition' 

           WHEN 25 THEN 'Message Catalog' 

           WHEN 26 THEN 'Dimension' 

           WHEN 27 THEN 'Cube Definitions' 

           WHEN 28 THEN 'Cube Instance Definitions' 

           WHEN 29 THEN 'Business Interlink' 

           WHEN 30 THEN 'SQL' 

           WHEN 31 THEN 'File Layout Definition' 

           WHEN 32 THEN 'Component Interfaces' 

           WHEN 33 THEN 'AE program' 

           WHEN 34 THEN 'AE section' 

           WHEN 35 THEN 'Message Node' 

           WHEN 36 THEN 'Message Channel' 

           WHEN 37 THEN 'Message' 

           WHEN 38 THEN 'Approval rule set' 

           WHEN 39 THEN 'Message PeopleCode' 

           WHEN 40 THEN 'Subscription PeopleCode' 

           WHEN 41 THEN 'N/A' 

           WHEN 42 THEN 'Component Interface PeopleCode' 

           WHEN 43 THEN 'AE PeopleCode' 

           WHEN 44 THEN 'Page PeopleCode' 

           WHEN 45 THEN 'Page Field PeopleCode' 

           WHEN 46 THEN 'Component PeopleCode' 

           WHEN 47 THEN 'Component Record PeopleCode' 

           WHEN 48 THEN 'Component Rec Fld PeopleCode' 

           WHEN 49 THEN 'Image' 

           WHEN 50 THEN 'Style sheet' 

           WHEN 51 THEN 'HTML' 

           WHEN 52 THEN 'Not used' 

           WHEN 53 THEN 'Permission List' 

           WHEN 54 THEN 'Portal Registry Definitions' 

           WHEN 55 THEN 'Portal Registry Structures' 

           WHEN 56 THEN 'URL Definitions' 

           WHEN 57 THEN 'Application Packages' 

           WHEN 58 THEN 'Application Package Peoplecode' 

           WHEN 59 THEN 'Portal Registry User Homepage' 

           WHEN 60 THEN 'Problem Type' 

           WHEN 61 THEN 'Archive Templates' 

           WHEN 62 THEN 'XSLT' 

           WHEN 63 THEN 'Portal Registry User Favorite' 

           WHEN 64 THEN 'Mobile Page' 

           WHEN 65 THEN 'Relationships' 

           WHEN 66 THEN 'Component Interface Property Peoplecode' 

           WHEN 67 THEN 'Optimization Models' 

           WHEN 68 THEN 'File References' 

           WHEN 69 THEN 'File Type Codes' 

           WHEN 70 THEN 'Archive Object Definitions' 

           WHEN 71 THEN 'Archive Templates (Type 2)' 

           WHEN 72 THEN 'Diagnostic Plug In' 

           WHEN 73 THEN 'Analytic Model' 

           WHEN 74 THEN 'Not Used' 

           WHEN 75 THEN 'Java Portlet User Preferences' 

           WHEN 76 THEN 'WSRP Remote Producers' 

           WHEN 77 THEN 'WSRP Remote Portlets' 

           WHEN 78 THEN 'WSRP Cloned Portlet Handles' 

           WHEN 79 THEN 'Services' 

           WHEN 80 THEN 'Service Operations' 

           WHEN 81 THEN 'Service Operation Handlers' 

           WHEN 82 THEN 'Service Operation Version' 

           WHEN 83 THEN 'Service Operation Routings' 

           WHEN 84 THEN 'IB Queues' 

           WHEN 85 THEN 'BIP Template Definitions' 

           WHEN 86 THEN 'BIP Report Definitions' 

           WHEN 87 THEN 'BIP File Definitions' 

           WHEN 88 THEN 'BIP Data Source Definitions' 

           WHEN 89 THEN 'WSDL' 

           WHEN 90 THEN 'Message Schemas' 

           WHEN 91 THEN 'Connected Query Definition' 

           WHEN 92 THEN 'Logical Schemas' 

           WHEN 93 THEN 'XML Schemas' 

           WHEN 94 THEN 'Relational Schemas' 

           WHEN 95 THEN 'Dependency Documents' 

           WHEN 96 THEN 'Document Schema' 

           WHEN 97 THEN 'Essbase Cube Dimensions' 

           WHEN 98 THEN 'Essbase Cube Outlines' 

           WHEN 99 THEN 'Essbase Cube Connections' 

           WHEN 100 THEN 'Essbase Cube Templates' 

           WHEN 101 THEN 'Delimited Schemas' 

           WHEN 102 THEN 'Positional Schemas' 

           WHEN 103 THEN 'Application Data Set Definitions' 

           WHEN 104 THEN 'Test Definitions' 

           WHEN 105 THEN 'Test Framework Test Cases' 

           WHEN 106 THEN 'Application Data Set Bindings' 

           WHEN 107 THEN 'Feed Definitions' 

           WHEN 108 THEN 'Feed Categories' 

           WHEN 109 THEN 'Feed Data Type' 

           WHEN 110 THEN 'JSON Documents' 

           WHEN 111 THEN 'Related Content Definition' 

           WHEN 112 THEN 'Related Content Services' 

           WHEN 113 THEN 'Related Content Configurations' 

           WHEN 114 THEN 'Related Content Layouts' 

           WHEN 115 THEN 'Search Attributes' 

           WHEN 116 THEN 'Search Definitions' 

           WHEN 117 THEN 'Search Categories' 

           WHEN 118 THEN 'Search Contexts' 

           WHEN 119 THEN 'Integration Groups' 

           ELSE 'UNKNOWN OBJECT TYPE' 

         END )                            AS OBJECTTYPE_DESCR, 

       Rtrim(Rtrim(objectvalue1) 

             || '.' 

             || Rtrim(objectvalue2) 

             || '.' 

             || Rtrim(objectvalue3) 

             || '.' 

             || Rtrim(objectvalue4), '.') OBJECTNAME, 

       B.lastupddttm, 

       B.lastupdoprid 

FROM   sysadm.psprojectitem A, 

       sysadm.psprojectdefn B 

WHERE  A.projectname = B.projectname 

       --AND OBJECTVALUE1 = 'AY_LNREC_WRK' 

       --AND OBJECTVALUE2 = 'SAVE_BTN' 

       --AND OBJECTVALUE3 = 'FieldChange' 

       AND A.projectname = '&PROJECT NAME' 

ORDER  BY B.projectname, 

          objecttype, 

          objectvalue1, 

          objectvalue2, 

          objectvalue3;