Delete data from data integration

Hello,

It is actually possible to delete data using Data Migration. Normally the most common way to do this via migration jobs is using the procedure Remove__ in the Method List.

As an example, let’s imagine that we want to remove the Supplier Delivery Tax Code Tab from a supplier. We can then create a migration job calling the method Supplier_Delivery_Tax_Code_API.Remove__:

In the Database Information tab, we enter the view from where we want to remove the record, in our case SUPPLIER_DELIVERY_TAX_CODE, and in the Where Clause we must filter which records we want to fetch, in my case i tested with a dummy supplier:

In the Source Mapping we must map the OBJID, OBJVERSION and ROWID fields and enter ‘DO’ as default value in the ACTION field:

Once this is done you should be able to run the job. Be careful with the where clause and check the select is fetching the correct lines!!!

Thank you for your prompt answer. i’ll try the solution .

Hello Sergio,

Your solution seems great ; however applying something similar, I get the answer :
 

READ ONLY GRANTS FOR IFSINFO

5554 rows selected   
5554 rows failed   

I tried to delete rows from PERSON where

person_id<'A' and CUSTOMER_CONTACT_DB='TRUE' and CONTACT_CUSTOMERS is null and Objstate='Active'

with the following Source Mapping :

Do you have any clue about the error ? 

Hello,

Can you please send the error message? By pressing the Execute Job → Details.

It could be due to some business logic not a migration issue, let’s see.

Regards,

Sergio

Thanks for answering so quickly : That’s good to see so reactive people 🙂
Here is the message I get :

ORA-20114: PersonInfo.FND_MODIFIED: The Person Info record has already been changed. Please refresh the record and reenter your changes.

And the corresponding Attribute String is :

#5=AAARlBAA5AAEU3SAAF
#6=20241202154122
#7=DO
NEW_MASTER=TRUE

You’re welcome! Can you please double check that the persons are still existing?

If yes, please Clean Up the job and execute it again. Normally that error happens when someone else is doing changes on the record at the same time.

By the way, I noticed a difference between the method name (PERSON_INFO_API.Remove__) and the view name (PERSON_INFO_ALL).

If I try to set the method to PERSON_INFO_ALL_API.Remove__ I can’t save it : 

Method PERSON_INFO_ALL_API.Remove__ does not exist

So may be I should choose PERSO_INFO as the view ?

Aha, that could be also the case. PERSON_INFO_ALL is reading from the table PERSON_INFO, so it makes sense that the Remove_ method does not exist there.

Please try doing what you suggested that should be working!

I checked the records : they are still here 🙄

I changed the view to remove the _ALL, clean the job and launch it once again : 

 20 IC_RDD_CI_010A_D_20 
       Processed rows  : 0  , Errors : 5553  

READ ONLY GRANTS FOR IFSINFO 

    5553 rows selected 
    5553 rows failed 

ORA-20114: PersonInfo.FND_MODIFIED: The Person Info record has already been changed. Please refresh the record and reenter your changes.

Attribute String : 

#5=AAARlBAA5AAEU3SAAF
#6=20241202154122
#7=DO
NEW_MASTER=TRUE

😕

Did you add also PERSON_INFO in the Source Name in the Database Information tab? It could be the issue. If still having the error I would recommend to create the job again with PERSON_INFO and enter the Remove_ method correctly.

Hello,
I tried with another table COMM_METHOD to delete unwanted 0 values... unsuccessfully.

I get 2404 errors: ORA-20115: CommMethod.The "Comm Method" has already been removed by another user. which is wrong !

Here are my settings:

If someone has any idea on how to make it works, that would be very helpful.

Help ! Still the same problem. Can it comes from somewhere in the Rules tab ?
 

Hello,

I was trying another mass delete : Deleting every records from DELIVERY_FEE_CODE. I noticed a difference between Serio parameters and what I have done before : The column names prefix are different. Sergio prefixed them by “method10.” while I let the table / view name : “PERSON_INFO_ALL.” On this new attempt, I prefixed them with “METHOD11.”, 11 being the number of my method ; and it works 😊

Hoping it can help someone else...

Another point that seems important to avoid errors such as :

ORA-01422: exact fetch returns more than requested number of rows

It’s seems better to leave empty the View Name on the Method List tab