Rem drv: Rem $Header: page_custmzn_pkgbody.sql 04-aug-2005.12:31:15 vesriniv Exp $ Rem Rem page_custmzn_pkgbody.sql Rem Rem Copyright (c) 2004, 2005, Oracle. All rights reserved. Rem Rem NAME Rem page_custmzn_pkgbody.sql - This sql has API to upload customization metadata Rem Rem MODIFIED (MM/DD/YY) Rem vesriniv 08/04/05 - vesriniv_custtxn Rem vesriniv 07/28/05 - input validation Rem vesriniv 07/26/05 - Rem rgorle 07/18/05 - Fixed the iD retrieval issue. Rem vesriniv 06/06/05 - created Rem CREATE OR REPLACE PACKAGE BODY em_page_custmzn AS -- -- PROCEDURE: add_page_customzn_metadata -- PURPOSE: To add the page to customization metadata -- -- PARAMETERS: -- p_page_name : Name of the UI page which supports customization -- -- p_customization_data: A list of name/value pairs containing customization data. -- Example: disallow_perf_metrics, false -- -- p_condition_names: A list of condition for the given page -- Example: type, target etc -- -- ERROR CODES: -- PAGE_NAME_ALREADY_EXISTS - The given page name alrady exists in the table -- CONDITION_ALREADY_EXISTS - The given condition metadata already exists in the table -- PROCEDURE add_page_customzn_metadata ( p_page_name IN VARCHAR2, p_customization_data IN SMP_EMD_NVPAIR_ARRAY, p_condition_names IN SMP_EMD_STRING_ARRAY ) IS BEGIN IF (p_page_name IS NULL) OR (p_customization_data IS NULL) OR (p_condition_names IS NULL) THEN return; END IF; -- insert customization metadata for the given page into EM_PAGE_CUST_METADATA FOR i IN 1..p_customization_data.count LOOP BEGIN INSERT INTO EM_PAGE_CUST_METADATA ( page_name, cust_name,def_cust_value )VALUES ( p_page_name, p_customization_data(i).name, p_customization_data(i).value ); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN RAISE_APPLICATION_ERROR(mgmt_global.page_name_already_exists_err, mgmt_global.PAGE_NAME_ALREADY_EXISTS_M||':'||p_page_name); END; END LOOP; -- insert condition metadata for the given page into EM_PAGE_CONDITION_METADATA FOR j IN 1..p_condition_names.count LOOP BEGIN INSERT INTO EM_PAGE_CONDITION_METADATA ( page_name, condition_name )VALUES ( p_page_name, p_condition_names(j) ); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.CONDITION_ALREADY_EXISTS_ERR, MGMT_GLOBAL.CONDITION_ALREADY_EXISTS_M||':'||p_condition_names(j)|| ':'||p_page_name); END; END LOOP; END add_page_customzn_metadata; --- -- PROCEDURE : get_matching_page_customzns -- PURPOSE : To get the page to customization for the given condition -- -- PARAMETERS : -- p_page_name : Name of the UI page which supports customization -- -- p_context: A list of name/value pairs containing condition data. -- Example: type, ocs_email_system -- -- RETURNS : -- p_customizations: A list of customization for the condition and given page -- Example: disallow_member_target, true -- -- ERROR CODES: -- NO_SUCH_PAGE_EXISTS : The requested page name doesnt exists in the metadata table -- INVALID_CONDITION_NAME : The given condition name doesnt exist in metadata table -- PROCEDURE get_matching_page_customzns ( p_page_name IN VARCHAR2, p_context IN SMP_EMD_NVPAIR_ARRAY, p_customizations OUT SMP_EMD_NVPAIR_ARRAY ) IS l_guid RAW(16) := NULL; l_cust_name VARCHAR2(64); l_cust_value VARCHAR2(64); l_count NUMBER(6) :=0; l_temp_str VARCHAR2(32000) :=''; l_int NUMBER; BEGIN IF (p_page_name IS NULL) OR (p_context IS NULL) THEN return; END IF; -- check whether page_name is valid SELECT count(*) INTO l_count FROM EM_PAGE_CUST_METADATA WHERE page_name = p_page_name; IF l_count =0 THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.NO_SUCH_PAGE_EXISTS_ERR, MGMT_GLOBAL.NO_SUCH_PAGE_EXISTS_M||':'||p_page_name); END IF; l_count := 0; p_customizations := new SMP_EMD_NVPAIR_ARRAY(); FOR i IN 1..p_context.count LOOP BEGIN l_temp_str := l_temp_str || p_context(i).name || ';'||p_context(i).value||';'; END; END LOOP; l_guid := DBMS_OBFUSCATION_TOOLKIT.md5( input => UTL_RAW.cast_to_raw(l_temp_str)); -- check whether condition is valid for the given page_name --SELECT condition_set_id INTO l_guid FROM EM_PAGE_CUSTOM_CONDITIONS WHERE --condition_name = p_context(i).name and condition_value = p_context(i).value; SELECT count(*) INTO l_count FROM EM_PAGE_CUSTOM_CONDITIONS WHERE condition_set_id = l_guid; IF l_count IS NULL THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_CONDITION_NAME_ERR, MGMT_GLOBAL.INVALID_CONDITION_NAME_M||':'); --||p_context(i).name); END IF; -- Retrieve the customizations for the given page and conditions SELECT SMP_EMD_NVPAIR(cust_name, cust_value) BULK COLLECT INTO p_customizations FROM EM_PAGE_CUSTOMIZATIONS WHERE page_name = p_page_name AND condition_set_id = l_guid; END get_matching_page_customzns; PROCEDURE get_matching_customzns ( p_context IN SMP_EMD_NVPAIR_ARRAY, p_customizations OUT cursorType ) IS l_guid RAW(16) := NULL; l_cust_name VARCHAR2(64); l_cust_value VARCHAR2(64); l_count NUMBER(6) :=0; l_temp_str VARCHAR2(32000) :=''; l_int NUMBER; BEGIN IF (p_context IS NULL) THEN return; END IF; l_count := 0; FOR i IN 1..p_context.count LOOP BEGIN l_temp_str := l_temp_str || p_context(i).name || ';'||p_context(i).value||';'; END; END LOOP; l_guid := DBMS_OBFUSCATION_TOOLKIT.md5( input => UTL_RAW.cast_to_raw(l_temp_str)); -- check whether condition is valid for the given page_name --SELECT condition_set_id INTO l_guid FROM EM_PAGE_CUSTOM_CONDITIONS WHERE --condition_name = p_context(i).name and condition_value = p_context(i).value; SELECT count(*) INTO l_count FROM EM_PAGE_CUSTOM_CONDITIONS WHERE condition_set_id = l_guid ; IF l_count IS NULL THEN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_CONDITION_NAME_ERR, MGMT_GLOBAL.INVALID_CONDITION_NAME_M||':'||p_context(1).name); END IF; -- Retrieve the customizations for the given page and conditions OPEN p_customizations FOR SELECT page_name, cust_name, cust_value FROM EM_PAGE_CUSTOMIZATIONS WHERE condition_set_id = l_guid; END get_matching_customzns; END em_page_custmzn; / show errors;