CREATE OR REPLACE PACKAGE PIPER_RX_FOG_CUSTOM_ALERTS IS
   
/****************************************************

Copyright (C) 2009 G.Piper

Examples for generating custom Foglight alerts in to OEBS cartridge

Note: This only works for the Foglight 5 Oracle E-Business Suite module 
as the summary table does not exist in other cartridges.

THIS PLSQL CODE IS NOT SUPPORTED BY EITHER PIPER-RX OR QUEST SOFTWARE

DISCLAIMER
Please refer to our Disclaimer at www.piper-rx.com

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

-----------------------------------------------------
Version 1.0       G.Piper May-09    Initial Creation
*****************************************************/

   PROCEDURE CHECK_ACTIVE_USERS       (i_alert_level number);
   PROCEDURE CHECK_COMPLETED_ERROR    (i_alert_level number);
   PROCEDURE CHECK_CONCURRENT_PROGRAM (i_program_application_id number, i_concurrent_program_id number );
   PROCEDURE CHECK_FOR_MY_BIRTHDAY;

   
END PIPER_RX_FOG_CUSTOM_ALERTS;
/


CREATE OR REPLACE PACKAGE BODY PIPER_RX_FOG_CUSTOM_ALERTS AS

error_message VARCHAR2(100);



PROCEDURE CHECK_ACTIVE_USERS  (i_alert_level number) AS

v_user_count        number(10);
v_server_time_stamp varchar2(30);
v_alert_id          varchar2(30) := 'SPECIAL-001';

-- -------------------------------------------
-- Choose one of the following severities
-- -------------------------------------------
v_severity varchar2(20):= 'Warning' ;
-- v_severity varchar2(20) := 'Critical';
-- v_severity varchar2(20) := 'Fatal'; -- Use this level with care as it will register a Foglight SLA failure

v_details           varchar2(240); -- This will be generated in the same format as all OEBS cartridge meessages
v_your_message      varchar2(200);

BEGIN

    -- ----------------------
    -- YOUR EXCEPTION CHECK SQL GOES HERE
    -- ----------------------
    SELECT count(*)
      INTO v_user_count
      FROM applsys.fnd_logins fl
     WHERE fl.start_time < sysdate
       and fl.end_time is null
       and nvl(fl.terminal_id, 'X') != 'Concurrent';

    -- ------------------------------------------
    -- Add record to Foglight summary table on Exception
    -- ------------------------------------------
    IF ( v_user_count > i_alert_level ) THEN 

        SELECT to_char(sysdate, 'DD-Mon-YY HH24:MI')
          INTO v_server_time_stamp
          FROM dual;

        v_details := '['||v_server_time_stamp||'] ';

        -- ------------------------------------------------------------------------------------------
        -- YOUR MESSAGE GOES HERE
        -- ------------------------------------------------------------------------------------------
        v_your_message := 'There are '||v_user_count||' active users - exceeding the alert level of ' ||i_alert_level ;

        v_details := v_details||v_your_message;

        INSERT into QUEST_FG_OEBS_ALERT_SUMMARY_T
        VALUES ( sysdate, v_alert_id, v_severity, v_details);

        COMMIT;

    END IF;

-- *****************************
-- **       EXCEPTION         **
-- *****************************
EXCEPTION
 WHEN NO_DATA_FOUND THEN
  error_message := 'No data Found';
  dbms_output.put_line('CHECK_ACTIVE_USERS: '||error_message);
 WHEN OTHERS THEN
   error_message := 'Other problem';
   dbms_output.put_line('CHECK_ACTIVE_USERS: '||error_message);
   dbms_output.put_line('Oracle Error : '|| sqlerrm);

END CHECK_ACTIVE_USERS;


PROCEDURE CHECK_COMPLETED_ERROR  (i_alert_level number) AS

v_completed_error   number(10);
v_server_time_stamp varchar2(30);
v_alert_id          varchar2(30) := 'SPECIAL-002';

-- -------------------------------------------
-- Choose one of the following severities
-- -------------------------------------------
v_severity varchar2(20):= 'Warning' ;
-- v_severity varchar2(20) := 'Critical';
-- v_severity varchar2(20) := 'Fatal'; -- Use this level with care as it will register a Foglight SLA failure

