Bruce Knox    Database Programmer/Analyst                                                                                date last updated 02/13/14

 

This page is intended for users of  SCT's Banner Finance Software Only!

These are programs that we use to send reports (to the application manager) of Approval Queue Errors via email.  

 

The UNIX scripts and SQL*Plus scripts for Approval Notices are highly integrated.  Email and scheduling are both UNIX.  Also, the SQL*Plus program scripts use UNIX commands that are run by using the HOST statement.  

 

We use the HOST abbreviation of ! for our IBM RS6000.  Other servers may use the $ for this abbreviation.

If you are not on a UNIX server, then the underlying statements must be converted as well.  Most of these are just sending information to the user while running the script.  However, the programs also remove files and the output files are changed via UNIX commands.

 

This application sends email reports to manager of the Approval Notices system errors.   One reports documents that cannot be approved due to being out-of-period.  A second reports errors that would cause the Approval Notices system to fail to send a notice.   (We have some other monitoring too, but that tells the application manager that an approver is taking too long to approve/disapprove a document.)

 

We run this job once a week.  If no errors are found, no report will not be mailed to the manager.

 

The email looks something like:

From: <common@edison.uaex.edu>
To: <bpowell@uaex.edu>
Date: 3/18/03 5:39AM
Subject: Requisitions Pending with Errors 

Approvals Pending for Approver with Errors - Unapproved But Out of Period 

         FOBAINP     FOBUAPP     FORAQUS 
         ACTIVITY    ACTIVITY    USER_ID 
Document DATE        DATE        APPR       Queue Level Seq

R0002347 11-FEB-2003 21-FEB-2003 RRODIBAUGH FFCS    2    3
R0002430 17-FEB-2003 17-FEB-2003 RBOCK      FGRA    2    1
R0002544 27-FEB-2003 10-MAR-2003 MFRENCH    7000    1    3
3 rows selected.

Approvals Pending for Approver with Errors - Unapproved But Will Not be Sent to Approver 

         FOBAINP     FOBUAPP     FORAQUS 
         ACTIVITY    ACTIVITY    USER_ID 
Document DATE        DATE        APPR       Queue Level Seq

R0002677 07-MAR-2003 07-MAR-2003 DJOHNSON   7800    1    1
R0002678 07-MAR-2003 10-MAR-2003 MFRENCH    7000    1    3
R0002686 10-MAR-2003 13-MAR-2003 MFRENCH    8300    2    2
3 rows selected.

The first date is the Requisition Creation Date (or perhaps Modification Date). 
The second date indicates pending the current Approver.

CC: <bknox@uaex.edu>

The following scripts and comments explain: 

 

approval_errors.shl  Checks for Requisitions Pending with Errors.

Runs approval_queue_errors.sql

 

approaval_errors.shl is run by cron according to the crontab entry:

38 5 * * 2 /home/common/approval_errors.shl > approval_errors.log 2>&1

Runs the job at 5:38 a.m. every Tuesday.  The timing is just to be out of office hours.  Once a week is probably a good frequency unless you have many errors.

 

This crontab entry logs approval_errors.shl standard and error messages to approval_errors.log.  This is especially useful since no report will go to the manager if no errors are detected.  The log file will always be made if the job is run by cron.

 

approval_errors.log  is a typical log file from the job.

 

approval_queue_errors.sql  Generates the 'Requisitions Pending Approval with Errors' email Report.  This script was literally created from cutting and pasting components of approval_gather2.sql and approval_periodic2.sql from the Approval Notices email System.

approval_queue_errors.shl is typical of the file containing the mail command created by the job.  Running this file sends the mail via UNIX.

The three primary causes for errors in the Approvals are:

1. Transaction date in a closed period.  We are handling these in Banner by disapproving and having the transaction date changed when reentered.  (Sure, we could just fix them.  But disapproving an item that is already waiting for 10 days or more rather sends a message of its own:)

2. Document routing is set up incorrectly or missing.  Usually this will be a new Fund or Orgn that has not been included in document routing yet. These should get rare once your procedures are in place to catch them when setting up new/changed Funds and Orgns.

3. Changes in approvers.  If we make a change to the approval queue after a document is created, the approval table is not updated.  Again disapproving the document will work.

Since the manager of the Approval setup will be getting these error reports, that manager will have a head start on fixing any problems.  We may need to search for FOAPAL sometimes to figure out the routing or check the queue to see where we missed someone. Not too much manual labor.

The Approval Notices system that this supports can be found at http://www.uaex.edu/bknox/banner_approval_notices_email.htm

Disclaimer

Use this information and these scripts at your own risk. As a condition of using these scripts and information from this site, you agree to hold harmless both the University of Arkansas Cooperative Extension Service and Bruce Knox for any problems that they may cause or other situations that may arise from their use, and that neither the Extension Service nor I will be held liable for those consequences.  The scripts and information are provided "as is" without warranty, implied or otherwise.  Limitation of liability will be the amount paid to the University of Arkansas specifically for this information. (It was free:)