MATERIALIZED VIEW: APPS.POA_NEG_001_MV

Source

SELECT /* 12.0: bug#4526784 */
 grouping_id(f.org_id,
             f.commodity_id,
             f.base_uom,
             f.supplier_id,
             f.supplier_site_id,
             f.category_id,
             f.po_item_id,
             f.negotiation_creator_id,
             f.doctype_id,
             f.ent_qtr_id,
             f.ent_period_id,
             f.week_id,
             f.day_id) grp_id,
 decode(grouping_id(f.ent_qtr_id,f.ent_period_id,f.week_id,f.day_id), 0,f.day_id,1, f.week_id, 3,f.ent_period_id,7,f.ent_qtr_id) time_id,
 grouping_id(f.category_id, f.supplier_id, f.po_item_id) aggregation_level,
 f.org_id,
 f.commodity_id,
 f.category_id,
 f.supplier_id,
 f.supplier_site_id,
 f.po_item_id,
 f.negotiation_creator_id,
 f.base_uom,
 f.doctype_id,
 f.ent_qtr_id,
 f.ent_period_id,
 f.week_id,
 f.day_id,
 sum(f.award_qty) award_qty,
 sum(f.award_amount_b) award_amt_b,
 sum(f.award_amount_g) award_amt_g,
 sum(f.award_amount_sg) award_amt_sg,
 sum(f.current_amount_b) current_amt_b,
 sum(f.current_amount_g) current_amt_g,
 sum(f.current_amount_sg) current_amt_sg,
 sum(f.current_amount_b - f.award_amount_b) proj_savings_amt_b,
 sum(f.current_amount_g - f.award_amount_g) proj_savings_amt_g,
 sum(f.current_amount_sg - f.award_amount_sg) proj_savings_amt_sg,
 sum(
  case when f.supplier_id is null then
        (f.publish_date - f.auction_creation_date)
  else
        decode(sign((f.publish_date - f.supplier_invite_date)), -1, 0, (f.publish_date - f.auction_creation_date))
  end
    ) preparation_time,
 sum(
  case when f.supplier_id is null then
        (f.close_bidding_date - f.publish_date)
  else
        decode(sign((f.publish_date - f.supplier_invite_date)), -1, (f.close_bidding_date - f.supplier_invite_date) , (f.close_bidding_date - f.publish_date))
  end
    ) bidding_time,
  sum(decode(sign(nvl(f.next_round_creation_date,f.close_bidding_date) - f.close_bidding_date), -1, 0, (nvl(f.next_round_creation_date,f.close_bidding_date) - f.close_bidding_date))) analysis_time,
  sum(nvl((nvl(f.rfi_complete_date, f.award_complete_date) - f.close_bidding_date),0)) award_time,
  sum(case when f.umarker = 2 and f.award_status <> 'NO' then 1 else null end) negotiated_lines,
  sum(case when f.umarker = 2 and f.award_status <> 'NO' and f.current_amount_b is not null then 1 else null end) neg_lines_with_cp,
  count(f.award_qty) c_award_qty,
  count(f.award_amount_b) c_award_amt_b,
  count(f.award_amount_g) c_award_amt_g,
  count(f.award_amount_sg) c_award_amt_sg,
  count(f.current_amount_b) c_current_amt_b,
  count(f.current_amount_g) c_current_amt_g,
  count(f.current_amount_sg) c_current_amt_sg,
  count(f.current_amount_b - f.award_amount_b) c_proj_savings_amt_b,
  count(f.current_amount_g - f.award_amount_g) c_proj_savings_amt_g,
  count(f.current_amount_sg - f.award_amount_sg) c_proj_savings_amt_sg,
  count(
  case when f.supplier_id is null then
        (f.publish_date - f.auction_creation_date)
  else
        decode(sign((f.publish_date - f.supplier_invite_date)), -1, 0, (f.publish_date - f.auction_creation_date))
  end
    ) c_preparation_time,
  count(
  case when f.supplier_id is null then
        (f.close_bidding_date - f.publish_date)
  else
        decode(sign((f.publish_date - f.supplier_invite_date)), -1, (f.close_bidding_date - f.supplier_invite_date) , (f.close_bidding_date - f.publish_date))
  end
    ) c_bidding_time,
  count(nvl((nvl(f.rfi_complete_date, f.award_complete_date) - f.close_bidding_date),0)) c_award_time,
  count(decode(sign(nvl(f.next_round_creation_date,f.close_bidding_date) - f.close_bidding_date), -1, 0, (nvl(f.next_round_creation_date,f.close_bidding_date) - f.close_bidding_date))) c_analysis_time,
  count(case when f.umarker = 2 and f.award_status <> 'NO' then 1 else null end) c_negotiated_lines,
  count(case when f.umarker = 2 and f.award_status <> 'NO' and f.current_amount_b is not null then 1 else null end) c_neg_lines_with_cp,
  count(*) c_total
FROM
   poa_neg_000_mv f
WHERE
   f.umarker in (1,2)
GROUP BY
  f.org_id,
  f.commodity_id,
  f.ent_qtr_id,
  rollup(f.category_id,
         f.supplier_id,
	(f.po_item_id, f.supplier_site_id, f.negotiation_creator_id, f.base_uom, f.doctype_id)),
  rollup(f.ent_period_id, f.week_id, f.day_id)