Forecast Quota - Please help with the code below : I'm trying to fetch the quota amount for each salesRep within the Custom Object , where in Custom object maintains QuoteLine details . This try and catch block is failing during data loader update.

// Caluclate the start date and end date
for (SBQQ__QuoteLine__c quoteLine : qline){
if( quoteLine.SBQQ__StartDate__c == null){
startDate = quoteLine.SBQQ__Quote__r.SBQQ__StartDate__c;
} else {
startDate = quoteLine.SBQQ__StartDate__c;
}
if( quoteLine.SBQQ__EndDate__c == null){
endDate = quoteLine.SBQQ__Quote__r.SBQQ__EndDate__c;
} else {
endDate = quoteLine.SBQQ__EndDate__c;
}

if(startDate!=null && endDate!=null){

Integer calculateMonths = startDate.monthsBetween(endDate);
System.debug(' Calculate Date = ' + calculateMonths);
Integer calculateDays = startDate.daysBetween(endDate) +1;
System.debug(' Calculate Days = ' + calculateDays);


for(i = 0 ; i <= calculateMonths ; i++) {
Integer numberOfDays = Date.daysInMonth(startDate.year(), startDate.month());
Date lastDayOfMonth = Date.newInstance(startDate.year(), startDate.month(), numberOfDays);
Integer daysInMonths = startDate.daysBetween(lastDayOfMonth)+1;

Integer lastMonth = endDate.month();
Integer lastYear = endDate.year();
String opportunityId = quoteLine.SBQQ__Quote__r.SBQQ__Opportunity2__c;
String salesRep = quoteLine.SBQQ__Quote__r.SBQQ__SalesRep__r.Name;
String salesRepID = quoteLine.SBQQ__Quote__r.SBQQ__SalesRep__r.Id;
String clientRelation = quoteLine.SBQQ__Quote__r.Client_Relationship__c;
Double probability = quoteLine.SBQQ__Quote__r.SBQQ__Opportunity2__r.Probability;

if(lastDayOfMonth > = endDate){
daysInMonths = startDate.daysBetween(endDate) +1;
lastDayOfMonth = endDate;
}
while(startDate < lastDayOfMonth)
{

Double calculatePrice = ((quoteLine.SBQQ__NetTotal__c/ calculateDays) * daysInMonths) * probability/100 ;
ForecastingQuota fq ;
System.debug(' Net price=' +quoteLine.SBQQ__NetTotal__c + ' CalculatetotalDays = ' +calculateDays + ' DaysinMonths =' +daysInMonths+ ' Probablility=' +probability);
System.debug(' startDate= ' +startDate+ ' daysInMonths = ' + daysInMonths+ ' calculatePrice = ' +calculatePrice+ ' lastDayOfMonth =' +lastDayOfMonth);
system.debug('Product Type = ' + quoteLine.SBQQ__ProductName__c + ' Quote Line =' +quoteLine.id);
Quote_Report__c qReport = new Quote_Report__c();
try{
date stdate = date.newInstance(startdate.year(), startDate.month(), 01);
fq = [Select QuotaAmount from ForecastingQuota where
startdate =: stdate
AND QuotaOwnerId =: salesRepID];
System.debug('++Fq' + fq);
qReport.Quota_amount__c = fq.QuotaAmount;
} catch(exception e){
system.debug('Exception') ;
}

qReport.Quote_ID__c = quoteLine.SBQQ__Quote__c;
qReport.Quote_Line__c = quoteLine.id;
qReport.Opporunity_Id__c = opportunityId;
qReport.Status__c = true;
qReport.Start_Date__c = startDate ;
qReport.End_Date__c = lastDayOfMonth;
qReport.Net_Amount__c = quoteLine.SBQQ__NetTotal__c ;
qReport.Price_Per_Month__c =calculatePrice ;
qReport.Product_Name__c = quoteLine.SBQQ__ProductName__c;
qReport.Product_Type__c=quoteLine.ProductProduct_Type__c;
qReport.Sales_Rep__c=salesRep;
qReport.Client_Relationship__c= clientRelation;
qReport.State__c = quoteLine.ProductState__c;
qReport.Centre__c = quoteLine.ProductCentre__c;
qReport.Pipeline__c = probability;
reportList.add(qReport);

startDate = startDate.toStartOfMonth();
startDate = startDate.AddMonths(1);
}
}
}
}

September 9, 2018
·
Like
0
·
Follow
1

Best Answer chosen by maddy27

Raj Vakati
try this

