OVERVIEW: We use COMCASH POS for our sales and inventory software which is an MS SQL database. We use Quickbooks Enterprise for our accounting software. We take weekly cycle counts in our warehouse and make adjustments to inventory and COGS (expense) for shortages or overages. The two programs do not interact. NEEDED: 1)Procedure for making adjustments to inventory by accessing and making changes to an MS SQL database used by our POS software, Comcash. 2) Summary of the item adjustment costs by DEPARTMENT. Summary: Bypass Comcash software to adjust inventory in a MSSQL database. Use spreadsheet. Use macros or scripts (or other suggestions). Consolidate adjustment totals by DEPT and save in a format that can be used in PHASE II (transferring Journal Entries into Quickbooks).
My idea is as follows: Suggested Procedure(s): 1) A separate, shadow inventory (of the COMCASH inventory) would be kept in an Excel file. This would be a 'flat' file. The file would consist of 8 columns representing Item#, Dept#, Beginning Inventory, Cycle Count, Short/Over, FIFO_Cost, GrossCost [Short/Over X FIFO_Cost] and Ending Inventory. 2)We would do our weekly Cycle Count in our warehouse, enter the totals in the Cycle Count field, have a formula calculate the quantity difference, the GrossCost, and have the last column calculate the Ending Inventory amount. The FIFO_Cost would need to be pulled from the data base. Also, a summary of Short/Over and GrossCost per DEPT would need to be tabulated. 3)We would need a procedure, whether a macro in Excel and using an ODBC connection or a script in Windows XP, probably also through an ODBC connection to the SQL database. The procedure would gather the FIFO_Cost appropriate for each Item# and enter it in that record's cell. 4)We have Item#s that act as temporary 'holding' itemS. For example we have items such as HouseBeer, HappyHourBeer, HouseWine, HappyHourWine that we sell at lower prices. At the beginning of a new week, we make a manual adjustment to inventory that zeros out these holding items and removes (lowers) the number of items from actual inventory. We need the programming procedure to do this for us. 5)Finally, the procedure would make adjustments in MS SQL under the Store Transfers tables. Due to Comcash limits in their reporting module, we track our adjusments to inventory through Store Transfer tables. The reason is that we can have 'fake' Stores (named DamagedGoods, Charity, CycleCount, etc..) that will allow us to run reports on these transfers. ALSO... 1) Complete and fully-functional working program(s) in executable form as well as complete source code of all work done.
2) Deliverables must be in ready-to-run condition, as follows (depending on the nature of the deliverables):
a) For web sites or other server-side deliverables intended to only ever exist in one place in the Buyer's environment--Deliverables must be installed by the Seller in ready-to-run condition in the Buyer's environment.
b) For all others including desktop software or software the buyer intends to distribute: A software installation package that will install the software in ready-to-run condition on the platform(s) specified in this bid request.
3) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement).
Operating System: Windows XP Pro SP2 Database: MS SQL currently accessed through MSDE on client machines. POS Software: COMCASH Other Software: Microsoft Office 2003