How to execute OS Commands in SQR?

Most of the projects have the below mentioned scenarios…..

1. FTPing the file from one server location to another
2. Deleting the file

We can simply execute those in SQR by using CALL SYSTEM built-in…Here is the syntax

CALL SYSTEM Using $del_file #del_status Wait

if edit(#del_status,’9′) = ‘0′
show ‘intfiles.txt was deleted sucessfully’
end-if

$del_file – Specifies the operating system command to execute. The command can be a quoted string, string variable, or column.
#del_status – Represents the status of execution of OS Command. This is always going to be Numeric variable
UNIX/Linux: Zero (0) indicates success. Any other value is the system error code.
PC/ Microsoft Windows: A value less than 32 indicates an error.
Wait – This flag is used to enable to do multitask processing.

(Microsoft Windows only): WAIT specifies that SQR suspend its execution until the CALL SYSTEM command has finished processing. NOWAIT specifies that SQR start the CALL SYSTEM command but continue its own processing while that command is in progress.

For Microsoft Windows, the default is NOWAIT. On UNIX\Linux operating systems the behavior is always WAIT.

Note : While executing OS Commands, we should append the value of Command Environment Settings along with OS Command. This is done by !Command Environment Settings
let $comspec = getenv(‘COMSPEC’) /* For Window OS */
let $comspec = getenv(‘SHELL’) /* For UNIX */
let $del_file = $comspec||’ /c del’||$int_file_name

Read More About  How To Execute OS Commands

How to trace AE and determine which AE has trace enabled?

In this blog, I will answer two questions.

1. How to trace AE?
2.  

There are a few ways to trace an AE program. You can enable the trace using some of the below methods:

1. In the process scheduler configuration file psprcs.cfg
2. You can enable the trace in the“Process Definition”.
3. Grab the Oracle session information and generate SQL Trace
4.  

It is not advised to enable the trace using psprcs.cfg because it will enable trace for ALL AE programs using that scheduler. However, this might be the best choice in an environment where you want to trace multiple AE programs or you do not have access to modify the process definition. Exercise this option with caution knowing that it might generate trace files for all AE programs.

To enable trace using “Process Definition”, make the below change. You can use the “-TRACE” or “-TOOLSTRACESQL” or “-TOOLSTRACEPC” option depending on what information you require.

1.  
2. Which AE program has trace enabled in its process definition?
3.  

The below SQL will help you determine all the AE programs that have TRACE enabled. It will be useful to use this SQL in your monitoring scripts if you notice developers enabling trace using the process definitions and getting the program with its process definition migrated all the way to production or if your analyst or DBA have the tendency to enable the trace and forget about it.

SELECT PRCSNAME, PARMLIST

FROM PS_PRCSDEFN

WHERE UPPER(PARMLIST) LIKE ‘%TRACE%’

AND PRCSTYPE = ‘Application Engine’;

Read More About How To Trace AE

How to create new Manager Self Service Transaction?

As everyone knows, PeopleSoft itself provides a lot of MSS transaction.

For example

1. Transfer Employee
2. Job Change
3. Promote Employee
4. Terminate Employee
5.  

Like that, say for example, if we are getting new MSS related requirements for Paygroup Change, How will we accomplish? Here is an insight!!!

Broadly saying, we should have 3 types of components that needs to be built to accomplish any MSS transaction

1. Transaction Level Components
2. Approval related Components (If Required)
3. Viewing Transaction/Approval Workflow Status related Components
4.  

Each of these components are in turn driven by 2 types of Component Pages, one for Launching Manager’s Direct Reports and the other one for Adding / Approving/ Viewing a (Paygroup change) Transaction.

In order to maintain all these transactional and approval workflow status related data, we should create 2 records to hold the data. As per PS Convention, One of the ways to maintain the data is achieved by having

1. HR_PAYGRPCHG_DAT – This record used to hold all the (Paygroup change related) Transactional data
2. HR_PAYGRPCHG_STA – This record used to hold all the Approval Workflow Status related data
3.  

Transactional Level Components

This type of components is purely meant for doing transactional changes. In our case, transactional change is nothing but Paygroup change for an employee.

1. Launching Page:
2. 
   Manager’s Direct Reports  Launching Component Page is constructed either by Direct Report Setup (Using OPRROWS component to show Direct Reports) or Configure Direct Reports UI Setup (Using HR_DR_SELECTION_UI component to show Direct Reports)
   Navigation: Setup HRMS >> Common Definition >> Direct Reports for Managers
3.  
4. Transaction Page:
5. 
   Transactional Component Page should be manually built as per the Requirements. As a manager, this is where Manager has to change the Paygroup of an employee, which in turn will fire Approval workflows (if required) or insert the data to Job component directly.
   Once the data is saved on this page, it should insert this transaction related data to DAT record and all the Approval workflow related data to STA record.
6.  

Approval related Components / Viewing Approval Workflow Status related Components

1. Launching Page:
2. 
   The Launching Page for both Approval / Viewing Status related Components should be manually designed to populate the Manager’s Direct Reports.
3. 
   Basically Launching pages are used to show the Direct Reports of the Manager. But in this case, Population of Manager’s Direct reports is restricted. For Eg. If the MSS transaction needs an approval, then the corresponding employee for that transaction will be shown as one of the Direct Reports in Launching page. Needless to say, Showing data for Manager’s Direct Reports should be determined by both Transactional (HR_PAYGRPCHG_DAT) and Status (HR_PAYGRPCHG_STA) data records.
4.  
5. Transaction Page:
6. 
   In the case of Approval related components, the main Approval page should be provided with 2 extra buttons to approve or deny the transaction. All other transactional data information should be placed in this page in non editable mode.
7. 
   For Viewing Statuses, the entire page should be in READ only access mode. This page should be used only to view the transaction and approval workflow status related details.

Read More About  Self Service Transaction

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

 Configure Direct Reports UI

 


Configure” Button is used to setup the Access Type and the fields to be displayed on the Direct Reports Listing page.

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

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

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