· 7 years ago · Sep 25, 2018, 05:10 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 " WHERE " + lcSQLFilterString
1072
1073 .oLog.LogEntry("Retrieving the inventory data.")
1074
1075 llRetVal = llRetVal and v_sqlExecUncommitted(lcSqlString)
1076
1077 *--- TechRec 1066673 02-Oct-2013 GSternik --- Thuis is not needed anymore
1078
1079*=== TechRec 1066673 17-Jul-2013 GSternik ===
1080
1081 .oLog.LogEntry("Removing locations from header that are not used in the detail...") && 1066673
1082
1083 * Remove locations from header that are not used in the detail
1084 * THis will let us respect filter criteria
1085
1086 *--- TR 1058987 06-Mar-2012 Goutam
1087*!* lcSQLString = "DELETE FROM " + .cQ_846Hdr + ;
1088*!* " FROM " + .cQ_846Hdr + " h " + ;
1089*!* " LEFT JOIN " + lcTmpDtl + " d " + ;
1090*!* " ON h.location = d.location " + ;
1091*!* " AND h.locgroup = d.locgroup " + ;
1092*!* " WHERE d.location is null and d.locgroup is null"
1093
1094 lcSQLString = ;
1095 "DELETE h" +;
1096 " FROM " + .cQ_846Hdr + " h " + ;
1097 " LEFT JOIN " + lcTmpDtl + " d " + ;
1098 " ON h.CtrlPkey = d.CtrlPkey " + ;
1099 " WHERE d.CtrlPkey is null"
1100
1101 *=== TR 1058987 06-Mar-2012 Goutam
1102
1103 llRetVal = llRetVal and v_sqlexec(lcSQLString)
1104
1105 .oLog.LogEntry("Update the header location with locgroup.") && 1066673
1106 * We are exporting LOCGROUP in dtl so we should do the same for the hdr also.
1107 * 1. update the header location with locgroup if it has locgroup
1108 * 2. group the header based on location,vnd_id,our_id,vnd_qual,our_qual
1109 lcSQLString = " UPDATE " + .cQ_846Hdr + " SET location = locgroup WHERE locgroup > '' "
1110 llRetVal = llRetVal AND V_SQLEXEC( lcSQLString )
1111
1112 .oLog.LogEntry("Header grouping...") && 1066673
1113 *--- TR 1058987 06-Mar-2012 Goutam. Added Customer, catg_dest and template in select list as well as group by
1114 *--- TechRec 1060583 19-Apr-2012 jisingh Added use_threshold, threshold_qty & res_cprice ===
1115 lcSQLString = " SELECT " + ;
1116 " location " + ;
1117 " ,vnd_id " + ;
1118 " ,vnd_qual " + ;
1119 " ,our_qual " + ;
1120 " ,our_id " + ;
1121 " ,MAX(active_ok) active_ok " + ;
1122 " ,MAX(avl_only) avl_only " + ;
1123 " ,MAX(report_date) report_date " + ;
1124 " ,MAX(report_time) report_time " + ;
1125 " ,MAX(Supplier_num) Supplier_num "+;
1126 " ,Customer, catg_dest, template " + ;
1127 " ,MAX(use_threshold) use_threshold, MAX(threshold_qty) threshold_qty, MAX(res_cprice) res_cprice " + ;
1128 " ,MAX(resv_loc_adr) resv_loc_adr " + ; &&--- TechRec 1076524 10-Apr-2014 TSV added resv_loc_adr ===
1129 " FROM " + .cQ_846Hdr + " H " + ;
1130 " GROUP BY " + ;
1131 " location " + ;
1132 " ,vnd_id " + ;
1133 " ,vnd_qual " + ;
1134 " ,our_qual " + ;
1135 " ,our_id, Customer, catg_dest, template"
1136 lcTmpHdr = SQLTableFromQuery(lcSqlString)
1137 llRetVal = llRetVal AND !EMPTY(lcTmpHdr)
1138 lcSQLString = " DROP TABLE " + .cQ_846Hdr
1139 llRetVal = llRetVal AND V_SQLEXEC( lcSQLString )
1140 .cQ_846Hdr = lcTmpHdr
1141
1142 *=== Hdr grouping completed
1143 .oLog.LogEntry("Update the detail location with locgroup...") && 1066673
1144 lcSQLString = " UPDATE " + lcTmpDtl + " SET location = locgroup WHERE locgroup > '' "
1145 llRetVal = llRetVal AND V_SQLEXEC( lcSQLString )
1146
1147 .oLog.LogEntry("Detail grouping...") && 1066673
1148 *--- TR 1064755 09-Jan-13 SK Used MAX() instead of SUM() for qty fields.
1149 *--- TR 1058987 06-Mar-2012 Goutam. Added aux_sku, cust_sku in select list as well as group by
1150 lcSQLString = " SELECT location " + ;
1151 " , vnd_qual " + ;
1152 " , vnd_id " + ;
1153 " , our_qual " + ;
1154 " , our_id " + ;
1155 " , UPC" + ;
1156 " , Style" + ;
1157 " , Color_code " + ;
1158 " , lbl_code " + ;
1159 " , Dimension " + ;
1160 " , Size_desc" + ;
1161 " , SUM(onhand_qty) onhand_qty " +;
1162 " , SUM(wip_qty) wip_qty" +;
1163 " , SUM(open_qty) open_qty" +;
1164 " , SUM(pick_qty) pick_qty" +;
1165 " , MAX(avl_only) avl_only " +;
1166 " , division " +;
1167 " , MAX(style_name) style_name " +;
1168 " , MAX(color_name) color_name " +;
1169 " , MAX(color_desc) color_desc " +;
1170 " , MAX(prod_date1) prod_date1 " +;
1171 " , MAX(prod_date2) prod_date2 " +;
1172 " , MAX(prod_date3) prod_date3 " +;
1173 " , MAX(prod_date4) prod_date4 " +;
1174 " , Size_bk " +;
1175 " , SUM(OTS_QTY) OTS_QTY " +;
1176 " , aux_sku, cust_sku " + ;
1177 " , use_excl " + ; && TR 1064279 9-Nov-2012 Goutam
1178 " , CustStyle " + ; && TR 1064279 9-Nov-2012 Goutam
1179 " , cust_retail " + ; && TR 1064279 9-Nov-2012 Goutam
1180 " FROM " + lctmpdtl + ;
1181 " GROUP BY location " + ;
1182 " , vnd_qual " + ;
1183 " , vnd_id " + ;
1184 " , our_qual " + ;
1185 " , our_id " + ;
1186 " , division " +;
1187 " , Style" + ;
1188 " , Color_code " + ;
1189 " , lbl_code " + ;
1190 " , Dimension " + ;
1191 " , Size_bk " +;
1192 " , Size_desc" + ;
1193 " , UPC, aux_sku, cust_sku " + ;
1194 " , use_excl " + ; && TR 1064279 9-Nov-2012 Goutam
1195 " , CustStyle " + ; && TR 1064279 9-Nov-2012 Goutam
1196 " , cust_retail " && TR 1064279 9-Nov-2012 Goutam
1197
1198 *=== TR 1050639 04-Feb-2011 Partha ===
1199
1200 If llRetVal
1201 .cQ_846Dtl = SQLTableFromQuery(lcSqlString)
1202 EndIf
1203
1204 llRetVal = llRetVal AND !EMPTY(.cQ_846Dtl)
1205
1206 llRetVal = llRetVal AND V_SQLEXEC( " Select count(*) cnt from " + .cQ_846Dtl , lcTempCursor )
1207
1208 llRetval = llRetVal AND USED(lcTempCursor)
1209
1210 IF llRetVal
1211 SELECT(lcTempCursor)
1212 .nDtlExport = cnt
1213 ENDIF
1214
1215 * Remove locations from header that are not used in the detail
1216 * THis will let us respect filter criteria
1217
1218 *--- TR 1050639 07-Feb-2011 Partha ---
1219*!* taking this up, after that tmpdtl creation and before update of location with location group. to based on that tmpdtl
1220*!* llRetVal = llRetVal and v_sqlexec("DELETE FROM " + This.cQ_846Hdr + " WHERE location NOT IN (SELECT DISTINCT location FROM " + .cQ_846Dtl + ")" )
1221 *=== TR 1050639 07-Feb-2011 Partha ===
1222
1223 llRetVal = llRetVal AND V_SQLEXEC( " Select count(*) cnt from " + .cQ_846Hdr , lcTempCursor )
1224
1225 llRetval = llRetVal AND USED(lcTempCursor)
1226
1227 IF llRetVal
1228 SELECT(lcTempCursor)
1229 .nTotalLoc = cnt
1230
1231 IF .nTotalLoc = 0
1232 .oLog.LogEntry("No records to process.")
1233 ENDIF
1234 ENDIF
1235
1236 *--- TechRec 1066673 22-Oct-2013 GSternik ---
1237 *--- TR 1050639 04-Feb-2011 Partha ---
1238 *lcSQLString = " DROP TABLE " + lctmpdtl
1239 *V_SQLEXEC(lcSQLString)
1240 *=== TR 1050639 04-Feb-2011 Partha ===
1241 =v_SqlExec("begin try drop table "+ lcTmpDtl +" end try begin catch end catch")
1242 *=== TechRec 1066673 22-Oct-2013 GSternik ===
1243
1244 ENDIF
1245
1246 .TableClose(lcTempCursor)
1247 ENDWITH
1248
1249 RETURN llRetVal
1250 ENDFUNC
1251
1252*=====================================================
1253 PROCEDURE DeletePreviousTransaction
1254
1255 LOCAL llRetVal, lcSQLString
1256
1257 llRetVal= .T.
1258 WITH THIS
1259 .InitThermo(1)
1260 .UpdateThermoCaption("Deleting previous 846(o) transactions...")
1261 .oLog.LogEntry("Deleting previous 846 ")
1262
1263
1264 lcSQLString = " DELETE FROM " + .cInterFaceHeader
1265 llRetVal = llRetVal AND .vSQLExec(lcSQLString)
1266
1267 lcSQLString = " DELETE FROM " + .cInterFaceDetail
1268 llRetVal = llRetVal AND .vSQLExec(lcSQLString)
1269 .AdvanceThermo(1)
1270
1271 ENDWITH
1272 IF !llRetVal
1273 .oLog.LogEntry("Deleting previous 846 - failed")
1274 ENDIF
1275
1276 RETURN llRetVal
1277 ENDPROC
1278*=====================================================
1279 PROCEDURE ValidateTransaction
1280
1281 LOCAL llRetVal, lnSelect
1282
1283 *--- TR 1058987 7-Mar-2012 Goutam
1284 LOCAL lcHeader, lcDetail, lcSql
1285 lcHeader = THIS.cInterFaceHeader
1286 lcDetail = THIS.cInterFaceDetail
1287 *=== TR 1058987 7-Mar-2012 Goutam
1288
1289 llRetVal = True
1290 lnSelect = SELECT()
1291
1292 WITH THIS
1293 *--- TR 1058987 7-Mar-2012 Goutam
1294 *--- 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
1295 lcSql = "Update d set errs_flg_d = 'Y', errs_msg_d = 'Bonton/Amazon SKU for Style: ' + RTRIM(d.Style) + ', Color: ' + RTRIM(d.Color_code) + " + ;
1296 "', Label: ' + RTRIM(lbl_code) + ', Size : ' + RTRIM(d.size_desc) + ' is not found' from " + lcHeader + " h join " + lcDetail + " d " + ;
1297 " on d.fkey = h.pkey where d.cust_sku = '' and (h.template = 'BONTON V.4010' or h.template = 'AMAZON.COM 4010')"
1298
1299 llRetVal = llRetVal AND v_SqlExec(lcSql)
1300
1301 *--- 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
1302 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) + " + ;
1303 "', Label: ' + RTRIM(lbl_code) + ', Size : ' + RTRIM(d.size_desc) + ' is not found' from " + lcHeader + " h join " + lcDetail + " d " + ;
1304 " on d.fkey = h.pkey where d.aux_sku = '' and (h.template = 'BONTON V.4010' or h.template = 'AMAZON.COM 4010')"
1305
1306 llRetVal = llRetVal AND v_SqlExec(lcSql)
1307
1308 lcSql = "Update h set errs_flg_h = 'Y', errs_msg_h = 'Errors in detail' from " + lcHeader + " h join " + lcDetail + " d " + ;
1309 " on d.fkey = h.pkey where d.errs_flg_d = 'Y'"
1310
1311 llRetVal = llRetVal AND v_SqlExec(lcSql)
1312
1313 *=== TR 1058987 7-Mar-2012 Goutam
1314 ENDWITH
1315
1316 SELECT (lnSelect)
1317 RETURN llRetVal
1318 ENDPROC
1319*=====================================================
1320 FUNCTION PopulateTransaction
1321
1322 LOCAL llRetVal, lnSelect, lcSQLString, lcTempTable, lcCursor ,ltLast_Mod,lcUser_id
1323
1324 ltLast_Mod = DATETIME()
1325 lcUser_id = goEnv.SV("cUser")
1326
1327 lnSelect = SELECT()
1328 llRetVal = True
1329
1330 WITH THIS
1331 THIS.oLog.LogMajorStage("Retrieving Location(s)")
1332 *-- Populating Header
1333 WITH THIS.oSB
1334 .SB_Reset()
1335 .cSB_Target = THIS.cInterFaceHeader
1336 .cSB_Source = THIS.cQ_846Hdr
1337 .cSB_SourceAlias = "loc"
1338 .cSB_WHEREString = " "
1339
1340 .SB_SetLiteralFieldValue("location","loc.location")
1341 .SB_SetLiteralFieldValue("errs_flg_h","''")
1342 .SB_SetLiteralFieldValue("errs_msg_h","''")
1343 .SB_SetLiteralFieldValue("notes","''")
1344 .SB_SetLiteralFieldValue("last_mod",SQLFormatTS(ltLast_Mod))
1345 .SB_SetLiteralFieldValue("user_id",SQLFormatChar(lcUser_id))
1346 *--- TR 1040516 30-Jul-2009 Surinder Singh ---
1347 .SB_SetLiteralFieldValue("location","loc.supplier_num")
1348 *=== TR 1040516 30-Jul-2009 Surinder Singh ===
1349 *--- TR 1058987 10-Mar-2012 Goutam. Added loc.customer+loc.catg_dest and made Char(40) to Char(52)
1350 .SB_KeySetAddKeySpec("pkey","Char(52)","loc.location+loc.vnd_qual+loc.vnd_id+loc.our_qual+loc.our_id+loc.customer+loc.catg_dest ")
1351
1352 llRetVal = .SB_GenerateSQLInsertString()
1353 ENDWITH
1354
1355 llRetVal = llRetVal AND .vSQLExec(THIS.oSB.cSQLString)
1356 .AdvanceThermo(.5)
1357
1358 *-- Populating Detail
1359 WITH THIS.oSB
1360 .SB_Reset()
1361 .cSB_Target = THIS.cInterFaceDetail
1362 .cSB_Source = THIS.cQ_846Dtl
1363 .cSB_SourceAlias = "d"
1364
1365 *--- TechRec 1060583 19-Apr-2012 jisingh Added LEFT JOIN to zzxcstpr ===
1366 .cSB_FROMExpandedString = " JOIN " + THIS.cInterFaceHeader + " h " + ;
1367 " ON h.location = d.location" + ;
1368 " AND h.vnd_qual = d.vnd_qual " + ;
1369 " AND h.vnd_id = d.vnd_id " + ;
1370 " AND h.our_qual = d.our_qual " + ;
1371 " AND h.our_id = d.our_id " + ;
1372 " LEFT JOIN zzxcstpr cp " + ;
1373 " ON cp.customer = h.customer " + ;
1374 " AND cp.division = d.division " + ;
1375 " AND cp.style = d.style " + ;
1376 " AND cp.color_code = d.color_code " + ;
1377 " AND cp.lbl_code = d.lbl_code " + ;
1378 " AND cp.dimension = d.dimension "
1379
1380 *--- TechRec 1060583 19-Apr-2012 jisingh ---
1381 *.cSB_WHEREString = " "
1382
1383 *--- TR 1064279 7-Nov-2012 Goutam. Changed excl_846 to Remove_846, excl_846_sent to remove_846_sent
1384 *--- TR 1065893 16-12-2012 VKK
1385 * this is for use_excl = N
1386 *.cSB_WHEREString = " COALESCE(cp.remove_846,'') <> 'Y' OR (COALESCE(cp.remove_846,'') = 'Y' AND COALESCE(cp.remove_846_sent,'') <> 'Y') "
1387 .cSB_WHEREString = " d.use_excl = 'N' or d.use_excl = '' "
1388 *=== TR 1065893 16-12-2012 VKK
1389 *=== TechRec 1060583 19-Apr-2012 jisingh ===
1390 .SB_SetLiteralFieldValue("fkey","h.pkey")
1391
1392 .SB_SetLiteralFieldValue("errs_flg_d","''")
1393 .SB_SetLiteralFieldValue("errs_msg_d","''")
1394 .SB_SetLiteralFieldValue("notes","''")
1395 .SB_SetLiteralFieldValue("last_mod",SQLFormatTS(ltLast_Mod))
1396 .SB_SetLiteralFieldValue("user_id",SQLFormatChar(lcUser_id))
1397
1398 *--- TechRec 1060583 19-Apr-2012 jisingh ---
1399 .SB_SetLiteralFieldValue("cust_price", "CASE WHEN h.res_cprice = 'Y' THEN COALESCE(cp.price,0) ELSE 0 END")
1400
1401 *--- TR 1064279 7-Nov-2012 Goutam.
1402 .SB_SetLiteralFieldValue("cust_retail", "CASE WHEN d.cust_retail = 0 THEN COALESCE(cp.retail_price,0) ELSE d.cust_retail END")
1403 *.SB_SetLiteralFieldValue("discontinue_date", SQLFormatTS(DATE()))
1404 *=== TR 1064279 7-Nov-2012 Goutam.
1405
1406 *--- TR 1064279 7-Nov-2012 Goutam. Changed excl_846 to remove_846
1407*!* .SB_SetLiteralFieldValue("ots_qty", "COALESCE((CASE WHEN COALESCE(cp.remove_846,'') = 'Y' THEN 0 ELSE CASE WHEN h.use_threshold = 'Y' THEN " + ;
1408*!* "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)")
1409 *=== TechRec 1060583 19-Apr-2012 jisingh ===
1410
1411 .SB_SetLiteralFieldValue("ots_qty", "d.ots_qty")
1412
1413 *--- TR 1050639 04-Feb-2011 Partha ---
1414*!* .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)")
1415
1416 *--- TR 1058987 10-Mar-2012 Goutam. Added cust_sku+aux_sku
1417 *--- TR 1066673 23-Feb-2013 Bnarayanan ---
1418 *.SB_KeySetAddKeySpec("pkey","Char(250)","STR(h.pkey)+d.location+d.vnd_qual+d.vnd_id+d.our_qual+ ;
1419 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 ")
1420
1421 .SB_KeySetAddKeySpec("pkey","Int","h.pkey")
1422 .SB_KeySetAddKeySpec("pkey1","CHAR(6)","d.location")
1423 .SB_KeySetAddKeySpec("pkey2","CHAR(2)","d.vnd_qual")
1424 .SB_KeySetAddKeySpec("pkey3","CHAR(15)","d.vnd_id")
1425
1426 .SB_KeySetAddKeySpec("pkey4","CHAR(2)","d.our_qual")
1427 .SB_KeySetAddKeySpec("pkey5","CHAR(15)","d.our_id")
1428 .SB_KeySetAddKeySpec("pkey6","CHAR(3)","d.division")
1429 .SB_KeySetAddKeySpec("pkey7","VARCHAR(50)","d.style")
1430
1431 .SB_KeySetAddKeySpec("pkey8","VARCHAR(50)","d.color_code")
1432 .SB_KeySetAddKeySpec("pkey9","CHAR(7)","d.lbl_code")
1433 .SB_KeySetAddKeySpec("pkey10","CHAR(5)","d.dimension")
1434 .SB_KeySetAddKeySpec("pkey11","Int","d.Size_bk")
1435
1436 .SB_KeySetAddKeySpec("pkey12","CHAR(10)","d.Size_desc")
1437 .SB_KeySetAddKeySpec("pkey13","CHAR(12)","d.UPC")
1438 .SB_KeySetAddKeySpec("pkey14","CHAR(27)","cust_sku")
1439 .SB_KeySetAddKeySpec("pkey15","VARCHAR(50)","aux_sku")
1440
1441 *=== TR 1066673 23-Feb-2013 BNarayanan ===
1442 *=== TR 1050639 04-Feb-2011 Partha ===
1443
1444 llRetVal = .SB_GenerateSQLInsertString()
1445
1446
1447 ENDWITH
1448
1449
1450
1451 llRetVal = llRetVal AND .vSQLExec(THIS.oSB.cSQLString)
1452
1453 .AdvanceThermo(1)
1454
1455 *--- TR 1064279 7-Nov-2012 Goutam. New set above insertion with different set of condition.
1456 *-- Populating Detail ONCE MORE
1457 WITH THIS.oSB
1458 .SB_Reset()
1459 .cSB_Target = THIS.cInterFaceDetail
1460 .cSB_Source = THIS.cQ_846Dtl
1461 .cSB_SourceAlias = "d"
1462
1463 .cSB_FROMExpandedString = " JOIN " + THIS.cInterFaceHeader + " h " + ;
1464 " ON h.location = d.location" + ;
1465 " AND h.vnd_qual = d.vnd_qual " + ;
1466 " AND h.vnd_id = d.vnd_id " + ;
1467 " AND h.our_qual = d.our_qual " + ;
1468 " AND h.our_id = d.our_id " + ;
1469 " JOIN zzxcstpr cp " + ;
1470 " ON cp.customer = h.customer " + ;
1471 " AND cp.division = d.division " + ;
1472 " AND cp.style = d.style " + ;
1473 " AND cp.color_code = d.color_code " + ;
1474 " AND cp.lbl_code = d.lbl_code " + ;
1475 " AND cp.dimension = d.dimension "
1476
1477 *--- TR 1065893 16-12-2012 VKK
1478 *.cSB_WHEREString = "Not (COALESCE(cp.remove_846,'') <> 'Y' OR (COALESCE(cp.remove_846,'') = 'Y' AND COALESCE(cp.remove_846_sent,'') <> 'Y')) " + ;
1479 * " and (COALESCE(cp.excl_846,'') <> 'Y' OR (COALESCE(cp.excl_846,'') = 'Y' AND COALESCE(cp.excl_846_sent,'') <> 'Y')) " + ;
1480 * " 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 "
1481
1482 .cSB_WHEREString = " d.use_excl <> 'N' and d.use_excl > '' " + ; &&Only use these setup records
1483 " AND COALESCE(cp.excl_846,'') <> 'Y' " + ; &&Exclude anything not meant to be included
1484 " 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
1485 " AND NOT (cp.remove_846 = 'Y' AND cp.remove_846_sent = 'Y') " && do not send items that are removed and marked remove sent
1486 *=== TR 1065893 16-12-2012 VKK
1487
1488 .SB_SetLiteralFieldValue("fkey","h.pkey")
1489
1490 .SB_SetLiteralFieldValue("errs_flg_d","''")
1491 .SB_SetLiteralFieldValue("errs_msg_d","''")
1492 .SB_SetLiteralFieldValue("notes","''")
1493 .SB_SetLiteralFieldValue("last_mod",SQLFormatTS(ltLast_Mod))
1494 .SB_SetLiteralFieldValue("user_id",SQLFormatChar(lcUser_id))
1495
1496 .SB_SetLiteralFieldValue("cust_price", "CASE WHEN h.res_cprice = 'Y' THEN COALESCE(cp.price,0) ELSE 0 END")
1497 *--- TR 1065893 16-12-2012 VKK
1498*!* .SB_SetLiteralFieldValue("cust_retail", "CASE WHEN d.cust_retail = 0 THEN COALESCE(cp.retail_price,0) ELSE d.cust_retail END")
1499*!* .SB_SetLiteralFieldValue("discontinue_date", "CASE WHEN d.onhand_qty = 0 THEN '12/31/2039' ELSE '01/01/1900' END")
1500
1501*!* .SB_SetLiteralFieldValue("ots_qty", "COALESCE((CASE WHEN COALESCE(cp.remove_846,'') = 'Y' THEN 0 ELSE CASE WHEN h.use_threshold = 'Y' THEN " + ;
1502*!* "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)")
1503
1504 .SB_SetLiteralFieldValue("cust_retail", "CASE WHEN d.custstyle = '' THEN COALESCE(cp.retail_price,0) ELSE d.cust_retail END")
1505
1506
1507
1508 lcOtsThreshold = " dbo.bcfn_MAX(0,d.ots_qty - CASE WHEN h.use_threshold = 'Y' THEN h.threshold_qty ELSE 0 END)"
1509
1510
1511
1512 .SB_SetLiteralFieldValue("discontinue_date", "CASE WHEN cp.remove_846 = 'Y' THEN " + SQLFormatTS(DATE()) + ;
1513 " WHEN "+lcOtsThreshold+" <= 0 THEN '12/31/2039' ELSE '01/01/1900' END")
1514
1515 .SB_SetLiteralFieldValue("ots_qty", "COALESCE((CASE WHEN COALESCE(cp.remove_846,'') = 'Y' THEN 0 ELSE " + lcOtsThreshold + " END),0)")
1516 *=== TR 1065893 16-12-2012 VKK
1517 .SB_KeySetAddKeySpec("pkey","Char(250)","STR(h.pkey)+d.location+d.vnd_qual+d.vnd_id+d.our_qual+ ;
1518 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 ")
1519
1520 llRetVal = .SB_GenerateSQLInsertString()
1521
1522 ENDWITH
1523
1524 llRetVal = llRetVal AND .vSQLExec(THIS.oSB.cSQLString)
1525 .AdvanceThermo(1)
1526 *=== TR 1064279 7-Nov-2012 Goutam.
1527
1528 *--- TechRec 1060583 19-Apr-2012 jisingh ---
1529 llRetVal = llRetVal AND .vSQLExec(" DELETE h FROM " + .cInterFaceHeader + " h " + ;
1530 " LEFT JOIN " + .cInterFaceDetail + " d " + ;
1531 " ON h.pkey = d.fkey " + ;
1532 " WHERE d.pkey IS NULL ")
1533
1534 llRetVal = llRetVal AND .vSQLExec(" SELECT COUNT(*) ct FROM " + .cInterFaceHeader, "tcCount")
1535
1536 IF llRetVal AND USED("tcCount")
1537 .nTotalLoc = tcCount.ct
1538 .TableClose("tcCount")
1539 ENDIF
1540 *=== TechRec 1060583 19-Apr-2012 jisingh ===
1541 ENDWITH
1542
1543 SELECT (lnSelect)
1544 RETURN llRetVal
1545 ENDFUNC
1546*=====================================================
1547 PROCEDURE UnLoadInterfaceToFlatFile
1548 LPARAMETERS tcProcess, tcFlatFile, tcInterfaceHeader, tcInterfaceDetail, tcAddress
1549 &&--- TechRec 1076524 24-Apr-2014 TSV added tcAddress ===
1550 LOCAL llRetVal, lcSQLString, lnHandle
1551
1552 lnHandle = 0
1553 llRetVal= True
1554
1555 WITH THIS
1556 *--Get Metadata for vendor table
1557 IF .GetTranslationDefinition(EDI_ALL_TRANSACTION, "tcEmVend")
1558
1559 IF .GetTranslationDefinition(tcProcess, "tcEmData")
1560 *-- Creating Metadata work tables
1561 .CreateMetaDataWorkTable("tcEmVend", "VENDOR", "tcoibVND")
1562 .CreateMetaDataWorkTable("tcEmData", "HEADER", "tcoibHDR")
1563 .CreateMetaDataWorkTable("tcEmData", "DETAIL", "tcoibDTL")
1564
1565 *--- TechRec 1076524 24-Apr-2014 TSV---
1566 .CreateMetaDataWorkTable("tcEmData", "ADDRESS", "tcoibADR")
1567 *=== TechRec 1076524 24-Apr-2014 TSV===
1568
1569 *--- TR 1034990 NSD 8/8/08
1570 * Create index for SEEK/SCAN WHILE
1571 SELECT tcoibDTL
1572 *--- TR 1058987 17-4-2012 VKK
1573 *INDEX ON location TAG location
1574 INDEX ON fkey TAG fkey
1575 *=== TR 1058987 17-4-2012 VKK
1576 *=== TR 1034990 NSD 8/8/08
1577
1578 *--- TechRec 1076524 24-Apr-2014 TSV added ===
1579 SELECT tcoibADR
1580 INDEX ON location TAG location
1581 *=== TechRec 1076524 24-Apr-2014 TSV added ===
1582
1583 *--- TechRec 1058987 18-Apr-2012 jisingh ---
1584 SELECT tcoibHDR
1585 INDEX ON location + vnd_qual + vnd_id TAG locvnd
1586 *=== TechRec 1058987 18-Apr-2012 jisingh ===
1587
1588 *--- TR 1061446 25-May-2012 Goutam. Moving down. Should try to create flat file if valid record esists.
1589*!* IF NOT .Tie_Setup
1590*!* lcTempFlatfile= STRTRAN(UPPER(tcFlatFile), ".DAT", ".TMP")
1591*!* DELETE FILE (lcTempFlatfile)
1592*!* lnHandle = .OpenFlatFile(lcTempFlatfile, True)
1593*!* ENDIF
1594 *=== TR 1061446 25-May-2012 Goutam
1595
1596 *--- TR 1061446 25-May-2012 Goutam. Should go here always
1597 *IF .Tie_Setup OR lnHandle> 0
1598 IF true
1599 *=== TR 1061446 25-May-2012 Goutam
1600
1601 *-- Populating data to metadata work detail tables from interface tables
1602 *--- TR 1058987 7-Mar-2012 Goutam. Added third parameter in following RemoteScatterGather
1603 .RemoteScatterGather(tcInterfaceHeader,"tcoibHDR", " where errs_flg_h <> 'Y'")
1604 .RemoteScatterGather(tcInterfaceDetail,"tcoibDTL", " where errs_flg_d <> 'Y'")
1605
1606 *--- TechRec 1076524 24-Apr-2014 TSV---
1607 .RemoteScatterGather(tcAddress, "tcoibADR")
1608 *=== TechRec 1076524 24-Apr-2014 TSV===
1609
1610 *--- TR 1058987 7-Mar-2012 Goutam
1611 .nFailedValidation = .nTotalLoc - RECCOUNT("tcoibHDR")
1612 .nDtlExport = RECCOUNT("tcoibDTL")
1613 *=== TR 1058987 7-Mar-2012 Goutam
1614
1615 *-- Unloading to Flat file
1616 *--- TR 1058987 27-Mar-2012 Goutam
1617 IF (.nTotalLoc - .nFailedValidation) > 0
1618 *=== TR 1058987 27-Mar-2012 Goutam
1619
1620 *--- TR 1061446 25-May-2012 Goutam. Moved here from up. Should try to create flat file if valid record esists.
1621 IF NOT .Tie_Setup
1622 lcTempFlatfile= STRTRAN(UPPER(tcFlatFile), ".DAT", ".TMP")
1623 DELETE FILE (lcTempFlatfile)
1624 lnHandle = .OpenFlatFile(lcTempFlatfile, True)
1625 ENDIF
1626 *=== TR 1061446 25-May-2012 Goutam
1627 &&--- TechRec 1076524 24-Apr-2014 TSV added "tcoibADR" ===
1628 llRetVal = .CreateFlatfileFromMetadata(tcProcess, .cControlReference , "tcoibVND", ;
1629 "tcoibHDR", "tcoibDTL", lnHandle, "tcoibADR")
1630
1631 *--- TR 1058987 27-Mar-2012 Goutam
1632 ENDIF
1633 *=== TR 1058987 27-Mar-2012 Goutam
1634
1635 IF lnHandle > 0
1636 FCLOSE(lnHandle)
1637 ENDIF
1638
1639 IF !llRetVal
1640 .oLog.LogEntry("Creating Flat file from Metadata - failed")
1641 ENDIF
1642 ENDIF
1643 ENDIF
1644 ENDIF
1645 .TableClose('tcoibHDR')
1646 .TableClose('tcoibDTL')
1647 ENDWITH
1648 IF !llRetVal
1649 .oLog.LogEntry("Unloading to flat file - failed")
1650 ENDIF
1651
1652 RETURN llRetVal
1653 ENDPROC
1654*=====================================================
1655 PROCEDURE CreateFlatfileFromMetadata
1656 LPARAMETER tcProcess, tcWorkControl, tcWorkVendor, tcWorkHeader, tcWorkDetail, pnHandle, tcWorkAddress
1657 &&--- TechRec 1076524 24-Apr-2014 TSV added tcWorkAddress ===
1658
1659 &&--- TechRec 1076524 24-Apr-2014 TSV added llAdrLine ===
1660 LOCAL llRetVal, lnOldSelect, lnCurrentPkey, lcString, llAdrLine, ;
1661 llDelimited, lcDelimiter, ;
1662 lcPreviousLocation, lcControl, lnDtlCount, ;
1663 lcLocation, lcOutBoundFlatFile, lcTempFlatfile, lnHandle, lcOrder
1664
1665 *--- TechRec 1058987 18-Apr-2012 jisingh ---
1666 LOCAL lcVndQual, lcVndId, lcPreviousVndQual, lcPreviousVndId
1667
1668 lcVndQual = ""
1669 lcVndId = ""
1670 lcPreviousVndQual = ""
1671 lcPreviousVndId = ""
1672 *=== TechRec 1058987 18-Apr-2012 jisingh ===
1673
1674 llRetVal= True
1675
1676 lcPreviousLocation = ""
1677 lcLocation = ""
1678 lnHandle = 0
1679 lcDelimiter = ""
1680
1681 lnOldSelect= SELECT()
1682
1683 WITH THIS
1684 *--- TechRec 1060583 19-Apr-2012 jisingh ---
1685 .TableClose("__tcFlatFileList")
1686 CREATE CURSOR __tcFlatFileList(TempFlatFile MEMO, OutBoundFlatFile MEMO)
1687 *=== TechRec 1060583 19-Apr-2012 jisingh ===
1688
1689 *-- Control Reference
1690 lcControl = GetUniqueFileName()
1691
1692 lcSQLString = " SELECT pcr.* " + ;
1693 " FROM " + tcWorkControl + " pcr "
1694
1695 llRetVal = llRetVal AND .vSQLExec(lcSQLString, lcControl)
1696
1697 *--- TR 1034990 NSD 8/8/08
1698 *--- TechRec 1058987 18-Apr-2012 jisingh ---
1699 *INDEX ON ALLTRIM(location) TAG location
1700 INDEX ON ALLTRIM(location) + ALLTRIM(vnd_qual) + ALLTRIM(vnd_id) TAG locvnd
1701 *=== TechRec 1058987 18-Apr-2012 jisingh ===
1702 *=== TR 1034990 NSD 8/8/08
1703
1704 IF llRetVal
1705 SELECT (tcWorkHeader)
1706 SCAN
1707 * Reset lcString & Counters
1708 lcString = ""
1709 lnDtlCount = 1
1710
1711 lcLocation = Location
1712 *--- TR 1058987 17-4-2012 VKK
1713 lnPkey = Pkey
1714 *=== TR 1058987 17-4-2012 VKK
1715
1716 *--- TechRec 1058987 18-Apr-2012 jisingh ---
1717 lcVndQual = vnd_qual
1718 lcVndId = vnd_id
1719 *=== TechRec 1058987 18-Apr-2012 jisingh ===
1720
1721
1722 *--- TR 1067890 03-21-2013 RKI ---*
1723 llLocVndChanged = NOT (lcLocation + lcVndQual + lcVndId == lcPreviousLocation + lcPreviousVndQual + lcPreviousVndId)
1724 *=== TR 1067890 03-21-2013 RKI ===*
1725
1726 SELECT (lcControl)
1727
1728 *--- TR 1034990 NSD 8/8/08
1729 *LOCATE FOR location == lcLocation
1730 *IF FOUND()
1731 *--- TechRec 1058987 18-Apr-2012 jisingh ---
1732 *IF SEEK(ALLTRIM(lcLocation),lcControl,"location")
1733 IF SEEK(ALLTRIM(lcLocation)+ALLTRIM(lcVndQual)+ALLTRIM(lcVndId),lcControl,"locvnd")
1734 *=== TechRec 1058987 18-Apr-2012 jisingh ===
1735 *=== TR 1034990 NSD 8/8/08
1736
1737 llDelimited = (EDI_Format = 'D')
1738 lcDelimiter = IIF(llDelimited, EDI_Delim, "")
1739 ELSE
1740
1741 *--- TR 1034990 NSD 8/8/08
1742 *LOCATE FOR location == ""
1743 *IF FOUND()
1744 *--- TechRec 1059983 14-Jun-2012 MANI. To avoid index tag not found issue ---
1745 *IF SEEK("",lcControl,"fkey")
1746 IF SEEK("",lcControl,"locvnd")
1747 *=== TechRec 1059983 14-Jun-2012 MANI. ===
1748 *=== TR 1034990 NSD 8/8/08
1749
1750 llDelimited = (EDI_Format = 'D')
1751 lcDelimiter = IIF(llDelimited, EDI_Delim, "")
1752 ENDIF
1753
1754 ENDIF
1755
1756 *--- TechRec 1076524 24-Apr-2014 TSV---
1757 llAdrLine = False
1758 IF llLocVndChanged AND resv_loc_adr = 'Y'
1759 SELECT (tcWorkAddress)
1760
1761 IF SEEK(lcLocation, tcWorkAddress, "location")
1762 llAdrLine = True
1763 ELSE
1764 llAdrLine = False
1765 ENDIF
1766
1767 ENDIF
1768 *=== TechRec 1076524 24-Apr-2014 TSV===
1769
1770 *--- TechRec 1058987 18-Apr-2012 jisingh ---
1771 *IF NOT ( lcLocation == lcPreviousLocation )
1772 IF NOT (lcLocation + lcVndQual + lcVndId == lcPreviousLocation + lcPreviousVndQual + lcPreviousVndId)
1773 *=== TechRec 1058987 18-Apr-2012 jisingh ===
1774
1775 SELECT (lcControl)
1776 lcPreviousLocation = Location
1777 *--- TechRec 1058987 18-Apr-2012 jisingh ---
1778 lcPreviousVndQual = vnd_qual
1779 lcPreviousVndId = vnd_id
1780 *=== TechRec 1058987 18-Apr-2012 jisingh ===
1781
1782 SELECT (tcWorkVendor)
1783 APPEND BLANK
1784 REPLACE vnd_tran WITH tcProcess, ;
1785 vnd_id WITH EVALUATE(lcControl + ".vnd_id"), ;
1786 vnd_vers WITH EVALUATE(lcControl + ".vnd_vers") IN (tcWorkVendor)
1787
1788 *-- Create new file name and handle
1789 IF .Tie_Setup
1790 IF lnHandle > 0
1791 FCLOSE (lnHandle)
1792 lcTempFlatfile = STRTRAN(UPPER(lcOutBoundFlatFile), ".DAT", ".TMP")
1793 *--- TechRec 1060583 19-Apr-2012 jisingh ---
1794 *llRetVal = llRetVal and .CopyToFlatFileInBlock(lcTempFlatfile, lcOutBoundFlatFile)
1795 INSERT INTO __tcFlatFileList(TempFlatFile,OutBoundFlatFile) VALUES (lcTempFlatfile, lcOutBoundFlatFile)
1796 *=== TechRec 1060583 19-Apr-2012 jisingh ===
1797 ENDIF
1798
1799 *-- New flat file name
1800 lcOutBoundFlatFile = lcEDIPath + ALLTRIM(EVALUATE(tcWorkVendor + ".vnd_id")) + EDI_OIB_FILENAME
1801 lcTempFlatfile = STRTRAN(UPPER(lcOutBoundFlatFile), ".DAT", ".TMP")
1802 DELETE FILE (lcTempFlatfile)
1803 lnHandle = .OpenFlatFile(lcTempFlatfile, .T.)
1804 IF lnHandle <= 0
1805 LOOP
1806 ENDIF
1807 ENDIF
1808 ENDIF
1809
1810 *---- TR 1064402 12-OCT-12 Venuk
1811 *-- Output Vendor string "VND"
1812*!* lcString = lcString + EDI_VENDOR_TAG + lcDelimiter + .ConvertRecordToText(tcWorkVendor, llDelimited, lcDelimiter)
1813
1814*!* SELECT (tcWorkHeader)
1815*!* lcString = lcString + EDI_HEADER_TAG + lcDelimiter + .ConvertRecordToText(tcWorkHeader, llDelimited, lcDelimiter)
1816 *=== TR 1064402 12-OCT-12 Venuk
1817 *-- Output Detail string "DTL"
1818 SELECT (tcWorkDetail)
1819
1820 *--- TR 1034990 NSD 8/7/08
1821 * remove 10,000 detail restriction, do a seek/scan while instead of scan for.
1822 *SCAN FOR Location = lcLocation AND lnDtlCount < 10000
1823 *--- TR 1058987 17-4-2012 VKK replaced location seek with pkey/fkey seek
1824 IF SEEK(lnPkey,tcWorkDetail,"fkey")
1825 lcOrder = SET("ORDER")
1826 SET ORDER TO fkey &&*--- TR 1058987 17-4-2012 VKK changed to fkey
1827 SCAN WHILE fkey == lnPkey && *--- TR 1058987 17-4-2012 VKK changed to fkey fromlocation
1828 *---- TR 1064402 12-OCT-12 Venuk
1829 IF lnDtlCount = 1 OR MOD(lnDtlCount,10000) = 0
1830 *-- Output Vendor string "VND"
1831 SELECT (tcWorkVendor)
1832 lcString = lcString + EDI_VENDOR_TAG + lcDelimiter + .ConvertRecordToText(tcWorkVendor, llDelimited, lcDelimiter)
1833 *-- Output header string "HDR"
1834 SELECT (tcWorkHeader)
1835 lcString = lcString + EDI_HEADER_TAG + lcDelimiter + .ConvertRecordToText(tcWorkHeader, llDelimited, lcDelimiter)
1836
1837 *--- TechRec 1076524 24-Apr-2014 TSV---
1838 IF llAdrLine
1839 lcString = lcString + EDI_ADDRESS_TAG + lcDelimiter + .ConvertRecordToText(tcWorkAddress, llDelimited, lcDelimiter)
1840 lnDtlCount = lnDtlCount + 1 && TR 1080713 count every line
1841 ENDIF
1842 *=== TechRec 1076524 24-Apr-2014 TSV===
1843
1844 SELECT (tcWorkDetail)
1845 ENDIF
1846 *=== TR 1064402 12-OCT-12 Venuk
1847 lcString = lcString + EDI_DETAIL_TAG + lcDelimiter + .ConvertRecordToText(tcWorkDetail, llDelimited, lcDelimiter)
1848 lnDtlCount = lnDtlCount + 1
1849 ENDSCAN
1850 ENDIF
1851 *=== TR 1034990 NSD 8/7/08
1852
1853 *-- Write lcString to flat file
1854 IF !EMPTY(lcString)
1855 .PrepareEmptyDateForGentran(@lcString)
1856 IF NOT .Tie_Setup
1857 lnHandle = pnHandle
1858 ENDIF
1859 IF FWRITE(lnHandle, lcString) = 0
1860 lRetVal = .F.
1861 ENDIF
1862 ENDIF
1863
1864 ENDSCAN
1865
1866 IF .Tie_Setup AND lnHandle > 0
1867 *-- Close previously opened file
1868 FCLOSE(lnHandle)
1869 IF llRetVal
1870 *--- TechRec 1060583 19-Apr-2012 jisingh ---
1871 *llRetVal = .CopyToFlatFileInBlock(lcTempFlatfile, lcOutBoundFlatFile)
1872 INSERT INTO __tcFlatFileList(TempFlatFile,OutBoundFlatFile) VALUES (lcTempFlatfile, lcOutBoundFlatFile)
1873 *=== TechRec 1060583 19-Apr-2012 jisingh ===
1874 ENDIF
1875 ENDIF
1876 ENDIF
1877
1878 *-- Close All Work Tables
1879 .TableClose(tcWorkVendor)
1880 .TableClose(tcWorkHeader)
1881 .TableClose(tcWorkDetail)
1882 .TableClose(lcControl)
1883 ENDWITH
1884
1885 SELECT(lnOldSelect)
1886 RETURN llRetVal
1887 ENDPROC
1888*=====================================================
1889 PROCEDURE UpdateMessage
1890 LOCAL llRetVal, lcmsg1, lcmsg2
1891
1892 *--- TR 1058987 7-Mar-2012 Goutam
1893 LOCAL lcmsg3
1894 STORE "" TO lcmsg1, lcmsg2, lcmsg3
1895 *=== TR 1058987 7-Mar-2012 Goutam
1896
1897 WITH THIS
1898 llRetVal= .T.
1899 .cMessage= IIF(!EMPTY(.cMessage), .cMessage + CRLF, "")
1900
1901 *--- TR 1058987 7-Mar-2012 Goutam
1902*!* lcmsg1 = IIF(.nTotalLoc>0, ALLTRIM(STR(.nTotalLoc)) + " Location(s) successfully processed." + CRLF ,"")
1903*!* lcmsg2 = IIF(.nDtlExport>0,ALLTRIM(STR(.nDtlExport)) + " SKU(s) moved from Interface table to flat file." + CRLF,"")
1904*!*
1905*!* .cMessage= .cMessage + (lcmsg1 + lcmsg2 )
1906
1907 IF .nFailedValidation > 0
1908 lcmsg1 = IIF(.nTotalLoc>0, ALLTRIM(STR(.nTotalLoc)) + " Record(s) processed." + CRLF ,"")
1909 lcmsg2 = IIF(.nFailedValidation>0, ALLTRIM(STR(.nFailedValidation)) + " Record(s) failed validation." + CRLF ,"")
1910 ELSE
1911 lcmsg1 = IIF(.nTotalLoc>0, ALLTRIM(STR(.nTotalLoc)) + " Record(s) successfully processed." + CRLF ,"")
1912 ENDIF
1913 IF (.nTotalLoc - .nFailedValidation) > 0
1914 lcmsg3 = IIF(.nDtlExport>0,ALLTRIM(STR(.nDtlExport)) + " SKU(s) moved from Interface table to flat file." + CRLF,"")
1915 ENDIF
1916
1917 .cMessage= .cMessage + (lcmsg1 + lcmsg2 + lcmsg3)
1918
1919 *--- TR 1058987 7-Mar-2012 Goutam
1920
1921 ENDWITH
1922 RETURN llRetVal
1923 ENDPROC
1924*=====================================================
1925 PROCEDURE RemoteScatterGather
1926 LPARAMETERS tcRemoteTable, tcLocalCursor, tcWhere
1927 LOCAL llRetVal, lnSelect, lcSQLString, lcTempCursor
1928
1929 llRetVal = False
1930 lnSelect = SELECT()
1931 lcTempCursor= GetUniqueFileName()
1932
1933 lcSQLString = "SELECT * FROM " + tcRemoteTable + ;
1934 IIF(NOT EMPTY(tcWhere), tcWhere, '')
1935
1936 IF .vSQLExec(lcSQLString, lcTempCursor)
1937 .ScatterGather(lcTempCursor, tcLocalCursor)
1938 llRetVal = True
1939 ENDIF
1940
1941 THIS.TableClose(lcTempCursor)
1942
1943 RETURN llRetVal
1944 ENDPROC
1945*=====================================================
1946 FUNCTION DropTempTables
1947 LOCAL llRetVal, lnSelect, lcSQLString
1948
1949 llRetVal = true
1950 lnSelect = SELECT()
1951
1952 WITH This
1953 IF NOT EMPTY(.cQ_846Dtl)
1954 lcSQLString = "DROP TABLE " + .cQ_846Dtl
1955 llRetVal = llRetVal AND .vSQLExec(lcSQLString)
1956 ENDIF
1957
1958 IF NOT EMPTY(.cQ_846Hdr)
1959 lcSQLString = "DROP TABLE " + .cQ_846Hdr
1960 llRetVal = llRetVal AND .vSQLExec(lcSQLString)
1961 ENDIF
1962
1963 .cQ_846Dtl = ''
1964 .cQ_846Hdr = ''
1965
1966 ENDWITH
1967
1968 SELECT (lnSelect)
1969 RETURN llRetVal
1970
1971 ENDFUNC
1972
1973*=====================================================
1974
1975 *--- TechRec 1060583 19-Apr-2012 jisingh ---
1976 FUNCTION UpdateCustomerPriceFlag
1977 LPARAMETERS tcParam1, tcParam2
1978 LOCAL llRetVal, lnSelect, lcSQLString
1979
1980 llRetVal = true
1981 lnSelect = SELECT()
1982
1983 WITH This
1984
1985 *--- TR 1064279 Goutam Removed existing Where condition and replaced with the new one.
1986 *--- TR 1065893 16-12-2012 VKK
1987 * Commented first update. do not set any exclusion flags
1988 *lcSQLString = " UPDATE cp " + ;
1989 " SET excl_846_sent = 'Y' " + ;
1990 " ,excl_846_date = " + SQLFormatTS(DATETIME()) + ;
1991 " ,last_mod = " + SQLFormatTS(DATETIME()) + ;
1992 " ,user_id = " + SQLFormatChar(goenv.envlogin.cUserName) + ;
1993 " FROM zzxcstpr cp " + ;
1994 " JOIN " + .cQ_846Dtl + " d " + ;
1995 " ON cp.division = d.division " + ;
1996 " AND cp.style = d.style " + ;
1997 " AND cp.color_code = d.color_code " + ;
1998 " AND cp.lbl_code = d.lbl_code " + ;
1999 " AND cp.dimension = d.dimension " + ;
2000 " JOIN " + .cInterFaceHeader + " h " + ;
2001 " ON h.location = d.location" + ;
2002 " AND h.vnd_qual = d.vnd_qual " + ;
2003 " AND h.vnd_id = d.vnd_id " + ;
2004 " AND h.our_qual = d.our_qual " + ;
2005 " AND h.our_id = d.our_id " + ;
2006 " AND h.customer = cp.customer " + ;
2007 " AND h.errs_flg_h <> 'Y' " + ; &&--- TechRec 1061287 02-May-2012 jisingh ===
2008 " WHERE Not (COALESCE(cp.remove_846,'') <> 'Y' OR (COALESCE(cp.remove_846,'') = 'Y' AND COALESCE(cp.remove_846_sent,'') <> 'Y')) " + ; && *--- TR 1064279 Goutam
2009 " and (COALESCE(cp.excl_846,'') <> 'Y' OR (COALESCE(cp.excl_846,'') = 'Y' AND COALESCE(cp.excl_846_sent,'') <> 'Y')) " + ; && *--- TR 1064279 Goutam
2010 " 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
2011
2012 *llRetVal = llRetVal AND .vSQLExec(lcSQLString)
2013
2014 *--- TR 1064279 7-Nov-2012 Goutam.
2015 *--- TR 1065893 16-12-2012 VKK
2016 * Replace " WHERE (COALESCE(cp.remove_846,'') <> 'Y' OR (COALESCE(cp.remove_846,'') = 'Y' AND COALESCE(cp.remove_846_sent,'') <> 'Y'))"
2017 * with " WHERE cp.excl_846 <> 'Y' AND cp.remove_846 = 'Y' AND cp.remove_846_sent <> 'Y' "
2018 *=== TR 1065893 16-12-2012 VKK
2019 lcSQLString = " UPDATE cp " + ;
2020 " SET remove_846_sent = 'Y' " + ;
2021 " ,remove_846_date = " + SQLFormatTS(DATETIME()) + ;
2022 " ,last_mod = " + SQLFormatTS(DATETIME()) + ;
2023 " ,user_id = " + SQLFormatChar(goenv.envlogin.cUserName) + ;
2024 " FROM zzxcstpr cp " + ;
2025 " JOIN " + .cQ_846Dtl + " d " + ;
2026 " ON cp.division = d.division " + ;
2027 " AND cp.style = d.style " + ;
2028 " AND cp.color_code = d.color_code " + ;
2029 " AND cp.lbl_code = d.lbl_code " + ;
2030 " AND cp.dimension = d.dimension " + ;
2031 " JOIN " + .cInterFaceHeader + " h " + ;
2032 " ON h.location = d.location" + ;
2033 " AND h.vnd_qual = d.vnd_qual " + ;
2034 " AND h.vnd_id = d.vnd_id " + ;
2035 " AND h.our_qual = d.our_qual " + ;
2036 " AND h.our_id = d.our_id " + ;
2037 " AND h.customer = cp.customer " + ;
2038 " AND h.errs_flg_h <> 'Y' " + ;
2039 " WHERE cp.excl_846 <> 'Y' AND cp.remove_846 = 'Y' AND cp.remove_846_sent <> 'Y' " && *--- TR 1065893 16-12-2012 VKK
2040
2041
2042
2043 llRetVal = llRetVal AND .vSQLExec(lcSQLString)
2044 *=== TR 1064279 7-Nov-2012 Goutam
2045
2046 ENDWITH
2047
2048 SELECT (lnSelect)
2049 RETURN llRetVal
2050 ENDFUNC
2051 *=== TechRec 1060583 19-Apr-2012 jisingh ===
2052
2053 *--- TechRec 1059983 31-May-2012 MANI. ---
2054 FUNCTION ValidateParamBro
2055
2056 LOCAL llRetVal, lnSelect,lcValue, lnIndex
2057
2058 llRetVal = true
2059 lnSelect = SELECT()
2060 lnIndex = 0
2061 WITH This
2062 *-- Availablity Determination bro validation
2063 IF llRetVal
2064 lnIndex = ASCAN(.aParamBROs,"AVAIL_OPTION",1,ALEN(.aParamBROs,1),1,9)
2065 IF lnIndex > 0
2066 .cAvail_Option = (ALLTRIM(.aParamBROs[lnIndex, 2]))
2067
2068 IF NOT EMPTY(.cAvail_Option) AND (LEN(.cAvail_Option) > 1 OR !INLIST(.cAvail_Option ,'Q','O','A'))
2069
2070 .cMessage= "Invalid Available Based on value."
2071 .oLog.LogEntry(.cMessage)
2072 llRetVal = False
2073 ENDIF
2074 .oLog.LogEntry("Available Based on :" + .cAvail_Option)
2075 ENDIF
2076
2077 ENDIF
2078
2079 *-- % of Available bro validation
2080 IF llRetVal
2081
2082 lnIndex = ASCAN(.aParamBROs,"PERCENT_AVAIL",1,ALEN(.aParamBROs,1),1,9)
2083 IF lnIndex > 0
2084 .nPercent_Avail = VAL((ALLTRIM(.aParamBROs[lnIndex, 2])))
2085
2086 IF .nPercent_Avail < 0 OR .nPercent_Avail > 100
2087
2088 .cMessage= "% of Available Value Should be 0 to 100"
2089 .oLog.LogEntry(.cMessage)
2090 llRetVal = False
2091 ENDIF
2092 IF .nPercent_Avail = 0
2093 .nPercent_Avail = 100
2094 ENDIF
2095 .oLog.LogEntry("% of Available :" + ALLTRIM(STR(.nPercent_Avail)))
2096 ENDIF
2097
2098 ENDIF
2099
2100 *--- TechRec 1062747 18-Jul-2012 MANI. ---
2101 *-- Show zero qty bro validation
2102 IF llRetVal
2103 lnIndex = ASCAN(.aParamBROs,"SHOW_ZERO_QTY",1,ALEN(.aParamBROs,1),1,9)
2104 IF lnIndex > 0
2105 .cShowZeroQty = (ALLTRIM(.aParamBROs[lnIndex, 2]))
2106
2107 IF NOT EMPTY(.cShowZeroQty) AND !INLIST(.cShowZeroQty,'Y','N')
2108 .cMessage= "Invalid Show Zero Qty value. Should be Y or N"
2109 .oLog.LogEntry(.cMessage)
2110 llRetVal = False
2111 ENDIF
2112 .oLog.LogEntry("Show Zero Qty :" + .cShowZeroQty)
2113 ENDIF
2114
2115 ENDIF
2116
2117 *-- MIN size qty avl bro Validation
2118 IF llRetVal
2119 lnIndex = ASCAN(.aParamBROs,"SFNSIZE_QTY",1,ALEN(.aParamBROs,1),1,9)
2120 IF lnIndex > 0
2121 .nMinSzQtyAvl = (ALLTRIM(.aParamBROs[lnIndex, 2]))
2122
2123 IF NOT EMPTY(.nMinSzQtyAvl) AND TYPE(.nMinSzQtyAvl) <> "N"
2124 .cMessage= "Invalid MIN size qty avl. Should be Numeric"
2125 .oLog.LogEntry(.cMessage)
2126 llRetVal = False
2127 ENDIF
2128 .oLog.LogEntry("MIN size qty avl :" + .nMinSzQtyAvl)
2129
2130 ENDIF
2131 ENDIF
2132
2133 *=== TechRec 1062747 18-Jul-2012 MANI. ===
2134
2135
2136 ENDWITH
2137
2138 SELECT (lnSelect)
2139 RETURN llRetVal
2140 ENDFUNC
2141 *=== TechRec 1059983 31-May-2012 MANI. ===
2142
2143*--- TechRec 1076524 10-Apr-2014 TSV---
2144 PROCEDURE GetAddressCursor
2145 LOCAL llRetVal, lnOldSelect
2146
2147 llRetVal = .t.
2148 lnOldSelect = SELECT()
2149
2150 WITH This
2151 .cQ_846Adr = Iif(Version(2)=2,"##","#") + SubStr(SYS(2015), 2)
2152
2153 lcSQL = " SELECT h.location, l.loc_name, l.address1, l.address2, l.city, " + ;
2154 " l.state, l.zipcode, l.country " + ;
2155 " INTO " + .cQ_846Adr + ;
2156 " FROM " + .cQ_846Hdr + " h " + ;
2157 " JOIN zzxlocar l " + ;
2158 " ON h.location = l.location " + ;
2159 " WHERE h.resv_loc_adr = 'Y' " + ;
2160 " AND l.loc_type = 'W' "
2161
2162 llRetVal = v_SQLExec(lcSQL)
2163
2164 ENDWITH
2165
2166 SELECT(lnOldSelect)
2167 RETURN llRetVal
2168 ENDPROC
2169*============================================================
2170*=== TechRec 1076524 10-Apr-2014 TSV===
2171
2172ENDDEFINE