Monday 26 November 2007

How to setup the Direct Reports for Manager?

One of the Key for Manager Self Service transactions is setting up of Direct Reports for Manager.
For every MSS Transaction, The First page is always Direct Reports Listing Page. Generally this list is determined by Organizational Hierarchy Setup. (For Eg: Departmental Hierarchy or Supervisor driven Hierarchy). If we correlate with PeopleSoft Terminology, I would say this will come under any of these access types
  1. By Supervisor Id
  2. By Reports To Position
  3. By Department Manager Id
  4. By Department Security Tree
  5. By Part Position Management Supervisor
  6. By Part Position Management Department Manager ID
  7. By Group Id
If we take access type“By Reports To Position”, it means Organizational Hierarchy is determined by the Employee’s Reporting Position Number.
In PeopleSoft, this Direct Reports Listing is constructed by 2 types of Setups
Direct Reports Setup
Dir1
 Configure Direct Reports UI






Dir2 


Configure” Button is used to setup the Access Type and the fields to be displayed on the Direct Reports Listing page.
Dir3
Note: Configure Direct Reports UI is the new feature provided in PeopleSoft HCM 8.9 Version onwards.
In order to verify the above mentioned setups, PeopleSoft itself provided options to validate the setups. This is done by using Invoke Direct Reports API – This is used to view the Direct Reports of the Manager
Invoke Direct Reports UI API – This is used to view the MSS Transaction pages for a particular MSS Component
Note: Navigation for these setups:
Setup HRMS >> Common Definition >> Direct Reports for Manager

Read More About  Direct Reports for Manager

Friday 23 November 2007

How to delete/purge OPRID outside PS?

Peoplesoft provides an online page to delete OPRID. However, this can become cumbersome if you need to purge or delete 10+ OPRID’s as part of your cleanup exercise. I have created the below PL/SQL script which basically mimics what the online purge process executes.
DECLARE
CURSOR C1 IS
SELECT decode(A.SQLTABLENAME,’ ‘,’PS_’||A.RECNAME,A.SQLTABLENAME) RECNAME
FROM PSRECDEFN A
WHERE RECNAME IN (SELECT RECNAME FROM PS_TBLSELECTION_VW)
AND RECNAME != ‘PSOPRDEFN’
ORDER BY 1;
CURSOR C2 IS
SELECT decode(B.SQLTABLENAME,’ ‘,’PS_’||B.RECNAME,B.SQLTABLENAME) RECNAME
FROM PSRECDEFN B
WHERE
RECNAME IN (SELECT RECNAME FROM PS_ROLEUSR_TBLS_VW)
AND RECNAME != ‘PSOPRDEFN’

Read More About OPRID

Thursday 15 November 2007

How to be any OPRID, if

Here is the scenario…
  • You do not have access to PeopleSoft Security Administration pages to reset the password.
  • You want to log on to PeopleSoft using your id and you have forgotten your password or you want to log on as some other operator id (without being detected).
Hmmm…
This is possible “IF” (the BIG IF)
  • You are the DBA
  • You are not the DBA but your generous DBA has provided you SELECT and UPDATE access to PS tools tables
Here is an example.
1.  I want to logon to F89XXXX but I do not remember my password. I sent an email to my security admin who is busy working on other priority issues.
SYSADM@f89xxxx > select oprid, operpswd from psoprdefn where oprid = ‘NPAI’;
OPRID OPERPSWD
—————————— ——————————–
NPAI 2bc+5cdNDqN4to33X9hP98N97+k=
2.  I remember my password in F89NNNN. So I query my operator password in that db.
SYSADM@f89xxxx > select oprid, operpswd from psoprdefn@f89nnnn where oprid = ‘NPAI’;
OPRID OPERPSWD
—————————— ——————————–
NPAI VLmLlgq9QsvCkDvRoqLnvhn8SkM=
3.  Execute the below Update SQL
SYSADM@f89xxxx > update psoprdefn set operpswd = ‘VLmLlgq9QsvCkDvRoqLnvhn8SkM=’ where oprid = ‘NPAI’;
1 row updated.
SYSADM@f89xxxx > commit;
Commit complete.
4.Voilà!! I am now able to log on to F89XXXX using my password that I remember from F89NNNN.
You can follow similar steps to log on using any other operator id. You can update it back to the original value and go undetected unless it’s being audited.
Conclusion
For the DBA:
  • Do not provide ANY level of access to PS tools tables to ANY user in ANY environment
  • PROTECTPSOPRDEFN and other security tools tables
  • Consider HIDING OPERPSWD column using a view or restrict access to PSOPRDEFN using FGAC
  • AUDIT all security related tools tables.
