-- bjvfeed_z.sql   Batch Journal Voucher Load Category Z - Generic Batch JV Load to GURFEED 
--**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:) 
-- Value 1 is Period End Date MMDDYYYY  Value 2 is Batch Description

SET ECHO OFF
-- Store parameter passed from .shl
COLUMN TransDate NEW_VALUE TransDate FORMAT A8 TRUNC
SELECT '&1' TransDate FROM DUAL;

!echo TransDate is '&&TransDate'

COLUMN batch_description NEW_VALUE batch_description FORMAT A35 TRUNC;
SELECT REPLACE('&2','#',' ') batch_description FROM DUAL;

!echo Batch Description is: &&batch_description 

-- Uses the Banner Batch Interface table GURFEED to support Non-Banner 
-- automated input from a subsystem
-- table bjvfinc (non-Banner) is loaded using SQL Loader see bjvfeed_z.shl

-- change log
-- 10/25/02 bknox Created from bjvfeed_h.sql Printing Batch JV Load
-- 10/28/02 bknox added bjvfeed_n2.sql like transdate
-- 11/06/03 bknox changed sbaker@uaex.edu to $USER@edison.uaex.edu
-- 11/12/04 bknox fix DB_Instance routine for Oracle 9

SET TERMOUT OFF
SET ECHO ON
SET PAGESIZE 0
SET LINESIZE 256
SET FEEDBACK OFF
SET TRIMSPOOL ON

--debug
set echo on
set termout on
set feedback on
--debug

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYYHH24MISS';

COLUMN Error_Count NOPRINT NEW_VALUE Error_Count
SELECT 0 Error_Count FROM DUAL;

COLUMN DB_Instance NEW_VALUE DB_Instance FORMAT A11 TRUNC
SELECT SUBSTR(SUBSTR(global_name,1,30),1,instr(SUBSTR(global_name,1,30)||'.','.')-1) DB_Instance
  FROM global_name;
!echo "DB Instance: ' &&DB_Instance

-- Email Message If No Input
SET ECHO OFF
SET VERIFY OFF
SET TERMOUT OFF
SET PAGESIZE 0
SET LINESIZE 256
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET HEADING OFF
SET TIMING OFF

!rm -f bjvfeed_z_abort_noinput.sql
SET HEADING OFF
SPOOL bjvfeed_z_abort_noinput.sql
SELECT '!echo "Table Load Failed for bjvfeed_z.shl -- Check bjvfeed_z.log" > bjvfeed_z_error.txt'||CHR(10)||'!cat bjvfeed_z_error.txt bjvfeed_z.log > bjvfeed_z.txt'||CHR(10)||'!mail -s "Table Load Failed bjvfeed_z.shl" bknox@uaex.edu < bjvfeed_z.txt'||CHR(10)
   FROM DUAL
WHERE NOT EXISTS
 (SELECT 'X' FROM bjvfinc
   WHERE bjvfinc_category_code = 'Z'
     AND bjvfinc_interfaced_ind != 'Y');
SPOOL OFF
@bjvfeed_z_abort_noinput.sql
!rm -f bjvfeed_z_abort_noinput.sql
!rm -f bjvfeed_z_error.txt
!rm -f bjvfeed_z.txt

SET ECHO ON

-- Abort Program Exit -- If No Input
SET TERMOUT OFF
!rm -f bjvfeed_z_abort.sql
SPOOL bjvfeed_z_abort.sql
SELECT '!echo Table Load Failed bjvfeed_z' || chr(10) || '/*'
   FROM DUAL
WHERE NOT EXISTS
 (SELECT 'X' FROM bjvfinc
   WHERE bjvfinc_category_code = 'Z'
     AND bjvfinc_interfaced_ind != 'Y');
SPOOL OFF
@bjvfeed_z_abort.sql
SET TERMOUT ON

-- Sequence Number Incrementing for JV Document Number

COLUMN Seqn_Before NEW_VALUE Seqn_Before

-- SEQ_NO Before
select fobseqn_maxseqno_7 Seqn_Before from fobseqn
 WHERE  fobseqn_seqno_type   = 'B'
   FOR UPDATE;

-- Increment Sequence Number Before Use
UPDATE fobseqn
   SET fobseqn_maxseqno_7 = fobseqn_maxseqno_7 + 1
 WHERE  fobseqn_seqno_type   = 'B';

COMMIT;

-- SEQ_NO After
SELECT &&Error_Count+1 Error_Count, 'Seqn After Increment != Seqn Before + 1, Found Before='||&&Seqn_Before||' After='|| fobseqn_maxseqno_7
  FROM fobseqn
 WHERE  fobseqn_seqno_type   = 'B'
   AND  &&Seqn_Before+1 != fobseqn_maxseqno_7;

