EXTENDING EPICOR ERP – DATA MODEL REGENERATION

You will have to use the Epicor Administration Console to incorporate customization into the database and activate Business Process Management (BPM) directives. Users can customize the Epicor application database by adding user-defined columns. Through these additional columns, users can enter custom data that reflects business processes specific to your organization or industry. To complete a customization that has a user-defined table(s), you must regenerate the database data model. Likewise, some BPM directives that access these custom user-defined fields require that you need to regenerate the data model before they can run.

If you receive an error that the .dll file for the data model generation is in use by another process, end the task and close the Epicor Administration Console. Return to the Apps screen and launch the Command Prompt (under the Windows System section). In the Command Prompt window, enter IISRESET and press <Enter>. After IIS stops and restarts, close the Command Prompt window and return to the Epicor Administration Console. You should be able to regenerate the data model.

When you get stuck in regeneration process with an obscure message that says dotnet framework 4.5 is not found then it could be mostly due to the inclusion of non-standard table, or data type mismatch (something like you could see datatype might be created in non-unicode standard (varchar)), it is still advised not to directly create a column or table in DB.

Here is the best way you can identify this, running below query will show you the non-standard table in your Epicor enterprise erp solution.

<<

SELECT SCHEMA_NAME(schema_id) AS SchemaName,name AS TableName

FROM sys.tables

WHERE OBJECTPROPERTY(OBJECT_ID,’TableHasPrimaryKey’) = 0 AND

(SCHEMA_NAME(schema_id) = ‘ERP’ or SCHEMA_NAME(schema_id) = ‘ICE’)

ORDER BY SchemaName, TableName;

GO

>>

Simply delete it   or you should understand the below principle of Epicor.

Table Standards

If you are adding a custom table to the database, it must match the database requirements for the Epicor ERP application. The custom table will then be included when the data model regenerates.

Each custom table must have:

A primary key.

A SysRowID column with NOT NULL as its unique identifier.

Table and column names that only have letters, numbers, and underscore characters.

Before you regenerate the data model, be sure your custom table follows these standards.

Included and Excluded Tables The following tables are either included or excluded in the regenerate data model process.

Included tables:

All tables in Epicor ERP shipped schemas; typically, these tables use the Ice and ERP prefixes.

Table schemas specified through the IncludeSchemas configuration setting. If you wish to include custom tables, you need to add them using this configuration setting. The following Include Schemas topic describes how you update this setting with custom schemas

Excluded tables:

Tables listed in the ExcludeTables configuration setting. If you wish to exclude tables, you need to add them using this configuration setting. The following Exclude Tables topic describes how you add tables to this custom setting.

Tables that use the cdc, IM, and dbo prefixes are never included in the data model.

Include Schemas

To include your custom tables from other schemas, modify the IncludeSchemas setting by adding your custom prefix values to it. The next time you regenerate the database, your custom tables are included in the data model.

For example, you have created a series of “Prod” and “Fin” custom tables to handle unique production and financial data required by the products you manufacture. To update the configuration file:

1. Navigate to the Data Model Generator directory; typically this will be the C:Program Files (x86) CommonFilesEpicor Software CorporationDatabase Manager Extensions<VersionNumber>DataModelGenerator directory path.
2. Locate the Ice.Tool.DataModelGenerator.UI.exe.config file.
3. Open the file in Notepad or a similar text editor.
4. Locate the IncludeSchemas setting.
5. Add the custom table prefixes as a value list inside this setting. For example:

<setting name=”IncludeSchemas” serializeAs=”String”>

<value>Prod,Fin</value>

</setting>

6. Save the configuration file.
7. Close the text editor.

Now the next time you regenerate the data model, these custom schemas are included in the data model regeneration process.

Exclude Tables

1. You can also use the Ice.Tool.DataModelGenerator.UI.exe.config file to exclude specific tables from the data model. When you run the data model regeneration process, these tables are ignored and so are not added to the data model.

Navigate to the Data Model Generator directory; typically this will be the C:Program Files (x86)Common FilesEpicor Software CorporationDatabase Manager Extensions<VersionNumber>DataModelGenerator directory path.

2. Locate the Ice.Tool.DataModelGenerator.UI.exe.config file
3. Open the file in Notepad or a similar text editor.
4. Locate the ExcludeTables setting.
5. Enter the specific tables you wish to exclude from the data model. Separate each table with a comma.

For example:

<setting name=”ExcludeTables” serializeAs=”String”>

<value>Ice.SysSequence,Ice.DBMigrationLog,Ice.SessionState,Ice.SysAgentSchedProcessing</value>

</setting>

6. Save the configuration file.
7. Close the text editor.

The next time you regenerate the data model, the regeneration process ignores the listed tables.

The regenerate process stages the data model in the database. When the application server restarts, the Epicor ERP application checks the data model on both the disk and the database. If a new data model version is available, this version is retrieved from staging and the database is updated to include the extended user-defined table. Epicor users can now view and enter data in the extended user-defined columns. When you reference these users defined columns through either programming or a customization, the columns appear to be part of the base (primary) table because the data model merges the two tables into one logical entity. To Identify the custom columns, their identifiers all use the “_c” suffix.

This Data Model Regeneration process works exceptionally well for Epicor ERP 10 functionalities.