[Home] [Help]
MATERIALIZED VIEW: APPS.OZF_INVENTORY_SUMMARY_MV
Source
select b.report_date_julian time_id,
1 period_type_id,
a.sold_to_party_id party_id,
a.inventory_item_id inventory_item_id,
a.primary_uom_code primary_uom,
sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
0 common_quantity_manual,
SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
ozf_time_day b
where a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'OM'
group by
b.report_date_julian,
1,
a.sold_to_party_id,
a.inventory_item_id,
a.primary_uom_code
union all
select b.week_id time_id,
16 period_type_id,
a.sold_to_party_id party_id,
a.inventory_item_id inventory_item_id,
a.primary_uom_code primary_uom,
sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
0 common_quantity_manual,
SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
ozf_time_day b
where a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'OM'
group by
b.week_id,
16,
a.sold_to_party_id,
a.inventory_item_id,
a.primary_uom_code
union all
select b.ent_period_id time_id,
32 period_type_id,
a.sold_to_party_id party_id,
a.inventory_item_id inventory_item_id,
a.primary_uom_code primary_uom,
sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
0 common_quantity_manual,
SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
ozf_time_day b
where a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'OM'
group by
b.ent_period_id,
32,
a.sold_to_party_id,
a.inventory_item_id,
a.primary_uom_code
union all
select b.ent_qtr_id time_id,
64 period_type_id,
a.sold_to_party_id party_id,
a.inventory_item_id inventory_item_id,
a.primary_uom_code primary_uom,
sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
0 common_quantity_manual,
SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
ozf_time_day b
where a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'OM'
group by
b.ent_qtr_id,
64,
a.sold_to_party_id,
a.inventory_item_id,
a.primary_uom_code
union all
select b.ent_year_id time_id,
128 period_type_id,
a.sold_to_party_id party_id,
a.inventory_item_id inventory_item_id,
a.primary_uom_code primary_uom,
sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
0 common_quantity_manual,
SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
ozf_time_day b
where a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'OM'
group by
b.ent_year_id,
128,
a.sold_to_party_id,
a.inventory_item_id,
a.primary_uom_code
union all
select b.report_date_julian time_id, -- 'IS data'
1 period_type_id,
a.sold_from_party_id party_id,
a.inventory_item_id inventory_item_id,
a.primary_uom_code primary_uom,
sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
0 common_quantity_manual,
SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
ozf_time_day b
where a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'IS'
group by
b.report_date_julian,
1,
a.sold_from_party_id,
a.inventory_item_id,
a.primary_uom_code
union all
select b.week_id time_id,
16 period_type_id,
a.sold_from_party_id party_id,
a.inventory_item_id inventory_item_id,
a.primary_uom_code primary_uom,
sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
0 common_quantity_manual,
SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
ozf_time_day b
where a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'IS'
group by
b.week_id,
16,
a.sold_from_party_id,
a.inventory_item_id,
a.primary_uom_code
union all
select b.ent_period_id time_id,
32 period_type_id,
a.sold_from_party_id party_id,
a.inventory_item_id inventory_item_id,
a.primary_uom_code primary_uom,
sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
0 common_quantity_manual,
SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
ozf_time_day b
where a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'IS'
group by
b.ent_period_id,
32,
a.sold_from_party_id,
a.inventory_item_id,
a.primary_uom_code
union all
select b.ent_qtr_id time_id,
64 period_type_id,
a.sold_from_party_id party_id,
a.inventory_item_id inventory_item_id,
a.primary_uom_code primary_uom,
sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
0 common_quantity_manual,
SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
ozf_time_day b
where a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'IS'
group by
b.ent_qtr_id,
64,
a.sold_from_party_id,
a.inventory_item_id,
a.primary_uom_code
union all
select b.ent_year_id time_id,
128 period_type_id,
a.sold_from_party_id party_id,
a.inventory_item_id inventory_item_id,
a.primary_uom_code primary_uom,
sum(decode(a.transfer_type, 'IN', nvl(a.common_quantity,0), 0)) common_quantity_in,
sum(decode(a.transfer_type, 'OUT', nvl(a.common_quantity,0), 0)) common_quantity_out,
0 common_quantity_manual,
SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
ozf_time_day b
where a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'IS'
group by
b.ent_year_id,
128,
a.sold_from_party_id,
a.inventory_item_id,
a.primary_uom_code
union all
select b.report_date_julian time_id, -- 'MA data'
1 period_type_id,
a.sold_to_party_id party_id,
a.inventory_item_id inventory_item_id,
a.primary_uom_code primary_uom,
0 common_quantity_in,
0 common_quantity_out,
sum(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.common_quantity, 0)) common_quantity_manual,
SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
ozf_time_day b
where a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'MA'
group by
b.report_date_julian,
1,
a.sold_to_party_id,
a.inventory_item_id,
a.primary_uom_code
union all
select b.week_id time_id,
16 period_type_id,
a.sold_to_party_id party_id,
a.inventory_item_id inventory_item_id,
a.primary_uom_code primary_uom,
0 common_quantity_in,
0 common_quantity_out,
sum(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.common_quantity, 0)) common_quantity_manual,
SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
ozf_time_day b
where a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'MA'
group by
b.week_id,
16,
a.sold_to_party_id,
a.inventory_item_id,
a.primary_uom_code
union all
select b.ent_period_id time_id,
32 period_type_id,
a.sold_to_party_id party_id,
a.inventory_item_id inventory_item_id,
a.primary_uom_code primary_uom,
0 common_quantity_in,
0 common_quantity_out,
sum(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.common_quantity, 0)) common_quantity_manual,
SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
ozf_time_day b
where a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'MA'
group by
b.ent_period_id,
32,
a.sold_to_party_id,
a.inventory_item_id,
a.primary_uom_code
union all
select b.ent_qtr_id time_id,
64 period_type_id,
a.sold_to_party_id party_id,
a.inventory_item_id inventory_item_id,
a.primary_uom_code primary_uom,
0 common_quantity_in,
0 common_quantity_out,
sum(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.common_quantity, 0)) common_quantity_manual,
SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
ozf_time_day b
where a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'MA'
group by
b.ent_qtr_id,
64,
a.sold_to_party_id,
a.inventory_item_id,
a.primary_uom_code
union all
select b.ent_year_id time_id,
128 period_type_id,
a.sold_to_party_id party_id,
a.inventory_item_id inventory_item_id,
a.primary_uom_code primary_uom,
0 common_quantity_in,
0 common_quantity_out,
sum(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.common_quantity, 0)) common_quantity_manual,
SUM(decode(a.transfer_type, 'IN', 1, 'OUT', -1, 0) * nvl(a.primary_quantity,0)) primary_quantity
from ozf_sales_transactions_all a,
ozf_time_day b
where a.transaction_date = b.report_date
and a.error_flag = 'N'
and source_code = 'MA'
group by
b.ent_year_id,
128,
a.sold_to_party_id,
a.inventory_item_id,
a.primary_uom_code