PeopleSoft Distribution System

Introduction

The PeopleSoft Distribution System provides a convenient method for managing user permissions in Sybase and the automatic distribution of reports written in Sybernet.

User permissions include permissions on the stored procedure and row level security on a range of department ID's. Execute permission is granted and row level security is automatically assigned when a report is defined for a user. Execute permission is revoked and row level security is disabled when a report is deleted from the system. Note that execute permission is not revoked if other entries in the system allow this. Similarly, row level security is deleted only on those department ID's that are not otherwise specified.

The automatic distribution of reports is accomplished by using Sybercron to add or delete entries in the CRON table. Reports can be mailed electronically or printed to any postscript printer defined for SPOCK or KIRK.

Getting Started

Two windows or frames are created when the Distribution System is invoked. The left window is a directory of applications or user names. You can specify which directory you wish to see by selecting that choice from the drop-down list at the top of this window. The list of available options include:

Applications
Registered Users
All Employees

The default is Applications which lists all reports that are registered for the current database, usually HRPRD. The check box next to each application name is checked if there are users currently defined for that report.

The list of Registered Users lists all employees by name that are currently receiving reports or have access to reports in this system. All check boxes next to these names will be checked since this list only shows those employees defined to the system.

You can also select All Employees whether or not they are defined in to the system. This makes it possible to quickly locate any employee name. Again, the check box next to each name is checked if that employee is known to the system.

There are other ways to select an employee. This will be discussed shortly.

Report Identification

The right window is initially blank. When an application or employee is selected from the directory in the left window, a form screen appears representing that selection. Buttons on the bottom of this screen allow you to scroll forward and backward, save any changes made to this form, add a new entry or delete an entry from the system.

Scrolling depends on which directory you have selected from the left window. By default, scrolling occurs for a given application name since Applications is the default directory. That is, as you scroll forward or backward, each employee defined for that particular report is displayed.

If the directory window is a list of employees, then scrolling occurs for just those applications a particular employee is defined.

The information on this screen defines precisely each report and user that is defined to the system. The following fields are displayed in the upper section of this form:

Application name
Username
EMPLID
Version
Departments
Order

Application name is the name of this application as defined when this procedure was registered with the Sybernet Stored Procedure Register. It is safe to change this description with that facility, should you desire. It is not safe, however, to change the name of the stored procedure.

Username is the PeopleSoft name representing a particular user. You can use this field to search for other employees by entering their last name or their PeopleSoft name (lastname,firstname middlename) and pressing the TAB key. Note that selecting a name in this fashion will change the scrolling so that only the reports for that employee are displayed.

EMPLID is their employee ID. As with the username field, you can search for employees by EMPLID by entering their employee number in this field and pressing the TAB key. Again, this will change the scrolling so that only the reports defined for that employee number is displayed.

Version is used to resolve duplicate entries for the same user getting more than one access to the same report. This value is automatically assigned by the system. Although you may not assign this value, you can change its value to quickly locate a particular version for that user.

Departments are the DEPTID's specified as a range that this user is allowed to see. If specified, these department ID's are automatically added to the row-level security for this user when the form is saved. It makes sense to leave these fields blank if the report does not recognize department numbers.

NOTE: The table that controls row level security does not allow invalid values. Thus, any invalid department numbers you specify in a range of department numbers are not added to this table; however, that range is still a valid range and will be honored if the report is printed through Sybercron.

Order determines how the report is to be sorted. Not every report in the system will recognize this option, but those that do will allow you to specify one of the folowiing options:

DEPTID (Department number)
EMPLID (Employee ID)
NAME (Employee Name)
SSN (Social Security Number)

NOTE: If you attempt to set this option for a report that does not allow itself to be sorted, an appropriate alert is displayed when you go and save this information.

When this information is saved, execute permission on this report is automatically granted. When this information is deleted, execute permission is revoked if no other entries for this report exists.

Sybercron

The next section on this screen optionally defines when this report is to be automatically executed through Sybercron; for example, reports can be automatically distributed every Monday morning at 8 in the morning. The fields in this section allow the following:

Datename
Day
Hour
Minute

Datename specifies a date name mnemonic that determines when this report is executed; for example, WEEKDAY specifies that you want this report delivered each week day (Monday through Friday), but MONDAY says only run this report on Monday. You can also specify every MONTH or a particular month of the year. Month values also require that you specify which day of the month it should be run.

Day is the day of the month this report should be executed. Day is valid only if the Datename is a month of the year or the keyword MONTH. Day is invalid for any other value of Datename and will be nullified if specified.

Hour is the hour of the day in military time to execute this report. Hour is required for all Datenames.

Minute is the minute of the hour that this report is executed. The default, if not specifed, is 0 (on the hour).

Note that Sybercron sleeps for 5 minutes when there is nothing to do. The report is also going to take a while to run. If you want the report to arrive before 8 AM, for example, you will want to specify a time value that accounts for both situations. 7:50AM should make the report available by 8.

Destination

The last section on this form screen determines where this report should be delivered. The default is none which means Sybercron is not used to automatically deliver this report to the user. The allowed values for Destination include the following:

None
E-mail
Printer

None inhibits the execution of Sybercron. This option makes sense if the user does not want to receive this report automatically because they plan to access it directly from the web.

The E-mail option says to electronically mail this report. The E-mail address is automatically determined from the PeopleSoft database for this user and you cannot specify otherwise. If the user changes their E-mail address, their Sybercron entry will have to be modified as well.

The drop-down list to the right of the E-mail option allows you to specify how this e-mail is sent. The options available are:

in-line
attachment
url

In-line causes the report to be delivered directly in-line in the body of the e-mail message. The user, however, will need to have an e-mail capable browser to view in-line HTML reports. Netscape Communicator is such a browser.

