Bulk removing Responsibilities

[Shaun_Bailey]

Is there anyway to bulk remove a particular Responsibility (maybe using the Contract adaptor or other).  I know if i need to change the Point of Contact for a particular Contract i can go into the Responsibilities tab and manually remove one name and add another.  However i need to do that for several hundred contracts!  There is no issue adding a new Point of Contact (as i can just use the Contract adaptor) however it doesn't overwrite the old Point of Contact (probably correctly as you can have more than one) and i need a way to remove this old Point of Contact.

I have tried using the Contract adaptor and leaving the Point of Contact 'blank' but it just doesn't do any update and leaves the old name intact

NB - the reason this is an issue is we have setup an auto email to be sent to whoever is the Point of Contact for each contract and i only want it to go to one person

[erwinlindemann]

Hi Shaun,

Assuming that you work this is an on-premises version of FNMS and that you have access to the [FNMSCompliance] database, run the following SELECT statement for retrieving all contracts with the users that have a specific role on the contract (before executing the SQL, replace 'ResponsibilityType.Signatory' with the correct role that you are interested in, please):

SELECT 
      c.ContractName
    , cu.SAMAccountName
    , rt.ResourceString      AS [ResponsibilityType]
FROM [ComplianceResponsibility] cr
JOIN [Contract]                 c   ON c.ContractID = cr.ContractID
JOIN [ResponsibilityType]       rt  ON rt.ResponsibilityTypeID = cr.ResponsibilityTypeID
JOIN [ComplianceUser]           cu  ON cu.ComplianceUserID = cr.ComplianceUserID
WHERE rt.ResourceString = 'ResponsibilityType.Signatory'

After you confirmed that the previous SQL retrieves all contract responsibilities that you would like to remove, you can run the following statement to delete the responsibilities:

/* Deleting the responsibilities */
DELETE FROM [ComplianceResponsibility]
WHERE ComplianceResponsibilityID IN
(
    SELECT cr.ComplianceResponsibilityID
    FROM [ComplianceResponsibility] cr
    JOIN [ResponsibilityType]   rt  ON rt.ResponsibilityTypeID = cr.ResponsibilityTypeID
    WHERE rt.ResourceString = 'ResponsibilityType.Signatory'    
)

As usual, before deleting any data from a production FNMS environment, creating a backup of the [FNMSCompliance] database is generally recommended 😁.