-- bjv_print.sql Batch Journal Voucher Report --**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:) SET ECHO OFF -- 06/10/99 bjulian created jv_print.sql using report.shl -- 10/08/99 bknox created from jv_print.sql -- 11/02/99 bknox changed to jvdoc format -- 06/13/00 bknox added chmod 666 bjv_print.lst ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; COLUMN doc_code NEW_VALUE doc_code FORMAT A8; SELECT '&1' doc_code FROM DUAL; !echo document code is: &&doc_code SET NEWPAGE 0 SET PAGESIZE 43 -- for Landscape or 55 for Portrait SET SHOWMODE OFF SET TIMING OFF SET FEEDBACK OFF SET HEADING ON SET LINESIZE 128 SET VERIFY OFF SET TERMOUT ON -- SET NEWPAGE 0 -- SET ECHO OFF Suppress Display of commands -- SET FEEDBACK OFF Suppress Display of record counts -- SET LINESIZE n Set line length -- SET PAGESIZE n Set lines per page -- SET SHOWMODE OFF Suppress Display of old and new setting of SQL*Plus system variables -- SET NEWPAGE 0 Set no lines before TITLE and eject frist page -- SET VERIFY ON Show each line of the file before and after substitution -- SET TERMOUT OFF Suppress Display of output --CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES --DEBUG -- SET ECHO ON -- SET SHOWMODE ON -- SET FEEDBACK ON --DEBUG -- use current date to get asofdate COLUMN asofdate NOPRINT NEW_VALUE asofdate SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY') asofdate FROM DUAL; -- Accept run options from operator -- !echo " " -- !echo "* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *" -- !echo "Please Enter Parameters for the Journal Voucher Report Program" -- !echo "* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *" -- !echo "Parameter(s):" -- !echo -- ACCEPT doc_code CHAR PROMPT ' Enter Document Code ---------------(xxxxxxxx)-------------> ' -- !echo " " -- ACCEPT play_on CHAR PROMPT ' To Cancel, press or To Continue, press ---> ' -- Accept run options from operator SET TERMOUT OFF -- Time and Date COLUMN today NOPRINT NEW_VALUE datevar FORMAT A18 TRUNC COLUMN time NOPRINT NEW_VALUE timevar FORMAT A11 TRUNC SELECT TO_CHAR(SYSDATE,'fmMonth DD, yyyy') today, TO_CHAR(SYSDATE,'HH:MI:SS am') time FROM DUAL; COLUMN asofdatealpha NOPRINT NEW_VALUE asofdatealphavar FORMAT A18 TRUNC SELECT TO_CHAR(TO_DATE('&&asofdate', 'MM/DD/YYYY'), 'fmMonth DD, yyyy') asofdatealpha FROM DUAL; COLUMN ces NOPRINT NEW_VALUE cesvar SELECT RTRIM(gubinst_name) ces FROM general.gubinst; COLUMN coa NOPRINT NEW_VALUE coavar SELECT 'Z' coa FROM DUAL; COLUMN gurfeed_doc_code HEADING "Document|Code" FORMAT A8 COLUMN gurfeed_seq_num NOPRINT HEADING "Seq_Num" FORMAT 9999 COLUMN rucl_code HEADING "Rule" FORMAT A4 COLUMN fund_code HEADING "Fund" FORMAT A6 COLUMN orgn_code HEADING "Orgn" FORMAT A6 COLUMN acct_code HEADING "Acct" FORMAT A6 COLUMN trans_date NOPRINT NEW_VALUE trans_date HEADING "Trans|Date" FORMAT A11 TRUNC COLUMN prog_code HEADING "Prog" FORMAT A6 COLUMN trans_amt HEADING "Amount" FORMAT 99,999,999.99 COLUMN dr_cr_ind HEADING "D/C" FORMAT A3 COLUMN trans_desc HEADING "Description" FORMAT A35 COLUMN user_id NOPRINT NEW_VALUE user_id HEADING "User ID" FORMAT A8 TRUNC COLUMN bank_code HEADING "Bank" FORMAT A4 COLUMN gurfeed_fsyr_code HEADING "FY" FORMAT A2 COLUMN Header_Desc NEW_VALUE Header_Desc COLUMN Amt NEW_VALUE amtvar SET NUMWIDTH 10 SET HEADING OFF SET TERMOUT OFF SELECT TO_CHAR(gurfeed_trans_amt,'999,999,990.99') Amt, NVL(gurfeed_trans_desc,'No Header') Header_Desc FROM gurfeed WHERE gurfeed_doc_code = '&&doc_code' AND gurfeed_rec_type = '1'; COLUMN ln NOPRINT NEW_VALUE ln FORMAT '99'; SELECT (35 - FLOOR(LENGTH(RTRIM('&&Header_Desc'))))/2 "ln" FROM DUAL; SELECT LPAD(' ',&&ln)||RTRIM('&&Header_Desc',' ') Header_Desc FROM DUAL; SET HEADING ON SET FEEDBACK ON SET TERMOUT OFF TTITLE ON BTITLE ON -- BREAK ON REPORT -- COMPUTE SUM LABEL 'Total:' OF Amount ON REPORT -- COL 56 | IS CENTER TTITLE LEFT 'Report bjv_print' COL 35 cesvar - SKIP 2 COL 36 'Batch Journal Voucher Report' - SKIP 1 LEFT ' Total: ' amtvar COL 33 Header_Desc COL 70 'Transaction Date: ' trans_date - SKIP 1 LEFT 'Document Code: ' doc_code COL 70 'User ID: ' user_id - SKIP 2 BTITLE LEFT SKIP 1 ' Run: ' datevar ' ' timevar COL 63 ' Page No.: ' FORMAT 999 SQL.PNO SKIP 1 !rm -f bjv_print.lst !echo " " !echo "bjv_print report beginning" !echo " " SET FEEDBACK OFF SPOOL bjv_print SELECT gurfeed_trans_date trans_date, NVL(gurfeed_user_id,' ') user_id, NVL(gurfeed_fund_code,' ') fund_code, NVL(gurfeed_orgn_code,' ') orgn_code, NVL(gurfeed_acct_code,' ') acct_code, NVL(gurfeed_prog_code,' ') prog_code, NVL(gurfeed_trans_amt,0) trans_amt, NVL(gurfeed_dr_cr_ind,' ') dr_cr_ind, NVL(gurfeed_bank_code,' ') bank_code, NVL(gurfeed_rucl_code,'rucl') rucl_code, NVL(gurfeed_trans_desc,' ') trans_desc -- gurfeed_fsyr_code FROM gurfeed WHERE gurfeed_doc_code = '&&doc_code' AND gurfeed_rec_type = '2' ORDER BY gurfeed_doc_code, gurfeed_seq_num; SPOOL OFF !chmod 666 bjv_print.lst TTITLE OFF BTITLE OFF CLEAR COLUMNS CLEAR BREAK CLEAR COMPUTE SET NUMWIDTH 10 SET HEADING ON !echo !echo "bjv_print report complete" !echo ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY';