-- ******************************************************** -- ** PIPER_RX - Full Service Activity chart -- ** -- ** By Gary Piper -- ** -- ** (C) Copyright G.Piper 2004 -- ** All rights reserved -- ** -- ** File Name: PIPER_RX_FSUA.sql -- ** -- ** ----------------------------------------------------- -- ** Version 1.0 G.Piper Apr-04 Initial Creation -- ** -- ********************************************************* -- **************************************************************************** -- ** -- ** This program is free software: you can redistribute it and/or modify -- ** it under the terms of the GNU Affero General Public License as published by -- ** the Free Software Foundation, either version 3 of the License, or -- ** (at your option) any later version. -- ** -- ** This program is distributed in the hope that it will be useful, -- ** but WITHOUT ANY WARRANTY; without even the implied warranty of -- ** MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -- ** GNU Affero General Public License for more details. -- ** -- ** a copy of the GNU Affero General Public Licenses provided is part of the -- ** down load Terms and Conditions available on our site www.piper-rx.com or -- ** you can refere to www.gnu.org/licenses/gpl.html -- ** -- **************************************************************************** SELECT to_char( prx.full_day, 'DD-Mon-YY (Dy)')||','|| sum(decode(sign( 0 - prx.start_time)+ sign( 0 - prx.end_time), 0 ,1, 0))||','|| -- 0:00am sum(decode(sign( 1800 - prx.start_time)+ sign( 1800 - prx.end_time), 0 ,1, 0))||','|| -- 0:30am sum(decode(sign( 3600 - prx.start_time)+ sign( 3600 - prx.end_time), 0 ,1, 0))||','|| -- 1:00am sum(decode(sign( 5400 - prx.start_time)+ sign( 5400 - prx.end_time), 0 ,1, 0))||','|| -- 1:30am sum(decode(sign( 7200 - prx.start_time)+ sign( 7200 - prx.end_time), 0 ,1, 0))||','|| -- 2:00am sum(decode(sign( 9000 - prx.start_time)+ sign( 9000 - prx.end_time), 0 ,1, 0))||','|| -- 2:00am sum(decode(sign( 10800 - prx.start_time)+ sign( 10800 - prx.end_time), 0 ,1, 0))||','|| -- 3:00am sum(decode(sign( 12600 - prx.start_time)+ sign( 12600 - prx.end_time), 0 ,1, 0))||','|| -- 3:30am sum(decode(sign( 14400 - prx.start_time)+ sign( 14400 - prx.end_time), 0 ,1, 0))||','|| -- 4:00am sum(decode(sign( 16200 - prx.start_time)+ sign( 16200 - prx.end_time), 0 ,1, 0))||','|| -- 4:30am sum(decode(sign( 18000 - prx.start_time)+ sign( 18000 - prx.end_time), 0 ,1, 0))||','|| -- 5:00am sum(decode(sign( 19800 - prx.start_time)+ sign( 19800 - prx.end_time), 0 ,1, 0))||','|| -- 5:30am sum(decode(sign( 21600 - prx.start_time)+ sign( 21600 - prx.end_time), 0 ,1, 0))||','|| -- 6:00am sum(decode(sign( 23400 - prx.start_time)+ sign( 23400 - prx.end_time), 0 ,1, 0))||','|| -- 6:30am sum(decode(sign( 25200 - prx.start_time)+ sign( 25200 - prx.end_time), 0 ,1, 0))||','|| -- 7:00am sum(decode(sign( 27000 - prx.start_time)+ sign( 27000 - prx.end_time), 0 ,1, 0))||','|| -- 7:30am sum(decode(sign( 28800 - prx.start_time)+ sign( 28800 - prx.end_time), 0 ,1, 0))||','|| -- 8:00am sum(decode(sign( 30600 - prx.start_time)+ sign( 30600 - prx.end_time), 0 ,1, 0))||','|| -- 8:30am sum(decode(sign( 32400 - prx.start_time)+ sign( 32400 - prx.end_time), 0 ,1, 0))||','|| -- 9:00am sum(decode(sign( 34200 - prx.start_time)+ sign( 34200 - prx.end_time), 0 ,1, 0))||','|| -- 9:30am sum(decode(sign( 36000 - prx.start_time)+ sign( 36000 - prx.end_time), 0 ,1, 0))||','|| -- 10:00am sum(decode(sign( 37800 - prx.start_time)+ sign( 37800 - prx.end_time), 0 ,1, 0))||','|| -- 10:30am sum(decode(sign( 39600 - prx.start_time)+ sign( 39600 - prx.end_time), 0 ,1, 0))||','|| -- 11:00am sum(decode(sign( 41400 - prx.start_time)+ sign( 41400 - prx.end_time), 0 ,1, 0))||','|| -- 11:30am sum(decode(sign( 43200 - prx.start_time)+ sign( 43200 - prx.end_time), 0 ,1, 0))||','|| -- 12:00am sum(decode(sign( 45000 - prx.start_time)+ sign( 45000 - prx.end_time), 0 ,1, 0))||','|| -- 12:30am sum(decode(sign( 46800 - prx.start_time)+ sign( 46800 - prx.end_time), 0 ,1, 0))||','|| -- 1:00pm sum(decode(sign( 48600 - prx.start_time)+ sign( 48600 - prx.end_time), 0 ,1, 0))||','|| -- 1:30pm sum(decode(sign( 50400 - prx.start_time)+ sign( 50400 - prx.end_time), 0 ,1, 0))||','|| -- 2:00pm sum(decode(sign( 52200 - prx.start_time)+ sign( 52200 - prx.end_time), 0 ,1, 0))||','|| -- 2:30pm sum(decode(sign( 54000 - prx.start_time)+ sign( 54000 - prx.end_time), 0 ,1, 0))||','|| -- 3:00pm sum(decode(sign( 55800 - prx.start_time)+ sign( 55800 - prx.end_time), 0 ,1, 0))||','|| -- 3:30pm sum(decode(sign( 57600 - prx.start_time)+ sign( 57600 - prx.end_time), 0 ,1, 0))||','|| -- 4:00pm sum(decode(sign( 59400 - prx.start_time)+ sign( 59400 - prx.end_time), 0 ,1, 0))||','|| -- 4:30pm sum(decode(sign( 61200 - prx.start_time)+ sign( 61200 - prx.end_time), 0 ,1, 0))||','|| -- 5:00pm sum(decode(sign( 63000 - prx.start_time)+ sign( 63000 - prx.end_time), 0 ,1, 0))||','|| -- 5:30pm sum(decode(sign( 64800 - prx.start_time)+ sign( 64800 - prx.end_time), 0 ,1, 0))||','|| -- 6:00pm sum(decode(sign( 66600 - prx.start_time)+ sign( 66600 - prx.end_time), 0 ,1, 0))||','|| -- 6:30pm sum(decode(sign( 68400 - prx.start_time)+ sign( 68400 - prx.end_time), 0 ,1, 0))||','|| -- 7:00pm sum(decode(sign( 70200 - prx.start_time)+ sign( 70200 - prx.end_time), 0 ,1, 0))||','|| -- 7:30pm sum(decode(sign( 72000 - prx.start_time)+ sign( 72000 - prx.end_time), 0 ,1, 0))||','|| -- 8:00pm sum(decode(sign( 73800 - prx.start_time)+ sign( 73800 - prx.end_time), 0 ,1, 0))||','|| -- 8:30pm sum(decode(sign( 75600 - prx.start_time)+ sign( 75600 - prx.end_time), 0 ,1, 0))||','|| -- 9:00pm sum(decode(sign( 77400 - prx.start_time)+ sign( 77400 - prx.end_time), 0 ,1, 0))||','|| -- 9:30pm sum(decode(sign( 79200 - prx.start_time)+ sign( 79200 - prx.end_time), 0 ,1, 0))||','|| -- 10:00pm sum(decode(sign( 81000 - prx.start_time)+ sign( 81000 - prx.end_time), 0 ,1, 0))||','|| -- 10:30pm sum(decode(sign( 82800 - prx.start_time)+ sign( 82800 - prx.end_time), 0 ,1, 0))||','|| -- 11:00pm sum(decode(sign( 84600 - prx.start_time)+ sign( 84600 - prx.end_time), 0 ,1, 0)) CHART_THIS-- 11:30pm FROM ( SELECT trunc(fl.start_time)full_day, to_char(fl.start_time, 'SSSSS' ) start_time, to_char(fl.end_time, 'SSSSS' ) end_time FROM applsys.fnd_logins fl, applsys.fnd_login_responsibilities flr WHERE fl.login_id = flr.login_id and nvl(fl.terminal_id, 'X') != 'Concurrent' and (fl.end_time - fl.start_time) < 1 -- Exclude long running sessions and (sysdate - fl.start_time) < 7 -- Last seven days only ) prx GROUP by prx.full_day ORDER By prx.full_day DESC;