[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_dates_subquery := ' (SELECT fii.start_date START_DATE,
''C'' PERIOD,
least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
UNION ALL
SELECT p2.start_date START_DATE,
''P'' PERIOD,
p1.report_date REPORT_DATE
FROM (SELECT least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE,
rownum ID
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
AND &BIS_PREVIOUS_ASOF_DATE
ORDER BY fii.start_date DESC) p1,
(SELECT fii.start_date START_DATE,
rownum ID
FROM '||l_period_type||' fii
WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
ORDER BY fii.start_date DESC) p2
WHERE p1.id(+) = p2.id)';
SELECT dim_view.name VIEWBY,
nvl(c.prev_book,0) ISC_MEASURE_1,
nvL(c.curr_book,0) ISC_MEASURE_2,
(c.curr_book - c.prev_book)
/ decode(c.prev_book, 0, null, abs(c.prev_book))
* 100 ISC_MEASURE_3,
nvl(c.prev_serv,0) ISC_MEASURE_4,
nvl(c.curr_serv,0) ISC_MEASURE_5,
(c.curr_serv - c.prev_serv)
/ decode(c.prev_serv, 0, null, abs(c.prev_serv))
* 100 ISC_MEASURE_6,
nvl(c.prev_active,0) ISC_MEASURE_7,
nvl(c.curr_active,0) ISC_MEASURE_8,
(c.curr_active - c.prev_active)
/ decode(c.prev_active, 0, null, abs(c.prev_active))
* 100 ISC_MEASURE_9
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 dates.start_date DIMENSION_ID,
0 CURR_BOOK,
0 PREV_BOOK,
0 CURR_SERV,
0 PREV_SERV,
decode(dates.period, ''C'',
nvl(fact.activated'||sfx||',0)-nvl(fact.expired'||sfx||',0), 0) CURR_ACTIVE,
decode(dates.period, ''P'',
nvl(fact.activated'||sfx||',0)-nvl(fact.expired'||sfx||',0), 0) PREV_ACTIVE
FROM '||l_dates_subquery||' dates,
'||l_mv1 ||' fact,
FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
WHERE cal.report_date = dates.report_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 dates.start_date DIMENSION_ID,
0 CURR_BOOK,
0 PREV_BOOK,
0 CURR_SERV,
0 PREV_SERV,
decode(dates.period, ''C'',
nvl(fact.active'||sfx||',0), 0) CURR_ACTIVE,
decode(dates.period, ''P'',
nvl(fact.active'||sfx||',0), 0) PREV_ACTIVE
FROM '||l_dates_subquery||' dates,
'||l_mv2 ||' fact,
FII_TIME_DAY cal'||l_prod_cat_from||'
WHERE cal.report_date = dates.report_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 dates.start_date DIMENSION_ID,
decode(dates.period, ''C'',
nvl(fact.booked_amt'||sfx||',0)-nvl(fact.returned_amt'||sfx||',0), 0) CURR_BOOK,
decode(dates.period, ''P'',
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_dates_subquery||' dates,
'||l_mv3 ||' fact,
FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
WHERE cal.report_date = dates.report_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 dates.start_date DIMENSION_ID,
0 CURR_BOOK,
0 PREV_BOOK,
decode(dates.period, ''C'',
nvl(fact.first_opened_count,0), 0) CURR_SERV,
decode(dates.period, ''P'',
nvl(fact.first_opened_count,0), 0) PREV_SERV,
0 CURR_ACTIVE,
0 PREV_ACTIVE
FROM '||l_dates_subquery||' dates,
'||l_mv4 ||' fact,
FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
WHERE cal.report_date = dates.report_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,
'||l_period_type||' dim_view
WHERE dim_view.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
AND &BIS_CURRENT_ASOF_DATE
AND dim_view.start_date = c.dimension_id(+)
ORDER BY dim_view.start_date';