GetFile and Open file

Both get file and open file are used to access file from server.

Both have the same syntax.
GetFile(filename, mode [, charset] [, pathtype])
Open   (filespec, mode [, charset] [, pathtype])

Only difference is that Whenever a call is made to GetFile , a new instance is created. Meaning each instance requires a separate file object to refer the file.
So usage of Getfile will be effective in case multiple files are accesed at a time.

For Open file , for each call same file object is used to access file.
That means, file object &File can be used to access file1 and subsequent call to another file closes the current file and accesses the another file.

Output in multiple Excel sheets using VB Script

Lets see how we add data to sheets manually.
We need an Excel Application to open Excel file. So we click on 'MS Excel'.
Then a new workbook opens.
First sheet enter data and name the tab(sheet name).
In the Second sheet enter data and name the tab(sheet name).
Save and Close the file.

The same is done through programming in the below example.

Set objXL = CreateObject("Excel.Application") -- Create XML application object.
Set objWB = objXL.Workbooks.Add   -- Open Workbook
Set objSHTLG = objWB.Worksheets(1) -- Open first sheet
objSHTLG.Name = "ABC"   -- Name the first sheet

With objSHTLG.QueryTables.Add("TEXT;D:\ABC.txt", objSHTLG.Cells(1, 1)) 
-- Load data from ABC.txt file
.Refresh BackgroundQuery = True
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 1, 1, 1, 1, 1, 2)
.Refresh BackgroundQuery = False
End With

Set objSHTLG = objWB.Worksheets(2) -- Open Second Sheet
objSHTLG.Name = "TEST"  -- Name the Second sheet

With objSHTLG.QueryTables.Add("TEXT;D:\TEST.txt", objSHTLG.Cells(1, 1))
-- Load data from TEST.txt file
.Refresh BackgroundQuery = True
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 1, 1, 1, 1, 1, 2)
.Refresh BackgroundQuery = False
End With

objWB.SaveAs "C:\Book1.xlsx" -- Save the Excel File as Book1.xlsx
objXL.Quit  -- Close the file.

Some Info on the below code:
With objSHTLG.QueryTables.Add("TEXT;D:\ABC.txt", objSHTLG.Cells(1, 1)) 
TEXT -- refers type of file
After semicolon, the file path from which data gets loaded onto the sheet.
objSHTLG.Cells(1, 1) -- Row 1 Col 1 of the sheet from which data load happens.

Running the VB Script: Double Click on the ".vbs" file. The code gets executed and a new file "Book1.xlsx" gets created.

Below is the snapshots of the practical example:
Text Files which are considered in the example.

Content in Text Files:

Output after running VB Script.

PeopleSoft Security: Row Level Security Part 1

One of the topics which needs attention in PeopleSoft Security is Row level Security.
In Organizations, we have different people occupying different roles and each role
has its own responsibilities.
A Single person may handle two roles which he needs to perform in the organization , say Employee role and Manager role etc. Through Row level security data is secured such that it will be viewed by only authorized users.
 
Let's consider a college which offers courses in different Domain. To make it more simple lets consider there is Computer Science Department and Information Technology Department and Civil Department.

And below are the entities
Students
Assistants Professors
Professors
Head of the Department

As we know there are certain subjects which are common between Computer Science Department and IT Department, same professor can teach same subject for both of the Departments. Some Professors only teach CSE or only teach IT.

Let's say Professor A teaches CSE and IT Departments
Professor B teaches only CSE Department.

From the above scenario,  Professor B can access the records of students only from CSE Department and Professor A can access records of students from both CSE and IT Departments. This is nothing but making data available to authorized users by encapsulating unwanted data. The term we use in PeopleSoft for this is 'Row Level Security' where each entry in the database table is considered as a row of data.

If we are familiar with Set Theory in Mathematics, its the same concept used here.  Below is the diagram which shows the level of access each Professor has to the data in the system.

As of now we have just seen the conceptual part. Let's see how to setup the data level security in PeopleSoft with an Example in the next post.

PeopleSoft Security Overview

Before going into the details of PeopleSoft Security, let's see where all in the PeopleSoft application we come across security related stuff .

From the user's perspective:

1. To access application through PIA , one must have an UserID to login to the application.

2. Once logged in, we certainly have to do some tasks, like an Employee can go and apply for leave or see his performance data or view paychecks etc. That means we require access to certain pages in the application.

3. Once Employee access the pages there might be certain fields which need to be hidden .

4. Also if the Employee has a role of Manager, and if the Manager wants to know the list of resources available from his department so that he/she can assign them to different projects in such a case the search results should show only the Resources under his department which is nothing but access restriction at data level.

5. If user wants to run a job then a process profile is required so that access is given to run only those jobs.

6. Suppose user wants to run a query through PIA, then the same can be achieved through Query Manager. To build a query , a table is required. To restrict user to build queries on only certain tables you need to assign query access profile which controls user to access certain tables.

