Query to get return sales order in oracle apps : RMA sales order sql query in oracle apps

Query to get return sales order in oracle apps : RMA sales order sql query in oracle apps 

In this post , We will be discuss about the SQL query to extract return sales order information's in oracle apps. We do enter the return or RMA sales orders when we want to receive back the sold items from our customers as an rejection. Some time, Customers do rejects the Items which we have sold to them due to some quality or any other reasons but to receive those item again in our company or system we do need to create the return/rma sales order. Here below i will share the detail sql query to extract the return sales order in oracle apps.

Important Tables Used by return sales order SQL query in oracle apps

1.OE_ORDER_HEADERS_ALL

2.OE_ORDER_LINES_ALL

3.RCV_SHIPMENT_HEADERS

4.ar_customers ac

5.RCV_SHIPMENT_LINES

6.hz_cust_accounts

Detail SQL Query to get return sales order in oracle apps 

This is the SQL query which helps to extract the return sales order information's in oracle apps.

SELECT ooha.ORDER_NUMBER "SALES ORDER"

,ac.customer_name

 ,ooha.ORDER_CATEGORY_CODE

 ,oola.ORDERED_ITEM

 ,oola.ordered_quantity

 ,rsl.quantity_shipped

 ,rsl.quantity_received

 ,oola.SUBINVENTORY

 ,SHIPMENT_NUM

 ,rsh.RECEIPT_NUM

 ,rsh.ATTRIBUTE_CATEGORY

 ,rsl.UNIT_OF_MEASURE

 ,rsl.ITEM_DESCRIPTION

 ,rsl.SHIPMENT_LINE_STATUS_CODE

 ,rsl.SOURCE_DOCUMENT_CODE

 FROM apps.OE_ORDER_HEADERS_ALL ooha

 ,apps.OE_ORDER_LINES_ALL oola

 ,apps.RCV_SHIPMENT_HEADERS rsh

 ,apps.ar_customers ac

 ,apps.RCV_SHIPMENT_LINES rsl

 ,apps.hz_cust_accounts hca

 WHERE 1=1

 AND ooha.header_id=oola.header_id

 AND ooha.header_id=rsl.OE_ORDER_HEADER_ID

 AND rsh.shipment_header_id=rsl.shipment_header_id

 AND rsl.OE_ORDER_LINE_ID=oola.line_id

 AND ooha.ORDER_NUMBER=:P_ORDR_NUMBER

 and ooha.sold_to_org_id=hca.cust_account_id

 AND SOURCE_DOCUMENT_CODE ='RMA'

 and hca.cust_account_id=ac.customer_id