Script to delete a Data Import profile

MODDICONV-426: [RRT] Data Import Action profiles deletion failsClosed

Starting from the Poppy release, Data Import Profile associations are stored using Wrapper entities. Beginning with the Ramsons release, these associations are stored in a consolidated table shared across all profile types. Additionally, profiles deleted via the UI are now permanently removed rather than simply marked as deleted.

However, due to possible data inconsistencies or migration errors, some obsolete links may still remain, preventing a specific profile from being deleted via the UI or API. In such cases, the system may return an error similar to:

"The Action Profile cannot be deleted, as it is associated with one or more Job Profiles or Field Mapping Profiles."

To resolve this issue and delete the affected profile, follow the steps below.

Find the profile association(s)

SELECT *
FROM {tenant}_mod_di_converter_storage.profile_associations
WHERE detail_profile_id = '{profile_id}';

If no rows are returned, try the master side:

SELECT *
FROM {tenant}_mod_di_converter_storage.profile_associations
WHERE master_profile_id = '{profile_id}';

You should see at least one row (there may be multiple). From each returned row, note:

detail_wrapper_id
(optionally also) master_wrapper_id

Delete the association(s)

For each association row found, delete by detail_wrapper_id (preferred for specificity). If needed, you can also match by both wrapper IDs.

DELETE
FROM {tenant}_mod_di_converter_storage.profile_associations
WHERE detail_wrapper_id = '{detail_wrapper_id}';
-- Optionally, also ensure pairs are gone:
-- OR (master_wrapper_id = '{master_wrapper_id}' AND detail_wrapper_id = '{detail_wrapper_id}');

Repeat for every association row if multiple exist.

Delete the wrapper(s)

After associations are removed, delete the corresponding wrapper(s):

DELETE
FROM {tenant}_mod_di_converter_storage.profile_wrappers
WHERE id = '{detail_wrapper_id}';

If you also captured a master_wrapper_id that’s uniquely tied to this profile and no longer needed, delete it as well:

DELETE
FROM {tenant}_mod_di_converter_storage.profile_wrappers
WHERE id = '{master_wrapper_id}';

Delete the profile

Finally, delete the profile record itself from the appropriate table for its type:

Action Profile: {tenant}_mod_di_converter_storage.action_profiles
Mapping Profile: {tenant}_mod_di_converter_storage.mapping_profiles
Match Profile: {tenant}_mod_di_converter_storage.match_profiles
Job Profile: {tenant}_mod_di_converter_storage.job_profiles

Example:

DELETE
FROM {tenant}_mod_di_converter_storage.action_profiles
WHERE id = '{profile_id}';