-- fund_changed_notice.sql	generate 'FUND Changed Notice' email 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

SET SHOWMODE OFF
SET VERIFY OFF

-- change log
-- 04/24/03 bknox created
-- 02/26/04 bknox added FTVFUND_USER_ID to report
-- 03/02/04 bknox reformatted the message

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 Tx_Cnt NEW_VALUE Tx_Cnt NOPRINT FORMAT 99999
SELECT 0 Tx_Cnt FROM DUAL;

SET NUMWIDTH 10

!rm -f fund_changed_notice.txt

!echo "fund_changed_notice report beginning"

SPOOL fund_changed_notice.txt

-- mail SELECT
SELECT LPAD(FTVFUND_USER_ID,8),' Changed ', FTVFUND_ACTIVITY_DATE ACT_DATE,
       ' Effective', FTVFUND_EFF_DATE   EFF_DATE,   TO_CHAR(FTVFUND_EFF_DATE,'HH:MI:SS am') EFF_TIME,
       ' Fund', FTVFUND_FUND_CODE  FUND, FTVFUND_TITLE, 1 + &&Tx_Cnt Tx_Cnt
  FROM FTVFUND
 WHERE TRUNC(FTVFUND_ACTIVITY_DATE) = TRUNC(SYSDATE-1)
   AND FTVFUND_FUND_CODE IN
(
SELECT FUND -- Funds with Multiple Records
  FROM
(SELECT 1 CNT,
        FTVFUND_FUND_CODE  FUND, FTVFUND_TITLE,
        FTVFUND_EFF_DATE   EFF_DATE,
        FTVFUND_ACTIVITY_DATE ACT_DATE,
        FTVFUND_USER_ID FTVFUND_USER_ID
  FROM FTVFUND
 WHERE FTVFUND_ACTIVITY_DATE < TRUNC(SYSDATE)  -- Avoids Report Due To Todays Changes
)
 GROUP BY FUND
HAVING SUM(CNT) > 1
)
UNION  -- FUND NOT IN TABLE Before Yesterday
SELECT LPAD(FTVFUND_USER_ID,8),LPAD(' Added   ',9),FTVFUND_ACTIVITY_DATE ACT_DATE,
       ' Effective', FTVFUND_EFF_DATE   EFF_DATE, TO_CHAR(FTVFUND_EFF_DATE,'HH:MI:SS am') EFF_TIME,
       ' Fund', FTVFUND_FUND_CODE  FUND, FTVFUND_TITLE, 1 + &&Tx_Cnt Tx_Cnt
  FROM FTVFUND
 WHERE TRUNC(FTVFUND_ACTIVITY_DATE) = TRUNC(SYSDATE-1)
   AND FTVFUND_FUND_CODE NOT IN
(
SELECT FTVFUND_FUND_CODE  -- FUND IN Table Before Yesterday
  FROM FTVFUND
 WHERE TRUNC(FTVFUND_ACTIVITY_DATE) < TRUNC(SYSDATE-1)
)
 ORDER BY 2,3,8;

SPOOL OFF

-- correct record formats

SET ECHO OFF
SET SHOWMODE OFF
SET TIMING OFF
SET FEEDBACK ON
SET HEADING ON
SET LINESIZE 512
SET TRIMSPOOL ON
SET PAGESIZE 0          
SET NEWPAGE 0
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
TTITLE OFF
BTITLE OFF
CLEAR BREAK
CLEAR COMPUTE
SET TERMOUT OFF
SET HEADING OFF
SET PAGESIZE 0   
-- no page breaks
SET WRAP OFF
SET SPACE 0      
-- no space(s) between columns
SET TIME OFF
SET TIMING OFF   
SET FEEDBACK OFF

!rm -f fund_changed_send.sql
SPOOL fund_changed_send.sql
SELECT
'!mail -s "Fund Changed Notice" -c "financemanager@uaex.edu bknox@uaex.edu" fundmanager@uaex.edu < fund_changed_notice.txt'
FROM DUAL
WHERE 0 < &&Tx_Cnt;
SPOOL OFF

SET SPACE 1
SET colsep " "
SET FEEDBACK ON
SET linesize 80
SET newpage 1
SET pagesize 14
SET termout ON
SET trimspool OFF
SET VERIFY ON
SET WRAP ON
SET TIME ON
SET TIMING ON   
SET FEEDBACK ON
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
SET HEADING ON
TTITLE ON
BTITLE ON

-- Send Any Notices Found     
START fund_changed_send.sql

CLEAR COLUMNS
CLEAR BREAK

SET HEADING ON

!echo "fund_changed_notice report complete"

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