-- approval_periodic.sql	Generate 'Requisitions Awaiting Your Approval' email Reports
--                            approval_periodic.sql only extracts today's Unapproved Requisitions
--                            This is nearly twice as fast as approval_periodic2.sql
--**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 SHOWMODE OFF
SET VERIFY OFF

-- change log
-- 12/05/01 bknox created
-- 01/02/02 bknox modified !echo statements to better track runs reported to standard output
--                and standard error from cron
-- 01/08/02 bknox added to address list for cc
-- 01/23/02 bknox added ending / to url link
-- 01/29/02 bknox remove pebempb checking for manager
-- 04/09/02 bknox changed a cc address
-- 12/03/02 bknox changed to use FOAUAPP Form logic to show only those items that can be seen with FOAUAPP (Next Indicator = N)
-- 12/10/02 bknox changed to show Requested For, Commodity, Vendor, and Request Details
-- 01/14/03 bknox added Accounting information using UNION, corrected sort order for Item #s, removed extra blank lines.
-- 01/21/03 bknox removed FTVFSPD_PRD_STATUS_IND = 'O' from Accounting Information selection criteria
-- 02/05/03 bknox changed from FOBAINP_ACTIVITY_DATE to FOBUAPP_ACTIVITY_DATE to select yesterday's transactions (to eliminate orphaned transactions)
-- 02/13/03 bknox added check for Approval Table to make possible periodic running during a day

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
--COLUMN Sort_Date  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:'
--     Heading is the Report Column Headings for the email message "Report"
-- DEFINE Heading_String = 'Requisition # Requested by'
DEFINE Ending_String = 'Please login to Banner WebForms at http://webforms_server.uaex.edu/ and use Form FOAUAPP to approve these documents.'

!rm -f approval_periodic.lst

!echo "approval_periodic report beginning"
!echo "'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 "bpowell@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'),
--     pebempl, 
(
-- Requisition Line Item
SELECT  GOBEACC_PIDM               Report_pidm,
--       FORAQUS_USER_ID_APPR||FPBREQH_NAME||
         FOBAINP_ACTIVITY_DATE||FPBREQH_CODE||'1'||TO_CHAR(FPRREQD_ITEM,'99909') Sort_Order,  -- Approver_UserID||Requestor
--       FOBAINP_ACTIVITY_DATE Sort_Date,
         ' '||CHR(09)||TO_CHAR(FOBAINP_ACTIVITY_DATE , 'MM/DD/YYYY')||' '||
         'Requisition: '|| FPBREQH_CODE||' Item: '||TO_CHAR(FPRREQD_ITEM,'99909')||' Requested by: '||FPBREQH_NAME||' For: '||FPBREQH_ATTENTION_TO||CHR(09)||'Description: '||FPRREQD_COMM_DESC||CHR(09)||
         'Vendor: '||NVL(VENDOR_NAME,NULL)||FPRREQD_RECOMM_VEND_NAME||CHR(09)||
         'QTY: '||FPRREQD_QTY||'  Unit Price:'||' \'||LTRIM(TO_CHAR(FPRREQD_UNIT_PRICE,'$999,999,999.9999'))||
         DECODE(FPRREQD_TAX_AMT,0,NULL,'  Sales Tax:' ||' \'||LTRIM(TO_CHAR(FPRREQD_TAX_AMT,'$999,999,999.99')))||
         DECODE(FPRREQD_ADDL_CHRG_AMT,0,NULL,'  Additional Amt:'||' \'||LTRIM(TO_CHAR(FPRREQD_ADDL_CHRG_AMT,'$999,999,999.99')))||
         DECODE(ROUND(FPRREQD_UNIT_PRICE,2),ROUND(FPRREQD_QTY*FPRREQD_UNIT_PRICE ,2)+ FPRREQD_TAX_AMT + FPRREQD_ADDL_CHRG_AMT,NULL,'  Total:'||' \'||LTRIM(TO_CHAR(ROUND(FPRREQD_QTY*FPRREQD_UNIT_PRICE ,2)+ FPRREQD_TAX_AMT + FPRREQD_ADDL_CHRG_AMT,'$999,999,999.99'))) Detail_String
FROM
(SELECT  
-- Created from TABLE
-- FOBAINP: Approvals in Process Table
-- FOBUAPP: Unapproved Document Table 
-- FORAQUS: Approval Queue Definition Table
  FOBAINP_DOC_NUM   FOBAINP_DOC_NUM,  -- Document or Requisition Number
  FOBAINP_DOC_TYPE  FOBAINP_DOC_TYPE,
  TRUNC(FOBAINP_ACTIVITY_DATE)         FOBAINP_ACTIVITY_DATE,
  FOBAINP_USER_ID,
  FOBAINP_QUEUE_ID  FOBAINP_QUEUE_ID, -- Approval_Queue
  FOBAINP_LEVEL     FOBAINP_LEVEL,    -- Approver_Level
  FOBAINP_CHG_SEQ_NUM       FOBAINP_CHG_SEQ_NUM,
  FOBAINP_SUBMISSION_NUM    FOBAINP_SUBMISSION_NUM,
  FOBAINP_APPROVAL_SEQUENCE FOBAINP_APPROVAL_SEQUENCE,
  FORAQUS_USER_ID_APPR      FORAQUS_USER_ID_APPR
   FROM FOBAINP, FOBUAPP, FORAQUS
WHERE fobainp_doc_num LIKE 'R%'
  AND fobainp_doc_num = fobuapp_doc_code
  AND fobainp_doc_type = fobuapp_seq_num
  AND ((fobainp_chg_seq_num = fobuapp_chg_seq_num) OR (fobainp_chg_seq_num IS NULL AND fobuapp_chg_seq_num IS NULL))
  AND ((fobainp_submission_num = fobuapp_submission_number) OR (fobainp_submission_num IS NULL AND fobuapp_submission_number IS NULL))
  AND fobainp_approval_sequence = 
(
SELECT MIN(x.fobainp_approval_sequence)
  FROM fobainp x
 WHERE x.fobainp_doc_num = fobuapp_doc_code
   AND x.fobainp_doc_num LIKE 'R%'
   AND x.fobainp_doc_type = fobuapp_seq_num 
   AND ((x.fobainp_chg_seq_num = fobuapp_chg_seq_num) OR (x.fobainp_chg_seq_num IS NULL AND fobuapp_chg_seq_num IS NULL)) 
   AND ((x.fobainp_submission_num = fobuapp_submission_number) OR (x.fobainp_submission_num IS NULL AND fobuapp_submission_number IS NULL))
)
   AND TRUNC(FORAQUS_EFF_DATE) <= SYSDATE
   AND      (FORAQUS_NCHG_DATE  > TRUNC(SYSDATE) OR FORAQUS_NCHG_DATE IS NULL)
   AND      (FORAQUS_TERM_DATE  > TRUNC(SYSDATE) OR FORAQUS_TERM_DATE IS NULL)
   AND foraqus_queue_id    = fobainp_queue_id
   AND foraqus_queue_level = fobainp_level
),
(SELECT  DISTINCT foraqus_user_id_appr TODAYS_user_id_appr
--, FOBAINP_QUEUE_ID  FOBAINP_QUEUE_ID
   FROM FOBAINP, FOBUAPP, FORAQUS
WHERE fobainp_doc_num LIKE 'R%'
--AND TRUNC(FOBUAPP_ACTIVITY_DATE) = TRUNC(SYSDATE-1) -- Yesterday's
  AND TRUNC(FOBUAPP_ACTIVITY_DATE) = TRUNC(SYSDATE)   -- Today's
  AND fobainp_doc_num = fobuapp_doc_code
  AND fobainp_doc_type = fobuapp_seq_num
  AND ((fobainp_chg_seq_num = fobuapp_chg_seq_num) OR (fobainp_chg_seq_num IS NULL AND fobuapp_chg_seq_num IS NULL))
  AND ((fobainp_submission_num = fobuapp_submission_number) OR (fobainp_submission_num IS NULL AND fobuapp_submission_number IS NULL))
  AND fobainp_approval_sequence = 
(
SELECT MIN(x.fobainp_approval_sequence)
  FROM fobainp x
 WHERE x.fobainp_doc_num = fobuapp_doc_code
   AND x.fobainp_doc_num LIKE 'R%'
-- AND TRUNC(FOBUAPP_ACTIVITY_DATE) = TRUNC(SYSDATE-1) -- Yesterday's
   AND TRUNC(FOBUAPP_ACTIVITY_DATE) = TRUNC(SYSDATE)   -- Today's
   AND x.fobainp_doc_type = fobuapp_seq_num 
   AND ((x.fobainp_chg_seq_num = fobuapp_chg_seq_num) OR (x.fobainp_chg_seq_num IS NULL AND fobuapp_chg_seq_num IS NULL)) 
   AND ((x.fobainp_submission_num = fobuapp_submission_number) OR (x.fobainp_submission_num IS NULL AND fobuapp_submission_number IS NULL))
)
   AND TRUNC(FORAQUS_EFF_DATE) <= SYSDATE
   AND      (FORAQUS_NCHG_DATE  > TRUNC(SYSDATE) OR FORAQUS_NCHG_DATE IS NULL)
   AND      (FORAQUS_TERM_DATE  > TRUNC(SYSDATE) OR FORAQUS_TERM_DATE IS NULL)
   AND foraqus_queue_id    = fobainp_queue_id
   AND foraqus_queue_level = fobainp_level
),
GOBEACC,FPRREQD,
(SELECT   -- Created from TABLE FPBREQH  Request Header Table
  FPBREQH_CODE                         FPBREQH_CODE, -- Document or Requisition
  TRUNC(FPBREQH_ACTIVITY_DATE)         FPBREQH_ACTIVITY_DATE,
  TRUNC(FPBREQH_REQH_DATE)             FPBREQH_REQH_DATE,
  TRUNC(FPBREQH_TRANS_DATE)            FPBREQH_TRANS_DATE,
  FPBREQH_NAME                         FPBREQH_NAME,  -- Requestor
  TRUNC(FPBREQH_REQD_DATE)             FPBREQH_REQD_DATE,
  TRUNC(FPBREQH_CANCEL_DATE)           FPBREQH_CANCEL_DATE,
  TRUNC(FPBREQH_POST_DATE)             FPBREQH_POST_DATE,
  FPBREQH_ATTENTION_TO                 FPBREQH_ATTENTION_TO,
  TRUNC(FPBREQH_REQ_PRINT_DATE)        FPBREQH_REQ_PRINT_DATE,
  TRUNC(FPBREQH_CLOSED_DATE)           FPBREQH_CLOSED_DATE
  FROM  FPBREQH
),
(SELECT SPRIDEN_PIDM VENDOR_PIDM, DECODE(SPRIDEN_ENTITY_IND, 'C',
       SPRIDEN_LAST_NAME, 'P', DECODE(SPRIDEN_MI, '', (
       SPRIDEN_LAST_NAME || ', ' || SPRIDEN_FIRST_NAME), (
       SPRIDEN_LAST_NAME || ', ' || SPRIDEN_FIRST_NAME ||
       ' ' || SUBSTR(SPRIDEN_MI, 1, 1) || '.'))) VENDOR_NAME
  FROM SPRIDEN
 WHERE SPRIDEN_CHANGE_IND IS NULL
   AND SPRIDEN_PIDM IN 
(SELECT FPRREQD_VEND_PIDM FROM FPRREQD WHERE FPRREQD_REQH_CODE LIKE 'R%')
)
WHERE GOBEACC_USERNAME = FORAQUS_USER_ID_APPR
  AND FOBAINP_DOC_NUM   = FPBREQH_CODE
  AND FPRREQD_REQH_CODE = FPBREQH_CODE
  AND TODAYS_user_id_appr = FORAQUS_USER_ID_APPR
  AND FPRREQD_VEND_PIDM = VENDOR_PIDM (+)
  AND FOBAINP_DOC_NUM IN (SELECT APPROVAL_DOC_CODE FROM APPROVAL WHERE APPROVAL_SENT = 'N')
UNION
SELECT  GOBEACC_PIDM,  -- Requisition Accounting (Fund/Orgn) Detail -- Report_pidm,
         FOBAINP_ACTIVITY_DATE||FPBREQH_CODE||'9'||FPRREQA_SEQ_NUM, --Sort_Order,  -- Approver_UserID||Requestor
         ' '||CHR(09)||
         'Accounting Fund-Orgn'||FPRREQA_SEQ_NUM||' '|| FPBREQH_CODE||' Charged to: '||CHR(09)||'Fund '||FPRREQA_FUND_CODE||' '||FTVFUND_TITLE||CHR(09)||'Orgn '||FPRREQA_ORGN_CODE||' '||FTVORGN_TITLE||CHR(09)||  
'Amount:'||' \'||LTRIM(TO_CHAR(FPRREQA_AMT_plus_TAX_AMT,'$999,999,999.99')) --Detail_String
FROM
(SELECT  
-- Created from TABLE
-- FOBAINP: Approvals in Process Table
-- FOBUAPP: Unapproved Document Table 
-- FORAQUS: Approval Queue Definition Table
  FOBAINP_DOC_NUM   FOBAINP_DOC_NUM,  -- Document or Requisition Number
  FOBAINP_DOC_TYPE  FOBAINP_DOC_TYPE,
  TRUNC(FOBAINP_ACTIVITY_DATE)         FOBAINP_ACTIVITY_DATE,
  FOBAINP_USER_ID,
  FOBAINP_QUEUE_ID  FOBAINP_QUEUE_ID, -- Approval_Queue
  FOBAINP_LEVEL     FOBAINP_LEVEL,    -- Approver_Level
  FOBAINP_CHG_SEQ_NUM       FOBAINP_CHG_SEQ_NUM,
  FOBAINP_SUBMISSION_NUM    FOBAINP_SUBMISSION_NUM,
  FOBAINP_APPROVAL_SEQUENCE FOBAINP_APPROVAL_SEQUENCE,
  FORAQUS_USER_ID_APPR      FORAQUS_USER_ID_APPR
   FROM FOBAINP, FOBUAPP, FORAQUS
WHERE fobainp_doc_num LIKE 'R%'
  AND fobainp_doc_num = fobuapp_doc_code
  AND fobainp_doc_type = fobuapp_seq_num
  AND ((fobainp_chg_seq_num = fobuapp_chg_seq_num) OR (fobainp_chg_seq_num IS NULL AND fobuapp_chg_seq_num IS NULL))
  AND ((fobainp_submission_num = fobuapp_submission_number) OR (fobainp_submission_num IS NULL AND fobuapp_submission_number IS NULL))
  AND fobainp_approval_sequence = 
(
SELECT MIN(x.fobainp_approval_sequence)
  FROM fobainp x
 WHERE x.fobainp_doc_num = fobuapp_doc_code
   AND x.fobainp_doc_num LIKE 'R%'
   AND x.fobainp_doc_type = fobuapp_seq_num 
   AND ((x.fobainp_chg_seq_num = fobuapp_chg_seq_num) OR (x.fobainp_chg_seq_num IS NULL AND fobuapp_chg_seq_num IS NULL)) 
   AND ((x.fobainp_submission_num = fobuapp_submission_number) OR (x.fobainp_submission_num IS NULL AND fobuapp_submission_number IS NULL))
)
   AND TRUNC(FORAQUS_EFF_DATE) <= SYSDATE
   AND      (FORAQUS_NCHG_DATE  > TRUNC(SYSDATE) OR FORAQUS_NCHG_DATE IS NULL)
   AND      (FORAQUS_TERM_DATE  > TRUNC(SYSDATE) OR FORAQUS_TERM_DATE IS NULL)
   AND foraqus_queue_id    = fobainp_queue_id
   AND foraqus_queue_level = fobainp_level
),
(SELECT  DISTINCT foraqus_user_id_appr TODAYS_user_id_appr
   FROM FOBAINP, FOBUAPP, FORAQUS
WHERE fobainp_doc_num LIKE 'R%'
--AND TRUNC(FOBUAPP_ACTIVITY_DATE) = TRUNC(SYSDATE-1) -- Yesterday's
  AND TRUNC(FOBUAPP_ACTIVITY_DATE) = TRUNC(SYSDATE)   -- Today's
  AND fobainp_doc_num = fobuapp_doc_code
  AND fobainp_doc_type = fobuapp_seq_num
  AND ((fobainp_chg_seq_num = fobuapp_chg_seq_num) OR (fobainp_chg_seq_num IS NULL AND fobuapp_chg_seq_num IS NULL))
  AND ((fobainp_submission_num = fobuapp_submission_number) OR (fobainp_submission_num IS NULL AND fobuapp_submission_number IS NULL))
  AND fobainp_approval_sequence = 
(
SELECT MIN(x.fobainp_approval_sequence)
  FROM fobainp x
 WHERE x.fobainp_doc_num = fobuapp_doc_code
   AND x.fobainp_doc_num LIKE 'R%'
-- AND TRUNC(FOBUAPP_ACTIVITY_DATE) = TRUNC(SYSDATE-1) -- Yesterday's
   AND TRUNC(FOBUAPP_ACTIVITY_DATE) = TRUNC(SYSDATE)   -- Today's
   AND x.fobainp_doc_type = fobuapp_seq_num 
   AND ((x.fobainp_chg_seq_num = fobuapp_chg_seq_num) OR (x.fobainp_chg_seq_num IS NULL AND fobuapp_chg_seq_num IS NULL)) 
   AND ((x.fobainp_submission_num = fobuapp_submission_number) OR (x.fobainp_submission_num IS NULL AND fobuapp_submission_number IS NULL))
)
   AND TRUNC(FORAQUS_EFF_DATE) <= SYSDATE
   AND      (FORAQUS_NCHG_DATE  > TRUNC(SYSDATE) OR FORAQUS_NCHG_DATE IS NULL)
   AND      (FORAQUS_TERM_DATE  > TRUNC(SYSDATE) OR FORAQUS_TERM_DATE IS NULL)
   AND foraqus_queue_id    = fobainp_queue_id
   AND foraqus_queue_level = fobainp_level
),
GOBEACC,FPRREQD,
(
SELECT
  FPRREQA_REQH_CODE  FPRREQA_REQH_CODE,
  FPRREQA_SEQ_NUM    FPRREQA_SEQ_NUM,
  FPRREQA_USER_ID    FPRREQA_USER_ID,
  SUM(FPRREQA_AMT+FPRREQA_TAX_AMT) FPRREQA_AMT_plus_TAX_AMT,
  FPRREQA_FUND_CODE FPRREQA_FUND_CODE,
  FTVFUND_TITLE     FTVFUND_TITLE,
  FPRREQA_ORGN_CODE FPRREQA_ORGN_CODE,
  FTVORGN_TITLE     FTVORGN_TITLE
  FROM FPRREQA, FTVFSPD,
(SELECT
  FTVFUND_FUND_CODE FTVFUND_FUND_CODE,
  FTVFUND_TITLE     FTVFUND_TITLE
  FROM FTVFUND
 WHERE
       TRUNC(FTVFUND_EFF_DATE) <= SYSDATE
   AND      (FTVFUND_NCHG_DATE  > TRUNC(SYSDATE) OR FTVFUND_NCHG_DATE IS NULL)
   AND      (FTVFUND_TERM_DATE  > TRUNC(SYSDATE) OR FTVFUND_TERM_DATE IS NULL)
),
(SELECT
  FTVORGN_ORGN_CODE FTVORGN_ORGN_CODE,
  FTVORGN_TITLE     FTVORGN_TITLE
  FROM FTVORGN
 WHERE
 TRUNC(FTVORGN_EFF_DATE) <= SYSDATE
   AND  (FTVORGN_NCHG_DATE  > TRUNC(SYSDATE) OR FTVORGN_NCHG_DATE IS NULL)
   AND  (FTVORGN_TERM_DATE  > TRUNC(SYSDATE) OR FTVORGN_TERM_DATE IS NULL)
   AND   FTVORGN_STATUS_IND = 'A'
)
 WHERE FPRREQA_COAS_CODE = FTVFSPD_COAS_CODE
   AND FPRREQA_PERIOD    = FTVFSPD_FSPD_CODE
   AND FPRREQA_FSYR_CODE = FTVFSPD_FSYR_CODE
-- AND FTVFSPD_PRD_STATUS_IND = 'O'
   AND FPRREQA_REQH_CODE LIKE 'R%'
   AND FPRREQA_FUND_CODE = FTVFUND_FUND_CODE
   AND FPRREQA_ORGN_CODE = FTVORGN_ORGN_CODE
GROUP BY
  FPRREQA_REQH_CODE,
  FPRREQA_SEQ_NUM,    
  FPRREQA_USER_ID,
  FPRREQA_FUND_CODE, FTVFUND_TITLE,
  FPRREQA_ORGN_CODE, FTVORGN_TITLE
),
(SELECT   -- Created from TABLE FPBREQH  Request Header Table
  FPBREQH_CODE                         FPBREQH_CODE, -- Document or Requisition
  TRUNC(FPBREQH_ACTIVITY_DATE)         FPBREQH_ACTIVITY_DATE,
  TRUNC(FPBREQH_REQH_DATE)             FPBREQH_REQH_DATE,
  TRUNC(FPBREQH_TRANS_DATE)            FPBREQH_TRANS_DATE,
  FPBREQH_NAME                         FPBREQH_NAME,  -- Requestor
  TRUNC(FPBREQH_REQD_DATE)             FPBREQH_REQD_DATE,
  TRUNC(FPBREQH_CANCEL_DATE)           FPBREQH_CANCEL_DATE,
  TRUNC(FPBREQH_POST_DATE)             FPBREQH_POST_DATE,
  FPBREQH_ATTENTION_TO                 FPBREQH_ATTENTION_TO,
  TRUNC(FPBREQH_REQ_PRINT_DATE)        FPBREQH_REQ_PRINT_DATE,
  TRUNC(FPBREQH_CLOSED_DATE)           FPBREQH_CLOSED_DATE
  FROM  FPBREQH
),
(SELECT SPRIDEN_PIDM VENDOR_PIDM, DECODE(SPRIDEN_ENTITY_IND, 'C',
       SPRIDEN_LAST_NAME, 'P', DECODE(SPRIDEN_MI, '', (
       SPRIDEN_LAST_NAME || ', ' || SPRIDEN_FIRST_NAME), (
       SPRIDEN_LAST_NAME || ', ' || SPRIDEN_FIRST_NAME ||
       ' ' || SUBSTR(SPRIDEN_MI, 1, 1) || '.'))) VENDOR_NAME
  FROM SPRIDEN
 WHERE SPRIDEN_CHANGE_IND IS NULL
   AND SPRIDEN_PIDM IN 
(SELECT FPRREQD_VEND_PIDM FROM FPRREQD WHERE FPRREQD_REQH_CODE LIKE 'R%')
)
WHERE GOBEACC_USERNAME    = FORAQUS_USER_ID_APPR
  AND FOBAINP_DOC_NUM     = FPBREQH_CODE
  AND FPRREQD_REQH_CODE   = FPBREQH_CODE
  AND TODAYS_user_id_appr = FORAQUS_USER_ID_APPR
  AND FPRREQD_VEND_PIDM   = VENDOR_PIDM (+)
  AND FOBAINP_DOC_NUM     = FPRREQA_REQH_CODE
  AND FOBAINP_DOC_NUM IN (SELECT APPROVAL_DOC_CODE FROM APPROVAL WHERE APPROVAL_SENT = 'N')
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 (note that this is not the same as removing duplicate newlines)
!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
-- this send script (contains mail commands) is run by approval_gather.shl

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

CLEAR COLUMNS
CLEAR BREAK

SET HEADING ON

!echo "approval_periodic Report Complete"

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

-- approval_periodic.sql end