-- approval_listall.sql    Audit Listing for APPROVAL and FOBUAPP Tables
--**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 PAGESIZE 90
SET LINESIZE 128

!echo
!echo begin Unapproved List

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


COLUMN FOBUAPP_ORIG_USER FORMAT A10 TRUNC
COLUMN SNUM FORMAT 99
COLUMN SEQN FORMAT 99

SET FEEDBACK ON

-- list All FOBUAPP Records
!echo All FOBUAPP Records
SELECT
FOBUAPP_SEQ_NUM,
--FOBUAPP_USER_CODE,
FOBUAPP_DOC_CODE DOCU,
--FOBUAPP_DOC_AMT AMT,
--FOBUAPP_ORIG_USER,
FOBUAPP_ACTIVITY_DATE,
FOBUAPP_CHG_SEQ_NUM SEQN,
FOBUAPP_SUBMISSION_NUMBER SNUM,
FOBUAPP_STATUS_IND I
  FROM FOBUAPP
 WHERE FOBUAPP_DOC_CODE LIKE 'R%'
 ORDER BY FOBUAPP_DOC_CODE;


COLUMN FORAQUS_USER_ID_APPR HEADING "Approver" FORMAT A10 TRUNC

-- List Today's FOBUAPP Records
!echo 
!echo begin test logic using FOBUAPP_ACTIVITY_DATE
!date
SELECT distinct 
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,
  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 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,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 (+)
;
!echo end test logic using FOBUAPP_ACTIVITY_DATE
!date


!echo List All APPROVAL Records
-- List APPROVAL Table Records
SELECT APPROVAL_TYPE T,
       APPROVAL_SEQ_NUM SNUM,
       APPROVAL_DOC_CODE DOC_CODE,
       APPROVAL_ACTIVITY_DATE ACT_DATE,
       APPROVAL_CHG_SEQ_NUM SEQNUM,
       APPROVAL_SUBMISSION_NUMBER SUBNUM,
       APPROVAL_STATUS_IND I,
       APPROVAL_SENT S
  FROM APPROVAL
ORDER BY APPROVAL_DOC_CODE;

SET ECHO ON

-- approval_listall.sql end