Transfer order query

 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