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 希望让执行速度快点
解决方案 »
- 怎么提高like查询效率
- 数据迁移请大家帮忙看看,我想找出每个表的最后修改时间,这样我就可以把不经常用的表先导入过去
- Procedure中如何实现IN语句
- 一个ubuntu oracle数据库调用sqlplus执行.sql脚本的问题
- 求助:这种oracle统计查询语句如何写?
- 查看当前操作消耗资源的情况
- 怎样通过数据文件判断Oracle版本,以及如何恢复?
- 如何启动服务?急急急急急急急急!
- 那位高手用过Oracle的Advanced Queuing,能举一个例子吗?,用Q可以传输数据吗?(有分)
- 问题:如何删去一个表的主键 ~~~50
- 关于oracle数据库insert执行慢
- 再问个这种存储过程当有记录返回记录没记录就返回-1,一定要存储过程的,谢谢
trunc(mtt.transaction_date) > :p_as_of_date
使用函数了将会造成基于时间字段的索引无效,如果有的话