|
Oracle® Application Server Integration InterConnect User's Guide
10g Release 2 (10.1.2) B14069-02 |
|
 Previous |
 Next |
|
Oracle® Application Server Integration InterConnect User's Guide
10g Release 2 (10.1.2) B14069-02 |
|
|
Previous |
Next |
This appendix provides an integration scenario and model based on a fictitious company, Acme, Inc. using OracleAS Integration InterConnect. It contains the following topics:
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, and so on is costly and time consuming for Acme. In addition, the lack of integration between the systems prevents business analysis at the enterprise level.
Acme has created a new centralized system, and the first phase of the integration project is to synchronize the purchase order information on one of the legacy systems with the new system.
The new order fulfillment system operates on an Oracle Database 10g and uses the OracleAS Integration InterConnect Database Adapter to communicate with the system.
The legacy order fulfillment system operates on an Oracle8i database and uses the OracleAS Integration InterConnect Advanced Queuing Adapter to communicate with the system.
The Purchase Order table in this system has a database trigger to queue the changed records. OracleAS Integration InterConnect is configured to listen to that queue to accomplish the integration.
|
Note: There are many methods available to capture changes to a system. These methods include, but are not limited to, database triggers, interface tables, and database log files. |
Consider an organization that wishes to integrate its legacy system containing its purchase order tables with the new order fulfillment application running on Oracle Database 10g. illustrates this integration scenario:
The first step in any integration scenario is to model the integration.
Legacy System
Any change to the Purchase Order table in the legacy application is published using a database trigger. An administrator must approve all changes, such as insert, update, & delete before they are applied to the new order fulfillment System
Order Fulfillment System
If a change is approved, then it is sent to the order fulfillment system. If a change is rejected, then a cancellation notification is sent to the legacy system.
Additional Issues
The process must be non-intrusive. The user cannot alter the structure in 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.
illustrates how OracleAS Integration InterConnect integrates with the scenario in .
How are we going to accomplish this task?
Legacy Application publishes the insert, update, and delete messages to a queue. OracleAS Integration InterConnect Adapter for AQ (Advanced Queuing adapter) is used to send and receive messages to and from the queue.
The Order Fulfillment Application uses a standard Oracle database and the OracleAS Integration InterConnect Adapter for DB (Database adapter).
All messages are routed to Oracle Workflow to apply user-defined logic.
The following sections describe implementing the integration scenario using iStudio.
The source system uses Oracle Database 10g Advanced Queuing to publish changes to the purchase order table. 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 OracleAS Integration InterConnect Advanced Queuing Adapter is configured to listen on this queue.
The following is an example of the code 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;
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.
From the File menu, select New Project. The Create Project dialog box is displayed.
Enter PO_Integration in the Project Name field, and click OK. The Repository Information dialog box is displayed.
Enter the correct repository information, and click OK.
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 events or procedures that 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.
From the File menu, select New, then select Business Object. The Create Business Object dialog box is displayed.
Enter Purchase_Order in the Business Object Name field, and click OK.
In order to integrate data between two or more systems, a semantically compatible view, or common view, is required. In this scenario, the insert, update, 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 (Data Type Definition). The user can also use the database or other common data type to describe the structure of the event.
From the File menu, click New, and then select Event. The Create Event dialog box is displayed.
Select Purchase_Order as the Business Object.
Enter PO_Insert in the Event Name field.
Click Import, and select XML.
Select the predefined file, PO_Insert_CV.dtd in the Open dialog box, and click Open.
Select PO_Insert in the Select Root dialog box, and click OK to return to the Create Event dialog box.
Click OK.
Use similar 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 B-3 Completed Event Node in iStudio
Each event has its own XML DTD. The code for each event is shown.
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)>
An application in iStudio represents an instance of an adapter communicating with an application. When you install an adapter, a unique name is supplied, and in iStudio, this name is used as the name of the application. This section demonstrates creating the AQAPP and DBAPP applications.
The following steps describe creating the AQAPP application using iStudio.
From the File menu, select New, and then select Application. The Create Application dialog box is displayed.
Enter AQAPP in the Application Name field, and click OK.
Similarly, create the DBAPP application. The AQAPP and DBAPP applications appear in the Design Object Navigator under the Applications node as shown in .
Figure B-4 AQAPP and DBAPP Applications in iStudio
Each system has its own unique identifier or primary key. In most cases, an administrator does not allow any changes to the structure of the system. As a result, 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.
From the File menu, click New, and then select Cross Reference Tables. The Create Cross Reference Table dialog box is displayed.
Enter PO_XREF in the Cross Referenced Table Name field, and click Next.
Select WORKFLOW and DBAPP from the Available Components list and click the right arrow button.
Click Finish. The PO_XREF cross reference table is displayed in the Design Object Navigator under the Cross Reference Tables node as shown in .
Figure B-5 PO_XREF Cross Reference Table in iStudio
The database trigger in the Legacy Application, AQAPP, publishes messages when records are inserted, updated, or deleted in the purchase order table. This process happens outside the OracleAS Integration InterConnect environment. The OracleAS Integration InterConnect Advanced Queuing 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 OracleAS Integration InterConnect environment.
The following steps describe how the message received from the Legacy Application queue is processed.
Starting the Publish Wizard
To start the Publish Wizard:
Expand the Applications node in the Design Object Navigator.
Select and expand the AQAPP application.
Select the Published Events node.
Right-click Published Events, and select New. The Publish Wizard is displayed.
Using the Publish Wizard to Publish the PO_Insert Event
To publish the PO_Insert Event:
Select an Event Page:
Enter information in the following fields:
Application: Select AQAPP for the application.
Message Type: Select AQ for the message type.
Expand the Business Objects list in the Select an Event dialog box and navigate to PO_Insert.
Select PO_Insert and click Next.
Figure B-6 Publish Wizard - Select an Event page
Define Application View Page
Import Attributes
Import attributes from the common view by clicking Import and selecting Common View. The structure of the PO_Insert common view event is displayed. If the application view is different from the common view, then use the database or an XML DTD to define the structure.
Create an Event Map
An event is received and converted into a common view. This common view can be mapped by any application. 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 I for insert, U for update, or D for delete. Complete the following steps to create an event map:
Click Event Map, then click Add. The Add Event Map page is displayed.
Select the Action field, and enter I in the Value field.
Click OK in the Add Event Map page.
Click OK in the Event Map page.
Click Next.
Define Mapping Page
Use the Define Mapping page to map fields from the AQAPP View to the Common View by using transformations. In this scenario the structure is identical, as a result, the ObjectCopy transformation is used to map all the fields at once. To define new mappings:
Click DefineMappings. The Mapping Parameters dialog box is displayed.
Expand the PO_Insert list and select the PO_Insert node in the AQAPP View box.
Select ObjectCopy in the Transformations box.
Expand the PO_Insert list and select the PO_Insert node in the Common View box.
Click Add.
Click OK. The new mapping is displayed in the Define Mapping page.
Click Finish.
Figure B-7 Publish Wizard - Mapping Parameters
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.
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.
The following steps describe how the Order Fulfillment Application subscribes to messages.
Starting the Subscribe Wizard:
In the Design Object navigator, expand the Application node to display the Subscribed Events node.
Right-click Subscribed Events, and select New. The Subscribe Wizard is displayed as shown in .
Using the Subscribe Wizard to Subscribe to the PO_Insert Event
Select an Event Page
Enter information in the following fields:
Application: Select DBAPP.
Message Type: Select Database.
Expand the Business Objects node in the Select an Event dialog box and navigate to PO_Insert.
Select PO_Insert, and click Next.
Figure B-8 Subscribe Wizard - Select an Event page
Define Application View Page
Import attributes from the database.
Click Import, and select Database. The Database Login dialog box is displayed.
Enter the information to login to the database, and click Login. The Oracle Database Browser dialog box is displayed as shown in Figure B-9.
In the Browser dialog box, expand the Tables/Views node and select DBAPP.PO.
Click Done.
Figure B-9 Subscribe Wizard - Oracle Database Browser
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 box is displayed as shown in Figure B-10.
Select POID in the Application Returned Arguments For XRef box.
Select id in the Common View box.
Click Map.
Click OK.
Figure B-10 Subscribe Wizard - Cross Reference
Click Next.
Define Mapping Page
Define a new mapping:
Click Define Mappings. The Mapping Parameters dialog box is displayed as shown in Figure B-11.
Expand the PO_Insert list 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 B-11 Subscribe Wizard - Mapping Parameters
Click Next.
Define Stored Procedure Page
Select sub_PO_Insert_OAI_V1 from the SQL code list. The SQL code is displayed in the box.
Add the following code at end of the existing SQL 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;
Click Finish.
Create the Subscribed PO_Update Event
To create the subscribed PO_Update Event:
Select the PO_Update event.
Import the Application View.
Define Mapping Page
Map the same parameters as described in PO_Insert.
In addition, map the following:
Expand the PO_Update list and node in the Common View box and select id.
Select the LookupXref transformation.
Expand the PO_Update list and select POID in the Application View box.
Click Add.
Click OK.
Select the Req. checkbox for table listed in the Parameters column and click OK.
Click Next.
Define Stored Procedure Page
Select sub_PO_Update_OAI_V1 for the SQL code for field. The code is displayed in the box.
Add the following code at end of the existing SQL 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;
Click Finish.
Create the Subscribe PO_Delete Event
To create the subscribe PO_Delete Event:
Select the PO_Delete event.
Import the Application View.
Define Mapping Page
Map the following:
Expand the PO_Delete list and node in the Common View box and select id.
Select the DeleteXref transformation.
Expand the PO_Delete list and select POID.
Click Add.
Click OK.
Select PO_XREF from the values column and click OK.
Click Next.
Define Stored Procedure Page
Select sub_PO_Delete_OAI_V1 for the SQL code for field. The code is displayed in the box.
Add the following code at the end of the existing SQL 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;
Click Finish.
The AQAPP application subscribes to the PO_Cancel event.
Select an Event Page
Enter information in the following fields:
Application: Select AQAPP.
Message Type: Select AQ.
Select PO_Cancel and click Next.
Define Application View Page
Import attributes from the common view and click Next.
Define Mapping Page
Define a new mapping:
Click Define Mappings and map the following:
Id Copyfields Id
Click Add.
Click OK.
Click Finish.
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.
Create a Process Bundle
The following steps describe creating the PO process bundle using iStudio:
From the project list, expand the Workflow node and navigate to Process Bundle.
Right-click Process Bundles and select New. The Create Process Bundle dialog box is displayed.
Enter PO in the Process Bundle Name field and click OK.
Create a Business Process
The following steps describe creating the PO business process using iStudio:
Expand the Process Bundle node on the project list and navigate to Business Processes.
Right-click Business Processes and select New. The Create Business Process dialog box is displayed.
Enter PO in the Business Process Name field and click OK.
Create the Subscribe and Publish Activities
The Oracle Workflow business process uses the common view. As a result, transformation and mapping is not required and the only types of activities used are as follows:
Subscribe: Oracle Workflow receives a message from OracleAS Integration InterConnect.
Publish: Oracle Workflow sends a message to OracleAS Integration InterConnect.
Invoke: Oracle Workflow sends a request message to OracleAS Integration InterConnect and receives a reply.
Implement: Oracle Workflow receives a request from OracleAS Integration 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 sent to the Order Fulfillment Application or the PO_Cancel message is sent to the Legacy Application. Oracle Workflow must:
Subscribe to and publish PO_Insert.
Subscribe to and publish PO_Update.
Subscribe to and publish PO_Delete.
Publish PO_Cancel.
Create Subscribe Activity
The following steps describe creating the subscribe activity using iStudio:
From the Project list, expand the Workflow node and navigate to Business Processes.
Right-click PO business process and select Subscribe Activity.
Select Event PO_Insert and click Finish.
Repeat these steps for the PO_Update and PO_Delete events, substituting the correct values where necessary.
Create Publish Activity
The following steps describe creating the publish activity using iStudio:
From the Project list, expand the Workflow node and navigate to Business Processes.
Right-click PO business process and select Publish Activity. Right-clicking any item displays a pop-up box.
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 B-12 Subscribe and Publish Activities in iStudio
When an event is published, it is automatically routed to any event's subscriber, by default. If the routing of an event needs to be based on a value in the message or message header, then content-based routing is required in this scenario. All changes to the purchase orders must be approved and routed to Oracle Workflow to apply business logic.
The logic to be applied for the Events PO_Insert, PO_Update, and PO_Delete as follows:
If AQAPP is the source application, then route to the WORKFLOW destination application. The Wizard steps are as follows:
Source Page: Select OAI_Header.SendingApplication
Choose Operator Page: Select =
Choose Value Page: Enter AQAPP
Addition Condition Page: Select Radio Button Complete & press Finished
Destination Page: Select WORKFLOW
If WORKFLOW is the source application, then route to the DBAPP destination application. The Wizard steps are as follows:
Source Page: Select OAI_Header.SendingApplication
Select Operator Page: Select =
Choose Value Page: Enter WORKFLOW
Addition Condition Page: Select Radio Button Complete & press Finished
Destination Page: Select DBAPP
The procedure repeats for the PO_Update and PO_Delete events. Figure B-13 describes the completed content-based routing in iStudio.
Figure B-13 Completed Content Routing in iStudio
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.
The following steps describe deploying the process bundle to Oracle Workflow:
Right-click the Workflow node on the Deploy tab in iStudio and select Deploy. The Deploy dialog box is displayed.
Select Event Definitions to Workflow Business Event System, then Process Definitions for File in the Deploy to Workflow box.
Click OK. The Workflow BES Login dialog box is displayed.
Log in to Oracle Workflow using the correct user name, password, and URL. Click OK. The Deploy dialog box is displayed.
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 as shown in Figure B-14.
Figure B-14 Completed Deployment in Oracle Workflow
The original requirement for this scenario are as follows:
"An administrator must approve all changes such as insert, update, and 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 required 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.
A static list of values that can be referenced by various objects. 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.
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.
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.
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.
The following steps describe creating a new Oracle Workflow message called Insert_Message
In the Object Navigator right-click the Message Node and select New to launch the property sheet. In each tab, add the following entries:
Message Tab:
Internal Name: Insert_Message
Display Name: Insert Message
Description: Insert Message
Body Tab:
Subject: Insert Message
Text Body: A record has been Inserted in the Purchase Order Table.
Result Tab:
Display Name: Insert_Message
Description: Insert_Message
Lookup Type: Approval (From Lookup Type)
Click OK.
Using the default Copy and Paste functionality create the following messages using message Insert_Message as the template:
Update_Message: Repeats the preceding steps and use the same setting, changing all references to insert to update.
Delete_Message: Repeats the preceding steps and use the same setting, changing all references to insert to Delete.
The following steps describe creating a new Oracle Workflow Notification.
In the Object Navigator, right-click the Notification Node and select New to launch the property sheet. In each tab, add the following entries:
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)
Click OK.
Using the default Copy and Paste functionality create the following notifications using notification Insert_Notification as the template:
Update_Notification: Repeats the preceding steps and use the same setting, changing all references to insert to update.
Delete_Notification: Repeats the preceding steps and use the same setting, changing all references to insert to delete.
Figure B-15 Completed Oracle Workflow Notifications
Now that all of the required objects have been created, the business logic can be modeled. The following steps describe this process.
In the Oracle Workflow Object Navigator, expand the OAI Process Bundle: PO item type.
Expand the Processes node.
Right-click OAI Business Process: PO and select Process Details.
Another way to display the process details is to double-click OAI Business Process: PO.
Move the following from the Oracle Workflow Object Navigator to the Oracle Workflow Workspace:
Insert_Notification
Update_Notification
Delete_Notification
Rearrange the items as shown in Figure B-16.
Figure B-16 Items Arranged in Oracle Workflow Builder
The subscribe events are the entry point in this process. The Start and End Property for each event must be edited and set to START. Double-click an object to launch its property sheet. The Start and End property is under the Node tab.
Subscribe to the following events:
Purchase_Order.PO_Insert
Purchase_Order.PO_Update
Purchase_Order.PO_Delete
The publish events are the exit point from this process. The Start and End Property for each event must be edited and set to END. Double-click an object to launch its property sheet. The Start and End property is under the Node tab.
Publish to the following events:
Purchase_Order.PO_Insert
Purchase_Order.PO_Update
Purchase_Order.PO_Delete
Purchase_Order.PO_Cancel
The notifications must be assigned in order for a person to receive the notification in Oracle Workflow Monitor. The Performer value property should be set to SYSADMIN for each notification. Double-click an object to launch its property sheet. The Performer Value field is located under the Node tab.
Assign the following notifications:
Insert_Notification
Update_Notification
Delete_Notification
Mapping lines need to be drawn between the objects to define the process flow. Lines are drawn by right-clicking on an object and dragging to another object.
Draw a mapping line from Subscribe Purchase_Order. PO_Insert to Insert_Notification.
Draw a mapping line from Insert_Notification to Publish Purchase_Order.PO_Insert and select Approve from the list that will appear when the line is drawn.
Draw a mapping line from Insert_Notification to Publish Purchase_Order.PO_Cancel and select Reject from the list that will appear when the line is drawn.
Repeat steps for Update & Delete objects.
Save your work to the database. The completed business process in Oracle Workflow Builder is shown in Figure B-17.
Figure B-17 Completed Business Process in Oracle Workflow Builder
After modeling the business objects, it is time to deploy the OracleAS Integration InterConnect business objects. The following section describes the deployment process.
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 into a queue using Oracle Advanced Queuing. To communicate to and from the OracleAS Integration InterConnect environment, the adapter must be configured to send and receive on those external queues.
The following steps describe this task.
On the Deploy tab in iStudio, expand the Applications list and navigate to AQAPP.
Expand the AQAPP node and navigate to the Routing node.
Expand the Routing node and select Application Queues. The Application Queues property sheet is displayed in the iStudio window.
Select Edit from the Edit menu. The Edit Application Queue dialog box is displayed as shown in Figure B-18.
Figure B-18 Application Queues in iStudio
Add the Queue name to each event:
| Queue Name | Event |
|---|---|
| INBOUND_QUEUE | PO_Cancel |
| OUTBOUND_QUEUE | PO_Insert, PO_Update, and PO_Delete |
Click OK.
Each adapter has different cache settings to minimize communication to the repository and to improve performance. If you have updated the metadata, you must synchronize the adapter and repository metadata. The following steps describe this task:
Select File from the menu, then select Sync Adapters. The Sync Adapters dialog box is displayed.
Select the applications to which to sync adapters, and click OK.
Depending on the adapter type, there may be 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 box in iStudio.
In the iStudio window, click the Deploy tab. Right-click Applications and select Export PL/SQL. The Export Application dialog box is displayed.
Select the applications to export code.
Enter the file prefix in the File Prefix field and click OK.
The resulting text file is a SQL*Plus script that is run on the target schema.
Example B-1 Exporting and Installing Code
The following example helps to explain exporting and installing code. 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;
The final step is to test the integration.
A record is inserted into the Legacy System.
The legacy system's database trigger queues the record in its OUTBOUND_QUEUE.
OracleAS Integration InterConnect receives the message, performs transformations, converts data to a common view, and routes the message to Oracle Workflow.
Oracle Workflow applies the business logic and issues a notification.
The System Administrator logs on to Oracle Workflow Monitor, receives the Insert_notification, and approves the record.
OracleAS Integration 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.
The deployed code receives the message and inserts the record into the Order Fulfillment system.
Oracle InterConnect Manager is used to examine the inserted record and monitor the integration throughput.
This process should be repeated for Update and Delete.
