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