Adding SQL Scripts To JOBSUB
to Run From Banner

These are the steps that I went through to add my expense_voucher.sql script to Banner as EXPVOUCH so that it could be executed through gjapctl (jobsub).  Since SQL can't be executed directly in jobsub, I had to make a unix shell  script, expense_voucher.shl, to run it using runsqlplus (see runsqlplus.pc in the Oracle Scripts list on my web site).

I also had to split it up to take the SQL command line prompts out of it (for the optional printouts) and the printing itself, since that was dependent on the parameter values entered in gjapctl.  So, expense_voucher.sql now just gets the parameter values from gjbprun, calls expense_voucher1.sql, which contains the actual processing, and prints out the results depending on the parameter values if they were entered.  (To get and use the primary printer name entered into gjapctl, see the description at the end.  Also, note that our sql scripts are kept in our /home/common directory - you will need to change these instructions to match your directories and installation.)

1) In gsasecr (the security app, not in Banner itself, where the new function
   must be added before gjajobs in Banner will recognize it):

   A) In the Object Maintenance form, insert and save record for this new
      Finance function (F):
      EXPVOUCH   2.1.11   F   BAN_DEFAULT_M
   B) In the Class Maintenance form, click on the entry for BAN_FINANCE_C,
      click on the Class Objects button, and insert and save record:
      EXPVOUCH   BAN_DEFAULT_M
   C) Back in the Class Maintenance form, click on BAN_FINANCE_C and click
      on the Sync Users button.

2) In Banner screen gjajobs, insert and save the job description record:

   Name: EXPVOUCH   Title: Expense Voucher   System: F
   Desc: Expense Voucher
   Type: Procedure   Printer: hp3si_cr1   Lines: 60
   Command Name: expense_voucher     (this is the .shl)

3) You'll also need to insert and save the object record in Banner screen
   guaobjs:

     Name:     Description:       Type:  Ind:  User ID:
   EXPVOUCH    Expense Voucher    JOBS    F    LOCAL

4) In Banner screen gjapdef, insert and save the job parameter records for
   EXPVOUCH:

   Parameter: 01   Daily Activity Report Printer
   Type: Character   Optional   Single
   Length: 10
   Default: HP3SI_CR1
   Help: Printer for Daily Activity Report file in
         /home/common/expense_voucher.lst

   Parameter: 02   Daily Edit Report Printer
   Type: Character   Optional   Single
   Length: 10
   Default: HP3SI_CR1
   Help: Printer for Daily Edit Report file in
         /home/common/expense_voucher.err

   Parameter: 03   Treasury Fund Voucher Forms
   Type: Character   Optional   Single
   Length: 10
   Default: HP4000
   Help: Printer for Treasury Fund Voucher Forms in
         /home/common/expense.txt

   Parameter: 04   Cash Fund Voucher Forms
   Type: Character   Optional   Single
   Length: 10
   Default:
   Help: Optional Printer for Cash Fund Voucher Forms
         in /home/common/expense1.txt

   Parameter: 05   DFA Voucher File Printer
   Type: Character   Optional   Single
   Length: 10
   Default:
   Help: Optional DFA Voucher File Printer (hp3/legal)
         in /home/common/expense.vou

5) Create file $BANNER_LINKS/expense_voucher.shl to run expense_voucher.sql
   using runsqlplus:

#/bin/sh
#
# expense_voucher.shl script to run expense_voucher.sql
#
cd $BANNER_LINKS
runsqlplus $UID $PSWD expense_voucher.sql $ONE_UP $HOME/$TEMP.lis $LOG $PRNT

6) Created file $BANNER_LINKS/expense_voucher.sql to run /home/common/
   expense_voucher1.sql after getting the parameters, and then send the
   print commands based on the parameters entered (note that gjbprun
   numbers 01 through 05 match Parameter numbers in gjapdef):

set showmode off
set echo off
set linesize 80
set pagesize 0
set heading off
set timing off
set feedback off
set termout off
set verify off
rem
rem Script: expense_voucher.sql (runs expense voucher and prints reports)
rem
rem Purpose: Generate the expense voucher information for all currently
rem    existing but as yet unprocessed vouchers in the fabinvh/farinva
rem    tables, checking for various errors (which would necessitate the
rem    voucher to be recreated, since the bad one was flagged as already
rem    completed), and creating the daily activity report, daily edit
rem    report, voucher forms printout files, and DFA voucher file.  Mark
rem    all vouchers as being processed (either with errors or accepted)
rem    so that they are not reprocessed (fimsmgr.expvouflgs table).
rem
rem Author: Stephen Rea
rem Released: 7/1/98
rem
rem Get the parameters from GJBPRUN for the printers for daily activity
rem report, daily edit report, treasury fund voucher forms, cash fund
rem voucher forms, and DFA voucher file (usually not printed, but, if
rem specified, must be on hp3 printer with legal size paper).
rem
column actrpt noprint new_value act_rpt
column edtrpt noprint new_value edt_rpt
column treasfrm noprint new_value treas_frm
column cashfrm noprint new_value cash_frm
column dfafil noprint new_value dfa_fil
select lower(gjbprun_value) actrpt
   from general.gjbprun
   where gjbprun_job = 'EXPVOUCH'
   and gjbprun_one_up_no = &1
   and gjbprun_number = '01';
