-- approval_gather.sql          Gathers Approval Records from FOBUAPP, 
--                              Controls when approval_periodic.sql is run
--                              Note approval_periodic.sql only extracts today's Unapproved Requisitions
--**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:) 
-- Change Log
-- 02/18/2003 bknox Created

SET ECHO ON
SET FEEDBACK ON
SET TERMOUT ON
INSERT INTO APPROVAL
          ( APPROVAL_TYPE,
            APPROVAL_SEQ_NUM,
            APPROVAL_DOC_CODE,
            APPROVAL_ACTIVITY_DATE,
            APPROVAL_CHG_SEQ_NUM,
            APPROVAL_SUBMISSION_NUMBER,
            APPROVAL_STATUS_IND,
            APPROVAL_SENT,
            APPROVAL_QUEUE_ID,
            APPROVAL_LEVEL,
            APPROVAL_APPROVAL_SEQUENCE,
            APPROVAL_USER_ID_APPR
           )
     SELECT 'R',
            FOBUAPP_SEQ_NUM,
            FOBUAPP_DOC_CODE,
            FOBUAPP_ACTIVITY_DATE,
            FOBUAPP_CHG_SEQ_NUM,
            FOBUAPP_SUBMISSION_NUMBER,
            FOBUAPP_STATUS_IND,
            'N',
            FOBAINP_QUEUE_ID,
            FOBAINP_LEVEL,
            FOBAINP_APPROVAL_SEQUENCE,
            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 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
)
WHERE  FOBUAPP_STATUS_IND IS NULL
  AND FOBUAPP_DOC_CODE||FORAQUS_USER_ID_APPR NOT IN
(SELECT APPROVAL_DOC_CODE||APPROVAL_USER_ID_APPR FROM APPROVAL);


--  Report All WHERE approval_sent = 'N' To Be Sent.  This will go to the log file.
SELECT APPROVAL_DOC_CODE DOC_CODE
  FROM approval
 WHERE approval_sent = 'N';


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

-- Determine if there are any approvals to be mailed this run.  
-- This avoids running the extract program if nothing to extract for this Gather.

!rm -f approval_periodic_mail.sql
SPOOL approval_periodic_mail.sql
SELECT 'START approval_periodic.sql'
  FROM approval
 WHERE 0 < (SELECT count(APPROVAL_DOC_CODE)
  FROM approval
 WHERE approval_sent = 'N')
AND ROWNUM <2;
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_periodic_mail.sql

-- approval_gather.sql end