SQR

Call Unix Script from SQR

Few posts back, I wrote about how to call a UNIX script from PeopleCode. In this post, I will provide a sample code to call a UNIX script from an SQR program.

 
!**********************************
! Procedure:    Run-Script                            *
! Description:  Calls a unix script from SQR *
!**********************************
begin-procedure Run-Script
#debug show 'Entering Run-Script'

! call script located in /ps_scripts folder

Writing XLS file using SQR and PeopleCode

We can write a XLS file using PeopleCode and SQR. We will be simply writing a XLS file using HTML tags.
Below is the SQR code.

Let $filename='C:\PeopleSoft\PsftHRMS90\'||$Prcs_Process_Instance||'\Report'||$Prcs_Process_Instance||'.xls'
Open $filename  as 2 for-writing record=20000 status=#filestat2
<*start of  table *>
Write 2 from '<table border = 1>'

<*start of heading row*>
Write 2 from '<tr>'
<*heading cell tag, we can have multiple headings cells in a heading row*>
Write 2 from '<th>'
Write 2 from  $head
Write 2 from '</th>'
<*end of heading cell tag*>

Send Emails in SQR

Here is the piece of code you can use to send email with attachments in SQR.

BEGIN-PROCEDURE sendmail
let $subject = 'Subject of the Email Here'
let $to_list = 'email@email.com'
let $ReportID = 'G:\PeopleSoft\Reports\Report_Name.pdf'
let $alias ='Attachment File Name Alias including the file extension'
let $enter = chr(10)chr(13)

SQR

The SQR handbook will hold tips and tricks as well as some How to posts.

How to Read a File Using SQR?

Here is some SQR code that will enable you to read a file. The code will also track # of records being read and will print out how many records have been rejected as well as total records read.

!------------------------------------!
! Procedure: Read-Input-File !
! Descr:     Read file               !
!-----------------------------------!
Begin-Procedure Read-Input-File
#debug show 'Entering Read-Input-File'

Read 1 into $Record:3000  !skip the headings

Move 0 to #Tot_Recs
Move 0 to #Recs_Rej

While Not #end-file
        Read 1 into $Input:3000 Status=#Read_stat

Close an Open File Using SQR

Here is an SQR code to close a file that has been opened for reading. Check my other article if you would like to see how to open a file for reading using SQR.

!---------------------------------!
! Procedure: Close-File       !
! Descr:     Close a file that !
! has been opened for reading.!
!--------------------------------------!
Begin-Procedure Close-File
#debug show 'Entering Close-File'

If $file_open = {TRUE}
    close 1
    Move {FALSE} To $file_open
    Show 'File ' $your_file_name ' Closed successfully.'

Run an SQR From a PeopleSoft Application Engine

Here is a good sample of how to launch an SQR from within PeopleCode. You can use the peoplecode function called “ScheduleProcess”. Here is the example:

&PROCESS_NAME = "SQRNAME";
&PROCESS_TYPE = "SQR Report"; (or "SQR Process")
&RUN_CNTL_ID = "YOUR_RUN_ID";
&RUN_CONTROL = ScheduleProcess(&PROCESS_TYPE, &PROCESS_NAME, "2", &RUN_CNTL_ID, &PRCS_INST);

If &RC != 0 /*"1" = client "2" = server*/
Winmessage("Error: Error Scheduling SQR Process, RC = " | &RUN_CONTROL);
End-If;

Date Validation Using SQR

Here is some SQR to help you validate a date format. You can modify the code to fit your own needs and date format you desire.

!-------------------------------------!
! Procedure: Date-Validation !
! Descr: Validate $entered_date. !
!-------------------------------------!
Begin-Procedure Date-Validation
#debug show 'Entering Date-Validation'
Let $Date_Validation = 'N'
!Show '$entered_date: ' $entered_date
Let #1st_Slash_position = instr($entered_date, '/', 1)
!Show '#1st_Slash_position: ' #1st_Slash_position

Open a File for Reading Using SQR

Here is some SQR code that will open a file for reading:

Begin-Procedure Open-File
#debug show 'Entering Open-File'
!file name and where it is located.
Let $new_file = $PATHNAME || 'your_file_name.txt'
Show '$new_file = ' $new_file
!check if the filel exists before trying to open it
Let #estatus = exists($new_file)
If #estatus = 0
Show 'Log File ' $new_file ' exists. Will be opened for reading.'
!opening file for reading
Open $new_file As 1 For-Reading Record=3000
Status=#FileStat
!couldn't open file if #FileStat is not zero
If #FileStat != 0
Move 'N' To $file_open