SQL.Fetch: object isn't open on a select

Usually, you come across this error when there are erroneous rows while loading data through Component Interface (CI).

You might be using any of the sql class functions to loop through the data and then inserting each row.  When an error occurs the CI closes the SQL object which is open. Thus subsequent rows will not get processed.

This is a problem as the subsequent rows which are valid are not getting inserted.
Now that we identified the cause for the issue, lets try to fix this issue.

Below is one such approach which helps in resolving the issue.

Resolution:
1. Create a standalone rowset and fill it with all the rows which needs to be inserted.

2. Now loop through the rowset and load the data through CI.

3. For error row, CI fails while saving. At this point , call the Cancel function as the current transaction is invalid.

4. After Successful cancellation of erroneous row , CI will move on to next row in the loop.

This way , we can avoid/skip error rows and proceed with insertion of other valid rows.
Hope this post is useful..

BI Publisher: Run Date and Run Time in the Output Report

In Reports, we generally place the Report Run Date and Run Time in the Output Report.

Lets see how this can be done in BI Publisher.

Open the BI Publisher Template (RTF), insert the Form field and in the options select the Field Type as Current date for Run Date  and Current Time for Run Time.

Below are the snapshot for the same.

 Now save the template and check the Output in the Preview. Below is the sample Output.

Error No 53 BI Publisher

Error No 53  is because the Java Home Path mentioned is either incorrect or Java JRE is not installed.

Lets take a look at the below example.
I had purposefully entered incorrect JRE Path to replicate the issue.

Now , lets validate the BI Publisher template. We get below error message.

 To resolve this issue, Navigate to Tools --> Options --> Preview and correct the Java Home Path as shown below.

 Now Save the Template and then Validate.

This resolves the issue.

Record Type -- Description

RecType and Descr
0 -- SQL TABLE
1 -- SQL VIEW
2 -- DERIVED/WORK
3 -- SUBRECORD
5 -- DYNAMIC VIEW
6 -- QUERY VIEW
7 -- TEMPORARY TABLE

PeopleSoft Audit: Record Level , Field Level , Database Level

To help us keep track of changes done to sensitive data by appropriate user, we use Audit.
Later we can query the Audit record to see the list of changes happened.

In PeopleSoft, we can set Audit at
1. Record Level
2. Field Level
3. Database Level.

For Option 1 and 2, Audit captures changes done to data for the online page. If the data is changed by any program using a SQL statement then Audit is not captured.

For Option 3, It covers both online and database changes done to the transaction record.

Now lets see how to implement these 3 types of Audits:

1. Record Level Audit.
Open the record definiton and then Record Properties. You will find the section where you enter Audit Record Name and Type of Actions(Add/Delete/Selective/Change) which needs to be captured.
For any change done to the fields in the record, a row will be inserted into Audit record.

2. Field Level Audit.
Open the record definiton and then Record Field Properties for any of the field for which Audit needs to be captured. In the Audit section , you select the type of Action that needs to be audited.
For any change in the field, there will be mutilpe rows inserted in the Audit record.

3. Database level Audit.
There is navigation available in PIA to generate trigger script which is then excuted in the database. This kind of Audit captures the changes  done to the record at database level also.

Audit record should start with AUDIT_XXX  and should contain below fields as Key fields.

• AUDIT_OPRID -- Captures Info on who changed the data
  
• AUDIT_STAMP -- Captures the TimeStamp
  
• AUDIT_ACTN  -- Captures the Type of Transaction done (Insert/Delete/Update..)
   

In next post , we will see each of these in detail.

PeopleSoft: Process Groups

Just like we have security to pages, we have security to Processes as well.

In PeopleSoft Security is defined through Permission lists which will restrict user access not only to online pages but also to PeopleSoft Definitions.

Lets now focus on the Process Security and role of Process Groups.   

A Process definition is needed to run a Program from Process Scheduler.
When we setup a Process Definition we need to make sure the Process Name should be same as the Program Name.

To run the Process through PIA, we need to provide navigation. Thus we add Component to the Process Definition.

Adding a component to a process definition causes that process definition to appear on the Process Scheduler Request page when you select File, Run in that component, if you have security to run the process.
Below is the snapshot of the Process Definition page (PeopleTools 8.52)

                                            Diagram 1: Process Definition Page.

Make the process definition a member of the Process Group. A process definition can be a member of multiple process groups.

How to create/use existing Process Group:
Select an existing group, or add a new group by entering a unique process group name from the prompt provided for Process Group.
To attach the same process to multiple process groups, Click on Add button and include the Process group.

Note: There is no separate Setup page to define Process Groups.
The Prompt in the Process Definition is used to create new ones as there is no Edit for the Prompt. Below is the snapshot.

                                          Diagram 2: Process Group Prompt table on Process Definition page.

The Process groups are then assigned to Permission list. Now the Permission list is attached to Role.
A user now gets access to run the Processes under the Process group, attached to his/her Permission list.

Record which stores Permission lists and Process Group:
PSAUTHPRCS

Below Diagram depicts the usage of Process Groups in PeopleSoft.

.

                                       Diagram 3: Usage of Process Group Pictorial Representation

In real time, lets say users belonging to HR should run only HR jobs not Payroll. In this case , we create 2 process groups and assign all HR Processes to HR Process Group (say, HRPRCSGRP) and all Payroll Processes to Payroll Process Group (say, PYPRCSGRP). Now assign these process groups to permission lists and then permission lists to roles. Now assign HR role to HR user and Payroll Role to Payroll user. This way we can restrict access to particular users.

This concludes overview of usage of Process Groups which restricts the user to run the jobs he/she is authorized to run.