-- leave_requests.sql	generate 'Leave Requests Notices (Awaiting Your Approval)' 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 TERMOUT OFF
@/home/common/gysbsec.sql approval_queue
SET TERMOUT ON

SET SHOWMODE OFF
SET VERIFY OFF

-- change log
-- 07/07/03 bknox created
-- 07/21/03 bknox made app live

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

-- use current date to get asofdate
COLUMN asofdate NOPRINT NEW_VALUE asofdate
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY') asofdate FROM DUAL;

-- Time and Date

COLUMN today  NOPRINT NEW_VALUE datevar FORMAT A18 TRUNC
COLUMN time   NOPRINT NEW_VALUE timevar FORMAT A11 TRUNC
SELECT TO_CHAR(SYSDATE,'fmMonth DD, yyyy') today,
       TO_CHAR(SYSDATE,'HH:MI:SS am') time FROM DUAL;

COLUMN asofdatealpha NOPRINT NEW_VALUE asofdatealphavar FORMAT A18 TRUNC
SELECT TO_CHAR(TO_DATE('&&asofdate', 'MM/DD/YYYY'), 'fmMonth DD, yyyy') asofdatealpha FROM DUAL;

COLUMN coa    NOPRINT NEW_VALUE coavar
SELECT 'Z' coa FROM DUAL;

COLUMN Address_String NEW_VALUE Address_String FORMAT A512
COLUMN Detail_String  NEW_VALUE Detail_String  FORMAT A512
COLUMN Detail_String1 NEW_VALUE Detail_String1 FORMAT A512
COLUMN Ending_String1 NEW_VALUE Ending_String1 
COLUMN Ending_String2 NEW_VALUE Ending_String2 
COLUMN Ending_String  NEW_VALUE Ending_String 

COLUMN Sort_Order NOPRINT
--COLUMN Sort_Date  NOPRINT

SET NUMWIDTH 10

BREAK ON Address_String NODUPLICATES ON Detail_String1 NODUPLICATES

--     Subject is the Subject Section of the email header
DEFINE Subject = 'Leave Requests for Your Approval'

--     Title is the Report Title for the email message "report"
DEFINE Title_String = 'The following leave requests are awaiting your approval:'
--     Heading is the Report Column Headings for the email message "report"
-- DEFINE Heading_String = 'Requisition # Requested by'

-- Ending String for Leave Requests is actually four lines: String1, blank line, String2, String(3).
DEFINE Ending_String1 = 'Please approve via login to Banner through either of the following:'
DEFINE Ending_String2 = 'Self Service http://ban.uaex.edu/ then click on Employee Services and Time Sheet '
DEFINE Ending_String = 'WebForms at http://webforms.uaex.edu/ using form PHADSUM'

!rm -f leave_requests.lst

!echo "leave_requests report beginning"
!echo "'leave_requests Notices' email reports" &&datevar &&timevar

SPOOL leave_requests

-- 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_String1'||CHR(09)||' '||CHR(09)||'&&Ending_String2'||CHR(09)||'&&Ending_String'||CHR(09)||'eof'||CHR(09)||
     'mail -s '||''''||'&&Subject'||' '||''''||' '||'-c "kbailey@uaex.edu bknox@uaex.edu"'||' '||Email_Address||' << eof'||CHR(09)||'&&Title_String'||CHR(09) Address_String, 
--       'mail -s '||''''||'Test '||'&&Subject'||' '||''''||' '||'-c "kbailey@uaex.edu"'||' '||'bknox@uaex.edu'||' << eof'||CHR(09)||Email_Address||' '||'&&Title_String'||CHR(09) Address_String, 
        Detail_String1, CHR(09), Detail_String, CHR(09) 
  FROM (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'),
-- Replace the following Sub-Select with your code, Report_pidm and Detail_String are Required
(
-- Requisition Line Item
SELECT
 perrout_appr_pidm  Report_pidm,
 perrout_appr_pidm||PERJOBS_PIDM||TO_CHAR(TRUNC(PERHOUR_TIME_ENTRY_DATE),'MM/DD/YYYY') Sort_Order,
Name||' has requested ' Detail_String1, '   '||PERHOUR_HRS||' hours of '||PTREARN_LONG_DESC||' on '||TO_CHAR(TRUNC(PERHOUR_TIME_ENTRY_DATE),'MM/DD/YYYY') Detail_String
FROM
(SELECT   -- Created from TABLE PERHOUR  Time Entry Hours Breakdown Table
  PERHOUR_JOBS_SEQNO PERHOUR_JOBS_SEQNO,
  PERHOUR_EARN_CODE  PERHOUR_EARN_CODE,
  TRUNC(PERHOUR_TIME_ENTRY_DATE)       PERHOUR_TIME_ENTRY_DATE,
  PERHOUR_HRS PERHOUR_HRS,
  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,
  PERJOBS_YEAR  PERJOBS_YEAR,
  PERJOBS_PIDM PERJOBS_PIDM,
  PERJOBS_STATUS_IND PERJOBS_STATUS_IND,
  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,
  PERROUT_APPR_PIDM PERROUT_APPR_PIDM,
  TRUNC(PERROUT_ACTIVITY_DATE)         PERROUT_ACTIVITY_DATE
  FROM PERROUT
),
(SELECT   -- Created from TABLE PTREARN  Earnings Code Rule Table
  PTREARN_CODE PTREARN_CODE,
  PTREARN_LONG_DESC PTREARN_LONG_DESC
  FROM PTREARN
),
(SELECT
SPRIDEN_PIDM  SPRIDEN_PIDM, 
SPRIDEN_ID      ID,
SPBPERS_SSN   SSN,
SUBSTR(DECODE(SPBPERS_NAME_PREFIX,'Mr',NULL,'Mr.',NULL,'Miss',NULL,'Mrs',NULL,'Mrs.',NULL,'Ms',NULL,'Ms.',NULL,NULL,NULL, SPBPERS_NAME_PREFIX||' ')||RTRIM(DECODE(NVL(SPBPERS_PREF_FIRST_NAME,'X'),'X',spriden_first_name,spbpers_pref_first_name))||' '||RTRIM(spriden_last_name),1,25) Name,
TO_CHAR(SPRIDEN_PIDM)  PIDM_CHAR
  FROM SPRIDEN, SPBPERS
 WHERE SPRIDEN_PIDM = SPBPERS_PIDM (+)
   AND SPRIDEN_CHANGE_IND IS NULL
   AND SPRIDEN_ENTITY_IND = 'P'
   AND (SPRIDEN_PIDM <  623 OR SPRIDEN_PIDM > 623)    -- Catastrophic Leave, Bank 
  AND spriden_ID <> '000000000' 
),
(
SELECT LEAVE_SEQNO LEAVE_SEQNO
  FROM leave
 WHERE leave_sent = 'N'
)
WHERE PERJOBS_PIDM       = SPRIDEN_PIDM
  AND PERHOUR_EARN_CODE  = PTREARN_CODE
  AND PERHOUR_JOBS_SEQNO = PERJOBS_SEQNO
  AND PERHOUR_JOBS_SEQNO = PERROUT_JOBS_SEQNO
  AND PERHOUR_JOBS_SEQNO = PERHOUR_JOBS_SEQNO
  AND PERHOUR_JOBS_SEQNO = LEAVE_SEQNO
GROUP BY   perrout_appr_pidm,PERJOBS_PIDM,PERJOBS_SEQNO,
Name, PERJOBS_YEAR,
 PTREARN_CODE,
 PTREARN_LONG_DESC, TRUNC(PERHOUR_TIME_ENTRY_DATE), PERHOUR_HRS 
ORDER BY 1,2
)
-- Replace the above Sub-Select with your code, Report_pidm and Detail_String are Required
WHERE Report_pidm = email_pidm
;

-- The following just terminates the Last email message text with eof
SELECT ' '||CHR(09)||'&&Ending_String1'||CHR(09)||' '||CHR(09)||'&&Ending_String2'||CHR(09)||'&&Ending_String'||CHR(09)||'eof'||CHR(09) FROM DUAL;

SPOOL OFF

-- correct record formats

!rm -f leave_requests_inter.txt

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

!rm -f leave_requests.lst
!rm -f leave_requests.txt

-- Remove Blank Lines
!egrep -v '                                                                                                                                                            ' leave_requests_inter.txt > leave_requests.txt

!rm -f leave_requests_inter.txt

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

-- Flag All That Where Just Sent 
UPDATE leave
   SET leave_sent = 'Y'
 WHERE leave_sent = 'N';

-- leave_req_clear_approved.sql   Clear Records from Leave Table No Longer In Process and Unapproved

SET FEEDBACK ON

DELETE
--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
 WHERE TO_CHAR(LEAVE_SEQNO)||TO_CHAR(TRUNC(LEAVE_TIME_ENTRY_DATE),'MM/DD/YYYY')||TO_CHAR(TRUNC(LEAVE_ACTIVITY_DATE),'MM/DD/YYYY') NOT IN
(
SELECT TO_CHAR(PERJOBS_SEQNO)||TO_CHAR(TRUNC(PERHOUR_TIME_ENTRY_DATE),'MM/DD/YYYY')||TO_CHAR(TRUNC(PERHOUR_ACTIVITY_DATE),'MM/DD/YYYY')
       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,
  PERHOUR_HRS                     PERHOUR_HRS,
  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,
  PERJOBS_YEAR                     PERJOBS_YEAR,
  PERJOBS_PIDM                     PERJOBS_PIDM,
  PERJOBS_STATUS_IND               PERJOBS_STATUS_IND,
  TRUNC(PERJOBS_COMMENT_DATE_TIME) PERJOBS_COMMENT_DATE_TIME,
  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,
  PERROUT_APPR_PIDM               PERROUT_APPR_PIDM,
  TRUNC(PERROUT_STATUS_DATE_TIME) PERROUT_STATUS_DATE_TIME,
  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,PERJOBS_YEAR,
TRUNC(PERHOUR_TIME_ENTRY_DATE)
);

CLEAR COLUMNS
CLEAR BREAK

SET HEADING ON

!echo "leave_requests report complete"

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

SET TERMOUT OFF
@/home/common/gysbsec_reset.sql
SET TERMOUT ON