v_details           varchar2(240); -- This will be generated in the same format as all OEBS cartridge meessages
v_your_message      varchar2(200);

BEGIN

    -- ----------------------
    -- YOUR EXCEPTION CHECK SQL GOES HERE
    -- ----------------------
    SELECT count(*)
      INTO v_completed_error
      FROM applsys.fnd_concurrent_requests fcr
     WHERE fcr.status_code =  'E'
       and TRUNC(actual_start_date) = trunc(sysdate) 
       and not exists ( SELECT 'X'
                          FROM QUEST_FG_OEBS_ALERT_SUMMARY_T
                         WHERE alertid = v_alert_id
                           and trunc(timestamp) = trunc(sysdate));

    -- ------------------------------------------
    -- Add record to Foglight summary table on Exception
    -- ------------------------------------------
    IF ( v_completed_error > i_alert_level ) THEN 

        SELECT to_char(sysdate, 'DD-Mon-YY HH24:MI')
          INTO v_server_time_stamp
          FROM dual;

        v_details := '['||v_server_time_stamp||'] ';

        -- ------------------------------------------------------------------------------------------
        -- YOUR MESSAGE GOES HERE
        -- ------------------------------------------------------------------------------------------
        v_your_message := 'The number of ERROR requests ('||v_completed_error||') has exceeded the alert level of ' ||i_alert_level ;

        v_details := v_details||v_your_message;

        INSERT into QUEST_FG_OEBS_ALERT_SUMMARY_T
        VALUES ( sysdate, v_alert_id, v_severity, v_details);

        COMMIT;

    END IF;

-- *****************************
-- **       EXCEPTION         **
-- *****************************
EXCEPTION
 WHEN NO_DATA_FOUND THEN
  error_message := 'No data Found';
  dbms_output.put_line('CHECK_COMPLETED_ERROR: '||error_message);
 WHEN OTHERS THEN
   error_message := 'Other problem';
   dbms_output.put_line('CHECK_COMPLETED_ERROR: '||error_message);
   dbms_output.put_line('Oracle Error : '|| sqlerrm);

END CHECK_COMPLETED_ERROR;


PROCEDURE CHECK_CONCURRENT_PROGRAM  (i_program_application_id number, i_concurrent_program_id number ) AS

v_record_count      number(20);

c_request_id           number(20);
c_requested_by         varchar2(30);
c_program_name         varchar2(50);
c_requested_start_date date;

v_last_check_date   date;
v_alert_id          varchar2(30) := 'SPECIAL-';

-- -------------------------------------------
-- Choose one of the following severities
-- -------------------------------------------
v_severity varchar2(20):= 'Warning' ;
-- v_severity varchar2(20) := 'Critical';
-- v_severity varchar2(20) := 'Fatal'; -- Use this level with care as it will register a Foglight SLA failure

v_details           varchar2(240); -- This will be generated in the same format as all OEBS cartridge meessages
v_your_message      varchar2(200);

CURSOR CHECK_CONCURRENT_PROGRAM_C IS
SELECT fcr.request_id,
       substr(fu.user_name,1, 30),
       substr(fcpt.user_concurrent_program_name,1, 50),
       to_char(fcr.requested_start_date, 'DD-Mon-YY HH24:MI:SS')
  FROM applsys.fnd_concurrent_requests fcr,
       applsys.fnd_concurrent_programs_tl fcpt,
       applsys.fnd_user fu
 WHERE fcr.program_application_id = 0 --i_program_application_id
   and fcr.concurrent_program_id = 32263 --i_concurrent_program_id
   and fcr.program_application_id = fcpt.application_id
   and fcr.concurrent_program_id = fcpt.concurrent_program_id
   and fcpt.language = userenv('LANG')
   and fcr.requested_by = fu.user_id;
   --and fcr.requested_start_date > v_last_check_date;


BEGIN

-- Make sure the summary table has at least one entry or script will fail with no data found

