RE: How to group data from file on to IDOC Header & Line Item based on 2 fields

[Former Member]

Hello!! All

Need your help on the below:

I have a FILE to IDOC senario and need to group the data on the file to post IDOC based on data grouped by two fields (unique combication of Carton # & Order #).

Carton# ShipDate Quantity Prepacks Order#

1          12/22/013    10         450         101

1          12/22/013    20         200         102

1          12/22/013    30         100         103

2          12/27/013    10         100         201

2          12/27/013    20         200         202

1          12/28/013    10         500         105

The above needs to post 3 IDOC's as once you group by Carton # & Date you have 3 combinations

IDOC # 1

Header

Carton # 1         Ship Date - 12/22/013

With 3 Line Items

10 (Quantity)

20 (Quantity)

30 (Quantity)

IDOC # 2

Header

Carton # 2          Ship Date - 12/27/013

With 2 Line Items

10 (Quantity)

20 (Quantity)

IDOC # 3

Header

Carton # 1          Ship Date - 12/28/013

With 1 Line Items

10 (Quantity)

Can you please help on how this can be achieved - best approach in a graphical mapping ?!

Your help is greatly apprecaited!!

Thank you,

Ritu

[javier_alcubilla · Accepted solution]

Hi Ritu

It's easy. First you have to do a content conversion in file sender adapter.

Converting Text Format in the Sender File/FTP Adapter to XML - Configuring the File/FTP Adapter in I...

Then modify the idoc schema to allow to create more than one idoc

When you upload the standard schema of an IDoc, you have to modify it. When

referencing the element IDoc, the occurrence has to be changed from maxOccurs=”1” to

maxOccurs=”unbounded”, as explained in SAP Note 814393

Apply graphical mapping order by Carton contenated ( text / concat function) with ShipDate.

Regards

Javi

[Former Member · Accepted solution]

Hey! Javier

Thank you for your response ... I am good with the IDOC Occurance and File Content which I have already done - I wa mainly needing help on the grouping of data by Carton & ShipDate - Will try concat and grouping by - BUT will that alone help in the context of the rest of the fields as well?!

Please, advice ...

Thanks,

Ritu

[javier_alcubilla · Accepted solution]

This example could help you.

Kunnr & Sortl are the key (Check the context)

Sortl_ag the field correspondent to that key

Regards

[Harish · Accepted solution]

Hi Ritu,

To group  all the field you can use contact and sort function at IDOC creation. And the you need use sort by for all other item level field.

Regards,

Harish

[Former Member]

Hey! Javier, Harish

Thank you so much for your inputs - definetly very helpful & might work out!!

ALSO, there is one additional requirement which got added - there is a limitation to have the Line Items restricted to 999 on the IDOC - In this case - I am told that we need to split the Line Items on a 2nd IDOC ... Any idea how this can achieved.

Also, there will be some quanity values which I might have to aggregate on the first IDOC which I will have to carry over to the next IDOC.

So, basically here are the 2 challenges I have:

1. IDOC need to be restricted to '999' & the remainig Line Items need to be carried over to the 2nd IDOC.

2. Additionally I need to aggregate the total Quantity related to a particular file and put it a separate field - still gettin the aggregate logic/requirement ironed out - However, how do i carry values from the first IDOC on to the 2nd IDOC ... whenever I encounter the scenario where there is possibility of Line Items exceeding 999 & I am slitting the file in to 2 or more IDOC's ....

Appreciate all the help & going through the requirement details above!!

Thanks again,

Ritu

[Harish]

Hi Ritu,

i would suggest to add a UDF at the header level, you need to split the IDOC in below condition

When you have Line number count greater then 999 and some more item are left for same carton, then move the whole carton information to the next IDOC. So all your logic to genrate the IDOC needs to change with this req. and you need to change the logic for line item as well.

Please check with your source if they can split the file or you can pick the file in chunk mode from sender file adapter (999 recordset per message). This will not effect your mapping logic.

Hope this help

Harish

[javier_alcubilla]

Point 1.

Constant is the number that you want to split

Point 2

With Count function you can achieve the other requierement because you are at the same graphical mapping

[Former Member]

Thank you for your response! Harish

Also, do you have the UDF logic by any chance.

Thanks,

Ritu

[Former Member]

Thanks!! Javier ... Will tyr it out without the UDF using node functionas suggested above.

[Harish]

Hi Ritu,

Please provide me the source and target structure (and if you have any input file). I will provide you UDF.

regards,

Harish

[Former Member]

Hey! Javier

The above logic worked for Header to IDOC Node - 1 IDOC / Header.

However, I was not able to manage the line Item to be limited to 998 - please see detail requirement below.

Please, find below the source structure.

<Header>

     <Carton_Number></Carton_Number>

     <Ship_Date></Ship_Date>

     <LineItem>

          <Quanity></Quanity>

          <Prepack></Prepack>

          <Order_No></Order_No>

     </LineItem>

</Header>

<Header> will be mapped to <IDOC>

<LineItem> will be mapped to <E1EDP01> (IDOC Line Item)

Requirement:-

1. Header will be mapped to IDOC - Whenever a particular Header crosses 998 LineItems - There should be a second IDOC created.
2. The 2nd IDOC need to have the remaining LineItems.
3. Example: A message has 1200 Line Items - 1st IDOC has 998 LineItems & 2nd IDOC has the remaining 202 LineItems.

Thank you so much for all the help!!

Regards,

Ritu

[Former Member]

Hey! Harish

Please, find below the structure and also the requirement detail:

<Header>

     <Carton_Number></Carton_Number>

     <Ship_Date></Ship_Date>

     <LineItem>

          <Quanity></Quanity>

          <Prepack></Prepack>

          <Order_No></Order_No>

     </LineItem>

</Header>

<Header> will be mapped to <IDOC>

<LineItem> will be mapped to <E1EDP01> (IDOC Line Item)

Requirement:-

1. Header will be mapped to IDOC - Whenever a particular Header crosses 998 LineItems - There should be a second IDOC created.
2. The 2nd IDOC need to have the remaining LineItems.
3. Example: A message has 1200 Line Items - 1st IDOC has 998 LineItems & 2nd IDOC has the remaining 202 LineItems.

Thank you so much for your timely help!!

Regards,

Ritu

[Former Member]

Hey! Harish, Javier

I had a question with regard to the 999 handling - I able to get the IDOC created for every 999 LineItems - However, I need to create a default segment which has the cumulative counts. This segment should be created only for the IDOC's when it reaches 999.

I kind of managed to create this segment as well - however, for it to have the aggregate counts of only 999 LineItems - I am unable to limit the aggregate counts to consider only the 999 Line Items to aggregated and not all LineItems under a particular Header.

The Source Structure is same as above - Please, advice on best approach in PI mapping - Thanks again so much!!

Thank you,

Ritu

[Harish]

Hi Ritu,

Try the below logic it will create the field when count is 999 or more.

Please let me know if you have any other issue.

regards,

Harish

[Former Member]

Hey! Harish

Thank you for your quick response!!

The above logic is working only for first occurance - IF there are 2000 LineItems - The second set of LineItems in the Second IDOC - does not create this node.

Also, need help with an additonal logic - for aggregate field under this segment -

Eg: IF there is 2000 Line Items

1st IDOC field should have LineItem.Quantity aggregated(SUM): which should be only for 999

(1st set of Quantity for all 999 Line Items should be aggregated).

2nd IDOC should have LineItem. Quantity aggregated only for 2nd set of LineItems that is 1000 to 1998 LineItem Quantity should be aggregated).

