This REQ Receiving Exceptions Lines query retrieves receipt exception transactions specifically. It focuses on transactions where there are discrepancies or issues with the receipt of goods or services related to requisitions, such as mismatches in quantity or delivery issues. This data helps identify and resolve problems in the procurement process, ensuring accurate tracking and compliance with requisition orders.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves receipt transactions with exceptions with associated receiver and org details.
PU.USERNAME AS RECEIVER,
RSH.RECEIPT_NUM AS RECEIPT_NUM,
TO_CHAR(RT.TRANSACTION_DATE, 'DD/MON/YYYY HH24:MI:SS') AS RECEIPT_DATE,
PLC.DISPLAYED_FIELD AS TRANSACTION_TYPE,
ROUND(RT.QUANTITY, 1) AS TRANSACTION_QUANTITY,
RT.CURRENCY_CODE CURRENCY,
RT.INSPECTION_QUALITY_CODE AS QUALITY_CODE,
RT.COMMENTS AS COMMENTS,
PSV.VENDOR_NAME AS SUPPLIER_NAME,
HAOUX.NAME AS ORGANIZATION_NAME,
RT.SHIPMENT_HEADER_ID AS SHIPMENT_HEADER_ID,
HLA.REGION_1||' '||HLA.TOWN_OR_CITY||' '||HLA.REGION_2||' '||HLA.COUNTRY||' '||HLA.POSTAL_CODE RECEIVING_LOCATION,
RT.TRANSACTION_ID
FROM
PER_USERS PU,
RCV_TRANSACTIONS RT,
RCV_SHIPMENT_HEADERS RSH,
HR_ALL_ORGANIZATION_UNITS_X HAOUX,
POZ_LOOKUP_CODES PLC,
PER_ALL_PEOPLE_F PAPF,
POZ_SUPPLIERS_V PSV,
HR_LOCATIONS_ALL HLA
WHERE
PU.PERSON_ID = PAPF.PERSON_ID
AND RT.EMPLOYEE_ID = PAPF.PERSON_ID
AND PAPF.PERSON_ID = RT.EMPLOYEE_ID(+)
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
AND PLC.LOOKUP_CODE = RT.TRANSACTION_TYPE
AND RT.TRANSACTION_TYPE = 'RECEIVE'
AND RT.RECEIPT_EXCEPTION_FLAG = 'Y'
AND HAOUX.ORGANIZATION_ID = RT.ORGANIZATION_ID
AND PSV.VENDOR_ID=RT.VENDOR_ID
AND RT.LOCATION_ID=HLA.LOCATION_ID