· 7 years ago · Feb 11, 2019, 10:02 PM
1****************************************************************************************
2* Program....: clsoPRpr.PRG
3* Date.......:
4* Abstract...: EDI 855 Outbound Processing Business Object Class
5* Notes: When records stay back in interface tables (Auto_proc="N") from previous
6* process, if current Transaction tables process have save ord_num then delete
7* all records in Interface table.
8*****************************************************************************************
9
10#Include System.h
11#Include EDI.h
12
13#Define EDI_USER "EDI"
14#Define EDI_SUPPLIER_REQUIRED_MSG "Requires Supplier number"
15#Define EDI_DEPT_REQUIRED_MSG "Requires Department number"
16#Define EDI_PO_REQUIRED_MSG "Requires PO number"
17*--- TR 1034481 NH
18#Define EDI_DUNS_REQ_MSG "Requires DUNS number"
19*=== TR 1034481 NH
20#Define VFP_EMPTY_DATE_DEFAULT {01/01/1900}
21#Define EDI_DNT_TAG "DNT"
22#Define EDI_HNT_TAG "HNT"
23#Define MAX_HNT_LINES 100
24#Define MAX_DNT_LINES 100
25#Define EDI_SDQ_TAG "SDQ"
26*- 1005605 06/16/04 YIK
27#Define SET_RANGE_TYPE "S"
28#Define RUN_RANGE_TYPE "R"
29
30*--- TR 1030443 23-Apr-2008 Goutam
31#Define EDI_TRK_TAG "TRK"
32*=== TR 1030443 23-Apr-2008 Goutam
33
34Define Class BPO855OProcess As BPOEDIBase
35 Name = "BPO855OProcess"
36 lErrorState = .F.
37 lUserInterface = .F.
38 cFilterCriteria = ""
39 nRecordsToProcess= 0
40 nInterfaceRecords= 0
41 nFlatfileRecords=0
42 lHaveSLN = .F.
43 *--- TR 1010900 NH
44 Tie_setup = .F.
45 *=== TR 1010900 NH
46
47 *--- TR 1013007 NH
48 lFrom850 = .F.
49 *=== TR 1013007 NH
50
51 *--- TR 1030443 22-Apr-2008 Goutam
52 lFrom945 = false
53 *=== TR 1030443 22-Apr-2008 Goutam
54
55 *--- TR 1031624 JUL-14-2008 BR
56 lHasShipment = .F.
57 *=== TR 1031624 JUL-14-2008 BR
58
59 * --- TR 1044514 3/25/10 CM/NSD
60 lVMI = .F.
61 * === TR 1044514 3/25/10 CM/NSD
62
63 *--- TechRec 1078545 30-Jun-2014 vkrishnamurthy ---
64 cNewShipDate = ''
65 *=== TechRec 1078545 30-Jun-2014 vkrishnamurthy ===
66 lResolveUPCSKU = .F. && TR 1097094 7/22/16 Gurinder
67
68 ************************************************************************************
69 * ResetCounter
70 ************************************************************************************
71 Procedure ResetCounter
72 With This
73 .lErrorState = .F.
74 .lUserInterface = .F.
75 .nRecordsToProcess= 0
76 .nInterfaceRecords= 0
77 .nFlatfileRecords= 0
78 .cFilterCriteria = ""
79 .cMessage=""
80 Endwith
81 Endproc
82
83 ************************************************************************************
84 * Init
85 ************************************************************************************
86 Procedure Init
87 Lparameters plUserInterface
88 This.lUserInterface = plUserInterface
89 This.lScheduled= !plUserInterface
90 This.lResolveUPCSKU = (goEnv.sv('EDI_RESOLVE_UPC_SKU_FROM_SOURCE','N') = 'Y') && TR 1097094 7/22/16 Gurinder
91 Local llRetVal
92 llRetVal= .T.
93 Return llRetVal
94 Endproc
95
96 ************************************************************************************
97 * Cleanup
98 ************************************************************************************
99 Procedure Cleanup
100 * Delete all temp views
101 Dime laTables[25]
102 laTables[1]= "VzzeoPRth_Proc"
103 laTables[2]= "VzzeoPRtd_Proc"
104 laTables[3]= "VzzeoPRtc_Proc"
105 laTables[4]= "VzzeoPRta_Proc"
106
107 laTables[5]= "VzzeoPRih_Proc"
108 laTables[6]= "VzzeoPRid_Proc"
109 laTables[7]= "VzzeoPRic_Proc"
110 laTables[8]= "VzzeoPRia_Proc"
111
112 laTables[9]= "Vzzoordrh_Proc"
113 laTables[10]="Vzzoordrd_Proc"
114 laTables[11]="zzeoPRcr"
115 *- Detail notes
116 laTables[12]= "VzzeoPRtn_Proc"
117 laTables[13]= "VzzeoPRin_Proc"
118
119 laTables[14]= "VzzeoPRhh_Proc"
120 laTables[15]= "VzzeoPRhd_Proc"
121 laTables[16]= "VzzeoPRhc_Proc"
122 laTables[17]= "VzzeoPRha_Proc"
123 laTables[18]= "Vzzoshprh_Proc"
124 laTables[19]= "Vzzoshprd_Proc"
125
126 *- 1005605 06/14/04 YIK
127 *- Add 3 tables for SLN explosion
128 laTables[20]= "tcCustSLN"
129 laTables[21]= "tcItemSLN"
130 laTables[22]= "tcSLN"
131
132 laTables[23]= "VzzeopriSDQ_Proc" && TR 1074595 KISHORE 22-JAN-2014
133 laTables[24]= "VzzeoprhSDQ_Proc" && TR 1074595 KISHORE 22-JAN-2014
134
135 laTables[25]= "Vzzeopriwhse_proc" && 1098611
136
137 llOKtoContinue= This.TableClose(@laTables, .T.)
138
139 *- TR 1051511 FH 12/29/10
140 IF this.lFrom850 = .F. AND this.lFrom945 = .F. then
141 *-TR 1048160 FH
142 Dimension laUsedTables[1]
143 lnCount = AUSED(laUsedTables)
144 FOR lnX = 1 TO lnCount
145 lcCursor = laUsedTables[lnX, 1]
146 IF NOT INLIST(lcCursor, "SQLRESULT","TCCOLUMNS","TCFILTER","TCPROCS")
147 USE IN SELECT (lcCursor)
148 endif
149 ENDFOR
150 *-TR 1048160 FH
151 ENDIF
152 *- TR 1051511 FH 12/29/10
153
154 *--- TechRec 1058716 11-Jan-2012 jisingh ---
155 This.TableClose("tceoprWH")
156 This.TableClose("tceoprWD")
157 *=== TechRec 1058716 11-Jan-2012 jisingh ===
158
159 Endproc
160
161 ************************************************************************************
162 * MAIN Process(oPR)- outbound 855 ASN process
163 ************************************************************************************
164 *--- TR 1013007 NH : Added parameters pcRef855Msg, pc855DataTbls, plUserInterface
165 Procedure ProcessoPR
166 Lparameters pcFilterCriteria, pcRef855Msg, pc855DataTbls, plUserInterface
167 Local llOKtoContinue, lnOldSelect, ;
168 lcLogTableName, lnStartSeconds, lcFilterCriteria, llBeganTransaction,;
169 lcMessageStr, lnzzeoPRih_Reccount && TR 1031624 JUL-14-2008 BR
170
171 * --- TR 1044514 3/25/10 CM/NSD
172 LOCAL llVMI
173 llVMI = .F.
174 * === TR 1044514 3/25/10 CM/NSD
175
176 llOKtoContinue = .T.
177
178
179 ************************************************************************************
180 * Is there another EDI 855 process running?
181
182 If !This.LockProcess("EDIOPRPROCESS")
183 This.lProcessLocked = .T.
184 Return .F.
185 Endif
186 lnOldSelect = Select()
187
188 *--- TR 1013007 NH
189 Declare la855DataTbls[1]
190
191 *--- TR 1030443 30-Apr-2008 Goutam
192 *This.lFrom850 = Not Empty(pc855DataTbls)
193 This.lFrom850 = Not Empty(pc855DataTbls) AND NOT This.lFrom945
194 *=== TR 1030443 30-Apr-2008 Goutam
195
196 If This.lFrom850
197 This.lUserInterface = plUserInterface
198 This.lScheduled = !plUserInterface
199 parseArr(@la855DataTbls,pc855DataTbls,,",")
200 Else
201 This.CreateFrmProgressBar(10,"Selecting records For 855 Process...","Total 855 Process.")
202
203 *--- TechRec 1078545 30-Jun-2014 vkrishnamurthy ---
204 This.SetParamBRO()
205 *=== TechRec 1078545 30-Jun-2014 vkrishnamurthy ===
206 Endif
207 *=== TR 1013007 NH 2005-10-13
208
209 With This
210 *--- TR 1030443 30-Apr-2008 Goutam
211*!* IF !Empty(pcFilterCriteria)
212*!* This.cFilterCriteria = Iif(!Empty(pcFilterCriteria), " And " + pcFilterCriteria,"")
213*!* ENDIF
214 IF !Empty(pcFilterCriteria) AND NOT .lFrom945
215 This.cFilterCriteria = Iif(!Empty(pcFilterCriteria), " And " + pcFilterCriteria,"")
216 ELSE
217 This.cFilterCriteria = pcFilterCriteria
218 ENDIF
219 *=== TR 1030443 30-Apr-2008 Goutam
220
221 .oLog.OpenLog("EDI855OPRROCESS", I("EDI855OPRROCESS"), .lScheduled)
222 .oLog.LogProgram("clsoprpr.prg")
223 .oLog.LogEntry("Filter Criteria: " + Iif(Empty(.cFilterCriteria),"None", .cFilterCriteria))
224 * create all views
225 .oLog.LogMajorStage("Loading Flatfile to Transaction table.")
226 .AdvanceThermoTotalWithCaptionPlus("Creating all views to support 855 process...")
227 *--- TR 1013007 NH
228
229 *--- TR 1030443 30-Apr-2008 Goutam
230 *If .lFrom850
231 If .lFrom850 AND NOT .lFrom945
232 *=== TR 1030443 30-Apr-2008 Goutam
233 llOKtoContinue = llOKtoContinue And .GetAdditionalData(@la855DataTbls)
234 llOKtoContinue = llOKtoContinue And .CreateOPRViewsFrom850(@la855DataTbls)
235 Else
236 llOKtoContinue = llOKtoContinue And .CreateOPRViews(This.cFilterCriteria)
237 Endif
238
239 If llOKtoContinue
240 * If .CreateOPRViews(This.cFilterCriteria)
241
242 *=== TR 1013007 NH
243 .AdvanceThermoTotalWithCaptionPlus("Selecting Order(s) to process...")
244 If .OpenoPRViews(This.cFilterCriteria)
245 If Not .lFrom850
246 .CreateWorkTables("Vzzoordrh_Proc", "Vzzoordrd_Proc","Vzzoshprh_Proc", "Vzzoshprd_Proc", "tcWrkHdr", "tcWrkDtl")
247 * number of Pack Orders process
248 .lNoDataFound = .F.
249 *- .nRecordsToProcess= recc("Vzzoordrh_Proc")
250 .nRecordsToProcess= Recc("tcWrkHdr")
251 Endif
252
253 If .nRecordsToProcess > 0 Or .lFrom850
254 ******************************************************************************
255 * 1. recreate 855 Transaction header/detail
256 *--- TR 1013007 NH 2005-10-14
257 If .lFrom850
258 .AdvanceThermoTotalWithCaptionPlus("Creating 855 Transaction tables...")
259 llOKtoContinue= llOKtoContinue And .CreateOPR_TransCursor("tceoPRth", "tceoPRtd")
260 Else
261 .AdvanceThermoTotalWithCaptionPlus("Creating 855 Transaction tables...")
262 llOKtoContinue= llOKtoContinue And .CreateoPRTransaction("tcWrkHdr", "tcWrkDtl",;
263 "VzzeoPRth_Proc", "VzzeoPRtd_Proc", "tceoPRth", "tceoPRtd", "VzzeoPRih_Proc",;
264 "VzzeoPRid_Proc", "VzzeoPRic_Proc", "VzzeoPRia_Proc")
265 ENDIF
266
267 This.lHasShipment = .T. && TR 1031624 JUL-14-2008 BR
268
269 ******************************************************************************
270 * 1a Populate range styles
271 llOKtoContinue= llOKtoContinue And .ConsolidatePrepackByRangeSKU("tceoPRth", "tceoPRtd")
272 .AdvanceThermoTotalWithCaptionPlus("Getting PrePack informations to support SLN explosion...")
273 * 2. no check for transaction header
274 .oLog.LogMajorStage("Validating Transactions.")
275 .AdvanceThermoTotalWithCaptionPlus("Validating Order header informations...")
276
277 llOKtoContinue= llOKtoContinue And .CheckHeader("tcEoPRth", "zzeoprcr")
278 ******************************************************************************
279 *- 1005605 06/14/04 YIK
280 * 2a. Populate Prepack and Range Style SLNs
281 llOKtoContinue = llOKtoContinue And .GetPrepackInfos("tceoPRth", "tceoPRtd", "tcSLN")
282 llOKtoContinue = llOKtoContinue And .MergeRangeSLNsToSLNs("tcRangeSLN", "tcSLN")
283 If Used("tcSLN") And This.CountTotalRecs ("tcSLN")> 0
284 .AdvanceThermoTotalWithCaptionPlus("Validating PrePack components UPC/SKU...")
285 llOKtoContinue = llOKtoContinue And .CheckSLNUPCSKU("tceoPRth", "tceoPRtd", "tcSLN", "tcCustSLN", "tcItemSLN")
286 Endif
287 *= 1005605 YIK
288
289 * --- TR 1048571 8/18/10 CM
290 .AdvanceThermoTotalWithCaptionPlus("Changing Color Code With original Color Code...")
291 llOKtoContinue = llOKtoContinue And .ChangeColor("tceoPRtd")
292 * === TR 1048571 8/18/10 CM
293
294 * 3. validate UPC,SKU in transaction detail
295 .AdvanceThermoTotalWithCaptionPlus("Validating UPC, SKU...")
296 *- llOKtoContinue= llOKtoContinue And .CheckDetail("tcEoPRth", "tcEoPRtd", "zzeoPRcr")
297 llOKtoContinue= llOKtoContinue And .CheckUPCSKU("tcEoPRth", "tcEoPRtd", .T.)
298 *=
299
300 * --- TR 1048571 8/18/10 CM
301 .AdvanceThermoTotalWithCaptionPlus("Restoring Color Code With Invoice Color Code...")
302 llOKtoContinue = llOKtoContinue And .RestoreColor("tcEoPRtd")
303 * === TR 1048571 8/18/10 CM
304ASSERT .f.
305 *--- TR 1013007 NH 2005-10-18
306 If .lFrom850
307 *--- update division name and check if all trems infos are populated
308 llOKtoContinue = llOKtoContinue And .SetDivNameAndComp("tceoprTH","tceoprtd")
309 .oLog.LogEntry("Updating Division name and company name " + Iif(llOKtoContinue,"completed.","failed."))
310
311 *--- TechRec 1058716 11-Jan-2012 jisingh ---
312 llOKtoContinue = llOKtoContinue And .ValidateForzani("tceoprTH","tceoprTD", "zzeoprcr")
313
314 llOKtoContinue = llOKtoContinue And .PopulateDataWhseFrom850("tceoprTH", "tceoprTD", "zzeoprcr", "tceoprWH", "tceoprWD", la855DataTbls[7])
315 *=== TechRec 1058716 11-Jan-2012 jisingh ===
316 Else
317 *=== 1013007 NH 2005-10-18
318 *- populate terms, resolve address
319 .AdvanceThermoTotalWithCaptionPlus("Populate Terms info and resolve address for each order...")
320 llOKtoContinue= llOKtoContinue And .PopulateOrders("tceoprTH")
321
322 .AdvanceThermoTotalWithCaptionPlus("Populate EDI Store...")
323 llOKtoContinue= llOKtoContinue And .PopulateEDI_Store("tceoprTH", "zzeoprcr")
324 .AdvanceThermoTotalWithCaptionPlus("Populate EDI Center...")
325 llOKtoContinue= llOKtoContinue And .PopulateEDI_Center("tceoprTH", "zzeoprcr")
326
327 *--- TR 1030443 22-Apr-2008 Goutam
328 .AdvanceThermoTotalWithCaptionPlus("Populate Shipping Information...")
329 llOKtoContinue= llOKtoContinue And .PopulateShippingInfo("tceoprTH")
330 *=== TR 1030443 22-Apr-2008 Goutam
331
332 *--- TechRec 1038247 17-Mar-2009 T.Shenbagavalli ---
333 .AdvanceThermoTotalWithCaptionPlus("Populate Discount Information...")
334 llOKtoContinue= llOKtoContinue And .PopulateDiscount("tceoprTD")
335 *=== TechRec 1038247 17-Mar-2009 T.Shenbagavalli ===
336
337 Endif
338
339 *--- 36646 01/20/03 YIK - Check if there are any SDQ POs and that they are valid
340 *- Hold the entire PO if one order is invalid.
341
342 .AdvanceThermoTotalWithCaptionPlus("Validating SDQs...")
343 llOKtoContinue= llOKtoContinue And .CheckSDQ("tceoprTH", "tceoprTD", "zzeoprcr")
344 *=== 36646
345
346 *--- TR 1013007 NH 2005-10-18
347 If Not .lFrom850
348 *- 1005605 06/14/04 YIK
349 *- We can have a prepack conversion, when we use the 855(o) to send the
350 *- existing orders initially received via edi.
351 *- Retrieve data from PO detail history table
352
353 *--- TR 1025228 6-Jul-2007 Goutam
354 .AdvanceThermoTotalWithCaptionPlus("Getting header Information from the PO History ...")
355 llOKtoContinue= llOKtoContinue And .GetPOHistHeader("tceoPRth")
356 *=== TR 1025228 6-Jul-2007 Goutam
357
358 .AdvanceThermoTotalWithCaptionPlus("Getting Information from the PO History ...")
359 llOKtoContinue= llOKtoContinue And .GetPOHistDetail("tceoPRth", "tceoPRtd", .T.)
360
361 *--- TechRec 1089471 14-Oct-2015 TSV---
362 llOKtoContinue= llOKtoContinue And .PopulateDataWhse("tceoprTH", "tceoprTD", "zzeoprCR", "tceoprWH", "tceoprWD")
363 *=== TechRec 1089471 14-Oct-2015 TSV===
364
365 .AdvanceThermoTotalWithCaptionPlus("Running Prepack Quantity Conversion...")
366 llOKtoContinue= llOKtoContinue And .PrepackConversionOut("tceoPRtd")
367
368 *- Explosion - move SLNs (prepack components) to DTLs.
369 .AdvanceThermoTotalWithCaptionPlus("Prepack components explosion...")
370 llOKtoContinue = llOKtoContinue And .PrepackExplosion("tceoPRtd", "tcItemSLN")
371 *= 1005605 YIK
372
373 * --- TR 1044514 3/25/10 CM/NSD
374 .AdvanceThermoTotalWithCaptionPlus("Validating Aux SKU...")
375 llOKToContinue = llOKToContinue And .ValidateAuxSKUForVMIInvoices("tceoPRth", "tceoPRtd", "zzeoprcr", @llVMI)
376 If llVMI
377 .lVMI = .T.
378 Endif
379 * === TR 1044514 3/25/10 CM/NSD
380
381 Endif
382
383 ******************************************************************************
384 * 4. Mark all transaction headers that have detail error
385 .AdvanceThermoTotalWithCaptionPlus("Updating 855 headers with detail in error...")
386 llOKtoContinue= llOKtoContinue And .MarkHeaderForDetailWithError("tcEoPRth", "tcEoPRtd")
387 ******************************************************************************
388 If .lFrom850
389 *--- update proc_855 flat of 850 transaction orders
390 llOKtoContinue = llOKtoContinue And .Update855FlagIn850TempIpoth("tcEoPRth",la855DataTbls[1])
391 Else
392
393 * Flag all orders as sent in local orders view
394 .AdvanceThermoTotalWithCaptionPlus("Flag all orders that will make to flatfile...")
395 llOKtoContinue=.FlagProcessOrders("tceoprTH", "VzzoordrH_Proc")
396 If !llOKtoContinue
397 Return llOKtoContinue
398 Endif
399 llOKtoContinue=.FlagProcessOrders("tceoprTH", "VzzoshprH_Proc")
400 If !llOKtoContinue
401 Return llOKtoContinue
402 Endif
403 Endif
404
405 *--- 36646 1/27/03 YIK
406 *- Cannot SELECT from interface (views!). Select from trans. tables instead
407
408 llOKtoContinue= llOKtoContinue And .PrepareCursorsForSDQ("tcEoPRth", "tcEoPRtd", "Q_TempSDQ", "zzeoPRcr")
409 *=== 36646
410
411 *--- TR 1030443 22-Apr-2008 Goutam
412 IF this.lFrom945
413 llOKtoContinue= llOKtoContinue And .PrepareCursorsForTRK("tcEoPRth", "tcTRK")
414 ENDIF
415 *=== TR 1030443 22-Apr-2008 Goutam
416
417 * 5. create Interface header/detail from good/Auto_proc="Y" 855 Transaction
418 .AdvanceThermoTotalWithCaptionPlus("Creating 855 Transaction tables...")
419 * TR 1074595 KISHORE 22-JAN-2014 added Store cursor , "Q_TempSDQ"
420 *-TR 1098611 FH - added datawhse
421ASSERT .f.
422 llOKtoContinue= llOKtoContinue And .CreateoPRInterface("tcEoPRth", "tcEoPRtd",;
423 "VzzeoPRih_Proc","VzzeoPRid_Proc", "VzzeoPRic_Proc", "VzzeoPRia_Proc", "Q_TempSDQ", "tceoprWH", "tceoprWD")
424 ******************************************************************************
425 * 6 update trans work header/detail to trans views for tableupdate
426 llOKtoContinue= llOKtoContinue And This.UpdateWorkTable("tcEoPRth", "VzzeoPRth_Proc")
427 llOKtoContinue= llOKtoContinue And This.UpdateWorkTable("tcEoPRtd", "VzzeoPRtd_Proc")
428 * also, have to remove record from transaction view to keep insync with work table
429 Delete From vzzeoPRtH_Proc Where pkey Not In (Select pkey From tcEoPRtH)
430 Delete From vzzeoPRtD_Proc Where pkey Not In (Select pkey From tcEoPRtD)
431
432 *--- TR 1083402 14-Jan-2015 Partha ---
433 IF !.lFrom850
434 llOKtoContinue = llOKtoContinue And .ValidateQuidsi("VzzeoPRih_Proc","VzzeoPRid_Proc", "zzeoprcr")
435 ENDIF
436 *=== TR 1083402 14-Jan-2015 Partha ===
437
438 *--TR 1031624 JUL-14-2008 BR
439 lnzzeoPRih_Reccount = .GetRecordCountWithAutoProc("vzzeoprIH_Proc")
440 If This.CountTotalRecs ("vzzeoprIH_Proc")>0 And lnzzeoPRih_Reccount >0
441 This.lHasShipment = .T.
442 Else
443 This.lHasShipment = .F.
444 Endif
445 *==TR 1031624 JUL-14-2008 BR
446
447 Else
448 ******************************************************************************
449 * For no records from base sql select. Still have to check for some lefover
450 * from previous process that may be on hold in interface and user change to
451 * auto process.
452 .cMessage = 'There are no orders to process.' + CRLF
453 .lNoDataFound = .T.
454 This.lHasShipment = .F. && TR 1031624 JUL-14-2008 BR
455 Endif
456
457 ******************************************************************************
458 * create Flat ASCII file for GenTran using Metadata
459 .AdvanceThermoTotalWithCaptionPlus("Creating 855 ASCII text file...")
460
461 ******************************************************************************
462 * create move all interface record to history views (not update to server)
463 If Recc("VzzeoPRih_Proc")>0
464 * TR 1074595 KISHORE 22-JAN-2014 added - , "VzzeopriSDQ_proc", "VzzeoprhSDQ_proc"
465 llOKtoContinue= llOKtoContinue And .CreateAutoProcessInterface("VzzeoPRih_Proc", "VzzeoPRid_Proc",;
466 "VzzeoPRic_Proc","VzzeoPRia_Proc", "VzzeoPRhh_proc", "VzzeoPRhd_proc",;
467 "VzzeoPRhc_proc","VzzeoPRha_proc", "VzzeopriSDQ_proc", "VzzeoprhSDQ_proc")
468 Endif
469
470 *--- 01/21/03 YIK Reshuffle Header, details and create SDQ table
471 *-llOKtoContinue= llOKtoContinue And .ConvertToSDQ("VzzeoPRih_Proc", "VzzeoPRid_Proc",;
472 *- "VzzeoPRic_Proc","VzzeoPRia_Proc", "tcFinalSDQ")
473 If This.lHasShipment && TR 1031624 JUL-14-2008 BR
474 ******************************************************************************
475 * Unload from interface tables to Flatfile
476 * ATS 2777- move EDI tree structure to a level lower by company
477 *lcEDIPath = AddBS(goEnv.envLoginTablePath.Value) + "EDI\Outbound\"
478 lcEDIPath = This.GetEDIFlatFileDirectory("Outbound")
479
480 lcOutBoundFlatFile = lcEDIPath + "855.dat"
481 *- 01/27/03 YIK
482 *--- TR 1010900 NH
483
484 *--- TR 1030443 22-MAy-2008 Goutam
485 *.Tie_setup = (goEnv.sv("TIE_SETUP","N") = 'Y')
486 This.Tie_setup = IIF(goEnv.sv("TIE_SETUP","N") = 'N' OR GoEnv.SV("BC_EDI_VERSION") = '5.2', .F., .T.)
487 *=== TR 1030443 22-MAy-2008 Goutam
488
489 *=== TR 1010900 NH
490
491 *--- TR 1030443 24-Apr-2008 Goutam. Added parameter tcTRK
492 *--- TR 1040825 11-Dec-2010 BNarayan use the table which contains the pass interface records
493 *llOKtoContinue= llOKtoContinue And .UnLoadToFlatFile("OPR" , lcOutBoundFlatFile, ;
494 "VzzeoPRih_Proc", "VzzeoPRid_Proc","VzzeoPRic_Proc","VzzeoPRia_Proc", ;
495 "tcFinalSDQ", "Q_TempSDQ", "tcItemSLN", "zzeoPRcr", "tcTRK")
496 *--- TechRec 1058716 11-Jan-2012 jisingh Added tceoprWH, tceoprWD ===
497 * TR 1074595 KISHORE 22-JAN-2014 Changed Q_TempSDQ to VzzeoPRiSDQ_Proc
498 *- TR 1098611 replace header/detail whse with Vzzeopriwhse_proc
499 llOKtoContinue= llOKtoContinue And .UnLoadToFlatFile("OPR" , lcOutBoundFlatFile, ;
500 "VzzeoPRhh_Proc", "VzzeoPRhd_Proc","VzzeoPRhc_Proc","VzzeoPRha_Proc", ;
501 "tcFinalSDQ", "VzzeoPRiSDQ_Proc", "tcItemSLN", "zzeoPRcr", "tcTRK", "Vzzeopriwhse_proc")
502 *=== TR 1040825 11-Dec-2010 BNarayan
503 *=== TR 1030443 24-Apr-2008 Goutam
504 Endif && TR 1031624 JUL-14-2008 BR
505
506 ******************************************************************************
507 * Remove all interface records with auto_proc='Y'
508 * tr 1074595 KISHORE 22-JAN-2014 Added ,"VzzeopriSDQ_Proc"
509 *- TR 1098611 FH - added Vzzeopriwhse_proc
510 llOKtoContinue= llOKtoContinue And .DeleteInterfaceRecords("VzzeoPRih_Proc", "VzzeoPRid_Proc",;
511 "VzzeoPRic_Proc","VzzeoPRia_Proc","VzzeopriSDQ_Proc", "Vzzeopriwhse_proc")
512 ******************************************************************************
513 * Tableupdate all views
514 .AdvanceThermoTotalWithCaptionPlus("Sending all 855 views to Server...")
515 If llOKtoContinue
516
517 * TR 1074595 KISHORE 22-JAN-2014 Increased array to 16 from 14
518 * TR 1098611 FH - array dim 16 to 17
519 Dime laTables[17]
520 laTables[1]= "VzzeoPRth_Proc"
521 laTables[2]= "VzzeoPRtd_Proc"
522 laTables[3]= "VzzeoPRih_Proc"
523 laTables[4]= "VzzeoPRid_Proc"
524 laTables[5]= "VzzeoPRic_Proc"
525 laTables[6]= "VzzeoPRia_Proc"
526 laTables[7]= "VzzeoPRhh_Proc"
527 laTables[8]= "VzzeoPRhd_Proc"
528 laTables[9]= "VzzeoPRhc_Proc"
529 laTables[10]= "VzzeoPRha_Proc"
530 laTables[11]= "Vzzoordrh_Proc"
531 laTables[12]= "Vzzoshprh_Proc"
532 laTables[13]= "VzzeoPRtc_Proc"
533 laTables[14]= "VzzeoPRta_Proc"
534 laTables[15]= "VzzeoPRisdq_Proc" && TR 1074595 KISHORE 22-JAN-2014
535 laTables[16]= "VzzeoPRhsdq_Proc" && TR 1074595 KISHORE 22-JAN-2014
536 laTables[17]= "Vzzeopriwhse_proc" && TR 1098611 FH
537
538 llOKtoContinue= llOKtoContinue And This.TableUpdateWithTransaction(@laTables)
539 Endif
540
541 *---TR 1031624 JUL-14-2008 BR
542 *- ADDED here to prevent flat file from being generated should TableUpdateWithTransaction fail
543 IF llOKToContinue AND .lHasShipment
544 SELECT __tcFlatFileList
545 SCAN
546 lcTempFlatfile = __tcFlatFileList.TempFlatfile
547 lcOutBoundFlatFile = __tcFlatFileList.OutBoundFlatFile
548 llOKtoContinue = llOKtoContinue AND this.CopyToFlatFileInBlock(lcTempFlatfile, lcOutBoundFlatFile)
549 ENDSCAN
550 ELSE
551 IF !llOKToContinue
552 lcLogStr = "TABLE UPDATE FAILED! Flat File not created."
553 This.LogEntry(lcLogStr)
554 ENDIF
555 ENDIF
556 *===TR 1031624 JUL-14-2008 BR
557
558 ******************************************************************************
559 * format return messages ONLY if sucessfully executed
560
561 Local lcMsg1,lcMsg2,lcMsg3,lcMsg4
562 Store "" To lcMsg1,lcMsg2,lcMsg3,lcMsg4
563 If llOKtoContinue
564 lcMsg1= Iif(.nRecordsToProcess>0, ;
565 ALLT(Str(.nRecordsToProcess)) + " Order(s) to Process." + CRLF ,"")
566 lcMsg2= Iif(.nInterfaceRecords<.nRecordsToProcess, ;
567 ALLT(Str(.nRecordsToProcess-.nInterfaceRecords)) + " Order(s) have Errors." + CRLF ,"")
568 lcMsg3= Iif(.nInterfaceRecords>0, ;
569 ALLT(Str(.nInterfaceRecords)) + " Order(s) successfully processed and moved to Interface table." + CRLF ,"")
570 lcMsg4= Iif(.nRecordsToProcess>0, ;
571 ALLT(Str(.nFlatfileRecords)) + " Order(s) moved from Interface table to Flatfile." + CRLF,"")
572 .cMessage= .cMessage + (lcMsg1+ lcMsg2+ lcMsg3+ lcMsg4)
573
574 *--- TR 1074595 KISHORE 27-JAN-2014
575 IF EMPTY(.cMessage) AND .nFlatFileRecords > 0
576 .cMessage = ALLTRIM(STR(.nFlatfileRecords)) + " Order(s) moved from Interface table to Flatfile."
577 ENDIF
578 *=== TR 1074595 KISHORE 27-JAN-2014
579 Endif
580 Else
581 .cMessage = "No orders to process"
582 Endif
583 .oLog.LogResult(llOKtoContinue)
584 IF NOT this.lFrom850 &&--- TR 1034481 NH
585 .oLog.CloseLog()
586 ENDIF &&--- TR 1034481 NH
587 Else
588 .cMessage = MSG_FILTER_ERROR + CRLF + MSG_TRY_AGAIN_NOW
589 Endif
590 *--- TR 1013007 NH
591 If Vartype(pcRef855Msg) = 'C'
592 pcRef855Msg = .cMessage
593 Endif
594 *=== TR 1013007 NH
595
596 Endwith
597
598 IF NOT this.lFrom850 &&--- TR 1034481 NH
599 This.ResetFrmProgressBar()
600 ENDIF &&--- TR 1034481 NH
601
602 * close/detroy all temp views
603 This.Cleanup()
604
605 *--- TR 1030443 24-Apr-2008 Goutam
606 This.TableClose("tcTRK")
607 This.TableClose("tcoPRmRK")
608 *=== TR 1030443 24-Apr-2008 Goutam
609
610 * and release Syslock
611 *--- TR 1044556 23-Feb-2010 Goutam
612 *llOKtoContinue= This.UnLockProcess("EDIoPRPROCESS")
613 llOKtoContinue= This.UnLockProcess("EDIOPRPROCESS")
614 *=== TR 1044556 23-Feb-2010 Goutam
615
616 Select(lnOldSelect)
617 Return llOKtoContinue
618 Endproc
619
620 ***********************************************************************************
621
622 Procedure GetAdditionalData
623 Lparameters pa855DataTbls
624
625 Local lcoprtd, llRetVal, lnOldSelect, lcSQL
626 lnOldSelect = Select()
627 llRetVal = .T.
628 lcoprtd = pa855DataTbls[2]
629
630 *--- get nrf_color
631 *zzxcolrr -> NRF_COLOR
632 lcSQL = "update d set NRF_COLOR = c.nrf_color from " + lcoprtd + " d join zzxcolrr c on d.color_code = c.color_code"
633 llRetVal = llRetVal And v_sqlexec(lcSQL)
634
635 *--- get NRF_SIZE
636 *zzxdimer -> NRF_SIZE
637 lcSQL = "update d set nrf_size = case " + ;
638 " when d.sizebucket = 1 then nrf_sz01 " + ;
639 " when d.sizebucket = 2 then nrf_sz02 " + ;
640 " when d.sizebucket = 3 then nrf_sz03 " + ;
641 " when d.sizebucket = 4 then nrf_sz04 " + ;
642 " when d.sizebucket = 5 then nrf_sz05 " + ;
643 " when d.sizebucket = 6 then nrf_sz06 " + ;
644 " when d.sizebucket = 7 then nrf_sz07 " + ;
645 " when d.sizebucket = 8 then nrf_sz08 " + ;
646 " when d.sizebucket = 9 then nrf_sz09 " + ;
647 " when d.sizebucket = 10 then nrf_sz10 " + ;
648 " when d.sizebucket = 11 then nrf_sz11 " + ;
649 " when d.sizebucket = 12 then nrf_sz12 " + ;
650 " when d.sizebucket = 13 then nrf_sz13 " + ;
651 " when d.sizebucket = 14 then nrf_sz14 " + ;
652 " when d.sizebucket = 15 then nrf_sz15 " + ;
653 " when d.sizebucket = 16 then nrf_sz16 " + ;
654 " when d.sizebucket = 17 then nrf_sz17 " + ;
655 " when d.sizebucket = 18 then nrf_sz18 " + ;
656 " when d.sizebucket = 19 then nrf_sz19 " + ;
657 " when d.sizebucket = 20 then nrf_sz20 " + ;
658 " when d.sizebucket = 21 then nrf_sz21 " + ;
659 " when d.sizebucket = 22 then nrf_sz22 " + ;
660 " when d.sizebucket = 23 then nrf_sz23 " + ;
661 " when d.sizebucket = 24 then nrf_sz24 " + ;
662 " else '' end " + ;
663 " from " + lcoprtd + " d " + ;
664 " join zzxscolr c on d.division = c.division and d.style = c.style and " +;
665 " d.color_code = c.color_code and d.lbl_code = c.lbl_code and d.dimension = c.dimension " + ;
666 " join zzxdimer m on d.division = m.division and c.size_code = m.size_code and d.dimension = m.dimension "
667
668 llRetVal = llRetVal And v_sqlexec(lcSQL)
669
670 *--- get style_name
671 *--- TR 1063591 10/11/12 ATHIRUNAVU Added division condition to resolve correct style name.
672 *lcSQL = "update d set style_name = s.style_name from " + lcoprtd + " d join zzxstylr s on d.style = s.style"
673 lcSQL = "update d set style_name = s.style_name from " + lcoprtd + " d join zzxstylr s on d.division = s.division AND d.style = s.style"
674 llRetVal = llRetVal And v_sqlexec(lcSQL)
675
676 *--- TR 1016825 05/02/06 NH - updating ord_date from po_date.
677 lcSQL = "UPDATE " + pa855DataTbls[1] + " SET ord_date = po_date WHERE ord_date = {ts '1900-01-01 00:00:00'} and po_date > {ts '1900-01-01 00:00:00'} "
678 llRetVal = llRetVal And v_sqlexec(lcSQL)
679 *=== TR 1016825 05/02/06 NH
680 Select(lnOldSelect)
681
682 Endproc
683
684 ************************************************************************************
685 * Create all views for 855 process
686 * Parameter:
687 * 1- passing additional filter criteria(s).
688 * If not passing anything then process orders based on 855(o) control table flag
689 * Return: True= if something to process, False= Error open views or no record to process
690 *
691 ************************************************************************************
692 Procedure CreateOPRViews
693 Parameters pcFilterCriteria
694 Local llRetVal, lcSQLSelect, lcSQLOrder, lcSQLSelect_O, lcSQLSelect_P, lcSQLSelect_I, lcSQLSelect_C, ;
695 lcExtraCond, lcDateDiff, lcHDR_CR_join
696
697 *--- TR 1030443 16-Jun-2008 Goutam
698 LOCAL lcSql, lnOrd_Num
699
700 llRetVal = .T.
701
702 l_nThermoCnt= 0
703 This.InitThermoWithCaption(@l_nThermoCnt, 18, "Creating all views to support 855 process...")
704 This.oLog.LogEntry("Creating all views to support 855 process...")
705 * all pick orders headers for matching criteria
706 This.AdvanceThermoPlus(@l_nThermoCnt)
707
708 * all order headers (Ord & Shp) for matching criteria
709 lcSQLOrder = " h.customer, h.division, h.ord_num "
710
711 *--- TR 1030443 18-Jun-2008 Goutam
712 *lcHDR_CR_join = " h.customer=r.customer and h.division=r.division and r.active_ok = 'Y' and h.proc_855 = ' ' "
713 IF this.lFrom945
714 lcHDR_CR_join = " h.customer=r.customer and h.division=r.division and r.active_ok = 'Y' "
715 ELSE
716 lcHDR_CR_join = " h.customer=r.customer and h.division=r.division and r.active_ok = 'Y' and h.proc_855 = ' ' "
717 endif
718 *=== TR 1030443 18-Jun-2008 Goutam
719
720 *- add codition to take only orders entered not more r.num_days ago.
721 *- lnExtraCond = ' and ' ????
722 If "SQL"$goEnv.sv("cServerType", "SQL")
723 lcDateDiff = " GetDate() - r.num_days "
724 Else
725 lcDateDiff = SQLfnNow() + " - r.num_days DAYS "
726 Endif
727
728 *--- TR 1030443 16-Jun-2008 Goutam
729 IF this.lFrom945
730 lcSql = "Select top 1 ord_num from zzoshprh h " + pcFilterCriteria
731 llRetVal = llRetVal and v_sqlexec(lcSQL, "lcCurOrdNum")
732 IF llRetVal
733 lnOrd_Num = lcCurOrdNum.ord_num
734 USE IN lcCurOrdNum
735 ENDIF
736 ENDIF
737 *=== TR 1030443 16-Jun-2008 Goutam
738
739 *--- TechRec 1065807 17-Jan-2013 vkrishnamurthy ---
740 LOCAL lcHdrFldList
741 lcHdrFldList = GetCommonFieldListWithAlias("h.", "zzoordrh", "zzeoprth")
742 *=== TechRec 1065807 17-Jan-2013 vkrishnamurthy ===
743
744 *--- TR 1030443 23-Apr-2008 Goutam
745 IF NOT this.lFrom945
746 *=== TR 1030443 23-Apr-2008 Goutam
747
748 *--- TechRec 1065807 17-Jan-2013 vkrishnamurthy ---
749*!* *- Open
750*!* lcSQLSelect_O = "select h.*, r.num_days from zzoordrh h, zzeoPRcr r where " + ;
751*!* lcHDR_CR_join + " and h.ent_date > " + lcDateDiff + ;
752*!* " and r.incl_open = 'Y' and h.ord_status = 'O' " + ;
753*!* pcFilterCriteria && additional criteria when run 855 process
754*!* *- pick
755*!* lcSQLSelect_P = "select h.*, r.num_days from zzoordrh h, zzeoPRcr r where " + ;
756*!* lcHDR_CR_join + " and h.ent_date > " + lcDateDiff + ;
757*!* " and r.incl_pick = 'Y' and h.ord_status = 'P' " + ;
758*!* pcFilterCriteria
759*!* *- cancelled
760*!* lcSQLSelect_C = "select h.*, r.num_days from zzoordrh h, zzeoPRcr r where " + ;
761*!* lcHDR_CR_join + " and h.ent_date > " + lcDateDiff + ;
762*!* " and r.incl_canc = 'Y' and h.ord_status = 'C' " + ;
763*!* pcFilterCriteria
764*!* *- invoiced. all records in zzoshprh have status 'I', but check just for consistency
765*!* lcSQLSelect_I = "select h.*, r.num_days from zzoshprh h, zzeoPRcr r where " + ;
766*!* lcHDR_CR_join + " and h.ent_date > " + lcDateDiff + ;
767*!* " and r.incl_inv = 'Y' and h.ord_status = 'I' " + ;
768*!* pcFilterCriteria
769
770 *- Open
771 lcSQLSelect_O = "select "+ lcHdrFldList + ", r.num_days from zzoordrh h, zzeoPRcr r where " + ;
772 lcHDR_CR_join + " and h.ent_date > " + lcDateDiff + ;
773 " and r.incl_open = 'Y' and h.ord_status = 'O' " + ;
774 pcFilterCriteria && additional criteria when run 855 process
775 *- pick
776 lcSQLSelect_P = "select "+ lcHdrFldList + ", r.num_days from zzoordrh h, zzeoPRcr r where " + ;
777 lcHDR_CR_join + " and h.ent_date > " + lcDateDiff + ;
778 " and r.incl_pick = 'Y' and h.ord_status = 'P' " + ;
779 pcFilterCriteria
780 *- cancelled
781 lcSQLSelect_C ="select "+ lcHdrFldList + ", r.num_days from zzoordrh h, zzeoPRcr r where " + ;
782 lcHDR_CR_join + " and h.ent_date > " + lcDateDiff + ;
783 " and r.incl_canc = 'Y' and h.ord_status = 'C' " + ;
784 pcFilterCriteria
785 *- invoiced. all records in zzoshprh have status 'I', but check just for consistency
786 lcSQLSelect_I = "select "+ lcHdrFldList + ", r.num_days from zzoshprh h, zzeoPRcr r where " + ;
787 lcHDR_CR_join + " and h.ent_date > " + lcDateDiff + ;
788 " and r.incl_inv = 'Y' and h.ord_status = 'I' " + ;
789 pcFilterCriteria
790 *=== TechRec 1065807 17-Jan-2013 vkrishnamurthy ===
791
792 *--- TR 1030443 23-Apr-2008 Goutam
793 ELSE
794
795 * --- TR 1034147 NSD 6/18/08
796 * If the process is being called from the 945, then my filter criteria is the list of pick#s.
797 * This only applies to the invoice table because there can only be 1 invoice per pick ticket.
798 * For the cancel, open, and pick queries, it should pull off the order# of the pick ticket.
799
800 *--- TechRec 1065807 17-Jan-2013 vkrishnamurthy ---
801*!* *- Open
802*!* lcSQLSelect_O = "select h.*, r.num_days from zzoordrh h join zzeoPRcr r on " + ;
803*!* lcHDR_CR_join + " where h.ent_date > " + lcDateDiff + ;
804*!* " and r.incl_open = 'Y' and h.ord_status = 'O' and h.ord_num = " + sqlformatnum(lnOrd_Num)
805
806*!* *- pick
807*!* lcSQLSelect_P = "select h.*, r.num_days from zzoordrh h join zzeoPRcr r on " + ;
808*!* lcHDR_CR_join + " where h.ent_date > " + lcDateDiff + ;
809*!* " and r.incl_pick = 'Y' and h.ord_status = 'P' and h.ord_num = " + sqlformatnum(lnOrd_Num)
810*!* *- cancelled
811*!* lcSQLSelect_C = "select h.*, r.num_days from zzoordrh h join zzeoPRcr r on " + ;
812*!* lcHDR_CR_join + " where h.ent_date > " + lcDateDiff + ;
813*!* " and r.incl_canc = 'Y' and h.ord_status = 'C' and h.ord_num = " + sqlformatnum(lnOrd_Num)
814*!*
815*!* *- invoiced. all records in zzoshprh have status 'I', but check just for consistency
816*!* lcSQLSelect_I = "select h.*, r.num_days from zzoshprh h join zzeoPRcr r on " + ;
817*!* lcHDR_CR_join + pcFilterCriteria + " where h.ent_date > " + lcDateDiff + ;
818*!* " and r.incl_inv = 'Y' and h.ord_status = 'I' "
819
820 *- Open
821 lcSQLSelect_O ="select "+ lcHdrFldList + ", r.num_days from zzoordrh h join zzeoPRcr r on " + ;
822 lcHDR_CR_join + " where h.ent_date > " + lcDateDiff + ;
823 " and r.incl_open = 'Y' and h.ord_status = 'O' and h.ord_num = " + sqlformatnum(lnOrd_Num)
824
825 *- pick
826 lcSQLSelect_P = "select "+ lcHdrFldList + ", r.num_days from zzoordrh h join zzeoPRcr r on " + ;
827 lcHDR_CR_join + " where h.ent_date > " + lcDateDiff + ;
828 " and r.incl_pick = 'Y' and h.ord_status = 'P' and h.ord_num = " + sqlformatnum(lnOrd_Num)
829 *- cancelled
830 lcSQLSelect_C = "select "+ lcHdrFldList + ", r.num_days from zzoordrh h join zzeoPRcr r on " + ;
831 lcHDR_CR_join + " where h.ent_date > " + lcDateDiff + ;
832 " and r.incl_canc = 'Y' and h.ord_status = 'C' and h.ord_num = " + sqlformatnum(lnOrd_Num)
833
834 *- invoiced. all records in zzoshprh have status 'I', but check just for consistency
835 lcSQLSelect_I = "select "+ lcHdrFldList + ", r.num_days from zzoshprh h join zzeoPRcr r on " + ;
836 lcHDR_CR_join + pcFilterCriteria + " where h.ent_date > " + lcDateDiff + ;
837 " and r.incl_inv = 'Y' and h.ord_status = 'I' "
838 *=== TechRec 1065807 17-Jan-2013 vkrishnamurthy ===
839 ENDIF
840 *=== TR 1030443 23-Apr-2008 Goutam
841
842 lcSQLSelect= lcSQLSelect_O + " Union All " + lcSQLSelect_P + ;
843 " Union All " + lcSQLSelect_C
844
845 llRetVal=This.CreateSQLView("Vzzoordrh_Proc", lcSQLSelect,, lcSQLOrder)
846
847 lcSQLSelect= lcSQLSelect_I
848 llRetVal = llRetVal And This.CreateSQLView("Vzzoshprh_Proc", lcSQLSelect,, lcSQLOrder)
849
850 * exit if error
851 If !llRetVal
852 Return llRetVal
853 Endif
854
855 * all order details (Ord & Shp) for matching criteria
856
857 *--- TR 1030443 23-Apr-2008 Goutam
858 IF NOT this.lFrom945
859 *=== TR 1030443 23-Apr-2008 Goutam
860
861 lcSQLSelect_O= "select d.*, h.ship_date from zzoordrd d, zzoordrh h where h.pkey=d.fkey and fkey in (" +;
862 "select h.pkey from zzoordrh h, zzeoPRcr r where " + ;
863 lcHDR_CR_join + " and h.ent_date > " + lcDateDiff + ;
864 " and r.incl_open = 'Y' and h.ord_status = 'O' " + ;
865 pcFilterCriteria + ")"
866
867 lcSQLSelect_P = "select d.*, h.ship_date from zzoordrd d, zzoordrh h where h.pkey=d.fkey and fkey in (" +;
868 "select h.pkey from zzoordrh h, zzeoPRcr r where " + ;
869 lcHDR_CR_join + " and h.ent_date > " + lcDateDiff + ;
870 " and r.incl_pick = 'Y' and h.ord_status = 'P' " + ;
871 pcFilterCriteria + ")"
872
873 lcSQLSelect_C= "select d.*, h.ship_date from zzoordrd d, zzoordrh h where h.pkey=d.fkey and fkey in (" +;
874 "select h.pkey from zzoordrh h, zzeoPRcr r where " + ;
875 lcHDR_CR_join + " and h.ent_date > " + lcDateDiff + ;
876 " and r.incl_canc = 'Y' and h.ord_status = 'C' " + ;
877 pcFilterCriteria + ")"
878
879 lcSQLSelect_I= "select d.*, h.ship_date from zzoshprd d, zzoshprh h where h.pkey=d.fkey and fkey in (" +;
880 "select h.pkey from zzoshprh h, zzeoPRcr r where " + ;
881 lcHDR_CR_join + " and h.ent_date > " + lcDateDiff + ;
882 " and r.incl_inv = 'Y' and h.ord_status = 'I' " + ;
883 pcFilterCriteria + ")"
884
885 *--- TR 1030443 23-Apr-2008 Goutam
886 ELSE
887
888
889 * --- TR 1034147 NSD 6/18/08
890 * If the process is being called from the 945, then my filter criteria is the list of pick#s.
891 * This only applies to the invoice table because there can only be 1 invoice per pick ticket.
892 * For the cancel, open, and pick queries, it should pull off the order# of the pick ticket.
893
894 lcSQLSelect_O= "select d.*, h.ship_date from zzoordrd d, zzoordrh h where h.pkey=d.fkey and fkey in (" +;
895 "select h.pkey from zzoordrh h join zzeoPRcr r on " + ;
896 lcHDR_CR_join + " where h.ent_date > " + lcDateDiff + ;
897 " and r.incl_open = 'Y' and h.ord_status = 'O' and h.ord_num = " + sqlformatnum(lnOrd_Num) + ")"
898
899 lcSQLSelect_P = "select d.*, h.ship_date from zzoordrd d, zzoordrh h where h.pkey=d.fkey and fkey in (" +;
900 "select h.pkey from zzoordrh h join zzeoPRcr r on " + ;
901 lcHDR_CR_join + " where h.ent_date > " + lcDateDiff + ;
902 " and r.incl_pick = 'Y' and h.ord_status = 'P' and h.ord_num = " + sqlformatnum(lnOrd_Num) + ")"
903
904 lcSQLSelect_C= "select d.*, h.ship_date from zzoordrd d, zzoordrh h where h.pkey=d.fkey and fkey in (" +;
905 "select h.pkey from zzoordrh h join zzeoPRcr r on " + ;
906 lcHDR_CR_join + " where h.ent_date > " + lcDateDiff + ;
907 " and r.incl_canc = 'Y' and h.ord_status = 'C' and h.ord_num = " + sqlformatnum(lnOrd_Num) + ")"
908
909 lcSQLSelect_I= "select d.*, h.ship_date from zzoshprd d, zzoshprh h where h.pkey=d.fkey and fkey in (" +;
910 "select h.pkey from zzoshprh h join zzeoPRcr r on " + ;
911 lcHDR_CR_join + pcFilterCriteria + " where h.ent_date > " + lcDateDiff + ;
912 " and r.incl_inv = 'Y' and h.ord_status = 'I')"
913 ENDIF
914 *=== TR 1030443 23-Apr-2008 Goutam
915
916 lcSQLSelect= lcSQLSelect_O + " Union All " + lcSQLSelect_P + ;
917 " Union All " + lcSQLSelect_C
918
919 llRetVal= This.CreateSQLView("Vzzoordrd_Proc", lcSQLSelect)
920 lcSQLSelect= lcSQLSelect_I
921 llRetVal= llRetVal And This.CreateSQLView("Vzzoshprd_Proc", lcSQLSelect)
922 * exit if error
923 If !llRetVal
924 Return llRetVal
925 Endif
926 * create EDI 855 Transaction tables
927 This.AdvanceThermoPlus(@l_nThermoCnt)
928 lcSQLSelect = "select * from zzeoPRth"
929 This.CreateSQLView("VzzeoPRth_Proc", lcSQLSelect)
930 This.AdvanceThermoPlus(@l_nThermoCnt)
931 lcSQLSelect = "select * from zzeoPRtd"
932 This.CreateSQLView("VzzeoPRtd_Proc", lcSQLSelect)
933 This.AdvanceThermoPlus(@l_nThermoCnt)
934 lcSQLSelect = "select * from zzeoPRtc"
935 This.CreateSQLView("VzzeoPRtc_Proc", lcSQLSelect)
936 This.AdvanceThermoPlus(@l_nThermoCnt)
937 lcSQLSelect = "select * from zzeoPRta"
938 This.CreateSQLView("VzzeoPRta_Proc", lcSQLSelect)
939
940 * create EDI 855 Interface tables
941 This.AdvanceThermoPlus(@l_nThermoCnt)
942 lcSQLSelect = "select * from zzeoPRih"
943 This.CreateSQLView("VzzeoPRih_Proc", lcSQLSelect)
944 This.AdvanceThermoPlus(@l_nThermoCnt)
945 lcSQLSelect = "select * from zzeoPRid"
946 This.CreateSQLView("VzzeoPRid_Proc", lcSQLSelect)
947 This.AdvanceThermoPlus(@l_nThermoCnt)
948 lcSQLSelect = "select * from zzeoPRic"
949 This.CreateSQLView("VzzeoPRic_Proc", lcSQLSelect)
950 This.AdvanceThermoPlus(@l_nThermoCnt)
951 lcSQLSelect = "select * from zzeoPRia"
952 This.CreateSQLView("VzzeoPRia_Proc", lcSQLSelect)
953
954 * create EDI 855 Interface history tables (NOT real history table on server
955 * only dummy views {same table as interface} to store all pass interface
956 * records with auto_proc="Y")
957 * NEVER tableupdate these views ????????????????????????????? YIK
958 This.AdvanceThermoPlus(@l_nThermoCnt)
959 lcSQLSelect = "select * from zzeoPRhh"
960 This.CreateSQLView("VzzeoPRhh_Proc", lcSQLSelect)
961 This.AdvanceThermoPlus(@l_nThermoCnt)
962 lcSQLSelect = "select * from zzeoPRhd"
963 This.CreateSQLView("VzzeoPRhd_Proc", lcSQLSelect)
964 This.AdvanceThermoPlus(@l_nThermoCnt)
965 lcSQLSelect = "select * from zzeoPRhc"
966 This.CreateSQLView("VzzeoPRhc_Proc", lcSQLSelect)
967 This.AdvanceThermoPlus(@l_nThermoCnt)
968 lcSQLSelect = "select * from zzeoPRha"
969 This.CreateSQLView("VzzeoPRha_Proc", lcSQLSelect)
970
971 *--- TR 1074595 KISHORE 22-JAN-2014
972 lcSQLSelect = "SELECT * FROM zzeopriSDQ"
973 This.CreateSQLView("VzzeopriSDQ_Proc", lcSQLSelect)
974 lcSQLSelect = "SELECT * FROM zzeoprhSDQ"
975 This.CreateSQLView("VzzeoprhSDQ_Proc", lcSQLSelect)
976 *=== TR 1074595 KISHORE 22-JAN-2014
977
978 *- TR 1098611 FH
979 lcSQLSelect = "SELECT * FROM zzeopriwhse"
980 This.CreateSQLView("Vzzeopriwhse_proc", lcSQLSelect)
981 *- TR 1098611 FH
982
983 Return llRetVal
984 Endproc
985
986 *--- TR 1013007 NH 2005-10-14
987 ************************************************************************************
988
989 Procedure CreateOPRViewsFrom850
990 Lparameters paFilterTbl
991
992 *--- TR 1077093 25-Mar-2014 SMeenraja Added variable lcHdrFldList
993 Local llRetVal, lcSQLSelect, lcSQLOrder, lcSQLSelect_O, lcSQLSelect_P, lcSQLSelect_I, lcSQLSelect_C, ;
994 lcExtraCond, lcDateDiff, lcHDR_CR_join, lcHdrFldList
995 llRetVal = .T.
996
997 *--- first move data from temp tables to 855 transaction table
998
999 llRetVal = llRetVal And This.OPRTmpToOPRTran(@paFilterTbl)
1000
1001 l_nThermoCnt= 0
1002 This.InitThermoWithCaption(@l_nThermoCnt, 18, "Creating all views to support 855 process...")
1003 This.oLog.LogEntry("Creating all views to support 855 process...")
1004 * all pick orders headers for matching criteria
1005 This.AdvanceThermoPlus(@l_nThermoCnt)
1006
1007 * all order headers (Ord & Shp) for matching criteria
1008 lcSQLOrder = " h.customer, h.division, h.ord_num "
1009
1010 lcHDR_CR_join = " h.customer=r.customer and h.division=r.division and r.active_ok = 'Y' and h.proc_855 = ' ' "
1011
1012 *--- TR 1077093 25-Mar-2014 SMeenraja Used common fields only in SQL to avoid too many columns issue
1013 lcHdrFldList = GetCommonFieldListWithAlias("h.", "zzoordrh", "zzeoprth")
1014
1015 *--- No need to bring any data from Sales order views as 850 put all necessary data into 855 transaction table
1016*!* lcSQLSelect = "select h.*, r.num_days from zzoordrh h, zzeoPRcr r where " + lcHDR_CR_join + " and 1 = 0 "
1017 lcSQLSelect = "select "+ lcHdrFldList + ", r.num_days from zzoordrh h, zzeoPRcr r where " + lcHDR_CR_join + " and 1 = 0 "
1018 llRetVal = llRetVal And This.CreateSQLView("Vzzoordrh_Proc", lcSQLSelect,,lcSQLOrder)
1019*!* lcSQLSelect = "select h.*, r.num_days from zzoshprh h, zzeoPRcr r where " + lcHDR_CR_join + " and 1 = 0 "
1020 lcSQLSelect = "select " + lcHdrFldList + ", r.num_days from zzoshprh h, zzeoPRcr r where " + lcHDR_CR_join + " and 1 = 0 "
1021 *=== TR 1077093 25-Mar-2014 SMeenraja Used common fields only in SQL to avoid too many columns issue
1022 llRetVal = llRetVal And This.CreateSQLView("Vzzoshprh_Proc", lcSQLSelect,,lcSQLOrder)
1023
1024 lcSQLSelect = "select d.*, h.ship_date from zzoordrd d JOIN zzoordrh h ON h.pkey = d.fkey where 1 = 0 "
1025 llRetVal = llRetVal And This.CreateSQLView("Vzzoordrd_Proc", lcSQLSelect,,lcSQLOrder)
1026 lcSQLSelect = "select d.*, h.ship_date from zzoshprd d JOIN zzoshprh h ON h.pkey = d.fkey where 1 = 0 "
1027 llRetVal = llRetVal And This.CreateSQLView("Vzzoshprd_Proc", lcSQLSelect,,lcSQLOrder)
1028 * exit if error
1029 If !llRetVal
1030 Return llRetVal
1031 Endif
1032
1033 * create EDI 855 Transaction tables from 850 processs
1034 *--- filter all for pcFilterTbl
1035 This.AdvanceThermoPlus(@l_nThermoCnt)
1036 lcSQLSelect = "select h.* from zzeoPRth h "
1037 This.CreateSQLView("VzzeoPRth_Proc", lcSQLSelect)
1038 This.AdvanceThermoPlus(@l_nThermoCnt)
1039 lcSQLSelect = "select d.* from zzeoPRtd d "
1040 This.CreateSQLView("VzzeoPRtd_Proc", lcSQLSelect)
1041 This.AdvanceThermoPlus(@l_nThermoCnt)
1042 lcSQLSelect = "select c.* from zzeoPRtc c "
1043 This.CreateSQLView("VzzeoPRtc_Proc", lcSQLSelect)
1044 This.AdvanceThermoPlus(@l_nThermoCnt)
1045 lcSQLSelect = "select a.* from zzeoPRta a "
1046 This.CreateSQLView("VzzeoPRta_Proc", lcSQLSelect)
1047
1048 * create EDI 855 Interface tables
1049 *--- no need to bring data from interface tables.
1050 *--- all data is coming from 850 process
1051 This.AdvanceThermoPlus(@l_nThermoCnt)
1052 lcSQLSelect = "select * from zzeoPRih WHERE 1 = 0 "
1053 This.CreateSQLView("VzzeoPRih_Proc", lcSQLSelect)
1054 This.AdvanceThermoPlus(@l_nThermoCnt)
1055 lcSQLSelect = "select * from zzeoPRid WHERE 1 = 0 "
1056 This.CreateSQLView("VzzeoPRid_Proc", lcSQLSelect)
1057 This.AdvanceThermoPlus(@l_nThermoCnt)
1058 lcSQLSelect = "select * from zzeoPRic WHERE 1 = 0 "
1059 This.CreateSQLView("VzzeoPRic_Proc", lcSQLSelect)
1060 This.AdvanceThermoPlus(@l_nThermoCnt)
1061 lcSQLSelect = "select * from zzeoPRia WHERE 1 = 0 "
1062 This.CreateSQLView("VzzeoPRia_Proc", lcSQLSelect)
1063
1064 * create EDI 855 Interface history tables (NOT real history table on server
1065 * only dummy views {same table as interface} to store all pass interface
1066 * records with auto_proc="Y")
1067 This.AdvanceThermoPlus(@l_nThermoCnt)
1068 lcSQLSelect = "select * from zzeoPRhh "
1069 This.CreateSQLView("VzzeoPRhh_Proc", lcSQLSelect)
1070 This.AdvanceThermoPlus(@l_nThermoCnt)
1071 lcSQLSelect = "select * from zzeoPRhd "
1072 This.CreateSQLView("VzzeoPRhd_Proc", lcSQLSelect)
1073 This.AdvanceThermoPlus(@l_nThermoCnt)
1074 lcSQLSelect = "select * from zzeoPRhc "
1075 This.CreateSQLView("VzzeoPRhc_Proc", lcSQLSelect)
1076 This.AdvanceThermoPlus(@l_nThermoCnt)
1077 lcSQLSelect = "select * from zzeoPRha "
1078 This.CreateSQLView("VzzeoPRha_Proc", lcSQLSelect)
1079
1080 *--- TR 1074595 KISHORE 5-FEB-2014
1081 lcSQLSelect = "SELECT * FROM zzeopriSDQ"
1082 This.CreateSQLView("VzzeopriSDQ_Proc", lcSQLSelect)
1083 lcSQLSelect = "SELECT * FROM zzeoprhSDQ"
1084 This.CreateSQLView("VzzeoprhSDQ_Proc", lcSQLSelect)
1085 *=== TR 1074595 KISHORE 5-FEB-2014
1086
1087 *- TR 1098611 FH
1088 lcSQLSelect = "SELECT * FROM zzeopriwhse"
1089 This.CreateSQLView("Vzzeopriwhse_proc", lcSQLSelect)
1090 *- TR 1098611 FH
1091
1092 Return llRetVal
1093 Endproc
1094
1095 *************************************************************************************
1096
1097 Procedure OPRTmpToOPRTran(paFilterTbls)
1098
1099 Local lcopr_tmp
1100 Local llRetVal, lnOldSelect
1101 lnOldSelect = Select()
1102 llRetVal = .T.
1103 llRetVal = llRetVal And Not Empty(paFilterTbls) And Alen(paFilterTbls) >= 6
1104 This.oLog.LogEntry("Starting " + Program())
1105
1106 If llRetVal And This.BeginTransaction()
1107 *--- clear old data from trnsaction tables
1108 lcSQL = "DELETE zzeoprtd"
1109 llRetVal = llRetVal And v_sqlexec(lcSQL)
1110 This.oLog.LogEntry("Deleting all data from zzeoprtd " + Iif(llRetVal,"completed.","failed."))
1111
1112 lcSQL = "DELETE zzeoprtc"
1113 llRetVal = llRetVal And v_sqlexec(lcSQL)
1114 This.oLog.LogEntry("Deleting all data from zzeoprtc " + Iif(llRetVal,"completed.","failed."))
1115
1116 lcSQL = "DELETE zzeoprta"
1117 llRetVal = llRetVal And v_sqlexec(lcSQL)
1118 This.oLog.LogEntry("Deleting all data from zzeoprta " + Iif(llRetVal,"completed.","failed."))
1119
1120 lcSQL = "DELETE zzeoprth"
1121 llRetVal = llRetVal And v_sqlexec(lcSQL)
1122 This.oLog.LogEntry("Deleting all data from zzeoprth " + Iif(llRetVal,"completed.","failed."))
1123
1124 lcSQL = "DELETE sysnotes where table_name = 'ZZEOPRTH' "
1125 llRetVal = llRetVal And v_sqlexec(lcSQL)
1126 This.oLog.LogEntry("Deleting all data from sysnotes for table zzeoprth " + Iif(llRetVal,"completed.","failed."))
1127
1128 lcSQL = "DELETE sysnotes where table_name = 'ZZEOPRTD' "
1129 llRetVal = llRetVal And v_sqlexec(lcSQL)
1130 This.oLog.LogEntry("Deleting all data from sysnotes for table zzeoprtd " + Iif(llRetVal,"completed.","failed."))
1131
1132 *--- move from 855 temp tables to 855 transaction tables
1133
1134 *lcopr_tmp = "#zzeoprth"
1135 lcopr_tmp = paFilterTbls[1]
1136
1137 lcSQL = "INSERT INTO zzeoprth select * from " + lcopr_tmp
1138 llRetVal = llRetVal And v_sqlexec(lcSQL)
1139
1140 *lcopr_tmp = "#zzeoprtd"
1141 lcopr_tmp = paFilterTbls[2]
1142 lcSQL = "INSERT INTO zzeoprtd select * from " + lcopr_tmp
1143 llRetVal = llRetVal And v_sqlexec(lcSQL)
1144
1145 *lcopr_tmp = "#zzeoprtc"
1146 lcopr_tmp = paFilterTbls[3]
1147 lcSQL = "INSERT INTO zzeoprtc select * from " + lcopr_tmp
1148 llRetVal = llRetVal And v_sqlexec(lcSQL)
1149
1150 *lcopr_tmp = "#zzeoprta"
1151 lcopr_tmp = paFilterTbls[4]
1152 lcSQL = "INSERT INTO zzeoprta select * from " + lcopr_tmp
1153 llRetVal = llRetVal And v_sqlexec(lcSQL)
1154
1155
1156 *lcopr_tmp = "#sysnotes_oprth"
1157 lcopr_tmp = paFilterTbls[5]
1158 lcSQL = "INSERT INTO sysnotes select * from " + lcopr_tmp
1159 llRetVal = llRetVal And v_sqlexec(lcSQL)
1160
1161 *lcopr_tmp = "#sysnotes_oprtd"
1162 lcopr_tmp = paFilterTbls[6]
1163 lcSQL = "INSERT INTO sysnotes select * from " + lcopr_tmp
1164 llRetVal = llRetVal And v_sqlexec(lcSQL)
1165
1166 If llRetVal
1167 This.EndTransaction()
1168 Else
1169 This.RollbackTransaction()
1170 Endif
1171 Endif
1172
1173 Select(lnOldSelect)
1174 Return llRetVal
1175
1176 Endproc
1177
1178
1179 *=== TR 1013007 NH 2005-10-14
1180
1181 ************************************************************************************
1182 * Open all 855 views
1183 ************************************************************************************
1184 Procedure OpenoPRViews
1185 Parameters pcFilterCriteria
1186 Local llRetVal, lcSQLSelect, lcSQLOrder
1187 llRetVal = .T.
1188 This.oLog.LogEntry("Opening views")
1189
1190 lnOldSelect = Select()
1191 * Open order header/detail with data, transaction header/detail with data
1192 * and interface table with data, and history table (view from inter tables)
1193 * use these views to move records for create flatfile function
1194 lnCursorBuffer = DB_BUFOPTRECORD
1195 If !(This.OpenTable("Vzzoordrh_Proc",,.T.) And ;
1196 This.OpenTable("Vzzoordrd_Proc",,.T.) And ;
1197 This.OpenTable("Vzzoshprh_Proc",,.T.) And ;
1198 This.OpenTable("Vzzoshprd_Proc",,.T.) And ;
1199 This.OpenTable("VzzeoPRth_Proc",,.T.) And ;
1200 This.OpenTable("VzzeoPRtd_Proc",,.T.) And ;
1201 This.OpenTable("VzzeoPRtc_Proc",,.T.) And ;
1202 This.OpenTable("VzzeoPRta_Proc",,.T.) And ;
1203 This.OpenTable("VzzeoPRih_Proc",,.T.) And ;
1204 This.OpenTable("VzzeoPRia_Proc",,.T.) And ;
1205 This.OpenTable("VzzeoPRic_Proc",,.T.) And ;
1206 This.OpenTable("VzzeoPRid_Proc",,.T.) And ;
1207 This.OpenTable("VzzeoPRhh_Proc") And ;
1208 This.OpenTable("VzzeoPRha_Proc") And ;
1209 This.OpenTable("VzzeoPRhc_Proc") And ;
1210 This.OpenTable("VzzeoPRhd_Proc") AND ;
1211 This.OpenTable("Vzzeopriwhse_proc",,.T.) ; && 1098611 FH
1212 )
1213 llRetVal= .F.
1214 Endif
1215
1216 *--- TR 1074595 KISHORE 22-JAN-2014
1217 IF llRetVal AND NOT (This.OpenTable("VzzeoPRisdq_Proc",,.T.) AND ;
1218 This.OpenTable("VzzeoPrhsdq_Proc",,.T.))
1219 llRetVal = .f.
1220 ENDIF
1221 *=== TR 1074595 KISHORE 22-JAN-2014
1222
1223 * Query for all control locally (--need to join with
1224 * local cursor lots to get flags/parameters for the process)
1225 lcSQLString = "SELECT * FROM zzeoPRCR"
1226 llRetVal = llRetVal And v_SqlPrep(lcSQLString, "zzeoPRcr")
1227 If llRetVal
1228 *--TR 1031624 JUL-14-2008 BR
1229 Select vzzeoprIH_Proc
1230 CursorSetProp("BUFFERING", 3)
1231 Index On auto_proc Tag auto_proc
1232 *==TR 1031624 JUL-14-2008 BR
1233
1234 Select zzeoPRcr
1235 Index On customer Tag customer
1236 Index On division Tag division
1237 Endif
1238
1239 Select(lnOldSelect)
1240 Return llRetVal
1241 Endproc
1242
1243
1244 ************************************************************************************
1245 * Purpuse: re-create transaction header,detail(vertical) from order header/detail
1246 ************************************************************************************
1247 Procedure CreateoPRTransaction
1248 Parameters tcOrderHeader, tcOrderDetail, tcTransHeader, tcTransDetail, tcTransCursorHeader,;
1249 tcTransCursorDetail, pcInterHeader, pcInterDetail, ;
1250 pcInterComment, pcInterAddress
1251 Local llOKtoContinue, lcSQLSelect, lnOrd_num, lnNewPick_num, lnNewInv_num
1252 llOKtoContinue = .T.
1253 lnOldSelect = Select()
1254
1255 Local llHaveInterHeader, llHaveInterDetail, llHaveInterComment, llHaveInterAddress
1256 llHaveInterHeader= (Recc(pcInterHeader)>0)
1257 llHaveInterDetail= (Recc(pcInterDetail)>0)
1258 llHaveInterComment= (Recc(pcInterComment)>0)
1259 llHaveInterAddress= (Recc(pcInterAddress)>0)
1260 l_nThermoCnt= 0
1261 This.InitThermoWithCaption(@l_nThermoCnt, 6, "Re-create all 855 transaction record(s)...")
1262 This.oLog.LogEntry("Re-create all 855 transaction record(s)...")
1263 This.AdvanceThermoPlus(@l_nThermoCnt)
1264 * clear all records from previous process
1265 Delete All In (tcTransDetail)
1266 Delete All In (tcTransHeader)
1267
1268 * Take ship detail and explode to vertical to TcVertDtl (same schema as ZZEoPRTD)
1269 This.CreateCursorStructure(tcTransDetail,,"tcVertDtl")
1270 This.AdvanceThermoPlus(@l_nThermoCnt)
1271 This.ExplodeDetailSizeToVertical(tcOrderDetail, "tcVertDtl")
1272 * create transaction detail from temporary ship detail (explode to vertical size)
1273 Select (tcTransDetail)
1274 Scatt Memvar Memo Blank
1275 Select tcVertDtl
1276 Scan
1277 llOKtoContinue= This.CreateRecordWithPkey("tcVertDtl", tcTransDetail, "ZZEOPRTD")
1278 If !llOKtoContinue
1279 Exit
1280 Endif
1281 Endscan
1282
1283 *
1284 If llOKtoContinue
1285 This.AdvanceThermoPlus(@l_nThermoCnt)
1286 * create transaction header from ship header
1287 Local lnCurrentHeaderPkey, lnCurrentHeaderOrd_num, lnCurrentHeaderInv_num,;
1288 lnCurrentHeaderPick_num, lnCurrentHeaderCncl_num
1289 lnOrd_num= 0
1290 * TR 1074595 KISHORE 23-JAN-2014
1291 STORE 0 TO lnCurrentHeaderPick_num, lnCurrentHeaderInv_num, lnCurrentHeaderCncl_num
1292
1293 Select (tcOrderHeader)
1294 * Only create one order header (open,pick,cancel)
1295 Scan
1296 * Only create 1 Order header
1297 If !(ord_num== lnOrd_num)
1298 lnOrd_num= ord_num
1299
1300 *--- TR 1074595 KISHORE 20-JAN-2014
1301 IF llHaveInterHeader
1302 SELECT (pcInterHeader)
1303 LOCATE FOR ord_num = lnOrd_num
1304 IF FOUND()
1305 SELECT zzeoprcr
1306 LOCATE FOR division = EVALUATE(tcOrderHeader + '.division' ) ;
1307 AND customer = EVALUATE(tcOrderHeader + '.customer' ) ;
1308 AND active_ok = 'Y'
1309 IF FOUND()
1310 IF zzeoprcr.ack_by = 'O' AND llHaveInterDetail && Order #
1311 DELETE FROM (tcTransDetail) ;
1312 WHERE fkey IN (SELECT pkey ;
1313 FROM (tcTransHeader) ;
1314 WHERE ord_num = lnOrd_num)
1315
1316 DELETE FROM (tcTransHeader) WHERE ord_num = lnOrd_num
1317 LOOP
1318 ENDIF
1319
1320 IF zzeoprcr.ack_by = 'P' AND llHaveInterDetail && PO #
1321 DELETE FROM (tcTransDetail) ;
1322 WHERE fkey IN (SELECT pkey ;
1323 FROM (tcTransHeader) ;
1324 WHERE po_num = EVALUATE(tcOrderHeader + '.po_num'))
1325
1326 DELETE FROM (tcTransHeader) WHERE po_num = EVALUATE(tcOrderHeader + '.po_num')
1327 LOOP
1328 ENDIF
1329 ENDIF
1330 ENDIF
1331 ENDIF llHaveInterHeader
1332 *=== TR 1074595 KISHORE 20-JAN-2014
1333
1334 llOKtoContinue= This.CreateRecordWithPkey(tcOrderHeader, tcTransHeader, "ZZEoPRTH")
1335 If !llOKtoContinue
1336 Exit
1337 Else
1338 *--- TR 1074595 KISHORE 20-JAN-2014 - commented
1339 * delete same ord_num in interface tables
1340*!* If llHaveInterHeader
1341*!* Delete For ord_num= lnOrd_num In (pcInterHeader)
1342*!* Endif
1343*!* If llHaveInterDetail
1344*!* Delete For ord_num= lnOrd_num In (pcInterDetail)
1345*!* Endif
1346*!* If llHaveInterComment
1347*!* Delete For ord_num= lnOrd_num In (pcInterComment)
1348*!* Endif
1349*!* If llHaveInterAddress
1350*!* Delete For ord_num= lnOrd_num In (pcInterAddress)
1351*!* Endif
1352 *=== TR 1074595 KISHORE 20-JAN-2014 - commented
1353
1354 * resync all fkey for same ord_num
1355 lnCurrentHeaderPkey= &tcTransHeader..pkey
1356 lnCurrentHeaderOrd_num= &tcTransHeader..ord_num
1357 lnCurrentHeaderPick_num= &tcTransHeader..pick_num
1358 lnCurrentHeaderCncl_num= &tcTransHeader..cncl_num
1359 lnCurrentHeaderInv_num= &tcTransHeader..inv_num
1360 Replace All fkey With lnCurrentHeaderPkey In (tcTransDetail) ;
1361 For ord_num= lnCurrentHeaderOrd_num
1362
1363 *--- TechRec 1078545 30-Jun-2014 vkrishnamurthy ---
1364 IF !EMPTY(This.cNewShipdate)
1365 Replace new_ship_date WITH This.cNewShipdate IN (tcTransHeader)
1366 ENDIF
1367 *=== TechRec 1078545 30-Jun-2014 vkrishnamurthy ===
1368 Endif
1369 Else && same order header (partial pick/cancel)
1370 lnNewPick_num= &tcOrderHeader..pick_num
1371 If lnNewPick_num> lnCurrentHeaderPick_num
1372 Replace pick_num With lnNewPick_num, pick_date With &tcOrderHeader..pick_date ;
1373 in (tcTransHeader)
1374 lnCurrentHeaderPick_num= lnNewPick_num
1375 Endif
1376 lnNewInv_num= &tcOrderHeader..inv_num
1377 If lnNewInv_num> lnCurrentHeaderInv_num
1378 Replace inv_num With &tcOrderHeader..inv_num, ship_date With &tcOrderHeader..ship_date ;
1379 in (tcTransHeader)
1380 lnCurrentHeaderInv_num= lnNewInv_num
1381 Endif
1382 If lnCurrentHeaderCncl_num=0 And !Empty(&tcOrderHeader..cncl_num)
1383 Replace cncl_num With &tcOrderHeader..cncl_num In (tcTransHeader)
1384 Endif
1385 Endif
1386 Endscan
1387 Endif
1388
1389 * tableupdate trans header/detail
1390 If llOKtoContinue
1391
1392 This.AdvanceThermoPlus(@l_nThermoCnt)
1393 Dime laTables[2]
1394 laTables[1]= "vzzeoPRth_Proc"
1395 laTables[2]= "vzzeoPRtd_Proc"
1396
1397 llOKtoContinue= This.TableUpdateWithTransaction(@laTables)
1398
1399 * Make tran header cursor
1400 This.AdvanceThermoPlus(@l_nThermoCnt)
1401 *- 1005605 06/14/04 YIK
1402 *- Join with the 855 edi control table (local)
1403 *- Select * From vzzeoPRtH_Proc Into Cursor __EoPRth
1404
1405 *--- TR 1025228 3-Jul-2007 Goutam
1406 *Select h.*, c.sln_req, c.SkipPpkUPC, c.sku_upc As c_sku_upc ;
1407 From vzzeoPRtH_Proc h ;
1408 JOIN zzeoPRcr c On h.customer = c.customer And h.division = c.division ;
1409 Into Cursor __EoPRth
1410
1411 *--- TR 1034481 NH
1412
1413*!* Select h.*, NVL(c.sln_req, c2.sln_req) as sln_req, NVL(c.SkipPpkUPC, c2.SkipPpkUPC) as SkipPpkUPC, ;
1414*!* NVL(c.DUNS_req, c2.DUNS_req) as DUNS_req, NVL(c.suppl_req, c2.suppl_req) AS suppl_req, ;
1415*!* NVL(c.dept_req, c2.dept_req) AS dept_req, NVL(c.po_req, c2.po_req) AS po_req, ;
1416*!* NVL(c.sku_upc, c2.sku_upc) As c_sku_upc, NVL(c.ack_by, c2.ack_by) As c_ack_by, ;
1417*!* NVL(c.vnd_id, c2.vnd_id) As c_vnd_id, NVL(c.vnd_vers, c2.vnd_vers) As vnd_vers, ;
1418*!* NVL(c.pKey, c2.pKey) As Controlpkey ;
1419*!* From vzzeoPRtH_Proc h ;
1420*!* LEFT JOIN zzeoPRcr c On h.customer = c.customer And h.division = c.division ;
1421*!* AND h.oVnd_Key = c.oVnd_Key ;
1422*!* LEFT JOIN zzeoPRcr c2 On h.customer = c2.customer And h.division = c2.division ;
1423*!* AND c2.oVnd_Key = ' ' ;
1424*!* Into Cursor __EoPRth
1425
1426 *--- resolve which control ref record should be used (take the pkey of zzeoprcr)
1427 CREATE CURSOR tcOprXcr (pkey i,pkey_c i)
1428
1429 INSERT INTO tcOprXcr ;
1430 SELECT h.pkey, NVL(NVL(c.pkey, c2.pkey),0) pkey_c ;
1431 From vzzeoPRtH_Proc h ;
1432 LEFT JOIN zzeoPRcr c On h.customer = c.customer And h.division = c.division ;
1433 AND h.oVnd_Key = c.oVnd_Key ;
1434 LEFT JOIN zzeoPRcr c2 On h.customer = c2.customer And h.division = c2.division ;
1435 AND c2.oVnd_Key = ' '
1436
1437 INDEX on pkey TAG pkey
1438 INDEX on pkey_c TAG pkey_c
1439
1440 * TR 1089471 KISHORE 22-SEP-2015 added , NVL(c.template,' ') as template
1441 Select h.*, NVL(c.sln_req, ' ') as sln_req, NVL(c.SkipPpkUPC, ' ') as SkipPpkUPC, ;
1442 NVL(c.DUNS_req, ' ') as DUNS_req, NVL(c.suppl_req, ' ') AS suppl_req, ;
1443 NVL(c.dept_req, ' ') AS dept_req, NVL(c.po_req, ' ') AS po_req, ;
1444 NVL(c.sku_upc, ' ') As c_sku_upc, NVL(c.ack_by, ' ') As c_ack_by, ;
1445 NVL(c.vnd_id, ' ') As c_vnd_id, NVL(c.vnd_vers, ' ') As vnd_vers, ;
1446 x.pKey_c As Controlpkey, NVL(c.template,' ') as template ;
1447 from vzzeoPRtH_Proc h inner join tcOprXcr x ON h.pkey = x.pkey ;
1448 left outer join zzeoPRcr c on x.pkey_c = c.pkey ;
1449 Into Cursor __EoPRth
1450 *=== TR 1034481 NH
1451
1452 *=== TR 1025228 3-Jul-2007 Goutam
1453
1454 *= 1005605 YIK
1455 This.MakeCursorWritable("__EoPRth", tcTransCursorHeader)
1456 Select (tcTransCursorHeader)
1457
1458 *--- TR 1025228 3-Jul-2007 Goutam
1459 Replace ALL sku_upc WITH c_sku_upc, vnd_id WITH c_vnd_id, ack_by WITH c_ack_by
1460 *=== TR 1025228 3-Jul-2007 Goutam
1461
1462 .nRecordsToProcess= Recc(tcTransCursorHeader)
1463 Index On pkey Tag pkey
1464 Index On Str(ord_num) Tag ord
1465
1466 *--- TR 1025228 3-Jul-2007 Goutam
1467 *Index On customer+division Tag cust_div
1468 Index On customer + division + oVnd_Key Tag cust_div
1469 *=== TR 1025228 3-Jul-2007 Goutam
1470
1471 This.AdvanceThermoPlus(@l_nThermoCnt)
1472 * Make tran detail cursor
1473 * --- TR 1048571 8/18/10 CM --- Added Invoice_Color
1474 * TR 1089471 KISHORE 22-SEP-2015 Added , '' as slntodtl
1475 Select *, SPACE(5) as Invoice_Color, '' as slntodtl From vzzeoPRtD_Proc Into Cursor __EoPRtd
1476 This.MakeCursorWritable("__EoPRtd", tcTransCursorDetail)
1477 Select (tcTransCursorDetail)
1478 Index On pkey Tag pkey
1479 Index On Empty(upc) Tag upc
1480 Index On Empty(sku) Tag sku
1481
1482 *--- TR 1041272 06-Jul-2009 Cmettle ---
1483 Index On customer + division+ Style+ color_code+ Lbl_code+ Dimension TAG CustSKU
1484 *=== TR 1041272 06-Jul-2009 Cmettle ---
1485
1486 *- 1005605 06/16/04 YIK
1487 *- add indexes needed for range style explosion and
1488 *- Populate sln_req, sku_upc and SkipPpkUpc to details
1489 * --- TR 1048571 8/18/10 CM --- Added Invoice_Color to Replace
1490 Index On rng_style+ rng_color+ rng_lbl+ rng_pack+ Str(rng_bk)+ Str(ord_num)+line_status Tag RangeSKU
1491 Index On division+Style+color_code+lbl_code+Dimension+line_status Tag OurSKU
1492 llOKtoContinue= .SetRelation(tcTransCursorHeader, "Pkey", tcTransCursorDetail, "fkey")
1493 If llOKtoContinue
1494 Replace All customer With &tcTransCursorHeader..customer, ;
1495 sln_req With Iif(&tcTransCursorHeader..sln_req = "Y" Or &tcTransCursorDetail..implosion = "Y", "Y", "N"), ;
1496 sku_upc With &tcTransCursorHeader..c_sku_upc, ;
1497 SkipPpkUPC With &tcTransCursorHeader..SkipPpkUPC, ;
1498 Invoice_Color WITH &tcTransCursorDetail..color_code
1499 Set Relation To
1500 Endif
1501 *= 1005605
1502 Endif
1503 Select(lnOldSelect)
1504 Return llOKtoContinue
1505 Endproc
1506
1507 ************************************************************************************
1508 *
1509 ************************************************************************************
1510 Procedure ExplodeDetailSizeToVertical
1511 Parameters tcOrderDetail, tcTempTransDetail
1512 Local llRetVal, lcSQLSelect, loDetail, lnLoop, lnSizeQty
1513 This.oLog.LogEntry("Explode Detail Size To Vertical")
1514 llRetVal = .T.
1515 lnOldSelect = Select()
1516
1517 * use Memvar schema of 2 table is not match
1518 Select (tcTempTransDetail)
1519 Scatter Memvar
1520 Select (tcOrderDetail)
1521 Scan
1522 Scatt Memvar Memo
1523 * Notes: Future enhancement should consider range style as well
1524 * Original SKU substitution
1525 If sub_code="O" And !Empty(sub_style)
1526 m.Style= sub_style
1527 m.color_code= sub_color
1528 m.lbl_code= sub_lbl
1529 m.Dimension= sub_dimens
1530 Endif
1531 For lnLoop= 1 To goEnv.MaxBuckets
1532 lnSizeQty = Eval("size" + Trans(lnLoop, "@L 99") + "_qty")
1533 If lnSizeQty > 0
1534 * Prepare Size Bucket Information
1535 m.sizebucket = lnLoop
1536 m.total_qty = lnSizeQty
1537 If !(Used("tmpStylr") And (tmpStylr.division= division And tmpStylr.Style= Style))
1538 vl_stylr(division,, "tmpStylr", Style)
1539 Endif
1540 m.style_name = tmpStylr.style_name
1541 m.po4_uom = tmpStylr.uom
1542 * in getsizedesc also compare for current temp cursor size_code
1543 m.size_desc = This.getSizeDesc(m.division, tmpStylr.size_code, m.sizebucket)
1544 If vl_colrr(color_code,,"tmpcolrr")
1545 m.color_name = tmpcolrr.color_name
1546 m.nrf_color = tmpcolrr.nrf_color
1547 Endif
1548 If vl_dimer(m.division,,"tmpdimer", tmpStylr.size_code)
1549 lcFldName = "tmpdimer.nrf_sz" + Padl(Alltrim(Trans(m.sizebucket, "99")), 2, '0')
1550 m.nrf_size = Eval(lcFldName)
1551 Endif
1552 Select (tcTempTransDetail)
1553 Append Blank
1554 Gather Memvar Memo
1555 Endif
1556 Endfor
1557 Endscan
1558 * cleanup
1559 With This
1560 .TableClose("tmpStylr")
1561 .TableClose("tmpColrr")
1562 .TableClose("tmpDimer")
1563 Endwith
1564
1565 Select(lnOldSelect)
1566 Return llRetVal
1567 Endproc
1568
1569 ************************************************************************************
1570 * Error check header: validate remit#, DUNS#, supplier#, dept#, PO#
1571 ************************************************************************************
1572 Procedure CheckHeader
1573 Lparameters pcTransHeader, pcoprcr
1574 Local llRetVal, lnOldSelect, lcOldCustomer, lcOldDivision, llRequireBill_num, ;
1575 llRequireSCAC_Code, l_cSQLString
1576
1577 *--- TR 1025228 3-Jul-2007 Goutam
1578 LOCAL lcOldVnd_Key
1579 lcOldVnd_Key = ""
1580 *=== TR 1025228 3-Jul-2007 Goutam
1581
1582 llRetVal = .T.
1583 lnOldSelect = Select()
1584
1585 l_nThermoCnt = 0
1586 This.InitThermoWithCaption(@l_nThermoCnt, Recc(pcTransHeader), "Validating Order Header Informations...")
1587 This.oLog.LogEntry("Validating Order Header Informations...")
1588 *--- TR 1034481 NH
1589 IF llRetVal
1590 .AdvanceThermoTotalWithCaptionPlus("Validating Control Ref...")
1591 llRetVal = llRetVal and .Check855ControlRef(pcTransHeader, pcoprcr)
1592 ENDIF
1593 *=== TR 1034481 NH
1594 If llRetVal
1595 .AdvanceThermoTotalWithCaptionPlus("Validating D.U.N.S. Number...")
1596 llRetVal= .CheckDUNS(pcTransHeader, pcoprcr)
1597 Endif
1598 If llRetVal
1599 .AdvanceThermoTotalWithCaptionPlus("Validating Supplier Number...")
1600 llRetVal= .CheckSupplier(pcTransHeader, pcoprcr)
1601 Endif
1602 If llRetVal
1603 .AdvanceThermoTotalWithCaptionPlus("Validating Dept Number...")
1604 llRetVal= .CheckDept(pcTransHeader, pcoprcr)
1605 Endif
1606 If llRetVal
1607 .AdvanceThermoTotalWithCaptionPlus("Validating PO Number...")
1608 llRetVal= .CheckPO(pcTransHeader, pcoprcr)
1609 Endif
1610
1611 lcOldCustomer= ""
1612 lcOldDivision= ""
1613 Select (pcTransHeader)
1614 Set Order To cust_div
1615 Scan
1616 This.AdvanceThermoPlus(@l_nThermoCnt)
1617 * Level break get 855 control reference by division,customer once
1618 *--- TR 1025228 3-Jul-2007 Goutam
1619 *If !(customer == lcOldCustomer And division == lcOldDivision)
1620 If !(customer == lcOldCustomer And division == lcOldDivision And oVnd_Key == lcOldVnd_Key)
1621 *=== TR 1025228 3-Jul-2007 Goutam
1622
1623 lcOldCustomer = customer
1624 lcOldDivision = division
1625 *--- TR 1025228 3-Jul-2007 Goutam
1626 lcOldVnd_Key = oVnd_Key
1627 *=== TR 1025228 3-Jul-2007 Goutam
1628
1629 *--- TR 1025228 3-Jul-2007 Goutam
1630 *- we already created local cursor zzeoprcr in OpenOPRViews
1631 *Select * From zzeoPRcr Where customer = lcOldCustomer And division = lcOldDivision ;
1632 INTO Cursor tceoPRcr
1633 Select TOP 1* From zzeoPRcr Where customer = lcOldCustomer And division = lcOldDivision ;
1634 AND (oVnd_Key = lcOldVnd_Key OR oVnd_Key = ' ') ORDER BY oVnd_key DESC INTO Cursor tceoPRcr
1635
1636 *=== TR 1025228 3-Jul-2007 Goutam
1637 Endif
1638 * Successfully read 855 control reference
1639 If llRetVal
1640 *- 1/23/03 YIK - Add Ack_by
1641 * Write vnd,our id/qual, auto_proc back to transaction header
1642 * --- TR 1048557 7/30/10 CM --- Added eta_days
1643 Replace vnd_id With tceoPRcr.vnd_id, vnd_qual With tceoPRcr.vnd_qual, ;
1644 our_id With tceoPRcr.our_id, our_qual With tceoPRcr.our_qual, ;
1645 auto_proc With tceoPRcr.auto_proc, sku_upc With tceoPRcr.sku_upc, ;
1646 adr_resolv With tceoPRcr.adr_resolv, ack_by With tceoPRcr.ack_by, ;
1647 eta_days WITH tceoPRcr.eta_days ;
1648 In (pcTransHeader)
1649 *--- 1078104 KISHORE 22-APR-2014
1650 IF This.lFrom850
1651 REPLACE auto_proc WITH 'Y' IN (pcTransHeader)
1652 ENDIF
1653 *=== 1078104 KISHORE 22-APR-2014
1654 Endif
1655 Endscan
1656
1657 This.ResetThermoPlus()
1658 Select(lnOldSelect)
1659 Return llRetVal
1660 Endproc
1661
1662 ************************************************************************************
1663 * Validate DUNS from division ref.
1664 ***********************************************************************************
1665 *--- TR 1034481 NH
1666 PROCEDURE Check855ControlRef
1667 Lparameters pceoprTH, pceoprcr
1668
1669 LOCAL llRetVal, lnOldSelect
1670 llRetVal = .t.
1671 lnOldSelect = SELECT()
1672 SELECT(pceoprcr)
1673 INDEX on customer TAG customer
1674 INDEX on division TAG division
1675
1676 UPDATE th SET errs_flg_h = 'Y', errs_msg_h = errs_msg_h + 'Corresponding 855 control reference not found.' + CRLF ;
1677 from (pceoprTH) th WHERE ;
1678 NOT exists ( select c.customer from (pceoprcr) c WHERE c.customer = th.customer AND c.division = th.division)
1679
1680 UPDATE th SET errs_flg_h = 'Y', errs_msg_h = errs_msg_h + 'Corresponding 855 control reference not active.' + CRLF ;
1681 from (pceoprTH) th inner join (pceoprcr) c on c.customer = th.customer AND c.division = th.division AND c.Active_ok <> 'Y'
1682
1683 Select(lnOldSelect)
1684 Return llRetVal
1685 Endproc
1686
1687 ************************************************************************************
1688 * Validate DUNS from division ref.
1689 ***********************************************************************************
1690 Procedure CheckDUNS
1691 Lparameters pceoprTH, pceoprcr
1692 Local llRetVal, lnOldSelect, lcDUNS_num
1693 llRetVal = .T.
1694 lnOldSelect = Select()
1695 * Check for DUNS_num require [DUNS_req="Y"]
1696
1697 *--- TR 1025228 3-Jul-2007 Goutam
1698
1699*!* Select Distinct h.division,h.customer From (pceoprTH) h, (pceoprcr) c ;
1700*!* Where h.division= c.division And h.customer= c.customer And c.DUNS_req="Y" ;
1701*!* Order By h.division, h.customer Into Cursor tcDUNS
1702*!*
1703*!* Select tcDUNS
1704*!* If This.lUserInterface
1705*!* This.InitThermo(This.CountTotalRecs ('tcDUNS'))
1706*!* l_nThermoCnt = 0
1707*!* Endif
1708
1709*!* lcDUNS_num=""
1710*!* Scan
1711*!* If This.lUserInterface
1712*!* l_nThermoCnt = l_nThermoCnt + 1
1713*!* This.AdvanceThermo(l_nThermoCnt)
1714*!* Endif
1715*!* llRetVal= vl_divsr(tcDUNS.division,, "_Divsr")
1716*!* If llRetVal And !Empty(_Divsr.DUNS_num)
1717*!* Replace DUNS_num With _Divsr.DUNS_num ;
1718*!* For division= tcDUNS.division And customer= tcDUNS.customer In (pceoprTH)
1719*!* Else
1720*!* Replace Errs_Msg_H With Errs_Msg_H + EDI_DUNS_REQ_MSG + CRLF, ;
1721*!* Errs_Flg_H With "Y" In (pceoprTH) ;
1722*!* For division= tcDUNS.division And customer= tcDUNS.customer
1723*!* Endif
1724*!* Endscan
1725
1726*!* Use In tcDUNS
1727 *--- TR 1034481 NH
1728
1729*!* LOCAL lnOldOrder, lnTotalDUNSRecord, lcCustomer, lcDivision, lcoVnd_key, l_nThermoCnt
1730*!* SELECT (pceoprTH)
1731*!* lnOldOrder = ORDER()
1732*!* SET ORDER to cust_div
1733*!* COUNT TO lnTotalDUNSRecord FOR DUNS_req="Y"
1734*!* GO top
1735*!* If This.lUserInterface
1736*!* This.InitThermo(lnTotalDUNSRecord)
1737*!* l_nThermoCnt = 0
1738*!* Endif
1739
1740*!* SCAN FOR DUNS_req="Y"
1741*!* IF This.lUserInterface
1742*!* l_nThermoCnt = l_nThermoCnt + 1
1743*!* This.AdvanceThermo(l_nThermoCnt)
1744*!* ENDIF
1745*!* lcCustomer = Customer
1746*!* lcDivision = Division
1747*!* lcoVnd_key = oVnd_key
1748*!* llRetVal= vl_divsr(tcDUNS.division,, "_Divsr")
1749*!* If llRetVal And !Empty(_Divsr.DUNS_num)
1750*!* Replace DUNS_num With _Divsr.DUNS_num ;
1751*!* WHILE division = lcDivision And customer = lcCustomer ;
1752*!* And oVnd_key = lcoVnd_key In (pceoprTH)
1753*!* Else
1754*!* Replace Errs_Msg_H With Errs_Msg_H + EDI_DUNS_REQ_MSG + CRLF, ;
1755*!* Errs_Flg_H With "Y" In (pceoprTH) ;
1756*!* WHILE division= tcDUNS.division And customer= tcDUNS.customer ;
1757*!* And oVnd_key = tcDUNS.oVnd_key
1758*!* ENDIF
1759*!* IF _tally > 0
1760*!* SKIP - 1
1761*!* ENDIF
1762*!* ENDSCAN
1763*!* GO top
1764*!* SET ORDER TO (lnOldOrder)
1765 *--- cache divsiion table - zzxdivsr
1766 llRetVal = llRetVal and v_sqlexec("SELECT * FROM zzxdivsr","zzxdivsr")
1767
1768 IF llRetVal
1769 SELECT zzxdivsr
1770 INDEX on division TAG division
1771 *--- error out first
1772
1773 UPDATE th SET Errs_Msg_H = Errs_Msg_H + EDI_DUNS_REQ_MSG + CRLF, Errs_Flg_H = 'Y' ;
1774 from (pceoprTH) th WHERE th.DUNS_req = 'Y' ;
1775 AND (NOT exists (select dr.pkey from zzxdivsr dr where th.division = dr.division) ; &&--- No Division ref record
1776 OR exists( select dr.pkey from zzxdivsr dr where th.division = dr.division AND dr.DUNS_num = ' ')) &&--- Division ref exists but no DUNS_num
1777
1778 *--- stamp duns_num values from division ref
1779 UPDATE th SET duns_num = dr.duns_num ;
1780 from (pceoprTH) th inner JOIN zzxdivsr dr on th.division = dr.division ;
1781 WHERE th.DUNS_req = 'Y' AND dr.DUNS_num > ' '
1782
1783 ENDIF
1784
1785 this.ResetThermoPlus()
1786 *=== TR 1025228 3-Jul-2007 Goutam
1787
1788 If Used('_Divsr')
1789 Use In _Divsr
1790 Endif
1791 If This.lUserInterface
1792 This.ResetThermo()
1793 Endif
1794 Select(lnOldSelect)
1795 Return llRetVal
1796 Endproc
1797
1798
1799 ************************************************************************************
1800 * Check supplier_num only for 855(o) customer that set
1801 * the suppl_req ="Y"
1802 ************************************************************************************
1803 Procedure CheckSupplier
1804 Lparameters pceoprTH, pceoprcr
1805 Local llRetVal, lnOldSelect
1806 llRetVal= .T.
1807 lnOldSelect= Select()
1808
1809 *--- TR 1025228 3-Jul-2007 Goutam
1810*!* Select Distinct h.division, h.customer, h.supplier_num From (pceoprTH) h, ;
1811*!* (pceoprcr) c Where h.division= c.division And h.customer= c.customer And ;
1812*!* c.suppl_req = "Y" Into Cursor __TmpCursor
1813*!*
1814*!* Select __TmpCursor
1815*!* With This
1816*!* Local lnThermoCnt
1817*!* lnThermoCnt = 0
1818*!* .InitThermoPlus(This.CountTotalRecs ('__TmpCursor'))
1819*!* Scan For Empty(supplier_num)
1820*!* .AdvanceThermoPlus(lnThermoCnt)
1821*!*
1822*!* Replace Errs_Flg_H With "Y", Errs_Msg_H With Errs_Msg_H + CRLF + ;
1823*!* EDI_SUPPLIER_REQUIRED_MSG In (pceoprTH) ;
1824*!* For customer= __TmpCursor.customer And ;
1825*!* division= __TmpCursor.division And ;
1826*!* EMPTY(&pceoprTH..supplier_num)
1827
1828*!* Endscan
1829*!* Use In __TmpCursor
1830*!* .ResetThermoPlus()
1831*!* Endwith
1832
1833
1834 LOCAL lnOldOrder, l_nThermoCnt, lnTotalSupplRecord, lcCustomer, lcDivision, lcoVnd_key
1835 SELECT (pceoprTH)
1836 lnOldOrder = ORDER()
1837 SET ORDER to cust_div
1838 COUNT TO lnTotalSupplRecord FOR suppl_req = "Y"
1839 GO top
1840 This.InitThermo(lnTotalSupplRecord)
1841 l_nThermoCnt = 0
1842
1843 SCAN FOR suppl_req = "Y"
1844 l_nThermoCnt = l_nThermoCnt + 1
1845 This.AdvanceThermo(l_nThermoCnt)
1846
1847 lcCustomer = Customer
1848 lcDivision = Division
1849 lcoVnd_key = oVnd_key
1850
1851 Replace Errs_Flg_H With "Y", Errs_Msg_H With Errs_Msg_H + CRLF + ;
1852 EDI_SUPPLIER_REQUIRED_MSG In (pceoprTH) ;
1853 WHILE customer = lcCustomer And ;
1854 division = lcDivision And ;
1855 oVnd_Key = lcoVnd_key ;
1856 FOR EMPTY(supplier_num)
1857
1858 IF _tally > 0
1859 SKIP - 1
1860 ENDIF
1861
1862 Endscan
1863 GO top
1864 SET ORDER TO (lnOldOrder)
1865 this.ResetThermoPlus()
1866 *=== TR 1025228 3-Jul-2007 Goutam
1867
1868 Select(lnOldSelect)
1869 Return llRetVal
1870 Endproc
1871
1872
1873 ************************************************************************************
1874 * Check dept only for 855(o) customer that set
1875 * the dept_req ="Y"
1876 ************************************************************************************
1877 Procedure CheckDept
1878 Lparameters pceoprTH, pceoprcr
1879 Local llRetVal, lnOldSelect
1880 llRetVal= .T.
1881 lnOldSelect= Select()
1882
1883 *--- TR 1025228 3-Jul-2007 Goutam
1884*!* Select Distinct h.division, h.customer, h.department From (pceoprTH) h, ;
1885*!* (pceoprcr) c Where h.division= c.division And h.customer= c.customer And ;
1886*!* c.dept_req = "Y" Into Cursor __TmpCursor
1887*!*
1888*!* Select __TmpCursor
1889*!* With This
1890*!* Local lnThermoCnt
1891*!* lnThermoCnt = 0
1892*!* .InitThermoPlus(This.CountTotalRecs ('__TmpCursor'))
1893*!* Scan For Empty(department)
1894*!* .AdvanceThermoPlus(lnThermoCnt)
1895
1896*!* Replace Errs_Flg_H With "Y", Errs_Msg_H With Errs_Msg_H + CRLF + ;
1897*!* EDI_DEPT_REQUIRED_MSG In (pceoprTH) ;
1898*!* For customer= __TmpCursor.customer And ;
1899*!* division= __TmpCursor.division And ;
1900*!* EMPTY(&pceoprTH..department)
1901*!*
1902*!* Endscan
1903*!* Use In __TmpCursor
1904*!* .ResetThermoPlus()
1905*!* Endwith
1906
1907 LOCAL lnOldOrder, l_nThermoCnt, lnTotalDeptRecord, lcCustomer, lcDivision, lcoVnd_key
1908 SELECT (pceoprTH)
1909
1910 lnOldOrder = ORDER()
1911 SET ORDER to cust_div
1912
1913 COUNT TO lnTotalDeptRecord FOR dept_req = "Y"
1914 GO top
1915 This.InitThermo(lnTotalDeptRecord)
1916 l_nThermoCnt = 0
1917
1918 SCAN FOR dept_req = "Y"
1919 l_nThermoCnt = l_nThermoCnt + 1
1920 This.AdvanceThermo(l_nThermoCnt)
1921
1922 lcCustomer = Customer
1923 lcDivision = Division
1924 lcoVnd_key = oVnd_key
1925
1926 Replace Errs_Flg_H With "Y", Errs_Msg_H With Errs_Msg_H + CRLF + ;
1927 EDI_DEPT_REQUIRED_MSG In (pceoprTH) ;
1928 WHILE customer = lcCustomer And ;
1929 division = lcDivision And ;
1930 oVnd_Key = lcoVnd_key ;
1931 FOR EMPTY(department)
1932
1933 IF _tally > 0
1934 SKIP - 1
1935 ENDIF
1936 ENDSCAN
1937 GO top
1938 SET ORDER TO (lnOldOrder)
1939 this.ResetThermoPlus()
1940 *=== TR 1025228 3-Jul-2007 Goutam
1941
1942 Select(lnOldSelect)
1943 Return llRetVal
1944 Endproc
1945
1946
1947 ************************************************************************************
1948 * Check PO# only for 855(o) customer that set
1949 * the po_req ="Y"
1950 ************************************************************************************
1951 Procedure CheckPO
1952 Lparameters pceoprTH, pceoprcr
1953 Local llRetVal, lnOldSelect
1954 llRetVal= .T.
1955 lnOldSelect= Select()
1956
1957 *--- TR 1025228 3-Jul-2007 Goutam
1958*!* Select Distinct h.division, h.customer, h.po_num From (pceoprTH) h, ;
1959*!* (pceoprcr) c Where h.division= c.division And h.customer= c.customer And ;
1960*!* c.po_req = "Y" Into Cursor __TmpCursor
1961
1962*!* Select __TmpCursor
1963*!* With This
1964*!* Local lnThermoCnt
1965*!* lnThermoCnt = 0
1966*!* .InitThermoPlus(This.CountTotalRecs ('__TmpCursor'))
1967*!* Scan For Empty(po_num)
1968*!* .AdvanceThermoPlus(lnThermoCnt)
1969*!*
1970*!* Replace Errs_Flg_H With "Y", Errs_Msg_H With Errs_Msg_H + CRLF + ;
1971*!* EDI_PO_REQUIRED_MSG In (pceoprTH) ;
1972*!* For customer= __TmpCursor.customer And ;
1973*!* division= __TmpCursor.division And ;
1974*!* EMPTY(&pceoprTH..po_num)
1975*!*
1976*!* Endscan
1977*!* Use In __TmpCursor
1978*!* .ResetThermoPlus()
1979*!* Endwith
1980
1981 LOCAL lnOldOrder, l_nThermoCnt, lnTotalPORecord, lcCustomer, lcDivision, lcoVnd_key
1982 SELECT (pceoprTH)
1983 lnOldOrder = ORDER()
1984 SET ORDER to cust_div
1985 COUNT TO lnTotalPORecord FOR po_req = "Y"
1986 GO top
1987 This.InitThermo(lnTotalPORecord)
1988 l_nThermoCnt = 0
1989
1990 SCAN FOR po_req = "Y"
1991 l_nThermoCnt = l_nThermoCnt + 1
1992 This.AdvanceThermo(l_nThermoCnt)
1993
1994 lcCustomer = Customer
1995 lcDivision = Division
1996 lcoVnd_key = oVnd_key
1997
1998 Replace Errs_Flg_H With "Y", Errs_Msg_H With Errs_Msg_H + CRLF + ;
1999 EDI_PO_REQUIRED_MSG In (pceoprTH) ;
2000 WHILE customer = lcCustomer And ;
2001 division = lcDivision And ;
2002 oVnd_Key = lcoVnd_key ;
2003 FOR EMPTY(&pceoprTH..po_num)
2004
2005 IF _tally > 0
2006 SKIP - 1
2007 ENDIF
2008 ENDSCAN
2009 GO top
2010 SET ORDER TO (lnOldOrder)
2011 this.ResetThermoPlus()
2012 *=== TR 1025228 3-Jul-2007 Goutam
2013
2014 Select(lnOldSelect)
2015 Return llRetVal
2016 Endproc
2017
2018 ******************************************************************************************
2019 Procedure PopulateOrders
2020 Lparameters tcWorkHeader
2021 *- Use lnTerm_duedays, because PrepareTermsInfo() returns 0 since ship_date is 0 for open SO.
2022 Local llRetVal, lnOldSelect, lnMerch_amt, lnTerm_rate, ;
2023 lnInv_amt, lcterm_basis, lcTerm_desc, lcTerm_type, lnTerm_dom, ;
2024 ldDsc_duedate, lnDsc_duedays, ldNet_duedate, lnNet_duedays, lnTermDscAmt, ;
2025 lcFTerms_code, lcITD_TmType, lnITD_DueDays, lnITD_DiscPct, ;
2026 lnITD_DiscDays, lnTerm_duedays, lcCustomer
2027
2028 This.oLog.LogMajorStage("Starting: " + Program())
2029
2030 * should already have public variable that hold company name
2031 If Type('g_cCompany')="U"
2032 g_cCompany= vl_compr(,"comp_name", "tmpCompr")
2033 Endif
2034
2035 llRetVal = .T.
2036 lnOldSelect = Select()
2037
2038 l_nThermoCnt = 0
2039 This.InitThermoWithCaption(@l_nThermoCnt, Recc(tcWorkHeader), "Populate order informations...")
2040 Select (tcWorkHeader)
2041 Scan For !(Errs_Flg_H= "Y") && Only for good header
2042 This.AdvanceThermoPlus(@l_nThermoCnt)
2043 * reset
2044 Store 0 To lnTerm_rate, lnTermDscAmt, lnDsc_duedays, lnNet_duedays, ;
2045 lnTerm_dom, lnDetailDiscount, lnITD_DueDays, lnITD_DiscPct, lnITD_DiscDays, lnTerm_duedays
2046
2047 Store "" To lcterm_basis, lcTerm_desc, lcTerm_type, lcFTerms_code, lcITD_TmType, lcCustomer
2048 Store {//} To ldDsc_duedate, ldNet_duedate
2049
2050 lcCustomer = &tcWorkHeader..customer
2051 *- Retrieve ITD segment info from zzetermx
2052 If This.GetEDITerms(tcWorkHeader, @lnITD_DueDays, @lnITD_DiscPct, @lnITD_DiscDays, ;
2053 @lcFTerms_code, @lcITD_TmType, lcCustomer)
2054
2055 Replace ITD_DueDays With lnITD_DueDays, ITD_DiscPct With lnITD_DiscPct, ;
2056 ITD_DiscDays With lnITD_DiscDays, FctTerm_code With lcFTerms_code, ;
2057 ITD_TmType With lcITD_TmType ;
2058 in (tcWorkHeader)
2059 Endif
2060 * Prepare Terms Infos in memvar (will populate from 3rd param down)
2061 *- send 0 as amount since we don't need to calc. discount
2062 If This.PrepareTermsInfo(tcWorkHeader, 0, @lcterm_basis, @lnTerm_rate,;
2063 @lcTerm_desc, @lcTerm_type, @lnTerm_dom, @ldDsc_duedate, @lnDsc_duedays,;
2064 @ldNet_duedate, @lnNet_duedays, @lnTermDscAmt, @lnTerm_duedays)
2065 * Gentran cannot take " / / " - force {01/01/1900}
2066 If Empty(ldDsc_duedate)
2067 ldDsc_duedate= VFP_EMPTY_DATE_DEFAULT
2068 Endif
2069 If Empty(ldNet_duedate)
2070 ldNet_duedate= VFP_EMPTY_DATE_DEFAULT
2071 Endif
2072
2073 * write to order transaction header
2074 Replace term_basis With lcterm_basis,;
2075 term_rate With lnTerm_rate, term_desc With lcTerm_desc, ;
2076 term_type With lcTerm_type, term_dom With lnTerm_dom, ;
2077 dsc_duedate With ldDsc_duedate, dsc_duedays With lnDsc_duedays, ;
2078 net_duedate With ldNet_duedate, net_duedays With lnNet_duedays, ;
2079 Term_duedays With lnTerm_duedays In (tcWorkHeader)
2080 Else
2081 * problem in PrepareTermsInfo
2082 llRetVal= .F.
2083 Exit && scan loop
2084 Endif
2085
2086 * populate company name
2087 Replace comp_name With g_cCompany In (tcWorkHeader)
2088 If vl_divsr(&tcWorkHeader..division,,"tmpdivsr")
2089 Replace div_name With tmpdivsr.div_name In (tcWorkHeader)
2090 Endif
2091 * Pass all previous population fields
2092 llRetVal= .T.
2093 Endscan
2094 This.ResetThermoPlus()
2095 Select(lnOldSelect)
2096 Return llRetVal
2097 Endproc
2098
2099 *--- TR 1013007 NH 2005-10-17
2100 ************************************************************************************
2101
2102 Procedure SetDivNameAndComp(tcWorkHeader, tcWorkDetail)
2103
2104 Local llRetVal, lnOldSelect
2105 Local lcDivCursor,loDivSrc, loDiv
2106 llRetVal = .T.
2107 lnOldSelect = Select()
2108 lcDivCursor = Sys(2015)
2109 lcDivSrc = "DivName" + lcDivCursor
2110
2111 With This
2112 .oLog.LogEntry("Starting Programe : " + Program())
2113 * populate company name
2114 Replace All comp_name With g_cCompany In (tcWorkHeader)
2115 .oLog.LogEntry("Updating Company name completed." )
2116
2117 Select Distinct division From (tcWorkHeader) Into Cursor (lcDivCursor)
2118 .cSQLTempTable = ""
2119 If Reccount(lcDivCursor) > 0
2120 llRetVal = llRetVal And .GenerateSQLTempTable(lcDivCursor)
2121 llRetVal = llRetVal And .PopulateSQLTempTable(lcDivCursor)
2122 Endif
2123
2124 If Not Empty(.cSQLTempTable) And llRetVal
2125 lcSQL = "SELECT d.DIVISION, d.div_name FROM zzxdivsr d join " + .cSQLTempTable + " t on d.division = t.division"
2126 llRetVal = llRetVal And v_sqlexec(lcSQL, lcDivSrc)
2127 Select(lcDivSrc)
2128 Scan
2129 Scatter Name loDivSrc
2130 Replace All div_name With loDivSrc.div_name For division = loDivSrc.division In (tcWorkHeader)
2131 Endscan
2132 Endif
2133 .oLog.LogEntry("Updating division name " + Iif(llRetVal,"completed.","failed."))
2134 Endwith
2135
2136 Select(lnOldSelect)
2137 Return llRetVal
2138 Endproc
2139
2140 *==== TR 1013007 NH 2005-10-17
2141 ************************************************************************************
2142 * Error check UPC, SKU for all transaction details
2143 * Notes: For any detail line that 855 control for div,customer require both UPC,SKU
2144 * or just UPC,SKU and it still empty then getit from UPC,SKU ref.
2145 * NOT USED !!!
2146 ************************************************************************************
2147 *!* Procedure CheckDetail
2148 *!* Lparameters pcTransHeader, pcTransDetail, pcTransControl
2149 *!* Local llRetVal, lnOldSelect, lcOldDivision, llCheckSKU, llCheckUPC, ;
2150 *!* lcOldStyle, lcOldColor_code, lcOldLbl_code, lcOldDimension, lnOldSizeBucket
2151 *!* store "" to lcOldDivision, lcOldStyle, lcOldColor_code, lcOldLbl_code, lcOldDimension
2152 *!* This.oLog.LogEntry("Validating details")
2153 *!* lnOldSizeBucket= 0
2154 *!* llRetVal = .T.
2155 *!* lnOldSelect = Select()
2156
2157 *!* Select h.customer, h.division,d.style,d.Color_code,d.Lbl_code,d.dimension,;
2158 *!* d.sizebucket,d.pkey,d.upc,d.sku,h.sku_upc From (pcTransHeader) h, (pcTransDetail) d ;
2159 *!* Where h.pkey = d.fkey And ;
2160 *!* (((h.sku_upc= 'B' or h.sku_upc= 'U') And Empty(d.upc)) Or ;
2161 *!* ((h.sku_upc= 'B' or h.sku_upc= 'S') And Empty(d.sku))) ;
2162 *!* Order by 1,2,3,4,5,6,7 Into Cursor tmpCurs
2163
2164 *!* Select tmpCurs
2165 *!* l_nThermoCnt = 0
2166 *!* This.InitThermoWithCaption(@l_nThermoCnt, RECC('TmpCurs'), "Validating UPC, SKU...")
2167
2168 *!* Scan
2169 *!* This.AdvanceThermoPlus(@l_nThermoCnt)
2170
2171 *!* * When SKU change valid UPC,SKU dedend on llCheckUPC/SKU, reset all keys
2172 *!* *
2173 *!* If !(tmpCurs.division== lcOldDivision and tmpCurs.Style == lcOldStyle and ;
2174 *!* tmpCurs.Color_code== lcOldColor_code and tmpCurs.Lbl_code == lcOldLbl_code and ;
2175 *!* tmpCurs.Dimension== lcOldDimension And tmpCurs.sizebucket== lnOldSizeBucket) && level break
2176 *!* lcOldDivision = tmpCurs.division
2177 *!* lcOldStyle = tmpCurs.style
2178 *!* lcOldColor_code = tmpCurs.Color_code
2179 *!* lcOldLbl_code = tmpCurs.Lbl_code
2180 *!* lcOldDimension= tmpCurs.dimension
2181 *!* lnOldSizeBucket= tmpCurs.sizebucket
2182
2183 *!* llCheckUPC= (tmpCurs.sku_upc= "B" or tmpCurs.sku_upc= "U")
2184 *!* llCheckSKU= (tmpCurs.sku_upc= "B" or tmpCurs.sku_upc= "S")
2185
2186 *!* * reset error msg, SKU, UPC
2187 *!* lcErrs_Msg= ""
2188 *!* lcUPC=""
2189 *!* lcSKU=""
2190 *!* * Check UPC **************************************************************************
2191 *!* If llCheckUPC
2192 *!* lcUPC= This.GetUPC(@lcErrs_Msg, division, Style, Color_code, Lbl_code, Dimension, ;
2193 *!* sizebucket, "tmpUPCnr")
2194 *!* Endif
2195 *!* * Check SKU **************************************************************************
2196
2197 *!* If llCheckSKU
2198 *!* lcSKU= This.GetSKU(@lcErrs_Msg, customer,division, Style, Color_code, Lbl_code, ;
2199 *!* Dimension, sizebucket, "tmpCstdr")
2200 *!* Endif
2201 *!* Endif
2202
2203 *!* Select (pcTransDetail)
2204 *!* Locate For pkey = tmpCurs.pkey
2205 *!* * Write Either error or UPC,SKU back to transaction detail
2206 *!* * event with error still should write lcUPC,lcSKU back because
2207 *!* * maybe it require both UPC & SKU but only failing when look for one
2208 *!* * of them
2209 *!* If !Empty(lcErrs_Msg)
2210 *!* Replace Errs_Msg_D with Errs_Msg_D + lcErrs_Msg, Errs_Flg_D With "Y", ;
2211 *!* sku with lcSKU, upc with lcUPC In (pcTransDetail)
2212 *!* Else
2213 *!* Replace Errs_Msg_D with lcErrs_Msg, Errs_Flg_D With "N", ;
2214 *!* sku with lcSKU, upc with lcUPC In (pcTransDetail)
2215 *!* Endif
2216
2217 *!* Endscan
2218 *!* Use in tmpCurs
2219
2220 *!* If This.lUserInterface
2221 *!* * Reset Thermometer
2222 *!* This.ResetThermo()
2223 *!* Endif
2224
2225 *!* Select(lnOldSelect)
2226 *!* Return llRetVal
2227 *!* Endproc
2228
2229
2230 ************************************************************************************
2231 * Create 855 interface tables:
2232 * For any transaction headers that have no error, will move to interface table
2233 * deleted from trans and flag pick order header proc_855="P".
2234 * when create 855 interface have to put it on hold if 855 control auto_proc="N"
2235 ************************************************************************************
2236 Procedure CreateoPRInterface
2237 * TR 1074595 KISHORE 22-JAN-2014 Added pcStoresSDQ
2238 *- TR 1098611 FH - added datawhse
2239 Lparameters pcTransHeader, pcTransDetail, pcInterHeader, pcInterDetail, ;
2240 pcInterComment, pcInterAddress, pcStoreSDQ, pcWhseHeader ,pcWhseDetail
2241 Local llOKtoContinue, lnOldSelect, llInterHeaderPkey, ltDatetime, lnInterHeaderOrd_num, lcFldName, lcShip_To
2242 ltDatetime= Datetime()
2243 llOKtoContinue = .T.
2244 lnOldSelect = Select()
2245
2246 *- TR 1098611 FH
2247 LOCAL llHaveEDIH , llHaveEDID , lcWHSEScanFor
2248 llHaveEDIH = USED(pcWhseHeader) AND (This.CountTotalRecs(pcWhseHeader) > 0)
2249 llHaveEDID = USED(pcWhseDetail) AND (This.CountTotalRecs(pcWhseDetail) > 0)
2250 *- TR 1098611 FH
2251
2252 *--- TR 1100564 30-Nov-16 JPaul.
2253 LOCAL lnIPO_PKey
2254 lnIPO_PKey= 0
2255 *=== TR 1100564 30-Nov-16 JPaul.
2256
2257 * Get all interface header/detail to Memvar
2258 Select (pcInterHeader)
2259 Scatt Memvar Memo Blank
2260 Select (pcInterDetail)
2261 Scatt Memvar Memo Blank
2262
2263 * get all fields from trans header (history+shiporder+order header )
2264 Select * From (pcTransHeader) ;
2265 Where !(Errs_Flg_H= 'Y') ; && Empty(Errs_Msg_H)
2266 Order By customer, division ;
2267 Into Cursor tmpCurs
2268
2269 Select tmpCurs
2270 .nInterfaceRecords= Recc("TmpCurs") && number of good pick orders to process
2271 l_nThermoCnt = 0
2272 This.InitThermoWithCaption(@l_nThermoCnt, Recc('TmpCurs'), "Creating 855(o) Interface records...")
2273 This.oLog.LogEntry("Creating 855(o) Interface records...")
2274 Scan
2275 This.AdvanceThermoPlus(@l_nThermoCnt)
2276 Bcaddres(tmpCurs.customer,, "tmpAddrs", tmpCurs.Store, tmpCurs.ship_dc, ;
2277 tmpCurs.center_code, tmpCurs.consol_code, tmpCurs.ord_num, ;
2278 tmpCurs.division, tmpCurs.factor, tmpCurs.location)
2279
2280 lcShip_To = tmpAddrs.S_EDI_SHIP_TO
2281
2282 Select tmpCurs
2283 Scatt Memvar Memo
2284 * Create Interface header record
2285 *- 36646 1/27/03 YIK - No need to create new pkey
2286 *-m.pkey= v_NextPkey("ZZEOPRIH")
2287 If Empty(m.pkey)
2288 llOKtoContinue= .F.
2289 Exit && header scan
2290 Endif
2291 llInterHeaderPkey= m.pkey
2292 lnInterHeaderOrd_num= m.ord_num
2293
2294 *--- TR 1100564 30-Nov-16 JPaul.
2295 lnIPO_PKey= m.ipo_pkey
2296 *=== TR 1100564 30-Nov-16 JPaul.
2297
2298
2299 Select (pcInterHeader)
2300 Append Blank
2301 m.Ship_to = lcShip_To
2302 *- 1011846 08/25/05 YIK
2303 m.EDI_Shipto = lcShip_To
2304
2305 Gather Memvar Memo
2306
2307 * For each header locate 850 history header merge inbound fields into interface
2308 *-llOKtoContinue= This.UseIPOHistoryHeaderFields(customer, ord_num, pcInterHeader)
2309
2310 This.TimeStampDocument()
2311
2312 If tmpCurs.adr_resolv = 'Y'
2313 m.ord_num = lnInterHeaderOrd_num
2314 m.fkey = llInterHeaderPkey
2315 * Create Interface address record
2316
2317 * --- TR 1044108 12/2/09 CM --- Added 5th paramter to pass "ZZEOPRTA"
2318 * to generate pkeys for 855 address records, instead of using "ZZEOSHIA"
2319 *This.CreateVerticalAddress("TmpAddrs", pcInterAddress)
2320 This.CreateVerticalAddress("TmpAddrs", pcInterAddress, "", "", "ZZEOPRTA")
2321 * --- TR 1044108 12/2/09 CM
2322
2323 Endif
2324 ************************************************************************************
2325 * create Interface details using division, ord_num
2326 ************************************************************************************
2327 Select (pcTransDetail)
2328 *--- TR 1013007 2005-10-28 NH turn around 855 from 850
2329 *-- in turn around 855 we do not have any order number so, we will use fkey instead
2330 *!* SCAN FOR ord_num= lnInterHeaderOrd_num &&inv_num = TmpCurs.inv_num AND division = TmpCurs.division
2331 Scan For fkey = llInterHeaderPkey
2332 *=== TR 1013007 2005-10-28 NH
2333 *************************************************************************
2334 * Get all transaction detail fields to Memvar
2335 Scatter Memvar Memo
2336 *************************************************************************
2337 *!* * Final ajustment to interface detail fields in Memvar
2338 *!* If !(used("tmpStylr") And (tmpStylr.division= division And tmpStylr.style= style))
2339 *!* vl_stylr(division,, "tmpStylr", Style)
2340 *!* Endif
2341 *!* m.style_name = tmpStylr.style_name
2342 *!* m.po4_uom = tmpStylr.uom
2343 *!* * in getsizedesc also compare for current temp cursor size_code
2344 *!* m.size_desc = This.getSizeDesc(m.division, tmpStylr.size_code, m.sizebucket)
2345
2346 *!* IF vl_colrr(color_code,,"tmpcolrr")
2347 *!* m.color_name = tmpcolrr.color_name
2348 *!* m.nrf_color = tmpcolrr.nrf_color
2349 *!* ENDIF
2350 *!* IF vl_dimer(m.division,,"tmpdimer", tmpStylr.size_code)
2351 *!* lcFldName = "tmpdimer.nrf_sz" + PADL(ALLTRIM(TRANS(m.sizebucket, "99")), 2, '0')
2352 *!* m.nrf_size = EVAL(lcFldName)
2353 *!* ENDIF
2354 ************************************************************************************
2355 *--- TR 1097094 7/22/16 Gurinder
2356 IF .lResolveUPCSKU
2357 DO case
2358 Case m.is_upcsku = "U"
2359 m.sku = ''
2360 Case m.is_upcsku = "S"
2361 m.upc = ''
2362 OTHERWISE
2363 ENDCASE
2364 ENDIF
2365 *=== TR 1097094 7/22/16 Gurinder
2366
2367 * Create Interface detail record
2368 m.fkey= llInterHeaderPkey
2369 *- 36646 1/27/03 YIK - No need to create new pkey
2370 *- m.pkey= v_NextPkey("ZZEoPRID")
2371 If Empty(m.pkey)
2372 llOKtoContinue= .F.
2373 Exit && detail scan
2374 Endif
2375 Select (pcInterDetail)
2376 Append Blank
2377 Gather Memvar Memo
2378 This.TimeStampDocument()
2379 ************************************************************************************
2380 * Delete Trans. detail record, already sucessfully create Inter. detail record
2381 * Tested already in scan and delete current record you are on does not messup the
2382 * scan loop
2383 Delete In (pcTransDetail)
2384 Endscan && details
2385
2386 *--- TR 1074595 KISHORE 22-JAN-2014
2387 IF tmpCurs.c_ack_by = 'P'
2388 lcTempCursor = GetUniqueFileName()
2389
2390 SELECT po_num,edi_store,edi_center,upc,sku,pkey as controlpkey,hpkey,dpkey,fkey, ;
2391 total_qty,line_status,division,style,color_code,lbl_code,dimension,size_desc ;
2392 FROM (pcStoreSDQ) ;
2393 WHERE fkey = llInterHeaderPkey ;
2394 INTO CURSOR (lcTempCursor)
2395
2396 IF USED(lcTempCursor) AND RECCOUNT(lcTempCursor) > 0
2397
2398 SELECT (lcTempCursor)
2399 SCAN FOR fkey = llInterHeaderPkey
2400 SCATTER MEMVAR MEMO
2401
2402 IF EMPTY(m.pkey)
2403 llOKtoContinue= .F.
2404 EXIT
2405 ENDIF
2406
2407 m.fkey = llInterHeaderPkey
2408 m.pkey= v_NextPkey("ZZEOPRISDQ")
2409
2410 SELECT VzzeopriSDQ_Proc
2411 APPEND BLANK
2412 GATHER MEMVAR MEMO
2413 This.TimeStampDocument()
2414 ENDSCAN
2415
2416 USE IN SELECT(lcTempCursor)
2417 ENDIF
2418 ENDIF
2419 *=== TR 1074595 KISHORE 22-JAN-2014
2420
2421 *- TR 1098611 FH
2422 IF .lFrom850 AND NOT .lFrom945
2423 *--- TR 1100564 30-Nov-16 JPaul. Condition changed for filtering EDIH/EDID records
2424 *lcWHSEScanFor = "fkey == llInterHeaderPkey"
2425 lcWHSEScanFor = "whskey == lnIPO_Pkey"
2426 *=== TR 1100564 30-Nov-16 JPaul.
2427 ELSE
2428 lcWHSEScanFor = "ord_num == lnInterHeaderOrd_num"
2429 ENDIF
2430
2431 IF llHaveEDIH
2432 SELECT (pcWhseHeader)
2433
2434 SCAN FOR &lcWHSEScanFor
2435 SCATTER MEMVAR MEMO
2436 m.pkey= v_NextPkey("ZZEOPRIWHSE")
2437 m.table_name = 'ZZOORDRH'
2438 m.sln_line = ''
2439 SELECT Vzzeopriwhse_proc
2440 APPEND BLANK
2441 GATHER MEMVAR MEMO
2442 This.TimeStampDocument()
2443 ENDSCAN
2444 ENDIF
2445
2446 IF llHaveEDID
2447 SELECT (pcWhseDetail)
2448 SCAN FOR &lcWHSEScanFor
2449 SCATTER MEMVAR MEMO
2450 m.pkey= v_NextPkey("ZZEOPRIWHSE")
2451 m.table_name = 'ZZOORDRD'
2452 m.sln_line = ''
2453 SELECT Vzzeopriwhse_proc
2454 APPEND BLANK
2455 GATHER MEMVAR MEMO
2456 This.TimeStampDocument()
2457 ENDSCAN
2458 ENDIF
2459 *- TR 1098611 FH
2460
2461 If !llOKtoContinue
2462 Exit && header scan
2463 Endif
2464 * delete tran header when move to inter header
2465 Delete For pkey= tmpCurs.pkey In (pcTransHeader)
2466 Endscan && headers
2467
2468 * cleanup
2469 With This
2470 .TableClose("TmpCurs")
2471 .TableClose("tmpTermr")
2472 .TableClose("tmpShipr")
2473 .TableClose("tmpAddrs")
2474 *!* .TableClose("tmpStylr")
2475 *!* .TableClose("tmpColrr")
2476 *!* .TableClose("tmpDimer")
2477 .CloseTempAddressCursors()
2478 Endwith
2479
2480 If This.lUserInterface
2481 * Reset Thermometer
2482 This.ResetThermo()
2483 Endif
2484
2485 Select(lnOldSelect)
2486 Return llOKtoContinue
2487 Endproc
2488
2489 ************************************************************************************
2490 * UnloadToFlatFile
2491 ***********************************************************************************
2492 Procedure UnLoadToFlatFile
2493 Lparameters tcProcess, tcFlatFile, tcInterfaceHeader, tcInterfaceDetail, ;
2494 tcInterfaceComment, tcInterfaceAddress, tcFinalSDQ, tcSourceSDQ, tcFinalSLN, tcControlCursor, tcTRK, ;
2495 tcInterfaceWhse
2496 *- TR 1098611 FH - replace tcInterfaceWH, tcInterfaceWD with tcInterfaceWhse
2497 *--- TechRec 1058716 11-Jan-2012 jisingh Added tcInterfaceWH, tcInterfaceWD ===
2498 *--- TR 1030443 24-Apr-2008 Goutam. Added parameter tcTRK in abovelist
2499
2500 Local llRetVal, lcSQLString, lnOldSelect
2501 *--- TR 1010900 NH
2502 Local lnHandle
2503 lnHandle = 0
2504 *=== TR 1010900 NH
2505 llRetVal= .F.
2506 lnOldSelect= Select()
2507 With This
2508 .oLog.LogEntry("Unloading to Flat File")
2509
2510 *---TR 1031624 JUL-14-2008 BR
2511 USE IN SELECT("__tcFlatFileList")
2512 Create Cursor __tcFlatFileList(TempFlatfile memo, OutBoundFlatFile memo)
2513 *===TR 1031624 JUL-14-2008 BR
2514
2515
2516 * get Metadata for vendor table
2517 If .GetTranslationDefinition(EDI_ALL_TRANSACTION, "tcEmVend")
2518 * get Metadata for a process
2519
2520 If .GetTranslationDefinition(tcProcess, "tcEmData")
2521 * Create Metadata work tables
2522 .CreateMetaDataWorkTable("tcEmVend", "VENDOR", "tcoPRmwV")
2523 .CreateMetaDataWorkTable("tcEmData", "HEADER", "tcoPRmwH")
2524 .CreateMetaDataWorkTable("tcEmData", "ADDRESS", "tcoPRmwA")
2525 .CreateMetaDataWorkTable("tcEmData", "HDRNOTES", "tcoPRmHN")
2526 .CreateMetaDataWorkTable("tcEmData", "DETAIL", "tcoPRmwD")
2527 .CreateMetaDataWorkTable("tcEmData", "DTLNOTES", "tcoPRmDN")
2528
2529 *--- TR 1030443 22-Apr-2008 Goutam
2530 IF this.lFrom945
2531 .CreateMetaDataWorkTable("tcEmData", "TRK", "tcoPRmRK")
2532 ENDIF
2533 *=== TR 1030443 22-Apr-2008 Goutam
2534
2535 *- 1005605 06/15/04 YIK
2536 *- Add SLNs
2537 .CreateMetaDataWorkTable("tcEmData", "SLN", "tcoPRSLN")
2538 *=
2539
2540 *--- TechRec 1058716 11-Jan-2012 jisingh ---
2541 .CreateMetaDataWorkTable("tcEmData", "EDIH", "tcoPrEDIH")
2542 .CreateMetaDataWorkTable("tcEmData", "EDID", "tcoPrEDID")
2543 SELECT tcoPrEDIH
2544 INDEX ON fkey TAG fkey
2545
2546 SELECT tcoPrEDID
2547 INDEX ON fkey TAG fkey
2548 *=== TechRec 1058716 11-Jan-2012 jisingh ===
2549
2550 *--- 36646 01/16/03 YIK
2551 *- Add SDQs
2552 **Because we put the CreateSDQ routine inside datafound portion, it
2553 **will crash if we go here and call the routine ConvertToSDQ unconditionally.
2554
2555 If Used(tcSourceSDQ)
2556 .CreateMetaDataWorkTable("tcEmData", "SDQ", "tcoPRmSD")
2557 .ConvertToSDQ(tcInterfaceHeader, tcInterfaceDetail, ;
2558 tcInterfaceComment, tcInterfaceAddress, tcFinalSDQ, tcSourceSDQ)
2559 Endif
2560 *=== 36646
2561 * Open Flatfile and append to it
2562
2563 * remove previous tmp text file
2564 *lcTempFlatfile= tcFlatFile+ ".tmp"
2565 *--- TR 1010900 NH
2566 lnHandle = 0
2567
2568 *--- TR 1072318 12-Aug-2013 Partha ---
2569*!* If Not .Tie_setup
2570*!* lcTempFlatfile= Strtran(Upper(tcFlatFile), ".DAT", ".TMP")
2571*!* Delete File (lcTempFlatfile)
2572*!* lnHandle= .OpenFlatFile(lcTempFlatfile, True)
2573*!* Endif
2574*!* If .Tie_setup Or lnHandle> 0
2575 *=== TR 1072318 12-Aug-2013 Partha ===
2576
2577 *=== TR 1010900 NH
2578 * Populate data to metadata work tables from interface tables
2579 .ScatterGather(tcInterfaceHeader, "tcoPRmwH")
2580 *--- TR 1013007 NH
2581 *-- scatter gather records that has valid 855 ack status code.
2582 * .ScatterGather(tcInterfaceDetail, "tcoPRmwD")
2583 .ScatterGather_STATUS_855(tcInterfaceDetail, "tcoPRmwD",tcInterfaceHeader)
2584 *=== TR 1013007 NH
2585 Select tcoPRmwD
2586 Index On fkey + Style+color_code+lbl_code+Dimension+size_desc Tag fkey
2587
2588 *-.ScatterGather(tcInterfaceComment, "tcoPRmwC")
2589 .ScatterGather(tcInterfaceAddress, "tcoPRmwA")
2590 .ScatterGatherNotes(tcInterfaceHeader, "tcoPRmHN", 254)
2591 .ScatterGatherNotes(tcInterfaceDetail, "tcoPRmDN", 254)
2592
2593 * Mash/group all records to a single flatfile
2594 * If get here will return true if successfully create flatfile
2595 *--- 36646 01/16/03 YIK
2596 *- Add SDQs
2597 If Used(tcFinalSDQ)
2598 .ScatterGather(tcFinalSDQ, "tcoPRmSD")
2599 Endif
2600 *- 1005605 06/15/04 YIK
2601 If Used(tcFinalSLN)
2602 .ScatterGather(tcFinalSLN, "tcOPRSLN")
2603 Endif
2604
2605 *--- TR 1030443 22-Apr-2008 Goutam
2606 IF this.lFrom945 AND USED(tcTRK)
2607 .ScatterGather(tcTRK, "tcoPRmRK")
2608 ENDIF
2609 *=== TR 1030443 22-Apr-2008 Goutam
2610
2611 *--- TechRec 1058716 11-Jan-2012 jisingh ---
2612 *- TR 1098611 FH - change to tcInterfaceWhse, added forString
2613 IF USED(tcInterfaceWhse)
2614 .ScatterGather(tcInterfaceWhse, "tcoPrEDIH",,,"table_name = 'ZZOORDRH'")
2615 ENDIF
2616
2617 *- TR 1098611 FH - change to tcInterfaceWhse, added forString
2618 IF USED(tcInterfaceWhse)
2619 .ScatterGather(tcInterfaceWhse, "tcoPrEDID",,,"table_name = 'ZZOORDRD'")
2620 ENDIF
2621 *=== TechRec 1058716 11-Jan-2012 jisingh ===
2622
2623 *--- TR 1030443 23-Apr-2008 Goutam Added parameter "tcoPRmRK"
2624 *--- TechRec 1058716 11-Jan-2012 jisingh Added tcoPrEDIH, tcoPrEDID ===
2625 llRetVal= .CreateFlatfile(tcProcess, "tcoPRmwV", ;
2626 "tcoPRmwH", "tcoPRmwD", "tcoPRmwC", "tcoPRmwA", "tcoPRmHN", ;
2627 "tcoPRmDN","tcoPRmSD", "tcOPRSLN",lnHandle, tcControlCursor, "tcoPRmRK", "tcoPrEDIH", "tcoPrEDID")
2628 *= 1005605
2629 *--- TR 1010900 NH
2630
2631 *--- TR 1072318 12-Aug-2013 Partha ---
2632*!* If Not .Tie_setup
2633*!* Fclose(lnHandle)
2634*!*
2635*!* * append to 855.dat form 855.dat.tmp
2636*!* If llRetVal
2637*!* *---TR 1031624 JUL-14-2008 BR
2638*!* *- Comment out to prevent flat file from being generated if table update fails
2639*!* *llRetVal=.CopyToFlatFile(lcTempFlatfile, tcFlatFile)
2640*!* INSERT INTO __tcFlatFileList(TempFlatfile,OutBoundFlatFile) VALUES (lcTempFlatfile, tcFlatFile)
2641*!* *===TR 1031624 JUL-14-2008 BR
2642*!* Endif
2643*!* ENDIF
2644 *=== TR 1072318 12-Aug-2013 Partha ===
2645
2646 *=== TR 1010900 NH
2647
2648 *--- TR 1072318 12-Aug-2013 Partha ---
2649*!* ENDIF
2650 *=== TR 1072318 12-Aug-2013 Partha ===
2651
2652 Endif
2653 Endif
2654 Endwith
2655 Select (lnOldSelect)
2656 Return llRetVal
2657 Endproc
2658 ************************************************************************************
2659 * group all records from metadata work tables to a flat text file
2660 * Notes: all datatype at metadata work layer will be CHARACTER
2661 ***********************************************************************************
2662 *- 1005605 06/15/04 YIK
2663 *- Added tcFinalSLN
2664 *============================================================
2665
2666 Procedure CreateFlatfile
2667 Lparameter tcProcess, tcWorkVendor, tcWorkHeader, ;
2668 tcWorkDetail, tcWorkComment, tcWorkAddress, tcHeaderNotes, tcDetailNotes, tcFinalSDQ, ;
2669 tcFinalSLN, pnHandle, tcControlCursor, tcoPRmRK, tcFinalEDIH, tcFinalEDID
2670 *--- TechRec 1058716 11-Jan-2012 jisingh Added tcFinalEDIH, tcFinalEDID ===
2671 *--- TR 1030443 23-Apr-2008 Goutam Added above parameter tcoPRmRK
2672
2673 Local llRetVal, lnSelect
2674
2675 llRetVal = True
2676 lnSelect = Select()
2677 With This
2678 *- 1005605 06/145/04 YIK
2679 If Used(tcFinalSLN)
2680 .lHaveSLN = (This.CountTotalRecs (tcFinalSLN)>0)
2681 Endif
2682 *=
2683 llRetVal = .FinalDataProcessing(tcProcess, tcWorkVendor, tcWorkHeader, ;
2684 tcWorkDetail, tcWorkComment, tcWorkAddress, tcHeaderNotes, tcDetailNotes, ;
2685 tcFinalSDQ, tcFinalSLN, tcControlCursor)
2686
2687 * --- TR 1044514 3/25/10 CM
2688 *llRetVal = llRetVal And .GroupAndSumData(tcWorkHeader, tcWorkDetail, ;
2689 tcWorkComment, tcWorkAddress, tcHeaderNotes, tcDetailNotes, tcFinalSLN)
2690 llRetVal = llRetVal And .GroupAndSumData(tcWorkHeader, tcWorkDetail, ;
2691 tcWorkComment, tcWorkAddress, tcHeaderNotes, tcDetailNotes, tcFinalSLN, tcControlCursor)
2692 * === TR 1044514 3/25/10 CM
2693
2694 *--- TR 1030443 23-Apr-2008 Goutam Added parameter tcoPRmRK
2695 *--- TechRec 1058716 11-Jan-2012 jisingh Added tcFinalEDIH, tcFinalEDID ===
2696 llRetVal = llRetVal And .WriteDataToFlatFile(tcWorkVendor, tcWorkHeader, ;
2697 tcWorkDetail, tcWorkComment, tcWorkAddress, tcHeaderNotes, tcDetailNotes, tcFinalSDQ, ;
2698 tcFinalSLN, pnHandle, tcControlCursor, tcoPRmRK, tcProcess, tcFinalEDIH, tcFinalEDID) && TR 1043557 DEC-4-09 BR - Added tcProcess
2699 Endwith
2700
2701 Select (lnSelect)
2702 Return llRetVal
2703 Endfunc
2704
2705
2706 *============================================================
2707
2708 Function FinalDataProcessing
2709 Lparameter tcProcess, tcWorkVendor, tcWorkHeader, ;
2710 tcWorkDetail, tcWorkComment, tcWorkAddress, tcHeaderNotes, tcDetailNotes, ;
2711 tcFinalSDQ, tcFinalSLN, tcControlCursor
2712 Local llRetVal, lnSelect, lcTempCursor, lcHeaderPkeysList, lcDetailPkeysList, ;
2713 lcSkuList, lcCurHPkey, lcCurDPkey
2714
2715 llRetVal = True
2716 lnSelect = Select()
2717 lcTempCursor = GetUniqueFileName()
2718 lcHeaderPkeysList = GetUniqueFileName()
2719 lcSkuList = GetUniqueFileName()
2720 lcDetailPkeysList = GetUniqueFileName()
2721
2722 With This
2723 *---Insert Verndor information into tcWorkVendor
2724
2725 Select h.customer, h.division, c.* ;
2726 from (tcWorkHeader) h, (tcControlCursor) c ;
2727 Where Alltrim(h.customer)==Alltrim(c.customer) ;
2728 And Alltrim(h.division)==Alltrim(c.division) ;
2729 Into Cursor (lcTempCursor) Group By h.customer, h.division
2730
2731 Select (lcTempCursor)
2732 Scan
2733 m.vnd_id = vnd_id
2734 m.vnd_vers = vnd_vers
2735 m.vnd_tran = tcProcess
2736
2737 Insert Into (tcWorkVendor) From Memvar
2738 Endscan
2739
2740 *** Manipulate pkeys, fkeys in Notes, Comments, Addresses, Details,
2741 *** Detail Notes and SDQs for those combinations of customer and division
2742 *** that need to be grouped by po_num
2743 *** Please note that we can only handle those records that Sku_UPC="N"
2744 *- 1005605 07/21/04 YIK
2745 *- Added h.edi_center to group by it.
2746 *- 1006635 08/11/04 YIK - Per May Co., don't group by edi center.
2747 *- send any one as ship-to
2748 *!* Select h.customer, h.division, h.po_num, h.edi_center ;
2749 *!* from (tcWorkHeader) h, (tcControlCursor) c ;
2750 *!* Where Alltrim(h.customer)==Alltrim(c.customer) ;
2751 *!* And Alltrim(h.division)==Alltrim(c.division) ;
2752 *!* And Alltrim(c.ack_by)=="P" And Not Alltrim(c.sku_upc)=="N" ;
2753 *!* Into Cursor (lcTempCursor) Group By h.customer, h.division, h.po_num, h.edi_center
2754
2755 *--- TR 1025228 3-Jul-2007 Goutam
2756 *Select h.customer, h.division, h.po_num ;
2757 from (tcWorkHeader) h, (tcControlCursor) c ;
2758 Where Alltrim(h.customer)==Alltrim(c.customer) ;
2759 And Alltrim(h.division)==Alltrim(c.division) ;
2760 And Alltrim(c.ack_by)=="P" And Not Alltrim(c.sku_upc)=="N" ;
2761 Into Cursor (lcTempCursor) Group By h.customer, h.division, h.po_num
2762
2763 *=== TR 1031304 03/10/2008 TT - Changed the ==P... line into WHERE clause
2764*!* Select distinct h.customer, h.division, h.oVnd_key, h.po_num ;
2765*!* from (tcWorkHeader) h LEFT JOIN (tcControlCursor) c ;
2766*!* ON Alltrim(h.customer)=Alltrim(c.customer) ;
2767*!* And Alltrim(h.division)=Alltrim(c.division) ;
2768*!* And Alltrim(h.oVnd_key)=Alltrim(c.oVnd_key) ;
2769*!* LEFT JOIN (tcControlCursor) c2 ;
2770*!* ON Alltrim(h.customer)=Alltrim(c2.customer) ;
2771*!* And Alltrim(h.division)=Alltrim(c2.division) ;
2772*!* And c2.oVnd_key = ' ' ;
2773*!* And Alltrim(c2.ack_by)=="P" And Not Alltrim(c2.sku_upc)=="N" ;
2774*!* Into Cursor (lcTempCursor)
2775
2776 Select distinct h.customer, h.division, h.oVnd_key, h.po_num ;
2777 from (tcWorkHeader) h LEFT JOIN (tcControlCursor) c ;
2778 ON Alltrim(h.customer)=Alltrim(c.customer) ;
2779 And Alltrim(h.division)=Alltrim(c.division) ;
2780 And Alltrim(h.oVnd_key)=Alltrim(c.oVnd_key) ;
2781 LEFT JOIN (tcControlCursor) c2 ;
2782 ON Alltrim(h.customer)=Alltrim(c2.customer) ;
2783 And Alltrim(h.division)=Alltrim(c2.division) ;
2784 And c2.oVnd_key = ' ' ;
2785 where (Alltrim( c.ack_by)=="P" And Not Alltrim( c.sku_upc)=="N");
2786 or (Alltrim(c2.ack_by)=="P" And Not Alltrim(c2.sku_upc)=="N") ;
2787 Into Cursor (lcTempCursor)
2788
2789 *=== TR 1031304 03/10/2008 TT - Changed the ==P... line into WHERE clause
2790 *=== TR 1025228 3-Jul-2007 Goutam
2791
2792 *= 1006635
2793 Select (lcTempCursor)
2794 Scan
2795 Scatter Memvar
2796 *** For each of the customer, division and po_num, get header pkey lists
2797 *- 1006635 08/11/04 YIK - Per May Co., don't group by edi center.
2798 *!* Select pkey from (tcWorkHeader) ;
2799 *!* where Alltrim(customer)==Alltrim(m.customer) ;
2800 *!* And Alltrim(division)==Alltrim(m.division) ;
2801 *!* And Alltrim(po_num)==Alltrim(m.po_num) ;
2802 *!* And Alltrim(edi_center) == Alltrim(m.edi_center) ;
2803 *!* Into Cursor (lcHeaderPkeysList) Group by pkey order by pkey asc
2804
2805 *--- TR 1025228 3-Jul-2007 Goutam
2806 *Select pkey From (tcWorkHeader) ;
2807 where Alltrim(customer)==Alltrim(m.customer) ;
2808 And Alltrim(division)==Alltrim(m.division) ;
2809 And Alltrim(po_num)==Alltrim(m.po_num) ;
2810 Into Cursor (lcHeaderPkeysList) Group By pkey Order By pkey Asc
2811 Select pkey From (tcWorkHeader) ;
2812 where Alltrim(customer)==Alltrim(m.customer) ;
2813 And Alltrim(division)==Alltrim(m.division) ;
2814 And Alltrim(oVnd_Key)==Alltrim(m.oVnd_Key) ;
2815 And Alltrim(po_num)==Alltrim(m.po_num) ;
2816 Into Cursor (lcHeaderPkeysList) Group By pkey Order By pkey Asc
2817 *=== TR 1025228 3-Jul-2007 Goutam
2818
2819 *= 1005605 YIK
2820 Select (lcHeaderPkeysList)
2821 Go Top
2822 lcCurHPkey = pkey
2823 ***For each of header pkey,
2824 *** 1: change the pKey of header to the first pKey of the same group,
2825 *** 2: change the fkey of header notes, comments, addresses, details, SDQs to the first pKey
2826 *** 3: Blank out store and ord_num because right now we are grouping by po_num
2827 Scan
2828 Replace Edi_Store With '', ord_num With '', pkey With lcCurHPkey ;
2829 For Alltrim(pkey)==Alltrim(&lcHeaderPkeysList..pkey) In (tcWorkHeader)
2830
2831 If Alltrim(pkey) == Alltrim(lcCurHPkey)
2832 Loop &&Don't need to do the rest
2833 Endif
2834
2835 If Used(tcWorkComment)
2836 Replace fkey With lcCurHPkey ;
2837 For Alltrim(fkey)==Alltrim(&lcHeaderPkeysList..pkey) In (tcWorkComment)
2838 Endif
2839
2840 Replace fkey With lcCurHPkey ;
2841 For Alltrim(fkey)==Alltrim(&lcHeaderPkeysList..pkey) In (tcWorkAddress)
2842
2843 Replace pkey With lcCurHPkey ;
2844 For Alltrim(pkey)==Alltrim(&lcHeaderPkeysList..pkey) In (tcHeaderNotes)
2845
2846 Replace fkey With lcCurHPkey ;
2847 For Alltrim(fkey)==Alltrim(&lcHeaderPkeysList..pkey) In (tcWorkDetail)
2848 *- 12/21/06 1020717 YIK
2849 If Used(tcFinalSDQ)
2850
2851 Replace fkey With lcCurHPkey ;
2852 For Alltrim(fkey)==Alltrim(&lcHeaderPkeysList..pkey) In (tcFinalSDQ)
2853 ENDIF
2854 Endscan
2855
2856 ***Get Sku list that belong to current lcCurHPkey. (We already update the fkey in detail)
2857 Select Style, color_code, lbl_code, Dimension, size_desc ;
2858 From (tcWorkDetail) Where Alltrim(fkey)==Alltrim(lcCurHPkey) ;
2859 Into Cursor (lcSkuList) ;
2860 Group By Style, color_code, lbl_code, Dimension, size_desc ;
2861 Order By Style, color_code, lbl_code, Dimension, size_desc
2862
2863 ***For each of the skus that belong to current po(lcCurHPkey),
2864 *** get detailPkey list and pick the first pkey
2865 Select (lcSkuList)
2866 Scan
2867 Scatter Memvar
2868 Select pkey From (tcWorkDetail) ;
2869 where Alltrim(fkey)==Alltrim(lcCurHPkey) And ;
2870 Alltrim(Style)==Alltrim(m.Style) And ;
2871 Alltrim(color_code)==Alltrim(m.color_code) And ;
2872 Alltrim(lbl_code)==Alltrim(m.lbl_code) And ;
2873 Alltrim(Dimension)==Alltrim(m.Dimension) And ;
2874 Alltrim(size_desc)==Alltrim(m.size_desc) ;
2875 Into Cursor (lcDetailPkeysList) ;
2876 Group By pkey Order By pkey Asc
2877
2878 ***For each of detail pKey,
2879 *** 1: replace pKey in detail to the first pKey we hit
2880 *** 2: replace fKey of detail notes to the first pKey
2881 Select (lcDetailPkeysList)
2882 lcCurDPkey = pkey
2883 Scan
2884 If Alltrim(pkey) == Alltrim(lcCurDPkey)
2885 Loop &&Don't need to do anything for the first pkey
2886 Endif
2887
2888 Replace pkey With lcCurDPkey ;
2889 For Alltrim(pkey)==Alltrim(&lcDetailPkeysList..pkey) In (tcWorkDetail)
2890
2891 Replace pkey With lcCurDPkey ;
2892 For Alltrim(pkey)==Alltrim(&lcDetailPkeysList..pkey) In (tcDetailNotes)
2893
2894 *- 1005605 06/15/04 YIK
2895 Replace itm_pkey With lcCurDPkey ;
2896 For Alltrim(itm_pkey)==Alltrim(&lcDetailPkeysList..pkey) In (tcFinalSLN)
2897 *=
2898 Endscan
2899
2900 Select (lcSkuList)
2901 Endscan
2902
2903
2904 Select (lcTempCursor)
2905 Endscan
2906
2907 .TableClose(lcTempCursor)
2908 .TableClose(lcHeaderPkeysList)
2909 .TableClose(lcSkuList)
2910 .TableClose(lcDetailPkeysList)
2911 Endwith
2912
2913 Select (lnSelect)
2914 Return llRetVal
2915 Endfunc
2916
2917 *============================================================
2918
2919 Function GroupAndSumData
2920 * --- TR 1044514 3/25/10 CM --- Added tcControlCursor
2921 Lparameters tcWorkHeader, tcWorkDetail, tcWorkComment, tcWorkAddress, ;
2922 tcHeaderNotes, tcDetailNotes, tcFinalSLN, tcControlCursor
2923 Local llRetVal, lnSelect, lcCursorName
2924
2925 llRetVal = True
2926 lnSelect = Select()
2927 lcCursorName = GetUniqueFileName()
2928
2929 With This
2930 *** For those orders that need to be sent out by po_num, we already blank out ;
2931 *** ord_num. So we can group by ord_num here.
2932 *- 1005605 07/21/04 YIK
2933 *- group by edi_center
2934 *- 1006635 08/11/04 YIK - Per May Co., don't group by edi center.
2935 *!* Select * from (tcWorkHeader) into cursor (lcCursorName) ;
2936 *!* group by customer, division, po_num, edi_center, ord_num
2937 *--- TR 1025228 Goutam
2938 *Select * From (tcWorkHeader) Into Cursor (lcCursorName) ;
2939 group By customer, division, po_num, ord_num
2940
2941 *--- TR 1038204 JAN-22-2008 BR
2942 *Select * From (tcWorkHeader) Into Cursor (lcCursorName) ;
2943 group By customer, division, oVnd_key, po_num, ord_num
2944 *- 1077979 07/08/14 YIK
2945 *- Add ord_num to grouping in case the same store is repeated per PO
2946 Select * From (tcWorkHeader) Into Cursor (lcCursorName) ;
2947 group By customer, division, oVnd_key, po_num, edi_store, ord_num
2948 *=== TR 1038204 JAN-22-2008 BR
2949
2950 *=== TR 1025228 Goutam
2951
2952 *=
2953 .TableClose(tcWorkHeader)
2954 MakeCursorWritable(lcCursorName, tcWorkHeader)
2955
2956 If Used(tcWorkComment)
2957 Select Distinct * From (tcWorkComment) Into Cursor (lcCursorName)
2958 .TableClose(tcWorkComment)
2959 MakeCursorWritable(lcCursorName, tcWorkComment)
2960 Endif
2961
2962 Select Distinct * From (tcWorkAddress) Into Cursor (lcCursorName)
2963 .TableClose(tcWorkAddress)
2964 MakeCursorWritable(lcCursorName, tcWorkAddress)
2965
2966 Select Distinct * From (tcHeaderNotes) Into Cursor (lcCursorName)
2967 .TableClose(tcHeaderNotes)
2968 MakeCursorWritable(lcCursorName, tcHeaderNotes)
2969
2970 Select Distinct * From (tcDetailNotes) Into Cursor (lcCursorName)
2971 .TableClose(tcDetailNotes)
2972 MakeCursorWritable(lcCursorName, tcDetailNotes)
2973
2974 *- 1005605 06/15/04 YIK
2975 If .lHaveSLN
2976 Select Distinct * From (tcFinalSLN) Into Cursor (lcCursorName)
2977 .TableClose(tcFinalSLN)
2978 MakeCursorWritable(lcCursorName, tcFinalSLN)
2979 Select (tcFinalSLN)
2980 Index On itm_pkey Tag pkey
2981 Endif
2982 *=
2983 *--- TR 1030443 22-Apr-2008 Goutam
2984 IF NOT this.lFrom945
2985 *=== TR 1030443 22-Apr-2008 Goutam
2986 *--- TR 1035793 07-Oct-2008 TEJAS : Added Line_seq to Group By
2987 Select *, ;
2988 sum(Iif(Alltrim(line_status)=="P", Val(total_qty), 0)) As CalPick_QTY, ;
2989 sum(Iif(Alltrim(line_status)=="I", Val(total_qty), 0)) As CalInvoice_QTY, ;
2990 sum(Iif(Alltrim(line_status)=="C", Val(total_qty), 0)) As CalCancel_QTY, ;
2991 Sum(Val(total_qty)) As CalTotal_Qty ;
2992 from (tcWorkDetail) ;
2993 Into Cursor (lcCursorName) ;
2994 Group By fkey, Line_Seq, Style, color_code, lbl_code, Dimension, size_desc
2995 *--Group By fkey, Style, color_code, lbl_code, Dimension, size_desc
2996 *=== TR 1035793 07-Oct-2008 TEJAS : Added Line_seq to Group By
2997 Delete From (tcWorkDetail)
2998 Select (lcCursorName)
2999 Scan
3000 Scatter Memvar
3001 m.total_qty = Trans(m.CalTotal_Qty, '9999999999')
3002 m.OpenPickQty = Trans(m.CalPick_QTY, '9999999999')
3003 m.InvoiceQty = Trans(m.CalInvoice_QTY, '9999999999')
3004 m.CancelQty = Trans(m.CalCancel_QTY, '9999999999')
3005
3006 Insert Into (tcWorkDetail) From Memvar
3007 Endscan
3008 *--- TR 1030443 22-Apr-2008 Goutam
3009 ELSE
3010 *--- TR 1035793 07-Oct-2008 TEJAS : Added Line_seq to Group By
3011 Select *, ;
3012 sum(Iif(Alltrim(line_status)<>"I", Val(total_qty), 0)) As NotShipped_QTY, ;
3013 sum(Iif(Alltrim(line_status)=="I", Val(total_qty), 0)) As Shipped_QTY, ;
3014 Iif(Alltrim(line_status)<>"I", 'N', 'Y') As Shipped ;
3015 from (tcWorkDetail) ;
3016 Into Cursor (lcCursorName) ;
3017 Group By fkey, Line_Seq, Style, color_code, lbl_code, Dimension, size_desc, shipped
3018 *--Group By fkey, Style, color_code, lbl_code, Dimension, size_desc, shipped
3019 *=== TR 1035793 07-Oct-2008 TEJAS : Added Line_seq to Group By
3020 Delete From (tcWorkDetail)
3021 Select (lcCursorName)
3022 SCAN
3023 SCATTER MEMVAR
3024 IF Shipped = 'Y'
3025 m.total_qty = Trans(m.Shipped_QTY, '9999999999')
3026 m.OpenPickQty = TRANSFORM(0)
3027 m.InvoiceQty = m.Total_Qty
3028 m.line_status = 'I'
3029 ELSE
3030 m.total_qty = Trans(m.NotShipped_QTY, '9999999999')
3031 m.line_status = 'O'
3032 m.OpenPickQty = m.Total_Qty
3033 m.InvoiceQty = TRANSFORM(0)
3034 ENDIF
3035 INSERT INTO (tcWorkDetail) FROM MEMVAR
3036 ENDSCAN
3037 ENDIF
3038 *=== TR 1030443 22-Apr-2008 Goutam
3039
3040 * --- TR 1044514 3/25/10 CM/NSD
3041 IF .lVMI
3042 .GroupAndSumDataForVMI(tcWorkDetail, tcWorkHeader, tcControlCursor, tcFinalSLN)
3043 ENDIF
3044 * === TR 1044514 3/25/10 CM/NSD
3045
3046 .TableClose(lcCursorName)
3047 Endwith
3048
3049 Select (lnSelect)
3050 Return llRetVal
3051 Endfunc
3052
3053 *============================================================
3054
3055 Function WriteDataToFlatFile
3056 Lparameters tcWorkVendor, tcWorkHeader, ;
3057 tcWorkDetail, tcWorkComment, tcWorkAddress, tcHeaderNotes, ;
3058 tcDetailNotes, tcFinalSDQ, tcFinalSLN, pnHandle, tcControlCursor, tcoPRmRK, tcProcess, ; && TR 1043557 DEC-4-09 BR - ADDED: tcProcess
3059 tcFinalEDIH, tcFinalEDID
3060 *--- TechRec 1058716 11-Jan-2012 jisingh Added tcFinalEDIH, tcFinalEDID ===
3061 *--- TR 1030443 23-Apr-2008 Goutam Added above parameter tcoPRmRK
3062
3063 *--- TechRec 1058716 11-Jan-2012 jisingh ---
3064 LOCAL lnCurrentIPOPkey, lnDetIPOPkey
3065 *=== TechRec 1058716 11-Jan-2012 jisingh ===
3066 LOCAL lcCurEan &&TR 1073064 30-Aug-13 Venuk.
3067 Local llRetVal, lnSelect, lcString, lnCurrentPkey, lnDetPkey, lcCurSku, lcCurUpc
3068 *--- TR 1014084 NH
3069 Local lcPrevVnd_id, llCustomerChanged, llVndstring_req
3070 llCustomerChanged = .f.
3071 lcPrevVnd_id = ""
3072 llVndstring_req = .f.
3073
3074 *- TR 1074987 FH
3075 LOCAL lcPreviousVndQual, lcPreviousVndID
3076 lcPreviousVndQual = ""
3077 lcPreviousVndID = ""
3078 *- TR 1074987 FH
3079
3080 *=== TR 1014084 NH
3081 llRetVal = True
3082 lnSelect = Select()
3083 *--- TR 1010900 NH:
3084 Local lnHandle, lcEDIPath, lcPreviousCustomer, lcPreviousDivision, lcMac && TR 1043557 DEC-4-09 BR - ADDED: lcMac
3085 Store "" To lcPreviousCustomer, lcPreviousDivision
3086 lcEDIPath = This.GetEDIFlatFileDirectory("Outbound")
3087 lnHandle = 0
3088
3089 *-TR 1087742 FH
3090 STORE ',' TO lcAltFileNameList
3091 lNewFile = .F.
3092
3093 Select (tcWorkHeader)
3094
3095 Index On customer + division Tag Custdiv
3096
3097 *=== TR 1010900 NH
3098
3099 *--- TechRec 1058716 11-Jan-2012 jisingh ---
3100 LOCAL llHaveEDIH, llHaveEDID, lnCurrentOrdNum
3101 llHaveEDIH = USED(tcFinalEDIH) AND (This.CountTotalRecs(tcFinalEDIH) > 0)
3102 llHaveEDID = USED(tcFinalEDID) AND (This.CountTotalRecs(tcFinalEDID) > 0)
3103 *=== TechRec 1058716 11-Jan-2012 jisingh ===
3104
3105 *--- TR 1072318 12-Aug-2013 Partha ---
3106 lcAltFilename = ""
3107 *=== TR 1072318 12-Aug-2013 Partha ===
3108ASSERT .f.
3109 With This
3110 Select (tcWorkHeader)
3111 Scan
3112 lcString = ""
3113 *--- TR 1014084 NH
3114 llCustomerChanged = (customer <> lcPreviousCustomer)
3115 *=== TR 1014084 NH
3116
3117 Scatter Memvar
3118 Select (tcControlCursor)
3119
3120 *--- TR 1025228 6-Jul-2007 Goutam
3121 *Locate For Alltrim(customer)==Alltrim(m.customer) ;
3122 And Alltrim(division)==Alltrim(m.division)
3123 Locate For Alltrim(customer)==Alltrim(m.customer) ;
3124 And Alltrim(division)==Alltrim(m.division) ;
3125 And Alltrim(oVnd_Key)==Alltrim(m.oVnd_Key)
3126
3127 IF NOT FOUND()
3128 Locate For Alltrim(customer)==Alltrim(m.customer) ;
3129 And Alltrim(division)==Alltrim(m.division) ;
3130 And oVnd_Key = ' '
3131 ENDIF
3132 *=== TR 1025228 6-Jul-2007 Goutam
3133
3134 If Not Found() &&Should always found
3135 llRetVal = .F.
3136 EXIT
3137 LOOP
3138 ENDIF
3139
3140 *--- TR 1072318 12-Aug-2013 Partha ---
3141 lcAltFilename = ALLTRIM(alt_file_name)
3142 *=== TR 1072318 12-Aug-2013 Partha ===
3143
3144 *- 1059585 FH
3145*!* Select (tcWorkVendor)
3146*!* Locate For Alltrim(customer)==Alltrim(m.customer) ;
3147*!* And Alltrim(division)==Alltrim(m.division)
3148 *- 1059585 FH
3149
3150 *-- TR 1043557 DEC-4-09 BR
3151 *Select (tcWorkVendor)
3152 *Locate For Alltrim(customer)==Alltrim(m.customer) ;
3153 * And Alltrim(division)==Alltrim(m.division)
3154
3155 *If Found() &&Should always found, because we generate tcWorkVendor by tcWorkHeader
3156 *--- TR 1010900 NH
3157 *If .Tie_setup And Not (customer == lcPreviousCustomer And division == lcPreviousDivision)
3158 * TR 1074987 FH - break using vnd_qual, and vnd_id
3159 *IF !(customer == lcPreviousCustomer And division == lcPreviousDivision)
3160 If !(vnd_qual == lcPreviousVndQual AND vnd_ID == lcPreviousVndID)
3161 lcPreviousCustomer = customer
3162 lcPreviousDivision = division
3163
3164 lcPreviousVndQual = vnd_qual && TR 1074987 FH
3165 lcPreviousVndID = vnd_id && TR 1074987 FH
3166
3167 lcMac= [vl_] + tcProcess + "cr" + [(Customer, "", "__Ecntrc", Division)]
3168 &lcMac
3169 IF Used('__Ecntrc')
3170 SELECT (tcWorkVendor)
3171 APPEN BLANK
3172 REPLACE vnd_id With __Ecntrc.vnd_id,;
3173 vnd_tran With tcProcess,;
3174 vnd_vers With __Ecntrc.vnd_vers In (tcWorkVendor)
3175 SELECT (tcWorkHeader)
3176 * === TR 1043557 DEC-4-09 BR
3177
3178 *--- TR 1072318 12-Aug-2013 Partha ---
3179*!* IF .Tie_setup && TR 1043557 DEC-4-09 BR
3180 *=== TR 1072318 12-Aug-2013 Partha ===
3181
3182 *- close previously opened file
3183 If lnHandle > 0
3184 Fclose(lnHandle)
3185 If llRetVal
3186 *---TR 1031624 JUL-14-2008 BR
3187 *- Comment out to prevent flat file from being generated if table update fails
3188 *llRetVal =.CopyToFlatFileInBlock(lcTempFlatfile, lcOutBoundFlatFile)
3189 *-TR 1087742 - FH
3190 *INSERT INTO __tcFlatFileList(TempFlatfile,OutBoundFlatFile) VALUES (lcTempFlatfile, lcOutBoundFlatFile)
3191 .InsertIntoFlatFileCursor("__tcFlatFileList",lcTempFlatfile,lcOutBoundFlatFile)
3192 *===TR 1031624 JUL-14-2008 BR
3193 Endif
3194 ENDIF
3195
3196 *- new flat file name
3197 lcOutBoundFlatFile = lcEDIPath + Alltrim(Evaluate(tcWorkVendor + ".vnd_id")) + "855.DAT"
3198 lcTempFlatfile = Strtran(Upper(lcOutBoundFlatFile), ".DAT", ".TMP")
3199
3200 *--- TR 1072318 12-Aug-2013 Partha ---
3201 IF !.Tie_setup
3202 lcOutBoundFlatFile = lcEDIPath + "855.DAT"
3203 lcTempFlatfile = Strtran(Upper(lcOutBoundFlatFile), ".DAT", ".TMP")
3204 ENDIF
3205 IF !EMPTY(ALLTRIM(lcAltFilename))
3206 lcOutBoundFlatFile = lcEDIPath + ALLTRIM(lcAltFilename) + ".DAT"
3207 lcTempFlatfile = Strtran(Upper(lcOutBoundFlatFile), ".DAT", ".TMP")
3208 ENDIF
3209 *=== TR 1072318 12-Aug-2013 Partha ===
3210
3211 *-TR 1087742 FH
3212 *- TR 1089432 FH - changed lcTempFlatfile to lcTempFileName
3213 lcTempFileName = STRTRAN(lcTempFlatfile,UPPER(lcEDIPath), "") && Get rid of path
3214 lcTempFileName = STRTRAN(lcTempFileName, ".TMP", "") && get rid of ext
3215 IF ATC(","+ALLTRIM(lcTempFileName)+",",lcAltFileNameList ) = 0
3216 lcAltFileNameList = lcAltFileNameList + ALLTRIM(lcTempFileName) + ","
3217 lNewFile = .T.
3218 ELSE
3219 lNewFile = .F.
3220 ENDIF
3221
3222 IF lNewFIle
3223 Delete File (lcTempFlatfile)
3224 ENDIF
3225 *-TR 1087742 FH
3226
3227 lnHandle = .OpenFlatFile(lcTempFlatfile, .T.)
3228 If lnHandle <= 0
3229 Loop
3230 ENDIF
3231 =FSEEK(lnHandle,0,2) && TR 1087742 FH
3232
3233 *--- TR 1072318 12-Aug-2013 Partha ---
3234*!* ENDIF && TR 1043557 DEC-4-09 BR -- .Tie_setup
3235 *=== TR 1072318 12-Aug-2013 Partha ===
3236
3237 *=== TR 1010900 NH
3238
3239 *- 1009713 3/3/05 YIK
3240
3241 *--- TR 1014084 NH
3242 *--- TR 1015001 NH remove special logic for VND string writing for TIE setup
3243 *!* llVndstring_req = .f.
3244 *!* IF .Tie_setup
3245 *!* IF llCustomerChanged OR (lcPrevVnd_id <> EVALUATE(tcWorkVendor + ".vnd_id"))
3246 *!* llVndstring_req = .t.
3247 *!* ENDIF
3248 *!* ELSE
3249 *!* llVndstring_req = .t. && 1043557 Dont need this anymore.
3250 *!* ENDIF
3251 *=== TR 1015001 NH
3252
3253 *-- 1043557 DEC-4-09 BR - Dont need this anymore.
3254 *IF llVndstring_req
3255 *lcString = EDI_VENDOR_TAG + lcDelimiter + .ConvertRecordToText(tcWorkVendor, llDelimited, lcDelimiter)
3256 *ENDIF
3257 *== 1043557 DEC-4-09 BR
3258
3259 *=== TR 1014084 NH
3260 *==
3261 Else
3262 llRetVal = .F.
3263 EXIT
3264 ENDIF && 1043557 DEC-4-09 BR - IF Used('__Ecntrc')
3265 ENDIF && 1043557 DEC-4-09 BR - !(customer == lcPreviousCustomer And division == lcPreviousDivision)
3266
3267 *-- TR 1043557 DEC-4-09 BR
3268 *llDelimited = (&tcControlCursor..EDI_Format = 'D')
3269 *lcDelimiter = Iif(llDelimited, &tcControlCursor..EDI_Delim, "")
3270 llDelimited = (__Ecntrc.EDI_Format = 'D')
3271 lcDelimiter = IIF(llDelimited, __Ecntrc.EDI_Delim, "")
3272
3273 lcString = EDI_VENDOR_TAG + lcDelimiter + .ConvertRecordToText(tcWorkVendor, llDelimited, lcDelimiter)
3274 *== TR 1043557 DEC-4-09 BR
3275
3276 Select (tcWorkHeader)
3277 *- 1009713 3/3/05 YIK
3278 lcString = lcString + EDI_HEADER_TAG + lcDelimiter + .ConvertRecordToText(tcWorkHeader, llDelimited, lcDelimiter)
3279
3280 lnCurrentPkey= &tcWorkHeader..pkey
3281 *--- TechRec 1058716 11-Jan-2012 jisingh ---
3282 lnCurrentIPOPkey = EVALUATE(tcWorkHeader + ".ipo_pkey")
3283 *=== TechRec 1058716 11-Jan-2012 jisingh ===
3284
3285 lnOrdNum = EVALUATE(tcWorkheader + '.ord_num') && TR 1089471 KISHORE 24-SEP-2015
3286
3287 *--- TR 1030443 23-Apr-2008 Goutam
3288 IF this.lFrom945 AND USED(tcoPRmRK)
3289 SELECT (tcoPRmRK)
3290
3291 * --- TR 1034281 NSD 6/24/08
3292 * should be scanning by fkey not pkey
3293 *SCAN FOR Alltrim(pkey) == Alltrim(lnCurrentPkey)
3294 SCAN FOR Alltrim(fkey) == Alltrim(lnCurrentPkey)
3295 * === TR 1034281 NSD 6/24/08
3296
3297 lcString = lcString + EDI_TRK_TAG + lcDelimiter + .ConvertRecordToText(tcoPRmRK, llDelimited, lcDelimiter)
3298 ENDSCAN
3299 ENDIF
3300 *=== TR 1030443 23-Apr-2008 Goutam
3301
3302 Select (tcHeaderNotes)
3303 Scan For Alltrim(pkey) == Alltrim(lnCurrentPkey)
3304 *- 1009713 3/3/05 YIK
3305 lcString = lcString + EDI_HNT_TAG + lcDelimiter + .ConvertRecordToText(tcHeaderNotes, llDelimited, lcDelimiter)
3306 Endscan
3307
3308 *****************************output comment string "CMT" anthing # ST/SF
3309 If Used(tcWorkComment)
3310 Select (tcWorkComment)
3311 Scan For Alltrim(fkey) == Alltrim(lnCurrentPkey)
3312 *- 1009713 3/3/05 YIK
3313 lcString = lcString + EDI_COMMENT_TAG + lcDelimiter + .ConvertRecordToText(tcWorkComment, llDelimited, lcDelimiter)
3314 Endscan
3315 Endif
3316
3317 *********************************output Address string "ADR"
3318 *No address for now per Paul
3319
3320 *--- TR 1027403 01-Oct-2007 Partha ---
3321 * ok, we need it now.
3322 SELECT (tcWorkAddress)
3323 SCAN For Alltrim(fKey) == Alltrim(lnCurrentPkey)
3324 lcString = lcString + EDI_ADDRESS_TAG + lcDelimiter + ;
3325 .ConvertRecordToText(tcWorkAddress, llDelimited, lcDelimiter)
3326 ENDSCAN
3327 *=== TR 1027403 01-Oct-2007 Partha ===
3328
3329 *--- TechRec 1058716 11-Jan-2012 jisingh ---
3330 IF llHaveEDIH
3331 SELECT (tcFinalEDIH)
3332 *--- TR 1089471 KISHORE 24-SEP-2015
3333 IF NOT .lFrom850
3334 SCAN FOR fkey= lnCurrentPkey && TR 1100564 30-Nov-16 JPaul. Changed the scan condition from ord_num = lnOrdNum because Ord_num is empty
3335 lcString = lcString + "EDIH" + lcDelimiter + .ConvertRecordToText(tcFinalEDIH, llDelimited, lcDelimiter)
3336 ENDSCAN
3337 ELSE
3338 *=== TR 1089471 KISHORE 24-SEP-2015
3339 SCAN FOR fkey = lnCurrentIPOPkey
3340 lcString = lcString + "EDIH" + lcDelimiter + .ConvertRecordToText(tcFinalEDIH, llDelimited, lcDelimiter)
3341 ENDSCAN
3342 ENDIF && TR 1089471
3343 ENDIF
3344 *=== TechRec 1058716 11-Jan-2012 jisingh ===
3345
3346 Select (tcWorkDetail)
3347 Scan For Alltrim(fkey) == Alltrim(lnCurrentPkey)
3348 lcCurSku = Alltrim(sku)
3349 lcCurUpc = Alltrim(upc)
3350 lcCurEan = Alltrim(ean) &&TR 1073064 30-Aug-13 Venuk.
3351 *- 1009713 3/3/05 YIK
3352 lcString = lcString + EDI_DETAIL_TAG + lcDelimiter + .ConvertRecordToText(tcWorkDetail, llDelimited, lcDelimiter)
3353
3354 lnDetPkey = &tcWorkDetail..pkey
3355 *--- TechRec 1058716 11-Jan-2012 jisingh ---
3356 lnDetIPOPkey = EVALUATE(tcWorkDetail + ".ipo_pkey")
3357 *=== TechRec 1058716 11-Jan-2012 jisingh ===
3358
3359 *No detail notes for now per Paul
3360 *SELECT (tcDetailNotes)
3361 *SCAN FOR Alltrim(pKey) == Alltrim(lnDetPkey)
3362 * lcString = lcString + EDI_DNT_TAG + .ConvertRecordToText(tcDetailNotes)
3363 *ENDSCAN
3364 *- 1020956 12/08/06 YIK
3365 *- added ..AND USED(tcFinalSDQ)
3366
3367 If &tcControlCursor..ack_by = 'P' And Not Alltrim(&tcControlCursor..sku_upc)=="N" ;
3368 AND USED(tcFinalSDQ)
3369
3370 If Alltrim(&tcControlCursor..sku_upc) = "S"
3371 lcSDQScanFor = "Alltrim(fkey) == Alltrim(lnCurrentPkey) AND Alltrim(Sku)==Alltrim(lcCurSku)"
3372 ELSE
3373 IF Alltrim(&tcControlCursor..sku_upc) = "U" &&TR 1073064 30-Aug-13 Venuk.
3374 lcSDQScanFor = "Alltrim(fkey) == Alltrim(lnCurrentPkey) AND Alltrim(UPC)==Alltrim(lcCurUPC)"
3375 *--- TR 1073064 30-Aug-13 Venuk.
3376 ELSE
3377 lcSDQScanFor = "Alltrim(fkey) == Alltrim(lnCurrentPkey) AND Alltrim(EAN)==Alltrim(lcCurEan )"
3378 ENDIF
3379 *=== TR 1073064 30-Aug-13 Venuk.
3380 Endif
3381
3382 Select (tcFinalSDQ)
3383 Scan For &lcSDQScanFor
3384 *- 1009713 3/3/05 YIK
3385 lcString = lcString + EDI_SDQ_TAG + lcDelimiter + .ConvertRecordToText(tcFinalSDQ, llDelimited, lcDelimiter)
3386 Endscan
3387 Endif
3388
3389 Select (tcWorkDetail)
3390 *- 1005605 06/15/04 YIK
3391 If .lHaveSLN
3392 *- 1009713 3/3/05 YIK
3393 .BuildSLNString(tcWorkDetail, tcFinalSLN, "Pkey", @lcString, llDelimited, lcDelimiter)
3394 Endif
3395 *=
3396
3397 *--- TechRec 1058716 11-Jan-2012 jisingh ---
3398 IF llHaveEDID
3399 SELECT (tcFinalEDID)
3400 *--- TechRec 1089471 29-Oct-2015 TSV---
3401 IF NOT .lFrom850
3402 SCAN FOR fkey = lnDetPkey
3403 lcString = lcString + "EDID" + lcDelimiter + .ConvertRecordToText(tcFinalEDID, llDelimited, lcDelimiter)
3404 ENDSCAN
3405 ELSE
3406 *=== TechRec 1089471 29-Oct-2015 TSV===
3407 SCAN FOR fkey = lnDetIPOPkey
3408 lcString = lcString + "EDID" + lcDelimiter + .ConvertRecordToText(tcFinalEDID, llDelimited, lcDelimiter)
3409 ENDSCAN
3410 ENDIF &&--- TechRec 1089471 29-Oct-2015 TSV===
3411 ENDIF
3412 *=== TechRec 1058716 11-Jan-2012 jisingh ===
3413
3414 Endscan && tcWorkDetail
3415
3416 If !Empty(lcString)
3417 *--- 36646 02/04/03 YIK - Replace / / and 01/01/1900 with blanks.
3418 .PrepareEmptyDateForGentran (@lcString)
3419 *--- TR 1010900 NH : Writes all info of each header into flat file
3420
3421 *--- TR 1072318 12-Aug-2013 Partha ---
3422*!* If .Tie_setup
3423 *=== TR 1072318 12-Aug-2013 Partha ===
3424 If Fwrite(lnHandle, lcString) = 0
3425 lRetVal = .F.
3426 Exit
3427 Endif
3428 *--- TR 1072318 12-Aug-2013 Partha ---
3429*!* Else
3430*!* If Fwrite(pnHandle, lcString) = 0
3431*!* lRetVal = .F.
3432*!* Exit
3433*!* Endif
3434*!* ENDIF
3435 *=== TR 1072318 12-Aug-2013 Partha ===
3436
3437 *=== TR 1010900 NH
3438 Endif
3439 Select (tcWorkHeader)
3440 *--- TR 1014084 NH
3441 lcPrevVnd_id = EVALUATE(tcWorkVendor + ".vnd_id")
3442 *=== TR 1014084 NH
3443 Endscan && tcWorkHeader
3444 *--- TR 1010900 NH
3445 *--- following copytoflatfileInBlock is need because program will exit scan before
3446 *--- adding last tempfile header record to .dat file
3447
3448 *--- TR 1072318 12-Aug-2013 Partha ---
3449*!* If .Tie_setup And lnHandle > 0
3450
3451 If lnHandle > 0
3452 *=== TR 1072318 12-Aug-2013 Partha ===
3453
3454 *- close previously opened file
3455 Fclose(lnHandle)
3456 If llRetVal
3457 *---TR 1031624 JUL-14-2008 BR
3458 *- Comment out to prevent flat file from being generated if table update fails
3459 *llRetVal =.CopyToFlatFileInBlock(lcTempFlatfile, lcOutBoundFlatFile)
3460 *-TR 1087742 FH
3461 *INSERT INTO __tcFlatFileList(TempFlatfile,OutBoundFlatFile) VALUES (lcTempFlatfile, lcOutBoundFlatFile)
3462 .InsertIntoFlatFileCursor("__tcFlatFileList",lcTempFlatfile,lcOutBoundFlatFile)
3463 *===TR 1031624 JUL-14-2008 BR
3464 Endif
3465 Endif
3466 *=== TR 1010900 NH
3467 Endwith
3468
3469 Select (lnSelect)
3470 Return llRetVal
3471 Endfunc
3472
3473 *============================================================
3474
3475 *-- Procedure CreateFlatfileBak
3476 *-- Lparameter tcProcess, tcWorkVendor, tcWorkHeader, ;
3477 *-- tcWorkDetail, tcWorkComment, tcWorkAddress, tcHeaderNotes, tcDetailNotes, tcFinalSDQ, ;
3478 *-- pnHandle, tcControlCursor
3479
3480 *-- Local llRetVal, lnOldSelect, lnCurrentPkey, llHaveComment, llHaveAddress, lcPreviousCustomer,;
3481 *-- lcPreviousDivision, lcMac, lcString, lcSetCentury, lcPreviousPONum, lcPreviousOrdNum, ;
3482 *-- lc5KeyValue, lnTotal_qty, lnOpenPickQty, lnInvoiceQty, lnCancelQty, ;
3483 *-- lcSDQScanFor, lcCurSku, lcCurUpc
3484 *-- This.oLog.LogEntry("Creating Flat File")
3485 *-- llRetVal= .T.
3486 *-- lcPreviousCustomer= ""
3487 *-- lcPreviousDivision=""
3488 *-- lcPreviousPONum = ""
3489 *-- lcPreviousOrdNum=""
3490 *-- Local lcS_PreviousCustomer,lcS_PreviousShipper,lcS_PreviousShip_date,;
3491 *-- lcS_PreviousShip_to,lcS_PreviousBill_num,lcS_PreviousLocation, lcO_PreviousBill_num,;
3492 *-- lcO_PreviousOrd_num
3493 *-- lcS_PreviousCustomer= ""
3494 *-- lcS_PreviousDivision= ""
3495 *-- lcO_PreviousOrd_num= ""
3496
3497 *-- *-llHaveComment= (recc(tcWorkComment)>0)
3498 *-- llHaveAddress= (recc(tcWorkAddress)>0)
3499 *-- lnOldSelect= Select()
3500
3501 *-- * need order by for order level break
3502 *-- Select * From (tcWorkHeader) ;
3503 *-- GROUP BY customer, division, po_num, ord_num ;
3504 *-- Order By customer, division, po_num, ord_num ;
3505 *-- Into Cursor _tmpHdrCrs
3506 *-- This.MakeCursorWritable("_tmpHdrCrs", "tmpCurs")
3507 *-- Select tmpCurs
3508
3509 *-- With This
3510 *-- Select tmpCurs
3511 *-- Scan
3512 *-- * reset lcString
3513 *-- lcString= ""
3514
3515 *-- **********Create vendor record***************************************************
3516 *-- **********On level break of customer,division************************************
3517 *-- * For GenTran find System template need VND- record
3518 *-- * TradingPartnerID, EDI transaction set (810, 855..), Version (003060,004010...)
3519 *-- If !(customer == lcPreviousCustomer and division == lcPreviousDivision and ord_num == lcPreviousOrdNum)
3520 *-- lcPreviousCustomer= customer
3521 *-- lcPreviousDivision= division
3522 *-- This.vl_oPRcr(customer, "", "__Ecntrc", division)
3523 *-- If Used('__Ecntrc')
3524 *-- Select (tcWorkVendor)
3525 *-- Appen blank
3526 *-- Replace vnd_id with __Ecntrc.vnd_id,;
3527 *-- vnd_tran with tcProcess,; && "oPR" (OB 855 )
3528 *-- vnd_vers with __Ecntrc.vnd_vers In (tcWorkVendor)
3529 *-- Else
3530 *-- llRetVal= .F.
3531 *-- Exit && scan
3532 *-- Endif
3533 *-- *- 36646 1/27/03 YIK
3534 *-- ENDIF
3535 *-- *=
3536 *-- * output Vendor string ("VND",TradingPartnerID,Transaction,Version)
3537 *-- lcString = EDI_VENDOR_TAG + .ConvertRecordToText(tcWorkVendor, llDelimited, lcDelimiter)
3538 *-- ***********************************************End of level break (cust,div)
3539
3540 *-- *********************************output header-level string "ORD"
3541 *-- * ready to convert to flatfile
3542 *-- Select tmpCurs
3543 *-- lcString = lcString + EDI_HEADER_TAG + .ConvertRecordToText("tmpCurs")
3544
3545 *-- * use header.Pkey as the indication for the following tables
3546 *-- * (DTL,CMT,ADR) as child's records of.
3547 *-- lnCurrentPkey= tmpCurs.pkey
3548 *-- SELECT (tcHeaderNotes)
3549 *-- SCAN FOR pkey = lnCurrentPkey
3550 *-- lcString = lcString + EDI_HNT_TAG + .ConvertRecordToText(tcHeaderNotes)
3551 *-- ENDSCAN
3552 *-- *********************************output comment string "CMT" anthing # ST/SF
3553 *-- If llHaveComment
3554 *-- Select (tcWorkComment)
3555 *-- Scan For fkey = lnCurrentPkey
3556 *-- lcString = lcString + EDI_COMMENT_TAG + .ConvertRecordToText(tcWorkComment)
3557 *-- Endscan
3558 *-- Endif
3559 *-- *********************************output Address string "ADR"
3560 *-- If llHaveAddress
3561 *-- Select (tcWorkAddress)
3562 *-- Scan For fkey = lnCurrentPkey
3563 *-- lcString = lcString + EDI_ADDRESS_TAG + .ConvertRecordToText(tcWorkAddress)
3564 *-- Endscan
3565 *-- Endif
3566 *-- *- 36646 1/27/03 YIK
3567 *-- *-Endif
3568 *-- *=
3569 *-- *********************************output detail string "DTL"
3570
3571 *-- *-lnCurrentPkey= tmpCurs.pkey
3572 *-- Select (tcWorkDetail)
3573 *-- lc5KeyValue= SPACE(20)
3574 *-- Scan For fkey = lnCurrentPkey
3575 *-- IF !lc5KeyValue == style+Color_code+Lbl_code+dimension+size_desc
3576 *-- lc5KeyValue = style+Color_code+Lbl_code+dimension+size_desc
3577 *-- lcCurSku = Alltrim(sku)
3578 *-- lcCurUpc = Alltrim(upc)
3579 *-- ENDIF
3580 *-- lnTotal_qty = 0
3581 *-- lnOpenPickQty = 0
3582 *-- lnInvoiceQty = 0
3583 *-- lnCancelQty = 0
3584 *-- *- combine multiple records (open, picked, invoiced) with the same 5 key into 1 flat file record
3585 *-- SCAN WHILE fkey = lnCurrentPkey AND style+Color_code+Lbl_code+dimension+size_desc = lc5KeyValue
3586 *-- lnTotal_qty = lnTotal_qty + EVAL(total_qty)
3587 *-- DO CASE
3588 *-- CASE line_status = "P"
3589 *-- lnOpenPickQty = lnOpenPickQty + EVAL(total_qty)
3590 *-- CASE line_status = "I"
3591 *-- lnInvoiceQty = lnInvoiceQty + EVAL(total_qty)
3592 *-- CASE line_status = "C"
3593 *-- lnCancelQty = lnCancelQty + EVAL(total_qty)
3594 *-- OTHERWISE
3595 *-- ENDCASE
3596 *-- ENDSCAN
3597 *-- *- update qty in the last record for the 5key
3598 *-- IF NOT BOF()
3599 *-- SKIP (-1)
3600 *-- ENDIF
3601 *-- REPLACE total_qty with TRANS(lnTotal_qty, '9999999999'), ;
3602 *-- OpenPickQty WITH TRANS(lnOpenPickQty, '9999999999'), ;
3603 *-- InvoiceQty WITH TRANS(lnInvoiceQty, '9999999999'), ;
3604 *-- CancelQty WITH TRANS(lnCancelQty, '9999999999')
3605
3606 *-- lnDetPkey = pkey
3607 *-- lcFKey = fkey
3608 *-- lcString = lcString + EDI_DETAIL_TAG + .ConvertRecordToText(tcWorkDetail)
3609
3610 *-- SELECT (tcDetailNotes)
3611 *-- SCAN FOR pkey = lnDetPkey
3612 *-- lcString = lcString + EDI_DNT_TAG + .ConvertRecordToText(tcDetailNotes)
3613 *-- ENDSCAN
3614
3615 *-- *--- 36646 01/27/03 YIK
3616 *-- *IF tmpCurs.ack_by = 'P'
3617 *-- *HH according to paul, if sku_upc is "N", don't export sdq data for now.
3618 *-- IF tceoPRcr.ack_by = 'P' and Not Alltrim(tceoPRcr.sku_upc)=="N"
3619 *-- If Alltrim(tceoPRcr.sku_upc) = "S"
3620 *-- lcSDQScanFor = "Alltrim(fkey) = Alltrim(lnCurrentPkey) AND Alltrim(Sku)==Alltrim(lcCurSku)"
3621 *-- Else
3622 *-- lcSDQScanFor = "Alltrim(fkey) = Alltrim(lnCurrentPkey) AND Alltrim(UPC)==Alltrim(lcCurUPC)"
3623 *-- Endif
3624 *-- Select (tcFinalSDQ)
3625 *-- SCAN For &lcSDQScanFor
3626 *-- lcString = lcString + EDI_SDQ_TAG + .ConvertRecordToText(tcFinalSDQ)
3627 *-- EndScan
3628 *-- ENDIF
3629 *-- *===
3630 *-- Endscan
3631 *-- If !Empty(lcString)
3632 *-- If FWRITE(pnHandle, lcString) = 0
3633 *-- lRetVal = .F.
3634 *-- Endif
3635 *-- Endif
3636 *-- ENDSCAN
3637 *-- * cleanup
3638 *-- .TableClose("TmpCurs")
3639 *-- .TableClose("_tmpHdrCrs")
3640 *-- Endwith
3641
3642 *-- Select(lnOldSelect)
3643 *-- Return llRetVal
3644 *-- Endproc
3645
3646 ************************************************************************************
3647 * DeleteInterfaceRecords
3648 ***********************************************************************************
3649 Procedure DeleteInterfaceRecords
3650 * TR 1074595 KISHORE 22-JAN-2014 Added , tcInterfaceStores
3651 Lparameters tcInterfaceHeader, tcInterfaceDetail, tcInterfaceComment, tcInterfaceAddress, tcInterfaceStores, tcInterfaceWhse
3652 *- TR 1098611 FH - added tcInterfaceWhse
3653 Local llRetVal, lcSQLString, lnOldSelect
3654 llRetVal= .T.
3655 lnOldSelect= Select()
3656 This.oLog.LogEntry("Deleting Interface records")
3657 * for each interface header record that successfully move to flatfile
3658 * (auto_proc="Y" and NO Errs_flg_h) have to delete related (pkey/fkey)
3659 * detail, comment, address records
3660 Select (tcInterfaceHeader)
3661 Scan For auto_proc= "Y"
3662 lnCurrentPkey= pkey
3663 lnCurrentOrd_num = ord_num && TR 1098611 FH
3664 Select (tcInterfaceDetail)
3665 Scan For fkey= lnCurrentPkey
3666 Delete In VzzeoPRid_Proc
3667 Endscan
3668 Delete For fkey= lnCurrentPkey In (tcInterfaceComment)
3669 Delete For fkey= lnCurrentPkey In (tcInterfaceAddress)
3670 * TR 1074595 KISHORE 22-JAN-2014
3671 DELETE FOR fkey = lnCurrentPkey IN (tcInterfaceStores)
3672
3673 *-TR 1098611 FH
3674 DELETE FOR ord_num = lnCurrentOrd_num IN (tcInterfaceWhse)
3675 DELETE FOR fkey = lnCurrentPkey IN (tcInterfaceWhse)
3676 *-TR 1098611 FH
3677
3678 Delete In (tcInterfaceHeader)
3679 Endscan
3680
3681 Select(lnOldSelect)
3682 Return llRetVal
3683 Endproc
3684
3685 ************************************************************************************
3686 * move interface records to history with condition of auto_proc="Y"
3687 ************************************************************************************
3688 Procedure CreateAutoProcessInterface
3689 * TR 1074595 KISHORE 22-JAN-2014 Added , pcHistSDQ, pcInterSDQ
3690 Lparameters pcInterHeader, pcInterDetail, pcInterComment, pcInterAddress, ;
3691 pcHistHeader, pcHistDetail, pcHistComment, pcHistAddress, pcInterSDQ, pcHistSDQ
3692 Local llOKtoContinue, lnOldSelect, llInterHeaderPkey, ltDatetime, ;
3693 lnCurrentOrd_num, lnCurrentInv_num
3694
3695 This.oLog.LogEntry("Moving to History tables")
3696 lnCurrentOrd_num= 0
3697 ltDatetime= Datetime()
3698 llOKtoContinue = .T.
3699 lnOldSelect = Select()
3700
3701 * Only for auto process and no last error
3702 * This is the only place that will still potentially have error in interface tables
3703 Select (pcInterHeader)
3704 Scan For auto_proc="Y"
3705 * track number of invoices make it to flatfile
3706 .nFlatfileRecords= .nFlatfileRecords+ 1
3707
3708 * create history header from interface header
3709 llOKtoContinue= This.CreateRecordWithPkey(pcInterHeader, pcHistHeader) && no real TAG in bcsysnum
3710 If !llOKtoContinue
3711 Exit
3712 Endif
3713 * create history detail/comment/address base on current ord_num,inv_num with auto_proc="Y"
3714 lnCurrentPkey= &pcInterHeader..pkey
3715 * create history detail from interface detail for each header pkey to detail fkey
3716 Select (pcInterDetail)
3717 Scan For fkey= lnCurrentPkey
3718 llOKtoContinue= This.CreateRecordWithPkey(pcInterDetail, pcHistDetail)
3719 If !llOKtoContinue
3720 Exit
3721 Endif
3722 Endscan
3723 * create history Comment from interface detail for each ord_num,invoice#
3724 Select (pcInterComment)
3725 Scan For fkey= lnCurrentPkey
3726 llOKtoContinue= This.CreateRecordWithPkey(pcInterComment, pcHistComment)
3727 If !llOKtoContinue
3728 Exit
3729 Endif
3730 Endscan
3731 * create history Address from interface detail for each div,invoice#
3732 Select (pcInterAddress)
3733 Scan For fkey= lnCurrentPkey
3734 llOKtoContinue= This.CreateRecordWithPkey(pcInterAddress, pcHistAddress)
3735 If !llOKtoContinue
3736 Exit
3737 Endif
3738 Endscan
3739
3740 *--- TR 1074595 KISHORE 22-JAN-2014
3741 SELECT (pcInterSDQ)
3742 SCAN FOR fkey = lnCurrentPkey
3743 llOKtoContinue= This.CreateRecordWithPkey(pcInterSDQ, pcHistSDQ)
3744 IF !llOKtoContinue
3745 EXIT
3746 ENDIF
3747 ENDSCAN
3748 *=== TR 1074595 KISHORE 22-JAN-2014
3749
3750 Endscan
3751
3752 Select(lnOldSelect)
3753 Return llOKtoContinue
3754 Endproc
3755
3756 ************************************************************************************
3757 * YIK 09/18/02 - Get terms code and/or ITD segment values from zzetermx
3758 * based on trading partner and BC terms code
3759 ************************************************************************************
3760 Procedure GetEDITerms
3761 Lparameters tcWorkHeader, tnITD_DueDays, tnITD_DiscPct, tnITD_DiscDays, ;
3762 tcFTerms_code, tcITD_TmType, tcPartner
3763
3764 Local lcCode, lcTerms, llRetVal, lnOldSelect
3765
3766 llRetVal = .T.
3767 lnOldSelect = Select()
3768
3769 lcCode = tcPartner
3770 lcTerms = &tcWorkHeader..terms
3771 If This.vl_editermX(lcCode, , "_TermX", lcTerms)
3772 tnITD_DueDays = _TermX.ITD_DueDays
3773 tnITD_DiscPct = _TermX.ITD_DiscPct
3774 tnITD_DiscDays = _TermX.ITD_DiscDays
3775 tcFTerms_code = _TermX.Fact_Terms
3776 tcITD_TmType = _TermX.ITD_TmType
3777 Endif
3778 If Used('_TermX')
3779 Use In _TermX
3780 Endif
3781 Select(lnOldSelect)
3782 Return llRetVal
3783 Endproc
3784
3785 ************************************************************************************
3786 * YIK 09/18/02 - Get Factor terms code and/or ITD segment values from zzetermx
3787 ************************************************************************************
3788 Procedure vl_editermX
3789 Parameters p_cKeyField1, p_cGetField, p_cCursor, p_cKeyField2
3790 Local l_cSQLString, l_vRetVal
3791 *- Select record with matching factor (customer) or empty (for generic terms setup)
3792 l_cSQLString = "SELECT TOP 1 * FROM zzetermx WHERE (Factor = ?p_cKeyField1 OR Factor = ' ')" + ;
3793 " AND Terms = ?p_cKeyField2 ORDER BY Factor DESC"
3794
3795 l_vRetVal = v_SqlPrep(l_cSQLString, p_cCursor, p_cGetField)
3796 Return l_vRetVal
3797 Endproc
3798
3799 *********************************************************
3800 * Procedure FlagProcessOrders
3801 *********************************************************
3802 Procedure FlagProcessOrders
3803 Parameters pcTranHeader, pcOrderHeader
3804 Local llRetVal , lnOldSelect, ltDatetime, lcUser_id
3805 This.oLog.LogEntry("Marking processed orders")
3806 llRetVal= .T.
3807 ltDatetime= Datetime()
3808 lcUser_id= goEnv.envLogin.cUserName
3809 lnOldSelect = Select()
3810
3811 *--- TR 1030443 22-Apr-2008 Goutam
3812 *If Used(pcTranHeader) And Used(pcOrderHeader) &&pcTranHeader is tceoprth, pcorderheader is vzzoordrh_proc
3813 If Used(pcTranHeader) And Used(pcOrderHeader) AND NOT this.lFrom945 &&pcTranHeader is tceoprth, pcorderheader is vzzoordrh_proc
3814 *=== TR 1030443 22-Apr-2008 Goutam
3815
3816 llRetVal= .SetRelation(pcTranHeader, "Ord", pcOrderHeader, "Str(ord_num)") &&set relation to str(ord_num) into pcTranHeader
3817 If llRetVal && pcOrderHeader is the current work area
3818 Scan For !(&pcTranHeader..Errs_Flg_H= "Y") && None error trans header only
3819 Replace Proc_855 With "P",;
3820 user_id With lcUser_id, ;
3821 last_mod With ltDatetime In (pcOrderHeader)
3822 Endscan
3823 Set Relation To
3824 Endif
3825 Endif
3826
3827 Select(lnOldSelect)
3828 Return llRetVal
3829 Endproc
3830
3831 Procedure vl_oPRcr
3832 Parameters p_cKeyField1, p_cGetField, p_cCursor, p_cKeyField2
3833 Local l_cSQLString, l_vRetVal
3834
3835 If Empty(p_cKeyField1)
3836 l_vRetVal = Iif(Empty(p_cGetField), .T., "")
3837 Return l_vRetVal
3838 Endif
3839
3840 l_cSQLString = "SELECT * FROM zzeoprcr WHERE customer = ?p_cKeyField1" + ;
3841 " AND Division = ?p_cKeyField2"
3842
3843 l_vRetVal = v_SqlPrep(l_cSQLString, p_cCursor, p_cGetField)
3844 Return l_vRetVal
3845
3846 *****************************************************************
3847 Procedure ScatterGatherNotes
3848 Parameters p_cFrom, p_cTo, p_nMemoWidth
3849
3850 Local lcNotes, lcHoldMemo, lnNumLines, lcNoteStr
3851 Select (p_cFrom)
3852 Scan
3853 Scatter Memvar Memo
3854 lcNotes = Alltrim(notes)
3855 If !Empty(lcNotes)
3856 lcHoldMemo = Set("MEMOWIDTH")
3857 Set Memo To p_nMemoWidth
3858 Store Memlines(lcNotes) To lnNumLines && Number of lines in memo field
3859 Store 0 To _Mline
3860 For Count = 1 To lnNumLines
3861 lcNoteStr = Mline(lcNotes, 1, _Mline)
3862 Select (p_cTo)
3863 Append Blank
3864 Gather Memvar Memo
3865 Replace notes With lcNoteStr
3866 Endfor
3867 Set Memo To (lcHoldMemo)
3868 Endif
3869 Endscan
3870 Endproc
3871
3872 *****************************************************************************************************************
3873 Procedure CreateWorkTables
3874 Parameters tcOrderHeader, tcOrderDetail, tcShpHeader, tcShpDetail, tcWorkHeader, tcWorkDetail
3875
3876 *- 1005605 06/14/04 YIK
3877 *- Use GetSortedFieldList() from bclibfwk
3878 *!* SELECT customer as _cust, ord_num as _order, * FROM (tcOrderHeader) ;
3879 *!* UNION ALL ;
3880 *!* SELECT customer as _cust, ord_num as _order, * FROM (tcShpHeader) ;
3881 *!* ORDER BY 1,2 ; &&order by needs to be a number, so have to select dummy fields.
3882 *!* INTO Cursor &tcWorkHeader
3883
3884 *!* SELECT * FROM (tcOrderDetail) ;
3885 *!* UNION ALL ;
3886 *!* SELECT * FROM (tcShpDetail) ;
3887 *!* INTO Cursor &tcWorkDetail
3888
3889 * --- TR 1034147 NSD 6/18/08
3890 * For the work header, it needs to be sorted by inv_num desc in addition to customer & order
3891 * When from 945, add the sort in, otherwise do not. This is done so the invoice header is the first
3892 * record evaluated when combining all the headers. We need the information on the invoice that may not
3893 * be the same on the open order.
3894
3895 *--- TechRec 1065807 17-Jan-2013 vkrishnamurthy ---
3896 LOCAL lcHdrFldList
3897 lcHdrFldList = GetCommonFieldListWithAlias("", "zzoordrh", "zzeoprth")
3898 *=== TechRec 1065807 17-Jan-2013 vkrishnamurthy ===
3899
3900 lcOrdHdrList = GetSortedFieldList("zzoordrh")
3901 lcOrdDtlList = GetSortedFieldList("zzoordrd")
3902
3903 *--- TechRec 1065807 17-Jan-2013 vkrishnamurthy ---
3904*!* lcSQL_H = "SELECT customer as _cust, ord_num as _order,inv_num as _invoice, " + ;
3905*!* lcOrdHdrList + ;
3906*!* " FROM " + tcOrderHeader + ;
3907*!* " UNION ALL " + ;
3908*!* "SELECT customer as _cust, ord_num as _order,inv_num as _invoice , " + ;
3909*!* lcOrdHdrList + ;
3910*!* " FROM " + tcShpHeader + ;
3911*!* " ORDER BY 1,2" + IIF(THIS.lFrom945,",3 DESC ","") + ; &&order by needs to be a number, so have to select dummy fields.
3912*!* " INTO Cursor " + tcWorkHeader
3913
3914 * TR 1074595 KISHORE 21-JAN-2014 added UNION to Vzzeoprih_proc
3915 lcSQL_H = "SELECT customer as _cust, ord_num as _order,inv_num as _invoice, " + ;
3916 lcHdrFldList + ;
3917 " FROM " + tcOrderHeader + ;
3918 " UNION ALL " + ;
3919 "SELECT customer as _cust, ord_num as _order,inv_num as _invoice , " + ;
3920 lcHdrFldList + ;
3921 " FROM " + tcShpHeader + ;
3922 " UNION ALL " + ;
3923 "SELECT customer as _cust, ord_num as _order,inv_num as _invoice , " + ;
3924 lcHdrFldList + ;
3925 " FROM Vzzeoprih_proc " + ;
3926 " WHERE 1=1 " + STRTRAN(This.cFilterCriteria, 'H.', '') + ;
3927 " ORDER BY 1,2" + IIF(THIS.lFrom945,",3 DESC ","") + ; &&order by needs to be a number, so have to select dummy fields.
3928 " INTO Cursor " + tcWorkHeader
3929 *=== TechRec 1065807 17-Jan-2013 vkrishnamurthy ===
3930
3931 lcSQL_D = "SELECT " + lcOrdDtlList + ;
3932 " FROM " + tcOrderDetail + ;
3933 " UNION ALL " + ;
3934 "SELECT " + lcOrdDtlList + ;
3935 " FROM " + tcShpDetail + ;
3936 " INTO Cursor " + tcWorkDetail
3937
3938 &lcSQL_H
3939 &lcSQL_D
3940 *= 1005605 06/14/04 YIK
3941 Endproc
3942
3943 *****************************************************************************************************************
3944 *--- 36646 1/20/03 YIK
3945 Procedure CheckSDQ
3946 *------------------
3947 Parameters pcTransHeader, pcTransDetail, pcTransControl
3948 Local llRetVal, lcTempCursor, lcErr_Msg, lcPo_num
3949 llRetVal = .T.
3950 lcTempCursor = GetUniqueFileName()
3951 lcErr_Msg = "One or More orders of this PO have blank UPC, SKU or EDI Store."
3952 With This
3953 *--- TR 1025228 9-Jul-2007 Goutam
3954 *Select h.po_num From (pcTransHeader) h, (pcTransControl) c;
3955 Where Empty(h.Edi_Store) ;
3956 and Alltrim(h.division)==Alltrim(c.division) ;
3957 and Alltrim(h.customer)==Alltrim(c.customer) ;
3958 and Alltrim(c.ack_by)=="P" ;
3959 Group By h.po_num ;
3960 Union All ;
3961 Select h.po_num From (pcTransHeader) h, (pcTransDetail) d, (pcTransControl) c ;
3962 Where Alltrim(d.Errs_Flg_D)=="Y" And h.pkey=d.fkey ;
3963 and Alltrim(h.division)==Alltrim(c.division) ;
3964 and Alltrim(h.customer)==Alltrim(c.customer) ;
3965 and Alltrim(c.ack_by)=="P" ;
3966 Group By h.po_num ;
3967 into Cursor (lcTempCursor) &&If a certain detail line fails, then fail the whole PO.
3968
3969 *=== TR 1031304 03/10/2008 TT - Added !EMPTY(Store) per Yuri ----------------------
3970*!* Select h.po_num From (pcTransHeader) h ;
3971*!* Where Empty(h.Edi_Store) ;
3972*!* and Alltrim(h.ack_by)=="P" ;
3973*!* Group By h.po_num ;
3974*!* Union All ;
3975*!* Select h.po_num From (pcTransHeader) h, (pcTransDetail) d ;
3976*!* Where Alltrim(d.Errs_Flg_D)=="Y" And h.pkey=d.fkey ;
3977*!* and Alltrim(h.ack_by)=="P" ;
3978*!* Group By h.po_num ;
3979*!* into Cursor (lcTempCursor) &&If a certain detail line fails, then fail the whole PO.
3980
3981 SELECT h.po_num FROM (pcTransHeader) h ;
3982 WHERE EMPTY(h.Edi_Store) ;
3983 AND !EMPTY(h.store) ;
3984 AND ALLTRIM(h.ack_by)=="P" ;
3985 GROUP BY h.po_num ;
3986 UNION ALL ;
3987 SELECT h.po_num FROM (pcTransHeader) h, (pcTransDetail) d ;
3988 WHERE ALLTRIM(d.Errs_Flg_D)=="Y" And h.pkey=d.fkey ;
3989 AND ALLTRIM(h.ack_by)=="P" ;
3990 GROUP BY h.po_num ;
3991 INTO CURSOR (lcTempCursor) &&If a certain detail line fails, then fail the whole PO.
3992
3993 *=== TR 1031304 03/10/2008 TT - Added !EMPTY(Store) per Yuri ----------------------
3994 *=== TR 1025228 9-Jul-2007 Goutam
3995
3996 Select (lcTempCursor)
3997 Scan
3998 lcPo_num = Alltrim(&lcTempCursor..po_num)
3999 Replace Errs_Msg_H With Errs_Msg_H + lcErr_Msg, ;
4000 Errs_Flg_H With "Y" In (pcTransHeader) ;
4001 For Alltrim(po_num)==lcPo_num And Not Alltrim(Errs_Flg_H)=="Y"
4002 Endscan
4003
4004 ***Blank out deparment and terms for those POs that have different
4005 ***department and terms
4006
4007 *--- TR 1025228 9-Jul-2007 Goutam
4008 *Select h.po_num, h.department, h.terms From (pcTransHeader) h, (pcTransControl) c;
4009 Where ;
4010 Alltrim(h.division)==Alltrim(c.division) ;
4011 and Alltrim(h.customer)==Alltrim(c.customer) ;
4012 and Alltrim(c.ack_by)=="P" ;
4013 group By h.po_num, h.department, h.terms ;
4014 Into Cursor (lcTempCursor)
4015
4016 Select h.po_num, h.department, h.terms From (pcTransHeader) h;
4017 Where ;
4018 Alltrim(h.ack_by)=="P" ;
4019 group By h.po_num, h.department, h.terms ;
4020 Into Cursor (lcTempCursor)
4021 *=== TR 1025228 9-Jul-2007 Goutam
4022
4023 Select po_num From (lcTempCursor) Group By po_num ;
4024 Having Count(*)>1 Into Cursor (lcTempCursor)
4025
4026 Select (lcTempCursor)
4027 Scan
4028 lcPo_num = Alltrim(&lcTempCursor..po_num)
4029 Replace department With '', terms With '' ;
4030 In (pcTransHeader) For Alltrim(po_num)==lcPo_num
4031 Endscan
4032 .TableClose(lcTempCursor)
4033 Endwith
4034
4035 Return llRetVal
4036 Endproc
4037
4038
4039 Procedure PrepareCursorsForSDQ
4040 *------------------------------
4041 Parameters tcHeader, tcDetail, tcTempSDQ, tcControlCursor
4042 Local lcTempCursor
4043 lnWorkArea = Select()
4044 lcTempCursor = GetUniqueFileName()
4045 With This
4046 *- Select only POs with multiple stores, otherwise there's no need in SDQs if there's only 1 store in a PO.
4047 *SELECT h.po_num, h.pkey, count(*) AS cnt ;
4048 * FROM (tcHeader) h ;
4049 * WHERE h.ack_by = 'P' AND h.errs_flg_h <> 'Y';
4050 * GROUP BY h.po_num ;
4051 * HAVING Cnt > 1 ;
4052 * INTO CURSOR (lcTempCursor)
4053
4054 *- styles to work with
4055 *- 1005605 07/21/04 YIK
4056 *- Added h.edi_center to group by edi_center
4057 *- 1006635 08/11/04 YIK - Per May Co., don't group by edi center.
4058 *- send any one as ship-to
4059
4060 *--- TR 1025228 9-Jun-2007 Goutam
4061 *Select h.po_num, h.Edi_Store, h.edi_center, d.upc, d.sku, q.pkey As pkey, h.pkey As hpkey, d.pkey As dpkey, d.fkey As fkey, ;
4062 d.total_qty, d.line_status, d.division, d.Style, d.color_code, d.lbl_code, d.Dimension, ;
4063 d.size_desc ;
4064 FROM (tcHeader) h ;
4065 JOIN (tcControlCursor) q ;
4066 ON Alltrim(q.customer)==Alltrim(h.customer) And ;
4067 Alltrim(q.division)==Alltrim(h.division) And ;
4068 Alltrim(q.ack_by)=="P" ;
4069 JOIN (tcDetail) d ;
4070 ON h.pkey = d.fkey ;
4071 WHERE h.Errs_Flg_H <> 'Y' ;
4072 ORDER By h.po_num, d.upc, d.sku, h.Edi_Store ;
4073 INTO Cursor (tcTempSDQ)
4074
4075 Select h.po_num, h.Edi_Store, h.edi_center, d.upc, d.sku, h.Controlpkey As pkey, h.pkey As hpkey, d.pkey As dpkey, d.fkey As fkey, ;
4076 d.total_qty, d.line_status, d.division, d.Style, d.color_code, d.lbl_code, d.Dimension, ;
4077 d.size_desc ;
4078 FROM (tcHeader) h ;
4079 JOIN (tcDetail) d ;
4080 ON h.pkey = d.fkey ;
4081 WHERE h.Errs_Flg_H <> 'Y' ;
4082 AND Alltrim(h.ack_by)=="P" ;
4083 ORDER By h.po_num, d.upc, d.sku, h.Edi_Store ;
4084 INTO Cursor (tcTempSDQ)
4085 *=== TR 1025228 9-Jun-2007 Goutam
4086
4087 *=
4088 .TableClose(lcTempCursor)
4089 Endwith
4090 Select (lnWorkArea)
4091
4092 Endproc
4093
4094 Procedure ConvertToSDQ
4095 *-------------------------
4096 Parameters tcInterfaceHeader, tcInterfaceDetail, ;
4097 tcInterfaceComment, tcInterfaceAddress, pcFinalSDQ, pcSourceSDQ
4098
4099 Local loSDQ, lnWorkArea, llRetVal,lnTotal_qty, lnOpenPickQty, lnInvoiceQty, lnCancelQty
4100
4101 *--- TR 1074595 6-Feb-2014 Goutam
4102 LOCAL lnPkey
4103 *=== TR 1074595 6-Feb-2014 Goutam
4104
4105 llRetVal = .T.
4106 lnWorkArea = Select()
4107 Select * From (pcSourceSDQ) Where .F. Into Cursor Q_VertSDQ
4108
4109 With This
4110 .MakeCursorWritable("Q_VertSDQ", "VertSDQ")
4111 .TableClose("Q_VertSDQ")
4112
4113 *--- TR 1074595 6-Feb-2014 Goutam
4114 SELECT (tcInterfaceHeader)
4115 SCAN
4116 lnPkey = Pkey
4117 *=== TR 1074595 6-Feb-2014 Goutam
4118
4119 Select (pcSourceSDQ)
4120
4121 *--- TR 1074595 6-Feb-2014 Goutam
4122 *Scan
4123 SCAN FOR fkey = lnPkey
4124 *=== TR 1074595 6-Feb-2014 Goutam
4125
4126 Scatter Memvar
4127 Insert Into VertSDQ From Memvar
4128 Endscan
4129
4130 *--- TR 1074595 6-Feb-2014 Goutam
4131 ENDSCAN
4132 SELECT VertSDQ
4133 INDEX on po_num+upc+sku+STR(fkey) TAG po_num
4134 *=== TR 1074595 6-Feb-2014 Goutam
4135
4136 llRetVal = .CreateSDQ("VertSDQ", pcFinalSDQ)
4137
4138 *IF RECC(pcSourceSDQ) > 0
4139 **We can not delete data from interface views here because we
4140 **still need the data when we call UnLoadToFlatFile routine.
4141 *!* IF false
4142 *!* SELECT (pcSourceSDQ)
4143 *!* lnDetTotal_qty = 0
4144 *!* lnTotal_qty = 0
4145 *!* lnOpenPickQty = 0
4146 *!* lnInvoiceQty = 0
4147 *!* lnCancelQty = 0
4148 *!* lnHoldPKey = pkey
4149 *!* lnHoldFKey = dpkey
4150 *!* SCATTER NAME loSDQ
4151
4152 *!* SCAN
4153 *!* IF !(po_num == loSDQ.po_num AND upc == loSDQ.upc AND sku == loSDQ.sku)
4154 *!* SELECT (tcInterfaceDetail)
4155 *!* LOCATE FOR pkey = lnHoldFKey
4156 *!* REPLACE total_qty WITH lnDetTotal_qty
4157
4158 *!* SELECT (pcSourceSDQ)
4159 *!* lnHoldFKey = dpkey
4160 *!* IF ! (po_num == loSDQ.po_num)
4161 *!* lnHoldPKey = pkey
4162 *!* ENDIF
4163 *!* lnDetTotal_qty = 0
4164 *!* ENDIF
4165
4166 *!* IF ! (po_num == loSDQ.po_num AND Edi_Store == loSDQ.Edi_Store AND upc == loSDQ.upc AND sku == loSDQ.sku)
4167 *!* SELECT VertSDQ
4168 *!* APPEND BLANK
4169 *!* GATHER NAME loSDQ
4170 *!* SELECT (pcSourceSDQ)
4171 *!* SCATTER NAME loSDQ
4172
4173 *!* lnTotal_qty = 0
4174 *!* lnOpenPickQty = 0
4175 *!* lnInvoiceQty = 0
4176 *!* lnCancelQty = 0
4177 *!* ENDIF
4178 *!* *- combine multiple records (open, picked, invoiced) with the same 5 key into 1 flat file record
4179 *!* lnTotal_qty = lnTotal_qty + loSDQ.total_qty
4180 *!* lnDetTotal_qty = lnDetTotal_qty + loSDQ.total_qty
4181 *!* DO CASE
4182 *!* CASE line_status = "P"
4183 *!* lnOpenPickQty = lnOpenPickQty + loSDQ.total_qty
4184 *!* CASE line_status = "I"
4185 *!* lnInvoiceQty = lnInvoiceQty + loSDQ.total_qty
4186 *!* CASE line_status = "C"
4187 *!* lnCancelQty = lnCancelQty + loSDQ.total_qty
4188 *!* OTHERWISE
4189 *!* ENDCASE
4190
4191 *!* IF hpkey <> lnHoldPKey
4192 *!* lchpkey = hpkey
4193 *!* SELECT (tcInterfaceHeader)
4194 *!* DELETE FOR pkey = lchpkey
4195 *!* SELECT (tcInterfaceComment)
4196 *!* DELETE FOR pkey = lchpkey
4197 *!* SELECT (tcInterfaceAddress)
4198 *!* DELETE FOR pkey = lchpkey
4199 *!* SELECT (pcSourceSDQ)
4200 *!* ENDIF
4201
4202 *!* IF dpkey <> lnHoldFKey
4203 *!* lcFKey = dpkey
4204 *!* SELECT (tcInterfaceDetail)
4205 *!* DELETE FOR fkey = lcFKey
4206 *!* SELECT (pcSourceSDQ)
4207 *!* ENDIF
4208
4209 *!* ENDSCAN
4210 *!* SELECT VertSDQ
4211 *!* APPEND BLANK
4212 *!* GATHER NAME loSDQ
4213 *!* llRetVal = .CreateSDQ("VertSDQ", pcFinalSDQ)
4214 *!* ENDIF
4215 Endwith
4216
4217 Select (lnWorkArea)
4218 Endproc
4219
4220 Procedure CreateSDQ
4221 *--------------------
4222 Parameters tcSDQ, tcFinalSDQ
4223 Local lnWorkArea, loFinalSDQ, lcPo_num, lcEdi_center
4224
4225 lnWorkArea = Select()
4226 With This
4227 .CreateMetaDataWorkTable("tcEmData", "SDQ", tcFinalSDQ)
4228 Select (tcFinalSDQ)
4229 Scatter Name loFinalSDQ Blank
4230 Select (tcSDQ)
4231 Go Top
4232 lnCnt = 1
4233 lcCnt = Padl(Alltr(Trans(lnCnt, "99")), 2, '0')
4234 *- 1005605 07/21/04 YIK
4235 *!* loFinalSDQ.fkey = TRANS(fkey)
4236 *!* loFinalSDQ.pkey = TRANS(hpkey)
4237 loFinalSDQ.fkey = Padl(Trans(fkey), 10)
4238 loFinalSDQ.pkey = Padl(Trans(hpkey), 10)
4239 *-lcdpkey = dpkey
4240 *=
4241 loFinalSDQ.sku = sku
4242 loFinalSDQ.upc = upc
4243 lcPo_num = po_num
4244
4245 *- 1005605 07/21/04 YIK
4246 lcEdi_center = edi_center
4247
4248 *--- TR 1074595 6-Feb-2014 Goutam
4249 *SCAN
4250 DO WHILE !EOF()
4251 *=== TR 1074595 6-Feb-2014 Goutam
4252
4253 *- 1006635 08/11/04 YIK - Per May Co., don't group by edi center.
4254 *- send any one as ship-to
4255 *!* IF !(po_num = lcPo_num AND sku = loFinalSDQ.sku AND upc = loFinalSDQ.upc AND lcEdi_center = edi_center) ;
4256 *!* Or lnCnt = 11&&We handle 10 stores at a time
4257
4258 *--- TR 1074595 6-Feb-2014 Goutam
4259 *If !(po_num = lcPo_num And sku = loFinalSDQ.sku And upc = loFinalSDQ.upc) ;
4260 Or lnCnt = 11 &&We handle 10 stores at a time
4261 If (po_num = lcPo_num And sku = loFinalSDQ.sku And upc = loFinalSDQ.upc)
4262 *=== TR 1074595 6-Feb-2014 Goutam
4263 *=
4264
4265 *--- TR 1074595 6-Feb-2014 Goutam
4266*!* Select (tcFinalSDQ)
4267*!* Append Blank
4268*!* Gather Name loFinalSDQ
4269*!* Select (tcSDQ)
4270
4271*!* *- 1005605 07/21/04 YIK
4272*!* loFinalSDQ.fkey = Padl(Trans(fkey), 10)
4273*!* loFinalSDQ.pkey = Padl(Trans(hpkey), 10)
4274*!* *-lcdpkey = dpkey
4275*!* *=
4276
4277*!* loFinalSDQ.sku = sku
4278*!* loFinalSDQ.upc = upc
4279*!* lcPo_num = po_num
4280*!* lcEdi_center = edi_center
4281 *=== TR 1074595 6-Feb-2014 Goutam
4282
4283 *== 1005605 07/21/04 YIK
4284
4285 *---Reset the store, qty fields. Otherwise they will carry down the values of previous line
4286 For lnCnt=1 To 10
4287 lcStoreFld = "loFinalSDQ.Store_" + Padl(Alltr(Trans(lnCnt, "99")), 2, '0')
4288 lcQtyFld = "loFinalSDQ.Qty_" + Padl(Alltr(Trans(lnCnt, "99")), 2, '0')
4289 &lcStoreFld = ''
4290 &lcQtyFld = ''
4291 Endfor
4292 *===End Reseting values
4293
4294 *--- TR 1074595 6-Feb-2014 Goutam
4295 Select (tcSDQ)
4296 lnCnt = 1
4297 SCAN WHILE (po_num = lcPo_num And sku = loFinalSDQ.sku And upc = loFinalSDQ.upc)
4298 lcCnt = Padl(Alltr(Trans(lnCnt, "99")), 2, '0')
4299 lcStoreFld = "loFinalSDQ.Store_" + lcCnt
4300 lcQtyFld = "loFinalSDQ.Qty_" + lcCnt
4301 &lcStoreFld = Edi_Store
4302 &lcQtyFld = Trans(total_qty)
4303 lnCnt = lnCnt + 1
4304 IF lnCnt = 11
4305 Select (tcFinalSDQ)
4306 Append Blank
4307 Gather Name loFinalSDQ
4308 Select (tcSDQ)
4309 For lnCnt=1 To 10
4310 lcStoreFld = "loFinalSDQ.Store_" + Padl(Alltr(Trans(lnCnt, "99")), 2, '0')
4311 lcQtyFld = "loFinalSDQ.Qty_" + Padl(Alltr(Trans(lnCnt, "99")), 2, '0')
4312 &lcStoreFld = ''
4313 &lcQtyFld = ''
4314 Endfor
4315 lnCnt = 1
4316 ENDIF
4317 ENDSCAN
4318 IF lnCnt > 1
4319 Select (tcFinalSDQ)
4320 Append Blank
4321 Gather Name loFinalSDQ
4322 Select (tcSDQ)
4323 ENDIF
4324
4325 loFinalSDQ.fkey = Padl(Trans(fkey), 10)
4326 loFinalSDQ.pkey = Padl(Trans(hpkey), 10)
4327
4328 loFinalSDQ.sku = sku
4329 loFinalSDQ.upc = upc
4330 lcPo_num = po_num
4331 lcEdi_center = edi_center
4332 *=== TR 1074595 6-Feb-2014 Goutam
4333
4334 *--- TR 1074595 6-Feb-2014 Goutam
4335 *lnCnt = 1
4336 *lcCnt = Padl(Alltr(Trans(lnCnt, "99")), 2, '0')
4337 *=== TR 1074595 6-Feb-2014 Goutam
4338
4339 ENDIF
4340
4341 *--- TR 1074595 6-Feb-2014 Goutam
4342*!* lcCnt = Padl(Alltr(Trans(lnCnt, "99")), 2, '0')
4343*!* lcStoreFld = "loFinalSDQ.Store_" + lcCnt
4344*!* lcQtyFld = "loFinalSDQ.Qty_" + lcCnt
4345*!* &lcStoreFld = Edi_Store
4346*!* &lcQtyFld = Trans(total_qty)
4347*!* lnCnt = lnCnt + 1
4348 *=== TR 1074595 6-Feb-2014 Goutam
4349
4350 *--- TR 1074595 6-Feb-2014 Goutam
4351 *Endscan
4352 ENDDO
4353 *=== TR 1074595 6-Feb-2014 Goutam
4354
4355 *--- TR 1074595 6-Feb-2014 Goutam
4356*!* Select (tcFinalSDQ)
4357*!* Append Blank
4358*!* Gather Name loFinalSDQ
4359 *=== TR 1074595 6-Feb-2014 Goutam
4360
4361 Select (lnWorkArea)
4362 Endwith
4363 Endproc
4364
4365 *- 1005605 06/16/04 YIK
4366 Procedure ConsolidatePrepackByRangeSKU
4367 Lparameters pcTransHeader, pcTransDetail
4368 Local llRetVal, lnOldSelect, lnRangeDtlPkey, lcCurRangeSKU,;
4369 lcRng_style, lcRng_color, lcRng_lbl, lcRng_pack, loRecord, lcReplaceStr
4370
4371 Local lcCurRangeDtlSKU, llFound && TR 1013140 NH 2005-09-19
4372 llRetVal= .T.
4373 lnOldSelect= Select()
4374 With This
4375 lcSQLString = "Select ph.ppk_desc, ph.pack_qty, ph.mulcllb_ok, pd.*, " + ;
4376 " s.style, s.style as PPK_Style, s.color_code as PPK_Color, " + ;
4377 " s.lbl_code as PPK_Label,s.dimension as PPK_Dimension, " + ;
4378 "'" + Space(12) + "' as UPC," + "'" + Space(27) + "' as SKU, " +;
4379 "'" + Space(14) + "' as EAN, " +;
4380 "999999 as PPSize_qty , 999 as Sizebucket ,"+;
4381 "'" + Space(5) + "' AS Size_Desc " +;
4382 " from zzxppakh ph, zzxppakd pd, zzxscolr s " + ;
4383 " Where 0 = 1 "
4384 *- , ph.pkey AS itm_pkey
4385 llRetVal= llRetVal And v_sqlexec(lcSQLString, "__RangeSLN")
4386 .MakeCursorWritable("__RangeSLN", "tcRangeSLN")
4387 .TableClose("__RangeSLN")
4388 Select Distinct ord_num, rng_style, rng_color, rng_lbl, rng_pack, ;
4389 rng_bk, rng_qty, rng_type, Sum(total_qty) As total_qty ;
4390 From (pcTransDetail) ;
4391 Where Errs_Flg_D<> "Y" And rng_style<> '' ; && range style
4392 Group By rng_style, rng_color, rng_lbl, rng_pack, ;
4393 rng_bk, rng_qty, rng_type, ord_num ;
4394 Into Cursor tcTemp
4395
4396 lcErrs_Msg= "Sum of units for the same range style must be divisible by "
4397 lcCurRangeSKU= ""
4398 Select tcTemp
4399 Scan
4400 lcCurRangeSKU= rng_style+ rng_color+ rng_lbl+ rng_pack+ Str(rng_bk) + Str(ord_num)
4401 lnCurTotal_qty= total_qty
4402
4403 * For "S"et range type when Mod(total_qty, rng_qty)> 0 -- Invalid range qty
4404 *- Add type 'R' which is to be rolled up in a single line but w/o qty conversion.
4405 If rng_type = SET_RANGE_TYPE Or rng_type = RUN_RANGE_TYPE
4406 * Mark group of trans. detail for same Range style with error
4407 * when Sum(total_qty) not divisible by rng_qty for "S"et range style
4408
4409 *- Added ..AND rng_type = SET_RANGE_TYPE - qty conversion only for 'S'et
4410 If Mod(tcTemp.total_qty, tcTemp.rng_qty) > 0 And tcTemp.rng_qty> 0 And rng_type = SET_RANGE_TYPE
4411 Replace Errs_Msg_D With Errs_Msg_D + lcErrs_Msg + ;
4412 allt(Str(tcTemp.rng_qty)) + CRLF, Errs_Flg_D With "Y" ;
4413 For rng_style+ rng_color+ rng_lbl+ rng_pack+ Str(rng_bk) +;
4414 Str(ord_num)= lcCurRangeSKU In (pcTransDetail)
4415 Else
4416 * Consolidate group of trans. detail for same range style
4417 If Seek(lcCurRangeSKU, pcTransDetail, "RangeSKU")
4418 Select (pcTransDetail)
4419 Scatter Name loRecord
4420 * Switch s,c,l,d,sz with range s,c,l,d,sz
4421 loRecord.Style= loRecord.rng_style
4422 loRecord.color_code= loRecord.rng_color
4423 loRecord.lbl_code= loRecord.rng_lbl
4424 loRecord.Dimension= loRecord.rng_pack
4425 loRecord.sizebucket= loRecord.rng_bk
4426 If rng_type = SET_RANGE_TYPE
4427 loRecord.total_qty= lnCurTotal_qty/ loRecord.rng_qty && rollup total qty by range SKU
4428 Else
4429 loRecord.total_qty= lnCurTotal_qty
4430 Endif
4431 * reset range SKU; only want to consolidate once
4432 loRecord.rng_style= ''
4433 loRecord.rng_color= ''
4434 loRecord.rng_lbl= ''
4435 loRecord.rng_pack= ''
4436 loRecord.rng_bk= 0
4437 loRecord.rng_qty= 0
4438
4439 * Get range style price
4440 llRetVal= vl_rangr(loRecord.division,, 'tcRangh', loRecord.Style, ;
4441 loRecord.color_code, loRecord.lbl_code, loRecord.Dimension)
4442 lcPrice_code= Iif(Empty(&pcTransDetail..price_code), 'a', &pcTransDetail..price_code)
4443 lnPrice= Eval("tcRangh." + lcPrice_code + "_price")
4444 loRecord.price= lnPrice
4445 loRecord.org_price= lnPrice
4446
4447 * Remove all trans detail with same range SKU and replace with
4448 * rollup level of range SKU
4449 *- Insert range style components into tcRangeSLN (structure of pcTransDetail)
4450 *--- TR 1013140 NH 2005-09-19
4451 *!* llRetVal= vl_rangd1(loRecord.division, "", 'tcRangd', style, Color_code, Lbl_code, dimension)
4452 *--- bring range style all details
4453 llRetVal = llRetVal And vl_rangd(loRecord.division, "", 'tcRangd', loRecord.Style, ;
4454 loRecord.color_code, loRecord.lbl_code, loRecord.Dimension)
4455
4456 This.oLog.LogEntry("Range style detail components query " + Iif(llRetVal, "completed.","failed."))
4457 lcCurRangeDtlSKU = ""
4458 llFound = .F.
4459 *=== TR 1013140 NH 2005-09-19
4460
4461 lnPkey = loRecord.pkey
4462 Scan For rng_style+ rng_color+ rng_lbl+ rng_pack+ Str(rng_bk)+ ;
4463 Str(ord_num) = lcCurRangeSKU And sln_req = 'Y'
4464 lnItemPkey = pkey
4465 *--- TR 1013140 NH 2005-09-19
4466 *-- get to the matching rangd record
4467 lcCurRangeDtlSKU = Style + color_code + lbl_code + Dimension
4468 Select tcRangd
4469 Locate For Style + color_code + lbl_code + Dimension = lcCurRangeDtlSKU
4470 llFound = Found()
4471 Select (pcTransDetail)
4472 If Not llFound
4473 .oLog.LogEntry("SKU " + lcCurRangeDtlSKU + " does not exists for range style " +;
4474 rng_style+ rng_color+ rng_lbl+ rng_pack+ Str(rng_bk))
4475 llRetVal = .F.
4476 Endif
4477 *=== TR 1013140 NH 2005-09-19
4478
4479 If llRetVal
4480 lcQtyFld = "SIZE" + Trans(sizebucket, "@L 99") + "_qty"
4481 lnppsize_qty = Eval('tcRangd.' + lcQtyFld)
4482 lnPack_total = tcRangd.total_qty
4483 Else
4484 lnppsize_qty = 0
4485 lnPack_total = 0
4486 Endif
4487 lcReplaceStr = "Replace pack_qty with tcRangh.rng_qty, " + ;
4488 "ppsize_qty with lnppsize_qty, Pack_total with lnPack_total, " + ;
4489 "PPK_style with rng_style, PPK_color with rng_color, " + ;
4490 "PPK_label with rng_lbl, PPK_dimension with rng_pack "
4491 *- , itm_pKey with lnpkey
4492 .ScatterGather(pcTransDetail, "tcRangeSLN", ;
4493 lcReplaceStr, lnItemPkey)
4494 Endscan
4495 Delete For rng_style+ rng_color+ rng_lbl+ rng_pack+ Str(rng_bk)+ ;
4496 Str(ord_num) = lcCurRangeSKU In (pcTransDetail)
4497 Append Blank
4498 Gather Name loRecord
4499 Endif
4500 Endif
4501 *- 1006052 07/01/04 YIK
4502 *- Populate SLNs for range styles type 'P'
4503 Else
4504 .PopulateSLNsForRangeP(lcCurRangeSKU, pcTransDetail)
4505 *= 1006052
4506 Endif
4507
4508 Endscan
4509 Use In Select("tcTemp")
4510 Endwith
4511
4512 Select(lnOldSelect)
4513 Return llRetVal
4514
4515 *- 1009713 3/3/05 YIK
4516 *- Added tlDelimited, tcDelimiter
4517 Procedure BuildSLNString
4518 Lparameters tcDetail, tcSLN, tcSLNIndexTag, tcFlatFileString, tlDelimited, tcDelimiter
4519 Local llRetVal, lnOldSelect
4520 llRetVal= .T.
4521 lnOldSelect= Select()
4522
4523 lcCurrItemPkey= &tcDetail..pkey
4524 Select (tcSLN)
4525
4526 * --- TR 1044514 3/25/10 CM/NSD
4527 If This.lVMI
4528 If Seek(lcCurrItemPkey, tcSLN, tcSLNIndexTag)
4529 Scan While itm_pkey+customer = lcCurrItemPkey
4530 tcFlatFileString = tcFlatFileString + EDI_SLN_TAG + tcDelimiter + .ConvertRecordToText(tcSLN, tlDelimited, tcDelimiter)
4531 Endscan
4532 Endif
4533 Else
4534 * === TR 1044514 3/25/10 CM/NSD
4535
4536 If Seek(lcCurrItemPkey, tcSLN, tcSLNIndexTag)
4537 Scan While itm_pkey+customer = lcCurrItemPkey
4538
4539 * --- TR 1044514 3/25/10 CM
4540 *tcFlatFileString = tcFlatFileString + EDI_SLN_TAG+ .ConvertRecordToText(tcSLN, tlDelimited, tcDelimiter)
4541 tcFlatFileString = tcFlatFileString + EDI_SLN_TAG + tcDelimiter + .ConvertRecordToText(tcSLN, tlDelimited, tcDelimiter)
4542 * === TR 1044514 3/25/10 CM
4543
4544 Endscan
4545 Endif
4546
4547 * --- TR 1044514 3/25/10 CM/NSD
4548 Endif
4549 * === TR 1044514 3/25/10 CM/NSD
4550
4551 Select(lnOldSelect)
4552 Return llRetVal
4553 Endproc
4554 *= 1005605 YIK
4555
4556 *--- TR 1013007 NH 2005-10-17
4557
4558 ***********************************************************************************************
4559
4560 Procedure CreateOPR_TransCursor(pceoprTH, pceoPRtd)
4561
4562 Local lnOldSelect, llRetVal, lcDivCursor, loDiv
4563 lnOldSelect = Select()
4564 llRetVal = .T.
4565 lcDivCursor = Sys(2015)
4566 With This
4567
4568 *--- TR 1025228 9-Jul-2007 Goutam
4569 *Select h.*, c.sln_req, c.SkipPpkUPC, c.sku_upc As c_sku_upc ;
4570 From vzzeoPRtH_Proc h ;
4571 JOIN zzeoPRcr c On h.customer = c.customer And h.division = c.division ;
4572 Into Cursor __EoPRth
4573 *--- TR 1034481 NH
4574
4575*!* Select h.*, NVL(c.sln_req, c2.sln_req) as sln_req, NVL(c.SkipPpkUPC, c2.SkipPpkUPC) as SkipPpkUPC, ;
4576*!* NVL(c.DUNS_req, c2.DUNS_req) as DUNS_req, NVL(c.suppl_req, c2.suppl_req) AS suppl_req, ;
4577*!* NVL(c.dept_req, c2.dept_req) AS dept_req, NVL(c.po_req, c2.po_req) AS po_req, ;
4578*!* NVL(c.sku_upc, c2.sku_upc) As c_sku_upc, NVL(c.ack_by, c2.ack_by) As c_ack_by, ;
4579*!* NVL(c.vnd_id, c2.vnd_id) As c_vnd_id, NVL(c.vnd_vers, c2.vnd_vers) As vnd_vers, ;
4580*!* NVL(c.pKey, c2.pKey) As Controlpkey ;
4581*!* From vzzeoPRtH_Proc h ;
4582*!* LEFT JOIN zzeoPRcr c On h.customer = c.customer And h.division = c.division ;
4583*!* AND h.oVnd_Key = c.oVnd_Key ;
4584*!* LEFT JOIN zzeoPRcr c2 On h.customer = c2.customer And h.division = c2.division ;
4585*!* AND c2.oVnd_Key = ' ' ;
4586*!* Into Cursor __EoPRth
4587
4588 *--- resolve which control ref record should be used (take the pkey of zzeoprcr)
4589 CREATE CURSOR tcOprXcr (pkey i,pkey_c i)
4590
4591 INSERT INTO tcOprXcr ;
4592 SELECT h.pkey, NVL(NVL(c.pkey, c2.pkey),0) pkey_c ;
4593 From vzzeoPRtH_Proc h ;
4594 LEFT JOIN zzeoPRcr c On h.customer = c.customer And h.division = c.division ;
4595 AND h.oVnd_Key = c.oVnd_Key ;
4596 LEFT JOIN zzeoPRcr c2 On h.customer = c2.customer And h.division = c2.division ;
4597 AND c2.oVnd_Key = ' '
4598
4599 INDEX on pkey TAG pkey
4600 INDEX on pkey_c TAG pkey_c
4601
4602 *--- Now, we do not have any null value even if there is no matching conrol ref record
4603 Select h.*, NVL(c.sln_req, ' ') as sln_req, NVL(c.SkipPpkUPC, ' ') as SkipPpkUPC, ;
4604 NVL(c.DUNS_req, ' ') as DUNS_req, NVL(c.suppl_req, ' ') AS suppl_req, ;
4605 NVL(c.dept_req, ' ') AS dept_req, NVL(c.po_req, ' ') AS po_req, ;
4606 NVL(c.sku_upc, ' ') As c_sku_upc, NVL(c.ack_by, ' ') As c_ack_by, ;
4607 NVL(c.vnd_id, ' ') As c_vnd_id, NVL(c.vnd_vers, ' ') As vnd_vers, ;
4608 x.pKey_c As Controlpkey ;
4609 from vzzeoPRtH_Proc h inner join tcOprXcr x ON h.pkey = x.pkey ;
4610 left outer join zzeoPRcr c on x.pkey_c = c.pkey ;
4611 Into Cursor __EoPRth
4612
4613 *=== TR 1034481 NH
4614 *=== TR 1025228 9-Jul-207 Goutam
4615
4616 .MakeCursorWritable("__EoPRth", pceoprTH)
4617
4618 Select (pceoprTH)
4619
4620 *--- TR 1025228 9-Jul-2007 Goutam
4621 Replace ALL sku_upc WITH c_sku_upc, vnd_id WITH c_vnd_id, ack_by WITH c_ack_by
4622 *=== TR 1025228 9-Jul-2007 Goutam
4623
4624 .nRecordsToProcess= Recc(pceoprTH)
4625 Index On pkey Tag pkey
4626 Index On Str(ord_num) Tag ord
4627
4628 *--- TR 1025228 3-Jul-2007 Goutam
4629 *Index On customer+division Tag cust_div
4630 Index On customer + division + oVnd_Key Tag cust_div
4631 *--- TR 1025228 3-Jul-2007 Goutam
4632
4633 * Make tran detail cursor
4634 * --- TR 1048571 8/18/10 CM --- Added Invoice_Color
4635 Select *, SPACE(5) as Invoice_Color From vzzeoPRtD_Proc Into Cursor __EoPRtd
4636 This.MakeCursorWritable("__EoPRtd", pceoPRtd)
4637 Select (pceoPRtd)
4638
4639 * --- TR 1048571 8/18/10 CM
4640 REPLACE ALL Invoice_Color WITH color_code , po4_uom with ib_uom && TR 1061313 FH- added po4_uom with ib_uom 05/07/12
4641 * === TR 1048571 8/18/10 CM
4642
4643 Index On pkey Tag pkey
4644 Index On Empty(upc) Tag upc
4645 Index On Empty(sku) Tag sku
4646 *--- TR 1044382 16-Dec-2009 JK ---
4647 Index On rng_style+ rng_color+ rng_lbl+ rng_pack+ Str(rng_bk)+ Str(ord_num)+line_status Tag RangeSKU
4648 Index On division+Style+color_code+lbl_code+Dimension+line_status Tag OurSKU
4649 *=== TR 1044382 16-Dec-2009 JK ===
4650 Endwith
4651
4652 Select(lnOldSelect)
4653 Return llRetVal
4654
4655 Endproc
4656
4657 *=============================================================================================================
4658 * Get Record Count of Records with Auto-Proc flag = Y
4659 * TR 1031624 JUL-14-2008 BR
4660 *=============================================================================================================
4661 Procedure GetRecordCountWithAutoProc
4662 Parameters tcVzzeoPRih_Proc
4663 Local lcOldOrder, lcAutoProcRecords
4664
4665 With This
4666 Select (tcVzzeoPRih_Proc)
4667 lcAutoProcRecords = 0
4668 lcOldOrder = Order()
4669 Set Order To auto_proc
4670
4671 If Seek("Y", tcVzzeoPRih_Proc,"auto_proc")
4672 Scan While auto_proc = "Y"
4673
4674 Endscan
4675 lcAutoProcRecords = .CountTotalRecs (tcVzzeoPRih_Proc)
4676 Endif
4677 Endwith
4678
4679 Set Order To (lcOldOrder)
4680 Return lcAutoProcRecords
4681 Endproc
4682 *=============================================================================================================
4683 * TR 1031624 JUL-14-2008 BR
4684 *=============================================================================================================
4685
4686 ***********************************************************************************************
4687 *--- pcoprth_filter temporary table is know to 850 process. by updating this temporary table
4688 *--- we pass the info of 855 records that went into the 855 outbound flat.
4689
4690 Procedure Update855FlagIn850TempIpoth(pcorprth, pcoprth_850)
4691
4692 Local llRetVal, lnOldSelect
4693 Local lcTmpOprTH
4694 llRetVal = .T.
4695 lnOldSelect = Select()
4696 lcTmpOprTH = Sys(2015)
4697
4698 Select Distinct pkey From (pcorprth) Where Errs_Flg_H <> 'Y' Into Cursor (lcTmpOprTH)
4699
4700 With This
4701 .cSQLTempTable = ""
4702 If Reccount(lcTmpOprTH) > 0
4703 llRetVal = llRetVal And .GenerateSQLTempTable(lcTmpOprTH)
4704 If .PopulateSQLTempTable(lcTmpOprTH) And Not Empty(.cSQLTempTable) And llRetVal
4705 *--- TR 1034481 NH : Change 'Y' to 'P'
4706 * TR 1078104 KISHORE 22-APR-2014 added - , AUTO_PROC = 'Y'
4707 lcSQL = "UPDATE F SET PROC_855 = 'P', AUTO_PROC = 'Y' FROM " + pcoprth_850 + " F JOIN " + ;
4708 .cSQLTempTable + " T ON F.PKEY = T.PKEY "
4709 *=== TR 1034481 NH
4710 llRetVal = llRetVal And v_sqlexec(lcSQL)
4711 Endif
4712 Endif
4713 Endwith
4714 Select(lnOldSelect)
4715 Return llRetVal
4716 Endproc
4717
4718 ***********************************************************************************************
4719
4720 Procedure ScatterGather_STATUS_855
4721 Lparameter tcSource, tcTarget, tcParentTbl
4722
4723 Local lnOldSelect
4724 lnOldSelect = Select()
4725 Local loParentTbl
4726 Select(tcParentTbl)
4727 *--- do not populate detail for status 855 code = 'AK' or 'RJ'
4728 *- 1034481 10/06/08 YIK
4729 *- Bad logic. S/b AND instead of OR
4730 *- Scan For status_855 <> 'AK' Or status_855 <> 'RJ'
4731 Scan For status_855 <> 'AK' AND status_855 <> 'RJ'
4732
4733 Scatter Name loParentTbl
4734 Select(tcSource)
4735
4736 *--- TechRec 1041325 17-Sep-2009 T.Shenbagavalli removed condition And status_855 == loParentTbl.status_855 ---
4737 Scan For fkey = loParentTbl.pkey && And status_855 == loParentTbl.status_855 TR 1041325
4738
4739 Scatter Memvar Memo
4740 Select (tcTarget)
4741 Append Blank
4742 Gather Memvar Memo
4743 Endscan
4744 Endscan
4745
4746 Select(lnOldSelect)
4747 Endproc
4748
4749 *******************************************************************************************
4750
4751 *=== TR 1013007 NH 2005-10-17
4752
4753 *--- TR 1025228 6-Jul-2007 Goutam
4754 FUNCTION GetPOHistHeader
4755 LPARAMETERS pceTransHeader
4756 LOCAL lcSqlString, llRetVal, lcTempzzeipohh, lnOldSelect, lcTmpIpohhCursor
4757
4758 lnOldSelect = SELECT()
4759 lcTempzzeipohh = GetUniqueFileName()
4760 lcTmpIpohhCursor = GetUniqueFileName()
4761
4762 llRetVal = .T.
4763
4764 lcSqlString = "SELECT ord_num FROM " + pceTransHeader
4765
4766 llRetVal = llRetVal and v_sqlexec(lcSqlString, lcTmpIpohhCursor,,.t.)
4767
4768 .cSQLTempTable = ""
4769 IF RECCOUNT(lcTmpIpohhCursor) > 0
4770 llRetVal = llRetVal And .GenerateSQLTempTable(lcTmpIpohhCursor)
4771 llRetVal = llRetVal And .PopulateSQLTempTable(lcTmpIpohhCursor)
4772 ENDIF
4773
4774 IF Not EMPTY(.cSQLTempTable) And llRetVal
4775 *--- TR 1030443 22-Apr-2008 Goutam. Added extern_cust in the select list of following sql
4776 *--- TR 1078866 26-Jun-2014 Partha. Added edibill_to ---
4777 lcSqlString = "SELECT hh.ord_num, hh.ovnd_key, hh.extern_cust, hh.edibill_to " + ;
4778 " FROM zzeipohh hh JOIN " + .cSQLTempTable + " th " + ;
4779 " ON th.ord_num = hh.ord_num"
4780
4781 llRetVal = llRetVal and v_sqlexec(lcSqlString, lcTempzzeipohh)
4782
4783 *--- TR 1030443 22-Apr-2008 Goutam. Added extern_cust, word COALESCE and LEFT JOIN in the update list of following sql
4784 *--- TR 1078866 26-Jun-2014 Partha. Added edibill_to ---
4785 lcSqlString = "UPDATE th SET ovnd_key = COALESCE(hh.ovnd_key,''), " + ;
4786 " extern_cust = COALESCE(hh.extern_cust,'') " + ;
4787 " ,edibill_to = COALESCE(hh.edibill_to,'') " + ;
4788 " FROM " + pceTransHeader + " th LEFT JOIN " + lcTempzzeipohh + " hh " + ;
4789 "ON hh.ord_num = th.ord_num"
4790
4791 llRetVal = llRetVal and v_sqlexec(lcSqlString,,, .t.)
4792 .oLog.LogEntry("Updating oVnd_key in transaction header " + IIF(llRetVal,"completed.","failed."))
4793 ENDIF
4794
4795 IF USED(lcTempzzeipohh)
4796 USE IN (lcTempzzeipohh)
4797 ENDIF
4798 IF USED(lcTmpIpohhCursor)
4799 USE IN (lcTmpIpohhCursor)
4800 ENDIF
4801
4802 SELECT (lnOldSelect)
4803
4804 RETURN llRetVal
4805
4806 ENDFUNC
4807 *=== TR 1025228 6-Jul-2007 Goutam
4808
4809 *********************************************************************************************
4810 *--- TR 1030443 22-Apr-2008 Goutam
4811 FUNCTION PopulateShippingInfo
4812 LPARAMETERS pceTransHeader
4813
4814 LOCAL lcSqlString, llRetVal, lnOldSelect, lcTempShiper, lcTmpShprCursor
4815 llRetVal = true
4816 lnOldSelect = SELECT()
4817
4818 lcTempShiper = GetUniqueFileName()
4819 lcTmpShprCursor = GetUniqueFileName()
4820
4821 lcSqlString = "SELECT pkey, Shipper FROM " + pceTransHeader + " WHERE NOT EMPTY(Shipper)"
4822
4823 llRetVal = llRetVal and v_sqlexec(lcSqlString, lcTmpShprCursor,,.t.)
4824
4825 .cSQLTempTable = ""
4826 IF llRetVal AND RECCOUNT(lcTmpShprCursor) > 0
4827 llRetVal = llRetVal And .GenerateSQLTempTable(lcTmpShprCursor)
4828 llRetVal = llRetVal And .PopulateSQLTempTable(lcTmpShprCursor)
4829 ENDIF
4830
4831 IF llRetVal AND Not EMPTY(.cSQLTempTable)
4832 lcSqlString = "SELECT th.pkey, sh.Shipper, sh.scac_code, sh.edishptype, sh.ship_name " + ;
4833 " FROM zzxshipr sh JOIN " + .cSQLTempTable + " th " + ;
4834 " ON th.Shipper = sh.Shipper"
4835
4836 llRetVal = llRetVal and v_sqlexec(lcSqlString, lcTempShiper)
4837
4838 lcSqlString = "UPDATE th SET th.scac_code = sh.scac_code, " + ;
4839 " th.trans_type = sh.edishptype, " + ;
4840 " th.ship_name = sh.ship_name " + ;
4841 " FROM " + pceTransHeader + " th JOIN " + lcTempShiper + " sh " + ;
4842 "ON sh.pkey = th.pkey"
4843
4844 llRetVal = llRetVal and v_sqlexec(lcSqlString,,, .t.)
4845 .oLog.LogEntry("Updating shipper information in transaction header " + IIF(llRetVal,"completed.","failed."))
4846 ENDIF
4847
4848 IF USED(lcTmpShprCursor)
4849 USE IN (lcTmpShprCursor)
4850 ENDIF
4851 IF USED(lcTempShiper)
4852 USE IN (lcTempShiper)
4853 ENDIF
4854
4855 SELECT (lnOldSelect)
4856 RETURN llRetVal
4857
4858 ENDFUNC
4859
4860 FUNCTION PrepareCursorsForTRK
4861 LPARAMETERS pceTransHeader, pcoPRmRK
4862 LOCAL llRetVal, lnOldSelect, lcTempTrack
4863
4864 llRetVal = true
4865 lnOldSelect = SELECT()
4866
4867 lcTempTrack = GetUniqueFileName()
4868
4869 lcSqlString = "SELECT pkey, ord_num, pick_num, inv_num FROM " + pceTransHeader + " WHERE Errs_Flg_H <> 'Y'"
4870
4871 llRetVal = llRetVal and v_sqlexec(lcSqlString, lcTempTrack,,.t.)
4872
4873 .cSQLTempTable = ""
4874 IF llRetVal AND USED(lcTempTrack)
4875 llRetVal = llRetVal And .GenerateSQLTempTable(lcTempTrack)
4876 llRetVal = llRetVal And .PopulateSQLTempTable(lcTempTrack)
4877 ENDIF
4878
4879 IF llRetVal AND Not EMPTY(.cSQLTempTable)
4880
4881 lcSqlString = "select * from (select oh.pkey fkey, ph.track_no from zzoctnph ph " + ;
4882 " join zzoordsp sp " + ;
4883 " on sp.pkey = ph.fkey " + ;
4884 " join " + .cSQLTempTable + " oh " + ;
4885 " on oh.ord_num = sp.ord_num " + ;
4886 " and oh.pick_num = sp.pick_num " + ;
4887 " and oh.inv_num = sp.inv_num " + ;
4888 " where coalesce(ph.track_no,'') <> '' " + ;
4889 " and oh.pick_num > 0 " + ;
4890 " union all " + ;
4891 " select oh.pkey, ph.track_no from zzoctnsh ph " + ;
4892 " join zzoordsp sp " + ;
4893 " on sp.pkey = ph.fkey " + ;
4894 " join " + .cSQLTempTable + " oh " + ;
4895 " on oh.ord_num = sp.ord_num " + ;
4896 " and oh.pick_num = sp.pick_num " + ;
4897 " and oh.inv_num = sp.inv_num " + ;
4898 " where coalesce(ph.track_no,'') <> '' " + ;
4899 " and oh.pick_num > 0) tmp " + ;
4900 " group by fkey, track_no"
4901
4902 llRetVal = llRetVal and v_sqlexec(lcSqlString, pcoPRmRK)
4903 ENDIF
4904
4905 IF USED(lcTempTrack)
4906 USE IN (lcTempTrack)
4907 ENDIF
4908
4909 SELECT (lnOldSelect)
4910 RETURN llRetVal
4911 ENDFUNC
4912 *=== TR 1030443 22-Apr-2008 Goutam
4913
4914*--- TechRec 1038247 17-Mar-2009 T.Shenbagavalli ---
4915*===================================================================================
4916 FUNCTION PopulateDiscount
4917 LPARAMETERS pcTransDetail
4918
4919 LOCAL lcSqlString, llRetVal, lnSelect, lcSQLString, tcDiscr, tcTemp
4920 llRetVal = True
4921 lnSelect = SELECT()
4922
4923 tcTemp = GetUniqueFileName()
4924
4925 lcSQLString = "SELECT distinct discount FROM " + pcTransDetail + " WHERE Errs_Flg_D <> 'Y'"
4926
4927 llRetVal = llRetVal and v_sqlexec(lcSqlString, tcTemp,,.t.)
4928
4929 WITH This
4930 .cSQLTempTable = ""
4931 IF llRetVal AND USED( tcTemp )
4932 llRetVal = llRetVal And .GenerateSQLTempTable(tcTemp)
4933 llRetVal = llRetVal And .PopulateSQLTempTable(tcTemp)
4934 ENDIF
4935
4936 IF llRetVal AND Not EMPTY(.cSQLTempTable)
4937 lcSQLString = " SELECT c.discount, c.disc_perc " + ;
4938 " FROM zzxdiscr c JOIN " + .cSQLTempTable + " t " + ;
4939 " ON c.discount = t.discount "
4940
4941 tcDiscr = GetUniqueFileName()
4942 llRetVal = llRetVal and v_sqlexec(lcSqlString, tcDiscr )
4943
4944 lcSqlString = "UPDATE td SET td.disc_perc = cr.disc_perc " + ;
4945 " FROM " + pcTransDetail + " td JOIN " + tcDiscr + " cr " + ;
4946 "ON td.discount = cr.discount " + ;
4947 " WHERE td.Errs_Flg_D <> 'Y' "
4948
4949 llRetVal = llRetVal and v_sqlexec(lcSqlString,,, .t.)
4950 .oLog.LogEntry("Updating discount information in transaction detail " + IIF(llRetVal,"completed.","failed."))
4951 ENDIF
4952
4953 .TableClose(tcDiscr)
4954 .TableClose(tcTemp)
4955 ENDWITH
4956
4957 SELECT(lnSelect)
4958 RETURN llRetVal
4959 ENDFUNC
4960*===================================================================================
4961*=== TechRec 1038247 17-Mar-2009 T.Shenbagavalli ===
4962
4963* --- TR 1048571 8/18/10 CM
4964* --- Adding rolling color functionality to the 855(o) Process
4965*============================================================
4966
4967 FUNCTION ChangeColor
4968 LPARAMETERS tceoprtd
4969 LOCAL llRetVal, lnSelect
4970
4971 llRetVal = true
4972 lnSelect = SELECT()
4973
4974 WITH This
4975 REPLACE color_Code WITH org_color , ;
4976 org_color WITH Invoice_Color ;
4977 FOR !EMPTY(org_color) ;
4978 IN (tceoprtd)
4979
4980 GO TOP IN (tceoprtd)
4981 ENDWITH
4982
4983 SELECT (lnSelect)
4984 RETURN llRetVal
4985 ENDFUNC
4986
4987*============================================================
4988
4989 FUNCTION RestoreColor
4990 LPARAMETERS tceoprtd
4991 LOCAL llRetVal, lnSelect
4992
4993 llRetVal = true
4994 lnSelect = SELECT()
4995
4996 WITH This
4997 REPLACE org_color WITH Color_code, ;
4998 color_Code WITH Invoice_Color ;
4999 FOR !EMPTY(org_color) ;
5000 IN (tceoprtd)
5001
5002 GO TOP IN (tceoprtd)
5003 ENDWITH
5004
5005 SELECT (lnSelect)
5006 RETURN llRetVal
5007 ENDFUNC
5008
5009*============================================================
5010* === TR 1048571 8/18/10 CM
5011
5012 *--- TechRec 1058716 11-Jan-2012 jisingh ---
5013 FUNCTION ValidateForzani
5014 LPARAMETERS tcHeader, tcDetail, tcControl
5015 LOCAL llRetVal, lnSelect, lcSQLString, lnThermoCnt, lcTemplate, lcHdrTag, lcDtlTag, lcFields
5016
5017 llRetVal = true
5018 lnSelect = SELECT()
5019 lcHdrTag = "HEADER."
5020 lcDtlTag = "DETAIL."
5021 lcTemplate = "FORZANI 5010"
5022
5023 WITH This
5024 lcSQLString = " SELECT h.customer, h.store, h.ship_dc, h.center_code, h.consol_code, " + ;
5025 " h.ord_num, h.division, h.factor, h.location, h.errs_msg_h, " + ;
5026 " h.status_855, h.po_num, h.po_date, h.supplier_num, h.pkey " + ;
5027 " ,h.edibill_to " + ; && TR 1078866 26-Jun-2014 Partha
5028 " FROM " + tcHeader + " h " + ;
5029 " LEFT JOIN " + tcControl + " c " + ;
5030 " ON h.division = c.division " + ;
5031 " AND h.customer = c.customer " + ;
5032 " WHERE c.template = 'FORZANI 5010' "
5033
5034 llRetVal = llRetVal AND v_SQLExec(lcSQLString, "tcForzani",,true)
5035
5036 IF llRetVal AND USED("tcForzani") AND RECCOUNT("tcForzani") > 0
5037 IF .lUserInterface
5038 .InitThermo(.CountTotalRecs("tcForzani"))
5039 lnThermoCnt = 0
5040 ENDIF
5041
5042 llRetVal = llRetVal AND v_SQLExec("SELECT * FROM zzeoprta WHERE 1 = 2", "__tmpAddrs")
5043
5044 SELECT ("tcForzani")
5045 SCAN
5046 IF .lUserInterface
5047 lnThermoCnt = lnThermoCnt + 1
5048 .AdvanceThermo(lnThermoCnt)
5049 ENDIF
5050 lcFields = ""
5051
5052 IF EMPTY(status_855)
5053 lcFields = lcFields + IIF(EMPTY(lcFields), "", ",") + lcHdrTag + "STATUS_855"
5054 ENDIF
5055
5056 IF EMPTY(po_num)
5057 lcFields = lcFields + IIF(EMPTY(lcFields), "", ",") + lcHdrTag + "PO_NUM"
5058 ENDIF
5059
5060 IF EMPTY(po_date) OR (po_date = {01/01/1900})
5061 lcFields = lcFields + IIF(EMPTY(lcFields), "", ",") + lcHdrTag + "PO_DATE"
5062 ENDIF
5063
5064 IF EMPTY(supplier_num)
5065 lcFields = lcFields + IIF(EMPTY(lcFields), "", ",") + lcHdrTag + "SUPPLIER_NUM"
5066 ENDIF
5067
5068 *--- TR 1078866 26-Jun-2014 Partha ---
5069 IF EMPTY(edibill_to)
5070 lcFields = lcFields + IIF(EMPTY(lcFields), "", ",") + lcHdrTag + "EDIBILL_TO"
5071 ENDIF
5072 *=== TR 1078866 26-Jun-2014 Partha ===
5073
5074 IF Bcaddres(customer,, "_tmpAddrs", store, ship_dc, center_code, consol_code, ord_num, division, factor, location) ;
5075 AND USED("_tmpAddrs") AND .CreateVerticalAddress("_tmpAddrs", "__tmpAddrs", "", "", "ZZEOPRTA") AND USED("__tmpAddrs")
5076
5077 SELECT __tmpAddrs
5078 INDEX ON addr_type TAG addrtype
5079
5080 IF !SEEK("BT","__tmpAddrs","addrtype")
5081 lcFields = lcFields + IIF(EMPTY(lcFields), "", ",") + lcHdrTag + "BILL_TO"
5082 ENDIF
5083
5084 IF !SEEK("ST","__tmpAddrs","addrtype")
5085 lcFields = lcFields + IIF(EMPTY(lcFields), "", ",") + lcHdrTag + "SHIP_TO"
5086 ENDIF
5087 ENDIF
5088
5089 IF NOT EMPTY(lcFields)
5090 REPLACE errs_msg_h WITH errs_msg_h + lcTemplate + " MANDATORY FIELD(S) EMPTY/MISSING: " + lcFields + CRLF, ;
5091 errs_flg_h WITH "Y" FOR pkey = tcForzani.pkey IN (tcHeader)
5092 ENDIF
5093 SELECT ("tcForzani")
5094 ENDSCAN
5095 ENDIF
5096
5097
5098 lcSQLString = " SELECT d.status_855, d.pkey " + ;
5099 " FROM " + tcHeader + " h " + ;
5100 " JOIN " + tcDetail + " d " + ;
5101 " ON h.pkey = d.fkey " + ;
5102 " LEFT JOIN " + tcControl + " c " + ;
5103 " ON h.division = c.division " + ;
5104 " AND h.customer = c.customer " + ;
5105 " WHERE c.template = 'FORZANI 5010' " + ;
5106 " AND EMPTY(d.status_855) "
5107
5108 llRetVal = llRetVal AND v_SQLExec(lcSQLString, "tcForzani",,true)
5109
5110
5111 IF llRetVal AND USED("tcForzani") AND RECCOUNT("tcForzani") > 0
5112 IF .lUserInterface
5113 .InitThermo(.CountTotalRecs("tcForzani"))
5114 lnThermoCnt = 0
5115 ENDIF
5116
5117 SELECT ("tcForzani")
5118 SCAN
5119 IF .lUserInterface
5120 lnThermoCnt = lnThermoCnt + 1
5121 .AdvanceThermo(lnThermoCnt)
5122 ENDIF
5123 lcFields = ""
5124
5125 IF EMPTY(status_855)
5126 lcFields = lcFields + IIF(EMPTY(lcFields), "", ",") + lcDtlTag + "STATUS_855"
5127 ENDIF
5128
5129 IF NOT EMPTY(lcFields)
5130 REPLACE errs_msg_d WITH errs_msg_d + lcTemplate + " MANDATORY FIELD(S) EMPTY/MISSING: " + lcFields + CRLF, ;
5131 errs_flg_d WITH "Y" FOR pkey = tcForzani.pkey IN (tcDetail)
5132 ENDIF
5133 ENDSCAN
5134 .TableClose("tcForzani")
5135 ENDIF
5136
5137 IF .lUserInterface
5138 .ResetThermo()
5139 ENDIF
5140 ENDWITH
5141
5142 SELECT (lnSelect)
5143 RETURN llRetVal
5144 ENDFUNC
5145
5146*============================================================
5147
5148 FUNCTION PopulateDataWhseFrom850
5149 LPARAMETERS pceTransHeader, pceTransDetail, pceControl, pcWhseHeader, pcWhseDetail, pczzeiPOtWhse
5150 LOCAL llRetVal, lnSelect
5151
5152 llRetVal = true
5153 lnSelect = SELECT()
5154
5155 WITH This
5156 SELECT DISTINCT h.ipo_pkey FROM (pceTransHeader) h ;
5157 JOIN (pceControl) c ON h.division = c.division AND h.customer = c.customer ;
5158 WHERE c.data_whse = 'Y' INTO CURSOR __TmpCursor
5159
5160 IF USED("__TmpCursor") AND RECCOUNT("__TmpCursor") > 0
5161 SELECT s.pkey, s.ord_num, s.line_seq, s.sizebucket, s.segment, s.element, ;
5162 s.qualifier, s.value, t.ipo_pkey as fkey, t.ipo_pkey as WhsKey ; && TR 1100564 30-Nov-16 JPaul. new field created for filtering in createoprinterface method
5163 FROM (pczzeiPOtWhse) s, __TmpCursor t ;
5164 WHERE s.hfkey = t.ipo_pkey AND s.dfkey = 0 INTO CURSOR tcWhseHeader
5165
5166 .MakeCursorWritable("tcWhseHeader", pcWhseHeader)
5167 ENDIF
5168
5169
5170 SELECT DISTINCT d.line_seq, d.sizebucket, d.ipo_pkey, h.ipo_pkey as whskey, d.ipo_pkey as fkey ; && TR 1100564 30-Nov-16 JPaul. fields whskey and fkey are added
5171 FROM (pceTransHeader) h ;
5172 JOIN (pceTransDetail) d ON h.pkey = d.fkey ;
5173 JOIN (pceControl) c ON h.division = c.division AND h.customer = c.customer ;
5174 WHERE c.data_whse = 'Y' INTO CURSOR __TmpCursor
5175
5176 IF USED("__TmpCursor") AND RECCOUNT("__TmpCursor") > 0
5177 SELECT s.pkey, s.ord_num, s.line_seq, s.sizebucket, s.segment, ;
5178 s.element, s.qualifier, s.value, t.ipo_pkey AS fkey, t.whskey ; && TR 1100564 30-Nov-16 JPaul. field whskey added for filtering in createoprinterface method
5179 FROM (pczzeiPOtWhse) s, __TmpCursor t ;
5180 WHERE s.dfkey = t.ipo_pkey AND s.line_seq = t.line_seq INTO CURSOR tcWhseDetail
5181
5182 .MakeCursorWritable("tcWhseDetail", pcWhseDetail)
5183 ENDIF
5184 .TableClose("tcWhseHeader")
5185 .TableClose("tcWhseDetail")
5186 .TableClose("__TmpCursor")
5187 ENDWITH
5188
5189 SELECT (lnSelect)
5190 RETURN llRetVal
5191 ENDFUNC
5192 *=== TechRec 1058716 11-Jan-2012 jisingh ===
5193
5194*--- TechRec 1078545 27-Jun-2014 vkrishnamurthy ---
5195*============================================================
5196 PROCEDURE GetParamBRO
5197 LPARAMETERS tcParamBro, tcDefault
5198 LOCAL lnSelect, lnParmNameIndex, lcParmName
5199
5200 lcParmName = ""
5201 lnSelect = SELECT()
5202
5203 lnParmNameIndex = ASCAN(.aParamBROs,tcParamBro,1,ALEN(.aParamBROs,1),1,9)
5204
5205 *-- Validate parameter
5206 IF lnParmNameIndex > 0
5207 lcParmName = ALLTRIM(.aParamBROs[lnParmNameIndex, 2])
5208 ENDIF
5209
5210 IF EMPTY(lcParmName) AND TYPE("tcDefault") == "C" AND NOT EMPTY(tcDefault)
5211 lcParmName = tcDefault
5212 ENDIF
5213
5214 SELECT (lnSelect)
5215 RETURN lcParmName
5216 ENDPROC
5217*======================================================================
5218 FUNCTION SetParamBRO
5219 LOCAL llRetVal, lnSelect,lcNewShipDate
5220 llRetVal = true
5221 lnSelect = SELECT()
5222
5223 WITH This
5224 lcNewShipDate = .getParamBro("PRM_NEW_SHIP_DATE")
5225 IF EMPTY(lcNewShipDate)
5226 .cNewShipDate = DATE()
5227 ELSE
5228 SET PROCEDURE TO clsmacro addi
5229 loDate = CREATEOBJECT("DynamicDate",lcNewShipDate)
5230 IF TYPE("loDate") = "O"
5231 lcNewShipDate = loDate.GetDynamicDate(loDate.cFilterValue)
5232 ENDIF
5233 loDate = null
5234
5235 .cNewShipDate = CTOD(lcNewShipDate)
5236 IF EMPTY(.cNewShipDate)
5237 .LogEntry("New Ship Date Not Set.")
5238 ELSE
5239 .LogEntry("New Ship Date: " + TRANSFORM(.cNewShipDate))
5240 ENDIF
5241 ENDIF
5242 ENDWITH
5243
5244 SELECT (lnSelect)
5245 RETURN llRetVal
5246 ENDFUNC
5247*========================================================================
5248*=== TechRec 1078545 27-Jun-2014 vkrishnamurthy ===
5249
5250*--- TR 1083402 14-Jan-2015 Partha ---
5251 FUNCTION ValidateQuidsi
5252 LPARAMETERS tcHeader, tcDetail, tcControl
5253 LOCAL llRetVal, lnSelect, lcSQLString, lnThermoCnt, lcTemplate, lcHdrTag, lcDtlTag, lcFields
5254 llRetVal = true
5255 lnSelect = SELECT()
5256 WITH This
5257ASSERT .f.
5258 lcSQLString = " SELECT p.lk_code FROM zzxlookp p " + ;
5259 " WHERE p.Lk_Type ='ACK29 Codes' "
5260 llRetVal = llRetVal AND v_SQLExec(lcSQLString, "zzxlookp")
5261
5262 lcSQLString = " SELECT d.ack29, d.pkey, d.fkey, c.ack29_req, c.customer, c.division, h.ord_num " + ;
5263 " FROM " + tcHeader + " h with (buffer = .t.) " + ;
5264 " JOIN " + tcDetail + " d with (buffer = .t.) " + ;
5265 " ON h.pkey = d.fkey " + ;
5266 " LEFT JOIN " + tcControl + " c " + ;
5267 " ON h.division = c.division " + ;
5268 " AND h.customer = c.customer " + ;
5269 " WHERE c.ack29_req = 'Y' " + ;
5270 " AND (d.ack29 = '' OR " + ;
5271 " NOT EXISTS ( SELECT 1 FROM zzxlookp p " + ;
5272 " WHERE p.LK_CODE=d.ack29 ) ) "
5273
5274 llRetVal = llRetVal AND v_SQLExec(lcSQLString, "tcQuidsi",,true)
5275 .TableClose("zzxlookp")
5276
5277 IF llRetVal AND USED("tcQuidsi") AND RECCOUNT("tcQuidsi") > 0
5278 IF .lUserInterface
5279 .InitThermo(.CountTotalRecs("tcQuidsi"))
5280 lnThermoCnt = 0
5281 ENDIF
5282
5283 SELECT ("tcQuidsi")
5284 INDEX on STR(fkey) TAG fkey
5285 GOTO TOP
5286
5287 llRetVal = llRetVal AND .SetRelation("tcQuidsi", "fkey", tcHeader, "str(pkey)")
5288 GOTO TOP
5289
5290 REPLACE auto_proc WITH 'N' FOR !EOF("tcQuidsi") IN (tcHeader)
5291
5292 SET RELATION to
5293
5294 .TableClose("tcQuidsi")
5295 ENDIF
5296
5297 IF .lUserInterface
5298 .ResetThermo()
5299 ENDIF
5300 ENDWITH
5301
5302 SELECT (lnSelect)
5303 RETURN llRetVal
5304 ENDFUNC
5305
5306*============================================================
5307
5308
5309*=== TR 1083402 14-Jan-2015 Partha ===
5310
5311Enddefine