SQR Generate Outbound CSV File & Send Email Attachment

SQR Generate Outbound CSV File & Send Email  Attachment

Introduction:

The requirement of the document is to generate an outbound CSV file for the

business data stored in the database and need to send an email to user with attached

new CSV file.

Document Summary:

This document briefly describes how to open and write heading and the main data in

to the CSV and the email the file to the user as an attachment using “PSMAIL.EXE”.

The PeopleSoft Process Scheduler Recurrence has been used to schedule the SQR

report to email the file to the user on daily basis.

STEP 1: Open File to Write

This Procedure opens the file "TopRunSQL_Report<CurrentDate>.csv" in the folder

tmp

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

*******

! Begin-Procedure open-file

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

*******

Begin-Procedure open-file

let $sys_date=$current-date

let $OutputFile = '/tmp/TopRunSQL_Report'||$sys_date|| '.csv'

Open $OutputFile as 1 For-Writing Record=5000

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

*******

End-Procedure open-file

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

*******

STEP 2: Write Heading

This procedure writes the heading details:

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

******

Begin-Procedure write-heading

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

******

LET $Write_Job = 'SQL_TYPE'

||'|'||'LAST_LOAD_TIME'

||'|'||'ELAPSED_TIME_IN_S'

||'|'||'ELAPSED_TIME_PER_SQL_IN_S'

||'|'||'EXECUTIONS'

||'|'||'HASH_VALUE'

||'|'||'MODULE'

||'|'||'PIECE'

||'|'||'SQL_TEXT'

write 1 from $Write_Job

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

******

End-Procedure !write-heading

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

******

STEP 3: MAIN-SELECT

This procedure retrieves the data from the table PS_LN_LONGRUNSQL

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

*********

Begin-Procedure Process-main

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

*********

BEGIN-SELECT

A.LN_SQLTYPE

A.LN_LASTLOAD_TIME

A.LN_ELAPSTIMEIN_SEC

A.LN_ELAPSTIMPER_SEC

A.LN_EXECUTIONS

A.LN_HASHVALUE

A.LN_MODULE_LNGRN

A.LN_PIECE

A.SQL_TXT_LNGRNSQL

move &A.LN_SQLTYPE to $SHORT_SQL

move &A.LN_LASTLOAD_TIME to $last_load_time

move &A.LN_ELAPSTIMEIN_SEC to $elapsed_time_in_s

move &A.LN_ELAPSTIMPER_SEC to $ELAPSED_TIME_PER_SQL_IN_S

move &A.LN_EXECUTIONS to $executions

move &A.LN_HASHVALUE to $hash_value

move &A.LN_MODULE_LNGRN to $module

move &A.LN_PIECE to $piece

move &A.SQL_TXT_LNGRNSQL to $sql_text

do Write_To_File

FROM PS_LN_LONGRUNSQL A

END-SELECT

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

*********

End-Procedure !Process-main

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

*********

STEP 4: Write_To_File

This procedure writes the retrieved data to the CSV file

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

******

Begin-Procedure Write_To_File

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

******

let $Values = $SHORT_SQL

||'|'||$last_load_time

||'|'||$elapsed_time_in_s

||'|'||$ELAPSED_TIME_PER_SQL_IN_S

||'|'||$executions

||'|'||$hash_value

||'|'||$module

||'|'||$piece

||'|'||$sql_text

write 1 from $Values

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

******

End-procedure !Write_To_File

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

******

STEP 5: Send Email with Attachment

This procedure email the report to the user as an attachment

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

******

Begin-Procedure SendEmail

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

******

let $EMAIL= 'toemailid@abc.com'

let $curr_date = $current-date

let $file = '/tmp/TopRunSQL_Report'||$curr_date|| '.csv'

let $subject = 'FEA TOP RUN SQL Report'||$curr_date

let $newline = chr(13)

let $linefeed = chr(10)

LET $BODY = 'Hi,'||$newline|| ' Please find the attached FEA Long RUN SQL report. '

let $alias ='TopRunSQL_Report'||$curr_date|| '.csv'

Let $system_command ='psmail -TO"'||$EMAIL||'" -SUBJECT"'||$subject||'" -

BODY"'||$BODY||'" -FILE"'||$file||'" -ALIAS"'||$alias||'"'

show 'EMAIL INFO :' $system_command

Call system using $system_command #call_status

if #call_status <> 0

show ' '

show 'Error Sending Mail'

show '=================='

show #call_status

show $system_command

show ' '

end-if

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

*********

End-Procedure !SendEmail

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

*********

References:

1. Project Work
2. People Books
3. Google