# Deduct quantities from a balance but dealing with oldest balance first in Visual Basic 2008

Hi all.

We have something called "Blanket Orders" which is when a client agrees to buy a certain amount of a product throughout the year. Sometimes we may get another blanket order for the same customer and for the same product:

BlanketOrder1, BlanketOrderDate = 03/01/2014, CustomerA, Product1, Quantity = 500
BlanketOrder2, BlanketOrderDate = 03/02/2014, CustomerA, Product1, Quantity = 200
BlanketOrder3, BlanketOrderDate = 04/01/2014, CustomerB, Product2, Quantity = 300

Now, the next step is to deduct quantities from the blanket order when we invoice that specific customer and product number that matches the blanket order. So if we invoiced Customer A on 03/02/2014 for Product1 a total of 100 then the script should enter a new line in the Transactions table:

03/01/2014, Transaction = Blanket Order Qty, Qty = 500, BlanketOrder1
03/02/2014, Transaction = Invoice, Qty = 100, BlanketOrder1

So the new balance is 400 for BlanketOrder1, as you can see, the balance of BlanketOrder2 is not touched because BlanketOrder1 is older and it's for the same customer and product number.

Let's say the Balance for BlanketOrder1 reaches 50, and now a new invoice comes in on 03/25/2014 for 150. Then the script should enter a new row in the Transactions table for BlanketOrder1 for 50 (therefore making the balance 0), and applying the remaining invoiced quantity (in this case 100) to BlanketOrder2.

03/25/2014, Transaction= Partial Invoice, Qty = 50, BlanketOrder1 (balance is now 0)
03/25/2014, Transaction = Partial Invoice, Qty = 100, BlanketOrder2 (balance is now 100)

If there is no second blanket order then the SSIS would apply the whole invoiced quantity (150) to BlanketOrder1:

03/25/2014, Transaction = Invoice, Qty = 150, BlanketOrder1 (balance now has an overage of 100).

Any idea how I can do this?

Through a foreach loop container, the SSIS handles each invoice, currently, I have it simply displaying the invoice number in a message box to test what I've got so far.

The key is deciding where the quantity is applied, if there is only one blanket order for that same customer and product number then apply the entire quantity to that one blanket order, but if there are more than one (for the same customer and product) then first fulfill/deduct from the oldest blanket order and then proceed to the next blanket order and so on. And sometimes it may have to split quantities for an invoice in order to make the first blanket order = 0 and then applying the rest to the other blanket orders if there are any, if not then apply the entire quantity total to the only blanket order.

I plan on using a script task in a SSIS package.

Any help would be greatly appreciated.

###### Who is Participating?

Commented:
Ace,

You didn't include a copy of your data model.  This is key to giving the proper advice.  I've included a basic data model that I made up in MS-Access as an example.  My understanding of your post is that there's only one table with all the transactions in it, and all updates are done through code that loops through them all.  If that's correct, you're looking at a code maintenance nightmare that will go on forever.

I've sent a graphic of a proper basic data model and a very rough SQL query.  Using a proper data model, you can implement your requirements with a mixture SQL queries and much less code using the capabilities of SQL.  The query below is a basic outline that will return the order ID and the number of unbilled units left on that order.  It needs to be updated to select the correct customer, oldest order, and proper product.

Best regards,

AielloJ

SELECT
Ord.ID,
SUM(InvDet.TransCount) - Ord.OrderQty AS UnbilledCount
FROM
Orders  Ord
INNER JOIN
Invoices Inv
ON
Ord.ID = Inv.fkOrderID
INNER JOIN
InvDetails  InvDet
ON
Inv.ID = InvDet.fkInvID
WHERE
SUM(InvDet.TransCount) - Ord.OrderQty > 0
Data-Model.JPG
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.