Receiving Transaction Query in Oracle Fusion

Receiving transactions come into play when you implement the P2P (Procure-to-Pay) cycle and place a purchase order to the supplier. Once the supplier receives the order, they will inform you about the shipment plan before the actual delivery, and these plans are stored in the RCV_SHIPMENT_HEADERS and RCV_SHIPMENT_LINES tables. However, the actual receiving data is stored in the RCV_TRANSACTIONS table. In this blog, we will look at the query to get the details of receiving transactions.

You can expect to find the 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. Unit of Measure Code
8. Purchase Order (PO) Number
9. PO Line Number
10. Line Status
11. Comments
12. Receiving Date
13. Received By

SQL

SELECT rsh.shipment_num AS Número_de_Embarque,
       rsl.line_num AS Num_Linha_Embarque,
       esi.item_number AS Número_do_Item,
       rsl.item_description AS Descrição_do_Item,
       rsl.quantity_shipped AS Quantidade_Enviada,
       rt.quantity AS Quantidade_Recebida,
       rsl.uom_code AS Código_Unidade_Medida,
       pha.segment1 AS Número_PO,
       pla.line_num AS Num_Linha_PO,
       rsl.shipment_line_status_code AS Status_Linha_Embarque,
       rt.comments AS Observação,
       TO_CHAR(rt.transaction_date, 'MM/DD/YYYY') AS Data_Recebimento,
       ppn.display_name AS Recebido_Por,
       rsh.shipment_header_id AS ID_Cabeçalho_Embarque,
       rsl.shipment_line_id AS ID_Linha_Embarque
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, the actual receiving details come from the RCV_TRANSACTIONS table, but at the same time, if you query the RCV_SHIPMENT_LINES table, you will see many details in common. The difference is that the RCV_SHIPMENT_LINES table contains the initial planning data that may become true in the future, but the RCV_TRANSACTIONS table will always have the actual receiving data. Additionally, there may be multiple receiving transactions for each line in the RCV_SHIPMENT_LINES table. Check the query again, please. As you can see, the comments column comes from the RCV_TRANSACTIONS table. This means that the comments can be different for 2 different receiving transactions, even if they both belong to the same shipment line.

Explanation of the query

The query joins the following tables:

• RCV_SHIPMENT_HEADERS
• RCV_SHIPMENT_LINES
• PER_PERSON_NAMES_F
• EGP_SYSTEM_ITEMS_B
• INV_ORG_PARAMETERS
• PO_HEADERS_ALL
• PO_LINES_ALL
• RCV_TRANSACTIONS

The WHERE clause joins the tables and filters the results to only include shipments