MATERIALIZED VIEW: APPS.POA_REQ_002_MV

Source

SELECT /*+ FULL(FBKT) */ /* 12.0: bug#4526784 */
grouping_id(f.org_id,
	    f.category_id,
	    f.po_item_id,
	    f.supplier_id,
	    f.buyer_id,
	    f.ship_to_org_id,
	    f.requester_id,
	    f.base_uom,
            cal.ent_year_id,
	    cal.ent_qtr_id,
	    cal.ent_period_id,
	    cal.week_id,
	    cal.report_date_julian) grp_id,
decode(
       grouping_id(cal.ent_year_id, cal.ent_qtr_id, cal.ent_period_id,cal.week_id,cal.report_date_julian),
       0,cal.report_date_julian,
       1,cal.week_id,
       3,cal.ent_period_id,
       7,cal.ent_qtr_id,
       15,cal.ent_year_id) time_id,
decode(
       grouping_id(cal.ent_year_id, cal.ent_qtr_id, cal.ent_period_id,cal.week_id,cal.report_date_julian),
       0,1,
       1,16,
       3,32,
       7,64,
       15,128) period_type_id,
grouping_id(f.buyer_id, f.category_id, f.po_item_id) aggregation_level,
f.category_id,
f.po_item_id,
f.supplier_id,
f.buyer_id,
f.ship_to_org_id,
f.requester_id,
f.org_id,
f.base_uom base_uom,
cal.ent_year_id,
cal.ent_qtr_id,
cal.ent_period_id,
cal.week_id,
cal.report_date_julian day_id,
sum(f.line_amount_b) fulfilled_amt_b,
sum(f.line_amount_g) fulfilled_amt_g,
sum(f.line_amount_sg) fulfilled_amt_sg,
sum(case when f.po_creation_method = 'M' then f.line_amount_b end) fulfilled_man_amt_b,
sum(case when f.po_creation_method = 'M' then f.line_amount_g end) fulfilled_man_amt_g,
sum(case when f.po_creation_method = 'M' then f.line_amount_sg end) fulfilled_man_amt_sg,
sum(f.line_quantity) fulfilled_qty,
sum((greatest(0,f.req_fulfilled_date - f.req_approved_date)))  days_to_fulfill,
count(f.line_amount_b) fulfilled_cnt_b,
count(f.line_amount_g) fulfilled_cnt_g,
count(f.line_amount_sg) fulfilled_cnt_sg,
count(case when f.po_creation_method = 'M' then f.line_amount_b end) fulfilled_man_cnt_b,
count(case when f.po_creation_method = 'M' then f.line_amount_g end) fulfilled_man_cnt_g,
count(case when f.po_creation_method = 'M' then f.line_amount_sg end) fulfilled_man_cnt_sg,
count(f.line_quantity) fulfilled_qty_cnt,
count((greatest(0,f.req_fulfilled_date - f.req_approved_date)))  days_to_fulfill_cnt,
count((case when (f.expected_date < f.req_fulfilled_date) then f.line_amount_g end)) fulfilled_ped_cnt,
sum(case when ((fb.range1_low is null or (greatest(0,f.req_fulfilled_date - f.req_approved_date)) >= fb.range1_low)
	   and (fb.range1_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range1_high))
     then f.line_amount_b
     else null end
) fulfilled_amt_b_age_b1,
sum(case when ((fb.range2_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range2_low)
	   and (fb.range2_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range2_high))
     then f.line_amount_b
     else null end
) fulfilled_amt_b_age_b2,
sum(case when ((fb.range3_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range3_low)
	   and (fb.range3_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range3_high))
     then f.line_amount_b
     else null end
) fulfilled_amt_b_age_b3,
sum(case when ((fb.range4_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range4_low)
	   and (fb.range4_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range4_high))
     then f.line_amount_b
     else null end
) fulfilled_amt_b_age_b4,
sum(case when ((fb.range5_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range5_low)
	   and (fb.range5_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range5_high))
     then f.line_amount_b
     else null end
) fulfilled_amt_b_age_b5,
sum(case when ((fb.range6_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range6_low)
	   and (fb.range6_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range6_high))
     then f.line_amount_b
     else null end
) fulfilled_amt_b_age_b6,
sum(case when ((fb.range7_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range7_low)
	   and (fb.range7_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range7_high))
     then f.line_amount_b
     else null end
) fulfilled_amt_b_age_b7,
sum(case when ((fb.range8_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range8_low)
	   and (fb.range8_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range8_high))
     then f.line_amount_b
     else null end
) fulfilled_amt_b_age_b8,
sum(case when ((fb.range9_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range9_low)
	   and (fb.range9_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range9_high))
     then f.line_amount_b
     else null end
) fulfilled_amt_b_age_b9,
sum(case when ((fb.range10_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range10_low)
	   and (fb.range10_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range10_high))
     then f.line_amount_b
     else null end
) fulfilled_amt_b_age_b10,
sum(case when ((fb.range1_low is null or (greatest(0,f.req_fulfilled_date - f.req_approved_date)) >= fb.range1_low)
	   and (fb.range1_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range1_high))
     then f.line_amount_g
     else null end
) fulfilled_amt_g_age_b1,
sum(case when ((fb.range2_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range2_low)
	   and (fb.range2_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range2_high))
     then f.line_amount_g
     else null end
) fulfilled_amt_g_age_b2,
sum(case when ((fb.range3_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range3_low)
	   and (fb.range3_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range3_high))
     then f.line_amount_g
     else null end
) fulfilled_amt_g_age_b3,
sum(case when ((fb.range4_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range4_low)
	   and (fb.range4_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range4_high))
     then f.line_amount_g
     else null end
) fulfilled_amt_g_age_b4,
sum(case when ((fb.range5_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range5_low)
	   and (fb.range5_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range5_high))
     then f.line_amount_g
     else null end
) fulfilled_amt_g_age_b5,
sum(case when ((fb.range6_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range6_low)
	   and (fb.range6_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range6_high))
     then f.line_amount_g
     else null end
) fulfilled_amt_g_age_b6,
sum(case when ((fb.range7_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range7_low)
	   and (fb.range7_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range7_high))
     then f.line_amount_g
     else null end
) fulfilled_amt_g_age_b7,
sum(case when ((fb.range8_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range8_low)
	   and (fb.range8_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range8_high))
     then f.line_amount_g
     else null end
) fulfilled_amt_g_age_b8,
sum(case when ((fb.range9_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range9_low)
	   and (fb.range9_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range9_high))
     then f.line_amount_g
     else null end
) fulfilled_amt_g_age_b9,
sum(case when ((fb.range10_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range10_low)
	   and (fb.range10_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range10_high))
     then f.line_amount_g
     else null end
) fulfilled_amt_g_age_b10,
sum(case when ((fb.range1_low is null or (greatest(0,f.req_fulfilled_date - f.req_approved_date)) >= fb.range1_low)
	   and (fb.range1_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range1_high))
     then f.line_amount_sg
     else null end
) fulfilled_amt_sg_age_b1,
sum(case when ((fb.range2_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range2_low)
	   and (fb.range2_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range2_high))
     then f.line_amount_sg
     else null end
) fulfilled_amt_sg_age_b2,
sum(case when ((fb.range3_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range3_low)
	   and (fb.range3_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range3_high))
     then f.line_amount_sg
     else null end
) fulfilled_amt_sg_age_b3,
sum(case when ((fb.range4_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range4_low)
	   and (fb.range4_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range4_high))
     then f.line_amount_sg
     else null end
) fulfilled_amt_sg_age_b4,
sum(case when ((fb.range5_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range5_low)
	   and (fb.range5_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range5_high))
     then f.line_amount_sg
     else null end
) fulfilled_amt_sg_age_b5,
sum(case when ((fb.range6_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range6_low)
	   and (fb.range6_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range6_high))
     then f.line_amount_sg
     else null end
) fulfilled_amt_sg_age_b6,
sum(case when ((fb.range7_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range7_low)
	   and (fb.range7_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range7_high))
     then f.line_amount_sg
     else null end
) fulfilled_amt_sg_age_b7,
sum(case when ((fb.range8_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range8_low)
	   and (fb.range8_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range8_high))
     then f.line_amount_sg
     else null end
) fulfilled_amt_sg_age_b8,
sum(case when ((fb.range9_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range9_low)
	   and (fb.range9_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range9_high))
     then f.line_amount_sg
     else null end
) fulfilled_amt_sg_age_b9,
sum(case when ((fb.range10_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range10_low)
	   and (fb.range10_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range10_high))
     then f.line_amount_sg
     else null end
) fulfilled_amt_sg_age_b10,
count(case when ((fb.range1_low is null or (greatest(0,f.req_fulfilled_date - f.req_approved_date)) >= fb.range1_low)
	   and (fb.range1_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range1_high))
     then f.line_amount_b
     else null end
) fulfilled_cnt_b_age_b1,
count(case when ((fb.range2_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range2_low)
	   and (fb.range2_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range2_high))
     then f.line_amount_b
     else null end
) fulfilled_cnt_b_age_b2,
count(case when ((fb.range3_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range3_low)
	   and (fb.range3_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range3_high))
     then f.line_amount_b
     else null end
) fulfilled_cnt_b_age_b3,
count(case when ((fb.range4_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range4_low)
	   and (fb.range4_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range4_high))
     then f.line_amount_b
     else null end
) fulfilled_cnt_b_age_b4,
count(case when ((fb.range5_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range5_low)
	   and (fb.range5_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range5_high))
     then f.line_amount_b
     else null end
) fulfilled_cnt_b_age_b5,
count(case when ((fb.range6_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range6_low)
	   and (fb.range6_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range6_high))
     then f.line_amount_b
     else null end
) fulfilled_cnt_b_age_b6,
count(case when ((fb.range7_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range7_low)
	   and (fb.range7_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range7_high))
     then f.line_amount_b
     else null end
) fulfilled_cnt_b_age_b7,
count(case when ((fb.range8_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range8_low)
	   and (fb.range8_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range8_high))
     then f.line_amount_b
     else null end
) fulfilled_cnt_b_age_b8,
count(case when ((fb.range9_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range9_low)
	   and (fb.range9_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range9_high))
     then f.line_amount_b
     else null end
) fulfilled_cnt_b_age_b9,
count(case when ((fb.range10_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range10_low)
	   and (fb.range10_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range10_high))
     then f.line_amount_b
     else null end
) fulfilled_cnt_b_age_b10,
count(case when ((fb.range1_low is null or (greatest(0,f.req_fulfilled_date - f.req_approved_date)) >= fb.range1_low)
	   and (fb.range1_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range1_high))
     then f.line_amount_g
     else null end
) fulfilled_cnt_g_age_b1,
count(case when ((fb.range2_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range2_low)
	   and (fb.range2_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range2_high))
     then f.line_amount_g
     else null end
) fulfilled_cnt_g_age_b2,
count(case when ((fb.range3_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range3_low)
	   and (fb.range3_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range3_high))
     then f.line_amount_g
     else null end
) fulfilled_cnt_g_age_b3,
count(case when ((fb.range4_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range4_low)
	   and (fb.range4_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range4_high))
     then f.line_amount_g
     else null end
) fulfilled_cnt_g_age_b4,
count(case when ((fb.range5_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range5_low)
	   and (fb.range5_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range5_high))
     then f.line_amount_g
     else null end
) fulfilled_cnt_g_age_b5,
count(case when ((fb.range6_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range6_low)
	   and (fb.range6_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range6_high))
     then f.line_amount_g
     else null end
) fulfilled_cnt_g_age_b6,
count(case when ((fb.range7_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range7_low)
	   and (fb.range7_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range7_high))
     then f.line_amount_g
     else null end
) fulfilled_cnt_g_age_b7,
count(case when ((fb.range8_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range8_low)
	   and (fb.range8_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range8_high))
     then f.line_amount_g
     else null end
) fulfilled_cnt_g_age_b8,
count(case when ((fb.range9_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range9_low)
	   and (fb.range9_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range9_high))
     then f.line_amount_g
     else null end
) fulfilled_cnt_g_age_b9,
count(case when ((fb.range10_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range10_low)
	   and (fb.range10_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range10_high))
     then f.line_amount_g
     else null end
) fulfilled_cnt_g_age_b10,
count(case when ((fb.range1_low is null or (greatest(0,f.req_fulfilled_date - f.req_approved_date)) >= fb.range1_low)
	   and (fb.range1_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range1_high))
     then f.line_amount_sg
     else null end
) fulfilled_cnt_sg_age_b1,
count(case when ((fb.range2_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range2_low)
	   and (fb.range2_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range2_high))
     then f.line_amount_sg
     else null end
) fulfilled_cnt_sg_age_b2,
count(case when ((fb.range3_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range3_low)
	   and (fb.range3_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range3_high))
     then f.line_amount_sg
     else null end
) fulfilled_cnt_sg_age_b3,
count(case when ((fb.range4_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range4_low)
	   and (fb.range4_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range4_high))
     then f.line_amount_sg
     else null end
) fulfilled_cnt_sg_age_b4,
count(case when ((fb.range5_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range5_low)
	   and (fb.range5_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range5_high))
     then f.line_amount_sg
     else null end
) fulfilled_cnt_sg_age_b5,
count(case when ((fb.range6_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range6_low)
	   and (fb.range6_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range6_high))
     then f.line_amount_sg
     else null end
) fulfilled_cnt_sg_age_b6,
count(case when ((fb.range7_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range7_low)
	   and (fb.range7_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range7_high))
     then f.line_amount_sg
     else null end
) fulfilled_cnt_sg_age_b7,
count(case when ((fb.range8_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range8_low)
	   and (fb.range8_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range8_high))
     then f.line_amount_sg
     else null end
) fulfilled_cnt_sg_age_b8,
count(case when ((fb.range9_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range9_low)
	   and (fb.range9_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range9_high))
     then f.line_amount_sg
     else null end
) fulfilled_cnt_sg_age_b9,
count(case when ((fb.range10_low is null or (f.req_fulfilled_date - f.req_approved_date) >= fb.range10_low)
	   and (fb.range10_high is null or (f.req_fulfilled_date - f.req_approved_date) < fb.range10_high))
     then f.line_amount_sg
     else null end
) fulfilled_cnt_sg_age_b10,
count(*) c_total
from
FII.FII_TIME_DAY cal,
POA.POA_DBI_REQ_F f,
BIS.BIS_BUCKET fbkt,
BIS.BIS_BUCKET_CUSTOMIZATIONS fb
where
fbkt.short_name = 'POA_DBI_FR_BUCKET'
and fbkt.bucket_id = fb.bucket_id
and trunc(f.req_fulfilled_date) = cal.report_date
and f.include_in_ufr = 'Y'
group by
 f.org_id,
  rollup(f.buyer_id,
	 f.category_id,
	 (f.po_item_id, f.ship_to_org_id, f.supplier_id, f.requester_id, f.base_uom)),
  rollup(cal.ent_qtr_id, cal.ent_period_id, cal.week_id, cal.report_date_julian),
  cal.ent_year_id