update i set i.fseOperatorId = o.fseOperatorId from tbl_IMPORT_Opportunities2 i inner join tbl_IMPORT_Operators o on o.external_id = i.company_id and o.ownerId = i.ownerId and o.ownerType = i.ownerType and o.eai_batchId = and o.fseOperatorId is not null where i.ownerId = and i.ownerType = and i.eai_batchId = and i.fseOperatorId is null select distinct cdr.salesForceId_cs, cdr.cdr_recordId from tbl_IMPORT_Opportunities2 i with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.ownerId = i.ownerId and cdr.fsl_tablecode = i.ownerType and cdr.salesForceId_cs = i.company_id collate Latin1_General_CS_AS where i.ownerId = and i.ownerType = and i.eai_batchId = and i.company_type = 'CDR' and i.fseDistributorId is null update i set i.fseDistributorId = from tbl_IMPORT_Opportunities2 i where i.ownerId = and i.ownerType = and i.eai_batchId = and i.company_type = 'CDR' and i.company_id collate Latin1_General_CS_AS = and i.fseDistributorId is null select opr.companyName, opr.operatorId partnerId, 'OPR' as partnerType, i.opportunity_ref, i.opportunity_name, stg.stageId, stg.statusCode, case when stg.statusCode = 'R' then i.fseNoSaleReasonId else cast( null as integer ) end noSaleReasonId, opportunity_est_order_date availableDate, m.fspro_userId salesPersonUserId, count(*) itemCount from tbl_IMPORT_Opportunities2 i with (nolock) inner join tbl_PRD_Skus s with (nolock) on s.skuId = i.fseSKUId and s.ownerId = i.ownerId and s.fsl_tablecode = i.ownerType inner join tbl_OPR_CLientOperators opr with (nolock) on opr.operatorId = i.fseOperatorId and opr.ownerId = i.ownerId and opr.fsltablecode = i.ownerType inner join tbl_SPL_Stages stg with (nolock) on stg.stageId = i.fseStageId and stg.ownerId = i.ownerId and stg.ownerType = i.ownerType inner join tbl_fspro_members m with (nolock) on m.fspro_userid = i.fseSalesPersonId and m.ownerId > 0 where i.eai_batchId = and i.ownerId = and i.ownerType = and i.company_type = 'OPR' group by opr.companyName, opr.operatorId, i.opportunity_ref, i.opportunity_name, stg.stageId, stg.statusCode, stg.statusCode, i.fseNoSaleReasonId, i.opportunity_est_order_date, m.fspro_userId order by i.opportunity_ref, partnerId, availableDate select cdr.cdr_dstName as companyName, cdr.cdr_recordId partnerId, 'CDR' as partnerType, i.opportunity_ref, i.opportunity_name, stg.stageId, stg.statusCode, case when stg.statusCode = 'R' then i.fseNoSaleReasonId else cast( null as integer ) end noSaleReasonId, opportunity_est_order_date availableDate, m.fspro_userId salesPersonUserId, count(*) itemCount from tbl_IMPORT_Opportunities2 i with (nolock) inner join tbl_PRD_Skus s with (nolock) on s.skuId = i.fseSKUId and s.ownerId = i.ownerId and s.fsl_tablecode = i.ownerType inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = i.fseDistributorId and cdr.ownerId = i.ownerId and cdr.fsl_tablecode = i.ownerType inner join tbl_SPL_Stages stg with (nolock) on stg.stageId = i.fseStageId and stg.ownerId = i.ownerId and stg.ownerType = i.ownerType inner join tbl_fspro_members m with (nolock) on m.fspro_userid = i.fseSalesPersonId and m.ownerId > 0 where i.eai_batchId = and i.ownerId = and i.ownerType = and i.company_type = 'CDR' group by cdr.cdr_dstName, cdr.cdr_recordId, i.opportunity_ref, i.opportunity_name, stg.stageId, stg.statusCode, stg.statusCode, i.fseNoSaleReasonId, i.opportunity_est_order_date, m.fspro_userId order by i.opportunity_ref, partnerId, availableDate select #qmd_headers_opr.columnList# from qmd_headers_opr union all select #qmd_headers_cdr.columnList# from qmd_headers_cdr order by opportunity_ref, partnerType, partnerId, availableDate select i.eai_lineNumber, s.skuId, s.prodId, convert( float, i.opportunity_quantity_amt ) as quantity, i.opportunity_quantity_uom as uom, i.opportunity_status_comment, i.opportunity_competitive_comment from tbl_IMPORT_Opportunities2 i with (nolock) inner join tbl_PRD_Skus s with (nolock) on s.skuId = i.fseSKUId and s.ownerId = i.ownerId and s.fsl_tablecode = i.ownerType where i.eai_batchId = and i.ownerId = and i.ownerType = and i.fseStageId = and i.fseOperatorId = and i.company_type = 'OPR' and i.fseDistributorId = and i.company_type = 'CDR' and i.fseSalesPersonId = and i.opportunity_ref = and i.opportunity_ref is null select spl.opportunityId, osd.subjectDetailId as skuId, spl.availableDate, spl.stageId, spl.noSaleReasonId, spl.caseAmt, spl.weightAmt, spl.incomeAmt, spl.quantityAmt, spl.salesPersonUserId from tbl_SPL_Opportunities spl with (nolock) inner join tbl_SPL_OpportunitySubjectDetails osd with (nolock) on osd.opportunityId = spl.opportunityId and osd.ownerId = spl.ownerId and osd.ownerType = spl.ownerType and osd.subjectDetailType = 'SKU' where spl.ownerId = and spl.ownerType = and spl.partnerId = and spl.partnerType = and osd.subjectDetailId = and spl.opportunityRef_caseSensitive = and spl.opportunityRef_caseSensitive is null update tbl_IMPORT_Opportunities2 set fseOpportunityId = , fseOpportunityStatus = 'A' where ownerId = and ownerType = and eai_batchId = and eai_lineNumber = update tbl_IMPORT_Opportunities2 set fseOpportunityId = , fseOpportunityStatus = 'U''-' where ownerId = and ownerType = and eai_batchId = and eai_lineNumber = select case when company_type = 'OPR' then 'Operator' when company_type = 'CDR' then 'Distributor' else 'Unknown' end Account_Type, case when fseOpportunityStatus = '-' then 'Existing Opportunities NOT Changed' when fseOpportunityStatus = 'A' then 'New Opportunities Added' when fseOpportunityStatus = 'U' then 'Existing Opportunities Changed' else 'Unknown Status ''' + fseOpportunityStatus + '''' end as Status, count( distinct fseOpportunityId ) Opportunities from tbl_IMPORT_Opportunities2 with (nolock) where eai_batchId = and ownerId = and ownerType = and fseOpportunityId > 0 group by company_type, fseOpportunityStatus order by Account_Type desc select count(*) lines_not_applied from tbl_IMPORT_Opportunities2 with (nolock) where eai_batchId = and ownerId = and ownerType = and nullif( fseOpportunityId, 0 ) is null select company_id_type, company_id_owner, company_id, company_name, company_address, company_city, company_state, company_zip, count(*) lines, min( eai_lineNumber ) firstLine from tbl_IMPORT_Opportunities2 with (nolock) where eai_batchId = and ownerId = and ownerType = and company_type = 'OPR' and fseOperatorId is null group by company_id_type, company_id_owner, company_id, company_name, company_address, company_city, company_state, company_zip order by firstLine select company_id_type, company_id_owner, company_id, company_name, company_address, company_city, company_state, company_zip, count(*) lines, min( eai_lineNumber ) firstLine from tbl_IMPORT_Opportunities2 with (nolock) where eai_batchId = and ownerId = and ownerType = and company_type = 'CDR' and fseDistributorId is null group by company_id_type, company_id_owner, company_id, company_name, company_address, company_city, company_state, company_zip order by firstLine select product_id as sku, product_name as description, min( eai_lineNumber ) firstLine, count(*) lines from tbl_IMPORT_Opportunities2 with (nolock) where eai_batchId = and ownerId = and ownerType = and fseSKUIdStatus = 'A' group by product_id, product_name order by lines desc select salesperson_first_name, salesperson_last_name, min( eai_lineNumber ) firstLine, count(*) lines from tbl_IMPORT_Opportunities2 with (nolock) where eai_batchId = and ownerId = and ownerType = and fseSalesPersonId is null group by salesperson_first_name, salesperson_last_name order by lines desc update tbl_EAI_inboundBatches set processingState = where batchId = and ownerId = and ownerType =