[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select value
INTO l_utl_file_dir
FROM v$parameter
WHERE name = 'utl_file_dir';
SELECT 'HZ_PARTY:'||TO_CHAR(PARTY_ID)
INTO l_party_id
FROM EGO_USER_V
WHERE USER_NAME = FND_GLOBAL.USER_NAME;
SELECT CONCATENATED_SEGMENTS
INTO l_item_number
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id;
SELECT ORGANIZATION_CODE
INTO l_org_code
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_organization_id;
PROCEDURE INSERT_ITEM_ATTRS
( p_api_version IN NUMBER
,p_object_name IN VARCHAR2
,p_application_id IN NUMBER
,p_attr_group_type IN VARCHAR2
,p_base_attr_names_values IN EGO_USER_ATTR_DATA_TABLE
,p_tl_attr_names_values IN EGO_USER_ATTR_DATA_TABLE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_exec_mode IN VARCHAR2
)
IS
l_attr_name_dml VARCHAR2(3200);
SELECT LANGUAGE_CODE
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG='I'
or INSTALLED_FLAG='B';
SELECT CHANGE_B_TABLE_NAME ,
CHANGE_TL_TABLE_NAME
INTO l_pending_base_tbl,l_pending_tl_tbl
from ENG_PENDING_CHANGE_CTX
where CHANGE_ATTRIBUTE_GROUP_TYPE= p_attr_group_type
AND APPLICATION_ID = p_application_id;
SELECT EGO_EXTFWK_S.NEXTVAL
into l_extension_id
FROM dual;
Write_Debug('Insert base name DML : '||l_attr_name_dml);
Write_Debug('Insert base value DML : '||l_attr_value_dml);
l_attr_base_dml := 'INSERT INTO '|| l_pending_base_tbl || '('||
l_attr_name_dml || ') VALUES ( ' || l_attr_value_dml || ')';
Write_Debug('Insert base DML : '||l_attr_base_dml);
Write_Debug('Insert tl name DML : '||l_attr_name_dml);
Write_Debug('Insert tl value DML : '||l_attr_value_dml);
l_attr_tl_dml := 'INSERT INTO '|| l_pending_tl_tbl || '('||l_attr_name_dml || ' ) VALUES ( ' || l_attr_value_dml ||')';
Write_Debug('Insert base DML : '||l_attr_tl_dml);
END INSERT_ITEM_ATTRS;
PROCEDURE UPDATE_ITEM_ATTRS
( p_api_version IN NUMBER
,p_object_name IN VARCHAR2
,p_application_id IN NUMBER
,p_attr_group_type IN VARCHAR2
,p_base_attr_names_values IN EGO_USER_ATTR_DATA_TABLE
,p_tl_attr_names_values IN EGO_USER_ATTR_DATA_TABLE
,p_pk_attr_names_values IN EGO_USER_ATTR_DATA_TABLE
,x_return_status OUT NOCOPY VARCHAR2
,x_errorcode OUT NOCOPY NUMBER
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_exec_mode IN VARCHAR2
)
IS
l_attr_b_update_dml VARCHAR2(3200);
l_attr_tl_update_dml VARCHAR2(3200);
l_attr_update_where_dml VARCHAR2(3200);
SELECT CHANGE_B_TABLE_NAME , CHANGE_TL_TABLE_NAME
INTO l_pending_base_tbl, l_pending_tl_tbl
from ENG_PENDING_CHANGE_CTX
where CHANGE_ATTRIBUTE_GROUP_TYPE= p_attr_group_type
AND APPLICATION_ID = p_application_id;
IF (l_attr_update_where_dml IS NULL)
THEN
l_attr_update_where_dml := p_pk_attr_names_values(i).ATTR_NAME;
l_attr_update_where_dml := l_attr_update_where_dml ||' AND '|| p_pk_attr_names_values(i).ATTR_NAME;
l_attr_update_where_dml := l_attr_update_where_dml || ' = ''' || p_pk_attr_names_values(i).ATTR_VALUE_STR || '''';
l_attr_update_where_dml := l_attr_update_where_dml || ' = ' || p_pk_attr_names_values(i).ATTR_VALUE_NUM;
l_attr_update_where_dml := l_attr_update_where_dml || ' = '|| p_pk_attr_names_values(i).ATTR_VALUE_DATE;
IF (l_attr_b_update_dml IS NULL)
THEN
l_attr_b_update_dml := p_base_attr_names_values(i).ATTR_NAME;
l_attr_b_update_dml := l_attr_b_update_dml ||' , '|| p_base_attr_names_values(i).ATTR_NAME;
l_attr_b_update_dml := l_attr_b_update_dml ||' = :FND_BIND'||l_bind_count ;
l_attr_b_update_dml := l_attr_b_update_dml ||' , '|| 'UOM_'|| p_base_attr_names_values(i).ATTR_NAME;
l_attr_b_update_dml := l_attr_b_update_dml ||' = :FND_BIND'||l_bind_count ;
l_attr_b_update_dml := 'UPDATE '|| l_pending_base_tbl || ' SET '|| l_attr_b_update_dml || ' WHERE '|| l_attr_update_where_dml;
DBMS_SQL.Parse(l_cursor_id, l_attr_b_update_dml, DBMS_SQL.Native);
IF l_attr_b_update_dml IS NOT NULL
THEN
-- l_attr_b_update_dml := 'UPDATE '|| l_pending_base_tbl || ' SET '|| l_attr_b_update_dml || ' WHERE '|| l_attr_update_where_dml;
Write_Debug('UPDATE base DML : '|| l_attr_b_update_dml);
IF (l_attr_tl_update_dml IS NULL)
THEN
l_attr_tl_update_dml := p_tl_attr_names_values(i).ATTR_NAME;
l_attr_tl_update_dml := l_attr_tl_update_dml ||' , '|| p_tl_attr_names_values(i).ATTR_NAME;
l_attr_tl_update_dml := l_attr_tl_update_dml ||'= :FND_BIND'||i;
IF l_attr_tl_update_dml IS NOT NULL
THEN
l_attr_update_where_dml := l_attr_update_where_dml ||' AND LANGUAGE = USERENV(''LANG'')' ;
l_attr_tl_update_dml := 'UPDATE '|| l_pending_tl_tbl || ' SET '|| l_attr_tl_update_dml || ' WHERE '|| l_attr_update_where_dml;
DBMS_SQL.Parse(l_cursor_id, l_attr_tl_update_dml, DBMS_SQL.Native);
Write_Debug('UPDATE base DML : '|| l_attr_tl_update_dml);
END UPDATE_ITEM_ATTRS;
l_delete_index NUMBER;
SELECT 'Y' FROM FND_TABLES FT,FND_COLUMNS FC
WHERE FT.TABLE_NAME = UPPER(TABLENAME) AND FT.TABLE_ID = FC.TABLE_ID
AND COLUMN_NAME = 'DATA_LEVEL_ID';
SELECT FLEX_EXT.APPLICATION_VL_NAME
INTO p_prod_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 ;
having the key attibutes in select and where condition
with a join using EXTENSION_ID AND PRIMARY KEYS AND a
condition to get rows other than the current EXTENSION_ID
*/
if L_CACHED_SQL_FOUND <>'Y' THEN
l_dynamic_sql := 'SELECT ';
if l_dynamic_sql <> 'SELECT ' THEN
l_dynamic_sql := l_dynamic_sql ||', ';
IF l_dynamic_sql = 'SELECT ' THEN
l_dynamic_sql := l_dynamic_sql || ' PEND.EXTENSION_ID ';
/* The final query would actualy select EXTENSION_ID from the
previous query using alais 'dy_sql' and would filter the rows
based on the key attr values of the current row being valildated.
*/
l_uk_where_clause := ' 1=1 ';
l_mode := 'UPDATE';
PROCEDURE INSERT_ITEM_USER_ATTRS
(
p_api_version IN NUMBER
,p_object_name IN VARCHAR2
,p_attr_group_id IN NUMBER
,p_application_id IN NUMBER
,p_attr_group_type IN VARCHAR2
,p_attr_group_name IN VARCHAR2
,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_NAME IN VARCHAR2
,p_data_level_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY DEFAULT NULL
,p_attr_name_value_pairs IN EGO_USER_ATTR_DATA_TABLE
,p_mode IN VARCHAR2
,p_extra_pk_col_name_val_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
,p_extension_id IN NUMBER
,p_pending_b_table_name IN VARCHAR2
,p_pending_tl_table_name IN VARCHAR2
,p_pending_vl_name IN VARCHAR2
,p_acd_type IN VARCHAR2
,p_dml_attr_name_value_pairs IN EGO_USER_ATTR_DATA_TABLE
,p_api_caller IN VARCHAR2
,p_key_attr_upd IN 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_b_dml_for_ag VARCHAR2(30000);
Write_Debug('Start INSERT_ITEM_USER_ATTRS');
SELECT CHANGE_B_TABLE_NAME
, CHANGE_TL_TABLE_NAME
, CHANGE_VL_TABLE_NAME
INTO l_pending_base_tbl
,l_pending_tl_tbl
,l_pending_vl
FROM ENG_PENDING_CHANGE_CTX
WHERE CHANGE_ATTRIBUTE_GROUP_TYPE= p_attr_group_type
AND APPLICATION_ID = p_application_id;
IF P_MODE <> 'DELETE' -- AND (P_API_CALLER = G_EXEC_MODE_IMPORT OR P_API_CALLER = 'PWB')
THEN
IF g_debug_flag THEN
Write_Debug('p_extension_id ' || to_char(p_extension_id));
AND (p_acd_type='CHANGE' OR p_acd_type='DELETE')
THEN
l_attr_group_request_table := EGO_ATTR_GROUP_REQUEST_TABLE(
EGO_ATTR_GROUP_REQUEST_OBJ(p_attr_group_id
, p_application_id
, p_attr_group_type
, null
, p_data_level_name
, null
, null
, null
, null
, null
, null
));
IF P_ACD_TYPE<>'DELETE'
THEN
-- l_row_identifier := l_extension_id;
END IF; -- P_ACD_TYPE<>'DELETE'
END IF; -- P_MODE<>'DELETE'
if(p_mode <> 'DELETE') THEN
FOR i IN L_ATTRIBUTES_DATA_TABLE.FIRST .. L_ATTRIBUTES_DATA_TABLE.LAST
LOOP
FOR l_attr_index IN p_dml_attr_name_value_pairs.FIRST .. p_dml_attr_name_value_pairs.LAST
LOOP
if L_ATTRIBUTES_DATA_TABLE(i).ATTR_NAME = p_dml_attr_name_value_pairs(l_attr_index).ATTR_NAME
AND ((L_ATTRIBUTES_DATA_TABLE(i).ATTR_VALUE_STR is NULL AND p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_STR is NOT NULL)
OR(L_ATTRIBUTES_DATA_TABLE(i).ATTR_VALUE_NUM is NULL AND p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_NUM is NOT NULL)
OR(L_ATTRIBUTES_DATA_TABLE(i).ATTR_VALUE_DATE is NULL AND p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_DATE is NOT NULL))
THEN
L_ATTRIBUTES_DATA_TABLE(i):= p_dml_attr_name_value_pairs(l_attr_index);
IF p_mode ='UPDATE' OR p_mode ='DELETE'
THEN
l_temp_extension_id := l_extension_id;
Write_Debug('Insert base DML : '|| l_b_dml_for_ag);
Write_Debug('Insert tl DML : '|| l_tl_dml_for_ag);
if p_mode = 'CREATE' and (p_acd_type ='CHANGE' OR p_acd_type ='DELETE') AND l_b_bind_attr_table is NOT NULL
THEN
l_bind_index := l_b_bind_attr_table.FIRST;
if l_b_bind_attr_table is NOT NULL AND p_mode <>'DELETE'
THEN
FOR l_bind_index IN l_b_bind_attr_table.FIRST .. l_b_bind_attr_table.LAST
LOOP
FOR l_attr_index IN p_dml_attr_name_value_pairs.FIRST .. p_dml_attr_name_value_pairs.LAST
LOOP
if l_b_bind_attr_table(l_bind_index).ATTR_DISP_VALUE is NOT NULL
AND SUBSTR(l_b_bind_attr_table(l_bind_index).ATTR_DISP_VALUE,INSTR(l_b_bind_attr_table(l_bind_index).ATTR_DISP_VALUE,'$$')+2)= p_dml_attr_name_value_pairs(l_attr_Index).ATTR_NAME
THEN
l_b_bind_attr_table(l_bind_index).ATTR_VALUE_STR := p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_STR;
IF l_bind_index = l_b_bind_attr_table.LAST AND (p_mode ='UPDATE' OR p_mode ='DELETE')
THEN
DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':FND_BIND'||l_bind_index,l_extension_id);
if p_mode = 'CREATE' and (p_acd_type ='CHANGE' OR p_acd_type ='DELETE') AND l_tl_bind_attr_table is NOT NULL
THEN
l_bind_index := l_tl_bind_attr_table.FIRST;
IF l_tl_bind_attr_table is NOT NULL AND p_mode <>'DELETE'
THEN
FOR l_bind_index IN l_tl_bind_attr_table.FIRST .. l_tl_bind_attr_table.LAST
LOOP
FOR l_attr_index IN p_dml_attr_name_value_pairs.FIRST .. p_dml_attr_name_value_pairs.LAST
LOOP
if l_tl_bind_attr_table(l_bind_index).ATTR_DISP_VALUE is NOT NULL
AND SUBSTR(l_tl_bind_attr_table(l_bind_index).ATTR_DISP_VALUE,INSTR(l_tl_bind_attr_table(l_bind_index).ATTR_DISP_VALUE,'$$')+2)= p_dml_attr_name_value_pairs(l_attr_Index).ATTR_NAME
THEN
l_tl_bind_attr_table(l_bind_index).ATTR_VALUE_STR := p_dml_attr_name_value_pairs(l_attr_index).ATTR_VALUE_STR;
END INSERT_ITEM_USER_ATTRS;
PROCEDURE DELETE_ITEM_ATTRS
( p_api_version IN NUMBER
,p_object_name IN VARCHAR2
,p_application_id IN NUMBER
,p_attr_group_type IN VARCHAR2
,p_pk_attr_names_values IN EGO_USER_ATTR_DATA_TABLE
,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
NULL;
END DELETE_ITEM_ATTRS;
PROCEDURE UPDATE_DATA_LEVEL(P_PK_ATTR_NAME_VALUE_PAIRS EGO_COL_NAME_VALUE_PAIR_ARRAY
,P_NEW_DL_NAME_VALUE_PAIRS EGO_COL_NAME_VALUE_PAIR_ARRAY
,P_OLD_DL_NAME_VALUE_PAIRS EGO_COL_NAME_VALUE_PAIR_ARRAY
,P_OBJECT_NAME VARCHAR2
,P_APPLICATION_ID NUMBER)
IS
CURSOR C_DATA_LEVELS (p_objet_name VARCHAR2)
IS
SELECT DATA_LEVEL_INTERNAL_NAME
,DATA_LEVEL_DISPLAY_NAME
,DATA_LEVEL_COLUMN
,DL_COL_DATA_TYPE
FROM (SELECT LOOKUP_CODE DATA_LEVEL_INTERNAL_NAME
,MEANING DATA_LEVEL_DISPLAY_NAME
,DECODE(ATTRIBUTE2, 1, ATTRIBUTE3,
2, ATTRIBUTE5,
3, ATTRIBUTE7,
'NONE') DATA_LEVEL_COLUMN
,DECODE(ATTRIBUTE2, 1, ATTRIBUTE4,
2, ATTRIBUTE6,
3, ATTRIBUTE8,
'NONE') DL_COL_DATA_TYPE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'EGO_EF_DATA_LEVEL'
AND ATTRIBUTE1 = p_objet_name
AND LANGUAGE = USERENV('LANG')) DATA_LEVELS
WHERE DATA_LEVEL_COLUMN <>'NONE';
l_b_update_dml VARCHAR2(10000);
l_tl_update_dml VARCHAR2(10000);
L_UPDATE_WHERE_CLAUSE VARCHAR2(4000) := NULL;
L_UPDATE_BIND_INDEX NUMBER :=0;
L_UPDATE_BIND_VALUES EGO_COL_NAME_VALUE_PAIR_ARRAY;
L_ADDED_UPDATE_B_DML VARCHAR2(10000);
L_ADDED_UPDATE_TL_DML VARCHAR2(10000);
SELECT CHANGE_B_TABLE_NAME ,
CHANGE_TL_TABLE_NAME
INTO l_pending_base_tbl,l_pending_tl_tbl
from ENG_PENDING_CHANGE_CTX
where CHANGE_ATTRIBUTE_GROUP_TYPE= 'EGO_ITEMMGMT_GROUP'
AND APPLICATION_ID = p_application_id;
L_B_UPDATE_DML := ' UPDATE '|| l_pending_base_tbl || ' SET EXTENSION_ID=:1';
L_TL_UPDATE_DML := ' UPDATE '|| l_pending_TL_tbl || ' SET EXTENSION_ID=:1 ';
L_UPDATE_BIND_INDEX := L_UPDATE_BIND_INDEX+1;
L_ADDED_UPDATE_B_DML := 'UPDATE ' || l_pending_base_tbl || ' SET ';
L_ADDED_UPDATE_TL_DML := 'UPDATE ' || l_pending_base_tbl|| ' SET ';
L_DYN_ATTR_GRP_SQL := ' SELECT DISTINCT ATTR_GROUP_ID ' ||
' FROM ' || l_pending_base_tbl ||
' WHERE ';
l_B_data_level_dml := ' SELECT A.EXTENSION_ID NEW_EXT_ID, ';
if L_UPDATE_WHERE_CLAUSE is NULL THEN
L_UPDATE_WHERE_CLAUSE := ' WHERE ' ;
L_UPDATE_WHERE_CLAUSE := L_UPDATE_WHERE_CLAUSE ||
P_PK_ATTR_NAME_VALUE_PAIRS(i).NAME ||
' = ''' || P_PK_ATTR_NAME_VALUE_PAIRS(i).VALUE ||'''';
L_UPDATE_BIND_INDEX := L_UPDATE_BIND_INDEX+1;
L_B_UPDATE_DML := L_B_UPDATE_DML ||' , '|| REC.DATA_LEVEL_COLUMN || ' = :'||L_UPDATE_BIND_INDEX;
L_TL_UPDATE_DML := L_TL_UPDATE_DML ||' , '|| REC.DATA_LEVEL_COLUMN|| ' = :'||L_UPDATE_BIND_INDEX;
L_ADDED_UPDATE_B_DML := L_ADDED_UPDATE_B_DML
||' '|| REC.DATA_LEVEL_COLUMN
||' = ' || P_NEW_DL_NAME_VALUE_PAIRS(i).VALUE;
L_ADDED_UPDATE_TL_DML := L_ADDED_UPDATE_TL_DML
||' '|| REC.DATA_LEVEL_COLUMN
||' = ' || P_NEW_DL_NAME_VALUE_PAIRS(i).VALUE;
L_UPDATE_BIND_INDEX := L_UPDATE_BIND_INDEX +1;
if L_UPDATE_WHERE_CLAUSE is NULL then
L_UPDATE_WHERE_CLAUSE := ' WHERE ';
L_UPDATE_WHERE_CLAUSE := L_UPDATE_WHERE_CLAUSE || ' AND ';
L_UPDATE_WHERE_CLAUSE := L_UPDATE_WHERE_CLAUSE || ' EXTENSION_ID =:' || L_UPDATE_BIND_INDEX;
L_B_UPDATE_DML := L_B_UPDATE_DML || L_UPDATE_WHERE_CLAUSE;
L_TL_UPDATE_DML := L_TL_UPDATE_DML || L_UPDATE_WHERE_CLAUSE;
DBMS_SQL.Parse(l_B_cursor_id, L_B_UPDATE_DML, DBMS_SQL.Native);
DBMS_SQL.Parse(l_TL_cursor_id, L_TL_UPDATE_DML, DBMS_SQL.Native);
L_ADDED_UPDATE_B_DML := L_ADDED_UPDATE_B_DML ||' '|| L_ADDED_WHERE_CLAUSE;
L_ADDED_UPDATE_TL_DML := L_ADDED_UPDATE_TL_DML ||' ' || L_ADDED_WHERE_CLAUSE;
EXECUTE IMMEDIATE L_ADDED_UPDATE_B_DML;
EXECUTE IMMEDIATE L_ADDED_UPDATE_TL_DML;
END UPDATE_DATA_LEVEL;
select change_vo_def_name into x_vo_def
from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
where pc_ctx.change_attribute_group_type = p_change_attr_group_type
and fnd_obj.obj_name = p_object_name
and fnd_appl.application_short_name = p_application_short_name;
select change_vo_inst_name into x_vo_instance
from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
where pc_ctx.change_attribute_group_type = p_change_attr_group_type
and fnd_obj.obj_name = p_object_name
and fnd_appl.application_short_name = p_application_short_name;
select change_vo_row_class_name into x_vo_row_class
from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
where pc_ctx.change_attribute_group_type = p_change_attr_group_type
and fnd_obj.obj_name = p_object_name
and fnd_appl.application_short_name = p_application_short_name;
select change_eo_def_name into x_eo_def
from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
where pc_ctx.change_attribute_group_type = p_change_attr_group_type
and fnd_obj.obj_name = p_object_name
and fnd_appl.application_short_name = p_application_short_name;
select change_b_table_name into x_base_table
from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
where pc_ctx.change_attribute_group_type = p_change_attr_group_type
and fnd_obj.obj_name = p_object_name
and fnd_appl.application_short_name = p_application_short_name;
select change_tl_table_name into x_tl_table
from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
where pc_ctx.change_attribute_group_type = p_change_attr_group_type
and fnd_obj.obj_name = p_object_name
and fnd_appl.application_short_name = p_application_short_name;
select change_vl_table_name into x_vl_name
from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
where pc_ctx.change_attribute_group_type = p_change_attr_group_type
and fnd_obj.obj_name = p_object_name
and fnd_appl.application_short_name = p_application_short_name;
select change_b_table_name into x_context_value
from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
where pc_ctx.change_attribute_group_type = p_change_attr_group_type
and fnd_obj.obj_name = p_object_name
and fnd_appl.application_short_name = p_application_short_name;
select change_tl_table_name into x_context_value
from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
where pc_ctx.change_attribute_group_type = p_change_attr_group_type
and fnd_obj.obj_name = p_object_name
and fnd_appl.application_short_name = p_application_short_name;
select change_vl_table_name into x_context_value
from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
where pc_ctx.change_attribute_group_type = p_change_attr_group_type
and fnd_obj.obj_name = p_object_name
and fnd_appl.application_short_name = p_application_short_name;
select change_vo_def_name into x_context_value
from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
where pc_ctx.change_attribute_group_type = p_change_attr_group_type
and fnd_obj.obj_name = p_object_name
and fnd_appl.application_short_name = p_application_short_name;
select change_vo_inst_name into x_context_value
from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
where pc_ctx.change_attribute_group_type = p_change_attr_group_type
and fnd_obj.obj_name = p_object_name
and fnd_appl.application_short_name = p_application_short_name;
select change_vo_row_class_name into x_context_value
from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
where pc_ctx.change_attribute_group_type = p_change_attr_group_type
and fnd_obj.obj_name = p_object_name
and fnd_appl.application_short_name = p_application_short_name;
select change_eo_def_name into x_context_value
from eng_pending_change_ctx pc_ctx, fnd_objects fnd_obj, fnd_application fnd_appl
where pc_ctx.change_attribute_group_type = p_change_attr_group_type
and fnd_obj.obj_name = p_object_name
and fnd_appl.application_short_name = p_application_short_name;
SELECT DATA_LEVEL_ID FROM EGO_DATA_LEVEL_B WHERE DATA_LEVEL_NAME = dataLevelName;
--pass p_mode as LINE to delete pending changes for revised item
--pass p_mode as ASSOC to delete pending changes for the association
IF P_MODE = 'ASSOC' THEN
l_dynamic_sql := l_dynamic_sql || 'AND ORGANIZATION_ID= :3 '
|| 'AND DATA_LEVEL_ID = :4 '
|| 'AND NVL(PK1_VALUE, -1) = :5 '
|| 'AND NVL(PK2_VALUE, -1) = :6 '
|| 'AND NVL(PK3_VALUE, -1) = :7 '
|| 'AND NVL(PK4_VALUE, -1) = :8 '
|| 'AND NVL(PK5_VALUE, -1) = :9 ';
EXECUTE IMMEDIATE 'DELETE FROM EGO_ITEMS_ATTRS_CHANGES_B '||l_dynamic_sql USING P_CHANGE_ID, P_CHANGE_LINE_ID, P_ORG_ID,
l_data_level_id, l_data_level_pk1, l_data_level_pk2,
l_data_level_pk3, l_data_level_pk4, l_data_level_pk5;
EXECUTE IMMEDIATE 'DELETE FROM EGO_ITEMS_ATTRS_CHANGES_TL '||l_dynamic_sql USING P_CHANGE_ID, P_CHANGE_LINE_ID, P_ORG_ID,
l_data_level_id, l_data_level_pk1, l_data_level_pk2,
l_data_level_pk3, l_data_level_pk4, l_data_level_pk5;
EXECUTE IMMEDIATE 'DELETE FROM EGO_ITEMS_ATTRS_CHANGES_B '||l_dynamic_sql USING P_CHANGE_ID, P_CHANGE_LINE_ID;
EXECUTE IMMEDIATE 'DELETE FROM EGO_ITEMS_ATTRS_CHANGES_TL '||l_dynamic_sql USING P_CHANGE_ID, P_CHANGE_LINE_ID;
SELECT LANGS.LANGUAGE_CODE
FROM FND_LANGUAGES LANGS
WHERE LANGS.installed_flag IN ('B','I');
insert into EGO_MTL_SY_ITEMS_CHG_B(
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
CHANGE_ID,
CHANGE_LINE_ID,
ACD_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
DESCRIPTION,
ITEM_NUMBER)
values
(
p_item_id,
p_organization_id,
p_change_id,
p_change_line_id,
'CHANGE',
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.user_id,
SYSDATE,
P_ITEM_DESC,
P_ITEM_NUM
);
insert into EGO_MTL_SY_ITEMS_CHG_TL(
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
CHANGE_ID,
CHANGE_LINE_ID,
ACD_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LANGUAGE,
SOURCE_LANG
)
values
(
p_item_id,
p_organization_id,
p_change_id,
p_change_line_id,
'CHANGE',
FND_GLOBAL.user_id,
SYSDATE,
FND_GLOBAL.user_id,
FND_GLOBAL.user_id,
SYSDATE,
LANG_CODE.LANGUAGE_CODE,
USERENV('LANG')
);
ELSIF 'UPDATE' = p_transaction_mode
THEN
UPDATE EGO_MTL_SY_ITEMS_CHG_B
SET DESCRIPTION = p_item_desc,
ITEM_NUMBER = p_item_num
where change_line_id = p_change_line_id;