SELECT count(*)
  INTO v_record_count
  FROM QUEST_FG_OEBS_ALERT_SUMMARY_T;

 IF ( v_record_count = 0 ) THEN

    SELECT trunc(sysdate)
      INTO v_last_check_date
      FROM dual;

 ELSE

    SELECT greatest (max(timestamp), trunc(sysdate)) -- Stops storm is first connected and very few summary messages
      INTO v_last_check_date
      FROM QUEST_FG_OEBS_ALERT_SUMMARY_T;

 END IF;


 OPEN CHECK_CONCURRENT_PROGRAM_C;
 LOOP
     FETCH CHECK_CONCURRENT_PROGRAM_C into 
                           c_request_id,
                           c_requested_by,
                           c_program_name,
                           c_requested_start_date;
     
      EXIT WHEN CHECK_CONCURRENT_PROGRAM_C%NOTFOUND;

         v_alert_id := v_alert_id||c_request_id;
         v_details := '['||c_requested_start_date||'] ';

        -- ------------------------------------------------------------------------------------------
        -- YOUR MESSAGE GOES HERE
        -- ------------------------------------------------------------------------------------------
        v_your_message := 'Program '|| c_program_name||' submitted by '||c_requested_by||' at '||c_requested_start_date ;

        v_details := v_details||v_your_message;

        INSERT into QUEST_FG_OEBS_ALERT_SUMMARY_T
        VALUES ( sysdate, v_alert_id, v_severity, v_details);

        COMMIT;

 
 END LOOP;
 
 CLOSE CHECK_CONCURRENT_PROGRAM_C;

-- *****************************
-- **       EXCEPTION         **
-- *****************************
EXCEPTION
 WHEN NO_DATA_FOUND THEN
  error_message := 'No data Found';
  dbms_output.put_line('CHECK_CONCURRENT_PROGRAM: '||error_message);
 WHEN OTHERS THEN
   error_message := 'Other problem';
   dbms_output.put_line('CHECK_CONCURRENT_PROGRAM: '||error_message);
   dbms_output.put_line('Oracle Error : '|| sqlerrm);

END CHECK_CONCURRENT_PROGRAM;



PROCEDURE CHECK_FOR_MY_BIRTHDAY IS

v_birthday_date     varchar2(10) := '15-FEB';
v_check_value       number(10);
v_server_time_stamp varchar2(30);
v_alert_id          varchar2(30) := 'B-DAY:001';
-- -------------------------------------------
-- Choose one of the following severities
-- -------------------------------------------
v_severity varchar2(20):= 'Warning' ;
-- v_severity varchar2(20) := 'Critical';
-- v_severity varchar2(20) := 'Fatal'; -- Use this level with care as it will register a Foglight SLA failure

v_details           varchar2(240); -- This will be generated in the same format as all OEBS cartridge meessages
v_your_message      varchar2(200);

BEGIN

    -- ----------------------
    -- YOUR EXCEPTION CHECK SQL GOES HERE
    -- ----------------------
    SELECT count(*)
      INTO v_check_value
      FROM dual
     WHERE to_char(sysdate, 'DD-MON') = v_birthday_date;

    -- ------------------------------------------
    -- Add record to Foglight summary table on Exception
    -- ------------------------------------------
    IF ( v_check_value > 0 ) THEN 

        SELECT to_char(sysdate, 'DD-Mon-YY HH24:MI')
          INTO v_server_time_stamp
          FROM dual;

        v_details := '['||v_server_time_stamp||'] ';

        -- ------------------------------------------------------------------------------------------
        -- YOUR MESSAGE GOES HERE
        -- ------------------------------------------------------------------------------------------
        v_your_message := 'Today is Gary Pipers Birthday - Dont embarrass yourself with a gift too small';

        v_details := v_details||v_your_message;

        INSERT into QUEST_FG_OEBS_ALERT_SUMMARY_T
        VALUES ( sysdate, v_alert_id, v_severity, v_details);

        COMMIT;

    END IF;

-- *****************************
-- **       EXCEPTION         **
-- *****************************
EXCEPTION
 WHEN NO_DATA_FOUND THEN
  error_message := 'No data Found';
  dbms_output.put_line('CHECK_FOR_MY_BIRTHDAY: '||error_message);
 WHEN OTHERS THEN
   error_message := 'Other problem';
   dbms_output.put_line('CHECK_FOR_MY_BIRTHDAY: '||error_message);
   dbms_output.put_line('Oracle Error : '|| sqlerrm);

END CHECK_FOR_MY_BIRTHDAY;

END PIPER_RX_FOG_CUSTOM_ALERTS;