P2P and O2C PROCESS TABLES in Oracle Apps R12

PROCURE TO PAY :

ORDER TO CASH :

Key SQL Queries for Purchase Requisition, PO, and Receipt in Oracle Apps R12

SELECT prha.segment1 requisition_number,
       prha.type_lookup_code,
       prha.authorization_status,
       prla.item_description,
       prla.quantity,
       pha.type_lookup_code,
       pha.segment1 po_number,
       pha.document_creation_method,
       pla.item_id,pla.list_price_per_unit,rsh.receipt_num
FROM  po_requisition_headers_all prha,--REQUISITION HEADERS TABLE
      po_requisition_lines_all  prla,--REQUISITIONS LINES TABLE
      po_req_distributions_all prda,--REQUISITIONS DISTRIBUTION TABLE
      po_headers_all pha, --PURCHASE ORDER HEADER TABLE
      po_lines_all pla,--PURCHASE ORDER LINE TABLE
      po_distributions_all pda,--PURCHASE ORDER DISTRIBUTIONS TABLE
      rcv_shipment_headers rsh,--RECEIPT HEADER TABLE
      rcv_shipment_lines rsl--RECEIPT LINE TABLE
WHERE 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 = pda.po_line_id
and prda.distribution_id = pda.req_distribution_id
and rsh.shipment_header_id = rsl.shipment_header_id
and pha.po_header_id = rsl.po_header_id
--and   prha.segment1 = '336276';
and rsh.shipment_header_id = '23163104';

Procure to Pay Life Cycle in Oracle Apps R12

P2P and O2C Cycle in Oracle Apps: Complete Documentation

https://drive.google.com/open?id=1zG8ULRtgZTNHovTFC1OySEeWPR7HRGup

Oracle P2P, O2C, Drop Shipment & B2B Processes Explained | Complete Guide

P2P: (Procure to Pay)

• Create Requisition
• Create Purchase Order
• Create Inventory Receipt
• Enter AP Invoice
• Make Payment
• Transfer to GL

1. Create requisition:

  Headers - po_requisition_headers_all

  Lines - po_requisition_lines_all

  Distribution -  po_req_distribution_all

Po_requisition_headers_all:

  Important columns:

•   Requisition_header_id: requisition header unique identifier.
•   Segment 1: requisition number.
•   Authorization_status: authorization type. (Complete)
•   Type_lookup_code: requisition type. (Purchase and internal) 

Description:

PO_REQUISITION_HEADERS_ALL stores information about requisition headers.

 

Po_requisition_lines_all:

Important columns:

• Requisition_header_id: requisition header unique identifier.
• Line_type_id: line type (goods).
• Quantity_number: quantity ordered.
• Categeory_id: item category unique identifier.

 Description:

• PO_REQUISITION_lines store information about requisition lines. Each row contains the line number, item number, item category, item description, a need-by date, deliver-to location, item quantities, units, prices, requestor, notes, and suggested supplier information for the requisition line.

 

 po_req_distribution_all:

 Important columns:

•  Distribution_id: requisition distribution unique identifier.
•  Requisition_line_id: requisition line unique identifier.
•  Code_combination_id: unique identifier for the general ledger.
•  Distribution_num: distribution number.

 Description:

•  Po_req_distribution_all stores information about the accounting distribution associated with each requisition line. Each requisition line must have at least one accounting distribution.
• Po_req_distribution_all is one of three tables storing your requisition number.

Accounting distribution:

• Accounting distributions are used to define how an amount will be accounted for, such as how the expense, tax, or charges will be accounted for on a vendor invoice. Every amount that must be accounted for when the vendor invoice is journalized will have one or more accounting distributions.

2. Create purchase order:

    Header: po_headers_all

    Line: po_lines_all

    Distribution: po_distribution_all

 po_headers_all:

Important columns:

• Po_header_id
• Segment1
• Vendor_id
• Vendor_site_id

Description:

Po_headers_all contains header information for all purchasing documents. Six types of documents use po_headers_all.

• RFQs
• Quotations
• Standard purchase orders
• Planned purchase orders
• Blanket purchase orders
• Contracts

 po_lines_all:         

  Important columns:

               Po_line_id: document the unique identifier.

               Po_header_id: document header unique identifier. (Reference po_headers_all ,po_header_id)

               Item_id: item unique identifier.

               Line_num: line number.

 Description:

     Po_lines_all stores current information about each order purchases order line. There are five document types that use the line.

•  RFQs
• Quotations
• Standard purchase orders
• Blanket purchase orders
• Planned purchase orders

