select distinct t.territoryId, t.name from tbl_BRO_PrincipalTerritoryResources ptr inner join tbl_BRO_PrincipalTerritories pt on pt.principalTerritoryId = ptr.principalTerritoryId and pt.ownerId = ptr.ownerId and pt.ownerType = ptr.ownerType inner join tbl_BRO_Principals p on p.principalId = pt.principalId and p.ownerId = pt.ownerId and p.ownerType = pt.ownerType inner join tbl_TER_Territories t on t.territoryId = ptr.resourceId and t.ownerId = ptr.ownerId and t.fsl_tablecode = ptr.ownerType where p.ownerId = and p.ownerType = and p.mfrId = and ptr.resourceType = 'TER' order by t.name select territoryId from tbl_TER_Territories where ownerId = and fsl_tablecode = select distinct source_co_type, source_co_name from tbl_IMPORT_Opportunities2 where eai_batchId = update i set i.fseBrokerId = b.broker_id from tbl_IMPORT_Opportunities2 i inner join tblBrokers b on b.broker_name = i.source_co_name inner join tbl_SMA_BrokerOfficeLink sma on sma.mfr_id = and sma.brokerId = b.broker_id where i.eai_batchId = and i.ownerId = and i.ownerType = and left( i.source_co_type, 1 ) = 'B' update i set i.fseBrokerId = b.broker_id from tbl_IMPORT_Opportunities2 i inner join tblBrokers b on b.broker_name = i.source_co_name and b.broker_id = where i.eai_batchId = and i.ownerId = and i.ownerType = and left( i.source_co_type, 1 ) = 'B' select unitOfMeasurePlural, planningMethod from tblManufacturers where mfr_id = update i set i.fseMfrId = m.mfr_id from tbl_IMPORT_Opportunities2 i inner join tblManufacturers m on m.mfr_name = i.product_manufacturer and m.mfr_id = inner join tblMfrBrokerLink mbl on mbl.mfr_id = m.mfr_id and mbl.broker_id = where i.eai_batchId = and i.ownerId = and i.ownerType = update i set i.fseAliasPrefix = null from tbl_IMPORT_Opportunities2 i where i.eai_batchId = update i set i.fseAliasPrefix = left( i.ownerType, 1 ) + ':' + convert( varchar, i.ownerId ) + ';' + left( i.source_co_type, 1 ) + ':' + convert( varchar, i.fseBrokerId ) + ';' from tbl_IMPORT_Opportunities2 i where i.eai_batchId = and left( i.source_co_type, 1 ) = 'B' and i.fseBrokerId is not null update i set i.fseAliasPrefix = left( i.ownerType, 1 ) + ':' + convert( varchar, i.ownerId ) + ';' + left( i.source_co_type, 1 ) + ':' + convert( varchar, i.fseMfrId ) + ';' from tbl_IMPORT_Opportunities2 i where i.eai_batchId = and left( i.source_co_type, 1 ) = 'M' and i.fseMfrId is not null update i set i.fseInclude = 'N', i.fseIncludeReason = 'No Alias Prefix' from tbl_IMPORT_Opportunities2 i where i.eai_batchId = and i.fseAliasPrefix is null and i.fseInclude <> 'N' select eai_importId, company_type, opportunity_id, opportunity_name, opportunity_status, opportunity_campaign, opportunity_success_probability, opportunity_stage_name, opportunity_status_comment, opportunity_competitive_comment, opportunity_est_order_date, opportunity_type, opportunity_quantity_amt, opportunity_quantity_uom, opportunity_dollar_amt, opportunity_mfr_ref_nbr, opportunity_likely_distributor, product_id, product_level, product_manufacturer, opportunity_create_dt, opportunity_last_update_dt, fseOperatorId, fseDistributorId, fseOpportunityId, fseOpportunityStatus, fseContactId, fseSalesPersonId, fseStageId, fseCampaignId, fseBrokerId, fseMfrId, fseProdId, fseSkuId, fseMktCategoryId, fseMktSubCategoryId, source_co_type, fseNoSaleReasonId, fseLikelyDistributorId from tbl_IMPORT_Opportunities2 i where i.eai_batchId = and i.ownerId = and i.ownerType = and ( i.fseOperatorId is not null or i.fseDistributorId is not null ) and i.fseStageId is not null and i.fseMfrId is not null and i.fseProdId is not null and i.fseInclude = 'Y' Add #opportunity_name#
createOppAttrs.ownerId = qmd_batch.ownerId; createOppAttrs.ownerType = qmd_batch.ownerType; createOppAttrs.datasource = attributes.datasource; if( company_type eq "OPERATOR" ) { createOppAttrs.partnerId = fseOperatorId; createOppAttrs.partnerType = "OPR"; if( val( fseLikelyDistributorId )) { createOppAttrs.refDistributorId = fseLikelyDistributorId; } } else if( company_type eq "DISTRIBUTOR" ) { createOppAttrs.partnerId = fseDistributorId; createOppAttrs.partnerType = "CDR"; } createOppAttrs.opportunityName = opportunity_name; createOppAttrs.stageId = fseStageId; if( isDate( opportunity_est_order_date ) ) { createOppAttrs.availableDate = opportunity_est_order_date; } else { // todo: set to last day of fiscal year createOppAttrs.availableDate = CreateDate( Year(Now()), 12, 31 ); } //createOppAttrs.quantityAmt = val( opportunity_quantity_amt ); // createOppAttrs.incomeAmt = val( opportunity_dollar_amt ); createOppAttrs.incomeAmt = _incomeAmt; createOppAttrs.autoIncomeAmt = _autoIncomeAmt; createOppAttrs.weightAmt = _weightAmt; createOppAttrs.autoWeightAmt = _autoWeightAmt; createOppAttrs.caseAmt = _caseAmt; createOppAttrs.autoCaseAmt = _autoCaseAmt; createOppAttrs.unitPrice = 0; if( opportunity_type eq "LTO" ) { createOppAttrs.durationUnit = "WK"; // todo: need to bring this in from the file createOppAttrs.durationAmt = 1; } else { createOppAttrs.durationUnit = "YR"; createOppAttrs.durationAmt = 1; } // todo: correctly set subject id and subject type createOppAttrs.subjectId = rereplace( val( fseProdId ), "\.\d*$", "" ); createOppAttrs.subjectType = "PRD"; if( val( fseSkuId )) { createOppAttrs.detailId = fseSkuId; } createOppAttrs.typeCode = "NEW"; // for now it is always new business if( request.mfrLoad or request.broLoad or request.bro2MfrLoad ) { if( val( fseSalesPersonId )) { createOppAttrs.salesPersonUserId = fseSalesPersonId; } } createOppAttrs.opportunityDescription = opportunity_status_comment; if( qmd_batch.ownerType eq "MFR" ) { createOppAttrs.mfrId = qmd_batch.ownerId; } if( val( fseCampaignId )) { createOppAttrs.campaignId = fseCampaignId; } /* don't add contact */ createOppAttrs.contactId = -1; if( qmd_batch.ownerType eq "BRO" and left( source_co_type, 1 ) eq "M" ) { createOppAttrs.mfrRefNbr = opportunity_mfr_ref_nbr; } if( request.mfrLoad or request.broLoad) { createOppAttrs.sourceOwnerId = qmd_batch.ownerId; createOppAttrs.sourceOwnerType = qmd_batch.ownerType; } else if ( request.bro2MfrLoad ) { createOppAttrs.sourceOwnerId = fseBrokerId; createOppAttrs.sourceOwnerType = "BRO"; } else if ( request.mfr2BroLoad ) { createOppAttrs.sourceOwnerId = fseMfrId; createOppAttrs.sourceOwnerType = "MFR"; } createOppAttrs.nosaleReasonId = val( fseNoSaleReasonId ); createOppAttrs.competitiveComment = trim( opportunity_competitive_comment ); update tbl_IMPORT_Opportunities2 set fseOpportunityId = where eai_importId = and eai_batchId = and ownerId = and ownerType = update tbl_SPL_Opportunities set contactId = where opportunityId = update tbl_SPL_Opportunities set recordSource = where opportunityId = and ownerId = and ownerType = Update #opportunity_name#
select stageId, opportunityDescription, availableDate, incomeAmt, quantityAmt, durationUnit from tbl_SPL_Opportunities where ownerId = and ownerType = and opportunityId = update spl set spl.stageId = , spl.opportunityName = , spl.salesPersonUserId = , spl.quantityAmt = , spl.caseAmt = , spl.autoCaseAmt = , spl.weightAmt = , spl.autoWeightAmt = , spl.incomeAmt = , spl.autoIncomeAmt = , spl.subjectId = , spl.subjectType = , spl.opportunityDescription = , spl.durationUnit = , spl.durationAmt = 1 , spl.durationUnit = , spl.durationAmt = 1 , spl.availableDate = , spl.updateDate = , spl.campaignId = , spl.noSaleReasonId = , spl.competitiveComment = , spl.cdrDistributorId = from tbl_SPL_Opportunities spl where ownerId = and ownerType = and opportunityId = set nocount on insert into tbl_SPL_Opportunity_StageChanges ( opportunityId, previousStageId, newStageId, changeDateTime, fspro_userId, ipaddress, event, message ) values ( , , , , , , , null, , ) select stageChangeId = @@identity set nocount off insert into tbl_IMPORT_Aliases ( ownerId, ownerType, objectId, objectType, alias ) select distinct ownerId, ownerType, fseOpportunityId, 'SPL', i.fseAliasPrefix + 'SPL:' + i.opportunity_id from tbl_IMPORT_Opportunities2 i where i.fseOpportunityStatus = 'A' and i.fseOpportunityId is not null and i.eai_batchId = and i.ownerId = and i.ownerType = and nullif( i.opportunity_id, '' ) is not null #resultsHTML# #_batchId# #resultsHTML# Source file attached. ---> #_batchId# #resultsHTML# Source file attached.