[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_sql_sttmnt := 'select ''BACKLOG'', ' ||
biv_core_pkg.g_base_column || ' col1,' ||
'sum(decode(stat.close_flag,''Y'',0,1)) col4,
sum(decode(sr.incident_status_id, :x_stat1,1,0)) col6,
sum(decode(sr.incident_status_id, :x_stat2,1,0)) col8,
sum(decode(sr.incident_status_id, :x_stat3,1,0)) col10,
:session_id '||
x_from_list || x_where_clause ||
' group by ' || biv_core_pkg.g_base_column
-- || ' order by ' || nvl(biv_core_pkg.g_srt_by,'2')
;
x_sql_sttmnt := 'insert into biv_tmp_rt2(report_code,col1,col4,
col6,col8,col10,session_id)
' || x_sql_sttmnt ;
biv_core_pkg.update_base_col_desc('biv_tmp_rt2');
update biv_tmp_rt2
set col12 = col4 - col6 - col8 - col10
where report_code = 'BACKLOG'
and session_id = l_session_id;
select decode(biv_core_pkg.g_srt_by, '2', 'to_number(col4) desc',
'3', 'to_number(col6) desc',
'4', 'to_number(col8) desc',
'5', 'to_number(col10) desc',
'6', 'to_number(col12) desc',
'col2 asc')
into l_order_by from dual;
insert into biv_tmp_rt2 (report_code, rowno, col1, col2,
col4, col6, col8, col10, col12, session_id)
select ''BIV_RT_BACKLOG_BY_STATUS'', rownum,
col1, col2, col4, col6, col8, col10, col12,session_id
from (select col1, col2, col4, col6, col8, col10, col12,session_id
from biv_tmp_rt2
where report_code = ''BACKLOG''
and session_id = :session_id
order by ' || l_order_by || ')
where rownum <= :rows_to_display ';-- || nvl(biv_core_pkg.g_disp,10);
update biv_tmp_rt2
set col5= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
'P_STS_ID' || biv_core_pkg.g_value_sep || x_status1 ||
biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
col7= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
'P_STS_ID' || biv_core_pkg.g_value_sep || x_status2 ||
biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
col9= l_new_param_str||col1||biv_core_pkg.g_param_sep ||
'P_STS_ID' || biv_core_pkg.g_value_sep || x_status3 ||
biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
col11=l_new_param_str||col1||biv_core_pkg.g_param_sep ||
'P_OTHER_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
col3= l_new_param_str || col1 ||
biv_core_pkg.g_param_sep || 'P_BLOG' ||
biv_core_pkg.g_value_sep || 'Y' ||
biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
creation_date = sysdate
where report_code = 'BIV_RT_BACKLOG_BY_STATUS'
and session_id = l_session_id
;
delete from biv_tmp_rt2
where report_code = 'BACKLOG'
and session_id = l_session_id;
select count(*) into l_ttl_recs
from biv_tmp_rt2
where report_code = 'BIV_RT_BACKLOG_BY_STATUS'
and session_id = l_session_id;
insert into biv_tmp_rt2 (report_code, rowno,
col4, col6, col8, col10, col12, col13,session_id)
select report_code, max(rowno) + 1, sum(col4), sum(col6), sum(col8),
sum(col10), sum(col12), 'Y', session_id
from biv_tmp_rt2
where session_id = l_session_id
and report_code = 'BIV_RT_BACKLOG_BY_STATUS'
group by report_code, session_id;
update biv_tmp_rt2
set col5= l_ttl_param_str||
'P_STS_ID' || biv_core_pkg.g_value_sep || x_status1 ||
biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
col7= l_ttl_param_str||
'P_STS_ID' || biv_core_pkg.g_value_sep || x_status2 ||
biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
col9= l_ttl_param_str||
'P_STS_ID' || biv_core_pkg.g_value_sep || x_status3 ||
biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
col11=l_ttl_param_str||
'P_OTHER_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
col3= l_ttl_param_str ||
'P_BLOG' || biv_core_pkg.g_value_sep || 'Y' ||
biv_core_pkg.g_param_sep || 'P_UA' || biv_core_pkg.g_value_sep || 'N' ||
biv_core_pkg.g_param_sep || 'P_PREVR' || biv_core_pkg.g_value_sep || 'BIV_RT_BACKLOG_BY_STATUS',
col2= l_ttl_meaning,
creation_date = sysdate
where report_code = 'BIV_RT_BACKLOG_BY_STATUS'
and session_id = l_session_id
and col13 = 'Y'
;
select ' || biv_core_pkg.g_base_column || ' base_col,
count(distinct sr.incident_id) no_of_srs,
count(distinct task.task_id) no_of_tasks';
insert into biv_tmp_rt2(report_code,rowno,
col1,col4, col6,session_id)
select ''X'', rownum,
base_col, no_of_srs, no_of_tasks, :session_id
from (' || l_sql_sttmnt || l_from_list || l_where_clause ||
' group by ' || biv_core_pkg.g_base_column || ')';
biv_core_pkg.update_base_col_desc('biv_tmp_rt2');
select decode(biv_core_pkg.g_srt_by,'2','to_number(col4) desc',
'3','to_number(col6) desc',
'col2 asc')
into l_order_by
from dual;
insert into biv_tmp_rt2(report_code,rowno, col1,col2,
col4, col6,session_id)
select ''BIV_RT_TASK_ACTIVITY'', rownum,
col1, col2, col4, col6,session_id
from ( select col1, col2, col4, col6,session_id
from biv_tmp_rt2
where report_code =''X''
and session_id = :session_id
order by ' || l_order_by || ' ,col2)
where rownum <= :rows_to_display ';--|| nvl(biv_core_pkg.g_disp,'10') ;
update biv_tmp_rt2
set col5= l_new_param_str2||nvl(col1,biv_core_pkg.g_null)||
biv_core_pkg.g_param_sep,
col3= l_new_param_str1|| nvl(col1,biv_core_pkg.g_null) ||
biv_core_pkg.g_param_sep || 'P_OTT' ||
biv_core_pkg.g_value_sep || 'Y',
creation_date = sysdate
where report_code = 'BIV_RT_TASK_ACTIVITY'
and session_id = l_session_id
;
delete from biv_tmp_rt2
where report_code = 'X';
select count(*) into l_ttl_recs
from biv_tmp_rt2
where report_code = 'BIV_RT_TASK_ACTIVITY'
and session_id = l_session_id;
insert into biv_tmp_rt2 (report_code, rowno,
col4, col6, col13,session_id)
select report_code, max(rowno) + 1, sum(col4), sum(col6),
'Y', session_id
from biv_tmp_rt2
where session_id = l_session_id
and report_code = 'BIV_RT_TASK_ACTIVITY'
group by report_code, session_id;
update biv_tmp_rt2
set col5= l_ttl_param_str2||
biv_core_pkg.g_param_sep,
col3= l_ttl_param_str1||
biv_core_pkg.g_param_sep || 'P_OTT' ||
biv_core_pkg.g_value_sep || 'Y',
col2= l_ttl_meaning,
creation_date = sysdate
where report_code = 'BIV_RT_TASK_ACTIVITY'
and session_id = l_session_id
and col13 = 'Y';
insert into biv_tmp_rt2 (report_code, col1, col2, col3, col6,
col8, col10,
col12, col14, col15,col16, col17,
col19,session_id,creation_date)
select ''BIV_OPEN_TASKS'', ''task' ||
biv_core_pkg.g_param_sep || 'task_id' || biv_core_pkg.g_value_sep
|| ''' || task.task_id,
task.task_number,
task.owner_id, tstat.name, null,
task.creation_date, task.last_update_date,
task.escalation_level,
''X' || biv_core_pkg.g_param_sep ||'SR_ID'||
biv_core_pkg.g_value_sep || ''' || task.source_object_id,
sr.incident_number,
sr.incident_owner_id,
sr.inventory_item_id,
:session_id,sysdate
from ' || l_from_list || l_where_clause;
biv_core_pkg.update_description('P_AGENT_ID','col3' ,'col4' , 'biv_tmp_rt2');
biv_core_pkg.update_description('P_AGENT_ID','col17','col18', 'biv_tmp_rt2');
biv_core_pkg.update_description('P_PRD_ID' ,'col19','col20', 'biv_tmp_rt2');
select sr.incident_id col1,
sr.incident_number col2,
sr.incident_owner_id col3,
sr.incident_type_id col5,
sr.customer_id col7,
decode(sr.customer_product_id,null,sr.inv_component_id,
sr.cp_component_id) col9,
sr.sr_creation_channel col10,
sr.inventory_item_id col11,
decode(sr.customer_product_id,null,sr.inv_subcomponent_id,
sr.cp_subcomponent_id) col13,
sr.product_revision col14,
sr.platform_id col16,
sr.incident_date col17,
sr.close_date col18,
trunc(sysdate) - trunc(sr.incident_date) col19,
sr.last_update_date col20,
sr.incident_severity_id col22,
sr.incident_status_id col24
';
insert into biv_tmp_hs2(report_code,rowno,
col1, col2, col3, col5,
col7, col9, col10, col11, col13, col14,
col16, col17, col18,
col19, col20, col22, col24,session_id,
creation_date)
select ''BIV_SERVICE_REQUEST'', rownum,
col1, col2, col3, col5,
col7, col9, col10, col11, col13,
col14, col16, col17, col18,
col19, col20, col22, col24,:session_id,sysdate
from ( ' || l_sql_sttmnt || ' )';
update biv_tmp_hs2 r
set col4 = (select substr(source_name,1,50) from jtf_rs_resource_extns s
where s.resource_id = r.col3)
where report_code = 'BIV_SERVICE_REQUEST'
and session_id = l_session_id;
update biv_tmp_hs2 r
set col6 = (select substr(name,1,50) from cs_incident_types_vl s
where s.incident_type_id = r.col5)
where report_code = 'BIV_SERVICE_REQUEST'
and session_id = l_session_id;
update biv_tmp_hs2 r
set col8 = (select substr(party_name,1,50) from hz_parties p
where p.party_id = r.col7)
where report_code = 'BIV_SERVICE_REQUEST'
and session_id = l_session_id;
update biv_tmp_hs2 r
set col10= (select sr_creation_channel
from cs_incidents_all_tl inc
where inc.incident_id = r.col1
and inc.language = userenv('LANG'))
where report_code = 'BIV_SERVICE_REQUEST'
and session_id = l_session_id;
update biv_tmp_hs2 r
set col12= (select substr(description,1,50) from mtl_system_items_vl i
where i.organization_id = biv_core_pkg.g_prd_org
and i.inventory_item_id = r.col11)
where report_code = 'BIV_SERVICE_REQUEST'
and session_id = l_session_id;
update biv_tmp_hs2 r
set col9 = (select substr(description,1,50) from mtl_system_items_vl i
where i.organization_id = biv_core_pkg.g_prd_org
and i.inventory_item_id = r.col9)
where report_code = 'BIV_SERVICE_REQUEST'
and session_id = l_session_id;
update biv_tmp_hs2 r
set col13= (select substr(description,1,50) from mtl_system_items_vl i
where i.organization_id = biv_core_pkg.g_prd_org
and i.inventory_item_id = r.col13)
where report_code = 'BIV_SERVICE_REQUEST'
and session_id = l_session_id;
update biv_tmp_hs2 r
set col23= (select substr(name,1,50) from cs_incident_severities_vl s
where s.incident_severity_id = r.col22)
where report_code = 'BIV_SERVICE_REQUEST'
and session_id = l_session_id;
update biv_tmp_hs2 r
set col25= (select substr(name,1,50) from cs_incident_statuses_vl s
where s.incident_status_id = r.col24)
where report_code = 'BIV_SERVICE_REQUEST'
and session_id = l_session_id;
update biv_tmp_hs2 r
set col21= (select task.escalation_level
from jtf_tasks_b task,
jtf_task_references_b ref
where ref.object_type_code = 'SR'
and ref.object_id = r.col1
and ref.reference_code = 'ESC'
and ref.task_id = task.task_id
and task_type_id = 22)
where report_code = 'BIV_SERVICE_REQUEST'
and session_id = l_session_id;
update biv_tmp_hs2
set col15 = col1,
col1 = 'BIV_SERVICE_REQUEST' || biv_core_pkg.g_param_sep ||
'SR_ID=' || col1 || l_new_param_str
--'SrCreate_SrID=' || col1 || l_new_param_str
where col1 is not null
and report_code = 'BIV_SERVICE_REQUEST'
and session_id = l_session_id;
select name into l_name
from cs_incident_statuses_vl
where incident_status_id = to_number(p_sts_id);
update biv_tmp_hs2 rep
set col26 = (select count(*)
from jtf_tasks_b tsk,
jtf_task_statuses_b tstat
where source_object_type_code = 'SR'
and source_object_id = to_number(rep.col15)
and tsk.task_status_id = tstat.task_status_id
and nvl(tstat.closed_flag,'N') <> 'Y'
)
where report_code = 'BIV_SERVICE_REQUEST'
and session_id = biv_core_pkg.get_session_id;