Categories
SQR

Generate Excel Report From PeopleSoft SQR

This blog post i will share & discuss about, how to create excel report using SQR. For generating excel files i have created a sample query to fetch from job data like employee id , name , business unit , location & dept name etc. This sample sqr is very easy to understand for producing excel report (.xls) file. Now let s explain the SQR.

  • On Begin-Program  section i have called number of procedure i.e Init_Report which will initialize report by calling Stdapi-Init method. We all know what is the purpose of Stdapi-Init procedure, i’ll not discuss it here
  • Second thing is Init_Variables – I have used this procedure for variables and constant declaration. let say an example i have declare worksheet name and quote for dynamic sql creation.
  • Now let come across Main_Report procedure which is also number of procedure
  • If your program has any  Run Control Page that take values from end user then you should  remove comments from Get_Runcntl_Value procedure and do the necessary changes as per your requirements. Since i’m using any run control page for this hence i have commented out.
  • Most important thing is about Validate_RunCntl_Value procedure , we all know based on Operator ID ( Some time Called User ID ) & Run Control Id only one row will be pickup from Run Control Record hence it is always good idea to check using such numeric variables #Row_In_Run_Cntl_Table = 1 then i can built any dynamic sql or any other routing procedure etc. Follow this examples.
    !Validate Run Control Value
        !If #Row_In_Run_Cntl_Table = 1
            ! To Make Dynamic Where clause according to input parameters.
            !IF NOT ISBLANK (LTRIM(RTRIM(&BUSINESS_UNIT,' '),' ')) 
            !    LET $WHERE = ' AND BUSINESS_UNIT = ' || $QUOTE || &BUSINESS_UNIT || $QUOTE
            !END-IF
    
            !IF NOT ISBLANK (LTRIM(RTRIM(&LOCATION,' '),' ')) 
            !        LET $WHERE = $WHERE || ' AND LOCATION = ' || $QUOTE || &LOCATION || $QUOTE
            !END-IF
  • Now lets go to Init_Excel_File – it is simply initialize and open the excel files for writing on Process Scheduler Output Directory then write rows one by one using SQR Write 1 From Syntax.
  • Write_XML_HTML_Code – Write XML tags into Excel , used for Excel schema in MS Office Excel 2003 format.
  • For Generate_Excel – Excel report creation is simple output the html tags & render into excel files ( .xls ) so Write_Excel_Header write html table column header. So you can use all html tags specially table attribute like rowspan colspan bgcolor etc.
  • Fetch_Excel_Data – Fetching data from job record and write column values Write_Excel_Data procedure
  • Write_Excel_Data – is Write data with table tr td tags, follow this examples.
    !********************************
    Begin-Procedure Write_Excel_Data
    !********************************
    
            WRITE 1 FROM '<tr>'
    
            WRITE 1 FROM '<td align = left>' $EMPLID '</td>'
            WRITE 1 FROM '<td>' $NAME_DISPLAY
            Let $BU = $BusUnit
            Do Get-Business-Unit-Name
            WRITE 1 FROM '</td><td>' $BU_DESCR
                Let $DeptID = $DEPTID
                Let $AsOfDate = $AsOfToday
                DO Get-Department-Name
            WRITE 1 FROM '</td><td>' $DeptName
                Let $SetID = $SetIDJobCode
                Let $JobCode = $JOBCODE
                DO Get-Job-Title
            WRITE 1 FROM '</td><td>' $JobTitle

So Download the Code and register Process As SQR Report

SQRXL

Sample Excel Output

ExcelReport_4010

Process Registration Steps

2014-12-03_00002
Step 1
2014-12-03_00001
Step 2
Step 3 - Not Required,but used for Debugging purpose.
Step 3 – Not Required,but used for Debugging purpose.

So Please let us know your view & suggestion. Hope this helps 🙂

Here is the Full Code

!-----------------------------------------------------------------------------------------------!
!  Report Name: SQRXL  -- Run Via Process Scheduler SQR Report (API Aware)                      !
!  Report Descriptions: Sample SQR Report Produce Excel                                         !
!  Report Create Date: 30-Nov-2014                                                              !
!  Report Run Component: PRCSMULTI ( System Process )                                           !
!  Written By - DBTutorBlog    https://www.dbtutor.com                                           !
!------------------------------------------------------------------------------------------------

#include 'setenv.sqc'       !Set environment
#define RecordLength  10000 !Set Record Length

!*****************
Begin-Program
!****************
     Do Init_Report
     Do Init_Variables
     Do Main_Report
     Do Stdapi-Term
End-Program


