API tools faq
paste
Guest User

Untitled

a guest
Nov 6th, 2017
114
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 14.05 KB | None | 0 0
raw download clone embed print report
  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.  
  23. Sub 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
  31. end sub
  32.  
  33. Sub warnchk(r, desc, o)
  34. if (r = 0) then
  35. MsgBox("Warning " & desc & ": " & o.sLastErrorMsg)
  36. end if
  37. end sub
  38.  
  39. function 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
  68. end function
  69.  
  70. function 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
  86. end function
  87.  
  88. function iif(what, iftrue, iffalse)
  89. if (what) then
  90. iif = iftrue
  91. else
  92. iif = iffalse
  93. end if
  94. end function
  95.  
  96. 'Create ProvideX COM Object
  97. Set oScript = CreateObject ("ProvideX.Script")
  98.  
  99. 'oScript.TraceOn=true
  100.  
  101. 'Get the ODBC path for the last accessed installation of MAS 90/200
  102. Const HKEY_CURRENT_USER = &H80000001
  103. Set oReg = GetObject("winmgmts:\\.\root\default:StdRegProv")
  104. oReg.GetExpandedStringValue HKEY_CURRENT_USER,"Software\ODBC\ODBC.INI\SOTAMAS90","Directory",PathRoot
  105. PathHome = PathRoot & "\Home"
  106. 'MsgBox(PathHome)
  107. Set oReg = Nothing
  108.  
  109. 'The Init method must be the first method called
  110. oScript.Init(PathHome)
  111.  
  112. 'The Session object must be the first MAS 90 object created
  113. Set oSS = oScript.NewObject("SY_Session")
  114.  
  115. ' Set the user for the Session
  116. r = oSS.nLogon()
  117. If 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
  126. End If
  127.  
  128. ' Set the company, module date and module for the Session
  129. company = Trim(InputBox("Enter Company", "", "AAA"))
  130. r = oss.nsetcompany(company)
  131. errchk r, "setting company", oss
  132.  
  133. orderNumber = trim(InputBox("Enter Sales Order Number", "", "0000182"))
  134.  
  135. ' Prompt for a batch ID
  136. batchOptions = "N"
  137. Set oBatchOptions = oScript.NewObject("SY_BATCHOPTIONS_SVC", oSS)
  138. r = oBatchOptions.nFind("S/O01")
  139. errchk r, "Finding batch options", oBatchOptions
  140. r = oBatchOptions.nGetValue("ENABLEBATCHENTRY$", batchOptions)
  141. errchk r, "Getting batch options", oBatchOptions
  142. batchId = ""
  143. nextBatchNumber = ""
  144. if batchOptions = "Y" then
  145. batchId = trim(InputBox("Enter Batch ID", "", "00001"))
  146. ' nextBatchNumber = oBatchOptions.nGetValue("NEXTBATCHNO$")
  147. end if
  148.  
  149. shipperId = ""
  150. shipperId = trim(InputBox("Enter Shipper ID", "", "1"))
  151.  
  152. sDate = oSS.sModuleDate
  153. if sDate = "" then
  154. sDate = Year(Date) & Right("0" & Month(Date), 2) & Right("0" & Day(Date), 2)
  155. end if
  156. retVAL = oSS.nSetDate("S/O",sDate)
  157. r = oSS.nSetModule("S/O")
  158. errchk r, "Setting module", oSS
  159.  
  160. qtyOrdered = 0
  161. qtyBackordered = 0
  162. Dim lines()
  163. Dim qtiesToShip()
  164.  
  165. ''''''''''''''''''''''''''''''''''
  166. ' Filling in the outstanding quantity to ship
  167.  
  168. oSEC_order = oSS.nSetProgram(oSS.nLookupTask("SO_SalesOrder_ui"))
  169. Set o_order = oScript.NewObject("SO_SalesOrder_bus", oSS)
  170.  
  171. retVal = o_order.nSetKey( orderNumber )
  172. errchk retVal, "Set Order Key", o_order
  173.  
  174. i = 0
  175. r = o_order.oLines.nMoveFirst
  176. 'MsgBox "Before the first line items loop"
  177. do 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
  189. loop
  190.  
  191. r = o_order.DropObject()
  192. o_order = 0
  193.  
  194. ''
  195. ''''''''''''''''''''''''''''''''''
  196.  
  197. ' Creating Shipment business object
  198. oSEC = oSS.nSetProgram(oSS.nLookupTask("SO_Shipping_UI"))
  199. Set oHeader = oScript.NewObject("SO_Shipping_bus", oSS)
  200. fields = oHeader.sGetDataSources
  201. 'MsgBox(fields)
  202.  
  203. nextInvoiceNo = ""
  204. r = oHeader.nGetNextInvoiceNo(nextInvoiceNo)
  205. errchk r, "Getting next invoice number", oHeader
  206.  
  207. if batchOptions = "Y" then
  208. r = oHeader.nSelectBatch(batchId)
  209. errchk r, "Selecting the batch", oHeader
  210. 'MsgBox("Selected")
  211. end if
  212.  
  213. r = oHeader.nSetKeyValue("InvoiceNo$", nextInvoiceNo)
  214. errchk r, "Setting invoice number", oHeader
  215. r = oHeader.nSetKey()
  216. errchk r, "Selecting the invoice", oHeader
  217. r = oHeader.nSetValue("ShipperID$", shipperId)
  218. errchk r, "setting shipper ID", oHeader
  219. r = oHeader.nSetValue("SalesOrderNo$", orderNumber)
  220. 'errchk r, "setting order number", oHeader
  221.  
  222. MsgBox "Document created: " & nextInvoiceNo
  223.  
  224. invoiceNumber=""
  225. lineSequenceNumber=""
  226. lineKey=""
  227.  
  228. ' Re-populating shipment line items
  229. r = oHeader.oLines.nClear
  230. errchk r, "clear lines", oHeader.oLines
  231. r = oHeader.oLines.nCopyLinesFromSalesOrder(orderNumber, "Y")
  232. errchk r, "copy lines", oHeader.oLines
  233.  
  234. ' Fillng in an in-memory list of line items to further navigate
  235. i = 0
  236. r = oHeader.oLines.nMoveFirst
  237. do 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
  250. loop
  251.  
  252. currentIndex=""
  253. index = 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
  259. tableName = "IM_ItemCost_SVC"
  260. bObjectExists = oScript.Evaluate("o_" & tableName) <> 0
  261. if not bObjectExists then
  262. sExpr = "o_" + tableName + "=NEW( """ + tableName + """, %SYS_SS )"
  263. oScript.Execute(sExpr)
  264. end if
  265.  
  266. ' Getting ItemCode Length
  267. Set fo = oSS.oFileObj
  268. itemCodeLength = fo.nGetColumnLength("IM_ItemCost", "ItemCode$")
  269.  
  270. ' Iterating in-memory line items
  271. for 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"
  420. next
  421.  
  422. r = oHeader.nWrite
  423. errchk r, "saving the shipment - " & op, oHeader
  424.  
  425. Wscript.Echo "DONE"
  426.  
  427. ' Done with the Invoice object
  428. r = oHeader.DropObject()
  429. oHeader = 0
  430.  
  431. oss.nCleanup() ' Call Cleanup() before dropping the Session Object
  432. oss.DropObject()
  433. Set oss = Nothing
  434. WScript.Quit
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!