-- Store Sequence Number for this Batch
COLUMN Batch_JV_Number NEW_VALUE Batch_JV_Number
SELECT 'B'||LPAD(TO_CHAR(fobseqn_maxseqno_7),7,'0') Batch_JV_Number
  FROM fobseqn
 WHERE fobseqn_seqno_type   = 'B';

-- Email Message If Sequence Number Increment Error
SET ECHO OFF
SET VERIFY OFF
SET TERMOUT OFF
SET PAGESIZE 0
SET LINESIZE 256
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET HEADING OFF
SET TIMING OFF

!rm -f bjvfeed_z_abort_seq_err.sql
SET HEADING OFF
SPOOL bjvfeed_z_abort_seq_err.sql
SELECT '!echo "Table Load Failed for bjvfeed_z.shl -- fobseqn_maxseqno_7 Increment Error" > bjvfeed_z_error.txt'||CHR(10)||'!mail -s "Table Load Failed bjvfeed_z" bknox@uaex.edu < bjvfeed_z_error.txt'||CHR(10)
  FROM fobseqn
 WHERE  fobseqn_seqno_type   = 'B'
   AND  &&Seqn_Before+1 != fobseqn_maxseqno_7;
SPOOL OFF
@bjvfeed_z_abort_seq_err.sql
!rm -f bjvfeed_z_abort_seq_err.sql
!rm -f bjvfeed_z_error.txt

SET ECHO ON
SET FEEDBACK ON
SET VERIFY ON

-- Abort Program Exit -- Test If No Input
SET TERMOUT OFF
!rm -f bjvfeed_z_abort.sql
SPOOL bjvfeed_z_abort.sql
SELECT '!echo Table Load Failed bjvfeed_z' || chr(10) || '/*'
  FROM fobseqn
 WHERE  fobseqn_seqno_type   = 'B'
   AND  &&Seqn_Before+1 != fobseqn_maxseqno_7;
SPOOL OFF
@bjvfeed_z_abort.sql
SET TERMOUT ON

SET ECHO ON
SET FEEDBACK ON
SET VERIFY ON

!echo bjvfinc Table Loaded

-- ** Code Specific to Generic Batch JV Load System Input Manipulation ** 

-- Store Transaction Date 
COLUMN Trans_Date NEW_VALUE Trans_Date FORMAT A10 TRUNC
SELECT TO_CHAR(TO_DATE('&&TransDate','MMDDYYYY'),'MM/DD/YYYY') Trans_Date FROM DUAL;

-- Store Month_Year (Transaction Date in Mon + YY Format)
COLUMN Month_Year NEW_VALUE Month_Year
SELECT TO_CHAR(TO_DATE('&&Trans_Date','MM/DD/YYYY'),'MonYY') Month_Year FROM DUAL;

-- Store Batch Header Description
COLUMN Batch_Header_Desc NEW_VALUE Batch_Header_Desc
SELECT SUBSTR('&&Month_Year'||' &&batch_description',1,35) Batch_Header_Desc FROM DUAL;

-- Validate Input Field

SET SHOW OFF
SET ECHO OFF
SET VERIFY OFF
SET TERMOUT ON
SET PAGESIZE 0
SET LINESIZE 256
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET HEADING OFF
SET TIMING OFF

!rm -f bjvfeed_validation_errors.txt
SPOOL bjvfeed_validation_errors.txt

-- Report Validation Errors
SELECT CHR(10)||CHR(10)||'bjvfeed_z Validation Errors for Input'||CHR(10)||'.'||CHR(10) FROM DUAL;
SELECT ' **Error**     Description                              Amount Fund   Orgn   Account D/C  RUCL'||CHR(10)||'-------------- ----------------------------------- ----------- ------ ------ ------  ---  ----' FROM DUAL;

-- List Records with missing Fund
SELECT 'Missing Fund  ', BJVFINC_TRANS_DESC, bjvfinc_amt, '      ', bjvfinc_orgn_code, bjvfinc_acct_code,' ', BJVFINC_DR_CR_IND,'', BJVFINC_RUCL_CODE
FROM bjvfinc
WHERE  NVL(bjvfinc_fund_code,'X') = 'X'
  AND bjvfinc_amt != 0
  AND bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N';

-- Count Errors from Last Test
SELECT &&Error_Count + COUNT(*) Error_Count
FROM bjvfinc
WHERE  NVL(bjvfinc_fund_code,'X') = 'X'
  AND bjvfinc_amt != 0
  AND bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N';

-- List Records with missing Orgn
SELECT 'Missing Orgn  ', BJVFINC_TRANS_DESC, bjvfinc_amt, bjvfinc_fund_code, '      ', bjvfinc_acct_code,' ', BJVFINC_DR_CR_IND,'', BJVFINC_RUCL_CODE
FROM bjvfinc
WHERE  NVL(bjvfinc_orgn_code,'X') = 'X'
  AND bjvfinc_amt != 0
  AND bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N';

