Writing an SQR output to a custom file and post it to web
The background of the issue is in older versions of PeopleSoft SQR programs used to execute from a client workstation and there was no 4-tier concept, so People used to hardcode the output file path with in the program.
PeopleSoft 8 onwards reports are being posted to web and people can view the reports in the view log trace.
But for custom data files SQR uses the OPEN FOR WRITING and WRITE commands for creating these files and control where the output goes. Since SQR is controlling the output vs the Process Scheduler or PSSQR controlling the output, even if it is written to the right directory, the Report Distribution Server may be unaware of it’s presence and will not transfer and generate a link to view this output.
To overcome this issue one of the following solutions can be adopted.
With PeopleTools 8.4 onwards the following option can be used. Make sure that the supported file extensions (like .pdf, .lis, .txt are used.).
Option 1:
Use the FILEPREFIX variable from the delivered SETENV.SQC to build the directory path for the hard coded file name. Make sure that SETENV.SQC is coded at the top of the program. Otherwise it will give a substitution variable error.
Example:
LET $filename = ‘{FILEPREFIX}’||’bank1.txt’
OPEN $filename AS 1 FOR-WRITING RECORD=85:FIXED
Option 2:
Use the following code format.
Example:
let $filepath = ‘[PS_HOME]\appserv\prcs\[databasename]\log_output\SQR_’ || $ReportID || ‘_’ || $prcs_process_instance || ‘\’
let $filename = $filepath || ‘datafile.lis’
open $filename as 1 for-writing record=100
if the above code doesn’t work for you, you can do one more thing.
You can query PSPRCSPARMS table for PRCSOUTPUTDIR field for your $prcs_process_instance and substitute output value as your file path
Example:
SELECT PRCSOUTPUTDIR FROM PSPRCSPARMS WHERE PRCSINSTANCE = $prcs_process_instance