Panic by a customer this week: they were looking to find all the details on invoices and who the sales people are. 

Running this query, they found a whole bunch of seemingly duplicate invoice detail lines. So I got a flaming e-mail back from the controller worried that we were not able to tie out the invoice totals in CRM and SL. 

SELECT DISTINCT

A.OwnerIdName AS [Salesperson],

A.name AS [Acct Name],

A.accountnumber AS [Solomon Acct #],

A.statuscode as [Acct Status],

I.name AS [Invoice #],

I.InvoiceId AS [Invoice Id],

ID.invoicedetailid AS [Invoice Detail ID],

ID.productidname AS [Product],

ID.Quantity AS [Quantity],

ID.extendedamount AS [Extended Amt],

I.totalamount AS [Total Amt Billed]

FROM Account as A

inner join SalesOrder AS SO

ON A.AccountId = SO.AccountId

inner join Invoice AS I

ON SO.SalesOrderId = I.SalesOrderId

inner join InvoiceDetail AS ID

ON I.InvoiceId = ID.InvoiceId

ORDER BY A.OwnerIdName, A.name, I.InvoiceId, ID.invoicedetailid

After reviewing the query I noticed they were missing filtering out for the deletion state code. 

By just adding Where id.DeletionStateCode =0, the results tied out. It worked just fine. The CRM deletion process happens every 2 hours. So when we are looking at Sales and Invoice Detail, we will often see deleted lines that have not been cleaned out by the actual Deletion service yet.  

Why are there so many deleted lines you may ask?  When an invoice or Order is updated in SL, the update job in CRM actually deletes all the existing line items on the invoice or order and adds in what is in SL. This way we assure that CRM contains exactly what is in SL, because sometimes the actual line items change along the way.  It also keeps us from having to manage Xref for every AR or SO Line Items to CRM line items.  That may change with CRM 4.0 as they now seem to have added Line ID numbers to the schema.  

The application filters these line items out automatically when it fetches the active line items, so the invoice or order does not actually show these deleted items when it shows on the screen.  It applies the filter automatically.

There are 3 different values for deletion state code

DeletionStateCode