[Home] [Help]
MATERIALIZED VIEW: APPS.IBE_BI_CART_ORD_MV
Source
SELECT /* Fix for bug # 5024093 */
'QRESTIME' umarker,
qot.minisite_id,
DECODE (GROUPING_ID (qot.resource_flag),
1, 'IBE_QOT_TOTAL',
DECODE (qot.resource_flag,
'N', 'IBE_QOT_UNASSISTED',
'IBE_QOT_ASSISTED'
)
) measure_type,
(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,
qot.currency_cd_f,
SUM (amount_f) amount_f,
SUM (amount_g) amount_g,
SUM (amount_g1) amount_g1,
0 list_amount_f,
0 list_amount_g,
0 list_amount_g1,
0 disc_amount_f,
0 disc_amount_g,
0 disc_amount_g1,
SUM (quote_lines) no_lines,
COUNT (qot.quote_header_id) tot_count,
COUNT (qot.order_id) con_ord,
COUNT (amount_f) d_lines_f,
COUNT (amount_g) d_lines_g,
COUNT (amount_g1) d_lines_g1,
0 d_list_lines_f,
0 d_list_lines_g,
0 d_list_lines_g1,
0 d_disc_lines_f,
0 d_disc_lines_g,
0 d_disc_lines_g1,
COUNT (quote_lines) d_no_of_lines_cnt,
COUNT (*) d_total_number,
TIME.ent_year_id,
TIME.ent_qtr_id,
TIME.ent_period_id,
TIME.report_date_julian,
TIME.week_id,
GROUPING_ID (qot.resource_flag,
qot.currency_cd_f,
qot.minisite_id,
TIME.ent_year_id,
TIME.ent_qtr_id,
TIME.ent_period_id,
TIME.week_id,
TIME.report_date_julian
) d_gid,
qot.resource_flag
FROM APPS.IBE_BI_CARTS_MV qot, FII.FII_TIME_DAY TIME
WHERE qot.creation_date = TIME.report_date
AND qot.published_quote_flag = 'N'
GROUP BY qot.currency_cd_f,
qot.minisite_id,
TIME.ent_year_id,
ROLLUP (TIME.ent_qtr_id, TIME.ent_period_id, TIME.week_id,
TIME.report_date_julian),
ROLLUP (qot.resource_flag)
UNION ALL
SELECT 'QQUOTETIME' umarker,
qot.minisite_id,
'IBE_QOT_QUOTING' measure_type,
(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,
qot.currency_cd_f,
SUM (amount_f) amount_f,
SUM (amount_g) amount_g,
SUM (amount_g1) amount_g1,
0 list_amount_f,
0 list_amount_g,
0 list_amount_g1,
0 disc_amount_f,
0 disc_amount_g,
0 disc_amount_g1,
SUM (quote_lines) no_lines,
COUNT (qot.quote_header_id) tot_count,
COUNT (qot.order_id) con_ord,
COUNT (amount_f) d_lines_f,
COUNT (amount_g) d_lines_g,
COUNT (amount_g1) d_lines_g1,
0 d_list_lines_f,
0 d_list_lines_g,
0 d_list_lines_g1,
0 d_disc_lines_f,
0 d_disc_lines_g,
0 d_disc_lines_g1,
COUNT (quote_lines) d_no_of_lines_cnt,
COUNT (*) d_total_number,
TIME.ent_year_id,
TIME.ent_qtr_id,
TIME.ent_period_id,
TIME.report_date_julian,
TIME.week_id,
GROUPING_ID (qot.currency_cd_f,
qot.minisite_id,
TIME.ent_year_id,
TIME.ent_qtr_id,
TIME.ent_period_id,
TIME.week_id,
TIME.report_date_julian
) d_gid,
NULL resource_flag
FROM APPS.IBE_BI_CARTS_MV qot, FII.FII_TIME_DAY TIME
WHERE qot.creation_date = TIME.report_date
AND qot.published_quote_flag = 'Y'
GROUP BY qot.currency_cd_f,
qot.minisite_id,
TIME.ent_year_id,
ROLLUP (TIME.ent_qtr_id, TIME.ent_period_id, TIME.week_id,
TIME.report_date_julian)
UNION ALL
SELECT 'QCAMPTIME' umarker,
qot.minisite_id,
'IBE_QOT_CAMPAIGN' measure_type,
(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,
qot.currency_cd_f,
SUM (mkt_amt_f) amount_f,
SUM (mkt_amt_g) amount_g,
SUM (mkt_amt_g1) amount_g1,
0 list_amount_f,
0 list_amount_g,
0 list_amount_g1,
0 disc_amount_f,
0 disc_amount_g,
0 disc_amount_g1,
SUM (mkt_lines) no_lines,
COUNT (qot.quote_header_id) tot_count,
COUNT (qot.order_id) con_ord,
COUNT (mkt_amt_f) d_lines_f,
COUNT (mkt_amt_g) d_lines_g,
COUNT (mkt_amt_g1) d_lines_g1,
0 d_list_lines_f,
0 d_list_lines_g,
0 d_list_lines_g1,
0 d_disc_lines_f,
0 d_disc_lines_g,
0 d_disc_lines_g1,
COUNT (mkt_lines) d_no_of_lines_cnt,
COUNT (*) d_total_number,
TIME.ent_year_id,
TIME.ent_qtr_id,
TIME.ent_period_id,
TIME.report_date_julian,
TIME.week_id,
GROUPING_ID (qot.currency_cd_f,
qot.minisite_id,
TIME.ent_year_id,
TIME.ent_qtr_id,
TIME.ent_period_id,
TIME.week_id,
TIME.report_date_julian
) d_gid,
NULL resource_flag
FROM APPS.IBE_BI_CARTS_MV qot, FII.FII_TIME_DAY TIME
WHERE qot.creation_date = TIME.report_date
AND qot.published_quote_flag = 'N'
AND qot.mkt_lines > 0
GROUP BY qot.currency_cd_f,
qot.minisite_id,
TIME.ent_year_id,
ROLLUP (TIME.ent_qtr_id, TIME.ent_period_id, TIME.week_id,
TIME.report_date_julian)
UNION ALL
SELECT 'ORESTIME' umarker,
ord.minisite_id,
DECODE (GROUPING_ID (ord.resource_flag),
1, 'IBE_ORD_TOTAL',
DECODE (ord.resource_flag,
'N', 'IBE_ORD_UNASSISTED',
'IBE_ORD_ASSISTED'
)
) measure_type,
(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,
ord.currency_cd_f,
SUM (amount_f) amount_f,
SUM (amount_g) amount_g,
SUM (amount_g1) amount_g1,
SUM (list_amount_f) list_amount_f,
SUM (list_amount_g) list_amount_g,
SUM (list_amount_g1) list_amount_g1,
SUM (disc_amount_f) disc_amount_f,
SUM (disc_amount_g) disc_amount_g,
SUM (disc_amount_g1) disc_amount_g1,
SUM (order_lines) no_lines,
COUNT (ord.header_id) tot_count,
NULL con_ord,
COUNT (amount_f) d_order_lines_f,
COUNT (amount_g) d_order_lines_g,
COUNT (amount_g1) d_order_lines_g1,
COUNT (list_amount_f) d_list_lines_f,
COUNT (list_amount_g) d_list_lines_g,
COUNT (list_amount_g1) d_list_lines_g1,
COUNT (disc_amount_f) d_disc_lines_f,
COUNT (disc_amount_g) d_disc_lines_g,
COUNT (disc_amount_g1) d_disc_lines_g1,
COUNT (order_lines) d_no_of_lines_cnt,
COUNT (*) d_total_number,
TIME.ent_year_id,
TIME.ent_qtr_id,
TIME.ent_period_id,
TIME.report_date_julian,
TIME.week_id,
GROUPING_ID (ord.resource_flag,
ord.currency_cd_f,
ord.minisite_id,
TIME.ent_year_id,
TIME.ent_qtr_id,
TIME.ent_period_id,
TIME.week_id,
TIME.report_date_julian
) d_gid,
ord.resource_flag
FROM APPS.IBE_BI_ORDERS_MV ord, FII.FII_TIME_DAY TIME
WHERE ord.time_booked_date_id = TIME.report_date
AND ord.published_quote_flag = 'N'
GROUP BY ord.currency_cd_f,
ord.minisite_id,
TIME.ent_year_id,
ROLLUP (TIME.ent_qtr_id, TIME.ent_period_id, TIME.week_id,
TIME.report_date_julian),
ROLLUP (ord.resource_flag)
UNION ALL
SELECT 'OQUOTETIME' umarker,
ord.minisite_id,
'IBE_ORD_QUOTING' measure_type,
(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,
ord.currency_cd_f,
SUM (amount_f) amount_f,
SUM (amount_g) amount_g,
SUM (amount_g1) amount_g1,
SUM (list_amount_f) list_amount_f,
SUM (list_amount_g) list_amount_g,
SUM (list_amount_g1) list_amount_g1,
SUM (disc_amount_f) disc_amount_f,
SUM (disc_amount_g) disc_amount_g,
SUM (disc_amount_g1) disc_amount_g1,
SUM (order_lines) no_lines,
COUNT (ord.header_id) tot_count,
NULL con_ord,
COUNT (amount_f) d_order_lines_f,
COUNT (amount_g) d_order_lines_g,
COUNT (amount_g1) d_order_lines_g1,
COUNT (list_amount_f) d_list_lines_f,
COUNT (list_amount_g) d_list_lines_g,
COUNT (list_amount_g1) d_list_lines_g1,
COUNT (disc_amount_f) d_disc_lines_f,
COUNT (disc_amount_g) d_disc_lines_g,
COUNT (disc_amount_g1) d_disc_lines_g1,
COUNT (order_lines) d_no_of_lines_cnt,
COUNT (*) d_total_number,
TIME.ent_year_id,
TIME.ent_qtr_id,
TIME.ent_period_id,
TIME.report_date_julian,
TIME.week_id,
GROUPING_ID (ord.currency_cd_f,
ord.minisite_id,
TIME.ent_year_id,
TIME.ent_qtr_id,
TIME.ent_period_id,
TIME.week_id,
TIME.report_date_julian
) d_gid,
NULL resource_flag
FROM APPS.IBE_BI_ORDERS_MV ord, FII.FII_TIME_DAY TIME
WHERE ord.time_booked_date_id = TIME.report_date
AND ord.published_quote_flag = 'Y'
GROUP BY ord.currency_cd_f,
ord.minisite_id,
TIME.ent_year_id,
ROLLUP (TIME.ent_qtr_id, TIME.ent_period_id, TIME.week_id,
TIME.report_date_julian)
UNION ALL
SELECT 'OCAMPTIME' umarker,
ord.minisite_id,
'IBE_ORD_CAMPAIGN' measure_type,
(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,
ord.currency_cd_f,
SUM (mkt_amt_f) amount_f,
SUM (mkt_amt_g) amount_g,
SUM (mkt_amt_g1) amount_g1,
SUM (list_amount_f) list_amount_f,
SUM (list_amount_g) list_amount_g,
SUM (list_amount_g1) list_amount_g1,
0 disc_amount_f,
0 disc_amount_g,
0 disc_amount_g1,
SUM (mkt_lines) no_lines,
COUNT (header_id) tot_count,
NULL con_ord,
COUNT (mkt_amt_f) d_order_lines_f,
COUNT (mkt_amt_g) d_order_lines_g,
COUNT (mkt_amt_g1) d_order_lines_g1,
COUNT (list_amount_f) d_list_lines_f,
COUNT (list_amount_g) d_list_lines_g,
COUNT (list_amount_g1) d_list_lines_g1,
0 d_disc_lines_f,
0 d_disc_lines_g,
0 d_disc_lines_g1,
COUNT (mkt_lines) no_of_lines_cnt,
COUNT (*) d_total_number,
TIME.ent_year_id,
TIME.ent_qtr_id,
TIME.ent_period_id,
TIME.report_date_julian,
TIME.week_id,
GROUPING_ID (ord.currency_cd_f,
ord.minisite_id,
TIME.ent_year_id,
TIME.ent_qtr_id,
TIME.ent_period_id,
TIME.week_id,
TIME.report_date_julian
) d_gid,
NULL resource_flag
FROM APPS.IBE_BI_ORDERS_MV ord, FII.FII_TIME_DAY TIME
WHERE ord.time_booked_date_id = TIME.report_date
AND ord.published_quote_flag = 'N'
AND ord.mkt_lines > 0
GROUP BY ord.currency_cd_f,
ord.minisite_id,
TIME.ent_year_id,
ROLLUP (TIME.ent_qtr_id, TIME.ent_period_id, TIME.week_id,
TIME.report_date_julian)