Aggregation logic to take several lines into account in SAP PO Mapping

[former_member367131]

Hi All,

I have below requirement in SAP PO, i have given source and expected file below,

Could you please suggest correct UDF for the below requirement,

IF AKONT is empty THEN

Do not aggregate(even though same HKONT, which i highlighted in bold in below file)

ELSE

For lines that have same HKONT and same XBLNR

Sum amounts on WRBTR and DMBTR

Source file ex:

HEADER XBLNR BSCHL HKONT / LIFNR / KUNNR AKONT WRBTR DMBTR

N 15172 01 5000044372 A123 1250000.00 1250000.00

15172 50 R702000000 1250000.00 1250000.00

N 15172 40 X970100000 125000.00 125000.00

15172 50 L487100000 125000.00 125000.00

N 15172 01 5000044372 A123 200288.00 200288.00

15172 50 L445710M02 200288.00 200288.00

N 15172 01 5000044372 200000.00 200000.00

15172 50 L445710M02 200000.00 200000.00

Expected output:

HEADER XBLNR BSCHL HKONT / LIFNR / KUNNR AKONT WRBTR DMBTR

N 15172 1 5000044372 A123 1450288 1450288

15172 50 R702000000 1250000 1250000

N 15172 40 X970100000 125000 125000

15172 50 L487100000 125000 125000

15172 50 L445710M02 200288 200288

N 15172 01 5000044372 200000 200000

15172 50 L445710M02 200000200000

Thanks in Advance,

Venkat

[peter_wallner2]

Hello Venkat,

Do you have the files as TXT? Or do you have them in XML format? If you have it in XML I suggest to do a XSLT map. If it is really a flat TXT(CSV) file I also think Java is the way to go.

If you have the source file in XML please provide a sample.

Best regards, Peter

[peter_wallner2]

Hello Venkat,

your file structure is not clear to me. If I put it into EXCEL it looks like this:

WRBTR and DMBTR are empty - but you write you want to sum up the amounts of those fields?
I think you want to sum up AKONT? Correct?

Best regards, Peter

[former_member367131]

Hi Peter,

File is in text format only,

i have given source and expected out put file in my query(given space as delimiter)

Thanks,

Venkatram

[peter_wallner2]

Hello Venkat,

I came up with this code. You can try it locally with Eclipse. Try it with the file I attached called "sumUp.txt". To use this in a UDF you can basically use the code from line 13 to 47 I would say. I am not very familiar with UDFs:

import java.io.*;
import java.util.*;

public class ReadFileAndSumUpValue {

    public static void main(String[] args) throws IOException {

        List<String> toSumUp = new ArrayList<>(); //list storing lines to be summed up
        Collection<String> finalList = new ArrayList<>(); //final list of all lines

        double akont = 0.00;

        try(BufferedReader br = new BufferedReader(new FileReader("sumUp.txtsumup.txt"))){

            String line;
            boolean add = false;
            String header = br.readLine(); //read first line and ignore
            while((line = br.readLine())!= null){

                //put current line into an array
                String [] array = line.split(" ");
                //if it has less than 7 elements AKONT is empty and ...
                if(array.length < 7){
                    finalList.add(line);//... we add it to our final list
                }else{
                    //get the AKONT value and add it up to "akont"
                    akont +=Double.parseDouble(array[6]);
                    //add the line to the sum up list
                    toSumUp.add(line);
                }
            }
            br.close();

            //now we just take the first line of the sum up list
            String temp = toSumUp.get(1);
            //split it into an array
            String [] array = temp.split(" ");
            //we replace the current AKONT value with our summed up value "akont":
            array[6] = Double.toString(akont);
            //we replace the current KUNNR value with our summed up value "akont":
            array[5] = Double.toString(akont);
            temp = "";//reset temp
            //rebuild temp from the temp String
            for(int i = 0; i<array.length;i++){
                temp += array[i] + " ";
            }
            finalList.add(temp);

            //now print both out
            System.out.println("final file:");
            System.out.println("\t" + header);
            for(String entry : finalList){
                System.out.println("\t" + entry);
            }

            System.out.println("sumup file:");
            for(String entry : toSumUp){
                System.out.println("\t" + entry);
            }


        } catch (FileNotFoundException e) {
            //e.printStackTrace();
            System.out.println("file not found " + e.getMessage());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

Best regards, Peter

[former_member367131]

Hi Peter,

Thanks for your response,

i am trying to implement this, will let you know

Best Regards,

Venkatram