[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Converts a SELECT expression to its correct data type.
BUG FIX
6319734
PARAMETERS
See below.
AUTHOR
ssarnoba
NOTES
-----------------------------------------------------------------------------*/
PROCEDURE Convert_Child_VS_Select_Expr (
p_parent_vs_row IN ego_value_sets_v%ROWTYPE
, p_convert_date IN VARCHAR2 := FND_API.G_FALSE
, x_column_name IN OUT NOCOPY VARCHAR2
)
IS
BEGIN
-- If the value set is not of data type character, conversion is needed.
IF ( p_parent_vs_row.FORMAT_CODE = G_NUMBER_DATA_TYPE ) THEN
x_column_name := 'TO_NUMBER(' || x_column_name || ')';
END Convert_Child_VS_Select_Expr;
Builds the SELECT clause expressions for a child value set definition that
gets inserted into FND_FLEX_VALIDATION_TABLES.
BUG FIX
6319734
PARAMETERS
See below.
AUTHOR
ssarnoba
NOTES
The ID_COLUMN_NAME and VALUE_COLUMN_NAME values ultimately appears in a
SELECT clause to represent the value set. These child value set values
require conversion to their true data type for the values to be read
correctly in the Java layer.
-----------------------------------------------------------------------------*/
PROCEDURE Build_Child_VS_Select_Exprs (
p_parent_vs_row IN ego_value_sets_v%ROWTYPE
, x_id_column_type OUT NOCOPY fnd_flex_validation_tables.id_column_type%TYPE
, x_value_column_type OUT NOCOPY fnd_flex_validation_tables.value_column_type%TYPE
, x_id_column_name OUT NOCOPY fnd_flex_validation_tables.id_column_name%TYPE
, x_value_column_name OUT NOCOPY fnd_flex_validation_tables.value_column_name%TYPE
)
IS
BEGIN
----------------------------------------------------------------------------
-- 1. FND_FLEX_VALIDATION_TABLES --
-- - ID_COLUMN_NAME --
-- - ID_COLUMN_TYPE --
----------------------------------------------------------------------------
-- ID_COLUMN_TYPE must ALWAYS be the true data type of the value set
x_id_column_type := p_parent_vs_row.FORMAT_CODE;
Convert_Child_VS_Select_Expr (
p_parent_vs_row => p_parent_vs_row
, p_convert_date => FND_API.G_FALSE
, x_column_name => x_id_column_name
);
Convert_Child_VS_Select_Expr (
p_parent_vs_row => p_parent_vs_row
, p_convert_date => FND_API.G_TRUE
, x_column_name => x_value_column_name
);
END Build_Child_VS_Select_Exprs;
Inserts a space before an ORDER BY clause, so that order by elimination
takes place for inner query blocks.
BUG FIX
6148833
PARAMETERS
See below.
-----------------------------------------------------------------------------*/
PROCEDURE Insert_Order_By_Space (
p_where_order_by IN OUT NOCOPY VARCHAR2
)
IS
BEGIN
-- Insert a space before an ORDER BY clause, so that ORDER BY elimination
-- takes place for inner query blocks
p_where_order_by := regexp_replace(
p_where_order_by, -- input string
'\)(O)', -- pattern to match
') \1', -- replacement string
1, -- begin the search at the first character
0, -- replaces all occurrences of the match
'i'); -- case insensitive matching
END Insert_Order_By_Space;
PROCEDURE Delete_Action_Data_Level (
p_api_version IN NUMBER
,p_action_id IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Action_Data_Level';
SAVEPOINT Delete_Action_Data_Level;
DELETE FROM EGO_ACTIONS_DL
WHERE ACTION_ID = p_action_id;
ROLLBACK TO Delete_Action_Data_Level;
END Delete_Action_Data_Level;
SELECT additional_where_clause
INTO l_addl_where_clause
FROM fnd_flex_validation_tables
WHERE flex_value_set_id = p_value_set_id;
SELECT validation_type
INTO l_return_value
FROM fnd_flex_value_sets
WHERE flex_value_set_id = p_value_set_id;
SELECT validation_type
INTO l_return_value
FROM fnd_flex_value_sets
WHERE flex_value_set_name = p_value_set_name;
SELECT 'Y' INTO x_fnd_exists
FROM FND_DESCR_FLEX_CONTEXTS
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_internal_name;
SELECT 'Y' INTO x_ego_exists
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_internal_name;
SELECT 'Y' INTO x_fnd_exists
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_Code = p_attr_group_name
AND END_USER_COLUMN_NAME = p_internal_name;
SELECT 'Y' INTO x_ego_exists
FROM EGO_FND_DF_COL_USGS_EXT ext
,FND_DESCR_FLEX_COLUMN_USAGES fl_col
WHERE ext.APPLICATION_ID (+) = fl_col.APPLICATION_ID
AND ext.DESCRIPTIVE_FLEXFIELD_NAME (+) = fl_col.DESCRIPTIVE_FLEXFIELD_NAME
AND ext.DESCRIPTIVE_FLEX_CONTEXT_CODE (+) = fl_col.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND ext.APPLICATION_COLUMN_NAME (+) = fl_col.APPLICATION_COLUMN_NAME
AND ext.APPLICATION_ID = p_application_id
AND ext.DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND ext.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
AND fl_col.END_USER_COLUMN_NAME = p_internal_name;
SELECT flex_value_set_id
INTO l_value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_name = p_value_set_name;
SELECT fnd_flex_values_s.NEXTVAL
INTO l_flex_value_id
FROM dual;
FND_FLEX_VALUES_PKG.INSERT_ROW
(x_rowid => l_rowid
,x_flex_value_id => l_flex_value_id
,x_attribute_sort_order => NULL
,x_flex_value_set_id => l_value_set_id
,x_flex_value => p_internal_name
,x_enabled_flag => NVL(p_enabled,'Y')
,x_summary_flag => 'N'
,x_start_date_active => p_start_date
,x_end_date_active => p_end_date
,x_parent_flex_value_low => NULL
,x_parent_flex_value_high => NULL
,x_structured_hierarchy_level => NULL
,x_hierarchy_level => NULL
,x_compiled_value_attributes => NULL
,x_value_category => NULL
,x_attribute1 => NULL
,x_attribute2 => NULL
,x_attribute3 => NULL
,x_attribute4 => NULL
,x_attribute5 => NULL
,x_attribute6 => NULL
,x_attribute7 => NULL
,x_attribute8 => NULL
,x_attribute9 => NULL
,x_attribute10 => NULL
,x_attribute11 => NULL
,x_attribute12 => NULL
,x_attribute13 => NULL
,x_attribute14 => NULL
,x_attribute15 => NULL
,x_attribute16 => NULL
,x_attribute17 => NULL
,x_attribute18 => NULL
,x_attribute19 => NULL
,x_attribute20 => NULL
,x_attribute21 => NULL
,x_attribute22 => NULL
,x_attribute23 => NULL
,x_attribute24 => NULL
,x_attribute25 => NULL
,x_attribute26 => NULL
,x_attribute27 => NULL
,x_attribute28 => NULL
,x_attribute29 => NULL
,x_attribute30 => NULL
,x_attribute31 => NULL
,x_attribute32 => NULL
,x_attribute33 => NULL
,x_attribute34 => NULL
,x_attribute35 => NULL
,x_attribute36 => NULL
,x_attribute37 => NULL
,x_attribute38 => NULL
,x_attribute39 => NULL
,x_attribute40 => NULL
,x_attribute41 => NULL
,x_attribute42 => NULL
,x_attribute43 => NULL
,x_attribute44 => NULL
,x_attribute45 => NULL
,x_attribute46 => NULL
,x_attribute47 => NULL
,x_attribute48 => NULL
,x_attribute49 => NULL
,x_attribute50 => NULL
,x_flex_value_meaning => p_display_name
,x_description => p_description
,x_creation_date => l_sysdate
,x_created_by => l_owner
,x_last_update_date => l_sysdate
,x_last_updated_by => l_owner
,x_last_update_login => G_CURRENT_LOGIN_ID);
ELSIF p_transaction_type = 'UPDATE' THEN
--bug 13629961
--When some Attribute has value, it is overridden with NULL value,hence getting the value from table and resetting
SELECT *
INTO l_rec_fnd_flex_values
FROM fnd_flex_values
WHERE flex_value_set_id = l_value_set_id
AND flex_value = p_internal_name;
FND_FLEX_VALUES_PKG.UPDATE_ROW
(x_flex_value_id =>l_rec_fnd_flex_values.FLEX_VALUE_ID
,x_attribute_sort_order => NULL
,x_flex_value_set_id => l_value_set_id
,x_flex_value => p_internal_name
,x_enabled_flag => p_enabled
,x_summary_flag => 'N'
,x_start_date_active => p_start_date
,x_end_date_active => p_end_date
,x_parent_flex_value_low =>l_rec_fnd_flex_values.PARENT_FLEX_VALUE_LOW
,x_parent_flex_value_high =>l_rec_fnd_flex_values.PARENT_FLEX_VALUE_HIGH
,x_structured_hierarchy_level =>l_rec_fnd_flex_values.STRUCTURED_HIERARCHY_LEVEL
,x_hierarchy_level => l_rec_fnd_flex_values.HIERARCHY_LEVEL
,x_compiled_value_attributes =>l_rec_fnd_flex_values.COMPILED_VALUE_ATTRIBUTES
,x_value_category => l_rec_fnd_flex_values.VALUE_CATEGORY
,x_attribute1 => l_rec_fnd_flex_values.ATTRIBUTE1
,x_attribute2 => l_rec_fnd_flex_values.ATTRIBUTE2
,x_attribute3 => l_rec_fnd_flex_values.ATTRIBUTE3
,x_attribute4 => l_rec_fnd_flex_values.ATTRIBUTE4
,x_attribute5 => l_rec_fnd_flex_values.ATTRIBUTE5
,x_attribute6 => l_rec_fnd_flex_values.ATTRIBUTE6
,x_attribute7 => l_rec_fnd_flex_values.ATTRIBUTE7
,x_attribute8 => l_rec_fnd_flex_values.ATTRIBUTE8
,x_attribute9 => l_rec_fnd_flex_values.ATTRIBUTE9
,x_attribute10 => l_rec_fnd_flex_values.ATTRIBUTE10
,x_attribute11 => l_rec_fnd_flex_values.ATTRIBUTE11
,x_attribute12 => l_rec_fnd_flex_values.ATTRIBUTE12
,x_attribute13 => l_rec_fnd_flex_values.ATTRIBUTE13
,x_attribute14 => l_rec_fnd_flex_values.ATTRIBUTE14
,x_attribute15 => l_rec_fnd_flex_values.ATTRIBUTE15
,x_attribute16 => l_rec_fnd_flex_values.ATTRIBUTE16
,x_attribute17 => l_rec_fnd_flex_values.ATTRIBUTE17
,x_attribute18 => l_rec_fnd_flex_values.ATTRIBUTE18
,x_attribute19 => l_rec_fnd_flex_values.ATTRIBUTE19
,x_attribute20 => l_rec_fnd_flex_values.ATTRIBUTE20
,x_attribute21 => l_rec_fnd_flex_values.ATTRIBUTE21
,x_attribute22 => l_rec_fnd_flex_values.ATTRIBUTE22
,x_attribute23 => l_rec_fnd_flex_values.ATTRIBUTE23
,x_attribute24 => l_rec_fnd_flex_values.ATTRIBUTE24
,x_attribute25 => l_rec_fnd_flex_values.ATTRIBUTE25
,x_attribute26 => l_rec_fnd_flex_values.ATTRIBUTE26
,x_attribute27 => l_rec_fnd_flex_values.ATTRIBUTE27
,x_attribute28 => l_rec_fnd_flex_values.ATTRIBUTE28
,x_attribute29 => l_rec_fnd_flex_values.ATTRIBUTE29
,x_attribute30 => l_rec_fnd_flex_values.ATTRIBUTE30
,x_attribute31 => l_rec_fnd_flex_values.ATTRIBUTE31
,x_attribute32 => l_rec_fnd_flex_values.ATTRIBUTE32
,x_attribute33 => l_rec_fnd_flex_values.ATTRIBUTE33
,x_attribute34 => l_rec_fnd_flex_values.ATTRIBUTE34
,x_attribute35 => l_rec_fnd_flex_values.ATTRIBUTE35
,x_attribute36 => l_rec_fnd_flex_values.ATTRIBUTE36
,x_attribute37 => l_rec_fnd_flex_values.ATTRIBUTE37
,x_attribute38 => l_rec_fnd_flex_values.ATTRIBUTE38
,x_attribute39 => l_rec_fnd_flex_values.ATTRIBUTE39
,x_attribute40 => l_rec_fnd_flex_values.ATTRIBUTE40
,x_attribute41 => l_rec_fnd_flex_values.ATTRIBUTE41
,x_attribute42 => l_rec_fnd_flex_values.ATTRIBUTE42
,x_attribute43 => l_rec_fnd_flex_values.ATTRIBUTE43
,x_attribute44 => l_rec_fnd_flex_values.ATTRIBUTE44
,x_attribute45 => l_rec_fnd_flex_values.ATTRIBUTE45
,x_attribute46 => l_rec_fnd_flex_values.ATTRIBUTE46
,x_attribute47 => l_rec_fnd_flex_values.ATTRIBUTE47
,x_attribute48 => l_rec_fnd_flex_values.ATTRIBUTE48
,x_attribute49 => l_rec_fnd_flex_values.ATTRIBUTE49
,x_attribute50 => l_rec_fnd_flex_values.ATTRIBUTE50
,x_flex_value_meaning => p_display_name
,x_description => p_description
,x_last_update_date => l_sysdate
,x_last_updated_by => l_owner
,x_last_update_login => G_CURRENT_LOGIN_ID);
ELSIF p_transaction_type = 'UPDATE' THEN
code_debug (l_api_name||' calling FND_FLEX_VAL_API.update_independent_vset_value ');
FND_FLEX_VAL_API.update_independent_vset_value
(p_flex_value_set_name => p_value_set_name
,p_flex_value => p_internal_name
,p_description => p_description
,p_enabled_flag => p_enabled
,p_start_date_active => p_start_date
,p_end_date_active => p_end_date
-- allow default values
-- ,p_summary_flag IN VARCHAR2 DEFAULT 'N',
-- ,p_structured_hierarchy_level IN NUMBER DEFAULT NULL,
-- ,p_hierarchy_level IN VARCHAR2 DEFAULT NULL,
,x_storage_value => l_storage_value);
code_debug (l_api_name||' returning FND_FLEX_VAL_API.update_independent_vset_value with value '||l_storage_value );
SELECT application_id , descriptive_flexfield_name ,descriptive_flex_context_code
FROM ego_fnd_dsc_flx_ctx_ext
WHERE attr_group_id = p_attr_group_id;
PROCEDURE Delete_Attribute_Internal (
p_application_id IN NUMBER
,p_attr_group_type IN VARCHAR2
,p_attr_group_name IN VARCHAR2
,p_attr_name IN VARCHAR2
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attribute_Internal';
SAVEPOINT Delete_Attribute_PRV;
SELECT APPLICATION_COLUMN_NAME
INTO l_app_col_name
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
AND END_USER_COLUMN_NAME = p_attr_name;
DELETE FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
AND END_USER_COLUMN_NAME = p_attr_name;
DELETE FROM FND_DESCR_FLEX_COL_USAGE_TL
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
AND APPLICATION_COLUMN_NAME = l_app_col_name;
DELETE FROM EGO_FND_DF_COL_USGS_EXT
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
AND APPLICATION_COLUMN_NAME = l_app_col_name;
ROLLBACK TO Delete_Attribute_PRV;
END Delete_Attribute_Internal;
PROCEDURE Delete_Attr_Group_Internal (
p_application_id IN NUMBER
,p_attr_group_type IN VARCHAR2
,p_attr_group_name IN VARCHAR2
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attr_Group_Internal';
SELECT END_USER_COLUMN_NAME
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name;
SAVEPOINT Delete_Attribute_Group_PVT;
Delete_Attribute_Internal(p_application_id, p_attr_group_type, p_attr_group_name,
attrs_rec.end_user_column_name, p_commit,
x_return_status, x_errorcode, x_msg_count, x_msg_data);
DELETE FROM FND_DESCR_FLEX_CONTEXTS
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name;
DELETE FROM FND_DESCR_FLEX_CONTEXTS_TL
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name;
DELETE FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name;
DELETE FROM EGO_ATTR_GROUP_DL
WHERE ATTR_GROUP_ID = (SELECT ATTR_GROUP_ID
FROM EGO_ATTR_GROUPS_V
WHERE APPLICATION_ID = p_application_id
AND ATTR_GROUP_TYPE = p_attr_group_type
AND ATTR_GROUP_NAME = p_attr_group_name);
ROLLBACK TO Delete_Attribute_Group_PVT;
END Delete_Attr_Group_Internal;
SELECT ASSOCIATION_ID
INTO l_association_id
FROM EGO_OBJ_AG_ASSOCS_B
WHERE OBJECT_ID = p_object_id
AND CLASSIFICATION_CODE = p_classification_code
AND ATTR_GROUP_ID = p_attr_group_id;
SELECT PAGE_ID
INTO l_page_id
FROM EGO_PAGES_B
WHERE OBJECT_ID = p_object_id
AND CLASSIFICATION_CODE = p_classification_code
AND INTERNAL_NAME = p_page_internal_name;
SELECT APPLICATION_ID, DESCRIPTIVE_FLEXFIELD_NAME, DESCRIPTIVE_FLEX_CONTEXT_CODE
INTO l_application_id, l_attr_group_type, l_attr_group_name
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE ATTR_GROUP_ID = p_attr_group_id;
SELECT COUNT(*)
INTO l_assocs_num
FROM EGO_OBJ_AG_ASSOCS_B
WHERE ATTR_GROUP_ID = l_attr_group_id
AND ENABLED_FLAG = 'Y';
SELECT COLUMN_NAME
INTO l_uom_column_name
FROM FND_COLUMNS
WHERE TABLE_ID =
(SELECT TABLE_ID FROM FND_TABLES
WHERE TABLE_NAME = p_table_name)
AND COLUMN_NAME = 'UOM_'||p_column ;
SELECT 1
INTO l_uom_column_name
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME= p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
AND END_USER_COLUMN_NAME <> p_internal_name
AND APPLICATION_COLUMN_NAME = p_uom_column_name ;
SELECT OWNER
INTO l_table_owner
FROM ALL_TABLES
WHERE TABLE_NAME = p_table_name;
SELECT APPLICATION_SHORT_NAME
INTO l_appl_name
FROM FND_APPLICATION
WHERE APPLICATION_ID = p_appl_id;
SELECT
ORACLE_USERNAME INTO l_oracleUser
FROM
FND_ORACLE_USERID
WHERE
READ_ONLY_FLAG = 'U';
SELECT CLASSIFICATION_COL_NAME
INTO l_class_code_column
FROM EGO_FND_OBJECTS_EXT
WHERE OBJECT_NAME = (SELECT OBJ_NAME FROM FND_OBJECTS
WHERE OBJECT_ID = (SELECT OBJECT_ID
FROM EGO_OBJECT_EXT_TABLES_B
WHERE EXT_TABLE_NAME = p_table_name));
SELECT EGO_DB_COL_INDEX_S.NEXTVAL INTO l_index_name FROM DUAL;
SELECT F.FUNCTION_NAME
INTO l_privilege_name
FROM FND_FORM_FUNCTIONS F
,EGO_FND_DSC_FLX_CTX_EXT E
WHERE E.ATTR_GROUP_ID = p_attr_group_id
AND E.VIEW_PRIVILEGE_ID = F.FUNCTION_ID;
SELECT F.FUNCTION_NAME
INTO l_privilege_name
FROM FND_FORM_FUNCTIONS F
,EGO_FND_DSC_FLX_CTX_EXT E
WHERE E.ATTR_GROUP_ID = p_attr_group_id
AND E.EDIT_PRIVILEGE_ID = F.FUNCTION_ID;
select col.column_name
into l_real_column_name
from dba_tables tab,
dba_tab_columns col,
dba_editioning_views ev,
dba_editioning_view_cols evc
where tab.owner = l_table_owner
and tab.table_name = l_table_name
and col.owner = tab.owner
and col.table_name = tab.table_name
and ev.owner(+) = tab.owner
and ev.table_name(+) = tab.table_name
and evc.owner(+) = ev.owner
and evc.view_name(+) = ev.view_name
and (ev.view_name is null or
evc.table_column_name = col.column_name)
and decode(ev.view_name,
NULL,
col.column_name,
evc.view_column_name) = p_column_name;
SELECT 'Y'
INTO l_index_flag
FROM ALL_IND_COLUMNS
WHERE TABLE_OWNER = l_table_owner
AND TABLE_NAME = l_table_name
AND COLUMN_NAME = l_real_column_name
AND ROWNUM < 2;
SELECT ATTR_GROUP_ID INTO l_attr_group_id
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name;
SELECT FL_CTX_TL.DESCRIPTIVE_FLEX_CONTEXT_NAME INTO l_attr_group_name
FROM FND_DESCR_FLEX_CONTEXTS_TL FL_CTX_TL,
EGO_FND_DSC_FLX_CTX_EXT FL_CTX_EXT
WHERE
FL_CTX_EXT.APPLICATION_ID = FL_CTX_TL.APPLICATION_ID
AND FL_CTX_EXT.DESCRIPTIVE_FLEXFIELD_NAME = FL_CTX_TL.DESCRIPTIVE_FLEXFIELD_NAME
AND FL_CTX_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_CTX_TL.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND FL_CTX_EXT.ATTR_GROUP_ID = p_attr_group_id
AND FL_CTX_TL.LANGUAGE = userenv('LANG');
SELECT meaning into l_p_data_level_disp_name
FROM fnd_lookup_values
WHERE lookup_type = 'EGO_EF_DATA_LEVEL'
AND language = userenv('LANG')
AND lookup_code = p_data_level;
SELECT DISTINCT DATA_LEVEL
INTO l_data_level
FROM EGO_OBJ_AG_ASSOCS_B
WHERE ATTR_GROUP_ID = p_attr_group_id
AND OBJECT_ID = p_object_id
AND ROWNUM < 2;
l_column_deleted BOOLEAN;
SELECT COLUMN_NAME
FROM FND_COLUMNS
WHERE APPLICATION_ID = cp_application_id
AND TABLE_ID = cp_table_id
AND COLUMN_TYPE = cp_column_type
AND COLUMN_NAME LIKE cp_column_prefix
ORDER BY COLUMN_SEQUENCE;
SELECT FC.COLUMN_NAME
FROM FND_COLUMNS FC
,EGO_FND_DF_COL_USGS_EXT EXT
WHERE EXT.APPLICATION_ID = cp_application_id
AND EXT.DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
AND EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name
AND EXT.DATA_TYPE = cp_data_type
AND FC.APPLICATION_ID = cp_application_id
AND FC.TABLE_ID = cp_table_id
AND FC.COLUMN_TYPE = cp_column_type
AND EXT.APPLICATION_COLUMN_NAME = FC.COLUMN_NAME
ORDER BY FC.COLUMN_SEQUENCE;
SELECT FT.TABLE_ID
INTO l_ext_table_id
FROM FND_TABLES FT
,EGO_ATTR_GROUP_TYPES_V EAGTV
WHERE FT.TABLE_NAME = EAGTV.EXT_TABLE_NAME
AND EAGTV.APPLICATION_ID = p_application_id
AND EAGTV.ATTR_GROUP_TYPE = p_attr_group_type;
l_column_deleted := FALSE;
EXIT WHEN (l_column_deleted);
x_database_columns.DELETE(l_db_cols_table_index);
l_column_deleted := TRUE;
SELECT APPLICATION_TABLE_NAME
INTO l_table_name
FROM FND_DESCRIPTIVE_FLEXS
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type;
SELECT EXT_TL_TABLE_NAME
INTO l_table_name
FROM EGO_ATTR_GROUP_TYPES_V
WHERE APPLICATION_ID = p_application_id
AND ATTR_GROUP_TYPE = p_attr_group_type;
SELECT OBJECT_ID INTO l_object_id
FROM FND_OBJECTS
WHERE OBJ_NAME = p_object_name;
SELECT OBJ.OBJECT_ID
INTO l_object_id
FROM EGO_OBJECT_EXT_TABLES_B OBJ
,FND_DESCRIPTIVE_FLEXS FLX
WHERE OBJ.EXT_TABLE_NAME = FLX.APPLICATION_TABLE_NAME
AND OBJ.APPLICATION_ID = FLX.APPLICATION_ID
AND FLX.APPLICATION_ID = p_application_id
AND FLX.DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type;
SELECT EXT_ATTR_OCV_NAME INTO l_ocv_name
FROM EGO_FND_OBJECTS_EXT
WHERE OBJECT_NAME = p_object_name;
l_dynamic_sql := 'SELECT MEANING FROM ' || l_ocv_name ||
' WHERE CODE = :1 AND LANGUAGE = USERENV(''LANG'') ' ||
' AND ROWNUM = 1 ';
SELECT OBJ_NAME INTO l_object_name
FROM FND_OBJECTS
WHERE OBJECT_ID = p_object_id;
SELECT PK1_COLUMN_NAME, PK1_COLUMN_TYPE,
PK2_COLUMN_NAME, PK2_COLUMN_TYPE,
PK3_COLUMN_NAME, PK3_COLUMN_TYPE,
PK4_COLUMN_NAME, PK4_COLUMN_TYPE,
PK5_COLUMN_NAME, PK5_COLUMN_TYPE
FROM FND_OBJECTS
WHERE OBJ_NAME = cp_obj_name;
UPDATE FND_DESCR_FLEX_CONTEXTS
SET LAST_UPDATED_BY = NVL(p_owner, g_current_user_id),
LAST_UPDATE_DATE = p_lud,
LAST_UPDATE_LOGIN = g_current_login_id
WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_internal_name
AND APPLICATION_ID = p_application_id;
UPDATE FND_DESCR_FLEX_CONTEXTS_TL
SET DESCRIPTION = p_attr_group_desc,
DESCRIPTIVE_FLEX_CONTEXT_NAME = p_display_name,
LAST_UPDATE_DATE = p_lud,
LAST_UPDATED_BY = NVL(p_owner, g_current_user_id),
LAST_UPDATE_LOGIN = g_current_login_id,
SOURCE_LANG = USERENV('LANG')
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_internal_name
AND USERENV('LANG') in (LANGUAGE , SOURCE_LANG);
SELECT APPLICATION_SHORT_NAME
INTO l_appl_short_name
FROM FND_APPLICATION
WHERE APPLICATION_ID = p_application_id;
SELECT EGO_ATTR_GROUPS_S.NEXTVAL INTO x_attr_group_id FROM DUAL;
INSERT INTO EGO_FND_DSC_FLX_CTX_EXT
(
ATTR_GROUP_ID
,APPLICATION_ID
,DESCRIPTIVE_FLEXFIELD_NAME
,DESCRIPTIVE_FLEX_CONTEXT_CODE
,MULTI_ROW
,VARIANT --VARIANT(R12C)
,NUM_OF_COLS
,NUM_OF_ROWS
,SECURITY_TYPE
,OWNING_PARTY_ID
,REGION_CODE
,VIEW_PRIVILEGE_ID
,EDIT_PRIVILEGE_ID
,BUSINESS_EVENT_FLAG
,PRE_BUSINESS_EVENT_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
)
VALUES
(
x_attr_group_id --ATTR_GROUP_ID
,p_application_id --APPLICATION_ID
,p_attr_group_type --DESCRIPTIVE_FLEXFIELD_NAME
,p_internal_name --DESCRIPTIVE_FLEX_CONTEXT_CODE
,nvl(p_multi_row_attrib_group, 'N') --MULTI_ROW
,p_variant_attrib_group --VARIANT
,l_num_of_cols
,l_num_of_rows
,nvl(p_security_type, 'P') --SECURITY_TYPE
,-100 --p_owning_company_id --OWNING_PARTY_ID
,p_region_code --p_region_code
,p_view_privilege_id -- View privilege
,p_edit_privilege_id -- Edit privilege
,p_business_event_flag --BUSINESS_EVENT_FLAG
,p_pre_business_event_flag --PRE_BUSINESS_EVENT_FLAG
,NVL(p_owner, g_current_user_id) --CREATED_BY
,p_lud --CREATION_DATE
,NVL(p_owner, g_current_user_id) --LAST_UPDATED_BY
,p_lud --LAST_UPDATE_DATE
,g_current_login_id --LAST_UPDATE_LOGIN
);
SELECT COUNT(*) into l_partition_count
FROM ALL_TAB_PARTITIONS
WHERE
table_name = l_ext_table_name
and table_owner = l_table_owner;
SELECT COUNT(*) into l_partition_count
FROM ALL_TAB_PARTITIONS
WHERE
table_name = l_ext_table_name
and table_owner = l_table_owner;
SELECT COUNT(*)
INTO l_partition_count
FROM ALL_TAB_PARTITIONS
WHERE table_name = l_attr_chg_b_table
AND table_owner = l_table_owner;
SELECT COUNT(*)
INTO l_partition_count
FROM ALL_TAB_PARTITIONS
WHERE table_name = l_attr_chg_tl_table
AND table_owner = l_table_owner;
SELECT MESSAGE_TEXT INTO x_msg_data
FROM FND_NEW_MESSAGES
WHERE MESSAGE_NAME = 'EGO_INTERNAL_NAME_EXISTS'
AND LANGUAGE_CODE = USERENV('LANG');
SELECT APPLICATION_ID,
ATTR_GROUP_TYPE,
ATTR_GROUP_NAME,
ATTR_NAME,
ATTR_DISPLAY_NAME,
DESCRIPTION,
DATA_TYPE_CODE,
SEQUENCE,
UNIQUE_KEY_FLAG,
DEFAULT_VALUE,
INFO_1,
VALUE_SET_ID,
ENABLED_FLAG,
REQUIRED_FLAG,
SEARCH_FLAG,
DISPLAY_CODE,
DATABASE_COLUMN,
UOM_CLASS,
DECODE(DISPLAY_CODE,'D',1,0) DISP_CODE --bugFix:5589398
FROM EGO_ATTRS_V
WHERE APPLICATION_ID = v_source_ag_app_id
AND ATTR_GROUP_TYPE = v_source_ag_type
AND ATTR_GROUP_NAME = v_source_ag_name
ORDER BY DISP_CODE, SEQUENCE;
PROCEDURE Update_Attribute_Group (
p_api_version IN NUMBER
,p_attr_group_id IN NUMBER
,p_display_name IN VARCHAR2
,p_attr_group_desc IN VARCHAR2
,p_security_type IN VARCHAR2
,p_multi_row_attrib_group IN VARCHAR2
,p_variant_attrib_group IN VARCHAR2
,p_num_of_cols IN NUMBER DEFAULT NULL
,p_num_of_rows IN NUMBER DEFAULT NULL
,p_owning_company_id IN NUMBER
,p_region_code IN VARCHAR2 DEFAULT NULL
,p_view_privilege_id IN NUMBER DEFAULT NULL
,p_edit_privilege_id IN NUMBER DEFAULT NULL
,p_business_event_flag IN VARCHAR2 DEFAULT NULL
,p_pre_business_event_flag IN VARCHAR2 DEFAULT NULL
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Attribute_Group';
SAVEPOINT Update_Attribute_Group_PUB;
EGO_EXT_FWK_PUB.Update_Attribute_Group(
p_api_version => p_api_version
,p_application_id => l_attr_group_pks(1)
,p_attr_group_type => l_attr_group_pks(2)
,p_internal_name => l_attr_group_pks(3)
,p_display_name => p_display_name
,p_attr_group_desc => p_attr_group_desc
,p_security_type => p_security_type
,p_multi_row_attrib_group => p_multi_row_attrib_group
,p_variant_attrib_group => p_variant_attrib_group
,p_num_of_cols => p_num_of_cols
,p_num_of_rows => p_num_of_rows
,p_owning_company_id => p_owning_company_id
,p_region_code => p_region_code
,p_view_privilege_id => p_view_privilege_id
,p_edit_privilege_id => p_edit_privilege_id
,p_business_event_flag => p_business_event_flag
,p_pre_business_event_flag => p_pre_business_event_flag
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO Update_Attribute_Group_PUB;
END Update_Attribute_Group;
PROCEDURE Update_Attribute_Group (
p_api_version IN NUMBER
,p_application_id IN NUMBER
,p_attr_group_type IN VARCHAR2
,p_internal_name IN VARCHAR2
,p_display_name IN VARCHAR2
,p_attr_group_desc IN VARCHAR2
,p_security_type IN VARCHAR2
,p_multi_row_attrib_group IN VARCHAR2
,p_variant_attrib_group IN VARCHAR2
,p_num_of_cols IN NUMBER DEFAULT NULL
,p_num_of_rows IN NUMBER DEFAULT NULL
,p_owning_company_id IN NUMBER
,p_region_code IN VARCHAR2 DEFAULT NULL
,p_view_privilege_id IN NUMBER DEFAULT NULL
,p_edit_privilege_id IN NUMBER DEFAULT NULL
,p_business_event_flag IN VARCHAR2 DEFAULT NULL
,p_pre_business_event_flag IN VARCHAR2 DEFAULT NULL
,p_owner IN NUMBER DEFAULT NULL
,p_lud IN DATE DEFAULT SYSDATE
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,p_is_nls_mode IN VARCHAR2 DEFAULT FND_API.G_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Attribute_Group';
SAVEPOINT Update_Attribute_Group_PUB;
UPDATE FND_DESCR_FLEX_CONTEXTS
SET LAST_UPDATED_BY = NVL(p_owner, g_current_user_id),
LAST_UPDATE_DATE = p_lud,
LAST_UPDATE_LOGIN = g_current_login_id
WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_internal_name
AND APPLICATION_ID = p_application_id;
UPDATE EGO_FND_DSC_FLX_CTX_EXT
SET MULTI_ROW = nvl(p_multi_row_attrib_group, MULTI_ROW),
VARIANT = nvl(p_variant_attrib_group, VARIANT),
NUM_OF_COLS = l_num_of_cols,
NUM_OF_ROWS = l_num_of_rows,
SECURITY_TYPE = nvl(p_security_type, SECURITY_TYPE),
REGION_CODE = p_region_code,
VIEW_PRIVILEGE_ID = p_view_privilege_id,
EDIT_PRIVILEGE_ID = p_edit_privilege_id,
BUSINESS_EVENT_FLAG = p_business_event_flag,
PRE_BUSINESS_EVENT_FLAG = p_pre_business_event_flag,
LAST_UPDATED_BY = NVL(p_owner, g_current_user_id),
LAST_UPDATE_DATE = p_lud,
LAST_UPDATE_LOGIN = g_current_login_id
WHERE DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_internal_name
AND APPLICATION_ID = p_application_id;
UPDATE FND_DESCR_FLEX_CONTEXTS_TL
SET DESCRIPTION = p_attr_group_desc,
DESCRIPTIVE_FLEX_CONTEXT_NAME = p_display_name,
LAST_UPDATE_DATE = p_lud,
LAST_UPDATED_BY = NVL(p_owner, g_current_user_id),
LAST_UPDATE_LOGIN = g_current_login_id,
SOURCE_LANG = USERENV('LANG')
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_internal_name
-- AND LANGUAGE = USERENV('LANG');
ROLLBACK TO Update_Attribute_Group_PUB;
END Update_Attribute_Group;
PROCEDURE Delete_Attribute_Group (
p_api_version IN NUMBER
,p_attr_group_id IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attribute_Group';
SAVEPOINT Delete_Attribute_Group_PUB;
EGO_EXT_FWK_PUB.Delete_Attribute_Group(
p_api_version => p_api_version
,p_application_id => l_attr_group_pks(1)
,p_attr_group_type => l_attr_group_pks(2)
,p_attr_group_name => l_attr_group_pks(3)
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO Delete_Attribute_Group_PUB;
END Delete_Attribute_Group;
PROCEDURE Delete_Attribute_Group (
p_api_version IN NUMBER
,p_application_id IN NUMBER
,p_attr_group_type IN VARCHAR2
,p_attr_group_name IN VARCHAR2
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attribute_Group';
SAVEPOINT Delete_Attribute_Group_PUB;
SELECT DESCRIPTIVE_FLEX_CONTEXT_NAME
INTO l_attr_group_disp_name
FROM FND_DESCR_FLEX_CONTEXTS_TL
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
AND LANGUAGE = USERENV('LANG');
Delete_Attr_Group_Internal(p_application_id
,p_attr_group_type
,p_attr_group_name
,p_commit
,x_return_status
,x_errorcode
,x_msg_count
,x_msg_data);
ROLLBACK TO Delete_Attribute_Group_PUB;
END Delete_Attribute_Group;
SELECT application_table_name
INTO l_ext_table_name
FROM fnd_descriptive_flexs
WHERE application_id = l_attr_group_metadata_obj.application_id
AND descriptive_flexfield_name = l_attr_group_metadata_obj.attr_group_type;
||'/NVL((SELECT CONVERSION_RATE FROM MTL_UOM_CONVERSIONS '
||' WHERE UOM_CLASS = '''||l_curr_attr_metadata_obj.UNIT_OF_MEASURE_CLASS||''' '
||' AND UOM_CODE = UOM_' || SUBSTR(l_curr_attr_metadata_obj.DATABASE_COLUMN,INSTR(l_curr_attr_metadata_obj.DATABASE_COLUMN,'EXT_ATTR'))
||' AND ROWNUM = 1),1) '
||SUBSTRB(l_curr_attr_metadata_obj.ATTR_NAME, 1, 24)||'_UVAL , ';
' AS SELECT EXTENSION_ID, '||l_pk_col_names||l_dl_col_names||l_aliased_attr_names||
' FROM '||l_attr_group_metadata_obj.EXT_TABLE_VL_NAME ||
' WHERE attr_group_id = ' || p_attr_group_id ;
SELECT USGS.APPLICATION_ID,
USGS.DESCRIPTIVE_FLEXFIELD_NAME,
USGS.DESCRIPTIVE_FLEX_CONTEXT_CODE,
USGS.END_USER_COLUMN_NAME,
USGS.APPLICATION_COLUMN_NAME
INTO
l_application_id
,l_attr_group_type
,l_attr_group_name
,l_attr_name
,l_curr_db_column
FROM
FND_DESCR_FLEX_COLUMN_USAGES USGS,
EGO_FND_DF_COL_USGS_EXT EXT
WHERE
USGS.APPLICATION_ID = EXT.APPLICATION_ID
AND USGS.DESCRIPTIVE_FLEXFIELD_NAME = EXT.DESCRIPTIVE_FLEXFIELD_NAME
AND USGS.DESCRIPTIVE_FLEX_CONTEXT_CODE = EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND USGS.APPLICATION_COLUMN_NAME = EXT.APPLICATION_COLUMN_NAME
AND EXT.ATTR_ID = p_attr_id;
SELECT APPLICATION_COLUMN_NAME
INTO l_curr_db_column
FROM FND_DESCR_FLEX_COLUMN_USAGES EXT
WHERE
EXT.APPLICATION_ID = l_application_id
AND EXT.DESCRIPTIVE_FLEXFIELD_NAME = l_attr_group_type
AND EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = l_attr_group_name
AND EXT.END_USER_COLUMN_NAME = l_attr_name;
SELECT EXT.ATTR_GROUP_ID
,NVL(FLX_EXT.APPLICATION_VL_NAME, FLX.APPLICATION_TABLE_NAME)
INTO l_attr_group_id
,l_ext_table_vl_name
FROM EGO_OBJECT_EXT_TABLES_B OBJ
,EGO_FND_DSC_FLX_CTX_EXT EXT
,FND_DESCRIPTIVE_FLEXS FLX
,EGO_FND_DESC_FLEXS_EXT FLX_EXT
WHERE OBJ.EXT_TABLE_NAME = FLX.APPLICATION_TABLE_NAME
AND FLX.APPLICATION_ID = l_application_id
AND FLX.APPLICATION_ID = FLX_EXT.APPLICATION_ID(+)
AND FLX.APPLICATION_ID = EXT.APPLICATION_ID
AND FLX.DESCRIPTIVE_FLEXFIELD_NAME = l_attr_group_type
AND FLX.DESCRIPTIVE_FLEXFIELD_NAME = FLX_EXT.DESCRIPTIVE_FLEXFIELD_NAME(+)
AND FLX.DESCRIPTIVE_FLEXFIELD_NAME = EXT.DESCRIPTIVE_FLEXFIELD_NAME
AND EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = l_attr_group_name;
l_dynamic_sql := ' SELECT 1 ' ||
' FROM DUAL ' ||
' WHERE EXISTS (SELECT 1 FROM ' || l_ext_table_vl_name ||
' WHERE ATTR_GROUP_ID = :1' ||
' AND ROWNUM = 1 ' ||
' AND ' || l_curr_db_column || ' IS NOT NULL) ';
SELECT OBJ.OBJECT_ID
,EXT.ATTR_GROUP_ID
,NVL(FLX_EXT.APPLICATION_VL_NAME, FLX.APPLICATION_TABLE_NAME)
,DECODE(FLX_EXT.APPLICATION_TL_TABLE_NAME, NULL, 'N', 'Y')
INTO l_object_id
,l_attr_group_id
,l_ext_table_vl_name
,l_tl_table_exists
FROM EGO_OBJECT_EXT_TABLES_B OBJ
,EGO_FND_DSC_FLX_CTX_EXT EXT
,FND_DESCRIPTIVE_FLEXS FLX
,EGO_FND_DESC_FLEXS_EXT FLX_EXT
WHERE OBJ.EXT_TABLE_NAME = FLX.APPLICATION_TABLE_NAME
AND FLX.APPLICATION_ID = p_application_id
AND FLX.APPLICATION_ID = FLX_EXT.APPLICATION_ID(+)
AND FLX.APPLICATION_ID = EXT.APPLICATION_ID
AND FLX.DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND FLX.DESCRIPTIVE_FLEXFIELD_NAME = FLX_EXT.DESCRIPTIVE_FLEXFIELD_NAME(+)
AND FLX.DESCRIPTIVE_FLEXFIELD_NAME = EXT.DESCRIPTIVE_FLEXFIELD_NAME
AND EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name;
l_dynamic_sql := ' SELECT APPLICATION_COLUMN_NAME' ||
' FROM EGO_FND_DF_COL_USGS_EXT' ||
' WHERE ATTR_ID IN ('||p_id_list||') ';
l_dynamic_sql := ' SELECT COUNT(1)' ||
' FROM ' || l_ext_table_vl_name ||
' WHERE ATTR_GROUP_ID = :1' ||
' GROUP BY ' || l_column_list;
,p_last_updated_by IN VARCHAR2
,p_last_update_date IN DATE
,p_is_nls_mode IN VARCHAR2
,x_data_level_id IN OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_version NUMBER;
SELECT NVL2(pre_business_event_name,NVL(p_enable_pre_event,'N'),'N'),
NVL2(business_event_name,NVL(p_enable_post_event,'N'),'N')
INTO l_enable_pre_event, l_enable_post_event
FROM EGO_FND_DESC_FLEXS_EXT
WHERE application_id = p_application_id
AND descriptive_flexfield_name = p_attr_group_type;
SELECT max(data_level_id)
INTO l_data_level_id
FROM ego_data_level_b
WHERE application_id = p_application_id;
code_debug(l_api_name ||' Data level id being used in insert: '||l_data_level_id);
INSERT INTO EGO_DATA_LEVEL_B
(data_level_id
,application_id
,attr_group_type
,data_level_name
,pk1_column_name
,pk1_column_type
,pk2_column_name
,pk2_column_type
,pk3_column_name
,pk3_column_type
,pk4_column_name
,pk4_column_type
,pk5_column_name
,pk5_column_type
,enable_defaulting
,enable_view_priv
,enable_edit_priv
,enable_pre_event
,enable_post_event
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
values
(l_data_level_id
,p_application_id
,p_attr_group_type
,p_data_level_name
,p_pk1_column_name
,p_pk1_column_type
,p_pk2_column_name
,p_pk2_column_type
,p_pk3_column_name
,p_pk3_column_type
,p_pk4_column_name
,p_pk4_column_type
,p_pk5_column_name
,p_pk5_column_type
,p_enable_defaulting
,p_enable_view_priv
,p_enable_edit_priv
,l_enable_pre_event
,l_enable_post_event
,NVL(p_last_updated_by, G_CURRENT_USER_ID)
,NVL(p_last_update_date, SYSDATE)
,NVL(p_last_updated_by, G_CURRENT_USER_ID)
,NVL(p_last_update_date, SYSDATE)
,G_CURRENT_LOGIN_ID
);
INSERT INTO EGO_DATA_LEVEL_TL
(data_level_id
,user_data_level_name
,language
,source_lang
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
SELECT
l_data_level_id
,NVL(p_user_data_level_name, p_data_level_name)
,l.language_code
,USERENV('LANG')
,NVL(p_last_updated_by, G_CURRENT_USER_ID)
,NVL(p_last_update_date, SYSDATE)
,NVL(p_last_updated_by, G_CURRENT_USER_ID)
,NVL(p_last_update_date, SYSDATE)
,G_CURRENT_LOGIN_ID
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B');
ELSIF p_transaction_type = 'UPDATE' THEN
SELECT data_level_id
INTO l_data_level_id
FROM ego_data_level_b
WHERE application_id = p_application_id
AND attr_group_type = p_attr_group_type
AND data_level_name = p_data_level_name;
code_debug(l_api_name ||' Data level id being used in update: '||l_data_level_id);
UPDATE EGO_DATA_LEVEL_TL
SET user_data_level_name = NVL(p_user_data_level_name, p_data_level_name)
,source_lang = userenv('LANG') -- Bug 6964013
,last_updated_by = NVL(p_last_updated_by, G_CURRENT_USER_ID)
,last_update_date = NVL(p_last_update_date, SYSDATE)
,last_update_login = G_CURRENT_LOGIN_ID
WHERE data_level_id = l_data_level_id
AND USERENV('LANG') in (LANGUAGE , SOURCE_LANG);
UPDATE EGO_DATA_LEVEL_B
SET pk1_column_name = p_pk1_column_name
,pk1_column_type = p_pk1_column_type
,pk2_column_name = p_pk2_column_name
,pk2_column_type = p_pk2_column_type
,pk3_column_name = p_pk3_column_name
,pk3_column_type = p_pk3_column_type
,pk4_column_name = p_pk4_column_name
,pk4_column_type = p_pk4_column_type
,pk5_column_name = p_pk5_column_name
,pk5_column_type = p_pk5_column_type
,enable_defaulting = p_enable_defaulting
,enable_view_priv = p_enable_view_priv
,enable_edit_priv = p_enable_edit_priv
,enable_pre_event = l_enable_pre_event
,enable_post_event = l_enable_post_event
,last_updated_by = NVL(p_last_updated_by, G_CURRENT_USER_ID)
,last_update_date = NVL(p_last_update_date, SYSDATE)
,last_update_login = G_CURRENT_LOGIN_ID
WHERE data_level_id = l_data_level_id;
,p_last_updated_by IN VARCHAR2
,p_last_update_date IN DATE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_version NUMBER;
SELECT attr_group_id
INTO l_attr_group_id
FROM ego_fnd_dsc_flx_ctx_ext
WHERE application_id = p_application_id
AND descriptive_flexfield_name = p_attr_group_type
AND descriptive_flex_context_code = p_attr_group_name;
SELECT attr_group_id
INTO l_attr_group_id
FROM ego_fnd_dsc_flx_ctx_ext
WHERE attr_group_id = p_attr_group_id;
SELECT *
INTO l_data_level_rec
FROM ego_data_level_b
WHERE data_level_name = p_data_level_name
AND attr_group_type = p_attr_group_type
AND application_id = p_application_id;
SELECT *
INTO l_data_level_rec
FROM ego_data_level_b
WHERE 1=1 --data_level_name = p_data_level_id;
SELECT lookup_code
INTO l_defaulting
FROM fnd_lookup_values
WHERE lookup_type = 'EGO_EF_AG_DL_BEHAVIOR'
AND lookup_code = p_defaulting
AND language = USERENV('LANG');
SELECT lookup_code
INTO l_defaulting
FROM fnd_lookup_values
WHERE lookup_type = 'EGO_EF_AG_DL_BEHAVIOR'
AND meaning = p_defaulting_name
AND language = USERENV('LANG');
SELECT function_id
INTO l_view_priv_id
FROM fnd_form_functions
WHERE function_id = p_view_priv_id;
SELECT function_id
INTO l_view_priv_id
FROM fnd_form_functions
WHERE function_name = p_view_priv_name;
SELECT function_id
INTO l_view_priv_id
FROM fnd_form_functions_vl
WHERE user_function_name = p_user_view_priv_name;
SELECT function_id
INTO l_edit_priv_id
FROM fnd_form_functions
WHERE function_id = p_edit_priv_id;
SELECT function_id
INTO l_edit_priv_id
FROM fnd_form_functions
WHERE function_name = p_edit_priv_name;
SELECT function_id
INTO l_edit_priv_id
FROM fnd_form_functions_vl
WHERE user_function_name = p_user_edit_priv_name;
INSERT INTO EGO_ATTR_GROUP_DL
(attr_group_id
,data_level_id
,defaulting
,view_privilege_id
,edit_privilege_id
,raise_pre_event
,raise_post_event
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
VALUES
(l_attr_group_id
,l_data_level_rec.data_level_id
,l_defaulting
,l_view_priv_id
,l_edit_priv_id
,l_raise_pre_event
,l_raise_post_event
,NVL(p_last_updated_by, G_CURRENT_USER_ID)
,NVL(p_last_update_date, SYSDATE)
,NVL(p_last_updated_by, G_CURRENT_USER_ID)
,NVL(p_last_update_date, SYSDATE)
,G_CURRENT_LOGIN_ID
);
UPDATE EGO_ATTR_GROUP_DL
SET defaulting = l_defaulting
,view_privilege_id = l_view_priv_id
,edit_privilege_id = l_edit_priv_id
,raise_pre_event = l_raise_pre_event
,raise_post_event = l_raise_post_event
,last_updated_by = NVL(p_last_updated_by, G_CURRENT_USER_ID)
,last_update_date = NVL(p_last_update_date, SYSDATE)
,last_update_login = G_CURRENT_LOGIN_ID
where attr_group_id = l_attr_group_id and data_level_id =l_data_level_rec.data_level_id;
SELECT COUNT(*)
INTO l_sequence
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_Code = p_attr_group_name
AND COLUMN_SEQ_NUM = p_sequence;
SELECT MAX(COLUMN_SEQ_NUM)
INTO l_sequence
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_Code = p_attr_group_name;
EXECUTE IMMEDIATE 'SELECT NULL AS ' || p_internal_name || ' FROM DUAL';
SELECT MULTI_ROW
INTO l_multi_row_flag
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_Code = p_attr_group_name;
SELECT MIN(COLUMN_SEQ_NUM)
INTO l_min_seq
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_Code = p_attr_group_name;
SELECT COLUMN_TYPE , WIDTH
INTO l_col_data_type, l_col_width
FROM FND_COLUMNS
WHERE COLUMN_NAME = p_column
AND TABLE_ID = (SELECT TABLE_ID
FROM FND_TABLES
WHERE TABLE_NAME = l_table_name);
SELECT FORMAT_TYPE
INTO l_value_set_format_code
FROM FND_FLEX_VALUE_SETS
WHERE FLEX_VALUE_SET_ID = p_value_set_id;
SELECT LOOKUP_CODE
INTO l_is_column_indexed
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'YES_NO'
AND LANGUAGE = USERENV('LANG')
AND VIEW_APPLICATION_ID = 0
AND MEANING = p_is_column_indexed;
SELECT LOOKUP_CODE
INTO l_is_chg_column_indexed
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'YES_NO'
AND LANGUAGE = USERENV('LANG')
AND VIEW_APPLICATION_ID = 0
AND MEANING = p_is_column_indexed;
SELECT flex_value_set_id
INTO l_value_set_id
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_Code = p_attr_group_name
AND END_USER_COLUMN_NAME = p_internal_name;
UPDATE FND_DESCR_FLEX_COLUMN_USAGES
SET LAST_UPDATED_BY = NVL(p_owner, g_current_user_id),
LAST_UPDATE_DATE = p_lud,
LAST_UPDATE_LOGIN = g_current_login_id,
COLUMN_SEQ_NUM = NVL(l_sequence, COLUMN_SEQ_NUM),
ENABLED_FLAG = NVL(p_enabled, ENABLED_FLAG),
REQUIRED_FLAG = NVL(p_required, REQUIRED_FLAG),
DISPLAY_FLAG = NVL(p_display, DISPLAY_FLAG),
FLEX_VALUE_SET_ID = l_value_set_id,
DEFAULT_VALUE = p_default_value
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
AND APPLICATION_COLUMN_NAME = p_column;
UPDATE FND_DESCR_FLEX_COL_USAGE_TL
SET LAST_UPDATED_BY = NVL(p_owner, g_current_user_id),
LAST_UPDATE_DATE = p_lud,
LAST_UPDATE_LOGIN = g_current_login_id,
FORM_LEFT_PROMPT = p_display_name,
FORM_ABOVE_PROMPT = p_display_name,
DESCRIPTION = p_description,
SOURCE_LANG = USERENV('LANG')
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
AND APPLICATION_COLUMN_NAME = p_column
--AND LANGUAGE = USERENV('LANG');
INSERT INTO FND_DESCR_FLEX_COLUMN_USAGES
(
APPLICATION_ID
,DESCRIPTIVE_FLEXFIELD_NAME
,DESCRIPTIVE_FLEX_CONTEXT_CODE
,APPLICATION_COLUMN_NAME
,END_USER_COLUMN_NAME
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,COLUMN_SEQ_NUM
,ENABLED_FLAG
,REQUIRED_FLAG
,SECURITY_ENABLED_FLAG
,DISPLAY_FLAG
,DISPLAY_SIZE
,MAXIMUM_DESCRIPTION_LEN
,CONCATENATION_DESCRIPTION_LEN
,FLEX_VALUE_SET_ID
,RANGE_CODE
,DEFAULT_TYPE
,DEFAULT_VALUE
,SRW_PARAM
,RUNTIME_PROPERTY_FUNCTION
)
VALUES
(
p_application_id --APPLICATION_ID
,p_attr_group_type --DESCRIPTIVE_FLEXFIELD_NAME
,p_attr_group_name --DESCRIPTIVE_FLEX_CONTEXT_CODE
,p_column --APPLICATION_COLUMN_NAME
,p_internal_name --END_USER_COLUMN_NAME
,NVL(p_owner, g_current_user_id) --CREATED_BY
,p_lud --CREATION_DATE
,NVL(p_owner, g_current_user_id) --LAST_UPDATED_BY
,p_lud --LAST_UPDATE_DATE
,g_current_login_id --LAST_UPDATE_LOGIN
,l_sequence --COLUMN_SEQ_NUM
,l_enabled --ENABLED_FLAG
,l_required --REQUIRED_FLAG
,'N' --SECURITY_ENABLED_FLAG
,l_display --DISPLAY_FLAG
,50 --DISPLAY_SIZE
,50 --MAXIMUM_DESCRIPTION_LEN
,25 --CONCATENATION_DESCRIPTION_LEN
,p_value_set_id --FLEX_VALUE_SET_ID
,'' --RANGE_CODE
,'' --DEFAULT_TYPE
,p_default_value --DEFAULT_VALUE
,'' --SRW_PARAM
,'' --RUNTIME_PROPERTY_FUNCTION
);
INSERT INTO FND_DESCR_FLEX_COL_USAGE_TL
(
APPLICATION_ID
,DESCRIPTIVE_FLEXFIELD_NAME
,DESCRIPTIVE_FLEX_CONTEXT_CODE
,APPLICATION_COLUMN_NAME
,LANGUAGE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,FORM_LEFT_PROMPT
,FORM_ABOVE_PROMPT
,DESCRIPTION
,SOURCE_LANG
)
SELECT
p_application_id --APPLICATION_ID
,p_attr_group_type --DESCRIPTIVE_FLEXFIELD_NAME
,p_attr_group_name --DESCRIPTIVE_FLEX_CONTEXT_CODE
,p_column --APPLICATION_COLUMN_NAME
,L.LANGUAGE_CODE --LANGUAGE
,NVL(p_owner, g_current_user_id) --CREATED_BY
,p_lud --CREATION_DATE
,NVL(p_owner, g_current_user_id) --LAST_UPDATED_BY
,p_lud --LAST_UPDATE_DATE
,g_current_login_id --LAST_UPDATE_LOGIN
,p_display_name --FORM_LEFT_PROMPT
,p_display_name --FORM_ABOVE_PROMPT
,p_description --DESCRIPTION
,USERENV('LANG') --SOURCE_LANG
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B');
INSERT INTO EGO_FND_DF_COL_USGS_EXT
(
ATTR_ID
,APPLICATION_ID
,DESCRIPTIVE_FLEXFIELD_NAME
,DESCRIPTIVE_FLEX_CONTEXT_CODE
,APPLICATION_COLUMN_NAME
,DATA_TYPE
,SEARCH_FLAG
,UNIQUE_KEY_FLAG
,INFO_1
,UOM_CLASS
,CONTROL_LEVEL
,ATTRIBUTE_CODE
,VIEW_IN_HIERARCHY_CODE
,EDIT_IN_HIERARCHY_CODE
,CUSTOMIZATION_LEVEL
,READ_ONLY_FLAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
)
SELECT
EGO_ATTRS_S.NEXTVAL -- ATTR_ID
,p_application_id -- APPLICATION_ID
,p_attr_group_type -- DESCRIPTIVE_FLEXFIELD_NAME
,p_attr_group_name -- DESCRIPTIVE_FLEX_CONTEXT_CODE
,p_column -- APPLICATION_COLUMN_NAME
,p_data_type -- DATA_TYPE
,l_searchable -- SEARCHABLE FLAG
,l_unique_key_flag -- UNIQUE_KEY_FLAG
,p_info_1 -- DYNAMIC URL (IF APPLICABLE; USUALLY NULL)
,NVL(p_owner, g_current_user_id) -- LAST_UPDATED_BY
,p_lud -- LAST_UPDATE_DATE
,g_current_login_id -- LAST_UPDATE_LOGIN
FROM DUAL;
SELECT DESCRIPTIVE_FLEX_CONTEXT_NAME
INTO l_attr_group_disp_name
FROM FND_DESCR_FLEX_CONTEXTS_TL
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
AND LANGUAGE = USERENV('LANG');
PROCEDURE Update_Attribute (
p_api_version IN NUMBER
,p_application_id IN NUMBER
,p_attr_group_type IN VARCHAR2
,p_attr_group_name IN VARCHAR2
,p_internal_name IN VARCHAR2
,p_display_name IN VARCHAR2
,p_description IN VARCHAR2
,p_sequence IN NUMBER
,p_required IN VARCHAR2
,p_searchable IN VARCHAR2
,p_column IN VARCHAR2
,p_value_set_id IN NUMBER DEFAULT G_MISS_NUM
,p_info_1 IN VARCHAR2 DEFAULT NULL
,p_default_value IN VARCHAR2
,p_unique_key_flag IN VARCHAR2 DEFAULT NULL
,p_enabled IN VARCHAR2
,p_display IN VARCHAR2
,p_control_level IN NUMBER DEFAULT -1
,p_attribute_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_view_in_hierarchy_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_edit_in_hierarchy_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_customization_level IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_owner IN NUMBER DEFAULT NULL
,p_lud IN DATE DEFAULT SYSDATE
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,p_is_nls_mode IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_uom_class IN VARCHAR2 DEFAULT G_MISS_CHAR
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
Update_Attribute (
p_api_version
,p_application_id
,p_attr_group_type
,p_attr_group_name
,p_internal_name
,p_display_name
,p_description
,p_sequence
,p_required
,p_searchable
,NULL
,p_column
,p_value_set_id
,p_info_1
,p_default_value
,p_unique_key_flag
,p_enabled
,p_display
,p_control_level
,p_attribute_code
,p_view_in_hierarchy_code
,p_edit_in_hierarchy_code
,p_customization_level
,p_owner
,p_lud
,p_init_msg_list
,p_commit
,p_is_nls_mode
,p_uom_class
,x_return_status
,x_errorcode
,x_msg_count
,x_msg_data
);
PROCEDURE Update_Attribute (
p_api_version IN NUMBER
,p_application_id IN NUMBER
,p_attr_group_type IN VARCHAR2
,p_attr_group_name IN VARCHAR2
,p_internal_name IN VARCHAR2
,p_display_name IN VARCHAR2
,p_description IN VARCHAR2
,p_sequence IN NUMBER
,p_required IN VARCHAR2
,p_searchable IN VARCHAR2
,p_read_only_flag IN VARCHAR2 --DEFAULT 'N' add by geguo
,p_column IN VARCHAR2
,p_value_set_id IN NUMBER DEFAULT G_MISS_NUM
,p_info_1 IN VARCHAR2 DEFAULT NULL
,p_default_value IN VARCHAR2
,p_unique_key_flag IN VARCHAR2 DEFAULT NULL
,p_enabled IN VARCHAR2
,p_display IN VARCHAR2
,p_control_level IN NUMBER DEFAULT -1
,p_attribute_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_view_in_hierarchy_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_edit_in_hierarchy_code IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_customization_level IN VARCHAR2 DEFAULT G_MISS_CHAR
,p_owner IN NUMBER DEFAULT NULL
,p_lud IN DATE DEFAULT SYSDATE
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,p_is_nls_mode IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_uom_class IN VARCHAR2 DEFAULT G_MISS_CHAR
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Attribute';
SAVEPOINT Update_Attribute_PUB;
SELECT SEARCH_FLAG, view_in_hierarchy_code, edit_in_hierarchy_code,
customization_level, attribute_code, uom_class, read_only_flag
INTO l_was_searchable, l_view_in_hierarchy_code, l_edit_in_hierarchy_code,
l_customization_level, l_attribute_code, l_uom_class, l_read_only_flag
FROM EGO_FND_DF_COL_USGS_EXT
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
AND APPLICATION_COLUMN_NAME = p_column;
SELECT flex_value_set_id
INTO l_value_set_id
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_Code = p_attr_group_name
AND END_USER_COLUMN_NAME = p_internal_name;
SELECT COLUMN_SEQ_NUM
INTO l_sequence
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_Code = p_attr_group_name
AND END_USER_COLUMN_NAME = p_internal_name;
SELECT COUNT(*)
INTO l_sequence
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_Code = p_attr_group_name
AND COLUMN_SEQ_NUM = p_sequence;
SELECT MULTI_ROW
INTO l_multi_row_flag
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_Code = p_attr_group_name;
SELECT MIN(COLUMN_SEQ_NUM)
INTO l_min_seq
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_Code = p_attr_group_name;
SELECT DATA_TYPE
INTO l_data_type_code
FROM EGO_FND_DF_COL_USGS_EXT
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
AND APPLICATION_COLUMN_NAME = p_column;
SELECT WIDTH
INTO l_col_width
FROM FND_COLUMNS
WHERE COLUMN_NAME = p_column
AND TABLE_ID = (SELECT TABLE_ID
FROM FND_TABLES
WHERE TABLE_NAME = l_table_name);
UPDATE FND_DESCR_FLEX_COLUMN_USAGES
SET LAST_UPDATED_BY = NVL(p_owner, g_current_user_id),
LAST_UPDATE_DATE = p_lud,
LAST_UPDATE_LOGIN = g_current_login_id,
COLUMN_SEQ_NUM = NVL(l_sequence, COLUMN_SEQ_NUM),
ENABLED_FLAG = NVL(p_enabled, ENABLED_FLAG),
REQUIRED_FLAG = NVL(p_required, REQUIRED_FLAG),
DISPLAY_FLAG = NVL(p_display, DISPLAY_FLAG),
FLEX_VALUE_SET_ID = l_value_set_id,
DEFAULT_VALUE = p_default_value
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
AND APPLICATION_COLUMN_NAME = p_column;
UPDATE EGO_FND_DF_COL_USGS_EXT
SET LAST_UPDATED_BY = NVL(p_owner, g_current_user_id),
LAST_UPDATE_DATE = p_lud,
LAST_UPDATE_LOGIN = g_current_login_id,
INFO_1 = p_info_1,
SEARCH_FLAG = NVL(p_searchable, SEARCH_FLAG),
CONTROL_LEVEL = decode(p_control_level, -1, CONTROL_LEVEL, p_control_level),
ATTRIBUTE_CODE = l_attribute_code,
VIEW_IN_HIERARCHY_CODE = l_view_in_hierarchy_code, -- update or keep the same by default?
EDIT_IN_HIERARCHY_CODE = l_edit_in_hierarchy_code, -- update or keep the same by default?
UOM_CLASS = l_uom_class -- Bug: 3525490
,CUSTOMIZATION_LEVEL = l_customization_level
,UNIQUE_KEY_FLAG = NVL(p_unique_key_flag, UNIQUE_KEY_FLAG)--to update the unique key in case of multi row attrgrp.
,READ_ONLY_FLAG = NVL(l_read_only_flag, 'N')
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
AND APPLICATION_COLUMN_NAME = p_column;
UPDATE FND_DESCR_FLEX_COL_USAGE_TL
SET LAST_UPDATED_BY = NVL(p_owner, g_current_user_id),
LAST_UPDATE_DATE = p_lud,
LAST_UPDATE_LOGIN = g_current_login_id,
FORM_LEFT_PROMPT = p_display_name,
FORM_ABOVE_PROMPT = p_display_name,
DESCRIPTION = p_description,
SOURCE_LANG = USERENV('LANG')
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
AND APPLICATION_COLUMN_NAME = p_column
--AND LANGUAGE = USERENV('LANG');
ROLLBACK TO Update_Attribute_PUB;
ROLLBACK TO Update_Attribute_PUB;
ROLLBACK TO Update_Attribute_PUB;
ROLLBACK TO Update_Attribute_PUB;
ROLLBACK TO Update_Attribute_PUB;
END Update_Attribute;
PROCEDURE Delete_Attribute (
p_api_version IN NUMBER
,p_application_id IN NUMBER
,p_attr_group_type IN VARCHAR2
,p_attr_group_name IN VARCHAR2
,p_attr_name IN VARCHAR2
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Attribute';
SELECT TL.FORM_LEFT_PROMPT
INTO l_token_table(1).TOKEN_VALUE
FROM FND_DESCR_FLEX_COL_USAGE_TL TL
,FND_DESCR_FLEX_COLUMN_USAGES FL_COL
WHERE FL_COL.APPLICATION_ID = p_application_id
AND FL_COL.DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
AND FL_COL.END_USER_COLUMN_NAME = p_attr_name
AND FL_COL.APPLICATION_ID = TL.APPLICATION_ID
AND FL_COL.DESCRIPTIVE_FLEXFIELD_NAME = TL.DESCRIPTIVE_FLEXFIELD_NAME
AND FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE = TL.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND FL_COL.APPLICATION_COLUMN_NAME = TL.APPLICATION_COLUMN_NAME
AND TL.LANGUAGE = USERENV('LANG');
SELECT DESCRIPTIVE_FLEX_CONTEXT_NAME
INTO l_token_table(2).TOKEN_VALUE
FROM FND_DESCR_FLEX_CONTEXTS_TL
WHERE APPLICATION_ID = p_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
AND LANGUAGE = USERENV('LANG');
Delete_Attribute_Internal(p_application_id, p_attr_group_type, p_attr_group_name,
p_attr_name, p_commit, x_return_status,
x_errorcode, x_msg_count, x_msg_data);
END Delete_Attribute;
SELECT version_seq_id INTO l_version_seq_id
FROM ego_flex_valueset_version_b
WHERE start_active_date < p_start_effective_date
AND ( end_active_date > p_start_effective_date
OR end_active_date IS NULL ) AND last_update_date < p_creation_date
AND flex_value_set_id = p_value_set_id AND version_seq_id <> 0 ;
SELECT version_seq_id INTO l_version_seq_id
FROM ego_flex_valueset_version_b
WHERE start_active_date < p_start_effective_date
AND last_update_date =(SELECT Min(last_update_date) from ego_flex_valueset_version_b
WHERE flex_value_set_id = p_value_set_id AND version_seq_id <> 0
AND last_update_date > p_creation_date )
AND flex_value_set_id = p_value_set_id AND version_seq_id <> 0 ;
SELECT start_active_date ,end_active_date,version_seq_id
FROM ego_flex_valueset_version_b
WHERE flex_value_set_id = p_value_set_id AND version_seq_id <> 0 ;
SELECT flex_value_id,SEQUENCE
FROM EGO_FLEX_VALUE_VERSION_B
WHERE flex_value_set_id = p_value_set_id AND version_seq_id = 0;
SELECT COUNT(*) INTO L_DUP_REC FROM
(
(SELECT FLEX_VALUE_ID,SEQUENCE FROM EGO_FLEX_VALUE_VERSION_B
WHERE FLEX_VALUE_SET_ID = P_VALUE_SET_ID AND VERSION_SEQ_ID=0
MINUS
SELECT FLEX_VALUE_ID,SEQUENCE FROM EGO_FLEX_VALUE_VERSION_B
WHERE FLEX_VALUE_SET_ID = P_VALUE_SET_ID AND VERSION_SEQ_ID=L_MAX_VERSION_SEQ_ID)
UNION
(SELECT FLEX_VALUE_ID,SEQUENCE FROM EGO_FLEX_VALUE_VERSION_B
WHERE FLEX_VALUE_SET_ID = P_VALUE_SET_ID AND VERSION_SEQ_ID=L_MAX_VERSION_SEQ_ID
MINUS
SELECT FLEX_VALUE_ID,SEQUENCE FROM EGO_FLEX_VALUE_VERSION_B
WHERE FLEX_VALUE_SET_ID = P_VALUE_SET_ID AND VERSION_SEQ_ID=0)
) ;
SELECT COUNT(*) INTO L_SAME_REL_DATE FROM EGO_FLEX_VALUESET_VERSION_B
WHERE FLEX_VALUE_SET_ID = P_VALUE_SET_ID AND
( START_ACTIVE_DATE= P_START_DATE OR END_ACTIVE_DATE = P_START_DATE)
AND VERSION_SEQ_ID <>0;
UPDATE EGO_FLEX_VALUESET_VERSION_B
SET END_ACTIVE_DATE =p_start_date -1/(24*60*60) ,
LAST_UPDATED_BY= FND_GLOBAL.PARTY_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE FLEX_VALUE_SET_ID = p_value_set_id AND VERSION_SEQ_ID = l_version_seq_id;
INSERT INTO EGO_FLEX_VALUESET_VERSION_B
(FLEX_VALUE_SET_ID, VERSION_SEQ_ID,
DESCRIPTION, START_ACTIVE_DATE,END_ACTIVE_DATE, CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,last_update_date,last_update_login)
VALUES (p_value_set_id, p_version_seq_id,
p_description, p_start_date,l_relver_end_active_date,fnd_global.party_id,
SYSDATE,fnd_global.party_id,SYSDATE,
fnd_global.login_id) ;
INSERT INTO EGO_FLEX_VALUESET_VERSION_B (FLEX_VALUE_SET_ID, VERSION_SEQ_ID,
CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,last_update_date,last_update_login) VALUES (p_value_set_id, 0,
fnd_global.party_id,SYSDATE,fnd_global.party_id,SYSDATE,
fnd_global.login_id) ;
SELECT Min(start_active_date ) INTO l_min_start_active_date
FROM EGO_FLEX_VALUESET_VERSION_B WHERE FLEX_VALUE_SET_ID = p_value_set_id ;
UPDATE EGO_FLEX_VALUESET_VERSION_B SET END_ACTIVE_DATE =(p_start_date- 1/(24*60*60)),
version_seq_id = l_max_version_seq_id ,
LAST_UPDATED_BY= FND_GLOBAL.PARTY_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE FLEX_VALUE_SET_ID = p_value_set_id AND (start_active_date =( SELECT Max(start_active_date )
FROM EGO_FLEX_VALUESET_VERSION_B WHERE FLEX_VALUE_SET_ID = p_value_set_id) ) ;
INSERT INTO EGO_FLEX_VALUESET_VERSION_B (FLEX_VALUE_SET_ID, VERSION_SEQ_ID,
CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,last_update_date,last_update_login) VALUES (p_value_set_id, 0,
fnd_global.party_id,SYSDATE,fnd_global.party_id,SYSDATE,
fnd_global.login_id) ;
INSERT INTO EGO_FLEX_VALUESET_VERSION_B (FLEX_VALUE_SET_ID, VERSION_SEQ_ID,
DESCRIPTION, START_ACTIVE_DATE,CREATED_BY,CREATION_DATE,
LAST_UPDATED_BY,last_update_date,last_update_login) VALUES (p_value_set_id,p_version_seq_id,
p_description, p_start_date,fnd_global.party_id,SYSDATE,fnd_global.party_id,SYSDATE,
fnd_global.login_id) ;
UPDATE EGO_FLEX_VALUESET_VERSION_B SET END_ACTIVE_DATE =(l_min_start_active_date -
1/(24*60*60) ),LAST_UPDATED_BY= FND_GLOBAL.PARTY_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE FLEX_VALUE_SET_ID = p_value_set_id AND start_active_date = p_start_date;
UPDATE EGO_FLEX_VALUE_VERSION_B SET VERSION_SEQ_ID =P_VERSION_SEQ_ID ,
LAST_UPDATED_BY= FND_GLOBAL.PARTY_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE FLEX_VALUE_SET_ID = P_VALUE_SET_ID AND VERSION_SEQ_ID = 0 AND FLEX_VALUE_ID
= REC_DUPLICATEROW.FLEX_VALUE_ID;
INSERT INTO EGO_FLEX_VALUE_VERSION_B (FLEX_VALUE_SET_ID,FLEX_VALUE_ID,VERSION_SEQ_ID
,SEQUENCE,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
VALUES(P_VALUE_SET_ID,REC_DUPLICATEROW.FLEX_VALUE_ID,0,REC_DUPLICATEROW.SEQUENCE,
FND_GLOBAL.PARTY_ID,SYSDATE,FND_GLOBAL.PARTY_ID,SYSDATE,FND_GLOBAL.LOGIN_ID);
INSERT INTO EGO_FLEX_VALUE_VERSION_TL (FLEX_VALUE_ID,VERSION_SEQ_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,
CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,DESCRIPTION,FLEX_VALUE_MEANING,LANGUAGE,SOURCE_LANG)
SELECT FLEX_VALUE_ID,P_VERSION_SEQ_ID,SYSDATE,FND_GLOBAL.PARTY_ID,SYSDATE,FND_GLOBAL.PARTY_ID,
FND_GLOBAL.LOGIN_ID,DESCRIPTION,FLEX_VALUE_MEANING,LANGUAGE,SOURCE_LANG FROM
EGO_FLEX_VALUE_VERSION_TL WHERE FLEX_VALUE_ID = REC_DUPLICATEROW.FLEX_VALUE_ID AND VERSION_SEQ_ID=0 ;
SELECT flex_value_id,SEQUENCE
FROM ego_flex_value_version_b
WHERE version_seq_id = p_version_number AND flex_value_set_id = p_value_set_id;
DELETE FROM EGO_FLEX_VALUE_VERSION_TL WHERE VERSION_SEQ_ID =0 AND FLEX_VALUE_ID IN
(SELECT FLEX_VALUE_ID FROM EGO_FLEX_VALUE_VERSION_B WHERE FLEX_VALUE_SET_ID =P_VALUE_SET_ID
AND VERSION_SEQ_ID = 0) ;
DELETE FROM EGO_FLEX_VALUE_VERSION_B
WHERE FLEX_VALUE_SET_ID =P_VALUE_SET_ID AND VERSION_SEQ_ID = 0;
INSERT INTO EGO_FLEX_VALUE_VERSION_B(FLEX_VALUE_SET_ID,FLEX_VALUE_ID
,VERSION_SEQ_ID,SEQUENCE,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
VALUES (P_VALUE_SET_ID,RECT_T.FLEX_VALUE_ID,0,RECT_T.SEQUENCE,FND_GLOBAL.PARTY_ID,
SYSDATE,FND_GLOBAL.PARTY_ID,SYSDATE,FND_GLOBAL.LOGIN_ID );
INSERT INTO EGO_FLEX_VALUE_VERSION_TL (FLEX_VALUE_ID,VERSION_SEQ_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,
CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,DESCRIPTION,FLEX_VALUE_MEANING,LANGUAGE,SOURCE_LANG)
SELECT FLEX_VALUE_ID,0,SYSDATE,FND_GLOBAL.PARTY_ID,SYSDATE,FND_GLOBAL.PARTY_ID,
FND_GLOBAL.LOGIN_ID,DESCRIPTION,FLEX_VALUE_MEANING,LANGUAGE,SOURCE_LANG FROM EGO_FLEX_VALUE_VERSION_TL
WHERE VERSION_SEQ_ID = P_VERSION_NUMBER AND FLEX_VALUE_ID IN
(SELECT FLEX_VALUE_ID FROM EGO_FLEX_VALUE_VERSION_B
WHERE FLEX_VALUE_SET_ID = P_VALUE_SET_ID AND VERSION_SEQ_ID = P_VERSION_NUMBER);
SELECT PERSON_PARTY_ID INTO L_CREATED_BY FROM FND_USER WHERE USER_ID
=( SELECT CREATED_BY FROM FND_FLEX_VALUE_SETS WHERE FLEX_VALUE_SET_ID = P_VALUE_SET_ID) ;
INSERT INTO EGO_FLEX_VALUESET_VERSION_B(FLEX_VALUE_SET_ID,VERSION_SEQ_ID,DESCRIPTION
,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
VALUES (P_VALUE_SET_ID, 0, P_DESCRIPTION,L_CREATED_BY,SYSDATE,FND_GLOBAL.PARTY_ID,SYSDATE,
FND_GLOBAL.LOGIN_ID);
INSERT INTO EGO_OBJECT_LOCK (LOCK_ID,OBJECT_NAME,PK1_VALUE,LOCKING_PARTY_ID,LOCK_FLAG,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
VALUES
(EGO_OBJECT_LOCK_S.NEXTVAL,'EGO_VALUE_SET',P_VALUE_SET_ID,FND_GLOBAL.PARTY_ID,'L',
FND_GLOBAL.LOGIN_ID,SYSDATE,FND_GLOBAL.LOGIN_ID,SYSDATE,FND_GLOBAL.LOGIN_ID) ;
UPDATE FND_FLEX_VALUES SET START_DATE_ACTIVE = NULL ,END_DATE_ACTIVE= NULL
WHERE FLEX_VALUE_SET_ID= P_VALUE_SET_ID AND ENABLED_FLAG='Y';
INSERT INTO EGO_FLEX_VALUE_VERSION_B (FLEX_VALUE_SET_ID, FLEX_VALUE_ID,SEQUENCE,
VERSION_SEQ_ID,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
SELECT P_VALUE_SET_ID, A.FLEX_VALUE_ID, B.DISP_SEQUENCE,0,A.CREATED_BY,SYSDATE,
FND_GLOBAL.PARTY_ID,SYSDATE,FND_GLOBAL.LOGIN_ID
FROM FND_FLEX_VALUES A, EGO_VS_VALUES_DISP_ORDER B
WHERE
A.FLEX_VALUE_SET_ID = P_VALUE_SET_ID
AND A.ENABLED_FLAG='Y'
AND B.VALUE_SET_VALUE_ID = A.FLEX_VALUE_ID ;
INSERT INTO EGO_FLEX_VALUE_VERSION_TL (FLEX_VALUE_ID,DESCRIPTION,VERSION_SEQ_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,
CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,FLEX_VALUE_MEANING,LANGUAGE,SOURCE_LANG
) SELECT A.FLEX_VALUE_ID,B.DESCRIPTION ,0,SYSDATE,FND_GLOBAL.PARTY_ID,SYSDATE,FND_GLOBAL.PARTY_ID,FND_GLOBAL.LOGIN_ID,
B.FLEX_VALUE_MEANING,B.LANGUAGE,USERENV('LANG') FROM EGO_FLEX_VALUE_VERSION_B A,
FND_FLEX_VALUES_TL B
WHERE A.FLEX_VALUE_SET_ID = P_VALUE_SET_ID AND
A.VERSION_SEQ_ID = 0 AND A.FLEX_VALUE_ID = B.FLEX_VALUE_ID;
Insert into EGO_FLEX_VALUESET_VERSION_B
(flex_value_set_id,version_seq_id,description
,created_by,creation_date,last_updated_by,last_update_date,last_update_login)
VALUES (x_value_set_id, 0, p_description,FND_GLOBAL.party_id,SYSDATE,FND_GLOBAL.party_id,SYSDATE,
FND_GLOBAL.login_id);
INSERT INTO ego_object_lock (LOCK_ID,OBJECT_NAME,PK1_VALUE,locking_party_id,lock_flag,
created_by,creation_date,last_updated_by,last_update_date,last_update_login) VALUES
(EGO_OBJECT_LOCK_S.nextval,'EGO_VALUE_SET',x_value_set_id,FND_GLOBAL.party_id,'L',FND_GLOBAL.login_id,SYSDATE,
FND_GLOBAL.login_id,SYSDATE,FND_GLOBAL.login_id) ;
UPDATE fnd_flex_value_sets
SET validation_type = l_validation_code
WHERE flex_value_set_name = p_value_set_name;
UPDATE fnd_flex_value_sets
SET validation_type = l_validation_code
WHERE flex_value_set_name = p_value_set_name;
SELECT USER_NAME INTO l_owner_name
FROM FND_USER
WHERE USER_ID = l_owner;
SELECT flex_value_set_id
INTO x_value_set_id
FROM fnd_flex_value_sets
WHERE flex_value_set_name = p_value_set_name;
SELECT *
INTO l_parent_vs_row
FROM ego_value_sets_v
WHERE value_set_id = p_parent_vs_id;
INSERT INTO ego_value_set_ext (value_set_id, parent_value_set_id,
created_by, creation_date, last_updated_by, last_update_date,
last_update_login)
VALUES (x_child_vs_id, p_parent_vs_id, l_owner, SYSDATE, l_owner, SYSDATE,
g_current_login_id );
INSERT INTO ego_vs_values_disp_order (value_set_value_id, value_set_id,
disp_sequence, created_by, creation_date, last_updated_by,
last_update_date, last_update_login)
VALUES (child_vs_value_ids(i), x_child_vs_id, i, l_owner, SYSDATE, l_owner,
SYSDATE, g_current_login_id);
Build_Child_VS_Select_Exprs (
p_parent_vs_row => l_parent_vs_row
, x_id_column_type => l_id_column_type
, x_value_column_type => l_value_column_type
, x_id_column_name => l_id_column_name
, x_value_column_name => l_value_column_name
);
Insert_Value_Set_Table_Inf (
p_api_version => p_api_version
, p_value_set_id => x_child_vs_id
, p_table_application_id => 431
, p_table_name => 'EGO_VALUE_SET_VALUES_V vsv , EGO_VS_VALUES_DISP_ORDER do'
, p_value_column_name => l_value_column_name
, p_value_column_type => l_value_column_type
, p_value_column_size => 150
, p_meaning_column_name => 'vsv.DESCRIPTION' --Bug 13242028
, p_meaning_column_type => G_CHAR_DATA_TYPE --Bug 13242028
, p_meaning_column_size => 150 --Bug 13242028
, p_id_column_name => l_id_column_name
, p_id_column_type => l_id_column_type
, p_id_column_size => 150
, p_where_order_by => l_inf_where_clause
, p_additional_columns => ''
, p_owner => l_owner
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, x_return_status => l_return_status_inf
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
code_debug('Create_Child_Value_Set(): Insert into FND_FLEX_VALIDATION_TABLES failed.');
Deletes a child value set
PARAMETERS
See below.
-----------------------------------------------------------------------------*/
PROCEDURE Delete_Child_Value_Set (
p_api_version IN NUMBER
,p_application_id IN NUMBER
,p_child_vs_id IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)
IS
BEGIN
----------------------------------------------------------------------------
-- Delete from fnd_flex_value_sets --
----------------------------------------------------------------------------
delete from FND_FLEX_VALUE_SETS where flex_value_set_id = p_child_vs_id;
delete from ego_value_set_ext where VALUE_SET_ID = p_child_vs_id;
delete from ego_vs_values_disp_order where VALUE_SET_ID = p_child_vs_id;
END Delete_Child_Value_Set;
PROCEDURE Update_Value_Set (
p_api_version IN NUMBER
,p_value_set_id IN NUMBER
,p_description IN VARCHAR2
,p_format_code IN VARCHAR2
,p_maximum_size IN NUMBER
,p_maximum_value IN VARCHAR2
,p_minimum_value IN VARCHAR2
,p_long_list_flag IN FND_FLEX_VALUE_SETS.LONGLIST_FLAG%TYPE
-- VARCHAR2
,p_validation_code IN VARCHAR2
,p_owner IN NUMBER DEFAULT NULL
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
-- ,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_versioned_vs OUT NOCOPY VARCHAR2
) IS
l_api_name VARCHAR2(30);
l_api_name := 'Update_Value_Set';
SAVEPOINT Update_Value_Set_PUB;
UPDATE FND_FLEX_VALUE_SETS
SET DESCRIPTION = p_description,
LONGLIST_FLAG = p_long_list_flag,
MINIMUM_VALUE = l_minimum_value,
MAXIMUM_VALUE = l_maximum_value,
MAXIMUM_SIZE = l_maximum_size,
LAST_UPDATED_BY = l_owner,
LAST_UPDATE_DATE = l_Sysdate,
LAST_UPDATE_LOGIN = g_current_login_id
WHERE FLEX_VALUE_SET_ID = p_value_set_id;
(SELECT *
FROM ego_value_sets_v
WHERE parent_value_set_id = p_value_set_id)
LOOP
-- Update the long list display type
UPDATE fnd_flex_value_sets
SET longlist_flag = p_long_list_flag
WHERE flex_value_set_id = l_value_set_rec.value_set_id;
SELECT Count(*) INTO isVersionedVS FROM ego_flex_valueset_version_b
WHERE flex_value_set_id = p_value_set_id ;
ROLLBACK TO Update_Value_Set_PUB;
ROLLBACK TO Update_Value_Set_PUB;
END Update_Value_Set;
Updates a child value set
PARAMETERS
See below.
NOTES
(-) Old child value set IDs get erased.
(-) The value set name cannot be altered after creation.
(-) The 'created' fields will not retain the old data. The fact that the
value set values are recreated every time becomes apparent when \
viewing these fields.
-----------------------------------------------------------------------------*/
PROCEDURE Update_Child_Value_Set (
p_api_version IN NUMBER
,p_value_set_id IN NUMBER
,p_description IN VARCHAR2
,p_format_code IN VARCHAR2
,p_owner IN NUMBER DEFAULT NULL
,child_vs_value_ids IN EGO_VALUE_SET_VALUE_IDS
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_vs_value_count NUMBER;
l_api_name := 'Update_Child_Value_Set';
SAVEPOINT Update_Child_Value_Set_PUB;
SELECT pvs.*
INTO l_parent_vs_row
FROM ego_value_sets_v vs, ego_value_sets_v pvs
WHERE vs.parent_value_set_id = pvs.value_set_id
AND vs.value_set_id = p_value_set_id;
Update_Value_Set (
p_api_version => p_api_version
,p_value_set_id => p_value_set_id
,p_description => p_description
,p_format_code => l_parent_vs_row.format_code
,p_maximum_size => l_parent_vs_row.maximum_size
,p_maximum_value => l_parent_vs_row.maximum_value
,p_minimum_value => l_parent_vs_row.minimum_value
,p_long_list_flag => l_parent_vs_row.longlist_flag
,p_validation_code => l_parent_vs_row.validation_code_admin
,p_owner => p_owner
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
--changes for p4t begin
, x_versioned_vs => x_versioned_vs
--changes for p4t ends
);
SELECT COUNT(*) INTO l_vs_value_count
FROM ego_vs_values_disp_order
WHERE value_set_value_id = child_vs_value_ids(i)
AND value_set_id = p_value_set_id;
SELECT
value_set_value_id -- VALUE_SET_VALUE_ID
, value_set_id -- VALUE_SET_ID
, i -- DISP_SEQUENCE
, created_by -- CREATED_BY
, creation_date -- CREATION_DATE
, p_owner -- LAST_UPDATED_BY
, SYSDATE -- LAST_UPDATE_DATE
, g_current_login_id -- LAST_UPDATE_LOGIN
*/
SELECT *
/* Bug 12382454 : End */
INTO l_vs_value_disp_orders(i)
FROM ego_vs_values_disp_order
WHERE value_set_value_id = child_vs_value_ids(i) and
value_set_id = p_value_set_id;
l_vs_value_disp_orders(i).LAST_UPDATED_BY := p_owner;
l_vs_value_disp_orders(i).LAST_UPDATE_DATE := SYSDATE;
l_vs_value_disp_orders(i).LAST_UPDATE_LOGIN := g_current_login_id;
l_vs_value_disp_orders(i).LAST_UPDATED_BY := p_owner;
l_vs_value_disp_orders(i).LAST_UPDATE_DATE := SYSDATE;
l_vs_value_disp_orders(i).LAST_UPDATE_LOGIN := g_current_login_id;
SELECT
child_vs_value_ids(i) -- VALUE_SET_VALUE_ID
, p_value_set_id -- VALUE_SET_ID
, i -- DISP_SEQUENCE
, p_owner -- CREATED_BY
, SYSDATE -- CREATION_DATE
, p_owner -- LAST_UPDATED_BY
, SYSDATE -- LAST_UPDATE_DATE
, g_current_login_id -- LAST_UPDATE_LOGIN
INTO l_vs_value_disp_orders(i)
FROM dual;
DELETE FROM ego_vs_values_disp_order
WHERE value_set_id = p_value_set_id;
INSERT INTO ego_vs_values_disp_order
VALUES l_vs_value_disp_orders(j);
ROLLBACK TO Update_Child_Value_Set_PUB;
PROCEDURE Insert_Value_Set_Table_Inf (
p_api_version IN NUMBER
,p_value_set_id IN NUMBER
,p_table_application_id IN NUMBER
,p_table_name IN VARCHAR2
,p_value_column_name IN VARCHAR2
,p_value_column_type IN VARCHAR2
,p_value_column_size IN NUMBER
,p_meaning_column_name IN VARCHAR2
,p_meaning_column_type IN VARCHAR2
,p_meaning_column_size IN NUMBER
,p_id_column_name IN VARCHAR2
,p_id_column_type IN VARCHAR2
,p_id_column_size IN NUMBER
,p_where_order_by IN VARCHAR2
,p_additional_columns IN VARCHAR2
,p_owner IN NUMBER DEFAULT NULL
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name VARCHAR2(30);
l_api_name := 'Insert_Value_Set_Table_Inf';
SAVEPOINT Insert_Value_Set_Table_Inf_PUB;
Insert_Order_By_Space (
p_where_order_by => l_where_order_by
);
code_debug(l_api_name ||' inserting data into fnd_flex_validation_tables ');
INSERT INTO fnd_flex_validation_tables
(
FLEX_VALUE_SET_ID
,APPLICATION_TABLE_NAME
,VALUE_COLUMN_NAME
,VALUE_COLUMN_TYPE
,VALUE_COLUMN_SIZE
,COMPILED_ATTRIBUTE_COLUMN_NAME
,ENABLED_COLUMN_NAME
,HIERARCHY_LEVEL_COLUMN_NAME
,START_DATE_COLUMN_NAME
,END_DATE_COLUMN_NAME
,SUMMARY_ALLOWED_FLAG
,SUMMARY_COLUMN_NAME
,ID_COLUMN_NAME
,ID_COLUMN_TYPE
,ID_COLUMN_SIZE
,MEANING_COLUMN_NAME
,MEANING_COLUMN_TYPE
,MEANING_COLUMN_SIZE
,TABLE_APPLICATION_ID
,ADDITIONAL_WHERE_CLAUSE
,ADDITIONAL_QUICKPICK_COLUMNS
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
)
VALUES
(
p_value_set_id --FLEX_VALUE_SET_ID
,p_table_name --APPLICATION_TABLE_NAME
,p_value_column_name --VALUE_COLUMN_NAME
,p_value_column_type --VALUE_COLUMN_TYPE
,p_value_column_size --VALUE_COLUMN_SIZE
-- ,p_value_column_name --COMPILED_ATTRIBUTE_COLUMN_NAME
,'NULL' --COMPILED_ATTRIBUTE_COLUMN_NAME
-- ,'Y' --ENABLED_COLUMN_NAME
,'''Y''' --ENABLED_COLUMN_NAME
,'NULL' --HIERARCHY_LEVEL_COLUMN_NAME
-- ,'START_DATE_COLUMN_NAME' --START_DATE_COLUMN_NAME
-- ,'END_DATE_COLUMN_NAME' --END_DATE_COLUMN_NAME
,'TO_DATE(NULL)' --START_DATE_COLUMN_NAME
,'TO_DATE(NULL)' --END_DATE_COLUMN_NAME
,'N' --SUMMARY_ALLOWED_FLAG
,'''N''' --SUMMARY_COLUMN_NAME
,p_id_column_name --ID_COLUMN_NAME
,p_id_column_type --ID_COLUMN_SIZE
,p_id_column_size --ID_COLUMN_TYPE
,p_meaning_column_name --MEANING_COLUMN_NAME
,p_meaning_column_type --MEANING_COLUMN_TYPE
,p_meaning_column_size --MEANING_COLUMN_SIZE
,p_table_application_id --TABLE_APPLICATION_ID
,l_where_order_by --ADDITIONAL_WHERE_CLAUSE
,'' --ADDITIONAL_QUICKPICK_COLUMNS
,l_owner --CREATED_BY
,l_Sysdate --CREATION_DATE
,l_owner --LAST_UPDATED_BY
,l_Sysdate --LAST_UPDATE_DATE
,g_current_login_id --LAST_UPDATE_LOGIN
);
code_debug(l_api_name ||' inserting data into fnd_flex_validation_tables COMPLETED ');
ROLLBACK TO Insert_Value_Set_Table_Inf_PUB;
ROLLBACK TO Insert_Value_Set_Table_Inf_PUB;
END Insert_Value_Set_table_Inf;
PROCEDURE Update_Value_Set_Table_Inf (
p_api_version IN NUMBER
,p_value_set_id IN NUMBER
,p_table_application_id IN NUMBER
,p_table_name IN VARCHAR2
,p_value_column_name IN VARCHAR2
,p_value_column_type IN VARCHAR2
,p_value_column_size IN NUMBER
,p_meaning_column_name IN VARCHAR2
,p_meaning_column_type IN VARCHAR2
,p_meaning_column_size IN NUMBER
,p_id_column_name IN VARCHAR2
,p_id_column_type IN VARCHAR2
,p_id_column_size IN NUMBER
,p_where_order_by IN VARCHAR2
,p_additional_columns IN VARCHAR2
,p_owner IN NUMBER DEFAULT NULL
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name VARCHAR2(30);
l_api_name := 'Update_Value_Set_Table_Inf';
SAVEPOINT Update_Value_Set_Table_Inf_PUB;
Insert_Order_By_Space (
p_where_order_by => l_where_order_by
);
code_debug(l_api_name ||' calling update fnd_flex_validation_tables ');
UPDATE FND_FLEX_VALIDATION_TABLES
SET APPLICATION_TABLE_NAME = p_table_name,
VALUE_COLUMN_NAME = p_value_column_name,
VALUE_COLUMN_TYPE = p_value_column_type,
VALUE_COLUMN_SIZE = p_value_column_size,
-- COMPILED_ATTRIBUTE_COLUMN_NAME = p_value_column_name,
-- ENABLED_COLUMN_NAME = 'Y',
-- HIERARCHY_LEVEL_COLUMN_NAME = 'Y',
-- START_DATE_COLUMN_NAME = 'START_DATE_COLUMN_NAME',
-- END_DATE_COLUMN_NAME = 'END_DATE_COLUMN_NAME',
-- SUMMARY_ALLOWED_FLAG = 'N',
-- SUMMARY_COLUMN_NAME = 'N',
ID_COLUMN_NAME = p_id_column_name,
ID_COLUMN_SIZE = p_id_column_size,
ID_COLUMN_TYPE = p_id_column_type,
MEANING_COLUMN_NAME = p_meaning_column_name,
MEANING_COLUMN_SIZE = p_meaning_column_size,
MEANING_COLUMN_TYPE = p_meaning_column_type,
TABLE_APPLICATION_ID = p_table_application_id,
ADDITIONAL_WHERE_CLAUSE = l_where_order_by,
-- ADDITIONAL_QUICKPICK_COLUMNS = '',
LAST_UPDATED_BY = l_owner,
LAST_UPDATE_DATE = l_Sysdate,
LAST_UPDATE_LOGIN = g_current_login_id
WHERE FLEX_VALUE_SET_ID = p_value_set_id;
code_debug(l_api_name ||' calling update fnd_flex_validation_tables COMPLETED ');
ROLLBACK TO Update_Value_Set_Table_Inf_PUB;
ROLLBACK TO Update_Value_Set_Table_Inf_PUB;
END Update_Value_Set_table_Inf;
SELECT FLEX_VALUE_SET_ID INTO
X_FLEX_VALUE_SET_ID FROM FND_FLEX_VALUE_SETS WHERE FLEX_VALUE_SET_NAME = P_VALUE_SET_NAME ;
SELECT COUNT(*) INTO L_VALUE_EXIST FROM FND_FLEX_VALUES WHERE FLEX_VALUE_SET_ID = X_FLEX_VALUE_SET_ID
AND FLEX_VALUE = P_INTERNAL_NAME ;
SELECT count (*) INTO VersionedValueSet FROM EGO_FLEX_VALUESET_VERSION_B WHERE
FLEX_VALUE_SET_ID = x_flex_Value_Set_Id ;
SELECT FLEX_VALUE_ID INTO l_flex_value_id FROM FND_FLEX_VALUES
WHERE FLEX_VALUE_SET_ID = x_flex_Value_Set_Id AND FLEX_VALUE like p_internal_name ;
INSERT INTO EGO_FLEX_VALUE_VERSION_B (FLEX_VALUE_SET_ID, FLEX_VALUE_ID, VERSION_SEQ_ID,
sequence,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
VALUES (x_flex_Value_Set_Id, l_flex_value_id,0,p_sequence,fnd_global.party_id,SYSDATE,
fnd_global.party_id,SYSDATE,fnd_global.login_id);
INSERT INTO EGO_FLEX_VALUE_VERSION_TL (FLEX_VALUE_ID,VERSION_SEQ_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,
CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,DESCRIPTION,FLEX_VALUE_MEANING,LANGUAGE,
SOURCE_LANG
) SELECT
L_FLEX_VALUE_ID,0,SYSDATE,FND_GLOBAL.PARTY_ID,SYSDATE,FND_GLOBAL.PARTY_ID,FND_GLOBAL.LOGIN_ID,
P_DESCRIPTION,P_DISPLAY_NAME,L.LANGUAGE_CODE,USERENV('LANG')
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B' ) ;
INSERT INTO fnd_lookup_values
(
LOOKUP_TYPE
,LANGUAGE
,LOOKUP_CODE
,MEANING
,DESCRIPTION
,ENABLED_FLAG
,START_DATE_ACTIVE
,END_DATE_ACTIVE
,SOURCE_LANG
,SECURITY_GROUP_ID
,VIEW_APPLICATION_ID
,TERRITORY_CODE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,TAG
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
)
SELECT
'EGO_EF_'||p_value_set_name||'_TYPE' --LOOKUP_TYPE
,L.LANGUAGE_CODE --LANGUAGE
,p_internal_name --LOOKUP_CODE
,p_display_name --MEANING
,p_description --DESCRIPTION
,p_enabled --ENABLED_FLAG
,p_start_date --START_DATE_ACTIVE
,p_end_date --END_DATE_ACTIVE
,USERENV('LANG') --SOURCE_LANG
,0 --SECURITY_GROUP_ID
,p_application_id --VIEW_APPLICATION_ID
,'' --TERRITORY_CODE
,'' --ATTRIBUTE_CATEGORY
,'' --ATTRIBUTE1
,'' --ATTRIBUTE2
,'' --ATTRIBUTE3
,'' --ATTRIBUTE4
,'' --ATTRIBUTE5
,'' --ATTRIBUTE6
,'' --ATTRIBUTE7
,'' --ATTRIBUTE8
,'' --ATTRIBUTE9
,'' --ATTRIBUTE10
,'' --ATTRIBUTE11
,'' --ATTRIBUTE12
,'' --ATTRIBUTE13
,'' --ATTRIBUTE14
,'' --ATTRIBUTE15
,to_char(p_sequence) --TAG
,g_current_user_id --CREATED_BY
,l_Sysdate --CREATION_DATE
,g_current_user_id --LAST_UPDATED_BY
,l_Sysdate --LAST_UPDATE_DATE
,g_current_login_id --LAST_UPDATE_LOGIN
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B');
PROCEDURE Update_Value_Set_Val (
p_api_version IN NUMBER
,p_value_set_name IN VARCHAR2
,p_internal_name IN VARCHAR2
,p_display_name IN VARCHAR2
,p_description IN VARCHAR2
,p_sequence IN NUMBER
,p_start_date IN DATE
,p_end_date IN DATE
,p_enabled IN VARCHAR2
,p_owner IN NUMBER DEFAULT NULL
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_is_versioned OUT NOCOPY VARCHAR2
,x_valueSetId OUT NOCOPY VARCHAR2
) IS
l_api_name VARCHAR2(30);
l_api_name := 'Update_Value_Set_Val';
SAVEPOINT Update_Value_Set_Val_PUB;
UPDATE FND_LOOKUP_VALUES
SET MEANING = p_display_name,
DESCRIPTION = p_description,
ENABLED_FLAG = p_enabled,
START_DATE_ACTIVE = p_start_date,
END_DATE_ACTIVE = p_end_date,
TAG = TO_CHAR(p_sequence),
LAST_UPDATED_BY = g_current_user_id,
LAST_UPDATE_DATE = l_Sysdate,
LAST_UPDATE_LOGIN = g_current_login_id,
SOURCE_LANG = USERENV('LANG')
WHERE LOOKUP_TYPE = l_lookup_type
AND LOOKUP_CODE = p_internal_name
AND VIEW_APPLICATION_ID = p_application_id
--AND LANGUAGE = USERENV('LANG');
SELECT flex_value_set_id INTO l_value_set_id FROM fnd_flex_value_sets
WHERE flex_value_set_name = p_value_set_name;
SELECT flex_value_id INTO l_flex_value_id FROM fnd_flex_values
WHERE flex_value_set_id = l_value_set_id AND flex_value = p_internal_name;
p_transaction_type => 'UPDATE'
,p_value_set_name => p_value_set_name
,p_internal_name => p_internal_name
,p_display_name => p_display_name
,p_description => p_description
,p_sequence => p_sequence
,p_start_date => p_start_date
,p_end_date => p_end_date
,p_enabled => p_enabled
,p_owner => l_owner
,x_return_status => x_return_status
);
SELECT COUNT(*) INTO L_VALUES_COUNT FROM EGO_FLEX_VALUESET_VERSION_B WHERE FLEX_VALUE_SET_ID = L_VALUE_SET_ID;
UPDATE ego_flex_value_version_b SET SEQUENCE = p_sequence
WHERE version_seq_id = 0 AND flex_value_set_id = l_value_set_id AND flex_value_id = l_flex_value_id ;
UPDATE EGO_FLEX_VALUE_VERSION_TL SET DESCRIPTION = P_DESCRIPTION,
FLEX_VALUE_MEANING = P_DISPLAY_NAME , SOURCE_LANG = USERENV('LANG'),LAST_UPDATE_DATE =SYSDATE,
LAST_UPDATED_BY = FND_GLOBAL.PARTY_ID,LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE FLEX_VALUE_ID = L_FLEX_VALUE_ID AND VERSION_SEQ_ID = 0 AND USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
ROLLBACK TO Update_Value_Set_Val_PUB;
ROLLBACK TO Update_Value_Set_Val_PUB;
END Update_Value_Set_Val;
,p_last_update_date IN VARCHAR2
,p_last_updated_by IN NUMBER
,p_init_msg_list IN VARCHAR2
,p_commit IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_flex_value_id fnd_flex_values.flex_value_id%TYPE;
SELECT vsvtl.last_updated_by, vsvtl.last_update_date
FROM FND_FLEX_VALUES vsv, FND_FLEX_VALUES_TL vsvtl
WHERE vsv.flex_value_set_id = cp_value_set_id
AND vsv.flex_value = p_internal_name
AND vsv.flex_value_id = vsvtl.flex_value_id
AND USERENV('LANG') IN (language, source_lang);
l_last_update_login fnd_flex_values.last_update_login%TYPE;
l_last_update_date fnd_flex_values.last_update_date%TYPE;
l_last_updated_by fnd_flex_values.last_updated_by%TYPE;
SELECT vs.flex_value_set_id, vs.validation_type
INTO l_value_set_id, l_validation_type
FROM FND_FLEX_VALUE_SETS vs
WHERE vs.flex_value_set_name = p_value_set_name;
FETCH c_get_vs_details INTO l_last_updated_by, l_last_update_date;
l_last_update_login := FND_GLOBAL.Login_Id;
IF (fnd_load_util.upload_test(p_last_updated_by
,p_last_update_date
,l_last_updated_by
,l_last_update_date
,NULL)) THEN
SELECT vsv.*
INTO l_vsv_row
FROM FND_FLEX_VALUES vsv
WHERE vsv.flex_value_set_id = l_value_set_id
AND vsv.flex_value = p_internal_name;
FND_FLEX_VALUES_PKG.UPDATE_ROW
(x_flex_value_id => l_vsv_row.flex_value_id
,x_attribute_sort_order => l_vsv_row.attribute_sort_order
,x_flex_value_set_id => l_value_set_id
,x_flex_value => p_internal_name
,x_enabled_flag => l_vsv_row.enabled_flag
,x_summary_flag => l_vsv_row.summary_flag
,x_start_date_active => l_vsv_row.start_date_active
,x_end_date_active => l_vsv_row.end_date_active
,x_parent_flex_value_low => l_vsv_row.parent_flex_value_low
,x_parent_flex_value_high => l_vsv_row.parent_flex_value_high
,x_structured_hierarchy_level => l_vsv_row.structured_hierarchy_level
,x_hierarchy_level => l_vsv_row.hierarchy_level
,x_compiled_value_attributes => l_vsv_row.compiled_value_attributes
,x_value_category => l_vsv_row.value_category
,x_attribute1 => l_vsv_row.attribute1
,x_attribute2 => l_vsv_row.attribute2
,x_attribute3 => l_vsv_row.attribute3
,x_attribute4 => l_vsv_row.attribute4
,x_attribute5 => l_vsv_row.attribute5
,x_attribute6 => l_vsv_row.attribute6
,x_attribute7 => l_vsv_row.attribute7
,x_attribute8 => l_vsv_row.attribute8
,x_attribute9 => l_vsv_row.attribute9
,x_attribute10 => l_vsv_row.attribute10
,x_attribute11 => l_vsv_row.attribute11
,x_attribute12 => l_vsv_row.attribute12
,x_attribute13 => l_vsv_row.attribute13
,x_attribute14 => l_vsv_row.attribute14
,x_attribute15 => l_vsv_row.attribute15
,x_attribute16 => l_vsv_row.attribute16
,x_attribute17 => l_vsv_row.attribute17
,x_attribute18 => l_vsv_row.attribute18
,x_attribute19 => l_vsv_row.attribute19
,x_attribute20 => l_vsv_row.attribute20
,x_attribute21 => l_vsv_row.attribute21
,x_attribute22 => l_vsv_row.attribute22
,x_attribute23 => l_vsv_row.attribute23
,x_attribute24 => l_vsv_row.attribute24
,x_attribute25 => l_vsv_row.attribute25
,x_attribute26 => l_vsv_row.attribute26
,x_attribute27 => l_vsv_row.attribute27
,x_attribute28 => l_vsv_row.attribute28
,x_attribute29 => l_vsv_row.attribute29
,x_attribute30 => l_vsv_row.attribute30
,x_attribute31 => l_vsv_row.attribute31
,x_attribute32 => l_vsv_row.attribute32
,x_attribute33 => l_vsv_row.attribute33
,x_attribute34 => l_vsv_row.attribute34
,x_attribute35 => l_vsv_row.attribute35
,x_attribute36 => l_vsv_row.attribute36
,x_attribute37 => l_vsv_row.attribute37
,x_attribute38 => l_vsv_row.attribute38
,x_attribute39 => l_vsv_row.attribute39
,x_attribute40 => l_vsv_row.attribute40
,x_attribute41 => l_vsv_row.attribute41
,x_attribute42 => l_vsv_row.attribute42
,x_attribute43 => l_vsv_row.attribute43
,x_attribute44 => l_vsv_row.attribute44
,x_attribute45 => l_vsv_row.attribute45
,x_attribute46 => l_vsv_row.attribute46
,x_attribute47 => l_vsv_row.attribute47
,x_attribute48 => l_vsv_row.attribute48
,x_attribute49 => l_vsv_row.attribute49
,x_attribute50 => l_vsv_row.attribute50
,x_flex_value_meaning => p_display_name
,x_description => p_description
,x_last_update_date => SYSDATE
,x_last_updated_by => p_last_updated_by
,x_last_update_login => l_last_update_login);
SELECT *
INTO l_vs_row
FROM fnd_flex_value_sets
WHERE flex_value_set_id = cp_value_set_id;
SELECT a.created_by, b.format_type, b.validation_type,
b.alphanumeric_allowed_flag, b.uppercase_only_flag,
b.numeric_mode_enabled_flag, b.security_enabled_flag, b.created_by
INTO l_vsv_created_by, l_vs_format_type, l_vs_validation_type,
l_vs_alpha_numeric_allowed, l_vs_uppercase_only,
l_vs_numeric_mode_enabled, l_vs_security_enabled, l_vs_created_by
FROM fnd_flex_values a, fnd_flex_value_sets b
WHERE a.flex_value_id = cp_vs_value_id
AND a.flex_value_set_id = b.flex_value_set_id;
l_update_sequence BOOLEAN;
SELECT disp_sequence
FROM ego_vs_values_disp_order
WHERE value_set_value_id = cp_flex_value_id
AND value_set_id = cp_flex_id;
SELECT flex_value_id, flex_value_set_id
INTO l_value_set_value_id, l_value_set_id
FROM fnd_flex_values
WHERE flex_value_id = p_value_set_value_id;
SELECT a.flex_value_id, a.flex_value_set_id
INTO l_value_set_value_id, l_value_set_id
FROM fnd_flex_values a, fnd_flex_value_sets b
WHERE a.flex_value_set_id = b.flex_value_set_id
AND a.flex_value = p_value_set_value
AND (b.flex_value_set_id = p_value_set_id OR b.flex_value_set_name = p_value_set_name);
l_update_sequence := FALSE;
l_update_sequence := TRUE;
INSERT INTO EGO_VS_VALUES_DISP_ORDER
(value_set_value_id
,value_set_id
,disp_sequence
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login)
VALUES
(l_value_set_value_id
,l_value_set_id
,p_sequence
,l_owner
,l_Sysdate
,l_owner
,l_Sysdate
,G_CURRENT_LOGIN_ID);
IF l_update_sequence THEN
--
-- Fix for bug#12660306
--
UPDATE EGO_VS_VALUES_DISP_ORDER
SET disp_sequence = p_sequence
,last_updated_by = l_owner
,last_update_date = l_sysdate
,last_update_login = G_CURRENT_LOGIN_ID
WHERE value_set_value_id = l_value_set_value_id
AND value_set_id = l_value_set_id;
SELECT attr_grp_dl.data_level_id data_level_id, dl_meta.data_level_name data_level_name
FROM ego_data_level_b dl_meta, ego_attr_group_dl attr_grp_dl
WHERE attr_grp_dl.data_level_id = dl_meta.data_level_id
AND attr_grp_dl.attr_group_id = cp_attr_group_id
AND NOT EXISTS
(SELECT 1
FROM EGO_OBJ_AG_ASSOCS_B
WHERE classification_code = p_classification_code
AND attr_group_id = cp_attr_group_id
AND data_level_id = dl_meta.data_level_id
);
SELECT Nvl(VARIANT, 'N')
INTO l_variant_flag
FROM EGO_ATTR_GROUPS_V WHERE ATTR_GROUP_ID = p_attr_group_id;
SELECT Count(1)
INTO l_attr_count
FROM EGO_ATTR_GROUPS_V AGV, EGO_ATTRS_V AV
WHERE AGV.APPLICATION_ID = AV.APPLICATION_ID
AND AGV.ATTR_GROUP_TYPE = AV.ATTR_GROUP_TYPE
AND AGV.ATTR_GROUP_NAME = AV.ATTR_GROUP_NAME
AND AGV.ATTR_GROUP_ID = p_attr_group_id;
SELECT COUNT(*)
INTO l_variant_exist_count
FROM EGO_OBJ_ATTR_GRP_ASSOCS_V
WHERE OBJECT_ID = p_object_id
AND CLASSIFICATION_CODE = p_classification_code
AND VARIANT = 'Y'
AND EXISTS (SELECT 'X'
FROM EGO_ATTR_GROUPS_V AGV
WHERE AGV.ATTR_GROUP_ID = p_attr_group_id
AND AGV.VARIANT = 'Y');
SELECT OBJECT_ID
INTO l_dummy_number
FROM FND_OBJECTS
WHERE OBJECT_ID = p_object_id
AND OBJ_NAME = 'EGO_ITEM';
INSERT INTO EGO_OBJ_AG_ASSOCS_B
(
ASSOCIATION_ID
,OBJECT_ID
,CLASSIFICATION_CODE
,DATA_LEVEL
,ATTR_GROUP_ID
,ENABLED_FLAG
,DATA_LEVEL_ID
,VIEW_PRIVILEGE_ID
,EDIT_PRIVILEGE_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
(
x_association_id
,p_object_id
,p_classification_code
,p_data_level
,p_attr_group_id
,p_enabled_flag
,(SELECT data_level_id FROM ego_attr_groups_dl_v WHERE attr_group_id = p_attr_group_id AND data_level_internal_name = p_data_level)
,p_view_privilege_id
,p_edit_privilege_id
,l_Sysdate
,g_current_user_id
,l_Sysdate
,g_current_user_id
,g_current_login_id
) ;
INSERT INTO EGO_OBJ_AG_ASSOCS_B
(
ASSOCIATION_ID,
OBJECT_ID,
CLASSIFICATION_CODE,
DATA_LEVEL,
ATTR_GROUP_ID,
ENABLED_FLAG,
DATA_LEVEL_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
EGO_ASSOCS_S.NEXTVAL,
p_object_id,
p_classification_code,
cursor_rec.data_level_name,
p_attr_group_id,
p_enabled_flag,
cursor_rec.data_level_id,
l_Sysdate,
g_current_user_id,
l_Sysdate,
g_current_user_id,
g_current_login_id
);
PROCEDURE Update_Association (
p_api_version IN NUMBER
,p_association_id IN NUMBER
,p_enabled_flag IN VARCHAR2
,p_view_privilege_id IN NUMBER --ignored for now
,p_edit_privilege_id IN NUMBER --ignored for now
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Association';
SAVEPOINT Update_Association_PUB;
/*DELETE FROM EGO_PAGE_ENTRIES_B
WHERE ASSOCIATION_ID = p_association_id;*/
UPDATE EGO_OBJ_AG_ASSOCS_B
SET ENABLED_FLAG = p_enabled_flag,
VIEW_PRIVILEGE_ID = p_view_privilege_id,
EDIT_PRIVILEGE_ID = p_edit_privilege_id,
LAST_UPDATE_DATE = l_Sysdate,
LAST_UPDATED_BY = g_current_user_id,
LAST_UPDATE_LOGIN = g_current_login_id
WHERE ASSOCIATION_ID = p_association_id;
ROLLBACK TO Update_Association_PUB;
END Update_Association;
PROCEDURE Update_Association (
p_api_version IN NUMBER
,p_object_id IN NUMBER
,p_classification_code IN VARCHAR2
,p_attr_group_id IN NUMBER
,p_enabled_flag IN VARCHAR2
,p_view_privilege_id IN NUMBER --ignored for now
,p_edit_privilege_id IN NUMBER --ignored for now
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Association';
SAVEPOINT Update_Association_PUB;
EGO_EXT_FWK_PUB.Update_Association(
p_api_version => p_api_version
,p_association_id => l_association_id
,p_enabled_flag => p_enabled_flag
,p_view_privilege_id => p_view_privilege_id
,p_edit_privilege_id => p_edit_privilege_id
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO Update_Association_PUB;
END Update_Association;
PROCEDURE Update_Association (
p_api_version IN NUMBER
,p_object_id IN NUMBER
,p_classification_code IN VARCHAR2
,p_application_id IN NUMBER
,p_attr_group_type IN VARCHAR2
,p_attr_group_name IN VARCHAR2
,p_enabled_flag IN VARCHAR2
,p_view_privilege_id IN NUMBER --ignored for now
,p_edit_privilege_id IN NUMBER --ignored for now
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Association';
SAVEPOINT Update_Association_PUB;
EGO_EXT_FWK_PUB.Update_Association(
p_api_version => p_api_version
,p_association_id => l_association_id
,p_enabled_flag => p_enabled_flag
,p_view_privilege_id => p_view_privilege_id
,p_edit_privilege_id => p_edit_privilege_id
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO Update_Association_PUB;
END Update_Association;
PROCEDURE Delete_Association (
p_api_version IN NUMBER
,p_association_id IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,p_force IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Association';
l_is_ok_to_delete BOOLEAN := TRUE;
SELECT *
FROM EGO_OBJ_ATTR_GRP_ASSOCS_V
WHERE (object_id, classification_code, attr_group_id) IN
(SELECT object_id, classification_code, attr_group_id
FROM ego_obj_ag_assocs_b
where association_id = cp_association_id);
SAVEPOINT Delete_Association_PUB;
DELETE FROM EGO_OBJ_AG_ASSOCS_B
WHERE association_id = cr.association_id;
l_is_ok_to_delete := TRUE;
l_dummy_rec.PACKAGE_AND_PROCEDURE := 'EGO_ITEM_CATALOG_PUB.Check_Delete_AttrGroup_Assoc';
l_dummy_rec.PACKAGE_AND_PROCEDURE := 'PA_USER_ATTR_PUB.Check_Delete_Assoc_Ok';
l_dummy_rec.PACKAGE_AND_PROCEDURE := 'EGO_CHANGE_USER_ATTRS_PUB.Check_Delete_Associations'; --Bug 3070807
l_dummy_rec.PACKAGE_AND_PROCEDURE := 'EGO_CHANGE_USER_ATTRS_PUB.Check_Delete_Associations'; --Bug 3070807
l_ok_to_delete VARCHAR2(1) := fnd_api.G_FALSE;
',x_ok_to_delete => :9 '||
',x_return_status => :10 '||
',x_errorcode => :11 '||
',x_msg_count => :12 '||
',x_msg_data => :13 '||
'); END;';
OUT l_ok_to_delete,
OUT x_return_status,
OUT x_errorcode,
OUT x_msg_count,
OUT x_msg_data;
l_is_ok_to_delete := FND_API.To_Boolean(l_ok_to_delete);
l_is_ok_to_delete := FALSE;
l_is_ok_to_delete := FALSE;
l_is_ok_to_delete := TRUE;
IF (l_is_ok_to_delete) THEN
code_debug(l_api_name||' deleting association as conditions are met for assoc :'||cr.association_id);
DELETE FROM EGO_PAGE_ENTRIES_B
WHERE ASSOCIATION_ID = cr.association_id;
DELETE FROM EGO_OBJ_AG_ASSOCS_B
WHERE ASSOCIATION_ID = cr.association_id;
code_debug(l_api_name||' CANNOT delete as prod specific conditions are not met for :'||cr.association_id);
IF (l_is_ok_to_delete) THEN
-- Standard check of p_commit
IF FND_API.To_Boolean(p_commit) THEN
COMMIT WORK;
ROLLBACK TO Delete_Association_PUB;
ROLLBACK TO Delete_Association_PUB;
END Delete_Association;
SELECT EGO_PAGES_S.NEXTVAL INTO x_page_id FROM DUAL;
INSERT INTO EGO_PAGES_B
(
PAGE_ID
,OBJECT_ID
,CLASSIFICATION_CODE
,DATA_LEVEL
,INTERNAL_NAME
,SEQUENCE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
(
x_page_id
,p_object_id
,p_classification_code
,p_data_level
,p_internal_name
,p_sequence
,l_Sysdate
,g_current_user_id
,l_Sysdate
,g_current_user_id
,g_current_login_id
);
INSERT INTO EGO_PAGES_TL
(
PAGE_ID
,DISPLAY_NAME
,DESCRIPTION
,LANGUAGE
,SOURCE_LANG
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
)
SELECT
x_page_id
,p_display_name
,p_description
,L.LANGUAGE_CODE
,USERENV('LANG')
,l_Sysdate
,g_current_user_id
,l_Sysdate
,g_current_user_id
,g_current_login_id
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B');
PROCEDURE Update_Page (
p_api_version IN NUMBER
,p_page_id IN NUMBER
,p_internal_name IN VARCHAR2
,p_display_name IN VARCHAR2
,p_description IN VARCHAR2
,p_sequence IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,p_is_nls_mode IN VARCHAR2 DEFAULT FND_API.G_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Page';
SAVEPOINT Update_Page_PUB;
UPDATE EGO_PAGES_B
SET INTERNAL_NAME = p_internal_name,
SEQUENCE = p_sequence,
LAST_UPDATED_BY = g_current_user_id,
LAST_UPDATE_DATE = l_Sysdate,
LAST_UPDATE_LOGIN = g_current_login_id
WHERE PAGE_ID = p_page_id;
UPDATE EGO_PAGES_TL
SET DISPLAY_NAME = p_display_name,
DESCRIPTION = p_description,
LAST_UPDATED_BY = g_current_user_id,
LAST_UPDATE_DATE = l_Sysdate,
LAST_UPDATE_LOGIN = g_current_login_id,
SOURCE_LANG = USERENV('LANG')
WHERE PAGE_ID = p_page_id
-- AND LANGUAGE = USERENV('LANG');
ROLLBACK TO Update_Page_PUB;
END Update_Page;
PROCEDURE Update_Page (
p_api_version IN NUMBER
,p_object_id IN NUMBER
,p_classification_code IN VARCHAR2
,p_data_level IN VARCHAR2
,p_old_internal_name IN VARCHAR2
,p_new_internal_name IN VARCHAR2
,p_display_name IN VARCHAR2
,p_description IN VARCHAR2
,p_sequence IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Page';
SAVEPOINT Update_Page_PUB;
EGO_EXT_FWK_PUB.Update_Page(
p_api_version => p_api_version
,p_page_id => l_page_id
,p_internal_name => l_new_internal_name
,p_display_name => p_display_name
,p_description => p_description
,p_sequence => p_sequence
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO Update_Page_PUB;
END Update_Page;
PROCEDURE Delete_Page (
p_api_version IN NUMBER
,p_page_id IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Page';
SAVEPOINT Delete_Page_PUB;
DELETE FROM EGO_PAGE_ENTRIES_B
WHERE PAGE_ID = p_page_id;
DELETE FROM EGO_PAGES_B
WHERE PAGE_ID = p_page_id;
DELETE FROM EGO_PAGES_TL
WHERE PAGE_ID = p_page_id;
ROLLBACK TO Delete_Page_PUB;
END Delete_Page;
PROCEDURE Delete_Page (
p_api_version IN NUMBER
,p_object_id IN NUMBER
,p_classification_code IN VARCHAR2
,p_internal_name IN VARCHAR2
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Page';
SAVEPOINT Delete_Page_PUB;
EGO_EXT_FWK_PUB.Delete_Page(
p_api_version => p_api_version
,p_page_id => l_page_id
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO Delete_Page_PUB;
END Delete_Page;
SELECT PAGE_DISPLAY_NAME
FROM EGO_PAGE_ENTRIES_V
WHERE ASSOCIATION_ID = cp_association_id;*/
select display_name PAGE_DISPLAY_NAME from ego_pages_v
where page_id in (select page_id from ego_page_entries_b
where ASSOCIATION_ID = cp_association_id);
INSERT INTO EGO_PAGE_ENTRIES_B
(
PAGE_ID
,ASSOCIATION_ID
,SEQUENCE
,CLASSIFICATION_CODE
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
(
p_page_id
,p_association_id
,p_sequence
,p_classification_code
,l_Sysdate
,g_current_user_id
,l_Sysdate
,g_current_user_id
,g_current_login_id
);
SELECT A.ATTR_GROUP_DISP_NAME
,PE.PAGE_DISPLAY_NAME
,EGO_EXT_FWK_PUB.Get_Class_Meaning(A.OBJECT_ID, PE.CLASSIFICATION_CODE)
INTO l_ag_disp_name
,l_page_disp_name
,l_class_meaning
FROM EGO_OBJ_ATTR_GRP_ASSOCS_V A,
EGO_PAGE_ENTRIES_V PE
WHERE A.ASSOCIATION_ID = PE.ASSOCIATION_ID
AND PE.PAGE_ID = p_page_id
AND PE.ASSOCIATION_ID = p_association_id;
PROCEDURE Update_Page_Entry (
p_api_version IN NUMBER
,p_page_id IN NUMBER
,p_new_association_id IN NUMBER --2995435: Doesnt update association id
,p_old_association_id IN NUMBER --2995435: Doesnt update association id
,p_sequence IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Page_Entry';
SAVEPOINT Update_Page_Entry_PUB;
UPDATE EGO_PAGE_ENTRIES_B
SET SEQUENCE = p_sequence,
ASSOCIATION_ID = p_new_association_id, --2995435: Doesnt update association id
LAST_UPDATED_BY = g_current_user_id,
LAST_UPDATE_DATE = l_Sysdate,
LAST_UPDATE_LOGIN = g_current_login_id
WHERE PAGE_ID = p_page_id
AND ASSOCIATION_ID = p_old_association_id;
ROLLBACK TO Update_Page_Entry_PUB;
SELECT A.ATTR_GROUP_DISP_NAME
,PE.PAGE_DISPLAY_NAME
,EGO_EXT_FWK_PUB.Get_Class_Meaning(A.OBJECT_ID, PE.CLASSIFICATION_CODE)
INTO l_ag_disp_name
,l_page_disp_name
,l_class_meaning
FROM EGO_OBJ_ATTR_GRP_ASSOCS_V A,
EGO_PAGE_ENTRIES_V PE
WHERE A.ASSOCIATION_ID = PE.ASSOCIATION_ID
AND PE.PAGE_ID = p_page_id
AND PE.ASSOCIATION_ID = p_new_association_id;
ROLLBACK TO Update_Page_Entry_PUB;
END Update_Page_Entry;
PROCEDURE Delete_Page_Entry (
p_api_version IN NUMBER
,p_page_id IN NUMBER
,p_association_id IN NUMBER
,p_classification_code IN VARCHAR2
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Page_Entry';
SAVEPOINT Delete_Page_Entry_PUB;
DELETE FROM EGO_PAGE_ENTRIES_B
WHERE PAGE_ID = p_page_id
AND ASSOCIATION_ID = p_association_id
AND CLASSIFICATION_CODE = p_classification_code;
ROLLBACK TO Delete_Page_Entry_PUB;
END Delete_Page_Entry;
SELECT EGO_FUNCTIONS_S.NEXTVAL INTO x_function_id FROM DUAL;
INSERT INTO EGO_FUNCTIONS_B
(
FUNCTION_ID
,INTERNAL_NAME
,FUNCTION_TYPE
,FUNCTION_INFO_1
,FUNCTION_INFO_2
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
(
x_function_id
,p_internal_name
,p_function_type
,p_FUNCTION_INFO_1
,p_FUNCTION_INFO_2
,l_Sysdate
,g_current_user_id
,l_Sysdate
,g_current_user_id
,g_current_login_id
);
INSERT INTO EGO_FUNCTIONS_TL
(
FUNCTION_ID
,DISPLAY_NAME
,DESCRIPTION
,LANGUAGE
,SOURCE_LANG
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
SELECT
x_function_id
,p_display_name
,p_description
,L.LANGUAGE_CODE
,USERENV('LANG')
,l_Sysdate
,g_current_user_id
,l_Sysdate
,g_current_user_id
,g_current_login_id
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B');
PROCEDURE Update_Function (
p_api_version IN NUMBER
,p_function_id IN NUMBER
,p_internal_name IN VARCHAR2
,p_function_info_1 IN VARCHAR2
,p_function_info_2 IN VARCHAR2
,p_display_name IN VARCHAR2
,p_description IN VARCHAR2
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Function';
SAVEPOINT Update_Function_PUB;
UPDATE EGO_FUNCTIONS_B
SET
INTERNAL_NAME = p_internal_name
, FUNCTION_INFO_1 = p_FUNCTION_INFO_1
, FUNCTION_INFO_2 = p_FUNCTION_INFO_2
, LAST_UPDATE_DATE = l_Sysdate
, LAST_UPDATED_BY = g_current_user_id
, LAST_UPDATE_LOGIN = g_current_login_id
WHERE
FUNCTION_ID = p_function_id;
UPDATE EGO_FUNCTIONS_TL
SET
DISPLAY_NAME = p_display_name
, DESCRIPTION = p_description
, LAST_UPDATE_DATE = l_Sysdate
, LAST_UPDATED_BY = g_current_user_id
, LAST_UPDATE_LOGIN = g_current_login_id
, SOURCE_LANG = USERENV('LANG')
WHERE
-- FUNCTION_ID = p_function_id AND LANGUAGE = USERENV('LANG');
ROLLBACK TO Update_Function_PUB;
END Update_Function;
PROCEDURE Delete_Function (
p_api_version IN NUMBER
,p_function_id IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Function';
l_delete_error_flag VARCHAR2(1) := 'N';
SAVEPOINT Delete_Function_PUB;
SELECT count(*)
INTO l_function_use_count
FROM EGO_ACTIONS_B
WHERE FUNCTION_ID = p_function_id;
l_delete_error_flag := 'Y';
SELECT count(*)
INTO l_function_use_count
FROM EGO_ACTION_DISPLAYS_B
WHERE PROMPT_FUNCTION_ID = p_function_id;
l_delete_error_flag := 'Y';
SELECT count(*)
INTO l_function_use_count
FROM EGO_ACTION_DISPLAYS_B
WHERE VISIBILITY_FUNC_ID = p_function_id;
l_delete_error_flag := 'Y';
DELETE FROM EGO_FUNCTIONS_B
WHERE
FUNCTION_ID = p_function_id;
DELETE FROM EGO_FUNCTIONS_TL
WHERE
FUNCTION_ID = p_function_id;
ROLLBACK TO Delete_Function_PUB;
IF (l_delete_error_flag = 'Y') THEN
-- Let the calling API know that this Function is being used
x_msg_data := 'Executing - '||G_PKG_NAME||'.'||l_api_name||' FunctionInUse';
END Delete_Function;
SELECT EGO_ACTIONS_S.NEXTVAL INTO x_action_id FROM DUAL;
SELECT COUNT(*)
INTO l_validate
FROM EGO_ACTIONS_B
WHERE SEQUENCE = p_sequence
AND OBJECT_ID = p_object_id
AND CLASSIFICATION_CODE = p_classification_code
AND ATTR_GROUP_ID = p_attr_group_id;
SELECT COUNT(*)
INTO l_validate
FROM EGO_ACTIONS_B
WHERE ACTION_NAME = p_action_name
AND OBJECT_ID = p_object_id
AND CLASSIFICATION_CODE = p_classification_code
AND ATTR_GROUP_ID = p_attr_group_id;
INSERT INTO EGO_ACTIONS_B
(
ACTION_ID
,OBJECT_ID
,CLASSIFICATION_CODE
,ATTR_GROUP_ID
,SEQUENCE
,ACTION_NAME
,FUNCTION_ID
,ENABLE_KEY_ATTRIBUTES
,SECURITY_PRIVILEGE_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
(
x_action_id
,p_object_id
,p_classification_code
,p_attr_group_id
,p_sequence
,p_action_name
,p_function_id
,p_enable_key_attrs
,p_security_privilege_id
,l_Sysdate
,g_current_user_id
,l_Sysdate
,g_current_user_id
,g_current_login_id
);
INSERT INTO EGO_ACTIONS_TL
(
ACTION_ID
,DESCRIPTION
,LANGUAGE
,SOURCE_LANG
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
SELECT
x_action_id
,p_description
,L.LANGUAGE_CODE
,USERENV('LANG')
,l_Sysdate
,g_current_user_id
,l_Sysdate
,g_current_user_id
,g_current_login_id
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B');
PROCEDURE Update_Action (
p_api_version IN NUMBER
,p_action_id IN NUMBER
,p_sequence IN NUMBER
,p_action_name IN VARCHAR2
,p_description IN VARCHAR2
,p_function_id IN NUMBER
,p_enable_key_attrs IN VARCHAR2 DEFAULT NULL
,p_security_privilege_id IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Action';
SAVEPOINT Update_Action_PUB;
SELECT OBJECT_ID, CLASSIFICATION_CODE, ATTR_GROUP_ID
INTO l_object_id, l_classification_code, l_attr_group_id
FROM EGO_ACTIONS_B
WHERE ACTION_ID = p_action_id;
SELECT COUNT(*)
INTO l_validate
FROM EGO_ACTIONS_B
WHERE SEQUENCE = p_sequence
AND ACTION_ID <> p_action_id
AND OBJECT_ID = l_object_id
AND CLASSIFICATION_CODE = l_classification_code
AND ATTR_GROUP_ID = l_attr_group_id;
SELECT COUNT(*)
INTO l_validate
FROM EGO_ACTIONS_B
WHERE ACTION_NAME = p_action_name
AND ACTION_ID <> p_action_id
AND OBJECT_ID = l_object_id
AND CLASSIFICATION_CODE = l_classification_code
AND ATTR_GROUP_ID = l_attr_group_id;
SELECT FUNCTION_ID
INTO l_old_function_id
FROM EGO_ACTIONS_B
WHERE ACTION_ID = p_action_id;
SELECT COUNT(*)
INTO l_mapping_count
FROM EGO_MAPPINGS_B
WHERE MAPPED_OBJ_PK1_VAL = TO_CHAR(p_action_id)
AND FUNCTION_ID = l_old_function_id
AND MAPPED_OBJ_TYPE = l_mapped_obj_type;
EGO_EXT_FWK_PUB.Delete_Func_Mapping(p_api_version, l_old_function_id,
l_mapped_obj_type, p_action_id, null,
null, x_return_status, x_errorcode,
x_msg_count, x_msg_data);
UPDATE EGO_ACTIONS_B
SET
SEQUENCE = p_sequence
, ACTION_NAME = p_action_name
, FUNCTION_ID = p_function_id
, SECURITY_PRIVILEGE_ID = p_security_privilege_id
, LAST_UPDATE_DATE = l_Sysdate
, LAST_UPDATED_BY = g_current_user_id
, LAST_UPDATE_LOGIN = g_current_login_id
, ENABLE_KEY_ATTRIBUTES = p_enable_key_attrs
WHERE
ACTION_ID = p_action_id;
UPDATE EGO_ACTIONS_TL
SET
DESCRIPTION = p_description
, LAST_UPDATE_DATE = l_Sysdate
, LAST_UPDATED_BY = g_current_user_id
, LAST_UPDATE_LOGIN = g_current_login_id
, SOURCE_LANG = USERENV('LANG')
WHERE
-- ACTION_ID = p_action_id AND LANGUAGE = USERENV('LANG');
ROLLBACK TO Update_Action_PUB;
ROLLBACK TO Update_Action_PUB;
ROLLBACK TO Update_Action_PUB;
END Update_Action;
PROCEDURE Delete_Action (
p_api_version IN NUMBER
,p_action_id IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Action';
SAVEPOINT Delete_Action_PUB;
SELECT FUNCTION_ID
INTO l_function_id
FROM EGO_ACTIONS_B
WHERE ACTION_ID = p_action_id;
DELETE FROM EGO_ACTIONS_B
WHERE
ACTION_ID = p_action_id;
DELETE FROM EGO_ACTIONS_TL
WHERE
ACTION_ID = p_action_id;
SELECT COUNT(*)
INTO l_mapping_count
FROM EGO_MAPPINGS_B
WHERE MAPPED_OBJ_PK1_VAL = TO_CHAR(p_action_id)
AND FUNCTION_ID = l_function_id
AND MAPPED_OBJ_TYPE = l_mapped_obj_type;
EGO_EXT_FWK_PUB.Delete_Func_Mapping(p_api_version, l_function_id, l_mapped_obj_type,
p_action_id, null, null, x_return_status,
x_errorcode, x_msg_count, x_msg_data);
SELECT COUNT(*)
INTO l_visibility_count
FROM EGO_ACTIONS_DL
WHERE ACTION_ID = p_action_id;
code_debug ('ENTERED THE FUCNTION Delete_Action l_visibility_count'||l_visibility_count);
Delete_Action_Data_Level(p_api_version,p_action_id,null, null, x_return_status,x_errorcode, x_msg_count, x_msg_data);
ROLLBACK TO Delete_Action_PUB;
END Delete_Action;
INSERT INTO EGO_ACTIONS_DL
(
ACTION_ID
,DATA_LEVEL_ID
,VISIBILITY_FLAG
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
(
p_action_id
,p_data_level_id
,p_visibility_flag
,l_Sysdate
,g_current_user_id
,l_Sysdate
,g_current_user_id
,g_current_login_id
);
INSERT INTO EGO_ACTION_DISPLAYS_B
(
ACTION_ID
,EXECUTION_METHOD
,DISPLAY_STYLE
,PROMPT_APPLICATION_ID
,PROMPT_MESSAGE_NAME
,VISIBILITY_FLAG
,PROMPT_FUNCTION_ID
,VISIBILITY_FUNC_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
(
p_action_id
,P_EXEC_CODE
,p_display_style
,p_prompt_application_id
,p_prompt_message_name
,p_visibility_flag
,p_prompt_function_id
,p_visibility_func_id
,l_Sysdate
,g_current_user_id
,l_Sysdate
,g_current_user_id
,g_current_login_id
);
INSERT INTO EGO_ACTION_DISPLAYS_B
(
ACTION_ID
,EXECUTION_METHOD
,EXECUTION_TRIGGER
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
(
p_action_id
,'T'
,p_trigger_code
,l_Sysdate
,g_current_user_id
,l_Sysdate
,g_current_user_id
,g_current_login_id
);
PROCEDURE Update_Action_Display (
p_api_version IN NUMBER
,p_action_id IN NUMBER
,P_EXEC_CODE IN VARCHAR2 := 'U'
,p_display_style IN VARCHAR2
,p_prompt_application_id IN NUMBER
,p_prompt_message_name IN VARCHAR2
,p_visibility_flag IN VARCHAR2
,p_prompt_function_id IN NUMBER
,p_visibility_func_id IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Action_Display';
SAVEPOINT Update_Action_Display_PUB;
SELECT COUNT(*)
INTO l_disp_check_count
FROM EGO_ACTION_DISPLAYS_B
WHERE ACTION_ID = p_action_id;
SELECT PROMPT_FUNCTION_ID
INTO l_old_prompt_func_id
FROM EGO_ACTION_DISPLAYS_B
WHERE ACTION_ID = p_action_id;
SELECT COUNT(*)
INTO l_mapping_count
FROM EGO_MAPPINGS_B
WHERE MAPPED_OBJ_PK1_VAL = TO_CHAR(p_action_id)
AND FUNCTION_ID = l_old_prompt_func_id
AND MAPPED_OBJ_TYPE = l_prompt_obj_type;
EGO_EXT_FWK_PUB.Delete_Func_Mapping(p_api_version, l_old_prompt_func_id,
l_prompt_obj_type, p_action_id, null,
null, x_return_status, x_errorcode,
x_msg_count, x_msg_data);
SELECT VISIBILITY_FUNC_ID
INTO l_old_vis_func_id
FROM EGO_ACTION_DISPLAYS_B
WHERE ACTION_ID = p_action_id;
SELECT COUNT(*)
INTO l_mapping_count
FROM EGO_MAPPINGS_B
WHERE MAPPED_OBJ_PK1_VAL = to_char(p_action_id)
AND FUNCTION_ID = l_old_vis_func_id
AND MAPPED_OBJ_TYPE = l_vis_obj_type;
EGO_EXT_FWK_PUB.Delete_Func_Mapping(p_api_version, l_old_vis_func_id,
l_vis_obj_type, p_action_id, null,
null, x_return_status, x_errorcode,
x_msg_count, x_msg_data);
UPDATE EGO_ACTION_DISPLAYS_B
SET
EXECUTION_METHOD = P_EXEC_CODE
,DISPLAY_STYLE = p_display_style
,PROMPT_APPLICATION_ID = p_prompt_application_id
,PROMPT_MESSAGE_NAME = p_prompt_message_name
,VISIBILITY_FLAG = p_visibility_flag
,PROMPT_FUNCTION_ID = p_prompt_function_id
,VISIBILITY_FUNC_ID = p_visibility_func_id
,LAST_UPDATE_DATE = l_Sysdate
,LAST_UPDATED_BY = g_current_user_id
,LAST_UPDATE_LOGIN = g_current_login_id
,EXECUTION_TRIGGER = null
WHERE
ACTION_ID = p_action_id;
ROLLBACK TO Update_Action_Display_PUB;
END Update_Action_Display;
PROCEDURE Update_Action_Display (
p_api_version IN NUMBER
,p_action_id IN NUMBER
,p_trigger_code IN VARCHAR2
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Action_Display';
SAVEPOINT Update_Action_Display_PUB;
SELECT COUNT(*)
INTO l_disp_check_count
FROM EGO_ACTION_DISPLAYS_B
WHERE ACTION_ID = p_action_id;
SELECT PROMPT_FUNCTION_ID
INTO l_old_prompt_func_id
FROM EGO_ACTION_DISPLAYS_B
WHERE ACTION_ID = p_action_id;
SELECT COUNT(*)
INTO l_mapping_count
FROM EGO_MAPPINGS_B
WHERE MAPPED_OBJ_PK1_VAL = TO_CHAR(p_action_id)
AND FUNCTION_ID = l_old_prompt_func_id
AND MAPPED_OBJ_TYPE = l_prompt_obj_type;
EGO_EXT_FWK_PUB.Delete_Func_Mapping(p_api_version, l_old_prompt_func_id,
l_prompt_obj_type, p_action_id, null,
null, x_return_status, x_errorcode,
x_msg_count, x_msg_data);
SELECT VISIBILITY_FUNC_ID
INTO l_old_vis_func_id
FROM EGO_ACTION_DISPLAYS_B
WHERE ACTION_ID = p_action_id;
SELECT COUNT(*)
INTO l_mapping_count
FROM EGO_MAPPINGS_B
WHERE MAPPED_OBJ_PK1_VAL = TO_CHAR(p_action_id)
AND FUNCTION_ID = l_old_vis_func_id
AND MAPPED_OBJ_TYPE = l_vis_obj_type;
EGO_EXT_FWK_PUB.Delete_Func_Mapping(p_api_version, l_old_vis_func_id,
l_vis_obj_type, p_action_id, null,
null, x_return_status, x_errorcode,
x_msg_count, x_msg_data);
UPDATE EGO_ACTION_DISPLAYS_B
SET
EXECUTION_METHOD = 'T'
,EXECUTION_TRIGGER = p_trigger_code
,DISPLAY_STYLE = NULL
,PROMPT_APPLICATION_ID = NULL
,PROMPT_MESSAGE_NAME = NULL
,VISIBILITY_FLAG = NULL
,PROMPT_FUNCTION_ID = NULL
,VISIBILITY_FUNC_ID = NULL
,LAST_UPDATE_DATE = l_Sysdate
,LAST_UPDATED_BY = g_current_user_id
,LAST_UPDATE_LOGIN = g_current_login_id
WHERE
ACTION_ID = p_action_id;
ROLLBACK TO Update_Action_Display_PUB;
END Update_Action_Display;
PROCEDURE Delete_Action_Display (
p_api_version IN NUMBER
,p_action_id IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Action';
SAVEPOINT Delete_Action_Display_PUB;
SELECT PROMPT_FUNCTION_ID
INTO l_prompt_func_id
FROM EGO_ACTION_DISPLAYS_B
WHERE ACTION_ID = p_action_id;
SELECT VISIBILITY_FUNC_ID
INTO l_vis_func_id
FROM EGO_ACTION_DISPLAYS_B
WHERE ACTION_ID = p_action_id;
DELETE FROM EGO_ACTION_DISPLAYS_B
WHERE ACTION_ID = p_action_id;
SELECT COUNT(*)
INTO l_mapping_count
FROM EGO_MAPPINGS_B
WHERE MAPPED_OBJ_PK1_VAL = TO_CHAR(p_action_id)
AND FUNCTION_ID = l_prompt_func_id
AND MAPPED_OBJ_TYPE = l_prompt_obj_type;
EGO_EXT_FWK_PUB.Delete_Func_Mapping(p_api_version, l_prompt_func_id,
l_prompt_obj_type, p_action_id, null,
null, x_return_status, x_errorcode,
x_msg_count, x_msg_data);
SELECT COUNT(*)
INTO l_mapping_count
FROM EGO_MAPPINGS_B
WHERE MAPPED_OBJ_PK1_VAL = TO_CHAR(p_action_id)
AND FUNCTION_ID = l_vis_func_id
AND MAPPED_OBJ_TYPE = l_vis_obj_type;
EGO_EXT_FWK_PUB.Delete_Func_Mapping(p_api_version, l_vis_func_id,
l_vis_obj_type, p_action_id, null,
null, x_return_status, x_errorcode,
x_msg_count, x_msg_data);
ROLLBACK TO Delete_Action_Display_PUB;
END Delete_Action_Display;
SELECT EGO_FUNC_PARAMS_S.NEXTVAL INTO x_function_param_id FROM DUAL;
INSERT INTO EGO_FUNC_PARAMS_B
(
FUNC_PARAM_ID
,FUNCTION_ID
,SEQUENCE
,INTERNAL_NAME
,DATA_TYPE
,PARAM_TYPE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
(
x_function_param_id
,p_function_id
,p_sequence
,p_internal_name
,p_data_type
,p_param_type
,l_Sysdate
,g_current_user_id
,l_Sysdate
,g_current_user_id
,g_current_login_id
);
INSERT INTO EGO_FUNC_PARAMS_TL
(
FUNC_PARAM_ID
,DISPLAY_NAME
,LANGUAGE
,SOURCE_LANG
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
SELECT
x_function_param_id
,p_display_name
,L.LANGUAGE_CODE
,USERENV('LANG')
,l_Sysdate
,g_current_user_id
,l_Sysdate
,g_current_user_id
,g_current_login_id
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B');
PROCEDURE Update_Function_Param (
p_api_version IN NUMBER
,p_function_param_id IN NUMBER
,p_sequence IN NUMBER
,p_internal_name IN VARCHAR2
,p_display_name IN VARCHAR2
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Function_Param';
SAVEPOINT Update_Function_Param_PUB;
UPDATE EGO_FUNC_PARAMS_B
SET
SEQUENCE = p_sequence
,INTERNAL_NAME = p_internal_name
,LAST_UPDATE_DATE = l_Sysdate
,LAST_UPDATED_BY = g_current_user_id
,LAST_UPDATE_LOGIN = g_current_login_id
WHERE
FUNC_PARAM_ID = p_function_param_id;
UPDATE EGO_FUNC_PARAMS_TL
SET
DISPLAY_NAME = p_display_name
,LAST_UPDATE_DATE = l_Sysdate
,LAST_UPDATED_BY = g_current_user_id
,LAST_UPDATE_LOGIN = g_current_login_id
,SOURCE_LANG = USERENV('LANG')
WHERE
FUNC_PARAM_ID = p_function_param_id AND
--userenv('LANG') = LANGUAGE;
ROLLBACK TO Update_Function_Param_PUB;
END Update_Function_Param;
PROCEDURE Delete_Function_Param (
p_api_version IN NUMBER
,p_function_param_id IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Function_Param';
SAVEPOINT Delete_Function_Param_PUB;
DELETE FROM EGO_FUNC_PARAMS_B
WHERE
FUNC_PARAM_ID = p_function_param_id;
DELETE FROM EGO_FUNC_PARAMS_TL
WHERE
FUNC_PARAM_ID = p_function_param_id;
DELETE FROM EGO_MAPPINGS_B
WHERE
FUNC_PARAM_ID = p_function_param_id;
ROLLBACK TO Delete_Function_Param_PUB;
END Delete_Function_Param;
INSERT INTO EGO_MAPPINGS_B
(
FUNCTION_ID
,MAPPED_OBJ_TYPE
,MAPPED_OBJ_PK1_VAL
,FUNC_PARAM_ID
,MAPPED_TO_GROUP_TYPE
,MAPPED_TO_GROUP_PK1
,MAPPED_TO_GROUP_PK2
,MAPPED_TO_GROUP_PK3
,MAPPED_ATTRIBUTE
,MAPPED_UOM_PARAMETER
,VALUE_UOM_SOURCE
,FIXED_UOM
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
(
p_function_id
,p_mapped_obj_type
,p_mapped_obj_pk1_value
,p_func_param_id
,p_mapping_group_type
,p_mapping_group_pk1
,p_mapping_group_pk2
,p_mapping_group_pk3
,p_mapping_value
,p_mapped_uom_parameter
,p_value_uom_source
,p_fixed_uom
,l_Sysdate
,g_current_user_id
,l_Sysdate
,g_current_user_id
,g_current_login_id
);
PROCEDURE Update_Mapping (
p_api_version IN NUMBER
,p_function_id IN NUMBER
,p_mapped_obj_type IN VARCHAR2
,p_mapped_obj_pk1_value IN VARCHAR2
,p_func_param_id IN NUMBER
,p_mapping_group_type IN VARCHAR2
,p_mapping_group_pk1 IN VARCHAR2
,p_mapping_group_pk2 IN VARCHAR2
,p_mapping_group_pk3 IN VARCHAR2
,p_mapping_value IN VARCHAR2
,p_new_func_param_id IN NUMBER := NULL
,p_new_mapping_group_pk1 IN VARCHAR2 := NULL
,p_new_mapping_group_pk2 IN VARCHAR2 := NULL
,p_new_mapping_group_pk3 IN VARCHAR2 := NULL
,p_new_mapping_value IN VARCHAR2 := NULL
,p_mapped_uom_parameter IN VARCHAR2 := NULL
,p_value_uom_source IN VARCHAR2 := NULL
,p_fixed_uom IN VARCHAR2 := NULL
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Mapping';
SAVEPOINT Update_Mapping_PUB;
UPDATE EGO_MAPPINGS_B
SET
MAPPED_TO_GROUP_TYPE = p_mapping_group_type
,MAPPED_TO_GROUP_PK1 = p_new_mapping_group_pk1
,MAPPED_TO_GROUP_PK2 = p_new_mapping_group_pk2
,MAPPED_TO_GROUP_PK3 = p_new_mapping_group_pk3
,MAPPED_ATTRIBUTE = p_new_mapping_value
,MAPPED_UOM_PARAMETER = p_mapped_uom_parameter
,VALUE_UOM_SOURCE = p_value_uom_source
,FIXED_UOM = p_fixed_uom
,FUNC_PARAM_ID = NVL(p_new_func_param_id, p_func_param_id)
,LAST_UPDATE_DATE = l_Sysdate
,LAST_UPDATED_BY = g_current_user_id
,LAST_UPDATE_LOGIN = g_current_login_id
WHERE
FUNCTION_ID = p_function_id AND
MAPPED_OBJ_TYPE = p_mapped_obj_type AND
MAPPED_OBJ_PK1_VAL = p_mapped_obj_pk1_value AND
FUNC_PARAM_ID = p_func_param_id AND
MAPPED_TO_GROUP_PK1 = p_mapping_group_pk1 AND
MAPPED_TO_GROUP_PK2 = p_mapping_group_pk2 AND
MAPPED_TO_GROUP_PK3 = p_mapping_group_pk3 AND
MAPPED_ATTRIBUTE = p_mapping_value;
UPDATE EGO_MAPPINGS_B
SET
MAPPED_TO_GROUP_TYPE = p_mapping_group_type
,MAPPED_TO_GROUP_PK1 = p_mapping_group_pk1
,MAPPED_TO_GROUP_PK2 = p_mapping_group_pk2
,MAPPED_TO_GROUP_PK3 = p_mapping_group_pk3
,MAPPED_ATTRIBUTE = p_mapping_value
,MAPPED_UOM_PARAMETER = p_mapped_uom_parameter
,VALUE_UOM_SOURCE = p_value_uom_source
,FIXED_UOM = p_fixed_uom
,FUNC_PARAM_ID = NVL(p_new_func_param_id, p_func_param_id)
,LAST_UPDATE_DATE = l_Sysdate
,LAST_UPDATED_BY = g_current_user_id
,LAST_UPDATE_LOGIN = g_current_login_id
WHERE
FUNCTION_ID = p_function_id AND
MAPPED_OBJ_TYPE = p_mapped_obj_type AND
MAPPED_OBJ_PK1_VAL = p_mapped_obj_pk1_value AND
FUNC_PARAM_ID = p_func_param_id;
ROLLBACK TO Update_Mapping_PUB;
END Update_Mapping;
PROCEDURE Update_Mapping (
p_api_version IN NUMBER
,p_function_id IN NUMBER
,p_mapped_obj_type IN VARCHAR2
,p_mapped_obj_pk1_value IN VARCHAR2
,p_func_param_id IN NUMBER
,p_attr_group_id IN NUMBER
,p_mapping_value IN VARCHAR2
,p_mapping_group_pk1 IN VARCHAR2 := NULL
,p_mapping_group_pk2 IN VARCHAR2 := NULL
,p_mapping_group_pk3 IN VARCHAR2 := NULL
,p_new_func_param_id IN NUMBER := NULL
,p_new_mapping_value IN VARCHAR2 := NULL
,p_mapped_uom_parameter IN VARCHAR2 := NULL
,p_value_uom_source IN VARCHAR2 := NULL
,p_fixed_uom IN VARCHAR2 := NULL
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Mapping';
SAVEPOINT Update_Mapping_PUB;
EGO_EXT_FWK_PUB.Update_Mapping (
p_api_version => p_api_version
,p_function_id => p_function_id
,p_mapped_obj_type => p_mapped_obj_type
,p_mapped_obj_pk1_value => p_mapped_obj_pk1_value
,p_func_param_id => p_func_param_id
,p_mapping_group_type => l_mapping_group_type
,p_mapping_group_pk1 => p_mapping_group_pk1
,p_mapping_group_pk2 => p_mapping_group_pk2
,p_mapping_group_pk3 => p_mapping_group_pk3
,p_mapping_value => p_mapping_value
,p_new_mapping_group_pk1 => l_application_id
,p_new_mapping_group_pk2 => l_attr_grp_type
,p_new_mapping_group_pk3 => l_attr_grp_name
,p_new_mapping_value => p_new_mapping_value
,p_new_func_param_id => p_new_func_param_id
,p_mapped_uom_parameter => p_mapped_uom_parameter
,p_value_uom_source => p_value_uom_source
,p_fixed_uom => p_fixed_uom
,p_init_msg_list => p_init_msg_list
,p_commit => p_commit
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO Update_Mapping_PUB;
END Update_Mapping;
PROCEDURE Delete_Func_Mapping (
p_api_version IN NUMBER
,p_function_id IN NUMBER
,p_mapped_obj_type IN VARCHAR2
,p_mapped_obj_pk1_value IN VARCHAR2
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Func_Mapping';
SAVEPOINT Update_Mapping_PUB;
DELETE FROM EGO_MAPPINGS_B
WHERE
FUNCTION_ID = p_function_id AND
MAPPED_OBJ_TYPE = p_mapped_obj_type AND
MAPPED_OBJ_PK1_VAL = p_mapped_obj_pk1_value;
ROLLBACK TO Delete_Func_Mapping_PUB;
END Delete_Func_Mapping;
PROCEDURE Delete_Func_Param_Mapping (
p_api_version IN NUMBER
,p_function_id IN NUMBER
,p_mapped_obj_type IN VARCHAR2
,p_mapped_obj_pk1_value IN VARCHAR2
,p_func_param_id IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Func_Param_Mapping';
SAVEPOINT Delete_Func_Param_Mapping_PUB;
DELETE FROM EGO_MAPPINGS_B
WHERE
FUNCTION_ID = p_function_id AND
MAPPED_OBJ_TYPE = p_mapped_obj_type AND
MAPPED_OBJ_PK1_VAL = p_mapped_obj_pk1_value AND
FUNC_PARAM_ID = p_func_param_id;
ROLLBACK TO Delete_Func_Param_Mapping_PUB;
END Delete_Func_Param_Mapping;
SELECT EGO_ACTION_GROUPS_S.NEXTVAL INTO x_action_group_id FROM DUAL;
INSERT INTO EGO_ACTION_GROUPS_B
(
ACTION_GROUP_ID
,OBJECT_ID
,CLASSIFICATION_CODE
,SEQUENCE
,INTERNAL_NAME
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
(
x_action_group_id
,p_object_id
,p_classification_code
,p_sequence
,p_internal_name
,l_Sysdate
,g_current_user_id
,l_Sysdate
,g_current_user_id
,g_current_login_id
);
INSERT INTO EGO_ACTION_GROUPS_TL
(
ACTION_GROUP_ID
,DISPLAY_NAME
,DESCRIPTION
,LANGUAGE
,SOURCE_LANG
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
SELECT
x_action_group_id
,p_display_name
,p_description
,L.LANGUAGE_CODE
,USERENV('LANG')
,l_Sysdate
,g_current_user_id
,l_Sysdate
,g_current_user_id
,g_current_login_id
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG in ('I', 'B');
PROCEDURE Update_Action_Group (
p_api_version IN NUMBER
,p_action_group_id IN NUMBER
,p_sequence IN NUMBER
,p_internal_name IN VARCHAR2
,p_display_name IN VARCHAR2
,p_description IN VARCHAR2
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Action_Group';
SAVEPOINT Update_Action_Group_PUB;
UPDATE EGO_ACTION_GROUPS_B
SET
SEQUENCE = p_sequence
, INTERNAL_NAME = p_internal_name
, LAST_UPDATE_DATE = l_Sysdate
, LAST_UPDATED_BY = g_current_user_id
, LAST_UPDATE_LOGIN = g_current_login_id
WHERE
ACTION_GROUP_ID = p_action_group_id;
UPDATE EGO_ACTION_GROUPS_TL
SET
DESCRIPTION = p_description
, DISPLAY_NAME = p_display_name
, LAST_UPDATE_DATE = l_Sysdate
, LAST_UPDATED_BY = g_current_user_id
, LAST_UPDATE_LOGIN = g_current_login_id
, SOURCE_LANG = USERENV('LANG')
WHERE
-- ACTION_GROUP_ID = p_action_group_id AND LANGUAGE = USERENV('LANG');
ROLLBACK TO Update_Action_Group_PUB;
END Update_Action_Group;
PROCEDURE Delete_Action_Group (
p_api_version IN NUMBER
,p_action_group_id IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Action_Group';
SAVEPOINT Delete_Action_Group_PUB;
DELETE FROM EGO_ACTN_GRP_ENTRIES_B
WHERE
ACTION_GROUP_ID = p_action_group_id;
DELETE FROM EGO_ACTION_GROUPS_B
WHERE
ACTION_GROUP_ID = p_action_group_id;
DELETE FROM EGO_ACTION_GROUPS_TL
WHERE
ACTION_GROUP_ID = p_action_group_id;
ROLLBACK TO Delete_Action_Group_PUB;
END Delete_Action_Group;
SELECT COUNT(ACTION_ID)
INTO l_exist_action_count
FROM EGO_ACTN_GRP_ENTRIES_B
WHERE ACTION_GROUP_ID = p_action_group_id
AND ACTION_ID = p_action_id;
INSERT INTO EGO_ACTN_GRP_ENTRIES_B
(
ACTION_GROUP_ID
,ACTION_ID
,SEQUENCE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
(
p_action_group_id
,p_action_id
,p_sequence
,l_Sysdate
,g_current_user_id
,l_Sysdate
,g_current_user_id
,g_current_login_id
);
PROCEDURE Update_Action_Group_Entry (
p_api_version IN NUMBER
,p_action_group_id IN NUMBER
,p_action_id IN NUMBER
,p_sequence IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Action_Group_Entry';
SAVEPOINT Update_Action_Group_Entry_PUB;
UPDATE EGO_ACTN_GRP_ENTRIES_B
SET SEQUENCE = p_sequence
WHERE ACTION_GROUP_ID = p_action_group_id
AND ACTION_ID = p_action_id;
ROLLBACK TO Update_Action_Group_Entry_PUB;
END Update_Action_Group_Entry;
PROCEDURE Delete_Action_Group_Entry (
p_api_version IN NUMBER
,p_action_group_id IN NUMBER
,p_action_id IN NUMBER
,p_init_msg_list IN VARCHAR2 := fnd_api.g_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Action_Group_Entry';
SAVEPOINT Update_Action_Group_Entry_PUB;
DELETE FROM EGO_ACTN_GRP_ENTRIES_B
WHERE
ACTION_GROUP_ID = p_action_group_id AND
ACTION_ID = p_action_id;
ROLLBACK TO Delete_Action_Group_Entry_PUB;
END Delete_Action_Group_Entry;
delete from EGO_ACTIONS_TL T
where not exists
(select NULL
from EGO_ACTIONS_B B
where B.ACTION_ID = T.ACTION_ID
);
update EGO_ACTIONS_TL T set (
DESCRIPTION
) = (select
B.DESCRIPTION
from EGO_ACTIONS_TL B
where B.ACTION_ID = T.ACTION_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.ACTION_ID,
T.LANGUAGE
) in (select
SUBT.ACTION_ID,
SUBT.LANGUAGE
from EGO_ACTIONS_TL SUBB, EGO_ACTIONS_TL SUBT
where SUBB.ACTION_ID = SUBT.ACTION_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
));
insert into EGO_ACTIONS_TL (
ACTION_ID,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG)
select
B.ACTION_ID,
B.DESCRIPTION,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from EGO_ACTIONS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from EGO_ACTIONS_TL T
where T.ACTION_ID = B.ACTION_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
delete from EGO_ACTION_GROUPS_TL T
where not exists
(select NULL
from EGO_ACTION_GROUPS_B B
where B.ACTION_GROUP_ID = T.ACTION_GROUP_ID
);
update EGO_ACTION_GROUPS_TL T set (
DISPLAY_NAME,
DESCRIPTION
) = (select
B.DISPLAY_NAME,
B.DESCRIPTION
from EGO_ACTION_GROUPS_TL B
where B.ACTION_GROUP_ID = T.ACTION_GROUP_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.ACTION_GROUP_ID,
T.LANGUAGE
) in (select
SUBT.ACTION_GROUP_ID,
SUBT.LANGUAGE
from EGO_ACTION_GROUPS_TL SUBB, EGO_ACTION_GROUPS_TL SUBT
where SUBB.ACTION_GROUP_ID = SUBT.ACTION_GROUP_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
));
insert into EGO_ACTION_GROUPS_TL (
ACTION_GROUP_ID,
DISPLAY_NAME,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
B.ACTION_GROUP_ID,
B.DISPLAY_NAME,
B.DESCRIPTION,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from EGO_ACTION_GROUPS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from EGO_ACTION_GROUPS_TL T
where T.ACTION_GROUP_ID = B.ACTION_GROUP_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
delete from EGO_FUNCTIONS_TL T
where not exists
(select NULL
from EGO_FUNCTIONS_B B
where B.FUNCTION_ID = T.FUNCTION_ID
);
update EGO_FUNCTIONS_TL T set (
DISPLAY_NAME,
DESCRIPTION
) = (select
B.DISPLAY_NAME,
B.DESCRIPTION
from EGO_FUNCTIONS_TL B
where B.FUNCTION_ID = T.FUNCTION_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.FUNCTION_ID,
T.LANGUAGE
) in (select
SUBT.FUNCTION_ID,
SUBT.LANGUAGE
from EGO_FUNCTIONS_TL SUBB, EGO_FUNCTIONS_TL SUBT
where SUBB.FUNCTION_ID = SUBT.FUNCTION_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
));
insert into EGO_FUNCTIONS_TL (
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CREATED_BY,
FUNCTION_ID,
DISPLAY_NAME,
DESCRIPTION,
LANGUAGE,
SOURCE_LANG
) select
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
B.CREATED_BY,
B.FUNCTION_ID,
B.DISPLAY_NAME,
B.DESCRIPTION,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from EGO_FUNCTIONS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from EGO_FUNCTIONS_TL T
where T.FUNCTION_ID = B.FUNCTION_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
delete from EGO_FUNC_PARAMS_TL T
where not exists
(select NULL
from EGO_FUNC_PARAMS_B B
where B.FUNC_PARAM_ID = T.FUNC_PARAM_ID
);
update EGO_FUNC_PARAMS_TL T set (
DISPLAY_NAME,
DESCRIPTION
) = (select
B.DISPLAY_NAME,
B.DESCRIPTION
from EGO_FUNC_PARAMS_TL B
where B.FUNC_PARAM_ID = T.FUNC_PARAM_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.FUNC_PARAM_ID,
T.LANGUAGE
) in (select
SUBT.FUNC_PARAM_ID,
SUBT.LANGUAGE
from EGO_FUNC_PARAMS_TL SUBB, EGO_FUNC_PARAMS_TL SUBT
where SUBB.FUNC_PARAM_ID = SUBT.FUNC_PARAM_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
));
insert into EGO_FUNC_PARAMS_TL (
FUNC_PARAM_ID,
DISPLAY_NAME,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
B.FUNC_PARAM_ID,
B.DISPLAY_NAME,
B.DESCRIPTION,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from EGO_FUNC_PARAMS_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from EGO_FUNC_PARAMS_TL T
where T.FUNC_PARAM_ID = B.FUNC_PARAM_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
delete from EGO_PAGES_TL T
where not exists
(select NULL
from EGO_PAGES_B B
where B.PAGE_ID = T.PAGE_ID
);
update EGO_PAGES_TL T set (
DISPLAY_NAME,
DESCRIPTION
) = (select
B.DISPLAY_NAME,
B.DESCRIPTION
from EGO_PAGES_TL B
where B.PAGE_ID = T.PAGE_ID
and B.LANGUAGE = T.SOURCE_LANG)
where (
T.PAGE_ID,
T.LANGUAGE
) in (select
SUBT.PAGE_ID,
SUBT.LANGUAGE
from EGO_PAGES_TL SUBB, EGO_PAGES_TL SUBT
where SUBB.PAGE_ID = SUBT.PAGE_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and (SUBB.DISPLAY_NAME <> SUBT.DISPLAY_NAME
or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
));
insert into EGO_PAGES_TL (
PAGE_ID,
DISPLAY_NAME,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
B.PAGE_ID,
B.DISPLAY_NAME,
B.DESCRIPTION,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from EGO_PAGES_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from EGO_PAGES_TL T
where T.PAGE_ID = B.PAGE_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
DELETE FROM EGO_DATA_LEVEL_TL T
WHERE NOT EXISTS
(SELECT NULL
FROM EGO_DATA_LEVEL_B B
WHERE B.DATA_LEVEL_ID = T.DATA_LEVEL_ID
);
UPDATE EGO_DATA_LEVEL_TL T
SET (USER_DATA_LEVEL_NAME
)
=
(SELECT B.USER_DATA_LEVEL_NAME
FROM EGO_DATA_LEVEL_TL B
WHERE B.DATA_LEVEL_ID = T.DATA_LEVEL_ID
AND B.LANGUAGE = T.SOURCE_LANG)
WHERE (
T.DATA_LEVEL_ID,
T.LANGUAGE
)
IN
(SELECT
SUBT.DATA_LEVEL_ID,
SUBT.LANGUAGE
from EGO_DATA_LEVEL_TL SUBB, EGO_DATA_LEVEL_TL SUBT
where SUBB.DATA_LEVEL_ID = SUBT.DATA_LEVEL_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and SUBB.USER_DATA_LEVEL_NAME <> SUBT.USER_DATA_LEVEL_NAME
);
INSERT INTO EGO_DATA_LEVEL_TL (
DATA_LEVEL_ID,
USER_DATA_LEVEL_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
LANGUAGE,
SOURCE_LANG
) select
B.DATA_LEVEL_ID,
B.USER_DATA_LEVEL_NAME,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from EGO_DATA_LEVEL_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from EGO_DATA_LEVEL_TL T
where T.DATA_LEVEL_ID = B.DATA_LEVEL_ID
and T.LANGUAGE = L.LANGUAGE_CODE);
DELETE FROM EGO_FLEX_VALUE_VERSION_TL T
WHERE NOT EXISTS
(SELECT NULL
FROM EGO_FLEX_VALUE_VERSION_B B
WHERE B.flex_value_id = T.flex_value_id
);
UPDATE EGO_FLEX_VALUE_VERSION_TL T
SET (DESCRIPTION,
FLEX_VALUE_MEANING
)
=
(SELECT B.DESCRIPTION,
B.FLEX_VALUE_MEANING
FROM EGO_FLEX_VALUE_VERSION_TL B
WHERE B.flex_value_id = T.flex_value_id
AND B.LANGUAGE = T.SOURCE_LANG)
WHERE (
T.flex_value_id,
T.LANGUAGE
)
IN
(SELECT
SUBT.flex_value_id,
SUBT.LANGUAGE
from EGO_FLEX_VALUE_VERSION_TL SUBB, EGO_FLEX_VALUE_VERSION_TL SUBT
where SUBB.flex_value_id = SUBT.flex_value_id
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and ( SUBB.FLEX_VALUE_MEANING <> SUBT.FLEX_VALUE_MEANING
OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL )
OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
)
);
INSERT INTO EGO_FLEX_VALUE_VERSION_TL (
FLEX_VALUE_ID,
VERSION_SEQ_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
DESCRIPTION,
FLEX_VALUE_MEANING,
LANGUAGE,
SOURCE_LANG
) SELECT
B.FLEX_VALUE_ID,
B.VERSION_SEQ_ID,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.CREATION_DATE,
B.CREATED_BY,
B.LAST_UPDATE_LOGIN,
B.DESCRIPTION,
B.FLEX_VALUE_MEANING,
L.LANGUAGE_CODE,
B.SOURCE_LANG
FROM EGO_FLEX_VALUE_VERSION_TL B, FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
(select NULL
from EGO_FLEX_VALUE_VERSION_TL T
where T.flex_value_id = B.flex_value_id
and T.LANGUAGE = L.LANGUAGE_CODE);
PROCEDURE Update_AGV_Name(
p_api_version IN NUMBER
,p_application_id IN NUMBER
,p_attr_group_type IN VARCHAR2
,p_attr_group_name IN VARCHAR2
,p_agv_name IN VARCHAR2
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := FND_API.G_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
-- Start OF comments
-- API name : Update_AGV_Name
-- TYPE : Public
-- Pre-reqs : None
-- FUNCTION : Update Table 'ego_fnd_dsc_flx_ctx_ext'
-- for attribute View Name
-- Version: Current Version 1.0
-- Previous Version : None
--
-- END OF comments
fnd_apps_schema VARCHAR2(30) := NULL;
SELECT agv_name
FROM ego_fnd_dsc_flx_ctx_ext
WHERE agv_name = cp_agv_name
AND attr_group_id NOT IN
(select attr_group_id
from ego_fnd_dsc_flx_ctx_ext
where agv_name = cp_agv_name
AND application_id = cp_application_id
AND descriptive_flexfield_name = cp_attr_group_type
AND descriptive_flex_context_code = cp_attr_group_name
);
SELECT object_name
FROM all_objects
where object_name = cp_agv_name
and owner in (EGO_EXT_FWK_PUB.Get_Application_Owner(cp_appl_id), EGO_EXT_FWK_PUB.Get_Oracle_UserName);
l_api_name CONSTANT VARCHAR (30) := 'Update_AGV_Name' ;
SAVEPOINT Update_AGV_Name ;
l_dynamic_sql := 'CREATE OR REPLACE VIEW '|| l_agv_name ||' AS SELECT * FROM DUAL';
ROLLBACK TO Update_AGV_Name;
ROLLBACK TO Update_AGV_Name;
ROLLBACK TO Update_AGV_Name;
END Update_AGV_Name;
PROCEDURE Update_Attribute_Control_Level (
p_api_version IN NUMBER
,p_application_id IN NUMBER
,p_descriptive_flexfield_name IN VARCHAR2
,p_application_column_name IN VARCHAR2
,p_control_level IN NUMBER
,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
,p_commit IN VARCHAR2 := fnd_api.g_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Attribute_Control_Level';
select lookup_code into l_control_level from fnd_lookups
where lookup_type = 'EGO_PC_CONTROL_LEVEL'
and lookup_code = p_control_level;
select attr_id into l_attr_id
from EGO_FND_DF_COL_USGS_EXT
where application_id = p_application_id
and descriptive_flexfield_name = p_descriptive_flexfield_name
and application_column_name = p_application_column_name;
update EGO_FND_DF_COL_USGS_EXT
set control_level = p_control_level
where application_id = p_application_id
and descriptive_flexfield_name = p_descriptive_flexfield_name
and application_column_name = p_application_column_name;
END Update_Attribute_Control_Level;
SELECT EXT_ATTR_OCV_NAME
INTO l_ocv_name
FROM EGO_FND_OBJECTS_EXT
WHERE OBJECT_NAME = p_object_name;
EXECUTE IMMEDIATE 'SELECT MEANING FROM '||l_ocv_name||' WHERE CODE = :code AND ROWNUM = 1'
INTO l_class_name
USING p_class_code;
SELECT EXT_ATTR_OCV_NAME
INTO l_ocv_name
FROM EGO_FND_OBJECTS_EXT
WHERE OBJECT_NAME = p_object_name;
EXECUTE IMMEDIATE 'SELECT CODE FROM '||l_ocv_name||' WHERE MEANING = :name AND ROWNUM = 1'
INTO l_class_code
USING p_class_name;
l_dynamic_sql:='SELECT CHANGE_TL_TABLE_NAME'||
' FROM ENG_PENDING_CHANGE_CTX'||
' WHERE APPLICATION_ID = :1'||--p_application_id
' AND CHANGE_ATTRIBUTE_GROUP_TYPE =:2' ;--p_attr_group_type;
l_dynamic_sql:='SELECT CHANGE_B_TABLE_NAME'||
' FROM ENG_PENDING_CHANGE_CTX'||
' WHERE APPLICATION_ID = :1'||--p_application_id
' AND CHANGE_ATTRIBUTE_GROUP_TYPE =:2' ;--p_attr_group_type;
SELECT APPLICATION_COLUMN_NAME
INTO l_col_name
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE APPLICATION_ID = p_target_appl_id
AND DESCRIPTIVE_FLEXFIELD_NAME = p_target_ag_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_target_ag_name
AND END_USER_COLUMN_NAME = l_attr_metadata_table(i).ATTR_NAME;
Update_Attribute (
p_api_version => 1.0
,p_application_id => p_target_appl_id
,p_attr_group_type => p_target_ag_type
,p_attr_group_name => p_target_ag_name
,p_internal_name => l_attr_metadata_table(i).ATTR_NAME
,p_display_name => l_attr_metadata_table(i).ATTR_DISP_NAME
,p_description => NULL
,p_sequence => l_attr_metadata_table(i).SEQUENCE
,p_required => l_attr_metadata_table(i).REQUIRED_FLAG
,p_searchable => NULL
,p_column => l_col_name
,p_value_set_id => l_attr_metadata_table(i).VALUE_SET_ID
,p_info_1 => l_attr_metadata_table(i).INFO_1
,p_default_value => l_attr_metadata_table(i).DEFAULT_VALUE
,p_enabled => NULL
,p_display => NULL
,p_view_in_hierarchy_code => NULL
,p_edit_in_hierarchy_code => NULL
,p_uom_class => l_attr_metadata_table(i).UNIT_OF_MEASURE_CLASS
,x_return_status => l_return_status
,x_errorcode => l_errorcode
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
);
SELECT DL_TL.user_data_level_name data_level_name
FROM EGO_ATTR_GROUP_DL AG_DL, EGO_DATA_LEVEL_B DL_B, EGO_DATA_LEVEL_TL DL_TL
WHERE AG_DL.data_level_id = DL_B.data_level_id
AND DL_B.data_level_id = DL_TL.data_level_id
AND UserEnv('LANG')=LANGUAGE
AND AG_DL.attr_group_id = p_attr_grp_id;
/*Procedure to delete the versioned value fron the draft of Versioned value set.*/
PROCEDURE Delete_Value_Set_val(
p_value_set_id IN NUMBER
,p_value_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
) IS
l_value_set_name FND_FLEX_VALUE_SETS.flex_value_set_name%TYPE;
SELECT FLEX_VALUE_SET_NAME INTO L_VALUE_SET_NAME FROM FND_FLEX_VALUE_SETS
WHERE FLEX_VALUE_SET_ID = p_value_set_id ;
SELECT FLEX_VALUE,FLEX_VALUE_MEANING,DESCRIPTION, START_DATE_ACTIVE,
ENABLED_FLAG INTO l_internal_name,l_display_name,l_description,l_start_date,l_enabled
FROM FND_FLEX_VALUES,FND_FLEX_VALUES_TL WHERE
LANGUAGE = userenv('LANG') AND FND_FLEX_VALUES.FLEX_VALUE_ID = p_value_id
AND FND_FLEX_VALUES_TL.FLEX_VALUE_ID = p_value_id AND ROWNUM=1;
FND_FLEX_VALUES_PKG.UPDATE_ROW
(x_flex_value_id => p_value_id
,x_attribute_sort_order => NULL
,x_flex_value_set_id => p_value_set_id
,x_flex_value => l_internal_name
,x_enabled_flag => l_enabled
,x_summary_flag => 'N'
,x_start_date_active => l_start_date
,x_end_date_active => sysdate
,x_parent_flex_value_low => NULL
,x_parent_flex_value_high => NULL
,x_structured_hierarchy_level => NULL
,x_hierarchy_level => NULL
,x_compiled_value_attributes => NULL
,x_value_category => NULL
,x_attribute1 => NULL
,x_attribute2 => NULL
,x_attribute3 => NULL
,x_attribute4 => NULL
,x_attribute5 => NULL
,x_attribute6 => NULL
,x_attribute7 => NULL
,x_attribute8 => NULL
,x_attribute9 => NULL
,x_attribute10 => NULL
,x_attribute11 => NULL
,x_attribute12 => NULL
,x_attribute13 => NULL
,x_attribute14 => NULL
,x_attribute15 => NULL
,x_attribute16 => NULL
,x_attribute17 => NULL
,x_attribute18 => NULL
,x_attribute19 => NULL
,x_attribute20 => NULL
,x_attribute21 => NULL
,x_attribute22 => NULL
,x_attribute23 => NULL
,x_attribute24 => NULL
,x_attribute25 => NULL
,x_attribute26 => NULL
,x_attribute27 => NULL
,x_attribute28 => NULL
,x_attribute29 => NULL
,x_attribute30 => NULL
,x_attribute31 => NULL
,x_attribute32 => NULL
,x_attribute33 => NULL
,x_attribute34 => NULL
,x_attribute35 => NULL
,x_attribute36 => NULL
,x_attribute37 => NULL
,x_attribute38 => NULL
,x_attribute39 => NULL
,x_attribute40 => NULL
,x_attribute41 => NULL
,x_attribute42 => NULL
,x_attribute43 => NULL
,x_attribute44 => NULL
,x_attribute45 => NULL
,x_attribute46 => NULL
,x_attribute47 => NULL
,x_attribute48 => NULL
,x_attribute49 => NULL
,x_attribute50 => NULL
,x_flex_value_meaning => l_display_name
,x_description => l_description
,x_last_update_date => sysdate
,x_last_updated_by => fnd_global.party_id
,x_last_update_login => G_CURRENT_LOGIN_ID);
DELETE EGO_FLEX_VALUE_VERSION_B WHERE FLEX_VALUE_ID = p_value_id AND VERSION_SEQ_ID = 0 ;
DELETE EGO_FLEX_VALUE_VERSION_TL WHERE FLEX_VALUE_ID = p_value_id AND VERSION_SEQ_ID = 0 ;
SELECT *
FROM (SELECT FUNCTION_ID,
INTERNAL_NAME,
DISPLAY_NAME,
DESCRIPTION,
FUNC_TYPE_CODE FUNCTION_TYPE,
FUNC_TYPE_MEANING,
FUNCTION_INFO_1,
FUNCTION_INFO_2
FROM EGO_FUNCTIONS_V) QRSLT
WHERE (FUNCTION_ID IN (v_function_id))
ORDER BY DISPLAY_NAME;
SELECT *
FROM (SELECT MA.FUNCTION_ID FUNCTION_ID,
MA.MAPPED_OBJ_PK1_VAL MAPPED_OBJ_PK1_VAL,
MA.FUNC_PARAM_ID FUNC_PARAM_ID,
MA.MAPPED_OBJ_TYPE MAPPED_OBJ_TYPE,
MA.MAPPED_TO_GROUP_TYPE MAPPED_TO_GROUP_TYPE,
MA.MAPPED_TO_GROUP_PK1 MAPPED_TO_GROUP_PK1,
MA.MAPPED_TO_GROUP_PK2 MAPPED_TO_GROUP_PK2,
MA.MAPPED_TO_GROUP_PK3 MAPPED_TO_GROUP_PK3,
MA.MAPPED_ATTRIBUTE MAPPED_ATTRIBUTE,
MA.MAPPED_TO_GROUP_MEANING MAPPED_TO_GROUP_MEANING,
MA.MAPPED_UOM_PARAMETER MAPPED_UOM_PARAMETER,
MA.VALUE_UOM_SOURCE VALUE_UOM_SOURCE,
MA.FIXED_UOM FIXED_UOM,
(SELECT AGV.ATTR_GROUP_DISP_NAME
FROM EGO_ATTR_GROUPS_V AGV
WHERE AGV.APPLICATION_ID = MA.MAPPED_TO_GROUP_PK1
AND AGV.ATTR_GROUP_TYPE = MA.MAPPED_TO_GROUP_PK2
AND AGV.ATTR_GROUP_NAME = MA.MAPPED_TO_GROUP_PK3) ATTR_GROUP_DISP_NAME,
(SELECT AV.SEQUENCE
FROM EGO_ATTRS_V AV
WHERE AV.APPLICATION_ID = MA.MAPPED_TO_GROUP_PK1
AND AV.ATTR_GROUP_TYPE = MA.MAPPED_TO_GROUP_PK2
AND AV.ATTR_GROUP_NAME = MA.MAPPED_TO_GROUP_PK3
AND AV.ATTR_NAME = MA.MAPPED_ATTRIBUTE) SEQUENCE,
DECODE((SELECT AV.DISPLAY_CODE
FROM EGO_ATTRS_V AV
WHERE AV.APPLICATION_ID = MA.MAPPED_TO_GROUP_PK1
AND AV.ATTR_GROUP_TYPE = MA.MAPPED_TO_GROUP_PK2
AND AV.ATTR_GROUP_NAME = MA.MAPPED_TO_GROUP_PK3
AND AV.ATTR_NAME = MA.MAPPED_ATTRIBUTE),
'H',
TO_NUMBER(NULL),
1) HIDDEN_FILTER
FROM EGO_MAPPINGS_V MA) QRSLT
WHERE (MAPPED_OBJ_PK1_VAL IN (v_action_id))
ORDER BY FUNC_PARAM_ID ASC;
SELECT FP.FUNC_PARAM_ID,
FP.FUNCTION_ID,
FP.SEQUENCE,
FP.INTERNAL_NAME,
FP.DISPLAY_NAME,
FP.DESCRIPTION,
FP.DATA_TYPE_CODE,
FP.DATA_TYPE_MEANING,
FP.FUNC_TYPE_CODE,
FP.FUNC_TYPE_MEANING,
FP.PARAM_TYPE_CODE,
FP.PARAM_TYPE_MEANING,
MA.MAPPED_TO_GROUP_TYPE
FROM EGO_FUNC_PARAMS_V FP, EGO_MAPPINGS_V MA
WHERE (FP.FUNCTION_ID IN (v_function_id)
AND FP.FUNCTION_ID = MA.FUNCTION_ID
AND FP.FUNC_PARAM_ID = MA.FUNC_PARAM_ID
AND MA.MAPPED_OBJ_PK1_VAL = p_Action_Id)
ORDER BY FP.FUNC_PARAM_ID ASC;
SELECT *
FROM (SELECT EAV.ACTION_ID,
EAV.OBJECT_ID,
EAV.CLASSIFICATION_CODE,
EAV.OBJ_NAME,
EAV.OBJ_DISP_NAME,
EAV.ATTR_GRP_APPLICATION_ID,
EAV.ATTR_GROUP_TYPE,
EAV.ATTR_GROUP_NAME,
EAV.ATTR_GROUP_DISP_NAME,
EAV.SEQUENCE,
EAV.ACTION_NAME,
EAV.DESCRIPTION,
EAV.FUNCTION_ID,
EAV.FUNC_DISPLAY_NAME,
EAV.SECURITY_PRIVILEGE_ID,
EAV.SECURITY_PRIVILEGE_NAME,
FFF.FUNCTION_NAME,
EAV.ATTR_GROUP_ID,
EAV.ENABLE_KEY_ATTRIBUTES
FROM EGO_ACTIONS_V EAV, FND_FORM_FUNCTIONS FFF
WHERE EAV.SECURITY_PRIVILEGE_ID = FFF.FUNCTION_ID(+)
AND classification_code IN
(SELECT classification_code
FROM ego_obj_attr_grp_assocs_v
WHERE attr_group_id = EAV.ATTR_GROUP_ID)) QRSLT
WHERE ACTION_ID = v_action_id
ORDER BY FUNCTION_ID ASC;
PROCEDURE Delete_Attr_Group_Views(p_view_name IN VARCHAR2)
IS
obj_name VARCHAR2(30) ;
SELECT object_name
FROM SYS.all_objects
WHERE object_name = p_view_name
AND Upper(object_type)='VIEW';
END Delete_Attr_Group_Views;
SELECT AGV_NAME
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE attr_group_id = p_attr_grp_id ;
SELECT object_name
FROM SYS.all_objects
WHERE object_name = view_name
AND UPPER(object_type)='VIEW';
SELECT AGV_NAME
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE UPPER(agv_name) LIKE view_name ;
UPDATE EGO_FND_DSC_FLX_CTX_EXT SET AGV_NAME = NULL WHERE Upper(AGV_NAME) = atg_view_name ;