Inventory Purchase Improvements

[QMcKay]

I’m not sure where to put this, so for now I’ll log it as a Request.

I refuse to believe that I am the only one who thinks that Inventory Purchases needs some work:

First of all, this is in the Manage section, which, for small operations is very inconvenient. When a Supplier arrives at my door we frequently pay for the Stock out of the Drawer. Unfortunately, we are forced to enter the Total Amount of all products into a Payout screen for that particular supplier, with no regard for the individual products, their quantities, or their cost.

Second, and perhaps more importantly, the Inventory Transactions are separated from the other types of Transactions and their Accounts - there is no correlation between them at this time.

I eluded to a proposed flow here:

Expenses and Payouts - Method Q

Originally derived from the v3 Tutorial here: http://sambapos.org/wiki/doku.php/en/configuring_cash_transactions_for_v2_users Understanding the operational flow of Transaction Types and Document Types from the Tutorial above, I implemented the following method to handle Payouts to Expenses and Suppliers that come out of the Cash Drawer (Payment Account Type), and an Account that I call Wallet (Bank Account Type). This screenshot shows the Operational Flow of making a Payment to an Expense A…

Specifically, here:

@QMcKay

I would also like to see the Inventory Purchasing transactions exposed, perhaps in a more friendly, integrated manner.  Currently, when we purchase product from a vendor we use the method described above to track the money.  Then if we want to track inventory, we need to make a purchase in the Warehouse, so it is double the work.  It would be nice if we could combine them.

I am about to start on a PHP solution for this so that I can enter Stock Purchases in a more friendly manner, including the cost of each Case/Package, it’s Tax (if any), and Total Price to finally result in a transaction being made from/to the proper accounts, and update the Inventory counts - all of this done via custom SQL against the SambaPOS DB.

However, if improvements in this system are going to made in SambaPOS in the very near future, I’m not sure that I need or want to go through the work required to make this work outside of SambaPOS, while integrating it into the SambaPOS DB.

@emre, have you given this any thought?

[emre]

Yes it is one of the missing pieces I like to implement soon. Basically you’ll configure document types like we configure account transaction documents and these will appear as buttons on warehouse screen. In fact Inventory > Document Types menu created for that configuration but we still have things to do. For better integration with accounting system taxes, discounts, carriage fees and other purchasing specific features also should be taken to account.

However I also want to release features such as multi language support, credit card processing, easier configuration screens for new users, import & export entities I can’t promise for a deadline but this one have higher priority in our to do list.

To be able to keep up a project like SambaPOS needs a lot of work other than development. It seems a lot for us but with the contributions of some amazing people we’ll be able to improve our team soon. Compared to more advanced countries our startup ecosystem works a little slower but we succeeded on generating serious attention. So keep supporting us. I’ll solve that .

[QMcKay]

I shall begin then.

@emre, any advice or guidance on the operations that are being performed on the DB when and Inventory Purchase is made?

[emre]

We store purchases in InventoryTransactions table

[QMcKay]

Making progress…
Inventory Items with TransactionUnits
Supplier Entities

@emre, if there is an Inventory Item that could be purchased from more than 1 Warehouse (Supplier), how would I go about setting that up?

As it stands, each Inventory Item can only have 1 value for Warehouse.
I want to link a Supplier Entity Warehouse with an Inventory Item Warehouse so that I can filter Items by Supplier Warehouse… I can achieve this now, however, if the item is available from more than 1 Supplier I’m not sure how to handle it at this point.

[paxi]

@QMcKay

First of all, this is in the Manage section, which, for small operations is very inconvenient. When a Supplier arrives at my door we frequently pay for the Stock out of the Drawer. Unfortunately, we are forced to enter the Total Amount of all products into a Payout screen for that particular supplier, with no regard for the individual products, their quantities, or their cost.

Fully endorse your sentiments!

I currently use a simple excel solution for this, which is far from perfect.

@emre things will be simplified if transactions can be done without the need for the admin to log onto the system!

[Jesse]

paxi you can make it so a non admin account can purchase inventory would not be advisable however because it HAS to be management screen… but I agree having the ability to do it outside the main menu/admin functionality would be way more convenient.

