The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT change_id
INTO l_local_change_id
FROM eng_engineering_changes
WHERE change_id =
(SELECT object_to_id1
FROM eng_change_obj_relationships
WHERE change_id = p_entity_map_rec.change_id
AND (relationship_code = 'PROPAGATED_TO' OR relationship_code = 'TRANSFERRED_TO')
AND object_to_name = 'ENG_CHANGE'
AND object_to_id3 = p_entity_map_rec.local_organization_id);
SELECT ecpm.change_propagation_map_id
FROM eng_change_propagation_maps ecpm
WHERE ecpm.change_id = p_change_id
AND ecpm.local_organization_id = p_local_organization_id
AND ecpm.entity_name = p_entity_name
AND nvl(ecpm.revised_item_sequence_id, -1) = nvl(p_revised_item_sequence_id, -1)
AND nvl(ecpm.revised_line_type, '-1') = nvl(p_revised_line_type, '-1')
AND nvl(ecpm.revised_line_id1, -1) = nvl(p_revised_line_id1, -1)
AND nvl(ecpm.revised_line_id2, -1) = nvl(p_revised_line_id2, -1)
AND nvl(ecpm.revised_line_id3, -1) = nvl(p_revised_line_id3, -1)
AND nvl(ecpm.revised_line_id4, -1) = nvl(p_revised_line_id4, -1)
AND nvl(ecpm.revised_line_id5, -1) = nvl(p_revised_line_id5, -1);
UPDATE eng_change_propagation_maps
SET local_change_id = p_entity_map_rec.local_change_id
, local_revised_item_sequence_id = p_entity_map_rec.local_revised_item_seq_id
, local_revised_line_id1 = p_entity_map_rec.local_revised_line_id1
, local_revised_line_id2 = p_entity_map_rec.local_revised_line_id2
, local_revised_line_id3 = p_entity_map_rec.local_revised_line_id3
, local_revised_line_id4 = p_entity_map_rec.local_revised_line_id4
, local_revised_line_id5 = p_entity_map_rec.local_revised_line_id5
, entity_action_status = p_entity_map_rec.entity_action_status
, creation_date = SYSDATE
, created_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_login = FND_GLOBAL.LOGIN_ID
, program_id = FND_GLOBAL.CONC_PROGRAM_ID--FND_PROFILE.value('CONC_PROGRAM_ID')
, program_application_id = FND_GLOBAL.PROG_APPL_ID--FND_PROFILE.value('PROG_APPL_ID')
, program_update_date = SYSDATE
, request_id = FND_GLOBAL.CONC_REQUEST_ID--FND_PROFILE.value('CONC_REQUEST_ID')
WHERE change_propagation_map_id = p_entity_map_rec.change_propagation_map_id;
SELECT eng_change_propagation_maps_s.nextval
INTO p_entity_map_rec.change_propagation_map_id
FROM DUAL;
INSERT INTO eng_change_propagation_maps(
change_propagation_map_id
, change_id
, revised_item_sequence_id
, local_change_id
, local_revised_item_sequence_id
, local_organization_id
, program_id
, program_application_id
, program_update_date
, request_id
, revised_line_type
, revised_line_id1
, revised_line_id2
, revised_line_id3
, revised_line_id4
, revised_line_id5
, local_revised_line_id1
, local_revised_line_id2
, local_revised_line_id3
, local_revised_line_id4
, local_revised_line_id5
, entity_name
, entity_action_status
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
VALUES(
p_entity_map_rec.change_propagation_map_id
, p_entity_map_rec.CHANGE_ID
, p_entity_map_rec.revised_item_sequence_id
, p_entity_map_rec.local_change_id
, p_entity_map_rec.local_revised_item_seq_id
, p_entity_map_rec.LOCAL_ORGANIZATION_ID
, FND_GLOBAL.CONC_PROGRAM_ID--FND_PROFILE.value('CONC_PROGRAM_ID')
, FND_GLOBAL.PROG_APPL_ID--FND_PROFILE.value('PROG_APPL_ID')
, SYSDATE
, FND_GLOBAL.CONC_REQUEST_ID--FND_PROFILE.value('CONC_REQUEST_ID')
, p_entity_map_rec.revised_line_type
, p_entity_map_rec.revised_line_id1
, p_entity_map_rec.revised_line_id2
, p_entity_map_rec.revised_line_id3
, p_entity_map_rec.revised_line_id4
, p_entity_map_rec.revised_line_id5
, p_entity_map_rec.local_revised_line_id1
, p_entity_map_rec.local_revised_line_id2
, p_entity_map_rec.local_revised_line_id3
, p_entity_map_rec.local_revised_line_id4
, p_entity_map_rec.local_revised_line_id5
, p_entity_map_rec.ENTITY_NAME
, p_entity_map_rec.entity_action_status
, SYSDATE
, FND_GLOBAL.USER_ID
, SYSDATE
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
);
, p_delete_map_logs IN VARCHAR2 := FND_API.G_TRUE
, x_return_status IN OUT NOCOPY VARCHAR2
)
IS
CURSOR c_change_logs (
cp_change_id NUMBER
, cp_local_org_id NUMBER
, cp_change_map_id NUMBER)
IS
SELECT change_log_id
FROM eng_change_logs_b
WHERE change_id = cp_change_id
AND local_organization_id = cp_local_org_id
AND log_type_code <> 'INFO'
AND FND_API.G_TRUE = p_delete_map_logs
AND change_propagation_map_id = cp_change_map_id;
Debug_Log(G_LOG_STATEMENT, 'Delete Errors for Map p_entity_map_rec.change_propagation_map_id'|| p_entity_map_rec.change_propagation_map_id);
Eng_change_logs_pkg.Delete_row (
X_change_log_id => clog.change_log_id
);
SELECT eng_change_logs_s.nextval
INTO l_new_log_id
FROM dual;
Eng_change_logs_pkg.Insert_row (
X_rowid => l_val
, X_change_log_id => l_new_log_id
, X_change_id => p_entity_map_rec.change_id
, X_change_line_id => null
, X_local_revised_item_sequence_ => p_entity_map_rec.local_revised_item_seq_id
, X_log_classification_code => G_ERROR_CLASSIFICATION_CODE
, X_log_type_code => l_log_type_code
, X_local_change_id => p_entity_map_rec.local_change_id
, X_local_change_line_id => null
, X_revised_item_sequence_id => p_entity_map_rec.revised_item_sequence_id
, X_local_organization_id => p_entity_map_rec.local_organization_id
, X_log_text => p_entity_map_rec.message_list(i).message_text
, X_creation_date => SYSDATE
, X_created_by => FND_GLOBAL.USER_ID
, X_last_update_date => SYSDATE
, X_last_updated_by => FND_GLOBAL.USER_ID
, X_last_update_login => FND_GLOBAL.LOGIN_ID
, X_change_propagation_map_id => p_entity_map_rec.change_propagation_map_id
);
, p_delete_map_logs => FND_API.G_TRUE
, x_return_status => l_return_status
);
p_entity_map_rec.message_list.DELETE;
G_Entity_Map_Log_Table.DELETE;
l_message_list.delete;
SELECT eclv.log_text
FROM eng_change_logs_vl eclv
WHERE eclv.change_propagation_map_id = p_change_propagation_map_id;
SELECT count(1)
FROM eng_revised_items new_ri
WHERE new_ri.change_id= p_global_change_id -- global change id
AND NOT EXISTS (
SELECT 1
FROM eng_change_propagation_maps new_ri_map
WHERE new_ri_map.change_id = new_ri.change_id
AND new_ri_map.revised_item_sequence_id = new_ri.revised_item_sequence_id
AND new_ri_map.entity_name = 'ENG_REVISED_ITEM'
AND new_ri_map.local_organization_id = p_local_organization_id)
AND ROWNUM <2;