Rem Rem $Header: sdk_view_user_pkgbody.sql 02-may-2005.06:38:25 mkm Exp $ Rem Rem sdk_view_user_pkgbody.sql Rem Rem Copyright (c) 2004, 2005, Oracle. All rights reserved. Rem Rem NAME Rem sdk_view_user_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem mkm 05/02/05 - adding ADJUST_TZ Rem mgoodric 10/26/04 - mgoodric_add_mgmt_view_package_041025 Rem mgoodric 10/23/04 - moved from ecm_util_pkgbody Rem CREATE OR REPLACE PACKAGE BODY MGMT_VIEW_UTIL IS -- Given a text (alphanumeric) string, try to figure out if the string is a -- number (Numeric Value). If yes, return the number else return NULL FUNCTION NV( v_textInQuestion IN VARCHAR2 ) RETURN NUMBER IS numberTry NUMBER; result NUMBER; BEGIN result := NULL; -- try a numeric conversion BEGIN numberTry := TO_NUMBER( v_textInQuestion ); result := numberTry; EXCEPTION WHEN OTHERS THEN result := NULL; END; RETURN result; END NV; ----------------------------------------------------------------------- -- Function that returns a string that is a concatenated list -- of all the key's name-value pairs. This can be used to display keys. -- Example result: 'Name: kernel; Type: Package' -- FUNCTION GET_DELTA_KEY_NLS_STRING( v_key_row_guid RAW, v_target_type VARCHAR2, v_snapshot_type VARCHAR2, v_collection_type VARCHAR2, v_locale VARCHAR2 DEFAULT NULL ) RETURN NVARCHAR2 IS tmp NVARCHAR2(4000) := NULL; tmp_value NVARCHAR2(4000); l_ui_name MGMT_MESSAGES.message%TYPE; l_history_ui_on MGMT_ECM_SNAPSHOT_MD_COLUMNS.history_ui_on%TYPE; l_bundle MGMT_MESSAGES.message_id%TYPE; BEGIN l_bundle := v_target_type || '.' || v_snapshot_type || '.'; FOR k IN ( SELECT id.name, id.value, NVL(c.ui_name, c.name) ui_name, c.source_table_name FROM MGMT_DELTA_ID_VALUES id, MGMT_ECM_MD_ALL_TBL_COLUMNS c WHERE id.delta_ids_guid = v_key_row_guid AND c.target_type = v_target_type AND c.snapshot_type = v_snapshot_type AND c.table_name = v_collection_type AND c.name = id.name AND c.history_ui_on = 'Y') LOOP BEGIN l_ui_name := GET_DELTA_RESOURCE_NLS_STRING( k.name, l_bundle || k.source_table_name, v_locale, k.ui_name); EXCEPTION WHEN OTHERS THEN l_ui_name := k.name; END; IF k.value IS NULL THEN tmp_value := GET_DELTA_RESOURCE_NLS_STRING('UNAVAILABLE', 'MGMT_DELTA_ENTRY_ID_VALUES', v_locale, 'Unavailable'); ELSE tmp_value := k.value; END IF; IF tmp IS NULL THEN tmp := l_ui_name || ': ' || tmp_value; ELSE tmp := tmp || '; ' || l_ui_name || ': ' || tmp_value; END IF; END LOOP; RETURN tmp; EXCEPTION WHEN VALUE_ERROR THEN RETURN tmp; -- Truncated. END GET_DELTA_KEY_NLS_STRING; ----------------------------------------------------------------------- -- Function that returns a string that is the column's NLS display name -- Example result: 'Name' for NAME column -- FUNCTION GET_DELTA_VALUE_NLS_STRING( v_column_name VARCHAR2, v_target_type VARCHAR2, v_snapshot_type VARCHAR2, v_collection_type VARCHAR2, v_locale VARCHAR2 DEFAULT NULL ) RETURN NVARCHAR2 IS l_ui_name MGMT_ECM_SNAPSHOT_MD_COLUMNS.ui_name%TYPE; l_bundle MGMT_MESSAGES.message_id%TYPE; BEGIN BEGIN l_bundle := v_target_type || '.' || v_snapshot_type || '.'; SELECT NVL(c.ui_name, v_column_name), l_bundle || c.source_table_name INTO l_ui_name, l_bundle FROM MGMT_ECM_MD_ALL_TBL_COLUMNS c WHERE c.snapshot_type = v_snapshot_type AND c.target_type = v_target_type AND c.table_name = v_collection_type AND c.name = v_column_name; RETURN GET_DELTA_RESOURCE_NLS_STRING(v_column_name, l_bundle, v_locale, l_ui_name); EXCEPTION WHEN OTHERS THEN RETURN v_column_name; END; END GET_DELTA_VALUE_NLS_STRING; ----------------------------------------------------------------------- -- Function that returns a string that is the resource id's NLS display name -- Example result: 'New Item' for INSERT value -- FUNCTION GET_DELTA_RESOURCE_NLS_STRING( v_resource_id VARCHAR2, v_resource_bundle VARCHAR2, v_locale VARCHAR2 DEFAULT NULL, v_default VARCHAR2 DEFAULT NULL ) RETURN NVARCHAR2 IS l_locale VARCHAR2(20); l_default VARCHAR2(1000) := v_default; l_message_id MGMT_MESSAGES.message_id%TYPE; l_subsystem MGMT_MESSAGES.subsystem%TYPE := 'ECM_METADATA'; l_language_code MGMT_MESSAGES.language_code%TYPE; l_country_code MGMT_MESSAGES.country_code%TYPE := NULL; l_message MGMT_MESSAGES.message%TYPE; BEGIN IF l_default IS NULL THEN l_default := v_resource_id; END IF; IF v_resource_id IS NULL THEN l_message := l_default; ELSE IF v_locale IS NOT NULL THEN l_locale := v_locale; ELSE l_locale := 'en'; END IF; IF (INSTR(l_locale,'_') = 3) AND (LENGTH(l_locale) >= 5) -- e.g. en_US THEN l_country_code := SUBSTR(l_locale,4,2); l_language_code := SUBSTR(l_locale,1,2); ELSE l_language_code := l_locale; END IF; IF (l_country_code IS NULL) OR (l_country_code = 'US') THEN l_country_code := ' '; END IF; IF v_resource_bundle IS NOT NULL THEN l_message_id := v_resource_bundle || '.' || v_resource_id; ELSE l_message_id := v_resource_id; END IF; l_message := MGMT_MESSAGE.GET_MESSAGE(l_message_id, l_subsystem, l_language_code, l_country_code, l_default); END IF; RETURN l_message; END GET_DELTA_RESOURCE_NLS_STRING; ---------------------------------------------------------------------------------- FUNCTION ADJUST_TZ(v_date_in DATE, v_from_tz VARCHAR2, v_to_tz VARCHAR2) RETURN DATE IS BEGIN RETURN mgmt_global.ADJUST_TZ(v_date_in,v_from_tz,v_to_tz); END ADJUST_TZ; END MGMT_VIEW_UTIL; / show errors;