!*****************************
Begin-Procedure Init_Variables
!*****************************
        !Variable Init & Constant Declaration 
        Let $Quote = ''''    ! Used to make dynamic where caluse.
        Let $WorksheetName = 'Employee Report'
End-Procedure


!****************************
Begin-Procedure Init_Report
!****************************
     !Init Report & Load Lookup Section
     Do Stdapi-Init
     
End-Procedure


!***************************
Begin-Procedure Main_Report 
!***************************
    !Do Get_Runcntl_Value
    !Do Validate_RunCntl_Value
    Do Init_Excel_File
    Do Write_XML_HTML_Code
    Do Generate_Excel
    Do Close_HTML_Tag
End-Procedure


!*************************************
!Begin-Procedure Get_Runcntl_Value
!************************************
!Move 0 to #Row_In_Run_Cntl_Table
!Begin-Select On-Error=SQL-Error Loops=1 !Run Only 1 Time As RunControl is Key Field
!RC.SOA_RPT_TYPE_FLAG
!RC.COMPANY
!        
!        !Run Control Row Should be Only One
!        Add 1 to #Row_In_Run_Cntl_Table
!        
!        !Getting Run Control Value
!        Let $Soa_Rpt_Type_Flag  = rtrim(ltrim(&RC.SOA_RPT_TYPE_FLAG, ' '), ' ')
!        Let $Company  = rtrim(ltrim(&RC.COMPANY, ' '), ' ')
!        !----------- End

!FROM PS_SOA_RUNCNTL_TBL RC
!WHERE RC.OPRID = $prcs_oprid
!AND   RC.RUN_CNTL_ID = $prcs_run_cntl_id
!End-Select
!End-Procedure
  
!**************************************
!Begin-Procedure Validate_RunCntl_Value
!**************************************
    
    !Validate Run Control Value
    !If #Row_In_Run_Cntl_Table = 1
        ! To Make Dynamic Where clause according to input parameters.
        !IF NOT ISBLANK (LTRIM(RTRIM(&BUSINESS_UNIT,' '),' ')) 
        !    LET $WHERE = ' AND BUSINESS_UNIT = ' || $QUOTE || &BUSINESS_UNIT || $QUOTE
        !END-IF

        !IF NOT ISBLANK (LTRIM(RTRIM(&LOCATION,' '),' ')) 
        !        LET $WHERE = $WHERE || ' AND LOCATION = ' || $QUOTE || &LOCATION || $QUOTE
        !END-IF
    !else
        !Show 'This program must be run from the Process Monitor. with valid Run Control Value.'
        !Stop Quiet
    !End-If
    
!End-Procedure

!*********************************
Begin-Procedure Init_Excel_File
!*********************************
    
    !*************************************************************
    ! Now lets initialize the Excel File.
    ! We Will put Excel File to Process Scheduler Output Directory
    !*************************************************************
                                  
    Do Get-Prcsoutputdir      !** Procedure To Get The Path Of The Output Folder.
                                    
    If (rtrim($prcsoutputdir,' ') <> '')
        Let $GenerateNewFile = $prcsoutputdir || 'ExcelReport' || '_' || $prcs_process_instance || '.xls' !* The Output File Represented By the Variable $GenerateNewFile Is Suffixed With The Report Name And The Process Instance
    End-if
    
    !Open File For Writing From Line No - 1
    Open $GenerateNewFile as 1 for-writing record = {RecordLength}:vary status = #FileStatus
    
    !Checking For File Status
     if #FileStatus != 0
          display $GenerateNewFile noline
          display 'Unable to Write File'
     end-if
    
End-Procedure

!*********************************
Begin-Procedure Write_Excel_Header
!*********************************

        Write 1 From '<html xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/tr/REC-html40">'
        Write 1 From '<body>'
        Write 1 From '<table border="1">'
        Write 1 From ' '
        
        !Start Printing Top Excel Information, Specially Run Date Time & Run Control Values
        Write 1 From '<tr>'
        Write 1 From '<td class=tablehead colspan=6 align=center>Oracle PeopleSoft Enterprise</td>'
        Write 1 From '</tr>'

        Write 1 From '<tr>'
        Write 1 From '<td colspan=6 align=center>Employee Info Sheet</td>'
        Write 1 From '</tr>'

        Write 1 From '<tr><td colspan=6></td></tr>'
        
        Write 1 From '<tr>'
        Write 1 From '<td colspan=6 align=center>No INPUT PARAMETERS</td>'
        Write 1 From '</tr>'
        
        Write 1 From '<tr><td colspan=6></td></tr>'
        !End Printing Top Excel Information
        
        !Start Writing Column Header Information , Put your Column Name Here
        WRITE 1 FROM '<tr>'
                WRITE 1 FROM '<th bgcolor = "#98AFC7" rowspan="1"> Emplid </th>'
                WRITE 1 FROM '<th bgcolor = "#98AFC7" rowspan="1"> Name </th>'
                WRITE 1 FROM '<th bgcolor = "#98AFC7" rowspan="1"> Business Unit </th>'
                WRITE 1 FROM '<th bgcolor = "#98AFC7" rowspan="1"> Department </th>'
                WRITE 1 FROM '<th bgcolor = "#98AFC7" rowspan="1"> Job Title </th>'
                WRITE 1 FROM '<th bgcolor = "#98AFC7" rowspan="1"> Location </th>'
        WRITE 1 FROM '</tr>'
        !End Writing Column Header Information
            
End-Procedure 

!*******************************
Begin-Procedure Generate_Excel
!************/******************
    Do Write_Excel_Header
    Do Fetch_Excel_Data
    Do Write_Excel_Data
End-Procedure


!********************************
! Driving SQL 
!********************************
!********************************
Begin-Procedure Fetch_Excel_Data
!********************************
Begin-Select DISTINCT

JOB.EMPLID
NAMES.NAME_DISPLAY
JOB.DEPTID
JOB.BUSINESS_UNIT
JOB.JOBCODE
JOB.SETID_JOBCODE
JOB.LOCATION
JOB.SETID_LOCATION

    !**** Getting Column Value *****
    Let $EMPLID = &JOB.EMPLID
    Let $NAME_DISPLAY = &NAMES.NAME_DISPLAY
    Let $DEPTID = &JOB.DEPTID
    Let $BusUnit = &JOB.BUSINESS_UNIT
    Let $JOBCODE = &JOB.JOBCODE
    Let $SetIDJobCode = &JOB.SETID_JOBCODE
    Let $Location = &JOB.LOCATION
    Let $SetidLocation = &JOB.SETID_LOCATION
    !**************************************
    
    DO Write_Excel_Data     ! Calling another procedure to write details in excel file.
   
FROM PS_JOB JOB,  PS_PERSON_NAME NAMES 
  WHERE (JOB.EFFDT = 
        (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED 
        WHERE JOB.EMPLID = A_ED.EMPLID 
          AND JOB.EMPL_RCD = A_ED.EMPL_RCD 
          AND A_ED.EFFDT <= $current-date)
    AND JOB.EFFSEQ = 
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES 
        WHERE JOB.EMPLID = A_ES.EMPLID 
          AND JOB.EMPL_RCD = A_ES.EMPL_RCD 
          AND JOB.EFFDT = A_ES.EFFDT) 
     AND JOB.EMPLID = NAMES.EMPLID)   
     AND JOB.EMPL_STATUS IN ('A','L','P','S')
![$WHERE]
ORDER BY JOB.EMPLID

End-select
End-Procedure


!********************************
Begin-Procedure Write_Excel_Data
!********************************

        WRITE 1 FROM '<tr>'

        WRITE 1 FROM '<td align = left>' $EMPLID '</td>'
        WRITE 1 FROM '<td>' $NAME_DISPLAY
        Let $BU = $BusUnit
        Do Get-Business-Unit-Name
        WRITE 1 FROM '</td><td>' $BU_DESCR
            Let $DeptID = $DEPTID
            Let $AsOfDate = $AsOfToday
            DO Get-Department-Name
        WRITE 1 FROM '</td><td>' $DeptName
            Let $SetID = $SetIDJobCode
            Let $JobCode = $JOBCODE
            DO Get-Job-Title
        WRITE 1 FROM '</td><td>' $JobTitle
            Let $Location = $Location
            Let $Setid = $SetidLocation
            Do Get-Location-Name
        WRITE 1 FROM '</td><td>' $LocationName '</td></tr>'

End-Procedure



!**********************************************************************************
! Open_HTML_Code procedure - To convert html to excel file and make it landscape
!**********************************************************************************
Begin-Procedure Write_XML_HTML_Code

        Write 1 From '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel">'
        Write 1 From '<head>'
        Write 1 From '<style>'
        Write 1 From '    {mso-displayed-decimal-separator:"\.";'
        Write 1 From '    mso-displayed-thousand-separator:"\,";}'
        Write 1 From '@page'
        Write 1 From '    {margin:1.0in .75in 1.0in .75in;'
        Write 1 From '    mso-header-margin:.5in;'
        Write 1 From '    mso-footer-margin:.5in;'
        Write 1 From '    mso-page-orientation:landscape;}'
        Write 1 From '.tablehead{'
        Write 1 From 'white-space:nowrap;'
        Write 1 From 'font-family:Arial;'
        Write 1 From 'font-weight:bold;'
        Write 1 From 'font-Size:13px;'
        Write 1 From 'color:#333333;'
        Write 1 From 'background-color:#ECF1F4;'
        Write 1 From 'Padding:4px;'
        Write 1 From 'text-align:center;'
        Write 1 From 'border:1px solid #d1d1d1;}'
        Write 1 From '</style>'
        Write 1 From '<xml>'
        Write 1 From ' <x:ExcelWorkbook>'
        Write 1 From '  <x:ExcelWorksheets>'
        Write 1 From '   <x:ExcelWorksheet>'
        Write 1 From '      <x:Name>' $WorksheetName '</x:Name>'
        Write 1 From '    <x:WorksheetOptions>'
        Write 1 From '     <x:Print>'
        Write 1 From '      <x:ValidPrinterInfo/>'
        Write 1 From '      <x:HorizontalResolution>600</x:HorizontalResolution>'
        Write 1 From '      <x:VerticalResolution>0</x:VerticalResolution>'
        Write 1 From '     </x:Print>'
        Write 1 From '     <x:Selected/>'
        Write 1 From '     <x:DoNotDisplayGridlines/>'
        Write 1 From '    </x:WorksheetOptions>'
        Write 1 From '   </x:ExcelWorksheet>'
        Write 1 From '  </x:ExcelWorksheets>'
        Write 1 From '  <x:WindowHeight>8700</x:WindowHeight>'
        Write 1 From '  <x:WindowWidth>15195</x:WindowWidth>'
        Write 1 From '  <x:WindowTopX>0</x:WindowTopX>'
        Write 1 From '  <x:WindowTopY>120</x:WindowTopY>'
        Write 1 From '  <x:ProtectStructure>False</x:ProtectStructure>'
        Write 1 From '  <x:ProtectWindows>False</x:ProtectWindows>'
        Write 1 From ' </x:ExcelWorkbook>'
        Write 1 From '</xml>'

End-Procedure 


!*****************************
! Close HTML Tags
!***************************** 
Begin-Procedure Close_HTML_Tag
    
      Write 1 From '</table>'
      Write 1 From '</body>'
      Write 1 From '</html>'
      Close 1
      
End-Procedure 

!***********************************************************************
! Standard Include Files
!***********************************************************************
#Include 'curdttim.sqc'   !Get-Current-DateTime procedure
#Include 'datetime.sqc'   !Routines for date and time formatting
#Include 'datemath.sqc'   !Date Manipulation functions
#Include 'readxlat.sqc'   !Read Translate Table
#Include 'number.sqc'     !Routines to format numbers
#Include 'stdapi.sqc'     !Update Process API
#include 'getlogou.sqc'   !get current logoutput directory
#include 'getlocnm.sqc'   !Get Location Description
#include 'getjobtl.sqc'   !Get JobCode Title
#include 'getdptnm.sqc'   !Get Department Name
#include 'getbunam.sqc'   !Get Business Unit Name
#include 'getsetid.sqc'   !Get Setid for the BU and Record Name
!***********************************************************************
Categories
SQR

Working with Date Calculation and Manipulation in PeopleSoft SQR

As Other Programming languages PeopleSoft SQR also contains date related functions, for example

strtodate() -  convert a string to date
datetostr() -  convert a date to a string
dateadd() - add to a date
datediff() - subtract dates
datenow() - returns the date and time it is executed.

You can look forward these documents

  1. SQR Date Function Official Documents (Recommended)
  2. PeopleSoftSQR Blog Dates in SQR (Recommended & Exceptionally Well Documented)
  3. PeopleSoftWiki Blog

I was curious to know about datemath.sqc which was written and delivered by Oracle Corp. These SQC contains huge information about SQR Date calculation , obviously all the calculation contains 5 parent SQR date functions i.e strtodate,datetostr,dateadd,datediff,datenow.So in this blogpost i will discuss about SQR Date Calculation & Manipulation example.I hope it will be helpful for you in order to work on SQR Date.

Before the discussion you already understand that we need to include datemath.sqc file at the bottom of your Primary SQR file.Also note that validdt.sqc needs to include.

!***********************************************************************
! Standard Include Files
!***********************************************************************
#Include 'curdttim.sqc'   !Get-Current-DateTime procedure
#Include 'datetime.sqc'   !Routines for date and time formatting
#Include 'number.sqc'     !Routines to format numbers
#Include 'stdapi.sqc'     !Update Process API
#Include 'datemath.sqc'   !Date Manipulation functions
#include 'validdt.sqc'    !Date Validations 
!***********************************************************************

So lets see how we can call the procedure

!-----------------------------------------------------------------------------------------------!
!  Report Name:  DATECALC.SQR -- Run Via Process Schedular SQR Report (API Aware)               !
!  Report Descriptions: PeopleSoft DATE Calculatin & Manipulation								!
!  Report Create Date: 																			!
!  Report Run Component: PRCSMULTI ( System Process Request ) 									!
!-----------------------------------------------------------------------------------------------!

#include 'setenv.sqc'    ! Set Default environment

!******************
Begin-Program
!******************
	 do Init_Report
	 do Main_Report
	 do Stdapi-Term
end-program

!****************************
Begin-Procedure Init_Report
!****************************
	 Do Stdapi-Init
	 Do Init-DateTime
	 Do Init-Number
	 Do Get-Current-DateTime
end-procedure

!***************************
Begin-Procedure Main_Report
!***************************

	!~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	!Set back day in [YYYY-MM-DD] format
	Let $BackDay = '2013-01-01'
	Let $BackDayYear = '2012-01-01'
	! Before Using any procedure first call it [Convert-To-DTU-Date] it is actually take Input:  $date_in  (native format)
	! and convert Output: $dtu_date (YYYY-MM-DD format)
	do Convert-To-DTU-Date($AsOfToday, $Today)
	!~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

	!dtu-diff-days
        do dtu-diff-days($BackDay,$Today,#diffDays)
	   Let $GetDateDiff = to_char(#diffDays)
	   #DEBUG SHOW 'Diff Days -> ' $GetDateDiff

	!Dtu-Diff-Months
        do Dtu-Diff-Months ($BackDay, $Today, #DiffMonth)
		Let $GetMonthDiff = to_char(#DiffMonth)
	    #DEBUG SHOW 'Diff Months -> ' $GetMonthDiff

	!Dtu-Diff-years
        do Dtu-Diff-years ($BackDayYear, $Today, #DiffYear)
		Let $GetYearDiff = to_char(#DiffYear)
	   #DEBUG SHOW 'Diff Year -> ' $GetYearDiff

	!Dtu-Diff-weeks  
	do dtu-diff-weeks ($BackDay, $Today, #DiffWeek)
	   Let $GetWeekDiff = to_char(#DiffWeek)
	   #DEBUG SHOW 'Diff Weeks -> ' $GetWeekDiff

	!dtu-parse-date
	!Parse Date basically read a date and convert it to Separate Year,Month & Date with Integer Version
	do dtu-parse-date ($Today,#yyyy,#mm,#dd)

		!Convert it to String
		Move #yyyy  to $yyyy '0000'
		Move #mm to $mm '00'
		Move #dd to $dd '00'

		Let $BuildingStringDateV1 = $yyyy || '/' || $mm || '/' || $dd
		Let $BuildingStringDateV2 = $yyyy || '-' || $mm || '-' || $dd

			#DEBUG SHOW 'String Building Date V1 -> ' $BuildingStringDateV1
			#DEBUG SHOW 'String Building Date V2 -> ' $BuildingStringDateV2

		Let #year = to_number('2013')
		do DTU-Format-Date(#year, 1, 1, $YearBegin)
			#DEBUG SHOW 'Year Begin Without Convert-From-DTU-Date -> ' $YearBegin
		do Convert-From-DTU-Date($YearBegin, $YearBegin)
			#DEBUG SHOW 'Year Begin With Convert-From-DTU-Date -> ' $YearBegin

		do DTU-Format-Date(#year, 12, 31, $YearEnd)
			#DEBUG SHOW 'Year End Without Convert-From-DTU-Date -> ' $YearEnd
		do Convert-From-DTU-Date($YearEnd, $YearEnd)
			#DEBUG SHOW 'Year End With Convert-From-DTU-Date -> ' $YearEnd		

		do DTU-Add-Weeks($Today,7,$WorkDate)
			#DEBUG SHOW 'New Date After Adding 7 Weeks From Todays Date -> ' $WorkDate	

	!Get_Month_Start_Date
	do Get_Month_Start_Date($AsOfToday, $BgDt)
		#DEBUG SHOW 'Month Begin Date -> ' $BgDt

	!Get_Month_End_Date	
	do Get_Month_End_Date($AsOfToday, $EdDt)
		#DEBUG SHOW 'Month End Date -> ' $EdDt	

	!Dtu-DayOfWeek# (Returns As Sunday = 1, Monday = 2 etc)
	do DTU-DayOfWeek#($Today, #StartDay)
		#DEBUG SHOW 'Days Of Week -> ' #StartDay 

	!dtu-add-months ( Minus Denotes Backward )
	let #Months = -3
	do DTU-Add-Months($Today,#Months,$AfterAddingtNewMonth)
		#DEBUG SHOW 'New Date After Adding Month -> ' $AfterAddingtNewMonth 

	!Dtu-Add-Month
	Do DTU-Add-Years($Today, -2, $AfterAddingtNewYear)
	    #DEBUG SHOW 'New Date After Adding Year -> ' $AfterAddingtNewYear	

	!Dtu-Diff-Years
	Let $Dob = '1988-01-01'
	do Dtu-Diff-Years($Dob,$Today,#Age_Yrs)
		Let $AgeYrs = to_char(#Age_Yrs)
		#DEBUG SHOW 'Diff Years -> ' $AgeYrs	

end-procedure

!****************************************************
Begin-Procedure Get_Month_End_Date ( $Dte1, :$Dte2 )
!****************************************************

  do ConvertToComponents($Dte1 , $tmp1_yy , $tmp1_mm , $tmp1_dd)
  let  #tmp1_mm  = to_number($tmp1_mm)
  let  #tmp1_yy  = to_number($tmp1_yy)

  if #tmp1_mm = 2
     move 28 to #MonthDays1
     if(((mod(#tmp1_yy,4) = 0) and (mod(#tmp1_yy,100) != 0)) or (mod(#tmp1_yy,400) = 0))
         add 1 to #MonthDays1
     end-if
   else
     if #tmp1_mm = 4 or #tmp1_mm = 6 or #tmp1_mm = 9 or #tmp1_mm = 11
         move 30 to #MonthDays1
     else
         move 31 to #MonthDays1
     end-if
   end-if

   let $MonthDays1 = to_char(#MonthDays1)
   let $E_DT_tmp1 = $tmp1_yy  || $tmp1_mm  || $MonthDays1 || '0000'
   do Format-DateTime($E_DT_tmp1 , $Dte2, {DEFCMP}, '', 'native')

End-Procedure

!******************************************************
Begin-Procedure Get_Month_Start_Date ( $Dte1 , :$Dte2 )
!******************************************************

  do ConvertToComponents($Dte1 , $tmp1_yy , $tmp1_mm , $tpm1_dd)

  let $B_DT_tmp1 = $tmp1_yy  || $tmp1_mm  || '01' || '0000'
  do Format-DateTime($B_DT_tmp1 , $Dte2, {DEFCMP}, '', 'native')

End-Procedure

!****************************************************************************************
! WE have made another procedure called diff-week as it was not available in datemath.sqc
! dtu-diff-weeks
!   Input:    $dtu_date1, $dtu_date2
!   Output:   #dtu_weeks
!****************************************************************************************
begin-procedure dtu-diff-weeks($dtu_date1, $dtu_date2, :#dtu_weeks)

    declare-variable
        date    $wkDate1
        date    $wkDate2
        integer #dtu_weeks
    end-declare

    let $wkDate1    = strtodate($dtu_date1,{DTUDateFormat})
    let $wkDate2    = strtodate($dtu_date2,{DTUDateFormat})
    let #dtu_weeks  = trunc(datediff($wkDate2,$wkDate1,'week'),0)

end-procedure

!***********************************************************************
! Standard Include Files
!***********************************************************************
#Include 'curdttim.sqc'   !Get-Current-DateTime procedure
#Include 'datetime.sqc'   !Routines for date and time formatting
#Include 'number.sqc'     !Routines to format numbers
#Include 'stdapi.sqc'     !Update Process API
#Include 'datemath.sqc'   !Date Manipulation functions
#include 'validdt.sqc'    !Date Validations 
!***********************************************************************

I have attached the full code which contains Comments for each calculation.so just download & Create new Process definitions (SQR Report). Please make sure you use -DEBUG flag in Process Override tab, unless you can’t view the output.One interesting thing i have customize two procedure Get_Month_End_Date , Get_Month_Start_Date which will return Month End Date & Month Start Date.

Get Month Start Date & End Date with SQR

 !****************************************************
Begin-Procedure Get_Month_End_Date ( $Dte1, :$Dte2 )
!****************************************************

  do ConvertToComponents($Dte1 , $tmp1_yy , $tmp1_mm , $tmp1_dd)
  let  #tmp1_mm  = to_number($tmp1_mm)
  let  #tmp1_yy  = to_number($tmp1_yy)

  if #tmp1_mm = 2
     move 28 to #MonthDays1
     if(((mod(#tmp1_yy,4) = 0) and (mod(#tmp1_yy,100) != 0)) or (mod(#tmp1_yy,400) = 0))
         add 1 to #MonthDays1
     end-if
   else
     if #tmp1_mm = 4 or #tmp1_mm = 6 or #tmp1_mm = 9 or #tmp1_mm = 11
         move 30 to #MonthDays1
     else
         move 31 to #MonthDays1
     end-if
   end-if

   let $MonthDays1 = to_char(#MonthDays1)
   let $E_DT_tmp1 = $tmp1_yy  || $tmp1_mm  || $MonthDays1 || '0000'
   do Format-DateTime($E_DT_tmp1 , $Dte2, {DEFCMP}, '', 'native')

End-Procedure

!******************************************************
Begin-Procedure Get_Month_Start_Date ( $Dte1 , :$Dte2 )
!******************************************************

  do ConvertToComponents($Dte1 , $tmp1_yy , $tmp1_mm , $tpm1_dd)

  let $B_DT_tmp1 = $tmp1_yy  || $tmp1_mm  || '01' || '0000'
  do Format-DateTime($B_DT_tmp1 , $Dte2, {DEFCMP}, '', 'native')

End-Procedure 

!How to Use Them , an example.
!Get_Month_Start_Date
do Get_Month_Start_Date($AsOfToday, $BgDt)
#DEBUG SHOW 'Month Begin Date -> ' $BgDt

!Get_Month_End_Date	
do Get_Month_End_Date($AsOfToday, $EdDt)
#DEBUG SHOW 'Month End Date -> ' $EdDt
Download the full code Date Calculation

My Output file looks like

Date Calculation Process Output File
Date Calculation Process Output File

Hope this helps, Please let me know your problems,suggestions etc.

Categories
SQR

Read File Using SQR and Insert data into Table PeopleSoft

I was working on SQR code that will read CSV file with SQR Code and Insert data into Staging Table.This blog post will help you to understand How to do Read file with SQR.I hope you are aware of SQR File Reading , Writing & open commands, if you are not then please read the Hyperion SQR Command on Oracle Docs.Here is the Links ( Opens in New Window) for you.

So Lets look the CSV file along with structure,columns etc.

2013_11_04_01
CSV File Structure

Here is the Full SQR Code

I have also attached the Sample CSV file & Full SQR Code

 !-----------------------------------------------------------------------------------------------!
!  Report Name:  FILEREAD.SQR -- Run Via Process Schedular SQR Process (API Aware)              !
!  Report Descriptions: This SQR Program will read file data & insert into staging table         !
!  Report Create Date: 29-October-2013                                                             !
!  Report Run Component: PRCSMULTI                                                                !
!-----------------------------------------------------------------------------------------------!

#include 'setenv.sqc'      !Set environment

!******************
Begin-Program
!******************
     do Init_Report
     do Main_Report
     do Stdapi-Term
end-program

!****************************
Begin-Procedure Init_Report
!****************************
     Do Stdapi-Init
     Do Truncate_Tables
end-procedure

!***************************
Begin-Procedure Main_Report
!***************************
    Do Process_Input_File

End-Procedure

!**********************************
Begin-Procedure Process_Input_File
!**********************************
    !Set the File Path
    Let $OpenFileName = 'E:\AppEngineFiles\GBI_29October2013.csv' !Please Change the File Path
    Open $OpenFileName As 1 For-Reading Record=3000:Vary Status=#FileStatus
        !Checking For File Status
        If #FileStatus != 0
            display $OpenFileName noline
            display 'Open Failed'
        Else 
            !Read Input File
            Do Read_Input_File
        End-If
End-Procedure

!***************************************
Begin-Procedure Read_Input_File
!***************************************
#debug show 'Entering Read_Input_File'

    !First We need to skip the headings
    Read 1 into $Record:3000

    Move 0 to #Total_Record
    Move 0 to #Total_Inserted_Record

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

        If #end-file !When End-File then Break
            Break
        End-If

        Add 1 to #Total_Record     
        If #Read_Stat <> 0 !SQR returns zero if the read is successful
                #debug show 'Bad return from the Read command, errno= ' #Read_Stat
                #debug show ' Record # = ' #Total_Record
                #debug show 'Could not read record # ' #Total_Record
        Else
                Add 1 to #Recs_Rej
                !#debug show 'Calling Process-Input-Record to process record # ' #Total_Record
                Do Process_Input_Record 
        End-If

    End-While
    !At this point all records are read from the file
    Close 1
    Let $Total_Rec_Inserted = to_char(#Total_Inserted_Record)
    #debug show 'Total records inserted:  ' $Total_Rec_Inserted
End-Procedure

!************************************
Begin-Procedure Process_Input_Record
!************************************
    !We are doing Unstring
    Unstring $Input By ',' Into $Emplid $Emplname $Country $NidDescr $Nid $primaryNid 
    Do Insert_Data_Into_Tmp_Table
End-Procedure

!*******************************
Begin-Procedure Truncate_Tables
!*******************************
    !Before Insertion, We need to Truncate The Staging Table
    Let $TruncateCommand = '{PTTRUNCATE}'
    BEGIN-SQL  On-Error=SQL-Error
            [$TruncateCommand] PS_UD_TMP_DATA
    END-SQL
End-Procedure

!*****************************************
Begin-Procedure Insert_Data_Into_Tmp_Table
!*****************************************

Begin-SQL On-Error=Insert_Error !Using Insert Error Procedure ( Duplicate Check )

    !When your program finishes without errors, Commitis performed by SQR automatically.

    Insert Into PS_UD_TMP_DATA 
    ( EMPLID,EMPLNAME,COUNTRY,DESCR,NATIONAL_ID,PRIMARY_NID )
    Values
    ( $Emplid,$Emplname,$Country,$NidDescr,$Nid,$primaryNid )

End-Sql

    If #sql-count=1
        Add 1 to #Total_Inserted_Record
    Else
        #debug  Show 'No rows inserted'
    End-If

End-Procedure

!****************************
Begin-Procedure Insert_Error
!****************************
    If #sql-status = -9 ! if duplicate (this code is for ORACLE only)
        #debug Show 'Insert Error: Duplicate row is not allowed for emplid=' $Emplid
    Else
        #debug Show 'Insert Error: ' $sql-error
        #debug Show 'Error number: ' #sql-status
        Stop ! Halt Program and Rollback 
    End-If
End-Procedure

!***********************************************************************
! Standard Include Files
!***********************************************************************
#Include 'datetime.sqc'   !Routines for date and time formatting
#Include 'number.sqc'     !Routines to format numbers
#Include 'stdapi.sqc'     !Update Process API
!***********************************************************************

I’m not explain the full code, i request you to read the code carefully hope you will be understand it easily.

For Process Definitions i have made Process Type: SQR Process
SQR Process Definations
SQR Process Definations
Debug
Debug

My Staging Table Looks like

 CREATE TABLE PS_UD_TMP_DATA (EMPLID VARCHAR2(11) NOT NULL,
   EMPLNAME VARCHAR2(50) NOT NULL,
   COUNTRY VARCHAR2(3) NOT NULL,
   DESCR VARCHAR2(30) NOT NULL,
   NATIONAL_ID VARCHAR2(20) NOT NULL,
   PRIMARY_NID VARCHAR2(1) NOT NULL) TABLESPACE HRLARGE STORAGE
 (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0)
 PCTFREE 10 PCTUSED 80
/
SQL Record App Designer View
SQL Record App Designer View
Record Type is SQL Table

After Successfully completed the process , Output File Looks like.

Process Output File
Process Output File

Running Select Statement on Staging Table

Querying the Staging Table
Querying the Staging Table

Please let me know your feedback and if you think it can be improved by your valuable suggestions.

Download The Full Code
FILEREAD SQR Code
Sample CSV File
Categories
SQR

Generate Multiple Report with Unique Name in PeopleSoft SQR

From last couple of days i was working on SQR Master Details report and also contains requirements to create a more than one report simultaneously based on the common data.After searching on Peoplebook as well Hyperion SQR Manual i found the solutions. So, today i’m sharing the solutions how to proceed it.

I’m describing this blog post pretty elaborate way so you will get fully understand. 🙂

How can we create multiple reports?

  •     Use DECLARE-REPORT to declare one or more reports to be produced in the application.
  •     You must use this command when developing applications to produce more than one report.
  •     This DECLARE-REPORT Command should be use in the SETUP i.e Begin-Setup section.
  •     It includes name of the Report/Layout/Printer.
  •     The name of the Layout and type of the report may different  that depends on the business requirement.
  •     If no printer type is specified, the default is LINEPRINTER.
  •     if no Layout is specified, the default Layout is used.
  •     Valid values for the Printer-Type arguments are HT-HTML, HP-HPLASERJET,PS-POSTSCRIPT, LP-LINEPRINTER

So Declare-Report Command Looke Like

 DECLARE-REPORT report_name

[TOC=toc_name]

[LAYOUT=layout_name]

[PRINTER-TYPE=printer_type]

END-DECLARE

Arguments

report_name

The report name.

TOC

The name of the Table of Contents.

LAYOUT

The layout name. If none is specified, the default layout is used.

PRINTER-TYPE

The type of printer used. If none is specified, the default is the LINEPRINTER. If no DECLARE‑PRINTER is specified, DEFAULT-LP is used. Valid values for PRINTER-TYPE are HT, HP, PD, PS, LP, HTML, HPLASERJET, POSTSCRIPT, and LINEPRINTER.

So first i am declaring the layout like that

 !DECLARE-LAYOUT should be usd in SETUP section.
    DECLARE-LAYOUT RPT_LAYOUT_A
		paper-size={PAGE_PAPER_SIZE}
		orientation = LANDSCAPE
		line-height=9
		char-width=2
    END-DECLARE 

then create the report defination with DECLARE-REPORT command like that and use the previously defined Layout Name RPT_LAYOUT_A

 !DECLARE-REPORT produced one or more reports and should be use in the SETUP section.
	DECLARE-REPORT RPT_A
		LAYOUT=RPT_LAYOUT_A
	END-DECLARE

As you can see the below whole snippets i have use 5 Layout along with 5 Report. So i general terms this MULRPT.SQR will create 5 output in Process Output Directory.

Process Output Directory Generate 5 Reports
Process Output Directory Generate 5 Reports

So next process i have created Different Heading Section for all reports as it is required.

 
!**************
! For Report A
!**************
BEGIN-HEADING 1 FOR-REPORTS=(RPT_A)
	PRINT 'This is Report A' (1) CENTER
END-HEADING

!**************
! For Report B
!**************
BEGIN-HEADING 1 FOR-REPORTS=(RPT_B)
  PRINT 'This is Report B' (1) CENTER
END-HEADING

!**************
! For Report C
!**************
BEGIN-HEADING 1 FOR-REPORTS=(RPT_C)
  PRINT 'This is Report C' (1) CENTER
END-HEADING

But i have use Common Footer for all

 !**** Common Footer all Report *****************
BEGIN-FOOTING 3 FOR-REPORTS=(RPT_A,RPT_B,RPT_C,RPT_D,RPT_E)
	ALTER-PRINTER
		FONT = 3
		POINT-SIZE =13
    PAGE-NUMBER (1,1) 'Page - '
END-FOOTING

RPT_A,RPT_B,RPT_C,RPT_D,RPT_E Use Same Footer.

So here is the most important section. I’m using getlogou.sqc to get Process Output directory and making new file name with some naming conventions that i want to see in process output directory, then use USE-REPORT for using the report, write some text on that and finally Using New-Report command To close the current report output file and open a new one with the specified file name.

 Do get-logoutputdir-value !Get Process Output Directory
		
		if (rtrim($prcsoutputdir,' ') <> '')
			
			Let $NewFileFileSuffixValue = edit($AsOfToday,'DDMonthYYYY')
			
			Let $GenNewFileA = $prcsoutputdir || 'Report_A' || '_' || $NewFileFileSuffixValue || '.pdf'
			Let $GenNewFileB = $prcsoutputdir || 'Report_B' || '_' || $NewFileFileSuffixValue || '.pdf'
			Let $GenNewFileC = $prcsoutputdir || 'Report_C' || '_' || $NewFileFileSuffixValue || '.pdf'
			Let $GenNewFileD = $prcsoutputdir || 'Report_D' || '_' || $NewFileFileSuffixValue || '.pdf'
			Let $GenNewFileE = $prcsoutputdir || 'Report_E' || '_' || $NewFileFileSuffixValue || '.pdf'
			
			Use-Report RPT_A
			
			!To close the current report output file and open a new one with the specified file name
			New-Report $GenNewFileA
			PRINT 'This is the A Multiple Report' (1,1)

Same thing happening for all others reports.

Process Defination
Process Defination
Server Selection
Server Selection
Most Important: You need to specified at Override Tab, else multiple report will not gerenate
Most Important: You need to specified at Override Tab, else multiple report will not gerenate

So here is the Whole Code Snippet, Just Download it and put it to sqr folder i.e PS_HOME/sqr then Create Proess Name MULRPT.SQR as SQR Report

Please Put -MR 5 at the Override Options Tabs in *Parameter List: (Append )
*Parameter List Append -MR 5

See the results in process Monitor.

Please let me know if any problems , suggestion and correction & Hope this help you 🙂

 
!-----------------------------------------------------------------------------------------------!
!  Report Name:  MULRPT.SQR -- Run Via Process Schedular SQR Report (API Aware)                 !
!  Report Descriptions: Generate Multiple Reports with Unique Name in SQR			!
!  Report Create Date: 	4-Oct-2013								!
!  Report Run Component: PRCSMULTI								!
!  Report Process Group: HRALL									!	
!  Note: To make the process scheduler aware of multiple reports in the Parameter section of 	!
!        Process Scheduler specify "-MR n" without quotes. Here, MR indicates 			!
!		 Multiple Reports and 'n' is a number between 2 to 99 ( limit upto 99 )		!
!		 indicating to the  Process Scheduler the number                                !
!                of reports generated by the SQR report.                                        !
!-----------------------------------------------------------------------------------------------!

#include 'setenv.sqc'    ! set Default environment
#define PAGE_PAPER_SIZE (A4)

Begin-Setup

    !DECLARE-LAYOUT should be usd in SETUP section.
    DECLARE-LAYOUT RPT_LAYOUT_A
		paper-size={PAGE_PAPER_SIZE}
		orientation = LANDSCAPE
		line-height=9
		char-width=2
    END-DECLARE

    DECLARE-LAYOUT RPT_LAYOUT_B
		paper-size={PAGE_PAPER_SIZE}
		orientation = LANDSCAPE
		line-height=9
		char-width=2
    END-DECLARE

    DECLARE-LAYOUT RPT_LAYOUT_C
      paper-size={PAGE_PAPER_SIZE}
	  orientation = LANDSCAPE
	  line-height=9
	  char-width=2
	END-DECLARE

	DECLARE-LAYOUT RPT_LAYOUT_D
      paper-size={PAGE_PAPER_SIZE}
	  	orientation = LANDSCAPE
		line-height=9
		char-width=2
	END-DECLARE

	DECLARE-LAYOUT RPT_LAYOUT_E
      paper-size={PAGE_PAPER_SIZE}
	  orientation = LANDSCAPE
	  line-height=9
	  char-width=2
	END-DECLARE

	!DECLARE-REPORT produced one or more reports and should be use in the SETUP section.
	DECLARE-REPORT RPT_A
		LAYOUT=RPT_LAYOUT_A
	END-DECLARE

	DECLARE-REPORT RPT_B
		LAYOUT=RPT_LAYOUT_B
	END-DECLARE

	DECLARE-REPORT RPT_C
		LAYOUT=RPT_LAYOUT_C
	END-DECLARE

	DECLARE-REPORT RPT_D
		LAYOUT=RPT_LAYOUT_D
	END-DECLARE

	DECLARE-REPORT RPT_E
		LAYOUT=RPT_LAYOUT_E
	END-DECLARE

END-SETUP

!*****************************************
! Header & Footer Declation for All Report
!*****************************************

!**************
! For Report A
!**************
BEGIN-HEADING 1 FOR-REPORTS=(RPT_A)
	PRINT 'This is Report A' (1) CENTER
END-HEADING

!**************
! For Report B
!**************
BEGIN-HEADING 1 FOR-REPORTS=(RPT_B)
  PRINT 'This is Report B' (1) CENTER
END-HEADING

!**************
! For Report C
!**************
BEGIN-HEADING 1 FOR-REPORTS=(RPT_C)
  PRINT 'This is Report C' (1) CENTER
END-HEADING

!**************
! For Report D
!**************
BEGIN-HEADING 1 FOR-REPORTS=(RPT_D)
  PRINT 'This is Report D' (1) CENTER
END-HEADING

!**************
! For Report E
!**************
BEGIN-HEADING 1 FOR-REPORTS=(RPT_E)
  PRINT 'This is Report E' (1) CENTER
END-HEADING

!**** Common Footer all Report *****************
BEGIN-FOOTING 3 FOR-REPORTS=(RPT_A,RPT_B,RPT_C,RPT_D,RPT_E)
	ALTER-PRINTER
		FONT = 3
		POINT-SIZE =13
    PAGE-NUMBER (1,1) 'Page - '
END-FOOTING

!***** Heading & Footing Section Completed ************

!*****************
Begin-Program
!****************
	 do Init_Report
	 do Main_Report
	 do Stdapi-Term
end-program

!****************************
Begin-Procedure Init_Report
!****************************
	 Do Stdapi-Init
	 Do Init-DateTime
	 Do Init-Number
	 Do Get-Current-DateTime
end-procedure

!*****************
Begin-Procedure Main_Report
!****************

		Do get-logoutputdir-value !Get Process Output Directory

		if (rtrim($prcsoutputdir,' ') <> '')

			Let $NewFileFileSuffixValue = edit($AsOfToday,'DDMonthYYYY')

			Let $GenNewFileA = $prcsoutputdir || 'Report_A' || '_' || $NewFileFileSuffixValue || '.pdf'
			Let $GenNewFileB = $prcsoutputdir || 'Report_B' || '_' || $NewFileFileSuffixValue || '.pdf'
			Let $GenNewFileC = $prcsoutputdir || 'Report_C' || '_' || $NewFileFileSuffixValue || '.pdf'
			Let $GenNewFileD = $prcsoutputdir || 'Report_D' || '_' || $NewFileFileSuffixValue || '.pdf'
			Let $GenNewFileE = $prcsoutputdir || 'Report_E' || '_' || $NewFileFileSuffixValue || '.pdf'

			Use-Report RPT_A

			!To close the current report output file and open a new one with the specified file name
			New-Report $GenNewFileA
			PRINT 'This is the A Multiple Report' (1,1)

			Use-Report RPT_B

			!To close the current report output file and open a new one with the specified file name
			New-Report $GenNewFileB
			PRINT 'This is the B Multiple Report' (1,1)

			Use-Report RPT_C

			!To close the current report output file and open a new one with the specified file name
			New-Report $GenNewFileC
			PRINT 'This is the C Multiple Report' (1,1)

			Use-Report RPT_D

			!To close the current report output file and open a new one with the specified file name
			New-Report $GenNewFileD
			PRINT 'This is the D Multiple Report' (1,1)

			Use-Report RPT_E

			!To close the current report output file and open a new one with the specified file name
			New-Report $GenNewFileE
			PRINT 'This is the E Multiple Report' (1,1)

		end-if

end-procedure

!***********************************************************************
! Standard Include Files
!***********************************************************************
#Include 'curdttim.sqc'   !Get-Current-DateTime procedure
#Include 'datetime.sqc'   !Routines for date and time formatting
#Include 'number.sqc'     !Routines to format numbers
#Include 'stdapi.sqc'     !Update Process API
#Include 'datemath.sqc'   !Date Manipulation functions
#Include 'timemath.sqc'   !Time Related Calculatin
#include 'getlogou.sqc'   !get current logoutput directory
#include 'reset.sqc'      !Reset Printer Show End of Report
!***********************************************************************
Categories
SQR

How to Get Process Name by Process Instance Using PeopleSoft SQR

When i was working on reportlog.sqc Projects then i have found really interesting tips that is Getting Process Name by process instance.
So lets tell the procedure.We all know the all process related information PeopleSoft Stored on PSPRCSRQST table. You can easily look the table data along with column name by using

  SELECT * FROM PSPRCSRQST;

here is the results

Query Results
Query Results

PRCSINSTANCE  is the Key Field here.

I am telling the overall scenario before starting main SQR Code snippet section.

Fortunately PeopleSoft Deliver a SQC File called eoprcsnm.sqc , So just include the SQC file

 #include 'eoprcsnm.sqc'

at the bottom of your SQR Programme File. else you can use this if you don’t intend to include another file.

  
!********************************
begin-procedure Get-Process-Name (#prcs_process_instance, :$prcsname)
!********************************
Begin-SELECT 
P.PRCSNAME  
FROM PSPRCSRQST P
WHERE P.PRCSINSTANCE = #prcs_process_instance

End-SELECT

   let $prcsname = RTRIM(&P.PRCSNAME,' ') 

End-Procedure !Get-Process-Name

Here you can see, this procedure take 1 parameter as input (#prcs_process_instance) and return Process Name ($prcsname)

Now lets go to Example how to call them

  
!Get Process Name By Process Instance 
do Get-Process-Name (#prcs_process_instance, $prcsname)

Now again another Example

  
!*******************************************************************************
begin-procedure Get_Runcntl_Defn
!*******************************************************************************

!Get Process Name By Process Instance 
do Get-Process-Name (#prcs_process_instance, $prcsname)

Begin-Select
PNLGRPNAME &COMPONENT
			FROM PS_PRCSDEFNPNL
			WHERE PRCSNAME = $prcsname
End-Select

Begin-Select
PF.RECNAME
PF.FIELDNAME
PF.LBLTEXT

		Let $RecordName = 'PS_' || &PF.RECNAME
		do Get_Runcntl_Page_Value
		FROM PSPNLGROUP PG, PSPNLFIELD PF
			WHERE PG.PNLGRPNAME = &COMPONENT
			AND PF.PNLNAME = PG.PNLNAME
			AND PF.RECNAME <> ' '
			AND PF.FIELDNAME <> ' '
		    AND EXISTS ( SELECT 1
					FROM PSRECFIELD
					WHERE RECNAME = PF.RECNAME
					AND FIELDNAME = 'OPRID'
					AND FIELDNUM = 1
				)
			ORDER BY PF.RECNAME, PF.FIELDNUM
End-Select
End-Procedure

Hope this Helps 🙂
Please let me know your Suggestion or any Correction.

I have also attached the eoprcsnm.sqc

Download the eoprcsnm.sqc
Also you can find it to your PS_HOME/sqr Folder