Stephen Smith's Blog

Musings on Machine Learning…

Posts Tagged ‘Web Services

Sage 300c Web Services

with 16 comments

Introduction

Hand in hand with true HTML/JavaScript/CSS Web UI’s you also want to access the same logic from other general programs using RESTful Web Services. This gives a general API to access the application which doesn’t require any Sage 300 components be installed on the client computer and doesn’t require the calling application be on the same computer or even at the same location.

ASP.Net MVC Web screens tend to have quite quickly changing interfaces between the Views, Controllers and Models which makes using then same Web Services as the UI a bit problematic, especially as the screens evolve quickly. You want a stable Web Services interface that preserves compatibility from version to version and provides a wider more general interface. At the same time the developer of an ASP.Net MVC program doesn’t want to do a completely different implementation for exposing Web Services.

The way ASP.Net MVC solves this dilemma is by allowing you to add a Web Services stack on top of your existing models (which in our case means fully leveraging the business repositories and Sage 300 Business Logic as well). But it uses a custom controller to handle the Web Services requests. In the Microsoft stack there are several supported standards for Web Services, but the one we used is OData. This means that using our Web Services you can do all the standard OData queries and supports the standard OData meta-data.

With our Sage 300 2016 Product Update 1 we have included a number of Web Services in the product. These are automatically installed if you select the Web UIs option from the main installation. So if the Web UIs are up and running then you can try playing with the Web Services. In this article we’ll show how to get started using these. Over next couple of releases, we’ll be fleshing these out to support all the Business logic as well as services beyond the basic CRUD operations.

Some Examples

If you type:

https://yourservername/Sage300webapi/sdata/-/SAMLTD/GL/Accounts

into the Chrome browser you will be prompted for your Sage 300 login credentials, which you can enter. Note that from this browser prompt the password is case sensitive, so you need to uppercase your normal Sage 300 password (since our regular login screen normally does this).

webserv1

Then after entering the correct data you will get back a JSON object with all the information in your chart of Accounts (including details like optional fields):

webserv2

Working with the Browser directly, although fun, will soon become tedious. Another easier approach is to install the Chrome add-in PostMan which will remember your Web Services so you can adjust and repeat them. You need to set the Basic Authorization header with your Sage 300 login and password. Below we use the shortened URL to get the list of all the available feeds for SAMLTD with the URL:

https://yourservername/Sage300webapi/sdata/-/SAMLTD

using PostMan:

webserv3

And we get the returned JSON object containing the list of Web Services we support. It by company since not all accounting application may be activated in the database.

Queries

You can do standard OData queries to filter the returned data. For instance:

https://yourservername/Sage300webapi/sdata/-/SAMLTD/GL/Accounts?$filter=UnformattedAccount eq ‘1020’

will result in just this one account being returned:

webserv4

The way we implement queries is via adding LINQ support that will convert the LINQ query to a Browse filter for our Sage 300 View. This means we will support any query as long as we can translated it into a Browse filter. If the filter contains a SQL function we don’t support, then you will get back a not supported error for your query. Note that often people writing code for the regular Web UIs just use our C# LINQ support to browse/fetch rather than calling browse/fetch directly since this lets you leverage other advanced features in C# and .Net.

Other Clauses

You can specify a sort order as long as what you requests matches an index in the Sage 300 database:

https://yourservername/Sage300webapi/sdata/-/SAMLTD/GL/Accounts?$orderby=UnformattedAccount desc

webserv5

You can specify to get the top n records or to skip n records via:

https://yourservername/Sage300webapi/sdata/-/SAMLTD/GL/Accounts?$top=2

https://yourservername/Sage300webapi/sdata/-/SAMLTD/GL/Accounts?$skip=2

which is useful to page data.

Meta Data

You can get meta data for all the feeds using the $metadata tag. For instance:

https://yourservername/Sage300webapi/sdata/-/SAMLTD/$metadata

will return the meta data for all the feeds that are relevant for SAMLTD:

webserv6

(Note that this is quite a large JSON object to process).

Updating/Inserting/Deleting

This initial implementation includes sufficient G/L feeds for supporting financial reporting. Hence these G/L feeds are read only at this point. We do support inserting G/L Batches, O/E Orders and A/R Customers. Many of the non-G/L feeds support updating, inserting and deleting. If the entity supports these then you can delete the record by specifying DELETE as the HTTP verb (which is easy in PostMan), similarly insert if via POST and update if via PUT or PATCH.

Generally, the best way to figure out the format of the payload to include with these is to do a GET and then use that payload as a template to build the JSON object with the data you want to update or insert.

Since these Web APIs are built on the Sage 300 Business Logic all the usual validation will take place and you will get back Sage 300 error messages in the response payload if the request fails.

Troubleshooting

Ideally the responses from the server will include error messages to tell you what went wrong, so always check these. If they aren’t helpful, then on your web server check the Web API trace log which is located at:

Sage300InstallDir/Online/WebApi/Logs/trace.log

