Rem
Rem $Header: sdk_console_page_custmzn_pkgbody.sql 04-aug-2005.12:31:16 vesriniv Exp $
Rem
Rem sdk_console_page_custmzn_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      sdk_console_page_custmzn_pkgbody.sql - This sql has SDK API to access customization framework.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    vesriniv    08/04/05 - vesriniv_custtxn
Rem    vesriniv    07/28/05 - add validation 
Rem    vesriniv    07/20/05 - 
Rem    rgorle      07/17/05 - fix in remove_page_customzn_condns proc 
Rem    vesriniv    06/06/05 - created
Rem    

CREATE OR REPLACE PACKAGE BODY mgmt_page_custmzn AS

--
-- PROCEDURE: add_page_customzn_conditions 
-- PURPOSE: To add the customization and condition data to a page
--
-- PARAMETERS :
--   p_page_name : Name of the UI page which supports customization
--
--   p_conditions: A list of name/value pairs containing condition data.
--                         Example: type, ocs_email_system
--
--   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_CUSTOMIZATION_NAME : The customization name doesnt exists in the metadata table
--       INVALID_CONDITION_NAME : The condtion name doesnt exists in the metadata table
--
PROCEDURE add_page_customzn_conditions (
	p_page_name IN VARCHAR2,
	p_customizations IN SMP_EMD_NVPAIR_ARRAY,
	p_conditions IN SMP_EMD_NVPAIR_ARRAY
)
IS
        l_guid RAW(16) := NULL;
        l_temp_guid RAW(16) := NULL;
        l_count NUMBER(6) :=0;
        l_temp_str VARCHAR2(32000) :='';
        l_temp_int NUMBER;

BEGIN
        IF (p_page_name IS NULL) OR (p_customizations IS NULL) OR (p_conditions IS NULL) THEN
           return;
        END IF; 
        -- Check whether page_name is valid by querying EM_PAGE_CUST_METADATA table
  	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;

        -- Check whether the given customizations are allowed for the given page
	FOR i IN 1..p_customizations.count
	LOOP
           SELECT COUNT(*) INTO l_count FROM EM_PAGE_CUST_METADATA WHERE page_name = p_page_name
	   AND cust_name = p_customizations(i).name;
           IF l_count = 0 THEN
               RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_CUSTOMIZATION_NAME_ERR,
	           MGMT_GLOBAL.INVALID_CUSTOMIZATION_NAME_M ||':'||p_customizations(i).name|| ':'||p_page_name);
           END IF;                            
	   l_count := 0;
	END LOOP;
        l_count := 0;

        -- Check whether the given conditions are allowed for the given page
	FOR i IN 1..p_conditions.count
	LOOP
	        SELECT COUNT(*) INTO l_count FROM EM_PAGE_CONDITION_METADATA WHERE page_name = p_page_name
	        AND condition_name = p_conditions(i).name;
	        
	        IF l_count = 0 THEN
                     RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_CONDITION_NAME_ERR,
		         MGMT_GLOBAL.INVALID_CONDITION_NAME_M||':'||p_conditions(i).name|| ':'|| p_page_name);
                END IF;    
        l_count := 0;
        END LOOP;      
        
        -- Add the given conditions for the given page
	FOR i IN 1..p_conditions.count
	   LOOP
	     BEGIN
                  l_temp_str := l_temp_str || p_conditions(i).name || ';'||p_conditions(i).value||';';
	     END;
	   END LOOP;  
        l_guid :=  DBMS_OBFUSCATION_TOOLKIT.md5( input => UTL_RAW.cast_to_raw(l_temp_str));

        -- Add the given conditions for the given page
         FOR i IN 1..p_conditions.count
	     LOOP
	         BEGIN
                    INSERT INTO EM_PAGE_CUSTOM_CONDITIONS
                    (
                       condition_set_id, condition_name, condition_value
                    )
                    VALUES
                    (
	               l_guid, p_conditions(i).name,
		       p_conditions(i).value
	            );
                  EXCEPTION
		        WHEN DUP_VAL_ON_INDEX THEN
		           dbms_output.put_line('ignore this as same condition guid might exist already');
                  END;          
	      END LOOP;

        -- Add the given customizations for the given page
        FOR j IN 1..p_customizations.count
            LOOP
	      BEGIN
		INSERT INTO EM_PAGE_CUSTOMIZATIONS
                (
                   page_name, cust_name, cust_value, condition_set_id
                )
                VALUES
                (
		   p_page_name, p_customizations(j).name,
		   p_customizations(j).value, l_guid
		);
              EXCEPTION
		   WHEN DUP_VAL_ON_INDEX THEN
		       raise_application_error(MGMT_GLOBAL.CUSTOMIZATION_EXISTS_ERR,
                        MGMT_GLOBAL.CUSTOMIZATION_EXISTS_M || ':' ||  p_customizations(j).name);
              END;              
	    END LOOP;
            
END add_page_customzn_conditions;


--
-- PROCEDURE: remove_page_customzn_condns 
-- PURPOSE: To remove the page to customization data
--
-- PARAMETERS :
--   p_page_name : Name of the UI page which supports customization
--
--   p_conditions: A list of name/value pairs containing condition data.
--                         Example: type, ocs_email_system
--
--   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 for the page in 
--                                in metadata table
--       INVALID_CUSTOMIZATION_NAME : The given customization name doesnt exist for the page in 
--                                in metadata table
--
PROCEDURE remove_page_customzn_condns (
	p_page_name IN VARCHAR2,
	p_customizations IN SMP_EMD_NVPAIR_ARRAY,
	p_conditions IN SMP_EMD_NVPAIR_ARRAY
)
IS
        l_guid RAW(16) := NULL;
        l_count NUMBER(6) := 0;
        l_custom_cursor cursorType;
        l_temp_str VARCHAR2(32000) :='';
        l_temp_int NUMBER; 
        
BEGIN
        IF(p_page_name IS NULL) OR (p_customizations IS NULL) OR (p_conditions IS NULL) THEN
           return;
        END IF;   
  
        -- Check whether page_name is valid by querying EM_PAGE_CUST_METADATA table
  	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;

        -- check whether the given customizations are allowed for the given page
	FOR i IN 1..p_customizations.count
	LOOP
            SELECT COUNT(*) INTO l_count FROM EM_PAGE_CUST_METADATA WHERE page_name = p_page_name
	    AND cust_name = p_customizations(i).name;
            IF l_count = 0 THEN
                     RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_CUSTOMIZATION_NAME_ERR,
		        MGMT_GLOBAL.INVALID_CUSTOMIZATION_NAME_M||':'|| p_customizations(i).name||':' ||   p_page_name);
            END IF;                            
	    l_count := 0;
	END LOOP;
        l_count := 0;

        -- check whether the given conditions are allowed for the given page
	FOR i IN 1..p_conditions.count
	LOOP
	        SELECT COUNT(*) INTO l_count FROM EM_PAGE_CONDITION_METADATA WHERE page_name = p_page_name
	        AND condition_name = p_conditions(i).name;
	        
	        IF l_count = 0 THEN
                     RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_CONDITION_NAME_ERR,
		         MGMT_GLOBAL.INVALID_CONDITION_NAME_M || ':'|| p_conditions(i).name|| ':'||  p_page_name);
                END IF;    
        l_count := 0;
        END LOOP;        

        -- Remove the given conditions for the given page
	FOR i IN 1..p_conditions.count
	   LOOP
	     BEGIN
                  l_temp_str := l_temp_str || p_conditions(i).name || ';'||p_conditions(i).value||';';
	     END;
	   END LOOP;  
        l_guid :=  DBMS_OBFUSCATION_TOOLKIT.md5( input => UTL_RAW.cast_to_raw(l_temp_str));

        -- Remove the given customizations for the given page
        FOR j IN 1..p_customizations.count
            LOOP
	      BEGIN
                DELETE FROM EM_PAGE_CUSTOMIZATIONS WHERE condition_set_id = l_guid AND page_name = p_page_name
                AND cust_name = p_customizations(j).name;
              END;   
           END LOOP;     
        
        SELECT COUNT(1) INTO l_temp_int FROM EM_PAGE_CUSTOMIZATIONS WHERE condition_set_id = l_guid;
        IF l_temp_int = 0 THEN
           DELETE FROM EM_PAGE_CUSTOM_CONDITIONS where condition_set_id = l_guid;
        END IF;   
END remove_page_customzn_condns;

END mgmt_page_custmzn;
/
show errors;