PO Tables in Oracle Apps R12: A Complete Guide

Purchasing PO Tables in Oracle Apps R12 Diagram

PO Tables in Oracle Apps R12

Oracle Purchasing(PO) consists of several interconnected tables managing the entire procurement lifecycle—from requisition to payment. Understanding PO tables in Oracle Apps R12 is crucial for developers, DBAs, and functional consultants involved in customizations, reporting, and troubleshooting.

The PO module tables can be categorized based on their function:

Table of Contents

Requisition Table in Oracle Apps R12

Purchasing Base Tables in Oracle Apps R12

Buyer Table in Oracle Apps R12

PO Approval Tables

Purchasing Interface Tables

Requisition Interface Tables

Receiving Shipments Table

Purchasing(PO) Receipt Tables

Receiving Interface Tables

PO Type and Line Types Tables

Purchasing Key Table Relationships

Diagram: PO Module Table Relationships

Practical Applications of Purchasing Tables

Best Practices for Working with PO Tables

FAQs

1. PO Requisition Table in Oracle Apps R12

PO requisition table in Oracle Apps R12 store data related to purchase requests before they are converted into purchase orders. These tables help track requisition details, approval status, and distribution information.

  • requisition_header_id: Primary key, links to lines and distributions
  • segment1: Requisition number (displayed to users)
  • preparer_id: Employee who prepared the requisition
  • authorization_status: Approval status (e.g., INCOMPLETE, APPROVED)
  • org_id: Operating unit ID
  • description: Description of requisition purpose

PO_REQUISITION_LINES_ALL: Stores line-level requisition details (item description, quantity, UOM, need-by date).

  • requisition_line_id: Primary key for the line
  • item_id: Inventory item reference
  • quantity: Quantity requested
  • unit_price: Estimated unit price
  • need_by_date: Required date for delivery
  • item_description: Free text item description

PO_REQUISITION_DISTRIBUTIONS_ALL: Holds distribution data for requisition lines (account codes, project details, charge account).

  • distribution_id: Primary key
  • requisition_line_id: Link to requisition line
  • charge_account_id: Accounting distribution
  • quantity_ordered: Ordered quantity at distribution level
  • code_combination_id: GL accounting code

2. PO Base Tables in Oracle Apps R12

PO base tables in Oracle Apps R12 contain key information regarding purchase orders, including headers, line items, distributions, and approvals. These tables are essential for tracking the procurement process from PO creation to receipt and payment.

PO_HEADERS_ALL: Stores PO header details (PO number, supplier, buyer, terms).

  • po_header_id: Unique header ID
  • segment1: PO Number
  • vendor_id: Supplier reference
  • agent_id: Buyer reference (linked to PO_AGENTS)
  • type_lookup_code: Type of PO (e.g., STANDARD, BLANKET)
  • org_id: Operating unit
  • authorization_status: Approval state

PO_LINES_ALL: Holds PO line details (item, quantity, price, ship-to location).

  • po_line_id: Unique PO line ID
  • po_header_id: Link to PO header
  • line_num: Line number
  • item_id: Inventory item
  • quantity: Quantity ordered
  • unit_price: Price per unit

PO_LINE_LOCATIONS_ALL: Stores PO Shipments (PO line location) details (ship-to and bill-to addresses).

  • line_location_id: Unique ID per shipment
  • po_line_id: Link to PO line
  • ship_to_location_id: Destination location
  • need_by_date: Expected delivery date
  • quantity: Shipment quantity

PO_DISTRIBUTIONS_ALL: po_distributions_all stores distribution details for PO line locations or PO Shipments (account codes, project details, charge account).

  • po_distribution_id: Unique ID
  • line_location_id: Link to shipment
  • code_combination_id: Accounting code
  • quantity_ordered: Distribution quantity
  • charge_account_id: Accounting charge
  • accrual_account_id: Accrual account for purchasing

