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:
PSAUTHITEM
WHERE
MENUNAME = ‘MENUNAME’
AND PNLITEMNAME = ‘PAGENAME’;
PSAUTHUITEM table contains these fields:
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'
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’;
- CLASSID
- MENUNAME
- BARNAME
- BARITEMNAME
- PNLITEMNAME
- DISPLAYONLY
- AUTHORIZEDACTIONS
The following actions(%Action): are available for accessing a PeopleSoft component:
- A - Add
- U - Update
- L - Update/Display All
- C - Correction
- E - Data entry
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'
Thank you very much for this. I found this information to be very helpful.
ReplyDelete