-- Count Errors from Last Test
SELECT &&Error_Count + COUNT(*) Error_Count
FROM bjvfinc
WHERE  NVL(bjvfinc_orgn_code,'X') = 'X'
  AND bjvfinc_amt != 0
  AND bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N';

-- List Records with missing Account
SELECT 'Missing ACCT  ', BJVFINC_TRANS_DESC, bjvfinc_amt, bjvfinc_fund_code, bjvfinc_orgn_code, '      ',' ', BJVFINC_DR_CR_IND,'', BJVFINC_RUCL_CODE
FROM bjvfinc
WHERE  NVL(bjvfinc_acct_code,'X') = 'X'
  AND bjvfinc_amt != 0
  AND bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N';

-- Count Errors from Last Test
SELECT &&Error_Count + COUNT(*) Error_Count
FROM bjvfinc
WHERE  NVL(bjvfinc_acct_code,'X') = 'X'
  AND bjvfinc_amt != 0
  AND bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N';

-- List Records with missing Debit/Credit Indicator
SELECT 'Missing D/C   ', BJVFINC_TRANS_DESC, bjvfinc_amt, bjvfinc_fund_code, bjvfinc_orgn_code, bjvfinc_acct_code,' ', ' ','', BJVFINC_RUCL_CODE
FROM bjvfinc
WHERE  NVL(BJVFINC_DR_CR_IND,'X') = 'X'
  AND bjvfinc_amt != 0
  AND bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N';

-- Count Errors from Last Test
SELECT &&Error_Count + COUNT(*) Error_Count
FROM bjvfinc
WHERE  NVL(BJVFINC_DR_CR_IND,'X') = 'X'
  AND bjvfinc_amt != 0
  AND bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N';

-- List Records with missing RUCL
SELECT 'Missing RUCL  ', BJVFINC_TRANS_DESC, bjvfinc_amt, bjvfinc_fund_code, bjvfinc_orgn_code, bjvfinc_acct_code,' ', BJVFINC_DR_CR_IND,'', '    '
FROM bjvfinc
WHERE  NVL(bjvfinc_rucl_code,'X') = 'X'
  AND bjvfinc_amt != 0
  AND bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N';

-- Count Errors from Last Test
SELECT &&Error_Count + COUNT(*) Error_Count
FROM bjvfinc
WHERE  NVL(bjvfinc_rucl_code,'X') = 'X'
  AND bjvfinc_amt != 0
  AND bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N';

-- Entered Fund is not in FTVFUND
SELECT 'Invalid Fund  ', BJVFINC_TRANS_DESC, bjvfinc_amt, bjvfinc_fund_code, bjvfinc_orgn_code, bjvfinc_acct_code,' ', BJVFINC_DR_CR_IND,'', BJVFINC_RUCL_CODE
   FROM bjvfinc
WHERE bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N'
  AND bjvfinc_amt != 0
  AND bjvfinc_fund_code IS NOT NULL
  AND bjvfinc_fund_code NOT IN
 (SELECT FTVFUND_FUND_CODE FROM ftvfund
   WHERE ftvfund_fund_code = bjvfinc_fund_code
     AND   ftvfund_coas_code = 'Z'
     AND   TRUNC(ftvfund_eff_date) <= SYSDATE
     AND   ftvfund_nchg_date > TRUNC(SYSDATE)
     AND   ftvfund_data_entry_ind = 'Y')
  AND bjvfinc_fund_code NOT IN
 (SELECT FTVFUND_FUND_CODE FROM ftvfund
   WHERE ftvfund_fund_code = bjvfinc_fund_code
     AND   ftvfund_coas_code = 'Z'
     AND   TRUNC(ftvfund_eff_date) <= SYSDATE
     AND   ftvfund_nchg_date > TRUNC(SYSDATE)
     AND   ftvfund_data_entry_ind != 'Y');

-- Count Errors from Last Test
SELECT &&Error_Count + COUNT(*) Error_Count
   FROM bjvfinc
WHERE bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N'
  AND bjvfinc_amt != 0
  AND bjvfinc_fund_code IS NOT NULL
  AND bjvfinc_fund_code NOT IN
 (SELECT FTVFUND_FUND_CODE FROM ftvfund
   WHERE ftvfund_fund_code = bjvfinc_fund_code
     AND   ftvfund_coas_code = 'Z'
     AND   TRUNC(ftvfund_eff_date) <= SYSDATE
     AND   ftvfund_nchg_date > TRUNC(SYSDATE)
     AND   ftvfund_data_entry_ind = 'Y')
  AND bjvfinc_fund_code NOT IN
 (SELECT FTVFUND_FUND_CODE FROM ftvfund
   WHERE ftvfund_fund_code = bjvfinc_fund_code
     AND   ftvfund_coas_code = 'Z'
     AND   TRUNC(ftvfund_eff_date) <= SYSDATE
     AND   ftvfund_nchg_date > TRUNC(SYSDATE)
     AND   ftvfund_data_entry_ind != 'Y');

