Header Ads

  • Recent Posts

    Querying PSPROJECTITEM data and extracting it to spreadsheet

    While doing PeopleSoft upgrades or during migration of major projects, the compare reports provided by PeopleSoft cannot be exported easily into Spreadsheets for detailed analysis. In order to get the relevant data of such projects, we need to understand the PSPROJECTITEM table and all its associated fields. Once we know what values are stored in OBJECTTYPE, SOURCESTATUS, TARGETSTATUS, UPGRADEACTION, TAKEACTION and COPYDONE fields in PSPROJECTITEM we can achieve our objective easily. Then we can easily get the required data(with description of above fields instead of values) from database and thereby export the data to Excel Sheets for further analysis.

    Here are couple of my posts on PSPROJECTITEM Part 1 and Part 2 which will help in understanding PSPROJECTITEM record.

    Below is the SQL which fetches all the field values in PSPROJECTITEM and provide data in a meaningful way with appropriate descriptions for any project. This SQL will be particularly useful in PeopleSoft upgrades while comparing latest DEMO environment and current copy of Production environment which will be running in a prior application release.

    SELECT
    (CASE WHEN A.OBJECTTYPE = 0 Then 'Record'
    WHEN A.OBJECTTYPE = 1 Then 'Index'
    WHEN A.OBJECTTYPE = 2 Then 'Field'
    WHEN A.OBJECTTYPE = 3 Then 'Field Format'
    WHEN A.OBJECTTYPE = 4 Then 'Translate Value'
    WHEN A.OBJECTTYPE = 5 Then 'Pages'
    WHEN A.OBJECTTYPE = 6 Then 'Menus'
    WHEN A.OBJECTTYPE = 7 Then 'Components'
    WHEN A.OBJECTTYPE = 8 Then 'Record PeopleCode'
    WHEN A.OBJECTTYPE = 9 Then 'Menu PeopleCode'
    WHEN A.OBJECTTYPE = 10 Then 'Query'
    WHEN A.OBJECTTYPE = 11 Then 'Tree Structures'
    WHEN A.OBJECTTYPE = 12 Then 'Trees'
    WHEN A.OBJECTTYPE = 13 Then 'Access group'
    WHEN A.OBJECTTYPE = 14 Then 'Color'
    WHEN A.OBJECTTYPE = 15 Then 'Style'
    WHEN A.OBJECTTYPE = 16 Then 'N/A'
    WHEN A.OBJECTTYPE = 17 Then 'Business process'
    WHEN A.OBJECTTYPE = 18 Then 'Activity'
    WHEN A.OBJECTTYPE = 19 Then 'Role'
    WHEN A.OBJECTTYPE = 20 Then 'Process Definition'
    WHEN A.OBJECTTYPE = 21 Then 'Server Definition'
    WHEN A.OBJECTTYPE = 22 Then 'Process Type Definition'
    WHEN A.OBJECTTYPE = 23 Then 'Job Definitions'
    WHEN A.OBJECTTYPE = 24 Then 'Recurrence Definition'
    WHEN A.OBJECTTYPE = 25 Then 'Message Catalog'
    WHEN A.OBJECTTYPE = 26 Then 'Dimension'
    WHEN A.OBJECTTYPE = 27 Then 'Cube Definitions'
    WHEN A.OBJECTTYPE = 28 Then 'Cube Instance Definitions'
    WHEN A.OBJECTTYPE = 29 Then 'Business Interlink'
    WHEN A.OBJECTTYPE = 30 Then 'SQL'
    WHEN A.OBJECTTYPE = 31 Then 'File Layout Definition'
    WHEN A.OBJECTTYPE = 32 Then 'Component Interfaces'
    WHEN A.OBJECTTYPE = 33 Then 'AE program'
    WHEN A.OBJECTTYPE = 34 Then 'AE section'
    WHEN A.OBJECTTYPE = 35 Then 'Message Node'
    WHEN A.OBJECTTYPE = 36 Then 'Message Channel'
    WHEN A.OBJECTTYPE = 37 Then 'Message'
    WHEN A.OBJECTTYPE = 38 Then 'Approval rule set'
    WHEN A.OBJECTTYPE = 39 Then 'Message PeopleCode'
    WHEN A.OBJECTTYPE = 40 Then 'Subscription PeopleCode'
    WHEN A.OBJECTTYPE = 41 Then 'N/A'
    WHEN A.OBJECTTYPE = 42 Then 'Component Interface PeopleCode'
    WHEN A.OBJECTTYPE = 43 Then 'AE PeopleCode'
    WHEN A.OBJECTTYPE = 44 Then 'Page PeopleCode'
    WHEN A.OBJECTTYPE = 45 Then 'Page Field PeopleCode'
    WHEN A.OBJECTTYPE = 46 Then 'Component PeopleCode'
    WHEN A.OBJECTTYPE = 47 Then 'Component Record PeopleCode'
    WHEN A.OBJECTTYPE = 48 Then 'Component Rec Fld PeopleCode'
    WHEN A.OBJECTTYPE = 49 Then 'Image'
    WHEN A.OBJECTTYPE = 50 Then 'Style sheet'
    WHEN A.OBJECTTYPE = 51 Then 'HTML'
    WHEN A.OBJECTTYPE = 52 Then 'Not used'
    WHEN A.OBJECTTYPE = 53 Then 'Permission List'
    WHEN A.OBJECTTYPE = 54 Then 'Portal Registry Definitions'
    WHEN A.OBJECTTYPE = 55 Then 'Portal Registry Structures'
    WHEN A.OBJECTTYPE = 56 Then 'URL Definitions'
    WHEN A.OBJECTTYPE = 57 Then 'Application Packages'
    WHEN A.OBJECTTYPE = 58 Then 'Application Package Peoplecode'
    WHEN A.OBJECTTYPE = 59 Then 'Portal Registry User Homepage'
    WHEN A.OBJECTTYPE = 60 Then 'Problem Type'
    WHEN A.OBJECTTYPE = 61 Then 'Archive Templates'
    WHEN A.OBJECTTYPE = 62 Then 'XSLT'
    WHEN A.OBJECTTYPE = 63 Then 'Portal Registry User Favorite'
    WHEN A.OBJECTTYPE = 64 Then 'Mobile Page'
    WHEN A.OBJECTTYPE = 65 Then 'Relationships'
    WHEN A.OBJECTTYPE = 66 Then 'Component Interface Property Peoplecode'
    WHEN A.OBJECTTYPE = 67 Then 'Optimization Models'
    WHEN A.OBJECTTYPE = 68 Then 'File References'
    WHEN A.OBJECTTYPE = 69 Then 'File Type Codes'
    WHEN A.OBJECTTYPE = 70 Then 'Archive Object Definitions'
    WHEN A.OBJECTTYPE = 71 Then 'Archive Templates (Type 2)'
    WHEN A.OBJECTTYPE = 72 Then 'Diagnostic Plug In'
    WHEN A.OBJECTTYPE = 73 Then 'Analytic Model'
    ELSE TO_CHAR(A.OBJECTTYPE) END) AS OBJECTTYPE,
     (CASE WHEN A.OBJECTID4 <> 0 THEN
        (A.OBJECTVALUE1 || ' -> ' || A.OBJECTVALUE2 || ' -> ' || A.OBJECTVALUE3 || ' -> ' || A.OBJECTVALUE4)
     ELSE (CASE WHEN A.OBJECTID3 <> 0 THEN
        (A.OBJECTVALUE1 || ' -> ' || A.OBJECTVALUE2 || ' -> ' || A.OBJECTVALUE3)
     ELSE (CASE WHEN A.OBJECTID2 <> 0 THEN
        (A.OBJECTVALUE1|| ' -> ' || A.OBJECTVALUE2)
     ELSE A.OBJECTVALUE1 END) END )END) AS OBJECTVALUE,
    (CASE WHEN A.SOURCESTATUS = 0 THEN 'Unknown'
            WHEN A.SOURCESTATUS = 1 THEN 'Absent'
            WHEN A.SOURCESTATUS = 2 THEN 'Changed'
            WHEN A.SOURCESTATUS = 3 THEN 'Unchanged'
            WHEN A.SOURCESTATUS = 4 THEN '*Changed'
            WHEN A.SOURCESTATUS = 5 THEN '*Unchanged'
            WHEN A.SOURCESTATUS = 6 THEN 'Same'
            ELSE TO_CHAR(A.SOURCESTATUS) END) AS SOURCESTATUS ,
    (CASE WHEN A.TARGETSTATUS = 0 THEN 'Unknown'
            WHEN A.TARGETSTATUS = 1 THEN 'Absent'
            WHEN A.TARGETSTATUS = 2 THEN 'Changed'
            WHEN A.TARGETSTATUS = 3 THEN 'Unchanged'
            WHEN A.TARGETSTATUS = 4 THEN '*Changed'
            WHEN A.TARGETSTATUS = 5 THEN '*Unchanged'
            WHEN A.TARGETSTATUS = 6 THEN 'Same'
            ELSE TO_CHAR(A.TARGETSTATUS) END) AS TARGETSTATUS,
    (CASE WHEN A.UPGRADEACTION = 0 THEN 'Copy'
            WHEN A.UPGRADEACTION = 1 THEN 'Delete'
            WHEN A.UPGRADEACTION = 2 THEN 'None'
            WHEN A.UPGRADEACTION = 3 THEN 'CopyProp'
            ELSE TO_CHAR(A.UPGRADEACTION) END)AS UPGRADEACTION,
    (CASE WHEN A.TAKEACTION = 1 THEN 'Yes'
          WHEN A.TAKEACTION = 0 THEN 'No'
          ELSE TO_CHAR(A.TAKEACTION) END) AS TAKEACTION,
    (CASE WHEN A.COPYDONE = 1 THEN 'Yes'
          WHEN A.COPYDONE = 0 THEN 'No'
          ELSE TO_CHAR(A.COPYDONE) END) AS COPYDONE
    FROM PSPROJECTITEM A WHERE A.PROJECTNAME = :1 -- Project name to be analyzed.

    4 comments:

    Please refrain for marketing messages and unnecessary back links.

    Post Top Ad

    Post Bottom Ad