SAP Tips: Script for Mass Text Change – A Useful Solution That Solves Several Issues at Once!
SAP Tips: Script for Mass Text Change
Is the task queue of your ABAP programmer rather long, and you have to wait? Do you want to be able to quickly and easily change parameters and conditions in the script? Do you want flexible custom-tailored SAP Mass change possibility? You need to change sales order item text and there is no remote-enabled SAVE_TEXT or CREATE_TEXT? Do you also want a de-bugger just in case?
Many of you reading this have probably said yes to more than one of these questions. And you probably wish that there was one answer to asolve them all! Surprisingly, the solution for all these issues is offered by a combination of PowerShell, HANA ODBC (part of SAP HANA Database Client installation) and SAP Connector for Microsoft .NET.
The presented script is an automation example for correcting a missing sales order line item text that represents a billing date (used for recurring business) that users overlooked to enter. The text change is done by calling BAPI_SALESORDER_CHANGE. The Get-Credential cmdlet asks users for a username and password, thus avoiding storing them in a script. The data for text creation comes from SQL query, but instead we can use Excel for this purpose.
using namespace SAP.Middleware.Connector
$cred = Get-Credential -Message “SAP Logon Screen”
$Path=”C:\Program Files\sap\SAP_DotNetConnector3_Net40_x64\”
[Reflection.Assembly]::LoadFile($Path + “sapnco.dll”) > $Null
[Reflection.Assembly]::LoadFile($Path + “sapnco_utils.dll”) > $Null
$cfgParams = New-Object RfcConfigParameters
$cfgParams.Add(“NAME”, “QAS”)
$cfgParams.Add(“ASHOST”, “SAPHOST”)
$cfgParams.Add(“SYSNR”, “40”)
$cfgParams.Add(“CLIENT”, “210”)
$cfgParams.Add(“USER”, $cred.GetNetworkCredential().UserName)
$cfgParams.Add(“PASSWD”, $cred.GetNetworkCredential().Password)
$destination = [RfcDestinationManager]::GetDestination($cfgParams)
$bapiCommit = $destination.Repository.CreateFunction(“BAPI_TRANSACTION_COMMIT”)
$bapiCommit.SetValue(“WAIT”,”X”)
$conn = new-object System.Data.Odbc.OdbcConnection
$cred1 = Get-Credential -Message “HANA Logon Screen”
$huser = $cred1.GetNetworkCredential().UserName
$hpwd = $cred1.GetNetworkCredential().Password
$conn.connectionstring = “Driver={HDBODBC};SERVERNODE={hanahost:30044};CLIENT={210};UID={“+$huser+”};PWD={“+$hpwd+”};encrypt={False};sslValidateCertificate=False”
$conn.Open()
$sql=”select vbap.vbeln,vbap.posnr,to_char(to_date(vbrk.fkdat, ‘YYYYMMDD’), ‘DD.MM.YYYY’) as fkdat
from sapabap1.vbak,sapabap1.vbap,sapabap1.vbup,sapabap1.vbrk,sapabap1.vbrp
where vbak.mandt=’210′ and vbap.mandt=vbak.mandt and vbup.mandt=vbak.mandt and vbrk.mandt=vbak.mandt and vbrp.mandt=vbak.mandt
and vbup.gbsta=’C’ and vbrp.vgbel=vbap.vbeln and vbrp.vgpos=vbap.posnr
and vbak.auart=’ZREC’ and vbrk.vbeln=vbrp.vbeln and vbrk.fksto<>’X’ and vbrk.fkart<>’S1′
and vbap.vbeln=vbak.vbeln and vbap.abgru=” and vbup.vbeln=vbak.vbeln and vbup.posnr=vbap.posnr
order by vbap.vbeln,vbap.posnr”
$cmd = New-object System.Data.Odbc.OdbcCommand($sql,$conn)
$dtab = New-Object System.Data.DataTable
$adap = New-Object System.Data.Odbc.OdbcDataAdapter($cmd)
$nrr = $adap.fill($dtab)
Write-Host “Records selected: ${nrr}”
$conn.Close()
foreach ($Row in $dtab.Rows) {
$read_text = $destination.Repository.CreateFunction(“RFC_READ_TEXT”)
$text_lines = $read_text.GetTable(“TEXT_LINES”)
$text_lines.Append()
$text_lines.SetValue(“MANDT”, “210”)
$text_lines.SetValue(“TDOBJECT”, “VBBP”)
$text_lines.SetValue(“TDNAME”, “$($Row.vbeln)$($Row.posnr)”)
$text_lines.SetValue(“TDID”, “Z342”)
$text_lines.SetValue(“TDSPRAS”, “E”)
$read_text.Invoke($destination)
$messages = $read_text.GetTable(“MESSAGES”)
$message = “”
If($messages.Count -gt 0) {
$message = $messages[0].GetString(“MESSAGE”)
}
$billing_date = “”
If($message.Length -eq 0) {
$billing_date = $text_lines[0].GetString(“TDLINE”)
$billing_date = $billing_date.Trim()
}
If($billing_date.Length -eq 0) {
$update = $destination.Repository.CreateFunction(“BAPI_SALESORDER_CHANGE”)
$update.SetValue(“SALESDOCUMENT”, “$($Row.vbeln)”);
$orderHeaderInx = $update.GetStructure(“ORDER_HEADER_INX”)
$orderHeaderInx.SetValue(“UPDATEFLAG”, “U”)
$orderItemInx = $update.GetTable(“ORDER_ITEM_INX”)
$orderItemInx.Append();
$orderItemInx.SetValue(“UPDATEFLAG”, “U”)
$orderItemInx.SetValue(“ITM_NUMBER”, “$($Row.posnr)”)
$orderItemIn = $update.GetTable(“ORDER_ITEM_IN”)
$orderItemIn.Append();
$orderItemIn.SetValue(“ITM_NUMBER”, “$($Row.posnr)”)
$order_text=$update.GetTable(“ORDER_TEXT”)
$order_text.Append()
$order_text.SetValue(“DOC_NUMBER”, “$($Row.vbeln)”)
$order_text.SetValue(“ITM_NUMBER”, “$($Row.posnr)”)
$order_text.SetValue(“TEXT_ID”, “Z342”)
$order_text.SetValue(“LANGU”, “E”)
$order_text.SetValue(“TEXT_LINE”, “$($Row.fkdat)”)
$order_text.SetValue(“FORMAT_COL”, “*”)
$order_text.SetValue(“FUNCTION”, “005”)
$update.Invoke($destination)
$bapiCommit.Invoke($destination)
For ($i=0; $i -lt $ret.RowCount; $i++) {
Write-Host $ret[$i].GetString(“TYPE”)
Write-Host $ret[$i].GetString(“MESSAGE”)
}
}
} [RfcSessionManager]::EndContext($destination) > $Null
Enjoy!
Stay tuned for another SAP Tip from Eursap next soon!
Be sure to also check out Eursap’s SAP Blog for more in depth articles.
Author: Tomislav Milinovic
Get in touch with Eursap – Europe’s Specialist SAP Recruitment Agency