OpenMFG gateway API

With the release of 2.2 Beta one we finally have a first pass at the views for simplified e-commerce integration. We sure would like to hear feedback.

They are pretty much done; we'll need a white paper of some sort when they fully is released, but for now here's a short synopsis:

*The goal is to make importing data directly into the database much safer and easier than it is going into the regular table structure. The specific target audience here is folks such as vars and customers who would want to integrate e-commerce solutions with OpenMFG, without having deep experience in the bowels of OpenMFG business logic.
*There are views to to support the creation of Customers, Quotes and Sales Orders using simple SQL statements.
*They are all prefixed with an underscore to so they appear separate and at the top when looking at a list of views and/or tables. For example the sales order view is _salesorder.
*To aid in mapping exercises the view names, structure, sub view relations and field names mirror the conventions of their corresponding windows rather than the source tables.
*Where combo box or openmfg widget data is used in the GUI, you must pass the text code equivalent in the view. So for example you would pass values like 'BTRUCK1' for an item, 'WHS1' for a warehouse and 'VA TAX' for a tax authority.
*Normal SELECT, INSERT, UPDATE and DELETE statements can be run against them where they apply. If they don't (like deleting a comment) the command is ignored.
*Inserting records behaves as closely as possible like the GUI equivalents. That is, you only need to populate required fields. Any left unpopulated will default in the same way they would in the GUI.

The only logic we have not gotten to is automatic CM allocations on sales orders, credit card data validation on credit cards (the _custcreditcard view does work and will encrypt the data, just not check all the fields yet.) and a few constraints I'd like to add. Other than those few remaining loose ends, most all the business logic in the GUI including data validation, automatic default population and privileges should be honored in the views.

There are some sample scripts posted on the associated Mantis log 5380

Note the CSV import tool published on our website has also been upgraded to see the views.

I think this is a great idea.

+1 from me.

Dave

I've got a specific data import implementation I'm thinking about, and I guess this is as good of a place as any to post something on it. We'd like to be able to import bank transaction files into the Bank Reconciliation screen. Most all banks offer an option to download their transactions in .csv format. Unfortunately, they don't use a common field format.

In spite of my rantings against writing a general purpose translator, this might be a case where a very specific one could be useful. You just need to be able map the .csv bank statement file fields to a predefined import table, which would probably just have four mappable fields: Date, Description, Detail and Amount.

Perhaps users could create these maps on-the-fly, and save them such that they become associated with the bank account. Quickbooks, Quicken, Peachtree and other basic accounting programs all do this. In fact, it often the only kind of data import they do. The idea is very specific, and I think much narrower than what this thread topic is about. But I just wanted to throw it out there in case anyone felt there might be a conflict.

I think the balance of this thread supports my original comment, that there are too many platform variables out there to attempt to write a universal translator. Fortunately, though, there are many wonderful tools available from scripts to EAI engines to facilitate translation. I think people are generally going to want to use the tools they are familiar and comfortable with for that. If OpenMFG can provide a means to import and export via a set of views with full business logic support, that would be a huge help regardless of the tools being used and it keeps all the options (ahem) open. So even if OpenMFG does ultimately have some kind of limited processor per Scott's map, such as a batch manager driven import routine, other users who find that tool insufficient are not forced into a limited paradigm, but can freely leverage the power of the "i" views and still be in the box of "OpenMFG supported functionality."

For example we have several maps for edi imports processed using Jitterbit that are immensely complex due to openmfg's highly normalized architecture. A new sales order for a new customer touches or calls on the following tables:

cust
cntc
addr
shiptoinfo
cohead
coitem
crmacct
whse
item
itemsite

Getting the keys for these to marry up and calling necessary functions and lookups is an arduous task. We would love to be able to map our data to the following views...

iCustomer
iShipto
iSalesOrder
iSalesOrderLine

... and just feed item numbers, customer numbers, contact data etc directly in and let the database do all the work figuring out keys, discounts, where address data belongs, privileges and so on just like the GUI.

