[Home] [Help]
MATERIALIZED VIEW: APPS.ISC_DBI_CFM_005_MV
Source
SELECT /* 12.0: bug#4526784 */
fact.item_inv_org_id INV_ORG_ID,
fact.top_model_org_id TOP_MODEL_ORG_ID,
fact.customer_id CUSTOMER_ID,
(item.inventory_item_id||'-'||item.organization_id) ITEM_ID,
nvl(item.vbh_category_id, -1) ITEM_CATEGORY_ID,
grouping_id(decode(fact.line_category_code,'RETURN',1,0),
decode(fact.order_source_id,10,1,0),
fact.item_inv_org_id,
fact.customer_id,
(item.inventory_item_id||'-'||item.organization_id),
nvl(item.vbh_category_id, -1),
item.primary_uom_code,
fact.top_model_org_id,
day.ent_qtr_id,
day.ent_period_id,
day.week_id,
day.report_date_julian) GRP_ID,
grouping_id(nvl(item.vbh_category_id, -1),
(item.inventory_item_id||'-'||item.organization_id)) ITEM_CAT_FLAG,
grouping_id(fact.customer_id) CUSTOMER_FLAG,
decode(grouping_id(day.ent_qtr_id,
day.ent_period_id,
day.week_id,
day.report_date_julian),
14,day.report_date_julian,
13,day.week_id,
11,day.ent_period_id,
7,day.ent_qtr_id) TIME_ID,
decode(grouping_id(day.ent_qtr_id,
day.ent_period_id,
day.week_id,
day.report_date_julian),
14,1,13,16,11,32,7,64) PERIOD_TYPE_ID,
day.report_date_julian DAY_ID,
day.week_id WEEK_ID,
day.ent_period_id ENT_PERIOD_ID,
day.ent_qtr_id ENT_QTR_ID,
decode(fact.line_category_code,'RETURN',1,0) RETURN_FLAG,
decode(fact.order_source_id,10,1,0) INTERNAL_FLAG,
sum(fact.fulfilled_amt_g) FULFILLED_AMT_G,
count(fact.fulfilled_amt_g) FULFILLED_AMT_G_CNT,
sum(fact.fulfilled_amt_g1) FULFILLED_AMT_G1,
count(fact.fulfilled_amt_g1) FULFILLED_AMT_G1_CNT,
sum(fact.fulfilled_amt_f1) FULFILLED_AMT_F,
count(fact.fulfilled_amt_f1) FULFILLED_AMT_F_CNT,
sum(CASE WHEN (top_model_line_id = line_id OR
top_model_line_id IS NULL)
THEN fact.fulfilled_qty_inv ELSE NULL END) FULFILLED_QTY,
count(CASE WHEN (top_model_line_id = line_id OR
top_model_line_id IS NULL)
THEN fact.fulfilled_qty_inv ELSE NULL END) FULFILLED_QTY_CNT,
item.primary_uom_code UOM,
sum(fact.booked_qty_inv) BOOKED_QTY,
count(fact.booked_qty_inv) BOOKED_QTY_CNT,
sum(fact.booked_amt_g) BOOKED_AMT_G,
count(fact.booked_amt_g) BOOKED_AMT_G_CNT,
count(*) CNT
FROM ISC.ISC_BOOK_SUM2_F fact,
ENI.ENI_OLTP_ITEM_STAR item,
FII.FII_TIME_DAY day
WHERE fact.time_fulfilled_date_id = day.report_date
AND fact.item_id = item.inventory_item_id
AND fact.inv_org_id = item.organization_id
AND fact.item_type_code <> 'SERVICE'
AND fact.order_source_id <> 27
AND fact.ordered_quantity <> 0
AND fact.unit_selling_price <> 0
AND fact.charge_periodicity_code is NULL
GROUP BY decode(fact.line_category_code,'RETURN',1,0),
decode(fact.order_source_id,10,1,0),
fact.item_inv_org_id,
fact.top_model_org_id,
rollup(fact.customer_id),
rollup(nvl(item.vbh_category_id,-1),
((item.inventory_item_id||'-'||item.organization_id),item.primary_uom_code)),
grouping sets(day.ent_qtr_id,
day.ent_period_id,
day.week_id,
day.report_date_julian)