-- dd_email.sql	generate email messages for Reimbursements Direct Deposit

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

SET SHOWMODE OFF
SET VERIFY OFF

-- change log
-- 12/17/01 bknox created
-- 02/27/02 bknox changed to Reimbursements vs Travel, added 
--                Exceptions Report for no suitable email address
-- 11/19/02 bknox changed to Payment vs Reimbursement
-- 12/16/02 bknox changed to "I0065002:       $120.95 for Invoice# 188728279-001 Item: electric stapler"  format
-- 12/17/02 bknox changed exception report to same format.
-- 04/11/03 bknox added second exception report (for bad Vendor email addresses) - uses SPRADDR_ATYP_CODE = 'P1' rather than 'CM' and uses FABINVH_ATYP_SEQ_NUM = SPRADDR_SEQNO rather than MIN(SPRADDR_SEQNO)
-- 04/11/03 bknox changed to use /home/common/dd_email_exceptions_print.shl vs. dd_email_exceptions_print.shl    
-- 06/06/03 bknox changed to use multi-line invoices
-- 07/18/03 bknox changed expection reports to select only 'P' for Employees (People) and 'C' Venodor
-- 01/28/04 bknox REPLACEd Single Quotes, Double Quotes, Back Quotes, and Line Feeds in FARINVC_COMM_DESC to prevent mail errors

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

COLUMN Address_String NEW_VALUE Address_String FORMAT A512
COLUMN Detail_String  NEW_VALUE Detail_String  FORMAT A512
COLUMN Detail_String1  NEW_VALUE Detail_String1 FORMAT A512
COLUMN Ending_String  NEW_VALUE Ending_String FORMAT A512

COLUMN Sort_Order NOPRINT FORMAT A5 TRUNC

SET NUMWIDTH 10

BREAK ON Address_String NODUPLICATES ON Detail_String NODUPLICATES

--     Subject is the Subject Section of the email header
DEFINE Subject = 'Direct Deposit Notification'

--     Title is the Report Title for the email message "report"
DEFINE Title_String = ''
DEFINE Ending_String = 'Please allow 1 to 2 business days for processing by the bank. This payment should be applied as follows:'

!rm -f dd_email.lst

!echo " "
!echo "dd_email beginning"
!echo " "

SPOOL dd_email

-- The following SELECT is just to negate the first eof in the mail SELECT
SELECT 'cat << eof' FROM DUAL;

-- mail SELECT
SELECT Sort_Order,
'eof'||CHR(09)||'mail -s '||''''||'&&Subject'||''''||' '||'-c "bknox@uaex.edu bknox@uaex.edu"'||' '||Email_Address||' << eof'||CHR(09)||' '||CHR(09)||
        Detail_String1||' '||CHR(09)||'&&Ending_String'||CHR(09) Address_String, Detail_String, Detail_String2
  FROM SPRIDEN, 
       (SELECT goremal_pidm Email_pidm, RTRIM(NVL(goremal_email_address,'bknox@uaex.edu')) Email_Address FROM goremal WHERE GOREMAL_STATUS_IND = 'A' AND GOREMAL_PREFERRED_IND = 'Y' AND goremal_email_address != 'N/A' AND goremal_email_address != 'n/a' AND (goremal_emal_code = 'DD' OR goremal_emal_code = 'SMTP')),
