Skip to main content
Solved

Using a Projection to perform bulk deletes (eTag issues)

  • April 20, 2026
  • 3 replies
  • 48 views
martin.surasky
Hero (Customer)
Forum|alt.badge.img+10

In the past I was able to loop in a PL/SQL Script and, inside that loop provide values parameters that I used to invoke a Projection to perform some action in Bulk. Here is one example:

-- Use IFS API repeatedly to perform some action
DECLARE 
   err_code INT;
   err_msg VARCHAR(512);
   issue_number SMALLINT := '5189';


   CountryCode_ VARCHAR2(32000) := 'AR';
   StateCode_ VARCHAR2(32000) := 'CATA';
   CountyCode_ VARCHAR2(32000) := '10523';
   ETag_ VARCHAR2(32000) := 'W/"AAASGyAAMAAAQjVAAZ:20260107063806"';
   Action$_ VARCHAR2(32000) := 'DO';
   Return_$_ COUNTIES_HANDLING_SVC.ENTITY_DEC := null;   
BEGIN   
   FND_SESSION_API.SET_PROPERTY('ODP_SESSION', 'TRUE');

     FOR county_code_delete_rec IN (    
      SELECT COUNTRY_CODE, STATE_CODE, COUNTY_CODE, 'W/"' || OBJID || ':' || OBJVERSION ETAG
      FROM COUNTY_CODE           
      WHERE COUNTRY_CODE = 'AR'
      AND ROWNUM <= 10 -- do this in batches
      ORDER BY COUNTRY_CODE, STATE_CODE, COUNTY_CODE
  ) LOOP                       
       CountryCode_ := county_code_delete_rec.COUNTRY_CODE;
       StateCode_ := county_code_delete_rec.STATE_CODE;
       CountyCode_ := county_code_delete_rec.COUNTY_CODE;
       ETag_ := county_code_delete_rec.ETAG;
       Action$_ := 'DO';

       BEGIN
           Return_$_ := Counties_Handling_SVC.CRUD_Delete(ETag_, CountryCode_, StateCode_, CountyCode_, Action$_, county_code## => '');
           INSERT INTO MANUAL_CHANGES_LOG (issue_number, text) VALUES (issue_number, 'Deleted row: COUNTRY_CODE: ' || CountryCode_ || ', STATE_CODE: '|| StateCode_ || ', COUNTY_CODE: ' || CountyCode_);
       EXCEPTION       
           WHEN OTHERS THEN
             err_code := SQLCODE;
             err_msg := SUBSTR(SQLERRM, 1, 200);
             IF err_code = -20110 THEN
                INSERT INTO MANUAL_CHANGES_LOG (issue_number, text) VALUES (issue_number, 'ERROR ' || err_code || ' - ' || err_msg || 'when deleting County entry with COUNTRY_CODE: ' || CountryCode_ || ', STATE_CODE: '|| StateCode_ || ', COUNTY_CODE: ' || CountyCode_);          
             ELSE
                INSERT INTO MANUAL_CHANGES_LOG (issue_number, text) VALUES (issue_number, 'ERROR ' || err_code || ' - ' || err_msg || 'when deleting County entry with COUNTRY_CODE: ' || CountryCode_ || ', STATE_CODE: '|| StateCode_ || ', COUNTY_CODE: ' || CountyCode_);          
             END IF;
       END;
  END LOOP;
  FND_SESSION_API.SET_PROPERTY('ODP_SESSION', 'FALSE'); 
END;

Now, when running a script like this I see Oracle fails on calling the CRUD_Delete with the error message “ERROR -20114 - ORA-20114: CountyCode.FND_MODIFIED: The County Code record has already been changed. Please refresh the record and reenter your changes”.

 

Any idea why can be wrong

Best answer by Tharshan SM

Hi ​@martin.surasky.

In my understanding, when you follow the debug console it handles calls from the client side. Usually if you check the projection generated SVC files(CountiesHandling) they will have wrapper functions like Counties_Handling_SVC.CRUD_CREATE, Counties_Handling_SVC.CRUD_UPDATE, Counties_Handling_SVC.CRUD_DELETE. But if you check inside those functions they will call the actual API for the entity like County_Code_API.New__, County_Code_API.Modify, County_Code_API.Remove__. However if your script cannot handle private methods then it is better to go with the wrapper functions.

Also info is simply an output variable used to return values.

 

Also, this is just some sample code that we mostly use in PL/SQL scripts to handle CRUD operations for the entity.

 

 

If I understand your question correctly, in the API documentation any CRUD operation done with the EntitySet (like StateCode in CountiesHandling) refers to the Entity (like StateCode). You can also find it in the API Explorer as well, where you can see any CRUD operations that have been used in the entity, although they may not be active by default.

 

Regards

3 replies

Tharshan SM
Do Gooder (Customer)
Forum|alt.badge.img+4
  • Tharshan SM
  • Do Gooder (Customer)
  • April 21, 2026

Hi ​@martin.surasky,

I think the issue is in your FOR loop. As you know, the ETAG is a combination of OBJID and OBJVERSION. When the first record is deleted, the OBJVERSION changes in the entity, which also causes the ETAG to change. As a result, the loop cannot find the updated ETAG for the remaining records. A better approach would be to use a cursor to re-fetch the ETAG fresh before each delete operation.

 

Additionally, since this is a PL/SQL script, instead of using Counties_Handling_SVC.CRUD_Delete, you can directly call the IFS API for the delete operation, such as County_Code_API.Remove__(info_, objid_, objversion_, 'DO'),  if it is available. This would be a simpler and more direct approach.

 

Regards

 

 

    martin.surasky
    Hero (Customer)
    Forum|alt.badge.img+10
    • martin.surasky
    • Author
    • Hero (Customer)
    • April 21, 2026

    Thanks ​@Tharshan SM!

    Turns out my error was caused by how I was crafting the eTag in Line 18 of my script. Instead of

    SELECT COUNTRY_CODE, STATE_CODE, COUNTY_CODE, 'W/"' || OBJID || ':' || OBJVERSION ETAG

    I should have done this (notice the extra double quotes)

    SELECT COUNTRY_CODE, STATE_CODE, COUNTY_CODE, 'W/"' || OBJID || ':' || OBJVERSION || '"' ETAG

    The reason I used the Projection (Counties_Handling_SVC.CRUD_Delete) instead of the Entity itself (County_Code_API.Remove__) is because, in order to find the right code to call I first performed the action manually (with IFS Chrome Dev Extension tracking the network activity) and I found a the Project DELETE rest API showing up there (as opposed to the Entity “Remove__” Private method one...).

     

    Just curious. When you need to explore calls (to use them on Scripts, Worflows, etc.) do you have any other (more effective) method that leads you to the Entity API (as opposed to the Projection API)? If there is a way to see clearly the Entity API to use (together with how the info parameter must be put together) I may choose that route!

     

    Thanks!

      Tharshan SM
      Do Gooder (Customer)
      Forum|alt.badge.img+4
      • Tharshan SM
      • Do Gooder (Customer)
      • Answer
      • April 22, 2026

      Hi ​@martin.surasky.

      In my understanding, when you follow the debug console it handles calls from the client side. Usually if you check the projection generated SVC files(CountiesHandling) they will have wrapper functions like Counties_Handling_SVC.CRUD_CREATE, Counties_Handling_SVC.CRUD_UPDATE, Counties_Handling_SVC.CRUD_DELETE. But if you check inside those functions they will call the actual API for the entity like County_Code_API.New__, County_Code_API.Modify, County_Code_API.Remove__. However if your script cannot handle private methods then it is better to go with the wrapper functions.

      Also info is simply an output variable used to return values.

       

      Also, this is just some sample code that we mostly use in PL/SQL scripts to handle CRUD operations for the entity.

       

       

      If I understand your question correctly, in the API documentation any CRUD operation done with the EntitySet (like StateCode in CountiesHandling) refers to the Entity (like StateCode). You can also find it in the API Explorer as well, where you can see any CRUD operations that have been used in the entity, although they may not be active by default.

       

      Regards

      Terms & ConditionsCookie settingsAccessibility statement