This will usually have the raw error when something has gone wrong.

If you don’t see anything in either of these places, perhaps check your IIS log to make sure that the request didn’t get rejected for some other reason. Especially remember to include your basic authorization header.

Security

If you expose your Web Services to the general Internet, ensure that you follow all the security measures in this article. You will need to do this if you are integrating with an external cloud service or other client located outside your network. Generally, you want to keep your Web Service communications private, so they can’t be accessed by hackers or spied on by hackers. Using good practices around enforcing HTTPS is crucial here.

Summary

The set of Web Services included in the Sage 300 2016 Product Update 1 are intended to support Financial Reporting on General Ledger as well as basic e-Commerce functionality like accessing Customers and entering Orders. Part of the intent of this release is to let people play with these and provide feedback as we move to complete out the full set of Web Services for our next version.

Advertisements

Written by smist08

February 15, 2016 at 11:26 pm

Defining SData Feeds for Sage 300 ERP

with 8 comments

Introduction

We introduced SData support with Sage ERP Accpac 6.0A; however, the product as it shipped only defined a few SData feeds that it needed to support the new Web Portal, Data Snapshots, Inquiry Tool and Quotes to Orders features. But Sage 300’s support for SData is based on converting SData Web Service requests into View calls. So it is possible to expose any View (or collection of composed Views) as an SData feed.

In a future version of Sage 300 ERP we will expose all the relevant Views via SData, but in the meantime if you want to use SData with Sage 300, then you need to provide XML files to define the feeds you need.

All the feed definitions are XML files, which means you can read all the existing ones that come with Sage 300 using a normal text editor. Hence you can use the existing ones either as examples or templates for the definitions you need.

One thing to be careful of is that most of the fields in these XML files are case sensitive. This means they must match exactly or they won’t work. When things don’t work, it’s worth looking in the Tomcat\log folder at the relevant SDataServlet.log as this will often point out errors when parsing the XML files.

Class Map Files

The classmap files are a series of XML files located in the sub-folders under: C:\Program Files (x86)\Common Files\Sage\Sage Accpac\Tomcat6\portal\sageERP. The feeds for a given application are stored under the application’s program id and version id directory such as oe60a. Note that these need to be in a folder for an activated application to be read, but within an application you can define feeds that access Views in any application.

All the configuration XML files are loaded into memory by the SDataServlet on startup. So if make any changes to these files, you need to restart the “Sage Accpac Tomcat6” service for your changes to take effect.

You can use these to define custom Java classes to process the SData requests, I’ve covered this a bit in other blog postings, but won’t go into that here, since this article is only considering what can be done by editing XML files.

The classmap defines each SData feed and specifies the class to handle the feed and then a detailed feed definition file called a resourceMap file.

Example – Currency Codes

The currencyCodes resource is implemented by the Java class: ViewResourceKind and is defined by the resource map file: currencyCodeViewMapping.xml. ViewResourceKind is a system provided Java Class for generically converting SData requests into View calls. You can use this to expose most Views (that have data) as SData feeds.

Classmap.xml

<classmap>
<contracts>
<contract name=”accpac”>
<resource name=”currencyCodes” className=”com.sage.orion.sdata.servlet.accpac.ViewResourceKind>
<parameters>
<parameter name=”ResourceMapFile“ value=”currencyCodeViewMapping.xml”/>
</parameters>
</resource>
</contract/>
</contracts>
</classmap>

If you aren’t programming server classes then this is all you need to know about the classmap files.

Resource Map File

Maps an SData resource to a backing family of Accpac Views and fields. These are stored in the resourceMap folder under the folder that holds the classmap file.

By default all fields from the view are exposed as SData Resource Elements.

Has the ability to exclude or include or overwrite Sage 300 fields from the SData resource.

Example – Currency Codes

The currencyCodeViewMapping.xml resource map file contains the following:

<resource name=”currencycode” description=”Currency Codes”>
<viewID>CS0003</viewID>
<pluralName>currencycodes</pluralName>

<includedFields>
<resourceViewField viewFieldName=”CURID” />
<resourceViewField viewFieldName=”CURNAME” name=”Description”/>
<resourceViewField viewFieldName=”DECIMALS” />
<resourceViewField viewFieldName=”SYMBOLPOS” />
<resourceViewField viewFieldName=”THOUSSEP” />
<resourceViewField viewFieldName=”DECSEP” />
<resourceViewField viewFieldName=”NEGDISP” />
<resourceViewField viewFieldName=”SYMBOL” />
</includedFields>
</resource>

The key points are the viewID that maps this feed to the currency codes view CS0003. The URI of the feed is the plural name, namely currencyCodes. Then you can specify the list of fields you want included in the feed. You might specify a shorter list of fields to keep the size of the feed to a minimum. The includedFields section is optional.  I tend to prefer using an excludedFields section to just list the fields I don’t want.

Example – Single Level Resource

SData resource “customer” is defined from the view AR0024.

