slide1 n.
Skip this Video
Download Presentation
NZOUG Conference 2010

Loading in 2 Seconds...

play fullscreen
1 / 44

NZOUG Conference 2010 - PowerPoint PPT Presentation

  • Uploaded on

NZOUG Conference 2010. Electronically approve and create Suppliers in Oracle Financials using a combination of APEX and Oracle Workflow. Brad Sayer Team Leader - Systems Environment Waikato (Asparona/More4Apps). Sarah Sinclair Technical Consultant Specialist Solutions. Agenda.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'NZOUG Conference 2010' - zach

Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

NZOUG Conference 2010

Electronically approve and create Suppliers in Oracle Financials using a combination of APEX and Oracle Workflow.

Brad Sayer

Team Leader - Systems

Environment Waikato


Sarah Sinclair

Technical Consultant

Specialist Solutions

  • The Business Case
  • The Technology
  • The Process
  • The Application
  • Other Application Advantages
  • Questions
the business case
The Business Case

Environment Waikato created their suppliers in Oracle Financials using the common approach where users complete a pre-printed form and send it to a manager for approval.

the business case1
The Business Case

Once approved the Payables processing staff would manually create the Supplier master-file records.

Lots of requests were received on a daily basis and delays during the manual process were experienced in addition to a lack of audit and volume control.

There has to be a better way!

technology components
Technology Components
  • Oracle Application Express
  • Oracle Application Express (Oracle APEX) is a rapid web application development tool for the Oracle database.  Using only a web browser and limited programming experience, you can develop and deploy professional applications that are both fast and secure.
technology components1
Technology Components

Oracle Workflow Builder

Oracle Workflow delivers a complete workflow management system that supports business process based integration. Its technology enables modeling, automation, and continuous improvement of business processes, routing information of any type according to user-defined business rules.

Oracle Workflow 2.6.2 was used in this example.

apex application express
APEX – Application Express

LDAP – we use the built in LDAP authentication

Function in APEX to authenticate via Active Directory

apex supplier search
APEX – Supplier Search

Forced to initially search for the supplier in Financials

Supplier Name and Address automatically enclosed in % wildcard. Searches are case insensitive.

apex search results
APEX – Search Results

The result set returns vendors in the po_vendors and po_vendor_sites views and also any PENDING vendors still in the workflow process which may match their criteria.

apex special validations
APEX – Special Validations


We use an AJAX HTTP call to validate city against our list of cities in the AJAX database. If an invalid city is used then a city selection box is automatically popped up. This is all done using Javascript and HTML.


We link out to the NZ Post site so end users can lookup correct post codes.

Bank Account

We have a bank account validation program which ensures the bank account is in the correct format.

apex storing the data
APEX – Storing the data

We use the APEX database to store a copy of the request in the ewapps_po_vendors table.

There is a unique ID for each request which is used to raise the business event and is used for the workflow itemkey.

Details about the approver, reviewer and process dates are kept in this table.

Once the vendor is approved and created in Oracle Financials this table is updated with the corresponding vendor_id and vendor_site_id.

workflow business event
Workflow – Business Event

There are two things we must do before we can call our workflow:

Setup the Business event

Setup the workflow

Our business event is called ew.apps.ap.vendor.create and it subscribes to our workflow EWPOVEND.

workflow raise the business event
Workflow – Raise the Business Event

Inside APEX when the requestor submits the vendor for creation – we call out to the Oracle Financials database package to raise the event.

There are two parameters:

The event name (ew.apps.ap.vendor.create)

A unique itemkey

workflow raise the business event1
Workflow – Raise the Business Event

The itemkey will uniquely identify this workflow.

In this case we use the primary key from our APEX ewapps_po_vendors table so we can easily link our workflow back to our vendor.

workflow raise the business event2
Workflow – Raise the Business Event

This is the PL/SQL code which raises the event:

PROCEDURE raise_event(eventname IN VARCHAR2,

itemkey IN VARCHAR2 ) IS

l_parameter_list wf_parameter_list_t;

l_debug varchar2(200);


wf_event.raise(eventname, itemkey);



