Oracle Apps R12 Sales order and lines details query

 --Sales order and lines details query

SELECT ooha.order_number,
       ott.name                  order_type,
       ooha.cust_po_number,
       ooha.fob_point_code       fob,
       ooha.flow_status_code     Order_Status,
       ooha.ordered_date,
       ooha.booked_date,
       ooha.org_id,

       ood.organization_name warehouse,--SHIP_FROM_ORG_ID : Warehouse from where you ship the goods to customer
       hcasa.cust_acct_site_id,
       hp.party_name             Customer_Name,
       hps.party_site_number     Ship_to_site_number,
       hcsua.location                  SHIP_TO_LOCATION,
       hcsuab.location                 BILL_TO_LOCATION,
       hl.city                   Customer_City,
       hl.state                  Customer_State,
       hl.country                Customer_Country,
       ft.nls_territory          Region,
       hpb.party_name            Bill_Customer_Name,
       hpsb.party_site_number    Bill_to_site_number,
       hlb.city                  Bill_Customer_City,
       hlb.state                 Bill_Customer_State,
       hlb.country               Bill_Customer_Country,
       ftb.nls_territory         Bill_Region,
       organization_code         Inventory_Org,
       oola.line_number,

      oe_order_misc_pub.Get_concat_line_number (oola.line_id) line_no,
       oola.actual_shipment_date Actual_Ship_Date,
       oola.ordered_item         Item#_or_Part#,
       oola.flow_status_code     Line_Status,
       msib.description          Item_Description,
       oola.source_type_code     Source_Type,
       oola.schedule_ship_date,
       oola.pricing_quantity     Quantity,
       oola.pricing_quantity_uom UOM,
       oe_line_status_pub.Get_line_status(oola.line_id, oola.flow_status_code) line_status,
       (SELECT set_name
        FROM   oe_sets oes
        WHERE  oes.set_id = oola.ship_set_id
               AND oes.header_id = oola.header_id)             SHIP_SET
FROM   apps.oe_order_headers_all ooha,
       apps.oe_order_lines_all oola,
       apps.mtl_system_items_b msib,
       -----
       apps.org_organization_definitions ood,
       apps.hz_cust_site_uses_all hcsua,
       apps.hz_cust_acct_sites_all hcasa,
       apps.hz_party_sites hps,
       apps.hz_locations hl,
       apps.hz_parties hp,
       apps.fnd_territories ft,
       ------
       apps.hz_cust_site_uses_all hcsuab,
       apps.hz_cust_acct_sites_all hcasab,
       apps.hz_party_sites hpsb,
       apps.hz_locations hlb,
       apps.hz_parties hpb,
       apps.fnd_territories ftb,
       apps.oe_transaction_types_tl ott
WHERE  1 = 1
       AND ooha.header_id = oola.header_id
       AND ooha.org_id = oola.org_id
       AND oola.ordered_item = msib.segment1
       AND ooha.ship_from_org_id = msib.organization_id
       --
       AND ooha.ship_from_org_id = ood.organization_id(+)
       AND ooha.ship_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 hps.location_id = hl.location_id(+)
       AND hps.party_id = hp.party_id(+)
       AND hl.country = ft.territory_code(+)
       --
       AND ooha.invoice_to_org_id = hcsuab.site_use_id
       AND hcsuab.cust_acct_site_id = hcasab.cust_acct_site_id
       AND hcasab.party_site_id = hpsb.party_site_id
       AND hpsb.location_id = hlb.location_id
       AND hpsb.party_id = hpb.party_id
       AND hlb.country = ftb.territory_code
       --
       AND ott.LANGUAGE = 'US'
       AND ott.transaction_type_id = ooha.order_type_id
       AND ooha.order_number = '---------'
ORDER  BY ooha.order_number,
          oola.line_number; 

P.S.

OE_ORDER_HEADERS_ALL:

ORG_ID --[Operating Unit which performed this transaction; Organization that is taking the order (same as sold_from_org_id)]  

SOLD_FROM_ORG_ID, --[Selling Organization (OU); Organization that took the order (same as org_id) ]

SOLD_TO_ORG_ID,  --Sold to customer ( hz_cust_accounts.CUST_ACCOUNT_ID )

SHIP_FROM_ORG_ID, --Ship From Organization Id - Warehouse

SHIP_TO_ORG_ID,  --Ship to Organization Id = HZ_CUST_SITE_USES_ALL.site_use_id

INVOICE_TO_ORG_ID, --Invoice To Organization ID = HZ_CUST_SITE_USES_ALL.site_use_id

DELIVER_TO_ORG_ID, --Deliver to Organization Id

OE_ORDER_LINES_ALL:

ORG_ID --(Operating Unit which performed this transaction, same as sold_from_org_id )

SHIP_FROM_ORG_ID --(Ship from organization id -warehouse)

SOLD_FROM_ORG_ID --(Selling organization, same as org_id )

sold_to_org_id --(Customer to whom the item is sold to)  = HZ_CUST_ACCOUNTS.cust_account_id

ship_to_org_id --(Ship to Organization Id) = HZ_CUST_SITE_USES_ALL.site_use_id

invoice_to_org_id --(Invoice To Organization ID)  = HZ_CUST_SITE_USES_ALL.site_use_id