For others:
  • Do not attempt this in your environment!

Read More About OPRID

Monday 5 November 2007

How to determine what is being processed?

As a DBA you might be receiving a lot of calls from users asking some of the questions listed below.
- What their process is currently executing?
- How long will it take?
- Should I cancel my process?
In this post, I will cover some steps that will help you quickly respond to the above questions.
I have executed the below SQR Report in my environment and want to know what is happening.

Identify_sql_1_2
What their process is currently executing?
1.Use the below SQL to correctly identify the Oracle session
SELECT CLIENT_INFO, PROGRAM, SID, SERIAL#, SQL_ADDRESS FROM V$SESSION WHERE CLIENT_INFO LIKE ‘NPAI%’ AND STATUS =‘ACTIVE’;
Identify_sql_2_2
2.The above results identify the session executing the report; in this case it is SID 32.
3.Use this SID to identify the SQL being executed.
SELECT SQL_TEXT FROM V$SQLTEXT WHERE ADDRESS = (SELECT SQL_ADDRESS FROM V$SESSION WHERE SID= 32) ORDER BY PIECE;
SQL_TEXT
—————————————————————-
SELECT A.RECNAME, A.FIELDNAME FROM PSRECFIELD A WHERE A.SUBRECO
RD <> ‘Y’ AND NOT EXISTS (SELECT ‘X’ FROM PSDBFIELD B WHERE B.FI
ELDNAME = A.FIELDNAME)
4.The above results will provide you the SQL that is currently being executed. Equipped with this information you SHOULD be able to dig deeper if the session continues to execute the same SQL for more than expected time. Some of the scenarios could be:
a.Locked rows
b.Bad SQL
c.Statistics not updated
d.Bad DB configuration – SQL waiting on some event
e.Missing index, etc
Note 1
Also, there are scenarios where the session might not be executing any SQL because it is busy executing the code logic within the program. If the program is written using good coding standards then you should be able to look at the log file to decipher the progress.
Note 2
It is possible that there are no Oracle sessions created by the process. Some of the scenarios are:
- Process is Queued
- Process has completed and is Posting, etc
How long will it take?In most scenarios the functional support personnel along with the developer should be able to respond to this question based on the SQL or results in log file that you have identified. If you have knowledge about this process based on its history and/or your ability to interpret the program then you can estimate the completion time.
Should I cancel my process?This decision will require approval from the business + functional support personnel unless you have knowledge about the impact of the process.
Read More About  what is being processed?

Friday 2 November 2007

Calling Oracle DB Function from PS Query Manager

A few weeks back a developer posed a question“Can I use an Oracle Function in PS Query Manager?”
Yes, you can and here are the steps.
1.Create your function in Oracle DB
CREATE OR REPLACE FUNCTION TEMP_FUNC(VAR1 IN NUMBER)
RETURN VARCHAR2
IS VAR2 VARCHAR2(48);
BEGIN
SELECT NVL(PROGRAM,’NULL’)
INTO VAR2
FROM V$SESSION
WHERE AUDSID=USERENV(‘SESSIONID’);
RETURN(VAR2);
END;
2.Create a view in PeopleSoft
Oracle_func_ps_qry_1
CREATE VIEW PS_TEMP_VW AS SELECT TEMP_FUNC(10) VARIABLE_NAME FROM DUAL;
3.That’s it! Use your view in Query Manager as shown below.
Oracle_func_ps_qry_2
Oracle_func_ps_qry_3 
Read More About  PS Query Manager