How to Use Federation

Brian Pye — Thu, 14 May 2009 04:54:04 GMT

Current revision posted to Aras Innovator KnowledgeBase by Brian Pye on 5/14/2009 12:54:04 AM

How to Use Federation

About Federated Data in Innovator

Innovator uses the term “Federated” to mean data shared between different information systems.

Virtually all businesses are using multiple information systems to address different aspects of their business, these are usually at different stages in their lifecycle.

Many business processes need to draw on information from multiple systems in the most cost effective way.

Aras Innovator has a Service Oriented Architecture and is a Framework; it lends itself to addressing such needs.

This document describes how the Innovator Framework can gather and present Federated Data, and how it can provide data to other systems.

<o:p> </o:p>The Innovator Framework includes

‘Federated’ data-type for Properties
‘Federated’ Implementation type for ItemTypes.
‘Server Events’ which call .Net Methods when Items are selected, added, updated or deleted to implement the Interface.

The following table shows how different technologies can used as the interface to implement Federated Data where Innovator is the destination or source.

<o:p>

Interface	Innovator as Destination	Innovator as Source
Web Services	Methods consume WebService and show data in the Innovator UI transparently	Innovator 9.1 “Web Service Configuration” can create Web Services automatically. They can be consumed by remote applications
SQL	Innovator Methods or SQL Items connect to remote database using SQL Server Linked Server or ADO.Net	Remote database connects to Innovator database using SQL Server Linked Server, ADO.Net, ODBC or JDBC
CAD Integration	CAD integrations are available at Aras.com/Projects for two-way sharing of data with popular CAD programs
<st1:place w:st="on"><st1:city w:st="on">Enterprise</st1:city></st1:place> Service Bus	Methods put jobs onto the job queue	Innovator responds to requests from the job queue
API	Innovator Methods call remote system API	Remote system can use Innovator Object Model (IOM) API using COM or .Net
Other	Methods can do anything .Net can do

</o:p>

Federated data-type

When a Property is of data-type ‘Federated’ it still appears on forms and in grids, just like every other Property, but no column is created in the database and values for the Property will ignored in Innovator unless means of providing and acting upon values are created.

The simplest way to provide a value for a Federated Property is in an onAfterGet Server Event. This onAfterGet Server Event says “after you have done what you normally do, execute this Method”. The Method code can get a value in any way it chooses and return it in the context Item for further processing. For a get request this means the value will be stored in the client cache and appear in any Forms or Grids just like all the other properties.

When the value is returned to the server by a save or other code it will be ignored, unless means are provided to act on it.

Note there is also a Foreign data-type which serves a different purpose. Foreign Properties have no column in the database either, but values are provided from Properties of type Item in the same ItemType.

Federated ItemType

ItemTypes can be of Implementation ‘Table’, ‘Federated’ or ‘Polymorphic’.

· Table is the ordinary implementation used by most ItemTypes.

· Federated means that at least some of the values of the Properties for the Item will come from a source other than the Innovator database.

· Polymorphic is used for PolyItems like Deliverable in the Program Management Solution and Affected Item in the Product Engineering Solution

Selecting Federated Implementation type does not actually change the behavior of an ItemType, the behavior needs to be implemented by Server Events, so Federated Implementation is more of a flag than a switch. (Note this is different for Polymorphic, selecting Polymorphic determines the behavior of the ItemType when it is created and you cannot change Polymorphic to or from other implementation types.)

Federated behavior for an ItemType is provided by using onAdd, onGet, onUpdate and onDelete Server Events. ‘on’ Events are distinct from ‘onBefore’ and ‘onAfter’ events.

· ‘onBefore’ events say “before you do what you normally do, do this”

· ‘onAfter’ events say “after you do what you normally do, do this”

· ‘on’ evens say “instead of doing what you normally do, do this”

If no onAdd, onGet, onUpdate and onDelete Server Events are created for a Federated Implementation, the Item will behave like an ordinary Table Implementation.

If onAdd, onGet, onUpdate and onDelete Server Events are created with Method code that does nothing, the ItemType will exist but there will be no instances and no behavior.

The simplest Federated Item would have an onGet Server Event which would provide values whenever a get request was made. There would be no data in the Innovator database for the ItemType but values would be returned by the Innovator Server and displayed in the Innovator Client . The values would be determined by the Method code. This would be appropriate for the case where it was required to simply show information from a remote data source in Innovator.

For more complex requirements where information about an item may be shared between Innovator and remote data sources, a stub Item may be stored in the Innovator database including a unique key to the remote data and any data managed in Innovator, other data would be retrieved from the remote data source when needed. In this case a combination of onAdd, onGet, onUpdate and onDelete Server Events can be designed to create the behavior required. In order to do the required behavior is must be clearly defined.

When any instance data is added to the Innovator database, system properties such as id, created_on, created_by, state etc are created automatically. When this occurs the Item will be able to have any and all standard Innovator behaviors including Permissions, History, Relationships, Lifecycle and Workflow. These behaviors can augment the data stored in remote systems.

