Query to find hold and release information for sales orders

SELECT
    hou.name,
    hca.account_number     customer_number,
    hp.party_name          customer_name,
    ooha.header_id,
    ooha.order_number,
    ott.name               order_type,
    ooha.ordered_date,
    lkup3.meaning          order_status,
    hd.name                hold_name,
    lkup1.meaning          hold_type,
    holds.creation_date    hold_applied_date,
    fu.user_name           hold_applied_by,
    hr.creation_date       released_date,
    fur.user_name          released_by,
    lkup2.meaning          release_reason,
    holds.hold_release_id,
    holds.hold_source_id,
    hd.hold_id
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               lkup1,
    oe_lookups               lkup2,
    oe_lookups               lkup3,
    hz_cust_site_uses_all    hcsua,
    hz_cust_acct_sites_all   hcasa,
    hz_cust_accounts         hca,
    hz_parties               hp,
    fnd_user                 fu,
    fnd_user                 fur,
    fnd_lookup_values        flv
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 ooha.order_type_id = ott.transaction_type_id
    AND ooha.order_type_id = flv.lookup_code
    AND flv.lookup_type = 'NUAN_CREDIT_HOLD_RELEASE_OT'
    AND flv.enabled_flag = 'Y'
    AND trunc(nvl(flv.end_date_active, sysdate)) >= trunc(sysdate)
    AND lkup1.lookup_code = hd.type_code
    AND lkup1.lookup_type = 'HOLD_TYPE'
    AND trunc(sysdate) BETWEEN nvl(lkup1.start_date_active, trunc(sysdate)) AND nvl(lkup1.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 lkup2.lookup_type = 'RELEASE_REASON'
    AND lkup2.lookup_code = hr.release_reason_code
    AND trunc(sysdate) BETWEEN nvl(lkup2.start_date_active, trunc(sysdate)) AND nvl(lkup2.end_date_active, trunc(sysdate))
    AND ooha.invoice_to_org_id = hcsua.site_use_id
    AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
    AND hcasa.cust_account_id = hca.cust_account_id
    AND hca.party_id = hp.party_id
    AND fu.user_id = holds.created_by
    AND fur.user_id = hr.created_by
    AND hd.name = 'Credit Check Failure'
    AND trunc(ooha.ordered_date) >= '01-JAN-2025'
    AND ooha.order_number IN ( 'order_number')
ORDER BY
    fur.user_name;