Scribd
Upload
37 views

Base Tables in Oracle Apps PO Module

This document describes various tables used for managing purchase orders (PO) in an ERP system. It provides descriptions of tables for PO requisitions, PO headers and lines, PO receipts and shipments, and other reference tables. It also describes the relationships between these tables through primary and foreign keys. Examples are provided to illustrate how to write SQL queries to retrieve data from these tables.

Uploaded by

pavan kumar Piratla
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
37 views

Base Tables in Oracle Apps PO Module

This document describes various tables used for managing purchase orders (PO) in an ERP system. It provides descriptions of tables for PO requisitions, PO headers and lines, PO receipts and shipments, and other reference tables. It also describes the relationships between these tables through primary and foreign keys. Examples are provided to illustrate how to write SQL queries to retrieve data from these tables.

Uploaded by

pavan kumar Piratla
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 4

PO REQUISITION TABLES:

**********************
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL

PO TABLES:
**********
PO_HEADERS_ALL
PO_LINES_ALL
PO_LINE_LOCATIONS_ALL
PO_DISTRIBUTIONS_ALL

RECEIPT TABLES:
***************
RCV_SHIPMENT_HEADERS
RCV_SHIPMENT_LINES
RCV_TRANSACTIONS

OTHER TABLES:
*************
PO_RELEASES_ALL
PO_LINE_TYPES
PO_ACTION_HISTORY

PO_HEADERS_ALL:
****************
-->This table stores PO header level data, each record in this table represents a
purchase order, which is an order for goods or services from a single supplier.
Each purchase order may have multiple lines (PO_LINES)

PO_LINES_ALL:
*************
--> This table stores PO line level data, each record in this table represents a
purchase order line, which identifies the items and unit price for the goods
ordered on a purchase order. Each purchase order line may have multiple shipments
(PO_LINE_LOCATIONS).

PO_LINE_LOCATIONS_ALL:
**********************
-->This table stores PO line location level data, each record in this table
represents a purchase order shipment, which identifies the quantity of an item
shipped to a buyer location by the supplier. Each purchase order shipment may have
multiple accounting distributions (PO_DISTRIBUTIONS).

PO_DISTRIBUTIONS_ALL:
*********************
-->This table stores PO distributions level data, each record in this table/view
represents a purchase order distribution, which identifies the account charged for
the items on a purchase order shipment.

PO_VENDORS_ALL:
***************
-->This table stores PO general information about the suppliers.

PO_VENDOR_SITES_ALL:
********************
-->This table stores PO related supplier site level information, each row includes
the site address, supplier reference, purchasing, payment, bank, and general
information.

PO_RELEASES_ALL:
*****************
-->This table stores planned and blanket Purchase Order releases, each row includes
the date, buyer, release status, and release number, each release must have at
least one purchase order shipment.

PO_VENDOR_CONTACTS:
*******************
-->This table stores supplier contact details which are related to purchase order,
each record includes contact name and supplier site.

EX:
****

REQUISTION TABLES:
******************
SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1='1';
SELECT * FROM PO_REQUISITION_LINES_ALL WHERE REQUISITION_HEADER_ID='5204';
SELECT * FROM PO_REQ_DISTRIBUTIONS_ALL WHERE REQUISITION_LINE_ID='1'

-->LINK BETWEEN PO_REQUISITION_HEADERS_ALL AND PO_REQUISITION_LINES_ALL

PO_REQUISITION_HEADERS_ALL.REQUISITION_HEADER_ID=PO_REQUISITION_LINES_ALL.REQUISITI
ON_HEADER_ID

-->LINK BETWEEN PO_REQUISITION_LINES_ALL AND PO_REQ_DISTRIBUTIONS_ALL

PO_REQUISITION_LINES_ALL.REQUISITION_LINE_ID=PO_REQ_DISTRIBUTIONS_ALL.REQUISITION_L
INE_ID

PO TABLES:
**********
SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1='500' AND TYPE_LOOKUP_CODE='STANDARD';
SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID='1';
SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_LINE_ID=1;
SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE LINE_LOCATION_ID=1;

-->LINK BETWEEN PO_HEADERS_ALL AND PO_LINES_ALL

PO_HEADERS_ALL.PO_HEADER_ID=PO_LINES_ALL.PO_HEADER_ID

-->LINK BETWEEN PO_LINES_ALL AND PO_LINE_LOCATIONS_ALL

