预计发货日期 - 光标？
我被要求加入一些“智能”逻辑来估计已延期交货的物品的发货日期。他们希望我使用的逻辑是这样的：
天空中的馅饼/真正的花式展示 如果我们的延期交货数量多于任何单个采购订单，请计算哪些销售订单将提交给哪些入库采购订单并显示适当的日期。 示例：
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
我知道我可以创建一个临时表来保存未结采购订单信息，然后使用游标按请求日期的顺序抓取每个未结销售订单，然后获取满足所需数量的最早采购订单，然后从那个 PO。为了让事情变得更有趣，我们将运送部分货物，因此，如果第一个订单是 7 个，我们将运送 5 个，并将剩余的 2 个置于缺货状态。因此，类似于“5 个于 12/29/10 发货，剩余 2 个于 1/15/11 发货”。有什么建议吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我怀疑这可以用一条 SQL 语句来完成，但它会非常复杂，你会疯狂地尝试编写它。 （在我发布这篇文章后几分钟内，有人可能会证明我是错的。）循环程序代码似乎是这里的惯例。
就在我的脑海中，我可能会做这样的事情：
这都是估计/预测，所以您需要保留此例程生成的数据，对吧？
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:
It's all estimate/projection, so you wouldn'd need to persist the data generated by this routine, right?