The only aspect of an SQR program that must be unique is its file name. The file name may also indicate something about the program within. There are restrictions on SQR program file names in the Peoplesoft environment, but there’s more flexibility than meets the eye.
Peoplesoft includes SQR program files with names like PER099.SQR and BEN100.SQR. The name has a meaningful prefix (PERsonnel or BENefits) and a meaningless serial number. Organizations that modify these programs sometimes differentiate the new files with a suffix, for example, PER099A.SQR.
Organizations that create new programs sometimes keep the meaningful prefixes and choose serial numbers well above the delivered range. Alternately, they choose a standard differentiator, starting or ending the name with X or some more meaningful letter(s).
Some organizations create their own style of names to differentiate their program files. They could reverse the serial number and the prefix; 099PER.SQR. Or, each file name can be some combination of letters that tries to describe the program within, like a directory of vanity license plates. Part of the difficulty is that Peoplesoft limits SQR program file names to eight characters, right?
One option to set the custom program files apart and differentiate among them is to start the names with the digits zero (0) through nine (9). There are no delivered SQR program files with those first characters, at least in Peoplesoft HCM. They are distinctly in-house files, and our organization can establish ten categories for them. Those files will sort to the top of a directory listing. Their process definitions will sort to the top of the Application Designer’s “insert object into project” dialog box
For example, a file name like “0abcdefg.sqr” says something like “this is an SQR program developed in-house that deals with the payroll module” in the extension and the first character, leaving seven more characters to elaborate.
Process and Process Type
The Peoplesoft Process Scheduler uses process type definitions and process definitions to run SQR programs.
Process types are stored in the PS_PRCSTYPEDEFN table. Oracle Corporation delivers that table with three process types for SQR: SQR Process, SQR Report, and SQR Report for WF Delivery (WF probably means “work flow”). Each process type has rows for several combinations of operating systems and databases. Most process types have commands (the executable program) and parameter lists.
Processes are stored in the seven tables: PS_PRCSDEFN, PS_PRCSDEFNCNTDIST, PS_PRCSDEFNGRP, PS_PRCSDEFNLANG, PS_PRCSDEFNNOTIFY, PS_PRCSDEFNPNL, and PS_PRCSDEFNXFER. They all have key fields PRCSTYPE and PRCSNAME. There are many interesting functions controlled by the process definition – two that we’ll discuss below; there is a thirty character description, and a process can replace or supplement the command line parameters from its process type.
Longer SQR File Names
The process name field (PRCSNAME) is twelve characters long, but Peoplesoft allows only Application Engine and Optimization Engine processes to be that long. All other processes, including SQR programs, are limited to eight characters … as delivered.
The only place that limit is enforced is on the process definition search page. The SearchSave Peoplecode in the PRCSDEFN record, PRCSNAME field has a line that reads:
If &type <> 5 Then
The commands inside the if statement reject the process name if it exceeds eight characters. The “5” value refers to Application Engine and Optimization Engine processes. SQR processes have a value of “1.” We can change this line to:
If &type <> 1 And &type <> 5 Then
As always, Peoplecode changes should be made carefully in a development environment and tested before migrating to production. You should have a safe “backup” copy of the original Peoplecode in your demo environment. You make any change at your own risk.
It worked for me and it should work for you. You will be allowed to use twelve characters in your SQR program file names. If you use the “vanity license plate” naming style, your names can be less cryptic.
Custom Process Types
Peoplesoft allows us to create process types, but on the surface, there’s no reason to bother. In the past ten years, every SQR I wrote was fine running as an “SQR Report.”
The process type appears on the Process Scheduler Request page of the run control component. Peoplesoft Process Monitor devotes a column to it. Its bland, generic values report information of little or no value. We can create new process types whose definitions, except for the name, are identical to “SQR Report.” The Peoplesoft Process Monitor can display process types like “Payroll SQR” or “Nightly Housekeeping” (we have thirty characters).
We might choose to have several process definitions with different process types for the same SQR program. Those process types could have different command line parameters, either PSSQR.EXE flags or data for our programs. Some possibilities are “Regular SQR”, “Debug Mode” (debug flag), “Alternate DBMS” (connect string),” Companywide” (use #ifdef to omit row level security from begin-select), or “Dept Secure”.
This doesn’t enlarge the SQR process name or file name, but it allows us to move some of the information out of the name, leaving more room for the other information. It also makes the features that select processes by process type, in the Process Monitor and Application Designer, more useful.
Thirty Character File Names
The process type definition contains the command line parameter that names the SQR file that PSSQR.EXE is to compile and execute. It assumes the SQR file name is the name of the process with an “.sqr” extension. We could leave the file name parameter off the process type definition and put the actual file name in the process definitions. That should free us to make the file name and extension anything we want.
The process definition record defaults the description to the process name. We could write a long file name in that field and use FieldChange Peoplecode to copy it to the command line parameter field. I haven’t tried it – twelve characters has been enough so far – but you could. You will be allowed to use thirty characters in your SQR program file names. They will start to look like Microsoft Office documents.
The choice of processes offered by the Process Scheduler Request page includes the thirty character description from the process definitions. Unfortunately, the thirty characters wouldn’t display in the Peoplesoft Process Monitor – unless we start to modify that too.
I have always been curious as to why the file names have to be in lowercase when we upload them to UNIX servers? I would approeciate if you could solve this mystery?
I don’t think file names have to be lower case to upload to UNIX; it may just be a tradition. UNIX file names are case sensitive, which means a single directory could contain BEN100A.SQR, BEN100a.SQR, ben100a.sqr, and many other combinations as different files. It’s not always a good idea to use all the flexibility an operating system gives us. If we had all those variations of BEN100A.SQR, how would we remember which was which? Also, PSSQR.EXE takes the process name (BEN100A) as a parameter and somehow opens the ben100a.sqr file. The PSSQR command line seems to be case insensitive, which means we’d better not have program file names that differ only by case. There also may be some FTP (or is it ftp?) programs whose command lines are case insensitive or that adjust the case of the files they handle.
How can to kick off another SQR process from within a SQR?
Check my article, Never Fear A Database Refresh Again.