Order & Hold Details

SELECT DISTINCT
OHA.ORDER_NUMBER,
HS.HOLD_ENTITY_CODE CREDIT_CHECK_CRITERIA_CODE,
FLV.MEANING CREDIT_CHECK_CRITERIA,
HD.NAME HOLD_NAME,
HD.TYPE_CODE HOLD_TYPE,
HR.RELEASE_REASON_CODE,
OH.RELEASED_FLAG,
HS.HOLD_ENTITY_ID,
OH.HOLD_RELEASE_ID,
OH.HEADER_ID,
HS.HOLD_SOURCE_ID
FROM
ONT.OE_HOLD_SOURCES_ALL HS,
ONT.OE_HOLD_DEFINITIONS HD,
ONT.OE_ORDER_HOLDS_ALL OH,
ONT.OE_ORDER_HEADERS_ALL OHA,
APPS.FND_LOOKUP_VALUES FLV,
ONT.OE_HOLD_RELEASES HR
WHERE
HS.ORG_ID = '8' -- ORG ID
AND HS.HOLD_ID = HD.HOLD_ID
AND OH.HOLD_SOURCE_ID = HS.HOLD_SOURCE_ID
AND OH.HEADER_ID = OHA.HEADER_ID
AND FLV.LOOKUP_TYPE = 'HOLD_ENTITY_DESC'
AND FLV.VIEW_APPLICATION_ID = 660 -- ORDER MANAGEMENT
AND FLV.LOOKUP_CODE = HS.HOLD_ENTITY_CODE
AND OH.HOLD_RELEASE_ID = HR.HOLD_RELEASE_ID(+)
-- AND OHA.ORDER_NUMBER = '14658144'
-- AND OH.RELEASED_FLAG = 'N'