-- dd_audit.sql Update the dd_audit table with values from the FATCKIN temporary table

--**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

-- AP Direct Deposit Auditing.  
-- Stores values lost by Banner when the temporary tables are cleared.

SET SHOWMODE OFF
SET VERIFY OFF

-- change log
-- 01/23/02 bknox created

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

SET SCAN ON
SET TERMOUT OFF
SET PAGESIZE 0
SET LINESIZE 512
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET HEADING OFF
SET FEEDBACK OFF
SET TIMING OFF

-- SET ECHO OFF     Suppress Display of commands
-- SET FEEDBACK OFF Suppress Display of record counts
-- SET VERIFY   ON  Show each line of the file before and after substitution
-- SET SHOWMODE OFF Suppress Display of old and new setting of SQL*Plus system variables
-- SET LINESIZE n   Set line length
-- SET PAGESIZE n   Set lines per page
-- SET TRIMSPOOL ON Trim trailing spaces from end of SPOOLed record
-- SET TERMOUT  OFF Suppress Display of output

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

--DEBUG
-- SET ECHO ON
-- SET SHOWMODE ON
-- SET FEEDBACK ON
--DEBUG

SET NUMWIDTH 10

!echo 
!echo "dd_audit update beginning"
!echo 


CLEAR COLUMNS
CLEAR BREAK

COLUMN AcctType HEADING 'AcctType' FORMAT A8

SET HEADING ON
SET FEEDBACK ON
SET TERMOUT ON

!echo 
!echo "dd_audit found these records to Insert into dd_audit"
!echo 

  SELECT 
--    'T',
    FATCKIN_VEND_CHECK_PIDM PIDM,
    FATCKIN_CHECK_NUM "CheckNo",
    FATCKIN_INVH_CODE "InvCode",
    FATCKIN_CHECK_DATE "CheckDate",
    FATCKIN_NET_AMT "Amount",
    FATCKIN_ADDR_TYPE AT,
    FATCKIN_ADDR_SEQNO S,
    GXRDIRD_BANK_ROUT_NUM "BankRoute",
    GXRDIRD_BANK_ACCT_NUM "BankAcct",
    DECODE(GXRDIRD_ACCT_TYPE,'C','Checking','S','Savings',GXRDIRD_ACCT_TYPE)  AcctType,
    FATCKIN_PAYMT_TYPE_IND I
   FROM FATCKIN, GXRDIRD
  WHERE FATCKIN_VEND_CHECK_PIDM = GXRDIRD_PIDM
    AND FATCKIN_ADDR_TYPE       = GXRDIRD_ATYP_CODE
    AND FATCKIN_ADDR_SEQNO      = GXRDIRD_ADDR_SEQNO
    AND FATCKIN_PAYMT_TYPE_IND = 'D'
    AND GXRDIRD_AP_IND = 'A'
    AND GXRDIRD_DOC_TYPE = 'D'
    AND GXRDIRD_STATUS = 'A';

SET HEADING OFF
SET FEEDBACK ON

!echo 
!echo "dd_audit update beginning"
!echo 

INSERT INTO dd_audit
 ( DD_AUDIT_SOURCE,
   DD_AUDIT_VEND_CHECK_PIDM,
   DD_AUDIT_CHECK_NUM,
   DD_AUDIT_INVH_CODE,
   DD_AUDIT_CHECK_DATE,
   DD_AUDIT_NET_AMT,
   DD_AUDIT_ATYP_CODE_VEND,
   DD_AUDIT_ATYP_SEQ_NUM_VEND,
   DD_AUDIT_BANK_ROUT_NUM,
   DD_AUDIT_BANK_ACCT_NUM,
   DD_AUDIT_ACCT_TYPE,
   DD_AUDIT_PAYMT_TYPE_IND,
   DD_AUDIT_ACTIVITY_DATE
  )
  SELECT 
    'T',
    FATCKIN_VEND_CHECK_PIDM,
    FATCKIN_CHECK_NUM,
    FATCKIN_INVH_CODE,
    FATCKIN_CHECK_DATE,
    FATCKIN_NET_AMT,
    FATCKIN_ADDR_TYPE,
    FATCKIN_ADDR_SEQNO,
    GXRDIRD_BANK_ROUT_NUM,
    GXRDIRD_BANK_ACCT_NUM,
    GXRDIRD_ACCT_TYPE,
    FATCKIN_PAYMT_TYPE_IND,
    SYSDATE
   FROM FATCKIN, GXRDIRD
  WHERE FATCKIN_VEND_CHECK_PIDM = GXRDIRD_PIDM
    AND FATCKIN_ADDR_TYPE       = GXRDIRD_ATYP_CODE
    AND FATCKIN_ADDR_SEQNO      = GXRDIRD_ADDR_SEQNO
    AND FATCKIN_PAYMT_TYPE_IND = 'D'
    AND GXRDIRD_AP_IND = 'A'
    AND GXRDIRD_DOC_TYPE = 'D'
    AND GXRDIRD_STATUS = 'A';

COMMIT;

!echo
!echo "dd_audit report complete"
!echo

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