XML Publisher and PDF output with Password Protection

Many times we have requirement to generate the PDF output with a Password Protection. In PeopleSoft we can generate the Output report in different format using XML publisher.

Protect with Same Password

For this purpose, you need to modify XDO.cfg file. This will have all pdf to be protected with same password.

Protect with Different Password

From 8.50 users can have different password for different pdf.

Navigate to: - Main Menu == Reporting Tools == XML Publisher == Report Definition

Select report Name and go to Properties Tab. Select Property Group as "pdf Security". User can define the Default Password here.

Using PeopleCode Password can be set dynamically for pdf output.

&TestProperty = CreateArrayRept("", 0);
& TestPropertyValue = CreateArrayRept("", 0);
&TestProperty.Push("pdf-open-password");
& TestPropertyValue.Push("password123"); (This can be generated Randomly by Rand() function)
&oRptDefn.SetRuntimeProperties(&TestProperty, & TestPropertyValue);
&oRptDefn.ProcessReport(&sTemplateId, %Language_User, &dAsOfDate, &sOutputFormat);

PeopleSoft Query Security

PeopleSoft Query Tools helps user to select the data from base table. This also provides different help for grouping and Join. Using Query Security we can restricts the user to query certain records.
Here we should differentiate between Restricting the data and restricting the records for query.
User can create Query Access Group Manager and then attach the Query Access Group to user profile with Query permission. These restrictions are only valid when you query the data and it will not restrict on component search.

Query Tree

:-PeopleSoft Query uses query access group trees to control the access of the tables in the database. Query Access Manager provides the flexibility to Create/Modify the query tree. Tree should contain Access Group and Record definition. Record that user wants to query should be in Query tree.

Understanding Query Access Group Trees

Nodes: - Nodes can be either groups or records

Structure:- uses ACCESS_GROUP as Tree Structure. It never uses ACCESS_GROUP. User Should not use Details, Level or Branches

For PeopleSoft Query Tree the Root Node is always a group. Groups are always unique in a given Tree while records definitions can be repeated. Groups and records can have Child Groups and Child Records. Each record needs a unique fully qualified path in the tree.

Structure ID:- The Structure ID is read Only and reads ACCESS_GROUPS for Query access trees.

Defining Row-Level Security and Query Security Records

By default, when Admin gives Query users access to a record definition, they will have access to all the rows of data in the table. In many cases, Admin wants to restrict users from seeing some of those data rows. Admin want to enforce row-level security, (also called data permission security).

Row-Level Security

Using row-level security, users can access to a table without having access to all rows on that table. This type of security is used on tables that houses sensitive data. PeopleSoft applications implement row-level security by using view that joins the data table with an Security table. When a user finds for data in the base table, the system performs a related join between the view and the base table instead of searching the table directly.

Query Security Record Definitions

Admin can implement row-level security by having Query search for data using a query security record definition. The query security record definition adds a security check to the search. Query security record definition determines what data the user can display with Query. You need to specify the appropriate Query Security Record when you create the base table's record definition.
To apply row level security:
1. Open the record on which need to apply row-level security.
2. Properties button == Use tab from the Record Properties.
3. Select the security record definition in the Query Security Record list box.
4. click OK to close the Record Properties dialog box, and save the record definition.

Row-Level (Data Permission) Security Views

You can restrict data by:

1. User (OPRID field)
2. Primary permission list (OPRCLASS field)
3. Row security permission list (ROWSECCLASS field)

To implement row-level security through a security view

1. Insert one of the three fields in record (OPRID, OPRCLASS, and ROWSECCLASS)
2. Make the field as Key. Uncheck he List box property.
3. Build the view and make this record as Search record or define this record as Query Security record.

PeopleSoft system dynamically adds a WHERE clause -to filter the rows specific to current user.

PS Query and Component

You can have PS Query run through PIA URL without having a Component and Menu created. Here is the Trick:-


Navigate to

Main Menu === People Tools === Portal === Structure and Content === Add Content Reference

URL Type:- PeopleSoft Generic URL

Portal URL:- q/?ICAction=ICQryNameURL=PUBLIC."Query Name"

Signout and Sign In again to find the URL in PIA.

Effective Date Logic in PeopleSoft

I am sure people accessing this page are aware of the Effective date Logic in PeopleSoft. But interestingly i found a different Behavior of Effective Date.

Lets take an Example of record having Effective Date in it.

Record name:- PS_EFFDT_TST
Record Structure:- (K)- Key
EMPLID (K)
EMPL_RCD (K)
EFFDT (K)

The Query to Select Data from this table with Effective Date condition will be

SELECT A.EMPLID, A.EMPL_RCD FROM PS_EFFDT_TST A
WHERE A.EFFDT =
(SELECT MAX(B.EFFDT)
FROM PS_EFFDT_TST B
WHERE B.EMPLID = A.EMPLID
AND B.EMPL_RCD = A.EMPL_RCD
AND B.EFFDT <= SYSDATE ) Also if we use %EffDtCheck function to write this query we will have SELECT A.EMPLID, A.EMPL_RCD FROM %Table(EFFDT_TST) A WHERE %EffDtCheck(EFFDT_TST B, A, %CurrentDateIn) There is no Problem so far. Now lets modify the Record Structure little more with one more Key field after Effdt field. Record name:- PS_EFFDT_TST
Record Structure:- (K)- Key
EMPLID (K)
EMPL_RCD (K)
EFFDT (K)
RUN_ID (K)

