-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 = '123'

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'

/