CREATE OR REPLACE FUNCTION get_string_app_setting(find_key VARCHAR2) RETURN VARCHAR2
IS
TYPE ref_cursor IS REF CURSOR;
c_config ref_cursor;
r_cmn_config cmn_cf_configuration.value%TYPE;
r_dib_config VARCHAR2(255);
dib_config_table VARCHAR2(100);
sql_string VARCHAR2(500);
BEGIN
-- Get the dib variant table
dib_config_table := common.fn_get_pdl_app_table('dib_config_meta_table');
IF (dib_config_table IS NULL) THEN
-- Get configuration setting from common configuration table
OPEN c_config FOR SELECT value FROM cmn_cf_configuration WHERE key = find_key;
FETCH c_config INTO r_cmn_config;
CLOSE c_config;
RETURN r_cmn_config;
ELSE
-- Get configuration setting from dib or common configuration table
-- Find consolidated config values.
sql_string := 'SELECT value FROM ' dib_config_table ' WHERE key = ''' find_key ''' UNION ';
sql_string := sql_string ' SELECT c.value FROM cmn_cf_configuration c';
sql_string := sql_string ' LEFT OUTER JOIN ' dib_config_table ' d ON d.key = c.key';
sql_string := sql_string ' WHERE d.key IS NULL'; --Ignore any settings which have been overridden by Variant.
sql_string := sql_string ' AND c.key = ''' find_key '''';
DBMS_OUTPUT.put_line(sql_string);
EXECUTE IMMEDIATE sql_string INTO r_dib_config;
RETURN r_dib_config;
END IF;
END get_string_app_setting;
