SELECT ht.meaning hold_type,
hs.creation_date apply_date,
hr.creation_date release_date,
h.name hold_name,
hs.hold_entity_code object_type_code,
ot.meaning object_type,
hs.hold_entity_code2 object_type_code2,
ot2.meaning object_type2,
hs.hold_entity_id object_id,
hs.hold_entity_id2 object_id2,
hs.hold_until_date,
rr.meaning release_reason,
hs.released_flag
FROM oe_hold_sources hs,
oe_hold_releases hr,
oe_hold_definitions h,
oe_lookups ht,
oe_lookups ot,
oe_lookups ot2,
oe_lookups rr
WHERE hs.hold_id = h.hold_id
AND hs.hold_entity_code = ot.lookup_code
AND ot.lookup_type = 'HOLD_ENTITY_TYPE'
AND hs.hold_entity_code2 = ot2.lookup_code(+)
AND ot2.lookup_type(+) = 'HOLD_ENTITY_TYPE'
AND h.type_code = ht.lookup_code
AND ht.lookup_type = 'HOLD_TYPE'
AND hs.hold_source_id = hr.hold_source_id(+)
AND hr.release_reason_code = rr.lookup_code(+)
AND rr.lookup_type(+) = 'RELEASE_REASON'
AND (hs.hold_entity_code <> 'O'
OR (hs.hold_entity_code = 'O'
AND EXISTS
(SELECT 'x'
FROM oe_order_lines_all sl
WHERE sl.header_id = hs.hold_entity_id
AND sl.open_flag = 'Y')))
ORDER BY ht.meaning, hr.creation_date, hs.creation_date
