MATERIALIZED VIEW: APPS.OPI_INV_ITD_ORG_MV

Source

Select /* 12.0: bug#4526784 */ ytd.organization_id,
           ytd.as_of_date transaction_date,
           ytd.inv_balance_g + NVL(SUM(prior_yr.inv_total_value_g),0)
            inv_balance_g,
           ytd.inv_balance_b + NVL(SUM(prior_yr.inv_total_value_b),0)
            inv_balance_b,
           ytd.inv_balance_sg + NVL(SUM(prior_yr.inv_total_value_sg),0) inv_balance_sg
      FROM (SELECT /*+ ORDERED */
                as_of.organization_id organization_id,
                struct.report_date as_of_date,
                day.ent_year_id as_of_year,
                SUM(f.inv_total_value_g) inv_balance_g,
                SUM(f.inv_total_value_sg) inv_balance_sg,
                SUM(f.inv_total_value_b) inv_balance_b
              FROM opi_inv_val_sum_mv as_of,
                   FII.FII_TIME_DAY day,
                   FII.FII_TIME_RPT_STRUCT struct,
                   opi_inv_val_sum_mv f
              WHERE day.report_date_julian = as_of.time_id
                AND struct.calendar_id = -1
                AND struct.report_date = day.report_date
                AND struct.period_type_id <= 64
                AND BITAND (struct.record_type_id, 119) = struct.record_type_id
                AND struct.record_type_id not in (256, 512)
                AND f.organization_id = as_of.organization_id
                AND f.source = as_of.source
                AND f.time_id = struct.time_id
                AND as_of.aggregation_level_flag = 7
                AND f.aggregation_level_flag = 7
              GROUP BY as_of.organization_id,
                       struct.report_date,
                       day.ent_year_id) ytd,
            (SELECT f.organization_id,
                    y.ent_year_id,
                    f.inv_total_value_b,
                    f.inv_total_value_sg,
                    f.inv_total_value_g
              FROM  opi_inv_val_sum_mv f,
                    FII.FII_TIME_ENT_YEAR y
              WHERE y.ent_year_id = f.time_id
                AND f.aggregation_level_flag = 7) prior_yr
      WHERE prior_yr.organization_id(+) = ytd.organization_id
        AND prior_yr.ent_year_id(+) < ytd.as_of_year
      GROUP BY ytd.organization_id,
               ytd.as_of_date,
               ytd.inv_balance_g,
               ytd.inv_balance_b,
               ytd.inv_balance_sg