-- cookie_cutter.sql	generate email reports
--**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
-- 07/07/03 bknox created
-- 07/21/03 bknox made app live

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

-- use current date to get asofdate
COLUMN asofdate NOPRINT NEW_VALUE asofdate
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY') asofdate FROM DUAL;

-- 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 coa    NOPRINT NEW_VALUE coavar
SELECT 'Z' coa FROM DUAL;

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

COLUMN Sort_Order NOPRINT

SET NUMWIDTH 10

BREAK ON Address_String NODUPLICATES

--     Subject is the Subject Section of the email header
DEFINE Subject = 'email Subject text string'

--     Title is the Report Title for the email message "report"
DEFINE Title_String = 'Beginning of Text Message'
--     Heading is the Report Column Headings for the email message "report"
--  DEFINE Heading_String = 'Column Headings'

-- Ending String for the email message "report"
DEFINE Ending_String = 'Ending of Text Message'

!rm -f cookie_cutter.lst

!echo "cookie_cutter report beginning"
!echo "'cookie_cutter Notices' email reports" &&datevar &&timevar

SPOOL cookie_cutter

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

-- mail SELECT
SELECT Sort_Order, ' '||CHR(09)||'&&Ending_String'||CHR(09)||'eof'||CHR(09)||
--     'mail -s '||''''||'&&Subject'||'-c "mgr_@uaex.edu bknox_@uaex.edu"'||' '||Email_Address||' << eof'||CHR(09)||'&&Title_String'||CHR(09) Address_String, 
       'mail -s '||''''||'TEST '||'&&Subject'||' '||''''||' '||'-c "bknox_@uaex.edu"'||' '||'bknox_@uaex.edu'||' << eof'||CHR(09)||Email_Address||' '||'&&Title_String'||CHR(09) Address_String, 
        Detail_String, CHR(09) 
  FROM (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'),
-- Replace the following Sub-Select with your code, Report_pidm and Detail_String are Required
(
SELECT SYSDATE Sort_Order,
       1722    Report_pidm,
       'This was mailed '||SYSDATE Detail_String 
FROM DUAL
)
-- Replace the above     Sub-Select with your code, Report_pidm and Detail_String are Required
WHERE Report_pidm = email_pidm
ORDER BY Sort_Order
;

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

SPOOL OFF

-- correct record formats

!rm -f cookie_cutter_inter.txt

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

!rm -f cookie_cutter.lst
!rm -f cookie_cutter.txt

-- Remove Blank Lines
!egrep -v '                                                                                                                                                            ' cookie_cutter_inter.txt > cookie_cutter.txt

!rm -f cookie_cutter_inter.txt

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

CLEAR COLUMNS
CLEAR BREAK

SET HEADING ON

!echo "cookie_cutter report complete"

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

SET TERMOUT OFF
--@/home/common/gysbsec_reset.sql
SET TERMOUT ON