MATERIALIZED VIEW: APPS.OPI_DBI_RES_VAR_MV

Source

select /* 12.0: bug#4526784 */grouping_id (res.organization_id,
                        res.resource_group,
                        res.department_id,
                        res.resource_id,
                        ent_qtr_id,
                        ent_period_id,
                        week_id,
                        report_date_julian,
                        res.ID,
                        res.department_fk,
                        res.resource_group_fk) grp_id,
           decode (grouping_id (ent_qtr_id, ent_period_id,
                                week_id, report_date_julian),
                   14, report_date_julian,
                   13,week_id,
                   11, ent_period_id,
                   7, ent_qtr_id) time_id,
           decode (grouping_id (cal.ent_qtr_id, cal.ent_period_id,
                                cal.week_id, cal.report_date_julian),
                   14, 1,
                   13, 16,
                   11, 32,
                   7,64) PERIOD_TYPE_ID,
           grouping_id (res.resource_group, res.department_id,
                        res.resource_id ) RESOURCE_LEVEL_FLAG,
           res.organization_id,
           res.resource_id,
           res.ID resource_org_id,
           res.resource_group,
           res.resource_group_fk resource_group_id,
           res.department_id,
           res.department_fk resource_department_id,
           cal.ent_qtr_id,
           cal.ent_period_id,
           cal.week_id,
           cal.report_date_julian,
           sum (std.std_usage_val_b) std_usage_val_b,
           sum (std.std_usage_val_b * job.conversion_rate) std_usage_val_g,
           sum (std.std_usage_qty_g) std_usage_qty,
           sum (act.actual_val_b) actual_val_b,
           sum (act.actual_val_b * job.conversion_rate) actual_val_g,
           sum (act.actual_qty_g) actual_qty,
           count (std.std_usage_val_b) std_usage_val_b_cnt,
           count (std.std_usage_val_b * job.conversion_rate)
            std_usage_val_g_cnt,
           count (std.std_usage_qty_g) std_usage_qty_cnt,
           count (act.actual_val_b) actual_val_b_cnt,
           count (act.actual_val_b * job.conversion_rate)
            actual_val_g_cnt,
           count (act.actual_qty_g) actual_qty_cnt,
           sum (std.std_usage_val_b * job.sec_conversion_rate) std_usage_val_sg,
           count (std.std_usage_val_b * job.sec_conversion_rate) std_usage_val_sg_cnt,
           sum (act.actual_val_b * job.sec_conversion_rate) actual_val_sg,
           count (act.actual_val_b * job.sec_conversion_rate) actual_val_sg_cnt,
           count(*) cnt
      FROM  OPI.OPI_DBI_RES_STD_F std,
            OPI.OPI_DBI_RES_ACTUAL_F act,
            OPI.OPI_DBI_JOBS_F job,
            FII.FII_TIME_DAY cal,
            eni_res_5_mv res
      WHERE  job.job_status_code in (7, 5, 12) -- 7 Cancelled, 5 CNC, 12 Closed
        AND job.job_id = std.job_id
        AND job.job_type <> 5
        AND job.job_type = std.job_type
        AND job.assembly_item_id = std.assembly_item_id
        AND job.organization_id = std.organization_id
        AND job.job_id = act.job_id
        AND job.job_type = act.job_type
        AND job.organization_id = act.organization_id
        AND job.assembly_item_id = act.assembly_item_id
        AND std.resource_id = act.resource_id
        AND ( (std.source = 1 and std.department_id = act.department_id)
             OR std.source = 2 OR std.source = 3)
        AND cal.report_date = trunc(job.completion_date)
        AND std.resource_id = res.resource_id
        AND ( (std.source = 1 and std.department_id   = res.department_id)
             OR std.source = 2 OR std.source = 3)
        AND std.organization_id = res.organization_id
      GROUP BY
            res.organization_id,
            grouping sets (   (res.resource_group, res.resource_group_fk ),
                              (res.department_id, res.department_fk),
                              (res.resource_group, res.resource_group_fk,
                               res.department_id, res.department_fk,
                               res.resource_id, res.ID),
                              () ),
            grouping sets (ent_qtr_id, ent_period_id,
                           week_id, report_date_julian)