This PO Receiving Value By Destination Account Report (With PO Distributions Transaction) query retrieves purchase order-based receipt transactions, showing received quantities and calculated purchase values.It also associates each transaction with the appropriate destination account, including UOM and location details.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves purchase order-based receipt transactions with destination accounts, quantities, and purchase values.
-- Purchase Order 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 & Receiving Details
RSL.ITEM_DESCRIPTION,
RSL.ITEM_REVISION,
RSL.VENDOR_LOT_NUM VENDOR_LOT_NUMBER,
RSL.CUSTOMER_ITEM_NUM CUSTOMER_ITEM_NUMBER,
RSL.TRUCK_NUM TRUCK_NUMBER,
RSL.CONTAINER_NUM CONTAINER_NUM,
RSL.BAR_CODE_LABEL,
RSL.INVOICE_STATUS_CODE INVOICE_STATUS,
RT.TRANSACTION_ID RECEIVING_TRANSACTION_ID,
-- 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 Info
PLC.DISPLAYED_FIELD DESTINATION,
FND_FLEX_EXT.GET_SEGS(
'GL',
'GL#',
GCC.CHART_OF_ACCOUNTS_ID,
GCC.CODE_COMBINATION_ID
) ACCOUNT,
PHA.CURRENCY_CODE CURRENCY,
-- Quantity & Purchase Value
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,
RCV_SUPPLY RS,
POZ_LOOKUP_CODES PLC,
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 PLA.CATEGORY_ID = ECB.CATEGORY_ID
AND PDA.DELIVER_TO_LOCATION_ID = HLA.LOCATION_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 = 'POR_DESTINATION_TYPE'
;