Tables and Data Flow of the Accpac Inventory Control Module – Part 1
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:
- 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.
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
ICPRIC (IC0480): Price Lists.
ICPRICP (IC0482): Item Pricing Details.
ICPRTX (IC0490): Price List Tax Authorities.
ICPRICC (IC0481): Pricing Price Check.
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…