INV Material Status Change History

with operating_unit as(
select
hou.name operating_unit_name, 
ood.operating_unit operating_unit_id, 
ood.organization_code, 
ood.organization_name inventory_organization_name,
ood.organization_id inv_organization_id
from
hr_operating_units hou,
org_organization_definitions ood
where
1=1 and
hou.organization_id=ood.operating_unit
)
select
ou.operating_unit_name operating_unit,
ou.organization_code,
ou.inventory_organization_name organization_name,
'01 SUBINVENTORY' row_Level,
mss.secondary_inventory_name subinventory,
null item_code,
null lot_number,
null serial_number,
null location,
mms.status_code,
mms.description status_description,
mms.enabled_flag,
mmsh.creation_date,
to_char(mmsh.creation_date,'hh24:mi:ss') creation_time,
mmsh.initial_status_flag initial_status,
decode(mmsh.from_mobile_apps_flag,'Y',:p_mob_apps,'N',:p_desk_apps,:p_desk_apps) updated_from,
ml.meaning update_method,
mtr.reason_name reason,
decode(p.person_id,null,fu.user_name,p.last_name||','||first_name) username
from
mtl_secondary_inventories mss,
mtl_material_status_history mmsh,
mfg_lookups ml,
mtl_transaction_reasons mtr,
mtl_material_statuses_vl mms,
per_all_people_f p,
fnd_user fu,
operating_unit ou
where
(2=2 and 2=1) and 
mss.organization_id=ou.inv_organization_id and 
mmsh.organization_id=mss.organization_id and
mmsh.zone_code=mss.secondary_inventory_name and
mmsh.status_id=mms.status_id and
mmsh.locator_id is null and
mmsh.lot_number is null and
mmsh.serial_number is null and
mtr.reason_id(+)=mmsh.update_reason_id and
ml.lookup_code=mmsh.update_method and
ml.lookup_type='MTL_STATUS_UPDATE_METHOD' and
mmsh.created_by=fu.user_id and
fu.employee_id=p.person_id(+)
&l_date_range
union all
select
ou.operating_unit_name operating_unit,
ou.organization_code,
ou.inventory_organization_name organization_name,
'02 LOCATOR' row_Level,
mil.subinventory_code subinventory,
null item_code,
null lot_number,
null serial_number,
mil.concatenated_segments location,
mms.status_code,
mms.description status_description,
mms.enabled_flag,
mmsh.creation_date,
to_char(mmsh.creation_date,'hh24:mi:ss') creation_time,
mmsh.initial_status_flag initial_status,
decode(mmsh.from_mobile_apps_flag,'Y',:p_mob_apps,'N',:p_desk_apps,:p_desk_apps) updated_from,
ml.meaning update_method,
mtr.reason_name reason,
decode(p.person_id,null,fu.user_name,p.last_name||','||first_name) username
from
mtl_item_locations_kfv mil,
mtl_material_status_history mmsh,
mfg_lookups ml,
mtl_transaction_reasons mtr,
mtl_material_statuses_vl mms,
per_all_people_f p,
fnd_user fu,
operating_unit ou
where 
&p_loc_where and
&p_xml_where and
mil.organization_id=ou.inv_organization_id and
mmsh.organization_id=mil.organization_id and
mmsh.zone_code=mil.subinventory_code and
mmsh.locator_id=mil.inventory_location_id and
mmsh.status_id=mms.status_id and
mmsh.locator_id is not null and
mmsh.lot_number is null and
mmsh.serial_number is null and
mtr.reason_id(+)=mmsh.update_reason_id and
ml.lookup_code=mmsh.update_method and
ml.lookup_type='MTL_STATUS_UPDATE_METHOD'and
mmsh.created_by=fu.user_id and
fu.employee_id=p.person_id (+)
&l_date_range
union all
select
ou.operating_unit_name operating_unit,
ou.organization_code,
ou.inventory_organization_name organization_name,
'03 LOT NUMBER' row_level,
mmsh.zone_code subinventory,
msi.concatenated_segments item_code,
mln.lot_number,
null serial_number,
null location,
mms.status_code,
mms.description status_description,
mms.enabled_flag,
mmsh.creation_date,
to_char(mmsh.creation_date,'hh24:mi:ss') creation_time,
mmsh.initial_status_flag initial_status,
decode(mmsh.from_mobile_apps_flag,'Y',:p_mob_apps,'N',:p_desk_apps,:p_desk_apps) updated_from,
ml.meaning update_method,
mtr.reason_name reason,
decode(p.person_id,null,fu.user_name,p.last_name||','||first_name) username
from
mtl_lot_numbers mln,
mtl_material_status_history mmsh,
mfg_lookups ml,
mtl_transaction_reasons mtr,
mtl_material_statuses_vl mms,
mtl_system_items_kfv msi,
per_all_people_f p,
fnd_user fu,
operating_unit ou
where
(3=3 and 3=1) and
mln.organization_id=ou.inv_organization_id and
mln.inventory_item_id=msi.inventory_item_id and
mln.organization_id=msi.organization_id and
mmsh.organization_id=mln.organization_id and
mmsh.inventory_item_id=mln.inventory_item_id and
mmsh.lot_number=mln.lot_number and
mmsh.status_id=mms.status_id and
mmsh.serial_number is null and
mtr.reason_id(+)=mmsh.update_reason_id and
ml.lookup_code=mmsh.update_method and
ml.lookup_type='MTL_STATUS_UPDATE_METHOD' and
mmsh.created_by=fu.user_id and
fu.employee_id=p.person_id(+)
&l_date_range
union all
select
ou.operating_unit_name operating_unit,
ou.organization_code,
ou.inventory_organization_name organization_name,
'04 SERIAL NUMBER' row_level,
mmsh.zone_code subinventory,
msi.concatenated_segments item_code,
null lot_number,
msn.serial_number,
null location,
mms.status_code,
mms.description status_description,
mms.enabled_flag,
mmsh.creation_date,
to_char(mmsh.creation_date,'hh24:mi:ss') creation_time,
mmsh.initial_status_flag initial_status,
decode(mmsh.from_mobile_apps_flag,'Y',:p_mob_apps,'N',:p_desk_apps,:p_desk_apps) updated_from,
ml.meaning update_method,
mtr.reason_name reason,
decode(p.person_id,null,fu.user_name,p.last_name||','||first_name) username
from
mtl_serial_numbers msn,
mtl_material_status_history mmsh,
mfg_lookups ml,
mtl_transaction_reasons mtr,
mtl_material_statuses_vl mms,
mtl_system_items_kfv msi,
per_all_people_f p,
fnd_user fu,
operating_unit ou 
where 
(4=4 and 4=1) and
msn.current_organization_id=ou.inv_organization_id and
msn.inventory_item_id=msi.inventory_item_id and
msn.current_organization_id=msi.organization_id and
mmsh.inventory_item_id=msn.inventory_item_id and
mmsh.serial_number=msn.serial_number and
mmsh.status_id=mms.status_id and
mtr.reason_id(+)= mmsh.update_reason_id and
ml.lookup_code=mmsh.update_method and
ml.lookup_type='MTL_STATUS_UPDATE_METHOD' and
mmsh.created_by=fu.user_id and
fu.employee_id=p.person_id (+)
&l_date_range

