CODEWITHSUNDEEP
sqlt-sqlcursor
dtaylo04
dtaylo04

Reputation: 203

Estimated Ship dates - cursor?

I’ve been asked to put some “smart” logic into estimating our shipping dates for items that have been placed on backorder. The logic that they’d like me to use is this:

Pie in the Sky / Real Fancy Display If we have more on backorder than on any single Purchase Order, calculate which Sales Orders will commit to which inbound Purchase Orders and display the appropriate date. Example: 

Open Sales Orders
SO#  123455 – Req Date 12/15/10 – PN A000123  Backorder 2 pcs.
SO#  123462 – Req Date 12/16/10 – PN A000123  Backorder 7 pcs.
SO#  123941 – Req Date 12/17/10 – PN A000123  Backorder 4 pcs.

Open Purchase Orders
PO#  987654 – Promised 12/29/10 – 5 pcs.
PO#  994258 – Promised 1/15/11 – 15 pcs.

Dates we should be displaying
SO#  123455 – ESD = 12/29/10
SO#  123462 – ESD = 1/15/11
SO#  123941 – ESD = 1/15/11

I know that I could create a temp table to hold the open PO information and then use a cursor to grab each open Sales Order in order by Requested Date and then get the earliest PO that satisfies the quantity needed and then decrement that quantity from that PO. To make things more interesting, we will ship partials, so if the first order was for 7, we’d ship 5 and place the remaining 2 on backorder. So, it would be something like “5 shipped on 12/29/10, remaining 2 to be shipped on 1/15/11”. Any recommendations?

Upvotes: 1

Views: 143

Answers (1)

Philip Kelley
Philip Kelley

Reputation: 40319

I suspect this could be done with a single SQL statement, but it'd be so fantastically complex you'd be nuts to try and write it. (Someone will probably prove me wrong within minutes of my posting this.) Looping procedural code would seem to be the order of the day here.

Just off the top of my head, I'd probably do seomthing like:

  • Produce a list of Sales and Purcahse activity, ordered first by day and then by (Purchase then Activity)
  • Iterate through this list, keeping track of counters (units available) and lists of met and unmet obligations (Sales)
  • As each row is processed, track the pool of avaialble items, incrementing (Purchases) or decrementing (sales) as necessary
  • As each Sales items comes up, depending on the then-availablile volume of parts, create new entries (new table?) showing fulfillment, whether complete or partial, and/or keep track of Sales that have not yet been fully procssed.

It's all estimate/projection, so you wouldn'd need to persist the data generated by this routine, right?

Upvotes: 1

Related Questions