· 6 years ago · Sep 30, 2019, 09:38 PM
1USE [mas500_app]
2GO
3/****** Object: StoredProcedure [dbo].[sperpRepoPullShip] Script Date: 9/30/2019 5:34:59 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9
10ALTER PROCEDURE [dbo].[sperpRepoPullShip]
11-- 8/4/19 NB : Take 1 shiprp from xchange.export_ship_flat and attempt to create a shipment in Sage to match.
12-- This proc will always attempt to cancel previous open pick lines or pending shipment from the specified @shiprp.
13-- Results will be logged to tumErpTransferLogWrk and tumErpTransferDetWrk to cross reference mobe3 shipment lines with sage shipment lines.
14--
15@shiprp uniqueidentifier = null
16, @cancel int = 0 -- pass in 1 to delete a pending shipment / cancel picks
17, @transferkey int = null
18, @ext_ref nvarchar(50) = null
19, @shipkey int = null OUTPUT
20, @tranno nvarchar(10) = null OUTPUT
21, @retval int = null OUTPUT
22, @message varchar( max) = null OUTPUT
23, @rowcount int = null OUTPUT
24AS
25/*
26
27*/
28DECLARE @sokey int, @picklistkey int, @userid varchar(30), @company char(3), @whsekey int, @trandate datetime, @oasessionid int
29 , @docno varchar(30), @wms_sesstoken varchar(40), @count int, @shiptran varchar(10)
30
31SELECT @transferkey = COALESCE(@transferkey,0), @oasessionid = 999 -- todo grab next
32
33SELECT @userid = 'mobe3admin', @trandate = dbo.fnevParseDate(GETDATE()), @cancel = COALESCE(@cancel,0)
34
35IF OBJECT_ID('tempdb..#verpShip') IS NOT NULL
36 DROP TABLE #verpShip
37
38IF OBJECT_ID('tempdb..#val') IS NOT NULL
39 DROP TABLE #val
40
41IF OBJECT_ID('tempdb..#to2EntitySelectWrk') IS NULL
42 SELECT * INTO #to2EntitySelectWrk FROM to2EntitySelectWrk (NOLOCK) WHERE 1 = 0
43ELSE TRUNCATE TABLE #to2EntitySelectWrk
44
45
46IF @transferkey > 0 AND @shiprp is null SELECT @shiprp = ownerrp FROM export_transfer_log l WHERE l.transferkey = @transferkey AND l.sub_type IN ('SO')
47
48
49
50-- GET LIST OF SHIP LINES
51SELECT ship.docno, ship.company, ship.itemid, ship.whseid
52 , CASE WHEN ship.stock_qty_ship > 0 THEN ship.stock_qty_ship ELSE ship.qty_ship END as qty_ship
53 , CASE WHEN ship.stock_qty_ship > 0 THEN CAST(null as varchar(10)) ELSE ship.uomid END as uomid
54 , ship.erp_locid
55 , ship.line_ext_ref_int, ship.lotno, ship.serno, ship.doc_ext_ref_int, newid() as rp
56 , ship.rowid, ship.shipno, ship.shiprp, ship.pickno
57 , null as picklistkey, null as whsebinkey, null as whsekey, null as itemkey, null as invtlotkey, null as invtserialkey, null as uomkey
58 , 0 as proc_status, CAST('' as varchar(500)) as proc_message
59INTO #verpShip
60FROM export_ship_flat ship (NOLOCK)
61WHERE ship.tran_type = 'PCK' AND ship.sub_type = 'SO'
62 AND ship._create_utc <= DATEADD(MINUTE,-2,GETUTCDATE()) -- give the packs time to come over.
63 AND (ship.shiprp = @shiprp)
64
65-- if line was deleted, link to new line with same item
66UPDATE wrk SET line_ext_ref_int = solnew.SOLineKey
67 FROM #verpShip wrk
68 JOIN timItem i (NOLOCK) ON i.companyid = wrk.company AND i.itemid = wrk.itemid
69 LEFT OUTER JOIN tsoSOLine sol (NOLOCK) ON sol.SOLineKey = wrk.line_ext_ref_int
70 JOIN tsoSOLine solnew (NOLOCK) ON solnew.SOKey = wrk.doc_ext_ref_int AND solnew.itemkey = i.itemkey AND solnew.status=1 -- open, same order, same item
71 WHERE sol.SOLineKey is null
72
73SELECT 'ASDF', @@ROWCOUNT as linked_lines, docno as SO, shipno, * FROM #verpShip
74
75-- update stock uom
76UPDATE s SET uomid = uom.UnitMeasID
77 FROM #verpShip s
78 JOIN timItem i (NOLOCK) ON i.CompanyID = s.company AND i.ItemID = s.itemid
79 JOIN tciUnitMeasure uom (NOLOCK) ON uom.UnitMeasKey = i.StockUnitMeasKey
80 WHERE s.uomid is null
81
82SELECT TOP 1 @company = s.company
83 , @shiptran = 'M' + RIGHT('000000'+ltrim(str(s.shipno)),6) -- RIGHT('0000000000' + ltrim(str(s.shipno)), CASE WHEN LEN(ltrim(str(s.shipno))) < 5 THEN 5 ELSE LEN(ltrim(str(s.shipno))) END + 1)
84 FROM #verpShip s
85 WHERE s.shiprp = @shiprp
86-----------------------------------
87-- cancel this shipment (reverse)
88-----------------------------------
89--
90-- NB 8/4/19 If we are retransferring a shipment from mobe3 to Sage, and the Sage shipment exists as a pending shipment
91-- , we want to delete that shipment and re-post. If the shipment is already committed in Sage, then this will not be able to go through
92-- and will report back an error
93-- That means that every shipment we push through this proc will first attempt a full cancellation of picks / pending shipment.
94
95--IF @cancel > 0
96--BEGIN
97 DELETE FROM #to2EntitySelectWrk
98
99 SELECT 'ASDF PRIOR ATTEMPTS', wrk.* FROM tumErpTransferLogWrk wrk WHERE wrk.ownerrp = @shiprp
100 DECLARE @shipkeytmp int, @priorshiptemp varchar(10)
101
102 -- There may be multiple prior shipments if Sage broke up the shipment, ie: M000123, M000123.2
103 SELECT @shipkey = ps.shipkey, @priorshiptemp = ps.TranNo
104 FROM tsoPendShipment ps
105 WHERE ps.CompanyId = @Company
106 AND (ps.TranNo = @shiptran OR ps.TranNo LIKE @shiptran + '-%')
107 AND ps.trantype = 810
108
109 -- Cancel the shipment
110 WHILE @ShipKey > 0 AND COALESCE(@shipkeytmp,0) <> @ShipKey
111 BEGIN
112 SET @shipkeytmp = @ShipKey
113 SELECT 'Cancel shipment ' + str(@shipkey) + ' ' + @priorshiptemp
114
115 IF EXISTS(SELECT 1 FROM tsoShipment s (NOLOCK) WHERE s.ShipKey = @ShipKey)
116 BEGIN
117 SELECT @retval = -1, @message = 'Shipment is already committed in Sage, it may not be deleted.'
118 END
119 ELSE IF EXISTS(SELECT 1 FROM tsoPendShipment ps (NOLOCK) WHERE ps.ShipKey = @ShipKey)
120 BEGIN
121
122 SELECT 'ASDF CANCEL SHIPMENT FOR SO ', so.trannorel as SO, @priorshiptemp as TranNo, @shiprp ShipRp, @ShipKey as ShipKey
123 FROM tsoShipLine sl (NOLOCK)
124 JOIN tsoSOLine sol (NOLOCK) ON sol.SOLineKey = sl.SOLineKey
125 JOIN tsoSalesOrder SO (NOLOCK) ON so.sokey = sol.sokey
126 WHERE sl.ShipKey = @ShipKey
127
128 EXEC spoaSODeleteShipment @CompanyID = @company, @shipKey = @ShipKey, @RetVal = @RetVal OUTPUT
129
130 IF COALESCE(@RetVal,0)<> 1
131 BEGIN
132 SELECT @retval=-1, @message = 'Existing shipment cancellation failed'
133 GOTO ERROR
134 END
135 END
136
137 SET @Shipkey = null
138
139 -- get next if valid
140 SELECT @shipkey = ps.shipkey, @priorshiptemp = ps.TranNo
141 FROM tsoPendShipment ps
142 WHERE ps.CompanyId = @Company
143 AND (ps.TranNo = @shiptran OR ps.TranNo LIKE @shiptran + '-%')
144 AND ps.trantype = 810
145
146 END
147--END
148
149-- CANCEL ANY UNSHIPPED PICKS FOR THIS mobe3 SHIPMENT
150-- If not shipped yet, just cancel the pick list
151DELETE FROM #to2EntitySelectWrk
152INSERT #to2EntitySelectWrk (OwnerKey, SessionID, CompanyID, EntityType)
153SELECT DISTINCT sl.ShipLineKey, @@SPID, i.CompanyID, 811
154 FROM #verpShip det
155 JOIN tsoShipLine sl (NOLOCK) ON sl.SOLineKey = det.line_ext_ref_int AND sl.ShipKey is null
156 JOIN timItem i (NOLOCK) ON i.ItemKey = sl.ItemKey
157 WHERE det.shiprp = @shiprp
158
159IF @@ROWCOUNT > 0
160BEGIN
161 SELECT @company = MAX(CompanyID) FROM #to2EntitySelectWrk
162
163 SELECT 'CANCELLING LINES', * FROM #to2EntitySelectWrk
164
165 EXEC spoaSOCancelUnshippedPickLines @CompanyId = @company, @UseEntitySelectWrkSessionId = @@SPID, @userid = @userid, @RetVal = @RetVal OUTPUT
166
167 IF COALESCE(@retval,0)=1
168 BEGIN
169 -- only delete those lines whose ShipLine has already been deleted
170 DELETE wrk
171 FROM tumErpTransferDetWrk wrk
172 LEFT OUTER JOIN tsoShipLine sl (NOLOCK) ON sl.ShipLineKey = wrk.erp_ref_int
173 WHERE doc_ownerrp = @shiprp
174 AND sl.ShipLineKey is null
175
176 DELETE wrk
177 FROM tumErpTransferLogWrk wrk
178 WHERE wrk.ownerrp = @shiprp
179 AND NOT EXISTS(SELECT 1 FROM tumErpTransferDetWrk det WHERE det.doc_ownerrp = @shiprp)
180 END
181END
182
183IF @cancel > 0
184BEGIN
185 IF COALESCE(@retval,0) = 1
186 BEGIN
187 SET @message = 'Shipment reversed'
188 GOTO SPEND
189 END ELSE BEGIN
190 SELECT @retval =-1, @message = 'Shipment cancellation failed'
191 GOTO ERROR
192 END
193END
194
195IF @@TRANCOUNT > 0 COMMIT TRAN
196
197--------------------------------------------------------------------------------------------------------
198-- POST 1 Shipment to Sage
199--------------------------------------------------------------------------------------------------------
200SELECT @message = ''
201
202
203IF @@ROWCOUNT = 0
204BEGIN
205 SELECT @RetVal = 99, @Message = 'Shipment not yet in xChange'
206 GOTO SPEND
207END
208
209IF NOT EXISTS (SELECT 1 FROM tsmUser (NOLOCK) WHERE userid = @userid)
210BEGIN
211 SET @message = 'mobe3admin is not a user in sage.'
212 GOTO ERROR
213END
214
215-- validate fks --
216SELECT s.rp, w.WhseKey, c.CompanyID, i.ItemKey, so.SOKey, sol.SOLineKey, ser.InvtSerialKey, lot.InvtLotKey, b.WhseBinKey, uom.UOMKey
217 , CASE WHEN sl.ShipLineKey > 0 THEN sl.ShipLineKey ELSE null END as log_rowid
218 , CASE WHEN so.Hold > 0 THEN 'SO is on hold' + COALESCE(' for ' + so.HoldReason ,'')
219 WHEN so.status=0 THEN 'SO ' + so.TranNoRel + ' is Unacknowledged'
220 WHEN so.status=2 THEN 'SO ' + so.TranNoRel + ' is Closed'
221 WHEN cust.Hold > 0 THEN 'Customer ' + cust.CustID + ' is on hold'
222 ELSE '' END as SOError
223 , ps.TranNo as ShipTranNo
224 , CASE WHEN COALESCE(sol.Status,0)=1 OR sol.Status is null THEN 0 ELSE 1 END as SOLineClosed
225 INTO #val
226 FROM #verpShip s
227 LEFT OUTER JOIN tumErpTransferDetWrk l (NOLOCK) ON l.xchange_rowid = s.rowid
228 LEFT OUTER JOIN tsoShipLine sl (NOLOCK) ON sl.ShipLineKey = l.erp_ref_int
229 LEFT OUTER JOIN tsoPendShipment ps (NOLOCK) ON ps.shipkey = sl.shipkey
230 LEFT OUTER JOIN timInvtTranDist itd (NOLOCK) ON itd.invttrandistkey = l.ext_ref_int -- see if this row has been sent before
231 LEFT OUTER JOIN tsoSOLine sol (NOLOCK) ON sol.SOLineKey = s.line_ext_ref_int
232 LEFT OUTER JOIN tsoSalesOrder so (NOLOCK) ON so.companyid = s.company AND so.trannorel=s.docno AND so.trantype = 801 -- so.SOKey = sol.SOKey AND so.status = 1
233 LEFT OUTER JOIN tarCustomer cust (NOLOCK) ON cust.CustKey = so.CustKey
234 LEFT OUTER JOIN timItem i (NOLOCK) ON i.itemid = s.itemid AND i.companyid = s.company
235 LEFT OUTER JOIN vo2ItemUOMLight uom (NOLOCK) ON i.ItemKey = uom.ItemKey AND uom.CompanyID = i.CompanyID AND uom.UOMID = s.uomid
236 LEFT OUTER JOIN to2Company c (NOLOCK) ON c.companyid = s.company
237 LEFT OUTER JOIN timWarehouse w (NOLOCK) ON w.whseid = s.whseid AND w.companyid = s.company
238 LEFT OUTER JOIN timInvtLot lot (NOLOCK) ON lot.LotNo = s.lotno AND lot.ItemKey = i.ItemKey AND lot.WhseKey = w.whsekey
239 LEFT OUTER JOIN timInvtSerial ser (NOLOCK) ON ser.SerialNo = s.serno AND ser.ItemKey = i.ItemKey AND Available = 1
240 LEFT OUTER JOIN timWhseBin b (NOLOCK) ON b.WhseBinID = s.erp_locid AND b.whsekey = w.whsekey
241
242-- validations --
243UPDATE s SET proc_message = CASE WHEN v.companyid is null THEN 'Invalid company ' + s.company
244 WHEN v.WhseKey is null THEN 'Invalid warehouse ' + s.whseid
245 WHEN v.ItemKey is null THEN 'Invalid item ' + s.itemid
246 WHEN v.WhseBinKey is null THEN 'Invalid location ' + s.erp_locid
247 WHEN v.SOKey is null THEN 'Invalid SO ' + s.docno
248 WHEN v.SOLineClosed > 0 THEN 'SO Line is closed for SO ' + s.docno + ', item ' + s.itemid
249 WHEN v.SOLineKey is null THEN 'Invalid SO line, key ' + ltrim(str(s.line_ext_ref_int)) + ' for SO ' + s.docno + ', item ' + s.itemid
250 -- WHEN v.InvtLotKey is null AND s.lotno > '' THEN 'Invalid lot ' + s.lotno
251 -- WHEN v.InvtSerialKey is null AND s.serno > '' THEN 'Invalid serial ' + s.serno
252 WHEN v.UOMKey is null THEN 'Invalid UOM ' + s.uomid
253 WHEN v.log_rowid is not null THEN 'Pick lines already exist in Sage for mobe3 shipment ' + ltrim(str(s.shipno)) + ', SO ' + s.docno + ', Sage Shipment ' + COALESCE(v.ShipTranNo,'') + '. If you wish to re-transfer, then cancel these picks in Sage, then retransfer the shipment in mobe3.'
254 WHEN v.SOError > '' THEN v.SOError
255 ELSE '' END
256 , s.whsekey = v.WhseKey, s.itemkey = v.ItemKey, s.invtlotkey = v.InvtLotKey, s.invtserialkey = v.InvtSerialKey
257 , s.uomkey = v.UOMKey, s.whsebinkey = v.WhseBinKey
258FROM #verpShip s
259JOIN #val v ON v.rp = s.rp
260
261UPDATE s SET proc_status = 99
262 FROM #verpShip s
263 JOIN #val v ON v.rp = s.rp
264 WHERE v.log_rowid > 0
265
266IF @@ROWCOUNT > 0 SET @retval = 99 -- already processed, requires user action to delete and retransfer from mobe3
267
268SELECT @message = proc_message FROM #verpShip s WHERE proc_message > ''
269IF @message > ''
270BEGIN
271 SELECT 'ASDF',proc_message FROM #verpShip s WHERE proc_message > ''
272 GOTO ERROR
273END
274
275-- clean out info in case user deleted from Sage
276DELETE FROM tumErpTransferDetWrk WHERE doc_ownerrp = @shiprp
277DELETE FROM tumErpTransferLogWrk WHERE ownerrp = @shiprp
278
279IF @@TRANCOUNT > 0 COMMIT TRAN
280
281-- process all that passed validation --
282DECLARE @shipno int
283
284DECLARE cs1 CURSOR LOCAL FOR
285SELECT distinct s.shiprp, s.company, s.shipno, s.docno
286 FROM #verpShip s
287
288OPEN cs1
289FETCH NEXT FROM cs1 INTO @shiprp, @company, @shipno, @docno
290WHILE @@FETCH_STATUS = 0
291BEGIN
292 SELECT @shipkey = null, @picklistkey = null
293 SELECT 'SHIPMENT', @shipno, @docno
294
295 -- grab all SO lines for this shipment
296 DELETE FROM #to2EntitySelectWrk
297 INSERT #to2EntitySelectWrk (OwnerKey, SessionID, CompanyID, EntityType)
298 SELECT DISTINCT s.line_ext_ref_int, 999, @company, 802
299 FROM #verpShip s
300 WHERE s.shiprp = @shiprp AND s.company = @company
301
302 -- pick --
303 EXEC spoaSOGenMASPickList @CompanyID = @company, @UserId = @userid, @TranDate = @trandate, @OAEventSessionID = @oasessionid, @sessionid = 999, @sokey = null
304 , @WhseKey = 0, @IncludeOOSPicks=1, @PickListKey=@PickListKey OUTPUT, @RetVal = @retval OUTPUT, @ErrMess = @message OUTPUT
305
306 IF COALESCE(@picklistkey,0) > 0 AND EXISTS(SELECT 1 FROM tsoShipLine (NOLOCK) WHERE PickListKey = @picklistkey)
307 BEGIN
308
309 SET @tranno = 'S' + right('0000000000' + ltrim(str(@shipno)), 7)
310
311 -- for retransfers, delete the prior pick list header which remains after the lines are cancelled
312 DELETE pl
313 FROM tsoPickList pl
314 LEFT OUTER JOIN tsoShipLine sl ON sl.PickListKey = pl.PickListKey
315 WHERE pl.PickListNo = @tranno AND pl.CompanyId = @company
316 AND sl.PickListKey is null
317
318 SELECT @count = COUNT(1) FROM tsoPickList pl WHERE pl.CompanyID = @company AND pl.PickListNo LIKE @tranno + '%'
319
320 -- tag pick list with this shipment so we can match later
321 UPDATE tsoPickList SET PickListNo = @tranno + CASE WHEN COALESCE(@count,0)=0 THEN '' ELSE '-' + LEFT(ltrim(str(@count)),1) END
322 WHERE PickListKey = @picklistkey
323
324 UPDATE s SET picklistkey = @picklistkey
325 FROM #verpShip s
326 WHERE s.shiprp = @shiprp AND s.company = @company
327
328 SELECT 'DEBUG SHIP LINES', * FROM tsoShipLine sl JOIN tsoShipLinedist sld ON sld.shiplinekey = sl.shiplinekey WHERE sl.PickListKey = @pickListKey
329
330 INSERT tumErpTransferDetWrk (company, itemid, whseid, erp_locid, stock_qty, rec_date, lotno, proc_Status, proc_message
331 , transferkey, ext_ref, ext_ref_int, erp_ref_int, ownerrp, doc_ownerrp, xchange_rowid)
332 SELECT s.company, s.itemid, s.whseid, s.erp_locid, COALESCE(itd.DistQty,0) , getdate(), s.lotno, 1, ''
333 , @transferkey, '', s.line_ext_ref_int, sl.ShipLineKey, s.rp, @shiprp, s.rowid
334 FROM #verpShip s
335 JOIN #val v ON v.rp = s.rp
336 JOIN tsoShipLine sl (NOLOCK) ON sl.PickListKey = @PickListKey AND sl.ItemKey = s.ItemKey AND sl.SOLineKey = s.line_ext_ref_int
337 LEFT OUTER JOIN timInvtTranDist itd (NOLOCK) ON itd.InvtTranKey = sl.InvtTranKey
338 LEFT OUTER JOIN timInvtLotBin ilb (NOLOCK) ON ilb.InvtLotBinKey = itd.InvtLotBinKey
339 LEFT OUTER JOIN timInvtLot lot (NOLOCK) ON lot.InvtLotKey = ilb.InvtLotKey
340 WHERE sl.picklistkey = @picklistkey
341
342 SELECT 'ASDF ADD DIST'
343 EXEC sperpRepoPullShipsAddDists @picklistkey=@picklistkey, @message=@message OUTPUT, @retval=@retval OUTPUT
344 SELECT 'ASDF ADD DIST RETURN', @retval, @message, @picklistkey as PickListKey
345 SET @retval = 1 -- do not fail if a dist fails
346
347 -- 8/12/19 NB / BN Even if a dist fails, we still want the qty to show up on the shipment
348 --IF COALESCE(@retval,0) < 0
349 --BEGIN
350 -- SELECT 'DIST ERROR', @message
351
352 -- --UPDATE s SET proc_status = @retval, proc_message = @message
353 -- -- FROM #verpShip s
354 -- -- WHERE s.shiprp = @shiprp AND company = @company
355
356 -- -- all or none
357 -- -- EXEC spoaSOCancelUnshippedPickLines @UserID = @UserId, @CompanyId = @Company, @picklistkey = @picklistkey
358 --END
359 --ELSE
360 --BEGIN
361 -- pack and ship --
362 EXEC spoaSOGenMASShipment @CompanyID=@company,@UserId=@userid,@PickListKey=@picklistkey,@TranDate=@trandate,@OAEventSessionID=@oasessionid, @message = @message OUTPUT
363 , @AllLines=1
364
365 -- get shipkey
366 SELECT @shipkey = MIN(shipkey), @count = COUNT(DISTINCT shipkey) FROM tsoShipLine (NOLOCK) WHERE PickListKey = @picklistkey
367
368
369 SELECT 'ASDF SHIPMENT GENERATED', @shiptran, @shipkey as ShipKey, @PickListKey as PickListKey, @message as Message
370 SELECT 'ASDF SHIP INFO', sl.shipkey, itd.*
371 from tsosalesorder so (NOLOCK)
372 join tsosoline sol (NOLOCK)on sol.sokey = so.sokey
373 join tsoshipline sl (NOLOCK)on sl.solinekey = sol.solinekey
374 LEFT OUTER JOIN timInvtTranDist itd (NOLOCK) ON itd.InvtTranKey = sl.InvtTranKey
375 where sl.ShipKey = @ShipKey
376
377 -- SELECT @message = event FROM toaEvent (NOLOCK) WHERE EventSessionID = @OAsessionid AND eventtypekey = 9 AND event > ''
378
379 -- SHIPMENT GEN FAILED
380 IF COALESCE(@shipkey,0) = 0
381 BEGIN
382 IF COALESCE(@message,'') = '' SELECT @message = 'Unknown error generating shipment'
383 END
384
385 IF @shipkey > 0 -- generated
386 BEGIN
387 SET @message = ''
388
389 UPDATE tsoShipLine SET ShipKey = @shipkey WHERE PickListKey = @picklistkey AND ShipKey is null
390
391 DELETE l
392 FROM tsoShipmentLog l
393 LEFT OUTER JOIN tsoPendShipment ps ON ps.ShipKey = l.ShipKey
394 LEFT OUTER JOIN tsoShipment s ON s.ShipKey = l.ShipKey
395 WHERE l.CompanyID = @Company
396 AND s.ShipKey is null
397 AND (l.TranNo = @shiptran OR l.TranNo LIKE @shiptran + '-%')
398 AND ps.ShipKey is null
399
400 SELECT 'ASDF LOG DELETE', @@ROWCOUNT
401 DECLARE @shipnotmp varchar(10), @tmp int
402
403 DECLARE s CURSOR LOCAL READ_ONLY FOR
404 SELECT DISTINCT ShipKey
405 FROM tsoShipLine sl (NOLOCK) WHERE sl.PickListKey = @PickListKey
406
407 OPEN s
408 FETCH NEXT FROM s INTO @shipkeytmp
409 WHILE @@FETCH_STATUS = 0
410 BEGIN
411 SET @shipnotmp = @shiptran + CASE WHEN @count > 1 THEN '-' + ltrim(str(COALESCE(@tmp,0)+1)) ELSE '' END
412 SELECT 'ASDF', @shipnotmp
413 UPDATE tsoPendShipment SET TranNo = @shipnotmp, TranId = @shipnotmp + '-SH' WHERE ShipKey = @shipkeytmp
414 UPDATE tsoShipmentLog SET TranNo = @shipnotmp WHERE ShipKey = @shipkeytmp
415 SET @tmp = COALESCE(@tmp,0) + 1
416 FETCH NEXT FROM s INTO @shipkeytmp
417 END
418 END
419
420 -- insert log, even in failure we want to be able to come back later and cancel the pick lines
421 INSERT tumErpTransferLogWrk (tran_type, sub_type, company, rec_date, transferkey, proc_Status, proc_message
422 , doc_ext_ref, fail_attempts, docno, erp_ref, erp_ref_int, comment, ownerrp)
423 SELECT 'PCK', 'SO', @company, getdate(), @transferkey, @retval, @message
424 , @docno, 0, @docno, @shiptran, @shipkey, '', @shiprp
425
426 -- Presence of @message indicates error
427 IF COALESCE(@message,'') > ''
428 BEGIN
429 select 'ERR' + COALESCE(@message,'')
430
431 UPDATE s SET proc_status = -1, proc_message = @message
432 FROM #verpShip s
433 WHERE s.shiprp = @shiprp
434 END
435 ELSE -- pack
436 BEGIN
437
438 UPDATE p SET TranCmnt = 'mobe3 SO ' + LTRIM(STR(@docno))
439 FROM tsoPendShipment p
440 WHERE ShipKey = @shipkey
441
442 EXEC sperpRepoPullShipsAddPacks @shipkey = @shipkey, @shiprp = @shiprp, @repack=1, @message = @message OUTPUT, @retval = @retval OUTPUT -- standalone proc, reports its own errors
443
444 IF NOT EXISTS(SELECT 1 FROM tsoShipLine sl WHERE ShipKey = @ShipKey)
445 BEGIN
446 SELECT @retval = -1, @message = 'Lines were not created.'
447
448 END ELSE BEGIN
449 IF COALESCE(@Retval,0)=1 SET @message = 'Shipment ' + @shiptran + ' created'
450
451 -- mark passed
452 UPDATE s SET proc_status = 1, proc_message = @message
453 FROM #verpShip s
454 WHERE s.shiprp = @shiprp
455
456 -- ASDF
457
458
459 END
460
461 END
462 --END
463 END
464 ELSE
465 BEGIN -- no pick list
466 UPDATE s SET proc_status = -1, proc_message = COALESCE(@message, 'Failed to generate pick list')
467 FROM #verpShip s
468 WHERE s.shiprp = @shiprp
469
470 SELECT @retval = -1, @message = 'Failed to generate pick list.'
471 END
472
473FETCH NEXT FROM cs1 INTO @shiprp, @company, @shipno, @docno
474END
475CLOSE cs1
476DEALLOCATE cs1
477
478
479SET @rowcount = @@ROWCOUNT
480
481
482--UPDATE e SET proc_status = s.proc_status, proc_message = s.proc_message
483-- FROM export_ship_flat e
484-- JOIN #verpShip s ON s.rowid = e.rowid
485-- WHERE e.rowid IN (SELECT rowid FROM #verpShip) -- Opt
486
487-- for return value, return @shiptran
488SET @tranno = @shiptran
489
490GOTO SPEND
491ERROR: -- in this case we processed nothing
492
493IF COALESCE(@retval,0) IN (0,1) SET @retval = -1
494SELECT 'spumRepoPullShip ERR', @retval, @message
495
496---- report errors to cloud
497--IF OBJECT_ID('tempdb..#verpShip') is not null
498--BEGIN
499-- UPDATE e SET proc_status = s.proc_status, proc_message = CASE WHEN s.proc_message > '' THEN s.proc_message ELSE @message END
500-- FROM export_ship_flat e
501-- JOIN #verpShip s ON s.rowid = e.rowid
502-- WHERE e.shiprp = @shiprp
503-- AND e.rowid IN (SELECT rowid FROM #verpShip) -- Opt
504--END
505
506SPEND:
507
508IF COALESCE(@retval,0) = 0 SET @retval = 1
509IF @@TRANCOUNT > 0 COMMIT TRAN
510
511-- report errors to cloud - join is too slow NB 8/30/19
512DECLARE @tmpprocstatus int, @tmpprocmess varchar(500), @tmprowid bigint
513DECLARE v CURSOR LOCAL READ_ONLY FOR
514 SELECT rowid, proc_status, proc_message FROM #verpShip s
515OPEN v
516FETCH NEXT FROM v INTO @tmprowid, @tmpprocstatus, @tmpprocmess
517WHILE @@FETCH_STATUS=0
518BEGIN
519 UPDATE export_ship_flat SET proc_status = @tmpprocstatus, proc_message = @tmpprocmess WHERE rowid = @tmprowid
520 FETCH NEXT FROM v INTO @tmprowid, @tmpprocstatus, @tmpprocmess
521END
522CLOSE v
523DEALLOCATE v
524
525-- report errors and progress to xchange
526UPDATE e SET proc_status = @retval
527 , proc_message = @message, ext_ref_int = @shipkey, ext_ref = @tranno
528 , pickup_spid = null
529 , status_text = CASE @retval WHEN 0 THEN 'PENDING'
530 WHEN 1 THEN 'POSTED'
531 WHEN 2 THEN 'PENDING REVERSAL'
532 WHEN 3 THEN 'REVERSED'
533 WHEN 99 THEN 'VOID'
534 ELSE '' END
535 FROM export_transfer_log e
536 WHERE e.ownerrp = @shiprp
537
538SELECT 'ASDF2', @retval as retval, @message as message, @transferkey as Transferkey