Thursday, April 10, 2008

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
)

No comments:

Post a Comment