Header Ads

  • Recent Posts

    Finding BARNAME, ITEMNAME and all about PSAUTHITEM table

    When we know a PeopleSoft Page and Component name we could find the Menu name from 'Find Definition References'. But if the menu has too many items it requires tremendous patience to find out the online navigation, bar name, item name. Of course with experience this does not take a long. To find out the navigation of the PeopleSoft Page\Component we can use SQL queries and the details are available in one of my previous posts: SQL Query to find navigation of PeopleSoft Components

    However, when we do page transfers through peoplecode we need the BAR name and ITEM name. Finding these values are manual and the time taken depends on the prior experience of the developer.

    PSAUTHITEM table provides a way to find out the menu name, bar name, item name and page name from the following fields respectively MENUNAME, BARNAME, ITEMNAME, PAGE. Once we know the page name and menu name we can query the PSAUTHITEM table and find out the BAR name and ITEM name. This query would returns results for all permission lists associated with the menu item:

    SELECT *
    FROM
     PSAUTHITEM
    WHERE
     MENUNAME = ‘MENUNAME’
     AND PNLITEMNAME = ‘PAGENAME’;


    PSAUTHUITEM table contains these fields:
    • CLASSID
    • MENUNAME
    • BARNAME
    • BARITEMNAME
    • PNLITEMNAME
    • DISPLAYONLY
    • AUTHORIZEDACTIONS
    CLASSID is nothing but the Permission list. MENUNAME, BARNAME, BARITEMNAME and PNLITEMNAME are all key fields on the PSMENUITEM table. When we navigate to PeopleSoft pages via the Portal Registry, the Menu structure is used for page security.

    The following actions(%Action): are available for accessing a PeopleSoft component:

    • A - Add
    • U - Update
    • L - Update/Display All
    • C - Correction
    • E - Data entry
    One important point while doing Page Transfers is to make sure that the users clicking on the Page Transfer link must have the appropriate action available on the target page being transferred to. AUTHORIZEDACTIONS field in the PSAUTHITEM table will provide information on which authorized actions correspond to which permission lists. This can provide valuable inputs while doing page transfers.

    PSAUTHITEM table primarily provides information on which pages and authorized actions are allowed for assigned Permission Lists, but it tracks web library (iScript) permissions also.

    Below are few Oracle SQL queries which I found long back from another blog through google search.
    SQL Query 1:
    SELECT
     A.MENUNAME,
     B.PNLGRPNAME,
     A.PNLITEMNAME,
     A.BARNAME,
     A.BARITEMNAME,
     A.AUTHORIZEDACTIONS
    FROM
     PSAUTHITEM A,
     PSMENUITEM B
    WHERE
     A.MENUNAME = B.MENUNAME
     AND A.BARNAME = B.BARNAME
     AND A.BARITEMNAME = B.ITEMNAME
     AND A.CLASSID = 'ALLPAGES'

    SQL Query 2:
    Here’s a query written in Oracle 10G that splits AUTHORIZEDACTIONS into a series of Yes/No fields for Add, Update/Display, UpdateDisplay All, and Correction:
    SELECT
     CLASSID,
     MENUNAME,
     BARNAME,
     BARITEMNAME,
     PNLITEMNAME,
     DISPLAYONLY,
     CASE WHEN BITAND(AUTHORIZEDACTIONS,1) > 0 THEN 'Y' ELSE 'N' END ADDACTION,
     CASE WHEN BITAND(AUTHORIZEDACTIONS,2) > 0 THEN 'Y' ELSE 'N' END UPDATEACTION,
    CASE WHEN BITAND(AUTHORIZEDACTIONS,4) > 0 THEN 'Y' ELSE 'N' END UPDATEALLACTION,
    CASE WHEN BITAND(AUTHORIZEDACTIONS,8) > 0 THEN 'Y' ELSE 'N' END CORRECTIONACTION,
    CASE WHEN AUTHORIZEDACTIONS > 15 THEN 'Y' ELSE 'N' END SPECIAL
    FROM
     PSAUTHITEM
    WHERE
     CLASSID = 'ALLPAGES'

    Authorized Actions:

    1: Add
    2: Update Display
    3: Add/Update/Display
    4: Update/Display All
    5: Add/Update/Display All
    6: Update/Display/Update/Display All
    7: Add/Update/Display/Update/Display All
    8: Correction
    9: Add/Correction
    10: Update/Display / Correction
    11: Add/Update/Display/Correction
    12: Update/Display All/Correction
    13: Add/Update/Display All/Correction
    14: Update/Display/Update/Display All/Correction
    15: Add/Update/Display/Update/Display All/Correction
    128: Data Entry

    Each possible authorized action has a corresponding decimal value. For all the authorized actions for a given menu item for a given operator, these numbers are added together and stored in the authorizedactions column.
    There are two more useful SQLs I got from the same article or blog(I do not remember where I got these and thanks to the author). These queries are for SQL server as bitAnd does not work.

    SQL Query 3: SELECT
     CLASSID,
     MENUNAME,
     BARNAME,
     BARITEMNAME,
     PNLITEMNAME,
     DISPLAYONLY,
     CASE WHEN BITAND(AUTHORIZEDACTIONS,1) > 0 THEN 'Y' ELSE 'N' END ADDACTION,  CASE WHEN BITAND(AUTHORIZEDACTIONS,2) > 0 THEN 'Y' ELSE 'N' END) UPDATEACTION,
     CASE WHEN BITAND(AUTHORIZEDACTIONS,4) > 0 THEN 'Y' ELSE 'N' END UPDATEALLACTION,
     CASE WHEN BITAND(AUTHORIZEDACTIONS, > 0 THEN 'Y' ELSE 'N' END CORRECTIONACTION,
     CASE WHEN AUTHORIZEDACTIONS > 15 THEN 'Y' ELSE 'N' END SPECIAL
    FROM
     PSAUTHITEM
    WHERE
     CLASSID = 'ALLPAGES'

    SQL Query 4: SELECT
     CLASSID,
     MENUNAME,
     BARNAME,
     BARITEMNAME,
     PNLITEMNAME,
     CASE WHEN (DISPLAYONLY & 1) > 0 THEN 'Y' ELSE 'N' END DISPLAYONLY,
     CASE WHEN (CAST(AUTHORIZEDACTIONS AS INT) & 1) > 0 THEN 'Y' ELSE 'N' END ADDACTION,
     CASE WHEN (CAST(AUTHORIZEDACTIONS AS INT) & 2) > 0 THEN 'Y' ELSE 'N' END UPDATEACTION,
     CASE WHEN (CAST(AUTHORIZEDACTIONS AS INT) & 4) > 0 THEN 'Y' ELSE 'N' END UPDATEALLACTION,
     CASE WHEN (CAST(AUTHORIZEDACTIONS AS INT) & > 0 THEN 'Y' ELSE 'N' END CORRECTIONACTION,
     CASE WHEN CAST(AUTHORIZEDACTIONS AS INT) > 15 THEN 'Y' ELSE 'N' END SPECIAL FROM
     PSAUTHITEM NOLOCK
    WHERE
     CLASSID = 'ALLPAGES'

    1 comment:

    1. Thank you very much for this. I found this information to be very helpful.

      ReplyDelete

    Please refrain for marketing messages and unnecessary back links.

    Post Top Ad

    Post Bottom Ad