JDBC-->RFC-->IDOC

[Former Member]

Hello Friends,  I am working on a JDBC-->RFC--->IDOC scenario where we have two database tables one of them contains the project ID and another table contains the milestones correspond to a particular Project ID.Now I need to select the data of two tables with a single communication channel and I want to get the Project ID,corresponding milestones for the project ID.  The project ID will call an RFC and will return some data that I need to send to ECC by an IDOC.  The milestones correspond to a particular project ID also need to send to a single idoc with the RFC return data.  What I am doing is :  I have created two message type for two different Database table.  One of them will take Project ID as input and will call an RFC.So I did an RFC lookup and linked the return of the RFC ,directly to the corresponding fields in the IDOC.  on the other had,I need to get all the milestones corresponding to a particular Project ID, and need to send to the same IDOC.  So, here are my questions :  1.Could you please help,how I can select the data from two different table with a single query?  2.Do I need a BPM in this scenario for a loop on the Project ID, and to generate 1 IDOC for each project ID and it's corresponding milestones?  3.When I choose two different message type in the source structure,the target structure put an header with Message and message 1 over the idoc structure,is it normal? do you think,I need to delete the header on the idoc to post it successfully in ECC?  If you think of any other thing,I need to add in my scenario or have any doubt to understand the scenario please do let me know !  Thanks & regards, Sambaran

[ambrish_mishra]

Hi Sambaran,

You cannot possibly fetch data from 2 tables with the same comm. channel. If you want to poll 2 tables separately and combine the 2 messages in PI based on Project ID,(n:1 mapping), you would need a BPM which is an overkill for this scenario.

You should ideally use a Stored procedure for your requirement if you want to fetch data from 2 different tables. This will allow you to have the data required in a single JDBC call to the database.

You won't need a BPM for creating IDocs based on unique Project IDs. this can be handled in graphical mapping.

You need to explain your design further as to why you are making the RFC call and how you would want to create the IDoc(s).

Hope it helps!

Ambrish

[Bhargavakrishna]

Hi Sambaran Pradhan,

1.Could you please help,how I can select the data from two different table with a single query?  

http://databases.about.com/od/sql/l/aajoins2.htm

http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/viewer.htm#a001361224.htm

2.Do I need a BPM in this scenario for a loop on the Project ID, and to generate 1 IDOC for each project ID and it's corresponding milestones?

No need of BPM.

3.When I choose two different message type in the source structure,the target structure put an header with Message and message 1 over the idoc structure,is it normal? do you think,I need to delete the header on the idoc to post it successfully in ECC? 

Agree with baskar reply..

Regards

Bhargava krishna

[Former Member]

Hi, Thanks for your input 🙂  I am facing a problem with my JDBC lookup.I taking the internal project id and external project id as input and taking several fields as output but when I am trying to generate several idocs from the output fields,all the data segments of the 2nd record or 3 rd record are getting attached with the first idoc data segment  could you please suggest a solution to this?  Thanks!

[baskar_gopalakrishnan2]

Question 1:

Two database tables... Is it two different databases or just two tables in the same database? If it is two different databases, you have to use two different communication channels. If two different tables in the same database, you can use select query with join and handle this.

Question 2) If it is two different database tables.. You can achieve this without bpm like this.

First jdbc sender for selecting the project Id  then use a) rfc lookup against project Id in the mapping and then do the second lookup for jdbc in the same mapping for milestone with the rfc return data and map all the values in the idoc receiver structure.  This way you can avoid bpm.  I dont go bpm with jdbc all together in the same scenario.

Queston 3: You can avoid using two different structure. Like the way I explained. just use select query for the projectId in the sender message structure and stay with just one messg structure.

Hope this helps.