What is the Epicor ICE SDK?

A couple people have asked me if I know what the Epicor ICE SDK (aka. Vantage SDK) is.

Here is all I know about the Software Developer's Kit:

1. It allows you to customize the Epicor itself by adding new tables, business objects, and forms.
   
   
2. It seems to be geared more for developing features which run inside of Epicor, as opposed to integrating Epicor with other systems.
   
   
3. There is no trial version to try before you buy.
   
   
4. The best place to find out more is to ask your sales rep. He or she should be able to email you some PDFs regarding the capabilities.
   
   
5. It's not cheap.
   

If anyone has any experience with it, please comment below, or, if you have something more to say, I can post an entry for you.

Set ReportParam in Epicor Customization

In Epicor, I needed to customize the Sales Order Pick List report screen so it defaults to a wider From and To Ship By date range. In customization, this means updating the ReportParam value when the form loads:


private void SOPickListForm_Load(object sender, EventArgs args)
{
// wrong approach, change data source of control
// EpiTokenDate tdtFrom = (EpiTokenDate) csm.GetNativeControlReference("1f043839-c70e-41e0-8d1b-c33fd3d7e3c5");

// change from date
EpiDataView epiDataView = (EpiDataView) oTrans.EpiDataViews["ReportParam"];
epiDataView.dataView[epiDataView.Row]["FromDate"] = DateTime.Now.AddMonths(-6);
}


Normally, to access the text field for a control in a customization you use csm.GetNativeControlReference. But, in this case, I needed to change the ReportParam, which is accessed through oTrans.EpiDataViews["ReportParam"] dataset.

Get Results of Epicor BAQ as DataSet in C#

One of the easiest, and fastest ways to get data out of Epicor is to use C# and the Epicor client library assemblies. This article provides sample code to export the results of a business activity query created using the BAQ Designer into a C# .Net framework dataset.

Step 1 - Add References
In you project, at a minimum you will need to reference the following assemblies:

-Epicor.Mfg.BO.DynamicQuery
-Epicor.Mfg.IF.IDynamicQuery
-Epicor.Mfg.Core.BLConnectionPool
-Epicor.Mfg.Lib.ISessionMod
-Epicor.Mfg.Lib.SessionMod


DynamicQuery is the business object to manage BAQs (it replaces QueryDesignDataSet in Vantage). BLConnectionPool & SessionMod are necessary to explicitly set the company for the session. This is important, because otherwise the company will be whatever company was last selected for the user account used to create the session, which can yield unpredictable results.

Step 2 - Add class to create business object
The following is my class to create Epicor business objects for me:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;

using Epicor.Mfg.Core;
using Epicor.Mfg.Proxy;
using Epicor.Mfg.BO;
using Epicor.Mfg.Lib;

namespace ExecuteDynamicQuery
{
public class BOFactory
{
private BLConnectionPool m_cnVantage = null;
private static string m_strKeyVantageServer = "VantageServer";
private static string m_strKeyVantagePort = "VantagePort";
private string company;
private string companyName;
private string plantID;
private string plantName;
private string workstationID;
private string workstationDescription;
private string employeeID;
private string countryGroupCode;
private string countryCode;

public BOFactory(string company)
{
this.company = company;
string user = ConfigurationSettings.AppSettings["UserName"];
string password = ConfigurationSettings.AppSettings["Password"];

m_cnVantage = new BLConnectionPool(user, password, "AppServerDC://"
+ ConfigurationSettings.AppSettings[m_strKeyVantageServer] + ":"
+ ConfigurationSettings.AppSettings[m_strKeyVantagePort]);

SetCompany(company);
}

///

/// Returns the DyanmicQuery object which manages BAQs
///

///
public Epicor.Mfg.BO.DynamicQuery GetDynamicQueryBO()
{
return new Epicor.Mfg.BO.DynamicQuery(m_cnVantage);
}

///

/// returns the session mod
///

///
public Epicor.Mfg.Lib.SessionMod GetSessionMod()
{
SessionMod sessionMod = new SessionMod(m_cnVantage);
return sessionMod;
}

///

/// changes the company associated with the current session
///

///
public void SetCompany(string company)
{
this.company = company;
Epicor.Mfg.Lib.SessionMod sessionMod = GetSessionMod();

sessionMod.SetCompany(company,
out companyName, out plantID, out plantName, out workstationID, out workstationDescription,
out employeeID, out countryGroupCode, out countryCode);
}
}
}