Reports can also be sent as attachments. This is convenient if the user prefers this method or they are using an e-mail program that does not recognized HTML in the body of the message. Eudora Pro is such an example.

If you specify the url option, E-mail is sent containing a list of URL's for each department ID. This option not only saves band-width because it suppresses delivery of the report to the user's mail inbox, but also provides a secure environment for the delivery of these reports since only the intended recepient can actually view these reports. The user must have a valid certificate and that certificate must have been published to our Directory server.

With either option, however, it is possible for the programmer to override this option when the application is registered. This would happen if the programmer has created an Excel or RTF document and doesn't want you to specify the content type. This makes sense because Excel and RTF are always sent as attachments since there is no e-mail program that recognize either of these formats.

The Printer option says to print this report on a postscript printer. The list of defined devicenames must be defined to Unix before you can print to this printer. New device names must be added to the table CRON_DEVICENAMES in the HTTP database before they are allowed in this system.

New Employees

When a new employee is added to the PeopleSoft system, a separate process that runs at the end of each business day will add that employee's username to Sybase. This username is then added to the PeopleSoft production database.

    Creating E05688 (Riedel,Alfred F)
    Password correctly set.
    Account unlocked.
    New login created.
    New user added.

By default, new employees will have no access to any of the applications or reports in the distribution system unless that application or report is available to the group public.

Terminated Employees

The same process that adds new employees to Sybase and the Peoplesoft database will also remove usernames when employees are terminated. This process also knows about the Distribution System and will delete any references to that employee from the system. This includes any row-level permissions in dataprotects, scheduled Sybercron jobs in the CRON table, and any entries in the DISTRIBUTION tables.

    Deleting E20242 (Prohaska,Roberta P)
    3 rows were deleted from dataprotects.
    0 rows were deleted from CRON_PARAMETERS.
    0 rows were deleted from CRON.
    1 rows were deleted from DISTRIBUTION.
    User has been dropped from current database.
    Account locked.
    Login dropped.

Programming Conventions

The web applications in PeopleSoft conform to a common naming convention and structure that makes it possible for the Distribution System to invoke these applications with minimum information.

Structure wise all of the applications are self-contained. In other words, there are usually no external stored procedures needed for the application to run. And since these procedures are fairly complex, state information is passed between multiple invocations so the procedure knows exactly what to do next.

Keeping all of the code in one place certainly adds to its bulk. The procedure, however, is not only easier to maintain this way, but also makes it easier to grant and revoke permissions on that procedure since we don't need to be concerned with ansillary procedures we know nothing about.

While it is acceptable for a procedure to execute other stored procedures, it is not acceptable to build form screens or links that reference other stored procedures.

State wise all web applications use the parameter name @BUTTON to determine what state they are in; for example, a NULL value indicates the procedure is initializing. If state information is not required, this parameter name should not used in your application.

The Distribution System assumes some knowledge of the following parameter names:

@BUTTON
@BUTTON should accept the keyword REPORT to mean the entire report should be printed.
If your procedure does not use @BUTTON (such as some of the benefit interface reports), that is okay too because those procedures (when invoked) simply produce a report. It is only required for those procedures that do use @BUTTON.
Some of your procedures are calling sp_html_filter and you are already setting @BUTTON to REPORT so no changes are needed. If you are not calling sp_html_filter then you are probably using one of the frame names to indicate this; for example,
```
        if @BUTTON = "CENTER"
        begin
            /*
            ** Build the Center frame (i.e., do the report).
            */
            ...
        end
```
If this is the case, all you would need to do is add the check for REPORT thusly:
```
        if @BUTTON = "CENTER" or @BUTTON = "REPORT"
        begin
            /*
            ** Build the Center frame (i.e., do the report).
            */
            ...
        end
```
If your procedure builds a form screen to request input from the user, you want to make sure that @BUTTON is set correctly. The easiest way to do this is use the NAME option in your SUBMIT button; however, since the HRPAY server is case-sensitive, this means the value must be REPORT in all uppercase:
```
        select '<INPUT TYPE="SUBMIT" VALUE="REPORT">'
```
This is costemtically unappealing so the alternative is to make @BUTTON a hidden field and omit the NAME option from your submit button:
```
        select '<INPUT TYPE="HIDDEN" NAME="BUTTON" VALUE="REPORT">'
        ...
        select '<INPUT TYPE="SUBMIT" VALUE="Report">'
```
The alternative (which works equally as well and is probably the simpler to implement) is to account for the case-sensitive nature by uppercasing your test on @BUTTON:
```
        if upper(@BUTTON) = "REPORT"
        begin
            /*
            ** Do the report.
            */
            ...
        end
```
The point is the distribution system passes REPORT in all uppercase so this is the value you must detect.
@DEPARTMENT
If your procedure is capable of listing a single DEPTID, you should use this parameter name to accept that value. Those procedures that are calling sp_html_filter are using this convention already and nothing needs to be changed.
The Distribution System automatically detects whether or not your procedure recognizes @DEPARTMENT and invokes it accordingly; for example, if a range of DEPTID's are specified, the procedure is called once for each department in that range, but only if @DEPARTMENT is a real parameter; otherwise the procedure is invoked once without passing this value.
@SORT
If your procedure is capable of ordering the report by more than one column name, you should use this parameter to accept that value. Since I don't think it is reasonable to assume that a procedure capable of ordering by more than one column is going to have every sortable column the same between every other procedure, I will make the assumption that your procedure will at least sort by the following column names:
- DEPTID
- EMPLID
- NAME
- SSN
Thus you also want to use these values when interrogating @SORT.

Again, you just can't write any stored procedure and expect it to run in the Distribution System, but the rules for making it work are fairly straight-forward.