[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_viewby_select VARCHAR2(32767);
l_url_select VARCHAR2(32767);
l_viewby_select := oki_dbi_util_pvt.get_viewby_select_clause(p_view_by_dim, 'SRM', '6.0');
l_viewby_select := l_viewby_select ||
', OKI_DYNAMIC_URL_1 ,OKI_DYNAMIC_URL_2 ,'|| l_prodcat_url || ' ,oki_measure_1 ,oki_measure_2
,oki_measure_3 ,oki_measure_4 ,oki_measure_5 ,oki_measure_6 ,oki_measure_11 ,oki_measure_12
,oki_measure_13 ,oki_measure_14 ,oki_measure_15, oki_measure_23
,oki_calc_item1 ,oki_calc_item2 , oki_calc_item11 ,oki_calc_item12
FROM (SELECT rank() over (&ORDER_BY_CLAUSE nulls last , '||p_view_by_col||') - 1 rnk ,'||p_view_by_col||'
,OKI_DYNAMIC_URL_1 ,OKI_DYNAMIC_URL_2 ,oki_measure_1 ,oki_measure_2 ,oki_measure_3
,oki_measure_4 ,oki_measure_5 ,oki_measure_6 ,oki_measure_11, oki_measure_12
,oki_measure_13 ,oki_measure_14, sum(oki_measure_5) over() oki_measure_15, oki_measure_23, oki_calc_item1
,oki_calc_item2, oki_calc_item11 ,oki_calc_item12
FROM ( ';
l_url_select :=
'SELECT decode(resource_id,-999, ''pFunctionName=OKI_DBI_SRM_BLG_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP'', '''') OKI_DYNAMIC_URL_1 '||
' , decode(resource_id,-999,'''',decode(rg_id,-1,'''','||l_pastdue_url||')) OKI_DYNAMIC_URL_2 ';
l_url_select :=
' SELECT NULL OKI_DYNAMIC_URL_1 '||
' , '||l_pastdue_url||' OKI_DYNAMIC_URL_2 ';
l_url_select :=
' SELECT NULL OKI_DYNAMIC_URL_1 '||
' , NULL OKI_DYNAMIC_URL_2 ';
l_sel_clause := l_viewby_select || l_url_select ||
-- AK Attribute naming
' ,'|| p_view_by_col ||
' , nvl(c_Or,0) oki_measure_1'||
' , nvl(c_Br,0) oki_measure_2'||
' ,'||POA_DBI_UTIL_PKG.rate_clause('c_Br','c_Or') || 'oki_measure_3 '||
' ,'||OKI_DBI_UTIL_PVT.change_clause(POA_DBI_UTIL_PKG.rate_clause('c_Br','c_Or'),
POA_DBI_UTIL_PKG.rate_clause('p_Br','p_Or'),'P')||'oki_measure_4 '||
' ,'||POA_DBI_UTIL_PKG.rate_clause('c_Br','c_Br_tot') || ' oki_measure_5 '||
' ,'||OKI_DBI_UTIL_PVT.change_clause(POA_DBI_UTIL_PKG.rate_clause('c_Br','c_Br_tot'),
POA_DBI_UTIL_PKG.rate_clause('p_Br','p_Br_tot'),'P')||' oki_measure_6 '||
' , nvl(c_Or_tot,0) oki_measure_11'||
' , nvl(c_Br_tot,0) oki_measure_12'||
' ,'||POA_DBI_UTIL_PKG.rate_clause('c_Br_tot','c_Or_tot') || 'oki_measure_13 '||
' ,'||OKI_DBI_UTIL_PVT.change_clause(POA_DBI_UTIL_PKG.rate_clause('c_Br_tot','c_Or_tot'),
POA_DBI_UTIL_PKG.rate_clause('p_Br_tot','p_Or_tot'),'P')||'oki_measure_14 '||
' ,'||POA_DBI_UTIL_PKG.rate_clause('p_Br','p_Or') || 'oki_measure_23 '||
' ,'||POA_DBI_UTIL_PKG.rate_clause('c_Br','c_Or') || 'oki_calc_item1 '||
' ,'||POA_DBI_UTIL_PKG.rate_clause('p_Br','p_Or') || 'oki_calc_item2 '||
' ,'||POA_DBI_UTIL_PKG.rate_clause('c_Br_tot','c_Or_tot') || 'oki_calc_item11 '||
' ,'||POA_DBI_UTIL_PKG.rate_clause('p_Br_tot','p_Or_tot') || 'oki_calc_item12 '||
' from '||
' ( ';
' select '||
-- Measures Based on a formula
p_view_by_col ||
' ,to_number(NULL) c_Br_itd '||
' ,to_number(NULL) c_or_itd '||
' ,to_number(NULL) p_Br_itd '||
' ,to_number(NULL) p_or_itd '||
' ,NVL2(COALESCE(c_Br1,c_Br2,c_Br3),(NVL(c_Br1,0)-NVL(c_Br2,0)-NVL(c_Br3,0)),NULL) c_Br_ytd '||
' ,NVL2(COALESCE(c_Or1,c_Or2,c_Or3),(NVL(c_Or1,0)-NVL(c_Or2,0)-NVL(c_Or3,0)),NULL) c_Or_ytd '||
' ,NVL2(COALESCE(p_Br1,p_Br2,p_Br3),(NVL(p_Br1,0)-NVL(p_Br2,0)-NVL(p_Br3,0)),NULL) p_Br_ytd '||
' ,NVL2(COALESCE(p_Or1,p_Or2,p_Or3),(NVL(p_Or1,0)-NVL(p_Or2,0)-NVL(p_Or3,0)),NULL) p_Or_ytd '||
' FROM ( '||
' select '||
' '||p_view_by_col ||
' , c_Br1 '||
' , c_Br2 '||
' , c_Br3 '||
' , c_Or1 '||
' , c_Or2 '||
' , c_Or3 '||
' , p_Br1 '||
' , p_Br2 '||
' , p_Br3 '||
' , p_Or1 '||
' , p_Or2 '||
' , p_Or3 ';
l_select0 varchar2(1000);
l_select varchar2(32767);
l_select := 'SUM(c_br_itd) c_br_itd, SUM(c_or_itd) c_or_itd, SUM(p_br_itd) p_br_itd, SUM(p_or_itd)p_or_itd, SUM(to_number(NULL)) c_br_ytd, SUM(to_number(NULL)) c_or_ytd , SUM(to_number(NULL)) p_br_ytd, SUM(to_number(NULL)) p_or_ytd';
l_select0 :=
' fact, fii_time_day time ' ||
' WHERE 1 = 1 '||
' AND fact.ent_year_id = time.ent_year_id '||
' AND time.report_date IN ( &BIS_CURRENT_ASOF_DATE , &BIS_PREVIOUS_ASOF_DATE ) '||
l_where_clause;
' SELECT '||
' fact.rg_id ,'||
' SUM( DECODE(time.report_date, &BIS_CURRENT_ASOF_DATE , DECODE(fact.rg_id, &ORGANIZATION+JTF_ORG_SALES_GROUP, b_r_amt_n_'||l_cur_suffix||', b_r_amt_t_'||l_cur_suffix||'))) c_br_itd,'||
' to_number(NULL) c_or_itd,'||
' SUM( DECODE(time.report_date, &BIS_PREVIOUS_ASOF_DATE , DECODE(fact.rg_id, &ORGANIZATION+JTF_ORG_SALES_GROUP, b_r_amt_n_'||l_cur_suffix||', b_r_amt_t_'||l_cur_suffix||'))) p_br_itd, '||
' to_number(NULL) p_or_itd '||
' FROM '|| l_blg_mv || l_select0 ||
' GROUP BY fact.rg_id ';
' SELECT '||
' fact.rg_id ,'||
' to_number(NULL) c_br_itd,'||
' SUM( DECODE(time.report_date, &BIS_CURRENT_ASOF_DATE , DECODE(fact.rg_id, &ORGANIZATION+JTF_ORG_SALES_GROUP, o_r_amt_n_'||l_cur_suffix||', o_r_amt_t_'||l_cur_suffix||'))) c_or_itd,'||
' to_number(NULL) p_br_itd, '||
' SUM( DECODE(time.report_date, &BIS_PREVIOUS_ASOF_DATE , DECODE(fact.rg_id, &ORGANIZATION+JTF_ORG_SALES_GROUP, o_r_amt_n_'||l_cur_suffix||', o_r_amt_t_'||l_cur_suffix||'))) p_or_itd '||
' FROM '|| l_opn_mv || l_select0 ||
' GROUP BY fact.rg_id ';
'SELECT '||
'fact.'||l_view_by_col||','||
'SUM( DECODE(time.report_date, &BIS_CURRENT_ASOF_DATE , b_r_amt_'||l_cur_suffix||')) c_br_itd,'||
' to_number(NULL) c_or_itd,'||
'SUM( DECODE(time.report_date, &BIS_PREVIOUS_ASOF_DATE , b_r_amt_'||l_cur_suffix||')) p_br_itd, '||
' to_number(NULL) p_or_itd '||
' FROM '|| l_blg_mv || l_select0 ||
' GROUP BY fact.'||l_view_by_col;
'SELECT fact.'||l_view_by_col||','||
' to_number(NULL) c_br_itd, '||
'SUM( DECODE(time.report_date, &BIS_CURRENT_ASOF_DATE , o_r_amt_'||l_cur_suffix||')) c_or_itd,'||
' to_number(NULL) p_br_itd, '||
'SUM( DECODE(time.report_date, &BIS_PREVIOUS_ASOF_DATE , o_r_amt_'||l_cur_suffix||')) p_or_itd '||
' FROM '|| l_opn_mv || l_select0 ||
' GROUP BY fact.'||l_view_by_col;
l_query := oki_dbi_util_pvt.two_way_join (l_select,
'/* BLG ITD */'||l_blg_sql || '/* END BLG ITD */',
'/* OPN ITD */'||l_opn_sql || '/* END OPN ITD */',
l_join_column1,
l_join_column2);
l_select VARCHAR2(32767);
l_select := 'SUM(c_br_itd) c_br_itd, SUM(c_or_itd) c_or_itd, SUM(p_br_itd) p_br_itd, SUM(p_or_itd) p_or_itd ,SUM(c_br_ytd) c_br_ytd, SUM(c_or_ytd) c_or_ytd, SUM(p_br_ytd) p_br_ytd, SUM(p_or_ytd) p_or_ytd ';
l_query := oki_dbi_util_pvt.two_way_join (l_select,
p_blgopn_itd_sql,
p_blgopn_ytd_sql,
l_view_by_col,
l_view_by_col);
return 'SELECT '||l_view_by_col||','||'
NVL2(COALESCE(c_br_itd,c_br_ytd),NVL(c_br_itd,0)+NVL(c_br_ytd,0),NULL) c_br ,
NVL2(COALESCE(c_or_itd,c_or_ytd),NVL(c_or_itd,0)+NVL(c_or_ytd,0),NULL) c_or ,
NVL2(COALESCE(p_br_itd,p_br_ytd),NVL(p_br_itd,0)+NVL(p_br_ytd,0),NULL) p_br ,
NVL2(COALESCE(p_or_itd,p_or_ytd),NVL(p_or_itd,0)+NVL(p_or_ytd,0),NULL) p_or ,
SUM(NVL2(COALESCE(c_br_itd,c_br_ytd),NVL(c_br_itd,0)+NVL(c_br_ytd,0),NULL) ) over() c_br_tot ,
SUM(NVL2(COALESCE(c_or_itd,c_or_ytd),NVL(c_or_itd,0)+NVL(c_or_ytd,0),NULL) ) over()c_or_tot ,
SUM(NVL2(COALESCE(p_br_itd,p_br_ytd),NVL(p_br_itd,0)+NVL(p_br_ytd,0),NULL) ) over()p_br_tot ,
SUM(NVL2(COALESCE(p_or_itd,p_or_ytd),NVL(p_or_itd,0)+NVL(p_or_ytd,0),NULL) ) over()p_or_tot
FROM ( '|| l_query ||')';
'SELECT
oki_attribute_1,
cust.value oki_attribute_2,
DECODE(fact.resource_id,-1,&UNASSIGNED,rsex.resource_name) oki_attribute_3,
OKI_DATE_1,
OKI_DATE_2,
oki_measure_1,
oki_measure_3,
OKI_MEASURE_4,
oki_measure_11,
oki_measure_13,
OKI_MEASURE_14,
fact.chr_id OKI_ATTRIBUTE_5
FROM (SELECT *
FROM (
SELECT
rank() over (&ORDER_BY_CLAUSE nulls last) - 1 rnk ,
customer_party_id,
resource_id,
oki_measure_1,
oki_measure_3,
oki_measure_11,
oki_measure_13,
oki_date_1,
oki_date_2,
oki_attribute_1,
oki_measure_4,
oki_measure_14,
chr_id
FROM (SELECT fact.*
, to_char(k.start_date) OKI_DATE_1
, to_char(k.expected_close_date) OKI_DATE_2
, k.COMPLETE_k_number oki_attribute_1
, NVL(k.price_nego_'|| p_cur_suffix || ',0) OKI_MEASURE_4
, NVL(SUM(k.price_nego_' ||p_cur_suffix ||') over (),0) OKI_MEASURE_14
FROM (SELECT *
FROM (
SELECT
oset5.chr_id,
oset5.customer_party_id ,
oset5.resource_id ,
nvl(oset5.full_value,0) OKI_MEASURE_1,
nvl(oset5.forecast_value,0) OKI_MEASURE_3,
nvl(oset5.full_value_total,0) OKI_MEASURE_11,
nvl(oset5.forecast_value_total,0) OKI_MEASURE_13
FROM
(SELECT
fact.chr_id,
fact.customer_party_id,
fact.resource_id ';
l_viewby_select VARCHAR2(10000);
l_url_select VARCHAR2(20000);
l_viewby_select := oki_dbi_util_pvt.get_viewby_select_clause(l_view_by, 'SRM', '6.0');
l_viewby_select := l_viewby_select ||
', OKI_DYNAMIC_URL_1 ,'|| l_prodcat_url || ' ,oki_measure_1 ,oki_measure_2 ,oki_measure_3 ,oki_measure_4
,oki_measure_5 ,oki_measure_6 ,oki_measure_7 ,oki_measure_8 ,oki_measure_9 ,oki_measure_11 ,oki_measure_12
,oki_measure_13 ,oki_measure_14 ,oki_measure_15 ,oki_measure_16 ,oki_measure_17 ,oki_measure_18 ,oki_measure_19
,oki_measure_6 OKI_ATTRIBUTE_3 ,oki_measure_9 OKI_ATTRIBUTE_4
FROM (
SELECT rank() over (&ORDER_BY_CLAUSE nulls last , '||l_view_by_col||') - 1 rnk ,'||l_view_by_col||'
,OKI_DYNAMIC_URL_1 ,oki_measure_1 ,oki_measure_2 ,oki_measure_3 ,oki_measure_4 ,oki_measure_5 ,oki_measure_6
,oki_measure_7 ,oki_measure_8 ,oki_measure_9 ,oki_measure_11 ,oki_measure_12 ,oki_measure_13 ,oki_measure_14
,oki_measure_15 ,oki_measure_16 ,oki_measure_17 ,oki_measure_18 ,oki_measure_19
FROM ( ';
l_url_select :=
'SELECT decode(resource_id,-999,''pFunctionName=OKI_DBI_SRM_PDUE_DRPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP'','''') OKI_DYNAMIC_URL_1 ';
l_url_select :=
'SELECT NULL OKI_DYNAMIC_URL_1 ';
l_query := l_viewby_select || l_url_select || ' ,'||
l_view_by_col || ',' ||
'oset10.pastdue_val OKI_MEASURE_1,
oset10.open_val OKI_MEASURE_2,
oset10.val_rate OKI_MEASURE_3,
oset10.pastdue_lcount OKI_MEASURE_4,
oset10.open_lcount OKI_MEASURE_5,
oset10.lcount_rate OKI_MEASURE_6,
oset10.pastdue_hcount OKI_MEASURE_7,
oset10.open_hcount OKI_MEASURE_8,
oset10.hcount_rate OKI_MEASURE_9,
oset10.pastdue_val_tot OKI_MEASURE_11,
oset10.open_val_tot OKI_MEASURE_12,
oset10.val_rate_tot OKI_MEASURE_13,
oset10.pastdue_lcount_tot OKI_MEASURE_14,
oset10.open_lcount_tot OKI_MEASURE_15,
oset10.lcount_rate_tot OKI_MEASURE_16,
oset10.pastdue_hcount_tot OKI_MEASURE_17,
oset10.open_hcount_tot OKI_MEASURE_18,
oset10.hcount_rate_tot OKI_MEASURE_19
FROM
(
SELECT '|| l_view_by_col || ',
nvl(oset05.pastdue_val,0) pastdue_val,
nvl(oset05.open_val,0) open_val,
oset05.pastdue_val /decode( open_val,0,NULL,open_val)*100 val_rate ,
nvl(oset05.pastdue_lcount,0) pastdue_lcount,
nvl(oset05.open_lcount,0) open_lcount,
oset05.pastdue_lcount/decode( open_lcount,0,NULL,open_lcount)*100 lcount_rate ,
nvl(oset05.pastdue_hcount,0) pastdue_hcount,
nvl(oset05.open_hcount,0) open_hcount ,
oset05.pastdue_hcount/decode( open_hcount,0,NULL,open_hcount)*100 hcount_rate ,
nvl(oset05.pastdue_val_tot,0) pastdue_val_tot,
nvl(oset05.open_val_tot,0) open_val_tot,
oset05.pastdue_val_tot/decode(oset05.open_val_tot,0,NULL,oset05.open_val_tot)*100 val_rate_tot,
nvl(oset05.pastdue_lcount_tot,0) pastdue_lcount_tot,
nvl(oset05.open_lcount_tot,0) open_lcount_tot,
oset05.pastdue_lcount_tot /decode( oset05.open_lcount_tot,0,NULL,oset05.open_lcount_tot)*100 lcount_rate_tot,
nvl(oset05.pastdue_hcount_tot,0) pastdue_hcount_tot,
nvl(oset05.open_hcount_tot,0) open_hcount_tot,
oset05.pastdue_hcount_tot /decode( oset05.open_hcount_tot,0,NULL,oset05.open_hcount_tot)*100 hcount_rate_tot
FROM
( '||
get_pdue_open_2way_sql(l_pastdue_where,
l_open_where,
l_viewby_col_special,
l_view_by_col,
l_cur_suffix )
|| l_VIEWBY_RANK_ORDER;
l_select varchar2(32767);
l_select :=' SUM(pastdue_val) pastdue_val,
SUM(pastdue_hcount) pastdue_hcount,
SUM(pastdue_lcount) pastdue_lcount,
SUM(pastdue_val_tot) pastdue_val_tot,
SUM(pastdue_hcount_tot) pastdue_hcount_tot,
SUM(pastdue_lcount_tot) pastdue_lcount_tot,
SUM(open_val) open_val,
SUM(open_hcount) open_hcount,
SUM(open_lcount) open_lcount,
SUM(open_val_tot) open_val_tot,
SUM(open_hcount_tot) open_hcount_tot,
SUM(open_lcount_tot) open_lcount_tot
';
' SELECT '|| l_viewby_col_special ||l_view_by_col ||',
NVL(SUM(fact.price_nego_'||l_cur_suffix||'),0) pastdue_val,
NVL(COUNT(distinct(fact.chr_id)),0) pastdue_hcount,
NVL(COUNT(distinct(fact.cle_id)),0) pastdue_lcount,
NVL(SUM(SUM(fact.price_nego_'||l_cur_suffix||')) over (),0)pastdue_val_tot,
NVL(SUM(COUNT(distinct(fact.chr_id))) over (),0)pastdue_hcount_tot,
NVL(SUM(COUNT(distinct(fact.cle_id))) over (),0)pastdue_lcount_tot,
to_number(NULL) open_val,
to_number(NULL) open_hcount,
to_number(NULL) open_lcount,
to_number(NULL) open_val_tot,
to_number(NULL) open_hcount_tot,
to_number(NULL) open_lcount_tot '||l_pastdue_where;
' SELECT '|| l_viewby_col_special ||l_view_by_col ||',
to_number(NULL) pastdue_val,
to_number(NULL) pastdue_hcount,
to_number(NULL) pastdue_lcount,
to_number(NULL) pastdue_val_tot,
to_number(NULL) pastdue_hcount_tot,
to_number(NULL) pastdue_lcount_tot,
NVL(SUM(fact.price_nego_'||l_cur_suffix||'),0) Open_val,
NVL(COUNT(distinct(fact.chr_id)),0) Open_hcount,
NVL(COUNT(distinct(fact.cle_id)),0) Open_lcount,
NVL(SUM(SUM(fact.price_nego_'||l_cur_suffix||')) over (),0) Open_val_tot,
NVL(SUM(COUNT(distinct(fact.chr_id))) over (),0) Open_hcount_tot,
NVL(SUM(COUNT(distinct(fact.cle_id))) over (),0) Open_lcount_tot '||
l_open_where;
l_query := oki_dbi_util_pvt.two_way_join (l_select, l_query1, l_query2,l_join_column1, l_join_column2);
l_template_sql := '(select
cn vb
,csd
,sum(c_Br1)c_Br1
,sum(p_Br1)p_Br1
,sum(c_Br3)c_Br3
,sum(p_Br3)p_Br3
,sum(c_Or3)c_Or3
,sum(p_Or3)p_Or3
,sum(c_Br2)c_Br2
,sum(p_Br2)p_Br2
,sum(c_Or2)c_Or2
,sum(p_Or2)p_Or2
,sum(c_Or1)c_Or1
,sum(p_Or1)p_Or1
from(
WITH n AS(select /*+ NO_MERGE */n.time_id ntid,n.record_type_id,n.period_type_id,n.report_date nrd,cal.start_date nsd,cal.end_date
from '|| poa_dbi_util_pkg.get_calendar_table (l_xtd1)||' cal,fii_time_rpt_struct_v n
where cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE and n.report_date in(least(cal.end_date,&BIS_CURRENT_ASOF_DATE),&BIS_PREVIOUS_ASOF_DATE)
and n.report_date between cal.start_date and cal.end_date and bitand(n.record_type_id,&BIS_NESTED_PATTERN)=n.record_type_id)
(SELECT
cal.name cn,cal.start_date csd,c_Br1,p_Br1,0 c_Br3,0 p_Br3,0 c_Or3,0 p_Or3,0 c_Br2,0 p_Br2,0 c_Or2,0 p_Or2,0 c_Or1,0 p_Or1
from(select n.start_date isd
,sum(case when(n.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE and nrd=LEAST(n.end_date,&BIS_CURRENT_ASOF_DATE))then Br1 end)c_Br1
,lag(sum(case when(n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE and nrd=LEAST(n.end_date,&BIS_PREVIOUS_ASOF_DATE))then Br1 end),&LAG)over(order by n.start_date)p_Br1
from(select nsd,nrd,sum(b_r_amt_g)Br1 FROM '|| l_mv1 ||' fact,n
where fact.time_id=ntid '
|| l_where_clause1 ||
' GROUP by nsd, nrd)i,'|| poa_dbi_util_pkg.get_calendar_table (l_xtd1)|| ' n where nsd(+)=n.start_date and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
group by n.start_date)iset,'|| poa_dbi_util_pkg.get_calendar_table (l_xtd1)|| ' cal where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and cal.start_date=iset.isd(+)
)UNION ALL
(select
cal.name cn,cal.start_date csd,0 c_Br1,0 p_Br1,c_Br3,p_Br3,c_Or3,p_Or3,0 c_Br2,0 p_Br2,0 c_Or2,0 p_Or2,0 c_Or1,0 p_Or1
from
(select n.start_date isd
,sum(case when(n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE and nrd=LEAST(n.end_date,&BIS_CURRENT_ASOF_DATE))then Br3 end)c_Br3
,lag(sum(case when(n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE and nrd=LEAST(n.end_date,&BIS_PREVIOUS_ASOF_DATE))then Br3 end),&LAG)over(order by n.start_date)p_Br3
,sum(case when(n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE and nrd=LEAST(n.end_date,&BIS_CURRENT_ASOF_DATE))then Or3 end) c_Or3
,lag(sum(case when(n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE and nrd=LEAST (n.end_date,&BIS_PREVIOUS_ASOF_DATE))then Or3 end),&LAG)over(order by n.start_date)p_Or3
from(select nsd,nrd,sum(b_rgr_amt_g)Br3,sum(o_rgr_amt_g)Or3 from '||l_mv2|| ' fact,n
where fact.time_id=ntid '
|| l_where_clause2 ||
' GROUP by nsd,nrd)i,'|| poa_dbi_util_pkg.get_calendar_table (l_xtd2)|| ' n where nsd(+)=n.start_date and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
group by n.start_date)iset,'|| poa_dbi_util_pkg.get_calendar_table (l_xtd2)|| ' cal
where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and cal.start_date=iset.isd(+)
)UNION ALL
(select
cal.name cn,cal.start_date csd,0 c_Br1,0 p_Br1,0 c_Br3,0 p_Br3,0 c_Or3,0 p_Or3,c_Br2,p_Br2,c_Or2,p_Or2,0 c_Or1,0 p_Or1 from
(select n.start_date isd
,sum(case when(n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE and nrd=LEAST(n.end_date,&BIS_CURRENT_ASOF_DATE))then Br2 end)c_Br2
,lag(sum(case when(n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE and nrd=LEAST(n.end_date,&BIS_PREVIOUS_ASOF_DATE))then Br2 end),&LAG)over(order by n.start_date)p_Br2
,sum(case when(n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE and nrd=LEAST(n.end_date,&BIS_CURRENT_ASOF_DATE))then Or2 end)c_Or2
,lag(sum(case when(n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE and nrd=LEAST(n.end_date,&BIS_PREVIOUS_ASOF_DATE))then Or2 end),&LAG)over(order by n.start_date)p_Or2
from(select nsd,nrd,sum(b_rcr_amt_g)Br2,sum(o_rcr_amt_g)Or2 from ' || l_mv3 ||' fact,n
where fact.time_id=ntid '
||l_where_clause3||
' GROUP by nsd,nrd)i,'|| poa_dbi_util_pkg.get_calendar_table (l_xtd3)|| ' n where nsd(+)=n.start_date and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
group by n.start_date)iset,' || poa_dbi_util_pkg.get_calendar_table (l_xtd3)|| ' cal
where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and cal.start_date=iset.isd(+)
)UNION ALL
(select
cal.name cn,cal.start_date csd,0 c_Br1,0 p_Br1,0 c_Br3,0 p_Br3,0 c_Or3,0 p_Or3,0 c_Br2,0 p_Br2,0 c_Or2,0 p_Or2,c_Or1,p_Or1
from
(select n.start_date isd
,sum(case when(n.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE and nrd=LEAST(n.end_date,&BIS_CURRENT_ASOF_DATE))then Or1 end)c_Or1
,lag(sum(case when(n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE and nrd=LEAST(n.end_date,&BIS_PREVIOUS_ASOF_DATE))then Or1 end),&LAG)over(order by n.start_date)p_Or1
from(select nsd,nrd,sum(o_r_amt_g)Or1 from ' ||l_mv4||' fact,n
where fact.time_id=ntid '
|| l_where_clause4 ||
' GROUP by nsd,nrd)i,' ||poa_dbi_util_pkg.get_calendar_table (l_xtd4)|| ' n where nsd(+)=n.start_date and n.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
group by n.start_date)iset,'|| poa_dbi_util_pkg.get_calendar_table (l_xtd4)|| ' cal
where cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
and cal.start_date=iset.isd(+)
))group by cn,csd
)u
order by csd';
'SELECT
VIEWBY,
p_rate OKI_MEASURE_1,
c_rate OKI_MEASURE_2,
'||OKI_DBI_UTIL_PVT.change_clause('c_rate','p_rate','P') || ' OKI_MEASURE_3,
c_Or OKI_MEASURE_4,
c_Br OKI_MEASURE_5
FROM(
SELECT
csd s,
vb VIEWBY,
c_Br,
c_Or,
c_Br_iy,
c_Br_i,
c_Br_y,
c_Br_x_cum,
c_Br_x,
c_BrS,
c_BrC,
c_BrG,
c_Or_iy,
c_Or_i,
c_Or_y,
c_Or_x,
c_Or_x_cum,
c_Or_x,
c_OrS,
c_OrC,
c_OrG, '||
POA_DBI_UTIL_PKG.rate_clause('c_Br','c_Or') || ' c_rate,'||
POA_DBI_UTIL_PKG.rate_clause('p_Br','p_Or') || ' p_rate '||
' FROM(
SELECT
csd,
vb,
nvl(b.c_br,0)c_Br_iy,
nvl(b.c_br_i,0)c_br_i,
nvl(b.c_br_y,0)c_br_y,
nvl(x.c_br,0)c_Br_x_cum,
nvl(x.c_Br_x,0)c_Br_x,
nvl(x.c_BrS,0)c_BrS,
nvl(x.c_BrC,0)c_BrC,
nvl(x.c_BrG,0)c_BrG,
nvl(o.c_or,0)c_Or_iy,
nvl(o.c_or_i,0)c_or_i,
nvl(o.c_or_y,0)c_or_y,
nvl(x.c_or,0)c_Or_x_cum,
nvl(x.c_or_x,0)c_or_x,
nvl(x.c_OrS,0)c_OrS,
nvl(x.c_OrC,0)c_OrC,
nvl(x.c_OrG,0)c_OrG,
nvl(b.c_br,0)+nvl(x.c_br,0)c_br,
NVL(b.p_br,0)+NVL(x.p_br,0)p_br,
NVL(o.c_or,0)+NVL(x.c_or,0)c_or,
NVL(o.p_or,0)+NVL(x.p_or,0)p_or ';
' SELECT
csd,
vb,
c_br c_br_x,
c_or c_or_x,
c_BrS,
c_BrC,
c_BrG,
c_OrS,
c_OrC,
c_OrG,
SUM(c_br)OVER(ORDER BY csd ROWS UNBOUNDED PRECEDING)c_br,
SUM(p_br)OVER(ORDER BY csd ROWS UNBOUNDED PRECEDING)p_br,
SUM(c_or)OVER(ORDER BY csd ROWS UNBOUNDED PRECEDING)c_or,
SUM(p_or)OVER(ORDER BY csd ROWS UNBOUNDED PRECEDING)p_or
FROM (
SELECT
vb,
csd,
u.c_Br1 c_BrS,
u.c_Br2 c_BrC,
u.c_Br3 c_BrG,
u.c_Or1 c_OrS,
u.c_Or2 c_OrC,
u.c_Or3 c_OrG,
NVL(u.c_Br1,0)-NVL(u.c_Br2,0)-NVL(u.c_Br3,0)c_br,
NVL(u.p_Br1,0)-NVL(u.p_Br2,0)-NVL(u.p_Br3,0)p_br,
NVL(u.c_Or1,0)-NVL(u.c_Or2,0)-NVL(u.c_Or3,0)c_Or,
NVL(u.p_Or1,0)-NVL(u.p_Or2,0)-NVL(u.p_Or3,0)p_Or ';
' SELECT NVL2(COALESCE(p_b,p_b_s,p_b_c),'||
'(NVL(p_b,0)-NVL(p_b_s,0)-NVL(p_b_c,0)),NULL)p_b_y,'||
'NVL2( coalesce(c_b,c_b_s,c_b_c),'||
'(NVL(c_b,0)-NVL(c_b_s,0)-NVL(c_b_c,0)),NULL)c_b_y'||
' FROM(select
sum(p_b)p_b,
sum(c_b)c_b,
sum(p_b_s)p_b_s,
sum(c_b_s)c_b_s,
sum(p_b_c)p_b_c,
sum(c_b_c)c_b_c
from (with cal as(SELECT /*+ NO_MERGE */
cal.time_id ctid,
cal.record_type_id,
cal.period_type_id,
cal.report_date crd
FROM fii_time_rpt_struct_v cal
WHERE cal.report_date IN(&BIS_PREVIOUS_REPORT_START_DATE-1,&BIS_CURRENT_REPORT_START_DATE-1)
AND bitAND(cal.record_type_id,119)=cal.record_type_id)(SELECT '||
'SUM(decode(crd,&BIS_PREVIOUS_REPORT_START_DATE-1,b_r_amt_'||l_cur_suffix||'))p_b,'||
'SUM(decode(crd,&BIS_CURRENT_REPORT_START_DATE-1,b_r_amt_'||l_cur_suffix||'))c_b, 0 p_b_s,0 c_b_s,0 p_b_c,0 c_b_c '||
'FROM '||l_mv1||' fact,cal WHERE fact.time_id=ctid '||
l_where_clause1 ||')
UNION ALL
(SELECT 0 p_b,0 c_b,'||
'SUM(decode(crd,&BIS_PREVIOUS_REPORT_START_DATE-1,b_rgr_amt_'||l_cur_suffix||'))p_b_s,'||
'SUM(decode(crd,&BIS_CURRENT_REPORT_START_DATE-1,b_rgr_amt_'||l_cur_suffix||'))c_b_s,0 p_b_c,0 c_b_c '||
'FROM '||l_mv2||' fact,cal WHERE fact.time_id=ctid '||
l_where_clause2 ||')
UNION ALL
(SELECT 0 p_b,0 c_b,0 p_b_s,0 c_b_s,'||
'SUM(decode(crd,&BIS_PREVIOUS_REPORT_START_DATE-1,b_rcr_amt_'||l_cur_suffix||'))p_b_c,'||
'SUM(decode(crd,&BIS_CURRENT_REPORT_START_DATE-1,b_rcr_amt_'||l_cur_suffix||'))c_b_c '||
' FROM '||l_mv3||' fact,cal WHERE fact.time_id=ctid '||
l_where_clause3 ||')))';
' SELECT NVL2(COALESCE(p_o,p_o_s,p_o_c),'||
'(NVL(p_o,0)-NVL(p_o_s,0)-NVL(p_o_c,0)),NULL)p_o_y,'||
'NVL2( coalesce(c_o,c_o_s,c_o_c),'||
'(NVL(c_o,0)-NVL(c_o_s,0)-NVL(c_o_c,0)),NULL)c_o_y'||
' FROM(select
sum(p_o)p_o,
sum(c_o)c_o,
sum(p_o_s)p_o_s,
sum(c_o_s)c_o_s,
sum(p_o_c)p_o_c,
sum(c_o_c)c_o_c
from(with cal as(SELECT /*+ NO_MERGE */
cal.time_id ctid,
cal.record_type_id,
cal.period_type_id,
cal.report_date crd
FROM fii_time_rpt_struct_v cal
WHERE cal.report_date IN(&BIS_PREVIOUS_REPORT_START_DATE-1,&BIS_CURRENT_REPORT_START_DATE-1)
AND bitAND(cal.record_type_id,119)=cal.record_type_id)(SELECT '||
'SUM(decode(crd,&BIS_PREVIOUS_REPORT_START_DATE-1,o_r_amt_'||l_cur_suffix||'))p_o,'||
'SUM(decode(crd,&BIS_CURRENT_REPORT_START_DATE-1,o_r_amt_'||l_cur_suffix||'))c_o,0 p_o_s,0 c_o_s,0 p_o_c,0 c_o_c '||
'FROM '||l_mv4||' fact,cal WHERE fact.time_id=ctid '||
l_where_clause4 ||')
UNION ALL
(SELECT 0 p_o,0 c_o,'||
'SUM(decode(crd,&BIS_PREVIOUS_REPORT_START_DATE-1,o_rgr_amt_'||l_cur_suffix||'))p_o_s,'||
'SUM(decode(crd,&BIS_CURRENT_REPORT_START_DATE-1,o_rgr_amt_'||l_cur_suffix||'))c_o_s,0 p_o_c,0 c_o_c '||
'FROM '||l_mv2||' fact,cal WHERE fact.time_id=ctid '||
l_where_clause2 ||')
UNION ALL
(SELECT 0 p_o,0 c_o,0 p_o_s,0 c_o_s,'||
'SUM(decode(crd,&BIS_PREVIOUS_REPORT_START_DATE-1,o_rcr_amt_'||l_cur_suffix||'))p_o_c,'||
'SUM(decode(crd,&BIS_CURRENT_REPORT_START_DATE-1,o_rcr_amt_'||l_cur_suffix||'))c_o_c '||
' FROM '||l_mv3||' fact,cal WHERE fact.time_id=ctid '||
l_where_clause3 ||')))';
' SELECT c_b_i c_br_i,
c_b_y c_br_y,
NVL2(COALESCE(p_b_i,p_b_y),nvl(p_b_i,0)+nvl(p_b_y,0),NULL)p_br,'||
'NVL2(COALESCE(c_b_i,c_b_y),nvl(c_b_i,0)+nvl(c_b_y,0),NULL) c_br'||
' FROM('|| l_blg_itd ||')i,'||
'('|| l_blg_ytd ||')y ';
' SELECT c_o_i c_or_i,
c_o_y c_or_y,
NVL2(COALESCE(p_o_i,p_o_y),nvl(p_o_i,0)+nvl(p_o_y,0),NULL)p_or,'||
' NVL2(COALESCE(c_o_i,c_o_y),nvl(c_o_i,0)+nvl(c_o_y,0),NULL)c_or '||
' FROM('|| l_opn_itd ||')i , '||
'('|| l_opn_ytd ||')y ';
l_select0 varchar2(1000);
l_select varchar2(32767);
'SELECT SUM(decode(cal.report_date,&BIS_PREVIOUS_REPORT_START_DATE-1,b_r_amt_'||l_cur_suffix||'))p_b_i,'||
'SUM(decode(cal.report_date,&BIS_CURRENT_REPORT_START_DATE-1,b_r_amt_'||l_cur_suffix||'))c_b_i '||
'FROM '||l_mv||' fact,fii_time_day cal WHERE fact.ent_year_id=cal.ent_year_id'||
' AND cal.report_date IN(&BIS_PREVIOUS_REPORT_START_DATE-1,&BIS_CURRENT_REPORT_START_DATE-1)'||
l_where_clause;
l_select0 varchar2(1000);
l_select varchar2(32767);
'SELECT SUM(decode(cal.report_date,&BIS_PREVIOUS_REPORT_START_DATE-1,o_r_amt_'||l_cur_suffix||'))p_o_i,'||
'SUM(decode(cal.report_date,&BIS_CURRENT_REPORT_START_DATE-1,o_r_amt_'||l_cur_suffix||'))c_o_i '||
'FROM '||l_mv||' fact,fii_time_day cal WHERE fact.ent_year_id=cal.ent_year_id'||
' AND cal.report_date IN (&BIS_PREVIOUS_REPORT_START_DATE-1,&BIS_CURRENT_REPORT_START_DATE-1)'||
l_where_clause;
FUNCTION get_PDueVal_cust_sel_clause() returns the top most select
statment of SQL query by adding p the ITD measures and YTD Measures.
/*******************************************************************************/
/*
FUNCTION get_pdueVal_cust_sel_clause
RETURN VARCHAR2
AS
l_query VARCHAR2 (32767);
FUNCTION get_YTD_by_cust_sel_clause() returns the SQL select portion of the query for
YTD measures.
/*******************************************************************************/
/*
FUNCTION get_YTD_by_cust_sel_clause
RETURN VARCHAR2
AS
l_query VARCHAR2(2000);
FUNCTION get_ITD_by_cust_sel_clause() returns the select clause containing all
the ITD measures
/******************************************************************************/
/*
FUNCTION get_ITD_by_cust_sel_clause
RETURN VARCHAR2
AS
l_query VARCHAR2(2000);