There were freshers in my team who wanted to work on sender IDOC adapter. They were searching SDN for step by step guide on ALE configurations for triggering idocs out of SAP. Hence, thought of bloging this on SDN so that it can act as a guide/checklist for XI consultants.
All the configuration specified below are to be performed in the Sender SAP system
• Create a Logical system for the sender SAP system and the receiving XI system using Transaction - ‘SALE’: 
• Assign Client to the Sender Logical System 
• Create RFC destination of type ‘3’ for SAP XI system using transaction – SM59:
Make sure that the name of the RFC destination and Logical system for SAP XI is the same. 
• Create a Distribution Model through ‘SALE’ transaction: 
• Create a Distribution Model by selecting ‘Create Model View’:
Specify the name of the model view and a description. 
• For the model view created above, add the message type of the idoc that you wish to send to SAP XI using this model view. Select ‘Add Message Type’ option and then specify the sender logical system and the receiver logical system. 
Save your Model view before proceeding further.
• Generate the partner profiles by selecting the navigation path from the main menu, Environment --> Generate Partner profiles
Upon selecting the generate partner profiles option, then execute it with the default parameters displayed on the selection screen 
• From the main screen navigate through the menu option, EDIT --> MODEL VIEW --> DISTRIBUTE. Upon receiving the message shown here, it means the distribution is successful 
• Go to transaction BD10 to send a material out of IDES: 
• Go to transaction WE02/WE05 to check the status of the IDOC in the sendign system 
ALE configuration for pushing idocs from SAP to XI
EAI Primer for XI and Middleware Technologies
Hello everyone!
First, a little about me. I began working on SAP ABAP, but groping with XI and learning the tricks of this trade now. This is my first blog posted in SDN. This blog will give an over all idea about Enterprise Application Integration (EAI), which can be useful for anyone starting on XI (or any other middleware Technologies)
Enterprise Application Integration:
In earlier days companies bought software solutions like SAP, PeopleSoft, Oracle ERP, IDEdwards, Siebel etc. and those software solutions would work very well individually, but create silos of information. In many cases each individual system produced excessive information. When common information changed, we updated the information in each system manually and the process became difficult to handle. Ultimately some of the data across the system became inconsistent. When people noticed those resulting inconsistence in data and double entry data, they decided to get ways to integrate the systems. From that EAI (Enterprise Application Integration) was developed.
EAI allows the flow of information across the different systems. By connecting the different processes ,softwares and hardwares of more enterprises to become one enterprise system. The main motto of the Enterprise Application Integration is to condense the efforts needed for designing and coding. And it allows reuse of configurations or codes for the new system. It reduces the maintenance costs and difficulties of the custemers.
The main objective of the EAI :
In EAI there are two logical integration architectures. Direct point-to-point connects and middleware based integration.
Point-to-point integration
EAI developer follow point-to-point integration because they found it easy to understand and it can implement quickly for few systems to integrate. One example of point-to-point integration is one application makes direct database connection calls to another application’s database tables. Initially when we integrate two application systems the point-to-point integration is right choice. When we integrate additional applications we get a situation shown in below figure.
HCL Technologies
Later stages of a point-to-point integration
In this each application is tightly coupled with the other applications through point-to-point connection. If we change one application it may break the other integrated applications. If we have five applications to be integrated with each other we will need ten different integration points showed in below figure.And each additional application becomes difficult to integrate and maintain.
To avoid those problems we required an intermediate layer to isolate the changes in one application from the other applications.
Middleware-based integration
Middleware provides interfaces with all integrated applications passing messages to each other and each interface defines a business process provided by the application. The below figure shows the service-oriented architecture using middleware.
Middleware-based integration
Service-oriented architecture adds and replace applications without affecting the other applications. If we have ten applications to integrate we will have just ten integration points. It supports a larger number of integrated applications and less maintenance. Middleware can perform complex operations from application to applications.
The additional complexity is setting up the middleware and converting the existing application to use the middleware API.
Major EAI Solution Providers:
EAI can integrate Legacy / Mainframe, Client-Server, Web, e-Commerce, ERP / CRM / HRMS, Custom applications, etc. Various functionalities of EAI are Messaging ,Interfacing, Transformation, Routing and Process Modeling.
HCL Technologies
I hope this sneak peak into EAI was interesting to some of you. If you find any errors or have other input don't hesitate to comment here.
How XML Encryption can be done using web services security in SAP NetWeaver XI
Why XML encryption is important ?
In this blog I would like to discuss on XML encryption in a business scenario that uses web services. Have you ever thought of how a business that uses Web services to conduct online credit card transactions takes measures to ensure that the credit card numbers and other key information does not end up in the wrong hands ? Well it has been possible through the user name/password XML signature which enables you to sign a Web service message to ensure authenticity, data integrity and non-repudiation. This means that only the password will be secured with XML encryption, but not the full message content. So here is the first SAP solution provided by SAP NetWeaver Exchange Infrastructure to enable XML Encryption. Now, customers can ensure authenticity and confidentiality as they can encrypt entire Web service message payloads during the transport. By taking advantage of SAP NetWeaver XI’s mapping capabilities and using them along with adapters, an SAP system can act as a Web services sender or receiver with non-Web services based interfaces. Thus, companies can map to multiple interfaces and route to multiple partners.
An Example on XML Encryption.
Suppose there are two business partners X and Y. Now business partner X wants to consume a web service from business partner Y but he does have an interface for that and also he does not want to develop one. In this case he can use SAP NetWeaver Exchange Infrastructure to access an existing interface without coding a web service client. The interface in this case can be an RFC enabled function module. Instead of calling another application business partner X will execute a call to SAP NetWeaver XI RFC adapter. As soon as the call is executed the RFC message is converted to XML by the RFC adapter and is passed to the integration server. This integration server maps the XML structure of the RFC message with the web service interface document structure. The SOAP adapter is used to encrypt the message leveraging the Web Services Security standard. Both SOAP and SAP NetWeaver XI protocols are supported by this standard. There are two keys : Public and Private. Public key in system Y’s certificate is used by the SOAP adapter of system X to encrypt the message. This message when reaches system Y, it decrypts using its private key which is unique and known only to him. We should remember one thing that both system X and Y should be first configured to enable XML encryption.
Following are few brief steps for configuring XML encryption:
1.You need to deploy IAIK cryptographic library on the J2EE server of the adapter engine. It can also be downloaded from the SAP service marketplace at http://service.sap.com/sw-center .
2.Deploy Java Cryptographic Extension (JCE) to the Java Runtime Environment (JRE). Get the correct version (1.4) when you download JCE policy files. Check that the each file is about 5kb and if it is around 3kb then that means wrong version has been downloaded.
3.Create public and private key certificates for encryption and decryption. For creating a public key certificate the SAP J2EE Visual Administration tool’s Key Storage service is used by the receiver.
4.Configure XML encryption or decryption in SOAP adapter communication channel.
For complete explanation on how to configure SAP NetWeaver XI adapter, and for the Partner Connectivity Kit for Web Services Security, please see the guide ‘‘How To Configure Message Level Security in SAP XI 3.0,’’ available at http://service.sap.com/nw-howtoguides :- Exchange Infrastructure.
XML encryption comes as a part of SAP NetWeaver Exchange Infrastructure after the release of SAP NetWeaver 2004s and Support Package Stack (SPS) 15 of SAP NetWeaver 2004. The sap system supports synchronous scenarios for SAP NetWeaver Exchange Infrastructure SPS 19, or SAP NetWeaver 2004s Process Integration SPS 10 so these directions can be used to encrypt request as well as response messages.
So this was a brief overview of the use of XML encryption in a web service based scenario. I hope through this blog I am able to provide some useful information which might help you in some or the other way.
Guided Procedures Explorations: BPEL Integration – The long answer
I once asked a question in Business Process Modeling Forum regarding the integration of BPEL in Guided Procedures, Mario Herger (Moderator) said “short answer: no”. I never really thought about the reasons behind his answer. I was curious what the long answer might have been but I forgot about it and started exploring other topics.
Recently, I was reading the BPEL4People specification (supported by IBM and SAP) which is an optional extension to BPEL 2.0 that would standardize human tasks in a BPEL process, and I started to think about my old forum post.
I asked myself what might the long answer for my question look like.
A Caveat: I am not a BPEL expert who know the specification (this Blog reflects BPEL 1.1 more than BPEL 2.0) inside and out. So, if this blog contains any errors concerning BPEL, then please yell and scream in the comments. As mentioned in other blogs, my desire is to start others thinking and discussing these issues.
Caveat 2: I am not a SAP “Insider”, so I have no idea what the internal product managers for GP are thinking/doing about this issue. I do, however, have a Java decompiler and have found some “intriguing” Java classes in the trial version of the 2004s WAS. So, I know that somebody at SAP is looking at this stuff.
Motivation
Let’s first look at my motivation for asking the question. I was examining the gap between the work of the BPX at a design level and the technical implementation of this design. ARIS is a process design tool that has a tight integration with SAP, so I decided to use it as my starting point. I knew that ARIS could export BPEL and that there was an interface for Exchange Infrastructure (XI)., so I started looking for information regarding 1) ARIS- Guided Procedure (GP) interaction and 2) BPEL – GP interaction. I didn’t find anything, so I posted my forum question.
Why the answer is “no”
After reading the BPEL4People specification, it became clear why a complete mapping between BPEL and GP is unlikely.
GP ≠ XI
As I mentioned above, XI has the ability to import BPEL. This means that in general SAP has enough technical knowledge regarding BPEL to provide a BPEL import for GP – if this was technically possible. The reason that this import functionality doesn’t exist in GP is that BPEL and GP don’t really match well. Furthermore, XI is not the same product as GP.
If you tried to create a table with the appropriate mappings for BPEL and GP (just a quick attempt), you might end up with a table like this:
BPEL
GP
process
process
sequence
sequential block
flow
parallel block
assign & copy
set parameters for actions
variables
parameter in the process context
Obviously, there are a variety of missing GP elements / mappings. For example:
- Actions are missing. Actions are sort of similar to BPEL “invoke” elements in that the represent the last process element but they are not web-services.
- Callable Objects are missing as well.
- What about names of certain GP elements (Blocks, etc)? These are not included in the BPEL standard.
Thus, from a technical perspective, a possible BPEL – GP mapping would leave much to be desired.
BPEL is a standard
BPEL and BPEL4People both intend to be standards that are product-independent – this means that the technology of a particular product should not be included/promoted at the expense of other products / technologies. GP is part of the SAP NetWeaver and includes special features that take advantage of other SAP products/ technologies (BSP, CAF Core, Web Dynpro, etc.). This is also understandable from the perspective of SAP. If you look at GP, you notice that many of these special features are based on the Callable Object level – the diversity and power of these objects is, in my opinion, what really makes GP stand out. Thus, although a partial integration of BPEL in GP may be possible at the Process, Block and Action level, a standard will probably be unable to include those SAP-specific objects at the Callable Object level.
Effects of the Developmental Process
In general, the relationship between GP and the design environment of the BPX is still largely undefined. It is obvious that processes that involve human interaction should in all likelihood be “implemented” using GP. Does this mean, however, that the BPX can change processes in GP that he designed using an external tool (for example, ARIS)? If this is permitted in GP, there must be round-trip possible from GP to this external tool; otherwise, there will be a discrepancy between the realization in GP and the design in the external tool. Or do these environments represent different design platforms: a theoretical and a technical design? Irregardless, this question is still largely unclear and the answers might even be different based on organizational context.
This discussion has an impact of the use of BPEL in GP, because it impacts both the depth and basic characteristics of the relationship (who has the lead – design tool or GP?) (Or are the tool environments placed on equal footing, meaning that changes in both environments must be reflected in the other?) that must reflected in a potential BPEL<->GP implementation.
Repercussions of the “NO”
Based on the realization that a complete BPEL-GP interaction is unlikely what are the repercussions of this supposition?
- Manual interaction with GP User Interfaces (especially the Designtime Environment) is not going away. Even with a partial integration of BPEL (perhaps to generate the first version of a process with processes, blocks, actions), someone still has to map actions to callable actions as well as configure other aspects of the process environment (attachments, info objects, etc) that are not included in the BPEL specification. Whether this individual is a BPX or a GP technician might be the subject for another blog.
- As the questions raised in this blog reveal, there are still unanswered questions regarding the role of GP in the NetWeaver environment as well as more fundamental questions regarding the transition of process design to process implementation in an Enterprise SOA environment.
Summary
Thus, the long answer is still basically “NO” but at least I now know why.
Richard Hirsch is a SAP Mentor and has 20 years experience in computing. Currently, he is a senior Portal/NetWeaver consultant for Siemens with experience in a variety of related areas (including Web 2.0, Portal Integration, Enterprise SOA, etc.) His opinions are not necessarily those of his employer.
Troubleshooting File-to-IDOC Scenario in XI.
I was just going through the SDN XI Forums and found many questions on troubleshooting of File-To-IDOC scenario in XI. I felt I can share some of my experiences in this regard. Hope this blog helps.
(1) Error scenario - File is not getting picked up from the source
(i) Check if the given source file location path and source file name is same as what is specified in sender communication channel. Then check if the Adapter status is Active in the sender file communication channel.
Using the below URL we can monitor the sender file communication channel status (File Adapter) in Adapter Monitoring.
:/mdt/amtServlet">http://<hostname>:<J2EE Stack Portnumber>/mdt/amtServlet
If the sender communication channel shows processed successfully in adapter monitoring, then check point 4 below.
(ii) Error scenario - File is picked successfully from the given source path but is not getting converted to xml format, The adapter monitoring shows success but no messages in SXMB_MONI.. (This occurs when the file is a fixed length or comma delimited or tab delimited).
The problem could be that file structure lengths and datatype structure lengths in IR are not same. If both structures are same and still if the file is not getting converted into xml format then the problem could be in File Content Conversion (FCC). FCC parameters might not be configured correctly in sender file communication channel.
(2) Error Scenario - Cache is not getting refreshed
Cache needs to be refreshed. To refresh the cache, Tcode SXI_CACHE, XI Runtime Cache -> Start Complete cache refresh.
Check the Cache Notification in IR and ID. Status of the objects should be "Success".
To achieve this,
Go to IR, Environment -> Cache Notification
Status of the objects should be "Success" .
Go to ID, Environment -> Cache Notification
Status of the objects should be "Success" .
Keys:
IR - Integration Repository
ID - Integration Directory
(3) Error Scenario - Message is not appearing in Tcode SXMB_MONI
If there is no message in SXMB_MONI, do the below setting at Tcode SXMB_ADM.
Execute the Integration Engine Configuration
Check if role of business system has been configured to ‘Integration Server’ or not. If it is not ,then configure it to ‘Integration Server’.
(4) If message appeared in Tcode SXMB_MONI, check the XI Pipeline steps.
By default Pipeline steps do not appear in the Tcode SXMB_MONI. To get the Pipeline steps in SXMB_MONI we need to do following external setting in Tcode SXMB_ADM (Usually this setting will be done by an XI technology consultant, but its always better to know the steps)
Category: RUNTIME, Parameter: LOGGING, Current Value: 1
After entering the values, save the settings and test the scenario. Now we can view the pipeline steps in SXMB_MONI for an XML Message.
Using the XI pipeline steps we can find out the source payload and the payload after the mapping conversion. It also explains as to what happens in each pipeline step during runtime.
(5) Error Scenario - ATTRIBUTE_INV_SND_SERV or unable to convert sender service to an ALE logical system name.
The problem occurs when sender and receiver business system or business service does not have a logical system name.
ID - Integration Directory
Posting the IDOC'S into SAP R/3 System, sender and receiver business system or business service requires the logical system name. If the business system (Web As ABAP) does not have the logical system name, then add the logical system for business system. Please see below screen shot.
If business system type = Web AS ABAP, then add the Logical system in Technical System level and Save.
If business system type = Third-Party, then add the logical system in business system level and Save.
If the business service does not have the logical system name in ID, it needs to be configured as follows.
Check the below screen shot which shows how to add the logical system for business service.
Go to ID and click the Business service -> Services -> Adapter Specific Identifiers. Enter the logical system name and Save.
Apply and activate.
(6) Error Category - CO_TXT_OUTBINDING_NOT_FOUND
The problem is in Technical routing.
Check whether the receiver determination and interface determination objects are active in change lists tab. If they are not active, activate the objects.
Also check the if the receiver determination and interface determination objects are referenced to the same namespace or not. Sender and receiver interface objects should always refer to the same Namespace.
(7) Error Scenario - ATTRIBUTE_IDOC_METADATA
Check the receiver communication channel (Created using IDOC adapter). In this communication channel check if the two mandatory parameters are passed correctly.
Following is further info on those two mandatory parameters.
(i)Check if the RFC destination which is created in XI to connect the target SAP R/3 system is working fine.
(ii)Check if the port that has been created in XI is in the form SAP<SID of R/3 system>. If not, create in the form of SAP<SID of R/3 system>. To this port, specify the above RFC Destination.
(8) Error Scenario - IDOC not yet created in target SAP R/3 system.
Go to R/3 system and check at Tcode WE05 if the IDOC has been created successfully. If error is ‘"Partner profile not configured" check point 9.
Suppose IDOC did not get created at R/3 end and all the pipeline steps were successful in SXMB_MONI, then go to Tcode : IDX5 in XI and click on Transaction ID field and check the status description which explains the problem.
(9) Error Scenario - Partner profile not configured.
Go to Tcode WE20 in R/3 system and check if the partner profile has been configured correctly and is filled with the inbound parameters.
XI/PI: Automate&simplify your processes - master data missing
Some business processes that involve integration between many systems require accurate master data (materials, customers, vendors).
If both of your systems are SAP systems then there are at least a few possibilities that facilitate master data distribution:
a) you can send data manually - after creation or changing the master data
b) you can use change pointer technology which (at least in standard) requires scheduling of a report that sends the master data.
c) you can also use MDM (Master Data Management) and try to distribute the data with it
d) you can use your middleware (Exchange Infrastructure) to help you get the master data when necessary
What about consequences of using each of these approaches?
a) when you send the data manually a user may simply forget to do it and he/she will only do it when someone who takes care of the other system
will phone/mail that person - this is probably the worst case as it's a fully manual procedure - so no holidays for users :)
b) when you sue change pointers you need to schedule the report to run as often as possible - which is not always possible
when you try to minimize job scheduling especially during work hours. This means that the change done to master data
will be distributed but only after some time
c) MDM - you need to have MDM - unfortunately not very popular yet
d) when you start building complex business processes in XI (BPMs), which check for master data in destination system
and then fetch it from the source system, then probably the rest of your flows will soon start to suffer - they will slow down
as having too many BPMs is not advisable if you need great performance.
Is there any other solution then?
ALEREQ01 - General request IDOC might be it.
SAP gives you a possibility to fetch master data with special IDOCs. This combined with a little ABAP coding
can become a very powerfull weapon against master data missing errors. All you need to do is to change
the inbound IDOC processing procedure like shown below: 
Basically what you need to do is to check IDOC errors (in a function module exit) and if the erorr will show that master data is missing
you can call XI: either via ABAP proxy or RFC or even with an IDOC. All you need to send it the master data number
(material number for example. Then inside your XI you need to map this to IDOC ALEREQ01 and send to source system
which will send the missing data immediately.
Your IDOC that will be send to the destination system can look just like shown below (DPC1042 is the missing material number): 
What you do get with this approach:
1. No need to build complex BPMs inside XI. You just need use a standard flow (with no BPM) to send the document to target system
and standard flow to send the request to source system. Also the master data distribution from the source system will not require usage of a BPM.
2. You can get the data very quickly (probably faster then if you'd use change pointers scheduled to run after work hours)
3. IDOC will be processed incorrectly in target system but since you can schedule IDOC reprocessing as soon as the master data
will arrive to the destination system the IDOC will be processed correctly automatically
4. No human interaction necessary
What do you think about this approach? Did you use any other approach in your projects?
XI Customer Polling 2006 – Summary of Results
Some of you may have seen the call for action for the XI customer polling end of last year. With this blog, I like to thank all the customers and systems integrators for their participation and share some high level results with you.
Background
19 customers and 4 systems integrators responded to the call for action and submitted a completed questionnaire. Many more showed interest, but after sending out the questionnaire we never received any answers. This is unfortunate, since development had a very close look at the results and is taking the results as input for future planning. Therefore, I wonder if the process with the questions in Excel format was not streamlined enough. Hence, next year, we will be using a web based survey tool, to make the process simpler.
Let’s move on to the summary of the results.
XI as a key enabler for SOA
80% of the respondents see XI as a corner stone for their SOA strategy. It will be leveraged for service enablement as well as for service and process orchestration. Also, 75% within this group will use XI outside of their SAP landscape for these purposes.
XI Project Status and Momentum
Based on the above, it only makes sense that 95% of all customers are expanding the use of XI within their landscape. 
Type of Integration
SAP to non-SAP is the most common type of integration. One quarter of the respondents uses XI for non-SAP to non-SAP integration also.
While A2A integration dominates in scope and volume, B2B type integration is used at 75% of respondents. 
XI and the Competition
70% of the respondents are either already in the process or plan to replace existing integration solutions with XI.
Within our sample customer, XI is used to interoperate with Microsoft, IBM and Tibco integration products. 
Use of Adapters
The most commonly used adapters are the SAP RFC, iDoc and File/FTP adapters. The relative low usage of the JMS adapter in this group of customers is a surprise. Also, EDI AS2 showed up as a frequently used connection type. 
Usage of ccBPM
80% of the polled customers are using ccBPM in production. The average number of steps per integration scenario is between 5 and 10, and the most extensive scenario has a total of 138 steps!
What WS-* standard should XI embrace
WS-security is by far the most important standard and feature set that the customers in this poll want to see XI support. The other end of the answer spectrum was to support them all.
Customer Satisfaction
We were very pleased to see the results for customer satisfaction. The majority of polled customers are either ‘very satisfied’ or ‘mostly satisfied’ with our solution.
Of course, we like to see all customers with high satisfaction rating and we will be using the results from this survey to get there.
THANK YOU FROM SAP
A big thank you to all participants of this customer poll. I hope you will provide feedback again this year and I would also like to encourage other customers to take part in the XI customer poll for 2007.
The XI Customer Polling Team
Anders Ranum
Joe Grazer
Ginger Gatling
Paul Medaille
Swen Conrad
Generic mapping to convert nested XML to flat - Receiver file adatper
In this weblog, I am presenting a generic way to convert any nested complex XML structure to flat using ABAP mapping. This is especially useful for interfaces using receiver file adapter with content conversion.
We know that the receiver file adapter with content conversion can only handle flat XML structure. That is, the resulting XML file should be of structure
<root>
<nameA>
<value1>value</value1>
<value2>value</value2>
<value3>value</value3>
</nameA>
<nameB>
<value4>value</value4>
</nameB>
…
</root>
However, in most cases, we might have to handle complex nested structures in message mapping. This is especially true when the target flat file has complex structure with multiple layouts per line.
For example, consider the below target XML message and flat file structure
Target XML structure (input to receiver file adapter)
<root>
<ORDERS>
<HEADER>
<HDR></HDR>
<FH1></FH1>
<FH2></FH2>
</HEADER>
<DETAIL1>
<DET1></DET1>
<DF11></DF11>
<DF12></DF12>
</DETAIL1>
<DETAIL2>
<DF21></DF21>
<DF22></DF22>
</DETAIL2>
</ORDERS>
</root>
Target flat file layout
HDR,FH1,FH2…
DET1,DF11,DF12
DET2,DF21,DF22
….
HDR,FH1,FH2
DET1,DF11,DF12
DET2,DF21,DF22
The file contains a group of three record types namely, Header, Detail1 and Detail2. Each record type has is own structure and the group repeats itself. For e.g., if this file should contain sales order information, the header contains order header information, Detail 1 contains line item information and Detail 2 contains schedule line information. There could be multiple sales orders as well
We can derive the flat structure in graphical mapping but not without the risk of disturbing the context especially if the source structure is complex nested (for e.g. IDOC). In which case, all HDR lines would be grouped together followed by all DET1 lines, then DET2 line etc.
We had many interfaces with such complex target structures. Instead of repeatedly complicating the graphical mapping, I decided to come up with a generic mapping which will convert any complex structure in to flat structure. This way, the graphical mapping is free of complex context handling functions. I chose to use ABAP mapping to achieve this as described below
Input to the ABAP mapping has two parts in the XML document.
1. One contains the actual target structure, which is complex nested
2. Other part contains the nodes which need to be flattened. This also contains elements for target message name and namespace. The idea of this part is to make the ABAP mapping generic so as to use it with any interface
In our example the input to the ABAP mapping will look like as below
<FLATFILE>
<Z_FILTERS>
<NAME>HEADER</NAME>
<NAME>DETAIL1</NAME>
<NAME>DETAIL2</NAME>
</Z_FILTERS>
<Z_TMSG_TYPE></Z_TMSG_TYPE>
<ORDERS>
<HEADER>
<HDR></HDR>
<FH1></FH1>
<FH2></FH2>
..
</HEADER>
<DETAIL1>
<DET1></DET1>
<DF11></DF11>
<DF12></DF12>
..
</DETAIL1>
<DETAIL2>
<DF21></DF21>
<DF22></DF22>
..
</DETAIL2>
</ORDERS>
</FLATFILE>
The code for the ABAP mapping is provided below.
Method: IF_MAPPING~EXECUTE
In this method we parse the input XML using DOM and read through to get only the nodes which need to flattened out.
Method: GET_LAST_NODE
This method is used to recursively to call itself till reference to the last child node is obtained.
***************************************************
* All data declarations
TYPE-POOLS: IXML.
CLASS CL_IXML DEFINITION LOAD.
DATA: BEGIN OF WA_FILTERS,
NAME TYPE STRING,
END OF WA_FILTERS.
DATA: R_XML_FACTORY TYPE REF TO IF_IXML,
R_STREAM_FACTORY TYPE REF TO IF_IXML_STREAM_FACTORY,
R_STREAM TYPE REF TO IF_IXML_ISTREAM,
R_OSTREAM TYPE REF TO IF_IXML_OSTREAM,
R_IN_DOC TYPE REF TO IF_IXML_DOCUMENT,
R_OUT_DOC TYPE REF TO IF_IXML_DOCUMENT,
R_PARSER TYPE REF TO IF_IXML_PARSER,
R_ENCODING TYPE REF TO IF_IXML_ENCODING,
R_RENDERER TYPE REF TO IF_IXML_RENDERER,
R_ROOT TYPE REF TO IF_IXML_ELEMENT,
R_OUT_ROOT TYPE REF TO IF_IXML_ELEMENT,
R_FILTER_ELEMENT TYPE REF TO IF_IXML_ELEMENT,
R_TMSG_ELEMENT TYPE REF TO IF_IXML_ELEMENT,
R_FILTER_MAIN TYPE REF TO IF_IXML_NODE_FILTER,
R_FILTER TYPE REF TO IF_IXML_NODE_FILTER,
R_ITER TYPE REF TO IF_IXML_NODE_ITERATOR,
R_CHILD TYPE REF TO IF_IXML_NODE,
R_NODE TYPE REF TO IF_IXML_NODE,
R_CLONED_NODE TYPE REF TO IF_IXML_NODE,
T_FILTERS LIKE TABLE OF WA_FILTERS,
V_RC TYPE I,
V_ROOT_NAME TYPE STRING,
V_ROOT_PREFIX TYPE STRING,
V_ROOT_NS TYPE STRING,
V_ROOT_URI TYPE STRING,
V_SOURCE TYPE STRING.
* Create Main factory class
R_XML_FACTORY = CL_IXML=>CREATE( ).
* Create stream fatory
R_STREAM_FACTORY = R_XML_FACTORY->CREATE_STREAM_FACTORY( ).
V_SOURCE = SOURCE.
R_STREAM = R_STREAM_FACTORY->CREATE_ISTREAM_XSTRING( SOURCE ).
* initialize input/output document
R_IN_DOC = R_XML_FACTORY->CREATE_DOCUMENT( ).
R_OUT_DOC = R_XML_FACTORY->CREATE_DOCUMENT( ).
* parse input document
R_PARSER = R_XML_FACTORY->CREATE_PARSER( STREAM_FACTORY = R_STREAM_FACTORY
ISTREAM = R_STREAM
DOCUMENT = R_IN_DOC ).
R_PARSER->PARSE( ).
* Get the encoding
R_ENCODING = R_IN_DOC->GET_ENCODING( ).
* Get the root element
R_ROOT = R_IN_DOC->GET_ROOT_ELEMENT( ).
* Get the filter objects first
R_FILTER_ELEMENT = R_IN_DOC->FIND_FROM_NAME( 'Z_FILTERS' ).
IF NOT R_FILTER_ELEMENT IS INITIAL.
R_CHILD = R_FILTER_ELEMENT->GET_FIRST_CHILD( ).
WHILE NOT R_CHILD IS INITIAL.
CLEAR: WA_FILTERS.
WA_FILTERS-NAME = R_CHILD->GET_VALUE( ).
APPEND WA_FILTERS TO T_FILTERS.
R_CHILD = R_CHILD->GET_NEXT( ).
ENDWHILE.
ENDIF.
** Throw error if no filters are available.
CLEAR: R_FILTER, R_FILTER_MAIN.
LOOP AT T_FILTERS INTO WA_FILTERS.
IF SY-TABIX = 1.
R_FILTER_MAIN = R_IN_DOC->CREATE_FILTER_NAME( NAME = WA_FILTERS-NAME ).
ELSE.
R_FILTER = R_IN_DOC->CREATE_FILTER_NAME( NAME = WA_FILTERS-NAME ).
R_FILTER_MAIN = R_IN_DOC->CREATE_FILTER_OR( FILTER1 = R_FILTER_MAIN
FILTER2 = R_FILTER ).
ENDIF.
ENDLOOP.
* Get the root node to the outbound document.
* Get the target message name from the payload
R_TMSG_ELEMENT = R_IN_DOC->FIND_FROM_NAME( 'Z_TMSG_TYPE' ).
IF NOT R_TMSG_ELEMENT IS INITIAL.
V_ROOT_NAME = R_TMSG_ELEMENT->GET_VALUE( ).
ENDIF.
* V_ROOT_NAME = R_ROOT->GET_NAME( ).
V_ROOT_PREFIX = R_ROOT->GET_NAMESPACE_PREFIX( ).
V_ROOT_URI = R_ROOT->GET_NAMESPACE_URI( ).
R_OUT_ROOT = R_OUT_DOC->CREATE_SIMPLE_ELEMENT_NS( NAME = V_ROOT_NAME
PREFIX = V_ROOT_PREFIX
URI = V_ROOT_URI
PARENT = R_OUT_DOC ).
IF NOT V_ROOT_PREFIX IS INITIAL.
CONCATENATE 'xmlns:' V_ROOT_PREFIX INTO V_ROOT_NS.
V_RC = R_OUT_ROOT->SET_ATTRIBUTE( NAME = V_ROOT_NS
VALUE = V_ROOT_URI ).
ENDIF.
V_RC = R_OUT_DOC->APPEND_CHILD( R_OUT_ROOT ).
CLEAR: R_ITER.
R_ITER = R_IN_DOC->CREATE_ITERATOR_FILTERED( R_FILTER_MAIN ).
R_NODE = R_ITER->GET_NEXT( ).
WHILE NOT R_NODE IS INITIAL.
R_CLONED_NODE = R_NODE->CLONE( ).
R_OUT_ROOT->APPEND_CHILD( R_CLONED_NODE ).
R_NODE = R_ITER->GET_NEXT( ).
ENDWHILE.
* render document ======================================================
* create output stream
CLEAR: R_STREAM.
R_OSTREAM = R_STREAM_FACTORY->CREATE_OSTREAM_XSTRING( RESULT ).
* Set encoding
R_OUT_DOC->SET_ENCODING( ENCODING = R_ENCODING ).
* create renderer
R_RENDERER = R_XML_FACTORY->CREATE_RENDERER( OSTREAM = R_OSTREAM
DOCUMENT = R_OUT_DOC ).
V_RC = R_RENDERER->RENDER( ).
***************************************************
Method: GET_LAST_NODE
This method is used to recursively to call itself till reference to the last child node is obtained.
***************************************************
DATA: CHILD TYPE REF TO IF_IXML_NODE,
NODENAME TYPE STRING.
CHILD = IN_NODE->GET_FIRST_CHILD( ).
IF CHILD IS INITIAL.
NODENAME = CHILD->GET_NAME( ).
OUT_NODE->APPEND_CHILD( IN_NODE ).
ELSE.
WHILE NOT CHILD IS INITIAL.
NODENAME = CHILD->GET_NAME( ).
CALL METHOD ME->GET_LAST_NODE
EXPORTING
IN_NODE = CHILD
CHANGING
OUT_NODE = OUT_NODE.
ENDWHILE.
ENDIF.
***************************************************
The input parameters to method GET_LAST_NODE are as below 
Now, you can use this ABAP mapping as the last step in your mapping sequence to convert any complex structure to flat.
Note
The names of the filters should exactly match the node names which need to flattened out.
The namespace for the target message is assumed the same as the source in the mapping program
RFC calls from Adapter modules...the Web Service Way!
We had run into some setup related issues with the JRA way of invoking RFCs from the adapter modules , explained in my earlier weblog and an OSS was raised for the same. We had to come up with an alternative way for the same, till we received the response. I wish to share the approach we followed with the community here and have experts comment on the same with their views..
Note: I do not intend to compare the approaches here.Also, quite a few URLs are mentioned in this blog which just point to the help pages because those bits and pieces of information are not new to the community and I simply intend to highlight the way we have used all that in the XI context, to suit our requirements.
The following setup is required for this approach.
1. Expose the RFC as a web service - Off course, I need not explain this, it is very precisely documented at many places. Still, those who wish to have a look at the steps, the following tutor files in the Web Service eLearning Catalog would be of great help.
ABAP Web Service Creation Wizard in Web AS 6.40
ABAP Web Service Virtual Interface in Web AS 6.40
ABAP Web Service Definition in Web AS 6.40
ABAP Web Service Configuration in Web AS 6.40
2. Download the WSDL for the above web service from using transaction wsadmin.
3. Generate a web service deployable proxy from this WSDL - Refer help for more details. Note that since the call to the proxy will be made from the adapter module, which itself is a J2EE application , we go for deployable proxy. Check here to know more about the differences between standalone and deployable web service proxies.
4. Before deploying the deployable proxy, make sure to edit the logical port and set the security Authentication type as Basic. Having set this, deploy the EAR for the proxy on J2EE stack of XI.
5. In Visual administrator, specify the credentials for accessing the web service, i.e. the credentials with which you would otherwise access the RFC, with type as Basic Authentication.This has to be done for every logical port in your deployable proxy. Refer Configuring Security on a Deployable Proxy section of help.
6. Create your adapter module in NetWeaver Developer studio, and set references to the web service deployable proxy using the web service perspective. Do not forget to add an application type reference to the web service deployable proxy application in application-j2ee-engine.xml of the adapter module. Also as a best practice, retrieve the reference of the web service deployable proxy inside the create method of the adapter module. You don't need to do that again and again, so not to be done in process method. Precise details here.
7. Deploy the adapter module and you are ready with the total setup with this approach.
The best part with this approach , "as I felt" was
- No hard coding of credentials in the client code or no tweaky design to store the same with the client code is required.
- Access the proxy like your plain Java objects, after you are through with the initial lookup. You do not need to write any thing specific which otherwise is typically required for web service clients. The proxy takes care of the web service communication.
- Along with the credentials, few other properties of the deployable proxy destination can be modified at the runtime. Switching the property selection for the destination from default to Custom in Visual Administrator can do the trick.
- Most of the code, except the RFC(in case it is custom) and the adapter module, is auto generated and is bound to be error free and performance optimized.
Note : If you are stuck up with the JNDI lookup name of the Web service proxy from the client bean code(adapter module in our case, refer help link in step 6), you can ask your Basis team for the JNDI tree dump of the J2EE stack of XI. Visual administrator has a feature to export the tree structure in a file and you can easily refer to the name by which the proxy is bound in JNDI registry, as the pattern is a bit different in case of DC based setup.
Enterprise SOA Explorations: Fragments
This blog contains various ideas / thoughts about Enterprise SOA topics. I might write longer blogs about these topics later but I wanted to get the ideas out in the community to start discussions. You never want to have your ideas die in isolation – it is more satisfying to see your ideas out in the world where they are exposed to others.
Many of these fragments contain more questions than statements but this reflects the fact that I am not sure in which direction the ideas are taking me. These ideas are not fully developed otherwise they would be blogs of their own. If someone wants to take these ideas and create their own blogs, please feel free to do so.
GP (CAF) and XI
I have written numerous blogs about Guided Procedures (GP) and have received some comments about Exchange Infrastructure (XI). I think an analysis of the relationship between the two technologies demands more attention. Of course, XI concentrates more on automated processes (without human interaction) while GP focuses more on processes that contain human interaction. This obvious distinction would be sufficient to fully describe this relationship if processes were either purely automatic or based on only human interaction. As is evident in the real business world, this is rarely the case. Many processes contain parts of each process type. Thus, the technical implementation may include components that are based in XI and some that are based in GP; thus, a new process type – “mixed” processes is born.
Questions arise concerning the impact of this realization on the relationship between the design process and how the resulting processes are implemented. For the BPX designing a process at an abstract level, the technical realization of the process steps is largely irrelevant – indeed, the technical implementation may change over time (for example, when a legacy application is replaced by an service-enabled application). The most important factor is that the domain requirements that are the foundation for the process are fulfilled.
Related questions / points might be:
- In some ways, XI and GP are actually very similar. For example, both have the ability to integrate legacy applications – albeit at different levels. XI has its adapters and GP has its various Callable Objects.
- If a process is largely based on human interaction but still includes a series of service-based, should “automatic” process be wrapped by a single XI web-service? Or should CAF Core be used?(Note: This would probably be an interesting blog – When do I use XI and when do I use CAF Core? XI for existing services and CAF Core for extending services?)
- What do operations-related tasks, such as deployment, monitoring, etc. of such mixed processes look like?
- Who initiates the process XI or GP? Or is this decision based on domain requirements?
- If a service needs to be developed, then there must be some technical role in the project who decides what the optimal development platform is – either XI, CAF Core or something else.
Enterprise SOA and BPM
Whenever I read about Enterprise SOA, I always think “Technically very cool. But what is the business case for moving in this direction.” Without a motivation for moving towards Enterprise SOA, it will never make inroads into the corporate world. It is only when you start looking at how to combine Enterprise SOA with process optimization that this new technology really begins to show its potential.
This does not mean that BPM is purely SOA-based. In the real world, there are many processes that still contain process activities that include legacy applications; thus a process landscape, especially those processes that involve human interaction, that is purely service-based is probably still has a long way to go before becoming reality. Therefore, it is critical to use process tools that are able to use these legacy applications.
Thoughts on the Future of Business Packages
I have a love / hate relationships with Business Packages (Business Packages (BP) are prepackaged software from SAP or Partners that usually focus on a particular industry or organizational role. For more information, see the Portal Content Portfolio. I love the ability to basically install the software, add a few systems in the portal, perform a few customizing changes in the backend and the end-user has an initial set of IViews / Web Dynpros that he/she can use. I hate the problems that occur when the customer requirements don’t exactly match the functionality included in the business package. In the past, there were limited customizing options:
- You could change the associated IView properties
- You could perform back-end customizing (usually via IMGs)
- You could change the Portal PCD objects (for example, removing BP IViews from pages or adding new IViews to pages, worksets provided by the BP)
If the necessary functional requirements were not met by these changes, things got difficult. And these discrepancies are bound to occur, because the functionality in the BPs represented SAP’s attempt to create a standard that met the requirements of as many customers as possible.
However, recent developments, especially as seen in the latest version of the Business Package for Employee Self-Service (mySAP ERP), have given me hope that things are changing for the better. In particular, the XSS now includes the use of GP processes for certain life events (marriage, etc.) The advantage of this change is that organizations can now change the processes to accommodate differences between their own processes and those rolled out by SAP (Of course, the support issues are interesting. If you change a standard process from a BP, what happens to your SAP support…). The main advantage is that BP processes are no longer hidden in some Web Dynpro wizard but can be adapted. Of course, since these Web Dynpro components included in the existing BP-based processes all support the GP Interface, they can be can also be used in new GP processes.
A more radical approach would be to release parts of BPs as Visual Composers models. Then it would be possible to change the UI itself to fit a company’s needs. If you look at XApps, especially XApp Analytics, you can download the associated Visual Composer models and floor plans. The “important note” from SAP regarding these applications is intriguing and might be suggestive of future developments in this area: SAP xApps Analytics will not be maintained or supported by SAP, nor will SAP provide new versions and/or releases of such SAP xApps Analytics.
Ideally, Business Packages might provide similar VC models. This would give BPXs much more freedom to adapt existing BP content to the particular characteristics of the corporate process landscape in which they work. This is critical, because it is often these differences between the existing SAP standard and distinct organizational context that represent the innovative / dynamic heart of the organization that provides it with a competitive advantage in the marketplace.
XML TAGS simplifies Excel Download
In this post I will be explaining how we can utilize starndard xml tags for formatting data in excel.The use of this approach would eliminate the need of external apis. With standard xml approach we could download data into excel, but we could not do any formatting in the excel file. But this approach also provides lots of flexibility in formatting the excel file. The first thing comes into mind when we hear the word formatting is changing font of the text. But this time we would try to do something beyond the traditional way of formatting e.g. merging of cells, adding drop downs, fixing width of a cell etc.
XML Tags Hierarchy
<ss:Workbook>
<ss:Styles>
<ss:Style>
<ss:Alignment/>
<ss:Borders>
<ss:Border/>
</ss:Borders>
<ss:Font/>
<ss:Interior/>
<ss:NumberFormat/>
<ss:Protection/>
</ss:Style>
</ss:Styles>
<ss:Names>
<ss:NamedRange/>
</ss:Names>
<ss:Worksheet>
<ss:Names>
<ss:NamedRange/>
</ss:Names>
<ss:Table>
<ss:Column/>
<ss:Row>
<ss:Cell>
<ss:NamedCell/>
<ss:Data>
<Font/>
<B/>
<I/>
<U/>
<S/>
<Sub/>
<Sup/>
<Span/>
</ss:Data>
<x:PhoneticText/>
<ss:Comment>
<ss:Data>
<Font/>
<B/>
<I/>
<U/>
<S/>
<Sub/>
<Sup/>
<Span/>
</ss:Data>
</ss:Comment>
<o:SmartTags>
<stN:SmartTag/>
</o:SmartTags>
</ss:Cell>
</ss:Row>
</ss:Table>
<c:WorksheetOptions>
<c:DisplayCustomHeaders/>
</c:WorksheetOptions>
<x:WorksheetOptions>
<x:PageSetup>
<x:Layout/>
<x:PageMargins/>
<x:Header/>
<x:Footer/>
</x:PageSetup>
</x:WorksheetOptions>
<x:AutoFilter>
<x:AutoFilterColumn>
<x:AutoFilterCondition/>
<x:AutoFilterAnd>
<x:AutoFilterCondition/>
</x:AutoFilterAnd>
<x:AutoFilterOr>
<x:AutoFilterCondition/>
</x:AutoFilterOr>
</x:AutoFilterColumn>
</x:AutoFilter>
</ss:Worksheet>
<c:ComponentOptions>
<c:Toolbar>
<c:HideOfficeLogo/>
</c:Toolbar>
</c:ComponentOptions>
<o:SmartTagType/>
</ss:Workbook>
All of the formatting needed in the excel sheet are encapsulated in the <ss:Styles></ss:Styles> section or they can be specified individually for each cell within the tag <ss:Cell></ss:Cell>.
Example
XML CODE
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40"> <Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s1">
<Alignment ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="s2">
<Alignment ss:Vertical="Center"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="s3">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="s4">
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font x:Family="Swiss" ss:Bold="1"/>
<Interior ss:Color="#CCFFFF" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s5">
<Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font x:Family="Swiss" ss:Bold="1"/>
<Interior ss:Color="#CCFFFF" ss:Pattern="Solid"/>
</Style>
<Style ss:ID="s6">
<Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
</Style>
<Style ss:ID="s7">
<Alignment ss:Vertical="Bottom" ss:WrapText="1"/>
</Style>
<Style ss:ID="s8">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders>
<Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"/>
<Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"/>
</Borders>
<Font x:Family="Swiss" ss:Size="24" ss:Bold="1"/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="9" x:FullColumns="1"
x:FullRows="1">
<Column ss:AutoFitWidth="0" ss:Width="135"/>
<Column ss:Index="3" ss:StyleID="s7" ss:AutoFitWidth="0" ss:Width="66.75"/>
<Row ss:Height="30">
<Cell ss:MergeAcross="3" ss:StyleID="s8"><Data ss:Type="String">Title</Data></Cell>
</Row>
<Row>
<Cell ss:StyleID="s4"><Data ss:Type="String">Column1</Data></Cell>
<Cell ss:StyleID="s4"><Data ss:Type="String">Column2</Data></Cell>
<Cell ss:StyleID="s5"><Data ss:Type="String">Column3</Data></Cell>
<Cell ss:StyleID="s4"><Data ss:Type="String">Column4</Data></Cell>
</Row>
<Row ss:Height="76.5">
<Cell ss:MergeDown="1" ss:StyleID="s1"><Data ss:Type="String">Row 3 & Row 4 Merged</Data></Cell>
<Cell ss:StyleID="s3"><Data ss:Type="String">Red</Data></Cell>
<Cell ss:StyleID="s6"><Data ss:Type="String">Wrap text when width of the text exceeds the specified width</Data></Cell>
<Cell ss:StyleID="s3"/>
</Row>
<Row>
<Cell ss:Index="2" ss:StyleID="s3"><Data ss:Type="String">Black</Data></Cell>
<Cell ss:StyleID="s6"/>
<Cell ss:StyleID="s3"/>
</Row>
<Row>
<Cell ss:StyleID="s3"><Data ss:Type="String">Row 4</Data></Cell>
<Cell ss:StyleID="s3"><Data ss:Type="String">Black</Data></Cell>
<Cell ss:StyleID="s6"/>
<Cell ss:StyleID="s3"/>
</Row>
<Row>
<Cell ss:StyleID="s3"><Data ss:Type="String">Row 5</Data></Cell>
<Cell ss:StyleID="s3"><Data ss:Type="String">Red</Data></Cell>
<Cell ss:StyleID="s6"/>
<Cell ss:StyleID="s3"/>
</Row>
<Row>
<Cell ss:StyleID="s3"><Data ss:Type="String">Row 6</Data></Cell>
<Cell ss:StyleID="s3"><Data ss:Type="String">Black</Data></Cell>
<Cell ss:StyleID="s6"/>
<Cell ss:StyleID="s3"/>
</Row>
<Row>
<Cell ss:MergeDown="1" ss:StyleID="s2"><Data ss:Type="String">Row 8 & 9 Merged</Data></Cell>
<Cell ss:StyleID="s3"><Data ss:Type="String">Green</Data></Cell>
<Cell ss:StyleID="s6"/>
<Cell ss:StyleID="s3"/>
</Row>
<Row>
<Cell ss:Index="2" ss:StyleID="s3"><Data ss:Type="String">Red</Data></Cell>
<Cell ss:StyleID="s6"/>
<Cell ss:StyleID="s3"/>
</Row>
</Table> <DataValidation xmlns="urn:schemas-microsoft-com:office:excel">
<Range>R2C2:R1000C2</Range>
<Type>List</Type>
<CellRangeList/>
<Value>"Red, Black, Green"</Value>
</DataValidation>
</Worksheet></Workbook>
Save this code into an .xml file and open with excel to see whether your code is correct.Now all you have to do is to generate a xml string programmatically and download into .xml file.
Create fancy Excel Files with SAP PI
There are several blogs how to create Excel Files in SAP.
Some of them handle the SpreadsheetML standard which is a good approach to create enhanced Excel files with formatting, multiple sheets etc. Compared to OLE, issues like performance or no background processing do not occur here.
A short overview over Spreadsheet ML and its advantages:
SpreadsheetML is a XML Standard defined by Microsoft and is available from Office 2003 on. Each Excel File can be converted to SpreadsheetML. Such Files can be named either .xls or .xml and are automatically opened with Microsoft Excel. Users normally will notice that they are opening a XML file.
A big advantage compared to OLE is that developers do not understand the meaning of each element in the XML structure. Instead, the Excel file that needs to be produced is converted to SpreadSheetML and from that we know which fields need to be filled with which value. Therefore, there is not too much try and error until we get the desired result.
The Schema provided by Microsoft is quite complicated, but on the other hand only a view elements are mandatory to create an Excel File with formatting:
Existing Blogs and Examples in the SDN either create the XML manually in a character field using concatenate (not very convenient) and others suggest using Transformations to convert ABAP Structures into the SpreadSheetML Format.
Now, let’s come to the advantage of using PI.
As we have a schema from Microsoft and SpreadSheetML is a XML standard, the idea came to me that I could import the schema in the PI Enterprise Service Repository, create a Service Proxy from the XSD.
And what advantages do we get from that?
- The complete structure of the XML is created automatically as DDIC structure
- Developers just need to look at the converted SpreadSheetML and fill the same fields and internal tables in ABAP
- The conversion from ABAP to XML is done automatically by the Proxy Runtime
- We can use the built-in functionality of PI to send the Excel File by email, ftp etc.
Normally, I would not describe how to import a XSD files and create Proxies from it, but as there are some traps during this procedure, I will note them here
Step-by-Step Guide
Step 1) Make sure some OSS notes are installed
During Proxy Generation I came on several bugs in the SAP proxy runtime which SAP has fixed with the following notes:
0001472956 xsd:sequence with minOccurs=0 is ignored
0001485074 WSDL error: attribute .. defined more than once
0001487627 Syntax error in Stylesheet
Step 2) Get the XSD-Package from Microsoft
You can get the original files from here.
However, certain xsd language element are not supported by SAP. I therefore did some minor modification to the XSD.
You can download the modified files from here.
Step 3) Import the XSD in the Enterprise Service Builder
Place all 13 xsd from the package in one directory, use the “Import External Definition” Wizard to import the file "excelss.xsd".
Make sure to keep “Import References” checked.
Once done, all 13 xsd should be imported automatically in one step.
Step 4) Create an asyncronous outbound interface
As message Type select "Workbook" from the excelss.xsd external Definition.
Step 5) Create the Proxy in SPROXY
Before activating, you need to change one structure because SAP generates duplicate field names.
Open the external view and naviage to the Table Element under Worksheet. You will find 4 duplicates. Just change the ABAP Name of the duplicates to any other name.
The location of those duplicates is shown in below screenshot:
After that you should be able to activate the proxy.
In below box, there is a sample report that will generate a simple Excel File with a little bit formatting.
In order to run this report in your system you need to change the prefix that you have choose during Proxy generation from ZTMP to the value that you have choosen as prefix. Additionally, the name of the service interface need to be changed (here: ZTMPCO_SI3_GENERIC_EXCEL_OUTB / SI3_GENERIC_EXCEL_OUTB_ASY
***************************************************
*&---------------------------------------------------------------------*
*& Report ZPROXY_TEST_EXCEL
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT ZPROXY_TEST_EXCEL.
DATA: ref_app TYPE REF TO cx_ai_application_fault,
ref_proxy TYPE REF TO ZTMPCO_SI3_GENERIC_EXCEL_OUTB,
ref_sys TYPE REF TO cx_ai_system_fault,
l_ack_request TYPE PRX_ACK_REQUEST_DETAILS,
l_async_messaging TYPE REF TO if_wsprotocol_async_messaging.
data: ls_workbook type ZTMPWORKBOOK.
data: ls_worksheet type ZTMPWORKSHEET_TYPE.
TRY.
CREATE OBJECT ref_proxy.
CATCH cx_ai_system_fault INTO ref_sys.
* Hier Ausnahmebehandlung
ENDTRY.
* ask for transport ack
TRY.
l_async_messaging ?= ref_proxy->get_protocol( if_wsprotocol=>async_messaging ).
l_ack_request = if_wsprotocol_async_messaging=>co_transport_acknowledgment.
l_async_messaging->set_acknowledgment_requested( l_ack_request ).
CATCH cx_ai_system_fault INTO ref_sys.
ENDTRY.
************** START ***************** FILL EXCEL STRUCTURE***********************************
perform generate_stlyes changing ls_workbook-styles-style.
perform fill_row_tab changing ls_worksheet-table-row.
ls_worksheet-name = 'myWorksheet'.
append ls_worksheet to ls_workbook-worksheet.
ls_worksheet-name = 'anotherWorksheet'.
append ls_worksheet to ls_workbook-worksheet.
************** END ***************** FILL EXCEL STRUCTURE***********************************
* Execute Proxy
TRY.
CALL METHOD ref_proxy->SI3_GENERIC_EXCEL_OUTB_ASYNC
EXPORTING
output = ls_workbook.
COMMIT WORK AND WAIT.
CATCH cx_ai_system_fault INTO ref_sys.
* Exception Handling
* ev_errmsg = ref_sys->errortext.
CATCH cx_ai_application_fault INTO ref_app.
ENDTRY.
*&---------------------------------------------------------------------*
*& Form FILL_ROW_TAB
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* <--P_LS_WORKSHEET_TABLE_ROW text
*----------------------------------------------------------------------*
FORM FILL_ROW_TAB CHANGING P_ROW_TAB type ZTMPSI3_GENERIC_EXCEL_OUT_TAB4.
data: ls_row type ZTMPSI3_GENERIC_EXCEL_OUTB_A16.
data: ls_cell type ZTMPCELL.
data: lt_cell_tab type ZTMPCELL_TAB.
* empty rows
append ls_row to p_row_tab.
append ls_row to p_row_tab.
append ls_row to p_row_tab.
* header row
ls_cell-Merge_Across = '1'.
ls_cell-Style_ID = 'rahmen'.
ls_cell-data-type = 'String'.
ls_cell-data-content = 'Bestandsübersicht'.
append ls_cell to lt_cell_tab.
ls_row-cell = lt_cell_tab.
append ls_row to p_row_tab.
clear: ls_cell, lt_cell_tab.
* data row
ls_cell-data-type = 'String'.
ls_cell-data-content = 'Früchte'.
append ls_cell to lt_cell_tab.
ls_cell-data-content = 'Anzahl'.
ls_cell-data-type = 'String'.
append ls_cell to lt_cell_tab.
ls_row-cell = lt_cell_tab.
append ls_row to p_row_tab.
clear lt_cell_tab.
ls_cell-data-type = 'String'.
ls_cell-data-content = 'Äpfel'.
append ls_cell to lt_cell_tab.
ls_cell-data-content = '7'.
ls_cell-data-type = 'Number'.
append ls_cell to lt_cell_tab.
ls_row-cell = lt_cell_tab.
append ls_row to p_row_tab.
clear lt_cell_tab.
ls_cell-data-type = 'String'.
ls_cell-data-content = 'Birnen'.
append ls_cell to lt_cell_tab.
ls_cell-data-content = '9'.
ls_cell-data-type = 'Number'.
append ls_cell to lt_cell_tab.
ls_row-cell = lt_cell_tab.
append ls_row to p_row_tab.
clear lt_cell_tab.
* summary row
ls_cell-Style_ID = 'gruen_fett'.
ls_cell-data-type = 'String'.
ls_cell-data-content = 'Summe'.
append ls_cell to lt_cell_tab.
ls_cell-data-content = '16'.
ls_cell-data-type = 'Number'.
append ls_cell to lt_cell_tab.
ls_row-cell = lt_cell_tab.
append ls_row to p_row_tab.
clear lt_cell_tab.
ENDFORM. " FILL_ROW_TAB
*&---------------------------------------------------------------------*
*& Form GENERATE_STLYES
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* <--P_LS_WORKSHEET_STYLES_STYLE text
*----------------------------------------------------------------------*
FORM GENERATE_STLYES CHANGING P_style_tab type ZTMPSTYLE_TYPE_TAB.
data: ls_style type ZTMPSTYLE_TYPE,
ls_border type ZTMPBORDER_TYPE.
* Style 1: Border around cell
ls_style-ID = 'rahmen'.
ls_border-Position = 'Bottom'.
ls_border-Line_Style = 'Continuous'.
ls_border-Weight = '2'.
append ls_border to ls_style-borders-border.
ls_border-Position = 'Top'.
append ls_border to ls_style-borders-border.
ls_border-Position = 'Left'.
append ls_border to ls_style-borders-border.
ls_border-Position = 'Right'.
append ls_border to ls_style-borders-border.
append ls_style to p_style_tab.
clear ls_style.
* Style 2: Bold Text with green background
ls_style-ID = 'gruen_fett'.
ls_style-Font-Family = 'Swiss'.
ls_style-Font-Bold = '1'.
ls_style-Interior-Color = '#339966'.
ls_style-Interior-Pattern = 'Solid'.
append ls_style to p_style_tab.
ENDFORM. " GENERATE_STLYES
***************************************************
Step 6) XSL-Mapping (optional)
At the beginning of the SpreadSheetML file, you will see following processing instruction:
<?mso-application progid="Excel.Sheet"?>
This instruction ensures, that the file can be saved as .xml but is still shown as Excel File under Windows and is not opened with a XML Editor.
Without this processing instruction, you also can save the file as .xls and it can be opened as regular Excel file.
If you want to include this processing instruction, you should create a simple XSLT-Mapping in PI that will add this processing instruction.
