SAP PI Jdbc receiver adapter: How to insert header/deatil records

[former_member250191]

I have to design a SAP PI integration workflow SFTP to JDBC.

I receive a transaction with a header record and detail record (e.g. invoice). This transaction must be inserted into a database with a header and a detail table where the detail records refer to a technical header keyId.

I think this is a typical use case so that i can image that there is a simple solution for my probem.

Can somebody help me ?

[suman_saha]

Hi,

The requirement is not very clear to me.

Database can hold flat linear data i.e. no header and item level differentiation can be done.

You can use function use one as many function to repeat header information in each row with same header but different item level records.

Regards,

Suman

[former_member250191]

the database structure is like

header table (e.g. invoice header):

technical primKey	invoiceNo	invoice date	Header 4
100	100-20.20020	1.1.2015

detail table (e.g. invoice lines):

technical primKey	referenece to header table	article	quantity
100	100	A1	54
101	100	A2	4
102	100	A3	12

The technical key is a field which is set by the database. The field "reference to header table" is now my problem. After inserting the header I need to know which ID has been given by the database to my new record.

[iaki_vila]

Hi Christian,

In my opinion you have two options:

1. To create a stored procedure to do all the inserts and to control inside the ID.

2. Do two scenarios. The sender system must control the the first scenario response to use the ID with the second scenario.

Regards.

[former_member184720]

Third option -

I believe you always have only one Header record.

Insert the header record from the mapping using the below approach

Then do a JDBC lookup to retrieve the technical primary key and pass the same to your item table.

to make it simple

Source -> Mapping( Insert into Header using a UDF) + ( JDBC lookup to retrieve TPK) -> Target (Item Table)