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)

PL/SQL Function in Oracle – Syntax, Parameters, and Real-Time Examples

1. NVL FUNCTION:

• The NVL function is used to replace NULL values with another value.
• The NVL function replaces an NA value or an empty string with a string.

syntax:
       NVL( value_in, replace_with )

• The NVL2 is an extended version of NVL. It takes three arguments.

syntax:
       NVL2(expression,value1,value2)


2. UID FUNCTIONS:

• The UID function returns the ID number for a user

syntax:
        UID

3. USERENV Function:

• The USERENV function can be used to retrieve information about the current Oracle session.

syntax:
       usernev(parameter)

      select userenv('language')from dual;
      select userenv('sid')from dual;         
      select userenv('lang')from dual;

4. NULLIF Function:

• NULLIF function compares expr1 and expr2.
• If expr1 and expr2 are equal, the NULLIF function returns NULL. Otherwise, it returns expr1.

Syntax:
      NULLIF( expr1, expr2 )

       select nullif('suriya','suriya')from dual;
       select nullif('suriya','parithy')from dual; 

PL/SQL %TYPE and %ROWTYPE – Usage, Differences, and Examples Explained

• %TYPE is used to declare a variable that is of the same type as a specified table’s column.

     
        example: Emp_number emp.empno%type;

• %ROWTYPE is used to declare a record (the variable that represents the entire row of a table).

         example: Emp_record emp%rowtype;

PL/SQL BULK COLLECT INTO and CURSOR – Performance Tuning with Examples

BULK COLLECT INTO:

       Bulk collect can be used in the 'SELECT' statement to populate the records in bulk or in fetching the cursor in bulk. BULK COLLECT fetches the record in BULK, the INTO clause should always contain a collection type variable.

• The FORALL statement
• The BULK COLLECT clause

The FORALL statement:

• The FORALL statement sends INSERT, UPDATE, or DELETE statements in batches from PL/SQL.

       
           FOR i IN 1..10 LOOP
             INSERT INTO table_name
             VALUES (...);
           END LOOP;

• The bulk collect clause can appear in

              select into statement
              fetch into the statement
             -> returning into
                   delete statement
                   insert
                   update
                   execute immediately.

CURSOR:

• A cursor is a temporary work area created in the system memory when a SQL statement is executed.
• A cursor contains information on a select statement and the rows of data accessed by it.
• This temporary work area is used to store the data retrieved from the database and manipulate this data.
• A cursor is a SELECT statement that is defined within the declaration section of your PLSQL code.

       Three different syntaxes to declare a cursor.

            * Cursor without parameters.
            * Cursor with parameters.
            * Cursor with a return clause.

Cursor without parameters:

     Syntax:
 
            CURSOR cursor_name
            IS
            SELECT statement;

Cursor with parameters:

    Syntax:
         
            CURSOR(parameter_list)
            IS
            SELECT statement;

Cursor with return clause:

    Syntax:
     
           CURSOR cursor_name
           RETURN field%ROWTYPE
           IS
           SELECT statement;



Retrieve data from the cursor

   Syntax:

          FETCH cursor_name INTO PL/SQL variable;
          or
          FETCH cursor_name INTO PL/SQL record;



Difference between cursor and ref cursor:

• A ref cursor is a pointer to a result set.
• This is normally used to open a query on the database server, then leave it up to the client to fetch the result it needs.
• A ref cursor is also a cursor, though normally the term cursor is used when discussing static SQL.

CURSOR ATTRIBUTES:

         Name             Description
       %FOUND            Returns TRUE if the record was fetched successfully, FALSE otherwise.
       %NOTFOUND    Returns TRUE if the record was not fetched successfully, FALSE otherwise.
       %ROWCOUNT    Returns the number of records fetched from the cursor at that point in time.
       %IS OPEN            Returns TRUE if the cursor is open, FALSE otherwise.

REF CURSOR:

