Query to get Sales order and lines details in Oracle Apps

1--Sales order and lines details query
2
3  SELECT ooha.order_number,
4         ott.name order_type,
5         ooha.cust_po_number,
6         ooha.fob_point_code     fob,
7         ooha.flow_status_code   "Order Status",
8         ooha.ordered_date,
9         ooha.booked_date,
10         ooha.org_id,
11         hcasa.cust_acct_site_id,
12         hp.party_name           "Customer Name",
13         hps.party_site_number   "Ship to site number",
14         hl.city                 "Customer City",
15         hl.state                "Customer State",
16         hl.country              "Customer Country",
17         ft.nls_territory        "Region",
18         hpb.party_name          "Bill Customer Name",
19         hpsb.party_site_number  "Bill to site number",
20         hlb.city                "Bill Customer City",
21         hlb.state               "Bill Customer State",
22         hlb.country             "Bill Customer Country",
23         ftb.nls_territory       "Bill Region",
24         organization_code       "Inventory Org",
25         oola.line_number,
26         oola.actual_shipment_date "Actual Ship Date",
27         oola.ordered_item       "Item#/Part#",
28         oola.flow_status_code   "Line Status",
29         msib.description        "Item Description",
30         oola.source_type_code   "Source Type",
31         oola.schedule_ship_date,
32         oola.pricing_quantity   "Quantity",
33         oola.pricing_quantity_uom "UOM"
34    FROM apps.oe_order_headers_all       ooha,
35         apps.oe_order_lines_all         oola,
36         apps.mtl_system_items_b         msib,
37         -----
38         apps.org_organization_definitions ood,
39         apps.hz_cust_site_uses_all      hcsua,
40         apps.hz_cust_acct_sites_all     hcasa,
41         apps.hz_party_sites             hps,
42         apps.hz_locations               hl,
43         apps.hz_parties                 hp,
44         apps.fnd_territories            ft,
45         ------
46         apps.hz_cust_site_uses_all      hcsuab,
47         apps.hz_cust_acct_sites_all     hcasab,
48         apps.hz_party_sites             hpsb,
49         apps.hz_locations               hlb,
50         apps.hz_parties                 hpb,
51         apps.fnd_territories            ftb,
52         apps.oe_transaction_types_tl    ott
53   WHERE     1 = 1
54         AND ooha.header_id = oola.header_id
55         AND ooha.org_id = oola.org_id
56         AND oola.ordered_item = msib.segment1
57         AND ooha.ship_from_org_id = msib.organization_id
58         --
59         AND ooha.ship_from_org_id = ood.organization_id(+)
60         AND ooha.ship_to_org_id = hcsua.site_use_id(+)
61         AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id(+)
62         AND hcasa.party_site_id = hps.party_site_id(+)
63         AND hps.location_id = hl.location_id(+)
64         AND hps.party_id = hp.party_id(+)
65         AND hl.country = ft.territory_code(+)
66         --
67         AND ooha.invoice_to_org_id = hcsuab.site_use_id
68         AND hcsuab.cust_acct_site_id = hcasab.cust_acct_site_id
69         AND hcasab.party_site_id = hpsb.party_site_id
70         AND hpsb.location_id = hlb.location_id
71         AND hpsb.party_id = hpb.party_id
72         AND hlb.country = ftb.territory_code
73         --
74         AND ott.language = 'US'
75         AND ott.transaction_type_id = ooha.order_type_id
76         AND ooha.order_number = '1235513'
77ORDER BY ooha.order_number, oola.line_number;