This stored procedure will insert records into a table that will be processed to place a product order with a distributor. This procedure will calculate the sum of each item to be filled, minus what is currently on order, minus what is currently in stock. The procedure will also calculate the order cost. If the cost is under $100.00, the procedure will quit with no records written for an order. Procedure must also determine if order must be placed in packs of more than one. An example of this: Customer orders one T-Shirt, but 3 come in a pack ([url removed, login to view] order), three must be ordered from the distributer. The current columns to be used for each table are laid out below:
**Orders** ??" this table provides basic order information
Ready to be filled: y/n
Order filled: y/n
Order ID: Unique Identifier
**Order Items** ??" this table provides quantity of each item to be filled
SKU: Inventory SKU
Order ID: Orders ID
Quantity: Number of item ordered
SKU: Unique Identifier of each item
Distributor SKU: Distributor Item Number
Inventory: Amount On Hand
Discontinued: y/n (If yes, item does not reorder)
Master Pack: int
Min Order: int, number of items that must be shipped in a pack. **
Distributor Order Log
**Distributor SKU: Inventory Distributor SKU
SKU: Inventory SKU
Quantity: Amount on order
Date: Date of order
This is just a simple layout. A detailed layout will be made available. DTS and BCP are currently unavailable on the SQL server. No file access is granted either. A ColdFusion scheduled task will run this procedure about twice a day. When the procedure is finished, the task will create a text file saved to the web server for later upload. If you see a more efficient method of doing this, I am open to suggestions.
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).
The Microsoft SQL Server is version 2003. It is located on a shared environment at CFXHosting.com.
The Web Server that will be calling the procdure is a Virtual Private Server located at CFXHosting.com.