Search This Blog

Loading...

Saturday, March 1, 2014

SQR Basics: Usage of DISTINCT clause

Below is the syntax for the usage of DISTINCT clause in SQR.

BEGIN-SELECT DISTINCT
[COL1]
[COL2]
.
.
.
FROM [RECORD_NAME]
END-SELECT

Friday, January 24, 2014

PeopleSoft: Working with grids have same record at Level 0 and Level 1

In PeopleSoft, we use grids to populate data in a list wise manner. And generally in Setup pages, we see same record being used at Level 0 and Level 1.

In such case, there was an issue that creeped up while saving the data in the page. Along with the data in the grid , extra row gets inserted to the database record which contains only data for Level0 fields.

To avoid the unwanted row, it is noticed that the page design is causing the issue. The Level 0 field which we enter data from Search Page is in Edit mode. So the Component Processor allocated buffer for the Level0 fields expecting some data. During Save , along with Level1 data in grid , Level 0 data also got inserted.

To resolve the issue, the Level 0 field is made 'Display Only', such that Component processor will treat this field as 'Read Only' and does not allocate buffer. Now when we save data , only data from the grid gets inserted.

Monday, January 13, 2014

Oracle: Differnce between to_date('01-JAN-00','DD-MON-RR') and to_date('01-JAN-1900','DD-MON-YYYY')

Difference between to_date('01-JAN-00','DD-MON-RR') and to_date('01-JAN-1900','DD-MON-YYYY')

Result of to_date('01-JAN-00','DD-MON-RR') will be '01-JAN-2000'  where 'RR' refers to current century
Result of to_date('01-JAN-1900','DD-MON-YYYY') will be '01-JAN-1900'.

Note: By default, if you do a 'Save As' with Insert , the default format will be in 'RR'.



Saturday, November 30, 2013

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.

Tuesday, October 29, 2013

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.



Thursday, September 26, 2013

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.

Friday, September 20, 2013

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.