Automate SAP Ariba Procurement Integration Tasks from PowerShell

The CData Cmdlets for SAP Ariba Procurement are standard PowerShell cmdlets that make it easy to accomplish data cleansing, normalization, backup, and other integration tasks by enabling real-time access to SAP Ariba Procurement.

PowerShell Cmdlets or ADO.NET Provider?

The Cmdlets are not only a PowerShell interface to SAP Ariba Procurement, but also an SQL interface; this tutorial shows how to use both to retrieve SAP Ariba Procurement data. We also show examples of the ADO.NET equivalent, which is possible with the CData ADO.NET Provider for SAP Ariba Procurement. To access SAP Ariba Procurement data from other .NET applications, like LINQPad, use the CData ADO.NET Provider for SAP Ariba Procurement.

Once you have acquired the necessary connection properties, accessing SAP Ariba Procurement data in PowerShell can be enabled in three steps.

In order to connect with SAP Ariba Procurement, set the following:

• ANID: Your Ariba Network ID.
• ANID: Specify which API you would like the provider to retrieve SAP Ariba data from. Select the Buyer or Supplier API based on your business role (possible values are PurchaseOrdersBuyerAPIV1 or PurchaseOrdersSupplierAPIV1).
• Environment: Indicate whether you are connecting to a test or production environment (possible values are TEST or PRODUCTION).

Authenticating with OAuth

After setting connection properties, you need to configure OAuth connectivity to authenticate.

• Set AuthScheme to OAuthClient.
• Register an application with the service to obtain the APIKey, OAuthClientId and OAuthClientSecret.

  For more information on creating an OAuth application, refer to the Help documentation.

Automatic OAuth

After setting the following, you are ready to connect:

APIKey: The Application key in your app settings. OAuthClientId: The OAuth Client Id in your app settings. OAuthClientSecret: The OAuth Secret in your app settings.

When you connect, the provider automatically completes the OAuth process:

1. The provider obtains an access token from SAP Ariba and uses it to request data.
2. The provider refreshes the access token automatically when it expires.
3. The OAuth values are saved in memory relative to the location specified in OAuthSettingsLocation.

PowerShell

1. Install the module:

   Install-Module SAPAribaProcurementCmdlets

2. Connect:

   $saparibaprocurement = Connect-SAPAribaProcurement -ANID "$ANID" -API "$API" -APIKey "$APIKey" -AuthScheme "$AuthScheme"

3. Search for and retrieve data:

   $orderstatus = "CHANGED" $orders = Select-SAPAribaProcurement -Connection $saparibaprocurement -Table "Orders" -Where "OrderStatus = `'$OrderStatus`'" $orders

   You can also use the Invoke-SAPAribaProcurement cmdlet to execute SQL commands:

   $orders = Invoke-SAPAribaProcurement -Connection $saparibaprocurement -Query 'SELECT * FROM Orders WHERE OrderStatus = @OrderStatus' -Params @{'@OrderStatus'='CHANGED'}

ADO.NET

1. Load the provider's assembly:

   [Reflection.Assembly]::LoadFile("C:\Program Files\CData\CData ADO.NET Provider for SAP Ariba Procurement\lib\System.Data.CData.SAPAribaProcurement.dll")

2. Connect to SAP Ariba Procurement:

   $conn= New-Object System.Data.CData.SAPAribaProcurement.SAPAribaProcurementConnection("ANID=AN02000000280;API=PurchaseOrdersBuyerAPI-V1;APIKey=wWVLn7WTAXrIRMAzZ6VnuEj7Ekot5jnU;AuthScheme=OAuthClient;InitiateOAuth=GETANDREFRESH") $conn.Open()

3. Instantiate the SAPAribaProcurementDataAdapter, execute an SQL query, and output the results:

   $sql="SELECT DocumentNumber, Revision from Orders" $da= New-Object System.Data.CData.SAPAribaProcurement.SAPAribaProcurementDataAdapter($sql, $conn) $dt= New-Object System.Data.DataTable $da.Fill($dt) $dt.Rows | foreach { Write-Host $_.documentnumber $_.revision }