select unitOfMeasurePlural, planningMethod from tblManufacturers where mfr_id = select groupID from tbl_SEC_Groups with (nolock) where GroupTypeCD = 'MRR' and ownerid= 0 and FSL_TableCode = 'SYS' and ( groupname = 'Distributor Management' or groupname = 'Operator Selling' ) select teamMemberRole from qmd_teamMembers where teamMemberUserId = and teamMemberRole like 'MPR:%' select * from qmd_stages where statusCode = 'A' order by likelihoodpercent select * from qmd_stages where statusCode = 'C' order by likelihoodpercent select spl.opportunityId, spl.stageId, spl.availableDate, spl.updateDate, opr.territoryId territoryId, spl.salesPersonUserId, spl.caseAmt, spl.incomeAmt, spl.weightAmt, spl.fsePlanTerritoryId, spl.fseAcctTerritoryId, spl.durationUnit,spl.QuantityAmt, coalesce( spl.mfrPlanStart, -- tbp.startDate case when spl.availableDate is not null then convert( datetime, convert( varchar, datepart( year, spl.availableDate )) + '-1-1' ) else convert( datetime, convert( varchar, datepart( year, getDate() )) + '-1-1' ) end ) planStartDate , coalesce( spl.mfrPlanEnd, -- tbp.endDate case when spl.availableDate is not null then convert( datetime, convert( varchar, datepart( year, spl.availableDate )) + '-12-31' ) else convert( datetime, convert( varchar, datepart( year, getDate() )) + '-12-31' ) end ) planEndDate, s.statusCode, s.stateCode, t.typeCode from tbl_SPL_Opportunities spl with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.operatorId = spl.partnerId and opr.fsltablecode = spl.ownerType and opr.ownerId = spl.ownerid inner join tbl_SPL_Stages s with (nolock) on s.stageid = spl.stageid and s.ownerId = spl.ownerId and s.ownerType = spl.ownerType inner join tbl_SPL_OpportunityTypes t with (nolock) on t.typeCode = spl.typeCode where spl.ownerId = and spl.ownerType = and spl.stageId in ( ) and ( spl.fsePlanTerritoryId in ( ) andor spl.salesPersonUserId in ( ) ) and spl.availableDate between '#lsdateformat( _fiscalPeriodRange.from, "YYYY-MM-DD")#' and '#lsdateformat( _fiscalPeriodRange.to )#' and spl.partnerType = 'OPR' and spl.typeCode <> 'PRO' and opr.crmActive = 'Y' and spl.mfrId in ( ) and ( spl.salesPersonUserId in ( ) or ( nullif( spl.salesPersonUserId, 0 ) is null and opr.mfr_bsr_id = ) ) union select spl.opportunityId, spl.stageId, spl.availableDate, spl.updateDate, null territoryId, spl.salesPersonUserId, spl.caseAmt, spl.incomeAmt, spl.weightAmt, spl.fsePlanTerritoryId, spl.fseAcctTerritoryId, spl.durationUnit,spl.QuantityAmt, coalesce( spl.mfrPlanStart, -- tbp.startDate case when spl.availableDate is not null then convert( datetime, convert( varchar, datepart( year, spl.availableDate )) + '-1-1' ) else convert( datetime, convert( varchar, datepart( year, getDate() )) + '-1-1' ) end ) planStartDate , coalesce( spl.mfrPlanEnd, -- tbp.endDate case when spl.availableDate is not null then convert( datetime, convert( varchar, datepart( year, spl.availableDate )) + '-12-31' ) else convert( datetime, convert( varchar, datepart( year, getDate() )) + '-12-31' ) end ) planEndDate, s.statusCode, s.stateCode, t.typeCode from tbl_SPL_Opportunities spl with (nolock) inner JOIN tbl_CDR_Distributors cdr with (nolock) ON cdr.fsl_tablecode = spl.ownerType and cdr.ownerId = spl.ownerId and cdr.cdr_recordId = spl.partnerId inner join tbl_SPL_Stages s with (nolock) on s.stageid = spl.stageid and s.ownerId = spl.ownerId and s.ownerType = spl.ownerType inner join tbl_SPL_OpportunityTypes t with (nolock) on t.typeCode = spl.typeCode where spl.ownerId = and spl.ownerType = and spl.stageId in ( ) and ( spl.fsePlanTerritoryId in ( ) andor spl.salesPersonUserId in ( ) ) and spl.availableDate between '#lsdateformat( _fiscalPeriodRange.from, "YYYY-MM-DD")#' and '#lsdateformat( _fiscalPeriodRange.to )#' and spl.partnerType = 'CDR' and spl.typeCode <> 'PRO' and cdr.crmActive = 'Y' and spl.mfrId in ( ) and ( spl.salesPersonUserId in ( ) or ( nullif( spl.salesPersonUserId, 0 ) is null and cdr.mfr_bsr_id = ) ) select sum( caseAmt ) cases, sum( weightAmt ) lbs, sum( incomeAmt ) dollars, count(*) opportunityCount from qmd_sales qmd_data = queryNew("saleslabel,opportunities,dollars,index,add", "varchar,varchar,varchar,integer,varchar"); i = 1; queryAddRow(qmd_data); querySetCell(qmd_data, "saleslabel",'#lsnumberformat( val( qmd_saleSummary[_uom][1] ))# #htmleditformat( _uomLabel )# '); if (val( qmd_saleSummary.dollars )) { querySetCell(qmd_data, "dollars",'#lsNumberformat(val( qmd_saleSummary.dollars ))# dollars'); } if (qmd_saleSummary.opportunityCount gt 0 and listFind( client.permissionList, "OpportunityPipelineApp" )) { querySetCell(qmd_data, "opportunities",'view #lsnumberformat( qmd_saleSummary.opportunityCount )# sold opportunities'); } querySetCell(qmd_data, "index","#i#"); if (cgi.server_name eq "dev.fsenablers.com") querySetCell(qmd_data, "add","#_qr_sql#"); ; i = i + 1;