[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert into biv_tmp_bin(report_code, col1, col2, col4,session_id)
select ''BIV_BIN_SR_ESCALATION'',lup.lookup_code,lup.meaning,
count(distinct sr.incident_id),:session_id
' || l_from_list || l_where_clause ||
' group by lup.lookup_code, lup.meaning';
insert into biv_tmp_bin(report_code, col1, col2, col4,session_id)
select 'BIV_BIN_SR_ESCALATION',lup.lookup_code,lup.meaning,
count(distinct t.task_id),l_session_id
from jtf_task_references_b r,
cs_incidents_all_b ina,
jtf_tasks_b t,
cs_incident_statuses_b stat,
fnd_lookups lup
where ina.incident_id = r.OBJECT_ID
and ina.incident_status_id = stat.incident_status_id
and nvl(stat.close_flag,'N') <> 'Y'
and r.object_type_code = 'SR'
and r.reference_code = 'ESC'
and r.task_id = t.task_id
and t.task_type_id = 22
and lup.lookup_type = 'JTF_TASK_ESC_LEVEL'
and lup.lookup_code = t.escalation_level
group by lup.lookup_code, lup.meaning;
update biv_tmp_bin
set col1 = l_new_param_str || col1
where report_code = 'BIV_BIN_SR_ESCALATION'
and session_id = l_session_id;
insert into biv_tmp_bin ( report_code, rowno, col1, col2, col4,session_id)
select ''BIV_BIN_RESOURCE'', rownum, id, descr, total,:session_id
from (select nvl(ra.mode_of_availability,''ALL'') id,
nvl(ra.mode_of_availability,:all_meaning) descr,
count(*) total ' ||
l_from_list || l_where_clause || '
group by ra.mode_of_availability)';
/* 3/13/02 web available is being inserted in above statement.
This is due to change sql Statment.
l_from_list := l_from_list || ',
jtf_rs_res_availability avl ';
insert into biv_tmp_bin ( report_code, rowno, col1, col2, col4,session_id)
select ''BIV_BIN_RESOURCE'', rownum, id, descr, total,:session_id
from (select ''WEB'' id, ''WEB'' descr,count(*) total ' ||
l_from_list || l_where_clause || ')';
insert into biv_tmp_bin ( report_code, rowno, col1, col2, col4,session_id)
select 'BIV_BIN_RESOURCE', rownum, id, descr, total,l_session_id
from (select 'ALL' id, 'ALL' descr,count(*) total
from jtf_rs_resource_extns
where category = 'EMPLOYEE'
);
insert into biv_tmp_bin ( report_code, rowno, col1, col2, col4,session_id)
select 'BIV_BIN_RESOURCE', 2, id, descr, total,l_session_id
from (select 'WEB' id, 'WEB' descr,count(*) total
from jtf_rs_res_availability
);
update biv_tmp_bin
set col1 = l_new_param_str || col1 ||
biv_core_pkg.g_param_sep ||
'P_DISP' ||
biv_core_pkg.g_value_sep || col4
where report_code = 'BIV_BIN_RESOURCE'
and session_id = l_session_id;
insert into biv_tmp_rt2(report_code, rowno, id,col2, col4,
col6, col8, col10, col12, col13,session_id)
select ''BIV_TASK_SUMMARY'',
rownum,
task.task_id,
task.task_number,
p.party_name,
stat.name,
ina.incident_date,
task.planned_end_date,
rsc.source_name,
reason_code,
:session_id ' || l_from_list || l_where_clause;
update biv_tmp_rt2
set col1 = 'task' || biv_core_pkg.g_param_sep ||
'task_id' || biv_core_pkg.g_value_sep || id
;
update biv_tmp_rt2 t
set col14 = ( select meaning from fnd_lookups lup
where lup.lookup_code = t.col13
and lup.lookup_type = 'JTF_TASK_REASON_CODES'
);
insert into biv_tmp_rt2(report_code, rowno, col1,col2, col4,
col6, col8, col10, col12, col14,session_id)
select 'BIV_TASK_SUMMARY',
rownum,
'X' || biv_core_pkg.g_param_sep || 'task_id=' ||t.task_id,
t.task_number,
p.party_name,
stat.name,
ina.incident_date,
t.planned_end_date,
rsc.source_name,
reason_code,
l_session_id
from jtf_task_references_b r,
cs_incidents_all_b ina,
cs_incident_statuses_vl stat,
jtf_tasks_b t,
jtf_rs_resource_extns rsc,
hz_parties p
where ina.incident_id = r.OBJECT_ID
and r.object_type_code = 'SR'
and r.reference_code = 'ESC'
and r.TASK_ID = t.task_id
and t.task_type_id = 22
and ina.incident_status_id = stat.incident_status_id
and ina.customer_id = p.party_id
and t.owner_id = rsc.resource_id
and t.escalation_level = l_esc_lvl
and nvl(stat.close_flag,'N') <> 'Y';
insert into biv_tmp_rt2(report_code, rowno, col1,col2, col4, col6,
col8, col10, col12, col14, col16,session_id)
select 'BIV_RELATED_TASK_REPORT', rownum,
tsk.task_id,
tsk.task_number,
tskl.task_name,
tskl.description,
pr.name,
'ESC',
stat.name,
rsc.source_name,
typ.name,
l_session_id
from jtf_tasks_b tsk,
jtf_tasks_tl tskl,
jtf_task_priorities_vl pr,
jtf_task_statuses_vl stat,
jtf_task_types_vl typ,
jtf_rs_resource_extns rsc
where tsk.source_object_type_code = 'SR'
and tsk.source_object_id = to_number(l_sr_id)
and tsk.task_status_id = stat.task_status_id
and tsk.task_type_id = typ.task_type_id
and tsk.task_priority_id = pr.task_priority_id
and tsk.owner_id = rsc.resource_id (+)
and tsk.task_id = tskl.task_id (+)
and userenv('LANG') = tskl.language (+)
;