Tuesday, 13 January 2015

Query to find customer, ship to and bill to information of an order :

SELECT   h.order_number,
         c.name customer_name,
         lk1.meaning Freight_Terms,
         lk2.meaning FOB,
         s.location_code ship_location_code,
         s.address_line_1 ship_address1,
         s.address_line_2 ship_address2,
         s.state ship_state,
         s.postal_code ship_zip,
         s.country ship_country,
         b.location_code bill_location_code,
         b.address_line_1 bill_address1,
         b.address_line_2 bill_address2,
         b.country bill_country
  FROM   ar_lookups lk2,
         oe_lookups lk1,
         oe_sold_to_orgs_v c,
         oe_invoice_to_orgs_v b,
         oe_ship_to_orgs_v s,
         oe_order_headers_all h
 WHERE       h.order_number = 14463
         AND h.org_id = 204
         AND h.ship_to_org_id = s.organization_id
         AND h.invoice_to_org_id = b.organization_id
         AND h.sold_to_org_id = c.organization_id
         AND h.freight_terms_code = lk1.lookup_code(+)
         AND lk1.lookup_type(+) = 'FREIGHT_TERMS'
         AND lk2.lookup_code(+) = h.fob_point_code
         AND lk2.lookup_type(+) = 'FOB'

No comments:

Post a Comment