Oracle - P2P , O2C, Drop shipment, B2B

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. There are six types of documents that 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 Invoices 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 invoice, 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:

1.        Wsh_delievery_details

2.        Wsh_new_deliveries

3.        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