use an e-mail report "cookie cutter" to create work-a-round solutions
for missing pieces of Banner.
have done a lot of e-mail reporting: Direct Deposit Notices, Requisitions
Awaiting Approval, Leave Requests, encrypted attachments for Insurance
Reporting, Job Control Reports, Database Table Monitoring, etc.
The ones that have gotten the most positive attention fall into two
types: Reporting to individuals and
reporting to mangers. Both of these
use the same template.
standard template or "cookie cutter" script needs just the unique SQL
code for the new application to be inserted.
I actually develop my SQL code apart from the e-mail code and have a
pretty good idea that everything will work the first try with the e-mailing
version. But, I use a built-in
review step just in case (much easier on your ego to catch your own errors).
The objective is to send an email message reporting Banner information from a standard template generated script..
cookie_cutter.shl is the output, a file of executable UNIX commands, of cookie_cutter.sql
Run it and you’d see:
Subject: TEST email Subject text string
firstname.lastname@example.org Beginning of Text Message
This was mailed 08-SEP-2003
of Text Message
cookie_cutter.sql is the skeleton or template program script. This will generate email "as is" and will make obvious missed string changes you should make.
cookie_sub_select_code.sql replaces the dummy Sub-Select. This code could be anything that returns records with a Report To PIDM, Detail line, and a Sort Order.
cookie_new.sql is the new email SQL*Plus script created from cookie_cutter.sql and cookie_sub_select_code.sql.
cookie_new.shl is the output of cookie_new.shl.
is the code for an application (Leave Requests for Your Approval)
OK, that is instructive, but here is a real application built using the above technique.
leave_requests.shl is the output, a file of executable UNIX commands.
Run it and you get something like:
Date: 8/5/03 4:47AM
Subject: Leave Requests for Your Approval
The following leave requests are awaiting your approval:
Don Mayday has requested
8 hours of Sick Pay on 09/17/2003
8 hours of Sick Pay on 09/18/2003
8 hours of Vacation Pay on 09/29/2003
8 hours of Vacation Pay on 10/03/2003
Please approve via login to Banner through either of the following:
Self Service http://ban.uaex.edu/ then click on Employee Services and Time Sheet
WebForms at http://webforms.uaex.edu/ using form PHADSUM
CC: <email@example.com>, <firstname.lastname@example.org>
gather step uses a temporary table.
Name Null? Type
----------------------------------------- -------- ----------------------------
leave_requests.sql is the cookie_cutter.sql derived code.
leave_request.gather_cron.log is a log of leave_request_gather.shl as run by cron.
generate this .log file using a crontab entry like:
40 4 * * 1-6 /home/common/leave_requests_gather.shl > leave_requests_gather_cron.log 2>&1
This .log file proves very useful both in debugging the routine and for troubleshooting and run tracking.
don't tell anyone it is this easy:)
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:)
Michelangelo Fubaloo image and Bugography Animation are
used with the permission of
Cision Studios and are Copyright 2002 Cision Studios