• REF CURSOR is one of the most powerful, flexible, and scalable ways to return query results from an Oracle Database to a client application.
• A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database.
• Cursor variables give you easy access to centralized data retrieval.
• REF CURSOR is a PL/SQL data type. create and return a REF CURSOR inside a PL/SQL code block.
• REF CURSOR is also referred to as Cursor Variables.
• Ref Cursor itself is a data type and easy to declare.
• Cursor variables are bind variables.
• PL/SQL cursor is static and the ref cursor is dynamic.

Dis advantage:

• Ref Cursors are not as efficient as Static cursors.
• Need additional code to print Ref Cursor values.

syntax:
       type<type_name>is ref cursor
       [return return_type];

strong and weak cursor:

• cursor variable has a return type then it is called a strong cursor.
• cursor variable has no return type then it is called a weak cursor.

Example:
         1) TYPE ref_item is ref cursor return item_master%rowtype.
         2) TYPE client is ref cursor;

PL/SQL Exception Handling – Predefined, User-Defined & WHEN OTHERS Explained

Exception Handling:

• The exception is an error condition during program execution.
• Exceptions are used to handle errors that occur in your PL/SQL code.

 Types of exception:

1. System defined.
2. User-defined.

Different parts of the exception

• Declare the exception
• Raise an exception
• Handle the exception

SYNTAX:
       EXCEPTION
            WHEN OTHERS THEN
            <statements>

• no_data_found: when a select into statement returns no rows.
• program_error: when pl/sql has an internal problem.
• storage_error: memory was corrupted.
• too_many_rows: when a select into statement returns more than a row.
• value_error:  when arithmetic, conversion, truncation, or size constraint error occurs.
• Zero divide: when an attempt is made to divide a number by zero.
• invalid number.
• invalid cursor.

   This Exception Type of Error:

• ORA Core RDBMS errors
• PLS PL/SQL errors
• FRM Oracle Forms error
• REP Oracle Reports errors.

RAISE APPLICATION ERROR:

• Which is used to display the user-defined error messages along with the error number whose range is between -20000 and -20999.
• RAISE_APPLICATION_ERROR raises an exception but does not handle it.
• RAISE_APPLICATION_ERROR is used for the following reasons,

            a) to create a unique id for a user-defined exception.
            b) to make the user-defined exception look like an Oracle error.

Syntax:
       RAISE_APPLICATION_ERROR (error_number, error_message);

         • The Error number must be between -20000 and -20999
         • The Error_message is the message you want to display when the error occurs.

USER-DEFINED EXCEPTIONS:
     
        1. Declare exception
        2. RAISE exception
        3. Implement exception conditions.

Declare exception:

• must declare user user-defined exception name in the DECLARE block.

syntax:   
          user_define_exception_name EXCEPTION;

Raise exception:

• RAISE statement to raise defined exception name and control transfer to an EXCEPTION block.

syntax:
        RAISE user_define_exception_name;

Implement exception condition:

• In PL/SQL EXCEPTION block add the WHEN condition to implement a user-defined action.

syntax:
      WHEN user_define_exception_name THEN
User-defined statement (action) will be taken;


User-defined exception syntax:

DECLARE
user_define_exception_name EXCEPTION;
BEGIN
statement(s);
IF condition THEN
RAISE user_define_exception_name;
END IF;
EXCEPTION
WHEN user_define_exception_name THEN
User defined statement (action) will be taken;
END;

     
Purpose of SQL code and sqlerrm:

• SQL code returns the numeric value for the error code.
• sqlerrm: returns the message associated with the error number.

           sqlcode                     description
            0                               no exception encountered.
            1                               user_defined exception.
            +100                         no_data_found exception.
           negative number    another oracle server error number.

            

DECLARE
           name employees.last_name%TYPE;
           v_code NUMBER;
           v_errm VARCHAR2(64);
BEGIN
       SELECT last_name INTO name FROM employees WHERE employee_id = 1000;
       EXCEPTION
           WHEN OTHERS THEN
             v_code := SQLCODE;
             v_errm := SUBSTR(SQLERRM, 1 , 64);
         Dbms_Output.Put_Line('The error code is ' || V_Code || '- ' || V_Errm);
END;