Prepared by : Ishtiaq Khan

PO AP INV

PO PAYABLE INVOICES ITEM

PO_HEADERS_ALL AP_INVOICES_ALL MTL_SYSTEM_ITEMS_B
PO_LINES_ALL AP_INVOICE_DISTRIBUTIONS_ALL MTL_ITEM_REVISIONS_B

PO_LINE_LOCATIONS_ALL PAYMENTS TO INVOICE ITEM CATEGORIES

PO_DISTRIBUTIONS_ALL AP_PAYMENT_DISTRIBUTIONS_ALL MTL_CATEGORIES_B
BUYER AP_PAYMENT_HISTORY_ALL MTL_CATEGORY_SETS_B
PO_AGENTS AP_PAYMENT_SCHEDULES_ALL MTL_ITEM_CATEGORIES
REQUISITIONS AP_INVOICE_PAYMENTS_ALL TRANSACTIONS

PO_REQUISITION_HEADERS_ALL MTL_MATERIAL_TRANSACTIONS

PO_REQUISITION_LINES_ALL MTL_TRANSACTION_TYPES
PO_REQ_DISTRIBUTIONS_ALL LOCATION AND ON HAND QTY
VENDORS MTL_ITEM_LOCATIONS
PO_VENDORS MTL_ONHAND_QUANTITIES_DETAIL

PO_VENDOR_SITES_ALL LOT AND SERIAL NUMBR

PO_VENDOR_CONTACTS MTL_SERIAL_NUMBERS

RECEVING MTL_LOT_NUMBERS
RCV_TRANSACTIONS
RCV_SHIPMENT_HEADERS
RCV_SHIPMENT_LINES
 Table 1: PO_HEADERS_ALL
Description: Document headers (for purchase orders, purchase agreements,
quotations, RFQs)
PO_HEADERS_ALL contains header information for your purchasing
documents. You need one row for each document you create. There are six types
of documents that use PO_HEADERS_ALL: RFQs,Quotations
Standard purchase orders, Planned purchase orders, Blanket purchase orders,
Contracts.
Each row contains buyer information, supplier information, brief notes, foreign
currency information, terms and conditions information, and the status of the
document.
PO_HEADER_ID is the unique system-generated primary key and is
invisible to the user. SEGMENT1 is the system-assigned number you use
to identify the document in forms and reports. SEGMENT1 is not unique for the
entire table. Different document types can share the same numbers. You can
uniquely identify a row in PO_HEADERS_ALL using ORG_ID, SEGMENT1, and
TYPE_LOOKUP_CODE, or using PO_HEADER_ID.
 Table 1: PO_HEADERS_ALL
If APPROVED_FLAG is 'Y', the purchase order is approved. If your document type is
a blanket purchase order, contract purchase order,RFQ, or quotation, Oracle
Purchasing uses START_DATE and END_DATE to store the valid date range for the
document. Oracle Purchasing only uses BLANKET_TOTAL_AMOUNT for blanket
purchase orders or contract purchase orders.

Oracle Purchasing does not use SUMMARY_FLAG and ENABLED_FLAG. Because

future versions of Oracle Purchasing will use them, SUMMARY_FLAG and
ENABLED_FLAG should always be 'N' and 'Y' respectively .
You enter document header information in the Header region of the Purchase
Orders, RFQs, and Quotations windows
 Table 1: PO_HEADERS_ALL
Primary Key: PO_HEADERS_PK
PO_HEADER_ID
Primary Key: PO_HEADERS_UK1
SEGMENT1
Foreign Keys from this table:
Foreign Key Column Foreign Table

PO_HEADERS_ALL.VENDOR_ID PO_VENDORS

PO_HEADERS_ALL.SHIP_TO_LOCATION_ID HR_LOCATIONS_ALL

PO_HEADERS_ALL.AGENT_ID PO_AGENTS

PO_HEADERS_ALL.BILL_TO_LOCATION_ID HR_LOCATIONS_ALL

PO_HEADERS_ALL.VENDOR_SITE_ID PO_VENDOR_SITES_ALL

PO_HEADERS_ALL.VENDOR_CONTACT_ID PO_VENDOR_CONTACTS

PO_HEADERS_ALL.FROM_HEADER_ID PO_HEADERS_ALL

PO_HEADERS_ALL.RATE_TYPE GL_DAILY_CONVERSION_TYPES

PO_HEADERS_ALL.CURRENCY_CODE FND_CURRENCIES
 Table 1: PO_HEADERS_ALL

•PO_HEADER_ID
•AGENT_ID
•TYPE_LOOKUP_CODE
•SEGMENT1
•SUMMARY_FLAG
•ENABLED_FLAG
 Table 2 : PO_LINES_ALL
Description: Purchase document lines (for purchase orders, purchase agreements,
quotations, RFQs)
PO_LINES_ALL stores current information about each purchase order line. You
need one row for each line you attach to a document. There are five document
types that use lines: RFQs , Quotations , Standard purchase orders , Blanket
purchase orders , Planned purchase orders .
Each row includes the line number, the item number and category, unit, price, tax
information, matching information, and quantity ordered for the line. Oracle
Purchasing uses this information to record and update item and price information for
purchase orders,quotations, and RFQs.
PO_LINE_ID is the unique system-generated line number invisible to the user.
LINE_NUM is the number of the line on the purchase order.
Oracle Purchasing uses CONTRACT_ID to reference a contract purchase order
from a standard purchase order line. Oracle Purchasing uses
ALLOW_PRICE_OVERRIDE_FLAG, COMMITTED_AMOUNT,
QUANTITY_COMMITTED, MIN_RELEASE_AMOUNT only for blanket and planned
purchase order lines.
The QUANTITY field stores the total quantity of all purchase order
shipment lines (found in PO_LINE_LOCATIONS_ALL).
 Table 2 : PO_LINES_ALL
Primary Key: PO_LINES_PK
PO_LINE_ID
Primary Key: PO_LINES_UK1
PO_HEADER_ID
LINE_NUM
Foreign Keys from this table:
Foreign Key Column Foreign Table

PO_LINES_ALL.CATEGORY_ID MTL_CATEGORIES_B

PO_LINES_ALL.LINE_TYPE_ID
PO_LINES_ALL.LINE_TYPE_ID PO_LINE_TYPES_B

PO_LINES_ALL.UN_NUMBER_ID PO_UN_NUMBERS_B

PO_LINES_ALL.HAZARD_CLASS_ID PO_HAZARD_CLASSES_B

PO_LINES_ALL.FROM_HEADER_ID
PO_LINES_ALL.FROM_HEADER_ID PO_HEADERS_ALL

PO_LINES_ALL.FROM_LINE_ID PO_LINES_ALL
 Table 2 : PO_LINES_ALL

•PO_LINE_ID
•PO_HEADER_ID
•LINE_TYPE_ID
•LINE_NUM
 Table 3 : PO_LINE_LOCATIONS_ALL
Description: Document shipment schedules (for purchase orders, purchase
agreements, quotations, RFQs)
PO_LINE_LOCATIONS_ALL contains information about purchase order shipment
schedules and blanket agreement price breaks. You need one row for each schedule
or price break you attach to a document line.
There are seven types of documents that use shipment schedules: RFQs ,
Quotations, Standard purchase orders , Planned purchase orders , Planned purchase
order releases , Blanket purchase orders , Blanket purchase order releases .
Each row includes the location, quantity, and dates for each shipment schedule.
Oracle Purchasing uses this information to record delivery schedule information for
purchase orders, and price break information for blanket purchase orders, quotations
and RFQs.
PO_RELEASE_ID applies only to blanket purchase order release shipments.
PO_RELEASE_ID identifies the release on which you placed this shipment.
SOURCE_SHIPMENT_ID applies only to planned purchase order release shipments.
It identifies the planned purchase order shipment you chose to release from.
 Table 3 : PO_LINE_LOCATIONS_ALL
The QUANTITY field corresponds to the total quantity ordered on all purchase order
distribution lines (found in PO_DISTRIBUTIONS_ALL).
Oracle Purchasing automatically updates QUANTITY_RECEIVED,
QUANTITY_ACCEPTED, and QUANTITY_REJECTED when you receive, return, or
inspect goods or services. Oracle Payables automatically updates
QUANTITY_BILLED when you match an invoice with a purchase order shipment.
Oracle Purchasing automatically updates QUANTITY_CANCELLED when you
cancel a purchase order shipment.
Oracle Purchasing sets APPROVED_FLAG to 'Y' when you approve the
corresponding purchase order if there are no problems associated with
the shipment and its related distributions.
Oracle Purchasing sets ENCUMBERED_FLAG to 'Y' and enters the
ENCUMBERED_DATE when you approve a purchase order if you use
encumbrance.
 Table 3 : PO_LINE_LOCATIONS_ALL
Primary Key: PO_LINE_LOCATIONS_PK
LINE_LOCATION_ID
Primary Key: PO_LINE_LOCATIONS_UK1
PO_LINE_ID
SHIPMENT_NUM
Foreign Keys from this table:
Foreign Key Column Foreign Table

PO_LINE_LOCATIONS_ALL.PO_HEADER_ID PO_HEADERS_ALL

PO_LINE_LOCATIONS_ALL.FROM_HEADER_ID
PO_LINE_LOCATIONS_ALL.FROM_HEADER_ID PO_HEADERS_ALL

PO_LINE_LOCATIONS_ALL.FROM_LINE_ID PO_LINES_ALL

PO_LINE_LOCATIONS_ALL.FROM_LINE_LOCATION_ID PO_LINE_LOCATIONS_ALL

PO_LINE_LOCATIONS_ALL.SOURCE_SHIPMENT_ID
PO_LINE_LOCATIONS_ALL.SOURCE_SHIPMENT_ID PO_LINE_LOCATIONS_ALL

PO_LINE_LOCATIONS_ALL.RECEIVING_ROUTING_ID
PO_LINE_LOCATIONS_ALL.RECEIVING_ROUTING_ID RCV_ROUTING_HEADERS

PO_LINE_LOCATIONS_ALL.SHIP_TO_LOCATION_ID HR_LOCATIONS_ALL

PO_LINE_LOCATIONS_ALL.PO_LINE_ID PO_LINES_ALL

PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID
PO_LINE_LOCATIONS_ALL.PO_RELEASE_ID PO_RELEASES_ALL

PO_LINE_LOCATIONS_ALL.TAX_CODE_ID AP_TAX_CODES_ALL
Table 3 : PO_LINE_LOCATIONS_ALL

•LINE_LOCATION_ID
•LAST_UPDATE_DATE
•LAST_UPDATED_BY
•PO_HEADER_ID
•PO_LINE_ID
•SHIPMENT_TYPE
 Table 4 : PO_DISTRIBUTIONS_ALL
PO_DISTRIBUTIONS_ALL contains accounting distribution information for a
purchase order shipment line. You need one row for each distribution line you attach
to a purchase order shipment. There are four types of documents using distributions
in Oracle Purchasing: Standard Purchase Orders , Planned Purchase Orders ,
Planned Purchase Order Releases , Blanket Purchase Order Releases .
Each row includes the destination type, requestor ID,quantity ordered and deliver-to
location for the distribution.
PO_DISTRIBUTIONS_ALL is one of five tables storing purchase order and release
information. Some columns in PO_DISTRIBUTIONS_ALL contain information only if
certain conditions exist:
If you autocreate this accounting distribution from a requisition,
REQ_DISTRIBUTION_ID corresponds to the ID of the requisition distribution you
copy on the purchase order.
If you use a foreign currency on your purchase order, Oracle Purchasing stores
currency conversion information in RATE and RATE_DATE.
If you use encumbrance, GL_ENCUMBERED_DATE and
GL_ENCUMBERED_PERIOD_NAME contain encumbrance information Oracle
Purchasing uses to create journal entries in Oracle General Ledger.
 Table 4 : PO_DISTRIBUTIONS_ALL
If you do not auto create the purchase order from online requisitions,
REQ_LINE_REFERENCE_NUM and REQ_HEADER_REFERENCE_NUM contain
the requisition number and requisition line number of the corresponding paper
requisition. These two columns are not foreign keys to another table.
If the distribution corresponds to a blanket purchase order release,
PO_RELEASE_ID identifies this release.
If SOURCE_DISTRIBUTION_ID has a value, the distribution is part of a planned
purchase order release.
If you cancel the distribution, Oracle Purchasing automatically updates
QUANTITY_CANCELLED or GL_CANCELLED_DATE. Oracle Purchasing also
enters UNENCUMBERED_AMOUNT if you use encumbrance.
You enter distribution information in the Distributions
window, accessible through the Purchase Orders and Releases windows
 Table 4 : PO_DISTRIBUTIONS_ALL