PO_ASL_ATTRIBUTES: Contains Approved Supplier List attributes.

  • asl_attribute_id: Unique ID for the attribute row
  • vendor_id: Supplier ID
  • item_id: Inventory item ID
  • approved_flag: Indicates if the supplier is approved for the item
  • enable_date: Date from which the supplier-item is valid
  • disable_date: Date after which the record becomes inactive
  • organization_id: Inventory organization ID
  • source_document_type_id: Links to sourcing rules or blanket agreements
  • source_document_id: References the sourcing document (e.g., BPA or contract)
  • source_type_code: Type of source (e.g., SOURCED, CONTRACT)
  • source_document_line_id: Line-level reference in sourcing document

PO_ACTION_HISTORY: Tracks purchase order actions (approval, changes, cancellations).

  • object_id: Reference ID of the PO object
  • action_code: Action taken (e.g., APPROVE, REJECT)
  • action_date: Timestamp of the action

3. PO Buyer Table in Oracle Apps R12

The PO buyer information in Oracle Apps is stored in the PO module tables and is linked to HR tables to fetch employee details. This helps in tracking purchase orders based on buyer assignment.

The buyer information is stored in the PO_HEADERS_ALL table under the agent_id column, which links to PO_AGENTS for employee details.

  • agent_id: Primary key
  • employee_id: Employee linked as buyer
  • agent_name: Name of buyer

4. PO Approval Tables in Oracle Apps R12

PO approval tables in Oracle Apps R12 store data related to the purchase order approval workflow. They help in tracking approval hierarchy, workflow attributes, and action history.

  • WF_ITEM_ATTRIBUTE_VALUES: Stores workflow item attributes related to approvals.
  • WF_ITEMS: Holds workflow item information.
  • PO_APPROVAL_LISTS_HEADERS: Stores approval list headers.
  • PO_APPROVAL_LISTS_LINES: Contains approval list line details.
  • PO_ACTION_HISTORY: Tracks approval and rejection actions on POs.

5. PO Interface Tables in Oracle Apps R12

PO interface tables in Oracle Apps R12 store transactional data for import and integration between different modules. These tables help in loading purchase orders and requisitions from external systems.

  • PO_HEADERS_INTERFACE: Interface table for PO headers.
  • PO_LINES_INTERFACE: Interface table for PO lines and PO Shipments
  • PO_DISTRIBUTIONS_INTERFACE: Interface table for PO distributions.

6. PO Requisition Interface Tables in Oracle Apps R12

These PO Requisition interface tables handle requisition data import from external sources into Oracle Applications. They allow bulk data processing for purchase requisitions.

  • PO_REQUISITIONS_INTERFACE_ALL: Interface table for requisition header and lines.
  • PO_REQ_DIST_INTERFACE: Interface table for requisition distributions.

7. PO Shipments Table in Oracle Apps R12

PO shipment tables manage data related to the receipt and shipment of purchase orders. These tables ensure accurate tracking of received goods and their status.

RCV_SHIPMENT_HEADERS: Stores receipt header details (receipt number, date).

  • shipment_header_id: Unique header ID
  • receipt_num: Receipt document number
  • vendor_id: Vendor for shipment
  • receipt_date: Date of receipt

RCV_SHIPMENT_LINES: Holds line-level receipt details (item, quantity, PO reference).

  • shipment_line_id: Unique line ID
  • item_id: Received item
  • quantity_received: Total quantity received

RCV_TRANSACTIONS: Tracks material transactions related to receipts.

  • transaction_id: Unique ID
  • shipment_line_id: Link to shipment line
  • transaction_type: Type (e.g., RECEIVE, DELIVER)
  • transaction_date: Date of transaction

8. PO Receipt Tables in Oracle Apps R12

Receipt tables in Oracle Apps store information about goods received against purchase orders. These tables help in verifying supplier deliveries and recording inventory transactions.

  • RCV_TRANSACTIONS: Tracks receipt transactions.
  • RCV_SHIPMENT_HEADERS: Stores receipt shipment headers.
  • RCV_SHIPMENT_LINES: Contains receipt line details.

9. PO Receiving Interface Tables in Oracle Apps R12

PO Receiving interface tables in Oracle Apps handle the data import process for receipts and transactions in the procurement module.

RCV_TRANSACTIONS_INTERFACE: Stores transaction details related to receipts before processing.

