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