Customer Information from Sales Order SQL

11i

SELECT
NVL(hcasa.translated_customer_name, hp.party_name) Customer_Name, ooha.order_number
FROM oe_order_headers_all ooha
,oe_order_lines_all oola
,hz_cust_site_uses_all hcsua
,hz_cust_acct_sites_all hcasa
,hz_parties hp
,hz_cust_accounts hca
,hz_party_sites hps
WHERE hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND ooha.ship_to_org_id = hcsua.site_use_id(+)
AND hcsua.cust_acct_site_id= hcasa.cust_acct_site_id(+)
AND ooha.sold_to_org_id = hca.cust_account_id(+)
AND hca.party_id = hp.party_id(+)
AND hcasa.party_site_id = hps.party_site_id(+)
AND order_number = ' '


OR


SELECT hp.party_name, hl.address1, hl.address2, hl.address3, hl.address4,
          DECODE (hl.city, NULL, NULL, hl.city || ', ')
       || DECODE (hl.state, NULL, hl.province || ', ', hl.state || ', ')
       || DECODE (hl.postal_code, NULL, NULL, hl.postal_code || ', ')
       || DECODE (hl.country, NULL, NULL, hl.country) address5
  FROM (SELECT DECODE (v_site_use_code,
                       'BILL_TO', invoice_to_org_id,
                       'SHIP_TO', ship_to_org_id
                      ) site_org_id,
               org_id, sold_to_org_id
          FROM apps.oe_order_headers_all
         WHERE header_id = v_header_id) ooh,
       apps.hz_cust_accounts hca,
       apps.hz_parties hp,
       apps.hz_cust_acct_sites_all hcas,
       apps.hz_cust_site_uses_all hcsu,
       apps.hz_party_sites hps,
       apps.hz_locations hl
 WHERE ooh.sold_to_org_id = hca.cust_account_id
   AND hca.party_id = hp.party_id
   AND hca.cust_account_id = hcas.cust_account_id
   AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
   AND hcsu.site_use_id = ooh.site_org_id
   AND hcas.org_id = hcsu.org_id
   AND hcsu.org_id = ooh.org_id
   AND hcas.party_site_id = hps.party_site_id
   AND hps.location_id = hl.location_id


R12

SELECT   ooha.header_id order_header_id,
         ooha.order_number, ooha.ordered_date,
         ooha.transactional_curr_code order_currency, hp.party_id,
         hp.party_number, hp.party_name customer_name,
         hca.cust_account_id customer_id, hca.account_number customer_number
             FROM oe_order_headers_all ooha,
                 hz_parties hp,
         hz_cust_accounts hca
   WHERE     hca.cust_account_id(+) = ooha.sold_to_org_id
     AND hp.party_id = hca.party_id
          AND ooha.order_number = :sales_order_number;
       

OR


SELECT   ooha.header_id order_header_id, ottt.NAME order_type_name,
         ooha.order_number, ooha.ordered_date,
         ooha.transactional_curr_code order_currency, hp.party_id,
         hp.party_number, hp.party_name customer_name,
         hca.cust_account_id customer_id, hca.account_number customer_number,
         oola.line_id order_line_id, oola.line_number, oola.inventory_item_id,
         msib.segment1 item_number, msib.description item_desc,
         oola.attribute15 superseded_item, oola.order_quantity_uom,
         oola.ordered_quantity, oola.unit_selling_price
    FROM oe_order_headers_all ooha,
         oe_order_lines_all oola,
         oe_transaction_types_tl ottt,
         mtl_system_items_b msib,
         mtl_parameters mp,
         org_organization_definitions ood,
         hz_parties hp,
         hz_cust_accounts hca
   WHERE ooha.header_id = oola.header_id
     AND ottt.transaction_type_id(+) = ooha.order_type_id
         AND hca.cust_account_id(+) = ooha.sold_to_org_id
     AND hp.party_id = hca.party_id
     AND ooha.org_id = oola.org_id(+)
     AND msib.inventory_item_id = oola.inventory_item_id
     AND msib.organization_id = mp.master_organization_id
     AND mp.organization_id = ood.organization_id
     AND mp.master_organization_id = mp.organization_id
         AND ooha.order_number = :sales_order_number
ORDER BY ottt.NAME, ooha.order_number, oola.line_number;