Join Between oe_order_headers_all and aso_quote_headers_all

Join Between oe_order_headers_all and aso_quote_headers_all

Hi friends, we are going to discuss about the Join Between oe_order_headers_all and aso_quote_headers_all. We will share the complete joins between the Sales Quote tables and the Sales order tables. In this post, We will explain the complete joins between the oe_order_headers_all and aso_quote_headers_all tables in more detail. If we want to develop the sales order reports in oracle apps , then we should have the complete idea about the joins between the sales order tables. Sales order informations stores in oe_order_headers_all and Sales quotation informations stores in the aso_quote_headers_all. We will also share the important sql queries too , which helps to extract the complete sales order and sales order quotation details in oracle apps. Please find below the complete detail about the Join Between oe_order_headers_all and aso_quote_headers_all.

Join Between oe_order_headers_all and aso_quote_headers_all

3 Important Sales Order Quotation Tables in Oracle apps

Here below are some of the important sales order quote tables.

1.ASO_QUOTE_HEADERS_ALL.

2.ASO_QUOTE_LINES_ALL.

3.ASO_SHIPMENTS.

2 Important Sales Order Tables in Oracle apps

Here below are some of the important sales order tables in oracle apps.

1.OE_ORDER_HEADERS_ALL

2.OE_ORDER_LINES_ALL

2 Important Joins between oe_order_headers_all and aso_quote_headers_all

To Join the ASO_QUOTE_HEADERS_ALL and OE_ORDER_HEADERS_ALL , We need to use ORDER_ID field from ASO_QUOTE_HEADERS_ALL and HEADER_ID field from OE_ORDER_HEADERS_ALL.

To Join the ASO_QUOTE_LINES_ALL and OE_ORDER_LINES_ALL , We need to use ORDER_ID field from ASO_QUOTE_HEADERS_ALL and HEADER_ID field from OE_ORDER_LINES_ALL.

Sample SQL Query using Join Between oe_order_headers_all and aso_quote_headers_all

Here below is the sample sql query in which we are using the Join Between oe_order_headers_all and aso_quote_headers_all tables in oracle apps.

select a1.quote_header_id,

a1.creation_date "Quote Creation Date",

a1.QUOTE_NAME,

A1.QUOTE_NUMBER,

A1.QUOTE_SOURCE_CODE,

A1.QUOTE_EXPIRATION_DATE,

A1.CURRENCY_CODE "Quote Currency",

A1.TOTAL_QUOTE_PRICE,

A1.PAYMENT_AMOUNT "Quote Payment Amount",

A1.EXCHANGE_RATE "Quote Exchange Rate",

A1.EXCHANGE_TYPE_CODE "Quote Exchange Type",

EXCHANGE_RATE_DATE "Quote Exchange Rate Date",

a1.order_id,

a2.order_number "Sales Order Number",a2.ordered_date "Sales Order Date",a2.CUST_PO_NUMBER,A3.ORDERED_ITEM,A3.REQUEST_DATE,A3.PROMISE_DATE,A3.ORDER_QUANTITY_UOM,

A3.PRICING_QUANTITY,

A3.SHIPPED_QUANTITY,

A3.ORDERED_QUANTITY

from apps.aso_quote_headers_all a1, apps.aso_quote_lines_all A4,apps.oe_order_headers_all a2,apps.oe_order_LINEs_all a3

where  a1.quote_header_id=a4.quote_header_id

AND A2.HEADER_ID=A3.HEADER_ID

AND A2.HEADER_ID=701684

AND a1.order_id=A2.HEADER_ID

AND a1.order_id=A3.HEADER_ID

Detail SQL Query to extract the Sales order and Quotation Details

select distinct

a1.QUOTE_NAME,

A1.QUOTE_NUMBER,

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,

apps.aso_quote_headers_all a1

 where ooha.header_id = oola.header_id

and ooha.ship_from_org_id =msib.organization_id

and A1.ORDER_ID=OOHA.HEADER_ID

and nvl(rcta.interface_header_context,'ORDER ENTRY') = 'ORDER ENTRY'

and to_char(ooha.order_number) = rcta.interface_header_attribute1(+)

and ooha.ship_from_org_id =ood.organization_id

and ooha.org_id =oola.org_id

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 hl.country = ft.territory_code

Join Between oe_order_headers_all and aso_quote_headers_all

Join Between oe_order_headers_all and aso_quote_headers_all