Kishore Ryali

                                Part I – POR_CUSTOM_PKG

One of the common requirements in iProcurement implementation is to perform custom defaulting or validating fields in requisition header/line/distribution during Requisition creation. For example, you may want to default project value based on requestor or validate deliver-to location to check if it is valid for a project etc. The objective of the article is to share different approaches I learnt from my experience, to achieve it.

I will take a simple requirement to explain each of the approaches and analyze their pros and cons. My requirement in is as follows as. Please note I'm using 11.5.10.2 iProcurement.

Requirement:

I’ve defined attributes (Attribute15) on requisition header and line with the name “Fund”. Fund attribute on the line drives charge account generation. User is required to enter fund on header in ‘Checkout: Requisition Information’ page, this value is defaulted or copied to fund attribute on the line. This means user doesn’t have to enter fund attribute for all the lines in the requisition, in fact fund on line is not displayed unless it is shown using OAF Personalization (DFFs are by default not shown in OAF page). Only Fund attribute on header is displayed by setting ‘Rendered’ property to “true” for item ‘ReqHeaderDFF’ using OAF Personalization. Fund on requisition header in Checkout page is shown in the below screenshot.

Different solution approaches for addressing the above requirement are:

1. POR_CUSTOM_PKG

This is Oracle seeded package provided as a hook to include custom defaulting and validation logic in iProcurement requisition creation.

1. Extending Application Module and Controller of Checkout page using OAF extension.

Create a method to copy fund from header to line in extended application module. Extend controller to make a call to that AM method during page events like Edit Lines, Submit etc.

1. Extend existing Helper / Server Command classes using OAF.

iProcurement OAF architecture is designed to use Server Command and Helper classes to encapsulate the business logic. These additional classes contain code that would normally be placed in Application Module. The code is separated to prevent AM from growing too big and promote reusability. We can extend these classes to place custom logic to meet our requirement. This approach requires good understanding of OAF and its integration with underlying BC4J components.

I will use above approaches to implement my requirement, and point out some limitations of each approach. This first article in the series has implementation using POR_CUSTOM_PKG.

Approach 1: POR_CUSTOM_PKG

POR_CUSTOM_PKG provides customization hooks to perform the defaulting logic and validation of the requisition header, line, and distribution data. The procedures in this package are called during requisition creation in iProcurement. Requisitions created in Purchasing module doesn’t use this package.

The procedures are invoked from CustomReqHeaderHelper, CustomReqLineHelper, and CustomReqDistHelper classes which implements interface classes PoRequisitionHeaderEOHelper, PoRequisitionLineEOHelper, and PoReqDistributionEOHelper respectively. Without going too much into details of their implementation, let us look at different pl/sql procedures available in por_custom_pkg.

This package contains 7 procedures (3 for defaulting logic, 3 for validations and 1 for updating charge account). Function of each procedure is self explanatory by looking at their names; I will give a gist of each of them:

If result code is 1, error is displayed on the Delivery tab

If result code is 2, error is displayed on the Billing tab

If result code is 2, error is displayed on the Accounts tab

These procedures are by default do not have any implementation. Once custom code is placed, three profile options control whether customization takes effect on existing requisition process.

The profiles are:

• POR: Enable Req Header Customization

• POR: Enable Requisition Line Customization

• POR: Enable Req Distribution Customization

These profiles have to be set to ‘Yes’ at site level, to tell iProcurement to execute customization in corresponding procedures.

Implementation:

Going back to my requirement of copying fund value from header to line, implementation requires:

• Coding defaulting procedure for requisition line i.e. CUSTOM_DEFAULT_REQ_LINE

• Profile “POR: Enable Requisition Line Customization” need to be set to “Yes” at site level.

In POR_CUSTOM_PKG.CUSTOM_DEFAULT_REQ_LINE procedure, header fund value is available in header attribute15 i.e. IN parameter header_attribute_15, copy its value to line fund value i.e. IN OUT parameter x_attribute15. Return 0 at the end to signal success. Below screenshot shows implementation of the procedure.

Advantages of this approach are it is simple and doesn’t require developer to have understanding of OAF or Java. If customization needs to turned off, clear the profile “POR: Enable Requisition Line Customization” value or set it to “No” at site level.

Limitations of this approach is defaulting procedures are only executed when requisition is first created. If the user changes fund value in header, new value is not copied to line because default procedure is not executed. So this implementation doesn’t fully solve the purpose.

A similar example where defaulting fails is, suppose you want to default a value say ‘0000000’ to header fund when requisition is created. We can code POR_CUSTOM_PKG. CUSTOM_VALIDATE_REQ_HEADER to copy ‘0000000’ to x_attribute15 if it is null. This works when requisition is created from scratch in Non-catalog request page or by selecting inventory item. iProcurement create new records in requisition header (po_requisition_headers_all) and line (po_requisition_lines_all) tables when checkout is done. Suppose you change fund value to ‘1234567’, save it, delete lines in requisition and start over from Non-catalog request page, default value ‘0000000’ is not shown for fund instead  ‘1234567’ is shown. iProcurement doesn’t delete requisition header when lines are deleted. It uses existing header record i.e. same req_header_id which means default header procedure is not executed. These are few scenarios where I felt default procedures doesn’t work the way I wanted it to be. So I have to opt for alternative solution.

The next article in the series uses OAF extension of controller and application module to implement my requirement.

Kishore Ryali

Add comment