I have a problem I am trying to solve with my companies inventory.
In the old system that we have, every chemical we sell was set up with 2 SKUs.
Due to the fact that we still have to use the old system for accounting, I can't
change how this is set up.
One is called a charge SKU and one is called a no charge SKU.
This is because we sell the chemical, but we also do not charge for the same exact chemical
when the customer has a Dish Machine lease with us. The chemical is included in the
monthly fee for leasing the machine.
My challenge is my company now wants to track each warehouses inventory of the chemicals.
Warehouse 1 receives an transfer request for 4 cases of 1 Gallon Big Blue dish detergent from Warehouse 25.
They send it using the charge SKU 1347-3202.
Warehouse 25 receives it, but they sell 2 cases as a charge SKU 1347-3202 and deliver 1 to a lease with the no charge SKU 1347-3203.
I need warehouse 25 to show they now have only 1 on hand, even though is it two different SKUs.
I have thought about making new SKUs, where the only difference is the last letter C or N, which would denote Charge or No Charge.
That way I could write a script that removes the last character before it adds/subtracts it on my Transaction Log, and they would be the same.
The problem with that is I still need the old SKU numbers for our old system.
So if I only add the product once to my Product Table, instead of twice, how would the Salesmen in the field still see both the Charge and No Charge SKU on the Products choices for the Invoice. Plus, I still need to track how much chemical the Lease accounts are getting for free.
My Transactions Log uses the Product ID to add entries, and has the relationship of _fkTransactionID to _pkTransactionID.
The Invoice Out script adds a line to the Transaction Log with the ProductID, Warehouse, and Out Quantity.
So if I had two different Product IDs, but the same SKU for each BB41G, it would still not show
the right quantity on Warehouse 25.
I also have to have the old numbers because each of our Corporate Account customers have different prices on the
Charge chemicals. On our current printed orders guides, list each SKU for the charge chemicals, can have many different prices
for the same charge SKU.
My table Structure so far is:
Customers--->Invoices-->Invoice Data--> Products
--> Transaction Log
Warehouses--> Employees-->Transfers--> Transfer Data--> Products-->Transaction Log
Transfer Data Script adds 2 entires to the Transaction log.
ProductID, Warehouse, and Out Quantity.
ProductID, Warehouse, and In Quantity.
Does anyone have any suggestions on how I can solve this, with 1 Product ID for each chemical?
Thanks in advance for the help.