Primary Key: PO_DISTRIBUTIONS_PK
PO_DISTRIBUTION_ID
Primary Key: PO_DISTRIBUTIONS_UK1
PO_HEADER_ID ; PO_LINE_ID ; LINE_LOCATION_ID ;DISTRIBUTION_NUM
Foreign Keys from this table:
Foreign Key Column Foreign Table
PO_DISTRIBUTIONS_ALL.PO_HEADER_ID
PO_DISTRIBUTIONS_ALL.PO_HEADER_ID PO_HEADERS_ALL
PO_DISTRIBUTIONS_ALL.DESTINATION_SUBINVENTORY MTL_SECONDARY_INVENTORIES
PO_DISTRIBUTIONS_ALL.DESTINATION_ORGANIZATION_ID
PO_DISTRIBUTIONS_ALL.DESTINATION_ORGANIZATION_ID
PO_DISTRIBUTIONS_ALL.BOM_RESOURCE_ID BOM_RESOURCES
PO_DISTRIBUTIONS_ALL.BUDGET_ACCOUNT_ID GL_CODE_COMBINATIONS
PO_DISTRIBUTIONS_ALL.ACCRUAL_ACCOUNT_ID
PO_DISTRIBUTIONS_ALL.ACCRUAL_ACCOUNT_ID GL_CODE_COMBINATIONS
PO_DISTRIBUTIONS_ALL.PO_LINE_ID PO_LINES_ALL
PO_DISTRIBUTIONS_ALL.VARIANCE_ACCOUNT_ID GL_CODE_COMBINATIONS
PO_DISTRIBUTIONS_ALL.SOURCE_DISTRIBUTION_ID PO_DISTRIBUTIONS_ALL
PO_DISTRIBUTIONS_ALL.TASK_ID
PO_DISTRIBUTIONS_ALL.TASK_ID PA_TASKS
PO_DISTRIBUTIONS_ALL.LINE_LOCATION_ID PO_LINE_LOCATIONS_ALL
PO_DISTRIBUTIONS_ALL.PO_RELEASE_ID
PO_DISTRIBUTIONS_ALL.PO_RELEASE_ID PO_RELEASES_ALL
PO_DISTRIBUTIONS_ALL.REQ_DISTRIBUTION_ID PO_REQ_DISTRIBUTIONS_ALL
PO_DISTRIBUTIONS_ALL.SET_OF_BOOKS_ID GL_SETS_OF_BOOKS
PO_DISTRIBUTIONS_ALL.CODE_COMBINATION_ID
PO_DISTRIBUTIONS_ALL.CODE_COMBINATION_ID GL_CODE_COMBINATIONS
Table 4 : PO_DISTRIBUTIONS_ALL

•PO_DISTRIBUTION_ID
•PO_HEADER_ID
•PO_LINE_ID
•LINE_LOCATION_ID
•SET_OF_BOOKS_ID
•CODE_COMBINATION_ID
•QUANTITY_ORDERED
•DISTRIBUTION_NUM
 Table 5: PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_HEADERS_ALL stores information about requisition headers.
You need one row for each requisition header you create. Each row contains the
requisition number, preparer, status, and description.
REQUISITION_HEADER_ID is the unique system-generated requisition number.
REQUISITION_HEADER_ID is invisible to the user. SEGMENT1 is the number you
use to identify the requisition in forms and reports. Oracle Purchasing generates
SEGMENT1 using the PO_UNIQUE_IDENTIFIER_CONTROL table if you choose to
let Oracle Purchasing generate requisition numbers for you.
PO_REQUISITION_HEADERS_ALL is one of three tables storing requisition
information. PO_REQUISITION_HEADERS_ALL corresponds to the Header region
of the Requisitions window.
SEGMENT1 provides unique values for each row in the table in addition to
REQUISITION_HEADER_ID.
Primary Key: PO_REQUISITION_HEADERS_PK
REQUISITION_HEADER_ID
Primary Key: PO_REQUISITION_HEADERS_UK1
SEGMENT1
Table 5: PO_REQUISITION_HEADERS_ALL

•REQUISITION_HEADER_ID
•PREPARER_ID
•SEGMENT1
•SUMMARY_FLAG
•ENABLED_FLAG
 Table 6: PO_REQUISITION_LINES_ALL
PO_REQUISITION_LINES stores information about requisition lines. You need one row
for each requisition line you create. Each row contains the line number, item number, item
category, item description, need-by date, deliver-to location, item quantities, units, prices,
requestor, notes, and suggested supplier information for the requisition line.
LINE_LOCATION_ID identifies the purchase order shipment line on which you placed the
requisition. LINE_LOCATION_ID is null if you have not placed the requisition line on a
purchase order.
BLANKET_PO_HEADER_ID and BLANKET_PO_LINE_NUM store the suggested
blanket purchase agreement or catalog quotation line information for the requisition line.
PARENT_REQ_LINE_ID contains the REQUISITION_LINE_ID from the original
requisition line if you exploded or multi sourced this requisition line.
PO_REQUISITION_LINES is one of three tables storing requisition information. This table
corresponds to the Lines region of the Requisitions window.
 Table 6: PO_REQUISITION_LINES_ALL
Primary Key: PO_REQUISITION_LINES_PK
REQUISITION_LINE_ID
Foreign Keys from this table:
Foreign Key Column Foreign Table
PO_REQUISITION_LINES_ALL.REQUISITION_HEADER_ID PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL.LINE_TYPE_ID
PO_REQUISITION_LINES_ALL.LINE_TYPE_ID PO_LINE_TYPES_B
PO_REQUISITION_LINES_ALL.CATEGORY_ID MTL_CATEGORIES_B
PO_REQUISITION_LINES_ALL.SUGGESTED_BUYER_ID
PO_REQUISITION_LINES_ALL.SUGGESTED_BUYER_ID PO_AGENTS
PO_REQUISITION_LINES_ALL.LINE_LOCATION_ID PO_LINE_LOCATIONS_ALL
PO_REQUISITION_LINES_ALL.CURRENCY_CODE
PO_REQUISITION_LINES_ALL.CURRENCY_CODE FND_CURRENCIES
PO_REQUISITION_LINES_ALL.RATE_TYPE GL_DAILY_CONVERSION_TYPES
PO_REQUISITION_LINES_ALL.UN_NUMBER_ID
PO_REQUISITION_LINES_ALL.UN_NUMBER_ID PO_UN_NUMBERS_B
PO_REQUISITION_LINES_ALL.HAZARD_CLASS_ID PO_HAZARD_CLASSES_B
PO_REQUISITION_LINES_ALL.PARENT_REQ_LINE_ID PO_REQUISITION_LINES_ALL
PO_REQUISITION_LINES_ALL.WIP_ENTITY_ID
PO_REQUISITION_LINES_ALL.WIP_ENTITY_ID WIP_ENTITIES
PO_REQUISITION_LINES_ALL.WIP_LINE_ID WIP_LINES
PO_REQUISITION_LINES_ALL.WIP_REPETITIVE_SCHEDULE_ID
PO_REQUISITION_LINES_ALL.WIP_REPETITIVE_SCHEDULE_ID WIP_REPETITIVE_SCHEDULES
PO_REQUISITION_LINES_ALL.BOM_RESOURCE_ID BOM_RESOURCES
PO_REQUISITION_LINES_ALL.TAX_CODE_ID
PO_REQUISITION_LINES_ALL.TAX_CODE_ID AP_TAX_CODES_ALL
PO_REQUISITION_LINES_ALL.BLANKET_PO_HEADER_ID PO_HEADERS_ALL
PO_REQUISITION_LINES_ALL.VENDOR_ID
PO_REQUISITION_LINES_ALL.VENDOR_ID PO_VENDORS
PO_REQUISITION_LINES_ALL.VENDOR_SITE_ID PO_VENDOR_SITES_ALL
PO_REQUISITION_LINES_ALL.VENDOR_CONTACT_ID PO_VENDOR_CONTACTS
 Table 6: PO_REQUISITION_LINES_ALL

•REQUISITION_LINE_ID
•REQUISITION_HEADER_ID
•LINE_NUM
•LINE_TYPE_ID
•CATEGORY_ID
•ITEM_DESCRIPTION
•UNIT_MEAS_LOOKUP_CODE
•UNIT_PRICE
•QUANTITY
•DELIVER_TO_LOCATION_ID
•TO_PERSON_ID
•SOURCE_TYPE_CODE
 Table 7: PO_REQ_DISTRIBUTIONS_ALL

PO_REQ_DISTRIBUTIONS_ALL stores information about the accounting

distributions associated with each requisition line. Each requisition line must have at
least one accounting distribution. You need one row for each requisition distribution
you create. Each row includes the Accounting Flexfield ID and requisition line
quantity.

PO_REQ_DISTRIBUTIONS_ALL is one of three tables storing your requisition

information. This table corresponds to the requisition Distributions window,
accessible through the Requisitions window.
 Table 7: PO_REQ_DISTRIBUTIONS_ALL
Primary Key: PO_REQ_DISTRIBUTIONS_PK
DISTRIBUTION_ID

Foreign Keys from this table:

Foreign Key Column Foreign Table

PO_REQ_DISTRIBUTIONS_ALL.REQUISITION_LINE_ID PO_REQUISITION_LINES_ALL

PO_REQ_DISTRIBUTIONS_ALL.SET_OF_BOOKS_ID
PO_REQ_DISTRIBUTIONS_ALL.SET_OF_BOOKS_ID GL_SETS_OF_BOOKS

PO_REQ_DISTRIBUTIONS_ALL.CODE_COMBINATION_ID GL_CODE_COMBINATIONS

PO_REQ_DISTRIBUTIONS_ALL.BUDGET_ACCOUNT_ID GL_CODE_COMBINATIONS

PO_REQ_DISTRIBUTIONS_ALL.ACCRUAL_ACCOUNT_ID GL_CODE_COMBINATIONS

PO_REQ_DISTRIBUTIONS_ALL.VARIANCE_ACCOUNT_ID GL_CODE_COMBINATIONS

PO_REQ_DISTRIBUTIONS_ALL.PROJECT_ID
PO_REQ_DISTRIBUTIONS_ALL.PROJECT_ID PA_PROJECTS_ALL

PO_REQ_DISTRIBUTIONS_ALL.TASK_ID PA_TASKS

PO_REQ_DISTRIBUTIONS_ALL.EXPENDITURE_TYPE PA_EXPENDITURE_TYPES

PO_REQ_DISTRIBUTIONS_ALL.DISTRIBUTION_ID PO_REQ_DISTRIBUTIONS_ALL
 Table 8: PO_AGENTS

PO_AGENTS contains information about buyers and purchasing managers.

You need one row for each employee who has purchasing responsibilities in your

organization. You define a buyer by entering a default Item Category and Ship-To

Location.

Oracle Purchasing uses this information to determine active buyers and to provide

defaults in the Find Requisitions Lines window. When creating a purchase order,

you can use the list of values to choose a buyer you defined in this table.

The primary key AGENT_ID has the same value as the EMPLOYEE_ID in the

HR_EMPLOYEES view for the corresponding employee.

 Table 8: PO_AGENTS
Primary Key: PO_AGENTS_PK
AGENT_ID

Foreign Keys from this table:

Foreign Key Column Foreign Table
PO_AGENTS.CATEGORY_ID MTL_CATEGORIES_B

Foreign Keys to this table:

Foreign Key Column Foreign Table

AHL_OSP_ORDERS_B.PO_AGENT_ID
AHL_OSP_ORDERS_B.PO_AGENT_ID PO_AGENTS

MRP_SYSTEM_ITEMS.BUYER_ID
MRP_SYSTEM_ITEMS.BUYER_ID PO_AGENTS

PO_HEADERS_ARCHIVE_ALL.AGENT_ID PO_AGENTS

PO_HEADERS_ALL.AGENT_ID PO_AGENTS

PO_RELEASES_ALL.HOLD_BY
PO_RELEASES_ALL.HOLD_BY PO_AGENTS

PO_RELEASES_ALL.AGENT_ID
PO_RELEASES_ALL.AGENT_ID PO_AGENTS

PO_REQEXPRESS_LINES_ALL.SUGGESTED_BUYER_ID
PO_REQEXPRESS_LINES_ALL.SUGGESTED_BUYER_ID PO_AGENTS

PO_REQUISITIONS_INTERFACE_ALL.SUGGESTED_BUYER_ID
PO_REQUISITIONS_INTERFACE_ALL.SUGGESTED_BUYER_ID PO_AGENTS

PO_REQUISITION_LINES_ALL.SUGGESTED_BUYER_ID PO_AGENTS

PO_VENDORS.HOLD_BY
PO_VENDORS.HOLD_BY PO_AGENTS
 Table 8: PO_AGENTS

•AGENT_ID
•CREATION_DATE
•LINE_NUM
•LOCATION_ID
•CATEGORY_ID
 Table 9: RCV_SHIPMENT_HEADERS
RCV_SHIPMENT_HEADERS stores common information about the source of your
receipts or expected receipts. You group your receipts by the source type and the
source of the receipt. Oracle Purchasing does not allow you to group receipts from
different sources under one receipt header.
There are two receipt source types, Supplier and Internal Order, that you need to
use when receiving against different source document types. You use a receipt
source type of 'Supplier' when receiving items that you ordered from an external
supplier using a purchase order. When you receive items that are part of an inter-
organization transfer, or when receiving items that you request from your inventory
using an internal requisition, the receipt type would be 'Internal Order'.
The 'Internal Order' receipt source type populates the ORGANIZATION_ID column.
Oracle Purchasing creates a receipt header when you are entering your receipts or
when you perform inter-organization transfers using Oracle Inventory. When Oracle
Inventory creates a receipt header for an intransit shipment, the receipt number is
not populated until you receive the shipment.
 Table 9: RCV_SHIPMENT_HEADERS