-- Entered Orgn is not in FTVORGN
SELECT 'Invalid Orgn  ', BJVFINC_TRANS_DESC, bjvfinc_amt, bjvfinc_fund_code, bjvfinc_orgn_code, bjvfinc_acct_code,' ', BJVFINC_DR_CR_IND,'', BJVFINC_RUCL_CODE
   FROM bjvfinc
WHERE bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N'
  AND bjvfinc_amt != 0
  AND bjvfinc_orgn_code IS NOT NULL
  AND bjvfinc_orgn_code NOT IN
 (SELECT ftvorgn_orgn_code FROM ftvorgn
   WHERE ftvorgn_orgn_code = bjvfinc_orgn_code
     AND   ftvorgn_coas_code = 'Z'
     AND   TRUNC(ftvorgn_eff_date) <= SYSDATE
     AND   ftvorgn_nchg_date > TRUNC(SYSDATE)
     AND   ftvorgn_data_entry_ind = 'Y')
  AND bjvfinc_orgn_code NOT IN
 (SELECT ftvorgn_orgn_code FROM ftvorgn
   WHERE ftvorgn_orgn_code = bjvfinc_orgn_code
     AND   ftvorgn_coas_code = 'Z'
     AND   TRUNC(ftvorgn_eff_date) <= SYSDATE
     AND   ftvorgn_nchg_date > TRUNC(SYSDATE)
     AND   ftvorgn_data_entry_ind != 'Y');

-- Count Errors from Last Test
SELECT &&Error_Count + COUNT(*) Error_Count
   FROM bjvfinc
WHERE bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N'
  AND bjvfinc_amt != 0
  AND bjvfinc_orgn_code IS NOT NULL
  AND bjvfinc_orgn_code NOT IN
 (SELECT ftvorgn_orgn_code FROM ftvorgn
   WHERE ftvorgn_orgn_code = bjvfinc_orgn_code
     AND   ftvorgn_coas_code = 'Z'
     AND   TRUNC(ftvorgn_eff_date) <= SYSDATE
     AND   ftvorgn_nchg_date > TRUNC(SYSDATE)
     AND   ftvorgn_data_entry_ind = 'Y')
  AND bjvfinc_orgn_code NOT IN
 (SELECT ftvorgn_orgn_code FROM ftvorgn
   WHERE ftvorgn_orgn_code = bjvfinc_orgn_code
     AND   ftvorgn_coas_code = 'Z'
     AND   TRUNC(ftvorgn_eff_date) <= SYSDATE
     AND   ftvorgn_nchg_date > TRUNC(SYSDATE)
     AND   ftvorgn_data_entry_ind != 'Y');

-- Account IS NOT IN Validation Table or is Not Available for JV
SELECT 'Invalid ACCT  ', BJVFINC_TRANS_DESC, bjvfinc_amt, bjvfinc_fund_code, bjvfinc_orgn_code, bjvfinc_acct_code,' ', BJVFINC_DR_CR_IND,'', BJVFINC_RUCL_CODE
FROM bjvfinc
WHERE bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N'
  AND bjvfinc_amt != 0
  AND bjvfinc_acct_code IS NOT NULL
  AND bjvfinc_acct_code NOT IN 
(
SELECT   -- Created from TABLE FTVACCT Account Validation Table
  ftvacct_acct_code
  FROM FTVACCT
 WHERE
       TRUNC(FTVACCT_EFF_DATE) <= SYSDATE
   AND      (FTVACCT_NCHG_DATE  > TRUNC(SYSDATE) OR FTVACCT_NCHG_DATE IS NULL)
   AND      (FTVACCT_TERM_DATE  > TRUNC(SYSDATE) OR FTVACCT_TERM_DATE IS NULL)
   AND FTVACCT_STATUS_IND     = 'A' 
   AND FTVACCT_DATA_ENTRY_IND = 'Y'
);

-- Count Errors from Last Test
SELECT &&Error_Count + COUNT(*) Error_Count
   FROM bjvfinc
WHERE bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N'
  AND bjvfinc_amt != 0
  AND bjvfinc_acct_code IS NOT NULL
  AND bjvfinc_acct_code NOT IN 
(
SELECT   -- Created from TABLE FTVACCT Account Validation Table
  ftvacct_acct_code
  FROM FTVACCT
 WHERE
       TRUNC(FTVACCT_EFF_DATE) <= SYSDATE
   AND      (FTVACCT_NCHG_DATE  > TRUNC(SYSDATE) OR FTVACCT_NCHG_DATE IS NULL)
   AND      (FTVACCT_TERM_DATE  > TRUNC(SYSDATE) OR FTVACCT_TERM_DATE IS NULL)
   AND FTVACCT_STATUS_IND     = 'A' 
   AND FTVACCT_DATA_ENTRY_IND = 'Y'
);