Step 3 - Add the call to your dynamic query

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ExecuteDynamicQuery
{
class Program
{
static void Main(string[] args)
{
string company = "12";
string queryID = "12-MyQuery";
string orderNum = "726250";

BOFactory factory = new BOFactory(company);
var dynamicQuery = factory.GetDynamicQueryBO();

// get query
var queryDesignDataSet = dynamicQuery.GetByID(queryID);

// get only specific order
// QueryWhereItem is a criteria on OrderHed table
queryDesignDataSet.QueryWhereItem[0]["RValue"] = orderNum;

// execute query
DataSet ds = dynamicQuery.Execute(queryDesignDataSet);

// print results
for (int i = 0; i < ds.Tables[0].Columns.Count; i++ )
{
Console.Write(ds.Tables[0].Rows[0][i].ToString());
}
}
}
}


Above, I'm passing in an order number as the constant value to a criteria that already exists on the OrderHed table in the design of the query.

Epicor Customizations and Personalizations

If a user has a personalization for an existing form in Epicor, and you create a new customization, as soon as you "deploy" the customization by linking it to the form in Menu Maintenance, the user's personalization will be ignored.

Personalizations are always based on either the default form, or a customization (referred to as the parent layer).

To hook the personalization to the new customization you must:
1. Export the personalization to an .xml file by either:
a) Using System Maintenance, Utilities, Customization Maintenance (sic)
b) As the user, open the personalization designer, and choose Export button.

2. Attach the new customization to the menu.

3. As the user, log into Epicor, open the form (customization should now be loaded), start personalization, and click Import button.

Note that the only way associate the personalization being importing to the new customization is to log in as the user. You can not do this from any other account.

That said, Epicor does not recommend re-importing personalizations under a different customization. Instead, they suggest the user should recreate the personalization, because moving the personalization to the new customization may yield unexpected results. This is because part of the user's personalization is not actually exported to the personalization xml file, but instead is saved locally on the user's computer (such as the location of certain grids).

Epicor Replication - Fully-Functional Database

Epicor Replication allows you to replicate one or more tables from Epicor into a seperate SQL database.

Because we use OpenEdge for our Epicor database, we use replication to first export the data to an SQL database, before we import into our data warehouse, or as a source for other applications.

