In Oracle Fusion Procurement, several key tables store data related to purchasing, suppliers, purchase orders, and related procurement activities. Here’s an overview of some important tables in Oracle Fusion Procurement, along with sample SQL queries which will be able to leveraged and can retrieve data from these tables.
Key Tables in Oracle Fusion Procurement
PO_HEADERS_ALL
- Contains header information for purchase orders.
- Key columns:
PO_HEADER_ID, PO_NUMBER, VENDOR_ID, CREATION_DATE, STATUS, etc.
PO_LINES_ALL
- Stores line-level details for purchase orders.
- Key columns:
PO_LINE_ID, PO_HEADER_ID, LINE_ID, ITEM_ID, QUANTITY, UNIT_PRICE, etc.
PO_DISTRIBUTIONS_ALL
- Contains distribution information for purchase order lines.
- Key columns:
PO_DISTRIBUTION_ID, PO_LINE_ID, ACCOUNTING_DATE, DISTRIBUTION_AMOUNT, etc.
PO_SUPPLIERS
- Information about suppliers.
- Key columns:
SUPPLIER_ID, SUPPLIER_NAME, SUPPLIER_TYPE, STATUS, ADDRESS, etc.
PO_DOCUMENTS
- Stores documents related to purchase orders.
- Key columns:
DOCUMENT_ID, PO_HEADER_ID, DOCUMENT_TYPE, DOCUMENT_NAME, etc.
PO_AGREEMENTS
- Contains details about procurement agreements.
- Key columns:
AGREEMENT_ID, AGREEMENT_NUMBER, SUPPLIER_ID, START_DATE, END_DATE, etc.
PO_AGREEMENT_LINES
- Details of lines in procurement agreements.
- Key columns:
AGREEMENT_LINE_ID, AGREEMENT_ID, ITEM_ID, QUANTITY, PRICE, etc.
PO_APPROVALS
- Contains approval information for purchase orders.
- Key columns:
APPROVAL_ID, PO_HEADER_ID, APPROVAL_STATUS, APPROVER_ID, APPROVAL_DATE, etc.
PO_RECEIPTS
- Stores information about goods receipts against purchase orders.
- Key columns:
RECEIPT_ID, PO_HEADER_ID, RECEIPT_DATE, QUANTITY_RECEIVED, ITEM_ID, etc.
PO_PAYMENTS
- Contains details about payments made to suppliers.
- Key columns:
PAYMENT_ID, PO_HEADER_ID, PAYMENT_AMOUNT, PAYMENT_DATE, PAYMENT_STATUS, etc.
Sample SQL Queries
1. Retrieve Purchase Order Header Information
SELECT
p.PO_HEADER_ID,
p.PO_NUMBER,
p.VENDOR_ID,
v.SUPPLIER_NAME,
p.CREATION_DATE,
p.STATUS
FROM
PO_HEADERS_ALL p
JOIN PO_SUPPLIERS v ON p.VENDOR_ID = v.SUPPLIER_ID
WHERE
p.CREATION_DATE BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-12-31', 'YYYY-MM-DD')
ORDER BY
p.CREATION_DATE DESC;
2. Get Purchase Order Line Details
SELECT
l.PO_LINE_ID,
l.PO_HEADER_ID,
l.LINE_ID,
l.ITEM_ID,
i.ITEM_NAME,
l.QUANTITY,
l.UNIT_PRICE
FROM
PO_LINES_ALL l
JOIN INV_MTL_SYSTEM_ITEMS_B i ON l.ITEM_ID = i.ITEM_ID
WHERE
l.PO_HEADER_ID = :PO_HEADER_ID
ORDER BY
l.LINE_ID;
3. Retrieve Purchase Order Distributions
SELECT
d.PO_DISTRIBUTION_ID,
d.PO_LINE_ID,
d.ACCOUNTING_DATE,
d.DISTRIBUTION_AMOUNT,
l.ITEM_ID
FROM
PO_DISTRIBUTIONS_ALL d
JOIN PO_LINES_ALL l ON d.PO_LINE_ID = l.PO_LINE_ID
WHERE
d.PO_HEADER_ID = :PO_HEADER_ID
ORDER BY
d.ACCOUNTING_DATE;
4. Get Supplier Information
SELECT
s.SUPPLIER_ID,
s.SUPPLIER_NAME,
s.SUPPLIER_TYPE,
s.STATUS,
s.ADDRESS
FROM
PO_SUPPLIERS s
WHERE
s.STATUS = 'Active'
ORDER BY
s.SUPPLIER_NAME;
5. Retrieve Purchase Order Receipts
SELECT
r.RECEIPT_ID,
r.PO_HEADER_ID,
p.PO_NUMBER,
r.RECEIPT_DATE,
r.QUANTITY_RECEIVED,
l.ITEM_ID
FROM
PO_RECEIPTS r
JOIN PO_HEADERS_ALL p ON r.PO_HEADER_ID = p.PO_HEADER_ID
JOIN PO_LINES_ALL l ON r.PO_HEADER_ID = l.PO_HEADER_ID
WHERE
r.RECEIPT_DATE BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-12-31', 'YYYY-MM-DD')
ORDER BY
r.RECEIPT_DATE DESC;
6. List Procurement Agreements and Lines
SELECT
a.AGREEMENT_ID,
a.AGREEMENT_NUMBER,
a.START_DATE,
a.END_DATE,
s.SUPPLIER_NAME
FROM
PO_AGREEMENTS a
JOIN PO_SUPPLIERS s ON a.SUPPLIER_ID = s.SUPPLIER_ID
WHERE
a.START_DATE BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD') AND TO_DATE('2024-12-31', 'YYYY-MM-DD')
ORDER BY
a.START_DATE;
SELECT
al.AGREEMENT_LINE_ID,
al.ITEM_ID,
i.ITEM_NAME,
al.QUANTITY,
al.PRICE
FROM
PO_AGREEMENT_LINES al
JOIN INV_MTL_SYSTEM_ITEMS_B i ON al.ITEM_ID = i.ITEM_ID
WHERE
al.AGREEMENT_ID = :AGREEMENT_ID
ORDER BY
al.ITEM_ID;
Notes
- Refer to the Oracle documentations for any changes to the table names and column names.
- For large datasets, consider optimizing queries with appropriate indexing and filtering to enhance performance.
These tables and queries should help you effectively manage and analyze procurement data within Oracle Fusion Applications and can be leveraged for building custom requirements.
