Solved

query for open orders & associated open pick tickets without lines from closed Pick Tickets

Posted on 2014-03-05
5
542 Views
Last Modified: 2014-03-21
Hi Experts,


query for open orders & associated open pick tickets without showing lines from closed Pick Tickets

We are trying to construct an SQL query that will show all open sales orders (where t5.openqty > 0 ) and associated open pick tickets with a line level view of the pick ticket (eg amounts  picked, line pick status).

Now, we have managed to get this working but there is an issue, we don't want item lines from closed pick tickets to appear in the query results. However, if we simply filter them out then open sales orders will not appear if they only have closed tickets associated with them (we want to show all open sales orders in query results whether they have an associated picking ticket with them or not).

This issue can happen if a sales order has a delivery created from it and the delivery is then cancelled which in turn automatically re-opens the sales order.

Conversely, if we don't filter out the closed picking item lines we get duplicate item lines appearing where an order has more than one picking ticket associated with it.

The tables are as follows:
ORDR – Order header table
RDR1 – Order line level  table

OPKL – Pick ticket header table
PKL1 – Pick Tick line level table

OITM – Item table

OBIN – Bin Stock locations
OIBQ – Bin Stock location item line table
(Tables that are in format #tt are temp tables)

SQL code is as follows:

/*This Section gets the stock from the item bin locations for all items
Left Outer joins to OIBQ and OIBN are required as some items may have no
stock in bin locations but are on open sales orders  
Case statement splits stock in warehouse zone 1 into its own field*/
SELECT T0.[itemcode],
       T0.[itemname],
       t2.bincode,
       Isnull(t1.[onhandqty], 0) AS 'OnhandQTY',
       CASE
         /*This filters items within Warehouse Zone number 1 */
         WHEN( T2.sl1code ) = '1' THEN T1.onhandqty
       END                       AS '01'
INTO   #tt1
FROM   oitm T0
       LEFT JOIN oibq T1
              ON T0.itemcode = T1.itemcode
       LEFT JOIN obin T2
              ON T1.binabs = T2.absentry

/*This Section totals up the stock for all items */
SELECT t3.[itemcode],
       Sum(t3.[onhandqty]) AS 'OnHandQTY',
       Sum(t3.[01])        AS '01Stock'
INTO   #tt2
FROM   #tt1 t3
GROUP  BY T3.[itemcode]

/*This Section links the order and pick tickets up  */
SELECT t4.[docnum]                    AS 'Order',
       CASE
         WHEN( t5.[linenum] ) IS NULL THEN 1
         ELSE t5.linenum + 1
       END                            AS 'Row',
       t4.[cardname]                  AS 'Customer',
       t4.[docduedate]                AS 'Due',
       t4.[numatcard]                 AS 'Cust Ref',
       t5.[itemcode],
       t5.[dscription],
       t5.[openqty]                   AS 'Ordered',
/*Filter Out Picklists that are closed and replace them with Null, also replace the PickQTYs with NULL*/
       CASE
         WHEN ( t10.[status] ) = 'C' THEN NULL
         ELSE t6.[absentry]
       END                            AS 'PickNum',
       CASE
         WHEN ( t10.[status] ) = 'C' THEN NULL
         ELSE Sum(t6.[pickqtty])
       END                            AS 'PickQty',
       Sum(Isnull(T9.[01stock], 0))   AS '01Stock',
       Sum(Isnull(t9.[onhandqty], 0)) AS 'Tot Stock',
/* Check the line level pickstatus trap Nulls, we can use this to filter out lines on closed tickets in the final stage*/
         CASE WHEN (t6.[PickStatus]) IS NULL THEN 'NONE' ELSE t6.PickStatus END AS 'PickStatus'  
INTO   #tt3
FROM   ordr t4
       INNER JOIN rdr1 t5
               ON t4.[docentry] = t5.[docentry]
       LEFT JOIN pkl1 t6
              ON t6.[orderentry] = t5.[docentry]
                 AND t6.[orderline] = t5.[linenum]
       LEFT JOIN #tt2 t9
              ON t5.[itemcode] = t9.[itemcode]
       LEFT JOIN opkl t10
              ON t10.[absentry] = t6.[absentry]
WHERE  t5.[openqty] > 0
GROUP  BY t4.[docnum],
          t4.[cardname],
          t5.[linenum],
          t4.[docduedate],
          t4.[numatcard],
          t5.[itemcode],
          t5.[dscription],
          t5.[openqty],
          t6.[absentry],
              t6.PickStatus,
          t9.[onhandqty],
          t10.[status]
ORDER  BY t4.[cardname],
          T4.[docnum],
          t5.linenum

/* Even though the #tt3 temporary table contains the output we need we can't use it directly
This is because the aliasing  we have used breaks the drill down, we need to link the temp table fields
back to the fields in the original SAP tables so that the drill down arrows will work*/
SELECT t3.[docnum]   AS 'Order',
       t0.[row],
       t0.[customer],
       t0.[due],
       t0.[cust ref],
       t2.[itemcode],
       t0.[dscription],
       t0.[ordered],
       t1.[absentry] AS 'PickNum',
       t0.[Pickqty],
       t0.[01stock],
       t0.[tot stock]
FROM   #tt3 t0
       LEFT JOIN opkl t1
              ON t0.picknum = t1.absentry
       INNER JOIN oitm t2
               ON t0.itemcode = t2.itemcode
       INNER JOIN ordr t3
               ON t0.[order] = t3.[docnum]

WHERE t0.PickStatus IN ('NONE','Y','R','C')

Any suggestions on how to resolve this?, maybe the way the query is constructed has more info than needed, & using temp tables could of instead been done in a different way but not sure on this.

An example output of the query results is attached

Thanks

Kevin
/*This Section gets the stock from the item bin locations for all items 
Left Outer joins to OIBQ and OIBN are required as some items may have no 
stock in bin locations but are on open sales orders  
Case statement splits stock in warehouse zone 1 into its own field*/ 
SELECT T0.[itemcode], 
       T0.[itemname], 
       t2.bincode, 
       Isnull(t1.[onhandqty], 0) AS 'OnhandQTY', 
       CASE 
	   /*This filters items within Warehouse Zone number 1 */ 
         WHEN( T2.sl1code ) = '1' THEN T1.onhandqty 
       END                       AS '01' 
INTO   #tt1 
FROM   oitm T0 
       LEFT JOIN oibq T1 
              ON T0.itemcode = T1.itemcode 
       LEFT JOIN obin T2 
              ON T1.binabs = T2.absentry 

/*This Section totals up the stock for all items */ 
SELECT t3.[itemcode], 
       Sum(t3.[onhandqty]) AS 'OnHandQTY', 
       Sum(t3.[01])        AS '01Stock' 
INTO   #tt2 
FROM   #tt1 t3 
GROUP  BY T3.[itemcode] 

/*This Section links the order and pick tickets up  */ 
SELECT t4.[docnum]                    AS 'Order', 
       CASE 
         WHEN( t5.[linenum] ) IS NULL THEN 1 
         ELSE t5.linenum + 1 
       END                            AS 'Row', 
       t4.[cardname]                  AS 'Customer', 
       t4.[docduedate]                AS 'Due', 
       t4.[numatcard]                 AS 'Cust Ref', 
       t5.[itemcode], 
       t5.[dscription], 
       t5.[openqty]                   AS 'Ordered', 
/*Filter Out Picklists that are closed and replace them with Null, also replace the PickQTYs with NULL*/
       CASE 
         WHEN ( t10.[status] ) = 'C' THEN NULL 
         ELSE t6.[absentry] 
       END                            AS 'PickNum', 
       CASE 
         WHEN ( t10.[status] ) = 'C' THEN NULL 
         ELSE Sum(t6.[pickqtty]) 
       END                            AS 'PickQty', 
       Sum(Isnull(T9.[01stock], 0))   AS '01Stock', 
       Sum(Isnull(t9.[onhandqty], 0)) AS 'Tot Stock',
/* Check the line level pickstatus trap Nulls, we can use this to filter out lines on closed tickets in the final stage*/
	   CASE WHEN (t6.[PickStatus]) IS NULL THEN 'NONE' ELSE t6.PickStatus END AS 'PickStatus'  
INTO   #tt3 
FROM   ordr t4 
       INNER JOIN rdr1 t5 
               ON t4.[docentry] = t5.[docentry] 
       LEFT JOIN pkl1 t6 
              ON t6.[orderentry] = t5.[docentry] 
                 AND t6.[orderline] = t5.[linenum] 
       LEFT JOIN #tt2 t9 
              ON t5.[itemcode] = t9.[itemcode] 
       LEFT JOIN opkl t10 
              ON t10.[absentry] = t6.[absentry] 
WHERE  t5.[openqty] > 0 
GROUP  BY t4.[docnum], 
          t4.[cardname], 
          t5.[linenum], 
          t4.[docduedate], 
          t4.[numatcard], 
          t5.[itemcode], 
          t5.[dscription], 
          t5.[openqty], 
          t6.[absentry],
		  t6.PickStatus,
          t9.[onhandqty], 
          t10.[status] 
ORDER  BY t4.[cardname], 
          T4.[docnum], 
          t5.linenum 

/* Even though the #tt3 temporary table contains the output we need we can't use it directly 
This is because the aliasing  we have used breaks the drill down, we need to link the temp table fields
back to the fields in the original SAP tables so that the drill down arrows will work*/ 
SELECT t3.[docnum]   AS 'Order', 
       t0.[row], 
       t0.[customer], 
       t0.[due], 
       t0.[cust ref], 
       t2.[itemcode], 
       t0.[dscription], 
       t0.[ordered], 
       t1.[absentry] AS 'PickNum', 
       t0.[Pickqty], 
       t0.[01stock],
       t0.[tot stock] 
FROM   #tt3 t0 
       LEFT JOIN opkl t1 
              ON t0.picknum = t1.absentry 
       INNER JOIN oitm t2 
               ON t0.itemcode = t2.itemcode 
       INNER JOIN ordr t3 
               ON t0.[order] = t3.[docnum]

WHERE t0.PickStatus IN ('NONE','Y','R','C')

Open in new window

Query-Screenshotsample.jpg
0
Comment
Question by:kevin1983
  • 3
  • 2
5 Comments
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39907170
I suspect lines 62-63 should have an additional filter to exclude closed tickets, since t10 is participating in a left-outer-join, the filter should not eliminate the parent ticket (t6):
LEFT JOIN opkl t10 
	ON	t10.[absentry] = t6.[absentry] 
	AND	t10.[status] <> 'C'

Open in new window

0
 

Author Comment

by:kevin1983
ID: 39908767
John_vidmar - Thanks for you reply, I have tried what you have suggested and the results are showing much less records & appear to be showing only open sales orders that have a picking ticket created/associated with them (The picknnum column has a picknum for all records).

Its missing all open sales orders that have not yet had a picking ticket created for them which i would like to be shown in the results. Is this possible?
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 500 total points
ID: 39909254
Find a sales order that is open, but has no picking tickets.  Run each SQL statement one-at-a-time and see if your temp-tables have included that sales order.  Using this technique, you should be able to determine which query is eliminating those special cases.  Remember, if your table is participating in an outer-join (left, right, full) then you must use the on-clause to filter data instead of the where-clause.

What seems strange to me is how you summarized data for #tt3, the select-clause contains formulas (case-statement, for example, line-52) but your group-by-clause is using the original column (for example, line-74).  I would also remove the order-by, it's unnecessary.  If #tt3 has something weird going on then line-104 may eliminate data.
0
 

Author Comment

by:kevin1983
ID: 39917419
Hi, Sorry about late reply, Thanks, i have only just noticed you replied as the EE notification email was marked as spam.

Ok i have a sales order thats open but no picking tickets...Im not to sure what you mean by running each SQL statement one at a time?, I have tried running different sections but they wont run on there own. I will have another look none the less.

hm interesting point about the on clause, ill try to make some sense of this.
maybe temp table 3 is not needed or is the main cause of the issue. will take out order by clause as suggested
0
 

Author Closing Comment

by:kevin1983
ID: 39944659
Hi,

You were right, the query was far more complex than it needed to be. I went back and rewrote it from scratch. Will accept your post as a solution as it made me re-think the whole thing.

Thanks for the help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now