Message mapping

[Former Member]

Hi

I am working on a mapping scenario, where I will have Header and Detail messages in the source. I have to collect all the detail records and produce a target structure with one transaction per invoice number with all detail records of that invoice number. I have tried with user defined functions, split by value, creatIf functions but not successful. It looks like a very std scenario think I am missing something, anyone please point the right steps in sequence, that would be helpful

<b>Source Structure</b>

<b><HEADER></b>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>123</b></INVOICE_NO>

<SALES_PERSON_CODE/>

<b></HEADER></b>

<b><HEADER></b>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>456</b></INVOICE_NO>

<SALES_PERSON_CODE/>

<b></HEADER></b>

<b><DETAIL></b>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>123</b></INVOICE_NO>

<ITEM_CODE><b>abc</b></ITEM_CODE>

<QTY_SIGN/>

<QUANTITY/>

<UNIT_PRICE/>

<PROCDSIGN/>

<NET_SALE_AMOUNT/>

<b></DETAIL></b>

<b><DETAIL></b> <SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>123</b></INVOICE_NO>

<ITEM_CODE><b>xyz</b></ITEM_CODE>

<QTY_SIGN/>

<QUANTITY/>

<UNIT_PRICE/>

<PROCDSIGN/>

<NET_SALE_AMOUNT/>

<b></DETAIL></b>

<b><DETAIL></b>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>456</b></INVOICE_NO>

<ITEM_CODE><b>lmn</b></ITEM_CODE>

<QTY_SIGN/>

<QUANTITY/>

<UNIT_PRICE/>

<PROCDSIGN/>

<NET_SALE_AMOUNT/>

<b></DETAIL></b>

<b><DETAIL></b>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>456</b></INVOICE_NO>

<ITEM_CODE><b>opr</b></ITEM_CODE>

<QTY_SIGN/>

<QUANTITY/>

<UNIT_PRICE/>

<PROCDSIGN/>

<NET_SALE_AMOUNT/>

<b></DETAIL></b>

<b>Target Structure</b>

<b><Transaction></b>

<b><HEADER></b> <SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>123</b></INVOICE_NO>

<SALES_PERSON_CODE/>

<b></HEADER></b>

<b><DETAIL></b>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>123</b></INVOICE_NO>

<ITEM_CODE><b>abc</b></ITEM_CODE>

<QTY_SIGN/>

<QUANTITY/>

<UNIT_PRICE/>

<PROCDSIGN/>

<NET_SALE_AMOUNT/>

<b></DETAIL></b>

<b><DETAIL></b>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>123</b></INVOICE_NO>

<ITEM_CODE><b>xyz</b></ITEM_CODE>

<QTY_SIGN/>

<QUANTITY/>

<UNIT_PRICE/>

<PROCDSIGN/>

<NET_SALE_AMOUNT/>

<b></DETAIL></b>

<b></Transaction></b>

<b><Transaction></b>

<b><HEADER></b> <SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>456</b></INVOICE_NO>

<SALES_PERSON_CODE/>

<b></HEADER></b>

<b><DETAIL></b> <SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>456</b></INVOICE_NO>

<ITEM_CODE><b>lmn</b></ITEM_CODE>

<QTY_SIGN/>

<QUANTITY/>

<UNIT_PRICE/>

<PROCDSIGN/>

<NET_SALE_AMOUNT/>

<b></DETAIL></b>

<b><DETAIL></b>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>456</b></INVOICE_NO>

<ITEM_CODE><b>opr</b></ITEM_CODE>

<QTY_SIGN/>

<QUANTITY/>

<UNIT_PRICE/>

<PROCDSIGN/>

<NET_SALE_AMOUNT/>

<b></DETAIL></b>

<b></Transaction></b>

[Former Member · Accepted solution]

Hi Stefan

Thanks for your input it works, but a slight problem

please have a look at the source structure below, I have given detail recods in reverse sequence, it is mapping all occurances of first detail record to first instance.. second to second and so on, Since we are not making any collection based on the invoice number.

Source Structure

<HEADER>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>123</b></INVOICE_NO>

<SALES_PERSON_CODE/>

</HEADER>

<HEADER>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>456</b></INVOICE_NO>

<SALES_PERSON_CODE/>

</HEADER>

<DETAIL>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>456</b></INVOICE_NO>

<ITEM_CODE>xyz</ITEM_CODE>

<QTY_SIGN/>

<QUANTITY/>

<UNIT_PRICE/>

<PROCDSIGN/>

<NET_SALE_AMOUNT/>

</DETAIL>

<DETAIL> <SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>123</b></INVOICE_NO>

<ITEM_CODE>abc</ITEM_CODE>

<QTY_SIGN/>

<QUANTITY/>

<UNIT_PRICE/>

<PROCDSIGN/>

<NET_SALE_AMOUNT/>

</DETAIL>

Target Structure

<Transaction>

<HEADER> <SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>123</b></INVOICE_NO>

<SALES_PERSON_CODE/>

</HEADER>

<DETAIL>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>456</b></INVOICE_NO>

<ITEM_CODE>xyz</ITEM_CODE>

<QTY_SIGN/>

<QUANTITY/>

<UNIT_PRICE/>

<PROCDSIGN/>