select lower(gjbprun_value) edtrpt
   from general.gjbprun
   where gjbprun_job = 'EXPVOUCH'
   and gjbprun_one_up_no = &1
   and gjbprun_number = '02';
select lower(gjbprun_value) treasfrm
   from general.gjbprun
   where gjbprun_job = 'EXPVOUCH'
   and gjbprun_one_up_no = &1
   and gjbprun_number = '03';
select lower(gjbprun_value) cashfrm
   from general.gjbprun
   where gjbprun_job = 'EXPVOUCH'
   and gjbprun_one_up_no = &1
   and gjbprun_number = '04';
select lower(gjbprun_value) dfafil
   from general.gjbprun
   where gjbprun_job = 'EXPVOUCH'
   and gjbprun_one_up_no = &1
   and gjbprun_number = '05';
rem
rem Run the expense voucher for all unprocessed vouchers.
rem
@/home/common/expense_voucher1.sql
rem
rem Print out the results as requested through the parameters.
rem
spool /home/common/expvoudo.sql
select '!qprt -P &act_rpt /home/common/expense_voucher.lst' from dual
   where length('&act_rpt') > 0;
select '!qprt -P &edt_rpt /home/common/expense_voucher.err' from dual
   where length('&edt_rpt') > 0;
select '!qprt -P &treas_frm /home/common/expense.txt' from dual
   where length('&treas_frm') > 0;
select '!qprt -P &cash_frm /home/common/expense1.txt' from dual
   where length('&cash_frm') > 0;
select '!qprt -P &dfa_fil -z 1 -Q 2 /home/common/expense.vou' from dual
   where length('&dfa_fil') > 0;
spool off
@/home/common/expvoudo.sql
!rm -f /home/common/expvoudo.sql
rem
rem Delete job parameters from gjbprun.
rem
-- delete from general.gjbprun
--  where gjbprun_job = 'EXPVOUCH'
--    and gjbprun_one_up_no = &1;
exit;

7) Since the expense voucher uses three new tables, I created those tables in
   my user ID SQL area (schema) by just running the "create table" commands
   after getting into sqlplus with my user ID.  Then, since whoever runs the
   expense voucher needs to access and update those tables, I granted all
   rights on those tables to public.  I also created synonyms for the tables
   so that you wouldn't have to prefix each reference with "srea.", such as
   srea.expvouflgs (public synonyms can only be created through a DBA user
   account).  These grants and synonyms are shown below:

   grant all on expvouflgs to public;
   grant all on expvouhdrs to public;
   grant all on expvourecs to public;
   create public synonym expvouflgs for srea.expvouflgs;
   create public synonym expvouhdrs for srea.expvouhdrs;
   create public synonym expvourecs for srea.expvourecs;

One problem that I've found is that Banner doesn't show you which number was assigned to the job, so you have to log into unix, get into the /home/jobsub directory, and do an "ls -ltr" to find your log file, such as srea_expense_voucher_8935.log, which will be near the bottom of the listing.  It does name the log file using your user ID and the command name (.shl file name) entered into gjajobs.  Note that any !echo's or other screen output generated by your .sql script automatically goes to the .log file, so you don't have to make any changes to your .sql code to do that.

Another problem is that the gjbprun parameters don't include the printer name entered into gjapctl.  To get that, you have to use the unix system variable $PRNT which gjapctl sets up before calling your shell file (.shl).   The following lines put into your SQL file will retrieve that printer value into the SQL variable prnt:

   !echo define prnt=$PRNT >/home/common/getprnt.sql
   @/home/common/getprnt.sql
   !rm /home/common/getprnt.sql

When printing with that prnt string, don't forget to check it for 'NOPRINT' and 'NO-PRINT', which are Banner's values for bypassing the print.   The following lines put into your SQL file will print the given listing file (myfile.lst here) if that prnt string is not empty and is not NOPRINT or NO-PRINT:

   spool /home/common/doprnt.sql
   select '!qprt -P &prnt /home/common/myfile.lst' from dual
      where length('&prnt') > 0 and upper('&prnt') <> 'NOPRINT'
      and upper('&prnt') <> 'NO-PRINT';
   spool off
   @/home/common/doprnt.sql
   !rm -f /home/common/doprnt.sql          

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.