+21My customer has an IFS75 environment which contains a number of companies. There are some companies that they have sold and they want the companies to be removed from the database.
Anyone an idea or hint or thought on how to commence with this request?
Regards,
Steve
Best answer by eqbstal
All,
I solved this in the brutal way. This is data that is in the database only and there is no applications (to perform CRUD options) available for the end customer, so this is a solution that works for this customer. Remember I do violate the waranty with IFS for this, but once again the customer doesn’t have the intention to use IFS Application and only has access to the database.
My solution is to remove the data from the tables:
declare
company_ site.company%type := 'TST';
site_ site.contract%type;
rem_start_ date;
rem_end_ date;
/**
Use the following select to prepare the list of tables that contain a certain column. Paste result in the loop.
select
'delete from ' || table_name || ' where ' || column_name || ' = ' || 'site_;' Statement
from
(select DISTINCT C.TABLE_NAME, C.COLUMN_NAME
from User_Tab_Columns C, USER_TABLES T, USER_TAB_COMMENTS TC
WHERE C.TABLE_NAME = T.TABLE_NAME
AND TC.TABLE_NAME = T.TABLE_NAME
AND C.column_name in ('CONTRACT', 'SITE')
AND T.TABLESPACE_NAME = 'IFSAPP_DATA'
AND TC.TABLE_TYPE = 'TABLE');
**/
cursor get_sit is
select contract from site
where company = company_;
begin
for rec in get_sit
loop
site_ := rec.contract;
delete from MAINT_TEAM_TAB where CONTRACT = site_;
-- All other deletes based upon site
delete from FAMILY_SET_DATA_TAB where CONTRACT = site_;
commit;
end loop;
--delete from SITE_TAB where CONTRACT = site_;
-- Now that the sites are gone, remove the company
delete from BUDGET_TEMPLATE_ARCH_FILE_TAB where COMPANY = company_;
-- All other deletes based upon company
delete from COMPANY_SITE_TAB where COMPANY = company_;
commit;
end;
-----
I did have a performance problem as it proofed that some tables had been triggered to log deletions to history_log_tab. I have not taken the time to find out how to turn of the triggers to the history log (when only having the database with its api’s in place).
If you use the above, be aware, you are on your own and IFS nor Eqeep nor I, will not support you.
Regards,
Steve
+15Note: not sure if there is a similar option in Apps 7.5 or not, but here is how it’s done in Apps 9
In the Companies overview screen there is an RMB option to Remove Company…
You will only see the “Remove Company…” option if you have access to delete the company. You will need to get the person who created the company to add you to the ‘Users Privileged to Remove Company...” window.
+10I can only imagine this is a big task as we have hung on to companies we have sold. When we moved from IFS7.5 to 9 we planned to leave them behind, but were told by IFS to do ‘Data cleansing’ after the migration… (We still have them). Will be good to know if anyone out there has been through this process.
Mike
+21
. There is even the function to add an additional personthat is allowed to remove the company. This is especially handy if that person is not IFSAPP.
First want to test this.
+21I tried to add IFSAPP as another person thatis allowed to remove a company. Not possible. CallumW was right, only the person that created the company is allowed to do so. As this person is no longer working for the customer any more, security should be opened to allow this.
+10I had a look at this in our IFSv75 system and understand the points made above.
I am wondering why you cannot change the password of the previous user (company creator)? I can understand not wanting to remove the company as the old user, as this would not look great from an audit perspective.
I would unlock the user, change the users password and give additional rights required to the past user to add IFSAPP as a ‘Users Privileged to Remove Company’. Log in as the left user, add IFSAPP as a user privileged to remove that company, then log out and lock that user.
Then login as IFSAPP and remove the company.
Auditing is possibly the only concern, but like you I see no other way around this. At least you would not be removing the company as the left user.
Regards
Mike
+15IFS APIs block updates to the CHANGED_BY value - so if you didn’t want to log in as the old user for any reason, the only other way would be to impersonate the user who created the Company to grant the access to IFSAPP (or any other account you wish to delete the company with).
-- Created on 17/02/2020
declare
-- Local variables here
attr_ varchar2(32000);
objid_ varchar2(32000);
objversion_ varchar2(32000);
info_ varchar2(32000);
begin
client_sys.Clear_Attr(attr_);
client_sys.Add_To_Attr('IDENTITY','IFSAPP',attr_);
client_sys.Add_To_Attr('COMPANY','COMPANY_ID_OR_NAME_HERE',attr_);
IFSAPP.Fnd_Session_API.Impersonate_Fnd_User('OLD_USER_NAME_HERE');
USER_PRIV_REMOVE_COMPANY_API.NEW__(info_,
objid_,
objversion_,
attr_,
'DO' );
IFSAPP.Fnd_Session_API.Reset_Fnd_User;
end;
Removing a company from a Live environment (way to much of a pain for a non-permanent), first things first… has any one transacted in that company, if the answer is yes give up now, remove all access to the company and its associated sites and place two fingers in your ears when asked to remove it, you’ll pretty much spend the rest of time tracking down, trying to remove (as you cant reverse) traces this company has left on your IFS.
Trust me, its not really an option unless you through a lot of money at it and get IFS involved as you’ll have to remove (eg delete) transactions from tables which will most likely invalidate any warranty you have...
+21I do appreciate all answers and ideas.
This morning I added IFSAPP manually as a person with authorization to remove company (database and IFS are no longer ‘alive’ for this group of companies).
Found out that if one wants to remove a company, no sites may be connected. Makes sense.
Brought me the idea to update records in tables (not via views/APIs) for the company that will make the data unclear/random/weird/wrong.
Closing this post and opening another for the change internal data thought.
Thanks for thinking with me.
KR,
Steve
+21All,
I solved this in the brutal way. This is data that is in the database only and there is no applications (to perform CRUD options) available for the end customer, so this is a solution that works for this customer. Remember I do violate the waranty with IFS for this, but once again the customer doesn’t have the intention to use IFS Application and only has access to the database.
My solution is to remove the data from the tables:
declare
company_ site.company%type := 'TST';
site_ site.contract%type;
rem_start_ date;
rem_end_ date;
/**
Use the following select to prepare the list of tables that contain a certain column. Paste result in the loop.
select
'delete from ' || table_name || ' where ' || column_name || ' = ' || 'site_;' Statement
from
(select DISTINCT C.TABLE_NAME, C.COLUMN_NAME
from User_Tab_Columns C, USER_TABLES T, USER_TAB_COMMENTS TC
WHERE C.TABLE_NAME = T.TABLE_NAME
AND TC.TABLE_NAME = T.TABLE_NAME
AND C.column_name in ('CONTRACT', 'SITE')
AND T.TABLESPACE_NAME = 'IFSAPP_DATA'
AND TC.TABLE_TYPE = 'TABLE');
**/
cursor get_sit is
select contract from site
where company = company_;
begin
for rec in get_sit
loop
site_ := rec.contract;
delete from MAINT_TEAM_TAB where CONTRACT = site_;
-- All other deletes based upon site
delete from FAMILY_SET_DATA_TAB where CONTRACT = site_;
commit;
end loop;
--delete from SITE_TAB where CONTRACT = site_;
-- Now that the sites are gone, remove the company
delete from BUDGET_TEMPLATE_ARCH_FILE_TAB where COMPANY = company_;
-- All other deletes based upon company
delete from COMPANY_SITE_TAB where COMPANY = company_;
commit;
end;
-----
I did have a performance problem as it proofed that some tables had been triggered to log deletions to history_log_tab. I have not taken the time to find out how to turn of the triggers to the history log (when only having the database with its api’s in place).
If you use the above, be aware, you are on your own and IFS nor Eqeep nor I, will not support you.
Regards,
Steve
+8This should be done using IFS Data Archiving.
We have successfully removed several sites/companies from multiple IFS instances with the built in toolset and some clever automation to make the creation of the archive jobs easier.
Give me a shout if you want to know more.
+21Thanks Mike.Hollifield. I guess that you are right. It still means that the data is available in the new archive tables. This is exactly the data that we want to remove, so there is still some to do like drop table or delete data from these tables.
Next time I will try that method as it most likely will be working better in combination with the indexing.
Regards,
Steve
+8When you configure the data archiving objects you can specify if it's a hard delete, so the data is only retained if you want it to be.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
