Facing issue with Duplicate data in Proxy-JDBC scenario

[Former Member]

Hello Experts,

This is a Proxy-JDBC scenario.

Three tables are getting updated in a single interface .

Header Information

Details Information

Serial Number

As per the requirement for the Serial number table, if any Material has multiple quantity, multiple number of unique serial numbers will be created. To fulfill this requirement the ABAP code is written in such a way that the quantity field is repeated multiple times. And in PI side we are maintaining only one interface to update the three tables : Header, Details and Serial Number.

InvoiceNumber	CustomerCode	MaterialNumber	Quantity	Date
INV100	XYZ	FFS0-FS1440	3	19-02-2015
INV100	XYZ	FFS0-FS1440	3	19-02-2015
INV100	XYZ	FFS0-FS1440	3	19-02-2015
INV111	XYZ	FFS0-FS1443	2	19-02-2015
INV111	XYZ	FFS0-FS1443	2	19-02-2015

<INVOICE>                                                                      ---------------

       <InvoiceNo>1130033531</InvoiceNo>

       <Date>2011-12-31</Date>

       <CustomerNo>DIST63</CustomerNo>

       <SONo>15218418</SONo>

       <MaterialCode>FFS0-FS1440-35BH</MaterialCode>

       <Quantity>3</Quantity>

       <GrossPrice>300</GrossPrice>

       <SerialNumber>AAF2014111</SerialNumber>

</INVOICE>

<INVOICE>

       <InvoiceNo>1130033531</InvoiceNo>

       <Date>2011-12-31</Date>

       <CustomerNo>DIST63</CustomerNo>                                Duplicate Records only the serial number is different

       <SONo>15218418</SONo>

       <MaterialCode>FFS0-FS1440-35BH</MaterialCode>

       <Quantity>3</Quantity>

       <GrossPrice>300</GrossPrice>

       <SerialNumber>AAF2014112</SerialNumber>

</INVOICE>

<INVOICE>

       <InvoiceNo>1130033531</InvoiceNo>

       <Date>2011-12-31</Date>

       <CustomerNo>DIST63</CustomerNo>

       <SONo>15218418</SONo>

       <MaterialCode>FFS0-FS1440-35BH</MaterialCode>

       <Quantity>3</Quantity>

       <GrossPrice>300</GrossPrice>

       <SerialNumber>AAF2014113</SerialNumber>              ----------------------------------------------

</INVOICE>

<INVOICE>

       <InvoiceNo>1130033537</InvoiceNo>

       <Date>2011-12-31</Date>

       <CustomerNo>DIST63</CustomerNo>

       <SONo>15218418</SONo>

       <MaterialCode>FFS0-FS1440-35BH</MaterialCode>

      <Quantity>2</Quantity>

       <GrossPrice>300</GrossPrice>

       <SerialNumber>ABF2014111</SerialNumber>

</INVOICE>

<INVOICE>

       <InvoiceNo>1130033537</InvoiceNo>

       <Date>2011-12-31</Date>

       <CustomerNo>DIST63</CustomerNo>

       <SONo>15218418</SONo>

       <MaterialCode>FFS0-FS1440-35BH</MaterialCode>

       <Quantity>2</Quantity>

       <GrossPrice>300</GrossPrice>

       <SerialNumber>ABF2014112</SerialNumber>

</INVOICE>

Due to this reason the Header and Detail tables are getting updated as mentioned below:

It should updated as:

Header Table

pi id	sales org	division	vendor name	Quantity	invoice no	date
617984	INDL	EX	SIFY	3	1130033532	19-02-2015
617985	INDL	EX	SIFY	2	1130033531	19-02-2015

Detail Table

det id	invoice no	material code	price	qty
617984	1130033532	FFS0-FS1440	300	3
617985	1130033531	FFS0-FS1443	200	2

Please let me know how to handle with these duplicate records in SAP PI

Regards,

Sayanti

[manoj_khavatkopp · Accepted solution]

hi,

you can apply node functions to remove duplicate records.

pass the final output to your Statement  target structure filed.

so an SQL statement is created only when new record is found.

Br,

Manoj

[Former Member · Accepted solution]

Hello Manoj,

I have changed my mapping as per your suggestion. Mapping is successful in ESR. But whenever I am trying to test the scenario E2E it is failing with mapping error.

