MATERIALIZED VIEW: APPS.IBE_BI_CARTS_MV

Source

SELECT   qot.quote_creation_date                              creation_date,
         qot.quote_header_id,
         qot.quote_number,
         qot.minisite_id_hdr minisite_id,
         qot.functional_currency currency_cd_f,
         qot.order_id,
         qot.quote_expiration_date,
         qot.quote_status_id,
         qot.cust_party_id,
         DECODE (qot.resource_id, NULL,
         'N', 'Y')                                        resource_flag,
         DECODE (UPPER (SUBSTR (qot.quote_source_code,
         1, 6)), 'ISTORE', 'N','Y')                     published_quote_flag,
         qot.marketing_source_code_id_hdr,
         SUM (DECODE (marketing_source_code_id,
                      NULL, NULL,
                      NVL (qot.line_quote_value, 0)
                      * NVL (functional_conversion_rate, 0)
                     ))                                       mkt_amt_f,
         SUM (DECODE (marketing_source_code_id,
                      NULL, NULL,
                      NVL (qot.line_quote_value, 0)
                      * NVL (reporting_conversion_rate, 0)
                      ))                                      mkt_amt_g,
         SUM (DECODE (marketing_source_code_id,
                      NULL, NULL,
                      NVL (qot.line_quote_value, 0)
                      * NVL (sec_conversion_rate, 0)
                      ))                                      mkt_amt_g1,
         COUNT (DECODE (marketing_source_code_id,
                        NULL, NULL, 1))                       mkt_lines,
         SUM (  NVL (qot.line_quote_value, 0)
              * NVL (functional_conversion_rate, 0)
             )                                                amount_f,
         SUM (  NVL (qot.line_quote_value, 0)
              * NVL (reporting_conversion_rate, 0)
             )                                                amount_g,
         SUM (NVL (qot.line_quote_value, 0)
              * NVL (sec_conversion_rate, 0)
             )                                                amount_g1,
         COUNT (DECODE (qot.top_inventory_item_id,
                        qot.inventory_item_id, 1)
               )                                              quote_lines,
         COUNT (DECODE (marketing_source_code_id,
                        NULL, NULL,
                        NVL (qot.line_quote_value, 0)
                        * NVL (functional_conversion_rate, 0)
                       ))                                     d_mkt_amt_f_cnt,
         COUNT (DECODE (marketing_source_code_id,
                        NULL, NULL,
                        NVL (qot.line_quote_value, 0)
                        * NVL (reporting_conversion_rate, 0)
                       ))                                     d_mkt_amt_g_cnt,
         COUNT (DECODE (marketing_source_code_id,
                        NULL, NULL,
                        NVL (qot.line_quote_value, 0)
                        * NVL (sec_conversion_rate, 0)
                       ))                                     d_mkt_amt_g1_cnt,
         COUNT (  NVL (qot.line_quote_value, 0)
                * NVL (functional_conversion_rate, 0)
               )                                              d_amount_f_cnt,
         COUNT (  NVL (qot.line_quote_value, 0)
                * NVL (reporting_conversion_rate, 0)
               )                                              d_amount_g_cnt,
         COUNT (NVL (qot.line_quote_value, 0)
                * NVL (sec_conversion_rate, 0)
               )                                              d_amount_g1_cnt,
         COUNT (*) d_total_cnt
   FROM ASO.ASO_BI_QUOTE_LINES_ALL qot
   WHERE qot.minisite_id_hdr IS NOT NULL AND qot.line_category_code = 'ORDER'
     AND qot.CHARGE_PERIODICITY_CODE is NULL
   GROUP BY qot.quote_creation_date,
         qot.quote_header_id,
         qot.quote_number,
         qot.minisite_id_hdr,
         qot.functional_currency,
         qot.order_id,
         qot.quote_expiration_date,
         qot.quote_status_id,
         qot.cust_party_id,
         DECODE (qot.resource_id, NULL, 'N', 'Y'),
         DECODE (UPPER (SUBSTR (qot.quote_source_code, 1, 6)),
                 'ISTORE', 'N',
                 'Y'),
         qot.marketing_source_code_id_hdr