· 8 years ago · Nov 06, 2017, 06:20 PM
1' Replicating a problem with Sage 100 / Distribution table access.
2'
3' Format: VBScript. To run: Copy to a local PC folder as a 'test.vbs' file;
4' double click using Windows File Explorer. Sage 100 must be installed.
5'
6' Input: The script will prompt for:
7' User Name, Password, Company, Sales Order number, Shipped ID, Lot/Serial per each SO item.
8'
9' A Sales Order Entry must be created before the test, containing at least two line items with a product with LOT valuation.
10' For example, for ABC sample company: two line items containing items 2480-8-50 and 2481-5-50. Items should NOT be
11' allocated/distributed in the sales order entry.
12' The script will distribute the items. JAN10 may be entered as a lot number for both items, with script prompts.
13' The issue is replicated when the following shows up with script messages:
14' - 'WHY AM I SHOWING UNRELATED DATA?', and
15' - 'WHY DID NOT I SHOW DISTRIBUTION ON LINE ITEM 000004 ?'.
16'
17' Issue: oLines.oDistribution does not give Read access to a proper distribution record when navigating through line items
18' and the current line item is second or next.
19' In other words: Navigating to the second line item does not give access to that line item's distributions. It allows
20' saving changes, but those changes again are not reflected with a subsequent Read operation.
21'
22
23Sub errchk(r, desc, o)
24 if (r = 0) then
25 MsgBox("Error " & desc & ": " & o.sLastErrorMsg)
26 oss.nCleanup()
27 oss.DropObject()
28 Set oss = Nothing
29 WScript.Quit
30 end if
31end sub
32
33Sub warnchk(r, desc, o)
34 if (r = 0) then
35 MsgBox("Warning " & desc & ": " & o.sLastErrorMsg)
36 end if
37end sub
38
39function wSplittedString(data)
40 r = ""
41 i_ = 1
42 if (InStr(data, Chr(138)) > 0) then
43 while i_ <= Len(data)
44 char=Mid(data, i_, 1)
45 if (char = Chr(138)) then
46 r = r & Chr(255)
47 else
48 r = r & char
49 end if
50 i_ = i_ + 1
51 wend
52 else
53 while i_ <= Len(data)
54 char=Mid(data, i_, 1)
55 if (Asc(char) > 128) then
56 r = r & Chr(255)
57 else
58 r = r & char
59 end if
60 i_ = i_ + 1
61 wend
62 end if
63
64 r = Replace(r, Chr(13), " ")
65 r = Replace(r, Chr(10), " ")
66 r = Replace(r, Chr(255), vbCrLf)
67 wSplittedString = r
68end function
69
70function PreparedString(data, removeEmptyStrings)
71 data = wSplittedString(data)
72 if (removeEmptyStrings) then
73 while InStr(data, vbCrLf & vbCrLf) > 0
74 data = Replace(data, vbCrLf & vbCrLf, vbCrLf)
75 if (InStr(data, vbCrLf) = 1) then
76 data = Mid(data, Len(vbCrLf) + 1)
77 end if
78 wend
79
80 if (Mid(data, Len(data) - Len(vbCrLf) + 1, Len(vbCrLf)) = vbCrLf) then
81 data = Mid(data, 1, Len(data) - Len(vbCrLf))
82 end if
83 end if
84
85 PreparedString = data
86end function
87
88function iif(what, iftrue, iffalse)
89 if (what) then
90 iif = iftrue
91 else
92 iif = iffalse
93 end if
94end function
95
96'Create ProvideX COM Object
97Set oScript = CreateObject ("ProvideX.Script")
98
99'oScript.TraceOn=true
100
101'Get the ODBC path for the last accessed installation of MAS 90/200
102Const HKEY_CURRENT_USER = &H80000001
103Set oReg = GetObject("winmgmts:\\.\root\default:StdRegProv")
104oReg.GetExpandedStringValue HKEY_CURRENT_USER,"Software\ODBC\ODBC.INI\SOTAMAS90","Directory",PathRoot
105PathHome = PathRoot & "\Home"
106'MsgBox(PathHome)
107Set oReg = Nothing
108
109'The Init method must be the first method called
110oScript.Init(PathHome)
111
112'The Session object must be the first MAS 90 object created
113Set oSS = oScript.NewObject("SY_Session")
114
115' Set the user for the Session
116r = oSS.nLogon()
117If r=0 Then
118 '''''''''''''''''''''''
119 ' Enter: username, password
120 user = Trim(InputBox("Enter User Name", "", "alex"))
121 password = Trim(InputBox("Enter Password", "", ""))
122 retVAL = oSS.nSetUser(User,Password)
123
124 r = oss.nSetUser(user, password)
125 errchk r, "logging in", oss
126End If
127
128' Set the company, module date and module for the Session
129company = Trim(InputBox("Enter Company", "", "AAA"))
130r = oss.nsetcompany(company)
131errchk r, "setting company", oss
132
133orderNumber = trim(InputBox("Enter Sales Order Number", "", "0000182"))
134
135' Prompt for a batch ID
136batchOptions = "N"
137Set oBatchOptions = oScript.NewObject("SY_BATCHOPTIONS_SVC", oSS)
138r = oBatchOptions.nFind("S/O01")
139errchk r, "Finding batch options", oBatchOptions
140r = oBatchOptions.nGetValue("ENABLEBATCHENTRY$", batchOptions)
141errchk r, "Getting batch options", oBatchOptions
142batchId = ""
143nextBatchNumber = ""
144if batchOptions = "Y" then
145 batchId = trim(InputBox("Enter Batch ID", "", "00001"))
146' nextBatchNumber = oBatchOptions.nGetValue("NEXTBATCHNO$")
147end if
148
149shipperId = ""
150shipperId = trim(InputBox("Enter Shipper ID", "", "1"))
151
152sDate = oSS.sModuleDate
153if sDate = "" then
154 sDate = Year(Date) & Right("0" & Month(Date), 2) & Right("0" & Day(Date), 2)
155end if
156retVAL = oSS.nSetDate("S/O",sDate)
157r = oSS.nSetModule("S/O")
158errchk r, "Setting module", oSS
159
160qtyOrdered = 0
161qtyBackordered = 0
162Dim lines()
163Dim qtiesToShip()
164
165''''''''''''''''''''''''''''''''''
166' Filling in the outstanding quantity to ship
167
168oSEC_order = oSS.nSetProgram(oSS.nLookupTask("SO_SalesOrder_ui"))
169Set o_order = oScript.NewObject("SO_SalesOrder_bus", oSS)
170
171retVal = o_order.nSetKey( orderNumber )
172errchk retVal, "Set Order Key", o_order
173
174i = 0
175r = o_order.oLines.nMoveFirst
176'MsgBox "Before the first line items loop"
177do while o_order.oLines.nEOF = 0
178 'MsgBox "First line items loop - iteration " & i
179
180 r = o_order.oLines.nGetValue("QUANTITYORDERED", qtyOrdered)
181 r = o_order.oLines.nGetValue("QUANTITYSHIPPED", qtyToBackordered)
182
183 Redim preserve qtiesToShip(i+1)
184 qtiesToShip(i) = qtyOrdered - qtyBackordered
185' MsgBox "Qty to ship: " + CStr(qtiesToShip(i))
186
187 i = i + 1
188 r = o_order.oLines.nMoveNext
189loop
190
191r = o_order.DropObject()
192o_order = 0
193
194''
195''''''''''''''''''''''''''''''''''
196
197' Creating Shipment business object
198oSEC = oSS.nSetProgram(oSS.nLookupTask("SO_Shipping_UI"))
199Set oHeader = oScript.NewObject("SO_Shipping_bus", oSS)
200fields = oHeader.sGetDataSources
201'MsgBox(fields)
202
203nextInvoiceNo = ""
204r = oHeader.nGetNextInvoiceNo(nextInvoiceNo)
205errchk r, "Getting next invoice number", oHeader
206
207if batchOptions = "Y" then
208 r = oHeader.nSelectBatch(batchId)
209 errchk r, "Selecting the batch", oHeader
210 'MsgBox("Selected")
211end if
212
213r = oHeader.nSetKeyValue("InvoiceNo$", nextInvoiceNo)
214errchk r, "Setting invoice number", oHeader
215r = oHeader.nSetKey()
216errchk r, "Selecting the invoice", oHeader
217r = oHeader.nSetValue("ShipperID$", shipperId)
218errchk r, "setting shipper ID", oHeader
219r = oHeader.nSetValue("SalesOrderNo$", orderNumber)
220'errchk r, "setting order number", oHeader
221
222MsgBox "Document created: " & nextInvoiceNo
223
224invoiceNumber=""
225lineSequenceNumber=""
226lineKey=""
227
228' Re-populating shipment line items
229r = oHeader.oLines.nClear
230errchk r, "clear lines", oHeader.oLines
231r = oHeader.oLines.nCopyLinesFromSalesOrder(orderNumber, "Y")
232errchk r, "copy lines", oHeader.oLines
233
234' Fillng in an in-memory list of line items to further navigate
235i = 0
236r = oHeader.oLines.nMoveFirst
237do while oHeader.oLines.nEOF = 0
238 r = oHeader.oLines.nGetValue("INVOICENO$", invoiceNumber)
239 r = oHeader.oLines.nGetValue("LineSeqNo$", lineSequenceNumber)
240 r = oHeader.oLines.nGetValue("LINEKEY$", lineKey)
241 r = oHeader.oLines.nGetValue("QUANTITYORDERED", qtyOrdered)
242 r = oHeader.oLines.nGetValue("QUANTITYSHIPPED", qtyToBackordered)
243
244 key = invoiceNumber+lineSequenceNumber+lineKey
245 Redim preserve lines(i+1)
246 lines(i) = key
247
248 i = i + 1
249 r = oHeader.oLines.nMoveNext
250loop
251
252currentIndex=""
253index = oHeader.oLines.sCurrentIndex()
254'MsgBox "Current index: " + index
255'r = oHeader.oLines.nSetIndex("kDISPLAY")
256'errchk r, "error setting index", oHeader.oLines
257
258' Preparing distributions object to get available distributions
259tableName = "IM_ItemCost_SVC"
260bObjectExists = oScript.Evaluate("o_" & tableName) <> 0
261if not bObjectExists then
262 sExpr = "o_" + tableName + "=NEW( """ + tableName + """, %SYS_SS )"
263 oScript.Execute(sExpr)
264end if
265
266' Getting ItemCode Length
267Set fo = oSS.oFileObj
268itemCodeLength = fo.nGetColumnLength("IM_ItemCost", "ItemCode$")
269
270' Iterating in-memory line items
271for i=0 to UBound(lines)-1
272 'MsgBox "Iteration # " & i+1 & " Searching for key: '" + lines(i)
273
274 r = oHeader.oLines.nMoveFirst()
275 errchk r, "Move First", oHeader.oLines
276
277 r = oHeader.oLines.nFind(lines(i))
278 errchk r, "document not found", oHeader.oLines
279
280 item = ""
281 r = oHeader.oLines.nGetValue("ItemCode$", item)
282 errchk r, "getting item code", oHeader.oLines
283
284 sValuation = ""
285 sValuation = oHeader.oLines.sItemValuation
286
287 ' Current position diagnostics
288 r = oHeader.oLines.nGetValue("INVOICENO$", invoiceNumber)
289 r = oHeader.oLines.nGetValue("LineSeqNo$", lineSequenceNumber)
290 r = oHeader.oLines.nGetValue("LINEKEY$", lineKey)
291 MsgBox "Processing line item: Invoice '" & invoiceNumber & "' Line sequence # '" & lineSequenceNumber & "' Line Key '" & lineKey & "' Item '" & item & "' valuation " & sValuation & "; Shipping qty: " & qtiesToShip(i)
292
293 if sValuation = 5 or sValuation = 6 then
294
295 ' Getting available lot/serial
296 itemLow = "PAD(""" & item & """, " & itemCodeLength & ", $00$)"
297 itemHigh = itemLow + " + $FE$"
298 condition = ""
299 expr = "iRet = o_" & tableName & "'GetResultSets(""ItemCode$+""""`!`""""+TierType$+""""`!`""""+LotSerialNo$+""""`!`""""+STR(QuantityOnHand-QuantityCommitted)"", ""ItemCode$"", s1$, s2$, """ & condition & """," & itemLow & "," & itemHigh & ")"
300 r = oScript.Execute(expr)
301
302 iRes = oScript.Evaluate("iRet")
303 wResultFirst = oScript.Evaluate("S1$")
304 wResult = oScript.Evaluate("S2$")
305
306 list = PreparedString(wResultFirst, true)
307 listArr = Split(list, vbCrlf)
308 itemList = ""
309 for j=0 to UBound(listArr)
310 itemList = itemList & vbCrlf & listArr(j)
311 next
312
313 qty = qtiesToShip(i)
314 if sValuation = 6 then
315 qty = 1
316 end if
317
318 ' Lot/Serial prompt
319 lotSerial = trim(InputBox("Enter a lot/serial. List of available: " & itemList, "", ""))
320
321 currentIndex=""
322 index = oHeader.oLines.oDistribution.sCurrentIndex()
323 'MsgBox "Current distribution index: " + index
324 'r = oHeader.oLines.nSetIndex("kDISPLAY")
325 'errchk r, "error setting index", oHeader.oLines
326
327 ' Reading existing distributed quantity
328 distributionKey = invoiceNumber + lineKey + lotSerial
329
330 r = oHeader.oLines.oDistribution.nFind(distributionKey)
331 ' warnchk r, "Setting Distribution position", oHeader.oLines.oDistribution
332
333 r = oHeader.oLines.oDistribution.nMoveFirst
334 'MsgBox "Iterating distributions: MoveFirst = " & r
335
336 eof = oHeader.oLines.oDistribution.nEOF
337 MsgBox "EXISTING DISTRIBUTIONS: Iterating; EOF = " & eof & " meaning " & iif(eof=0, "RECORD EXISTS", "NO MORE RECORDS")
338
339 do while eof = 0
340 itemCode = ""
341 r = oHeader.oLines.oDistribution.nGetValue("ITEMCODE$", itemCode)
342 lotSerial = ""
343 r = oHeader.oLines.oDistribution.nGetValue("INVOICENO$", lotSerial)
344 qty = 0
345 r = oHeader.oLines.oDistribution.nGetValue("QuantityShipped", qty)
346 lk = ""
347 r = oHeader.oLines.oDistribution.nGetValue("LineKey$", lk)
348
349 MsgBox "EXISTING DISTRIBUTIONS: Read item '" & itemCode & "' Line Key '" & lk & "' Lot/Serial '" & lotSerial & "' Qty " & qty & Iif(lk <> lineKey, " WHY AM I SHOWING UNRELATED DATA? ", "")
350
351 r = oHeader.oLines.oDistribution.nMoveNext
352 'MsgBox "Iterating distributions: MoveNext = " & r
353 eof = oHeader.oLines.oDistribution.nEOF
354 MsgBox "EXISTING DISTRIBUTIONS: Iterating; MoveNext EOF = " & eof & " meaning " & iif(eof=0, "RECORD EXISTS", "NO MORE RECORDS")
355 loop
356
357 ' Distributing
358 MsgBox "DISTRIBUTING: Invoice '" & invoiceNumber & "' Line Key '" & lineKey & "' Lot/serial '" & lotSerial & "'; qty " & qty
359
360 r = oHeader.oLines.oDistribution.nSetKeyValue("INVOICENO$", invoiceNumber)
361 r = oHeader.oLines.oDistribution.nSetKeyValue("LINEKEY$", lineKey)
362 r = oHeader.oLines.oDistribution.nSetKeyValue("LOTSERIALNO$", lotSerial)
363 r = oHeader.oLines.oDistribution.nSetKey
364 errchk r, "navigating to distribution item", oHeader.oLines.oDistribution
365
366 r = oHeader.oLines.oDistribution.nSetValue("QUANTITYSHIPPED", qty)
367 r = oHeader.oLines.oDistribution.nWrite
368 errchk r, "distribution line write", oHeader.oLines.oDistribution
369
370 r = oHeader.oLines.oDistribution.nUpdateIMItemCost
371 ' warnchk r, "IM_ItemCost update", oHeader.oLines.oDistribution
372
373 'MsgBox "Iterating distributions"
374 r = oHeader.oLines.oDistribution.nMoveFirst
375 'MsgBox "Iterating distributions: MoveFirst = " & r
376 t = ""
377 r = oHeader.oLines.oDistribution.nGetValue("LINEKEY$", t)
378 'MsgBox "LineKey for check: " & t
379
380 conditionSatisfied = 0
381 eof = oHeader.oLines.oDistribution.nEOF
382 MsgBox "DISTRIBUTIONS AFTER UPDATE: Iterating; EOF = " & eof & " meaning " & iif(eof=0, "RECORD EXISTS", "NO MORE RECORDS")
383 do while eof = 0
384 itemCode = ""
385 r = oHeader.oLines.oDistribution.nGetValue("ITEMCODE$", itemCode)
386 lotSerial = ""
387 r = oHeader.oLines.oDistribution.nGetValue("INVOICENO$", lotSerial)
388 qty = 0
389 r = oHeader.oLines.oDistribution.nGetValue("QuantityShipped", qty)
390 lk = ""
391 r = oHeader.oLines.oDistribution.nGetValue("LineKey$", lk)
392
393 MsgBox "DISTRIBUTIONS AFTER UPDATE: Read item '" & itemCode & "' Line Key '" & lk & "' Lot/Serial '" & lotSerial & "' Qty " & qty
394
395 if lk = lineKey then
396 conditionSatisfied = conditionSatisfied + 1
397 end if
398
399 r = oHeader.oLines.oDistribution.nMoveNext
400 'MsgBox "Iterating distributions: MoveNext = " & r
401 eof = oHeader.oLines.oDistribution.nEOF
402 MsgBox "DISTRIBUTIONS AFTER UPDATE: Iterating; MoveNext EOF = " & eof & " meaning " & iif(eof=0, "RECORD EXISTS", "NO MORE RECORDS")
403 loop
404 end if
405
406 if conditionSatisfied = 0 then
407 MsgBox "WHY DID NOT I SHOW DISTRIBUTION ON LINE ITEM " & lineKey & " ?"
408 end if
409
410 r = oHeader.oLines.nSetValue("QUANTITYSHIPPED", qtiesToShip(i))
411 r = oHeader.oLines.nSetValue("QUANTITYBACKORDERED", 0)
412 r = oHeader.oLines.nSetQuantityBackordered(0)
413
414 r = oHeader.oLines.nSetValue("LotSerialFullyDistributed$", "Y")
415
416 r = oHeader.oLines.nWrite
417 errchk r, "line write", oHeader.oLines
418
419 MsgBox "Item processing finalized"
420next
421
422r = oHeader.nWrite
423errchk r, "saving the shipment - " & op, oHeader
424
425Wscript.Echo "DONE"
426
427' Done with the Invoice object
428r = oHeader.DropObject()
429oHeader = 0
430
431oss.nCleanup() ' Call Cleanup() before dropping the Session Object
432oss.DropObject()
433Set oss = Nothing
434WScript.Quit