Open Sales orders

Question

My aim is to write a SQL query on S/4 hana system to get a list of open sales orders.

Open sales orders are those orders which have been partially shipped or have not been shipped at all.

I have two tables - Sales order table and Shipped Orders table

Sales Order Table - table 1

VBELN - Order Number - primary key

Ord_qty - Order qty

values in the table

VBELN    ORD_QTY

1         10

2         20

3         30

Shipped Orders table - table 2

SHIP_ORD - Shipment number - Primary key

VBELN - Order number - Foreign key from table 1

Ship_qty - shipment quantity

Values in table 2

SHIP_ORD    VBELN     SHIP_QTY

100          1         4

200          1         5

300          2         20

I wrote this query. This returns me

VBELN    ORD_QTY   SHIP_QTY

1         10          9

But I want it also return another record 3 30 0 for the order that has not been shipped.

Here is my query

get open orders ie orders not shipped and orders that are not shipped completely

SELECT a~vbeln,sum( b~ship_qty ) as ship_qty, sum( ORD_QTY ) as ord_type
     INTO TABLE @DATA(LT_DATA)
       from zvic_order as a
       left outer join zvic_ship as b
       on a~vbeln = b~vbeln
       where b~ship_qty is not null or
       b~ship_qty is null
       GROUP BY A~VBELN
       HAVING sum( ord_qty ) > sum( b~ship_qty ) .

I think it is the having condition that is eliminating the NULL entry from shipment table but I don't know any other way of writing this query and obtaining the desired result.

PS: I can get the records without having condition and then loop on the records and filter out but my aim to get the result directly from the query.

Ankit Bajpai · Answer 1 · 2018-10-01T08:54:22.937

0

Try this -

SELECT a~vbeln,sum( b~ship_qty ) as ship_qty, sum( ORD_QTY ) as ord_type
INTO TABLE @DATA(LT_DATA)
   from zvic_order as a
   left outer join (SELECT b~VBELN, SSUM(SHIP_QTY) SHIP_QTY
                    FROM zvic_ship
                    GROUP BY b~VBELN) as b
   on a~vbeln = b~vbeln
   GROUP BY A~VBELN
   HAVING sum( ord_qty ) > sum( b~ship_qty )
          OR sum( b~ship_qty ) IS NULL

edited Oct 01 '18 at 08:54

answered Sep 27 '18 at 15:30

Ankit Bajpai

13,128
4
25
40

No luck. The output is still the same 1 10 9 – user10422059 Sep 27 '18 at 20:35
Your where condition makes no sense. Try my edited version. – Ankit Bajpai Sep 28 '18 at 10:28
super! this is bringing the order with no shipment but, the sum(ord_qty) field in the selection is adding the order quantity twice. I get output for order 1 as 1 20 9 – user10422059 Sep 29 '18 at 14:39

Eralper · Answer 2 · 2018-09-28T07:20:58.937

0

Could you please try following SQL SELECT command ?

select
    o.vbeln,
    o.ord_qty,
    s.ship_qty
from zvic_order as o
left outer join (
    select VBELN, sum(SHIP_QTY) as ship_qty from zvic_ship group by VBELN
) as s on o.vbeln = s.vbeln
where o.ord_qty > ifnull(s.ship_qty,0);

It produces following output

edited Sep 28 '18 at 07:20

answered Sep 28 '18 at 06:30

Eralper

6,461
2
21
27

The second row should not be part of the output since all is shipped. – Joakim Danielson Sep 28 '18 at 07:04
I added a WHERE clause for filtering orders with all items shipped – Eralper Sep 28 '18 at 07:21

score 0 · Answer 3 · answered Sep 28 '18 at 07:15

Use IFNULL for the SUM() to correctly handle rows not shipped at all

SELECT vbeln, ord_qty, IFNULL(SUM(ship_qty),0) as total_shipped
FROM (SELECT o.vbeln, ord_qty, s.ship_qty
      FROM zvic_order o
      LEFT JOIN zvic_ship s ON s.vbeln = o.vbeln ) as j
GROUP BY vbeln
HAVING (ord_qty > total_shipped)

Open Sales orders

3 Answers3