Orders Without Holds

SELECT acct.account_number
     , order_number
     , ool.line_number
     , ool.ordered_item
     , TRUNC(ordered_date) ordered_date
     , ool.flow_status_code order_line_status
     , loc.address1 Billto_address1
     , loc.address2 Billto_Address2
     , loc.city billto_city
     , loc.state billto_state
     , loc.postal_code billto_postal_code 
     , loc1.address1 Shipto_address1
     , loc1.address2 Shipto_Address2
     , loc1.city Shipto_city
     , loc1.state shipto_state
     , loc1.postal_code shipto_postal_code
     , unit_selling_price*ordered_quantity line_amount
  FROM apps.oe_order_headers_all ooh
     , apps.oe_order_lines_all ool
     , apps.hz_cust_accounts    acct
     -- Bill To Address
     , apps.hz_cust_acct_sites_all   asite
     , apps.hz_party_sites           psite
     , apps.hz_locations             loc
     , apps.hz_cust_site_uses_all    siteu
     -- ship To Addess
     , apps.hz_cust_acct_sites_all   asite1
     , apps.hz_party_sites           psite1
     , apps.hz_locations             loc1
     , apps.hz_cust_site_uses_all    siteu1
WHERE ooh.ordered_date between sysdate - 10
                           and sysdate
  and ooh.header_id= ool.header_id
  and order_category_code= 'ORDER'
  and ooh.flow_status_code <> 'CANCELLED'
  and ool.flow_status_code <> 'CANCELLED'
  and acct.cust_account_id     = ooh.sold_to_org_id
  -- bill To Address
  and ooh.invoice_to_org_id    = siteu.site_use_id
  and siteu.cust_acct_site_id  = asite.cust_acct_site_id
  and asite.party_site_id      = psite.party_site_id
  and psite.location_id        = loc.location_id
  -- ship To Address
  and ooh.ship_to_org_id       = siteu1.site_use_id
  and siteu1.cust_acct_site_id = asite1.cust_acct_site_id
  and asite1.party_site_id     = psite1.party_site_id
  and psite1.location_id       = loc1.location_id
  AND NOT EXISTS
  ( SELECT 'x'
      FROM apps.oe_order_holds_all hld
         , apps.oe_hold_sources_all src
         , apps.oe_hold_definitions def
     WHERE src.hold_source_id= hld.hold_source_id
       and def.hold_id= src.hold_id 
       and hld.header_id= ooh.header_id
  )