Primary Key: RCV_SHIPMENT_HEADERS_PK
SHIPMENT_HEADER_ID
Primary Key: RCV_SHIPMENT_HEADERS_UK01
RECEIPT_NUM ; SHIP_TO_ORG_ID

Foreign Keys from this table:

Foreign Key Column Foreign Table

RCV_SHIPMENT_HEADERS.VENDOR_ID PO_VENDORS

RCV_SHIPMENT_HEADERS.VENDOR_SITE_ID
RCV_SHIPMENT_HEADERS.VENDOR_SITE_ID PO_VENDOR_SITES_ALL
 Table 9: RCV_SHIPMENT_HEADERS

•SHIPMENT_HEADER_ID
•CREATION_DATE
•RECEIPT_SOURCE_CODE
•VENDOR_ID
•SHIPMENT_NUM
•RECEIPT_NUM
•SHIP_TO_LOCATION_ID
•SHIPPED_DATE
•INVOICE_NUM
•INVOICE_DATE
•INVOICE_AMOUNT
•APPROVAL_STATUS
 Table 10: RCV_SHIPMENT_LINES
RCV_SHIPMENT_LINES stores information about items that have been shipped
and/or received from a specific receipt source.
RCV_SHIPMENT_LINES also stores information about the default
destination for intransit shipments.

Primary Key: RCV_SHIPMENT_LINES_PK

SHIPMENT_LINE_ID

Primary Key: RCV_SHIPMENT_LINES_U1

SHIPMENT_HEADER_ID
LINE_NUM
 Table 10: RCV_SHIPMENT_LINES
Foreign Keys from this table:
Foreign Key Column Foreign Table

RCV_SHIPMENT_LINES.SHIPMENT_HEADER_ID
RCV_SHIPMENT_LINES.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADERS

RCV_SHIPMENT_LINES.ROUTING_HEADER_ID
RCV_SHIPMENT_LINES.ROUTING_HEADER_ID RCV_ROUTING_HEADERS

RCV_SHIPMENT_LINES.CHARGE_ACCOUNT_ID GL_CODE_COMBINATIONS

RCV_SHIPMENT_LINES.TRANSPORTATION_ACCOUNT_ID
RCV_SHIPMENT_LINES.TRANSPORTATION_ACCOUNT_ID GL_CODE_COMBINATIONS

RCV_SHIPMENT_LINES.CATEGORY_ID MTL_CATEGORIES_B

RCV_SHIPMENT_LINES.TO_SUBINVENTORY MTL_SECONDARY_INVENTORIES
RCV_SHIPMENT_LINES.TO_ORGANIZATION_ID

RCV_SHIPMENT_LINES.PO_RELEASE_ID
RCV_SHIPMENT_LINES.PO_RELEASE_ID PO_RELEASES_ALL

RCV_SHIPMENT_LINES.REASON_ID MTL_TRANSACTION_REASONS

RCV_SHIPMENT_LINES.PO_HEADER_ID PO_HEADERS_ALL

RCV_SHIPMENT_LINES.PO_LINE_ID
RCV_SHIPMENT_LINES.PO_LINE_ID PO_LINES_ALL

RCV_SHIPMENT_LINES.PO_LINE_LOCATION_ID PO_LINE_LOCATIONS_ALL

RCV_SHIPMENT_LINES.PO_DISTRIBUTION_ID
RCV_SHIPMENT_LINES.PO_DISTRIBUTION_ID PO_DISTRIBUTIONS_ALL

RCV_SHIPMENT_LINES.REQUISITION_LINE_ID PO_REQUISITION_LINES_ALL

RCV_SHIPMENT_LINES.REQ_DISTRIBUTION_ID PO_REQ_DISTRIBUTIONS_ALL
 Foreign Keys to this table:

Table 10: RCV_SHIPMENT_LINES

•SHIPMENT_LINE_ID
•CREATION_DATE
•SHIPMENT_HEADER_ID
•LINE_NUM
•QUANTITY_SHIPPED
•QUANTITY_RECEIVED
•UNIT_OF_MEASURE
 Table 11: RCV_TRANSACTIONS
RCV_TRANSACTIONS stores historical information about receiving transactions
that you have performed. When you enter a receiving transaction and the
receiving transaction processor processes your transaction, the transaction is
recorded in this table.
Once a row has been inserted into this table, it will never be updated. When you
correct a transaction, the net transaction quantity is maintained in RCV_SUPPLY.
The original transaction quantity does not get updated. You can only delete rows
from this table using the Purge feature of Oracle Purchasing.

Primary Key: RCV_TRANSACTIONS_PK

TRANSACTION_ID
Foreign Keys from this table:

Foreign Key Column Foreign Table

RCV_TRANSACTIONS.PO_LINE_ID
RCV_TRANSACTIONS.PO_LINE_ID PO_LINES_ALL
RCV_TRANSACTIONS.PO_LINE_LOCATION_ID PO_LINE_LOCATIONS_ALL
RCV_TRANSACTIONS.PO_RELEASE_ID PO_RELEASES_ALL
RCV_TRANSACTIONS.PO_DISTRIBUTION_ID PO_DISTRIBUTIONS_ALL
RCV_TRANSACTIONS.REQUISITION_LINE_ID
RCV_TRANSACTIONS.REQUISITION_LINE_ID PO_REQUISITION_LINES_ALL
RCV_TRANSACTIONS.REQ_DISTRIBUTION_ID PO_REQ_DISTRIBUTIONS_ALL
RCV_TRANSACTIONS.ROUTING_HEADER_ID
RCV_TRANSACTIONS.ROUTING_HEADER_ID RCV_ROUTING_HEADERS
RCV_TRANSACTIONS.ROUTING_STEP_ID RCV_ROUTING_STEPS
RCV_TRANSACTIONS.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADERS
RCV_TRANSACTIONS.VENDOR_ID
RCV_TRANSACTIONS.VENDOR_ID PO_VENDORS
RCV_TRANSACTIONS.VENDOR_SITE_ID PO_VENDOR_SITES_ALL
RCV_TRANSACTIONS.SUBINVENTORY MTL_SECONDARY_INVENTORIES
RCV_TRANSACTIONS.ORGANIZATION_ID
RCV_TRANSACTIONS.SHIPMENT_LINE_ID
RCV_TRANSACTIONS.SHIPMENT_LINE_ID RCV_SHIPMENT_LINES
RCV_TRANSACTIONS.BOM_RESOURCE_ID BOM_RESOURCES
RCV_TRANSACTIONS.LOCATOR_ID MTL_ITEM_LOCATIONS
RCV_TRANSACTIONS.ORGANIZATION_ID
RCV_TRANSACTIONS.ORGANIZATION_ID
RCV_TRANSACTIONS.REASON_ID MTL_TRANSACTION_REASONS
RCV_TRANSACTIONS.CURRENCY_CONVERSION_TYPE
RCV_TRANSACTIONS.CURRENCY_CONVERSION_TYPE GL_DAILY_CONVERSION_TYPES
RCV_TRANSACTIONS.LOCATION_ID HR_LOCATIONS_ALL
RCV_TRANSACTIONS.INV_TRANSACTION_ID MTL_MATERIAL_TRANSACTIONS
RCV_TRANSACTIONS.PARENT_TRANSACTION_ID
RCV_TRANSACTIONS.PARENT_TRANSACTION_ID RCV_TRANSACTIONS
RCV_TRANSACTIONS.PO_HEADER_ID
RCV_TRANSACTIONS.PO_HEADER_ID PO_HEADERS_ALL
 Table 11: RCV_TRANSACTIONS

•CREATION_DATE
•TRANSACTION_TYPE
•TRANSACTION_DATE
•SHIPMENT_HEADER_ID
•CUSTOMER_ID
•CUSTOMER_SITE_ID
 Table 12: PO_VENDORS

PO_VENDORS stores information about your suppliers. You need one row for each
supplier you define. Each row includes the supplier name as well as purchasing,
receiving, payment, accounting, tax, classification, and general information. Oracle
Purchasing uses this information to determine active suppliers.
VENDOR_ID is the unique system–generated receipt header number invisible to the
user.
SEGMENT1 is the system–generated or manually assigned number you use to
identify the supplier in forms and reports. Oracle Purchasing generates SEGMENT1
using the PO_UNIQUE_IDENTIFIER_CONTROL table if you choose to let Oracle
Purchasing generate supplier numbers for you. This table is one of three tables that
store supplier information. PO_VENDORS corresponds to the Suppliers window.
JOINING BETWEEN PO_HEADERS_ALL AND PO_LINES_ALL

QUERY

SELECT
A.PO_HEADER_ID,A.TYPE_LOOKUP_CODE,A.SEGMENT1
,B.ITEM_ID,B.ITEM_DESCRIPTION,B.UNIT_PRICE,
B.QUANTITY
FROM PO_HEADERS_ALL A, PO_LINES_ALL B
WHERE A.PO_HEADER_ID = B.PO_HEADER_ID
 JOINING BETWEEN PO_HEADERS_ALL AND PO_LINE_LOCATIONS_ALL

QUERY

SELECT
C.PO_HEADER_ID,C.TYPE_LOOKUP_CODE,C.SEGMENT1,C.VENDOR_ID,
D.QUANTITY,D.QUANTITY_RECEIVED,D.QUANTITY_ACCEPTED,
D.QUANTITY_REJECTED,D.QUANTITY_BILLED
FROM PO_HEADERS_ALL C, PO_LINE_LOCATIONS_ALL D
WHERE C.PO_HEADER_ID = D.PO_HEADER_ID
 JOINING BETWEEN PO_HEADERS_ALL AND PO_DISTRIBUTIONS_ALL

QUERY:

SELECT E.PO_HEADER_ID,
E.TYPE_LOOKUP_CODE,
E.SEGMENT1,
F.SET_OF_BOOKS_ID,
F.CODE_COMBINATION_ID,
F.REQ_DISTRIBUTION_ID,
F.GL_CLOSED_DATE
FROM PO_HEADERS_ALL E,
PO_DISTRIBUTIONS_ALL F
WHERE E.PO_HEADER_ID = F.PO_HEADER_ID
 JOINING BETWEEN PO_LINES_ALL AND PO_DISTRIBUTIONS_ALL

QUERY:

SELECT A.LINE_NUM,
A.ITEM_ID,
A.ITEM_DESCRIPTION,
A.QUANTITY,
B.CODE_COMBINATION_ID,
B.ORG_ID,
B.RATE
FROM PO_LINES_ALL A,
PO_DISTRIBUTIONS_ALL B
WHERE A.PO_LINE_ID = B.PO_LINE_ID
 JOINING BETWEEN PO_REQUISITION_HEADERS_ALL
AND PO_REQUISITION_LINES_ALL

QUERY:

SELECT A.PREPARER_ID,
A.SEGMENT1,
B.UNIT_PRICE,
B.ITEM_DESCRIPTION
FROM PO_REQUISITION_HEADERS_ALL A,
PO_REQUISITION_LINES_ALL B
WHERE A.REQUISITION_HEADER_ID = B.REQUISITION_HEADER_ID
 JOINING BETWEEN PO_REQUISITION_HEADERS_ALL
AND PO_REQ_DISTRIBUTIONS_ALL

SELECT
A.PREPARER_ID,
A.SEGMENT1,
A.CONTRACTOR_STATUS,
B.CODE_COMBINATION_ID,
B.SET_OF_BOOKS_ID
FROM
PO_REQUISITION_HEADERS_ALL A,
PO_REQ_DISTRIBUTIONS_ALL B,
PO_REQUISITION_LINES_ALL C
WHERE A.REQUISITION_HEADER_ID = C.REQUISITION_HEADER_ID
AND C.REQUISITION_LINE_ID = B.REQUISITION_LINE_ID
 Table 1: AP_INVOICES_ALL
Description: AP_INVOICES_ALL contains records for invoices you enter. There is
one row for each invoice you enter. An invoice can have one or more invoice
distribution lines. An invoice can also have one or more scheduled payments.
An invoice of type EXPENSE REPORT must relate to a row in
AP_EXPENSE_REPORT_HEADERS_ALL unless the record has been purged from
AP_EXPENSE_REPORT_HEADERS_ALL. Your Oracle Payables application uses
the INTEREST type invoice for interest that it calculates on invoices that are
overdue. Your Oracle Payables application links the interest invoice to the original
invoice by inserting the INVOICE_ID in the AP_INVOICE_RELATIONSHIPS table.
This table corresponds to the Invoices window.

Primary Key: AP_INVOICES_PK

INVOICE_ID

Primary Key: AP_INVOICES_UK1

DOC_SEQUENCE_ID ; DOC_SEQUENCE_VALUE

Primary Key: AP_INVOICES_UK2

VENDOR_ID ; INVOICE_NUM
Foreign Keys from this table:
Foreign Key Column Foreign Table

AP_INVOICES_ALL.BATCH_ID
AP_INVOICES_ALL.BATCH_ID AP_BATCHES_ALL

AP_INVOICES_ALL.PAYMENT_CURRENCY_CODE FND_CURRENCIES

