Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2014-04-28
3
Medium Priority
?
293 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
1 Comment
 
LVL 13

Accepted Solution

by:
AielloJ earned 2000 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

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

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.

Join & Write a Comment

In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
During the weekend, I was asked to investigate into a deadlock in SQL Server 2014. SQL being something I don’t really fancy myself being an expert at, I had to do some refreshing. This article is a collection of my notes.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

606 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