Resolving Contract Line Errors

Steps to correct contract line allotments

Dynamics CRM 4 does not provide a way to revise case resolution allotments after the case has been resolved. Reactivating the case and reclosing it will double-dip allotments from the contract lines, leaving them incorrect.
This must be resolved behind the scenes, using SQL scripts.
NOTE: This is not a supported resolution as it directly modifies data rather than relying on the CRM interface. Always make sure and backup a SQL database before modifying data directly.
This article applies specifically to Dynamics CRM v4.0

Get the Case Resolution Activity ID

Use this procedure to get the activity id related to the closed case.
Open the case. Click the History menu item on the left. The top activity should be the case resolution activity. Open it, then click CTRL+N to open the activity in a new browser window.
The Activity ID is the long string between the {} characters

http://server:5555/company/cs/cases/closecase.aspx?id={68C018E8-5D08-E011-B563-001A4B4D64BE}&_CreateFromType=112&_CreateFromId=%7b616C59EA-2DE8-DF11-BA3B-001A4B4D64BE%7d

Get the Contract Line ID

Click through the case to the contract line and use the same procedure as above to get the contract line id.

Double-Check Your Work

Before you start updating all willy-nilly, make sure you have the correct records.
Using SQL Management Studio, connect to the CRM Company database. Use the following scripts to return the records you’re looking to update. Replace the <ID placeholders> tags in the script below with the correct ID’s you retrieved above.

SELECT *
FROM ContractDetailBase
WHERE ContractDetailId = '<CONTRACT LINE ID>'

SELECT *
FROM IncidentResolutionBase
Where ActivityId = '<RESOLUTION ID>'

Sample:

SELECT *
FROM IncidentResolutionBase
Where ActivityId = '81A0A2C9-DE1F-DF11-9063-001A4B4D64BE'

Verify that you’re looking at the correct record and note the current values. In the ContractDetailBase table, we’re specifically looking at the AllotmentsUsed and AllotmentsRemaining fields. For the IncidentResolutionBase table, we’re changing the TimeSpent field.

Update Your Values

If everything matches up and we’re sure we have the right records, use the scripts below to update the correct fields. Replace the <NEW VALUE> tag with the number you wish to use. Replace the <ID placeholders> tags in the script below with the correct ID’s you retrieved above.

UPDATE IncidentResolutionBase
SET TimeSpent = '<NEW VALUE>'
Where ActivityId = '<CONTRACT LINE ID>’

UPDATE ContractDetailBase
SET AllotmentsUsed = '<NEW VALUE>', AllotmentsRemaining = '<NEW VALUE>'
WHERE ContractDetailId = '<RESOLUTION ID>'

You’ll first adjust the Resolution Activity to the correct value. Make a note of how much it as changed and then adjust the Allotments Used on the Contract Line accordingly. Then, use the same number and adjust the Allotments Remaining as well.
For example, we closed a case and resolved it for 30 minutes, when actually it should have been resolved for 45 minutes. The Contract Line is for a total of 600 minutes (10 hours) and after we closed this case, 4 hours (240) had already been used up.
Old Values

Case Resolution: TimeSpent = 30
Contract Line: Allotments Used = 240
Contract Line: AllotmentsRemaining = 360

Corrected Values

Case Resolution: TimeSpent = 45
Contract Line: Allotments Used = 255
Contract Line: AllotmentsRemaining = 345