INV Material Status Change History

SELECT	 msi.inventory_item_id
	,&p_item_flex_all c_lotitem_flexdata
	,mln.lot_number
	,mms.status_code
      	,mms.description
	,mms.enabled_flag
	,mmsh.creation_date cdate
	,to_char(mmsh.creation_date,'hh24:mi:ss') ctime
	,mmsh.initial_status_flag
	,decode(mmsh.from_mobile_apps_flag,'Y',:p_mob_apps
				        ,'N',:p_desk_apps
				              ,:p_desk_apps
					)  from_mobile_apps_flag
	,ml.meaning Update_Method
	,mtr.reason_name
	,decode(p.person_id,NULL,fu.user_name
			      ,p.last_name||','||first_name) full_name,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_lotitem_flexfield', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') CF_lotitem_flexfield
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 msi
	,(SELECT DISTINCT person_id, last_name, first_name FROM per_all_people_f) p
	,fnd_user fu
WHERE
 	mln.organization_id	 =  :p_org_id
  AND (mln.lot_number >= NVL (:p_lot_number_lo, mln.lot_number)
     AND mln.lot_number <= NVL (:p_lot_number_hi, mln.lot_number))
  and mln.inventory_item_id	 =  :p_lot_item_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
ORDER BY mmsh.creation_date