Archive for September 2011
Last weekend I travelled down to Seattle, Washington for a nice getaway weekend and to see the Science Fiction exhibits at the EMP. Then on Monday night I gave a presentation to the Seattle GWT Users Group on Sage ERP Accpac’s use of the Google Web Toolkit (GWT) in our 6.x product line. I found the group to be really knowledgeable and they asked great questions throughout the presentation. I learned a lot from the meeting and from chatting with the various people attending. Definitely very worthwhile. I previously blogged about Sage ERP Accpac’s use of GWT here.
Games on GWT
id Software makes many of the most popular first person shooter games including Doom, Quake and Castle Wolfenstein. Their lead programmer is John Carmack who is a legend in the gaming industry. One of the cool things they do is release the source code for some of their older games. They also port their games to various platforms, so often there will be an OpenGL version as well as a proprietary version, and perhaps a version ported to Java as well as the original version in C. A key metric of new computers, operating systems and devices has been how well they run the open versions of these games. I was really impressed when Google ported Quake II to GWT. Seeing Quake II work really well in the Chrome browser just using HTML5 was really really impressive. To me this really showed the power of the Browser as an application development platform, that if you could get Quake II running with a reasonable frame rate, then getting ERP screens going should be a piece of cake.
Of course the times have changed and Quake isn’t the most popular game anymore. So I was really surprised to see the latest hit game, namely Angry Birds ported to GWT. Angry Birds originated as an iOS game that has been hugely popular on iPhones and iPads. Angry Birds has perhaps more than anything been the killer application that has driven people to these devices. Of course Rovio wants to capitalize on the success of the iOS game, by porting it to other platforms. The easiest way to do this is to port it to HTML5 so it will work on all platforms, rather than doing separate ports for Android, Windows, WebOS, Blackberry, etc. For some more details on the porting of Angry Birds to GWT, have a look at this blog. GWT seems to be becoming quite a good technology for developing HTML5 based games. Many people are predicting that doing separate ports to all the mobile platforms, desktop platforms and game consoles is already way too expensive and most game development houses are looking to solve this with HTML5. This won’t work for all games, but it will work for many of the popular classes of games that are so popular these days.
When we started moving our Accpac screens to the Web we did all the layout using DOM layout nodes (which are abstracted as layout controls in GWT), but over time as we’ve become more proficient in CSS and now that we don’t have to worry so much about IE’s deficiencies, we are actually doing much of our layouts using CSS. This tends to lead to finer control than you can obtain using layout nodes so we can refine our look and feel to a higher level.
One of the reasons we chose GWT as the foundation for our new Web UIs, was that GWT was all about AJAX fundamentally from the start. AJAX wasn’t tacked on later to a system that was really from the Web 1.0 days. The support for AJAX in GWT is really great; but, our programmers coming from the VB world sometimes have trouble wrapping their heads around it. One of the fundamental differences between programming for the Web versus Client/Server is the issue of latency and how this accumulates as messages move across the Web. As the Web has progressed we now have tremendous bandwidth; so there are services that can stream a 4Gig movie to you in less than half an hour. However if you need to send a message across the Internet and wait for a response, it might takes 100ms which is still really fast, as long as you are only doing 1 message. If you do 10 messages then it takes a second, 100 takes 10 seconds, etc.
Programmers used to client/server programming on a LAN that probably spans only 1 building, don’t have to worry about this. Their latency is less than 1ms and they can make 1000 calls to the server without annoying users with a delay too much. As client/server programmers move to the Web there is a strong discipline required that if you have a process that require 10 server calls, then it must be re-factored to only require 1 call. Within our SWT framework, we make it easy to make 1 call, but don’t provide any help to string together multiple calls. This is intentional. Then we provide a good framework for our programmers to move “bad” code from the VB UIs into server classes where they can make many calls without penalty.
Perhaps the point here is that you need to do this independent of the Web programming framework. GWT has good support for AJAX, but it falls to the application programmers to use it correctly and this mind set change is really a function of moving to the Web rather than the choice of Web technologies.
Sorry if this blog posting is a bit disjointed. This is really just some thoughts based on discussions at the GTUG meeting on the pros and cons of GWT as well as adapting to other general Web programming models.
As we move to finish and release Sage ERP Accpac 6.1A, we are also working to host the Web Version of Accpac at AccpacOnline.com. I talked a bit about AccpacOnline and how it hosts Accpac currently as well as some of our plans here, this article looks a little deeper into how we will be deploying the Web version of Accpac Online, how we will be scaling it and how we will manage reliability. Of course as we develop this out, there will likely be some changes from what I am describing here.
The goal is to have an economically profitable hosted solution that can scale with our anticipated growth. Although it would be nice to assume we will be as successful as Facebook and quickly have 750,000,000 users, realistically we won’t and engineering a solution for 750,000,000 users doesn’t make sense. Like most companies you want to engineer your solution for the number of users you anticipate over the next few years and then as that materializes, you engineer for the next step.
Our initial goal is fairly conservative, and that is to host as many users on a web server as we currently host on a Citrix server. The Web server has a number of advantages over the Citrix server; namely it doesn’t run the UIs, so we save all the memory and processing by not having all those VB programs running, there is just the View portion then further the Web Server runs fewer processes and instead individual sessions are threads within a process, so this further saves resources.
Then we have the ability to scale out as many web servers as we need to accommodate our user load. As we move forward then we will work to increase the number of users that run efficiently on each server to make our solution better economically trying to scale to more users per server faster than we need to add servers.
To compare to how we deploy for on-premise installations, have a look at this article.
Below is a high level block diagram of how we will be running. This just shows the main components. I’m not covering everything, for instance this doesn’t show firewalls or other security layers that are present.
Hardware Load Balancer
At the left is a hardware load balancer. Its first job is handle encryption. All external requests come in using SSL. It is the job of this piece of hardware to decrypt all incoming communications and encrypt all outgoing communication. This offloads this onerous function from the actual Web Servers. Then its second job is to distribute incoming connections between however many Web Servers we happen to be running. Due to the way Accpac works we require that once a new connection is sent to one particular Web Server then all subsequent requests for that connection go to the same server, this is called operating with sticky sessions.
From the load balancer un-encrypted requests are routed to one of the IIS’s. If the request is for static content like bitmap images or HTML files then IIS just provides these. If the request is an SData request then it is passed onto Jakarta.
Jakarta is the connector which connects IIS to Tomcat. Besides connecting IIS to Tomcat, this connector has a number of very important jobs in a hosted environment. In our hosted environment we run a number of Tomcat server processes; each one running as a Windows service. Jakarta then acts as a software load balancer distributing requests across the Tomcat processes using sticky sessions again.
So why do we want to run multiple Tomcat processes and why do we want to operate a software load balancer on each server in addition to the main hardware one? The two big reasons are reliability and memory usage.
Accpac is still a 32-Bit Windows application. It runs fine on 64-Bit versions of Windows, but a 32-Bit process can only address 2Gig of RAM. As a consequence each Tomcat process can only allocate up to 2Gig of RAM and this is shared by all the threads running inside that Tomcat process. It’s easy and cheap now to get servers with far more RAM than this. So the way to utilize that RAM is to run multiple Tomcat processes each of which can utilize up to 2Gig of RAM.
The second issue is reliability. Of course we write our software to never crash and to never leak resources or do anything else harmful or bad. But the reality is that bugs happen and they have to be accounted for. If your Order Entry screen crashes, it only affects you and usually you just re-run the screen or at worst re-boot your computer. In a Web environment, something crashing on the server affects all the users on that server and re-booting a server is considered a very long time for a service to be down. Newer versions of Windows Server have gotten very good at isolating problems to an individual process, so when that process crashes, Windows can recover all its resources and happily continue working. So if a thread inside Tomcat (representing one user) crashes then the Tomcat process will crash and all the users using that Tomcat will be affected. Jakarta monitors this and if a Tomcat process crashes it will restart it. It also notes when a Tomcat process isn’t responding and will stop directing new requests to that Tomcat. Additionally you can configure Jakarta to stop using a Tomcat after some period of time (usually some number of days) and then when everyone is logged off it, restart it to recover any leaked resources. Generally the overall health and availability of the Web Server is being monitored and controlled by Jakarta. Of course we do extensive testing so these sort of events are infrequent, but they still need to be handled and recovered from.
Tomcat is a Java application server that runs our Java program that interprets SData requests. Then this Java application will call into the Accpac Business Logic (Views) to do the actual processing. Tomcat runs as a multi-threaded program in which each new request is handled from a pool of worker threads which do their job and then return to the pool. All the classic Accpac Business Logic then runs in the context of this program. As far as Tomcat can tell at this point, it is running as if it was on-premise and it doesn’t know about the other entire infrastructure that is going on around it.
Then off to the right of the diagram we have our pool of SQL Servers. Basically an individual customer’s company will exist on one specific one of these. Basically we manage these in exactly the same way we do for the current Accpac Online. In fact for a given customer, some users could be accessing the SQL Server through all the items in this blog and other users could be accessing it via Citrix and our current VB screens. All co-existing and sharing nicely.
This blog is just meant to give an idea of how we are setting up AccpacOnline.com for running the web version of Sage ERP Accpac, hopefully you found it interesting.
This blog posting will be looking at the structure of some of the parts of the Purchase Order (P/O) module of the Sage ERP Accpac product. You can find the structure of the individual tables in the Accpac Application Object Model (AOM), which must be viewed in IE. However this doesn’t tell you how the tables are related or how data flows from one table to another as you post various transactions. There are 169 tables in P/O, so obviously we can’t describe each in a single blog post. So we’ll just look at a few interesting cases. Understanding these relationships can be especially important to people writing sophisticated custom reports or BI Views.
The main types of database tables in P/O can be categorized as:
- Data Entry
- Periodic Processing
Purchase Order is one of the main entry points for data into the Accpac system. Notice that unlike other modules (except O/E); P/O has no master data files. Purchase Order mirrors Order Entry to some degree to handle the purchasing side of things while O/E handles the sales side. Documents from Purchase Order then flow into A/P, inventory is updated in I/C and all financial numbers end up in G/L. Below is a diagram showing this flow:
The Setup Tables are:
POOPT (PO00600): Options. Various global options for the P/O module.
POVIA (PO0900): Ship Via Addresses.
POPLAT (PO0605): P/O Templates.
POACST (PO0300): Additional Costs.
POACD (PO0290): Additional Cost Taxes.
POACSTO (PO0299): Additional Cost Optional Fields.
POMSG (PO0540): E-mail Messages.
POOFH (PO0585): P/O Optional Field Locations.
POOFD (PO0580): P/O Optional Fields.
POVUPR (PO0181): Vendor Contract Costs.
POVUMB (PO0191): Vendor Contract Cost Base Units.
POVUMS (PO0192): Vendor Contract Cost Sale Units.
POVUTX (PO0183): Vendor Contract Included Taxes.
Besides the standard tables that each application has, P/O has very few setup tables. However beware that P/O uses the setup tables in A/P and I/C extensively so look to these as well when considering what affects P/O.
Data Entry is the largest part of P/O. There are many tables involved in just entering a Purchase Order. Then you have requisitions, receipts, invoices, returns, and credit/debit notes. Several of these involve tracking serial numbers and lot numbers. Quite a few tables have associated optional field tables. Many of the tables (like POPORHx) are quite large records with many fields and span two physical tables (one ending in 1 and the other 2).
All the data entry tables rely heavily on header/detail relationships. Each level of indentation indicates that the indented level is a detail of the less indented level. For more on header detail relationships check out this.
The data entry Views perform many calculations as you enter data. This includes sales tax calculations and a very sophisticated proration engine.
A general workflow might be to start with a Requisition then from this generate a Purchase Order then from the Purchase Order generate a Receipt then from the Receipt generate an Invoice. Then optionally a Return might be generated or some Credit/Debit Notes. Generally whenever you create on document from another, you can in fact create a document from multiple of the preceding documents. So for instance you can create 1 Invoice for a collection of Purchase Orders.
Requisitions are usually the documents that start the purchasing process, here you are requesting something, but need approval or more data before generating a Purchase Order.
PORQNH1,2 (PO0760): Requisition Headers. Notice that the View spans two physical tables. For each P/O Requisition, there is 1 record each in PORQNH1 and PORQNH2.
PORQNL (PO0770): Requisition Details.
PORQNLO (PO0773): Requisition Detail Optional Fields.
PORQNC (PO0750): Requisition Comments .
PORQNG (PO0759): Requisition Functions.
PORQNHO (PO0763): Requisition Header Optional Fields.
PORQNLV (PO0777): Requisition Vendors.
POGNRQN (PO0359): SuperView to create POs from Requisitions.
Calls the following
POGNVD (PO0361): Create from Requisitions by Vendor.
Purchase Orders can either be created new, or can be generated from one or more Requisitions.
POPORH1,2 (PO0620): PO Headers. There are two physical tables for each P/O.
POPORL (PO0630): PO Details.
POPORLO (PO0633): PO Detail Optional Fields.
POPORC (PO0610): PO Comments.
POPORR (PO0632): PO (from) Requisitions.
POPORG (PO0619): PO Functions.
POPORHO (PO0623): PO Header Optional Fields.
POFRQNL (PO0345): Requisitions (lookup helper).
POPORI (PO0621): PO Postings. Result of POST button.
POGENIC (PO0350): Create POs from IC – Auto-generate from IC.
POGENOE (PO0352): Create POs from OE – Auto-generate from OE.
POGNOE (PO0355): Create PO from OE Order by Vendor.
POGNOEB (PO0357): Create PO from OE Order by Order Number.
Next come Receipts which are usually generated from one or more Purchase Orders.
PORCPH1,2 (PO0700): Receipt Header. There are two physical tables for this and each record spans both tables.
PORCPL (PO0710): Receipt Lines.
PORCPC (PO0695): Receipt Comments.
PORCPV (PO0718): Receipt Vendors.
PORCPS (PO0714): Receipt Additional Costs.
PORCPR (PO0705): Receipt (from) Purchase Orders.
PORCPG (PO0699): Receipt Functions.
PORCPHO (PO0703): Receipt Optional Fields.
PORCPD (PO0696): Receipt Cost Distributions.
PORCPI (PO0701): Receipt Postings – Result of POST button.
PORCPM (PO0711): Receipt Postings Lines.
PORCPT (PO0715): Receipt Postings Add. Costs.
Now it’s time to pay with an Invoice document.
POINVH1,2 (PO0420): Invoice Headers. Each Invoice header spans two physical records.
POINVP (PO0436): Invoice Payment Schedules.
POINVL (PO0430): Invoice Lines.
POINVLO (PO0433): Invoice Detail Optional Fields.
POINVLL (PO0819): Invoice Detail Lot Numbers.
POINVLS (PO0810): Invoice Line Serial Numbers.
POINVS (PO0440): Invoice Additional Costs.
POINVC (PO0416): Invoice Comments.
POINVG (PO0419): Invoice Functions.
POINVHO (PO0423): Invoice Header Optional Fields.
POINVD (PO0415): Invoice Cost Distributions.
POINVB (PO0414): Duplicate Invoices.
POINVI (PO0421): Invoice Postings Header. Result of POST button.
POINVT (PO0441): Invoice Post Cost Lines.
POINVM (PO0431): Invoice Postings Lines.
If you don’t like what you purchased then return it.
PORETH1,2 (PO0731): Returns Headers.
PORETL (PO0735): Return Lines.
PORETLO (PO0739): Return Detail Optional Fields.
PORETLL (PO0799): Return Line Lot Numbers.
PORETLS (PO0790): Return Line Serial Numbers.
PORETC (PO0729): Return Comments.
PORETG (PO0730): Return Functions.
PORETHO (PO0738): Return Header Optional Fields.
PORETI (PO0732): Return Postings. Result of POST button.
PORETM (PO0736): Return Posting Lines.
POCRNH1,2 (PO0311): Credit/Debit Note Headers.
POCRNL (PO0315): Credit/Debit Note Details.
POCRNLO (PO0318): Credit/Debit Note Line Optional Fields.
POCRNLL (PO0829): Credit/Debit Note Line Lot Numbers.
POCRNLS (PO0820): Credit/Debit Note Line Serial Numbers.
POCRNC (PO0309): Credit/Debit Note Comments.
POCRNS (PO0320): Credit/Debit Note Add’l Costs .
POCRNG (PO0310): Credit/Debit Note Functions.
POCRNHO (PO0314): Credit/Debit Note Header Optional Fields.
POCRND (PO0326): Credit/Debit Note Cost Distributions.
POCRNB (PO0308): Cr/Dr Duplicate Notes.
POCRNI (PO0312): Credit/Debit Note Posting. Result of POST button.
POCRNM (PO0316): Credit/Debit Note Posting Line.
POCRNT (PO0312): Posting Additional Costs.
Most data processing in P/O happens at Day End time, whether this is initiated from I/C Day End Processing or you have the perform day end during posting option set. You can also choose to split the operation and do costing during posting and the rest (like sub-ledger batch generation) during I/C Day End. I/C Day End will call the Day End processing View in each application that subscribes to Day End (including O/E and P/O). For more on Day End Processing check out this blog post.
Day End Processing
PODEPR (PO0335): PO Day end Processing.
POPORZ (PO0636): PO Posting.
POHSTH (PO0380): Purchase Order History.
POHSTL (PO0384): Purchase Order History Lines.
POPORAH (PO0612): Purchase Order Audit.
POPORAL (PO0614): Purchase Order Audit Lines.
PORCPZ (PO0720): Receipt Posting.
PORCPAH (PO0686): Receipt Audit Headers.
PORCPAL (PO0688): Receipt Audit Lines.
PORCPAQ (PO0690): Receipt Audit Prorate Lines.
PORCPAS (PO0692): Receipt Audit Costs.
PORCPJ (PO0702): Receipt Day End History Headers.
PORCPN (PO0712): Receipt Day End Lines.
PORCPU (PO0716): Receipt Day End Costs.
POINVZ (PO0445): PO Invoice Posting.
POINVAH (PO0409): Invoice Audit Headers.
POINVAL (PO0410): Invoice Audit Lines.
POINVAQ (PO0411): Invoice Audit Prorate Lines.
POINVAS (PO0412): Invoice Audit Costs.
POINVJ (PO0422): Invoice Day End History Headers.
POINVN (PO0432): Invoice Day End Lines.
POINVU (PO0442): Invoice Day End Additional Costs.
PORETZ (PO0744): Return Posting.
PORETAH (PO0724): Return Audit Headers.
PORETAL (PO0726): Return Audit Lines.
PORETAQ (PO0727): Return Audit Prorate Lines.
PORETAS (PO0728): Return Audit Costs.
PORETJ (PO0733): Returns Day End History Headers.
PORETN (PO0734): Returns Day End Lines.
POCRNZ (PO0330): Dr/Cr Note Posting.
POCRNAH (PO0304): Dr/Cr Note Audit Headers.
POCRNAL (PO0305): Dr/Cr Note Audit Lines.
POCRNAQ (PO0306): Dr/Cr Note Audit Prorate Lines.
POCRNAS (PO0307): Dr/Cr Note Audit Costs.
POCRNJ (PO0313): Dr/Cr Note Day End History Headers.
POCRNN (PO0317): Dr/Cr Note Day End History Lines.
POCRNU (PO0322): Dr/Cr Note Historical Add’l Costs.
Day End Helpers
Updated during day end
POLINEZ (PO0520): Generic Line Data.
POCOSTZ (PO0295): Generic Cost Data.
POPRXH (PO0656): Cost Prorate Headers.
POPRXL (PO0658): Cost Prorate Lines.
POPRXP (PO0660): Line Cost Prorate Lines.
POPRXC (PO0650): Line Cost Prorate Details.
POSTTL (PO0800): Statistics – from Day End.
POCH (PO0302): PO Clear History.
POGLTR (PO0353): Create GL Batch.
POLBLS (PO0500): Mailing Labels.
POFLAG (PO0340): Mark Posting Journals Printed.
POPRNT (PO0640): Mark Documents Posted.
PODLDN (PO0336): Drill Down.
POINIT (PO0405): PO Activation (new).
POUPGD (PO0850): PO Activation Upgrade.
POINTCK (PO0407): Integrity Checker.
Hopefully this blog posting provides a bit more insight into how P/O operates and hopefully helps when you use P/O, interface to P/O or are creating custom P/O reports or forms.
Sage ERP Accpac 6.1A is starting to come together with most screens mostly implemented. Beta will be starting soon. It’s been seven months or so since we kicked off development and we’re all starting to look forward to release. I started to show a number of screens from the new Web Based Order Entry module here, now with this blog posting we’ll look at a few more aspects of Order Entry 6.1A in the web.
Remember that you aren’t forced to use the new Web UIs, we hope you will use them; however, all the regular VB based UIs are still there just like they are in 6.0A (or 5.x). This means if you have heavily customized O/E screen you can continue to use those and choose when and if you will move the customizations to the new screens. Also if you are incredibly well trained in heads down order entry on the old screens and what to maintain that, then you are free to do so. In fact you can have some users running the old screen and some users running the new screens, they all co-exist perfectly in one installation.
Generally the web based screens have the same functionality as the current VB based screens. However things may be moved around a bit or styled to look a bit nicer. Below is a screen shot of the new Order Entry UI showing the customer credit check popup. Which shows Ronald Black is very much over his credit limit.
Reports are still based on Crystal Reports. In fact we haven’t changed the current O/E reports at all. This way you can use exactly the same customized reports in the web that you are currently using in Accpac. The general mechanism of running reports is the same, you first get a screen that asks you for various ranges, filters and other options for the report, and then when you click print you get the actual Crystal Report. This means there is no extra cost re-doing customized forms when you go to version 6.1A whether you use the Web screens or not. Below is the form for getting the options for the O/E Aged Orders report:
Notice that the “Print” button is actually a combo-box where you can have the report open in a new tab, or replace the current tab. If you select a new tab, then you can have multiple versions of the report open at once and compare them. The reports are displayed in the Accpac Web Portal using the Crystal HTML Viewer. There is no requirement for a plug-in or ActiveX control. This means you can view these reports in any browser on any device. The reports are run on the server using the Crystal Java runtime, so there is no requirement for any extra server like Crystal Enterprise. When you press “Print” you get the following:
Since, so far only O/E has moved to the Web, if you drill down to a form in another module you will get a VB UI. This functionality will only work if you are running in the 32-Bit version of Internet Explorer. All other functionality works in other browsers like Chrome, Safari or Firefox; but, you can only run non-web based programs from the 32-Bit version of IE. Partly this is why the previous screen shots are from my favorite browser which is Chrome, but the next screen shot is from IE. The next screen shot shows choosing Inquiry from the Customer’s context menu in the Web Screen. Then this runs the A/R Customer Inquiry screen which we still only have a VB version for.
Of course there are Web versions of all the various statistics and inquiry screens such as Salesperson Inquiry pictured below:
Then we have all the setup screens such as Ship-via Codes pictured below:
The intent of this blog posting was to give a further view into the forthcoming Web Based Order Entry module. Just to show what’s coming soon…
Update 2011/09/17: Actually it turns out there is an open plug-in interface that works for Firefox, Chrome and Safari which has allowed us to develop a single plug-in that now lets the VB UIs run from these Browsers. So now there are no limitations using Chrome, Firefox or Safari.