public class QuoteReportTriggerHandler { public QuoteReportTriggerHandler() {} public static void createNewRecords(List<SBQQ__Quote__c> rec){ List<SBQQ__QuoteLine__c> qLine = new List<SBQQ__QuoteLine__c>(); List<Quote_Report__c> reportList = new List<Quote_Report__c>(); Set<Id> quoteId = new Set<Id>(); // Get the quote ID for(SBQQ__Quote__c quote : rec){ if(quote.SBQQ__Primary__c== true){ quoteId.add(quote.Id) ; } } // Check the Quote Id and fetch all the fields from QuoteLine if(!quoteId.isEmpty()){ qline = [ SELECT SBQQ__Quote__c , SBQQ__ProductName__c , SBQQ__ProductCode__c , ProductProduct_Type__c,SBQQ__Quote__r.SBQQ__SalesRep__r.Name, SBQQ__Quote__r.SBQQ__SalesRep__r.ID, SBQQ__Quote__r.SBQQ__StartDate__c , SBQQ__Quote__r.SBQQ__EndDate__c , SBQQ__StartDate__c , SBQQ__EndDate__c, SBQQ__CustomerTotal__c,SBQQ__Quote__r.SBQQ__Opportunity2__r.Probability, SBQQ__NetTotal__c,SBQQ__ListPrice__c,SBQQ__NetPrice__c, SBQQ__Quote__r.SBQQ__Opportunity2__c, SBQQ__Quote__r.Client_Relationship__c, ProductCentre__c , ProductState__c From SBQQ__QuoteLine__c where SBQQ__Quote__c IN : quoteId and (NOT (SBQQ__SegmentLabel__c like 'Instal%' or SBQQ__SegmentLabel__c like 'Print%' )) ]; } System.debug(' Check the query result' + qline); if(qline.size() > 0) { Integer i = 0; Date endDate; Date startDate; Map<Id,Date> salesReptoDate = new Map<Id,Date>(); for (SBQQ__QuoteLine__c quoteLine : qline){ if( quoteLine.SBQQ__StartDate__c == null){ startDate = quoteLine.SBQQ__Quote__r.SBQQ__StartDate__c; } else { startDate = quoteLine.SBQQ__StartDate__c; } String salesRepID = quoteLine.SBQQ__Quote__r.SBQQ__SalesRep__r.Id; date stdate = date.newInstance(startdate.year(), startDate.month(), 01); salesReptoDate.put(salesRepID , stdate); } List<ForecastingQuota> fq = [Select QuotaAmount,startdate,QuotaOwnerId from ForecastingQuota where startdate IN : salesReptoDate.values() AND QuotaOwnerId IN salesReptoDate.keySet();]; // Caluclate the start date and end date for (SBQQ__QuoteLine__c quoteLine : qline){ if( quoteLine.SBQQ__StartDate__c == null){ startDate = quoteLine.SBQQ__Quote__r.SBQQ__StartDate__c; } else { startDate = quoteLine.SBQQ__StartDate__c; } if( quoteLine.SBQQ__EndDate__c == null){ endDate = quoteLine.SBQQ__Quote__r.SBQQ__EndDate__c; } else { endDate = quoteLine.SBQQ__EndDate__c; } if(startDate!=null && endDate!=null){ Integer calculateMonths = startDate.monthsBetween(endDate); System.debug(' Calculate Date = ' + calculateMonths); Integer calculateDays = startDate.daysBetween(endDate) +1; System.debug(' Calculate Days = ' + calculateDays); for(i = 0 ; i <= calculateMonths ; i++) { Integer numberOfDays = Date.daysInMonth(startDate.year(), startDate.month()); Date lastDayOfMonth = Date.newInstance(startDate.year(), startDate.month(), numberOfDays); Integer daysInMonths = startDate.daysBetween(lastDayOfMonth)+1; Integer lastMonth = endDate.month(); Integer lastYear = endDate.year(); String opportunityId = quoteLine.SBQQ__Quote__r.SBQQ__Opportunity2__c; String salesRep = quoteLine.SBQQ__Quote__r.SBQQ__SalesRep__r.Name; String salesRepID = quoteLine.SBQQ__Quote__r.SBQQ__SalesRep__r.Id; String clientRelation = quoteLine.SBQQ__Quote__r.Client_Relationship__c; Double probability = quoteLine.SBQQ__Quote__r.SBQQ__Opportunity2__r.Probability; if(lastDayOfMonth > = endDate){ daysInMonths = startDate.daysBetween(endDate) +1; lastDayOfMonth = endDate; } while(startDate < lastDayOfMonth) { Double calculatePrice = ((quoteLine.SBQQ__NetTotal__c/ calculateDays) * daysInMonths) * probability/100 ; // ForecastingQuota fq ; System.debug(' Net price=' +quoteLine.SBQQ__NetTotal__c + ' CalculatetotalDays = ' +calculateDays + ' DaysinMonths =' +daysInMonths+ ' Probablility=' +probability); System.debug(' startDate= ' +startDate+ ' daysInMonths = ' + daysInMonths+ ' calculatePrice = ' +calculatePrice+ ' lastDayOfMonth =' +lastDayOfMonth); system.debug('Product Type = ' + quoteLine.SBQQ__ProductName__c + ' Quote Line =' +quoteLine.id); Quote_Report__c qReport = new Quote_Report__c(); try{ date stdate = date.newInstance(startdate.year(), startDate.month(), 01); for(ForecastingQuota f:fq ){ if(f.startdate==stdate && f.QuotaOwnerId==salesRepID){ qReport.Quota_amount__c = f.QuotaAmount; } } } catch(exception e){ system.debug('Exception') ; } qReport.Quote_ID__c = quoteLine.SBQQ__Quote__c; qReport.Quote_Line__c = quoteLine.id; qReport.Opporunity_Id__c = opportunityId; qReport.Status__c = true; qReport.Start_Date__c = startDate ; qReport.End_Date__c = lastDayOfMonth; qReport.Net_Amount__c = quoteLine.SBQQ__NetTotal__c ; qReport.Price_Per_Month__c =calculatePrice ; qReport.Product_Name__c = quoteLine.SBQQ__ProductName__c; qReport.Product_Type__c=quoteLine.ProductProduct_Type__c; qReport.Sales_Rep__c=salesRep; qReport.Client_Relationship__c= clientRelation; qReport.State__c = quoteLine.ProductState__c; qReport.Centre__c = quoteLine.ProductCentre__c; qReport.Pipeline__c = probability; reportList.add(qReport); startDate = startDate.toStartOfMonth(); startDate = startDate.AddMonths(1); } } } } <b>List<Quote_Report__c> deletelist = new List<Quote_Report__c> (); Set<Id> idsToDelte = new Set<Id>(); for( SBQQ__QuoteLine__c quoLine : qline) { idsToDelte.add(quoLine.SBQQ__Quote__r.SBQQ__Opportunity2__c); // String oppId = quoLine.SBQQ__Quote__r.SBQQ__Opportunity2__c; } deletelist = [Select Quote_id__c , Quote_line__c from Quote_Report__c where Opporunity_Id__c IN : idsToDelte]; if(deletelist.size()>0){ delete deletelist; } insert reportList</b>; } } }

September 9, 2018
·
Like
1
·
Dislike
0

Raj Vakati
try this

try{ if(startdate!=null){ date stdate = date.newInstance(startdate.year(), startDate.month(), 01); fq = new ForecastingQuota(); fq = [Select QuotaAmount from ForecastingQuota where startdate =: stdate AND QuotaOwnerId =: salesRepID]; if(fq.QuotaAmount!=NULL && fq!=null){ qReport.Quota_amount__c = fq.QuotaAmount; } } } catch(exception e){ system.debug('Exception') ; }

September 9, 2018
·
Like
0
·
Dislike
0

maddy27
actually when i tried updating records using Data loader , came across -
limit_exceeded system.limitexception too many soql queries 101
Can we use this try catch block before the loop?
background : we are spliting start and end date into months to calculate the amount for each month. And get the actual Quota amount for each salesrep.

public class QuoteReportTriggerHandler {

public QuoteReportTriggerHandler() {}

public static void createNewRecords(List<SBQQ__Quote__c> rec){

List<SBQQ__QuoteLine__c> qLine = new List<SBQQ__QuoteLine__c>();
List<Quote_Report__c> reportList = new List<Quote_Report__c>();
Set<Id> quoteId = new Set<Id>();

// Get the quote ID
for(SBQQ__Quote__c quote : rec){
if(quote.SBQQ__Primary__c== true){
quoteId.add(quote.Id) ;
}
}

// Check the Quote Id and fetch all the fields from QuoteLine
if(!quoteId.isEmpty()){

qline = [ SELECT SBQQ__Quote__c , SBQQ__ProductName__c , SBQQ__ProductCode__c ,
ProductProduct_Type__c,SBQQ__Quote__r.SBQQ__SalesRep__r.Name,
SBQQ__Quote__r.SBQQ__SalesRep__r.ID,
SBQQ__Quote__r.SBQQ__StartDate__c , SBQQ__Quote__r.SBQQ__EndDate__c ,
SBQQ__StartDate__c , SBQQ__EndDate__c,
SBQQ__CustomerTotal__c,SBQQ__Quote__r.SBQQ__Opportunity2__r.Probability,
SBQQ__NetTotal__c,SBQQ__ListPrice__c,SBQQ__NetPrice__c,
SBQQ__Quote__r.SBQQ__Opportunity2__c, SBQQ__Quote__r.Client_Relationship__c,
ProductCentre__c , ProductState__c
From SBQQ__QuoteLine__c
where SBQQ__Quote__c IN : quoteId and
(NOT (SBQQ__SegmentLabel__c like 'Instal%' or SBQQ__SegmentLabel__c like 'Print%' ))
];

}

System.debug(' Check the query result' + qline);

if(qline.size() > 0) {

Integer i = 0;
Date endDate;
Date startDate;

// Caluclate the start date and end date
for (SBQQ__QuoteLine__c quoteLine : qline){
if( quoteLine.SBQQ__StartDate__c == null){
startDate = quoteLine.SBQQ__Quote__r.SBQQ__StartDate__c;
} else {
startDate = quoteLine.SBQQ__StartDate__c;
}
if( quoteLine.SBQQ__EndDate__c == null){
endDate = quoteLine.SBQQ__Quote__r.SBQQ__EndDate__c;
} else {
endDate = quoteLine.SBQQ__EndDate__c;
}

if(startDate!=null && endDate!=null){

Integer calculateMonths = startDate.monthsBetween(endDate);
System.debug(' Calculate Date = ' + calculateMonths);
Integer calculateDays = startDate.daysBetween(endDate) +1;
System.debug(' Calculate Days = ' + calculateDays);


for(i = 0 ; i <= calculateMonths ; i++) {
Integer numberOfDays = Date.daysInMonth(startDate.year(), startDate.month());
Date lastDayOfMonth = Date.newInstance(startDate.year(), startDate.month(), numberOfDays);
Integer daysInMonths = startDate.daysBetween(lastDayOfMonth)+1;

Integer lastMonth = endDate.month();
Integer lastYear = endDate.year();
String opportunityId = quoteLine.SBQQ__Quote__r.SBQQ__Opportunity2__c;
String salesRep = quoteLine.SBQQ__Quote__r.SBQQ__SalesRep__r.Name;
String salesRepID = quoteLine.SBQQ__Quote__r.SBQQ__SalesRep__r.Id;
String clientRelation = quoteLine.SBQQ__Quote__r.Client_Relationship__c;
Double probability = quoteLine.SBQQ__Quote__r.SBQQ__Opportunity2__r.Probability;

if(lastDayOfMonth > = endDate){
daysInMonths = startDate.daysBetween(endDate) +1;
lastDayOfMonth = endDate;
}
while(startDate < lastDayOfMonth)
{

Double calculatePrice = ((quoteLine.SBQQ__NetTotal__c/ calculateDays) * daysInMonths) * probability/100 ;
ForecastingQuota fq ;
System.debug(' Net price=' +quoteLine.SBQQ__NetTotal__c + ' CalculatetotalDays = ' +calculateDays + ' DaysinMonths =' +daysInMonths+ ' Probablility=' +probability);
System.debug(' startDate= ' +startDate+ ' daysInMonths = ' + daysInMonths+ ' calculatePrice = ' +calculatePrice+ ' lastDayOfMonth =' +lastDayOfMonth);
system.debug('Product Type = ' + quoteLine.SBQQ__ProductName__c + ' Quote Line =' +quoteLine.id);
Quote_Report__c qReport = new Quote_Report__c();
try{
date stdate = date.newInstance(startdate.year(), startDate.month(), 01);
fq = [Select QuotaAmount from ForecastingQuota where
startdate =: stdate
AND QuotaOwnerId =: salesRepID];
System.debug('++Fq' + fq);
qReport.Quota_amount__c = fq.QuotaAmount;
} catch(exception e){
system.debug('Exception') ;
}

qReport.Quote_ID__c = quoteLine.SBQQ__Quote__c;
qReport.Quote_Line__c = quoteLine.id;
qReport.Opporunity_Id__c = opportunityId;
qReport.Status__c = true;
qReport.Start_Date__c = startDate ;
qReport.End_Date__c = lastDayOfMonth;
qReport.Net_Amount__c = quoteLine.SBQQ__NetTotal__c ;
qReport.Price_Per_Month__c =calculatePrice ;
qReport.Product_Name__c = quoteLine.SBQQ__ProductName__c;
qReport.Product_Type__c=quoteLine.ProductProduct_Type__c;
qReport.Sales_Rep__c=salesRep;
qReport.Client_Relationship__c= clientRelation;
qReport.State__c = quoteLine.ProductState__c;
qReport.Centre__c = quoteLine.ProductCentre__c;
qReport.Pipeline__c = probability;
reportList.add(qReport);

startDate = startDate.toStartOfMonth();
startDate = startDate.AddMonths(1);
}
}
}
}
List<Quote_Report__c> deletelist = new List<Quote_Report__c> ();
for( SBQQ__QuoteLine__c quoLine : qline) {

String oppId = quoLine.SBQQ__Quote__r.SBQQ__Opportunity2__c;
deletelist = [Select Quote_id__c , Quote_line__c
from Quote_Report__c
where Opporunity_Id__c =: oppId];

}

if(deletelist.size()>0){
delete deletelist;
}

insert reportList;
}
}
}

September 9, 2018
·
Like
0
·
Dislike
0

Raj Vakati
Got it .. try this

public class QuoteReportTriggerHandler { public QuoteReportTriggerHandler() {} public static void createNewRecords(List<SBQQ__Quote__c> rec){ List<SBQQ__QuoteLine__c> qLine = new List<SBQQ__QuoteLine__c>(); List<Quote_Report__c> reportList = new List<Quote_Report__c>(); Set<Id> quoteId = new Set<Id>(); // Get the quote ID for(SBQQ__Quote__c quote : rec){ if(quote.SBQQ__Primary__c== true){ quoteId.add(quote.Id) ; } } // Check the Quote Id and fetch all the fields from QuoteLine if(!quoteId.isEmpty()){ qline = [ SELECT SBQQ__Quote__c , SBQQ__ProductName__c , SBQQ__ProductCode__c , ProductProduct_Type__c,SBQQ__Quote__r.SBQQ__SalesRep__r.Name, SBQQ__Quote__r.SBQQ__SalesRep__r.ID, SBQQ__Quote__r.SBQQ__StartDate__c , SBQQ__Quote__r.SBQQ__EndDate__c , SBQQ__StartDate__c , SBQQ__EndDate__c, SBQQ__CustomerTotal__c,SBQQ__Quote__r.SBQQ__Opportunity2__r.Probability, SBQQ__NetTotal__c,SBQQ__ListPrice__c,SBQQ__NetPrice__c, SBQQ__Quote__r.SBQQ__Opportunity2__c, SBQQ__Quote__r.Client_Relationship__c, ProductCentre__c , ProductState__c From SBQQ__QuoteLine__c where SBQQ__Quote__c IN : quoteId and (NOT (SBQQ__SegmentLabel__c like 'Instal%' or SBQQ__SegmentLabel__c like 'Print%' )) ]; } System.debug(' Check the query result' + qline); if(qline.size() > 0) { Integer i = 0; Date endDate; Date startDate; // Caluclate the start date and end date for (SBQQ__QuoteLine__c quoteLine : qline){ if( quoteLine.SBQQ__StartDate__c == null){ startDate = quoteLine.SBQQ__Quote__r.SBQQ__StartDate__c; } else { startDate = quoteLine.SBQQ__StartDate__c; } if( quoteLine.SBQQ__EndDate__c == null){ endDate = quoteLine.SBQQ__Quote__r.SBQQ__EndDate__c; } else { endDate = quoteLine.SBQQ__EndDate__c; } if(startDate!=null && endDate!=null){ Integer calculateMonths = startDate.monthsBetween(endDate); System.debug(' Calculate Date = ' + calculateMonths); Integer calculateDays = startDate.daysBetween(endDate) +1; System.debug(' Calculate Days = ' + calculateDays); for(i = 0 ; i <= calculateMonths ; i++) { Integer numberOfDays = Date.daysInMonth(startDate.year(), startDate.month()); Date lastDayOfMonth = Date.newInstance(startDate.year(), startDate.month(), numberOfDays); Integer daysInMonths = startDate.daysBetween(lastDayOfMonth)+1; Integer lastMonth = endDate.month(); Integer lastYear = endDate.year(); String opportunityId = quoteLine.SBQQ__Quote__r.SBQQ__Opportunity2__c; String salesRep = quoteLine.SBQQ__Quote__r.SBQQ__SalesRep__r.Name; String salesRepID = quoteLine.SBQQ__Quote__r.SBQQ__SalesRep__r.Id; String clientRelation = quoteLine.SBQQ__Quote__r.Client_Relationship__c; Double probability = quoteLine.SBQQ__Quote__r.SBQQ__Opportunity2__r.Probability; if(lastDayOfMonth > = endDate){ daysInMonths = startDate.daysBetween(endDate) +1; lastDayOfMonth = endDate; } while(startDate < lastDayOfMonth) { Double calculatePrice = ((quoteLine.SBQQ__NetTotal__c/ calculateDays) * daysInMonths) * probability/100 ; ForecastingQuota fq ; System.debug(' Net price=' +quoteLine.SBQQ__NetTotal__c + ' CalculatetotalDays = ' +calculateDays + ' DaysinMonths =' +daysInMonths+ ' Probablility=' +probability); System.debug(' startDate= ' +startDate+ ' daysInMonths = ' + daysInMonths+ ' calculatePrice = ' +calculatePrice+ ' lastDayOfMonth =' +lastDayOfMonth); system.debug('Product Type = ' + quoteLine.SBQQ__ProductName__c + ' Quote Line =' +quoteLine.id); Quote_Report__c qReport = new Quote_Report__c(); try{ date stdate = date.newInstance(startdate.year(), startDate.month(), 01); fq = [Select QuotaAmount from ForecastingQuota where startdate =: stdate AND QuotaOwnerId =: salesRepID]; System.debug('++Fq' + fq); qReport.Quota_amount__c = fq.QuotaAmount; } catch(exception e){ system.debug('Exception') ; } qReport.Quote_ID__c = quoteLine.SBQQ__Quote__c; qReport.Quote_Line__c = quoteLine.id; qReport.Opporunity_Id__c = opportunityId; qReport.Status__c = true; qReport.Start_Date__c = startDate ; qReport.End_Date__c = lastDayOfMonth; qReport.Net_Amount__c = quoteLine.SBQQ__NetTotal__c ; qReport.Price_Per_Month__c =calculatePrice ; qReport.Product_Name__c = quoteLine.SBQQ__ProductName__c; qReport.Product_Type__c=quoteLine.ProductProduct_Type__c; qReport.Sales_Rep__c=salesRep; qReport.Client_Relationship__c= clientRelation; qReport.State__c = quoteLine.ProductState__c; qReport.Centre__c = quoteLine.ProductCentre__c; qReport.Pipeline__c = probability; reportList.add(qReport); startDate = startDate.toStartOfMonth(); startDate = startDate.AddMonths(1); } } } } List<Quote_Report__c> deletelist = new List<Quote_Report__c> (); Set<Id> idsToDelte = new Set<Id>(); for( SBQQ__QuoteLine__c quoLine : qline) { idsToDelte.add(quoLine.SBQQ__Quote__r.SBQQ__Opportunity2__c); // String oppId = quoLine.SBQQ__Quote__r.SBQQ__Opportunity2__c; } deletelist = [Select Quote_id__c , Quote_line__c from Quote_Report__c where Opporunity_Id__c IN : idsToDelte]; if(deletelist.size()>0){ delete deletelist; } insert reportList; } } }

September 9, 2018
·
Like
0
·
Dislike
0

maddy27
That was good catch , to add QuoteLine ids into Set.
But the major issue is with this part of quote. I want to avoid writing Query in for loops.
try{ if(startdate!=null){ date stdate = date.newInstance(startdate.year(), startDate.month(), 01); fq = new ForecastingQuota(); fq = [Select QuotaAmount from ForecastingQuota where startdate =: stdate AND QuotaOwnerId =: salesRepID]; if(fq.QuotaAmount!=NULL && fq!=null){ qReport.Quota_amount__c = fq.QuotaAmount; } } } catch(exception e){ system.debug('Exception') ; }

September 9, 2018
·
Like
0
·
Dislike
0

Raj Vakati
Try this
public class QuoteReportTriggerHandler { public QuoteReportTriggerHandler() {} public static void createNewRecords(List<SBQQ__Quote__c> rec){ List<SBQQ__QuoteLine__c> qLine = new List<SBQQ__QuoteLine__c>(); List<Quote_Report__c> reportList = new List<Quote_Report__c>(); Set<Id> quoteId = new Set<Id>(); // Get the quote ID for(SBQQ__Quote__c quote : rec){ if(quote.SBQQ__Primary__c== true){ quoteId.add(quote.Id) ; } } // Check the Quote Id and fetch all the fields from QuoteLine if(!quoteId.isEmpty()){ qline = [ SELECT SBQQ__Quote__c , SBQQ__ProductName__c , SBQQ__ProductCode__c , ProductProduct_Type__c,SBQQ__Quote__r.SBQQ__SalesRep__r.Name, SBQQ__Quote__r.SBQQ__SalesRep__r.ID, SBQQ__Quote__r.SBQQ__StartDate__c , SBQQ__Quote__r.SBQQ__EndDate__c , SBQQ__StartDate__c , SBQQ__EndDate__c, SBQQ__CustomerTotal__c,SBQQ__Quote__r.SBQQ__Opportunity2__r.Probability, SBQQ__NetTotal__c,SBQQ__ListPrice__c,SBQQ__NetPrice__c, SBQQ__Quote__r.SBQQ__Opportunity2__c, SBQQ__Quote__r.Client_Relationship__c, ProductCentre__c , ProductState__c From SBQQ__QuoteLine__c where SBQQ__Quote__c IN : quoteId and (NOT (SBQQ__SegmentLabel__c like 'Instal%' or SBQQ__SegmentLabel__c like 'Print%' )) ]; } System.debug(' Check the query result' + qline); if(qline.size() > 0) { Integer i = 0; Date endDate; Date startDate; Map<Id,Date> salesReptoDate = new Map<Id,Date>(); for (SBQQ__QuoteLine__c quoteLine : qline){ if( quoteLine.SBQQ__StartDate__c == null){ startDate = quoteLine.SBQQ__Quote__r.SBQQ__StartDate__c; } else { startDate = quoteLine.SBQQ__StartDate__c; } String salesRepID = quoteLine.SBQQ__Quote__r.SBQQ__SalesRep__r.Id; date stdate = date.newInstance(startdate.year(), startDate.month(), 01); salesReptoDate.put(salesRepID , stdate); } List<ForecastingQuota> fq = [Select QuotaAmount from ForecastingQuota where startdate IN : salesReptoDate.values() AND QuotaOwnerId IN salesReptoDate.keySet()]; // Caluclate the start date and end date for (SBQQ__QuoteLine__c quoteLine : qline){ if( quoteLine.SBQQ__StartDate__c == null){ startDate = quoteLine.SBQQ__Quote__r.SBQQ__StartDate__c; } else { startDate = quoteLine.SBQQ__StartDate__c; } if( quoteLine.SBQQ__EndDate__c == null){ endDate = quoteLine.SBQQ__Quote__r.SBQQ__EndDate__c; } else { endDate = quoteLine.SBQQ__EndDate__c; } if(startDate!=null && endDate!=null){ Integer calculateMonths = startDate.monthsBetween(endDate); System.debug(' Calculate Date = ' + calculateMonths); Integer calculateDays = startDate.daysBetween(endDate) +1; System.debug(' Calculate Days = ' + calculateDays); for(i = 0 ; i <= calculateMonths ; i++) { Integer numberOfDays = Date.daysInMonth(startDate.year(), startDate.month()); Date lastDayOfMonth = Date.newInstance(startDate.year(), startDate.month(), numberOfDays); Integer daysInMonths = startDate.daysBetween(lastDayOfMonth)+1; Integer lastMonth = endDate.month(); Integer lastYear = endDate.year(); String opportunityId = quoteLine.SBQQ__Quote__r.SBQQ__Opportunity2__c; String salesRep = quoteLine.SBQQ__Quote__r.SBQQ__SalesRep__r.Name; String salesRepID = quoteLine.SBQQ__Quote__r.SBQQ__SalesRep__r.Id; String clientRelation = quoteLine.SBQQ__Quote__r.Client_Relationship__c; Double probability = quoteLine.SBQQ__Quote__r.SBQQ__Opportunity2__r.Probability; if(lastDayOfMonth > = endDate){ daysInMonths = startDate.daysBetween(endDate) +1; lastDayOfMonth = endDate; } while(startDate < lastDayOfMonth) { Double calculatePrice = ((quoteLine.SBQQ__NetTotal__c/ calculateDays) * daysInMonths) * probability/100 ; // ForecastingQuota fq ; System.debug(' Net price=' +quoteLine.SBQQ__NetTotal__c + ' CalculatetotalDays = ' +calculateDays + ' DaysinMonths =' +daysInMonths+ ' Probablility=' +probability); System.debug(' startDate= ' +startDate+ ' daysInMonths = ' + daysInMonths+ ' calculatePrice = ' +calculatePrice+ ' lastDayOfMonth =' +lastDayOfMonth); system.debug('Product Type = ' + quoteLine.SBQQ__ProductName__c + ' Quote Line =' +quoteLine.id); Quote_Report__c qReport = new Quote_Report__c(); try{ date stdate = date.newInstance(startdate.year(), startDate.month(), 01); for(ForecastingQuota f:fq ){ if(f.startdate==stdate && f.QuotaOwnerId==salesRepID){ qReport.Quota_amount__c = f.QuotaAmount; } } } catch(exception e){ system.debug('Exception') ; } qReport.Quote_ID__c = quoteLine.SBQQ__Quote__c; qReport.Quote_Line__c = quoteLine.id; qReport.Opporunity_Id__c = opportunityId; qReport.Status__c = true; qReport.Start_Date__c = startDate ; qReport.End_Date__c = lastDayOfMonth; qReport.Net_Amount__c = quoteLine.SBQQ__NetTotal__c ; qReport.Price_Per_Month__c =calculatePrice ; qReport.Product_Name__c = quoteLine.SBQQ__ProductName__c; qReport.Product_Type__c=quoteLine.ProductProduct_Type__c; qReport.Sales_Rep__c=salesRep; qReport.Client_Relationship__c= clientRelation; qReport.State__c = quoteLine.ProductState__c; qReport.Centre__c = quoteLine.ProductCentre__c; qReport.Pipeline__c = probability; reportList.add(qReport); startDate = startDate.toStartOfMonth(); startDate = startDate.AddMonths(1); } } } } <b>List<Quote_Report__c> deletelist = new List<Quote_Report__c> (); Set<Id> idsToDelte = new Set<Id>(); for( SBQQ__QuoteLine__c quoLine : qline) { idsToDelte.add(quoLine.SBQQ__Quote__r.SBQQ__Opportunity2__c); // String oppId = quoLine.SBQQ__Quote__r.SBQQ__Opportunity2__c; } deletelist = [Select Quote_id__c , Quote_line__c from Quote_Report__c where Opporunity_Id__c IN : idsToDelte]; if(deletelist.size()>0){ delete deletelist; } insert reportList</b>; } } }

September 9, 2018
·
Like
0
·
Dislike
0

maddy27
Great Raj -
but coming across error : xceptionSystem.SObjectException: SObject row was retrieved via SOQL without querying the requested field: ForecastingQuota.StartDate
Quota Amount is not set.

September 9, 2018
·
Like
0
·
Dislike
0

Raj Vakati
Add the StartDate field to SOQL and debug it ..

Approach is correct ..

September 9, 2018
·
Like
1
·
Dislike
0

This was selected as the best answer

maddy27
Yes added -
Select QuotaAmount , startdate,QuotaOwnerId from ForecastingQuota where startdate IN : salesReptoDate.values() AND QuotaOwnerId IN : salesReptoDate.keySet()

Thanks a lot !
I'm adding my test class let me know if thats right
@isTest (SeeAllData = true) public class QuoteReportTrigger_test { static testMethod void TestcreateNewRecords(){ //Declare Variables and Objects Decimal ST = 12; Date StartQuote = Date.today(); SBQQ__Quote__c q = new SBQQ__Quote__c(); //Create Account Record Account a = new Account(); a.Name = 'TestAccount'; insert a; update a; //Create Opporunity Opportunity o = new Opportunity(); o.Name = 'Test Opp'; o.AccountId = a.Id; o.Type = 'Variable'; o.Billing_Account__c = a.Id; o.Client_Relationship__c = 'Direct Client'; o.StageName = 'Owned Lead'; // o.SBQQ__PrimaryQuote__c = q.id; o.Probability = 90; o.CloseDate= date.today(); insert o; system.debug('++ Opp' + o); //Create Quote Line Records Product2 p = new product2(name='unittest'); p.Family = 'Media'; p.IsActive = true; insert p; system.debug('++ Prod'+ p); Id pricebookId = Test.getStandardPricebookId(); PricebookEntry stdPBE = new PriceBookEntry( Product2Id=p.Id, Pricebook2Id=pricebookId, UnitPrice = 3000000.00, UseStandardPrice = false, IsActive=true ); insert stdPBE; system.debug('++ stdPBE'+ stdPBE); PriceBook2 customPriceBook = new PriceBook2(); customPriceBook.Name='Custom Pricebook'; customPriceBook.IsActive=true; customPriceBook.Effective_From__c = date.today(); customPriceBook.Effective_To__c= date.today()+60; insert customPriceBook; system.debug('++ customPriceBook'+ customPriceBook); PricebookEntry pbe = new PriceBookEntry( Product2Id=p.Id, Pricebook2Id=customPriceBook.Id, UnitPrice = 5000000.00, UseStandardPrice = false, IsActive=true ); insert pbe; system.debug('++ pbe'+ pbe); //Create OpportunityLineItem Records /* OpportunityLineItem item=new OpportunityLineItem(); item.OpportunityId = o.Id; item.PricebookEntryId = pbe.Id; item.Quantity = 2; item.TotalPrice = 10.0; insert item; system.debug('++ item'+ item);*/ //Create Quote Record q.SBQQ__Account__c = a.Id; q.SBQQ__Opportunity2__c = o.Id; q.SBQQ__Status__c = 'Draft'; q.SBQQ__Type__c = 'Quote'; q.SBQQ__StartDate__c = StartQuote; q.SBQQ__EndDate__c = StartQuote + 60; q.SBQQ__Primary__c = True; q.SBQQ__SubscriptionTerm__c = ST; q.SBQQ__SalesRep__c = UserInfo.getUserId(); // q.SBQQ__PriceBook__c = [select id from Pricebook2 where IsActive = true limit 1].id; insert q; System.debug('++ Quote' + q); SBQQ__QuoteLine__c qlRec = new SBQQ__QuoteLine__c(); qlRec.SBQQ__Quote__c = q.Id; qlRec.SBQQ__StartDate__c = date.today(); qlrec.SBQQ__EndDate__c = date.today() + 60; qlrec.SBQQ__Product__c = p.id; qlRec.SBQQ__Quote__r.SBQQ__Opportunity2__r.Probability = 90; // qlRec.SBQQ__Pri = [select id from Pricebook2 where IsActive = true limit 1].id; insert qlRec; } }

September 9, 2018
·
Like
0
·
Dislike
0

Raj Vakati
Cool ..Test class looks good now .. close this thread !

September 9, 2018
·
Like
1
·
Dislike
0

maddy27
Great Thanks a lot! Cheers :)

September 9, 2018
·
Like
0
·
Dislike
0

You need to sign in to do that.

Need an account? Sign Up

Have an account? Sign In

Dismiss

Browse by Topic

Welcome to Support!

Show

sorted by

Forecast Quota - Please help with the code below : I'm trying to fetch the quota amount for each salesRep within the Custom Object , where in Custom object maintains QuoteLine details . This try and catch block is failing during data loader update.

All Answers

You need to sign in to do that.