7. Also if the Client has more than one PeopleSoft application and user wants to connect to other application without providing credentials again , this can be achieved through Single Sign on.
8. If we want user not to update any data on the page and just view it then we can set the same at the Page Permissions.

Now lets dig further and see what restrictions are there at Developer level :
1. Developers login to the tool called App Designer to build applications. If we want developers to restrict that certain objects like CI etc then we assign security at Definition level.

At PSAdmin Level:
1. The Webserver and Appserver config files will have information related to security which is used in validation of the requests that come browser and then give access to valid users.

2. Not everyone will have access to webserver and App Server. These tasks are handeled by PeopleSoft Admins (PS Admin)

This post is intended to give an Overview of  PeopleSoft Security.

BI Publisher: Basics in creating a RTF template

BI Publisher in PeopleSoft helps us to view output in different formats.
We use templates to design the output layouts. Let's see a sample example below.

Data Preparation:
I had an XML file which has Band and Description.
There are 20 rows of data. Now lets load this data into the template.

                                           

Loading Data into the Template:           

Open the MS Window and Go to Add-ins tab and click on Data-->Load XML Data. 

Designing the Template:

After that drag the fields from the Field Brower onto the document as shown below.

Below is the final draft of the document which has Band and Description.

Now Click on Preview to view the Output. In the Output our expectation is to view all 20 rows but to our surprise there is only row which comes up. This is because we have not looped through all the rows of XML.

To loop through all the rows, we need to add the for-each tag. Now let's add this tag and view the output.
Tag used in this example: for-each:@row_Band

Now lets see the preview below. As expected now we get all rows in the output.

This completes the sample example of designing a report layout using RTF template.
We will see in next post on how to place headers in the template.

SQR: BEGIN-SELECT to get Count value

In SQR, we use below select construct to retrieve data from database.

BEGIN-SELECT
COL1
COL2
.
.
FROM TABLE_NAME
WHERE CRITERIA
END-SELECT

Now I need count of EE working in Department '10'. Below is the code to achieve this.

BEGIN-SELECT
COUNT(EMPLID) &Count_EE
FROM EE_DEPT_TBL
WHERE DEPARTMENT_ID = 10
END-SELECT

Sometimes, by mistake we might use #Count_EE as column variable since the value of Count is numeric. So coming to basics, all column variables will be referred with & (Ampersand).

PeopleSoft Migration Queries

Most of us have come across the term Migration, where we migrate objects developed from one environment to other. Below are few queries related to Migration issues.


Migration Queries: BI Publisher

Scenario: There is a change done to the RTF template and this new template should be migrated to other environment. What are things to keep in mind while creating a project.
Most of us have come across the term Migration, where we migrate objects developed from one environment to other. Below are few queries related to Migration issues.

You need to include below objects in the project.
BI File Definition
BI Template Definition

BI Report Definition.

Note: If the template is using a new XML file as template file, then it is mandatory to have the file ID associated with the Template included in the project. If this is not done and migrated only Template and Report Defn, then you will end up with a weird error message while opening the report defn through PIA. This is because the FileID used for template does  not exist in target database.

Migration Queries: App Engine.
Scenario: Changes are done only to few step properties in a section. Now these steps should be migrated to other envrionment.

You need to include below objects in the project.
Section
Steps for which change is done.

Migration Queries: Record Properties. Adding AUDIT record.

There was Audit Record added to the Record at Record Properties and after migration through STAT the change is not getting reflected in the target environment.

To resolve this issue, in the project Compare options, there will be option to Keep Target changes flag for Audit. It should be unchecked so that the change will take place in the target environment.

PeopleSoft: Audit Actions.

From the previous post, we have seen various ways of Auditing. Let's see the easy way to understand the Record Level Audit with below example.
Below is the sample Transaction Record and Audit Record.

 Lets now do below transactions and see how data is stored for Record Level Audit.

Scenario 1: Insert a new effective dated row for a Deduction.

Action : 'A'

Scenario 2: Changing a Non-Key Field.

Action : 'C'

Scenario 3: Changing a Key Field.

Action : 'K'
Action : 'N'                                      

Scenario 4: Deleting a row.

Action : 'D'

Note: The tables mentioned are only for example and the fields with * can be understood as KEY fields.

Field Label : Importance of Label ID and its usage

Field Definition: Importance of Label ID and its usage

When you create a new Field Definition in PeopleSoft, you not only choose Field Type/Length/Format but also you enter the Label for the field which should appear on the page.

So it makes a logical sense, to have atleast one Label ID for a field. This will be the Default Label.

Suppose that the same field definition should display different Labels in different pages. In such a case, we go for multiple labels.

In case of Multiple labels, if no default label is selected then the first one in the list will be treated as 'Default label'.

Below are the various places in PeopleSoft where we come across the Labels for a field that should be displayed on the page.

