rma sales order query in oracle apps r12

rma sales order query in oracle apps r12

RMA sales order we do enter in oracle system , when we have to receive the rejected sold items by our customers. In Standard Sales Order , We sent the Items to our customer but in rma sales order in receive the sold items from our customers which got rejected by our customer due to any issue. i am sharing the SQL query which will help to extract the rma sales order information in oracle apps. Please find below the detail of sql query for rma sales orders.

6 Important tables of rma sales order query in oracle apps r12

1.OE_ORDER_HEADERS_ALL

2.OE_ORDER_LINES_ALL

3.RCV_SHIPMENT_HEADERS

4.ar_customers 

5.RCV_SHIPMENT_LINES

6.hz_cust_accounts

Detail rma sales order query in oracle apps r12

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

rma sales order query in oracle apps r12