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