-- leave_requests_gather.sql          Gathers Leave Request Records from PERJOBS/PERHOUR runs leave_requests.sql when needed

--**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:) 

!date
SET ECHO ON
SET FEEDBACK ON
SET TERMOUT ON

INSERT INTO LEAVE
          ( LEAVE_SEQNO,
            LEAVE_TIME_ENTRY_DATE,
            LEAVE_ACTIVITY_DATE,
            LEAVE_SENT
           )
     SELECT PERJOBS_SEQNO,
            TRUNC(PERHOUR_TIME_ENTRY_DATE),
            TRUNC(PERHOUR_ACTIVITY_DATE),
            'N'
       FROM 
(SELECT   -- Created from TABLE PERHOUR  Time Entry Hours Breakdown Table
  PERHOUR_JOBS_SEQNO              PERHOUR_JOBS_SEQNO,
  TRUNC(PERHOUR_TIME_ENTRY_DATE)  PERHOUR_TIME_ENTRY_DATE,
  TRUNC(PERHOUR_ACTIVITY_DATE)    PERHOUR_ACTIVITY_DATE
  FROM PERHOUR
 WHERE PERHOUR_HRS != 0
--  AND PERHOUR_TIME_ENTRY_DATE > LAST_DAY(LAST_DAY(SYSDATE)-32)
),
(SELECT   -- Created from TABLE PERJOBS  Time Entry Jobs Table
  PERJOBS_SEQNO                    PERJOBS_SEQNO,
  TRUNC(PERJOBS_ACTIVITY_DATE)     PERJOBS_ACTIVITY_DATE
  FROM PERJOBS
 WHERE PERJOBS_ACTION_IND = 'R'
   AND PERJOBS_STATUS_IND = 'P'
   AND PERJOBS_YEAR = TO_CHAR(SYSDATE,'YYYY')
),
(SELECT   -- Created from TABLE PERROUT  Time Entry Routing Queue Table
  PERROUT_JOBS_SEQNO              PERROUT_JOBS_SEQNO,
  TRUNC(PERROUT_ACTIVITY_DATE)    PERROUT_ACTIVITY_DATE
  FROM PERROUT
)
WHERE PERHOUR_JOBS_SEQNO = PERJOBS_SEQNO
  AND PERHOUR_JOBS_SEQNO = PERROUT_JOBS_SEQNO
  AND PERHOUR_JOBS_SEQNO = PERHOUR_JOBS_SEQNO
GROUP BY  PERHOUR_ACTIVITY_DATE,PERJOBS_SEQNO,TRUNC(PERHOUR_TIME_ENTRY_DATE)
HAVING TO_CHAR(PERJOBS_SEQNO)||TO_CHAR(TRUNC(PERHOUR_TIME_ENTRY_DATE),'MM/DD/YYYY')||TO_CHAR(TRUNC(PERHOUR_ACTIVITY_DATE),'MM/DD/YYYY') NOT IN
(SELECT TO_CHAR(LEAVE_SEQNO)||TO_CHAR(TRUNC(LEAVE_TIME_ENTRY_DATE),'MM/DD/YYYY')||TO_CHAR(TRUNC(LEAVE_ACTIVITY_DATE),'MM/DD/YYYY') FROM LEAVE);

-- List All WHERE leave_sent = 'N' To Be Sent
SELECT LEAVE_SEQNO,
            LEAVE_TIME_ENTRY_DATE,
            LEAVE_ACTIVITY_DATE
  FROM leave
 WHERE leave_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

!rm -f leave_requests_mail.sql
SPOOL leave_requests_mail.sql
SELECT 'START leave_requests.sql'
  FROM leave
 WHERE 0 < (SELECT count(LEAVE_SEQNO)
  FROM leave
 WHERE leave_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 leave_requests_mail.sql

!date