-- 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 <Ctrl+c> or To Continue, press <Enter>---> '

-- 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';