AP_INVOICES_ALL.EXCHANGE_RATE_TYPE
AP_INVOICES_ALL.EXCHANGE_RATE_TYPE GL_DAILY_CONVERSION_TYPES

AP_INVOICES_ALL.PO_HEADER_ID PO_HEADERS_ALL

AP_INVOICES_ALL.PA_DEFAULT_DIST_CCID GL_CODE_COMBINATIONS

AP_INVOICES_ALL.DOC_SEQUENCE_ID AP_DOC_SEQUENCE_AUDIT
AP_INVOICES_ALL.DOC_SEQUENCE_VALUE

AP_INVOICES_ALL.DOC_SEQUENCE_ID
AP_INVOICES_ALL.DOC_SEQUENCE_ID FND_DOCUMENT_SEQUENCES

AP_INVOICES_ALL.AWT_GROUP_ID AP_AWT_GROUPS

AP_INVOICES_ALL.TASK_ID PA_TASKS

AP_INVOICES_ALL.EXPENDITURE_TYPE PA_EXPENDITURE_TYPES

AP_INVOICES_ALL.EXPENDITURE_ORGANIZATION_ID PA_EXP_ORGS_IT

AP_INVOICES_ALL.PROJECT_ID
AP_INVOICES_ALL.PROJECT_ID PA_PROJECTS_ALL

AP_INVOICES_ALL.PAYMENT_CROSS_RATE_TYPE
AP_INVOICES_ALL.PAYMENT_CROSS_RATE_TYPE GL_DAILY_CONVERSION_TYPES

AP_INVOICES_ALL.VALIDATION_REQUEST_ID FND_CONCURRENT_REQUESTS

AP_INVOICES_ALL.RECURRING_PAYMENT_ID
AP_INVOICES_ALL.RECURRING_PAYMENT_ID AP_RECURRING_PAYMENTS_ALL

AP_INVOICES_ALL.VENDOR_ID
AP_INVOICES_ALL.VENDOR_ID PO_VENDORS

AP_INVOICES_ALL.VENDOR_SITE_ID
AP_INVOICES_ALL.VENDOR_SITE_ID PO_VENDOR_SITES_ALL

AP_INVOICES_ALL.SET_OF_BOOKS_ID
AP_INVOICES_ALL.SET_OF_BOOKS_ID %

AP_INVOICES_ALL.ACCTS_PAY_CODE_COMBINATION_ID GL_CODE_COMBINATIONS

AP_INVOICES_ALL.INVOICE_CURRENCY_CODE FND_CURRENCIES
 Table 1: AP_INVOICES_ALL

•INVOICE_ID
•INVOICE_NUM
•INVOICE_AMOUNT
•VENDOR_SITE_ID
•AMOUNT_PAID
•INVOICE_DATE
•INVOICE_TYPE_LOOKUP_CODE
•PAYMENT_STATUS_FLAG
•VOUCHER_NUM
•GL_DATE
•TOTAL_TAX_AMOUNT
•AWT_FLAG
•CANCELLED_DATE
•CANCELLED_AMOUNT
 Table 2: AP_INVOICE_DISTRIBUTIONS_ALL
Description: Invoice distribution line information.
AP_INVOICE_DISTRIBUTIONS_ALL holds the distribution information that is
manually entered or system-generated. There is one row for each invoice
distribution. A distribution must be associated with an invoice. An invoice can have
multiple distributions. Examples of when your Oracle Payables application
automatically creates rows in this table include the following:
You choose a distribution set at the invoice header level.
You match an invoice line to a purchase order or receipt. The system uses
information from the matched purchase order or receipt to create the distributions.
You match a credit or debit memo to an invoice.
You generate charge distributions (tax, freight, misc.) from allocation rules.
You apply a prepayment or unapply a prepayment.
Payables automatically withholds tax.
Payables creates an interest invoice.
 Table 2: AP_INVOICE_DISTRIBUTIONS_ALL
Primary Key: AP_INVOICE_DISTRIBUTIONS_PK
INVOICE_ID ; DISTRIBUTION_LINE_NUMBER

Foreign Keys from this table:

Foreign Key Column Foreign Table

AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID
AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_ID AP_INVOICES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL.RECEIPT_CURRENCY_CODE FND_CURRENCIES

AP_INVOICE_DISTRIBUTIONS_ALL.ACCOUNTING_EVENT_ID
AP_INVOICE_DISTRIBUTIONS_ALL.ACCOUNTING_EVENT_ID AP_ACCOUNTING_EVENTS_ALL

AP_INVOICE_DISTRIBUTIONS_ALL.INCOME_TAX_REGION AP_INCOME_TAX_REGIONS

AP_INVOICE_DISTRIBUTIONS_ALL.RATE_VAR_CODE_COMBINATION_ID GL_CODE_COMBINATIONS

AP_INVOICE_DISTRIBUTIONS_ALL.PRICE_VAR_CODE_COMBINATION_ID
AP_INVOICE_DISTRIBUTIONS_ALL.PRICE_VAR_CODE_COMBINATION_ID GL_CODE_COMBINATIONS

AP_INVOICE_DISTRIBUTIONS_ALL.PARENT_INVOICE_ID AP_INVOICES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL.AWT_GROUP_ID
AP_INVOICE_DISTRIBUTIONS_ALL.AWT_GROUP_ID AP_AWT_GROUPS

AP_INVOICE_DISTRIBUTIONS_ALL.TAX_CODE_ID AP_TAX_CODES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL.AWT_TAX_RATE_ID AP_AWT_TAX_RATES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL.TASK_ID
AP_INVOICE_DISTRIBUTIONS_ALL.TASK_ID PA_TASKS

AP_INVOICE_DISTRIBUTIONS_ALL.EXPENDITURE_TYPE PA_EXPENDITURE_TYPES
Foreign Keys from this table:
Foreign Key Column Foreign Table

AP_INVOICE_DISTRIBUTIONS_ALL.EXPENDITURE_ORGANIZATION_ID PA_EXP_ORGS_IT

AP_INVOICE_DISTRIBUTIONS_ALL.RCV_TRANSACTION_ID RCV_TRANSACTIONS

AP_INVOICE_DISTRIBUTIONS_ALL.AWT_INVOICE_ID
AP_INVOICE_DISTRIBUTIONS_ALL.AWT_INVOICE_ID AP_INVOICES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL.AWT_ORIGIN_GROUP_ID
AP_INVOICE_DISTRIBUTIONS_ALL.AWT_ORIGIN_GROUP_ID AP_AWT_GROUPS

AP_INVOICE_DISTRIBUTIONS_ALL.CREDIT_CARD_TRX_ID
AP_INVOICE_DISTRIBUTIONS_ALL.CREDIT_CARD_TRX_ID AP_CREDIT_CARD_TRXNS_ALL

AP_INVOICE_DISTRIBUTIONS_ALL.COMPANY_PREPAID_INVOICE_ID
AP_INVOICE_DISTRIBUTIONS_ALL.COMPANY_PREPAID_INVOICE_ID AP_INVOICES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL.AWT_INVOICE_PAYMENT_ID AP_INVOICE_PAYMENTS_ALL

AP_INVOICE_DISTRIBUTIONS_ALL.PRICE_CORRECT_INV_ID AP_INVOICES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL.BATCH_ID AP_BATCHES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL.DIST_CODE_COMBINATION_ID GL_CODE_COMBINATIONS

AP_INVOICE_DISTRIBUTIONS_ALL.PROJECT_ID
AP_INVOICE_DISTRIBUTIONS_ALL.PROJECT_ID PA_PROJECTS_ALL

AP_INVOICE_DISTRIBUTIONS_ALL.SET_OF_BOOKS_ID
AP_INVOICE_DISTRIBUTIONS_ALL.SET_OF_BOOKS_ID %

AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID
AP_INVOICE_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID PO_DISTRIBUTIONS_ALL

AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_TYPE
AP_INVOICE_DISTRIBUTIONS_ALL.EXCHANGE_RATE_TYPE GL_DAILY_CONVERSION_TYPES
Table 2: AP_INVOICE_DISTRIBUTIONS_ALL

•ACCOUNTING_DATE
•INVOICE_ID
•UNIT_PRICE
•RCV_TRANSACTION_ID
•ASSET_BOOK_TYPE_CODE
•ASSET_CATEGORY_ID
•SET_OF_BOOKS_ID
•AMOUNT
 Table3 : AP_INVOICE_LINES_ALL
Description: AP_INVOICE_LINES_ALL contains records for invoice lines entered
manually, generated automatically or imported from the Open Interface.
An invoice can have one or more invoice lines. An invoice line can have one or more
invoice distributions. An invoice line represents goods (direct or indirect materials),
service(s), and/or associated tax/freight/miscellaneous charges invoiced from a
supplier. An invoice line should contain all the attributes that are present on the
physical or electronic invoice presented by the supplier. These attributes should
suffice in specifying the charge/expense/asset information that makes up the invoice
line. An invoice line can be of any of the following types: Item, Freight,
Miscellaneous, Tax, Prepayment or Withholding Tax. Prepayment Lines are
generated via prepayment application. Item type lines may represent a match to a
purchase order or receipt, a price correction against an existing purchase order or
receipt matched invoice line, a quantity correction against an existing purchase order
or receipt matched invoice line, an invoice correction against an existing non-
matched invoice line or a non-matched invoice line.
Freight and Miscellaneous lines may represent a match to a Receipt or may be non-
matched. Freight and Miscellaneous type lines may be allocated to Item type lines
whenever they are not themselves matched to a receipt.
 Table3 : AP_INVOICE_LINES_ALL
Lines may be generated automatically via any of the following methods:
. Match to purchase order or receipt requested from the invoice header
. Generation of freight automatically from the invoice header
. Generation of Item line from Distribution Set requested from the invoice header
. Generation of Freight/Miscellaneous from purchase order or receipt matching
windows
. Prepayment application
Lines will also be created during import of Open Interface invoices. It is the
distributions for a line, and not the line itself, that will carry the status relative to
Validation, Encumbrance, and Accounting. Furthermore, it is the Distributions for a
line that get transferred to Oracle Assets and/or processed by Oracle Projects.
However, lines will contain an Approval status. A line may be discarded on its own
or as part of cancellation of the invoice. A discarded line will have an amount of 0
and will be marked as discarded. During discarding of a line, appropriate
distributions are generated to reverse the effect of any existing distributions for the
line
 Table3 : AP_INVOICE_LINES_ALL

•INVOICE_ID
•LINE_NUMBER
•ITEM_DESCRIPTION
•SERIAL_NUMBER
•MANUFACTURER
•AMOUNT
•QUANTITY_INVOICED
•PO_HEADER_ID
•RCV_TRANSACTION_ID
•ASSET_BOOK_TYPE_CODE
•PREPAY_INVOICE_ID
•UNIT_MEAS_LOOKUP_CODE
 Table4 : AP_INVOICE_PAYMENTS_ALL
Description: Invoice payment records. AP_INVOICE_PAYMENTS_ALL contains
records of invoice payments that you made to suppliers. There is one row for each
payment you make for each invoice. There is one payment and one invoice for
each payment in this table. Your Oracle Payables application updates this table
when you confirm an automatic payment batch, enter a manual payment, or
process a Quick payment. When you void a payment, your Oracle Payables
application inserts an additional payment line that is the negative of the original
payment line.
Values for POSTED_FLAG may be 'Y' for accounted payments or 'N' for
unaccounted payments. Values for ACCRUAL_POSTED_FLAG may be 'Y' for
accounted payments or 'N' for unaccounted payments under accrual basis
accounting; values for CASH_POSTED_FLAG may be 'Y' for accounted payments
or 'N' for unaccounted payments under cash basis accounting.
 Table4 : AP_INVOICE_PAYMENTS_ALL
Primary Key: AP_INVOICE_PAYMENTS_PK
INVOICE_PAYMENT_ID
Foreign Keys from this table:
Foreign Key Column Foreign Table

AP_INVOICE_PAYMENTS_ALL.INVOICE_ID
AP_INVOICE_PAYMENTS_ALL.INVOICE_ID AP_INVOICES_ALL

AP_INVOICE_PAYMENTS_ALL.INVOICING_VENDOR_SITE_ID %

AP_INVOICE_PAYMENTS_ALL.CHECK_ID AP_CHECKS_ALL

AP_INVOICE_PAYMENTS_ALL.SET_OF_BOOKS_ID %

AP_INVOICE_PAYMENTS_ALL.EXCHANGE_RATE_TYPE
AP_INVOICE_PAYMENTS_ALL.EXCHANGE_RATE_TYPE GL_DAILY_CONVERSION_TYPES

AP_INVOICE_PAYMENTS_ALL.ACCOUNTING_EVENT_ID
AP_INVOICE_PAYMENTS_ALL.ACCOUNTING_EVENT_ID AP_ACCOUNTING_EVENTS_ALL

AP_INVOICE_PAYMENTS_ALL.INVOICE_ID AP_PAYMENT_SCHEDULES_ALL
AP_INVOICE_PAYMENTS_ALL.PAYMENT_NUM

AP_INVOICE_PAYMENTS_ALL.INVOICING_PARTY_ID HZ_PARTIES