1. Field Definition Level
2. Record Field Properties Level
3. Page Field Properties Level
4. Peoplecode

Lets see each of them in detail.

1. Field Definition Level:
Lets take Example of EMPLID  field. You can see that the default Label ID here is EMPLID and hence the Name which is used for this Label Id will be displayed on the page. But which name would come up Long Name or Short Name. This can verified by simply creating a dummy page and inserting field on the page. In the Field Properties, you can see that By Default RFT Long Name gets selected.

Also Label ID can be same as Field Name.In this case both the Field Name and Default Label ID are same.

Some Rules while creating Field Label:
The LabelID should not contain any spaces. 

The other Labels for EMPLID are as shown above.

2. Record Field Properties Level:
Lets talk of a scenario where we want Label ID 'EMPLID3' (row number 20 in the above image)
instead of Default Label 'EMPLID'.
 This can done at Record Field Properties as shown below.
In the below snapshot, Defualt Label ID is used at Record Field Properties.

It can be observed that the precedence of values shown in DropDown list. Label ID followed by Short Name and then Long Name.
Now lets change the Record Field Label ID to the one which is desired. In this case, it is 'EMPLID3'.

So now the Name for the Label ID 'EMPLID3' will be displayed on the page.
To know which Name should be displayed, let's see the Page Field Properties
3. Page Field Properties Level
From the image below, we can set the Label as either None/Text/RFT Short/RFT Long.
When RFT Short/Long is selected appropriate value will get displayed for the Label ID 'EMPLID3'.

 

4. At Peoplecode Level.  

Also through peoplecode you can change the label of field that is displayed on the page using Field Class.

The label appearing on the page can changed by using below code.
The code checks the page name and if it is JOBDATA page then LongDescr which is set for the LABEL (‘EMPLID’) will be displayed on the page and vice-versa.

Local Field &FIELD;
&FIELD = GetField(RECORD.MYFIELD);
   If %Page = PAGE.JOBDATA Then
      &FIELD.Label = &FIELD.GetLongLabel("EMPLID");
   Else If %Page = PAGE.STUDENTAPPL Then
      &FIELD.Label = &FIELD.GetLongLabel("EMPLID3");
End-If;

Note:  JOBDATA,STUDENTAPPL  are Dummy Page Names used in the above example and ‘EMPLID’/’ID’ are the labels that will be displayed based on the condition. 

Peopletools MetaTables
PSDBFIELD -- Stores Field Definitions
PSDBFLDLABL -- Stores the Field Labels

This concludes the importance and usage of Label ID in PeopleSoft.

Business Unit

The term Business Unit is used quite often in PeopleSoft.  There are various ways we can interpret the meaning of Business Unit. 

Basically, it helps organizations to track the day to day transactions and also helps in forecasting on things that require attention.

Now let’s see the various scenarios in which Business Units can be used.

Scenario 1: Think of a college that has different Departments like Computers/Mechanical/Civil and other Departments such as Administration/Cultural/Sports.

If the College Management want to know the details happening in each of these department then each department can be treated as one Business Unit. 

Scenario 2: Consider a Restaurant, which had outlets in major cities of the country.

If the Hotel Management want to know the transactions happening in each of these City Unit then each City Unit can be treated as one Business Unit.

Scenario 3: A Company that has Business operations covered in various countries.

In this case, each office operating  from each country can be treated as one Business Unit.

Thus Business Unit is nothing but a logical entity that is used by HR/Admin/Management/Finance and various other people to generate the reports that will be helpful in understanding the operations/transactions happening in each of the units.

Hope this post is useful..

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.

Understanding Restart in AE

Understanding Restart in AE

Application Engine programs save to the database (perform a commit) only when an entire program successfully completes. You must set individual commits where appropriate.

At the section level, you can set a commit after each step in a section. At the step level, you can require or defer commits for individual steps, or you can increase the commit frequency within a step to N iterations of a looping action, such as a Do Select of Do While, within a step.

The commit level that you select affects how restart works in a program. Each time Application Engine issues a commit with restart enabled, it records the current state of the program. The recording of the current state that Application Engine performs is referred to as a checkpoint.

Using the restart feature enables you to perform commits more often in a program. Restart reduces the overall effect on other users and processes while the background program is running because it reduces the number of rows that are locked by the program. Fewer rows allows multiple instances of the program to run concurrently (parallel processing), which may be useful for high-volume solutions.

With restart, if a failure occurs at any point in the process, the user can restart the program and expect the program to behave as follows:

    Ignore the steps that have already completed up to the last successful commit.

    Begin processing at the next step after the last successful commit.

The ability for Application Engine to remember completed steps depends on a record called AERUNCONTROL, which is keyed by process instance.

When a program runs, each time Application Engine issues a commit it also saves all of the information required for a program restart in the AERUNCONTROL record.

Reference : Peopletools 8.53