Mapping "urn:com:****/OM_PurInvoice_SAPECC_To_PurchaseInvoice" failed to execute: MappingException: Runtime Exception when executing application mapping program com/sap/xi/tf/_MM_Invoice_SAPECC_To_PurchaseInvoice_; Details: com.sap.aii.mappingtool.tf7.IllegalInstanceException; Cannot create target element /ns0:MT_PurchaseInvoice/Statement. Values missing in queue context. Target XSD requires a value for this element, but the target-field mapping does not create one. Check whether the XML instance is valid for the source XSD, and whether the target-field mapping fulfils the requirement of the target XSD.

Please help.

Thanks,

Sayanti

[former_member182412 · Accepted solution]

Hi Sayanti,

Use below mapping. Is only invoice number is not enough to check the duplicates? if yes you can use below.

InvoiceNo->removeContexts->removeDuplicates(UDF)->Statement  in your case.

UDF execution type is "All values of context".

public void removeDuplicates(String[] input, ResultList result, Container container) {
        Set<String> found = new HashSet<String>();
        for (int i = 0; i < input.length; i++) {
            if (found.contains(input[i])) {
                result.addSuppress();
            } else {
                result.addValue(input[i]);
                found.add(input[i]);
            }
        }
    }

Regards,

Praveen.

[former_member191216 · Accepted solution]

Great answer, helped me with same issue. Thank you:)

[manoj_khavatkopp · Accepted solution]

Sayanti,

If its working fine in ESR it should work E2E also.

Its failing in E2E  because thier is an mandatory filed in you target structure which needs to have some value but your soucre field dont have any data, so just as an important point i am telling you whenever you are working with JDBC on receiver side always keep map with default for all target fields, so that it dosnt fail in mapping.

[Former Member · Accepted solution]

Hi Manoj,

I have kept map with default for all target fields.

But still getting the same error.

Thanks,

Sayanti

[manoj_khavatkopp · Accepted solution]

Can you put the screenshot of MONI erro as well as your souce and target structure.

[former_member182412 · Accepted solution]

Hi Sayanti,

Have you tried the mapping which i suggested??

Regards,

Praveen.

[former_member182412 · Accepted solution]

Hi Manoj,

As per your mapping you removing the duplicates at the root node level what about the field level?? you want to apply the same logic for each field under statement?

As per your mapping statement node display queue

Field invoiceNo field display queue.

So the output invoice numbers for two statement nodes will be first two value that is same invoice numbers.

Result:

So easiest solution is to add suppress at the root node level if the duplicate found.

Regards,

Praveen.

[Former Member · Accepted solution]

Hi Manoj,

Please find the screenshots below:

Source Structure:

Target Structure:

Message Mapping:

Error:

Thanks,

Sayanti

[former_member182412 · Accepted solution]

Hi Sayanti,

Use the mapping which i suggested and let me know.

1) change the access occurrence to 1.unbounded and map access instead of statement (if you create statement multiple time you going to execute the statement multiple times on database, if you create one statement with multiple rows then it will only execute on time on database.)

Your problem which you experiencing now is you concatenating all the fields and passing it to target one of the field does not exist then suppress will map it to target (your statement defined as 1 to unbounded that means minimum occurrence is one) and mapping is failing with above error.

Regards,

Praveen.

[manoj_khavatkopp · Accepted solution]

Hi Praveen,

I guess mapping to access don't make any difference , in communication channel if you have tick batch mode chek box then each SQL statement doesn't get executed one by one.

And praveen to be frank i am new to PI , i proposed this solution as i had similar issue earlier which i fixed through node function , no idea about adding suppress in UDF it would be very helpful if you share and docs related to Advanced UDF.

: you told it is working in ESR , so do one thing copy this payload (if you are using ICO get it from ECC MONI) try executing in ESR with that data then you can easily make out where it is failing or else you can go for changing occurrence in target structure for all fields as 0..1.

[Former Member · Accepted solution]

Hi Praveen/ Manoj,

My Issue has been resolved.

Thanks all for the solution.

Thanks,

Sayanti

[Former Member · Accepted solution]

Hi Praveen/ Manoj,

My Issue has been resolved.

Thanks all for the solution.

Thanks,

Sayanti

[former_member182412 · Accepted solution]

Hi Sayanti,

You can close the thread by choosing correct answer.

Regards,

Praveen.