This PO Receiving Value By Destination Account Report (With RCV Supply Transaction) query retrieves receipt transactions originating from Purchase Orders (POs), detailing quantities received and corresponding purchase values.It links each transaction to its destination account, enabling analysis of receiving activity by account and destination type.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves PO-based receipt transactions with quantities and purchase values per destination account.
-- PO Details
PHA.SEGMENT1 PO_NUMBER,
PHA.DOCUMENT_STATUS,
PHA.TYPE_LOOKUP_CODE PO_TYPE,
PHA.COMMENTS,
-- PO Line Details
PLA.LINE_NUM LINE_NUMBER,
PLA.LINE_STATUS,
PLA.UNIT_PRICE,
-- Item Details
RSL.ITEM_DESCRIPTION,
RSL.ITEM_REVISION,
RSL.VENDOR_LOT_NUM SUPPLIER_LOT_NUMBER,
RSL.CUSTOMER_ITEM_NUM CUSTOMER_ITEM_NUMBER,
RSL.TRUCK_NUM TRUCK_NUMBER,
RSL.CONTAINER_NUM AS CONTAINER_NUMBER,
RSL.BAR_CODE_LABEL,
RSL.INVOICE_STATUS_CODE INVOICE_STATUS,
-- Transaction Details
RT.TRANSACTION_TYPE,
RT.TRANSACTION_DATE,
RT.UOM_CODE,
RT.SOURCE_DOCUMENT_CODE,
RT.DESTINATION_TYPE_CODE,
-- Product Information
RT.PRODUCT_CATEGORY,
RT.PRODUCT_TYPE,
-- Location & Category
HLA.LOCATION_CODE DELIVER_TO_LOCATION,
ECB.CATEGORY_CODE CATEGORY,
-- Destination Information
PLC.DISPLAYED_FIELD DESTINATION,
FND_FLEX_EXT.GET_SEGS(
'GL',
'GL#',
GCC.CHART_OF_ACCOUNTS_ID,
GCC.CODE_COMBINATION_ID
) ACCOUNT,
-- Calculated Quantities & Values
PHA.CURRENCY_CODE CURRENCY,
ROUND(
RS.TO_ORG_PRIMARY_QUANTITY * NVL(PDA.QUANTITY_ORDERED, 0) / NVL(PLL.QUANTITY, 1),
2
) QUANTITY,
ROUND(
PLA.UNIT_PRICE * ROUND(
RS.TO_ORG_PRIMARY_QUANTITY * NVL(PDA.QUANTITY_ORDERED, 0) / NVL(PLL.QUANTITY, 1),
2
),
2
) PURCHASE_VALUE
FROM
PO_DISTRIBUTIONS_ALL PDA,
PO_LINE_LOCATIONS_ALL PLL,
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
RCV_TRANSACTIONS RT,
GL_CODE_COMBINATIONS GCC,
POZ_LOOKUP_CODES PLC,
RCV_SUPPLY RS,
HR_LOCATIONS_ALL HLA,
RCV_SHIPMENT_LINES RSL,
EGP_CATEGORIES_B ECB
WHERE
RS.SUPPLY_TYPE_CODE = 'RECEIVING'
AND RS.SUPPLY_SOURCE_ID = RT.TRANSACTION_ID
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RT.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
AND RT.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND RT.PO_LINE_ID = PLA.PO_LINE_ID
AND RT.PO_HEADER_ID = PHA.PO_HEADER_ID
AND PDA.DELIVER_TO_LOCATION_ID = HLA.LOCATION_ID(+)
AND ECB.CATEGORY_ID = PLA.CATEGORY_ID
AND GCC.CODE_COMBINATION_ID = NVL(PDA.DEST_CHARGE_ACCOUNT_ID,PDA.CODE_COMBINATION_ID )
AND PDA.DESTINATION_TYPE_CODE IN ('INVENTORY', 'EXPENSE', 'SHOP FLOOR')
AND PDA.DESTINATION_TYPE_CODE = PLC.LOOKUP_CODE
AND PLC.LOOKUP_TYPE = 'RCV_DESTINATION_TYPE';