ShipComplete SQL Query

[Borvik]

Ok, I'm revisiting a project of mine - our program that prints packing slips.

Recently with our upgrade from GP7.5 to GP9 I had to make a change in how it handled ShipComplete documents - it now has to check each item on an order to make sure all the items on the document are shipping.

Previously this is the process it took:
Gather all Orders that haven't printed before
Weed out ones that shouldn't print using the following checks:
Make sure order has items that would ship out
Make sure there isn't a hold on the order
Make sure there isn't a lock on the order (someone has it open)
Make sure the document date isn't in the future
Do ShipComplete check.

After the GP9 upgrade this whole process slowed down considerably, namely due to the fact I changed how I was dealing with ShipComplete documents (previously it was a hold and a third-party plugin removed the hold when it was ready).

I want to streamline this process to make it faster. I'm trying to consolidate all the steps into a single SQL statement to speed things up. So far I've got everything into 1 sql statement, except checking items on ShipComplete documents.

Here is the sql code I have so far:

SELECT RTRIM(SOP10100.SOPNUMBE) AS DOCNUMBE, COUNT(SOP10200.ITEMNMBR) AS ShippableItems,
  COUNT(SOP10104.PRCHLDID) AS HoldCount, SOP10100.DOCDATE,
  COUNT(tempdb.dbo.DEX_LOCK.row_id) AS LockCount
  FROM SOP10100
   LEFT OUTER JOIN SOP10200 ON (SOP10100.SOPTYPE = SOP10200.SOPTYPE AND SOP10100.SOPNUMBE = SOP10200.SOPNUMBE AND (SOP10200.DROPSHIP = 0 AND SOP10200.QTYTOINV >0))
   LEFT OUTER JOIN SOP10104 ON (SOP10100.SOPTYPE = SOP10104.SOPTYPE AND SOP10100.SOPNUMBE = SOP10104.SOPNUMBE AND (SOP10104.DELETE1 = 0))
   LEFT OUTER JOIN tempdb.dbo.DEX_LOCK ON (SOP10100.DEX_ROW_ID = tempdb.dbo.DEX_LOCK.row_id AND tempdb.dbo.DEX_LOCK.table_path_name = 'PDP.dbo.SOP10100')
  WHERE
   SOP10100.TIMESPRT = 0 AND SOP10100.SOPTYPE = 2 AND
   SOP10100.DOCDATE <= GETDATE()
  GROUP BY SOP10100.SOPNUMBE, SOP10100.DOCDATE
  HAVING COUNT(SOP10200.ITEMNMBR) > 0 AND COUNT(SOP10104.PRCHLDID) = 0 AND
    COUNT(tempdb.dbo.DEX_LOCK.row_id) = 0

Maybe I'll have better luck in the SQL forums, but I figured I'd better check here first - as you guys know the GP system better. I'll post in the SQL forums tomorrow - if I don't figure it out in the meantime.

 

[Borvik]

I may have the answer. Currently my new script and the program are telling me nothing is ready to print - so that's not much of a test. I shall soon find out - when/if they show that there is something to print. Once I've validated it works I'll post the script here for everyone to enjoy.

 

[Borvik]

It worked!

The SQL is going to cut down validation times from ~10 mins to < 5 secs.

Here's the sql for those interested:

SELECT SOP10100.SOPNUMBE, SOP10100.DOCDATE, SOP10100.TIMESPRT,
	CASE SOP10100.SHIPCOMPLETE
		WHEN 0 THEN -1
		WHEN 1 THEN COUNT(UFILLED.ITEMNMBR)
	END AS UnfilledItemCount,
	COUNT(tempdb.dbo.DEX_LOCK.row_id) AS LockCount,
	HCOUNT.HoldCount,
	IOUT.ItemsToShip
FROM SOP10100 
	LEFT OUTER JOIN SOP10200 AS UFILLED ON (SOP10100.SOPTYPE = UFILLED.SOPTYPE AND SOP10100.SOPNUMBE = UFILLED.SOPNUMBE AND UFILLED.QUANTITY <> UFILLED.QTYTOINV)
	LEFT OUTER JOIN tempdb.dbo.DEX_LOCK ON (SOP10100.DEX_ROW_ID = tempdb.dbo.DEX_LOCK.row_id AND tempdb.dbo.DEX_LOCK.table_path_name = 'PDP.dbo.SOP10100')
	LEFT OUTER JOIN (SELECT SOP10100.SOPTYPE, SOP10100.SOPNUMBE, COUNT(SOP10104.PRCHLDID) AS HoldCount
FROM SOP10100
	LEFT OUTER JOIN SOP10104 ON SOP10100.SOPTYPE = SOP10104.SOPTYPE AND SOP10100.SOPNUMBE = SOP10104.SOPNUMBE AND SOP10104.DELETE1 = 0
GROUP BY SOP10100.SOPTYPE, SOP10100.SOPNUMBE) AS HCOUNT ON (SOP10100.SOPTYPE = HCOUNT.SOPTYPE AND SOP10100.SOPNUMBE = HCOUNT.SOPNUMBE)
	LEFT OUTER JOIN (SELECT SOP10100.SOPTYPE, SOP10100.SOPNUMBE, COUNT(SOP10200.ITEMNMBR) AS ItemsToShip
FROM SOP10100
	LEFT OUTER JOIN SOP10200 ON SOP10100.SOPTYPE = SOP10200.SOPTYPE AND SOP10100.SOPNUMBE = SOP10200.SOPNUMBE AND SOP10200.DROPSHIP = 0 AND SOP10200.QTYTOINV > 0
GROUP BY SOP10100.SOPTYPE, SOP10100.SOPNUMBE) AS IOUT ON (SOP10100.SOPTYPE = IOUT.SOPTYPE AND SOP10100.SOPNUMBE = IOUT.SOPNUMBE)
WHERE SOP10100.SOPTYPE = 2 AND SOP10100.DOCDATE <= GETDATE() AND SOP10100.TIMESPRT = 0
	AND IOUT.ItemsToShip > 0 AND HCOUNT.HoldCount = 0
GROUP BY SOP10100.SOPNUMBE, SOP10100.SHIPCOMPLETE, SOP10100.DOCDATE, SOP10100.TIMESPRT, IOUT.ItemsToShip, HCOUNT.HoldCount
HAVING COUNT(tempdb.dbo.DEX_LOCK.row_id) = 0 AND 
	(CASE SOP10100.SHIPCOMPLETE
		WHEN 0 THEN -1
		WHEN 1 THEN COUNT(UFILLED.ITEMNMBR)
	END) <= 0
ORDER BY SOP10100.SOPNUMBE

 

[martink1]

Borvik, what do you do with this script? what do you use it for? does this somehow print only packing slips that have inventory or ?? sound very interesting and I am curious to see.
Martin

 

[Borvik]

Basically yes.

I have written my own program that prints pack slips using VB.NET and Crystal Reports every ten minutes. The report takes an array of SOP Numbers for building the report. Our packing slips are basically an order confirmation report (as GP terms it) with a barcode on it.

This SQL query gets me the complete list of the orders ready to go out. The query does the following:

Makes sure it hasn't printed before
The DocDate is less than or equal to the current date
Order has at least 1 item that will ship
There is no hold on the order
There is no lock on the order (someone has it open for editing)
If ShipComplete document, makes sure all items are shipping