Manual Items Import into NotrinosERP

Purpose'

To import a list of items and prices into a fresh install of FA v2.3.x from some other accounting system into FA manually.

Procedure

Each Item is stored as one record in the stock_master table (primary key: stock_id) along with costs and CoA Account codes for various transactions like manufacturing, sales, etc.
Each Item is also stored in the item_codes table as well with information pertaining to block quantity of sales and whether it is a foreign item (in which case the Item description here will override the one in the stock_master table and an alternate name for the Item is stored in the item_code field).
Each item is allotted a price in the prices table for each sale_type_id listed in the sales_types table which has defaults of

Retail
Wholesale

Items Accounts Settings

Assumptions

Company # = 1
Currency = USD
Default Sales Types of Retail and Wholesale related to Cost Price:
- Item Cost Price field name = CP
- Item WS Markup factor = WMK (eg., 1.15 => 15% Markup)
- Item Wholesale Selling Price = CP * WMK
- Item Retail Markup Factor = RMK
- Item Retail Price = CP * WMK * RMK
Item Description field = ItemDesc
Numeric CategoryID field = category_id
Stock Item ID field = stock_id
Unit of measure = UOM (eg., each)
All items bear Tax Type Id = 1 (taken from id in tax_types table)
All items are sold in blocks of quantity of 1
All items are at Default Location = DEF (taken from loc_code in locations table)
Reorder Level field = ROL
Table name containing raw items data = items_list
Fields in items_list table to import from => stock_id, ItemDesc, category_id, UOM, ROL, CP, WMK, RMK
All items are of "B" type (Bought Out / Purchased)
sales_account = 4010
cogs_account = 5010
inventory_account = 1510
adjustment_account = 5040
assembly_account = 1530

Import SQLs

First we import the items into the stock_master table:

INSERT INTO 1_stock_master
SELECT stock_id
    , category_id
    , 1 AS tax_type_id
    , ItemDesc AS `description`
    , '' AS long_description
    , 'each' AS units
    , 'B' AS mb_flag
    , 4010 AS sales_account
    , 5010 AS cogs_account
    , 1510 AS inventory_account
    , 5040 AS adjustment_account
    , 1530 AS assembly_account
    , 0 AS dimension_id
    , 0 AS dimension2_id
    , 0 AS actual_cost
    , 0 AS last_cost
    , 0 AS material_cost
    , 0 AS labour_cost
    , 0 AS overhead_cost
    , 0 AS inactive
    , 0 AS no_sale
    , 0 AS editable
FROM item_codes
ORDER BY stock_id;

Then we import the items into the item_codes table:

INSERT INTO `1_item_codes`
SELECT 
      NULL AS id
    , stock_id AS item_code
    , stock_id
    , ItemDesc AS `description`
    , category_id
    , 1 AS quantity
    , 0 AS is_foreign
    , 0 AS inactive
FROM items_list
ORDER BY stock_id;

Populate the loc_stock table with the stock_id's they have:

INSERT INTO `1_loc_stock`
SELECT 
      'DEF' AS loc_code
    , stock_id
    , ROL AS reorder_level
FROM items_list
ORDER BY stock_id;

Now the prices get in

INSERT INTO `1_prices`
SELECT NULL AS id, stock_id, sales_type_id, curr_abrev, price FROM (
SELECT 
       stock_id
    , 2 AS sales_type_id
    , 'USD' AS curr_abrev
    , CP * WMK AS price
FROM items_list
UNION
SELECT 
      stock_id
    , 1 AS sales_type_id
    , 'USD' AS curr_abrev
    , CP * WMK * RMK AS price
FROM items_list
) a ORDER BY stock_id, sales_type_id;

We are now done!

Manual Items Import into NotrinosERP

Purpose'

Procedure

Assumptions

Import SQLs

Tips and Tricks