Choosing on…, onBefore… or onAfter… for Server Events

The Event for Sever Events needs to be chosen by considering

a) the intended behavior

b) how Innovator works

For the example:

CI Inventory

· onGet is used because we want to replace standard Innovator behavior

CI Order

· onAfterGet is used because we want to get data from the Innovator database then use it to get data from the remote system

· onAfterAdd, onAfterUpdate is used because we want Innovator to do whqt it normally does, onBefore would probably work as well

· onBeforeDelete is used because we won’t be able to get data we need after we delete it

Standard Work for Implementing Federated Data in Innovator

1. Determine the sources and desired behavior for data to be federated. It helps to document this in a Use Case.

2. Design the Data Model identifying ItemTypes, Relationships, Properties indicating which will be federated and which stored in Innovator.

3. Select and test the interface type to be used.

4. Build the Data Model

5. Implement Server Events

6. Test the application

Example Use Case

1. Use case summary

a. In the simple Retail Catalog application used in Admin, Workflow and API training a Retail Catalog has a relationship to Catalog Items, we will add the following Federated Data

b. Catalog Items will have two relationships CI Inventory and CI Order

c. CI Inventory represents the inventory on hand at each Catalog Item at several warehouses

d. CI Order represents orders to replenish inventory for a single Catalog Item at a single warehouse

e. CI Inventory data will be provided by a remote system, no data will reside in Innovator but inventory records will be shown in the Catalog Item relationship grid.

f. Catalog Item will have a property indicating the quantity in stock which will be retrieved from data on the remote system calculated as sum of quantity on hand at all warehouses.

g. CI Order data will be shared between Innovator and the remote system and will shown in the Catalog Item relationship grid.

h. CI Order records may be added in either Innovator or the remote system, the remote system alone will control the quantity received.

i. A Lifecycle will be implemented in Innovator for CI Orders. Orders with qty received less than qty ordered will be in the <st1:place w:st="on"><st1:placename w:st="on">Open</st1:placename> <st1:placetype w:st="on">State</st1:placetype></st1:place> and other orders will be in the Closed state

2. Innovator Items

a. ItemType Retail Catalog

i. Implementation: Table

b. ItemType Catalog Item

i. Implementation: Table

c. RelationshipType Retail Catalog Item

i. Source: Retail Catalog

ii. Related: Catalog Item

iii. Relationship ItemType Implementation: Table

d. RelationshipType CI Inventory

i. Source: Catalog Item

ii. No related

iii. Relationship ItemType Implementation: Federated, no data in Innovator

iv. ServerEvent on get to implement business rules

e. RelationshipType CI Order

i. Source: Catalog Item

ii. Relationship ItemType Implementation: Federated, stub data in Innovator

iii. No related

iv. Lifecyle Open and Closed, World can promote

v. ServerEvents on get, add, update and delete to implement business rules

3. Business rules

a. For Catalog Item show sum of quantity on hand at all warehouses

b. For Catalog Inventory show warehouse and qty on hand

c. For Catalog Item Order show warehouse, qty on order, qty received

a. Since Catalog Item Order has data shared between Innovator and the remote system we need to define where information can be added changed and deleted in these rules. You may choose different rules according the business need.

b. CI Orders will only be displayed in the relationship grid of Catalog Items, ie where the source_id is known.

c. When a new CI Order is added to Innovator and it does not exist on the remote system, it will be added to both systems

d. When a CI Order order quantity is edited in Innovator it will also be updated on the remote system

e. When a CI Order is deleted in Innovator, it will be deleted from the remote system also

f. An action may be run in Innovator which will synchronize data between Innovator and the remote system

i. An CI Orders on the remote system but not in Innovator will be added to Innovator

ii. Any CI Orders not on the remote system but in Innovator will be deleted from Innovator

iii. All CI Orders in Innovator will be promoted to the appropriate state

4. User Interface

a. No special requirements

IMPLEMENTATION OF EXAMPLE

A SQL interface will be used.

For simplicity ‘remote data’ will be in tables not owned by innovator. Data might just as easily be in a linked server, which can be referenced directly using sql in the Innovator data server

Remote table definitions

