with promotionLookup as ( ) select coalesce( nullif( i.promotionName, '' ), 'BLANK' ) promotionName, count(*) lines, min( eai_lineNumber ) firstLine from tbl_IMPORT_RPCSalesDeals i left outer join promotionLookup l on l.promotionName = i.promotionName where i.ownerId = and i.ownerType = and i.eai_batchId = and l.promotionName is null group by i.promotionName order by lines desc, promotionName select distinct customerCode from tbl_IMPORT_RPCSalesDeals where nullif( customerCode, '' ) is not null and ownerId = and ownerType = and eai_batchId = update tbl_IMPORT_RPCSalesDeals set fsePartnerType = , fsePartnerId = , fsePrimaryPartner = where customerCode = and eai_batchId = and ownerId = and ownerType = select cdr_recordId from tbl_CDR_Distributors with (nolock) where ownerId = and FSL_TableCode = and (cdr_dstCode like or cdr_dstCode = ) and cdr_dstCompanyType <> 'S' update tbl_IMPORT_RPCSalesDeals set fsePartnerType = , fsePartnerId = , fsePrimaryPartner = where customerCode = and eai_batchId = and ownerId = and ownerType = union select cdr.cdr_recordId as partnerId, 'CDR' partnerType, cdr.ownerId, cdr.fsl_TableCode as ownerType, cdr.cdr_DstName as companyName,cdr.cdr_DstCity as city,cdr.cdr_DstState as state from tbl_CDR_Distributors cdr with (nolock) where cdr.ownerId = and cdr.fsl_tablecode = and cdr.cdr_dstCompanyType <> 'S' ) ---> with promotionLookup as ( where primaryPartnerType = 'OPR' ) select sd.blacksmithPromotion, sd.customerCode, p.cdr_recordId as partnerId, 'CDR' as partnerType, p.cdr_dstName as companyName,sd.dealName,sd.eai_lineNumber as lineNumber, sd.eai_importId as importId, sd.matchCount from tbl_IMPORT_RPCSalesDeals sd with (nolock) inner join promotionLookup on promotionLookup.promotionName = sd.promotionName left outer join tbl_CDR_Distributors p on p.cdr_recordId = sd.fsePartnerId and p.ownerId = sd.ownerId and p.fsl_tablecode = sd.ownerType where sd.ownerId = and sd.ownerType = and sd.eai_batchId = and sd.fsePartnerType = 'CDR' and nullif( sd.customerCode, '' ) is not null select distinct blacksmithPromotion, dealName from qmd_getDealsAndDistOpr select partnerId,partnerType, companyName, dealName, lineNumber, importId, matchCount,customerCode, 0 instances from qmd_getDealsAndDistOpr where dealName = and blacksmithPromotion = update tbl_IMPORT_RPCSalesDeals set matchCount = where ownerId = and ownerType = and eai_lineNumber = and eai_importId = and eai_batchId = select distinct partnerId, partnerType from qmd_AllDistWithSameDealName where matchCount = select distinct partnerId,partnerType, companyName, dealName, lineNumber, importId, matchCount,customerCode,instances from qmd_AllDistWithSameDealName where matchCount > 0 and matchCount = order by matchCount desc update tbl_IMPORT_RPCSalesDeals set fsePrimaryPartner = where ownerId = and ownerType = and eai_batchId = and eai_lineNumber = and eai_importId = update tbl_IMPORT_RPCSalesDeals set matchCount = -1 where ownerId = and ownerType = and eai_batchId = and fsePartnerId in () ---> and fsePrimaryPartner = and matchCount = and dealName = ---> with partners as ( select opr.operatorId as partnerId, 'OPR' partnerType, opr.ownerId, opr.fslTableCode as ownerType, opr.companyName as companyName ,opr.city as city,opr.state as state from tbl_OPR_CLientOperators opr with (nolock) where opr.ownerId = and opr.fsltablecode = and opr.crmactive = 'Y' union select cdr.cdr_recordId as partnerId, 'CDR' partnerType, cdr.ownerId, cdr.fsl_TableCode as ownerType, cdr.cdr_DstName as companyName,cdr.cdr_DstCity as city,cdr.cdr_DstState as state from tbl_CDR_Distributors cdr with (nolock) where cdr.ownerId = and cdr.fsl_tablecode = and cdr.crmActive = 'Y' ) ,promotionLookup as ( where primaryPartnerType = 'OPR' ) select sd.dealName,p.companyName, sd.matchCount, sd.customerCode, p.partnerId, p.partnerType from tbl_IMPORT_RPCSalesDeals sd with (nolock) inner join promotionLookup on promotionLookup.promotionName = sd.promotionName left outer join partners p on p.partnerId = sd.fsePartnerId and p.partnerType = sd.fsePartnerType and p.ownerId = sd.ownerId and p.ownerType = sd.ownerType where fsePrimaryPartner = 'Y' and matchCount > 0 and nullif( sd.customerCode, '' ) is not null and sd.ownerId = and sd.ownerType = and sd.eai_batchId = order by dealName, p.companyName with partners as ( select opr.operatorId as partnerId, 'OPR' partnerType, opr.ownerId, opr.fslTableCode as ownerType, opr.companyName as companyName ,opr.city as city,opr.state as state from tbl_OPR_CLientOperators opr with (nolock) where opr.ownerId = and opr.fsltablecode = and opr.crmactive = 'Y' union select cdr.cdr_recordId as partnerId, 'CDR' partnerType, cdr.ownerId, cdr.fsl_TableCode as ownerType, cdr.cdr_DstName as companyName,cdr.cdr_DstCity as city,cdr.cdr_DstState as state from tbl_CDR_Distributors cdr with (nolock) where cdr.ownerId = and cdr.fsl_tablecode = and cdr.crmActive = 'Y' ) ,promotionLookup as ( where primaryPartnerType = 'OPR' ) select distinct p.partnerId, p.partnerType, sd.dealName,p.companyName, sd.matchCount, sd.customerCode from tbl_IMPORT_RPCSalesDeals sd with (nolock) inner join promotionLookup on promotionLookup.promotionName = sd.promotionName inner join partners p on p.partnerId = sd.fsePartnerId and sd.fsePartnerType in ('CDR','OPR') and p.ownerId = sd.ownerId and p.ownerType = sd.ownerType where fsePrimaryPartner = 'N' and nullif( sd.customerCode, '' ) is not null and sd.ownerId = and sd.ownerType = and sd.eai_batchId = order by dealName, p.companyName ---> with promotionLookup as ( where primaryPartnerType = 'OPR' ) ,noPrimary as ( select distinct d.promotionName, d.blacksmithPromotion, d.dealName, d.startDate, d.endDate from tbL_IMPORT_RPCSalesDeals d with (nolock) inner join promotionLookup on promotionLookup.promotionName = d.promotionName and promotionLookup.primaryPartnerType = 'OPR' where nullif( customerCode, '' ) is not null and d.eai_batchId = and d.ownerId = and d.ownerType = and not exists ( select * from tbl_IMPORT_RPCSalesDeals d2 with (nolock) where d2.dealName = d.dealName and d2.fsePrimaryPartner = 'Y' and d2.ownerId = d.ownerId and d2.ownerType = d.ownerType and d2.eai_batchId = d.eai_batchId) ) select * from noPrimary order by promotionName, dealName

No Agreement Load Initiated

--->