Deleting Rows from an Extension Collection in Bulk

[Former Member]

Hi -

Recently, I have received some questions from E-Sourcing users on how they could delete rows from an extension collection in bulk. The use case that I have heard of is that the user has an extension collection on some business document or master data that they intend to bulk load using the standard E-Sourcing extension collection import file. The users are concerned that if they make a mistake during this upload or have a reason to perform a mass change on the extension collection in the future, there is no out-of-the-box solution for doing so: extension collection imports are always "additive" - new rows are added to the collection and there is no out-of-the-box facility to update or delete rows.

As it turns out, there is a pretty straightforward solution to this challenge using an additional extension field and a custom script definition. The concept is that a script definition can be written that will delete the rows from the collection and an import file can be used to specify the specific object instances that require the collection deletion.

As a concrete example, consider a situation where an extension collection has been created on the Product Category (aka Internal Category) object that holds a list of approver users for that Product Category. During initial setup, the approvers can be loaded into the extension collection using the standard E-Sourcing extension collection import file. At some later time, it is required to change the list of approvers on many of the internal categories. The solution presented here would first require that an import file be used to delete all rows from the approvers extension collection table (using the Product Category import file), followed by using the standard extension collection import file to apply the new approver list.

The technical implementation of this solution requires two components:

1) A extension field should be added to the object on which the extension collection also exists (in the example above, the Product Category). This field would be named "Delete Collection" and have ID "DELETE_COLLN", for example. It would have a default value of FALSE.

2) A script definition would be added to the Validate target of the object on which the extension collection exists (again, in the example above, the Product Category). The script definition would simply look at the value of the DELETE_COLLN extension field and, if it is TRUE, delete all rows from the extension collection.

Using the above components, a two-column import file for the affected object would be created that would have the ID of the object instance and a column for the DELETE_COLLN field set to TRUE. When the import file is imported, the result would be that each object specified via the ID would have all rows in the extension collection deleted.

I have prepared a sample script to demonstrate how it would be done:

// If the "delete collections" boolean is set to true,
// then delete all rows in our extension collection
if (doc.getExtensionField("DELETE_COLLN").get().booleanValue())
{
    extColl = doc.getExtensionCollection("EXT_COLLN");
    for (iter = extColl.size(); iter > 0; iter--)
    {
        member = extColl.get(iter-1);
        extColl.delete(member);
    }
}

// Clear the flag so that we don't continually delete
doc.getExtensionField("DELETE_COLLN").set(Boolean.FALSE);

In the above code snippet, the DELETE_COLLN is the Boolean extension field on the object that indicates that the extension collection should be cleared. The EXT_COLLN is the identifier of the extension collection that is required to be deleted.

It is important to note that this solution will only work for objects that support import files. Thankfully, this is most objects. It is also important to note that if there are multiple extension collections where the data needs to be cleared, then multiple Boolean extensions may be required as well as repeating the logic in the script above for each extension collection.

Lastly, it is strongly recommended that E-Sourcing Page Customizations be used to hide the Boolean "Delete Collection" indicator so that it is not unintentionally used by a user in the user interface.

I hope this concept proves useful. Please add comments or questions.

Rob Stevenson

SAP E-Sourcing Expert

[Former Member]

Hi Rob,

Just to notice that even if your solution will only work for objects that support import files, there is a solution for other object as well. I had the problem because I was needed to delete in bulk on the project and project doesn't support import files.

The solution is to do a collection for the delete boolean.

=> Create a collection named for exemple "DELETE_COL" with a boolean field called "DELETE_FIELD".

Then you need to change a little bit the code for the object validation as well:

// If the "delete collections" boolean is set to true,
// then delete all rows in our extension collection
deleteCol = doc.getExtensionCollection("DELETE_COL");
if(deleteCol.size()>0)
{
	if(deleteCol.get(0).getExtensionField("DELETE_FIELD").get().booleanValue())
	{
	    extColl = doc.getExtensionCollection("EXT_COLLN");
	    for (iter = extColl.size(); iter > 0; iter--)
	    {
		member = extColl.get(iter-1);
		extColl.delete(member);
	    }
	}
	// Clear the flag so that we don't continually delete
	deleteCol.delete(deleteCol.get(0));
}

You will not need to use the object import file that could not exists, but the extension collection file.

Regards,

Gaëtan

[Former Member]

Hi Gaëtan, I have one requirement, while we duplicate (copy) any project document from existing one, all the fields should be cleared up in new document. can you help me on this. Regards, KP

[Former Member]

Hello Rob,

First, Thanks for this helpful note.

I have some questions to go deeper:

1) Could it be possible to add the extension field to the collection so that we could choose which line should be deleted by importing a file with all the lines we want to delete?

2) In version 6, there is a new script context called "Import Lifecycle Event" with several targets, can we already find any help about what is possible to do and what are the variables that we can use, the objects we can access...?

Regards,

Gaëtan

[Former Member]

Hi Gaëtan -

Regarding your first question, I believe you are inquiring about an extension collection. Unfortunately, there is no good way to do this. The extension collection import file always adds to the extension collection and does not provide for an update or delete option. You would need an update option available on the import file so that an existing extension collection row could be influenced.

Regarding your second question about the new scripting options available in 6.0, below is some information. I have not personally used these events yet, so I cannot provide any real-world experiences, yet hopefully the documentation below is useful.

Regards,

Rob

Import Lifecycle Event: A script that executes during data import as dictated by the target lifecycle. Import lifecycle scripts allow access to inbound data and newly created business objects.

Similar to the Document Life Cycle, a series of events are associated with data import. Different points in the life cycle allow access to different objects and data. There are two main categories of events: u201Crecordu201D events and u201CiBeanu201D events. The former occur before a business object has been constructed and only allow access to an u201Cimport recordu201D containing the inbound data. The latter occur after the object has been constructed and is available to the script as an instance of IBeanIfc. In the script code, the iBean will always be available in the variable u201Cdocu201D, as with other scripting events. The record, which implements ImportRecordIfc, is available in the variable u201Cimport_recordu201D.

Event Descriptions:

Init: The init hook allows scripting at the beginning of file processing. There is no record or ibean data available. This can be used to validate environmental variables, for example. This is of limited use, as no record data is available.

Pre-Process Row: Allows access to inbound data before the iBean has been created.

Should Process Row: Gives the opportunity to reject the import of a particular row.

Process Row: Gives the opportunity to manipulate the newly constructed iBean before it is saved.

Ok To Save: Gives the opportunity to reject the save of a particular iBean

Save: Gives the opportunity to act on the iBean after it has been saved.

Final: This is the mirror image of the Init event. Allows scripting after the import of the entire file has completed.

[Former Member]

Rob,

We have an extension collection that we populate weekly. We have an external UNIX script that deletes all rows before populating the rows with external (SAP ECC) data.

This works fine for us. However, we have found that we can only import our extension collection file manually. There doesn't appear to be an option to schedule the import.

We're using E-Sourcing 5.1 SP01 Patch 1.

Do you know of a way to schedule the import for extension collections?

Thanks, Scott

[Former Member]

Hi Scott -

Thank you for the reply to my posting. I have a couple of comments on your question:

- From what I understand, you are using SQL to directly delete the rows from the extension collection table. While I understand this is technically possible, I would discourage using this approach as application logic is potentially being bypassed when this is done. The technique that I described above uses application APIs which ensures that the application logic is also applied.

- Regarding scheduling the extension collection import, I too have the same results as you: there is no way to use the Data Import Monitor and schedule the import of the extension collection CSV. This is unfortunate and it would be a good idea for you to enter a message with SAP that indicates you would like this functionality.

I hope this information is helpful.

Thanks,

Rob Stevenson