This PO Invoice Price Variance By Vendor query retrieves vendor-wise invoice and PO details, highlighting price variances and associated accounting information filtered by item type and location for accurate financial analysis.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves vendor-wise invoice and purchase order variances along with accounting details.
PSV.VENDOR_NAME SUPPLIER_NAME,
PHA.SEGMENT1 PO_NUMBER,
PHA.TYPE_LOOKUP_CODE PO_TYPE,
PLA.LINE_NUM LINE_NUMBER,
ESIB.ITEM_NUMBER,
ECT.CATEGORY_NAME,
PHA.CURRENCY_CODE,
PLA.UNIT_PRICE PO_UNIT_PRICE,
AIA.INVOICE_NUM INVOICE_NUMBER,
ROUND(AIDA.QUANTITY_INVOICED, 20) QUANTITY_INVOICED,
AIDA.UNIT_PRICE INVOICE_UNIT_PRICE,
AIA.INVOICE_AMOUNT INVOICE_AMOUNT,
NVL(AIDA.UNIT_PRICE,0) - NVL(PLA.UNIT_PRICE,0) VARIANCE_AMOUNT,
AIDA1.BASE_AMOUNT BASE_INVOICE_PRICE_VARIANCE,
HLA.LOCATION_NAME,
AIDA.ACCOUNTING_DATE,
FND_FLEX_EXT.GET_SEGS('GL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, GCC.CODE_COMBINATION_ID) CHARGE_ACCOUNT,
FND_FLEX_EXT.GET_SEGS('GL', 'GL#', GCC1.CHART_OF_ACCOUNTS_ID, GCC1.CODE_COMBINATION_ID) VARIANCE_ACCOUNT
FROM
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
PO_DISTRIBUTIONS_ALL PDA,
AP_INVOICES_ALL AIA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_LINES_ALL PLA,
PO_HEADERS_ALL PHA,
POZ_SUPPLIERS_V PSV,
EGP_SYSTEM_ITEMS_B ESIB,
EGP_CATEGORIES_TL ECT,
GL_CODE_COMBINATIONS GCC1,
GL_CODE_COMBINATIONS GCC,
HR_LOCATIONS_ALL HLA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA1
WHERE
AIDA.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID
AND AIDA.INVOICE_ID = AIA.INVOICE_ID
AND PDA.LINE_LOCATION_ID = PLLA.LINE_LOCATION_ID
AND PDA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PDA.PO_LINE_ID = PLA.PO_LINE_ID
AND PDA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND PHA.VENDOR_ID = PSV.VENDOR_ID
AND PLA.ITEM_ID = ESIB.INVENTORY_ITEM_ID
AND PLLA.SHIP_TO_ORGANIZATION_ID = ESIB.ORGANIZATION_ID
AND PLA.CATEGORY_ID = ECT.CATEGORY_ID
AND ECT.LANGUAGE = USERENV('LANG')
AND GCC.CODE_COMBINATION_ID = PDA.CODE_COMBINATION_ID
AND GCC1.CODE_COMBINATION_ID = PDA.VARIANCE_ACCOUNT_ID
AND AIDA1.RELATED_ID = AIDA.INVOICE_DISTRIBUTION_ID
AND AIDA1.LINE_TYPE_LOOKUP_CODE = 'IPV' --Includes only invoice distribution lines related to invoice price variance (IPV) entries.
AND PLLA.SHIP_TO_LOCATION_ID = HLA.LOCATION_ID
AND AIDA1.AMOUNT IS NOT NULL ;