Procure To Pay (P2P) Life Cycle

Procure to Pay is a complete life cycle of procurement i.e. acquisition of Goods, Services or Works form external world. It integrates the Purchasing with the Accounting module.It enables the buyer to buy the best fit Item with respect the price, quality, quantity, time and location.

The below diagram mentions the whole P2P life cycle which initiates from Requisition and closes at Payment.

Requisition :
It is formal document raise by a requester to mention the need or buy of some goods and then go through the company authorization workflow. After it is finalized or approved it converts to a Purchase Order. It mentions the required goods, time frame, location amount and price.

There are two categories of Requisitions we can use. The person/Organization who request for goods is called Buyer/Requester and the person/Organization who supplies the same is called Supplier/Vendor.

• Internal Requisition : Internal requisition mentions the goods transferred from an inventory to another inventory within the organization.
• Purchasing Requisition : Purchasing Requisition mentions goods are requested/needed to buy from a third party i.e. Supplier.

Purchase Order :

It is a Commercial document and first official document provided from a buyer to a supplier. It is the contract document which mentions the Goods, agreed price, quantity, need by date and shipping location etc.

There are the following categories of Purchase orders exist.

• Standard PO : This type of purchase Order is created if the purchase goods once from a supplier. Here the user should know the price, quantity, payment terms, time and location clearly.
• Blanket Purchase Agreement : This type of PO is used to mention the agreed price of the item before actual purchasing. Here the user knows about the goods and services to be purchased but don't know about the exact delivery schedule. We can use a Blanket Release against a Blanket Purchase Agreement to create a actual Purchase Order.
• Planned PO : This is a long term purchasing agreement to buy goods and services from a single supplier. User needs to about the tentative delivery schedule and all the details of the goods and services.  We can issue a scheduled release against a planned order to create a actual Order.
• Contract Purchase Agreement : This is an agreement with suppliers to agree on specific terms and conditions without indicating the goods and services that you will be purchasing. You can later issue standard purchase orders referencing your contracts.

Advance Shipment Notification(ASN):

It is a formal electronic document provided by supplier to buyer prior to the item delivery which includes the goods delivery details i.e shipment date, Identification number, freight information and item details etc. Supplier can create an ASN using various ways like using iSupplier portal, XML gateway etc. An ASN can have billing information too.

Receipt:
For receive goods against a Purchase order this document is created. It has all the information of date of receiving, location, Inventory and Order details.

There are three types of Receipt routing way are there.

• Direct Delivery : The received good directly store into corresponding Inventory.
• Standard Receipt : The received quantity at first receive to a location and then transferred to inventory.
• Inspection Required : In this type, goods received to a location then inspected for quality then deliver to inventory.

Invoice :

Invoice is a document created by supplier to the buyer representing the due amount against the goods or services provided. It can be created against a Purchase Order or a Receipt depending upon the Invoice match Option. 

There are the following types of Payable Invoices are exist:

• Standard Invoice: This is the Invoice is a document created by supplier to the buyer representing the due amount against the goods or services provided.
• Credit Memo : This is a type of Invoice created by supplier to Buyer informing the negative amount.If a customer returns some of the goods then the supplier raise a Credit memo to balance the transaction amount.
• Debit Memo : This type of invoice is created by a Buyer on behalf of supplier with a negative quantity to balance the transaction amount.
• Mixed Type : This type of invoice can have the either positive or negative total amount and can be matched with a Purchase order or with an parent invoice.
• Prepayment : This type of invoice is raised to record the advance payment to a Supplier.
• Expense Reports : Expense reports are the invoices that represent amount due to an employee for his business related expenses.
• Recurring Invoice : This is an invoice type which automatically generated using an Invoice template for a required time interval  as rent.  

Payments : 
It is the payment made to supplier against an Invoice.



Please give your valuable comments on this blog post. :)

PL/SQL Sample Procedure To Check Status Of Internal Manager, Conflict Resolution Manager and Standard Manager in Oracle Apps R12

The below is a sample procedure which check the status of Internal Manager, Conflict Resolution Manager and Standard Manager in Oracle Apps R12. If any one of them is down then it returns ERROR status.

Create the below types

• create or replace type CM_STATUS_TYPE  as object

            ( 
               cm_name varchar2(100),
       target_processes  number,                                   
       actual_processes number
    );

• create or replace type CM_STATUS_TBL  is table of CM_STATUS_TYPE;

Create the following Procedure :

PROCEDURE cm_status_check(   p_status out VARCHAR2,

                             p_status_msg  out VARCHAR2)

AS

v_cm_detail cm_status_tbl;

v_count_correct_cm NUMBER;

BEGIN

       BEGIN

        -- Select the detail of Internal Manager, Conflict Resolution Manager and Standard Manager.

        SELECT cm_status_type(concurrent_queue_name,

          max_processes,

          running_processes)  

          BULK COLLECT INTO v_cm_detail

        FROM apps.fnd_concurrent_queues

        WHERE concurrent_queue_name IN ('FNDICM','FNDCRM','STANDARD');

        exception

        WHEN others THEN

        p_status:='ERROR';

        p_status_msg := 'Error in selecting the CM details'||sqlerrm;

        END;

        

        v_count_correct_cm := 0;

        -- The above query will give the detail of Internal Manager, Conflict Resolution Manager and Standard Manager.

        

        FOR i IN v_cm_detail.FIRST .. v_cm_detail.LAST

        loop

          -- if this condition checks the status of all three CMs 

          IF((v_cm_detail(i).target_processes != 0) AND (v_cm_detail(i).actual_processes!=0) AND         (v_cm_detail(i).target_processes = v_cm_detail(i).actual_processes)) THEN

           v_count_correct_cm:=v_count_correct_cm+1;

          END IF;

        END loop;

        IF v_count_correct_cm = 3 THEN

          p_status :='SUCCESS';

          p_status_msg :='Internal Manager, Conflict Resolution Manager and Standard Manager Is Working Fine.';

        ELSE

          p_status :='ERROR';

          p_status_msg :='Internal Manager, Conflict Resolution Manager and Standard Manager Needs To Be Restarted.';

        END IF;

