A
Integration Scenario

This appendix provides an integration scenario and model based on a fictitious company, Acme, Inc. using Oracle9iAS InterConnect. This appendix discusses the following topics:

• Integration Scenario Overview

• Modeling the Integration

• Implementing the Scenario

• Modeling Business Logic in Oracle Workflow

• Deployment

• Conclusion

Integration Scenario Overview

Each division of Acme, Inc. has multiple Order Fulfillment Systems which are a legacy from various mergers and acquisitions. Maintaining the parts of these systems such as platforms, software, training, etc. is costly and time consuming for Acme. In addition, the lack of integration between the systems prevents business analysis on the enterprise level.

Acme has created a new centralized system and the first phase of the integration project is to synchronize one of the legacy systems with the new system. The first test is to synchronize the Purchase Order information, where all purchase orders from the legacy system are to be reflected in the new system.

The New Centralized System

This new order fulfillment system operates on an Oracle9i database and uses the Oracle9iAS InterConnect Database Adapter to communicate with this system.

The Legacy System

The legacy order fulfillment system operates on an Oracle8i database and uses the Oracle9iAS InterConnect Advanced Queueing Adapter to communicate with this system.

On the Purchase Order table in this system a database trigger queues the changed records. Oracle9iAS InterConnect is configured to listen to that queue to accomplish this integration.

Note: There are many methods available to capture changes to in a system. These methods include but are not limited to database triggers, a batch process using interface tables, and the use of database log files.

The Integration Scenario

Figure A-1 illustrates the integration scenario:

Figure A-1 Integration Scenario

Text description of the illustration integration.gif

The question mark above is the task at hand:

How can we accomplish this task?

The first step in any integration scenario is to model the integration.

• Legacy System
  • Any changes to the Purchase Order table in the Legacy application are published via a database trigger. An administrator must approve all changes such as insert, update, & delete before they are applied to the Order Fulfillment System

• Order Fulfillment System
  • If a change is approved, it is sent to the Order Fulfillment System. If a change is rejected, then a cancellation notification is sent back the Legacy system.

• Additional Issues
  • The process must be non intrusive, the user cannot alter the structure of either system.

  • Synchronization of the primary keys of each system must be maintained by the integration platform.

  • The integration must be scalable and support addition of systems.

Modeling the Integration

Figure A-2 illustrates how Oracle9iAS InterConnect integrates with the scenario in Figure A-1.

Figure A-2 Integration Modeling

Text description of the illustration integration2.gif

Now that the integration scenario has been defined:

How are we going to accomplish this task?

1. The Legacy Application publishes the insert, update, and delete messages onto a queue. To receive or send messages onto that queue, the Oracle9iAS InterConnect Advanced Queueing adapter is used.

   The Order Fulfillment Application is a standard Oracle database and uses the Oracle9iAS InterConnect Database adapter.

2. All messages are routed to Oracle Workflow to apply user-defined logic.

Integration Modeling using iStudio

The following list of steps outlines the process to accomplish this integration.

1. Review Legacy System Database Trigger

2. Create a Project

3. Create the Common View Business Object

4. Create Business Object Events

5. Create Applications

6. Create a Cross Reference Table

7. Create Publish Events

8. Subscribing to Events

9. Create Content Based Routing

10. Create an Oracle Workflow Process Bundle

11. Deploy the Process Bundle to Oracle Workflow

12. Creating Objects in Oracle Workflow for Modeling

13. Deployment

14. Creating Objects in Oracle Workflow for Modeling

15. Modeling Business Logic in Oracle Workflow

16. Deployment
    • Setting Queues

    • Pushing Metadata

    • Exporting and Installing Code

Implementing the Scenario

The following sections describe implementing the integration scenario.

Review Legacy System Database Trigger

The source system uses Oracle8i Advanced Queueing to publish changes to the purchase order table. To be non-intrusive, the user creates a database trigger on the purchase order table. When a record is updated, inserted, or deleted and then committed, the trigger enqueues the appropriate payload. The Oracle9iAS InterConnect Advanced Queueing Adapter is configured to listen on this queue.

The following code is for the database trigger.

CREATE OR REPLACE TRIGGER AQAPP.ENQUEUE_PO
        AFTER INSERT OR DELETE OR UPDATE ON AQAPP.PURCHASE_ORDER FOR EACH ROW
DECLARE
       qname                   VARCHAR2(20)    := 'OUTBOUND_QUEUE';
       enqueue_options         DBMS_AQ.ENQUEUE_OPTIONS_T;
       message_properties      DBMS_AQ.MESSAGE_PROPERTIES_T;
       msgid                   RAW(16);
       recip_agent             SYS.AQ$_AGENT;
       raw_payload             RAW(32767);
       payload                 VARCHAR2(256);
