How to determine the PeopleSoft navigation?

While working on my previous projects, I had some difficulties identifying the menu navigation for a process or report in PeopleSoft. So I developed this SQL that will help determine the navigation.

select distinct b.menugroup||’ > ‘ ||b.menulabel||

‘ > ‘||barlabel||’ > ‘||a.itemlabel||’ > ‘

||d.pnlgrpname||’ – ‘||prcstype||’ – ‘||prcsname“Navigation”

from psmenuitem a, psmenudefn b, ps_prcsdefnpnl c, pspnlgroup d

where a.menuname=b.menuname

and a.pnlgrpname = c.pnlgrpname

and a.pnlgrpname = d.pnlgrpname

and prcsname like upper(‘&prcsname’)

group by b.menuname, b.menugroup, b.menulabel, barlabel, a.itemlabel,

d.pnlgrpname, prcstype, prcsname

/

Navigation
—————————————————————————-

Mana&ge Assets > &Interface Asset Information > &Process > &Transaction Loader > RUN_AMIF1000 – Application Engine – AMIF1000

Read More about Peoplesoft Navigation

Adding HINT to PeopleSoft Query using Query Manager

As an Oracle DBA administering a PeopleSoft database, we would like to ensure that all PeopleSoft queries (PRIVATE or PUBLIC) are optimized. There is always a possibility of introducing a HINT to tune the SQL generated by PeopleSoft in Query Manager. Here are the steps to add the HINT.

1. Create a new expression
2. Click“Add Field” and select the first field that is used in the query

3. Add your HINT before the field name in the expression

4. Click Ok
5. Select the Expression as a field in the query and move it as the first column
6. Deselect the first field A.SETID that has now moved to be the second column

7. Now, you have a query ready with your chosen HINT

Scenario with DISTINCTIf you have chosen DISTINCT in the query properties then your SQL will appear as shown below.

Oracle will ignore the HINT in this case because the DISTINCT precedes the HINT. To correct this behavior follow the below steps.

1. From the query properties uncheck “DISTINCT”

2. Edit the expression holding the HINT and add DISTINCT as shown below.

3. Now the query SQL will appear corrected.

 

Read More about  Peoplesoft

Demystifying PSPROJECTITEM -Part 2

Continuing from my previous post, here are the steps to load the upg.sqc data relevant for our manipulation into the Oracle database.

1. Create record HX_COMP_TBL in App Designer as shown below.

2. Build/create the table
3. Add it to Query Security if you need to access it using PeopleSoft Query Manager.
4. Create Download upg_sqc.txt file
5. Create SQL*Loader control file as shown below.
load data
infile ‘/export/home/npai/psprojectitem/upg_sqc.txt’
into table PS_HX_CMP_TBL
fields terminated by ” ” optionally enclosed by ‘”‘
(OBJ_DESCR2, OBJNAME, VAL1)
6. Execute sqlldr command to load the file
sqlldr sysadm/sysadm@ftest control=sqlld.ctl
7. Use it in query through Query Manager or SQLPLUS

Read More about  Peoplesoft

Demystifying PSPROJECTITEM – Part 1

As a DBA, I do not always trust Application Designer when it comes to following tasks.

• Merging projects – typical example is migration of 2 or more bundles using a merged project.
• Quickly changing the update flags based on KEEP/DROP decisions on a huge project
• Any other manipulations

Interpreting Data in PSPROJECTITEM can help you get one step closer to sailing through some of the above mentioned problems.
The following columns provided me with significant information to understand the data in PSPROJECTITEM table.

Column Name

PROJECTNAME

OBJECTTYPE

OBJECTVALUE1

OBJECTVALUE2

OBJECTVALUE3

OBJECTVALUE4

SOURCESTATUS

TARGETSTATUS

UPGRADEACTION

TAKEACTION

COPYDONE

The translation for OBJECTTYPE, SOURCESTATUS, TARGETSTATUS, and UPGRADEACTION are not present in the PeopleSoft database (unlike application translates which are available in PSXLATITEM). The translation for these columns exists in delivered upg.sqc.

In my next post, I will share with you a technique I use to quickly decode the data instead of having to refer back to upg.sqc.

How to monitor PeopleSoft process scheduler for availability?

There are many approaches to monitor the PeopleSoft process scheduler. One of the options that come to mind is to have a script running on the OS hosting the process scheduler to monitor the OS process. But having process schedulers running on multiple OS (PSUNX and PSNT) will need development of scripts running on both servers.

My preferred method is to use the below SQL to monitor all the process schedulers running on the database.

SELECT A.SERVERNAME, B.XLATSHORTNAME,B.FIELDNAME,B.FIELDVALUE,TO_CHAR(B.EFFDT,’YYYY-MM-DD’)

FROM PSSERVERSTAT A, PSXLATITEM B

WHERE B.EFFDT =

(SELECT MAX(B_ED.EFFDT) FROM PSXLATITEM B_ED

WHERE B.FIELDNAME = B_ED.FIELDNAME

AND B.FIELDVALUE = B_ED.FIELDVALUE

AND B_ED.EFFDT <= SYSDATE)

AND B.FIELDNAME = ‘SERVERSTATUS’

AND B.FIELDVALUE = A.SERVERSTATUS

Depending on the results of the above SQL you can trigger an email notification or any other form of action.

Read More about  Peoplesoft