Generating xls file in PeopleSoft using OLE Automation

Generating xls or formatted excel file is tedious with SQR.

But we can generate them using Peoplecode with OLE Automation.

We need to create a Excel object and then we can use almost all VBA commands in PeopleCode with Peoplesoft notations, Remember vbconstants like vbblue will not be used here.

Also Make sure you are running this code in windows server with excel installed.

Local object &workApp;Local object &workBook;
Local object &workSheet1, &workSheet2, &workSheet3, &workSheet4;
try
 &workApp = CreateObject("COM", "Excel.Application"); 
 ObjectSetProperty(&workApp, "Visible", False);
 &workBook = &workApp.WorkBooks.Add(); 
 &workSheet1 = &workBook.WorkSheets(1); 
 &workSheet2 = &workBook.WorkSheets(2);  
 &workSheet3 = &workBook.WorkSheets(3); 
 &workSheet3.activate();  
 &workSheet4 = &workBook.Worksheets.Add(4);

 Rem - Name worksheets   
 &workSheet1.name = "Hires";  
 &workSheet2.name = "Terminations"; 
 &workSheet3.name = "Contract Changes"; 
 &workSheet4.name = "New";   

 Rem - Assign values in different worksheets  
 &workSheet1.Cells(1, 1).Value = "Sample1";  
 &workSheet1.Cells(1, 2).Value = "Heading2";    
 &workSheet1.rows(1).Font.Bold = True;  
 &workSheet1.rows(1).Font.colorindex = 5;    
 &workSheet1.rows(1).Interior.colorindex = 3;   
 &workSheet1.rows(1).Font.size = 8;

 &workSheet2.Cells(1, 1).Value = "Sample2 for big text";  
 &workSheet2.Cells(1, 2).Value = "Sample2 for big text, even big";  
 &workSheet2.rows(1).Font.size = 10; 
 &workSheet2.rows(1).Font.Name = "Verdana";  
 &workSheet2.Columns.AutoFit(); 
 &workSheet3.Cells(1, 1).Value = "Sample3"; 
 &workSheet4.Cells(1, 1).Value = "Sample4"; 
 SQLExec("select PRCSOUTPUTDIR from psprcsparms where prcsinstance= 
 (select max(prcsinstance) from psprcsrqst where prcsname='AENAME')", 
 &path); 
 &sFileName = &path | "\ExportData2Excel.xls";   
 &workApp.ActiveWorkBook.SaveAs(&sFileName, 18);
 &workApp.Quit();  
 MessageBox(0, "", 0, 0, "Information has been exported to an Excel file """ |  
 &sFileName | """.");  catch Exception &exp  
throw
 CreateException(1000, 22407, "Error saving MS Excel file." | &exp);
end-try;

Process Groups in PeopleSoft

The process groups are not stored in any setup table of its own.
They are stored in the table: PRCSDEFNGRP and PRCSJOBGRP where we store the process and job definitions.
If you take a look a look at the prompt for PRCSGRP field in those tables, it is a 'Prompt with no edit', so u can keep adding new Process groups on the fly, on process and job definition components (Page: Process Definition Options)

Significance of the process group:

They are linked with the user ID through a permission list. 

On the permission list setup, in the "Process tab", you can link the process groups.
This plays very important role in securing the processes and jobs in the system. Process groups to users are tied as below

Process Group ->Permission list -> Roles->User profiles

To find which permission lists have access to which process groups use the following query:

select * from PSAUTHPRCS order by CLASSID, PRCSGRP;

Below screens will show how the new process group is created and mapped through user profile.

Union / Minus operator in SQR

There are different scenarios where we will be using Union / Union all / Intersect / Minus operator in sqr.
below is the example of using union in sqr

begin-select 
field-a &fld-a 
field-b &fld-b 
from tbl1 
where ....
union 
select field-c, field-d from tbl2 where....
end-select

Delete PeopleSoft Query

There could be different reasons we would like to delete a query from the database. Below are the SQL that are needed to delete a query from database.

   DELETE FROM PSQRYDEFN WHERE QRYNAME='Queryname';
   DELETE FROM PSQRYSELECT WHERE QRYNAME='Queryname';
   DELETE FROM PSQRYRECORD WHERE QRYNAME='Queryname';
   DELETE FROM PSQRYFIELD WHERE QRYNAME='Queryname';

The macro cannot be found or has been disabled while accessing BI publisher in MS Word

Recently I have installed XML publisher in my local client workstation.

When I open Microsoft word and try to access Oracle BI publisher I got below error

From different google sources below are two most common errors and how should Word be setup correctly

Templates should correctly enabled

Even after above 2 actions my issue is not solved.

Solution to above issue is

Solution 1:

Search for MSComctlLib.exd in your local drive and rename all the existence, such as MSComctlLib.exd_bak.

usually the file will occur in %USERPROFILE%Application Data\Microsoft\Forms or C:\Users\ramu\AppData\Roaming\Microsoft\Forms

now re-open microsoft word, this will create new file of MSComctlLib.exd  and try accessing BI publisher.

if above soltion doesnot solve your issue. try below

Solution 2:

For 64-bit operating systems, type the following in command prompt (Run command prompt as administrator, otherwise you will get error code 0x80004005): 

Regsvr32 "C:\Windows\SysWOW64\MSCOMCTL.OCX"

For 32-bit operating systems, type the following:

Regsvr32 "C:\Windows\System32\MSCOMCTL.OCX"

open the Microsoft word and access BI Publisher now.

Equivalent of grep in windows

There are instances where we want to search for table / field referenced in sqr on windows.
The built in windows command FindStr mirrors the capabilities of the Unix command Grep.

Findstr /?
FINDSTR [/B] [/E] [/L] [/R] [/S] [/I] [/X] [/V] [/N] [/M] [/O] [/P] [/F:file]
        [/C:string] [/G:file] [/D:dir list] [/A:color attributes] [/OFF[LINE]]
        strings [[drive:][path]filename[ ...]]
  /B         Matches pattern if at the beginning of a line.
  /E         Matches pattern if at the end of a line.
  /L         Uses search strings literally.
  /R         Uses search strings as regular expressions.
  /S         Searches for matching files in the current directory and all
             subdirectories.
  /I         Specifies that the search is not to be case-sensitive.
  /X         Prints lines that match exactly.
  /V         Prints only lines that do not contain a match.
  /N         Prints the line number before each line that matches.
  /M         Prints only the filename if a file contains a match.
  /O         Prints character offset before each matching line.
  /P         Skip files with non-printable characters.
  /OFF[LINE] Do not skip files with offline attribute set.
  /A:attr    Specifies color attribute with two hex digits. See “color /?”
  /F:file    Reads file list from the specified file(/ stands for console).
  /C:string  Uses specified string as a literal search string.
  /G:file    Gets search strings from the specified file(/ stands for console).
  /D:dir     Search a semicolon delimited list of directories
  strings    Text to be searched for.
  [drive:][path]filename Specifies a file or files to search.

Example 1:

findstr /n /i /s /c:"ps_job" "d:\sqr\*.*" > "c:\temp\output.txt"

The above example will search for ps_job in all files on folder / sub-folders of d:\sqr\ and redirect the output to output.txt in c:\.
/n - will print the line number ps_job is referenced.
/i - will ignore case-sensitive
/s - will search string in all files on folder and subfolders od d:\sqr\
/c - denotes to search for string.

Example 2:

findstr /n /i /s /g:"d:\search_all.txt" "d:\sqr\*.*" > "c:\temp\output.txt"

we can also search for multiple strings in one command.
save the file having multiple strings in d:\search_all.txt (multiple strings should be ended with newline)
this example will search of all strings defined in d:\search_all.txt on d:\sqr\ and redirect the output to c:\temp\output.txt

Time and Labor installation steps

Below are the basic installation steps required for Time and Labor

(SQR 4407) Referenced variables not defined

There are 2 causes of this issue to occur.

1. Code between Begin-Select and End-select are not correctly formatted
or
2. check for variable which got error out, if the variable is referenced inside a procedure which have parameters (Called as function) and also referenced outside the issue occurs, the solution will be pass the referenced variable as parameter to the procedure.

Example for 2:

begin-select
----
----
    let $emplid=&emplid
----
----
end-select
do proc1($setid)


begin-procedure proc1($setid)
begin-select
----
---
where emplid = $emplid and setid=$setid
----
end-select
end-procedure

should be changed as below

begin-select

----

----

    let $emplid=&emplid

----

----

end-select

do proc1($setid,$emplid)

begin-procedure proc1($setid,$emplid)

begin-select

----

---

where emplid = $emplid and setid=$setid

----

end-select

end-procedure

(SQR 9004) TrueType font file cumbwr__.ttf cannot be opened

There are two things to check; either the font file does not exist in the location specified in pssqr.ini file, or the path specified in sqr.ini file is not correct. 

01 = Font type. 
Verify if the setup is correctly into the pssqr.ini to the  [TrueType Fonts] section 
The Font Path=directory where fonts resides and the TrueType collection file (.ttf).

For example:
 [TrueType Fonts]

; This section specifies the mapping from TrueType font names used in 
; above configuration section and physical file path of the font on 
; operating system. For TrueType collection file (.ttc), font directory 
; number should also be specified.
; (ex. font name=file path, directory number)

; Font Path=directory where fonts resides.  Default is SQRDIR.  On 
; Windows, Windows font directory will be looked up too.  Fonts not 
; residing on other directories must be specified full physical path.

Font Path= <PSHOME>\fonts\truetype\
Albany=albw.ttf
Albany-Bold=albwb.ttf
Cumberland=cumbwr__.ttf
Cumberland-Bold=cumbwb__.ttf
Thorndale=thowr___.ttf
Thorndale-Bold=thowb___.ttf
Angsana=angsa.ttf

02 = Font file path.
Verify if the thowr___.ttf collection is located to the correct destination  <PSHOME>\fonts\truetype.

Note: if you are running sqr on unix then check pssqr.unx file