Query to find hold and release information of sales order

SELECT

    hou.name,

    ooha.org_id,

    ooha.order_number,

    ott.name                                                                        order_type,

    lkup3.meaning                                                                   order_status,

    hd.name                                                                         hold_name,

    lkup.meaning                                                                    hold_type,

    (

        SELECT

            flv.meaning

        FROM

            fnd_lookup_values flv

        WHERE

                1 = 1

            AND flv.lookup_code = hs.hold_entity_code

            AND flv.lookup_type = 'HOLD_ENTITY_DESC'

            AND flv.language = userenv('LANG')

            AND flv.view_application_id = 660

            AND flv.security_group_id = fnd_global.lookup_security_group(flv.lookup_type, flv.view_application_id)

    )                                                                               criteria,

    oe_holds_pvt.entity_id_value(hs.hold_entity_code, hs.hold_entity_id)            value,

    holds.creation_date                                                             hold_applied_date,

    hr.creation_date                                                                released_date,

    lkup2.meaning                                                                   release_reason,

    hr.release_comment

FROM

    oe_order_headers_all     ooha,

    hr_organization_units    hou,

    oe_order_holds_all       holds,

    oe_hold_sources_all      hs,

    oe_hold_definitions      hd,

    oe_hold_releases         hr,

    oe_transaction_types_tl  ott,

    oe_lookups               lkup,

    oe_lookups               lkup2,

    oe_lookups               lkup3

WHERE

        1 = 1

    AND ooha.header_id = holds.header_id

    AND ooha.org_id = hou.organization_id

    AND holds.hold_source_id = hs.hold_source_id

    AND hd.hold_id = hs.hold_id

    AND holds.hold_release_id = hr.hold_release_id(+)

    AND lkup.lookup_code = hd.type_code

    AND ooha.order_type_id = ott.transaction_type_id

    AND lkup.lookup_type = 'HOLD_TYPE'

    AND trunc(sysdate) BETWEEN nvl(lkup.start_date_active, trunc(sysdate)) AND nvl(lkup.end_date_active, trunc(sysdate))

    AND lkup2.lookup_type = 'RELEASE_REASON'

    AND hr.release_reason_code = lkup2.lookup_code (+)

    AND trunc(sysdate) BETWEEN nvl(lkup2.start_date_active, trunc(sysdate)) AND nvl(lkup2.end_date_active, trunc(sysdate))

    AND lkup3.lookup_type = 'FLOW_STATUS'

    AND lkup3.lookup_code = ooha.flow_status_code

    AND trunc(sysdate) BETWEEN nvl(lkup3.start_date_active, trunc(sysdate)) AND nvl(lkup3.end_date_active, trunc(sysdate))

    AND ooha.order_number IN ( '71669760', '71667130' );