SQR Programming Techniques

 In order make SQR program to run through process scheduler  


if IsBlank($prcs_process_instance)
    do Ask-Values
  else
    do Get-Values

  end-if

!***************************************
Begin-Procedure Ask-Values                         
!***************************************
  input $AsOfDate 'Enter Date'
End-Procedure

!**************************************

Begin-Procedure Get-Values

!***************************************

begin-procedure Select-Parameters

begin-select

RUN_CNTL.ASOFDATE   

    let $AsOfDate=&ASOFDATE  

from PS_RUN_CNTL RUN_CNTL

where RUN_CNTL.OPRID = $prcs_oprid

  and RUN_CNTL.RUN_CNTL_ID = $prcs_run_cntl_id

end-select

end-procedure

---------------------------------------------------------------------------------------------

if $prcs_process_instance = ''
    do get-params-from-cmd
else
    do get-params-from-pia
end-if

!****************************************
!Getting I/P Parameters from cmd
!****************************************
begin-procedure get-params-from-cmd
 input $emplid 'enter employee id'
end-procedure get-params-from-cmd

!****************************************
!Getting I/P Parameters from PIA
!****************************************
begin-procedure get-params-from-pia
begin-SELECT
EMPLID 
     let $emplid=&emplid
FROM PS_MY1_PRCSRUNCNTL  
WHERE OPRID=$prcs_oprid 
and RUN_CNTL_ID=$prcs_run_cntl_id
END-SELECT 
end-procedure get-params-from-pia


In order to know the starting and ending timings of a program

!**********************************
Begin-Program
!**********************************
  do Init-Report

  display 'Report Begin at: ' noline
  do display-time

  do Process-Main

  display 'Report Ended at: ' noline
  do display-time

  do Reset
  do StdAPI-Term
End-Program
---------------------------------------------------------------------------------

Getting report repository path 

When we are writing data into files from a SQR program generally it will be written under the same path that we specify , Some thing like in the following way 


let  $CSVFileName ='File Path'

Open  $CSVFileName  as  {filenumber}  For-Writing  Record={recordSize}  Status=#OpenStat

• But If we want to redirect the file path to the Report Repository , i.e. End user can find out the file from View/Log files hyperlink after the successful run from Process Monitor
• In order to do this we can take the help of  PSPRCSPARMS record where all the log file locations will be saved 
• By bringing the code in to the following way , We can easily redirect the written file path to the report repository

!--------------------------------------------------------------------------------

! Procedure: Main Procedure

! Desc:           

!--------------------------------------------------------------------------------

begin-program 

  

   Let $Stat_Dte = datenow()

   display 'Report Begin at: ' noline

   display $Stat_Dte

     do Define-Prcs-Vars

     do Get-Run-Control-Parms                             

 

     if IsBlank($prcs_process_instance)

              do Ask-Values

         else

              do Get-Values

         end-if

 

     do Get-Directory

     do Setup-CSV-File

     do Update-Prcs-Run-Status

  

   Let $End_Dte = datenow()

   display 'Report End at : ' noline

   display $End_Dte

    

end-program

---------------------------------------------------------------------------------

! Procedure:Get-Directory                                    

! Desc     :This is used to get the Report where  All !the Repository path OUT/LOG/REPORT 

! files will get stored 

!--------------------------------------------------------------------------------

begin-Procedure Get-Directory 

begin-select

ORIGOUTDEST,

PRCSOUTPUTDIR

      if rtrim (upper(&origoutdest), ' ') = '%%LOG/OUTPUT DIRECTORY%%'

              let $directory = rtrim(&prcsoutputdir, ' ') || '\'

     else

             !let $directory = rtrim(&origoutdest, ' ')

      let  $directory = '\\FINDEV\psoft\findev\sqr\'

     end-if

FROM  PSPRCSPARMS where

PRCSINSTANCE = #prcs_process_instance

end-select

end-procedure 

!--------------------------------------------------------------------------------

! Procedure: Setup-CSV-File                                       

! Desc: This procedure creates a CSV file & Opens it for !writing the data      

! '$directory' Value will give the path to Report Repository in !process scheduler

!--------------------------------------------------------------------------------

begin-Procedure Setup-CSV-File

 #debuga show 'File SetUp Started'

let $Comma = ','            ! Will be used as a delimiter while writing into file

let $DeleteValue = 'N'

let  $CSVFileName =  $directory || 'Pgm-name' || '_' || $prcs_process_instance ||'.csv'

Open $CSVFileName as {filenumber} For-Writing Record={recordSize} Status=#OpenStat

If #OpenStat != 0

    #debuga Show 'Error Opening ' $CSVFileName

Else

    #debuga Show 'File Opened Success' $CSVFileName

 do Write-CSV-Header

         do  process_level1

Close {filenumber}

End-If

#debuga show 'File setup end'

end-procedure