Extracting data from SAP and updating local Oracle DB

, ,
1

Hello,

We have currently implementing wM 8.0 at our company - we have just completed all necessary training and are now starting some proof of concept projects. I find documentation is quite complicated and difficult to really figure out something really easy…but regarding extracting data from SAP and updating local Oracle DB - please can someone advise me on a few things.

  1. We have designed an SAP RFC that extracts all purchase order header and items with the delivery addresses into tabes (parameterss)
  2. I have created a SAP adapters that executes the RFC and the data comes through fine.
  • this means that my connection to SAP is being established correctly.
  1. I created a canonical document - made up of the PO_header, PO_items and PO_address SAP table structures.
  2. I create a wrapper flow service using the created sap adapter service in (1) to pipe the data into my canonical document.
  3. Now I need to insert these records into my local wM Oracle database - so
  4. I created a JDBC insert adapter service

Now I cannot understand clearly what I need to do to insert the data into my Oracle Db…

I have design the tables in Toad - generated the scripts and use sqldeveloper to create the entities in Oracle

In my JDBC adapter I am seeing all my tables for the PO user (PO database) in which I am wanting to insert data into…

I also created a dictionary but

Please advise…

Thank you very much for your help…
VR

2

Hello,

I believe you have created a SAP document to catch the data from SAP as per your company structure.

Now, you need to create a output canonical (ie oracle structure).
2. Map the data as per your mapping requirement to the output canonical format.
3.Once you have the output canonical mapped, call the jdbc adapter service to insert into oracle.

HTH.
Sasanka

3

Dear Sasanka,

Thank you for the reply,

So, what I was doing - was mapping the my SAP document (which I call my canonical) directly to the input fields of the JDBC (insert) adapter service.

Will change to your solution and let you know soon…

Kind regards,
Vikash

4

Hi Sasanka,

Thanks again for your help - I implemented (2) and (3) just for one of the three tables that brings through data from SAP (the PO_HEADER).

My problem now is that I get only the first record inserted into my local oracle database.

Here’s additional info.
SAP is returning data through my RFC call (via the SAP adapter) in tables.
It appears in my document as a list.

I’m thinking about looping through each record in my PO_TAB() and invoking the insert adapter - tried many different ways - but failed :confused:

Any ideas - please…

Thanks
Vikash

5

You can either use batch insert or loop on the document list and use the adapter service which you have already created.

6

Hi,

Thanks for the reply, I’ve been trying with your option 2 - looping on document list but was not getting the desired results. However I finally figured that my input array of my loop was incorrect - changed it and I’m now getting all the records of the SAP PO_HEADER into my local database. :slight_smile:

I can’t believe how quick it took to download over 8000 records from our SAP DB to wM DB.

Guys thanks for your help…

Vikash

7

Hi Guys,

Have another challenge - extracting PO_Items table from SAP now - which has a two date fields. I defined the date fields in my local Oracle DB as a timestamp(8). Data from SAP comes through my document in yyyy-mm-dd format but when trying to insert into my oracle db - I get error - [ADA.1.314] Cannot set data for the input field “PO_CHANGE_DATE”
Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff].

I’m thinking of inserting a map step before calling my jdbc insert adapter to convert the date fields in my document. It just seems like an inefficient way…

Any ideas - please?

Thanks
V.

8

Invoke pub.date:dateTimeFormat service to convert the incoming date format to target format, and insert… There is nothing like inefficiency here…

Senthil

9

Dear Senthil,

Thanks for your reply. I am receiving the date fields from SAP as text (yyyy-MM-dd) and initially had the type of the date field in my wM db as Timestamp. Changed the type from Timestamp to Date and now there’s no need for any date transmformation when parsing the date - however oracle the dates are stored as dd/MMM/yy (29/Apr/11). I’m happy with this for now…

However, my current situation is -

  1. I have three document types to catch three data returned in different
    table exports via a SAP RFC.
    doctypeCatchSapResults/doclistTab1
    doctypeCatchSapResults/doclistTab2
    doctypeCatchSapResults/doclistTab3

  2. I have three different JDBC insert adapter service invoked from three
    different flow services inserting the data to three different wM db
    tables by looping through the respective doclistTab in each flow
    service.

  3. Because each flow service will invoke the SAP RFC call separately to
    export the same data - I decided to consolidate it all into one service.

My problem is that the inserts slows down drastically…

I map each of the SapResults/tables to an outputCanonical/doclistTab1;2 and 3 - all in one map statement - its fine upto this point.

Then I have three loop steps over outputCanonical/doclistTab1;2 and 3 - each step invoking its respective adapter service.
On the very first loop it just slows down…but on its own service it’s fine.

Any ideas.

Thanks a lot.
Vikash

10

Hi guys,

Apologies for the huge explanation - anyways - got it all working quite nicely - looks very good and was a great learning experience.

Thanks guys.