[Home] [Help]
MATERIALIZED VIEW: APPS.POA_PQC_BS_MV
Source
select /* 12.0: bug#4526784 */ grouping_id(f.org_id,
f.ship_to_org_id,
f.supplier_id,
f.supplier_site_id,
f.commodity_id,
f.category_id,
f.po_item_id,
f.base_uom,
f.buyer_id,
f.ent_year_id,
ent_qtr_id,
ent_period_id,
week_id,
day_id) grp_id,
decode(grouping_id(f.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,f.ent_year_id) time_id,
decode( grouping_id(f.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,
grouping_id(f.supplier_id, f.po_item_id) aggregation_level,
f.org_id,
f.ship_to_org_id,
f.supplier_id,
f.supplier_site_id,
f.commodity_id, /* primary dim */
f.category_id,
f.po_item_id,
f.base_uom,
f.buyer_id,
f.ent_year_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(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 / decode(f.global_cur_conv_rate,0,1,f.global_cur_conv_rate) ) pbpcqcs_amt_b,
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 / decode(f.global_cur_conv_rate,0,1,f.global_cur_conv_rate) ) 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.order_type = 'QUANTITY'
AND f.consigned_code <> 1
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.complex_work_flag = 'N'
group by
f.org_id,
f.commodity_id,
rollup((f.supplier_id, f.category_id),
(f.base_uom, f.supplier_site_id,
f.po_item_id, f.ship_to_org_id,
f.buyer_id)),
rollup(ent_qtr_id,ent_period_id, week_id, day_id),
f.ent_year_id