-- approval_periodic2.sql	generate 'Requisitions Awaiting Your Approval' email reports
--                            Include all dates for Unapproved Records
--**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

-- Commodity Transactions
@approval_periodic2_1
-- Accounting Transactions
@approval_periodic2_2


SET SHOWMODE OFF
SET VERIFY OFF
SET WRAP ON

-- change log
-- 12/05/01 bknox created
-- 02/13/03 bknox added check for Approval Table to make possible periodic running during a day
-- 05/28/03 bknox split code to eliminate out of space errors: ORA-01652: unable to extend temp segment by 150 in tablespace TEMP
-- 06/10/03 bknox added approval_clear_approved after updates

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 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 = 'Requisitions Awaiting Your Approval'

--     Title is the Report Title for the email message "report"
DEFINE Title_String = 'The following requisitions are awaiting your approval:'

COLUMN Ending_String NEW_VALUE FORMAT A512
SELECT 'Please login to Banner WebForms at http://webforms.uaex.edu/ and use Form FOAUAPP to approve these documents, '||CHR(09)||
'or login to Self Service at http://ban.uaex.edu and select Approve Documents from the Finance menu.'  Ending_String FROM DUAL;

!rm -f approval_periodic.lst

!echo "approval_periodic2 report beginning"
!echo "'approval_periodic2 Requisitions Awaiting Your Approval' email reports"

SPOOL approval_periodic

-- 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 "bknox@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 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'),
(
-- Requisition Line Item
SELECT  approval_wk_Report_pidm   Report_pidm,
        approval_wk_Sort_Order    Sort_Order,
        approval_wk_Detail_String Detail_String
FROM approval_wk
ORDER BY 1,2
)
WHERE spriden_pidm = Report_pidm
  AND spriden_pidm = email_pidm
  AND spriden_change_ind Is Null 
  AND spriden_entity_ind='P'
;

-- 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 approval_periodic_inter.txt

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

!rm -f approval_periodic.lst
!rm -f approval_periodic.txt

-- Remove Blank Lines
!egrep -v '                                                                                                                                                            ' approval_periodic_inter.txt > approval_periodic.txt

!rm -f approval_periodic_inter.txt

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

-- Flag All That Where Just Sent 
UPDATE approval
   SET approval_sent = 'Y'
 WHERE approval_sent = 'N';

DELETE approval_wk;

@approval_clear_approved.sql

CLEAR COLUMNS
CLEAR BREAK

SET HEADING ON

!echo "approval_periodic2 report complete"

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

SET TERMOUT ON