Remove SFG Partner Profile (Hard Delete if SFG removal fails)

There was bug in SFG (old versions) where it leaves some orphan entries in DB when you try to remove the trading partner from SFG UI. To solve the problem here are steps to do hard delete of partner profile.

Attempt to delete from SFG in regular way and then remove from Dashboard --> Trading Partner (All Contracts, Document Exchange, Profile, Routing Channels, Mailbox Virtual Roots if any etc.)

Make sure the following table should not return any entry for Trading Partner, If it returns then remove the entry manually...

This example Partner Name is : EDI

select 'YFS_USER' from YFS_USER where UPPER(USERNAME) like '%EDI%'  UNION
select 'YFS_ORGANIZATION' from YFS_ORGANIZATION where UPPER(ORGANIZATION_KEY) like '%EDI%'  UNION
select 'MBX_ACTION'  from MBX_ACTION where UPPER(USER_ID) like '%EDI%'  UNION
select 'MBX_MAILBOX'  from MBX_MAILBOX where UPPER(PATH) like '%EDI%'  UNION
select 'SCI_CONTRACT' from SCI_CONTRACT where UPPER(OBJECT_NAME) like '%EDI%'  UNION
select 'SCI_DOC_EXCHANGE' from SCI_DOC_EXCHANGE where UPPER(OBJECT_NAME) like '%EDI%'  UNION
select 'SCI_ENTITY'  from SCI_ENTITY where UPPER(OBJECT_NAME) like '%EDI%'  UNION
select 'SCI_PACKAGING'  from SCI_PACKAGING where UPPER(OBJECT_NAME) like '%EDI%'  UNION
select 'SCI_PROFILE' from SCI_PROFILE where UPPER(OBJECT_NAME) like '%EDI%'  UNION
select 'SCI_TRANSPORT' from SCI_TRANSPORT where UPPER(OBJECT_NAME) like '%EDI%'  UNION
select 'SCI_CODE_USR_XREF'  from SCI_CODE_USR_XREF where UPPER(USER_ID) = 'EDI%';

select * from YFS_USER where UPPER(USERNAME) = 'EDI';
select * from YFS_ORGANIZATION where UPPER(ORGANIZATION_KEY) = 'EDI';
select * from MBX_ACTION where UPPER(USER_ID) = 'EDI';
select * from MBX_MAILBOX where UPPER(PATH) = 'EDI';
select * from SCI_CONTRACT where UPPER(OBJECT_NAME) ='EDI';
select * from SCI_DOC_EXCHANGE where UPPER(OBJECT_NAME) = 'EDI';
select *  from SCI_ENTITY where UPPER(OBJECT_NAME) = 'EDI'; 
select *  from SCI_PACKAGING where UPPER(OBJECT_NAME) = 'EDI';
select * from SCI_PROFILE where UPPER(OBJECT_NAME) = 'EDI';
select * from SCI_TRANSPORT where UPPER(OBJECT_NAME) = 'EDI';
select * from SCI_CODE_USR_XREF where UPPER(USER_ID) = 'EDI';

--finally delete (be careful) --
DELETE from YFS_USER where UPPER(USERNAME) = 'EDI';
DELETE from YFS_ORGANIZATION where UPPER(ORGANIZATION_KEY) = 'EDI';