How to Update single(latest) record in ABAP Routine when there are multiple records in SOURCE-DSO.

[Former Member]

Hi Experts,

I have a scenario where I need to lookup Standard price from lookup-DSO and update the std price value to only one record in Target-DSO based on Material, Plant and latest Creation date.

Example:

Lookup-DSO table:  Only single record with Material and plant combination

Material	Plant	Standard Price
1000	10	20

Source-DSO table:  Multiple records with Material and plant combination based on different creation dates.

Material	Plant	Createdon
1000	10	01/01/2014
1000	10	01/01/2015
1000	10	01/01/2016

Target-DSO table:  In target-DSO, I need to update std price value for only one record based on latest createdon field as shown below

Material	Plant	Createdon	Standard Price
1000	10	01/01/2014	blank
1000	10	01/01/2015	blank
1000	10	01/01/2016	20

I written the below code but it’s not updating to only one latest record, rather its updating to all the record. So could you please help me with code for updating the latest record?

Start Routine:

TYPES: BEGIN OF S_SPRICE,
MATERIAL TYPE /BI0/OIMATERIAL,
PLANT TYPE /BI0/OIPLANT,
PRICE_STD TYPE /BI0/OIPRICE_STD,
END OF S_SPRICE.
DATA: IT_SPRICE TYPE STANDARD TABLE OF S_SPRICE,
WA_SPRICE TYPE S_SPRICE.

SELECT MATERIAL PLANT PRICE_STD INTO TABLE IT_SPRICE
FROM Lookup-DSO
FOR ALL ENTRIES IN SOURCE_PACKAGE
WHERE  MATERIAL = SOURCE_PACKAGE-MATERIAL
AND  PLANT = SOURCE_PACKAGE-PLANT.
SORT IT_SPRICE BY MATERIAL PLANT.

Field Routine for Standard Price:

CLEAR WA_SPRICE.
READ TABLE IT_SPRICE INTO WA_SPRICE
WITH KEY MATERIAL = SOURCE_FIELDS-MATERIAL
PLANT = SOURCE_FIELDS-PLANT
BINARY SEARCH.
IF SY-SUBRC = 0.
RESULT = WA_SPRICE-PRICE_STD.
ENDIF.

Please suggest me..

Thanks,

BRaj.

[Former Member · Accepted solution]

Start routine code between source and target dso.

**********************************************************************

TYPES: BEGIN OF S_SPRICE,

MATERIAL TYPE /BI0/OIMATERIAL,

PLANT TYPE /BI0/OIPLANT,

PRICE_STD TYPE /BI0/OIPRICE_STD,

END OF S_SPRICE.

DATA: IT_SPRICE TYPE STANDARD TABLE OF S_SPRICE,

WA_SPRICE TYPE S_SPRICE.

TYPES: BEGIN OF S_SPRICE_C,

MATERIAL TYPE /BI0/OIMATERIAL,

PLANT TYPE /BI0/OIPLANT,

PRICE_STD TYPE /BI0/OIPRICE_STD,

0created_on type /bi0/oicreatedon,

END OF S_SPRICE_C.

DATA: IT_SPRICE_c TYPE STANDARD TABLE OF S_SPRICE_c,

WA_SPRICE_c TYPE S_SPRICE_c.

SELECT MATERIAL PLANT PRICE_STD INTO TABLE IT_SPRICE

FROM Lookup-DSO

FOR ALL ENTRIES IN SOURCE_PACKAGE

WHERE  MATERIAL = SOURCE_PACKAGE-MATERIAL

AND  PLANT = SOURCE_PACKAGE-PLANT.

SORT IT_SPRICE BY MATERIAL PLANT.

SELECT material plant price_std MAX( createdon )

       FROM source_dso

       INTO table  it_sprice_c

       for entries all in Source_package

       where material = source_package-material and

  plant = source_package-plant

       GROUP BY material plant.

sort it_sprice_c by material plant.

Loop at source_package assigning <sfs>

  read table it_sprice_c into wa_sprice_c with key material =  <sfs>-material

  plant = <sfs>-plant createdon = <sfs>-createdon binary search.

  if sy-subrc eq 0.

  read table it_sprice into wa_sprice with key material =  <sfs>-material

  plant = <sfs>-plant binary search.

  if sy-subrc = 0.

  <sfs>-price_std = wa_sprice-price_std.

  endif.

  else.

  <sfs>-price_std = 0.

  endif.

endloop.

**********************************************************************

make sure you have semantic group (material,plant ) in DTP.

Regards,

Sandeep

[Former Member · Accepted solution]

Thanks a lot Sandeep.

Now the code is updating for latest record.

Regards,

BR

[Former Member]

Experts, Thanks a lot for your help.

[former_member186445]

another option:

in the end routine:

sort on material and plant ascending, createdon descending.

for the first record you do a lookup in the lookup dso.

for the next records, you first check if the material and plant is the same as the ones of the previous record. if this is the case, you set blank. else, you do a lookup.

