[Home] [Help]
MATERIALIZED VIEW: APPS.ISC_DR_SERVICE_CODE_MV
Source
select
/*+ use_hash(crs ftd fact eoi) */
/* 12.0: bug#4526784 */
fact.item_org_id item_org_id
, nvl(eoi.vbh_category_id,-1) product_category_id
, fact.repair_organization_id repair_organization_id
, fact.repair_type_id repair_type_id
, fact.customer_id customer_id
, crs.service_code_id service_code_id
, 31 - grouping_id( fact.item_org_id
, fact.customer_id
, nvl(eoi.vbh_category_id,-1)
, fact.repair_organization_id
, fact.repair_type_id
) aggregation_flag
, ftd.ent_qtr_id qtr_id
, ftd.ent_period_id month_id
, ftd.week_id week_id
, ftd.report_date_julian day_id
, decode( grouping_id( ftd.ent_qtr_id
, ftd.ent_period_id
, ftd.week_id
, ftd.report_date_julian
)
, 0, report_date_julian
, 1, week_id
, 3, ent_period_id
, 7, ent_qtr_id
) time_id
, decode( grouping_id( ftd.ent_qtr_id
, ftd.ent_period_id
, ftd.week_id
, ftd.report_date_julian
)
, 0, 1
, 1, 16
, 3, 32
, 7, 64
) period_type_id
, count(crs.repair_line_id) ro_count
, count(*) mv_count
, grouping_id( crs.service_code_id
, fact.item_org_id
, fact.customer_id
, nvl(eoi.vbh_category_id,-1)
, fact.repair_organization_id
, fact.repair_type_id
, ftd.ent_qtr_id
, ftd.ent_period_id
, ftd.week_id
, ftd.report_date_julian
) mv_grp_id
from
CSD.CSD_RO_SERVICE_CODES crs
, ISC.ISC_DR_REPAIR_ORDERS_F fact
, FII.FII_TIME_DAY ftd
, ENI.ENI_OLTP_ITEM_STAR eoi
where
crs.repair_line_id = fact.repair_line_id
and ftd.report_date = (trunc(crs.creation_date))
and fact.item_org_id = eoi.id
and fact.status in ('O','C')
and crs.applicable_flag = 'Y'
group by
crs.service_code_id
, fact.repair_organization_id
, fact.repair_type_id
, ftd.ent_qtr_id
, rollup( fact.customer_id )
, rollup( nvl(eoi.vbh_category_id,-1)
, fact.item_org_id
)
, rollup( ftd.ent_period_id
, ftd.week_id
, ftd.report_date_julian
)