-- D/C Indicator IS NOT Valid
SELECT 'Invalid D/C   ', BJVFINC_TRANS_DESC, bjvfinc_amt, bjvfinc_fund_code, bjvfinc_orgn_code, bjvfinc_acct_code,' ', BJVFINC_DR_CR_IND,'', BJVFINC_RUCL_CODE
FROM bjvfinc
WHERE DECODE(BJVFINC_DR_CR_IND,'D','X','C','X','+','X','-','X','Z') = 'Z'
  AND bjvfinc_interfaced_ind = 'N'
  AND bjvfinc_amt != 0
  AND BJVFINC_DR_CR_IND IS NOT NULL;

-- Count Errors from Last Test
SELECT &&Error_Count + COUNT(*) Error_Count
   FROM bjvfinc
WHERE DECODE(BJVFINC_DR_CR_IND,'D','X','C','X','+','X','-','X','Z') = 'Z'
  AND bjvfinc_interfaced_ind = 'N'
  AND bjvfinc_amt != 0
  AND BJVFINC_DR_CR_IND IS NOT NULL;

-- Entered Fund is not to be used for Data Entry 
SELECT 'Non-Entry Fund', BJVFINC_TRANS_DESC, bjvfinc_amt, bjvfinc_fund_code, bjvfinc_orgn_code, bjvfinc_acct_code,' ', BJVFINC_DR_CR_IND,'', BJVFINC_RUCL_CODE
   FROM bjvfinc, ftvfund
WHERE bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N'
  AND bjvfinc_amt != 0
  AND ftvfund_fund_code = bjvfinc_fund_code
  AND ftvfund_coas_code = 'Z'
  AND TRUNC(ftvfund_eff_date) <= SYSDATE
  AND ftvfund_nchg_date > TRUNC(SYSDATE)
  AND ftvfund_data_entry_ind != 'Y';

-- Count Errors from Last Test
SELECT &&Error_Count + COUNT(*) Error_Count
   FROM bjvfinc, ftvfund
WHERE bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N'
  AND bjvfinc_amt != 0
  AND ftvfund_fund_code = bjvfinc_fund_code
  AND ftvfund_coas_code = 'Z'
  AND TRUNC(ftvfund_eff_date) <= SYSDATE
  AND ftvfund_nchg_date > TRUNC(SYSDATE)
  AND ftvfund_data_entry_ind != 'Y';

-- Entered Orgn is not to be used for Data Entry 
SELECT 'Non-Entry Orgn', BJVFINC_TRANS_DESC, bjvfinc_amt, bjvfinc_fund_code, bjvfinc_orgn_code, bjvfinc_acct_code,' ', BJVFINC_DR_CR_IND,'', BJVFINC_RUCL_CODE
   FROM bjvfinc, ftvorgn
WHERE bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N'
  AND bjvfinc_amt != 0
  AND ftvorgn_orgn_code = bjvfinc_orgn_code
  AND ftvorgn_coas_code = 'Z'
  AND TRUNC(ftvorgn_eff_date) <= SYSDATE
  AND ftvorgn_nchg_date > TRUNC(SYSDATE)
  AND ftvorgn_data_entry_ind != 'Y';

-- Count Errors from Last Test
SELECT &&Error_Count + COUNT(*) Error_Count
   FROM bjvfinc, ftvorgn
WHERE bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N'
  AND bjvfinc_amt != 0
  AND ftvorgn_orgn_code = bjvfinc_orgn_code
  AND ftvorgn_coas_code = 'Z'
  AND TRUNC(ftvorgn_eff_date) <= SYSDATE
  AND ftvorgn_nchg_date > TRUNC(SYSDATE)
  AND ftvorgn_data_entry_ind != 'Y';

-- Rule Class IS NOT IN Validation Table or is Not Available for JV
SELECT 'Invalid RUCL  ', BJVFINC_TRANS_DESC, bjvfinc_amt, bjvfinc_fund_code, bjvfinc_orgn_code, bjvfinc_acct_code,' ', BJVFINC_DR_CR_IND,'', BJVFINC_RUCL_CODE
FROM bjvfinc
WHERE bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N'
  AND bjvfinc_amt != 0
  AND bjvfinc_RUCL_CODE IS NOT NULL
  AND bjvfinc_RUCL_CODE NOT IN 
(
SELECT   -- Created from TABLE FTVRUCL  Rule Class Validation Table
  FTVRUCL_RUCL_CODE
  FROM FTVRUCL
 WHERE
       TRUNC(FTVRUCL_EFF_DATE) <= SYSDATE
   AND      (FTVRUCL_NCHG_DATE  > TRUNC(SYSDATE) OR FTVRUCL_NCHG_DATE IS NULL)
   AND      (FTVRUCL_TERM_DATE  > TRUNC(SYSDATE) OR FTVRUCL_TERM_DATE IS NULL)
   AND FTVRUCL_CLASS_TYPE = 'J'
   AND FTVRUCL_STATUS_IND = 'A'
);