AP_INVOICE_PAYMENTS_ALL.INVOICING_PARTY_SITE_ID HZ_PARTY_SITES
Table4 : AP_INVOICE_PAYMENTS_ALL

•ACCOUNTING_DATE
•ACCRUAL_POSTED_FLAG
•AMOUNT
•CHECK_ID
•INVOICE_ID
•BANK_ACCOUNT_NUM
•BANK_ACCOUNT_TYPE
•BANK_NUM
•ORG_ID
•EXTERNAL_BANK_ACCOUNT_ID
 Table 5 : AP_PAYMENT_SCHEDULES_ALL

Description: Scheduled payment information on invoices.

AP_PAYMENT_SCHEDULES_ALL contains information about scheduled payments
for an invoice. You need one row for each time you intend to make a payment on an
invoice. Your Oracle Payables application uses this information to determine when
to make payments on an invoice and how much to pay in an automatic payment
batch. Values for HOLD_FLAG may be ’Y’ to place a hold on the scheduled
payment, or ’N’ not to do so. Values for PAYMENT_STATUS_FLAG may be ’Y’ for
fully paid payment schedules, ’N’ for unpaid scheduled payments, or ’P’ for partially
paid scheduled payments. For converted records, enter a value for
AMOUNT_REMAINING.
Table 5 : AP_PAYMENT_SCHEDULES_ALL
Primary Key: AP_PAYMENT_SCHEDULES_PK
1.INVOICE_ID
2.PAYMENT_NUM

Foreign Keys from this table:

Foreign Key Column Foreign Table

AP_PAYMENT_SCHEDULES_ALL.INVOICE_ID AP_INVOICES_ALL

AP_PAYMENT_SCHEDULES_ALL.BATCH_ID AP_BATCHES_ALL

AP_PAYMENT_SCHEDULES_ALL.EXTERNAL_BANK_ACCOUNT_ID
AP_PAYMENT_SCHEDULES_ALL.EXTERNAL_BANK_ACCOUNT_ID AP_BANK_ACCOUNTS_ALL

Foreign Keys to this table:

Foreign Key Column Foreign Table

AP_INVOICE_PAYMENTS_ALL.INVOICE_ID AP_PAYMENT_SCHEDULES_ALL
AP_INVOICE_PAYMENTS_ALL.PAYMENT_NUM

JL_BR_AP_COLLECTION_DOCS_ALL.INVOICE_ID
JL_BR_AP_COLLECTION_DOCS_ALL.INVOICE_ID AP_PAYMENT_SCHEDULES_ALL
JL_BR_AP_COLLECTION_DOCS_ALL.PAYMENT_NUM
Table 5 : AP_PAYMENT_SCHEDULES_ALL

•INVOICE_ID
•AMOUNT_REMAINING
•DUE_DATE
•GROSS_AMOUNT
•PAYMENT_STATUS_FLAG
•PAYMENT_METHOD_CODE
•DISCOUNT_AMOUNT_REMAINING
•PAYMENT_PRIORITY
•ORG_ID
 Table 6 : AP_CHECKS_ALL

Description: Supplier payment data. AP_CHECKS_ALL stores information about

payments issued to suppliers or refunds received from suppliers.You need one row
for each payment you issue to a supplier or refund received from a supplier. Your
Oracle Payables application uses this information to record payments you make to
suppliers or refunds you receive from suppliers. Your Oracle Payables application
stores the supplier name and bank account name for auditing purposes, in case
either one is changed after you create the payment. Your Oracle Payables
application stores address information for all payments. If you allow changes to the
supplier payment address on manual payments or Quick payments, your Oracle
Payables application maintains the new address information in this table. Your
Oracle Payables application uses BANK_ACCOUNT_NUM, BANK_NUM, and
BANK_ACCOUNT_TYPE for the supplier's bank information when you use the
Electronic payment method. Your Oracle Payables application stores a dummy
value for CHECK_STOCK_ID for refunds, thus, CHECK_STOCK_ID should not be
treated as a foreign key to AP_CHECK_STOCKS_ALL in the case of refunds.
 Table 6 : AP_CHECKS_ALL
Primary Key: AP_CHECKS_PK
CHECK_ID
Primary Key: AP_CHECKS_UK1
CHECK_STOCK_ID ; CHECK_NUMBER
Primary Key: AP_CHECKS_UK2
DOC_SEQUENCE_ID ;DOC_SEQUENCE_VALUE
Foreign Keys to this table:
Foreign Key Column Foreign Table

AP_INVOICE_PAYMENTS_ALL.CHECK_ID
AP_INVOICE_PAYMENTS_ALL.CHECK_ID AP_CHECKS_ALL

AP_PAYMENT_HISTORY_ALL.CHECK_ID AP_CHECKS_ALL

AP_SELECTED_INVOICE_CHECKS_ALL.CHECK_ID
AP_SELECTED_INVOICE_CHECKS_ALL.CHECK_ID AP_CHECKS_ALL

FV_REFUNDS_VOIDS_ALL.CHECK_ID
FV_REFUNDS_VOIDS_ALL.CHECK_ID AP_CHECKS_ALL

JAI_AP_ETDS_T.TDS_CHECK_ID AP_CHECKS_ALL

JAI_AP_ETDS_T.BASE_INVOICE_CHECK_ID
JAI_AP_ETDS_T.BASE_INVOICE_CHECK_ID AP_CHECKS_ALL

JAI_AP_TDS_INV_PAYMENTS.CHECK_ID
JAI_AP_TDS_INV_PAYMENTS.CHECK_ID AP_CHECKS_ALL
 Table 6 : AP_CHECKS_ALL
Foreign Keys from this table:

Foreign Key Column Foreign Table

AP_CHECKS_ALL.CE_BANK_ACCT_USE_ID
AP_CHECKS_ALL.CE_BANK_ACCT_USE_ID CE_BANK_ACCT_USES_ALL

AP_CHECKS_ALL.BANK_ACCOUNT_ID AP_BANK_ACCOUNTS_ALL

AP_CHECKS_ALL.VENDOR_ID PO_VENDORS

AP_CHECKS_ALL.VENDOR_SITE_ID PO_VENDOR_SITES_ALL

AP_CHECKS_ALL.EXCHANGE_RATE_TYPE
AP_CHECKS_ALL.EXCHANGE_RATE_TYPE GL_DAILY_CONVERSION_TYPES

AP_CHECKS_ALL.CLEARED_EXCHANGE_RATE_TYPE GL_DAILY_CONVERSION_TYPES

AP_CHECKS_ALL.MATURITY_EXCHANGE_RATE_TYPE
AP_CHECKS_ALL.MATURITY_EXCHANGE_RATE_TYPE GL_DAILY_CONVERSION_TYPES

AP_CHECKS_ALL.EXTERNAL_BANK_ACCOUNT_ID AP_BANK_ACCOUNTS_ALL

AP_CHECKS_ALL.CHECKRUN_ID
AP_CHECKS_ALL.CHECKRUN_ID AP_INV_SELECTION_CRITERIA_ALL

AP_CHECKS_ALL.PAYMENT_INSTRUCTION_ID %

AP_CHECKS_ALL.CHECK_FORMAT_ID
AP_CHECKS_ALL.CHECK_FORMAT_ID AP_CHECK_FORMATS

AP_CHECKS_ALL.CHECK_STOCK_ID AP_CHECK_STOCKS_ALL

AP_CHECKS_ALL.CHECKRUN_NAME AP_INV_SELECTION_CRITERIA_ALL

AP_CHECKS_ALL.CURRENCY_CODE
AP_CHECKS_ALL.CURRENCY_CODE FND_CURRENCIES

AP_CHECKS_ALL.DOC_SEQUENCE_ID AP_DOC_SEQUENCE_AUDIT
AP_CHECKS_ALL.DOC_SEQUENCE_VALUE
AP_CHECKS_ALL.DOC_SEQUENCE_VALUE

AP_CHECKS_ALL.DOC_SEQUENCE_ID
AP_CHECKS_ALL.DOC_SEQUENCE_ID FND_DOCUMENT_SEQUENCES
 Table 6 : AP_CHECKS_ALL

•AMOUNT
•BANK_ACCOUNT_ID
•BANK_ACCOUNT_NAME
•CHECK_DATE
•CHECK_NUMBER
•CURRENCY_CODE
•PAYMENT_METHOD_LOOKUP_CODE
•PAYMENT_TYPE_FLAG
•VENDOR_NAME
•VENDOR_SITE_CODE
•BANK_ACCOUNT_NUM
•STAMP_DUTY_AMT
•LEGAL_ENTITY_ID
 Table 7 : AP_PAYMENT_HISTORY_ALL

Description: Maturity and reconciliation history for payments.

AP_PAYMENT_HISTORY_ALL stores the clearing/unclearing history for

payments. It also stores the maturity history for future dated payments. The table

contains a row for each future dated payment, once the future dated payment

matures, i.e. becomes negotiable. Any time a payment is cleared or uncleared, a

row is inserted into this table for the payment. The values for

TRANSACTION_TYPE can be PAYMENT MATURITY, PAYMENT CLEARING, or

PAYMENT UNCLEARING. Each row in this table also has the accounting status

for the maturity, clearing or unclearing event.

 Table 7 : AP_PAYMENT_HISTORY_ALL
Primary Key:
AP_PAYMENT_HISTORY_PK
1.PAYMENT_HISTORY_ID

Foreign Keys from this table:

Foreign Key Column Foreign Table

AP_PAYMENT_HISTORY_ALL.CHECK_ID
AP_PAYMENT_HISTORY_ALL.CHECK_ID AP_CHECKS_ALL
AP_PAYMENT_HISTORY_ALL.ACCOUNTING_EVENT_ID AP_ACCOUNTING_EVENTS_ALL
AP_PAYMENT_HISTORY_ALL.PAYMENT_HISTORY_ID AP_PAYMENT_HISTORY_ALL

Foreign Keys to this table:

Foreign Key Column Foreign Table

AP_PAYMENT_HISTORY_ALL.PAYMENT_HISTORY_ID AP_PAYMENT_HISTORY_ALL
Table 7 : AP_PAYMENT_HISTORY_ALL

•PAYMENT_HISTORY_ID
•CHECK_ID
•ACCOUNTING_DATE
•TRANSACTION_TYPE
•POSTED_FLAG
•ORG_ID
•BANK_CURRENCY_CODE
•CHARGES_BANK_AMOUNT
 Table 8 : AP_HOLDS_ALL
Description: Invoice hold information.

AP_HOLDS_ALL contains information about holds that you or your Oracle Payables
application place on an invoice. For non-matching holds, there is one row for each
hold placed on an invoice. For matching holds, there is one row for each hold placed
on an invoice-shipment match. An invoice may have one or more corresponding rows
in this table. Your Oracle Payables application does not pay invoices that have one or
more unreleased holds recorded in this table.
In the strictest sense, AP_HOLDS_ALL has no primary key. It is possible for your
Oracle Payables application to place a certain type of hold on an invoice, then release
it, then place another hold of the same type (if data changes before each submission
of Payables Invoice Validation), which would result in a duplicate primary key. But for
practical purposes, the primary key is a concatenation of INVOICE_ID,
LINE_LOCATION_ID, and HOLD_LOOKUP_CODE.
 Table 8 : AP_HOLDS_ALL
Foreign Keys from this table:

Foreign Key Column Foreign Table

AP_HOLDS_ALL.INVOICE_ID AP_INVOICES_ALL

AP_HOLDS_ALL.HOLD_LOOKUP_CODE
AP_HOLDS_ALL.HOLD_LOOKUP_CODE AP_HOLD_CODES

AP_HOLDS_ALL.RELEASE_LOOKUP_CODE AP_HOLD_CODES

AP_HOLDS_ALL.LINE_LOCATION_ID PO_LINE_LOCATIONS_ALL

AP_HOLDS_ALL.HELD_BY
AP_HOLDS_ALL.HELD_BY FND_USER

AP_HOLDS_ALL.RCV_TRANSACTION_ID RCV_TRANSACTIONS
 Table 8 : AP_HOLDS_ALL

•INVOICE_ID
•HOLD_LOOKUP_CODE
•HOLD_DATE
•HOLD_REASON
•RCV_TRANSACTION_ID
•HOLD_DETAILS
 Table 9 : AP_AE_HEADERS_ALL

Description: Accounting entry headers table.

An accounting entry header is an entity grouping all accounting entry lines created

for a given accounting event and a particular set of books. An accounting entry

header can either be transferred over to GL or not at all. That is, either all its

accounting entry lines are transferred or none at all. The transferred to GL status is

marked in the GL_TRANSFER_FLAG. Possible values for GL_TRANSFER_FLAG

are Y, N, or E. Y indicates that the accounting entry header has been transferred to

GL. N indicates that the accounting entry header has not been transferred to GL

due to 2 possible reasons: either the transfer process has not run or it has run but

the accounting entry had an accounting error on it. E indicates that an error was

encountered during the transfer to GL process.

 Table 9 : AP_AE_HEADERS_ALL

Primary Key: AP_AE_HEADERS_PK

1.AE_HEADER_ID

Foreign Keys from this table:

Foreign Key Column Foreign Table