(
SELECT LTRIM(TO_CHAR(DD_AUDIT_VEND_CHECK_PIDM))||FARINVC_INVH_CODE||FARINVC_COMM_DESC  Sort_Order,
 DD_AUDIT_VEND_CHECK_PIDM Report_pidm,
'A payment has been sent to your '||DECODE(DD_AUDIT_ACCT_TYPE,'C','checking account','S','savings account','bank account')||
' by Direct Deposit on '|| TO_CHAR(DD_AUDIT_CHECK_DATE, 'MM/DD/YYYY')||'.' Detail_String1,
' '||CHR(09)||FARINVC_INVH_CODE||LPAD(DECODE(SIGN(DD_AUDIT_NET_AMT),-1,'-',' ')||'\'||LTRIM(TO_CHAR(ABS(DD_AUDIT_NET_AMT),'$999,999,999.99')),16) || 
' for Invoice# '||FABINVH_VEND_INV_CODE||DECODE(FABINVH_POHD_CODE,NULL,NULL,' PO# ')||FABINVH_POHD_CODE Detail_String,
'  Item: '||REPLACE(REPLACE(REPLACE(REPLACE(FARINVC_COMM_DESC,CHR(034),' '),CHR(039),' '),CHR(096),' '),CHR(010),'') Detail_String2
  FROM DD_AUDIT, FARINVC,
(
SELECT   --  Invoice Header Table
  FABINVH_CODE          FABINVH_CODE,
  FABINVH_VEND_INV_CODE FABINVH_VEND_INV_CODE,
  FABINVH_POHD_CODE     FABINVH_POHD_CODE
  FROM FABINVH, FABINCK
        WHERE  FABINVH_CODE = FABINCK_INVH_CODE
          AND FABINCK_CANCEL_IND IS NULL
)
 WHERE 
 TRUNC(DD_AUDIT_CHECK_DATE) = TRUNC(SYSDATE)
-- TRUNC(DD_AUDIT_CHECK_DATE) = TRUNC(SYSDATE-1) -- test yesterday
   AND FARINVC_INVH_CODE = DD_AUDIT_INVH_CODE
   AND FARINVC_INVH_CODE = FABINVH_CODE
) 
WHERE spriden_pidm = Report_pidm
  AND spriden_pidm = email_pidm
  AND spriden_change_ind Is Null 
ORDER BY Sort_Order;

-- Sort_Date DESC

-- The following just terminates the Last email message text with eof
SELECT 'eof'||CHR(09) FROM DUAL;

SPOOL OFF

-- correct record formats

!rm -f dd_email_inter.txt

-- translate Tab CHR(09) to LF CHR(10) and remove duplicate newlines
!cat dd_email.lst | tr '\011' '\012' | tr -s '\n'  > dd_email_inter.txt

!rm -f dd_email.lst
!rm -f dd_email.txt

-- Remove Blank Lines
!egrep -v '                                                                                                                                                            ' dd_email_inter.txt > dd_email.txt

!rm -f dd_email_inter.txt

!mv dd_email.txt dd_email_send.shl
!chmod 740 dd_email_send.shl
-- BE VERY SURE FIRST !dd_email_send.shl

--CLEAR COLUMNS
--CLEAR BREAK

COLUMN ZIP NOPRINT
COLUMN SPRIDEN_LAST_NAME NOPRINT

SET HEADING OFF

-- Exceptions for Employees
-- Employee Exceptions Beginning

SPOOL dd_email_exceptions

SELECT  -- Reimbursement Direct Deposit Audit
CHR(12)||
'A payment has been sent to your '||DECODE(DD_AUDIT_ACCT_TYPE,'C','checking account','S','savings account','bank account')||CHR(09)||
'by Direct Deposit on '|| TO_CHAR(DD_AUDIT_CHECK_DATE, 'MM/DD/YYYY')||'.',
SPRADDR_ZIP ZIP, SPRIDEN_LAST_NAME,
' '||CHR(09)||' '||CHR(09)||' '||CHR(09)||
' '||CHR(09)||' '||CHR(09)||' '||CHR(09)||
' '||CHR(09)||' '||CHR(09)||' '||CHR(09)||
' '||CHR(09)||
'     '||DECODE(spriden_entity_ind,                                         
             'C',substr(spriden_last_name,                                                              1,16),  
             'P', substr(DECODE(SPBPERS_NAME_PREFIX,                                   
                 '',(SPRIDEN_FIRST_NAME || ' ' ||                               
                      DECODE(SPRIDEN_MI,'',SPRIDEN_LAST_NAME||' '||             
                                             SPBPERS_NAME_SUFFIX,               
                                           SUBSTR(SPRIDEN_MI,1,1) || '. ' ||    
                                             SPRIDEN_LAST_NAME || ' ' ||        
                                             SPBPERS_NAME_SUFFIX)),             
                     SPBPERS_NAME_PREFIX || ' ' || SPRIDEN_FIRST_NAME || ' ' || 
                      DECODE(SPRIDEN_MI,'',SPRIDEN_LAST_NAME || ' ' ||          
                                             SPBPERS_NAME_SUFFIX,               
                                           SUBSTR(SPRIDEN_MI,1,1) || '. ' ||    
                                             SPRIDEN_LAST_NAME || ' ' ||        
                                             SPBPERS_NAME_SUFFIX)),1,22)) ||CHR(09)|| --  "Name",
  '     '||SPRADDR_STREET_LINE1||CHR(09)
  ||DECODE(SPRADDR_STREET_LINE2,NULL,NULL,'     '||SPRADDR_STREET_LINE2||CHR(09))
  ||DECODE(SPRADDR_STREET_LINE3,NULL,NULL,'     '||SPRADDR_STREET_LINE3||CHR(09))||
  '     '||SPRADDR_CITY||' '||SPRADDR_STAT_CODE||' '||SPRADDR_ZIP||CHR(09)||' '||CHR(09)||' '||CHR(09)||' '||CHR(09)
||' '||CHR(09)||'&&Ending_String' ||CHR(09)||' '
 ||CHR(09)||FARINVC_INVH_CODE||LPAD(LTRIM(TO_CHAR(DD_AUDIT_NET_AMT,'$999,999,999.99')),16) || 
' for Invoice# '||FABINVH_VEND_INV_CODE||DECODE(FABINVH_POHD_CODE,NULL,NULL,' PO# ')||FABINVH_POHD_CODE||
' Item: '||FARINVC_COMM_DESC Detail_String
            FROM SPRIDEN, SPBPERS, DD_AUDIT, FARINVC,
(
SELECT   --  Invoice Header Table
  FABINVH_CODE          FABINVH_CODE,
  FABINVH_VEND_INV_CODE FABINVH_VEND_INV_CODE,
  FABINVH_POHD_CODE     FABINVH_POHD_CODE
  FROM FABINVH, FABINCK
        WHERE  FABINVH_CODE = FABINCK_INVH_CODE
          AND FABINCK_CANCEL_IND IS NULL
),
(SELECT
  SPRADDR_PIDM SPRADDR_PIDM,
  SPRADDR_STREET_LINE1 SPRADDR_STREET_LINE1,
  SPRADDR_STREET_LINE2 SPRADDR_STREET_LINE2,
  SPRADDR_STREET_LINE3 SPRADDR_STREET_LINE3,
  SPRADDR_CITY SPRADDR_CITY,
  SPRADDR_STAT_CODE SPRADDR_STAT_CODE,
  SPRADDR_ZIP SPRADDR_ZIP
     FROM spraddr Y
   WHERE ((Y.SPRADDR_STATUS_IND IS NULL) OR (Y.SPRADDR_STATUS_IND = 'A'))
     AND SYSDATE BETWEEN NVL(SPRADDR_FROM_DATE,SYSDATE) AND NVL(SPRADDR_TO_DATE,SYSDATE) 
     AND Y.SPRADDR_ATYP_CODE = 'CM'
     AND (TRUNC(Y.SPRADDR_FROM_DATE)<= SYSDATE
      Or Y.SPRADDR_FROM_DATE Is Null) 
  AND Y.SPRADDR_SEQNO =(SELECT MIN(X.SPRADDR_SEQNO)
   FROM SPRADDR X
  WHERE X.SPRADDR_PIDM = Y.SPRADDR_PIDM
    AND SYSDATE BETWEEN NVL(SPRADDR_FROM_DATE,SYSDATE) AND NVL(SPRADDR_TO_DATE,SYSDATE) 
    AND X.SPRADDR_ATYP_CODE = Y.SPRADDR_ATYP_CODE
    AND X.SPRADDR_SEQNO     = Y.SPRADDR_SEQNO
    AND ((X.SPRADDR_STATUS_IND IS NULL) OR (X.SPRADDR_STATUS_IND = 'A'))
    AND (TRUNC(X.SPRADDR_FROM_DATE)<= SYSDATE
      Or X.SPRADDR_FROM_DATE Is Null))
)
           WHERE SPRIDEN_PIDM = SPBPERS_PIDM (+)                                
             AND spriden_change_ind is null
             AND spriden_entity_ind = 'P'
             AND SPRIDEN_PIDM = DD_AUDIT_VEND_CHECK_PIDM
--             AND TRUNC(DD_AUDIT_CHECK_DATE) = TRUNC(SYSDATE-1) -- test yesterday
             AND TRUNC(DD_AUDIT_CHECK_DATE) = TRUNC(SYSDATE)
             AND FARINVC_INVH_CODE = DD_AUDIT_INVH_CODE
             AND FARINVC_INVH_CODE = FABINVH_CODE
             AND SPRIDEN_PIDM = SPRADDR_PIDM
             AND SPRIDEN_PIDM NOT IN
(SELECT goremal_pidm FROM goremal WHERE GOREMAL_STATUS_IND = 'A' AND GOREMAL_PREFERRED_IND = 'Y' AND goremal_email_address != 'N/A' AND goremal_email_address != 'n/a' AND goremal_emal_code = 'SMTP')
;

SPOOL OFF

!rm -f dd_email_inter.txt

-- translate Tab CHR(09) to LF CHR(10) and remove duplicate newlines
!cat dd_email_exceptions.lst | tr '\011' '\012' | tr -s '\n'  > dd_email_inter.txt

!rm -f dd_email_exceptions.lst
!rm -f dd_email.txt

-- Remove Blank Lines
!egrep -v '                                                                                                                                                            ' dd_email_inter.txt > dd_email.txt

!rm -f dd_email_inter.txt

!mv dd_email.txt dd_email_exceptions.lst
!chmod 666 dd_email_exceptions.lst

-- Employee Exceptions Complete

-- Exceptions for Vendors
-- Vendor Exceptions Beginning

BREAK ON aPayment NODUPLICATES ON Address_String NODUPLICATES ON Detail_String NODUPLICATES 
apayment

SPOOL dd_email_exceptions2 

SELECT  -- Reimbursement Direct Deposit Audit
CHR(12)||
'A payment has been sent to your '||DECODE(DD_AUDIT_ACCT_TYPE,'C','checking account','S','savings account','bank account')||CHR(09)||
'by Direct Deposit on '|| TO_CHAR(DD_AUDIT_CHECK_DATE, 'MM/DD/YYYY')||'.' aPayment,
SPRADDR_ZIP ZIP, SPRIDEN_LAST_NAME,
' '||CHR(09)||' '||CHR(09)||' '||CHR(09)||
' '||CHR(09)||' '||CHR(09)||' '||CHR(09)||
' '||CHR(09)||' '||CHR(09)||' '||CHR(09)||
' '||CHR(09)||
'     '||DECODE(spriden_entity_ind,                                         
             'C',substr(spriden_last_name,                                                              1,16),  
             'P', substr(DECODE(SPBPERS_NAME_PREFIX,                                   
                 '',(SPRIDEN_FIRST_NAME || ' ' ||                               
                      DECODE(SPRIDEN_MI,'',SPRIDEN_LAST_NAME||' '||             
                                             SPBPERS_NAME_SUFFIX,               
                                           SUBSTR(SPRIDEN_MI,1,1) || '. ' ||    
                                             SPRIDEN_LAST_NAME || ' ' ||        
                                             SPBPERS_NAME_SUFFIX)),             
                     SPBPERS_NAME_PREFIX || ' ' || SPRIDEN_FIRST_NAME || ' ' || 
                      DECODE(SPRIDEN_MI,'',SPRIDEN_LAST_NAME || ' ' ||          
                                             SPBPERS_NAME_SUFFIX,               
                                           SUBSTR(SPRIDEN_MI,1,1) || '. ' ||    
                                             SPRIDEN_LAST_NAME || ' ' ||        
                                             SPBPERS_NAME_SUFFIX)),1,22)) ||CHR(09)|| --  "Name",
  '     '||SPRADDR_STREET_LINE1||CHR(09)
  ||DECODE(SPRADDR_STREET_LINE2,NULL,NULL,'     '||SPRADDR_STREET_LINE2||CHR(09))
  ||DECODE(SPRADDR_STREET_LINE3,NULL,NULL,'     '||SPRADDR_STREET_LINE3||CHR(09))||
  '     '||SPRADDR_CITY||' '||SPRADDR_STAT_CODE||' '||SPRADDR_ZIP||CHR(09)||' '||CHR(09)||' '||CHR(09)||' '||CHR(09)
||' '||CHR(09)||'&&Ending_String' Address_String, CHR(09), CHR(09),
 ' '||CHR(09)||FARINVC_INVH_CODE||LPAD(LTRIM(TO_CHAR(DD_AUDIT_NET_AMT,'$999,999,999.99')),16) || 
' for Invoice# '||FABINVH_VEND_INV_CODE||DECODE(FABINVH_POHD_CODE,NULL,NULL,' PO# ')||FABINVH_POHD_CODE Detail_String,
' Item: '||FARINVC_COMM_DESC Detail_String2
            FROM SPRIDEN, SPBPERS, DD_AUDIT, FARINVC,
(
SELECT   --  Invoice Header Table
  FABINVH_CODE          FABINVH_CODE,
  FABINVH_ATYP_SEQ_NUM  FABINVH_ATYP_SEQ_NUM,
  FABINVH_VEND_INV_CODE FABINVH_VEND_INV_CODE,
  FABINVH_POHD_CODE     FABINVH_POHD_CODE
  FROM FABINVH, FABINCK
        WHERE  FABINVH_CODE = FABINCK_INVH_CODE
          AND FABINCK_CANCEL_IND IS NULL
),
(SELECT
  SPRADDR_PIDM SPRADDR_PIDM,
  SPRADDR_SEQNO SPRADDR_SEQNO,
  SPRADDR_STREET_LINE1 SPRADDR_STREET_LINE1,
  SPRADDR_STREET_LINE2 SPRADDR_STREET_LINE2,
  SPRADDR_STREET_LINE3 SPRADDR_STREET_LINE3,
  SPRADDR_CITY SPRADDR_CITY,
  SPRADDR_STAT_CODE SPRADDR_STAT_CODE,
  SPRADDR_ZIP SPRADDR_ZIP
     FROM spraddr Y
   WHERE ((Y.SPRADDR_STATUS_IND IS NULL) OR (Y.SPRADDR_STATUS_IND = 'A'))
     AND SYSDATE BETWEEN NVL(SPRADDR_FROM_DATE,SYSDATE) AND NVL(SPRADDR_TO_DATE,SYSDATE) 
     AND Y.SPRADDR_ATYP_CODE = 'P1'
     AND (TRUNC(Y.SPRADDR_FROM_DATE)<= SYSDATE
      Or Y.SPRADDR_FROM_DATE Is Null) 
  AND Y.SPRADDR_SEQNO =(SELECT X.SPRADDR_SEQNO
   FROM SPRADDR X
  WHERE X.SPRADDR_PIDM = Y.SPRADDR_PIDM
    AND SYSDATE BETWEEN NVL(SPRADDR_FROM_DATE,SYSDATE) AND NVL(SPRADDR_TO_DATE,SYSDATE) 
    AND X.SPRADDR_ATYP_CODE = Y.SPRADDR_ATYP_CODE
    AND X.SPRADDR_SEQNO     = Y.SPRADDR_SEQNO
    AND ((X.SPRADDR_STATUS_IND IS NULL) OR (X.SPRADDR_STATUS_IND = 'A'))
    AND (TRUNC(X.SPRADDR_FROM_DATE)<= SYSDATE
      Or X.SPRADDR_FROM_DATE Is Null))
)
           WHERE SPRIDEN_PIDM = SPBPERS_PIDM (+)                                
             AND spriden_change_ind is null
             AND spriden_entity_ind = 'C'
             AND SPRIDEN_PIDM = DD_AUDIT_VEND_CHECK_PIDM
--           AND TRUNC(DD_AUDIT_CHECK_DATE) = TRUNC(SYSDATE-1) -- test yesterday
             AND TRUNC(DD_AUDIT_CHECK_DATE) = TRUNC(SYSDATE)
             AND FARINVC_INVH_CODE = DD_AUDIT_INVH_CODE
             AND FARINVC_INVH_CODE = FABINVH_CODE
             AND FABINVH_ATYP_SEQ_NUM = SPRADDR_SEQNO
             AND SPRIDEN_PIDM = SPRADDR_PIDM
             AND SPRIDEN_PIDM NOT IN 
(SELECT goremal_pidm
     FROM goremal
    WHERE GOREMAL_STATUS_IND = 'A'
      AND GOREMAL_PREFERRED_IND = 'Y'
      AND GOREMAL_EMAL_CODE = 'DD'
      AND goremal_email_address != 'N/A'
      AND goremal_email_address != 'n/a')
;

SPOOL OFF

!rm -f dd_email_inter2.txt

-- translate Tab CHR(09) to LF CHR(10) and remove duplicate newlines
!cat dd_email_exceptions2.lst | tr '\011' '\012' | tr -s '\n'  > dd_email_inter2.txt

!rm -f dd_email_exceptions2.lst
!rm -f dd_email.txt

-- Remove Blank Lines
!egrep -v '                                                                                                                                                            ' dd_email_inter2.txt > dd_email2.txt

!rm -f dd_email_inter2.txt

!mv dd_email2.txt dd_email_exceptions2.lst
!chmod 666 dd_email_exceptions2.lst

-- Vendor Exceptions Complete

!echo
!echo "dd_email report complete"
!echo

-- check if anything to print?  if so, .shl will ask if you want to print now??
!
/home/common/dd_email_exceptions_print.shl

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