MATERIALIZED VIEW: APPS.OPI_CURR_PROD_DEL_001_MV

Source

SELECT     
           fact.organization_id,
           fact.source,
           fact.assembly_item_id,
           fact.uom_code,
           fact.assembly_item_id||'-'||fact.organization_id item_org_id,
           nvl (star.inv_category_id, -1) inv_category_id,
           grouping_id (nvl (star.inv_category_id, -1),
                        fact.assembly_item_id) item_cat_flag,
          count(case when 
	     log.from_bound_date > fact.scheduled_completion_date then 1 end) 
	  late_jobs_cnt,
          sum(case when 
	     log.from_bound_date > fact.scheduled_completion_date then fact.start_quantity else 0 end)   
	  late_jobs_qty,
          sum(case when 
	     log.from_bound_date > fact.scheduled_completion_date then fact.job_start_value else 0 end) 
	  late_jobs_val_b,
          sum(case when 
	     log.from_bound_date > fact.scheduled_completion_date then (fact.job_start_value * conversion_rate) 
	     else 0 end) 
	  late_jobs_val_g,
	  sum(case when 
	     log.from_bound_date > fact.scheduled_completion_date then (fact.job_start_value * sec_conversion_rate) 
	     else 0 end) 
	  late_jobs_val_sg,
          count(distinct fact.job_id) open_jobs_cnt,
          sum(fact.start_quantity)  open_jobs_qty,
          sum(fact.job_start_value) open_jobs_val_b,
	  sum(fact.job_start_value * conversion_rate) open_jobs_val_g,
          sum(fact.job_start_value * sec_conversion_rate) open_jobs_val_sg
FROM
        OPI.OPI_DBI_JOBS_F fact,
        OPI.OPI_DBI_RUN_LOG_CURR log,
        ENI.ENI_OLTP_ITEM_STAR  star
WHERE       
         star.organization_id = fact.organization_id
   AND   star.inventory_item_id = fact.assembly_item_id
   AND   log.etl_id = 4
   AND   log.source = 2
   AND   fact.job_status_code in (3,6)
   AND   fact.line_type = 1
GROUP BY 
         fact.organization_id,
         fact.source,
         rollup (nvl (star.inv_category_id, -1),
                    (fact.assembly_item_id, fact.uom_code))