Thursday 17 July 2014

Orders Without Holds Query

/* Formatted on 7/17/2014 12:40:18 PM (QP5 v5.115.810.9015) */
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