WF_CORE.CONTEXT('EW_WF',eventname, itemkey);




Load Attributes

This function is where we load up all the details that workflow will need to send notifications and make decisions about what to do.

We select the details from the APEX vendor temporary table:

This code assigns the value of the vendor name to the attribute VENDOR_NAME.

select *

into ewapps_po_vendors_rec

from ewapps_po_vendors

where id = p_item_key;


(itemtype => p_item_type,

itemkey => p_item_key,

aname => 'VENDOR_NAME',

avalue => ewapps_po_vendors_rec.vendor_name);


PL/SQL calls from Oracle Workflow

Here is an example of how the pl/sql procedure to set the attribute values looks.

Oracle Workflow expects these parameters:

PROCEDURE setattributevalues(

p_item_type IN VARCHAR2,

p_item_key IN VARCHAR2,

p_actid IN NUMBER,

p_funmode IN VARCHAR2,

p_result OUT VARCHAR2 )


PL/SQL calls from Oracle Workflow

workflow notifications
Workflow - Notifications

The reviewer will receive one email notification for each supplier request containing all the entered supplier details.

A reviewer can review each supplier request individually by clicking the “Click here to SUBMIT/REJECT the Supplier” url or open a list of pending requests by clicking the “Click here to see all Pending Suppliers for review” url in the email notification.

workflow notifications1
Workflow - Notifications

Here is an example of the email notification in Outlook the reviewer received for a new supplier request.

Notice the only option is to click on a URL. The Reviewer and Approver are required to log into the Application to respond to the notifications.

apex review
APEX - Review

Upon clicking on the URL the reviewer can check all the supplier details and add/amend additional information if required.

apex review1
APEX - Review

A summarised review/approval screen is also available:

apex review2
APEX - Review

If Suppliers already exist with the same bank account, they are displayed to the reviewer and approver in this screen.

apex review3
APEX Review

Before submitting for final approval the reviewer can add comments if required

workflow review rejection
Workflow – Review Rejection

If the reviewer decides to reject the supplier for creation they are required to enter comments.

The email notification goes back to the requestor with those comments.

apex review4
APEX Review

Submitting the Notification

Clicking the Submit or Reject button in APEX will call our Oracle database package to respond to the notification.

We pass in the notification id, the apex user, the action and any comments.

apex review5
APEX Review

Submitting the Notification

In the Oracle Financials database package the following actions occur to action the notification. Our Notification has an attribute associated with it called: REVIEWCOMMENTS.

First we set the value of this attribute so these comments can be passed on the next step in the workflow



Avalue=> comments);

apex review6
APEX Review

Submitting the Notification

Then we set the value of the RESULT (this will be SUBMIT or REJECT)



Avalue=> status);

Then we respond to the notification – passing in the username. This will in effect submit the notification with the result and comments as we have set.



apex approve
APEX Approve

Approving the Notification

Once submitted and the notification has been passed onto the Approver, the notification is then removed from the Reviewers list and now appears in the Approvers list. The approver will now receive a notification.


Approving the Notification

The approver will receive one email for each supplier requested with the supplier details.

A approver can review each supplier request one by one from within their notification OR

View all pending approvals via APEX, depending on which URL they click on in their notification.


Rejecting the Notification

If the approver decides to reject the supplier for creation they are required to enter comments.

The email goes back to the reviewer and the requestor.

oracle financials supplier creation
Oracle Financials – Supplier Creation

Upon Approval the following Supplier data is created automatically in Oracle Financials using built in API calls:

  • Supplier
  • Supplier Site
  • Bank Account and Assignment
  • Contact information
  • Descriptive flexfields – Requestor, Reviewer, Approver
other advantages
Other Advantages

Leverage of standard Oracle Workflow features, e.g.:

  • Vacation Rules
  • Process Flow
  • Escalation by System Administrator

Use profiles to nominate the reviewer and approver

No synchronization between applications required

Is also a useful supplier search tool.

more information
More information

Workflow APIS and usage:Oracle Workflow Guide RELEASE 2.6.2- can be downloaded from Oracle

APEX documentation