UNIX scripts and SQL*Plus scripts for Banner Approval Notices email

 by Bruce Knox  bknox @t uaex.edu     Oracle Database Programmer/Analyst                                                                   date last updated 03/06/09
This page is intended for users of  SCT's Banner Finance Software Only! 

These are programs that we use to send Approval (needed) Notices 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 pSeries running AIX.  Other servers may use the $ for this abbreviation.

The UNIX scripts must have changes to run.  Change the cd command to work for the directory containing your scripts.  Change the PATH= to your Banner PATH=.

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 Notices to Approvers for Requisitions awaiting their approval.  If the requisition is still waiting for that same approver for over seven days, an additional notice will be sent.  (We have some other monitoring too, but that only goes to the application manager.)

The application is broken into a gather and a send step to allow a very quick (1 second or less)  check ( or gather) of new unapproved items in a queue.  The send step (100 times longer) is only run if the gather step finds new items.

The email (for a simple one) looks something like:

From: <bpowell @ banner.uaex.edu>
To: <bray @ uaex.edu>
Date: 3/19/03 11:10AM

 

Subject: Requisitions Awaiting Your Approval

The following requisitions are awaiting your approval:

 

03/19/2003 Requisition: R0002803 Item: 01 Requested by: Tami Fort For: Sue Young
Description: Epson Model S020089 Color Ink Cartridge
Vendor: Office Depot
QTY: 4 Unit Price: $26.5000 Sales Tax: $7.02 Total: $113.02

 

Accounting Fund-Orgn1 R0002803 Charged to:
Fund 14000 Unrestricted FSL
Orgn 3201 Office of Communications
Amount: $113.02

Please login to Banner WebForms at http://webforms_server.uaex.edu/ and use Form FOAUAPP to approve these documents.

CC: <bknox@uaex.edu>, ...

This is working very well for us and has significantly sped getting a Requisition through the approval process.

 

We have created a table used to track which unapproved requisitions (FOBUAPP: Unapproved Document Table) have already been sent to an approver.  The following scripts and comments explain: 

create_approval_table.sql  Create the APPROVAL Table.  A one time script.
Creates Table: approval
The approval table will store the information from the Banner Approval tables required to track which notices were sent by prior runs.  

create_approval_wk_table.sql  Create the APPROVAL_WK Table.  A one time script.
Creates Table: approval_wk
The approval_wk table will store temporary or work information for the duration of a single run.  (This was used to reduce the memory resources required to compose the email messages and has worked very well.)

approval_clear.shl  Clear Older Unapproved Requisitions from APPROVAL Table

Allows approval_gather2.shl (.sql) to Resend the older Requisition Notices
Runs approval_listall.sql Audit Listing for APPROVAL and FOBUAPP Tables (helpful for debugging).
Runs approval_clear.sql Clears Approval Records from APPROVAL, Allows approval_gather2.sql to select all cleared records again.

 

approval_clear.sql  Clears Older Records from the APPROVAL table.

 

approval_clear.log  is the run log file created by cron (helpful for debugging).

 

crontab entry:
 

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

will run approal_clear.shl at 5:38 a.m. and creates approval_clear.log from the standard and error outputs.

approval_gather2.shl  Gather Unapproved Requisitions for APPROVAL Table All Days'
Runs approval_gather2.sql
conditionally runs approval_periodic.shl the script of emails.

(Yes, it is gather2.  gather is just a relic:)

crontab entry (must be owned by authority that will send the email):

39 7 * * 1-5 /home/common/approval_gather2.shl > /home/common/approval_gather.log 2>&1

will run approval_gather2.shl at 7:39 a.m. Monday through Friday and creates approval_gather.log from the standard and error outputs.  This is actually before our sleep/wait runs begin for the day.  It might find an unsent item from the last run of sleep/wait the day before.

approval_gather2.sql  Gathers Approval Records from FOBUAPP 
Conditionally runs approval_periodic2 to save processor time.

approval_periodic2.sql  Generate 'Requisitions Awaiting Your Approval' email Reports
Include all dates for Unapproved Records.

Runs approval_periodic2_1.sql  -- Commodity Transactions

Runs approval_periodic2_2.sql  -- Accounting Transactions

Runs approval_clear_approved.sql  -- Clears Approved Records from the APPROVAL table.

approval_gather.log is the run log created by cron for approval_gather2.shl.

crontab entry (must be owned by authority that will send the email):

9,39 8-17 * * 1-5 /home/common/approval_gather2.shl >> /home/common/approval_gather.log 2>&1

will run approval_gather2.shl at  :09 and :39 every hour from 8:09 a.m. through 5:39 p.m. Monday through Friday and appends all messages (to standard and error outputs) to approval_gather.log.  Appending the messages allows collecting all of today's messages in a single log file.

By running every half hour approval notices tend to be grouped instead of sending an email for each new requisition notice.  (We considered using a trigger to run the processes, but that might send an annoying number of emails to an approver handling many requisitions.) 

approval_listall.sql Audit Listing for APPROVAL and FOBUAPP Tables
List All FOBUAPP Records
List Today's FOBUAPP Records
List APPROVAL Table Records
This script helps in debugging.

Brett Powell's Summit 2003 Session "Requisitions: From Start to Finish" is a very good review and will help relate the Banner Tables/Screens to what we have done with the Approval Notices email (slide #24).  Slide #23 is Steve Rea's Simplified Requisition Entry/Inquiry Form that has much improved our requisition entry.

 

http://betwinx.com/banner_approval_notices_email.htm is a personal site maintained by Bruce Knox ( bknox at uaex.edu )
 

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:)

Any University of Arkansas Division of Agriculture website Disclaimer terms found in conflict with terms of this disclaimer shall over ride and replace the conflicting terms found herein.


Most of the works of art on my pages
Book Dragon copyright 2006 J. Wilson Spenceare used by permission of J. Wilson Spence.

http://betwinx.com is oriented to Reporting from Ellucian's Banner product. (I maintain a number of special topic pages with links from this primary site.)