I have a batch class that will be performing some actions over selected Case records.
The only problem with this batch class is the Start method.
When the query is returned and processed, it returns the error "Unexpected Token ':'".
The query is pretty big and has many Where clauses. I've been looking to find where is the incorrect ':' for two days now but I haven't been able to fix it.
Here is the simplified code for my batch class, the problem is in the start() method. As soon as it is executed, the error appears:
public class CaseTimeBasedActionsBatch implements Database.Batchable<sObject>, Database.Stateful {
public String query;
public String queryFields;
public String caseOriginSelfServDelSys;
public String queuePXSelfServDelTimeout;
public DateTime timeStart_sendSelfServiceDeletionEmail;
public DateTime timeStart_selfServiceDeletionTimeout;
public DateTime timeStart_sendUnresponsiveEmail;
public DateTime timeStart_closeUnresponsiveCase;
public DateTime timeStart_reopenOnHoldCase;
public String criteria_sendSelfServiceDeletionEmail;
public String criteria_selfServiceDeletionTimeout;
public String criteria_sendUnresponsiveEmail;
public String criteria_closeUnresponsiveCase;
public String criteria_reopenOnHoldCase;
public CaseTimeBasedActionsBatch() {
queryFields = getQueryFields();
query = getQueryString();
}
public Database.QueryLocator start(Database.BatchableContext bc) {
return Database.getQueryLocator(query);
}
public String getQueryFields() {
List<String> fields = new List<String> {
'Origin',
'Owner.Name',
'Owner.Type',
'Status',
'CreatedDate',
'Language',
'ContactId',
'SuppliedEmail',
'Auto_Comment_Date__c',
'Threat_Non_Credible__c',
'On_Hold_End_Date__c',
'Last_Response_Date__c'
};
return String.join(fields, ',');
}
public String getQueryString () {
setTimeRanges();
setQueryCriterias();
return 'SELECT :queryFields FROM Case WHERE RecordType.DeveloperName = \'Player_Support\' AND (' +
criteria_sendSelfServiceDeletionEmail + ' OR ' +
criteria_selfServiceDeletionTimeout + ' OR ' +
criteria_sendUnresponsiveEmail + ' OR ' +
criteria_closeUnresponsiveCase + ' OR ' +
criteria_reopenOnHoldCase +')';
}
public void setTimeRanges() {
DateTime currentDT = DateTime.now();
timeStart_sendSelfServiceDeletionEmail = currentDT.addHours(
-Integer.valueOf(Case_Time_Based_Action__mdt.getInstance('SendSelfServiceDeletionEmail').Hours_Amount__c)
);
timeStart_selfServiceDeletionTimeout = currentDT.addHours(
-Integer.valueOf(Case_Time_Based_Action__mdt.getInstance('SelfServiceDeletionTimeout').Hours_Amount__c)
);
timeStart_sendUnresponsiveEmail = currentDT.addHours(
-Integer.valueOf(Case_Time_Based_Action__mdt.getInstance('SendUnresponsiveEmail').Hours_Amount__c)
);
timeStart_closeUnresponsiveCase = currentDT.addHours(
-Integer.valueOf(Case_Time_Based_Action__mdt.getInstance('CloseUnresponsiveCase').Hours_Amount__c)
);
timeStart_closeUnresponsiveCase = timeStart_closeUnresponsiveCase.date();
timeStart_reopenOnHoldCase = currentDT.addHours(
-Integer.valueOf(Case_Time_Based_Action__mdt.getInstance('ReopenOnHoldCase').Hours_Amount__c)
);
}
public void setQueryCriterias() {
criteria_sendSelfServiceDeletionEmail = '(Origin = :caseOriginSelfServDelSys AND Owner.Name = :queuePXSelfServDelTimeout AND Auto_Comment_Date__c = null AND CreatedDate >= :timeStart_sendSelfServiceDeletionEmail)';
criteria_selfServiceDeletionTimeout = '(Origin = :caseOriginSelfServDelSys AND Owner.Name = :queuePXSelfServDelTimeout AND Status = \'Pending\' AND CreatedDate >= :timeStart_selfServiceDeletionTimeout)';
criteria_sendUnresponsiveEmail = '(Owner.Type != \'Queue\' AND Threat_Non_Credible__c != \'Credible\' AND Status = \'Pending\' AND Auto_Comment_Date__c = null AND Last_Response_Date__c >= :timeStart_sendUnresponsiveEmail)';
criteria_closeUnresponsiveCase = '(Owner.Type != \'Queue\' AND Threat_Non_Credible__c != \'Credible\' AND Status = \'Pending\' AND Auto_Comment_Date__c != null AND Auto_Comment_Date__c >= :timeStart_closeUnresponsiveCase)';
criteria_reopenOnHoldCase = '(Owner.Type != \'Queue\' AND Status = \'On Hold\' AND On_Hold_End_Date__c >= :timeStart_reopenOnHoldCase)';
}
}
And here there is the full code of my class, just in case it is useful.
public class CaseTimeBasedActionsBatch implements Database.Batchable<sObject>, Database.Stateful {
public String query;
public String queryFields;
public String caseOriginSelfServDelSys;
public String queuePXSelfServDelTimeout;
public Map<String, Id> selfServDelEmailTemplateMap;
public Map<String, Id> unresponsiveEmailTemplateMap;
public Map<String, Id> queuesMap;
public List<String> caseOriginsToAvoid;
public DateTime timeStart_sendSelfServiceDeletionEmail;
public DateTime timeStart_selfServiceDeletionTimeout;
public DateTime timeStart_sendUnresponsiveEmail;
public DateTime timeStart_closeUnresponsiveCase;
public DateTime timeStart_reopenOnHoldCase;
public String criteria_sendSelfServiceDeletionEmail;
public String criteria_selfServiceDeletionTimeout;
public String criteria_sendUnresponsiveEmail;
public String criteria_closeUnresponsiveCase;
public String criteria_reopenOnHoldCase;
public CaseTimeBasedActionsBatch() {
caseOriginSelfServDelSys = 'Self-Service Deletion System';
queuePXSelfServDelTimeout = 'PX Self-Service Deletion Timeout';
selfServDelEmailTemplateMap = getSelfServDelEmailTemplatesMap();
unresponsiveEmailTemplateMap = getUnresponsiveEmailTemplatesMap();
queuesMap = getQueuesMap();
caseOriginsToAvoid = getCaseOriginsToAvoid();
queryFields = getQueryFields();
query = getQueryString();
}
public Database.QueryLocator start(Database.BatchableContext bc) {
return Database.getQueryLocator(query);
}
public void execute(Database.BatchableContext bc, List<SObject> records) {
for (SObject record : records) {
Case currentCase = (Case) record;
List<Case> casesToUpdate = new List<Case>();
String origin = currentCase.Origin;
String ownerName = currentCase.Owner.Name;
String ownerType = currentCase.Owner.Type;
String status = currentCase.Status;
String treatNonCredible = currentCase.Threat_Non_Credible__c;
String languageCode = currentCase.Language.right(2).toUpperCase();
Date autoCommentDate = currentCase.Auto_Comment_Date__c;
Datetime createdDate = currentCase.CreatedDate;
Datetime lastResponseDate = currentCase.Last_Response_Date__c;
Datetime onHoldDate = currentCase.On_Hold_End_Date__c;
if (
origin == caseOriginSelfServDelSys &&
ownerName == queuePXSelfServDelTimeout &&
autoCommentDate == null &&
createdDate >= timeStart_sendSelfServiceDeletionEmail
) {
Id templateId = selfServDelEmailTemplateMap.get(languageCode) == null ? selfServDelEmailTemplateMap.get('EN') : selfServDelEmailTemplateMap.get(languageCode);
callPXEmailsFlow(currentCase, templateId);
currentCase.Status = 'Pending';
casesToUpdate.add(currentCase);
} else if (
origin == caseOriginSelfServDelSys &&
ownerName == queuePXSelfServDelTimeout &&
status == 'Pending' &&
createdDate >= timeStart_selfServiceDeletionTimeout
) {
currentCase.OwnerId = queuesMap.get(languageCode) == null ? queuesMap.get('EN') : queuesMap.get(languageCode);
currentCase.Status = 'Open';
casesToUpdate.add(currentCase);
} else if (
ownerType != 'Queue' &&
treatNonCredible != 'Credible' &&
status == 'Pending' &&
autoCommentDate == null &&
lastResponseDate >= timeStart_sendUnresponsiveEmail
) {
Id templateId = unresponsiveEmailTemplateMap.get(languageCode) == null ? unresponsiveEmailTemplateMap.get('EN') : unresponsiveEmailTemplateMap.get(languageCode);
callPXEmailsFlow(currentCase, templateId);
currentCase.Auto_Comment_Date__c = Date.today();
casesToUpdate.add(currentCase);
} else if (
ownerType != 'Queue' &&
treatNonCredible != 'Credible' &&
status == 'Pending' &&
autoCommentDate >= timeStart_closeUnresponsiveCase.date()
) {
currentCase.Status = 'Closed';
currentCase.Reason = 'Auto Closed - Unresponsive';
casesToUpdate.add(currentCase);
if (!caseOriginsToAvoid.contains(origin)) {
callCSATFlow(currentCase.Id);
}
} else if (
ownerType != 'Queue' &&
status == 'On Hold' &&
onHoldDate >= timeStart_reopenOnHoldCase
) {
currentCase.Status = 'Open';
casesToUpdate.add(currentCase);
}
if (!casesToUpdate.isEmpty() && !Test.isRunningTest()) {
update casesToUpdate;
}
}
}
public void finish(Database.BatchableContext bc) {
CaseHistoryTimeBasedActionsBatch batchJob = new CaseHistoryTimeBasedActionsBatch();
Database.executeBatch(batchJob, 200);
}
public String getQueryFields() {
List<String> fields = new List<String> {
'Origin',
'Owner.Name',
'Owner.Type',
'Status',
'CreatedDate',
'Language',
'ContactId',
'SuppliedEmail',
'Auto_Comment_Date__c',
'Threat_Non_Credible__c',
'On_Hold_End_Date__c',
'Last_Response_Date__c'
};
return String.join(fields, ',');
}
public String getQueryString () {
setTimeRanges();
setQueryCriterias();
return 'SELECT :queryFields FROM Case WHERE RecordType.DeveloperName = \'Player_Support\' AND (' +
criteria_sendSelfServiceDeletionEmail + ' OR ' +
criteria_selfServiceDeletionTimeout + ' OR ' +
criteria_sendUnresponsiveEmail + ' OR ' +
criteria_closeUnresponsiveCase + ' OR ' +
criteria_reopenOnHoldCase +')';
}
public Map<String, Id> getSelfServDelEmailTemplatesMap() {
Map<String, Id> finalMap = new Map<String, Id>();
Map<String, String> namesMap = new Map<String, String> {
'AR' => 'PX_Self-Service_Deletion_AR',
'DE' => 'PX_Self-Service_Deletion_DE',
'EN' => 'PX_Self-Service_Deletion_EN',
'ES' => 'PX_Self-Service_Deletion_ES',
'FR' => 'PX_Self-Service_Deletion_FR',
'IT' => 'PX_Self-Service_Deletion_IT',
'JA' => 'PX_Self-Service_Deletion_JA',
'KO' => 'PX_Self-Service_Deletion_KO',
'PL' => 'PX_Self-Service_Deletion_PL',
'PT' => 'PX_Self-Service_Deletion_PT',
'RU' => 'PX_Self-Service_Deletion_RU',
'TH' => 'PX_Self-Service_Deletion_TH',
'TR' => 'PX_Self-Service_Deletion_TR',
'ZH' => 'PX_Self-Service_Deletion_ZH'
};
List<EmailTemplate> templateLst = [
SELECT Id, Name
FROM EmailTemplate
WHERE Name IN :namesMap.values()
];
for (String langCode : namesMap.keySet()) {
for (EmailTemplate template : templateLst) {
if (template.Name.right(2) == langCode) {
finalMap.put(langCode, template.Id);
}
}
}
return finalMap;
}
public Map<String, Id> getUnresponsiveEmailTemplatesMap() {
Map<String, Id> finalMap = new Map<String, Id>();
Map<String, String> namesMap = new Map<String, String> {
'AR' => 'PX_Unresponsive_AR',
'DE' => 'PX_Unresponsive_DE',
'EN' => 'PX_Unresponsive_EN',
'ES' => 'PX_Unresponsive_ES',
'FR' => 'PX_Unresponsive_FR',
'IT' => 'PX_Unresponsive_IT',
'JA' => 'PX_Unresponsive_JA',
'KO' => 'PX_Unresponsive_KO',
'PL' => 'PX_Unresponsive_PL',
'PT' => 'PX_Unresponsive_PT',
'RU' => 'PX_Unresponsive_RU',
'TH' => 'PX_Unresponsive_TH',
'TR' => 'PX_Unresponsive_TR',
'ZH' => 'PX_Unresponsive_ZH'
};
List<EmailTemplate> templateLst = [
SELECT Id, Name
FROM EmailTemplate
WHERE Name IN :namesMap.values()
];
for (String langCode : namesMap.keySet()) {
for (EmailTemplate template : templateLst) {
if (template.Name.right(2) == langCode) {
finalMap.put(langCode, template.Id);
}
}
}
return finalMap;
}
public Map<String, Id> getQueuesMap() {
Map<String, Id> finalMap = new Map<String, Id>();
Map<String, String> namesMap = new Map<String, String> {
'AR' => 'PX T1 AR General',
'DE' => 'PX T1 DE General',
'EN' => 'PX T1 EN General',
'ES' => 'PX T1 ES General',
'FR' => 'PX T1 FR General',
'IT' => 'PX T1 IT General',
'JA' => 'PX T1 JA General',
'KO' => 'PX T1 KO General',
'PL' => 'PX T1 PL General',
'PT' => 'PX T1 PT General',
'RU' => 'PX T1 RU General',
'TH' => 'PX T1 TH Genera',
'TR' => 'PX T1 TR General',
'ZH' => 'PX T1 ZH General'
};
List<Group> queueLst = [
SELECT Id, Name
FROM Group
WHERE type = 'Queue'
AND Name IN :namesMap.values()
];
for (String langCode : namesMap.keySet()) {
for (Group groupRec : queueLst) {
if (groupRec.Name.right(2) == langCode) {
finalMap.put(langCode, groupRec.Id);
}
}
}
return finalMap;
}
public List<String> getCaseOriginsToAvoid() {
return new List<String> {
'Epic Entitlements Email',
'Epic Escalations Email',
'Epic Partners Email',
'Epic DMCA Email',
'Epic Departure Email',
'Epic DMCA Form',
'Epic Report A Creator Email',
'Contract Departure Email'
};
}
public void setTimeRanges() {
DateTime currentDT = DateTime.now();
timeStart_sendSelfServiceDeletionEmail = currentDT.addHours(
-Integer.valueOf(Case_Time_Based_Action__mdt.getInstance('SendSelfServiceDeletionEmail').Hours_Amount__c)
);
timeStart_selfServiceDeletionTimeout = currentDT.addHours(
-Integer.valueOf(Case_Time_Based_Action__mdt.getInstance('SelfServiceDeletionTimeout').Hours_Amount__c)
);
timeStart_sendUnresponsiveEmail = currentDT.addHours(
-Integer.valueOf(Case_Time_Based_Action__mdt.getInstance('SendUnresponsiveEmail').Hours_Amount__c)
);
timeStart_closeUnresponsiveCase = currentDT.addHours(
-Integer.valueOf(Case_Time_Based_Action__mdt.getInstance('CloseUnresponsiveCase').Hours_Amount__c)
);
timeStart_closeUnresponsiveCase = timeStart_closeUnresponsiveCase.date();
timeStart_reopenOnHoldCase = currentDT.addHours(
-Integer.valueOf(Case_Time_Based_Action__mdt.getInstance('ReopenOnHoldCase').Hours_Amount__c)
);
}
public void setQueryCriterias() {
criteria_sendSelfServiceDeletionEmail = '(Origin = :caseOriginSelfServDelSys AND Owner.Name = :queuePXSelfServDelTimeout AND Auto_Comment_Date__c = null AND CreatedDate >= :timeStart_sendSelfServiceDeletionEmail)';
criteria_selfServiceDeletionTimeout = '(Origin = :caseOriginSelfServDelSys AND Owner.Name = :queuePXSelfServDelTimeout AND Status = \'Pending\' AND CreatedDate >= :timeStart_selfServiceDeletionTimeout)';
criteria_sendUnresponsiveEmail = '(Owner.Type != \'Queue\' AND Threat_Non_Credible__c != \'Credible\' AND Status = \'Pending\' AND Auto_Comment_Date__c = null AND Last_Response_Date__c >= :timeStart_sendUnresponsiveEmail)';
criteria_closeUnresponsiveCase = '(Owner.Type != \'Queue\' AND Threat_Non_Credible__c != \'Credible\' AND Status = \'Pending\' AND Auto_Comment_Date__c != null AND Auto_Comment_Date__c >= :timeStart_closeUnresponsiveCase)';
criteria_reopenOnHoldCase = '(Owner.Type != \'Queue\' AND Status = \'On Hold\' AND On_Hold_End_Date__c >= :timeStart_reopenOnHoldCase)';
}
public void callPXEmailsFlow(Case caseRecord, Id templateId) {
try {
Map<String, Object> params = new Map<String, Object>();
params.put('rec_Case', caseRecord);
params.put('var_template_Id', templateId);
Flow.Interview.PX_Support_Emails flowObj = new Flow.Interview.PX_Support_Emails(params);
flowObj.start();
} catch (Exception ex) {
}
}
public void callCSATFlow(Id caseId) {
try {
Map<String, Object> params = new Map<String, Object>();
params.put('recordId', caseId);
Flow.Interview.PX_Send_CSAT flowObj = new Flow.Interview.PX_Send_CSAT(params);
flowObj.start();
} catch (Exception ex) {
}
}
}
Maybe another pair of eyes could see what I couldn't during these days. I really appreciate any help. Thank you so much!