somthing like this:

clear o_material o_plant.

loop at records

     if records-material ne o_material

       and records-plant ne o_plant

         --> lookup

     else

        -->   set to blank

     endif

     o_material = records-material

     o_plant = records-plant

endloop.

hope this is clear.

M.

[Former Member]

Hi M Tibollo,

Thanks for your time.

Let me try this and share you the output.

Thanks,

Braj

[Former Member]

Hi Braj,

You can add a new field in the internal table called Flag (char 1) that will be filled by ' ' or 'X' depending on material and plant values.

Then do a loop to the internal table and fill this field with a 'X' using at new sentence (for fields material and plant). If it is the first record for the same material and plant X, else  blank.

First you have to order table also by creation date in descending order.

Best regards and good luck!

Gorka.

[Former Member]

Hi Gorka,

Thanks for your inputs,

In this scenario my internal table will have lookup dso data and in lookup dso i have only one record with material plant combination. so problem here is source and result package willl have multiple records.

could you elaborate your answer little bit please ?

Thanks,

Braj

[Former Member]

Hi again,

Of course Braj, pass your SOURCE_PACKAGE data to an internal table (that may have the following fields: material, plant, date and flag), you can check as semantic keys in DTP material and plant before. Then do:

sort internal table by material plant ascending and created on in descending order

loop internal table.

at new plant. (because the condition enter if material and plant are not the same)

set flag to "X"

endat.

nothing to do or set flag to " "

endloop.

Finally in transformation asigment do a read table when 3 condition met and if flag is "X", select Price in lookup DSO.

Best regards,

Gorka.

[Former Member]

Thanks a lot Gorka for your inputs and your time.

[surendra_p]

Hi Raj,

You have Created On Info objects in Source- DSO, So we can find the latest record from source data by using that object using Select command and Bring that data only.

SELECT FIELDS/IOBJ INTO I_TAB FROM SOURCE-DSO WHERE CREATED ON = SELECT MAX(CREATED ON) FROM SOURCE-DSO.

For reference please go through the following link.

https://scn.sap.com/thread/3413589

Regards

Surendra

[Former Member]

Hi Surendra,

Thanks for your inputs..

Let me try this one too and share you the Output of it.

Thanks,

Braj

[Former Member]

Hi B.raj,

This can be achieved in multiple ways. Safest is to have your code in End Routine and not in Field Routine. You need a couple of things to achieve this:

1. All your data for same material and plant should be in same package else you will end up having multiple record price updates and that too randomly. To get all same material and plant records use  - Semantic Groups in your DTP for Material and Plant.

2. Next, you need to sort your source data descending in end routine based on material, plant and 'create date' as said by @Raf Boudewijns .

Now from this point you can manipulate in following ways:

a) Run a loop on your result_package and read the standard price for material and plant from look up dso and update only the first record encountered for material and plant combination and leave rest. This involve a tricky code so I would say go with option b.

b) Copy result package into a internal table (lt_result_pack). Sort lt_result_pack by material plant created_date descending. Delete adjacent duplicates from lt_result_pack comparing material plant.

Run a loop on lt_result_pack and read/update the corresponding std price from look up dso. 

Here you can directly update the result package from lt_result_pack by reading/updating result package with material plant and create date.

With this you will not lose any data and standard price will be updated correctly.

Just a thought, how do you plan to update this to target DSO i.e. based on which keys? if you have material and plant only then you should be good else you may end up with multiple standard prices for same combination of material and plant. So chose the keys wisely

Thanks

Amit

[Former Member]

Hi Amith,

Thanks for your inputs..

In the Target DSO we have different keys like Billing doc and Billing Item, i have tried with the your option b but it std price was updating to all the records based on material and plant.

And also i tried your option a but i could not able to skip the remaining records after looping the first record. could please elaborate the coding part here ti skip the duplicate record?

Thanks,

Braj

[Former Member]

just create an internal table and put the the source package data into it. Sort it by Material & Plant and descending for Created On and delete data from this table comparing Material & Plant.

Now in the field level routine, 1st read this table matching the 3 fields Material & Plant and  Created On.

if sy-subrc = 0 then only do your read else put result as blank.

Regards,

Anupama

[Former Member]

Hi Anupama,

Thanks for your inputs..

Let me try this and share you the Output of it.

Thanks,

Braj

[RafkeMagic]

how about sorting your table IT_SPRICE (ascending for Material & Plant and descending for Created On) and then deleting adjacent duplicates comparing Material & Plant?

PS you probably also need to read on Created On if you only want to update the last record in the target

[Former Member]

Hi Raf,

Thanks for your inputs.

I did sort descending and deleted adjustment duplicate records but with this approach i am going to loose the records in the target.

All i need to update the latest record without deleting any records either in source or result package.

Also tried to implement this logic in end routine but same result.

Can you explain me on how to loop only one record and skip the remaining duplicate record in the loop based on material and plant combination ?

Thanks,

Braj.