Warehouses are optional and can be used to manage your inventory at more than one physical place.

• Important: Once you have defined warehouses, these are no longer optional and you cannot save a transaction (invoice or transfer) without specifying a warehouse.

You can add, change or delete warehouses through 'System–Warehouses' option.

You can define a default warehouse for users through administrative interface.

You can restrict a user to view and make transactions to his warehouse by setting his role to User. Users with role Administrator, Manager, Supervisor always have access to all warehouses.

Warehouse drop down is enabled on relevant transactions forms once you define at least one warehouse. When you purchase goods, quantity is added to the specified warehouse. When you sell goods, quantity is subtracted from the specified warehouse.

You can move inventory between warehouses by using 'Warehouses–Add Transfer' menu option.

Some companies also need to track the in-transit goods between warehouse transfers. Delivered date is usually different from transfer date.

When you login, you will see the number of transfers which have been sent to your default warehouse but not received by you yet.

To 'receive' the transfers, click the 'Warehouses–Reports–Deliveries' menu option, specify criteria and click Continue to display the transfers pending to be received.

Here you specify the dates when the goods were delivered at 'your' warehouse and click 'Save Delivered'.

• Goods & Services–Parts report provides summary of your on hand quantity at selected or all warehouses. Click 'Warehouse' check box to display onhand by warehouse.

• Warehouses–Reports–Transfers gives you a list of transfers. Summary lists transfer transactions and Detail lists all items in each transfer transaction. You can click on transfer number hyper link to edit the transfer.

• Warehouses–Reports–Onhand gives you inventory onhand for all warehouses or for a particular warehouse.

• Warehouses–Reports–Activity gives you all activity of a particular item or all items. Select warehouse to see the activity in a particular warehouse. Activity report shows activity from purchase invoices, sales invoices and transfers.

• Note: Skip this section if you are hosting your sql-ledger with us.

If you have upgraded your sql-ledger installation with our enhanced version, you need to run few queries to bring your old data in sync with the new warehouses structure.

Assemblies are a special case. In standard sql-ledger, 'Stock Assembly' action does not create any transaction/log and directly updates the onhand quantities in parts table. If you are using assemblies, you will almost always need to adjust the components and assemblies quantities after running these queries. See step 4 below.

Here is the recommended way. If you need further help, send email to support@ledger123.com.

• Make sure you have a current backup before doing this.

1. Add a new warehouse. This will hold the inventory quantities for existing transactions without a warehouse. Lets call it 'OLD-WH'.

INSERT INTO warehouse (description) VALUES ('OLD-WH');
INSERT INTO address (trans_id) SELECT id FROM warehouse WHERE description='OLD-WH';

2. Move existing transactions which are without warehouse into this warehouse using following queries:

UPDATE ar SET warehouse_id = (SELECT id FROM warehouse WHERE description = 'OLD-WH') 
WHERE warehouse_id IS NULL OR warehouse_id = 0;

UPDATE ap SET warehouse_id = (SELECT id FROM warehouse WHERE description = 'OLD-WH')
WHERE warehouse_id IS NULL OR warehouse_id = 0;

UPDATE invoice SET warehouse_id = (SELECT id FROM warehouse WHERE description = 'OLD-WH')
WHERE warehouse_id IS NULL OR warehouse_id = 0;

DELETE FROM inventory WHERE trans_id IN (SELECT id FROM ar UNION SELECT id FROM ap);

INSERT INTO inventory (warehouse_id, parts_id, trans_id, qty, shippingdate, 
  department_id, serialnumber, itemnotes, description, invoice_id)
SELECT a.warehouse_id, i.parts_id, i.trans_id, 0 - i.qty, i.transdate,
  a.department_id, i.serialnumber, i.itemnotes, i.description, i.id
FROM invoice i
JOIN ar a ON (a.id = i.trans_id)
WHERE i.parts_id IN (SELECT id FROM parts WHERE inventory_accno_id IS NOT NULL OR assembly)
AND NOT assemblyitem;

INSERT INTO inventory (warehouse_id, parts_id, trans_id, qty, shippingdate, 
  department_id, serialnumber, itemnotes, description, invoice_id)
SELECT a.warehouse_id, i.parts_id, i.trans_id, 0 - i.qty, i.transdate,
  a.department_id, i.serialnumber, i.itemnotes, i.description, i.id
FROM invoice i
JOIN ap a ON (a.id = i.trans_id)
WHERE i.parts_id IN (SELECT id FROM parts WHERE inventory_accno_id IS NOT NULL OR assembly);

3. Move existing assemblies quantities into OLD-WH

-- Create transaction header
INSERT INTO build (reference, transdate, warehouse_id) 
VALUES ('OLD-WH', (SELECT current_date), (SELECT id FROM warehouse WHERE description='OLD-WH'));

-- Add assemblies made into the warehouse
INSERT INTO inventory (warehouse_id, parts_id, trans_id, qty, shippingdate, linetype)
SELECT 
  (SELECT id FROM warehouse WHERE description='OLD-WH'), 
  p.id, 
  (SELECT id FROM build WHERE reference='OLD-WH'),
  p.onhand + (SELECT SUM(qty) FROM invoice i WHERE i.parts_id = p.id AND i.trans_id IN (SELECT id FROM ar)), 
  (SELECT current_date), 
  '5'
FROM parts p
WHERE assembly;

-- Remove components used from warehouse
INSERT INTO inventory (warehouse_id, parts_id, trans_id, qty, shippingdate, linetype)
SELECT 
  (SELECT id FROM warehouse WHERE description='OLD-WH'),
  a.parts_id,
  (SELECT id FROM build WHERE reference='OLD-WH'),
  0 - (p.onhand + (SELECT SUM(qty) FROM invoice i WHERE i.parts_id = p.id AND i.trans_id IN (SELECT id FROM ar))) * a.qty,
  (SELECT current_date), 
  '4'    
FROM parts p
JOIN assembly a ON (p.id = a.aid);

4. Using Warehouses–Onhand report, make sure you have correct onhand stock quantities. Following query will sync the 'onhand' quantity of items with the quantities from transactions.

UPDATE parts 
SET onhand = (SELECT SUM(qty) FROM inventory WHERE inventory.parts_id = parts.id)
WHERE id IN (SELECT DISTINCT parts_id FROM inventory);

5. If there is a difference, you need to adjust these quantities. Use vendor invoices for a made-up vendor (like 'inventory adjustment') to make such adjustments. Use +ve quantities to increase your onhand and -ve quantities to decrease your onhand. Use 0 for sellprice.