[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /*+ LEADING(a) INDEX(dim_view) */ dim_view.value VIEWBY,
dim_view.id VIEWBYID,
'||l_drill_prod_cat||' ISC_ATTRIBUTE_1,
'||l_drill_leaf_cat||' ISC_ATTRIBUTE_2,
'||l_drill_active||' ISC_ATTRIBUTE_3,
'||l_measures||'
FROM
(SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, dimension_id)) - 1 RNK,
dimension_id,
'||l_measures||'
FROM (SELECT c.dimension_id DIMENSION_ID,
c.curr_book ISC_MEASURE_1,
(c.curr_book - c.prev_book)
/ decode(c.prev_book, 0, null, abs(c.prev_book))
* 100 ISC_MEASURE_2,
c.curr_serv ISC_MEASURE_3,
(c.curr_serv - c.prev_serv)
/ decode(c.prev_serv, 0, null, abs(c.prev_serv))
* 100 ISC_MEASURE_4,
c.curr_active ISC_MEASURE_5,
(c.curr_active - c.prev_active)
/ decode(c.prev_active, 0, null, abs(c.prev_active))
* 100 ISC_MEASURE_6,
c.curr_active
/ decode(sum(c.curr_active) over (), 0, null,
sum(c.curr_active) over ())
* 100 ISC_MEASURE_7,
c.curr_active
/ decode(sum(c.curr_active) over (), 0, null,
sum(c.curr_active) over ()) * 100
- c.prev_active
/ decode(sum(c.prev_active) over (), 0, null,
sum(c.prev_active) over ()) * 100 ISC_MEASURE_8,
0 ISC_MEASURE_9,
0 ISC_MEASURE_10,
sum(c.curr_book) over () ISC_MEASURE_11,
(sum(c.curr_book) over () - sum(c.prev_book) over ())
/ decode(sum(c.prev_book) over (), 0, null,
abs(sum(c.prev_book) over ()))
* 100 ISC_MEASURE_12,
sum(c.curr_serv) over () ISC_MEASURE_13,
(sum(c.curr_serv) over () - sum(c.prev_serv) over ())
/ decode(sum(c.prev_serv) over (), 0, null,
abs(sum(c.prev_serv) over ()))
* 100 ISC_MEASURE_14,
sum(c.curr_active) over () ISC_MEASURE_15,
(sum(c.curr_active) over () - sum(c.prev_active) over ())
/ decode(sum(c.prev_active) over (), 0, null,
abs(sum(c.prev_active) over ()))
* 100 ISC_MEASURE_16,
sum(c.curr_active) over ()
/ decode(sum(c.curr_active) over (), 0, null,
sum(c.curr_active) over ()) * 100 ISC_MEASURE_17,
0 ISC_MEASURE_19,
0 ISC_MEASURE_20,
c.prev_book ISC_MEASURE_21,
c.prev_serv ISC_MEASURE_22,
c.prev_active ISC_MEASURE_23,
c.curr_active ISC_MEASURE_24,
c.prev_active ISC_MEASURE_25,
c.curr_serv ISC_MEASURE_26,
c.prev_serv ISC_MEASURE_27,
sum(c.curr_active) over () ISC_MEASURE_28,
sum(c.prev_active) over () ISC_MEASURE_29,
sum(c.curr_serv) over () ISC_MEASURE_30,
sum(c.prev_serv) over () ISC_MEASURE_31
FROM (SELECT dimension_id DIMENSION_ID,
sum(curr_book) CURR_BOOK,
sum(prev_book) PREV_BOOK,
sum(curr_serv) CURR_SERV,
sum(prev_serv) PREV_SERV,
sum(curr_active) CURR_ACTIVE,
sum(prev_active) PREV_ACTIVE
FROM (SELECT '||l_dimension_id||' DIMENSION_ID,
0 CURR_BOOK,
0 PREV_BOOK,
0 CURR_SERV,
0 PREV_SERV,
decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
nvl(fact.activated'||sfx||',0)-nvl(fact.expired'||sfx||',0), 0) CURR_ACTIVE,
decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
nvl(fact.activated'||sfx||',0)-nvl(fact.expired'||sfx||',0), 0) PREV_ACTIVE
FROM '||l_mv1 ||' fact,
FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
WHERE cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
AND bitand(cal.record_type_id, 119) = cal.record_type_id
AND cal.time_id = fact.time_id
AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
AND fact.customer_flag = :ISC_CUST_FLAG '||l_prod_cat_where||l_leaf_cat_where||l_prod_where||l_cust_where||l_class_where||'
UNION ALL
SELECT '||l_dimension_id||' DIMENSION_ID,
0 CURR_BOOK,
0 PREV_BOOK,
0 CURR_SERV,
0 PREV_SERV,
decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
nvl(fact.active'||sfx||',0), 0) CURR_ACTIVE,
decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
nvl(fact.active'||sfx||',0), 0) PREV_ACTIVE
FROM '||l_mv2 ||' fact,
FII_TIME_DAY cal'||l_prod_cat_from||'
WHERE cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
AND cal.ent_year_id = fact.ent_year_id
AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
AND fact.customer_flag = :ISC_CUST_FLAG '||l_prod_cat_where||l_leaf_cat_where||l_prod_where||l_cust_where||l_class_where||'
UNION ALL
SELECT '||l_dimension_id||' DIMENSION_ID,
decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
nvl(fact.booked_amt'||sfx||',0)-nvl(fact.returned_amt'||sfx||',0), 0) CURR_BOOK,
decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
nvl(fact.booked_amt'||sfx||',0)-nvl(fact.returned_amt'||sfx||',0), 0) PREV_BOOK,
0 CURR_SERV,
0 PREV_SERV,
0 CURR_ACTIVE,
0 PREV_ACTIVE
FROM '||l_mv3 ||' fact,
FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
WHERE cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
AND cal.time_id = fact.time_id
AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
AND fact.customer_flag = :ISC_CUST_FLAG '||l_prod_cat_where||l_leaf_cat_where||l_prod_where||l_cust_where||l_class_where||'
UNION ALL
SELECT '||biv_column_name(l_dimension_id)||' DIMENSION_ID,
0 CURR_BOOK,
0 PREV_BOOK,
decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
nvl(fact.first_opened_count,0), 0) CURR_SERV,
decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
nvl(fact.first_opened_count,0), 0) PREV_SERV,
0 CURR_ACTIVE,
0 PREV_ACTIVE
FROM '||l_mv4 ||' fact,
FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
WHERE cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
AND cal.time_id = fact.time_id
AND cal.period_type_id = fact.period_type_id '
||l_biv_flag_where
||biv_column_name(l_prod_cat_where)
||biv_column_name(l_leaf_cat_where)
||l_prod_where
||l_cust_where||l_class_where||' )
GROUP BY dimension_id) c
WHERE c.curr_book <> 0
OR c.prev_book <> 0
OR c.curr_serv <> 0
OR c.prev_serv <> 0
OR c.curr_active <> 0
OR c.prev_active <> 0)) a,
'||l_dimension_view||' dim_view
WHERE a.dimension_id = dim_view.id
AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))'||l_dim_where_clause||'
ORDER BY rnk';