Rem Rem $Header: sdk_blackouts_pkgbody.sql 27-dec-2007.01:42:48 smudumba Exp $ Rem Rem sdk_blackouts_pkgbody.sql Rem Rem Copyright (c) 2002, 2007, Oracle. All rights reserved. Rem Rem NAME Rem sdk_blackouts_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem smudumba 12/27/07 - Bug: 6430179 Blackout Reasons NLS Rem vkgarg 10/21/04 - moving reasons procs to mgmt_blackout_ui pkg Rem mbhalla 10/05/04 - grabtrans 'vkgarg_botrans' Rem mbhalla 10/05/04 - Rem dcawley 07/07/04 - Increase user name size Rem skini 11/22/02 - Only superusers can create reason Rem skini 09/18/02 - blackout windows Rem skini 08/26/02 - Introduce START_PROCESSING Rem skini 08/26/02 - ENDED state Rem skini 08/20/02 - New blackout states Rem skini 08/13/02 - Continue development, finish edit Rem skini 08/02/02 - Rem skini 08/01/02 - Rem skini 07/31/02 - Implement stop Rem skini 07/26/02 - Change schedule table to refer to blackouts Rem skini 07/22/02 - Rem skini 07/19/02 - Continue blackout implementation Rem skini 07/17/02 - Continue blackout development Rem skini 06/20/02 - skini_blackouts Rem skini 06/14/02 - Continue blackout implementation Rem skini 06/12/02 - MGMT_BLACKOUTS=>MGMT_BLACKOUT Rem skini 06/07/02 - Created Rem CREATE OR REPLACE PACKAGE BODY MGMT_BLACKOUT AS -- -- Public procedures -- -- -- CREATE_BLACKOUT -- -- PURPOSE -- Create a new blackout -- -- FUNCTION create_blackout(p_blackout_name IN VARCHAR2, p_description IN VARCHAR2, p_schedule IN MGMT_BLACKOUT_SCHEDULE_RECORD, p_reason IN VARCHAR2, p_job_flag IN NUMBER, p_targets IN MGMT_BLACKOUT_TARGET_LIST) RETURN RAW IS BEGIN RETURN MGMT_BLACKOUT_ENGINE.create_blackout(p_blackout_name, p_description, p_schedule, p_reason, p_job_flag, p_targets); END; -- -- EDIT_BLACKOUT -- -- PURPOSE -- Edit an existing blackout. In the description below, if any of -- the parameters passed in is null, then the existing value is unchanged -- Note that some attributes cannot be changed when a blackout is active, -- for example, the target list, shortening the duration. -- -- PROCEDURE edit_blackout(p_blackout_id IN RAW, p_description IN VARCHAR2, p_schedule IN MGMT_BLACKOUT_SCHEDULE_RECORD, p_reason IN VARCHAR2, p_job_flag IN NUMBER, p_targets_add IN MGMT_BLACKOUT_TARGET_LIST, p_targets_remove IN MGMT_BLACKOUT_TARGET_LIST) IS BEGIN MGMT_BLACKOUT_ENGINE.edit_blackout(p_blackout_id, p_description, p_schedule, p_reason, p_job_flag, p_targets_add, p_targets_remove); END; -- -- STOP_BLACKOUT -- -- PURPOSE -- Stop an existing blackout. If the targets list is empty, the stop -- is applied to all targets in the blackout -- -- PROCEDURE stop_blackout(p_blackout_id IN RAW, p_targets IN MGMT_BLACKOUT_TARGET_LIST) IS BEGIN MGMT_BLACKOUT_ENGINE.stop_blackout(p_blackout_id); END; -- -- DELETE_BLACKOUT -- -- The DELETE_BLACKOUT procedure deletes an existing blackout. Note that -- deletion may not be a synchronous operation. The application must -- check the state of the blackout in the blackout table to ensure that -- it was deleted -- -- PROCEDURE delete_blackout(p_blackout_id IN RAW) IS BEGIN MGMT_BLACKOUT_ENGINE.delete_blackout(p_blackout_id); END; FUNCTION add_reason(p_reason VARCHAR2, p_reason_nlsid VARCHAR2 DEFAULT NULL) RETURN NUMBER IS l_reason_id NUMBER; BEGIN IF MGMT_USER.has_priv(MGMT_USER.GET_CURRENT_EM_USER, MGMT_USER.SUPER_USER) != MGMT_USER.USER_HAS_PRIV THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, 'Only superusers can create a new reason'); END IF; BEGIN SELECT l_reason_id INTO l_reason_id FROM MGMT_BLACKOUT_REASON WHERE reason = p_reason; UPDATE MGMT_BLACKOUT_REASON SET reason_nls_id = p_reason_nlsid WHERE reason = p_reason; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO MGMT_BLACKOUT_REASON(reason_id, reason, reason_nls_id) VALUES (MGMT_BLACKOUT_REASON_SEQ.NEXTVAL, p_reason, p_reason_nlsid) RETURNING reason_id INTO l_reason_id; END; RETURN l_reason_id; END; -- -- FUNCTION: get_reason_required -- FUNCTION get_reason_required RETURN VARCHAR2 IS l_reason_required VARCHAR2(10); BEGIN l_reason_required:=NULL; BEGIN SELECT parameter_value INTO l_reason_required FROM mgmt_parameters WHERE parameter_name = MGMT_BLACKOUT.REASON_REQUIRED_PARAMETER_NAME; EXCEPTION WHEN NO_DATA_FOUND --Required parameter is not present in the Database, so we set it --and return the default value. THEN INSERT INTO mgmt_parameters (parameter_name, parameter_value, parameter_comment, internal_flag) VALUES (MGMT_BLACKOUT.REASON_REQUIRED_PARAMETER_NAME, 'false', 'Indicates whether Blackout Reasons are mandatory when creating Blackouts', 0); COMMIT; l_reason_required := 'false'; WHEN OTHERS THEN RAISE; END; RETURN l_reason_required; END get_reason_required; END MGMT_BLACKOUT; /