SCHUB Performance test

select
count(*) count,
nvl2(ooha.quote_number,'not null',null) ooha_quote_number,
case when oola.quantity<0 then '<0' when oola.quantity>0 then '>0' else '0' end quantity_check,
oola.line_category_code,
oola.open_flag,
nvl2(oola.schedule_ship_date,'not null',null) schedule_ship_date,
nvl2(oola.demand_source_line,'not null',null) demand_source_line,
oola.flow_status_code
from
(
select
oola.header_id,
nvl(case when oola.order_quantity_uom<>msib.primary_uom_code and oola.ordered_quantity<>0 then
inv_convert.inv_um_convert(
item_id => oola.inventory_item_id,
precision => 9,
from_quantity => oola.ordered_quantity,
from_unit => oola.order_quantity_uom,
to_unit => msib.primary_uom_code,
from_name => null,
to_name => null
) else oola.ordered_quantity end,0)-
nvl((select sum(md.primary_uom_quantity) from mtl_demand md where to_char(oola.line_id)=md.demand_source_line and nvl(md.demand_source_type,2) in (2,8,12) and nvl(md.reservation_type,2) in (2,3)),0)-
nvl(case when oola.order_quantity_uom<>msib.primary_uom_code and oola.shipped_quantity<>0 then
inv_convert.inv_um_convert(
item_id => oola.inventory_item_id,
precision => 9,
from_quantity => oola.shipped_quantity,
from_unit => oola.order_quantity_uom,
to_unit => msib.primary_uom_code,
from_name => null,
to_name => null
) else oola.shipped_quantity end,0) quantity,
oola.line_category_code,
oola.open_flag,
nvl2(oola.schedule_ship_date,'not null',null) schedule_ship_date,
oola.flow_status_code,
md.demand_source_line
from
oe_order_lines_all oola,
mtl_system_items_b msib,
mtl_demand md
where
1=1 and
--msib.primary_uom_code<>oola.order_quantity_uom and
oola.ship_from_org_id=msib.organization_id and
oola.inventory_item_id=msib.inventory_item_id and
to_char(oola.line_id)=md.demand_source_line(+) and
md.demand_source_type(+)=2
) oola,
oe_order_headers_all ooha
where
oola.header_id=ooha.header_id(+)
group by
nvl2(ooha.quote_number,'not null',null),
case when oola.quantity<0 then '<0' when oola.quantity>0 then '>0' else '0' end,
oola.line_category_code,
oola.open_flag,
nvl2(oola.schedule_ship_date,'not null',null),
nvl2(oola.demand_source_line,'not null',null),
oola.flow_status_code
order by
oola.line_category_code,
oola.open_flag desc,
count(*) desc

select count(*) count, nvl2(ooha.quote_number,'not null',null) ooha_quote_number, case when oola.quantity<0 then '<0' when oola.quantity>0 then '>0' else '0' end quantity_check, oola.line_category_code, oola.open_flag, nvl2(oola.schedule_ship_date,'not null',null) schedule_ship_date, nvl2(oola.demand_source_line,'not null',null) demand_source_line, oola.flow_status_code from ( select oola.header_id, nvl(case when oola.order_quantity_uom<>msib.primary_uom_code and oola.ordered_quantity<>0 then inv_convert.inv_um_convert( item_id => oola.inventory_item_id, precision => 9, from_quantity => oola.ordered_quantity, from_unit => oola.order_quantity_uom, to_unit => msib.primary_uom_code, from_name => null, to_name => null ) else oola.ordered_quantity end,0)- nvl((select sum(md.primary_uom_quantity) from mtl_demand md where to_char(oola.line_id)=md.demand_source_line and nvl(md.demand_source_type,2) in (2,8,12) and nvl(md.reservation_type,2) in (2,3)),0)- nvl(case when oola.order_quantity_uom<>msib.primary_uom_code and oola.shipped_quantity<>0 then inv_convert.inv_um_convert( item_id => oola.inventory_item_id, precision => 9, from_quantity => oola.shipped_quantity, from_unit => oola.order_quantity_uom, to_unit => msib.primary_uom_code, from_name => null, to_name => null ) else oola.shipped_quantity end,0) quantity, oola.line_category_code, oola.open_flag, nvl2(oola.schedule_ship_date,'not null',null) schedule_ship_date, oola.flow_status_code, md.demand_source_line from oe_order_lines_all oola, mtl_system_items_b msib, mtl_demand md where 1=1 and --msib.primary_uom_code<>oola.order_quantity_uom and oola.ship_from_org_id=msib.organization_id and oola.inventory_item_id=msib.inventory_item_id and to_char(oola.line_id)=md.demand_source_line(+) and md.demand_source_type(+)=2 ) oola, oe_order_headers_all ooha where oola.header_id=ooha.header_id(+) group by nvl2(ooha.quote_number,'not null',null), case when oola.quantity<0 then '<0' when oola.quantity>0 then '>0' else '0' end, oola.line_category_code, oola.open_flag, nvl2(oola.schedule_ship_date,'not null',null), nvl2(oola.demand_source_line,'not null',null), oola.flow_status_code order by oola.line_category_code, oola.open_flag desc, count(*) desc