· 7 years ago · Sep 25, 2018, 04:58 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* Changes....: This Process outputs
11* 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
12* 2. In Detail - It is fetching the Locations wise SKUs ( UPC shuld exist in ZVEUPNR)
13* 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
14*======================================================================
15
16#INCLUDE SYSTEM.h
17#INCLUDE EDI.h
18#DEFINE EDI_OIB_FILENAME "846.dat"
19
20#DEFINE EDI_OIB_TRANSACTION "OIB"
21
22
23DEFINE CLASS BPOOIBProcess AS BPOEDIBase
24
25 NAME = "BPOOIBProcess"
26 cTitle = "846(o) Process"
27 lSuppressWarning = True && To suppress error log warning msg UI
28 cSQLFilterString = ""
29
30 cJobID = "OIBProcess"
31
32 lUserInterface = False
33 Tie_Setup = False
34
35 cInterFaceHeader = "ZZEOIBTH"
36 cInterFaceDetail = "ZZEOIBTD"
37 cControlReference = "ZZEOIBCR"
38
39 cQ_846Dtl = ""
40 cQ_846Hdr = ""
41
42 oSB = NULL
43
44 nTotalLoc = 0
45 nDtlExport = 0
46
47 *--- TR 1058987 7-Mar-2012 Goutam
48 nFailedValidation = 0
49 *=== TR 1058987 7-Mar-2012 Goutam
50
51 *--- TechRec 1059983 31-May-2012 MANI. ---
52 cAvail_Option = ""
53 nPercent_Avail = 100
54 cShowZeroQty = "" &&--- TechRec 1062747 18-Jul-2012 MANI. ===
55 nMinSzQtyAvl = "" &&--- TechRec 1062747 18-Jul-2012 MANI. ===
56
57 *=== TechRec 1059983 31-May-2012 MANI. ===
58
59 *--- TR 1064279 7-Nov-2012 Goutam.
60 l846_Use_Priority100_OTS = false
61 *=== TR 1064279 7-Nov-2012 Goutam.
62
63 *--- TechRec 1076524 27-Apr-2014 TSV---
64 cQ_846Adr = ""
65 *=== TechRec 1076524 27-Apr-2014 TSV===
66
67 l846_OTS_QTY_FROM_AVAILABILITY_BY_STYLE = false && TR 1067015 11-Mar-2013 Partha
68
69*=====================================================
70 FUNCTION INIT
71 LPARAMETERS plUserInterface
72
73 LOCAL llRetVal,lcSQLString
74
75 llRetVal = DODEFAULT()
76
77 THIS.lUserInterface = plUserInterface
78
79 * --- TR 1035726 9/12/08 CM
80 *THIS.lScheduled = !plUserInterface
81 * === TR 1035726 9/12/08 CM
82
83 THIS.Tie_Setup = IIF(goEnv.sv("TIE_SETUP","N") = 'N' OR GoEnv.SV("BC_EDI_VERSION","") = "5.2", .F., .T.)
84
85 THIS.oSB = NEWOBJECT("SQLStringBuilder", "clsgnstr.prg")
86 llRetVal = llRetVal AND IsObject(THIS.oSB, True)
87
88 *--- TR 1064279 7-Nov-2012 Goutam.
89 IF llRetVal
90 This.l846_Use_Priority100_OTS = goEnv.sv("846_USE_PRIORITY100_OTS","N") = 'Y'
91 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
92 ENDIF
93 *=== TR 1064279 7-Nov-2012 Goutam.
94
95 RETURN llRetVal
96 ENDFUNC
97*=====================================================
98 FUNCTION DESTROY
99
100 WITH THIS
101 IF IsObject(.oSB, True)
102 .oSB.DESTROY()
103 .oSB = NULL
104 ENDIF
105
106 DODEFAULT()
107 ENDWITH
108 ENDFUNC
109*=====================================================
110 FUNCTION OIBProcess
111 LPARAMETER pcSQLFilterString, plScheduled
112
113 LOCAL llRetVal, lnSelect, llQueryBOL
114
115 llRetVal = True
116 lnSelect = SELECT()
117
118 *-- Path to SysLock directory on server (same place as Login.DBF)
119 *-- Suppress syslock processing if coming from carton weight entry
120 lcSyslockTablePath = ADDBS(goEnv.envLoginTablePath.VALUE)
121
122 IF NOT v_SysLock( lcSyslockTablePath+"SYSLOCK", THIS.cJobID, goEnv.cCompany)
123 RETURN False
124 ENDIF
125
126 WITH THIS
127 .lNoDataFound = False
128 .cSQLFilterString = pcSQLFilterString
129
130 * --- TR 1035726 9/12/08 CM
131 *.lScheduled = plScheduled
132 * === TR 1035726 9/12/08 CM
133
134 .oLog.OpenLog(.cJobID, I(.cJobID), .lScheduled)
135 .oLog.LogProgram("clsoibpr.prg")
136 .oLog.LogEntry("Filter Criteria: " + .cSQLFilterString)
137
138 .CreateFormProgressBar(.lScheduled)
139 .SetCaption(.cTitle)
140
141 lnThermoTotal = 5
142 .InitProgressBarTotal(lnThermoTotal, "Total " + .cTitle)
143
144 *--- TechRec 1059983 31-May-2012 MANI. ---
145 llRetVal = llRetVal AND .ValidateParamBro()
146 *=== TechRec 1059983 31-May-2012 MANI. ===
147
148 IF llRetVal
149
150 .DropTempTables()
151
152 .InitThermo(.1)
153 .UpdateThermoCaption("Selecting Location(s) to export...")
154 INKEY(0.2)
155
156 .oLog.LogEntry("Selecting Location(s) to export...") && 1066673
157 llQuerySKU = .FetchLocationData()
158 .oLog.LogEntry("Selecting Location(s) to export...completed") && 1066673
159
160 .AdvanceThermo(1)
161 .AdvanceThermoTotal(1)
162 INKEY(0.2)
163
164 IF NOT llQuerySKU OR .nTotalLoc = 0
165 .cMessage = 'No records to process.'
166 .lNoDataFound = True
167 .m_close()
168 .oLog.LogEntry(.cMessage)
169 ELSE
170 .InitThermo(.nDtlExport)
171 INKEY(0.2)
172
173 .oLog.LogMajorStage("Processing SKU(s)")
174 .oLog.LogEntry("Total records to export: " + ALLTRIM(STR(.nDtlExport)),.T.)
175
176 .AdvanceThermoTotal(1)
177 INKEY(0.2)
178 .oLog.LogResult(llRetVal, "Processing SKU(s)")
179
180 llRetVal= llRetVal AND .DeletePreviousTransaction()
181 .AdvanceThermoTotal(1)
182
183 * Populate Data to Transaction Tables
184 llRetVal = llRetVal AND .PopulateTransaction()
185
186 *--- TechRec 1076524 27-Apr-2014 TSV---
187 llRetVal = llRetVal AND .GetAddressCursor()
188 *=== TechRec 1076524 27-Apr-2014 TSV===
189
190 *--- TechRec 1060583 19-Apr-2012 jisingh ---
191 IF .nTotalLoc = 0
192 .cMessage = "No records to process."
193 .lNoDataFound = true
194 .oLog.LogEntry(.cMessage)
195 ELSE
196 *=== TechRec 1060583 19-Apr-2012 jisingh ===
197
198 llRetVal= llRetVal AND .ValidateTransaction()
199
200 .AdvanceThermoTotal(1)
201
202 .AdvanceThermoTotalWithCaptionPlus("Creating flatfile...")
203 lcEDIPath = THIS.GetEDIFlatFileDirectory("Outbound")
204 lcOutBoundFlatFile = lcEDIPath + EDI_OIB_FILENAME
205
206 &&--- TechRec 1076524 27-Apr-2014 TSV added .cQ_846Adr ===
207 llRetVal= llRetVal AND .UnLoadInterfaceToFlatFile(EDI_OIB_TRANSACTION, lcOutBoundFlatFile, ;
208 .cInterFaceHeader, .cInterFaceDetail, .cQ_846Adr)
209
210 .AdvanceThermoTotal(1)
211
212 IF llRetVal
213 *--- TechRec 1061287 02-May-2012 jisingh Added AND (.nTotalLoc - .nFailedValidation) > 0 ===
214 IF NOT .Tie_Setup AND (.nTotalLoc - .nFailedValidation) > 0
215 lcTempFlatfile = STRTRAN(UPPER(lcOutBoundFlatFile), ".DAT", ".TMP")
216 *--- TechRec 1060583 19-Apr-2012 jisingh ---
217 *llRetVal = .CopyToFlatFileInBlock(lcTempFlatfile, lcOutBoundFlatFile)
218 INSERT INTO __tcFlatFileList(TempFlatFile,OutBoundFlatFile) VALUES (lcTempFlatfile, lcOutBoundFlatFile)
219 *=== TechRec 1060583 19-Apr-2012 jisingh ===
220 ENDIF
221
222 .AdvanceThermoTotal(1)
223
224 llRetVal= llRetVal AND .UpdateMessage()
225
226 ENDIF
227
228 IF NOT llRetVal
229 .oLog.LogWarning("Flat file to export is incomplete. " + CRLF + ;
230 "Please examine the file " + lcOutBoundFlatFile + " before proceeding further" + CRLF + ;
231 "Then please make sure that you have enough disk space and " + CRLF + ;
232 "Your network connection is fine, then unflag all orders for this batch " + CRLF + ;
233 "and reprocess them." + CRLF)
234 ENDIF
235
236 *--- TechRec 1060583 19-Apr-2012 jisingh ---
237 llRetVal= llRetVal AND .UpdateCustomerPriceFlag()
238
239 *- ADDED here to prevent flat file from being generated should TableUpdateWithTransaction fail
240 *--- TechRec 1061287 02-May-2012 jisingh Added AND (.nTotalLoc - .nFailedValidation) > 0 ===
241 IF llRetVal AND (.nTotalLoc - .nFailedValidation) > 0
242 SELECT __tcFlatFileList
243 SCAN
244 lcTempFlatfile = __tcFlatFileList.TempFlatFile
245 lcOutBoundFlatFile = __tcFlatFileList.OutBoundFlatFile
246 llRetVal = llRetVal AND .CopyToFlatFileInBlock(lcTempFlatfile, lcOutBoundFlatFile)
247 ENDSCAN
248 ENDIF
249 ENDIF
250 *=== TechRec 1060583 19-Apr-2012 jisingh ===
251
252 .m_close()
253 ENDIF
254
255 ENDIF
256
257 .DropTempTables()
258
259 IF IsObject(.oFrmProgressBar) AND NOT ISNULL(.oFrmProgressBar)
260 .m_close()
261 ENDIF
262
263 *-- Close Log
264 .oLog.LogResult(llRetVal)
265 .oLog.CloseLog()
266 ENDWITH
267
268 v_SysUnLock( lcSyslockTablePath+"SYSLOCK", THIS.cJobID, goEnv.cCompany)
269
270 SELECT (lnSelect)
271 RETURN llRetVal
272 ENDFUNC
273*=====================================================
274
275 FUNCTION FetchLocationData
276
277 LOCAL llRetVal, lnSelect, lcSQLFilterSting, lcSQLString, lcTempCursor, lnRecs ,lcPOSQLMainString , lcPOSubString
278 LOCAL lcTmpDtl,lcTmpHdr, lcQtyField , lcZzbaljoin ,lcSizeWhr ,lcsizeqty && TR 1050639 28-Jan-2011 Partha
279 &&--- TechRec 1059983 06-Jun-2012 MANI. Added lcQtyField,lcZzbaljoin,lcSizeWhr,lcsizeqty ===
280
281 *--- TR 1058987 28-Feb-2012 Goutam.
282 LOCAL lcCatgSubJoin,lcAvail_Qty1
283 &&--- TechRec 1062747 18-Jul-2012 MANI. Added lcAvail_Qty1 ===
284 *=== TR 1058987 28-Feb-2012 Goutam.
285
286 *--- TR 1061613 20-Jun-2012 Partha ---
287 LOCAL lcOptPercentage,lcPercentage,lcOptMinMult,lcRoundUpMinMult,lcRoundDownMinMult
288 STORE "" TO lcOptPercentage,lcPercentage,lcOptMinMult,lcRoundUpMinMult,lcRoundDownMinMult
289 *=== TR 1061613 20-Jun-2012 Partha ===
290
291 *--- TR 1063182 03-Aug-2012 Partha ---
292 LOCAL lcNearestMult
293 lcNearestMult = ""
294 *=== TR 1063182 03-Aug-2012 Partha ===
295
296 *--- TR 1064279 7-Nov-2012 Goutam.
297 LOCAL lcOrderSubSQL, lcOTSQtyCal
298 STORE "" TO lcOrderSubSQL, lcOTSQtyCal
299 *=== TR 1064279 7-Nov-2012 Goutam.
300
301 *--- TechRec 1059983 06-Jun-2012 MANI. ---
302 STORE "" TO lcQtyField,lcZzbaljoin, lcSizeWhr,lcsizeqty, lcAvail_Qty1
303 &&--- TechRec 1062747 18-Jul-2012 MANI. Added lcAvail_Qty1 ===
304 *=== TechRec 1059983 06-Jun-20DO 12 MANI. ===
305
306 llRetVal = True
307 lnSelect = SELECT()
308 lcSQLFilterString = IIF(EMPTY(THIS.cSQLFilterString), " 1=1", THIS.cSQLFilterString)
309 lcTempCursor = GetUniqueFileName()
310 lnRecs = 0
311
312
313 WITH THIS
314 .oLog.LogMajorStage("Retrieving Location Records")
315
316 *--- TR 1034990 NSD 8/7/08 Added filter for location > '' from summary.
317 *--- TR 1040516 30-Jul-2009 Surinder Singh : Added c1.Supplier_num ===
318
319 *--- TR 1050639 28-Jan-2011 Partha ---
320
321 *--- TR 1058987 28-Feb-2012 Goutam. Added pkey as CtrlPkey, Customer, catg_dest and template in select list
322 *--- TR 1058987 28-Feb-2012 Goutam. Removed ZVXSSUMHVSZLL_call and replaced with zzxssumh and Added Order by
323 *--- TechRec 1060583 19-Apr-2012 jisingh Added use_threshold, threshold_qty & res_cprice ===
324 *--- TR 1061166 05-28-2012 RKI ---*
325
326 *--- TechRec 1066673 17-Jul-2013 GSternik ---
327 *-- SQLTableFromQuery cannot be used due to subselect. let's do the explicit INTO (no DB2 support anyway)
328
329 *-- All previous versions deleted... see VSS
330*!* lcSQLString = " SELECT " + ;
331*!* " h.location " + ;
332*!* " ,CASE WHEN EXISTS(Select 1 from zzxwhsgd where c1.location = whs_grp) " + ;
333*!* " THEN COALESCE(c1.location, ' ' ) ELSE '' END as locgroup " + ;
334*!* " ,coalesce( c1.vnd_id, '') as vnd_id " + ;
335*!* " ,coalesce( c1.vnd_qual, '') as vnd_qual " + ;
336*!* " ,coalesce( c1.our_qual, '') as our_qual " + ;
337*!* " ,coalesce( c1.our_id, '') as our_id " + ;
338*!* " ,coalesce( c1.active_ok,'') as active_ok " + ;
339*!* " ,coalesce( c1.avl_only, '') as avl_only " + ;
340*!* " ,convert(varchar,getdate(),101) as report_date " + ;
341*!* " ,convert(Varchar,replace(convert(Varchar, getdate(),108), ':','')) as report_time " + ;
342*!* " ,coalesce( c1.Supplier_num,'') as Supplier_num "+;
343*!* " ,coalesce( c1.pkey, '') as CtrlPkey "+;
344*!* " ,coalesce( c1.Customer,'') as Customer "+;
345*!* " ,coalesce( c1.catg_dest,'') as catg_dest "+;
346*!* " ,coalesce( c1.Template,'') as Template "+;
347*!* " ,coalesce( c1.use_threshold,'') as use_threshold " + ;
348*!* " ,coalesce( c1.threshold_qty, 0) as threshold_qty " + ;
349*!* " ,coalesce( c1.res_cprice, '') as res_cprice " + ;
350*!* " ,coalesce( c1.percentage, 0) as percentage " + ; && TR 1061613 15-Jun-2012 Partha
351*!* " ,coalesce( c1.round_to, '') as round_to " + ; && TR 1061613 15-Jun-2012 Partha
352*!* " ,coalesce( c1.min_mult, '') as min_mult " + ; && TR 1061613 15-Jun-2012 Partha
353*!* " ,coalesce( c1.use_excl, '') as use_excl " + ; && TR 1064279 9-Nov-2012 Goutam
354*!* " ,coalesce( c1.add_bulk, '') as add_bulk " + ; && TR 1072524 7-Aug-2013 Goutam
355*!* " FROM ( select distinct location from zzxssumh) H " + ;
356*!* " LEFT JOIN ZZEOIBCR c1 " + ;
357*!* " ON C1.active_ok = 'Y' " + ;
358*!* " AND (EXISTS " + ;
359*!* " ( SELECT DISTINCT ld.location " + ;
360*!* " FROM zzxwhsgh lh " + ;
361*!* " JOIN zzxwhsgd ld " + ;
362*!* " ON lh.pkey = ld.fkey " + ;
363*!* " AND lh.WHS_GRP = c1.location " + ;
364*!* " AND ld.location = H.location ) " + ;
365*!* " OR c1.location = h.LOCATION or c1.LOCATION = '') "+ ;
366*!* " WHERE (c1.location is not null) " + ; &&--- TechRec 1059983 14-Jun-2012 MANI. Removed existing and added c1.location is not null ===
367*!* " order by coalesce( c1.location, h.location ) " + ;
368*!* " , c1.avl_only ,c1.catg_dest , c1.Customer "
369 *=== TR 1061166 05-28-2012 RKI ===*
370
371*!* " order by coalesce( c1.location, h.location ) " + ;
372*!* " , c1.avl_only ,c1.catg_dest , c1.Customer "
373 *=== TR 1061166 05-28-2012 RKI ===*
374*!* *=== TR 1050639 28-Jan-2011 Partha ===
375*!* *--- TR 1061166 05-22-2012 RKI ---*
376*!* lcCursor = GetUniqueFileName()
377*!* llRetVal = llRetVal AND .vSQLExec(lcSQLString , lcCursor)
378*!* llRetVal = .GenerateSQLTempTable(lcCursor) AND .PopulateSQLTempTable(lcCursor)
379*!* .cQ_846Hdr = .cSQLTempTable
380*!* *!* .cQ_846Hdr = SQLTableFromQuery(lcSqlString)
381*!* *!* llRetVal = llRetVal AND !EMPTY(.cQ_846Hdr)
382*!* *=== TR 1061166 05-22-2012 RKI ===*
383
384 *-- Let's create the global temp table in Dev mode, so we can check it in the query analyzer:
385 .cQ_846Hdr = Iif(Version(2)=2,"##","#") + SubStr(SYS(2015), 2)
386
387 *----------------------------------------------------------
388 * !!!
389 * TO DO: Parse the filter and apply LOCATION, CUSTOMER, and CATG_DEST filters (with alias "H.") to this query (to ZZEOIBCR table, C1 now)
390 * Figure out what to do about LOCGROUP filter
391 * filter sample ((U.DIVISION = '01')) AND ((H.CUSTOMER <>'560493')
392 * AND (H.CUSTOMER <>'560275') AND (H.CUSTOMER <>'393739')
393 * AND (H.CUSTOMER <>'560557') AND (H.CUSTOMER <>'560558'))
394 * !!!
395 *----------------------------------------------------------
396
397 lcSQLString =;
398 "select h.Location"+;
399 " , case when exists( "+;
400 "select *"+;
401 " from zzxwhsgd"+;
402 " where c1.Location = whs_grp) then c1.Location"+;
403 " else '' end as LocGroup"+;
404 " , c1.Vnd_Id"+;
405 " , c1.Vnd_Qual"+;
406 " , c1.Our_Qual"+;
407 " , c1.Our_Id"+;
408 " , c1.Active_Ok"+;
409 " , c1.Avl_Only"+;
410 " , convert(varchar(10), getdate(), 101) as report_date"+;
411 " , convert(Varchar, replace(convert(Varchar(8), getdate(), 108), ':', '')) as report_time"+;
412 " , c1.Supplier_Num"+;
413 " , c1.pkey as CtrlPkey"+;
414 " , c1.Customer"+;
415 " , c1.Catg_Dest"+;
416 " , c1.Template"+;
417 " , c1.Use_Threshold"+;
418 " , c1.Threshold_Qty"+;
419 " , c1.Res_Cprice"+;
420 " , c1.Percentage"+;
421 " , c1.Round_To"+;
422 " , c1.Min_Mult"+;
423 " , c1.Use_Excl"+;
424 " , c1.resv_loc_adr" + ; &&--- TechRec 1076524 10-Apr-2014 TSV added resv_loc_adr ===
425 " into " + .cQ_846Hdr +;
426 " from ( "+;
427 "select distinct Location"+;
428 " from zzxssumh with (NoLock)) H"+;
429 " join ZZEOIBCR c1 with (NoLock)"+;
430 " on c1.Active_Ok = 'Y'"+;
431 " and (c1.Location = h.Location or c1.Location = '' or exists ( "+;
432 "select ld.Location"+;
433 " from zzxwhsgh lh"+;
434 " join zzxwhsgd ld"+;
435 " on lh.pkey = ld.fkey"+;
436 " and lh.WHS_GRP = c1.Location"+;
437 " and ld.Location = H.Location ))"+;
438 " order by c1.Location, c1.avl_only, c1.catg_dest, c1.Customer"
439
440 llRetVal = llRetVal and v_SqlExec(lcSqlString)
441 *=== TechRec 1066673 17-Jul-2013 GSternik ===
442
443 llRetVal = llRetVal AND V_SQLEXEC( " Select count(*) cnt from " + .cQ_846Hdr , lcTempCursor )
444
445 llRetval = llRetVal AND USED(lcTempCursor)
446
447 IF llRetVal
448
449 SELECT(lcTempCursor)
450 .nTotalLoc = cnt
451 ENDIF
452
453 llRetVal = llRetVal AND .nTotalLoc > 0
454
455 IF NOT llRetVal
456 .oLog.LogEntry("No records to process.")
457 ELSE
458 .AdvanceThermo(1)
459 .AdvanceThermoTotal(1)
460
461 .InitThermo(.nDtlExport)
462
463
464 *--- TR 1034990 NSD 8/7/08
465 * 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.
466 * 2- Due Dates will be calculated using new views zvcordrd_wip1v_shipto, zvcordrd_wip2v_shipto, zvcordrd_wip3v_shipto and zvcordrd_wip4v_shipto.
467 * Removing PO Substring code
468
469 .UpdateThermoCaption("Selecting Locationwise SKU(s) to export...")
470 .oLog.LogEntry("Selecting Locationwise SKU(s) to export...") && 1066673
471 *--- TR 1058987 28-Feb-2012 Goutam.
472 lcCatgSubJoin = " and (sc.catg_dest1 = case when h.catg_dest > '' then h.catg_dest else sc.catg_dest1 end or " + ;
473 " sc.catg_dest2 = case when h.catg_dest > '' then h.catg_dest else sc.catg_dest2 end or " + ;
474 " sc.catg_dest3 = case when h.catg_dest > '' then h.catg_dest else sc.catg_dest3 end or " + ;
475 " sc.catg_dest4 = case when h.catg_dest > '' then h.catg_dest else sc.catg_dest4 end or " + ;
476 " sc.catg_dest5 = case when h.catg_dest > '' then h.catg_dest else sc.catg_dest5 end or " + ;
477 " sc.catg_dest6 = case when h.catg_dest > '' then h.catg_dest else sc.catg_dest6 end) "
478 *=== TR 1058987 28-Feb-2012 Goutam.
479 *--- TechRec 1059983 05-Jun-2012 MANI. ---
480 DO CASE
481 CASE .cAvail_Option = "Q"
482 *-- option QOH
483 lcQtyField = " (SFUR1_V + SFUR2_V + SFURT_V + SFUSA_V - SFUIN_V-SFUCM_V) "
484
485 CASE .cAvail_Option = "A"
486 *-- Option Availability
487
488 *--- TechRec 1066673 16-Aug-2013 GSternik ---
489 *lcQtyField = " (bal.Onhand_qty)"
490 lcQtyField = " IsNull(bal.Onhand_qty, 0)"
491 *=== TechRec 1066673 16-Aug-2013 GSternik ===
492
493 *--- TechRec 1072177 04-Aug-2013 vkrishnamurthy ---
494*!* lcsizeqty = " SUM(Size01_Qty*Sz01+Size02_Qty*Sz02+Size03_Qty*Sz03+Size04_Qty*Sz04 + "+;
495*!* " Size05_Qty*Sz05+Size06_Qty*Sz06+Size07_Qty*Sz07+Size08_Qty*Sz08 +"+;
496*!* " Size09_Qty*Sz09+Size10_Qty*Sz10+Size11_Qty*Sz11+Size12_Qty*Sz12 + "+;
497*!* " Size13_Qty*Sz13+Size14_Qty*Sz14+Size15_Qty*Sz15+Size16_Qty*Sz16 + "+;
498*!* " Size17_Qty*Sz17+Size18_Qty*Sz18+Size19_Qty*Sz19+Size20_Qty*Sz20 +" + ;
499*!* " Size21_Qty*Sz21+Size22_Qty*Sz22+Size23_Qty*Sz23+Size24_Qty*Sz24) as Onhand_qty"
500
501*!* lcSizeWhr = " Size01_Qty*Sz01+Size02_Qty*Sz02+Size03_Qty*Sz03+Size04_Qty*Sz04 + "+;
502*!* " Size05_Qty*Sz05+Size06_Qty*Sz06+Size07_Qty*Sz07+Size08_Qty*Sz08 + "+;
503*!* " Size09_Qty*Sz09+Size10_Qty*Sz10+Size11_Qty*Sz11+Size12_Qty*Sz12 + "+;
504*!* " Size13_Qty*Sz13+Size14_Qty*Sz14+Size15_Qty*Sz15+Size16_Qty*Sz16 + "+;
505*!* " Size17_Qty*Sz17+Size18_Qty*Sz18+Size19_Qty*Sz19+Size20_Qty*Sz20 + " + ;
506*!* " Size21_Qty*Sz21+Size22_Qty*Sz22+Size23_Qty*Sz23+Size24_Qty*Sz24 > 0 "
507
508 lcstr = ''
509 FOR l_nxx = 1 TO goEnv.MaxBuckets
510 l_cBucket = TRANSFORM(l_nxx, "@L 99")
511 lcstr = lcstr + IIF(!EMPTY(lcStr)," +","")+ " Size"+l_cBucket + "_Qty *Sz"+l_cBucket
512 ENDFOR
513 lcsizeqty = " SUM("+ lcstr + ") as Onhand_qty"
514
515 lcSizeWhr = lcstr + " > 0 "
516
517 *=== TechRec 1072177 04-Aug-2013 vkrishnamurthy ===
518
519 *--- TechRec 1071370 31-May-2013 MANI. Added left keyword ===
520 lcZzbalJoin = " LEFT JOIN ( SELECT b.division, b.style,b.lbl_code, b.color_code, b.dimension, b.location, size_num as Size_bk , " + ;
521 lcsizeqty +;
522 " FROM zzaalbal b " +;
523 " CROSS JOIN zzxBuckt x " + ;
524 " WHERE REC_TYPE = 'I' AND " + lcSizeWhr + ;
525 " GROUP BY b.division, b.style,b.lbl_code, b.color_code, b.dimension, b.location, size_num ) bal " + ;
526 " ON bal.division = Szy.Division " + ;
527 " AND bal.Style = Szy.Style " + ;
528 " AND bal.Color_code = Szy.Color_Code " + ;
529 " AND bal.lbl_Code = Szy.lbl_Code " + ;
530 " AND bal.Dimension = Szy.Dimension " + ;
531 " AND bal.Size_bk = Szy.Size_bk " + ;
532 " AND bal.location = Szy.location "
533
534 OTHERWISE
535 *-- Existing Functionality
536 lcQtyField = " (SFUSA_V+SFURT_V+SFUR1_V+SFUR2_V-SFUIN_V-SFUPK_V-SFUCM_V-SFURS_V "+;
537 " -SFUOP_V-SFUDM_V-{FN IFNULL(({FN IFNULL(ZVXMRPUDV.SFUUD_V, 0)}), 0)} "+;
538 " ) "
539
540 ENDCASE
541 *=== TechRec 1059983 05-Jun-2012 MANI. ===
542
543 *lcPOSubString = " SELECT d.division, d.style, d.color_code, d.lbl_code, d.dimension, h.shipto,h.due_date " +;
544 " FROM zzcordrh h join zzcordrd d on h.pkey = d.fkey " + ;
545 " WHERE d.last_stage <> 'Y' " + ;
546 " group by d.division, d.style, d.color_code, d.lbl_code, d.dimension,h.shipto,h.due_Date "
547
548 *lcSQLMainString = " SELECT division, style, color_code,lbl_code,dimension,shipto, " + ;
549 " MAX(case when cnt =1 then due_date else '' end) as prod_date1 , " + ;
550 " MAX(case when cnt =2 then due_date else '' end) as prod_date2 , " + ;
551 " MAX(case when cnt =3 then due_date else '' end) as prod_date3 , " + ;
552 " MAX(case when cnt =4 then due_date else '' end) as prod_date4 " +;
553 " FROM ( Select division, style, color_code, lbl_code, dimension, shipto,due_date,"+;
554 " (select count(*) cnt FROM ("+ lcPOSubString+ ") PO"+;
555 " WHERE po.division = PO.division " +;
556 " AND po.style = po1.style "+;
557 " AND po.color_code = po1.color_code "+;
558 " AND po.lbl_code = po1.lbl_code "+;
559 " AND po.dimension = po1.dimension "+;
560 " AND po.shipto = po1.shipto " +;
561 " AND po.due_date >= po1.due_Date) "+;
562 " as cnt from ( " + lcPOSubString+ ") PO1"+;
563 + " ) A group by division, style, color_code,lbl_code,dimension ,shipto "
564
565 *--- TR 1040516 30-Jul-2009 Surinder Singh ---
566 * Added OTS_QTY field below in select and new join conditions from
567 * LEFT OUTER JOIN ( select division,style............
568
569 *--- TR 1058987 6-Mar-2012 Goutam. Added h.CtrlPkey in the select list
570 *--- TR 1058987 6-Mar-2012 Goutam. Added lcCatgSubJoin with zzxscolr join
571 *--- TR 1058987 6-Mar-2012 Goutam. Added cust_sku and aux_sku and join table zzxcstdr dr with sku and size bucket.
572 *--- TechRec 1059983 05-Jun-2012 MANI. Added lcQtyField ,lcZzbalJoin and removed the folloing lines in sql ===
573 *-- TR 1050639 28-Jan-2011 Partha
574
575 *--- TechRec 1059983 12-Jun-2012 MANI. ---
576
577 *--- TR 1061613 15-Jun-2012 Partha ---
578*!* lcavai_Qty = lcQtyField + " * " + ALLTRIM(STR(.nPercent_Avail)) + " / 100 - " + ;
579*!* lcQtyField + " * " + ALLTRIM(STR(.nPercent_Avail)) + " / 100 % sc.min_multiple "
580
581 *!* example 1 :-
582 *!* qty = 15 , % = 90 , mult = 6 , round = Y
583 *!* lcOptPercentage = 90/100 = .9
584 *!* lcPercentage = 15 * .9 = 13.5
585 *!* lcOptMinMult = 13.5 % 6 = 1.5
586 *!* lcRoundUpMinMult= 6 - 1.5 = 4.5
587 *!* lcRoundDownMinMult= 0 - 1.5 = -1.5
588 *!* lcavai_Qty = 13.5 + 4.5 = 18
589 *!* exceed chk ->lcavai_Qty = 13.5 + (-1.5) = 12
590
591 *!* example 2 :-
592 *!* qty = 15 , % = 90 , mult = 6 , round = D
593 *!* lcOptPercentage = 90/100 = .9
594 *!* lcPercentage = 15 * .9 = 13.5
595 *!* lcOptMinMult = 13.5 % 6 = 1.5
596 *!* lcRoundUpMinMult= 6 - 1.5 = 4.5
597 *!* lcRoundDownMinMult= 0 - 1.5 = -1.5
598 *!* lcavai_Qty = 13.5 + (-1.5) = 12
599 *!* exceed chk ->lcavai_Qty = 13.5 + (-1.5) = 12
600
601 *!* example 3 :-
602 *!* qty = -15 , % = 90 , mult = 6 , round = Y
603 *!* lcOptPercentage = 90/100 = .9
604 *!* lcPercentage = -15 * .9 = -13.5
605 *!* lcOptMinMult = -13.5 % 6 = -1.5
606 *!* lcRoundUpMinMult= (6 * -1) - (-1.5) = -6 + 1.5 = -4.5
607 *!* lcRoundDownMinMult= 0 - (-1.5) = 1.5
608 *!* lcavai_Qty = -13.5 + (-4.5) = -18
609 *!* exceed chk ->lcavai_Qty = -18
610
611 *!* example 4 :-
612 *!* qty = -15 , % = 90 , mult = 6 , round = D
613 *!* lcOptPercentage = 90/100 = .9
614 *!* lcPercentage = -15 * .9 = -13.5
615 *!* lcOptMinMult = -13.5 % 6 = -1.5
616 *!* lcRoundUpMinMult= (6 * -1) - (-1.5) = -6 * 1.5 = -9
617 *!* lcRoundDownMinMult= 0 - (- 1.5) = 1.5
618 *!* lcavai_Qty = -13.5 + 1.5 = -12
619 *!* exceed chk ->lcavai_Qty = -12
620
621 lcOptPercentage = " ( CASE WHEN h.percentage = 0 THEN " + ALLTRIM(STR(.nPercent_Avail)) + " ELSE h.percentage END /100 ) "
622 lcPercentage = " ( CASE WHEN " + lcQtyField + " > 0 THEN (" + lcQtyField + " * " + lcOptPercentage + " ) ELSE " + lcQtyField + " END )" && TR 1063182 08-Aug-2012 Partha
623 lcOptMinMult = " ( " + lcPercentage + " % CASE WHEN h.min_mult = 'Y' THEN sc.min_multiple ELSE 1 END ) "
624 lcRoundUpMinMult = " ( CASE WHEN " + lcOptMinMult + " <>0 THEN ( CASE WHEN (" + lcQtyField + ") >0 THEN sc.min_multiple ELSE (sc.min_multiple * -1) END) - " + lcOptMinMult + " ELSE 0 END ) "
625 lcRoundDownMinMult = " ( 0 - " + lcOptMinMult + " ) "
626 lcavai_Qty = " ( " + lcPercentage + " + " + " CASE WHEN h.round_to = 'Y' THEN " + lcRoundUpMinMult + " ELSE " + lcRoundDownMinMult + " END ) "
627
628 *--- Partha 24JULY2012 - Following will Round down if rounded up qty exceeds actual. as guided by Bea and Fatima.
629*!* lcavai_Qty = " CASE WHEN h.round_to = 'Y' AND ( " + lcavai_Qty + ") > ( " + lcQtyField + " ) THEN " + ;
630*!* " ( " + lcPercentage + " + " + lcRoundDownMinMult + ") " + ;
631*!* " ELSE (" + lcavai_Qty +") END "
632
633 *--- TR 1063182 03-Aug-2012 Partha ---
634*!* lcavai_Qty = " CASE WHEN h.round_to = 'Y' AND ( " + lcavai_Qty + ") > ( " + lcQtyField + " ) THEN " + ;
635*!* " ( " + lcPercentage + " + " + lcRoundDownMinMult + ") " + ;
636*!* " WHEN h.round_to = 'D' AND ( " + lcavai_Qty + ") > ( " + lcQtyField + " ) THEN " + ;
637*!* " ( " + lcPercentage + " + " + lcRoundUpMinMult + ") " + ;
638*!* " ELSE (" + lcavai_Qty +") END "
639
640 *!* when +ve then take qty- (qty % min_multiple) as NearestMult
641 *!* when -ve then take NearestMult - min_multiple as NextMultiple
642 lcNearestMult = " ( " + lcQtyField + " - (" + lcQtyField + " % sc.min_multiple )) "
643 lcavai_Qty = " CASE WHEN (" +lcavai_Qty + ") <= 0 AND ( " + lcavai_Qty + ") > ( " + lcQtyField + " ) THEN " + ;
644 " CASE WHEN " + lcNearestMult + " = " + lcQtyField + " THEN " + lcQtyField + " ELSE "+ lcNearestMult + " - sc.min_multiple END " + ;
645 " WHEN (" +lcavai_Qty + ") >0 AND ( " + lcavai_Qty + ") > ( " + lcQtyField + " ) THEN " + ;
646 lcNearestMult + ;
647 " ELSE (" + lcavai_Qty +") END "
648 *=== TR 1063182 03-Aug-2012 Partha ===
649
650 *=== TR 1061613 15-Jun-2012 Partha ===
651
652 *--- TechRec 1062747 18-Jul-2012 MANI. ---
653 IF NOT EMPTY(.nMinSzQtyAvl)
654 lcAvail_Qty1 = " CASE WHEN (" + lcavai_Qty + ") < " +.nMinSzQtyAvl + ;
655 " THEN 0 ELSE " + lcavai_Qty + " END "
656 ELSE
657 lcAvail_Qty1 = " CASE WHEN (" + lcavai_Qty + ") < 0" +;
658 " THEN 0 ELSE " + lcavai_Qty + " END "
659 ENDIF
660
661 IF .cShowZeroQty = "N"
662 lcSQLFilterString = lcSQLFilterString + " AND " + lcAvail_Qty1 +" > 0"
663 ENDIF
664
665*!* IF "SFNSIZE_QTY" $ lcSQLFilterString
666*!* lcSQLFilterString = STRTRAN(lcSQLFilterString,'SFNSIZE_QTY',lcavai_Qty)
667*!* ENDIF
668
669 *=== TechRec 1062747 18-Jul-2012 MANI. ===
670 *=== TechRec 1059983 12-Jun-2012 MANI. ===
671 *--- TR 1067015 11-Mar-2013 Partha ---
672 IF THIS.l846_OTS_QTY_FROM_AVAILABILITY_BY_STYLE
673 IF THIS.cAvail_Option = "A"
674 lcOTSQtyCal = "," + lcQtyField + " AS OTS_QTY "
675 ELSE
676 lcOTSQtyCal = ", (bal.Onhand_qty) AS OTS_QTY "
677
678 *--- TechRec 1072177 04-Aug-2013 vkrishnamurthy ---
679*!* lcOTSsizeqty = " SUM(Size01_Qty*Sz01+Size02_Qty*Sz02+Size03_Qty*Sz03+Size04_Qty*Sz04 + "+;
680*!* " Size05_Qty*Sz05+Size06_Qty*Sz06+Size07_Qty*Sz07+Size08_Qty*Sz08 +"+;
681*!* " Size09_Qty*Sz09+Size10_Qty*Sz10+Size11_Qty*Sz11+Size12_Qty*Sz12 + "+;
682*!* " Size13_Qty*Sz13+Size14_Qty*Sz14+Size15_Qty*Sz15+Size16_Qty*Sz16 + "+;
683*!* " Size17_Qty*Sz17+Size18_Qty*Sz18+Size19_Qty*Sz19+Size20_Qty*Sz20 +" + ;
684*!* " Size21_Qty*Sz21+Size22_Qty*Sz22+Size23_Qty*Sz23+Size24_Qty*Sz24) as Onhand_qty"
685
686*!* lcOTSSizeWhr = " Size01_Qty*Sz01+Size02_Qty*Sz02+Size03_Qty*Sz03+Size04_Qty*Sz04 + "+;
687*!* " Size05_Qty*Sz05+Size06_Qty*Sz06+Size07_Qty*Sz07+Size08_Qty*Sz08 + "+;
688*!* " Size09_Qty*Sz09+Size10_Qty*Sz10+Size11_Qty*Sz11+Size12_Qty*Sz12 + "+;
689*!* " Size13_Qty*Sz13+Size14_Qty*Sz14+Size15_Qty*Sz15+Size16_Qty*Sz16 + "+;
690*!* " Size17_Qty*Sz17+Size18_Qty*Sz18+Size19_Qty*Sz19+Size20_Qty*Sz20 + " + ;
691*!* " Size21_Qty*Sz21+Size22_Qty*Sz22+Size23_Qty*Sz23+Size24_Qty*Sz24 > 0 "
692
693 lcstr = ''
694 FOR l_nxx = 1 TO goEnv.MaxBuckets
695 l_cBucket = TRANSFORM(l_nxx, "@L 99")
696 lcstr = lcstr + IIF(!EMPTY(lcStr)," +","")+ " Size"+l_cBucket + "_Qty *Sz"+l_cBucket
697 ENDFOR
698 lcOTSsizeqty = " SUM("+ lcstr + ") as Onhand_qty"
699 lcOTSSizeWhr = lcstr + " >0 "
700 *=== TechRec 1072177 04-Aug-2013 vkrishnamurthy ===
701
702 *--- TechRec 1071370 31-May-2013 MANI. Added left keyword ===
703 lcZzbalJoin = " LEFT JOIN ( SELECT b.division, b.style,b.lbl_code, b.color_code, b.dimension, b.location, size_num as Size_bk , " + ;
704 lcOTSsizeqty +;
705 " FROM zzaalbal b " +;
706 " CROSS JOIN zzxBuckt x " + ;
707 " WHERE REC_TYPE = 'I' AND " + lcOTSSizeWhr + ;
708 " GROUP BY b.division, b.style,b.lbl_code, b.color_code, b.dimension, b.location, size_num ) bal " + ;
709 " ON bal.division = Szy.Division " + ;
710 " AND bal.Style = Szy.Style " + ;
711 " AND bal.Color_code = Szy.Color_Code " + ;
712 " AND bal.lbl_Code = Szy.lbl_Code " + ;
713 " AND bal.Dimension = Szy.Dimension " + ;
714 " AND bal.Size_bk = Szy.Size_bk " + ;
715 " AND bal.location = Szy.location "
716
717 ENDIF
718
719 ELSE
720 *=== TR 1067015 11-Mar-2013 Partha ===
721
722 *--- TechRec 1066673 02-Oct-2013 GSternik ---
723 *--- TR 1064279 7-Nov-2012 Goutam.
724 *--- TechRec 1066673 GSternik --- moved to Else:
725 *lcOTSQtyCal = " ,(SFUSA_V+SFURT_V+SFUR1_V+SFUR2_V-SFUIN_V-SFUPK_V-SFUCM_V-SFURS_V "+;
726 " -SFUOP_V-SFUDM_V-{FN IFNULL(({FN IFNULL(ZVXMRPUDV.SFUUD_V, 0)}), 0)}) as OTS_QTY "
727 IF this.l846_Use_Priority100_OTS
728 *--- TechRec 1066673 12-Aug-2013 GSternik --- zvoordrdv use is an overkill in here. just moved Line_Status to the filter
729*!* lcOrderSubSQL = " left join (Select division, style, color_code, lbl_Code, dimension, size_bk, location, line_status, SUM(bk_Qty) bk_Qty From " + ;
730*!* " zvoordrdv od " + ;
731*!* " where od.priority <= '100' " + ;
732*!* " Group by division, style, color_code, lbl_Code, dimension, size_bk, location, line_status) od " + ;
733*!* " on U.division = od.Division " + ;
734*!* " and U.Style = od.Style " + ;
735*!* " and U.Color_code = od.Color_Code " + ;
736*!* " and U.lbl_Code = od.lbl_Code " + ;
737*!* " and U.Dimension = od.Dimension " + ;
738*!* " and U.Sizebucket = od.Size_bk " + ;
739*!* " and h.location = od.location " + ;
740*!* " and od.line_status = 'O'"
741 *-- od alias was coliding with the main SQL!!!
742 lcOrderSubSQL = ;
743 " left join ("+;
744 "select Division, Style, Color_Code, Lbl_Code, Dimension, Size_Bk, Location, Sum(Bk_Qty) as bk_Qty" +;
745 " from zvoordrdv o " +;
746 " where o.Priority <= '100'" +;
747 " and o.Line_Status = 'O'" +;
748 " group by Division, Style, Color_Code, Lbl_Code, Dimension, Size_Bk, Location) ov " +;
749 " on u.Division = ov.Division " +;
750 " and u.Style = ov.Style " +;
751 " and u.Color_Code = ov.Color_Code " +;
752 " and u.Lbl_Code = ov.lbl_Code " +;
753 " and u.Dimension = ov.Dimension " +;
754 " and u.Sizebucket = ov.Size_bk " +;
755 " and h.Location = ov.Location "
756
757 lcOTSQtyCal = " ,(SFUSA_V+SFURT_V+SFUR1_V+SFUR2_V-SFUIN_V-SFUPK_V-SFUCM_V-SFURS_V "+;
758 " -IsNull(OV.BK_QTY, 0)-SFUDM_V-{FN IFNULL(({FN IFNULL(ZVXMRPUDV.SFUUD_V, 0)}), 0)}) AS OTS_QTY "
759
760 Else
761 *--- TechRec 1066673 02-Oct-2013 GSternik --- Moved to "Else"
762 lcOTSQtyCal = " ,(SFUSA_V+SFURT_V+SFUR1_V+SFUR2_V-SFUIN_V-SFUPK_V-SFUCM_V-SFURS_V "+;
763 " -SFUOP_V-SFUDM_V-{FN IFNULL(({FN IFNULL(ZVXMRPUDV.SFUUD_V, 0)}), 0)}) AS OTS_QTY "
764 *=== TechRec 1066673 12-Aug-2013 GSternik ===
765 ENDIF
766 *=== TR 1064279 7-Nov-2012 Goutam.
767
768 *--- TR 1067015 11-Mar-2013 Partha ---
769 ENDIF
770 *=== TR 1067015 11-Mar-2013 Partha ===
771
772 *--- TR 1064279 8-Nov-2012 Goutam.
773 *Removed following part from below sql and replace with lcOTSQtyCal. Added lcOrderSubSQL at the end.
774 *" ,(SFUSA_V+SFURT_V+SFUR1_V+SFUR2_V-SFUIN_V-SFUPK_V-SFUCM_V-SFURS_V "+;
775 " -SFUOP_V-SFUDM_V-{FN IFNULL(({FN IFNULL(ZVXMRPUDV.SFUUD_V, 0)}), 0)}) as OTS_QTY "+;
776 *=== TR 1064279 8-Nov-2012 Goutam.
777
778 *--- TR 1064755 09-Jan-13 SK Added join condition (Customer) for zzxcstdr to filter ===
779 *--- TechRec 1066673 17-Jul-2013 GSternik --- Optimized
780 *-- !!! this lcSQLString value is for the Source Safe comparison only. Not used (see below)
781 lcSQLString = " SELECT h.CtrlPkey, h.location " + ;
782 " ,h.locgroup " + ;
783 " ,h.vnd_qual " + ;
784 " ,h.vnd_id " + ;
785 " ,h.our_qual " + ;
786 " ,h.our_id " + ;
787 " ,(U.Upc_num+U.chk_digit) as UPC" + ;
788 " ,Szy.Style" + ;
789 " ,Szy.Color_code " + ;
790 " ,Szy.lbl_code " + ;
791 " ,Szy.Dimension " + ;
792 " ,U.Size_desc , " + ;
793 lcAvail_Qty1 + " as onhand_qty " + ;
794 " ,Szy.sfust_v as wip_qty " +;
795 " ,Szy.sfuop_v as open_qty" +;
796 " ,Szy.sfupk_v as pick_qty" +;
797 " ,Szy.Available as avl_only" +;
798 " ,Szy.division " +;
799 " ,st.style_name" +;
800 " ,cl.color_name " +;
801 " ,cl.color_desc " +;
802 " ,COALESCE(p1.due_date1,'')as prod_date1 " +;
803 " ,COALESCE(p2.due_date2,'')as prod_date2 " +;
804 " ,COALESCE(p3.due_date3,'')as prod_date3 " +;
805 " ,COALESCE(p4.due_date4,'')as prod_date4 " +;
806 " ,Szy.Size_bk " +;
807 lcOTSQtyCal + ;
808 " ,COALESCE(dr.cust_style, '') as Cust_Sku, COALESCE(dr.Aux_Sku, '') as Aux_Sku " + ;
809 " ,h.use_excl " + ; && TR 1064279 9-Nov-2012 Goutam
810 " ,COALESCE(dr.Style, '') as CustStyle " + ; && TR 1064279 9-Nov-2012 Goutam
811 " ,COALESCE(hr.Retail_price, 0) as cust_retail " + ; && TR 1064279 9-Nov-2012 Goutam
812 " FROM " + This.cQ_846Hdr + " H " + ;
813 " JOIN ZVXSSUMHVSZLL_call Szy " +;
814 " on Szy.Location = H.Location " + ;
815 " and Szy.available = case when h.avl_only = 'Y' then h.avl_only else Szy.available end " + ;
816 " JOIN Zveupcnr U " +;
817 " on U.division = Szy.Division" + ;
818 " and U.Style = Szy.Style " + ;
819 " and U.Color_code = Szy.Color_Code " + ;
820 " and U.lbl_Code = Szy.lbl_Code " + ;
821 " and U.Dimension = Szy.Dimension " + ;
822 " and U.Sizebucket = Szy.Size_bk " + ;
823 " JOIN ZZXSTYLR st " +;
824 " on st.division = u.division" + ;
825 " and st.style = u.style " + ;
826 " JOIN zzxscolr sc " + ;
827 " on U.division = Sc.Division" + ;
828 " and U.Style = Sc.Style " + ;
829 " and U.Color_code = Sc.Color_Code " + ;
830 " and U.lbl_Code = Sc.lbl_Code " + ;
831 " and U.Dimension = Sc.Dimension " + ;
832 lcCatgSubJoin + ;
833 " JOIN zzxcolrr cl " +;
834 " on cl.color_code = u.color_code" + ;
835 " LEFT OUTER JOIN zvcordrd_wip1v_shipto p1 "+ ;
836 " ON U.division = p1.division " + ;
837 " AND U.style = p1.style " + ;
838 " AND U.color_code = p1.color_code " +;
839 " AND U.lbl_code = p1.lbl_code " +;
840 " AND U.dimension = p1.dimension " +;
841 " AND u.sizebucket = p1.size_bk " +;
842 " AND h.location = p1.shipto " + ;
843 " LEFT OUTER JOIN zvcordrd_wip2v_shipto p2 "+ ;
844 " ON U.division = p2.division " + ;
845 " AND U.style = p2.style " + ;
846 " AND U.color_code = p2.color_code " +;
847 " AND U.lbl_code = p2.lbl_code " +;
848 " AND U.dimension = p2.dimension " +;
849 " AND u.sizebucket = p2.size_bk " +;
850 " AND h.location = p2.shipto " + ;
851 " LEFT OUTER JOIN zvcordrd_wip3v_shipto p3 "+ ;
852 " ON U.division = p3.division " + ;
853 " AND U.style = p3.style " + ;
854 " AND U.color_code = p3.color_code " +;
855 " AND U.lbl_code = p3.lbl_code " +;
856 " AND U.dimension = p3.dimension " +;
857 " AND u.sizebucket = p3.size_bk " +;
858 " AND h.location = p3.shipto " + ;
859 " LEFT OUTER JOIN zvcordrd_wip4v_shipto p4 "+ ;
860 " ON U.division = p4.division " + ;
861 " AND U.style = p4.style " + ;
862 " AND U.color_code = p4.color_code " +;
863 " AND U.lbl_code = p4.lbl_code " +;
864 " AND U.dimension = p4.dimension " +;
865 " AND u.sizebucket = p4.size_bk " +;
866 " AND h.location = p4.shipto " + ;
867 " LEFT OUTER JOIN ( select division,style,color_code,lbl_code,dimension,size_bk, " +;
868 " SUM(SFUUD_V) as SFUUD_V from ZVXMRPUDV group by " +;
869 " division,style,color_code,lbl_code,dimension,size_bk ) ZVXMRPUDV ON " +;
870 " U.DIVISION = ZVXMRPUDV.DIVISION " +;
871 " AND U.STYLE = ZVXMRPUDV.STYLE " +;
872 " AND U.COLOR_CODE = ZVXMRPUDV.COLOR_CODE " +;
873 " AND U.LBL_CODE = ZVXMRPUDV.LBL_CODE " +;
874 " AND U.DIMENSION = ZVXMRPUDV.DIMENSION " +;
875 " AND U.sizebucket = ZVXMRPUDV.SIZE_BK " +;
876 " LEFT JOIN zzxcstdr dr " + ;
877 " ON U.division = dr.division " + ;
878 " AND U.style = dr.style " + ;
879 " AND U.color_code = dr.color_code " +;
880 " AND U.lbl_code = dr.lbl_code " +;
881 " AND U.dimension = dr.dimension " +;
882 " AND u.sizebucket = dr.size_bk " +;
883 " AND h.customer = dr.customer " +; && 1064755
884 " LEFT JOIN zzxcsthr hr on hr.pkey = dr.fkey " + ; && TR 1064279 9-Nov-2012 Goutam
885 lcZzbalJoin + ;
886 lcOrderSubSQL + ;
887 " WHERE " + lcSQLFilterString
888
889 *=== TR 1040516 30-Jul-2009 Surinder Singh ===
890
891 *--- TR 1050639 04-Feb-2011 Partha ---
892 * we need to group the dtl based on location group. so we r adding one more layer of tmp dtl table
893
894 *lcTmpDtl = SQLTableFromQuery(lcSqlString)
895 *llRetVal = llRetVal AND !EMPTY(lcTmpDtl)
896
897 *--- TechRec 1066673 Query by GS:
898 lcTmpDtl = Iif(Version(2)=2,"##","#") + SubStr(SYS(2015), 2)
899Text to lcSqlString NoShow Flags 1
900
901;
902with wip as (
903select Division
904 , Style
905 , Color_Code
906 , Lbl_Code
907 , Dimension
908 , Size_Num as Size_Bk
909 , Due_Date
910 , sum( WIP01_Qty*Sz01+WIP02_Qty*Sz02+WIP03_Qty*Sz03+WIP04_Qty*Sz04+WIP05_Qty*Sz05+WIP06_Qty*Sz06+
911 WIP07_Qty*Sz07+WIP08_Qty*Sz08+WIP09_Qty*Sz09+WIP10_Qty*Sz10+WIP11_Qty*Sz11+WIP12_Qty*Sz12+
912 WIP13_Qty*Sz13+WIP14_Qty*Sz14+WIP15_Qty*Sz15+WIP16_Qty*Sz16+WIP17_Qty*Sz17+WIP18_Qty*Sz18+
913 WIP19_Qty*Sz19+WIP20_Qty*Sz20+WIP21_Qty*Sz21+WIP22_Qty*Sz22+WIP23_Qty*Sz23+WIP24_Qty*Sz24) as WIP_Qty
914 , ShipTo
915 , dense_rank() over (
916 partition by Division
917 , Style
918 , Color_Code
919 , Lbl_Code
920 , Dimension
921 , Size_Num
922 , ShipTo order by Due_Date) as Due_Rank
923 from zzcordrd p with (NoLock)
924 cross join zzxBuckt b with (NoLock)
925 where WIP_OK = 'Y'
926 and WIP_Total > 0
927 group by Division
928 , Style
929 , Color_Code
930 , Lbl_Code
931 , Dimension
932 , Size_Num
933 , Due_Date
934 , ShipTo
935having sum( WIP01_Qty*Sz01+WIP02_Qty*Sz02+WIP03_Qty*Sz03+WIP04_Qty*Sz04+WIP05_Qty*Sz05+WIP06_Qty*Sz06+
936 WIP07_Qty*Sz07+WIP08_Qty*Sz08+WIP09_Qty*Sz09+WIP10_Qty*Sz10+WIP11_Qty*Sz11+WIP12_Qty*Sz12+
937 WIP13_Qty*Sz13+WIP14_Qty*Sz14+WIP15_Qty*Sz15+WIP16_Qty*Sz16+WIP17_Qty*Sz17+WIP18_Qty*Sz18+
938 WIP19_Qty*Sz19+WIP20_Qty*Sz20+WIP21_Qty*Sz21+WIP22_Qty*Sz22+WIP23_Qty*Sz23+WIP24_Qty*Sz24) > 0)
939
940select h.CtrlPkey
941 , h.Location
942 , h.LocGroup
943 , h.Vnd_Qual
944 , h.Vnd_Id
945 , h.Our_Qual
946 , h.Our_Id
947 , (U.UPC_Num+U.Chk_Digit) as UPC
948 , szy.Style
949 , szy.Color_code
950 , szy.Lbl_Code
951 , szy.Dimension
952 , u.Size_Desc
953 , szy.SFUST_V as WIP_Qty
954 , szy.SFUOP_V as Open_Qty
955 , szy.SFUPK_V as Pick_Qty
956 , szy.Available as Avl_Only
957 , szy.Division
958 , st.Style_Name
959 , cl.Color_Name
960 , cl.Color_Desc
961 , coalesce(p1.Due_Date, '') as Prod_Date1
962 , coalesce(p2.Due_Date, '') as Prod_Date2
963 , coalesce(p3.Due_Date, '') as Prod_Date3
964 , coalesce(p4.Due_Date, '') as Prod_Date4
965 , szy.Size_bk
966 , coalesce(dr.Cust_Style, '') as Cust_Sku
967 , coalesce(dr.Aux_Sku, '') as Aux_Sku
968 , h.Use_Excl
969 , coalesce(dr.Style, '') as CustStyle
970 , coalesce(hr.Retail_price, 0) as Cust_Retail
971 -- OnHand, OTS, Bulk --
972 from --This.cQ_846Hdr--
973 join ZVXSSUMHVSZLL_call szy
974 on szy.Location = H.Location
975 and szy.available =
976 case
977 when h.Avl_Only = 'Y'
978 then h.Avl_Only
979 else szy.Available end
980 join zVeupcnr U
981 on u.Division = szy.Division
982 and u.Style = szy.Style
983 and u.Color_code = szy.Color_Code
984 and u.lbl_Code = szy.lbl_Code
985 and u.Dimension = szy.Dimension
986 and u.Sizebucket = szy.Size_bk
987 join ZZXSTYLR st
988 on st.Division = u.Division
989 and st.Style = u.Style
990 join zzxscolr sc
991 on u.Division = Sc.Division
992 and u.Style = Sc.Style
993 and u.Color_code = Sc.Color_Code
994 and u.Lbl_Code = Sc.Lbl_Code
995 and u.Dimension = Sc.Dimension
996 --lcCatgSubJoin--
997 join zzxcolrr cl
998 on cl.Color_Code = u.Color_Code
999 left join wip p1
1000 on p1.Division = u.Division
1001 and p1.Style = u.Style
1002 and p1.Color_Code = u.Color_Code
1003 and p1.Lbl_Code = u.Lbl_Code
1004 and p1.Dimension = u.Dimension
1005 and p1.Size_Bk = u.SizeBucket
1006 and p1.ShipTo = h.Location
1007 and p1.Due_Rank = 1
1008 left join wip p2
1009 on p2.Division = u.Division
1010 and p2.Style = u.Style
1011 and p2.Color_Code = u.Color_Code
1012 and p2.Lbl_Code = u.Lbl_Code
1013 and p2.Dimension = u.Dimension
1014 and p2.Size_Bk = u.SizeBucket
1015 and p2.ShipTo = h.Location
1016 and p2.Due_Rank = 2
1017 left join wip p3
1018 on p3.Division = u.Division
1019 and p3.Style = u.Style
1020 and p3.Color_Code = u.Color_Code
1021 and p3.Lbl_Code = u.Lbl_Code
1022 and p3.Dimension = u.Dimension
1023 and p3.Size_Bk = u.SizeBucket
1024 and p3.ShipTo = h.Location
1025 and p3.Due_Rank = 3
1026 left join wip p4
1027 on p4.Division = u.Division
1028 and p4.Style = u.Style
1029 and p4.Color_Code = u.Color_Code
1030 and p4.Lbl_Code = u.Lbl_Code
1031 and p4.Dimension = u.Dimension
1032 and p4.Size_Bk = u.SizeBucket
1033 and p4.ShipTo = h.Location
1034 and p4.Due_Rank = 4
1035 left join (
1036 select Division
1037 , Style
1038 , Color_Code
1039 , Lbl_Code
1040 , Dimension
1041 , Size_Bk
1042 , sum(SFUUD_V) as SFUUD_V
1043 from ZVXMRPUDV
1044 group by Division, Style, Color_Code, Lbl_Code, Dimension, Size_Bk ) ZVXMRPUDV
1045 on ZVXMRPUDV.Division = u.Division
1046 and ZVXMRPUDV.Style = u.Style
1047 and ZVXMRPUDV.Color_Code = u.Color_Code
1048 and ZVXMRPUDV.Lbl_Code = u.Lbl_Code
1049 and ZVXMRPUDV.Dimension = u.Dimension
1050 and ZVXMRPUDV.Size_Bk = u.SizeBucket
1051 left join zzxcstdr dr
1052 on dr.Division = u.Division
1053 and dr.Style = u.Style
1054 and dr.Color_Code = u.Color_Code
1055 and dr.Lbl_Code = u.Lbl_Code
1056 and dr.Dimension = u.Dimension
1057 and dr.Size_Bk = u.Sizebucket
1058 and dr.Customer = h.Customer
1059 left join zzxcsthr hr
1060 on hr.PKey = dr.FKey
1061EndText
1062
1063
1064 lcSqlString = Strtran(lcSqlString, "from --This.cQ_846Hdr--", ;
1065 "into " + lcTmpDtl + " from " + This.cQ_846Hdr + " H ")
1066 lcSqlString = Strtran(lcSqlString, "--lcCatgSubJoin--", lcCatgSubJoin)
1067 lcSqlString = Strtran(lcSqlString, "-- OnHand, OTS, Bulk --",;
1068 lcOTSQtyCal + ", " + lcAvail_Qty1 + " as OnHand_Qty " )+;
1069 lcZzbalJoin + ;
1070 lcOrderSubSQL + ;
1071 lcBulkOrderSubSQL +;
1072 " WHERE " + lcSQLFilterString
1073
1074 .oLog.LogEntry("Retrieving the inventory data.")
1075
1076 llRetVal = llRetVal and v_sqlExecUncommitted(lcSqlString)
1077
1078 *--- TechRec 1066673 02-Oct-2013 GSternik --- Thuis is not needed anymore
1079
1080*=== TechRec 1066673 17-Jul-2013 GSternik ===
1081
1082 .oLog.LogEntry("Removing locations from header that are not used in the detail...") && 1066673
1083
1084 * Remove locations from header that are not used in the detail
1085 * THis will let us respect filter criteria
1086
1087 *--- TR 1058987 06-Mar-2012 Goutam
1088*!* lcSQLString = "DELETE FROM " + .cQ_846Hdr + ;
1089*!* " FROM " + .cQ_846Hdr + " h " + ;
1090*!* " LEFT JOIN " + lcTmpDtl + " d " + ;
1091*!* " ON h.location = d.location " + ;
1092*!* " AND h.locgroup = d.locgroup " + ;
1093*!* " WHERE d.location is null and d.locgroup is null"
1094
1095 lcSQLString = ;
1096 "DELETE h" +;
1097 " FROM " + .cQ_846Hdr + " h " + ;
1098 " LEFT JOIN " + lcTmpDtl + " d " + ;
1099 " ON h.CtrlPkey = d.CtrlPkey " + ;
1100 " WHERE d.CtrlPkey is null"
1101
1102 *=== TR 1058987 06-Mar-2012 Goutam
1103
1104 llRetVal = llRetVal and v_sqlexec(lcSQLString)
1105
1106 .oLog.LogEntry("Update the header location with locgroup.") && 1066673
1107 * We are exporting LOCGROUP in dtl so we should do the same for the hdr also.
1108 * 1. update the header location with locgroup if it has locgroup
1109 * 2. group the header based on location,vnd_id,our_id,vnd_qual,our_qual
1110 lcSQLString = " UPDATE " + .cQ_846Hdr + " SET location = locgroup WHERE locgroup > '' "
1111 llRetVal = llRetVal AND V_SQLEXEC( lcSQLString )
1112
1113 .oLog.LogEntry("Header grouping...") && 1066673
1114 *--- TR 1058987 06-Mar-2012 Goutam. Added Customer, catg_dest and template in select list as well as group by
1115 *--- TechRec 1060583 19-Apr-2012 jisingh Added use_threshold, threshold_qty & res_cprice ===
1116 lcSQLString = " SELECT " + ;
1117 " location " + ;
1118 " ,vnd_id " + ;
1119 " ,vnd_qual " + ;
1120 " ,our_qual " + ;
1121 " ,our_id " + ;
1122 " ,MAX(active_ok) active_ok " + ;
1123 " ,MAX(avl_only) avl_only " + ;
1124 " ,MAX(report_date) report_date " + ;
1125 " ,MAX(report_time) report_time " + ;
1126 " ,MAX(Supplier_num) Supplier_num "+;
1127 " ,Customer, catg_dest, template " + ;
1128 " ,MAX(use_threshold) use_threshold, MAX(threshold_qty) threshold_qty, MAX(res_cprice) res_cprice " + ;
1129 " ,MAX(resv_loc_adr) resv_loc_adr " + ; &&--- TechRec 1076524 10-Apr-2014 TSV added resv_loc_adr ===
1130 " FROM " + .cQ_846Hdr + " H " + ;
1131 " GROUP BY " + ;
1132 " location " + ;
1133 " ,vnd_id " + ;
1134 " ,vnd_qual " + ;
1135 " ,our_qual " + ;
1136 " ,our_id, Customer, catg_dest, template"
1137 lcTmpHdr = SQLTableFromQuery(lcSqlString)
1138 llRetVal = llRetVal AND !EMPTY(lcTmpHdr)
1139 lcSQLString = " DROP TABLE " + .cQ_846Hdr
1140 llRetVal = llRetVal AND V_SQLEXEC( lcSQLString )
1141 .cQ_846Hdr = lcTmpHdr
1142
1143 *=== Hdr grouping completed
1144 .oLog.LogEntry("Update the detail location with locgroup...") && 1066673
1145 lcSQLString = " UPDATE " + lcTmpDtl + " SET location = locgroup WHERE locgroup > '' "
1146 llRetVal = llRetVal AND V_SQLEXEC( lcSQLString )
1147
1148 .oLog.LogEntry("Detail grouping...") && 1066673
1149 *--- TR 1064755 09-Jan-13 SK Used MAX() instead of SUM() for qty fields.
1150 *--- TR 1058987 06-Mar-2012 Goutam. Added aux_sku, cust_sku in select list as well as group by
1151 lcSQLString = " SELECT location " + ;
1152 " , vnd_qual " + ;
1153 " , vnd_id " + ;
1154 " , our_qual " + ;
1155 " , our_id " + ;
1156 " , UPC" + ;
1157 " , Style" + ;
1158 " , Color_code " + ;
1159 " , lbl_code " + ;
1160 " , Dimension " + ;
1161 " , Size_desc" + ;
1162 " , SUM(onhand_qty) onhand_qty " +;
1163 " , SUM(wip_qty) wip_qty" +;
1164 " , SUM(open_qty) open_qty" +;
1165 " , SUM(pick_qty) pick_qty" +;
1166 " , MAX(avl_only) avl_only " +;
1167 " , division " +;
1168 " , MAX(style_name) style_name " +;
1169 " , MAX(color_name) color_name " +;
1170 " , MAX(color_desc) color_desc " +;
1171 " , MAX(prod_date1) prod_date1 " +;
1172 " , MAX(prod_date2) prod_date2 " +;
1173 " , MAX(prod_date3) prod_date3 " +;
1174 " , MAX(prod_date4) prod_date4 " +;
1175 " , Size_bk " +;
1176 " , SUM(OTS_QTY) OTS_QTY " +;
1177 " , aux_sku, cust_sku " + ;
1178 " , use_excl " + ; && TR 1064279 9-Nov-2012 Goutam
1179 " , CustStyle " + ; && TR 1064279 9-Nov-2012 Goutam
1180 " , cust_retail " + ; && TR 1064279 9-Nov-2012 Goutam
1181 " FROM " + lctmpdtl + ;
1182 " GROUP BY location " + ;
1183 " , vnd_qual " + ;
1184 " , vnd_id " + ;
1185 " , our_qual " + ;
1186 " , our_id " + ;
1187 " , division " +;
1188 " , Style" + ;
1189 " , Color_code " + ;
1190 " , lbl_code " + ;
1191 " , Dimension " + ;
1192 " , Size_bk " +;
1193 " , Size_desc" + ;
1194 " , UPC, aux_sku, cust_sku " + ;
1195 " , use_excl " + ; && TR 1064279 9-Nov-2012 Goutam
1196 " , CustStyle " + ; && TR 1064279 9-Nov-2012 Goutam
1197 " , cust_retail " && TR 1064279 9-Nov-2012 Goutam
1198
1199 *=== TR 1050639 04-Feb-2011 Partha ===
1200
1201 If llRetVal
1202 .cQ_846Dtl = SQLTableFromQuery(lcSqlString)
1203 EndIf
1204
1205 llRetVal = llRetVal AND !EMPTY(.cQ_846Dtl)
1206
1207 llRetVal = llRetVal AND V_SQLEXEC( " Select count(*) cnt from " + .cQ_846Dtl , lcTempCursor )
1208
1209 llRetval = llRetVal AND USED(lcTempCursor)
1210
1211 IF llRetVal
1212 SELECT(lcTempCursor)
1213 .nDtlExport = cnt
1214 ENDIF
1215
1216 * Remove locations from header that are not used in the detail
1217 * THis will let us respect filter criteria
1218
1219 *--- TR 1050639 07-Feb-2011 Partha ---
1220*!* taking this up, after that tmpdtl creation and before update of location with location group. to based on that tmpdtl
1221*!* llRetVal = llRetVal and v_sqlexec("DELETE FROM " + This.cQ_846Hdr + " WHERE location NOT IN (SELECT DISTINCT location FROM " + .cQ_846Dtl + ")" )
1222 *=== TR 1050639 07-Feb-2011 Partha ===
1223
1224 llRetVal = llRetVal AND V_SQLEXEC( " Select count(*) cnt from " + .cQ_846Hdr , lcTempCursor )
1225
1226 llRetval = llRetVal AND USED(lcTempCursor)
1227
1228 IF llRetVal
1229 SELECT(lcTempCursor)
1230 .nTotalLoc = cnt
1231
1232 IF .nTotalLoc = 0
1233 .oLog.LogEntry("No records to process.")
1234 ENDIF
1235 ENDIF
1236
1237 *--- TechRec 1066673 22-Oct-2013 GSternik ---
1238 *--- TR 1050639 04-Feb-2011 Partha ---
1239 *lcSQLString = " DROP TABLE " + lctmpdtl
1240 *V_SQLEXEC(lcSQLString)
1241 *=== TR 1050639 04-Feb-2011 Partha ===
1242 =v_SqlExec("begin try drop table "+ lcTmpDtl +" end try begin catch end catch")
1243 *=== TechRec 1066673 22-Oct-2013 GSternik ===
1244
1245 ENDIF
1246
1247 .TableClose(lcTempCursor)
1248 ENDWITH
1249
1250 RETURN llRetVal
1251 ENDFUNC
1252
1253*=====================================================
1254 PROCEDURE DeletePreviousTransaction
1255
1256 LOCAL llRetVal, lcSQLString
1257
1258 llRetVal= .T.
1259 WITH THIS
1260 .InitThermo(1)
1261 .UpdateThermoCaption("Deleting previous 846(o) transactions...")
1262 .oLog.LogEntry("Deleting previous 846 ")
1263
1264
1265 lcSQLString = " DELETE FROM " + .cInterFaceHeader
1266 llRetVal = llRetVal AND .vSQLExec(lcSQLString)
1267
1268 lcSQLString = " DELETE FROM " + .cInterFaceDetail
1269 llRetVal = llRetVal AND .vSQLExec(lcSQLString)
1270 .AdvanceThermo(1)
1271
1272 ENDWITH
1273 IF !llRetVal
1274 .oLog.LogEntry("Deleting previous 846 - failed")
1275 ENDIF
1276
1277 RETURN llRetVal
1278 ENDPROC
1279*=====================================================
1280 PROCEDURE ValidateTransaction
1281
1282 LOCAL llRetVal, lnSelect
1283
1284 *--- TR 1058987 7-Mar-2012 Goutam
1285 LOCAL lcHeader, lcDetail, lcSql
1286 lcHeader = THIS.cInterFaceHeader
1287 lcDetail = THIS.cInterFaceDetail
1288 *=== TR 1058987 7-Mar-2012 Goutam
1289
1290 llRetVal = True
1291 lnSelect = SELECT()
1292
1293 WITH THIS
1294 *--- TR 1058987 7-Mar-2012 Goutam
1295 *--- 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
1296 lcSql = "Update d set errs_flg_d = 'Y', errs_msg_d = 'Bonton/Amazon SKU for Style: ' + RTRIM(d.Style) + ', Color: ' + RTRIM(d.Color_code) + " + ;
1297 "', Label: ' + RTRIM(lbl_code) + ', Size : ' + RTRIM(d.size_desc) + ' is not found' from " + lcHeader + " h join " + lcDetail + " d " + ;
1298 " on d.fkey = h.pkey where d.cust_sku = '' and (h.template = 'BONTON V.4010' or h.template = 'AMAZON.COM 4010')"
1299
1300 llRetVal = llRetVal AND v_SqlExec(lcSql)
1301
1302 *--- 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
1303 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) + " + ;
1304 "', Label: ' + RTRIM(lbl_code) + ', Size : ' + RTRIM(d.size_desc) + ' is not found' from " + lcHeader + " h join " + lcDetail + " d " + ;
1305 " on d.fkey = h.pkey where d.aux_sku = '' and (h.template = 'BONTON V.4010' or h.template = 'AMAZON.COM 4010')"
1306
1307 llRetVal = llRetVal AND v_SqlExec(lcSql)
1308
1309 lcSql = "Update h set errs_flg_h = 'Y', errs_msg_h = 'Errors in detail' from " + lcHeader + " h join " + lcDetail + " d " + ;
1310 " on d.fkey = h.pkey where d.errs_flg_d = 'Y'"
1311
1312 llRetVal = llRetVal AND v_SqlExec(lcSql)
1313
1314 *=== TR 1058987 7-Mar-2012 Goutam
1315 ENDWITH
1316
1317 SELECT (lnSelect)
1318 RETURN llRetVal
1319 ENDPROC
1320*=====================================================
1321 FUNCTION PopulateTransaction
1322
1323 LOCAL llRetVal, lnSelect, lcSQLString, lcTempTable, lcCursor ,ltLast_Mod,lcUser_id
1324
1325 ltLast_Mod = DATETIME()
1326 lcUser_id = goEnv.SV("cUser")
1327
1328 lnSelect = SELECT()
1329 llRetVal = True
1330
1331 WITH THIS
1332 THIS.oLog.LogMajorStage("Retrieving Location(s)")
1333 *-- Populating Header
1334 WITH THIS.oSB
1335 .SB_Reset()
1336 .cSB_Target = THIS.cInterFaceHeader
1337 .cSB_Source = THIS.cQ_846Hdr
1338 .cSB_SourceAlias = "loc"
1339 .cSB_WHEREString = " "
1340
1341 .SB_SetLiteralFieldValue("location","loc.location")
1342 .SB_SetLiteralFieldValue("errs_flg_h","''")
1343 .SB_SetLiteralFieldValue("errs_msg_h","''")
1344 .SB_SetLiteralFieldValue("notes","''")
1345 .SB_SetLiteralFieldValue("last_mod",SQLFormatTS(ltLast_Mod))
1346 .SB_SetLiteralFieldValue("user_id",SQLFormatChar(lcUser_id))
1347 *--- TR 1040516 30-Jul-2009 Surinder Singh ---
1348 .SB_SetLiteralFieldValue("location","loc.supplier_num")
1349 *=== TR 1040516 30-Jul-2009 Surinder Singh ===
1350 *--- TR 1058987 10-Mar-2012 Goutam. Added loc.customer+loc.catg_dest and made Char(40) to Char(52)
1351 .SB_KeySetAddKeySpec("pkey","Char(52)","loc.location+loc.vnd_qual+loc.vnd_id+loc.our_qual+loc.our_id+loc.customer+loc.catg_dest ")
1352
1353 llRetVal = .SB_GenerateSQLInsertString()
1354 ENDWITH
1355
1356 llRetVal = llRetVal AND .vSQLExec(THIS.oSB.cSQLString)
1357 .AdvanceThermo(.5)
1358
1359 *-- Populating Detail
1360 WITH THIS.oSB
1361 .SB_Reset()
1362 .cSB_Target = THIS.cInterFaceDetail
1363 .cSB_Source = THIS.cQ_846Dtl
1364 .cSB_SourceAlias = "d"
1365
1366 *--- TechRec 1060583 19-Apr-2012 jisingh Added LEFT JOIN to zzxcstpr ===
1367 .cSB_FROMExpandedString = " JOIN " + THIS.cInterFaceHeader + " h " + ;
1368 " ON h.location = d.location" + ;
1369 " AND h.vnd_qual = d.vnd_qual " + ;
1370 " AND h.vnd_id = d.vnd_id " + ;
1371 " AND h.our_qual = d.our_qual " + ;
1372 " AND h.our_id = d.our_id " + ;
1373 " LEFT JOIN zzxcstpr cp " + ;
1374 " ON cp.customer = h.customer " + ;
1375 " AND cp.division = d.division " + ;
1376 " AND cp.style = d.style " + ;
1377 " AND cp.color_code = d.color_code " + ;
1378 " AND cp.lbl_code = d.lbl_code " + ;
1379 " AND cp.dimension = d.dimension "
1380
1381 *--- TechRec 1060583 19-Apr-2012 jisingh ---
1382 *.cSB_WHEREString = " "
1383
1384 *--- TR 1064279 7-Nov-2012 Goutam. Changed excl_846 to Remove_846, excl_846_sent to remove_846_sent
1385 *--- TR 1065893 16-12-2012 VKK
1386 * this is for use_excl = N
1387 *.cSB_WHEREString = " COALESCE(cp.remove_846,'') <> 'Y' OR (COALESCE(cp.remove_846,'') = 'Y' AND COALESCE(cp.remove_846_sent,'') <> 'Y') "
1388 .cSB_WHEREString = " d.use_excl = 'N' or d.use_excl = '' "
1389 *=== TR 1065893 16-12-2012 VKK
1390 *=== TechRec 1060583 19-Apr-2012 jisingh ===
1391 .SB_SetLiteralFieldValue("fkey","h.pkey")
1392
1393 .SB_SetLiteralFieldValue("errs_flg_d","''")
1394 .SB_SetLiteralFieldValue("errs_msg_d","''")
1395 .SB_SetLiteralFieldValue("notes","''")
1396 .SB_SetLiteralFieldValue("last_mod",SQLFormatTS(ltLast_Mod))
1397 .SB_SetLiteralFieldValue("user_id",SQLFormatChar(lcUser_id))
1398
1399 *--- TechRec 1060583 19-Apr-2012 jisingh ---
1400 .SB_SetLiteralFieldValue("cust_price", "CASE WHEN h.res_cprice = 'Y' THEN COALESCE(cp.price,0) ELSE 0 END")
1401
1402 *--- TR 1064279 7-Nov-2012 Goutam.
1403 .SB_SetLiteralFieldValue("cust_retail", "CASE WHEN d.cust_retail = 0 THEN COALESCE(cp.retail_price,0) ELSE d.cust_retail END")
1404 *.SB_SetLiteralFieldValue("discontinue_date", SQLFormatTS(DATE()))
1405 *=== TR 1064279 7-Nov-2012 Goutam.
1406
1407 *--- TR 1064279 7-Nov-2012 Goutam. Changed excl_846 to remove_846
1408*!* .SB_SetLiteralFieldValue("ots_qty", "COALESCE((CASE WHEN COALESCE(cp.remove_846,'') = 'Y' THEN 0 ELSE CASE WHEN h.use_threshold = 'Y' THEN " + ;
1409*!* "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)")
1410 *=== TechRec 1060583 19-Apr-2012 jisingh ===
1411
1412 .SB_SetLiteralFieldValue("ots_qty", "d.ots_qty")
1413
1414 *--- TR 1050639 04-Feb-2011 Partha ---
1415*!* .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)")
1416
1417 *--- TR 1058987 10-Mar-2012 Goutam. Added cust_sku+aux_sku
1418 *--- TR 1066673 23-Feb-2013 Bnarayanan ---
1419 *.SB_KeySetAddKeySpec("pkey","Char(250)","STR(h.pkey)+d.location+d.vnd_qual+d.vnd_id+d.our_qual+ ;
1420 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 ")
1421
1422 .SB_KeySetAddKeySpec("pkey","Int","h.pkey")
1423 .SB_KeySetAddKeySpec("pkey1","CHAR(6)","d.location")
1424 .SB_KeySetAddKeySpec("pkey2","CHAR(2)","d.vnd_qual")
1425 .SB_KeySetAddKeySpec("pkey3","CHAR(15)","d.vnd_id")
1426
1427 .SB_KeySetAddKeySpec("pkey4","CHAR(2)","d.our_qual")
1428 .SB_KeySetAddKeySpec("pkey5","CHAR(15)","d.our_id")
1429 .SB_KeySetAddKeySpec("pkey6","CHAR(3)","d.division")
1430 .SB_KeySetAddKeySpec("pkey7","VARCHAR(50)","d.style")
1431
1432 .SB_KeySetAddKeySpec("pkey8","VARCHAR(50)","d.color_code")
1433 .SB_KeySetAddKeySpec("pkey9","CHAR(7)","d.lbl_code")
1434 .SB_KeySetAddKeySpec("pkey10","CHAR(5)","d.dimension")
1435 .SB_KeySetAddKeySpec("pkey11","Int","d.Size_bk")
1436
1437 .SB_KeySetAddKeySpec("pkey12","CHAR(10)","d.Size_desc")
1438 .SB_KeySetAddKeySpec("pkey13","CHAR(12)","d.UPC")
1439 .SB_KeySetAddKeySpec("pkey14","CHAR(27)","cust_sku")
1440 .SB_KeySetAddKeySpec("pkey15","VARCHAR(50)","aux_sku")
1441
1442 *=== TR 1066673 23-Feb-2013 BNarayanan ===
1443 *=== TR 1050639 04-Feb-2011 Partha ===
1444
1445 llRetVal = .SB_GenerateSQLInsertString()
1446
1447
1448 ENDWITH
1449
1450
1451
1452 llRetVal = llRetVal AND .vSQLExec(THIS.oSB.cSQLString)
1453
1454 .AdvanceThermo(1)
1455
1456 *--- TR 1064279 7-Nov-2012 Goutam. New set above insertion with different set of condition.
1457 *-- Populating Detail ONCE MORE
1458 WITH THIS.oSB
1459 .SB_Reset()
1460 .cSB_Target = THIS.cInterFaceDetail
1461 .cSB_Source = THIS.cQ_846Dtl
1462 .cSB_SourceAlias = "d"
1463
1464 .cSB_FROMExpandedString = " JOIN " + THIS.cInterFaceHeader + " h " + ;
1465 " ON h.location = d.location" + ;
1466 " AND h.vnd_qual = d.vnd_qual " + ;
1467 " AND h.vnd_id = d.vnd_id " + ;
1468 " AND h.our_qual = d.our_qual " + ;
1469 " AND h.our_id = d.our_id " + ;
1470 " JOIN zzxcstpr cp " + ;
1471 " ON cp.customer = h.customer " + ;
1472 " AND cp.division = d.division " + ;
1473 " AND cp.style = d.style " + ;
1474 " AND cp.color_code = d.color_code " + ;
1475 " AND cp.lbl_code = d.lbl_code " + ;
1476 " AND cp.dimension = d.dimension "
1477
1478 *--- TR 1065893 16-12-2012 VKK
1479 *.cSB_WHEREString = "Not (COALESCE(cp.remove_846,'') <> 'Y' OR (COALESCE(cp.remove_846,'') = 'Y' AND COALESCE(cp.remove_846_sent,'') <> 'Y')) " + ;
1480 * " and (COALESCE(cp.excl_846,'') <> 'Y' OR (COALESCE(cp.excl_846,'') = 'Y' AND COALESCE(cp.excl_846_sent,'') <> 'Y')) " + ;
1481 * " 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 "
1482
1483 .cSB_WHEREString = " d.use_excl <> 'N' and d.use_excl > '' " + ; &&Only use these setup records
1484 " AND COALESCE(cp.excl_846,'') <> 'Y' " + ; &&Exclude anything not meant to be included
1485 " 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
1486 " AND NOT (cp.remove_846 = 'Y' AND cp.remove_846_sent = 'Y') " && do not send items that are removed and marked remove sent
1487 *=== TR 1065893 16-12-2012 VKK
1488
1489 .SB_SetLiteralFieldValue("fkey","h.pkey")
1490
1491 .SB_SetLiteralFieldValue("errs_flg_d","''")
1492 .SB_SetLiteralFieldValue("errs_msg_d","''")
1493 .SB_SetLiteralFieldValue("notes","''")
1494 .SB_SetLiteralFieldValue("last_mod",SQLFormatTS(ltLast_Mod))
1495 .SB_SetLiteralFieldValue("user_id",SQLFormatChar(lcUser_id))
1496
1497 .SB_SetLiteralFieldValue("cust_price", "CASE WHEN h.res_cprice = 'Y' THEN COALESCE(cp.price,0) ELSE 0 END")
1498 *--- TR 1065893 16-12-2012 VKK
1499*!* .SB_SetLiteralFieldValue("cust_retail", "CASE WHEN d.cust_retail = 0 THEN COALESCE(cp.retail_price,0) ELSE d.cust_retail END")
1500*!* .SB_SetLiteralFieldValue("discontinue_date", "CASE WHEN d.onhand_qty = 0 THEN '12/31/2039' ELSE '01/01/1900' END")
1501
1502*!* .SB_SetLiteralFieldValue("ots_qty", "COALESCE((CASE WHEN COALESCE(cp.remove_846,'') = 'Y' THEN 0 ELSE CASE WHEN h.use_threshold = 'Y' THEN " + ;
1503*!* "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)")
1504
1505 .SB_SetLiteralFieldValue("cust_retail", "CASE WHEN d.custstyle = '' THEN COALESCE(cp.retail_price,0) ELSE d.cust_retail END")
1506
1507
1508
1509 lcOtsThreshold = " dbo.bcfn_MAX(0,d.ots_qty - CASE WHEN h.use_threshold = 'Y' THEN h.threshold_qty ELSE 0 END)"
1510
1511
1512
1513 .SB_SetLiteralFieldValue("discontinue_date", "CASE WHEN cp.remove_846 = 'Y' THEN " + SQLFormatTS(DATE()) + ;
1514 " WHEN "+lcOtsThreshold+" <= 0 THEN '12/31/2039' ELSE '01/01/1900' END")
1515
1516 .SB_SetLiteralFieldValue("ots_qty", "COALESCE((CASE WHEN COALESCE(cp.remove_846,'') = 'Y' THEN 0 ELSE " + lcOtsThreshold + " END),0)")
1517 *=== TR 1065893 16-12-2012 VKK
1518 .SB_KeySetAddKeySpec("pkey","Char(250)","STR(h.pkey)+d.location+d.vnd_qual+d.vnd_id+d.our_qual+ ;
1519 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 ")
1520
1521 llRetVal = .SB_GenerateSQLInsertString()
1522
1523 ENDWITH
1524
1525 llRetVal = llRetVal AND .vSQLExec(THIS.oSB.cSQLString)
1526 .AdvanceThermo(1)
1527 *=== TR 1064279 7-Nov-2012 Goutam.
1528
1529 *--- TechRec 1060583 19-Apr-2012 jisingh ---
1530 llRetVal = llRetVal AND .vSQLExec(" DELETE h FROM " + .cInterFaceHeader + " h " + ;
1531 " LEFT JOIN " + .cInterFaceDetail + " d " + ;
1532 " ON h.pkey = d.fkey " + ;
1533 " WHERE d.pkey IS NULL ")
1534
1535 llRetVal = llRetVal AND .vSQLExec(" SELECT COUNT(*) ct FROM " + .cInterFaceHeader, "tcCount")
1536
1537 IF llRetVal AND USED("tcCount")
1538 .nTotalLoc = tcCount.ct
1539 .TableClose("tcCount")
1540 ENDIF
1541 *=== TechRec 1060583 19-Apr-2012 jisingh ===
1542 ENDWITH
1543
1544 SELECT (lnSelect)
1545 RETURN llRetVal
1546 ENDFUNC
1547*=====================================================
1548 PROCEDURE UnLoadInterfaceToFlatFile
1549 LPARAMETERS tcProcess, tcFlatFile, tcInterfaceHeader, tcInterfaceDetail, tcAddress
1550 &&--- TechRec 1076524 24-Apr-2014 TSV added tcAddress ===
1551 LOCAL llRetVal, lcSQLString, lnHandle
1552
1553 lnHandle = 0
1554 llRetVal= True
1555
1556 WITH THIS
1557 *--Get Metadata for vendor table
1558 IF .GetTranslationDefinition(EDI_ALL_TRANSACTION, "tcEmVend")
1559
1560 IF .GetTranslationDefinition(tcProcess, "tcEmData")
1561 *-- Creating Metadata work tables
1562 .CreateMetaDataWorkTable("tcEmVend", "VENDOR", "tcoibVND")
1563 .CreateMetaDataWorkTable("tcEmData", "HEADER", "tcoibHDR")
1564 .CreateMetaDataWorkTable("tcEmData", "DETAIL", "tcoibDTL")
1565
1566 *--- TechRec 1076524 24-Apr-2014 TSV---
1567 .CreateMetaDataWorkTable("tcEmData", "ADDRESS", "tcoibADR")
1568 *=== TechRec 1076524 24-Apr-2014 TSV===
1569
1570 *--- TR 1034990 NSD 8/8/08
1571 * Create index for SEEK/SCAN WHILE
1572 SELECT tcoibDTL
1573 *--- TR 1058987 17-4-2012 VKK
1574 *INDEX ON location TAG location
1575 INDEX ON fkey TAG fkey
1576 *=== TR 1058987 17-4-2012 VKK
1577 *=== TR 1034990 NSD 8/8/08
1578
1579 *--- TechRec 1076524 24-Apr-2014 TSV added ===
1580 SELECT tcoibADR
1581 INDEX ON location TAG location
1582 *=== TechRec 1076524 24-Apr-2014 TSV added ===
1583
1584 *--- TechRec 1058987 18-Apr-2012 jisingh ---
1585 SELECT tcoibHDR
1586 INDEX ON location + vnd_qual + vnd_id TAG locvnd
1587 *=== TechRec 1058987 18-Apr-2012 jisingh ===
1588
1589 *--- TR 1061446 25-May-2012 Goutam. Moving down. Should try to create flat file if valid record esists.
1590*!* IF NOT .Tie_Setup
1591*!* lcTempFlatfile= STRTRAN(UPPER(tcFlatFile), ".DAT", ".TMP")
1592*!* DELETE FILE (lcTempFlatfile)
1593*!* lnHandle = .OpenFlatFile(lcTempFlatfile, True)
1594*!* ENDIF
1595 *=== TR 1061446 25-May-2012 Goutam
1596
1597 *--- TR 1061446 25-May-2012 Goutam. Should go here always
1598 *IF .Tie_Setup OR lnHandle> 0
1599 IF true
1600 *=== TR 1061446 25-May-2012 Goutam
1601
1602 *-- Populating data to metadata work detail tables from interface tables
1603 *--- TR 1058987 7-Mar-2012 Goutam. Added third parameter in following RemoteScatterGather
1604 .RemoteScatterGather(tcInterfaceHeader,"tcoibHDR", " where errs_flg_h <> 'Y'")
1605 .RemoteScatterGather(tcInterfaceDetail,"tcoibDTL", " where errs_flg_d <> 'Y'")
1606
1607 *--- TechRec 1076524 24-Apr-2014 TSV---
1608 .RemoteScatterGather(tcAddress, "tcoibADR")
1609 *=== TechRec 1076524 24-Apr-2014 TSV===
1610
1611 *--- TR 1058987 7-Mar-2012 Goutam
1612 .nFailedValidation = .nTotalLoc - RECCOUNT("tcoibHDR")
1613 .nDtlExport = RECCOUNT("tcoibDTL")
1614 *=== TR 1058987 7-Mar-2012 Goutam
1615
1616 *-- Unloading to Flat file
1617 *--- TR 1058987 27-Mar-2012 Goutam
1618 IF (.nTotalLoc - .nFailedValidation) > 0
1619 *=== TR 1058987 27-Mar-2012 Goutam
1620
1621 *--- TR 1061446 25-May-2012 Goutam. Moved here from up. Should try to create flat file if valid record esists.
1622 IF NOT .Tie_Setup
1623 lcTempFlatfile= STRTRAN(UPPER(tcFlatFile), ".DAT", ".TMP")
1624 DELETE FILE (lcTempFlatfile)
1625 lnHandle = .OpenFlatFile(lcTempFlatfile, True)
1626 ENDIF
1627 *=== TR 1061446 25-May-2012 Goutam
1628 &&--- TechRec 1076524 24-Apr-2014 TSV added "tcoibADR" ===
1629 llRetVal = .CreateFlatfileFromMetadata(tcProcess, .cControlReference , "tcoibVND", ;
1630 "tcoibHDR", "tcoibDTL", lnHandle, "tcoibADR")
1631
1632 *--- TR 1058987 27-Mar-2012 Goutam
1633 ENDIF
1634 *=== TR 1058987 27-Mar-2012 Goutam
1635
1636 IF lnHandle > 0
1637 FCLOSE(lnHandle)
1638 ENDIF
1639
1640 IF !llRetVal
1641 .oLog.LogEntry("Creating Flat file from Metadata - failed")
1642 ENDIF
1643 ENDIF
1644 ENDIF
1645 ENDIF
1646 .TableClose('tcoibHDR')
1647 .TableClose('tcoibDTL')
1648 ENDWITH
1649 IF !llRetVal
1650 .oLog.LogEntry("Unloading to flat file - failed")
1651 ENDIF
1652
1653 RETURN llRetVal
1654 ENDPROC
1655*=====================================================
1656 PROCEDURE CreateFlatfileFromMetadata
1657 LPARAMETER tcProcess, tcWorkControl, tcWorkVendor, tcWorkHeader, tcWorkDetail, pnHandle, tcWorkAddress
1658 &&--- TechRec 1076524 24-Apr-2014 TSV added tcWorkAddress ===
1659
1660 &&--- TechRec 1076524 24-Apr-2014 TSV added llAdrLine ===
1661 LOCAL llRetVal, lnOldSelect, lnCurrentPkey, lcString, llAdrLine, ;
1662 llDelimited, lcDelimiter, ;
1663 lcPreviousLocation, lcControl, lnDtlCount, ;
1664 lcLocation, lcOutBoundFlatFile, lcTempFlatfile, lnHandle, lcOrder
1665
1666 *--- TechRec 1058987 18-Apr-2012 jisingh ---
1667 LOCAL lcVndQual, lcVndId, lcPreviousVndQual, lcPreviousVndId
1668
1669 lcVndQual = ""
1670 lcVndId = ""
1671 lcPreviousVndQual = ""
1672 lcPreviousVndId = ""
1673 *=== TechRec 1058987 18-Apr-2012 jisingh ===
1674
1675 llRetVal= True
1676
1677 lcPreviousLocation = ""
1678 lcLocation = ""
1679 lnHandle = 0
1680 lcDelimiter = ""
1681
1682 lnOldSelect= SELECT()
1683
1684 WITH THIS
1685 *--- TechRec 1060583 19-Apr-2012 jisingh ---
1686 .TableClose("__tcFlatFileList")
1687 CREATE CURSOR __tcFlatFileList(TempFlatFile MEMO, OutBoundFlatFile MEMO)
1688 *=== TechRec 1060583 19-Apr-2012 jisingh ===
1689
1690 *-- Control Reference
1691 lcControl = GetUniqueFileName()
1692
1693 lcSQLString = " SELECT pcr.* " + ;
1694 " FROM " + tcWorkControl + " pcr "
1695
1696 llRetVal = llRetVal AND .vSQLExec(lcSQLString, lcControl)
1697
1698 *--- TR 1034990 NSD 8/8/08
1699 *--- TechRec 1058987 18-Apr-2012 jisingh ---
1700 *INDEX ON ALLTRIM(location) TAG location
1701 INDEX ON ALLTRIM(location) + ALLTRIM(vnd_qual) + ALLTRIM(vnd_id) TAG locvnd
1702 *=== TechRec 1058987 18-Apr-2012 jisingh ===
1703 *=== TR 1034990 NSD 8/8/08
1704
1705 IF llRetVal
1706 SELECT (tcWorkHeader)
1707 SCAN
1708 * Reset lcString & Counters
1709 lcString = ""
1710 lnDtlCount = 1
1711
1712 lcLocation = Location
1713 *--- TR 1058987 17-4-2012 VKK
1714 lnPkey = Pkey
1715 *=== TR 1058987 17-4-2012 VKK
1716
1717 *--- TechRec 1058987 18-Apr-2012 jisingh ---
1718 lcVndQual = vnd_qual
1719 lcVndId = vnd_id
1720 *=== TechRec 1058987 18-Apr-2012 jisingh ===
1721
1722
1723 *--- TR 1067890 03-21-2013 RKI ---*
1724 llLocVndChanged = NOT (lcLocation + lcVndQual + lcVndId == lcPreviousLocation + lcPreviousVndQual + lcPreviousVndId)
1725 *=== TR 1067890 03-21-2013 RKI ===*
1726
1727 SELECT (lcControl)
1728
1729 *--- TR 1034990 NSD 8/8/08
1730 *LOCATE FOR location == lcLocation
1731 *IF FOUND()
1732 *--- TechRec 1058987 18-Apr-2012 jisingh ---
1733 *IF SEEK(ALLTRIM(lcLocation),lcControl,"location")
1734 IF SEEK(ALLTRIM(lcLocation)+ALLTRIM(lcVndQual)+ALLTRIM(lcVndId),lcControl,"locvnd")
1735 *=== TechRec 1058987 18-Apr-2012 jisingh ===
1736 *=== TR 1034990 NSD 8/8/08
1737
1738 llDelimited = (EDI_Format = 'D')
1739 lcDelimiter = IIF(llDelimited, EDI_Delim, "")
1740 ELSE
1741
1742 *--- TR 1034990 NSD 8/8/08
1743 *LOCATE FOR location == ""
1744 *IF FOUND()
1745 *--- TechRec 1059983 14-Jun-2012 MANI. To avoid index tag not found issue ---
1746 *IF SEEK("",lcControl,"fkey")
1747 IF SEEK("",lcControl,"locvnd")
1748 *=== TechRec 1059983 14-Jun-2012 MANI. ===
1749 *=== TR 1034990 NSD 8/8/08
1750
1751 llDelimited = (EDI_Format = 'D')
1752 lcDelimiter = IIF(llDelimited, EDI_Delim, "")
1753 ENDIF
1754
1755 ENDIF
1756
1757 *--- TechRec 1076524 24-Apr-2014 TSV---
1758 llAdrLine = False
1759 IF llLocVndChanged AND resv_loc_adr = 'Y'
1760 SELECT (tcWorkAddress)
1761
1762 IF SEEK(lcLocation, tcWorkAddress, "location")
1763 llAdrLine = True
1764 ELSE
1765 llAdrLine = False
1766 ENDIF
1767
1768 ENDIF
1769 *=== TechRec 1076524 24-Apr-2014 TSV===
1770
1771 *--- TechRec 1058987 18-Apr-2012 jisingh ---
1772 *IF NOT ( lcLocation == lcPreviousLocation )
1773 IF NOT (lcLocation + lcVndQual + lcVndId == lcPreviousLocation + lcPreviousVndQual + lcPreviousVndId)
1774 *=== TechRec 1058987 18-Apr-2012 jisingh ===
1775
1776 SELECT (lcControl)
1777 lcPreviousLocation = Location
1778 *--- TechRec 1058987 18-Apr-2012 jisingh ---
1779 lcPreviousVndQual = vnd_qual
1780 lcPreviousVndId = vnd_id
1781 *=== TechRec 1058987 18-Apr-2012 jisingh ===
1782
1783 SELECT (tcWorkVendor)
1784 APPEND BLANK
1785 REPLACE vnd_tran WITH tcProcess, ;
1786 vnd_id WITH EVALUATE(lcControl + ".vnd_id"), ;
1787 vnd_vers WITH EVALUATE(lcControl + ".vnd_vers") IN (tcWorkVendor)
1788
1789 *-- Create new file name and handle
1790 IF .Tie_Setup
1791 IF lnHandle > 0
1792 FCLOSE (lnHandle)
1793 lcTempFlatfile = STRTRAN(UPPER(lcOutBoundFlatFile), ".DAT", ".TMP")
1794 *--- TechRec 1060583 19-Apr-2012 jisingh ---
1795 *llRetVal = llRetVal and .CopyToFlatFileInBlock(lcTempFlatfile, lcOutBoundFlatFile)
1796 INSERT INTO __tcFlatFileList(TempFlatFile,OutBoundFlatFile) VALUES (lcTempFlatfile, lcOutBoundFlatFile)
1797 *=== TechRec 1060583 19-Apr-2012 jisingh ===
1798 ENDIF
1799
1800 *-- New flat file name
1801 lcOutBoundFlatFile = lcEDIPath + ALLTRIM(EVALUATE(tcWorkVendor + ".vnd_id")) + EDI_OIB_FILENAME
1802 lcTempFlatfile = STRTRAN(UPPER(lcOutBoundFlatFile), ".DAT", ".TMP")
1803 DELETE FILE (lcTempFlatfile)
1804 lnHandle = .OpenFlatFile(lcTempFlatfile, .T.)
1805 IF lnHandle <= 0
1806 LOOP
1807 ENDIF
1808 ENDIF
1809 ENDIF
1810
1811 *---- TR 1064402 12-OCT-12 Venuk
1812 *-- Output Vendor string "VND"
1813*!* lcString = lcString + EDI_VENDOR_TAG + lcDelimiter + .ConvertRecordToText(tcWorkVendor, llDelimited, lcDelimiter)
1814
1815*!* SELECT (tcWorkHeader)
1816*!* lcString = lcString + EDI_HEADER_TAG + lcDelimiter + .ConvertRecordToText(tcWorkHeader, llDelimited, lcDelimiter)
1817 *=== TR 1064402 12-OCT-12 Venuk
1818 *-- Output Detail string "DTL"
1819 SELECT (tcWorkDetail)
1820
1821 *--- TR 1034990 NSD 8/7/08
1822 * remove 10,000 detail restriction, do a seek/scan while instead of scan for.
1823 *SCAN FOR Location = lcLocation AND lnDtlCount < 10000
1824 *--- TR 1058987 17-4-2012 VKK replaced location seek with pkey/fkey seek
1825 IF SEEK(lnPkey,tcWorkDetail,"fkey")
1826 lcOrder = SET("ORDER")
1827 SET ORDER TO fkey &&*--- TR 1058987 17-4-2012 VKK changed to fkey
1828 SCAN WHILE fkey == lnPkey && *--- TR 1058987 17-4-2012 VKK changed to fkey fromlocation
1829 *---- TR 1064402 12-OCT-12 Venuk
1830 IF lnDtlCount = 1 OR MOD(lnDtlCount,10000) = 0
1831 *-- Output Vendor string "VND"
1832 SELECT (tcWorkVendor)
1833 lcString = lcString + EDI_VENDOR_TAG + lcDelimiter + .ConvertRecordToText(tcWorkVendor, llDelimited, lcDelimiter)
1834 *-- Output header string "HDR"
1835 SELECT (tcWorkHeader)
1836 lcString = lcString + EDI_HEADER_TAG + lcDelimiter + .ConvertRecordToText(tcWorkHeader, llDelimited, lcDelimiter)
1837
1838 *--- TechRec 1076524 24-Apr-2014 TSV---
1839 IF llAdrLine
1840 lcString = lcString + EDI_ADDRESS_TAG + lcDelimiter + .ConvertRecordToText(tcWorkAddress, llDelimited, lcDelimiter)
1841 lnDtlCount = lnDtlCount + 1 && TR 1080713 count every line
1842 ENDIF
1843 *=== TechRec 1076524 24-Apr-2014 TSV===
1844
1845 SELECT (tcWorkDetail)
1846 ENDIF
1847 *=== TR 1064402 12-OCT-12 Venuk
1848 lcString = lcString + EDI_DETAIL_TAG + lcDelimiter + .ConvertRecordToText(tcWorkDetail, llDelimited, lcDelimiter)
1849 lnDtlCount = lnDtlCount + 1
1850 ENDSCAN
1851 ENDIF
1852 *=== TR 1034990 NSD 8/7/08
1853
1854 *-- Write lcString to flat file
1855 IF !EMPTY(lcString)
1856 .PrepareEmptyDateForGentran(@lcString)
1857 IF NOT .Tie_Setup
1858 lnHandle = pnHandle
1859 ENDIF
1860 IF FWRITE(lnHandle, lcString) = 0
1861 lRetVal = .F.
1862 ENDIF
1863 ENDIF
1864
1865 ENDSCAN
1866
1867 IF .Tie_Setup AND lnHandle > 0
1868 *-- Close previously opened file
1869 FCLOSE(lnHandle)
1870 IF llRetVal
1871 *--- TechRec 1060583 19-Apr-2012 jisingh ---
1872 *llRetVal = .CopyToFlatFileInBlock(lcTempFlatfile, lcOutBoundFlatFile)
1873 INSERT INTO __tcFlatFileList(TempFlatFile,OutBoundFlatFile) VALUES (lcTempFlatfile, lcOutBoundFlatFile)
1874 *=== TechRec 1060583 19-Apr-2012 jisingh ===
1875 ENDIF
1876 ENDIF
1877 ENDIF
1878
1879 *-- Close All Work Tables
1880 .TableClose(tcWorkVendor)
1881 .TableClose(tcWorkHeader)
1882 .TableClose(tcWorkDetail)
1883 .TableClose(lcControl)
1884 ENDWITH
1885
1886 SELECT(lnOldSelect)
1887 RETURN llRetVal
1888 ENDPROC
1889*=====================================================
1890 PROCEDURE UpdateMessage
1891 LOCAL llRetVal, lcmsg1, lcmsg2
1892
1893 *--- TR 1058987 7-Mar-2012 Goutam
1894 LOCAL lcmsg3
1895 STORE "" TO lcmsg1, lcmsg2, lcmsg3
1896 *=== TR 1058987 7-Mar-2012 Goutam
1897
1898 WITH THIS
1899 llRetVal= .T.
1900 .cMessage= IIF(!EMPTY(.cMessage), .cMessage + CRLF, "")
1901
1902 *--- TR 1058987 7-Mar-2012 Goutam
1903*!* lcmsg1 = IIF(.nTotalLoc>0, ALLTRIM(STR(.nTotalLoc)) + " Location(s) successfully processed." + CRLF ,"")
1904*!* lcmsg2 = IIF(.nDtlExport>0,ALLTRIM(STR(.nDtlExport)) + " SKU(s) moved from Interface table to flat file." + CRLF,"")
1905*!*
1906*!* .cMessage= .cMessage + (lcmsg1 + lcmsg2 )
1907
1908 IF .nFailedValidation > 0
1909 lcmsg1 = IIF(.nTotalLoc>0, ALLTRIM(STR(.nTotalLoc)) + " Record(s) processed." + CRLF ,"")
1910 lcmsg2 = IIF(.nFailedValidation>0, ALLTRIM(STR(.nFailedValidation)) + " Record(s) failed validation." + CRLF ,"")
1911 ELSE
1912 lcmsg1 = IIF(.nTotalLoc>0, ALLTRIM(STR(.nTotalLoc)) + " Record(s) successfully processed." + CRLF ,"")
1913 ENDIF
1914 IF (.nTotalLoc - .nFailedValidation) > 0
1915 lcmsg3 = IIF(.nDtlExport>0,ALLTRIM(STR(.nDtlExport)) + " SKU(s) moved from Interface table to flat file." + CRLF,"")
1916 ENDIF
1917
1918 .cMessage= .cMessage + (lcmsg1 + lcmsg2 + lcmsg3)
1919
1920 *--- TR 1058987 7-Mar-2012 Goutam
1921
1922 ENDWITH
1923 RETURN llRetVal
1924 ENDPROC
1925*=====================================================
1926 PROCEDURE RemoteScatterGather
1927 LPARAMETERS tcRemoteTable, tcLocalCursor, tcWhere
1928 LOCAL llRetVal, lnSelect, lcSQLString, lcTempCursor
1929
1930 llRetVal = False
1931 lnSelect = SELECT()
1932 lcTempCursor= GetUniqueFileName()
1933
1934 lcSQLString = "SELECT * FROM " + tcRemoteTable + ;
1935 IIF(NOT EMPTY(tcWhere), tcWhere, '')
1936
1937 IF .vSQLExec(lcSQLString, lcTempCursor)
1938 .ScatterGather(lcTempCursor, tcLocalCursor)
1939 llRetVal = True
1940 ENDIF
1941
1942 THIS.TableClose(lcTempCursor)
1943
1944 RETURN llRetVal
1945 ENDPROC
1946*=====================================================
1947 FUNCTION DropTempTables
1948 LOCAL llRetVal, lnSelect, lcSQLString
1949
1950 llRetVal = true
1951 lnSelect = SELECT()
1952
1953 WITH This
1954 IF NOT EMPTY(.cQ_846Dtl)
1955 lcSQLString = "DROP TABLE " + .cQ_846Dtl
1956 llRetVal = llRetVal AND .vSQLExec(lcSQLString)
1957 ENDIF
1958
1959 IF NOT EMPTY(.cQ_846Hdr)
1960 lcSQLString = "DROP TABLE " + .cQ_846Hdr
1961 llRetVal = llRetVal AND .vSQLExec(lcSQLString)
1962 ENDIF
1963
1964 .cQ_846Dtl = ''
1965 .cQ_846Hdr = ''
1966
1967 ENDWITH
1968
1969 SELECT (lnSelect)
1970 RETURN llRetVal
1971
1972 ENDFUNC
1973
1974*=====================================================
1975
1976 *--- TechRec 1060583 19-Apr-2012 jisingh ---
1977 FUNCTION UpdateCustomerPriceFlag
1978 LPARAMETERS tcParam1, tcParam2
1979 LOCAL llRetVal, lnSelect, lcSQLString
1980
1981 llRetVal = true
1982 lnSelect = SELECT()
1983
1984 WITH This
1985
1986 *--- TR 1064279 Goutam Removed existing Where condition and replaced with the new one.
1987 *--- TR 1065893 16-12-2012 VKK
1988 * Commented first update. do not set any exclusion flags
1989 *lcSQLString = " UPDATE cp " + ;
1990 " SET excl_846_sent = 'Y' " + ;
1991 " ,excl_846_date = " + SQLFormatTS(DATETIME()) + ;
1992 " ,last_mod = " + SQLFormatTS(DATETIME()) + ;
1993 " ,user_id = " + SQLFormatChar(goenv.envlogin.cUserName) + ;
1994 " FROM zzxcstpr cp " + ;
1995 " JOIN " + .cQ_846Dtl + " d " + ;
1996 " ON cp.division = d.division " + ;
1997 " AND cp.style = d.style " + ;
1998 " AND cp.color_code = d.color_code " + ;
1999 " AND cp.lbl_code = d.lbl_code " + ;
2000 " AND cp.dimension = d.dimension " + ;
2001 " JOIN " + .cInterFaceHeader + " h " + ;
2002 " ON h.location = d.location" + ;
2003 " AND h.vnd_qual = d.vnd_qual " + ;
2004 " AND h.vnd_id = d.vnd_id " + ;
2005 " AND h.our_qual = d.our_qual " + ;
2006 " AND h.our_id = d.our_id " + ;
2007 " AND h.customer = cp.customer " + ;
2008 " AND h.errs_flg_h <> 'Y' " + ; &&--- TechRec 1061287 02-May-2012 jisingh ===
2009 " WHERE Not (COALESCE(cp.remove_846,'') <> 'Y' OR (COALESCE(cp.remove_846,'') = 'Y' AND COALESCE(cp.remove_846_sent,'') <> 'Y')) " + ; && *--- TR 1064279 Goutam
2010 " and (COALESCE(cp.excl_846,'') <> 'Y' OR (COALESCE(cp.excl_846,'') = 'Y' AND COALESCE(cp.excl_846_sent,'') <> 'Y')) " + ; && *--- TR 1064279 Goutam
2011 " 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
2012
2013 *llRetVal = llRetVal AND .vSQLExec(lcSQLString)
2014
2015 *--- TR 1064279 7-Nov-2012 Goutam.
2016 *--- TR 1065893 16-12-2012 VKK
2017 * Replace " WHERE (COALESCE(cp.remove_846,'') <> 'Y' OR (COALESCE(cp.remove_846,'') = 'Y' AND COALESCE(cp.remove_846_sent,'') <> 'Y'))"
2018 * with " WHERE cp.excl_846 <> 'Y' AND cp.remove_846 = 'Y' AND cp.remove_846_sent <> 'Y' "
2019 *=== TR 1065893 16-12-2012 VKK
2020 lcSQLString = " UPDATE cp " + ;
2021 " SET remove_846_sent = 'Y' " + ;
2022 " ,remove_846_date = " + SQLFormatTS(DATETIME()) + ;
2023 " ,last_mod = " + SQLFormatTS(DATETIME()) + ;
2024 " ,user_id = " + SQLFormatChar(goenv.envlogin.cUserName) + ;
2025 " FROM zzxcstpr cp " + ;
2026 " JOIN " + .cQ_846Dtl + " d " + ;
2027 " ON cp.division = d.division " + ;
2028 " AND cp.style = d.style " + ;
2029 " AND cp.color_code = d.color_code " + ;
2030 " AND cp.lbl_code = d.lbl_code " + ;
2031 " AND cp.dimension = d.dimension " + ;
2032 " JOIN " + .cInterFaceHeader + " h " + ;
2033 " ON h.location = d.location" + ;
2034 " AND h.vnd_qual = d.vnd_qual " + ;
2035 " AND h.vnd_id = d.vnd_id " + ;
2036 " AND h.our_qual = d.our_qual " + ;
2037 " AND h.our_id = d.our_id " + ;
2038 " AND h.customer = cp.customer " + ;
2039 " AND h.errs_flg_h <> 'Y' " + ;
2040 " WHERE cp.excl_846 <> 'Y' AND cp.remove_846 = 'Y' AND cp.remove_846_sent <> 'Y' " && *--- TR 1065893 16-12-2012 VKK
2041
2042
2043
2044 llRetVal = llRetVal AND .vSQLExec(lcSQLString)
2045 *=== TR 1064279 7-Nov-2012 Goutam
2046
2047 ENDWITH
2048
2049 SELECT (lnSelect)
2050 RETURN llRetVal
2051 ENDFUNC
2052 *=== TechRec 1060583 19-Apr-2012 jisingh ===
2053
2054 *--- TechRec 1059983 31-May-2012 MANI. ---
2055 FUNCTION ValidateParamBro
2056
2057 LOCAL llRetVal, lnSelect,lcValue, lnIndex
2058
2059 llRetVal = true
2060 lnSelect = SELECT()
2061 lnIndex = 0
2062 WITH This
2063 *-- Availablity Determination bro validation
2064 IF llRetVal
2065 lnIndex = ASCAN(.aParamBROs,"AVAIL_OPTION",1,ALEN(.aParamBROs,1),1,9)
2066 IF lnIndex > 0
2067 .cAvail_Option = (ALLTRIM(.aParamBROs[lnIndex, 2]))
2068
2069 IF NOT EMPTY(.cAvail_Option) AND (LEN(.cAvail_Option) > 1 OR !INLIST(.cAvail_Option ,'Q','O','A'))
2070
2071 .cMessage= "Invalid Available Based on value."
2072 .oLog.LogEntry(.cMessage)
2073 llRetVal = False
2074 ENDIF
2075 .oLog.LogEntry("Available Based on :" + .cAvail_Option)
2076 ENDIF
2077
2078 ENDIF
2079
2080 *-- % of Available bro validation
2081 IF llRetVal
2082
2083 lnIndex = ASCAN(.aParamBROs,"PERCENT_AVAIL",1,ALEN(.aParamBROs,1),1,9)
2084 IF lnIndex > 0
2085 .nPercent_Avail = VAL((ALLTRIM(.aParamBROs[lnIndex, 2])))
2086
2087 IF .nPercent_Avail < 0 OR .nPercent_Avail > 100
2088
2089 .cMessage= "% of Available Value Should be 0 to 100"
2090 .oLog.LogEntry(.cMessage)
2091 llRetVal = False
2092 ENDIF
2093 IF .nPercent_Avail = 0
2094 .nPercent_Avail = 100
2095 ENDIF
2096 .oLog.LogEntry("% of Available :" + ALLTRIM(STR(.nPercent_Avail)))
2097 ENDIF
2098
2099 ENDIF
2100
2101 *--- TechRec 1062747 18-Jul-2012 MANI. ---
2102 *-- Show zero qty bro validation
2103 IF llRetVal
2104 lnIndex = ASCAN(.aParamBROs,"SHOW_ZERO_QTY",1,ALEN(.aParamBROs,1),1,9)
2105 IF lnIndex > 0
2106 .cShowZeroQty = (ALLTRIM(.aParamBROs[lnIndex, 2]))
2107
2108 IF NOT EMPTY(.cShowZeroQty) AND !INLIST(.cShowZeroQty,'Y','N')
2109 .cMessage= "Invalid Show Zero Qty value. Should be Y or N"
2110 .oLog.LogEntry(.cMessage)
2111 llRetVal = False
2112 ENDIF
2113 .oLog.LogEntry("Show Zero Qty :" + .cShowZeroQty)
2114 ENDIF
2115
2116 ENDIF
2117
2118 *-- MIN size qty avl bro Validation
2119 IF llRetVal
2120 lnIndex = ASCAN(.aParamBROs,"SFNSIZE_QTY",1,ALEN(.aParamBROs,1),1,9)
2121 IF lnIndex > 0
2122 .nMinSzQtyAvl = (ALLTRIM(.aParamBROs[lnIndex, 2]))
2123
2124 IF NOT EMPTY(.nMinSzQtyAvl) AND TYPE(.nMinSzQtyAvl) <> "N"
2125 .cMessage= "Invalid MIN size qty avl. Should be Numeric"
2126 .oLog.LogEntry(.cMessage)
2127 llRetVal = False
2128 ENDIF
2129 .oLog.LogEntry("MIN size qty avl :" + .nMinSzQtyAvl)
2130
2131 ENDIF
2132 ENDIF
2133
2134 *=== TechRec 1062747 18-Jul-2012 MANI. ===
2135
2136
2137 ENDWITH
2138
2139 SELECT (lnSelect)
2140 RETURN llRetVal
2141 ENDFUNC
2142 *=== TechRec 1059983 31-May-2012 MANI. ===
2143
2144*--- TechRec 1076524 10-Apr-2014 TSV---
2145 PROCEDURE GetAddressCursor
2146 LOCAL llRetVal, lnOldSelect
2147
2148 llRetVal = .t.
2149 lnOldSelect = SELECT()
2150
2151 WITH This
2152 .cQ_846Adr = Iif(Version(2)=2,"##","#") + SubStr(SYS(2015), 2)
2153
2154 lcSQL = " SELECT h.location, l.loc_name, l.address1, l.address2, l.city, " + ;
2155 " l.state, l.zipcode, l.country " + ;
2156 " INTO " + .cQ_846Adr + ;
2157 " FROM " + .cQ_846Hdr + " h " + ;
2158 " JOIN zzxlocar l " + ;
2159 " ON h.location = l.location " + ;
2160 " WHERE h.resv_loc_adr = 'Y' " + ;
2161 " AND l.loc_type = 'W' "
2162
2163 llRetVal = v_SQLExec(lcSQL)
2164
2165 ENDWITH
2166
2167 SELECT(lnOldSelect)
2168 RETURN llRetVal
2169 ENDPROC
2170*============================================================
2171*=== TechRec 1076524 10-Apr-2014 TSV===
2172
2173ENDDEFINE