Stephen Smith's Blog

Musings on Machine Learning…

Archive for November 2010

Customizing Quotes to Orders

with 9 comments

With Sage ERP Accpac 6.0A we have incorporated Web based versions of the Accpac Order and Quote entry screens into SageCRM. These are the first accounting document entry screens in the new Accpac Sage Web Toolkit (SWT)  technology. A common question I get asked is how to customize these screens. Certainly the regular current Accpac Order Entry screen is one of the most customized screens in the Accpac system. However it is very hard to customize the current screen when it’s run from SageCRM because of the way it is packaged and run from a CAB file. The new screens are regular Accpac screens and run inside SageCRM just like they would run from Accpac. This means all the regular customization techniques for the new web based technology can be used on them.

As a starting point the screen definitions are now stored in a separate XML screen definition files. You can accomplish a lot just by editing these files. In this blog posting we will look at a collection of simple but powerful customizations you can perform here. In this blog posting we will just be looking at the XML for the controls and manipulating those. Actually in addition to that you can add JavaScript processing code to the events that get fired, this is a bit more sophisticated and we will look at that in a following blog posting.

The XML file is located in the folder: C:\”Program Files (x86)\Common Files\Sage\Sage Accpac\Tomcat6\portal\swtServices\uiDefinitions\oe60a\eng\sagecrmorderui”. The file we will look at is SageCRMOrderUIUIDefinition.xml. The XML files for a couple of popup forms are here also and you can customize these also. You can edit these files in any text or XML editor. If you have the SDK you can edit these files in a visual SWT UI designer, however for the purposes of this blog posting we will assume you don’t have the SDK and edit this file directly as a text file. One nice thing about the new web based technologies is that they are entirely configured by XML files, which being text files can easily be edited for customization purposes.

Introduction to XML

First just a few notes about XML. It’s worth your while to check out a site like http://en.wikipedia.org/wiki/XML to learn the basics of XML. But for what we’re doing here you really don’t need to know very much, just how to find things, cut and paste things, and make minor changes.

The key point is that all data is contained between a starting tag and a closing tag like:

<caption>This is a caption</caption>

Where the closing tag is the opening tag with a / in front of it.

Sometimes you can express attributes for a tag like:

<caption font=”Sans Serif”>This is a caption</caption>

Also if there is no general data then you can end the tag with a /> like:

<caption font=”Sans Serif” text=”This is a caption”/>

Generally most of the other stuff you find in the XML file, you would leave alone, like the version and encoding and such.

Form Layout

We talked a bit on how to layout forms in https://smist08.wordpress.com/2010/05/28/how-to-layout-forms-in-sage-erp-accpac-6/. So we won’t talk too much about how to control the layout here, but we may look at this more in depth in a future posting. But beware that there are two types of widgets in a screen definition file, there are layout widgets that control how things look and then data widgets for displaying/editing data.

Widget Elements

Here a technical description of widgets in the layout XML files taken from the reference XSD file. An XSD file defines what is correct in a corresponding XML file

A widget element must contain a “type” attribute (corresponding to the namespace-qualified class name of the widget).  It may contain other attributes such as an ID as well as other attributes corresponding to simple properties (such as “enabled”).

A widget element may also contain other elements that correspond to compound properties. These compound properties include action (for predefined actions associated with button-like widgets), translatable display string properties such as caption, column headers (for tables), images (which include the URLs to the normal, normal, mouseover, and disabled images for a widget), search fields (for finders) and selectable items (for list boxes).

A widget element may also contain a “handlers” element for JavaScript and default actions widget event handling. That “handlers” element in turn contains a list of “handler” elements.  Each “handler” element has an event  and action attribute that refers to the name of the event and the name of the action that will be executed when the event is triggered.  The “handler” element may also contain a “params” element, which in turn contains a list of “param” elements.  Each “param” element has an id and value attribute that refers to the name and value of a parameter that will be passed in as an additional parameter to the action that is executed when the event is triggered.  Parameters will be passed to the action in the same order in which they are specified in the XML (which is important if it is a java script function being executed by the action).

A “regular” container (“HasWidgets”) widget’s element contains a “children” element.  That “children” element in turn contains a list of widget elements for child widgets.

