[Home] [Help]
MATERIALIZED VIEW: APPS.IBE_BI_CAT_TIME_MV
Source
SELECT /* Fix for bug # 5024093 */
1 umar,
(CASE
WHEN GROUPING (TIME.report_date_julian) = 0 THEN TIME.report_date_julian
WHEN GROUPING (TIME.week_id) = 0 THEN TIME.week_id
WHEN GROUPING (TIME.ent_period_id) = 0 THEN TIME.ent_period_id
WHEN GROUPING (TIME.ent_qtr_id) = 0 THEN TIME.ent_qtr_id
ELSE TIME.ent_year_id
END
) time_id,
(CASE
WHEN GROUPING (TIME.report_date_julian) = 0 THEN 1
WHEN GROUPING (TIME.week_id) = 0 THEN 16
WHEN GROUPING (TIME.ent_period_id) = 0 THEN 32
WHEN GROUPING (TIME.ent_qtr_id) = 0 THEN 64
ELSE 128
END
) period_type_id,
minisite_id,
umarker umark,
umarker,
category_id catid,
parent_category_id,
top_node_flag,
leaf_node,
assist_flag,
currency_cd_f curr_f,
COUNT (DECODE (SUBSTR (umarker, 1, 5), 'ORDER', header_id, NULL)) ord_count,
COUNT (DECODE (SUBSTR (umarker, 1, 5), 'CARTS', order_id, NULL)) con_ord,
SUM (DECODE (SUBSTR (umarker, 1, 5), 'ORDER', booked_amt_g, 0)) ord_amt_g,
SUM (DECODE (SUBSTR (umarker, 1, 5), 'ORDER', booked_amt_g1, 0)) ord_amt_g1,
SUM (DECODE (SUBSTR (umarker, 1, 5), 'ORDER', booked_amt_f, 0)) ord_amt_f,
SUM (DECODE (SUBSTR (umarker, 1, 5), 'ORDER', list_amt_g, 0)) ord_list_g,
SUM (DECODE (SUBSTR (umarker, 1, 5), 'ORDER', list_amt_g1, 0)) ord_list_g1,
SUM (DECODE (SUBSTR (umarker, 1, 5), 'ORDER', list_amt_f, 0)) ord_list_f,
SUM (DECODE (SUBSTR (umarker, 1, 5), 'ORDER', discount_amt_g, 0)) ord_disc_g,
SUM (DECODE (SUBSTR (umarker, 1, 5), 'ORDER', discount_amt_g1, 0)) ord_disc_g1,
SUM (DECODE (SUBSTR (umarker, 1, 5), 'ORDER', discount_amt_f, 0)) ord_disc_f,
COUNT (DECODE (SUBSTR (umarker, 1, 5), 'CARTS', header_id, NULL)) cart_count,
SUM (DECODE (SUBSTR (umarker, 1, 5), 'CARTS', booked_amt_g, 0)) cart_amt_g,
SUM (DECODE (SUBSTR (umarker, 1, 5), 'CARTS', booked_amt_g1, 0)) cart_amt_g1,
SUM (DECODE (SUBSTR (umarker, 1, 5), 'CARTS', booked_amt_f, 0)) cart_amt_f,
0 t_ord_count,
0 t_con_ord,
0 t_ord_amt_g,
0 t_ord_amt_g1,
0 t_ord_amt_f,
0 t_ord_list_g,
0 t_ord_list_g1,
0 t_ord_list_f,
0 t_ord_disc_g,
0 t_ord_disc_g1,
0 t_ord_disc_f,
0 t_cart_count,
0 t_cart_amt_g,
0 t_cart_amt_g1,
0 t_cart_amt_f,
TIME.ent_year_id,
TIME.ent_qtr_id,
TIME.ent_period_id,
TIME.week_id,
TIME.report_date_julian,
COUNT (DECODE (SUBSTR (umarker, 1, 5), 'ORDER', booked_amt_g, 0)) d_ord_amt_g_cnt,
COUNT (DECODE (SUBSTR (umarker, 1, 5), 'ORDER', booked_amt_g1, 0)) d_ord_amt_g1_cnt,
COUNT (DECODE (SUBSTR (umarker, 1, 5), 'ORDER', booked_amt_f, 0)) d_ord_amt_f_cnt,
COUNT (DECODE (SUBSTR (umarker, 1, 5), 'ORDER', list_amt_g, 0)) d_ord_list_g_cnt,
COUNT (DECODE (SUBSTR (umarker, 1, 5), 'ORDER', list_amt_g1, 0)) d_ord_list_g1_cnt,
COUNT (DECODE (SUBSTR (umarker, 1, 5), 'ORDER', list_amt_f, 0)) d_ord_list_f_cnt,
COUNT (DECODE (SUBSTR (umarker, 1, 5), 'ORDER', discount_amt_g, 0)) d_ord_disc_g_cnt,
COUNT (DECODE (SUBSTR (umarker, 1, 5), 'ORDER', discount_amt_g1, 0)) d_ord_disc_g1_cnt,
COUNT (DECODE (SUBSTR (umarker, 1, 5), 'ORDER', discount_amt_f, 0)) d_ord_disc_f_cnt,
COUNT (DECODE (SUBSTR (umarker, 1, 5), 'CARTS', booked_amt_g, 0)) d_cart_amt_g_cnt,
COUNT (DECODE (SUBSTR (umarker, 1, 5), 'CARTS', booked_amt_g1, 0)) d_cart_amt_g1_cnt,
COUNT (DECODE (SUBSTR (umarker, 1, 5), 'CARTS', booked_amt_f, 0)) d_cart_amt_f_cnt,
0 d_con_ord,
0 d_cart_count,
GROUPING_ID
(
minisite_id,
category_id,
parent_category_id,
top_node_flag,
leaf_node,
umarker,
assist_flag,
currency_cd_f,
TIME.ent_year_id,
TIME.ent_qtr_id,
TIME.ent_period_id,
TIME.week_id,
TIME.report_date_julian
) d_gid,
COUNT (*) d_total_cnt,
0 d_ord_count,
0 d_ord_amt_g,
0 d_ord_amt_g1,
0 d_ord_amt_f,
0 d_ord_disc_g,
0 d_ord_disc_g1,
0 d_ord_disc_f,
0 d_ord_list_g,
0 d_ord_list_g1,
0 d_ord_list_f
FROM
APPS.IBE_BI_CAT_MV fact, FII.FII_TIME_DAY TIME
WHERE fact.booked_date_id = TIME.report_date
GROUP BY
minisite_id,
umarker,
category_id,
parent_category_id,
top_node_flag,
leaf_node,
assist_flag,
currency_cd_f,
TIME.ent_year_id,
ROLLUP (TIME.ent_qtr_id, TIME.ent_period_id, TIME.week_id,TIME.report_date_julian)
UNION ALL
SELECT
2 umar,
a.time_id,
a.period_type_id,
a.minisite_id,
'TORDERS' umark,
umarker,
NULL catid,
-233 parent_category_id,
'Y' top_node_flag,
'N' leaf_node,
DECODE (a.measure_type,'IBE_ORD_UNASSISTED', 'N','IBE_ORD_ASSISTED', 'Y','N') assist_flag,
a.currency_cd_f curr_f,
0 ord_count,
0 con_ord,
0 ord_amt_g,
0 ord_amt_g1,
0 ord_amt_f,
0 ord_list_g,
0 ord_list_g1,
0 ord_list_f,
0 ord_disc_g,
0 ord_disc_g1,
0 ord_disc_f,
0 cart_count,
0 cart_amt_g,
0 cart_amt_g1,
0 cart_amt_f,
SUM (tot_count) t_ord_count,
SUM (con_ord) t_con_ord,
SUM (a.amount_g) t_ord_amt_g,
SUM (a.amount_g1) t_ord_amt_g1,
SUM (a.amount_f) t_ord_amt_f,
SUM (a.list_amount_g) t_ord_list_g,
SUM (a.list_amount_g1) t_ord_list_g1,
SUM (a.list_amount_f) t_ord_list_f,
SUM (a.disc_amount_g) t_ord_disc_g,
SUM (a.disc_amount_g1) t_ord_disc_g1,
SUM (a.disc_amount_f) t_ord_disc_f,
0 t_cart_count,
0 t_cart_amt_g,
0 t_cart_amt_g1,
0 t_cart_amt_f,
1 ent_year_id,
1 ent_qtr_id,
1 ent_period_id,
1 week_id,
1 report_date_julian,
COUNT (a.d_lines_g) d_amt_g_cnt,
COUNT (a.d_lines_g1) d_amt_g1_cnt,
COUNT (a.d_lines_f) d_amt_f_cnt,
COUNT (a.d_list_lines_g) d_list_amt_g_cnt,
COUNT (a.d_list_lines_g1) d_list_amt_g1_cnt,
COUNT (a.d_list_lines_f) d_list_amt_f_cnt,
COUNT (a.d_disc_lines_g) d_disc_g_cnt,
COUNT (a.d_disc_lines_g1) d_disc_g1_cnt,
COUNT (a.d_disc_lines_f) d_disc_f_cnt,
0 d_cart_amt_g_cnt,
0 d_cart_amt_g1_cnt,
0 d_cart_amt_f_cnt,
COUNT (con_ord) d_con_ord,
0 d_cart_count,
COUNT (1) d_gid,
COUNT (*) d_total_cnt,
COUNT (tot_count) d_ord_count,
COUNT (a.amount_g) d_ord_amt_g,
COUNT (a.amount_g1) d_ord_amt_g1,
COUNT (a.amount_f) d_ord_amt_f,
COUNT (a.disc_amount_g) d_ord_disc_g,
COUNT (a.disc_amount_g1) d_ord_disc_g1,
COUNT (a.disc_amount_f) d_ord_disc_f,
COUNT (a.list_amount_g) d_ord_list_g,
COUNT (a.list_amount_g1) d_ord_list_g1,
COUNT (a.list_amount_f) d_ord_list_f
FROM
APPS.IBE_BI_CART_ORD_MV a
WHERE
umarker = 'ORESTIME'
AND a.measure_type IN ('IBE_ORD_UNASSISTED', 'IBE_ORD_ASSISTED')
GROUP BY
time_id,
period_type_id,
minisite_id,
umarker,
DECODE (a.measure_type,'IBE_ORD_UNASSISTED', 'N','IBE_ORD_ASSISTED', 'Y','N'),
a.currency_cd_f
UNION ALL
SELECT
3 umar,
a.time_id,
a.period_type_id,
a.minisite_id,
'TCARTS' umark,
umarker,
NULL catid,
-233 parent_category_id,
'Y' top_node_flag,
'N' leaf_node,
DECODE (a.measure_type,'IBE_QOT_UNASSISTED', 'N','IBE_QOT_ASSISTED', 'Y','N') assist_flag,
a.currency_cd_f curr_f,
0 ord_count,
0 con_ord,
0 ord_amt_g,
0 ord_amt_g1,
0 ord_amt_f,
0 ord_list_g,
0 ord_list_g1,
0 ord_list_f,
0 ord_disc_g,
0 ord_disc_g1,
0 ord_disc_f,
0 cart_count,
0 cart_amt_g,
0 cart_amt_g1,
0 cart_amt_f,
0 t_ord_count,
SUM (con_ord) t_con_ord,
0 t_ord_amt_g,
0 t_ord_amt_g1,
0 t_ord_amt_f,
0 t_ord_list_g,
0 t_ord_list_g1,
0 t_ord_list_f,
0 t_ord_disc_g,
0 t_ord_disc_g1,
0 t_ord_disc_f,
SUM (tot_count) t_cart_count,
SUM (a.amount_g) t_cart_amt_g,
SUM (a.amount_g1) t_cart_amt_g1,
SUM (a.amount_f) t_cart_amt_f,
1 ent_year_id,
1 ent_qtr_id,
1 ent_period_id,
1 week_id,
1 report_date_julian,
0 d_amt_g_cnt,
0 d_amt_g1_cnt,
0 d_amt_f_cnt,
0 d_list_amt_g_cnt,
0 d_list_amt_g1_cnt,
0 d_list_amt_f_cnt,
0 d_disc_g_cnt,
0 d_disc_g1_cnt,
0 d_disc_f_cnt,
COUNT (a.amount_g) d_cart_amt_g_cnt,
COUNT (a.amount_g1) d_cart_amt_g1_cnt,
COUNT (a.amount_f) d_cart_amt_f_cnt,
COUNT (con_ord) d_con_ord,
COUNT (tot_count ) d_cart_count,
COUNT (1) d_gid,
COUNT (*) d_total_cnt,
0 d_ord_count,
0 d_ord_amt_g,
0 d_ord_amt_g1,
0 d_ord_amt_f,
0 d_ord_disc_g,
0 d_ord_disc_g1,
0 d_ord_disc_f,
0 d_ord_list_g,
0 d_ord_list_g1,
0 d_ord_list_f
FROM
APPS.IBE_BI_CART_ORD_MV a
WHERE
umarker = 'QRESTIME'
AND a.measure_type IN ('IBE_QOT_UNASSISTED', 'IBE_QOT_ASSISTED')
GROUP BY
time_id,
period_type_id,
minisite_id,
umarker,
DECODE (a.measure_type,'IBE_QOT_UNASSISTED', 'N','IBE_QOT_ASSISTED', 'Y','N'),
a.currency_cd_f