· 9 years ago · Nov 11, 2016, 07:20 PM
1#Include SYSTEM.h
2
3*** Query for style headers. Use this to create a temp table to join the rest of the queries on.
4*** The last mod filter needs to be a parameter BRO because you are looking for changes in either (or) zzxstylr or zzxscolr
5*** Make this a class. See Tommy Bahama ajillus on how to post XML.
6*** We are going to need a place to store the info necessary to get to Threadvine site.
7
8DEFINE CLASS BPOThreadvineOutput As BPOThreadvineBase
9 Name = "BPOThreadvineOutput"
10 cJobID = "ThreadvineOutput"
11
12 cThreadvineURL = ""
13 cThreadvineUserID = ""
14 cThreadvinePasswd = ""
15 tLast_Mod = ""
16 cDeleteFlag = ""
17 cTempTbl = ""
18 oLog = NULL
19
20 nProcessStyles = 0
21 nSuccessStyles = 0
22 nFailedStyles = 0
23 nSkippedStyles = 0
24
25
26 lOnlyAvailability = .F.
27 lSendDeliveryDatesFromUDF = .f.
28
29 cImageHandling = "Y"
30
31 cOrderTypeExclusion = ""
32 cImageField = "stor_file"
33 cHistoryRecType = "S"
34
35 nMinimumUnits = 0
36 nMaximumUnits = 0
37
38 lPriceLevelSupport = .F.
39
40 cLocationFilter = ""
41 cAvailabilityFilter = ""
42 nWipDueDateOffset = 0
43
44 cFullAvailDate = ""
45
46 lArchiveStyles= .F.
47
48 cCatgDestFilter = ""
49
50 *--- TR 1081856 13-10-2014 VKK
51 cProcessFilter = ""
52 *=== TR 1081856 13-10-2014 VKK
53
54 lSplitPacks = .F.
55 cFullAvailSeason = ""
56
57 cLangLocaleTmp = ""
58
59 cTVRegionCode = ""
60 lOnlyAvailability_includePrice = .f.
61
62 lSendTranslations = .F.
63
64 *============================================================
65 PROCEDURE MainProc
66 LPARAMETER pcSQLFilterString, plNoScreenUI
67
68 LOCAL llRetVal, lcSyslockTablePath
69
70 llRetVal = true
71
72 WITH This
73 .oLog.OpenLog(.cJobID, I(.cJobID), .lScheduled)
74 .oLog.LogProgram("ThreadvineOutput.prg")
75
76 lcSyslockTablePath = AddBS(goEnv.envLoginTablePath.Value)
77 IF NOT v_SysLock(lcSyslockTablePath+"SYSLOCK", .cJobID, goEnv.cCompany)
78 .oLog.LogEntry("Process Locked")
79 .lProcessLocked = true
80 RETURN false
81 ENDIF
82
83 .CreateFormProgressBar(.lScheduled)
84 *--- TechRec 1065902 27-Dec-2012 jisingh ---
85 *.InitProgressBarTotal(100, "Total Threadvine Interface process")
86 .InitProgressBarTotal(100, "Total Threadvine Style Interface process")
87 *=== TechRec 1065902 27-Dec-2012 jisingh ===
88
89 llRetVal = llRetVal AND .CheckParameters()
90
91 IF llRetVal
92 llRetVal = llRetVal AND .GetStyleRecords(pcSQLFilterString)
93
94 llRetVal = llRetVal AND .PrepareLocalCursors()
95
96 llRetVal = llRetVal AND .CreateXMLFiles()
97
98 llRetVal = llRetVal AND .PostToThreadvine()
99
100 llRetVal = llRetVal AND .UpdateSkuHistory()
101 llRetVal = llRetVal AND .UpdateProductHistory()
102 ENDIF
103
104
105
106
107
108 .AdvanceThermo(100)
109 .AdvanceThermoTotal(100)
110
111 IF IsObject(.oFrmProgressBar) AND NOT IsNull(.oFrmProgressBar)
112 .m_Close()
113 ENDIF
114
115 USE IN SELECT("tcScolr")
116 USE IN SELECT("tcUPCnr")
117 USE IN SELECT("tcStylr")
118 USE IN SELECT("tcColrr")
119 USE IN SELECT("tcDlvcd")
120 USE IN SELECT("tcContr")
121 USE IN SELECT("zzxdivsr_local")
122 USE IN SELECT("zzxseasr_local")
123 USE IN SELECT("zzxpgrpr_local")
124 USE IN SELECT("zzxpgtyr_local")
125 USE IN SELECT("tcStylr_img")
126 USE IN SELECT("tcSColr_img")
127 *USE IN SELECT("zzxthvnc_local")
128 USE IN SELECT("tcHeader")
129
130
131
132 .oLog.LogEntry("")
133 .oLog.LogResult(llRetVal)
134 .oLog.CloseLog()
135
136 *--- TechRec 1065902 27-Dec-2012 jisingh Added Style ===
137 IF llRetVal AND this.lNoDataFound
138 .LogEntry("No Data to process.")
139 ELSE
140 IF llRetVal
141 .cMessage = "Threadvine Style Interface process " + ;
142 IIF(llRetVal, "successfully completed.", "failed.") + CRLF + CRLF + ;
143 ALLTRIM(STR(.nProcessStyles)) + " Styles were processed." + CRLF + ;
144 ALLTRIM(STR(.nSuccessStyles)) + " Styles were valid." + CRLF + ;
145 ALLTRIM(STR(.nSkippedStyles)) + " Styles were skipped (no update required)." + CRLF + ;
146 ALLTRIM(STR(.nFailedStyles)) + " Styles failed."+ CRLF + ;
147 ALLTRIM(STR(.nImageSuccess)) + " Images Uploaded." + CRLF + ;
148 ALLTRIM(STR(.nImageFailed )) + " Images Failed." + CRLF + CRLF + ;
149 "Please see History Log for more details..."
150
151 .LogEntry(ALLTRIM(STR(.nProcessStyles)) + " Styles were processed.")
152 .LogEntry(ALLTRIM(STR(.nSuccessStyles)) + " Styles were valid.")
153 .LogEntry(ALLTRIM(STR(.nSkippedStyles)) + " Styles were skipped (no update required).")
154 .LogEntry(ALLTRIM(STR(.nFailedStyles)) + " Styles failed.")
155 .LogEntry(ALLTRIM(STR(.nImageSuccess)) + " Images Uploaded.")
156 .LogEntry(ALLTRIM(STR(.nImageFailed )) + " Images Failed.")
157
158
159 IF llRetVal AND .nSuccessStyles = 0 AND .nSkippedStyles > 0
160 this.lNoDataFound = .T.
161 ENDIF
162 ENDIF
163
164 ENDIF
165
166 ENDWITH
167
168 v_SysUnLock(lcSyslockTablePath+"SYSLOCK", This.cJobID, goEnv.cCompany)
169 RETURN llRetVal
170 ENDFUNC
171
172 *============================================================
173 *============================================================
174
175 FUNCTION CheckParameters
176 * TR 1077292 - Added , lcFullDate, loDate
177 LOCAL llRetVal, lcHeading, lcFullDate, loDate, lcCatgDestFilter , lnCatgCnt , llCalcDate
178
179 llRetVal = true
180
181 WITH THIS
182 lcHeading = "Checking Parameters..."
183 .UpdateThermoCaption(lcHeading)
184 .LogMajorStage(lcHeading)
185 .oLog.LogEntry("")
186
187 .AdvanceThermo(10)
188 .AdvanceThermoTotal(10)
189 INKEY(.1,"H")
190
191*!* .cThreadvineURL = allt(goEnv.sv('Threadvine_INTERFACE_URL',''))
192*!* llRetVal = llRetVal AND NOT EMPTY(.cThreadvineURL)
193*!* .cmessage = iif(!llRetVal,'Please check System Parameter Threadvine_INTERFACE_URL','')
194
195*!* .cThreadvineUserID = allt(goEnv.sv('Threadvine_INTERFACE_USERID',''))
196*!* llRetVal = llRetVal AND NOT EMPTY(.cThreadvineUserID)
197*!* .cmessage = iif(!llRetVal and empty(.cmessage),'Please check System Parameter Threadvine_INTERFACE_USERID','')
198
199*!* .cThreadvinePasswd = allt(goEnv.sv('Threadvine_INTERFACE_PASSWORD',''))
200*!* llRetVal = llRetVal AND NOT EMPTY(.cThreadvinePasswd)
201*!* .cmessage = iif(!llRetVal and empty(.cmessage),'Please check System Parameter Threadvine_INTERFACE_PASSWORD','')
202
203
204 llRetVal = llRetVal AND .getThreadvineControl()
205
206
207 IF llRetVal
208 *- Last Mod Param BRO
209 .tLast_Mod = .getParamBro("ZZXTVINT_LASTMOD")
210 IF NOT EMPTY(.tLast_Mod)
211 .tLast_Mod = THIS.GetFilterString(.tLast_Mod ,"D.LAST_MOD","D")
212 IF NOT EMPTY(.tLast_Mod)
213 .tLast_Mod = "((" + .tLast_Mod + "))"
214 ENDIF
215 ELSE
216 .tLast_Mod = ""
217 ENDIF
218
219 * TR 1079445 Filtering for catalog destination. Since there are 6-9 fields, create a dynamic filter for them.
220 lcCatgDestFilter = .getParamBro("PRM_CATG_DEST")
221 IF NOT EMPTY(lcCatgDestFilter)
222 * get count of category destination fields on zzxscolr
223 lnCatgCnt = v_sqlprep(;
224 "SELECT count(*) as cnt FROM zzzddcol where tabname = 'zzxscolr' and colname like 'catg_dest%' and flgdrop <> 'Y'",,"cnt")
225
226
227 .cCatgDestFilter = " AND ( "
228 FOR x = 1 TO lnCatgCnt
229 .cCatgDestFilter = .cCatgDestFilter + ;
230 THIS.GetFilterString(lcCatgDestFilter ,"D.CATG_DEST" + TRANSFORM(x),"C")
231
232 IF x < lnCatgCnt
233 .cCatgDestFilter = .cCatgDestFilter + " OR "
234 ENDIF
235
236 ENDFOR
237 .cCatgDestFilter = .cCatgDestFilter + ")"
238
239 ELSE
240 .cCatgDestFilter = ""
241 ENDIF
242 .LogEntry("Category Destination Filter: " + lcCatgDestFilter)
243 *=== TR 1079445 Filtering for catalog destination. Since there are 6-9 fields, create a dynamic filter for them.
244
245 *- Last Mod Param BRO
246 *--- TR 1077292
247 lcFullDate = .getParamBro("PRM_FULL_AVAL_DATE")
248 IF EMPTY(lcFullDate)
249 .cFullAvailDate = ""
250 ELSE
251
252 SET PROCEDURE TO clsmacro addi
253
254
255
256 IF "@SEASON" $ lcFullDate
257 =v_sqlexec("select DATEDIFF(dd,getdate(),COALESCE((start_date),getdate())) as days_to_start,season FROM zzxseasr where vs_hide = 'T' and ACTIVE_OK = 'Y' order by start_date desc","tcFullAvailSeason")
258
259 IF USED("tcFullAvailSeason") AND RECCOUNT("tcFullAvailSeason") > 0
260 lcFullDate = STRTRAN(lcFullDate,"@SEASON","@TODAY+" + TRANSFORM(tcFullAvailSeason.days_to_start) + "D ")
261 .cFullAvailSeason = ALLTRIM(tcFullAvailSeason.season)
262 .LogEntry("Full Available Season: " + .cFullAvailSeason)
263
264 loDate = CREATEOBJECT("DynamicDate",lcFullDate)
265 IF TYPE("loDate") = "O"
266 lcFullDate = loDate.GetDynamicDate(loDate.cFilterValue,.T.)
267 ENDIF
268 loDate = null
269
270 .cFullAvailDate = CTOD(lcFullDate)
271 IF EMPTY(.cFullAvailDate )
272 .LogEntry("Full Availability Date Not Set.")
273 ELSE
274 .LogEntry("Full Availability Date: " + TRANSFORM(.cFullAvailDate ))
275 ENDIF
276
277
278 ELSE
279 .LogEntry("Could find full available season, not sending")
280
281 ENDIF
282
283 ELSE
284
285
286 loDate = CREATEOBJECT("DynamicDate",lcFullDate)
287 IF TYPE("loDate") = "O"
288 lcFullDate = loDate.GetDynamicDate(loDate.cFilterValue,.T.)
289 ENDIF
290 loDate = null
291
292
293 .cFullAvailDate = CTOD(lcFullDate)
294 IF EMPTY(.cFullAvailDate )
295 .LogEntry("Full Availability Date Not Set.")
296 ELSE
297 .LogEntry("Full Availability Date: " + TRANSFORM(.cFullAvailDate ))
298 ENDIF
299
300 ENDIF
301
302 ENDIF
303 *=== TR 1077292
304
305 * Order Type Exclusion for OTS
306 .cOrderTypeExclusion = .getParamBro("ZZXTVINT_ORDTYPE")
307 IF NOT EMPTY(.cOrderTypeExclusion)
308 .cOrderTypeExclusion = THIS.GetFilterString(.cOrderTypeExclusion,"H.ORD_TYPE","C")
309 IF NOT EMPTY(.cOrderTypeExclusion)
310 .cOrderTypeExclusion = " AND ((" + .cOrderTypeExclusion + "))"
311 ENDIF
312 ELSE
313 .cOrderTypeExclusion = ""
314 ENDIF
315
316 IF EMPTY(.cOrderTypeExclusion )
317 .LogEntry("OTS Exclude Order Type is empty.")
318 ELSE
319 .LogEntry("OTS Exclude Order Type Filter: " + .cOrderTypeExclusion )
320 ENDIF
321
322
323 *- Delete Flag Param BRO
324 .cDeleteFlag = .getParamBro("ZZXTVINT_DELETE")
325 .cDeleteFlag = IIF(EMPTY(.cDeleteFlag),"N",.cDeleteFlag)
326
327 IF llRetVal
328 IF LEN(.cDeleteFlag)>1 OR !INLIST(.cDeleteFlag,'Y','N')
329 .cMessage = iLong("ZZXTVINT_DELETE") + " can only be Y-Yes or N-No."
330 ENDIF
331 ENDIF
332
333 .lOnlyAvailability = INLIST(.getParamBro("ZZXTVINT_AVAILONLY"),"Y","P")
334 .lOnlyAvailability_includePrice = (.getParamBro("ZZXTVINT_AVAILONLY")=="P")
335 .lSendTranslations = INLIST(.getParamBro("ZZXTVINT_AVAILONLY"),"T")
336
337 IF .lOnlyAvailability
338 .LogEntry("Sending Only Availability Updates.")
339 ENDIF
340 IF .lOnlyAvailability_includePrice
341 .cHistoryRecType = "X"
342 .LogEntry(".... And Price")
343 ENDIF
344 IF .lSendTranslations
345 .LogEntry("Sending Translations")
346 .cHistoryRecType = "T"
347 ENDIF
348
349 .lSendDeliveryDatesFromUDF = goEnv.SV("Threadvine_DELIVERY_DATES_SEND_UDF","N") == "Y"
350
351 IF .lSendDeliveryDatesFromUDF
352 .LogEntry("Delivery Dates- Sending UDF 1 & 2.")
353 ELSE
354 .LogEntry("Delivery Dates- Sending Earliest Start and Latest End.")
355 ENDIF
356
357 .cImageHandling = .getParamBro("ZZXTVINT_IMGUPL")
358 IF EMPTY(.cImageHandling)
359 .cImageHandling = "Y"
360 ELSE
361 IF NOT INLIST(.cImageHandling,"Y","I","N")
362 .LogEntry("Invalid Image Handling BRO. Must be Y, I, or N.")
363 .cMessage = "Invalid Image Handling BRO. Must be Y, I, or N."
364 llRetVal = .F.
365 ENDIF
366 ENDIF
367
368
369 IF llRetVal
370 lcLookDesc = vl_xlookp("TV_IMAGE_UPLOAD","lk_desc",,.cImageHandling)
371 .LogEntry("Imaging Handling set to: " + .cImageHandling + " - " + ALLTRIM(lcLookDesc ))
372 ENDIF
373
374 llRetVal = llRetVal AND .GetTagHandling()
375
376 .nMinimumUnits = VAL(.getParamBro("ZZXTVINT_MINUNIT"))
377 IF .nMinimumUnits <= 0
378 .LogEntry("No minimum availability set.")
379 ELSE
380 .LogEntry("Minimum Availability to Send Availability: " + TRANSFORM(.nMinimumUnits))
381 ENDIF
382
383 .nMaximumUnits = VAL(.getParamBro("ZZXTVINT_MAXUNIT"))
384 IF .nMaximumUnits <= 0
385 .LogEntry("No maximum availability set.")
386 ELSE
387 .LogEntry("Maximum Availability to Send Availability: " + TRANSFORM(.nMaximumUnits))
388 ENDIF
389
390
391 .lPriceLevelSupport = (goEnv.SV("THREADVINE_SUPPORT_PRICE_LEVELS","N")=="Y")
392 IF .lPriceLevelSupport
393 .LogEntry("Parameter THREADVINE_SUPPORT_PRICE_LEVELS = Y. Price Rules will be sent for each style.")
394 ELSE
395 .LogEntry("Parameter THREADVINE_SUPPORT_PRICE_LEVELS <> Y. Legacy Price logic will be used.")
396 ENDIF
397
398
399 *- Last Mod Param BRO
400 .cLocationFilter = .getParamBro("LOCATION")
401 IF NOT EMPTY(.cLocationFilter )
402 .LogEntry("Location Filter: " + .cLocationFilter )
403 .cLocationFilter = THIS.GetFilterString(.cLocationFilter ,"A.LOCATION","C")
404 IF NOT EMPTY(.cLocationFilter )
405 .cLocationFilter = " AND ((" + .cLocationFilter + "))"
406 ENDIF
407 ELSE
408 .cLocationFilter = ""
409 ENDIF
410
411
412 *- Last Mod Param BRO
413 .cAvailabilityFilter = .getParamBro("PRM_TV_OTS_FILTER")
414 IF NOT EMPTY(.cAvailabilityFilter )
415 .LogEntry("Availability Filter: " + .cAvailabilityFilter)
416 .cAvailabilityFilter = THIS.GetFilterString(.cAvailabilityFilter , "SUM(total_qty * mult)","I")
417 IF NOT EMPTY(.cAvailabilityFilter )
418 .cAvailabilityFilter = " ((" + .cAvailabilityFilter + "))"
419 ENDIF
420 ELSE
421 .cAvailabilityFilter = ""
422 ENDIF
423
424 .nWipDueDateOffset = VAL(.getParamBro("PRM_TV_WIP_DATE_DAYS"))
425 IF .nWipDueDateOffset <= 0
426 .LogEntry("No WIP Due Date offset Set")
427 ELSE
428 .LogEntry("WIP Due Date Offset Set: " + TRANSFORM(.nWipDueDateOffset))
429 ENDIF
430
431 .lArchiveStyles = (.getParamBro("PRM_TV_ARCHIVE_STYLE")=="Y")
432 IF .lArchiveStyles
433 .LogEntry("PRM_TV_ARCHIVE_STYLE = Y: Marking all selected styles as archived.")
434 ENDIF
435
436 .lSplitPacks = (.getParamBro("PRM_TV_SPLIT_PACKS")=="Y")
437 IF .lSplitPacks
438 .LogEntry("PRM_TV_SPLIT_PACKS = Y: Prepacks and Range Styles will be their own product.")
439 ENDIF
440
441
442 .cTVRegionCode = .getParamBro("PRM_TV_REGION_CODE")
443 .LogEntry("Seasonal Collection Region Code: " + .cTVRegionCode)
444
445
446 ENDIF
447
448 .oLog.LogResult(llRetVal, lcHeading)
449 .oLog.LogEntry("")
450 ENDWITH
451
452 RETURN llRetVal
453 ENDFUNC
454
455 *============================================================
456 *============================================================
457
458 PROCEDURE GetStyleRecords
459 LPARAMETER pSQLFilter
460
461 LOCAL llRetVal, lcSQLFilterString, lcHeading , lcDivAvail, lcDueDateSQL, lcAvailTmp, lcAvailTmp2, lcSizeRoll, lcTotalRoll, lcSizeClear
462
463 *--- TR 1081856 13-10-2014 VKK
464 LOCAL lcDelvTableJoin
465 lcDelvTableJoin = ""
466 *=== TR 1081856 13-10-2014 VKK
467
468 WITH THIS
469 lcHeading = "Selecting Style and Color records..."
470 .UpdateThermoCaption(lcHeading)
471 .LogMajorStage(lcHeading)
472 .oLog.LogEntry("")
473
474 .AdvanceThermo(20)
475 .AdvanceThermoTotal(20)
476 INKEY(.1,"H")
477
478 llRetVal = true
479 .cTempTbl = "#" + getuniquefilename()
480
481 lcSQLFilterString = IIF(EMPTY(pSQLFilter), "", " And " + pSQLFilter)
482
483
484 .LogEntry("Filter: " + pSQLFilter)
485
486
487 *--- TR 1081856 13-10-2014 VKK
488 lcSQLFilterString = STRTRAN(lcSQLFilterString , "DLV.DETAIL_DELV_CODES", "DLV.DELV_CODE")
489 .cProcessFilter = lcSQLFilterString
490
491 IF "DLV.DELV_CODE" $ UPPER(lcSQLFilterString )
492 lcDelvTableJoin = " Join zzxscdlv dlv on dlv.fkey = d.Pkey "
493 ENDIF
494 *=== TR 1081856 13-10-2014 VKK
495
496 *--- 09/12/11 FGCjr
497 *lcSQLFilterString = STRTRAN(UPPER(lcSQLFilterString),"D.","")
498
499*!* lcSQLString = " SELECT DISTINCT h.pkey, h.division, h.style INTO " + .cTempTbl + ;
500*!* " FROM zzxstylr H " + ;
501*!* " JOIN zzxscolr D ON h.pkey = d.fkey " + ;
502*!* " WHERE 1=1 " + .tLast_Mod + lcSQLFilterString
503 *--- TR 1065429 5-NOV-12 Venuk. Added IIF( EMPTY(.tLast_Mod),""," AND "+.tLast_Mod") in place of tLast_Mod
504
505 TEXT TO lcSQLString NOSHOW TEXTMERGE
506 SELECT DISTINCT D.fkey, D.pkey, D.division, D.style, D.color_code, D.lbl_code, D.dimension, D.season, D.delv_code
507 ,CASE WHEN rh.pkey is not null then 'R'
508 WHEN ppk.pkey is not null then 'P'
509 ELSE 'O' END as style_pack_type
510 ,RTRIM(d.division)
511 + '-' + RTRIM(d.style)
512 + '-' + RTRIM(d.color_code)
513 + CASE WHEN d.lbl_code > '' THEN '-' + RTRIM(d.lbl_code) ELSE '' END
514 + CASE WHEN d.dimension > '' AND (ppk.pkey is not null or rh.pkey is not null) THEN <<IIF(.lSplitPacks," '-' + RTRIM(d.dimension) ","''")>> ELSE '' END
515 AS cProductNum
516 ,COALESCE(ppk.pkey,0) as PPk_Key
517 , d.discontinued
518 INTO <<.cTempTbl>>
519 FROM zzxscolr d
520 LEFT JOIN zzxppakh ppk
521 ON d.division = ppk.division
522 AND d.size_code = ppk.size_code
523 AND d.dimension = ppk.ppk_code
524 AND d.ppack_ok = 'Y'
525 LEFT JOIN zzxrangh rh
526 ON d.division = rh.division
527 AND d.style = rh.rng_style
528 AND d.color_code = rh.rng_color
529 AND d.lbl_code = rh.rng_lbl
530 AND d.dimension = rh.rng_pack
531 <<lcDelvTableJoin>>
532 WHERE 1=1
533 <<IIF( EMPTY(.tLast_Mod),""," AND "+.tLast_Mod) + .cCatgDestFilter + lcSQLFilterString>>
534 ENDTEXT
535 llRetVal = llRetVal AND v_sqlexec(lcSQLString)
536
537
538
539
540
541 *lcDivAvail = "#" + Getuniquefilename()
542 *llRetVal = llRetVal AND v_sqlexec(;
543 "SELECT division,avail_source " + ;
544 " INTO " + lcDivAvail + ;
545 " FROM zzxthvnc WHERE division > '' " + ;
546 " UNION ALL " + ;
547 " SELECT zzxdivsr.division,avail_source FROM zzxdivsr,zzxthvnc " + ;
548 " where zzxthvnc.division = '' and zzxdivsr.division NOT IN (SELECT division from zzxthvnc WHERE division > '') ")
549
550* cLocationFilter = ""
551* cAvailabilityFilter = ""
552* nWipDueDateOffset = 0
553
554 lcSQLString = ""
555 lcDueDateSQL = " CASE WHEN DATEADD(dd," + SQLFormatNum(.nWipDueDateOffset) + ",due_date) <= "+sqlformatts(DATE())+" THEN "+sqlformatts(DATE())+;
556 " ELSE DATEADD(dd," + SQLFormatNum(.nWipDueDateOffset) + ",due_date) END "
557
558 lcAvailTmp = "#" + getuniquefilename()
559 lcAvailTmp2 = "#" + getuniquefilename()
560 IF ALLTRIM(.oControl.avail_source) == "ATS"
561 lcSQLString = " SELECT a.division,a.style,a.color_code,a.lbl_code,a.dimension," + ;
562 " CASE WHEN a.rec_type = 'I' THEN " + sqlformatts(DATE()) + " ELSE " + lcDueDateSQL + " END as due_date,9999 as rec_num, " + ;
563 v_selectsizes("SUM(size","_qty)",,,"size","_qty") +;
564 " ,SUM(a.total_qty) as total_qty " + ;
565 ", 1 as mult " + ;
566 " INTO " + lcAvailTmp + ;
567 " FROM zzaalbal a " + ;
568 " JOIN " + .cTempTbl + " t ON a.division = t.division AND a.style = t.style " + ;
569 " AND a.color_code=t.color_code and a.lbl_code=t.lbl_code and a.dimension=t.dimension " + ;
570 " WHERE 1=1 " + .cLocationFilter + ;
571 " GROUP BY a.division,a.style,a.color_code,a.lbl_code,a.dimension," + ;
572 " CASE WHEN a.rec_type = 'I' THEN " + sqlformatts(DATE()) + " ELSE " + lcDueDateSQL + " END "
573 ENDIF
574
575 IF LEFT(.oControl.avail_source,3) == "OTS"
576 lcSQLString = "SELECT a.division,a.style,a.color_code,a.lbl_code,a.dimension," + ;
577 sqlformatts(DATE()) + " as due_date, 9999 as rec_num, " + ;
578 v_selectsizes("SUM(size","_qty * r.mult)",,,"size","_qty") + ;
579 " ,SUM(a.total_qty * r.mult) as total_qty " + ;
580 ", 1 as mult " + ;
581 " INTO " + lcAvailTmp + ;
582 " FROM zzxssumh a " + ;
583 " JOIN " + .cTempTbl + " t ON a.division = t.division AND a.style = t.style " + ;
584 " AND a.color_code=t.color_code and a.lbl_code=t.lbl_code and a.dimension=t.dimension " + ;
585 " JOIN ( SELECT rec_type,mult from zzxavqoh UNION ALL SELECT 'OP',-1 UNION ALL SELECT 'DM',-1 ) r ON a.rec_type = r.rec_type " + ;
586 " WHERE 1=1 " + .cLocationFilter + ;
587 " GROUP BY a.division,a.style,a.color_code,a.lbl_code,a.dimension "
588 ENDIF
589
590
591 IF INLIST(ALLTRIM(.oControl.avail_source),"TIMEPHASED")
592 lcSQLString = "SELECT a.division,a.style,a.color_code,a.lbl_code,a.dimension," + ;
593 sqlformatts(DATE()) + " as due_date, 9999 as rec_num, " + ;
594 v_selectsizes("SUM(size","_qty * r.mult)",,,"size","_qty") + ;
595 " ,SUM(a.total_qty * r.mult) as total_qty " + ;
596 ", 1 as mult " + ;
597 " INTO " + lcAvailTmp + ;
598 " FROM zzxssumh a " + ;
599 " JOIN " + .cTempTbl + " t ON a.division = t.division AND a.style = t.style " + ;
600 " AND a.color_code=t.color_code and a.lbl_code=t.lbl_code and a.dimension=t.dimension " + ;
601 " JOIN ( SELECT rec_type,mult from zzxavqoh ) r ON a.rec_type = r.rec_type " + ;
602 " WHERE 1=1 " + .cLocationFilter + ;
603 " GROUP BY a.division,a.style,a.color_code,a.lbl_code,a.dimension "
604 ENDIF
605
606 IF INLIST(ALLTRIM(.oControl.avail_source),"OTS WIP","TIMEPHASED")
607 lcSQLString = lcSQLString + ;
608 " UNION ALL " + ;
609 " SELECT a.division,a.style,a.color_code,a.lbl_code,a.dimension," + ;
610 lcDueDateSQL + " as due_date,9999 as rec_num, " + ;
611 v_selectsizes("SUM(a.wip","_qty)",,,"size","_qty") +;
612 " ,SUM(a.wip_total) as total_qty " + ;
613 " ,1 as mult " + ;
614 " FROM zzcordrd a " + ;
615 " JOIN " + .cTempTbl + " t ON a.division = t.division AND a.style = t.style " + ;
616 " AND a.color_code=t.color_code and a.lbl_code=t.lbl_code and a.dimension=t.dimension " + ;
617 " WHERE a.wip_ok = 'Y' and a.wip_total > 0 " + STRTRAN(UPPER(.cLocationFilter),"LOCATION","SHIPTO") + ;
618 " GROUP BY a.division,a.style,a.color_code,a.lbl_code,a.dimension," + ;
619 lcDueDateSQL
620 ENDIF
621
622 IF LEFT(.oControl.avail_source,3) == "OTS" AND NOT EMPTY(.cOrderTypeExclusion)
623 * Adding back to OTS specific order types.
624 lcSQLString = lcSQLString + ;
625 " UNION ALL " + ;
626 " SELECT a.division,a.style,a.color_code,a.lbl_code,a.dimension," + ;
627 sqlformatts(DATE()) + " as due_date, 9999 as rec_num, " + ;
628 v_selectsizes("SUM(size","_qty)",,,"size","_qty") + ;
629 " ,SUM(a.total_qty) as total_qty " + ;
630 ", 1 as mult " + ;
631 " FROM zzoordrd a " + ;
632 " JOIN zzoordrh h ON a.fkey = h.pkey " + ;
633 " JOIN " + .cTempTbl + " t ON a.division = t.division AND a.style = t.style " + ;
634 " AND a.color_code=t.color_code and a.lbl_code=t.lbl_code and a.dimension=t.dimension " + ;
635 " WHERE h.ord_status = 'O' " + .cOrderTypeExclusion + .cLocationFilter + ;
636 " GROUP BY a.division,a.style,a.color_code,a.lbl_code,a.dimension "
637 ENDIF
638
639 IF INLIST(ALLTRIM(.oControl.avail_source),"WLG-AVAIL")
640
641 * Start with Available QOH
642 lcSQLString = "SELECT a.division,a.style,a.color_code,a.lbl_code,a.dimension," + ;
643 sqlformatts(DATE()) + " as due_date, 9999 as rec_num, " + ;
644 v_selectsizes("SUM(size","_qty * r.mult)",,,"size","_qty") + ;
645 " ,SUM(a.total_qty * r.mult) as total_qty " + ;
646 ", 1 as mult " + ;
647 " INTO " + lcAvailTmp + ;
648 " FROM zzxssumh a " + ;
649 " JOIN " + .cTempTbl + " t ON a.division = t.division AND a.style = t.style " + ;
650 " AND a.color_code=t.color_code and a.lbl_code=t.lbl_code and a.dimension=t.dimension " + ;
651 " JOIN ( SELECT rec_type,mult from zzxavqoh ) r ON a.rec_type = r.rec_type " + ;
652 " WHERE 1=1 " + .cLocationFilter + ;
653 " GROUP BY a.division,a.style,a.color_code,a.lbl_code,a.dimension "
654
655 * Add in production allocation results
656 lcSQLString = lcSQLString + " UNION ALL " + ;
657 " SELECT a.division,a.style,a.color_code,a.lbl_code,a.dimension," + ;
658 " CASE WHEN a.rec_type = 'I' THEN " + sqlformatts(DATE()) + " ELSE " + lcDueDateSQL + " END as due_date,9999 as rec_num, " + ;
659 v_selectsizes("SUM(size","_qty)",,,"size","_qty") +;
660 " ,SUM(a.total_qty) as total_qty " + ;
661 ", 1 as mult " + ;
662 " FROM zzaalbal a " + ;
663 " JOIN " + .cTempTbl + " t ON a.division = t.division AND a.style = t.style " + ;
664 " AND a.color_code=t.color_code and a.lbl_code=t.lbl_code and a.dimension=t.dimension " + ;
665 " WHERE a.rec_type = 'P' " + .cLocationFilter + ;
666 " GROUP BY a.division,a.style,a.color_code,a.lbl_code,a.dimension," + ;
667 " CASE WHEN a.rec_type = 'I' THEN " + sqlformatts(DATE()) + " ELSE " + lcDueDateSQL + " END "
668
669
670 * reduce by open orders allocated to inventory and are confirmed
671 * For ATP enhancement, removed shpst_code check
672 lcSQLString = lcSQLString + " UNION ALL " + ;
673 " SELECT a.division,a.style,a.color_code,a.lbl_code,a.dimension," + ;
674 " " + sqlformatts(DATE()) + " as due_date,9999 as rec_num, " + ;
675 v_selectsizes("SUM(size","_qty)",,,"size","_qty") +;
676 " ,SUM(a.total_qty) as total_qty " + ;
677 ", -1 as mult " + ;
678 " FROM zzaavalr a " + ;
679 " JOIN " + .cTempTbl + " t ON a.division = t.division AND a.style = t.style " + ;
680 " AND a.color_code=t.color_code and a.lbl_code=t.lbl_code and a.dimension=t.dimension " + ;
681 " WHERE a.ALLO_SOURCE = 'INVT' " + .cLocationFilter + ;
682 " AND EXISTS ( SELECT 1 FROM zzoordrh h where h.ord_num = a.ord_num) " + ;
683 " GROUP BY a.division,a.style,a.color_code,a.lbl_code,a.dimension "
684
685
686 lcSQLString = lcSQLString + " UNION ALL " + ;
687 " SELECT a.division,a.style,a.color_code,a.lbl_code,a.dimension," + ;
688 " " + sqlformatts(DATE()) + " as due_date,9999 as rec_num, " + ;
689 v_selectsizes("SUM(a.size","_qty)",,,"size","_qty") +;
690 " ,SUM(a.total_qty) as total_qty " + ;
691 ", -1 as mult " + ;
692 " FROM zzoordrd a " + ;
693 " JOIN " + .cTempTbl + " t ON a.division = t.division AND a.style = t.style " + ;
694 " AND a.color_code=t.color_code and a.lbl_code=t.lbl_code and a.dimension=t.dimension " + ;
695 " JOIN zzoordrh h ON a.fkey = h.pkey " + ;
696 " JOIN zzotyper o ON h.ord_type = o.ord_type " + ;
697 " WHERE h.ord_status = 'O' " + .cLocationFilter + ;
698 " AND NOT EXISTS ( SELECT 1 FROM zzaavalr h where h.ord_num = a.ord_num) " + ;
699 " AND o.atp_incl_unalloc = 'Y' " + ;
700 " GROUP BY a.division,a.style,a.color_code,a.lbl_code,a.dimension "
701
702
703
704 ENDIF
705
706
707
708
709 IF NOT EMPTY(.cFullAvailDate)
710
711
712 lcSeasonAvailFilter = ""
713 IF NOT EMPTY(.cFullAvailSeason)
714 lcSeasonAvailFilter = ;
715 " JOIN (SELECT distinct division,style,color_code,lbl_code,dimension FROM ZZXSCLLR " + ;
716 " WHERE season = " + SQLFormatChar(.cFullAvailSeason) + " AND " + ;
717 " (int_region_code = " + SQLFormatChar(.cTVRegionCode) + " OR int_region_code = '') " + ;
718 " ) s " + ;
719 " ON a.division = s.division " + ;
720 " AND a.style = s.style " + ;
721 " AND a.color_code = s.color_code " + ;
722 " AND a.lbl_code = s.lbl_code " + ;
723 " AND a.dimension = s.dimension " + ;
724 " WHERE a.discontinued <> 'Y' "
725
726
727 ENDIF
728
729 lcSizeStr = ""
730 FOR x = 1 TO goENv.nMaxBuckets
731 lcSizeStr = lcSizeStr + ", 999999 as size" + PADL(x,2,"0") + "_qty "
732 ENDFOR
733
734 lcSQLString = lcSQLString + ;
735 " UNION ALL " + ;
736 " SELECT a.division,a.style,a.color_code,a.lbl_code,a.dimension," + ;
737 sqlformatts(.cFullAvailDate) + " as due_date, 9999 as rec_num " + ;
738 lcSizeStr + ;
739 ", 999 as total_qty " + ;
740 ", 1 as mult " + ;
741 " FROM " + .cTempTbl + " a " + ;
742 lcSeasonAvailFilter
743
744 ENDIF
745
746 llRetVal = llRetVal AND v_sqlexec(lcSQLString)
747
748 * If user set a full available at a date earlier than a production order, delete the production order.
749 IF NOT EMPTY(.cFullAvailDate)
750 llRetVal = llRetVal AND v_sqlexec(;
751 "DELETE FROM " + lcAvailTmp + " WHERE due_date > " + SQLFormatTS(.cFullAvailDate))
752 ENDIF
753
754 LOCAL lcOrderTmp
755 lcOrderTmp = "#" + getuniquefilename()
756
757 IF ALLTRIM(.oControl.avail_source) == "TIMEPHASED"
758 * Swapped start date with end date.
759 llRetVal = llRetVal AND v_sqlexec(;
760 " SELECT a.division,a.style,a.color_code,a.lbl_code,a.dimension," + ;
761 STRTRAN(UPPER(lcDueDateSQL),"DUE_DATE","A.END_DATE") + " as due_date,9999 as rec_num, " + ;
762 v_selectsizes("SUM(size","_qty)",,,"size","_qty") + ;
763 " ,SUM(a.total_qty) as total_qty " + ;
764 ", -1 as mult " + ;
765 " INTO " + lcOrderTmp + ;
766 " FROM zzoordrd a " + ;
767 " JOIN zzoordrh h ON a.fkey = h.pkey " + ;
768 " JOIN " + .cTempTbl + " t ON a.division = t.division AND a.style = t.style " + ;
769 " AND a.color_code=t.color_code and a.lbl_code=t.lbl_code and a.dimension=t.dimension " + ;
770 " WHERE h.ord_status = 'O' " + .cLocationFilter + ;
771 " GROUP BY a.division,a.style,a.color_code,a.lbl_code,a.dimension, " + ;
772 STRTRAN(UPPER(lcDueDateSQL),"DUE_DATE","A.END_DATE"))
773
774 llRetVal = llRetVal AND v_sqlexec(;
775 "UPDATE o set due_date = COALESCE(p.due_date,o.due_date,'') " + ;
776 " from "+lcOrderTmp+" o " + ;
777 " left join "+lcAvailTmp+" p " + ;
778 " on p.Due_Date <= o.due_date " + ;
779 " AND o.DIVISION = p.DIVISION " + ;
780 " AND o.style = p.style " + ;
781 " AND o.color_code = p.color_code " + ;
782 " AND o.LBL_CODE = p.LBL_CODE " + ;
783 " AND o.DIMENSION = p.DIMENSION " + ;
784 " where not exists ( " + ;
785 " select NULL " + ;
786 " from "+lcAvailTmp+" pb " + ;
787 " where pb.Due_Date <= o.due_date " + ;
788 " and pb.Due_Date > p.Due_Date " + ;
789 " AND pb.DIVISION = p.DIVISION " + ;
790 " AND pb.style = p.style " + ;
791 " AND pb.color_code = p.color_code " + ;
792 " AND pb.LBL_CODE = p.LBL_CODE " + ;
793 " AND pb.DIMENSION = p.DIMENSION ) ")
794
795
796 llRetVal = llRetVal AND v_sqlexec("insert into "+lcAvailTmp+" select * FROM "+lcOrderTmp)
797
798 ENDIF
799
800
801 * For order type filtering, just add another
802 lcAvailJoin = ""
803 IF NOT EMPTY(.cAvailabilityFilter)
804 lcAvailJoin = ;
805 " JOIN (SELECT division,style,color_code,lbl_code,dimension " + ;
806 " FROM " + lcAvailTmp + " a " + ;
807 " GROUP BY division,style,color_code,lbl_code,dimension " + ;
808 " HAVING " + .cAvailabilityFilter + ") a " + ;
809 " ON x.division = a.division " + ;
810 " and x.style = a.style " + ;
811 " and x.color_code = a.color_code " + ;
812 " and x.lbl_code = a.lbl_code " + ;
813 " and x.dimension = a.dimension "
814
815 ENDIF
816
817 lcSQLString = " SELECT x.division,x.style,x.color_code,x.lbl_code,x.dimension,x.due_date,9999 as rec_num, " + ;
818 v_selectsizes("SUM(x.size","_qty * mult)",,,"size","_qty") + ;
819 ",SUM(x.total_qty * x.mult) as total_qty " + ;
820 ",ROW_NUMBER() OVER(PARTITION BY division,style,color_code,lbl_code,dimension ORDER BY due_date) as sort_seq " + ;
821 " INTO " + lcAvailTmp2 + ;
822 " FROM " + lcAvailTmp + " x " + ;
823 lcAvailJoin + ;
824 " GROUP BY x.division,x.style,x.color_code,x.lbl_code,x.dimension,x.due_date "
825 llRetVal = llRetVal AND v_sqlexec(lcSQLString)
826
827 * Roll backward negative qty
828 lcSizeUpdate = ""
829 lcSizeRoll = ""
830 lcTotalRoll = "0"
831 lcSizeClear = ""
832
833 FOR x = 1 TO goEnv.nMaxBuckets
834 lcSizeUpdate = lcSizeUpdate + "size"+ PADL(x,2,"0") + "_qty = t4.size" + + PADL(x,2,"0") + "_qty,"
835 lcSizeRoll = lcSizeRoll + "SUM(CASE WHEN t1.sort_seq = @max_seq AND t1.size" + PADL(x,2,"0") + "_qty > 0 THEN 0 ELSE t1.size" + PADL(x,2,"0") + "_qty end) as size" + PADL(x,2,"0") + "_qty,"
836 lcTotalRoll = lcTotalRoll + "+size"+ PADL(x,2,"0") + "_qty"
837 lcSizeClear = lcSizeClear + "size"+ PADL(x,2,"0") + "_qty = CASE WHEN size"+ PADL(x,2,"0") + "_qty < 0 THEN 0 ELSE size"+ PADL(x,2,"0") + "_qty END,"
838 ENDFOR
839
840 lcSizeUpdate = LEFT(lcSizeUpdate,LEN(lcSizeUpdate)-1)
841 lcSizeRoll = LEFT(lcSizeRoll,LEN(lcSizeRoll)-1)
842 lcSizeClear = LEFT(lcSizeClear ,LEN(lcSizeClear )-1)
843
844 TEXT TO lcSQLString TEXTMERGE NOSHOW
845
846 DECLARE @max_seq INT
847 SELECT @max_seq = MAX(sort_seq) FROM <<lcAvailTmp2>>
848
849 WHILE @max_seq > 1 -- no need to roll 1 backwards, its already the first record
850 begin
851
852 UPDATE t3
853 SET <<lcSizeUpdate>>
854 FROM <<lcAvailTmp2>> t3
855 JOIN (
856
857 SELECT t1.division,t1.style,t1.color_code,t1.lbl_code,t1.dimension, MIN(t1.sort_seq) as sort_seq, MIN(due_date) as due_date,
858 <<lcSizeRoll>>
859 FROM <<lcAvailTmp2>> t1
860 WHERE (sort_seq = @max_seq ) OR (sort_seq = (@max_seq-1)) -- filter for current seq and line before since we are combining
861 GROUP BY t1.division,t1.style,t1.color_code,t1.lbl_code,t1.dimension
862 ) t4
863
864 ON t3.division = t4.division
865 AND t3.style = t4.style
866 AND t3.color_code = t4.color_code
867 AND t3.lbl_code = t4.lbl_code
868 AND t3.dimension = t4.dimension
869 AND t3.sort_seq = t4.sort_seq
870
871 update t
872 set <<lcSizeClear>>
873 FROM <<lcAvailTmp2>> t where sort_seq = @max_seq
874
875 set @max_seq = @max_seq - 1
876 end
877 ENDTEXT
878 llRetVal = llRetVal AND v_sqlexec(lcSQLString)
879
880 llRetVal = llRetVal AND v_sqlexec("UPDATE " + lcAvailTmp2 + " set total_qty = " + lcTotalRoll )
881
882
883 * Roll forward qty
884 lcTotalQtyAnyPos = "("
885 lcTotalQtyAll = "("
886 lcSizeUpdate = " total_qty = t4.total_qty"
887 FOR x = 1 TO goEnv.nMaxBuckets
888 lcSizeUpdate = lcSizeUpdate + ",size"+ PADL(x,2,"0") + "_qty = t4.size" + + PADL(x,2,"0") + "_qty"
889
890
891 lcTotalQtyAll = lcTotalQtyAll + "t2.size"+ PADL(x,2,"0") + "_qty <= 0 " + IIF(x=goEnv.nMaxBuckets,")"," AND ")
892 lcTotalQtyAnyPos = lcTotalQtyAnyPos + "size"+ PADL(x,2,"0") + "_qty > 0 " + IIF(x=goEnv.nMaxBuckets,")"," OR ")
893 ENDFOR
894
895
896
897
898
899 TEXT TO lcSQLString TEXTMERGE NOSHOW
900 UPDATE t3
901 SET <<lcSizeUpdate>>
902 FROM <<lcAvailTmp2>> t3
903 JOIN (
904 SELECT t1.division,t1.style,t1.color_code,t1.lbl_code,t1.dimension, t1.due_date, SUM(t2.total_qty) as total_qty,
905 <<v_selectsizes("SUM(t2.size","_qty)",,,"size","_qty")>>
906 FROM <<lcAvailTmp2>> t1
907 JOIN <<lcAvailTmp2>> t2
908 ON t1.division = t2.division
909 AND t1.style = t2.style
910 AND t1.color_code = t2.color_code
911 AND t1.lbl_code = t2.lbl_code
912 AND t1.dimension = t2.dimension
913 AND t1.due_date >= t2.due_date
914 GROUP BY t1.division,t1.style,t1.color_code,t1.lbl_code,t1.dimension, t1.due_date
915 ) t4
916 ON t3.division = t4.division
917 AND t3.style = t4.style
918 AND t3.color_code = t4.color_code
919 AND t3.lbl_code = t4.lbl_code
920 AND t3.dimension = t4.dimension
921 AND t3.due_date = t4.due_date
922 ENDTEXT
923 llRetVal = llRetVal AND v_sqlexec(lcSQLString)
924
925
926 * Min mult check. availability must respect that after the roll forward
927 * Only update records with min mult > 1
928 LOCAL lcMinMultUpdate
929 lcMinMultUpdate = ""
930 FOR x = 1 TO goEnv.nMaxBuckets
931 lcMinMultUpdate= lcMinMultUpdate+ "size"+ PADL(x,2,"0") + "_qty = t.size" + PADL(x,2,"0") + "_qty - " + ;
932 " CASE WHEN t.size" + PADL(x,2,"0") + "_qty < s.min_multiple THEN t.size" + PADL(x,2,"0") + "_qty " + ;
933 " WHEN t.size" + PADL(x,2,"0") + "_qty % s.min_multiple > 0 THEN t.size" + PADL(x,2,"0") + "_qty % s.min_multiple " + ;
934 " ELSE 0 END " + IIF(x=goEnv.nMaxBuckets,"",",")
935 ENDFOR
936
937 TEXT TO lcSQLString TEXTMERGE NOSHOW
938 UPDATE t
939 SET <<lcMinMultUpdate>>
940 FROM <<lcAvailTmp2>> t
941 JOIN zzxscolr s
942 ON t.division = s.division
943 AND t.style = s.style
944 AND t.color_code = s.color_code
945 AND t.lbl_code = s.lbl_code
946 AND t.dimension = s.dimension
947 WHERE s.min_multiple > 1
948
949 ENDTEXT
950 llRetVal = llRetVal AND v_sqlexec(lcSQLString)
951
952
953 * Delete any items with a 0 or negative OTS that has no R1, R2, SA, RV, WP, NW cut&sold records
954 TEXT TO lcSQLString TEXTMERGE NOSHOW
955 DELETE x
956 FROM <<lcAvailTmp2>> x
957 LEFT JOIN
958 (
959 SELECT s.division, s.style, s.color_code, s.lbl_code, s.dimension
960 FROM <<lcAvailTmp2>> z
961 JOIN zzxssumh s
962 ON z.division = s.division
963 AND z.style = s.style
964 AND z.color_code = s.color_code
965 AND z.lbl_code = s.lbl_code
966 AND z.dimension = s.dimension
967 WHERE s.rec_type IN ('R1','R2','SA','RV','WP','NW')
968 GROUP BY s.division, s.style, s.color_code, s.lbl_code, s.dimension
969 ) t
970 ON t.division = x.division
971 AND t.style = x.style
972 AND t.color_code = x.color_code
973 AND t.lbl_code = x.lbl_code
974 AND t.dimension = x.dimension
975 WHERE <<STRTRAN(lcTotalQtyAll,"t2","x")>> AND t.style is null
976 ENDTEXT
977 llRetVal = llRetVal AND v_sqlexec(lcSQLString)
978
979 *if a due date for a sku goes negative (all buckets <= 0), eliminate all rows before it.
980 TEXT to lcSQLString TextMerge NoShow
981 DELETE t2 FROM
982 (
983 select division,style,color_code,lbl_code,dimension, MAX(due_date) as due_date
984 FROM <<lcAvailTmp2>> t2
985 WHERE <<lcTotalQtyAll>>
986 GROUP BY division,style,color_code,lbl_code,dimension
987 ) t1
988 JOIN <<lcAvailTmp2>> t2
989 ON t1.division = t2.division
990 AND t1.style = t2.style
991 AND t1.color_code = t2.color_code
992 AND t1.lbl_code = t2.lbl_code
993 AND t1.dimension = t2.dimension
994 WHERE t2.due_date < t1.due_date
995 ENDTEXT
996 llRetVal = llRetVal AND v_sqlexec(lcSQLString)
997
998
999 * Delete any availability entries for a sku that are <= 0 but have a later date that is > 0. This corrects the availability immediate problem
1000 TEXT TO lcSQLString TEXTMERGE NOSHOW
1001 DELETE t2 FROM
1002 (
1003 select division,style,color_code,lbl_code,dimension, MIN(due_date) as due_date
1004 FROM <<lcAvailTmp2>>
1005 WHERE <<lcTotalQtyAnyPos>>
1006 GROUP BY division,style,color_code,lbl_code,dimension
1007 ) t1
1008 JOIN <<lcAvailTmp2>> t2
1009 ON t1.division = t2.division
1010 AND t1.style = t2.style
1011 AND t1.color_code = t2.color_code
1012 AND t1.lbl_code = t2.lbl_code
1013 AND t1.dimension = t2.dimension
1014 WHERE <<lcTotalQtyAll>>
1015 ENDTEXT
1016 llRetVal = llRetVal AND v_sqlexec(lcSQLString)
1017
1018 llRetVal = llRetVal AND v_sqlexec("SELECT * FROM " + lcAvailTmp2 + " Order by division,style,color_code,lbl_code,dimension,due_date " ,"tcAval")
1019
1020 IF NOT EMPTY(.cAvailabilityFilter)
1021 * using an availability filter. remove any styles
1022 llRetVal = llRetVal AND v_sqlexec(;
1023 "DELETE x " + ;
1024 " from " + .cTempTbl + " x " + ;
1025 " LEFT JOIN " + lcAvailTmp2 + " a " + ;
1026 " ON x.division = a.division " + ;
1027 " and x.style = a.style " + ;
1028 " and x.color_code = a.color_code " + ;
1029 " and x.lbl_code = a.lbl_code " + ;
1030 " and x.dimension = a.dimension " + ;
1031 " WHERE a.dimension is null " )
1032
1033 ENDIF
1034
1035 v_sqlexecnoerror("DROP TABLE "+ lcAvailTmp2)
1036 v_sqlexecnoerror("DROP TABLE "+ lcAvailTmp)
1037
1038 lcGrpJoin = ""
1039 lcGrpFlds = ""
1040
1041 FOR x = 1 TO 13
1042 lcAlias = "gp" + TRANSFORM(x)
1043 lcGrpJoin = lcGrpJoin + ;
1044 " LEFT JOIN zzxpgrpr " + lcAlias + ;
1045 " ON " + lcAlias + ".division = st.division " + ;
1046 " AND " + lcAlias + ".group_code = st.group_code" + TRANSFORM(x) + ;
1047 " AND " + lcAlias + ".group_type = " + SQLFormatChar(CHR(x+64))
1048
1049
1050 lcGrpFlds = lcGrpFlds + " ,COALESCE(" + lcAlias + ".group_name,'') as group_name" + TRANSFORM(x)
1051 ENDFOR
1052
1053 * Get Style Header Cursor
1054 TEXT TO lcSQLString NOSHOW TEXTMERGE
1055 SELECT st.*
1056 ,COALESCE(x.type_desc,'') as style_type_desc
1057 ,COALESCE(ctg.catg_desc,'') as catg_desc
1058 ,COALESCE(r.roy_name,'') as roy_name
1059 ,COALESCE(r.roy_cls_name,'') as roy_cls_name
1060 ,COALESCE(sz.size_name,'') as size_name
1061 ,COALESCE(cont.cont_desc,'') as cont_desc
1062 ,COALESCE(cntry.cnty_name,'') as cnty_name
1063 <<lcGrpFlds>>
1064 FROM zzxstylr st
1065 JOIN <<.cTempTbl>> t
1066 ON st.pkey = t.fkey
1067 LEFT JOIN zzxsttyr x ON st.style_type = x.style_type
1068 LEFT JOIN zzecatgr ctg ON st.catg_num = ctg.catg_num
1069 LEFT JOIN zvtoproyaltyname r
1070 ON st.royalty = r.royalty
1071 AND st.roy_cls = r.roy_cls
1072 LEFT JOIN zzxsizer sz ON st.division = sz.division AND st.size_code = sz.size_code
1073 LEFT JOIN zzxcontr cont ON st.cont_code = cont.cont_code
1074 LEFT JOIN zzxcntyr cntry ON st.country = cntry.country
1075 <<lcGrpJoin>>
1076 ENDTEXT
1077 llRetVal = llRetVal AND v_sqlexec(lcSQLString,"tcStylr")
1078
1079
1080
1081 LOCAL lcFieldList
1082 TEXT to lcSQLString NOSHOW
1083 DECLARE @cFieldList Varchar(max);
1084 set @cFieldList = '';
1085 select @cFieldList = @cFieldList + 'sc.' + rtrim(colname) + ','
1086 FROM zzzddstb s
1087 JOIN zzzddtab t on s.stbname = t.stbname
1088 JOIN zzzddcol c ON t.tabname = c.tabname
1089 WHERE c.flgdrop <> 'Y' and c.dvdfnc = '' AND s.stbname = 'zzxscolr'
1090 AND c.colname NOT LIKE 'ALLOW%' AND c.colname NOT LIKE 'CALL%';
1091
1092 SELECT LEFT(@cFieldList,LEN(@cFieldList)-1) as cFieldList
1093 ENDTEXT
1094 lcFieldList = v_sqlprep(lcSQLString,,"cFieldList")
1095
1096
1097 * Get Style Detail Cursor
1098 TEXT TO lcSQLString NOSHOW TEXTMERGE
1099 SELECT <<lcFieldList>>
1100 , COALESCE(d.line_seq,9999) as dim_seq
1101 , COALESCE(d.pkey,9999) as dim_pkey
1102 ,CAST(ROW_NUMBER() OVER(
1103 PARTITION BY t.cProductNum
1104 ORDER BY COALESCE(d.line_seq,9999),COALESCE(d.pkey,9999),sc.dimension
1105 ) as INT) as sort_seq
1106 ,COALESCE(ppk.ppk_desc,'') ppk_desc
1107 ,t.cProductNum
1108 FROM zzxscolr sc
1109 JOIN <<.cTempTbl>> t ON sc.pkey = t.pkey
1110 LEFT JOIN zzxdimer d
1111 ON sc.division = d.division
1112 AND sc.size_code = d.size_code
1113 AND sc.dimension = d.dimension
1114 LEFT JOIN zzxppakh ppk
1115 ON sc.division = ppk.division
1116 AND sc.size_code = ppk.size_code
1117 AND sc.dimension = ppk.ppk_code
1118 AND sc.ppack_ok = 'Y'
1119 ENDTEXT
1120 llRetVal = llRetVal AND v_sqlexec(lcSQLString,"tcScolr")
1121
1122
1123 TEXT to lcSQLString NOSHOW
1124 DECLARE @cFieldList Varchar(max);
1125 set @cFieldList = '';
1126 select @cFieldList = @cFieldList + 'sc.' + rtrim(colname) + ','
1127 FROM zzzddstb s
1128 JOIN zzzddtab t on s.stbname = t.stbname
1129 JOIN zzzddcol c ON t.tabname = c.tabname
1130 WHERE c.flgdrop <> 'Y' and c.dvdfnc = '' AND s.stbname = 'zzxscolr'
1131 AND (c.colname LIKE 'ALLOW%' OR c.colname LIKE 'CALL%') ;
1132
1133 SELECT LEFT(@cFieldList,LEN(@cFieldList)-1) as cFieldList
1134 ENDTEXT
1135 lcFieldList = v_sqlprep(lcSQLString,,"cFieldList")
1136
1137 * Get Style Detail Cursor
1138 TEXT TO lcSQLString NOSHOW TEXTMERGE
1139 SELECT sc.pkey,<<lcFieldList>>
1140 FROM zzxscolr sc
1141 JOIN <<.cTempTbl>> t ON sc.pkey = t.pkey
1142 ENDTEXT
1143 llRetVal = llRetVal AND v_sqlexec(lcSQLString,"tcScolr_sizes")
1144
1145
1146 IF !llRetVal
1147 .cMessage = MSG_FILTER_ERROR + CRLF + MSG_TRY_AGAIN_NOW
1148 llRetVal = .F.
1149 ENDIF
1150
1151 .oLog.LogResult(llRetVal, lcHeading)
1152 .oLog.LogEntry("")
1153 ENDWITH
1154
1155 RETURN llRetVal
1156 ENDPROC
1157
1158 *============================================================
1159 *============================================================
1160
1161 PROCEDURE PrepareLocalCursors
1162 LOCAL llRetVal, lnSelect, lcHeading
1163
1164 WITH THIS
1165 lnSelect = SELECT()
1166 llRetVal = true
1167
1168 lcHeading = "Preparing local cursors..."
1169 .UpdateThermoCaption(lcHeading)
1170 .LogMajorStage(lcHeading)
1171 .oLog.LogEntry("")
1172
1173 .AdvanceThermo(30)
1174 .AdvanceThermoTotal(30)
1175 INKEY(.01,"H")
1176
1177
1178 llRetVal = llRetVal AND v_sqlexec(;
1179 "SELECT * FROM zzxcolrr c ","tcColrr")
1180
1181
1182
1183 llRetVal = llRetVal AND .LoadLocalRegionLangTemp()
1184
1185 llRetVal = llRetVal AND v_sqlexec("SELECT * FROM zzxsizer","tcSizer")
1186 llRetVal = llRetVal AND v_sqlexec(;
1187 "SELECT *,CAST(RIGHT(rtrim(field_name),2) as INT) as sizebucket " + ;
1188 " ,CAST(ROW_NUMBER() OVER (PARTITION by fkey,field_name ORDER BY int_region_code) as INT) as sort_seq " + ;
1189 " FROM zzxtrnsl where shellid = 'ZZXSIZER'","tcSizer_trns")
1190
1191
1192
1193 *--- FGCjr
1194 llRetVal = llRetVal AND v_sqlexec("SELECT * FROM zzodlvcd","tcDlvcd")
1195 llRetVal = llRetVal AND v_sqlexec("SELECT * FROM zzxcontr","tcContr")
1196 llRetVal = llRetVal AND v_sqlexec("SELECT * FROM zzxseasr","zzxseasr_local")
1197 llRetVal = llRetVal AND v_sqlexec("SELECT * FROM zzxdivsr","zzxdivsr_local")
1198 llRetVal = llRetVal AND v_sqlexec("SELECT * FROM zzxpgrpr","zzxpgrpr_local")
1199 llRetVal = llRetVal AND v_sqlexec("SELECT * FROM zzxpgtyr","zzxpgtyr_local")
1200 llRetVal = llRetVal AND v_sqlexec("SELECT i.* FROM zzxexfvr i JOIN " + .cTempTbl + " t ON i.fkey = t.fkey WHERE i.contextid = 'ZZXSTYLR' AND prime_image = 'Y'","tcStylr_img")
1201 llRetVal = llRetVal AND v_sqlexec("SELECT i.* FROM zzxexfvr i JOIN " + .cTempTbl + " t ON i.fkey = t.pkey WHERE i.contextid = 'ZZXSCOLR' AND prime_image = 'Y'","tcScolr_img")
1202
1203 llRetVal = llRetVal AND v_sqlexec("SELECT * FROM zzxhsnor","zzxhsnor_local")
1204 *llRetVal = llRetVal AND v_sqlexec("SELECT * FROM zzxthvnc","zzxthvnc_local")
1205
1206 llRetVal = llRetVal AND v_sqlexec("SELECT * FROM zzxclblr","zzxclblr_local")
1207
1208 llRetVal = llRetVal AND v_sqlexec(;
1209 "SELECT * FROM zzxthvnd where detail_type = 'WHSL.PRICE' AND curr_code > '' AND fkey = " + SQLFormatNum(.oControl.pkey),"zzxthvnd_whsl_local")
1210
1211
1212 llRetVal = llRetVal AND v_sqlexec(;
1213 "SELECT t.*,l.localeid " + ;
1214 " ,CAST(ROW_NUMBER() OVER (PARTITION by t.fkey ORDER BY l.localeid) as INT) as sort_seq " + ;
1215 " FROM zzxtrnsl t JOIN " + .cLangLocaleTmp + " l ON t.langcode = l.langcode where shellid = 'ZZXCOLRR' and field_name = 'Color_Name'","tcColrr_trns")
1216
1217 llRetVal = llRetVal AND v_sqlexec(;
1218 "SELECT t.*,l.localeid " + ;
1219 " ,CAST(ROW_NUMBER() OVER (PARTITION by t.fkey ORDER BY l.localeid) as INT) as sort_seq " + ;
1220 " ,m.division,m.group_type, m.group_code " + ;
1221 " FROM zzxtrnsl t JOIN " + .cLangLocaleTmp + " l ON t.langcode = l.langcode " + ;
1222 " JOIN ZZXPGRPR m ON m.pkey = t.fkey " + ;
1223 " where shellid = 'ZZXPGRPR' AND field_name = 'Group_name' "+;
1224 " ORDER BY m.division,m.group_type, m.group_code, l.localeid ","tcMerch_trns")
1225
1226
1227 llRetVal = llRetVal AND v_sqlexec(;
1228 "SELECT i.*,l.localeid " + ;
1229 " ,CAST(ROW_NUMBER() OVER (PARTITION by i.fkey ORDER BY l.localeid) as INT) as sort_seq " + ;
1230 " FROM zzxtrnsl i JOIN (SELECT distinct fkey FROM " + .cTempTbl + ") t ON i.fkey = t.fkey " + ;
1231 " JOIN " + .cLangLocaleTmp + " l ON i.langcode = l.langcode " + ;
1232 " WHERE i.table_name = 'ZZXSTYLR' AND i.field_name = 'STYLE_NAME' and i.langcode > '' ",'zzxtrnsl_sty_name_local')
1233
1234 llRetVal = llRetVal AND v_sqlexec(;
1235 "SELECT i.*,l.localeid " + ;
1236 " ,CAST(ROW_NUMBER() OVER (PARTITION by i.fkey ORDER BY l.localeid) as INT) as sort_seq " + ;
1237 " FROM zzxtrnsl i JOIN (SELECT distinct fkey FROM " + .cTempTbl + ") t ON i.fkey = t.fkey " + ;
1238 " JOIN " + .cLangLocaleTmp + " l ON i.langcode = l.langcode " + ;
1239 " WHERE i.table_name = 'ZZXSTYLR' AND i.field_name = 'STYLE_DESC' and i.langcode > '' ",'zzxtrnsl_sty_desc_local')
1240
1241 llRetVal = llRetVal AND v_sqlexec(;
1242 "select n.notes,t.THREADVINE_DESC,s.fkey,t.LANGCODE, l.localeid " + ;
1243 " ,CAST(ROW_NUMBER() OVER (PARTITION by s.fkey ORDER BY t.threadvine_desc,l.localeid) as INT) as sort_seq " + ;
1244 " FROM (SELECT distinct fkey FROM " + .cTempTbl + ")s " + ;
1245 " JOIN sysnotes n ON s.fkey = n.fkey and n.table_name = 'ZZXSTYLR' " + ;
1246 " JOIN zzxnotyp t on n.note_type = t.note_type " + ;
1247 " JOIN " + .cLangLocaleTmp + " l ON t.langcode = l.langcode " + ;
1248 " where t.THREADVINE_DESC > '' AND LEFT(t.THREADVINE_DESC,3) <> '[A]' ", 'tcStyleDescriptors')
1249
1250
1251 *llRetVal = llRetVal AND v_sqlexec(;
1252 "select n.notes,RTRIM(LTRIM(SUBSTRING(t.THREADVINE_DESC,4,100))) as THREADVINE_DESC,s.fkey,t.LANGCODE, l.localeid " + ;
1253 " ,CAST(ROW_NUMBER() OVER (PARTITION by s.fkey ORDER BY t.threadvine_desc,l.localeid) as INT) as sort_seq " + ;
1254 " FROM (SELECT distinct fkey FROM " + .cTempTbl + ")s " + ;
1255 " JOIN sysnotes n ON s.fkey = n.fkey and n.table_name = 'ZZXSTYLR' " + ;
1256 " JOIN zzxnotyp t on n.note_type = t.note_type " + ;
1257 " JOIN " + .cLangLocaleTmp + " l ON t.langcode = l.langcode " + ;
1258 " where t.THREADVINE_DESC > '' AND LEFT(t.THREADVINE_DESC,3) = '[A]' ", 'tcStyleTagNotes')
1259
1260 * Filtering for just en_US. do not translate this.
1261 llRetVal = llRetVal AND v_sqlexec(;
1262 "select n.notes,RTRIM(LTRIM(SUBSTRING(t.THREADVINE_DESC,4,100))) as THREADVINE_DESC,s.fkey,t.LANGCODE, l.localeid " + ;
1263 " ,CAST(ROW_NUMBER() OVER (PARTITION by s.fkey ORDER BY t.threadvine_desc,l.localeid) as INT) as sort_seq " + ;
1264 " FROM (SELECT distinct fkey FROM " + .cTempTbl + ")s " + ;
1265 " JOIN sysnotes n ON s.fkey = n.fkey and n.table_name = 'ZZXSTYLR' " + ;
1266 " JOIN zzxnotyp t on n.note_type = t.note_type " + ;
1267 " JOIN " + .cLangLocaleTmp + " l ON t.langcode = l.langcode " + ;
1268 " where t.THREADVINE_DESC > '' AND LEFT(t.THREADVINE_DESC,3) = '[A]' AND l.localeid = 'en_US' ", 'tcStyleTagNotes')
1269
1270
1271
1272
1273 IF llRetVal
1274 SELECT distinct curr_code FROM zzxthvnd_whsl_local INTO CURSOR tcDistinctCurr
1275
1276 SELECT zzxthvnd_whsl_local
1277 INDEX on curr_code+division TAG csku
1278
1279
1280 ENDIF
1281
1282 llRetVal = llRetVal AND .getHistoryCursor()
1283
1284 llRetVal = llRetVal AND v_sqlexec(" SELECT upc.division,upc.style,upc.color_code,upc.lbl_code,upc.dimension," + ;
1285 " upc.sizebucket,upc.size_desc,upc.upc_num,upc.chk_digit,upc.ean as upc,upc.upclbl_code " + ;
1286 " FROM zveupcnr upc " + ;
1287 " JOIN " + .cTempTbl + " t ON upc.division = t.division and upc.style = t.style ",'tcUpcNr')
1288
1289 TEXT TO lcSQL NOSHOW TEXTMERGE
1290 SELECT
1291 CAST(ROW_NUMBER() OVER(PARTITION BY cProductNum
1292 ORDER BY int_region_code DESC,curr_code) as INT) as sort_seq
1293 ,*
1294 FROM (
1295 SELECT
1296 i.curr_code,i.int_region_code,i.division,i.style,i.color_code,i.lbl_code,cProductNum
1297 ,MAX(i.a_price) as a_price
1298 ,MAX(i.b_price) as b_price
1299 ,MAX(i.c_price) as c_price
1300 ,MAX(i.d_price) as d_price
1301 ,MAX(i.e_price) as e_price
1302 ,MAX(i.ret_price) as ret_price
1303 FROM zzxintsd i
1304 JOIN <<.cTempTbl>> t
1305 ON i.division = t.division
1306 AND i.style = t.style
1307 AND i.color_code = t.color_code
1308 AND i.lbl_code = t.lbl_code
1309 AND i.dimension = t.dimension
1310 GROUP BY i.curr_code,i.int_region_code,i.division,i.style,i.color_code,i.lbl_code,cProductNum
1311 ) h
1312 ENDTEXT
1313 llRetVal = llRetVal AND v_sqlexec(lcSQL,"tcIntSP")
1314
1315 *--- TR 1096152 07-Jun-2016 BNarayanan added filter p.active_ok = 'Y'
1316 TEXT TO lcSQL NOSHOW TEXTMERGE
1317 select rtrim(p.season) + rtrim(country) + RTRIM(ord_type) + rtrim(COALESCE(r.int_region_code,'')) + rtrim(customer) as rule_name
1318 , t.cProductNum
1319 ,p.a_price,p.b_price,p.c_price,p.d_price,p.e_price, p.curr_code
1320 FROM zzxdtprd p
1321 LEFT JOIN zzxintrd r ON p.price_rgn_code = r.RGN_TYPE_VAL AND r.RGN_TYPE_CODE = 'PRICE'
1322 JOIN <<.cTempTbl>> t
1323 ON p.division = t.division
1324 AND p.style = t.style
1325 WHERE p.COLOR_CODE = '' and p.active_ok = 'Y'
1326 UNION ALL
1327 select 'WHSL' as rule_name
1328 , t.cProductNum
1329 , MAX(p.a_price) as a_price,-1 b_price,-1 c_price,-1 d_price,-1 e_price, p.curr_code
1330 FROM zzxdtprd p
1331 JOIN zzxseasr s ON p.season = s.season
1332 JOIN <<.cTempTbl>> t
1333 ON p.division = t.division
1334 AND p.style = t.style
1335 JOIN (
1336 select
1337 t.cProductNum, p.curr_code, MAX(s.start_date) as start_date
1338 FROM zzxdtprd p
1339 JOIN zzxseasr s ON p.season = s.season
1340 LEFT JOIN zzxintrd r ON p.price_rgn_code = r.RGN_TYPE_VAL AND r.RGN_TYPE_CODE = 'PRICE'
1341 JOIN <<.cTempTbl>> t
1342 ON p.division = t.division
1343 AND p.style = t.style
1344 WHERE p.COLOR_CODE = '' AND p.price_rgn_code <> 'PEF' and p.active_ok = 'Y'
1345 GROUP BY t.cProductNum, p.curr_code
1346 ) x ON x.cProductNum = t.cProductNum
1347 AND x.curr_code = p.curr_code
1348 AND x.start_date = s.start_date
1349 WHERE p.color_code = '' and p.price_rgn_code <> 'PEF' and p.active_ok = 'Y'
1350 GROUP BY t.cProductNum, p.curr_code
1351 UNION ALL
1352 select 'MSRP' as rule_name
1353 , t.cProductNum
1354 , MAX(p.ret_price) as a_price,-1 b_price,-1 c_price,-1 d_price,-1 e_price, p.curr_code
1355 FROM zzxdtprd p
1356 JOIN zzxseasr s ON p.season = s.season
1357 JOIN <<.cTempTbl>> t
1358 ON p.division = t.division
1359 AND p.style = t.style
1360 JOIN (
1361 select
1362 t.cProductNum, p.curr_code, MAX(s.start_date) as start_date
1363 FROM zzxdtprd p
1364 JOIN zzxseasr s ON p.season = s.season
1365 LEFT JOIN zzxintrd r ON p.price_rgn_code = r.RGN_TYPE_VAL AND r.RGN_TYPE_CODE = 'PRICE'
1366 JOIN <<.cTempTbl>> t
1367 ON p.division = t.division
1368 AND p.style = t.style
1369 WHERE p.COLOR_CODE = '' AND p.price_rgn_code <> 'PEF' and p.active_ok = 'Y'
1370 GROUP BY t.cProductNum, p.curr_code
1371 ) x ON x.cProductNum = t.cProductNum
1372 AND x.curr_code = p.curr_code
1373 AND x.start_date = s.start_date
1374 WHERE p.color_code = '' and p.price_rgn_code <> 'PEF' and p.active_ok = 'Y'
1375 GROUP BY t.cProductNum, p.curr_code
1376 ENDTEXT
1377 llRetVal = llRetVal AND v_sqlexec(lcSQL,"tcSpPrice")
1378 IF llRetVal
1379 SELECT tcSpPrice
1380 INDEX on ALLTRIM(cProductNum) TAG cProd
1381 ENDIF
1382
1383
1384
1385*!* llRetVal = llRetVal AND v_sqlexec(;
1386*!* " SELECT a.division,a.style,a.color_code,a.lbl_code,a.dimension," + ;
1387*!* " CASE WHEN due_date <= "+sqlformatts(DATE())+" THEN "+sqlformatts(DATE())+" ELSE due_date END as due_date,9999 as rec_num, " + ;
1388*!* v_selectsizes("SUM(size","_qty)",,,"size","_qty") +;
1389*!* " FROM zzaalbal a " + ;
1390*!* " JOIN " + .cTempTbl + " t ON a.division = t.division AND a.style = t.style " + ;
1391*!* " GROUP BY a.division,a.style,a.color_code,a.lbl_code,a.dimension," + ;
1392*!* " CASE WHEN due_date <= "+sqlformatts(DATE())+" THEN "+sqlformatts(DATE())+" ELSE due_date END" + ;
1393*!* " ORDER BY 1,2,3,4,5,6 ","tcAval")
1394
1395 if llRetVal
1396 LOCAL lcPackStr,lcSzStr
1397 lcSizeStr = ""
1398 lcPackStr = ""
1399 lcSzStr = ""
1400 FOR x = 1 TO goEnv.nMaxBuckets
1401 IF x > 1
1402 lcSizeStr = lcSizeStr + "+"
1403 ENDIF
1404 lcSizeStr = lcSizeStr + " MAX(IIF(call" + PADL(x,2,"0") + " = 'Y' AND allow" + PADL(x,2,"0") + " = 'Y','Y','N')) "
1405 lcPackStr= lcPackStr+ IIF(!EMPTY(lcPackStr),"+","")+ " Pack"+PADL(x,2,"0")+"_qty*Sz"+PADL(x,2,"0")
1406 lcSzStr = lcSzStr+ IIF(!EMPTY(lcSzStr),"","")+ " when "+PADL(x,2,"0")+" then z.size"+PADL(x,2,"0")
1407 ENDFOR
1408
1409 IF .lSplitPacks
1410 TEXT TO lcSQL TEXTMERGE NOSHOW
1411 SELECT d.ppk_key
1412 ,d.pkey
1413 , d.division
1414 , d.Style
1415 , case when ph.MulClLb_Ok < 'Y' or pd.Color_Code = 'ALL' then d.Color_Code else pd.Color_Code end as Color_Code
1416 , case when ph.MulClLb_Ok < 'Y' or pd.Lbl_Code = 'ALL' then d.Lbl_Code else pd.Lbl_Code end as Lbl_Code
1417 , pd.Dimension
1418 , s.style_name
1419 , zzxColrR.Color_Name
1420 , zzxColrR.Color_Desc
1421 , case b.Size_Num
1422 <<lcSzStr>>
1423 end as Size_Desc
1424 , b.Size_Num
1425 , Coalesce(u.ean, Space(14)) as UPC
1426 , <<lcPackStr>> as pack_qty
1427 ,d.cProductNum
1428 FROM <<.cTempTbl>> d
1429 JOIN zzxPPakH ph
1430 on ph.PKey = d.PPk_Key
1431 join zzxPPakD pd
1432 on pd.FKey = d.PPk_Key
1433 cross join zzxBuckt b
1434 join zzxSColR sc
1435 on sc.Style = d.Style
1436 and sc.Color_Code = case when ph.MulClLb_Ok < 'Y' or pd.Color_Code = 'ALL' then d.Color_Code else pd.Color_Code end
1437 and sc.Lbl_Code = case when ph.MulClLb_Ok < 'Y' or pd.Lbl_Code = 'ALL' then d.Lbl_Code else pd.Lbl_Code end
1438 and sc.Dimension = d.Dimension
1439 and sc.Division = d.Division
1440 join zzxStylR s
1441 on s.PKey = sc.FKey
1442 join zzxColrR
1443 on zzxColrR.Color_Code = case when ph.MulClLb_Ok < 'Y' or pd.Color_Code = 'ALL' then d.Color_Code else pd.Color_Code end
1444 join zzxSizer z
1445 on z.Division = s.Division
1446 and z.Size_Code = s.Size_Code
1447 left join zveUPCNR u
1448 on u.Style = d.Style
1449 and u.Color_Code = zzxColrR.Color_Code
1450 and u.Lbl_Code = case when ph.MulClLb_Ok < 'Y' or pd.Lbl_Code = 'ALL' then d.Lbl_Code else pd.Lbl_Code end
1451 and u.Dimension = pd.Dimension
1452 and u.Division = d.Division
1453 and u.SizeBucket = b.Size_Num
1454 WHERE <<lcPackStr>> > 0
1455 ORDER BY d.pkey
1456 ,case when ph.MulClLb_Ok < 'Y' or pd.Color_Code = 'ALL' then d.Color_Code else pd.Color_Code end
1457 ,case when ph.MulClLb_Ok < 'Y' or pd.Lbl_Code = 'ALL' then d.Lbl_Code else pd.Lbl_Code end
1458 ,pd.Dimension
1459 ,b.Size_Num
1460 ENDTEXT
1461
1462 ENDIF
1463 llRetVal = llRetVal AND v_sqlexec(lcSQL,"tcPPK_1")
1464
1465 llRetVal = llRetVal AND .BuildPrePackContent()
1466
1467
1468
1469
1470 lcHdrSQL = ;
1471 " SELECT division,style,color_code,lbl_code,cProductNum,MAX(std_cost) as std_cost " + ;
1472 " ,MAX(a_price) as a_price, MAX(ret_price) as ret_price " + ;
1473 " ,MAX(cont_code) as cont_code, MAX(nrf_color) as nrf_color " + ;
1474 " ,MIN(active_ok) as active_ok " + ;
1475 " , sc.fkey as fkey " + ;
1476 " , " + lcSizeStr + " as sizes " + ;
1477 " , IIF(COUNT(distinct ATC('Y',sizes))==COUNT(*) AND MAX(OCCURS('Y',sizes)) <= 1 AND MAX(LEN(ALLT(sc.ppk_desc))) = 0,'Y','N') as single_size " + ; && if customer has non pack sizes vertical this will roll up horizontal
1478 " , ICASE(THIS.lSplitPacks AND MAX(LEN(ALLT(sc.ppk_desc))) > 0, ' (Prepack ' + RTRIM(MAX(sc.dimension)) + ')' , ' ') as cDimColor " + ;
1479 " FROM tcScolr sc " + ;
1480 " JOIN tcScolr_sizes sz ON sc.pkey = sz.pkey " + ;
1481 " INTO CURSOR tcHeader1 READWRITE " + ;
1482 " GROUP BY sc.division,sc.style,sc.color_code,sc.lbl_code,sc.fkey,sc.cProductNum ORDER BY cProductNum"
1483 &lcHdrSQL
1484
1485
1486
1487
1488 TEXT TO lcSQL NOSHOW TEXTMERGE
1489 SELECT DISTINCT t.pkey,s.season
1490 FROM <<.cTempTbl>> t
1491 JOIN ZZXSCLLR s
1492 ON s.division = t.division
1493 AND s.style = t.style
1494 AND s.color_code = t.color_code
1495 AND s.lbl_code = t.lbl_code
1496 AND s.dimension = t.dimension
1497 WHERE s.int_region_code = <<SQLFormatChar(.cTVRegionCode)>> OR s.int_region_code = ''
1498 ENDTEXT
1499 llRetVal = llRetVal AND v_sqlexec(lcSQL,"tcMultiSeason")
1500
1501
1502 * Gore's min max style. translated. not sure yet how to merge to base
1503 TEXT TO lcSQL NOSHOW TEXTMERGE
1504 select s.pkey as fkey,h.division,h.style,t2.localeid, t2.langcode
1505 ,CASE WHEN MAX(h.min_bk) = MAX(h.max_bk) THEN
1506 MAX(case when h.min_bk = t1.sizebucket THEN t1.TRANSLATED_CONTENT ELSE '' END)
1507 ELSE
1508 RTRIM(MAX(case when h.min_bk = t1.sizebucket THEN t1.TRANSLATED_CONTENT ELSE '' END)) + '-' + RTRIM(MAX(case when h.max_bk = t1.sizebucket THEN t1.TRANSLATED_CONTENT ELSE '' END))
1509 END as TRANSLATED_CONTENT
1510 ,CAST(ROW_NUMBER() OVER (PARTITION by s.pkey ORDER BY t2.localeid) as INT) as sort_seq
1511 FROM
1512 (
1513 select u1.division,u1.style,MIN(u1.sizebucket) as min_bk, MAX(u1.sizebucket) as max_bk
1514 FROM zveupcnr u1
1515 JOIN (SELECT distinct division,style FROM <<.cTempTbl>>) t3 ON u1.division = t3.division AND u1.style = t3.style
1516 GROUP by u1.division,u1.style
1517 ) h
1518 JOIN zzxstylr s
1519 ON h.division = s.division
1520 AND h.style = s.style
1521 JOIN zzxsizer sz
1522 ON s.DIVISION = sz.division
1523 AND s.size_code = sz.size_code
1524 JOIN (
1525 SELECT *,CAST(RIGHT(rtrim(field_name),2) as INT) as sizebucket FROM zzxtrnsl where shellid = 'ZZXSIZER'
1526 ) t1
1527 ON t1.FKEY = sz.pkey
1528 AND t1.sizebucket IN (h.min_bk,h.max_bk) -- not sure about this
1529
1530 JOIN <<.cLangLocaleTmp>> t2 ON t1.INT_REGION_CODE = t2.size_region
1531 GROUP BY s.pkey,h.DIVISION,h.STYLE,t2.localeid, t2.langcode
1532 ENDTEXT
1533 llRetVal = llRetVal AND v_sqlexec(lcSQL,"tcStyleMinMax")
1534
1535 IF llRetVal
1536 SELECT tcStyleMinMax
1537 INDEX ON fkey TAG fkey
1538 ENDIF
1539
1540
1541 .nProcessStyles = RECCOUNT('tcHeader1')
1542 IF .nProcessStyles = 0
1543 .cMessage = "There is no data to process."
1544 this.lNoDataFound = .T.
1545 ENDIF
1546
1547 SELECT h.*, NVL(c.ppk_contents,"") as ppk_content ;
1548 INTO CURSOR tcHeader ;
1549 FROM tcHeader1 h ;
1550 LEFT JOIN tcPpkContent c ON ALLTRIM(h.cProductNum) == ALLTRIM(c.cProductNum) ;
1551 ORDER BY h.cProductNum
1552
1553 SELECT tcScolr
1554 *INDEX ON division+style+color_code+lbl_code+PADL(sort_seq,10,"0") TAG prodNum
1555 INDEX ON cProductNum+PADL(sort_seq,10,"0") TAG prodNum
1556
1557
1558 SELECT tcUPCnr
1559 INDEX ON division+style+color_code+lbl_code+dimension+PADL(sizebucket,2,"0") TAG upc
1560
1561 SELECT tcStylr
1562 INDEX on division+style TAG style
1563
1564 SELECT tcColrr
1565 INDEX on color_code TAG color
1566
1567 *--- FGCjr
1568 SELECT tcDlvcd
1569 INDEX on delv_code TAG delv_code
1570 SELECT tcContr
1571 INDEX on cont_code TAG cont_code
1572
1573 SELECT zzxdivsr_local
1574 INDEX on division TAG div
1575
1576 SELECT zzxseasr_local
1577 INDEX on division+season TAG div_seas
1578
1579 SELECT zzxpgrpr_local
1580 INDEX ON division+group_type+group_code TAG mg
1581
1582 SELECT zzxpgtyr_local
1583 INDEX ON group_type TAG gt
1584
1585 SELECT tcColrr_trns
1586 *INDEX on fkey TAG fkey
1587 INDEX on PADL(fkey,10,"0")+PADL(sort_seq,10,"0") TAG fkey
1588
1589 SELECT tcStylr_img
1590 INDEX ON fkey TAG fkey
1591
1592 SELECT tcSColr_img
1593 INDEX on fkey TAG fkey
1594
1595* SELECT zzxthvnc_local
1596* INDEX on division TAG div
1597
1598 SELECT tcIntSP
1599 *INDEX on division+style+color_code+lbl_code+PADL(sort_seq,10,"0") TAG price
1600 INDEX on cProductNum+PADL(sort_seq,10,"0") TAG price
1601
1602 SELECT tcStyleDescriptors
1603 INDEX ON PADL(fkey,10,"0")+PADL(sort_seq,10,"0") tag fkey
1604
1605 SELECT tcStyleTagNotes
1606 INDEX ON PADL(fkey,10,"0")+PADL(sort_seq,10,"0") tag fkey
1607
1608
1609 SELECT zzxhsnor_local
1610 INDEX on hs_num TAG hs_num
1611
1612 SELECT tcSizer
1613 INDEX on division + size_code TAG sz
1614
1615 select tcScolr_sizes
1616 INDEX on pkey tag pkey
1617
1618 SELECT zzxtrnsl_sty_name_local
1619 *INDEX on fkey TAG fkey
1620 INDEX on PADL(fkey,10,"0")+PADL(sort_seq,10,"0") TAG fkey
1621
1622 SELECT zzxtrnsl_sty_desc_local
1623 *INDEX on fkey TAG fkey
1624 INDEX on PADL(fkey,10,"0")+PADL(sort_seq,10,"0") TAG fkey
1625
1626 SELECT zzxclblr_local
1627 INDEX on lbl_code TAG lbl_code
1628
1629
1630 SELECT tcMultiSeason
1631 INDEX on pkey TAG pkey
1632
1633 SELECT tcSizer_trns
1634 *INDEX on STR(fkey)+ALLTRIM(LOWER(field_name)) TAG fkey
1635 INDEX on STR(fkey)+ALLTRIM(LOWER(field_name))+PADL(sort_seq,10,"0") TAG fkey
1636
1637 SELECT tcMerch_trns
1638 *INDEX on fkey TAG fkey
1639 INDEX on PADL(fkey,10,"0")+PADL(sort_seq,10,"0") TAG fkey
1640
1641
1642
1643 *--- TR 1081856 13-10-2014 VKK
1644 lcString = "SELECT dlv.* FROM zzxscdlv dlv " + ;
1645 " JOIN " + .cTempTbl + " t " + ;
1646 " ON dlv.fkey = t.pkey " + ;
1647 " JOIN zzxscolr d " + ;
1648 " On d.pkey = t.pkey " + ;
1649 "Where 1 = 1 " + ;
1650 .cProcessFilter
1651
1652 llRetVal = llRetVal AND v_sqlexec(lcString,"zzxscdlv_Local")
1653 SELECT zzxscdlv_Local
1654 INDEX On Fkey TAG Fkey
1655* INDEX on Division+Style+color_code+lbl_code+dimension TAG sku
1656 *=== TR 1081856 13-10-2014 VKK
1657
1658 v_sqlexecnoerror("DROP TABLE "+ .cTempTbl)
1659 endif
1660
1661 SELECT (lnSelect)
1662
1663 .oLog.LogResult(llRetVal, lcHeading)
1664 .oLog.LogEntry("")
1665 ENDWITH
1666
1667 RETURN llRetVal
1668
1669 ENDPROC
1670
1671 *============================================================
1672 *============================================================
1673
1674 PROCEDURE CreateXMLFiles
1675 LOCAL lcSKU,x, lcHeading, llRetVal, llSizeAvail
1676 LOCAL lcStyleTypeDesc, lcStyleType, lcImageFile, lcAttribGrp, lnRecNo,lcDivCurr, lcTranslateXML
1677
1678 WITH THIS
1679 lcHeading = "Creating XML Files..."
1680 .UpdateThermoCaption(lcHeading)
1681 .LogMajorStage(lcHeading)
1682 .oLog.LogEntry("")
1683
1684 .AdvanceThermo(50)
1685 .AdvanceThermoTotal(50)
1686 INKEY(.1,"H")
1687
1688 llRetVal = true
1689
1690 SELECT tcAval
1691 lcSKU = ""
1692 x = 0
1693 SCAN
1694 IF lcSKU <> division+style+color_code+lbl_code+dimension
1695 x = 0
1696 ELSE
1697 x = x + 1
1698 ENDIF
1699
1700 replace rec_num WITH x
1701
1702 lcSKU = division+style+color_code+lbl_code+dimension
1703 ENDSCAN
1704
1705 * Create work cursor indexed to the order we need.
1706* SELECT * INTO CURSOR __tcScolr READWRITE FROM tcScolr WHERE 1=0
1707* INDEX ON PADL(dim_seq,10,"0") + PADL(dim_pkey,10,"0") + dimension TAG corder
1708
1709 SELECT tcAval
1710 INDEX ON division+style+color_code+lbl_code+dimension+ALLTRIM(STR(rec_num)) TAG sku
1711
1712 LOCAL lcToday,lcProduct,lcXML,lcStyle,lcAvailXML,lcColor ,lcStylr , lcStyleDesc , lcStyleName , lcColorname , lnQTY
1713 LOCAL lcDelvDesc,lcImage, lcskuXML , lcUpcSegment,llFoundImage, lcSize1, lcPriceXML
1714 lcToday = This.getDateFormat(DATE())
1715 lcProduct = ""
1716 lcXML = ""
1717 lcStylr = ""
1718 lcAvailXML = ""
1719 lcPriceXML = ""
1720
1721 CREATE CURSOR tcXML (cProdNum c(30),cXML m,cXMLResult m, division c(3), active_ok c(1), image_file m, uploaded c(1), xml_hash m)
1722
1723
1724 IF NOT .lSendTranslations AND NOT .lOnlyAvailability
1725 lcXML = .GenerateMerchGroupServiceXml()
1726
1727 IF NOT EMPTY(lcXML)
1728 SELECT tcXML
1729 APPEND BLANK
1730 replace cProdNum WITH "MERCHGROUPSXLATE", cXML WITH lcXML, division WITH "",;
1731 active_ok WITH "Y",;
1732 image_file WITH "",;
1733 uploaded WITH 'N',;
1734 xml_hash WITH SYS(2007,STRTRAN(lcXML,"<date>" +This.getDateFormat(DATE()) + "</date>",""))
1735 ENDIF
1736
1737 ENDIF
1738
1739
1740
1741 SELECT tcHeader
1742 SCAN
1743 lcXML = ""
1744 lcAvailXML = ""
1745 lcHeaderXML = ""
1746 lcskuXML = ""
1747 lcPriceXML = ""
1748 lcTranslateXML = ""
1749 *lcProduct = division+style+color_code+lbl_code
1750 lcProduct = cProductNum
1751 lcStylr = division+style
1752 lcColor = color_code
1753 lcNRF_clr = nrf_color
1754 lcImageFile = ""
1755 lcColorFld = ""
1756 llFoundImage = .F.
1757
1758 *lcProdNum = ALLTRIM(tcHeader.division)+"-" +ALLTRIM(tcHeader.style)+"-"+ALLTRIM(tcHeader.color_code)+;
1759 IIF(EMPTY(tcHeader.lbl_code),"","-"+ALLTRIM(tcHeader.lbl_code))
1760 lcProdNum = tcHeader.cProductNum
1761
1762
1763 lcProdNum = CHRTRAN(lcProdNum,"/\ ","-")
1764
1765* =SEEK(tcHeader.division,"zzxthvnc_local","div") OR SEEK(" ","zzxthvnc_local","div")
1766
1767
1768 lcHeaderXML = [<?xml version="1.0" encoding="UTF-8"?>] + CRLF
1769
1770
1771
1772
1773 *--- TR 1081377 17-oct-2014 BNarayanan Code modified as per Neils recommendtion ---
1774 *lcHeaderXML = lcHeaderXML + [<style ] + IIF(.lOnlyAvailability,"",[availability="overwrite" ]) + [tag="]+.cTagHandling+[" action="] + ;
1775 *ICASE(.lArchiveStyles,"archive",tcHeader.active_ok = "Y","activate","archive") + [" price="overwite">] + CRLF
1776
1777 lcHeaderXML = lcHeaderXML + [<style ] + [availability="overwrite" ] + [tag="]+.cTagHandling+[" action="] + ;
1778 ICASE(.lArchiveStyles,"archive",tcHeader.active_ok = "Y","activate","archive") + [" price="overwite">] + CRLF
1779
1780 *=== TR 1081377 17-oct-2014 BNarayanan Code modified as per Neils recommendtion ===
1781
1782 *lcHeaderXML = lcHeaderXML + [<style ] + IIF(.lOnlyAvailability,"",[availability="overwrite" ]) + [tag="overwrite,create" >] + CRLF
1783 lcHeaderXML = lcHeaderXML + [ ] + .EncodeXML("productNo",lcProdNum) + CRLF
1784
1785
1786 *=-=-=-=-=-=-=-=- Translation is now a separate XML
1787 lcTranslateXML = [<?xml version="1.0" encoding="UTF-8"?>] + CRLF
1788 lcTranslateXML = lcTranslateXML + [<style ] + [availability="overwrite" ] + [tag="]+.cTagHandling+[" action="] + ;
1789 ICASE(.lArchiveStyles,"archive",tcHeader.active_ok = "Y","activate","archive") + [" price="overwite" hasTranslations="YES">] + CRLF
1790 lcTranslateXML = lcTranslateXML + .EncodeXML("productNo",lcProdNum) + CRLF
1791 *=-=-=-=-=-=-=-=- Translation is now a separate XML
1792
1793
1794 lcStyleDesc = ""
1795 lcStyleName = ""
1796 lcColorname = ""
1797
1798
1799 STORE "" TO lcStyleTypeDesc,lcStyleType
1800 IF SEEK(lcStylr,'tcStylr','style')
1801
1802 SELECT tcStylr
1803 IF NOT EMPTY(.oControl.tv_style_name_formula)
1804 lcStyleName = EVALUATE(ALLTRIM(.oControl.tv_style_name_formula))
1805 ELSE
1806 lcStyleName = ALLTRIM(tcStylr.style_name)
1807 ENDIF
1808
1809
1810 IF NOT EMPTY(.oControl.tv_style_desc_formula)
1811 lcStyleDesc = EVALUATE(ALLTRIM(.oControl.tv_style_desc_formula))
1812 ELSE
1813 lcStyleDesc = IIF(EMPTY(ALLTRIM(tcStylr.style_desc)),ALLTRIM(tcStylr.style_name),ALLTRIM(tcStylr.style_desc)) + ;
1814 IIF(!EMPTY(tcStylr.cont_desc),CRLF+"CONTENT: "+ALLTRIM(tcStylr.cont_desc),"")
1815 ENDIF
1816
1817 lcStyleType = ALLTRIM(tcStylr.style_type)
1818 lcStyleTypeDesc = ALLTRIM(tcStylr.style_type_desc)
1819
1820 SELECT tcHeader
1821 ENDIF
1822
1823 IF SEEK(lcColor,'tcColrr','color')
1824 *lcColorName = ALLTRIM(tcColrr.color_name)
1825 SELECT tcColrr
1826 IF NOT EMPTY(.oControl.tv_color_name_formula)
1827 lcColorName = EVALUATE(ALLTRIM(.oControl.tv_color_name_formula))
1828 ELSE
1829 lcColorName = ALLTRIM(lcColor) + "-" + ALLTRIM(tcColrr.color_name)
1830 ENDIF
1831
1832 IF NOT EMPTY(.oControl.tv_color_formula)
1833 lcColorFld = EVALUATE(ALLTRIM(.oControl.tv_color_formula))
1834 ELSE
1835 lcColorFld = ALLTRIM(lcColor)+ IIF(!EMPTY(lcNRF_clr),"-"+allt(lcNRF_clr),"")
1836 ENDIF
1837
1838
1839 IF NOT EMPTY(tcColrr.img_file) AND NOT ISNULL(tcColrr.img_file)
1840 .AddImage(tcHeader.division,ALLTRIM(tcColrr.img_file),"swatch")
1841 ENDIF
1842
1843
1844 SELECT tcHeader
1845 ENDIF
1846
1847 =SEEK(tcStylr.division+tcStylr.size_code,"tcSizer","sz")
1848
1849 *--- FGCjr
1850 lcContCode = tcHeader.cont_code
1851 lcContDesc = ""
1852 IF SEEK(lcContCode,'tcContr','cont_code')
1853 lcContDesc = ALLTRIM(tcContr.cont_desc)
1854 ENDIF
1855
1856 IF NOT .lOnlyAvailability
1857 lcHeaderXML = lcHeaderXML + [ ] + .EncodeXML("styleNo",ALLT(tcHeader.style)) + CRLF
1858
1859
1860 lcHeaderXML = lcHeaderXML + .EncodeXML("styleName",ALLTRIM(lcStyleName)) + CRLF
1861 lcTranslateXML = lcTranslateXML + .exportTranslation("styleName","zzxtrnsl_sty_name_local",tcStylr.pkey,lcStyleName)
1862
1863 lcHeaderXML = lcHeaderXML + [ ] + .EncodeXML("color",ALLTRIM(lcColorFld)) + CRLF
1864 *lcHeaderXML = lcHeaderXML + [ ] + .EncodeXML("colorName",ALLT(lcColorName)) + CRLF
1865
1866
1867 lcXML = lcXML + .EncodeXML("colorName",ALLT(lcColorName)) + CRLF
1868 lcTranslateXML = lcTranslateXML + .exportTranslation("colorName","tcColrr_trns",tcColrr.pkey,ALLT(lcColorName))
1869
1870 ENDIF
1871
1872
1873*zzxtrnsl_sty_desc_local
1874
1875 lcXML = lcXML + " <description><![CDATA[" + CRLF+ lcStyleDesc + CRLF + ; && FGCjr
1876 + "]]></description>" + CRLF
1877
1878 lcTranslateXML = lcTranslateXML + .exportTranslation("description","zzxtrnsl_sty_desc_local",tcStylr.pkey,lcStyleDesc)
1879
1880
1881 lcTranslateXML = lcTranslateXML + .exportDescriptors(tcStylr.pkey,"tcStyleDescriptors",.F.) && Export descriptors
1882
1883
1884
1885 *IF NOT .lPriceLevelSupport
1886 * lcXML = lcXML + [ <cost>] + TRANSFORM(tcHeader.std_cost) + [</cost>] + CRLF
1887 * lcXML = lcXML + [ <retail>] + TRANSFORM(tcHeader.ret_price) + [</retail>] + CRLF
1888 * lcXML = lcXML + [ <wholesale>] + TRANSFORM(tcHeader.a_price) + [</wholesale>] + CRLF
1889 *ENDIF
1890
1891
1892
1893
1894
1895 *lcProdNum = ALLTRIM(tcHeader.division)+"-"+ALLTRIM(tcHeader.style)+"-"+ALLTRIM(tcHeader.color_code)+;
1896 IIF(EMPTY(tcHeader.lbl_code),"","-"+ALLTRIM(tcHeader.lbl_code))
1897
1898 *lcProdNum = CHRTRAN(lcProdNum,"/\ ","-")
1899
1900 * SELECT __tcScolr
1901 * ZAP
1902
1903 * IF SEEK(lcProduct,"tcScolr","prodnum")
1904 * SELECT tcScolr
1905 * SCAN WHILE lcProduct == division+style+color_code+lbl_code
1906 * SCATTER NAME loOne MEMO
1907 * SELECT __tcScolr
1908 * APPEND BLANK
1909 * GATHER NAME loOne MEMO
1910 * ENDSCAN
1911 * ENDIF
1912
1913 * SELECT __tcScolr
1914 * LOCATE
1915
1916 * SELECT * FROM tcScolr INTO CURSOR __tcScolr ;
1917 WHERE division == tcHeader.division ;
1918 AND style == tcHeader.style ;
1919 AND color_code == tcHeader.color_code ;
1920 AND lbl_code == tcHeader.lbl_code ;
1921 ORDER BY dim_seq,dim_pkey,dimension
1922
1923 *IF USED("__tcScolr") AND RECCOUNT("__tcScolr") > 0
1924
1925 SELECT tcScolr
1926 IF SEEK(lcProduct +PADL(1,10,"0"),"tcScolr","prodnum")
1927
1928 SELECT tcScolr
1929 *--- FGCjr
1930 lcDelvDesc = ""
1931 lcDelvCode = tcScolr.delv_code
1932 lxStartDate = ""
1933 lxEndDate = ""
1934 IF SEEK(lcDelvCode,'tcDlvcd','delv_code')
1935 lcDelvDesc = ALLTRIM(tcDlvcd.delv_desc)
1936
1937 IF .lSendDeliveryDatesFromUDF
1938 lxStartDate = LEFT(tcDlvcd.udfdlv1c,10)
1939 lxEndDate = LEFT(tcDlvcd.udfdlv2c,10)
1940 ELSE
1941 lxStartDate = .getDateFormat(tcDlvcd.ear_sdate)
1942 lxEndDate = .getDateFormat(tcDlvcd.lat_edate)
1943 ENDIF
1944 ENDIF
1945
1946 lcDivDesc = ""
1947 lcSeasDesc= ""
1948 lcDivCurr = ""
1949 IF SEEK(tcScolr.division,"zzxdivsr_local","div")
1950 lcDivDesc = ALLTRIM(zzxdivsr_local.div_name)
1951 lcDivCurr = ALLTRIM(zzxdivsr_local.curr_code)
1952 ENDIF
1953 IF SEEK(tcScolr.division+tcScolr.Season,"zzxseasr_local","div_seas")
1954 lcSeasDesc = ALLTRIM(zzxseasr_local.seas_name)
1955 ENDIF
1956
1957 lcXML = lcXML + [ <tags>] + CRLF
1958 lcXML = lcXML + [ ] + .EncodeTag(ALLT(tcScolr.division),"Division Code") + CRLF
1959 lcXML = lcXML + [ ] + .EncodeTag(lcDivDesc ,"Division") + CRLF
1960
1961 lcXML = lcXML + [ ] + .EncodeTag(ALLT(tcScolr.season),"Season Code") + CRLF
1962 lcXML = lcXML + [ ] + .EncodeTag(lcSeasDesc,"Season") + CRLF
1963
1964 lcXML = lcXML + [ ] + .EncodeTag(ALLT(lcContCode),"Content Code") + CRLF
1965 lcXML = lcXML + [ ] + .EncodeTag(lcContDesc,"Content") + CRLF
1966
1967 IF NOT EMPTY(tcScolr.lbl_code) AND SEEK(tcScolr.lbl_code,"zzxclblr_local","lbl_code")
1968 lcXML = lcXML + [ ] + .EncodeTag(ALLT(tcScolr.lbl_code),"Label Code") + CRLF
1969 lcXML = lcXML + [ ] + .EncodeTag(ALLTRIM(zzxclblr_local.lbl_name),"Label") + CRLF
1970 ENDIF
1971
1972 *--- FGCjr
1973 if !empty(lcDelvDesc)
1974 lcXML = lcXML + [ ] + .EncodeTag(ALLT(lcDelvDesc),"Delivery") + CRLF
1975 lcXML = lcXML + [ ] + .EncodeTag(ALLT(lcDelvCode),"Delivery Code") + CRLF
1976 endif
1977
1978 *** CUSTOM FOR GORE
1979 IF tcScolr.gar_wgt > 0
1980 lcXML = lcXML + [ ] + .EncodeTag(TRANSFORM(INT(tcScolr.gar_wgt * 1000)) + "g","Weight") + CRLF
1981 ENDIF
1982 *** CUSTOM FOR GORE
1983
1984 LOCAL lnChar_ctr, lcChar,lcGroupName
1985 lnChar_ctr = 64 && 65 = 'A'
1986
1987 * Send all group codes
1988 FOR x = 1 TO 13
1989 lcChar = chr(lnChar_ctr + x)
1990 lcField = "group_code" + TRANSFORM(x)
1991 lcGroup = sysgetfieldvalue("tcScolr",lcField)
1992
1993 lcGroupName = ""
1994
1995 * Get merch desc from language ref
1996 lcGroupName = i(lcField)
1997
1998 IF EMPTY(.cTagMatchCursor) OR NOT USED(.cTagMatchCursor)
1999 .DownloadTagTable()
2000 ENDIF
2001
2002 IF NOT EMPTY(.cTagMatchCursor) AND USED(.cTagMatchCursor)
2003 IF SEEK(PADR(UPPER(lcGroupName),100)+PADR("",100),.cTagMatchCursor,"ckey")
2004 lcGroupName = ALLTRIM(sysgetfieldvalue(.cTagMatchCursor,"new_tagname"))
2005 ENDIF
2006 ENDIF
2007
2008
2009 *IF SEEK(lcChar,"zzxpgtyr_local","gt")
2010 * lcGroupName = ALLTRIM(zzxpgtyr_local.type_name)
2011 *ENDIF
2012
2013
2014 lcGroupDesc = ""
2015 IF NOT EMPTY(lcGroup)
2016 IF SEEK(tcScolr.division+lcChar+lcGroup,"zzxpgrpr_local","mg")
2017 lcGroupDesc = ALLTRIM(zzxpgrpr_local.group_name)
2018
2019 * this is the place for language translation
2020 lcXML = lcXML + .exportTagTranslation(lcGroupName,"tcMerch_trns",zzxpgrpr_local.pkey,ALLT(lcGroupDesc),.t.)
2021
2022
2023 ENDIF
2024
2025 ELSE
2026
2027 lcXML = lcXML + [ ] + .EncodeTag(lcGroupDesc ,lcGroupName) + CRLF
2028
2029 ENDIF
2030
2031 lcXML = lcXML + [ ] + .EncodeTag(lcGroup,lcGroupName + " Code") + CRLF
2032
2033
2034
2035
2036 ENDFOR
2037
2038
2039 lcXML = lcXML + [ ] + .EncodeTag(lcStyleType,"Style Type Code") + CRLF
2040 lcXML = lcXML + [ ] + .EncodeTag(lcStyleTypeDesc,"Style Type") + CRLF
2041
2042 * UDFs
2043 lcXML = lcXML + [ ] + .EncodeTag(tcStylr.udfsty1c,"udfsty1c") + CRLF
2044 lcXML = lcXML + [ ] + .EncodeTag(tcStylr.udfsty2c,"udfsty2c") + CRLF
2045 lcXML = lcXML + [ ] + .EncodeTag(tcStylr.udfsty3c,"udfsty3c") + CRLF
2046 lcXML = lcXML + [ ] + .EncodeTag(tcStylr.udfsco2c,"udfsco2c") + CRLF
2047 lcXML = lcXML + [ ] + .EncodeTag(TRANSFORM(tcStylr.udfsty1n),"udfsty1n") + CRLF
2048
2049 lcXML = lcXML + [ ] + .EncodeTag(tcStylr.catg_num,"catg_num") + CRLF
2050 lcXML = lcXML + [ ] + .EncodeTag(tcStylr.catg_desc,"catg_desc") + CRLF
2051
2052 lcXML = lcXML + [ ] + .EncodeTag(tcStylr.size_code,"size_code") + CRLF
2053 lcXML = lcXML + [ ] + .EncodeTag(tcStylr.size_name,"size_name") + CRLF
2054
2055 IF NOT EMPTY(tcStylr.country)
2056 lcXML = lcXML + [ ] + .EncodeTag(tcStylr.country,"COO Code") + CRLF
2057 ENDIF
2058 IF NOT EMPTY(tcStylr.cnty_name)
2059 lcXML = lcXML + [ ] + .EncodeTag(tcStylr.cnty_name,"COO") + CRLF
2060 ENDIF
2061
2062
2063 IF NOT EMPTY(tcStylr.hs_num)
2064 IF SEEK(tcStylr.hs_num,"zzxhsnor_local","hs_num")
2065 lcXML = lcXML + [ ] + .EncodeTag(tcStylr.hs_num,"HS Number") + CRLF
2066 lcXML = lcXML + [ ] + .EncodeTag(zzxhsnor_local.hs_name,"HS Description") + CRLF
2067 ENDIF
2068 ENDIF
2069
2070 IF NOT EMPTY(tcStylr.royalty)
2071 lcXML = lcXML + [ ] + .EncodeTag(tcStylr.royalty,"Royalty Code") + CRLF
2072 lcXML = lcXML + [ ] + .EncodeTag(tcStylr.roy_cls,"Royalty Class Code") + CRLF
2073 lcXML = lcXML + [ ] + .EncodeTag(tcStylr.roy_name,"Royalty") + CRLF
2074 lcXML = lcXML + [ ] + .EncodeTag(tcStylr.roy_cls_name,"Royalty Class") + CRLF
2075 ENDIF
2076
2077 *--- TR 1081856 13-10-2014 VKK
2078 llSeek = SEEK(tcScolr.Pkey, "zzxscdlv_Local", "fkey")
2079
2080 IF llSeek
2081 SELECT zzxscdlv_Local
2082
2083 SCAN WHILE Fkey = tcScolr.Pkey
2084 lcXML = lcXML + [ ] + .EncodeTag(zzxscdlv_Local.Delv_code,"") + CRLF
2085 ENDSCAN
2086
2087 ENDIF
2088
2089 SELECT tcScolr
2090 *=== TR 1081856 13-10-2014 VKK
2091
2092 * No translations here
2093 lcXML = lcXML + .exportDescriptors(tcStylr.pkey,"tcStyleTagNotes",.T.,.T.) && Descriptors as tags
2094
2095
2096
2097
2098
2099 lcXML = lcXML + [ </tags>] + CRLF
2100
2101 * added lSendDeliveryDatesFromUDF since we only want this logic for komar
2102 IF UPPER(ALLTRIM(tcScolr.group_code2)) == "99" AND .lSendDeliveryDatesFromUDF
2103 *lcXML = lcXML + " <deliverySetting>CORE</deliverySetting>" + CRLF
2104 ELSE
2105
2106 **-**lxStartDate = PADL(tcScolr.UDFSCO1N,6,"0")
2107
2108
2109 **IF VAL(SUBSTR(lxStartDate,3,2)) = 0 OR VAL(RIGHT(lxStartDate,2)) = 0
2110 ** lxStartDate = DATE()
2111 **ELSE
2112 ** lxStartDate = DATE(2000+VAL(LEFT(lxStartDate,2)),VAL(SUBSTR(lxStartDate,3,2)),VAL(RIGHT(lxStartDate,2)))
2113 **ENDIF
2114
2115 **lxEndDate = lxStartDate + 14
2116
2117 **lcStartDate = THIS.getDateFormat(lxStartDate)
2118 **lcEndDate = THIS.getDateFormat(lxEndDate)
2119
2120 lcStartDate = lxStartDate
2121 lcEndDate = lxEndDate
2122
2123 *lcXML = lcXML + " <deliverySetting>SEASONAL</deliverySetting>" + CRLF
2124 IF NOT EMPTY(lcStartDate)
2125 lcXML = lcXML + " <deliveries>" + CRLF
2126 lcXML = lcXML + " <delivery>" + CRLF
2127 lcXML = lcXML + " <start>" + lcStartDate + "</start>" + CRLF
2128 lcXML = lcXML + " <end>" + lcEndDate + "</end>" + CRLF
2129 lcXML = lcXML + " </delivery>" + CRLF
2130 lcXML = lcXML + " </deliveries>" + CRLF
2131 ENDIF
2132 ENDIF
2133
2134 *lcXML = lcXML + " <attributeGroup>" + ALLTRIM(tcScolr.group_code13) + "</attributeGroup>"
2135 *IF NOT SEEK(tcScolr.division,"zzxthvnc_local","div")
2136 * =SEEK(" ","zzxthvnc_local","div")
2137 *ENDIF
2138
2139 SELECT tcMultiSeason
2140 IF SEEK(tcScolr.pkey,"tcMultiSeason","pkey")
2141 lcXML = lcXML + " <seasons>" + CRLF
2142 SCAN WHILE pkey = tcScolr.pkey
2143 lcXML = lcXML + " " + .EncodeXML("season",ALLT(tcMultiSeason.season))+ CRLF
2144 ENDSCAN
2145 lcXML = lcXML + " </seasons>" + CRLF
2146 ENDIF
2147
2148
2149
2150
2151 DO CASE
2152
2153 CASE INLIST(ALLTRIM(.oControl.attrib_group),"STANDARD")
2154 * Hardcoded attribute group
2155 lcAttribGrp = ALLTRIM(.oControl.attrib_group)
2156 CASE INLIST(ALLTRIM(.oControl.attrib_group),"STYLE_TYPE")
2157 * Specific header bros
2158 lcAttribGrp = ALLTRIM(sysgetfieldvalue("tcStylr",ALLTRIM(.oControl.attrib_group)))
2159 OTHERWISE
2160 lcAttribGrp = ALLTRIM(sysgetfieldvalue("tcScolr",ALLTRIM(.oControl.attrib_group)))
2161 ENDCASE
2162
2163 lcXML = lcXML + " <attributeGroup>" + lcAttribGrp + "</attributeGroup>" + CRLF
2164
2165 ENDIF
2166
2167
2168 *--- FGCjr 08/05/11
2169 lcOldExact = set('EXACT')
2170 SET EXACT ON
2171 *=== FGCjr 08/05/11
2172
2173
2174
2175
2176 llFoundDivCurr = .F.
2177 lcPriceXML= ""
2178 lcPriceXML= lcPriceXML+ [ <prices>] + CRLF
2179 *IF .lPriceLevelSupport - retired parameter
2180 * Do prices before skus
2181
2182 SELECT tcSpPrice
2183 LOCATE
2184
2185 IF SEEK(ALLTRIM(lcProduct),"tcSpPrice","cProd")
2186
2187 SCAN WHILE ALLTRIM(cProductNum) == ALLTRIM(lcProduct)
2188 FOR x = 1 TO 5
2189 lcRuleName = ALLTRIM(tcSpPrice.rule_name)
2190 IF !INLIST(lcRuleName,"WHSL","MSRP")
2191 lcRuleName = lcRuleName + UPPER(CHR(64+x))
2192 ENDIF
2193 lcPriceField = CHR(64+x) + "_price"
2194 IF sysgetfieldvalue("tcSpPrice",lcPriceField)>0
2195 lcPriceXML= lcPriceXML+ [ <price id="]+lcRuleName+[">] + CRLF
2196 lcPriceXML= lcPriceXML+ [ <currency>]+ALLTRIM(tcSpPrice.curr_code)+[</currency>] + CRLF
2197 lcPriceXML= lcPriceXML+ [ <amount>]+ALLTRIM(TRANSFORM(sysgetfieldvalue("tcSpPrice",lcPriceField),"999999.99"))+[</amount>] + CRLF
2198 lcPriceXML= lcPriceXML+ [ </price>] + CRLF
2199 ENDIF
2200 ENDFOR
2201 ENDSCAN
2202 ELSE
2203
2204
2205 IF RECCOUNT("zzxthvnd_whsl_local") > 0
2206 SELECT tcDistinctCurr
2207 SCAN
2208 IF SEEK(tcDistinctCurr.curr_code+tcScolr.division,"zzxthvnd_whsl_local","csku") OR ;
2209 SEEK(tcDistinctCurr.curr_code+" ","zzxthvnd_whsl_local","csku")
2210
2211 IF sysgetfieldvalue("tcScolr",ALLTRIM(zzxthvnd_whsl_local.detail_value)) > 0
2212 lcPriceXML= lcPriceXML+ [ <price id="WHSL">] + CRLF
2213 lcPriceXML= lcPriceXML+ [ <currency>]+ALLTRIM(zzxthvnd_whsl_local.curr_code)+[</currency>] + CRLF
2214 lcPriceXML= lcPriceXML+ [ <amount>]+ALLTRIM(TRANSFORM(sysgetfieldvalue("tcScolr",ALLTRIM(zzxthvnd_whsl_local.detail_value))))+[</amount>] + CRLF
2215 lcPriceXML= lcPriceXML+ [ </price>] + CRLF
2216
2217 IF NOT llFoundDivCurr
2218 llFoundDivCurr = ALLTRIM(lcDivCurr) == ALLTRIM(zzxthvnd_whsl_local.curr_code)
2219 ENDIF
2220
2221
2222 ENDIF
2223
2224 ENDIF
2225 ENDSCAN
2226 ENDIF
2227
2228 IF SEEK(lcProduct +PADL(1,10,"0"),"tcIntSP","price")
2229
2230 SELECT tcIntSP
2231 SCAN WHILE cProductNum = lcProduct
2232
2233 IF NOT llFoundDivCurr
2234 * If there is an international record for this sku and the div currency, do not output style master prices.
2235 llFoundDivCurr = ALLTRIM(lcDivCurr) == ALLTRIM(tcIntSP.curr_code) AND EMPTY(tcIntSP.int_region_code)
2236 ENDIF
2237
2238 FOR x = 1 TO 5
2239 lcPriceField = CHR(64+x) + "_price"
2240 lcPriceName = "BC-" + IIF(EMPTY(tcIntSP.int_region_code),"",ALLTRIM(tcIntSP.int_region_code)+"-")+ CHR(64+x)
2241
2242 IF sysgetfieldvalue("tcIntSP",lcPriceField) > 0
2243 lcPriceXML= lcPriceXML+ [ <price id="]+lcPriceName+[">] + CRLF
2244 lcPriceXML= lcPriceXML+ [ <currency>]+ALLTRIM(tcIntSP.curr_code)+[</currency>] + CRLF
2245 lcPriceXML= lcPriceXML+ [ <amount>]+ALLTRIM(TRANSFORM(sysgetfieldvalue("tcIntSP",lcPriceField),"999999.99"))+[</amount>] + CRLF
2246 lcPriceXML= lcPriceXML+ [ </price>] + CRLF
2247 ENDIF
2248 ENDFOR
2249
2250 IF RECCOUNT("zzxthvnd_whsl_local") = 0
2251 * Do not output isp price a as wholesale if detail grid was setup
2252 lcPriceXML= lcPriceXML+ [ <price id="WHSL">] + CRLF
2253 lcPriceXML= lcPriceXML+ [ <currency>]+ALLTRIM(tcIntSP.curr_code)+[</currency>] + CRLF
2254 lcPriceXML= lcPriceXML+ [ <amount>]+ALLTRIM(TRANSFORM(sysgetfieldvalue("tcIntSP","a_price")))+[</amount>] + CRLF
2255 lcPriceXML= lcPriceXML+ [ </price>] + CRLF
2256 ENDIF
2257
2258 lcPriceXML= lcPriceXML+ [ <price id="MSRP">] + CRLF
2259 IF tcIntSP.ret_price > 0
2260 lcPriceXML= lcPriceXML+ [ <currency>]+ALLTRIM(tcIntSP.curr_code)+[</currency>] + CRLF
2261 lcPriceXML= lcPriceXML+ [ <amount>]+ALLTRIM(TRANSFORM(tcIntSP.ret_price,"999999.99"))+[</amount>] + CRLF
2262 ENDIF
2263 lcPriceXML= lcPriceXML+ [ </price>] + CRLF
2264
2265
2266 ENDSCAN
2267
2268 ENDIF
2269
2270 IF NOT llFoundDivCurr
2271 FOR x = 1 TO 5
2272 lcPriceField = CHR(64+x) + "_price"
2273 lcPriceName = "BC-" + CHR(64+x)
2274
2275
2276 IF sysgetfieldvalue("tcScolr",lcPriceField) > 0
2277 lcPriceXML= lcPriceXML+ [ <price id="]+lcPriceName+[">] + CRLF
2278 lcPriceXML= lcPriceXML+ [ <currency>]+ALLTRIM(lcDivCurr)+[</currency>] + CRLF
2279 lcPriceXML= lcPriceXML+ [ <amount>]+ALLTRIM(TRANSFORM(sysgetfieldvalue("tcScolr",lcPriceField)))+[</amount>] + CRLF
2280 lcPriceXML= lcPriceXML+ [ </price>] + CRLF
2281 ENDIF
2282 ENDFOR
2283
2284 * Wholesale & MSRP price in division currency if not already outputted
2285 lcPriceXML= lcPriceXML+ [ <price id="WHSL">] + CRLF
2286 lcPriceXML= lcPriceXML+ [ <currency>]+ALLTRIM(lcDivCurr)+[</currency>] + CRLF
2287 lcPriceXML= lcPriceXML+ [ <amount>]+ALLTRIM(TRANSFORM(sysgetfieldvalue("tcScolr","a_price")))+[</amount>] + CRLF
2288 lcPriceXML= lcPriceXML+ [ </price>] + CRLF
2289 lcPriceXML= lcPriceXML+ [ <price id="MSRP">] + CRLF
2290 IF sysgetfieldvalue("tcScolr","ret_price") > 0
2291 lcPriceXML= lcPriceXML+ [ <currency>]+ALLTRIM(lcDivCurr)+[</currency>] + CRLF
2292 lcPriceXML= lcPriceXML+ [ <amount>]+ALLTRIM(TRANSFORM(sysgetfieldvalue("tcScolr","ret_price")))+[</amount>] + CRLF
2293 ENDIF
2294 lcPriceXML= lcPriceXML+ [ </price>] + CRLF
2295 ENDIF
2296
2297 ENDIF
2298
2299
2300
2301
2302 lcPriceXML= lcPriceXML+ [ </prices>] + CRLF
2303
2304 IF !.lOnlyAvailability
2305 lcXML = lcXML + lcPriceXML
2306 ENDIF
2307
2308
2309 SELECT tcScolr
2310 *locate
2311
2312 lnRecNo = RECNO("tcScolr")
2313 lnSkuCnt = 0
2314 SCAN WHILE lcProduct == cProductNum
2315 lnSkuCnt = lnSkuCnt + 1
2316 ENDSCAN
2317
2318 SELECT tcScolr
2319 GO (lnRecNo)
2320
2321 IF tcHeader.single_size == "Y" OR lnSkuCnt = 1
2322 lcXML = lcXML + " <sizeGridType>ONE</sizeGridType>"+ CRLF
2323 * lcXML = lcXML + " <sizeLabel1>Size</sizeLabel1>"+ CRLF
2324 ELSE
2325 lcXML = lcXML + " <sizeGridType>TWO</sizeGridType>"+ CRLF
2326 lcXML = lcXML + " <sizeLabel1>DIM-PPK</sizeLabel1>"+ CRLF
2327 lcXML = lcXML + " <sizeLabel2>SIZE</sizeLabel2>"+ CRLF
2328 ENDIF
2329
2330
2331
2332
2333 lcTranslateXML= lcTranslateXML+ .exportTranslation("sizeGridName","tcStyleMinMax",tcStylr.pkey,"")
2334
2335 lcXML = lcXML + [ <skus>] + CRLF
2336 lcTranslateXML = lcTranslateXML + [ <skus>] + CRLF
2337 lcImage = ""
2338 lcImageFile = ""
2339
2340
2341
2342
2343
2344 SCAN WHILE lcProduct == cProductNum
2345
2346 *** Do the dimensions
2347 lcSKU = division+style+color_code+lbl_code+dimension
2348
2349 llFoundAvail = SEEK(lcSku+ALLTRIM(STR(0)),"tcAval","sku")
2350 lnAvailRecno = 0
2351 IF llFoundAvail
2352 lnAvailRecno = RECNO("tcAval")
2353 ENDIF
2354
2355
2356 =SEEK(tcScolr.pkey,"tcScolr_sizes","pkey")
2357
2358
2359 FOR x = 1 TO goEnv.MAXBUCKETS
2360 lnQTY = 0
2361
2362 *IF EVALUATE("__tcScolr.call" + PADL(x,2,"0")) == "Y" AND EVALUATE("__tcScolr.allow" + PADL(x,2,"0")) == "Y"
2363 *IF SUBSTR(tcStylr.sizes,x,1) = 'Y'
2364 IF SUBSTR(tcHeader.sizes,x,1) = 'Y'
2365 * only output allowed and available sizes.
2366
2367
2368 lcUPC = ""
2369
2370 lcUpcSegment = ""
2371 llSizeAvail = EVALUATE("tcScolr_sizes.call" + PADL(x,2,"0")) == "Y" AND EVALUATE("tcScolr_sizes.allow" + PADL(x,2,"0")) == "Y"
2372 IF llSizeAvail AND SEEK(lcSKU +PADL(x,2,"0"),"tcUPCnr","upc")
2373 lcUPC = ALLT(tcUPCNr.upc) + IIF(EMPTY(tcUpcnr.upclbl_code) AND NOT EMPTY(tcUpcnr.lbl_code),"-"+ALLTRIM(tcUpcnr.lbl_code),"")
2374 lcUpcSegment = [ ] + .EncodeXML("upc",lcUPC) + CRLF
2375 ENDIF
2376
2377
2378 IF (tcHeader.single_size == "Y" AND llSizeAvail) OR (tcHeader.single_size <> "Y")
2379
2380 * Changed to lcHeaderXML so UPCs are always sent. If komar asks to put this back, it is not maintence. -NSD
2381 lcskuXML = lcskuXML + [ <sku] + IIF(llSizeAvail,[],[ active="false"]) + [>] + CRLF
2382 lcTranslateXML = lcTranslateXML+ [ <sku] + IIF(llSizeAvail,[],[ active="false"]) + [>] + CRLF
2383 IF tcHeader.single_size == "Y" OR lnSkuCnt = 1
2384 lcskuXML = lcskuXML + [ ] + .EncodeXML("size1",CHRTRAN(ALLTRIM(EVALUATE("tcSizer.size" + PADL(x,2,"0"))),"_","-")) + CRLF
2385 IF SEEK(STR(tcSizer.pkey)+"size" + PADL(x,2,"0")+PADL(1,10,"0"),"tcSizer_trns","fkey")
2386 lcTranslateXML = lcTranslateXML + [ ] + [<size1><regions>] + CRLF
2387 lcTranslateXML = lcTranslateXML + [ ] + [<region region='def'>] + CHRTRAN(ALLTRIM(EVALUATE("tcSizer.size" + PADL(x,2,"0"))),"_","-") + "</region>" + CRLF
2388 SELECT tcSizer_trns
2389 SCAN WHILE fkey = tcSizer.pkey AND ALLTRIM(LOWER(field_name)) == ALLTRIM(LOWER("size" + PADL(x,2,"0")))
2390 lcTranslateXML = lcTranslateXML + [ ] + [<region region='] + ALLTRIM(tcSizer_trns.int_region_code) + ['>]
2391 lcTranslateXML = lcTranslateXML + ALLTRIM(tcSizer_trns.translated_content) + "</region>" + CRLF
2392 ENDSCAN
2393 lcTranslateXML = lcTranslateXML + [ ] + [</regions></size1>] + CRLF
2394 ENDIF
2395 ELSE
2396 lcSize1 = ""
2397 DO CASE
2398 CASE NOT EMPTY(tcScolr.ppk_desc)
2399 lcSize1 = tcScolr.ppk_desc
2400 CASE EMPTY(tcScolr.dimension)
2401 lcSize1 = "-"
2402 OTHERWISE
2403 lcSize1 = tcScolr.dimension
2404 ENDCASE
2405
2406 * size 1 is not translated
2407 lcskuXML = lcskuXML + [ ] + .EncodeXML("size1",CHRTRAN(ALLT(lcSize1),"_","-")) + CRLF
2408 lcTranslateXML = lcTranslateXML + [ ] + .EncodeXML("size1",CHRTRAN(ALLT(lcSize1),"_","-")) + CRLF
2409
2410 lcskuXML = lcskuXML + [ ] + .EncodeXML("size2",CHRTRAN(ALLTRIM(EVALUATE("tcSizer.size" + PADL(x,2,"0"))),"_","-")) + CRLF
2411 IF SEEK(STR(tcSizer.pkey)+"size" + PADL(x,2,"0"),"tcSizer_trns","fkey")
2412 lcTranslateXML = lcTranslateXML + [ ] + [<size2><regions>] + CRLF
2413 lcTranslateXML = lcTranslateXML + [ ] + [<region region='def'>] + CHRTRAN(ALLTRIM(EVALUATE("tcSizer.size" + PADL(x,2,"0"))),"_","-") + "</region>" + CRLF
2414 SELECT tcSizer_trns
2415 SCAN WHILE fkey = tcSizer.pkey AND ALLTRIM(LOWER(field_name)) == ALLTRIM(LOWER("size" + PADL(x,2,"0")))
2416 lcTranslateXML = lcTranslateXML + [ ] + [<region region='] + ALLTRIM(tcSizer_trns.int_region_code) + ['>]
2417 lcTranslateXML = lcTranslateXML + ALLTRIM(tcSizer_trns.translated_content) + "</region>" + CRLF
2418 ENDSCAN
2419 lcTranslateXML = lcTranslateXML + [ ] + [</regions></size2>] + CRLF
2420 ENDIF
2421
2422
2423 ENDIF
2424
2425 *--- TR 1079445 min mult by size
2426 lcskuXML = lcskuXML + [ ] + .EncodeXML("multipleOrderQuantity",TRANSFORM(MAX(1,tcScolr.min_multiple)))+ CRLF
2427 *=== TR 1079445 min mult by size
2428 lcTranslateXML = lcTranslateXML + [ ] + .EncodeXML("multipleOrderQuantity",TRANSFORM(MAX(1,tcScolr.min_multiple)))+ CRLF
2429
2430 lcskuXML = lcskuXML + lcUpcSegment
2431 lcTranslateXML = lcTranslateXML +lcUpcSegment
2432
2433 lcskuXML = lcskuXML + [ </sku>] + CRLF
2434 lcTranslateXML = lcTranslateXML + [ </sku>] + CRLF
2435
2436 ENDIF
2437
2438 IF NOT EMPTY(lcUpcSegment) && UPC required to send availability, otherwise it fails
2439 IF !llFoundAvail
2440 IF .lOnlyAvailability AND llSizeAvail
2441 lcDate = lcToday
2442 lcAvailXML = lcAvailXML + [ <availability>] + CRLF
2443 lcAvailXML = lcAvailXML + lcUpcSegment
2444 lcAvailXML = lcAvailXML + [ <date>] + lcDate + [</date>] + CRLF
2445 lcAvailXML = lcAvailXML + [ <quantity>0</quantity>] + CRLF
2446 lcAvailXML = lcAvailXML + [ </availability>] + CRLF
2447 ENDIF
2448 ELSE
2449 SELECT tcAval
2450 GO lnAvailRecno
2451 SCAN WHILE division+style+color_code+lbl_code+dimension = lcSku
2452 IF llSizeAvail
2453 * Roll up is handled in base sql
2454 *lnQTY = lnQTY + sysgetfieldvalue("tcAval","size"+PADL(x,2,"0")+"_qty")
2455 lnQTY = sysgetfieldvalue("tcAval","size"+PADL(x,2,"0")+"_qty")
2456 lcDate = This.getDateFormat(tcAval.due_date)
2457 lcAvailXML = lcAvailXML + [ <availability>] + CRLF
2458 lcAvailXML = lcAvailXML + lcUpcSegment
2459 lcAvailXML = lcAvailXML + [ <date>] + lcDate + [</date>] + CRLF
2460 lcAvailXML = lcAvailXML + [ <quantity>] + .getDisplayAvailability(lnQty) + [</quantity>] + CRLF
2461 lcAvailXML = lcAvailXML + [ </availability>] + CRLF
2462 ENDIF
2463 ENDSCAN
2464 ENDIF
2465 ENDIF
2466
2467
2468 ENDIF
2469 ENDFOR
2470
2471
2472 IF !llFoundImage
2473 llFoundImage = .AddMainImage(tcScolr.division,tcScolr.pkey,"tcScolr_img")
2474 ENDIF
2475
2476 SELECT tcScolr
2477
2478 ENDSCAN
2479 lcXML = lcXML + lcskuXML
2480 lcXML = lcXML + [ </skus>] + CRLF
2481 lcTranslateXML = lcTranslateXML + [ </skus>] + CRLF
2482 lcTranslateXML = lcTranslateXML + "</style> "
2483
2484 *--- FGCjr 08/05/11
2485 SET EXACT &lcOldExact
2486 *=== FGCjr 08/05/11
2487
2488 *IF NOT EMPTY(lcAvailXML)
2489 * lcXML = lcXML + [ <availabilities>] + CRLF
2490 * lcXML = lcXML + lcAvailXML
2491 * lcXML = lcXML + [ </availabilities>] + CRLF
2492 *ENDIF
2493
2494 IF !llFoundImage
2495 llFoundImage = .AddMainImage(tcHeader.division,tcHeader.fkey,"tcStylr_img")
2496 ENDIF
2497
2498 lcImage = .GetImageXML(lcProdNum)
2499
2500 IF NOT EMPTY(lcImage)
2501 lcXML = lcXML + lcImage
2502 ENDIF
2503
2504
2505 lcXML = lcHeaderXML + lcXML + "</style> "
2506
2507 IF .lOnlyAvailability
2508 lcXML = lcHeaderXML
2509 *lcXML = lcXML + [ <skus>] + CRLF + lcskuXML + [ </skus>] + CRLF
2510
2511 IF .lOnlyAvailability_includePrice
2512 lcXML = lcXML + lcPriceXML
2513 ENDIF
2514
2515 lcXML = lcXML + [ <availabilities>] + CRLF + lcAvailXML + [ </availabilities>] + CRLF
2516 lcXML = lcXML + "</style> "
2517 ENDIF
2518
2519
2520 IF .lSendTranslations
2521 lcXML = lcTranslateXML
2522 ENDIF
2523
2524
2525 SELECT tcXML
2526 APPEND BLANK
2527
2528 * Doing a strtan to clear any date fields with today as the date. this should only clear inventory. we do not want to resend if inventory has not changed since yesterday
2529
2530 replace cProdNum WITH allt(lcProdNum), cXML WITH lcXML, division WITH tcHeader.division,;
2531 active_ok WITH tcHeader.active_ok,;
2532 image_file WITH lcImageFile,;
2533 uploaded WITH 'N',;
2534 xml_hash WITH SYS(2007,STRTRAN(lcXML,"<date>" +This.getDateFormat(DATE()) + "</date>",""))
2535
2536
2537 SELECT tcHeader
2538 ENDSCAN
2539
2540 .oLog.LogResult(llRetVal, lcHeading)
2541 .oLog.LogEntry("")
2542 ENDWITH
2543
2544 RETURN llRetVal
2545 ENDPROC
2546
2547 *============================================================
2548 *============================================================
2549
2550 * Format availability number
2551 * Make sure it is not negative, not less than the minimum, and not greater than the maximum
2552 PROCEDURE getDisplayAvailability
2553 LPARAMETERS tnQty
2554
2555 LOCAL lcQty
2556
2557 tnQty = MAX(tnQty,0)
2558
2559 IF .nMinimumUnits > 0 AND tnQty < .nMinimumUnits
2560 tnQty = 0
2561 ENDIF
2562
2563 IF .nMaximumUnits > 0 AND tnQty > .nMaximumUnits
2564 tnQty = .nMaximumUnits
2565 ENDIF
2566
2567 lcQty = TRANSFORM(tnQty)
2568
2569 RETURN lcQty
2570
2571 endproc
2572
2573
2574 *----------------------------------------------------------
2575 PROCEDURE PostToThreadvine
2576
2577 LOCAL lcHeading, llRetVal, loXML, lcResult, lcSite,lcUser,lcPasswd
2578 WITH THIS
2579 lcHeading = "Sending XML files to Threadvine..."
2580 .UpdateThermoCaption(lcHeading)
2581 .LogMajorStage(lcHeading)
2582 .oLog.LogEntry("")
2583
2584 llRetVal = true
2585
2586 SELECT tcXML
2587 lnRecs = recc('tcXML')
2588 lnThermo = 50
2589
2590 *--- TechRec 1077508 28-Apr-2014 TSV---
2591*!* lcHistDirectory = ADDBS(goEnv.SystemParm(SP_PATH_INTERFACEHST)) + "Threadvine\"
2592*!* IF NOT DIRECTORY(lcHistDirectory)
2593*!* MKDIR (lcHistDirectory)
2594*!* ENDIF
2595
2596
2597*!* lcHistDirectory = lcHistDirectory + "Style\"
2598*!* IF NOT DIRECTORY(lcHistDirectory)
2599*!* MKDIR (lcHistDirectory)
2600*!* ENDIF
2601
2602 lcHistDirectory = .GetHistoryDir("Styles")
2603 *=== TechRec 1077508 28-Apr-2014 TSV===
2604
2605
2606 IF .cImageHandling == "I"
2607
2608 .LogEntry("Image Upload Handling set to I. Not sending SKU Info.")
2609 ELSE
2610
2611 SCAN
2612
2613
2614 IF .lForceResend
2615 replace uploaded WITH "Y" IN tcXML
2616 ENDIF
2617
2618 IF SEEK(allt(tcXML.cProdNum),"tcHist","csku")
2619
2620 IF EVALUATE(IIF(.lOnlyAvailability,"tcHist.avail_checksum","tcHist.prod_checksum")) <> tcXML.xml_hash
2621 replace uploaded WITH "Y" IN tcXML
2622 ENDIF
2623
2624 ELSE
2625
2626
2627 * Last check, if lUpdateExisting is true then do not do this. user does not want to create new products.
2628 IF !.lUpdateExisting
2629 * Product is not in history table. Uploading.
2630 replace uploaded WITH "Y" IN tcXML
2631 ENDIF
2632 ENDIF
2633
2634
2635
2636 IF tcXML.uploaded <> "Y" AND (.cDeleteFlag<>"Y")
2637 *.LogEntry("Skipping Product, no update required: " + allt(tcXML.cProdNum))
2638 .nSkippedStyles = .nSkippedStyles + 1
2639 ELSE
2640
2641 * Get username, password, site for the division
2642 *IF SEEK(tcXML.division,"zzxthvnc_local","div") OR SEEK(" ","zzxthvnc_local","div")
2643
2644 lcSite = LOWER(ALLTRIM(.oControl.site_url))
2645 lcUser = ALLTRIM(.oControl.tv_user_id)
2646 *lcPasswd = BCDecrypto(ALLTRIM(.oControl.tv_passwd))
2647 lcPasswd = (ALLTRIM(.oControl.tv_passwd))
2648
2649 * append / to the end of the site_url if it is not there.
2650 IF RIGHT(lcSite,1) <> "/"
2651 lcSite = lcSite + "/"
2652 ENDIF
2653
2654 IF ALLTRIM(tcXML.cProdNum)=="MERCHGROUPSXLATE"
2655 lcSite = lcSite + "style_tag/create.xml"
2656 ELSE
2657 IF RIGHT(lcSite,6) <> "style/"
2658 lcSite = lcSite + "style/"
2659 ENDIF
2660 ENDIF
2661
2662
2663
2664
2665 =STRTOFILE(ALLTRIM(tcXML.cXML),lcHistDirectory + allt(tcXML.cProdNum)+"_" + TTOC(DATETIME(),1) + ".xml")
2666 IF ALLTRIM(tcXML.cProdNum)=="MERCHGROUPSXLATE"
2667 .cUrl = lcSite
2668 ELSE
2669 .cUrl = lcSite+allt(tcXML.cProdNum)+".xml"
2670 ENDIF
2671 .cSharedKeyName = lcUser
2672 .cSharedKeyValue = lcPasswd
2673 .cBody = ALLTRIM(tcXML.cXML)
2674
2675 lcStart = .GetServerTime() && TR 1077923 KISHOR 11-APR-2014
2676 IF .GetTVObject()
2677 .PostXML((.cDeleteFlag=="Y"))
2678 *--- TR 1077923 KISHOR 11-APR-2014
2679 lcEnd = .GetServerTime()
2680 lcTime = .GetTimeDiff(lcStart, lcEnd)
2681 *=== TR 1077923 KISHOR 11-APR-2014
2682
2683 .AdvanceThermo(lnThermo+int(40/lnRecs))
2684 lnThermo = lnThermo+int(40/lnRecs)
2685
2686 REPLACE tcXML.cXMLResult WITH .cResponseXML
2687 *--- TR 1077923 KISHOR 11-APR-2014
2688 *.oLog.LogEntry("Processing " + allt(cProdNum) + " ===> " + .cStatusCode)
2689 lcLogMessage = "Processing " + allt(cProdNum) + " ===> " + ALLTRIM(.cStatusCode) + " (" + TRANSFORM(lcTime) + ")"
2690 .oLog.LogEntry(lcLogMessage)
2691 *=== TR 1077923 KISHOR 11-APR-2014
2692
2693 if left(.cStatusCode ,2) = '20'
2694 .nSuccessStyles = .nSuccessStyles + 1
2695 ELSE
2696 IF LEFT(.cStatusCode,3) == "404" AND tcXML.active_ok <> "Y"
2697 .LogEntry("Product does not exist in Threadvine, no need to deactivate.")
2698 ELSE
2699 replace uploaded WITH 'N' IN tcXML
2700 ENDIF
2701 .nFailedStyles = .nFailedStyles + 1
2702 endif
2703
2704 .LogWarningsAndErrors()
2705 ENDIF
2706 *ENDIF && Found control
2707 ENDIF &&uploaded = "Y"
2708 ENDSCAN
2709 ENDIF
2710
2711 lcHeading = "Sending Image files to Threadvine..."
2712 .UpdateThermoCaption(lcHeading)
2713 .LogMajorStage(lcHeading)
2714 LOCAL lcByteFile
2715 * Now do images
2716 SELECT tcXML
2717
2718 IF .cImageHandling == "N" OR (.cDeleteFlag=="Y")
2719 .LogEntry("Image Upload Handling set to N. Not sending images.")
2720 ELSE
2721 IF NOT EMPTY(.cImageCursor) AND USED(.cImageCursor )
2722 SELECT (.cImageCursor)
2723 SCAN
2724
2725 SELECT (.cImageCursor)
2726 SCATTER NAME loImg MEMO
2727
2728 lcByteFile = ""
2729 * Get username, password, site for the division
2730 *IF SEEK(loImg.division,"zzxthvnc_local","div") OR SEEK(" ","zzxthvnc_local","div")
2731
2732 lcSite = LOWER(ALLTRIM(.oControl.site_url))
2733 lcUser = ALLTRIM(.oControl.tv_user_id)
2734 *lcPasswd = BCDecrypto(ALLTRIM(.oControl.tv_passwd))
2735 lcPasswd = (ALLTRIM(.oControl.tv_passwd))
2736
2737 * append / to the end of the site_url if it is not there.
2738 IF RIGHT(lcSite,1) <> "/"
2739 lcSite = lcSite + "/"
2740 ENDIF
2741 IF RIGHT(lcSite,6) <> "style/"
2742 lcSite = lcSite + "style/"
2743 ENDIF
2744
2745
2746 IF NOT FILE(loImg.cImage)
2747 .LogEntry("File Not Found: " + ALLTRIM(loImg.cImage))
2748 .nImageFailed = .nImageFailed + 1
2749 ELSE
2750 IF .GetTVObject()
2751
2752 lcUrl = lcSite+allt(loImg.cProdNum)+".xml"
2753 lcURL = lcSite + allt(loImg.cProdNum) + "/image/" + ALLTRIM(loimg.cImageType) + ".jpg"
2754
2755 .cUrl = lcURL
2756 .cSharedKeyName = lcUser
2757 .cSharedKeyValue = lcPasswd
2758 .cImageFile = ALLTRIM(loImg.cImage)
2759
2760 .LogEntry("Product: " + ALLTRIM(loImg.cProdNum))
2761 .LogEntry("...Uploading image: " + ALLTRIM(loImg.cImage))
2762 .PostImage()
2763 .LogEntry("........ ===> " + .cStatusCode)
2764 .LogWarningsAndErrors()
2765
2766 ENDIF
2767 ENDIF
2768 *ENDIF
2769 ENDSCAN
2770 ENDIF
2771 ENDIF
2772
2773 .AdvanceThermoTotal(90)
2774
2775 .oLog.LogEntry("")
2776 .oLog.LogResult(llRetVal, lcHeading)
2777 .oLog.LogEntry("")
2778 ENDWITH
2779
2780 ENDPROC
2781
2782
2783
2784 *-----------------------------------------------
2785 PROCEDURE UpdateSkuHistory
2786
2787 LOCAL llRetVal,lnSelect,loSB,lcSQL,lcTempTable
2788 llRetVal = .T.
2789 lnSelect = SELECT()
2790
2791
2792 *IF THIS.lOnlyAvailability
2793 IF .F.
2794
2795 llRetVal = llRetVal AND THIS.generatesqltemptable("tcNewAval")
2796 IF llRetVal
2797 THIS.populatesqltemptable("tcNewAval")
2798 ENDIF
2799 lcTempTable = THIS.cSQLTEMPTABLE
2800
2801
2802 lcSQL = " Update zzxthvsa " + ;
2803 " SET avail_checksum = r.avail_checksum " + ;
2804 " , last_mod = " + SQLFormatTs(DATETIME()) + ;
2805 " , user_id = " + SQLFormatChar(goEnv.envLogin.cUserName) + ;
2806 " FROM " + lcTempTable + " r " + ;
2807 " WHERE r.division = zzxthvsa.division " + ;
2808 " AND r.style = zzxthvsa.style" + ;
2809 " AND r.color_code = zzxthvsa.color_code" +;
2810 " ANd r.lbl_code = zzxthvsa.lbl_Code " + ;
2811 " AND r.dimension =zzxthvsa.dimension "
2812 llRetVal = llRetVal AND v_SQlExec(lcSQL)
2813
2814 loSB = NEWOBJECT("SQLStringBuilder","clsgnstr.fxp")
2815 loSB.SB_Reset()
2816 loSB.cSB_Target = "zzxthvsa"
2817 loSB.cSB_Source = lcTempTable
2818 loSB.cSB_SourceAlias = "r"
2819
2820 loSB.cSB_WHEREString = "NOT EXISTS( SELECT * FROM zzxthvsa tbl WHERE " + ;
2821 " tbl.Division = r.Division " + + ;
2822 " AND tbl.Style = r.Style " + ;
2823 " AND tbl.color_code = r.color_code " +;
2824 " AND tbl.lbl_code = r.lbl_code "+;
2825 " AND tbl.dimension = r.dimension ) "
2826
2827
2828 llRetVal = loSB.SB_GenerateSQLInsertString()
2829
2830 IF llRetVal
2831 lcSQL = loSB.cSQLString
2832 ENDIF
2833
2834 llRetVal = llRetVal AND V_SQLExec(lcSQL)
2835
2836 ENDIF
2837
2838 SELECT (lnSelect)
2839 RETURN llRetVal
2840
2841 ENDPROC
2842
2843 *--- TR 1077923 KISHOR 11-APR-2014
2844 FUNCTION GetServerTime
2845 LOCAL lcTime, lcSQL, lcTempFile, lnSelect
2846
2847*!* lcTime = ''
2848*!* lnSelect = SELECT()
2849*!* lcTempFile = GetUniqueFileName()
2850*!* lcSQL = "select convert(time(3),getdate()) as vtime"
2851*!* This.vSQLExec(lcSQL, lcTempFile)
2852*!*
2853*!* IF USED(lcTempFile)
2854*!* SELECT (lcTempFile)
2855*!* lcTime = SysGetFieldValue(lcTempFile, 'vtime')
2856*!* USE
2857*!* ENDIF
2858*!*
2859
2860 RETURN SECONDS()
2861
2862 SELECT (lnSelect)
2863 RETURN lcTime
2864 ENDFUNC
2865
2866 FUNCTION GetTimeDiff
2867 LPARAMETERS lcStart, lcEnd
2868 LOCAL lcSQL, lcDiff, lcTempFile, lnSelect
2869
2870 *lnSelect = SELECT()
2871 *lcTempFile = GetUniqueFileName()
2872 *lcDiff = ''
2873
2874*!* lcSQL = TEXTMERGE("select Convert(time(3),(Convert(datetime,'<<lcEnd>>') - Convert(datetime,'<<lcStart>>')),8) as diffr")
2875*!* This.vSQLExec(lcSQL, lcTempFile)
2876*!*
2877*!* IF USED(lcTempFile)
2878*!* SELECT (lcTempFile)
2879*!* lcDiff = SysGetFieldValue(lcTempFile, 'diffr')
2880*!* IF NOT EMPTY(lcDiff)
2881*!* lcDiff = RIGHT(lcDiff, 6)
2882*!* ENDIF
2883*!* USE
2884*!* ENDIF
2885*!*
2886*!* SELECT (lnSelect)
2887
2888
2889 lcDiff = TRANSFORM(lcEnd - lcStart)
2890
2891 RETURN lcDiff
2892 ENDFUNC
2893 *=== TR 1077923 KISHOR 11-APR-2014
2894
2895
2896 PROCEDURE BuildPrePackContent
2897
2898 LOCAL llRetVal,lnSelect,lnKey,lnTotal, lcOneLine, lcContent, lnCnt, lcProdNum, lcMasterProdNum
2899 llRetVal= .T.
2900 lnSelect = SELECT()
2901
2902 CREATE CURSOR tcPpkContent (sc_key i, ppk_contents m, cProductNum c(200))
2903
2904 IF THIS.lSplitPacks
2905
2906 SELECT tcPPK_1
2907 DO WHILE NOT EOF("tcPPK_1")
2908
2909 lnKey = tcPPK_1.pkey
2910 lnTotal = 0
2911 lnCnt = 0
2912 lcMasterProdNum = tcPPK_1.cProductNum
2913 lcContent = [<table class="data"><thead>] + CRLF
2914 lcContent = lcContent + [<tr><th colspan="5" class="table-head">Prepack Contents</th>] + CRLF
2915 lcContent = lcContent + [</tr><tr><th>Product</th><th>Style</th><th>Color</th><th>Size</th><th style="text-align:center">Qty</th></tr></thead><tbody>] + CRLF
2916
2917 SCAN WHILE tcPPK_1.pkey = lnKey
2918 lnTotal = lnTotal + tcPPK_1.pack_qty
2919 lnCnt = lnCnt + 1
2920 lcProdNum = ALLTRIM(tcPPK_1.division) + "-" + ALLTRIM(tcPPK_1.style) + "-" + ALLTRIM(tcPPK_1.color_code) + IIF(EMPTY(tcPPK_1.Lbl_Code),"","-"+tcPPK_1.Lbl_Code)
2921 lcContent = lcContent + [<tr class="] + IIF(MOD(lnCnt,2)=0,'even','odd')+ ["><td>]
2922 lcContent = lcContent + [<a href="/s/search?category=style&terms=] + ALLTRIM(tcPPK_1.UPC)+ [+-prepack">] + ALLTRIM(lcProdNum) + [</a></td></td>]
2923 lcContent = lcContent + [<td>] + ALLTRIM(tcPPK_1.style) + [</td><td>] + ALLTRIM(tcPPK_1.Color_Name) + [</td>]
2924 lcContent = lcContent + [<td>] + ALLTRIM(tcPPK_1.Size_Desc) + [</td><td style="text-align:center">] + TRANSFORM(tcPPK_1.pack_qty) + [</td></tr>]
2925 ENDSCAN
2926
2927 lcContent = lcContent + [</tbody><tfoot><tr>]
2928 lcContent = lcContent + [<th colspan="4" style="text-align:right">Prepack Quantity</th>]
2929 lcContent = lcContent + [<th style="text-align:center">]+TRANSFORM(lnTotal)+[</th></tr></tfoot></table>]
2930
2931 INSERT INTO tcPpkContent VALUES (lnKey , lcContent , lcMasterProdNum )
2932
2933
2934 ENDDO
2935 ENDIF
2936
2937 SELECT (lnSelect)
2938 RETURN llRetVal
2939
2940 ENDPROC
2941
2942 PROCEDURE getTVLangFromBCLang
2943 LPARAMETERS tcSource
2944
2945 LOCAL lcRetVal
2946 tcSource = ALLTRIM(UPPER(tcSource))
2947
2948 DO CASE
2949 CASE tcSource == "ENG"
2950 lcRetVal = "en_US"
2951 CASE tcSource == "FRE"
2952 lcRetVal = "fr_FR"
2953 CASE tcSource == "GER"
2954 lcRetVal = "de_DE"
2955 CASE tcSource == "SPA"
2956 lcRetVal = "es_ES"
2957 CASE tcSource == "ITA"
2958 lcRetVal = "it_IT"
2959 OTHERWISE
2960 lcRetVal = "en_US"
2961 ENDCASE
2962
2963 RETURN lcRetVal
2964 ENDPROC
2965
2966 *---------------------------------------------------------------------
2967 PROCEDURE exportTranslation
2968 LPARAMETERS tcTopRoot, tcCursor, tnKey, tcDefault
2969
2970 LOCAL lcXml, lcLangCode, lcContent,lnSelect, llFoundEng
2971 lnSelect = SELECT()
2972 llFoundEng = .F.
2973
2974 lcXml = ""
2975
2976
2977 *IF NOT SEEK(tnKey,tcCursor,"fkey")
2978 IF NOT SEEK(PADL(tnKey,10,"0")+PADL(1,10,"0"),tcCursor,"fkey")
2979 *lcXml = lcXml + [ <] + tcTopRoot + [>] + CRLF
2980 *lcXml = lcXml + " <![CDATA["+ tcDefault +"]]>"
2981 *lcXml = lcXml + [ </] + tcTopRoot + [>] + CRLF
2982 ELSE
2983
2984 lcXml = lcXml + [ <] + tcTopRoot+ [>] + CRLF
2985 lcXml = lcXml + [ <translations>] + CRLF
2986
2987 SELECT (tcCursor)
2988 SCAN WHILE fkey = tnKey
2989
2990 lcLangCode = ALLTRIM(sysgetfieldvalue(tcCursor,"langcode"))
2991 lcContent = ALLTRIM(sysgetfieldvalue(tcCursor,"translated_content"))
2992 *lcLangXLate = .getTVLangFromBCLang(lcLangCode)
2993 lcLangXLate = ALLT(sysgetfieldvalue(tcCursor,"localeid"))
2994 lcXml = lcXml + [ <translation locale="] + ;
2995 lcLangXLate + [">] + ;
2996 "<![CDATA["+ lcContent +"]]></translation>" + CRLF
2997
2998
2999 IF lcLangCode=="ENG"
3000 llFoundEng = .T.
3001 ENDIF
3002
3003 ENDSCAN
3004
3005 IF NOT llFoundEng
3006
3007
3008 IF SEEK("ENG"+PADL(1,10,"0"),"tcLangCodes","lang")
3009 select tcLangCodes
3010 SCAN WHILE langcode == "ENG"
3011 lcXml = lcXml + [ <translation locale="]+ALLTRIM(tcLangCodes.localeid)+[">] + "<![CDATA["+ ALLTRIM(tcDefault) +"]]></translation>" + CRLF
3012 ENDSCAN
3013 SELECT (tcCursor)
3014 ENDIF
3015 ENDIF
3016
3017 lcXml = lcXml + [ </translations>] + CRLF
3018 lcXml = lcXml + [ </] + tcTopRoot + [>] + CRLF
3019 ENDIF
3020
3021
3022 SELECT (lnSelect)
3023 RETURN lcXML
3024
3025 endproc
3026
3027 PROCEDURE exportTagTranslation
3028 LPARAMETERS lcAttribute, tcCursor, tnKey, tcDefault, tlNoTranslate
3029
3030 LOCAL lcXml, lcLangCode, lcContent,lnSelect, llFoundEng
3031 lnSelect = SELECT()
3032 llFoundEng = .F.
3033
3034 lcXml = ""
3035
3036
3037 *IF NOT SEEK(tnKey,tcCursor,"fkey")
3038 IF tlNoTranslate
3039 lcXml = lcXml + [ <tag attribute="] + lcAttribute + [">] + CRLF
3040 lcXml = lcXml + " <![CDATA["+ tcDefault +"]]>"
3041 lcXml = lcXml + [ </tag>] + CRLF
3042 ELSE
3043
3044 lcXml = lcXml + [ <tag attribute="] + lcAttribute + [">] + CRLF
3045 lcXml = lcXml + [ <translations>] + CRLF
3046
3047 SELECT (tcCursor)
3048 SCAN WHILE fkey = tnKey
3049
3050 lcLangCode = ALLTRIM(sysgetfieldvalue(tcCursor,"langcode"))
3051 lcContent = ALLTRIM(sysgetfieldvalue(tcCursor,"translated_content"))
3052 *lcLangXLate = .getTVLangFromBCLang(lcLangCode)
3053 lcLangXLate = ALLT(sysgetfieldvalue(tcCursor,"localeid"))
3054
3055 lcXml = lcXml + [ <translation locale="] + ;
3056 lcLangXLate + [">] + ;
3057 "<![CDATA["+ lcContent +"]]></translation>" + CRLF
3058
3059
3060 IF lcLangCode=="ENG"
3061 llFoundEng = .T.
3062 ENDIF
3063
3064 ENDSCAN
3065
3066 IF NOT llFoundEng
3067 IF SEEK("ENG"+PADL(1,10,"0"),"tcLangCodes","lang")
3068 select tcLangCodes
3069 SCAN WHILE langcode == "ENG"
3070 lcXml = lcXml + [ <translation locale="]+ALLTRIM(tcLangCodes.localeid)+[">] + "<![CDATA["+ ALLTRIM(tcDefault) +"]]></translation>" + CRLF
3071 ENDSCAN
3072 select (tcCursor)
3073 ENDIF
3074 ENDIF
3075
3076 lcXml = lcXml + [ </translations>] + CRLF
3077 lcXml = lcXml + [ </tag>] + CRLF
3078 ENDIF
3079
3080
3081 SELECT (lnSelect)
3082 RETURN lcXML
3083
3084 endproc
3085
3086
3087 PROCEDURE exportDescriptors
3088 LPARAMETERS tnKey, tcCursor, tlHideDescriptor, tlNoTranslate
3089
3090 LOCAL lcXml, lcLangCode, lcContent,lnSelect, llFoundEng, lcAttribute
3091 lnSelect = SELECT()
3092 llFoundEng = .F.
3093
3094 * Gore asked to wrap this in <ul><li> </li></ul> Then replace all piples w/ </li><li>
3095
3096 lcXml = ""
3097
3098 * n.notes,t.THREADVINE_DESC,s.fkey,t.LANGCODE
3099 IF SEEK(PADL(tnKey,10,"0")+PADL(1,10,"0"),tcCursor,"fkey")
3100 IF !tlHideDescriptor
3101 lcXml = lcXml + [ <descriptors>] + CRLF
3102 ENDIF
3103
3104 DO WHILE NOT EOF(tcCursor) AND fkey = tnKey
3105
3106 lcAttribute = ALLTRIM(sysgetfieldvalue(tcCursor,"THREADVINE_DESC"))
3107
3108 lcXml = lcXml + [ <tag attribute="] + lcAttribute + [">] + CRLF
3109
3110 IF NOT tlNoTranslate
3111 lcXml = lcXml + [ <translations>] + CRLF
3112 ENDIF
3113
3114 SELECT (tcCursor)
3115 SCAN WHILE fkey = tnKey AND ALLTRIM(UPPER(THREADVINE_DESC)) == ALLTRIM(UPPER(lcAttribute))
3116
3117 lcLangCode = ALLTRIM(sysgetfieldvalue(tcCursor,"langcode"))
3118 lcContent = ALLTRIM(sysgetfieldvalue(tcCursor,"notes"))
3119 *lcLangXLate = .getTVLangFromBCLang(lcLangCode)
3120 lcLangXLate = ALLT(sysgetfieldvalue(tcCursor,"localeid"))
3121
3122 * if this is a descriptor, replace all pipes w/ set text
3123 * NOTE: make generic during codemerge
3124 if tlHideDescriptor
3125 IF NOT tlNoTranslate
3126 lcXml = lcXml + [ <translation locale="] + ;
3127 lcLangXLate + [">] + ;
3128 "<![CDATA["+ lcContent + "]]></translation>" + CRLF
3129 ELSE
3130 lcXml = lcXml + "<![CDATA["+ lcContent + "]]>" + CRLF
3131 ENDIF
3132
3133 else
3134 lcXml = lcXml + [ <translation locale="] + ;
3135 lcLangXLate + [">] + ;
3136 "<![CDATA["+ ;
3137 STRTRAN(lcContent,"|"," • ") + "]]></translation>" + CRLF
3138 endif
3139 ENDSCAN
3140
3141 IF NOT tlNoTranslate
3142 lcXml = lcXml + [ </translations>] + CRLF
3143 ENDIF
3144
3145 lcXml = lcXml + [ </tag>] + CRLF
3146
3147 ENDDO
3148 IF !tlHideDescriptor
3149 lcXml = lcXml + [ </descriptors>] + CRLF
3150 ENDIF
3151 ENDIF
3152
3153
3154 SELECT (lnSelect)
3155 RETURN lcXML
3156
3157 endproc
3158
3159 PROCEDURE LoadLocalRegionLangTemp
3160
3161 LOCAL llRetVal,lnSelect
3162 llRetVal = .T.
3163
3164
3165 * long terms load into misc user reference or zzxlookp or somewhere else. hardcode for now
3166 WITH THIS
3167 .cLangLocaleTmp = "#" + getuniquefilename()
3168 llRetVal = llRetVal AND v_sqlexec(;
3169 "CREATE TABLE " + .cLangLocaleTmp + " (langcode char(3), localeid char(10), size_region char(3))")
3170
3171 llRetVal = llRetVal AND v_sqlexec("INSERT INTO " + .cLangLocaleTmp + " VALUES ('ENG','en_US','SUS')")
3172 llRetVal = llRetVal AND v_sqlexec("INSERT INTO " + .cLangLocaleTmp + " VALUES ('FRE','fr_FR','SEU')")
3173 llRetVal = llRetVal AND v_sqlexec("INSERT INTO " + .cLangLocaleTmp + " VALUES ('GER','de_DE','SEU')")
3174 llRetVal = llRetVal AND v_sqlexec("INSERT INTO " + .cLangLocaleTmp + " VALUES ('SPA','es_ES','SEU')")
3175 llRetVal = llRetVal AND v_sqlexec("INSERT INTO " + .cLangLocaleTmp + " VALUES ('SPA','es_US','SUS')")
3176 llRetVal = llRetVal AND v_sqlexec("INSERT INTO " + .cLangLocaleTmp + " VALUES ('ITA','it_IT','SEU')")
3177 llRetVal = llRetVal AND v_sqlexec("INSERT INTO " + .cLangLocaleTmp + " VALUES ('ENG','en_GB','SUK')")
3178
3179 ENDWITH
3180
3181 llRetVal = llRetVal AND v_sqlexec("SELECT * " + ;
3182 ",CAST(ROW_NUMBER() OVER (PARTITION by langcode ORDER BY localeid) as INT) as sort_seq " + ;
3183 " FROM " + .cLangLocaleTmp,"tcLangCodes")
3184 IF llRetVal AND USED("tcLangCodes")
3185 SELECT tcLangCodes
3186 INDEX on size_region TAG reg
3187 INDEX on ALLTRIM(langcode)+PADL(sort_seq,10,"0") TAG lang
3188 ENDIF
3189
3190 RETURN llRetVAl
3191
3192
3193 ENDPROC
3194
3195 *------------------------------------------------------------
3196 PROCEDURE GenerateMerchGroupServiceXml
3197
3198 LOCAL lcXML
3199 lcXML = ""
3200 lcXML = lcXML + [<?xml version="1.0" encoding="UTF-8"?>] + CRLF
3201 lcXML = lcXML + [<styleTags tag="overwrite,create">] + CRLF
3202 lcXML = lcXML + [ <tags>] + CRLF
3203
3204 SELECT tcMerch_trns
3205 DO WHILE NOT EOF("tcMerch_trns")
3206 SCATTER NAME lo1 MEMO
3207 lcField = "GROUP_CODE" + TRANSFORM((ASC(UPPER(lo1.group_type))-64) )
3208 lcGroupName = ""
3209
3210 * Get merch desc from language ref
3211 lcGroupName = i(lcField)
3212
3213 lcXML = lcXML + [ <tag attribute="]+lcGroupName+[">] + CRLF
3214 lcXML = lcXML + [ <translations>] + CRLF
3215
3216
3217 SCAN WHILE division == lo1.division AND group_code == lo1.group_code AND group_type = lo1.group_type
3218 lcXml = lcXml + [ <translation locale="] + ;
3219 ALLTRIM(tcMerch_trns.localeid) + [">] + ;
3220 "<![CDATA["+ ALLTRIM(tcMerch_trns.translated_content) +"]]></translation>" + CRLF
3221 ENDSCAN
3222
3223 lcXML = lcXML + [ </translations>] + CRLF
3224 lcXML = lcXML + [ </tag>] + CRLF
3225 ENDDO
3226
3227
3228 lcXML = lcXML + [ </tags>] + CRLF
3229 lcXML = lcXML + [</styleTags>] + CRLF
3230
3231
3232 RETURN lcXML
3233
3234
3235 ENDPROC
3236
3237
3238
3239ENDDEFINE