[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ' || biv_core_pkg.g_base_column || ' col1,
srs.escalation_level col4,
count(distinct sr.incident_id) col6
' || l_from_list || '
' || l_where_clause || '
group by ' || biv_core_pkg.g_base_column || ',srs.escalation_level
' ;
l_sql_sttmnt := 'insert into biv_tmp_hs2(report_code, rowno,
col1,col4,col6,session_id)
select ''SR_ESC'', rownum, col1, col4, col6, :session_id from (
' || l_sql_sttmnt || ')
';
biv_core_pkg.update_base_col_desc('biv_tmp_hs2');
l_sql_sttmnt := 'insert into biv_tmp_hs2(report_code, rowno,
col1,col2, col4,col6,session_id)
select ''BIV_HS_SR_ESCALATION'', rownum * 2, col1, col2, col4,
col6 ,session_id
from (
select col1, col2, col4, col6,session_id
from biv_tmp_hs2
where report_code = ''SR_ESC''
and session_id = :session_id
order by ' || l_order_by || ' ,nvl(col2,'' ''))
where rownum <= :rows_to_display '; -- || biv_core_pkg.g_disp ;
delete from biv_tmp_hs2
where report_code = 'SR_ESCaa'
and session_id = l_session_id;
l_loc := 'Before update of odd col';
update biv_tmp_hs2
set /*col5 = l_new_param_str || col1 || biv_core_pkg.g_param_sep ||
'P_ESC_LVL' || biv_core_pkg.g_value_sep || col4,*/
-- this was column col3
col5 = l_new_param_str1 || nvl(col1,biv_core_pkg.g_null) ||
biv_core_pkg.g_param_sep ||
'P_ESC_LVL' || biv_core_pkg.g_value_sep || col4,
creation_date = sysdate
where session_id = l_session_id
and report_code = 'BIV_HS_SR_ESCALATION' ;
insert into biv_tmp_hs2 (report_code, rowno, col4, col6,session_id,col5,col9,
creation_date)
select ''BIV_HS_SR_ESCALATION'', max(rowno)+1,
''Total '' || nvl(' || l_order_by ||','' ''), sum(col6), :session_id,
:l_new_param_str1 || nvl('||l_param_col ||',''' ||
biv_core_pkg.g_null ||'''),''Total'', sysdate
from biv_tmp_hs2
where report_code = ''BIV_HS_SR_ESCALATION''
and session_id = :session_id
group by nvl(' ||l_order_by ||','' '')' ||
', nvl('|| l_param_col || ','''||biv_core_pkg.g_null ||
''')'
;
insert into biv_tmp_hs2 (report_code, rowno, col4, col6,session_id,col5,
creation_date)
select 'BIV_HS_SR_ESCALATION', max(rowno)+1,
'Grand Total ', sum(col6), l_session_id,
l_gt_param_str, sysdate
from biv_tmp_hs2
where report_code = 'BIV_HS_SR_ESCALATION'
and session_id = l_session_id
and (l_order_by = 'col6 ' or col9 = 'Total')
--and col1 is not null
;
select sr.customer_id col1, srs.escalation_level col4,
sr.incident_id col5,
sr.incident_number col6, sr.incident_owner_id col7,
srs.esc_owner_id col9, sr.inventory_item_id col11,
null col14, sr.platform_id col16' || l_from_list ||
l_where_clause ; --|| '
insert into biv_tmp_hs2 (report_code,
col1,col4, col5, col6, col7, col9, col11, col14, col16,
session_id)
select ''TEMP'', col1,col4,col5,col6, col7, col9, col11,
col14, col16, :x_session_id
from (' || l_sql_sttmnt || ')'
;
biv_core_pkg.update_description('P_AGENT_ID','col7' ,'col8' ,'biv_tmp_hs2');
biv_core_pkg.update_description('P_AGENT_ID','col9' ,'col10','biv_tmp_hs2');
biv_core_pkg.update_description('P_PRD_ID' ,'col11','col12','biv_tmp_hs2');
biv_core_pkg.update_description('P_CUST_ID' ,'col1' ,'col2' ,'biv_tmp_hs2');
insert into biv_tmp_hs2 (report_code, rowno,
col1, col2, col4, col5, col6, col7, col8, col9, col10,
col11, col12, col14, col16,session_id)
select * from (
select 'BIV_HS_ESCALATION_VIEW' report_code, rownum rowno,
col1, col2, col4, col5, col6, col7, col8, col9, col10,
col11, col12, col14, col16,session_id
from biv_tmp_hs2
where report_code = 'TEMP'
and session_id = l_session_id
order by col2)
;
delete from biv_tmp_hs2 where report_code = 'TEMP'
and session_id = l_session_id;
update biv_tmp_hs2
set col5 = 'X' || biv_core_pkg.g_param_sep || 'SR_ID=' || col5,
--set col5 = 'X' || biv_core_pkg.g_param_sep || 'SrCreate_SrID=' || col5,
creation_date = sysdate
where report_code = 'BIV_HS_ESCALATION_VIEW'
and session_id = l_session_id;
select ' || biv_core_pkg.g_base_column ||' col1,
sum(decode(sign(sr.incident_date-:y_start_date1),
-1,decode(nvl(close_flag,''N''),''N'',1,
decode(sign(nvl(sr.close_date,sysdate-1000)-
:y_start_date2),-1,0,1)
),
0)) col4, --open_blog
sum(decode(sign(sr.incident_date-:y_start_date3),
-1,0,decode(sign(sr.incident_date-:y_end_date1),-1,1,0))
) col6, --new_sr
sum(decode(sign(nvl(sr.close_date,sysdate+1000)-:y_start_date4),
-1,0,decode(sign(nvl(sr.close_date,sysdate+1000)-
:y_end_date2),-1,1,0))
) col8, --closed_sr
sum(decode(sign(srs.reopen_date-:y_start_date5),
-1,0,decode(sign(srs.reopen_date-:y_end_date3),-1,1,0))
) col10, --reopened_sr
sum(decode(sign(srs.reclose_date-:y_start_date6),
-1,0,decode(sign(srs.reclose_date-:y_end_date4),-1,1,0))
) col12, --reclosed_sr
/* 5/9/2 this is causing problem with null close_date
sum(decode(sign(sr.incident_date-:y_end_date5),
-1,decode(nvl(stat.close_flag,''N''), ''Y'',0,1)
)) col14,
*/
sum(decode(sign(sr.incident_date-:y_end_date5),
-1,decode(nvl(close_flag,''N''),''N'',1,
decode(sign(nvl(sr.close_date,sysdate-1000)-
:y_end_date6)-1,0,1)
),
0)
) col14,
avg(srs.days_to_close) col16, --time_to_close
sum(decode(sign(sr.incident_date-:y_start_date7),
-1,0,decode(sign(sr.incident_date-:y_end_date7),1,0,
decode(sr.sr_creation_channel,''WEB'',1,0))
)
) col18, --new_web_sr
0 col20, --updated_via_web
avg(decode(sr.sr_creation_channel,''WEB'',srs.response_time,null)) col22, --resp_time'; --Bug 2960243
insert into biv_tmp_hs2(report_code,rowno,
col1,col4,col6,col8,col10,col12,
col14, col16,col18,col20, col22, col24,
session_id)
select ''SR_ACT'', rownum, col1,
col4, col6, col8,
col10, col12, col14,
to_char(col16,''999,999.99''),
col18, col20,
to_char(col22*24,''999,999,999.00''),
col24, :session_id
from (' || l_sql_sttmnt || l_from_list || l_where_clause ||
' group by ' || biv_core_pkg.g_base_column || ')'
/*order by ' || l_order_by || ')
where rownum <= ' || biv_core_pkg.g_disp */ ;
biv_core_pkg.update_base_col_desc('biv_tmp_hs2');
insert into biv_tmp_hs2(report_code,rowno,
col1,col2,col4,col6,col8,col10,col12,
col14, col16,col18,col20, col22, col24,
session_id)
select ''BIV_HS_SR_ACTIVITY'', rownum, col1,
col2,col4, col6, col8,
col10, col12, col14,
col16, col18, col20, col22, col24,session_id
from ( select col1,col2, col4, col6, col8, col10, col12,
col14, col16, col18, col20, col22, col24, session_id
from biv_tmp_hs2
where report_code = ''SR_ACT''
and session_id = :session_id
order by ' || l_order_by || ')' ;
l_loc := 'Before update of odd cols for drill down';
update biv_tmp_hs2
set id = col1,
col1 = l_new_param_str1|| nvl(col1,biv_core_pkg.g_null) ||
biv_core_pkg.g_param_sep || 'P_VIEW_BY' ||
biv_core_pkg.g_value_sep || l_new_view_by ||
biv_core_pkg.g_param_sep ||'P_ST_DATE' ||
biv_core_pkg.g_value_sep ||
to_char(l_start_date,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||'P_END_DATE' ||
biv_core_pkg.g_value_sep ||
to_char(l_end_date-1,l_dt_fmt),
col3 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
biv_core_pkg.g_param_sep ||'P_ST_DATE' ||
biv_core_pkg.g_value_sep ||
to_char(l_start_date,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||
'P_OBLOG' || biv_core_pkg.g_value_sep || 'Y',
col5 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
biv_core_pkg.g_param_sep ||'P_CR_ST' ||
biv_core_pkg.g_value_sep ||
to_char(l_start_date,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||'P_CR_END' ||
biv_core_pkg.g_value_sep ||
-- Change for Bug 3285048 l_end_date changed to l_end_date-1
to_char(l_end_date-1,l_dt_fmt),
col7 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
biv_core_pkg.g_param_sep ||'P_CL_ST' ||
biv_core_pkg.g_value_sep ||
to_char(l_start_date,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||'P_CL_END' ||
biv_core_pkg.g_value_sep ||
to_char(l_end_date-1,l_dt_fmt) ,
col9 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
biv_core_pkg.g_param_sep ||'P_ST_DATE' ||
biv_core_pkg.g_value_sep ||
to_char(l_start_date,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||'P_END_DATE' ||
biv_core_pkg.g_value_sep ||
-- Change for Bug 3285048 l_end_date changed to l_end_date-1
to_char(l_end_date-1,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||
'P_REOPEN' || biv_core_pkg.g_value_sep || 'Y',
col11 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
biv_core_pkg.g_param_sep ||'P_ST_DATE' ||
biv_core_pkg.g_value_sep ||
to_char(l_start_date,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||'P_END_DATE' ||
biv_core_pkg.g_value_sep ||
-- Change for Bug 3285048 l_end_date changed to l_end_date-1
to_char(l_end_date-1,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||
'P_RECLOSE' || biv_core_pkg.g_value_sep || 'Y',
col13 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
biv_core_pkg.g_param_sep ||'P_END_DATE' ||
biv_core_pkg.g_value_sep ||
to_char(l_end_date,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||
'P_EBLOG' || biv_core_pkg.g_value_sep || 'Y',
col17 = l_new_param_str || nvl(col1,biv_core_pkg.g_null) ||
biv_core_pkg.g_param_sep ||'P_CR_ST' ||
biv_core_pkg.g_value_sep ||
to_char(l_start_date,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||'P_CR_END' ||
biv_core_pkg.g_value_sep ||
-- Change for Bug 3285048 changed l_end_date to l_end_date-1
to_char(l_end_date-1,l_dt_fmt) ||
biv_core_pkg.g_param_sep || 'P_CHNL=WEB',
creation_date = sysdate
where report_code = 'BIV_HS_SR_ACTIVITY'
and session_id = l_session_id;
SELECT count(*) into l_ttl_recs
FROM biv_tmp_hs2
WHERE report_code = 'BIV_HS_SR_ACTIVITY'
and session_id = l_session_id;
insert into biv_tmp_hs2 (report_code, rowno,
col4, col6, col8, col10, col12,
col14, col18, col1, session_id)
SELECT report_code, max(rowno) + 1, sum(col4), sum(col6), sum(col8),
sum(col10), sum(col12), sum(col14), sum(col18), 'Y', session_id
FROM biv_tmp_hs2
WHERE session_id = l_session_id
and report_code = 'BIV_HS_SR_ACTIVITY'
group by report_code, session_id;
l_loc := 'Before update of odd cols for drill down for Total Row';
SELECT count(*) into l_ttl_recs
FROM biv_tmp_hs2
WHERE report_code = 'BIV_HS_SR_ACTIVITY'
and session_id = l_session_id;
update biv_tmp_hs2
set --id = col1,
col1 = l_new_param_str1|| 'P_VIEW_BY' ||
biv_core_pkg.g_value_sep || l_new_view_by ||
biv_core_pkg.g_param_sep ||'P_ST_DATE' ||
biv_core_pkg.g_value_sep ||
to_char(l_start_date,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||'P_END_DATE' ||
biv_core_pkg.g_value_sep ||
to_char(l_end_date-1,l_dt_fmt),
col3 = l_new_param_str || 'P_ST_DATE' ||
biv_core_pkg.g_value_sep ||
to_char(l_start_date,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||
'P_OBLOG' || biv_core_pkg.g_value_sep || 'Y',
col5 = l_new_param_str || 'P_CR_ST' ||
biv_core_pkg.g_value_sep ||
to_char(l_start_date,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||'P_CR_END' ||
biv_core_pkg.g_value_sep ||
-- Change for Bug 3285048 chnaged l_end_date to l_end_date-1
to_char(l_end_date-1,l_dt_fmt),
col7 = l_new_param_str || 'P_CL_ST' ||
biv_core_pkg.g_value_sep ||
to_char(l_start_date,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||'P_CL_END' ||
biv_core_pkg.g_value_sep ||
to_char(l_end_date-1,l_dt_fmt) ,
col9 = l_new_param_str || 'P_ST_DATE' ||
biv_core_pkg.g_value_sep ||
to_char(l_start_date,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||'P_END_DATE' ||
biv_core_pkg.g_value_sep ||
-- Change for Bug 3285048 changed l_end_date to l_end_date-1
to_char(l_end_date-1,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||
'P_REOPEN' || biv_core_pkg.g_value_sep || 'Y',
col11 = l_new_param_str || 'P_ST_DATE' ||
biv_core_pkg.g_value_sep ||
to_char(l_start_date,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||'P_END_DATE' ||
biv_core_pkg.g_value_sep ||
-- Change for Bug 3285048 changed l_end_date to l_end_date-1
to_char(l_end_date-1,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||
'P_RECLOSE' || biv_core_pkg.g_value_sep || 'Y',
col13 = l_new_param_str || 'P_END_DATE' ||
biv_core_pkg.g_value_sep ||
to_char(l_end_date,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||
'P_EBLOG' || biv_core_pkg.g_value_sep || 'Y',
col17 = l_new_param_str || 'P_CR_ST' ||
biv_core_pkg.g_value_sep ||
to_char(l_start_date,l_dt_fmt) ||
biv_core_pkg.g_param_sep ||'P_CR_END' ||
biv_core_pkg.g_value_sep ||
-- Change for Bug 3285048 changed l_end_date to l_end_date-1
to_char(l_end_date-1,l_dt_fmt) ||
biv_core_pkg.g_param_sep || 'P_CHNL=WEB',
col2 = l_ttl_meaning,
creation_date = sysdate
where report_code = 'BIV_HS_SR_ACTIVITY'
and session_id = l_session_id
and col1 = 'Y';
l_loc := 'After update of odd cols';