CREATE TABLE [dbo].[fed_ci_inventory](

[wh] [varchar](1) NULL,

[item_number] [nvarchar](7) NULL,

[qty_oh] [int] NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[fed_ci_order](

[ordno] [int] NULL,

[wh] [char](1) NULL,

[item_number] [varchar](8) NULL,

[qty_ord] [int] NULL,

[qty_rcd] [int] NULL

) ON [PRIMARY]

CI Inventory, ServerEvent, onGet

// get the item_number, it's not passed automatically

Item qry0 = this.newItem("Catalog Item");

qry0.setAttribute("select","item_number");

qry0.setID(this.getProperty("source_id"));

Item res=qry0.apply("get");

if (res.isError()) return res;

// get federated data

// we use a SQL Item here rather than applySQL(...)

// because SQL PROCESS conveniently takes

// id and type columns from the result set

// and turns them into attributes of the returned Items

Item qry1 = this.newItem("SQL","SQL PROCESS");

qry1.setProperty("name","select_ci_inventory");

qry1.setProperty("PROCESS","CALL");

qry1.setProperty("ARG1", res.getProperty("item_number"));

res = qry1.apply();

return res

SQL Item, SELECT_CI_INVENTORY

create procedure select_ci_inventory (@ci varchar(8))

BEGIN

select replace(NewID(),'-','') id, 'CI Inventory' type, *

from dbo.fed_ci_inventory

where item_number=@ci

END

CI Order, ServerEvent, onAfterGet

// onAfterGet may return a single Item for example to populate an Item Window

// or a collection of Items for example to populate the Main Grid

// so we loop through the context Item

for (int i=0;i<this.getItemCount();i++){

Item ci = this.getItemByIndex(i);

// get data for the key to the remote system

string ordno =ci.getProperty("ordno");

string wh=ci.getProperty("wh");

string item_number=ci.getProperty("item_number");

if (ordno==null||wh==null||item_number==null){

// the context Item might only contain the id

// in which case we'll get them from the server

Item ci2= this.newItem("CI Order");

ci2.setID(this.getItemByIndex(i).getID());

ci2.setAttribute("select","ordno,wh,item_number");

ci2=ci2.apply("get");

if (ci2.isError()) return ci;

ordno =ci2.getProperty("ordno");

wh=ci2.getProperty("wh");

item_number=ci2.getProperty("item_number");

}

// retrieve data from the remote system

// in this case we’ll use applySQL(…)

// all the code is in the method, no separate SQL Item

// in C# the @”…” indicates a string literal

// so the string can include linebreaks

// and String.Format(…) makess substitutions

string sql=@"

select qty_rcd from dbo.fed_ci_order

where ordno={0} and wh={1} and item_number='{2}'";

sql=String.Format(sql,ordno,wh,item_number);

Item res =this.getInnovator().applySQL(sql);

if (res.isError()) return res;

// update context Item with retrieved data

if (res.getResult()!=""){

string qty_rcd = res.getProperty("qty_rcd","0");

ci.setProperty("qty_rcd",qty_rcd);

}

// pass updated contextItem back to Innovator

return this;

CI Order, Server Event, onAfterAdd

<o:p> </o:p>

// get data for key to remote system from context Item

string ordno=this.getProperty("ordno");

string wh=this.getProperty("wh");

string item_number=this.getProperty("item_number");

string qty_ord=this.getProperty("qty_ord");

// add a new row to the remote system

string sql=@"

insert into dbo.fed_ci_order select '{0}','{1}','{2}','{3}', null

where not exists

(select * from dbo.fed_ci_order

where ordno={0} and wh={1} and item_number='{2}')

sql=String.Format(sql,ordno,wh,item_number,qty_ord);

Item result=this.getInnovator().applySQL(sql);

// no further processing in Innovator

// but c# must return something, if it's an error the add will roll back

CI Order, Server Event, onAfterUpdate

// get data for key to remote system from context Item

string ordno=this.getProperty("ordno");

string wh=this.getProperty("wh");

string item_number=this.getProperty("item_number");

string qty_ord=this.getProperty("qty_ord");

// update remote system

string sql=@"

update dbo.fed_ci_order

set qty_ord={3}

where ordno={0} and wh={1} and item_number='{2}'

sql=String.Format(sql,ordno,wh,item_number,qty_ord);

Item result=this.getInnovator().applySQL(sql);

// no further processing in Innovator

// but c# must return something, if it's an error the update will roll back

return result;

CI Order, Server Event, onBeforeDelete

<o:p> </o:p>

// delete server event passes only id in the context Item

// get data for key in remote system

Item o=this.newItem("CI Order");

o.setID(this.getID());

o=o.apply("get");

if (o.isError()) return o;

string ordno=o.getProperty("ordno");

string wh=o.getProperty("wh");

string item_number=o.getProperty("item_number");

// delete row from remote system

string sql=@"

delete from dbo.fed_ci_order

where ordno={0} and wh={1} and item_number='{2}'";

sql=String.Format(sql,ordno,wh,item_number);

Item result=this.getInnovator().applySQL(sql);

if (result.isError()) return result;

// return context Item to continue processing in Innovator

return this;

Retail Catalog, Action, Sync Orders

<o:p> </o:p>

Innovator innov=this.newInnovator();

// find CI Orders on remote system not in Innovator, and add them

string sql= @"

select distinct f.ordno, f.wh, ci.item_number, ci.id as source_id

from dbo.fed_ci_order f

left outer join innovator.ci_order i

on f.ordno= i.ordno and f.wh=i.wh

inner join innovator.catalog_item ci

on ci.item_number= f.item_number

where i.ordno is null";

Item adds =innov.applySQL(sql);

if (adds.isError()) {return adds;}

// if the resultset is empty applySQL returns an empty Result

if (adds.getResult()!=""){

// applySQL returns a resultset which looks like AML

// but we need to add 'type' and 'action' attributes

// we'll build an aml string containing a collection of Items that can be applied once

string aml="<AML>";

for (int i=0; i<adds.getItemCount();i++){

Item a=adds.getItemByIndex(i);

a.setType("CI Order");

a.setAction("add");

// turn add server event off

// we don't want the onAfterAdd event to run!

a.setAttribute("serverEvents","0");

aml+=a.node.OuterXml;

}

aml+="</AML>";

// add the CI Orders to Innovator

adds=innov.applyAML(aml);

if (adds.isError()) {return adds;}

}

// find CI Orders in innovator not on remote system and delete them

sql= @"

delete from innovator.ci_order where id in

(select i.id

from innovator.ci_order i

right outer join dbo.fed_ci_order f

on f.ordno= i.ordno and f.wh=i.wh

inner join innovator.catalog_item ci

on i.source_id = ci.id and f.item_number=ci.item_number

where f.ordno is null)";

Item dels =innov.applySQL(sql);

if (dels.isError()) {return dels;}

// find CI Orders

Item pr_opn = this.newItem("CI Order");

pr_opn.setAttribute("select","qty_ord,qty_rcd,state");

pr_opn=pr_opn.apply("get");

if (pr_opn.isError()&&(!pr_opn.isEmpty())) {return pr_opn;}

// and promote them if necessary

for (int i=0;i<pr_opn.getItemCount();i++){

Item o = pr_opn.getItemByIndex(i);

if ((Int16.Parse(o.getProperty("qty_rcd","0")) <

Int16.Parse(o.getProperty("qty_ord","0"))) &&

o.getProperty("state")=="Closed"){

Item res =pr_opn.getItemByIndex(i).promote("Open","");

if (res.isError()) return res;

}

if ((Int16.Parse(o.getProperty("qty_rcd","0")) >=

Int16.Parse(o.getProperty("qty_ord","0"))) &&

o.getProperty("state")=="Open"){

Item res =pr_opn.getItemByIndex(i).promote("Closed","");

if (res.isError()) return res;

}

return innov.newResult("OK");

<o:p> </o:p>

How to Use Federation

Peter Schroer — Fri, 27 Mar 2009 17:28:47 GMT

Revision 3 posted to Aras Innovator KnowledgeBase by Peter Schroer on 3/27/2009 1:28:47 PM

How to Use Federation

About Federated Data in Innovator

Innovator uses the term “Federated” to mean data shared between different information systems.

Virtually all businesses are using multiple information systems to address different aspects of their business, these are usually at different stages in their lifecycle.

Many business processes need to draw on information from multiple systems in the most cost effective way.

Aras Innovator has a Service Oriented Architecture and is a Framework; it lends itself to addressing such needs.

This document describes how the Innovator Framework can gather and present Federated Data, and how it can provide data to other systems.

<o:p> </o:p>The Innovator Framework includes

‘Federated’ data-type for Properties
‘Federated’ Implementation type for ItemTypes.
‘Server Events’ which call .Net Methods when Items are selected, added, updated or deleted to implement the Interface.

The following table shows how different technologies can used as the interface to implement Federated Data where Innovator is the destination or source.

<o:p>

Interface	Innovator as Destination	Innovator as Source
Web Services	Methods consume WebService and show data in the Innovator UI transparently	Innovator 9.1 “Web Service Configuration” can create Web Services automatically. They can be consumed by remote applications
SQL	Innovator Methods or SQL Items connect to remote database using SQL Server Linked Server or ADO.Net	Remote database connects to Innovator database using SQL Server Linked Server, ADO.Net, ODBC or JDBC
CAD Integration	CAD integrations are available at Aras.com/Projects for two-way sharing of data with popular CAD programs
<st1:place w:st="on"><st1:city w:st="on">Enterprise</st1:city></st1:place> Service Bus	Methods put jobs onto the job queue	Innovator responds to requests from the job queue
API	Innovator Methods call remote system API	Remote system can use Innovator Object Model (IOM) API using COM or .Net
Other	Methods can do anything .Net can do

</o:p>

Federated data-type

<o:p> </o:p>

The simplest way to provide a value for a Federated Property is in an onAfterGet Server Event. This onAfterGet Server Event says “after you have done you normally do, execute this Method”. The Method code can get a value in any way it chooses and return it in the context Item for further processing. For a get request this means the value will be stored in the client cache and appear in any Forms or Grids just like all the other properties.<o:p></o:p>

<o:p> </o:p>

When the value is returned to the server by a save or other code it will be ignored, unless means are provided to act on it.<o:p></o:p>

<o:p> </o:p>

Federated ItemType

<o:p> </o:p>

ItemTypes can be of Implementation ‘Table’, ‘Federated’ or ‘Polymorphic’.<o:p></o:p>‘Polymorphic’.

· Table is the ordinary implementation used by most ItemTypes.<o:p></o:p>

· Federated means that at least some of the values of the Properties for the Item will come from a source other than the Innovator database.<o:p></o:p>

· Polymorphic is used for PolyItems like Deliverable in the Program Management Solution and Affected Item in the Product Engineering Solution<o:p></o:p>

<o:p> </o:p>

Federated behavior for an ItemType is provided by using onAdd, onGet, onUpdate and onDelete Server Events. ‘on’ Events are distinct from ‘onBefore’ and ‘onAfter’ events. <o:p></o:p>

· ‘onBefore’ events say “before you do what you normally do, do this”<o:p></o:p>”

· ‘onAfter’ events say “after you do what you normally do, do this”<o:p></o:p>”

· ‘on’ evens say “instead of doing what you normally do, do this”<o:p></o:p>

<o:p> </o:p>”

If no onAdd, onGet, onUpdate and onDelete Server Events are created for a Federated Implementation, the Item will behave like an ordinary Table Implementation.<o:p></o:p>

<o:p> </o:p>

If onAdd, onGet, onUpdate and onDelete Server Events are created with Method code that does nothing, the ItemType will exist but there will be no instances and no behavior.<o:p></o:p>

<o:p> </o:p>

Choosing on…, onBefore… or onAfter… for Server Events

<o:p> </o:p>

The Event for Sever Events needs to be chosen by considering<o:p></o:p>

a) the intended behavior<o:p></o:p>

b) how Innovator works<o:p></o:p>

<o:p> </o:p>

For the example:<o:p></o:p>

<o:p> </o:p>

CI Inventory<o:p></o:p>

· onGet is used because we want to replace standard Innovator behavior<o:p></o:p>

<o:p> </o:p>

CI Order<o:p></o:p>

· onAfterGet is used because we want to get data from the Innovator database then use it to get data from the remote system<o:p></o:p>

· onAfterAdd, onAfterUpdate is used because we want Innovator to do whqt it normally does, onBefore would probably work as well<o:p></o:p>

· onBeforeDelete is used because we won’t be able to get data we need after we delete it<o:p></o:p>

Standard Work for Implementing Federated Data in Innovator

<o:p> </o:p>

1. Determine the sources and desired behavior for data to be federated. It helps to document this in a Use Case.<o:p></o:p>

2. Design the Data Model identifying ItemTypes, Relationships, Properties indicating which will be federated and which stored in Innovator.<o:p></o:p>

3. Select and test the interface type to be used.<o:p></o:p>

4. Build the Data Model<o:p></o:p>

5. Implement Server Events<o:p></o:p>

6. Test the application<o:p></o:p>

<o:p> </o:p>

<o:p></o:p>

Example Use Case

<o:p> </o:p>

1. Use case summary<o:p></o:p>

a. In the simple Retail Catalog application used in Admin, Workflow and API training a Retail Catalog has a relationship to Catalog Items, we will add the following Federated Data<o:p></o:p>

b. Catalog Items will have two relationships CI Inventory and CI Order<o:p></o:p>

c. CI Inventory represents the inventory on hand at each Catalog Item at several warehouses<o:p></o:p>

d. CI Order represents orders to replenish inventory for a single Catalog Item at a single warehouse<o:p></o:p>

e. CI Inventory data will be provided by a remote system, no data will reside in Innovator but inventory records will be shown in the Catalog Item relationship grid.<o:p></o:p>

f. Catalog Item will have a property indicating the quantity in stock which will be retrieved from data on the remote system calculated as sum of quantity on hand at all warehouses.<o:p></o:p>

g. CI Order data will be shared between Innovator and the remote system and will shown in the Catalog Item relationship grid.<o:p></o:p>

h. CI Order records may be added in either Innovator or the remote system, the remote system alone will control the quantity received.<o:p></o:p>

<o:p> </o:p>

2. Innovator Items<o:p></o:p>

a. ItemType Retail Catalog<o:p></o:p>

i. Implementation: Table<o:p></o:p>

b. ItemType Catalog Item<o:p></o:p>

i. Implementation: Table<o:p></o:p>

c. RelationshipType Retail Catalog Item<o:p></o:p>

i. Source: Retail Catalog<o:p></o:p>

ii. Related: Catalog Item<o:p></o:p>

iii. Relationship ItemType Implementation: Table<o:p></o:p>

d. RelationshipType CI Inventory<o:p></o:p>

i. Source: Catalog Item<o:p></o:p>

ii. No related<o:p></o:p>

iii. Relationship ItemType Implementation: Federated, no data in Innovator<o:p></o:p>

iv. ServerEvent on get to implement business rules<o:p></o:p>

e. RelationshipType CI Order<o:p></o:p>

i. Source: Catalog Item<o:p></o:p>

ii. Relationship ItemType Implementation: Federated, stub data in Innovator<o:p></o:p>

iii. No related<o:p></o:p>

iv. Lifecyle Open and Closed, World can promote<o:p></o:p>

v. ServerEvents on get, add, update and delete to implement business rules<o:p></o:p>

<o:p> </o:p>

3. Business rules<o:p></o:p>

a. For Catalog Item show sum of quantity on hand at all warehouses<o:p></o:p>

b. For Catalog Inventory show warehouse and qty on hand<o:p></o:p>

c. For Catalog Item Order show warehouse, qty on order, qty received<o:p></o:p>

b. CI Orders will only be displayed in the relationship grid of Catalog Items, ie where the source_id is known.<o:p></o:p>

c. When a new CI Order is added to Innovator and it does not exist on the remote system, it will be added to both systems<o:p></o:p>

d. When a CI Order order quantity is edited in Innovator it will also be updated on the remote system<o:p></o:p>

e. When a CI Order is deleted in Innovator, it will be deleted from the remote system also<o:p></o:p>

f. An action may be run in Innovator which will synchronize data between Innovator and the remote system<o:p></o:p>

i. An CI Orders on the remote system but not in Innovator will be added to Innovator<o:p></o:p>

ii. Any CI Orders not on the remote system but in Innovator will be deleted from Innovator<o:p></o:p>

iii. All CI Orders in Innovator will be promoted to the appropriate state<o:p></o:p>

4. User Interface<o:p></o:p>

a. No special requirements<o:p></o:p>

<o:p> </o:p>

IMPLEMENTATION OF EXAMPLE

<o:p> </o:p>

A SQL interface will be used.<o:p></o:p>

<o:p> </o:p>

For simplicity ‘remote data’ will be in tables not owned by innovator. Data might just as easily be in a linked server, which can be referenced directly using sql in the Innovator data server<o:p></o:p>

Remote table definitions

<o:p> </o:p>

CREATE TABLE [dbo].[fed_ci_inventory](<o:p></o:p>

[wh] [varchar](1) NULL,<o:p></o:p>

[item_number] [nvarchar](7) NULL,<o:p></o:p>

[qty_oh] [int] NULL<o:p></o:p>

) ON [PRIMARY]<o:p></o:p>

<o:p> </o:p>

CREATE TABLE [dbo].[fed_ci_order](<o:p></o:p>

[ordno] [int] NULL,<o:p></o:p>

[wh] [char](1) NULL,<o:p></o:p>

[item_number] [varchar](8) NULL,<o:p></o:p>

[qty_ord] [int] NULL,<o:p></o:p>

[qty_rcd] [int] NULL<o:p></o:p>

) ON [PRIMARY]<o:p></o:p>

CI Inventory, ServerEvent, onGet

// get the item_number, it's not passed automatically<o:p></o:p>

Item qry0 = this.newItem("Catalog Item");<o:p></o:p>

qry0.setAttribute("select","item_number");<o:p></o:p>

qry0.setID(this.getProperty("source_id"));<o:p></o:p>

Item res=qry0.apply("get");<o:p></o:p>

if (res.isError()) return res;<o:p></o:p>

// get federated data<o:p></o:p>

// we use a SQL Item here rather than applySQL(...)<o:p></o:p>

// because SQL PROCESS conveniently takes<o:p></o:p>

// id and type columns from the result set<o:p></o:p>

// and turns them into attributes of the returned Items<o:p></o:p>

Item qry1 = this.newItem("SQL","SQL PROCESS");<o:p></o:p>

qry1.setProperty("name","select_ci_inventory");<o:p></o:p>

qry1.setProperty("PROCESS","CALL");<o:p></o:p>

qry1.setProperty("ARG1", res.getProperty("item_number"));<o:p></o:p>

res = qry1.apply();<o:p></o:p>

return res<o:p></o:p>

SQL Item, SELECT_CI_INVENTORY

create procedure select_ci_inventory (@ci varchar(8))<o:p></o:p>

AS<o:p></o:p>

BEGIN<o:p></o:p>

select replace(NewID(),'-','') id, 'CI Inventory' type, *<o:p></o:p>

from dbo.fed_ci_inventory<o:p></o:p>

where item_number=@ci<o:p></o:p>

END<o:p></o:p>

<o:p> </o:p>

CI Order, ServerEvent, onAfterGet

<o:p> </o:p>

// onAfterGet may return a single Item for example to populate an Item Window <o:p></o:p>

// or a collection of Items for example to populate the Main Grid<o:p></o:p>

// so we loop through the context Item<o:p></o:p>

for (int i=0;i<this.getItemCount();i++){<o:p></o:p>

Item ci = this.getItemByIndex(i);<o:p></o:p>

// get data for the key to the remote system<o:p></o:p>

string ordno =ci.getProperty("ordno");<o:p></o:p>

string wh=ci.getProperty("wh");<o:p></o:p>

string item_number=ci.getProperty("item_number");<o:p></o:p>

if (ordno==null||wh==null||item_number==null){<o:p></o:p>

// the context Item might only contain the id<o:p></o:p>

// in which case we'll get them from the server<o:p></o:p>

Item ci2= this.newItem("CI Order");<o:p></o:p>

ci2.setID(this.getItemByIndex(i).getID());<o:p></o:p>

ci2.setAttribute("select","ordno,wh,item_number");<o:p></o:p>

ci2=ci2.apply("get");<o:p></o:p>

if (ci2.isError()) return ci;<o:p></o:p>

ordno =ci2.getProperty("ordno");<o:p></o:p>

wh=ci2.getProperty("wh");<o:p></o:p>

item_number=ci2.getProperty("item_number"); <o:p></o:p>

}<o:p></o:p>

// retrieve data from the remote system<o:p></o:p>

// in this case we’ll use applySQL(…)<o:p></o:p>…)

// all the code is in the method, no separate SQL Item<o:p></o:p>

// in C# the @”…” indicates a string literal<o:p></o:p>

// so the string can include linebreaks<o:p></o:p>

// and String.Format(…) makess substitutions<o:p></o:p>

string sql=@"<o:p></o:p>"

select qty_rcd from dbo.fed_ci_order <o:p></o:p>

where ordno={0} and wh={1} and item_number='{2}'";<o:p></o:p>";

sql=String.Format(sql,ordno,wh,item_number);<o:p></o:p>

Item res =this.getInnovator().applySQL(sql);<o:p></o:p>

if (res.isError()) return res;<o:p></o:p>

// update context Item with retrieved data<o:p></o:p>

if (res.getResult()!=""){<o:p></o:p>

string qty_rcd = res.getProperty("qty_rcd","0");<o:p></o:p>

ci.setProperty("qty_rcd",qty_rcd);<o:p></o:p>

}<o:p></o:p>

// pass updated contextItem back to Innovator<o:p></o:p>

return this;<o:p></o:p>

<o:p> </o:p>

CI Order, Server Event, onAfterAdd

<o:p> </o:p>

// get data for key to remote system from context Item<o:p></o:p>

string ordno=this.getProperty("ordno");<o:p></o:p>

string wh=this.getProperty("wh");<o:p></o:p>

string item_number=this.getProperty("item_number");<o:p></o:p>

string qty_ord=this.getProperty("qty_ord");<o:p></o:p>

<o:p> </o:p>

// add a new row to the remote system<o:p></o:p>

string sql=@"<o:p></o:p>"

insert into dbo.fed_ci_order select '{0}','{1}','{2}','{3}', null<o:p></o:p>

where not exists<o:p></o:p>

(select * from dbo.fed_ci_order<o:p></o:p>

where ordno={0} and wh={1} and item_number='{2}')<o:p></o:p>

";<o:p></o:p>

sql=String.Format(sql,ordno,wh,item_number,qty_ord);<o:p></o:p>

Item result=this.getInnovator().applySQL(sql);<o:p></o:p>

// no further processing in Innovator<o:p></o:p>

// but c# must return something, if it's an error the add will roll back<o:p></o:p>

CI Order, Server Event, onAfterUpdate<o:p></o:p>

// get data for key to remote system from context Item<o:p></o:p>

string ordno=this.getProperty("ordno");<o:p></o:p>

string wh=this.getProperty("wh");<o:p></o:p>

string item_number=this.getProperty("item_number");<o:p></o:p>

string qty_ord=this.getProperty("qty_ord");<o:p></o:p>

// update remote system<o:p></o:p>

string sql=@"<o:p></o:p>"

update dbo.fed_ci_order<o:p></o:p>

set qty_ord={3}<o:p></o:p>

where ordno={0} and wh={1} and item_number='{2}'<o:p></o:p>

";<o:p></o:p>

sql=String.Format(sql,ordno,wh,item_number,qty_ord);<o:p></o:p>

Item result=this.getInnovator().applySQL(sql);<o:p></o:p>

<o:p> </o:p>

// no further processing in Innovator<o:p></o:p>

// but c# must return something, if it's an error the update will roll back<o:p></o:p>

return result;<o:p></o:p>

CI Order, Server Event, onBeforeDelete

<o:p> </o:p>

// delete server event passes only id in the context Item<o:p></o:p>

// get data for key in remote system<o:p></o:p>

Item o=this.newItem("CI Order");<o:p></o:p>

o.setID(this.getID());<o:p></o:p>

o=o.apply("get");<o:p></o:p>

if (o.isError()) return o;<o:p></o:p>

<o:p> </o:p>

string ordno=o.getProperty("ordno");<o:p></o:p>

string wh=o.getProperty("wh");<o:p></o:p>

string item_number=o.getProperty("item_number");<o:p></o:p>

<o:p> </o:p>

// delete row from remote system<o:p></o:p>

string sql=@"<o:p></o:p>"

delete from dbo.fed_ci_order<o:p></o:p>

where ordno={0} and wh={1} and item_number='{2}'";<o:p></o:p>";

sql=String.Format(sql,ordno,wh,item_number);<o:p></o:p>

<o:p> </o:p>

Item result=this.getInnovator().applySQL(sql);<o:p></o:p>

if (result.isError()) return result;<o:p></o:p>

// return context Item to continue processing in Innovator<o:p></o:p>

return this;

Retail Catalog, Action, Sync Orders

<o:p> </o:p>

Innovator innov=this.newInnovator();<o:p></o:p>

<o:p> </o:p>

// find CI Orders on remote system not in Innovator, and add them<o:p></o:p>

string sql= @"<o:p></o:p>"

select distinct f.ordno, f.wh, ci.item_number, ci.id as source_id<o:p></o:p>

from dbo.fed_ci_order f<o:p></o:p>

left outer join innovator.ci_order i<o:p></o:p>

on f.ordno= i.ordno and f.wh=i.wh<o:p></o:p>

inner join innovator.catalog_item ci<o:p></o:p>

on ci.item_number= f.item_number <o:p></o:p>

where i.ordno is null";<o:p></o:p>

<o:p> </o:p>";

Item adds =innov.applySQL(sql);<o:p></o:p>

if (adds.isError()) {return adds;}<o:p></o:p>

// if the resultset is empty applySQL returns an empty Result<o:p></o:p>

if (adds.getResult()!=""){<o:p></o:p>

// applySQL returns a resultset which looks like AML<o:p></o:p>

// but we need to add 'type' and 'action' attributes<o:p></o:p>

// we'll build an aml string containing a collection of Items that can be applied once<o:p></o:p>

string aml="<AML>";<o:p></o:p>

for (int i=0; i<adds.getItemCount();i++){<o:p></o:p>

Item a=adds.getItemByIndex(i);<o:p></o:p>

a.setType("CI Order");<o:p></o:p>

a.setAction("add");<o:p></o:p>

// turn add server event off<o:p></o:p>

// we don't want the onAfterAdd event to run!<o:p></o:p>

a.setAttribute("serverEvents","0");<o:p></o:p>

aml+=a.node.OuterXml;<o:p></o:p>

}<o:p></o:p>

aml+="</AML>"; <o:p></o:p>

// add the CI Orders to Innovator<o:p></o:p>

adds=innov.applyAML(aml);<o:p></o:p>

if (adds.isError()) {return adds;}<o:p></o:p>

}<o:p></o:p>

// find CI Orders in innovator not on remote system and delete them <o:p></o:p>

sql= @"<o:p></o:p>"

delete from innovator.ci_order where id in<o:p></o:p>

(select i.id<o:p></o:p>

from innovator.ci_order i<o:p></o:p>

right outer join dbo.fed_ci_order f<o:p></o:p>

on f.ordno= i.ordno and f.wh=i.wh<o:p></o:p>

inner join innovator.catalog_item ci<o:p></o:p>

on i.source_id = ci.id and f.item_number=ci.item_number <o:p></o:p>

where f.ordno is null)";<o:p></o:p>

<o:p> </o:p>";

Item dels =innov.applySQL(sql);<o:p></o:p>

if (dels.isError()) {return dels;}<o:p></o:p>

<o:p> </o:p>

// find CI Orders <o:p></o:p>

Item pr_opn = this.newItem("CI Order");<o:p></o:p>

pr_opn.setAttribute("select","qty_ord,qty_rcd,state");<o:p></o:p>

pr_opn=pr_opn.apply("get");<o:p></o:p>

if (pr_opn.isError()&&(!pr_opn.isEmpty())) {return pr_opn;}<o:p></o:p>

// and promote them if necessary<o:p></o:p>

for (int i=0;i<pr_opn.getItemCount();i++){<o:p></o:p>

Item o = pr_opn.getItemByIndex(i);<o:p></o:p>

if ((Int16.Parse(o.getProperty("qty_rcd","0")) <<o:p></o:p><

Int16.Parse(o.getProperty("qty_ord","0"))) &&<o:p></o:p>&&

o.getProperty("state")=="Closed"){<o:p></o:p>

Item res =pr_opn.getItemByIndex(i).promote("Open","");<o:p></o:p>

if (res.isError()) return res;<o:p></o:p>

}<o:p></o:p>

if ((Int16.Parse(o.getProperty("qty_rcd","0")) >=<o:p></o:p>>=

Int16.Parse(o.getProperty("qty_ord","0"))) &&<o:p></o:p>&&

o.getProperty("state")=="Open"){<o:p></o:p>

Item res =pr_opn.getItemByIndex(i).promote("Closed","");<o:p></o:p>

if (res.isError()) return res;<o:p></o:p>

}<o:p></o:p>

return innov.newResult("OK");<o:p></o:p>

<o:p> </o:p>