SELECT /* 12.0: bug#4526784 */
fact.item_inv_org_id INV_ORG_ID,
fact.customer_id CUSTOMER_ID,
item.id ITEM_ID,
nvl(item.master_id,item.id) MASTER_ITEM_ID,
nvl(item.vbh_category_id, -1) ITEM_CATEGORY_ID,
grouping_id(fact.item_inv_org_id,
fact.customer_id,
item.id,fact.inv_uom_code,
nvl(item.vbh_category_id, -1),
nvl(item.master_id,item.id),
day.ent_qtr_id,
day.ent_period_id,
day.week_id,
day.report_date_julian) GRP_ID,
decode(grouping_id(nvl(item.vbh_category_id, -1),
nvl(item.master_id,item.id),
item.id),
1,4,3,1,7,3,0) 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,
sum(fact.booked_amt_g) BOOKED_AMT_G,
count(fact.booked_amt_g) BOOKED_AMT_G_CNT,
sum(fact.booked_amt_g1) BOOKED_AMT_G1,
count(fact.booked_amt_g1) BOOKED_AMT_G1_CNT,
sum(fact.booked_amt_f1) BOOKED_AMT_F,
count(fact.booked_amt_f1) BOOKED_AMT_F_CNT,
sum(fact.booked_qty_inv) BOOKED_QTY,
count(fact.booked_qty_inv) BOOKED_QTY_CNT,
sum(fact.booked_amt_g *
CASE WHEN (fact.open_flag='N' AND
fact.time_fulfilled_date_id is NULL)
THEN 0 ELSE 1 END) BOOKED_AMT2_G,
count(fact.booked_amt_g *
CASE WHEN (fact.open_flag='N' AND
fact.time_fulfilled_date_id is NULL)
THEN 0 ELSE 1 END) BOOKED_AMT2_G_CNT,
sum(fact.booked_amt_g1 *
CASE WHEN (fact.open_flag='N' AND
fact.time_fulfilled_date_id is NULL)
THEN 0 ELSE 1 END) BOOKED_AMT2_G1,
count(fact.booked_amt_g1 *
CASE WHEN (fact.open_flag='N' AND
fact.time_fulfilled_date_id is NULL)
THEN 0 ELSE 1 END) BOOKED_AMT2_G1_CNT,
sum(fact.booked_amt_f1 *
CASE WHEN (fact.open_flag='N' AND
fact.time_fulfilled_date_id is NULL)
THEN 0 ELSE 1 END) BOOKED_AMT2_F,
count(fact.booked_amt_f1 *
CASE WHEN (fact.open_flag='N' AND
fact.time_fulfilled_date_id is NULL)
THEN 0 ELSE 1 END) BOOKED_AMT2_F_CNT,
sum(fact.booked_qty_inv *
CASE WHEN (fact.open_flag='N' AND
fact.time_fulfilled_date_id is NULL)
THEN 0 ELSE 1 END) BOOKED_QTY2,
count(fact.booked_qty_inv *
CASE WHEN (fact.open_flag='N' AND
fact.time_fulfilled_date_id is NULL)
THEN 0 ELSE 1 END) BOOKED_QTY2_CNT,
fact.inv_uom_code UOM,
sum((CASE WHEN (fact.schedule_ship_date IS NOT NULL AND
fact.request_date IS NOT NULL AND
(fact.order_date_type_code = 'SHIP' or
fact.order_date_type_code IS NULL))
THEN (fact.schedule_ship_date - fact.booked_date)
ELSE 0 END) *
decode(fact.shippable_flag,'Y',1,0) *
decode(nvl(fact.ato_line_id,-1),-1,1,
decode(fact.item_type_code,'CONFIG',1,0))) SCHEDULED_DAYS,
count((CASE WHEN (fact.schedule_ship_date IS NOT NULL AND
fact.request_date IS NOT NULL AND
(fact.order_date_type_code = 'SHIP' or
fact.order_date_type_code IS NULL))
THEN (fact.schedule_ship_date - fact.booked_date)
ELSE 0 END) *
decode(fact.shippable_flag,'Y',1,0) *
decode(nvl(fact.ato_line_id,-1),-1,1,
decode(fact.item_type_code,'CONFIG',1,0))) SCHEDULED_DAYS_CNT,
sum((CASE WHEN (fact.schedule_ship_date IS NOT NULL AND
fact.request_date IS NOT NULL AND
(fact.order_date_type_code = 'SHIP' or
fact.order_date_type_code IS NULL))
THEN (fact.request_date - fact.booked_date)
ELSE 0 END) *
decode(fact.shippable_flag,'Y',1,0) *
decode(nvl(fact.ato_line_id,-1),-1,1,
decode(fact.item_type_code,'CONFIG',1,0))) REQUESTED_DAYS,
count((CASE WHEN (fact.schedule_ship_date IS NOT NULL AND
fact.request_date IS NOT NULL AND
(fact.order_date_type_code = 'SHIP' or
fact.order_date_type_code IS NULL))
THEN (fact.request_date - fact.booked_date)
ELSE 0 END) *
decode(fact.shippable_flag,'Y',1,0) *
decode(nvl(fact.ato_line_id,-1),-1,1,
decode(fact.item_type_code,'CONFIG',1,0))) REQUESTED_DAYS_CNT,
sum((CASE WHEN (fact.schedule_ship_date IS NOT NULL AND
fact.request_date IS NOT NULL AND
(fact.order_date_type_code = 'SHIP' or
fact.order_date_type_code IS NULL))
THEN 1 ELSE 0 END) *
decode(fact.shippable_flag,'Y',1,0) *
decode(nvl(fact.ato_line_id,-1),-1,1,
decode(fact.item_type_code,'CONFIG',1,0))) BOOKED_LINE_CNT,
count((CASE WHEN (fact.schedule_ship_date IS NOT NULL AND
fact.request_date IS NOT NULL AND
(fact.order_date_type_code = 'SHIP' or
fact.order_date_type_code IS NULL))
THEN 1 ELSE 0 END) *
decode(fact.shippable_flag,'Y',1,0) *
decode(nvl(fact.ato_line_id,-1),-1,1,
decode(fact.item_type_code,'CONFIG',1,0))) BOOKED_LINE_CNT_C,
count(*) CNT
FROM ISC.ISC_BOOK_SUM2_F fact,
ENI.ENI_OLTP_ITEM_STAR item,
FII.FII_TIME_DAY day
WHERE fact.time_booked_date_id = day.report_date
AND fact.inventory_item_id = item.inventory_item_id
AND fact.item_inv_org_id = item.organization_id
AND fact.line_category_code <> 'RETURN'
AND fact.item_type_code <> 'SERVICE'
AND fact.order_source_id <> 10
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 rollup(fact.customer_id),
grouping sets((fact.item_inv_org_id,
nvl(item.vbh_category_id,-1),
nvl(item.master_id,item.id),
item.id,
fact.inv_uom_code),
(nvl(item.master_id,item.id),
nvl(item.vbh_category_id,-1)),
(fact.item_inv_org_id,
nvl(item.vbh_category_id,-1)),
fact.item_inv_org_id),
grouping sets(day.ent_qtr_id,
day.ent_period_id,
day.week_id,
day.report_date_julian)
[Home] [Help]