po_distribution_all:

 Important columns:

         Po_distribution_id: document distribution unique identifier. The primary key for this table.

         Po_header_id: document header unique identifier. Reference po_headers_all.po_header_id.

         Po_line_id: document the unique identifier. Reference po_lines_all.po_lines_id.

        Line_location_id:

Description:

            Po_distribution_all contains accounting distribution information for a purchase order shipment line. 

3. Create the receipt:

       Headers: rcv_shipment_headers_all

       Lines: rcv_shipment_lines_all

       Transaction: rcv_transactions

Rcv_shipment_headers_all:

   Important columns:          

          Shipment_header_id: receipt header unique identifier.

          Receipt_source_code: source type of the shipment – vendor, internal order, or customer.

          Vendor_id: source supplier unique identifier.

          Shipment_num: shipment number.

          Receipt_num: receipt number.

 

Description:

            Rcv_shipment_headers_all stores common information about the source of your receipts or expected receipts.

           There are two receipt source types, supplier and internal order.

 

 Rcv_shipment_lines_all:

Important columns:

        Shipment_line_id: shipment line unique identifier.

       Item_id: item identifier.

        Vendor_item_num: suppliers item number.

       Shipment_line_status_code: receipt status of the shipment line

 Description:

       Rcv_shipment_lines_all stores information about items that have been shipped.

 

 Rcv_transaction:

  Important columns:

       Transaction_id: receiving transaction unique identifier.

       Request_id:

      Transaction_type: receiving transaction type.

     Transaction_date:

     Quantity:

Description:

        Rcv_transaction stores historical information about receiving transactions that you have performed. 

4. Invoices:

 ap_invoices_all:

Important columns:

           Invoice_id: generated using a database sequence.

           Vendor_id:                

           Invoice_num:

          Set_of_books_id:

          Payment_currency_code:

Description:

        This table corresponds to the invoice header block of the Invoice workbench. AP_INVOICES_ALL holds information this table corresponds to the Invoices header block of the Invoice workbench. AP_INVOICES_ALL holds information on Oracle Projects, Supplier Portal, Refunds from Oracle Receivables, etc. This table holds all type of invoices, which includes Standard, Prepayments, Credit Memo, Debit Memo, Mixed invoice, Withholding invoices, Interest Invoice, Retainage invoices, Payment Requests, etc.,

 ap_invoice_distributions_all:

 Important columns:

          Invoice_id: Unique invoice distribution identifier.

         SET_OF_BOOKS_ID: Ledger identifier of the invoice distribution.

         AMOUNT: Amount on invoice distribution.

         INVOICE_DISTRIBUTION_ID:

   Description:

          AP_INVOICE_DISTRIBUTIONS_ALL holds the distribution information that is manually entered or system-generated.

 

 ap_checks_all:

Important columns:

         Amount: payment amount

         Bank_account_id: longer used

        Bank_account_name:

        Check_id_number:

        Check_number: payment number

       Vendor_name: supplier name

Vendor_site_code: supplier site code

 

Descriptions:

    Ap_checks_all stores information about payments issued to suppliers or refunds received from suppliers.

 ap_invoice_payments_all:

Important columns:

       Invoice_payment_id:

      Invoice_id:

      Check_id:

      Payment_num:

Descriptions:

       Ap_invoice_payments_all contains the record of invoice payments that you made to suppliers.

 ap_payment_schedules_all:

Important columns:

       Invoice_id:

       Payment_num:

      Amount_remaining:

Descriptions:

    Ap_payment_schedules_all contains information about the schedule for an invoice.

5. GL Transfer:

 gl_periods:

Important columns:

       Period_set_name: name of the accounting calendar.

       Period_name: system-generated accounting period name.

Descriptions:

      Gl_periods contains information about the accounting periods that are defined using the accounting calendar form.

 gl_period_statuses:

Important columns:

Application_id: identifier associated with the application

Ledger_id: the unique identifier of the ledger

Period_name:

Descriptions:

   Gl_period_statuses contains the statuses of your accounting periods.

 

gl_set_of_books:

Important_columns:

 

Oracle application modules:

AP - oracle payables.

AR- oracle receivables

AS- oracle sales

CS - oracle service

CSS - support.

GL - oracle general ledger

GR - oracle processes regulatory management

HRI - human resource intelligence

ONT - oracle order management

PO – oracle purchasing

QP- oracle pricing

QRM- oracle risk management

WIP – oracle work in process

WMS- oracle warehouse management systems

