Solved

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

Posted on 2014-04-28
3
264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sorting a SQL script 5 38
SQL State HYT00. Timeout expired proplem 8 42
What does "Between" mean? 6 42
Migrate SQL 2005 DB to SQL 2016 4 25
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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 setup several different housekeeping processes for a SQL Server.

737 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