AP_AE_HEADERS_ALL.ACCOUNTING_EVENT_ID AP_ACCOUNTING_EVENTS_ALL

AP_AE_HEADERS_ALL.SET_OF_BOOKS_ID %

Foreign Keys to this table:

Foreign Key Column Foreign Table

AP_AE_LINES_ALL.AE_HEADER_ID AP_AE_HEADERS_ALL

AP_ENCUMBRANCE_LINES_ALL.AE_HEADER_ID AP_AE_HEADERS_ALL
 Table 9 : AP_AE_HEADERS_ALL

•AE_HEADER_ID
•SET_OF_BOOKS_ID
•AE_CATEGORY
•ACCOUNTING_DATE
•GL_TRANSFER_FLAG
•TRIAL_BALANCE_FLAG
•DESCRIPTION
•ORG_ID
•PERIOD_NAME
 Table 10 : AP_AE_LINES_ALL

Description: Accounting entry lines table

An accounting entry line is an entity containing a proper accounting entry with

debits or credits both in transaction currency as well as functional currency along

with an account and other reference information pointing to the transaction data

that originated the accounting entry line. An accounting entry line is grouped with

other accounting entry lines for a specific accounting entry header. Any such group

of accounting entry lines should result in balanced entries in the functional

currency.
 Table 10 : AP_AE_LINES_ALL
Primary Key: AP_AE_LINES_PK
1.AE_LINE_ID

Foreign Keys from this table:

Foreign Key Column Foreign Table

AP_AE_LINES_ALL.AE_HEADER_ID
AP_AE_LINES_ALL.AE_HEADER_ID AP_AE_HEADERS_ALL

AP_AE_LINES_ALL.CURRENCY_CODE FND_CURRENCIES

AP_AE_LINES_ALL.SUBLEDGER_DOC_SEQUENCE_ID
AP_AE_LINES_ALL.SUBLEDGER_DOC_SEQUENCE_ID FND_DOCUMENT_SEQUENCES

AP_AE_LINES_ALL.CURRENCY_CONVERSION_TYPE GL_DAILY_CONVERSION_TYPES

AP_AE_LINES_ALL.THIRD_PARTY_ID PO_VENDORS

AP_AE_LINES_ALL.THIRD_PARTY_SUB_ID PO_VENDOR_SITES_ALL
 Table 10 : AP_AE_LINES_ALL

•AE_LINE_ID
•AE_HEADER_ID
•AE_LINE_TYPE_CODE
•CODE_COMBINATION_ID
•ENTERED_DR
•ENTERED_CR
•ACCOUNTED_DR
•ACCOUNTED_CR
•SOURCE_TABLE
•SOURCE_ID
 JOINING BETWEEN AP_INVOICES_ALL AND AP_INVOICE_LINES_ALL

QUERY:

SELECT A.VENDOR_ID,
A.INVOICE_NUM,
A.INVOICE_AMOUNT,
A.INVOICE_TYPE_LOOKUP_CODE,
B.DESCRIPTION,
B.MANUFACTURER,
B.ACCOUNTING_DATE,
B.UNIT_PRICE
FROM AP_INVOICES_ALL A,
AP_INVOICE_LINES_ALL B
WHERE A.INVOICE_ID = B.INVOICE_ID
JOINING BETWEEN AP_INVOICES_ALL AND AP_INVOICE_PAYMENTS_ALL

QUERY:

SELECT A.INVOICE_NUM,
A.INVOICE_AMOUNT,
A.INVOICE_DATE,
A.VOUCHER_NUM,
B.CHECK_ID,
B.AMOUNT,
B.BANK_ACCOUNT_NUM
FROM AP_INVOICES_ALL A ,
AP_INVOICE_PAYMENTS_ALL B
WHERE A.INVOICE_ID = B.INVOICE_ID
 Table 1: MTL_SYSTEM_ITEMS_B
Description: Inventory item definitions. MTL_SYSTEM_ITEMS_B is the definition
table for items. This table holds the definitions for inventory items, engineering
items, and purchasing items. You can specify item-related information in fields such
as:
Bill of Material, Costing, Purchasing, Receiving, Inventory, Physical attributes,
General Planning, MPS/MRP Planning, Lead times, Work in Process, Order
Management, and Invoicing.
You can set up the item with multiple segments, since it is implemented as a
flexfield. Use the standard 'System Items' flexfield that is shipped with the product to
configure your item flexfield. The flexfield code is MSTK.
Each item is initially defined in an item master organization. The user then assigns
the item to other organizations that need to recognize this item. A row is inserted for
each new organization the item is assigned to. Many columns such as
MTL_TRANSACTIONS_ENABLED_FLAG and BOM_ENABLED_FLAG correspond
to item attributes defined in the MTL_ITEM_ATTRIBUTES table. The attributes that
are available to the user depend on which Oracle applications are installed. The
table MTL_ATTR_APPL_DEPENDENCIES maintains the relationships between
item attributes and Oracle applications.
 Table 1: MTL_SYSTEM_ITEMS_B
Two unit of measure columns are stored in MTL_SYSTEM_ITEMS_B table.
PRIMARY_UOM_CODE is the 3-character unit that is used throughout Oracle
Manufacturing. PRIMARY_UNIT_OF_MEASURE is the 25-character Unit of Measure
that is used throughout Oracle Purchasing. Unlike the PRIMARY_UOM_CODE, the
Unit of Measure is language-dependent attribute, however,
PRIMARY_UNIT_OF_MEASURE column stores value in the installation base
language only.
Items now support multilingual description. MLS is implemented with a pair of tables:
MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL. Translations table
(MTL_SYSTEM_ITEMS_TL) holds item Description and Long Description in multiple
languages. DESCRIPTION column in the base table (MTL_SYSTEM_ITEMS_B) is for
backward compatibility and is maintained in the installation base language only.
 Table 1: MTL_SYSTEM_ITEMS_B
Primary Key: MTL_SYSTEM_ITEMS_B_PK
INVENTORY_ITEM_ID
ORGANIZATION_ID

Foreign Keys from this table:

Foreign Key Column Foreign Table

MTL_SYSTEM_ITEMS_B.COST_OF_SALES_ACCOUNT
MTL_SYSTEM_ITEMS_B.COST_OF_SALES_ACCOUNT GL_CODE_COMBINATIONS

MTL_SYSTEM_ITEMS_B.DEFAULT_SHIPPING_ORG MTL_PARAMETERS

MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_STATUS_CODE MTL_ITEM_STATUS

MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID
MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID MTL_PARAMETERS

MTL_SYSTEM_ITEMS_B.ENGINEERING_ITEM_ID MTL_SYSTEM_ITEMS_B
MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID

MTL_SYSTEM_ITEMS_B.ACCOUNTING_RULE_ID
MTL_SYSTEM_ITEMS_B.ACCOUNTING_RULE_ID RA_RULES

MTL_SYSTEM_ITEMS_B.SOURCE_ORGANIZATION_ID MTL_PARAMETERS

MTL_SYSTEM_ITEMS_B.SOURCE_SUBINVENTORY
MTL_SYSTEM_ITEMS_B.SOURCE_SUBINVENTORY MTL_SECONDARY_INVENTORIES
MTL_SYSTEM_ITEMS_B.SOURCE_ORGANIZATION_ID

MTL_SYSTEM_ITEMS_B.HAZARD_CLASS_ID PO_HAZARD_CLASSES_B

MTL_SYSTEM_ITEMS_B.INVOICING_RULE_ID RA_RULES

MTL_SYSTEM_ITEMS_B.UN_NUMBER_ID PO_UN_NUMBERS_B

MTL_SYSTEM_ITEMS_B.ASSET_CATEGORY_ID
MTL_SYSTEM_ITEMS_B.ASSET_CATEGORY_ID FA_CATEGORIES_B
 Foreign Keys from this table:
Foreign Key Column Foreign Table

MTL_SYSTEM_ITEMS_B.BASE_ITEM_ID
MTL_SYSTEM_ITEMS_B.BASE_ITEM_ID MTL_SYSTEM_ITEMS_B
MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID

MTL_SYSTEM_ITEMS_B.WIP_SUPPLY_LOCATOR_ID
MTL_SYSTEM_ITEMS_B.WIP_SUPPLY_LOCATOR_ID MTL_ITEM_LOCATIONS
MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID
MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID

MTL_SYSTEM_ITEMS_B.WIP_SUPPLY_SUBINVENTORY
MTL_SYSTEM_ITEMS_B.WIP_SUPPLY_SUBINVENTORY MTL_SECONDARY_INVENTORIES
MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID

MTL_SYSTEM_ITEMS_B.RECEIVING_ROUTING_ID RCV_ROUTING_HEADERS

MTL_SYSTEM_ITEMS_B.ATP_RULE_ID MTL_ATP_RULES

MTL_SYSTEM_ITEMS_B.BASE_WARRANTY_SERVICE_ID MTL_SYSTEM_ITEMS_B
MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID

MTL_SYSTEM_ITEMS_B.PAYMENT_TERMS_ID
MTL_SYSTEM_ITEMS_B.PAYMENT_TERMS_ID RA_TERMS_B

MTL_SYSTEM_ITEMS_B.ITEM_CATALOG_GROUP_ID
MTL_SYSTEM_ITEMS_B.ITEM_CATALOG_GROUP_ID MTL_ITEM_CATALOG_GROUPS_B

MTL_SYSTEM_ITEMS_B.EXPENSE_ACCOUNT GL_CODE_COMBINATIONS

MTL_SYSTEM_ITEMS_B.ENCUMBRANCE_ACCOUNT GL_CODE_COMBINATIONS

MTL_SYSTEM_ITEMS_B.ENGINEERING_ECN_CODE
MTL_SYSTEM_ITEMS_B.ENGINEERING_ECN_CODE ENG_ENGINEERING_CHANGES
MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID

MTL_SYSTEM_ITEMS_B.PRODUCT_FAMILY_ITEM_ID
MTL_SYSTEM_ITEMS_B.PRODUCT_FAMILY_ITEM_ID MTL_SYSTEM_ITEMS_B
MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID

MTL_SYSTEM_ITEMS_B.SALES_ACCOUNT
MTL_SYSTEM_ITEMS_B.SALES_ACCOUNT GL_CODE_COMBINATIONS

MTL_SYSTEM_ITEMS_B.PICKING_RULE_ID
MTL_SYSTEM_ITEMS_B.PICKING_RULE_ID MTL_PICKING_RULES

MTL_SYSTEM_ITEMS_B.PLANNER_CODE MTL_PLANNERS
MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID
 Table 1: MTL_SYSTEM_ITEMS_B

•INVENTORY_ITEM_ID
•ORGANIZATION_ID
•SUMMARY_FLAG
•ENABLED_FLAG
•BUYER_ID
•DESCRIPTION
•PURCHASING_ITEM_FLAG
•RECEIPT_REQUIRED_FLAG
•MARKET_PRICE
•LIST_PRICE_PER_UNIT
•ASSET_CATEGORY_ID
•SALES_ACCOUNT
•ITEM_TYPE
 Table 2: MTL_ITEM_REVISIONS_B
Description:

MTL_ITEM_REVISIONS stores revision levels for an inventory item. When an item

is defined a starting revision record is written out to this table, so every item will at

least have one starting revision.

The presence of an engineering change notice code in this table

indicates that the engineering system issued the revision through the

implementation process. Along with the engineering change order, an

implementation date and initiation date will be stored .

 Table 2: MTL_ITEM_REVISIONS_B
Primary Key: MTL_ITEM_REVISIONS_B_PK
INVENTORY_ITEM_ID
ORGANIZATION_ID
REVISION_ID

Foreign Keys from this table:

Foreign Key Column Foreign Table

MTL_ITEM_REVISIONS_B.INVENTORY_ITEM_ID
MTL_ITEM_REVISIONS_B.INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS_B
MTL_ITEM_REVISIONS_B.ORGANIZATION_ID

MTL_ITEM_REVISIONS_B.CHANGE_NOTICE
MTL_ITEM_REVISIONS_B.CHANGE_NOTICE ENG_ENGINEERING_CHANGES
MTL_ITEM_REVISIONS_B.ORGANIZATION_ID

MTL_ITEM_REVISIONS_B.ORGANIZATION_ID
MTL_ITEM_REVISIONS_B.ORGANIZATION_ID MTL_PARAMETERS
 Table 2: MTL_ITEM_REVISIONS_B

•INVENTORY_ITEM_ID
•ORGANIZATION_ID
•REVISION
•CHANGE_NOTICE
•ECN_INITIATION_DATE
•IMPLEMENTATION_DATE
•EFFECTIVITY_DATE
•REVISED_ITEM_SEQUENCE_ID
•REVISION_ID
•REVISION_LABEL
•REVISION_REASON
 Table 3: MTL_CATEGORIES_B
Description: MTL_CATEGORIES_B is the code combinations table for item
categories. Items are grouped into categories within the context of a category set
to provide flexible grouping schemes.
The item category is a key flexfield with a flex code of MCAT. The flexfield structure
identifier is also stored in this table to support the ability to define more than one
flexfield structure (multi-flex).
Item categories now support multilingual category description. MLS is implemented
with a pair of tables: MTL_CATEGORIES_B and MTL_CATEGORIES_TL.
MTL_CATEGORIES_TL table holds translated Description for Categories.

