MATERIALIZED VIEW: APPS.POA_POD_BS_MV

Source

SELECT /* 12.0: bug#4526784 */
                             grouping_id(org_id,
			     commodity_id,
			     base_uom,
			     supplier_id,
			     supplier_site_id,
			     category_id,
			     po_item_id,
			     Buyer_id,
			     negotiation_creator_id,
			     doctype_id,
			     contract_type,
                             ent_year_id,
			     ent_qtr_id,
			     ent_period_id,
			     week_id,
			     day_id)
	grp_id,
	 decode(grouping_id(ent_year_id,ent_qtr_id,ent_period_id,week_id,day_id),
0,day_id,1, week_id, 3,ent_period_id,7,ent_qtr_id,15,ent_year_id) time_id,
         grouping_id(commodity_id,category_id,supplier_id,po_item_id) aggregation_level,
	 decode(grouping_id(ent_year_id,ent_qtr_id,ent_period_id,week_id,day_id),
0,1,1,16,3,32,7,64,15,128) period_type_id,
	 f.org_id,
	 f.supplier_id,
	 f.supplier_site_id,
	 f.category_id,
	 f.po_item_id,
	 f.buyer_id,
	 f.negotiation_creator_id,
	 f.doctype_id,
         f.ent_year_id,
	 f.ent_qtr_id,
	 f.ent_period_id,
	 f.week_id,
	 f.day_id,
	 sum(f.purchase_amt_b) purchase_amt_b,
	 sum(f.purchase_amt_g) purchase_amt_g,
	 sum(f.purchase_amt_sg) purchase_amt_sg,
	 sum(f.p_contract_amt_b) p_contract_amt_b,
	 sum(f.p_contract_amt_g) p_contract_amt_g,
	 sum(f.p_contract_amt_sg) p_contract_amt_sg,
	 sum(f.n_contract_amt_b) n_contract_amt_b,
	 sum(f.n_contract_amt_g) n_contract_amt_g,
	 sum(f.n_contract_amt_sg) n_contract_amt_sg,
	 sum(f.p_savings_amt_b) p_savings_amt_b,
	 sum(f.p_savings_amt_g) p_savings_amt_g,
	 sum(f.p_savings_amt_sg) p_savings_amt_sg,
	 sum(case when f.auction_header_id is not null then f.purchase_amt_b else null end) neg_purchase_amt_b,
	 sum(case when f.auction_header_id is not null then f.purchase_amt_g else null end) neg_purchase_amt_g,
	 sum(case when f.auction_header_id is not null then f.purchase_amt_sg else null end) neg_purchase_amt_sg,
	 sum(f.current_amt_b) current_amt_b,
	 sum(f.current_amt_g) current_amt_g,
 	 sum(f.current_amt_sg) current_amt_sg,
	 sum(case when f.auction_header_id is not null then (f.current_amt_b - f.purchase_amt_b) else null end) real_svngs_amt_b,
	 sum(case when f.auction_header_id is not null then (f.current_amt_g - f.purchase_amt_g) else null end) real_svngs_amt_g,
	 sum(case when f.auction_header_id is not null then (f.current_amt_sg - f.purchase_amt_sg) else null end) real_svngs_amt_sg,
	 sum(f.quantity) quantity,
	 sum(f.contract_amt_b) contract_amt_b,
	 sum(f.contract_amt_g) contract_amt_g,
	 sum(f.contract_amt_sg) contract_amt_sg,
	 sum(f.above_contract_amt_b) above_contract_amt_b,
	 sum(f.above_contract_amt_g) above_contract_amt_g,
	 sum(f.above_contract_amt_sg) above_contract_amt_sg,
	 sum(f.below_contract_amt_b) below_contract_amt_b,
	 sum(f.below_contract_amt_g) below_contract_amt_g,
	 sum(f.below_contract_amt_sg) below_contract_amt_sg,
	 sum(f.nz_quantity) nz_quantity,
	 count(f.purchase_amt_b) c_purchase_amt_b,
	 count(f.purchase_amt_g) c_purchase_amt_g,
	 count(f.purchase_amt_sg) c_purchase_amt_sg,
	 count(f.p_contract_amt_b) c_p_contract_amt_b,
	 count(f.p_contract_amt_g) c_p_contract_amt_g,
	 count(f.p_contract_amt_sg) c_p_contract_amt_sg,
	 count(f.n_contract_amt_b) c_n_contract_amt_b,
	 count(f.n_contract_amt_g) c_n_contract_amt_g,
	 count(f.n_contract_amt_sg) c_n_contract_amt_sg,
	 count(f.p_savings_amt_b) c_p_savings_amt_b,
	 count(f.p_savings_amt_g) c_p_savings_amt_g,
	 count(f.p_savings_amt_sg) c_p_savings_amt_sg,
	 count(case when f.auction_header_id is not null then f.purchase_amt_b else null end) c_neg_purchase_amt_b,
	 count(case when f.auction_header_id is not null then f.purchase_amt_g else null end) c_neg_purchase_amt_g,
	 count(case when f.auction_header_id is not null then f.purchase_amt_sg else null end) c_neg_purchase_amt_sg,
	 count(case when f.auction_header_id is not null then (f.current_amt_b - f.purchase_amt_b) else null end) c_real_svngs_amt_b,
	 count(case when f.auction_header_id is not null then (f.current_amt_g - f.purchase_amt_g) else null end) c_real_svngs_amt_g,
	 count(case when f.auction_header_id is not null then (f.current_amt_sg - f.purchase_amt_sg) else null end) c_real_svngs_amt_sg,
	 count(*) c_total,
	 f.commodity_id,
	 f.base_uom,
	 count(f.quantity) c_quantity,
	 count(f.contract_amt_b) c_contract_amt_b,
	 count(f.contract_amt_g) c_contract_amt_g,
	 count(f.contract_amt_sg) c_contract_amt_sg,
	 count(f.above_contract_amt_b) c_above_contract_amt_b,
	 count(f.above_contract_amt_g) c_above_contract_amt_g,
	 count(f.above_contract_amt_sg) c_above_contract_amt_sg,
	 count(f.below_contract_amt_b) c_below_contract_amt_b,
	 count(f.below_contract_amt_g) c_below_contract_amt_g,
	 count(f.below_contract_amt_sg) c_below_contract_amt_sg,
	 count(f.nz_quantity) c_nz_quantity,
	 count(f.current_amt_b) c_current_amt_b,
	 count(f.current_amt_g) c_current_amt_g,
 	 count(f.current_amt_sg) c_current_amt_sg,
  	 f.contract_type
FROM	 poa_pod_bs_j_mv f
WHERE consigned_code <> 1
and shipment_type <> 'PREPAYMENT'
group by
org_id,
grouping sets(
      (commodity_id, category_id,	      /* 0 */
	       supplier_id, supplier_site_id,
	        po_item_id, base_uom, buyer_id,
	       contract_type,negotiation_creator_id, doctype_id),
      (commodity_id,category_id,supplier_id), /* 1 */
      (commodity_id,category_id),	      /* 3 */
      (commodity_id),			      /* 7 */
      ()				      /* 15 */
),
ent_year_id, rollup(ent_qtr_id, ent_period_id, week_id, day_id)