PO_LINES_ALL.PO_LINE_ID=PO_LINE_LOCATIONS_ALL.PO_LINE_ID

-->LINK BETWEEN PO_LINE_LOCATIONS_ALL AND PO_DISTRIBUTIONS_ALL

PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID=PO_DISTRIBUTIONS_ALL.LINE_LOCATION_ID

-->LINK BETWEEN PO_REQ_DISTRIBUTIONS_ALL AND PO_DISTRIBUTIONS_ALL


PO_REQ_DISTRIBUTIONS_ALL.DISTRIBUTION_ID=PO_DISTRIBUTIONS_ALL.REQ_DISTRIBUTION_ID

RECEIPT TABLES:
***************
SELECT * FROM RCV_SHIPMENT_HEADERS WHERE RECEIPT_NUM='2000';
SELECT * FROM RCV_SHIPMENT_LINES WHERE SHIPMENT_HEADER_ID=1;
SELECT * FROM RCV_TRANSACTIONS WHERE SHIPMENT_LINE_ID='225845';

-->LINK BETWEEN RCV_SHIPMENT_HEADERS AND RCV_SHIPMENT_LINES

RCV_SHIPMENT_HEADERS.SHIPMENT_HEADER_ID=RCV_SHIPMENT_LINES.SHIPMENT_HEADER_ID

-->LINK BETWEEN RCV_SHIPMENT_LINES AND RCV_TRANSACTIONS

RCV_SHIPMENT_LINES.SHIPMENT_LINE_ID=RCV_TRANSACTIONS.SHIPMENT_LINE_ID

-->LINK BETWEEN PO_HEADERS_ALL AND RCV_SHIPMENT_LINES

PO_HEADERS_ALL.PO_HEADER_ID=RCV_SHIPMENT_LINES.PO_HEADER_ID

-->LINK BETWEEN PO_LINES_ALL AND RCV_SHIPMENT_LINES

PO_LINES_ALL.PO_LINE_ID=RCV_SHIPMENT_LINES.PO_LINE_ID

-->LINK BETWEEN PO_LINE_LOCATIONS_ALL AND RCV_SHIPMENT_LINES

PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID=RCV_SHIPMENT_LINES.PO_LINE_LOCATION_ID
PO_LINE_LOCATIONS_ALL.SHIP_TO_ORGANIZATION_ID=RSL.TO_ORGANIZATION_ID

-->LINK BETWEEN PO_DISTRIBUTIONS_ALL AND RCV_SHIPMENT_LINES

PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID=RSL.PO_DISTRIBUTION_ID

EX:
***
-->WRITE A SQL QUERY TO GET THE REQUISITION NUMBER FOR THE PO NUMBER 3764.

SELECT PRHA.SEGMENT1 REQUISITON_NUMBER


FROM PO_REQUISITION_HEADERS_ALL PRHA,
PO_REQUISITION_LINES_ALL PRLA,
PO_REQ_DISTRIBUTIONS_ALL PRDA,
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS_ALL PDA
WHERE 1=1
AND PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
AND PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID
AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
AND PDA.REQ_DISTRIBUTION_ID=PRDA.DISTRIBUTION_ID
AND PHA.SEGMENT1='3764';

--WRITE A SQL QUERY TO DISPLAY THE RECEIPT NUMBER FOR THE PO NUMBER 3764

SELECT *
FROM PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_LINE_LOCATIONS_ALL PLLA,
PO_DISTRIBUTIONS_ALL PDA,
RCV_SHIPMENT_HEADERS RSH,
RCV_SHIPMENT_LINES RSL
WHERE 1=1
AND PHA.PO_HEADER_ID=PLA.PO_HEADER_ID
AND PLA.PO_LINE_ID=PLLA.PO_LINE_ID
AND PLLA.LINE_LOCATION_ID=PDA.LINE_LOCATION_ID
AND RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
AND PHA.PO_HEADER_ID=RSL.PO_HEADER_ID
AND PLA.PO_LINE_ID=RSL.PO_LINE_ID
AND PLLA.LINE_LOCATION_ID=RSL.PO_LINE_LOCATION_ID
AND PLLA.SHIP_TO_ORGANIZATION_ID=RSL.TO_ORGANIZATION_ID
AND PDA.PO_DISTRIBUTION_ID=RSL.PO_DISTRIBUTION_ID
AND PHA.SEGMENT1='3764';

You might also like