Receiving Transaction Query in Oracle Fusion

Receiving Transactions come in the picture if you have implemented P2P Cycle and placed the purchase order with the vendor. Once the vendor has the order, he will intimate you of the shipment plan before the actual delivery and these plans are stored in RCV_SHIPMENT_HEADERS & RCV_SHIPMENT_LINES Table. But actual receiving data is stored in RCV_TRANSACTIONS table. In this blog we will have a look at the query to fetch you receiving transaction details.

You can expect to find following details using this query:
1. Shipment Number
2. Shipment Line Number
3. Item Number
4. Item Description
5. Shipped Quantity
6. Received Quantity
7. UOM Code
8. PO Number
9. PO Line Number
10. Line Status
11. Comments
12. Receiving Date
13. Received By

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...

Receiving Transaction Query

SELECT rsh.shipment_num AS Shipment_Number,
rsl.line_num AS shipment_line_num,
esi.item_number,
rsl.item_description,
rsl.quantity_shipped,
rt.quantity AS quantity_received,
rsl.uom_code,
pha.segment1 AS po_number,
pla.line_num AS po_line_num,
rsl.shipment_line_status_code AS line_status,
rt.comments NOTE,
TO_CHAR(rt.transaction_date, 'MM/DD/YYYY') receipt_date
,ppn.display_name Received_By
, rsh.shipment_header_id
, rsl.shipment_line_id
FROM
rcv_shipment_headers rsh,
rcv_shipment_lines rsl
,per_person_names_f ppn ,
egp_system_items_b esi,
inv_org_parameters iop ,
po_headers_all pha,
po_lines_all pla ,
rcv_transactions rt
WHERE rsh.shipment_header_id = rsl.shipment_header_id (+)
AND esi.inventory_item_id(+) = rsl.item_id
AND esi.organization_id = iop.organization_id(+)
AND iop.organization_code(+) = 'IMO'
AND pla.po_line_id(+) = rsl.po_line_id
AND pha.po_header_id(+) = rsl.po_header_id
AND rt.SHIPMENT_LINE_ID(+) = rsl.SHIPMENT_LINE_ID
AND rt.transaction_type(+) = 'RECEIVE'
AND ppn.person_id(+) = rt.employee_id
AND ppn.name_type(+) = 'GLOBAL'
AND rsh.shipment_num = :ASBN_NUM 
Order by rsh.shipment_num , rsl.line_num  

As you can see that actual receiving details are coming from RCV_TRANSACTIONS table but at the same time if you query RCV_SHIPMENT_LINES table, you will see a lot of common details. Difference is that RCV_SHIPMENT_LINES contain the planning data initially which can become true in future but RCV_TRANSACTIONS table will always have the actual receiving data. Also there can be multiple receiving transaction for each row in RCV_SHIPMENT_LINES table. Check the query again once pls. As you see that comment column is coming from RCV_TRANSACTIONS table. Means comments can be different for 2 different receiving transactions, even though both belong to same shipment line.

Related Posts:

RCV_SHIPMENT_HEADERS: Columns & Indexes
RCV_SHIPMENT_LINES: Columns, Indexes & Few Tricks
RCV_TRANSACTIONS: Columns, Indexes & Tricks

Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.

Reference:
support.oracle.com

Check out the Amazon Deals of the day based on your interests..