ONT Hold Source Activity

Description
Categories: BI Publisher
Columns: Hold Type, Apply Date, Release Date, Hold Name, Object Type Code, Object Type, Object Type Code2, Object Type2, Object Id, Object Id2 ...
Application: Order Management
Source: Hold Source Activity Report (XML)
Short Name: OEXOHHSA_XML
DB package: ONT_OEXOHHSA_XMLP_PKG
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
Parameter Name SQL text Validation
Hold Type (From)
 
LOV Oracle
Hold Type (To)
 
LOV Oracle
Hold Name (From)
 
LOV Oracle
Hold Name (To)
 
LOV Oracle
Activity Date (From)
 
Date
Activity Date (To)
 
Date
Activity Type
 
LOV Oracle
Ledger Id
 
Number