Move Order details query in Oracle Apps R12

 

select mtrh.request_number,
mtrl.FROM_SUBINVENTORY_CODE,
mtrl.TO_SUBINVENTORY_CODE,
mtrl.QUANTITY Move_Order_Qty,
mtrl.inventory_item_id,
mtrl.organization_id,
MFG.MEANING MOVE_ORDER_TYPE_NAME ,
msib.planner_code reference_type,
msib.description,
misi.secondary_inventory,
misi.attribute1 WIP_loc,
mtrl.TRANSACTION_TYPE_ID,
moq.locator_id,
mil.concatenated_segments SOURCE_LOCATOR,
(select mil1.concatenated_segments
from mtl_item_locations_KFV mil1
where  msib.organization_id = mil1.organization_id
AND moq.locator_id = mil1.inventory_location_id(+)
AND mtrl.TO_SUBINVENTORY_CODE = mil1.subinventory_code) to_locator, 
moq.primary_transaction_quantity on_hand_quantity
from mtl_txn_request_headers mtrh,
mtl_txn_request_lines mtrl,
mtl_system_items_b msib,
mtl_item_sub_inventories misi,
MFG_LOOKUPS MFG,
mtl_onhand_quantities_detail moq,
mtl_item_locations_KFV mil
where mtrh.header_id=mtrl.header_id
AND mtrh.request_number = NVL(:P_MOVE_ORDER_NO,mtrh.request_number)
AND mtrl.organization_id=msib.organization_id
AND mtrl.inventory_item_id=msib.inventory_item_id
AND msib.inventory_item_id=misi.inventory_item_id
AND msib.organization_id=misi.organization_id
AND MFG.LOOKUP_TYPE = 'MOVE_ORDER_TYPE'
AND MFG.LOOKUP_CODE = MTRH.MOVE_ORDER_TYPE
AND msib.organization_id = moq.organization_id
AND msib.inventory_item_id = moq.inventory_item_id
AND msib.organization_id = mil.organization_id
AND moq.locator_id = mil.inventory_location_id(+)
AND mtrl.FROM_SUBINVENTORY_CODE = mil.subinventory_code
AND MFG.MEANING = :P_MOVE_ORDER_TYPE
				AND mtrl.FROM_SUBINVENTORY_CODE = NVL(:P_SOURCE_SUB_INV , mtrl.FROM_SUBINVENTORY_CODE)
				AND mtrl.TO_SUBINVENTORY_CODE = NVL(:P_TO_SUB_INV , MTRL.TO_SUBINVENTORY_CODE)
				AND mtrl.organization_id = :P_INV_ORG
				AND msib.segment1 = NVL(:P_ITEM , msib.segment1)
				AND msib.planner_code = NVL(:P_REFERENCE_TYPE, msib.planner_code)