This PO Internal Order Receipts Register query retrieves internal order receipt transactions related to requisition sources, including details on items, pricing, receivers, and delivery locations. It provides a comprehensive overview of internal order receipts within the requisition process.
#sqlquery
SELECT
-- SQL4Fusion (An Orbit Analytics Project)
-- This query retrieves internal order receipt transactions from requisition sources, including item, price, receiver, and delivery location
-- Organization Details
HOU1.NAME ORGANIZATION_NAME,
HOU.NAME || PRLA.SOURCE_SUBINVENTORY ORGANIZATION_SOURCE,
-- Requisition Details
PRH.REQUISITION_NUMBER,
PRH.DESCRIPTION REQUISITION_DESCRIPTION,
PRH.DOCUMENT_STATUS,
PRLA.LINE_NUMBER,
PRLA.UOM_CODE,
PRLA.DESTINATION_TYPE_CODE,
-- Receipt and Transaction Info
RSH.RECEIPT_NUM RECIPT_NUMBER,
RCT.TRANSACTION_DATE,
RCT.CREATION_DATE RECIPT_CREATION_DATE,
-- Receiver and Location
PU.USERNAME RECEIVER,
HLA.COUNTRY,
HLA.LOCATION_NAME,
HLAT.LOCATION_CODE DELIVER_TO_LOCATION,
HLA.POSTAL_CODE,
-- Item Information
ESI.ITEM_NUMBER,
PRLA.PRODUCT_TYPE,
PRLA.PRODUCT_CATEGORY,
ESI.SOURCE_TYPE,
PRLA.ITEM_SOURCE,
ESI.BOM_ITEM_TYPE,
RSL.ITEM_REVISION,
ECT.CATEGORY_NAME,
RSL.ITEM_DESCRIPTION,
-- Conversion information
PRLA.CURRENCY_CODE CURRENCY,
PRLA.RATE,
PRLA.RATE_DATE,
PRLA.RATE_TYPE,
-- Amount Information
ROUND(PRLA.QUANTITY, 2) QUANTITY,
NVL(PRLA.UNIT_PRICE, 0) PRICE,
PRLA.AMOUNT REQUISITION_LINE_AMOUNT
from
POR_REQUISITION_LINES_ALL PRLA,
POR_REQUISITION_HEADERS PRH,
RCV_TRANSACTIONS RCT,
RCV_SHIPMENT_LINES RSL,
RCV_SHIPMENT_HEADERS RSH,
PO_DOCUMENT_TYPES PDT,
EGP_CATEGORIES_TL ECT,
EGP_SYSTEM_ITEMS_B ESI,
PER_ALL_PEOPLE_F PAPF1,
PER_ALL_PEOPLE_F PAPF2,
PER_USERS PU,
HR_ORGANIZATION_UNITS HOU,
HR_ORGANIZATION_UNITS HOU1,
HR_LOCATIONS_ALL HLA,
HR_LOCATIONS_ALL_TL HLAT
WHERE
1 = 1
AND PRLA.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND RSL.REQUISITION_LINE_ID(+) = PRLA.REQUISITION_LINE_ID
AND RCT.SHIPMENT_LINE_ID(+) = RSL.SHIPMENT_LINE_ID
AND RSH.SHIPMENT_HEADER_ID(+) = RSL.SHIPMENT_HEADER_ID
AND PRH.REQ_BU_ID = PDT.PRC_BU_ID(+)
AND PDT.DOCUMENT_TYPE_CODE = 'SOURCING RFI'
AND ECT.CATEGORY_ID = PRLA.CATEGORY_ID
AND ECT.LANGUAGE(+) = USERENV('LANG')
AND PRLA.ITEM_ID = ESI.INVENTORY_ITEM_ID(+)
AND ESI.DEFAULT_SO_SOURCE_TYPE = 'INTERNAL'
AND PAPF1.PERSON_ID = PRLA.REQUESTER_ID
AND TRUNC(SYSDATE) BETWEEN NVL(PAPF1.EFFECTIVE_START_DATE, TRUNC(SYSDATE))
AND NVL(PAPF1.EFFECTIVE_END_DATE, TRUNC(SYSDATE))
AND PAPF2.PERSON_ID = PRH.PREPARER_ID
AND TRUNC(SYSDATE) BETWEEN NVL(PAPF2.EFFECTIVE_START_DATE, TRUNC(SYSDATE))
AND NVL(PAPF2.EFFECTIVE_END_DATE, TRUNC(SYSDATE))
AND PU.PERSON_ID(+) = PAPF1.PERSON_ID
AND HOU.ORGANIZATION_ID = PRLA.DESTINATION_ORGANIZATION_ID
AND RSH.ORGANIZATION_ID = HOU1.ORGANIZATION_ID(+)
AND HOU.LOCATION_ID = HLA.LOCATION_ID
AND HLA.LOCATION_DETAILS_ID = HLAT.LOCATION_DETAILS_ID
AND HLAT.LANGUAGE(+) = USERENV('LANG')