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
Jun 04, 2021 06:38 AM
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 😁.
Jun 08, 2021 01:49 AM - edited Jun 08, 2021 03:32 AM