This PO Unordered Receipt Report query retrieves a list of unordered receipts, providing details about the vendor, receiver, and item information for each receipt. The data is organized by organization, helping to track and manage receipts that were not linked to a purchase order. It’s valuable for identifying discrepancies or irregularities in inventory management and receipt processes.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves unordered receipt transactions with receiver, vendor, item, and organization details
RSH.RECEIPT_NUM RECEIPT_NUMBER,
RSH.SHIPMENT_NUM SHIPMENT_NUMBER,
RSH.SHIPPED_DATE,
RSH.EXPECTED_RECEIPT_DATE,
PPNF.FULL_NAME RECEIVER,
HAOU.NAME RECEIVER_ORG,
PSV.VENDOR_NAME SUPPLIER_NAME,
PSV.SEGMENT1 SUPPLIER_NUMBER,
RT.TRANSACTION_DATE RECEIPT_DATE,
ESI.INVENTORY_ITEM_ID,
ESI.INVENTORY_ITEM_STATUS_CODE,
ESI.ITEM_TYPE,
ESI.ITEM_NUMBER,
RSL.ITEM_DESCRIPTION DESCRIPTION,
RSL.ITEM_REVISION,
RSL.SHIPMENT_LINE_STATUS_CODE,
RSL.VENDOR_ITEM_NUM,
IOP.ORGANIZATION_CODE,
RSL.LINE_NUM,
RSL.QUANTITY_RECEIVED,
RSL.QUANTITY_SHIPPED,
RSL.QUANTITY_DELIVERED,
RSL.QUANTITY_ACCEPTED,
RSL.QUANTITY_REJECTED,
RSL.QUANTITY_RETURNED,
RT.QUANTITY,
RT.AMOUNT RCV_AMOUNT
FROM
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_LINES RSL,
RCV_SHIPMENT_HEADERS RSH,
POZ_SUPPLIERS_V PSV,
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F_V PPNF,
EGP_SYSTEM_ITEMS ESI,
INV_ORG_PARAMETERS IOP,
-- FINANCIALS_SYSTEM_PARAMETERS FSP,
POZ_LOOKUP_CODES PLC,
HR_ALL_ORGANIZATION_UNITS HAOU
WHERE
RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND PPNF.PERSON_ID = PAPF.PERSON_ID
AND RT.EMPLOYEE_ID = PAPF.PERSON_ID
AND RT.VENDOR_ID = PSV.VENDOR_ID(+)
AND RSL.ITEM_ID = ESI.INVENTORY_ITEM_ID(+)
AND RT.SUBSTITUTE_UNORDERED_CODE = 'UNORDERED'
AND RT.TRANSACTION_TYPE = 'UNORDERED'
AND RT.ORGANIZATION_ID = IOP.ORGANIZATION_ID
AND HAOU.ORGANIZATION_ID = IOP.ORGANIZATION_ID
AND RSL.QUANTITY_RECEIVED > 0
AND RSH.RECEIPT_SOURCE_CODE = PLC.LOOKUP_CODE(+)
AND PLC.LOOKUP_TYPE = 'RCV_SHIPMENT_SOURCE_TYPE'
AND HAOU.ORGANIZATION_ID = RT.ORGANIZATION_ID
AND TRUNC(SYSDATE) BETWEEN NVL(PAPF.EFFECTIVE_START_DATE, TRUNC(SYSDATE))
AND NVL(PAPF.EFFECTIVE_END_DATE, TRUNC(SYSDATE))
AND NOT EXISTS (
SELECT 1
FROM RCV_TRANSACTIONS RT1
WHERE RT1.TRANSACTION_TYPE = 'MATCH'
AND RT1.PARENT_TRANSACTION_ID = RT.TRANSACTION_ID
) ;