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 siteu1WHERE 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 )
