[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_DEFAULT_ON_INSERT_FLAG BOOLEAN := FALSE;
G_SYNC_TO_UPDATE VARCHAR2(1) := 'N';
G_WHO_LAST_UPDATE_DATE DATE := NULL;
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;
l_dynamic_sql := 'SELECT DISTINCT(assoc.data_level_id) ' ||
' FROM ego_data_level_b dl, ego_obj_ag_assocs_b assoc, ego_fnd_dsc_flx_ctx_ext ag '||
' WHERE ag.attr_group_id = '||p_attr_group_id ||
' AND dl.attr_group_type = ag.descriptive_flexfield_name '||
' AND dl.application_id = ag.application_id ';
l_dynamic_sql := 'SELECT HIERARCHY_NODE_QUERY FROM EGO_FND_DESC_FLEXS_EXT '||
'WHERE DESCRIPTIVE_FLEXFIELD_NAME = :1';
IF (p_dml_operation = 'INSERT') THEN
-----------------------------------------------------------------------
-- For the INSERT case, just record the new attribute values --
-----------------------------------------------------------------------
Debug_Msg(l_api_name||' insert', 1);
ELSIF (p_dml_operation = 'UPDATE' ) THEN
-----------------------------------------------------------------------
-- For the UPDATE case, record the new attribute values, then query --
-- for the old values, and pack both into the attr diffs table --
-----------------------------------------------------------------------
Debug_Msg(l_api_name||' update', 1);
FND_DSQL.Add_Text('SELECT EXTENSION_ID, ' ||l_db_column_list||
' FROM ' ||NVL(p_attr_group_metadata_obj.EXT_TABLE_VL_NAME
,p_attr_group_metadata_obj.EXT_TABLE_B_NAME)||
' WHERE ');
ELSIF (p_dml_operation = 'DELETE' ) THEN
Debug_Msg(l_api_name||' Transaction Type is Delete ', 1);
, px_is_delete OUT NOCOPY BOOLEAN
, x_error_message OUT NOCOPY VARCHAR2
)
IS
l_dynamic_sql VARCHAR2(4000);
px_is_delete := TRUE;
IF px_is_delete AND
(p_attr_diff_tbl(i).NEW_ATTR_VALUE_STR IS NOT NULL OR
p_attr_diff_tbl(i).NEW_ATTR_VALUE_NUM IS NOT NULL OR
p_attr_diff_tbl(i).NEW_ATTR_VALUE_DATE IS NOT NULL OR
p_attr_diff_tbl(i).NEW_ATTR_UOM IS NOT NULL)
THEN
px_is_delete := FALSE;
if px_is_delete
then
l_debug_msg := 'True' ;
Debug_Msg('In Convert_Attr_Diff_To_Data, done, px_is_delete = '||l_debug_msg);
SELECT OBJECT_ID
INTO l_object_id
FROM FND_OBJECTS
WHERE OBJ_NAME = p_object_name;
l_dynamic_sql := 'SELECT TO_CHAR('||l_formatted_string||', '''||
EGO_USER_ATTRS_COMMON_PVT.G_DATE_FORMAT||''') FROM DUAL';
px_attr_name_value_pairs.DELETE();
l_dynamic_sql := 'SELECT DISTINCT FLEX_VALUE '||
' FROM FND_FLEX_VALUES_VL '||
' WHERE FLEX_VALUE_SET_ID = :1 '||
' AND ENABLED_FLAG = ''Y'' '||
' AND (NVL(START_DATE_ACTIVE, SYSDATE - 1) < SYSDATE) '||
' AND (NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE) '||
' AND FLEX_VALUE_MEANING = :2 ';
l_val_set_query := 'SELECT DISTINCT FLEX_VALUE_MEANING '||
' FROM FND_FLEX_VALUES_VL '||
' WHERE FLEX_VALUE_SET_ID = :1 '||
' AND ENABLED_FLAG = ''Y'' '||
' AND (NVL(START_DATE_ACTIVE, SYSDATE - 1) < SYSDATE) '||
' AND (NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE) '||
' AND TO_NUMBER(FLEX_VALUE) = :2 ';
l_val_set_query := 'SELECT DISTINCT FLEX_VALUE_MEANING '||
' FROM FND_FLEX_VALUES_VL '||
' WHERE FLEX_VALUE_SET_ID = :1 '||
' AND ENABLED_FLAG = ''Y'' '||
' AND (NVL(START_DATE_ACTIVE, SYSDATE - 1) < SYSDATE) '||
' AND (NVL(END_DATE_ACTIVE, SYSDATE + 1) > SYSDATE) '||
' AND FLEX_VALUE = :2 ';
FND_DSQL.Add_Text('SELECT EXTENSION_ID FROM ' || l_vl_name ||
' WHERE 1=1 AND ' || l_change_where_clause||l_extra_where_clause||' AND ');
SELECT validation_type INTO l_validation_type
FROM FND_FLEX_VALUE_SETS WHERE
flex_value_set_id= p_attr_group_metadata_obj.attr_metadata_table(l_table_index).VALUE_SET_ID ;
SELECT
application_table_name,
value_column_name,
id_column_name,
additional_where_clause
INTO
l_vs_table_name,
l_vs_value_column,
l_vs_id_column,
l_vs_where_clause
FROM EGO_VALIDATION_TABLE_INFO_V
WHERE id_column_name IS NOT NULL
AND flex_value_set_id=p_attr_group_metadata_obj.attr_metadata_table(l_table_index).VALUE_SET_ID;
SELECT REPLACE(l_vs_where_clause,FND_GLOBAL.LOCAL_CHR(10),FND_GLOBAL.LOCAL_CHR(32)) INTO l_vs_where_clause FROM dual; --replacing new line character
SELECT REPLACE(l_vs_where_clause,FND_GLOBAL.LOCAL_CHR(13),FND_GLOBAL.LOCAL_CHR(32)) INTO l_vs_where_clause FROM dual; --removing carriage return
l_dynamic_query :='SELECT '|| l_vs_value_column ||' FROM '||l_vs_table_name||' WHERE to_char('|| l_vs_id_column || ') = to_char('''||p_attr_name_value||''')'||' AND '||l_vs_where_clause;
l_dynamic_query := 'SELECT ' || l_vs_value_column || ' FROM ' ||
l_vs_table_name || ' WHERE to_char(' ||
l_vs_id_column || ') = to_char(''' ||
p_attr_name_value || ''')';
SELECT
display_name INTO l_disp_value(l_table_index)
FROM ego_value_set_values_v
WHERE value_set_id=p_attr_group_metadata_obj.attr_metadata_table(l_table_index).VALUE_SET_ID
AND internal_name= trim(l_attr_value_date);
SELECT
display_name INTO l_disp_value(l_table_index)
FROM ego_value_set_values_v
WHERE value_set_id=p_attr_group_metadata_obj.attr_metadata_table(l_table_index).VALUE_SET_ID
AND To_Char(internal_name)= p_attr_name_value;
IF (p_mode = G_DELETE_MODE OR
(l_is_change_case AND p_change_obj.ACD_TYPE = 'DELETE')) THEN
------------------------------------------------------------
-- If user is trying to delete from the UI, we don't want --
-- to worry about Unique Key violations; we just want to --
x_mode := G_UPDATE_MODE;
l_error_message_name := 'EGO_EF_NO_ATTR_VALS_TO_INSERT';
l_token_table.DELETE();
SELECT COUNT (*)
INTO l_item_ag_count
FROM ego_attr_groups_v
WHERE attr_group_type = 'EGO_ITEMMGMT_GROUP' AND (attr_group_id = 1 OR attr_group_id = 2) AND attr_group_id = p_attr_group_id ;
FND_DSQL.Add_Text(' SELECT DATA_LEVEL FROM EGO_OBJ_AG_ASSOCS_B'||
' WHERE OBJECT_ID = ');
FND_DSQL.Add_Text(' SELECT DECODE(ATTRIBUTE2, 1, ATTRIBUTE3,'||
' 2, ATTRIBUTE5,'||
' 3, ATTRIBUTE7,'||
' ''NONE'')'||
' FROM FND_LOOKUP_VALUES'||
' WHERE LOOKUP_TYPE = ''EGO_EF_DATA_LEVEL'''||
' AND LANGUAGE = USERENV(''LANG'')'||
' AND LOOKUP_CODE = (SELECT DATA_LEVEL'||
' FROM EGO_OBJ_AG_ASSOCS_B'||
' WHERE OBJECT_ID = ');
SELECT EGO_EXT_FWK_PUB.Get_Class_Meaning(p_object_id, p_class_code_name_value_pairs(1).VALUE)
INTO x_token_table(2).TOKEN_VALUE
FROM DUAL;
SELECT CONVERSION_RATE
INTO l_conv_rate
FROM MTL_UOM_CONVERSIONS
WHERE UOM_CLASS = p_attr_metadata_obj.UNIT_OF_MEASURE_CLASS
AND UOM_CODE = px_attr_value_obj.ATTR_UNIT_OF_MEASURE
AND ROWNUM = 1;
(UPPER(p_mode) = G_CREATE_MODE OR UPPER(p_mode) = G_UPDATE_MODE) AND --gnanda:BugFix:4640128
l_value IS NULL AND
(p_attr_metadata_obj.DEFAULT_VALUE IS NULL OR
NOT G_DEFAULT_ON_INSERT_FLAG)) THEN
Debug_Msg('In Is_Required_Flag_Respected, required Attr '||p_attr_value_obj.ATTR_NAME||' has no value in '||p_mode||' mode');
SELECT 'X'
INTO l_dummy
FROM MTL_UNITS_OF_MEASURE_TL
WHERE UOM_CLASS = p_attr_metadata_obj.UNIT_OF_MEASURE_CLASS
AND UOM_CODE = px_attr_value_obj.ATTR_UNIT_OF_MEASURE
AND ROWNUM = 1;
l_token_table.DELETE();
SELECT BUSINESS_EVENT_NAME
INTO l_event_name
FROM EGO_FND_DESC_FLEXS_EXT
WHERE APPLICATION_ID = p_attr_group_metadata_obj.APPLICATION_ID
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_metadata_obj.ATTR_GROUP_TYPE;
SELECT PRE_BUSINESS_EVENT_NAME
INTO l_event_name
FROM EGO_FND_DESC_FLEXS_EXT
WHERE APPLICATION_ID = p_attr_group_metadata_obj.APPLICATION_ID
AND DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_metadata_obj.ATTR_GROUP_TYPE;
SELECT BUSINESS_EVENT_FLAG
INTO l_is_event_enabled_flag
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE ATTR_GROUP_ID = p_attr_group_metadata_obj.ATTR_GROUP_ID;*/
SELECT COUNT(*)
INTO l_dummy
FROM EGO_ATTR_GROUP_DL
WHERE ATTR_GROUP_ID = p_attr_group_metadata_obj.ATTR_GROUP_ID
AND DATA_LEVEL_ID = NVL(p_data_level_id,DATA_LEVEL_ID)
AND RAISE_POST_EVENT = 'Y'; -- abedajna 6137035
SELECT PRE_BUSINESS_EVENT_FLAG
INTO l_is_event_enabled_flag
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE ATTR_GROUP_ID = p_attr_group_metadata_obj.ATTR_GROUP_ID;*/
SELECT COUNT(*)
INTO l_dummy
FROM EGO_ATTR_GROUP_DL
WHERE ATTR_GROUP_ID = p_attr_group_metadata_obj.ATTR_GROUP_ID
AND DATA_LEVEL_ID = NVL(p_data_level_id,DATA_LEVEL_ID)
AND RAISE_PRE_EVENT = 'Y'; --abedajna 6137035
IF l_dml_type = 'UPDATE' AND G_SYNC_TO_UPDATE = 'Y' THEN
l_dml_type := 'CREATE';
G_SYNC_TO_UPDATE := 'N';
IF(l_dml_type = 'UPDATE') THEN
WHILE (l_attrs_index <= px_attr_diffs.LAST) LOOP
l_attr_rec.attr_name := px_attr_diffs(l_attrs_index).attr_name;
ELSIF(l_dml_type = 'DELETE') then
WHILE (l_attrs_index <= px_attr_diffs.LAST) LOOP
l_attr_rec.attr_name := px_attr_diffs(l_attrs_index).attr_name;
SELECT function_name
INTO l_view_privilege
FROM fnd_form_functions
WHERE function_id = p_data_level_row_obj.view_privilege_id;
SELECT function_name
INTO l_edit_privilege
FROM fnd_form_functions
WHERE function_id = p_data_level_row_obj.edit_privilege_id;
,p_use_def_vals_on_insert_flag IN BOOLEAN DEFAULT FALSE
,x_return_status OUT NOCOPY VARCHAR2
) IS
l_err_msg_name VARCHAR2(30);
G_DEFAULT_ON_INSERT_FLAG := p_use_def_vals_on_insert_flag OR p_bulkload_flag;
G_ASSOCIATION_DATA_LEVEL_CACHE.DELETE();
G_HIERARCHY_CACHE.DELETE();
G_DEFAULT_ON_INSERT_FLAG := FALSE;
l_in_update_mode BOOLEAN;
l_update_expression VARCHAR2(32767);
l_dynamic_sql := ' SELECT COLUMN_NAME, Decode(DATA_TYPE,''NUMBER'',''N'', ''DATE'',''D'',''VARCHAR2'',''V'',NULL) COLUMN_TYPE '|| --BugFix:5503749 (FND_COLUMNS has the TL columns registered for the B table also hence cannot FND_COLUMNS now)
' FROM SYS.ALL_TAB_COLUMNS ' ||
' WHERE TABLE_NAME = :1 ';
l_dynamic_sql := ' SELECT COLUMN_NAME, Decode(DATA_TYPE,''NUMBER'',''N'', ''DATE'',''D'',''VARCHAR2'',''V'',NULL) COLUMN_TYPE '|| --BugFix:5503749 (FND_COLUMNS has the TL columns registered for the B table also hence cannot FND_COLUMNS now)
' FROM user_synonyms syn, dba_tab_columns col ' ||
' WHERE syn.synonym_name = :1 AND col.owner = syn.table_owner AND col.table_name = syn.table_name ';
l_in_update_mode := (p_to_table_name IS NOT NULL);
IF (l_in_update_mode) THEN
l_table_column_names_list := l_table_column_names_list ||
p_from_table_alias_prefix || '_' ||
l_column_name || ',' ||
p_to_table_alias_prefix || '.' ||
l_column_name || ' ' ||
p_to_table_alias_prefix || '_' ||
l_column_name;
l_update_expression := l_update_expression ||
p_to_table_alias_prefix || '_' ||
l_column_name || '=' ||
p_from_table_alias_prefix || '_' ||
l_column_name || ',';
IF (LENGTH(l_update_expression) > 0) THEN
l_update_expression := RTRIM(l_update_expression, ',');
IF (l_in_update_mode) THEN
-- Bug 8939034 : Start - Removed the hint BYPASS_UJVC
-- l_table_column_names_list := 'UPDATE /*+ BYPASS_UJVC */ (SELECT '||l_table_column_names_list||
l_table_column_names_list := 'UPDATE (SELECT '||l_table_column_names_list||
-- Bug 8939034 : End
' FROM '||p_from_table_name||' '||p_from_table_alias_prefix||
','||p_to_table_name||' '||p_to_table_alias_prefix||' '||
p_in_line_view_where_clause||') SET '||l_update_expression;
PROCEDURE Insert_Row (
p_api_version IN NUMBER
,p_attr_group_metadata_obj IN EGO_ATTR_GROUP_METADATA_OBJ
,p_ext_table_metadata_obj IN EGO_EXT_TABLE_METADATA_OBJ
,p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
,p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
,p_data_level IN VARCHAR2 DEFAULT NULL --R12C
,p_data_level_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
,p_extension_id IN NUMBER
,p_attr_name_value_pairs IN EGO_USER_ATTR_DATA_TABLE
,p_language_to_process IN VARCHAR2
,p_change_obj IN EGO_USER_ATTR_CHANGE_OBJ
,p_extra_pk_col_name_val_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
,p_extra_attr_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
,p_pending_b_table_name IN VARCHAR2
,p_pending_tl_table_name IN VARCHAR2
,p_execute_dml IN VARCHAR2 DEFAULT FND_API.G_TRUE
,p_entity_id IN VARCHAR2
,p_entity_index IN NUMBER
,p_entity_code IN VARCHAR2
,p_commit IN VARCHAR2
,p_bulkload_flag IN BOOLEAN DEFAULT FALSE
,px_attr_diffs IN OUT NOCOPY EGO_USER_ATTR_DIFF_TABLE
,p_raise_business_event IN BOOLEAN DEFAULT TRUE
,x_extension_id OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
Debug_Msg('In Insert_Row, starting', 1);
SAVEPOINT Insert_Row;
SELECT EGO_EXTFWK_S.NEXTVAL INTO l_new_extension_id FROM DUAL;
p_dml_operation => 'INSERT'
, p_object_name => null
, p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
, p_attr_group_metadata_obj => p_attr_group_metadata_obj
, p_ext_table_metadata_obj => p_ext_table_metadata_obj
, p_data_level => p_data_level
, p_data_level_name_value_pairs => p_data_level_name_value_pairs
, p_attr_name_value_pairs => p_attr_name_value_pairs
, p_extension_id => null
, p_entity_id => p_entity_id
, p_entity_index => p_entity_index
, p_entity_code => p_entity_code
, px_attr_diffs => l_attr_diffs_event);
IF (G_DEFAULT_ON_INSERT_FLAG) THEN
l_default_values_or_not := 'VALUES_DEF';
FND_DSQL.Add_Text('INSERT INTO '||l_b_table_name||
' ('||
'EXTENSION_ID, ');
'LAST_UPDATED_BY, '||
'LAST_UPDATE_DATE, '||
'LAST_UPDATE_LOGIN'||
') VALUES ( ');
Debug_Msg(' in insert_row -- p_data_level-'||p_data_level);
Debug_Msg(' in insert_row -- l_data_level_id-'||l_data_level_id);
FND_DSQL.Add_Bind(l_new_extension_id); -- inserting the ext id value in UNIQUE_VALUE column for MR UDAs
SELECT NVL(G_WHO_CREATION_DATE, SYSDATE),
NVL(G_WHO_LAST_UPDATE_DATE, SYSDATE)
INTO G_WHO_CREATION_DATE, G_WHO_LAST_UPDATE_DATE
FROM DUAL;
Add_Bind(p_bind_identifier => 'LAST_UPDATED_BY'
,p_value => l_current_user_id);
Add_Bind(p_bind_identifier => 'LAST_UPDATE_DATE'
,p_value => G_WHO_LAST_UPDATE_DATE);
Add_Bind(p_bind_identifier => 'LAST_UPDATE_LOGIN'
,p_value => l_current_login_id);
Debug_Msg('In Insert_Row, l_dynamic_sql for base table is as follows:', 3);
G_WHO_LAST_UPDATE_DATE := NULL;
FND_DSQL.Add_Text('INSERT INTO '||l_tl_table_name||
' ('||
'EXTENSION_ID, ');
'LAST_UPDATED_BY, '||
'LAST_UPDATE_DATE, '||
'LAST_UPDATE_LOGIN, '||
'SOURCE_LANG, '||
'LANGUAGE) '||
'SELECT ');
Add_Bind(p_bind_identifier => 'LAST_UPDATED_BY'
,p_value => l_current_user_id);
Add_Bind(p_bind_identifier => 'LAST_UPDATE_DATE'
,p_value => SYSDATE);
Add_Bind(p_bind_identifier => 'LAST_UPDATE_LOGIN'
,p_value => l_current_login_id);
Debug_Msg('In Insert_Row, l_dynamic_sql for TL table is as follows:', 3);
Debug_Msg('In Insert_Row, done', 1);
Debug_Msg('Insert_Row EXCEPTION EGO_USER_ATTRS_COMMON_PVT.G_SUBSCRIPTION_EXC ');
ROLLBACK TO insert_row;
Debug_Msg('Insert_Row EXCEPTION others '||SQLERRM);
ROLLBACK TO insert_row;
END Insert_Row;
PROCEDURE Update_Row (
p_api_version IN NUMBER
,p_attr_group_metadata_obj IN EGO_ATTR_GROUP_METADATA_OBJ
,p_ext_table_metadata_obj IN EGO_EXT_TABLE_METADATA_OBJ
,p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
,p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
,p_data_level IN VARCHAR2 DEFAULT NULL --R12C
,p_data_level_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
,p_extension_id IN NUMBER
,p_attr_name_value_pairs IN EGO_USER_ATTR_DATA_TABLE
,p_language_to_process IN VARCHAR2
,p_change_obj IN EGO_USER_ATTR_CHANGE_OBJ
,p_extra_attr_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
,p_extra_pk_col_name_val_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
,p_pending_b_table_name IN VARCHAR2
,p_pending_tl_table_name IN VARCHAR2
,p_execute_dml IN VARCHAR2 DEFAULT FND_API.G_TRUE
,p_entity_id IN VARCHAR2
,p_entity_index IN NUMBER
,p_entity_code IN VARCHAR2
,p_commit IN VARCHAR2
,p_bulkload_flag IN BOOLEAN DEFAULT FALSE
,px_attr_diffs IN OUT NOCOPY EGO_USER_ATTR_DIFF_TABLE
,p_raise_business_event IN BOOLEAN DEFAULT TRUE
,x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
l_which_attrs_to_update VARCHAR2(10);
Debug_Msg('In Update_Row, starting', 1);
SAVEPOINT update_row;
p_dml_operation => 'UPDATE'
, p_object_name => null
, p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
, p_attr_group_metadata_obj => p_attr_group_metadata_obj
, p_ext_table_metadata_obj => p_ext_table_metadata_obj
, p_data_level => p_data_level
, p_data_level_name_value_pairs => p_data_level_name_value_pairs
, p_attr_name_value_pairs => p_attr_name_value_pairs
, p_extension_id => p_extension_id
, p_entity_id => p_entity_id
, p_entity_index => p_entity_index
, p_entity_code => p_entity_code
, px_attr_diffs => l_attr_diffs_event);
FND_DSQL.Add_Text('UPDATE '||l_b_table_name||
' SET '||l_extra_col_value_string||l_change_col_value_string);
FND_DSQL.Add_Text('LAST_UPDATED_BY = ');
Add_Bind(p_bind_identifier => 'LAST_UPDATED_BY'
,p_value => l_current_user_id);
FND_DSQL.Add_Text('LAST_UPDATE_DATE = ');
SELECT NVL(G_WHO_LAST_UPDATE_DATE, SYSDATE) INTO G_WHO_LAST_UPDATE_DATE FROM DUAL;
Add_Bind(p_bind_identifier => 'LAST_UPDATE_DATE'
,p_value => G_WHO_LAST_UPDATE_DATE);
FND_DSQL.Add_Text('LAST_UPDATE_LOGIN = ');
Add_Bind(p_bind_identifier => 'LAST_UPDATE_LOGIN'
,p_value => l_current_login_id);
Debug_Msg('In Update_Row, l_dynamic_sql for base table is as follows:', 3);
Debug_Msg('In Update_Row, l_dynamic_sql:'||l_dynamic_sql, 3);
G_WHO_LAST_UPDATE_DATE := NULL;
p_dml_type => 'UPDATE'
,p_attr_group_metadata_obj => p_attr_group_metadata_obj
,p_extension_id => p_extension_id
,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
,p_data_level_name_value_pairs => p_data_level_name_value_pairs
,p_entity_id => p_entity_id
,p_entity_index => p_entity_index
,p_entity_code => p_entity_code
,p_pre_event_flag => 'T'
,p_data_level_id => l_data_level_id
,px_attr_diffs => l_attr_diffs_event
);
FND_DSQL.Add_Text('UPDATE '||l_tl_table_name||
' SET '||l_change_col_value_string);
FND_DSQL.Add_Text('LAST_UPDATED_BY = ');
Add_Bind(p_bind_identifier => 'LAST_UPDATED_BY'
,p_value => l_current_user_id);
FND_DSQL.Add_Text('LAST_UPDATE_DATE = ');
Add_Bind(p_bind_identifier => 'LAST_UPDATE_DATE'
,p_value => SYSDATE);
FND_DSQL.Add_Text('LAST_UPDATE_LOGIN = ');
Add_Bind(p_bind_identifier => 'LAST_UPDATE_LOGIN'
,p_value => l_current_login_id);
Debug_Msg('In UPDATE_ROW ,p_data_level_name_value_pairs IS NOT NULL');
Debug_Msg('In Update_Row, l_dynamic_sql for TL table is as follows:', 3);
p_dml_type => 'UPDATE'
,p_attr_group_metadata_obj => p_attr_group_metadata_obj
,p_extension_id => p_extension_id
,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
,p_data_level_name_value_pairs => p_data_level_name_value_pairs
,p_entity_id => p_entity_id
,p_entity_index => p_entity_index
,p_entity_code => p_entity_code
,p_pre_event_flag => 'T'
,p_data_level_id => l_data_level_id
,px_attr_diffs => l_attr_diffs_event
);
, G_UPDATE_MODE
, p_attr_group_metadata_obj
, x_return_status
, l_error_message);
p_dml_type => 'UPDATE'
,p_attr_group_metadata_obj => p_attr_group_metadata_obj
,p_extension_id => p_extension_id
,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
,p_data_level_name_value_pairs => p_data_level_name_value_pairs
,p_entity_id => p_entity_id
,p_entity_index => p_entity_index
,p_entity_code => p_entity_code
,p_data_level_id => l_data_level_id
,px_attr_diffs => l_attr_diffs_event
);
Debug_Msg('In Update_Row, done', 1);
Debug_Msg('Update_Row EXCEPTION EGO_USER_ATTRS_COMMON_PVT.G_SUBSCRIPTION_EXC ');
ROLLBACK TO update_row;
Debug_Msg('Update_Row EXCEPTION others '||SQLERRM);
ROLLBACK TO update_row;
END Update_Row;
PROCEDURE Delete_Row (
p_api_version IN NUMBER
,p_attr_group_metadata_obj IN EGO_ATTR_GROUP_METADATA_OBJ
,p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
,p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
,p_data_level IN VARCHAR2 DEFAULT NULL --R12C
,p_data_level_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
,p_extension_id IN NUMBER
-- Start ssingal -For Ucc Net Attribute Propagation
,p_attr_name_value_pairs IN EGO_USER_ATTR_DATA_TABLE
,p_ext_table_metadata_obj IN EGO_EXT_TABLE_METADATA_OBJ
-- End ssingal
,p_language_to_process IN VARCHAR2
,p_change_obj IN EGO_USER_ATTR_CHANGE_OBJ
,p_extra_pk_col_name_val_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
,p_pending_b_table_name IN VARCHAR2
,p_pending_tl_table_name IN VARCHAR2
,p_execute_dml IN VARCHAR2 DEFAULT FND_API.G_TRUE
-- Start ssingal -For Ucc Net Attribute Propagation
,p_bulkload_flag IN BOOLEAN DEFAULT FALSE
,px_attr_diffs IN OUT NOCOPY EGO_USER_ATTR_DIFF_TABLE
-- End ssingal -For Ucc Net Attribute Propagation
,p_entity_id IN VARCHAR2
,p_entity_index IN NUMBER
,p_entity_code IN VARCHAR2
,p_commit IN VARCHAR2
,p_raise_business_event IN BOOLEAN DEFAULT TRUE
,x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
Debug_Msg('In Delete_Row, starting', 1);
SAVEPOINT delete_row;
p_dml_operation => 'DELETE'
, p_object_name => null
, p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
, p_attr_group_metadata_obj => p_attr_group_metadata_obj
, p_ext_table_metadata_obj => p_ext_table_metadata_obj
, p_data_level => p_data_level
, p_data_level_name_value_pairs => p_data_level_name_value_pairs
, p_attr_name_value_pairs => p_attr_name_value_pairs
, p_extension_id => p_extension_id
, p_entity_id => p_entity_id
, p_entity_index => p_entity_index
, p_entity_code => p_entity_code
, px_attr_diffs => l_attr_diffs_event);
l_dynamic_sql := 'DELETE FROM '||l_b_table_name||
' WHERE EXTENSION_ID = '||p_extension_id||l_change_col_where_string||l_extra_col_where_string;
p_dml_type => 'DELETE'
,p_attr_group_metadata_obj => p_attr_group_metadata_obj
,p_extension_id => p_extension_id
,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
,p_data_level_name_value_pairs => p_data_level_name_value_pairs
,p_entity_id => p_entity_id
,p_entity_index => p_entity_index
,p_entity_code => p_entity_code
,p_pre_event_flag => 'T'
,p_data_level_id => l_data_level_id
,px_attr_diffs => l_attr_diffs_event
);
l_dynamic_sql := 'DELETE FROM '||l_tl_table_name||
' WHERE EXTENSION_ID = '||p_extension_id||l_change_col_where_string||l_extra_col_where_string;
, G_DELETE_MODE
, p_attr_group_metadata_obj
, x_return_status
, l_error_message);
p_dml_type => 'DELETE'
,p_attr_group_metadata_obj => p_attr_group_metadata_obj
,p_extension_id => p_extension_id
,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
,p_data_level_name_value_pairs => p_data_level_name_value_pairs
,p_entity_id => p_entity_id
,p_entity_index => p_entity_index
,p_entity_code => p_entity_code
,p_data_level_id => l_data_level_id
,px_attr_diffs => l_attr_diffs_event
);
Debug_Msg('In Delete_Row, done', 1);
Debug_Msg('Delete_Row EXCEPTION EGO_USER_ATTRS_COMMON_PVT.G_SUBSCRIPTION_EXC ');
ROLLBACK TO delete_row;
Debug_Msg('Delete_Row EXCEPTION others '||SQLERRM);
ROLLBACK TO delete_row;
END Delete_Row;
l_token_table.DELETE();
l_token_table.DELETE();
l_token_table.DELETE();
l_token_table.DELETE();
l_token_table.DELETE();
l_token_table.DELETE();
l_token_table.DELETE();
l_token_table.DELETE();
l_token_table.DELETE();
Insert_Row(
p_api_version => p_api_version
,p_attr_group_metadata_obj => p_attr_group_metadata_obj
,p_ext_table_metadata_obj => p_ext_table_metadata_obj
,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
,p_class_code_name_value_pairs => p_class_code_name_value_pairs
,p_data_level => p_data_level
,p_data_level_name_value_pairs => p_data_level_name_value_pairs
,p_extension_id => p_extension_id
,p_attr_name_value_pairs => p_attr_name_value_pairs
,p_language_to_process => p_language_to_process
,p_change_obj => p_change_obj
,p_extra_pk_col_name_val_pairs => p_extra_pk_col_name_val_pairs
,p_extra_attr_name_value_pairs => p_extra_attr_name_value_pairs
,p_pending_b_table_name => p_pending_b_table_name
,p_pending_tl_table_name => p_pending_tl_table_name
,p_execute_dml => p_execute_dml
,p_entity_id => p_entity_id
,p_entity_index => p_entity_index
,p_entity_code => p_entity_code
,p_commit => FND_API.G_FALSE
,p_bulkload_flag => p_bulkload_flag
,px_attr_diffs => l_attr_diffs
,p_raise_business_event => p_raise_business_event
,x_extension_id => x_extension_id
,x_return_status => x_return_status
);
ELSIF (p_mode = G_UPDATE_MODE) THEN
Update_Row(
p_api_version => p_api_version
,p_attr_group_metadata_obj => p_attr_group_metadata_obj
,p_ext_table_metadata_obj => p_ext_table_metadata_obj
,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
,p_class_code_name_value_pairs => p_class_code_name_value_pairs
,p_data_level => p_data_level
,p_data_level_name_value_pairs => p_data_level_name_value_pairs
,p_extension_id => p_extension_id
,p_attr_name_value_pairs => p_attr_name_value_pairs
,p_language_to_process => p_language_to_process
,p_change_obj => p_change_obj
,p_extra_pk_col_name_val_pairs => p_extra_pk_col_name_val_pairs
,p_extra_attr_name_value_pairs => p_extra_attr_name_value_pairs
,p_pending_b_table_name => p_pending_b_table_name
,p_pending_tl_table_name => p_pending_tl_table_name
,p_execute_dml => p_execute_dml
,p_entity_id => p_entity_id
,p_entity_index => p_entity_index
,p_entity_code => p_entity_code
,p_commit => FND_API.G_FALSE
,p_bulkload_flag => p_bulkload_flag
,px_attr_diffs => l_attr_diffs
,p_raise_business_event => p_raise_business_event
,x_return_status => x_return_status
);
ELSIF (p_mode = G_DELETE_MODE) THEN -- mode must be G_DELETE_MODE
Delete_Row(
p_api_version => p_api_version
,p_attr_group_metadata_obj => p_attr_group_metadata_obj
,p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
,p_class_code_name_value_pairs => p_class_code_name_value_pairs
,p_data_level => p_data_level
,p_data_level_name_value_pairs => p_data_level_name_value_pairs
,p_extension_id => p_extension_id
,p_attr_name_value_pairs => p_attr_name_value_pairs
,p_ext_table_metadata_obj => p_ext_table_metadata_obj
,p_language_to_process => p_language_to_process
,p_change_obj => p_change_obj
,p_extra_pk_col_name_val_pairs => p_extra_pk_col_name_val_pairs
,p_pending_b_table_name => p_pending_b_table_name
,p_pending_tl_table_name => p_pending_tl_table_name
,p_execute_dml => p_execute_dml
,p_bulkload_flag => p_bulkload_flag
,px_attr_diffs => l_attr_diffs
,p_entity_id => p_entity_id
,p_entity_index => p_entity_index
,p_entity_code => p_entity_code
,p_commit => FND_API.G_FALSE
,p_raise_business_event => p_raise_business_event
,x_return_status => x_return_status
);
,p_use_def_vals_on_insert_flag IN BOOLEAN DEFAULT FALSE
,p_init_fnd_msg_list IN VARCHAR2
,p_mode IN VARCHAR2
,p_change_obj IN EGO_USER_ATTR_CHANGE_OBJ DEFAULT NULL
,p_extra_pk_col_name_val_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
,p_pending_b_table_name IN VARCHAR2 DEFAULT NULL
,p_pending_vl_name IN VARCHAR2 DEFAULT NULL
,p_bulkload_flag IN BOOLEAN DEFAULT FALSE
,px_object_id IN OUT NOCOPY NUMBER
,px_attr_name_value_pairs IN OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
,x_attr_group_metadata_obj OUT NOCOPY EGO_ATTR_GROUP_METADATA_OBJ
,x_ext_table_metadata_obj OUT NOCOPY EGO_EXT_TABLE_METADATA_OBJ
,x_extension_id OUT NOCOPY NUMBER
,x_mode OUT NOCOPY VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Perform_Setup_Operations';
,p_use_def_vals_on_insert_flag => p_use_def_vals_on_insert_flag
,x_return_status => x_return_status
);
l_token_table.DELETE();
IF (UPPER(x_mode) = G_UPDATE_MODE OR UPPER(x_mode) = G_DELETE_MODE) THEN
Debug_Msg(l_api_name || ' after checking, x_extension_id is '||x_extension_id);
l_token_table.DELETE();
l_row_attrs_table.DELETE();
SELECT agdl.defaulting
FROM ego_attr_group_dl agdl,
ego_data_level_b dl
WHERE agdl.attr_group_id = p_attr_group_id
AND agdl.data_level_id = dl.data_level_id
AND dl.data_level_name = p_data_level;
SELECT msib.style_item_flag, msib.style_item_id
FROM mtl_system_items_b msib
WHERE msib.organization_id = p_organization_id
AND msib.inventory_item_id = p_inventory_item_id;
fnd_dsql.add_text (' SELECT ' || p_uom_db_column_name ||
' FROM ' || p_table_name ||
' WHERE EXTENSION_ID = ');
l_token_table.DELETE();
fnd_dsql.add_text(' SELECT EXTENSION_ID, ATTR_GROUP_ID, '||l_db_column_list||
' FROM ' ||NVL(l_curr_ag_vl_name,l_curr_ag_table_name)||' EMSI'||
' WHERE ' );
SELECT CONVERSION_RATE
INTO l_conv_rate
FROM MTL_UOM_CONVERSIONS
WHERE UOM_CLASS = l_new_attr_metadata_obj.UNIT_OF_MEASURE_CLASS
AND UOM_CODE = NVL(l_uom_value, l_new_attr_metadata_obj.UNIT_OF_MEASURE_BASE)
AND ROWNUM = 1;
l_db_column_name_table.DELETE;
l_db_column_query_table.DELETE;
l_token_table.DELETE();
l_mode <> G_DELETE_MODE) THEN
Debug_Msg(l_api_name || ' calling Validate_Row_Pvt ',1);
,p_use_def_vals_on_insert IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_log_errors IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_init_fnd_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_write_to_concurrent_log IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_add_errors_to_fnd_stack IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_bulkload_flag IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_raise_business_event IN BOOLEAN DEFAULT TRUE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_b_dml_for_ag OUT NOCOPY VARCHAR2
,x_tl_dml_for_ag OUT NOCOPY VARCHAR2
,x_b_bind_count OUT NOCOPY NUMBER
,x_tl_bind_count OUT NOCOPY NUMBER
,x_b_bind_attr_table OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
,x_tl_bind_attr_table OUT NOCOPY EGO_USER_ATTR_DATA_TABLE
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Generate_DML_For_Row';
G_SYNC_TO_UPDATE := 'N';
,p_use_def_vals_on_insert_flag => FND_API.To_Boolean(p_use_def_vals_on_insert)
,p_debug_level => p_debug_level
,p_bulkload_flag => FND_API.To_Boolean(p_bulkload_flag)
,px_object_id => l_object_id
,px_attr_name_value_pairs => l_attr_name_value_pairs
,x_attr_group_metadata_obj => l_attr_group_metadata_obj
,x_ext_table_metadata_obj => l_ext_table_metadata_obj
,x_extension_id => l_extension_id
,x_mode => l_mode
,x_return_status => x_return_status
);
SELECT OBJECT_ID INTO l_object_id
FROM FND_OBJECTS
WHERE OBJ_NAME = p_object_name;
,p_use_def_vals_on_insert IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_log_errors IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_init_fnd_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_write_to_concurrent_log IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_add_errors_to_fnd_stack IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_bulkload_flag 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_extension_id NUMBER;
,p_use_def_vals_on_insert => p_use_def_vals_on_insert
,p_log_errors => p_log_errors
,p_init_fnd_msg_list => p_init_fnd_msg_list
,p_write_to_concurrent_log => p_write_to_concurrent_log
,p_add_errors_to_fnd_stack => p_add_errors_to_fnd_stack
,p_commit => p_commit
,p_bulkload_flag => p_bulkload_flag
,x_extension_id => l_extension_id
,x_mode => l_mode
,x_return_status => x_return_status
,x_errorcode => x_errorcode
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
,p_use_def_vals_on_insert IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_log_errors IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_init_fnd_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_write_to_concurrent_log IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_add_errors_to_fnd_stack IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
,p_bulkload_flag IN VARCHAR2 DEFAULT FND_API.G_FALSE
--Added by geguo for 9373845 begin
,p_creation_date IN DATE DEFAULT NULL
,p_last_update_date IN DATE DEFAULT NULL
--Added by geguo 9373845 end
,x_extension_id OUT NOCOPY NUMBER
,x_mode OUT NOCOPY VARCHAR2
,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) := 'Perform_DML_On_Row';
G_WHO_LAST_UPDATE_DATE := p_last_update_date;
G_SYNC_TO_UPDATE := 'N';
,p_use_def_vals_on_insert_flag => FND_API.To_Boolean(p_use_def_vals_on_insert)
,p_debug_level => p_debug_level
,p_bulkload_flag => FND_API.To_Boolean(p_bulkload_flag)
,px_object_id => l_object_id
,px_attr_name_value_pairs => l_attr_name_value_pairs
,x_attr_group_metadata_obj => l_attr_group_metadata_obj
,x_ext_table_metadata_obj => l_ext_table_metadata_obj
,x_extension_id => x_extension_id
,x_mode => x_mode
,x_return_status => x_return_status
);
AND (l_mode = 'UPDATE'
AND p_attr_group_type in ('EGO_ITEM_GTIN_ATTRS','EGO_ITEM_GTIN_MULTI_ATTRS'))
THEN
G_SYNC_TO_UPDATE := 'Y' ;
'SELECT ETA_OUTER.ATTRIBUTE_GROUP_ID,' ||
' ETA_OUTER.ATTRIBUTE_ID,' ||
' ETA_OUTER.ROW_NUMBER,' ||
' ETA_OUTER.ATTRIBUTE_STRING_VALUE,' ||
' ETA_OUTER.ATTRIBUTE_NUMBER_VALUE,' ||
' ETA_OUTER.ATTRIBUTE_UOM_CODE,' ||
' ETA_OUTER.ATTRIBUTE_DATE_VALUE,' ||
' ETA_OUTER.ATTRIBUTE_TRANSLATED_VALUE' ||
' FROM EGO_TEMPL_ATTRIBUTES ETA_OUTER'
);
' IN (SELECT ETA.TEMPLATE_ID' ||
' ,ETA.ATTRIBUTE_GROUP_ID' ||
' ,ETA.ATTRIBUTE_ID' ||
' ,ETA.ROW_NUMBER' ||
' ,MIN(DECODE(ETA.CLASSIFICATION_CODE, ');
l_decode_query := 'SELECT DECODE(ATTRIBUTE2, 1, ATTRIBUTE3, 2, ATTRIBUTE5,3, ATTRIBUTE7,''NONE'') ';
l_decode_query := l_decode_query||' AND LOOKUP_CODE = (SELECT DATA_LEVEL ';
l_attr_name_value_pairs.DELETE();
l_decode_query := 'SELECT DECODE(ATTRIBUTE2, 1, ATTRIBUTE3, 2, ATTRIBUTE5,3, ATTRIBUTE7,''NONE'') ';
l_decode_query := l_decode_query||' AND LOOKUP_CODE = (SELECT DATA_LEVEL ';
l_insert_pk_sql VARCHAR2(500);
l_insert_dtlevel_sql VARCHAR2(500) := '';
l_insert_class_code_sql VARCHAR2(100) := '';
l_select_pk_sql VARCHAR2(2000);
l_select_dtlevel_sql VARCHAR2(2000) := '';
l_select_class_code_sql VARCHAR2(500) := '';
SELECT DISTINCT FDF.DESCRIPTIVE_FLEXFIELD_NAME ATTR_GROUP_TYPE
FROM EGO_OBJECT_EXT_TABLES_B EOET
,FND_DESCRIPTIVE_FLEXS FDF
WHERE EOET.APPLICATION_ID = cp_application_id
AND EOET.OBJECT_ID = cp_object_id
AND FDF.APPLICATION_ID = cp_application_id
AND EOET.APPLICATION_ID = FDF.APPLICATION_ID
AND EOET.EXT_TABLE_NAME = FDF.APPLICATION_TABLE_NAME;
l_select_pk_sql := l_select_pk_sql ||'''' ||p_new_pk_col_value_pairs(l_pk_index).VALUE ||''', ';
l_insert_pk_sql := l_insert_pk_sql ||p_old_pk_col_value_pairs(l_pk_index).NAME ||', ';
Debug_Msg(l_api_name || ' After PK loop l_insert_pk_sql: '||l_insert_pk_sql);
Debug_Msg(l_api_name || ' After PK loop l_select_pk_sql: '||l_select_pk_sql);
l_select_dtlevel_sql := l_select_dtlevel_sql ||'''' ||p_new_dtlevel_col_value_pairs(l_dtlevel_index).VALUE ||''', ';
l_select_dtlevel_sql := l_select_dtlevel_sql || ' NULL, ';
l_insert_dtlevel_sql := l_insert_dtlevel_sql ||p_new_dtlevel_col_value_pairs(l_dtlevel_index).NAME ||', ';
l_select_dtlevel_sql := l_select_dtlevel_sql ||'''' ||p_new_dtlevel_col_value_pairs(l_dtlevel_index).VALUE ||''', ';
l_select_dtlevel_sql := l_select_dtlevel_sql || ' NULL, ';
l_insert_dtlevel_sql := l_insert_dtlevel_sql ||p_old_dtlevel_col_value_pairs(l_dtlevel_index).NAME ||', ';
Debug_Msg(l_api_name || ' After DL loop l_insert_dtlevel_sql: '||l_insert_dtlevel_sql);
Debug_Msg(l_api_name || ' After DL loop l_select_dtlevel_sql: '||l_select_dtlevel_sql);
l_insert_class_code_sql := l_insert_class_code_sql ||
l_class_code_metadata_array(l_cc_col_index).COL_NAME ||
', ';
l_select_class_code_sql := l_select_class_code_sql || '''' ||
p_new_cc_col_value_pairs(l_cc_value_index).VALUE ||
''', ';
l_select_class_code_sql := l_select_class_code_sql ||
l_class_code_metadata_array(l_cc_col_index).COL_NAME ||
', ';
Debug_Msg(l_api_name || ' After CC loop l_select_class_code_sql: '||l_select_class_code_sql);
'''LAST_UPDATED_BY'', '||
'''LAST_UPDATE_DATE'', '||
'''LAST_UPDATE_LOGIN''';
SELECT EXT_TABLE_NAME, EXT_TL_TABLE_NAME, EXT_VL_NAME, ATTR_GROUP_TYPE
INTO l_b_table_name, l_tl_table_name, l_vl_name, l_attr_group_type
FROM EGO_ATTR_GROUP_TYPES_V
WHERE APPLICATION_ID = p_application_id
AND ATTR_GROUP_TYPE = l_attr_group_types(agt_itr);
l_select_dtlevel_sql := '';
IF INSTR(l_insert_dtlevel_sql,l_pk_name) = 0 THEN
l_select_dtlevel_sql := l_select_dtlevel_sql || ' NULL, ';
l_select_dtlevel_sql := l_select_dtlevel_sql ||'''' ||p_new_dtlevel_col_value_pairs(l_dtlevel_index).VALUE ||''', ';
l_insert_dtlevel_sql := l_insert_dtlevel_sql ||p_new_dtlevel_col_value_pairs(l_dtlevel_index).NAME ||', ';
Debug_Msg(l_api_name || ' Before Query l_insert_pk_sql: '||l_insert_pk_sql);
Debug_Msg(l_api_name || ' Before Query l_insert_class_code_sql: '||l_insert_class_code_sql );
Debug_Msg(l_api_name || ' Before Query l_select_pk_sql: '|| l_select_pk_sql);
Debug_Msg(l_api_name || ' Before Query l_select_dtlevel_sql: '|| l_select_dtlevel_sql);
Debug_Msg(l_api_name || ' Before Query l_select_class_code_sql: '|| l_select_class_code_sql );
l_base_table_copy_dml := ' INSERT INTO '||l_b_table_name||
' (EXTENSION_ID, '||
l_insert_pk_sql ||' '||
' DATA_LEVEL_ID, '||
l_all_dl_cols ||' '||
l_insert_class_code_sql ||' '||
l_b_table_col_names_list||' '||
'CREATED_BY, '||
'CREATION_DATE, '||
'LAST_UPDATED_BY, '||
'LAST_UPDATE_DATE, '||
'LAST_UPDATE_LOGIN)'||
' SELECT '||
':1, '||
l_select_pk_sql ||' '||
p_new_data_level_id ||', '||
l_select_dtlevel_sql ||' '||
l_select_class_code_sql ||' '||
l_b_table_col_names_list||' '||
l_current_user_id||', '||
'SYSDATE, '||
l_current_user_id||', '||
'SYSDATE, '||
l_current_login_id||
' FROM '||l_b_table_name||
' WHERE EXTENSION_ID = :2';
l_tl_table_copy_dml := ' INSERT INTO '||l_tl_table_name||
' (EXTENSION_ID, '||
l_insert_pk_sql ||' '||
' DATA_LEVEL_ID, '||
l_all_dl_cols ||' '||
l_insert_class_code_sql ||' '||
l_tl_table_col_names_list||' '||
'CREATED_BY, '||
'CREATION_DATE, '||
'LAST_UPDATED_BY, '||
'LAST_UPDATE_DATE, '||
'LAST_UPDATE_LOGIN)'||
' SELECT '||
':1, '||
l_select_pk_sql ||' '||
p_new_data_level_id ||', '||
l_select_dtlevel_sql ||' '||
l_select_class_code_sql ||' '||
l_tl_table_col_names_list||' '||
l_current_user_id||', '||
'SYSDATE, '||
l_current_user_id||', '||
'SYSDATE, '||
l_current_login_id||
' FROM '||l_tl_table_name||
' WHERE EXTENSION_ID = :2';
l_base_table_copy_dml := ' INSERT INTO '||l_b_table_name||
' (EXTENSION_ID, '||
l_insert_pk_sql ||' '||
l_insert_dtlevel_sql ||' '||
l_insert_class_code_sql ||' '||
l_b_table_col_names_list||' '||
'CREATED_BY, '||
'CREATION_DATE, '||
'LAST_UPDATED_BY, '||
'LAST_UPDATE_DATE, '||
'LAST_UPDATE_LOGIN)'||
' SELECT '||
':1, '||
l_select_pk_sql ||' '||
l_select_dtlevel_sql ||' '||
l_select_class_code_sql ||' '||
l_b_table_col_names_list||' '||
l_current_user_id||', '||
'SYSDATE, '||
l_current_user_id||', '||
'SYSDATE, '||
l_current_login_id||
' FROM '||l_b_table_name||
' WHERE EXTENSION_ID = :2';
l_tl_table_copy_dml := ' INSERT INTO '||l_tl_table_name||
' (EXTENSION_ID, '||
l_insert_pk_sql ||' '||
l_insert_dtlevel_sql ||' '||
l_insert_class_code_sql ||' '||
l_tl_table_col_names_list||' '||
'CREATED_BY, '||
'CREATION_DATE, '||
'LAST_UPDATED_BY, '||
'LAST_UPDATE_DATE, '||
'LAST_UPDATE_LOGIN)'||
' SELECT '||
':1, '||
l_select_pk_sql ||' '||
l_select_dtlevel_sql ||' '||
l_select_class_code_sql ||' '||
l_tl_table_col_names_list||' '||
l_current_user_id||', '||
'SYSDATE, '||
l_current_user_id||', '||
'SYSDATE, '||
l_current_login_id||
' FROM '||l_tl_table_name||
' WHERE EXTENSION_ID = :2';
l_dynamic_sql := ' SELECT EXTENSION_ID, EGO_EXTFWK_S.NEXTVAL '||
' FROM '||NVL(l_vl_name, l_b_table_name)||
' WHERE '||l_where_pk_sql||l_where_dtlevel_sql||
' ATTR_GROUP_ID IN ('||p_attr_group_list||')';
l_dynamic_sql := ' SELECT EXTENSION_ID, EGO_EXTFWK_S.NEXTVAL '||
' FROM '||NVL(l_vl_name, l_b_table_name)||
' WHERE '||l_where_pk_sql||l_where_dtlevel_sql;
'''CREATION_DATE'',''LAST_UPDATED_BY'','||
'''LAST_UPDATE_DATE'',''LAST_UPDATE_LOGIN''';
l_cols_to_exclude_list := '''CHANGE_ID'', ''CHANGE_LINE_ID'', ''ACD_TYPE'', ''IMPLEMENTATION_DATE'', ''EXTENSION_ID'' , ''PROGRAM_ID'', ''PROGRAM_UPDATE_DATE'' , ''REQUEST_ID'' ,''PROGRAM_APPLICATION_ID'' ';
l_dynamic_sql := 'SELECT '||l_chng_col_names_list||','||
l_b_chng_cols_list||','||
l_tl_chng_cols_list||
' FROM '||p_change_b_table_name||' B,'||
p_change_tl_table_name||' TL'||
' WHERE B.ACD_TYPE <> ''HISTORY'' AND B.IMPLEMENTATION_DATE IS NULL'||
' AND B.EXTENSION_ID = TL.EXTENSION_ID'||
' AND B.ACD_TYPE = TL.ACD_TYPE'||
' AND B.CHANGE_LINE_ID = TL.CHANGE_LINE_ID'||
' AND B.CHANGE_LINE_ID = :1';
l_attr_name_value_pairs.DELETE();
l_token_table.DELETE();
l_mode_for_current_row := G_UPDATE_MODE;
l_utility_dynamic_sql := 'UPDATE '||p_change_b_table_name||
' SET EXTENSION_ID = :1'||
' WHERE EXTENSION_ID = :2'||
' AND ACD_TYPE = ''ADD'''||
' AND CHANGE_LINE_ID = :3';
l_utility_dynamic_sql := 'UPDATE '||p_change_tl_table_name||
' SET EXTENSION_ID = :1'||
' WHERE EXTENSION_ID = :2'||
' AND ACD_TYPE = ''ADD'''||
' AND CHANGE_LINE_ID = :3'||
' AND LANGUAGE = :4';
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM '||p_change_b_table_name||
' WHERE EXTENSION_ID = :1'
INTO l_dummy
USING l_current_pending_ext_id;
l_mode_for_current_row := G_UPDATE_MODE;
l_mode_for_current_row := G_UPDATE_MODE;
ELSIF (l_current_acd_type = 'DELETE') THEN
IF (l_current_production_ext_id IS NULL) THEN
---------------------------------------
-- If ACD Type is DELETE and there's --
-- no production row, we do nothing --
---------------------------------------
l_mode_for_current_row := 'SKIP';
l_mode_for_current_row := G_DELETE_MODE;
IF (l_mode_for_current_row = G_DELETE_MODE OR
l_mode_for_current_row = G_UPDATE_MODE) THEN
-----------------------------------------------------------
-- Process_Row will only process our pending B table row --
-- in the loop when LANGUAGE is NULL or when LANGUAGE = --
-- SOURCE_LANG, so we insert a History row in that loop --
-----------------------------------------------------------
IF (l_current_row_language IS NULL OR
l_current_row_language = l_current_row_source_lang) THEN
l_utility_dynamic_sql := ' INSERT INTO '||p_change_b_table_name||' CT ('||
l_history_b_chng_cols_list||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
', CT.EXTENSION_ID) SELECT '||
l_history_b_prod_cols_list||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
', PT.EXTENSION_ID FROM '||
p_production_b_table_name||' PT, '||
p_change_b_table_name||
' CT WHERE PT.EXTENSION_ID = :1'||
' AND CT.EXTENSION_ID = :2'||
' AND CT.CHANGE_LINE_ID = :3'||
' AND CT.ACD_TYPE = :4';
l_utility_dynamic_sql := ' INSERT INTO '||p_change_tl_table_name||' CT ('||
l_history_tl_chng_cols_list||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, CT.ACD_TYPE'||
', CT.EXTENSION_ID) SELECT '||
l_history_tl_prod_cols_list||
', CT.CHANGE_ID, CT.CHANGE_LINE_ID, ''HISTORY'''||
', PT.EXTENSION_ID FROM '||
p_production_tl_table_name||' PT, '||
p_change_tl_table_name||
' CT WHERE PT.EXTENSION_ID = :1'||
' AND CT.EXTENSION_ID = :2'||
' AND CT.CHANGE_LINE_ID = :3'||
' AND CT.ACD_TYPE = :4'||
' AND CT.LANGUAGE = PT.LANGUAGE AND CT.LANGUAGE = :5';
EXECUTE IMMEDIATE ' UPDATE '||p_change_b_table_name||
' SET IMPLEMENTATION_DATE = :1'||
' WHERE CHANGE_LINE_ID = :2'
USING SYSDATE, p_change_line_id;
EXECUTE IMMEDIATE ' UPDATE '||p_change_tl_table_name||
' SET IMPLEMENTATION_DATE = :1'||
' WHERE CHANGE_LINE_ID = :2'
USING SYSDATE, p_change_line_id;
l_dynamic_sql := 'SELECT ' || l_db_column_alias ||
' FROM ' || NVL(p_attr_group_metadata_obj.EXT_TABLE_VL_NAME
,p_attr_group_metadata_obj.EXT_TABLE_B_NAME) ||
' WHERE ';
l_token_table.DELETE();
l_token_table.DELETE();
SELECT VALUE
INTO l_output_dir
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir';
PROCEDURE Update_Attributes (
p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
, p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
, p_data_level IN VARCHAR2 DEFAULT NULL
, p_data_level_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
, p_attr_diffs IN EGO_USER_ATTR_DIFF_TABLE
, p_transaction_type IN VARCHAR2
, p_attr_group_id IN NUMBER DEFAULT NULL
, x_error_message OUT NOCOPY VARCHAR2
)
IS
l_object_id NUMBER;
l_is_delete BOOLEAN;
Debug_Msg('In Update_Attributes, called with transaction type '||p_transaction_type);
Debug_Msg('In Update_Attributes, retrieved l_object_id as '||l_object_id, 2);
Debug_Msg('In Update_Attributes, getting AG metadata for '||p_attr_group_id, 2);
Debug_Msg('In Update_Attributes, got AG metadata: '||
l_attr_group_metadata_obj.attr_group_id||','||
l_attr_group_metadata_obj.application_id||','||
l_attr_group_metadata_obj.attr_group_type||','||
l_attr_group_metadata_obj.attr_group_name||','||
l_attr_group_metadata_obj.attr_group_disp_name
, 2);
Convert_Attr_Diff_To_Data(p_attr_diffs, l_old_attr_name_value_pairs, FALSE, l_is_delete, x_error_message);
Convert_Attr_Diff_To_Data(p_attr_diffs, l_new_attr_name_value_pairs, TRUE, l_is_delete, x_error_message);
Debug_Msg('In Update_Attributes, got AG and ext table objs, getting ext id ');
Debug_Msg('In Update_Attributes, old('||i||') '
||l_old_attr_name_value_pairs(i).ROW_IDENTIFIER||','
||l_old_attr_name_value_pairs(i).ATTR_NAME||','
||l_old_attr_name_value_pairs(i).ATTR_VALUE_STR||','
||l_old_attr_name_value_pairs(i).ATTR_VALUE_NUM);
Debug_Msg('In Update_Attributes, new('||i||') '
||l_new_attr_name_value_pairs(i).ROW_IDENTIFIER||','
||l_new_attr_name_value_pairs(i).ATTR_NAME||','
||l_new_attr_name_value_pairs(i).ATTR_VALUE_STR||','
||l_new_attr_name_value_pairs(i).ATTR_VALUE_NUM);
Debug_Msg('In Update_Attributes, using ext id '||l_extension_id);
IF (l_is_delete) THEN
l_mode := G_DELETE_MODE;
l_mode := G_UPDATE_MODE;
ELSE -- transaction type is DELETE
l_mode := p_transaction_type;
Debug_Msg('In Update_Attributes, calling perform_dml_on_row_pvt with mode '||l_mode, 2);
Debug_Msg('In Update_Attributes, Perform_DML_On_Row_Pvt returned with status '||l_return_status, 2);
Debug_Msg('In Update_Attributes, skipped perform_dml');
Debug_Msg('In Update_Attributes, ERROR ret status: '||l_return_status, 2);
Debug_Msg('In Update_Attributes, EXCEPTION FND_API.G_EXC_ERROR');
END Update_Attributes;
p_dml_operation => 'UPDATE'
, p_object_name => p_object_name
, p_pk_column_name_value_pairs => p_pk_column_name_value_pairs
, p_attr_group_metadata_obj => l_attr_group_metadata_obj
, p_ext_table_metadata_obj => l_ext_table_metadata_obj
, p_data_level => p_data_level
, p_data_level_name_value_pairs => p_data_level_name_value_pairs
, p_attr_name_value_pairs => l_attr_name_value_pairs
, p_extension_id => null
, p_entity_id => null
, p_entity_index => null
, p_entity_code => null
, px_attr_diffs => l_attr_diffs);
SELECT pk1_column_name, pk2_column_name, pk3_column_name
FROM ego_data_level_vl
WHERE data_level_name = cp_data_level_name;
SELECT assoc.attr_group_id
FROM ego_obj_ag_assocs_b assoc,
fnd_objects object,
ego_fnd_dsc_flx_ctx_ext ag
WHERE assoc.classification_code = p_class_code
AND assoc.object_id = object.object_id
AND object.obj_name = p_obj_name
AND ag.descriptive_flexfield_name = p_ag_type
AND ag.attr_group_id = assoc.attr_group_id
AND assoc.data_level = p_data_level;
SELECT assoc.attr_group_id
FROM ego_obj_ag_assocs_b assoc,
fnd_objects object,
ego_fnd_dsc_flx_ctx_ext ag
WHERE assoc.classification_code = p_class_code
AND assoc.object_id = object.object_id
AND object.obj_name = p_obj_name
AND ag.descriptive_flexfield_name = p_ag_type
AND ag.attr_group_id = assoc.attr_group_id;
SELECT attr.attr_name,
attr.attr_display_name,
ag.attr_group_id,
ag.attr_group_name,
ag.attr_group_disp_name,
ag.attr_group_type,
ag.application_id
FROM ego_attrs_v attr ,
ego_attr_groups_v ag
WHERE ag.application_id = attr.application_id
AND ag.attr_group_type = attr.attr_group_type
AND ag.attr_group_name = attr.attr_group_name
AND ag.attr_group_id = p_ag_id
AND attr.required_flag = 'Y'
AND attr.enabled_flag = 'Y';
l_request_table_batch_iter.DELETE;
l_attributes_row_table.DELETE;
l_attributes_data_table.DELETE;
l_token_table.DELETE();
SELECT FLEX.APPLICATION_TABLE_NAME EXT_TABLE_NAME,
FLEX_EXT.APPLICATION_VL_NAME EXT_VL_NAME
INTO l_ext_b_table_name,
l_ext_vl_name
FROM FND_DESCRIPTIVE_FLEXS FLEX,
EGO_FND_DESC_FLEXS_EXT FLEX_EXT
WHERE FLEX.APPLICATION_ID = FLEX_EXT.APPLICATION_ID(+)
AND FLEX.DESCRIPTIVE_FLEXFIELD_NAME = FLEX_EXT.DESCRIPTIVE_FLEXFIELD_NAME(+)
AND FLEX.APPLICATION_ID = p_application_id
AND FLEX.DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type;
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;
l_dynamic_sql := ' SELECT /*+ LEADING( assoc_tbl) */ ATTR_GROUP_TBL.ATTR_GROUP_ID, ATTR_TBL.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP_NAME,'||
' ATTR_TBL.END_USER_COLUMN_NAME ATTR_NAME, REQUIRED_FLAG, DEFAULT_VALUE , ASSOC_TBL.DATA_LEVEL,'||
' ATTR_EXT_TBL.DATA_TYPE'||
' FROM FND_DESCR_FLEX_COLUMN_USAGES ATTR_TBL,'||
' EGO_FND_DSC_FLX_CTX_EXT ATTR_GROUP_TBL,'||
' EGO_OBJ_AG_ASSOCS_B ASSOC_TBL,'||
' EGO_FND_DF_COL_USGS_EXT ATTR_EXT_TBL'||
' WHERE ATTR_TBL.APPLICATION_ID = ATTR_GROUP_TBL.APPLICATION_ID ';
SELECT data_level_name
INTO l_base_data_level
FROM ( SELECT MIN(data_level_id) data_level_id
FROM ego_data_level_b
WHERE application_id = p_application_id
AND attr_group_type = p_attr_group_type
) min_dl, ego_data_level_b dl
WHERE dl.data_level_id = min_dl.data_level_id;
EXECUTE IMMEDIATE 'SELECT '||l_temp_date_str||' FROM DUAL '
INTO l_date_val;
l_dynamic_sql := 'SELECT 1 FROM '||l_ext_vl_name||' EXT WHERE '||l_ext_where_clause;
SELECT owner
INTO g_owner
FROM sys.all_objects
WHERE object_type IN ('TABLE','VIEW')
AND object_name = p_object_name;
SELECT 1
INTO l_dummy_number
FROM SYS.all_tab_columns
WHERE table_name = p_object_name
AND column_name = p_column_name;
SELECT 1
INTO l_dummy_number
FROM user_synonyms syn, dba_tab_columns col
WHERE syn.synonym_name = p_object_name
AND col.owner = syn.table_owner
AND col.table_name = syn.table_name
AND col.column_name = p_column_name;