Upgrades: Contracts and Contract Detail Records

Contracts within Dynamics CRM can sometimes be a little peculiar to work with.  Case in point: You must have at least one Contract Detail record associated with the contract in order to make it function properly.

The issue there is that some companies don’t actually care about detail lines, but they have to have them anyway.

On a recent upgrade, the previous consultant had created a plugin to automatically create a dummy, or placeholder, Contract Detail record.

We ran into an issue later when we found that CRM was very upset when attempting operations such a Renew Contract, when it found start and stop dates on the Contract Detail that were outside of the start and stop dates of the Contract itself.

It turns out that for some reason, the internal CRM process that checks the Contract Detail start and stop dates does not occur when you create the record via the SDK.  No idea why that is, but it did cause a bit of a problem until I tracked it down and modified the custom plugin to use different start/end dates on the Contract Detail.

You can use this SQL query to locate Contract Detail lines that are outside of the bounds of the parent Contract:

SELECT     
    'Status' = CASE
        WHEN ContractDetail.ActiveOn < Contract.ActiveOn THEN 'Before Contract Start'
        WHEN ContractDetail.ExpiresOn > Contract.ExpiresOn THEN 'After Contract End'
        END,
    CONVERT(VARCHAR(10), dbo.fn_UTCToLocalTime(Contract.ActiveOn), 101)

       AS "Contract ActiveOn", 
    CONVERT(VARCHAR(10), dbo.fn_UTCToLocalTime(ContractDetail.ActiveOn), 101)

       AS "Contract Detail ActiveOn", 
    CONVERT(VARCHAR(10), dbo.fn_UTCToLocalTime(Contract.ExpiresOn), 101)

       AS "Contract ExpiresOn",
    CONVERT(VARCHAR(10), dbo.fn_UTCToLocalTime(ContractDetail.ExpiresOn), 101)

       AS "Contract Detail ExpiresOn",
    Contract.Title, 
    ContractDetail.Title AS "Contract Detail Title",
    'http://[crm server]/[organization]/userdefined/edit.aspx?etc=1010&id=%7b' + 

     convert(nvarchar(50), Contract.ContractId) + '%7d'

FROM         
    Contract INNER JOIN
        ContractDetail ON Contract.ContractId = ContractDetail.ContractId
WHERE
    (ContractDetail.ActiveOn < Contract.ActiveOn
    OR
    ContractDetail.ExpiresOn > Contract.ExpiresOn)
    AND
    Contract.StateCode in (1,2,3)

     --1= Draft
     --2= Invoiced
     --3= Active
     --4= On Hold
     --5= Canceled
     --6= Expired
 

I have included a column containing a URL that will open the parent contract. If you need to use this line, just replace the [crm server] and [organization] placeholders with the proper values.

Also, I am filtering the Contracts based on their StateCode.  The comment block at the end of the SQL script lists the available Contract states should you wish to modify the query.

You have three courses of action once you have this data:

1. Change the contract start and stop dates using direct SQL (unsupported, but fast).

2. Write a small application that uses the CRM SDK to correct the dates.

3. Manually delete the offending Contract Detail line and add another.