-- Count Errors from Last Test
SELECT &&Error_Count + COUNT(*) Error_Count
  FROM bjvfinc
 WHERE bjvfinc_category_code = 'Z'
   AND bjvfinc_interfaced_ind = 'N'
   AND bjvfinc_amt != 0
   AND bjvfinc_RUCL_CODE IS NOT NULL
   AND bjvfinc_RUCL_CODE NOT IN 
(
SELECT   -- Created from TABLE FTVRUCL  Rule Class Validation Table
  FTVRUCL_RUCL_CODE
  FROM FTVRUCL
 WHERE
       TRUNC(FTVRUCL_EFF_DATE) <= SYSDATE
   AND      (FTVRUCL_NCHG_DATE  > TRUNC(SYSDATE) OR FTVRUCL_NCHG_DATE IS NULL)
   AND      (FTVRUCL_TERM_DATE  > TRUNC(SYSDATE) OR FTVRUCL_TERM_DATE IS NULL)
   AND FTVRUCL_CLASS_TYPE = 'J'
   AND FTVRUCL_STATUS_IND = 'A'
);

SPOOL OFF

-- ** General Batch Entry Processes using Generic Batch JV Load System Specific Codes **

-- Total Charges
COLUMN Total_Charges NEW_VALUE Total_Charges
SELECT SUM(bjvfinc_amt) Total_Charges 
  FROM bjvfinc 
 WHERE bjvfinc_category_code = 'Z'
   AND bjvfinc_interfaced_ind = 'N';

-- Get Maximum Sequence Number from Input Transactions (set by LOADER)
COLUMN Seq_Num NEW_VALUE Seq_Num
SELECT MAX(bjvfinc_seq_num) Seq_Num
  FROM bjvfinc 
 WHERE bjvfinc_category_code = 'Z'
   AND bjvfinc_interfaced_ind = 'N';

!echo 'Max Seqence Number: ' &&Seq_Num

SELECT &&Seq_Num +1 Seq_Num FROM DUAL;

-- Update Transaction Date in Table bjvfinc
UPDATE bjvfinc SET bjvfinc_trans_date = TO_DATE('&&Trans_Date', 'MM/DD/YYYY')
WHERE bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N';

--Debug
SET TERMOUT ON
SELECT bjvfinc_seq_num, bjvfinc_fund_code fund, bjvfinc_orgn_code orgn, bjvfinc_acct_code acct, bjvfinc_amt amt, bjvfinc_DR_CR_IND I FROM bjvfinc;

-- ** General Batch Entry Processes **

-- Total for Batch
COLUMN Batch_Total NEW_VALUE Batch_Total
SELECT SUM(bjvfinc_amt) Batch_Total 
  FROM bjvfinc 
 WHERE bjvfinc_category_code = 'Z'
   AND bjvfinc_interfaced_ind = 'N';

-- Store timestamp
COLUMN timestamp NEW_VALUE timestamp FORMAT A14 TRUNC
SELECT TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') timestamp FROM DUAL;

-- Store System ID
COLUMN System_ID NEW_VALUE System_ID
SELECT 'BATCHJV' System_ID FROM DUAL;

-- Store Batch Transaction Date 
COLUMN Batch_Trans_Date NEW_VALUE Batch_Trans_Date FORMAT A17 TRUNC
SELECT TO_CHAR(BJVFINC_TRANS_DATE, 'DD-MON-YYYY')||'235959' Batch_Trans_Date FROM bjvfinc
 WHERE ROWNUM < 2 
  AND bjvfinc_category_code = 'Z'
  AND bjvfinc_interfaced_ind = 'N';

!rm -f bjvfeed_validation_errors2.txt
SPOOL bjvfeed_validation_errors2.txt

-- Entered System ID is not in FTVSDAT
SELECT &&Error_Count+1 Error_Count, 'System ID '||'&&System_ID'||' is Not Defined'
   FROM DUAL
WHERE '&&System_ID' NOT IN
 (SELECT ftvsdat_sdat_code_opt_1 FROM ftvsdat
          WHERE ftvsdat_sdat_code_entity = 'FGBTRNI'
            AND ftvsdat_sdat_code_attr   = 'SYSTEM_ID'
            AND ftvsdat_sdat_code_opt_1  = 'BATCHJV'
            AND substr(ftvsdat_data,1,2) = 'DS');

