We use an e-mail report "cookie cutter" to create work-a-round solutions for missing pieces of Banner. 

 

Abstract:

We 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.

 

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

From:     <bknox_@edison.uaex.edu>
To:          <bknox_@uaex.edu>
Date:      9/8/03 3:41PM
Subject: TEST email Subject text string  

bknox_@uaex.edu Beginning of Text Message
This was mailed 08-SEP-2003

Ending of Text Message

  Here is the code for the template:

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.

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

From: <kbailey_@edison.uaex.edu>
To: <nboston_@uaex.edu>
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: <bknox_@uaex.edu>, <kbailey_@uaex.edu>

leave_requests_gather.shl The controlling script (usually run by cron).

leave_requests_gather.sql determines if any new leave requests to report.

 

This gather step uses a temporary table.  

LEAVE Table
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
LEAVE_SEQNO                                        NUMBER(12)
LEAVE_TIME_ENTRY_DATE                              DATE
LEAVE_ACTIVITY_DATE                                DATE
LEAVE_SENT                                         VARCHAR2(1)

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.

 

 

Ssh, don't tell anyone it is this easy:)

 

 

 

 

 

 

 

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

 

Michelangelo Fubaloo image and Bugography Animation are used with the permission of 
Cision Studios and are Copyright 2002 Cision Studios 

 

 

http://betwinx.com is my personal site.  betwinx?  This site is largely oriented to Oracle Reporting Solutions in the context of generating bespoken reports from Ellucian's Banner product. (I maintain a number of special topic pages with links from this primary site.)