om table

/* demo to concatenate order info */
select
oh.order_number, cust.customer_id, oh.cust_po_number, cust.customer_number,
cust.customer_name, rc.contact_key contact, ottt.name order_type,
rsa.name, oh.flow_status_code status, qlht.name price_list, ordered_date,
( select '('||oh.ship_to_org_id||') '||
arp_addr_pkg.format_address( ra.address_style,
ra.address1, ra.address2, ra.address3, ra.address4,
ra.city, ra.county, ra.state, ra.province,
ra.postal_code, terr.territory_short_name
) concatenated_address
from ra_addresses ra, fnd_territories_vl terr
where ra.address_id = (
select rsua.address_id from ra_site_uses_all rsua
where location = oh.ship_to_org_id
) and ra.country = terr.territory_code(+)
) ship_to_address,
( select '('||oh.invoice_to_org_id||') '||
arp_addr_pkg.format_address( ra.address_style,
ra.address1, ra.address2, ra.address3, ra.address4,
ra.city, ra.county, ra.state, ra.province,
ra.postal_code, terr.territory_short_name
) concatenated_address
from ra_addresses ra, fnd_territories_vl terr
where ra.address_id = (
select rsua.address_id from ra_site_uses_all rsua
where location = oh.invoice_to_org_id
) and ra.country = terr.territory_code(+)
) bill_to_address
from ra_customers cust, oe_order_headers_all oh, ra_contacts rc,
ra_salesreps_all rsa, oe_transaction_types_tl ottt,
qp_list_headers_tl qlht, oe_transaction_types_all otta
where 1=1
and cust.customer_id = oh.sold_to_org_id
and order_number like '1010612117%'
and otta.transaction_type_id = oh.order_type_id
and rsa.salesrep_id(+) = oh.salesrep_id
and ottt.transaction_type_id = oh.order_type_id
and qlht.list_header_id = oh.price_list_id
and rc.contact_id(+) = oh.sold_to_contact_id