eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
[Home] [Help]
MATERIALIZED VIEW: APPS.POA_PQC_001_MV

Source


select /* 12.0: bug#4526784 */ grouping_id(f.org_id,
           f.supplier_id,
           f.commodity_id,
           f.category_id,
           f.po_item_id,
           f.base_uom,
           f.company_id,
           f.cost_center_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,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.supplier_id, f.category_id) aggregation_level,
  f.org_id,
  f.supplier_id,
  f.commodity_id,
  f.category_id,
  f.po_item_id,
  f.base_uom,
  f.company_id,
  f.cost_center_id,
  f.ent_qtr_id,
  f.ent_period_id,
  f.week_id,
  f.day_id,
  SUM(cip.purchase_amt_g/decode(cip.quantity,0,to_number(NULL),cip.quantity) * f.quantity ) pbcqco_amt_g,
  SUM(cip.purchase_amt_sg/decode(cip.quantity,0,to_number(NULL),cip.quantity) * f.quantity ) pbcqco_amt_sg,
  COUNT(cip.purchase_amt_g/decode(cip.quantity,0,to_number(NULL),cip.quantity) * f.quantity ) c_pbcqco_amt_g,
  COUNT(cip.purchase_amt_sg/decode(cip.quantity,0,to_number(NULL),cip.quantity) * f.quantity ) c_pbcqco_amt_sg,
  SUM(cip.purchase_amt_b/decode(cip.quantity,0,to_number(NULL),cip.quantity) * f.quantity ) pbcqco_amt_b,
  COUNT(cip.purchase_amt_b/decode(cip.quantity,0,to_number(NULL),cip.quantity) * f.quantity ) c_pbcqco_amt_b,
  SUM(Nvl(pip_amt_g/decode(pip_quantity,0,to_number(NULL),pip_quantity), cip.purchase_amt_g/decode(cip.quantity,0,to_number(NULL),cip.quantity)) * f.quantity) pbpcqco_amt_g,
  SUM(Nvl(pip_amt_sg/decode(pip_quantity,0,to_number(NULL),pip_quantity), cip.purchase_amt_sg/decode(cip.quantity,0,to_number(NULL),cip.quantity)) * f.quantity) pbpcqco_amt_sg,
  COUNT(Nvl(pip_amt_g/decode(pip_quantity,0,to_number(NULL),pip_quantity), cip.purchase_amt_g/decode(cip.quantity,0,to_number(NULL),cip.quantity)) * f.quantity ) c_pbpcqco_amt_g,
  COUNT(Nvl(pip_amt_sg/decode(pip_quantity,0,to_number(NULL),pip_quantity), cip.purchase_amt_sg/decode(cip.quantity,0,to_number(NULL),cip.quantity)) * f.quantity ) c_pbpcqco_amt_sg,
  SUM(Nvl(pip_amt_b/decode(pip_quantity,0,to_number(NULL),pip_quantity), cip.purchase_amt_b/decode(cip.quantity,0,to_number(NULL),cip.quantity)) * f.quantity) pbpcqco_amt_b,
  COUNT(Nvl(pip_amt_b/decode(pip_quantity,0,to_number(NULL),pip_quantity), cip.purchase_amt_b/decode(cip.quantity,0,to_number(NULL),cip.quantity)) * f.quantity ) c_pbpcqco_amt_b,
  SUM(Nvl(pisp_amt_g/decode(pisp_quantity,0,to_number(NULL),pisp_quantity), cisp.purchase_amt_g/decode(cisp.quantity,0,to_number(NULL),cisp.quantity)) * f.quantity ) pbpcqcs_amt_g,
  SUM(Nvl(pisp_amt_sg/decode(pisp_quantity,0,to_number(NULL),pisp_quantity), cisp.purchase_amt_sg/decode(cisp.quantity,0,to_number(NULL),cisp.quantity)) * f.quantity ) pbpcqcs_amt_sg,
  COUNT(Nvl(pisp_amt_g/decode(pisp_quantity,0,to_number(NULL),pisp_quantity), cisp.purchase_amt_g/decode(cisp.quantity,0,to_number(NULL),cisp.quantity)) * f.quantity ) c_pbpcqcs_amt_g,
  COUNT(Nvl(pisp_amt_sg/decode(pisp_quantity,0,to_number(NULL),pisp_quantity), cisp.purchase_amt_sg/decode(cisp.quantity,0,to_number(NULL),cisp.quantity)) * f.quantity ) c_pbpcqcs_amt_sg,
  SUM((1/ decode(f.global_cur_conv_rate,0,1,f.global_cur_conv_rate))*Nvl(pisp_amt_g/decode(pisp_quantity,0,to_number(NULL),pisp_quantity), cisp.purchase_amt_g/decode(cisp.quantity,0,to_number(NULL),cisp.quantity)) * f.quantity  ) pbpcqcs_amt_b,
  COUNT((1/ decode(f.global_cur_conv_rate,0,1,f.global_cur_conv_rate))*Nvl(pisp_amt_g/decode(pisp_quantity,0,to_number(NULL),pisp_quantity), cisp.purchase_amt_g/decode(cisp.quantity,0,to_number(NULL),cisp.quantity)) * f.quantity  ) c_pbpcqcs_amt_b,
  sum(f.purchase_amt_b) purchase_amt_b,
  COUNT(f.purchase_amt_b) c_purchase_amt_b,
  sum(f.purchase_amt_g) purchase_amt_g,
  sum(f.purchase_amt_sg) purchase_amt_sg,
  COUNT(f.purchase_amt_g) c_purchase_amt_g,
  COUNT(f.purchase_amt_sg) c_purchase_amt_sg,
  SUM(f.quantity) quantity,
  COUNT(f.quantity) c_quantity,
  count(*) c_total
  FROM     poa_pqc_bs_j2_mv f,
  poa_bm_item_o_mv cip,
  poa_bm_item_s_mv cisp
  where
  f.ent_year_id = cip.ent_year_id
  and f.company_id is not null
  and f.cost_center_id is not null
  AND f.org_id = cip.org_id
  and f.po_item_id = cip.po_item_id
  AND f.base_uom = cip.base_uom
  and f.ent_year_id = cisp.ent_year_id
  and f.po_item_id = cisp.po_item_id
  AND f.base_uom = cisp.base_uom
  and f.supplier_id = cisp.supplier_id
  and f.consigned_code <> 1
  and f.order_type = 'QUANTITY'
  and f.complex_work_flag = 'N'
  group by
  f.company_id, f.cost_center_id,
  grouping sets(
    (f.supplier_id, f.org_id), /* 1 */
    (f.commodity_id, f.category_id), /* 2 */
    (f.supplier_id, f.category_id, f.commodity_id, f.org_id, f.base_uom, f.po_item_id), /* 0 */
    () /* 3 */
 ),
 rollup(f.ent_period_id, f.week_id, f.day_id), f.ent_qtr_id