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 teamMemberRole from qmd_teamMembers where teamMemberUserId = and teamMemberRole like 'MPR:%' ---> select * from qmd_stages where statusCode = 'A' order by likelihoodpercent #_jsonString# ---> select * from qmd_stages where statusCode = 'C' order by likelihoodpercent select top 250 * from ( select spl.opportunityId,spl.opportunityname, spl.stageId,stg.likelihoodpercent, spl.availableDate, spl.updateDate, opr.territoryId territoryId, spl.salesPersonUserId, spl.caseAmt, spl.incomeAmt, spl.weightAmt, spl.fsePlanTerritoryId, spl.fseAcctTerritoryId,spl.typeCode, case when spl.availabledate < '#lsdateformat( now(), "MM/DD/YYYY")#' then 1 else 0 end as PastDue, case when datediff( day, spl.updateDate, '#lsdateformat( now(), "MM/DD/YYYY")#' ) > #request.portalConfiguration.apps.SPL.alerts.notupdated.daysthreshold# then 1 else 0 end as NotUpdated from tbl_SPL_Opportunities spl with (nolock) inner join tbl_OPR_ClientOperators opr with (nolock) on opr.operatorId = spl.partnerId left outer join tbl_CRM_Alerts aPD on aPD.subjectType = 'SPL' and aPD.subjectId = spl.opportunityId and aPD.alertType = 'pastDue' left outer join tbl_CRM_Alerts aNU on aNU.subjectType = 'SPL' and aNU.subjectId = spl.opportunityId and aNU.alertType = 'notUpdated' left outer join tbl_SPL_Stages stg on stg.OwnerId = spl.OwnerID and stg.OwnerType = spl.OwnerType and stg.StageId = spl.StageId where spl.ownerId = and spl.ownerType = and spl.stageId in ( ) and ( spl.fsePlanTerritoryId in ( ) andor spl.salesPersonUserId in ( ) ) and ( spl.availableDate >= getdate() or spl.availableDate >= spl.updateDate or datediff( day, spl.updateDate, '#lsdateformat( now(), "MM/DD/YYYY")#' ) > 60 or -- NotUpdated spl.availabledate < '#lsdateformat( now(), "MM/DD/YYYY")#' -- PastDue ) and spl.availableDate between '#lsdateformat( _fiscalPeriodRange.from, "YYYY-MM-DD")#' and '#lsdateformat( _fiscalPeriodRange.to )#' and spl.partnerType = 'OPR' 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.opportunityname, spl.stageId,stg.likelihoodpercent, spl.availableDate, spl.updateDate, cdr.cdr_territoryId territoryId, spl.salesPersonUserId, spl.caseAmt, spl.incomeAmt, spl.weightAmt, spl.fsePlanTerritoryId, spl.fseAcctTerritoryId,spl.typeCode, case when spl.availabledate < '#lsdateformat( now(), "MM/DD/YYYY")#' then 1 else 0 end as PastDue, case when datediff( day, spl.updateDate, '#lsdateformat( now(), "MM/DD/YYYY")#' ) > #request.portalConfiguration.apps.SPL.alerts.notupdated.daysthreshold# then 1 else 0 end as NotUpdated from tbl_SPL_Opportunities spl with (nolock) inner join tbl_CDR_Distributors cdr with (nolock) on cdr.cdr_recordId = spl.partnerId left outer join tbl_CRM_Alerts aPD on aPD.subjectType = 'SPL' and aPD.subjectId = spl.opportunityId and aPD.alertType = 'pastDue' left outer join tbl_CRM_Alerts aNU on aNU.subjectType = 'SPL' and aNU.subjectId = spl.opportunityId and aNU.alertType = 'notUpdated' left outer join tbl_SPL_Stages stg on stg.OwnerId = spl.OwnerID and stg.OwnerType = spl.OwnerType and stg.StageId = spl.StageId where spl.ownerId = and spl.ownerType = and spl.stageId in ( ) and ( spl.fsePlanTerritoryId in ( ) andor spl.salesPersonUserId in ( ) ) and ( spl.availableDate >= dateadd(year, -1, getdate()) or spl.availableDate >= spl.updateDate or datediff( day, spl.updateDate, '#lsdateformat( now(), "MM/DD/YYYY")#' ) > 60 or -- NotUpdated spl.availabledate < '#lsdateformat( now(), "MM/DD/YYYY")#' -- PastDue ) and spl.availableDate between '#lsdateformat( _fiscalPeriodRange.from, "YYYY-MM-DD")#' and '#lsdateformat( _fiscalPeriodRange.to )#' and spl.partnerType = 'CDR' and cdr.crmActive = 'Y' and spl.mfrId in ( ) and ( spl.salesPersonUserId in ( ) or ( nullif( spl.salesPersonUserId, 0 ) is null and cdr.mfr_bsr_id = ) ) ) t where 1 = 1 and PastDue = 1 NotUpdated = 1 concat(round( likelihoodpercent * 100,0 ),'%') = select min( availableDate ) eodFrom, max( availableDate ) eodTo from qmd_opportunities PastDue gt 0 > ( NotUpdated gt 0 ) > select distinct fsePlanTerritoryId from qmd_opportunities _colorList = '##35A6FF,##1E74FF,##0040FF,##45FFD4,##4BFFFF,##45D4FF'; qmd_data = queryNew("label,value,color,index,add,shownow", "varchar,integer,varchar,integer,varchar,varchar"); i = 1; _colorArray = listToArray(_colorList,","); queryAddRow(qmd_data); querySetCell(qmd_data, "label","#round( likelihoodpercent * 100 )#%"); querySetCell(qmd_data, "value","#_stageCounts[stageId]#"); querySetCell(qmd_data, "color","#_colorArray[i]#"); querySetCell(qmd_data, "index","#i#"); i = i + 1; queryAddRow(qmd_data); querySetCell(qmd_data, "label","Past Due"); querySetCell(qmd_data, "value","#_pastDue#"); querySetCell(qmd_data, "color","##ff1300"); querySetCell(qmd_data, "index","#i#"); /*SCRUM 29483 - JF - 03/05/2021 - 'Not Update' will only shows if enabled in the Portal Builder*/ if (request.portalConfiguration.apps.SPL.alerts.notupdated.showInPortlet eq "true") { i = i + 1; queryAddRow(qmd_data); querySetCell(qmd_data, "label","Not Updated"); querySetCell(qmd_data, "value","#_needUpdating#"); querySetCell(qmd_data, "color","##ffdb4c"); querySetCell(qmd_data, "index","#i#"); } if (cgi.server_name eq "dev.fsenablers.com") querySetCell(qmd_data, "add","#_qr_sql#"); querySetCell(qmd_data, "shownow","#_currentSettings.fiscalPeriod#"); ; i = i + 1;

opportunityId	opportunityName	stageid	likelihoodpercent	availableDate	updateDate	territoryId	salesPersonUserId	caseAmt	incomeAmt	weightAmt	fsePlanTerritoryId	fseAcctTerritoryId	PastDue	NotUpdated
#opportunityId#	#opportunityName#	#stageid#	#likelihoodpercent * 100#%	#dateformat(availableDate,"mm/dd/yyyy")#	#dateformat(updateDate,"mm/dd/yyyy")#	#territoryId#	#salesPersonUserId#	#caseAmt#	#incomeAmt#	#weightAmt#	#fsePlanTerritoryId#	#fseAcctTerritoryId#	#PastDue#	#NotUpdated#