[paxi]

Yeah exactly. Because he can then change user accounts or roll back the database.

[emre]

@QMcKay

if there is an Inventory Item that could be purchased from more than 1 Warehouse (Supplier), how would I go about setting that up?

@QMcKay that warehouse setting for inventory item does nothing other than hiding 0 inventory items from other warehouses. For example if you have kitchen and bar warehouses you can assign bar items to bar warehouse so they won’t appear as 0 on kitchen warehouse. Of course that does not prevent you from purchasing bar items for kitchen warehouse. If inventory is different than zero they’ll appear.

[QMcKay]

I noticed that the linkage of Inventory Item Warehouse to Supplier Entity Warehouse does not look natural.

The Supplier Entity Warehouse contains WarehouseID (integer) while the Inventory Item Warehouse contains Warehouse (varchar field, not integer ID). However, I managed to link them together to perform filtering of Inventory Items by Supplier Entity Warehouse.

I’m realizing that I don’t really need to specify more than 1 Supplier Warehouse per Inventory Item. We can have a default Supplier, but still choose to buy from a different Supplier, which is fine.

For example, if my Bagels normally come from a Supplier named “Bakery”, that will be my default choice, but I can also change the Supplier as shown (I chose a Supplier named “GENERIC”)…

[QMcKay]

This is the Warehouse screen… LOL! @emre what can we do to improve this?

[Jesse]

That is a lot of warehouses haha

[emre]

Ice warehouse? I think it is a little off from our intention with creating multi warehouse support. What do you mean to do here? Is it Inventory item group code?

[QMcKay]

It’s for Product filtering by Supplier Entity via assigned Warehouse. It’s not GroupCode. Take the following example for GroupCode Tobacco, which has 2 different suppliers based on the brand.

It’s a bit of a moot point, since I won’t be using the Warehouses Screen; instead I’ll be using the PHP web-based system… The following is actually 2 different screenshots: 1 filtered by Belmont Supplier/Warehouse, and the other filtered by Marlboro Supplier/Warehouse…

In the following shot, the filter is by GroupCode (Product Type) Tobacco…

[QMcKay]

Inventory Transactions screen is not reporting correct Warehouse…

use [SambaPOS4]
SELECT tx.[Id]
--      ,[InventoryTransactionDocumentId]
--      ,[InventoryTransactionTypeId]
      ,[SourceWarehouseId] as WHsrcId
      ,[TargetWarehouseId] as WHtgtId
      ,wh1.[Name] as WHsrc
      ,wh2.[Name] as WHtgt
      ,ii.[Name] as [Item]
--      ,[Date]
      ,[Unit]
--      ,[Multiplier]
      ,[Quantity]
      ,[Price]
      ,[InventoryItem_Id]
  FROM [InventoryTransactions] tx
  left join [Warehouses] wh1 on wh1.[Id]= tx.[SourceWarehouseId]
  left join [Warehouses] wh2 on wh2.[Id]= tx.[TargetWarehouseId]
  left join [InventoryItems] ii on ii.[Id]= tx.[InventoryItem_Id]

[emre]

Thank you very much for reporting it. I’ve a little surprised for not noticing it before. Maybe a recent change was the reason of it.

Edit: I’ve uploaded a new version for fix.

[QMcKay]

Thanks for that @emre - it’s working as expected with v4.1.55.

Now I just need to tie the purchases to the Accounting for Cash/Wallet Account and Supplier Account…

[emre]

Supplier’s Entity contains both warehouse and account mappings however I think Cash/Wallet account thing should be handled inside SambaPOS by creating a supplier payment account transaction.

Do you pay supplies immediately?

[QMcKay]

Yes, we pay suppliers immediately, and most of the time straight from the Drawer. We have been doing this every day for quite some time using the following Transactions and Documents:

My intent is to get rid of these Supplier Account Screens and use the PHP instead. I think I can still use the defined Transactions and Documents and emulate the money transfer using SQL. That’s what I’ve done with the Inventory Transaction/Docs side of things.

[emre]

OK. So you’ll create an account transaction document, create and account transaction and two rows for account transaction values (for source and target accounts).