Stephen Smith's Blog

All things Sage 300…

Tables and Data Flow of the Accpac Inventory Control Module – Part 1

with 3 comments

This blog posting will be looking at the structure of some of the parts of the Inventory Control (I/C) 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 146 tables in I/C, so obviously we can’t describe each in this two part 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 I/C can be categorized as:

  • Setup
  • Master Data
  • Data Entry
  • Data Processing
  • Periodic Processing

Inventory Control is a fairly big module that you typically don’t see much of. The modules you run are Order Entry and Purchase Order which then remove things from inventory or add things to inventory. Both P/O and O/E make extensive use of I/C business logic in everything they do. I/C owns the Day End Processing task which I blogged about here. Both the Lot Tracking and Serialized Inventory modules actually reside entirely within the I/C module. I/C is responsible for maintaining all the complicated pricing models Accpac supports as well as the various costing models that are supported. I/C tracks the inventory by location as well as via serial and lot numbers. Below is a diagram showing this flow:


The ICX tables are used for serial and lot tracking. I/C Items like G/L accounts have a structure and several of these are used to define these. The Setup Tables are:

ICOPT  (IC0380): Options.

ICSEG (IC0610): Item Segments.

ICITMS (IC0320): Structure Codes.

ICSEGV (IC0620): Segment  Codes.

ICLOC (IC0370): Locations.

ICCATG (IC0210): Categories.

ICCATTX (IC0220): Category Taxes.

ICACCT (IC0100): Account Sets.

ICBOMH (IC0200): Bill of Materials.

ICBOMD (IC0190): Bill of Material Details

ICPCOD (IC0390): Price List Codes.

ICPCTX (IC0395): Price List Tax Codes

ICXWARY (IC0850): Warranty Codes.

ICXCONT (IC0800): Contract Codes.

ICXMASK (IC0805): Mask Structures used for lot and serial numbers.

Master Data

I/C has quite a lot of master data. Neither O/E nor P/O have much master data because all of theirs is located here in I/C. This includes the main inventory items, along with pricing, location details, reorder quantities, kits, lot numbers and serial numbers.


ICITEM (IC0310): Items.

ICUNIT (IC0750): Units of Measure.

ICITMTX (IC0330): Item Tax Authorities.

ICITMV (IC0340): Vendor Item Number Details.

ICITEMLO (IC0312): Item Lot Optional Fields.

ICITEMO (IC0313): Item Optional Fields.

ICITEMSO (IC0314): Item Serial Optional Fields.

ICITMC (IC0319): Customer Item Numbers.

ICITMB (IC0318): Build Item Number

Item Pricing

ICPRIC (IC0480): Price Lists.

ICPRICP (IC0482): Item Pricing Details.

ICPRTX (IC0490): Price List Tax Authorities.

ICPRICC (IC0481): Pricing Price Check.

Location Details

ICILOC (IC0290):Location Details contains Quantities / Costs.

ICCOST (IC0260): LIFO/FIFO Receipt Costs – Detail records for LIFO/FIFO Qty / Costing.

Using ICILOC can be quite complicated so there are a number of super-views to help with Location Details:

ICLOCI (IC0372): Loc. Quantities (Finder).

ICNCST (IC0375): Transactions Not Costed.

ICILOCG (IC0295): Create Item Location.

Other Master Tables

ICCUPR (IC0274): Contract Pricing.

ICIOTH (IC0305): Manufacturer’s items.

ICKITH (IC0356): Kitting Items.

ICKITD (IC0355): Kitting Item Components.

ICREOR (IC0599): Reorder Quantities.

ICREORD (IC0600): Reorder Quantities Details.

ICREORO (IC0601): Reorder optional fields.

ICXSER (IC0830): Inventory Serial Numbers.

ICXSERO (IC0832): Inventory serial number optional fields.

ICXLOT (IC0810): Inventory Lot Numbers.

ICXLOTO (IC0812): Inventory lot number optional fields.

A Superview to help with LIFO/FIFO is:

ICCOSS (IC0258): LIFO/FIFO Inquiry Summary

Below is a diagram showing how these tables are related:

Often using the I/C Views directly can be quite complicated. One trick is to use the O/E or P/O views instead. For instance figuring out what the price will be for an item for a customer can be quite complicated, but an easier way to do it is to create an order for that customer then insert a detail line to buy one of that item and O/E will fill in all the correct pricing information into that detail line for you (then just don’t post that order).


Hopefully this blog posting provides a bit more insight into how I/C operates and hopefully helps when you use I/C, interface to I/C or are creating custom I/C reports or forms.

To be continued with data entry, data processing and periodic processing

Written by smist08

August 20, 2011 at 8:21 pm

Posted in sage 300

Tagged with , ,

3 Responses

Subscribe to comments with RSS.

  1. Hi Stephen, this is Linda from Norming Software.
    Hope you’re doing well.

    I have two questions about creating GL transaction using GPGLPO from my application:
    1. When GL transation is being created from any other subledger(such as AP or AR), sometimes I cannot create GL transaction from my application at the same time. I have tried to create GL transaction from AP and AR at the same time, and got a conflict error, asked me to re-create GL transaction. So my first question is, how can I get to know any other subledger is creating GL transactions. If I can know that, I can prompt an error information to ask user to try later. Also how can I prevent any other subledger to create GL transaction when my application is creating GL transaction?

    2. GPGLPO cannot provide the GL batch number and entry number for the GL journal entry which is generated from my applicationI, right? Also with GLPGLPO I cannot set ‘Auto Reverse’ flag for the generated GL journal entry, right? But I think these two features are very important for the subledger, or I missed something in my codes? I noticed AR and AP do not use GPGLPO to create GL transaction, why? For the two reasons I stated above?

    Linda He

    August 26, 2011 at 10:00 am

    • Hi Linda,

      I don’t know GP very well, but it is documented in Appendix G of the SDK Programmer’s Guide. Plus I’m sure DPP support can help you with this.


      August 26, 2011 at 11:52 pm

  2. […] Last week we started to cover the database structure of the Accpac Inventory Control (I/C) module here. […]

    Stephen Smith's Blog

    August 27, 2011 at 7:40 pm

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: