Search This Blog

Tuesday, June 17, 2014

SQR: Date formatting in CSV file

For writing data to ouput files we use write command in SQR. And if we have date as one of the output fields, clients prefer '01062014'. This can be achieved by applying formatting to the date field.

Below is the manual way of doing in Excel file. Enter date as show in pic 1 and tab out.
Once we tab out, below is the format of the date that appears in the file.




But this is not we expected. So now lets apply formatting.
So when we prepend =" to date and append ", it converts date to Text format , thus retaining required format.



Fine. Now we need to achieve same through the SQR. Below is the sample code to do the same.
Sample Code:
  let $write_header = 'EMPLID,EFFDT,FORMATTED EFFDT'
  let $emplid = 'EMP101'
  let $effdt  = '01022014'
  let $text = '="05062014"'

  let $delim = ','
 
write 1 from $write_header

write 1 from $emplid
         $delim
         $effdt
         $delim
         $text
SQR Output for a comma separated CSV file

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.