Primary Key: MTL_CATEGORIES_B_PK

CATEGORY_ID
 Table 3: MTL_CATEGORIES_B

•CATEGORY_ID
•STRUCTURE_ID
•DESCRIPTION
•DISABLE_DATE
•SUMMARY_FLAG
•ENABLED_FLAG
•ATTRIBUTE_CATEGORY
 Table 4: MTL_CATEGORY_SETS_B

Description: MTL_CATEGORY_SETS_B contains the entity definition for category

sets. A category set is a categorization scheme for a group of items. Items may be
assigned to different categories in different category sets to represent the different
groupings of items used for different purposes. An item may be assigned to only one
category within a category set, however.
STRUCTURE_ID identifies the flexfield structure associated with the category set.
Only categories with the same flexfield structure may be grouped into a category set.
CONTROL_LEVEL defines whether the category set is controlled at the item or the
item/organization level. When an item is assigned to an item level category set within
the item master organization, the category set assignment is propagated to all other
organizations to which the item is assigned.
VALIDATE_FLAG defines whether a list of valid categories is used to validate
category usage within the set. Validated category sets will not allow item assignment
to the category set in categories that are not in a predefined list of valid categories.
 Table 4: MTL_CATEGORY_SETS_B

Category Sets now support multilingual category set name and description. MLS is
implemented with a pair of tables: MTL_CATEGORY_SETS_B and
MTL_CATEGORY_SETS_TL. MTL_CATEGORY_SETS_TL table holds translated
Name and Description for Category Sets.

Primary Key: MTL_CATEGORY_SETS_B_PK

CATEGORY_SET_ID

Foreign Keys from this table:

Foreign Key Column Foreign Table

MTL_CATEGORY_SETS_B.DEFAULT_CATEGORY_ID
MTL_CATEGORY_SETS_B.DEFAULT_CATEGORY_ID MTL_CATEGORIES_B
 Table 4: MTL_CATEGORY_SETS_B

•CATEGORY_SET_ID
•STRUCTURE_ID
•CONTROL_LEVEL
•DEFAULT_CATEGORY_ID
•MULT_ITEM_CAT_ASSIGN_FLAG
•CONTROL_LEVEL_UPDATEABLE_FLAG
•MULT_ITEM_CAT_UPDATEABLE_FLAG
 Table 5: MTL_ITEM_CATEGORIES

Description: MTL_ITEM_CATEGORIES stores inventory item assignments to

categories within a category set. For each category assignment, this table stores the
item, the category set, and the category. Items always may be assigned to multiple
category sets. However, depending on the Multiple Assignments Allowed attribute
value in a given category set definition, an item can be assigned to either many or
only one category in that category set.
This table may be populated through the Master Items and
Organization Items windows. It can also be populated by
performing item assignments when a category set is defined. It is
also populated when an item is transferred from engineering to
manufacturing. The table may also be populated through the Item Category Open
Interface.
 Table 5: MTL_ITEM_CATEGORIES
Primary Key: MTL_ITEM_CATEGORIES_PK
INVENTORY_ITEM_ID
ORGANIZATION_ID
CATEGORY_SET_ID
Foreign Keys from this table:

Foreign Key Column Foreign Table

MTL_ITEM_CATEGORIES.INVENTORY_ITEM_ID
MTL_ITEM_CATEGORIES.INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS_B
MTL_ITEM_CATEGORIES.ORGANIZATION_ID

MTL_ITEM_CATEGORIES.CATEGORY_SET_ID MTL_CATEGORY_SET_VALID_CATS
MTL_ITEM_CATEGORIES.CATEGORY_ID

MTL_ITEM_CATEGORIES.ORGANIZATION_ID
MTL_ITEM_CATEGORIES.ORGANIZATION_ID MTL_PARAMETERS

Foreign Keys to this table:

Foreign Key Column Foreign Table

AMS_ACT_PRODUCTS.INVENTORY_ITEM_ID MTL_ITEM_CATEGORIES
AMS_ACT_PRODUCTS.ORGANIZATION_ID
AMS_ACT_PRODUCTS.ORGANIZATION_ID
AMS_ACT_PRODUCTS.CATEGORY_SET_ID

MRP_SR_ASSIGNMENTS.INVENTORY_ITEM_ID MTL_ITEM_CATEGORIES
MRP_SR_ASSIGNMENTS.ORGANIZATION_ID
MRP_SR_ASSIGNMENTS.ORGANIZATION_ID
MRP_SR_ASSIGNMENTS.CATEGORY_SET_ID
 Table 5: MTL_ITEM_CATEGORIES

•INVENTORY_ITEM_ID
•ORGANIZATION_ID
•CATEGORY_SET_ID
•CATEGORY_ID
•CREATION_DATE
•REQUEST_ID
•PROGRAM_ID
•PROGRAM_UPDATE_DATE
•WH_UPDATE_DATE
 Table 6: MTL_MATERIAL_TRANSACTIONS
Description: MTL_MATERIAL_TRANSACTIONS stores a record of every material
transaction or cost update performed in Inventory.
Records are inserted into this table either through the transaction processor or by the
standard cost update program. The columns TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID, TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_ID and TRANSACTION_SOURCE_NAME describe
what the transaction is and against what entity it was performed.
All accounting journal entries for a given transaction are stored in
MTL_TRANSACTION_ACCOUNTS, joined by the column TRANSACTION_ID.
If the item is under lot control then the lot records are stored in
MTL_TRANSACTION_LOT_NUMBERS, joined by the column TRANSACTION_ID.
If the item is under serial control then the serial records are stored in
MTL_UNIT_TRANSACTIONS, joined by the column TRANSACTION_ID. The Item
revision and locator control are stored in the columns REVISION and LOCATOR_ID
respectively. Transfer transactions are represented as two single records in the
table. They are related through the column TRANSFER_TRANSACTION_ID, which
contains the TRANSACTION_ID of the other transaction in the transfer.
 Table 6: MTL_MATERIAL_TRANSACTIONS
Primary Key: MTL_MATERIAL_TRANSACTIONS_PK
TRANSACTION_ID
Foreign Keys from this table:

Foreign Key Column Foreign Table

MTL_MATERIAL_TRANSACTIONS.TRANSFER_COST_DIST_ACCOUNT
MTL_MATERIAL_TRANSACTIONS.TRANSFER_COST_DIST_ACCOUNT GL_CODE_COMBINATIONS

MTL_MATERIAL_TRANSACTIONS.INVENTORY_ITEM_ID MTL_ITEM_REVISIONS_B
MTL_MATERIAL_TRANSACTIONS.ORGANIZATION_ID
MTL_MATERIAL_TRANSACTIONS.REVISION
MTL_MATERIAL_TRANSACTIONS.REVISION

MTL_MATERIAL_TRANSACTIONS.CYCLE_COUNT_ID MTL_CYCLE_COUNT_ENTRIES

MTL_MATERIAL_TRANSACTIONS.PHYSICAL_ADJUSTMENT_ID
MTL_MATERIAL_TRANSACTIONS.PHYSICAL_ADJUSTMENT_ID MTL_PHYSICAL_ADJUSTMENTS

MTL_MATERIAL_TRANSACTIONS.TRANSACTION_TYPE_ID MTL_TRANSACTION_TYPES

MTL_MATERIAL_TRANSACTIONS.CURRENCY_CODE GL_DAILY_CONVERSION_TYPES

MTL_MATERIAL_TRANSACTIONS.INVENTORY_ITEM_ID
MTL_MATERIAL_TRANSACTIONS.INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS_B
MTL_MATERIAL_TRANSACTIONS.ORGANIZATION_ID

MTL_MATERIAL_TRANSACTIONS.TRANSFER_COST_GROUP_ID CST_COST_GROUPS
 Table 6: MTL_MATERIAL_TRANSACTIONS
Foreign Keys from this table:
Foreign Key Column Foreign Table

MTL_MATERIAL_TRANSACTIONS.TRANSACTION_SOURCE_ID WIP_DISCRETE_JOBS
MTL_MATERIAL_TRANSACTIONS.TRANSACTION_SOURCE_ID WIP_REPETITIVE_ITEMS
MTL_MATERIAL_TRANSACTIONS.REPETITIVE_LINE_ID
MTL_MATERIAL_TRANSACTIONS.REPETITIVE_LINE_ID
MTL_MATERIAL_TRANSACTIONS.LOCATOR_ID MTL_ITEM_LOCATIONS
MTL_MATERIAL_TRANSACTIONS.ORGANIZATION_ID
MTL_MATERIAL_TRANSACTIONS.ACCT_PERIOD_ID ORG_ACCT_PERIODS
MTL_MATERIAL_TRANSACTIONS.ORGANIZATION_ID
MTL_MATERIAL_TRANSACTIONS.ORGANIZATION_ID
MTL_MATERIAL_TRANSACTIONS.TRANSPORTATION_DIST_ACCOUNT GL_CODE_COMBINATIONS
MTL_MATERIAL_TRANSACTIONS.SUBINVENTORY_CODE MTL_SECONDARY_INVENTORIES
MTL_MATERIAL_TRANSACTIONS.ORGANIZATION_ID
MTL_MATERIAL_TRANSACTIONS.ORGANIZATION_ID
MTL_MATERIAL_TRANSACTIONS.TRANSACTION_SOURCE_ID WIP_FLOW_SCHEDULES
MTL_MATERIAL_TRANSACTIONS.RCV_TRANSACTION_ID
MTL_MATERIAL_TRANSACTIONS.RCV_TRANSACTION_ID RCV_TRANSACTIONS
MTL_MATERIAL_TRANSACTIONS.MOVE_TRANSACTION_ID WIP_MOVE_TRANSACTIONS
MTL_MATERIAL_TRANSACTIONS.TRANSACTION_SOURCE_TYPE_ID MTL_TXN_SOURCE_TYPES
MTL_MATERIAL_TRANSACTIONS.MOVE_ORDER_LINE_ID
MTL_MATERIAL_TRANSACTIONS.MOVE_ORDER_LINE_ID MTL_TXN_REQUEST_LINES
MTL_MATERIAL_TRANSACTIONS.REASON_ID
MTL_MATERIAL_TRANSACTIONS.REASON_ID MTL_TRANSACTION_REASONS
MTL_MATERIAL_TRANSACTIONS.DISTRIBUTION_ACCOUNT_ID GL_CODE_COMBINATIONS
MTL_MATERIAL_TRANSACTIONS.ENCUMBRANCE_ACCOUNT GL_CODE_COMBINATIONS
MTL_MATERIAL_TRANSACTIONS.COST_GROUP_ID
MTL_MATERIAL_TRANSACTIONS.COST_GROUP_ID CST_COST_GROUPS
Table 6: MTL_MATERIAL_TRANSACTIONS

•TRANSACTION_ID
•INVENTORY_ITEM_ID
•TRANSACTION_TYPE_ID
•TRANSACTION_ACTION_ID
•TRANSACTION_SOURCE_TYPE_ID
•TRANSACTION_SOURCE_ID
•TRANSACTION_SOURCE_NAME
•TRANSACTION_QUANTITY
•TRANSACTION_UOM
•TRANSACTION_DATE
•RCV_TRANSACTION_ID
•SHIPMENT_NUMBER
 Table 7: MTL_TRANSACTION_TYPES

Description: Inventory Transaction Types Table

MTL_TRANSACTION_TYPES contains seeded transaction types and the user

defined ones. USER_DEFINED_FLAG will distinguish the two. The table also stores
the TRANSACTION_ACTION_ID and TRANSACTION_SOURCE_TYPE_ID that is
associated with each transaction type

Primary Key: MTL_TRANSACTION_TYPES_PK

TRANSACTION_TYPE_ID
Table 7: MTL_TRANSACTION_TYPES
Foreign Keys from this table:

Foreign Key Column Foreign Table

MTL_TRANSACTION_TYPES.TRANSACTION_SOURCE_TYPE_ID
MTL_TRANSACTION_TYPES.TRANSACTION_SOURCE_TYPE_ID MTL_TXN_SOURCE_TYPES

Foreign Keys to this table:

Foreign Key Column Foreign Table

AHL_WORKORDER_MTL_TXNS.TRANSACTION_TYPE_ID MTL_TRANSACTION_TYPES

CSP_USAGE_HISTORIES.TRANSACTION_TYPE_ID MTL_TRANSACTION_TYPES

EAM_JOB_COMPLETION_TXNS.TRANSACTION_TYPE MTL_TRANSACTION_TYPES

EAM_OP_COMPLETION_TXNS.TRANSACTION_TYPE MTL_TRANSACTION_TYPES

JAI_CMN_TRANSACTIONS_T.TRANSACTION_TYPE_ID
JAI_CMN_TRANSACTIONS_T.TRANSACTION_TYPE_ID MTL_TRANSACTION_TYPES

JAI_PO_OSP_CENVAT_HDRS.TRANSACTION_TYPE_ID
JAI_PO_OSP_CENVAT_HDRS.TRANSACTION_TYPE_ID MTL_TRANSACTION_TYPES

