[Home] [Help]
MATERIALIZED VIEW: APPS.POA_POD_001_MV
Source
SELECT /* 12.0: bug#4526784, bug 5248132 */ grouping_id(
org_id,
commodity_id,
base_uom,
supplier_id,
category_id,
po_item_id,
company_id,
cost_center_id,
contract_type,
ent_qtr_id,
ent_period_id,
week_id,
day_id
) grp_id,
decode(grouping_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) time_id,
grouping_id(category_id, supplier_id, po_item_id) aggregation_level,
f.org_id,
f.supplier_id,
f.category_id,
f.po_item_id,
f.company_id,
f.cost_center_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,
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(*) c_total,
f.commodity_id,
f.base_uom,
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,
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,
f.contract_type
FROM poa_pod_bs_j_mv f
WHERE consigned_code <> 1
and f.company_id is not null
and f.cost_center_id is not null
and f.shipment_type <> 'PREPAYMENT'
group by
grouping sets(
(org_id, commodity_id, category_id, /* 0 */
supplier_id,
po_item_id, base_uom,
contract_type, company_id,
cost_center_id),
(company_id, cost_center_id, commodity_id, category_id), /* 3 */
(company_id, cost_center_id, org_id, supplier_id), /* 5 */
(company_id, cost_center_id) /* 7 */
),
ent_qtr_id, rollup(ent_period_id, week_id, day_id)