· 9 years ago · Jan 24, 2017, 11:18 PM
1*======================================================================
2* (C) Copyright 2001 CGS, Inc.
3* ALL RIGHTS RESERVED
4*======================================================================
5* Version....: 1.00.0
6* Author.....: Kishore Kumar
7* Date.......: 04/06/11 03:41:26 AM
8* Platform...: Visual FoxPro 09.00.0000.3504 for Windows
9* Abstract...: Auto-bill Process - Creates Invoices (setbased)
10* Changes....: TR 1053427
11*======================================================================
12#Include system.h
13
14DEFINE CLASS setbased_invoice as BaseBusiness
15
16 oEdi = NULL
17 oLog = NULL
18
19 cPickCursor = ""
20 cPickHdrKeyTmp = ""
21 cPickDtlKeyTmp = ""
22 cPickHeaderTmp = ""
23 cPickDetailTmp = ""
24 cOrdSPTmp = ""
25 cErrorText = ""
26 nProcessOrders = 0
27 nInvoicedOrders = 0
28 nFailedOrders = 0
29 lFactorApproveByAmount = .F.
30 lResolveFreightAtInvoice = .F.
31 lMultiOrganization = .F.
32 lEnableCreditCard = .F.
33 lCCVerisignApproval = .F.
34 lSupportEncrypt = .F.
35 oBPOPayflowPro = .F.
36 oProgressBar = .F.
37 nThermTotal = 0
38
39 *--- TR 1054859 14-06-2011 RKI ---*
40 oBOSOPDF = NULL
41 *=== TR 1054859 14-06-2011 ===*
42
43 *--- TechRec 1058339 03-Jan-2012 jisingh ---
44 lResolveCharges = .F.
45 lUseFRMCode = .F.
46 *=== TechRec 1058339 03-Jan-2012 jisingh ===
47
48*-----------------------------------------------------------------------------
49
50 PROCEDURE ProcessPickTickets
51
52 *---TR 1064618 02/22/2013 YE === New variable lcSQL
53 LOCAL llRetVal, lnSelect, lcCursor, llContinue, lcSQL
54 llRetVal = .T.
55 lnSelect = SELECT()
56
57 WITH THIS
58
59 llRetVal = (RECCOUNT(.cPickCursor) > 0)
60
61 IF llRetVal
62 .LogEntry("Optimized invoice generation started.")
63
64 .lFactorApproveByAmount = (goEnv.sv("FACTOR_APPV_BY_AMOUNT","N") = 'Y')
65 .lResolveFreightAtInvoice = (goEnv.SV('SOP_RESOLVE_FREIGHT_INVOICE', 'N') = 'Y')
66
67 *--- TechRec 1058339 03-Jan-2012 jisingh ---
68 .lResolveCharges = (vl_compr(, "chrg_ok") == "Y")
69 .lUseFRMCode = (goEnv.SV('SOP_USE_FRM_CODES', 'N') = 'Y')
70 *=== TechRec 1058339 03-Jan-2012 jisingh ===
71
72 SET PROCEDURE TO clsedi additive
73 .oEDI = CREATEOBJECT("BPOEDIBase")
74 .oEDI.oLog = .oLog
75
76 * cc setup
77 .lEnableCreditCard = (vl_compr('','creditcard_ok') == 'Y')
78 .lMultiOrganization = (vl_compr('','org_ok') = 'Y')
79 .lCCVerisignApproval = (goEnv.SV('CC_WITH_VERISIGN_APPROVAL_FEATURE', 'N') = 'Y')
80 IF (.lEnableCreditCard OR .lCCVerisignApproval)
81 .oBPOPayflowPro = NEWOBJECT('BOVerisign',"clsccard.prg")
82 llRetVal = IsObject(.oBPOPayflowPro ,.T.)
83 IF NOT llRetVal
84 .cMessage = "Couldn't create Verisign Payflow Pro Object"
85 loBPOPayflowPro= NULL
86 ENDIF
87
88 *--- TR 1054859 14-06-2011 RKI ---*
89 .oBOSOPDF = NEWOBJECT('BOSalesOP',"clsslsop.prg")
90 llRetVal = llRetVal AND IsObject(.oBOSOPDF,.T.)
91 IF NOT llRetVal
92 .cMessage = "Couldn't create Sales OP Business Object"
93 .oBOSOPDF= NULL
94 ENDIF
95 *=== TR 1054859 14-06-2011 RKI ===*
96
97 ENDIF
98
99 IF .lCCVerisignApproval AND llRetVal
100 lcCursor = GetUniqueFileName()
101 lcSQLString = " Select COUNT(*) cnt from master.dbo.sysobjects where name = 'XP_crypt' "
102 llContinue = llRetVal AND v_Sqlexec(lcSQLString, lcCursor) AND USED(lcCursor)
103 IF llContinue
104 SELECT(lcCursor)
105 llContinue = (cnt >0 )
106 IF !llContinue
107 .lSupportEncrypt = .f.
108 ELSE
109 .lSupportEncrypt = .t.
110 ENDIF
111 USE IN SELECT(lcCursor)
112 ENDIF
113 ENDIF
114
115 IF .lCCVerisignApproval AND NOT .lSupportEncrypt
116 .LogEntry("Credit Card Encryption Feature not is available.")
117 RETURN .f.
118 ENDIF
119
120 *---TR 1064618 02/22/2013 YE
121 ELSE
122 .LogEntry("No records to process.")
123 *===TR 1064618 02/22/2013 YE
124
125 ENDIF
126
127 llRetVal = llRetVal AND .CreateServerTempTables()
128
129 *---TR 1064618 02/22/2013 YE
130 *llRetVal = llRetVal AND v_sqlexec("SELECT COUNT(*) as cnt FROM " + .cPickHeaderTmp,"tcHdrCnt")
131 lcSQL = "SELECT COUNT(*) as cnt FROM " + .cPickHeaderTmp
132 llRetVal = llRetVal AND v_sqlexec(lcSQL,"tcHdrCnt")
133 *===TR 1064618 02/22/2013 YE
134
135 IF llRetVal
136 .nProcessOrders = tcHdrCnt.cnt
137
138 *---TR 1064618 02/22/2013 YE
139 .LogEntry("# of Orders to process: " + STR(.nProcessOrders))
140
141 ELSE
142 .LogEntry("SQL failed: " + lcSQL)
143 *===TR 1064618 02/22/2013 YE
144
145 ENDIF
146
147 *---TR 1064618 02/22/2013 YE === Comment out and move logging inside the routine
148 *.LogEntry("Validating Picks.")
149
150 llRetVal = llRetVal AND .ValidateOrders()
151
152 *---TR 1064618 02/22/2013 YE === Comment out and move logging inside the routine
153 *.LogEntry("Generating Invoices.")
154
155 llRetVal = llRetVal AND .GenerateInvoices()
156
157 * delete all processed records from zzyabill
158
159 *---TR 1064618 02/22/2013 YE === Comment out and move logging inside the routine
160 *.LogEntry("Removing processed records from zzyabill")
161
162 llRetVal = llRetVal AND .RemoveFromHoldTable()
163
164 * get total invoiced
165
166 *---TR 1064618 02/22/2013 YE
167 *llRetVal = llRetVal AND v_sqlexec("SELECT COUNT(*) as cnt FROM " + .cPickHeaderTmp, "tcHdrCnt")
168 lcSQL = "SELECT COUNT(*) as cnt FROM " + .cPickHeaderTmp
169 llRetVal = llRetVal AND v_sqlexec(lcSQL, "tcHdrCnt")
170 *===TR 1064618 02/22/2013 YE
171
172 IF llRetVal
173 .nInvoicedOrders = tcHdrCnt.cnt
174
175 *---TR 1064618 02/22/2013 YE
176 ELSE
177 .LogEntry("SQL failed: " + lcSQL)
178 *===TR 1064618 02/22/2013 YE
179
180 ENDIF
181
182 .nFailedOrders = (.nProcessOrders - .nInvoicedOrders)
183
184 .Cleanup()
185
186 ENDWITH
187
188 SELECT (lnSelect)
189 RETURN llRetVal
190
191 ENDPROC
192
193*-----------------------------------------------------------------------------
194
195 PROCEDURE CreateServerTempTables
196
197 *---TR 1064618 02/22/2013 YE === New variables lcHeading and lcSQL
198 LOCAL llRetVal,lnSelect, lcHeading, lcSQL
199
200 llRetVal = .T.
201 lnSelect = SELECT()
202
203 WITH This
204
205 *---TR 1064618 02/22/2013 YE
206 lcHeading = " Creating server temp tables "
207 .oLog.LogMajorStage(lcHeading)
208 *===TR 1064618 02/22/2013 YE
209
210 .cPickDtlKeyTmp = "#" + GetUniqueFileName()
211 .cPickHdrKeyTmp = "#" + GetUniqueFileName()
212 .cPickHeaderTmp = "#" + GetUniqueFileName()
213 .cPickDetailTmp = "#" + GetUniqueFileName()
214 .cOrdSPTmp = "#" + GetUniqueFileName()
215
216 * Get list of open order details on to server.
217 SELECT DISTINCT pkey,pick_pkey as fkey FROM (.cPickCursor) INTO CURSOR __tcPkeyList
218
219 *---TR 1064618 02/22/2013 YE
220 If Used('__tcPkeyList') And Reccount('__tcPkeyList') > 0
221 .LogEntry(" # of Records in __tcPkeyList cursor from .cPickCursor: " + STR(Reccount('__tcPkeyList')))
222 ELSE
223 .LogEntry("Creating cursor __tcPkeyList from .cPickCursor failed.")
224 llRetVal = .F.
225 ENDIF
226
227 *llRetVal = llRetVal AND v_sqlexec("CREATE TABLE " + .cPickDtlKeyTmp + " (pkey INT,FKEY INT)")
228 lcSQL = "CREATE TABLE " + .cPickDtlKeyTmp + " (pkey INT,FKEY INT)"
229 llRetVal = llRetVal AND v_sqlexec(lcSQL)
230
231 IF NOT llRetVal
232 .LogEntry("SQL failed: " + lcSQL)
233 ENDIF
234 *===TR 1064618 02/22/2013 YE
235
236 llRetVal = llRetVal AND .oEDI.BulkInsertFromCursor("__tcPkeyList",.cPickDtlKeyTmp)
237
238 *---TR 1064618 02/22/2013 YE
239 IF NOT llRetVal
240 .LogEntry("Bulk insert from cursor __tcPkeyList into .cPickDtlKeyTmp table failed.")
241 ENDIF
242
243 *llRetVal = llRetVal AND v_sqlexec("SELECT *,IDENTITY(int) as pkey2 INTO " + .cOrdSPTmp + " FROM zzoordsp where 1=0")
244 lcSQL = "SELECT *,IDENTITY(int) as pkey2 INTO " + .cOrdSPTmp + " FROM zzoordsp where 1=0"
245 llRetVal = llRetVal AND v_sqlexec(lcSQL)
246
247 IF NOT llRetVal
248 .LogEntry("SQL failed: " + lcSQL)
249 ENDIF
250
251 *llRetVal = llRetVal AND v_sqlexec("exec bcsp_givetabledefaults '" + .cOrdSPTmp + "','zzoordsp'")
252 lcSQL = "exec bcsp_givetabledefaults '" + .cOrdSPTmp + "','zzoordsp'"
253 llRetVal = llRetVal AND v_sqlexec(lcSQL)
254
255 IF NOT llRetVal
256 .LogEntry("SQL failed: " + lcSQL)
257 ELSE
258 *===TR 1064618 02/22/2013 YE
259
260 .LogEntry("Generating Key Temporary Tables.")
261
262 *---TR 1064618 02/22/2013 YE
263 ENDIF
264 *===TR 1064618 02/22/2013 YE
265
266 * Get Order Header pkeys
267
268 *---TR 1064618 02/22/2013 YE
269 *llRetVal = llRetVal AND v_sqlexec("SELECT DISTINCT fkey as pkey INTO " + .cPickHdrKeyTmp + " FROM " + .cPickDtlKeyTmp)
270 lcSQL = "SELECT DISTINCT fkey as pkey INTO " + .cPickHdrKeyTmp + " FROM " + .cPickDtlKeyTmp
271 llRetVal = llRetVal AND v_sqlexec(lcSQL)
272
273 IF NOT llRetVal
274 .LogEntry("SQL failed: " + lcSQL)
275 ELSE
276 *===TR 1064618 02/22/2013 YE
277
278 .LogEntry("Generating Order Temporary Tables.")
279
280 *---TR 1064618 02/22/2013 YE
281 ENDIF
282 *===TR 1064618 02/22/2013 YE
283
284 * Build Temp Tables for open and detail.
285 *--- TR 1054859 06-16-2011 RKI ---*
286 * ApprovalID Width increased from 1 to 30
287*!* llRetVal = llRetVal AND v_sqlexec(;
288*!* "SELECT h.*,IDENTITY(int) as pkey2, h.pkey as orig_key, h.pkey as open_pkey " + ;
289*!* ",'N' as DoCredChk, 'N' as NonFactDet,'Y' as ManApp , COALESCE(r.ar_customer,h.customer) as ar_customer " + ;
290*!* ",'' as cncl_type, '' as cncl_rsn, '' as bkord_cncl, '' as output_abill, '' as shortsku_ok " + ;
291*!* ",'' as errs_flg, '' as errs_msg, '' as approvalid, CAST(0.00 as numeric(12,2)) as ChargeAmt " + ;
292*!* " INTO " + .cPickHeaderTmp + " FROM zzoordrh h " + ;
293*!* " JOIN " + .cPickHdrKeyTmp + " t ON h.pkey = t.pkey " + ;
294*!* " LEFT OUTER JOIN zzrCustR r ON h.customer = r.customer ")
295
296 *---TR 1064618 02/22/2013 YE
297 *llRetVal = llRetVal AND v_sqlexec(;
298 "SELECT h.*,IDENTITY(int) as pkey2, h.pkey as orig_key, h.pkey as open_pkey " + ;
299 ",'N' as DoCredChk, 'N' as NonFactDet,'Y' as ManApp , COALESCE(r.ar_customer,h.customer) as ar_customer " + ;
300 ",'' as cncl_type, '' as cncl_rsn, '' as bkord_cncl, '' as output_abill, '' as shortsku_ok " + ;
301 ",'' as errs_flg, '' as errs_msg, SPACE(30) as approvalid, CAST(0.00 as numeric(12,2)) as ChargeAmt " + ;
302 " INTO " + .cPickHeaderTmp + " FROM zzoordrh h " + ;
303 " JOIN " + .cPickHdrKeyTmp + " t ON h.pkey = t.pkey " + ;
304 " LEFT OUTER JOIN zzrCustR r ON h.customer = r.customer ")
305 lcSQL = "SELECT h.*,IDENTITY(int) as pkey2, h.pkey as orig_key, h.pkey as open_pkey " + ;
306 ",'N' as DoCredChk, 'N' as NonFactDet,'Y' as ManApp , COALESCE(r.ar_customer,h.customer) as ar_customer " + ;
307 ",'' as cncl_type, '' as cncl_rsn, '' as bkord_cncl, '' as output_abill, '' as shortsku_ok " + ;
308 ",'' as errs_flg, '' as errs_msg, SPACE(30) as approvalid, CAST(0.00 as numeric(12,2)) as ChargeAmt " + ;
309 " INTO " + .cPickHeaderTmp + " FROM zzoordrh h " + ;
310 " JOIN " + .cPickHdrKeyTmp + " t ON h.pkey = t.pkey " + ;
311 " LEFT OUTER JOIN zzrCustR r ON h.customer = r.customer "
312
313 llRetVal = llRetVal AND v_sqlexec(lcSQL)
314
315 IF NOT llRetVal
316 .LogEntry("SQL failed: " + lcSQL)
317 ENDIF
318 *===TR 1064618 02/22/2013 YE
319
320 *=== TR 1054859 06-16-2011 RKI ===*
321
322 *---TR 1064618 02/22/2013 YE
323 *llRetVal = llRetVal AND v_sqlexec(;
324 "SELECT d.*,IDENTITY(int) as pkey2, d.pkey as orig_key, d.fkey as open_fkey, t.pkey as pick_pkey INTO " + .cPickDetailTmp + " FROM zzoordrd d JOIN " + .cPickDtlKeyTmp + " t ON d.fkey = t.fkey ")
325 lcSQL = "SELECT d.*,IDENTITY(int) as pkey2, d.pkey as orig_key, d.fkey as open_fkey, t.pkey as pick_pkey INTO " + .cPickDetailTmp + " FROM zzoordrd d JOIN " + .cPickDtlKeyTmp + " t ON d.fkey = t.fkey "
326 llRetVal = llRetVal AND v_sqlexec(lcSQL)
327
328 IF NOT llRetVal
329 .LogEntry("SQL failed: " + lcSQL)
330 ENDIF
331 *===TR 1064618 02/22/2013 YE
332
333 USE IN SELECT("__tcPkeyList")
334
335 *---TR 1064618 02/22/2013 YE
336 .oLog.LogResult(llRetVal, lcHeading)
337 *===TR 1064618 02/22/2013 YE
338
339 ENDWITH
340
341 SELECT (lnSelect)
342 RETURN llRetVal
343 ENDPROC
344
345*------------------------------------------------------------
346
347 PROCEDURE CleanUp
348
349 WITH This
350 v_sqlexecnoerror('drop table ' + .cPickCursor)
351 v_sqlexecnoerror('drop table ' + .cPickHdrKeyTmp)
352 v_sqlexecnoerror('drop table ' + .cPickDtlKeyTmp)
353 v_sqlexecnoerror('drop table ' + .cPickHeaderTmp)
354 v_sqlexecnoerror('drop table ' + .cPickDetailTmp)
355 v_sqlexecnoerror('drop table ' + .cOrdSPTmp)
356
357 IF IsObject(.oBPOPayflowPro ,.T.)
358 .oBPOPayflowPro.Destroy()
359 .oBPOPayflowPro = .NULL.
360 ENDIF
361 ENDWITH
362
363 ENDPROC
364
365*-------------------------------------------------------------------
366
367 PROCEDURE LogEntry
368 LPARAMETERS tcText, tnVal
369
370 This.oLog.LogEntry(tcText)
371
372 * progress bar
373 IF VARTYPE(tnVal) = 'N'
374 This.UpdateProgressBar(tcText, tnVal)
375 ENDIF
376 ENDPROC
377
378*----------------------------------------------------------------------------
379
380 PROCEDURE ValidateOrders
381
382 *---TR 1064618 02/22/2013 YE === New variables lcHeading and lcSQL
383 LOCAL llRetVal,lnSelect, lcHeading, lcSQL
384 llRetVal = .T.
385 lnSelect = SELECT()
386
387 WITH THIS
388
389 *---TR 1064618 02/22/2013 YE
390 lcHeading = " Validating Picks "
391 .oLog.LogMajorStage(lcHeading)
392 *===TR 1064618 02/22/2013 YE
393
394 *---TR 1064618 02/22/2013 YE
395 *llRetVal = llRetVal AND v_SQLExec("DELETE FROM " + .cPickHeaderTmp + " WHERE division NOT IN (SELECT division FROM zzxcntrc)")
396 lcSQL = "DELETE FROM " + .cPickHeaderTmp + " WHERE division NOT IN (SELECT division FROM zzxcntrc)"
397 llRetVal = llRetVal AND v_SQLExec(lcSQL)
398
399 IF NOT llRetVal
400 .LogEntry("SQL failed: " + lcSQL)
401 ENDIF
402 *===TR 1064618 02/22/2013 YE
403
404 * Regular credit checking goes here....
405
406 *---TR 1064618 02/22/2013 YE - Commented logging and moved inside the routine
407 *.LogEntry("Begin Credit Checking.")
408
409 llRetVal = llRetVal AND .CreditCheck()
410
411 *--- TechRec 1052703 04-Aug-2011 TShenbagavalli ---
412 * Validating Carton Stage
413
414 *---TR 1064618 02/22/2013 YE - Commented logging and moved inside the routine
415 *.LogEntry("Validating Carton Stage.")
416 *===TR 1064618 02/22/2013 YE
417
418 llRetVal = llRetVal AND .ValidateCartonStage()
419 *=== TechRec 1052703 04-Aug-2011 TShenbagavalli ===
420
421 *--- TechRec 1065054 13-Mar-2013 vkrishnamurthy ---
422 llRetVal = llRetVal AND .ValidateFrozenCostsheetCheck()
423 llRetVal = llRetVal AND .ValidateFrozenCostsheetCheck(.t.)
424 *=== TechRec 1065054 13-Mar-2013 vkrishnamurthy ===
425
426 * If any headers have been removed, whack the details
427
428 *---TR 1064618 02/22/2013 YE
429 *llRetVal = llRetVal AND v_sqlexec("DELETE FROM " + .cPickDetailTmp + " WHERE fkey NOT IN (select pkey FROM " + .cPickHeaderTmp + ")")
430 lcSQL = "DELETE FROM " + .cPickDetailTmp + " WHERE fkey NOT IN (select pkey FROM " + .cPickHeaderTmp + ")"
431 llRetVal = llRetVal AND v_sqlexec(lcSQL)
432
433 IF NOT llRetVal
434 .LogEntry("SQL failed: " + lcSQL)
435 ENDIF
436
437 *llRetVal = llRetVal AND v_sqlexec("DELETE FROM " + .cPickDtlKeyTmp + " WHERE fkey NOT IN (SELECT open_pkey FROM " + .cPickHeaderTmp + ")")
438 lcSQL ="DELETE FROM " + .cPickDtlKeyTmp + " WHERE fkey NOT IN (SELECT open_pkey FROM " + .cPickHeaderTmp + ")"
439 llRetVal = llRetVal AND v_sqlexec(lcSQL)
440
441 IF NOT llRetVal
442 .LogEntry("SQL failed: " + lcSQL)
443 ENDIF
444
445 *llRetVal = llRetVal AND v_sqlexec("DELETE FROM " + .cPickHdrKeyTmp + " WHERE pkey NOT IN (SELECT open_pkey FROM " + .cPickHeaderTmp + ")")
446 lcSQL = "DELETE FROM " + .cPickHdrKeyTmp + " WHERE pkey NOT IN (SELECT open_pkey FROM " + .cPickHeaderTmp + ")"
447 llRetVal = llRetVal AND v_sqlexec(lcSQL)
448
449 IF NOT llRetVal
450 .LogEntry("SQL failed: " + lcSQL)
451 ENDIF
452
453 .oLog.LogResult(llRetVal, lcHeading)
454 *===TR 1064618 02/22/2013 YE
455
456 ENDWITH
457
458 SELECT (lnSelect)
459 RETURN llRetVal
460
461 ENDPROC
462
463*-------------------------------------------------------------------
464
465 PROCEDURE GenerateInvoices
466
467 *--- TechRec 1056032 17-Aug-2011 jjanand === Added lcSqlUpdate
468 *---TR 1064618 02/22/2013 YE === New variable lcHeading
469 LOCAL llRetVal,lnSelect,lcOHList,lcODList,lcSqlUpdate, lcHeading
470
471 LOCAL lnHdrCount,lnDtlCount,lnNewHdrPkey,lnNewDtlPkey,lnInv_Num,lcPckTmp,lnSPPkey,lcSPList
472
473 *--- TechRec 1064555 10-May-2013 vkrishnamurthy ---
474 LOCAL lcOrgCode , lcCursor ,llOrgControl ,lcSQL
475 lcCursor = GetUniqueFileName()
476 *=== TechRec 1064555 10-May-2013 vkrishnamurthy ===
477
478 llRetVal = .T.
479 lnSelect = SELECT()
480
481 WITH THIS
482
483 *---TR 1064618 02/22/2013 YE
484 lcHeading = " Generating Invoices "
485 .oLog.LogMajorStage(lcHeading)
486 *===TR 1064618 02/22/2013 YE
487
488 v_sqlexec("SELECT COUNT(*) as cnt FROM " + .cPickHeaderTmp,"tcHdrCnt")
489 v_sqlexec("SELECT COUNT(*) as cnt FROM " + .cPickDetailTmp,"tcDtlCnt")
490
491 *--- TechRec 1075812 29-Jan-2014 vkrishnamurthy ---
492*!* v_sqlexec("SELECT division,COUNT(*) as cnt FROM " + .cPickHeaderTmp + " GROUP BY division ","tcDivCount")
493 v_sqlexec("SELECT division,ord_type,COUNT(*) as cnt FROM " + .cPickHeaderTmp + " t " + ;
494 " where Not exists ( select ord_type from zzotyper where ord_type = t.ord_type and str_inv>=0 and end_inv>0 ) "+ ;
495 " GROUP BY division,ord_type ","tcDivCount")
496 *=== TechRec 1075812 29-Jan-2014 vkrishnamurthy ===
497
498 lnHdrCount = tcHdrCnt.cnt
499 lnDtlCount = tcDtlCnt.cnt
500
501 lcSHList = getSortedFieldList("ZZOSHPRH")
502 lcSDList = getSortedFieldList("ZZOSHPRD")
503 lcSPList = getSortedFieldList("ZZOORDSP")
504
505 * Assign New Keys
506 lnNewDtlPkey = v_NextPkey("ZZOSHPRD",lnDtlCount) - lnDtlCount
507 lnNewHdrPkey = v_NextPkey("ZZOSHPRH",lnHdrCount) - lnHdrCount
508
509 *--- TechRec 1056032 17-Aug-2011 jjanand ---
510 *lnSPPkey = v_NextPkey("ZZOORDSP",lnHdrCount) - lnHdrCount
511 *=== TechRec 1056032 17-Aug-2011 jjanand ===
512
513 *--- TR 1056929 03-Oct-11 SK Ignoring time values in GETDATE() using SQLFormatTS function
514 llRetVal = llRetVal AND v_sqlexec(;
515 "UPDATE " + .cPickHeaderTmp + ;
516 " set pkey = pkey2 + " + SQLFormatNum(lnNewHdrPkey) + ;
517 " ,last_mod = " + SQLFormatTS(DATETIME()) + ;
518 " ,user_id = " + SQLformatChar(goEnv.cUser) + ;
519 " ,ship_date = CASE WHEN ship_date = '1900-01-01 00:00:00.000' THEN " + SQLFormatTS(DATE()) + " ELSE ship_date END " + ;
520 " ,trans_date = " + SQLFormatTS(DATE()) + ;
521 " ,ack_prn = 'N' " + ;
522 " ,ar_post = 'N' " + ;
523 " ,fappv_amt = 0 " + ;
524 " ,ord_status = 'I' ")
525 *=== TR 1056929 03-Oct-11 SK
526
527 .LogEntry("Assigning invoice numbers.")
528
529 *--- TechRec 1064555 10-May-2013 vkrishnamurthy ---
530 lcSQL = " Select org.org_code from zzxorgnr org " + ;
531 " join " + .cPickHeaderTmp + " t " + ;
532 " on t.org_code = org.org_code " + ;
533 " and str_inv >=0 and end_inv > 0 "
534 llOrgControl = V_sqlexec(lcSQL ,lcCursor ) and RECCOUNT(lcCursor)>0
535 IF llOrgControl
536 lcOrgCount = "tcOrgCount"
537 lcSql = "SELECT org_code, COUNT(*) as orgCount, 0 as inv_num FROM " + .cPickHeaderTmp + " group by org_code "
538 llRetVal = llRetVal and v_sqlexec(lcSql,lcOrgCount)
539
540 SELECT tcOrgCount
541 SCAN
542 lcPckTmp = "#" + GetUniqueFileName()
543 llRetVal = llRetVal = v_sqlexec(;
544 "SELECT pkey,inv_num,IDENTITY(int) as pkey2 INTO " + lcPckTmp + ;
545 " FROM " + .cPickHeaderTmp + " WHERE org_code = " + SQLFormatChar(tcOrgCount.org_code))
546
547 *lnInv_Num = v_nextpickinv("inv",tcDivCount.division,tcDivCount.cnt ) - tcDivCount.cnt
548 *======
549 lcSQL = "bcsp_GetNextOrgInv_num" + SQLFormatChar(tcOrgCount.org_code)+ ","+ SQLFormatNum(tcOrgCount.orgCount)
550 llRetVal = v_SQLExec(lcSQL , 'tcOrgcnt')
551 IF llRetVal AND RECCOUNT('tcOrgcnt')>0
552 SELECT ('tcOrgcnt')
553 lnInv_Num = Exp - tcOrgCount.orgCount &&--- TechRec 1073552 20-Sep-2013 vkrishnamurthy ===
554 ENDIF
555 *======
556
557 llRetVal = llRetVal AND v_sqlexec(;
558 "UPDATE " + lcPckTmp + " SET inv_num = pkey2 + " + SQLFormatNum(lnInv_Num))
559
560 llRetVal = llRetVal AND v_sqlexec(;
561 "UPDATE h set inv_num = t.inv_num " + ;
562 " FROM " + .cPickHeaderTmp + " h JOIN " + lcPckTmp + " t ON h.pkey = t.pkey ")
563
564 v_sqlexecnoerror("DROP TABLE " + lcPckTmp)
565 ENDSCAN
566 ELSE
567 *=== TechRec 1064555 10-May-2013 vkrishnamurthy ===
568 *---1072881/1073552 12-Sep-13 Venuk.
569 *--- TechRec 1075812 29-Jan-2014 vkrishnamurthy ---
570*!* lcSQL = " Select typ.ord_type from zzotyper typ " + ;
571*!* " join " + .cPickHeaderTmp + " t " + ;
572*!* " on t.ord_type = typ.ord_type " + ;
573*!* " and str_inv >=0 and end_inv > 0 "
574
575*!* llOrdTypeControl = V_sqlexec(lcSQL ,lcCursor ) and RECCOUNT(lcCursor)>0
576*!* IF llOrdTypeControl
577 *=== TechRec 1075812 29-Jan-2014 vkrishnamurthy === also modified the below query
578 lcOrdTypeCount = "tcOrdTypeCount"
579 lcSql = "SELECT t.Ord_type , COUNT(*) as ordTypeCount, 0 as inv_num FROM " + .cPickHeaderTmp + " t " +;
580 " Join zzotyper typ on t.ord_type = typ.ord_type "+ ;
581 " and typ.str_inv >=0 and typ.end_inv > 0 " + ;
582 " group by t.Ord_type "
583 llRetVal = llRetVal and v_sqlexec(lcSql,lcOrdTypeCount)
584
585 SELECT tcOrdTypeCount
586 SCAN
587
588 lcPckTmp = "#" + GetUniqueFileName()
589 llRetVal = llRetVal = v_sqlexec(;
590 "SELECT pkey,inv_num,IDENTITY(int) as pkey2 INTO " + lcPckTmp + ;
591 " FROM " + .cPickHeaderTmp + " WHERE ord_type = " + SQLFormatChar(tcOrdTypeCount.ord_type))
592
593 lcSQL = "bcsp_GetNextOrdTypeInv_num " + SQLFormatChar(tcOrdTypeCount.ord_type)+ ","+ SQLFormatNum(tcOrdTypeCount.ordTypeCount)
594 llRetVal = v_SQLExec(lcSQL , 'tcOrdTypecnt')
595 IF llRetVal AND RECCOUNT('tcOrdTypecnt')>0
596 SELECT ('tcOrdTypecnt')
597 lnInv_Num = Exp - tcOrdTypeCount.ordTypeCount
598 ENDIF
599
600 llRetVal = llRetVal AND v_sqlexec(;
601 "UPDATE " + lcPckTmp + " SET inv_num = pkey2 + " + SQLFormatNum(lnInv_Num))
602
603 llRetVal = llRetVal AND v_sqlexec(;
604 "UPDATE h set inv_num = t.inv_num " + ;
605 " FROM " + .cPickHeaderTmp + " h JOIN " + lcPckTmp + " t ON h.pkey = t.pkey ")
606
607 v_sqlexecnoerror("DROP TABLE " + lcPckTmp)
608 ENDSCAN
609*!* ENDIF &&--- TechRec 1075812 29-Jan-2014 vkrishnamurthy ===
610 *=== 1072881/1073552 12-Sep-13 Venuk.
611 * Assign New Inv num.
612 SELECT tcDivCount
613 SCAN
614 lcPckTmp = "#" + GetUniqueFileName()
615 *--- TechRec 1075812 30-Jan-2014 vkrishnamurthy ---
616*!* llRetVal = llRetVal = v_sqlexec(;
617*!* "SELECT pkey,inv_num,IDENTITY(int) as pkey2 INTO " + lcPckTmp + ;
618*!* " FROM " + .cPickHeaderTmp + " WHERE division = " + SQLFormatChar(tcDivCount.division))
619
620 llRetVal = llRetVal = v_sqlexec(;
621 "SELECT pkey,inv_num,ord_type,IDENTITY(int) as pkey2 INTO " + lcPckTmp + ;
622 " FROM " + .cPickHeaderTmp + " WHERE division = " + SQLFormatChar(tcDivCount.division) + ;
623 " and ord_type = " + SQLFormatChar(tcDivCount.ord_type))
624
625 *=== TechRec 1075812 30-Jan-2014 vkrishnamurthy ===
626 lnInv_Num = v_nextpickinv("inv",tcDivCount.division,tcDivCount.cnt ) - tcDivCount.cnt
627 llRetVal = llRetVal AND v_sqlexec(;
628 "UPDATE " + lcPckTmp + " SET inv_num = pkey2 + " + SQLFormatNum(lnInv_Num))
629
630 llRetVal = llRetVal AND v_sqlexec(;
631 "UPDATE h set inv_num = t.inv_num " + ;
632 " FROM " + .cPickHeaderTmp + " h JOIN " + lcPckTmp + " t ON h.pkey = t.pkey ")
633
634 v_sqlexecnoerror("DROP TABLE " + lcPckTmp)
635 ENDSCAN
636*!* ENDIF &&--- 1072881/1073552 12-Sep-13 Venuk. ===
637 *--- TechRec 1064555 10-May-2013 vkrishnamurthy ---
638 Endif
639 *=== TechRec 1064555 10-May-2013 vkrishnamurthy ===
640 * Cascade to Detail
641 llRetVal = llRetVal AND v_sqlexec(;
642 "UPDATE d " + ;
643 " set inv_num = h.inv_num " + ;
644 " ,pkey = d.pkey2 + " + SQLFormatNum(lnNewDtlPkey) + ;
645 " ,fkey = h.pkey " + ;
646 " ,last_mod = " + SQLFormatTS(DATETIME()) + ;
647 " ,user_id = " + SQLformatChar(goEnv.cUser) + ;
648 " ,line_status = 'I' " + ;
649 " ,obr_proc = 'N' " + ;
650 " FROM " + .cPickDetailTmp + " d " + ;
651 " JOIN " + .cPickHeaderTmp + " h ON d.fkey = h.orig_key ")
652
653 * Calc Ord Qty.
654 llRetVal = llRetVal AND v_sqlexec(;
655 "UPDATE h " + ;
656 " SET ord_qty = COALESCE(d.total_qty,0) " + ;
657 " FROM " + .cPickHeaderTmp + " h " + ;
658 " LEFT OUTER JOIN (SELECT fkey,SUM(total_qty) total_qty FROM " + .cPickDetailTmp + " GROUP BY fkey) d " + ;
659 " ON h.pkey = d.fkey ")
660
661 * set flags and other properties
662 llRetVal = llRetVal AND .UpdateInvoices()
663
664 *--- TechRec 1056032 17-Aug-2011 jjanand ---
665 *.LogEntry("Adding zzoordsp records.")
666 * SP Records
667 *llRetVal = llRetVal AND V_sqlexec(;
668 "INSERT INTO " + .cOrdSPTmp + ;
669 " (ord_num,inv_num,user_id,last_mod,carton_cls,packs)" + ;
670 " SELECT ord_num,inv_num,user_id,last_mod,'S','N' " + ;
671 " FROM " + .cPickHeaderTmp)
672
673 *llRetVal = llRetVal AND v_sqlexec(;
674 "UPDATE " + .cOrdSPTmp + " set pkey = pkey2 + " + SQLFormatNum(lnSPPkey))
675 *=== TechRec 1056032 17-Aug-2011 jjanand ===
676
677 *--- TR 1087654 06/22/15 ATHIRUNAVU CASCADING ship_date & ar_period
678 llRetVal = llRetVal AND v_sqlexec(;
679 "UPDATE d " + ;
680 " SET ship_date_cascaded = h.ship_date, " + ;
681 " ar_period_Cascaded = h.ar_period " + ;
682 " FROM " + .cPickDetailTmp + " d " + ;
683 " JOIN " + .cPickHeaderTmp + " h ON d.fkey = h.pkey ")
684 *=== TR 1087654 06/22/15 ATHIRUNAVU
685
686 *--- TechRec 1058339 03-Jan-2012 jisingh ---
687 LOCAL lcCopyExtHdr, lcCopyExtDtl
688 lcCopyExtHdr = "#" + GetUniqueFileName()
689 lcCopyExtDtl = "#" + GetUniqueFileName()
690
691 .LogEntry("Generating Extender Temporary Tables.")
692
693 * Copy Header Extender
694 llRetVal = llRetVal AND v_SQLExec(;
695 " SELECT u.*, t.pkey as new_hdrfkey, 0 as new_dtlfkey, t.pkey2 " + ;
696 " INTO " + lcCopyExtHdr + ;
697 " FROM zzzudfdt u " + ;
698 " JOIN " + .cPickHeaderTmp + " t ON u.hdrfkey = t.orig_key AND u.dtlfkey = 0 ")
699
700 llRetVal = llRetVal AND v_SQLExec(;
701 " UPDATE " + lcCopyExtHdr + " SET hdrfkey = new_hdrfkey, dtlfkey = new_dtlfkey ")
702
703 * Copy Detail Extender
704 llRetVal = llRetVal AND v_SQLExec(;
705 " SELECT u.*, t.pkey as new_dtlfkey, t.fkey as new_hdrfkey, t.pkey2 " + ;
706 " INTO " + lcCopyExtDtl + ;
707 " FROM zzzudfdt u " + ;
708 " JOIN " + .cPickDetailTmp + " t ON u.hdrfkey = t.open_fkey AND u.dtlfkey = t.orig_key ")
709
710 llRetVal = llRetVal AND v_SQLExec(;
711 " UPDATE " + lcCopyExtDtl + " SET hdrfkey = new_hdrfkey, dtlfkey = new_dtlfkey ")
712
713 IF .lResolveCharges
714 LOCAL lcChgTmpHdr, lcChgTmpDtl
715 lcChgTmpHdr = "#" + GetUniqueFileName()
716 lcChgTmpDtl = "#" + GetUniqueFileName()
717
718 .LogEntry("Generating Charges Temporary Tables.")
719
720 * Copy Header Charges
721 llRetVal = llRetVal AND v_SQLExec(;
722 " SELECT c.*, t.pick_num as new_pick, t.inv_num as new_inv, t.pkey as new_fkey, t.pkey2 " + ;
723 " INTO " + lcChgTmpHdr + ;
724 " FROM zzxchrgs c " + ;
725 " JOIN " + .cPickHeaderTmp + " t ON c.fkey = t.orig_key ")
726
727 llRetVal = llRetVal and v_SQLExec(;
728 " UPDATE " + lcChgTmpHdr + " SET pick_num = new_pick, inv_num = new_inv, fkey = new_fkey ")
729
730 * Copy Detail Charges
731 llRetVal = llRetVal AND v_SQLExec(;
732 " SELECT c.*, t.pick_num as new_pick, t.inv_num as new_inv, t.pkey as new_fkey, t.fkey as new_hdr_fkey, t.pkey2 " + ;
733 " INTO " + lcChgTmpDtl + ;
734 " FROM zzxchrds c " + ;
735 " JOIN " + .cPickDetailTmp + " t ON c.fkey = t.orig_key ")
736
737 llRetVal = llRetVal and v_SQLExec(;
738 " UPDATE " + lcChgTmpDtl + " SET pick_num = new_pick, inv_num = new_inv, fkey = new_fkey, hdrfkey = new_hdr_fkey ")
739 ENDIF
740
741 IF .lUseFRMCode
742 LOCAL lcCopyFrmHdr, lcCopyFrmDtl
743 lcCopyFrmHdr = "#" + GetUniqueFileName()
744 lcCopyFrmDtl = "#" + GetUniqueFileName()
745
746 .LogEntry("Generating FRM Temporary Tables.")
747
748 * Copy Header FRM
749 llRetVal = llRetVal AND v_SQLExec(;
750 " SELECT f.*, t.pkey as new_hdrfkey, 0 as new_dtlfkey, t.pkey2 " + ;
751 " INTO " + lcCopyFrmHdr + ;
752 " FROM zzofchrg f " + ;
753 " JOIN " + .cPickHeaderTmp + " t ON f.hdrfkey = t.orig_key AND f.dtlfkey = 0 " + ;
754 " WHERE t.frmcode_ok = 'Y' " )
755
756 llRetVal = llRetVal AND v_SQLExec(;
757 " UPDATE " + lcCopyFrmHdr + " SET hdrfkey = new_hdrfkey, dtlfkey = new_dtlfkey ")
758
759 * Copy Detail FRM
760 llRetVal = llRetVal AND v_SQLExec(;
761 " SELECT f.*, t.pkey as new_dtlfkey, t.fkey as new_hdrfkey, t.pkey2 " + ;
762 " INTO " + lcCopyFrmDtl + ;
763 " FROM zzofchrg f " + ;
764 " JOIN " + .cPickDetailTmp + " t ON f.hdrfkey = t.open_fkey AND f.dtlfkey = t.orig_key " + ;
765 " WHERE t.frmcode_ok = 'Y' ")
766
767 llRetVal = llRetVal AND v_SQLExec(;
768 " UPDATE " + lcCopyFrmDtl + " SET hdrfkey = new_hdrfkey, dtlfkey = new_dtlfkey ")
769 ENDIF
770 *=== TechRec 1058339 03-Jan-2012 jisingh ===
771
772 *--- TechRec 1060623 25-Jun-2012 jisingh ---
773 llRetVal = llRetVal AND Copy_UDF_Data_Backup(This.cPickHeaderTmp, "orig_key", "zzoordrh")
774 llRetVal = llRetVal AND Copy_UDF_Data_Backup(This.cPickDetailTmp, "orig_key", "zzoordrd")
775 *=== TechRec 1060623 25-Jun-2012 jisingh ===
776
777 *Transaction
778 LOCAL llBeginTransaction
779 IF llRetVal
780 llBeginTransaction = this.BeginTransaction()
781 ENDIF
782
783 IF llRetVal AND llBeginTransaction
784 * Insert Header
785 llRetVal = llRetVal AND v_sqlexec(;
786 "INSERT INTO zzoshprh (" + lcSHList + ")SELECT " + lcSHList + " FROM " + .cPickHeaderTmp)
787 * Insert Detail
788 llRetVal = llRetVal AND v_sqlexec(;
789 "INSERT INTO zzoshprd (" + lcSDList + ")SELECT " + lcSDList + " FROM " + .cPickDetailTmp)
790 * Update SP
791
792 *--- TechRec 1056032 17-Aug-2011 jjanand ---
793 *llRetVal = llRetVal AND v_sqlexec(;
794 "INSERT INTO zzoordsp (" + lcSPList + ")SELECT " + lcSPList + " FROM " + .cOrdSPTmp)
795 lcSqlUpdate = " UPDATE s SET inv_num = p.inv_num " + ;
796 " FROM " + .cPickHeaderTmp + " p " + ;
797 " JOIN zzoordsp s ON s.ord_num = p.ord_num AND s.pick_num = p.pick_num " + ;
798 " AND s.inv_num = 0 "
799 llRetVal = llRetVal AND v_sqlexec(lcSqlUpdate)
800 *=== TechRec 1056032 17-Aug-2011 jjanand ===
801
802 * Delete Open Details
803 llRetVal = llRetVal AND v_sqlexec("DELETE FROM zzoordrd where fkey IN (select orig_key from " + .cPickHeaderTmp + ")")
804
805 * Delete orphan headers (ord_qty = 0)
806 llRetVal = llRetVal AND v_sqlexec(;
807 "DELETE FROM zzoordrh where pkey IN (select orig_key from " + .cPickHeaderTmp + ")")
808
809 ENDIF
810
811 IF llRetVal
812 .LogEntry("Tax Resolution.")
813 *--- TechRec 1075909 15-Jan-2014 asharma --- at this stage invoice in created, tax resolution should be from shipper table
814 *llRetVal = llRetVal and setbased_taxresolution(.cPickHeaderTmp,,.t.)
815 llRetVal = llRetVal and setbased_taxresolution(.cPickHeaderTmp,,.f.)
816 *=== TechRec 1075909 15-Jan-2014 asharma ===
817 ENDIF
818
819 *--- TR 1055775 11-10-2011 RKI ---*
820 *Calculate Materialize Standard Cost - Optimized Auto Bill Process
821 IF llRetVal
822 .LogEntry("Account Cost Resolution.")
823 llRetVal = llRetVal and Setbased_MaterializeStandardCost(.cPickHeaderTmp,,.f.)
824 ENDIF
825 *=== TR 1055775 11-10-2011 RKI ===*
826
827 *--- TR 1071221 21-Nov-2014 BNarayanan ---
828 *llRetVal = llRetVal AND .UpdateCCInvoice()
829 llRetVal = llRetVal AND .UpdateInvoicedCC()
830 *=== TR 1071221 21-Nov-2014 BNarayanan ===
831
832 *--- TechRec 1058339 03-Jan-2012 jisingh ---
833 LOCAL loSB
834 loSB = NEWOBJECT("SQLStringBuilder","clsgnstr.fxp")
835
836 .LogEntry("Moving Extenders.")
837 WITH loSB
838 * Header Extender Insert
839 .SB_Reset()
840 .cSB_Target = "zzzudfdt"
841 .cSB_Source = lcCopyExtHdr
842 .cSB_SourceAlias = "h"
843
844 .SB_MapFromToFields("h.hdrfkey","hdrfkey")
845 .SB_MapFromToFields("h.dtlfkey","dtlfkey")
846 .SB_MapFromToFields("h.notes","notes")
847
848 .SB_KeySetAddKeySpec("pkey", "Int", "h.pkey")
849 .SB_KeySetAddKeySpec("pkey2", "Int", "h.pkey2")
850
851 llRetVal = llRetVal AND .SB_GenerateSQLInsertString()
852 llRetVal = llRetVal AND v_SQLExec(.cSqlString)
853
854 llRetVal = llRetVal AND v_SQLExec(;
855 " DELETE u FROM zzzudfdt u " + ;
856 " JOIN " + This.cPickHeaderTmp + " t " + ;
857 " ON u.hdrfkey = t.orig_key AND u.dtlfkey = 0")
858
859 * Detail Extender Insert
860 .SB_Reset()
861 .cSB_Target = "zzzudfdt"
862 .cSB_Source = lcCopyExtDtl
863 .cSB_SourceAlias = "d"
864
865 .SB_MapFromToFields("d.hdrfkey","hdrfkey")
866 .SB_MapFromToFields("d.dtlfkey","dtlfkey")
867 .SB_MapFromToFields("d.notes","notes")
868
869 .SB_KeySetAddKeySpec("pkey", "Int", "d.pkey")
870 .SB_KeySetAddKeySpec("pkey2", "Int", "d.pkey2")
871
872 llRetVal = llRetVal AND .SB_GenerateSQLInsertString()
873 llRetVal = llRetVal AND v_SQLExec(.cSqlString)
874
875 llRetVal = llRetVal AND v_SQLExec(;
876 " DELETE u FROM zzzudfdt u " + ;
877 " JOIN " + This.cPickDetailTmp + " t " + ;
878 " ON u.dtlfkey = t.orig_key AND u.hdrfkey = t.open_fkey")
879 ENDWITH
880
881 v_SQLExecNoError("DROP TABLE " + lcCopyExtHdr)
882 v_SQLExecNoError("DROP TABLE " + lcCopyExtDtl)
883
884 IF llRetVal AND .lResolveCharges
885 .LogEntry("Moving Charges.")
886 WITH loSB
887 * Header Insert
888 .SB_Reset()
889 .cSB_Target = "zzxchrgs"
890 .cSB_Source = lcChgTmpHdr
891 .cSB_SourceAlias = "c"
892
893 .SB_MapFromToFields("c.fkey","fkey")
894 .SB_MapFromToFields("c.pick_num","pick_num")
895 .SB_MapFromToFields("c.inv_num","inv_num")
896 .SB_MapFromToFields("c.notes","notes")
897
898 .SB_KeySetAddKeySpec("pkey", "Int", "c.pkey")
899 .SB_KeySetAddKeySpec("pkey2", "Int", "c.pkey2")
900
901 llRetVal = llRetVal AND .SB_GenerateSQLInsertString()
902 llRetVal = llRetVal AND v_SQLExec(.cSqlString)
903
904 llRetVal = llRetVal AND v_SQLExec(;
905 " DELETE c FROM zzxchrgs c " + ;
906 " JOIN " + This.cPickHeaderTmp + " t ON c.fkey = t.orig_key")
907
908 * Detail Insert
909 .SB_Reset()
910 .cSB_Target = "zzxchrds"
911 .cSB_Source = lcChgTmpDtl
912 .cSB_SourceAlias = "c"
913
914 .SB_MapFromToFields("c.fkey","fkey")
915 .SB_MapFromToFields("c.hdrfkey","hdrfkey")
916 .SB_MapFromToFields("c.pick_num","pick_num")
917 .SB_MapFromToFields("c.inv_num","inv_num")
918 .SB_MapFromToFields("c.notes","notes")
919
920 .SB_KeySetAddKeySpec("pkey", "Int", "c.pkey")
921 .SB_KeySetAddKeySpec("pkey2", "Int", "c.pkey2")
922
923 llRetVal = llRetVal AND .SB_GenerateSQLInsertString()
924 llRetVal = llRetVal AND v_SQLExec(.cSqlString)
925
926 llRetVal = llRetVal AND v_SQLExec(;
927 " DELETE c FROM zzxchrds c " + ;
928 " JOIN " + This.cPickDetailTmp + " t ON c.fkey = t.orig_key")
929 ENDWITH
930
931 v_SQLExecNoError("DROP TABLE " + lcChgTmpHdr)
932 v_SQLExecNoError("DROP TABLE " + lcChgTmpDtl)
933 ENDIF
934
935 IF llRetVal AND .lUseFRMCode
936 .LogEntry("Moving FRMs.")
937 WITH loSB
938 * Header Insert
939 .SB_Reset()
940 .cSB_Target = "zzofchrg"
941 .cSB_Source = lcCopyFrmHdr
942 .cSB_SourceAlias = "h"
943
944 .SB_MapFromToFields("h.hdrfkey","hdrfkey")
945 .SB_MapFromToFields("h.dtlfkey","dtlfkey")
946 .SB_MapFromToFields("h.notes","notes")
947
948 .SB_KeySetAddKeySpec("pkey", "Int", "h.pkey")
949 .SB_KeySetAddKeySpec("pkey2", "Int", "h.pkey2")
950
951 llRetVal = llRetVal AND .SB_GenerateSQLInsertString()
952 llRetVal = llRetVal AND v_SQLExec(.cSqlString)
953
954 llRetVal = llRetVal AND v_SQLExec(;
955 " DELETE f FROM zzofchrg f " + ;
956 " JOIN " + This.cPickHeaderTmp + " t " + ;
957 " ON f.hdrfkey = t.orig_key AND f.dtlfkey = 0")
958
959 * Detail Insert
960 .SB_Reset()
961 .cSB_Target = "zzofchrg"
962 .cSB_Source = lcCopyFrmDtl
963 .cSB_SourceAlias = "d"
964
965 .SB_MapFromToFields("d.hdrfkey","hdrfkey")
966 .SB_MapFromToFields("d.dtlfkey","dtlfkey")
967 .SB_MapFromToFields("d.notes","notes")
968
969 .SB_KeySetAddKeySpec("pkey", "Int", "d.pkey")
970 .SB_KeySetAddKeySpec("pkey2", "Int", "d.pkey2")
971
972 llRetVal = llRetVal AND .SB_GenerateSQLInsertString()
973 llRetVal = llRetVal AND v_SQLExec(.cSqlString)
974
975 llRetVal = llRetVal AND v_SQLExec(;
976 " DELETE f FROM zzofchrg f " + ;
977 " JOIN " + This.cPickDetailTmp + " t " + ;
978 " ON f.dtlfkey = t.orig_key AND f.hdrfkey = t.open_fkey")
979
980 LOCAL lcFRMHdrReso, lcFRMDtlReso, loFRM
981 lcFRMHdrReso = "#" + GetUniqueFileName()
982 lcFRMDtlReso = "#" + GetUniqueFileName()
983
984 llRetVal = llRetVal AND v_SQLExec(;
985 " SELECT h.* INTO " + lcFRMHdrReso + " FROM " + This.cPickHeaderTmp + " h " + ;
986 " WHERE h.frmcode_ok <> 'Y' ")
987
988 llRetVal = llRetVal AND v_SQLExec(;
989 " SELECT d.* INTO " + lcFRMDtlReso + " FROM " + This.cPickDetailTmp + " d " + ;
990 " WHERE d.frmcode_ok <> 'Y' ")
991
992 loFRM = NEWOBJECT("BPOSOFRMCharges", "clsfrmch_opt.prg")
993 loFRM.cQ_Hdr = lcFRMHdrReso
994 loFRM.cQ_Dtl = lcFRMDtlReso
995 loFRM.lResolveTempTables = .F.
996 lOFRM.lByPassTransaction = .T.
997
998 IF llRetVal
999 =loFRM.GenBulkDefaultFRMCode()
1000 ENDIF
1001 loFRM.Destroy
1002 loFRM = NULL
1003 ENDWITH
1004
1005 v_SQLExecNoError("DROP TABLE " + lcCopyFrmHdr)
1006 v_SQLExecNoError("DROP TABLE " + lcCopyFrmDtl)
1007 v_SQLExecNoError("DROP TABLE " + lcFRMHdrReso)
1008 v_SQLExecNoError("DROP TABLE " + lcFRMDtlReso)
1009 ENDIF
1010 *=== TechRec 1058339 03-Jan-2012 jisingh ===
1011
1012 *--- TechRec 1060623 25-Jun-2012 jisingh ---
1013 *--- TechRec 1063736 30-Aug-2012 jisingh ---
1014*!* llRetVal = llRetVal AND v_SQLExec(;
1015*!* " SELECT orig_key AS oldpkey, pkey AS newpkey " + ;
1016*!* " FROM " + This.cPickHeaderTmp, "tcPick")
1017*!*
1018*!* IF llRetVal AND USED("tcPick") AND RECCOUNT("tcPick") > 0
1019*!* SELECT tcPick
1020*!* SCAN
1021*!* llRetVal = llRetVal AND Copy_UDF_Data_Infinity(tcPick.oldpkey, tcPick.newpkey, "zzoordrh", "zzoshprh")
1022*!* ENDSCAN
1023*!* USE IN SELECT("tcPick")
1024*!* ENDIF
1025
1026*!* llRetVal = llRetVal AND v_SQLExec(;
1027*!* " SELECT orig_key AS oldpkey, pkey AS newpkey " + ;
1028*!* " FROM " + This.cPickDetailTmp, "tcPick")
1029*!*
1030*!* IF llRetVal AND USED("tcPick") AND RECCOUNT("tcPick") > 0
1031*!* SELECT tcPick
1032*!* SCAN
1033*!* llRetVal = llRetVal AND Copy_UDF_Data_Infinity(tcPick.oldpkey, tcPick.newpkey, "zzoordrd", "zzoshprd")
1034*!* ENDSCAN
1035*!* USE IN SELECT("tcPick")
1036*!* ENDIF
1037
1038 llRetVal = llRetVal AND Copy_UDF_Data_Infinity(,, "zzoordrh", "zzoshprh",, .T., This.cPickHeaderTmp, "orig_key", "pkey")
1039 llRetVal = llRetVal AND Copy_UDF_Data_Infinity(,, "zzoordrd", "zzoshprd",, .T., This.cPickDetailTmp, "orig_key", "pkey")
1040 *=== TechRec 1063736 30-Aug-2012 jisingh ===
1041
1042 llRetVal = llRetVal AND Copy_UDF_Data_Drop("zzoordrh")
1043 llRetVal = llRetVal AND Copy_UDF_Data_Drop("zzoordrd")
1044 *=== TechRec 1060623 25-Jun-2012 jisingh ===
1045
1046 IF llBeginTransaction
1047 IF llRetVal
1048 .EndTransaction()
1049 ELSE
1050 .RollbackTransaction()
1051 ENDIF
1052 ENDIF
1053
1054 IF NOT llRetVal
1055 .RevertCCInvoice()
1056 ENDIF
1057
1058 *---TR 1064618 02/22/2013 YE
1059 .oLog.LogResult(llRetVal, lcHeading)
1060 *===TR 1064618 02/22/2013 YE
1061
1062 ENDWITH
1063
1064 SELECT (lnSelect)
1065 RETURN llRetVal
1066 ENDPROC
1067
1068*-------------------------------------------------------------------
1069
1070 PROCEDURE UpdateInvoices
1071
1072 *---TR 1064618 02/22/2013 YE === New variable lcHeading
1073 LOCAL llRetVal, lnSelect, lcHeading
1074
1075 llRetVal = .T.
1076 lnSelect = SELECT()
1077
1078 WITH This
1079
1080 *---TR 1064618 02/22/2013 YE
1081 lcHeading = " Updating Invoices "
1082 .oLog.LogMajorStage(lcHeading)
1083 *===TR 1064618 02/22/2013 YE
1084
1085 * factor
1086 .LogEntry("Checking Factor.",10)
1087 llRetVal = llRetVal AND .CheckFactorInvoice()
1088
1089 * validating weight
1090 .LogEntry("Validating Weight.",20)
1091 llRetVal = llRetVal AND .ValidateWeight('tcCartons')
1092
1093 * validate mismatches
1094 .LogEntry("Validating Mismatches",30)
1095 llRetVal = llRetVal AND .ValidateMismatches()
1096
1097 * stamp average weighted cost
1098 .LogEntry("Stamp Average Weighted Cost.",40)
1099 llRetVal = llRetVal AND .StampAvgWeightedCost()
1100
1101 * stamp sales period
1102 .LogEntry("Stamp Sales Period.",50)
1103 llRetVal = llRetVal AND .StampSalesPeriod()
1104
1105 * location organization
1106 .LogEntry("Populate location organization.",60)
1107 llRetVal = llRetVal AND .PopulateLocationOrg()
1108
1109 * resolve freight
1110 .LogEntry("Resolving Freight.",70)
1111 llRetVal = llRetVal AND .ResolveFreight()
1112
1113 * process credit card
1114 *--- TR 1071221 21-Nov-2014 BNarayanan commented below lines ---
1115 *.LogEntry("Credit Card Processing.",80)
1116 *llRetVal = llRetVal AND .ProcessCCard()
1117 *=== TR 1071221 21-Nov-2014 BNarayanan ===
1118
1119 *---TR 1064618 02/22/2013 YE
1120 .oLog.LogResult(llRetVal, lcHeading)
1121 *===TR 1064618 02/22/2013 YE
1122
1123 ENDWITH
1124
1125 SELECT (lnSelect)
1126 RETURN llRetVal
1127 ENDPROC
1128
1129*-------------------------------------------------------------------
1130
1131 PROCEDURE CheckFactorInvoice
1132
1133 LOCAL llRetVal, lnSelect, lcSQL, lcTempCursor, lcFactor, lcAppv_num, lcFact_Status
1134 llRetVal = .t.
1135 lnSelect = SELECT()
1136 lcTempCursor = GetUniqueFileName()
1137
1138 WITH This
1139
1140 TEXT TO lcSQL TEXTMERGE NOSHOW PRETEXT 3
1141 SELECT ord_num,fkey, COUNT(DISTINCT factor)
1142 FROM <<.cPickDetailTmp>>
1143 WHERE factor_ok = 'Y'
1144 AND factor <> 'LC'
1145 GROUP BY ord_num,fkey
1146 HAVING COUNT(DISTINCT factor) > 1
1147 UNION ALL
1148 SELECT ord_num,fkey, COUNT(DISTINCT appv_num+factor+fact_status)
1149 FROM <<.cPickDetailTmp>>
1150 WHERE factor_ok = 'Y' AND fact_status = 'A'
1151 AND factor = 'LC'
1152 GROUP BY ord_num,fkey
1153 HAVING COUNT(DISTINCT appv_num+factor+fact_status) > 1
1154 ENDTEXT
1155
1156 llRetVal = llRetVal AND v_sqlexec(lcSQL,'tcFactor')
1157
1158 IF llRetVal AND USED('tcFactor')
1159 SELECT tcFactor
1160 SCAN
1161 * remove order from processing
1162 llRetVal = llRetVal AND V_sqlexec("DELETE FROM " + .cPickDetailTmp + " WHERE fkey = " + SQLFormatNum(tcFactor.fkey))
1163 llRetVal = llRetVal AND V_sqlexec("DELETE FROM " + .cPickHeaderTmp + " WHERE pkey = " + SQLFormatNum(tcFactor.fkey))
1164
1165 .LogEntry("Order: " + TRANSFORM(tcFactor.ord_num)+ " - Invoice detail lines must have the same factor.")
1166 ENDSCAN
1167 USE IN SELECT('tcFactor')
1168 ENDIF
1169
1170 ENDWITH
1171
1172 SELECT (lnSelect)
1173 RETURN llRetVal
1174 ENDPROC
1175
1176*-------------------------------------------------------------------
1177
1178 PROCEDURE StampAvgWeightedCost
1179
1180 LOCAL llRetVal, lnSelect, lcSQL, lcFormula
1181 llRetVal = .t.
1182 lnSelect = SELECT()
1183 lcFormula = vl_frmul("SFC_AVE_WGT_COST", "Classfrmul")
1184 lcFormula = CHRTRAN(lcFormula, "{}", "")
1185
1186 *--- TR 1073552/1074265 31-10-2013 VKK commented below and moved above issue found when testing 1074265
1187 *--- TR 1056306 09-09-2011 RKI ---*
1188 tnDiv_Rate = 0
1189 lcformula=STRTRAN(lcformula,'div_rate','tndiv_rate')
1190 *=== TR 1056306 09-09-2011 RKI ===*
1191 *=== TR 1073552/1074265 31-10-2013 VKK commented below and moved above issue found when testing 1074265
1192 WITH This
1193
1194
1195 *--- TR 1056306 09-27-2011 RKI ---*
1196*!* TEXT TO lcSQL TEXTMERGE NOSHOW
1197*!* SELECT *
1198*!* FROM <<.cPickDetailTmp>>
1199*!* WHERE line_status = 'I'
1200*!* ENDTEXT
1201
1202 TEXT TO lcSQL TEXTMERGE NOSHOW
1203 SELECT D.*,H.Div_Rate
1204 FROM <<.cPickDetailTmp>> D
1205 JOIN <<.cPickHeaderTmp>> H
1206 ON h.pkey = D.fkey
1207 WHERE D.line_status = 'I'
1208 ENDTEXT
1209 *=== TR 1056306 09-27-2011 RKI ===*
1210
1211 llRetVal = v_sqlexec(lcSQL,'tcDetail')
1212
1213 SELECT tcDetail
1214 SCAN
1215
1216 *--- TR 1073552/1074265 31-10-2013 VKK commented below and moved above issue found when testing 1074265
1217*!* *--- TR 1056306 09-09-2011 RKI ---*
1218 tnDiv_Rate = Div_Rate
1219*!* lcformula=STRTRAN(lcformula,'div_rate','tndiv_rate')
1220*!* *=== TR 1056306 09-09-2011 RKI ===*
1221 *=== TR 1073552/1074265 31-10-2013 VKK commented below and moved above issue found when testing 1074265
1222
1223 lnAvgWgtCost = ROUND(EVAL(lcFormula), 5) && evaluate the formula
1224
1225 TEXT TO lcSQL TEXTMERGE NOSHOW
1226 UPDATE <<.cPickDetailTmp>>
1227 SET AvgWgtCost = <<lnAvgWgtCost>>
1228 WHERE pkey = <<tcDetail.pkey>>
1229 ENDTEXT
1230
1231 llRetVal = llRetVal AND v_SqlExec(lcSQL)
1232 ENDSCAN
1233 USE
1234
1235 ENDWITH
1236
1237 SELECT (lnSelect)
1238 RETURN llRetVal
1239 ENDPROC
1240
1241*-------------------------------------------------------------------
1242
1243 PROCEDURE StampSalesPeriod
1244
1245 LOCAL llRetVal, lnSelect, lcSQL, lcPeriod
1246 llRetVal = .t.
1247 lnSelect = SELECT()
1248
1249 WITH This
1250
1251 *!* TR 1058006 second join checking for blank division.
1252
1253 TEXT TO lcSQL TEXTMERGE NOSHOW
1254 UPDATE h
1255 SET h.ar_period = COALESCE(dr1.open_period,dr2.open_period,h.ar_period)
1256 ,h.prd_year = COALESCE(dr1.prd_year,dr2.prd_year,h.prd_year)
1257 ,h.prd_month = COALESCE(dr1.prd_month,dr2.prd_month,h.prd_month)
1258 FROM <<.cPickHeaderTmp>> h
1259 LEFT JOIN zzxperdr dr1
1260 ON dr1.division = h.division
1261 AND dr1.type = 'SALES'
1262 AND dr1.period_opn = 'Y'
1263 LEFT JOIN zzxperdr dr2
1264 ON dr2.division = ''
1265 AND dr2.type = 'SALES'
1266 AND dr2.period_opn = 'Y'
1267 ENDTEXT
1268
1269 llRetVal = v_sqlexec(lcSQL)
1270
1271 ENDWITH
1272
1273 SELECT (lnSelect)
1274 RETURN llRetVal
1275 ENDPROC
1276
1277*-------------------------------------------------------------------
1278
1279 PROCEDURE CreditCheck
1280
1281 *---TR 1064618 02/22/2013 YE === New variable lcHeading
1282 LOCAL llRetVal,lnSelect,lcCustomerTmp, lcHeading
1283
1284 llRetVal = .T.
1285 lnSelect = SELECT()
1286 lcCustomerTmp = "#" + GetUniquefilename()
1287
1288 WITH THIS
1289
1290 *---TR 1064618 02/22/2013 YE
1291 *.LogEntry("Credit Checking - Setup.")
1292 lcHeading = "Credit Checking "
1293 .oLog.LogMajorStage(lcHeading)
1294 *===TR 1064618 02/22/2013 YE
1295
1296 * Update DoCreditCheck
1297 llRetVal = llRetVal AND v_sqlexec(;
1298 " UPDATE h " + ;
1299 " SET DoCredChk = c.DoCredChk " + ;
1300 " FROM " + .cPickHeaderTmp + " h " + ;
1301 " JOIN zzhcntrd c ON h.division = c.division and c.process = 'Invoice' ")
1302
1303 * See if any of NonFactDet
1304 llRetVal = llRetVal AND v_sqlexec(;
1305 " UPDATE h " + ;
1306 " SET NonFactDet = 'Y' " + ;
1307 " FROM " + .cPickHeaderTmp + " h " + ;
1308 " JOIN (" + ;
1309 " SELECT DISTINCT fkey " + ;
1310 " FROM " + .cPickDetailTmp + " d " + ;
1311 " LEFT OUTER JOIN zzwfactr f ON d.factor = f.factor " + ;
1312 " WHERE d.Factor_Ok <> 'Y' " + ;
1313 " OR (f.pkey IS NOT NULL AND f.credit_ok = 'Y' )) d on h.pkey = d.fkey ")
1314
1315 * Flag manually approved orders. By default it is Y. If we find any details <> Y then , then make it N
1316 llRetVal = llRetVal AND v_sqlexec(;
1317 " UPDATE h " + ;
1318 " SET ManApp = 'N' " + ;
1319 " FROM " + .cPickHeaderTmp + " h " + ;
1320 " JOIN " + .cPickDetailTmp + " d ON h.pkey = d.fkey and d.man_app <> 'Y'")
1321
1322 * Get Customers to credit check...
1323 llRetVal = llRetVal AND v_sqlexec(;
1324 "SELECT DISTINCT division,ar_customer" + ;
1325 " ,0 as hcust_pkey,'N' as ByPass,' ' as errs_msg " + ;
1326 " ,CAST(0 as NUMERIC(15,2)) as limit " + ;
1327 " ,'C' as rate_fld,0 as period,0 as Term_Days_Limit " + ;
1328 " ,CAST(0 as Numeric(7,2)) as Allow_Perc,CAST(0 as Numeric(15,2)) as Allow_Amt" + ;
1329 " ,CAST(0 as Numeric(7,2)) as Perc_Down " + ;
1330 " ,CAST(0 as float) as open_amt " + ;
1331 " ,CAST(0 as float) as bulk_amt " + ;
1332 " ,CAST(0 as float) as pick_amt " + ;
1333 " ,CAST(0 as float) as inv_amt " + ;
1334 " ,CAST(0 as float) as man_doc_amt " + ;
1335 " ,CAST(0 as float) as cmem_amt " + ;
1336 " ,CAST(0 as float) as ar_current " + ;
1337 " ,CAST(0 as float) as ar_overdue " + ;
1338 " ,CAST(0 as float) as ar_future " + ;
1339 " ,CAST(0 as float) as ar_pastallow " + ;
1340 " ,CAST(0 as float) as ar_balance " + ;
1341 " ,CAST(0 as float) as new_pick_amt " + ;
1342 " ,CAST(0 as float) as min_ar_amt " + ;
1343 " ,CAST(0 as float) as total_amt " + ;
1344 " INTO " + lcCustomerTmp + ;
1345 " FROM " + .cPickHeaderTmp + " WHERE DoCredChk = 'Y' AND NonFactDet = 'Y' AND manApp = 'N' ")
1346
1347 * Customer Credit Check.
1348 llRetVal = llRetVal And v_sqlexec(;
1349 "UPDATE c SET " + ;
1350 " hcust_pkey = h.pkey " + ;
1351 " FROM " + lcCustomerTmp + " c " + ;
1352 " JOIN zzhCustR h ON c.ar_customer = h.customer AND h.division = ''")
1353 llRetVal = llRetVal And v_sqlexec(;
1354 "UPDATE c SET " + ;
1355 " hcust_pkey = h.pkey " + ;
1356 " ,rate_fld = 'D' " + ;
1357 " FROM " + lcCustomerTmp + " c " + ;
1358 " JOIN zzhCustR h ON c.ar_customer = h.customer AND h.division = c.division " + ;
1359 " WHERE c.hcust_pkey = 0 ")
1360
1361 IF vl_Compr('','Org_Ok')='Y'
1362 llRetVal = llRetVal AND v_sqlexec(;
1363 "UPDATE " + lcCustomerTmp + " SET rate_fld = 'O'")
1364 ENDIF
1365
1366 .LogEntry("Credit Checking - Customer Credit Check.")
1367
1368 * New account failure
1369 llRetVal = llRetVal AND v_sqlexec(;
1370 "UPDATE "+lcCustomerTmp +" set errs_msg = 'New Account (No Credit Record)' where hcust_pkey = 0")
1371
1372 * Check expired record
1373 * Set bypass flag
1374 * Check LC
1375 * Set limit
1376 llRetVal = llRetVal and v_sqlexec(;
1377 "UPDATE c SET " + ;
1378 " bypass = h.bypass " + ;
1379 " ,period = h.period " + ;
1380 " ,Term_Days_Limit = h.Term_Days_Limit " + ;
1381 " ,Allow_Perc = h.Allow_Perc " + ;
1382 " ,Allow_Amt = h.Allow_Amt " + ;
1383 " ,Perc_Down = h.Perc_Down " + ;
1384 " ,errs_msg = CASE WHEN h.Expire_Date < " + SQLFormatTS(DATE()) + " THEN 'Credit Expired' " + ;
1385 " WHEN h.bypass = 'Y' THEN '' " + ;
1386 " WHEN h.Lc_Flg = 'Y' AND (h.Lc_Exp_Dt > " + SQLFormatTS({}) + " AND h.Lc_Exp_Dt < " + SQLFormatTS(DATE()) + ;
1387 " ) THEN 'Letter of Credit Expired' ELSE '' END " + ;
1388 " ,limit = h.limit " + ;
1389 " * (CASE WHEN h.Perc_Up > 0 THEN (100 + h.Perc_Up)/100 ELSE 1 END) " + ;
1390 " + (CASE WHEN h.Lc_Flg = 'Y' THEN h.Lc_Amt ELSE 0 END) " + ;
1391 " FROM " + lcCustomerTmp + " c " + ;
1392 " JOIN zzhCustR h ON c.hcust_pkey = h.pkey WHERE errs_msg = '' ")
1393
1394 .LogEntry("Credit Checking - Retrieving Credit Amounts.")
1395 llRetVal = llRetVal AND .PopulateARInfo(lcCustomerTmp)
1396
1397 .LogEntry("Credit Checking - Order Credit Checking.")
1398 * Past allowed term due days. If any order for the division/customer, then fail the division/customer
1399 llRetVal = llRetVal AND v_sqlexec(;
1400 "UPDATE c SET errs_msg = 'Past Allowed Terms Due Days'" + ;
1401 " FROM " + lcCustomerTmp + " c " + ;
1402 " WHERE c.errs_msg = '' AND c.Term_Days_Limit > 0 AND EXISTS(" + ;
1403 " SELECT h.pkey FROM " + .cPickHeaderTmp + " h " + ;
1404 " LEFT JOIN zzxtermr t ON h.terms = t.terms " + ;
1405 " WHERE COALESCE(t.extra_days,0) > c.Term_Days_Limit) ")
1406
1407 * Calc Total Amt and min ar amt
1408 llRetVal = llRetVal AND v_sqlexec(;
1409 "UPDATE " + lcCustomerTmp + ;
1410 " SET min_ar_amt = CASE WHEN ar_balance > ar_pastallow " + ;
1411 " THEN ar_pastallow ELSE ar_balance END " + ;
1412 " ,total_amt = open_amt + bulk_amt + pick_amt + inv_amt + man_doc_amt " + ;
1413 " + cmem_amt + ar_balance + new_pick_amt ")
1414
1415 * Over Credit Limit (sum up records by customer credit record since they may be blank divisions.
1416 llRetVal = llRetVal AND v_sqlexec(;
1417 "UPDATE c " + ;
1418 " SET errs_msg = " + ;
1419 " CASE WHEN s.total_amt > c.Limit THEN 'Over Credit Limit' " + ;
1420 " WHEN c.Perc_Down > 0 AND (s.total_amt > (limit * (100 - Perc_Down)/100)) " + ;
1421 " THEN 'Within Credit Limit Warning' ELSE '' END " + ;
1422 " FROM " + lcCustomerTmp + " c " + ;
1423 " JOIN (SELECT hcust_pkey,SUM(total_amt) as total_amt FROM " + lcCustomerTmp + " GROUP BY hcust_pkey ) s ON c.hcust_pkey = s.hcust_pkey " + ;
1424 " WHERE errs_msg = '' ")
1425
1426 * past due
1427 llRetVal = llRetVal AND v_sqlexec(;
1428 "UPDATE c SET errs_msg = " + ;
1429 " CASE WHEN (c.min_ar_amt*100/c.ar_balance ) > c.Allow_Perc " + ;
1430 " THEN 'Past Due (by pct)' " + ;
1431 " WHEN c.min_ar_amt > c.allow_amt " + ;
1432 " THEN 'Past Due (by amt)' ELSE '' END" + ;
1433 " FROM " + lcCustomerTmp + " c " + ;
1434 " WHERE c.errs_msg = '' " + ;
1435 " AND c.ar_balance > 0 AND c.period > 0 AND c.allow_perc > 0 AND c.allow_amt > 0 ")
1436
1437 llRetVal = llRetVal AND v_sqlexec(;
1438 "SELECT * FROM " + lcCustomerTmp + " WHERE errs_msg > '' ","tcErrors")
1439
1440 IF llRetVal
1441 IF RECCOUNT("tcErrors") = 0
1442 .LogEntry("Credit Checking Passed for All Customers")
1443 ELSE
1444 SELECT tcErrors
1445 .LogEntry("==========================================")
1446 .LogEntry("Credit Failed for the following Customers:")
1447 .LogEntry("==========================================")
1448
1449 SCAN
1450 .LogEntry(" Division: " + ALLTRIM(division) + ;
1451 " / AR Customer: " + ALLTRIM(ar_customer) +" / Error: " + ALLTRIM(errs_msg))
1452
1453 llRetVal = llRetVal AND ;
1454 v_sqlexec("DELETE FROM " + .cPickHeaderTmp + " WHERE division = " + SQLFormatChar(division) + " AND ar_customer = " + SQLFormatChar(ar_customer))
1455
1456 ENDSCAN
1457 ENDIF
1458 ENDIF
1459
1460 *---TR 1064618 02/22/2013 YE
1461 .oLog.LogResult(llRetVal, lcHeading)
1462 *===TR 1064618 02/22/2013 YE
1463
1464 ENDWITH
1465
1466 SELECT (lnSelect)
1467 RETURN llRetVal
1468
1469 ENDPROC
1470
1471*--------------------------------------------------------------
1472
1473 PROCEDURE PopulateARInfo
1474 LPARAMETERS tcCustomerTmp
1475
1476 * Populate the different A/R Amount Bros into tcCustomer temp.
1477 LOCAL llRetVal,lnSelect,lcAllCustTmp , lcFactAlias
1478 llRetVal = .T.
1479 lnSelect = SELECT()
1480 lcAllCustTmp = "#" + GetUniquefilename()
1481 lcFactAlias = IIF((goEnv.sv("FACTOR_APPV_BY_AMOUNT","N") = 'Y'),"d","h")
1482
1483 WITH THIS
1484 * Build Temp Table of all customers for the A/Rs being processed.
1485 llRetVal = llRetVal AND v_sqlexec(;
1486 "SELECT * INTO " + lcAllCustTmp + " FROM ( " + ;
1487 "SELECT c.ar_customer as customer,c.division,c.ar_customer,rate_fld,period " + ;
1488 " FROM " + tcCustomerTmp + " c WHERE c.bypass <> 'Y' AND c.errs_msg = ''" + ;
1489 " UNION " + ;
1490 "SELECT r.customer,c.division,c.ar_customer,rate_fld,period " + ;
1491 " FROM " + tcCustomerTmp + " c " + ;
1492 " JOIN zzrCustR r ON c.ar_customer = r.ar_customer WHERE c.bypass <> 'Y' AND c.errs_msg = '') h ")
1493
1494 llRetVal = llRetVal AND v_sqlexec(;
1495 "UPDATE c " + ;
1496 " set open_amt = x.open_amt " + ;
1497 " ,pick_amt = x.pick_amt " + ;
1498 " ,bulk_amt = x.bulk_amt " + ;
1499 " FROM " + tcCustomerTmp + " c " + ;
1500 " JOIN (" + ;
1501 " SELECT ar_customer,division " + ;
1502 " ,SUM(CASE WHEN line_status = 'O' AND Conf_type = 'B' THEN (curr_rate*(frgt_amt+insu_amt+misc_amt+tax_amt-disc_amt)) + zdetailnetext ELSE 0 END ) as Bulk_Amt " + ;
1503 " ,SUM(CASE WHEN line_status = 'O' AND Conf_Type = 'A' THEN (curr_rate*(frgt_amt+insu_amt+misc_amt+tax_amt-disc_amt)) + zdetailnetext ELSE 0 END ) as Open_Amt " + ;
1504 " ,SUM(CASE WHEN line_status = 'P' THEN (curr_rate*(frgt_amt+insu_amt+misc_amt+tax_amt-disc_amt)) + zdetailnetext ELSE 0 END ) as Pick_Amt " + ;
1505 " FROM ( " + ;
1506 " SELECT customer,ar_customer,division,line_status,pkey,conf_type,AVG(disc_amt) as disc_amt, AVG(Frgt_Amt) Frgt_Amt, AVG(Insu_Amt) Insu_amt, AVG(Misc_Amt) Misc_Amt, AVG(Tax_Amt) Tax_amt, " + ;
1507 " AVG(curr_rate) curr_rate, " + ;
1508 " SUM( curr_rate * ((((price*total_qty)- (((price * " + ;
1509 " ((disc_perc) / 100)) * total_qty)))/case " + ;
1510 " when total_qty = 0 then 1 else total_qty end)*total_qty)) as zdetailnetext " + ;
1511 " FROM ( " + ;
1512 " SELECT c.customer,c.ar_customer,h.division,h.Ord_Num,h.pick_num, d.Price, d.Discount, d.Total_Qty, " + ;
1513 " h.Disc_Amt, h.Frgt_Amt, h.Insu_Amt, h.Misc_Amt, h.Tax_Amt, " + ;
1514 " d.Line_Status, d.Line_Seq, h.pKey, t.debit_credit, h.conf_type " + ;
1515 " ,CASE WHEN c.rate_fld = 'D' THEN h.div_rate WHEN c.rate_fld = 'O' THEN h.co_rate ELSE h.org_rate END as curr_rate " + ;
1516 " ,COALESCE(disc.disc_perc, 0) as disc_perc " + ;
1517 " from zzoordrh h " + ;
1518 " JOIN zzoordrd d on h.pkey = d.fkey " + ;
1519 " JOIN zzrtyper t ON t.doc_type = 'INV' " + ;
1520 " JOIN "+lcAllCustTmp+" c ON h.customer = c.customer " + ;
1521 " LEFT JOIN zzwfactr f ON "+lcFactAlias+".factor = f.factor AND f.credit_ok = 'Y' " + ;
1522 " LEFT JOIN zzxdiscr disc ON d.discount = disc.discount " + ;
1523 " WHERE h.ORD_STATUS <> 'C' " + ;
1524 " and h.CONF_TYPE = 'A' " + ;
1525 " and ("+lcFactAlias+".Factor_Ok <> 'Y' OR f.factor IS NOT NULL) " + ;
1526 " and h.pkey NOT IN (SELECT pkey FROM " + .cPickHdrKeyTmp + ") ) h " + ;
1527 " GROUP BY customer,ar_customer,division,line_status,pkey,conf_type " + ;
1528 ") h GROUP BY ar_customer,division " + ;
1529 " ) x ON c.ar_customer = x.ar_customer AND c.division = x.division ")
1530
1531 llRetVal = llRetVal AND v_sqlexec(;
1532 "UPDATE c " + ;
1533 " set inv_amt = x.inv_amt " + ;
1534 " FROM " + tcCustomerTmp + " c " + ;
1535 " JOIN (" + ;
1536 " SELECT ar_customer,division " + ;
1537 " ,SUM(CASE WHEN line_status = 'I' THEN (curr_rate*(frgt_amt+insu_amt+misc_amt+tax_amt-disc_amt)) + zdetailnetext ELSE 0 END ) as Inv_Amt " + ;
1538 " FROM ( " + ;
1539 " SELECT customer,ar_customer,division,line_status,pkey,AVG(disc_amt) as disc_amt, AVG(Frgt_Amt) Frgt_Amt, AVG(Insu_Amt) Insu_amt, AVG(Misc_Amt) Misc_Amt, AVG(Tax_Amt) Tax_amt, " + ;
1540 " AVG(curr_rate) curr_rate, " + ;
1541 " SUM( curr_rate * ((((price*total_qty)- (((price * " + ;
1542 " ((disc_perc) / 100)) * total_qty)))/case " + ;
1543 " when total_qty = 0 then 1 else total_qty end)*total_qty)) as zdetailnetext " + ;
1544 " FROM ( " + ;
1545 " SELECT c.customer,c.ar_customer,h.division,h.Ord_Num,h.pick_num, d.Price, d.Discount, d.Total_Qty, " + ;
1546 " h.Disc_Amt, h.Frgt_Amt, h.Insu_Amt, h.Misc_Amt, h.Tax_Amt, " + ;
1547 " d.Line_Status, d.Line_Seq, h.pKey, t.debit_credit, " + ;
1548 " CASE WHEN c.rate_fld = 'D' THEN h.div_rate WHEN c.rate_fld = 'O' THEN h.co_rate ELSE h.org_rate END as curr_rate " + ;
1549 " ,COALESCE(disc.disc_perc, 0) as disc_perc " + ;
1550 " from zzoshprh h " + ;
1551 " JOIN zzoshprd d on h.pkey = d.fkey " + ;
1552 " JOIN zzrtyper t ON t.doc_type = 'INV' " + ;
1553 " JOIN "+lcAllCustTmp+" c ON h.customer = c.customer " + ;
1554 " LEFT JOIN zzwfactr f ON "+lcFactAlias+".factor = f.factor AND f.credit_ok = 'Y' " + ;
1555 " LEFT JOIN zzxdiscr disc ON d.discount = disc.discount " + ;
1556 " WHERE h.Ar_Post <> 'P' " + ;
1557 " and h.CONF_TYPE = 'A' " + ;
1558 " and ("+lcFactAlias+".Factor_Ok <> 'Y' OR f.factor IS NOT NULL) ) h " + ;
1559 " GROUP BY customer,ar_customer,division,line_status,pkey " + ;
1560 " ) h " + ;
1561 " GROUP BY ar_customer,division " + ;
1562 " ) x ON c.ar_customer = x.ar_customer AND c.division = x.division ")
1563
1564 llRetVal = llRetVal AND v_sqlexec(;
1565 "UPDATE c " + ;
1566 " set man_doc_amt = x.man_doc_amt " + ;
1567 " FROM " + tcCustomerTmp + " c " + ;
1568 " JOIN (" + ;
1569 " SELECT ar_customer,division,SUM( " + ;
1570 " (CASE WHEN rate_fld = 'D' THEN div_rate WHEN rate_fld = 'O' THEN co_rate ELSE org_rate END ) * " + ;
1571 " ((Frgt_Amt + Tax_Amt + Misc_Amt + Line_Amt- Disc_Amt) * (CASE WHEN debit_credit = 'CR' THEN -1 ELSE 1 END))) as man_doc_amt " + ;
1572 " FROM ( " + ;
1573 " SELECT c.ar_customer,h.Doc_Num, h.division,h.pKey, h.Disc_Amt, h.Frgt_Amt, h.Tax_Amt, h.Misc_Amt,h.div_rate, h.co_rate,h.org_rate, " + ;
1574 " SUM(d.Line_Amt) AS Line_Amt, t.Debit_Credit, c.rate_fld " + ;
1575 " FROM zzrdocwh h " + ;
1576 " JOIN zzrdocwd d ON h.pkey = d.fkey " + ;
1577 " JOIN zzrtyper t ON h.doc_type = t.doc_type " + ;
1578 " JOIN "+lcAllCustTmp+" c ON h.customer = c.customer " + ;
1579 " LEFT OUTER JOIN zzwfactr f ON h.factor = f.factor and f.credit_ok = 'Y' " + ;
1580 " WHERE h.Factor_Ok <> 'Y' OR f.factor is not null " + ;
1581 " GROUP BY c.ar_customer,h.Doc_Num, h.division,h.pKey, h.Disc_Amt, h.Frgt_Amt, h.Tax_Amt, h.Misc_Amt,h.div_rate, h.co_rate,h.org_rate, t.Debit_Credit, c.rate_fld) h " + ;
1582 " GROUP BY ar_customer,division " + ;
1583 " ) x ON c.ar_customer = x.ar_customer AND c.division = x.division ")
1584
1585 llRetVal = llRetVal AND v_sqlexec(;
1586 "UPDATE c " + ;
1587 " set cmem_amt = x.cmem_amt " + ;
1588 " FROM " + tcCustomerTmp + " c " + ;
1589 " JOIN (" + ;
1590 " SELECT ar_customer,division,SUM((curr_rate*(frgt_amt+insu_amt+misc_amt+tax_amt-disc_amt)) + zdetailnetext) as cmem_amt " + ;
1591 " FROM( " + ;
1592 " SELECT ar_customer,division,pkey,AVG(disc_amt) as disc_amt, AVG(Frgt_Amt) Frgt_Amt, AVG(Insu_Amt) Insu_amt, AVG(Misc_Amt) Misc_Amt, AVG(Tax_Amt) Tax_amt, " + ;
1593 " AVG(curr_rate) curr_rate, " + ;
1594 " SUM( curr_rate * ((((price*total_qty)- (((price * " + ;
1595 " ((disc_perc) / 100)) * total_qty)))/case " + ;
1596 " when total_qty = 0 then 1 else total_qty end)*total_qty)) as zdetailnetext " + ;
1597 " FROM ( " + ;
1598 " SELECT c.ar_customer, h.division, d.Price, d.Discount, d.Total_Qty, " + ;
1599 " h.Disc_Amt, h.Frgt_Amt, h.Insu_Amt, h.Misc_Amt, h.Tax_Amt, " + ;
1600 " h.pKey, t.debit_credit ,h.div_rate, h.co_rate,h.org_rate,COALESCE(disc.disc_perc,0) as disc_perc " + ;
1601 " ,CASE WHEN c.rate_fld = 'D' THEN h.div_rate WHEN c.rate_fld = 'O' THEN h.co_rate ELSE h.org_rate END as curr_rate " + ;
1602 " FROM zznretnh h " + ;
1603 " JOIN zznretnd d ON h.pkey = d.fkey " + ;
1604 " JOIN zzrtyper t ON t.doc_type = 'CRD' " + ;
1605 " JOIN "+lcAllCustTmp+" c ON h.customer = c.customer " + ;
1606 " LEFT OUTER JOIN zzwfactr f ON d.factor = f.factor and f.credit_ok = 'Y' " + ;
1607 " LEFT OUTER JOIN zzxdiscr disc ON d.discount = disc.discount " + ;
1608 " where h.Doc_type = 'CM' " + ;
1609 " AND h.AR_post <> 'P' " + ;
1610 " and (d.Factor_Ok <> 'Y' OR f.factor IS NOT NULL) " + ;
1611 " ) h GROUP BY ar_customer,division,pkey " + ;
1612 " )h GROUP BY ar_customer,division " + ;
1613 " ) x ON c.ar_customer = x.ar_customer AND c.division = x.division ")
1614
1615 llRetVal = llRetVal AND v_sqlexec(;
1616 "UPDATE c " + ;
1617 " set ar_current = x.ar_current " + ;
1618 " ,ar_overdue = x.ar_overdue " + ;
1619 " ,ar_future = x.ar_future" + ;
1620 " ,ar_pastallow = x.ar_pastallow " + ;
1621 " ,ar_balance = x.ar_balance " + ;
1622 " FROM " + tcCustomerTmp + " c " + ;
1623 " JOIN (" + ;
1624 " SELECT ar_customer,division,SUM(ar_current) as ar_current, SUM(ar_overdue) as ar_overdue, " + ;
1625 " SUM(ar_future) as ar_future, SUM(ar_pastallow) as ar_pastallow " + ;
1626 " ,SUM(ar_current+ar_overdue+ar_future) as ar_balance " + ;
1627 " FROM ( " + ;
1628 " SELECT ar_customer,division,DEBIT_CREDIT,DUE_DATE,Doc_amt " + ;
1629 " ,CASE WHEN due_date = "+SQLFormatTS(DATE())+" THEN doc_amt ELSE 0 END as ar_current " + ;
1630 " ,CASE WHEN due_date < "+SQLFormatTS(DATE())+" THEN doc_amt ELSE 0 END as ar_overdue " + ;
1631 " ,CASE WHEN due_date > "+SQLFormatTS(DATE())+" THEN doc_amt ELSE 0 END as ar_future " + ;
1632 " ,CASE WHEN due_date < DATEADD(dd,-1*period,"+SQLFormatTS(DATE())+") THEN doc_amt ELSE 0 END as ar_pastallow " + ;
1633 " FROM ( " + ;
1634 " Select ar_customer,d.division,DEBIT_CREDIT,DUE_DATE," + ;
1635 " (CASE WHEN debit_credit = 'CR' THEN -1 ELSE 1 END) * " + ;
1636 " (doc_amt * (CASE WHEN rate_fld = 'D' THEN div_rate WHEN rate_fld = 'C' THEN co_rate ELSE org_rate END)) AS Doc_Amt " + ;
1637 " ,c.period " + ;
1638 " From zzropend d " + ;
1639 " JOIN "+tcCustomerTmp+" c ON d.customer = c.ar_customer " + ;
1640 " where factor_ok <> 'Y' or factor in (select factor from zzwfactr where factor = factor) " + ;
1641 " ) h " + ;
1642 " ) h GROUP BY ar_customer,division " + ;
1643 " ) x ON c.ar_customer = x.ar_customer AND c.division = x.division ")
1644
1645 llRetVal = llRetVal AND v_sqlexec(;
1646 "UPDATE c " + ;
1647 " set new_pick_amt = x.new_pick_amt " + ;
1648 " FROM " + tcCustomerTmp + " c " + ;
1649 " JOIN (" + ;
1650 " SELECT ar_customer,division " + ;
1651 " ,SUM((curr_rate*(frgt_amt+insu_amt+misc_amt+tax_amt-disc_amt)) + zdetailnetext) as new_pick_amt " + ;
1652 " FROM ( " + ;
1653 " SELECT customer,ar_customer,division,line_status,pkey,AVG(disc_amt) as disc_amt, AVG(Frgt_Amt) Frgt_Amt, AVG(Insu_Amt) Insu_amt, AVG(Misc_Amt) Misc_Amt, AVG(Tax_Amt) Tax_amt, " + ;
1654 " AVG(curr_rate) curr_rate, " + ;
1655 " SUM( curr_rate * ((((price*total_qty)- (((price * " + ;
1656 " ((disc_perc) / 100)) * total_qty)))/case " + ;
1657 " when total_qty = 0 then 1 else total_qty end)*total_qty)) as zdetailnetext " + ;
1658 " FROM ( " + ;
1659 " SELECT c.customer,c.ar_customer,h.division,h.Ord_Num,h.pick_num, d.Price, d.Discount, d.Total_Qty, " + ;
1660 " h.Disc_Amt, h.Frgt_Amt, h.Insu_Amt, h.Misc_Amt, h.Tax_Amt, " + ;
1661 " d.Line_Status, d.Line_Seq, h.pKey, t.debit_credit, " + ;
1662 " CASE WHEN c.rate_fld = 'D' THEN h.div_rate WHEN c.rate_fld = 'O' THEN h.co_rate ELSE h.org_rate END as curr_rate " + ;
1663 " ,COALESCE(disc.disc_perc, 0) as disc_perc " + ;
1664 " FROM " + .cPickHeaderTmp + " h " + ;
1665 " JOIN " + .cPickDetailTmp + " d on h.pkey = d.fkey " + ;
1666 " JOIN zzrtyper t ON t.doc_type = 'INV' " + ;
1667 " JOIN "+lcAllCustTmp+" c ON h.customer = c.customer " + ;
1668 " LEFT JOIN zzwfactr f ON "+lcFactAlias+".factor = f.factor AND f.credit_ok = 'Y' " + ;
1669 " LEFT JOIN zzxdiscr disc ON d.discount = disc.discount " + ;
1670 " WHERE h.ORD_STATUS <> 'C' " + ;
1671 " and h.CONF_TYPE = 'A' " + ;
1672 " and ("+lcFactAlias+".Factor_Ok <> 'Y' OR f.factor IS NOT NULL) ) h " + ;
1673 " GROUP BY customer,ar_customer,division,line_status,pkey " + ;
1674 ") h GROUP BY ar_customer,division " + ;
1675 " ) x ON c.ar_customer = x.ar_customer AND c.division = x.division ")
1676
1677 * Now that we have collected all of these great amounts. Let's zero out the ones we do not need.
1678 llRetVal = llRetVal AND v_sqlexec(;
1679 "UPDATE c SET " + ;
1680 " open_amt = CASE WHEN chk_open = 'Y' THEN open_amt ELSE 0 END "+ ;
1681 " ,pick_amt = CASE WHEN chk_pick = 'Y' THEN pick_amt ELSE 0 END " + ;
1682 " ,bulk_amt = CASE WHEN chk_bulk = 'Y' THEN bulk_amt ELSE 0 END " + ;
1683 " ,inv_amt = CASE WHEN chk_unpinv = 'Y' THEN inv_amt ELSE 0 END " + ;
1684 " ,man_doc_amt = CASE WHEN chk_unpmd = 'Y' THEN man_doc_amt ELSE 0 END " + ;
1685 " ,cmem_amt = CASE WHEN chk_crmemo = 'Y' THEN cmem_amt ELSE 0 END " + ;
1686 " ,ar_current = CASE WHEN chk_ar = 'Y' THEN ar_current ELSE 0 END " + ;
1687 " ,ar_overdue = CASE WHEN chk_ar = 'Y' THEN ar_overdue ELSE 0 END " + ;
1688 " ,ar_future = CASE WHEN chk_ar = 'Y' THEN ar_future ELSE 0 END " + ;
1689 " ,ar_pastallow = CASE WHEN chk_ar = 'Y' THEN ar_pastallow ELSE 0 END " + ;
1690 " ,ar_balance = CASE WHEN chk_ar = 'Y' THEN ar_balance ELSE 0 END " + ;
1691 " FROM " + tcCustomerTmp + " c " + ;
1692 " JOIN zzhcntrd h ON h.division = c.division and h.process = 'Pick' ")
1693 ENDWITH
1694
1695 SELECT (lnSelect)
1696 RETURN llRetVal
1697
1698 ENDPROC
1699
1700*-------------------------------------------------------------------
1701
1702 PROCEDURE ResolveFreight
1703
1704 LOCAL llRetVal, lnSelect, lcSQL
1705 llRetVal = .t.
1706 lnSelect = SELECT()
1707
1708 WITH This
1709
1710 IF .lResolveFreightAtInvoice
1711
1712 * header update
1713 TEXT TO lcSQL TEXTMERGE NOSHOW
1714 UPDATE h
1715 SET h.frgt_amt = 0
1716 FROM <<.cPickHeaderTmp>> h
1717 JOIN zzoshprh rh
1718 ON rh.ord_num = h.ord_num
1719 JOIN zzxcustr cust
1720 ON cust.customer = h.customer
1721 WHERE cust.use_calc_frgt = 'F'
1722 ENDTEXT
1723
1724 llRetVal = v_sqlexec(lcSQL)
1725
1726 * detail update
1727 TEXT TO lcSQL TEXTMERGE NOSHOW
1728 UPDATE d
1729 SET d.frgt_value = 0
1730 FROM <<.cPickHeaderTmp>> h
1731 JOIN <<.cPickDetailTmp>> d
1732 ON d.fkey = h.pkey
1733 JOIN zzoshprh rh
1734 ON rh.ord_num = h.ord_num
1735 JOIN zzxcustr cust
1736 ON cust.customer = h.customer
1737 WHERE cust.use_calc_frgt = 'F'
1738 AND d.frgt_ok = 'Y'
1739 ENDTEXT
1740
1741 llRetVal = v_sqlexec(lcSQL)
1742 ENDIF
1743
1744 ENDWITH
1745
1746 SELECT (lnSelect)
1747 RETURN llRetVal
1748 ENDPROC
1749
1750*-------------------------------------------------------------------
1751
1752 PROCEDURE PopulateLocationOrg
1753
1754 LOCAL llRetVal, lnSelect, lcSQL
1755 llRetVal = .t.
1756 lnSelect = SELECT()
1757
1758 WITH This
1759
1760 IF .lMultiOrganization
1761 TEXT TO lcSQL TEXTMERGE NOSHOW
1762 UPDATE h
1763 SET h.loc_org = r.org_code
1764 FROM <<.cPickHeaderTmp>> h
1765 JOIN zzxlocar r
1766 ON r.location = h.location
1767 WHERE h.location <> ''
1768 ENDTEXT
1769
1770 llRetVal = v_sqlexec(lcSQL)
1771 ENDIF
1772
1773 ENDWITH
1774
1775 SELECT (lnSelect)
1776 RETURN llRetVal
1777 ENDPROC
1778
1779*-------------------------------------------------------------------
1780
1781 *--- TechRec 1052703 04-Aug-2011 TShenbagavalli ---
1782 PROCEDURE ValidateCartonStage
1783
1784 *---TR 1064618 02/22/2013 YE === New variable lcHeading
1785 LOCAL llRetVal, lnSelect, lcSQL, llInvCartonStageReqd, lcSQLTempTable, lcTempCursor, lcStageMsg, lcHeading
1786
1787 llRetVal = .t.
1788 lnSelect = SELECT()
1789 lcSQLTempTable = GetUniqueFileName()
1790 lcTempCursor = GetUniqueFileName()
1791
1792 WITH This
1793
1794 *---TR 1064618 02/22/2013 YE
1795 lcHeading = " Validating Carton Stage "
1796 .oLog.LogMajorStage(lcHeading)
1797 *===TR 1064618 02/22/2013 YE
1798
1799 llInvCartonStageReqd = (goEnv.sv("INVOICE_CARTON_STAGE_REQD","N") = 'Y')
1800
1801 IF llInvCartonStageReqd
1802
1803 *--- TR 1058108 06-Dec-2011 Partha ---
1804*!* TEXT TO lcSQL TEXTMERGE NOSHOW
1805*!* SELECT c.*
1806*!* INTO <<lcSQLTempTable>>
1807*!* FROM (
1808*!* SELECT tmp.pkey, tmp.ord_num, tmp.pick_num
1809*!* FROM zzoctnph h
1810*!* JOIN zzoordsp s ON s.pkey = h.fkey
1811*!* JOIN <<.cPickHeaderTmp>> tmp
1812*!* ON tmp.ord_num = s.ord_num AND tmp.pick_num = s.pick_num AND s.inv_num = tmp.inv_num
1813*!* WHERE s.carton_cls = 'S'
1814*!* AND h.ctn_stage = ''
1815*!* ) c
1816*!* ENDTEXT
1817
1818 TEXT TO lcSQL TEXTMERGE NOSHOW
1819 SELECT c.*
1820 INTO <<lcSQLTempTable>>
1821 FROM (
1822 SELECT tmp.pkey, tmp.ord_num, tmp.pick_num
1823 FROM zzoctnph h
1824 JOIN zzoordsp s ON s.pkey = h.fkey
1825 JOIN <<.cPickHeaderTmp>> tmp
1826 ON tmp.ord_num = s.ord_num AND tmp.pick_num = s.pick_num AND s.inv_num = tmp.inv_num
1827 JOIN zzywavbh wv WITH (NOLOCK)
1828 ON tmp.wave_num = wv.wave_num
1829 WHERE s.carton_cls = 'S'
1830 AND h.ctn_stage = ''
1831 AND tmp.wave_num >0
1832 AND wv.pick_interface = 'PICKDIRECTOR'
1833 ) c
1834 ENDTEXT
1835 *=== TR 1058108 06-Dec-2011 Partha ===
1836
1837 llRetVal = v_SqlExec(lcSQL)
1838 llRetVal = llRetVal AND v_SQLExec("SELECT distinct pick_num FROM " + lcSQLTempTable, lcTempCursor)
1839
1840 IF llRetVal AND USED(lcTempCursor)
1841
1842 SELECT (lcTempCursor)
1843 SCAN
1844 lcStageMsg = " - This pick has empty carton stage."
1845 .LogEntry("Pick: " + TRANSFORM(pick_num) + lcStageMsg )
1846 ENDSCAN
1847 USE
1848
1849 * delete from header
1850 TEXT TO lcSQL TEXTMERGE NOSHOW PRETEXT 3
1851 DELETE FROM <<.cPickHeaderTmp>>
1852 WHERE pkey IN (SELECT pkey FROM <<lcSQLTempTable>>)
1853 ENDTEXT
1854
1855 llRetVal = llRetVal AND v_SqlExec(lcSQL)
1856
1857 * delete from detail
1858 TEXT TO lcSQL TEXTMERGE NOSHOW PRETEXT 3
1859 DELETE FROM <<.cPickDetailTmp>>
1860 WHERE pick_num IN (SELECT pick_num FROM <<lcSQLTempTable>>)
1861 ENDTEXT
1862
1863 llRetVal = llRetVal AND v_SqlExec(lcSQL)
1864
1865 * delete backend temp table
1866 v_sqlexec('DROP TABLE ' + lcSQLTempTable)
1867
1868 ENDIF
1869 ENDIF
1870
1871 *---TR 1064618 02/22/2013 YE
1872 .oLog.LogResult(llRetVal, lcHeading)
1873 *===TR 1064618 02/22/2013 YE
1874
1875 ENDWITH
1876
1877 SELECT (lnSelect)
1878 RETURN llRetVal
1879 ENDPROC
1880 *=== TechRec 1052703 04-Aug-2011 TShenbagavalli ===
1881
1882*-------------------------------------------------------------------
1883
1884 PROCEDURE ValidateWeight
1885 LPARAMETERS tcCursor
1886
1887 LOCAL llRetVal, lnSelect, lcSQL, lcHeaderTemp
1888 LOCAL llBypassCartonWeight, lcSQLTempTable, lcTempCursor, lcWeightMsg
1889 llRetVal = .t.
1890 lnSelect = SELECT()
1891 lcSQLTempTable = GetUniqueFileName()
1892 lcTempCursor = GetUniqueFileName()
1893
1894 WITH This
1895
1896 llBypassCartonWeight = (goEnv.sv("ALLOW_ZERO_CARTON_WEIGHT","N") = 'Y')
1897
1898 *--- TR 1075285 12/24/13 ATHIRUNAVU Modified zzeoschcr alias form "cr to cr1" as textmerg is wrongly converting to chr(13)
1899 IF NOT llBypassCartonWeight
1900 TEXT TO lcSQL TEXTMERGE NOSHOW
1901 SELECT c.*
1902 INTO <<lcSQLTempTable>>
1903 FROM (
1904 SELECT tmp.pkey, tmp.ord_num, tmp.pick_num
1905 FROM zzoctnph h
1906 JOIN zzoordsp s ON s.pkey = h.fkey
1907 JOIN <<.cPickHeaderTmp>> tmp
1908 ON tmp.ord_num = s.ord_num AND tmp.pick_num = s.pick_num AND s.inv_num = tmp.inv_num
1909 JOIN zzeoshcr cr1
1910 ON cr1.division = tmp.division AND cr1.customer = tmp.customer
1911 AND cr1.use_wgt = 'Y' AND cr1.active_ok = 'Y'
1912 JOIN zzocntrc rc
1913 ON rc.division = tmp.division AND rc.chkcarton = 'Y'
1914 WHERE s.carton_cls = 'S'
1915 AND h.carton_wgt = 0
1916 UNION
1917 SELECT tmp.pkey, tmp.ord_num, tmp.pick_num
1918 FROM zzoctnsh h
1919 JOIN zzoordsp s ON s.pkey = h.fkey
1920 JOIN <<.cPickHeaderTmp>> tmp
1921 ON tmp.ord_num = s.ord_num AND tmp.pick_num = s.pick_num AND s.inv_num = tmp.inv_num
1922 JOIN zzeoshcr cr1
1923 ON cr1.division = tmp.division AND cr1.customer = tmp.customer
1924 AND cr1.use_wgt = 'Y' AND cr1.active_ok = 'Y'
1925 JOIN zzocntrc rc
1926 ON rc.division = tmp.division AND rc.chkcarton = 'Y'
1927 WHERE s.carton_cls = 'S'
1928 AND h.carton_wgt = 0) c
1929 ENDTEXT
1930
1931 llRetVal = v_SqlExec(lcSQL, lcTempCursor)
1932 IF llRetVal AND USED(lcTempCursor)
1933 SELECT (lcTempCursor)
1934 SCAN
1935 lcWeightMsg = " - This order has cartons with zero weight."
1936 .LogEntry("Order: " + TRANSFORM(ord_num) + lcWeightMsg)
1937 ENDSCAN
1938 USE
1939
1940 * delete from header
1941 TEXT TO lcSQL TEXTMERGE NOSHOW PRETEXT 3
1942 DELETE FROM <<.cPickHeaderTmp>>
1943 WHERE pkey IN (SELECT pkey FROM <<lcSQLTempTable>>)
1944 ENDTEXT
1945
1946 llRetVal = llRetVal AND v_SqlExec(lcSQL)
1947
1948 * delete from detail
1949 TEXT TO lcSQL TEXTMERGE NOSHOW PRETEXT 3
1950 DELETE FROM <<.cPickDetailTmp>>
1951 WHERE pick_num IN (SELECT pick_num FROM <<lcSQLTempTable>>)
1952 ENDTEXT
1953
1954 llRetVal = llRetVal AND v_SqlExec(lcSQL)
1955
1956 * delete backend temp table
1957 v_sqlexec('DROP TABLE ' + lcSQLTempTable)
1958
1959 ENDIF
1960 ENDIF
1961 ENDWITH
1962
1963 SELECT (lnSelect)
1964 RETURN llRetVal
1965 ENDPROC
1966
1967*-------------------------------------------------------------------
1968
1969 PROCEDURE ValidateMismatches
1970
1971 LOCAL llRetVal, lnSelect, lcSQL, lcHeaderTemp, lcMsg
1972 llRetVal = .t.
1973 lnSelect = SELECT()
1974 lcHeaderTemp = GetUniqueFileName()
1975
1976 WITH This
1977
1978 lcSQL = "bcsp_ValidateOrder_Carton_set " + SQLFormatChar(.cPickHeaderTmp)
1979 llRetVal = v_sqlexec(lcSQL, lcHeaderTemp)
1980
1981 IF llRetVal AND USED(lcHeaderTemp)
1982 SELECT (lcHeaderTemp)
1983 SCAN
1984 DO CASE
1985 CASE CTN_STATUS = 'SNC'
1986 lcMsg = "has not been packed."
1987 .AddToErrorText(lcMsg, line_seq, style, color_code, lbl_code, dimension, size_desc)
1988
1989 CASE CTN_STATUS = 'SNO'
1990 lcMsg = "was removed from the order."
1991 .AddToErrorText(lcMsg, line_seq, style, color_code, lbl_code, dimension, size_desc)
1992
1993 CASE CTN_STATUS = 'UP'
1994 lcMsg = "is not completley packed."
1995 .AddToErrorText(lcMsg, line_seq, style, color_code, lbl_code, dimension, size_desc)
1996
1997 CASE CTN_STATUS = 'OP'
1998 lcMsg = "has been overpacked."
1999 .AddToErrorText(lcMsg, line_seq, style, color_code, lbl_code, dimension, size_desc)
2000 ENDCASE
2001
2002 SELECT (lcHeaderTemp)
2003 IF NOT EMPTY(.cErrorText)
2004 .LogEntry("Order: " + TRANSFORM(ord_num) + CRLF + .cErrorText)
2005
2006 * remove order from temp table
2007 llRetVal = llRetVal AND V_sqlexec("DELETE FROM " + .cPickDetailTmp + " WHERE fkey = " + SQLFormatNum(pkey))
2008 llRetVal = llRetVal AND V_sqlexec("DELETE FROM " + .cPickHeaderTmp + " WHERE pkey = " + SQLFormatNum(pkey))
2009 ENDIF
2010 ENDSCAN
2011 USE
2012 ENDIF
2013
2014 ENDWITH
2015
2016 SELECT (lnSelect)
2017 RETURN llRetVal
2018 ENDPROC
2019
2020*-------------------------------------------------------------------
2021
2022 FUNCTION AddToErrorText
2023 LPARAMETERS pcMsg, pnLine_Seq, pcStyle, pcColor, pcLabel, pcDimension, pcSize
2024
2025 This.cErrorText = This.cErrorText + CRLF + CRLF + "Line: " + ;
2026 ALLTRIM(STR(pnLine_Seq)) + "; SKU: " + ;
2027 ALLTRIM(pcStyle) + SPACE(2) + ;
2028 ALLTRIM(pcColor) + SPACE(2) + ;
2029 ALLTRIM(pcLabel) + SPACE(2) + ;
2030 ALLTRIM(pcDimension) + SPACE(2) + ;
2031 ALLTRIM(pcSize) + CRLF + pcMsg
2032 RETURN
2033 ENDFUNC
2034
2035*-------------------------------------------------------------------
2036
2037 FUNCTION ProcessCCard
2038
2039 *--- TR 1054859 14-06-2011 ---* && lnOver_ChargeAmt Added.
2040 LOCAL llRetVal, lnSelect, lcSQL, lcDivision, lnOver_ChargeAmt
2041 LOCAL lcFactor ,nInvAmt, lnPkey ,lcShipHeader ,lcShipDetail, lnOrd_Num, lnPick_Num, lnPick_Pkey, lnCCInvAmt,lcorg_cod,lcCurr_code
2042
2043 *--- TechRec 1080537 13-Aug-2014 vkrishnamurthy ---
2044 LOCAL loSOHeader
2045 *=== TechRec 1080537 13-Aug-2014 vkrishnamurthy ===
2046
2047 llRetVal = .t.
2048 lnSelect = SELECT()
2049
2050 WITH This
2051 IF (.lEnableCreditCard OR .lCCVerisignApproval)
2052 lcShipHeader = .cPickHeaderTmp
2053 lcShipDetail = .cPickDetailTmp
2054 cCurrHdrAlias = GetUniqueFileName()
2055 *--- TechRec 1076664 25-Feb-2014 MANI. Added h.org_code,h.curr_code missed in tr 10646083==
2056 &&--- TechRec 1080537 13-Aug-2014 vkrishnamurthy --- Added h.ord_type
2057 TEXT TO lcSQL TEXTMERGE NOSHOW FLAGS 3
2058 SELECT h.pkey, h.orig_key, h.division, h.ord_num, h.pick_num, rh.zpick_amount,h.org_code,h.curr_code,h.ord_type
2059 FROM <<lcShipHeader>> h
2060 JOIN zzwfactr f
2061 ON h.factor = f.factor
2062 AND f.ccard_ok = 'Y'
2063 JOIN vvoordrh rh
2064 ON h.ord_num = rh.ord_num AND h.pick_num = rh.pick_num
2065 ENDTEXT
2066
2067 llRetVal = v_sqlexec(lcSQL, cCurrHdrAlias)
2068 IF llRetVal AND USED(cCurrHdrAlias)
2069 SELECT(cCurrHdrAlias)
2070 SCAN
2071
2072 lcDivision = division
2073 lnOrd_Num = ord_num
2074 lnPick_Num= pick_num
2075 lnPick_Pkey = orig_key
2076 lnCCInvAmt = zpick_amount
2077 *--- TechRec 1076664 25-Feb-2014 MANI. ---
2078 lcorg_code = org_code
2079 lcCurr_code = Curr_code
2080 *=== TechRec 1076664 25-Feb-2014 MANI. ===
2081
2082 *--- TechRec 1080537 13-Aug-2014 vkrishnamurthy ---
2083 SCATTER Name loSOHeader
2084 *=== TechRec 1080537 13-Aug-2014 vkrishnamurthy ===
2085
2086 lcSQL = " SELECT * FROM ZZOCRDCR " + ;
2087 " WHERE fkey = " + SQLFormatNum(lnPick_Pkey)
2088
2089 llRetval = V_sqlExec(lcSQL,'vzzocrdcr') AND RECCOUNT('vzzocrdcr') = 1 && For Clsccard - LoadCardInfo
2090
2091 *--- TR 1054859 14-0-2011 RKI ---*
2092 lnOver_ChargeAmt = Over_ChargeAmt
2093
2094 IF lnOver_ChargeAmt = 1 AND ChargeAmt > 0
2095 lnCCInvAmt = ChargeAmt
2096 ELSE
2097 IF lnOver_ChargeAmt = 1 AND ChargeAmt = 0
2098 lnCCInvAmt = This.oBOSOPDF.CalculatePartialCCChargeAmt(lnOrd_Num,lnPick_Num,.T.,,'P')
2099 ENDIF
2100 ENDIF
2101 *=== TR 1054859 14-06-2011 RKI ===*
2102 *--- TechRec 1076664 25-Feb-2014 MANI. Added h.org_code,h.curr_code missed in tr 10646083==
2103 *--- TechRec 1080537 13-Aug-2014 vkrishnamurthy ---
2104*!* llRetVal = llRetVal AND This.oBPOPayflowPro.LoadMerchantInfo(lcdivision,lcorg_code,lcCurr_code)
2105
2106 llRetVal = llRetVal AND This.oBPOPayflowPro.LoadMerchantInfo(loSOHeader)
2107 *=== TechRec 1080537 13-Aug-2014 vkrishnamurthy ===
2108
2109
2110 llRetVal = llRetVal AND .oBPOPayflowPro.ProcessCreditCard("I", lnPick_Pkey , lnCCInvAmt)
2111
2112 llRetVal = llRetVal AND (.oBPOPayflowPro.cApproveMessage = "Approved" )
2113
2114 IF NOT llRetVal
2115 .LogEntry("Order: " + TRANSFORM(lnOrd_Num)+ " - Verisign Approval Failed. Credit Card " + .oBpoPayFlowpro.cApproveMessage)
2116 llRetVal = v_SqlExec("DELETE FROM " + .cPickDetailTmp + ;
2117 " WHERE pick_num = " + SQLFormatNum(lnPick_num))
2118 llRetVal = v_SqlExec("DELETE FROM " + .cPickHeaderTmp + ;
2119 " WHERE pick_num = " + SQLFormatNum(lnPick_num))
2120 ELSE
2121 llRetVal = v_SqlExec("UPDATE " + .cPickHeaderTmp + ;
2122 " SET approvalid = " + SQLFormatChar(.oBPOPayflowPro.cNewApproveId) + ;
2123 " ,ChargeAmt = " + SQLFormatNum(lnCCInvAmt,5) + ;
2124 " WHERE orig_key = " + SQLFormatNum(lnPick_Pkey))
2125 ENDIF
2126
2127 ENDSCAN
2128 .TableClose('vzzocrdcr')
2129 USE IN SELECT(cCurrHdrAlias)
2130 ENDIF
2131 ENDIF
2132 ENDWITH
2133 SELECT (lnSelect)
2134 RETURN llRetVal
2135 ENDFUNC
2136
2137*-------------------------------------------------------------------
2138
2139 FUNCTION UpdateCCInvoice
2140 LOCAL llRetVal, lcSQL
2141
2142 *--- TR 1055685 02-Aug-11 SK Initialised llRetVal
2143 llRetVal = .T.
2144
2145 IF (.lEnableCreditCard OR .lCCVerisignApproval)
2146 *--- TechRec 1076664 19-Feb-2014 MANI. Changed h.pkey to orig_key in subquery ====
2147 *-- Since h.orig_key will match fkey
2148 TEXT TO lcSQL TEXTMERGE NOSHOW FLAGS 3
2149 DELETE FROM zzocrdcr
2150 WHERE fkey IN (SELECT h.orig_key
2151 FROM <<.cPickHeaderTmp>> h
2152 JOIN zzwfactr f
2153 ON h.factor = f.factor
2154 AND f.ccard_ok = 'Y')
2155 ENDTEXT
2156
2157 llRetval = V_sqlExec(lcSQL)
2158
2159 *--- TR 1075285 12/24/13 ATHIRUNAVU Modified zzocrdcr_h alias form "cr to cr1" as textmerg is wrongly converting to chr(13)
2160 *--- TechRec 1076664 19-Feb-2014 MANI. Changed h.pkey to orig_key in subquery ====
2161 *-- Since h.orig_key will match fkey
2162 TEXT TO lcSQL TEXTMERGE NOSHOW FLAGS 3
2163 UPDATE zzocrdcr_h
2164 SET approvaltype = 'S'
2165 ,approvalid = h.approvalid
2166 ,chargeamt = h.chargeamt
2167 ,last_mod = <<SQLFormatTS(DATETIME())>>
2168 ,user_id = <<SQLFormatChar(g_cUser)>>
2169 ,fkey = h.pkey
2170 FROM zzocrdcr_h cr1
2171 JOIN <<.cPickHeaderTmp>> h
2172 ON h.orig_key = cr1.fkey
2173 JOIN zzwfactr f
2174 ON h.factor = f.factor
2175 WHERE f.ccard_ok = 'Y' AND h.approvalid <> ''
2176 ENDTEXT
2177
2178 llRetval = V_sqlExec(lcSQL)
2179 ENDIF
2180
2181 RETURN llRetVal
2182 ENDPROC
2183
2184*-------------------------------------------------------------------
2185
2186 FUNCTION RevertCCInvoice
2187 LOCAL llRetVal, lcSQL, lcTempCursor
2188
2189 IF (.lEnableCreditCard OR .lCCVerisignApproval)
2190
2191 lcTempCursor = GetUniqueFileName()
2192
2193 TEXT TO lcSQL TEXTMERGE NOSHOW PRETEXT 3
2194 SELECT pkey, orig_key, chargeamt, approvalid
2195 FROM <<.cPickHeaderTmp>>
2196 WHERE approvalid <> ''
2197 ENDTEXT
2198
2199 llRetval = V_sqlExec(lcSQL, lcTempCursor)
2200
2201 IF llRetVal AND USED(lcTempCursor)
2202 * Need to Revert the CCard Transaction
2203 SELECT (lcTempCursor)
2204 SCAN
2205 llRetVal = .oBPOPayflowPro.ProcessCreditCard("R", orig_key, chargeamt, approvalid)
2206 ENDSCAN
2207 USE IN SELECT(lcTempCursor)
2208 ENDIF
2209
2210 ENDIF
2211
2212 RETURN llRetVal
2213 ENDFUNC
2214
2215*-------------------------------------------------------------------
2216
2217 FUNCTION UpdateProgressBar
2218 LPARAMETERS pcMessage, pnProg
2219
2220 IF VARTYPE(.oProgressBar) = 'O'
2221 .oProgressBar.UpdateThermoCaption(pcMessage)
2222
2223 .oProgressBar.AdvanceThermo(100)
2224 .nThermTotal = .nThermTotal + pnProg
2225 .oProgressBar.AdvanceThermoTotal(.nThermTotal)
2226 ENDIF
2227 ENDFUNC
2228
2229*-------------------------------------------------------------------
2230
2231 PROCEDURE RemoveFromHoldTable
2232
2233 *---TR 1064618 02/22/2013 YE === New variable lcHeading
2234 LOCAL llRetVal, lnSelect, lcSQLDelete, lcHeading
2235
2236 llRetVal = .t.
2237 lnSelect = SELECT()
2238
2239 WITH THIS
2240
2241 *---TR 1064618 02/22/2013 YE
2242 lcHeading = " Removing processed records from zzyabill "
2243 .oLog.LogMajorStage(lcHeading)
2244 *===TR 1064618 02/22/2013 YE
2245
2246 * delete all processed records from zzyabill
2247 lcSQLDelete = "DELETE FROM zzyabill " + ;
2248 " WHERE pkey IN (SELECT orig_key FROM " + .cPickHeaderTmp + " )"
2249
2250 llRetVal = llRetVal AND v_SQLExec(lcSQLDelete)
2251
2252 *---TR 1064618 02/22/2013 YE
2253 .oLog.LogResult(llRetVal, lcHeading)
2254 *===TR 1064618 02/22/2013 YE
2255
2256 ENDWITH
2257
2258 SELECT (lnSelect)
2259 RETURN llRetVal
2260 ENDPROC
2261
2262*==================================================================
2263*--- TechRec 1065054 13-Mar-2013 vkrishnamurthy ---
2264*============================================================
2265
2266 FUNCTION ValidateFrozenCostsheetCheck
2267 LPARAMETERS tlFrozen
2268 LOCAL llRetVal, lnSelect ,lcSQLString ,lcHeading ,lcTempTable ,lcCursor,lcMsg
2269
2270 llRetVal = .t.
2271 lnSelect = SELECT()
2272
2273 lcCursor = GetUniqueFileName()
2274
2275 WITH This
2276 IF tlFrozen
2277 lcHeading = " Validating for Frozen Costsheets.."
2278 ELSE
2279 lcHeading = " Validating for Matching Costsheets.."
2280 ENDIF
2281 .oLog.LogMajorStage(lcHeading)
2282
2283 lcSQLString = "Select distinct d.ord_num , d.fkey,d.style, d.division, d.location ,x.auto_loc_cost "+;
2284 " from " + .cPickDetailTmp + " d "+;
2285 " Join ZZOCNTRC C " + ;
2286 " on d.division = c.division " + ;
2287 " and c.frzcostok = 'Y' " + ;
2288 " Join ZZXCOMPR X " +;
2289 " on 1= 1 " + ;
2290 " and not exists ( select 1 from zzdcosth " + ;
2291 " where division = d.division "+ ;
2292 " and style = d.style "+ ;
2293 " and shipto = case when x.auto_loc_cost = 'Y' then d.location else shipto end " + ;
2294 " and " + IIF(tlFrozen , " Frozen = 'Y' ", " 1= 1 ") + ")"
2295 lcTempTable = SQLTableFromQuery(lcSQLString)
2296
2297 IF NOT EMPTY(lcTempTable )
2298 lcSQLString = " Select * from " + lcTempTable
2299 llRetVal = llRetVal AND v_Sqlexec(lcSQLString ,lcCurSor)
2300
2301 IF llRetVal AND RECCOUNT(lcCurSor) >0
2302 SELECT(lcCurSor)
2303 IF tlFrozen
2304 lcMsg = "For Following order lines Matching cost sheet is found but not frozen "
2305 ELSE
2306 lcMsg = "For Following order lines Matching cost sheet not found "
2307 ENDIF
2308 .LogEntry(lcMsg )
2309 SCAN
2310 IF auto_loc_cost == 'Y'
2311 lcMsg = "Order: " + TRANSFORM(ord_num) + ;
2312 " Division /Style/Location- "+ SqlFormatChar(division)+ "/"+ SqlFormatChar(style);
2313 + "/"+ SqlFormatChar(location)
2314 ELSE
2315 lcMsg = "Order: " + TRANSFORM(ord_num) + ;
2316 " Division /Style- "+ SqlFormatChar(division)+ "/"+ SqlFormatChar(style)
2317 ENDIF
2318 .LogEntry(lcMsg )
2319 ENDSCAN
2320 ENDIF
2321
2322 * delete from header
2323 lcSQLString = " DELETE "+ .cPickHeaderTmp + ;
2324 " FROM "+ .cPickHeaderTmp + " H " + ;
2325 " JOIN " + lcTempTable + " temp" +;
2326 " ON H.Ord_num = temp.ord_num " + ;
2327 " AND H.Pkey = temp.fkey"
2328 llRetVal = llRetVal AND .vSQLExec(lcSQLString)
2329
2330
2331 * delete from detail
2332 lcSQLString = " DELETE "+ .cPickDetailTmp+ ;
2333 " FROM "+ .cPickDetailTmp+ " d " + ;
2334 " JOIN " + lcTempTable + " temp" +;
2335 " ON d.Ord_num = temp.ord_num " + ;
2336 " AND d.Fkey = temp.fkey"
2337 llRetVal = llRetVal AND .vSQLExec(lcSQLString)
2338
2339 * delete backend temp table
2340 llRetVal = llRetVal AND v_sqlexec('DROP TABLE ' + lcTempTable )
2341
2342 ENDIF
2343
2344 .oLog.LogResult(llRetVal, lcHeading)
2345 ENDWITH
2346
2347
2348 SELECT (lnSelect)
2349 RETURN llRetVal
2350 ENDFUNC
2351*============================================================
2352
2353*=== TechRec 1065054 13-Mar-2013 vkrishnamurthy ===
2354
2355 *--- TR 1071221 21-Nov-2014 BNarayanan ---
2356 FUNCTION UpdateInvoicedCC
2357 LOCAL llRetVal, lcSQL
2358
2359 llRetVal = .T.
2360
2361 IF (.lEnableCreditCard OR .lCCVerisignApproval)
2362 TEXT TO lcSQL TEXTMERGE NOSHOW FLAGS 3
2363 UPDATE c SET c.fkey = h.pkey
2364 FROM zzocrdcr c
2365 JOIN <<.cPickHeaderTmp>> h
2366 ON c.fkey = h.orig_key
2367 JOIN zzwfactr f
2368 ON h.factor = f.factor
2369 AND f.ccard_ok = 'Y'
2370 ENDTEXT
2371
2372 llRetval = V_sqlExec(lcSQL)
2373 ENDIF
2374
2375 RETURN llRetVal
2376 ENDFUNC
2377 *=== TR 1071221 21-Nov-2014 BNarayanan ===
2378
2379
2380ENDDEFINE