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
