[Home] [Help]
MATERIALIZED VIEW: APPS.OPI_ONTIME_PROD_001_MV
Source
SELECT
fact.organization_id,
fact.source,
fact.inventory_item_id,
fact.uom_code,
fact.inventory_item_id||'-'||fact.organization_id item_org_id,
nvl (star.inv_category_id, -1) inv_category_id,
grouping_id (fact.organization_id,
fact.source,
fact.inventory_item_id,
fact.uom_code,
nvl (star.inv_category_id, -1),
cal.ent_qtr_id,
cal.ent_period_id,
cal.week_id,
cal.report_date_julian) grouping_id,
grouping_id (nvl (star.inv_category_id, -1),
fact.inventory_item_id) item_cat_flag,
decode (grouping_id (cal.ent_qtr_id, cal.ent_period_id,
cal.week_id, cal.report_date_julian),
14, cal.report_date_julian,
13, cal.week_id,
11, cal.ent_period_id,
7, cal.ent_qtr_id) time_id,
decode (grouping_id (cal.ent_qtr_id, cal.ent_period_id,
cal.week_id, cal.report_date_julian),
14, 1,
13, 16,
11, 32,
7, 64) period_type_id,
cal.ent_qtr_id,
cal.ent_period_id,
cal.week_id,
cal.report_date_julian,
count (case when
jobs.completion_date > jobs.scheduled_completion_date then 1 end)
late_prod_cnt,
sum (case when
jobs.completion_date > jobs.scheduled_completion_date then fact.completion_quantity else 0 end)
late_prod_qty,
count(case when
jobs.completion_date > jobs.scheduled_completion_date then fact.completion_quantity else 0 end)
late_prod_qty_cnt,
sum (case when
jobs.completion_date > jobs.scheduled_completion_date then fact.completion_value_b else 0 end)
late_prod_val_b,
count (case when
jobs.completion_date > jobs.scheduled_completion_date then fact.completion_value_b else 0 end)
late_prod_val_b_cnt,
sum (case when
jobs.completion_date > jobs.scheduled_completion_date then
(fact.completion_value_b*jobs.conversion_rate) else 0 end)
late_prod_val_g,
count (case when
jobs.completion_date > jobs.scheduled_completion_date then
(fact.completion_value_b*jobs.conversion_rate) else 0 end)
late_prod_val_g_cnt,
sum (case when
jobs.completion_date > jobs.scheduled_completion_date then
(fact.completion_value_b*jobs.sec_conversion_rate) else 0 end)
late_prod_val_sg,
count (case when
jobs.completion_date > jobs.scheduled_completion_date then
(fact.completion_value_b*jobs.sec_conversion_rate) else 0 end)
late_prod_val_sg_cnt,
count (case when
jobs.completion_date <= jobs.scheduled_completion_date then 1 end)
ontime_prod_cnt,
sum (case when
jobs.completion_date <= jobs.scheduled_completion_date then fact.completion_quantity else 0 end)
ontime_prod_qty,
count (case when
jobs.completion_date <= jobs.scheduled_completion_date then fact.completion_quantity else 0 end)
ontime_prod_qty_cnt,
sum (case when
jobs.completion_date <= jobs.scheduled_completion_date then fact.completion_value_b else 0 end)
ontime_prod_val_b,
count (case when
jobs.completion_date <= jobs.scheduled_completion_date then fact.completion_value_b else 0 end)
ontime_prod_val_b_cnt,
sum (case when
jobs.completion_date <= jobs.scheduled_completion_date then
(fact.completion_value_b*jobs.conversion_rate) else 0 end)
ontime_prod_val_g,
count (case when
jobs.completion_date <= jobs.scheduled_completion_date then
(fact.completion_value_b*jobs.conversion_rate) else 0 end)
ontime_prod_val_g_cnt,
sum (case when
jobs.completion_date <= jobs.scheduled_completion_date then
(fact.completion_value_b*jobs.sec_conversion_rate) else 0 end)
ontime_prod_val_sg,
count (case when
jobs.completion_date <= jobs.scheduled_completion_date then
(fact.completion_value_b*jobs.sec_conversion_rate) else 0 end)
ontime_prod_val_sg_cnt,
count(*) lines_cnt
FROM ENI.ENI_OLTP_ITEM_STAR star,
FII.FII_TIME_DAY cal,
OPI.OPI_DBI_WIP_COMP_F fact,
OPI.OPI_DBI_JOBS_F jobs
WHERE star.organization_id = fact.organization_id
AND star.inventory_item_id = fact.inventory_item_id
AND cal.report_date = fact.transaction_date
AND jobs.job_status_code IN (4,5,12)
AND jobs.line_type = 1
AND jobs.job_id = fact.job_id
AND jobs.organization_id = fact.organization_id
AND jobs.assembly_item_id = fact.inventory_item_id
GROUP BY
fact.organization_id,
fact.source,
rollup (nvl (star.inv_category_id, -1),
(fact.inventory_item_id, fact.uom_code)),
grouping sets (cal.ent_qtr_id, cal.ent_period_id,
cal.week_id, cal.report_date_julian)