Solved

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

Posted on 2014-04-28
3
262 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
ID: 40030414
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My client sends a request to me that they want me to load data, which will be returned by Web Service APIs, and do some transformation before importing to database. In this article, I will provide an approach to load data with Web Service Task and X…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

685 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