select
'BY SUBINVENTORY' subinventory_type,
mos.subinventory_code wrh,
mos.padded_concatenated_segments item,
mos.item_description description,
mos.primary_uom_code unit,
nvl(total_qoh, 0) inventory_qty,
mtb.attribute11 project_code,
mos.inventory_item_id
from mtl_system_items_b mtb,
mtl_onhand_sub_v mos
where (:p_from_subinventory is null or mos.subinventory_code >= :p_from_subinventory)
and (:p_to_subinventory is null or mos.subinventory_code <= :p_to_subinventory)
and nvl(mos.total_qoh,0) <> 0
and mtb.inventory_item_id = mos.inventory_item_id
and mtb.organization_id = mos.organization_id
and mtb.organization_id = :p_organization_id
and mos.subinventory_code in (select subinv
from suga_subinv
where type = :p_org_type)
union
select
'BY TRANX' subinventory_type,
mtt.subinventory_code wrh,
max(mtb.segment1) item,
max(mtb.description) description,
max(mtb.primary_uom_code) unit,
sum(nvl(mtt.primary_quantity,0)) inventory_qty,
max(mtb.attribute11) project_code,
mtb.inventory_item_id
from mtl_material_transactions mtt,
mtl_system_items_b mtb
where mtt.inventory_item_id = mtb.inventory_item_id
and mtb.organization_id = :p_organization_id
and mtb.organization_id = mtt.organization_id
and trunc(mtt.transaction_date) > :p_as_of_date
and (:p_from_subinventory is null or mtt.subinventory_code >= :p_from_subinventory)
and (:p_to_subinventory is null or mtt.subinventory_code <= :p_to_subinventory)
and not exists (select 1
from mtl_onhand_sub_v mov
where mov.inventory_item_id = mtb.inventory_item_id
and mov.subinventory_code = mtt.subinventory_code)
and mtt.subinventory_code in (select subinv
from suga_subinv
where type = :p_org_type)
group by
mtt.subinventory_code,
mtb.inventory_item_id
having sum(nvl(mtt.primary_quantity,0)) <> 0 希望让执行速度快点
'BY SUBINVENTORY' subinventory_type,
mos.subinventory_code wrh,
mos.padded_concatenated_segments item,
mos.item_description description,
mos.primary_uom_code unit,
nvl(total_qoh, 0) inventory_qty,
mtb.attribute11 project_code,
mos.inventory_item_id
from mtl_system_items_b mtb,
mtl_onhand_sub_v mos
where (:p_from_subinventory is null or mos.subinventory_code >= :p_from_subinventory)
and (:p_to_subinventory is null or mos.subinventory_code <= :p_to_subinventory)
and nvl(mos.total_qoh,0) <> 0
and mtb.inventory_item_id = mos.inventory_item_id
and mtb.organization_id = mos.organization_id
and mtb.organization_id = :p_organization_id
and mos.subinventory_code in (select subinv
from suga_subinv
where type = :p_org_type)
union
select
'BY TRANX' subinventory_type,
mtt.subinventory_code wrh,
max(mtb.segment1) item,
max(mtb.description) description,
max(mtb.primary_uom_code) unit,
sum(nvl(mtt.primary_quantity,0)) inventory_qty,
max(mtb.attribute11) project_code,
mtb.inventory_item_id
from mtl_material_transactions mtt,
mtl_system_items_b mtb
where mtt.inventory_item_id = mtb.inventory_item_id
and mtb.organization_id = :p_organization_id
and mtb.organization_id = mtt.organization_id
and trunc(mtt.transaction_date) > :p_as_of_date
and (:p_from_subinventory is null or mtt.subinventory_code >= :p_from_subinventory)
and (:p_to_subinventory is null or mtt.subinventory_code <= :p_to_subinventory)
and not exists (select 1
from mtl_onhand_sub_v mov
where mov.inventory_item_id = mtb.inventory_item_id
and mov.subinventory_code = mtt.subinventory_code)
and mtt.subinventory_code in (select subinv
from suga_subinv
where type = :p_org_type)
group by
mtt.subinventory_code,
mtb.inventory_item_id
having sum(nvl(mtt.primary_quantity,0)) <> 0 希望让执行速度快点
解决方案 »
- 未接触过 Oracle 的菜鸟提个问!
- 大家好,问大家一时间段查询的问题,谢谢大家了
- oralce 统计查询,请高手指点.
- 用delphi的dbexpress中的SQLStoredProc1调用oracle的存储过程老出现list index out of bound(0)?
- 怎样获取oracle服务器中的所有数据库名
- 问个很菜的问题"Oracle executables" 这是什么意思啊
- 高手帮忙啊:insert date类型的数据
- 求 :最快的速度把一个表中的相关记录给删除?
- 漫谈数据库的中庸思想
- 请问谁有数据库表设计规范,,,,急
- 关于oracle数据库insert执行慢
- 再问个这种存储过程当有记录返回记录没记录就返回-1,一定要存储过程的,谢谢
trunc(mtt.transaction_date) > :p_as_of_date
使用函数了将会造成基于时间字段的索引无效,如果有的话