BEGIN
  IF INSERTING THEN
    payload := '<?xml version="1.0" standalone="no"?>'        ||
      '<PO_Insert>' || 
      '<id>'        || :new.id           || '</id>'           ||
      '<action>'    || 'I'               || '</action>'       ||
      '<item>'      || :new.item         || '</item>'         ||
      '<amount>'    || :new.amount       || '</amount>'       ||
      '<quantity>'  || :new.quantity     || '</quantity>'     || '</PO_Insert>';

ELSIF DELETING THEN
    payload := '<?xml version="1.0" standalone="no"?>'        ||
      '<PO_Delete>'   ||
      '<id>'          || :old.id         || '</id>'           ||
      '<action>'      || 'D'             || '</action>'       || '</PO_Delete>';
ELSIF UPDATING THEN
    payload := '<?xml version="1.0" standalone="no"?>'        ||
      '<PO_Update>'   || 
      '<id>'          || :old.id          || '</id>'          ||
      '<action>'      || 'U'              || '</action>'      ||
      '<item>'        || :new.item        || '</item>'        ||
      '<amount>'      || :new.amount      || '</amount>'      ||
      '<quantity>'    || :new.quantity    || '</quantity>'    ||
      '<last_updated>'|| :new.last_updated|| '</last_updated>'|| '</PO_Update>';
END IF;

raw_payload := UTL_RAW.CAST_TO_RAW( payload );

DBMS_AQ.ENQUEUE(  queue_name            => qname
                 ,enqueue_options       => enqueue_options
                 ,message_properties    => message_properties
                 ,payload               => raw_payload
                 ,msgid                 => msgid );
EXCEPTION
      WHEN OTHERS THEN NULL;
END;

Create a Project

A project is a container for the integration logic pertaining to an integration scenario. The following steps describe creating the PO_Integration project using iStudio.

1. From the File menu, select New Project. The Create Project dialog displays.

2. Enter PO_Integration in the Project Name field and click OK. The Repository Information dialog displays.

3. Enter the correct repository information and click OK.

   See Also: "Creating a New Project"

Figure A-3 Creating a Project

Text description of the illustration project.gif

Create the Common View Business Object

Each application has its own semantics and syntax. In order to integrate the data from multiple sources, a common view that is semantically compatible is required. The common views are either events or procedures and are grouped in a business object, located under the Common Views node in iStudio. In this scenario, all events are grouped under the Purchase_Order business object.

The following steps describe creating the Purchase_Order business object.

1. From the File menu select New, then select Business Object. The Create Business Object dialog displays.

2. Enter Purchase_Order in the Business Object Name field and click OK.

   See Also: "Creating Business Objects"

Create Business Object Events

In order to integrate data between two or more systems, a semantically compatible view, or common view, is required. In this scenario, the insert, updated, delete, and cancel events are grouped under the Purchase_Order business object. The following four events must be created:

• PO_Cancel

• PO_Insert

• PO_Update

• PO_Delete

  Note: When an event is created, a Common Data Type representing its structure is automatically created. This common data type can then be reused to define the structure of other events.

The following steps describe creating the PO_Insert event using an XML DTD (eXtensible Markup Language Data Type Definition). The user can also use the database or other common data type to describe the structure of the event.

1. From the File menu, click New, then select Event. The Create Event dialog displays.

2. Select Purchase_Order as the Business Object.

3. Enter PO_Insert in the Event Name field.

4. Click Import and select XML.

5. Select the predefined file, PO_Insert_CV.dtd in the Open dialog and click Open.

6. Select PO_Insert in the Choose Root Dialog and click OK to return to the Create Event dialog.

7. Click OK.

   See Also: "Creating Events"

Use the same steps for the PO_Update, PO_Delete, and PO_Cancel events, substituting the following correct XML DTD for each event. The PO_Cancel, PO_Delete, PO_Insert, and PO_Update events appear in the Design Object Navigator under the Events node as shown in Figure A-4.

DTD Code

Each event has its own XML DTD. The following code is listed for each event.