RCV_HEADERS_INTERFACE: Stores receipt header information before import into base tables.

10. PO and PO Line Types Tables in Oracle Apps R12

These tables store information about the types of purchase orders and purchase order lines used in Oracle Applications R12.

  • PO_DOCUMENT_TYPES_ALL_B: Stores the different types of purchase documents.
  • PO_LINE_TYPES_B: Contains information about the line types associated with purchase orders, such as goods, services, and expense lines.

Key Table Relationships

Understanding the relationships between these tables helps in creating accurate queries and reports.

Table 1RelationshipTable 2
PO_REQUISITION_HEADERS_ALLREQUISITION_HEADER_IDPO_REQUISITION_LINES_ALL
PO_REQUISITION_LINES_ALLREQUISITION_LINE_IDPO_REQUISITION_DISTRIBUTIONS_ALL
PO_HEADERS_ALLPO_HEADER_IDPO_LINES_ALL
PO_LINES_ALLPO_LINE_IDPO_DISTRIBUTIONS_ALL
PO_VENDORSVENDOR_IDPO_VENDOR_SITES_ALL
PO_HEADERS_ALLAGENT_IDPO_AGENTS
PO_HEADERS_ALLVENDOR_IDPO_VENDORS / AP_SUPPLIERS
RCV_SHIPMENT_HEADERSSHIPMENT_HEADER_IDRCV_SHIPMENT_LINES

Diagram: PO Module Table Relationships

+----------------------------+
| PO_REQUISITION_HEADERS_ALL |
+-------------+--------------+
              |
              | REQUISITION_HEADER_ID
              v
+----------------------------+
| PO_REQUISITION_LINES_ALL   |
+-------------+--------------+
              |
              | REQUISITION_LINE_ID
              v
+----------------------------+
| PO_REQUISITION_DISTRIBUTIONS_ALL |
+----------------------------------+

(Similar relationships exist for PO_HEADERS_ALL, PO_LINES_ALL, etc.)

Practical Applications

Understanding PO tables enables:

  • Custom Reports: Extract data for requisition status, PO spending, supplier performance.
  • Troubleshooting: Identify procurement issues by analyzing table data.
  • Custom Extensions: Develop enhancements for the PO module.
  • Data Migration: Move data from legacy systems.
  • Performance Tuning: Optimize queries for better performance.

Best Practices for Working with PO Tables

  • Use descriptive aliases in SQL queries.
  • Refer to Oracle documentation for in-depth details.
  • Understand table relationships before writing queries.
  • Test queries before deploying in production.
  • Use SQL Developer to visualize data and relationships.
  • Use Oracle eTRM for details of table structure of Oracle Applications.

Frequently Asked Questions (FAQs)

By mastering PO tables, you can improve procurement processes, enhance reporting, and optimize Oracle Applications R12 usage.

1. What is the purpose of the PO_HEADERS_ALL table?

PO_HEADERS_ALL stores header-level information for purchase orders, including PO number, supplier, buyer, and terms.

2. How do I find all requisitions created by a specific user?

You can query the PO_REQUISITION_HEADERS_ALL table using the PREPARER_ID field to filter by a specific user.

3. How are purchase order lines linked to distributions?

PO_LINES_ALL and PO_DISTRIBUTIONS_ALL are linked through the PO_LINE_ID column.

4. What table contains supplier contact details?

Supplier contact details are stored in PO_VENDOR_SITES_ALL and AP_SUPPLIER_SITES_ALL.

5. How can I track purchase order approvals?

Approval history can be found in the PO_ACTION_HISTORY table, which records approval actions, rejections, and modifications.

6. What are the key tables for receipt tracking?

Receipt tracking involves:
RCV_SHIPMENT_HEADERS: Receipt header details.
RCV_SHIPMENT_LINES: Receipt line-level details.
RCV_TRANSACTIONS: Tracks material movements.

7. How can I optimize performance when querying PO tables?

Use appropriate indexes on key columns.
Avoid SELECT *; specify only required columns.
Use table joins efficiently by leveraging indexed columns.
Analyze and tune queries using EXPLAIN PLAN.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.