select * from qmd_stages where statusCode in ( ) select * from qmd_stages where statusCode <> 'A' or stageId in ( ) select * from qmd_stages where statusCode = SELECT * from qmd_stages WHERE stateCode = 'O' SELECT * from qmd_stages WHERE stateCode = 'C' SELECT * from qmd_stages WHERE stateCode = 'R' SELECT * from qmd_stages WHERE stateCode = 'K' SELECT * from qmd_stages WHERE ( stateCode = 'RX' and stageId not in ( ) ) or stateCode = 'K' opportunityAttributes.ownerId=attributes.ownerID; opportunityAttributes.ownerType=attributes.ownerType; opportunityAttributes.partnerType=attributes.partnerType; opportunityAttributes.includePartner=true; opportunityAttributes.subjectType=attributes.subjectType; opportunityAttributes.includeSubject=true; opportunityAttributes.financialMarginMethod = attributes.financialMarginMethod; opportunityAttributes.datasource=attributes.datasource; opportunityAttributes.campaignId = attributes.campaignId; opportunityAttributes.auditConfirmed = attributes.auditConfirmed; opportunityAttributes.alert = attributes.alert; opportunityAttributes.volumeType = attributes.volumeType; if( not isDefined( "url.xo_mfrId" ) ) { if( isDefined( "attributes.mfrId" ) and isNumeric( attributes.mfrId )) { opportunityAttributes.mfrId = attributes.mfrId; } } else { if( val( url.xo_mfrId )) { opportunityAttributes.mfrId = url.xo_mfrId; } } if( isDefined( "attributes.partnerName" )) { opportunityAttributes.partnerName = attributes.partnerName; } if( isDefined( "attributes.focusAccount" )) { opportunityAttributes.focusAccount = attributes.focusAccount; } if( isDefined( "attributes.priority" )) { opportunityAttributes.priority = attributes.priority; } if( isDefined( "attributes.focusProducts")) { opportunityAttributes.focusProducts = attributes.focusProducts; } if( isDefined( "attributes.sampleStatus")) { opportunityAttributes.sampleStatus = attributes.sampleStatus; } if( isDefined( "attributes.opportunityKeyword" )) { opportunityAttributes.opportunityKeyword = attributes.opportunityKeyword; } if( isDefined( "attributes.affiliationOrgId" )) { opportunityAttributes.affiliationOrgId = attributes.affiliationOrgId; } if( isDefined( "attributes.affiliationType" )) { opportunityAttributes.affiliationType = attributes.affiliationType; } if( attributes.stageId neq "") { opportunityAttributes.stageId = attributes.stageId; } else { opportunityAttributes.stageId= valueList( qmd_stages.stageId ); } if( qmd_suppressStages.recordCount gt 0 ) { opportunityAttributes.excludeStageId = valueList( qmd_suppressStages.stageId ); } opportunityAttributes.prefDistributorId = attributes.prefDistributorId; opportunityAttributes.refDistributorId = attributes.refDistributorId; if( isDefined( "attributes.refDstClsId" )) { opportunityAttributes.refDstClsId = attributes.refDstClsId; } if( isDefined( "attributes.parentDistributorId" )) { opportunityAttributes.parentDistributorId = attributes.parentDistributorId; } opportunityAttributes.stageId= valueList( qmd_stages.stageId ); opportunityAttributes.typeCode = attributes.typeCode; opportunityAttributes.volumeLowerLimit = attributes.volumeLowerLimit; opportunityAttributes.volumeUpperLimit = attributes.volumeUpperLimit; opportunityAttributes.createDateLowerLimit = attributes.createDateLowerLimit; opportunityAttributes.createDateUpperLimit = attributes.createDateUpperLimit; opportunityAttributes.updateDateLowerLimit = attributes.updateDateLowerLimit; opportunityAttributes.updateDateUpperLimit = attributes.updateDateUpperLimit; opportunityAttributes.bgtCatId = attributes.bgtCatId; opportunityAttributes.salesPersonId = attributes.salesPersonId; opportunityAttributes.salesPersonIdWT = attributes.salesPersonIdWT; opportunityAttributes.partnerParentId = attributes.partnerParentId; if( isDefined( "attributes.availableDateLowerLimit" )) { opportunityAttributes.availableDateLowerLimit = attributes.availableDateLowerLimit; } if( isDefined( "attributes.availableDateUpperLimit" )) { opportunityAttributes.availableDateUpperLimit = attributes.availableDateUpperLimit; } opportunityAttributes.customCode = attributes.customCode; opportunityAttributes.productClassification = attributes.productClassification; if( isDefined( "attributes.productCatalogLevel3" )) { opportunityAttributes.productCatalogLevel3 = attributes.productCatalogLevel3; } if( isDefined( "attributes.productCatalogLevel4" )) { opportunityAttributes.productCatalogLevel4 = attributes.productCatalogLevel4; } if( isDefined( "attributes.productBrandId" )) { opportunityAttributes.productBrandId = attributes.productBrandId; } opportunityAttributes.subjectDetailId = attributes.subjectDetailId; opportunityAttributes.territoryId=attributes.territoryId; opportunityAttributes.queryName="qmd_opportunitiesRaw"; opportunityAttributes.lastYearToView = Year( Now() ) - 2; opportunityAttributes.operatorSegment = attributes.operatorSegment; opportunityAttributes.operatorComSeg = attributes.operatorComSeg; opportunityAttributes.partnerClassification = attributes.partnerClassification; opportunityAttributes.zeroPastDueImpact = true; if( isDefined( "url.format" ) and url.format eq "xls" ) { opportunityAttributes.includeDetails = true; opportunityAttributes.bindSalesPerson = true; opportunityAttributes.zeroPastDueImpact = false; } else { opportunityAttributes.bindSalesPerson = false; } if( attributes.partnerId neq -1 ) { opportunityAttributes.partnerId = attributes.partnerId; } if( isDefined( "attributes.incSharedOpps" )) { opportunityAttributes.incSharedOpps = attributes.incSharedOpps; } if( isDefined( "attributes.PARTNERLISTID" ) and attributes.PARTNERLISTID neq "?") { opportunityAttributes.partnerListId = attributes.PARTNERLISTID; } if( isDefined( "attributes.assignedAccountsOnly" ) and attributes.assignedAccountsOnly) { opportunityAttributes.assignedAccountsOnly = attributes.assignedAccountsOnly; } if( isDefined( "attributes.linkedMfr" ) and isDefined( "attributes.linkedMfrPrty" ) and attributes.linkedMfr neq "?" and attributes.linkedMfrPrty neq "?" ) { opportunityAttributes.linkedMfr = attributes.linkedMfr; opportunityAttributes.linkedMfrPrty = attributes.linkedMfrPrty; } if( isDefined( "attributes.PARTNERLISTID" ) and attributes.PARTNERLISTID neq "?") { opportunityAttributes.partnerListId = attributes.PARTNERLISTID; } if( isDefined( "attributes.dollarLowerLimit" ) and attributes.dollarLowerLimit neq "?") { opportunityAttributes.dollarLowerLimit = attributes.dollarLowerLimit; } if( isDefined( "attributes.dollarUpperLimit" ) and attributes.dollarUpperLimit neq "?") { opportunityAttributes.dollarUpperLimit = attributes.dollarUpperLimit; } if( isDefined( "attributes.srchUnits" ) and attributes.srchUnits neq "?") { opportunityAttributes.srchUnits = attributes.srchUnits; } select statusCode, count(*) opportunityCount from qmd_opportunitiesRaw group by statusCode select * from qmd_opportunitiesRaw order by sequence, companyName, opportunityName