CODEWITHSUNDEEP
sql
user3299169
user3299169

Reputation: 1

Sql Statement to determine the quantity available of each lot with open PO's and Sales Orders

I need to create a report that lists the quantity available per product by lot number. For example, I sell widget A100. Today's date is 2/11/2014 and I currently have 2 lots of widget A100 on hand (lot# 1001 and 1002). Lot# 1001 has a quantity of 100 on hand while lot# 1002 has a quantity of 200 on hand. We have three open purchase orders for widget A100 (PO# 1003, 1004, and 1005). PO#1003 is for a quantity of 150 and is due on 2/20/2014, PO# 1004 is for a quantity of 200 and is due on 2/27/2014. Finally, PO# 1005 is for a quantity of 250 and is due on 3/6/2014. So, we have a current quantity of 300 on hand and 600 on order.

Let's say I have four sales orders open (5001, 5002, 5003, and 5004). Sales Order 5001 is for 50 and has current quantity of 50 committed. Sales Order 5002 is for 100 and has quantity of 100 committed to it. Sales order 5003 is for a quantity of 150, not committed because it's not to ship until 2/24/2014. Sales Order 5004 is for a quantity of 100, not committed because it's not to ship until 3/3/2014.

Now, I need to figure out what I have available to sell based on the above and all open sales orders.

The tables that store the information are as follows (note I have simplified the tables for ease of communication):

IN_INVENTORY
  ITEM_NO,
  QTY_AVAILABLE

PO_DTLS
  ITEM_NO,
  DUE_DATE,
  QTY_ORDERED

SO_DTLS
  ITEM_NO,
  SHIP_BY_DATE,
  QTY_COMMITTED,
  QTY_BACKORDERED

IN_INVENTORY
ITEM_NO LOT QTY
A100    1001    100
A100    1002    200

PO_DTLS
ITEM_NO DOC_NO  DUE_DATE    QTY
A100    1003    2/20/2014   150
A100    1004    2/27/2014   200
A100    1005    3/6/2014    250

SO_DTLS
ITEM_NO DOC_NO  SHIP_BY_DATE    QTY_COMMITTED   QTY_BACKORDERED
A100    5001    2/14/2014   50      0
A100    5002    2/16/2014   100     0
A100    5003    2/24/2014   0       150
A100    5004    3/3/2014    0       100


My expected output would look as follows:

ITEM_NO LOT QTY_AVAILABLE       AVAILABLE_DATE
A100    1002    50          2/11/2014
A100    1004    100         2/27/2014
A100    105 250         3/6/2014

I need a sql statement that creates the desired output based on the tables and records listed. Any suggestions would be greatly appreciated!!! I would rather not use a cursor but I can't figure out a way around not using one without writing an application to do it.

Upvotes: 0

Views: 2047

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13248

I did the below using Oracle and get to your desired result with your example data.

If you're using a different database you may be able to mimic the logic:

  1. Sub1 block determines the impact on what's on hand (reduced by commitments) without taking into account incoming shipments, and shows just those lots with a balance remaining
  2. Sub2 block determines the impact on what's incoming (reduced by backorders), and returns only the first impacted doc_no, and shows its partial balance
  3. Sub3 block determines the impact on what's incoming (reduced by backorders), and returns only the the sebsequent doc_no's (above that returned in step 2), which logically must have an unaffected balance
  4. Last step is just to merge the results, and you get your 3 desired rows

I did make a few assumptions, such as the lots on your IN_INVENTORY table never currently being a DOC_NO on your PO_DTLS. Depending on how your application actually stores the data if I've made any bad assumptions let me know and the logic can probably be weaved in.

with sub1 as
 (select x.item_no,
         x.lot,
         x.qty - y.going as qty_available,
         to_date('02/11/2014', 'MM/DD/YYYY') as available_date
    from in_inventory x
    join (select item_no, sum(qty_committed) as going
           from so_dtls
          where qty_committed > 0
          group by item_no) y
      on x.item_no = y.item_no
   where y.going < x.qty
   order by 1, 2),
sub2 as
 (select item_no, doc_no, runner, due_date
    from ((select x.item_no,
                  x.doc_no,
                  x.qty,
                  y.going,
                  sum(x.qty - nvl(y.going, 0)) over(partition by x.item_no order by x.item_no, x.doc_no) as runner,
                  x.due_date
             from po_dtls x
             left join (select item_no, sum(qty_backordered) as going
                         from so_dtls
                        where qty_backordered > 0
                        group by item_no) y
               on x.item_no = y.item_no
              and x.doc_no = (select min(z.doc_no)
                                from po_dtls z
                               where z.item_no = x.item_no)
            order by 1, 2))),
sub3 as
 (select item_no, doc_no, qty, due_date
    from ((select x.item_no,
                  x.doc_no,
                  x.qty,
                  y.going,
                  sum(x.qty - nvl(y.going, 0)) over(partition by x.item_no order by x.item_no, x.doc_no) as runner,
                  x.due_date
             from po_dtls x
             left join (select item_no, sum(qty_backordered) as going
                         from so_dtls
                        where qty_backordered > 0
                        group by item_no) y
               on x.item_no = y.item_no
              and x.doc_no > (select min(z.doc_no)
                                from po_dtls z
                               where z.item_no = x.item_no)
            order by 1, 2)))
select *
  from sub1
union
select *
  from sub2
 where sub2.doc_no = (select min(x.doc_no) from sub2 x where x.runner >= 0)
union all
select *
  from sub3
 where sub3.doc_no > (select min(x.doc_no) from sub2 x where x.runner >= 0)
 order by item_no, lot

Upvotes: 0

Related Questions