WSH - oracle shipping

DBA – oracle database administrator

Difference between Oracle DBA and Oracle Developer:

• Developers are working on SQL & plsql.
• DBA's job is to manage server-related activities
• The developer has written to coding.
• DBA has to manage servers, like an administration job.

Order to Cash (O2C):

• Enter the Sales Order 
• Book the Sales Order
• Launch Pick Release
• Ship Confirm
• Create Invoice
• Create the Receipts either manually or using Auto Lockbox ( In this article we will concentrate on Manual creation)
• Transfer to General Ledger 
• Journal Import
• Posting

1. Enter sales order:

      Headers – oe_order_headers_all

      Lines – oe_order_lines_all

oe_order_headers_all:

     Important columns:

        Check_number:

        Header_id:

       Order_number:

        Order_date:

        Payment_amount:

        Order_type_id:

Descriptions:

     Order headers are stored in oe_orders_headers_all.

oe_order_lines_all:

   Important columns:

       Booked_flag: indicates whether the line has been closed (n)or not(y)

      Item_type_code:

      Line_id:

     Ordered_item:

    Inventory_item_id:

     Shipment_number:

    Price_quantity:

 Descriptions:

       Oe_order_lines_all stores information for all order lines in Oracle order management.

        

Shipping tables:

Wsh_delievery_details

Wsh_new_deliveries

Wsh_frieght_costs

 

Wsh_delievery_details: (released _status ‘S’ submitted for release)

Columns:

            Delivery_detail_id

             Source_header_id

            Source_line_id

            Inventory_item_id

           Ship_to_locations_id

            Ship_to_contact_id

           Delivered_quantity

           Unit_weight

           rcv_shipment_line_id: this column stores the receiving shipment receipt line unique identifier. This is used to join the rcv_shipment_ lines table.

 

wsh_new_deliveries:

Columns:

          Delivery_id

          Delivery_name: this is the name of the shipment.

         Planned_flag: planned flag (N=unplanned, Y=planned)

          Status_code: delivery shipping status. e.g OP for open delivery.

         Cod_amount: This amount is for cash on delivery.

wsh_frieght_costs:

        WSH_FREIGHT_COSTS records freight costs.

     Columns:

          Freight_costs_id

          Unit_amount: the unit amount of this shipping cost.

          Quantity:

          Delivery_id:

Dropship cycle:

1. Create Sale Order

2. Book Sales Order

3. Check Status

4. Progress Sales Order

5. Check Status

6. Release Purchase Order

7. Import Requisition / Purchase Order

8. Link between sales order and purchase order

9. Receive the material against the purchase order

10. Check the Order status.

Oracle drop-ship tables:

 Po_lines_all: stores purchase document lines for purchase orders, purchase agreements, quotations and RFQ.

 Po_headers_all: Po_headers_all contains header information for all purchasing documents.

 Po_distributions_all: stores purchase order information.

  po_requisition_headers_all:

  po_requisition_lines_all:

  po_req_distribution_all:

  oe_order_lines_all:

   rcv_shipment_lines:

  oe_drop_ship_sources: This table stores the relationship between Order Lines in the oe_order_lines_all table and associated oracle purchasing requisitions in PO_REQUISITIONS_ALL and Oracle purchasing purchase orders in PO_LINES_ALL.

 

Back to Backorder process:

1.     Enter Sales Order

2.     Book Sales Order

3.     Progress Sales Order to create Supply Order

4.     Requisition Import

5.     Create Purchase / Inventory Receipt

6.     Pick Release Sales Order

7.     Ship Confirm

 

 GL INTERFACE:

  Interface table:

• Gl_interface

  Base table:

• Gl_je_batches
• Gl_je_headers
• Gl_je_lines

Table columns:

     Accounting date, date created, category name, source name, entered debit/credit, segments.

Concurrent program:

    Journal Import

    Journal posting

 

Gl_interface:

    gl_interface table is where journal import receives accounting data that you import from other systems.

 

Gl_je_batches:

   GL_JE_BATCHES contains journal entry batches.  Each row includes the batch name, description, status, running total debits and credits, and other information.

 Columns:

     Je_batch_id

     Approve_employee_id

    Name

    Status

 

Gl_je_headers:

   GL_JE_HEADERS contains journal entries. Batch ID, journal entry name and description, and other information about the journal entry.

Columns:

  Je_header_id

  Ledger_id

  Name

  Status

 

Gl_je_lines:

  GL_JE_LINES contains the journal entry lines that you enter in the Enter Journals form.

 Columns:

    Je_header_id

    Je_line_num

    Ledger_id

    Status