Hope I am clear with my requirement - pl, let me know ...

Pl, do let me know your best approach.

Thanks again!!

Ritu

[Harish]

Hi Ritu,

Please apply the below map and UDF to create the node. Try the below udf.

int count = 0;
Integer temp;
try
{ temp = Integer.parseInt(input); }
catch (Exception e)
{ result.addValue(e); }

if(temp>0)
count = temp/999;
else
count = 1;

for(int i=0;i<count;i++)
result.addValue("true");

this will give you true for every 999 records.

regards,

Harish

[Former Member]

Hi! Harish

I tried to implement the above - UDF - I am getting the below error after giving variable - Input as integer  - With execution type - All values of Queue.

• cannot find symbol
  symbol : method parseInt(int[])
  location: class java.lang.Integer
  { temp = Integer.p...

Also, have an additional requirement needs to be added ( if possible): The segment we are creating below needs to be created for in another case also (apart from 999 Line Item) - It needs to created when the sum of Quantity = 1000. There will be cases of negative quantity also - so if the sum(Qunatity) = 1000 for the LineItems associated to a Header - then create the node as well.

Please, help accomdate this rule apart from having the node created for 999 Line Item.

Req:

1. Create Node - when LineItem = 999

OR

2. Create Node if Sum(Quantity) = 1000.

I cant thank enough for all the help on this Object!!

Thanks again,

Ritu

[Harish]

Hi Ritu,

try the modified code as context UDF

int count = 0;
Integer temp;
try
{ temp = Integer.parseInt(input[i]); }
catch (Exception e)
{ result.addValue(e); }

if(temp>0)
count = temp/999;
else
count = 1;

for(int i=0;i<count;i++)
result.addValue("true");

for the addition requirement of quantity you can modify the code like below. you need to provide second input quantity with remove context.

int count = 0, sum = 0,temp = 0, lin=0;

try
{ temp = Integer.parseInt(input[0]); }
catch (Exception e)
{ result.addValue(e); }

if(temp>0)
count = temp / 999;
else
count = 1;

for(int j=0;j<quantity.length;j++)
{sum = sum + Integer.parseInt(quantity[j]);
lin = lin+1;
if(lin==999)
{ lin = 0;
if(sum>1000)
  { count = count+1;}
sum=0;} }

for(int i=0;i<count;i++)
result.addValue("true");

regards,

Harish