Query to Get Oracle Sales Order Header and Line Details

SELECT ooha.header_id,

       ooha.order_number,

       ott.NAME "ORDER_TYPE",

       hp.party_name "CUSTOMER",

       hca.account_number "CUSTOMER_NUMBER",

       ooha.ordered_date "DATE_ORDERED",

       qh.NAME "PRICE_LIST",

       ooha.transactional_curr_code "CURRENCY",

       ooha.freight_carrier_code "SHIPPING_METHOD",

       ooha.flow_status_code "STATUS",

       rtt.NAME "PAYMENT_TERMS",

       --mp.organization_code "WARE_HOUSE",

       rsa.NAME "SALES_PERSON",

       hcsua.LOCATION || ','|| hl.address2|| ','|| hl.city|| ','|| hl.state|| ','|| hl.postal_code|| ','|| hl.county  "BILL_TO_LOCATION",

       hcsua1.LOCATION|| ','|| hl1.address2|| ','|| hl1.city|| ','|| hl1.state|| ','|| hl1.postal_code|| ','|| hl1.county  "SHIP_TO_LOCATION",

       oola.line_id,

       oola.line_number "LINE_NUMBER",

       ottl.NAME "LINE_TYPE",

       oola.flow_status_code Line_Status,

       oola.ordered_item "ORDERED_ITEM",

       oola.ordered_quantity "QTY",

       oola.order_quantity_uom "UOM",

       oola.unit_selling_price "UNIT_SELLING_PRICE",

       (NVL(oola.ordered_quantity,0)) * (NVL(oola.unit_selling_price,0)) "LINE_TOTAL"

  FROM oe_order_headers_all ooha,

       oe_transaction_types_tl ott,

       qp_list_headers qh,

       ra_terms_tl rtt,

       mtl_parameters mp,

       ra_salesreps_all rsa,

       hz_cust_accounts hca,

       hz_parties hp,

       hz_parties hp1,

       hz_locations hl,

       hz_locations hl1,

       hz_cust_acct_sites_all hcasa,

       hz_cust_acct_sites_all hcasa1,

       hz_cust_site_uses_all hcsua,

       hz_cust_site_uses_all hcsua1,

       hz_party_sites hps,

       hz_party_sites hps1,

       oe_order_lines_all oola,

       oe_transaction_types_tl ottl

WHERE     1 = 1

       AND ooha.sold_to_org_id = hca.cust_account_id

       AND ooha.order_type_id = ott.transaction_type_id

       AND ott.LANGUAGE = USERENV ('LANG')

       AND rtt.LANGUAGE = USERENV ('LANG')

       AND rtt.term_id = ooha.payment_term_id

       AND qh.list_header_id = ooha.price_list_id

       AND mp.organization_id = ooha.ship_from_org_id

       AND ooha.salesrep_id = rsa.salesrep_id

       AND hca.party_id = hp.party_id

       AND hca.party_id = hp1.party_id

       AND ooha.invoice_to_org_id = hcsua.site_use_id(+)

       AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id(+)

       AND hcasa.party_site_id = hps.party_site_id(+)

       AND hl.location_id(+) = hps.location_id

       AND ooha.ship_to_org_id = hcsua1.site_use_id(+)

       AND hcsua1.cust_acct_site_id = hcasa1.cust_acct_site_id(+)

       AND hcasa1.party_site_id = hps1.party_site_id(+)

       AND hl1.location_id(+) = hps1.location_id

       AND oola.header_id = ooha.header_id

       AND oola.line_type_id = ottl.transaction_type_id

       AND ooha.order_number = NVL(:P_ORDER_NUM,ooha.order_number)