SQL Queries tables in Oracle Fusion Procurement

 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

1. PO_HEADERS_ALL

   • Contains header information for purchase orders.
   • Key columns: PO_HEADER_ID, PO_NUMBER, VENDOR_ID, CREATION_DATE, STATUS, etc.

2. 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.

3. PO_DISTRIBUTIONS_ALL

   • Contains distribution information for purchase order lines.
   • Key columns: PO_DISTRIBUTION_ID, PO_LINE_ID, ACCOUNTING_DATE, DISTRIBUTION_AMOUNT, etc.

4. PO_SUPPLIERS

   • Information about suppliers.
   • Key columns: SUPPLIER_ID, SUPPLIER_NAME, SUPPLIER_TYPE, STATUS, ADDRESS, etc.

5. PO_DOCUMENTS

   • Stores documents related to purchase orders.
   • Key columns: DOCUMENT_ID, PO_HEADER_ID, DOCUMENT_TYPE, DOCUMENT_NAME, etc.

6. PO_AGREEMENTS

   • Contains details about procurement agreements.
   • Key columns: AGREEMENT_ID, AGREEMENT_NUMBER, SUPPLIER_ID, START_DATE, END_DATE, etc.

7. PO_AGREEMENT_LINES

   • Details of lines in procurement agreements.
   • Key columns: AGREEMENT_LINE_ID, AGREEMENT_ID, ITEM_ID, QUANTITY, PRICE, etc.

8. PO_APPROVALS

   • Contains approval information for purchase orders.
   • Key columns: APPROVAL_ID, PO_HEADER_ID, APPROVAL_STATUS, APPROVER_ID, APPROVAL_DATE, etc.

9. PO_RECEIPTS

   • Stores information about goods receipts against purchase orders.
   • Key columns: RECEIPT_ID, PO_HEADER_ID, RECEIPT_DATE, QUANTITY_RECEIVED, ITEM_ID, etc.

10. 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.