· 6 years ago · Jul 12, 2019, 02:56 PM
1#Include system.h
2*--- TR 1064560 19-2-2013 VKK Addded tlFrom850
3LPARAMETERS tcInvOrders,tcErrorCursor,tlOpen, tlFrom850, to945Log
4
5LOCAL loBPOSO,llRetVal,lcOrderTax
6
7IF ATC("CLSSLSOP",SET("PROCEDURE")) = 0
8 SET PROCEDURE TO clsslsop ADDITIVE
9ENDIF
10
11IF ATC("CLSNIBLR",SET("PROCEDURE")) = 0
12 SET PROCEDURE TO CLSNIBLR ADDITIVE
13ENDIF
14
15loBPOSO = CREATEOBJECT("BOSalesOP")
16llRetVal = .T.
17
18*--- TR 1064560 19-2-2013 VKK
19LOCAL lcSQLString
20lcSQLString = ""
21IF tlFrom850
22 * If call from 850 then we need to ensure that these filds are stamped to default values. Must have been done in clsipopr_createorder, but file contention
23 lcSQLString = " Update zzxchrds " + ;
24 " SEt resolved_ok = 'N', " + ;
25 " ov_ok = 'N', " + ;
26 " chrg_formula = case when chrg_flag IN( 'P', 'N') then chrg_formula else '' end, " + ; && *--- TR 1075078 8-1-2014 VKK Added 'N'
27 " chrg_Expr = '', " + ;
28 " zzxchrgd_pkey = 0, " + ;
29 " percentage = case when chrg_flag IN( 'P', 'N') then percentage else 0.00 end , " + ; && *--- TR 1075078 8-1-2014 VKK added 'N'
30 " trx_pct_amt = 0.0 " +;
31 " Where Exists(Select 1 From " + tcInvOrders + " Where pkey = zzxchrds.hdrfkey) "
32
33 llRetVal = llRetVal AND V_SQlExec(lcSQLString)
34
35 loBPOSO.lResolveDtlCharges = UPPER(vl_Compr(, "Dtl850Chrg_ok")) == "Y"
36
37 *--- 1073611 09/18/13 Ilya:
38 loBPOSO.l850DtlCharges = .T.
39 *=== 1073611 09/18/13 Ilya.
40
41 IF loBPOSO.lResolveDtlCharges
42 loBPOSO.cOrdDtlFilterFields = loBPOSO.GetFilterFields("CHRGDTLEXPR", "D")
43 ENDIF
44
45ENDIF
46*=== TR 1064560 19-2-2013 VKK
47
48*--- TechRec 1075078 11-Feb-2014 jisingh ---
49* No need to calculate charges for cancelled orders
50lcInvOrders = "#" + GetUniqueFileName()
51lcSQLString = " SELECT t.* INTO " + lcInvOrders + ;
52 " FROM " + tcInvOrders + " t " + ;
53 " JOIN " + IIF(tlOpen, "zzoordrh", "zzoshprh") + " h " + ;
54 " ON h.pkey = t.pkey " + ;
55 " AND h.ord_status <> 'C' "
56
57llRetVal = llRetVal AND v_SQLExec(lcSQLString)
58*--- STRY0202659
59IF TYPE('to945Log')= "O"
60to945Log.LogEntry("STRY0202659 - lcInvOrder fetched"+ IIF(llRetVal, "successful", "failed"))
61v_sqlexec("select COUNT(*) as rcnt from "+ lcInvOrders, "curxyz")
62to945Log.LogEntry("STRY0202659 - "+ ALLTRIM(STR(curxyz.rcnt))+ " Records found")
63endif
64*=== STRY0202659
65*=== TechRec 1075078 11-Feb-2014 jisingh ===
66
67lcOrderTax = "#" + getuniquefilename()
68
69*--- TechRec 1075078 11-Feb-2014 jisingh Replaced tcInvOrders with lcInvOrders ===
70llRetVal = llRetVal AND ResolveChargesAllByCursor(lcInvOrders,tcErrorCursor,loBPOSO,tlOpen, to945log)
71
72llRetVal = llRetVal AND UpdateChargesAllByCursor(tcInvOrders,tcErrorCursor,loBPOSO,tlOpen)
73
74*--- TechRec 1075078 11-Feb-2014 jisingh ---
75v_SQLExecNoError("DROP TABLE " + lcInvOrders)
76*=== TechRec 1075078 11-Feb-2014 jisingh ===
77
78*---TR 1044305 12/11/09 YE - backorder charge resolution is not necessary
79* -- Process any back order taxes
80*!* IF llRetVal
81*!* llRetVal = llRetVal AND v_sqlexec(;
82*!* " SELECT DISTINCT a.ord_num,a.pick_num,a.inv_num,a.pkey INTO " + lcOrderTax + ;
83*!* " FROM zzoordrh a " + ;
84*!* " JOIN " + tcInvOrders + " b on a.ord_num = b.ord_num " + ;
85*!* " WHERE a.ord_status = 'O' ")
86*!*
87*!* llRetVal = llRetVal AND v_sqlexec("SELECT COUNT(*) as cnt FROM " + lcOrderTax,'tcOpenCnt')
88*!*
89*!* IF llRetVal AND tcOpenCnt.cnt > 0
90*!* llRetVal = llRetVal AND ResolveChargesAllByCursor(lcOrderTax,tcErrorCursor,loBPOSO,.T.)
91
92*!* llRetVal = llRetVal AND UpdateChargesAllByCursor(lcOrderTax,tcErrorCursor,loBPOSO,.T.)
93
94*!* ENDIF
95*!*
96*!* llRetVal = llRetVal AND v_sqlexec("DROP TABLE " + lcOrderTax)
97
98*!* ENDIF
99
100*!* USE IN SELECT("tcOpenCnt")
101*===TR 1044305 12/11/09 YE
102
103***** 1043140 AZ
104IF loBPOSO.lByPassChargeFailure
105 llRetVal = .t.
106ENDIF
107
108RELEASE loBPOSO
109
110RETURN llRetVal
111
112*------------------------------------------------------------------------
113
114FUNCTION ResolveChargesAllByCursor
115 LPARAMETERS tcInvOrders,tcErrorCursor,toBPOSO,tlOpenTable,to945log
116
117 *--- TechRec 1064560 13-Feb-2013 TShenbagavalli added lcChrgOrdHdrFilterFields, lcChrg_Alias, lcChrgJoin ---
118 LOCAL llRetVal, lnSelect, lcChrgOrdHdrFilterFields, lcChrg_Alias, lcChrgJoin, ;
119 lcSQLString, ;
120 lcCountry, lcState, lcZipCode, lcTaxCategory , loHdrDtlRecord, ;
121 lcTaxCursor, llCalculateTax, lnTaxAmt, lcMasterSource, ;
122 lcDetailSource, llTaxExempt, lcWorkAlias_1, lcWorkAlias_2, ;
123 lnOrd_Num, lnPick_Num, lnInv_Num,lcMode
124
125 llRetVal = .T.
126 lnSelect = SELECT()
127
128 *--- TR 1043543 28-Dec-2009 Goutam
129 LOCAL lcOrdHdrFilterFields
130 lcOrdHdrFilterFields = STRTRAN(toBPOSO.cOrdHdrFilterFields, "h.ORD_NET_AMOUNT", "d.ORD_NET_AMOUNT")
131 *=== TR 1043543 28-Dec-2009 Goutam
132
133 *-- TR 1090555 30-Oct-2015 Dilip
134 lcOrdHdrFilterFields = STRTRAN(lcOrdHdrFilterFields , "h.ORD_GROSS_AMOUNT", "d.ORD_GROSS_AMOUNT")
135 *== TR 1090555 30-Oct-2015 Dilip
136
137
138 *--- TechRec 1064560 13-Feb-2013 TShenbagavalli ---
139 lcChrgOrdHdrFilterFields = toBPOSO.cChrgOrdHdrFilterFields
140 *=== TechRec 1064560 13-Feb-2013 TShenbagavalli ===
141
142 lcWorkAlias_1 = GetUniqueFileName()
143 lcWorkAlias_2 = GetUniqueFileName()
144
145 lcMasterSource = IIF(tlOpenTable,"zzoordrh","zzoshprh")
146 lcDetailSource = IIF(tlOpenTable,"zzoordrd","zzoshprd")
147
148 WITH toBPOSO
149 *--- STRY0202659
150 IF TYPE('to945Log')= "O"
151 to945log.LogEntry("STRY0202659 - Inside ResolveChargesAllByCursor")
152 to945log.LogEntry("STRY0202659 - SetBased_ChargeResolution.ResolveChargesAllByCursor")
153 to945log.LogEntry("STRY0202659 - .lResolveCharges "+ IIF(.lResolveCharges, "true", "false"))
154 endif
155 *=== STRY0202659
156
157 IF .lResolveCharges
158
159
160 lcMode = v_sqlprep("SELECT CASE WHEN inv_num > 0 THEN 'I' WHEN pick_num > 0 THEN 'P' ELSE 'O' END as cMode FROM " + tcInvOrders,,"cMode")
161 IF TYPE('to945Log')= "O"
162 to945log.LogEntry("STRY0202659 - lcMode "+ lcMode)
163 endif
164
165 llRetVal = llRetVal AND FetchChargesbyCursor( tcInvOrders, toBPOso, to945Log )
166 IF TYPE('to945Log')= "O"
167 to945log.LogEntry("STRY0202659 - FetchChargesByCursor "+IIF(llRetVal, "successful", "failed"))
168 endif
169
170 *--- TechRec 1064560 14-Feb-2013 TShenbagavalli ---
171 IF .lResolveDtlCharges
172 llRetVal = llRetVal AND FetchDtlChargesbyCursor(tcInvOrders, toBPOso, to945Log)
173 IF TYPE('to945Log')= "O"
174 to945log.LogEntry("STRY0202659 - FetchDtlChargesByCursor "+IIF(llRetVal, "successful", "failed"))
175 endif
176 ENDIF
177 *=== TechRec 1064560 14-Feb-2013 TShenbagavalli ===
178
179 *--- TR 1086313 KISHORE 13-APR-2015
180 lcCounts = ''
181 IF 'PICK_CNT' $ lcOrdHdrFilterFields OR 'INV_CNT' $ lcOrdHdrFilterFields
182 lcOrdHdrFilterFields = STRTRAN(UPPER(lcOrdHdrFilterFields ), 'H.PICK_CNT', 'COUNTS.PICK_CNT')
183 lcOrdHdrFilterFields = STRTRAN(UPPER(lcOrdHdrFilterFields ), 'H.INV_CNT', 'COUNTS.INV_CNT')
184
185 TEXT TO lcCounts NOSHOW TEXTMERGE
186 JOIN (SELECT ord_num, SUM(pick_cnt) pick_cnt, SUM(inv_cnt) inv_cnt
187 FROM (SELECT ord_num, SUM(case when pick_num > 0 then 1 else 0 end) as pick_cnt,
188 0 as inv_cnt
189 FROM zzoordrh
190 GROUP BY ord_num
191 UNION ALL
192 SELECT ord_num, SUM(case when pick_num > 0 then 1 else 0 end) as pick_cnt,
193 SUM(case when inv_num > 0 then 1 else 0 end) inv_cnt
194 FROM zzoshprh
195 GROUP BY ord_num) cnt
196 GROUP BY ord_num
197 ) counts
198 ON counts.ord_num = h.ord_num
199 ENDTEXT
200
201 ENDIF
202 *=== TR 1086313 KISHORE 13-APR-2015
203
204 .cSQLTempTable=""
205
206 * --- TR 1041118/1046460 14-Aug-09 Surinder Singh : Added "h.center_code, h.store " +; === (was not added previously). Goutam
207
208 *--- TR 1043543 28-Dec-2009 Goutam. Changed from .cOrdHdrFilterFields to lcOrdHdrFilterFields ;
209 in the following sql. also added ;
210 " CAST(SUM(d.price * d.total_qty - (d.price * d.total_qty * " + ;
211 SQLfnIFNull("dsc.disc_perc","0") + "/100)) AS NUMERIC(15,2)) AS ord_Net_amount " + ;
212 in the following sql.
213
214 *--- TR 1046460 4-May-2010 Goutam. Added h.carton and zone_udfc1 in the field list and join with , ;
215 zzordad2 and zzyzoned. The following join added ;
216 " JOIN zzordad2 ad ON h.ord_num = ad.ord_num AND h.pick_num = ad.pick_num AND h.inv_num = ad.inv_num " + ;
217 " JOIN zzxshipr sh ON h.shipper = sh.shipper " + ;
218 " JOIN zzyzoneh zh ON h.shipper = zh.shipper " + ;
219 " LEFT JOIN zzyzoned zd1 ON zh.pkey = zd1.fkey AND LEFT(ad.s_zipcode,5) = zd1.zip3 " + ;
220 " LEFT JOIN zzyzoned zd2 ON zh.pkey = zd2.fkey AND LEFT(ad.s_zipcode,3) = zd2.zip3 AND zd1.pkey IS NULL " + ;
221
222 *--- TR 1062239 08-Aug-12 SK Added condition 'OR zh.pkey IS NULL' to get record even no records in zzyzoneh
223 *--- TR 19-May-2010 Goutam. Added field eod_actual_frgt in the select list
224 *--- TechRec 1051033 29-Nov-2010 MANI. Added DISTINCT and WHERE conditions to avoid fetch the duplication records ===
225 lcSQLString = "SELECT DISTINCT h.ord_num, " + ;
226 " h.pick_num, " + ;
227 " h.inv_num, " + ;
228 " h.customer, " + ;
229 " h.cncl_num, " + ;
230 " h.pkey as hdr_pkey, " + ;
231 " d.total_Qty, " + ;
232 " h.frgt_Amt , " + ;
233 " h.disc_Amt , " + ;
234 " h.insu_amt , " + ;
235 " h.center_Code, " + ;
236 " h.store, " + ;
237 " h.season, " + ; && Tr 1064560
238 " CAST(d.merch_Amt AS NUMERIC(15,2)) as merch_amt, " + ;
239 lcOrdHdrFilterFields + ;
240 lcChrgOrdHdrFilterFields + ;&& Tr 1064560
241 " h.division, " + ;
242 " 0 Chrgh_pkey, " + ;
243 " d.netmer_amt, " + ;
244 " /* h.eod_actual_frgt, */" + ; && *--- TR 1073530 15-9-2013 VKK Commented , itis added in lcorddtlfitlerfields now
245 " h.carton, " + ;
246 " c.cust_type, " + ; && TR 1064560 c.cust_Type added existing issue fixed
247 " coalesce(zd1.zone_udfc1,zd2.zone_udfc1,'') as zone_udfc1 " + ;
248 " ,ad.s_state as ship_to_state, " + ; && *--- TR 1073530 15-9-2013 VKK
249 " ad.s_country as shipto_country, " + ; &&--- TechRec 1077586 27-Mar-2014 jisingh ===
250 " coalesce(cnty.int_region_code,'') as int_region_code, " + ; &&--- TechRec 1077586 27-Mar-2014 jisingh ===
251 " h.location , " + ; && *--- TR 1073530 15-9-2013 VKK
252 " h.source " + ; && *--- TR 1073530 15-9-2013 VKK
253 " FROM " + lcMasterSource + " h " + ;
254 " JOIN " + tcInvOrders + " x ON h.pkey = x.pkey " + ;
255 " JOIN zzxcustr c on c.customer = h.customer " + ; && TR 1064560 fixed existing issue
256 " JOIN ( SELECT fkey, " + ;
257 " SUM(total_Qty) Total_Qty, " + ;
258 " SUM(d.price * d.Total_Qty) as Merch_Amt, " + ;
259 " CAST(SUM(d.price * d.total_qty - (d.price * d.total_qty * " + ;
260 SQLfnIFNull("dsc.disc_perc","0") + "/100)) AS NUMERIC(15,2)) AS Netmer_amt, " + ;
261 " CAST(SUM(d.price * d.total_qty - (d.price * d.total_qty * " + ;
262 SQLfnIFNull("dsc.disc_perc","0") + "/100)) AS NUMERIC(15,2)) AS Ord_Net_amount, " + ;
263 " CAST(SUM(d.price * d.total_qty) AS NUMERIC(15,2)) AS Ord_Gross_amount " + ; &&-- TR 1090555 30OCT2015 Dilip
264 " FROM " + lcDetailSource + " d " + ;
265 " JOIN " + tcInvOrders + " tmp ON d.fkey = tmp.pkey " + ;
266 " LEFT OUTER JOIN zzxdiscr dsc " + ;
267 " ON dsc.discount = d.discount " + ;
268 " GROUP BY fkey ) d " + ;
269 " ON h.pkey = d.fkey " + ;
270 " JOIN zzordad2 ad ON h.ord_num = ad.ord_num AND h.pick_num = ad.pick_num AND h.inv_num = ad.inv_num and h.cncl_num = ad.cncl_num " + ;
271 " LEFT JOIN zzxshipr sh ON h.shipper = sh.shipper " + ;
272 " LEFT JOIN zzxcntyr cnty ON cnty.country = ad.s_country " + ; &&--- TechRec 1077586 27-Mar-2014 jisingh ===
273 " LEFT JOIN zzyzoneh zh ON h.location = zh.location and h.shipper = zh.shipper " + ; && TR1052821 FGCjr 03/10/11 add location
274 " AND zh.pkey = (SELECT MAX(pkey) FROM zzyzoneh eh WHERE zh.location = eh.location and zh.shipper = eh.shipper)" +; &&& 1057926 Removed When and put under LEFT JOIN &&1091423 added zh.location = eh.location and
275 " LEFT JOIN zzyzoned zd1 ON zh.pkey = zd1.fkey AND LEFT(ad.s_zipcode,5) = zd1.zip3 " + ;
276 " LEFT JOIN zzyzoned zd2 ON zh.pkey = zd2.fkey AND LEFT(ad.s_zipcode,3) = zd2.zip3 AND zd1.pkey IS NULL " + ;
277 lcCounts && TR 1086313 KISHORE 13-APR-2015
278* " WHERE zh.pkey = (SELECT MAX(pkey) FROM zzyzoneh eh WHERE zh.shipper = eh.shipper) OR zh.pkey IS NULL " &&--- TechRec 1051033 29-Nov-2010 MANI. Added 1 line (Where contion) === &&& 1057926 commented out
279
280 *===TR 1010721 19May.2005 AS
281
282 *=== TR 1008555 28-DEC-2004 TK
283
284 *=== TR 1004733 08/03/04 AM
285
286 .cQ_OrdAddrDtl = SQLTableFromQuery(lcSqlString)
287 llRetVal = llRetVal AND !EMPTY(.cQ_OrdAddrDtl)
288
289 *--- STRY0202659
290 IF TYPE('to945Log')= "O"
291 to945log.LogEntry("STRY0202659 - .cQ_OrdAddrDtl creation "+IIF(llRetVal, "successful", "failed"))
292 to945log.LogEntry("STRY0202659 - "+ lcSqlString)
293 v_sqlexec("select COUNT(*) as rcnt from "+ .cQ_OrdAddrDtl, "curxyz")
294 to945log.logEntry("STRY0202659 - "+ ALLTRIM(STR(curxyz.rcnt))+ " Records found")
295 endif
296 *=== STRY0202659
297
298
299 *--- TR 1043853 24-Nov-2009 Goutam. Added Resolve flag in following sql.
300 *--- TR 1046460 4-May-2010 Goutam. Added (ZONE_UDFC1 = ZONE_UDFC1 OR ZONE_UDFC1 = '')
301 * --- TR 1041118/1046460 14-Aug-2009 Surinder Singh Added center_code and store. (was not added previously). Goutam
302 *--- TR 1048101 02-JUL-2010 MANI. Make store field as desc order ===
303 *--- TechRec 1064560 08-Feb-2013 TShenbagavalli added season in where condition and in order by ---
304 lcResolveaddr = SQLTRANSLATETOPN( + ;
305 "SELECT TOP 1 pkey FROM zzxchrgh " + ;
306 " WHERE Resolve_ok = 'Y' " + ; && TR 1064560 existing issue fixed
307 " AND (customer = " + .cQ_ordAddrDtl + ".customer OR customer = '') " +;
308 " AND (division = " + .cQ_ordAddrDtl + ".division OR division = '') " +;
309 " AND (Center_Code = " + .cQ_ordAddrDtl + ".center_code OR Center_Code = '') " +;
310 " AND (Store = " + .cQ_ordAddrDtl + ".Store OR Store = '') " +;
311 " AND (cust_type = " + .cQ_ordAddrDtl + ".Cust_type OR Cust_type = '') "+ ; && TR 1064560 existing issue fixed
312 " AND (zone_udfc1 = " + .cQ_ordAddrDtl + ".zone_udfc1 OR zone_udfc1 = '') " + ;
313 " AND (season = " + .cQ_ordAddrDtl + ".season OR season = '') " + ;
314 " AND (ord_type = " + .cQ_ordAddrDtl + ".ord_type OR ord_type= '') " + ; && *--- TR 1073530 15-9-2013 VKK
315 " AND (ord_source = " + .cQ_ordAddrDtl + ".source OR ord_source= '') " + ; && *--- TR 1073530 15-9-2013 VKK
316 " AND (location = " + .cQ_ordAddrDtl + ".location OR location = '') " + ; && && *--- TR 1073530 15-9-2013 VKK
317 " AND (ship_to_state= " + .cQ_ordAddrDtl + ".ship_to_state OR ship_to_state = '') " + ; && *--- TR 1073530 15-9-2013 VKK
318 " AND (shipto_country = " + .cQ_ordAddrDtl + ".shipto_country OR shipto_country = '') " + ; &&--- TechRec 1077586 27-Mar-2014 jisingh ===
319 " AND (int_region_code = " + .cQ_ordAddrDtl + ".int_region_code OR int_region_code = '') " + ; &&--- TechRec 1077586 27-Mar-2014 jisingh ===
320 " AND group_code1 = '' And group_code2 = '' and group_code3 = '' and group_code4 = '' and group_code5 = '' " + ; && Tr 1064560
321 " AND group_code6 = '' and group_code7 = '' and group_code8 = '' and style = '' " + ; && Tr 1064560
322 " AND group_code9 = '' and group_code10 = '' and group_code11 = '' and group_code12 = '' and group_code13 = ''" + ; && *--- TR 1073530 15-9-2013 VKK group code added 9..13
323 " ANd color_code = '' and lbl_code = '' and dimension = '' " + ; && Tr 1064560
324 " ORDER BY Rank_seq, division desc, customer desc, center_code desc, store desc, zone_udfc1 desc, season desc, ord_type desc, ord_source desc, location desc, ship_to_state desc, shipto_country desc, int_region_code desc") && *--- TR 1073530 15-9-2013 VKK &&--- TechRec 1077586 27-Mar-2014 jisingh ===
325
326 *===TR 1010721 19May.2005 AS
327
328 *--- TechRec 1064560 12-Apr-2013 TShenbagavalli ---
329 lcSQLString = " UPDATE h " + ;
330 " SET chrgh_pkey = COALESCE(( SELECT Top 1 fkey " + ;
331 " FROM zzxchrgd gd " + ;
332 " JOIN " + .cQ_ZzxChrgs + " gs " + ;
333 " ON gd.pkey = gs.zzxchrgd_pkey " + ;
334 " WHERE gs.ord_num = h.ord_num " + ;
335 " AND gs.pick_num = h.pick_num " + ;
336 " AND gs.inv_num = h.inv_num ),0) " + ;
337 " FROM " + .cQ_ordAddrDtl + " h "
338
339 llRetVal = llRetVal AND V_SqlExec(lcSQLString)
340
341 *--- STRY0202659
342 IF TYPE('to945Log')= "O"
343 to945log.logEntry("STRY0202659 - update chrgh_pkey "+IIF(llRetVal, "successful", "failed"))
344 endif
345 *=== STRY0202659
346 *=== TechRec 1064560 12-Apr-2013 TShenbagavalli ===
347
348 *--- TechRec 1064560 12-Apr-2013 TShenbagavalli added where condition chrgh_pkey = 0 ---
349 lcSQLString = " UPDATE " + .cQ_ordAddrDtl + ;
350 " SET chrgh_pkey = COALESCE( ( " + lcResolveaddr + " ),0)" + ;
351 " WHERE chrgh_pkey = 0 " && Tr 1064560
352
353 llRetVal = llRetVal AND V_SqlExec(lcSQLString)
354 *--- STRY0202659
355 IF TYPE('to945Log')= "O"
356 to945log.LogEntry("STRY0202659 - update chrgh_pkey in detail "+IIF(llRetVal, "successful", "failed"))
357 endif
358 *=== STRY0202659
359
360 && Error when no tax header record found for any of the detail line.
361
362 lcSQLString = "SELECT COUNT(*) as nChrgCnt FROM " + .cQ_ordAddrDtl + " WHERE Chrgh_pkey = 0 "
363 llRetVal = llRetVal AND V_SQLExec(lcSQLString, lcWorkAlias_1) AND USED(lcWorkAlias_1)
364 *--- STRY0202659
365 IF TYPE('to945Log')= "O"
366 to945log.LogEntry("STRY0202659 - count for chrgh_pkey "+IIF(llRetVal, "successful", "failed"))
367 endif
368 *=== STRY0202659
369
370 IF llRetVal
371 SELECT(lcWorkAlias_1)
372
373 IF nChrgCnt > 0
374 .cChargeErrorMsg = .cChargeErrorMsg + CRLF + ;
375 "Charge header could not be found for order customer and division."
376 llRetVal = .f.
377
378 *---TR 1011941 21Jul.2005 AS
379 .lMatchChargeReference = .F.
380 *===TR 1011941 21Jul.2005 AS
381
382 *--- STRY0202659
383 IF TYPE('to945Log')= "O"
384 to945log.LogEntry("STRY0202659 - count for chrgh_pkey is "+STR(nChrgCnt))
385 endif
386 *=== STRY0202659
387
388
389 ENDIF
390 ENDIF
391
392 *--- TechRec 1064560 07-Feb-2013 TShenbagavalli ---
393 *--- TechRec 1070894 09-May-2013 TShenbagavalli ---
394*!* lcSQLString = " SELECT ch.chrg_expr " + ;
395*!* " FROM zzxchrgrh ch" + ;
396*!* " JOIN zzxchrgrd cd " + ;
397*!* " ON cd.fkey = ch.pkey " + ;
398*!* " WHERE cd.rank_seq = ( select rank_seq from zzxchrgh ch " + ;
399*!* " join " + .cQ_ordAddrDtl + " qt " +;
400*!* " on ch.pkey = qt.chrgh_pkey ) "
401
402 && In zzxchrgrh mostly one record will be there, so just taking top 1
403 lcSQLString = " SELECT top 1 ch.chrg_expr " + ;
404 " FROM zzxchrgrh ch"
405 *=== TechRec 1070894 09-May-2013 TShenbagavalli ===
406
407 llRetVal = llRetVal AND V_SQLExec(lcSQLString, "tcChrgexpr")
408 *--- STRY0202659
409 IF TYPE('to945Log')= "O"
410 to945log.logEntry("STRY0202659 - Fetching tcChrgexpr "+ IIF(llRetVal, "success", "fail"))
411 to945log.logEntry("STRY0202659 - Chrg_Expr - "+ IIF(llRetVal, ALLTRIM(tcChrgexpr.chrg_expr), ""))
412 endif
413 *--- STRY0202659
414
415 llEvaluateChrg = True
416 lcChrgJoin =""
417 IF llRetVal AND RECCOUNT("tcChrgexpr") > 0
418 lcChrg_Alias = GetUniqueFileName()
419 SELECT tcChrgexpr
420 IF !EMPTY(ALLTRIM(chrg_expr))
421 llEvaluateChrg = EvalChargeFiltertoCursor(.cQ_OrdAddrDtl, chrg_expr, @lcChrg_Alias)
422 lcChrgJoin = " JOIN " + lcChrg_Alias + " tc " + ;
423 " ON h.hdr_pkey = tc.hdr_pkey "
424 ENDIF
425 ENDIF
426
427 IF .t.
428 *--- STRY0202659
429 IF TYPE('to945Log')= "O"
430 to945log.logEntry("STRY0202659 - lResolveDtlCharges "+ IIF(.lResolveDtlCharges, "true", "false"))
431 endif
432 *=== STRY0202659
433
434 IF .lResolveDtlCharges
435 llRetVal = llRetVal AND ResolveDetailChargesbycursor(lcMasterSource, lcDetailSource, lcMode, tcInvOrders, toBPOso, llEvaluateChrg, to945Log )
436 *--- STRY0202659
437 IF TYPE('to945Log')= "O"
438 to945log.logEntry("STRY0202659 - ResolutionDetailChargeByCursor "+ IIF(llRetVal, "success", "fail"))
439 endif
440 *=== STRY0202659
441 ENDIF
442 *=== TechRec 1064560 07-Feb-2013 TShenbagavalli ===
443
444 *--- TR 1043853 24-Nov-2009 Goutam. Added Resolve flag in following sql.
445 lcSQLString = "SELECT h.division, " + ;
446 " h.ord_num, " + ;
447 " h.pick_num, " + ;
448 " h.inv_num, " + ;
449 " h.hdr_pkey, " + ;
450 " h.frgt_amt, " + ;
451 " h.disc_amt , " + ;
452 " h.insu_amt, " + ;
453 " h.merch_amt, " + ;
454 " h.netmer_amt, " + ;
455 " h.total_Qty, " + ;
456 .cOrdHdrFilterFields + ;
457 " td.pkey, " + ;
458 " td.chrg_type, " + ;
459 " h.carton, " + ; && --- TR 1046460 5-May-2010 Goutam
460 " /*h.eod_actual_frgt, */" + ; && --- TR 1046460 19-May-2010 && *--- TR 1073530 15-9-2013 VKK commented
461 " 000.000 as FCCOMM_Amt, " + ;
462 " CASE WHEN COALESCE(chrg.override, chrg_prev.override, 0) = 0 THEN td.Chrg_Formula " + ;
463 " ELSE COALESCE(chrg.chrg_formula, chrg_prev.chrg_formula) " + ;
464 " END As chrg_formula, " + ;
465 " CASE WHEN COALESCE(chrg.override, chrg_prev.override,0) = 0 THEN td.Chrg_Formula " + ;
466 " ELSE COALESCE(chrg.chrg_formula, chrg_prev.chrg_formula) " + ;
467 " END As old_chrg_formula, " + ;
468 " CASE WHEN COALESCE(chrg.override, chrg_prev.override, 0) = 0 THEN td.Chrg_expr " + ;
469 " ELSE COALESCE(chrg.chrg_expr, chrg_prev.chrg_expr) " + ;
470 " END As chrg_expr, " + ;
471 " CASE WHEN COALESCE(chrg.override, chrg_prev.override, 0) = 0 THEN td.percentage " + ;
472 " ELSE COALESCE(chrg.ov_pct, chrg_prev.ov_pct) " + ;
473 " END As percentage, " + ;
474 " CASE WHEN COALESCE(chrg.override, chrg_prev.override, 0) = 0 THEN td.percentage " + ;
475 " ELSE COALESCE(chrg.ov_pct, chrg_prev.ov_pct) " + ;
476 " END As orig_percentage, " + ;
477 " CASE WHEN COALESCE(chrg.override, chrg_prev.override, 0) = 0 THEN td.Chrg_amt " + ;
478 " ELSE COALESCE(chrg.ov_Amt, chrg_prev.ov_Amt) " + ;
479 " END As chrg_Amt, " + ;
480 " CASE WHEN COALESCE(chrg.override, chrg_prev.override, 0) = 0 THEN td.Chrg_flag " + ;
481 " ELSE COALESCE(chrg.chrg_Flag, chrg_prev.chrg_Flag) " + ;
482 " END As chrg_Flag, " + ;
483 " CASE WHEN COALESCE(chrg.Override, chrg_prev.Override, 0) = 0 THEN 'N'" + ;
484 " WHEN COALESCE(chrg.Override, chrg_prev.Override, 0) = 1 THEN 'Y' " + ;
485 " ELSE 'N' END as override " + ;
486 " FROM " + .cQ_ordAddrDtl + " h " + ;
487 lcChrgJoin + ; && TR 1064560
488 " JOIN zzxchrgd td " + ;
489 " ON h.Chrgh_pkey = td.fkey " + ;
490 " JOIN zzxchrtr tr ON td.chrg_type = tr.chrg_type AND tr.chrg_hdrdtl = 'H'" + ; && --- TR 1030465 RLN 02/14/08
491 IIF(llEvaluateChrg ," LEFT OUTER JOIN ", " JOIN ") + .cQ_ZzxChrgs + " chrg " + ; &&*--- TR 1064560 28-2-2013 VKK Addeed IIF( condition to handle overriden changes
492 " ON chrg.ord_num = h.ord_num " + ;
493 " AND chrg.pick_num = h.pick_num " + ;
494 " AND chrg.inv_num = h.inv_num " + ;
495 " AND chrg.zzxchrgd_pkey = td.pkey "
496
497 * Please refer to the Spec for More idea of what is done. Refer to the Truth Table.
498 * If you are processing for Order (open) search for 1.open, if not 2. charger refer table
499 * If you are processing for pick search for 1. pick, if not, 2. order, 3. charges refer table
500 * If you are processing for Inv search for 1. Inv, if not, 2. Pick, 3. charges refer table
501 IF INLIST(lcMode, 'P', 'I')
502 lcSQLString = lcSQLString + ;
503 " LEFT OUTER JOIN (SELECT c.*, CASE WHEN c.ov_ok = 'Y' " + ;
504 " THEN 1 ELSE 0 " + ;
505 " END AS override " + ;
506 " FROM zzxchrgs c) chrg_prev " + ;
507 " ON chrg_prev.ord_num = h.ord_num " + ;
508 " AND chrg_prev.pick_num = " + IIF(lcMode = 'P' , "0", "h.pick_num") + ;
509 " AND chrg_prev.inv_num = 0 " + ;
510 " AND chrg_prev.zzxchrgd_pkey = td.pkey "
511 ELSE
512 lcSQLString = STRTRAN(lcSQLString, "chrg_prev.", "chrg.")
513 ENDIF
514 *=== TR 1020107 28-OCT-2006 VKK
515
516 lcSQLString = lcSQLString + " WHERE td.Resolve_ok = 'Y' AND td.active_ok = 'Y' " && *--- TR 1082854 19-11-2014 VKK aded active_ok = 'Y'
517
518 .cQ_taxAddrdtl = SQLTableFromQuery(lcSqlString)
519 llRetVal = llRetVal AND !EMPTY(.cQ_taxAddrdtl)
520 *--- STRY0202659
521 IF TYPE('to945Log')= "O"
522 to945log.logEntry("STRY0202659 - Fetching cQ_taxAddrdtl"+ IIF(llRetVal, "success", "fail"))
523 v_sqlexec("select COUNT(*) as rcnt from "+ .cQ_taxAddrdtl, "curxyz")
524 to945log.logEntry("STRY0202659 - "+ ALLTRIM(STR(curxyz.rcnt))+ " Records found")
525 endif
526 *=== STRY0202659
527
528 * Build the tax formula - TYPE of one detail line if refered in the formula then
529 * we need to replace the formula of the later with the formula
530 * May be SQL Server specific
531
532 * Substitute tax formula with the respective prorated order amounts
533 lcSQLString = "UPDATE " + .cQ_taxAddrdtl + " " +;
534 " SET chrg_formula = " + ;
535 "REPLACE(LTRIM(RTRIM(chrg_formula )),'MERCH', LTRIM(RTRIM(CAST(merch_amt AS VARCHAR)))) "
536 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
537
538 * Substitute tax formula with the respective prorated order amounts
539 lcSQLString = "UPDATE " + .cQ_taxAddrdtl + " " +;
540 " SET chrg_formula = " + ;
541 "REPLACE(LTRIM(RTRIM(chrg_formula)),'MERCH', LTRIM(RTRIM(CAST(merch_amt AS VARCHAR)))) "
542 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
543
544 * Substitute tax formula for NET MERCH
545 lcSQLString = "UPDATE " + .cQ_taxAddrdtl + " " +;
546 " SET chrg_formula = " + ;
547 "REPLACE(LTRIM(RTRIM(chrg_formula)),'NETMER', LTRIM(RTRIM(CAST(netmer_amt AS VARCHAR)))) "
548 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
549
550 lcSQLString = "UPDATE " + .cQ_taxAddrdtl + " " +;
551 " SET chrg_formula = " + ;
552 "REPLACE(LTRIM(RTRIM(chrg_formula)),'FRGT', LTRIM(RTRIM(CAST(frgt_amt AS VARCHAR)))) "
553 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
554
555 lcSQLString = "UPDATE " + .cQ_taxAddrdtl + " " +;
556 " SET chrg_formula = " + ;
557 "REPLACE(LTRIM(RTRIM(chrg_formula)),'DISC', LTRIM(RTRIM(CAST(disc_amt AS VARCHAR)))) "
558 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
559
560 lcSQLString = "UPDATE " + .cQ_taxAddrdtl + " " +;
561 " SET chrg_formula = " + ;
562 "REPLACE(LTRIM(RTRIM(chrg_formula)),'INS', LTRIM(RTRIM(CAST(insu_amt AS VARCHAR)))) "
563 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
564
565 *--- TR 1041179 NSD 7/1/09 Added fccomm_amt
566 lcSQLString = "UPDATE " + .cQ_taxAddrdtl + " " +;
567 " SET percentage = 100 " + ;
568 " WHERE chrg_flag IN( 'P', 'N') AND chrg_formula like '%FCCOMM%' " && *--- TR 1075078 8-1-2014 VKKadded 'N'
569 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
570
571 lcSQLString = "UPDATE " + .cQ_taxAddrdtl + " " +;
572 " SET chrg_formula = " + ;
573 "REPLACE(LTRIM(RTRIM(chrg_formula)),'FCCOMM', LTRIM(RTRIM(CAST(FCCOMM_Amt AS VARCHAR)))) "
574 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
575 *=== TR 1041179 NSD 7/1/09 Added fccomm_amt
576
577 *--- TR 1046460 11-Jun-2010 Goutam.
578 lcSQLString = "UPDATE " + .cQ_taxAddrdtl + " " +;
579 " SET chrg_formula = " + ;
580 "REPLACE(LTRIM(RTRIM(chrg_formula)),'ACTFRT', LTRIM(RTRIM(CAST(eod_actual_frgt AS VARCHAR)))) "
581 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
582 *=== TR 1046460 11-Jun-2010 Goutam.
583
584 *--- TR 1046460 4-May-2010 Goutam. Added ;
585 " WHEN chrg_flag = 'C' " + ;
586 " THEN CAST (chrg_amt * Carton AS VARCHAR) " + ;
587
588 lcSQLString = "UPDATE " + .cQ_taxAddrdtl + " " +;
589 " SET chrg_formula = " + ;
590 " CASE WHEN chrg_flag = 'F'" + ;
591 " THEN CAST (chrg_amt AS Varchar) " + ;
592 " WHEN chrg_flag = 'U' " + ;
593 " THEN CAST (chrg_amt * Total_Qty AS VARCHAR) " + ;
594 " WHEN chrg_flag = 'P' " + ;
595 " THEN '1/100 *' + LTRIM(RTRIM(CAST(percentage as Varchar))) + '* ( '+ LTRIM(RTRIM(chrg_formula))+ ')'" + ;
596 " WHEN chrg_flag = 'C' " + ;
597 " THEN CAST (chrg_amt * Carton AS VARCHAR) " + ;
598 " WHEN chrg_flag = 'N' " + ; && *--- TR 1075078 8-1-2014 VKK
599 " THEN 'Total_Qty * 1/100 *' + LTRIM(RTRIM(CAST(percentage as Varchar))) + '* ( '+ LTRIM(RTRIM(chrg_formula))+ ')'" + ; && *--- TR 1075078 8-1-2014 VKK
600 " ELSE chrg_formula END " + ;
601 " ,percentage = orig_percentage "
602
603 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
604
605 lcSQLString = "SELECT * FROM " + .cQ_taxaddrdtl
606 llRetval = llRetval AND V_SQLExec(lcSQLString, lcWorkAlias_2)
607 llRetVal = llRetVal AND USED(lcWorkAlias_2)
608 *--- STRY0202659
609 IF TYPE('to945Log')= "O"
610 to945log.logEntry("STRY0202659 - lcWorkAlias_2 cursor create "+ IIF(llRetVal, "success", "fail"))
611 to945log.logEntry("STRY0202659 - RecCount of "+ lcWorkAlias_2+ " is "+ IIF(USED(lcWorkAlias_2), STR(RECCOUNT(lcWorkAlias_2)), ""))
612 endif
613 *=== STRY0202659
614
615 *--- TR 1073530 16-9-2013 VKK
616 llHasCtypechrg_Flag = llRetVal And (.CountTotalRecs(lcWorkAlias_2, "chrg_flag = 'C'") > 0)
617 * Now fire the Carton Query to fetch carton code and count(*) of cartons for each carton code
618 lcCartonAlias = Getuniquefilename()
619 IF TYPE('to945Log')= "O"
620 to945log.logEntry("STRY0202659 - llHasCtypechrg_Flag "+ IIF(llHasCtypechrg_Flag, "success", "fail")) && STRY0202659
621 endif
622 IF llHasCtypechrg_Flag
623
624 TEXT TO lcSQLString TEXTMERGE noshow
625 SELECT h.*, coalesce(Carton_code,'') Carton_code, coalesce(Carton_Count, 0) Carton_Count
626 From <<.cQ_taxaddrdtl>> h
627 left join (Select h.ord_num, h.pick_num, h.inv_num, Carton_code, COUNT(Distinct carton_num) Carton_Count
628 From <<.cQ_taxaddrdtl>> h
629 Join zzoordsp sp
630 ON sp.ord_num = h.ord_num
631 And sp.pick_num = h.pick_Num
632 And sp.inv_num = h.inv_num
633 Join zzoctnph ph
634 ON ph.fkey = sp.pkey
635 Group by h.ord_num, h.pick_num, h.inv_num, Carton_code) C
636 ON c.ord_num = h.ord_num
637 And c.pick_num = h.pick_Num
638 And c.inv_num = h.inv_num
639 ENDTEXT
640 llRetval = llRetval AND V_SQLExec(lcSQLString, lcCartonAlias)
641 *--- STRY0202659
642 IF TYPE('to945Log')= "O"
643 to945log.logEntry("STRY0202659 - lcCartonAlias created "+ IIF(llRetVal, "success", "fail"))
644 to945log.logEntry("STRY0202659 - lcCartonAlias RecCount "+ STR(RECCOUNT(lcCartonAlias)))
645 endif
646 *=== STRY0202659
647 ENDIF
648 *=== TR 1073530 16-9-2013 VKK
649
650 IF llRetVal AND RECCOUNT(lcWorkAlias_2) > 0
651
652 lcSQLString = "DELETE " + ;
653 " FROM zzxchrgs where pkey IN " + ;
654 " (SELECT a.pkey FROM zzxchrgs a " + ;
655 " JOIN " + tcInvOrders + " tmp " + ;
656 " ON a.ord_num = tmp.ord_num " + ;
657 " and a.pick_num = tmp.pick_num " + ;
658 " AND a.inv_num = tmp.inv_num " + ;
659 " AND a.ov_ok <> 'D' ) "
660 llRetval = llRetval AND V_SQLExec(lcSQLString)
661 *--- STRY0202659
662 IF TYPE('to945Log')= "O"
663 to945log.logEntry("STRY0202659 - Delete from zzxchrgs "+ IIF(llHasCtypechrg_Flag, "success", "fail"))
664 to945log.logEntry("STRY0202659 - "+ lcSqlString)
665 endif
666 *=== STRY0202659
667
668 * Build SQL String to insert into Order taxwr dtl
669 llRetVal = llRetVal AND v_sqlexec("SELECT * FROM zzxchrgs WHERE 1=0","tcChgs")
670 *--- STRY0202659
671 IF TYPE('to945Log')= "O"
672 to945log.logEntry("STRY0202659 - creation of tcChgs "+ IIF(llHasCtypechrg_Flag, "success", "fail"))
673 endif
674 *=== STRY0202659
675
676 lcSQLString = ""
677 *--- TR 1073530 16-9-2013 VKK Added lcCartonAlias
678 llRetVal = llRetval AND BuildInsertChargeString(lcWorkAlias_2,toBPOSO, lcCartonAlias)
679 *--- STRY0202659
680 IF TYPE('to945Log')= "O"
681 to945log.logEntry("STRY0202659 - BuildInsertString "+ IIF(llretval, "success", "failed"))
682 endif
683 *=== STRY0202659
684
685 ENDIF
686
687 .TableClose(lcWorkAlias_1)
688 .TableClose(lcWorkAlias_2)
689
690 *---TR 1011941 21Jul.2005 AS
691 *.lResolveTaxSuccess = llRetVal
692 *===TR 1011941 21Jul.2005 AS
693
694 IF NOT llRetVal
695 .DropTempTables()
696 ENDIF
697
698 *---TR 1011941 21Jul.2005 AS
699 IF NOT llRetVal AND NOT .lMatchTaxReference AND .lByPassTaxFailure
700 llRetVal = .T.
701 *--- STRY0202659
702 IF TYPE('to945Log')= "O"
703 to945log.logEntry("STRY0202659 - llRetVal changed to .T.")
704 endif
705 *=== STRY0202659
706 ENDIF
707
708 .lResolveTaxSuccess = llRetVal
709 *===TR 1011941 21Jul.2005 AS
710
711 llRetVal = llRetVal AND RollupChargesToHeaderbyCursor(tcInvOrders, lcDetailSource,toBPOso) && *--- TR 1064560 5-3-2013 VKK added tobposo
712
713 ENDIF && END lResolveTaxFeature
714
715 *--- TechRec 1064560 08-Feb-2013 TShenbagavalli ---
716 ENDIF
717 *=== TechRec 1064560 08-Feb-2013 TShenbagavalli ===
718 ENDWITH
719
720 SELECT (lnSelect)
721 RETURN llRetVal
722ENDFUNC
723
724*-------------------------------------------------------------
725*--- TechRec 1064560 31-Jan-2013 TShenbagavalli ---
726 FUNCTION EvalChargeFiltertoCursor
727 LPARAMETERS tcMasterAlias, tcFilterExp, tcChrgAlias
728 LOCAL llRetVal, lnSelect, lcExpr, lcSQL
729
730 llRetVal = .T.
731 lnSelect = SELECT()
732 lcExpr = ALLTRIM(tcFilterExp)
733
734 IF !EMPTY(lcExpr)
735 lcExpr = STRTRAN(lcExpr, "[", "'")
736 lcExpr = STRTRAN(lcExpr, "]", "'")
737
738 lcSQL = " SELECT * FROM " + tcMasterAlias + " tm " + ;
739 " WHERE ( " + lcExpr + " ) " + ;
740 " OR ( NOT EXISTS (SELECT 1 " + ;
741 " FROM zzxchrgs " + ;
742 " WHERE ord_num = tm.ord_num " + ;
743 " AND pick_num = tm.pick_num " +;
744 " AND inv_num = tm.inv_num " + "))"
745
746 tcChrgAlias = SQLTableFromQuery(lcSQL)
747
748 llRetval = llRetVal AND !EMPTY(tcChrgAlias)
749
750 ENDIF
751
752 SELECT (lnSelect)
753
754 RETURN llRetval
755 ENDFUNC
756*============================================================
757FUNCTION FetchDtlChargesbyCursor
758 LPARAMETERS tcInvOrders, toBPOso, to945log
759
760 LOCAL llRetVal,lnSelect,lcSQL
761 llRetVal = .T.
762 lnSelect = SELECT()
763
764 lcSQL = " SELECT CASE WHEN c.ov_ok = 'Y' " + ;
765 " THEN 1 ELSE 0 " + ;
766 " END as override, " + ;
767 " c.chrg_type, " + ;
768 " c.chrg_flag, " + ;
769 " c.ov_ok, " + ;
770 " c.resolved_ok, " + ;
771 " c.chrg_formula, " + ;
772 " c.chrg_expr, " + ;
773 " c.chrg_cost, " + ;
774 " c.chrg_amt, " + ;
775 " c.percentage, " + ;
776 " c.trx_pct_amt, " + ;
777 " c.ord_num, " + ;
778 " c.pick_num, " + ;
779 " c.inv_num, " + ;
780 " c.zzxchrgd_pkey, " + ;
781 " c.vasdtlpkey, " + ;
782 " c.vashdrpkey, " + ;
783 " c.round_to, " + ;
784 " c.fkey " + ;
785 " FROM zzxchrds c " + ;
786 " JOIN " + tcInvOrders + " i " + ;
787 " ON c.hdrfkey = i.pkey "
788
789 toBPOso.cQ_ZzxChrds = SQLTableFromQuery(lcSQL)
790
791 *--- STRY0202659
792 toBPOso.logEntry("STRY0202659 - Inside FetchDtlChargesbyCursor - "+ toBPOso.cQ_ZzxChrds)
793 toBPOso.logEntry("STRY0202659 - Query - "+ lcSQL)
794 v_sqlexec("select COUNT(*) as rcnt from "+ toBPOso.cQ_ZzxChrds, "curxyz")
795 toBPOso.logEntry("STRY0202659 - record count is "+ STR(curxyz.rcnt))
796
797 *=== STRY0202659
798
799 SELECT(lnSelect)
800 RETURN llRetVal
801
802ENDFUNC
803*=== TechRec 1064560 31-Jan-2013 TShenbagavalli ===
804FUNCTION UpdateChargesAllByCursor
805 LPARAMETERS tcInvOrders,tcErrorCursor,toBPOSO,tlOpenTable
806
807 *--- TR 1060233 03/15/12 ATHIRUNAVU Added lcChargeValueString
808 LOCAL llRetVal, lnSelect, lcSQLString, lcMasterSource, lcDetailSource, lnPKey, lcDtlRollCur,lcChargeValueString
809
810 llRetVal = .T.
811 lnSelect = SELECT()
812
813 lcDtlRollCur = "#" + GetUniqueFileName()
814
815 lcMasterSource = IIF(tlOpenTable,"zzoordrh","zzoshprh")
816 lcDetailSource = IIF(tlOpenTable,"zzoordrd","zzoshprd")
817
818 WITH toBPOSO
819 IF .lResolveCharges
820
821 llRetVal = llRetVal AND RollupChargesToDetailbyCursor(tcInvOrders,lcDetailSource,lcMasterSource )
822
823 *--- TR 1043543 28-Dec-2009 Goutam
824*!* lcSQLString = "UPDATE h " + ;
825*!* " SET misc_amt = (SELECT COALESCE(SUM(Chrg_value),0) " + ;
826*!* " FROM zzxchrgs " + ;
827*!* " WHERE ord_num = h.ord_num " +;
828*!* " AND pick_num = h.pick_num " +;
829*!* " AND inv_num = h.inv_num) " + ;
830*!* " FROM " + lcMasterSource + " h " + ;
831*!* " JOIN " + tcInvOrders + " t ON h.pkey = t.pkey " + ;
832*!* " WHERE h.over_frgt <> 'P' "
833
834 *--- TechRec 1065503/1064560 20-Dec-2012/20-Feb-2013 jjanand/Shenba === Changed COALESCE
835 *--- TR 1064560/1066402 20-Feb-2013/01/24/13 shenba/ ATHIRUNAVU Removed zzxchrgh and zzxchrgd joins and used gs.chrg_type instead
836 lcSQLString = "UPDATE h " + ;
837 " SET h.misc_amt = (SELECT COALESCE(SUM(Chrg_value),h.misc_amt) " + ;
838 " FROM zzxchrgs gs " + ;
839 " join zzxchrtr tr on tr.chrg_type = gs.chrg_type " + ;
840 " WHERE gs.ord_num = h.ord_num " + ;
841 " AND gs.pick_num = h.pick_num " + ;
842 " AND gs.inv_num = h.inv_num " + ;
843 " AND tr.charge_field = " + SqlformatChar(toBPOSO.cCharge_field_misc_amt) + ")" + ;
844 " ,h.user_id = " + SQLFormatChar(GoEnv.SV("cUSER","")) + ;
845 " ,h.last_mod = " + SQLFormatTS(DATETIME()) + ;
846 " FROM " + lcMasterSource + " h " + ;
847 " JOIN " + tcInvOrders + " t ON h.pkey = t.pkey " + ;
848 " WHERE h.over_frgt <> 'P' "
849
850 *=== TR 1043543 28-Dec-2009 Goutam
851
852 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
853
854 *--- TR 1043543 28-Dec-2009 Goutam
855 *--- TechRec 1065503/1064560 20-Dec-2012/20-Feb-2013 jjanand/Shenba === Changed COALESCE
856 *--- TR 1064560/1066402 20-Feb-2013/01/24/13 shenba/ ATHIRUNAVU Removed zzxchrgh and zzxchrgd joins and used gs.chrg_type instead
857 lcSQLString = "UPDATE oh " + ;
858 " SET oh.insu_amt = (SELECT COALESCE(SUM(Chrg_value),oh.insu_amt) " + ;
859 " FROM zzxchrgs gs " + ;
860 " join zzxchrtr tr on tr.chrg_type = gs.chrg_type " + ;
861 " WHERE gs.ord_num = oh.ord_num " + ;
862 " AND gs.pick_num = oh.pick_num " + ;
863 " AND gs.inv_num = oh.inv_num " + ;
864 " AND tr.charge_field = " + SqlformatChar(toBPOSO.cCharge_field_insu_amt) + ")" + ;
865 " ,oh.user_id = " + SQLFormatChar(GoEnv.SV("cUSER","")) + ;
866 " ,oh.last_mod = " + SQLFormatTS(DATETIME()) + ;
867 " from " + lcMasterSource + " oh " + ;
868 " JOIN " + tcInvOrders + " t ON oh.pkey = t.pkey " + ;
869 " join zzxshipr pr ON pr.shipper = oh.shipper " + ;
870 " WHERE oh.over_frgt <> 'P' " + ;
871 " AND pr.insurance_ok = 'Y'"
872
873 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
874 *=== TR 1043543 28-Dec-2009 Goutam
875
876
877 *--- TR 1046460 14-May-2010 Goutam
878 *--- TR 1046459 16-JUN-2010 HNISAR
879*!* lcSQLString = "UPDATE h " + ;
880*!* " SET h.frgt_amt = (SELECT COALESCE(SUM(Chrg_value),0) " + ;
881*!* " FROM zzxchrgs gs " + ;
882*!* " join zzxchrgd gd on gd.pkey = gs.zzxchrgd_pkey " + ;
883*!* " join zzxchrgh gh on gh.pkey = gd.fkey " + ;
884*!* " join zzxchrtr tr on tr.chrg_type = gd.chrg_type " + ;
885*!* " WHERE gs.ord_num = h.ord_num " + ;
886*!* " AND gs.pick_num = h.pick_num " + ;
887*!* " AND gs.inv_num = h.inv_num " + ;
888*!* " AND tr.charge_field = " + SqlformatChar(toBPOSO.cCharge_field_frgt_amt) + ")" + ;
889*!* " ,h.user_id = " + SQLFormatChar(GoEnv.SV("cUSER","")) + ;
890*!* " ,h.last_mod = " + SQLFormatTS(DATETIME()) + ;
891*!* " FROM " + lcMasterSource + " h " + ;
892*!* " JOIN " + tcInvOrders + " t ON h.pkey = t.pkey " + ;
893*!* " WHERE h.over_frgt <> 'P' "
894
895 *--- TR 1047995 20-JUL-2010 HNISAR
896*!* lcSQLString = "UPDATE h " + ;
897*!* " SET h.frgt_amt = (SELECT COALESCE(SUM(Chrg_value),0) " + ;
898*!* " FROM zzxchrgs gs " + ;
899*!* " join zzxchrgd gd on gd.pkey = gs.zzxchrgd_pkey " + ;
900*!* " join zzxchrgh gh on gh.pkey = gd.fkey " + ;
901*!* " join zzxchrtr tr on tr.chrg_type = gd.chrg_type " + ;
902*!* " WHERE gs.ord_num = h.ord_num " + ;
903*!* " AND gs.pick_num = h.pick_num " + ;
904*!* " AND gs.inv_num = h.inv_num " + ;
905*!* " AND tr.charge_field = " + SqlformatChar(toBPOSO.cCharge_field_frgt_amt) + ")" + ;
906*!* " ,h.user_id = " + SQLFormatChar(GoEnv.SV("cUSER","")) + ;
907*!* " ,h.last_mod = " + SQLFormatTS(DATETIME()) + ;
908*!* " FROM " + lcMasterSource + " h " + ;
909*!* " JOIN " + tcInvOrders + " t ON h.pkey = t.pkey " + ;
910*!* " LEFT JOIN zzyupsbr sps on h.bill_optn = sps.bill_optn "+;
911*!* " WHERE h.over_frgt <> 'P' " +;
912*!* " AND sps.zero_frgt <>'Y'"
913
914 *--- TR 1060233 03/15/12 ATHIRUNAVU
915 *--- TR 1064560/1066402 20-Feb-2013/01/24/13 shenba/ ATHIRUNAVU Removed zzxchrgh and zzxchrgd joins and used gs.chrg_type instead
916 lcChargeValueString = "(SELECT COALESCE(SUM(Chrg_value),0) " + ;
917 " FROM zzxchrgs gs " + ;
918 " join zzxchrtr tr on tr.chrg_type = gs.chrg_type " + ;
919 " WHERE gs.ord_num = h.ord_num " + ;
920 " AND gs.pick_num = h.pick_num " + ;
921 " AND gs.inv_num = h.inv_num " + ;
922 " AND tr.charge_field = " + SqlformatChar(toBPOSO.cCharge_field_frgt_amt) + ")"
923 *=== TR 1060233 03/15/12 ATHIRUNAVU
924
925 *--- TR 1060233 03/15/12 ATHIRUNAVU Preserved the freight amount if there are not charges exists for freight
926 *--- TechRec 1059811 13-Jun-2012 TShenbagavalliadded join to zzxcustr ---
927 lcSQLString = "UPDATE h " + ;
928 " SET h.frgt_amt = CASE WHEN COALESCE(sps.zero_frgt,'') = 'Y' THEN 0 "+;
929 " ELSE CASE WHEN " + lcChargeValueString + " > 0 THEN " + lcChargeValueString + " ELSE h.frgt_Amt END " + " END" + ;
930 " ,h.user_id = " + SQLFormatChar(GoEnv.SV("cUSER","")) + ;
931 " ,h.last_mod = " + SQLFormatTS(DATETIME()) + ;
932 " FROM " + lcMasterSource + " h " + ;
933 " JOIN " + tcInvOrders + " t ON h.pkey = t.pkey " + ;
934 " JOIN zzxcustr c " + ;
935 " ON h.customer = c.customer " + ; && TR 1059811
936 " LEFT JOIN zzyupsbr sps on sps.bill_optn = case when h.bill_optn <> '' then h.bill_optn else c.bill_optn end "+; && TR 1059811 added case when
937 " WHERE h.over_frgt <> 'P' "
938 *=== TR 1060233 03/15/12 ATHIRUNAVU
939
940 *=== TR 1047995 20-JUL-2010 HNISAR
941 *=== TR 1046459 16-JUN-2010 HNISAR
942
943 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
944 *--- TR 1046460 14-May-2010 Goutam
945
946 *--- TechRec 1075078 04-Dec-2013 jisingh ---
947 * TR 1084390 KISHORE 4-FEB-2015 added joins to zzxchrds, zzxchrtr and filter tr.aggr = 'Y'
948 lcSQLString = " UPDATE od " + ;
949 " SET od.price = od.base_price + (SELECT COALESCE(SUM(CASE WHEN ds.chrg_flag = 'F' THEN ds.chrg_amt " + ;
950 " WHEN ds.chrg_flag = 'U' THEN ds.chrg_amt " + ;
951 " WHEN ds.chrg_flag = 'N' THEN ds.trx_pct_amt/CASE WHEN d.total_qty <> 0 THEN d.total_qty ELSE 1 END " + ; && *--- TR 1075078 8-1-2014 VKK
952 " WHEN ds.chrg_flag = 'P' THEN ds.trx_pct_amt END), 0) " + ;
953 " FROM zzxchrds ds " + ;
954 " JOIN " + lcDetailSource + " d " + ;
955 " ON ds.fkey = d.pkey " + ;
956 " JOIN zzxchrtr tr " + ;
957 " ON tr.chrg_type = ds.chrg_type " + ;
958 " WHERE ds.ord_num = oh.ord_num " + ;
959 " AND ds.pick_num = oh.pick_num " + ;
960 " AND ds.inv_num = oh.inv_num " + ;
961 " AND d.pkey = od.pkey " + ;
962 " AND tr.aggr = 'Y') " + ;
963 " ,od.user_id = " + SQLFormatChar(goEnv.SV("cUser","")) + ;
964 " ,od.last_mod = " + SQLFormatTS(DATETIME()) + ;
965 " FROM " + lcDetailSource + " od " + ;
966 " JOIN " + lcMasterSource + " oh " + ;
967 " ON oh.pkey = od.fkey " + ;
968 " JOIN " + tcInvOrders + " t " + ;
969 " ON oh.pkey = t.pkey " + ;
970 " JOIN zzxchrds ds ON ds.fkey = od.pkey " + ;
971 " JOIN zzxchrtr tr ON tr.chrg_type = ds.chrg_type AND tr.aggr = 'Y' " + ;
972 " /*WHERE (SELECT COALESCE(SUM(CASE WHEN ds.chrg_flag = 'F' THEN ds.chrg_amt " + ;
973 " WHEN ds.chrg_flag = 'U' THEN ds.chrg_amt " + ;
974 " WHEN ds.chrg_flag = 'N' THEN ds.trx_pct_amt/CASE WHEN d.total_qty <> 0 THEN d.total_qty ELSE 1 END " + ; && *--- TR 1075078 8-1-2014 VKK
975 " WHEN ds.chrg_flag = 'P' THEN ds.trx_pct_amt END), 0) " + ;
976 " FROM zzxchrds ds " + ;
977 " JOIN " + lcDetailSource + " d " + ;
978 " ON ds.fkey = d.pkey " + ;
979 " JOIN zzxchrtr tr " + ;
980 " ON tr.chrg_type = ds.chrg_type " + ;
981 " WHERE ds.ord_num = oh.ord_num " + ;
982 " AND ds.pick_num = oh.pick_num " + ;
983 " AND ds.inv_num = oh.inv_num " + ;
984 " AND d.pkey = od.pkey " + ;
985 " AND tr.aggr = 'Y') <> 0*/ "
986
987 llRetVal = llRetVal AND v_SQLExec(lcSQLString)
988 *=== TechRec 1075078 04-Dec-2013 jisingh ===
989 ENDIF
990 ENDWITH
991
992 SELECT (lnSelect)
993 RETURN llRetVal
994ENDFUNC
995
996*--------------------------------------------------
997
998FUNCTION BuildInsertChargeString
999*--- TR 1073530 16-9-2013 VKK Added tcCartonAlias
1000 LPARAMETERS tcWorkAlias ,toBPOSO, tcCartonAlias && tcChargeString is output parameter, pass by reference && tcTaxTable is output alias.
1001
1002 LOCAL llRetVal, lnSelect , lcSQLString
1003 PRIVATE pnInv_num,pnLine_seq,pnOrd_num,pnpick_num,pntax_type,pndtl_pkey,;
1004 pcchrg_type,pcdivision,pntax_rate,pnTaxValue, pcUser, ptLast_mod,;
1005 pcChrg_Flag,pcov_ok,pcov_amt ,pcov_pct ,pcchrg_expr ,pcchrg_formula ,pczzxchrgd_pkey
1006
1007 pcUser = GoEnv.SV("cUSER","")
1008 ptLast_Mod = DATETIME()
1009
1010 llRetVal = .T.
1011 lnSelect = SELECT()
1012
1013 WITH toBPOSO
1014
1015 SELECT (tcWorkAlias)
1016 SCAN
1017 .nChargeEvalvalue = 0
1018 *--- TR 1073530 16-9-2013 VKK Added tcWorkAlias,tcCartonAlias
1019 llRetVal = llRetVal AND EvalChargeFormula(chrg_type, chrg_formula, chrg_Expr,toBPOSO, tcWorkAlias, tcCartonAlias)
1020
1021 IF .lValidCharge
1022 pnInv_num = inv_num
1023 pnOrd_num = ord_num
1024 pnpick_num = pick_num
1025 pndtl_pkey = Hdr_Pkey
1026 pcdivision = division
1027 pcchrg_type = chrg_type
1028 pcChrg_Flag = Chrg_Flag
1029 pcov_ok = override
1030 pcov_amt = IIF(Override = 'Y', Chrg_Amt, 0)
1031 pcov_pct = IIF(Override = 'Y', Percentage, 0)
1032 pcchrg_expr = chrg_expr
1033 pcchrg_formula = old_chrg_formula
1034 pczzxchrgd_pkey = pkey
1035
1036 pnTaxValue = .nChargeEvalvalue
1037
1038 llRetVal = llRetVal AND v_sqlexec(;
1039 " INSERT INTO zzxchrgs " + ;
1040 " (inv_num,ord_num,pick_num,chrg_type,chrg_flag,chrg_value,ov_ok," + ;
1041 " ov_amt,ov_pct,fkey,last_mod,user_id," + ;
1042 " division,doc_num,cmem_num,chrg_expr,chrg_formula,notes,zzxchrgd_pkey )" + ;
1043 " VALUES " + ;
1044 " (?pnInv_num ,?pnOrd_num ,?pnpick_num ,?pcchrg_type,?pcChrg_Flag,?pnTaxValue, ?pcov_ok," + ;
1045 " ?pcov_amt,?pcov_pct,?pndtl_pkey ,?ptLast_mod,?pcUser," + ;
1046 " ?pcdivision ,0,0,?pcchrg_expr ,?pcchrg_formula , '', ?pczzxchrgd_pkey )")
1047 ENDIF
1048
1049 ENDSCAN
1050
1051 ENDWITH
1052
1053 SELECT (lnSelect)
1054 RETURN llRetVal
1055ENDFUNC
1056
1057*-------------------------------------------------------
1058
1059FUNCTION EvalChargeFormula
1060 *--- TechRec 1064560 13-Mar-2013 TShenbagavalli added parameter tnRount_to ---
1061 *--- TR 1073530 16-9-2013 VKK Removed tnRound_to as it is not used and added tcCartonAlias
1062 LPARAMETERS tcChargetype, tcFormula, tcExpr,toBPOSO, tcWorkAlias, tcCartonAlias
1063 LOCAL llRetVal, lnSelect, lcTaxType, lcFormula, lcError, lcWorkAlias,lcChrgFlag, lnhdr_pkey && TR 1073530 16-9-2013 VKK lcWorkalias,lcChrgFlag , lnhdr_pkey
1064
1065 llRetVal = .T.
1066
1067 lnSelect = SELECT()
1068 lcTaxType = ALLTRIM(tcChargetype)
1069 lcFormula = ALLTRIM(tcFormula)
1070 lcExpr = ALLTRIM(tcExpr)
1071
1072 *--- TR 1073530 16-9-2013 VKK
1073 lcWorkAlias = IIF(EMPTY(tcWorkAlias), ALIAS(),tcWorkAlias)
1074 lcChrgFlag = SysGetFieldValue(lcWorkAlias , "Chrg_flag")
1075 lnHdr_Pkey = 0
1076 IF Fieldexists("Hdr_Pkey",lcWorkAlias )
1077 lnhdr_pkey = SysGetFieldValue(lcWorkAlias , "Hdr_Pkey")
1078 ENDIF
1079
1080 *=== TR 1073530 16-9-2013 VKK
1081 toBPOSO.lValidCharge = .T.
1082 lcError = ON("ERROR")
1083 ON ERROR llRetVal = .F.
1084
1085 IF NOT EMPTY(tcFormula)
1086 toBPOSO.nChargeEvalValue = EVALUATE(lcFormula)
1087 ENDIF
1088
1089 *--- TR 1073530 16-9-2013 VKK
1090 IF lcChrgFlag = "C" AND NOT EMPTY(tcCartonAlias ) AND USED(tcCartonAlias ) && Carton type And header charge only
1091
1092 IF NOT EMPTY(tcExpr)
1093 * This will be a filter expression. IF .f. then we will not take the charge type into account.
1094 SELECT (tcCartonAlias )
1095 toBPOSO.nChargeEvalValue = 0
1096 CALCULATE SUM(Carton_Count) TO toBPOSO.nChargeEvalValue FOR &tcExpr AND Chrg_type = tcChargetype AND hdr_pkey = lnhdr_pkey
1097 toBPOSO.nChargeEvalValue = toBPOSO.nChargeEvalValue * SysGetFieldValue(lcWorkAlias , "chrg_amt")
1098 SELECT (lnSelect)
1099 *.lValidCharge = EVALUATE(lcExpr)
1100 ENDIF
1101
1102 ELSE
1103 *=== TR 1073530 16-9-2013 VKK
1104 * This will be a filter expression. IF .f. then we will not take the charge type into account.
1105 *--- TR 1073530 16-9-2013 VKK Get rid of carton_code it is needed only for C type
1106 lcExpr = STRTRAN(UPPER(lcExpr),"CARTON_CODE", "''")
1107 *=== TR 1073530 16-9-2013 VKK
1108
1109 *--- TR 1074353 05-Nov-13 SK Added TRY...CATCH and Evaluvating only when lcExpr is not empty.
1110 TRY
1111 IF NOT EMPTY(tcExpr)
1112 toBPOSO.lValidCharge = EVALUATE(lcExpr)
1113 ENDIF
1114 CATCH
1115 llRetVal = .F.
1116 ENDTRY
1117 *=== TR 1074353 05-Nov-13 SK
1118 ENDIF && *--- TR 1073530 16-9-2013 VKK
1119
1120 toBPOSO.nChargeEvalValue = EVALUATE(lcFormula)
1121
1122 ON ERROR &lcError && restore system error handler
1123
1124 SELECT (lnSelect)
1125
1126 RETURN llRetval
1127ENDFUNC
1128
1129*-------------------------------------------------
1130
1131FUNCTION RollupChargesToHeaderbyCursor
1132 LPARAMETERS tcInvOrders, tcDetailSource, toBPOso && *--- TR 1064560 5-3-2013 VKK added tobposo
1133
1134 LOCAL llRetVal,lnSelect,lcSQL,lcChrgTmp
1135 llRetVal = .T.
1136 lnSelect = SELECT()
1137
1138 *--- TechRec 1064560 13-Feb-2013 TShenbagavalli ---
1139 llRetVal = llRetVal AND RecalcDetailCharges(tcInvOrders, tcDetailSource, toBPOso )
1140 *=== TechRec 1064560 13-Feb-2013 TShenbagavalli ===
1141
1142 lcSQL = "DELETE " + ;
1143 " FROM zzxchrgs where pkey IN " + ;
1144 " (SELECT a.pkey FROM zzxchrgs a " + ;
1145 " JOIN " + tcInvOrders + " tmp " + ;
1146 " ON a.ord_num = tmp.ord_num " + ;
1147 " and a.pick_num = tmp.pick_num " + ;
1148 " AND a.inv_num = tmp.inv_num " + ;
1149 " AND a.ov_ok = 'D' ) "
1150 llRetval = llRetval AND V_SQLExec(lcSQL)
1151
1152 lcSQL = "DELETE " + ;
1153 " FROM zzxchrgs where pkey IN " + ;
1154 " (SELECT a.pkey FROM zzxchrgs a " + ;
1155 " JOIN " + tcInvOrders + " tmp " + ;
1156 " ON a.fkey = tmp.pkey " + ;
1157 " AND a.ov_ok = 'D' ) "
1158 llRetval = llRetval AND V_SQLExec(lcSQL)
1159
1160 *---TR 1044305 12/18/09 YE === Exclude cancelled lines.
1161 *--- TechRec 1064560 22-Feb-2013 TShenbagavalli added 'F' as Chrg_flag and removed c.chrg_flag from select list removed c.chrg_flag from group by---
1162 lcSQL = " SELECT d.division, d.ord_num, d.pick_num, d.inv_num, c.chrg_type, 'F' as chrg_flag, SUM(c.chrg_amt) AS chrg_amt, c.hdrfkey AS fkey, " + ;
1163 " SUM(CASE WHEN c.chrg_flag = 'F' THEN c.chrg_amt WHEN c.chrg_flag IN('P', 'N') THEN c.trx_pct_amt ELSE d.total_qty * c.chrg_amt END) AS chrg_value " + ; && 1064560 added WHEN c.chrg_flag = 'P' THEN c.trx_pct_amt && *--- TR 1075078 8-1-2014 VKK dded 'N'
1164 " FROM zzxchrds c " + ;
1165 " JOIN " + tcDetailSource + " d ON c.fkey = d.pkey " + ;
1166 " JOIN " + tcInvOrders + " h " + ;
1167 " ON d.ord_num = h.ord_num " + ;
1168 " AND d.pick_num = h.pick_num " + ;
1169 " AND d.inv_num = h.inv_num AND d.line_status <> 'C' " + ; && TR 1044305
1170 " GROUP BY c.hdrfkey, d.division, d.ord_num, d.pick_num, d.inv_num, c.chrg_type"
1171
1172 lcChrgTmp = SQLTableFromQuery(lcSQL)
1173
1174 loSB = NewObject("SQLStringBuilder","clsgnstr.fxp")
1175 WITH loSB
1176 .SB_Reset()
1177 .cSB_Target = "zzxchrgs"
1178 .cSB_Source = lcChrgTmp
1179 .cSB_SourceAlias = "h"
1180 .SB_SetLiteralFieldValue("ov_ok","'D'")
1181 .lSB_UsePKeyGen = .F.
1182 .lKeyGenExpressionSet = "N"
1183 llRetVal = llRetVal AND .SB_GenerateSQLInsertString()
1184 llRetVal = llRetVal AND v_sqlexec(.cSqlString)
1185 ENDWITH
1186
1187 IF NOT EMPTY(lcChrgTmp)
1188 v_sqlexecnoerror("DROP TABLE " + lcChrgTmp)
1189 ENDIF
1190
1191 SELECT(lnSelect)
1192 RETURN llRetVal
1193
1194ENDFUNC
1195
1196*---------------------------------------------------
1197
1198FUNCTION RollupChargesToDetailbyCursor
1199 LPARAMETERS tcInvOrders, tcDetailSource, tcHeaderSource
1200
1201 LOCAL llRetVal,lnSelect,lcSQL
1202 llRetVal = .T.
1203 lnSelect = SELECT()
1204
1205 lcSQL = "update d " + ;
1206 " set d.chrg_value = COALESCE(c.chrg_value, 0) " + ;
1207 " , d.chrg_cost = COALESCE(c.chrg_cost ,0) " + ;
1208 " from " + tcDetailSource + " d " + ;
1209 " join " + tcInvOrders + " tmp ON d.fkey = tmp.pkey " + ;
1210 " JOIN " + tcHeaderSource + " h ON tmp.pkey = h.pkey " + ;
1211 " left join ( " + ;
1212 " select c.fkey " + ;
1213 " , SUM(CASE " + ;
1214 " when c.chrg_flag = 'F' THEN c.chrg_amt " + ;
1215 " WHEN c.chrg_flag IN( 'P', 'N') THEN c.trx_pct_amt " + ; && 1064560 && *--- TR 1075078 8-1-2014 VKK added 'N'
1216 " else d.total_qty * c.chrg_amt END) AS chrg_value " + ;
1217 " , SUM(CASE " + ;
1218 " when c.chrg_flag = 'F' THEN c.chrg_cost " + ;
1219 " WHEN c.chrg_flag IN( 'P', 'N') THEN c.trx_pct_amt " + ; && 1064560 && *--- TR 1075078 8-1-2014 VKK added 'N'
1220 " else d.total_qty * c.chrg_cost END) AS chrg_cost " + ;
1221 " from zzxchrds c " + ;
1222 " join " + tcDetailSource + " d " + ;
1223 " join " + tcInvOrders + " x ON d.fkey = x.pkey " + ;
1224 " on c.fkey = d.pkey " + ;
1225 " group by c.fkey) c " + ;
1226 " on c.fkey = d.pkey " + ;
1227 " where h.over_frgt <> 'P' "
1228
1229 *--- TechRec 1075078 11-Feb-2014 jisingh ---
1230 llRetVal = llRetVal AND v_SQLExec(lcSQL)
1231 *=== TechRec 1075078 11-Feb-2014 jisingh ===
1232
1233 SELECT(lnSelect)
1234 RETURN llRetVal
1235
1236ENDFUNC
1237
1238*-----------------------------------------------------
1239
1240FUNCTION FetchChargesbyCursor
1241 LPARAMETERS tcInvOrders, toBPOso, to945log
1242
1243 LOCAL llRetVal,lnSelect,lcSQL
1244 llRetVal = .T.
1245 lnSelect = SELECT()
1246
1247 lcSQL = " SELECT CASE WHEN c.ov_ok = 'Y' " + ;
1248 " THEN 1 ELSE 0 " + ;
1249 " END as override, " + ;
1250 " c.chrg_type, " + ;
1251 " c.chrg_flag, " + ;
1252 " c.ov_pct, " + ;
1253 " c.ov_amt, " + ;
1254 " c.chrg_formula, " + ;
1255 " c.chrg_expr, " + ;
1256 " c.chrg_value, " + ;
1257 " c.ov_pct as old_ov_pct, " + ;
1258 " c.ov_amt as old_ov_Amt, " + ;
1259 " c.ord_num, " + ;
1260 " c.pick_num, " + ;
1261 " c.inv_num, " + ;
1262 " c.zzxchrgd_pkey, " + ;
1263 " 0 AS dtlcharge " + ;
1264 " FROM zzxchrgs c " + ;
1265 " JOIN " + tcInvOrders + " i " + ;
1266 " ON c.ord_num = i.ord_num " + ;
1267 " AND c.pick_num = i.pick_num " + ;
1268 " AND c.inv_num = i.inv_num " + ;
1269 " WHERE c.ov_ok <> 'D' "
1270
1271 toBPOso.cQ_ZzxChrgs = SQLTableFromQuery(lcSQL)
1272
1273 *--- STRY0202659
1274 IF TYPE('to945Log')= "O"
1275 to945log.logEntry("STRY0202659 - Inside FetchChargesbyCursor - "+ toBPOso.cQ_ZzxChrgs)
1276 to945log.logEntry("STRY0202659 - Query - "+ lcSQL)
1277 v_sqlexec("select COUNT(*) as rcnt from "+ toBPOso.cQ_ZzxChrgs, "curxyz")
1278 to945log.logEntry("STRY0202659 - record count is "+ STR(curxyz.rcnt))
1279 endif
1280
1281 *=== STRY0202659
1282
1283 SELECT(lnSelect)
1284 RETURN llRetVal
1285
1286ENDFUNC
1287
1288*--- TechRec 1064560 14-Feb-2013 TShenbagavalli ---
1289FUNCTION ResolveDetailChargesbycursor
1290 LPARAMETERS tcMasterSource, tcDetailSource, tcMode, tcInvOrders, toBPOso, tlEvaluateChrg, to945log
1291 LOCAL llRetVal, lnSelect, lcSQLString, lcWorkAlias, lcOrdDtlFilterFields
1292
1293 llRetVal = true
1294 lnSelect = SELECT()
1295 lcWorkAlias = GetUniqueFileName()
1296
1297 lcWorkAlias_1 = GetUniqueFileName()
1298 lcWorkAlias_2 = GetUniqueFileName()
1299
1300 WITH toBPOso
1301
1302 IF TYPE('to945Log')= "O"
1303 to945log.logEntry("STRY0202659 - Inside ResolveDetailChargesbyCursor") &&STRY0202659
1304 endif
1305
1306 lcOrdDtlFilterFields = UPPER(.cOrdDtlFilterFields)
1307 lcOrdDtlFilterFields = STRTRAN(lcOrdDtlFilterFields, "D.SHIPPER", "H.SHIPPER")
1308 lcOrdDtlFilterFields = STRTRAN(lcOrdDtlFilterFields, "D.TERMS", "H.TERMS")
1309 lcOrdDtlFilterFields = STRTRAN(lcOrdDtlFilterFields, "D.ORD_TYPE", "H.ORD_TYPE")
1310 *--- TR 1073530 15-9-2013 VKK
1311 lcOrdDtlFilterFields = STRTRAN(lcOrdDtlFilterFields, "D.EOD_ACTUAL_FRGT", "H.EOD_ACTUAL_FRGT")
1312 *=== TR 1073530 15-9-2013 VKK
1313
1314 *--- TR 1086313 KISHORE 13-APR-2015
1315 lcCounts = ''
1316 IF 'PICK_CNT' $ lcOrdDtlFilterFields OR 'INV_CNT' $ lcOrdDtlFilterFields
1317 lcOrdDtlFilterFields = STRTRAN(UPPER(lcOrdDtlFilterFields ), 'D.PICK_CNT', 'COUNTS.PICK_CNT')
1318 lcOrdDtlFilterFields = STRTRAN(UPPER(lcOrdDtlFilterFields ), 'D.INV_CNT', 'COUNTS.INV_CNT')
1319
1320 TEXT TO lcCounts NOSHOW TEXTMERGE
1321 JOIN (SELECT ord_num, SUM(pick_cnt) pick_cnt, SUM(inv_cnt) inv_cnt
1322 FROM (SELECT ord_num, SUM(case when pick_num > 0 then 1 else 0 end) as pick_cnt,
1323 0 as inv_cnt
1324 FROM zzoordrh
1325 GROUP BY ord_num
1326 UNION ALL
1327 SELECT ord_num, SUM(case when pick_num > 0 then 1 else 0 end) as pick_cnt,
1328 SUM(case when inv_num > 0 then 1 else 0 end) inv_cnt
1329 FROM zzoshprh
1330 GROUP BY ord_num) cnt
1331 GROUP BY ord_num
1332 ) counts
1333 ON counts.ord_num = h.ord_num
1334 ENDTEXT
1335
1336 ENDIF
1337 *=== TR 1086313 KISHORE 13-APR-2015
1338
1339 lcSQLString = "SELECT DISTINCT h.ord_num, " + ;
1340 " h.pick_num, " + ;
1341 " h.inv_num, " + ;
1342 " h.customer, " + ;
1343 " h.cncl_num, " + ;
1344 " h.pkey as hdr_pkey, " + ;
1345 " h.frgt_Amt , " + ;
1346 " h.disc_Amt , " + ;
1347 " h.insu_amt , " + ;
1348 " h.center_Code, " + ;
1349 " h.store, " + ;
1350 " h.season, " + ; && TR 1064560
1351 " CAST(d.merch_Amt AS NUMERIC(15,2)) as merch_amt, " + ;
1352 .cChrgOrdHdrFilterFields + ;
1353 lcOrdDtlFilterFields + ;
1354 " h.division, " + ;
1355 " 0 Chrgh_pkey, " + ;
1356 " d.netmer_amt, " + ;
1357 " /*h.eod_actual_frgt, */" + ; && *--- TR 1073530 15-9-2013 VKK Commented , itis added in lcorddtlfitlerfields now
1358 " h.carton, " + ;
1359 " c.cust_type, " + ;
1360 " coalesce(zd1.zone_udfc1,zd2.zone_udfc1,'') as zone_udfc1, " + ;
1361 " d.pkey as Dtl_Pkey, " +;
1362 " d.price, " + ;
1363 " d.base_price, " + ; &&--- TechRec 1075078 04-Dec-2013 jisingh ===
1364 " d.style, d.color_code, d.lbl_code,d.dimension, " + ;
1365 " d.group_code1,d.group_code2,d.group_code3,d.group_code4, " + ;
1366 " d.group_code5,d.group_code6,d.group_code7,d.group_code8 " +;
1367 " ,d.group_code9,d.group_code10,d.group_code11,d.group_code12,d.group_code13,h.source,h.location,ad.s_state ship_to_state " +; && *--- TR 1073530 15-9-2013 VKK
1368 " ,d.FOBCST " + ; && TR 1077393 20-03-2014 TShenbagavalli
1369 " ,ad.s_country shipto_country " + ; &&--- TechRec 1077586 27-Mar-2014 jisingh ===
1370 " ,coalesce(cnty.int_region_code,'') as int_region_code " + ; &&--- TechRec 1077586 27-Mar-2014 jisingh ===
1371 " FROM " + tcMasterSource + " h " + ;
1372 " JOIN " + tcInvOrders + " x ON h.pkey = x.pkey " + ;
1373 " JOIN zzxcustr c on c.customer = h.customer " + ; &&&& 1054679 AZ
1374 " JOIN ( SELECT d.fkey, d.pkey, d.style, d.color_code, d.lbl_code,d.dimension," + ;
1375 " s.group_code1,s.group_code2,s.group_code3,s.group_code4, " + ;
1376 " s.group_code5,s.group_code6,s.group_code7,s.group_code8, " +;
1377 " s.group_code9,s.group_code10,s.group_code11,s.group_code12, s.group_code13," +; && *--- TR 1073530 15-9-2013 VKK
1378 " Total_Qty, d.price, d.base_price, d.line_status, " + ; &&--- TechRec 1075078 04-Dec-2013 jisingh Added d.base_price ===
1379 " (d.price * d.Total_Qty) as Merch_Amt, " + ;
1380 " CAST((d.price * d.total_qty - (d.price * d.total_qty * " + ;
1381 SQLfnIFNull("dsc.disc_perc","0") + "/100)) AS NUMERIC(15,2)) AS Netmer_amt, " + ;
1382 " CAST((d.price * d.total_qty - (d.price * d.total_qty * " + ;
1383 SQLfnIFNull("dsc.disc_perc","0") + "/100)) AS NUMERIC(15,2)) AS ord_Net_Line_amount, " + ;
1384 " CAST((d.price * d.total_qty) AS NUMERIC(15,2)) AS ord_gross_line_amount " + ; &&--TR 1090555 30OCT2015 Dilip
1385 " , COALESCE(lc.fob_cost,0) * d.total_qty as FOBCST " + ; &&--- TechRec 1077393 20-Mar-2014 TShenbagavalli ===
1386 " FROM " + tcDetailSource + " d " + ;
1387 " JOIN " + tcInvOrders + " x ON d.fkey = x.pkey " + ;
1388 " JOIN zzxscolr s " + ;
1389 " ON s.division = d.division AND s.style = d.style AND s.color_code = d.color_code " + ;
1390 " AND s.lbl_code = d.lbl_code and s.dimension = d.dimension " + ;
1391 " LEFT OUTER JOIN zzxdiscr dsc " + ;
1392 " ON dsc.discount = d.discount " + ;
1393 " LEFT JOIN zzdlocst lc " + ; &&--- TechRec 1077393 20-Mar-2014 TShenbagavalli added zzdlocst join ===
1394 " ON lc.division = d.division AND lc.style = d.style AND lc.color_code = d.color_code " + ;
1395 " AND lc.lbl_code = d.lbl_code and lc.dimension = d.dimension AND lc.location = d.location " + ;
1396 " ) d " + ;
1397 " ON h.pkey = d.fkey " + ;
1398 " JOIN zzordad2 ad ON h.ord_num = ad.ord_num AND h.pick_num = ad.pick_num AND h.inv_num = ad.inv_num and h.cncl_num = ad.cncl_num " + ;
1399 " LEFT JOIN zzxshipr sh ON h.shipper = sh.shipper " + ;
1400 " LEFT JOIN zzxcntyr cnty ON cnty.country = ad.s_country " + ; &&--- TechRec 1077586 27-Mar-2014 jisingh ===
1401 " LEFT JOIN zzyzoneh zh ON h.location = zh.location and h.shipper = zh.shipper " + ; && TR1052821 FGCjr 03/10/11 add location
1402 " AND zh.pkey = (SELECT MAX(pkey) FROM zzyzoneh eh WHERE zh.location = eh.location and zh.shipper = eh.shipper)" +; &&& 1057926 Removed When and put under LEFT JOIN &&1091423 added zh.location = eh.location and
1403 " LEFT JOIN zzyzoned zd1 ON zh.pkey = zd1.fkey AND LEFT(ad.s_zipcode,5) = zd1.zip3 " + ;
1404 " LEFT JOIN zzyzoned zd2 ON zh.pkey = zd2.fkey AND LEFT(ad.s_zipcode,3) = zd2.zip3 AND zd1.pkey IS NULL " + ;
1405 lcCounts && TR 1086313 KISHORE 13-APR-2015
1406* " WHERE zh.pkey = (SELECT MAX(pkey) FROM zzyzoneh eh WHERE zh.shipper = eh.shipper) OR zh.pkey IS NULL " &&--- TechRec 1051033 29-Nov-2010 MANI. Added 1 line (Where contion)=== &&& 1057926
1407
1408 .cQ_OrdAddrChgDtl = SQLTableFromQuery(lcSqlString)
1409 llRetVal = llRetVal AND !EMPTY(.cQ_OrdAddrChgDtl)
1410 *--- STRY0202659
1411 IF TYPE('to945Log')= "O"
1412 to945log.logEntry("STRY0202659 - .cQ_OrdAddrChgDtl Query - "+ lcsqlstring)
1413 v_sqlexec("select COUNT(*) as rcnt from "+ .cQ_OrdAddrChgDtl, "curxyz")
1414 to945log.logEntry("STRY0202659 - records found are "+ STR(curxyz.rcnt))
1415 endif
1416 *=== STRY0202659
1417
1418 * --- TR 1041118 14-Aug-2009 Surinder Singh ---
1419 * Added condition for center_code and store in the below code
1420 * changed oder by from rank_Seq to rank_Seq , division desc, customer desc, center_code desc, store desc
1421 *--- TR 1043853 24-Nov-2009 Goutam. Added Resolve flag in following sql.
1422 *--- TR 1046460 4-May-2010 Goutam. Added (ZONE_UDFC1 = ZONE_UDFC1 OR ZONE_UDFC1 = '') and zone_udfc1 in order by
1423 *--- TechRec 1064560 07-Feb-2013 TShenbagavalli Added AND (season = " + .cQ_ordAddrDtl + ".season OR season = '') and season in order by---
1424 lcResolveaddr = SQLTRANSLATETOPN( + ;
1425 "SELECT TOP 1 pkey FROM zzxchrgh " + ;
1426 " WHERE Resolve_ok = 'Y' " + ;
1427 " AND (customer = " + .cQ_OrdAddrChgDtl+ ".customer OR customer = '') " +;
1428 " AND (division = " + .cQ_OrdAddrChgDtl+ ".division OR division = '') " +;
1429 " AND (Center_Code = " + .cQ_OrdAddrChgDtl+ ".center_code OR Center_Code = '') " +;
1430 " AND (Store = " + .cQ_OrdAddrChgDtl+ ".Store OR Store = '') " +;
1431 " AND (cust_type = " + .cQ_OrdAddrChgDtl+ ".Cust_type OR Cust_type = '') "+ ; &&& 1054679
1432 " AND (zone_udfc1 = " + .cQ_OrdAddrChgDtl+ ".zone_udfc1 OR zone_udfc1 = '') " + ;
1433 " AND (season = " + .cQ_OrdAddrChgDtl+ ".season OR season = '') " + ; && Tr 1064560
1434 " AND (ord_type = " + .cQ_OrdAddrChgDtl+ ".ord_type OR ord_type= '') " + ; && *--- TR 1073530 15-9-2013 VKK
1435 " AND (ord_source = " + .cQ_OrdAddrChgDtl+ ".source OR ord_source= '') " + ; && *--- TR 1073530 15-9-2013 VKK
1436 " AND (location = " + .cQ_OrdAddrChgDtl+ ".location OR location = '') " + ; && && *--- TR 1073530 15-9-2013 VKK
1437 " AND (ship_to_state= " + .cQ_OrdAddrChgDtl+ ".ship_to_state OR ship_to_state = '') " + ; && *--- TR 1073530 15-9-2013 VKK
1438 " AND (shipto_country = " + .cQ_OrdAddrChgDtl + ".shipto_country OR shipto_country = '') " + ; &&--- TechRec 1077586 27-Mar-2014 jisingh ===
1439 " AND (int_region_code = " + .cQ_OrdAddrChgDtl + ".int_region_code OR int_region_code = '') " + ; &&--- TechRec 1077586 27-Mar-2014 jisingh ===
1440 " AND (style = " + .cQ_OrdAddrChgDtl+ ".style OR style = '') " + ;
1441 " AND (color_code = " + .cQ_OrdAddrChgDtl+ ".color_code OR color_code = '')" + ;
1442 " AND (lbl_code = " + .cQ_OrdAddrChgDtl+ ".lbl_code OR lbl_code = '')" + ;
1443 " AND (dimension = " + .cQ_OrdAddrChgDtl+ ".dimension OR dimension = '')" + ;
1444 " AND (group_code1 = " + .cQ_OrdAddrChgDtl+ ".group_code1 OR group_code1 = '')" + ;
1445 " AND (group_code2 = " + .cQ_OrdAddrChgDtl+ ".group_code2 OR group_code2 = '')" + ;
1446 " AND (group_code3= " + .cQ_OrdAddrChgDtl+ ".group_code3 OR group_code3 = '')" + ;
1447 " AND (group_code4 = " + .cQ_OrdAddrChgDtl+ ".group_code4 OR group_code4 = '')" + ;
1448 " AND (group_code5 = " + .cQ_OrdAddrChgDtl+ ".group_code5 OR group_code5 = '')" + ;
1449 "AND (group_code6 = " + .cQ_OrdAddrChgDtl+ ".group_code6 OR group_code6 = '')" + ;
1450 "AND (group_code7 = " + .cQ_OrdAddrChgDtl+ ".group_code7 OR group_code7 = '')" + ;
1451 " AND (group_code8= " + .cQ_OrdAddrChgDtl+ ".group_code8 OR group_code8 = '')" + ;
1452 " AND (group_code9 = " + .cQ_OrdAddrChgDtl+ ".group_code9 OR group_code9 = '')" + ; && *--- TR 1073530 15-9-2013 VKK
1453 " AND (group_code10 = " + .cQ_OrdAddrChgDtl+ ".group_code10 OR group_code10 = '')" + ; && *--- TR 1073530 15-9-2013 VKK
1454 " AND (group_code11= " + .cQ_OrdAddrChgDtl+ ".group_code11 OR group_code11 = '')" + ; && *--- TR 1073530 15-9-2013 VKK
1455 " AND (group_code12 = " + .cQ_OrdAddrChgDtl+ ".group_code12 OR group_code12 = '')" + ; && *--- TR 1073530 15-9-2013 VKK
1456 " AND (group_code13 = " + .cQ_OrdAddrChgDtl+ ".group_code13 OR group_code13 = '')" + ; && *--- TR 1073530 15-9-2013 VKK
1457 " ORDER BY Rank_seq, division desc, customer desc, center_code desc, store desc, zone_udfc1 desc, season desc," + ;
1458 " style desc, color_code desc,lbl_code desc, dimension desc, group_code1 desc, group_code2 desc, " + ;
1459 " group_code3 desc, group_code4 desc, group_code5 desc,group_code6 desc, group_code7 desc, group_code8 desc, " + ;
1460 " group_code9 desc, group_code10 desc,group_code11 desc, group_code12 desc, group_code13 desc, shipto_country desc, int_region_code desc ") && *--- TR 1073530 15-9-2013 VKK &&--- TechRec 1077586 27-Mar-2014 jisingh ===
1461
1462 * === TR 1041118 14-Aug-2009 Surinder Singh ===
1463 lcSQLString = " UPDATE " + .cQ_OrdAddrChgDtl+ ;
1464 " SET chrgh_pkey = COALESCE( ( " + lcResolveaddr + " ),0)"
1465 llRetVal = llRetVal AND V_SqlExec(lcSQLString)
1466
1467 && Error when no tax header record found for any of the detail line.
1468
1469 lcSQLString = "SELECT COUNT(*) as nChrgCnt " + ;
1470 " FROM " + .cQ_OrdAddrChgDtl+ ;
1471 " WHERE Chrgh_pkey = 0 "
1472
1473 llRetVal = llRetVal AND V_SQLExec(lcSQLString, lcWorkAlias_1) AND USED(lcWorkAlias_1)
1474
1475 *--- STRY0202659
1476 IF TYPE('to945Log')= "O"
1477 IF llRetVal
1478 to945log.logEntry("STRY0202659 - lcWorkAlias_1 has "+ STR(RECCOUNT(lcWorkAlias_1))+ "records")
1479 ELSE
1480 to945log.logEntry("STRY0202659 - lcWorkAlias_1 alias not ")
1481 ENDIF
1482 endif
1483
1484 IF llRetVal
1485 SELECT(lcWorkAlias_1)
1486
1487 IF nChrgCnt > 0
1488 .cChargeErrorMsg = .cChargeErrorMsg + CRLF + ;
1489 "Charge header could not be found for order detail record match."
1490 llRetVal = false
1491 .lMatchChargeReference = False
1492 ENDIF
1493
1494 ENDIF
1495*******************************
1496 *--- 1073611 07/08/14 Ilya: Set default resolved_ok to N for 850 resolution
1497 LOCAL lcDefaultResolvedOk
1498 lcDefaultResolvedOk = IIF(.l850DtlCharges,"N","Y")
1499
1500 && Error when no tax header record found for any of the detail line.
1501 lcSQLString = "SELECT h.ord_num, " + ;
1502 " h.pick_num, " + ;
1503 " h.inv_num, " + ;
1504 " h.customer, " + ;
1505 " h.cncl_num, " + ;
1506 " h.hdr_pkey, " + ;
1507 " h.frgt_Amt , " + ;
1508 " h.disc_Amt , " + ;
1509 " h.insu_amt , " + ;
1510 " h.center_Code, " + ;
1511 " h.store, " + ;
1512 " h.season, " + ;
1513 " h.Chrgh_pkey, " + ;
1514 " h.dtl_pkey, " + ;
1515 " h.price, " + ;
1516 " h.base_price, tr.aggr, " + ; &&--- TechRec 1075078 04-Dec-2013 jisingh ===
1517 " CAST(h.merch_Amt AS NUMERIC(15,2)) as merch_amt, " + ;
1518 STRTRAN(lcOrdDtlFilterFields, "D.","H.") + ;
1519 " h.division, " + ;
1520 " h.netmer_amt, " + ;
1521 " h.carton, " + ;
1522 " h.cust_type, " + ;
1523 " td.chrg_type, " + ;
1524 " td.pkey as zzxchrgd_pkey, " + ;
1525 " td.round_to as round_to, " + ; &&--- TechRec 1064560 13-Mar-2013 TShenbagavalli added round_to ===
1526 " 0 as trx_pct_Amt, " + ;
1527 " CASE WHEN ((COALESCE(chrg.resolved_ok, chrg_prev.resolved_ok, 'Y') = 'Y' AND " + IIF(tlEvaluateChrg," 1 =1 ", " 1= 2") + " AND " + ;
1528 " COALESCE(chrg.ov_ok, chrg_prev.ov_ok, 'N') <> 'Y')) THEN td.Chrg_Formula " + ; && TR 1070894 added IIF(tlEvaluateChrg," 1 =1 ", " 1= 2") condition
1529 " ELSE COALESCE(chrg.chrg_formula, chrg_prev.chrg_formula, '') " + ;
1530 " END As chrg_formula, " + ;
1531 " CASE WHEN ((COALESCE(chrg.resolved_ok, chrg_prev.resolved_ok, 'Y') = 'Y' AND " + IIF(tlEvaluateChrg," 1 =1 ", " 1= 2") + " AND "+ ;
1532 " COALESCE(chrg.ov_ok, chrg_prev.ov_ok, 'N') <> 'Y')) THEN td.Chrg_Formula " + ; && TR 1070894 added IIF(tlEvaluateChrg," 1 =1 ", " 1= 2") condition
1533 " ELSE COALESCE(chrg.chrg_formula, chrg_prev.chrg_formula, '') " + ;
1534 " END As old_chrg_formula, " + ;
1535 " CASE WHEN ((COALESCE(chrg.resolved_ok, chrg_prev.resolved_ok, 'Y') = 'Y' AND " + IIF(tlEvaluateChrg," 1 =1 ", " 1= 2") + " AND "+ ;
1536 " COALESCE(chrg.ov_ok, chrg_prev.ov_ok, 'N') <> 'Y')) THEN td.Chrg_expr " + ;&& TR 1070894 added IIF(tlEvaluateChrg," 1 =1 ", " 1= 2") condition
1537 " ELSE COALESCE(chrg.chrg_expr, chrg_prev.chrg_expr, '') " + ;
1538 " END As chrg_expr, " + ;
1539 " CASE WHEN ((COALESCE(chrg.resolved_ok, chrg_prev.resolved_ok, 'Y') = 'Y' AND " + IIF(tlEvaluateChrg," 1 =1 ", " 1= 2") + " AND "+ ;
1540 " COALESCE(chrg.ov_ok, chrg_prev.ov_ok, 'N') <> 'Y')) THEN td.percentage " + ;&& TR 1070894 added IIF(tlEvaluateChrg," 1 =1 ", " 1= 2") condition
1541 " ELSE COALESCE(chrg.percentage, chrg_prev.percentage, 00.00) " + ;
1542 " END As percentage, " + ;
1543 " CASE WHEN ((COALESCE(chrg.resolved_ok, chrg_prev.resolved_ok, 'Y') = 'Y' AND " + IIF(tlEvaluateChrg," 1 =1 ", " 1= 2") + " AND "+ ;
1544 " COALESCE(chrg.ov_ok, chrg_prev.ov_ok, 'N') <> 'Y')) THEN td.Chrg_amt " + ;&& TR 1070894 added IIF(tlEvaluateChrg," 1 =1 ", " 1= 2") condition
1545 " ELSE COALESCE(chrg.Chrg_amt, chrg_prev.Chrg_amt, 00000.00) " + ;
1546 " END As chrg_Amt, " + ;
1547 " CASE WHEN ((COALESCE(chrg.resolved_ok, chrg_prev.resolved_ok, 'Y') = 'Y' AND " + IIF(tlEvaluateChrg," 1 =1 ", " 1= 2") + " AND "+ ;
1548 " COALESCE(chrg.ov_ok, chrg_prev.ov_ok, 'N') <> 'Y')) THEN td.Chrg_flag " + ;&& TR 1070894 added IIF(tlEvaluateChrg," 1 =1 ", " 1= 2") condition
1549 " ELSE COALESCE(chrg.chrg_Flag, chrg_prev.chrg_Flag, '') " + ;
1550 " END As chrg_Flag, " + ;
1551 " CASE WHEN COALESCE(chrg.ov_ok, chrg_prev.ov_ok, 'N') = 'Y' THEN 1 " + ;
1552 " WHEN COALESCE(chrg.ov_ok, chrg_prev.ov_ok, 'N') = 'N' THEN 0 " + ;
1553 " ELSE 0 END as override, " + ;
1554 " COALESCE(chrg.ov_ok, chrg_prev.ov_ok, 'N') as ov_ok, " + ;
1555 " COALESCE(chrg.resolved_ok, chrg_prev.resolved_ok, '" + lcDefaultResolvedOk + "') as resolved_ok " + ;
1556 " ,FOBCST " + ; &&--- TechRec 1077393 20-Mar-2014 TShenbagavalli ===
1557 " FROM " + .cQ_OrdAddrChgDtl+ " h " + ;
1558 " JOIN zzxchrgd td " + ;
1559 " ON td.fkey = h.Chrgh_pkey " + ;
1560 " JOIN zzxchrtr tr " + ;
1561 " ON td.chrg_type = tr.chrg_type " + ;
1562 " AND tr.chrg_hdrdtl = 'D'" + ;
1563 " AND tr.Wo_only <> 'Y' " + ;
1564 IIF(tlEvaluateChrg, " LEFT OUTER JOIN ", " JOIN ") + .cQ_ZzxChrds + " chrg " + ;
1565 " ON chrg.ord_num = h.ord_num " + ;
1566 " AND chrg.pick_num = h.pick_num " + ;
1567 " AND chrg.inv_num = h.inv_num " + ;
1568 " AND chrg.zzxchrgd_pkey = td.pkey "+ ;
1569 " AND chrg.fkey = h.dtl_pkey " + ;
1570 lcCounts && TR 1086313 KISHORE 13-APR-2015
1571
1572 * Please refer to the Spec for More idea of what is done. Refer to the Truth Table.
1573 * If you are processing for Order (open) search for 1.open, if not 2. charger refer table
1574 * If you are processing for pick search for 1. pick, if not, 2. order, 3. charges refer table
1575 * If you are processing for Inv search for 1. Inv, if not, 2. Pick, 3. charges refer table
1576 IF INLIST(tcMode, 'P', 'I')
1577 lcSQLString = lcSQLString + ;
1578 " LEFT OUTER JOIN (SELECT c.*, CASE WHEN c.ov_ok = 'Y' " + ;
1579 " THEN 1 ELSE 0 " + ;
1580 " END AS override " + ;
1581 " FROM zzxchrds c) chrg_prev " + ;
1582 " ON chrg_prev.ord_num = h.ord_num " + ;
1583 " AND chrg_prev.pick_num = " + IIF(tcMode = 'P' , "0", "h.pick_num") + ;
1584 " AND chrg_prev.inv_num = 0 " + ;
1585 " AND chrg_prev.zzxchrgd_pkey = td.pkey " + ;
1586 " AND chrg_prev.fkey = h.dtl_pkey "
1587 ELSE
1588 lcSQLString = STRTRAN(lcSQLString, "chrg_prev.", "chrg.")
1589 ENDIF
1590
1591 lcSQLString = lcSQLString + " WHERE td.Resolve_ok = 'Y'" + ;
1592 " AND td.active_ok = 'Y' " + ; && *--- TR 1082854 19-11-2014 VKK
1593 " AND not exists ( select chrg_type from " + .cQ_ZzxChrds + " cd " + ;
1594 " where td.chrg_type = cd.chrg_type " + ;
1595 " AND cd.fkey = h.dtl_pkey " + ;
1596 " AND cd.fkey = h.dtl_pkey AND (cd.resolved_ok <> 'Y' or (cd.resolved_ok = 'Y' and cd.ov_ok = 'Y' ))" + ;
1597 " ) "
1598
1599
1600
1601 .cChargeTmpDtlAlias = SQLTableFromQuery(lcSqlString)
1602 llRetVal = llRetVal AND !EMPTY(.cChargeTmpDtlAlias )
1603 *--- STRY0202659
1604 IF TYPE('to945Log')= "O"
1605 to945log.logEntry("STRY0202659 - .cChargeTmpDtlAlias - lcSqlString")
1606 IF llRetVal
1607 v_sqlexec("select COUNT(*) as rcnt from "+ .cChargeTmpDtlAlias, "curxyz")
1608 to945log.logEntry("STRY0202659 - records "+ STR(curxyz.rcnt)+" found")
1609 ENDIF
1610 endif
1611 *=== STRY0202659
1612
1613 *--- TechRec 1075078 02-Dec-2013 jisingh ---
1614 lcSQLString = "UPDATE " + .cChargeTmpDtlAlias + " " +;
1615 " SET chrg_formula = " + ;
1616 "REPLACE(LTRIM(RTRIM(chrg_formula)), 'BASEP', LTRIM(RTRIM(CAST(base_price AS VARCHAR)))) "
1617 llRetVal = llRetVal AND v_SQLExec(lcSQLString)
1618 *=== TechRec 1075078 02-Dec-2013 jisingh ===
1619
1620 *--- TR 1080464 10-8-2014 VKK handled zero values for all 3 formulas
1621 lcSQLString = "UPDATE " + .cChargeTmpDtlAlias + " " +;
1622 " SET chrg_formula = " + ;
1623 "REPLACE(LTRIM(RTRIM(chrg_formula)),'MERCH', LTRIM(RTRIM(CAST(merch_amt * Case When Total_Qty > 0 AND merch_amt > 0 AND chrg_flag = 'N' THEN (merch_amt/merch_amt)/Total_Qty ELSE 1 END AS VARCHAR)))) " && *--- TR 1079480 3-7-2014 VKK Added case statmenet, because we will multiply qty later
1624 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
1625
1626 * Substitute tax formula for NET MERCH
1627 lcSQLString = "UPDATE " + .cChargeTmpDtlAlias + " " +;
1628 " SET chrg_formula = " + ;
1629 "REPLACE(LTRIM(RTRIM(chrg_formula)),'NETMER', LTRIM(RTRIM(CAST(netmer_amt * Case When Total_Qty > 0 ANd netmer_amt > 0 AND chrg_flag = 'N' THEN (netmer_amt/netmer_amt)/Total_Qty ELSE 1 END AS VARCHAR)))) " && *--- TR 1079480 3-7-2014 VKK Added case statmenet, because we will multiply qty later
1630 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
1631
1632 *--- TechRec 1077393 20-Mar-2014 TShenbagavalli ---
1633 lcSQLString = "UPDATE " + .cChargeTmpDtlAlias + " " +;
1634 " SET chrg_formula = " + ;
1635 "REPLACE(LTRIM(RTRIM(chrg_formula)),'FOBCST', LTRIM(RTRIM(CAST(FOBCST * Case When Total_Qty > 0 and FOBCST > 0 AND chrg_flag = 'N' THEN (fobcst/fobcst)/Total_Qty ELSE 1 END AS VARCHAR)))) " && *--- TR 1079480 3-7-2014 VKK Added case statmenet, because we will multiply qty later
1636
1637 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
1638 *=== TechRec 1077393 20-Mar-2014 TShenbagavalli ===
1639
1640 lcSQLString = "UPDATE " + .cChargeTmpDtlAlias + " " +;
1641 " SET chrg_formula = " + ;
1642 " CASE WHEN chrg_flag = 'F'" + ;
1643 " THEN CAST (chrg_amt AS Varchar) " + ;
1644 " WHEN chrg_flag = 'U' " + ;
1645 " THEN CAST (chrg_amt * Total_Qty AS VARCHAR) " + ;
1646 " WHEN chrg_flag = 'P' " + ;
1647 " THEN '1/100 *' + LTRIM(RTRIM(CAST(percentage as Varchar))) + '* ( '+ LTRIM(RTRIM(chrg_formula))+ ')'" + ;
1648 " WHEN chrg_flag = 'N' " + ; && *--- TR 1075078 8-1-2014 VKK
1649 " THEN 'Total_Qty * 1/100 *' + LTRIM(RTRIM(CAST(percentage as Varchar))) + '* ( '+ LTRIM(RTRIM(chrg_formula))+ ')'" + ; && *--- TR 1075078 8-1-2014 VKK
1650 " ELSE chrg_formula END "
1651
1652 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
1653 IF TYPE('to945Log')= "O"
1654 to945log.logEntry("STRY0202659 - update cChargeTmpDtlAlias 5 "+ IIF(llRetVal, "success", "failed")) &&STRY0202659
1655 endif
1656
1657 lcSQLString = "SELECT * FROM " + .cChargeTmpDtlAlias
1658 llRetval = llRetval AND V_SQLExec(lcSQLString, lcWorkAlias)
1659 llRetVal = llRetVal AND USED(lcWorkAlias)
1660 IF TYPE('to945Log')= "O"
1661 to945log.logEntry("STRY0202659 - lcWorkAlias cChargeTmpDtlAlias reccount is "+ STR(RECCOUNT(lcWorkAlias))) &&STRY0202659
1662 endif
1663
1664* need to retain the overrideden detail charges, to get rid of wrong pick # and inv #.
1665 lcSQLSTring = " Update d" + ;
1666 " Set Pick_num = Case When p.pkey is null AND i.pkey IS null THEN 0 ELSE d.pick_num END ," + ;
1667 " Inv_Num = case when i.pkey is null THEN 0 else d.inv_num END " + ;
1668 " From zzxchrds d " + ;
1669 " JOIN " + tcInvOrders + " tmp " + ;
1670 " ON tmp.ord_num = d.ord_num " + ;
1671 " LEFT Join zzoordrh p on p.pick_num = d.pick_num And d.division = p.division AND p.pick_Num > 0 " + ;
1672 " LEFT Join zzoshprh i on i.pick_num = d.pick_num And i.inv_num = d.inv_num And i.division = d.division"
1673
1674 llRetval = llRetval AND V_SQLExec(lcSQLString)
1675 IF TYPE('to945Log')= "O"
1676 to945log.logEntry("STRY0202659 - update zzxchrds "+ IIF(llRetVal, "sucessful", "failed")) &&STRY0202659
1677 endif
1678
1679
1680 IF llRetVal AND RECCOUNT(lcWorkAlias) > 0
1681
1682 lcSQLString = "DELETE " + ;
1683 " FROM zzxchrds " + ;
1684 " WHERE resolved_ok = 'Y' AND ov_ok <> 'Y' AND pkey IN " + ;
1685 " (SELECT a.pkey FROM zzxchrds a " + ;
1686 " JOIN " + tcInvOrders + " tmp " + ;
1687 " ON a.ord_num = tmp.ord_num " + ;
1688 " and a.pick_num = tmp.pick_num " + ;
1689 " AND a.inv_num = tmp.inv_num )"
1690
1691 llRetval = llRetval AND V_SQLExec(lcSQLString)
1692 IF TYPE('to945Log')= "O"
1693 to945log.logEntry("STRY0202659 - deleting zzxchrds "+ IIF(llRetVal, "sucessful", "failed")) &&STRY0202659
1694 endif
1695
1696
1697 * Build SQL String to insert into zzxchrgs
1698 lcSQLString = ""
1699 llRetVal = llRetval AND BuildInsertDtlChargeString(lcWorkAlias, toBPOSO)
1700 llRetval = llRetval AND V_SQLExec(lcSQLString)
1701 IF TYPE('to945Log')= "O"
1702 to945log.logEntry("STRY0202659 - BuildInsertDtlChargeString "+ IIF(llRetVal, "sucessful", "failed")) &&STRY0202659
1703 endif
1704 ENDIF
1705
1706 .TableClose(lcWorkAlias)
1707
1708 ENDWITH
1709
1710 SELECT (lnSelect)
1711 RETURN llRetVal
1712ENDFUNC
1713
1714FUNCTION BuildInsertDtlChargeString
1715 LPARAMETERS tcWorkAlias ,toBPOSO && tcTaxTable is output alias.
1716
1717 LOCAL llRetVal, lnSelect, lcSQLString, lcExact, lnChrg_amt
1718 PRIVATE pnInv_num,pnOrd_num,pnpick_num,pnHdrFkey, pnFkey,;
1719 pcchrg_type,pcdivision,pcUser, ptLast_mod, pnPercentage, pcResolved_ok;
1720 pcChrg_Flag,pcov_ok,pnChrg_amt,pntrx_pct_amt,pcchrg_expr,pcchrg_formula,pczzxchrgd_pkey,pnRound_to,pcAggr
1721 *--- TechRec 1075078 05-Dec-2013 jisingh Added pcAggr ===
1722 pcUser = GoEnv.SV("cUSER","")
1723 ptLast_Mod = DATETIME()
1724
1725 llRetVal = .T.
1726 lnSelect = SELECT()
1727 lcExact = SET("EXACT")
1728 SET EXACT ON
1729
1730 WITH toBPOSO
1731
1732 SELECT (tcWorkAlias)
1733 SCAN
1734 .nChargeEvalvalue = 0
1735 lnChrg_amt = 0
1736
1737 llRetVal = llRetVal AND EvalChargeFormula(chrg_type, chrg_formula, chrg_Expr, toBPOSO) && *--- TR 1073530 16-9-2013 VKK removed round_to as not used
1738
1739 && To avoid discrepancy in round off
1740 *--- TechRec 1070894 07-May-2013 TShenbagavalli ---
1741*!* lnChrg_amt = ROUND( .nChargeEvalValue/total_qty, round_to)
1742*!* lnChrg_amt = lnChrg_amt * total_qty
1743 lnChrg_amt = ROUND( .nChargeEvalValue/IIF(total_qty = 0, 1, total_qty), round_to)
1744 lnChrg_amt = lnChrg_amt * IIF(total_qty = 0, 1, total_qty)
1745 *=== TechRec 1070894 07-May-2013 TShenbagavalli ===
1746
1747 IF .lValidCharge
1748 pnInv_num = inv_num
1749 pnOrd_num = ord_num
1750 pnpick_num = pick_num
1751 pnHdrFkey = hdr_Pkey
1752 pcdivision = division
1753 pcchrg_type = chrg_type
1754 pcChrg_Flag = Chrg_Flag
1755 pcov_ok = "N"
1756 pcResolved_ok = IIF(.l850DtlCharges,"N","Y") && 1073611 09/18/13 Ilya: Charges resolved in 850 process are considered manually entered, not "resolved", for subsequent maintenance.
1757 pnFkey = dtl_pkey
1758 pnChrg_amt = IIF(!INLIST(Chrg_Flag, "P", "N"), Chrg_Amt, 0) && *--- TR 1075078 8-1-2014 VKK added N
1759 pnPercentage = IIF(INLIST(Chrg_Flag, "P", "N"), Percentage, 0) && *--- TR 1075078 8-1-2014 VKK added N
1760 pntrx_pct_amt = IIF(INLIST(Chrg_Flag, "P", "N"), lnChrg_amt, 0) && *--- TR 1075078 8-1-2014 VKK added N
1761 pcchrg_expr = chrg_expr
1762 pcchrg_formula = old_chrg_formula
1763 pczzxchrgd_pkey = zzxchrgd_pkey
1764 pnRound_to = round_to
1765 pcAggr = aggr &&--- TechRec 1075078 05-Dec-2013 jisingh ===
1766
1767 *--- TechRec 1075078 05-Dec-2013 jisingh Added aggr ===
1768 llRetVal = llRetVal AND v_sqlexec(;
1769 " INSERT INTO zzxchrds " + ;
1770 " (inv_num,ord_num,pick_num,chrg_type,chrg_flag,chrg_amt,ov_ok,resolved_ok,trx_pct_amt," + ;
1771 " percentage,fkey,hdrfkey,last_mod,user_id," + ;
1772 " division,chrg_cost,chrg_expr,chrg_formula,notes,zzxchrgd_pkey,round_to,aggr)" + ;
1773 " VALUES " + ;
1774 " (?pnInv_num ,?pnOrd_num ,?pnpick_num ,?pcchrg_type,?pcChrg_Flag,?pnChrg_amt, ?pcov_ok, ?pcResolved_ok, " + ;
1775 " ?pntrx_pct_amt, ?pnPercentage,?pnFkey,?pnHdrFkey, ?ptLast_mod,?pcUser," + ;
1776 " ?pcdivision ,0,?pcchrg_expr,?pcchrg_formula, '', ?pczzxchrgd_pkey, ?pnRound_to, ?pcAggr)")
1777 ENDIF
1778
1779 ENDSCAN
1780
1781 ENDWITH
1782
1783 SET EXACT &lcExact
1784 SELECT (lnSelect)
1785 RETURN llRetVal
1786ENDFUNC
1787
1788*============================================================
1789 FUNCTION RecalcDetailCharges
1790 LPARAMETERS tcInvOrders, tcOrdDtlAlias, tobposo
1791 LOCAL llRetVal, lnSelect, lcSQL, lcWorkAlias
1792
1793 llRetVal = true
1794 lnSelect = SELECT()
1795
1796 WITH tobposo
1797 *--- TR 1079480 KISHORE 3-JUL-2014
1798 lcFobcst = ;
1799 " LEFT JOIN zzdlocst lc " + ;
1800 " ON lc.division = d.division AND lc.style = d.style AND lc.color_code = d.color_code " + ;
1801 " AND lc.lbl_code = d.lbl_code and lc.dimension = d.dimension AND lc.location = d.location "
1802 *=== TR 1079480 KISHORE 3-JUL-2014
1803
1804 *--- TechRec 1075078 12-Dec-2013 jisingh Added od.base_price & d.base_price ===
1805 lcSQL = " select d.pkey, d.fkey, d.hdrfkey, d.percentage, d.chrg_formula, d.chrg_expr, d.chrg_flag, d.chrg_Amt, " + ;
1806 " d.chrg_cost, d.round_to, d.chrg_type, d.trx_pct_amt, od.merch_amt, od.netmer_amt, od.total_qty, od.price, od.base_price " + ;
1807 " ,od.fobcst " + ; && TR 1079480 KISHORE 3-JUL-2014
1808 " from zzxchrds d " + ;
1809 " join " + " ( select d.pkey, d.fkey, d.total_qty, d.price, d.base_price, " + ;
1810 " CAST((d.price * d.total_qty) AS NUMERIC(15,2)) as merch_amt, " + ;
1811 " CAST((d.price * d.total_qty - (d.price * d.total_qty * " + ;
1812 SQLFNIFNull("dsc.disc_perc","0") + "/100)) AS NUMERIC(15,2)) as netmer_amt " + ;
1813 ", COALESCE(lc.fob_cost,0) * d.total_qty as FOBCST " + ; && TR 1079480 KISHORE 3-JUL-2014
1814 " from " + tcOrdDtlAlias + " d " + ;
1815 " left outer join zzxdiscr dsc " + ;
1816 " on dsc.discount = d.discount " + ;
1817 " Join " + tcInvOrders + " I " + ;
1818 " On I.pkey = d.fkey " + ;
1819 lcFobcst + ; && TR 1079480 KISHORE 3-JUL-2014
1820 " ) od " + ;
1821 " on d.fkey = od.pkey " + ;
1822 " and d.hdrfkey = od.fkey " + ;
1823 " Join " + tcInvOrders + " I " + ;
1824 " On I.pkey = d.hdrfkey " + ;
1825 " and (d.resolved_ok <> 'Y' or (d.resolved_ok ='Y' and ov_ok = 'Y'))"
1826
1827 .cChargeTmpDtlAlias = SQLTableFromQuery(lcSQL)
1828
1829 *--- TechRec 1075078 02-Dec-2013 jisingh ---
1830 lcSQLString = "UPDATE " + .cChargeTmpDtlAlias + " " +;
1831 " SET chrg_formula = " + ;
1832 "REPLACE(LTRIM(RTRIM(chrg_formula)), 'BASEP', LTRIM(RTRIM(CAST(base_price AS VARCHAR)))) "
1833 llRetVal = llRetVal AND v_SQLExec(lcSQLString)
1834 *=== TechRec 1075078 02-Dec-2013 jisingh ===
1835
1836 *--- TR 1080464 10-8-2014 VKK handled zero values for all 3 formulas
1837 * Substitute tax formula with the respective prorated order amounts
1838 lcSQLString = " UPDATE " + .cChargeTmpDtlAlias + " " +;
1839 " SET chrg_formula = " + ;
1840 " REPLACE(LTRIM(RTRIM(chrg_formula)),'MERCH', LTRIM(RTRIM(CAST(merch_amt * Case When Total_Qty > 0 and merch_amt > 0 AND chrg_flag = 'N' THEN (merch_amt/merch_amt)/Total_Qty ELSE 1 END AS VARCHAR)))) " && *--- TR 1079480 3-7-2014 VKK Added case statmenet, because we wil m,uktiple qty later
1841
1842 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
1843
1844 * Substitute tax formula for NET MERCH
1845 lcSQLString = "UPDATE " + .cChargeTmpDtlAlias + " " +;
1846 " SET chrg_formula = " + ;
1847 "REPLACE(LTRIM(RTRIM(chrg_formula)),'NETMER', LTRIM(RTRIM(CAST(netmer_amt * Case When Total_Qty > 0 and netmer_amt > 0 AND chrg_flag = 'N' THEN (netmer_amt/netmer_amt)/Total_Qty ELSE 1 END AS VARCHAR)))) " && *--- TR 1079480 3-7-2014 VKK Added case statmenet, because we wil m,uktiple qty later
1848 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
1849
1850 *--- TR 1079480 3-7-2014 VKK && fob cost was missign totaly so added here
1851 lcSQLString = "UPDATE " + .cChargeTmpDtlAlias + " " +;
1852 " SET chrg_formula = " + ;
1853 "REPLACE(LTRIM(RTRIM(chrg_formula)),'FOBCST', LTRIM(RTRIM(CAST(FOBCST * Case When Total_Qty > 0 and FOBCST > 0 AND chrg_flag = 'N' THEN (fobcst/fobcst)/Total_Qty ELSE 1 END AS VARCHAR)))) "
1854 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
1855 *=== TR 1079480 3-7-2014 VKK
1856
1857 lcSQLString = "UPDATE " + .cChargeTmpDtlAlias + " " +;
1858 " SET chrg_formula = '1/100 *' + LTRIM(RTRIM(CAST(percentage as Varchar))) + '* ( '+ LTRIM(RTRIM(chrg_formula))+ ')'"
1859
1860 lcSQLString = "UPDATE " + .cChargeTmpDtlAlias + " " +;
1861 " SET chrg_formula = " + ;
1862 " CASE WHEN chrg_flag = 'F'" + ;
1863 " THEN CAST (chrg_amt AS Varchar) " + ;
1864 " WHEN chrg_flag = 'U' " + ;
1865 " THEN CAST (chrg_amt * Total_Qty AS VARCHAR) " + ;
1866 " WHEN chrg_flag = 'P' " + ;
1867 " THEN '1/100 *' + LTRIM(RTRIM(CAST(percentage as Varchar))) + '* ( '+ LTRIM(RTRIM(chrg_formula))+ ')'" + ;
1868 " ELSE chrg_formula END "
1869
1870 llRetVal = llRetVal AND V_SQLExec(lcSQLString)
1871
1872 IF llRetVal
1873 lcWorkAlias = GetUniqueFileName()
1874 llRetVal = llRetVal AND v_SQLExec(" SELECT * FROM " + .cChargeTmpDtlAlias, lcWorkAlias )
1875 llRetVal = llRetVal AND UpdateDetailCharges(tcInvOrders, lcWorkAlias, tobposo)
1876 ENDIF
1877
1878 ENDWITH
1879
1880 SELECT (lnSelect)
1881 RETURN llRetVal
1882 ENDFUNC
1883*============================================================
1884 FUNCTION UpdateDetailCharges
1885 LPARAMETERS tcInvOrders, tcWorkAlias, tobposo
1886 LOCAL llRetVal, lnSelect, lnPkey, lnRound, lnTotal_Qty, lnChrg_amt
1887
1888 llRetVal = true
1889 lnSelect = SELECT()
1890 WITH tobposo
1891 lcSQL = " update d " + ;
1892 " set d.percentage = 0, " + ;
1893 " d.trx_pct_amt = 0 " + ;
1894 " from zzxchrds d " + ;
1895 " join " + tcInvOrders + " I" + ;
1896 " On i.pkey = d.hdrfkey " + ;
1897 " and d.chrg_flag <> 'P' " + ;
1898 " and d.chrg_flag <> 'N' " && *--- TR 1075078 8-1-2014 VKK
1899
1900 llRetVal = llRetVal AND v_SQLExec(lcSQL)
1901
1902 SELECT (tcWorkAlias)
1903 SCAN
1904 .nChargeEvalValue = 0
1905 lnChrg_amt = 0
1906 lnPkey = pkey
1907 lnRound = round_to
1908 lnTotal_Qty = total_qty
1909
1910 llRetVal = llRetVal AND .EvalChargeFormula(chrg_type, chrg_formula, chrg_Expr) && *--- TR 1073530 16-9-2013 VKK removed round_to as not used
1911
1912 && To avoid discrepancy in round off
1913 *--- TechRec 1070894 07-May-2013 TShenbagavalli ---
1914*!* lnChrg_amt = ROUND( .nChargeEvalValue/total_qty, lnRound)
1915*!* lnChrg_amt = lnChrg_amt * total_qty
1916 lnChrg_amt = ROUND( .nChargeEvalValue/IIF(total_qty = 0, 1, total_qty), round_to)
1917 lnChrg_amt = lnChrg_amt * IIF(total_qty = 0, 1, total_qty)
1918 *=== TechRec 1070894 07-May-2013 TShenbagavalli ===
1919
1920
1921 IF llRetVal AND .lValidCharge
1922 *--- TR 1075078 8-1-2014 VKK added IN for both place
1923 lcSQL = " update d " + ;
1924 " set d.trx_pct_amt = case when d.chrg_flag IN('P', 'N') then " + SQLFormatNum(lnChrg_amt,lnRound) + " else 0 end /*, " + ;
1925 " d.chrg_amt = case when d.chrg_flag NOT IN('P', 'N') then " + SQLFormatNum(.nChargeEvalValue,2) + " else 0 end */" + ;
1926 " from zzxchrds d " + ;
1927 " where d.pkey = " + SQLFormatNum(lnPkey)
1928
1929 llRetVal = llRetVal AND v_SQLExec(lcSQL)
1930
1931 ENDIF
1932 ENDSCAN
1933
1934 ENDWITH
1935
1936 SELECT (lnSelect)
1937 RETURN llRetVal
1938 ENDFUNC
1939*============================================================
1940*=== TechRec 1064560 14-Feb-2013 TShenbagavalli ===