with init_items
as
(
select /*+ materialize */
msib.segment1 item_number
,msib.description item_description
,msib.inventory_item_id
,moq.subinventory_code
,moq.locator_id
,msib.organization_id
,moq.lot_number as lot
,sum(moq.primary_transaction_quantity) on_hand
,sum(moq.secondary_transaction_quantity) secondary_onhand
from mtl_system_items_b msib
,mtl_onhand_quantities_detail moq
,mtl_parameters mp
where 1=1
and moq.inventory_item_id = msib.inventory_item_id
and moq.organization_id = msib.organization_id
and mp.organization_id = msib.organization_id
and moq.organization_id = moq.organization_id
group by msib.segment1
,msib.description
,msib.inventory_item_id
,moq.subinventory_code
,moq.locator_id
,msib.organization_id
,moq.lot_number
)
, items as
(
select /*+ materialize */
aa.*
from init_items aa
where not exists
(select *
from mtl_material_transactions mmt,mtl_transaction_types mtt
where aa.inventory_item_id = mmt.inventory_item_id
and aa.organization_id = mmt.organization_id
and mtt.transaction_type_id = mmt.transaction_type_id
and mmt.transaction_type_id not in (24)
and trunc(mmt.transaction_date) > trunc(sysdate) - :p_period
and mmt.organization_id = aa.organization_id )
)
, onhand_data as
(
select /*+ materialize */
aa.item_number
,aa.item_description
,haou.name organization_name
,aa.subinventory_code
,mic.category_concat_segs
,mc.description category_desc
,cv.item_cost
,aa.on_hand
,aa.on_hand * cv.item_cost as item_value
,aa.secondary_onhand
,aa.organization_id
,aa.inventory_item_id
,aa.locator_id
,inv_project.get_locator(aa.locator_id,aa.organization_id) as locator_one
,aa.lot
from items aa
,cst_item_cost_type_v cv
,hr_all_organization_units haou
,mtl_category_sets mcs
,mtl_item_categories_v mic
,mtl_categories_v mc
where cv.organization_id = aa.organization_id
and cv.inventory_item_id = aa.inventory_item_id
and 2=2
and cv.cost_type = 'Frozen'
and haou.organization_id = aa.organization_id
and mic.inventory_item_id = aa.inventory_item_id
and mic.organization_id = aa.organization_id
and mic.category_set_id = mcs.category_set_id
and mcs.category_set_name = 'Inv.Items'
and mic.enabled_flag = 'Y'
and mc.category_id = mic.category_id
order by 1
)
select aa.item_number
,aa.item_description
,aa.organization_name
,aa.subinventory_code
,aa.locator_id,aa.organization_id
,inv_project.get_locator(aa.locator_id,aa.organization_id) as locator
,aa.locator_one
,aa.lot
,aa.category_concat_segs
,aa.category_desc
,aa.item_cost
,aa.on_hand
,aa.item_value
,aa.secondary_onhand
,(select max(mmt.transaction_date)
from mtl_material_transactions mmt
where aa.inventory_item_id = mmt.inventory_item_id
and aa.organization_id = mmt.organization_id
and mmt.transaction_type_id not in (24)
) last_transacted_date
from onhand_data aa |