Ned asked me today if the views should include view-only data seen on the GUI, such as inventory availability. After thinking about that, I'd say to start out no, it just makes the views more complicated. My guess is the variety of requirements to gather viewable data will be impossibly broad, and view-only data available in the GUI will rarely be of help in a custom application (otherwise why is the custom app being built?). I would only include the GUI editable fields in the beginning. If, however, there later turns out to be a call for easily accessible informational views, ones could be built that emulate GUI displays like Inventory Availability, A/R Open Items by Customer and so on.

All for now... I look forward to seeing where Scott takes this. Sounds like he's got a good handle on it.

As I read more on this topic I found myself laughing at my own ignorance as I read a Wikipedia article stating that Postgres was developed as a research effort to solve the "Object Relational Database" problem:

http://en.wikipedia.org/wiki/Object-relational_database

So it appears clearly Postgres would support an object model set of views. It's just a matter of defining how to go about it. In fact, it sounds like the ability to do this is another way to leverage the architecture that would be impossible for multi-DB supporting ERP competitors to follow.

I realized that there have been general discussions here, but we haven't directly answered Jason's questions.

1. What methods are you using now, or would you use now?
- Typically we end up writing a custom program that has lot's of variable1 = variable2 statements, and lot's of validation/transformation. Unfortunately you end up rewriting much of this for each implementation.

2. If you are using third-party products, what are they?
- We've toyed with some 3rd party stuff (Jitterbit and others I can't remember the name of) but haven't found the magic bullet yet.

3. If you are using scripting or programming languages, which ones?
- Depends on the platform/database. If we're coming from an iSeries, then we sometimes will use CL or RPG. More often, we use VBScript because it can connect easily to any database and is very fast to learn. This is really just a conduit for SQL statements. VBScript allows us to simplify conditional statements and split up complex joins if needed. I'm not a huge Microsoft fan..but this works.

4. If you saw benefit in adapting to a new scripting language, which ones could you adapt to?
- Having just given my endorsement of VBScript, I now feel compelled to push something else. I don't like the single-platform nature of VBScript. A cross-platform scripting language such as JavaScript might be more suitable. The problem we've had with using JavaScript is that it has a steeper learning curve than VBScript. Database connections are easier with VBScript and the ability to manipulate Excel spreadsheets using CreateObject is priceless.

Like I said...I'm not a huge MS fan (I use an Apple)...but the learning curve is low and the language is powerful.

We basically take care of the data interchange via scripts. On older windows based systems, and coming from an M$ world, we used good old Visual Foxpro, create a couple f nice .exe's and set it up on the windows scheduler. VFP has always been the red headed step child of M$ development products, but it plays very nicely with all sorts of databases.

In the current world, we use python scripting but in effect are doing the same thing, and cross platform to boot.

While in theory it'd be nice I suppose to translate everything into some sort of XML document, that would seem cumbersome to me for exchanging more than a few transactions / day.

Anyway you cut it, you need to build the maps (bridges) and define the processing rules for this "thing."

Synching databases is not trivial.

Javascript is a nice web language, but it's not built for this sort of thing. PHP in a browser is an option -- but you're assuming either a browser based app for processing, or something like PHP/GTK+ for a gui -- which is why I chose python.

Dan Means
Team SRS
Mission Viejo, CA
www.teamsrs.com

Ned had requested some information about EDI at a current implementation.

I've attached some EDI documents for United Engine. You should find the images attached to Jason's original post. This was scanned from greenbar off their existing legacy system.

These are the specs for incoming orders, based upon an older Melling format. This is an example of the type of data that we'll need to accomodate. In this scenario, this data is actually sent from the customer to ATT Easylink (the VAN) and then translated and sent to United Engine. Considering that this is a flat-file going to ATT, it makes sense to eliminate the VAN and provide the customers and url to send the data to.

United Engine retrieves the data each day from ATT, then validates and cleans the incoming flat-file before processing it into their existing system.

- Scott

What would be really handy in this effort is if Postgres had native support for objects the same way Oracle does. That way tables could be tied together as unified business logic units. I haven't found anything on the subject for Postgres, but am searching.

The summary for the Oracle implementation is:

"Oracle object types are user-defined data types that make it possible to model complex real-world entities such as customers and purchase orders as unitary entities--"objects"--in the database..."

