-- approval_queue_errors.sql	Generate 'Requisitions Pending Approval with Errors' 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
-- 02/26/03 bknox created from approval_queue_pending.sql

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 Sort_Order NOPRINT

COLUMN  FOBAINP_QUEUE_ID       HEADING "Queue" A5
COLUMN  FOBAINP_LEVEL          HEADING "Level" 99999
COLUMN  FOBAINP_APPROVAL_SEQUENCE  HEADING "Seq" 999

COLUMN Error_Count NEW_VALUE Error_Count NOPRINT FORMAT 999999
SELECT 0 Error_Count FROM DUAL;

SET NUMWIDTH 10

!rm -f approval_queue_errors.lst

!echo "approval_queue_errors report beginning"

SPOOL approval_queue_errors

-- mail SELECT
SELECT 
       'mail -s '||''''||'Requisitions Pending with Errors'||' '||''''||' '||'-c "bknox@uaex.edu"'||' '||'bpowell@uaex.edu'||' << eof'
||CHR(09)||'Approvals Pending for Approver with Errors - Unapproved But Out of Period'
||' '||CHR(09)||' '||CHR(09) ||'           FOBAINP       FOBUAPP       FORAQUS'
||' '||CHR(09)||'           ACTIVITY      ACTIVITY      USER_ID'
||' '||CHR(09)||'Document   DATE          DATE          APPR            Queue Level Seq'
||CHR(09)||' '||CHR(09)
  FROM DUAL;


SET FEEDBACK ON
--echo begin test logic using APPROVAL_Unapproved_But_Out_of_Period

SELECT 1 + &&Error_Count Error_Count,
--     FOBUAPP_SEQ_NUM,
       FOBUAPP_DOC_CODE||'   '||
       FOBAINP_ACTIVITY_DATE||'   '||
       FOBUAPP_ACTIVITY_DATE||'   '||
       RPAD(FORAQUS_USER_ID_APPR,13)||'   '||
--     FOBUAPP_CHG_SEQ_NUM,
--     FOBUAPP_SUBMISSION_NUMBER,
--     FOBUAPP_STATUS_IND,
       FOBAINP_QUEUE_ID||'    '||
       FOBAINP_LEVEL||'    '||
       FOBAINP_APPROVAL_SEQUENCE
--     FOBAINP_DOC_NUM DOCU,
--     FOBAINP_DOC_TYPE,FOBAINP_ACTIVITY_DATE,FOBAINP_USER_ID,
--     FOBAINP_QUEUE_ID Q,
--     FOBAINP_LEVEL    LVL,
--     FOBAINP_CHG_SEQ_NUM SEQN,
--     FOBAINP_APPROVAL_SEQUENCE ASEQ,
--     FOBAINP_SUBMISSION_NUM SNUM,
--     FOBAINP_ACTIVITY_DATE AINP_date,fobuapp_ACTIVITY_DATE uapp_date,
--     FORAQUS_USER_ID_APPR
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,
  FOBUAPP_DOC_CODE  FOBUAPP_DOC_CODE,
  FOBUAPP_SEQ_NUM   FOBUAPP_SEQ_NUM,
  FOBUAPP_CHG_SEQ_NUM FOBUAPP_CHG_SEQ_NUM,
  FOBUAPP_SUBMISSION_NUMBER FOBUAPP_SUBMISSION_NUMBER,
  FOBUAPP_STATUS_IND FOBUAPP_STATUS_IND,
  FOBAINP_ACTIVITY_DATE         FOBAINP_ACTIVITY_DATE,
  fobuapp_ACTIVITY_DATE fobuapp_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 FOBUAPP_STATUS_IND IS NULL
  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)   -- Today's
--AND TRUNC(FOBAINP_ACTIVITY_DATE) = TRUNC(SYSDATE-1) -- Yesterday'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)     -- Today's
-- AND TRUNC(x.FOBAINP_ACTIVITY_DATE) = TRUNC(SYSDATE-1) -- Yesterday'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,(SELECT
FPRREQD_REQH_CODE FPRREQD_REQH_CODE,
FPRREQD_VEND_PIDM FPRREQD_VEND_PIDM
FROM FPRREQD
WHERE FPRREQD_REQH_CODE LIKE 'R%'
GROUP BY FPRREQD_REQH_CODE,
FPRREQD_VEND_PIDM),
(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%'
GROUP BY FPRREQD_VEND_PIDM)
)
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 SUBSTR(FOBAINP_ACTIVITY_DATE,4,3)||SUBSTR(FOBAINP_ACTIVITY_DATE,8,4) NOT IN
(SELECT
SUBSTR(FTVFSPD_PRD_END_DATE,4,3)||SUBSTR(FTVFSPD_PRD_END_DATE,8,4) 
FROM FTVFSPD
WHERE FTVFSPD_PRD_STATUS_IND = 'O')
order by FOBUAPP_DOC_CODE;

