· 7 years ago · Feb 28, 2019, 02:34 PM
1*======================================================================
2* (C) Copyright 2001 CGS, Inc.
3* ALL RIGHTS RESERVED
4*======================================================================
5* Version....: 1.00.0
6* Author.....: K.Venkat
7* Date.......: 06/20/2008 07:57:22 AM
8* Platform...: Visual FoxPro 09.00.0000.3504 for Windows
9* Abstract...: TR 1031362
10* Cmd to Run.: g_oApp.m_Append("clsproc","clsproc",-1,.F.,"ZZEOIBPR")
11* Changes....: This Process outputs
12* 1. In Header - For the filtered location if there is OIB control is set ( or from the catch all exmpty location record) as per meta data
13* 2. In Detail - It is fetching the Locations wise SKUs ( UPC shuld exist in ZVEUPNR)
14* Fetching the Required Qty details from the new View ZVXSSUMHVSZLL_CALL ( CALLNN is taken care ) Taking the ETA_DATE from the ZZCORDRH for the skus
15* Changes....: Query corrected, Duplicate records were displaying if this table (zzxscocg) having more then one record for same SKU
16* for TR - 1101299 Dated on 09-Jan-2017
17*======================================================================
18
19#INCLUDE SYSTEM.h
20#INCLUDE EDI.h
21#DEFINE EDI_OIB_FILENAME "846.dat"
22
23#DEFINE EDI_OIB_TRANSACTION "OIB"
24
25#DEFINE EDI_LOC_TAG "LOC" && TR 1093677 06-Apr-2016 Partha
26
27DEFINE CLASS BPOOIBProcess AS BPOEDIBase
28
29 NAME = "BPOOIBProcess"
30 cTitle = "846(o) Process"
31 lSuppressWarning = True && To suppress error log warning msg UI
32 cSQLFilterString = ""
33
34 cJobID = "OIBProcess"
35
36 lUserInterface = False
37 Tie_Setup = False
38
39 cInterFaceHeader = "ZZEOIBTH"
40 cInterFaceDetail = "ZZEOIBTD"
41 cControlReference = "ZZEOIBCR"
42
43 cQ_846Dtl = ""
44 cQ_846Hdr = ""
45
46 *--- TechRec 1076524 27-Apr-2014 TSV---
47 cQ_846Adr = ""
48 *=== TechRec 1076524 27-Apr-2014 TSV===
49
50 *--- TechRec 1079208 29-Oct-2014 TSV---
51 lCustSku = False
52 *=== TechRec 1079208 29-Oct-2014 TSV===
53
54 *--- TechRec 1095862 24-May-2016 smullappilly ---
55 lZeroReportedOtsQty = False
56 *=== TechRec 1095862 24-May-2016 smullappilly ===
57
58 oSB = NULL
59
60 nTotalLoc = 0
61 nDtlExport = 0
62
63 *--- TR 1058987 7-Mar-2012 Goutam
64 nFailedValidation = 0
65 *=== TR 1058987 7-Mar-2012 Goutam
66
67 *--- TechRec 1059983 31-May-2012 MANI. ---
68 cAvail_Option = ""
69 nPercent_Avail = 100
70 cShowZeroQty = "" &&--- TechRec 1062747 18-Jul-2012 MANI. ===
71 nMinSzQtyAvl = "" &&--- TechRec 1062747 18-Jul-2012 MANI. ===
72 cNsrCnfTypeFlt = "" &&--- TechRec 1072901 30-Aug-2013 MANI. ===
73 *=== TechRec 1059983 31-May-2012 MANI. ===
74
75 *--- TR 1064279 7-Nov-2012 Goutam.
76 l846_Use_Priority100_OTS = false
77 *=== TR 1064279 7-Nov-2012 Goutam.
78
79 l846_OTS_QTY_FROM_AVAILABILITY_BY_STYLE = false && TR 1067015 11-Mar-2013 Partha
80
81 *--- TR 1072524 7-Aug-2013 Goutam
82 cBulkOrderType = ""
83 *=== TR 1072524 7-Aug-2013 Goutam
84
85 l846_OTS_FROM_AVAIL_BY_STYLE_FROM_ALL_LOC = false && TR 1071381 06-14-2013 RKI
86
87 *--- TR 1084609 30-Apr-2015 Partha --- !!!CHANGE IT TO 846 ONCE DONE TESTING!!!
88 cProcessID = '846'
89 cWorkflowName = '846 BC Process'
90 *=== TR 1084609 30-Apr-2015 Partha ===
91
92 *--- TechRec 1087873 29-Jul-2015 jisingh ---
93 lAllow846Override = false
94 *=== TechRec 1087873 29-Jul-2015 jisingh ===
95
96 *--- TechRec 1090249 29-Sep-2015 jisingh ---
97 cNsrCodeFilter = ""
98 cCnfTypeFilter = ""
99 *=== TechRec 1090249 29-Sep-2015 jisingh ===
100
101 cQ_846Loc = "" && TR 1093677 01-Apr-2016 Partha
102
103 *--- Tr 1091168 30-Nov-2015 Dilip ---
104 cNsrCode = ""
105 *=== Tr 1091168 30-Nov-2015 Dilip ===
106
107 cZzxcatgx = "" && FH 1093327
108*=====================================================
109 FUNCTION INIT
110 LPARAMETERS plUserInterface
111
112 LOCAL llRetVal,lcSQLString
113
114 llRetVal = DODEFAULT()
115
116 THIS.lUserInterface = plUserInterface
117
118 * --- TR 1035726 9/12/08 CM
119 *THIS.lScheduled = !plUserInterface
120 * === TR 1035726 9/12/08 CM
121
122 THIS.Tie_Setup = IIF(goEnv.sv("TIE_SETUP","N") = 'N' OR GoEnv.SV("BC_EDI_VERSION","") = "5.2", .F., .T.)
123
124 THIS.oSB = NEWOBJECT("SQLStringBuilder", "clsgnstr.prg")
125 llRetVal = llRetVal AND IsObject(THIS.oSB, True)
126
127 *--- TR 1064279 7-Nov-2012 Goutam.
128 IF llRetVal
129 This.l846_Use_Priority100_OTS = goEnv.sv("846_USE_PRIORITY100_OTS","N") = 'Y'
130 THIS.l846_OTS_QTY_FROM_AVAILABILITY_BY_STYLE = goEnv.sv("846_OTS_QTY_FROM_AVAILABILITY_BY_STYLE","N") = 'Y' && TR 1067015 11-Mar-2013 Partha
131 This.l846_OTS_FROM_AVAIL_BY_STYLE_FROM_ALL_LOC = goEnv.sv("846_OTS_FROM_AVAIL_BY_STYLE_FROM_ALL_LOC","N") = 'Y' && TR 1071381 06-14-2013 RKI
132 ENDIF
133 *=== TR 1064279 7-Nov-2012 Goutam.
134
135 RETURN llRetVal
136 ENDFUNC
137*=====================================================
138 FUNCTION DESTROY
139
140 WITH THIS
141 IF IsObject(.oSB, True)
142 .oSB.DESTROY()
143 .oSB = NULL
144 ENDIF
145
146 DODEFAULT()
147 ENDWITH
148 ENDFUNC
149*=====================================================
150 FUNCTION OIBProcess
151 LPARAMETER pcSQLFilterString, plScheduled
152
153 LOCAL llRetVal, lnSelect, llQueryBOL
154
155 llRetVal = True
156 lnSelect = SELECT()
157
158 *-- Path to SysLock directory on server (same place as Login.DBF)
159 *-- Suppress syslock processing if coming from carton weight entry
160 lcSyslockTablePath = ADDBS(goEnv.envLoginTablePath.VALUE)
161
162 IF NOT v_SysLock( lcSyslockTablePath+"SYSLOCK", THIS.cJobID, goEnv.cCompany)
163 RETURN False
164 ENDIF
165
166 WITH THIS
167 .lNoDataFound = False
168 .cSQLFilterString = pcSQLFilterString
169
170 * --- TR 1035726 9/12/08 CM
171 *.lScheduled = plScheduled
172 * === TR 1035726 9/12/08 CM
173
174 .oLog.OpenLog(.cJobID, I(.cJobID), .lScheduled)
175 .oLog.LogProgram("clsoibpr.prg")
176 .oLog.LogEntry("Filter Criteria: " + .cSQLFilterString)
177
178 .CreateFormProgressBar(.lScheduled)
179 .SetCaption(.cTitle)
180
181 lnThermoTotal = 5
182 .InitProgressBarTotal(lnThermoTotal, "Total " + .cTitle)
183
184 *--- TechRec 1059983 31-May-2012 MANI. ---
185 llRetVal = llRetVal AND .ValidateParamBro()
186 *=== TechRec 1059983 31-May-2012 MANI. ===
187
188 *--- TechRec 1087873 29-Jul-2015 jisingh ---
189 .lAllow846Override = NOT .l846_OTS_FROM_AVAIL_BY_STYLE_FROM_ALL_LOC AND ;
190 NOT .l846_OTS_QTY_FROM_AVAILABILITY_BY_STYLE AND ;
191 EMPTY(.cBulkOrderType) AND EMPTY(.BuildFilterForParamBro())
192 *=== TechRec 1087873 29-Jul-2015 jisingh ===
193
194 IF llRetVal
195
196 .DropTempTables()
197
198 .InitThermo(.1)
199 .UpdateThermoCaption("Selecting Location(s) to export...")
200 INKEY(0.2)
201
202 .oLog.LogEntry("Selecting Location(s) to export...") && 1066673
203 llQuerySKU = .FetchLocationData()
204
205 .oLog.LogEntry("Selecting Location(s) to export...completed") && 1066673
206 .AdvanceThermo(1)
207 .AdvanceThermoTotal(1)
208 INKEY(0.2)
209
210 IF NOT llQuerySKU OR .nTotalLoc = 0
211 .cMessage = 'No records to process.'
212 .lNoDataFound = True
213 .m_close()
214 .oLog.LogEntry(.cMessage)
215 ELSE
216 .InitThermo(.nDtlExport)
217 INKEY(0.2)
218
219 .oLog.LogMajorStage("Processing SKU(s)")
220 *--- TechRec 1076326 13-May-2014 asharma ---
221* moved inside UpdateMessage()
222* .oLog.LogEntry("Total records to export: " + ALLTRIM(STR(.nDtlExport)),.T.)
223* .oLog.LogEntry("Total records to process: " + ALLTRIM(STR(.nDtlExport)),.T.)
224 *=== TechRec 1076326 13-May-2014 asharma ===
225
226 .AdvanceThermoTotal(1)
227 INKEY(0.2)
228 .oLog.LogResult(llRetVal, "Processing SKU(s)")
229
230 llRetVal= llRetVal AND .DeletePreviousTransaction()
231 .AdvanceThermoTotal(1)
232
233 * Populate Data to Transaction Tables
234 llRetVal = llRetVal AND .PopulateTransaction()
235
236 *--- TechRec 1076524 27-Apr-2014 TSV---
237 llRetVal = llRetVal AND .GetAddressCursor()
238 *=== TechRec 1076524 27-Apr-2014 TSV===
239
240 *--- TechRec 1060583 19-Apr-2012 jisingh ---
241 IF .nTotalLoc = 0
242 .cMessage = "No records to process."
243 .lNoDataFound = true
244 .oLog.LogEntry(.cMessage)
245 ELSE
246 *=== TechRec 1060583 19-Apr-2012 jisingh ===
247
248 llRetVal= llRetVal AND .ValidateTransaction()
249
250 .AdvanceThermoTotal(1)
251
252 .AdvanceThermoTotalWithCaptionPlus("Creating flatfile...")
253 lcEDIPath = THIS.GetEDIFlatFileDirectory("Outbound")
254 lcOutBoundFlatFile = lcEDIPath + EDI_OIB_FILENAME
255
256 &&--- TechRec 1076524 27-Apr-2014 TSV added .cQ_846Adr ===
257 llRetVal= llRetVal AND .UnLoadInterfaceToFlatFile(EDI_OIB_TRANSACTION, lcOutBoundFlatFile, ;
258 .cInterFaceHeader, .cInterFaceDetail, .cQ_846Adr)
259
260 .AdvanceThermoTotal(1)
261
262 IF llRetVal
263 *--- TechRec 1061287 02-May-2012 jisingh Added AND (.nTotalLoc - .nFailedValidation) > 0 ===
264 *--- TechRec 1061287 02-May-2012 jisingh Added AND (.nTotalLoc - .nFailedValidation) > 0 ===
265*!* IF NOT .Tie_Setup AND (.nTotalLoc - .nFailedValidation) > 0
266*!* lcTempFlatfile = STRTRAN(UPPER(lcOutBoundFlatFile), ".DAT", ".TMP")
267*!* *--- TechRec 1060583 19-Apr-2012 jisingh ---
268*!* *llRetVal = .CopyToFlatFileInBlock(lcTempFlatfile, lcOutBoundFlatFile)
269*!* INSERT INTO __tcFlatFileList(TempFlatFile,OutBoundFlatFile) VALUES (lcTempFlatfile, lcOutBoundFlatFile)
270*!* *=== TechRec 1060583 19-Apr-2012 jisingh ===
271*!* ENDIF
272 *=== TR 1067890 03-22-2013 RKI ===*
273
274 .AdvanceThermoTotal(1)
275
276 llRetVal= llRetVal AND .UpdateMessage()
277
278 ENDIF
279
280 IF NOT llRetVal
281 .oLog.LogWarning("Flat file to export is incomplete. " + CRLF + ;
282 "Please examine the file " + lcOutBoundFlatFile + " before proceeding further" + CRLF + ;
283 "Then please make sure that you have enough disk space and " + CRLF + ;
284 "Your network connection is fine, then unflag all orders for this batch " + CRLF + ;
285 "and reprocess them." + CRLF)
286 ENDIF
287
288 *--- TechRec 1060583 19-Apr-2012 jisingh ---
289 llRetVal= llRetVal AND .UpdateCustomerPriceFlag()
290
291 *- ADDED here to prevent flat file from being generated should TableUpdateWithTransaction fail
292 *--- TechRec 1061287 02-May-2012 jisingh Added AND (.nTotalLoc - .nFailedValidation) > 0 ===
293 IF llRetVal AND (.nTotalLoc - .nFailedValidation) > 0
294 *--- TR 1084609 30-Apr-2015 Partha ---
295 IF .lRunWorkflow
296 .oLog.LogEntry("---------- Launching EDI workflow ----------", .T.)
297 .RunWorkflow("__tcWKFList")
298 *- If some files in __tcWKFList were not sent (RunWorkflow() set cr_auto_wkf = 'N')
299 *- then add them to __tcFlatFileList s that they are appended to 846.dat
300 INSERT INTO __tcFlatFileList SELECT TempFlatfile, lcOutBoundFlatFile ;
301 FROM __tcWKFList ;
302 WHERE cr_auto_wkf = 'N'
303 *- Add message to account for a workflow.
304 .cMessage = .cMessage + ;
305 "---------- EDI Workflow Summary ----------" + CRLF + ;
306 ALLT(Str(.nWorkflowProc)) + " Workflow(s) executed." + CRLF
307 .cMessage = .cMessage + ;
308 ALLT(Str(.nWorkflowSuccess)) + " Workflow(s) executed successfully." + CRLF
309 .oLog.LogEntry("---------- EDI Workflow Summary ----------")
310 .oLog.LogEntry(ALLT(Str(.nWorkflowProc)) + " Workflow(s) executed.")
311 .oLog.LogEntry(ALLT(Str(.nWorkflowSuccess)) + " Workflow(s) executed successfully.")
312 IF .nWorkflowProc - .nWorkflowSuccess > 0
313 .cMessage = .cMessage + ;
314 ALLT(Str(.nWorkflowProc - .nWorkflowSuccess)) + " Workflow(s) failed." + CRLF
315 .oLog.LogEntry(ALLT(Str(.nWorkflowProc - .nWorkflowSuccess)) + " Workflow(s) failed.")
316 ENDIF
317 .cMessage = .cMessage + "See History log for details."
318 ENDIF
319 *=== TR 1084609 30-Apr-2015 Partha ===
320
321 SELECT __tcFlatFileList
322
323 *--- TechRec 1076326 13-May-2014 asharma ---
324 *Total records to export
325 .oLog.LogEntry("Total records to export: " + ALLTRIM(STR(.nTotalLoc - .nFailedValidation)),.T.)
326 *=== TechRec 1076326 13-May-2014 asharma ===
327
328 SCAN
329 lcTempFlatfile = __tcFlatFileList.TempFlatFile
330 lcOutBoundFlatFile = __tcFlatFileList.OutBoundFlatFile
331 llRetVal = llRetVal AND .CopyToFlatFileInBlock(lcTempFlatfile, lcOutBoundFlatFile)
332 ENDSCAN
333 ENDIF
334 ENDIF
335 *=== TechRec 1060583 19-Apr-2012 jisingh ===
336
337 .m_close()
338 ENDIF
339
340 ENDIF
341
342 .DropTempTables()
343
344 IF IsObject(.oFrmProgressBar) AND NOT ISNULL(.oFrmProgressBar)
345 .m_close()
346 ENDIF
347
348 *-- Close Log
349 .oLog.LogResult(llRetVal)
350 .oLog.CloseLog()
351 ENDWITH
352
353 v_SysUnLock( lcSyslockTablePath+"SYSLOCK", THIS.cJobID, goEnv.cCompany)
354
355 SELECT (lnSelect)
356 RETURN llRetVal
357 ENDFUNC
358*=====================================================
359 FUNCTION FetchLocationData
360
361 LOCAL llRetVal, lnSelect, lcSQLFilterSting, lcSQLString, lcTempCursor, lnRecs ,lcPOSQLMainString , lcPOSubString
362 LOCAL lcTmpDtl,lcTmpHdr, lcQtyField , lcZzbaljoin ,lcSizeWhr ,lcsizeqty && TR 1050639 28-Jan-2011 Partha
363 &&--- TechRec 1059983 06-Jun-2012 MANI. Added lcQtyField,lcZzbaljoin,lcSizeWhr,lcsizeqty ===
364 *--- TR 1058987 28-Feb-2012 Goutam.
365 LOCAL lcCatgSubJoin,lcAvail_Qty1
366 &&--- TechRec 1062747 18-Jul-2012 MANI. Added lcAvail_Qty1 ===
367 *=== TR 1058987 28-Feb-2012 Goutam.
368
369 *--- TR 1061613 20-Jun-2012 Partha ---
370 &&--- TechRec 1072901 30-Aug-2013 MANI. Added lcNsrCnfSQL ===
371 LOCAL lcOptPercentage,lcPercentage,lcOptMinMult,lcRoundUpMinMult,lcRoundDownMinMult, lcNsrCnfSQL
372 STORE "" TO lcOptPercentage,lcPercentage,lcOptMinMult,lcRoundUpMinMult,lcRoundDownMinMult, lcNsrCnfSQL
373 *=== TR 1061613 20-Jun-2012 Partha ===
374
375 *--- TR 1063182 03-Aug-2012 Partha ---
376 LOCAL lcNearestMult
377 lcNearestMult = ""
378 *=== TR 1063182 03-Aug-2012 Partha ===
379
380 *--- TR 1064279 7-Nov-2012 Goutam.
381 LOCAL lcOrderSubSQL, lcOTSQtyCal
382 STORE "" TO lcOrderSubSQL, lcOTSQtyCal
383 *=== TR 1064279 7-Nov-2012 Goutam.
384
385 *--- TechRec 1059983 06-Jun-2012 MANI. ---
386 STORE "" TO lcQtyField,lcZzbaljoin, lcSizeWhr,lcsizeqty, lcAvail_Qty1
387 &&--- TechRec 1062747 18-Jul-2012 MANI. Added lcAvail_Qty1 ===
388 *=== TechRec 1059983 06-Jun-20DO 12 MANI. ===
389
390 *--- TR 1072524 7-Aug-2013 Goutam
391 LOCAL lcBulkOrderSubSQL
392 lcBulkOrderSubSQL = ""
393 *=== TR 1072524 7-Aug-2013 Goutam
394
395 *--- TR 1076805 14-Mar-2014 Partha ---
396 *--- TechRec 1087873 29-Jul-2015 jisingh Added lcLocations ===
397 LOCAL lcTempOTSQtyCal, lcTempQty, lcLocations
398 STORE "" TO lcTempOTSQtyCal, lcTempQty, lcLocations
399 *=== TR 1076805 14-Mar-2014 Partha ===
400
401 llRetVal = True
402 lnSelect = SELECT()
403 lcSQLFilterString = IIF(EMPTY(THIS.cSQLFilterString), " 1=1", THIS.cSQLFilterString)
404 lcTempCursor = GetUniqueFileName()
405 lnRecs = 0
406
407
408 WITH THIS
409 .oLog.LogMajorStage("Retrieving Location Records")
410
411 *--- TR 1034990 NSD 8/7/08 Added filter for location > '' from summary.
412 *--- TR 1040516 30-Jul-2009 Surinder Singh : Added c1.Supplier_num ===
413
414 *--- TR 1050639 28-Jan-2011 Partha ---
415
416 *--- TR 1058987 28-Feb-2012 Goutam. Added pkey as CtrlPkey, Customer, catg_dest and template in select list
417 *--- TR 1058987 28-Feb-2012 Goutam. Removed ZVXSSUMHVSZLL_call and replaced with zzxssumh and Added Order by
418 *--- TechRec 1060583 19-Apr-2012 jisingh Added use_threshold, threshold_qty & res_cprice ===
419 *--- TR 1061166 05-28-2012 RKI ---*
420
421 *--- TechRec 1066673 17-Jul-2013 GSternik ---
422 *-- SQLTableFromQuery cannot be used due to subselect. let's do the explicit INTO (no DB2 support anyway)
423
424 *-- All previous versions deleted... see VSS
425
426 *--- TechRec 1088815 22-Jul-2015 GSternik ---
427 *-- use the regular table instead of the # temp table
428*!* *-- Let's create the global temp table in Dev mode, so we can check it in the query analyzer:
429*!* .cQ_846Hdr = Iif(Version(2)=2,"##","#") + SubStr(SYS(2015), 2)
430 .cQ_846Hdr = '_846_' + SubStr(SYS(2015), 2)
431 *-- Cleanup
432 v_SqlExec("select Name from sysObjects where Name like '\_846\_%' escape '\' and Type = 'U'", "Left846tb")
433 Select Left846tb
434 Scan
435 v_SqlExecNoError("drop table " + Left846tb.Name )
436 EndScan
437 Use in Select("Left846tb")
438 *=== TechRec 1088815 22-Jul-2015 GSternik ===
439
440 *----------------------------------------------------------
441 * !!!
442 * TO DO: Parse the filter and apply LOCATION, CUSTOMER, and CATG_DEST filters (with alias "H.") to this query (to ZZEOIBCR table, C1 now)
443 * Figure out what to do about LOCGROUP filter
444 * filter sample ((U.DIVISION = '01')) AND ((H.CUSTOMER <>'560493')
445 * AND (H.CUSTOMER <>'560275') AND (H.CUSTOMER <>'393739')
446 * AND (H.CUSTOMER <>'560557') AND (H.CUSTOMER <>'560558'))
447 * !!!
448 *----------------------------------------------------------
449
450 lcSQLString =;
451 "select h.Location"+;
452 " , case when exists( "+;
453 "select *"+;
454 " from zzxwhsgd"+;
455 " where c1.Location = whs_grp) then c1.Location"+;
456 " else '' end as LocGroup"+;
457 " , c1.Vnd_Id"+;
458 " , c1.Vnd_Qual"+;
459 " , c1.Our_Qual"+;
460 " , c1.Our_Id"+;
461 " , c1.Active_Ok"+;
462 " , c1.Avl_Only"+;
463 " , convert(varchar(10), getdate(), 101) as report_date"+;
464 " , convert(Varchar, replace(convert(Varchar(8), getdate(), 108), ':', '')) as report_time"+;
465 " , c1.Supplier_Num"+;
466 " , c1.pkey as CtrlPkey"+;
467 " , c1.Customer"+;
468 " , c1.Catg_Dest"+;
469 " , c1.Template"+;
470 " , c1.Use_Threshold"+;
471 " , c1.Threshold_Qty"+;
472 " , c1.max_qty_send"+ ; &&--- TechRec 1090759 01-Dec-2015 vkrishnamurthy ===
473 " , c1.Res_Cprice"+;
474 " , c1.Percentage"+;
475 " , c1.Round_To"+;
476 " , c1.Min_Mult"+;
477 " , c1.Use_Excl"+;
478 " , c1.Add_Bulk"+;
479 " , c1.resv_loc_adr" + ; &&--- TechRec 1076524 10-Apr-2014 TSV added resv_loc_adr ===
480 " , c1.cust_loc_req"+; &&--- TR 1076961 KISHORE 6-MAR-2014
481 " , c1.auto_wkf CR_AUTO_WKF " + ; && TR 1084609 06-May-2015 Partha
482 " , c1.loc_recap " + ; && TR 1093677 01-Apr-2016 Partha
483 " , c1.override_ots_qty " + ; &&--- TechRec 1087873 29-Jul-2015 jisingh ===
484 " , COALESCE(p2.parm_value, p1.parm_value, '') AS ots_fields " + ; &&--- TechRec 1087873 29-Jul-2015 jisingh ===
485 " , COALESCE(xr.cust_loc,'') AS cust_loc "+;
486 " , COALESCE(xr.cust_loc_name,'') AS cust_loc_name " + ; &&=== TR 1076961 KISHORE 6-MAR-2014
487 " , c1.division ctrl_division " + ; && TR 1084425 30-Dec-2015 Partha
488 " , h.division " + ; &&*--- TR 1097211 18-Jun-2016 Goutam
489 " into " + .cQ_846Hdr +;
490 " from ( "+;
491 "select distinct Location,Division "+; && TR 1084425 30-Dec-2015 Partha
492 " from zzxssumh with (NoLock)) H"+;
493 " join ZZEOIBCR c1 with (NoLock)"+;
494 " on c1.Active_Ok = 'Y' "+;
495 " and h.Division = (CASE WHEN c1.division > '' THEN c1.division ELSE h.division END) " + ; && TR 1084425 30-Dec-2015 Partha
496 " and (c1.Location = h.Location or c1.Location = '' or exists ( "+;
497 "select ld.Location"+;
498 " from zzxwhsgh lh"+;
499 " join zzxwhsgd ld"+;
500 " on lh.pkey = ld.fkey"+;
501 " and lh.WHS_GRP = c1.Location"+;
502 " and ld.Location = H.Location ))"+;
503 " LEFT JOIN zzecloxr xr " + ; &&--- TR 1076961 KISHORE 6-MAR-2014
504 " ON xr.customer = c1.customer " + ;
505 " AND xr.location = c1.location " + ; &&=== TR 1076961 KISHORE 6-MAR-2014
506 " LEFT JOIN zzxparmr p1 ON p1.parm_code = '846OTSOVERRIDE' AND p1.location = 'ALL' " + ; &&--- TechRec 1087873 29-Jul-2015 jisingh ===
507 " LEFT JOIN zzxparmr p2 ON p2.parm_code = '846OTSOVERRIDE' AND p2.location = h.location " + ; &&--- TechRec 1087873 29-Jul-2015 jisingh ===
508 " order by c1.Location, c1.avl_only, c1.catg_dest, c1.Customer"
509
510 llRetVal = llRetVal and v_SqlExec(lcSqlString)
511 *=== TechRec 1066673 17-Jul-2013 GSternik ===
512 *--- TechRec 1087873 29-Jul-2015 jisingh ---
513 IF llRetVal AND NOT .lAllow846Override
514 * put non supported locations in log & delete
515 lcSQLString = " SELECT DISTINCT location FROM " + .cQ_846Hdr + ;
516 " WHERE override_ots_qty = 'Y' "
517
518 llRetVal = llRetVal AND v_SQLExec(lcSQLString, lcTempCursor) AND USED(lcTempCursor)
519
520 IF llRetVal AND RECCOUNT(lcTempCursor) > 0
521 SELECT (lcTempCursor)
522 SCAN
523 lcLocations = lcLocations + ALLTRIM(location) + ","
524 ENDSCAN
525 * add to log
526 .oLog.LogEntry("846 Process does not support OTS_QTY calculation override " + ;
527 "from Availability by Style for the following location(s)")
528 .oLog.LogEntry(.RemoveLastDelimiter(lcLocations))
529
530 * now delete
531 lcSQLString = " DELETE FROM " + .cQ_846Hdr + ;
532 " WHERE override_ots_qty = 'Y' "
533
534 llRetVal = llRetVal AND v_SQLExec(lcSQLString)
535 ENDIF
536 ENDIF
537 *=== TechRec 1087873 29-Jul-2015 jisingh ===
538
539 llRetVal = llRetVal AND V_SQLEXEC( " Select count(*) cnt from " + .cQ_846Hdr , lcTempCursor )
540
541 llRetval = llRetVal AND USED(lcTempCursor)
542
543 IF llRetVal
544
545 SELECT(lcTempCursor)
546 .nTotalLoc = cnt
547 ENDIF
548
549 llRetVal = llRetVal AND .nTotalLoc > 0
550
551 IF NOT llRetVal
552 .oLog.LogEntry("No records to process.")
553 ELSE
554 .AdvanceThermo(1)
555 .AdvanceThermoTotal(1)
556
557 .InitThermo(.nDtlExport)
558
559
560 *--- TR 1034990 NSD 8/7/08
561 * 1- WIP column (sfuwp_v) should be sfust_v. This process is displaying inventory that will be on hand in the future at location, not the current WIP at a contractor.
562 * 2- Due Dates will be calculated using new views zvcordrd_wip1v_shipto, zvcordrd_wip2v_shipto, zvcordrd_wip3v_shipto and zvcordrd_wip4v_shipto.
563 * Removing PO Substring code
564
565 .UpdateThermoCaption("Selecting Locationwise SKU(s) to export...")
566 .oLog.LogEntry("Selecting Locationwise SKU(s) to export...") && 1066673
567 *--- TR 1058987 28-Feb-2012 Goutam.
568 *- TR 1085714 FH - add catg_dest7 - 9.
569 *- TR 1093327 FH- make vertical
570*!* lcCatgSubJoin = " and (sc.catg_dest1 = case when h.catg_dest > '' then h.catg_dest else sc.catg_dest1 end or " + ;
571*!* " sc.catg_dest2 = case when h.catg_dest > '' then h.catg_dest else sc.catg_dest2 end or " + ;
572*!* " sc.catg_dest3 = case when h.catg_dest > '' then h.catg_dest else sc.catg_dest3 end or " + ;
573*!* " sc.catg_dest4 = case when h.catg_dest > '' then h.catg_dest else sc.catg_dest4 end or " + ;
574*!* " sc.catg_dest5 = case when h.catg_dest > '' then h.catg_dest else sc.catg_dest5 end or " + ;
575*!* " sc.catg_dest6 = case when h.catg_dest > '' then h.catg_dest else sc.catg_dest6 end or " + ;
576*!* " sc.catg_dest7 = case when h.catg_dest > '' then h.catg_dest else sc.catg_dest7 end or " + ;
577*!* " sc.catg_dest8 = case when h.catg_dest > '' then h.catg_dest else sc.catg_dest8 end or " + ;
578*!* " sc.catg_dest9 = case when h.catg_dest > '' then h.catg_dest else sc.catg_dest9 end) "
579
580 .cZzxcatgx = "#Zzxcatgx" + SYS(2015)
581 lcCrossTable = " create table " + .cZzxcatgx + " (catg_count INT); " + ;
582 " insert into " + .cZzxcatgx + " (catg_count) values (1); " + ;
583 " insert into " + .cZzxcatgx + " (catg_count) values (2); " + ;
584 " insert into " + .cZzxcatgx + " (catg_count) values (3); " + ;
585 " insert into " + .cZzxcatgx + " (catg_count) values (4); " + ;
586 " insert into " + .cZzxcatgx + " (catg_count) values (5); " + ;
587 " insert into " + .cZzxcatgx + " (catg_count) values (6); " + ;
588 " insert into " + .cZzxcatgx + " (catg_count) values (7); " + ;
589 " insert into " + .cZzxcatgx + " (catg_count) values (8); " + ;
590 " insert into " + .cZzxcatgx + " (catg_count) values (9); " + ;
591 " insert into " + .cZzxcatgx + " (catg_count) values (10) " &&--- TechRec 1094930 22-Aug-2016 jisingh ===
592
593 llRetVal = llRetVal and v_sqlexec(lcCrossTable)
594
595 *--- TechRec 1101299 09-Jan-2017 Sumit Gavate ---
596*!* Query corrected duplicate records were displaying if this table (zzxscocg) having more then one record for same SKU
597
598*!* lcCatgSubJoin = " join ( "+;
599*!* " select scolr.* "+;
600*!* " , x.catg_count "+;
601*!* " , case "+;
602*!* " when x.catg_count = 1 then scolr.catg_dest1 "+;
603*!* " when x.catg_count = 2 then scolr.catg_dest2 "+;
604*!* " when x.catg_count = 3 then scolr.catg_dest3 "+;
605*!* " when x.catg_count = 4 then scolr.catg_dest4 "+;
606*!* " when x.catg_count = 5 then scolr.catg_dest5 "+;
607*!* " when x.catg_count = 6 then scolr.catg_dest6 "+;
608*!* " when x.catg_count = 7 then scolr.catg_dest7 "+;
609*!* " when x.catg_count = 8 then scolr.catg_dest8 "+;
610*!* " when x.catg_count = 9 then scolr.catg_dest9 "+;
611*!* " else coalesce(dcd.catg_dest, '') "+; &&--- TechRec 1094930 22-Aug-2016 jisingh ===
612*!* " end as catg_dest "+;
613*!* " from zzxscolr scolr "+;
614*!* " left join zzxscocg dcd on dcd.fkey = scolr.pkey "+; &&--- TechRec 1094930 22-Aug-2016 jisingh ===
615*!* " and dcd.hfkey = scolr.fkey "+; &&--- TechRec 1094930 22-Aug-2016 jisingh ===
616*!* " cross join " +.cZzxcatgx + " x ) as sc "+;
617*!* " on U.division = Sc.Division "+;
618*!* " and U.Style = Sc.Style "+;
619*!* " and U.Color_code = Sc.Color_Code "+;
620*!* " and U.lbl_Code = Sc.lbl_Code "+;
621*!* " and U.Dimension = Sc.Dimension "+;
622*!* " and sc.catg_dest > '' "+;
623*!* " and sc.catg_dest = (case "+;
624*!* " when h.catg_dest> '' then h.catg_dest "+;
625*!* " else sc.catg_dest end) "
626
627 lcCatgSubJoin = " join ( "+;
628 " select scolr.*, x.catg_count "+;
629 " , case "+;
630 " when x.catg_count = 1 then scolr.catg_dest1 "+;
631 " when x.catg_count = 2 then scolr.catg_dest2 "+;
632 " when x.catg_count = 3 then scolr.catg_dest3 "+;
633 " when x.catg_count = 4 then scolr.catg_dest4 "+;
634 " when x.catg_count = 5 then scolr.catg_dest5 "+;
635 " when x.catg_count = 6 then scolr.catg_dest6 "+;
636 " when x.catg_count = 7 then scolr.catg_dest7 "+;
637 " when x.catg_count = 8 then scolr.catg_dest8 "+;
638 " when x.catg_count = 9 then scolr.catg_dest9 "+;
639 " else '' end as catg_dest "+;
640 " from zzxscolr scolr "+;
641 " cross join " +.cZzxcatgx + " x "+;
642 " UNION all "+;
643 " select scolr.*,0 as catg_count "+;
644 " ,coalesce(dcd.catg_dest, '') as catg_dest "+;
645 " from zzxscolr scolr "+;
646 " inner join zzxscocg dcd "+;
647 " on dcd.fkey = scolr.pkey "+;
648 " and dcd.hfkey = scolr.fkey) as sc "+;
649 " on U.division = Sc.Division "+;
650 " and U.Style = Sc.Style "+;
651 " and U.Color_code = Sc.Color_Code "+;
652 " and U.lbl_Code = Sc.lbl_Code "+;
653 " and U.Dimension = Sc.Dimension "+;
654 " and sc.catg_dest > '' "+;
655 " and sc.catg_dest = (case "+;
656 " when h.catg_dest> '' then h.catg_dest "+;
657 " else sc.catg_dest end) "
658 *=== TechRec 1101299 09-Jan-2017 Sumit Gavate ===
659
660 *- TR 1093327 FH- make vertical
661
662 *=== TR 1058987 28-Feb-2012 Goutam.
663 *--- TechRec 1059983 05-Jun-2012 MANI. ---
664 DO CASE
665 CASE .cAvail_Option = "Q"
666 *-- option QOH
667 lcQtyField = " (SFUR1_V + SFUR2_V + SFURT_V + SFUSA_V - SFUIN_V-SFUCM_V) "
668
669 CASE .cAvail_Option = "A"
670 *-- Option Availability
671
672 *--- TechRec 1066673 16-Aug-2013 GSternik ---
673 *lcQtyField = " (bal.Onhand_qty)"
674 lcQtyField = " IsNull(bal.Onhand_qty, 0)"
675 *=== TechRec 1066673 16-Aug-2013 GSternik ===
676
677 lcSizeQty = " SUM(Size01_Qty*Sz01+Size02_Qty*Sz02+Size03_Qty*Sz03+Size04_Qty*Sz04 + "+;
678 " Size05_Qty*Sz05+Size06_Qty*Sz06+Size07_Qty*Sz07+Size08_Qty*Sz08 +"+;
679 " Size09_Qty*Sz09+Size10_Qty*Sz10+Size11_Qty*Sz11+Size12_Qty*Sz12 + "+;
680 " Size13_Qty*Sz13+Size14_Qty*Sz14+Size15_Qty*Sz15+Size16_Qty*Sz16 + "+;
681 " Size17_Qty*Sz17+Size18_Qty*Sz18+Size19_Qty*Sz19+Size20_Qty*Sz20 +" + ;
682 " Size21_Qty*Sz21+Size22_Qty*Sz22+Size23_Qty*Sz23+Size24_Qty*Sz24) as Onhand_qty"
683
684 lcSizeWhr = " Size01_Qty*Sz01+Size02_Qty*Sz02+Size03_Qty*Sz03+Size04_Qty*Sz04 + "+;
685 " Size05_Qty*Sz05+Size06_Qty*Sz06+Size07_Qty*Sz07+Size08_Qty*Sz08 + "+;
686 " Size09_Qty*Sz09+Size10_Qty*Sz10+Size11_Qty*Sz11+Size12_Qty*Sz12 + "+;
687 " Size13_Qty*Sz13+Size14_Qty*Sz14+Size15_Qty*Sz15+Size16_Qty*Sz16 + "+;
688 " Size17_Qty*Sz17+Size18_Qty*Sz18+Size19_Qty*Sz19+Size20_Qty*Sz20 + " + ;
689 " Size21_Qty*Sz21+Size22_Qty*Sz22+Size23_Qty*Sz23+Size24_Qty*Sz24 > 0 "
690
691 *--- TechRec 1071370 31-May-2013 MANI. Added left keyword ===
692 lcZzbalJoin = " LEFT JOIN ( SELECT b.division, b.style,b.lbl_code, b.color_code, b.dimension, b.location, size_num as Size_bk , " + ;
693 lcsizeqty +;
694 " FROM zzaalbal b " +;
695 " CROSS JOIN zzxBuckt x " + ;
696 " WHERE REC_TYPE = 'I' AND " + lcSizeWhr + ;
697 " GROUP BY b.division, b.style,b.lbl_code, b.color_code, b.dimension, b.location, size_num ) bal " + ;
698 " ON bal.division = Szy.Division " + ;
699 " AND bal.Style = Szy.Style " + ;
700 " AND bal.Color_code = Szy.Color_Code " + ;
701 " AND bal.lbl_Code = Szy.lbl_Code " + ;
702 " AND bal.Dimension = Szy.Dimension " + ;
703 " AND bal.Size_bk = Szy.Size_bk " + ;
704 " AND bal.location = Szy.location "
705
706 OTHERWISE
707 *-- Existing Functionality
708 lcQtyField = " (SFUSA_V+SFURT_V+SFUR1_V+SFUR2_V-SFUIN_V-SFUPK_V-SFUCM_V-SFURS_V "+;
709 " -SFUOP_V-SFUDM_V-{FN IFNULL(({FN IFNULL(ZVXMRPUDV.SFUUD_V, 0)}), 0)} "+;
710 " ) "
711
712 ENDCASE
713 *=== TechRec 1059983 05-Jun-2012 MANI. ===
714
715 *lcPOSubString = " SELECT d.division, d.style, d.color_code, d.lbl_code, d.dimension, h.shipto,h.due_date " +;
716 " FROM zzcordrh h join zzcordrd d on h.pkey = d.fkey " + ;
717 " WHERE d.last_stage <> 'Y' " + ;
718 " group by d.division, d.style, d.color_code, d.lbl_code, d.dimension,h.shipto,h.due_Date "
719
720 *lcSQLMainString = " SELECT division, style, color_code,lbl_code,dimension,shipto, " + ;
721 " MAX(case when cnt =1 then due_date else '' end) as prod_date1 , " + ;
722 " MAX(case when cnt =2 then due_date else '' end) as prod_date2 , " + ;
723 " MAX(case when cnt =3 then due_date else '' end) as prod_date3 , " + ;
724 " MAX(case when cnt =4 then due_date else '' end) as prod_date4 " +;
725 " FROM ( Select division, style, color_code, lbl_code, dimension, shipto,due_date,"+;
726 " (select count(*) cnt FROM ("+ lcPOSubString+ ") PO"+;
727 " WHERE po.division = PO.division " +;
728 " AND po.style = po1.style "+;
729 " AND po.color_code = po1.color_code "+;
730 " AND po.lbl_code = po1.lbl_code "+;
731 " AND po.dimension = po1.dimension "+;
732 " AND po.shipto = po1.shipto " +;
733 " AND po.due_date >= po1.due_Date) "+;
734 " as cnt from ( " + lcPOSubString+ ") PO1"+;
735 + " ) A group by division, style, color_code,lbl_code,dimension ,shipto "
736
737 *--- TR 1040516 30-Jul-2009 Surinder Singh ---
738 * Added OTS_QTY field below in select and new join conditions from
739 * LEFT OUTER JOIN ( select division,style............
740
741 *--- TR 1058987 6-Mar-2012 Goutam. Added h.CtrlPkey in the select list
742 *--- TR 1058987 6-Mar-2012 Goutam. Added lcCatgSubJoin with zzxscolr join
743 *--- TR 1058987 6-Mar-2012 Goutam. Added cust_sku and aux_sku and join table zzxcstdr dr with sku and size bucket.
744 *--- TechRec 1059983 05-Jun-2012 MANI. Added lcQtyField ,lcZzbalJoin and removed the folloing lines in sql ===
745 *-- TR 1050639 28-Jan-2011 Partha
746
747 *--- TechRec 1059983 12-Jun-2012 MANI. ---
748
749 *--- TR 1061613 15-Jun-2012 Partha ---
750*!* lcavai_Qty = lcQtyField + " * " + ALLTRIM(STR(.nPercent_Avail)) + " / 100 - " + ;
751*!* lcQtyField + " * " + ALLTRIM(STR(.nPercent_Avail)) + " / 100 % sc.min_multiple "
752
753 *!* example 1 :-
754 *!* qty = 15 , % = 90 , mult = 6 , round = Y
755 *!* lcOptPercentage = 90/100 = .9
756 *!* lcPercentage = 15 * .9 = 13.5
757 *!* lcOptMinMult = 13.5 % 6 = 1.5
758 *!* lcRoundUpMinMult= 6 - 1.5 = 4.5
759 *!* lcRoundDownMinMult= 0 - 1.5 = -1.5
760 *!* lcavai_Qty = 13.5 + 4.5 = 18
761 *!* exceed chk ->lcavai_Qty = 13.5 + (-1.5) = 12
762
763 *!* example 2 :-
764 *!* qty = 15 , % = 90 , mult = 6 , round = D
765 *!* lcOptPercentage = 90/100 = .9
766 *!* lcPercentage = 15 * .9 = 13.5
767 *!* lcOptMinMult = 13.5 % 6 = 1.5
768 *!* lcRoundUpMinMult= 6 - 1.5 = 4.5
769 *!* lcRoundDownMinMult= 0 - 1.5 = -1.5
770 *!* lcavai_Qty = 13.5 + (-1.5) = 12
771 *!* exceed chk ->lcavai_Qty = 13.5 + (-1.5) = 12
772
773 *!* example 3 :-
774 *!* qty = -15 , % = 90 , mult = 6 , round = Y
775 *!* lcOptPercentage = 90/100 = .9
776 *!* lcPercentage = -15 * .9 = -13.5
777 *!* lcOptMinMult = -13.5 % 6 = -1.5
778 *!* lcRoundUpMinMult= (6 * -1) - (-1.5) = -6 + 1.5 = -4.5
779 *!* lcRoundDownMinMult= 0 - (-1.5) = 1.5
780 *!* lcavai_Qty = -13.5 + (-4.5) = -18
781 *!* exceed chk ->lcavai_Qty = -18
782
783 *!* example 4 :-
784 *!* qty = -15 , % = 90 , mult = 6 , round = D
785 *!* lcOptPercentage = 90/100 = .9
786 *!* lcPercentage = -15 * .9 = -13.5
787 *!* lcOptMinMult = -13.5 % 6 = -1.5
788 *!* lcRoundUpMinMult= (6 * -1) - (-1.5) = -6 * 1.5 = -9
789 *!* lcRoundDownMinMult= 0 - (- 1.5) = 1.5
790 *!* lcavai_Qty = -13.5 + 1.5 = -12
791 *!* exceed chk ->lcavai_Qty = -12
792
793 *--- TR 1076805 13-Mar-2014 Partha ---
794 *- All this number game should be played on lcOTSQtyCal variable
795 *- in this TR we are going to move this calculaton on the base of lcOTSQtyCal
796 *- commneted below code
797
798*!* lcOptPercentage = " ( CASE WHEN h.percentage = 0 THEN " + ALLTRIM(STR(.nPercent_Avail)) + " ELSE h.percentage END /100 ) "
799*!* lcPercentage = " ( CASE WHEN " + lcQtyField + " > 0 THEN (" + lcQtyField + " * " + lcOptPercentage + " ) ELSE " + lcQtyField + " END )" && TR 1063182 08-Aug-2012 Partha
800*!* lcOptMinMult = " ( " + lcPercentage + " % CASE WHEN h.min_mult = 'Y' THEN sc.min_multiple ELSE 1 END ) "
801*!* lcRoundUpMinMult = " ( CASE WHEN " + lcOptMinMult + " <>0 THEN ( CASE WHEN (" + lcQtyField + ") >0 THEN sc.min_multiple ELSE (sc.min_multiple * -1) END) - " + lcOptMinMult + " ELSE 0 END ) "
802*!* lcRoundDownMinMult = " ( 0 - " + lcOptMinMult + " ) "
803*!* lcavai_Qty = " ( " + lcPercentage + " + " + " CASE WHEN h.round_to = 'Y' THEN " + lcRoundUpMinMult + " ELSE " + lcRoundDownMinMult + " END ) "
804
805*!* *--- Partha 24JULY2012 - Following will Round down if rounded up qty exceeds actual. as guided by Bea and Fatima.
806*!* *!* lcavai_Qty = " CASE WHEN h.round_to = 'Y' AND ( " + lcavai_Qty + ") > ( " + lcQtyField + " ) THEN " + ;
807*!* *!* " ( " + lcPercentage + " + " + lcRoundDownMinMult + ") " + ;
808*!* *!* " ELSE (" + lcavai_Qty +") END "
809
810*!* *--- TR 1063182 03-Aug-2012 Partha ---
811*!* *!* lcavai_Qty = " CASE WHEN h.round_to = 'Y' AND ( " + lcavai_Qty + ") > ( " + lcQtyField + " ) THEN " + ;
812*!* *!* " ( " + lcPercentage + " + " + lcRoundDownMinMult + ") " + ;
813*!* *!* " WHEN h.round_to = 'D' AND ( " + lcavai_Qty + ") > ( " + lcQtyField + " ) THEN " + ;
814*!* *!* " ( " + lcPercentage + " + " + lcRoundUpMinMult + ") " + ;
815*!* *!* " ELSE (" + lcavai_Qty +") END "
816
817*!* *!* when +ve then take qty- (qty % min_multiple) as NearestMult
818*!* *!* when -ve then take NearestMult - min_multiple as NextMultiple
819*!* lcNearestMult = " ( " + lcQtyField + " - (" + lcQtyField + " % sc.min_multiple )) "
820*!* lcavai_Qty = " CASE WHEN (" +lcavai_Qty + ") <= 0 AND ( " + lcavai_Qty + ") > ( " + lcQtyField + " ) THEN " + ;
821*!* " CASE WHEN " + lcNearestMult + " = " + lcQtyField + " THEN " + lcQtyField + " ELSE "+ lcNearestMult + " - sc.min_multiple END " + ;
822*!* " WHEN (" +lcavai_Qty + ") >0 AND ( " + lcavai_Qty + ") > ( " + lcQtyField + " ) THEN " + ;
823*!* lcNearestMult + ;
824*!* " ELSE (" + lcavai_Qty +") END "
825*!* *=== TR 1063182 03-Aug-2012 Partha ===
826*!*
827*!* *=== TR 1061613 15-Jun-2012 Partha ===
828
829 lcavai_Qty = lcQtyField && Restore to basic
830 *=== TR 1076805 13-Mar-2014 Partha ===
831
832 *--- TechRec 1062747 18-Jul-2012 MANI. ---
833 IF NOT EMPTY(.nMinSzQtyAvl)
834 lcAvail_Qty1 = " CASE WHEN (" + lcavai_Qty + ") < " +.nMinSzQtyAvl + ;
835 " THEN 0 ELSE " + lcavai_Qty + " END "
836 ELSE
837 lcAvail_Qty1 = " CASE WHEN (" + lcavai_Qty + ") < 0" +;
838 " THEN 0 ELSE " + lcavai_Qty + " END "
839 ENDIF
840
841 *--- TR 1100531 12/02/16 ATHIRUNAVU Modified condition to take care if parameter BRO is not set.
842 *IF .cShowZeroQty = "N"
843 IF .cShowZeroQty < "Y"
844 *=== TR 1100531 12/02/16 ATHIRUNAVU
845 lcSQLFilterString = lcSQLFilterString + " AND " + lcAvail_Qty1 +" > 0"
846 ENDIF
847
848*!* IF "SFNSIZE_QTY" $ lcSQLFilterString
849*!* lcSQLFilterString = STRTRAN(lcSQLFilterString,'SFNSIZE_QTY',lcavai_Qty)
850*!* ENDIF
851
852ASSERT .f.
853
854 *=== TechRec 1062747 18-Jul-2012 MANI. ===
855 *=== TechRec 1059983 12-Jun-2012 MANI. ===
856 *--- TR 1067015 11-Mar-2013 Partha ---
857 IF THIS.l846_OTS_QTY_FROM_AVAILABILITY_BY_STYLE
858 IF THIS.cAvail_Option = "A"
859 lcOTSQtyCal = "," + lcQtyField + " AS OTS_QTY "
860 ELSE
861 lcOTSQtyCal = ", (bal.Onhand_qty) AS OTS_QTY "
862
863 lcOTSsizeqty = " SUM(Size01_Qty*Sz01+Size02_Qty*Sz02+Size03_Qty*Sz03+Size04_Qty*Sz04 + "+;
864 " Size05_Qty*Sz05+Size06_Qty*Sz06+Size07_Qty*Sz07+Size08_Qty*Sz08 +"+;
865 " Size09_Qty*Sz09+Size10_Qty*Sz10+Size11_Qty*Sz11+Size12_Qty*Sz12 + "+;
866 " Size13_Qty*Sz13+Size14_Qty*Sz14+Size15_Qty*Sz15+Size16_Qty*Sz16 + "+;
867 " Size17_Qty*Sz17+Size18_Qty*Sz18+Size19_Qty*Sz19+Size20_Qty*Sz20 +" + ;
868 " Size21_Qty*Sz21+Size22_Qty*Sz22+Size23_Qty*Sz23+Size24_Qty*Sz24) as OnHand_Qty"
869
870 lcOTSSizeWhr = " Size01_Qty*Sz01+Size02_Qty*Sz02+Size03_Qty*Sz03+Size04_Qty*Sz04 + "+;
871 " Size05_Qty*Sz05+Size06_Qty*Sz06+Size07_Qty*Sz07+Size08_Qty*Sz08 + "+;
872 " Size09_Qty*Sz09+Size10_Qty*Sz10+Size11_Qty*Sz11+Size12_Qty*Sz12 + "+;
873 " Size13_Qty*Sz13+Size14_Qty*Sz14+Size15_Qty*Sz15+Size16_Qty*Sz16 + "+;
874 " Size17_Qty*Sz17+Size18_Qty*Sz18+Size19_Qty*Sz19+Size20_Qty*Sz20 + " + ;
875 " Size21_Qty*Sz21+Size22_Qty*Sz22+Size23_Qty*Sz23+Size24_Qty*Sz24 > 0 "
876
877 *--- TR 1071381 06-14-2013 RKI ---*
878 IF !.l846_OTS_FROM_AVAIL_BY_STYLE_FROM_ALL_LOC
879 *=== TR 1071381 06-14-2013 RKI ===*
880 *--- TechRec 1071370 31-May-2013 MANI. Added left keyword ===
881 lcZzbalJoin = " LEFT JOIN ( SELECT b.division, b.style,b.lbl_code, b.color_code, b.dimension, b.location, size_num as Size_bk , " + ;
882 lcOTSsizeqty +;
883 " FROM zzaalbal b " +;
884 " CROSS JOIN zzxBuckt x " + ;
885 " WHERE REC_TYPE = 'I' AND " + lcOTSSizeWhr + ;
886 " GROUP BY b.division, b.style,b.lbl_code, b.color_code, b.dimension, b.location, size_num ) bal " + ;
887 " ON bal.division = Szy.Division " + ;
888 " AND bal.Style = Szy.Style " + ;
889 " AND bal.Color_code = Szy.Color_Code " + ;
890 " AND bal.lbl_Code = Szy.lbl_Code " + ;
891 " AND bal.Dimension = Szy.Dimension " + ;
892 " AND bal.Size_bk = Szy.Size_bk " + ;
893 " AND bal.location = Szy.location "
894 *--- TR 1071381 06-14-2013 RKI ---*
895 ELSE
896 lcZzbalJoin = " LEFT JOIN ( SELECT b.division, b.style,b.lbl_code, b.color_code, b.dimension, size_num as Size_bk , " + ;
897 lcOTSsizeqty +;
898 " FROM zzaalbal b " +;
899 " CROSS JOIN zzxBuckt x " + ;
900 " WHERE REC_TYPE = 'I' AND " + lcOTSSizeWhr + ;
901 " GROUP BY b.division, b.style,b.lbl_code, b.color_code, b.dimension, size_num ) bal " + ;
902 " ON bal.division = Szy.Division " + ;
903 " AND bal.Style = Szy.Style " + ;
904 " AND bal.Color_code = Szy.Color_Code " + ;
905 " AND bal.lbl_Code = Szy.lbl_Code " + ;
906 " AND bal.Dimension = Szy.Dimension " + ;
907 " AND bal.Size_bk = Szy.Size_bk "
908
909 ENDIF
910 *=== TR 1071381 06-14-2013 RKI ===*
911 ENDIF
912
913 ELSE
914 *=== TR 1067015 11-Mar-2013 Partha ===
915
916 *--- TechRec 1072901 30-Aug-2013 MANI. ---
917 .cNsrCnfTypeFlt = .BuildFilterForParamBro()
918 *=== TechRec 1072901 30-Aug-2013 MANI. ===
919 *--- TechRec 1066673 02-Oct-2013 GSternik ---
920 *--- TR 1064279 7-Nov-2012 Goutam.
921 *--- TechRec 1066673 GSternik --- moved to Else:
922 *lcOTSQtyCal = " ,(SFUSA_V+SFURT_V+SFUR1_V+SFUR2_V-SFUIN_V-SFUPK_V-SFUCM_V-SFURS_V "+;
923 " -SFUOP_V-SFUDM_V-{FN IFNULL(({FN IFNULL(ZVXMRPUDV.SFUUD_V, 0)}), 0)}) as OTS_QTY "
924 *--- Tr 1091168 30-Nov-2015 Dilip ---
925 IF !EMPTY(.cNsrCnfTypeFlt)
926 lcCustFilter = STREXTRACT(.cSQLFilterString,".CUSTOMER", "))")
927 IF !EMPTY(lcCustFilter)
928 lcCustFilterStr = " and EXISTS (SELECT 1 from zzoordrh h where " + "((h.customer " + lcCustFilter + ")) and h.ord_num = d.ord_num ) "
929 ELSE
930 lcCustFilterStr = ""
931 ENDIF
932 ELSE
933 lcCustFilter = ""
934 lcCustFilterStr = ""
935 ENDIF
936 TEXT TO lcAvalarJoing NOSHOW FLAGS 1 textmer
937 SELECT d.*, zzxBuckt.Size_Num as Size_Bk,
938 case Size_Num
939 when 01 then Size01 when 02 then Size02
940 when 03 then Size03 when 04 then Size04
941 when 05 then Size05 when 06 then Size06
942 when 07 then Size07 when 08 then Size08
943 when 09 then Size09 when 10 then Size10
944 when 11 then Size11 when 12 then Size12
945 when 13 then Size13 when 14 then Size14
946 when 15 then Size15 when 16 then Size16
947 when 17 then Size17 when 18 then Size18
948 when 19 then Size19 when 20 then Size20
949 when 21 then Size21 when 22 then Size22
950 when 23 then Size23 else Size24 end as size_desc,
951 Size01_Qty*Sz01+Size02_Qty*Sz02+Size03_Qty*Sz03+Size04_Qty*Sz04+
952 Size05_Qty*Sz05+Size06_Qty*Sz06+Size07_Qty*Sz07+Size08_Qty*Sz08+
953 Size09_Qty*Sz09+Size10_Qty*Sz10+Size11_Qty*Sz11+Size12_Qty*Sz12+
954 Size13_Qty*Sz13+Size14_Qty*Sz14+Size15_Qty*Sz15+Size16_Qty*Sz16+
955 Size17_Qty*Sz17+Size18_Qty*Sz18+Size19_Qty*Sz19+Size20_Qty*Sz20+
956 Size21_Qty*Sz21+Size22_Qty*Sz22+Size23_Qty*Sz23+Size24_Qty*Sz24 as Bk_Qty
957 FROM zzaavalr d
958 CROSS JOIN zzxBuckt
959 JOIN zzxStylr s
960 ON s.division = d.division
961 AND s.style = d.style
962 JOIN zzxSizer z
963 ON z.division= d.division
964 AND z.size_code = s.size_code
965 WHERE Size01_Qty*Sz01+Size02_Qty*Sz02+Size03_Qty*Sz03+Size04_Qty*Sz04+
966 Size05_Qty*Sz05+Size06_Qty*Sz06+Size07_Qty*Sz07+Size08_Qty*Sz08+
967 Size09_Qty*Sz09+Size10_Qty*Sz10+Size11_Qty*Sz11+Size12_Qty*Sz12+
968 Size13_Qty*Sz13+Size14_Qty*Sz14+Size15_Qty*Sz15+Size16_Qty*Sz16+
969 Size17_Qty*Sz17+Size18_Qty*Sz18+Size19_Qty*Sz19+Size20_Qty*Sz20+
970 Size21_Qty*Sz21+Size22_Qty*Sz22+Size23_Qty*Sz23+Size24_Qty*Sz24 > 0
971 <<lcCustFilterStr>>
972 ENDTEXT
973 *=== Tr 1091168 30-Nov-2015 Dilip ===
974 IF this.l846_Use_Priority100_OTS
975 *--- TechRec 1066673 12-Aug-2013 GSternik --- zvoordrdv use is an overkill in here. just moved Line_Status to the filter
976*!* lcOrderSubSQL = " left join (Select division, style, color_code, lbl_Code, dimension, size_bk, location, line_status, SUM(bk_Qty) bk_Qty From " + ;
977*!* " zvoordrdv od " + ;
978*!* " where od.priority <= '100' " + ;
979*!* " Group by division, style, color_code, lbl_Code, dimension, size_bk, location, line_status) od " + ;
980*!* " on U.division = od.Division " + ;
981*!* " and U.Style = od.Style " + ;
982*!* " and U.Color_code = od.Color_Code " + ;
983*!* " and U.lbl_Code = od.lbl_Code " + ;
984*!* " and U.Dimension = od.Dimension " + ;
985*!* " and U.Sizebucket = od.Size_bk " + ;
986*!* " and h.location = od.location " + ;
987*!* " and od.line_status = 'O'"
988 *-- od alias was coliding with the main SQL!!!
989
990 *--- 01-Nov-2013 GSternik ---: Attetion, all additional filters in here MUST be applied to the Bulk Qty Calculation too!!!
991 *--- TechRec 1090249 30-Sep-2015 jisingh Replaced .cNsrCnfTypeFlt with .cCnfTypeFilter ===
992 *--- TechRec 1090249 30-Sep-2015 jisingh Added AND STRTRAN(.cNsrCodeFilter,'dv.','o.') ===
993 *--- Tr 1091168 30-Nov-2015 Dilip ---
994 IF !EMPTY(.cNsrCode)
995 lcOrderSubSQL = ;
996 " left join ("+;
997 "select a.Division, a.Style, a.Color_Code, a.Lbl_Code, a.Dimension, a.Size_Bk, a.Location, Sum(a.bk_qty) as bk_Qty" +;
998 " from (" + lcAvalarJoing + ") a " +;
999 " left join zvoordrdv o " +;
1000 " on a.division = o.division " + ;
1001 " and a.ord_num = o.ord_num " + ;
1002 " and a.line_seq = o.line_seq " +;
1003 " and a.size_bk = o.size_bk " + ;
1004 " where o.Priority <= '100'" +;
1005 " and o.Line_Status = 'O'" +;
1006 " and " + STRTRAN(.cNsrCode,"dv.","a.") + " "+ ;
1007 IIF(EMPTY(.cNsrCnfTypeFlt) , ' AND 1 = 1'," AND "+ STRTRAN(.cCnfTypeFilter,'dv.','o.')) +;
1008 " group by a.Division, a.Style, a.Color_Code, a.Lbl_Code, a.Dimension, a.Size_Bk, a.Location) ov " +;
1009 " on u.Division = ov.Division " +;
1010 " and u.Style = ov.Style " +;
1011 " and u.Color_Code = ov.Color_Code " +;
1012 " and u.Lbl_Code = ov.lbl_Code " +;
1013 " and u.Dimension = ov.Dimension " +;
1014 " and u.Sizebucket = ov.Size_bk " +;
1015 " and h.Location = ov.Location "
1016
1017 lcOTSQtyCal = " ,IsNull(OV.BK_QTY, 0) AS OTS_QTY "
1018
1019 ELSE
1020 *=== Tr 1091168 30-Nov-2015 Dilip ===
1021 lcOrderSubSQL = ;
1022 " left join ("+;
1023 "select Division, Style, Color_Code, Lbl_Code, Dimension, Size_Bk, Location, Sum(Bk_Qty) as bk_Qty" +;
1024 " from zvoordrdv o " +;
1025 " where o.Priority <= '100'" +;
1026 " and o.Line_Status = 'O'" +;
1027 IIF(EMPTY(.cCnfTypeFilter) , ' AND 1 = 1'," AND "+ STRTRAN(.cCnfTypeFilter,'dv.','o.') +; &&--- TechRec 1072901 07-Apr-2014 MANI. ===
1028 " AND "+ STRTRAN(.cNsrCodeFilter,'dv.','o.')) + ;
1029 " group by Division, Style, Color_Code, Lbl_Code, Dimension, Size_Bk, Location) ov " +;
1030 " on u.Division = ov.Division " +;
1031 " and u.Style = ov.Style " +;
1032 " and u.Color_Code = ov.Color_Code " +;
1033 " and u.Lbl_Code = ov.lbl_Code " +;
1034 " and u.Dimension = ov.Dimension " +;
1035 " and u.Sizebucket = ov.Size_bk " +;
1036 " and h.Location = ov.Location "
1037
1038 lcOTSQtyCal = " ,(SFUSA_V+SFURT_V+SFUR1_V+SFUR2_V-SFUIN_V-SFUPK_V-SFUCM_V-SFURS_V "+;
1039 " -IsNull(OV.BK_QTY, 0)-SFUDM_V-{FN IFNULL(({FN IFNULL(ZVXMRPUDV.SFUUD_V, 0)}), 0)}) AS OTS_QTY "
1040 *--- Tr 1091168 30-Nov-2015 Dilip ---
1041 ENDIF
1042 *=== Tr 1091168 30-Nov-2015 Dilip ===
1043 ELSE
1044 *--- Tr 1091168 30-Nov-2015 Dilip ---
1045 IF !EMPTY(.cNsrCode) AND !EMPTY(.cNsrCnfTypeFlt)
1046 lcNsrCnfSQL = " left join (Select a.division, a.style, a.color_code, a.lbl_Code, a.dimension, dv.size_bk, a.location, dv.line_status, Sum(a.bk_qty) as bk_Qty From " + ;
1047 " (" + lcAvalarJoing + ") a " +;
1048 " left join zvoordrdv dv " + ;
1049 " on a.division = dv.division " + ;
1050 " and a.ord_num = dv.ord_num " + ;
1051 " and a.line_seq = dv.line_seq " +;
1052 " and a.size_bk = dv.size_bk " + ;
1053 " where (" + .cCnfTypeFilter + " AND " + STRTRAN(.cNsrCode,"dv.","a.") +;
1054 " and dv.Line_Status = 'O'" +;
1055 " )Group by a.division, a.style, a.color_code, a.lbl_Code, a.dimension, dv.size_bk, a.location, dv.line_status) Ord " + ;
1056 " on U.division = Ord.Division " + ;
1057 " and U.Style = Ord.Style " + ;
1058 " and U.Color_code = Ord.Color_Code " + ;
1059 " and U.lbl_Code = Ord.lbl_Code " + ;
1060 " and U.Dimension = Ord.Dimension " + ;
1061 " and U.Sizebucket = Ord.Size_bk " + ;
1062 " and h.location = Ord.location "
1063
1064
1065 lcOTSQtyCal = " ,IsNull(ORD.BK_QTY, 0) AS OTS_QTY "
1066 ELSE
1067 *=== Tr 1091168 30-Nov-2015 Dilip ===
1068 *--- TechRec 1066673 02-Oct-2013 GSternik --- Moved to "Else"
1069 lcOTSQtyCal = " ,(SFUSA_V+SFURT_V+SFUR1_V+SFUR2_V-SFUIN_V-SFUPK_V-SFUCM_V-SFURS_V "+;
1070 " -SFUOP_V-SFUDM_V-{FN IFNULL(({FN IFNULL(ZVXMRPUDV.SFUUD_V, 0)}), 0)}) AS OTS_QTY "
1071 *=== TechRec 1066673 12-Aug-2013 GSternik ===
1072 *--- TechRec 1072901 07-Apr-2014 MANI. ---
1073 IF NOT EMPTY(.cNsrCnfTypeFlt)
1074 *--- TechRec 1090249 30-Sep-2015 jisingh Replaced .cNsrCnfTypeFlt with .cCnfTypeFilter ===
1075 *--- TechRec 1090249 30-Sep-2015 jisingh Added AND .cNsrCodeFilter ===
1076 lcNsrCnfSQL = " left join (Select division, style, color_code, lbl_Code, dimension, size_bk, location, line_status, SUM(bk_Qty) bk_Qty From " + ;
1077 " zvoordrdv dv " + ;
1078 " where (" + .cCnfTypeFilter + " AND " + .cNsrCodeFilter + ;
1079 " and dv.Line_Status = 'O'" +;
1080 " )Group by division, style, color_code, lbl_Code, dimension, size_bk, location, line_status) Ord " + ;
1081 " on U.division = Ord.Division " + ;
1082 " and U.Style = Ord.Style " + ;
1083 " and U.Color_code = Ord.Color_Code " + ;
1084 " and U.lbl_Code = Ord.lbl_Code " + ;
1085 " and U.Dimension = Ord.Dimension " + ;
1086 " and U.Sizebucket = Ord.Size_bk " + ;
1087 " and h.location = Ord.location "
1088
1089
1090 lcOTSQtyCal = " ,(SFUSA_V+SFURT_V+SFUR1_V+SFUR2_V-SFUIN_V-SFUPK_V-SFUCM_V-SFURS_V "+;
1091 " -IsNull(Ord.BK_QTY, 0)-SFUDM_V-{FN IFNULL(({FN IFNULL(ZVXMRPUDV.SFUUD_V, 0)}), 0)}) AS OTS_QTY "
1092 ENDIF
1093 *=== TechRec 1072901 07-Apr-2014 MANI. ===
1094 *--- Tr 1091168 30-Nov-2015 Dilip ---
1095 ENDIF
1096 *=== Tr 1091168 30-Nov-2015 Dilip ===
1097 ENDIF
1098 *=== TR 1064279 7-Nov-2012 Goutam.
1099
1100 *--- TR 1067015 11-Mar-2013 Partha ---
1101 ENDIF
1102 *=== TR 1067015 11-Mar-2013 Partha ===
1103
1104 ASSERT .f.
1105 *--- TR 1072524 7-Aug-2013 Goutam
1106 IF !EMPTY(this.cBulkOrderType)
1107 *--- TechRec 1066673 12-Aug-2013 GSternik --- optimized.
1108 *lcBulkOrderSubSQL = " left join (Select od.division, od.style, od.color_code, od.lbl_Code, od.dimension, od.size_bk, od.location, od.line_status, SUM(od.bk_Qty) Bulk_bk_Qty From " + ;
1109 " zvoordrdv od join zzoordrh oh on oh.pkey = od.fkey " + ;
1110 " where oh.conf_type = 'B' and oh.ord_type = " + SqlFormatChar(this.cBulkOrderType) + ;
1111 " Group by od.division, od.style, od.color_code, od.lbl_Code, od.dimension, od.size_bk, od.location, od.line_status) od " + ;
1112 " on U.division = od.Division " + ;
1113 " and U.Style = od.Style " + ;
1114 " and U.Color_code = od.Color_Code " + ;
1115 " and U.lbl_Code = od.lbl_Code " + ;
1116 " and U.Dimension = od.Dimension " + ;
1117 " and U.Sizebucket = od.Size_bk " + ;
1118 " and h.location = od.location " + ;
1119 " and h.add_bulk = 'Y' " + ;
1120 " and od.line_status = 'O'"
1121
1122 *--- Need to add Bulk to OTS:
1123 lcOTSQtyCal = Strtran(lcOTSQtyCal, " AS OTS_QTY", " + IsNull(Bulk_bk_Qty, 0) AS OTS_QTY")
1124
1125 *--- TechRec 1074553 01-Nov-2013 GSternik --- added Priority check placeholder in the SQL below:
1126 Text to lcBulkOrderSubSQL NoShow Flags 3
1127 left join (
1128 select od.Division
1129 , od.Style
1130 , od.Color_Code
1131 , od.Lbl_Code
1132 , od.Dimension
1133 , zzxBuckt.Size_Num as Size_Bk
1134 , od.Location
1135 , sum( Size01_Qty*Sz01+Size02_Qty*Sz02+Size03_Qty*Sz03+Size04_Qty*Sz04+
1136 Size05_Qty*Sz05+Size06_Qty*Sz06+Size07_Qty*Sz07+Size08_Qty*Sz08+
1137 Size09_Qty*Sz09+Size10_Qty*Sz10+Size11_Qty*Sz11+Size12_Qty*Sz12+
1138 Size13_Qty*Sz13+Size14_Qty*Sz14+Size15_Qty*Sz15+Size16_Qty*Sz16+
1139 Size17_Qty*Sz17+Size18_Qty*Sz18+Size19_Qty*Sz19+Size20_Qty*Sz20+
1140 Size21_Qty*Sz21+Size22_Qty*Sz22+Size23_Qty*Sz23+Size24_Qty*Sz24) as Bulk_bk_Qty
1141 from zzoordrd od
1142 cross join zzxBuckt
1143 join zzoordrh oh
1144 on oh.PKey = od.FKey
1145 where oh.Conf_Type = 'B'
1146 and od.Line_Status = 'O'
1147 and oh.Ord_Type = '$'
1148 --- Priority
1149 group by od.Division, od.Style, od.Color_Code, od.Lbl_Code, od.Dimension, Size_Num, od.Location
1150 ) bul
1151 on u.Division = bul.Division
1152 and u.Style = bul.Style
1153 and u.Color_Code = bul.Color_Code
1154 and u.Lbl_Code = bul.Lbl_Code
1155 and u.Dimension = bul.Dimension
1156 and u.SizeBucket = bul.Size_bk
1157 and h.Location = bul.Location
1158 and h.Add_Bulk = 'Y'
1159EndText
1160 lcBulkOrderSubSQL = Strtran(lcBulkOrderSubSQL, "'$'", SqlFormatChar(this.cBulkOrderType))
1161 *=== TechRec 1066673 12-Aug-2013 GSternik ===
1162
1163 *--- TechRec 1074553 01-Nov-2013 GSternik ---
1164 *-- Correction for the error introduced in TR 1072525:
1165 *-- If the order with Priority > 100 ignored from the opened, they must be ignored from Bulk also!
1166 If This.l846_Use_Priority100_OTS and !This.l846_OTS_QTY_FROM_AVAILABILITY_BY_STYLE
1167 lcBulkOrderSubSQL = Strtran(lcBulkOrderSubSQL, "--- Priority", " and od.Priority <= '100'")
1168 EndIf
1169 *=== TechRec 1074553 01-Nov-2013 GSternik ===
1170
1171 ENDIF
1172 *=== TR 1072524 7-Aug-2013 Goutam
1173
1174
1175
1176 *--- TR 1076805 13-Mar-2014 Partha ---
1177 lcTempOTSQtyCal = SUBSTR(lcOTSQtyCal, AT(",",lcOTSQtyCal)+1)
1178 *-- TR 1072901/Partha .
1179 *--To avoid error when AS OTS_QTY is in lower case.
1180 *!* lcTempOTSQtyCal = STRTRAN( lcTempOTSQtyCal, " AS OTS_QTY", "")
1181 lcTempOTSQtyCal = STRTRAN( lcTempOTSQtyCal, " AS OTS_QTY", "", -1, -1, 1)
1182 *-- TR 1072901/Partha .
1183
1184 *--- TechRec 1087873 29-Jul-2015 jisingh ---
1185 IF llRetVal AND .lAllow846Override
1186 lcTempOTSQtyCal = .BuildOTSString(lcTempOTSQtyCal)
1187 ENDIF
1188 *=== TechRec 1087873 29-Jul-2015 jisingh ===
1189
1190 lcOptPercentage = " ( CASE WHEN h.percentage = 0 THEN " + ALLTRIM(STR(.nPercent_Avail)) + " ELSE h.percentage END /100 ) "
1191 lcPercentage = " ( CASE WHEN " + lcTempOTSQtyCal + " > 0 THEN (" + lcTempOTSQtyCal + " * " + lcOptPercentage + " ) ELSE " + lcTempOTSQtyCal + " END )"
1192 lcOptMinMult = " ( " + lcPercentage + " % CASE WHEN h.min_mult = 'Y' THEN sc.min_multiple ELSE 1 END ) "
1193 lcRoundUpMinMult = " ( CASE WHEN " + lcOptMinMult + " <>0 THEN ( CASE WHEN (" + lcTempOTSQtyCal + ") >0 THEN sc.min_multiple ELSE (sc.min_multiple * -1) END) - " + lcOptMinMult + " ELSE 0 END ) "
1194 lcRoundDownMinMult = " ( 0 - " + lcOptMinMult + " ) "
1195 lcTempQty = " ( " + lcPercentage + " + " + " CASE WHEN h.round_to = 'Y' THEN " + lcRoundUpMinMult + " ELSE " + lcRoundDownMinMult + " END ) "
1196
1197 lcNearestMult = " ( " + lcTempOTSQtyCal + " - (" + lcTempOTSQtyCal + " % sc.min_multiple )) "
1198 lcTempQty = " CASE WHEN (" + lcTempQty + ") <= 0 AND ( " + lcTempQty + ") > ( " + lcTempOTSQtyCal + " ) THEN " + ;
1199 " CASE WHEN " + lcNearestMult + " = " + lcTempOTSQtyCal + " THEN " + lcTempOTSQtyCal + " ELSE "+ lcNearestMult + " - sc.min_multiple END " + ;
1200 " WHEN (" +lcTempQty + ") >0 AND ( " + lcTempQty + ") > ( " + lcTempOTSQtyCal + " ) THEN " + ;
1201 lcNearestMult + ;
1202 " ELSE (" + lcTempQty +") END "
1203
1204 lcOTSQtyCal = ", ISNULL(" + lcTempQty + ",0) AS OTS_QTY " &&FH 1110128
1205 *=== TR 1076805 13-Mar-2014 Partha ===
1206
1207 *--- TechRec 1079208 29-Oct-2014 TSV---
1208 IF .lCustSku
1209 lcSQLFilterString = lcSQLFilterString + " AND dr.cust_style <> '' "
1210 ENDIF
1211 *=== TechRec 1079208 29-Oct-2014 TSV===
1212
1213 *--- TR 1064279 8-Nov-2012 Goutam.
1214 *Removed following part from below sql and replace with lcOTSQtyCal. Added lcOrderSubSQL at the end.
1215 *" ,(SFUSA_V+SFURT_V+SFUR1_V+SFUR2_V-SFUIN_V-SFUPK_V-SFUCM_V-SFURS_V "+;
1216 " -SFUOP_V-SFUDM_V-{FN IFNULL(({FN IFNULL(ZVXMRPUDV.SFUUD_V, 0)}), 0)}) as OTS_QTY "+;
1217 *=== TR 1064279 8-Nov-2012 Goutam.
1218
1219 *--- TR 1064755 09-Jan-13 SK Added join condition (Customer) for zzxcstdr to filter ===
1220 *--- TechRec 1066673 17-Jul-2013 GSternik --- Optimized
1221 *-- !!! this lcSQLString value is for the Source Safe comparison only. Not used (see below)
1222
1223 *=== TR 1040516 30-Jul-2009 Surinder Singh ===
1224
1225 *--- TR 1050639 04-Feb-2011 Partha ---
1226 * we need to group the dtl based on location group. so we r adding one more layer of tmp dtl table
1227
1228 *lcTmpDtl = SQLTableFromQuery(lcSqlString)
1229 *llRetVal = llRetVal AND !EMPTY(lcTmpDtl)
1230
1231 *--- TechRec 1066673 Query by GS:
1232 *--- TechRec 1075482 13-Jan-2014 GSternik ---
1233 *-- The data will be collected into the temp table and then updated.
1234 *lcTmpDtl = Iif(Version(2)=2,"##","#") + SubStr(SYS(2015), 2)
1235 lcSqlString = SubStr(SYS(2015), 5)
1236 lcTmpDtl = Iif(Version(2)=2,"##DTL","#DTL") + lcSqlString
1237 lcTmpWIP = Iif(Version(2)=2,"##WIP","#WIP") + lcSqlString
1238
1239*--- TR 1084609 06-May-2015 Partha added h.Auto_wkf ===
1240*--- TR 1093677 01-Apr-2016 Partha added h.loc_recap, h.customer ===
1241Text to lcSqlString NoShow Flags 1
1242select h.CtrlPkey
1243 , h.Location
1244 , h.LocGroup
1245 , h.Vnd_Qual
1246 , h.Vnd_Id
1247 , h.Our_Qual
1248 , h.Our_Id
1249 , h.loc_recap, h.customer
1250 , (U.UPC_Num+U.Chk_Digit) as UPC
1251 , szy.Style
1252 , szy.Color_code
1253 , szy.Lbl_Code
1254 , szy.Dimension
1255 , u.Size_Desc
1256 , szy.SFUST_V as WIP_Qty
1257 , szy.SFUOP_V as Open_Qty
1258 , szy.SFUPK_V as Pick_Qty
1259 , szy.Available as Avl_Only
1260 , szy.Division
1261 , st.Style_Name
1262 , cl.Color_Name
1263 , cl.Color_Desc
1264-- , coalesce(p1.Due_Date, '') as Prod_Date1
1265-- , coalesce(p2.Due_Date, '') as Prod_Date2
1266-- , coalesce(p3.Due_Date, '') as Prod_Date3
1267-- , coalesce(p4.Due_Date, '') as Prod_Date4
1268 , cast('' as datetime) as Prod_Date1
1269 , cast('' as datetime) as Prod_Date2
1270 , cast('' as datetime) as Prod_Date3
1271 , cast('' as datetime) as Prod_Date4
1272 , CAST('' as datetime) as receive_date1
1273 , h.CR_Auto_wkf
1274 , szy.Size_bk
1275 , coalesce(dr.Cust_Style, '') as Cust_Sku
1276 , coalesce(dr.Aux_Sku, '') as Aux_Sku
1277 , h.Use_Excl
1278 , coalesce(dr.Style, '') as CustStyle
1279 , coalesce(hr.Retail_price, 0) as Cust_Retail
1280 , sc.catg_dest /* TR 1093327 FH */
1281 -- OnHand, OTS, Bulk --
1282 from --This.cQ_846Hdr--
1283 join ZVXSSUMHVSZLL_call szy
1284 on szy.Location = H.Location
1285 and szy.division = h.division /* --- TR 1097211 18-Jul-2016 Goutam */
1286 and szy.available =
1287 case
1288 when h.Avl_Only = 'Y'
1289 then h.Avl_Only
1290 else szy.Available end
1291 join zVeupcnr U
1292 on u.Division = szy.Division
1293 and u.Style = szy.Style
1294 and u.Color_code = szy.Color_Code
1295 and u.lbl_Code = szy.lbl_Code
1296 and u.Dimension = szy.Dimension
1297 and u.Sizebucket = szy.Size_bk
1298 join ZZXSTYLR st
1299 on st.Division = u.Division
1300 and st.Style = u.Style
1301/* join zzxscolr sc
1302 on u.Division = Sc.Division
1303 and u.Style = Sc.Style
1304 and u.Color_code = Sc.Color_Code
1305 and u.Lbl_Code = Sc.Lbl_Code
1306 and u.Dimension = Sc.Dimension TR 1093327 FH */
1307 --lcCatgSubJoin--
1308 join zzxcolrr cl
1309 on cl.Color_Code = u.Color_Code
1310/*
1311 left join wip p1
1312 on p1.Division = u.Division
1313 and p1.Style = u.Style
1314 and p1.Color_Code = u.Color_Code
1315 and p1.Lbl_Code = u.Lbl_Code
1316 and p1.Dimension = u.Dimension
1317 and p1.Size_Bk = u.SizeBucket
1318 and p1.ShipTo = h.Location
1319 and p1.Due_Rank = 1
1320 left join wip p2
1321 on p2.Division = u.Division
1322 and p2.Style = u.Style
1323 and p2.Color_Code = u.Color_Code
1324 and p2.Lbl_Code = u.Lbl_Code
1325 and p2.Dimension = u.Dimension
1326 and p2.Size_Bk = u.SizeBucket
1327 and p2.ShipTo = h.Location
1328 and p2.Due_Rank = 2
1329 left join wip p3
1330 on p3.Division = u.Division
1331 and p3.Style = u.Style
1332 and p3.Color_Code = u.Color_Code
1333 and p3.Lbl_Code = u.Lbl_Code
1334 and p3.Dimension = u.Dimension
1335 and p3.Size_Bk = u.SizeBucket
1336 and p3.ShipTo = h.Location
1337 and p3.Due_Rank = 3
1338 left join wip p4
1339 on p4.Division = u.Division
1340 and p4.Style = u.Style
1341 and p4.Color_Code = u.Color_Code
1342 and p4.Lbl_Code = u.Lbl_Code
1343 and p4.Dimension = u.Dimension
1344 and p4.Size_Bk = u.SizeBucket
1345 and p4.ShipTo = h.Location
1346 and p4.Due_Rank = 4 */
1347 left join (
1348 select Division
1349 , Style
1350 , Color_Code
1351 , Lbl_Code
1352 , Dimension
1353 , Size_Bk
1354 , sum(SFUUD_V) as SFUUD_V
1355 from ZVXMRPUDV
1356 group by Division, Style, Color_Code, Lbl_Code, Dimension, Size_Bk ) ZVXMRPUDV
1357 on ZVXMRPUDV.Division = u.Division
1358 and ZVXMRPUDV.Style = u.Style
1359 and ZVXMRPUDV.Color_Code = u.Color_Code
1360 and ZVXMRPUDV.Lbl_Code = u.Lbl_Code
1361 and ZVXMRPUDV.Dimension = u.Dimension
1362 and ZVXMRPUDV.Size_Bk = u.SizeBucket
1363 /* *--- TechRec 1087873 31-Jul-2015 jisingh --- */
1364 LEFT JOIN ZVCRMMRPVLL_LOC
1365 ON h.Location = zvcrmmrpvll_loc.location
1366 AND u.division = zvcrmmrpvll_loc.division
1367 AND u.style = zvcrmmrpvll_loc.style
1368 AND u.color_code = zvcrmmrpvll_loc.color_code
1369 AND u.lbl_code = zvcrmmrpvll_loc.lbl_code
1370 AND u.dimension = zvcrmmrpvll_loc.dimension
1371 AND u.sizebucket = zvcrmmrpvll_loc.size_bk
1372 /* *=== TechRec 1087873 31-Jul-2015 jisingh === */
1373 left join zzxcstdr dr
1374 on dr.Division = u.Division
1375 and dr.Style = u.Style
1376 and dr.Color_Code = u.Color_Code
1377 and dr.Lbl_Code = u.Lbl_Code
1378 and dr.Dimension = u.Dimension
1379 and dr.Size_Bk = u.Sizebucket
1380 and dr.Customer = h.Customer
1381 left join zzxcsthr hr
1382 on hr.PKey = dr.FKey
1383EndText
1384
1385
1386 lcSqlString = Strtran(lcSqlString, "from --This.cQ_846Hdr--", ;
1387 "into " + lcTmpDtl + " from " + This.cQ_846Hdr + " H ")
1388 lcSqlString = Strtran(lcSqlString, "--lcCatgSubJoin--", lcCatgSubJoin)
1389 *--- TechRec 1088815 22-Jul-2015 GSternik --- Added RowCount :
1390 lcSqlString = Strtran(lcSqlString, "-- OnHand, OTS, Bulk --",;
1391 lcOTSQtyCal + ", " + lcAvail_Qty1 + " as OnHand_Qty " )+;
1392 lcZzbalJoin + ;
1393 lcOrderSubSQL + ;
1394 lcBulkOrderSubSQL +;
1395 lcNsrCnfSQL +; &&--- TechRec 1072901 30-Aug-2013 MANI. ===
1396 " WHERE " + lcSQLFilterString +;
1397 "; select @@RowCount as Rows"
1398
1399 .oLog.LogEntry("Retrieving the inventory data.")
1400
1401 llRetVal = llRetVal and v_sqlExecUncommitted(lcSqlString, "qResults")
1402
1403 if llRetVal
1404 .oLog.LogEntry("Found " + Transform(qResults.Rows) + " inventory records.")
1405 EndIf
1406 use in Select("qResults")
1407 *=== TechRec 1088815 22-Jul-2015 GSternik ===
1408
1409 *--- TechRec 1066673 02-Oct-2013 GSternik --- This is not needed anymore
1410*!* *--- TR 1072524 7-Aug-2013 Goutam
1411*!* IF llRetVal AND !EMPTY(lcBulkOrderSubSQL)
1412*!* lcSqlString = " Update " + lcTmpDtl + ;
1413*!* " Set OTS_QTY = coalesce(OTS_QTY, 0) + coalesce(Bulk_bk_Qty, 0) " + ;
1414*!* "Where coalesce(Bulk_bk_Qty, 0) > 0"
1415*!*
1416*!* llRetVal = llRetVal AND v_SqlExec(lcSqlString)
1417*!* ENDIF
1418*!* *=== TR 1072524 7-Aug-2013 Goutam
1419
1420*=== TechRec 1066673 17-Jul-2013 GSternik ===
1421
1422 .oLog.LogEntry("Removing locations from header that are not used in the detail...") && 1066673
1423
1424 * Remove locations from header that are not used in the detail
1425 * THis will let us respect filter criteria
1426
1427 *--- TR 1058987 06-Mar-2012 Goutam
1428*!* lcSQLString = "DELETE FROM " + .cQ_846Hdr + ;
1429*!* " FROM " + .cQ_846Hdr + " h " + ;
1430*!* " LEFT JOIN " + lcTmpDtl + " d " + ;
1431*!* " ON h.location = d.location " + ;
1432*!* " AND h.locgroup = d.locgroup " + ;
1433*!* " WHERE d.location is null and d.locgroup is null"
1434
1435 lcSQLString = ;
1436 "DELETE h" +;
1437 " FROM " + .cQ_846Hdr + " h " + ;
1438 " LEFT JOIN " + lcTmpDtl + " d " + ;
1439 " ON h.CtrlPkey = d.CtrlPkey " + ;
1440 " WHERE d.CtrlPkey is null"
1441
1442 *=== TR 1058987 06-Mar-2012 Goutam
1443
1444 llRetVal = llRetVal and v_sqlexec(lcSQLString)
1445
1446 *--- TechRec 1066673 08-Jan-2014 GSternik ---
1447 *-- Future WIP Due Dates as a separate SQL:
1448 Local lcWipSql
1449Text to lcWipSql NoShow Flags 1
1450select Division
1451 , Style
1452 , Color_Code
1453 , Lbl_Code
1454 , Dimension
1455 , Size_Num as Size_Bk
1456 , Due_Date
1457 , sum( WIP01_Qty*Sz01+WIP02_Qty*Sz02+WIP03_Qty*Sz03+WIP04_Qty*Sz04+WIP05_Qty*Sz05+WIP06_Qty*Sz06+
1458 WIP07_Qty*Sz07+WIP08_Qty*Sz08+WIP09_Qty*Sz09+WIP10_Qty*Sz10+WIP11_Qty*Sz11+WIP12_Qty*Sz12+
1459 WIP13_Qty*Sz13+WIP14_Qty*Sz14+WIP15_Qty*Sz15+WIP16_Qty*Sz16+WIP17_Qty*Sz17+WIP18_Qty*Sz18+
1460 WIP19_Qty*Sz19+WIP20_Qty*Sz20+WIP21_Qty*Sz21+WIP22_Qty*Sz22+WIP23_Qty*Sz23+WIP24_Qty*Sz24) as WIP_Qty
1461 , ShipTo
1462 , dense_rank() over (
1463 partition by Division
1464 , Style
1465 , Color_Code
1466 , Lbl_Code
1467 , Dimension
1468 , Size_Num
1469 , ShipTo order by Due_Date) as Due_Rank
1470 into ##Tmp_WIP
1471 from zzcordrd p with (NoLock)
1472 cross join zzxBuckt b with (NoLock)
1473 where WIP_OK = 'Y'
1474 and WIP_Total > 0
1475 and exists ( -- not sure about this... Maybe we should create an index first
1476 select NULL
1477 from --lcTmpDtl-- h
1478 where p.Division = h.Division
1479 and p.Style = h.Style
1480 and p.Color_Code = h.Color_Code
1481 and p.Lbl_Code = h.Lbl_Code
1482 and p.Dimension = h.Dimension
1483 and p.ShipTo = h.Location)
1484 group by Division
1485 , Style
1486 , Color_Code
1487 , Lbl_Code
1488 , Dimension
1489 , Size_Num
1490 , Due_Date
1491 , ShipTo
1492having sum( WIP01_Qty*Sz01+WIP02_Qty*Sz02+WIP03_Qty*Sz03+WIP04_Qty*Sz04+WIP05_Qty*Sz05+WIP06_Qty*Sz06+
1493 WIP07_Qty*Sz07+WIP08_Qty*Sz08+WIP09_Qty*Sz09+WIP10_Qty*Sz10+WIP11_Qty*Sz11+WIP12_Qty*Sz12+
1494 WIP13_Qty*Sz13+WIP14_Qty*Sz14+WIP15_Qty*Sz15+WIP16_Qty*Sz16+WIP17_Qty*Sz17+WIP18_Qty*Sz18+
1495 WIP19_Qty*Sz19+WIP20_Qty*Sz20+WIP21_Qty*Sz21+WIP22_Qty*Sz22+WIP23_Qty*Sz23+WIP24_Qty*Sz24) > 0
1496EndText
1497 lcWipSql = Strtran(Strtran(lcWipSql, "--lcTmpDtl--", lcTmpDtl), "##Tmp_WIP", lcTmpWIP)
1498 .oLog.LogEntry("Retrieving the future WIP Due dates.")
1499 llRetVal = llRetVal and v_sqlExecUncommitted(lcWipSql)
1500
1501 If llRetVal
1502 Local lcWipUpdate
1503 lcWipUpdate = ;
1504 "update h" +;
1505 " set Prod_Date(#) = w.Due_Date"+;
1506 " from " + lcTmpDtl + " h" +;
1507 " join " + lcTmpWIP + " w" +;
1508 " on w.Division = h.Division "+;
1509 " and w.Style = h.Style "+;
1510 " and w.Color_Code = h.Color_Code"+;
1511 " and w.Lbl_Code = h.Lbl_Code "+;
1512 " and w.Dimension = h.Dimension "+;
1513 " and w.Size_Bk = h.Size_Bk"+;
1514 " and w.ShipTo = h.Location "+;
1515 " and w.Due_Rank = (#)"
1516
1517 .oLog.LogEntry("Updating the 1st WIP Due date")
1518 llRetVal = llRetVal and v_sqlExecUncommitted(Strtran(lcWipUpdate, "(#)", "1"))
1519 .oLog.LogEntry("Updating the 2nd WIP Due date")
1520 llRetVal = llRetVal and v_sqlExecUncommitted(Strtran(lcWipUpdate, "(#)", "2"))
1521 .oLog.LogEntry("Updating the 3rd WIP Due date")
1522 llRetVal = llRetVal and v_sqlExecUncommitted(Strtran(lcWipUpdate, "(#)", "3"))
1523 .oLog.LogEntry("Updating the 4th WIP Due date")
1524 llRetVal = llRetVal and v_sqlExecUncommitted(Strtran(lcWipUpdate, "(#)", "4"))
1525 EndIf
1526
1527 =v_SqlExec("begin try drop table "+ lcTmpWIP + " end try begin catch end catch")
1528 *=== TechRec 1066673 08-Jan-2014 GSternik ===
1529
1530 *--- TR 1088278 2-Sep-15 NSD
1531 TEXT TO lcSQLString NOSHOW TEXTMERGE
1532 UPDATE h set receive_date1 = w.receive_date1
1533 FROM <<lcTmpDtl>> h
1534 JOIN (
1535 select p.Division, p.Style, p.Color_Code, p.Lbl_Code,
1536 p.Dimension, b.Size_Num as Size_Bk, MAX(p.Trans_date) as receive_date1,p.shipto
1537 from zzcordrd p
1538 join <<lcTmpDtl>> t
1539 on p.Division = t.Division
1540 and p.Style = t.Style
1541 and p.Color_Code = t.Color_Code
1542 and p.Lbl_Code = t.Lbl_Code
1543 and p.Dimension = t.Dimension
1544 and p.ShipTo = t.Location
1545 cross join zzxBuckt b
1546 where p.last_stage = 'Y'
1547 and (SIZE01_Qty*Sz01+SIZE02_Qty*Sz02+SIZE03_Qty*Sz03+SIZE04_Qty*Sz04
1548 +SIZE05_Qty*Sz05+SIZE06_Qty*Sz06+SIZE07_Qty*Sz07+SIZE08_Qty*Sz08
1549 +SIZE09_Qty*Sz09+SIZE10_Qty*Sz10+SIZE11_Qty*Sz11+SIZE12_Qty*Sz12
1550 +SIZE13_Qty*Sz13+SIZE14_Qty*Sz14+SIZE15_Qty*Sz15+SIZE16_Qty*Sz16
1551 +SIZE17_Qty*Sz17+SIZE18_Qty*Sz18+SIZE19_Qty*Sz19+SIZE20_Qty*Sz20
1552 +SIZE21_Qty*Sz21+SIZE22_Qty*Sz22+SIZE23_Qty*Sz23+SIZE24_Qty*Sz24
1553 ) > 0
1554 group by p.Division, p.Style, p.Color_Code, p.Lbl_Code, p.Dimension, b.Size_Num ,p.shipto
1555 ) w
1556 on w.Division = h.Division
1557 and w.Style = h.Style
1558 and w.Color_Code = h.Color_Code
1559 and w.Lbl_Code = h.Lbl_Code
1560 and w.Dimension = h.Dimension
1561 and w.Size_Bk = h.Size_Bk
1562 and w.ShipTo = h.Location
1563 ENDTEXT
1564
1565 .oLog.LogEntry("Updating receive date 1")
1566 llRetVal = llRetVal AND v_sqlexec(lcSQLString)
1567 *=== TR 1088278 2-Sep-15 NSD
1568
1569 .oLog.LogEntry("Update the header location with locgroup.") && 1066673
1570 * We are exporting LOCGROUP in dtl so we should do the same for the hdr also.
1571 * 1. update the header location with locgroup if it has locgroup
1572 * 2. group the header based on location,vnd_id,our_id,vnd_qual,our_qual
1573 lcSQLString = " UPDATE " + .cQ_846Hdr + " SET location = locgroup WHERE locgroup > '' "
1574 llRetVal = llRetVal AND V_SQLEXEC( lcSQLString )
1575
1576 .oLog.LogEntry("Header grouping...") && 1066673
1577 *--- TR 1058987 06-Mar-2012 Goutam. Added Customer, catg_dest and template in select list as well as group by
1578 *--- TechRec 1060583 19-Apr-2012 jisingh Added use_threshold, threshold_qty & res_cprice ===
1579 lcSQLString = " SELECT " + ;
1580 " location " + ;
1581 " ,vnd_id " + ;
1582 " ,vnd_qual " + ;
1583 " ,our_qual " + ;
1584 " ,our_id " + ;
1585 " ,MAX(active_ok) active_ok " + ;
1586 " ,MAX(avl_only) avl_only " + ;
1587 " ,MAX(report_date) report_date " + ;
1588 " ,MAX(report_time) report_time " + ;
1589 " ,MAX(Supplier_num) Supplier_num "+;
1590 " ,Customer, catg_dest, template " + ;
1591 " ,MAX(use_threshold) use_threshold, MAX(threshold_qty) threshold_qty, MAX(res_cprice) res_cprice " + ;
1592 " ,MAX(max_qty_send) max_qty_send "+ ; &&--- TechRec 1090759 01-Dec-2015 vkrishnamurthy ===
1593 " ,MAX(resv_loc_adr) resv_loc_adr " + ; &&--- TechRec 1076524 10-Apr-2014 TSV added resv_loc_adr ===
1594 " ,MAX(cust_loc_req) cust_loc_req " + ; &&--- TR 1076961 KISHORE 6-MAR-2014
1595 " ,cust_loc ,cust_loc_name " + ; &&=== TR 1076961 KISHORE 6-MAR-2014
1596 " ,CR_auto_wkf " + ; && TR 1084609 06-May-2015 Partha
1597 " ,ctrl_division " + ; && TR 1084425 04-Jan-2016 Partha
1598 " FROM " + .cQ_846Hdr + " H " + ;
1599 " GROUP BY " + ;
1600 " location " + ;
1601 " ,vnd_id " + ;
1602 " ,vnd_qual " + ;
1603 " ,our_qual " + ;
1604 " ,our_id, Customer, catg_dest, template" + ;
1605 " ,cust_loc, cust_loc_name " + ; && TR 1076961 KISHORE 6-MAR-2014
1606 " ,CR_auto_wkf " + ; && TR 1084609 06-May-2015 Partha
1607 " ,ctrl_division " && TR 1084425 04-Jan-2016 Partha
1608 lcTmpHdr = SQLTableFromQuery(lcSqlString)
1609 llRetVal = llRetVal AND !EMPTY(lcTmpHdr)
1610 lcSQLString = " DROP TABLE " + .cQ_846Hdr
1611 llRetVal = llRetVal AND V_SQLEXEC( lcSQLString )
1612 .cQ_846Hdr = lcTmpHdr
1613
1614 *=== Hdr grouping completed
1615
1616 *--- TR 1093677 01-Apr-2016 Partha ---
1617 * Create location detail records from detail table before location grouping
1618 * include all fields used for grouping the .cQ_846Dtl
1619 .oLog.LogEntry("Creating detail location breakup...")
1620 .cQ_846Loc = "##LOC" + GetUniqueFilename()
1621 lcSQLString = " SELECT DISTINCT 9999999999 as FKEY, T.location,T.lbl_code,T.customer, " + ;
1622 " CASE WHEN T.LocGroup > '' THEN T.LocGroup ELSE T.Location END AS Control_Loc, " + ;
1623 " CASE WHEN T.Customer > '' THEN COALESCE(c.cust_Loc, '') ELSE '' END AS CUST_LOC, " + ;
1624 " CASE WHEN T.Customer > '' THEN COALESCE(c.cust_Loc_name, '') ELSE '' END AS CUST_LOC_NAME, " + ;
1625 " COALESCE(T.Ots_qty,0) OTS_QTY " + ;
1626 " , vnd_qual " + ;
1627 " , vnd_id " + ;
1628 " , our_qual " + ;
1629 " , our_id " + ;
1630 " , division " +;
1631 " , UPC, aux_sku, cust_sku " + ;
1632 " , use_excl " + ;
1633 " , CustStyle " + ;
1634 " , cust_retail " + ;
1635 " INTO " + .cQ_846Loc + ;
1636 " FROM " + lcTmpDtl + " T " + ;
1637 " LEFT JOIN zzecloxr c " + ;
1638 " ON T.Customer = c.customer " + ;
1639 " AND T.Location = c.location" + ;
1640 " WHERE T.loc_recap = 'Y' "
1641
1642 llRetVal = llRetVal AND V_SQLEXEC( lcSQLString )
1643 *=== TR 1093677 01-Apr-2016 Partha ===
1644
1645 .oLog.LogEntry("Update the detail location with locgroup...") && 1066673
1646 lcSQLString = " UPDATE " + lcTmpDtl + " SET location = locgroup WHERE locgroup > '' "
1647 llRetVal = llRetVal AND V_SQLEXEC( lcSQLString )
1648
1649 .oLog.LogEntry("Detail grouping...") && 1066673
1650 *--- TR 1064755 09-Jan-13 SK Used MAX() instead of SUM() for qty fields.
1651 *--- TR 1058987 06-Mar-2012 Goutam. Added aux_sku, cust_sku in select list as well as group by
1652 * - TR 1076473 FH change MAX() back to SUM() for qty fields
1653
1654 lcSQLString = " SELECT location " + ;
1655 " , vnd_qual " + ;
1656 " , vnd_id " + ;
1657 " , our_qual " + ;
1658 " , our_id " + ;
1659 " , UPC" + ;
1660 " , Style" + ;
1661 " , Color_code " + ;
1662 " , lbl_code " + ;
1663 " , Dimension " + ;
1664 " , Size_desc" + ;
1665 " , SUM(onhand_qty) onhand_qty " +;
1666 " , SUM(wip_qty) wip_qty" +;
1667 " , SUM(open_qty) open_qty" +;
1668 " , SUM(pick_qty) pick_qty" +;
1669 " , MAX(avl_only) avl_only " +;
1670 " , division " +;
1671 " , MAX(style_name) style_name " +;
1672 " , MAX(color_name) color_name " +;
1673 " , MAX(color_desc) color_desc " +;
1674 " , MAX(prod_date1) prod_date1 " +;
1675 " , MAX(prod_date2) prod_date2 " +;
1676 " , MAX(prod_date3) prod_date3 " +;
1677 " , MAX(prod_date4) prod_date4 " +;
1678 " , MAX(receive_date1) receive_date1 " +;
1679 " , Size_bk " +;
1680 " , SUM(OTS_QTY) OTS_QTY " +;
1681 " , aux_sku, cust_sku " + ;
1682 " , use_excl " + ; && TR 1064279 9-Nov-2012 Goutam
1683 " , CustStyle " + ; && TR 1064279 9-Nov-2012 Goutam
1684 " , cust_retail " + ; && TR 1064279 9-Nov-2012 Goutam
1685 " FROM " + lctmpdtl + ;
1686 " GROUP BY location " + ;
1687 " , vnd_qual " + ;
1688 " , vnd_id " + ;
1689 " , our_qual " + ;
1690 " , our_id " + ;
1691 " , division " +;
1692 " , Style" + ;
1693 " , Color_code " + ;
1694 " , lbl_code " + ;
1695 " , Dimension " + ;
1696 " , Size_bk " +;
1697 " , Size_desc" + ;
1698 " , UPC, aux_sku, cust_sku " + ;
1699 " , use_excl " + ; && TR 1064279 9-Nov-2012 Goutam
1700 " , CustStyle " + ; && TR 1064279 9-Nov-2012 Goutam
1701 " , cust_retail " && TR 1064279 9-Nov-2012 Goutam
1702 *=== TR 1050639 04-Feb-2011 Partha ===
1703
1704 If llRetVal
1705 .cQ_846Dtl = SQLTableFromQuery(lcSqlString)
1706 EndIf
1707
1708 llRetVal = llRetVal AND !EMPTY(.cQ_846Dtl)
1709
1710 llRetVal = llRetVal AND V_SQLEXEC( " Select count(*) cnt from " + .cQ_846Dtl , lcTempCursor )
1711
1712 llRetval = llRetVal AND USED(lcTempCursor)
1713
1714 IF llRetVal
1715 SELECT(lcTempCursor)
1716 .nDtlExport = cnt
1717 ENDIF
1718
1719 * Remove locations from header that are not used in the detail
1720 * THis will let us respect filter criteria
1721
1722 *--- TR 1050639 07-Feb-2011 Partha ---
1723*!* taking this up, after that tmpdtl creation and before update of location with location group. to based on that tmpdtl
1724*!* llRetVal = llRetVal and v_sqlexec("DELETE FROM " + This.cQ_846Hdr + " WHERE location NOT IN (SELECT DISTINCT location FROM " + .cQ_846Dtl + ")" )
1725 *=== TR 1050639 07-Feb-2011 Partha ===
1726
1727 llRetVal = llRetVal AND V_SQLEXEC( " Select count(*) cnt from " + .cQ_846Hdr , lcTempCursor )
1728
1729 llRetval = llRetVal AND USED(lcTempCursor)
1730
1731 IF llRetVal
1732 SELECT(lcTempCursor)
1733 .nTotalLoc = cnt
1734
1735 IF .nTotalLoc = 0
1736 .oLog.LogEntry("No records to process.")
1737 ENDIF
1738 ENDIF
1739
1740 *--- TechRec 1066673 22-Oct-2013 GSternik ---
1741 *--- TR 1050639 04-Feb-2011 Partha ---
1742 *lcSQLString = " DROP TABLE " + lctmpdtl
1743 *V_SQLEXEC(lcSQLString)
1744 *=== TR 1050639 04-Feb-2011 Partha ===
1745 =v_SqlExec("begin try drop table "+ lcTmpDtl +" end try begin catch end catch")
1746 *=== TechRec 1066673 22-Oct-2013 GSternik ===
1747
1748 ENDIF
1749
1750 .TableClose(lcTempCursor)
1751
1752 *- TR 1093327 FH
1753 IF NOT EMPTY(.cZzxcatgx)
1754 .DropSQLTempTable(.cZzxcatgx)
1755 .cZzxcatgx = ""
1756 ENDIF
1757 ENDWITH
1758
1759 RETURN llRetVal
1760 ENDFUNC
1761*=====================================================
1762 PROCEDURE DeletePreviousTransaction
1763
1764 LOCAL llRetVal, lcSQLString
1765
1766 llRetVal= .T.
1767 WITH THIS
1768 .InitThermo(1)
1769 .UpdateThermoCaption("Deleting previous 846(o) transactions...")
1770 .oLog.LogEntry("Deleting previous 846 ")
1771
1772
1773 lcSQLString = " DELETE FROM " + .cInterFaceHeader
1774 llRetVal = llRetVal AND .vSQLExec(lcSQLString)
1775
1776 lcSQLString = " DELETE FROM " + .cInterFaceDetail
1777 llRetVal = llRetVal AND .vSQLExec(lcSQLString)
1778 .AdvanceThermo(1)
1779
1780 ENDWITH
1781 IF !llRetVal
1782 .oLog.LogEntry("Deleting previous 846 - failed")
1783 ENDIF
1784
1785 RETURN llRetVal
1786 ENDPROC
1787*=====================================================
1788 PROCEDURE ValidateTransaction
1789
1790 LOCAL llRetVal, lnSelect
1791
1792 *--- TR 1058987 7-Mar-2012 Goutam
1793 LOCAL lcHeader, lcDetail, lcSql
1794 lcHeader = THIS.cInterFaceHeader
1795 lcDetail = THIS.cInterFaceDetail
1796 *=== TR 1058987 7-Mar-2012 Goutam
1797
1798 llRetVal = True
1799 lnSelect = SELECT()
1800
1801 WITH THIS
1802 .oLog.LogMajorStage("Validating Data") && TR 1076961 KISHORE 6-MAR-2014
1803
1804 *--- TR 1058987 7-Mar-2012 Goutam
1805 *--- TR 1061446 17-May-2012 Goutam. Changed error message from Bonton to Bonton/Amazon and Added h.template = 'AMAZON.COM 4010' in the where clause
1806 lcSql = "Update d set errs_flg_d = 'Y', errs_msg_d = 'Bonton/Amazon SKU for Style: ' + RTRIM(d.Style) + ', Color: ' + RTRIM(d.Color_code) + " + ;
1807 "', Label: ' + RTRIM(lbl_code) + ', Size : ' + RTRIM(d.size_desc) + ' is not found' from " + lcHeader + " h join " + lcDetail + " d " + ;
1808 " on d.fkey = h.pkey where d.cust_sku = '' and (h.template = 'BONTON V.4010' or h.template = 'AMAZON.COM 4010')"
1809
1810 llRetVal = llRetVal AND v_SqlExec(lcSql)
1811
1812 *--- TR 1061446 17-May-2012 Goutam. Changed error message from Bonton to Bonton/Amazon and Added h.template = 'AMAZON.COM 4010' in the where clause
1813 lcSql = "Update d set errs_flg_d = 'Y', errs_msg_d = Convert(varchar(8000), errs_msg_d) + CHAR(10) + 'Bonton/Amazon Vendor Item# for Style: ' + RTRIM(d.Style) + ', Color: ' + RTRIM(d.Color_code) + " + ;
1814 "', Label: ' + RTRIM(lbl_code) + ', Size : ' + RTRIM(d.size_desc) + ' is not found' from " + lcHeader + " h join " + lcDetail + " d " + ;
1815 " on d.fkey = h.pkey where d.aux_sku = '' and (h.template = 'BONTON V.4010' or h.template = 'AMAZON.COM 4010')"
1816
1817 llRetVal = llRetVal AND v_SqlExec(lcSql)
1818
1819 lcSql = "Update h set errs_flg_h = 'Y', errs_msg_h = 'Errors in detail' from " + lcHeader + " h join " + lcDetail + " d " + ;
1820 " on d.fkey = h.pkey where d.errs_flg_d = 'Y'"
1821
1822 llRetVal = llRetVal AND v_SqlExec(lcSql)
1823
1824 *=== TR 1058987 7-Mar-2012 Goutam
1825
1826 *--- TR 1076961 KISHORE 6-MAR-2014
1827 TEXT TO lcSQL NOSHOW TEXTMERGE
1828 UPDATE h
1829 SET errs_flg_h = 'Y', errs_msg_h = 'Customer Location for ' + RTRIM(location) + ' required.'
1830 FROM <<lcHeader>> h
1831 WHERE errs_flg_h <> 'Y'
1832 AND (cust_loc_req = 'Y' AND customer > '' AND cust_loc = '' AND cust_loc_name = '')
1833 ENDTEXT
1834
1835 llRetVal = llRetVal AND v_SqlExec(lcSql)
1836
1837 lcErrorCursor = GetUniqueFileName()
1838
1839 TEXT TO lcSQL NOSHOW TEXTMERGE
1840 SELECT 'Customer Location for ' + RTRIM(location) + ' required for ' + RTRIM(customer) + '.' AS err_msg
1841 FROM <<lcHeader>>
1842 WHERE errs_flg_h = 'Y'
1843 AND (cust_loc_req = 'Y' AND customer > '' AND cust_loc = '' AND cust_loc_name = '')
1844 ENDTEXT
1845
1846 llRetVal = llRetVal AND v_SqlExec(lcSql, lcErrorCursor)
1847 IF llRetVal AND USED(lcErrorCursor)
1848 SELECT (lcErrorCursor)
1849 SCAN
1850 .oLog.LogEntry(err_msg)
1851 ENDSCAN
1852 USE
1853 ENDIF
1854
1855 .oLog.LogResult(llRetVal, "Validating Data")
1856 *=== TR 1076961 KISHORE 6-MAR-2014
1857 ENDWITH
1858
1859 SELECT (lnSelect)
1860 RETURN llRetVal
1861 ENDPROC
1862*=====================================================
1863 FUNCTION PopulateTransaction
1864
1865 LOCAL llRetVal, lnSelect, lcSQLString, lcTempTable, lcCursor ,ltLast_Mod,lcUser_id
1866
1867 ltLast_Mod = DATETIME()
1868 lcUser_id = goEnv.SV("cUser")
1869
1870 lnSelect = SELECT()
1871 llRetVal = True
1872
1873 *--- TechRec 1090759 02-Dec-2015 vkrishnamurthy ---
1874 LOCAL lcOtsThreshold ,lcOtsMax ,lcOtsQty
1875 *=== TechRec 1090759 02-Dec-2015 vkrishnamurthy ===
1876ASSERT .f.
1877 WITH THIS
1878 THIS.oLog.LogMajorStage("Retrieving Location(s)")
1879 *-- Populating Header
1880 WITH THIS.oSB
1881 .SB_Reset()
1882 .cSB_Target = THIS.cInterFaceHeader
1883 .cSB_Source = THIS.cQ_846Hdr
1884 .cSB_SourceAlias = "loc"
1885 .cSB_WHEREString = " "
1886
1887 .SB_SetLiteralFieldValue("location","loc.location")
1888 .SB_SetLiteralFieldValue("errs_flg_h","''")
1889 .SB_SetLiteralFieldValue("errs_msg_h","''")
1890 .SB_SetLiteralFieldValue("notes","''")
1891 .SB_SetLiteralFieldValue("last_mod",SQLFormatTS(ltLast_Mod))
1892 .SB_SetLiteralFieldValue("user_id",SQLFormatChar(lcUser_id))
1893 *--- TR 1040516 30-Jul-2009 Surinder Singh ---
1894 .SB_SetLiteralFieldValue("location","loc.supplier_num")
1895 *=== TR 1040516 30-Jul-2009 Surinder Singh ===
1896 .SB_SetLiteralFieldValue("division","loc.ctrl_division") && TR 1084425 08-Jan-2016 Partha
1897
1898 *--- TR 1058987 10-Mar-2012 Goutam. Added loc.customer+loc.catg_dest and made Char(40) to Char(52)
1899 *--- TechRec 1087873 19-Sep-2015 jisingh Added loc.template+loc.cust_loc+loc.cust_loc_name+loc.cr_auto_wkf & Changed Char(52) to Char(138) ===
1900 .SB_KeySetAddKeySpec("pkey","Char(143)","loc.location+loc.vnd_qual+loc.vnd_id+loc.our_qual+loc.our_id+loc.customer+loc.catg_dest+loc.template+loc.cust_loc+loc.cust_loc_name+loc.cr_auto_wkf") && *--- TR 1097211 3-8-2016 VKK changed 138 to 143 since catg desc is now c(10)
1901
1902 llRetVal = .SB_GenerateSQLInsertString()
1903 ENDWITH
1904
1905 llRetVal = llRetVal AND .vSQLExec(THIS.oSB.cSQLString)
1906 .AdvanceThermo(.5)
1907
1908 *-- Populating Detail
1909 WITH THIS.oSB
1910 .SB_Reset()
1911 .cSB_Target = THIS.cInterFaceDetail
1912 .cSB_Source = THIS.cQ_846Dtl
1913 .cSB_SourceAlias = "d"
1914
1915 *--- TechRec 1060583 19-Apr-2012 jisingh Added LEFT JOIN to zzxcstpr ===
1916 .cSB_FROMExpandedString = " JOIN " + THIS.cInterFaceHeader + " h " + ;
1917 " ON h.location = d.location" + ;
1918 " AND h.vnd_qual = d.vnd_qual " + ;
1919 " AND h.vnd_id = d.vnd_id " + ;
1920 " AND h.our_qual = d.our_qual " + ;
1921 " AND h.our_id = d.our_id " + ;
1922 " LEFT JOIN zzxcstpr cp " + ;
1923 " ON cp.customer = h.customer " + ;
1924 " AND cp.division = d.division " + ;
1925 " AND cp.style = d.style " + ;
1926 " AND cp.color_code = d.color_code " + ;
1927 " AND cp.lbl_code = d.lbl_code " + ;
1928 " AND cp.dimension = d.dimension "
1929
1930 *--- TechRec 1060583 19-Apr-2012 jisingh ---
1931 *.cSB_WHEREString = " "
1932 *--- TR 1064279 7-Nov-2012 Goutam. Changed excl_846 to Remove_846, excl_846_sent to remove_846_sent
1933 *--- TR 1065893 16-12-2012 VKK
1934 * this is for use_excl = N
1935 *.cSB_WHEREString = " COALESCE(cp.remove_846,'') <> 'Y' OR (COALESCE(cp.remove_846,'') = 'Y' AND COALESCE(cp.remove_846_sent,'') <> 'Y') "
1936 .cSB_WHEREString = " d.use_excl = 'N' or d.use_excl = '' "
1937 *=== TR 1065893 16-12-2012 VKK
1938 *=== TechRec 1060583 19-Apr-2012 jisingh ===
1939
1940 .SB_SetLiteralFieldValue("fkey","h.pkey")
1941
1942 .SB_SetLiteralFieldValue("errs_flg_d","''")
1943 .SB_SetLiteralFieldValue("errs_msg_d","''")
1944 .SB_SetLiteralFieldValue("notes","''")
1945 .SB_SetLiteralFieldValue("last_mod",SQLFormatTS(ltLast_Mod))
1946 .SB_SetLiteralFieldValue("user_id",SQLFormatChar(lcUser_id))
1947
1948 *--- TechRec 1060583 19-Apr-2012 jisingh ---
1949 .SB_SetLiteralFieldValue("cust_price", "CASE WHEN h.res_cprice = 'Y' THEN COALESCE(cp.price,0) ELSE 0 END")
1950
1951 *--- TR 1064279 7-Nov-2012 Goutam.
1952 .SB_SetLiteralFieldValue("cust_retail", "CASE WHEN d.cust_retail = 0 THEN COALESCE(cp.retail_price,0) ELSE d.cust_retail END")
1953
1954 *--- TR 1075432 12/18/13 ATHIRUNAVU Commented the discontinue_date population
1955 *.SB_SetLiteralFieldValue("discontinue_date", SQLFormatTS(DATE()))
1956 *=== TR 1064279 7-Nov-2012 Goutam.
1957
1958 *--- TR 1064279 7-Nov-2012 Goutam. Changed excl_846 to remove_846
1959
1960 *--- TechRec 1090759 01-Dec-2015 vkrishnamurthy ---
1961*!* .SB_SetLiteralFieldValue("ots_qty", "COALESCE((CASE WHEN COALESCE(cp.remove_846,'') = 'Y' THEN 0 ELSE CASE WHEN h.use_threshold = 'Y' THEN " + ;
1962*!* "CASE WHEN (d.ots_qty - h.threshold_qty) > 0 THEN (d.ots_qty - h.threshold_qty) ELSE 0 END ELSE ots_qty END END),0)")
1963
1964*dbo.bcfn_MIN() function between the resolved ots_qty and case when h.max_qty_send > 0 then h.max_qty_send else 999999999 end
1965 *- TR 1081304 FH - If use exclusion is set to 'N', customer price should not affect ots_qty
1966 *lcOtsThreshold = "COALESCE((CASE WHEN COALESCE(cp.remove_846,'') = 'Y' THEN 0 ELSE CASE WHEN h.use_threshold = 'Y' THEN " + ;
1967 "CASE WHEN (d.ots_qty - h.threshold_qty) > 0 THEN (d.ots_qty - h.threshold_qty) ELSE 0 END ELSE ots_qty END END),0)"
1968 *- STRY0121796 FH - Even if exclusion is set to 'N' we still need to calculate for threshold
1969 *lcOtsThreshold = "d.ots_qty"
1970*!* lcOtsThreshold = "COALESCE((CASE WHEN h.use_threshold = 'Y' THEN " + ;
1971*!* "CASE WHEN (d.ots_qty - h.threshold_qty) > 0 THEN (d.ots_qty - h.threshold_qty) ELSE 0 END ELSE ots_qty END),0)"
1972*!* lcOtsMax = " dbo.bcfn_MIN("+lcOtsThreshold+ " ,CASE WHEN h.max_qty_send > 0 THEN h.max_qty_send else 999999999 end )"
1973 *-FH LOCAL
1974 lcOtsThreshold = "COALESCE(CASE WHEN h.use_threshold = 'Y' AND d.ots_qty < h.threshold_qty THEN 0 ELSE ots_qty END ,0)"
1975 lcOtsMax = " dbo.bcfn_MAX(0, "+lcOtsThreshold +")"
1976
1977 *--- TechRec 1095862 24-May-2016 smullappilly --- && Added condition 'This.lZeroReportedOtsQty'
1978 *.SB_SetLiteralFieldValue("ots_qty", lcOtsMax )
1979 .SB_SetLiteralFieldValue("ots_qty", IIF(This.lZeroReportedOtsQty, SQLFormatNum(0), lcOtsMax))
1980 *=== TechRec 1095862 24-May-2016 smullappilly ===
1981
1982 *=== TechRec 1090759 01-Dec-2015 vkrishnamurthy ===
1983 *=== TechRec 1060583 19-Apr-2012 jisingh ===
1984
1985 *--- TR 1050639 04-Feb-2011 Partha ---
1986*!* .SB_KeySetAddKeySpec("pkey","Char(58)","STR(h.pkey)+d.location+d.division+d.style+d.color_code+d.lbl_code+d.dimension+STR(d.Size_bk)")
1987
1988 *--- TR 1058987 10-Mar-2012 Goutam. Added cust_sku+aux_sku
1989 *--- TechRec 1087873 21-Sep-2015 jisingh Changed Char(250) to Char(274) ===
1990 .SB_KeySetAddKeySpec("pkey","Char(274)","STR(h.pkey)+d.location+d.vnd_qual+d.vnd_id+d.our_qual+ ;
1991 d.our_id+d.division+d.style+d.color_code+d.lbl_code+d.dimension+STR(d.Size_bk)+d.Size_desc+d.UPC+cust_sku+aux_sku ")
1992 *=== TR 1050639 04-Feb-2011 Partha ===
1993
1994 llRetVal = .SB_GenerateSQLInsertString()
1995 ENDWITH
1996
1997 llRetVal = llRetVal AND .vSQLExec(THIS.oSB.cSQLString)
1998 .AdvanceThermo(1)
1999
2000 *--- TR 1064279 7-Nov-2012 Goutam. New set above insertion with different set of condition.
2001 *-- Populating Detail ONCE MORE
2002 WITH THIS.oSB
2003 .SB_Reset()
2004 .cSB_Target = THIS.cInterFaceDetail
2005 .cSB_Source = THIS.cQ_846Dtl
2006 .cSB_SourceAlias = "d"
2007
2008 .cSB_FROMExpandedString = " JOIN " + THIS.cInterFaceHeader + " h " + ;
2009 " ON h.location = d.location" + ;
2010 " AND h.vnd_qual = d.vnd_qual " + ;
2011 " AND h.vnd_id = d.vnd_id " + ;
2012 " AND h.our_qual = d.our_qual " + ;
2013 " AND h.our_id = d.our_id " + ;
2014 " JOIN zzxcstpr cp " + ;
2015 " ON cp.customer = h.customer " + ;
2016 " AND cp.division = d.division " + ;
2017 " AND cp.style = d.style " + ;
2018 " AND cp.color_code = d.color_code " + ;
2019 " AND cp.lbl_code = d.lbl_code " + ;
2020 " AND cp.dimension = d.dimension "
2021
2022 *--- TR 1065893 16-12-2012 VKK
2023 *.cSB_WHEREString = "Not (COALESCE(cp.remove_846,'') <> 'Y' OR (COALESCE(cp.remove_846,'') = 'Y' AND COALESCE(cp.remove_846_sent,'') <> 'Y')) " + ;
2024 * " and (COALESCE(cp.excl_846,'') <> 'Y' OR (COALESCE(cp.excl_846,'') = 'Y' AND COALESCE(cp.excl_846_sent,'') <> 'Y')) " + ;
2025 * " and 1 = case when d.use_excl = 'CPS' and d.custstyle > '' then 1 when d.use_excl = 'CPS' and d.custstyle <= '' then 2 else 1 end "
2026
2027 .cSB_WHEREString = " d.use_excl <> 'N' and d.use_excl > '' " + ; &&Only use these setup records
2028 " AND COALESCE(cp.excl_846,'') <> 'Y' " + ; &&Exclude anything not meant to be included
2029 " AND 1 = case when d.use_excl = 'CPS' AND d.custstyle = '' then 2 ELSE 1 END " + ; && Exclude items set for cust style that have no cust style
2030 " AND NOT (cp.remove_846 = 'Y' AND cp.remove_846_sent = 'Y') " && do not send items that are removed and marked remove sent
2031 *=== TR 1065893 16-12-2012 VKK
2032
2033 .SB_SetLiteralFieldValue("fkey","h.pkey")
2034
2035 .SB_SetLiteralFieldValue("errs_flg_d","''")
2036 .SB_SetLiteralFieldValue("errs_msg_d","''")
2037 .SB_SetLiteralFieldValue("notes","''")
2038 .SB_SetLiteralFieldValue("last_mod",SQLFormatTS(ltLast_Mod))
2039 .SB_SetLiteralFieldValue("user_id",SQLFormatChar(lcUser_id))
2040
2041 .SB_SetLiteralFieldValue("cust_price", "CASE WHEN h.res_cprice = 'Y' THEN COALESCE(cp.price,0) ELSE 0 END")
2042 *--- TR 1065893 16-12-2012 VKK
2043*!* .SB_SetLiteralFieldValue("cust_retail", "CASE WHEN d.cust_retail = 0 THEN COALESCE(cp.retail_price,0) ELSE d.cust_retail END")
2044*!* .SB_SetLiteralFieldValue("discontinue_date", "CASE WHEN d.onhand_qty = 0 THEN '12/31/2039' ELSE '01/01/1900' END")
2045
2046*!* .SB_SetLiteralFieldValue("ots_qty", "COALESCE((CASE WHEN COALESCE(cp.remove_846,'') = 'Y' THEN 0 ELSE CASE WHEN h.use_threshold = 'Y' THEN " + ;
2047*!* "CASE WHEN (d.ots_qty - h.threshold_qty) > 0 THEN (d.ots_qty - h.threshold_qty) ELSE 0 END ELSE ots_qty END END),0)")
2048
2049 .SB_SetLiteralFieldValue("cust_retail", "CASE WHEN d.custstyle = '' THEN COALESCE(cp.retail_price,0) ELSE d.cust_retail END")
2050
2051
2052 *-FH LOCAL
2053 lcOtsThreshold = "COALESCE(CASE WHEN h.use_threshold = 'Y' AND d.ots_qty < h.threshold_qty THEN 0 ELSE ots_qty END ,0)"
2054 lcOtsThreshold = " dbo.bcfn_MAX(0, "+lcOtsThreshold +")"
2055
2056
2057 .SB_SetLiteralFieldValue("discontinue_date", "CASE WHEN cp.remove_846 = 'Y' THEN " + SQLFormatTS(DATE()) + ;
2058 " WHEN "+lcOtsThreshold+" <= 0 THEN '12/31/2039' ELSE '01/01/1900' END")
2059 *--- TechRec 1090759 01-Dec-2015 vkrishnamurthy ---
2060*!* .SB_SetLiteralFieldValue("ots_qty", "COALESCE((CASE WHEN COALESCE(cp.remove_846,'') = 'Y' THEN 0 ELSE " + lcOtsThreshold + " END),0)")
2061 lcOtsQty = "COALESCE((CASE WHEN COALESCE(cp.remove_846,'') = 'Y' THEN 0 ELSE " + lcOtsThreshold + " END),0)"
2062 lcOtsMax = " dbo.bcfn_MIN("+lcOtsQty + ",CASE WHEN h.max_qty_send > 0 THEN h.max_qty_send else 999999999 end )"
2063
2064 *--- TechRec 1095862 24-May-2016 smullappilly --- && Added condition 'This.lZeroReportedOtsQty'
2065 *.SB_SetLiteralFieldValue("ots_qty", lcOtsMax)
2066 .SB_SetLiteralFieldValue("ots_qty", IIF(This.lZeroReportedOtsQty, SQLFormatNum(0), lcOtsMax))
2067 *=== TechRec 1095862 24-May-2016 smullappilly ===
2068
2069 *=== TechRec 1090759 01-Dec-2015 vkrishnamurthy ===
2070 *=== TR 1065893 16-12-2012 VKK
2071
2072 *--- TR 1066673 23-Feb-2013 Bnarayanan ---
2073 *.SB_KeySetAddKeySpec("pkey","Char(250)","STR(h.pkey)+d.location+d.vnd_qual+d.vnd_id+d.our_qual+ ;
2074 d.our_id+d.division+d.style+d.color_code+d.lbl_code+d.dimension+STR(d.Size_bk)+d.Size_desc+d.UPC+cust_sku+aux_sku ")
2075
2076 .SB_KeySetAddKeySpec("pkey","Int","h.pkey")
2077 .SB_KeySetAddKeySpec("pkey1","CHAR(6)","d.location")
2078 .SB_KeySetAddKeySpec("pkey2","CHAR(2)","d.vnd_qual")
2079 .SB_KeySetAddKeySpec("pkey3","CHAR(15)","d.vnd_id")
2080
2081 .SB_KeySetAddKeySpec("pkey4","CHAR(2)","d.our_qual")
2082 .SB_KeySetAddKeySpec("pkey5","CHAR(15)","d.our_id")
2083 .SB_KeySetAddKeySpec("pkey6","CHAR(3)","d.division")
2084 .SB_KeySetAddKeySpec("pkey7","VARCHAR(50)","d.style")
2085
2086 .SB_KeySetAddKeySpec("pkey8","VARCHAR(50)","d.color_code")
2087 .SB_KeySetAddKeySpec("pkey9","CHAR(7)","d.lbl_code")
2088 .SB_KeySetAddKeySpec("pkey10","CHAR(5)","d.dimension")
2089 .SB_KeySetAddKeySpec("pkey11","Int","d.Size_bk")
2090
2091 .SB_KeySetAddKeySpec("pkey12","CHAR(10)","d.Size_desc")
2092 .SB_KeySetAddKeySpec("pkey13","CHAR(12)","d.UPC")
2093 .SB_KeySetAddKeySpec("pkey14","CHAR(27)","cust_sku")
2094 .SB_KeySetAddKeySpec("pkey15","VARCHAR(50)","aux_sku")
2095
2096 *=== TR 1066673 23-Feb-2013 BNarayanan ===
2097
2098 llRetVal = .SB_GenerateSQLInsertString()
2099
2100 ENDWITH
2101
2102 llRetVal = llRetVal AND .vSQLExec(THIS.oSB.cSQLString)
2103 .AdvanceThermo(1)
2104 *=== TR 1064279 7-Nov-2012 Goutam.
2105
2106 *--- TechRec 1060583 19-Apr-2012 jisingh ---
2107 llRetVal = llRetVal AND .vSQLExec(" DELETE h FROM " + .cInterFaceHeader + " h " + ;
2108 " LEFT JOIN " + .cInterFaceDetail + " d " + ;
2109 " ON h.pkey = d.fkey " + ;
2110 " WHERE d.pkey IS NULL ")
2111
2112 llRetVal = llRetVal AND .vSQLExec(" SELECT COUNT(*) ct FROM " + .cInterFaceHeader, "tcCount")
2113
2114 IF llRetVal AND USED("tcCount")
2115 .nTotalLoc = tcCount.ct
2116 .TableClose("tcCount")
2117 ENDIF
2118
2119 *--- TR 1093677 12-Apr-2016 Partha ---
2120 * collect hdr/dtl pkey, fkey and corresponding fields for matching with LOC template
2121
2122 lcSQLString = " UPDATE " + .cQ_846Loc + " SET fkey = d.pkey " + ;
2123 " FROM " + .cInterFaceHeader + " h " + ;
2124 " JOIN " + .cInterFaceDetail + " d " + ;
2125 " ON h.pkey = d.fkey " + ;
2126 " JOIN " + .cQ_846Loc + " T " + ;
2127 " ON d.location = " + ;
2128 " (CASE WHEN T.control_loc > '' THEN T.control_loc ELSE T.location END ) " + ;
2129 " AND h.vnd_qual = T.vnd_qual " + ;
2130 " AND h.vnd_id = T.vnd_id " + ;
2131 " AND h.our_qual = T.our_qual " + ;
2132 " AND h.our_id = T.our_id " + ;
2133 " AND d.division = T.division " + ;
2134 " AND d.UPC = T.UPC " + ;
2135 " AND d.lbl_code = T.lbl_code " + ;
2136 " AND d.cust_sku = T.cust_sku " + ;
2137 " AND d.aux_sku = T.aux_sku "
2138
2139 llRetVal = llRetVal AND .vSQLExec(lcSQLString)
2140 *=== TR 1093677 12-Apr-2016 Partha ===
2141
2142 .oLog.LogResult(llRetVal,"Retrieving Location(s)") && TR 1076961 KISHORE 6-MAR-2014
2143
2144 *=== TechRec 1060583 19-Apr-2012 jisingh ===
2145 ENDWITH
2146
2147 SELECT (lnSelect)
2148 RETURN llRetVal
2149 ENDFUNC
2150*=====================================================
2151 PROCEDURE UnLoadInterfaceToFlatFile
2152 &&--- TechRec 1076524 24-Apr-2014 TSV added tcAddress ===
2153 LPARAMETERS tcProcess, tcFlatFile, tcInterfaceHeader, tcInterfaceDetail, tcAddress
2154
2155 LOCAL llRetVal, lcSQLString, lnHandle
2156
2157 lnHandle = 0
2158 llRetVal= True
2159
2160 WITH THIS
2161 *--Get Metadata for vendor table
2162 IF .GetTranslationDefinition(EDI_ALL_TRANSACTION, "tcEmVend")
2163
2164 IF .GetTranslationDefinition(tcProcess, "tcEmData")
2165 *-- Creating Metadata work tables
2166 .CreateMetaDataWorkTable("tcEmVend", "VENDOR", "tcoibVND")
2167 .CreateMetaDataWorkTable("tcEmData", "HEADER", "tcoibHDR")
2168
2169 .CreateMetaDataWorkTable("tcEmData", "DETAIL", "tcoibDTL")
2170
2171 *--- TechRec 1076524 24-Apr-2014 TSV---
2172 .CreateMetaDataWorkTable("tcEmData", "ADDRESS", "tcoibADR")
2173 *=== TechRec 1076524 24-Apr-2014 TSV===
2174
2175 *--- TR 1093677 05-Apr-2016 Partha ---
2176 .CreateMetaDataWorkTable("tcEmData", "LOC", "tcoibLOC")
2177 *=== TR 1093677 05-Apr-2016 Partha ===
2178
2179 *--- TR 1034990 NSD 8/8/08
2180 * Create index for SEEK/SCAN WHILE
2181 SELECT tcoibDTL
2182 *--- TR 1058987 17-4-2012 VKK
2183 *INDEX ON location TAG location
2184 INDEX ON fkey TAG fkey
2185 *=== TR 1058987 17-4-2012 VKK
2186 *=== TR 1034990 NSD 8/8/08
2187
2188 *--- TechRec 1076524 24-Apr-2014 TSV added ===
2189 SELECT tcoibADR
2190 INDEX ON location TAG location
2191 *=== TechRec 1076524 24-Apr-2014 TSV added ===
2192
2193 *--- TechRec 1058987 18-Apr-2012 jisingh ---
2194 SELECT tcoibHDR
2195
2196 *--- TR 1084425 30-Dec-2015 Partha - Added division ===
2197 INDEX ON location + vnd_qual + vnd_id + division TAG locvnd
2198 *=== TechRec 1058987 18-Apr-2012 jisingh ===
2199
2200 *--- TR 1093677 06-Apr-2016 Partha ---
2201 SELECT tcoibLOC
2202 INDEX ON fkey TAG fkey
2203 *=== TR 1093677 06-Apr-2016 Partha ===
2204
2205 *--- TR 1061446 25-May-2012 Goutam. Moving down. Should try to create flat file if valid record esists.
2206*!* IF NOT .Tie_Setup
2207*!* lcTempFlatfile= STRTRAN(UPPER(tcFlatFile), ".DAT", ".TMP")
2208*!* DELETE FILE (lcTempFlatfile)
2209*!* lnHandle = .OpenFlatFile(lcTempFlatfile, True)
2210*!* ENDIF
2211 *=== TR 1061446 25-May-2012 Goutam
2212
2213 *--- TR 1061446 25-May-2012 Goutam. Should go here always
2214 *IF .Tie_Setup OR lnHandle> 0
2215 IF true
2216 *=== TR 1061446 25-May-2012 Goutam
2217
2218 *-- Populating data to metadata work detail tables from interface tables
2219 *--- TR 1058987 7-Mar-2012 Goutam. Added third parameter in following RemoteScatterGather
2220 .RemoteScatterGather(tcInterfaceHeader,"tcoibHDR", " where errs_flg_h <> 'Y'")
2221 .RemoteScatterGather(tcInterfaceDetail,"tcoibDTL", " where errs_flg_d <> 'Y'")
2222
2223 *--- TechRec 1076524 24-Apr-2014 TSV---
2224 .RemoteScatterGather(tcAddress, "tcoibADR")
2225 *=== TechRec 1076524 24-Apr-2014 TSV===
2226
2227 *--- TR 1093677 13-Apr-2016 Partha ---
2228 .RemoteScatterGather(.cQ_846Loc, "tcoibLOC")
2229 *=== TR 1093677 13-Apr-2016 Partha ===
2230
2231 *--- TR 1084425 13-Jan-2016 Partha ---
2232 *- Remove header records without detail records for matching division
2233 SELECT ("tcoibDTL")
2234 lcDtlTag = TAG() && store old tag
2235 INDEX ON fkey+division TAG fdiv
2236 GO TOP
2237
2238 SELECT ("tcoibHDR")
2239 lcHdrTag = TAG() && store old tag
2240 INDEX ON pkey+division TAG pdiv
2241 SET RELATION TO pkey+division INTO ("tcoibDTL")
2242 GO TOP
2243
2244 SCAN FOR NOT EMPTY(ALLTRIM(division))
2245 IF EOF("tcoibDTL")
2246 DELETE && header
2247 ENDIF
2248 ENDSCAN
2249 SET RELATION OFF INTO ("tcoibDTL")
2250 GOTO TOP
2251
2252 * Remove orphan detail records.
2253 SELECT ("tcoibHDR")
2254 INDEX ON pkey TAG pkey
2255 GOTO TOP
2256
2257 SELECT ("tcoibDTL")
2258 INDEX ON fkey TAG fkey
2259 SET RELATION TO fkey INTO ("tcoibHDR")
2260 GO TOP
2261 SCAN
2262 IF EOF("tcoibHDR")
2263 DELETE && Detail
2264 ENDIF
2265 ENDSCAN
2266 SET RELATION OFF INTO ("tcoibHDR")
2267 GOTO TOP
2268 SET ORDER TO TAG (lcDtlTag) && restore tag
2269 GO TOP
2270 COUNT FOR NOT DELETED() TO .nDtlExport
2271
2272 SELECT ("tcoibHDR")
2273 SET ORDER TO TAG (lcHdrTag) && restore tag
2274 GO TOP
2275 COUNT FOR NOT DELETED() TO .nTotalLoc
2276
2277 .nFailedValidation = 0 && Not required as we are recounting the .nTotalLoc now.
2278 *=== TR 1084425 13-Jan-2016 Partha ===
2279
2280 *--- TR 1084425 18-Jan-2016 Partha ---
2281 * Recalculated after deleting headers and details.
2282
2283*!* *--- TR 1058987 7-Mar-2012 Goutam
2284*!* .nFailedValidation = .nTotalLoc - RECCOUNT("tcoibHDR")
2285*!* .nDtlExport = RECCOUNT("tcoibDTL")
2286*!* *=== TR 1058987 7-Mar-2012 Goutam
2287 *=== TR 1084425 18-Jan-2016 Partha ===
2288
2289 *--- TR 1084609 30-Apr-2015 Partha ---
2290 DECLARE lawkf[1]
2291 lawkf[1] = 0
2292 SELECT COUNT(*) FROM tcoibHDR ;
2293 WHERE cr_auto_wkf = 'Y' ;
2294 INTO ARRAY lawkf
2295 IF lawkf[1] > 0 && need to run workflow
2296 *- Method CanRunWorkflow() checks if workflow '846 BC PROCESS' exists.
2297 *- Sets properties .EDI_servername, .EDI_dbname
2298 .oLog.LogEntry("Validating EDI Workflow Setup:")
2299 IF .IsTieInstalled() AND .CanRunWorkflow()
2300 *- Property lRunWorkFlow is set up in clsedi
2301 .lRunWorkFlow = .T.
2302 USE IN SELECT("__tcWKFList")
2303 Create Cursor __tcWKFList(TempFlatfile memo, cr_auto_wkf CHAR(1), Vnd_ID CHAR(20), Customer CHAR(7))
2304 ENDIF
2305 ENDIF
2306 *=== TR 1084609 30-Apr-2015 Partha ===
2307
2308
2309 *-- Unloading to Flat file
2310 *--- TR 1058987 27-Mar-2012 Goutam
2311 IF (.nTotalLoc - .nFailedValidation) > 0
2312 *=== TR 1058987 27-Mar-2012 Goutam
2313
2314 *--- TR 1067890 03-21-2013 RKI ---*
2315 *--- TR 1061446 25-May-2012 Goutam. Moved here from up. Should try to create flat file if valid record esists.
2316*!* IF NOT .Tie_Setup
2317*!* lcTempFlatfile= STRTRAN(UPPER(tcFlatFile), ".DAT", ".TMP")
2318*!* DELETE FILE (lcTempFlatfile)
2319*!* lnHandle = .OpenFlatFile(lcTempFlatfile, True)
2320*!* ENDIF
2321 lnHandle = 0
2322 *=== TR 1061446 25-May-2012 Goutam
2323
2324*!* ENDIF
2325 *=== TR 1067890 03-21-2013 RKI ===*
2326 *=== TR 1061446 25-May-2012 Goutam
2327
2328 &&--- TechRec 1076524 24-Apr-2014 TSV added "tcoibADR" ===
2329 *--- TR 1093677 06-Apr-2016 Partha added "tcoibLOC" ===
2330 llRetVal = .CreateFlatfileFromMetadata(tcProcess, .cControlReference , "tcoibVND", ;
2331 "tcoibHDR", "tcoibDTL", lnHandle, "tcoibADR", "tcoibLOC")
2332
2333 *--- TR 1058987 27-Mar-2012 Goutam
2334 ENDIF
2335 *=== TR 1058987 27-Mar-2012 Goutam
2336
2337 IF lnHandle > 0
2338 FCLOSE(lnHandle)
2339 ENDIF
2340
2341 IF !llRetVal
2342 .oLog.LogEntry("Creating Flat file from Metadata - failed")
2343 ENDIF
2344 ENDIF
2345 ENDIF
2346 ENDIF
2347 .TableClose('tcoibHDR')
2348 .TableClose('tcoibDTL')
2349 ENDWITH
2350 IF !llRetVal
2351 .oLog.LogEntry("Unloading to flat file - failed")
2352 ENDIF
2353
2354 RETURN llRetVal
2355 ENDPROC
2356*=====================================================
2357 PROCEDURE CreateFlatfileFromMetadata
2358 &&--- TechRec 1076524 24-Apr-2014 TSV added tcWorkAddress ===
2359 *--- TR 1093677 06-Apr-2016 Partha added tcWorkLoc ===
2360 LPARAMETER tcProcess, tcWorkControl, tcWorkVendor, tcWorkHeader, tcWorkDetail, pnHandle, tcWorkAddress, tcWorkLoc
2361
2362 &&--- TechRec 1076524 24-Apr-2014 TSV added llAdrLine ===
2363 LOCAL llRetVal, lnOldSelect, lnCurrentPkey, lcString, llAdrLine, ;
2364 llDelimited, lcDelimiter, ;
2365 lcPreviousLocation, lcControl, lnDtlCount, ;
2366 lcLocation, lcOutBoundFlatFile, lcTempFlatfile, lnHandle, lcOrder
2367
2368 *--- TechRec 1058987 18-Apr-2012 jisingh ---
2369 LOCAL lcVndQual, lcVndId, lcPreviousVndQual, lcPreviousVndId
2370
2371 lcVndQual = ""
2372 lcVndId = ""
2373 lcPreviousVndQual = ""
2374 lcPreviousVndId = ""
2375 *=== TechRec 1058987 18-Apr-2012 jisingh ===
2376
2377 lcPreviousDivision = "" && TR 1084425 04-Jan-2016 Partha
2378
2379 llRetVal= True
2380
2381 lcPreviousLocation = ""
2382 lcLocation = ""
2383 lnHandle = 0
2384 lcDelimiter = ""
2385
2386 *--- TR 1067890 03-21-2013 RKI ---*
2387 lcEDIPath = This.GetEDIFlatFileDirectory("Outbound")
2388 lnHandle = 0
2389 lcOutBoundFlatFile = lcEDIPath + EDI_OIB_FILENAME
2390 *=== TR 1067890 03-21-2013 RKi ===*
2391
2392 *--- TR 1084609 30-Apr-2015 Partha ---
2393 LOCAL lcPrevAutowkf, lcPrevCustomer
2394 STORE "" TO lcPrevAutowkf, lcPrevCustomer
2395 *=== TR 1084609 30-Apr-2015 Partha ===
2396
2397 *--- TR 1093677 06-Apr-2016 Partha ---
2398 LOCAL lnDtlPkey
2399 lnDtlPkey = -1
2400 *=== TR 1093677 06-Apr-2016 Partha ===
2401
2402 lnOldSelect= SELECT()
2403
2404 WITH THIS
2405 *--- TechRec 1060583 19-Apr-2012 jisingh ---
2406 .TableClose("__tcFlatFileList")
2407 CREATE CURSOR __tcFlatFileList(TempFlatFile MEMO, OutBoundFlatFile MEMO)
2408 *=== TechRec 1060583 19-Apr-2012 jisingh ===
2409
2410 *-- Control Reference
2411 lcControl = GetUniqueFileName()
2412
2413 lcSQLString = " SELECT pcr.* " + ;
2414 " FROM " + tcWorkControl + " pcr "
2415
2416 llRetVal = llRetVal AND .vSQLExec(lcSQLString, lcControl)
2417
2418 *--- TR 1034990 NSD 8/8/08
2419 *--- TechRec 1058987 18-Apr-2012 jisingh ---
2420 *INDEX ON ALLTRIM(location) TAG location
2421 *--- TR 1084425 04-Jan-2016 Partha added division ===
2422 INDEX ON ALLTRIM(location) + ALLTRIM(vnd_qual) + ALLTRIM(vnd_id) + ALLTRIM(division) TAG locvnd
2423 *=== TechRec 1058987 18-Apr-2012 jisingh ===
2424 *=== TR 1034990 NSD 8/8/08
2425
2426 *--- TR 1067890 03-21-2013 RKI ---*
2427 LOCAL lcAltFileName, lcPrevAltFileName
2428 lcAltFileName = ""
2429 lcPrevAltFileName = ""
2430 STORE ',' TO lcAltFileNameList
2431 lNewFile = .F.
2432 *=== TR 1067890 03-21-2013 RKI ===*
2433 IF llRetVal
2434
2435 SELECT (tcWorkHeader)
2436 SCAN
2437 * Reset lcString & Counters
2438 lcString = ""
2439 lnDtlCount = 1
2440
2441 lcLocation = Location
2442 *--- TR 1058987 17-4-2012 VKK
2443 lnPkey = Pkey
2444 *=== TR 1058987 17-4-2012 VKK
2445
2446 *--- TechRec 1058987 18-Apr-2012 jisingh ---
2447 lcVndQual = vnd_qual
2448 lcVndId = vnd_id
2449 *=== TechRec 1058987 18-Apr-2012 jisingh ===
2450
2451 lcDivision = Division && TR 1084425 04-Jan-2016 Partha
2452
2453 *--- TR 1067890 03-21-2013 RKI ---*
2454
2455 *--- TR 1084425 04-Jan-2016 Partha ---
2456*!* llLocVndChanged = NOT (lcLocation + lcVndQual + lcVndId == lcPreviousLocation + lcPreviousVndQual + lcPreviousVndId)
2457 llLocVndChanged = NOT (lcLocation + lcVndQual + lcVndId + lcdivision == ;
2458 lcPreviousLocation + lcPreviousVndQual + lcPreviousVndId + lcPreviousdivision)
2459 *=== TR 1084425 04-Jan-2016 Partha ===
2460
2461 *=== TR 1067890 03-21-2013 RKI ===*
2462
2463 SELECT (lcControl)
2464 *--- TR 1034990 NSD 8/8/08
2465 *LOCATE FOR location == lcLocation
2466 *IF FOUND()
2467 *--- TechRec 1058987 18-Apr-2012 jisingh ---
2468 *IF SEEK(ALLTRIM(lcLocation),lcControl,"location")
2469 *--- TR 1084425 04-Jan-2016 Partha added lcDivision ===
2470 IF SEEK(ALLTRIM(lcLocation)+ALLTRIM(lcVndQual)+ALLTRIM(lcVndId)+ALLTRIM(lcDivision),lcControl,"locvnd")
2471 *=== TechRec 1058987 18-Apr-2012 jisingh ===
2472 *=== TR 1034990 NSD 8/8/08
2473
2474 llDelimited = (EDI_Format = 'D')
2475 lcDelimiter = IIF(llDelimited, EDI_Delim, "")
2476 ELSE
2477
2478 *--- TR 1034990 NSD 8/8/08
2479 *LOCATE FOR location == ""
2480 *IF FOUND()
2481 *--- TechRec 1059983 14-Jun-2012 MANI. To avoid index tag not found issue ---
2482 *IF SEEK("",lcControl,"fkey")
2483 IF SEEK("",lcControl,"locvnd")
2484 *=== TechRec 1059983 14-Jun-2012 MANI. ===
2485 *=== TR 1034990 NSD 8/8/08
2486
2487 llDelimited = (EDI_Format = 'D')
2488 lcDelimiter = IIF(llDelimited, EDI_Delim, "")
2489 ENDIF
2490
2491 ENDIF
2492
2493 *--- TR 1067890 03-21-2013 RKI ---*
2494 lcAltFileName = ALLTRIM(Juststem(alt_file_Name))
2495 *--- TR 1084609 30-Apr-2015 Partha ---
2496 IF EVALUATE(tcWorkHeader + ".cr_auto_wkf" ) = 'Y' AND EMPTY(lcAltFileName)
2497 lcAltFileName = Alltrim(vnd_id) + .cProcessID && "846"
2498 ENDIF
2499 *=== TR 1084609 30-Apr-2015 Partha ===
2500
2501 *-TR 1083107 FH moved down
2502*!* IF ATC(","+ALLTRIM(lcAltFileName)+",",lcAltFileNameList ) = 0
2503*!* lcAltFileNameList = lcAltFileNameList + ALLTRIM(lcAltFileName) + ","
2504*!* lNewFile = .T.
2505*!* ELSE
2506*!* lNewFile = .F.
2507*!* ENDIF
2508 *-TR 1083107 FH moved down
2509
2510 *--- TechRec 1076524 24-Apr-2014 TSV---
2511 llAdrLine = False
2512 IF llLocVndChanged AND resv_loc_adr = 'Y'
2513 SELECT (tcWorkAddress)
2514
2515 IF SEEK(lcLocation, tcWorkAddress, "location")
2516 llAdrLine = True
2517 ELSE
2518 llAdrLine = False
2519 ENDIF
2520
2521 ENDIF
2522 *=== TechRec 1076524 24-Apr-2014 TSV===
2523
2524 Select (tcWorkHeader)
2525 *=== TR 1067890 03-21-2013 RKI ===*
2526
2527 *--- TechRec 1058987 18-Apr-2012 jisingh ---
2528 *IF NOT ( lcLocation == lcPreviousLocation )
2529 *--- TR 1067890 03-201-2013 RKI ---*
2530*!* IF NOT (lcLocation + lcVndQual + lcVndId == lcPreviousLocation + lcPreviousVndQual + lcPreviousVndId)
2531 IF llLocVndChanged OR !(lcAltFileName == lcPrevAltFileName)
2532
2533 lcPreviousDivision = Division && TR 1084425 04-Jan-2016 Partha
2534
2535 *=== TR 1067890 03-21-2013 RKI ===*
2536 *=== TechRec 1058987 18-Apr-2012 jisingh ===
2537 SELECT (lcControl)
2538 lcPreviousLocation = Location
2539 *--- TechRec 1058987 18-Apr-2012 jisingh ---
2540 lcPreviousVndQual = vnd_qual
2541 lcPreviousVndId = vnd_id
2542 *=== TechRec 1058987 18-Apr-2012 jisingh ===
2543
2544 SELECT (tcWorkVendor)
2545 APPEND BLANK
2546 REPLACE vnd_tran WITH tcProcess, ;
2547 vnd_id WITH EVALUATE(lcControl + ".vnd_id"), ;
2548 vnd_vers WITH EVALUATE(lcControl + ".vnd_vers") IN (tcWorkVendor)
2549
2550 *-- Create new file name and handle
2551 *--- TR 1067890 03-21-2013 RKI ---*
2552*!* IF .Tie_Setup
2553 *=== TR 1067890 03-21-2013 RKI ===*
2554 IF lnHandle > 0
2555 FCLOSE (lnHandle)
2556 lnHandle = 0 && TR 1067890 03-21-2013 RKI
2557 *--- TR 1067890 03-21-2013 RKI ---*
2558*!* lcTempFlatfile = STRTRAN(UPPER(lcOutBoundFlatFile), ".DAT", ".TMP")
2559 IF llRetVal AND ((llLocVndChanged AND .Tie_Setup) OR !(lcAltFileName == lcPrevAltFileName))
2560 *=== TR 1067890 03-21-2013 RKI ===*
2561 *--- TechRec 1060583 19-Apr-2012 jisingh ---
2562 *llRetVal = llRetVal and .CopyToFlatFileInBlock(lcTempFlatfile, lcOutBoundFlatFile)
2563
2564 *--- TR 1084609 30-Apr-2015 Partha ---
2565*!* INSERT INTO __tcFlatFileList(TempFlatFile,OutBoundFlatFile) VALUES (lcTempFlatfile, lcOutBoundFlatFile)
2566
2567 IF .lRunWorkflow AND lcPrevAutowkf = 'Y'
2568 INSERT INTO __tcWKFList(TempFlatfile, cr_auto_wkf, Vnd_ID, Customer) ;
2569 VALUES (lcTempFlatfile, 'Y', lcPreviousVndId, lcPrevCustomer)
2570 ELSE
2571 *-TR 1083107 FH
2572 *INSERT INTO __tcFlatFileList(TempFlatFile,OutBoundFlatFile) VALUES (lcTempFlatfile, lcOutBoundFlatFile)
2573 .InsertIntoFlatFileCursor("__tcFlatFileList",lcTempFlatfile,lcOutBoundFlatFile)
2574 ENDIF
2575
2576 *=== TR 1084609 30-Apr-2015 Partha ===
2577
2578 *=== TechRec 1060583 19-Apr-2012 jisingh ===
2579 *--- TR 1067890 03-21-2013 RKI ---*
2580 ENDIF
2581 *=== TR 1067890 03-21-2013 RKI ===*
2582 ENDIF
2583
2584 *--- TR 1067890 03-21-2013 RKI ---*
2585 IF EMPTY(lcAltFileName )
2586 *- TR 1084609 30-Apr-2015 Partha. Added AND lcPrevAutowkf < 'Y'. Auto workflow requires unique file name.
2587 IF !.Tie_Setup AND lcPrevAutowkf < 'Y'
2588
2589 lcOutBoundFlatFile = lcEDIPath + EDI_OIB_FILENAME
2590 ELSE
2591 lcOutBoundFlatFile = lcEDIPath + Alltrim(Evaluate(tcWorkVendor + ".vnd_id")) + EDI_OIB_FILENAME
2592 ENDIF
2593 ELSE
2594 lcOutBoundFlatFile = lcEDIPath + lcAltFileName +".DAT"
2595 ENDIF
2596 *=== TR 1067890 03-21-2013 RKI ===*
2597
2598 *-- New flat file name
2599 *--- TR 1067890 03-21-2013 RKI ---*
2600*!* lcOutBoundFlatFile = lcEDIPath + ALLTRIM(EVALUATE(tcWorkVendor + ".vnd_id")) + EDI_OIB_FILENAME
2601 *=== TR 1067890 03-21-2013 RKI ===*
2602 lcTempFlatfile = STRTRAN(UPPER(lcOutBoundFlatFile), ".DAT", ".TMP")
2603 *--- TR 1067890 03-21-2013 RKI ---*
2604
2605 *-TR 1083107 FH
2606 lcTempFileName = STRTRAN(lcTempFlatfile,UPPER(lcEDIPath), "") && Get rid of path
2607 lcTempFileName = STRTRAN(lcTempFileName, ".TMP", "") && get rid of ext
2608 IF ATC(","+ALLTRIM(lcTempFileName)+",",lcAltFileNameList ) = 0
2609 lcAltFileNameList = lcAltFileNameList + ALLTRIM(lcTempFileName) + ","
2610 lNewFile = .T.
2611 ELSE
2612 lNewFile = .F.
2613 ENDIF
2614 *-TR 1083107 FH
2615
2616 IF lNewFIle
2617 *=== TR 1067890 03-21-2013 RKI ===*
2618 DELETE FILE (lcTempFlatfile)
2619 *--- TR 1067890 03-21-2013 RKI ---*
2620 ENDIF
2621 *=== TR 1067890 -03-21-2013 RKI ===*
2622
2623 lnHandle = .OpenFlatFile(lcTempFlatfile, .T.)
2624 IF lnHandle <= 0
2625 LOOP
2626 ENDIF
2627 =FSEEK(lnhandle,0,2) && *--- TR 1067890 03-21-2013 RKI ---*
2628*!* ENDIF && TR 1067890 03-21-2013 RKI
2629 ENDIF
2630
2631 *---- TR 1064402 12-OCT-12 Venuk
2632 *-- Output Vendor string "VND"
2633*!* lcString = lcString + EDI_VENDOR_TAG + lcDelimiter + .ConvertRecordToText(tcWorkVendor, llDelimited, lcDelimiter)
2634
2635*!* SELECT (tcWorkHeader)
2636*!* lcString = lcString + EDI_HEADER_TAG + lcDelimiter + .ConvertRecordToText(tcWorkHeader, llDelimited, lcDelimiter)
2637 *=== TR 1064402 12-OCT-12 Venuk
2638 *-- Output Detail string "DTL"
2639 SELECT (tcWorkDetail)
2640
2641 *--- TR 1034990 NSD 8/7/08
2642 * remove 10,000 detail restriction, do a seek/scan while instead of scan for.
2643 *SCAN FOR Location = lcLocation AND lnDtlCount < 10000
2644 *--- TR 1058987 17-4-2012 VKK replaced location seek with pkey/fkey seek
2645 IF SEEK(lnPkey,tcWorkDetail,"fkey")
2646 lcOrder = SET("ORDER")
2647 SET ORDER TO fkey &&*--- TR 1058987 17-4-2012 VKK changed to fkey
2648 SCAN WHILE fkey == lnPkey && *--- TR 1058987 17-4-2012 VKK changed to fkey fromlocation
2649 *---- TR 1064402 12-OCT-12 Venuk
2650
2651 lnDtlPkey = pkey && TR 1093677 06-Apr-2016 Partha
2652
2653 IF lnDtlCount = 1 OR MOD(lnDtlCount,10000) = 0
2654 *-- Output Vendor string "VND"
2655 SELECT (tcWorkVendor)
2656 lcString = lcString + EDI_VENDOR_TAG + lcDelimiter + .ConvertRecordToText(tcWorkVendor, llDelimited, lcDelimiter)
2657 lnDtlCount = lnDtlCount + 1 && TR 1080713 count every line
2658 *-- Output header string "HDR"
2659 SELECT (tcWorkHeader)
2660 *--- TechRec 1079354 14-Jul-2014 MANI. ---
2661 IF lnDtlCount <= 10000
2662 REPLACE PURP_CODE WITH '00'
2663 ELSE
2664 REPLACE PURP_CODE WITH '25'
2665 ENDIF
2666 *=== TechRec 1079354 14-Jul-2014 MANI. ===
2667
2668 lcString = lcString + EDI_HEADER_TAG + lcDelimiter + .ConvertRecordToText(tcWorkHeader, llDelimited, lcDelimiter)
2669 lnDtlCount = lnDtlCount + 1 && TR 1080713 count every line
2670
2671 *--- TechRec 1076524 24-Apr-2014 TSV---
2672 IF llAdrLine
2673 lcString = lcString + EDI_ADDRESS_TAG + lcDelimiter + .ConvertRecordToText(tcWorkAddress, llDelimited, lcDelimiter)
2674 lnDtlCount = lnDtlCount + 1 && TR 1080713 count every line
2675 ENDIF
2676 *=== TechRec 1076524 24-Apr-2014 TSV===
2677
2678 SELECT (tcWorkDetail)
2679 ENDIF
2680 *=== TR 1064402 12-OCT-12 Venuk
2681
2682 *--- TR 1084425 11-Jan-2016 Partha ---
2683 IF EMPTY(lcDivision) OR ( lcDivision=Division )
2684 *=== TR 1084425 11-Jan-2016 Partha ===
2685
2686 lcString = lcString + EDI_DETAIL_TAG + lcDelimiter + .ConvertRecordToText(tcWorkDetail, llDelimited, lcDelimiter)
2687 lnDtlCount = lnDtlCount + 1
2688
2689 *--- TR 1093677 06-Apr-2016 Partha ---
2690 * Write location detail line
2691 SELECT (tcWorkLoc)
2692 IF SEEK(lnDtlPkey,tcWorkLoc,"fkey")
2693 SCAN WHILE fkey=lnDtlPkey
2694 lcString = lcString + EDI_LOC_TAG + lcDelimiter + .ConvertRecordToText(tcWorkLoc, llDelimited, lcDelimiter)
2695 lnDtlCount = lnDtlCount + 1 && to maintain line output restrictions
2696 ENDSCAN
2697 ENDIF
2698 *=== TR 1093677 06-Apr-2016 Partha ===
2699
2700 *--- TR 1084425 11-Jan-2016 Partha ---
2701 ELSE
2702 .nDtlExport = .nDtlExport - 1
2703 ENDIF
2704 *=== TR 1084425 11-Jan-2016 Partha ===
2705
2706 *-TR 1070774 FH
2707 *- I want to mod here
2708 *- take whatever lcString I have already and write it into lcTempFlatfile
2709 *- clear lcString and continue
2710 IF MOD(lnDtlCount,10000) = 0
2711 .oLog.LogEntry("String getting too big, writing to file right away")
2712 .PrepareEmptyDateForGentran(@lcString) && prep string first
2713 *- if .Tie_setup is .T. meaning it's 4.4 we created lnHandle in this current method so (l)nHandle
2714 *- Other wise it was created outside of this method (5.2, Gentran) so (p)nHandle
2715 *llRetVal = .WriteToFileNow(@lcString, IIF(.Tie_Setup,lnHandle,pnHandle), lcTempFlatfile)
2716 llRetVal = .WriteToFileNow(@lcString, lnHandle, lcTempFlatfile)
2717 ENDIF
2718
2719
2720 ENDSCAN
2721 ENDIF
2722 *=== TR 1034990 NSD 8/7/08
2723
2724 *-- Write lcString to flat file
2725 IF !EMPTY(lcString)
2726 .PrepareEmptyDateForGentran(@lcString)
2727 *--- TR 1067890 03-21-2013 RKI ---*
2728*!* IF NOT .Tie_Setup
2729*!* lnHandle = pnHandle
2730*!* ENDIF
2731 *=== TR 1067890 03-21-2013 RKI ===*
2732
2733 *- FH 1070774 - Since we added method WriteToFileNow to write to file in mid process
2734 *- we need set the handle to EOF then write it.
2735 *- without moving to EOF we will replace whatever is left in lcString to the whole file.
2736 FSEEK(lnHandle, 0, 2)
2737
2738 IF FWRITE(lnHandle, lcString) = 0
2739 lRetVal = .F.
2740 ENDIF
2741 ENDIF
2742
2743 *--- TR 1067890 -03-21-2013 RKI ---*
2744 lcPrevAltFileName = ALLTRIM(UPPER(lcAltFileName))
2745 *=== TR 1067890 03-21-2013 RKI ===*
2746 *--- TR 1084609 30-Apr-2015 Partha ---
2747 Select (tcWorkHeader)
2748 lcPrevCustomer = Customer
2749 lcPrevAutowkf = cr_auto_wkf
2750 *=== TR 1084609 30-Apr-2015 Partha ===
2751
2752 ENDSCAN
2753
2754 *--- TR 1067890 03-21-2013 RKI ---*
2755*!* IF .Tie_Setup AND lnHandle > 0
2756 IF lnHandle > 0
2757 *=== TR 1067890 03-21-2013 RKI ===*
2758 *-- Close previously opened file
2759 FCLOSE(lnHandle)
2760 IF llRetVal
2761 *--- TechRec 1060583 19-Apr-2012 jisingh ---
2762 *llRetVal = .CopyToFlatFileInBlock(lcTempFlatfile, lcOutBoundFlatFile)
2763
2764 *--- TR 1084609 30-Apr-2015 Partha ---
2765*!* INSERT INTO __tcFlatFileList(TempFlatFile,OutBoundFlatFile) VALUES (lcTempFlatfile, lcOutBoundFlatFile)
2766 IF .lRunWorkflow AND lcPrevAutowkf = 'Y'
2767 INSERT INTO __tcWKFList(TempFlatfile, cr_auto_wkf, Vnd_ID, Customer) ;
2768 VALUES (lcTempFlatfile, 'Y', lcPreviousVndId, lcPrevCustomer)
2769 ELSE
2770 *-TR 1083107 FH
2771 *INSERT INTO __tcFlatFileList(TempFlatFile,OutBoundFlatFile) VALUES (lcTempFlatfile, lcOutBoundFlatFile)
2772 .InsertIntoFlatFileCursor("__tcFlatFileList",lcTempFlatfile,lcOutBoundFlatFile)
2773 ENDIF
2774 *=== TR 1084609 30-Apr-2015 Partha ===
2775
2776 *=== TechRec 1060583 19-Apr-2012 jisingh ===
2777 ENDIF
2778 ENDIF
2779 ENDIF
2780
2781 *-- Close All Work Tables
2782 .TableClose(tcWorkVendor)
2783 .TableClose(tcWorkHeader)
2784 .TableClose(tcWorkDetail)
2785 .TableClose(lcControl)
2786 ENDWITH
2787
2788 SELECT(lnOldSelect)
2789 RETURN llRetVal
2790 ENDPROC
2791*=====================================================
2792 PROCEDURE UpdateMessage
2793 LOCAL llRetVal, lcmsg1, lcmsg2
2794
2795 *--- TR 1058987 7-Mar-2012 Goutam
2796 LOCAL lcmsg3
2797 STORE "" TO lcmsg1, lcmsg2, lcmsg3
2798 *=== TR 1058987 7-Mar-2012 Goutam
2799
2800 WITH THIS
2801 llRetVal= .T.
2802 .cMessage= IIF(!EMPTY(.cMessage), .cMessage + CRLF, "")
2803
2804 *--- TR 1058987 7-Mar-2012 Goutam
2805*!* lcmsg1 = IIF(.nTotalLoc>0, ALLTRIM(STR(.nTotalLoc)) + " Location(s) successfully processed." + CRLF ,"")
2806*!* lcmsg2 = IIF(.nDtlExport>0,ALLTRIM(STR(.nDtlExport)) + " SKU(s) moved from Interface table to flat file." + CRLF,"")
2807*!*
2808*!* .cMessage= .cMessage + (lcmsg1 + lcmsg2 )
2809
2810 IF .nFailedValidation > 0
2811 lcmsg1 = IIF(.nTotalLoc>0, ALLTRIM(STR(.nTotalLoc)) + " Record(s) processed." + CRLF ,"")
2812 *--- TR 1076961 KISHORE 6-MAR-2014
2813 *lcmsg2 = IIF(.nFailedValidation>0, ALLTRIM(STR(.nFailedValidation)) + " Record(s) failed validation." + CRLF ,"")
2814 lcFailMsg = " Record(s) failed validation - See History Log."
2815 lcmsg2 = IIF(.nFailedValidation>0, ALLTRIM(STR(.nFailedValidation)) + lcFailMsg + CRLF ,"")
2816 *=== TR 1076961 KISHORE 6-MAR-2014
2817 ELSE
2818 lcmsg1 = IIF(.nTotalLoc>0, ALLTRIM(STR(.nTotalLoc)) + " Record(s) successfully processed." + CRLF ,"")
2819 ENDIF
2820 IF (.nTotalLoc - .nFailedValidation) > 0
2821 lcmsg3 = IIF(.nDtlExport>0,ALLTRIM(STR(.nDtlExport)) + " SKU(s) moved from Interface table to flat file." + CRLF,"")
2822
2823 *--- TechRec 1076326 13-May-2014 asharma ---
2824 .oLog.LogEntry("Total records to process: " + ALLTRIM(STR(.nDtlExport)),.T.)
2825 *=== TechRec 1076326 13-May-2014 asharma ===
2826
2827 ENDIF
2828
2829 .cMessage= .cMessage + (lcmsg1 + lcmsg2 + lcmsg3)
2830
2831 *--- TR 1058987 7-Mar-2012 Goutam
2832
2833 ENDWITH
2834 RETURN llRetVal
2835 ENDPROC
2836*=====================================================
2837 PROCEDURE RemoteScatterGather
2838 LPARAMETERS tcRemoteTable, tcLocalCursor, tcWhere
2839 LOCAL llRetVal, lnSelect, lcSQLString, lcTempCursor
2840
2841 llRetVal = False
2842 lnSelect = SELECT()
2843 lcTempCursor= GetUniqueFileName()
2844
2845 lcSQLString = "SELECT * FROM " + tcRemoteTable + ;
2846 IIF(NOT EMPTY(tcWhere), tcWhere, '')
2847
2848 IF .vSQLExec(lcSQLString, lcTempCursor)
2849 .ScatterGather(lcTempCursor, tcLocalCursor)
2850 llRetVal = True
2851 ENDIF
2852
2853 THIS.TableClose(lcTempCursor)
2854
2855 RETURN llRetVal
2856 ENDPROC
2857*=====================================================
2858 FUNCTION DropTempTables
2859 LOCAL llRetVal, lnSelect, lcSQLString
2860
2861 llRetVal = true
2862 lnSelect = SELECT()
2863
2864 WITH This
2865 IF NOT EMPTY(.cQ_846Dtl)
2866 lcSQLString = "DROP TABLE " + .cQ_846Dtl
2867 llRetVal = llRetVal AND .vSQLExec(lcSQLString)
2868 ENDIF
2869
2870 IF NOT EMPTY(.cQ_846Hdr)
2871 lcSQLString = "DROP TABLE " + .cQ_846Hdr
2872 llRetVal = llRetVal AND .vSQLExec(lcSQLString)
2873 ENDIF
2874
2875 *--- TR 1093677 01-Apr-2016 Partha ---
2876 IF NOT EMPTY(.cQ_846Loc)
2877 lcSQLString = "DROP TABLE " + .cQ_846Loc
2878 llRetVal = llRetVal AND .vSQLExec(lcSQLString)
2879 ENDIF
2880 *=== TR 1093677 01-Apr-2016 Partha ===
2881
2882 .cQ_846Dtl = ''
2883 .cQ_846Hdr = ''
2884 .cQ_846Loc = '' && TR 1093677 01-Apr-2016 Partha
2885
2886 ENDWITH
2887
2888 SELECT (lnSelect)
2889 RETURN llRetVal
2890
2891 ENDFUNC
2892
2893*=====================================================
2894
2895 *--- TechRec 1060583 19-Apr-2012 jisingh ---
2896 FUNCTION UpdateCustomerPriceFlag
2897 LPARAMETERS tcParam1, tcParam2
2898 LOCAL llRetVal, lnSelect, lcSQLString
2899
2900 llRetVal = true
2901 lnSelect = SELECT()
2902
2903 WITH This
2904
2905 *--- TR 1064279 Goutam Removed existing Where condition and replaced with the new one.
2906 *--- TR 1065893 16-12-2012 VKK
2907 * Commented first update. do not set any exclusion flags
2908 *lcSQLString = " UPDATE cp " + ;
2909 " SET excl_846_sent = 'Y' " + ;
2910 " ,excl_846_date = " + SQLFormatTS(DATETIME()) + ;
2911 " ,last_mod = " + SQLFormatTS(DATETIME()) + ;
2912 " ,user_id = " + SQLFormatChar(goenv.envlogin.cUserName) + ;
2913 " FROM zzxcstpr cp " + ;
2914 " JOIN " + .cQ_846Dtl + " d " + ;
2915 " ON cp.division = d.division " + ;
2916 " AND cp.style = d.style " + ;
2917 " AND cp.color_code = d.color_code " + ;
2918 " AND cp.lbl_code = d.lbl_code " + ;
2919 " AND cp.dimension = d.dimension " + ;
2920 " JOIN " + .cInterFaceHeader + " h " + ;
2921 " ON h.location = d.location" + ;
2922 " AND h.vnd_qual = d.vnd_qual " + ;
2923 " AND h.vnd_id = d.vnd_id " + ;
2924 " AND h.our_qual = d.our_qual " + ;
2925 " AND h.our_id = d.our_id " + ;
2926 " AND h.customer = cp.customer " + ;
2927 " AND h.errs_flg_h <> 'Y' " + ; &&--- TechRec 1061287 02-May-2012 jisingh ===
2928 " WHERE Not (COALESCE(cp.remove_846,'') <> 'Y' OR (COALESCE(cp.remove_846,'') = 'Y' AND COALESCE(cp.remove_846_sent,'') <> 'Y')) " + ; && *--- TR 1064279 Goutam
2929 " and (COALESCE(cp.excl_846,'') <> 'Y' OR (COALESCE(cp.excl_846,'') = 'Y' AND COALESCE(cp.excl_846_sent,'') <> 'Y')) " + ; && *--- TR 1064279 Goutam
2930 " and 1 = case when d.use_excl = 'CPS' and d.custstyle > '' then 1 when d.use_excl = 'CPS' and d.custstyle <= '' then 2 else 1 end " && *--- TR 1064279 Goutam
2931
2932 *llRetVal = llRetVal AND .vSQLExec(lcSQLString)
2933
2934 *--- TR 1064279 7-Nov-2012 Goutam.
2935 *--- TR 1065893 16-12-2012 VKK
2936 * Replace " WHERE (COALESCE(cp.remove_846,'') <> 'Y' OR (COALESCE(cp.remove_846,'') = 'Y' AND COALESCE(cp.remove_846_sent,'') <> 'Y'))"
2937 * with " WHERE cp.excl_846 <> 'Y' AND cp.remove_846 = 'Y' AND cp.remove_846_sent <> 'Y' "
2938 *=== TR 1065893 16-12-2012 VKK
2939 lcSQLString = " UPDATE cp " + ;
2940 " SET remove_846_sent = 'Y' " + ;
2941 " ,remove_846_date = " + SQLFormatTS(DATETIME()) + ;
2942 " ,last_mod = " + SQLFormatTS(DATETIME()) + ;
2943 " ,user_id = " + SQLFormatChar(goenv.envlogin.cUserName) + ;
2944 " FROM zzxcstpr cp " + ;
2945 " JOIN " + .cQ_846Dtl + " d " + ;
2946 " ON cp.division = d.division " + ;
2947 " AND cp.style = d.style " + ;
2948 " AND cp.color_code = d.color_code " + ;
2949 " AND cp.lbl_code = d.lbl_code " + ;
2950 " AND cp.dimension = d.dimension " + ;
2951 " JOIN " + .cInterFaceHeader + " h " + ;
2952 " ON h.location = d.location" + ;
2953 " AND h.vnd_qual = d.vnd_qual " + ;
2954 " AND h.vnd_id = d.vnd_id " + ;
2955 " AND h.our_qual = d.our_qual " + ;
2956 " AND h.our_id = d.our_id " + ;
2957 " AND h.customer = cp.customer " + ;
2958 " AND h.errs_flg_h <> 'Y' " + ;
2959 " WHERE cp.excl_846 <> 'Y' AND cp.remove_846 = 'Y' AND cp.remove_846_sent <> 'Y' " && *--- TR 1065893 16-12-2012 VKK
2960
2961 llRetVal = llRetVal AND .vSQLExec(lcSQLString)
2962 *=== TR 1064279 7-Nov-2012 Goutam
2963
2964 ENDWITH
2965
2966 SELECT (lnSelect)
2967 RETURN llRetVal
2968 ENDFUNC
2969 *=== TechRec 1060583 19-Apr-2012 jisingh ===
2970
2971 *--- TechRec 1059983 31-May-2012 MANI. ---
2972 FUNCTION ValidateParamBro
2973
2974 LOCAL llRetVal, lnSelect,lcValue, lnIndex
2975
2976 llRetVal = true
2977 lnSelect = SELECT()
2978 lnIndex = 0
2979 WITH This
2980 *-- Availablity Determination bro validation
2981 IF llRetVal
2982 lnIndex = ASCAN(.aParamBROs,"AVAIL_OPTION",1,ALEN(.aParamBROs,1),1,9)
2983 IF lnIndex > 0
2984 .cAvail_Option = (ALLTRIM(.aParamBROs[lnIndex, 2]))
2985
2986 IF NOT EMPTY(.cAvail_Option) AND (LEN(.cAvail_Option) > 1 OR !INLIST(.cAvail_Option ,'Q','O','A'))
2987
2988 .cMessage= "Invalid Available Based on value."
2989 .oLog.LogEntry(.cMessage)
2990 llRetVal = False
2991 ENDIF
2992 .oLog.LogEntry("Available Based on :" + .cAvail_Option)
2993 ENDIF
2994
2995 ENDIF
2996
2997 *-- % of Available bro validation
2998 IF llRetVal
2999
3000 lnIndex = ASCAN(.aParamBROs,"PERCENT_AVAIL",1,ALEN(.aParamBROs,1),1,9)
3001 IF lnIndex > 0
3002 .nPercent_Avail = VAL((ALLTRIM(.aParamBROs[lnIndex, 2])))
3003
3004 IF .nPercent_Avail < 0 OR .nPercent_Avail > 100
3005
3006 .cMessage= "% of Available Value Should be 0 to 100"
3007 .oLog.LogEntry(.cMessage)
3008 llRetVal = False
3009 ENDIF
3010 IF .nPercent_Avail = 0
3011 .nPercent_Avail = 100
3012 ENDIF
3013 .oLog.LogEntry("% of Available :" + ALLTRIM(STR(.nPercent_Avail)))
3014 ENDIF
3015
3016 ENDIF
3017
3018 *--- TechRec 1062747 18-Jul-2012 MANI. ---
3019 *-- Show zero qty bro validation
3020 IF llRetVal
3021 lnIndex = ASCAN(.aParamBROs,"SHOW_ZERO_QTY",1,ALEN(.aParamBROs,1),1,9)
3022 IF lnIndex > 0
3023 .cShowZeroQty = (ALLTRIM(.aParamBROs[lnIndex, 2]))
3024
3025 IF NOT EMPTY(.cShowZeroQty) AND !INLIST(.cShowZeroQty,'Y','N')
3026 .cMessage= "Invalid Show Zero Qty value. Should be Y or N"
3027 .oLog.LogEntry(.cMessage)
3028 llRetVal = False
3029 ENDIF
3030 .oLog.LogEntry("Show Zero Qty :" + .cShowZeroQty)
3031 ENDIF
3032
3033 ENDIF
3034
3035 *-- MIN size qty avl bro Validation
3036 IF llRetVal
3037 lnIndex = ASCAN(.aParamBROs,"SFNSIZE_QTY",1,ALEN(.aParamBROs,1),1,9)
3038 IF lnIndex > 0
3039 .nMinSzQtyAvl = (ALLTRIM(.aParamBROs[lnIndex, 2]))
3040
3041 IF NOT EMPTY(.nMinSzQtyAvl) AND TYPE(.nMinSzQtyAvl) <> "N"
3042 .cMessage= "Invalid MIN size qty avl. Should be Numeric"
3043 .oLog.LogEntry(.cMessage)
3044 llRetVal = False
3045 ENDIF
3046 .oLog.LogEntry("MIN size qty avl :" + .nMinSzQtyAvl)
3047
3048 ENDIF
3049 ENDIF
3050
3051 *=== TechRec 1062747 18-Jul-2012 MANI. ===
3052
3053 *--- TR 1072524 7-Aug-2013 Goutam
3054 *--- Order Type Bro for bulk order validation
3055 IF llRetVal
3056 lnIndex = ASCAN(.aParamBROs,"BULK_ORDER_TYPE", 1, ALEN(.aParamBROs,1),1,9)
3057 IF lnIndex > 0
3058 .cBulkOrderType = (ALLTRIM(.aParamBROs[lnIndex, 2]))
3059
3060 IF !EMPTY(.cBulkOrderType) AND !vl_typer(.cBulkOrderType)
3061 .cMessage= "Invalid Bulk Order Type."
3062 .oLog.LogEntry(.cMessage)
3063 llRetVal = False
3064 ENDIF
3065 .oLog.LogEntry("Bulk Order Type :" + .cBulkOrderType)
3066 ENDIF
3067 ENDIF
3068 *=== TR 1072524 7-Aug-2013 Goutam
3069
3070 *--- TechRec 1079208 29-Oct-2014 TSV---
3071 IF llRetVal
3072 lnIndex = ASCAN(.aParamBROs,"PRM_EXCLUDE_WO_CUSTSKU_846", 1, ALEN(.aParamBROs,1),1,9)
3073 IF lnIndex > 0
3074
3075 lcCustSku = (ALLTRIM(.aParamBROs[lnIndex, 2]))
3076
3077 IF !EMPTY(lcCustSku) AND !INLIST(lcCustSku, "Y", "N")
3078 .cMessage= "Parameter 'Exclude SKU without Customer style' value is invalid."
3079 .oLog.LogEntry(.cMessage)
3080 llRetVal = False
3081 ENDIF
3082 .oLog.LogEntry("Exclude SKU without Customer style :" + lcCustSku )
3083
3084 .lCustSku = (lcCustSku = "Y")
3085 ENDIF
3086 ENDIF
3087 *=== TechRec 1079208 29-Oct-2014 TSV===
3088
3089 *--- TechRec 1095862 24-May-2016 smullappilly ---
3090 IF llRetVal
3091 lnIndex = ASCAN(.aParamBROs,"PRM_OTS_QTY", 1, ALEN(.aParamBROs,1),1,9)
3092 IF lnIndex > 0
3093
3094 lcOtsQty = (ALLTRIM(.aParamBROs[lnIndex, 2]))
3095
3096 IF !EMPTY(lcOtsQty) AND !INLIST(lcOtsQty, "Y", "N")
3097 .cMessage= "Parameter 'Zero Reported OTS Qty' value is invalid."
3098 .oLog.LogEntry(.cMessage)
3099 llRetVal = False
3100 ENDIF
3101 .oLog.LogEntry("Zero Reported OTS Qty :" + lcOtsQty )
3102
3103 .lZeroReportedOtsQty = (lcOtsQty = "Y")
3104 ENDIF
3105 ENDIF
3106 *=== TechRec 1095862 24-May-2016 smullappilly ===
3107
3108 ENDWITH
3109
3110 SELECT (lnSelect)
3111 RETURN llRetVal
3112 ENDFUNC
3113 *=== TechRec 1059983 31-May-2012 MANI. ===
3114
3115 *--- TechRec 1072901 29-Aug-2013 MANI. ---
3116
3117 FUNCTION BuildFilterForParamBro
3118
3119 LOCAL llRetVal, lnSelect,lcValue,lcConf_typeFilter,lcNsr_codeFilter,lcFltstr
3120
3121 STORE "" TO lcValue,lcConf_typeFilter,lcNsr_codeFilter,lcFltstr
3122
3123 llRetVal = true
3124 lnSelect = SELECT()
3125
3126 WITH This
3127 lnIndex = ASCAN(.aParamBROs,"NSR_CODE", 1, ALEN(.aParamBROs,1),1,9)
3128 IF lnIndex > 0
3129 lcValue = (ALLTRIM(.aParamBROs[lnIndex, 2]))
3130
3131 IF !EMPTY(lcValue)
3132 lcNsr_codeFilter = .GetFilterString(lcValue,"dv.nsr_code","C")
3133 *--- TechRec 1090249 01-Oct-2015 jisingh Commented ---
3134 *lcFltstr = lcNsr_codeFilter
3135 *=== TechRec 1090249 01-Oct-2015 jisingh ===
3136 *--- Tr 1091168 30-Nov-2015 Dilip ---
3137 .cNsrCode = lcNsr_codeFilter
3138 *=== Tr 1091168 30-Nov-2015 Dilip ===
3139 .oLog.LogEntry("Filter String Nsr_Code:" + lcNsr_codeFilter)
3140 ENDIF
3141
3142 ENDIF
3143
3144 lnIndex = ASCAN(.aParamBROs,"CONF_TYPE", 1, ALEN(.aParamBROs,1),1,9)
3145 IF lnIndex > 0
3146 lcValue = (ALLTRIM(.aParamBROs[lnIndex, 2]))
3147
3148 IF !EMPTY(lcValue)
3149 lcConf_typeFilter = .GetFilterString(lcValue,"dv.conf_type","C")
3150 *--- TechRec 1090249 01-Oct-2015 jisingh Commented ---
3151 *lcFltstr = lcConf_typeFilter
3152 *=== TechRec 1090249 01-Oct-2015 jisingh ===
3153 .oLog.LogEntry("Filter String Conf_type:" + lcConf_typeFilter)
3154 ENDIF
3155
3156 ENDIF
3157
3158 ENDWITH
3159
3160 IF NOT EMPTY(lcNsr_codeFilter) AND NOT EMPTY(lcConf_typeFilter)
3161 lcFltstr = lcNsr_codeFilter +" AND " + lcConf_typeFilter
3162 *--- Tr 1091168 30-Nov-2015 Dilip replaced "WORST_NSR" with "NSR_CODE"---
3163 *--- TechRec 1090249 29-Sep-2015 jisingh ---
3164 This.cNsrCodeFilter = " EXISTS(SELECT 1 FROM zzaavalr WHERE division = dv.division AND ord_num = dv.ord_num " + ;
3165 " AND line_seq = dv.line_seq AND " + STRTRAN(UPPER(lcNsr_codeFilter), "DV.NSR_CODE", "NSR_CODE") + ") "
3166 This.cCnfTypeFilter = lcConf_typeFilter
3167 *=== TechRec 1090249 29-Sep-2015 jisingh ===
3168 *=== Tr 1091168 30-Nov-2015 Dilip ===
3169 ENDIF
3170
3171 SELECT (lnSelect)
3172
3173 RETURN lcFltstr
3174 ENDFUNC
3175 *=== TechRec 1072901 29-Aug-2013 MANI. ===
3176
3177 * FH 1070774
3178 FUNCTION WriteToFileNow
3179 LPARAMETERS pcString, pnHandle, pcFile
3180 LOCAL llRetVal, lnSelect, lnWrite
3181
3182 llRetVal = true
3183 lnSelect = SELECT()
3184
3185
3186 WITH This
3187 *- 2 cases :
3188 *- If it's the first time coming into this method pnHandle would be > 0, we can just write to file right away (handle already opened)
3189 *- or
3190 *- We already wrote into the file because it was too big. (handle would still be > 0 because we reopen it after exiting the method)
3191
3192 IF pnHandle > 0
3193 .oLog.LogEntry("Writing string to handle")
3194 llRetVal=fseek(pnHandle, 0, 2) && move to eof
3195 lnWrite = fwrite(pnHandle, pcString)
3196
3197 IF lnWrite = 0
3198 &&error
3199 .oLog.LogEntry("Error ! Writing String to handle")
3200 llRetVal = .F.
3201 RETURN llRetVal
3202 ELSE
3203 llRetVal = .T.
3204 ENDIF
3205
3206 &&close file
3207 llRetVal = FCLOSE(pnHandle)
3208 IF llRetval
3209 pnHandle = 0
3210 ELSE
3211 .oLog.LogEntry("Error ! Closing handle")
3212 llRetVal = .F.
3213 RETURN llRetVal
3214 ENDIF
3215 ELSE && something is wrong we enter this method with 0 handle
3216 .oLog.LogEntry("Error ! Handle has not been initiated")
3217 llRetval = .F.
3218 RETURN llRetVal
3219 ENDIF
3220 &&Clear it out
3221 pcString = ""
3222 *- Reopen handle
3223 if llRetval AND pnHandle = 0
3224 pnHandle = fopen(pcFile,12) && If so, open read-write
3225 endif
3226
3227 ENDWITH
3228
3229 SELECT (lnSelect)
3230 RETURN llRetVal AND (pnHandle > 0)
3231 ENDFUNC
3232
3233*============================================================
3234*--- TechRec 1076524 10-Apr-2014 TSV---
3235 PROCEDURE GetAddressCursor
3236 LOCAL llRetVal, lnOldSelect
3237
3238 llRetVal = .t.
3239 lnOldSelect = SELECT()
3240
3241 WITH This
3242 .cQ_846Adr = Iif(Version(2)=2,"##","#") + SubStr(SYS(2015), 2)
3243
3244 lcSQL = " SELECT h.location, l.loc_name, l.address1, l.address2, l.city, " + ;
3245 " l.state, l.zipcode, l.country " + ;
3246 " INTO " + .cQ_846Adr + ;
3247 " FROM " + .cQ_846Hdr + " h " + ;
3248 " JOIN zzxlocar l " + ;
3249 " ON h.location = l.location " + ;
3250 " WHERE h.resv_loc_adr = 'Y' " + ;
3251 " AND l.loc_type = 'W' "
3252
3253 llRetVal = v_SQLExec(lcSQL)
3254
3255 ENDWITH
3256
3257 SELECT(lnOldSelect)
3258 RETURN llRetVal
3259 ENDPROC
3260*============================================================
3261*=== TechRec 1076524 10-Apr-2014 TSV===
3262
3263*--- TechRec 1087873 29-Jul-2015 jisingh ---
3264 FUNCTION BuildOTSString
3265 LPARAMETERS tcOTSQtyCal
3266 LOCAL llRetVal, lcRetVal, lnSelect, lcSQLString, ;
3267 lcOTSFields, lcLocAlias, lcOtsAlias
3268
3269 llRetVal = true
3270 lcRetVal = " CASE "
3271 lnSelect = SELECT()
3272
3273 WITH This
3274 lcLocAlias = GetUniqueFileName()
3275 lcOtsAlias = GetUniqueFileName()
3276
3277 lcSQLString = " SELECT DISTINCT location, LEFT(ots_fields, 254) AS ots_fields " + ;
3278 " FROM " + .cQ_846Hdr + " WHERE override_ots_qty = 'Y' "
3279
3280 llRetVal = llRetVal AND v_SQLExec(lcSQLString, lcLocAlias)
3281
3282 lcSQLString = "SELECT DISTINCT ots_fields FROM " + lcLocAlias
3283
3284 llRetVal = llRetVal AND v_SQLExec(lcSQLString, lcOtsAlias,, true) && Local SQL
3285 llRetVal = llRetVal AND USED(lcLocAlias) AND USED(lcOtsAlias)
3286 llRetVal = llRetVal AND RECCOUNT(lcLocAlias) > 0 AND RECCOUNT(lcOtsAlias) > 0
3287
3288 IF llRetVal
3289 SELECT (lcOtsAlias)
3290 SCAN FOR !EMPTY(ots_fields)
3291 lcOTSFields = UPPER(ots_fields)
3292
3293 lcRetVal = lcRetVal + ;
3294 " WHEN EXISTS(SELECT 1 FROM " + .cQ_846Hdr + ;
3295 " WHERE LEFT(ots_fields, 254) = " + SQLFormatChar(lcOTSFields) + ;
3296 " AND location = h.location) " + ;
3297 " THEN " + .ResolveOTSValues(lcOTSFields)
3298
3299 SELECT (lcOtsAlias)
3300 ENDSCAN
3301 lcRetVal = lcRetVal + IIF(lcRetVal = " CASE ", " WHEN (1 = 2) THEN 0 ", "")
3302 lcRetVal = lcRetVal + " ELSE " + tcOTSQtyCal + " END "
3303 ELSE
3304 lcRetVal = tcOTSQtyCal
3305 ENDIF
3306 .TableClose(lcLocAlias)
3307 .TableClose(lcOtsAlias)
3308 ENDWITH
3309
3310 SELECT (lnSelect)
3311 RETURN lcRetVal
3312 ENDFUNC
3313
3314*============================================================
3315
3316 FUNCTION ResolveOTSValues
3317 LPARAMETERS tcString
3318 LOCAL lcRetVal, lnSelect, lnX
3319 DIMENSION laString[1]
3320
3321 lcRetVal = "(0 "
3322 lnSelect = SELECT()
3323
3324 WITH This
3325 =StringToArray(tcString, @laString)
3326 FOR lnX = 1 TO ALEN(laString)
3327 DO CASE
3328 CASE "_PLUS" $ laString[lnX]
3329 lcRetVal = lcRetVal + "+" + STRTRAN(laString[lnX], "_PLUS")
3330
3331 CASE "_MINUS" $ laString[lnX]
3332 IF "SFUUD" $ laString[lnX] OR "SFURMMRP" $ laString[lnX]
3333 lcRetVal = lcRetVal + "-COALESCE(" + STRTRAN(laString[lnX], "_MINUS") + ",0)"
3334 ELSE
3335 lcRetVal = lcRetVal + "-" + STRTRAN(laString[lnX], "_MINUS")
3336 ENDIF
3337 ENDCASE
3338 ENDFOR
3339 lcRetVal = lcRetVal + ")"
3340 ENDWITH
3341
3342 SELECT (lnSelect)
3343 RETURN lcRetVal
3344 ENDFUNC
3345*=== TechRec 1087873 29-Jul-2015 jisingh ===
3346ENDDEFINE