<resource name=”customer” description=”AR Customers”>
<viewID>AR0024</viewID>
<pluralName>customers</pluralName>
</resource>

Example –  Multi-Level Resource

SData resource ‘arInvoiceBatch’ is defined from a set of composed views – AR0031, AR0032, AR0033, AR0041 and AR0034.

<resource name=”arInvoiceBatch” description=”AR Batches”>
<viewID>AR0031</viewID>
<pluralName>arInvoiceBatches</pluralName>
<resources>
<resource name=”invoice” description=”AR Invoice”>
<kind>arInvoice</kind>
<viewID>AR0032</viewID>
<resources>
<resource name=”detail” description=”AR Invoice Details”>
<kind>arInvoiceDetail</kind>
<viewID>AR0033</viewID>
<resources>
<resource name=”optional” description=”AR Invoice Detail Optional Fields”>
<kind>arInvoiceDetailOptional</kind>
<viewID>AR0401</viewID>
</resource>
</resources>
</resource>
<resource name=”schedule” description=”AR Invoice Payment Schedules”>
<kind>arInvoiceSchedule</kind>
<viewID>AR0034</viewID>
</resource>
</resources>
</resource>
</resources>
</resource>

Resource Map File Details

Resource Mapping File Attributes / Elements:

name: name of the resource

description: description of the resource.

viewID: The ViewID of the resource. Remember you can get further information on the Views from the Sage 300 ERP Application Object Model (AOM).

pluralName: plural name of the resource.  If undefined then the pluralName = name +”s”.  This will be the URI of the resource.

resources: Collection of sub-resource elements

kind: resource kind name of the resource. Top resource of the resource tree the resource name and the resource kind name should be the same. However for a sub-resource the resource name reflects the name of the property that refers to it in the parent while the kind name is the name that it appears as at the top level of the schema.

includedFields: list of resourceViewFields that are to be included in the resource (by default all fields are included)

excludedFields: list of resourceViewFields that are not to be included in the resource

overridenFields: list of resourceViewFields that are to be overriden in the resource (usually done to change the SData name)

virtualFields: list of resourceViewFields that are to be added to the resource. Note: virtual fields requires extending ViewResourceKind with a custom class that implements these virtual fields.

lookupFields: These are a 6.1A feature that allow you to add fields looked up from another view like to get a description.

Summary

Hopefully this article gives an idea of how to setup additional SData feeds for Sage 300 ERP, without requiring any programming.

Written by smist08

March 3, 2012 at 11:57 pm

More on SData and Sage ERP Accpac 6

with 11 comments

SData is Sage’s new Web Services standard. It is documented in full at http://sdata.sage.com/. For Sage ERP Accpac 6.x, SData is the interface used by the browser based UI pages to communicate with the Server. Like in Accpac 5.X where the Accpac COM API was the interface used for all UI to Business Logic communication, this role is taken over by SData in 6.x. This means that there is a complete robust REST based Web Services interface to the Sage ERP Accpac application. Anything that can be done from an Accpac User Interface Web Page can also be done via any other program making SData Web Services calls.

REST based Web Services are very simple in how they are constructed, they are built using current Web Standards such as HTTP and Atom. For instance the screen shot below shows typing:

http://bcraccdemo/SDataServlet/sdata/sageERP
/accpac/SAMINC/Customers(‘1200’
)

into the URL field for the Chrome Browser.

This URL is an SData request, it is sent to the server bcraccdemo and the application SDataServlet running there. The rest of the URL is then processed by the Accpac SData provider to return the correct data for customer 1200 (Ronald Black) in XML Atom format, which is then displayed in the Browser window. As you can see, no special infrastructure or programming libraries were required to make this Web Service call. All that was needed was the ability to send an HTTP request and receive the response.

SData is also self describing. Below is a screen shot of asking for the schema of the XML response given above.

When doing update or insert calls you need to provide an XML payload of data similar to the response above of the data to be updated or inserted.  There is also a standard interface for doing services which in Accpac includes things like reporting, day end and posting.

The example above was for customer 1200, however if the (‘1200’) part was removed, SData would have returned all the customers in A/R. In this case you can specify a filter to say return all customers greater than some point or specify which fields to sort the returned data by. SData will also page the data returned, it will return a block of records with links to the previous and next blocks.

Basically SData is a fairly simple protocol to use that should be accessible to almost any programming system. It is rich enough to support all UI operations required by Accpac. And ISV’s can be confident that it is complete enough, since all Accpac UIs rely on it. It is standards based on HTTP, Atom and other Web Standards allowing standards based tools to be used. Generally a very powerful Web Based Interface to greatly enhance Accpac’s intergratability and extensibility. Since SData is being adopted by all Sage Business Applications, learning and using SData with Accpac will teach you how to use SData for any SData equipped application, such as Sage CRM.

Written by smist08

January 18, 2010 at 11:45 pm

Posted in SData

Tagged with , , ,