[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure delete_log( p_session_id number
, p_incident_id number)
is
PRAGMA AUTONOMOUS_TRANSACTION;
delete from cs_sr_log_data_temp
where session_id = p_session_id
and incident_id = p_incident_id;
end delete_log;
procedure insert_log( p_session_id number
, p_incident_id number
, p_log_text varchar2)
is
PRAGMA AUTONOMOUS_TRANSACTION;
insert into cs_sr_log_data_temp
( session_id,
incident_id,
log_text,
display_seq)
VALUES ( p_session_id,
p_incident_id,
p_log_text,
cs_sr_log_data_temp_pvt.display_seq);
end insert_log;
select userenv('SESSIONID') into l_session_id from dual;
cs_sr_log_data_temp_pvt.insert_log_data(l_session_id, p_incident_id );
select 'AUDIT' source_type,
audi.incident_id,
AUDI.incident_audit_id,
FU.USER_NAME last_updated_by,
AUDI.last_update_date last_update_date,
to_char(AUDI.old_incident_severity_id) severity_old,
to_char(AUDI.incident_severity_id) severity_new,
AUDI.CHANGE_INCIDENT_SEVERITY_FLAG incident_severity_flag,
to_char(AUDI.old_incident_type_id) type_old,
to_char(AUDI.incident_type_id) type_new,
AUDI.CHANGE_INCIDENT_TYPE_FLAG incident_type_flag,
to_char(AUDI.old_incident_status_id) status_old,
to_char(AUDI.incident_status_id) status_new,
AUDI.CHANGE_INCIDENT_STATUS_FLAG incident_status_flag,
to_char(AUDI.old_incident_urgency_id) urgency_old,
to_char(AUDI.incident_urgency_id) urgency_new,
AUDI.CHANGE_INCIDENT_URGENCY_FLAG incident_urgency_flag,
to_char(AUDI.old_incident_owner_id) owner_old,
to_char(AUDI.incident_owner_id) owner_new,
AUDI.CHANGE_INCIDENT_OWNER_FLAG incident_owner_flag,
to_char(AUDI.old_expected_resolution_date) date_old,
to_char(AUDI.expected_resolution_date) date_new,
AUDI.CHANGE_RESOLUTION_FLAG resolution_date_flag,
severity1.name old_severity_name ,
severity2.name new_severity_name ,
type1.name old_type_name ,
type2.name new_type_name ,
status1.name old_status_name ,
status2.name new_status_name ,
urgency1.name old_urgency_name ,
urgency2.name new_urgency_name ,
decode(audi.old_resource_type,'RS_EMPLOYEE',ext1.source_last_name||' '||ext1.source_first_name,'RS_TEAM',team1.team_name,'RS_GROUP',grp1.group_name) old_owner,
decode(audi.resource_type,'RS_EMPLOYEE',ext2.source_last_name||' '||ext2.source_first_name,'RS_TEAM',team2.team_name,'RS_GROUP',grp2.group_name) new_owner,
obj1.name old_resource_type ,
obj2.name resource_type ,
audi.change_resource_type_flag
from fnd_user FU,
cs_incidents_AUDIT_vl AUDI ,
cs_incident_severities_tl severity1 ,
cs_incident_severities_tl severity2 ,
cs_incident_types_tl type1 ,
cs_incident_types_tl type2 ,
cs_incident_statuses_tl status1 ,
cs_incident_statuses_tl status2 ,
cs_incident_urgencies_tl urgency1 ,
cs_incident_urgencies_tl urgency2 ,
jtf_rs_resource_extns ext1,
jtf_rs_teams_tl team1,
jtf_rs_groups_tl grp1,
jtf_rs_resource_extns ext2,
jtf_rs_teams_tl team2,
jtf_rs_groups_tl grp2,
jtf_objects_tl obj1 ,
jtf_objects_tl obj2
where audi.incident_id = p_incident_id
and FU.user_id = AUDI.last_updated_by
and severity1.incident_severity_id(+) = audi.old_incident_severity_id
and (severity1.language = userenv('LANG') or severity1.language is null)
and severity2.incident_severity_id(+) = audi.incident_severity_id
and (severity2.language = userenv('LANG') or severity2.language is null)
and type1.incident_type_id(+) = audi.old_incident_type_id
and (type1.language = userenv('LANG') or type1.language is null)
and type2.incident_type_id(+) = audi.incident_type_id
and (type2.language = userenv('LANG') or type2.language is null)
and status1.incident_status_id(+) = audi.old_incident_status_id
and (status1.language = userenv('LANG') or status1.language is null)
and status2.incident_status_id(+) = audi.incident_status_id
and (status2.language = userenv('LANG') or status2.language is null)
and urgency1.incident_urgency_id(+) = audi.old_incident_urgency_id
and (urgency1.language = userenv('LANG') or urgency1.language is null)
and urgency2.incident_urgency_id(+) = audi.incident_urgency_id
and (urgency2.language = userenv('LANG') or urgency2.language is null)
and ext1.resource_id(+) = audi.old_incident_owner_id
and team1.team_id(+) = audi.old_incident_owner_id
and (team1.language = userenv('LANG') or team1.language is null)
and grp1.group_id(+) = audi.old_incident_owner_id
and (grp1.language = userenv('LANG') or grp1.language is null)
and ext2.resource_id(+) = audi.incident_owner_id
and team2.team_id(+) = audi.incident_owner_id
and (team2.language = userenv('LANG') or team2.language is null)
and grp2.group_id(+) = audi.incident_owner_id
and (grp2.language = userenv('LANG') or grp2.language is null)
and obj1.object_code(+) = audi.old_resource_type
and (obj1.language = userenv('LANG') or obj1.language is null)
and obj2.object_code(+) = audi.resource_type
and (obj2.language = userenv('LANG') or obj2.language is null);
cursor task_cursor is select 'TASK' source_type,jtf.task_id,fnd.user_name,
jtf.last_update_date,jtf.description
from jtf_tasks_vl jtf, fnd_user fnd
where source_object_type_code='SR'
and source_object_id=p_incident_id
and jtf.last_updated_by = fnd.user_id;
cursor note_cursor is select 'NOTE' source_type,jtf.jtf_note_id,fnd.user_name,
jtf.last_update_date,jtf.notes
from jtf_notes_vl jtf, fnd_user fnd
where source_object_code='SR'
and source_object_id=p_incident_id
and jtf.last_updated_by = fnd.user_id;
cursor activity_cursor is select 'ACTIVITY' source_type,jtf.interaction_id,
act.short_description,fnd.user_name,jtf.last_update_date
from jtf_ih_activities jtf, jtf_ih_actions_tl act,
fnd_user fnd
where doc_ref = 'SR'
and doc_id = p_incident_id
and jtf.action_id = act.action_id
and act.language = userenv('LANG')
and jtf.last_updated_by = fnd.user_id;
select 'SOLN' source_type, setv.set_id,
setv.name set_summary, sett.name set_type_name,
elev.name element_summary, elet.name element_type_name,
setl.last_update_date, fnd.user_name
from cs_kb_set_links setl, cs_kb_sets_vl setv,
cs_kb_set_types_tl sett, cs_kb_element_links elel,
cs_kb_elements_vl elev, cs_kb_element_types_tl elet,
fnd_user fnd
where setl.object_code ='SR'
and setl.other_id = p_incident_id
and setl.set_id=setv.set_id
and sett.set_type_id=setv.set_type_id(+)
and (sett.language = userenv('LANG') or sett.language is null)
and (elel.object_code='KB' or elel.object_code is null)
and elel.other_id(+)=setv.set_id
and elel.element_id=elev.element_id(+)
and elev.element_type_id = elet.element_type_id(+)
and (elet.language = userenv('LANG') or elet.language is null)
and setl.last_updated_by = fnd.user_id;
i_ctn.last_update_date,
i_ctn.last_updated_by,
i_ctn.source_type);
i_ctn.last_update_date,
i_ctn.last_updated_by,
i_ctn.source_type);
i_ctn.last_update_date,
i_ctn.last_updated_by,
i_ctn.source_type);
i_ctn.last_update_date,
i_ctn.last_updated_by,
i_ctn.source_type);
i_ctn.last_update_date,
i_ctn.last_updated_by,
i_ctn.source_type);
i_ctn.last_update_date,
i_ctn.last_updated_by,
i_ctn.source_type);
i_ctn.last_update_date,
i_ctn.last_updated_by,
i_ctn.source_type);
cs_sr_log_data_temp_pvt.main_log_date(i_main_pointer) := i_ctn.last_update_date;
cs_sr_log_data_temp_pvt.other_log_text(i_other_pointer) := '*** '||'parameter.log_tasks'||': '||i_ctn.user_name||' '||rpad(to_char(i_ctn.last_update_date,'DD-MON-YYYY HH24:MI:SS'),20,' ')||gs_newline||i_ctn.description||gs_newline;
cs_sr_log_data_temp_pvt.main_log_date(i_main_pointer) := i_ctn.last_update_date;
cs_sr_log_data_temp_pvt.other_log_text(i_other_pointer) := '*** '||'parameter.log_notes'||': '||i_ctn.user_name||' '||rpad(to_char(i_ctn.last_update_date,'DD-MON-YYYY HH24:MI:SS'),20,' ')||gs_newline||i_ctn.notes||gs_newline;
cs_sr_log_data_temp_pvt.main_log_date(i_main_pointer) := i_ctn.last_update_date;
cs_sr_log_data_temp_pvt.main_log_text(i_main_pointer):= cs_sr_log_data_temp_pvt.main_log_text(i_main_pointer)||' '||rpad(to_char(i_ctn.last_update_date,'DD-MON-YYYY HH24:MI:SS'),20,' ')||' '||i_ctn.short_description||gs_newline;
cs_sr_log_data_temp_pvt.main_log_date(i_main_pointer) := i_ctn.last_update_date;
cs_sr_log_data_temp_pvt.main_log_text(i_main_pointer) := '*** '||'parameter.log_knowledge'||': '||i_ctn.user_name||' '||rpad(to_char(i_ctn.last_update_date,'DD-MON-YYYY HH24:MI:SS'),20,' ');
last_update_date in date,
last_updated_by in varchar2,
source_type in varchar2) return number is
l_formated_string varchar2(200);
l_formated_string := '*** '||'parameter.log_audit'||': '||last_updated_by||' '||rpad(to_char(last_update_date,'DD-MON-YYYY HH24:MI:SS'),20,' ')||' '||rpad(column_name,20,' ')||' '||old_field_name||' --> '||new_field_name;
cs_sr_log_data_temp_pvt.main_log_date(i_sort_pointer) := last_update_date;
i_mid := i_end; -- Selecting an arbitary mid point
procedure insert_log_data(p_session_id NUMBER,
p_incident_id NUMBER) is
i_counter integer;
This function is used to insert data from the memory arrays to
the field LOG_NOTES.LOG_DETAILS.
*/
-- Delete the records first
--
cs_sr_log_data_temp_pvt.delete_log( p_session_id, p_incident_id);
cs_sr_log_data_temp_pvt.insert_log( p_session_id, p_incident_id,
cs_sr_log_data_temp_pvt.main_log_text(i_counter));
cs_sr_log_data_temp_pvt.insert_log( p_session_id, p_incident_id,
cs_sr_log_data_temp_pvt.main_log_text(i_counter));
cs_sr_log_data_temp_pvt.insert_log( p_session_id, p_incident_id,
cs_sr_log_data_temp_pvt.other_log_text(i_position));
cs_sr_log_data_temp_pvt.insert_log( p_session_id, p_incident_id,
cs_sr_log_data_temp_pvt.other_log_text(i_position));
cs_sr_log_data_temp_pvt.insert_log( p_session_id, p_incident_id,
cs_sr_log_data_temp_pvt.main_log_text(i_counter));
cs_sr_log_data_temp_pvt.insert_log( p_session_id, p_incident_id, cs_sr_log_data_temp_pvt.other_log_text(i_position));
end insert_log_data;