• PO_Cancel

  <!ELEMENT PO_Cancel (id, action, item, amount, quantity)>
  <!ELEMENT id                  (#PCDATA)>
  <!ELEMENT action              (#PCDATA)>
  <!ELEMENT item                (#PCDATA)>
  <!ELEMENT amount              (#PCDATA)>
  <!ELEMENT quantity            (#PCDATA)>
  
  

• PO_Update

  <!ELEMENT PO_Update (id, action, item, amount, quantity, last_updated)>
  <!ELEMENT id                  (#PCDATA)>
  <!ELEMENT action              (#PCDATA)>
  <!ELEMENT item                (#PCDATA)>
  <!ELEMENT amount              (#PCDATA)>
  <!ELEMENT quantity            (#PCDATA)>
  <!ELEMENT last_updated        (#PCDATA)>
  
  

• PO_Delete

  <!ELEMENT PO_Delete (id, action)>
  <!ELEMENT id                  (#PCDATA)>
  <!ELEMENT action              (#PCDATA)>
  

• PO_Insert

  <!ELEMENT PO_Insert (id, action, item, amount, quantity)>
  <!ELEMENT id (#PCDATA)>
  <!ELEMENT action (#PCDATA)>
  <!ELEMENT item (#PCDATA)>
  <!ELEMENT amount (#PCDATA)>
  <!ELEMENT quantity (#PCDATA)>
  

Figure A-4 Completed Event Node in iStudio

Text description of the illustration bocreated.gif

Note: The Business Object and Events display in the Object Navigator under node Common Views as shown in Figure A-4.

Create Applications

An application in iStudio represents an instance of an adapter communicating with an application. When the user installs an adapter, a unique name is supplied and, in iStudio, this name is used as the name of the application. This scenario demonstrates creating the AQAPP and DBAPP applications.

The following steps describe creating the AQAPP application using iStudio.

1. From the File menu, select New, then select Application. The Create Application dialog displays.

2. Enter AQAPP in the Application Name field and click OK.

Complete the same steps to create the DBAPP application. The AQAPP and DBAPP applications appear in the Design Object Navigator under the Applications node as shown in Figure A-5.

Figure A-5 AQAPP and DBAPP Applications in iStudio

Text description of the illustration Apps.gif

Create a Cross Reference Table

Each system has its own unique identifier or primary key. In most cases, an administrator does not allow any changes to the structure of their systems. Therefore, using a cross reference table, the keys of both systems can be maintained and cross referenced for subsequent updates and deletes.

The following steps describe creating the PO_XREF cross reference table using iStudio. The table is automatically created in the repository schema and is referenced by the subscribing application. The WORKFLOW and DBAPP applications are added to the table, as the publisher and subscriber respectively.

1. From the File menu, click New, then select Cross Reference Tables. The Create Cross Reference Table dialog displays.

2. Enter PO_XREF in the Table Name field and click OK.

3. Right click on the PO_XREF in the Navigator and add the WORKFLOW and DBAPP applications. The PO_XREF cross reference table appears in the Design Object Navigator under the Cross Reference Tables node as shown in Figure A-6.

   See Also: "Creating Cross-Reference Tables"

Figure A-6 PO_XREF Cross Reference Table in iStudio

Text description of the illustration xref.gif

Create Publish Events

The database trigger in the Legacy Application, AQAPP, publishes messages when records are inserted, updated, or deleted on the purchase order table. This process happens outside the Oracle9iAS InterConnect environment. The Oracle9iAS InterConnect Advanced Queueing adapter is configured to read these messages. The publish events under the iStudio application will:

• Map the application view to the common view.

• Perform transformations.

• Publish the new event to subscribers in the Oracle9iAS InterConnect environment.

The following steps describe how the message received from the Legacy Application queue is processed.

See Also: "Publishing an Event"

Step 1 Starting the Publish Wizard

To start the Publish Wizard:

1. Expand the Applications node in the Design Object Navigator.

2. Select and expand the AQAPP application.

3. Select the published events node.

4. Right-click Published Events and select New. The Publish Wizard displays.

Step 2 Using the Publish Wizard to Publish the PO_Insert Event

When the Publish Wizard starts the following pages display.

1. Select an Event Page
   1. Enter information in the following fields:
      • Application--Select AQAPP for the application.

      • Message Type--Select XML for the message type.

   2. Expand the Business Objects tree in the Select an Event box and drill down to PO_Insert.

   3. Select PO_Insert and click Next.

      Figure A-7 Publish Wizard--Select an Event page

      
      
      Text description of the illustration pubwiz.gif

2. Define Application View Page
   1. Import Attributes

      Import attributes from the common view by clicking Import and select Common View. The structure of the PO_Insert common view event displays. If the application view is different from the common view, use the database or an XML DTD to define the structure.

   2. Create an Event Map

      An event is received and converted into a common view to which any application can map. If the structure of one or more events is identical, then routing becomes an issue. An event map is used to distinguish the routing in this situation. The Action field in the application view contains an I for insert, a U for update, or a D for delete. Complete the following steps to create an event map:

      • Click Event Map, then click Add.

      • Select the Action field and enter I.

      • Click Add.

   3. Click Next.

3. Define Mapping Page

   Use the Define Mapping page to map fields from the AQAPP View to the common view using transformations. In this scenario the structure is identical, therefore, the ObjectCopy transformation is used to map all the fields at once. To define new mappings:

   1. Click New. The Mapping Parameters dialog displays.

   2. Expand the PO_Insert tree and select the PO_Insert node in the AQAPP View box.

   3. Select ObjectCopy in the Transformations box.

   4. Expand the PO_Insert tree and select the PO_Insert node in the Common View box.

   5. Click OK. The new mapping displays in the Summary box of the Define Mapping page.

   6. Click Finish.

   Figure A-8 Publish Wizard--Mapping Parameters

   
   
   Text description of the illustration mapparams.gif

To create the PO_Update and PO_Delete publish events, repeat the same steps, using the following values for steps 2 and 3.

• PO_Update
  • Use the PO_Update common view.

  • The event map value is U.

  • Use the ObjectCopy transformation and map to PO_Update.

• PO_Delete
  • Use the PO_Delete common view.

  • The event map value is D.

  • Use the ObjectCopy transformation and map to PO_Delete.

Subscribing to Events

The DBAPP application subscribes to the following three events:

• PO_Insert

• PO_Update

• PO_Delete

The AQAPP application subscribes only to the PO_Cancel event.

See Also: "Subscribing to an Event"

DBAPP Application Subscriptions

Step 1 Starting the Subscribe Wizard

1. In the Design Object navigator, expand the Application node.

2. Select and expand the Application node to display the Subscribed Events node.

3. Right-click Subscribed Events and select New. The Subscribe Wizard displays.

Step 2 Using the Subscribe Wizard to Subscribe to the PO_Insert Event

1. Select an Event Page
   1. Enter information in the following fields:
      • Application--Select DBAPP.

      • Message Type--Select Database.

   2. Expand the Business Objects node in the Select an Event box and drill down to PO_Insert.

   3. Select PO_Insert and click Next.

   Figure A-9 Subscribe Wizard--Select an Event page

   
   
   Text description of the illustration subwiz.gif

2. Define Application View Page
   1. Import attributes from the database:
      • Click Import and select Database. The Database Login dialog displays.

      • Enter the correct information to login to the database and click Login. The Oracle Database Browser dialog displays.

      • In the Browser dialog, expand the Tables/Views node and select DBAPP.PO.

      • Click Done.

      Figure A-10 Subscribe Wizard--Oracle Database Browser

      
      
      Text description of the illustration browser.gif

   2. Create a cross reference.

      In "Create a Cross Reference Table", the PO_XREF cross reference table was created. This table synchronizes the primary keys on the source and target systems.

      • Click Cross Reference and select PO_XREF. The XRef dialog displays.

      • Select POID in the Application Returned Arguments For XRef box.

      • Select id in the Common View box.

      • Click Map.

      • Click OK.

      Figure A-11 Subscribe Wizard--Cross Reference

      
      
      Text description of the illustration xref2.gif

   3. Click Next.

3. Define Mapping Page
   1. Define a new mapping:
      • Click New. The Mapping Parameters dialog displays.

      • Expand the PO_Insert tree and the PO_Insert node in the Common View box. Map the following:

        Common View	Transformation	DBAPP View
        item	CopyFields	POITEM
        amount	CopyFields	PRICE
        quantity	CopyFields	QUANTITY

      • Click OK.

      Figure A-12 Subscribe Wizard--Mapping Parameters

      
      
      Text description of the illustration definemap.gif

   2. Click Next.

4. Define Stored Procedure Page
   1. Select sub_PO_Insert_OAI_V1 from the SQL code drop down list. The SQL code displays in the box.

   2. Add the following code:

      PROCEDURE sub_PO_Insert_OAI_V1( POID          IN OUT LONG,
                                      POITEM        IN LONG,
                                      PRICE         IN LONG,
                                      QUANTITY      IN NUMBER,
                                      LAST_UPDATED  IN DATE)
      AS
       v_poid NUMBER;
      
       BEGIN
        SELECT PO_SEQ.NEXTVAL INTO v_poid FROM dual;
        POID :=v_POID;
      
        INSERT INTO PO VALUES
         ( v_POID, POITEM, PRICE, QUANTITY, SYSDATE );
       COMMIT;
      END sub_PO_Insert_OAI_V1;
      
      

   3. Click Finish.

Step 3 Create the Subscribed PO_Update Event

The wizard steps have been abbreviated:

1. Select an Event Page

   Select the PO_Update event.

2. Define Application View Page

   Import the Common View.

3. Define Mapping Page
   1. Map the same parameters as described in PO_Insert.

   2. In addition, map the following:
      • Expand the PO_Update tree and node in the Common View box and select id.

      • Select the LookupXref transformation.

      • Expand the PO_Update tree and select POID in the Application View box.

      • Click Apply. The Mapping dialog displays.

      • Select the Req. checkbox for table listed in the Parameters column and click OK.

   3. Click Next.

4. Define Stored Procedure Page
   1. Select sub_PO_Update_OAI_V1 for the SQL code for field. The code displays in the box.

   2. Add the following code:

      PROCEDURE sub_PO_Update_OAI_V1( POID          IN NUMBER,
                                      POITEM        IN LONG,
                                      PRICE         IN LONG,
                                      QUANTITY      IN NUMBER,
                                      LAST_UPDATED  IN DATE)
      AS
       v_poid       NUMBER :=poid;
       v_poitem     LONG   :=poitem;
       v_price      LONG   :=price;
       v_quantity   NUMBER :=quantity;
      BEGIN
        UPDATE PO SET  poitem = v_poitem, price = v_price
                       quantity = v_quantity, last_updated = sysdate
        WHERE poid = v_poid;
        COMMIT;
      
      EXCEPTION
        WHEN OTHER THENS NULL;
      
      END sub_PO_Update_OAI_V1;
      
      

   3. Click Finish.

Step 4 Create the Subscribe PO_Delete Event

The wizard steps have been abbreviated:

1. Select an Event Page

   Select the PO_Delete event.

2. Define Application View Page

   Import the Common View.

3. Define Mapping Page
   1. Map the same parameters as described in PO_Insert.

   2. In addition, map the following:
      • Expand the PO_Delete tree and node in the Common View box and select id.

      • Select the DeleteXref transformation.

      • Expand the PO_Delete tree and select POID.

      • Click Apply. The Mapping dialog displays.

      • Select PO_XREF from the values column and click OK.

   3. Click Next.

4. Define Stored Procedure Page
   1. Select sub_PO_Delete_OAI_V1 for the SQL code for field. The code displays in the box.

   2. Add the following code:

      PROCEDURE sub_PO_Delete_OAI_V1( POID          IN NUMBER,
                                      POITEM        IN LONG,
                                      PRICE         IN LONG,
                                      QUANTITY      IN NUMBER,
                                      LAST_UPDATED  IN DATE)
      AS
       v_poid       NUMBER :=poid;
      BEGIN
        DELETE FROM WHERE PO v_poid = poid;
        COMMIT;
      EXCEPTION
        WHEN OTHERS THEN NULL;
      
      END sub_PO_Update_OAI_V1;
      
      

   3. Click Finish.

AQAPP Application Subscriptions

The AQAPP subscribes to the PO_Cancel event.

1. Select an Event Page
   1. Enter information in the following fields:
      • Application--Select AQAPP.

      • Message Type--Select AQAPP.

   2. Select PO_Cancel and click Next.

2. Define Application View Page
   1. Import attributes from the common view and click Next.

3. Define Mapping Page
   1. Define a new mapping:
      • Click New and map the following:

        Id Copyfields Id

      • Click OK.

   2. Click Finish.

Create Content Based Routing

When an event is published, it is automatically routed to any subscriber to that event by default. If the routing of an event needs to be based on a value in the payload (message or message header) then Content Based Routing is required. in this scenario all changes to the purchased orders must be approved and therefore must be routed to Oracle Workflow to apply our business logic.

The logic to be applied for the Events PO_Insert, PO_Update & PO_Delete is:

If the source application is AQAPP then route to destination application WORKFLOW. The Wizard steps are:

• Source Page: Select OAI_Header.SendingApplication

• Chose Operator Page: Select =

• Chose Value Page: Enter AQAPP

• Addition Condition Page: Select Radio Button Complete & press Finished

• Destination Page: Select WORKFLOW

If the source application is WORKFLOW, then route to destination application DBAPP. The Wizard steps are:

• Source Page: Select OAI_Header.SendingApplication

• Choose Operator Page: Select =

• Chose Value Page: Enter WORKFLOW

• Addition Condition Page: Select Radio Button Complete & press Finished

• Destination Page: Select DBAPP

Repeat for events: PO_Update and PO_Delete

Figure A-13 Completed Content Routing in iStudio

Text description of the illustration routing2.gif

Create an Oracle Workflow Process Bundle

A process bundle enables related business processes to be grouped and transferred to the Oracle Workflow environment where user-defined business logic is applied.

Each business process enables related publish, subscribe, invoke, and implement activities to be grouped and placed in the Oracle Workflow Business Event System.

Step 1 Create a Process Bundle

The following steps describe creating the PO process bundle using iStudio:

1. From the project tree, expand the Workflow node and drill down to Process Bundle.

2. Right click on Business Processes and select New. The Create Process Bundle dialog displays.

3. Enter PO in the Process Bundle Name field and click OK.

Step 2 Create Business Process

The following steps describe creating the PO business process using iStudio:

1. Expand the Process Bundle node on the project tree and drill down to Business Processes.

2. Right click on Business Processes and select New. The Create Business Process dialog displays.

3. Enter PO in the Business Process Name field and click OK.

Step 3 Create the Subscribe and Publish Activities

The Oracle Workflow business process uses the common view therefore, transformation and mapping is not required. The following lists the types of activities:

• Subscribe--Oracle Workflow receives a message from Oracle9iAS InterConnect.

• Publish--Oracle Workflow sends a message to Oracle9iAS InterConnect.

• Invoke--Oracle Workflow sends a request message to Oracle9iAS InterConnect and receives a reply.

• Implement--Oracle Workflow receives a request from Oracle9iAS InterConnect and sends a reply.

In this scenario, the PO_Insert, PO_Update, and PO_Delete messages are routed to Oracle Workflow to apply business logic. Based on this logic, messages are either sent to the Order Fulfillment Application or the PO_Cancel message is sent to the Legacy Application. Oracle Workflow must:

• Subscribe to PO_Insert and publish PO_Insert.

• Subscribe to PO_Update and publish PO_Update.

• Subscribe to PO_Delete and publish PO_Delete.

• Publish PO_Cancel.

Step 4 Create Subscribe Activity: PO_Insert, PO_Update, and PO_Delete

The following steps describe creating the subscribe activity using iStudio:

1. From the project tree, expand the Workflow node and drill down to Business Processes.

2. Right click on PO business process and select New Subscribe Activity. A right click on any item displays a pop-up box.

3. Select Event PO_Insert and click OK.

Repeat these steps for the PO_Update and PO_Delete events, substituting the correct values where necessary.

Step 5 Create Publish Activity: PO_Insert, PO_Update, PO_Delete, and PO_Cancel

The following steps describe creating the publish activity using iStudio:

1. From the project tree, expand the Workflow node and drill down to Business Processes.

2. Right click on PO business process and select New Publish Activity. A right click on any item displays a pop-up box.

3. Select Event PO_Insert and click OK.

Repeat these steps for the PO_Update, PO_Delete, and PO_Cancel events, substituting the correct values where necessary. The subscribe and publish events appear in the Design Object Navigator under the PO node as shown in Figure A-14.

Figure A-14 Subscribe and Publish Activities in iStudio

Text description of the illustration processbundles.gif

Deploy the Process Bundle to Oracle Workflow

Deploying the Oracle Workflow process bundle accomplishes the following:

• Places the event definitions in the Oracle Workflow Business Event System.

• Creates a default Oracle Workflow file (.wft).

• Launches the Oracle Workflow Builder and Monitor.

  See Also: Chapter 7, "Using Oracle Workflow"

The following steps describe deploying the process bundle to Oracle Workflow:

1. Right click the Workflow node on the Deploy tab in iStudio and select Deploy. The Deploy dialog displays.

2. Select Event Definitions to Workflow Business Event System, then Process Definitions for File in the Deploy to Workflow box and click OK. The Workflow BES Login dialog displays.

3. Log in to Oracle Workflow using the correct username, password, and URL. Click OK. The Deploy dialog displays.

4. Enter a file name for the Oracle Workflow file such as InterConnect_Demo.wft in the File Name field and click Open. Oracle Workflow is started with InterConnect_Demo.

Figure A-15 Completed Deployment in Oracle Workflow

Text description of the illustration workflowa.gif

Creating Objects in Oracle Workflow for Modeling

Lets' review the original requirement.

"An administrator must approve all changes such as insert, update, & delete before they are applied to the Order Fulfillment System. If a change is approved, it is sent to the Order Fulfillment System. If a change is rejected, then a cancellation notification is sent back the Legacy system."

This Business Logic can be implemented in Oracle Workflow. The Oracle Workflow components require are:

• An Item Type equivalent to a Project

• An Attribute An object to hold the message in the event

• A Process To model the Business Logic

• Events For the modeling in the process.

• A Notification To notify the administrator in the Oracle Workflow Monitor.

Components transferred from iStudio.

• Item Type: OAI Process Bundle: PO

• Attribute: OAI Message

• Process: OAI Business Process: PO

• Events:
  • Publish Purchase_Order.PO_Cancel

  • Publish Purchase_Order.PO_Insert

  • Publish Purchase_Order.PO_Update

  • Publish Purchase_Order.PO_Delete

  • Subscribe Purchase_Order.PO_Insert

  • Subscribe Purchase_Order.PO_Update

  • Subscribe Purchase_Order.PO_Delete

Oracle Workflow components are required to create a Notification.

Message

The message a notification activity will send.

Lookup Type

A static list of values that can be referenced various object. For example a message attribute can reference a lookup type as a means of providing a list of possible responses to the performer of a notification.

Notification

When the workflow engine reaches a notification activity, it issues a Send() API call to the Notification System to send the message to an assigned performer. When a performer responds to a notification activity, the Notification System processes the response and informs the workflow engine that the notification activity is complete.

What Oracle Workflow provides.

Oracle Workflow has a set of pre-defined item types with standard functionality The Standard item type contains generic activities that can be copied in a users item type. In this scenario we will be using the Lookup Type Approval.

Copy Lookup Type (Approval)

As described, the user must create a Oracle Workflow Notification. The notification has two dependent objects, A lookup Type and a Message. The Lookup Type (Approval) can be copied from the standard item type.

Create an Oracle Workflow Message

The following steps describe creating a new Oracle Workflow message called Insert_Message

1. In the Object Navigator right click on the Message Node and select New to launch the property sheet. In each tab add the following entries:

2. Message Tab
   • Internal Name: Insert_Message

   • Display Name: Insert Message

   • Description: Insert Message

3. Body Tab:
   • Subject: Insert Message

   • Text Body: A record has been Inserted in the Purchase Order Table.

4. Result Tab:
   • Display Name: Insert_Message

   • Description: Insert_Message

   • Lookup Type: Approval (From Lookup Type)

5. Click OK

Using the default Copy and Paste functionality create the following messages using message Insert_Message as the template:

• Update_Message--Repeat the above steps and use the same setting, changing all references to insert to update.

• Delete_Message--Repeat the above steps and use the same setting, changing all references to insert to Delete.

Create an Oracle Workflow Notification

The following steps describe creating a new Oracle Workflow Notification.

1. In the Object Navigator right click on the Notification Node and select New to launch the property sheet. In each tab add the following entries:

2. Activity Tab:
   • Internal Name:Insert_Notification

   • Display Name: Insert_Notification

   • Description: Insert_Notification

   • Message: Insert_Message (Created previous step)

   • Result Type: Approval (From Lookup Type)

3. Click OK

Using the default Copy and Paste functionality create the following notifications using notification Insert_Notification as the template:

• Update_Notification--Repeat the above steps and use the same setting, changing all references to insert to update.

• Delete_Notification--Repeat the above steps and use the same setting, changing all references to insert to delete.

Figure A-16 Completed Oracle Workflow Notifications

Text description of the illustration deploycomplete.gif

Modeling Business Logic in Oracle Workflow

Now that all of the required objects have been created, the business logic can be modeled. The following steps describe this process.

1. In the Oracle Workflow Object Navigator, expand the OAI Process Bundle: PO item type.

2. Expand the Processes node.

3. Right click on OAI Business Process: PO and select Process Details.

   Another way to display the process details is to double-click on OAI Business Process: PO.

4. Drag and drop the following from the Oracle Workflow Object Navigator to the Oracle Workflow Workspace:
   • Insert_Notification

   • Update_Notification

   • Delete_Notification

5. Rearrange the items as shown in Oracle Workflow Builder.

   Figure A-17 Items Arranged in Oracle Workflow Builder

   
   
   Text description of the illustration wfbuilder.gif

6. The subscribe events are the entry point into this process, therefore, the Start/End Property for each event must be edited and set to START. To launch the property sheet of each object by Double clicking on the object. The Start/End property is Under the Node tab.
   • Subscribe event Purchase_Order.PO_Insert

   • Subscribe event Purchase_Order.PO_Update

   • Subscribe event Purchase_Order.PO_Delete

7. The publish events are the exit point from this process, therefore, the Start/End Property for each event must be edited and set to END. To launch the property sheet of each object Double click on the object. The Start/End property is under the Node tab.
   • Publish Event Purchase_Order.PO_Insert

   • Publish Event Purchase_Order.PO_Update

   • Publish Event Purchase_Order.PO_Delete

   • Publish Event Purchase_Order.PO_Cancel

8. The notifications must be assignment to a performer in order for that person to receive the notification in the Oracle Workflow Monitor. The Performer value property should be set to SYSADMIN for each notification. To launch the property sheet of each object by Double clicking on the object. The Performer Value field is under the Node tab.
   • Notification: Insert_Notification

   • Notification: Update_Notification

   • Notification: Delete_Notification

9. Mapping lines need to be drawn between the object to define the process flow. Line are drawn by right click and drag fro one object to another
   1. Draw a mapping line from Subscribe Purchase_Order.PO_Insert to Insert_Notification.

   2. Draw a mapping line from Insert_Notification to Publish Purchase_Order.PO_Insert and select Approve from the pop-up that will automatically launch when the line is drawn.

   3. Draw a mapping line from Insert_Notification to Publish Purchase_Order.PO_Cancel and select Reject from the pop-up that will automatically launch when the line is drawn.

   4. Repeat steps for Update & Delete objects.

10. Save work to the database.

    Figure A-18 Completed Business Process in Oracle Workflow Builder

    
    
    Text description of the illustration wfbuildera.gif

Deployment

The Oracle Workflow item type OAI Process Bundle:PO is validated when saved to the database. The next step is to deploy the Oracle9iAS InterConnect objects.

Setting Queues

The AQAPP application in iStudio corresponds to the Advanced Queuing adapter that communicates with the legacy application. The legacy application, through a database trigger, places inserted, updated, and deleted records onto a queue using Oracle Advanced Queuing. To communicate to and from the Oracle9iAS InterConnect environment, the adapter must be configured to send and receive on those external queues.

The following steps describe this task.

1. On the Deploy tab in iStudio, expand the Applications tree and drill down to AQAPP.

2. Expand the AQAPP node and drill down to the Routing node.

3. Expand the Routing node and select Application Queues. The Application Queues property sheet displays on the right side of the iStudio window.

4. Select Edit from the Edit menu on the menu bar. This will launch the Edit Application Queue dialog

5. Add the application Queue name to each event:

   Queue Name	Event
   INBOUND_QUEUE	PO_Cancel
   OUTBOUND_QUEUE	PO_Insert, PO_Update, and PO_Delete

6. Click OK.

Figure A-19 Application Queues in iStudio

Text description of the illustration setqueues.gif

Pushing Metadata

Each adapter has different cache settings to minimize communication to the repository and to improve performance. Pushing metadata synchronizes the adapter and repository metadata. The following steps describe this task:

1. Select File from the menu bar, then Push Metadata. The Push Metadata dialog displays.

2. Select the applications to which to push metadata and click OK.

Exporting and Installing Code

Depending on the adapter type, there is code that must be exported to a file and installed in the target application database. The following steps describe exporting the code using the Export Application dialog in iStudio.

1. Select File from the menu bar, then select Export. The Export Application dialog displays.

2. Select the application(s) to export code.

3. Enter the file prefix in the File Prefix field and click OK.

The resulting text files are a SQL*Plus script that is executed on the target schema.

See Also: "Exporting Stored Procedures"

Example

The following example helps to explain the exporting and installing code task. This example is based on the following:

• Adapter type--Database Adapter

• iStudio application--DBAPP

• Subscribe event--PO_Delete

  PROCEDURE sub_PO_Delete_OAI_V1 (   POID         IN NUMBER,
                                     POITEM       IN LONG,
                                     PRICE        IN LONG,
                                     QUANTITY     IN NUMBER,
                                     LAST_UPDATED IN DATE,)
  AS
            v_poid NUMBER :=poid;
  BEGIN     DELETE FROM PO WHERE v_poid = poid;
            COMMIT;
  EXCEPTION
            WHEN OTHERS THEN NULL;
  
  END sub_PO_Delete_OAI_V1;
  
  

Conclusion

The final step in this scenario is to test the integration.

• A record should be inserted into the Legacy System.

1. The legacy system's database trigger enqueues the record onto its OUTBOUND_QUEUE.

2. Oracle9iAS InterConnect received the message, performs transformations, converts data to a common view and routes the message to Oracle Workflow.

3. Oracle Workflow applies the business logic and issues a notification.

4. The SYSADMIN:
   • Logs on to Oracle Workflow Monitor

   • Receives the Insert_notification.

   • Approves the record.

5. Oracle9iAS InterConnect received the message, performs transformations, cross references the primary keys, converts data to the application View, and routes the message to Order Fulfillment System

6. The deployed code receives the message and inserts the record into the Order Fulfillment system

7. Oracle Enterprise Manager
   • The inserted record can be examined.

   • Integration throughput can be monitored.

This process should be repeated for Update & Delete.

Figure A-20 Oracle Workflow Home Page

Text description of the illustration concl.gif

Figure A-21 Oracle Workflow Worklist and Notification Details

Text description of the illustration concl2.gif