<NET_SALE_AMOUNT/>

</DETAIL>

</Transaction>

<Transaction>

<HEADER>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>456</b></INVOICE_NO>

<SALES_PERSON_CODE/>

</HEADER>

<DETAIL>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO><b>123</b></INVOICE_NO>

<ITEM_CODE>abc</ITEM_CODE>

<QTY_SIGN/>

<QUANTITY/>

<UNIT_PRICE/>

<PROCDSIGN/>

<NET_SALE_AMOUNT/>

</DETAIL>

</Transaction>

[stefan_grube · Accepted solution]

You can use the sort function like follows:

HEADER - Transaction

Constant() - HEADER
 
INVOICE_NO - removeContexts - sort - SplitByValue(Each value) - INVOICE_NO

INVOICE_NO - removeContexts 
                            sortByKey - SplitByValue(Each value) - SALES_DATE 
SALES_DATE - removeContexts/

The same for all other fields beyond HEADER

INVOICE_NO - removeContexts - sort - SplitByValue(value changed) - DETAIL

INVOICE_NO - removeContexts - sort - SplitByValue(Each value) - INVOICE_NO

INVOICE_NO - removeContexts 
                            sortByKey - SplitByValue(Each value) - SALES_DATE 
SALES_DATE - removeContexts/

The same for all other fields beyond DETAIL

Use sort and sortByKey always with the option comparator=numerical

Stefan

[Former Member]

Hi Stefan

Thanks very much it worked.

Regards

Prahllad

[Former Member]

Hi Claus

Thanks for your input, I have to use information from Header also. The detailed scenario is I am getting two separate files Header and Detail. Two sender adapters to pull the file and I am merging them using a BPM.The invoice no is the key. In the transformation step of the BPM I am having this logic of message mapping to produce a target structure with Header + all Detail records. Is there any way I can do this in BPM collection process with container variables instead of message mapping?

[claus_wallacher]

Hi Prahllad,

assuming that in your source message all information you need is captured in the detail section, the solution is quite easy. Just take the solution from Stefan, but in the mapping

INVOICE_NO -> removeContext -> SplitByValue[Value changed] -> Details

make sure you use the INVOICE_NO out of the Detail section (based on your description it seems like you used the one from the header section). Also for all other information make sure you use the data from the detail section.

If you also need to use information from your header section, the solution becomes much more complicated, since you need to start sorting your data. It is possible, but - as I said - much more complex.

Regards,

Claus

[Former Member]

Hi Stefan

This is what I am getting when I do

Header --> Transaction

constant --> Header

Detail --> removeContext > SplitByValue(Value changed)Detail

Invoice no --> removeContext >SplitByValue (value changed)invoice no

Itemcode --> removeContext > splitByValue(value changed) Itemcode

It is putting all the detail records under first transaction and creating second transaction only with Header.

I have also tried with user defined function with add the result set based on the invoice number, but in that case I could able to correctly move detail records matching the invoice numbers but second transaction it created only with Header.Any help?

Target Structure

<Transaction>

<HEADER>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO>123</INVOICE_NO>

<SALES_PERSON_CODE/>

</HEADER>

<DETAIL>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO>123</INVOICE_NO>

<ITEM_CODE>abc</ITEM_CODE>

<QTY_SIGN/>

<QUANTITY/>

<UNIT_PRICE/>

<PROCDSIGN/>

<NET_SALE_AMOUNT/>

</DETAIL>

<DETAIL>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO>123</INVOICE_NO>

<ITEM_CODE>xyz</ITEM_CODE>

<QTY_SIGN/>

<QUANTITY/>

<UNIT_PRICE/>

<PROCDSIGN/>

<NET_SALE_AMOUNT/>

</DETAIL>

<DETAIL>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO>456</INVOICE_NO>

<ITEM_CODE>lmn</ITEM_CODE>

<QTY_SIGN/>

<QUANTITY/>

<UNIT_PRICE/>

<PROCDSIGN/>

<NET_SALE_AMOUNT/>

</DETAIL>

<DETAIL>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO>456</INVOICE_NO>

<ITEM_CODE>opr</ITEM_CODE>

<QTY_SIGN/>

<QUANTITY/>

<UNIT_PRICE/>

<PROCDSIGN/>

<NET_SALE_AMOUNT/>

</DETAIL>

</Transaction>

<Transaction>

<HEADER>

<SALES_DATE/>

<STORE_CODE/>

<INVOICE_NO>456</INVOICE_NO>

<SALES_PERSON_CODE/>

</HEADER>

</Transaction>

[stefan_grube]

Oh, I see, Prasad und me had different proposals.

Try mine:

<b>invoice_no</b> - removeContexts - SplitByValue(value changed) - <b>details</b>

All other elements that I did not mention do just one by one:

Itemcode --> Itemcode

Regards

Stefan

[stefan_grube]

Maybe you think too complicated

header - transaction
constant - header
invoice_no - removeContexts - SplitByValue(value changed) - details

Regards

Stefan

[former_member181955]

Hi,

You please try as follow.

Header ---> Transaction

Detail -> removeContext()->splitByValue()-->Detail

Thanks

Prasad Babu Nemalikanti

[stefan_grube]

You were faster