Order Status Report to Inform Sales of Orders that did not Sync with WMS

Order Status Report to Inform Sales of Orders that did not Sync with WMS

— Orders in GP but not in WMS
— Replace the XXX for a.docid for the different order types within GP that are managed by the WMS
— Replace the XXX in the THEN statement to give the docid data type a friendly name
— Replace the XXX% for the d.tracktrace with the first 3-4 numbers of the assigned carton label           sequence
— Replace the XXX for a.docdate with the date or date range you wish to search
— This report assumes a few things:
— #1. You know how to link different DB servers together as seen in the left joins at the bottom of the query
— #2. This is only dealing with open orders
— #3. It filters out all orders that have been voided

select a.docdate [Order Date], c.usrtab01 [Created By], a.sopnumbe [Document Number],

CASE
when a.docid = ‘XXX’ THEN ‘XXX’
when a.docid = ‘XXX’ THEN ‘XXX’
when a.docid = ‘XXX’ THEN ‘XXX’
when a.docid = ‘XXX’ THEN ‘XXX’
END [Order Type],

a.custnmbr [Customer Number], a.custname [Customer Name], a.shipmthd [Ship Method], a.subtotal [Subtotal],

CASE
when d.tracktrace LIKE ‘XXX%’  THEN ‘IN WAREHOUSE’
else ‘ ‘END [Order Status]

, e.tracking_number

from sop10100 a

left outer join [XXX].[XXX].[DBO].[PICKHEAD] b

on a.sopnumbe = b.order_num

left outer join [XXX].[XXX].[DBO].[TOTMASTR] d

on b.packslip = d.packslip

left outer join sop10106 c

on a.sopnumbe = c.sopnumbe

left outer join sop10107 e

on a.sopnumbe = e.sopnumbe

where a.docdate = ‘XXX’

and a.soptype = ‘2’

and a.VOIDSTTS = ‘0’