Sales order query in oracle apps

Sales order query in oracle apps

This sql query helps to extract the complete sales order information in oracle aps r12. This sql query is giving complete sale order information from Order to cash. It means we will get Sales Order information as well as the Customer Invoice created against the sale order with this sql query. Please find below sales order query in oracle apps.

 

 

Tables uses in the Sales order query in oracle apps:-

 

 

select distinct

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",

ooha.org_id,

hcasa.cust_acct_site_id,

hp.party_name "Customer Name",

hl.city "Customer City",

hl.state "Customer State",

hl.country "Customer Country",

ft.nls_territory "Region",

rs.name "Salesperson",

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"

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(+)

order by ooha.order_number,

oola.line_number;