MATERIALIZED VIEW: APPS.IBE_BI_ORDERS_MV

Source

SELECT   book.booked_date,
             book.time_booked_date_id,
             book.customer_id,
             book.header_id,
             book.minisite_id,
             book.currency_cd_f,
             book.resource_flag,
             book.published_quote_flag,
             book.order_number,
             book.marketing_source_code_id_hdr                       marketing_source_code_id,
             SUM (DECODE (NVL (book.marketing_source_code_id,
                          0),0, 0,book.booked_amt_f))                mkt_amt_f,
             SUM (DECODE (NVL (book.marketing_source_code_id,
                          0),0, 0,book.booked_amt_g))                mkt_amt_g,
             SUM (DECODE (NVL (book.marketing_source_code_id,
                          0),0, 0,book.booked_amt_g1))               mkt_amt_g1,
             SUM (DECODE (NVL (book.marketing_source_code_id,
                          0),0, 0,book.no_lines))                    mkt_lines,
             SUM (book.booked_amt_f)                                 amount_f,
             SUM (book.booked_amt_g)                                 amount_g,
             SUM (book.booked_amt_g1)                                amount_g1,
             SUM (book.booked_list_amt_f)                            list_amount_f,
             SUM (book.booked_list_amt_g)                            list_amount_g,
             SUM (book.booked_list_amt_g1)                           list_amount_g1,
             SUM (book.booked_list_amt_f - book.booked_amt_f)        disc_amount_f,
             SUM (book.booked_list_amt_g - book.booked_amt_g)        disc_amount_g,
             SUM (book.booked_list_amt_g1 - book.booked_amt_g1)      disc_amount_g1,
             SUM (no_lines) order_lines,
             COUNT (DECODE (NVL (book.marketing_source_code_id,
                    0),0, 0,book.booked_amt_f))                      d_mkt_amt_f_cnt,
             COUNT (DECODE (NVL (book.marketing_source_code_id,
                    0),0, 0,book.booked_amt_g))                      d_mkt_amt_g_cnt,
             COUNT (DECODE (NVL (book.marketing_source_code_id,
                    0),0, 0,book.booked_amt_g1))                     d_mkt_amt_g1_cnt,
             COUNT (DECODE (NVL (book.marketing_source_code_id,
                    0),0, 0,book.no_lines))                          d_mkt_lines_cnt,
             COUNT (book.booked_amt_f)                               d_amount_f_cnt,
             COUNT (book.booked_amt_g)                               d_amount_g_cnt,
             COUNT (book.booked_amt_g1)                              d_amount_g1_cnt,
             COUNT (book.booked_list_amt_f)                          d_list_amount_f_cnt,
             COUNT (book.booked_list_amt_g)                          d_list_amount_g_cnt,
             COUNT (book.booked_list_amt_g1)                         d_list_amount_g1_cnt,
             COUNT (book.booked_list_amt_f - book.booked_amt_f)      d_disc_amount_f_cnt,
             COUNT (book.booked_list_amt_g - book.booked_amt_g)      d_disc_amount_g_cnt,
             COUNT (book.booked_list_amt_g1 - book.booked_amt_g1)    d_disc_amount_g1_cnt,
             COUNT (no_lines)                                        d_order_lines_cnt,
             COUNT (*)                                               d_total_cnt
    FROM ibe_bi_orderbom_mv book
    GROUP BY book.booked_date,
             book.time_booked_date_id,
             book.customer_id,
             book.header_id,
             book.minisite_id,
             book.currency_cd_f,
             book.resource_flag,
             book.published_quote_flag,
             book.order_number,
             book.marketing_source_code_id_hdr