To learn more, check out these two links:

http://www.lc.leidenuniv.nl/awcourse/or ... objint.htm

http://www.lc.leidenuniv.nl/awcourse/or ... objfaq.htm

This link talks about "virtual objects" in Oracle, which means making special views based on legacy relational tables that behave as objects... which is EXACTLY what I've been talking about. Check this out:

http://www.lc.leidenuniv.nl/awcourse/or ... htm#433584

Since Postgres is extensible, I wonder what it would take to incorporate Object Logic into it as a contrib module or something? Or maybe such a thing already exists by some other name.

Jason's point about dealing with so many potential protocols, transmission and translation methods that might have be dealt with is valid and would be of great concern to me. There are many EDI and EAI (Enterprise Application Integration) companies that have large dedicated staffs of programmers and comprehensive products and consultants to deal with these sorts of problems. I would fear going in that direction would stray from OpenMFG's core competency which is providing a great manufacturing database application. I would stay clear of data transformation.

That said I think there are things that can be done that would greatly aid the integration of data into OpenMFG. We had a substantial integration project that was challenging, to say the least, largely because of translation of document numbers to ID keys and the calling of functions that had to be embedded into the translation logic. I believe these obstacles would be a big challenge for any integrator.

I would take the basic approach of creating a set of views in OpenMFG that expose the exact same fields as are found on the GUI interface and process INSERT and UPDATE data sent to those views according to OpenMFG business rules. Triggers can be attached to the views called by the INSTEAD method which tells the database that when a user inserts or updates data here, do something else "instead," which would be processing the data according to business rules such as validation, applying discounts, breaking and inserting data into appropriate table structures, creating work orders etc.

This keeps the technology simple and well within the realm of the current paradigm. It also lets the VAR or customer use any integration middleware tool set they are comfortable with to manage data translation such as BizTalk, Jitterbit,Tibco or even basic tools like MS Access and batch scripts. All they need to do is use a simple ODBC connection to attach to the views and they are good to go. Any EAI translation tool worth its salt can do that. Plus you won't necessarily have to know programming to use the views as you would if you implemented a full blown API toolkit.

I would use a naming convention for the views that prefixes them with "i" (for integration, import or interchange?) and a very obvious name so even novice developers can figure out exactly what they are with ease:

iCustomer
iSalesOrder
iSalesOrderLine
iItem
iBillOfMaterialHead
iBillOfMaterialItem
iBillOfOperations

If OpenMFG wanted to provide some way a basic user could get data into the system with GUI tools, I'd suggest adding a set of Export and Import screens to OpenMFG that leverage the new views. These would be able to export or import data using pre-formatted CSV or XML file structures. Tasks could be submitted to the batch manager so that, for example, you could schedule to the batch manager to import all .xml files found in a particular folder as sales orders. This assumes that the files have been created conforming to a predefined OpenMFG format. It would be up to the user or VAR to figure out how to get them into that format with their methodology and tool set of choice.

I fully agree with Dave's thought (relayed by John) that the OpenMFG GUI should use whatever API is developed: If we don't use it internally then it'll languish. In addition, if it isn't broad or deep enough to support the GUI then it isn't broad or deep enough to be useful.

In response to John's concern about system-wide changes, the entire GUI client will have to change regardless of the form the API takes. That's a lot of work but shouldn't stop us from designing a good API. Fortunately, much of the GUI is built using customized versions of the Qt widget classes. It wouldn't be difficult to change the interfaces of these widgets to give us the data we need, regardless of the form of the API.

For example, with the view-as-API the widgets would be modified so we can get codes instead of internal IDs. We could even use this mechanism to help ensure that we fix the GUI in all the right places (geek alert: make the id() methods private and let the compiler tell us when we missed a spot requiring conversion).

I have end-user usability concerns about the view-as-API approach. My biggest concerns surround error reporting and handling:

