Sales & Open Order Query

begin
dbms_application_info.set_client_info('237');
end;

select distinct
ooha.org_id,
hcasa.cust_acct_site_id,
hcasa.attribute1 "Customer Duns#",
hp.party_name "Customer Name",
hl.city "Customer City",
hl.state "Customer State",
hl.country "Customer Country",
hcsua.attribute1 "Customer Relationship",
ft.nls_territory "Region",
rs.name "Salesperson",
ooha.order_number,
ooha.cust_po_number,
ooha.fob_point_code FOB,
ooha.flow_status_code "Order Status",
ooha.ordered_date,
ooha.booked_date,
oola.line_number,
oola.flow_status_code "Line Status",
nvl((select 'Y' from oe_order_holds where header_id =ooha.header_id and line_id = oola.line_id and rownum <=1),'N') "Hold Status",
oola.actual_shipment_date "Actual Ship Date",
rcta.trx_number "AR Invoice Number",
rcta.trx_date "AR Invoice Date",
organization_code "Inventory Org",
oola.ordered_item "Item#/Part#",
msib.description "Item Description",
oola.source_type_code "Source Type",
oola.schedule_ship_date,
oola.pricing_quantity "Quantity",
oola.pricing_quantity_uom "UOM",
ooha.orig_sys_document_ref "Order Header Source Reference"
from oe_order_headers ooha,
oe_order_lines oola,
mtl_system_items_b msib,
org_organization_definitions ood,
ra_salesreps rs,
hz_cust_site_uses hcsua    ,
hz_cust_acct_sites hcasa   ,
hz_party_sites hps             ,
hz_locations hl                ,
hz_parties               hp,
fnd_territories ft,
ra_customer_trx rcta
where 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 oola.salesrep_id = rs.salesrep_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 nvl(rcta.interface_header_context,'ORDER ENTRY') = 'ORDER ENTRY'
and  to_char(ooha.order_number) = rcta.interface_header_attribute1(+)
AND to_date(TO_CHAR(ooha.ordered_date,'dd-mon-rrrr') ,'dd-mon-rrrr') BETWEEN to_date('28-Sep-2012','dd-mon-rrrr') AND to_date('31-Dec-2012','dd-mon-rrrr');
--and ooha.order_number='50001575'
order by ooha.order_number,
oola.line_number;