How to extract attachments for SAP ECC & SAP S/4HANA Plant Maintenance and Customer Service documents into Alteryx
The SAP Document Read tool can extract attachments in any of the SAP supported MIME type formats including texts, images, audio, video and applications such as PDF, MS Word, MS Excel, MS Powerpoint etc.
In this blog we will show you how to extract attachments for Plant Maintenance and Customer Service documents stored in SAP ECC or SAP S/4HANA. For example you can extract the attachments for Plant Maintenance Notifications (IW21 / IW22 / IW23), Plant Maintenance Orders / Service Orders (IW31 / IW32 / IW33), Service Notifications (IW51 / IW52 / IW53), Functional Locations (IL01 / IL02 / IL03), Equipment (IE01 / IE02 / IE03) and so on.
The principles used here can be applied to any of the other functional areas in SAP including Sales & Distribution, Purchasing, Inventory Management and Financial Accounting.
The remainder of this blog will be split into 2 parts.
In the first part, we will identify the source of the data we want to extract using the SAP Document Read tool.
In the second part, we will look at how to build an Alteryx workflow that will extract the required data using the Alteryx Connector for SAP and the optional SAP Document Read tool add-on.
If you would like to follow the steps described in this blog you will need:
We will use the Plant Maintenance Notification (IW21 / IW22 / IW23) to demonstate the approach.
In the example below, Notification Number, "10000000" has been selected and displayed with transaction IW23.
The selected Notification is of type, "M2" for a Malfunction Report. We will use this later when building the Alteryx workflow.
Select the Attachment List for the Notification. The selected Notification has 10 attachments. The Alteryx workflow will extract all 10 attachments for the Notification.
We now need to identify the SAP table name for the Plant Maintenance Notification.
SAP transactions typically have multiple tables associated with them. For the purposes of this example we are only interested in the document header table for the Plant Maintenance Notification. There are various resources available on the web covering this topic but if you have access to SAP then you can usually find the correct table by clicking on a field (we've selected the Notification Number field) and pressing F1. This displays a Field Help pop-up window from which you can select Technical Information.
The Technical Information for the Notification Number field is shown below. The Technical Information also shows the table associated with the selected field: VIQMEL (Notification Header). We will need to access table VIQMEL in the Alteryx workflow.
Let's take a look at table VIQMEL using the Data Browser (SE16 / SE16N) transaction. The header data associated with the Plant Maintenance Notification is highlighted below (partial extract). The attachment data is not directly associated with the Plant Maintenance Notification tables.
We need to establish the link between the Plant Maintenance Notification and its attachments. The link for Plant Maintenance Notifications and for all SAP transaction and master data objects is stored in table, SRGBTBREL. Field INSTID_A below represents the Plant Maintenance Notifcation number. Field INSTID_B is the technical name of the attachment. Note that there are 10 entries, one for each attachment linked to the Plant Maintenance Notification.
We have now established the source for the Plant Maintenance Notification transaction data and the associated attachment data.
We have published a sample workflow, "Extract attachments for Plant Maintenance and Customer Service documents stored in SAP ECC & SAP S/4HANA" to Alteryx Gallery. Click here to download it.
From Alteryx Designer select Open Workflow then Alteryx Gallery.
In the Alteryx Gallery window, search for "DVW". Then select, "Extract attachments for Plant Maintenance and Customer Service documents stored in SAP ECC & SAP S/4HANA" from the list of DVW Analytics workflows.
Click, Open to install the workflow. Note: You will need the Alteryx Connector for SAP and the SAP Document Read tool installed to use the workflow.
Let's take a look at how the workflow is constructed.
The SAP system to be accessed is selected on the Parameters tab of the SAP Logon tool. Details of the selected SAP system along with the credentials of the logged in user are passed to the other tools in the workflow.
Notification Header table, VIQMEL is selected on the Parameters tab of the SAP Table Data tool. The Notification Number and Notification Table fields are selected. Note, the "Show selected items only?" checkbox is checked so only the selected fields are displayed.
For this worfklow we are interested in extracting the attachments for all Plant Maintenance Notifications of type, "M2" (Malfunction Report).
Right-click on the Notification Type field and create a filter for Notification Type, "M2".
Check the Filters and Variables view on the Parameters tab of the SAP Table Data tool. Note, that is possible to directly edit the filter from this view.
When the workflow is executed, the SAP Table Data tool Ouput shows the VIQMEL table entries that match the selection criteria. This Ouput will be passed onto the next tool in the workflow.
We now need to convert the Output from table, VIQMEL into a format that can be used to dynamically select the corresponding entries in table, SRGBTBREL.
The Alteryx Formula tool is used for this. In the example below, we are using the Notification Number (QNUM) output from table VIQMEL and mapping it to SRGBTBREL field, INSTID_A.
When the workflow is executed, the Alteryx Formula tool Ouput shows the VIQMEL table entries that match the selection criteria. The "Name", "Selection", "Operator", "LowKey", "HighKey" and "Split" values from this Output will be passed onto the next tool in the workflow.
Table SRGBTBREL is selected on the Parameters tab of the SAP Table Data tool. Fields INSTID_A and INSTID_B are selected for the Output.
The values in this table will be selected dynamically using the Output from the Alteryx Formula tool in the previous step. The Dynamic Input tab on the SAP Table Data tool ensures that the correct fields from the Output of the Alteryx Formula tool are mapped.
When the workflow is executed, the SAP Table Data tool Ouput shows the SRGBTBREL table entries that match the dynamic selection criteria. The "INSTID_B" field value will be passed onto the next tool in the workflow and will be used to extract the attachments using the SAP Document Read tool.
Note, that in this example only 1 of the Plant Maintenance Notification documents originally selected ("10000000") has been found to have attachments. There are 10 attachments associated with the same Plant Maintenance Notification.
The SAP Document Read tool Configuration tab uses a simple dynamic input. The INSTID_B field value from the previous workflow step maps to the DOCUMENT_ID field.
When the workflow is executed, the SAP Table Data tool generates two Ouputs.
The first Output from the Document Data out port will be used to generate the file names for the attachments.
The second Output from the Contents Hex out port holds the hexadecimal content for each of the attachments. Note that there are multiple lines for a single Attachment Document ID.
An Alteryx Formula tool is used to create the File Name for each extracted attachment.
When the workflow is executed, the Alteryx Formula tool creates the File Name for each attachment.
The Alteryx Summarize tool is used to concatenate multiple Lines for each Document ID into a single output per Document ID.
After running the workflow, the 5000 plus records Input into the Alteryx Summarize tool...
... are Output as 10 records.
The Alteryx Blob tool is used to convert the HEX encoded Binary Data input from the Alteryx Summarize tool into a Blob Field.
When the workflow is executed, the Alteryx Blob Convert tool generates the Blob Field for each attachment.
The Alteryx Join tool combines the attachment File Name with the attachment Blob Field.
When the workflow is executed, the Alteryx Join tool generates the combined File Name and Blob Field ouput.
The Alteryx Blob Output tool is used to write the attachments to disk. Before running the workflow specify a dummy file name. This will be overwritten with the name created for each attachment when the workflow is executed.
When the workflow is executed the extracted attachment files are written to the directory specified in the Blob Output tool.