How do we give enough but not too much information to fix a problem when one arises?
How do we do this in multiple languages?
How can the GUI distinguish between errors it can fix without bugging the user and those it can't?
How do we collect enough context from the error for the GUI to be helpful (e.g. put the cursor back on the right widget when a required data element is missing

I think the solution to these concerns is a layered API. The lowest layer is a new set of data types, each of which enforces a set of rules (e.g. monetary values would be stored as NUMERIC/currency pairs with a precision appropriate to that currency rather than as pure NUMERICs with currencies tacked on in different fields). The next level up would be stricter definitions of columns (e.g. use more foreign keys, have fewer NULLable fields). The 3rd level would have two parts, the collection of views described in earlier postings and a superset of the stored procedures we have now. The stored procedures could either look for problems in the data and return numeric codes as they do now or catch exceptions thrown by lower layers and translate them into numeric codes. The fourth level would resemble the processing API drawn by Scott. The GUI would then use some combination of levels 3 and 4.

I don't know what the performance implications are of a stack this deep, but it'll be really hard to get bad data into the database. Migration of existing data will be a bear, too.

Gil

Just thought I bounce a long term concern/question I had about the API direction as it relates to the standard Interface.

Dave, who is working on this initiative, made the comment to me that in the long run the OpenMFG GUI should go through the API, or in this case views, to make sure there is "one code path." I think this is a valid point because I know of at least two systems that built an API that was not used by the GUI, and over time they got out of sync because there was never as much urgency to make sure the API was kept current as there was to add new functionality to the GUI.

Unfortunately using the views, or any form of the API, on the GUI probably won't be straight forward. The GUI screens have a clever way of looking up keys that are associated with drop down combo boxes, most of which are populated with codes concatenated with descriptions. One of the basic ideas of the API is to let the user simply pass the code that is presented in the GUI and let the API figure out the key. This is very important because one of the most difficult things about mapping external systems to OpenMFG right now is finding all the keys. Usually the external system has the code, not the key. In any case, the GUI in most cases DOESN'T have the code in a pure form that can be passed back; it has the key and a description. To get the code would make the GUI more complicated than not using the API. To change the GUI to use codes instead of IDs or concatenated descriptions would require a system wide paradigm change.

I don't propose to have an answer to this dilemma. Just fishing for thoughts.

Regards,

John

Hadn't thought of the idea of custom types. That sounds good. Dave and I had talked about a series of "get" functions that handle a lot of the key lookups and validation, in addition to column constraints and triggers potentially handling logic as well.

I'm not sure what you mean by the superset of stored procedures, unless you mean the kinds of functions I just mentioned.

The down side of the layered approach, which seems to be where things are going, is it scatters business logic around in the database and puts heavy reliance on Postgres. The good thing about the layered approach is it makes the system modular and flexible. Like Gil said, it would be hard to break the database this way if users attempt to stick their own data in using ODBC or something. The processing API would also be fairly light weight at that point.

How would migration of existing data be a bear unless there was a lot of bad data in the db to begin with? The GUI doesn't allow that now, right?

One thing we need to keep in mind is backward compatibility and stability of the application. Backwards compatibility has always been a goal, largely related to report definitions, of any change we have ever made. The more drastic and the more numerous the changes the more difficult that becomes to either maintain or keep at all. Stability is another concern to be aware of as well, as again the more drastic or numerous the changes are to one or both of the client and database the higher chance there is that stability of the system might not be maintained, either from a data or functionality stand point.

Good points, rf.

My basic response is: You've got to walk before you can run.

The API views are the method we are using to abstract the business logic. From there they could easily be attached to a generic web service wrapper/service that bolts on to Postgres. You just point it to the views and you're done.

The bigger bugaboo is translation. A lot of blood sweat and tears has gone into the development and debate about universal document standards and I'd like to jump on that bandwagon when it makes sense. Groups like OASIS (http://www.oasis-open.org/home/index.php) are leaders on this front, but other big players like Microsoft have their own ideas. If the standards could be settled on, we could have yet another API layer that translates to these standards or provide an XLST library to that does that work. But the problem is until some set of standards are widley adopted, someone with programming and database expertise has to get involved to make all the non-standard stuff out there work. Even with common protocals in place, making two disparate SOA systems talk is not something a SMB Controller, Enterpreneur, or Operations Manager knows how to do. But a lot of these folks can deal with ODBC connections. We're trying to get that ground covered today, while at the same time laying ground work for an easy path to SOA.

I welcome any experiments out there from folks who'd like to get the existing views working on a preferably OS independent web service. In the mean time, we've got a lot of views to create....

John

I've posted a proposed specification for a phase 1 API implementation of views and UI features based on feedback from this forum. The intention is to get started on this as soon as 2.1 is released. Your feedback is welcome.

John

PDF of spec is here

The strategy of the updatable view is fine, that makes it clean & straightforward for custom external apps to insert / update data.

Expecting the import files to be in XML -- well, that's doable I guess, but when you think about things like importing orders / customers data on a recurring basis (cron job) - is that the best way vs. tab / csv delimited or some such.

No ordinary user will use the graphical "import" tool.

Dan Means
Team SRS
Mission Viejo, CA
www.teamsrs.com

While for the most part I'd agree that an "Ordinary User" might not use the graphical import tool, I think there are important exceptions. I see this tool as being a means to manually process a single file. Provide a procedure and an operator (or other ordinary user) should be able to handle this step.

Regarding XML...there needs to be a standard input format...and XML by it's very nature gives you the ability to describe the data. When reviewing data 2 years after writing a conversion, it's nice to read a tag that says "customer_name" rather than have to count the commas and read a spec. I do agree that most incoming data is going to be CSV to tab-delimited, but XML processing gives us a "cleaner" input, IMHO.

Good to see some conversations on this.

- Scott

Inbound plug-in

It seems to me that the plug-in is the key. Marena's post is a great example. This is a common request which we'd normally handle by writing custom code to map to the table(s) in OpenMFG. Can we create a series of one-sided equations? What I mean by this is create the portions we know. If we have a solution written that would provide one side of the mapping for a bank reconciliation, then we would need to write custom plug-ins for each new inbound format.

I made a quick diagram illustrating this basic approach:

We finally have some documentation on the new API. We've created a zip file that has a white paper and some examples of how it can be used using SQL, the CSV import tool, MS Access and MS Excel. You can download it here.

Version 2.2 Beta 3 includes full support for inserting encrypted credit card data into customer records, including validation checks that were not present in beta 2. Note, however, this is intended to facilitate the importation of secure credit card data only. The actual authorization and charging of credit cards must take place in the standard PostBooks/OpenMFG UI.

Hi Wayne and -J,

I just read this discussion and I have a couple of comments:

I think the views are a good place to start but that it really should be extended to be the basis of a Service Oriented Architecture (SOA). Then the GUI is just one of the client applications/interfaces to the data/services that are the core of the SOA. What happens then is every one can specialize the components they need in there business domain. A good example for Wayne and -J are the Amazon SQS type interfaces which are SOAP and/or REST (Message Queues are for real time delivery) based. Also the SOA make adding Business Process Management practical.

Some examples that make SOA attractive in manufacturing are the interface to the production automation and/or process control equipment as well as collaboration with business partners.

Electronic Data Interchange is going to be more critical even in smaller organizations. We are already seeing it banking interfaces for reconciliation, payment requests, electronic deposit and other check23 applications.

I guess what I am saying is this: a good start, look at more of an SOA interface and start abstracting the business rules so they can be implemented in a rules engine.

EDI has been traditionally expensive but approached as a community it does not have to be! How many times do we need to implement any one interface? As a community only once and then everyone has access to it. Anything more than that and we are really missing an opportunity.

-rf

I'm jumping into this conversation very late so apologies in advance if this has already been covered somewhere else.

I've just been reading the API Phase 1 notes and wondered if there's been any consideration to using something like the Amazon Simple Queue Service (SQS). This would help to step around some of the issues with Batch Manager initiated imports and provide a scalable solution for EDI with trading partners and external systems.

I'm jumping into this conversation very late so apologies in advance if this has already been covered somewhere else.

I've just been reading the API Phase 1 notes and wondered if there's been any consideration to using something like the Amazon Simple Queue Service (SQS). This would help to step around some of the issues with Batch Manager initiated imports and provide a scalable solution for EDI with trading partners and external systems.

wayneb,

To be honest, I don't know much about SQS. There mght be some good arguments to move in that direction and as always we welocme any feedback and efforts by the community that would make the xTuple product suite more useful.

We've had a lot of soul searching discussions about the API over the last few months and our stance is basically boiling down to this: We leverage two primary technology pillars, the Postgres database and Qt C++ development environment. We believe that keeping our technology platform focused this way helps keep the product easy to install, consistant, and simple for basic users to operate and maintain. Ease of use is paramount (more so than scalability) because our target audience is small to mid size businesses who often lack technical resources.

As a result we are likely to think that a Qt based GUI interface or service, like our CSV import tool, will be more useful to the general user base than technical tools targeted towards programmers like object wrappers for alternate languages and integration to specific third party tools. That said, we believe our paradigm of using views will enable developers from all walks to use any language, tool or service they prefer to more easily leverage our product.

We always try to keep an open mind and ear to what the community has to say, however, so please feel free to challenge us if you disagree. And if someone has devised a slick alternative solution to solve a problem with our product we will be happy to showcase that solution to the community.

Regards,

John

wayneb,

To be honest, I don't know much about SQS. There mght be some good arguments to move in that direction and as always we welocme any feedback and efforts by the community that would make the xTuple product suite more useful.

We've had a lot of soul searching discussions about the API over the last few months and our stance is basically boiling down to this: We leverage two primary technology pillars, the Postgres database and Qt C++ development environment. We believe that keeping our technology platform focused this way helps keep the product easy to install, consistant, and simple for basic users to operate and maintain. Ease of use is paramount (more so than scalability) because our target audience is small to mid size businesses who often lack technical resources.

As a result we are likely to think that a Qt based GUI interface or service, like our CSV import tool, will be more useful to the general user base than technical tools targeted towards programmers like object wrappers for alternate languages and integration to specific third party tools. That said, we believe our paradigm of using views will enable developers from all walks to use any language, tool or service they prefer to more easily leverage our product.

We always try to keep an open mind and ear to what the community has to say, however, so please feel free to challenge us if you disagree. And if someone has devised a slick alternative solution to solve a problem with our product we will be happy to showcase that solution to the community.

Regards,

John

Hi Wayne,

Why give Amazon a share of the pie?

I was thinking about creating something like this from scratch using the Jabber XMPP protocol. There are a number of commercial packages (think IBM Websphere MQ), and maybe a few open source ones (Apache ActiveMQ for example) that allow disparate systems to pass messages back and forth. This is what Amazon SQS looks like, except you have to go through their servers.

There are also a few open source data interchange systems, like Redberri and JitterBit. Redberri is nice in that it will give you real EDI, but it's unsupported as the company has been "load testing 6.0 and will release it any day now, REALLY" for the past 2 years. Check out SourceForge for the projects.

This is something that will have a high interest level going forward...

Cheers,
-J

Dan,

You're right. No basic user would use the import tool as it is defined here. It's still targeted for technical people. I can't readily envision scenarios where any folks other than technical users and integrators are going to be able to grapple with the data interchange problem. No matter what the format, today it is a technical challenge to map data from one system to another. We're trying to make it easier for those technical people to talk to OpenMFG; in this round specifically to talk to E-commerce systems.

XML is best suited because it has become the defacto standard for e-commerce transactions. There is a lot of work being done out there to develop world standards for documents and transactions in XML format. (http://www.oasis-open.org/home/index.php) When those standards come to be widely used, we will be in a good position to leverage them. There will be a day when an ISO or ANSI standard XML PO is emailed from a customer using another system like SAP or Quickbooks to an OpenMFG user in an email, and that .XML file will just be dragged and dropped onto the S/O screen which will automatically populate from the data in the file. That is the technology we are laying ground work for, and when that time comes even non-technical users will benefit greatly.

I would love to include other formats as well such as CSV and the like, and perhaps in a later phase we can get to that. If anyone were to develop such tools, I would happy to see if we can incorporate them. The views will certainly make that job easier. In the meantime we need to start somewhere, and XML seems like the most appropriate format to use for both current and future needs.