Base tables for Purchase Order are as follows:
1. PO_HEADERS_ALL (SEGMENT1 column in this table represents the
Document number)
This table stores header information of a Purchasing Document.
You need one row for each document you create.
The following are the documents that use PO_HEADERS_ALL
RFQ’s, Quotations, Standard Purchase Order, Planned Purchase
Order, Blanket Purchase Order and Contracts
Important columns of this table:
PO_HEADER_ID, SEGMENT1, TYPE_LOOKUP_CODE, VENDOR_ID,
VENDOR_SITE_ID, CLOSED_CODE
PO_HEADER_ID is a unique system generated primary key and is
invisible to the users.
SEGMENT1 is the document number
You can uniquely identify a row in PO_HEADERS_ALL using ORG_ID,
SEGMENT1, and TYPE_LOOKUP_CODE, or using PO_HEADER_ID.
Sample queries:
SELECT PO_HEADER_ID, TYPE_LOOKUP_CODE FROM PO_HEADERS_ALL WHERE
SEGMENT1= ‘311’;
SQL Query
SELECT PO_HEADER_ID, AGENT_ID, TYPE_LOOKUP_CODE, CLOSED_CODE FROM
PO_HEADERS_ALL WHERE SEGMENT1= ‘6044’;
2. PO_LINES_ALL
This table stores the line information of a Purchasing Document.
Important columns of this table:
PO_LINE_ID: Unique identifier of the Document Line
PO_HEADER_ID:
Unique identifier of the Document Header (with
reference to PO_HEADERS_ALL, PO_LINE_ID)
LINE_TYPE_ID:
Unique identifier of Line_Type (with reference to PO_HEADERS_ALL, PO_LINE_ID
and PO_HEADER_ID)
LINE_NUMBER:
Line Number
ITEM_ID:
Unique Item Identifier
ORG_ID:
Unique Identifier of the Operating Unit
CLOSED_CODE:
Status of the Document
Sample Queries:
SELECT PO_LINE_ID FROM PO_LINES_ALL WHERE PO_HEADER_ID= 110334;
SQL Query
SELECT LINE_TYPE_ID FROM PO_LINES_ALL WHERE PO_HEADER_ID= 110334
AND PO_LINE_ID= 173263;
3. PO_LINE_LOCATIONS_ALL
This table contains the information related to 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 following are the seven documents that use shipment
schedules:
1. RFQs
2. Quotations
3. Standard purchase orders
4. Planned purchase orders
5. Planned purchase order releases
6. Blanket purchase orders
7. 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.
The following are the important columns of this table:
LINE_LOCATION_ID:
Unique
Identifier of Document shipment schedule
PO_HEADER_ID:
Unique Identifier of
Document header. (with reference to PO_HEADERS_ALL.po_header_id.)
PO_LINE_ID:
Unique Identifier of Document
line (with reference to PO_LINES_ALL.po_line_id)
QUANTITY:
Quantity ordered for
Purchase Orders, RFQs and Quotations
QUANTITY_RECEIVED:
Quantity
received until today
QUANTITY_ACCEPTED:
Quantity accepted
after inspection
QUANTITY_REJECTED:
Quantity rejected
after inspection
QUANTITY_BILLED:
Quantity invoiced
by Oracle Payables
QUANTITY_CANCELLED:
Quantity cancelled
TAXABLE_FLAG:
indicates whether
the shipment is taxable
ORG_ID:
Operating unit unique identifier
SHIP_TO_ORGANIZATION_ID: Unique identifier of Ship-to
organization
Sample Queries:
SELECT LINE_LOCATION_ID FROM PO_LINE_LOCATIONS_ALL WHERE
PO_HEADER_ID= 110334 AND PO_LINE_ID= 173263;
SQL Query
SELECT QUANTITY, QUANTITY_RECEIVED, QUANTITY_ACCEPTED,
QUANTITY_BILLED, QUANTITY_REJECTED, ORG_ID FROM PO_LINE_LOCATIONS_ALL WHERE
LINE_LOCATION_ID= 264421;
SQL Query
4. PO_DISTRIBUTIONS_ALL
This table contains the information related to accounting
distribution of 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:
1. Standard Purchase Orders
2. Planned Purchase Orders
3. Planned Purchase Order Releases
4. Blanket Purchase Order Releases
Each row includes the destination type, requestor ID, quantity
ordered and deliver-to location for the distribution.
Important columns of this table:
PO_DISTRIBUTION_ID:
This is the primary key for this table. It is a unique Document Distribution
identifier.
PO_HEADER_ID, PO_LINE_ID
LINE_LOCATION_ID:
Unique Identifier of the Document Shipment Schedule (with reference to
PO_LINE_LOCATIONS_ALL, LINE_LOCATION_ID)
CODE_COMBINATION_ID: Unique Identifier of
General Ledger Charge Account (with reference to
GL_CODE_COMBINATIONS.CODE_COMBINATION_ID)
REQ_DISTRIBUTION_ID:
Unique Identifier of a Requisition distribution (with Reference to PO_REQ_DISTRIBUTIONS_ALL.DISTRIBUTION_ID)
Sample Queries:
SELECT PO_DISTRIBUTION_ID FROM PO_DISTRIBUTIONS_ALL WHERE
PO_HEADER_ID= 11033;
SQL Query
SELECT CODE_COMBINATION_ID FROM PO_DISTRIBUTIONS_ALL WHERE
PO_DISTRIBUTION_ID= 13033;
SQL Query
5. VENDORS_ALL
This table stores the general information about the suppliers.
6. PO_VENDOR_SITES_ALL
This table stores information about the supplier sites.
Each row includes the site address, supplier reference, purchasing, payment,
bank, and general information.
7. PO_RELEASES_ALL
This table stores information related to planned and blanket
Purchase Order releases. Each row includes the buyer, date, release status, and
release number. Each release must have at least one purchase order shipment.
8. PO_VENDOR_CONTACTS
This table stores information about contacts related to Supplier
site. Each row includes contact name and site.
9. PO_ACTION_HISTORY
This table stores information about the approval and control
history of a Purchasing Document. This table stores one record for each
approval or control action an employee takes on a purchase order, purchase
agreement, release or requisition.
Base tables for
Purchase Requisition in Oracle Apps
The base
tables for reference while creating a purchase requisition in Oracle Apps is as
follows:
1. PO_REQUISITION_HEADERS_ALL (SEGMENT1 column in this table represents the requisition number)
This table stores Header information of a Purchase Requisition.
Important columns of this table:
REQUISITION_HEADER_ID: It is a unique system generated Requisition identifier
PREPARER_ID:
It is a unique identifier of the
employee who prepared the requisition
SEGMENT1:
It is the Requisition number
AUTHORIZATION_STATUS: Authorization status type
TYPE_LOOKUP_CODE:
Requisition type
ORG_ID:
Unique Operating unit unique identifier
You can get REQUISITION_HEADER_ID by executing the following
command:
Select REQUISITION_HEADER_ID FROM PO_REQUISITION_HEADERS_ALL WHERE
SEGMENT1= 14303; –14303 is our
Requisition Number
Result: REQUISITION_HEADER_ID = 181232
REQUISITION_HEADER_ID is the link betweenPO_REQUISITION_HEADERS_ALL and PO_REQUISITION_LINES_ALL
2. PO_REQUISITION_LINES_ALL
This table stores information about Requisition lines in a
Purchase Requisition. This table stores information related to the line number,
item number, item category, item description, item quantities, units, prices,
need-by date, deliver-to location, requestor, notes, and suggested supplier
information for the requisition line.
Important columns of this table:
REQUISITION_HEADER_ID: It is a unique system generated
Requisition identifier
REQUISITION_LINE_ID:
Link between PO_REQUISITION_LINES_ALL And PO_REQ_DISTRIBUTIONS_ALL
LINE_NUM:
Indicates the Line number
LINE_TYPE_ID:
Indicates the Line type
CATEGORY_ID:
Unique Item category identifier
ITEM_DESCRIPTION:
Description of the Item
QUANTITY NUMBER: Quantity ordered
QUANTITY NUMBER: Quantity ordered
SELECT REQUISITION_LINE_ID FROM PO_REQUISITION_LINES_ALL WHERE
REQUISITION_HEADER_ID=181232;
Result: REQUISITION_LINE_ID = 208442
3. PO_REQ_DISTRIBUTIONS_ALL
This table stores information about the accounting distributions
of a requisition line. Each requisition line must have at least one accounting
distribution. Each row includes the Accounting Flexfield ID and Requisition
line quantity.
Important columns of this table:
DISTRIBUTION_ID:
Unique Requisition distribution identifier
REQUISITION_LINE_ID: Unique Requisition line identifier
CODE_COMBINATION_ID: Unique General Ledger charge account identifier
DISTRIBUTION_NUM: Distribution number
REQUISITION_LINE_ID: Unique Requisition line identifier
CODE_COMBINATION_ID: Unique General Ledger charge account identifier
DISTRIBUTION_NUM: Distribution number
SELECT DISTRIBUTION_ID FROM PO_REQ_DISTRIBUTIONS_ALL WHERE
REQUISITION_LINE_ID= 208442;
Result: DISTRIBUTION_ID= 206959
You can view results by executing the following SQL commands:
SELECT CREATION_DATE, AUTHORIZATION_STATUS, SEGMENT1,
TYPE_LOOKUP_CODE from PO_REQUISITION_HEADERS_ALL where SEGMENT1= ’14303′;
Viewing results by executing
SQL command
SELECT CREATION_DATE, CREATED_BY, ORG_ID, REQ_LINE_QUANTITY FROM
PO_REQ_DISTRIBUTIONS_ALL WHERE REQUISITION_LINE_ID=208442;
viewing results by executing
SQL command
No comments:
Post a Comment