AVAILABLE INVENTORY

I am working in MSSQL 2000.

I have 2 tables

t1: CREATE TABLE SALES_ORDERS_DUE_2 (SHIPDATE DATETIME, ONORDERQUANTITY REAL, PRODUCT_MAIN_NUM INT)

and

t2:CREATE TABLE SALES_ORDERS_DUE_INVENTORY_SUM (PRODUCT_MAIN_NUM INT, SUMPIECES REAL)

Table t1 is a list of sales orders with the shipdate is the date that the order must be shipped, onorderquantity is the pieces to be shipped, product_main_num is the product id number.

Table t2 is a list of available inventory with product_main_num as the product id number and sumpieces as the total pieces available in inventory.

I would like to have a stored proc that would modify t1 and t2. I would like onorderquantity subtracted fromsumpieces if the pieces are available in t2 and at the same time if the sumpieces are available then onorderquantity should be reduced to 0 .

as an example1:

t1.shipdate=9/1/2004

t1.onorderquantity=300

t1.product_main_num=2510

t2.product_main_num=2510

t2.sumpieces=800

then

t1.shipdate=9/1/2004

t1.onorderquantity=0

t1.product_main_num=2510

t2.product_main_num=2510

t2.sumpieces=500

as an example2:

t1.shipdate=9/1/2004

t1.onorderquantity=300

t1.product_main_num=2510

t2.product_main_num=2510

t2.sumpieces=200

then

t1.shipdate=9/1/2004

t1.onorderquantity=100

t1.product_main_num=2510

t2.product_main_num=2510

t2.sumpieces=0

there are many records in t1 and many shipdates and ther may or may not be enough inventory(t2) to fill all the orders. This program will let me determine what product I need to manufacture by what date taking into account the inventory that is already available.

Note:I need simple code for this ,I am a novice programmer.

## Deliverables

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).

## Platform

windows 2000 server running mssql2000

Ingeniería MySQL PHP Arquitectura de software Verificación de software SQL

Nº del proyecto: #3327191

Sobre el proyecto