A grid-type container (“HasWidgetsInGrid”) widget’s element contains a “rows” element.  That “rows” element in turn contains a list of “row” elements, each of which contains a list of “cell” elements.  Each cell is either empty or contains one (child) widget element.

A dock panel widget’s element contains a “dockedWidgets” element.  That “dockedWidgets” element in turn contains a set of docking position elements (e.g. “northWidget” element) for north, south, east, west, and center (in that order).  Each docking position element is either empty or contains one (child) widget element.

A menu bar or menu button widget’s element contains a “menuItemList” element. That “menuItemList” element in turn contains a list of “menuitem” elements, which in turn may contain other “menuItemList” elements (representing submenus).

Before

Below is a screen shot of the Order Entry screen in CRM as it ships un-modified in the product:

Editing the Layout

Within the layout XML files are widget definitions of the form:

 <widget type=”swt:SwtTextBox” id=”orders_ORDNUMBER” enabled=”true” datasourceID=”oeorders” propertyBinding=”ORDNUMBER” width=”170″ style=”swt-TextBox-nohint-noBackground”/>

This is the contents for the base structure of a widget element.  These are the elements we customize.

Changing a Caption

Suppose we want to change the Document Number caption in this form. We can find the XML tag for this widget in the XML file:

<widget type=”swt:SwtLabel” style=”swt-Label documentDetails-LeftRow”>
     <text>
          <transText text=”xDocument Number:” textID=”oe60a_sagecrmorderui_lblDocNumber”/>
       </text>
 </widget>

This references a nice translatable string that exists in the Portal database, but we want to change this to a hard coded string specific for our customer. If we blank out the textID field, then it will use the text specified in the text field, so we can change the above to:

<widget type=”swt:SwtLabel” style=”swt-Label documentDetails-LeftRow”>
     <text>
          <transText text=”Customer Specific Reference:” textID=””/>
       </text>
 </widget>

Once we save this change, then running the Q2O Order Entry screen will show this caption instead of the one from the Portal database.

Adding a Field

The Quote to Order screen is optimized to make it easy for salespeople to enter orders quickly. However suppose the form is too simple and you require your sales people to enter another field that isn’t already on this screen. In the Q2O screen there is the order description field, but no order reference field. So let’s add the reference field to the Document Details tab.

