Transcript Sayer, Brad, Sinclair, Sarah

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
The Business Case
The Technology
The Process
The Application
Other Application Advantages
Questions
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 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
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.
http://www.oracle.com/technology/products/database/application
_express/index.html
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.
http://www.oracle.com/technology/products/integration/workflow/
workflow_fov.html
The Technology Flow
The Technology Flow
LDAP Server
APEX authentication
Create Vendors
Oracle
Payables
Request Data and Launch Processes
PL/SQL
Database
Package
Ew_po_vendors
Retrieve Data
Vendor Details
Launch Workflow
Oracle Workflow
EWPOVEND
Action Notifications
APEX database
HTTP Requests
Web Browser
Oracle Financials database
Send out emails and notifications
Supplier Creation
Search for
Supplier
Found
FINISH
Not Found
Requestor
Enter a Supplier
Request
Submits
Supplier goes for review
Email with Supplier details
Reviewer
Checks Details
Adds Information
Clicks on the link for review
Reject
FINISH
Email to Requestor
Submits
Supplier goes for approval
Email with Supplier Details
Approver
Reject
FINISH
Approves the Supplier
Requestor and reviewer are emailed
Supplier is created
FINISH
Requestor is emailed
Supplier Reactivation
Search for
Supplier
Found but Inactive
Requestor
Request a Vendor
Reactivation
Submits
Supplier goes for approval
Email with Supplier details
Approver
Approves the reactivation
Supplier is
reactivated
FINISH
Email to Requestor
Rejects the reactivation
FINISH
Email to Requestor
APEX – Application Express
LDAP – we use the built in LDAP authentication
Function in APEX to authenticate via Active Directory
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
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 –New Supplier Form
APEX –New Supplier Form
APEX – Special Validations
City
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.
Postcode
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
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
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
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 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 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);
BEGIN
wf_event.raise(eventname, itemkey);
EXCEPTION
WHEN OTHERS THEN
WF_CORE.CONTEXT('EW_WF',eventname, itemkey);
RAISE;
END;
Workflow
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:
select *
into ewapps_po_vendors_rec
from ewapps_po_vendors
where id = p_item_key;
This code assigns the value
of the vendor name to the
attribute VENDOR_NAME.
wf_engine.setitemattrtext
(itemtype
=> p_item_type,
itemkey
=> p_item_key,
aname
=> 'VENDOR_NAME',
avalue
=>
ewapps_po_vendors_rec.vendor_n
ame);
Workflow
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 )
Workflow
PL/SQL calls from Oracle Workflow
Column
Explanation
P_item_type
This is passed from workflow and will be the
internal name of the process. This
parameter can then be passed on to further
workflow calls.
P_item_key
This is passed from workflow and is the
unique ID for the workflow. In this case this
is the ID we raised the business event with
and links directly back to our APEX vendor
table.
P_Act_id
This is passed from workflow and is the
activity related to this function. Not used in
this example.
P_Funmode
Not used in this example.
P_result
Where a process or function expects a result
passed back – this parameter can be used.
Workflow Model
Workflow – Create Supplier
Workflow – Reactivate Supplier
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 - 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
Upon clicking on the URL the reviewer can check all the
supplier details and add/amend additional information if
required.
APEX - Review
A summarised review/approval screen is also available:
APEX - Review
If Suppliers already exist with the same bank account, they
are displayed to the reviewer and approver in this screen.
APEX Review
Before submitting for final approval the reviewer can add
comments if required
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 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 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
WF_NOTIFICATION.SETATTRTEXT(NID
=> nid,
ANAME =>'REVIEWCOMMENTS',
Avalue=> comments);
APEX Review
Submitting the Notification
Then we set the value of the RESULT (this will be SUBMIT
or REJECT)
WF_NOTIFICATION.SETATTRTEXT(NID
=> nid,
ANAME => 'RESULT',
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.
WF_NOTIFICATION.RESPOND(NID
=>nid,
RESPONDER=>username);
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.
Workflow
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.
Workflow
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
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
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
Workflow APIS and usage:
Oracle Workflow Guide RELEASE 2.6.2
- can be downloaded from Oracle
APEX documentation
http://www.oracle.com/technology/products/database/applic
ation_express/html/doc.html
Questions?