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

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

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.

 

Read More About  PS Query Manager

Tuning Data Conversion AE during PeopleSoft Upgrade – I

In this post I will share a tuning tip that I used to improve the performance of the PeopleSoft Upgrade data conversion AE.

Data Conversion AE was truncating the temporary table -> Inserting 35 million rows into temporary table from transaction table -> Updating stats

The above steps combined took around 6hrs to complete. It ran in 30mins after the below changes to AE.

e dropped the index on temporary table

NSERT was modified to use APPEND hint

Recreated the index using NOLOGGING

Summary: PeopleSoft does not necessarily ship optimal code. We have to keep our eyes open to identify and modify the problem code to improve conversion performance

Read More About  Tuning Data Conversion

How to build an efficient interface using AE or SQR?

While building an Inbound/Outbound interface, most of the time we may be in dilemma whether to use Application Engine (AE) or SQR. Here are the tips to build an efficient interface using either AE or SQR.

If we use Application Engine, below points are to be taken into account

• If the interface is INBOUND, its better to go for Application Engine
• If the program fails, we can make use of Restart Control to restart the program from the position where it abends
• PeopleSoft – Business Process related Validation will be taken care automatically when we go for AE/CI.
• Parallel Processing can be easily achieved using AE. This can be done by using Temporary Tables and State Records.
• Reusability of SQLs is very much easy when we go for AE.
• Bulk Insertion of data and Set Processing of data can be easily done in an AE
• COMMIT levels can be maintained either at SECTION or STEP levels
• Generation of Reports using File Layout, Workflow related batch interface can be easily developed using an AE
• XML related processing is quiet easy when we go for AE
• When we go for Upgrade of the Current PS System, Retrofitting of AE is done by easily identifying the impacted objects.

If we are going to use SQR, below points can be taken into account to build an interface

• Formatting of Reports is very much easy in SQR using Begin-Header, Begin-Footer and Begin-Setup commands.
• If we are going to consider the Performance of the Interface Program, its better to go for SQR.
• Better Performance can be achieved by using Load-Lookup Arrays and Dynamic SQL
• Reusability of SQLs can be achieved by creating either generic Procedures or SQCs

Load Lookup Arrays in SQR

How to improve the performance of the SQR using Load Lookup Arrays?

For Simple database related validation, we will fire a SQL to check the sanity of data. If this data validation is to be fired for each and every row of data, then it is better to go for Load-Lookup Arrays. This is like Prompt table validation in the case of Online Pages.

In this way, we can reduce the database hits and thereby increasing the Performance of the SQR Program.

For Eg: If we want to validate the Earnings Codes present in the file, we can use these arrays to validate the Earning Code data.

Generally we will use the below SQL to validate the ERNCD data

SELECT PET.ERNCD, PET.EFFECT_ON_FLSA, PET.ADD_GROSS
FROM PS_EARNINGS_TBL PET
WHERE PET.EFFDT= (SELECT MAX (EFFDT) FROM PS_EARNINGS_TBL
WHERE PET.ERNCD = ERNCD
AND EFFDT <=(SYSDATE)

AND PET.EFF_STATUS =”A”

AND PET.ERNCD = $Erncd
In order to avoid these database hits for each and every row of data, we can the below code to load all the valid Earning Codes at once from the Database to Load Lookup Array Name EARN and then use the array to validate the input ERNCD data.

! Lookup array for EARNINGS CODES

Let $where = ‘PET.EFFDT = (SELECT MAX (EFFDT) ‘||

‘FROM PS_EARNINGS_TBL ‘||

‘WHERE PET.ERNCD = ERNCD ‘||

‘AND EFFDT <=SYSDATE) ‘||

‘AND EFF_STATUS =”A”’

LOAD-LOOKUP

NAME = EARN

TABLE = ‘PS_EARNINGS_TBL PET’

KEY = ERNCD

RETURN_VALUE = ERNCD||’-'||EFFECT_ON_FLSA||’-'||ADD_GROSS

WHERE = $where

QUIET

In this case, KEY (This is nothing but Input Data value) is ERNCD and RETURN_VALUE (Output data value) is Combination of ERNCD, EFFECT_ON_FLSA and ADD_GROSS database field’s value.

! Checking Load lookup array for data validation

Lookup EARN $input_data $output_data

Note: It is good to initialize these Load Lookup Arrays in the SETUP Section related procedures.

Read More About Load Lookup Arrays