• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 448
  • Last Modified:

Deduct quantities from a balance but closing out oldest balance first in SQL Server 2008 SSIS

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 SSIS 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 SSIS 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 with a SSIS? The key is that we have to get each invoice for that customer and product number, one by one, and decide where the quantity is applied, if there is only one blanket order 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. Would some kind of script be needed with a for loop instead of a SQL task? Any help would be greatly appreciated.

Thank you in advance!
0
printmedia
Asked:
printmedia
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Any idea how I can do this with a SSIS?
How exactly is this an SSIS issue?  Sounds like you have a wompload of accounting business logic, that is best handled within the database, and not by the ETL tool.
0
 
printmediaAuthor Commented:
I wanted to know if there was a way to do this using SSIS. We cannot alter the accounting software database or add any of our own logic which is why we have to do it outside of it. All we can do is export the data from the accounting database.
0
 
David ToddSenior DBACommented:
Hi,

If all else fails (which it often does), call a procedure from SSIS, and code what you want in the procedure.

HTH
  David
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now