The ERP Hacker

Kits and Taxes

July 29th, 2010

Just when I think I’ve got something figured out, I suddenly realize the issue is much more complex than I thought.

My customer order hack of Macola had been working flawlessly (well, almost flawlessly) for years.  Now, suddenly, everything was going wrong.

The problem was that we had added a new customer to the automation system that had two conditions we’d never needed to address before.  First, state sales tax had to be added to the order.  Second, they ordered only kits rather than individual parts.

I’ll start with the second one. 

Everyone has their own way of defining kits and parts, depending on their own business rules.  Macola, however, has a built-in hierarchy:

A kit is made up of one or more finished parts.

A finished part is made up of one or more subcomponents.

A subcomponent can be made up of one or more pre-assembled subcomponents. In fact, it’s subcomponents, all the way down.

At the very bottom of the hierarchy lies raw materials, from which subcomponents are derived.

A unit of raw material can be the same as a subcomponent, which can be the same as a finished part if it is buy/sell and requires no assembly.

Finished parts are easy.  You don’t have to worry about what a finished part is made of (most of the time).  The cost is in there, the price is in there, the inventory location is in there. 

Kits are a completely different story.  You can’t go to the inventory location of a kit to get its cost or available quantity. It doesn’t exist until it’s ready to ship, at which time it is quickly assembled and shipped out.

The normal rules for creating an order in Macola took me some time to figure out when I first started experimenting with it five years ago.   The steps are:

1. Get the next order number from the order entry control file (OECTLFIL_SQL) and increment the table entry.
2. Create a record in the order entry header table (OEORDHDR_SQL) with order type “O” and using the order number obtained from step 1, and matching records in OEPDSHDR_SQL and OEINQORD_SQL.
3. For each line item, create a record in the order entry line table (OEORDLIN_SQL).
4. Also for each line item, update the inventory by modifying the location table (IMINVLOC_SQL) to show the number of items allocated, and enter a record in the inventory transaction table (IMINVTRX_SQL).
5.  Complete the order by updating total sales, total cost, total weight, total dollars, etc., from the line item tables.  Set selection code to “C” and OEPDSHDR_SQL’s status code = “O”.

Simple, right?  Actually, there are other details I haven’t included here, such as locking the control file table before updating it, inserting line comments, and logging the activity.  Adding kits to the process made my task considerably more complicated.

Step 3 above assumes that you’ve already got the relevant information about the item the customer is ordered.  Details like the item number, description, unit of measure, cost, price, quantity available, etc., would be included here.  At some point before the order is created, another process has derived all this information from the customer request. 

This “black box” process had to change when kits were involved.  Instead of simply going into the item master table (IMITMIDX_SQL) and the item location table (IMINVLOC_SQL) to get details and available quantity on the part, we must look at the kits lookup table (IMKITFIL_SQL) to see what finished parts are included in the kit.  Then we must look up each of those parts and determine the cost of the kit (presumably the price is not the same as the sum of these parts, and is associated with the kit and not the component parts).

Since a kit may contain more than one of each part, the formula for determining the cost of a kit is: Sum(part cost * quantity per kit).

Now we have the information we need for the order entry line table.  Inventory is a different matter, though. Step 4, which updates the inventory for each line item, had to be extended like this:

1. For each kit, insert a record in the inventory transaction table with the cost of zero.
2. For each part that comprises the kit, insert a record in the inventory transaction table with the quantity of (quantity per kit * number of kits) and the cost associated with that part.
3. Update the inventory location table to record the quantity allocated both for the kit and for each of the component parts.

So how do you handle the “each part” part?  It was important to de-couple the inventory lookup from the order creation, so I needed to know what parts were in a kit BEFORE I was ready to create the order.  I ended up adding a collection to the PartEntity that essentially held a list of component parts.  This list was populated when I got the inventory item data, using a kit query object that issued the following SQL command:

SELECT avg_cost, K.seq_no , qty_per_par, comp_item_no
from <<legacy>>.{0}.dbo.IMKITFIL_SQL K inner join <<legacy>>.{0}.dbo.IMINVLOC_SQL I
ON K.comp_item_no = I.item_no where K.item_no = @itemNum

(Where “<<legacy>>” is the server name and “{0}” is the database name.)

The sticky thing about the kit problem, for me, was that the consequences of handling kits incorrectly didn’t make themselves known for several months.  Financials and inventory numbers were off as a result of this problem, which fortunately didn’t involve one of our larger customers, but still brought the wrath of the bean-counters upon my head.

As if things weren’t bad enough, I then had to deal with the tax issue for this same customer.  I had accounted for taxes in my code, getting the tax rate from the tax table (TAXDTL_SQL) and adding the rate times the sales amount to the total.

It didn’t work for several reasons.  Downstream processes in Macola expect certain combinations of field values to be in the order header table, and they are unhappy if they don’t get them.

After painstaking analysis of the Macola processes, I was able to determine:

•  The “tot_tax_amt” field needed to be zero if the tax rate was zero, and total sales if the tax rate was greater than zero.
•  Likewise, the “tot_tax_cost” needed to be zero if no tax, total cost otherwise.
• The tax amount (total sales * tax rate) went into the “sls_tax_amt_1″ field (There were several fields for different taxes; luckily I only had to use the first one). 
• Finally, the “tot_dollars” field needed to be the total of sales + taxes.

I ended up handling some of this in code, calculating cost and the total sales amount minus tax, then passing this value into the SQL statement that finalized the customer order.

UPDATE [<<legacy>>].[{0}].DBO.[OEORDHDR_SQL] SET
selection_cd = 'C', tot_sls_amt = @tot_sls, tot_tax_amt = CASE WHEN
isnull(tax_pct,0) = 0 THEN 0 else @tot_sls END, tot_cost = @tot_cost,
tot_weight = @tot_wgt, tot_dollars = @tot_sls + isnull(tax_pct,0)/100 * @tot_sls, tot_tax_cost = CASE WHEN
isnull(tax_pct,0) = 0 THEN 0 else @tot_cost END,
SHIPPING_DT = Automation.dbo.fnDateToEDIDate(getdate()) ,
sls_tax_amt_1 = isnull(tax_pct,0)/100 * @tot_sls
WHERE ( ORD_TYPE = 'O' AND ORD_NO = @ordNo )
 
Problem solved!  For new orders, at least.  I still had to fix the existing orders that were missing sales tax by issuing credits for each affected order from the previous four months, then create invoice orders with the correct tax so they could be re-sent.

As busy as I was, I really couldn’t complain about all this extra work.  I had brought it on myself.

Now all I have to do is figure out to reproduce this black magic in our new ERP system, Syteline.  That’s a story for another day.

Entry Filed under: Uncategorized