--echo   end test logic using APPROVAL_Unapproved_But_Out_of_Period
SET FEEDBACK OFF

--echo begin test logic using APPROVAL_Unapproved_But_Not_Gathered

-- mail SELECT
SELECT CHR(09)||' '||CHR(09)||' '||CHR(09)||' '
||CHR(09)||'Approvals Pending for Approver with Errors - Unapproved But Will Not be Sent to Approver'
||' '||CHR(09)||' '||CHR(09) ||'           FOBAINP       FOBUAPP       FORAQUS'
||' '||CHR(09)||'           ACTIVITY      ACTIVITY      USER_ID'
||' '||CHR(09)||'Document   DATE          DATE          APPR           Queue Level Seq'
||CHR(09)||' '||CHR(09)
  FROM DUAL;


SET FEEDBACK ON

SELECT  --                Approval_Gather2 Records NOT IN Approval_Periodic2
    1 + &&Error_Count Error_Count,
-- Created from TABLE
-- FOBAINP: Approvals in Process Table
-- FOBUAPP: Unapproved Document Table 
-- FORAQUS: Approval Queue Definition Table
    FOBAINP_DOC_NUM||'   '||  -- Document or Requisition Number
--  FOBAINP_DOC_TYPE  FOBAINP_DOC_TYPE,
--  FOBUAPP_DOC_CODE  FOBUAPP_DOC_CODE,
--  FOBUAPP_SEQ_NUM   FOBUAPP_SEQ_NUM,
--  FOBUAPP_CHG_SEQ_NUM FOBUAPP_CHG_SEQ_NUM,
--  FOBUAPP_SUBMISSION_NUMBER FOBUAPP_SUBMISSION_NUMBER,
--  FOBUAPP_STATUS_IND FOBUAPP_STATUS_IND,
    FOBAINP_ACTIVITY_DATE||'   '||
    fobuapp_ACTIVITY_DATE||'   '||
    RPAD(FORAQUS_USER_ID_APPR,13)||'  '||
--  FOBAINP_USER_ID,
    FOBAINP_QUEUE_ID||'    '|| -- Approval_Queue
    FOBAINP_LEVEL||'    '||    -- Approver_Level
--  FOBAINP_CHG_SEQ_NUM       FOBAINP_CHG_SEQ_NUM,
--  FOBAINP_SUBMISSION_NUM    FOBAINP_SUBMISSION_NUM,
    FOBAINP_APPROVAL_SEQUENCE
   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
   AND FOBUAPP_STATUS_IND IS NULL
AND FOBAINP_DOC_NUM NOT IN (
-- Code from approval_periodic2.sql
SELECT
-- GOBEACC_PIDM               Report_pidm,
   FOBAINP_DOC_NUM -- DOCU,
-- FOBAINP_QUEUE_ID Q,
-- FOBAINP_LEVEL    LVL,
-- FOBAINP_CHG_SEQ_NUM SEQN,
-- FOBAINP_APPROVAL_SEQUENCE ASEQ,
-- FOBAINP_SUBMISSION_NUM SNUM,
-- FOBAINP_ACTIVITY_DATE AINP_date,fobuapp_ACTIVITY_DATE uapp_date,
-- FORAQUS_USER_ID_APPR,
-- FPBREQH_CODE,
-- FPBREQH_NAME,
-- FPBREQH_ATTENTION_TO,
-- NVL(VENDOR_NAME,NULL)||FPRREQD_RECOMM_VEND_NAME
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,
  fobuapp_ACTIVITY_DATE fobuapp_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 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
),
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')
);

--echo end test logic using APPROVAL_Unapproved_But_Not_Gathered
SET FEEDBACK OFF


SELECT CHR(09)||' '||CHR(09)||'The first date is the Requisition Creation Date (or perhaps Modification Date).',
 CHR(09)||'The second date indicates pending the current Approver.'||CHR(09)||' '||CHR(09)
FROM DUAL;
 
-- The following just terminates the Last email message text with eof
SELECT ' '||CHR(09)||'eof'||CHR(09) FROM DUAL;

SPOOL OFF

-- correct record formats

!rm -f approval_queue_e_inter.txt

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

!rm -f approval_queue_errors.lst
!rm -f approval_queue_errors.txt

-- Remove Blank Lines
!egrep -v '                                                                                                                                                            ' approval_queue_e_inter.txt > approval_queue_errors.txt

!rm -f approval_queue_e_inter.txt

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

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

-- The following SPOOLed file and the START following work as an IF Statement
-- to determine if the email is to be sent.  You cannot start a file that is not found:)

!rm -f approval_errors_mail.sql
SPOOL approval_errors_mail.sql
SELECT '!approval_queue_errors.shl'
  FROM DUAL
 WHERE 0 < &&Error_Count;
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 approval_errors_mail.sql

CLEAR COLUMNS
CLEAR BREAK

SET HEADING ON

!echo "approval_queue_errors report complete"

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

-- approval_queue_errors.sql end