MATERIALIZED VIEW: APPS.IBE_BI_ORDERBOM_MV

Source

SELECT   book.booked_date,
             book.time_booked_date_id,
             book.customer_id,
             book.header_id,
             book.order_number,
             COUNT (DECODE (top_model_item_id,
             inventory_item_id, line_id, NULL))              no_lines,
             quotes.minisite_id,
             book.currency_func_id                           currency_cd_f,
             quotes.resource_flag,
             quotes.published_quote_flag,
             quotes.marketing_source_code_id_hdr,
             COUNT(DECODE (top_model_item_id,
                   inventory_item_id,
                   book.marketing_source_code_id,NULL))      marketing_source_code_id,
             SUM (NVL (book.booked_amt_f, 0))                booked_amt_f,
             SUM (NVL (book.booked_amt_g, 0))                booked_amt_g,
             SUM (NVL (book.booked_amt_g1, 0))               booked_amt_g1,
             SUM (NVL (book.booked_list_amt_f, 0))           booked_list_amt_f,
             SUM (NVL (book.booked_list_amt_g, 0))           booked_list_amt_g,
             SUM (NVL (book.booked_list_amt_g1, 0))          booked_list_amt_g1,
             COUNT (NVL (book.booked_amt_f, 0))              d_booked_amt_f_cnt,
             COUNT (NVL (book.booked_amt_g, 0))              d_booked_amt_g_cnt,
             COUNT (NVL (book.booked_amt_g1, 0))             d_booked_amt_g1_cnt,
             COUNT (NVL (book.booked_list_amt_f, 0))         d_booked_list_amt_f_cnt,
             COUNT (NVL (book.booked_list_amt_g, 0))         d_booked_list_amt_g_cnt,
             COUNT (NVL (book.booked_list_amt_g1, 0))        d_booked_list_amt_g1_cnt,
             COUNT (*)                                       d_tot_cnt,
             book.top_model_item_id inventory_item_id,
             book.top_model_org_id organization_id
   FROM ISC.ISC_BOOK_SUM2_F book, ibe_bi_carts_mv quotes
   WHERE quotes.order_id = book.header_id
   AND book.order_category_id IN ('ORDER')
   AND book.line_category_code = 'ORDER'
   AND book.flow_status_code <> 'CANCELLED'
   AND book.charge_periodicity_code IS NULL            -- Added as per Bug # 4589076 to remove recurring charge lines
   GROUP BY book.booked_date,
         book.time_booked_date_id,
         book.customer_id,
         book.header_id,
         book.order_number,
         quotes.minisite_id,
         book.currency_func_id,
         quotes.resource_flag,
         quotes.published_quote_flag,
         quotes.marketing_source_code_id_hdr,
         book.top_model_item_id,
         book.top_model_org_id