API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 19th, 2018
85
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 7.82 KB | None | 0 0
raw download clone embed print report
  1. ' ProcessData Function
  2. '
  3. Function Run_VB_Script_Function()
  4.  
  5. ' CHANGE HISTORY
  6. '
  7. ' 10-May-2018 - RYP - CW400473 - Altered log messages for readability
  8. '
  9. '
  10. '** This function loops from start to end for each DocEntry value passed to it by the RecordSource
  11.  
  12. Dim oCmpSrv
  13. Dim oPickList
  14. Dim oPickListLines
  15. Dim lRet
  16. Dim lErrCode
  17. Dim sErrMsg
  18. Dim errCode
  19. Dim errDesc
  20. Dim transstate
  21. Dim i
  22. Dim j
  23. Dim k
  24. Dim l
  25. Dim Aux
  26. Dim Cn
  27.  
  28. On Error Resume Next
  29.  
  30.  
  31. '*** Initial connection to SBO. Runs only once for the entire step (regardless of looping)
  32. If CStr(ThisStep.RowsProcessed)="0" Then
  33. 'Setup connection To SBO
  34. Set oCmpSrv = CreateObject("SAPbobsCOM.Company")
  35. oCmpSrv.Server = "192.168.21.252:30015"
  36. oCmpSrv.CompanyDB = "SBOFFSCRM"
  37. oCmpSrv.UserName = "TCUser" 'Changed 12/06/2018 RYP CW#414283
  38. oCmpSrv.Password = "8fZtPSUB" 'Changed 12/06/2018 RYP CW#414283
  39. oCmpSrv.Language = 3
  40. oCmpSrv.UseTrusted = False
  41. oCmpSrv.DbServerType = 9
  42. oCmpSrv.DbUserName = "SYSTEM"
  43. oCmpSrv.DbPassword = "FFf0r3v3r"
  44. oCmpSrv.LicenseServer = "192.168.21.252:40000"
  45.  
  46. lRet = oCmpSrv.Connect
  47. If lRet <> 0 Then
  48.  
  49. lErrCode = oCmpSrv.GetLastErrorCode
  50. sErrMsg = oCmpSrv.GetLastErrorDescription
  51.  
  52. ThisStep.LogError sErrMsg
  53.  
  54. 'Throw exception
  55. Err.Raise CLng(lErrCode), CStr(sErrMsg)
  56.  
  57. Else
  58. Variables("sboCompany") = oCmpSrv
  59. End If
  60.  
  61. ThisStep.LogInfo "DI API connection to SAP Business One server " & oCmpSrv.Server & " and schema " & oCmpSrv.CompanyDB & " successfully established."
  62. Else
  63. oCmpSrv = Variables("sboCompany")
  64. oPickList = Variables("picklistObject")
  65. oPickListLines = Variables("oPickListLines")
  66. End If
  67.  
  68. If Not CInt(Variables("LastDocentry")) = CInt(ThisStep.RecordSource("Id")) Then
  69. Variables("LastDocentry") = CInt(ThisStep.RecordSource("Id"))
  70. Variables("DifLinenum") = CInt(ThisStep.RecordSource("LineNum"))
  71. Variables("currentBinCode")=""
  72. Variables("TotalLinesUsed") = 0
  73.  
  74. oCmpSrv.StartTransaction()
  75.  
  76. Set oPickList = oCmpSrv.GetBusinessObject(156)
  77. oPickListLines = ""
  78.  
  79. oPickList.Remarks = ThisStep.RecordSource("Remarks")
  80. oPickList.PickDate = ThisStep.RecordSource("CurrentDate")
  81. oPickList.UserFields.Fields.Item("U_CBTC").Value = "Y"
  82.  
  83. If Len(ThisStep.RecordSource("PickerName"))>0 Then
  84. oPickList.Name=ThisStep.RecordSource("PickerName")
  85. Else
  86. oPickList.Name=System.Variables("Default_Picker_U_NAME")
  87. End If
  88. If CStr(ThisStep.RecordSource("PickerCode"))<>CStr("-1") Then
  89. oPickList.OwnerCode=ThisStep.RecordSource("PickerCode")
  90. Else
  91. oPickList.OwnerCode=System.Variables("Default_Picker_USERID")
  92. End If
  93.  
  94. End If
  95.  
  96. lRet = 0
  97. If CInt(ThisStep.RecordSource("RowNum")) > 0 Then
  98. If Not CInt(Variables("DifLinenum")) = CInt(ThisStep.RecordSource("LineNum")) Then
  99. oPickList.Lines.Add()
  100. ThisStep.LogInfo "Line added to picklist"
  101. lRet = 1
  102. End If
  103. Else
  104. lRet = 1
  105. End If
  106.  
  107. Variables("TotalLinesUsed") = Variables("TotalLinesUsed") + 1
  108.  
  109. If lRet = 1 Then
  110. oPickList.Lines.SetCurrentLine(oPickList.Lines.Count-1)
  111. oPickList.Lines.BaseObjectType = ThisStep.RecordSource("ObjType")
  112. oPickList.Lines.OrderEntry = ThisStep.RecordSource("DocEntry")
  113. oPickList.Lines.OrderRowID = ThisStep.RecordSource("LineNum")
  114. oPickList.Lines.ReleasedQuantity = ThisStep.RecordSource("Quantity")
  115. End If
  116.  
  117. 'We need to store each line information in a string. Finally, when it changes to another pick list or it is the last one that pick list will be created using a sap transaction
  118. oPickListLines = oPickListLines & CStr(oPickList.Lines.Count - 1) & "#" & CStr(ThisStep.RecordSource("bincode")) & "#" & CStr(ThisStep.RecordSource("BinPicked")) & ";"
  119.  
  120. If CInt(ThisStep.RecordSource("TotalLines")) = CInt(Variables("TotalLinesUsed")) Then
  121.  
  122. ThisStep.LogInfo "Final Bin Quantities: " & oPickListLines & " (PicklistLines-1 # BinCode # BinPicked)"
  123.  
  124. 'In SAP to create a pick list we do need to execute the 'ADD' method and after that the 'UpdateReleasedAllocation'
  125. On Error Resume Next
  126. retVal = oPickList.Add()
  127.  
  128. If retVal <> 0 Then
  129.  
  130. errDesc =oCmpSrv.GetLastErrorDescription
  131. ThisStep.LogWarning "Error detected when adding picklist for Docentry: " & CStr(ThisStep.RecordSource("DocEntry")) & ". Error Detail: "& errDesc
  132. oCmpSrv.EndTransaction(1)
  133.  
  134. Set Cn = CreateObject("ADODB.Connection")
  135. Cn.Open "Provider=SQLOLEDB.1;Server=BEIGE\MSSQLSERVERR2;Database=SAPHANA;User Id=sa;Password=logix2020"
  136. Cn.Execute = "exec UpdateInsertEmails 0,"& ThisStep.RecordSource("DocEntry") & ",'" & errDesc & "'"
  137. Cn.Close()
  138.  
  139. Else
  140.  
  141. oPickListLines = Left(oPickListLines,Len(oPickListLines)-1)
  142.  
  143. Set oPickList = oCmpSrv.GetBusinessObject(156)
  144. oPickList.GetByKey(oCmpSrv.GetNewObjectKey())
  145.  
  146. i = Split(oPickListLines ,";")
  147. For Each j in i
  148. k = Split(CStr(j) ,"#")
  149. Aux = 0
  150. For Each l in k
  151. Select Case Aux
  152. Case 0
  153. oPickList.Lines.SetCurrentLine(CInt(l))
  154. If Not oPickList.Lines.BinAllocations.BinAbsEntry = 0 Then
  155. oPickList.Lines.BinAllocations.Add()
  156. ThisStep.LogInfo "BIN ADDED"
  157. End If
  158. oPickList.Lines.BinAllocations.SetCurrentLine(oPickList.Lines.BinAllocations.Count-1)
  159. Case 1
  160. oPickList.Lines.BinAllocations.BinAbsEntry = CInt(l)
  161. oPickList.Lines.BinAllocations.SerialAndBatchNumbersBaseLine = -1
  162. Case 2
  163. oPickList.Lines.BinAllocations.Quantity = l
  164. End Select
  165. Aux = Aux + 1
  166. Next
  167. Next
  168. On Error Resume Next
  169. retVal = oPickList.UpdateReleasedAllocation()
  170.  
  171. Set Cn = CreateObject("ADODB.Connection")
  172. Cn.Open "Provider=SQLOLEDB.1;Server=BEIGE\MSSQLSERVERR2;Database=SAPHANATEST;User Id=sa;Password=logix2020"
  173.  
  174. If retVal <> 0 Then
  175. errDesc =oCmpSrv.GetLastErrorDescription
  176. ThisStep.LogWarning "Error detected when updating released allocation on picklist for Docentry: " & CStr(ThisStep.RecordSource("DocEntry")) & ". Error Detail: "& errDesc
  177. oCmpSrv.EndTransaction(1)
  178. Cn.Execute = "exec UpdateInsertEmails 0,"& ThisStep.RecordSource("DocEntry") & ",'" & errDesc & "'"
  179. Else
  180.  
  181. Dim Counter
  182. Dim AnywithDecimal
  183. AnywithDecimal=False
  184. Dim LineInfo
  185.  
  186. For Counter=0 To oPickList.Lines.Count-1
  187. oPickList.Lines.SetCurrentLine(Counter)
  188. ThisStep.LogInfo "Checking line " & Counter & " for DocEntry: " & ThisStep.RecordSource("DocEntry") & ". RelQty = " & oPickList.Lines.ReleasedQuantity & "(" & CInt(oPickList.Lines.ReleasedQuantity) & ")."
  189. 'LogInfo message changed for clarity RYP 10/025/2018
  190. If oPickList.Lines.ReleasedQuantity <> CInt(oPickList.Lines.ReleasedQuantity) Then
  191. AnywithDecimal = True
  192. LineInfo ="PICKLIST WITH DECIMAL VALUES. Line: " + CStr(oPickList.Lines.LineNumber)+ ". Released quantity: " + CStr(oPickList.Lines.ReleasedQuantity)
  193. ThisStep.LogInfo "Decimal values detected in line " & CStr(oPickList.Lines.LineNumber) & " of DocEntry: " & ThisStep.RecordSource("DocEntry") & ". RelQty= " & oPickList.Lines.ReleasedQuantity & "(" & CStr(oPickList.Lines.ReleasedQuantity) & ""
  194. End If
  195. Next
  196.  
  197. ThisStep.LogInfo "Picklist added and updated: " & CStr(oCmpSrv.GetNewObjectKey())
  198.  
  199. If AnywithDecimal Then
  200. ThisStep.LogInfo "Decimal values detected in DocEntry: " & ThisStep.RecordSource("DocEntry") & ", LineInfo: " + LineInfo + "'"
  201. 'Uncomment this line to insert the info in the error table when there is a decimal value. But allowing the pick list to be created.
  202. Cn.Execute = "exec UpdateInsertEmailsDecimals 0," & ThisStep.RecordSource("DocEntry") & ",'" + LineInfo + "'"
  203. End If
  204.  
  205. oCmpSrv.EndTransaction(0)
  206.  
  207. End If
  208.  
  209. Cn.Close()
  210.  
  211. End If
  212.  
  213. oPickListLines = ""
  214.  
  215. Else
  216. ThisStep.LogInfo "Partial Bin Quantities: " & oPickListLines & " (PicklistLines-1 # BinCode # BinPicked)"
  217. End If
  218.  
  219. Variables("picklistObject") = oPickList
  220. Variables("oPickListLines") = oPickListLines
  221. Variables("DifLinenum") = ThisStep.RecordSource("LineNum")
  222.  
  223. End Function
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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!