The Query to Select Data from this table with Effective Date condition will be

SELECT A.EMPLID, A.EMPL_RCD
FROM PS_EFFDT_TST A
WHERE A.EFFDT =
(SELECT MAX(B.EFFDT)
FROM PS_EFFDT_TST B
WHERE B.EMPLID = A.EMPLID
AND B.EMPL_RCD = A.EMPL_RCD
AND B.EFFDT <= SYSDATE ) But if we use %EffDtCheck condition in this table SELECT A.EMPLID, A.EMPL_RCD FROM %Table(EFFDT_TST) A WHERE %EffDtCheck(EFFDT_TST B, A, %CurrentDateIn) we get following query after Resolve SELECT A.EMPLID, A.EMPL_RCD FROM PS_EFFDT_TST A WHERE A.EFFDT = (SELECT MAX(B.EFFDT) FROM PS_EFFDT_TST B WHERE B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD
AND B.RUN_ID = A.RUN_ID
AND B.EFFDT <= SYSDATE
)

which i feel is wrong.

User Re-Authentication on Online Pages

There are cases where we need to have user to Re-Authenticate during the Transaction.

Lets have an example to Authenticate user again when he does some action on a Page say clicks on submit.

PeopleCode provides a delivered function RevalidatePassword() for this Purpose.

This Function can be used at
1. SavePreChange.
2. Workflow.
3. RowSelect
4. SavePostChange

Do Not use this function on Single-Sign-On. This creates infinite loop.

Employee ID Series based on Category

Many times there is requirement to have diffenre series of employee ID, instead of having only one Generic series. To elaborate more say different series like RG00001 for Regular series of employee, CW00001 for Contract employee and so on. This article describes how this can be achieved in PeopleSoft HR.
Last Employee ID generated is stored in PS_INSTALLATION Table. TO achieve this

1. First step is to create a custom place holder to store LAST Id for different category. The structure of table can be something like
CATEGORY PREFIX LAST ID
Regular RG 10001
CWR CW 10001
2. Next, we need to customiz the function where delivered code to generate ID is written.
3. Open FUNCLIB_HR.EMPLID.FieldFormula and modify the function to get the last number.

Function AssignNextId(&Recname As string, &Fieldname As string,

....

If &Fieldname = "EMPLID_LAST_EMPL" Then
&NextId = GetNextAutoNumber(recordname, LastID);

4. Save and Test

PeopleSoft and sLDAP

Here are the steps to be followed to configure Secure Active Directory Integration through PeopleSoft.

Step1:- Create Wallet manager for Application Server

Step 2: Launch Oracle wallet Manager (owm)

Step 3: Create a New Wallet (Wallet> New)

Step 4: Set the Password for New Wallet.

Step 5: Click No when prompted for requesting certificate. We will import the Root CA at Later point of time.

Step 6: Go to Operations > Import Trusted Certificate to import Root Certificate.

Step 7:- Click OK to select a file. This will ask to select a file

Step 8:- If certificate is not available, you can select option “paste the certificate” (Optional).

Step 9:- Choose the Certificate file, which you want to import. Now, you will see a new entry for your root CA certificate Or Select Paste the certificate option to paste certificate. (Optional)

Step 10:- Save the wallet

Step 11:- You will be prompted for the location to save the wallet, Put it under “/psoft/pt848/hr90/install/wallet”

Step 12:- Now you will see a file called ewallet.p12 file under /psoft/pt848/hr90/install/wallet. You will need this file for the SSL connection in PeopleSoft.

Step 13:- Test the connectivity using secured port with LDAPSEARCH provided by Oracle Client installation. You can find it under $ORACLE_HOME/BIN (eg. /oracle/product/10.2.0/BIN)
ldapsearch -h <> -p 636 -W "file:/psoft/pt848/hr90/install/wallet" -P Welcome1 -U 2 -D "<>" -w "wallet Password" -b "Base entry" "objectclass=*"

Step 14:- Repeat above steps to create wallet on Process Scheduler Server.

Step 15:- Business Interlink Change in Application Designer

Step 16:- Open Business Interlink LDAP_BIND and set Port as 636 under Input Tab

Step: 17 Select the Setting tab and set following parameters
SSL= YES
SSL_DB = file:<> (e.g. file:/psoft/pt848/hr90/install/wallet)
SSL_DBPW = Wallet Password

Step 18 Repeat above steps for Business Interlink LDAP_SEARCH.

Step 19:- Navigate to Enterprise components > Directory Interface > Definition > Directory Configurations. Set the SSL Port to 636.

Step 20:- Click on “Test Connectivity” Tab.