-- Entered System ID is Not Valid for Transaction Date (see FTVSDAT) 
SELECT &&Error_Count+1 Error_Count, 'System ID '||'&&System_ID'||' is Not Valid for Transaction Date'
   FROM ftvsdat
          WHERE ftvsdat_sdat_code_entity = 'FGBTRNI'
            AND ftvsdat_sdat_code_attr   = 'SYSTEM_ID'
            AND ftvsdat_sdat_code_opt_1  = 'BATCHJV'
            AND substr(ftvsdat_data,1,2) = 'DS'
        AND NOT ftvsdat_eff_date <= TO_DATE('&&Batch_Trans_Date','DD-MON-YYYYHH24MISS') AND
          (ftvsdat_nchg_date > TO_DATE('&&Batch_Trans_Date','DD-MON-YYYYHH24MISS')
            OR ftvsdat_nchg_date is null);

SPOOL OFF

-- Remove Blank Lines from Error Report.
!rm -f bjvfeed_validation_errors3.txt
!cat bjvfeed_validation_errors.txt| tr -s "" "\012" > bjvfeed_validation_errors3.txt
-- Append System ID is not in FTVSDAT Error (if any)
!cat bjvfeed_validation_errors2.txt >> bjvfeed_validation_errors3.txt
!rm -f bjvfeed_validation_errors2.txt
!mv -f bjvfeed_validation_errors3.txt bjvfeed_validation_errors.txt

SET ECHO ON
SET FEEDBACK ON
SET VERIFY ON
SET SHOW ON

-- Insert GURFEED Detail Records
INSERT into gurfeed
          ( gurfeed_system_id,
            gurfeed_system_time_stamp,
            gurfeed_doc_code,
            gurfeed_rec_type,
            gurfeed_submission_number,
            gurfeed_seq_num,
            gurfeed_activity_date,
            gurfeed_user_id,
            gurfeed_rucl_code,
            gurfeed_trans_date,
            gurfeed_trans_amt,
            gurfeed_trans_desc,
            gurfeed_dr_cr_ind,
            gurfeed_bank_code,
            gurfeed_coas_code,
            gurfeed_fund_code,
            gurfeed_orgn_code,
            gurfeed_acct_code,
            gurfeed_prog_code,
            gurfeed_actv_code,
            gurfeed_locn_code,
            gurfeed_account,
            gurfeed_ptot_status)
    SELECT '&&system_id',
           '&&timestamp',
           '&&Batch_JV_Number',
           '2',
            0,
            bjvfinc_seq_num,
            SYSDATE,
            USER,
            bjvfinc_rucl_code,
            TO_DATE('&&Batch_Trans_Date','DD-MON-YYYYHH24MISS'),
            ABS(bjvfinc_amt),
            bjvfinc_trans_desc,    
            bjvfinc_dr_cr_ind,
            F_Bank,
            bjvfinc_coas_code,
            bjvfinc_fund_code,
            bjvfinc_orgn_code,
            bjvfinc_acct_code,
            NVL(DECODE(Override_Ind,'F',F_PROG,'O',O_PROG,F_PROG),bjvfinc_prog_code),
            bjvfinc_actv_code,
            bjvfinc_locn_code,
            bjvfinc_acct_code_external,
            bjvfinc_ptot_status
       FROM bjvfinc,
            (SELECT ftvfund_fund_code     F_FundB, 
                    ftvfund_bank_code     F_Bank
               FROM ftvfund
              WHERE ftvfund_coas_code = 'Z'
                AND ftvfund_eff_date <= SYSDATE
                AND ftvfund_nchg_date > SYSDATE
             ),
            (SELECT ftvfund_fund_code     F_Fund, 
                    ftvfund_prog_code_def F_PROG,
                    ftvfund_ftyp_code     F_FTYP,
                    ftvorgn_orgn_code     O_Orgn, 
                    ftvorgn_prog_code_def O_PROG
               FROM ftvfund, ftvorgn
              WHERE ftvfund_coas_code = 'Z'
                AND ftvfund_eff_date <= SYSDATE
                AND ftvfund_nchg_date > SYSDATE
                AND ftvorgn_eff_date <= SYSDATE
                AND (ftvorgn_term_date > SYSDATE 
                     OR
                     ftvorgn_term_date IS NULL)
                AND ftvorgn_nchg_date > SYSDATE 
                AND ftvorgn_coas_code ='Z'
             ),
             (SELECT ftvftyp_ftyp_code        FTyp_Code,
                     ftvftyp_def_override_ind Override_Ind
                FROM ftvftyp
               WHERE ftvftyp_coas_code = 'Z'
                 AND TRUNC(ftvftyp_eff_date) <= SYSDATE
                 AND ftvftyp_nchg_date > SYSDATE
                 AND ftvftyp_status_ind = 'A'
                 AND (ftvftyp_term_date > SYSDATE OR ftvftyp_term_date IS NULL)
             ) 
      WHERE bjvfinc_fund_code = F_Fund(+)
        AND bjvfinc_orgn_code = O_Orgn(+)
        AND bjvfinc_fund_code = F_FundB(+)
        AND FTyp_Code(+)      = NVL(F_FTyp,'.')
        AND bjvfinc_amt != 0
        AND bjvfinc_category_code = 'Z'
        AND bjvfinc_interfaced_ind = 'N';