MTL_MATERIAL_TRANSACTIONS.TRANSACTION_TYPE_ID MTL_TRANSACTION_TYPES

MTL_STATUS_TRANSACTION_CONTROL.TRANSACTION_TYPE_ID MTL_TRANSACTION_TYPES

MTL_TXN_REQUEST_HEADERS.TRANSACTION_TYPE_ID MTL_TRANSACTION_TYPES

MTL_TXN_REQUEST_LINES.TRANSACTION_TYPE_ID MTL_TRANSACTION_TYPES
Table 7: MTL_TRANSACTION_TYPES

•TRANSACTION_TYPE_ID
•TRANSACTION_TYPE_NAME
•TRANSACTION_ACTION_ID
•TRANSACTION_SOURCE_TYPE_ID
•DISABLE_DATE
•TYPE_CLASS
•LOCATION_REQUIRED_FLAG
•STATUS_CONTROL_FLAG
•TYPE_CLASS
 Table 8: MTL_LOT_NUMBERS

Description: Lot number definitions.

MTL_LOT_NUMBERS stores the definition and expiration date of all lot numbers

in inventory.

Since the primary key consists of LOT_NUMBER, ORGANIZATION_ID, and

INVENTORY_ITEM_ID, a lot number could exist in more than one organization or

for different items.

 Table 8: MTL_LOT_NUMBERS
Primary Key: MTL_LOT_NUMBER_PK
1.INVENTORY_ITEM_ID
2.ORGANIZATION_ID
3.LOT_NUMBER

Foreign Keys from this table:

Foreign Key Column Foreign Table

MTL_LOT_NUMBERS.INVENTORY_ITEM_ID
MTL_LOT_NUMBERS.INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS_B
MTL_LOT_NUMBERS.ORGANIZATION_ID

MTL_LOT_NUMBERS.ORGANIZATION_ID MTL_PARAMETERS

MTL_LOT_NUMBERS.STATUS_ID
MTL_LOT_NUMBERS.STATUS_ID MTL_MATERIAL_STATUSES_B
 Table 8: MTL_LOT_NUMBERS

•INVENTORY_ITEM_ID
•LOT_NUMBER
•EXPIRATION_DATE
•VENDOR_NAME
•ITEM_SIZE
•COLOR
•VOLUME
•VENDOR_ID
 Table 9: MTL_SERIAL_NUMBERS
Description: Serial number definitions
MTL_SERIAL_NUMBERS stores the definition and current status of all serial
numbers in Oracle Inventory. These serial numbers are also used in other areas
of Oracle Manufacturing..
A serial number can have one of four statuses:
- Defined but not used
- Resides in stores
- Issued out of stores
- Resides in intransit
.
The serial generation program populates this table, given the number
of serial numbers to create and the starting number and prefix.

Primary Key: MTL_SERIAL_NUMBERS_PK

SERIAL_NUMBER
INVENTORY_ITEM_ID
CURRENT_ORGANIZATION_ID
 Table 9: MTL_SERIAL_NUMBERS
Foreign Keys from this table:

Foreign Key Column Foreign Table

MTL_SERIAL_NUMBERS.LPN_ID
MTL_SERIAL_NUMBERS.LPN_ID WMS_LICENSE_PLATE_NUMBERS
MTL_SERIAL_NUMBERS.CURRENT_ORGANIZATION_ID MTL_PARAMETERS
MTL_SERIAL_NUMBERS.INVENTORY_ITEM_ID MTL_LOT_NUMBERS
MTL_SERIAL_NUMBERS.CURRENT_ORGANIZATION_ID
MTL_SERIAL_NUMBERS.LOT_NUMBER
MTL_SERIAL_NUMBERS.LOT_NUMBER
MTL_SERIAL_NUMBERS.ORIGINAL_UNIT_VENDOR_ID PO_VENDORS
MTL_SERIAL_NUMBERS.END_ITEM_UNIT_NUMBER PJM_UNIT_NUMBERS
MTL_SERIAL_NUMBERS.STATUS_ID
MTL_SERIAL_NUMBERS.STATUS_ID MTL_MATERIAL_STATUSES_B
MTL_SERIAL_NUMBERS.CURRENT_LOCATOR_ID MTL_ITEM_LOCATIONS
MTL_SERIAL_NUMBERS.CURRENT_ORGANIZATION_ID
MTL_SERIAL_NUMBERS.CURRENT_ORGANIZATION_ID
MTL_SERIAL_NUMBERS.INVENTORY_ITEM_ID MTL_ITEM_REVISIONS_B
MTL_SERIAL_NUMBERS.CURRENT_ORGANIZATION_ID
MTL_SERIAL_NUMBERS.REVISION
MTL_SERIAL_NUMBERS.REVISION
MTL_SERIAL_NUMBERS.CURRENT_SUBINVENTORY_CODE MTL_SECONDARY_INVENTORIES
MTL_SERIAL_NUMBERS.CURRENT_ORGANIZATION_ID
MTL_SERIAL_NUMBERS.CURRENT_ORGANIZATION_ID
MTL_SERIAL_NUMBERS.INVENTORY_ITEM_ID MTL_SYSTEM_ITEMS_B
MTL_SERIAL_NUMBERS.CURRENT_ORGANIZATION_ID
MTL_SERIAL_NUMBERS.PARENT_SERIAL_NUMBER MTL_SERIAL_NUMBERS
MTL_SERIAL_NUMBERS.PARENT_ITEM_ID
MTL_SERIAL_NUMBERS.CURRENT_ORGANIZATION_ID
MTL_SERIAL_NUMBERS.CURRENT_ORGANIZATION_ID
MTL_SERIAL_NUMBERS.ORIGINAL_WIP_ENTITY_ID WIP_ENTITIES
MTL_SERIAL_NUMBERS.LAST_TXN_SOURCE_TYPE_ID MTL_TXN_SOURCE_TYPES
MTL_SERIAL_NUMBERS.LAST_TRANSACTION_ID
MTL_SERIAL_NUMBERS.LAST_TRANSACTION_ID MTL_MATERIAL_TRANSACTIONS
 Table 9: MTL_SERIAL_NUMBERS

•INVENTORY_ITEM_ID
•SERIAL_NUMBER
•SHIP_DATE
•CURRENT_STATUS
•LOT_NUMBER
•VENDOR_LOT_NUMBER
•STATUS_ID
•ORGANIZATION_TYPE
•VENDOR_SERIAL_NUMBER
 Table 10: MTL_ITEM_LOCATIONS
Description: Definitions for stock locators
MTL_ITEM_LOCATIONS is the definition table for stock locators. The
associated attributes describe which sub inventory this locator belongs
to, what the locator physical capacity is, etc.
The locator is a key flexfield. The Flexfield Code is MTLL.

Primary Key: MTL_ITEM_LOCATIONS_PK

1.INVENTORY_LOCATION_ID
2.ORGANIZATION_ID

Foreign Keys from this table:

Foreign Key Column Foreign Table

MTL_ITEM_LOCATIONS.INVENTORY_ACCOUNT_ID GL_CODE_COMBINATIONS

MTL_ITEM_LOCATIONS.ORGANIZATION_ID
MTL_ITEM_LOCATIONS.ORGANIZATION_ID MTL_PARAMETERS

MTL_ITEM_LOCATIONS.SUBINVENTORY_CODE
MTL_ITEM_LOCATIONS.SUBINVENTORY_CODE MTL_SECONDARY_INVENTORIES
MTL_ITEM_LOCATIONS.ORGANIZATION_ID

MTL_ITEM_LOCATIONS.STATUS_ID MTL_MATERIAL_STATUSES_B
 Table 10: MTL_ITEM_LOCATIONS

•INVENTORY_LOCATION_ID
•INVENTORY_LOCATION_TYPE
•PICKING_ORDER
•MAX_WEIGHT
•SUMMARY_FLAG
•ENABLED_FLAG
•LENGTH
•WIDTH
•HEIGHT
•VOLUME_UOM_CODE
 Table 11: MTL_ONHAND_QUANTITIES_DETAIL

Description: FIFO quantities by control level and receipt

MTL_ONHAND_QUANTITIES stores quantity on hand information by control
level and location.
MTL_ONHAND_QUANTITIES is maintained as a stack of receipt records, which
are consumed by issue transactions in FIFO order. The quantity on hand of an item
at any particular control level and location can be found by summing
TRANSACTION_QUANTITY for all records that match the criteria. Note that any
transactions which are committed to the table
MTL_MATERIAL_TRANSACTIONS_TEMP are considered to be played out as far
as quantity on hand is concerned in Inventory transaction forms. All our Inquiry
forms and ABC compile are only based on MTL_ONHAND_QUANTITIES.
MTL_ONHAND_QUANTITIES has two columns, CREATE_TRANSACTION_ID
and UPDATE_TRANSACTION_IDs to join to
MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID the transactions that
created the row and the transaction that last updated a row.
Table 11: MTL_ONHAND_QUANTITIES_DETAIL

Primary Key: MTL_FIFO_COST_U1

INVENTORY_ITEM_ID
ORGANIZATION_ID
OWNING_ORGANIZATION_ID
PLANNING_ORGANIZATION_ID
DATE_RECEIVED

Primary Key: MTL_ONHAND_QUANTITIES_PK

ONHAND_QUANTITIES_ID
Table 11: MTL_ONHAND_QUANTITIES_DETAIL

•INVENTORY_ITEM_ID
•ORGANIZATION_ID
•DATE_RECEIVED
•PRIMARY_TRANSACTION_QUANTITY
•LOCATOR_ID
•LOT_NUMBER
•ONHAND_QUANTITIES_ID
•ORGANIZATION_TYPE
•TRANSACTION_QUANTITY
•TRANSACTION_UOM_CODE
 Table 12: CST_ITEM_COSTS

CST_ITEM_COSTS stores item cost control information by cost type.

For standard costing organizations, the item cost control information

for the Frozen cost type is created when you enter a new item. For

average cost organizations, item cost control information is created

when you transact the item for the first time.

You can use the Item Costs window to enter cost control

information.
 Table 12: CST_ITEM_COSTS

•INVENTORY_ITEM_ID
•ORGANIZATION_ID
•LOT_SIZE
•PRIMARY_TRANSACTION_QUANTITY
•LOCATOR_ID
•LOT_NUMBER
•ONHAND_QUANTITIES_ID
•ORGANIZATION_TYPE
•TRANSACTION_QUANTITY
•TRANSACTION_UOM_CODE
•MATERIAL_COST
•ITEM_COST
•INVENTORY_ASSET_FLAG
 Table 13: CST_COST_TYPES
CST_COST_TYPES stores cost type definitions. The table is seeded with three cost
types: Frozen, Average, and Pending. The Frozen cost type is used in standard
costing organizations.

The average cost type is used in average costing organizations. All costs reference
a cost type.

PL_ELEMENT_FLAG, PL_RESOURCE_FLAG, PL_OPERATION_FLAG,

PL_ACTIVITY_FLAG values are used by the Cost Rollup to determine whether to
group rollup costs by cost element, sub element, operation sequence number, or
activity. BOM_SNAPSHOT_FLAG is used by the Cost Rollup to determine whether
to save the bill structure when rolling up costs for a cost type.
 Table 13: CST_COST_TYPES

•COST_TYPE_ID
•ORGANIZATION_ID
•COST_TYPE
•DESCRIPTION
•COSTING_METHOD_TYPE
 JOINING BETWEEN MTL_ITEM_CATEGORIES ,
MTL_ONHAND_QUANTITIES_DETAIL AND MTL_SYSTEM_ITEMS_B

QUERY:

SELECT A.INVENTORY_ITEM_ID,
C.DESCRIPTION,
B.DATE_RECEIVED,
B.TRANSACTION_QUANTITY
FROM MTL_ITEM_CATEGORIES A, MTL_ONHAND_QUANTITIES_DETAIL B,
MTL_SYSTEM_ITEMS_B C
WHERE A.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
AND A.INVENTORY_ITEM_ID = C.INVENTORY_ITEM_ID
GROUP BY
A.INVENTORY_ITEM_ID,B.DATE_RECEIVED,B.TRANSACTION_QUANTITY,
C.DESCRIPTION
JOINING BETWEEN MTL_ITEM_CATEGORIES AND MTL_CATEGORIES_B

QUERY:

SELECT
A.INVENTORY_ITEM_ID,
A.CREATION_DATE,
A.PROGRAM_ID,
B.CATEGORY_ID,
B.DESCRIPTION,
B.DISABLE_DATE
FROM
MTL_ITEM_CATEGORIES A,
MTL_CATEGORIES_B B
WHERE A.CATEGORY_ID = B.CATEGORY_ID
 JOINING BETWEEN MTL_MATERIAL_TRANSACTIONS
AND MTL_TRANSACTION_TYPES

QUERY:

SELECT
A.TRANSACTION_ID,
A.INVENTORY_ITEM_ID,
A.TRANSACTION_QUANTITY,
A.EMPLOYEE_CODE,
B.TRANSACTION_TYPE_NAME,
B.DESCRIPTION
FROM MTL_MATERIAL_TRANSACTIONS A,
MTL_TRANSACTION_TYPES B
WHERE A.TRANSACTION_TYPE_ID = B.TRANSACTION_TYPE_ID