Oracle Procure to Pay (P2P) Cycle Explained: Step-by-Step Guide

P2P cycle:(procure to pay)

    P2P cycle those 3 applications are.

• Purchasing(PO).
• Account Payable(AP).
• General Ledger(GL).

P2P cycle steps following are:

• create a requisition.
• create a purchase order.
• receipts.
• invoice.
• payments.
• transfer to gl.

TABLES IN P2P CYCLE :

  1. REQUISITION :

           select * from PO_REQUISITION_HEADERS_all;  (HEADER)
           select * from PO_REQUISITION_LINES_all ;  (LINE)
           select * from po_req_distributions_all;        (DISTRIBUTION)

  2. PURCHASE ORDER (PO) :
       
            select * from po_headers_all;   (HEADER)
            select * from po_lines_all;        (LINE)
            select * from po_distribution_all;   (DISTRIBUTION)

  3. RECEIPT :

           select * from rcv_shipment_headers_all; (HEADER)
           select * from rcv_shipment_lines_all;   (LINES)

P2P JOINS :
        REQUISITIONS :

          select *
          from PO_REQUISITION_HEADERS_all rh,
          PO_REQUISITION_LINES_all rl
          where rh.requisition_header_id = rl.requisition_header_id;

       select *
       from PO_REQUISITION_HEADERS_all rh,
       PO_REQUISITION_LINES_all rl,
       po_req_distributions_all rd
       where rh.requisition_header_id = rl.requisition_header_id
       and rd.requisition_line_id = rl.requisition_line_id;

          PURCHASE ORDER :

         select * from  po_headers_all poh ,po_lines_all pol
         where poh.po_header_id = pol.po_header_id;

1. REQUISITION:

• requisitions can also be called requirements.
• it is a document prepared by the customer.

   we have two types of Requisitions
         1)Internal
         2)Purchase

• The internal requisition will be created if materials are received from another Inventory inside of the organization.
• Purchase requisition will be created while purchasing the materials from the Suppliers.

  We will enter the Requisition at three-level
        1)Header
        2)Line
        3)Distributions.

CREATING A REQUISITION FOLLOWING  BY STEPS:

  NAVIGATION: MM PURCHASING A SUPERUSER.
                         |
                    REQUISITION
                         |
                    REQUISITION
HEADER:
      Enter the requisition type as"purchase requisition".

LINE:
     item number:999020
     quantity:5
     price:10

SUPPLIER DETAILS:

     TO ORG: BOISE PLANT
     SUPPLIER: (anything you want)
     SITE    :  (anything you want)

• click on the distribution button to see distribution details for each line.
• click save, and now the requisition number is automatically created.
• note that number: 312176
• click approve and ok.
• to check whether the created requisition was approved or not.
• go to MM PURCHASING SUPER USER - REQUISITION - REQUISITIONS SUMMARY.
• find with created requisition number.

TABLE :
 
           select * from PO_REQUISITION_HEADERS_all;(HEADER)
           select * from PO_REQUISITION_LINES_all ;(LINE)
           select * from po_req_distributions_all;(DISTRIBUTION)

Note :

     segment1- requisition_number
     line    - requisition_header_id

LINK ON THE TABLE TYPE :

    rh.requisition_header_id = rl.requisition_header_id (Requisition header and requisition line)
    rd.requisition_line_id = rl.requisition_line_id  (requisition distributions and requisition line)

2.PURCHASE ORDER(PO):

• the purchase order is a document prepared by the customer and given to the supplier maintaining the goods which he required.
• which contains information on terms and conditions, item details, quantity, price, distribution, shipment details so on.

Types of purchase orders:

1. standard
2. planned
3. blanked
4. contract

CREATING A PURCHASE ORDER (PO) :

NAVIGATION: MM PURCHASING SUPER USER
                      |
                 AUTO-CREATE
                   
               
 -> Enter the requisition number.
 -> remove the buyer.
 -> select the requisition.
 -> click on the automatic button.
 -> next enter the old supplier name.
 -> auto creates to purchase orders window open.
 -> take the purchase order number.
 -> enter the ship to
 -> click on the approve button.
 -> status changed in the approved.
 -> to check whether the created purchase order was approved or not.
 -> go to purchase order
 -> click on the purchase order summary.

TABLE  :
 
           select * from po_headers_all;
           select * from po_lines_all;

LINK ON THE TABLE TYPE :

           poh.po_header_id = pol.po_header_id (PO header po line)