This PO Receiving Transactions Register query retrieves purchasing and receiving details such as vendor name, item description, category, unit price, and quantity. It also includes delivery location and other procurement-related information for tracking and analysis.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- This query retrieves purchasing and receiving details including supplier, item, category, price, and delivery location
--Organization Details
HAOU.NAME ORGANIZATION_NAME,
HLAT.LOCATION_CODE DELIVER_TO_LOCATION,
--PO Information
PHA.SEGMENT1 PO_NUMBER,
PHA.TYPE_LOOKUP_CODE PO_TYPE,
PLA.LINE_NUM PO_LINE_NUMBER,
PLA.LINE_TYPE_ID PO_LINE_TYPE,
--Receipt information
RSH.RECEIPT_NUM RECEIPT_NUMBER,
RSH.SHIPMENT_NUM SHIPMENT_NUMBER,
RCT.CREATION_DATE RECEIPT_CREATION_DATE,
PU.USERNAME RECEIVER,
-- Supplier Information
PSV.VENDOR_NAME SUPPLIER_NAME,
PSV.SEGMENT1 SUPPLIER_NUMBER,
-- Transaction Details
RCT.TRANSACTION_DATE,
RCT.TRANSACTION_TYPE,
RCT.SOURCE_DOCUMENT_CODE,
RCT.DESTINATION_TYPE_CODE,
RCT.PRODUCT_TYPE,
RCT.PRODUCT_CATEGORY,
-- Item Details
DECODE(PLA.PURCHASE_BASIS,'TEMP LABOR',PLA.CONTRACTOR_FIRST_NAME || ' ' || PLA.CONTRACTOR_LAST_NAME,ESI.ITEM_NUMBER) ITEM,
RSL.ITEM_DESCRIPTION,
RSL.ITEM_REVISION REVISION,
-- Category Informatin
ECT.CATEGORY_NAME ,
PHA.SEGMENT1 || DECODE(PLA.LINE_NUM, NULL, NULL, '-' || PLA.LINE_NUM) || DECODE(PLL.SHIPMENT_NUM,NULL,NULL,'-' || PLL.SHIPMENT_NUM) DOC_NUM,
DECODE(PLA.MATCHING_BASIS,'AMOUNT',RCT.CURRENCY_CODE) UOM,
DECODE(PLA.MATCHING_BASIS,'AMOUNT',NULL) PO_UOM,
--Amount Details
PHA.CURRENCY_CODE CURRENCY,
NVL(RCT.PO_UNIT_PRICE, 0) PRICE,
RCT.QUANTITY,
NVL(RCT.REQUESTED_AMOUNT,0) REQUESTED_TRANSACTION_AMOUNT,
NVL(RCT.AMOUNT,0) TRANSACTION_AMOUNT
FROM
HR_ALL_ORGANIZATION_UNITS HAOU,
POZ_SUPPLIERS_V PSV,
HR_LOCATIONS_ALL HLA,
HR_LOCATIONS_ALL_TL HLAT,
PER_USERS PU,
RCV_TRANSACTIONS RCT,
PO_LINES_ALL PLA,
EGP_SYSTEM_ITEMS ESI,
EGP_CATEGORIES_TL ECT,
PO_HEADERS_ALL PHA,
PO_LINE_LOCATIONS_ALL PLL,
RCV_SHIPMENT_LINES RSL,
RCV_SHIPMENT_HEADERS RSH,
PO_DISTRIBUTIONS_ALL PDA,
-- PO_DOCUMENT_TYPES_ALL PDTA,
PER_ALL_PEOPLE_F PAPF1,
PER_ALL_PEOPLE_F PAPF2,
RCV_TRANSACTIONS PAR,
PER_ALL_PEOPLE_F PAPF3
WHERE
RCT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RCT.PO_HEADER_ID = PHA.PO_HEADER_ID
AND RCT.PO_LINE_ID = PLA.PO_LINE_ID
AND RCT.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND RCT.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID(+)
--AND PDA.PRC_BU_ID = PDTA.PRC_BU_ID
AND RSH.VENDOR_ID = PSV.VENDOR_ID
AND HLAT.LANGUAGE(+) = USERENV('LANG')
AND RCT.PARENT_TRANSACTION_ID = PAR.TRANSACTION_ID(+)
AND HAOU.ORGANIZATION_ID = RCT.ORGANIZATION_ID
AND PAPF1.PERSON_ID(+) = RCT.EMPLOYEE_ID
AND PAPF2.PERSON_ID(+) = PHA.AGENT_ID
AND PAPF3.PERSON_ID(+) = RCT.DELIVER_TO_PERSON_ID
AND PAPF1.PERSON_ID = PU.PERSON_ID
AND ESI.INVENTORY_ITEM_ID(+) = RSL.ITEM_ID
AND ESI.ORGANIZATION_ID = RCT.ORGANIZATION_ID
AND ECT.CATEGORY_ID = RSL.CATEGORY_ID
AND ECT.LANGUAGE(+) = USERENV('LANG')
AND RSH.RECEIPT_SOURCE_CODE = 'VENDOR'
AND TRUNC(SYSDATE) BETWEEN NVL(PAPF1.EFFECTIVE_START_DATE, TRUNC(SYSDATE))
AND NVL(PAPF1.EFFECTIVE_END_DATE, TRUNC(SYSDATE))
AND TRUNC(SYSDATE) BETWEEN NVL(PAPF2.EFFECTIVE_START_DATE, TRUNC(SYSDATE))
AND NVL(PAPF2.EFFECTIVE_END_DATE, TRUNC(SYSDATE))
AND TRUNC(SYSDATE) BETWEEN NVL(PAPF3.EFFECTIVE_START_DATE, TRUNC(SYSDATE))
AND NVL(PAPF3.EFFECTIVE_END_DATE, TRUNC(SYSDATE))
AND HAOU.LOCATION_ID = HLA.LOCATION_ID
AND HLA.LOCATION_DETAILS_ID = HLAT.LOCATION_DETAILS_ID;