NZOUG Conference 2010

1. 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

2. Agenda • The Business Case • The Technology • The Process • The Application • Other Application Advantages • Questions

3. 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.

4. 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!

5. 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

6. 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

7. The Technology Flow

8. The Technology Flow

9. Supplier Creation

10. Supplier Reactivation

11. APEX – Application Express LDAP – we use the built in LDAP authentication Function in APEX to authenticate via Active Directory

12. APEX – Supplier Search Forced to initially search for the supplier in Financials Supplier Name and Address automatically enclosed in % wildcard. Searches are case insensitive.

13. 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.

14. APEX –New Supplier Form

15. APEX –New Supplier Form

16. 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.

17. 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.

18. 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.

19. 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

20. 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.

21. 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;

22. 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: 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; wf_engine.setitemattrtext (itemtype => p_item_type, itemkey => p_item_key, aname => 'VENDOR_NAME', avalue => ewapps_po_vendors_rec.vendor_name);

23. 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 )

24. Workflow PL/SQL calls from Oracle Workflow

25. Workflow Model

26. Workflow – Create Supplier

27. Workflow – Reactivate Supplier

28. 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.

29. 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.

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

31. APEX - Review A summarised review/approval screen is also available:

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

33. APEX Review Before submitting for final approval the reviewer can add comments if required

34. 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.

35. 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.

36. 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);

37. 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);

38. 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.

39. 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.

40. 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.

41. 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

42. 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.

43. 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/application_express/html/doc.html

44. Questions?