MATERIALIZED VIEW: APPS.ASO_BI_QOT_APRB_MV

Source

(SELECT /* 12.0: bug#4526784 */ 'NEWAPR' Umarker,
           Resource_Grp_id,
           Resource_Id,
           COUNT(*) open,
           COUNT(*) new,
           NULL complete,
           NULL approved,
           NULL days_for_approval,
           NULL approvers,
           NULL Approved_xcnt,
           NULL days_for_approval_xcnt,
           NULL approvers_xcnt,
           NULL complete_xcnt, 
           time.report_date_julian day_id,
           time.week_id,
           time.ent_period_id,
           time.ent_qtr_id,
           time.ent_year_id
    FROM
        ASO.ASO_BI_APR_F APR,
        FII.FII_TIME_DAY TIME
    WHERE apr.apr_start_date = time.report_date
    GROUP BY Resource_Grp_Id,
             Resource_Id,
             time.report_date_julian,
             time.week_id,
             time.ent_period_id,
             time.ent_qtr_id,
             time.ent_year_id
    UNION ALL
    SELECT  'CLSAPR' Umarker,
            Resource_grp_id,
            Resource_id,
            COUNT(*) open1,
            NULL new,
            SUM(DECODE(apr.qa_end_date,apr.apr_end_date,1,0)) complete,
            SUM(DECODE(approval_status,'APPR',1,0)) approved,
            SUM(apr_end_date - apr_start_date) days_for_approval,
            SUM(DECODE(apr.qa_end_date,apr.apr_end_date,num_approvers,0)) approvers,
            COUNT(DECODE(approval_status,'APPR',1,0)) Approved_xcnt,
            COUNT(apr_end_date - apr_start_date) days_for_approval_xcnt,
            COUNT(DECODE(apr.qa_end_date,apr.apr_end_date,num_approvers,0)) approvers_xcnt,
            COUNT(DECODE(apr.qa_end_date,apr.apr_end_date,1,0)) complete_xcnt,
            time.report_date_julian day_id,
            time.week_id,
            time.ent_period_id,
            time.ent_qtr_id,
            time.ent_year_id
    FROM
       ASO.ASO_BI_APR_F APR,
       FII.FII_TIME_DAY TIME
   WHERE
       apr.qa_end_date = time.report_date
   GROUP BY Resource_Grp_Id,
            Resource_Id,
   	    time.report_date_julian,
            time.week_id,
            time.ent_period_id,
            time.ent_qtr_id,
            time.ent_year_id)