PO Receiving Exceptions Lines (Oracle Fusion SQL)

Supply Chain & Manufacturing (SCM) Purchase Order (PO-Fusion-SQL's)
1

This PO Receiving Exceptions Lines query retrieves receipt transactions linked to purchase orders, including related user and organizational details. It offers insight into receiving activities and accountability.

#sqlquery

SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves receipt transactions from PO with associated user and org details.
-- Organization Details
  HAOUX.NAME AS ORGANIZATION_NAME,
-- Transaction Details
  RT.TRANSACTION_TYPE,
  TO_CHAR(RT.TRANSACTION_DATE, 'DD/MON/YYYY HH24:MI:SS') TRANSACTION_DATE,
  RT.PO_REVISION_NUM PO_REVISION_NUMBER,
-- Receipt Information  
  PU.USERNAME RECEIVER,
  RSH.RECEIPT_NUM RECEIPT_NUMBER,
  RT.INSPECTION_QUALITY_CODE ,
  RT.COMMENTS ,
  RSH.RECEIPT_SOURCE_CODE RECEIPT_SOURCE,
  RSH.SHIPMENT_NUM SHIPMENT_NUMBER,
  RSH.CREATION_DATE RECEIPT_CREATION_DATE,
  RSH.SHIPPED_DATE,
-- Amount Informaion
  ROUND(RT.QUANTITY, 2) TRANSACTION_QUANTITY,
  RT.AMOUNT TRANSACTION_AMOUNT
FROM
  RCV_SHIPMENT_HEADERS RSH,
  RCV_TRANSACTIONS RT,
  PER_ALL_PEOPLE_F PAPF,
  PER_USERS PU,
  HR_ALL_ORGANIZATION_UNITS_X HAOUX,
  POZ_LOOKUP_CODES PLC
WHERE
  1 = 1
  AND RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
  AND RT.TRANSACTION_TYPE = 'RECEIVE'
  AND RT.EMPLOYEE_ID = PAPF.PERSON_ID(+)
  AND PU.PERSON_ID(+) = PAPF.PERSON_ID
  AND HAOUX.ORGANIZATION_ID = RT.ORGANIZATION_ID
  AND PLC.LOOKUP_CODE = RT.TRANSACTION_TYPE
  AND PLC.LOOKUP_TYPE = 'RCV_TRANSACTION_TYPE'