· 6 years ago · Apr 05, 2019, 09:10 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 *--- TR 1089159 19-Aug-2015 SMeenraja Added variable llAddressFound
2242 Local llOKtoContinue, lnOldSelect, llInterHeaderPkey, ltDatetime, lnInterHeaderOrd_num, lcFldName, lcShip_To, llAddressFound
2243 ltDatetime= Datetime()
2244 llOKtoContinue = .T.
2245 lnOldSelect = Select()
2246
2247 *- TR 1098611 FH
2248 LOCAL llHaveEDIH , llHaveEDID , lcWHSEScanFor
2249 llHaveEDIH = USED(pcWhseHeader) AND (This.CountTotalRecs(pcWhseHeader) > 0)
2250 llHaveEDID = USED(pcWhseDetail) AND (This.CountTotalRecs(pcWhseDetail) > 0)
2251 *- TR 1098611 FH
2252
2253 *--- TR 1100564 30-Nov-16 JPaul.
2254 LOCAL lnIPO_PKey
2255 lnIPO_PKey= 0
2256 *=== TR 1100564 30-Nov-16 JPaul.
2257
2258 * Get all interface header/detail to Memvar
2259 Select (pcInterHeader)
2260 Scatt Memvar Memo Blank
2261 Select (pcInterDetail)
2262 Scatt Memvar Memo Blank
2263
2264 * get all fields from trans header (history+shiporder+order header )
2265 Select * From (pcTransHeader) ;
2266 Where !(Errs_Flg_H= 'Y') ; && Empty(Errs_Msg_H)
2267 Order By customer, division ;
2268 Into Cursor tmpCurs
2269
2270 Select tmpCurs
2271 .nInterfaceRecords= Recc("TmpCurs") && number of good pick orders to process
2272 l_nThermoCnt = 0
2273 This.InitThermoWithCaption(@l_nThermoCnt, Recc('TmpCurs'), "Creating 855(o) Interface records...")
2274 This.oLog.LogEntry("Creating 855(o) Interface records...")
2275 Scan
2276 This.AdvanceThermoPlus(@l_nThermoCnt)
2277 Bcaddres(tmpCurs.customer,, "tmpAddrs", tmpCurs.Store, tmpCurs.ship_dc, ;
2278 tmpCurs.center_code, tmpCurs.consol_code, tmpCurs.ord_num, ;
2279 tmpCurs.division, tmpCurs.factor, tmpCurs.location)
2280
2281 lcShip_To = tmpAddrs.S_EDI_SHIP_TO
2282
2283 Select tmpCurs
2284 Scatt Memvar Memo
2285 * Create Interface header record
2286 *- 36646 1/27/03 YIK - No need to create new pkey
2287 *-m.pkey= v_NextPkey("ZZEOPRIH")
2288 If Empty(m.pkey)
2289 llOKtoContinue= .F.
2290 Exit && header scan
2291 Endif
2292 llInterHeaderPkey= m.pkey
2293 lnInterHeaderOrd_num= m.ord_num
2294
2295 *--- TR 1100564 30-Nov-16 JPaul.
2296 lnIPO_PKey= m.ipo_pkey
2297 *=== TR 1100564 30-Nov-16 JPaul.
2298
2299
2300 Select (pcInterHeader)
2301 Append Blank
2302 m.Ship_to = lcShip_To
2303 *- 1011846 08/25/05 YIK
2304 m.EDI_Shipto = lcShip_To
2305
2306 Gather Memvar Memo
2307
2308 * For each header locate 850 history header merge inbound fields into interface
2309 *-llOKtoContinue= This.UseIPOHistoryHeaderFields(customer, ord_num, pcInterHeader)
2310
2311 This.TimeStampDocument()
2312
2313 If tmpCurs.adr_resolv = 'Y'
2314 m.ord_num = lnInterHeaderOrd_num
2315 m.fkey = llInterHeaderPkey
2316 * Create Interface address record
2317
2318 * --- TR 1044108 12/2/09 CM --- Added 5th paramter to pass "ZZEOPRTA"
2319 * to generate pkeys for 855 address records, instead of using "ZZEOSHIA"
2320 *This.CreateVerticalAddress("TmpAddrs", pcInterAddress)
2321
2322 *--- TR 1089159 19-Aug-2015 SMeenraja Updated address if address available in FF
2323*!* This.CreateVerticalAddress("TmpAddrs", pcInterAddress, "", "", "ZZEOPRTA")
2324 * --- TR 1044108 12/2/09 CM
2325 If .lFrom850 AND NOT .lFrom945
2326 llAddressFound = .f.
2327
2328 *- 1097514 - let's resolve all address first, then add the ones we don't have from 850 ADR
2329 This.CreateVerticalAddress("TmpAddrs", pcInterAddress, "", "", "ZZEOPRTA")
2330 *- 1097514
2331
2332 SELECT VzzeoPRta_Proc
2333 SCAN FOR fkey = llInterHeaderPkey
2334 llAddressFound = .t.
2335 Scatter Memvar Memo
2336 m.fkey= llInterHeaderPkey
2337 If Empty(m.pkey)
2338 llOKtoContinue= .F.
2339 Exit && detail scan
2340 Endif
2341 Select (pcInterAddress)
2342 *-1097514 - Check for empty address, There could be OT record but empty address', if empty we want to use 850's
2343 LOCATE FOR fkey = m.fkey AND addr_type = m.addr_type AND NOT EMPTY(Address1)
2344 IF NOT FOUND()
2345 *- This Locate is to find the empty address and delete it so we don't have 2 of the same Addr type , one from 850 and one all empty.
2346 LOCATE FOR fkey = m.fkey AND addr_type = m.addr_type AND EMPTY(Address1)
2347 IF FOUND()
2348 DELETE
2349 ENDIF
2350
2351 Append Blank
2352 Gather Memvar Memo
2353 This.TimeStampDocument()
2354 Delete In VzzeoPRta_Proc
2355 ENDIF && 1097514
2356 ENDSCAN
2357 IF NOT llAddressFound
2358 This.CreateVerticalAddress("TmpAddrs", pcInterAddress, "", "", "ZZEOPRTA")
2359 ENDIF
2360 ELSE
2361 This.CreateVerticalAddress("TmpAddrs", pcInterAddress, "", "", "ZZEOPRTA")
2362 ENDIF
2363 *=== TR 1089159 19-Aug-2015 SMeenraja Updated address if address available in FF
2364
2365 Endif
2366 ************************************************************************************
2367 * create Interface details using division, ord_num
2368 ************************************************************************************
2369 Select (pcTransDetail)
2370 *--- TR 1013007 2005-10-28 NH turn around 855 from 850
2371 *-- in turn around 855 we do not have any order number so, we will use fkey instead
2372 *!* SCAN FOR ord_num= lnInterHeaderOrd_num &&inv_num = TmpCurs.inv_num AND division = TmpCurs.division
2373 Scan For fkey = llInterHeaderPkey
2374 *=== TR 1013007 2005-10-28 NH
2375 *************************************************************************
2376 * Get all transaction detail fields to Memvar
2377 Scatter Memvar Memo
2378 *************************************************************************
2379 *!* * Final ajustment to interface detail fields in Memvar
2380 *!* If !(used("tmpStylr") And (tmpStylr.division= division And tmpStylr.style= style))
2381 *!* vl_stylr(division,, "tmpStylr", Style)
2382 *!* Endif
2383 *!* m.style_name = tmpStylr.style_name
2384 *!* m.po4_uom = tmpStylr.uom
2385 *!* * in getsizedesc also compare for current temp cursor size_code
2386 *!* m.size_desc = This.getSizeDesc(m.division, tmpStylr.size_code, m.sizebucket)
2387
2388 *!* IF vl_colrr(color_code,,"tmpcolrr")
2389 *!* m.color_name = tmpcolrr.color_name
2390 *!* m.nrf_color = tmpcolrr.nrf_color
2391 *!* ENDIF
2392 *!* IF vl_dimer(m.division,,"tmpdimer", tmpStylr.size_code)
2393 *!* lcFldName = "tmpdimer.nrf_sz" + PADL(ALLTRIM(TRANS(m.sizebucket, "99")), 2, '0')
2394 *!* m.nrf_size = EVAL(lcFldName)
2395 *!* ENDIF
2396 ************************************************************************************
2397 *--- TR 1097094 7/22/16 Gurinder
2398 IF .lResolveUPCSKU
2399 DO case
2400 Case m.is_upcsku = "U"
2401 m.sku = ''
2402 Case m.is_upcsku = "S"
2403 m.upc = ''
2404 OTHERWISE
2405 ENDCASE
2406 ENDIF
2407 *=== TR 1097094 7/22/16 Gurinder
2408
2409 * Create Interface detail record
2410 m.fkey= llInterHeaderPkey
2411 *- 36646 1/27/03 YIK - No need to create new pkey
2412 *- m.pkey= v_NextPkey("ZZEoPRID")
2413 If Empty(m.pkey)
2414 llOKtoContinue= .F.
2415 Exit && detail scan
2416 Endif
2417 Select (pcInterDetail)
2418 Append Blank
2419 Gather Memvar Memo
2420 This.TimeStampDocument()
2421 ************************************************************************************
2422 * Delete Trans. detail record, already sucessfully create Inter. detail record
2423 * Tested already in scan and delete current record you are on does not messup the
2424 * scan loop
2425 Delete In (pcTransDetail)
2426 Endscan && details
2427
2428 *--- TR 1074595 KISHORE 22-JAN-2014
2429 IF tmpCurs.c_ack_by = 'P'
2430 lcTempCursor = GetUniqueFileName()
2431
2432 SELECT po_num,edi_store,edi_center,upc,sku,pkey as controlpkey,hpkey,dpkey,fkey, ;
2433 total_qty,line_status,division,style,color_code,lbl_code,dimension,size_desc ;
2434 FROM (pcStoreSDQ) ;
2435 WHERE fkey = llInterHeaderPkey ;
2436 INTO CURSOR (lcTempCursor)
2437
2438 IF USED(lcTempCursor) AND RECCOUNT(lcTempCursor) > 0
2439
2440 SELECT (lcTempCursor)
2441 SCAN FOR fkey = llInterHeaderPkey
2442 SCATTER MEMVAR MEMO
2443
2444 IF EMPTY(m.pkey)
2445 llOKtoContinue= .F.
2446 EXIT
2447 ENDIF
2448
2449 m.fkey = llInterHeaderPkey
2450 m.pkey= v_NextPkey("ZZEOPRISDQ")
2451
2452 SELECT VzzeopriSDQ_Proc
2453 APPEND BLANK
2454 GATHER MEMVAR MEMO
2455 This.TimeStampDocument()
2456 ENDSCAN
2457
2458 USE IN SELECT(lcTempCursor)
2459 ENDIF
2460 ENDIF
2461 *=== TR 1074595 KISHORE 22-JAN-2014
2462
2463 *- TR 1098611 FH
2464 IF .lFrom850 AND NOT .lFrom945
2465 *--- TR 1100564 30-Nov-16 JPaul. Condition changed for filtering EDIH/EDID records
2466 *lcWHSEScanFor = "fkey == llInterHeaderPkey"
2467 lcWHSEScanFor = "whskey == lnIPO_Pkey"
2468 *=== TR 1100564 30-Nov-16 JPaul.
2469 ELSE
2470 lcWHSEScanFor = "ord_num == lnInterHeaderOrd_num"
2471 ENDIF
2472
2473 IF llHaveEDIH
2474 SELECT (pcWhseHeader)
2475
2476 SCAN FOR &lcWHSEScanFor
2477 SCATTER MEMVAR MEMO
2478 m.pkey= v_NextPkey("ZZEOPRIWHSE")
2479 m.table_name = 'ZZOORDRH'
2480 m.sln_line = ''
2481 SELECT Vzzeopriwhse_proc
2482 APPEND BLANK
2483 GATHER MEMVAR MEMO
2484 This.TimeStampDocument()
2485 ENDSCAN
2486 ENDIF
2487
2488 IF llHaveEDID
2489 SELECT (pcWhseDetail)
2490 SCAN FOR &lcWHSEScanFor
2491 SCATTER MEMVAR MEMO
2492 m.pkey= v_NextPkey("ZZEOPRIWHSE")
2493 m.table_name = 'ZZOORDRD'
2494 m.sln_line = ''
2495 SELECT Vzzeopriwhse_proc
2496 APPEND BLANK
2497 GATHER MEMVAR MEMO
2498 This.TimeStampDocument()
2499 ENDSCAN
2500 ENDIF
2501 *- TR 1098611 FH
2502
2503 If !llOKtoContinue
2504 Exit && header scan
2505 Endif
2506 * delete tran header when move to inter header
2507 Delete For pkey= tmpCurs.pkey In (pcTransHeader)
2508 Endscan && headers
2509
2510 * cleanup
2511 With This
2512 .TableClose("TmpCurs")
2513 .TableClose("tmpTermr")
2514 .TableClose("tmpShipr")
2515 .TableClose("tmpAddrs")
2516 *!* .TableClose("tmpStylr")
2517 *!* .TableClose("tmpColrr")
2518 *!* .TableClose("tmpDimer")
2519 .CloseTempAddressCursors()
2520 Endwith
2521
2522 If This.lUserInterface
2523 * Reset Thermometer
2524 This.ResetThermo()
2525 Endif
2526
2527 Select(lnOldSelect)
2528 Return llOKtoContinue
2529 Endproc
2530
2531 ************************************************************************************
2532 * UnloadToFlatFile
2533 ***********************************************************************************
2534 Procedure UnLoadToFlatFile
2535 Lparameters tcProcess, tcFlatFile, tcInterfaceHeader, tcInterfaceDetail, ;
2536 tcInterfaceComment, tcInterfaceAddress, tcFinalSDQ, tcSourceSDQ, tcFinalSLN, tcControlCursor, tcTRK, ;
2537 tcInterfaceWhse
2538 *- TR 1098611 FH - replace tcInterfaceWH, tcInterfaceWD with tcInterfaceWhse
2539 *--- TechRec 1058716 11-Jan-2012 jisingh Added tcInterfaceWH, tcInterfaceWD ===
2540 *--- TR 1030443 24-Apr-2008 Goutam. Added parameter tcTRK in abovelist
2541
2542 Local llRetVal, lcSQLString, lnOldSelect
2543 *--- TR 1010900 NH
2544 Local lnHandle
2545 lnHandle = 0
2546 *=== TR 1010900 NH
2547 llRetVal= .F.
2548 lnOldSelect= Select()
2549 With This
2550 .oLog.LogEntry("Unloading to Flat File")
2551
2552 *---TR 1031624 JUL-14-2008 BR
2553 USE IN SELECT("__tcFlatFileList")
2554 Create Cursor __tcFlatFileList(TempFlatfile memo, OutBoundFlatFile memo)
2555 *===TR 1031624 JUL-14-2008 BR
2556
2557
2558 * get Metadata for vendor table
2559 If .GetTranslationDefinition(EDI_ALL_TRANSACTION, "tcEmVend")
2560 * get Metadata for a process
2561
2562 If .GetTranslationDefinition(tcProcess, "tcEmData")
2563 * Create Metadata work tables
2564 .CreateMetaDataWorkTable("tcEmVend", "VENDOR", "tcoPRmwV")
2565 .CreateMetaDataWorkTable("tcEmData", "HEADER", "tcoPRmwH")
2566 .CreateMetaDataWorkTable("tcEmData", "ADDRESS", "tcoPRmwA")
2567 .CreateMetaDataWorkTable("tcEmData", "HDRNOTES", "tcoPRmHN")
2568 .CreateMetaDataWorkTable("tcEmData", "DETAIL", "tcoPRmwD")
2569 .CreateMetaDataWorkTable("tcEmData", "DTLNOTES", "tcoPRmDN")
2570
2571 *--- TR 1030443 22-Apr-2008 Goutam
2572 IF this.lFrom945
2573 .CreateMetaDataWorkTable("tcEmData", "TRK", "tcoPRmRK")
2574 ENDIF
2575 *=== TR 1030443 22-Apr-2008 Goutam
2576
2577 *- 1005605 06/15/04 YIK
2578 *- Add SLNs
2579 .CreateMetaDataWorkTable("tcEmData", "SLN", "tcoPRSLN")
2580 *=
2581
2582 *--- TechRec 1058716 11-Jan-2012 jisingh ---
2583 .CreateMetaDataWorkTable("tcEmData", "EDIH", "tcoPrEDIH")
2584 .CreateMetaDataWorkTable("tcEmData", "EDID", "tcoPrEDID")
2585 SELECT tcoPrEDIH
2586 INDEX ON fkey TAG fkey
2587
2588 SELECT tcoPrEDID
2589 INDEX ON fkey TAG fkey
2590 *=== TechRec 1058716 11-Jan-2012 jisingh ===
2591
2592 *--- 36646 01/16/03 YIK
2593 *- Add SDQs
2594 **Because we put the CreateSDQ routine inside datafound portion, it
2595 **will crash if we go here and call the routine ConvertToSDQ unconditionally.
2596
2597 If Used(tcSourceSDQ)
2598 .CreateMetaDataWorkTable("tcEmData", "SDQ", "tcoPRmSD")
2599 .ConvertToSDQ(tcInterfaceHeader, tcInterfaceDetail, ;
2600 tcInterfaceComment, tcInterfaceAddress, tcFinalSDQ, tcSourceSDQ)
2601 Endif
2602 *=== 36646
2603 * Open Flatfile and append to it
2604
2605 * remove previous tmp text file
2606 *lcTempFlatfile= tcFlatFile+ ".tmp"
2607 *--- TR 1010900 NH
2608 lnHandle = 0
2609
2610 *--- TR 1072318 12-Aug-2013 Partha ---
2611*!* If Not .Tie_setup
2612*!* lcTempFlatfile= Strtran(Upper(tcFlatFile), ".DAT", ".TMP")
2613*!* Delete File (lcTempFlatfile)
2614*!* lnHandle= .OpenFlatFile(lcTempFlatfile, True)
2615*!* Endif
2616*!* If .Tie_setup Or lnHandle> 0
2617 *=== TR 1072318 12-Aug-2013 Partha ===
2618
2619 *=== TR 1010900 NH
2620 * Populate data to metadata work tables from interface tables
2621 .ScatterGather(tcInterfaceHeader, "tcoPRmwH")
2622 *--- TR 1013007 NH
2623 *-- scatter gather records that has valid 855 ack status code.
2624 * .ScatterGather(tcInterfaceDetail, "tcoPRmwD")
2625 .ScatterGather_STATUS_855(tcInterfaceDetail, "tcoPRmwD",tcInterfaceHeader)
2626 *=== TR 1013007 NH
2627 Select tcoPRmwD
2628 Index On fkey + Style+color_code+lbl_code+Dimension+size_desc Tag fkey
2629
2630 *-.ScatterGather(tcInterfaceComment, "tcoPRmwC")
2631 .ScatterGather(tcInterfaceAddress, "tcoPRmwA")
2632 .ScatterGatherNotes(tcInterfaceHeader, "tcoPRmHN", 254)
2633 .ScatterGatherNotes(tcInterfaceDetail, "tcoPRmDN", 254)
2634
2635 * Mash/group all records to a single flatfile
2636 * If get here will return true if successfully create flatfile
2637 *--- 36646 01/16/03 YIK
2638 *- Add SDQs
2639 If Used(tcFinalSDQ)
2640 .ScatterGather(tcFinalSDQ, "tcoPRmSD")
2641 Endif
2642 *- 1005605 06/15/04 YIK
2643 If Used(tcFinalSLN)
2644 .ScatterGather(tcFinalSLN, "tcOPRSLN")
2645 Endif
2646
2647 *--- TR 1030443 22-Apr-2008 Goutam
2648 IF this.lFrom945 AND USED(tcTRK)
2649 .ScatterGather(tcTRK, "tcoPRmRK")
2650 ENDIF
2651 *=== TR 1030443 22-Apr-2008 Goutam
2652
2653 *--- TechRec 1058716 11-Jan-2012 jisingh ---
2654 *- TR 1098611 FH - change to tcInterfaceWhse, added forString
2655 IF USED(tcInterfaceWhse)
2656 .ScatterGather(tcInterfaceWhse, "tcoPrEDIH",,,"table_name = 'ZZOORDRH'")
2657 ENDIF
2658
2659 *- TR 1098611 FH - change to tcInterfaceWhse, added forString
2660 IF USED(tcInterfaceWhse)
2661 .ScatterGather(tcInterfaceWhse, "tcoPrEDID",,,"table_name = 'ZZOORDRD'")
2662 ENDIF
2663 *=== TechRec 1058716 11-Jan-2012 jisingh ===
2664
2665 *--- TR 1030443 23-Apr-2008 Goutam Added parameter "tcoPRmRK"
2666 *--- TechRec 1058716 11-Jan-2012 jisingh Added tcoPrEDIH, tcoPrEDID ===
2667 llRetVal= .CreateFlatfile(tcProcess, "tcoPRmwV", ;
2668 "tcoPRmwH", "tcoPRmwD", "tcoPRmwC", "tcoPRmwA", "tcoPRmHN", ;
2669 "tcoPRmDN","tcoPRmSD", "tcOPRSLN",lnHandle, tcControlCursor, "tcoPRmRK", "tcoPrEDIH", "tcoPrEDID")
2670 *= 1005605
2671 *--- TR 1010900 NH
2672
2673 *--- TR 1072318 12-Aug-2013 Partha ---
2674*!* If Not .Tie_setup
2675*!* Fclose(lnHandle)
2676*!*
2677*!* * append to 855.dat form 855.dat.tmp
2678*!* If llRetVal
2679*!* *---TR 1031624 JUL-14-2008 BR
2680*!* *- Comment out to prevent flat file from being generated if table update fails
2681*!* *llRetVal=.CopyToFlatFile(lcTempFlatfile, tcFlatFile)
2682*!* INSERT INTO __tcFlatFileList(TempFlatfile,OutBoundFlatFile) VALUES (lcTempFlatfile, tcFlatFile)
2683*!* *===TR 1031624 JUL-14-2008 BR
2684*!* Endif
2685*!* ENDIF
2686 *=== TR 1072318 12-Aug-2013 Partha ===
2687
2688 *=== TR 1010900 NH
2689
2690 *--- TR 1072318 12-Aug-2013 Partha ---
2691*!* ENDIF
2692 *=== TR 1072318 12-Aug-2013 Partha ===
2693
2694 Endif
2695 Endif
2696 Endwith
2697 Select (lnOldSelect)
2698 Return llRetVal
2699 Endproc
2700 ************************************************************************************
2701 * group all records from metadata work tables to a flat text file
2702 * Notes: all datatype at metadata work layer will be CHARACTER
2703 ***********************************************************************************
2704 *- 1005605 06/15/04 YIK
2705 *- Added tcFinalSLN
2706 *============================================================
2707
2708 Procedure CreateFlatfile
2709 Lparameter tcProcess, tcWorkVendor, tcWorkHeader, ;
2710 tcWorkDetail, tcWorkComment, tcWorkAddress, tcHeaderNotes, tcDetailNotes, tcFinalSDQ, ;
2711 tcFinalSLN, pnHandle, tcControlCursor, tcoPRmRK, tcFinalEDIH, tcFinalEDID
2712 *--- TechRec 1058716 11-Jan-2012 jisingh Added tcFinalEDIH, tcFinalEDID ===
2713 *--- TR 1030443 23-Apr-2008 Goutam Added above parameter tcoPRmRK
2714
2715 Local llRetVal, lnSelect
2716
2717 llRetVal = True
2718 lnSelect = Select()
2719 With This
2720 *- 1005605 06/145/04 YIK
2721 If Used(tcFinalSLN)
2722 .lHaveSLN = (This.CountTotalRecs (tcFinalSLN)>0)
2723 Endif
2724 *=
2725 llRetVal = .FinalDataProcessing(tcProcess, tcWorkVendor, tcWorkHeader, ;
2726 tcWorkDetail, tcWorkComment, tcWorkAddress, tcHeaderNotes, tcDetailNotes, ;
2727 tcFinalSDQ, tcFinalSLN, tcControlCursor)
2728
2729 * --- TR 1044514 3/25/10 CM
2730 *llRetVal = llRetVal And .GroupAndSumData(tcWorkHeader, tcWorkDetail, ;
2731 tcWorkComment, tcWorkAddress, tcHeaderNotes, tcDetailNotes, tcFinalSLN)
2732 llRetVal = llRetVal And .GroupAndSumData(tcWorkHeader, tcWorkDetail, ;
2733 tcWorkComment, tcWorkAddress, tcHeaderNotes, tcDetailNotes, tcFinalSLN, tcControlCursor)
2734 * === TR 1044514 3/25/10 CM
2735
2736 *--- TR 1030443 23-Apr-2008 Goutam Added parameter tcoPRmRK
2737 *--- TechRec 1058716 11-Jan-2012 jisingh Added tcFinalEDIH, tcFinalEDID ===
2738 llRetVal = llRetVal And .WriteDataToFlatFile(tcWorkVendor, tcWorkHeader, ;
2739 tcWorkDetail, tcWorkComment, tcWorkAddress, tcHeaderNotes, tcDetailNotes, tcFinalSDQ, ;
2740 tcFinalSLN, pnHandle, tcControlCursor, tcoPRmRK, tcProcess, tcFinalEDIH, tcFinalEDID) && TR 1043557 DEC-4-09 BR - Added tcProcess
2741 Endwith
2742
2743 Select (lnSelect)
2744 Return llRetVal
2745 Endfunc
2746
2747
2748 *============================================================
2749
2750 Function FinalDataProcessing
2751 Lparameter tcProcess, tcWorkVendor, tcWorkHeader, ;
2752 tcWorkDetail, tcWorkComment, tcWorkAddress, tcHeaderNotes, tcDetailNotes, ;
2753 tcFinalSDQ, tcFinalSLN, tcControlCursor
2754 Local llRetVal, lnSelect, lcTempCursor, lcHeaderPkeysList, lcDetailPkeysList, ;
2755 lcSkuList, lcCurHPkey, lcCurDPkey
2756
2757 llRetVal = True
2758 lnSelect = Select()
2759 lcTempCursor = GetUniqueFileName()
2760 lcHeaderPkeysList = GetUniqueFileName()
2761 lcSkuList = GetUniqueFileName()
2762 lcDetailPkeysList = GetUniqueFileName()
2763
2764 With This
2765 *---Insert Verndor information into tcWorkVendor
2766
2767 Select h.customer, h.division, c.* ;
2768 from (tcWorkHeader) h, (tcControlCursor) c ;
2769 Where Alltrim(h.customer)==Alltrim(c.customer) ;
2770 And Alltrim(h.division)==Alltrim(c.division) ;
2771 Into Cursor (lcTempCursor) Group By h.customer, h.division
2772
2773 Select (lcTempCursor)
2774 Scan
2775 m.vnd_id = vnd_id
2776 m.vnd_vers = vnd_vers
2777 m.vnd_tran = tcProcess
2778
2779 Insert Into (tcWorkVendor) From Memvar
2780 Endscan
2781
2782 *** Manipulate pkeys, fkeys in Notes, Comments, Addresses, Details,
2783 *** Detail Notes and SDQs for those combinations of customer and division
2784 *** that need to be grouped by po_num
2785 *** Please note that we can only handle those records that Sku_UPC="N"
2786 *- 1005605 07/21/04 YIK
2787 *- Added h.edi_center to group by it.
2788 *- 1006635 08/11/04 YIK - Per May Co., don't group by edi center.
2789 *- send any one as ship-to
2790 *!* Select h.customer, h.division, h.po_num, h.edi_center ;
2791 *!* from (tcWorkHeader) h, (tcControlCursor) c ;
2792 *!* Where Alltrim(h.customer)==Alltrim(c.customer) ;
2793 *!* And Alltrim(h.division)==Alltrim(c.division) ;
2794 *!* And Alltrim(c.ack_by)=="P" And Not Alltrim(c.sku_upc)=="N" ;
2795 *!* Into Cursor (lcTempCursor) Group By h.customer, h.division, h.po_num, h.edi_center
2796
2797 *--- TR 1025228 3-Jul-2007 Goutam
2798 *Select h.customer, h.division, h.po_num ;
2799 from (tcWorkHeader) h, (tcControlCursor) c ;
2800 Where Alltrim(h.customer)==Alltrim(c.customer) ;
2801 And Alltrim(h.division)==Alltrim(c.division) ;
2802 And Alltrim(c.ack_by)=="P" And Not Alltrim(c.sku_upc)=="N" ;
2803 Into Cursor (lcTempCursor) Group By h.customer, h.division, h.po_num
2804
2805 *=== TR 1031304 03/10/2008 TT - Changed the ==P... line into WHERE clause
2806*!* Select distinct h.customer, h.division, h.oVnd_key, h.po_num ;
2807*!* from (tcWorkHeader) h LEFT JOIN (tcControlCursor) c ;
2808*!* ON Alltrim(h.customer)=Alltrim(c.customer) ;
2809*!* And Alltrim(h.division)=Alltrim(c.division) ;
2810*!* And Alltrim(h.oVnd_key)=Alltrim(c.oVnd_key) ;
2811*!* LEFT JOIN (tcControlCursor) c2 ;
2812*!* ON Alltrim(h.customer)=Alltrim(c2.customer) ;
2813*!* And Alltrim(h.division)=Alltrim(c2.division) ;
2814*!* And c2.oVnd_key = ' ' ;
2815*!* And Alltrim(c2.ack_by)=="P" And Not Alltrim(c2.sku_upc)=="N" ;
2816*!* Into Cursor (lcTempCursor)
2817
2818 Select distinct h.customer, h.division, h.oVnd_key, h.po_num ;
2819 from (tcWorkHeader) h LEFT JOIN (tcControlCursor) c ;
2820 ON Alltrim(h.customer)=Alltrim(c.customer) ;
2821 And Alltrim(h.division)=Alltrim(c.division) ;
2822 And Alltrim(h.oVnd_key)=Alltrim(c.oVnd_key) ;
2823 LEFT JOIN (tcControlCursor) c2 ;
2824 ON Alltrim(h.customer)=Alltrim(c2.customer) ;
2825 And Alltrim(h.division)=Alltrim(c2.division) ;
2826 And c2.oVnd_key = ' ' ;
2827 where (Alltrim( c.ack_by)=="P" And Not Alltrim( c.sku_upc)=="N");
2828 or (Alltrim(c2.ack_by)=="P" And Not Alltrim(c2.sku_upc)=="N") ;
2829 Into Cursor (lcTempCursor)
2830
2831 *=== TR 1031304 03/10/2008 TT - Changed the ==P... line into WHERE clause
2832 *=== TR 1025228 3-Jul-2007 Goutam
2833
2834 *= 1006635
2835 Select (lcTempCursor)
2836 Scan
2837 Scatter Memvar
2838 *** For each of the customer, division and po_num, get header pkey lists
2839 *- 1006635 08/11/04 YIK - Per May Co., don't group by edi center.
2840 *!* Select pkey from (tcWorkHeader) ;
2841 *!* where Alltrim(customer)==Alltrim(m.customer) ;
2842 *!* And Alltrim(division)==Alltrim(m.division) ;
2843 *!* And Alltrim(po_num)==Alltrim(m.po_num) ;
2844 *!* And Alltrim(edi_center) == Alltrim(m.edi_center) ;
2845 *!* Into Cursor (lcHeaderPkeysList) Group by pkey order by pkey asc
2846
2847 *--- TR 1025228 3-Jul-2007 Goutam
2848 *Select pkey From (tcWorkHeader) ;
2849 where Alltrim(customer)==Alltrim(m.customer) ;
2850 And Alltrim(division)==Alltrim(m.division) ;
2851 And Alltrim(po_num)==Alltrim(m.po_num) ;
2852 Into Cursor (lcHeaderPkeysList) Group By pkey Order By pkey Asc
2853 Select pkey From (tcWorkHeader) ;
2854 where Alltrim(customer)==Alltrim(m.customer) ;
2855 And Alltrim(division)==Alltrim(m.division) ;
2856 And Alltrim(oVnd_Key)==Alltrim(m.oVnd_Key) ;
2857 And Alltrim(po_num)==Alltrim(m.po_num) ;
2858 Into Cursor (lcHeaderPkeysList) Group By pkey Order By pkey Asc
2859 *=== TR 1025228 3-Jul-2007 Goutam
2860
2861 *= 1005605 YIK
2862 Select (lcHeaderPkeysList)
2863 Go Top
2864 lcCurHPkey = pkey
2865 ***For each of header pkey,
2866 *** 1: change the pKey of header to the first pKey of the same group,
2867 *** 2: change the fkey of header notes, comments, addresses, details, SDQs to the first pKey
2868 *** 3: Blank out store and ord_num because right now we are grouping by po_num
2869 Scan
2870 Replace Edi_Store With '', ord_num With '', pkey With lcCurHPkey ;
2871 For Alltrim(pkey)==Alltrim(&lcHeaderPkeysList..pkey) In (tcWorkHeader)
2872
2873 If Alltrim(pkey) == Alltrim(lcCurHPkey)
2874 Loop &&Don't need to do the rest
2875 Endif
2876
2877 If Used(tcWorkComment)
2878 Replace fkey With lcCurHPkey ;
2879 For Alltrim(fkey)==Alltrim(&lcHeaderPkeysList..pkey) In (tcWorkComment)
2880 Endif
2881
2882 Replace fkey With lcCurHPkey ;
2883 For Alltrim(fkey)==Alltrim(&lcHeaderPkeysList..pkey) In (tcWorkAddress)
2884
2885 Replace pkey With lcCurHPkey ;
2886 For Alltrim(pkey)==Alltrim(&lcHeaderPkeysList..pkey) In (tcHeaderNotes)
2887
2888 Replace fkey With lcCurHPkey ;
2889 For Alltrim(fkey)==Alltrim(&lcHeaderPkeysList..pkey) In (tcWorkDetail)
2890 *- 12/21/06 1020717 YIK
2891 If Used(tcFinalSDQ)
2892
2893 Replace fkey With lcCurHPkey ;
2894 For Alltrim(fkey)==Alltrim(&lcHeaderPkeysList..pkey) In (tcFinalSDQ)
2895 ENDIF
2896 Endscan
2897
2898 ***Get Sku list that belong to current lcCurHPkey. (We already update the fkey in detail)
2899 Select Style, color_code, lbl_code, Dimension, size_desc ;
2900 From (tcWorkDetail) Where Alltrim(fkey)==Alltrim(lcCurHPkey) ;
2901 Into Cursor (lcSkuList) ;
2902 Group By Style, color_code, lbl_code, Dimension, size_desc ;
2903 Order By Style, color_code, lbl_code, Dimension, size_desc
2904
2905 ***For each of the skus that belong to current po(lcCurHPkey),
2906 *** get detailPkey list and pick the first pkey
2907 Select (lcSkuList)
2908 Scan
2909 Scatter Memvar
2910 Select pkey From (tcWorkDetail) ;
2911 where Alltrim(fkey)==Alltrim(lcCurHPkey) And ;
2912 Alltrim(Style)==Alltrim(m.Style) And ;
2913 Alltrim(color_code)==Alltrim(m.color_code) And ;
2914 Alltrim(lbl_code)==Alltrim(m.lbl_code) And ;
2915 Alltrim(Dimension)==Alltrim(m.Dimension) And ;
2916 Alltrim(size_desc)==Alltrim(m.size_desc) ;
2917 Into Cursor (lcDetailPkeysList) ;
2918 Group By pkey Order By pkey Asc
2919
2920 ***For each of detail pKey,
2921 *** 1: replace pKey in detail to the first pKey we hit
2922 *** 2: replace fKey of detail notes to the first pKey
2923 Select (lcDetailPkeysList)
2924 lcCurDPkey = pkey
2925 Scan
2926 If Alltrim(pkey) == Alltrim(lcCurDPkey)
2927 Loop &&Don't need to do anything for the first pkey
2928 Endif
2929
2930 Replace pkey With lcCurDPkey ;
2931 For Alltrim(pkey)==Alltrim(&lcDetailPkeysList..pkey) In (tcWorkDetail)
2932
2933 Replace pkey With lcCurDPkey ;
2934 For Alltrim(pkey)==Alltrim(&lcDetailPkeysList..pkey) In (tcDetailNotes)
2935
2936 *- 1005605 06/15/04 YIK
2937 Replace itm_pkey With lcCurDPkey ;
2938 For Alltrim(itm_pkey)==Alltrim(&lcDetailPkeysList..pkey) In (tcFinalSLN)
2939 *=
2940 Endscan
2941
2942 Select (lcSkuList)
2943 Endscan
2944
2945
2946 Select (lcTempCursor)
2947 Endscan
2948
2949 .TableClose(lcTempCursor)
2950 .TableClose(lcHeaderPkeysList)
2951 .TableClose(lcSkuList)
2952 .TableClose(lcDetailPkeysList)
2953 Endwith
2954
2955 Select (lnSelect)
2956 Return llRetVal
2957 Endfunc
2958
2959 *============================================================
2960
2961 Function GroupAndSumData
2962 * --- TR 1044514 3/25/10 CM --- Added tcControlCursor
2963 Lparameters tcWorkHeader, tcWorkDetail, tcWorkComment, tcWorkAddress, ;
2964 tcHeaderNotes, tcDetailNotes, tcFinalSLN, tcControlCursor
2965 Local llRetVal, lnSelect, lcCursorName
2966
2967 llRetVal = True
2968 lnSelect = Select()
2969 lcCursorName = GetUniqueFileName()
2970
2971 With This
2972 *** For those orders that need to be sent out by po_num, we already blank out ;
2973 *** ord_num. So we can group by ord_num here.
2974 *- 1005605 07/21/04 YIK
2975 *- group by edi_center
2976 *- 1006635 08/11/04 YIK - Per May Co., don't group by edi center.
2977 *!* Select * from (tcWorkHeader) into cursor (lcCursorName) ;
2978 *!* group by customer, division, po_num, edi_center, ord_num
2979 *--- TR 1025228 Goutam
2980 *Select * From (tcWorkHeader) Into Cursor (lcCursorName) ;
2981 group By customer, division, po_num, ord_num
2982
2983 *--- TR 1038204 JAN-22-2008 BR
2984 *Select * From (tcWorkHeader) Into Cursor (lcCursorName) ;
2985 group By customer, division, oVnd_key, po_num, ord_num
2986 *- 1077979 07/08/14 YIK
2987 *- Add ord_num to grouping in case the same store is repeated per PO
2988 Select * From (tcWorkHeader) Into Cursor (lcCursorName) ;
2989 group By customer, division, oVnd_key, po_num, edi_store, ord_num
2990 *=== TR 1038204 JAN-22-2008 BR
2991
2992 *=== TR 1025228 Goutam
2993
2994 *=
2995 .TableClose(tcWorkHeader)
2996 MakeCursorWritable(lcCursorName, tcWorkHeader)
2997
2998 If Used(tcWorkComment)
2999 Select Distinct * From (tcWorkComment) Into Cursor (lcCursorName)
3000 .TableClose(tcWorkComment)
3001 MakeCursorWritable(lcCursorName, tcWorkComment)
3002 Endif
3003
3004 Select Distinct * From (tcWorkAddress) Into Cursor (lcCursorName)
3005 .TableClose(tcWorkAddress)
3006 MakeCursorWritable(lcCursorName, tcWorkAddress)
3007
3008 Select Distinct * From (tcHeaderNotes) Into Cursor (lcCursorName)
3009 .TableClose(tcHeaderNotes)
3010 MakeCursorWritable(lcCursorName, tcHeaderNotes)
3011
3012 Select Distinct * From (tcDetailNotes) Into Cursor (lcCursorName)
3013 .TableClose(tcDetailNotes)
3014 MakeCursorWritable(lcCursorName, tcDetailNotes)
3015
3016 *- 1005605 06/15/04 YIK
3017 If .lHaveSLN
3018 Select Distinct * From (tcFinalSLN) Into Cursor (lcCursorName)
3019 .TableClose(tcFinalSLN)
3020 MakeCursorWritable(lcCursorName, tcFinalSLN)
3021 Select (tcFinalSLN)
3022 Index On itm_pkey Tag pkey
3023 Endif
3024 *=
3025 *--- TR 1030443 22-Apr-2008 Goutam
3026 IF NOT this.lFrom945
3027 *=== TR 1030443 22-Apr-2008 Goutam
3028 *--- TR 1035793 07-Oct-2008 TEJAS : Added Line_seq to Group By
3029 Select *, ;
3030 sum(Iif(Alltrim(line_status)=="P", Val(total_qty), 0)) As CalPick_QTY, ;
3031 sum(Iif(Alltrim(line_status)=="I", Val(total_qty), 0)) As CalInvoice_QTY, ;
3032 sum(Iif(Alltrim(line_status)=="C", Val(total_qty), 0)) As CalCancel_QTY, ;
3033 Sum(Val(total_qty)) As CalTotal_Qty ;
3034 from (tcWorkDetail) ;
3035 Into Cursor (lcCursorName) ;
3036 Group By fkey, Line_Seq, Style, color_code, lbl_code, Dimension, size_desc
3037 *--Group By fkey, Style, color_code, lbl_code, Dimension, size_desc
3038 *=== TR 1035793 07-Oct-2008 TEJAS : Added Line_seq to Group By
3039 Delete From (tcWorkDetail)
3040 Select (lcCursorName)
3041 Scan
3042 Scatter Memvar
3043 m.total_qty = Trans(m.CalTotal_Qty, '9999999999')
3044 m.OpenPickQty = Trans(m.CalPick_QTY, '9999999999')
3045 m.InvoiceQty = Trans(m.CalInvoice_QTY, '9999999999')
3046 m.CancelQty = Trans(m.CalCancel_QTY, '9999999999')
3047
3048 Insert Into (tcWorkDetail) From Memvar
3049 Endscan
3050 *--- TR 1030443 22-Apr-2008 Goutam
3051 ELSE
3052 *--- TR 1035793 07-Oct-2008 TEJAS : Added Line_seq to Group By
3053 Select *, ;
3054 sum(Iif(Alltrim(line_status)<>"I", Val(total_qty), 0)) As NotShipped_QTY, ;
3055 sum(Iif(Alltrim(line_status)=="I", Val(total_qty), 0)) As Shipped_QTY, ;
3056 Iif(Alltrim(line_status)<>"I", 'N', 'Y') As Shipped ;
3057 from (tcWorkDetail) ;
3058 Into Cursor (lcCursorName) ;
3059 Group By fkey, Line_Seq, Style, color_code, lbl_code, Dimension, size_desc, shipped
3060 *--Group By fkey, Style, color_code, lbl_code, Dimension, size_desc, shipped
3061 *=== TR 1035793 07-Oct-2008 TEJAS : Added Line_seq to Group By
3062 Delete From (tcWorkDetail)
3063 Select (lcCursorName)
3064 SCAN
3065 SCATTER MEMVAR
3066 IF Shipped = 'Y'
3067 m.total_qty = Trans(m.Shipped_QTY, '9999999999')
3068 m.OpenPickQty = TRANSFORM(0)
3069 m.InvoiceQty = m.Total_Qty
3070 m.line_status = 'I'
3071 ELSE
3072 m.total_qty = Trans(m.NotShipped_QTY, '9999999999')
3073 m.line_status = 'O'
3074 m.OpenPickQty = m.Total_Qty
3075 m.InvoiceQty = TRANSFORM(0)
3076 ENDIF
3077 INSERT INTO (tcWorkDetail) FROM MEMVAR
3078 ENDSCAN
3079 ENDIF
3080 *=== TR 1030443 22-Apr-2008 Goutam
3081
3082 * --- TR 1044514 3/25/10 CM/NSD
3083 IF .lVMI
3084 .GroupAndSumDataForVMI(tcWorkDetail, tcWorkHeader, tcControlCursor, tcFinalSLN)
3085 ENDIF
3086 * === TR 1044514 3/25/10 CM/NSD
3087
3088 .TableClose(lcCursorName)
3089 Endwith
3090
3091 Select (lnSelect)
3092 Return llRetVal
3093 Endfunc
3094
3095 *============================================================
3096
3097 Function WriteDataToFlatFile
3098 Lparameters tcWorkVendor, tcWorkHeader, ;
3099 tcWorkDetail, tcWorkComment, tcWorkAddress, tcHeaderNotes, ;
3100 tcDetailNotes, tcFinalSDQ, tcFinalSLN, pnHandle, tcControlCursor, tcoPRmRK, tcProcess, ; && TR 1043557 DEC-4-09 BR - ADDED: tcProcess
3101 tcFinalEDIH, tcFinalEDID
3102 *--- TechRec 1058716 11-Jan-2012 jisingh Added tcFinalEDIH, tcFinalEDID ===
3103 *--- TR 1030443 23-Apr-2008 Goutam Added above parameter tcoPRmRK
3104
3105 *--- TechRec 1058716 11-Jan-2012 jisingh ---
3106 LOCAL lnCurrentIPOPkey, lnDetIPOPkey
3107 *=== TechRec 1058716 11-Jan-2012 jisingh ===
3108 LOCAL lcCurEan &&TR 1073064 30-Aug-13 Venuk.
3109 Local llRetVal, lnSelect, lcString, lnCurrentPkey, lnDetPkey, lcCurSku, lcCurUpc
3110 *--- TR 1014084 NH
3111 Local lcPrevVnd_id, llCustomerChanged, llVndstring_req
3112 llCustomerChanged = .f.
3113 lcPrevVnd_id = ""
3114 llVndstring_req = .f.
3115
3116 *- TR 1074987 FH
3117 LOCAL lcPreviousVndQual, lcPreviousVndID
3118 lcPreviousVndQual = ""
3119 lcPreviousVndID = ""
3120 *- TR 1074987 FH
3121
3122 *=== TR 1014084 NH
3123 llRetVal = True
3124 lnSelect = Select()
3125 *--- TR 1010900 NH:
3126 Local lnHandle, lcEDIPath, lcPreviousCustomer, lcPreviousDivision, lcMac && TR 1043557 DEC-4-09 BR - ADDED: lcMac
3127 Store "" To lcPreviousCustomer, lcPreviousDivision
3128 lcEDIPath = This.GetEDIFlatFileDirectory("Outbound")
3129 lnHandle = 0
3130
3131 *-TR 1087742 FH
3132 STORE ',' TO lcAltFileNameList
3133 lNewFile = .F.
3134
3135 Select (tcWorkHeader)
3136
3137 Index On customer + division Tag Custdiv
3138
3139 *=== TR 1010900 NH
3140
3141 *--- TechRec 1058716 11-Jan-2012 jisingh ---
3142 LOCAL llHaveEDIH, llHaveEDID, lnCurrentOrdNum
3143 llHaveEDIH = USED(tcFinalEDIH) AND (This.CountTotalRecs(tcFinalEDIH) > 0)
3144 llHaveEDID = USED(tcFinalEDID) AND (This.CountTotalRecs(tcFinalEDID) > 0)
3145 *=== TechRec 1058716 11-Jan-2012 jisingh ===
3146
3147 *--- TR 1072318 12-Aug-2013 Partha ---
3148 lcAltFilename = ""
3149 *=== TR 1072318 12-Aug-2013 Partha ===
3150ASSERT .f.
3151 With This
3152 Select (tcWorkHeader)
3153 Scan
3154 lcString = ""
3155 *--- TR 1014084 NH
3156 llCustomerChanged = (customer <> lcPreviousCustomer)
3157 *=== TR 1014084 NH
3158
3159 Scatter Memvar
3160 Select (tcControlCursor)
3161
3162 *--- TR 1025228 6-Jul-2007 Goutam
3163 *Locate For Alltrim(customer)==Alltrim(m.customer) ;
3164 And Alltrim(division)==Alltrim(m.division)
3165 Locate For Alltrim(customer)==Alltrim(m.customer) ;
3166 And Alltrim(division)==Alltrim(m.division) ;
3167 And Alltrim(oVnd_Key)==Alltrim(m.oVnd_Key)
3168
3169 IF NOT FOUND()
3170 Locate For Alltrim(customer)==Alltrim(m.customer) ;
3171 And Alltrim(division)==Alltrim(m.division) ;
3172 And oVnd_Key = ' '
3173 ENDIF
3174 *=== TR 1025228 6-Jul-2007 Goutam
3175
3176 If Not Found() &&Should always found
3177 llRetVal = .F.
3178 EXIT
3179 LOOP
3180 ENDIF
3181
3182 *--- TR 1072318 12-Aug-2013 Partha ---
3183 lcAltFilename = ALLTRIM(alt_file_name)
3184 *=== TR 1072318 12-Aug-2013 Partha ===
3185
3186 *- 1059585 FH
3187*!* Select (tcWorkVendor)
3188*!* Locate For Alltrim(customer)==Alltrim(m.customer) ;
3189*!* And Alltrim(division)==Alltrim(m.division)
3190 *- 1059585 FH
3191
3192 *-- TR 1043557 DEC-4-09 BR
3193 *Select (tcWorkVendor)
3194 *Locate For Alltrim(customer)==Alltrim(m.customer) ;
3195 * And Alltrim(division)==Alltrim(m.division)
3196
3197 *If Found() &&Should always found, because we generate tcWorkVendor by tcWorkHeader
3198 *--- TR 1010900 NH
3199 *If .Tie_setup And Not (customer == lcPreviousCustomer And division == lcPreviousDivision)
3200 * TR 1074987 FH - break using vnd_qual, and vnd_id
3201 *IF !(customer == lcPreviousCustomer And division == lcPreviousDivision)
3202 If !(vnd_qual == lcPreviousVndQual AND vnd_ID == lcPreviousVndID)
3203 lcPreviousCustomer = customer
3204 lcPreviousDivision = division
3205
3206 lcPreviousVndQual = vnd_qual && TR 1074987 FH
3207 lcPreviousVndID = vnd_id && TR 1074987 FH
3208
3209 lcMac= [vl_] + tcProcess + "cr" + [(Customer, "", "__Ecntrc", Division)]
3210 &lcMac
3211 IF Used('__Ecntrc')
3212 SELECT (tcWorkVendor)
3213 APPEN BLANK
3214 REPLACE vnd_id With __Ecntrc.vnd_id,;
3215 vnd_tran With tcProcess,;
3216 vnd_vers With __Ecntrc.vnd_vers In (tcWorkVendor)
3217 SELECT (tcWorkHeader)
3218 * === TR 1043557 DEC-4-09 BR
3219
3220 *--- TR 1072318 12-Aug-2013 Partha ---
3221*!* IF .Tie_setup && TR 1043557 DEC-4-09 BR
3222 *=== TR 1072318 12-Aug-2013 Partha ===
3223
3224 *- close previously opened file
3225 If lnHandle > 0
3226 Fclose(lnHandle)
3227 If llRetVal
3228 *---TR 1031624 JUL-14-2008 BR
3229 *- Comment out to prevent flat file from being generated if table update fails
3230 *llRetVal =.CopyToFlatFileInBlock(lcTempFlatfile, lcOutBoundFlatFile)
3231 *-TR 1087742 - FH
3232 *INSERT INTO __tcFlatFileList(TempFlatfile,OutBoundFlatFile) VALUES (lcTempFlatfile, lcOutBoundFlatFile)
3233 .InsertIntoFlatFileCursor("__tcFlatFileList",lcTempFlatfile,lcOutBoundFlatFile)
3234 *===TR 1031624 JUL-14-2008 BR
3235 Endif
3236 ENDIF
3237
3238 *- new flat file name
3239 lcOutBoundFlatFile = lcEDIPath + Alltrim(Evaluate(tcWorkVendor + ".vnd_id")) + "855.DAT"
3240 lcTempFlatfile = Strtran(Upper(lcOutBoundFlatFile), ".DAT", ".TMP")
3241
3242 *--- TR 1072318 12-Aug-2013 Partha ---
3243 IF !.Tie_setup
3244 lcOutBoundFlatFile = lcEDIPath + "855.DAT"
3245 lcTempFlatfile = Strtran(Upper(lcOutBoundFlatFile), ".DAT", ".TMP")
3246 ENDIF
3247 IF !EMPTY(ALLTRIM(lcAltFilename))
3248 lcOutBoundFlatFile = lcEDIPath + ALLTRIM(lcAltFilename) + ".DAT"
3249 lcTempFlatfile = Strtran(Upper(lcOutBoundFlatFile), ".DAT", ".TMP")
3250 ENDIF
3251 *=== TR 1072318 12-Aug-2013 Partha ===
3252
3253 *-TR 1087742 FH
3254 *- TR 1089432 FH - changed lcTempFlatfile to lcTempFileName
3255 lcTempFileName = STRTRAN(lcTempFlatfile,UPPER(lcEDIPath), "") && Get rid of path
3256 lcTempFileName = STRTRAN(lcTempFileName, ".TMP", "") && get rid of ext
3257 IF ATC(","+ALLTRIM(lcTempFileName)+",",lcAltFileNameList ) = 0
3258 lcAltFileNameList = lcAltFileNameList + ALLTRIM(lcTempFileName) + ","
3259 lNewFile = .T.
3260 ELSE
3261 lNewFile = .F.
3262 ENDIF
3263
3264 IF lNewFIle
3265 Delete File (lcTempFlatfile)
3266 ENDIF
3267 *-TR 1087742 FH
3268
3269 lnHandle = .OpenFlatFile(lcTempFlatfile, .T.)
3270 If lnHandle <= 0
3271 Loop
3272 ENDIF
3273 =FSEEK(lnHandle,0,2) && TR 1087742 FH
3274
3275 *--- TR 1072318 12-Aug-2013 Partha ---
3276*!* ENDIF && TR 1043557 DEC-4-09 BR -- .Tie_setup
3277 *=== TR 1072318 12-Aug-2013 Partha ===
3278
3279 *=== TR 1010900 NH
3280
3281 *- 1009713 3/3/05 YIK
3282
3283 *--- TR 1014084 NH
3284 *--- TR 1015001 NH remove special logic for VND string writing for TIE setup
3285 *!* llVndstring_req = .f.
3286 *!* IF .Tie_setup
3287 *!* IF llCustomerChanged OR (lcPrevVnd_id <> EVALUATE(tcWorkVendor + ".vnd_id"))
3288 *!* llVndstring_req = .t.
3289 *!* ENDIF
3290 *!* ELSE
3291 *!* llVndstring_req = .t. && 1043557 Dont need this anymore.
3292 *!* ENDIF
3293 *=== TR 1015001 NH
3294
3295 *-- 1043557 DEC-4-09 BR - Dont need this anymore.
3296 *IF llVndstring_req
3297 *lcString = EDI_VENDOR_TAG + lcDelimiter + .ConvertRecordToText(tcWorkVendor, llDelimited, lcDelimiter)
3298 *ENDIF
3299 *== 1043557 DEC-4-09 BR
3300
3301 *=== TR 1014084 NH
3302 *==
3303 Else
3304 llRetVal = .F.
3305 EXIT
3306 ENDIF && 1043557 DEC-4-09 BR - IF Used('__Ecntrc')
3307 ENDIF && 1043557 DEC-4-09 BR - !(customer == lcPreviousCustomer And division == lcPreviousDivision)
3308
3309 *-- TR 1043557 DEC-4-09 BR
3310 *llDelimited = (&tcControlCursor..EDI_Format = 'D')
3311 *lcDelimiter = Iif(llDelimited, &tcControlCursor..EDI_Delim, "")
3312 llDelimited = (__Ecntrc.EDI_Format = 'D')
3313 lcDelimiter = IIF(llDelimited, __Ecntrc.EDI_Delim, "")
3314
3315 lcString = EDI_VENDOR_TAG + lcDelimiter + .ConvertRecordToText(tcWorkVendor, llDelimited, lcDelimiter)
3316 *== TR 1043557 DEC-4-09 BR
3317
3318 Select (tcWorkHeader)
3319 *- 1009713 3/3/05 YIK
3320 lcString = lcString + EDI_HEADER_TAG + lcDelimiter + .ConvertRecordToText(tcWorkHeader, llDelimited, lcDelimiter)
3321
3322 lnCurrentPkey= &tcWorkHeader..pkey
3323 *--- TechRec 1058716 11-Jan-2012 jisingh ---
3324 lnCurrentIPOPkey = EVALUATE(tcWorkHeader + ".ipo_pkey")
3325 *=== TechRec 1058716 11-Jan-2012 jisingh ===
3326
3327 lnOrdNum = EVALUATE(tcWorkheader + '.ord_num') && TR 1089471 KISHORE 24-SEP-2015
3328
3329 *--- TR 1030443 23-Apr-2008 Goutam
3330 IF this.lFrom945 AND USED(tcoPRmRK)
3331 SELECT (tcoPRmRK)
3332
3333 * --- TR 1034281 NSD 6/24/08
3334 * should be scanning by fkey not pkey
3335 *SCAN FOR Alltrim(pkey) == Alltrim(lnCurrentPkey)
3336 SCAN FOR Alltrim(fkey) == Alltrim(lnCurrentPkey)
3337 * === TR 1034281 NSD 6/24/08
3338
3339 lcString = lcString + EDI_TRK_TAG + lcDelimiter + .ConvertRecordToText(tcoPRmRK, llDelimited, lcDelimiter)
3340 ENDSCAN
3341 ENDIF
3342 *=== TR 1030443 23-Apr-2008 Goutam
3343
3344 Select (tcHeaderNotes)
3345 Scan For Alltrim(pkey) == Alltrim(lnCurrentPkey)
3346 *- 1009713 3/3/05 YIK
3347 lcString = lcString + EDI_HNT_TAG + lcDelimiter + .ConvertRecordToText(tcHeaderNotes, llDelimited, lcDelimiter)
3348 Endscan
3349
3350 *****************************output comment string "CMT" anthing # ST/SF
3351 If Used(tcWorkComment)
3352 Select (tcWorkComment)
3353 Scan For Alltrim(fkey) == Alltrim(lnCurrentPkey)
3354 *- 1009713 3/3/05 YIK
3355 lcString = lcString + EDI_COMMENT_TAG + lcDelimiter + .ConvertRecordToText(tcWorkComment, llDelimited, lcDelimiter)
3356 Endscan
3357 Endif
3358
3359 *********************************output Address string "ADR"
3360 *No address for now per Paul
3361
3362 *--- TR 1027403 01-Oct-2007 Partha ---
3363 * ok, we need it now.
3364 SELECT (tcWorkAddress)
3365 SCAN For Alltrim(fKey) == Alltrim(lnCurrentPkey)
3366 lcString = lcString + EDI_ADDRESS_TAG + lcDelimiter + ;
3367 .ConvertRecordToText(tcWorkAddress, llDelimited, lcDelimiter)
3368 ENDSCAN
3369 *=== TR 1027403 01-Oct-2007 Partha ===
3370
3371 *--- TechRec 1058716 11-Jan-2012 jisingh ---
3372 IF llHaveEDIH
3373 SELECT (tcFinalEDIH)
3374 *--- TR 1089471 KISHORE 24-SEP-2015
3375 IF NOT .lFrom850
3376 SCAN FOR fkey= lnCurrentPkey && TR 1100564 30-Nov-16 JPaul. Changed the scan condition from ord_num = lnOrdNum because Ord_num is empty
3377 lcString = lcString + "EDIH" + lcDelimiter + .ConvertRecordToText(tcFinalEDIH, llDelimited, lcDelimiter)
3378 ENDSCAN
3379 ELSE
3380 *=== TR 1089471 KISHORE 24-SEP-2015
3381 SCAN FOR fkey = lnCurrentIPOPkey
3382 lcString = lcString + "EDIH" + lcDelimiter + .ConvertRecordToText(tcFinalEDIH, llDelimited, lcDelimiter)
3383 ENDSCAN
3384 ENDIF && TR 1089471
3385 ENDIF
3386 *=== TechRec 1058716 11-Jan-2012 jisingh ===
3387
3388 Select (tcWorkDetail)
3389 Scan For Alltrim(fkey) == Alltrim(lnCurrentPkey)
3390 lcCurSku = Alltrim(sku)
3391 lcCurUpc = Alltrim(upc)
3392 lcCurEan = Alltrim(ean) &&TR 1073064 30-Aug-13 Venuk.
3393 *- 1009713 3/3/05 YIK
3394 lcString = lcString + EDI_DETAIL_TAG + lcDelimiter + .ConvertRecordToText(tcWorkDetail, llDelimited, lcDelimiter)
3395
3396 lnDetPkey = &tcWorkDetail..pkey
3397 *--- TechRec 1058716 11-Jan-2012 jisingh ---
3398 lnDetIPOPkey = EVALUATE(tcWorkDetail + ".ipo_pkey")
3399 *=== TechRec 1058716 11-Jan-2012 jisingh ===
3400
3401 *No detail notes for now per Paul
3402 *SELECT (tcDetailNotes)
3403 *SCAN FOR Alltrim(pKey) == Alltrim(lnDetPkey)
3404 * lcString = lcString + EDI_DNT_TAG + .ConvertRecordToText(tcDetailNotes)
3405 *ENDSCAN
3406 *- 1020956 12/08/06 YIK
3407 *- added ..AND USED(tcFinalSDQ)
3408
3409 If &tcControlCursor..ack_by = 'P' And Not Alltrim(&tcControlCursor..sku_upc)=="N" ;
3410 AND USED(tcFinalSDQ)
3411
3412 If Alltrim(&tcControlCursor..sku_upc) = "S"
3413 lcSDQScanFor = "Alltrim(fkey) == Alltrim(lnCurrentPkey) AND Alltrim(Sku)==Alltrim(lcCurSku)"
3414 ELSE
3415 IF Alltrim(&tcControlCursor..sku_upc) = "U" &&TR 1073064 30-Aug-13 Venuk.
3416 lcSDQScanFor = "Alltrim(fkey) == Alltrim(lnCurrentPkey) AND Alltrim(UPC)==Alltrim(lcCurUPC)"
3417 *--- TR 1073064 30-Aug-13 Venuk.
3418 ELSE
3419 lcSDQScanFor = "Alltrim(fkey) == Alltrim(lnCurrentPkey) AND Alltrim(EAN)==Alltrim(lcCurEan )"
3420 ENDIF
3421 *=== TR 1073064 30-Aug-13 Venuk.
3422 Endif
3423
3424 Select (tcFinalSDQ)
3425 Scan For &lcSDQScanFor
3426 *- 1009713 3/3/05 YIK
3427 lcString = lcString + EDI_SDQ_TAG + lcDelimiter + .ConvertRecordToText(tcFinalSDQ, llDelimited, lcDelimiter)
3428 Endscan
3429 Endif
3430
3431 Select (tcWorkDetail)
3432 *- 1005605 06/15/04 YIK
3433 If .lHaveSLN
3434 *- 1009713 3/3/05 YIK
3435 .BuildSLNString(tcWorkDetail, tcFinalSLN, "Pkey", @lcString, llDelimited, lcDelimiter)
3436 Endif
3437 *=
3438
3439 *--- TechRec 1058716 11-Jan-2012 jisingh ---
3440 IF llHaveEDID
3441 SELECT (tcFinalEDID)
3442 *--- TechRec 1089471 29-Oct-2015 TSV---
3443 IF NOT .lFrom850
3444 SCAN FOR fkey = lnDetPkey
3445 lcString = lcString + "EDID" + lcDelimiter + .ConvertRecordToText(tcFinalEDID, llDelimited, lcDelimiter)
3446 ENDSCAN
3447 ELSE
3448 *=== TechRec 1089471 29-Oct-2015 TSV===
3449 SCAN FOR fkey = lnDetIPOPkey
3450 lcString = lcString + "EDID" + lcDelimiter + .ConvertRecordToText(tcFinalEDID, llDelimited, lcDelimiter)
3451 ENDSCAN
3452 ENDIF &&--- TechRec 1089471 29-Oct-2015 TSV===
3453 ENDIF
3454 *=== TechRec 1058716 11-Jan-2012 jisingh ===
3455
3456 Endscan && tcWorkDetail
3457
3458 If !Empty(lcString)
3459 *--- 36646 02/04/03 YIK - Replace / / and 01/01/1900 with blanks.
3460 .PrepareEmptyDateForGentran (@lcString)
3461 *--- TR 1010900 NH : Writes all info of each header into flat file
3462
3463 *--- TR 1072318 12-Aug-2013 Partha ---
3464*!* If .Tie_setup
3465 *=== TR 1072318 12-Aug-2013 Partha ===
3466 If Fwrite(lnHandle, lcString) = 0
3467 lRetVal = .F.
3468 Exit
3469 Endif
3470 *--- TR 1072318 12-Aug-2013 Partha ---
3471*!* Else
3472*!* If Fwrite(pnHandle, lcString) = 0
3473*!* lRetVal = .F.
3474*!* Exit
3475*!* Endif
3476*!* ENDIF
3477 *=== TR 1072318 12-Aug-2013 Partha ===
3478
3479 *=== TR 1010900 NH
3480 Endif
3481 Select (tcWorkHeader)
3482 *--- TR 1014084 NH
3483 lcPrevVnd_id = EVALUATE(tcWorkVendor + ".vnd_id")
3484 *=== TR 1014084 NH
3485 Endscan && tcWorkHeader
3486 *--- TR 1010900 NH
3487 *--- following copytoflatfileInBlock is need because program will exit scan before
3488 *--- adding last tempfile header record to .dat file
3489
3490 *--- TR 1072318 12-Aug-2013 Partha ---
3491*!* If .Tie_setup And lnHandle > 0
3492
3493 If lnHandle > 0
3494 *=== TR 1072318 12-Aug-2013 Partha ===
3495
3496 *- close previously opened file
3497 Fclose(lnHandle)
3498 If llRetVal
3499 *---TR 1031624 JUL-14-2008 BR
3500 *- Comment out to prevent flat file from being generated if table update fails
3501 *llRetVal =.CopyToFlatFileInBlock(lcTempFlatfile, lcOutBoundFlatFile)
3502 *-TR 1087742 FH
3503 *INSERT INTO __tcFlatFileList(TempFlatfile,OutBoundFlatFile) VALUES (lcTempFlatfile, lcOutBoundFlatFile)
3504 .InsertIntoFlatFileCursor("__tcFlatFileList",lcTempFlatfile,lcOutBoundFlatFile)
3505 *===TR 1031624 JUL-14-2008 BR
3506 Endif
3507 Endif
3508 *=== TR 1010900 NH
3509 Endwith
3510
3511 Select (lnSelect)
3512 Return llRetVal
3513 Endfunc
3514
3515 *============================================================
3516
3517 *-- Procedure CreateFlatfileBak
3518 *-- Lparameter tcProcess, tcWorkVendor, tcWorkHeader, ;
3519 *-- tcWorkDetail, tcWorkComment, tcWorkAddress, tcHeaderNotes, tcDetailNotes, tcFinalSDQ, ;
3520 *-- pnHandle, tcControlCursor
3521
3522 *-- Local llRetVal, lnOldSelect, lnCurrentPkey, llHaveComment, llHaveAddress, lcPreviousCustomer,;
3523 *-- lcPreviousDivision, lcMac, lcString, lcSetCentury, lcPreviousPONum, lcPreviousOrdNum, ;
3524 *-- lc5KeyValue, lnTotal_qty, lnOpenPickQty, lnInvoiceQty, lnCancelQty, ;
3525 *-- lcSDQScanFor, lcCurSku, lcCurUpc
3526 *-- This.oLog.LogEntry("Creating Flat File")
3527 *-- llRetVal= .T.
3528 *-- lcPreviousCustomer= ""
3529 *-- lcPreviousDivision=""
3530 *-- lcPreviousPONum = ""
3531 *-- lcPreviousOrdNum=""
3532 *-- Local lcS_PreviousCustomer,lcS_PreviousShipper,lcS_PreviousShip_date,;
3533 *-- lcS_PreviousShip_to,lcS_PreviousBill_num,lcS_PreviousLocation, lcO_PreviousBill_num,;
3534 *-- lcO_PreviousOrd_num
3535 *-- lcS_PreviousCustomer= ""
3536 *-- lcS_PreviousDivision= ""
3537 *-- lcO_PreviousOrd_num= ""
3538
3539 *-- *-llHaveComment= (recc(tcWorkComment)>0)
3540 *-- llHaveAddress= (recc(tcWorkAddress)>0)
3541 *-- lnOldSelect= Select()
3542
3543 *-- * need order by for order level break
3544 *-- Select * From (tcWorkHeader) ;
3545 *-- GROUP BY customer, division, po_num, ord_num ;
3546 *-- Order By customer, division, po_num, ord_num ;
3547 *-- Into Cursor _tmpHdrCrs
3548 *-- This.MakeCursorWritable("_tmpHdrCrs", "tmpCurs")
3549 *-- Select tmpCurs
3550
3551 *-- With This
3552 *-- Select tmpCurs
3553 *-- Scan
3554 *-- * reset lcString
3555 *-- lcString= ""
3556
3557 *-- **********Create vendor record***************************************************
3558 *-- **********On level break of customer,division************************************
3559 *-- * For GenTran find System template need VND- record
3560 *-- * TradingPartnerID, EDI transaction set (810, 855..), Version (003060,004010...)
3561 *-- If !(customer == lcPreviousCustomer and division == lcPreviousDivision and ord_num == lcPreviousOrdNum)
3562 *-- lcPreviousCustomer= customer
3563 *-- lcPreviousDivision= division
3564 *-- This.vl_oPRcr(customer, "", "__Ecntrc", division)
3565 *-- If Used('__Ecntrc')
3566 *-- Select (tcWorkVendor)
3567 *-- Appen blank
3568 *-- Replace vnd_id with __Ecntrc.vnd_id,;
3569 *-- vnd_tran with tcProcess,; && "oPR" (OB 855 )
3570 *-- vnd_vers with __Ecntrc.vnd_vers In (tcWorkVendor)
3571 *-- Else
3572 *-- llRetVal= .F.
3573 *-- Exit && scan
3574 *-- Endif
3575 *-- *- 36646 1/27/03 YIK
3576 *-- ENDIF
3577 *-- *=
3578 *-- * output Vendor string ("VND",TradingPartnerID,Transaction,Version)
3579 *-- lcString = EDI_VENDOR_TAG + .ConvertRecordToText(tcWorkVendor, llDelimited, lcDelimiter)
3580 *-- ***********************************************End of level break (cust,div)
3581
3582 *-- *********************************output header-level string "ORD"
3583 *-- * ready to convert to flatfile
3584 *-- Select tmpCurs
3585 *-- lcString = lcString + EDI_HEADER_TAG + .ConvertRecordToText("tmpCurs")
3586
3587 *-- * use header.Pkey as the indication for the following tables
3588 *-- * (DTL,CMT,ADR) as child's records of.
3589 *-- lnCurrentPkey= tmpCurs.pkey
3590 *-- SELECT (tcHeaderNotes)
3591 *-- SCAN FOR pkey = lnCurrentPkey
3592 *-- lcString = lcString + EDI_HNT_TAG + .ConvertRecordToText(tcHeaderNotes)
3593 *-- ENDSCAN
3594 *-- *********************************output comment string "CMT" anthing # ST/SF
3595 *-- If llHaveComment
3596 *-- Select (tcWorkComment)
3597 *-- Scan For fkey = lnCurrentPkey
3598 *-- lcString = lcString + EDI_COMMENT_TAG + .ConvertRecordToText(tcWorkComment)
3599 *-- Endscan
3600 *-- Endif
3601 *-- *********************************output Address string "ADR"
3602 *-- If llHaveAddress
3603 *-- Select (tcWorkAddress)
3604 *-- Scan For fkey = lnCurrentPkey
3605 *-- lcString = lcString + EDI_ADDRESS_TAG + .ConvertRecordToText(tcWorkAddress)
3606 *-- Endscan
3607 *-- Endif
3608 *-- *- 36646 1/27/03 YIK
3609 *-- *-Endif
3610 *-- *=
3611 *-- *********************************output detail string "DTL"
3612
3613 *-- *-lnCurrentPkey= tmpCurs.pkey
3614 *-- Select (tcWorkDetail)
3615 *-- lc5KeyValue= SPACE(20)
3616 *-- Scan For fkey = lnCurrentPkey
3617 *-- IF !lc5KeyValue == style+Color_code+Lbl_code+dimension+size_desc
3618 *-- lc5KeyValue = style+Color_code+Lbl_code+dimension+size_desc
3619 *-- lcCurSku = Alltrim(sku)
3620 *-- lcCurUpc = Alltrim(upc)
3621 *-- ENDIF
3622 *-- lnTotal_qty = 0
3623 *-- lnOpenPickQty = 0
3624 *-- lnInvoiceQty = 0
3625 *-- lnCancelQty = 0
3626 *-- *- combine multiple records (open, picked, invoiced) with the same 5 key into 1 flat file record
3627 *-- SCAN WHILE fkey = lnCurrentPkey AND style+Color_code+Lbl_code+dimension+size_desc = lc5KeyValue
3628 *-- lnTotal_qty = lnTotal_qty + EVAL(total_qty)
3629 *-- DO CASE
3630 *-- CASE line_status = "P"
3631 *-- lnOpenPickQty = lnOpenPickQty + EVAL(total_qty)
3632 *-- CASE line_status = "I"
3633 *-- lnInvoiceQty = lnInvoiceQty + EVAL(total_qty)
3634 *-- CASE line_status = "C"
3635 *-- lnCancelQty = lnCancelQty + EVAL(total_qty)
3636 *-- OTHERWISE
3637 *-- ENDCASE
3638 *-- ENDSCAN
3639 *-- *- update qty in the last record for the 5key
3640 *-- IF NOT BOF()
3641 *-- SKIP (-1)
3642 *-- ENDIF
3643 *-- REPLACE total_qty with TRANS(lnTotal_qty, '9999999999'), ;
3644 *-- OpenPickQty WITH TRANS(lnOpenPickQty, '9999999999'), ;
3645 *-- InvoiceQty WITH TRANS(lnInvoiceQty, '9999999999'), ;
3646 *-- CancelQty WITH TRANS(lnCancelQty, '9999999999')
3647
3648 *-- lnDetPkey = pkey
3649 *-- lcFKey = fkey
3650 *-- lcString = lcString + EDI_DETAIL_TAG + .ConvertRecordToText(tcWorkDetail)
3651
3652 *-- SELECT (tcDetailNotes)
3653 *-- SCAN FOR pkey = lnDetPkey
3654 *-- lcString = lcString + EDI_DNT_TAG + .ConvertRecordToText(tcDetailNotes)
3655 *-- ENDSCAN
3656
3657 *-- *--- 36646 01/27/03 YIK
3658 *-- *IF tmpCurs.ack_by = 'P'
3659 *-- *HH according to paul, if sku_upc is "N", don't export sdq data for now.
3660 *-- IF tceoPRcr.ack_by = 'P' and Not Alltrim(tceoPRcr.sku_upc)=="N"
3661 *-- If Alltrim(tceoPRcr.sku_upc) = "S"
3662 *-- lcSDQScanFor = "Alltrim(fkey) = Alltrim(lnCurrentPkey) AND Alltrim(Sku)==Alltrim(lcCurSku)"
3663 *-- Else
3664 *-- lcSDQScanFor = "Alltrim(fkey) = Alltrim(lnCurrentPkey) AND Alltrim(UPC)==Alltrim(lcCurUPC)"
3665 *-- Endif
3666 *-- Select (tcFinalSDQ)
3667 *-- SCAN For &lcSDQScanFor
3668 *-- lcString = lcString + EDI_SDQ_TAG + .ConvertRecordToText(tcFinalSDQ)
3669 *-- EndScan
3670 *-- ENDIF
3671 *-- *===
3672 *-- Endscan
3673 *-- If !Empty(lcString)
3674 *-- If FWRITE(pnHandle, lcString) = 0
3675 *-- lRetVal = .F.
3676 *-- Endif
3677 *-- Endif
3678 *-- ENDSCAN
3679 *-- * cleanup
3680 *-- .TableClose("TmpCurs")
3681 *-- .TableClose("_tmpHdrCrs")
3682 *-- Endwith
3683
3684 *-- Select(lnOldSelect)
3685 *-- Return llRetVal
3686 *-- Endproc
3687
3688 ************************************************************************************
3689 * DeleteInterfaceRecords
3690 ***********************************************************************************
3691 Procedure DeleteInterfaceRecords
3692 * TR 1074595 KISHORE 22-JAN-2014 Added , tcInterfaceStores
3693 Lparameters tcInterfaceHeader, tcInterfaceDetail, tcInterfaceComment, tcInterfaceAddress, tcInterfaceStores, tcInterfaceWhse
3694 *- TR 1098611 FH - added tcInterfaceWhse
3695 Local llRetVal, lcSQLString, lnOldSelect
3696 llRetVal= .T.
3697 lnOldSelect= Select()
3698 This.oLog.LogEntry("Deleting Interface records")
3699 * for each interface header record that successfully move to flatfile
3700 * (auto_proc="Y" and NO Errs_flg_h) have to delete related (pkey/fkey)
3701 * detail, comment, address records
3702 Select (tcInterfaceHeader)
3703 Scan For auto_proc= "Y"
3704 lnCurrentPkey= pkey
3705 lnCurrentOrd_num = ord_num && TR 1098611 FH
3706 Select (tcInterfaceDetail)
3707 Scan For fkey= lnCurrentPkey
3708 Delete In VzzeoPRid_Proc
3709 Endscan
3710 Delete For fkey= lnCurrentPkey In (tcInterfaceComment)
3711 Delete For fkey= lnCurrentPkey In (tcInterfaceAddress)
3712 * TR 1074595 KISHORE 22-JAN-2014
3713 DELETE FOR fkey = lnCurrentPkey IN (tcInterfaceStores)
3714
3715 *-TR 1098611 FH
3716 DELETE FOR ord_num = lnCurrentOrd_num IN (tcInterfaceWhse)
3717 DELETE FOR fkey = lnCurrentPkey IN (tcInterfaceWhse)
3718 *-TR 1098611 FH
3719
3720 Delete In (tcInterfaceHeader)
3721 Endscan
3722
3723 Select(lnOldSelect)
3724 Return llRetVal
3725 Endproc
3726
3727 ************************************************************************************
3728 * move interface records to history with condition of auto_proc="Y"
3729 ************************************************************************************
3730 Procedure CreateAutoProcessInterface
3731 * TR 1074595 KISHORE 22-JAN-2014 Added , pcHistSDQ, pcInterSDQ
3732 Lparameters pcInterHeader, pcInterDetail, pcInterComment, pcInterAddress, ;
3733 pcHistHeader, pcHistDetail, pcHistComment, pcHistAddress, pcInterSDQ, pcHistSDQ
3734 Local llOKtoContinue, lnOldSelect, llInterHeaderPkey, ltDatetime, ;
3735 lnCurrentOrd_num, lnCurrentInv_num
3736
3737 This.oLog.LogEntry("Moving to History tables")
3738 lnCurrentOrd_num= 0
3739 ltDatetime= Datetime()
3740 llOKtoContinue = .T.
3741 lnOldSelect = Select()
3742
3743 * Only for auto process and no last error
3744 * This is the only place that will still potentially have error in interface tables
3745 Select (pcInterHeader)
3746 Scan For auto_proc="Y"
3747 * track number of invoices make it to flatfile
3748 .nFlatfileRecords= .nFlatfileRecords+ 1
3749
3750 * create history header from interface header
3751 llOKtoContinue= This.CreateRecordWithPkey(pcInterHeader, pcHistHeader) && no real TAG in bcsysnum
3752 If !llOKtoContinue
3753 Exit
3754 Endif
3755 * create history detail/comment/address base on current ord_num,inv_num with auto_proc="Y"
3756 lnCurrentPkey= &pcInterHeader..pkey
3757 * create history detail from interface detail for each header pkey to detail fkey
3758 Select (pcInterDetail)
3759 Scan For fkey= lnCurrentPkey
3760 llOKtoContinue= This.CreateRecordWithPkey(pcInterDetail, pcHistDetail)
3761 If !llOKtoContinue
3762 Exit
3763 Endif
3764 Endscan
3765 * create history Comment from interface detail for each ord_num,invoice#
3766 Select (pcInterComment)
3767 Scan For fkey= lnCurrentPkey
3768 llOKtoContinue= This.CreateRecordWithPkey(pcInterComment, pcHistComment)
3769 If !llOKtoContinue
3770 Exit
3771 Endif
3772 Endscan
3773 * create history Address from interface detail for each div,invoice#
3774 Select (pcInterAddress)
3775 Scan For fkey= lnCurrentPkey
3776 llOKtoContinue= This.CreateRecordWithPkey(pcInterAddress, pcHistAddress)
3777 If !llOKtoContinue
3778 Exit
3779 Endif
3780 Endscan
3781
3782 *--- TR 1074595 KISHORE 22-JAN-2014
3783 SELECT (pcInterSDQ)
3784 SCAN FOR fkey = lnCurrentPkey
3785 llOKtoContinue= This.CreateRecordWithPkey(pcInterSDQ, pcHistSDQ)
3786 IF !llOKtoContinue
3787 EXIT
3788 ENDIF
3789 ENDSCAN
3790 *=== TR 1074595 KISHORE 22-JAN-2014
3791
3792 Endscan
3793
3794 Select(lnOldSelect)
3795 Return llOKtoContinue
3796 Endproc
3797
3798 ************************************************************************************
3799 * YIK 09/18/02 - Get terms code and/or ITD segment values from zzetermx
3800 * based on trading partner and BC terms code
3801 ************************************************************************************
3802 Procedure GetEDITerms
3803 Lparameters tcWorkHeader, tnITD_DueDays, tnITD_DiscPct, tnITD_DiscDays, ;
3804 tcFTerms_code, tcITD_TmType, tcPartner
3805
3806 Local lcCode, lcTerms, llRetVal, lnOldSelect
3807
3808 llRetVal = .T.
3809 lnOldSelect = Select()
3810
3811 lcCode = tcPartner
3812 lcTerms = &tcWorkHeader..terms
3813 If This.vl_editermX(lcCode, , "_TermX", lcTerms)
3814 tnITD_DueDays = _TermX.ITD_DueDays
3815 tnITD_DiscPct = _TermX.ITD_DiscPct
3816 tnITD_DiscDays = _TermX.ITD_DiscDays
3817 tcFTerms_code = _TermX.Fact_Terms
3818 tcITD_TmType = _TermX.ITD_TmType
3819 Endif
3820 If Used('_TermX')
3821 Use In _TermX
3822 Endif
3823 Select(lnOldSelect)
3824 Return llRetVal
3825 Endproc
3826
3827 ************************************************************************************
3828 * YIK 09/18/02 - Get Factor terms code and/or ITD segment values from zzetermx
3829 ************************************************************************************
3830 Procedure vl_editermX
3831 Parameters p_cKeyField1, p_cGetField, p_cCursor, p_cKeyField2
3832 Local l_cSQLString, l_vRetVal
3833 *- Select record with matching factor (customer) or empty (for generic terms setup)
3834 l_cSQLString = "SELECT TOP 1 * FROM zzetermx WHERE (Factor = ?p_cKeyField1 OR Factor = ' ')" + ;
3835 " AND Terms = ?p_cKeyField2 ORDER BY Factor DESC"
3836
3837 l_vRetVal = v_SqlPrep(l_cSQLString, p_cCursor, p_cGetField)
3838 Return l_vRetVal
3839 Endproc
3840
3841 *********************************************************
3842 * Procedure FlagProcessOrders
3843 *********************************************************
3844 Procedure FlagProcessOrders
3845 Parameters pcTranHeader, pcOrderHeader
3846 Local llRetVal , lnOldSelect, ltDatetime, lcUser_id
3847 This.oLog.LogEntry("Marking processed orders")
3848 llRetVal= .T.
3849 ltDatetime= Datetime()
3850 lcUser_id= goEnv.envLogin.cUserName
3851 lnOldSelect = Select()
3852
3853 *--- TR 1030443 22-Apr-2008 Goutam
3854 *If Used(pcTranHeader) And Used(pcOrderHeader) &&pcTranHeader is tceoprth, pcorderheader is vzzoordrh_proc
3855 If Used(pcTranHeader) And Used(pcOrderHeader) AND NOT this.lFrom945 &&pcTranHeader is tceoprth, pcorderheader is vzzoordrh_proc
3856 *=== TR 1030443 22-Apr-2008 Goutam
3857
3858 llRetVal= .SetRelation(pcTranHeader, "Ord", pcOrderHeader, "Str(ord_num)") &&set relation to str(ord_num) into pcTranHeader
3859 If llRetVal && pcOrderHeader is the current work area
3860 Scan For !(&pcTranHeader..Errs_Flg_H= "Y") && None error trans header only
3861 Replace Proc_855 With "P",;
3862 user_id With lcUser_id, ;
3863 last_mod With ltDatetime In (pcOrderHeader)
3864 Endscan
3865 Set Relation To
3866 Endif
3867 Endif
3868
3869 Select(lnOldSelect)
3870 Return llRetVal
3871 Endproc
3872
3873 Procedure vl_oPRcr
3874 Parameters p_cKeyField1, p_cGetField, p_cCursor, p_cKeyField2
3875 Local l_cSQLString, l_vRetVal
3876
3877 If Empty(p_cKeyField1)
3878 l_vRetVal = Iif(Empty(p_cGetField), .T., "")
3879 Return l_vRetVal
3880 Endif
3881
3882 l_cSQLString = "SELECT * FROM zzeoprcr WHERE customer = ?p_cKeyField1" + ;
3883 " AND Division = ?p_cKeyField2"
3884
3885 l_vRetVal = v_SqlPrep(l_cSQLString, p_cCursor, p_cGetField)
3886 Return l_vRetVal
3887
3888 *****************************************************************
3889 Procedure ScatterGatherNotes
3890 Parameters p_cFrom, p_cTo, p_nMemoWidth
3891
3892 Local lcNotes, lcHoldMemo, lnNumLines, lcNoteStr
3893 Select (p_cFrom)
3894 Scan
3895 Scatter Memvar Memo
3896 lcNotes = Alltrim(notes)
3897 If !Empty(lcNotes)
3898 lcHoldMemo = Set("MEMOWIDTH")
3899 Set Memo To p_nMemoWidth
3900 Store Memlines(lcNotes) To lnNumLines && Number of lines in memo field
3901 Store 0 To _Mline
3902 For Count = 1 To lnNumLines
3903 lcNoteStr = Mline(lcNotes, 1, _Mline)
3904 Select (p_cTo)
3905 Append Blank
3906 Gather Memvar Memo
3907 Replace notes With lcNoteStr
3908 Endfor
3909 Set Memo To (lcHoldMemo)
3910 Endif
3911 Endscan
3912 Endproc
3913
3914 *****************************************************************************************************************
3915 Procedure CreateWorkTables
3916 Parameters tcOrderHeader, tcOrderDetail, tcShpHeader, tcShpDetail, tcWorkHeader, tcWorkDetail
3917
3918 *- 1005605 06/14/04 YIK
3919 *- Use GetSortedFieldList() from bclibfwk
3920 *!* SELECT customer as _cust, ord_num as _order, * FROM (tcOrderHeader) ;
3921 *!* UNION ALL ;
3922 *!* SELECT customer as _cust, ord_num as _order, * FROM (tcShpHeader) ;
3923 *!* ORDER BY 1,2 ; &&order by needs to be a number, so have to select dummy fields.
3924 *!* INTO Cursor &tcWorkHeader
3925
3926 *!* SELECT * FROM (tcOrderDetail) ;
3927 *!* UNION ALL ;
3928 *!* SELECT * FROM (tcShpDetail) ;
3929 *!* INTO Cursor &tcWorkDetail
3930
3931 * --- TR 1034147 NSD 6/18/08
3932 * For the work header, it needs to be sorted by inv_num desc in addition to customer & order
3933 * When from 945, add the sort in, otherwise do not. This is done so the invoice header is the first
3934 * record evaluated when combining all the headers. We need the information on the invoice that may not
3935 * be the same on the open order.
3936
3937 *--- TechRec 1065807 17-Jan-2013 vkrishnamurthy ---
3938 LOCAL lcHdrFldList
3939 lcHdrFldList = GetCommonFieldListWithAlias("", "zzoordrh", "zzeoprth")
3940 *=== TechRec 1065807 17-Jan-2013 vkrishnamurthy ===
3941
3942 lcOrdHdrList = GetSortedFieldList("zzoordrh")
3943 lcOrdDtlList = GetSortedFieldList("zzoordrd")
3944
3945 *--- TechRec 1065807 17-Jan-2013 vkrishnamurthy ---
3946*!* lcSQL_H = "SELECT customer as _cust, ord_num as _order,inv_num as _invoice, " + ;
3947*!* lcOrdHdrList + ;
3948*!* " FROM " + tcOrderHeader + ;
3949*!* " UNION ALL " + ;
3950*!* "SELECT customer as _cust, ord_num as _order,inv_num as _invoice , " + ;
3951*!* lcOrdHdrList + ;
3952*!* " FROM " + tcShpHeader + ;
3953*!* " ORDER BY 1,2" + IIF(THIS.lFrom945,",3 DESC ","") + ; &&order by needs to be a number, so have to select dummy fields.
3954*!* " INTO Cursor " + tcWorkHeader
3955
3956 * TR 1074595 KISHORE 21-JAN-2014 added UNION to Vzzeoprih_proc
3957 lcSQL_H = "SELECT customer as _cust, ord_num as _order,inv_num as _invoice, " + ;
3958 lcHdrFldList + ;
3959 " FROM " + tcOrderHeader + ;
3960 " UNION ALL " + ;
3961 "SELECT customer as _cust, ord_num as _order,inv_num as _invoice , " + ;
3962 lcHdrFldList + ;
3963 " FROM " + tcShpHeader + ;
3964 " UNION ALL " + ;
3965 "SELECT customer as _cust, ord_num as _order,inv_num as _invoice , " + ;
3966 lcHdrFldList + ;
3967 " FROM Vzzeoprih_proc " + ;
3968 " WHERE 1=1 " + STRTRAN(This.cFilterCriteria, 'H.', '') + ;
3969 " ORDER BY 1,2" + IIF(THIS.lFrom945,",3 DESC ","") + ; &&order by needs to be a number, so have to select dummy fields.
3970 " INTO Cursor " + tcWorkHeader
3971 *=== TechRec 1065807 17-Jan-2013 vkrishnamurthy ===
3972
3973 lcSQL_D = "SELECT " + lcOrdDtlList + ;
3974 " FROM " + tcOrderDetail + ;
3975 " UNION ALL " + ;
3976 "SELECT " + lcOrdDtlList + ;
3977 " FROM " + tcShpDetail + ;
3978 " INTO Cursor " + tcWorkDetail
3979
3980 &lcSQL_H
3981 &lcSQL_D
3982 *= 1005605 06/14/04 YIK
3983 Endproc
3984
3985 *****************************************************************************************************************
3986 *--- 36646 1/20/03 YIK
3987 Procedure CheckSDQ
3988 *------------------
3989 Parameters pcTransHeader, pcTransDetail, pcTransControl
3990 Local llRetVal, lcTempCursor, lcErr_Msg, lcPo_num
3991 llRetVal = .T.
3992 lcTempCursor = GetUniqueFileName()
3993 lcErr_Msg = "One or More orders of this PO have blank UPC, SKU or EDI Store."
3994 With This
3995 *--- TR 1025228 9-Jul-2007 Goutam
3996 *Select h.po_num From (pcTransHeader) h, (pcTransControl) c;
3997 Where Empty(h.Edi_Store) ;
3998 and Alltrim(h.division)==Alltrim(c.division) ;
3999 and Alltrim(h.customer)==Alltrim(c.customer) ;
4000 and Alltrim(c.ack_by)=="P" ;
4001 Group By h.po_num ;
4002 Union All ;
4003 Select h.po_num From (pcTransHeader) h, (pcTransDetail) d, (pcTransControl) c ;
4004 Where Alltrim(d.Errs_Flg_D)=="Y" And h.pkey=d.fkey ;
4005 and Alltrim(h.division)==Alltrim(c.division) ;
4006 and Alltrim(h.customer)==Alltrim(c.customer) ;
4007 and Alltrim(c.ack_by)=="P" ;
4008 Group By h.po_num ;
4009 into Cursor (lcTempCursor) &&If a certain detail line fails, then fail the whole PO.
4010
4011 *=== TR 1031304 03/10/2008 TT - Added !EMPTY(Store) per Yuri ----------------------
4012*!* Select h.po_num From (pcTransHeader) h ;
4013*!* Where Empty(h.Edi_Store) ;
4014*!* and Alltrim(h.ack_by)=="P" ;
4015*!* Group By h.po_num ;
4016*!* Union All ;
4017*!* Select h.po_num From (pcTransHeader) h, (pcTransDetail) d ;
4018*!* Where Alltrim(d.Errs_Flg_D)=="Y" And h.pkey=d.fkey ;
4019*!* and Alltrim(h.ack_by)=="P" ;
4020*!* Group By h.po_num ;
4021*!* into Cursor (lcTempCursor) &&If a certain detail line fails, then fail the whole PO.
4022
4023 SELECT h.po_num FROM (pcTransHeader) h ;
4024 WHERE EMPTY(h.Edi_Store) ;
4025 AND !EMPTY(h.store) ;
4026 AND ALLTRIM(h.ack_by)=="P" ;
4027 GROUP BY h.po_num ;
4028 UNION ALL ;
4029 SELECT h.po_num FROM (pcTransHeader) h, (pcTransDetail) d ;
4030 WHERE ALLTRIM(d.Errs_Flg_D)=="Y" And h.pkey=d.fkey ;
4031 AND ALLTRIM(h.ack_by)=="P" ;
4032 GROUP BY h.po_num ;
4033 INTO CURSOR (lcTempCursor) &&If a certain detail line fails, then fail the whole PO.
4034
4035 *=== TR 1031304 03/10/2008 TT - Added !EMPTY(Store) per Yuri ----------------------
4036 *=== TR 1025228 9-Jul-2007 Goutam
4037
4038 Select (lcTempCursor)
4039 Scan
4040 lcPo_num = Alltrim(&lcTempCursor..po_num)
4041 Replace Errs_Msg_H With Errs_Msg_H + lcErr_Msg, ;
4042 Errs_Flg_H With "Y" In (pcTransHeader) ;
4043 For Alltrim(po_num)==lcPo_num And Not Alltrim(Errs_Flg_H)=="Y"
4044 Endscan
4045
4046 ***Blank out deparment and terms for those POs that have different
4047 ***department and terms
4048
4049 *--- TR 1025228 9-Jul-2007 Goutam
4050 *Select h.po_num, h.department, h.terms From (pcTransHeader) h, (pcTransControl) c;
4051 Where ;
4052 Alltrim(h.division)==Alltrim(c.division) ;
4053 and Alltrim(h.customer)==Alltrim(c.customer) ;
4054 and Alltrim(c.ack_by)=="P" ;
4055 group By h.po_num, h.department, h.terms ;
4056 Into Cursor (lcTempCursor)
4057
4058 Select h.po_num, h.department, h.terms From (pcTransHeader) h;
4059 Where ;
4060 Alltrim(h.ack_by)=="P" ;
4061 group By h.po_num, h.department, h.terms ;
4062 Into Cursor (lcTempCursor)
4063 *=== TR 1025228 9-Jul-2007 Goutam
4064
4065 Select po_num From (lcTempCursor) Group By po_num ;
4066 Having Count(*)>1 Into Cursor (lcTempCursor)
4067
4068 Select (lcTempCursor)
4069 Scan
4070 lcPo_num = Alltrim(&lcTempCursor..po_num)
4071 Replace department With '', terms With '' ;
4072 In (pcTransHeader) For Alltrim(po_num)==lcPo_num
4073 Endscan
4074 .TableClose(lcTempCursor)
4075 Endwith
4076
4077 Return llRetVal
4078 Endproc
4079
4080
4081 Procedure PrepareCursorsForSDQ
4082 *------------------------------
4083 Parameters tcHeader, tcDetail, tcTempSDQ, tcControlCursor
4084 Local lcTempCursor
4085 lnWorkArea = Select()
4086 lcTempCursor = GetUniqueFileName()
4087 With This
4088 *- Select only POs with multiple stores, otherwise there's no need in SDQs if there's only 1 store in a PO.
4089 *SELECT h.po_num, h.pkey, count(*) AS cnt ;
4090 * FROM (tcHeader) h ;
4091 * WHERE h.ack_by = 'P' AND h.errs_flg_h <> 'Y';
4092 * GROUP BY h.po_num ;
4093 * HAVING Cnt > 1 ;
4094 * INTO CURSOR (lcTempCursor)
4095
4096 *- styles to work with
4097 *- 1005605 07/21/04 YIK
4098 *- Added h.edi_center to group by edi_center
4099 *- 1006635 08/11/04 YIK - Per May Co., don't group by edi center.
4100 *- send any one as ship-to
4101
4102 *--- TR 1025228 9-Jun-2007 Goutam
4103 *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, ;
4104 d.total_qty, d.line_status, d.division, d.Style, d.color_code, d.lbl_code, d.Dimension, ;
4105 d.size_desc ;
4106 FROM (tcHeader) h ;
4107 JOIN (tcControlCursor) q ;
4108 ON Alltrim(q.customer)==Alltrim(h.customer) And ;
4109 Alltrim(q.division)==Alltrim(h.division) And ;
4110 Alltrim(q.ack_by)=="P" ;
4111 JOIN (tcDetail) d ;
4112 ON h.pkey = d.fkey ;
4113 WHERE h.Errs_Flg_H <> 'Y' ;
4114 ORDER By h.po_num, d.upc, d.sku, h.Edi_Store ;
4115 INTO Cursor (tcTempSDQ)
4116
4117 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, ;
4118 d.total_qty, d.line_status, d.division, d.Style, d.color_code, d.lbl_code, d.Dimension, ;
4119 d.size_desc ;
4120 FROM (tcHeader) h ;
4121 JOIN (tcDetail) d ;
4122 ON h.pkey = d.fkey ;
4123 WHERE h.Errs_Flg_H <> 'Y' ;
4124 AND Alltrim(h.ack_by)=="P" ;
4125 ORDER By h.po_num, d.upc, d.sku, h.Edi_Store ;
4126 INTO Cursor (tcTempSDQ)
4127 *=== TR 1025228 9-Jun-2007 Goutam
4128
4129 *=
4130 .TableClose(lcTempCursor)
4131 Endwith
4132 Select (lnWorkArea)
4133
4134 Endproc
4135
4136 Procedure ConvertToSDQ
4137 *-------------------------
4138 Parameters tcInterfaceHeader, tcInterfaceDetail, ;
4139 tcInterfaceComment, tcInterfaceAddress, pcFinalSDQ, pcSourceSDQ
4140
4141 Local loSDQ, lnWorkArea, llRetVal,lnTotal_qty, lnOpenPickQty, lnInvoiceQty, lnCancelQty
4142
4143 *--- TR 1074595 6-Feb-2014 Goutam
4144 LOCAL lnPkey
4145 *=== TR 1074595 6-Feb-2014 Goutam
4146
4147 llRetVal = .T.
4148 lnWorkArea = Select()
4149 Select * From (pcSourceSDQ) Where .F. Into Cursor Q_VertSDQ
4150
4151 With This
4152 .MakeCursorWritable("Q_VertSDQ", "VertSDQ")
4153 .TableClose("Q_VertSDQ")
4154
4155 *--- TR 1074595 6-Feb-2014 Goutam
4156 SELECT (tcInterfaceHeader)
4157 SCAN
4158 lnPkey = Pkey
4159 *=== TR 1074595 6-Feb-2014 Goutam
4160
4161 Select (pcSourceSDQ)
4162
4163 *--- TR 1074595 6-Feb-2014 Goutam
4164 *Scan
4165 SCAN FOR fkey = lnPkey
4166 *=== TR 1074595 6-Feb-2014 Goutam
4167
4168 Scatter Memvar
4169 Insert Into VertSDQ From Memvar
4170 Endscan
4171
4172 *--- TR 1074595 6-Feb-2014 Goutam
4173 ENDSCAN
4174 SELECT VertSDQ
4175 INDEX on po_num+upc+sku+STR(fkey) TAG po_num
4176 *=== TR 1074595 6-Feb-2014 Goutam
4177
4178 llRetVal = .CreateSDQ("VertSDQ", pcFinalSDQ)
4179
4180 *IF RECC(pcSourceSDQ) > 0
4181 **We can not delete data from interface views here because we
4182 **still need the data when we call UnLoadToFlatFile routine.
4183 *!* IF false
4184 *!* SELECT (pcSourceSDQ)
4185 *!* lnDetTotal_qty = 0
4186 *!* lnTotal_qty = 0
4187 *!* lnOpenPickQty = 0
4188 *!* lnInvoiceQty = 0
4189 *!* lnCancelQty = 0
4190 *!* lnHoldPKey = pkey
4191 *!* lnHoldFKey = dpkey
4192 *!* SCATTER NAME loSDQ
4193
4194 *!* SCAN
4195 *!* IF !(po_num == loSDQ.po_num AND upc == loSDQ.upc AND sku == loSDQ.sku)
4196 *!* SELECT (tcInterfaceDetail)
4197 *!* LOCATE FOR pkey = lnHoldFKey
4198 *!* REPLACE total_qty WITH lnDetTotal_qty
4199
4200 *!* SELECT (pcSourceSDQ)
4201 *!* lnHoldFKey = dpkey
4202 *!* IF ! (po_num == loSDQ.po_num)
4203 *!* lnHoldPKey = pkey
4204 *!* ENDIF
4205 *!* lnDetTotal_qty = 0
4206 *!* ENDIF
4207
4208 *!* IF ! (po_num == loSDQ.po_num AND Edi_Store == loSDQ.Edi_Store AND upc == loSDQ.upc AND sku == loSDQ.sku)
4209 *!* SELECT VertSDQ
4210 *!* APPEND BLANK
4211 *!* GATHER NAME loSDQ
4212 *!* SELECT (pcSourceSDQ)
4213 *!* SCATTER NAME loSDQ
4214
4215 *!* lnTotal_qty = 0
4216 *!* lnOpenPickQty = 0
4217 *!* lnInvoiceQty = 0
4218 *!* lnCancelQty = 0
4219 *!* ENDIF
4220 *!* *- combine multiple records (open, picked, invoiced) with the same 5 key into 1 flat file record
4221 *!* lnTotal_qty = lnTotal_qty + loSDQ.total_qty
4222 *!* lnDetTotal_qty = lnDetTotal_qty + loSDQ.total_qty
4223 *!* DO CASE
4224 *!* CASE line_status = "P"
4225 *!* lnOpenPickQty = lnOpenPickQty + loSDQ.total_qty
4226 *!* CASE line_status = "I"
4227 *!* lnInvoiceQty = lnInvoiceQty + loSDQ.total_qty
4228 *!* CASE line_status = "C"
4229 *!* lnCancelQty = lnCancelQty + loSDQ.total_qty
4230 *!* OTHERWISE
4231 *!* ENDCASE
4232
4233 *!* IF hpkey <> lnHoldPKey
4234 *!* lchpkey = hpkey
4235 *!* SELECT (tcInterfaceHeader)
4236 *!* DELETE FOR pkey = lchpkey
4237 *!* SELECT (tcInterfaceComment)
4238 *!* DELETE FOR pkey = lchpkey
4239 *!* SELECT (tcInterfaceAddress)
4240 *!* DELETE FOR pkey = lchpkey
4241 *!* SELECT (pcSourceSDQ)
4242 *!* ENDIF
4243
4244 *!* IF dpkey <> lnHoldFKey
4245 *!* lcFKey = dpkey
4246 *!* SELECT (tcInterfaceDetail)
4247 *!* DELETE FOR fkey = lcFKey
4248 *!* SELECT (pcSourceSDQ)
4249 *!* ENDIF
4250
4251 *!* ENDSCAN
4252 *!* SELECT VertSDQ
4253 *!* APPEND BLANK
4254 *!* GATHER NAME loSDQ
4255 *!* llRetVal = .CreateSDQ("VertSDQ", pcFinalSDQ)
4256 *!* ENDIF
4257 Endwith
4258
4259 Select (lnWorkArea)
4260 Endproc
4261
4262 Procedure CreateSDQ
4263 *--------------------
4264 Parameters tcSDQ, tcFinalSDQ
4265 Local lnWorkArea, loFinalSDQ, lcPo_num, lcEdi_center
4266
4267 lnWorkArea = Select()
4268 With This
4269 .CreateMetaDataWorkTable("tcEmData", "SDQ", tcFinalSDQ)
4270 Select (tcFinalSDQ)
4271 Scatter Name loFinalSDQ Blank
4272 Select (tcSDQ)
4273 Go Top
4274 lnCnt = 1
4275 lcCnt = Padl(Alltr(Trans(lnCnt, "99")), 2, '0')
4276 *- 1005605 07/21/04 YIK
4277 *!* loFinalSDQ.fkey = TRANS(fkey)
4278 *!* loFinalSDQ.pkey = TRANS(hpkey)
4279 loFinalSDQ.fkey = Padl(Trans(fkey), 10)
4280 loFinalSDQ.pkey = Padl(Trans(hpkey), 10)
4281 *-lcdpkey = dpkey
4282 *=
4283 loFinalSDQ.sku = sku
4284 loFinalSDQ.upc = upc
4285 lcPo_num = po_num
4286
4287 *- 1005605 07/21/04 YIK
4288 lcEdi_center = edi_center
4289
4290 *--- TR 1074595 6-Feb-2014 Goutam
4291 *SCAN
4292 DO WHILE !EOF()
4293 *=== TR 1074595 6-Feb-2014 Goutam
4294
4295 *- 1006635 08/11/04 YIK - Per May Co., don't group by edi center.
4296 *- send any one as ship-to
4297 *!* IF !(po_num = lcPo_num AND sku = loFinalSDQ.sku AND upc = loFinalSDQ.upc AND lcEdi_center = edi_center) ;
4298 *!* Or lnCnt = 11&&We handle 10 stores at a time
4299
4300 *--- TR 1074595 6-Feb-2014 Goutam
4301 *If !(po_num = lcPo_num And sku = loFinalSDQ.sku And upc = loFinalSDQ.upc) ;
4302 Or lnCnt = 11 &&We handle 10 stores at a time
4303 If (po_num = lcPo_num And sku = loFinalSDQ.sku And upc = loFinalSDQ.upc)
4304 *=== TR 1074595 6-Feb-2014 Goutam
4305 *=
4306
4307 *--- TR 1074595 6-Feb-2014 Goutam
4308*!* Select (tcFinalSDQ)
4309*!* Append Blank
4310*!* Gather Name loFinalSDQ
4311*!* Select (tcSDQ)
4312
4313*!* *- 1005605 07/21/04 YIK
4314*!* loFinalSDQ.fkey = Padl(Trans(fkey), 10)
4315*!* loFinalSDQ.pkey = Padl(Trans(hpkey), 10)
4316*!* *-lcdpkey = dpkey
4317*!* *=
4318
4319*!* loFinalSDQ.sku = sku
4320*!* loFinalSDQ.upc = upc
4321*!* lcPo_num = po_num
4322*!* lcEdi_center = edi_center
4323 *=== TR 1074595 6-Feb-2014 Goutam
4324
4325 *== 1005605 07/21/04 YIK
4326
4327 *---Reset the store, qty fields. Otherwise they will carry down the values of previous line
4328 For lnCnt=1 To 10
4329 lcStoreFld = "loFinalSDQ.Store_" + Padl(Alltr(Trans(lnCnt, "99")), 2, '0')
4330 lcQtyFld = "loFinalSDQ.Qty_" + Padl(Alltr(Trans(lnCnt, "99")), 2, '0')
4331 &lcStoreFld = ''
4332 &lcQtyFld = ''
4333 Endfor
4334 *===End Reseting values
4335
4336 *--- TR 1074595 6-Feb-2014 Goutam
4337 Select (tcSDQ)
4338 lnCnt = 1
4339 SCAN WHILE (po_num = lcPo_num And sku = loFinalSDQ.sku And upc = loFinalSDQ.upc)
4340 lcCnt = Padl(Alltr(Trans(lnCnt, "99")), 2, '0')
4341 lcStoreFld = "loFinalSDQ.Store_" + lcCnt
4342 lcQtyFld = "loFinalSDQ.Qty_" + lcCnt
4343 &lcStoreFld = Edi_Store
4344 &lcQtyFld = Trans(total_qty)
4345 lnCnt = lnCnt + 1
4346 IF lnCnt = 11
4347 Select (tcFinalSDQ)
4348 Append Blank
4349 Gather Name loFinalSDQ
4350 Select (tcSDQ)
4351 For lnCnt=1 To 10
4352 lcStoreFld = "loFinalSDQ.Store_" + Padl(Alltr(Trans(lnCnt, "99")), 2, '0')
4353 lcQtyFld = "loFinalSDQ.Qty_" + Padl(Alltr(Trans(lnCnt, "99")), 2, '0')
4354 &lcStoreFld = ''
4355 &lcQtyFld = ''
4356 Endfor
4357 lnCnt = 1
4358 ENDIF
4359 ENDSCAN
4360 IF lnCnt > 1
4361 Select (tcFinalSDQ)
4362 Append Blank
4363 Gather Name loFinalSDQ
4364 Select (tcSDQ)
4365 ENDIF
4366
4367 loFinalSDQ.fkey = Padl(Trans(fkey), 10)
4368 loFinalSDQ.pkey = Padl(Trans(hpkey), 10)
4369
4370 loFinalSDQ.sku = sku
4371 loFinalSDQ.upc = upc
4372 lcPo_num = po_num
4373 lcEdi_center = edi_center
4374 *=== TR 1074595 6-Feb-2014 Goutam
4375
4376 *--- TR 1074595 6-Feb-2014 Goutam
4377 *lnCnt = 1
4378 *lcCnt = Padl(Alltr(Trans(lnCnt, "99")), 2, '0')
4379 *=== TR 1074595 6-Feb-2014 Goutam
4380
4381 ENDIF
4382
4383 *--- TR 1074595 6-Feb-2014 Goutam
4384*!* lcCnt = Padl(Alltr(Trans(lnCnt, "99")), 2, '0')
4385*!* lcStoreFld = "loFinalSDQ.Store_" + lcCnt
4386*!* lcQtyFld = "loFinalSDQ.Qty_" + lcCnt
4387*!* &lcStoreFld = Edi_Store
4388*!* &lcQtyFld = Trans(total_qty)
4389*!* lnCnt = lnCnt + 1
4390 *=== TR 1074595 6-Feb-2014 Goutam
4391
4392 *--- TR 1074595 6-Feb-2014 Goutam
4393 *Endscan
4394 ENDDO
4395 *=== TR 1074595 6-Feb-2014 Goutam
4396
4397 *--- TR 1074595 6-Feb-2014 Goutam
4398*!* Select (tcFinalSDQ)
4399*!* Append Blank
4400*!* Gather Name loFinalSDQ
4401 *=== TR 1074595 6-Feb-2014 Goutam
4402
4403 Select (lnWorkArea)
4404 Endwith
4405 Endproc
4406
4407 *- 1005605 06/16/04 YIK
4408 Procedure ConsolidatePrepackByRangeSKU
4409 Lparameters pcTransHeader, pcTransDetail
4410 Local llRetVal, lnOldSelect, lnRangeDtlPkey, lcCurRangeSKU,;
4411 lcRng_style, lcRng_color, lcRng_lbl, lcRng_pack, loRecord, lcReplaceStr
4412
4413 Local lcCurRangeDtlSKU, llFound && TR 1013140 NH 2005-09-19
4414 llRetVal= .T.
4415 lnOldSelect= Select()
4416 With This
4417 lcSQLString = "Select ph.ppk_desc, ph.pack_qty, ph.mulcllb_ok, pd.*, " + ;
4418 " s.style, s.style as PPK_Style, s.color_code as PPK_Color, " + ;
4419 " s.lbl_code as PPK_Label,s.dimension as PPK_Dimension, " + ;
4420 "'" + Space(12) + "' as UPC," + "'" + Space(27) + "' as SKU, " +;
4421 "'" + Space(14) + "' as EAN, " +;
4422 "999999 as PPSize_qty , 999 as Sizebucket ,"+;
4423 "'" + Space(5) + "' AS Size_Desc " +;
4424 " from zzxppakh ph, zzxppakd pd, zzxscolr s " + ;
4425 " Where 0 = 1 "
4426 *- , ph.pkey AS itm_pkey
4427 llRetVal= llRetVal And v_sqlexec(lcSQLString, "__RangeSLN")
4428 .MakeCursorWritable("__RangeSLN", "tcRangeSLN")
4429 .TableClose("__RangeSLN")
4430 Select Distinct ord_num, rng_style, rng_color, rng_lbl, rng_pack, ;
4431 rng_bk, rng_qty, rng_type, Sum(total_qty) As total_qty ;
4432 From (pcTransDetail) ;
4433 Where Errs_Flg_D<> "Y" And rng_style<> '' ; && range style
4434 Group By rng_style, rng_color, rng_lbl, rng_pack, ;
4435 rng_bk, rng_qty, rng_type, ord_num ;
4436 Into Cursor tcTemp
4437
4438 lcErrs_Msg= "Sum of units for the same range style must be divisible by "
4439 lcCurRangeSKU= ""
4440 Select tcTemp
4441 Scan
4442 lcCurRangeSKU= rng_style+ rng_color+ rng_lbl+ rng_pack+ Str(rng_bk) + Str(ord_num)
4443 lnCurTotal_qty= total_qty
4444
4445 * For "S"et range type when Mod(total_qty, rng_qty)> 0 -- Invalid range qty
4446 *- Add type 'R' which is to be rolled up in a single line but w/o qty conversion.
4447 If rng_type = SET_RANGE_TYPE Or rng_type = RUN_RANGE_TYPE
4448 * Mark group of trans. detail for same Range style with error
4449 * when Sum(total_qty) not divisible by rng_qty for "S"et range style
4450
4451 *- Added ..AND rng_type = SET_RANGE_TYPE - qty conversion only for 'S'et
4452 If Mod(tcTemp.total_qty, tcTemp.rng_qty) > 0 And tcTemp.rng_qty> 0 And rng_type = SET_RANGE_TYPE
4453 Replace Errs_Msg_D With Errs_Msg_D + lcErrs_Msg + ;
4454 allt(Str(tcTemp.rng_qty)) + CRLF, Errs_Flg_D With "Y" ;
4455 For rng_style+ rng_color+ rng_lbl+ rng_pack+ Str(rng_bk) +;
4456 Str(ord_num)= lcCurRangeSKU In (pcTransDetail)
4457 Else
4458 * Consolidate group of trans. detail for same range style
4459 If Seek(lcCurRangeSKU, pcTransDetail, "RangeSKU")
4460 Select (pcTransDetail)
4461 Scatter Name loRecord
4462 * Switch s,c,l,d,sz with range s,c,l,d,sz
4463 loRecord.Style= loRecord.rng_style
4464 loRecord.color_code= loRecord.rng_color
4465 loRecord.lbl_code= loRecord.rng_lbl
4466 loRecord.Dimension= loRecord.rng_pack
4467 loRecord.sizebucket= loRecord.rng_bk
4468 If rng_type = SET_RANGE_TYPE
4469 loRecord.total_qty= lnCurTotal_qty/ loRecord.rng_qty && rollup total qty by range SKU
4470 Else
4471 loRecord.total_qty= lnCurTotal_qty
4472 Endif
4473 * reset range SKU; only want to consolidate once
4474 loRecord.rng_style= ''
4475 loRecord.rng_color= ''
4476 loRecord.rng_lbl= ''
4477 loRecord.rng_pack= ''
4478 loRecord.rng_bk= 0
4479 loRecord.rng_qty= 0
4480
4481 * Get range style price
4482 llRetVal= vl_rangr(loRecord.division,, 'tcRangh', loRecord.Style, ;
4483 loRecord.color_code, loRecord.lbl_code, loRecord.Dimension)
4484 lcPrice_code= Iif(Empty(&pcTransDetail..price_code), 'a', &pcTransDetail..price_code)
4485 lnPrice= Eval("tcRangh." + lcPrice_code + "_price")
4486 loRecord.price= lnPrice
4487 loRecord.org_price= lnPrice
4488
4489 * Remove all trans detail with same range SKU and replace with
4490 * rollup level of range SKU
4491 *- Insert range style components into tcRangeSLN (structure of pcTransDetail)
4492 *--- TR 1013140 NH 2005-09-19
4493 *!* llRetVal= vl_rangd1(loRecord.division, "", 'tcRangd', style, Color_code, Lbl_code, dimension)
4494 *--- bring range style all details
4495 llRetVal = llRetVal And vl_rangd(loRecord.division, "", 'tcRangd', loRecord.Style, ;
4496 loRecord.color_code, loRecord.lbl_code, loRecord.Dimension)
4497
4498 This.oLog.LogEntry("Range style detail components query " + Iif(llRetVal, "completed.","failed."))
4499 lcCurRangeDtlSKU = ""
4500 llFound = .F.
4501 *=== TR 1013140 NH 2005-09-19
4502
4503 lnPkey = loRecord.pkey
4504 Scan For rng_style+ rng_color+ rng_lbl+ rng_pack+ Str(rng_bk)+ ;
4505 Str(ord_num) = lcCurRangeSKU And sln_req = 'Y'
4506 lnItemPkey = pkey
4507 *--- TR 1013140 NH 2005-09-19
4508 *-- get to the matching rangd record
4509 lcCurRangeDtlSKU = Style + color_code + lbl_code + Dimension
4510 Select tcRangd
4511 Locate For Style + color_code + lbl_code + Dimension = lcCurRangeDtlSKU
4512 llFound = Found()
4513 Select (pcTransDetail)
4514 If Not llFound
4515 .oLog.LogEntry("SKU " + lcCurRangeDtlSKU + " does not exists for range style " +;
4516 rng_style+ rng_color+ rng_lbl+ rng_pack+ Str(rng_bk))
4517 llRetVal = .F.
4518 Endif
4519 *=== TR 1013140 NH 2005-09-19
4520
4521 If llRetVal
4522 lcQtyFld = "SIZE" + Trans(sizebucket, "@L 99") + "_qty"
4523 lnppsize_qty = Eval('tcRangd.' + lcQtyFld)
4524 lnPack_total = tcRangd.total_qty
4525 Else
4526 lnppsize_qty = 0
4527 lnPack_total = 0
4528 Endif
4529 lcReplaceStr = "Replace pack_qty with tcRangh.rng_qty, " + ;
4530 "ppsize_qty with lnppsize_qty, Pack_total with lnPack_total, " + ;
4531 "PPK_style with rng_style, PPK_color with rng_color, " + ;
4532 "PPK_label with rng_lbl, PPK_dimension with rng_pack "
4533 *- , itm_pKey with lnpkey
4534 .ScatterGather(pcTransDetail, "tcRangeSLN", ;
4535 lcReplaceStr, lnItemPkey)
4536 Endscan
4537 Delete For rng_style+ rng_color+ rng_lbl+ rng_pack+ Str(rng_bk)+ ;
4538 Str(ord_num) = lcCurRangeSKU In (pcTransDetail)
4539 Append Blank
4540 Gather Name loRecord
4541 Endif
4542 Endif
4543 *- 1006052 07/01/04 YIK
4544 *- Populate SLNs for range styles type 'P'
4545 Else
4546 .PopulateSLNsForRangeP(lcCurRangeSKU, pcTransDetail)
4547 *= 1006052
4548 Endif
4549
4550 Endscan
4551 Use In Select("tcTemp")
4552 Endwith
4553
4554 Select(lnOldSelect)
4555 Return llRetVal
4556
4557 *- 1009713 3/3/05 YIK
4558 *- Added tlDelimited, tcDelimiter
4559 Procedure BuildSLNString
4560 Lparameters tcDetail, tcSLN, tcSLNIndexTag, tcFlatFileString, tlDelimited, tcDelimiter
4561 Local llRetVal, lnOldSelect
4562 llRetVal= .T.
4563 lnOldSelect= Select()
4564
4565 lcCurrItemPkey= &tcDetail..pkey
4566 Select (tcSLN)
4567
4568 * --- TR 1044514 3/25/10 CM/NSD
4569 If This.lVMI
4570 If Seek(lcCurrItemPkey, tcSLN, tcSLNIndexTag)
4571 Scan While itm_pkey+customer = lcCurrItemPkey
4572 tcFlatFileString = tcFlatFileString + EDI_SLN_TAG + tcDelimiter + .ConvertRecordToText(tcSLN, tlDelimited, tcDelimiter)
4573 Endscan
4574 Endif
4575 Else
4576 * === TR 1044514 3/25/10 CM/NSD
4577
4578 If Seek(lcCurrItemPkey, tcSLN, tcSLNIndexTag)
4579 Scan While itm_pkey+customer = lcCurrItemPkey
4580
4581 * --- TR 1044514 3/25/10 CM
4582 *tcFlatFileString = tcFlatFileString + EDI_SLN_TAG+ .ConvertRecordToText(tcSLN, tlDelimited, tcDelimiter)
4583 tcFlatFileString = tcFlatFileString + EDI_SLN_TAG + tcDelimiter + .ConvertRecordToText(tcSLN, tlDelimited, tcDelimiter)
4584 * === TR 1044514 3/25/10 CM
4585
4586 Endscan
4587 Endif
4588
4589 * --- TR 1044514 3/25/10 CM/NSD
4590 Endif
4591 * === TR 1044514 3/25/10 CM/NSD
4592
4593 Select(lnOldSelect)
4594 Return llRetVal
4595 Endproc
4596 *= 1005605 YIK
4597
4598 *--- TR 1013007 NH 2005-10-17
4599
4600 ***********************************************************************************************
4601
4602 Procedure CreateOPR_TransCursor(pceoprTH, pceoPRtd)
4603
4604 Local lnOldSelect, llRetVal, lcDivCursor, loDiv
4605 lnOldSelect = Select()
4606 llRetVal = .T.
4607 lcDivCursor = Sys(2015)
4608 With This
4609
4610 *--- TR 1025228 9-Jul-2007 Goutam
4611 *Select h.*, c.sln_req, c.SkipPpkUPC, c.sku_upc As c_sku_upc ;
4612 From vzzeoPRtH_Proc h ;
4613 JOIN zzeoPRcr c On h.customer = c.customer And h.division = c.division ;
4614 Into Cursor __EoPRth
4615 *--- TR 1034481 NH
4616
4617*!* Select h.*, NVL(c.sln_req, c2.sln_req) as sln_req, NVL(c.SkipPpkUPC, c2.SkipPpkUPC) as SkipPpkUPC, ;
4618*!* NVL(c.DUNS_req, c2.DUNS_req) as DUNS_req, NVL(c.suppl_req, c2.suppl_req) AS suppl_req, ;
4619*!* NVL(c.dept_req, c2.dept_req) AS dept_req, NVL(c.po_req, c2.po_req) AS po_req, ;
4620*!* NVL(c.sku_upc, c2.sku_upc) As c_sku_upc, NVL(c.ack_by, c2.ack_by) As c_ack_by, ;
4621*!* NVL(c.vnd_id, c2.vnd_id) As c_vnd_id, NVL(c.vnd_vers, c2.vnd_vers) As vnd_vers, ;
4622*!* NVL(c.pKey, c2.pKey) As Controlpkey ;
4623*!* From vzzeoPRtH_Proc h ;
4624*!* LEFT JOIN zzeoPRcr c On h.customer = c.customer And h.division = c.division ;
4625*!* AND h.oVnd_Key = c.oVnd_Key ;
4626*!* LEFT JOIN zzeoPRcr c2 On h.customer = c2.customer And h.division = c2.division ;
4627*!* AND c2.oVnd_Key = ' ' ;
4628*!* Into Cursor __EoPRth
4629
4630 *--- resolve which control ref record should be used (take the pkey of zzeoprcr)
4631 CREATE CURSOR tcOprXcr (pkey i,pkey_c i)
4632
4633 INSERT INTO tcOprXcr ;
4634 SELECT h.pkey, NVL(NVL(c.pkey, c2.pkey),0) pkey_c ;
4635 From vzzeoPRtH_Proc h ;
4636 LEFT JOIN zzeoPRcr c On h.customer = c.customer And h.division = c.division ;
4637 AND h.oVnd_Key = c.oVnd_Key ;
4638 LEFT JOIN zzeoPRcr c2 On h.customer = c2.customer And h.division = c2.division ;
4639 AND c2.oVnd_Key = ' '
4640
4641 INDEX on pkey TAG pkey
4642 INDEX on pkey_c TAG pkey_c
4643
4644 *--- Now, we do not have any null value even if there is no matching conrol ref record
4645 Select h.*, NVL(c.sln_req, ' ') as sln_req, NVL(c.SkipPpkUPC, ' ') as SkipPpkUPC, ;
4646 NVL(c.DUNS_req, ' ') as DUNS_req, NVL(c.suppl_req, ' ') AS suppl_req, ;
4647 NVL(c.dept_req, ' ') AS dept_req, NVL(c.po_req, ' ') AS po_req, ;
4648 NVL(c.sku_upc, ' ') As c_sku_upc, NVL(c.ack_by, ' ') As c_ack_by, ;
4649 NVL(c.vnd_id, ' ') As c_vnd_id, NVL(c.vnd_vers, ' ') As vnd_vers, ;
4650 x.pKey_c As Controlpkey ;
4651 from vzzeoPRtH_Proc h inner join tcOprXcr x ON h.pkey = x.pkey ;
4652 left outer join zzeoPRcr c on x.pkey_c = c.pkey ;
4653 Into Cursor __EoPRth
4654
4655 *=== TR 1034481 NH
4656 *=== TR 1025228 9-Jul-207 Goutam
4657
4658 .MakeCursorWritable("__EoPRth", pceoprTH)
4659
4660 Select (pceoprTH)
4661
4662 *--- TR 1025228 9-Jul-2007 Goutam
4663 Replace ALL sku_upc WITH c_sku_upc, vnd_id WITH c_vnd_id, ack_by WITH c_ack_by
4664 *=== TR 1025228 9-Jul-2007 Goutam
4665
4666 .nRecordsToProcess= Recc(pceoprTH)
4667 Index On pkey Tag pkey
4668 Index On Str(ord_num) Tag ord
4669
4670 *--- TR 1025228 3-Jul-2007 Goutam
4671 *Index On customer+division Tag cust_div
4672 Index On customer + division + oVnd_Key Tag cust_div
4673 *--- TR 1025228 3-Jul-2007 Goutam
4674
4675 * Make tran detail cursor
4676 * --- TR 1048571 8/18/10 CM --- Added Invoice_Color
4677 Select *, SPACE(5) as Invoice_Color From vzzeoPRtD_Proc Into Cursor __EoPRtd
4678 This.MakeCursorWritable("__EoPRtd", pceoPRtd)
4679 Select (pceoPRtd)
4680
4681 * --- TR 1048571 8/18/10 CM
4682 REPLACE ALL Invoice_Color WITH color_code , po4_uom with ib_uom && TR 1061313 FH- added po4_uom with ib_uom 05/07/12
4683 * === TR 1048571 8/18/10 CM
4684
4685 Index On pkey Tag pkey
4686 Index On Empty(upc) Tag upc
4687 Index On Empty(sku) Tag sku
4688 *--- TR 1044382 16-Dec-2009 JK ---
4689 Index On rng_style+ rng_color+ rng_lbl+ rng_pack+ Str(rng_bk)+ Str(ord_num)+line_status Tag RangeSKU
4690 Index On division+Style+color_code+lbl_code+Dimension+line_status Tag OurSKU
4691 *=== TR 1044382 16-Dec-2009 JK ===
4692 Endwith
4693
4694 Select(lnOldSelect)
4695 Return llRetVal
4696
4697 Endproc
4698
4699 *=============================================================================================================
4700 * Get Record Count of Records with Auto-Proc flag = Y
4701 * TR 1031624 JUL-14-2008 BR
4702 *=============================================================================================================
4703 Procedure GetRecordCountWithAutoProc
4704 Parameters tcVzzeoPRih_Proc
4705 Local lcOldOrder, lcAutoProcRecords
4706
4707 With This
4708 Select (tcVzzeoPRih_Proc)
4709 lcAutoProcRecords = 0
4710 lcOldOrder = Order()
4711 Set Order To auto_proc
4712
4713 If Seek("Y", tcVzzeoPRih_Proc,"auto_proc")
4714 Scan While auto_proc = "Y"
4715
4716 Endscan
4717 lcAutoProcRecords = .CountTotalRecs (tcVzzeoPRih_Proc)
4718 Endif
4719 Endwith
4720
4721 Set Order To (lcOldOrder)
4722 Return lcAutoProcRecords
4723 Endproc
4724 *=============================================================================================================
4725 * TR 1031624 JUL-14-2008 BR
4726 *=============================================================================================================
4727
4728 ***********************************************************************************************
4729 *--- pcoprth_filter temporary table is know to 850 process. by updating this temporary table
4730 *--- we pass the info of 855 records that went into the 855 outbound flat.
4731
4732 Procedure Update855FlagIn850TempIpoth(pcorprth, pcoprth_850)
4733
4734 Local llRetVal, lnOldSelect
4735 Local lcTmpOprTH
4736 llRetVal = .T.
4737 lnOldSelect = Select()
4738 lcTmpOprTH = Sys(2015)
4739
4740 Select Distinct pkey From (pcorprth) Where Errs_Flg_H <> 'Y' Into Cursor (lcTmpOprTH)
4741
4742 With This
4743 .cSQLTempTable = ""
4744 If Reccount(lcTmpOprTH) > 0
4745 llRetVal = llRetVal And .GenerateSQLTempTable(lcTmpOprTH)
4746 If .PopulateSQLTempTable(lcTmpOprTH) And Not Empty(.cSQLTempTable) And llRetVal
4747 *--- TR 1034481 NH : Change 'Y' to 'P'
4748 * TR 1078104 KISHORE 22-APR-2014 added - , AUTO_PROC = 'Y'
4749 lcSQL = "UPDATE F SET PROC_855 = 'P', AUTO_PROC = 'Y' FROM " + pcoprth_850 + " F JOIN " + ;
4750 .cSQLTempTable + " T ON F.PKEY = T.PKEY "
4751 *=== TR 1034481 NH
4752 llRetVal = llRetVal And v_sqlexec(lcSQL)
4753 Endif
4754 Endif
4755 Endwith
4756 Select(lnOldSelect)
4757 Return llRetVal
4758 Endproc
4759
4760 ***********************************************************************************************
4761
4762 Procedure ScatterGather_STATUS_855
4763 Lparameter tcSource, tcTarget, tcParentTbl
4764
4765 Local lnOldSelect
4766 lnOldSelect = Select()
4767 Local loParentTbl
4768 Select(tcParentTbl)
4769 *--- do not populate detail for status 855 code = 'AK' or 'RJ'
4770 *- 1034481 10/06/08 YIK
4771 *- Bad logic. S/b AND instead of OR
4772 *- Scan For status_855 <> 'AK' Or status_855 <> 'RJ'
4773 Scan For status_855 <> 'AK' AND status_855 <> 'RJ'
4774
4775 Scatter Name loParentTbl
4776 Select(tcSource)
4777
4778 *--- TechRec 1041325 17-Sep-2009 T.Shenbagavalli removed condition And status_855 == loParentTbl.status_855 ---
4779 Scan For fkey = loParentTbl.pkey && And status_855 == loParentTbl.status_855 TR 1041325
4780
4781 Scatter Memvar Memo
4782 Select (tcTarget)
4783 Append Blank
4784 Gather Memvar Memo
4785 Endscan
4786 Endscan
4787
4788 Select(lnOldSelect)
4789 Endproc
4790
4791 *******************************************************************************************
4792
4793 *=== TR 1013007 NH 2005-10-17
4794
4795 *--- TR 1025228 6-Jul-2007 Goutam
4796 FUNCTION GetPOHistHeader
4797 LPARAMETERS pceTransHeader
4798 LOCAL lcSqlString, llRetVal, lcTempzzeipohh, lnOldSelect, lcTmpIpohhCursor
4799
4800 lnOldSelect = SELECT()
4801 lcTempzzeipohh = GetUniqueFileName()
4802 lcTmpIpohhCursor = GetUniqueFileName()
4803
4804 llRetVal = .T.
4805
4806 lcSqlString = "SELECT ord_num FROM " + pceTransHeader
4807
4808 llRetVal = llRetVal and v_sqlexec(lcSqlString, lcTmpIpohhCursor,,.t.)
4809
4810 .cSQLTempTable = ""
4811 IF RECCOUNT(lcTmpIpohhCursor) > 0
4812 llRetVal = llRetVal And .GenerateSQLTempTable(lcTmpIpohhCursor)
4813 llRetVal = llRetVal And .PopulateSQLTempTable(lcTmpIpohhCursor)
4814 ENDIF
4815
4816 IF Not EMPTY(.cSQLTempTable) And llRetVal
4817 *--- TR 1030443 22-Apr-2008 Goutam. Added extern_cust in the select list of following sql
4818 *--- TR 1078866 26-Jun-2014 Partha. Added edibill_to ---
4819 lcSqlString = "SELECT hh.ord_num, hh.ovnd_key, hh.extern_cust, hh.edibill_to " + ;
4820 " FROM zzeipohh hh JOIN " + .cSQLTempTable + " th " + ;
4821 " ON th.ord_num = hh.ord_num"
4822
4823 llRetVal = llRetVal and v_sqlexec(lcSqlString, lcTempzzeipohh)
4824
4825 *--- TR 1030443 22-Apr-2008 Goutam. Added extern_cust, word COALESCE and LEFT JOIN in the update list of following sql
4826 *--- TR 1078866 26-Jun-2014 Partha. Added edibill_to ---
4827 lcSqlString = "UPDATE th SET ovnd_key = COALESCE(hh.ovnd_key,''), " + ;
4828 " extern_cust = COALESCE(hh.extern_cust,'') " + ;
4829 " ,edibill_to = COALESCE(hh.edibill_to,'') " + ;
4830 " FROM " + pceTransHeader + " th LEFT JOIN " + lcTempzzeipohh + " hh " + ;
4831 "ON hh.ord_num = th.ord_num"
4832
4833 llRetVal = llRetVal and v_sqlexec(lcSqlString,,, .t.)
4834 .oLog.LogEntry("Updating oVnd_key in transaction header " + IIF(llRetVal,"completed.","failed."))
4835 ENDIF
4836
4837 IF USED(lcTempzzeipohh)
4838 USE IN (lcTempzzeipohh)
4839 ENDIF
4840 IF USED(lcTmpIpohhCursor)
4841 USE IN (lcTmpIpohhCursor)
4842 ENDIF
4843
4844 SELECT (lnOldSelect)
4845
4846 RETURN llRetVal
4847
4848 ENDFUNC
4849 *=== TR 1025228 6-Jul-2007 Goutam
4850
4851 *********************************************************************************************
4852 *--- TR 1030443 22-Apr-2008 Goutam
4853 FUNCTION PopulateShippingInfo
4854 LPARAMETERS pceTransHeader
4855
4856 LOCAL lcSqlString, llRetVal, lnOldSelect, lcTempShiper, lcTmpShprCursor
4857 llRetVal = true
4858 lnOldSelect = SELECT()
4859
4860 lcTempShiper = GetUniqueFileName()
4861 lcTmpShprCursor = GetUniqueFileName()
4862
4863 lcSqlString = "SELECT pkey, Shipper FROM " + pceTransHeader + " WHERE NOT EMPTY(Shipper)"
4864
4865 llRetVal = llRetVal and v_sqlexec(lcSqlString, lcTmpShprCursor,,.t.)
4866
4867 .cSQLTempTable = ""
4868 IF llRetVal AND RECCOUNT(lcTmpShprCursor) > 0
4869 llRetVal = llRetVal And .GenerateSQLTempTable(lcTmpShprCursor)
4870 llRetVal = llRetVal And .PopulateSQLTempTable(lcTmpShprCursor)
4871 ENDIF
4872
4873 IF llRetVal AND Not EMPTY(.cSQLTempTable)
4874 lcSqlString = "SELECT th.pkey, sh.Shipper, sh.scac_code, sh.edishptype, sh.ship_name " + ;
4875 " FROM zzxshipr sh JOIN " + .cSQLTempTable + " th " + ;
4876 " ON th.Shipper = sh.Shipper"
4877
4878 llRetVal = llRetVal and v_sqlexec(lcSqlString, lcTempShiper)
4879
4880 lcSqlString = "UPDATE th SET th.scac_code = sh.scac_code, " + ;
4881 " th.trans_type = sh.edishptype, " + ;
4882 " th.ship_name = sh.ship_name " + ;
4883 " FROM " + pceTransHeader + " th JOIN " + lcTempShiper + " sh " + ;
4884 "ON sh.pkey = th.pkey"
4885
4886 llRetVal = llRetVal and v_sqlexec(lcSqlString,,, .t.)
4887 .oLog.LogEntry("Updating shipper information in transaction header " + IIF(llRetVal,"completed.","failed."))
4888 ENDIF
4889
4890 IF USED(lcTmpShprCursor)
4891 USE IN (lcTmpShprCursor)
4892 ENDIF
4893 IF USED(lcTempShiper)
4894 USE IN (lcTempShiper)
4895 ENDIF
4896
4897 SELECT (lnOldSelect)
4898 RETURN llRetVal
4899
4900 ENDFUNC
4901
4902 FUNCTION PrepareCursorsForTRK
4903 LPARAMETERS pceTransHeader, pcoPRmRK
4904 LOCAL llRetVal, lnOldSelect, lcTempTrack
4905
4906 llRetVal = true
4907 lnOldSelect = SELECT()
4908
4909 lcTempTrack = GetUniqueFileName()
4910
4911 lcSqlString = "SELECT pkey, ord_num, pick_num, inv_num FROM " + pceTransHeader + " WHERE Errs_Flg_H <> 'Y'"
4912
4913 llRetVal = llRetVal and v_sqlexec(lcSqlString, lcTempTrack,,.t.)
4914
4915 .cSQLTempTable = ""
4916 IF llRetVal AND USED(lcTempTrack)
4917 llRetVal = llRetVal And .GenerateSQLTempTable(lcTempTrack)
4918 llRetVal = llRetVal And .PopulateSQLTempTable(lcTempTrack)
4919 ENDIF
4920
4921 IF llRetVal AND Not EMPTY(.cSQLTempTable)
4922
4923 lcSqlString = "select * from (select oh.pkey fkey, ph.track_no from zzoctnph ph " + ;
4924 " join zzoordsp sp " + ;
4925 " on sp.pkey = ph.fkey " + ;
4926 " join " + .cSQLTempTable + " oh " + ;
4927 " on oh.ord_num = sp.ord_num " + ;
4928 " and oh.pick_num = sp.pick_num " + ;
4929 " and oh.inv_num = sp.inv_num " + ;
4930 " where coalesce(ph.track_no,'') <> '' " + ;
4931 " and oh.pick_num > 0 " + ;
4932 " union all " + ;
4933 " select oh.pkey, ph.track_no from zzoctnsh ph " + ;
4934 " join zzoordsp sp " + ;
4935 " on sp.pkey = ph.fkey " + ;
4936 " join " + .cSQLTempTable + " oh " + ;
4937 " on oh.ord_num = sp.ord_num " + ;
4938 " and oh.pick_num = sp.pick_num " + ;
4939 " and oh.inv_num = sp.inv_num " + ;
4940 " where coalesce(ph.track_no,'') <> '' " + ;
4941 " and oh.pick_num > 0) tmp " + ;
4942 " group by fkey, track_no"
4943
4944 llRetVal = llRetVal and v_sqlexec(lcSqlString, pcoPRmRK)
4945 ENDIF
4946
4947 IF USED(lcTempTrack)
4948 USE IN (lcTempTrack)
4949 ENDIF
4950
4951 SELECT (lnOldSelect)
4952 RETURN llRetVal
4953 ENDFUNC
4954 *=== TR 1030443 22-Apr-2008 Goutam
4955
4956*--- TechRec 1038247 17-Mar-2009 T.Shenbagavalli ---
4957*===================================================================================
4958 FUNCTION PopulateDiscount
4959 LPARAMETERS pcTransDetail
4960
4961 LOCAL lcSqlString, llRetVal, lnSelect, lcSQLString, tcDiscr, tcTemp
4962 llRetVal = True
4963 lnSelect = SELECT()
4964
4965 tcTemp = GetUniqueFileName()
4966
4967 lcSQLString = "SELECT distinct discount FROM " + pcTransDetail + " WHERE Errs_Flg_D <> 'Y'"
4968
4969 llRetVal = llRetVal and v_sqlexec(lcSqlString, tcTemp,,.t.)
4970
4971 WITH This
4972 .cSQLTempTable = ""
4973 IF llRetVal AND USED( tcTemp )
4974 llRetVal = llRetVal And .GenerateSQLTempTable(tcTemp)
4975 llRetVal = llRetVal And .PopulateSQLTempTable(tcTemp)
4976 ENDIF
4977
4978 IF llRetVal AND Not EMPTY(.cSQLTempTable)
4979 lcSQLString = " SELECT c.discount, c.disc_perc " + ;
4980 " FROM zzxdiscr c JOIN " + .cSQLTempTable + " t " + ;
4981 " ON c.discount = t.discount "
4982
4983 tcDiscr = GetUniqueFileName()
4984 llRetVal = llRetVal and v_sqlexec(lcSqlString, tcDiscr )
4985
4986 lcSqlString = "UPDATE td SET td.disc_perc = cr.disc_perc " + ;
4987 " FROM " + pcTransDetail + " td JOIN " + tcDiscr + " cr " + ;
4988 "ON td.discount = cr.discount " + ;
4989 " WHERE td.Errs_Flg_D <> 'Y' "
4990
4991 llRetVal = llRetVal and v_sqlexec(lcSqlString,,, .t.)
4992 .oLog.LogEntry("Updating discount information in transaction detail " + IIF(llRetVal,"completed.","failed."))
4993 ENDIF
4994
4995 .TableClose(tcDiscr)
4996 .TableClose(tcTemp)
4997 ENDWITH
4998
4999 SELECT(lnSelect)
5000 RETURN llRetVal
5001 ENDFUNC
5002*===================================================================================
5003*=== TechRec 1038247 17-Mar-2009 T.Shenbagavalli ===
5004
5005* --- TR 1048571 8/18/10 CM
5006* --- Adding rolling color functionality to the 855(o) Process
5007*============================================================
5008
5009 FUNCTION ChangeColor
5010 LPARAMETERS tceoprtd
5011 LOCAL llRetVal, lnSelect
5012
5013 llRetVal = true
5014 lnSelect = SELECT()
5015
5016 WITH This
5017 REPLACE color_Code WITH org_color , ;
5018 org_color WITH Invoice_Color ;
5019 FOR !EMPTY(org_color) ;
5020 IN (tceoprtd)
5021
5022 GO TOP IN (tceoprtd)
5023 ENDWITH
5024
5025 SELECT (lnSelect)
5026 RETURN llRetVal
5027 ENDFUNC
5028
5029*============================================================
5030
5031 FUNCTION RestoreColor
5032 LPARAMETERS tceoprtd
5033 LOCAL llRetVal, lnSelect
5034
5035 llRetVal = true
5036 lnSelect = SELECT()
5037
5038 WITH This
5039 REPLACE org_color WITH Color_code, ;
5040 color_Code WITH Invoice_Color ;
5041 FOR !EMPTY(org_color) ;
5042 IN (tceoprtd)
5043
5044 GO TOP IN (tceoprtd)
5045 ENDWITH
5046
5047 SELECT (lnSelect)
5048 RETURN llRetVal
5049 ENDFUNC
5050
5051*============================================================
5052* === TR 1048571 8/18/10 CM
5053
5054 *--- TechRec 1058716 11-Jan-2012 jisingh ---
5055 FUNCTION ValidateForzani
5056 LPARAMETERS tcHeader, tcDetail, tcControl
5057 LOCAL llRetVal, lnSelect, lcSQLString, lnThermoCnt, lcTemplate, lcHdrTag, lcDtlTag, lcFields
5058
5059 llRetVal = true
5060 lnSelect = SELECT()
5061 lcHdrTag = "HEADER."
5062 lcDtlTag = "DETAIL."
5063 lcTemplate = "FORZANI 5010"
5064
5065 WITH This
5066 lcSQLString = " SELECT h.customer, h.store, h.ship_dc, h.center_code, h.consol_code, " + ;
5067 " h.ord_num, h.division, h.factor, h.location, h.errs_msg_h, " + ;
5068 " h.status_855, h.po_num, h.po_date, h.supplier_num, h.pkey " + ;
5069 " ,h.edibill_to " + ; && TR 1078866 26-Jun-2014 Partha
5070 " FROM " + tcHeader + " h " + ;
5071 " LEFT JOIN " + tcControl + " c " + ;
5072 " ON h.division = c.division " + ;
5073 " AND h.customer = c.customer " + ;
5074 " WHERE c.template = 'FORZANI 5010' "
5075
5076 llRetVal = llRetVal AND v_SQLExec(lcSQLString, "tcForzani",,true)
5077
5078 IF llRetVal AND USED("tcForzani") AND RECCOUNT("tcForzani") > 0
5079 IF .lUserInterface
5080 .InitThermo(.CountTotalRecs("tcForzani"))
5081 lnThermoCnt = 0
5082 ENDIF
5083
5084 llRetVal = llRetVal AND v_SQLExec("SELECT * FROM zzeoprta WHERE 1 = 2", "__tmpAddrs")
5085
5086 SELECT ("tcForzani")
5087 SCAN
5088 IF .lUserInterface
5089 lnThermoCnt = lnThermoCnt + 1
5090 .AdvanceThermo(lnThermoCnt)
5091 ENDIF
5092 lcFields = ""
5093
5094 IF EMPTY(status_855)
5095 lcFields = lcFields + IIF(EMPTY(lcFields), "", ",") + lcHdrTag + "STATUS_855"
5096 ENDIF
5097
5098 IF EMPTY(po_num)
5099 lcFields = lcFields + IIF(EMPTY(lcFields), "", ",") + lcHdrTag + "PO_NUM"
5100 ENDIF
5101
5102 IF EMPTY(po_date) OR (po_date = {01/01/1900})
5103 lcFields = lcFields + IIF(EMPTY(lcFields), "", ",") + lcHdrTag + "PO_DATE"
5104 ENDIF
5105
5106 IF EMPTY(supplier_num)
5107 lcFields = lcFields + IIF(EMPTY(lcFields), "", ",") + lcHdrTag + "SUPPLIER_NUM"
5108 ENDIF
5109
5110 *--- TR 1078866 26-Jun-2014 Partha ---
5111 IF EMPTY(edibill_to)
5112 lcFields = lcFields + IIF(EMPTY(lcFields), "", ",") + lcHdrTag + "EDIBILL_TO"
5113 ENDIF
5114 *=== TR 1078866 26-Jun-2014 Partha ===
5115
5116 IF Bcaddres(customer,, "_tmpAddrs", store, ship_dc, center_code, consol_code, ord_num, division, factor, location) ;
5117 AND USED("_tmpAddrs") AND .CreateVerticalAddress("_tmpAddrs", "__tmpAddrs", "", "", "ZZEOPRTA") AND USED("__tmpAddrs")
5118
5119 SELECT __tmpAddrs
5120 INDEX ON addr_type TAG addrtype
5121
5122 IF !SEEK("BT","__tmpAddrs","addrtype")
5123 lcFields = lcFields + IIF(EMPTY(lcFields), "", ",") + lcHdrTag + "BILL_TO"
5124 ENDIF
5125
5126 IF !SEEK("ST","__tmpAddrs","addrtype")
5127 lcFields = lcFields + IIF(EMPTY(lcFields), "", ",") + lcHdrTag + "SHIP_TO"
5128 ENDIF
5129 ENDIF
5130
5131 IF NOT EMPTY(lcFields)
5132 REPLACE errs_msg_h WITH errs_msg_h + lcTemplate + " MANDATORY FIELD(S) EMPTY/MISSING: " + lcFields + CRLF, ;
5133 errs_flg_h WITH "Y" FOR pkey = tcForzani.pkey IN (tcHeader)
5134 ENDIF
5135 SELECT ("tcForzani")
5136 ENDSCAN
5137 ENDIF
5138
5139
5140 lcSQLString = " SELECT d.status_855, d.pkey " + ;
5141 " FROM " + tcHeader + " h " + ;
5142 " JOIN " + tcDetail + " d " + ;
5143 " ON h.pkey = d.fkey " + ;
5144 " LEFT JOIN " + tcControl + " c " + ;
5145 " ON h.division = c.division " + ;
5146 " AND h.customer = c.customer " + ;
5147 " WHERE c.template = 'FORZANI 5010' " + ;
5148 " AND EMPTY(d.status_855) "
5149
5150 llRetVal = llRetVal AND v_SQLExec(lcSQLString, "tcForzani",,true)
5151
5152
5153 IF llRetVal AND USED("tcForzani") AND RECCOUNT("tcForzani") > 0
5154 IF .lUserInterface
5155 .InitThermo(.CountTotalRecs("tcForzani"))
5156 lnThermoCnt = 0
5157 ENDIF
5158
5159 SELECT ("tcForzani")
5160 SCAN
5161 IF .lUserInterface
5162 lnThermoCnt = lnThermoCnt + 1
5163 .AdvanceThermo(lnThermoCnt)
5164 ENDIF
5165 lcFields = ""
5166
5167 IF EMPTY(status_855)
5168 lcFields = lcFields + IIF(EMPTY(lcFields), "", ",") + lcDtlTag + "STATUS_855"
5169 ENDIF
5170
5171 IF NOT EMPTY(lcFields)
5172 REPLACE errs_msg_d WITH errs_msg_d + lcTemplate + " MANDATORY FIELD(S) EMPTY/MISSING: " + lcFields + CRLF, ;
5173 errs_flg_d WITH "Y" FOR pkey = tcForzani.pkey IN (tcDetail)
5174 ENDIF
5175 ENDSCAN
5176 .TableClose("tcForzani")
5177 ENDIF
5178
5179 IF .lUserInterface
5180 .ResetThermo()
5181 ENDIF
5182 ENDWITH
5183
5184 SELECT (lnSelect)
5185 RETURN llRetVal
5186 ENDFUNC
5187
5188*============================================================
5189
5190 FUNCTION PopulateDataWhseFrom850
5191 LPARAMETERS pceTransHeader, pceTransDetail, pceControl, pcWhseHeader, pcWhseDetail, pczzeiPOtWhse
5192 LOCAL llRetVal, lnSelect
5193
5194 llRetVal = true
5195 lnSelect = SELECT()
5196
5197 WITH This
5198 SELECT DISTINCT h.ipo_pkey FROM (pceTransHeader) h ;
5199 JOIN (pceControl) c ON h.division = c.division AND h.customer = c.customer ;
5200 WHERE c.data_whse = 'Y' INTO CURSOR __TmpCursor
5201
5202 IF USED("__TmpCursor") AND RECCOUNT("__TmpCursor") > 0
5203 SELECT s.pkey, s.ord_num, s.line_seq, s.sizebucket, s.segment, s.element, ;
5204 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
5205 FROM (pczzeiPOtWhse) s, __TmpCursor t ;
5206 WHERE s.hfkey = t.ipo_pkey AND s.dfkey = 0 INTO CURSOR tcWhseHeader
5207
5208 .MakeCursorWritable("tcWhseHeader", pcWhseHeader)
5209 ENDIF
5210
5211
5212 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
5213 FROM (pceTransHeader) h ;
5214 JOIN (pceTransDetail) d ON h.pkey = d.fkey ;
5215 JOIN (pceControl) c ON h.division = c.division AND h.customer = c.customer ;
5216 WHERE c.data_whse = 'Y' INTO CURSOR __TmpCursor
5217
5218 IF USED("__TmpCursor") AND RECCOUNT("__TmpCursor") > 0
5219 SELECT s.pkey, s.ord_num, s.line_seq, s.sizebucket, s.segment, ;
5220 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
5221 FROM (pczzeiPOtWhse) s, __TmpCursor t ;
5222 WHERE s.dfkey = t.ipo_pkey AND s.line_seq = t.line_seq INTO CURSOR tcWhseDetail
5223
5224 .MakeCursorWritable("tcWhseDetail", pcWhseDetail)
5225 ENDIF
5226 .TableClose("tcWhseHeader")
5227 .TableClose("tcWhseDetail")
5228 .TableClose("__TmpCursor")
5229 ENDWITH
5230
5231 SELECT (lnSelect)
5232 RETURN llRetVal
5233 ENDFUNC
5234 *=== TechRec 1058716 11-Jan-2012 jisingh ===
5235
5236*--- TechRec 1078545 27-Jun-2014 vkrishnamurthy ---
5237*============================================================
5238 PROCEDURE GetParamBRO
5239 LPARAMETERS tcParamBro, tcDefault
5240 LOCAL lnSelect, lnParmNameIndex, lcParmName
5241
5242 lcParmName = ""
5243 lnSelect = SELECT()
5244
5245 lnParmNameIndex = ASCAN(.aParamBROs,tcParamBro,1,ALEN(.aParamBROs,1),1,9)
5246
5247 *-- Validate parameter
5248 IF lnParmNameIndex > 0
5249 lcParmName = ALLTRIM(.aParamBROs[lnParmNameIndex, 2])
5250 ENDIF
5251
5252 IF EMPTY(lcParmName) AND TYPE("tcDefault") == "C" AND NOT EMPTY(tcDefault)
5253 lcParmName = tcDefault
5254 ENDIF
5255
5256 SELECT (lnSelect)
5257 RETURN lcParmName
5258 ENDPROC
5259*======================================================================
5260 FUNCTION SetParamBRO
5261 LOCAL llRetVal, lnSelect,lcNewShipDate
5262 llRetVal = true
5263 lnSelect = SELECT()
5264
5265 WITH This
5266 lcNewShipDate = .getParamBro("PRM_NEW_SHIP_DATE")
5267 IF EMPTY(lcNewShipDate)
5268 .cNewShipDate = DATE()
5269 ELSE
5270 SET PROCEDURE TO clsmacro addi
5271 loDate = CREATEOBJECT("DynamicDate",lcNewShipDate)
5272 IF TYPE("loDate") = "O"
5273 lcNewShipDate = loDate.GetDynamicDate(loDate.cFilterValue)
5274 ENDIF
5275 loDate = null
5276
5277 .cNewShipDate = CTOD(lcNewShipDate)
5278 IF EMPTY(.cNewShipDate)
5279 .LogEntry("New Ship Date Not Set.")
5280 ELSE
5281 .LogEntry("New Ship Date: " + TRANSFORM(.cNewShipDate))
5282 ENDIF
5283 ENDIF
5284 ENDWITH
5285
5286 SELECT (lnSelect)
5287 RETURN llRetVal
5288 ENDFUNC
5289*========================================================================
5290*=== TechRec 1078545 27-Jun-2014 vkrishnamurthy ===
5291
5292*--- TR 1083402 14-Jan-2015 Partha ---
5293 FUNCTION ValidateQuidsi
5294 LPARAMETERS tcHeader, tcDetail, tcControl
5295 LOCAL llRetVal, lnSelect, lcSQLString, lnThermoCnt, lcTemplate, lcHdrTag, lcDtlTag, lcFields
5296 llRetVal = true
5297 lnSelect = SELECT()
5298 WITH This
5299ASSERT .f.
5300 lcSQLString = " SELECT p.lk_code FROM zzxlookp p " + ;
5301 " WHERE p.Lk_Type ='ACK29 Codes' "
5302 llRetVal = llRetVal AND v_SQLExec(lcSQLString, "zzxlookp")
5303
5304 lcSQLString = " SELECT d.ack29, d.pkey, d.fkey, c.ack29_req, c.customer, c.division, h.ord_num " + ;
5305 " FROM " + tcHeader + " h with (buffer = .t.) " + ;
5306 " JOIN " + tcDetail + " d with (buffer = .t.) " + ;
5307 " ON h.pkey = d.fkey " + ;
5308 " LEFT JOIN " + tcControl + " c " + ;
5309 " ON h.division = c.division " + ;
5310 " AND h.customer = c.customer " + ;
5311 " WHERE c.ack29_req = 'Y' " + ;
5312 " AND (d.ack29 = '' OR " + ;
5313 " NOT EXISTS ( SELECT 1 FROM zzxlookp p " + ;
5314 " WHERE p.LK_CODE=d.ack29 ) ) "
5315
5316 llRetVal = llRetVal AND v_SQLExec(lcSQLString, "tcQuidsi",,true)
5317 .TableClose("zzxlookp")
5318
5319 IF llRetVal AND USED("tcQuidsi") AND RECCOUNT("tcQuidsi") > 0
5320 IF .lUserInterface
5321 .InitThermo(.CountTotalRecs("tcQuidsi"))
5322 lnThermoCnt = 0
5323 ENDIF
5324
5325 SELECT ("tcQuidsi")
5326 INDEX on STR(fkey) TAG fkey
5327 GOTO TOP
5328
5329 llRetVal = llRetVal AND .SetRelation("tcQuidsi", "fkey", tcHeader, "str(pkey)")
5330 GOTO TOP
5331
5332 REPLACE auto_proc WITH 'N' FOR !EOF("tcQuidsi") IN (tcHeader)
5333
5334 SET RELATION to
5335
5336 .TableClose("tcQuidsi")
5337 ENDIF
5338
5339 IF .lUserInterface
5340 .ResetThermo()
5341 ENDIF
5342 ENDWITH
5343
5344 SELECT (lnSelect)
5345 RETURN llRetVal
5346 ENDFUNC
5347
5348*============================================================
5349
5350
5351*=== TR 1083402 14-Jan-2015 Partha ===
5352
5353Enddefine