When you select the replicated database, you have two choices, ad-hoc or fully-functional. An Ad-hoc database starts empty, and the replication server will create the table schemas only for the replicated tables. A fully-functional database has all tables, view, stored procedures, etc. predefined, and can act as a read-only database for the Epicor client (or so they say, I haven't tried).

However, there is another, undocumented difference. The tables created in an ad-hoc database are different than the tables predefined in the fully-functional database. Specifically, a Character01 field in an ad-hoc database automatically gets created as an NTEXT column. NTEXT columns can not be used in comparison operations, which can be very restricting.

The same table in a fully-functional database has a Character01 field defined as a VARCHAR, which is much more useful.

So, where do you get a copy of a fully-functional database create script?

In your Epicor installation, find your Epicor905 folder, and then go to Epicor905\db with install\newdb\Epicor90564.sql.

Delete Rows in Epicor DeleteById Alternative

Epicor DeleteById vs. Update (RowMod="D")

In my experience, deleting rows from Epicor through the business objects works differently between objects.

For some objects, you can call .DeleteById, such as PartService.DeleteById and the row will be removed from Epicor database.

For other objects, .DeleteById fails (CustShipService.DeleteByID, SaleOrder.DeletedById, ...) sending to you check the AppServer log file, which states something like: "No ttOrderHead record is available", "Error attempting to push run time paramters onto the stack.".


In these cases, try deleting using the Update/RowMod option.
Call .Update (different from MasterUpdate, UpdateExt, etc.) passing in the appropriate dataset. You don't need the entire dataset returned by .GetById. Just pass in the element which has the unique ID for the business objects, and in that element, set RowMod="D".

For SalesOrder, my Service Connect trace looks like this:

<ext_UpdateRequest:UpdateRequest xmlns:ext_UpdateRequest="http://Epicor.com/SalesOrder/UpdateRequest">
<ext_UpdateRequest:loginOptions>EPIC03</ext_UpdateRequest:loginOptions>
<ext_UpdateRequest:ds>
<ext_UpdateRequest:OrderHed>
<ext_UpdateRequest:UpdExtSalesOrderDataSetTypeOrderHed>
<ext_UpdateRequest:OrderNum>2300091</ext_UpdateRequest:OrderNum>
<ext_UpdateRequest:RowMod>D</ext_UpdateRequest:RowMod>
</ext_UpdateRequest:UpdExtSalesOrderDataSetTypeOrderHed>
</ext_UpdateRequest:OrderHed>
</ext_UpdateRequest:ds>
</ext_UpdateRequest:UpdateRequest>

Service Connect Workflow Architecture Tips for Developers

I’ve recently started designing workflows for Epicor’s Service Connect. Previously I have used Microsoft DTS and SSIS. There is little information available on how to design the overall architecture for workflows. Here are the things I wish I knew when I started. Some knowledge of Service Connect is required before this will make any sense at all.

DTA and USR Elements

Inside the message envelope, the two elements you will use the most are the DTA and USR elements. Think of the data in DTA as parameters you need for the current step, and USR as the overall data you are processing. The structure of data in the DTA will change from step to step, depending on what operation you are performing. The structure of the data in USR does not change across the workflow, and is designed by you as either Process Variables, or Message Extensions. Process Variables are single value fields, and hold things like object IDs. Message Extensions are based on a .xsd schema, and hold datasets.

To maintain USR data across steps, you must map the USR section from input schema into the output schema. This link is usually automatically done for you when you create a new Conversion or Request step, but in some cases it must be manually done.

Suggested Architecture

For your workflow, you should create a Workflow Schema which serves two purposes:
1. It defines the initial DTA structure which will receive data from an Input Channel.
2. It will act as the intermediary schema between steps, making your workflow much more flexible.

In general my workflows look like:
1. Request Conversion from Workflow Schema to Request.xsd
2. Web Method / .NET Call
3. Save Response Conversion from Response.xsd to Workflow Schema
4. Repeat 1-3 for next Web Method / .NET Call

In my Request Conversion, I move data from the USR section to the DTA section of the request.

In my Save Response Conversion, I move data from the DTA section of the response into the USR section (usually as a Message Extension based on the Response.xsd.

This allows the easy insert or rearrangement of workflow items, as all conversions before a Web Method request take Workflow Schema as their input. It also makes it easier to maintain the data through the workflow, as I know it always comes from the USR section.

Other Tips

Don’t Pass Entire Dataset

Just because a business object methods takes a dataset, it does not mean you must pass the entire dataset.

For instance, SalesOrder.ChangePartMaster takes a SalesOrderDataSet, but you really only have to pass in OrderHed, OrderDtl, OrderRepComm, and TaxConnectStatus. Passing in the entire dataset is less efficient. How do you know what elements a method actually needs? Perform the operation through Epicor Client, and see what element it passes.

Methods Were Developed for Epicor Client, not General Programming

I think the methods exposed by Epicor were really developed first to support the Epicor Client. This means, the methods often expect certain data in the dataset to exist, even if it seems unnecessary to the current method you are calling. If you having trouble getting a method to work, trace the same method call in Epicor Client, and look for any previous calls which modify the dataset you are passing in. You may have to call them first.