Key Tables for Oracle Purchase and P2P Cycle Flow

P2P cycle (Procure To Pay)

P2P Work Flow:

Ø  Item Creation

Ø  Requisition

Ø  Requisition Approval

Ø  Request for Quote (RFQ)

Ø  Quotat ion

Ø  Quote Analysis (Track/check record)

Ø  Issue Purchase Order (PO)

Ø  PO Approval

Ø  Goods Receipt Note (GRN)

Ø  ERS invoice (Pay on receipt) or PO matching invoice

Ø  Invoice validation

Ø  Invoice Approval

Ø  Create Accounting

Ø  Payment

Ø  Create Accounting

Ø  Transfer to GL (Payables transfer to GL program)

Ø  Journal Import

Ø  GL Balances

Key Tables for Oracle Purchase

  

Key Table Name	Key Table Description
Item Creation
MTL_SYSTEM_ITEMS_FVL	Item Master details
Purchase Requisition Creation
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.
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.
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 Flex field ID and requisition line quantity.
Purchase Order Creation
PO_HEADERS_ALL	PO_HEADERS_ALL contains header information for all purchasing documents. You need one row for each document you create. There are six types of documents that use PO_HEADERS_ALL: 1.RFQs 2.Quotations 3.Standard purchase orders 4.Planned purchase orders 5.Blanket purchase orders Contracts
PO_LINES_ALL	PO_LINES_ALL stores current information about each purchase order line. You need one row for each line you attach to a document.
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.
RCV_TRANSACTIONS	It Provides the receipt transaction details whether received or delivered
PO_REQUISITIONS_INTERFACE_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.
PO_HEADERS_INTERFACE	PO_HEADERS_INTERFACE is the interface table that holds header information for the PDOI program to create standard/blanket purchase orders and catalog quotations.
PO_LINES_INTERFACE	PO_LINES_INTERFACE is the interface table that holds lines information for the PDOI program to create standard/blanket purchase  orders and catalog quotations.
PO_DISTRIBUTIONS_INTERFACE	PO_DISTRIBUTIONS_INTERFACE is the interface table that holds distribution information for the PDOI program to create standard purchase order.
PO_VENDORS	PO_VENDORS is a view which contains selected columns of AP_SUPPLIERS
PO_VENDOR_SITES_ALL	PO_VENDORS is a view which contains selected columns of    AP_SUPPLIERS
PO_VENDOR_CONTACTS	It provide vendor contact details

R12: PO to Payment Query

Give 'PO Number' as an input to the following script.
It will provide all required PO details associated Receipt details, Invoice details, Payment details.
Purchase Order -> Receipts -> Invoices -> Payments


SELECT pha.segment1 po_number,
       pla.line_num,
       plla.shipment_num,
       msi.segment1 ordered_item,
       msi.description ordered_item_description,
       pla.unit_price po_unit_price,
       rt.transaction_type,
       rt.transaction_date,
       rt.subinventory,
       plla.quantity quantity_ordered,
       plla.quantity_received,
       pda.quantity_delivered,
       plla.quantity_Billed,
       rsh.receipt_num,
       aia.invoice_num,
       ail.line_number inv_line_number,
       aid.distribution_line_number inv_dist_number,
       ail.line_type_lookup_code Line_type,
       aid.line_type_lookup_code Dist_line_type,
       aid.amount,
       aid.quantity_invoiced,
       ac.check_number,
       aip.payment_num
  FROM apps.rcv_transactions rt,
       apps.po_headers_all pha,
       apps.po_line_locations_all plla,
       apps.po_distributions_all pda,
       apps.po_lines_all pla,
       apps.mtl_system_items msi,
       apps.rcv_shipment_headers rsh,
       apps.ap_invoices_all aia,
       apps.ap_invoice_lines_all ail,
       apps.ap_invoice_distributions_all aid,
       apps.ap_invoice_payments_all aip,
       apps.ap_checks_all ac
WHERE     rt.po_header_id = pha.po_header_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 rt.po_line_location_id = plla.line_location_id
       AND pla.item_id = msi.inventory_item_id
       AND rt.po_line_id = pla.po_line_id
       AND rt.organization_id = msi.organization_id
       AND rsh.shipment_header_id = rt.shipment_header_id
       AND aip.check_id = ac.check_id
       AND aia.invoice_id = aip.invoice_id
       AND aia.invoice_id = ail.invoice_id
       AND aia.invoice_id = aid.invoice_id
       AND pda.po_distribution_id(+) = aid.po_distribution_id
       AND rt.transaction_type = 'DELIVER'
       AND ail.line_type_lookup_code = 'ITEM'
       AND aid.line_type_lookup_code = 'ACCRUAL'
       AND pha.segment1 = '&PO_Number'