Solved

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

Posted on 2014-04-28
3
248 Views
Last Modified: 2016-02-10
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.

Thank you in advance!
0
Comment
Question by:printmedia
3 Comments
 
LVL 13

Accepted Solution

by:
AielloJ earned 500 total points
Comment Utility
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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now