with operating_unit as( select hou.name operating_unit_name, ood.operating_unit operating_unit_id, ood.organization_code, ood.organization_name inventory_organization_name, ood.organization_id inv_organization_id from hr_operating_units hou, org_organization_definitions ood where 1=1 and hou.organization_id=ood.operating_unit ) select ou.operating_unit_name operating_unit, ou.organization_code, ou.inventory_organization_name organization_name, '01 SUBINVENTORY' row_Level, mss.secondary_inventory_name subinventory, null item_code, null lot_number, null serial_number, null location, mms.status_code, mms.description status_description, mms.enabled_flag, mmsh.creation_date, to_char(mmsh.creation_date,'hh24:mi:ss') creation_time, mmsh.initial_status_flag initial_status, decode(mmsh.from_mobile_apps_flag,'Y',:p_mob_apps,'N',:p_desk_apps,:p_desk_apps) updated_from, ml.meaning update_method, mtr.reason_name reason, decode(p.person_id,null,fu.user_name,p.last_name||','||first_name) username from mtl_secondary_inventories mss, mtl_material_status_history mmsh, mfg_lookups ml, mtl_transaction_reasons mtr, mtl_material_statuses_vl mms, per_all_people_f p, fnd_user fu, operating_unit ou where (2=2 and 2=1) and mss.organization_id=ou.inv_organization_id and mmsh.organization_id=mss.organization_id and mmsh.zone_code=mss.secondary_inventory_name and mmsh.status_id=mms.status_id and mmsh.locator_id is null and mmsh.lot_number is null and mmsh.serial_number is null and mtr.reason_id(+)=mmsh.update_reason_id and ml.lookup_code=mmsh.update_method and ml.lookup_type='MTL_STATUS_UPDATE_METHOD' and mmsh.created_by=fu.user_id and fu.employee_id=p.person_id(+) &l_date_range union all select ou.operating_unit_name operating_unit, ou.organization_code, ou.inventory_organization_name organization_name, '02 LOCATOR' row_Level, mil.subinventory_code subinventory, null item_code, null lot_number, null serial_number, mil.concatenated_segments location, mms.status_code, mms.description status_description, mms.enabled_flag, mmsh.creation_date, to_char(mmsh.creation_date,'hh24:mi:ss') creation_time, mmsh.initial_status_flag initial_status, decode(mmsh.from_mobile_apps_flag,'Y',:p_mob_apps,'N',:p_desk_apps,:p_desk_apps) updated_from, ml.meaning update_method, mtr.reason_name reason, decode(p.person_id,null,fu.user_name,p.last_name||','||first_name) username from mtl_item_locations_kfv mil, mtl_material_status_history mmsh, mfg_lookups ml, mtl_transaction_reasons mtr, mtl_material_statuses_vl mms, per_all_people_f p, fnd_user fu, operating_unit ou where &p_loc_where and &p_xml_where and mil.organization_id=ou.inv_organization_id and mmsh.organization_id=mil.organization_id and mmsh.zone_code=mil.subinventory_code and mmsh.locator_id=mil.inventory_location_id and mmsh.status_id=mms.status_id and mmsh.locator_id is not null and mmsh.lot_number is null and mmsh.serial_number is null and mtr.reason_id(+)=mmsh.update_reason_id and ml.lookup_code=mmsh.update_method and ml.lookup_type='MTL_STATUS_UPDATE_METHOD'and mmsh.created_by=fu.user_id and fu.employee_id=p.person_id (+) &l_date_range union all select ou.operating_unit_name operating_unit, ou.organization_code, ou.inventory_organization_name organization_name, '03 LOT NUMBER' row_level, mmsh.zone_code subinventory, msi.concatenated_segments item_code, mln.lot_number, null serial_number, null location, mms.status_code, mms.description status_description, mms.enabled_flag, mmsh.creation_date, to_char(mmsh.creation_date,'hh24:mi:ss') creation_time, mmsh.initial_status_flag initial_status, decode(mmsh.from_mobile_apps_flag,'Y',:p_mob_apps,'N',:p_desk_apps,:p_desk_apps) updated_from, ml.meaning update_method, mtr.reason_name reason, decode(p.person_id,null,fu.user_name,p.last_name||','||first_name) username from mtl_lot_numbers mln, mtl_material_status_history mmsh, mfg_lookups ml, mtl_transaction_reasons mtr, mtl_material_statuses_vl mms, mtl_system_items_kfv msi, per_all_people_f p, fnd_user fu, operating_unit ou where (3=3 and 3=1) and mln.organization_id=ou.inv_organization_id and mln.inventory_item_id=msi.inventory_item_id and mln.organization_id=msi.organization_id and mmsh.organization_id=mln.organization_id and mmsh.inventory_item_id=mln.inventory_item_id and mmsh.lot_number=mln.lot_number and mmsh.status_id=mms.status_id and mmsh.serial_number is null and mtr.reason_id(+)=mmsh.update_reason_id and ml.lookup_code=mmsh.update_method and ml.lookup_type='MTL_STATUS_UPDATE_METHOD' and mmsh.created_by=fu.user_id and fu.employee_id=p.person_id(+) &l_date_range union all select ou.operating_unit_name operating_unit, ou.organization_code, ou.inventory_organization_name organization_name, '04 SERIAL NUMBER' row_level, mmsh.zone_code subinventory, msi.concatenated_segments item_code, null lot_number, msn.serial_number, null location, mms.status_code, mms.description status_description, mms.enabled_flag, mmsh.creation_date, to_char(mmsh.creation_date,'hh24:mi:ss') creation_time, mmsh.initial_status_flag initial_status, decode(mmsh.from_mobile_apps_flag,'Y',:p_mob_apps,'N',:p_desk_apps,:p_desk_apps) updated_from, ml.meaning update_method, mtr.reason_name reason, decode(p.person_id,null,fu.user_name,p.last_name||','||first_name) username from mtl_serial_numbers msn, mtl_material_status_history mmsh, mfg_lookups ml, mtl_transaction_reasons mtr, mtl_material_statuses_vl mms, mtl_system_items_kfv msi, per_all_people_f p, fnd_user fu, operating_unit ou where (4=4 and 4=1) and msn.current_organization_id=ou.inv_organization_id and msn.inventory_item_id=msi.inventory_item_id and msn.current_organization_id=msi.organization_id and mmsh.inventory_item_id=msn.inventory_item_id and mmsh.serial_number=msn.serial_number and mmsh.status_id=mms.status_id and mtr.reason_id(+)= mmsh.update_reason_id and ml.lookup_code=mmsh.update_method and ml.lookup_type='MTL_STATUS_UPDATE_METHOD' and mmsh.created_by=fu.user_id and fu.employee_id=p.person_id (+) &l_date_range