[Home] [Help]
MATERIALIZED VIEW: APPS.IBE_BI_ITEM_MV
Source
SELECT /* Fix for bug # 5024093 */
'ORDERS' umarker,
ord.time_booked_date_id booked_date_id,
ord.header_id,
COUNT (ord.header_id) num_of_ord_cart,
0 no_orders,
null ORDER_HEADER_ID,
ord.minisite_id minisite_id,
NVL (item.master_id,
item.inventory_item_id || '-'
|| item.organization_id) item_id,
item.vbh_category_id category_id,
ord.resource_flag resource_flag,
ord.currency_cd_f,
SUM (ord.booked_amt_g) booked_amt_g,
SUM (ord.booked_amt_g1) booked_amt_g1,
SUM (ord.booked_amt_f) booked_amt_f,
SUM (ord.booked_list_amt_g) booked_list_amt_g,
SUM (ord.booked_list_amt_g1) booked_list_amt_g1,
SUM (ord.booked_list_amt_f) booked_list_amt_f,
SUM (ord.booked_list_amt_g
- ord.booked_amt_g) discount_amt_g,
SUM (ord.booked_list_amt_g1
- ord.booked_amt_g1) discount_amt_g1,
SUM (ord.booked_list_amt_f
- ord.booked_amt_f) discount_amt_f,
SUM (ord.no_lines) num_of_lines,
COUNT (ord.booked_amt_g) d_booked_amt_g_cnt,
COUNT (ord.booked_amt_g1) d_booked_amt_g1_cnt,
COUNT (ord.booked_amt_f) d_booked_amt_f_cnt,
COUNT (ord.booked_list_amt_g
- ord.booked_amt_g) d_discount_amt_g_cnt,
COUNT (ord.booked_list_amt_g1
- ord.booked_amt_g1) d_discount_amt_g1_cnt,
COUNT (ord.booked_list_amt_f
- ord.booked_amt_f) d_discount_amt_f_cnt,
COUNT (ord.booked_list_amt_g) d_booked_list_amt_g_cnt,
COUNT (ord.booked_list_amt_g1) d_booked_list_amt_g1_cnt,
COUNT (ord.booked_list_amt_f) d_booked_list_amt_f_cnt,
COUNT (ord.no_lines) d_num_of_lines_cnt,
COUNT (*) d_tot_cnt
FROM ibe_bi_orderbom_mv ord, ENI.ENI_OLTP_ITEM_STAR item
WHERE ord.inventory_item_id = item.inventory_item_id
AND ord.organization_id = item.organization_id
AND ord.published_quote_flag = 'N'
GROUP BY NVL (item.master_id,item.inventory_item_id
|| '-' || item.organization_id),
ord.minisite_id,
item.vbh_category_id,
ord.time_booked_date_id,
ord.resource_flag,
ord.currency_cd_f,
ord.header_id
UNION ALL
SELECT 'CARTS' umarker,
TRUNC (qot.quote_creation_date) booked_date_id,
qot.quote_header_id,
COUNT (qot.quote_header_id) num_of_ord_cart,
COUNT (qot.order_id) no_orders,
qot.order_id ORDER_HEADER_ID,
qot.minisite_id_hdr minisite_id,
NVL (item.master_id,
item.inventory_item_id ||'-'
|| item.organization_id) item_id,
item.vbh_category_id category_id,
DECODE (qot.resource_id,
NULL, 'N', 'Y') resource_flag,
qot.functional_currency currency_cd_f,
SUM (NVL (qot.line_quote_value, 0)*
NVL (reporting_conversion_rate,
0)
) booked_amt_g,
SUM (NVL (qot.line_quote_value, 0) *
NVL (sec_conversion_rate, 0)) booked_amt_g1,
SUM (NVL (qot.line_quote_value, 0) *
NVL (functional_conversion_rate,
0)
) booked_amt_f,
0 booked_list_amt_g,
0 booked_list_amt_g1,
0 booked_list_amt_f,
0 discount_amt_g,
0 discount_amt_g1,
0 discount_amt_f,
COUNT (DECODE
(qot.top_inventory_item_id,
qot.inventory_item_id,
qot.quote_line_id)
) num_of_lines,
COUNT (NVL (qot.line_quote_value, 0)
* NVL (reporting_conversion_rate
, 0)
) d_booked_amt_g_cnt,
COUNT (NVL (qot.line_quote_value, 0)
* NVL (sec_conversion_rate, 0)
) d_booked_amt_g1_cnt,
COUNT (NVL (qot.line_quote_value, 0)
* NVL (functional_conversion_rate
, 0)
) d_booked_amt_f_cnt,
0 d_discount_amt_g_cnt,
0 d_discount_amt_g1_cnt,
0 d_discount_amt_f_cnt,
0 d_booked_list_amt_g_cnt,
0 d_booked_list_amt_g1_cnt,
0 d_booked_list_amt_f_cnt,
0 d_num_of_lines_cnt,
COUNT (*) d_tot_cnt
FROM ASO.ASO_BI_QUOTE_LINES_ALL qot, ENI.ENI_OLTP_ITEM_STAR item
WHERE qot.top_inventory_item_id = item.inventory_item_id
AND qot.top_organization_id = item.organization_id
AND qot.CHARGE_PERIODICITY_CODE is NULL
AND DECODE (UPPER (SUBSTR (qot.quote_source_code, 1, 6)),
'ISTORE', 'N','Y'
) = 'N'
AND minisite_id_hdr IS NOT NULL
GROUP BY NVL (item.master_id,
item.inventory_item_id || '-' || item.organization_id
),
qot.minisite_id_hdr,
item.vbh_category_id,
TRUNC (qot.quote_creation_date),
DECODE (qot.resource_id, NULL, 'N', 'Y'),
qot.functional_currency,
qot.quote_header_id,
qot.order_id