Query to get the status of the Transfer Order

SELECT DISTINCT
ITOH.HEADER_NUMBER "TRANSFER ORDER NUMBER",
HROU1.NAME "SOURCE ORGANIZATION",
ITOL.LINE_NUMBER "LINE NUMBER",
ITOL.STATUS_LOOKUP "LINE STATUS",
ITOL.REQUESTED_QTY "REQUESTED QTY",
NVL(ITOL.SHIPPED_QTY, 0) "SHIPPED QTY",
NVL(ITOL.RECEIVED_QTY, 0) "RECEIVED QTY",
NVL(ITOL.DELIVERED_QTY, 0) "DELIVERED QTY",
ITOL.QTY_UOM_CODE "UOM",
CASE
WHEN ITOL.STATUS_LOOKUP='CLOSED' THEN 'Closed'
WHEN ITOL.STATUS_LOOKUP='CANCELED' THEN 'Canceled'
WHEN (ITOL.SHIPPED_QTY=ITOL.RECEIVED_QTY) THEN 'Partially Shipped and Partially Received'
WHEN NVL(ITOL.SHIPPED_QTY, 0)=0 THEN 'Awaiting Fulfillment'
WHEN (ITOL.REQUESTED_QTY-ITOL.SHIPPED_QTY)=0 THEN 'Fulfilled'
WHEN (ITOL.REQUESTED_QTY-ITOL.SHIPPED_QTY)>0 THEN 'Partially Shipped'
END
STATUS,
FLV.MEANING "INTERFACE STATUS",
ITOL.SOURCE_TYPE_LOOKUP,
HROU2.NAME "DESTINATION ORGANIZATION"

from
INV_TRANSFER_ORDER_HEADERS ITOH,
INV_TRANSFER_ORDER_LINES ITOL,
HR_ORGANIZATION_UNITS HROU1,
HR_ORGANIZATION_UNITS HROU2,
FND_LOOKUP_VALUES FLV

WHERE
ITOL.HEADER_ID=ITOH.HEADER_ID
AND FLV.LOOKUP_CODE=ITOL.INTERFACE_STATUS_LOOKUP
AND FLV.SOURCE_LANG='US'
AND HROU1.ORGANIZATION_ID=ITOL.SOURCE_ORGANIZATION_ID
AND HROU2.ORGANIZATION_ID=ITOL.DESTINATION_ORGANIZATION_ID
AND ITOL.STATUS_LOOKUP like :LOV||'%'

ORDER BY ITOH.HEADER_NUMBER, ITOL.LINE_NUMBER