<cfparam name="attributes.datasource">
<cfparam name="attributes.queryName">

<cfquery name="caller.#attributes.queryName#" datasource="#attributes.datasource#">
	select cdr.ownerid, cdr.fsl_tablecode ownerType,
		   'DATA' as anomalyType,
		   'mappingPriorityDetector' detectorName, dst.dstname, 'More than one Branch is mapped to ' + dst.dstname + ' (' + convert( varchar, cdr.fsl_dstId ) +
		   ')' as message,
		   count(*) as anomalyCount
	  from tbl_CDR_Distributors cdr

     inner join tbl_DST_Distributors dst
	    on cdr.fsl_dstId = dst.dstId

     where cdr.cdr_dstcompanytype = 'B'
	   and nullif( cdr.fsl_dstId, 0 ) is not null
	   and cdr.ownerId > 0

     group by cdr.ownerId, cdr.fsl_tablecode, dst.dstname, cdr.fsl_dstId
    having count(*) > 1

     union

	select cdrS.ownerId, cdrS.fsl_tablecode ownerType,
			 'DATA' anomalyType,
			 'mappingPriorityDetector' detectorName, dst.dstname, 'Special Account is mapped to ' + dst.dstName + ' (' + convert( varchar, cdrS.fsl_dstId ) +
			 ') but is missing a corresponding Branch distributor.' as message,
			 1 anomalyCount
	  from tbl_DST_Distributors dst

	 inner join tbl_CDR_Distributors cdrS
		on cdrS.fsl_dstId = dst.dstId
	   and cdrS.cdr_dstcompanytype = 'S'

	  left outer join tbl_CDR_Distributors cdrB
	    on cdrB.ownerId = cdrS.ownerId
	   and cdrB.fsl_tablecode = cdrS.fsl_tablecode
	   and cdrB.fsl_dstId = dst.dstId
	   and cdrB.cdr_dstcompanytype = 'B'

	 where cdrS.ownerId > 0
	   and nullif( cdr.fsl_dstId, 0 ) is not null
	   and nullif( cdrS.cdr_recordId, '' ) is not null
	   and nullif( cdrB.cdr_recordId, '' ) is null
	 group by cdrS.ownerId, cdrS.fsl_tablecode, dst.dstname, cdrS.fsl_dstId

	 order by ownerId, ownerType, dstname
</cfquery>