ONT Hold Source Activity

select  ht.meaning hold_type,
       hs.creation_date apply_date,
       hr1.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,
	ONT_OEXOHHSA_XMLP_PKG.c_entity_value(hs.hold_entity_code, hs.hold_entity_id) C_entity_value,
	ONT_OEXOHHSA_XMLP_PKG.c_entity_value2(hs.hold_entity_code2, hs.hold_entity_id2) C_entity_value2
from oe_hold_sources hs,
       oe_hold_releases hr1,
       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 = hr1.hold_source_id(+)
 and   hr1.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' )
           )
      )
 &lp_hold_type_where
 &lp_hold_where
 &lp_activity_date_where
 &lp_activity_type_where
order by ht.meaning, hr1.creation_date,hs.creation_date