Query to get Open Sales Order in Oracle Apps

Query to get Open Sales Order in Oracle Apps

Hi friends , we are going to discuss about the Query to get Open Sales Order in Oracle Apps. We will share the detail sql query which helps to extract the open sales orders list in oracle apps system. using this sql query , we can find out which are the open sales order still in the system. This is one of the important sql query about sales order informations in oracle apps. We will share some of the important sales order tables which helps to store the open sales order information in oracle apps. Using this sql query , we can develop the custom open sale order report in oracle apps as well this sql query also helps for the extraction too. Please find below the complete detail about the Query to get Open Sales Order in Oracle Apps.

Query to get Open Sales Order in Oracle Apps

11 Important tables about Open Sales Order in Oracle Apps

 1.oe_order_headers

2.oe_order_lines

3.mtl_system_items_b

4.org_organization_definitions

5.hz_party_sites

6.hz_locations

7.hz_parties

8.fnd_territories

9.ra_salesreps

10.hz_cust_site_uses

11.hz_cust_acct_sites

Query Logic to find open sales order in oracle apps

Open Flag is the column in the sales order lines which helps to find out the open sales order lines in sales orders.

OE_ORDER_LINES_ALL.OPEN_FLAG='Y'

Detail SQL Query to get Open Sales Order in Oracle Apps

Here below is the detail sql query to get the open sales order in oracle apps. We can refer this sql query , to develop the open sales order report in oracle apps.

select 

organization_code "Inventory Org",

ooha.order_number "Sales Order No.",

ooha.cust_po_number "Sales PO",

ooha.flow_status_code "Order Status",

ooha.ordered_date "Sales Order Date",

oola.line_number,

oola.flow_status_code "Line Status",

hp.party_name "Customer Name",

hl.address1||' '||hl.address2||' '||hl.city||' '||hl.state||' '||hl.country  "Customer Address",

rs.name "Sales person",

oola.ordered_item "Sales Order Item",

oola.pricing_quantity_uom "Sales Item UOM",

msib.description "Sales Item Description",

oola.pricing_quantity "Sales Quantity"

from oe_order_headers ooha,

oe_order_lines oola,

mtl_system_items_b msib,

org_organization_definitions ood,

hz_party_sites hps ,

hz_locations hl ,

hz_parties hp,

fnd_territories ft,

ra_salesreps rs,

hz_cust_site_uses hcsua ,

hz_cust_acct_sites hcasa 

where ooha.header_id = oola.header_id

and ooha.org_id =oola.org_id

and ooha.ship_from_org_id =ood.organization_id

and oola.open_flag = 'Y' --condition to find the open sales order----

AND hl.country = ft.territory_code

and oola.ordered_item = msib.segment1

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 ooha.ship_from_org_id =msib.organization_id

order by ooha.order_number

Query to get Open Sales Order in Oracle Apps