--This query extracts all merchandise sales order lines belonging to sales orders that have at least one line pending fulfillment

--This query extracts all merchandise sales order lines belonging to --sales orders that have at least one line pending fulfillment --Part 1: Selects lines with no delivery id, i.e. not ready for release yet

SELECT h.order_number
      ,o.NAME org
      ,o.TYPE org_type
      ,l.subinventory
      ,m.segment1 sku
      ,m.description
      ,l.line_category_code
      ,TO_DATE(l.request_date, 'DD-MM-YY')
      ,TO_DATE(l.schedule_ship_date, 'DD-MM-YY')
      ,l.ship_to_org_id ship_to_org
      ,l.flow_status_code status
      ,NULL released_status
      ,l.attribute7 paid_flag
      ,l.attribute3 timeslot
      ,l.shipping_method_code
      ,l.return_attribute3 return_method
      ,partyname.last_name
      ,partyname.first_name
      ,partyname.phone
  FROM oe_order_headers_all h
      ,oe_order_lines_all l
      ,hr_organization_units o
      ,mtl_system_items_b m
      ,(SELECT shp_site_use.site_use_id invoice_to_org_id
              ,shipaddr.address1
              ,shipaddr.address2
              ,shipaddr.address3
              ,shipaddr.address4
              ,shipaddr.city
              ,shipaddr.province
              ,shipaddr.postal_code
              ,shp_party_site.party_id
              ,shipaddr.country
              ,DECODE(hp.person_last_name, NULL, hp.party_name, hp.person_last_name) last_name
              ,DECODE(hp.person_pre_name_adjunct
                     ,NULL, DECODE(hp.person_first_name, NULL, '', hp.person_first_name)
                     , lv.meaning || ' ' || hp.person_first_name) first_name
              ,    DECODE(cp.phone_area_code, NULL, '', cp.phone_area_code || '-')
                || cp.phone_number
                || DECODE(cp.phone_extension, NULL, '', ' x' || cp.phone_extension) phone
          FROM hz_locations shipaddr
              ,hz_party_sites shp_party_site
              ,hz_cust_acct_sites_all shp_acct_site
              ,hz_cust_site_uses_all shp_site_use
              ,hz_parties hp
              ,fnd_lookup_values_vl lv
              ,hz_contact_points cp
         WHERE shp_site_use.site_use_code = 'BILL_TO'
           AND shp_site_use.cust_acct_site_id = shp_acct_site.cust_acct_site_id
           AND shp_acct_site.party_site_id = shp_party_site.party_site_id
           AND shipaddr.location_id = shp_party_site.location_id
           AND shp_party_site.party_id = hp.party_id
           AND lv.lookup_type(+) = 'CONTACT_TITLE'
           AND lv.lookup_code(+) = hp.person_pre_name_adjunct
           AND cp.owner_table_name(+) = 'HZ_PARTIES'
           AND cp.owner_table_id(+) = shp_party_site.party_id
           AND cp.status(+) = 'A'
           AND cp.primary_flag(+) = 'Y'
           AND cp.contact_point_type(+) = 'PHONE') partyname
 WHERE TRUNC(l.schedule_ship_date) > '20-MAR-2012'
   AND l.flow_status_code IN('BOOKED', 'AWAITING_SHIPPING', 'AWAITING_RETURN')
   AND h.header_id = l.header_id
   AND l.ship_from_org_id =
          o.organization_id