Topic: Anatomy of adding 1 Inventory Item
Taking the sql backup diffs between before and after adding a single Inventory Item, the following sequence of SQL statements are observed (Assumed TB_PREF=3_):
SET @StockID := 'MT501';
SET @ItemDesc := 'Camera 5 MP';
SET @ItemLongDesc := 'Digital Camera of 5 MP resolution';
SET @ReOrderLevel := '0';
SET @MBFlag := 'B'; -- BoughtOut, Manufactured, D: Charges/Services
SET @CatID := '1'; -- stock_category.component_id -- Components
SET @LocationCode := 'DEF'; -- locations.loc_code
SET @Units := 'each'; -- item_units.abbr
SET @TaxTypeID := '2'; -- item_tax_types.id
SET @SalesAc := (SELECT `value` FROM 3_sys_prefs WHERE `category` = "glsetup.items" AND `name` = "default_inv_sales_act");
SET @COGSAc := (SELECT `value` FROM 3_sys_prefs WHERE `category` = "glsetup.items" AND `name` = "default_cogs_act");
SET @InventoryAc := (SELECT `value` FROM 3_sys_prefs WHERE `category` = "glsetup.items" AND `name` = "default_inventory_act");
SET @AdjAc := (SELECT `value` FROM 3_sys_prefs WHERE `category` = "glsetup.items" AND `name` = "default_adj_act");
SET @WIPAc := (SELECT `value` FROM 3_sys_prefs WHERE `category` = "glsetup.items" AND `name` = "default_wip_act");
INSERT INTO `3_stock_master` VALUES
(@StockID, @CatID, @TaxTypeID, @ItemDesc, @ItemLongDesc, @Units, @MBFlag, @SalesAc, @COGSAc, @InventoryAc, @AdjAc, @WIPAc, '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '', '0', '0', '0000-00-00', '0000-00-00', '');
INSERT INTO `3_item_codes` VALUES
(NULL, @StockID, @StockID, @ItemDesc, @CatID, '1', '0', '0');
INSERT INTO `3_loc_stock` VALUES
(@LocationCode, @StockID, @ReOrderLevel);
-- If a jpg image of the item was added to company #, then it would be named company/#/images/<StockID>.jpg
-- Here it would be: company/3/images/MT501.jpg
-- item_codes.id has no relevance in FA except for uniquely choosing the item and not even for sorting
-- Foreign Items can have different item_code and stock_id for the same itemHere, first a stock item is added to the stock_master..
Then one item_code is allotted for it which may be different from the stock_id if the item is a foreign one.
The stock_id is assigned to a location and a Re-Order level fixed for it.
The above can be useful for bulk importing of inventory items from disparate systems.