· 8 years ago · Apr 10, 2018, 06:00 PM
1* Program....: CLSUPSIB.PRG
2* Version....: 1.0
3* Author.....: Richard Berry
4* Date.......: June 10, 1999
5* Notice.....: Copyright (c) 1997 Garpac Corp, All Rights Reserved.
6* Compiler...: Visual FoxPro 05.00.00.0402 for Windows
7* Abstract...: Small Package Inbound Processing Business Class.
8* Changes....:
9
10#INCLUDE system.h
11
12*--- TechRec 1072744 02-Aug-2013 TShenbagavalli ---
13#Define UPS_DEFAULT_DELIMITER ","
14*=== TechRec 1072744 02-Aug-2013 TShenbagavalli ===
15
16DEFINE CLASS UPSInbound AS BaseBusiness
17 Name = "UPSInbound"
18
19 cTitle = "Small Package Inbound Process"
20 cDirectory = ""
21 cFieldList = ""
22 cHistoryDirectory = ""
23 cHistoryExtension = ""
24 cJobID = "UPSINBOUND" && Tan 35180 - SST
25 lIncludeLog = true && Tan 35180 - SST
26 cErrorLog = ""
27 cErrorLogWithPath = ""
28 cUPSFileName = ""
29 cShpstCode = '' && TAN 38304
30 nUPSBatch = 0
31 nTotalPicks = 0
32 nPicksUpdated = 0
33 nPicksFailed = 0
34 lSuppressWarning = true && To suppress error log warning msg UI
35 lCartonLevel = false && TAN 27379
36 lServerSide = false && TAN 35180
37 lShowPrintLog = true
38 lProrateFreight = false && TAN 36488
39 cOrderView = "Vzzoordrh_SPS_Inbound"
40 cInvoiceView = "Vzzoshprh_SPS_Inbound"
41 cPackageView = "Vzzoupsin_SPS_Inbound"
42 cCartonView = "Vzzoctnph_SPS_Inbound"
43 cSPSView = "Vzzoupsib_SPS_Inbound" && TAN 35180
44 cPickTicketView = "" && .cOrderView or .cInvoiceView depending on pick ticket
45 lActualFrgt = true && TR 1001791
46 lUpdateActShipDate = .f. && TechRec 1034271 20-Aug-2008 MA ---
47
48 *--- TechRec 1064004 11-Oct-2012 jjanand ---
49 cPickLstTemp = ''
50 cImportTemp = ''
51 clastCtnMod = ''
52 cTempUpdateCarton = ''
53 lOptimized = false
54 *=== TechRec 1064004 11-Oct-2012 jjanand ===
55
56 DIMENSION aUPSImportFiles[1]
57
58 *--- TR 1002172 5-Dec-2003 VKK Added Property SingleLocation
59 lSingleLocation = false
60 *=== TR 1002172 5-Dec-2003 VKK
61
62 *--- TR 1012974 22-NOV-2005 VKK
63 cWareHouseStage = ""
64 lInsertAutoBill = false
65 cPickNumListForBatch = ""
66 *=== TR 1012974 22-NOV-2005 VKK
67
68 *--- TR 1028259 - B.Diependaele 11/19/07 - Renamed property lSerial_Number_Enable to lProcessMasterCartons
69
70 *--- TechRec 1018111 04-Sep-2006 aelgendy ---
71 *lSerial_Number_Enable = false
72 *=== TechRec 1018111 04-Sep-2006 aelgendy ===
73
74 lProcessMasterCartons = false
75 *=== TR 1028259 - B.Diependaele 11/19/07
76
77 *--- TechRec 1054422 03-Jun-2011 TShenbagavalli ---
78 nWhseStageSeq = 0
79 nShpstCodeSeq = 0
80 oSB = Null
81 *=== TechRec 1054422 03-Jun-2011 TShenbagavalli ===
82
83 *--- TR 1060620 04-06-2012 RKI ---*
84 lIgnoreWeight = false
85 *=== TR 1060620 04-06-2012 RKI ===*
86
87 *--- TechRec 1079321 18-Jun-2014 GSternik ---
88 nChunkSize = 0
89 cPickLstAll = ""
90 *=== TechRec 1079321 18-Jun-2014 GSternik ===
91
92 *--- TechRec 1090349 10-Dec-2015 jisingh ---
93 cPrmCustomer = ""
94 *=== TechRec 1090349 10-Dec-2015 jisingh ===
95
96*==========================================
97
98 *--- Tan35180 SST
99 FUNCTION Init
100 LOCAL llRetVal
101
102 llRetVal = DoDefault()
103
104 WITH THIS
105 IF llRetVal
106 .lServerSide = (goEnv.SV('SPS_INBOUND_SERVER_SIDE', 'N') = 'Y')
107
108 *--- TechRec 1064004 11-Oct-2012 jjanand ---
109 .lOptimized = (goEnv.SV('OPTIMIZED_SPS_INBOUND', 'N') = 'Y')
110 *=== TechRec 1064004 11-Oct-2012 jjanand ===
111
112 *--- TechRec 1079321 18-Jun-2014 GSternik ---
113 If .lServerSide
114 .nChunkSize = Val(goEnv.SV("SPS_INBOUND_CHUNK_SIZE", "0"))
115 EndIf
116 *=== TechRec 1079321 18-Jun-2014 GSternik ===
117
118 *--- TR 1002172 5-Dec-2003 VKK Added Property SingleLocation
119 .lSingleLocation = (goEnv.SV('SPS_INBOUND_SINGLE_LOCATION', 'N') = 'Y')
120
121 *--- Serverside cannot have SingleLocation
122 .lSingleLocation = NOT .lServerSide AND .lSingleLocation
123 *=== TR 1002172 5-Dec-2003 VKK
124
125 *--- TR 1012974 22-NOV-2005 VKK
126 .cWarehouseStage = vl_ystage("SPSINBOUND", "shpst_code")
127 *=== TR 1012974 22-NOV-2005 VKK
128
129 *--- TechRec 1054422 03-Jun-2011 TShenbagavalli ---
130 .oSB = NEWOBJECT("SQLStringBuilder", "clsgnstr.prg")
131 llRetVal = llRetVal AND IsObject(.oSB, true)
132
133 IF NOT EMPTY(.cWarehouseStage)
134 .nWhseStageSeq = vl_shpst(.cWarehouseStage, "Sequence")
135 ENDIF
136 *=== TechRec 1054422 03-Jun-2011 TShenbagavalli ===
137
138 *--- TR 1028259 - 11/19/07 B.Diependaele - lSerial_Number_Enable is not used anymore -> use lProcessMasterCartons
139
140 *--- TechRec 1018111 04-Sep-2006 aelgendy ---
141 *.lSerial_Number_Enable = (goEnv.SV('AWS_SERIAL_NUMBER_ENABLED', 'N') = 'Y')
142 *=== TechRec 1018111 04-Sep-2006 aelgendy ===
143
144 *--- TR 1028259 - 11/19/07 B.Diependaele - New property lProcessMasterCartons (uses new system parameter SPS_PROCESS_MASTER_CARTON)
145 .lProcessMasterCartons = (goEnv.SV('AWS_SERIAL_NUMBER_ENABLED', 'N') = 'Y') OR ;
146 (goEnv.SV('SPS_PROCESS_MASTER_CARTON', 'N') = 'Y')
147 *=== TR 1028259 - 11/19/07 B.Diependaele
148
149 *--- TechRec 1034271 20-Aug-2008 MA ---
150 .lUpdateActShipDate = (goEnv.SV('SPS_INB_UPD_ACT_SHIP_DATE', 'N') = 'Y')
151
152 *--- TR 1060620 04-06-2012 RKI ---*
153 .lIgnoreWeight = (goEnv.SV('SPS_INBOUND_IGNORE_WEIGHT', 'N') = 'Y')
154 *=== TR 1060620 04-06-2012 RKI ===*
155
156 ENDIF
157 ENDWITH
158 RETURN llRetVal
159 ENDFUNC
160 *=== Tan35180 SST
161
162*===============================
163
164 FUNCTION ImportUPS
165 LPARAMETERS pcLocation, pcPack_Syst, pcFileName, pcTarget
166
167 &&--- TechRec 1072744 02-Aug-2013 TShenbagavalli added lcDelimiter ===
168 LOCAL lnSelect, lcFileName, lcFieldList, lcFileType, lcPack_Syst, llRetVal, lcDelimiter
169
170 *---TR 1071384 05/31/2013 YE
171 LOCAL lcSPSRenamed, lcMsg
172 *===TR 1071384 05/31/2013 YE
173
174 lnSelect = SELECT()
175 lcFieldList = This.cFieldList
176
177 *--- TR 1060466 05/23/12 JIJO - Commented and added new vl_pksys call with Location parameter ===
178*!* lcPack_Syst = IIF(NOT vl_pksys(pcPack_Syst), "", pcPack_Syst)
179*!* llRetVal = vl_pksys(lcPack_Syst, '', 'tcPkSys')
180 lcPack_Syst = IIF(NOT vl_pksys(pcPack_Syst,,,pcLocation), "", pcPack_Syst)
181 llRetVal = vl_pksys(lcPack_Syst, '', 'tcPkSys', pcLocation)
182 *=== TR 1060466 05/23/12 JIJO ===
183
184 llRetVal = llRetVal AND ALLTRIM(tcPkSys.ext_ib) == JUSTEXT(pcFileName)
185
186 IF llRetVal
187 lcFileType = IIF(tcPkSys.hdr_ib = 'Y', UPS_FILE_TYPE_CSV, ;
188 UPS_FILE_TYPE_DELIMITED)
189 lcFileName = This.cDirectory + ALLTRIM(pcFileName)
190
191 *--- TechRec 1072744 02-Aug-2013 TShenbagavalli ---
192 lcDelimiter = tcPkSys.ib_delimiter
193 *=== TechRec 1072744 02-Aug-2013 TShenbagavalli ===
194
195 * TAN 27378 Inbound file is DELIMITED or CSV based on Package System Reference flag
196 * TAN 28124 Only import files with extensions defined on Package System Reference
197 * TAN 27379 Include carton number for inbound carton level;
198 * Process weight and bill weight as decimals.
199
200 *--- Tan33953 HH 09/10/02
201 *CREATE CURSOR &pcTarget (pick_num C(10), charge_1 N(10,2), charge_2 N(10,2), ;
202 * weight N(11,3), carton_id C(20), bill_wgt N(11,3), ;
203 * status I(4), carton_num c(20), location C(6))
204
205 *--- Tan 35180 SST 11/15/02 -
206 * Introducs Serv_Code in the Cursor
207 * Modified Pick_Num to I(4) from C(10)
208 * Weight to N(11,3) to N(10,3)
209
210 *--- TAN 39764 - Extended carton_id from C20 to C30 -AG - 05/28/03
211 IF .lServerSide
212 CREATE CURSOR &pcTarget (pick_num I(4) , charge_1 N(10,2), charge_2 N(10,2), ;
213 weight N(10,3), carton_id C(30), bill_wgt N(11,3), ;
214 status c(1), serv_code C(3), carton_num c(20), ;
215 location C(6), pack_syst c(3), pKey i(4);
216 ,import_ok C(1), tb_carrier_type c(20), tb_carton_id C(16), tb_dt_act T, tb_manifest_close_id C(20) ; && TR 1072518 31-JUL-13 Venuk.
217 ,tb_shipper_id C(15), ref_num C(30), zpl_label_file C(254),retn_track_no C(30),retn_track_no_02 C(30) ; && TR 1072518 31-JUL-13 Venuk.
218 ,track_no_02 C(30) ) &&--- TechRec 1079799 16-Jul-2014 TSV added track_no_02 ===
219 ELSE
220
221 *--- TR 1007429 08-OCT-2004 KA
222 * Added oversize_code to pcTarget
223
224 *--- TR 1011161 07/Jun/2005 SK
225 * Added Book # and Page #, Changed OverSize_Code to C(5)
226*!* CREATE CURSOR &pcTarget (pick_num I(4), charge_1 N(10,2), charge_2 N(10,2), ;
227*!* weight N(10,3), carton_id C(30), bill_wgt N(11,3), ;
228*!* status c(1), oversize_code I(4), carton_num c(20), ;
229*!* location C(6))
230 CREATE CURSOR &pcTarget (pick_num I(4), charge_1 N(10,2), charge_2 N(10,2), ;
231 weight N(10,3), carton_id C(30), bill_wgt N(11,3), ;
232 status c(1), oversize_code C(5), carton_num c(20), ;
233 book_num I(4), page_num I(4), ;
234 location C(6);
235 , tb_carrier_type c(20), tb_carton_id C(16), tb_dt_act T, tb_manifest_close_id C(20) ; && TR 1072518 31-JUL-13 Venuk.
236 ,tb_shipper_id C(15), ref_num C(30), pack_syst C(3), serv_code C(3), import_ok C(1), zpl_label_file C(254),retn_track_no C(30),retn_track_no_02 C(30) ; && TR 1072518 31-JUL-13 Venuk.
237 ,track_no_02 C(30) ) &&--- TechRec 1079799 16-Jul-2014 TSV added track_no_02 ===
238 *=== TR 1011161 07/Jun/2005 SK
239
240 *=== TR 1007429 08-OCT-2004 KA
241
242 ENDIF
243 *=== TAN 39764 AG
244
245 *=== Tan 35180 SST
246
247 *=== Tan33953 HH 09/10/02
248
249 SELECT (pcTarget)
250
251 *--- TechRec 1072744 02-Aug-2013 TShenbagavalli ---
252 *APPEND FROM (lcFileName) FIELDS &lcFieldList TYPE &lcFileType
253 IF lcDelimiter = UPS_DEFAULT_DELIMITER
254 APPEND FROM (lcFileName) FIELDS &lcFieldList TYPE &lcFileType
255 ELSE
256 lcDelimiter = "'" + ALLTRIM(lcDelimiter) + "'"
257 APPEND FROM (lcFileName) FIELDS &lcFieldList DELIMITED WITH CHARACTER &lcDelimiter
258 ENDIF
259 *=== TechRec 1072744 02-Aug-2013 TShenbagavalli ===
260
261 *---TR 1071384 05/31/2013 YE - Rename just after APPEND FROM. The renamed file is still in inbound (not history) folder and will be moved to history later.
262 lcSPSRenamed = .cDirectory + JUSTSTEM(pcFileName) + "." + .cHistoryExtension
263
264 IF FILE(lcSPSRenamed)
265 lcMsg = "INFO: File " + lcSPSRenamed + ;
266 " will be overwritten."
267 .oLog.LogEntry(lcMsg)
268 DELETE FILE (lcSPSRenamed)
269 ENDIF
270 .oLog.LogEntry("Renaming " + .cDirectory + pcFileName + " to " + lcSPSRenamed)
271 RENAME (.cDirectory + pcFileName) TO (lcSPSRenamed)
272 *===TR 1071384 05/31/2013 YE
273
274 *--- Tan33953 HH 09/10/02
275 * DELETE FOR status = UPS_STATUS_VOID IN (pcTarget)
276 * DELETE FOR status == "Y" IN (pcTarget)
277
278 *--- TAN 33953 Make backward compatible for those who don't have worldship - RRB 11/01/02.
279 DELETE FOR status == "Y" OR status == "2" IN (pcTarget)
280 *=== TAN 33953 RRB 11/01/02
281
282 *=== Tan33953 HH 09/10/02
283
284 *--- TR 1002172 20-Jan-2004 VKK Added .lsinglelocation condiation. Location needs to be updated only for Multi Location
285 IF NOT .lSingleLocation
286 REPLACE ALL location WITH pcLocation IN (pcTarget)
287 ENDIF
288
289 *--- TR 1007429 08-OCT-2004 KA
290 IF NOT .lServerSide
291 SELECT (pcTarget)
292
293 *--- TR 1023301 04/19/07 RCO Accounting for the Case oversize_code = 'N' in pcTarget cursor
294
295 *--- TAN 1017202 05/24/06 AZ
296 *|* SCAN FOR NOT EMPTY(oversize_code)
297 *!* SCAN FOR NOT (EMPTY(oversize_code) OR UPPER(oversize_code) = 'NONE')
298 SCAN FOR NOT (EMPTY(oversize_code) OR UPPER(oversize_code) = 'NONE' OR UPPER(oversize_code) = 'N')
299 *=== TAN 1017202 05/24/06 AZ
300
301 *=== TR 1023301 04/19/07 RCO Accounting for the Case oversize_code = 'N' in pcTarget cursor
302
303 REPLACE weight WITH vl_OverSize(oversize_code,'Bill_wgt')
304 ENDSCAN
305 ENDIF
306 *=== TR 1007429 08-OCT-2004 KA
307
308 This.lCartonLevel = (tcPkSys.pk_ctn_ib = 'C') && TAN 27379 Carton level
309 This.lProrateFreight = (tcPkSys.prorate_ib = 'Y') && TAN 36488
310 This.cShpstCode = tcPkSys.shpst_code && TAN 38304 AM
311
312 *--- TechRec 1054422 28-Jun-2011 TShenbagavalli ---
313 This.nShpstCodeSeq = vl_shpst(This.cShpstCode, "Sequence")
314 *=== TechRec 1054422 28-Jun-2011 TShenbagavalli ===
315
316 *--- TR 1012974 22-NOV-2005 VKK Auto bil
317 This.lInsertAutoBill = (tcPkSys.aBill_Ok = "Y")
318 *=== TR 1012974 22-NOV-2005 VKK
319
320 ENDIF
321
322 * --- TR 1025728 7/25/07 CM
323 * Need to use this table in Update Pickets(), so we cannot close now
324 *This.TableClose('tcPkSys')
325 * === TR 1025728 7/25/07 CM
326
327 SELECT (lnSelect)
328 RETURN llRetVal
329 ENDFUNC
330
331*=====================================================
332
333 FUNCTION ProcessUPSInbound
334 *--- TechRec 1090349 11-Dec-2015 jisingh Added pcCustomer ===
335 LPARAMETERS plScheduled, poLog, pcCustomer && TR 1059711 02/22/2012 YE - Added new parameter poLog
336
337 LOCAL llRetVal, lnSelect, lnUPSImportFiles, lcMsg, lcErrorHist, lcSyslockTablePath,;
338 lcSQLString
339
340 llRetVal = true
341 lnSelect = SELECT()
342
343 * Path to SysLock directory on server (same place as Login.DBF)
344 lcSyslockTablePath = AddBS(goEnv.envLoginTablePath.VALUE)
345 IF NOT v_SysLock( lcSyslockTablePath+"SYSLOCK", This.cJobID, goEnv.cCompany)
346 RETURN false
347 ENDIF
348
349 WITH This
350 .lNoDataFound = true
351
352 *--- TR 1028386 16-Nov-2007 Partha ---
353
354 *---TR 1043020/1045297 - YE - Put this back. This was originally done by 1043020 but it was stepped on.
355 .lScheduled = plScheduled
356 *===TR 1043020/1045297 - YE
357
358 *=== TR 1028386 16-Nov-2007 Partha ===
359
360 .cDirectory = GetCompanyDirectoryPath() + UPS_INBOUND_DIR
361 .cHistoryDirectory = GetCompanyDirectoryPath() + UPS_HISTORY_DIR
362 .cErrorLog = UPS_ERROR_LOG
363 .cErrorLogWithPath = goEnv.SystemParm(SP_PATH_LOGS) + ;
364 goEnv.SystemParm(SP_COMP_CODE) + "_" + .cErrorLog
365
366 *---TR 1007429 08-OCT-2004 KA
367 *.cFieldList = "pick_num,charge_1,charge_2,weight,carton_id,bill_wgt,status,carton_num"
368 IF .lServerSide
369 .cFieldList = "pick_num,charge_1,charge_2,weight,carton_id,bill_wgt,status,carton_num "
370 ELSE
371
372 *--- TR 1011161 07/Jun/2005 SK
373*!* .cFieldList = "pick_num,charge_1,charge_2,weight,carton_id,bill_wgt,status,oversize_code,carton_num"
374 .cFieldList = "pick_num,charge_1,charge_2,weight,carton_id,bill_wgt,status,oversize_code,carton_num,book_num,page_num"
375 *=== TR 1011161 07/Jun/2005 SK
376
377 *--- TR 1072518 31-JUL-13 Venuk.
378 .cFieldList = .cFieldList + ", location, serv_Code, pack_syst, tb_carrier_type, tb_carton_id, tb_dt_act, tb_manifest_close_id, tb_shipper_id "
379 .cFieldList = .cFieldList + ", ref_num, import_ok, zpl_label_file, retn_track_no, retn_track_no_02 "
380 *=== TR 1072518 31-JUL-13 Venuk.
381
382 *--- TechRec 1079799 16-Jul-2014 TSV---
383 .cFieldList = .cFieldList + ", track_no_02 "
384 *=== TechRec 1079799 16-Jul-2014 TSV===
385
386 ENDIF
387 *===TR 1007429 08-OCT-2004 KA
388
389 *--- Tan 35180 SST
390 DELETE FILE (.cErrorLogWithPath) && Start afresh
391
392 *---TR 1053301 20-Oct-11 SK Instantiate the log is not required since .oLog already assigned in UPSIBSTB
393 *.InstantiateLogging()
394
395 *---TR 1059711 02/22/2012 YE -- Need to instantiate logging if not passing a log object
396 IF TYPE("polog") <> "O"
397 .InstantiateLogging()
398 ENDIF
399 *===TR 1059711 02/22/2012 YE
400
401 .oLog.OpenLog(.cJobID, I(.cJobID), .lScheduled, .cErrorLog)
402 .oLog.LogProgram("clsupsib.prg")
403 .oLog.LogEntry("Import Mode: " + IIF(.lServerSide, "Server Side", "Flat Files"))
404 .oLog.LogEntry("Optimization Mode: " + IIF(.lOptimized, "Optimized", "Non Optimized"))
405
406 *--- TR 1060620 04-09-2012 RKI ---*
407 .oLog.LogEntry("Sparrow SPS_INBOUND_IGNORE_WEIGHT = '" + IIF(This.lIgnoreWeight,"Y","N")+ "'")
408 *=== TR 1060620 04-09-2012 ===*
409
410 *--- TechRec 1090349 10-Dec-2015 jisingh ---
411 .cPrmCustomer = pcCustomer
412 .oLog.LogEntry("Customer : " + .cPrmCustomer)
413 *=== TechRec 1090349 10-Dec-2015 jisingh ===
414
415 * Included additional fields for server side
416 .cFieldList = .cFieldList + IIF(.lServerSide, ",location,serv_code,pack_syst,pkey", "")
417 *=== Tan 35180 SST
418
419 *--- TR 1072518 31-JUL-13 Venuk.
420 .cFieldList = .cFieldList + IIF(.lServerSide, ", tb_carrier_type, tb_carton_id, tb_dt_act, tb_manifest_close_id, tb_shipper_id ", "")
421 .cFieldList = .cFieldList + IIF(.lServerSide, ", ref_num, import_ok, zpl_label_file", "")
422 .cFieldList = .cFieldList + IIF(.lServerSide, ", retn_track_no, retn_track_no_02", "")
423 *=== TR 1072518 31-JUL-13 Venuk.
424
425 *--- TechRec 1079799 16-Jul-2014 TSV---
426 .cFieldList = .cFieldList + IIF(.lServerSide, ", track_no_02", "")
427 *=== TechRec 1079799 16-Jul-2014 TSV===
428
429 .nUPSBatch = v_NextID('ZZXCOMPR', "UPS_BATCH")
430
431 IF EMPTY(.nUPSBatch)
432 .ErrorBox(.cTitle + " failed." + CRLF + ;
433 "Could not get a small package batch number from the company table.", .cTitle)
434 RETURN false
435 ENDIF
436 .cHistoryExtension = PADL(RIGHT(ALLTRIM(STR(.nUPSBatch)), 3), 3, '0')
437
438 IF NOT vl_pksys('')
439 .ErrorBox(.cTitle + " failed." + CRLF + CRLF + ;
440 "You must enter a default record with a blank package system code on the" + CRLF + ;
441 "small package system reference before running this process.")
442 RETURN false
443 ENDIF
444
445 * TAN 35180 Move this higher up before we instantiate log
446 * DELETE FILE (.cErrorLogWithPath) && Start afresh
447 DIMENSION .aUPSImportFiles[1]
448 .aUPSImportFiles[1] = false
449
450 *--- Tan35180 SST
451 * creating flat file from server
452 IF .lServerSide AND NOT .CreateFlatFilesFromServer() && *---TR 1065406 01/18/2014 YE
453
454 *---TR 1065406 01/18/2014 YE
455 *.CreateFlatFilesFromServer()
456 lcMsg = " Creating flat files from Server failed."
457 .oLog.LogEntry(lcMsg)
458 *===TR 1065406 01/18/2014 YE
459
460 ENDIF
461 *=== Tan35180 SST
462
463 lnUPSImportFiles = ADIR(.aUPSImportFiles, .cDirectory + "*.???")
464
465 lcMsg = "INFO: " + .cTitle + " started - Batch No. " + ALLTRIM(STR(.nUPSBatch))
466
467 *--- Tan35180 SST
468 *UdateLog(.cErrorlog, lcMsg) && After ADIR()
469 .oLog.LogEntry(lcMsg)
470 *=== Tan35180 SST
471
472 .CreateFormProgressBar(.lScheduled)
473
474 * ATS 4388/4432
475 .SetCaption(.cTitle)
476 .InitProgressBarTotal(lnUPSImportFiles, "Total " + .cTitle)
477 INKEY(0.2)
478
479 * TAN 27379
480 IF lnUPSImportFiles > 0 && TAN 32230
481 llRetVal = llRetVal AND .CreateUPSInboundViews()
482 llRetVal = llRetVal AND .ProcessUPSInboundFiles() && Main Processing
483
484 .CloseUPSInboundViews()
485 ENDIF
486
487 IF IsObject(.oFrmProgressBar) AND NOT IsNull(.oFrmProgressBar)
488 .m_close()
489 ENDIF
490
491 IF llRetVal AND .lNoDataFound
492 lcMsg = "There are no small package files to process."
493
494 *--- TAN 35180 - SST
495 .oLog.LogEntry("INFO: " + lcMsg)
496 *UdateLog(.cErrorlog, "INFO: " + lcMsg)
497 *=== TAN 35180 - SST
498
499 .lShowPrintLog = false
500 .InfoBox(lcMsg, .cTitle)
501 ENDIF
502
503 *--- TAN 35180 - SST
504 *UdateLog(.cErrorlog, '') && Spacing for readability
505 *=== TAN 35180 - SST
506
507 lcMsg = "INFO: Pick Tickets Processed = " + ALLTRIM(STR(.nTotalPicks)) &&--- TechRec 1079321 18-Jun-2014 GSternik === not "Total" here
508
509 *--- Tan35180 SST
510 *UdateLog(.cErrorlog, lcMsg)
511 .oLog.LogEntry(lcMsg, true)
512 *=== Tan35180 SST
513
514 lcMsg = "INFO: Pick Tickets Updated = " + ALLTRIM(STR(.nPicksUpdated))
515
516 *--- Tan35180 SST
517 *UdateLog(.cErrorlog, lcMsg)
518 .oLog.LogEntry(lcMsg)
519 *=== Tan35180 SST
520
521 lcMsg = "INFO: Pick Tickets Failed = " + ALLTRIM(STR(.nPicksFailed))
522
523 *--- Tan35180 SST
524 *UdateLog(.cErrorlog, lcMsg)
525 .oLog.LogEntry(lcMsg)
526 *=== Tan35180 SST
527
528 lcMsg = "INFO: " + .cTitle + " finished - Batch No. " + ALLTRIM(STR(.nUPSBatch))
529
530 *--- Tan35180 SST
531 *UdateLog(.cErrorlog, lcMsg)
532 .oLog.LogEntry(lcMsg)
533 *=== Tan35180 SST
534
535 * Copy Errorlog
536 lcErrorHist = .cHistoryDirectory + JUSTSTEM(UPS_ERROR_LOG) + "." + .cHistoryExtension
537 COPY FILE (.cErrorLogWithPath) TO (lcErrorHist)
538
539 .TableClose("tcImport")
540
541 IF .lShowPrintLog AND NOT .lScheduled
542 LaunchUPSInboundLog() && See bclib.prg
543 ENDIF
544
545 * Close Log
546 .oLog.LogResult(llRetVal)
547 .oLog.CloseLog()
548 ENDWITH
549
550 v_SysUnLock( lcSyslockTablePath+"SYSLOCK", "UPSINBOUND", goEnv.cCompany)
551
552 *--- TR 1016461/1017374 05/11/2006
553
554 *--- TR 1012974 22-NOV-2005 VKK
555 * IF NOT EMPTY(This.cPickNumListForBatch)
556 * gcProcessFilter = "PICK_NUM~" + RemoveLastDelimiter(This.cPickNumListForBatch, "|")
557 * glFilterOverride = false
558 * ENDIF
559 *=== TR 1012974 22-NOV-2005 VKK
560
561 IF NOT EMPTY(This.cPickNumListForBatch)
562 gcProcessFilter = "PICK_NUM~" + RemoveLastDelimiter(This.cPickNumListForBatch, "|")
563 ELSE
564 * We want to process no data in the next process in the batch
565 gcProcessFilter = "PICK_NUM~-1"
566 ENDIF
567
568 glFilterOverride = false
569 *=== TR 1016461/1017374 05/11/2006
570
571 SELECT (lnSelect)
572 RETURN llRetVal
573 ENDFUNC
574
575*=====================================================
576
577 FUNCTION UpdatePickTickets
578 LPARAMETERS pcImport
579
580 *--- TR 1002172 5-Dec-2003 VKK Added lcpicklocation variable
581 *--- TechRec 1054422 31-May-2011 TShenbagavalli added lcPickList, llMastCtn, lcFinalPickList, lnCtnStgeSeq ---
582 LOCAL lnSelect, lcMsg, llBeganTransaction, lcPickTicketView, ;
583 llUpdateSuccess, lnPKey, lnCarton, lnFrgt_Amt, lnWeight, lnPick_Num, lcPick_Num, ;
584 lcLocation, loPackage, llOverwriteOrder, llCartonFound, llContinue, lcShipper, ;
585 lcImp_Frgt, lcPickLocation, lcPickList, llMastCtn, lcFinalPickList, lnCtnStageSeq && Tan 36097 SST
586 PRIVATE vnOrderPkey, vnCartonFkey, vnPick_Num
587
588 *--- TechRec 1054422 03-Jun-2011 TShenbagavalli ---
589 lnCtnStageSeq = 0
590 *=== TechRec 1054422 03-Jun-2011 TShenbagavalli ===
591
592 *--- TR 1016461/1017374 05/11/2006
593 LOCAL llProcessPick, lcPickTicketTable
594 *=== TR 1016461/1017374 05/11/2006
595
596 * --- TR 1025728 7/25/07 CM --- Added lcFrgt_Round
597 LOCAL lcFrgt_Round
598 * === TR 1025728 7/25/07 CM
599
600 *---TR 1024359 5/23/07 YE
601 LOCAL llInvoiced_AND_AR_Posted
602 *===TR 1024359 5/23/07 YE
603
604 *---TR 1053337 06/20/11 EB They want to respect over_frgt flag in SO Screen Additional
605 LOCAL lcOverFreight,llPreserveFreight
606 *===TR 1053337 06/20/11 EB
607
608 lnSelect = SELECT()
609
610 *--- Tan 36097 SST
611 lcImp_Frgt = "Y"
612 *=== Tan 36097 SST
613
614 WITH This
615 v_SqlExec("SELECT DISTINCT pick_num, location FROM " + pcImport + " ORDER BY pick_num", "tcPick",, true)
616
617 .nTotalPicks = .nTotalPicks + RECCOUNT('tcPick')
618 .nPicksFailed = .nPicksFailed + RECCOUNT('tcPick')
619
620 .InitThermo(RECCOUNT('tcPick'))
621 .UpdateThermoCaption("Processing file " + .cUPSFileName + "...")
622 INKEY(0.2)
623
624 SELECT tcPick
625 SCAN
626 llContinue = true && Initialize inside the SCAN for each pick ticket
627
628 *--- TR 1029068 NSD 12/4/07
629 * Set this to false prior to processing
630 llInvoiced_AND_AR_Posted = .F.
631 *=== TR 1029068 NSD 12/4/07
632
633 *--- TAN 35180 SST
634*!* lcPick_Num = tcPick.pick_num && No ALLTRIM
635*!* lnPick_Num = INT(VAL(tcPick.pick_num))
636 lcPick_Num = STR(tcPick.pick_num)
637 lnPick_Num = tcPick.pick_num
638 *=== TAN 35180 SST
639
640 lcLocation = tcPick.Location
641 llOverwriteOrder = false
642
643 IF llContinue
644*!* IF lnPick_Num <= 0 OR (NOT vl_ordrhpk(lnPick_Num,,'tcOrder', lcLocation) AND ;
645*!* NOT vl_shprhpk(lnPick_Num,,'tcOrder', lcLocation))
646 *--- TR 1002172 5-Dec-2003 VKK Replaced Old functions with vl_ordrhpk & vl_shprhpk with allowing blank location
647 *-- These new function will check for blank location and ignore the location condition if it is blank
648 IF lnPick_Num <= 0 OR (NOT vl_ordpickChk(lnPick_Num,,'tcOrder', lcLocation) AND ;
649 NOT vl_shppickChk(lnPick_Num,,'tcOrder', lcLocation))
650 lcMsg = "ERROR: Pick ticket " + ALLTRIM(lcPick_Num) + " cannot be processed - pick number not found."
651
652 *--- Tan35180 SST
653 *UdateLog(.cErrorlog, lcMsg)
654 .oLog.LogEntry(lcMsg)
655 *=== Tan35180 SST
656
657 llContinue = false
658 ENDIF
659 ENDIF
660
661 IF llContinue
662 IF tcOrder.Ord_Status = "I" AND tcOrder.Ar_Post = AR_POSTED
663 lcMsg = "ERROR: Pick ticket " + ALLTRIM(lcPick_Num) + " cannot be processed - invoice was posted to A/R."
664
665 *--- Tan35180 SST
666 *UdateLog(.cErrorlog, lcMsg)
667 .oLog.LogEntry(lcMsg)
668 *=== Tan35180 SST
669
670 *---TR 1024359 5/23/07 YE - Additional line to log - llContinue not set to false.
671 lcMsg = lcMsg + " This pick ticket will not be processed again. "
672 .oLog.LogEntry(lcMsg)
673 llInvoiced_AND_AR_Posted = .T.
674 *llContinue = false
675 *===TR 1024359 5/23/07 YE
676
677 ENDIF
678 ENDIF
679
680 * Open order view
681
682 *---TR 1024359 5/23/07 YE
683 *IF llContinue
684 IF llContinue AND NOT (llInvoiced_AND_AR_Posted)
685 *===TR 1024359 5/23/07 YE
686
687 vnOrderPkey = tcOrder.pKey
688 .cPickTicketView = IIF(tcOrder.Ord_Status = "I", .cInvoiceView, .cOrderView)
689
690 *--- TR 1016461/1017374 05/11/2006
691 lcPickTicketTable = IIF(tcOrder.Ord_Status = "I", "zzoshprh", "zzoordrh")
692 *=== TR 1016461/1017374 05/11/2006
693
694 llContinue = llContinue AND .OpenTable(.cPickTicketView,,true,,,,true) && Open with data
695 IF NOT llContinue
696 lcMsg = "ERROR: Pick ticket " + ALLTRIM(lcPick_Num) + " cannot be processed - unable to retrieve pick ticket."
697
698 *--- Tan35180 SST
699 *UdateLog(.cErrorlog, lcMsg)
700 .oLog.LogEntry(lcMsg)
701 *=== Tan35180 SST
702
703 ENDIF
704 ENDIF
705
706 * Open packages view
707
708 *---TR 1024359 5/23/07 YE
709 *IF llContinue
710 IF llContinue AND NOT (llInvoiced_AND_AR_Posted)
711 *===TR 1024359 5/23/07 YE
712
713 llContinue = llContinue AND .OpenTable(.cPackageView,,,,,,true) && Open without data
714 IF NOT llContinue
715 lcMsg = "ERROR: Pick ticket " + ALLTRIM(lcPick_Num) + " cannot be processed - unable to open package table."
716
717 *--- Tan35180 SST
718 *UdateLog(.cErrorlog, lcMsg)
719 .oLog.LogEntry(lcMsg)
720 *=== Tan35180 SST
721
722 ENDIF
723 ENDIF
724
725 *--- TAN 35180 - SST
726 * Open SPS view
727 IF llContinue AND .lServerSide
728
729 vnPick_Num = lnPick_Num
730
731 llContinue = llContinue AND .OpenTable(.cSPSView,,true,,,,true) && Open with data
732
733 IF NOT llContinue
734 lcMsg = "ERROR: Pick ticket " + ALLTRIM(lcPick_Num) + " cannot be processed - unable to open SPS Inbound table."
735
736 *--- Tan35180 SST
737 *UdateLog(.cErrorlog, lcMsg)
738 .oLog.LogEntry(lcMsg)
739 *=== Tan35180 SST
740
741 ENDIF
742 ENDIF
743 *=== TAN 35180 - SST
744
745 * TAN 27379 Open cartons view
746
747 *---TR 1024359 5/23/07 YE
748 *IF llContinue
749 IF llContinue AND NOT (llInvoiced_AND_AR_Posted)
750 *===TR 1024359 5/23/07 YE
751
752 IF .lCartonLevel
753 vnCartonFkey = vl_ordsp(tcOrder.Ord_Num, 'Pkey',, tcOrder.Pick_Num, tcOrder.Inv_Num)
754 llContinue = llContinue AND .OpenTable(.cCartonView,,true,,,,true) && Open with data
755 IF NOT llContinue
756 lcMsg = "ERROR: Pick ticket " + ALLTRIM(lcPick_Num) + " cannot be processed - unable to retrieve cartons."
757
758 *--- Tan35180 SST
759 *UdateLog(.cErrorlog, lcMsg)
760 .oLog.LogEntry(lcMsg)
761 *=== Tan35180 SST
762
763 ENDIF
764 ENDIF
765 ENDIF
766
767 IF llContinue
768
769 *--- Tan 35180 SST
770 * v_SqlExec("SELECT * FROM " + pcImport + " WHERE pick_num == '" + lcPick_Num + "'", "tcProcess",, true)
771 v_SqlExec("SELECT * FROM " + pcImport + " WHERE pick_num = " + STR(lnPick_Num), "tcProcess",, true)
772 *=== Tan 35180 SST
773
774 SELECT tcProcess
775 COUNT TO lnCarton
776 SUM weight TO lnWeight
777
778 *---TR 1046908 04/29/2010 YE - Do not round the weight. We are not rounding it for packages view. Why round it for pickticket view?
779 *lnWeight = ROUND(lnWeight, 0) && TAN 29379 To update order header
780 *===TR 1046908 04/29/2010 YE
781
782 lnFrgt_Amt = 0 && Initialize
783
784 lnPkey = v_NextPkey(UPPER("ZZOUPSIN"), lnCarton) && Outside transaction
785 lnPKey = lnPkey - lnCarton + 1 && Starting pKey
786
787 *--- TAN 35180 SST
788
789 *---TR 1024359 5/23/07 YE
790 IF NOT (llInvoiced_AND_AR_Posted)
791 *===TR 1024359 5/23/07 YE
792
793 lcPickTicketView = .cPickTicketView
794
795 *---TR 1024359 5/23/07 YE
796 ENDIF
797 *===TR 1024359 5/23/07 YE
798
799 *=== TAN 35180 SST
800
801 * --- TR 1025728 7/25/07 CM
802 * Check to see whether the user wants to round freight amount or not
803 SELECT tcPksys
804 lcFrgt_Round = tcPksys.frgt_round
805 * === TR 1025728 7/25/07 CM
806
807 * Add new packages records
808 SELECT tcProcess
809 SCAN
810
811 *---TR 1024359 5/23/07 YE
812 IF NOT (llInvoiced_AND_AR_Posted)
813 *===TR 1024359 5/23/07 YE
814
815 *---TAN 35180 SST
816 IF .lServerSide
817
818 * Removed 4th paramter pack_syst - 01/24/03 RRB.
819*!* lcShipper = vl_sps_shpr(&lcPickTicketView..Division, 'shipper',, tcProcess.Location, ;
820*!* tcProcess.Serv_Code, tcProcess.Pack_Syst)
821 lcShipper = vl_sps_shpr(&lcPickTicketView..Division, 'shipper',, tcProcess.Location, ;
822 tcProcess.Serv_Code)
823
824 IF !Empty(lcShipper)
825 REPLACE shipper WITH lcShipper IN (.cPickTicketView)
826 ENDIF
827
828 ELSE
829
830 *--- TR 1011161 07/Jun/2005 SK
831 lcBook_Num = IIF(Book_Num > 0, ALLTRIM(STR(Book_Num)), "")
832 lcPage_Num = IIF(Page_Num > 0, ALLTRIM(STR(Page_Num)), "")
833 lnMani_Num = INT(VAL(lcBook_Num + lcPage_Num))
834
835 IF lnMani_Num > 0
836 REPLACE Mani_Num WITH lnMani_Num IN (.cPickTicketView)
837 ENDIF
838 *=== TR 1011161 07/Jun/2005 SK
839
840 ENDIF
841 *===TAN 35180 SST
842
843 *---TAN 36097 SST
844
845 *--- TR 1002172 5-Dec-2003 VKK Added lcPicklocation and changed the parameter to lcpiclLocation from tcproces.location in vl_upsid
846 lcPickLocation = IIF(.lSingleLocation, &lcPickTicketView..Location, tcProcess.Location)
847
848 IF NOT EMPTY(&lcPickTicketView..Shipper)
849 * Replace tcprocess.location with lcPicklocation
850*!* lcImp_Frgt = vl_upsid(&lcPickTicketView..Division, 'imp_frgt',, tcProcess.Location, ;
851*!* &lcPickTicketView..Shipper)
852 lcImp_Frgt = vl_upsid(&lcPickTicketView..Division, 'imp_frgt',, lcPickLocation, ;
853 &lcPickTicketView..Shipper)
854 *=== TR 1002172 5-Dec-2003 VKK
855
856 ENDIF
857 *===TAN 36097 SST
858 SELECT (.cPackageView)
859 SCATTER MEMO NAME loPackage BLANK
860
861 *--- TR 1072518 31-JUL-13 Venuk.
862 loPackage.charge_1= tcProcess.charge_1
863 loPackage.charge_2= tcProcess.charge_2
864 loPackage.tb_carrier_type = tcProcess.tb_carrier_type
865 loPackage.tb_carton_id = tcProcess.tb_carton_id
866 loPackage.tb_dt_act = tcProcess.tb_dt_act
867 loPackage.tb_manifest_close_id= tcProcess.tb_manifest_close_id
868 loPackage.tb_shipper_id = tcProcess.tb_shipper_id
869 loPackage.bill_wgt = tcProcess.bill_wgt
870 loPackage.ref_num = tcProcess.ref_num
871 loPackage.pack_syst = tcProcess.pack_syst
872 loPackage.serv_code = tcProcess.serv_code
873 loPackage.status = tcProcess.status
874 loPackage.import_ok = tcProcess.import_ok
875 loPackage.zpl_label_file = tcProcess.zpl_label_file
876 *=== TR 1072518 31-JUL-13 Venuk.
877
878 *--- TechRec 1071520 23-Sep-2013 MANI. ---
879 loPackage.retn_track_no = tcProcess.retn_track_no
880 loPackage.retn_track_no_02 = tcProcess.retn_track_no_02
881
882 *--- TechRec 1079799 16-Jul-2014 TSV---
883 loPackage.track_no_02 = tcProcess.track_no_02
884 *=== TechRec 1079799 16-Jul-2014 TSV===
885
886 loPackage.pick_num = lnPick_Num
887
888 *--- TR 1002172 5-Dec-2003 VKK commented the line and added lcpicklocation
889 *loPackage.location = tcProcess.location
890 loPackage.location = lcPickLocation
891
892 loPackage.carton_id = tcProcess.carton_id
893 loPackage.weight = tcProcess.weight
894
895 *--- TR 1011395 02/14/2006 YE - Capture carton number from the inbound file
896 loPackage.carton_num = tcProcess.carton_num
897 *=== TR 1011395 02/14/2006 YE
898
899 *--- TR 1023828 06/19/2007 RCO the system will not be rounding values for proration
900
901 *---TAN 36097 SST
902 *!* loPackage.frgt_amt = 0
903 loPackage.frgt_amt = 0.00
904 *===TAN 36097 SST
905
906 *=== TR 1023828 06/19/2007 RCO the system will not be rounding values for proration
907
908 IF lcImp_Frgt = 'Y' && TAN 36097 SST
909 IF NOT .lProrateFreight && TAN 36488
910
911 * TAN 35968 No need to prorate for carton-level,
912 * already have details - RRB/SST 12/20/02.
913 loPackage.frgt_amt = (tcProcess.charge_1 + ;
914 tcProcess.charge_2)
915 ELSE
916
917 * TAN 35968 Perform proration because flat file data
918 * at pick level is repeated - RRB/SST 12/20/02.
919 * TAN 36645 Package weight should always be rounded up
920 * when prorating.
921
922 * --- TR 1025728 7/25/07 CM
923 * Perform rounding only if flag in zzypksys is 'Y' with ceiling function,
924 * otherwise if flag in 'N' perform normal calculation
925 IF lcFrgt_Round == 'N'
926 IF tcProcess.bill_wgt > 0 && Protect from divide by zero
927 loPackage.frgt_amt = (tcProcess.charge_1 + tcProcess.charge_2) * ;
928 (tcProcess.weight / ;
929 tcProcess.bill_wgt) &&& 1007027 AZ
930 ENDIF
931
932 ELSE
933 IF tcProcess.bill_wgt > 0 && Protect from divide by zero
934 loPackage.frgt_amt = (tcProcess.charge_1 + tcProcess.charge_2) * ;
935 (CEILING(tcProcess.weight) / ;
936 CEILING(tcProcess.bill_wgt)) &&& 1007027 AZ
937 ENDIF
938 ENDIF
939 * === TR 1025728 7/25/07 CM
940
941 *=== TR 1023828 06/19/2007 RCO the system will not be rounding values for proration
942
943 ENDIF
944 ENDIF
945
946 *--- TR 1023828 06/19/2007 RCO the system will not be rounding values for proration
947 *!* loPackage.frgt_amt = RoundIt(loPackage.frgt_amt, "UP")
948 *=== TR 1023828 06/19/2007 RCO the system will not be rounding values for proration
949
950 * --- TR 1025728 7/25/07 CM
951 * If frgt_round from zzypksys is 'Y', then round freight amount
952 IF lcFrgt_Round == 'Y'
953 loPackage.frgt_amt = RoundIt(loPackage.frgt_amt, "UP")
954 ENDIF
955 * === TR 1025728 7/25/07 CM
956
957 loPackage.ups_batch = .nUPSBatch
958 loPackage.pKey = lnPkey
959
960 APPEND BLANK IN (.cPackageView)
961 GATHER MEMO NAME loPackage
962
963 .TimeStampDocument(.cPackageView)
964
965 lnFrgt_Amt = lnFrgt_Amt + loPackage.frgt_amt
966 lnPkey = lnPkey + 1
967 * TAN 27379 Process carton level
968 IF .lCartonLevel
969 SELECT (.cCartonView)
970
971 LOCATE FOR carton_num == tcProcess.carton_num
972 llCartonFound = FOUND()
973 IF llCartonFound
974
975 *--- TechRec 1054422 03-Jun-2011 TShenbagavalli ---
976 lnCtnStageSeq = IIF(!EMPTY(ctn_stage), vl_shpst(ctn_stage, "Sequence"), 0)
977 *=== TechRec 1054422 03-Jun-2011 TShenbagavalli ===
978
979 *--- TR 1012974 22-NOV-2005 VKK
980*!* REPLACE carton_wgt WITH tcProcess.weight, ;
981*!* track_no WITH tcProcess.carton_id ;
982*!* IN (.cCartonView)
983
984 *--- TechRec 1054422 03-Jun-2011 TShenbagavalli ctn_Stage changed to ctn_stage WITH IIF((!EMPTY(.cWareHouseStage) AND lnCtnStageSeq > .nWhseStageSeq), .cWareHouseStage, ctn_Stage)---
985 *--- TR 1060620 04-06-2012 RKI ---*
986*!* REPLACE carton_wgt WITH tcProcess.weight, ;
987*!* frgt_amt WITH loPackage.frgt_amt, ;
988*!* track_no WITH tcProcess.carton_id, ;
989*!* ctn_stage WITH IIF((EMPTY(.cWareHouseStage) OR lnCtnStageSeq > .nWhseStageSeq), ctn_Stage, .cWareHouseStage) ; && TR 1054422 commented and changed IIF(EMPTY(.cWareHouseStage), ctn_stage, .cWareHouseStage)
990*!* IN (.cCartonView)
991
992 *--- TechRec 1071520 24-Sep-2013 MANI. Added retn_track_no ===
993 REPLACE frgt_amt WITH loPackage.frgt_amt, ;
994 track_no WITH tcProcess.carton_id, ;
995 retn_track_no WITH tcProcess.retn_track_no,;
996 retn_track_no_02 WITH tcProcess.retn_track_no_02,;
997 track_no_02 WITH tcProcess.track_no_02,;&&--- TechRec 1079799 16-Jul-2014 TSV===
998 ctn_stage WITH IIF((EMPTY(.cWareHouseStage) OR lnCtnStageSeq > .nWhseStageSeq), ctn_Stage, .cWareHouseStage) ; && TR 1054422 commented and changed IIF(EMPTY(.cWareHouseStage), ctn_stage, .cWareHouseStage)
999 IN (.cCartonView)
1000
1001 IF NOT This.lIgnoreWeight
1002 REPLACE carton_wgt WITH tcProcess.weight IN (.cCartonView)
1003 ENDIF
1004 *=== TR 1060620 04-06-2012 RKI ===*
1005 *=== TR 1012974 22-NOV-2005 VKK
1006
1007 .TimeStampDocument(.cCartonView)
1008 ELSE
1009 lcMsg = "INFO: Could not find carton number " + ALLTRIM(tcProcess.carton_num) + ;
1010 " for pick ticket " + ALLTRIM(lcPick_Num) + "."
1011
1012 *--- Tan35180 SST
1013 *UdateLog(.cErrorlog, lcMsg)
1014 .oLog.LogEntry(lcMsg)
1015 *=== Tan35180 SST
1016
1017 ENDIF
1018
1019 ENDIF
1020
1021 *---TAN 35180 SST
1022 IF .lServerSide
1023 DELETE FOR pKey = tcProcess.pKey IN (.cSPSView) && TR 1065406 01/18/2014 YE - This line was commented out by TR 1064004 for testing purposes but they forgot to uncomment it.
1024 ENDIF
1025 *===TAN 35180 SST
1026
1027 *---TR 1024359 5/23/07 YE - PT is invoiced and posted
1028 ELSE
1029
1030 IF .lServerSide
1031 REPLACE status WITH 'Y' FOR pKey = tcProcess.pKey IN (.cSPSView)
1032 ENDIF
1033
1034 ENDIF
1035 *===TR 1024359 5/23/07 YE
1036
1037 ENDSCAN
1038
1039 * Update Sales Order Record
1040 * lcPickTicketView = .cPickTicketView
1041 *---TR 1024359 5/23/07 YE
1042 IF NOT (llInvoiced_AND_AR_Posted)
1043 *===TR 1024359 5/23/07 YE
1044
1045 llOverwriteOrder = NOT EMPTY(&lcPickTicketView..carton) OR ;
1046 NOT EMPTY(&lcPickTicketView..weight) OR ;
1047 NOT EMPTY(&lcPickTicketView..frgt_amt)
1048
1049 *--- 1001791 11/06/03 AG/AM/RRB
1050 *--- Only update freight on order if flag on customer master is <> Y
1051*!* REPLACE carton WITH lnCarton, ;
1052*!* weight WITH lnWeight, ;
1053*!* frgt_amt WITH lnFrgt_Amt ;
1054*!* IN (.cPickTicketView)
1055
1056 *---TR 1036467 10/17/08 YE - Add to existing values rather than replace.
1057
1058 *---TR 1048200 07/13/2010 YE - Undo TR 1036467 - Always replace rather than add to existing vlaues
1059 *REPLACE carton WITH NVL(carton,0) + lnCarton, ;
1060 weight WITH NVL(weight,0) + lnWeight ;
1061 IN (.cPickTicketView)
1062
1063 *--- TR 1060620 04-06-2012 RKI ---*
1064 IF NOT This.lIgnoreWeight
1065 Replace Weight WITH lnWeight IN (.cPickTicketView)
1066 Endif
1067*!* REPLACE carton WITH lnCarton, ;
1068*!* weight WITH lnWeight ;
1069*!* IN (.cPickTicketView)
1070 REPLACE carton WITH lnCarton ;
1071 IN (.cPickTicketView)
1072 *=== TR 1060620 04-06-2012 RKI ===*
1073
1074 *===TR 1048200 07/13/2010 YE
1075
1076 *===TR 1036467 10/17/08 YE
1077
1078 * --- TR 1017199 RLN 08/17/07
1079 *.lActualFrgt = vl_custr(&lcPickTicketView..customer, "use_calc_frgt") <> "Y"
1080 lcFlag = vl_custr(&lcPickTicketView..customer, "use_calc_frgt")
1081 .lActualFrgt = INLIST(lcFlag, " ", "N")
1082 * === TR 1017199
1083
1084 *---TR 1053337 06/20/11 EB
1085 lcOverFreight=EVALUATE(lcPickTicketView+".over_frgt")
1086 llPreserveFreight=INLIST(lcOverFreight,"P","N")
1087 *===TR 1053337 06/20/11 EB
1088
1089 *---TR 1053337 06/20/11 EB
1090 *IF .lActualFrgt
1091 IF .lActualFrgt and !llPreserveFreight
1092 *===TR 1053337 06/20/11 EB
1093
1094 *---TR 1036467 10/17/08 YE - Add to existing values rather than replace.
1095
1096 *---TR 1048200 07/13/2010 YE - Undo TR 1036467
1097 *REPLACE frgt_amt WITH NVL(frgt_amt,0) + lnFrgt_Amt IN (.cPickTicketView)
1098 REPLACE frgt_amt WITH lnFrgt_Amt IN (.cPickTicketView)
1099 *===TR 1048200 07/13/2010 YE
1100
1101 *===TR 1036467 10/17/08 YE
1102
1103 ENDIF
1104 *=== 1001791 11/06/03 AG
1105
1106 *--- TechRec 1084184 24-Feb-2015 jisingh ---
1107 IF .lActualFrgt
1108 REPLACE eod_actual_frgt WITH lnFrgt_Amt IN (.cPickTicketView)
1109 ENDIF
1110 *=== TechRec 1084184 24-Feb-2015 jisingh ===
1111
1112 *--- TR 1016461/1017374 05/11/2006
1113 * Move this to inside the transaction because we have to test for last carton
1114
1115 *--- TAN 38304 AM
1116*!* IF NOT EMPTY(.cShpstCode)
1117*!* REPLACE shpst_code WITH .cShpstCode IN (.cPickTicketView)
1118*!* ENDIF
1119 *=== TAN 38304 AM
1120
1121 *=== TR 1016461/1017374 05/11/2006
1122
1123 IF tcOrder.Ord_Status = "I" AND tcOrder.Ack_Prn = "R"
1124 REPLACE ack_prn WITH "P" IN (.cPickTicketView)
1125 ENDIF
1126
1127 .TimeStampDocument(.cPickTicketView)
1128
1129 *---TR 1024359 5/23/07 YE
1130 ENDIF
1131 *===TR 1024359 5/23/07 YE
1132
1133 * Begin transaction
1134 llBeganTransaction = .BeginTransaction()
1135
1136 *---TR 1024359 5/23/07 YE
1137 IF NOT (llInvoiced_AND_AR_Posted)
1138 *===TR 1024359 5/23/07 YE
1139
1140 llUpdateSuccess = .TableUpdate(.cPackageView) AND .TableUpdate(.cPickTicketView)
1141 IF .lCartonLevel
1142 llUpdateSuccess = llUpdateSuccess AND .TableUpdate(.cCartonView)
1143 ENDIF
1144
1145 *--- TR 1012974 22-NOV-2005 VKK Insert into auto bill table
1146
1147 *--- TR 1016461/1017374 05/11/2006
1148*!* IF .lInsertAutoBill
1149*!* * If pick level or carton leve and last carton(all cartons stamped with shp stage)
1150*!* IF (NOT .lCartonLevel) OR ;
1151*!* (.lCartonLevel AND LastCartonProcessed(tcOrder.Ord_Num, tcOrder.Pick_Num, tcOrder.Inv_Num, .cWareHouseStage))
1152*!* llUpdateSuccess = llUpdateSuccess AND InsertAutoBill(lnPick_Num, tcOrder.Pkey)
1153*!* ENDIF
1154*!* ENDIF
1155
1156 llProcessPick = (NOT .lCartonLevel) OR (.lCartonLevel AND LastCartonProcessed(tcOrder.Ord_Num, tcOrder.Pick_Num, tcOrder.Inv_Num, .cWareHouseStage))
1157
1158 * If pick level or carton and last carton write to auto-bill
1159 IF tcOrder.Ord_Status = "P" AND .lInsertAutoBill AND llProcessPick
1160 llUpdateSuccess = llUpdateSuccess AND InsertAutoBill(lnPick_Num, tcOrder.Pkey)
1161 ENDIF
1162
1163 * If pick level or carton and last carton stamped pick ticket with stage
1164
1165 *---TR 1051962 01/20/2011 YE - No need to update IF .cShpstCode is Empty AND .lUpdateActShipDate is False
1166 *IF NOT EMPTY(.cShpstCode) AND llProcessPick
1167 IF llProcessPick AND (NOT EMPTY(.cShpstCode) OR .lUpdateActShipDate)
1168 *===TR 1051962 01/20/2011 YE
1169
1170 llUpdateSuccess = llUpdateSuccess AND .StampOrderStage(lnPick_Num, lcPickTicketTable)
1171 ENDIF
1172 *=== TR 1016461/1017374 05/11/2006
1173
1174 *=== TR 1012974 22-NOV-2005 VKK
1175
1176 *---TR 1024359 5/23/07 YE
1177 ENDIF
1178 *===TR 1024359 5/23/07 YE
1179
1180 *--- Tan35180 SST
1181 IF .lServerSide
1182 llUpdateSuccess = llUpdateSuccess AND .TableUpdate(.cSPSView)
1183 ENDIF
1184 *=== Tan35180 SST
1185
1186 *---TR 1024359 5/23/07 YE
1187 IF NOT (llInvoiced_AND_AR_Posted)
1188 *===TR 1024359 5/23/07 YE
1189
1190 *--- TR 1028259 - B.Diependaele 11/19/07 - Replaced property lSerial_Number_Enable to lProcessMasterCartons
1191
1192 *--- TechRec 1018111 04-Sep-2006 aelgendy ---
1193 *IF llUpdateSuccess AND .lSerial_Number_Enable AND .lCartonLevel
1194 IF llUpdateSuccess AND .lProcessMasterCartons AND .lCartonLevel
1195
1196 LOCAL lnSelect_Alias, lcCartonTrackAlias, lcTemp_Update_Carton, lcSQLSelectString
1197 lnSelect_Alias = SELECT(0)
1198 lcCartonTrackAlias = GetUniqueFileName()
1199
1200 SELECT carton_num, carton_id ;
1201 FROM tcProcess ;
1202 GROUP BY carton_num, carton_id ;
1203 INTO CURSOR (lcCartonTrackAlias)
1204
1205 *- Check before update if any data was return from the above code after executed.
1206 IF _TALLY > 0
1207 llUpdateSuccess = THIS.GenerateSqlTempTable(lcCartonTrackAlias) ;
1208 AND THIS.PopulateSqlTempTable(lcCartonTrackAlias)
1209
1210 IF llUpdateSuccess
1211 lcTemp_Update_Carton = THIS.cSQLTempTable
1212
1213 lcSQLSelectString = ;
1214 "UPDATE zzymastd " + ;
1215 "SET track_no = tmpTable.carton_id " + ;
1216 ", user_id = " + SQLFormatChar(goEnv.cUser) + ;
1217 ", last_mod = " + SQLFormatTS(DATETIME()) + ;
1218 " FROM zzymastd " + ;
1219 "JOIN " + lcTemp_Update_Carton + " tmpTable " + ;
1220 "ON tmpTable.carton_num = zzymastd.master_carton"
1221
1222 llUpdateSuccess = v_SQLExec(lcSQLSelectString)
1223
1224 *--- 1020418 11/10/2006 Ilya: Stamp zzoctnph as well
1225 lcSQLSelectString = ;
1226 "UPDATE zzoctnph " + ;
1227 "SET track_no = tmpTable.carton_id " + ;
1228 IIF(EMPTY(.cWareHouseStage), "", ;
1229 ", ctn_stage = CASE WHEN COALESCE(s.sequence,0) > " + SQLFormatNUM(.nWhseStageSeq) + " THEN zzoctnph.ctn_stage ELSE " + SQLFormatChar(.cWarehouseStage) + " END ") + ; && TR 1054422 " + SQLFormatChar(.cWareHouseStage))
1230 ", user_id = " + SQLFormatChar(goEnv.cUser) + ;
1231 ", last_mod = " + SQLFormatTS(DATETIME()) + ;
1232 " FROM zzoctnph " + ;
1233 "JOIN zzymastd ON zzymastd.carton_num = zzoctnph.carton_num " + ;
1234 "JOIN " + lcTemp_Update_Carton + " tmpTable " + ;
1235 " ON tmpTable.carton_num = zzymastd.master_carton" + ;
1236 " LEFT JOIN zzoshpst s ON zzoctnph.ctn_stage = s.shpst_code " && 1054422
1237
1238 llUpdateSuccess = llUpdateSuccess AND v_SQLExec(lcSQLSelectString)
1239 *=== 1020418 11/10/2006 Ilya.
1240
1241 *--- 1022339 2-MAY-07 KISHOR
1242
1243 *---TR 1051962 01/20/2011 YE - No need to update if .cShpstCode is empty AND .lUpdateActShipDate is false
1244 *IF NOT EMPTY(.cShpstCode) AND llProcessPick
1245 IF llProcessPick AND (NOT EMPTY(.cShpstCode) OR .lUpdateActShipDate)
1246 *===TR 1051962 01/20/2011 YE
1247
1248 *--- TechRec 1034271 20-Aug-2008 MA --- added act_ship_date
1249
1250 *---TR 1051962 01/20/2011 YE === Rearrange the SQL
1251
1252 *--- TechRec 1054422 28-Jun-2011 TShenbagavalli ---
1253 *Removed IIF(NOT EMPTY(.cShpstCode), ",shpst_code = " + SQLFormatChar(.cShpstCode),"") + ; from below lcSQLSelectString
1254 * and added the below condition
1255 *IIF(NOT EMPTY(.cShpstCode), ",shpst_code = CASE WHEN COALESCE(s.sequence,0) > " + SQLFormatNUM(.nShpstCodeSeq) + ;
1256 " THEN t.shpst_code ELSE " + SQLFormatChar(.cShpstCode) + " END ","")
1257 *=== TechRec 1054422 28-Jun-2011 TShenbagavalli ===
1258
1259 &&--- TechRec 1054422 28-Jun-2011 TShenbagavalli added " LEFT JOIN zzoshpst s ON " + lcPickTicketTable + ".shpst_code = s.shpst_code===
1260 lcSQLSelectString = ;
1261 "UPDATE " + lcPickTicketTable + ;
1262 " SET user_id = " + SQLFormatChar(goEnv.cUser) + ;
1263 " ,last_mod = " + SQLFormatTS(DATETIME()) + ;
1264 IIF(NOT EMPTY(.cShpstCode), ",shpst_code = CASE WHEN COALESCE(s.sequence,0) > " + SQLFormatNUM(.nShpstCodeSeq) + ;
1265 " THEN " + lcPickTicketTable + ".shpst_code ELSE " + SQLFormatChar(.cShpstCode) + " END ","") + ; && tr 1054422
1266 IIF(.lUpdateActShipDate, ",act_ship_date = " + SQLFormatTS(DATETIME()),"")+;
1267 " FROM " + lcPickTicketTable + ;
1268 " JOIN zzymastd " + ;
1269 " ON zzymastd.pick_num = " + lcPickTicketTable + ".pick_num " + ;
1270 " JOIN " + lcTemp_Update_Carton + " tmpTable " + ;
1271 " ON tmpTable.carton_num = zzymastd.master_carton" + ;
1272 " LEFT JOIN zzoshpst s ON " + lcPickTicketTable + ".shpst_code = s.shpst_code " &&--- TechRec 1054422 28-Jun-2011
1273
1274 llUpdateSuccess = llUpdateSuccess AND v_SQLExec(lcSQLSelectString)
1275 ENDIF
1276 *=== 1022339 2-MAY-07 KISHOR
1277
1278 *--- TechRec 1054422 01-Jun-2011 TShenbagavalli ---
1279 *-- Query Only master cartons
1280 lcSQLSelectString = " SELECT DISTINCT master_carton " + ;
1281 " FROM " + lcTemp_Update_Carton + " t " + ;
1282 " JOIN zzymastd d " + ;
1283 " ON t.carton_num = d.carton_num "
1284
1285 llMastCtn = llUpdateSuccess AND v_SQLExec(lcSQLSelectString, "cursMasterCtn") AND USED("cursMasterCtn")
1286 lcFinalPickList = ""
1287 IF llMastCtn AND RECCOUNT("cursMasterCtn") > 0
1288 SELECT cursMasterCtn
1289 SCAN
1290 lcPickList = ""
1291 llMastCtn = LastMasterCartonProcessed(Master_Carton, .cWareHouseStage, @lcPickList)
1292
1293 IF llMastCtn
1294 lcFinalPickList = lcFinalPickList + lcPickList + ","
1295 ENDIF
1296
1297 ENDSCAN
1298
1299 lcFinalPickList = RemoveLastDelimiter(lcFinalPickList, ",")
1300
1301 IF !EMPTY(lcFinalPickList )
1302 lcSQLSelectString = " UPDATE h SET shpst_code = " + ;
1303 " CASE WHEN COALESCE(s.sequence,0) > " + SQLFormatNUM(.nShpstCodeSeq) + ;
1304 " THEN h.shpst_code ELSE " + SQLFormatChar(.cShpstCode) + " END " + ;
1305 " FROM " + lcPickTicketTable + " h " + ;
1306 " LEFT JOIN zzoshpst s ON h.shpst_code = s.shpst_code " + ;
1307 " WHERE pick_num IN (" + lcFinalPickList + ")"
1308
1309 llUpdateSuccess = llUpdateSuccess AND v_SQLExec(lcSQLSelectString)
1310
1311 IF .lInsertAutoBill
1312 llUpdateSuccess = llUpdateSuccess AND .BulkInsertAutoBill(lcPickTicketTable, lcFinalPickList )
1313 ENDIF
1314
1315 ENDIF
1316
1317 ENDIF
1318 .TableClose("cursMasterCtn")
1319 *=== TechRec 1054422 01-Jun-2011 TShenbagavalli ===
1320
1321 ENDIF
1322 ENDIF
1323
1324 *-- close temp file.
1325 USE IN SELECT(lcCartonTrackAlias)
1326
1327 SELECT(lnSelect_Alias)
1328 ENDIF
1329 *=== TechRec 1018111 04-Sep-2006 aelgendy ===
1330
1331 *---TR 1024359 5/23/07 YE
1332 ENDIF
1333 *===TR 1024359 5/23/07 YE
1334
1335 IF llUpdateSuccess
1336 IF llBeganTransaction
1337 .EndTransaction()
1338 ENDIF
1339
1340 *---TR 1024359 5/23/07 YE
1341 IF NOT (llInvoiced_AND_AR_Posted)
1342 *===TR 1024359 5/23/07 YE
1343
1344 .nPicksUpdated = .nPicksUpdated + 1
1345 .nPicksFailed = .nPicksFailed - 1
1346
1347 *--- TR 1012974 22-NOV-2005 VKK
1348
1349 *--- TR 1016461/1017374 05/11/2006
1350 * If pick level or carton and last carton accumulate
1351 * pick list for next process in batch
1352 * IF tcOrder.Ord_Status <> "I"
1353 IF llProcessPick
1354 *=== TR 1016461/1017374 05/11/2006
1355
1356 .cPickNumListForBatch = .cPickNumListForBatch + ALLTRIM(lcPick_Num) + "|"
1357 ENDIF
1358 *=== TR 1012974 22-NOV-2005 VKK
1359
1360 IF tcOrder.Ord_Status = "I" AND tcOrder.Ack_Prn = "R"
1361 lcMsg = "INFO: Invoice for pick ticket " + ALLTRIM(lcPick_Num) + " has already been printed."
1362
1363 *--- Tan35180 SST
1364 *UdateLog(.cErrorlog, lcMsg)
1365 .oLog.LogEntry(lcMsg)
1366 *=== Tan35180 SST
1367
1368 ENDIF
1369
1370 IF llOverwriteOrder
1371 lcMsg = "INFO: Pick ticket " + ALLTRIM(lcPick_Num) + " was overwritten with new small package data."
1372
1373 *--- Tan35180 SST
1374 *UdateLog(.cErrorlog, lcMsg)
1375 .oLog.LogEntry(lcMsg)
1376 *=== Tan35180 SST
1377
1378 ENDIF
1379
1380 lcMsg = "INFO: Pick ticket " + ALLTRIM(lcPick_Num) + " was processed successfully."
1381
1382 *--- Tan35180 SST
1383 *UdateLog(.cErrorlog, lcMsg)
1384 .oLog.LogEntry(lcMsg)
1385 *=== Tan35180 SST
1386
1387 *---TR 1024359 5/23/07 YE
1388 ENDIF
1389 *===TR 1024359 5/23/07 YE
1390
1391 ELSE
1392 IF llBeganTransaction
1393 .RollbackTransaction()
1394 ENDIF
1395
1396 *---TR 1024359 5/23/07 YE
1397 IF NOT (llInvoiced_AND_AR_Posted)
1398 *===TR 1024359 5/23/07 YE
1399
1400 .TableRevert(.cPackageView)
1401 .TableRevert(.cPickTicketView)
1402 IF .lCartonLevel
1403 .TableRevert(.cCartonView)
1404 ENDIF
1405
1406 lcMsg = "ERROR: Pick ticket " + ALLTRIM(lcPick_Num) + " cannot be processed - update failed."
1407
1408 *--- Tan35180 SST
1409 *UdateLog(.cErrorlog, lcMsg)
1410 .oLog.LogEntry(lcMsg)
1411 *=== Tan35180 SST
1412
1413 *---TR 1024359 5/23/07 YE
1414 ENDIF
1415 *===TR 1024359 5/23/07 YE
1416
1417 ENDIF
1418 ENDIF
1419
1420 ENDSCAN
1421
1422 .AdvanceThermo(RECCOUNT('tcPick'))
1423 INKEY(0.2)
1424
1425 * --- TR 1025728 7/25/07 CM
1426 * Moved code here from ImportUPS(), table needed to be open until we check for rounding
1427 This.TableClose('tcPkSys')
1428 * === TR 1025728 7/25/07 CM
1429
1430 .TableClose("tcProcess")
1431 .TableClose("tcOrder")
1432 .TableClose("tcPick")
1433 ENDWITH
1434
1435 RELEASE vnOrderPkey, vnCartonFkey, vnPick_Num
1436 SELECT (lnSelect)
1437 RETURN true
1438 ENDFUNC
1439
1440*==============================================================
1441
1442 FUNCTION CreateUPSInboundViews
1443 LOCAL llRetVal, lnSelect, lcSQLSelectString, lcMsg
1444 PRIVATE vnOrderPkey, vnCartonFKey, vnPick_Num
1445
1446 lnSelect = SELECT()
1447 llRetVal = true
1448
1449 WITH This
1450 .UpdateThermoCaption("Creating views...")
1451 INKEY(0.2)
1452
1453 IF llRetVal
1454 lcSQLSelectString = "Select * From zzoordrh Where pkey = ?vnOrderPkey"
1455 llRetVal = llRetVal AND .CreateSQLView(.cOrderView, lcSQLSelectString)
1456
1457 IF NOT llRetVal
1458 lcMsg = "ERROR: Cannot create view for order table."
1459
1460 *--- Tan35180 SST
1461 *UdateLog(.cErrorlog, lcMsg)
1462 .oLog.LogEntry(lcMsg)
1463 *=== Tan35180 SST
1464
1465 ENDIF
1466 ENDIF
1467
1468 IF llRetVal
1469 lcSQLSelectString = "Select * From zzoshprh Where pkey = ?vnOrderPkey"
1470 llRetVal = llRetVal AND .CreateSQLView(.cInvoiceView, lcSQLSelectString)
1471
1472 IF NOT llRetVal
1473 lcMsg = "ERROR: Cannot create view for invoice table."
1474
1475 *--- Tan35180 SST
1476 *UdateLog(.cErrorlog, lcMsg)
1477 .oLog.LogEntry(lcMsg)
1478 *=== Tan35180 SST
1479
1480 ENDIF
1481 ENDIF
1482
1483 IF llRetVal
1484 lcSQLSelectString = "Select * From zzoupsin"
1485 llRetVal = llRetVal AND .CreateSQLView(.cPackageView, lcSQLSelectString)
1486
1487 IF NOT llRetVal
1488 lcMsg = "ERROR: Cannot create view for package table."
1489
1490 *--- Tan35180 SST
1491 *UdateLog(.cErrorlog, lcMsg)
1492 .oLog.LogEntry(lcMsg)
1493 *=== Tan35180 SST
1494
1495 ENDIF
1496 ENDIF
1497
1498 IF llRetVal
1499 lcSQLSelectString = "SELECT * " + ;
1500 " FROM zzoctnph " + ;
1501 " WHERE fKey = ?vnCartonFKey " + ;
1502 " AND carton_cls = '" + CARTON_CLASS_SALES + "'"
1503 llRetVal = llRetVal AND .CreateSQLView(.cCartonView, lcSQLSelectString)
1504
1505 IF NOT llRetVal
1506 lcMsg = "ERROR: Cannot create view for carton table."
1507
1508 *--- Tan35180 SST
1509 *UdateLog(.cErrorlog, lcMsg)
1510 .oLog.LogEntry(lcMsg)
1511 *=== Tan35180 SST
1512
1513 ENDIF
1514 ENDIF
1515 ENDWITH
1516
1517 IF llRetVal AND This.lServerSide
1518
1519 *---TAN 37551 - SST - 03/17/03
1520 *lcSQLSelectString = "Select * From zzoupsib Where pick_num = ?vnPick_Num"
1521 lcSQLSelectString = "SELECT * FROM zzoupsib WHERE pick_num = ?vnPick_Num AND import_ok = 'Y' and status not in ('Y','2') " && TR 1065406 01/18/2014 YE - added status
1522 *===TAN 37551 - SST - 03/17/03
1523
1524 llRetVal = llRetVal AND .CreateSQLView(.cSPSView, lcSQLSelectString)
1525
1526 IF NOT llRetVal
1527 lcMsg = "ERROR: Cannot create view for SPS table."
1528
1529 *--- Tan35180 SST
1530 *UdateLog(.cErrorlog, lcMsg)
1531 .oLog.LogEntry(lcMsg)
1532 *=== Tan35180 SST
1533
1534 ENDIF
1535 ENDIF
1536
1537 RELEASE vnOrderPkey, vnCartonFKey, vnPick_Num
1538 SELECT (lnSelect)
1539 RETURN llRetVal
1540 ENDFUNC
1541
1542*================================================
1543
1544 FUNCTION CloseUPSInboundViews
1545 LOCAL lnSelect
1546
1547 lnSelect = SELECT()
1548
1549 WITH This
1550 .TableClose(.cSPSView, true) && TAN 35180
1551 .TableClose(.cCartonView, true)
1552 .TableClose(.cPackageView, true)
1553 .TableClose(.cInvoiceView, true)
1554 .TableClose(.cOrderView, true)
1555 ENDWITH
1556
1557 SELECT (lnSelect)
1558 RETURN true
1559 ENDFUNC
1560
1561*=====================================================================
1562
1563 FUNCTION ProcessUPSInboundFiles
1564 LOCAL lnSelect, lnUPSImportFiles, lnCnt, lcFileName, lcLocation, lcPack_Syst, ;
1565 lcMsg, lcUPSHist
1566
1567 lnSelect = SELECT()
1568
1569 WITH This
1570 lnUPSImportFiles = ALEN(.aUPSImportFiles, 1)
1571
1572 FOR lnCnt = 1 TO lnUPSImportFiles
1573
1574 *--- Tan35180 SST
1575 *UdateLog(.cErrorlog, '') && Spacing for readability
1576 .oLog.LogEntry('')
1577 *=== Tan35180 SST
1578
1579 .cUPSFileName = ALLTRIM(.aUPSImportFiles[lnCnt, 1])
1580
1581 lcFileName = JUSTSTEM(.cUPSFileName)
1582 lcLocation = SUBSTR(lcFileName, 1, AT('_', lcFileName) - 1)
1583 lcLocation = IIF(EMPTY(lcLocation), lcFileName, lcLocation)
1584
1585 *--- TR 1002172 20-JAN-2004 VKK Added IF /ELSE condition SingleLocaiton
1586 IF NOT .lSingleLocation
1587 lcPack_Syst = IIF(OCCURS('_', lcFileName) > 0, SUBSTR(lcFileName, AT('_', lcFileName) + 1), "")
1588 ELSE
1589 lcPack_Syst = SUBSTR(lcFileName, AT('_', lcFileName) + 1)
1590 ENDIF
1591
1592 * TAN 28124 Only import files with extensions defined on Package System Reference
1593 IF .ImportUPS(lcLocation, lcPack_Syst, .cUPSFileName, "tcImport")
1594 .lNoDataFound = false
1595
1596 *--- TR 1002172 5-Dec-2003 VKK Added IF condition SingleLocaiton So validate location only for not singlelocation
1597 IF NOT .lSingleLocation AND NOT vl_locar(lcLocation,,, "W")
1598 lcMsg = "ERROR: File " + .cUPSFileName + " cannot be processed - invalid location."
1599
1600 *--- Tan35180 SST
1601 *UdateLog(.cErrorlog, lcMsg)
1602 .oLog.LogEntry(lcMsg)
1603 *=== Tan35180 SST
1604
1605 ELSE
1606 lcMsg = "INFO: Processing file " + .cUPSFileName + "..."
1607
1608 *--- Tan35180 SST
1609 *UdateLog(.cErrorlog, lcMsg)
1610 .oLog.LogEntry(lcMsg)
1611 *=== Tan35180 SST
1612
1613 *--- TR 1002172 5-Dec-2003 VKK Added Loggin For SingleLocation
1614 .oLog.LogLogical("INFO: Single Location", .lSingleLocation)
1615 *=== TR 1002172 5-Dec-2003 VKK
1616
1617 .oLog.LogLogical("INFO: Carton Level", .lCartonLevel)
1618 .oLog.LogLogical("INFO: Prorate Freight", .lProrateFreight)
1619
1620 *--- TR 1012974 22-NOV-2005 VKK
1621 .oLog.LogLogical("INFO: Auto Bill", .lInsertAutoBill)
1622 .oLog.LogEntry("INFO: Warehouse Stage: " + .cWareHouseStage)
1623 *=== TR 1012974 22-NOV-2005 VKK
1624
1625 .oLog.LogEntry("INFO: Next stage: " + .cShpstCode)
1626 .oLog.LogSkip()
1627
1628 *---TR 1051092 06/12/2010 YE === Move main processing down to AFTER copying file to history
1629 *.UpdatePickTickets("tcImport") && Main Processing
1630
1631 lcUPSHist = .cHistoryDirectory + lcFileName + "." + .cHistoryExtension
1632 IF FILE(lcUPSHist)
1633 lcMsg = "INFO: History file " + lcFileName + "." + .cHistoryExtension + ;
1634 " was overwritten."
1635
1636 *--- Tan35180 SST
1637 *UdateLog(.cErrorlog, lcMsg)
1638 .oLog.LogEntry(lcMsg)
1639 *=== Tan35180 SST
1640
1641 DELETE FILE (lcUPSHist)
1642 ENDIF
1643
1644 *---TR 1071384 05/31/2013 YE - The original file has already been renamed in .ImportUPS(), now moving the renamed file to history.
1645 *RENAME (.cDirectory + .cUPSFileName) TO (lcUPSHist)
1646 RENAME (.cDirectory + lcFileName + "." + .cHistoryExtension) TO (lcUPSHist)
1647 *===TR 1071384 05/31/2013 YE
1648
1649 *--- TechRec 1064004 11-Oct-2012 jjanand ---
1650 *---TR 1051092 06/12/2010 YE - Moved this down here because we want to copy file to history BEFORE processing it
1651 *.UpdatePickTickets("tcImport") && Main Processing
1652
1653 IF .lOptimized
1654 .UpdatePickTickets_setbased(lcLocation, lcPack_Syst,"tcImport")
1655 ELSE
1656 .UpdatePickTickets("tcImport")
1657 ENDIF
1658 *=== TechRec 1064004 11-Oct-2012 jjanand ===
1659 ENDIF
1660 ENDIF
1661
1662 .AdvanceThermoTotal(1)
1663 INKEY(0.2)
1664 ENDFOR
1665 ENDWITH
1666
1667 SELECT (lnSelect)
1668 RETURN true
1669 ENDFUNC
1670
1671*======================================================
1672
1673 *--- Tan35180 SST
1674 FUNCTION CreateFlatFilesFromServer
1675
1676 &&--- TechRec 1072744 02-Aug-2013 TShenbagavalli added lcDelimiter ===
1677 *--- TechRec 1090349 11-Dec-2015 jisingh Added lcCustExist ===
1678 LOCAL llRetVal, lnSelect, lcSQLString, lcFieldList,;
1679 lcFileName, lcFileType, lcPackHead, lcPackExtn, lcPackFile, lcTempFile,;
1680 lcCursorServerSide, lcLocation, lcPack_Syst, lcDelimiter, lcCustExist
1681
1682 LOCAL lcConvField && TR 1072518 31-JUL-13 Venuk.
1683
1684 llRetVal = true
1685 lnSelect = SELECT()
1686
1687 lcFieldList = This.cFieldList
1688 lcPackFile = GetUniqueFileName()
1689 lcTempFile = GetUniqueFileName()
1690 lcCursorServerSide = GetUniqueFileName()
1691 lnCnt = 0
1692
1693 WITH This
1694 * Not DB2 compatible, but .lServerSide only set for Winlet, a SQL client
1695 lcSQLString = "UPDATE zzoupsib " + ;
1696 " SET location = zzoordrh.Location " + ;
1697 " FROM zzoupsib,zzoordrh " + ;
1698 " WHERE zzoupsib.pick_num = zzoordrh.pick_num " + ;
1699 " AND zzoupsib.Location = ' ' " + ;
1700 " AND zzoupsib.pick_num <> 0 "
1701
1702 llRetVal = llRetVal AND v_SQLExec(lcSQLString)
1703
1704 * Not DB2 compatible, but .lServerSide only set for Winlet, a SQL client
1705 lcSQLString = "UPDATE zzoupsib "+;
1706 " SET location = zzoshprh.Location " + ;
1707 " FROM zzoupsib,zzoshprh " + ;
1708 " WHERE zzoupsib.pick_num = zzoshprh.pick_num " + ;
1709 " AND zzoupsib.Location = ' ' " + ;
1710 " AND zzoupsib.pick_num <> 0 "
1711
1712 llRetVal = llRetVal AND v_SQLExec(lcSQLString)
1713
1714 * The following SQL is data massage to prefix the carton number with four zeros
1715 * for Gideon. This is because Pitney Bowles are incapable of sending us a valid
1716 * carton number - TAN 37339 - 02/12/03 RRB.
1717
1718 * Not DB2 compatible, but .lServerSide only set for Winlet, a SQL client
1719 lcSQLString = "UPDATE zzoupsib " + ;
1720 " SET carton_num = '0000' + carton_num " + ;
1721 " WHERE LEN(carton_num) = 16 "
1722
1723 llRetVal = llRetVal AND v_SQLExec(lcSQLString)
1724
1725 *---TAN 37551 - SST - 03/17/03
1726 * Massaging Null data for Import_OK and Ref_num
1727 lcSQLString = "UPDATE zzoupsib " + ;
1728 " SET import_ok = 'Y' " + ;
1729 " WHERE (import_ok IS NULL)"
1730
1731 llRetVal = llRetVal AND v_SQLExec(lcSQLString)
1732
1733 lcSQLString = "UPDATE zzoupsib " + ;
1734 " SET ref_num = '' " + ;
1735 " WHERE (ref_num IS NULL)"
1736
1737 llRetVal = llRetVal AND v_SQLExec(lcSQLString)
1738 *=== TAN 37551 - SST - 03/17/03
1739
1740 *---TR 1065406 01/18/2014 YE - Delete invoiced and posted PT's from zzoupsib
1741 lcSQLString = " DELETE b " + ;
1742 " from zzoupsib b " + ;
1743 " join zzoshprh h " + ;
1744 " on b.location = h.location " + ;
1745 " and b.pick_num = h.pick_num " +;
1746 " where h.ord_status = 'I' and h.ar_post = 'P' "
1747
1748 llRetVal = llRetVal AND v_SQLExec(lcSQLString)
1749 *===TR 1065406 01/18/2014 YE
1750
1751 IF llRetVal
1752
1753 *---TAN 37551 - SST - 03/17/03
1754 *lcSQLString = "SELECT DISTINCT location, pack_syst FROM zzoupsib "+;
1755 * "WHERE location<>' '"
1756 lcSQLString = "SELECT DISTINCT location, pack_syst FROM zzoupsib "+;
1757 "WHERE location > '' AND import_ok = 'Y' and status not in ('Y','2') " && TR 1065406 01/18/2014 YE - added status and changed <> ' ' to > ''
1758 *=== TAN 37551 - SST - 03/17/03
1759
1760 *--- TechRec 1090349 11-Dec-2015 jisingh ---
1761 IF !EMPTY(.cPrmCustomer)
1762 lcCustExist = " AND EXISTS(SELECT 1 FROM " + ;
1763 " (SELECT DISTINCT location, pick_num FROM zzoordrh " + ;
1764 " WHERE customer = " + SQLFormatChar(.cPrmCustomer) + ;
1765 " UNION " + ;
1766 " SELECT DISTINCT location, pick_num FROM zzoshprh " + ;
1767 " WHERE customer = " + SQLFormatChar(.cPrmCustomer) + ;
1768 " ) t " + ;
1769 " WHERE t.location = zzoupsib.location " + ;
1770 " AND t.pick_num = zzoupsib.pick_num) "
1771
1772 lcSQLString = lcSQLString + lcCustExist
1773 ENDIF
1774 *=== TechRec 1090349 11-Dec-2015 jisingh ===
1775
1776 llRetVal = llRetVal AND v_SQLExec(lcSQLString, lcTempFile)
1777
1778 IF llRetVal
1779 SELECT (lcTempFile)
1780 SCAN
1781 lcLocation = &lcTempFile..location
1782 lcPack_Syst = &lcTempFile..pack_syst
1783
1784 *--- TAN 41427 SST 09/24/03
1785 * Replaced charge_2 with zcharge_3 and to get record from view instead.
1786*!* lcSQLString = "SELECT " + lcFieldList + ;
1787*!* " FROM zzoupsib " + ;
1788*!* " WHERE location = " + SQLFormatChar(lcLocation) + ;
1789*!* " AND pack_syst = " + SQLFormatChar(lcPack_Syst) + ;
1790*!* " AND import_ok = 'Y'" && TAN 37551
1791
1792 *--- TR 1072518 31-JUL-13 Venuk.
1793 lcConvField = STRTRAN(lcFieldList,"charge_2","zcharge_3 as charge_2")
1794 lcConvField = STRTRAN(lcConvField ,"zpl_label_file","SUBSTRING(zpl_label_file,1,254) as zpl_label_file")
1795 *=== TR 1072518 31-JUL-13 Venuk.
1796
1797 *--- TR 1072518 31-JUL-13 Venuk. Replaced STRTRAN(lcConvField ,"charge_2","zcharge_3 as charge_2") with lcConvField ---
1798 lcSQLString = "SELECT " + ;
1799 lcConvField + ;
1800 " FROM vvoupsib " + ;
1801 " WHERE location = " + SQLFormatChar(lcLocation) + ;
1802 " AND pack_syst = " + SQLFormatChar(lcPack_Syst) + ;
1803 " AND import_ok = 'Y' and status not in ('Y','2') " && TAN 37551 && TR 1065406 01/18/2014 YE - added status
1804 *=== TAN 41427 SST 09/24/03
1805
1806 *--- TechRec 1090349 11-Dec-2015 jisingh ---
1807 IF !EMPTY(.cPrmCustomer)
1808 lcSQLString = lcSQLString + STRTRAN(lcCustExist, "zzoupsib.", "vvoupsib.")
1809 ENDIF
1810 *=== TechRec 1090349 11-Dec-2015 jisingh ===
1811
1812 llRetVal = llRetVal AND v_SQLExec(lcSQLString, lcCursorServerSide)
1813 llRetVal = llRetVal AND USED(lcCursorServerSide)
1814
1815 * TAN 37551 Resolve to blank package system - RRB.
1816 *--- TR 1060466 05/23/12 JIJO - Commented and added new vl_pksys call with Location parameter ====
1817*!* lcPack_Syst = IIF(NOT vl_pksys(lcPack_Syst), "", lcPack_Syst)
1818*!* llRetVal = llRetVal AND vl_pksys(lcPack_Syst, '', lcPackFile)
1819 lcPack_Syst = IIF(NOT vl_pksys(lcPack_Syst,,,lcLocation), "", lcPack_Syst)
1820 llRetVal = llRetVal AND vl_pksys(lcPack_Syst, '', lcPackFile, lcLocation)
1821 *=== TR 1060466 05/23/12 JIJO ===
1822
1823 IF llRetVal AND RECCOUNT(lcCursorServerSide) > 0
1824 lcPackHead = &lcPackFile..hdr_ib
1825 lcPackExtn = &lcPackFile..ext_ib
1826 lcFileType = IIF(lcPackHead = 'Y', UPS_FILE_TYPE_CSV, UPS_FILE_TYPE_DELIMITED)
1827
1828 *--- TechRec 1072744 02-Aug-2013 TShenbagavalli ---
1829 lcDelimiter = ALLTRIM(&lcPackFile..ib_delimiter)
1830 *=== TechRec 1072744 02-Aug-2013 TShenbagavalli ===
1831
1832 lcFileName = ADDBS(.cDirectory) + ALLTRIM(lcLocation) + ;
1833 IIF(NOT EMPTY(lcPack_Syst), "_" + ALLTRIM(lcPack_Syst), "") + ;
1834 "." + ALLTRIM(lcPackExtn)
1835
1836 SELECT (lcCursorServerSide)
1837
1838 *--- TR 1031666 RCO 03/20/08 If file already exists in specified directory erase before creation
1839 IF FILE(lcFileName)
1840 ERASE(lcFileName)
1841 ENDIF
1842 *=== TR 1031666
1843
1844 *--- TechRec 1072744 02-Aug-2013 TShenbagavalli ---
1845 *COPY TO (lcFileName) FIELDS &lcFieldList TYPE &lcFileType
1846 IF lcDelimiter = UPS_DEFAULT_DELIMITER
1847 COPY TO (lcFileName) FIELDS &lcFieldList TYPE &lcFileType
1848 ELSE
1849 lcDelimiter = "'" + ALLTRIM(lcDelimiter) + "'"
1850 COPY TO (lcFileName) FIELDS &lcFieldList DELIMITED WITH CHARACTER &lcDelimiter
1851 ENDIF
1852 *=== TechRec 1072744 02-Aug-2013 TShenbagavalli ===
1853
1854 ENDIF
1855
1856 IF NOT llRetVal
1857 EXIT && Exit SCAN
1858 ENDIF
1859 ENDSCAN
1860 ENDIF
1861 ENDIF
1862
1863 .TableClose(lcCursorServerSide)
1864 .Tableclose(lcPackFile)
1865 .Tableclose(lcTempFile)
1866 ENDWITH
1867
1868 SELECT (lnSelect)
1869 RETURN llRetVal
1870 ENDFUNC
1871 *=== Tan35180 SST
1872
1873*============================================================
1874
1875 *--- TR 1016461/1017374 05/11/2006
1876 FUNCTION StampOrderStage
1877 LPARAMETERS tnPick_Num, tcTargetTable
1878 LOCAL llRetVal, lnSelect, lcSQLString
1879
1880 llRetVal = true
1881 lnSelect = SELECT()
1882
1883 WITH This
1884
1885 *--- TechRec 1034271 20-Aug-2008 MA --- added act_ship_date
1886 *---TR 1051962 01/20/2011 YE === Add user_id, last_mod, and condition for .cShpstCode to the Update
1887 *lcSQLString = "UPDATE " + tcTargetTable + ;
1888 " SET shpst_code = " + SQLFormatChar(.cShpstCode) + ;
1889 IIF(.lUpdateActShipDate,",act_ship_date = " + SQLFormatTS(DATETIME()),"")+;
1890 " WHERE pick_num = " + SQLFormatNum(tnPick_Num)
1891
1892 *--- TechRec 1054422 03-Jun-2011 TShenbagavalli ---
1893*!* lcSQLString = "UPDATE " + tcTargetTable + ;
1894*!* " SET user_id = " + SQLFormatChar(goEnv.cUser) + ;
1895*!* " ,last_mod = " + SQLFormatTS(DATETIME()) + ;
1896*!* IIF(NOT EMPTY(.cShpstCode), ",shpst_code = " + SQLFormatChar(.cShpstCode),"") + ;
1897*!* IIF(.lUpdateActShipDate, ",act_ship_date = " + SQLFormatTS(DATETIME()),"")+;
1898*!* " WHERE pick_num = " + SQLFormatNum(tnPick_Num)
1899
1900 lcSQLString = "UPDATE t " + + ;
1901 " SET user_id = " + SQLFormatChar(goEnv.cUser) + ;
1902 " ,last_mod = " + SQLFormatTS(DATETIME()) + ;
1903 IIF(NOT EMPTY(.cShpstCode), ",shpst_code = CASE WHEN COALESCE(s.sequence,0) > " + SQLFormatNUM(.nShpstCodeSeq) + ;
1904 " THEN t.shpst_code ELSE " + SQLFormatChar(.cShpstCode) + " END ","") + ;
1905 IIF(.lUpdateActShipDate, ",act_ship_date = " + SQLFormatTS(DATETIME()),"")+;
1906 " FROM " + tcTargetTable + " t " + ;
1907 " LEFT JOIN zzoshpst s ON t.shpst_code = s.shpst_code " + ;
1908 " WHERE pick_num = " + SQLFormatNum(tnPick_Num)
1909 *=== TechRec 1054422 03-Jun-2011 TShenbagavalli ===
1910
1911 *===TR 1051962 01/20/2011 YE
1912
1913 llRetVal = llRetVal AND v_SQLExec(lcSQLString)
1914 ENDWITH
1915
1916 SELECT (lnSelect)
1917 RETURN llRetVal
1918 ENDFUNC
1919 *=== TR 1016461/1017374 05/11/2006
1920
1921*=========================================
1922
1923 *---TR 1024359 5/23/07 YE
1924 FUNCTION SetSPSInboundStatusFlag
1925 LPARAMETERS tnPick_Num
1926 LOCAL llRetVal, lnSelect, lcSQLString, lcStatus
1927
1928 llRetVal = true
1929 lnSelect = SELECT()
1930
1931 lcStatus = 'Y'
1932
1933 WITH This
1934 lcSQLString = " UPDATE ib " + ;
1935 " SET status = " + SQLFormatChar(lcStatus) + ;
1936 " FROM zzoupsib ib JOIN zzoshprh sh WITH (NOLOCK) " + ;
1937 " ON ib.pick_num = sh.pick_num AND ib.location = sh.location " + ;
1938 " WHERE sh.ord_status = 'I' AND sh.ar_post = 'P' "
1939
1940 llRetVal = llRetVal AND v_SQLExec(lcSQLString)
1941 ENDWITH
1942
1943 SELECT (lnSelect)
1944 RETURN llRetVal
1945 ENDFUNC
1946 *===TR 1024359 5/23/07 YE
1947
1948*============================================================
1949
1950*--- TechRec 1054422 07-Jun-2011 TShenbagavalli ---
1951 FUNCTION BulkInsertAutoBill
1952 LPARAMETERS tcPickTable, tcPickList
1953 LOCAL llRetVal, lnSelect
1954
1955 llRetVal = true
1956 lnSelect = SELECT()
1957
1958 WITH This
1959
1960 WITH .oSB
1961 .SB_Reset()
1962 .cSB_Target = "zzyabill"
1963 .cSB_Source = tcPickTable
1964 .cSB_SourceAlias = "t"
1965
1966 .cSB_WhereString = " t.pick_num in ( " + tcPickList + " ) " + ;
1967 " AND t.ord_Status = 'P' " + ;
1968 " AND not exists(select 1 from zzyabill where pick_num = t.pick_num) "
1969
1970 .SB_SetLiteralFieldValue("pick_num","t.pick_num")
1971 .SB_SetLiteralFieldValue("pick_pkey","t.pkey")
1972
1973 .SB_KeySetAddKeySpec("pkey","Int","t.pick_num")
1974 llRetVal = .SB_GenerateSQLInsertString()
1975
1976 IF llRetVal
1977 llRetVal = llRetVal AND v_SQLExec(.cSQLString)
1978 ENDIF
1979
1980 ENDWITH
1981
1982 ENDWITH
1983
1984 SELECT (lnSelect)
1985 RETURN llRetVal
1986 ENDFUNC
1987*=== TechRec 1054422 07-Jun-2011 TShenbagavalli ===
1988
1989*============================================================
1990
1991*--- TechRec 1064004 11-Oct-2012 jjanand ---
1992 FUNCTION UpdatePickTickets_Setbased
1993 LPARAMETERS pcLocation, pcPack_Syst, pcImport
1994
1995 LOCAL llRetVal, lnSelect, lcCursor, llContinue
1996
1997 llRetVal = true
1998 lnSelect = SELECT()
1999
2000 WITH THIS
2001
2002 .InitThermo(RECCOUNT(pcImport))
2003 .UpdateThermoCaption("Processing file " + .cUPSFileName + "...")
2004
2005 INKEY(0.2)
2006
2007 llRetVal = llRetVal and .CreatePickTempTables(pcImport) && here we create .cImportTemp(from the flat file) and .PickLstTemp (distinct picks)
2008
2009 llRetVal = llRetVal and .ValidateImportData()
2010
2011 *--- TechRec 1079321 18-Jun-2014 GSternik ---
2012 If llRetVal
2013
2014 Local lnStartPick, lnEndPick, lnLastPick
2015
2016 Do While .T.
2017 If .nChunkSize > 0
2018 If Empty(.cPickLstAll)
2019 .cPickLstAll = .cPickLstTemp
2020 *-- Use SPID to ensure the unique global temp table name:
2021 Local lcSPID
2022 If SQLExec(g_nHandle, "select @@SPID as SPID", "ConInfo") > 0
2023 lcSPID = Transform(ConInfo.SPID)
2024 Else
2025 lcSPID = SubStr(SYS(2015), 5)
2026 EndIf
2027 Use in Select("ConInfo")
2028 .cPickLstTemp = "##SPS_PK_" + lcSPID
2029
2030 *-- Drop it, just in case:
2031 =SQLExec(g_nHandle, "begin try drop table " + .cPickLstTemp + " end try begin catch end catch")
2032 v_SQLExec("select top 1 * into " + .cPickLstTemp ;
2033 + " from " + .cPickLstAll + " where 1=2")
2034
2035 *-- Let's get the chunks:
2036 v_SqlExec("select Max(Pick_num) as Pick_Num from " + .cPickLstAll, "tcChunks")
2037 lnLastPick = tcChunks.Pick_Num
2038 v_SqlExec(;
2039 "select * from ("+;
2040 "select Pick_num"+;
2041 " , Row_Number() over (order by Pick_Num) as RN"+;
2042 " from " + .cPickLstAll + ") t"+;
2043 " where RN%" + Transform(.nChunkSize) + " in (0, 1)", "tcChunks")
2044 llBreak = .F. && RecCount("tcChunks") < 1
2045 Go top in tcChunks
2046 EndIf
2047
2048 lnStartPick = tcChunks.Pick_Num
2049 Skip in tcChunks
2050 If Eof("tcChunks")
2051 *-- this happens for the last chunk, except when we have no reminder.
2052 lnEndPick = lnLastPick
2053 llBreak = .T.
2054 Else
2055 lnEndPick = tcChunks.Pick_Num
2056 Skip in tcChunks
2057 if Eof()
2058 llBreak = .T.
2059 Endif
2060 EndIf
2061
2062 .oLog.LogSkip()
2063 .oLog.LogEntry("Processing the Pick Tickets from " + Transform(lnStartPick);
2064 + " to " + Transform(lnEndPick) + ".")
2065 v_SqlExec (;
2066 "truncate table " + .cPickLstTemp + "; " +;
2067 "insert into " + .cPickLstTemp + " select * from " + .cPickLstAll ;
2068 + " where Pick_num between " + Transform(lnStartPick) + " and " + Transform(lnEndPick))
2069 EndIf && Chunking
2070 *=== TechRec 1079321 18-Jun-2014 GSternik ===
2071
2072 llBeganTransaction = llRetVal and .BeginTransaction()
2073
2074 llRetVal = llRetVal and llBeganTransaction
2075
2076 llRetVal = llRetVal and .ProcessPackage(pcLocation, pcPack_Syst)
2077
2078 llRetVal = llRetVal and .ProcessOrder()
2079
2080 llRetVal = llRetVal and .ProcessCartons(pcLocation, pcPack_Syst)
2081
2082 llRetVal = llRetVal and .ProcessSps()
2083
2084 llRetVal = llRetVal and .ProcessAutoBill(pcLocation, pcPack_Syst)
2085
2086 llRetVal = llRetVal and .ProcessMasterCartons(pcLocation, pcPack_Syst)
2087
2088 IF llRetVal
2089 IF llBeganTransaction
2090 .EndTransaction()
2091 ENDIF
2092 ELSE
2093 IF llBeganTransaction
2094 .RollbackTransaction()
2095 ENDIF
2096 ENDIF
2097
2098 llRetVal = .PopulateUpdateLogs(llRetVal)
2099
2100 *--- TechRec 1079321 18-Jun-2014 GSternik ---
2101
2102 If .nChunkSize = 0 or !Used("tcChunks")
2103 exit
2104 EndIf
2105
2106 If llBreak
2107 *-- Cleanup
2108 =SQLExec(g_nHandle, "begin try drop table " + .cPickLstTemp + " end try begin catch end catch")
2109 .cPickLstTemp = .cPickLstAll
2110 .cPickLstAll = ""
2111
2112 use in Select("tcChunks")
2113 exit
2114 EndIf
2115
2116 EndDo
2117 EndIf && llRetVal
2118 *=== TechRec 1079321 18-Jun-2014 GSternik ===
2119
2120 &&.TableClose("tcPick") &&TR 1076282
2121 &&.TableClose("tcProcessPick") &&TR 1076282
2122 ENDWITH
2123
2124 SELECT (lnSelect)
2125 RETURN llRetVal
2126 ENDFUNC
2127*============================================================
2128
2129 FUNCTION PopulateUpdateLogs
2130 LPARAMETERS plRetVal
2131 LOCAL llRetVal, lnSelect
2132
2133 llRetVal = plRetVal
2134 lnSelect = SELECT()
2135
2136 WITH This
2137
2138 IF llRetVal
2139
2140 lcUpdatedPickLst = "##TT" + GetUniqueFileName()
2141
2142 lcSql = " select distinct pick_num, OverWrite, InvPrn " + ;
2143 " into " + lcUpdatedPickLst + ;
2144 " from " + .cPickLstTemp
2145
2146 llRetVal = llRetVal and V_Sqlexec(lcSql)
2147
2148 lcSql = " update b "+;
2149 " set OverWrite = case when p.carton > 0 or p.weight > 0 or p.frgt_amt > 0 then 'Y' else 'N' end "+;
2150 " , InvPrn = case when p.Ord_Status = 'I' and p.Ack_Prn = 'R' then 'Y' else 'N' end "+;
2151 " from " + lcUpdatedPickLst + " b " + ;
2152 " join zzoordrh p " + ;
2153 " on p.pick_num = b.pick_num "
2154
2155 llRetVal = llRetVal and V_Sqlexec(lcSql)
2156 lcSql = STRTRAN(lcSql,'zzoordrh','zzoshprh')
2157 llRetVal = llRetVal and V_Sqlexec(lcSql)
2158
2159
2160 lcSql = " select pick_num, OverWrite, InvPrn from " + lcUpdatedPickLst
2161
2162 IF v_SqlExec(lcSql, 'tcPick') AND USED('tcPick') AND RECCOUNT('tcPick') > 0
2163
2164 .nPicksUpdated = .nPicksUpdated + RECCOUNT('tcPick')
2165 .nPicksFailed = .nPicksFailed - RECCOUNT('tcPick')
2166
2167 ENDIF
2168
2169 lcSql = " select pick_num from " + .clastCtnMod + " where ProcessPick = 'Y' "
2170 llCheck = v_SqlExec(lcSql, 'tcProcessPick')
2171
2172 IF llCheck AND USED('tcProcessPick') AND RECCOUNT('tcProcessPick') > 0
2173 SELECT tcProcessPick
2174
2175 SCAN
2176 lcPick_Num = LTRIM(STR(tcProcessPick.pick_num))
2177 .cPickNumListForBatch = .cPickNumListForBatch + ALLTRIM(lcPick_Num) + "|"
2178 ENDSCAN
2179 ENDIF
2180 ENDIF
2181
2182 IF USED('tcPick') AND RECCOUNT('tcPick') > 0
2183 SELECT tcPick
2184
2185 SCAN
2186 lcPick_Num = LTRIM(STR(tcPick.pick_num))
2187
2188 IF llRetVal
2189
2190 llOverwriteOrder = ALLTRIM(tcPick.OverWrite) = 'Y'
2191 llInvoiceAckPrn = ALLTRIM(tcPick.InvPrn) = 'Y'
2192
2193 IF llOverwriteOrder
2194 lcMsg = "INFO: Pick ticket " + lcPick_Num + " was overwritten with new small package data."
2195 .oLog.LogEntry(lcMsg)
2196 ENDIF
2197
2198 IF llInvoiceAckPrn
2199 lcMsg = "INFO: Invoice for pick ticket " + lcPick_Num + " has already been printed."
2200 .oLog.LogEntry(lcMsg)
2201 ENDIF
2202
2203 ENDIF
2204
2205 lcMsg = IIF(llRetVal, "INFO: ", "ERROR: ")+ " Pick ticket " + lcPick_Num + ;
2206 IIF(llRetVal," was processed successfully."," cannot be processed - update failed.")
2207 .oLog.LogEntry(lcMsg)
2208 ENDSCAN
2209 ENDIF
2210
2211 .TableClose("tcPick") && TR 1076282
2212 .TableClose("tcProcessPick") && TR 1076282
2213
2214 ENDWITH
2215
2216 SELECT (lnSelect)
2217 RETURN llRetVal
2218 ENDFUNC
2219
2220*============================================================
2221
2222 FUNCTION CreatePickTempTables
2223 LPARAMETERS pcImport
2224 LOCAL llRetVal, lnSelect
2225
2226 llRetVal = true
2227 lnSelect = SELECT()
2228
2229 WITH This
2230
2231 llRetVal = THIS.GenerateSqlTempTable(pcImport) AND THIS.PopulateSqlTempTable(pcImport)
2232 .cImportTemp = .cSQLTempTable
2233
2234 *---TR 1076282 2/25/2014 YE
2235 lcIndex1 = " create index #" + getuniquefilename() + " on " + .cImportTemp + "(pick_num)"
2236 lcIndex2 = " create index #" + getuniquefilename() + " on " + .cImportTemp + "(carton_num)"
2237 lcindex3 = " create index #" + getuniquefilename() + " on " + .cImportTemp + "(location,pick_num)"
2238 llRetVal = llRetVal AND v_sqlexec(lcindex1 + ";" + lcindex2 + ";" + lcindex3)
2239 *===TR 1076282 2/25/2014 YE
2240
2241 lcSql = ;
2242 "select distinct pick_num"+;
2243 " , location"+;
2244 " , '' as errs_flg"+;
2245 " , SPACE(250) as errs_msg"+;
2246 " , 'N' as OverWrite"+;
2247 " , 'N' as InvPrn"+;
2248 " from " + .cImportTemp + ;
2249 " order by pick_num "
2250
2251 .cPickLstTemp = SqlTableFromQuery(lcSql)
2252
2253 *---TR 1076282 2/25/2014 YE
2254 lcIndex1 = " create index #" + getuniquefilename() + " on " + .cPickLstTemp + "(pick_num)"
2255 lcIndex2 = " create index #" + getuniquefilename() + " on " + .cPickLstTemp + "(location,pick_num)"
2256 lcIndex3 = " create index #" + getuniquefilename() + " on " + .cPickLstTemp + "(errs_flg)"
2257 llRetVal = llRetVal AND v_sqlexec(lcIndex1 + ";" + lcIndex2 + ";" + lcIndex3)
2258 *===TR 1076282 2/25/2014 YE
2259
2260 lcSql = " select distinct pick_num from " + .cPickLstTemp
2261 llRetVal = v_SqlExec(lcSql, 'tcPick') AND USED('tcPick') AND RECCOUNT('tcPick') > 0
2262
2263 IF llRetVal
2264 .nTotalPicks = .nTotalPicks + RECCOUNT('tcPick')
2265 .nPicksFailed = .nPicksFailed + RECCOUNT('tcPick')
2266 ENDIF
2267
2268 ENDWITH
2269
2270 SELECT (lnSelect)
2271 RETURN llRetVal
2272 ENDFUNC
2273
2274*============================================================
2275
2276 FUNCTION ValidateImportData
2277 LOCAL llRetVal, lnSelect, lcSql
2278
2279 llRetVal = true
2280 lnSelect = SELECT()
2281
2282 WITH This
2283
2284 lcSql = "update b " + ;
2285 " set Errs_Flg = 'Y' " + ;
2286 " , Errs_msg = 'ERROR: Pick ticket ' + ltrim(STR(b.Pick_Num)) + ' cannot be processed - pick number not found.' " + ;
2287 " from " + .cPickLstTemp + " b " + ;
2288 " where b.pick_num <= 0 " + ;
2289 " or (not exists( " + ;
2290 " select h.pkey " + ;
2291 " from zzoordrh h with (nolock) " + ; && TR 1076282 with (nolock)
2292 " where h.pick_num = b.pick_num " + ;
2293 " and h.location = case " + ;
2294 " when b.location > '' then b.location " + ;
2295 " else h.location end) " + ;
2296 " and not exists( " + ;
2297 " select h.pkey " + ;
2298 " from zzoshprh h with (nolock) " + ; && TR 1076282 with (nolock)
2299 " where h.pick_num = b.pick_num " + ;
2300 " and h.location = case " + ;
2301 " when b.location > '' then b.location " + ;
2302 " else h.location " + ;
2303 " end )) "
2304
2305 llRetVal = llRetVal and V_Sqlexec(lcSql)
2306
2307 lcSql = " update b "+;
2308 " set Errs_Flg = 'Y'"+;
2309 " , Errs_msg = 'ERROR: Pick ticket ' + ltrim(STR(b.Pick_Num)) + ' cannot be processed - invoice was posted to A/R.' "+;
2310 " from " + .cPickLstTemp + " b " + ;
2311 " where exists( "+;
2312 "select h.pkey "+;
2313 " from zzoshprh h with (nolock) "+; && TR 1076282 with (nolock)
2314 " where h.pick_num = b.pick_num "+;
2315 " and h.ord_status = 'I' "+;
2316 " and h.ar_post = 'P') "
2317
2318 llRetVal = llRetVal and V_Sqlexec(lcSql)
2319
2320 IF .lServerSide
2321
2322 lcSql = " update h "+;
2323 " set status = 'Y' "+;
2324 " from zzoupsib h " +;
2325 " join " + .cPickLstTemp + " b " + ;
2326 " on b.pick_num = h.pick_num " + ;
2327 " where h.import_ok = 'Y' and exists( "+;
2328 " select h.pkey "+;
2329 " from zzoshprh h with (nolock) "+; && TR 1076282 with (nolock)
2330 " where h.pick_num = b.pick_num "+;
2331 " and h.ord_status = 'I' "+;
2332 " and h.ar_post = 'P') "
2333
2334 llRetVal = llRetVal and V_Sqlexec(lcSql)
2335
2336 ENDIF
2337
2338 lcSql = " select * from " + .cPickLstTemp + " where Errs_Flg = 'Y' "
2339
2340 llRetVal = llRetVal and V_Sqlexec(lcSql,'tcErrList')
2341
2342 IF llRetVal AND USED('tcErrList') AND RECCOUNT('tcErrList') > 0
2343 SELECT tcErrList
2344
2345 SCAN
2346 lcMsg = tcErrList.Errs_msg
2347 .oLog.LogEntry(lcMsg)
2348 ENDSCAN
2349 ENDIF
2350
2351 lcSql = " delete from " + .cPickLstTemp + " where Errs_Flg = 'Y' "
2352
2353 llRetVal = llRetVal and V_Sqlexec(lcSql)
2354
2355 lcSql = " select COUNT(pick_num) As PickCnt from " + .cPickLstTemp + " where Errs_Flg < 'Y' "
2356
2357 llRetVal = llRetVal and V_SqlExec(lcSql,'tcCntLst') and tcCntLst.PickCnt > 0
2358
2359 .TableClose("tcErrList")
2360
2361 .TableClose("tcCntLst") && TR 1076282
2362
2363 ENDWITH
2364
2365 SELECT (lnSelect)
2366 RETURN llRetVal
2367 ENDFUNC
2368
2369*============================================================
2370
2371 FUNCTION ProcessOrder
2372 LOCAL llRetVal, lnSelect, lcFrgt_Round
2373
2374 llRetVal = true
2375 lnSelect = SELECT()
2376
2377 WITH This
2378
2379 lcSql = " update p " + ;
2380 " SET user_id = " + SQLformatChar(goEnv.cUser) + ;
2381 IIF(.lIgnoreWeight, "", + ;
2382 " , weight = imp_pck.weight") + ;
2383 " , last_mod = " + SQLFormatTS(DATETIME()) + ;
2384 " , Carton = imp_pck.Carton_count "+ ;
2385 " , frgt_amt = CASE WHEN cust.use_calc_frgt IN ('','N') AND p.over_frgt NOT IN ('P','N') THEN imp_pck.frgt_amt ELSE p.frgt_amt END " + ;
2386 " , eod_actual_frgt = CASE WHEN cust.use_calc_frgt IN ('','N') THEN imp_pck.frgt_amt ELSE p.frgt_amt END " + ; &&--- TechRec 1084184 25-Feb-2015 jisingh ===
2387 " , ack_prn = CASE WHEN p.Ord_Status = 'I' AND p.Ack_Prn = 'R' THEN 'P' ELSE p.Ack_Prn END " + ;
2388 " from zzoordrh p " + ;
2389 " join " + .cImportTemp + " i " + ;
2390 " on i.pick_num = p.pick_num " + ;
2391 " join " + .cPickLstTemp +" b " + ;
2392 " on b.pick_num = i.pick_num " + ;
2393 " and b.location = i.location " + ;
2394 " join (select pick_num, COUNT(*) as carton_count, " + ;
2395 " SUM(weight) as weight, SUM(frgt_Amt) as frgt_Amt from zzoupsin with (nolock) " + ; && TR 1076282 with (nolock)
2396 " group by pick_num) imp_pck " + ;
2397 " on imp_pck.pick_num = p.pick_num " + ;
2398 " left join zzoupsid d " + ;
2399 " on d.pkey =( " + ;
2400 " select TOP 1 pkey from zzoupsid with (nolock) " + ; && TR 1076282 with (nolock)
2401 " where division = p.division " + ;
2402 " and (location = i.location or location = '') " + ;
2403 " and serv_code = i.serv_code " + ;
2404 " order by location desc) " +;
2405 " left join zzxcustr cust with (nolock) " + ; && TR 1076282 with (nolock)
2406 " on cust.customer = p.customer "
2407
2408 llRetVal = llRetVal and V_Sqlexec(lcSql)
2409
2410 * Preparing query to update invoice table
2411 lcSql = STRTRAN(lcSql,'zzoordrh','zzoshprh')
2412
2413 lcSQL = lcSQL + " WHERE p.ar_post <> 'P' "
2414
2415 llRetVal = llRetVal and V_Sqlexec(lcSql)
2416
2417 ENDWITH
2418
2419 SELECT (lnSelect)
2420 RETURN llRetVal
2421 ENDFUNC
2422*============================================================
2423
2424 FUNCTION ProcessPackage
2425 PARAMETERS pcLocation, pcPack_Syst
2426 LOCAL llRetVal, lnSelect, lcPickLocation, lnUpsBatch, lcSql, lcUPSinTemp, lcFrgtAmt, lnRecCount, lnNewRecPkey
2427
2428 llRetVal = true
2429 lnSelect = SELECT()
2430
2431 WITH This
2432
2433 lcUpdate = ""
2434
2435 IF .lServerSide
2436 lcUpdate = " set shipper = CASE WHEN COALESCE(d.shipper,'') <> '' THEN d.shipper ELSE p.shipper END "
2437 ELSE
2438 lcUpdate = " set mani_num = COALESCE(cast((case when book_num > 0 then str(book_num,len(book_num)) else '' end + "+ ;
2439 " case when page_num > 0 then str(page_num,len(page_num)) else '' end) as int),0)"
2440 ENDIF
2441
2442 IF !EMPTY(lcUpdate)
2443
2444 lcSql = " update p " + ;
2445 lcUpdate + ;
2446 " from zzoordrh p " + ;
2447 " join " + .cImportTemp + " i " + ;
2448 " on i.pick_num = p.pick_num " + ;
2449 " join " + .cPickLstTemp + " b " + ;
2450 " on b.pick_num = i.pick_num " + ;
2451 " and b.location = i.location " + ;
2452 " left join zzoupsid d with (nolock) " + ; && TR 1076282 with (nolock)
2453 " on d.pkey =( " + ;
2454 " select TOP 1 pkey from zzoupsid with (nolock) " + ; && TR 1076282 with (nolock)
2455 " where division = p.division " + ;
2456 " and (location = i.location or location = '') " + ;
2457 " and serv_code = i.serv_code " + ;
2458 " order by location desc) "
2459
2460 llRetVal = llRetVal and V_Sqlexec(lcSql)
2461
2462 * Preparing query to update invoice table
2463 lcSql = STRTRAN(lcSql,'zzoordrh','zzoshprh')
2464
2465 lcSQL = lcSQL + " WHERE p.ar_post <> 'P' "
2466
2467 llRetVal = llRetVal and V_Sqlexec(lcSql)
2468
2469 ENDIF
2470
2471 lcPickLocation = IIF(.lSingleLocation, "p.Location", "i.Location")
2472
2473 lnUpsBatch = .nUPSBatch
2474
2475 &&--- TechRec 1071520 23-Sep-2013 MANI. Added retn_track_no ===
2476 lcSql = "select pick_num, location, carton_id, weight, carton_num, Frgt_Amt, Ups_Batch, last_mod, user_id,'N' as frgt_round " + ;
2477 ",tb_carrier_type, tb_carton_id, tb_dt_act, tb_manifest_close_id, tb_shipper_id " + ; && TR 1072518 31-JUL-13 Venuk.
2478 ",bill_wgt, charge_1, charge_2, ref_num, pack_syst, serv_code, status, import_ok, zpl_label_file , retn_track_no , retn_track_no_02 " + ; && TR 1072518 31-JUL-13 Venuk.
2479 ",track_no_02 " + ; &&--- TechRec 1079799 16-Jul-2014 TSV===
2480 " from zzoupsin where 1=2"
2481
2482 lcUPSinTemp = SqlTableFromQuery(lcSql)
2483
2484 lcSQL = "alter table " + lcUPSinTemp + " add pkey2 int identity(1,1) "
2485
2486 llRetVal = llRetVal and V_Sqlexec(lcSQL)
2487
2488 lcFrgtAmt = " case when s.imp_frgt = 'Y' then case " + ;
2489 " when " + SQLformatChar(tcPkSys.prorate_ib) + " <> 'Y' then i.charge_1 + i.charge_2 " + ;
2490 " else case " + ;
2491 " when " + SQLformatChar(tcPkSys.frgt_round) + " = 'N' then case " + ;
2492 " when i.bill_wgt > 0 then (i.charge_1 + i.charge_2) * (i.weight/i.bill_wgt) " + ;
2493 " end " + ;
2494 " else case " + ;
2495 " when i.bill_wgt > 0 then (i.charge_1 + i.charge_2) * (ceiling(i.weight)/ceiling(i.bill_wgt)) " + ;
2496 " end end end else 0 end "
2497
2498 &&--- TechRec 1071520 23-Sep-2013 MANI. Added retn_track_no ===
2499 lcSql = " insert " + ;
2500 " into " + lcUPSinTemp + " (pick_num, location, carton_id, weight, carton_num " + ;
2501 " , Frgt_Amt, frgt_round, Ups_Batch, last_mod, user_id " + ;
2502 " ,tb_carrier_type, tb_carton_id, tb_dt_act, tb_manifest_close_id, tb_shipper_id " + ; && TR 1072518 Venuk.
2503 " ,bill_wgt, charge_1, charge_2, ref_num, pack_syst, serv_code, status, import_ok, zpl_label_file,retn_track_no,retn_track_no_02 " + ; && TR 1072518 Venuk.
2504 " ,track_no_02 ) " + ; &&--- TechRec 1079799 16-Jul-2014 TSV===
2505 " select i.pick_num " + ;
2506 " , " + lcPickLocation + ;
2507 " , i.carton_id " + ;
2508 " , i.weight " + ;
2509 " , i.carton_num " + ;
2510 " , " + lcFrgtAmt + ;
2511 " , " + SQLformatChar(tcPkSys.frgt_round) + ;
2512 " , " + SQLFormatNum(lnUpsBatch) + ;
2513 " , " + SQLFormatTS(DATETIME()) + ;
2514 " , " + SQLformatChar(goEnv.cUser) + ;
2515 " , i.tb_carrier_type, i.tb_carton_id, i.tb_dt_act, i.tb_manifest_close_id, i.tb_shipper_id " + ; && TR 1072518 Venuk.
2516 " , i.bill_wgt, i.charge_1, i.charge_2, i.ref_num, i.pack_syst, i.serv_code, i.status " + ; && TR 1072518 Venuk.
2517 " , i.import_ok, i.zpl_label_file " + ; && TR 1072518 Venuk.
2518 " , i.retn_track_no " + ;
2519 " , i.retn_track_no_02 " + ;
2520 " , i.track_no_02 " + ; &&--- TechRec 1079799 16-Jul-2014 TSV===
2521 " from " + .cImportTemp + " i " + ;
2522 " join " + .cPickLstTemp + " b " + ;
2523 " on b.pick_num = i.pick_num " + ;
2524 " join zzoordrh p with (nolock) " + ; && TR 1076282 with (nolock)
2525 " on p.pick_num = b.pick_num " + ;
2526 " left join zzoupsid s with (nolock) " + ; && TR 1076282 with (nolock)
2527 " on s.pkey =( " + ;
2528 " select TOP 1 pkey " + ;
2529 " from zzoupsid with (nolock) " + ; && TR 1076282 with (nolock)
2530 " where division = p.division " + ;
2531 " and (location = " + lcPickLocation + " or location = '') " + ;
2532 " and shipper = p.shipper " + ;
2533 " order by location desc) "
2534
2535 llRetVal = llRetVal and V_Sqlexec(lcSql)
2536
2537 * invoice table
2538 lcSql = STRTRAN(lcSql,'zzoordrh','zzoshprh')
2539
2540 llRetVal = llRetVal and V_Sqlexec(lcSql)
2541
2542 llRetVal = llRetVal and v_sqlexec("select count(*) as cntr from " + lcUPSinTemp ,"tcRecCnt")
2543
2544 IF llRetVal
2545 lnRecCount = tcRecCnt.cntr
2546 lnNewRecPkey = v_NextPkey("zzoupsin",lnRecCount) - lnRecCount
2547 ENDIF
2548
2549 lcSql = " update " + lcUPSinTemp + ;
2550 " set frgt_Amt = case when frgt_round = 'Y' then CEILING(frgt_Amt * 100)/100 else frgt_Amt end "
2551
2552 llRetVal = llRetVal and V_Sqlexec(lcSql)
2553
2554 &&--- TechRec 1071520 23-Sep-2013 MANI. Added retn_track_no ===
2555 lcSql = " insert " + ;
2556 " into zzoupsin(pkey, pick_num, location, carton_id, weight, carton_num, frgt_amt, ups_batch, last_mod, user_id, notes, voided " + ;
2557 " , tb_carrier_type, tb_carton_id, tb_dt_act, tb_manifest_close_id, tb_shipper_id " + ; && TR 1072518 Venuk.
2558 " , bill_wgt, charge_1, charge_2, ref_num, pack_syst, serv_code, status " + ; && TR 1072518 Venuk.
2559 " , import_ok, zpl_label_file ,retn_track_no ,retn_track_no_02 " + ; && TR 1072518 Venuk.
2560 " , track_no_02 ) " + ; &&--- TechRec 1079799 16-Jul-2014 TSV===
2561 " select pkey2 + " + SQLFormatNum(lnNewRecPkey) + ", pick_num, location, carton_id, weight, carton_num, frgt_amt, ups_batch, last_mod, user_id, '', '' " + ;
2562 " , tb_carrier_type, tb_carton_id, tb_dt_act, tb_manifest_close_id, tb_shipper_id " + ; && TR 1072518 Venuk.
2563 " , bill_wgt, charge_1, charge_2, ref_num, pack_syst, serv_code, status " + ; && TR 1072518 Venuk.
2564 " , import_ok, zpl_label_file ,retn_track_no ,retn_track_no_02, track_no_02 " + ; && TR 1072518 Venuk. &&--- TechRec 1079799 16-Jul-2014 TSV added track_no_02 ===
2565 " from " + lcUPSinTemp
2566
2567 llRetVal = llRetVal and V_Sqlexec(lcSql)
2568
2569 ENDWITH
2570
2571 SELECT (lnSelect)
2572 RETURN llRetVal
2573 ENDFUNC
2574
2575*============================================================
2576
2577 FUNCTION ProcessCartons
2578 PARAMETERS pcLocation, pcPack_Syst
2579 LOCAL llRetVal, lnSelect
2580
2581 llRetVal = true
2582 lnSelect = SELECT()
2583
2584 WITH This
2585
2586 If .lCartonLevel
2587
2588 lcSql = " update c " + ;
2589 " set track_no = i.carton_id " + ;
2590 " , frgt_amt = COALESCE(u.frgt_amt,0) " + ;
2591 " , retn_track_no = i.retn_track_no "+; &&--- TechRec 1071520 24-Sep-2013 MANI.===
2592 " , retn_track_no_02 = i.retn_track_no_02 "+; &&--- TechRec 1071520 24-Sep-2013 MANI.===
2593 " , track_no_02 = i.track_no_02 " + ; &&--- TechRec 1079799 16-Jul-2014 TSV===
2594 IIF(EMPTY(.cWareHouseStage), "", + ;
2595 " , ctn_stage = CASE WHEN COALESCE(t.sequence,0) > " + SQLFormatNUM(.nWhseStageSeq) + " THEN c.ctn_stage ELSE " + SQLFormatChar(.cWarehouseStage) + " END ") + ;
2596 IIF(.lIgnoreWeight, "", + ;
2597 " , carton_wgt = COALESCE(u.weight,0)") + ;
2598 " , last_mod = " + SQLFormatTS(DATETIME()) + ;
2599 " , user_id = " + SQLformatChar(goEnv.cUser) + ;
2600 " from zzoctnph c " + ;
2601 " join " + .cImportTemp + " i " + ;
2602 " on i.carton_num = c.carton_num " + ;
2603 " join " + .cPickLstTemp + " b " + ;
2604 " on b.pick_num = i.pick_num " + ;
2605 " and b.location = i.location " + ;
2606 " join zzoordrh p with (nolock) " + ; && TR 1076282 with (nolock)
2607 " on p.pick_num = b.pick_num " + ;
2608 " join zzoordsp s with (nolock) " + ; && TR 1076282 with (nolock)
2609 " on s.ord_num = p.ord_num " + ;
2610 " and s.pick_num = p.pick_num " + ;
2611 " and s.inv_num = p.inv_num " + ;
2612 " and s.pkey = c.fkey " + ;
2613 " and s.carton_cls = " + SQLFormatChar(CARTON_CLASS_SALES) + ;
2614 " left join zzoshpst t " + ;
2615 " on t.shpst_code = c.ctn_stage " + ;
2616 " and t.pkey = ( " + ;
2617 " select top 1 pkey " + ;
2618 " from zzoshpst " + ;
2619 " where shpst_code = c.ctn_stage ) " + ;
2620 " left join zzoupsin u " + ;
2621 " on u.pick_num = i.pick_num " + ;
2622 " and u.carton_num = i.carton_num "
2623
2624 llRetVal = llRetVal and V_Sqlexec(lcSql)
2625
2626 * invoice table
2627 lcSql = STRTRAN(lcSql,'zzoordrh','zzoshprh')
2628
2629 llRetVal = llRetVal and V_Sqlexec(lcSql)
2630
2631 lcSql = " update b "+;
2632 " set Errs_Flg = 'Y'"+;
2633 " , Errs_msg = 'INFO: Could not find carton number '+ RTRIM(i.carton_num) + ' for pick ticket ' + LTRIM(STR(i.Pick_Num))"+;
2634 " from " + .cPickLstTemp + " b " + ;
2635 " join " + .cImportTemp + " i " + ;
2636 " on i.pick_num= b.pick_num "
2637
2638 llRetVal = llRetVal and V_Sqlexec(lcSql)
2639
2640 lcSql = " update b "+;
2641 " set Errs_Flg = 'N'"+;
2642 " , Errs_msg = '' "+;
2643 " from " + .cPickLstTemp + " b " + ;
2644 " join zzoordrh p with (nolock) " + ; && TR 1076282 with (nolock)
2645 " on p.pick_num = b.pick_num " + ;
2646 " join zzoordsp s with (nolock) " + ; && TR 1076282 with (nolock)
2647 " on s.ord_num = p.ord_num " + ;
2648 " and s.pick_num = p.pick_num " + ;
2649 " and s.inv_num = p.inv_num " + ;
2650 " and s.carton_cls = " + SQLFormatChar(CARTON_CLASS_SALES) + ;
2651 " join zzoctnph c with (nolock) " + ; && TR 1076282 with (nolock)
2652 " on s.pkey = c.fkey " + ;
2653 " join " + .cImportTemp + " i " + ;
2654 " on i.carton_num = c.carton_num " + ;
2655 " and i.pick_num = b.pick_num "
2656
2657 llRetVal = llRetVal and V_Sqlexec(lcSql)
2658
2659 * invoice table
2660 lcSql = STRTRAN(lcSql,'zzoordrh','zzoshprh')
2661
2662 llRetVal = llRetVal and V_Sqlexec(lcSql)
2663
2664 lcSql = " select * from " + .cPickLstTemp + " where Errs_Flg = 'Y' " && This changes to .cPickLstTemp2 also??
2665
2666 llRetVal = llRetVal and V_Sqlexec(lcSql,'tcErrList')
2667
2668 IF llRetVal AND USED('tcErrList') AND RECCOUNT('tcErrList') > 0
2669 SELECT tcErrList
2670
2671 SCAN
2672 lcMsg = tcErrList.Errs_msg
2673 .oLog.LogEntry(lcMsg)
2674 ENDSCAN
2675 ENDIF
2676
2677 .TableClose("tcErrList")
2678 ENDIF
2679 ENDWITH
2680
2681 SELECT (lnSelect)
2682 RETURN llRetVal
2683 ENDFUNC
2684
2685*============================================================
2686
2687 FUNCTION ProcessSps
2688 LOCAL llRetVal, lnSelect
2689
2690 llRetVal = true
2691 lnSelect = SELECT()
2692
2693 WITH This
2694 IF .lServerSide && for carton level some records in .cPickLstTemp were flagged as ERROR in .ProcessCartons.
2695
2696 lcSql = " delete h "+;
2697 " from zzoupsib h " + ;
2698 " join " + .cPickLstTemp + " b " + ;
2699 " on b.pick_num = h.pick_num " + ;
2700 " where h.import_ok = 'Y' "
2701
2702 llRetVal = llRetVal and V_Sqlexec(lcSql)
2703
2704 ENDIF
2705 ENDWITH
2706
2707 SELECT (lnSelect)
2708 RETURN llRetVal
2709 ENDFUNC
2710
2711*============================================================
2712
2713 FUNCTION ProcessAutoBill
2714 LPARAMETERS pcLocation, pcPack_Syst
2715 LOCAL llRetVal, lnSelect
2716
2717 llRetVal = true
2718 lnSelect = SELECT()
2719
2720 WITH This
2721
2722 .clastCtnMod= "##TT" + GetUniqueFileName()
2723
2724 lcSql = " select Ord_Status,pick_num,pkey as Pick_pkey,'' as LastPrCtn, '' as abill_ok, '' as ProcessPick " + ;
2725 " into " + .clastCtnMod + ;
2726 " from zzoordrh with (nolock) where 1=2" && TR 1076282 with (nolock)
2727
2728 llRetVal = llRetVal and V_Sqlexec(lcSql)
2729
2730 lcSql = " Insert into " + .clastCtnMod + "(Ord_Status, pick_num, Pick_pkey, LastPrCtn, abill_ok, ProcessPick) " + ;
2731 " select l.Ord_Status, l.pick_num, l.pkey, l.LastPrCtn, " + SQLformatChar(tcPkSys.abill_ok) + ;
2732 " , (case when (" + SQLformatChar(tcPkSys.pk_ctn_ib) + " <> 'C' or (" + SQLformatChar(tcPkSys.pk_ctn_ib) + " = 'C' AND l.LastPrCtn = 'Y')) then 'Y' else 'N' end) " + ;
2733 " from ( " + ;
2734 " select case when exists( " + ;
2735 " select * " + ;
2736 " from zzoctnph h with (nolock) " + ; && TR 1076282 with (nolock)
2737 " join zzoordsp sp with (nolock) " + ; && TR 1076282 with (nolock)
2738 " on h.fkey = sp.pkey " + ;
2739 " where sp.ord_num = t.ord_num " + ;
2740 " and sp.pick_num = t.pick_num " + ;
2741 " and sp.inv_num = t.inv_num " + ;
2742 " and h.Carton_cls = 'S') " + ;
2743 " then case when exists( " + ;
2744 " select h.pkey " + ;
2745 " from zzoctnph h with (nolock) " + ; && TR 1076282 with (nolock)
2746 " join zzoordsp sp with (nolock) " + ; && TR 1076282 with (nolock)
2747 " on h.fkey = sp.pkey " + ;
2748 " where sp.ord_num = t.ord_num " + ;
2749 " and sp.pick_num = t.pick_num " + ;
2750 " and sp.inv_num = t.inv_num " + ;
2751 " and h.Carton_cls = 'S' " + ;
2752 " and h.ctn_stage <> " + SQLformatChar(.cWareHouseStage) + ;
2753 " ) then 'N' " + ;
2754 " else 'Y' " + ;
2755 " end " + ;
2756 " else 'N' " + ;
2757 " end as LastPrCtn " + ;
2758 " , t.Ord_Status " + ;
2759 " , t.pick_num " + ;
2760 " , t.pkey " + ;
2761 " from " + .cPickLstTemp + " b " + ;
2762 " join zzoordrh t with (nolock) " + ; && TR 1076282 with (nolock)
2763 " on b.pick_num = t.pick_num " + ;
2764 " join " + .cImportTemp + " i " + ;
2765 " on i.pick_num = t.pick_num " + ;
2766 " ) L "
2767
2768 llRetVal = llRetVal and V_Sqlexec(lcSql)
2769
2770 * invoice table
2771 lcSql = STRTRAN(lcSql,'zzoordrh','zzoshprh')
2772
2773 llRetVal = llRetVal and V_Sqlexec(lcSql)
2774
2775*--- TechRec 1105628 27-Jun-2017 AZhadanov --- added extra where not exists (select 1 from zzyabill t where t.pick_num = a.pick_num)
2776***** to prevent duplicates in the zzyabill
2777
2778*!* lcSql = " Insert into zzyabill(pick_num,pick_pkey,notes,user_id,last_mod) " + ;
2779*!* " select pick_num, Pick_pkey,'' " + ;
2780*!* " , " + SQLformatChar(goEnv.cUser) + ;
2781*!* " , " + SQLFormatTS(DATETIME()) + ;
2782*!* " from (select distinct pick_num, pick_pkey, abill_ok , ProcessPick, Ord_Status from " + .clastCtnMod + ;
2783*!* " where abill_ok ='Y' AND ProcessPick = 'Y' AND Ord_Status = 'P') a "
2784
2785
2786
2787
2788
2789 lcSql = " Insert into zzyabill(pick_num,pick_pkey,notes,user_id,last_mod) " + ;
2790 " select distinct pick_num, pick_pkey ,'' " + ;
2791 " , " + SQLformatChar(goEnv.cUser) + ;
2792 " , " + SQLFormatTS(DATETIME()) + ;
2793 " from "+ .clastCtnMod + " a "+ ;
2794 " where abill_ok ='Y' AND ProcessPick = 'Y' AND Ord_Status = 'P' " + ;
2795 " and not exists (select 1 from zzyabill t where t.pick_num = a.pick_num) "
2796
2797*=== TechRec 1105628 27-Jun-2017 AZhadanov ---
2798
2799 llRetVal = llRetVal and V_Sqlexec(lcSql)
2800
2801 * StampOrderStage
2802
2803 IF (NOT EMPTY(.cShpstCode) OR .lUpdateActShipDate)
2804
2805 lcSQL = " update t " + + ;
2806 " set user_id = " + SQLFormatChar(goEnv.cUser) + ;
2807 " ,last_mod = " + SQLFormatTS(DATETIME()) + ;
2808 " ,shpst_code= CASE WHEN " + SQLformatChar(tcPkSys.shpst_code) + " > '' THEN CASE WHEN COALESCE(s.sequence,0) > " + SQLFormatNUM(.nShpstCodeSeq) + ;
2809 " THEN t.shpst_code ELSE " + SQLformatChar(tcPkSys.shpst_code) + " END ELSE t.shpst_code END" + ;
2810 IIF(.lUpdateActShipDate, ",act_ship_date = " + SQLFormatTS(DATETIME()),"")+;
2811 " from zzoordrh t " + ;
2812 " left join zzoshpst s " + ;
2813 " on t.shpst_code = s.shpst_code " + ;
2814 " join " + .cImportTemp + " i " + ;
2815 " on i.pick_num = t.pick_num " + ;
2816 " join " + .clastCtnMod + " l " + ;
2817 " on l.pick_num = t.pick_num " + ;
2818 " and l.Pick_pkey= t.pkey " + ;
2819 " join " + .cPickLstTemp + " b " + ;
2820 " on b.pick_num = t.pick_num " + ;
2821 " where l.ProcessPick = 'Y' "
2822
2823 llRetVal = llRetVal and V_Sqlexec(lcSql)
2824 * invoice table
2825 lcSql = STRTRAN(lcSql,'zzoordrh','zzoshprh')
2826
2827 llRetVal = llRetVal and V_Sqlexec(lcSql)
2828
2829 ENDIF
2830 ENDWITH
2831
2832 SELECT (lnSelect)
2833 RETURN llRetVal
2834 ENDFUNC
2835
2836*============================================================
2837
2838 FUNCTION ProcessMasterCartons
2839 PARAMETERS pcLocation, pcPack_Syst
2840 LOCAL llRetVal, lnSelect, lcTemp_Update_Carton, lcSQL
2841
2842 llRetVal = true
2843 lnSelect = SELECT()
2844
2845 WITH This
2846
2847 IF llRetVal AND .lProcessMasterCartons AND .lCartonLevel
2848
2849 * .LogEntry("Update Master Carton table...")
2850
2851 .cTempUpdateCarton = "##TT" + GetUniqueFileName()
2852
2853 lcSql = " select i.carton_num " + ;
2854 " , i.carton_id " + ;
2855 " , i.retn_track_no "+ ;
2856 " , i.retn_track_no_02 "+;
2857 " , i.track_no_02 " + ; &&--- TechRec 1079799 16-Jul-2014 TSV===
2858 " into " + .cTempUpdateCarton + ;
2859 " from " + .cImportTemp + " i " + ;
2860 " join " + .cPickLstTemp + " b " + ;
2861 " on b.pick_num = i.pick_num "
2862
2863 llRetVal = llRetVal and V_Sqlexec(lcSql)
2864
2865 lcSQL = " UPDATE zzymastd " + ;
2866 " SET track_no = tmpTable.carton_id " + ;
2867 " , user_id = " + SQLFormatChar(goEnv.cUser) + ;
2868 " , last_mod = " + SQLFormatTS(DATETIME()) + ;
2869 " FROM zzymastd " + ;
2870 " JOIN " + .cTempUpdateCarton + " tmpTable " + ;
2871 " ON tmpTable.carton_num = zzymastd.master_carton"
2872
2873 llRetVal = llRetVal and V_Sqlexec(lcSql)
2874
2875 lcSQL = " UPDATE zzoctnph " + ;
2876 " SET track_no = tmpTable.carton_id " + ;
2877 " , retn_track_no = tmpTable.retn_track_no "+; &&--- TechRec 1071520 24-Sep-2013 MANI. ===
2878 " , retn_track_no_02 = tmpTable.retn_track_no_02 "+; &&--- TechRec 1071520 24-Sep-2013 MANI. ===
2879 " , track_no_02 = tmpTable.track_no_02 " + ; &&--- TechRec 1079799 16-Jul-2014 TSV===
2880 IIF(EMPTY(.cWareHouseStage), "", ;
2881 " , ctn_stage = CASE WHEN COALESCE(s.sequence,0) > " + SQLFormatNUM(.nWhseStageSeq) + " THEN zzoctnph.ctn_stage ELSE " + SQLFormatChar(.cWarehouseStage) + " END ") + ; && TR 1054422 " + SQLFormatChar(.cWareHouseStage))
2882 " , user_id = " + SQLFormatChar(goEnv.cUser) + ;
2883 " , last_mod = " + SQLFormatTS(DATETIME()) + ;
2884 " FROM zzoctnph " + ;
2885 " JOIN zzymastd with (nolock) " + ; && TR 1076282 with (nolock)
2886 " ON zzymastd.carton_num = zzoctnph.carton_num " + ;
2887 " JOIN " + .cTempUpdateCarton + " tmpTable " + ;
2888 " ON tmpTable.carton_num = zzymastd.master_carton" + ;
2889 " LEFT JOIN zzoshpst s " + ;
2890 " ON zzoctnph.ctn_stage = s.shpst_code "
2891
2892 llRetVal = llRetVal and V_Sqlexec(lcSql)
2893
2894 IF (NOT EMPTY(.cShpstCode) OR .lUpdateActShipDate)
2895
2896 lcSQL = " UPDATE zzoordrh " + ;
2897 " SET user_id = " + SQLFormatChar(goEnv.cUser) + ;
2898 " ,last_mod = " + SQLFormatTS(DATETIME()) + ;
2899 IIF(NOT EMPTY(.cShpstCode), ",shpst_code = CASE WHEN COALESCE(s.sequence,0) > " + SQLFormatNUM(.nShpstCodeSeq) + ;
2900 " THEN zzoordrh.shpst_code ELSE " + SQLFormatChar(.cShpstCode) + " END ","") + ;
2901 IIF(.lUpdateActShipDate, ",act_ship_date = " + SQLFormatTS(DATETIME()),"")+;
2902 " FROM zzoordrh " + ;
2903 " JOIN zzymastd with (nolock) " + ; && TR 1076282 with (nolock)
2904 " ON zzymastd.pick_num = zzoordrh.pick_num " + ;
2905 " JOIN " + .cTempUpdateCarton + " tmpTable " + ;
2906 " ON tmpTable.carton_num = zzymastd.master_carton" + ;
2907 " LEFT JOIN zzoshpst s ON zzoordrh.shpst_code = s.shpst_code " + ;
2908 " join " + .clastCtnMod + " l " + ;
2909 " on l.pick_num = zzoordrh.pick_num " + ;
2910 " and l.Pick_pkey= zzoordrh.pkey " + ;
2911 " where l.ProcessPick = 'Y' "
2912
2913 llRetVal = llRetVal and V_Sqlexec(lcSql)
2914 * invoice table
2915 lcSql = STRTRAN(lcSql,'zzoordrh','zzoshprh')
2916 llRetVal = llRetVal and V_Sqlexec(lcSql)
2917 ENDIF
2918
2919 *-- Query Only master cartons
2920 lcSQL = " SELECT DISTINCT master_carton " + ;
2921 " FROM " + .cTempUpdateCarton + " t " + ;
2922 " JOIN zzymastd d with (nolock) " + ; && TR 1076282 with (nolock)
2923 " ON t.carton_num = d.carton_num "
2924
2925 llMastCtn = llRetVal AND v_SQLExec(lcSQL , "cursMasterCtn") AND USED("cursMasterCtn")
2926
2927 lcFinalPickList = ""
2928
2929 IF llMastCtn AND RECCOUNT("cursMasterCtn") > 0
2930 SELECT cursMasterCtn
2931 SCAN
2932 lcPickList = ""
2933 llMastCtn = .LastMasterCartonProcessed_New(Master_Carton, .cWareHouseStage, @lcPickList)
2934
2935 IF llMastCtn
2936 lcFinalPickList = lcFinalPickList + lcPickList + ","
2937 ENDIF
2938
2939 ENDSCAN
2940
2941 lcFinalPickList = RemoveLastDelimiter(lcFinalPickList, ",")
2942
2943 IF !EMPTY(lcFinalPickList)
2944
2945 lcSQL = " UPDATE h " + ;
2946 " SET shpst_code = " + ;
2947 " CASE WHEN COALESCE(s.sequence,0) > " + SQLFormatNUM(.nShpstCodeSeq) + ;
2948 " THEN h.shpst_code ELSE " + SQLFormatChar(.cShpstCode) + " END " + ;
2949 " FROM zzoordrh h " + ;
2950 " LEFT JOIN zzoshpst s " +;
2951 " ON h.shpst_code = s.shpst_code " + ;
2952 " WHERE pick_num IN (" + lcFinalPickList + ")"
2953
2954 llRetVal = llRetVal AND v_SQLExec(lcSQL)
2955 * invoice table
2956 lcSql = STRTRAN(lcSql,'zzoordrh','zzoshprh')
2957 llRetVal = llRetVal and V_Sqlexec(lcSql)
2958
2959 IF .lInsertAutoBill
2960 llRetVal = llRetVal AND .BulkInsertAutoBill("zzoordrh", lcFinalPickList )
2961 ENDIF
2962
2963 ENDIF
2964
2965 ENDIF
2966 .TableClose("cursMasterCtn")
2967 ENDIF
2968
2969 ENDWITH
2970
2971 SELECT (lnSelect)
2972 RETURN llRetVal
2973 ENDFUNC
2974
2975*================================================================
2976
2977 FUNCTION LastMasterCartonProcessed_New
2978 PARAMETERS tcMasterCarton,tcStage,tcPickList
2979
2980 LOCAL lcSQL,lcCursor,llRetVal,lnSelect,lnSeq
2981 lnSelect = SELECT()
2982 tcPickList = ""
2983 lnSeq = vl_shpst(tcStage,"sequence")
2984
2985 TEXT TO lcSQL TEXTMERGE NOSHOW
2986 select d2.pick_num
2987 from zzymastd d1
2988 join zzymastd d2
2989 on d1.pick_num = d2.pick_num
2990 join zzoctnph ch1
2991 on d2.carton_num = ch1.carton_num
2992 join zzoordsp sp1
2993 on ch1.fkey = sp1.pkey
2994 join zzoctnph ch2
2995 on sp1.pkey = ch2.fkey
2996 left join zzoshpst st
2997 on ch2.ctn_stage = st.shpst_code
2998 where d1.master_carton = <<SQLFormatChar(tcMasterCarton)>>
2999 group by d2.pick_num
3000 having min(COALESCE(st.sequence, 0)) >= <<SQLFormatNum(lnSeq)>>
3001 ENDTEXT
3002
3003 lcCursor = getuniquefilename()
3004 llRetVal = v_sqlprep(lcSQL,lcCursor)
3005
3006 IF v_sqlprep(lcSQL,lcCursor)
3007 SELECT (lcCursor)
3008 SCAN
3009 tcPickList = tcPickList + TRANSFORM(pick_num) + ","
3010 ENDSCAN
3011 tcPickList = removelastdelimiter(tcPickList,",")
3012 ENDIF
3013
3014 USE IN SELECT(lcCursor)
3015 SELECT (lnSelect)
3016 RETURN llRetVal
3017 ENDFUNC
3018*=== TechRec 1064004 11-Oct-2012 jjanand ===
3019
3020*===================================================================================================
3021
3022ENDDEFINE