To do this, find the SwtSingleTabPanel with transText: oe60a_sagecrmorderui_pnlDocDetails. A couple of lines below this is a SwtGridPanel with a rowCount of 4, change this rowCount to 6 (since we are adding one row with the label and one with the textbox.

Then add a new <row> at the end of the grid, this will be just before the </rows> tag:

<row>
         <cell>
                <widget type=”swt:SwtLabel”>
                          <text>   <transText text=”Reference:” textID=””/>    </text>
                 </widget>
          </cell>                                 
 </row>
 <row>
            <cell>
                   <widget type=”swt:SwtTextBox” id=”orders_REFERENCE” datasourceID=”oeorders”
                           propertyBinding=”REFERENCE” width=”400″/>
             </cell>                       
 </row>

This will add the controls to the layout. However it won’t work quite yet. This is because the REFERENCE field for the Order Header isn’t provided in the SData feed. However we can add it. To do this we need to edit another XML file, namely: C:\Program Files (x86)\Common Files\Sage\Sage Accpac\Tomcat6\portal\sageERP\oe60a\resourceMap\OEOrderViewMapping.xml. This file among other things defines which Order fields to include in the SData feed. In the <includedFields> section for the header, add the line:

    <resourceViewField viewFieldName=”REFERENCE”       />

This will add the REFERENCE feed to the header. Make sure you add this to the includeFields for the header and not one of the details.

Whenever you change one of the SData definition files, you need to restart Tomcat to have it take effect. After you do this you should see the new field on the form and be able to set and edit it.

After

Here is the screen shot with these two customizations:

Summary

Hopefully this starts to give some idea of how to customize the new screens. If you have the SDK then you can use the SwtUIDesigner to edit these forms; but, hopefully in future versions the visual screen designer will be included in the main product making this process much easier for non-SDK partners to customize.

But at least for now, once you get used to XML you can do a lot of customizations to the current Web based version 6.0A screens.

Written by smist08

November 28, 2010 at 3:00 am

Posted in sage 300

Tagged with , , ,

Setting up Sage ERP Accpac 6.0A Securely

with 7 comments

Sage ERP Accpac 6.0A comes with a new Web Portal (https://smist08.wordpress.com/2009/12/03/the-sage-erp-accpac-6-0a-portal/) along with Web based screen integration to SageCRM (https://smist08.wordpress.com/2009/12/17/sage-erp-accpac-6-0-quote-to-orders/). A common question is how I set this up in a secure manner so that these new features won’t be exploited by hackers.

Most people will probably just setup Accpac 6.0 and/or SageCRM running on their local network. If you don’t expose the web server to the internet, then your security concerns are the same as they are today. You are just regulating what bits of information your local users are allowed to see. Generally (hopefully) you aren’t as worried about your own employees hacking your network. The big concern for security here is usually social engineering (http://en.wikipedia.org/wiki/Social_engineering_(security)) which really requires good education to prevent. Note however that we have seen sites where people have added Internet access for all their employees, but unwittingly exposed their network to the Internet. It’s never a bad time to re-evaluate your current security to see if there are any weaknesses.

For Sage ERP Accpac 6 we’ve taken security very seriously and are involving security consideration into all parts of our Software Development Methodology. Additionally we commissioned a third party security audit of our product. From this audit we then made a number of changes to tighten up our security further. This way we’ve been looking for and being careful about SQL Injection attacks (http://en.wikipedia.org/wiki/SQL_injection) and cross site scripting attacks (http://en.wikipedia.org/wiki/Cross-site_scripting), among others.

For any site you should do some sort of threat risk modeling perhaps like: http://www.owasp.org/index.php/Threat_Risk_Modeling. Generally this sort of exercise gets you thinking about what you are trying to protect and what the possible threats are. Even if you do something simple like:

  • Identify bad guys – young hackers, disgruntled ex-employees, competitors, etc.
  • Identify assets – databases that you want protected, servers that should be secure, etc.
  • Identify risks – having your data stolen, having your website vandalized, having your data modified.

Then you can develop plans to protect your assets and to watch for your adversaries.

A lot of security isn’t a matter of being perfect, just being better than others. This way hackers will come across your web site, quickly see it has security in place and then move on to easier targets. Hackers tend to employ automated scripted scanning tools to search the Internet for unprotected servers (http://en.wikipedia.org/wiki/Port_scanner), just starting by being HTTPS and not having any other ports open, sets the bar quite high for hackers and the scanning tool will keep scanning.

Nmap/Zenmap

When you expose a web server to the Internet, your first line of defense is the firewall. The firewall’s job is to hide all the internally running processes from the Internet, such as SQL Server or Windows Networking. Basically you want to ensure that the only things people can access from the outside are HTTP and HTTPS (these are ports 80 and 443 respectively). This way the only things a hacker can attack are these ports. Generally hackers are looking for other ports that have been left open for convenience like RDP or SQL Server and then will try to attack these.

A great tool to test if any ports have been left open is Nmap/Zenmap (http://www.nmap.org/). You run this tool from outside your network (perhaps from home) to see what ports are visible to the Internet. Below is a screen shot of running this tool against www.yahoo.com. We see that ports 80 and 443 are open as expected but so are ports 25 and 53 (which are for email authentication and DNS). Since there are 4 ports, as a hacker if I have an exploit for any one of these I can give it a try. Obviously the fewer ports open, the better. Ideally only port 443 for HTTPS (though port 80 is often left open to give a better error message to use HTTPS or to redirect people to HTTPS automatically).

It is well worth running Nmap so you don’t have any surprises, especially since configuring firewalls can be complicated.

HTTPS

Now with your site protected so the only thing that can be attacked is the web site, we need to ensure it is only accessed in a secure manner. As a first step we only access it through HTTPS. This encrypts all communications, ensuring privacy and validates that users are talking to the right server avoiding man-in-the-middle attacks (http://en.wikipedia.org/wiki/Man-in-the-middle_attack).

To turn on HTTPS you need a server digital certificate. If you already have one, then great you are all set. If you don’t have one then you can purchase one from companies like VeriSign (http://www.verisign.com/).

To turn on HTTP for a web site in IIS, go to the IIS management console, select the “Default Web Site” and choose “Bindings…” from the right hand side. Then add a binding for https, at this point you need to reference you digital certificate for your server.

As a further step, you should now choose “SSL Settings” in the middle panel and check the “Requre SSL” checkbox. This will cause IIS to reject an HTTP:// requests and only accept HTTPS:// ones.

Other IIS Settings

If you browse the Internet there are many other recommended IIS settings, but generally Microsoft has done some good work making the defaults good. For instance by default virtual directories are read-only so you don’t need to set that. Also remember that Accpac doesn’t store any valuable data in IIS, Accpac only stores some bitmaps, style sheets and static html files here. So if someone “steals” the files in IIS, it doesn’t really matter, this isn’t where your valuable accounting data is stored. We just want to ensure someone can’t vandalize your web site by uploading additional content or replacing what you have there. The valuable data is stored in the database and only accessible through Tomcat, not directly from IIS.

Database Setup

The new Web Portal honors the security settings, set from the Security button in Database Setup. These should be set according to the screen shot below. The most important setting is to disable a user account after x failed password attempts. This prevents automated programs from being able to try every possible password and eventually guessing the correct one. With the settings below and automated program can only try 3 passwords every 30 minutes which will usually get the hacker to move on to find a less secure site to try to hack.

Vigilance

It is generally good practice to remain vigilant. Every now and then review the logs collected by IIS to see if there is a lot of strange activity, like strange looking URLs or login attempts being aimed at your server. If there is, chances are you are being attacked or probed and want to keep an eye on it. If it is very persistent you might want to work with your ISP or configure your Firewall to block the offending incoming IP addresses entirely.

Summary

The important steps are to:

  • Configure IIS for HTTPS (SSL).
  • Disable HTTP (require SSL).
  • Set more stringent security restrictions in Database Setup
  • Do an NMap port scan of your server.

Plus follow normal good IT practices like applying Windows Updates and not running services you don’t need. Practices you should follow whether running a web site or not. Then keep an eye on the IIS logs to see if you are being probed or attacked.

These steps should keep your data and your server safe.

Written by smist08

November 20, 2010 at 4:31 am

Posted in sage 300, Security

Tagged with , ,

How to Best Demo Sage ERP Accpac 6.0A

with 5 comments

We’ve talked a bit about how to install and deploy Sage ERP Accpac 6.0A: https://smist08.wordpress.com/2010/10/02/installing-and-deploying-sage-erp-accpac-6-0a/. When deploying for real you usually have several computers involved, usually a file/web server, a database server and then client workstations. This distributes the total workload over at least three computers, including memory used and processing requirements. But what if you want to demo all the new web components of Accpac 6 on a single self-contained laptop? Here you want everything installed on the laptop and ideally not even require an Internet connection to do the demo. You want the demo to run well, be quick and look good. Fortunately this isn’t hard to set up.

Of course the most important part of a good demo is to tell a compelling story and to involve the customer. To show him how he can save time and money and get work accomplished quicker and more pleasantly. This blog posting doesn’t help with that part, this posting just talks about a number of technical issues to avoid, allowing you to give your demo without anything getting in your way.

Windows 7

Microsoft did a really good job with Windows 7. It fixes most of the bugs from Windows Vista and is ten years more advanced than Windows XP. The bottom line is that Accpac 6 runs twice as fast on Windows 7 as it does on Windows XP. To have a good demo you really want to take advantage of this. If you only take away one tip from this blog posting, let it be this one. The biggest thing that will make the demo better is to use Windows 7.

Further use the 64-Bit version. This allows you to add more memory effectively past 2Gig of RAM. At 2Gig of RAM both the 32 and 64 bit versions run about the same, but with the 32 bit version you can never effectively use more RAM. The 64 bit version lets you expand the RAM to your laptop’s full capacity.

You cannot use a Home edition of Windows to demo with. Both Accpac and SageCRM require IIS be present and this is not available for the Home versions of Windows.

Database

You only want to have one database server running, so use SQL Server. Best to use SQL Server 2008 R2 (may as well use the newest). Since you will probably want other products like SageCRM installed, the common database is SQL Server. SQL Server 2008 is 10-15% faster than SQL Server 2005, so it isn’t the end of the world if you use 2005.

Memory

If you are using 32-Bit Windows then 2Gig is your limit. You won’t get any benefit having any more memory. Accpac 6 will run fine here, but if you are running through a lot of things, it really helps to have 4Gig of RAM, which is pretty standard these days in laptops. And again to use 4-Gig of RAM properly you need to be running a 64 Bit version of Windows (preferably Windows 7).

CPU

For demo purpose the CPU doesn’t make much difference, as long as it isn’t too old. Paying more for a CPU will get you a bit better speed and better multi-threading. Multi-threading is very important for a production server handling many users, but for a single user doing a demo it doesn’t make much difference. That being said if the price difference isn’t too great it doesn’t hurt to be running an Intel i5 or i7 CPU (http://en.wikipedia.org/wiki/Intel_Core).

Screen Resolution

Unfortunately laptop screens tend to be wide but not high. There isn’t much you can do about your laptop’s screen. Most have pretty good resolution but tend to be small. I expect most people will demo using an LCD projector. For some reason when Windows attaches to an external projector it tends to reduce the resolution quite a bit for no reason. The thing to remember is after switching to the projector, go and adjust your screen resolution to the highest setting you can.

SageCRM

One of the biggest new features in Accpac 6.0 is the new SageCRM (http://www.sagecrmsolutions.com/Products/SageCRM) integration called Quotes to Orders. Here you get Accpac Quote and Order screens running inside CRM as web pages. This works far better than previously having the Order Entry VB OCX popping up. There is no problem installing both Accpac and SageCRM on the same laptop for demo purposes. This is why earlier I recommended using SQL Server so both can share the same database service.

Warm up the System

When demo’ing, it’s well worth “warming up” the system before demo’ing it. Web servers expect to be running for a long time and aren’t necessarily optimized for starting quickly. If you run the portal and CRM for the first time when no one is looking then things will go much quicker during the actual demo. This isn’t a case we are going to optimize since in a real environment (hopefully) the web server is reset very infrequently, maybe only for Windows Updates. However laptops are always being turned off and on (see the section on changing IP addresses below).

Virtual PC/VMWare

Virtual images have been a great way to demo in the past. However Accpac 6 runs very slowly in a virtual image on a laptop whether its VMWare (http://www.vmware.com/) or VirtualPC (http://www.microsoft.com/windows/virtual-pc/). Partly this is because virtual images always have less memory than the host computer and partly because of the extra layers involved in accessing the hardware (like the hard drive).

So the advice here is to not demo from a virtual image. Have Accpac 6 installed directly on the laptop.

Changing IP Addresses

Accpac 6 requires a number of web server processes to run including IIS and Apache Tomcat. These programs were designed to run on servers, which typically don’t move around much and have fixed IP addresses. These programs really don’t like network changes. Even moving around the different WiFi zones of an office can cause them to get “confused” and stop working.

The safest way to run is to either have networking turned off, or turn on your computer from scratch at the location of the demo. I’ve also experienced that intermittent WiFi can cause pauses in computer operation, so if the WiFi isn’t reliable, turn off the laptop’s radio.

If you do have to move around and things stop working then you need to stop and start the IIS and Sage Accpac Tomcat Windows services.

SSD Drives

I’ve heard SSD Drives (http://en.wikipedia.org/wiki/Solid-state_drive) can make a big difference, but I don’t have one, so I can’t speak from experience. If you have one give it a try, I suspect it will really help.

Summary

My primary advice is to use a laptop running Windows 7 64-Bit with at least 4Gig of RAM. With this you will be well positioned to give a great demo. Plus turn the computer on from scratch and warm it up. Any of the other things mentioned here, will help, but won’t have nearly as big and effect.

Written by smist08

November 13, 2010 at 8:55 am

Posted in Business, sage 300

Tagged with ,

Entering Orders and Header/Detail Views

with 20 comments

Sage ERP Accpac Business Logic objects are called Views. With these Views you can use various interfaces to input data, retrieve data, perform calculations or perform business processes. The interface to every View is the same and this allows us to offer many different interfaces to the Accpac Business Logic (see:  https://smist08.wordpress.com/2010/09/18/a-short-history-of-the-accpac-view-interface/).

Overview of the O/E Views

The Views in this posting are to do with entering and reading O/E Orders. Each View sits on a corresponding database table. Below are some of the Views and tables that make up an order.

Table                     View                      Description
OEORDH              OE0520                 Order Headers
OEORDD              OE0500                 Order Details
OECOINO            OE0180                 Order Comments
OETERMO           OE0740                 Order Pmt Schedules
OEORDQ              OE0526                 Order from Quotes
OEORDHO           OE0522                 Order Optional Fields
OEORDDO           OE0501                 Order Detail Optional Fields
OEORDDD           OE0502                 Order Kitting Details
OEORDDDS         OE0504                 Order Kitting Serial Numbers
OEORDDDL         OE0506                 Order Kitting Lot Numbers
OEORDDB            OE0503                 Order BOM Details
OEORDDS            OE0508                 Order Detail Serial Numbers
OEORDDL            OE0507                 Order Detail Lot Numbers

These Views then work together to manage Orders. These Views form header/detail relationships with each other and Accpac has standard protocols for manipulating header/detail Views. These are documented in the macros section of the System Manager User Guide. You don’t need to use all the Views all the time. If you aren’t using serial numbers or lots then you don’t need to worry about those Views. Mostly you use the Order Header and Order Details Views. To make the Views work together you need to compose them. You can get the list of Views that compose together from SDK tools, the Application Object Model on our Web site or you can get the code generated for you by recording a macro.

These Views manage the data in the database. They ensure data integrity is maintained. They control security. But additionally they will do calculations. For instance you can ask the order header calculate taxes or do a ship all.

Header/Detail

As an example of header/detail we have the Order Header and then its detail lines. The Order header has information that there is one of for the order. The details then contain data that there are many of. The Order Header contains information like the customer id and the ship-to address. Then each detail line contains an I/C Item number and quantity. So for an order you can order as many items as you like.

The Order Header also stores information like various totals. So as each detail line is added, updated or deleted these header totals have to be updated to keep them correct. Details are always associated with their header by having their primary key start with the header’s primary key, then just adding a uniquifier or counter. This is show in the diagram below:

To use header detail views you need to follow what we call View protocols. Generally they go along the following lines:

  1. Open and compose all the Views
  2. Either create or read the correct header record
  3. Insert/update or delete which ever detail lines you are dealing with.
  4. Update or Insert the header record.

Nothing is actually done to the database until you insert or update the header. Until this is done all changes are maintained in memory. Then when you update/insert the header, all changes are written to the database in a single database transaction. This ensures the database’s integrity is maintained where the details always match the header. This protocol also handles any multi-user situations that could arise.

Although Order Entry could potentially involve 12 Views, the basic protocols and interactions all follow this simple model. The Views themselves will also report errors if you do things incorrectly.

Order Entry Macro

Below is the VBA source code of an Accpac macro that enters Orders into Order Entry. The code to open and compose the Views was obtained by macro recording. The code to enter 1 order was also from macro recording but then modified into functions to be made re-usable. This code uses the Accpac COM API, but if you use the .Net interface or Java JNI, then the code will be very similar with just syntax differences for the calling language. Entering orders via SData would be quite different since you just provide the data in an SData XML payload, and the SData server handles all the View opening and composing automatically. The SData server then knows the View header/detail protocol and takes the data from the XML and feeds it into the Views via the algorithm indicated.

The macro below enters some optional fields. There are two optional field views. One is a detail of the header, meaning that you can have any number of optional fields for each order. The second one is a detail of the order detail View, meaning you can have any number of optional fields for each order detail line. Each of these is handled using the header detail protocol, but where the detail optional fields has the order detail View has its header.

This macro shows entering orders, but exactly the same mechanisms, logic and protocols would be used to enter any header/detail document in Accpac whether in G/L, A/R or P/O. So learning to do this once has great benefit towards your second program.

‘ Macro to enter orders into O/E
‘ Global variables for database links and views that are needed in most routines.
Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Dim mDBLinkSysRW As AccpacCOMAPI.AccpacDBLink
Dim OEORD1header As AccpacCOMAPI.AccpacView
Dim OEORD1headerFields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail1 As AccpacCOMAPI.AccpacView
Dim OEORD1detail1Fields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail2 As AccpacCOMAPI.AccpacView
Dim OEORD1detail2Fields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail3 As AccpacCOMAPI.AccpacView
Dim OEORD1detail3Fields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail4 As AccpacCOMAPI.AccpacView
Dim OEORD1detail4Fields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail5 As AccpacCOMAPI.AccpacView
Dim OEORD1detail5Fields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail6 As AccpacCOMAPI.AccpacView
Dim OEORD1detail6Fields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail7 As AccpacCOMAPI.AccpacView
Dim OEORD1detail7Fields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail8 As AccpacCOMAPI.AccpacView
Dim OEORD1detail8Fields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail9 As AccpacCOMAPI.AccpacView
Dim OEORD1detail9Fields As AccpacCOMAPI.AccpacViewFields
Dim OEORD1detail10 As AccpacCOMAPI.AccpacView
Dim OEORD1detail10Fields As AccpacCOMAPI.AccpacViewFields
 
Sub MainSub()
 
On Error GoTo ACCPACErrorHandler
 
    Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
    Set mDBLinkSysRW = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)
   
    OpenAndComposeOrderViews
 
    ‘ In sample data we have set a price list of USA for customer 1200 location WAREHS so that prices are
    ‘ filled in automatically (A/R Shipto Locations).
 
    EnterOrder “1200”, “WAREHS”, Array(“A1-310/0”, “A1-400/0”, “A1-105/0”), Array(“5”, “4”, “6”), Array(“Red”, “White”, “Blue”)
 
    Cleanup
 
Exit Sub
ACCPACErrorHandler:
    MyErrorHandler
End Sub
 
‘ Open and componse the various views used in this macro. Code is straight from macro recording.
Sub OpenAndComposeOrderViews()
On Error GoTo OpenAndComposeOrderViewsErrorHandler
 
mDBLinkCmpRW.OpenView “OE0520”, OEORD1header
Set OEORD1headerFields = OEORD1header.Fields
mDBLinkCmpRW.OpenView “OE0500”, OEORD1detail1
Set OEORD1detail1Fields = OEORD1detail1.Fields
mDBLinkCmpRW.OpenView “OE0740”, OEORD1detail2
Set OEORD1detail2Fields = OEORD1detail2.Fields
mDBLinkCmpRW.OpenView “OE0180”, OEORD1detail3
Set OEORD1detail3Fields = OEORD1detail3.Fields
mDBLinkCmpRW.OpenView “OE0680”, OEORD1detail4
Set OEORD1detail4Fields = OEORD1detail4.Fields
mDBLinkCmpRW.OpenView “OE0526”, OEORD1detail5
Set OEORD1detail5Fields = OEORD1detail5.Fields
mDBLinkCmpRW.OpenView “OE0522”, OEORD1detail6
Set OEORD1detail6Fields = OEORD1detail6.Fields
mDBLinkCmpRW.OpenView “OE0501”, OEORD1detail7
Set OEORD1detail7Fields = OEORD1detail7.Fields
mDBLinkCmpRW.OpenView “OE0502”, OEORD1detail8
Set OEORD1detail8Fields = OEORD1detail8.Fields
mDBLinkCmpRW.OpenView “OE0504”, OEORD1detail9
Set OEORD1detail9Fields = OEORD1detail9.Fields
mDBLinkCmpRW.OpenView “OE0503”, OEORD1detail10
Set OEORD1detail10Fields = OEORD1detail10.Fields
 
OEORD1header.Compose Array(OEORD1detail1, OEORD1detail4, OEORD1detail3, OEORD1detail2, OEORD1detail5, OEORD1detail6)
OEORD1detail1.Compose Array(OEORD1header, OEORD1detail7, OEORD1detail10, OEORD1detail8)
OEORD1detail2.Compose Array(OEORD1header)
OEORD1detail3.Compose Array(OEORD1header, OEORD1detail1)
OEORD1detail4.Compose Array(OEORD1header, OEORD1detail1)
OEORD1detail5.Compose Array(OEORD1header)
OEORD1detail6.Compose Array(OEORD1header)
OEORD1detail7.Compose Array(OEORD1detail1)
OEORD1detail8.Compose Array(OEORD1detail1, OEORD1detail9)
OEORD1detail9.Compose Array(OEORD1detail8)
OEORD1detail10.Compose Array(OEORD1detail1)
 
Exit Sub
OpenAndComposeOrderViewsErrorHandler:  MyErrorHandler
   
End Sub
 
‘ Enter an order based on a number of parameters.
Sub EnterOrder(ByVal custno As String, ByVal shipto As String, ByVal items As Variant, ByVal quantities As Variant, ByVal colors As Variant)
    Dim lastLine As Long
On Error GoTo EnterOrderErrorHandler
   
    OEORD1header.Cancel
    OEORD1header.Init
 
    OEORD1headerFields(“CUSTOMER”).Value = custno                         ‘ Customer Number
    OEORD1headerFields(“SHIPTO”).Value = shipto                           ‘ Ship-To Location Code
   
    ‘ Insert any auto insert optional fields.
    OEORD1headerFields(“PROCESSCMD”).PutWithoutVerification (“1”)         ‘ Process Command
    OEORD1header.Process
           
        ‘ Set the CLERK optional field in the Order Header
        OEORD1detail6.RecordClear
        OEORD1detail6.RecordGenerate False
        OEORD1detail6Fields(“OPTFIELD”).Value = “CLERK”
        OEORD1detail6Fields(“VALIFTEXT”).Value = “Sammy ” + CStr(Date) + ” ” + CStr(Time)  ‘ Text Value
        OEORD1detail6.Insert
       
   
    For i = LBound(items) To UBound(items)
   
        lastLine = OEORD1detail1Fields(“LINENUM”).Value
        OEORD1detail1.RecordClear
        OEORD1detail1Fields(“LINENUM”).PutWithoutVerification (CStr(lastLine))          ‘ Line Number
        OEORD1detail1.RecordGenerate False
       
        ‘ Set the item code and quantity.
        ‘   Note that all the other fields are readonly until the ITEM is set.
        OEORD1detail1Fields(“ITEM”).Value = items(i)                                    ‘ Item
        OEORD1detail1Fields(“QTYORDERED”).Value = quantities(i)                         ‘ Quantity Ordered
       
        ‘ Insert any auto insert optional fields
        OEORD1detail1Fields(“PROCESSCMD”).PutWithoutVerification (“1”)                  ‘ Process Command
        OEORD1detail1.Process
       
            ‘ Add the COLOR optional field to the detail.
           ‘ OEORD1detail7.RecordClear
           ‘ OEORD1detail7.RecordGenerate False
            OEORD1detail7Fields(“OPTFIELD”).Value = “COLOR”
            OEORD1detail7.Read ‘ Optional Field
            OEORD1detail7Fields(“VALIFTEXT”).Value = colors(i)                        ‘ Text Value
            OEORD1detail7.Update
       
        OEORD1detail1.Insert
 
    Next i
 
    OEORD1headerFields(“GOCHKCRDT”).Value = “1”                           ‘ Perform Credit Limit Check
    OEORD1header.Process
    OEORD1header.Insert
Exit Sub
 
EnterOrderErrorHandler:
 MyErrorHandler
   
End Sub
 
‘ Cleanup routine destroys the COM objects in the reverse order that they were created.
Sub Cleanup()
On Error Resume Next
 
Set OEORD1detail10Fields = Nothing
Set OEORD1detail10 = Nothing
Set OEORD1detail9Fields = Nothing
Set OEORD1detail9 = Nothing
Set OEORD1detail8Fields = Nothing
Set OEORD1detail8 = Nothing
Set OEORD1detail7Fields = Nothing
Set OEORD1detail7 = Nothing
Set OEORD1detail6Fields = Nothing
Set OEORD1detail6 = Nothing
Set OEORD1detail5Fields = Nothing
Set OEORD1detail5 = Nothing
Set OEORD1detail4Fields = Nothing
Set OEORD1detail4 = Nothing
Set OEORD1detail3Fields = Nothing
Set OEORD1detail3 = Nothing
Set OEORD1detail2Fields = Nothing
Set OEORD1detail2 = Nothing
Set OEORD1detail1Fields = Nothing
Set OEORD1detail1 = Nothing
Set OEORD1headerFields = Nothing
Set OEORD1header = Nothing
Set mDBLinkSysRW = Nothing
Set mDBLinkCmpRW = Nothing
 
End Sub
 
‘ Simple generic error handler.
Sub MyErrorHandler()
  Dim lCount As Long
  Dim lIndex As Long
 
  If Errors Is Nothing Then
       MsgBox Err.Description
     Else
      lCount = Errors.Count
 
      If lCount = 0 Then
          MsgBox Err.Description
      Else
          For lIndex = 0 To lCount – 1
              MsgBox Errors.Item(lIndex)
          Next
          Errors.Clear
      End If
  End If
 
End Sub
 

Written by smist08

November 7, 2010 at 12:53 am

Posted in sage 300

Tagged with , , , , ,