END  cm_status_check; 

Happy Coding :)

DB Adapter Polling Multiple Records From Database BPEL Process

DB Adapter Polling is one and only inbound operation available and other operations like Select, insert, update, merge, call to a stored procedure and execute pure sql statement are the outbound operations for a DB Adapter.

It means we can use the DB adapter polling operation to initiate a BPEL process which gives the input data to the BPEL process.

We can poll a single record or multiple records from a database with a given time period interval. Also we can poll to the database depending upon some conditions like depends upon the column values.

The number of records poll from a database is also a run time configurable parameter through Weblogic Enterprise Manager.

The below is given an example of a BPEL process which explains all the above mentioned DB Adapter concepts.

• Create SOA project with BPEL a process. Make that BPEL process as Define Service Later.

• In the Composite.xml drag a Database Adapter to the exposed service pane.

• In the DB Adapter wizard give the service name and connection details to the database.
• Choose the operation type as Poll for New or Changed records in a table.

• Click on the Import table button. Choose the table you want to poll. Then click on Next. On the relationship page we can choose a child tables to poll for example if we have Master and child tables then it will poll one master record with all the corresponding child records from the child table.
• Select the primary key for the table. It will took the primary key by default if the table has defined any primary key otherwise we can virtually define the primary key for the polling operation. 
• After that we need to select the columns which we need to poll from the table.
• Then we need to define what the DB Adapter will do after poll the record so that the record will identified as processed. For this we can define the following After read operations.

1. Delete the Rows that were read : It will delete the row from the table after the DB Adapter reads the corresponding record.
2. Logical Delete : It will update a corresponding column value to something different one.
3. Update a Sequencing table/file : It will update another table's column value to store the last read value for example it will store a sequence value used as primary key of the polling DB or the last polling date. 

• Let us choose the logical delete after read operation. I set the process flag column of the employee table here. if polled then adapter update its value from N to P.
• After that on the polling options page set the polling frequency and number of records you want to poll per transaction. set Data&rows per xml document field which will poll that many records from the table on a single polling attempt.

• After that on the selection criteria wizard put where condition of the query. Here I am polling those employees who have salary> 15000

• Connect with wire the DB Adapter with the BPEL process.
• In the BPEL drag a receive activity and receive input from the reference partner DB Adapter. Check the create instance check box on the Receive activity.

Testing the above BPEL DB Adapter :

• Inserted 6 records with process flag is N (unread records)into the Employee table as shown below. 

• After polling the records process_flag of the records become P(polled records). With empname A the salary is 10000 so it should not be polled by the DB adapter.

• As I configured the xml &rows per document as 2 it should create three instances in the weblogic enterprise manager. two of them polls 2 records and one instance polled one record.

Reconfigure the number of xml records polling from Weblogic Enterprise Manager:

• In EM go down to the Services and references section, there it will show the DB adapter. Click on that.

• Then click on the properties tab and set the maxRaiseSize property value.

• Now the DB adapter will poll 5 records at a time. But if you redeploy the project again the EM property modifications will reset.
• All the DB adapter configured property value will resides in the dbadapter_name.jca file.

ALso Check : Test a BPEL process initiated by DB Adapter in Wblogic EM

Please give your valuable feedback on this post.  Happy Coding :)

Creating XML Node Sets From A Delimited String In BPEL Using Transformation

There are several ways in which we can create a custom xml node set for delimitedString value.
Among them one of the way is through XSLT.

Consider we have our xsd structure as :-

• Bring a Transform activity with source and target variable as input varaible.
• Now open the .xsl file and create a custom template for splitting the delimited string into custom xml nodes.
• Now call your custom template while assigning the values to the variable.

For Example:-



Below are the screenshots of both input and and the resultant transformed output.




Also check: Remove Duplicate Nodes with XSLT
                   Creating xml node set from delimited string using Assign activity


Let us know your valuable feedback.

Happy Coding .... :)

Remove Duplicate Nodes From XML Payload Using XSLT

At the time of development sometimes it is required to remove the duplicate nodes from a XML payload depending upon some condition values. The following is a short example for the same.

• Created a BPEL process which input has multiple employee information. The xsd of the input payload looks like below.

• The input variable xml payload is looks like as given below.

• The below is XSLT code snippet which can be used to remove duplicate nodes which have the same employee id in the Input variable xml payload.

   <xsl:template match="@*|node()">

        <xsl:copy>

            <xsl:apply-templates select="@*|node()"/>

        </xsl:copy>

    </xsl:template>

    

   <xsl:template match="client:employee">

        <xsl:if test="not(following::client:employee[client:empid=current()/client:empid])">

            <xsl:copy>

                <xsl:apply-templates select="@*|node()"/>

            </xsl:copy>

        </xsl:if>   

    </xsl:template> 

• Below screenshot is the after transformation of the input variable. 

Also check : Creating XML node set from delimited string

Please give your comments on the same. Happy Coding. :)