-- Insert GURFEED Batch Header Record
INSERT into gurfeed
          ( gurfeed_system_id,
            gurfeed_system_time_stamp,
            gurfeed_doc_code,
            gurfeed_rec_type,
            gurfeed_submission_number,
            gurfeed_activity_date,
            gurfeed_user_id,
            gurfeed_trans_date,
            gurfeed_trans_amt,
            gurfeed_trans_desc
           )
     SELECT '&&system_id',
            '&&timestamp',
            '&&Batch_JV_Number',
            '1',
             0,
             SYSDATE,
             USER,
             TO_DATE('&&Batch_Trans_Date','DD-MON-YYYYHH24MISS'),
             &&Batch_Total,
             '&&Batch_Header_Desc'
       FROM DUAL;

COMMIT;

-- Transaction Count for Batch
COLUMN Transaction_Count NEW_VALUE Transaction_Count
SELECT COUNT(*) Transaction_Count FROM gurfeed
 WHERE gurfeed_system_id = '&&System_ID'
   AND gurfeed_system_time_stamp = '&&timestamp';

DELETE FROM bjvfinc WHERE bjvfinc_category_code = 'Z'; 

-- Alternate to Clearing bjvfinc would be 
--UPDATE bjvfinc
--   SET bjvfinc_interfaced_ind = 'Y',
--       bjvfinc_activity_date  = (SELECT SYSDATE FROM DUAL)
--        WHERE bjvfinc_category_code   = 'Z'
--          AND bjvfinc_interfaced_ind != 'Y';
--

-- Email Message If Input Errors
SET ECHO OFF
SET VERIFY OFF
SET TERMOUT OFF
SET PAGESIZE 0
SET LINESIZE 256
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET HEADING OFF
SET TIMING OFF

!rm -f bjvfeed_z_email_errors.sql
SET HEADING OFF
SPOOL bjvfeed_z_email_errors.sql
SELECT '!mail -s "Table Load Errors for Batch JV Load Feed" -c bknox@uaex.edu $USER@edison.uaex.edu < bjvfeed_validation_errors.txt'||CHR(10)
   FROM DUAL
WHERE &&Error_Count > 0;
SPOOL OFF
@bjvfeed_z_email_errors.sql
!rm -f bjvfeed_z_email_errors.sql
!rm -f bjvfeed_z_error.txt
!rm -f bjvfeed_validation_errors.txt

-- email Notice of Job Run

SET ECHO OFF
SET VERIFY OFF
SET TERMOUT OFF
SET PAGESIZE 0
SET LINESIZE 256
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET HEADING OFF
SET TIMING OFF

!rm -f bjvfeed_z_email.txt
SPOOL bjvfeed_z_email.txt

SELECT 'Report bjvfeed_z JV Load: '||'&&batch_description'||' - Batch Journal Voucher Load Control Report'||CHR(10)
       ||CHR(10)
       ||'For furfeed'||CHR(10)
       ||'Enter System ID and Time Stamp: '||'&&System_ID'||' '||TO_CHAR(TO_DATE('&&timestamp','YYYYMMDDHH24MISS'),'DD-MON-YYYYHH24MISS')||CHR(10)
       ||CHR(10)
       ||'DB Instance: '||'&&DB_Instance'||CHR(10)
       ||'Error Count: '||'&&Error_Count'||CHR(10)
       ||'      Count: '||'&&Transaction_Count'||CHR(10)
       ||'   Document: '||'&&Batch_JV_Number'||CHR(10)
       ||'Description: '||'&&Batch_Header_Desc'||CHR(10)
       ||'     Amount: '||LTRIM(TO_CHAR('&&Batch_Total','9,999,999,990.99B'))||CHR(10)
       ||CHR(10)
       ||'See bjv_print_z.lst Batch Journal Voucher Report'||CHR(10)
       ||CHR(10) 
       ||'Batch Entered: '|| TO_CHAR(SYSDATE,'fmMonth DD, yyyy')|| '  '|| TO_CHAR(SYSDATE,'HH:MI:SS am')
  FROM DUAL;

SPOOL OFF

!mail -s "JV Load Feed - Batch Journal Voucher"  -c bknox@uaex.edu $USER@edison.uaex.edu < bjvfeed_z_email.txt

!rm -f bjvfeed_z_email.txt

START bjv_print.sql &&Batch_JV_Number
!mv -f bjv_print.lst bjv_print_z.lst
!echo

SET TERMOUT OFF
-- Abort Program Exit 
*/
!echo
!rm -f bjvfeed_z_abort.sql
-- Abort Program Exit

--debug
--DELETE FROM gurfeed WHERE gurfeed_user_id = 'BKNOX'; 
--DELETE FROM bjvfinc WHERE bjvfinc_category_code = 'Z'; 

SET TERMOUT ON

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY';

EXIT