Below is my Schedulable apex code. I would like to select data from 5 tables: wkCSRCorderMaster__c(main), InstallationMaster__c, AddressMaster__c, ItemMaster__c, and ItemAddonMaster__c.
Basically, there are some relationships among them but I don't know how to use sub-query or write as 'left outer join' in this case. Hence, I divided them by some of distinct queries as you can see.
The problem is that there's a lot of data in wkCSRCorderMaster once time (about 50-100 records), but Apex Execution Governors Limitation for SOQL commands is just only 100 times for Synchronous Limit and 200 times Asynchronous Limit.
Therefore, my code will not work as I expected.
Please help me to consider whether I can reduce SOQL commands or combine them into just only 1 by using Left Outer Join syntax or sub query.
Global class C_Apex_CSRCUpdate implements Schedulable {
Global void execute (SchedulableContext SC){
// Constant variable
String ListElement; // List Element
String tempCompany; // Company
String tempRecordType; // Record Type
// Constant for Incident Record
String IncCategory = [SELECT Id FROM BMCServiceDesk__Category__c WHERE Name = :'ServiceRequest.'].Id; // Incident Category
String IncImpact = [SELECT Id FROM BMCServiceDesk__Impact__c WHERE Name = :'low.'].Id; // Incident Impact
String IncUrgency = [SELECT Id FROM BMCServiceDesk__Urgency__c WHERE Name = :'low.'].Id; // Incident Urgency
String IncStatus = [SELECT Id FROM BMCServiceDesk__Status__c WHERE Name = :'Close.'].Id; // Incident Status
// List Incident
List<BMCServiceDesk__Incident__c> wkIncList = new List<BMCServiceDesk__Incident__c>();
// Assignment
for(wkCSRCorderMaster__c wkCSRCorderMaster: [SELECT Id, // wkCSRCorderMaster ID
Name, // wkCSRCorderMaster Name
CreatedDate, // wkCSRCorderMaster CreatedDate
HAIS_SK_NAME__c, // wkCSRCorderMaster HAIS_SK_NAME
HAIS_SK_ADD1__c, // wkCSRCorderMaster HAIS_SK_ADD1
HAIS_SK_ADD2__c, // wkCSRCorderMaster HAIS_SK_ADD2
HAIS_SK_ADD3__c, // wkCSRCorderMaster HAIS_SK_ADD3
HAIS_SK_TEL__c, // wkCSRCorderMaster HAIS_SK_TEL
JTCH_DMPY_NO__c, // wkCSRCorderMaster JTCH_DMPY_NO
HMMK_CD__c, // wkCSRCorderMaster HMMK_CD
NEW_ITEM_CD__c, // wkCSRCorderMaster NEW_ITEM_CD
OYA_KBN__c, // wkCSRCorderMaster OYA_KBN
STCI_DATE__c // wkCSRCorderMaster STCI_DATE
FROM wkCSRCorderMaster__c ORDER BY CreatedDate ASC]){
// Select data from wkInstallationMaster based on wkCSRCorderMaster
InstallationMaster__c InstallationMaster = [SELECT Id,
Name,
MACHINE_ID__c,
KBN__c
FROM InstallationMaster__c
WHERE HMMK_CD__c =: wkCSRCorderMaster.NEW_ITEM_CD__c
AND KBN__c =: wkCSRCorderMaster.OYA_KBN__c
AND STCI_DATE__c =: wkCSRCorderMaster.STCI_DATE__c
AND EIGY_TNTO_BMN_CD__c IN ('M76' , 'N99')];
// Select data from AddressMaster based on wkCSRCorderMaster
AddressMaster__c wkAddressMaster = [SELECT Id,
Name,
JUSHO_KNJ__c
FROM AddressMaster__c
WHERE JUSHO_CD__c =: wkCSRCorderMaster.HAIS_SK_ADD1__c];
// Select data from ItemMaster based on wkCSRCorderMaster
ItemMaster__c wkItemMaster = [SELECT Id,
Name,
MAKTX2__c
FROM ItemMaster__c
WHERE Name =: wkCSRCorderMaster.HMMK_CD__c];
// Select data from ItemAddonMaster based on wkCSRCorderMaster
ItemAddonMaster__c wkItemAddonMaster = [SELECT Id,
Name,
KSHU_NAME__c
FROM ItemAddonMaster__c
WHERE HMMK_CD__c =: wkCSRCorderMaster.HMMK_CD__c];
// Get Company name from MachineID
tempRecordType = GetRecordType(InstallationMaster.MACHINE_ID__c);
// New Incident
BMCServiceDesk__Incident__c wkInc = new BMCServiceDesk__Incident__c();
wkInc.Gender__c = 'unknown'; // Gender
wkInc.BMCServiceDesk__FKCategory__c = IncCategory; // Category
wkInc.Product_category__c = 'MFP-KM'; // Product category
wkInc.Resolution_result__c = 'エスカレーション'; // Resolution result
wkInc.BMCServiceDesk__FKImpact__c = IncImpact; // Impact
wkInc.BMCServiceDesk__FKUrgency__c = IncUrgency; // Urgency
wkInc.BMCServiceDesk__FKStatus__c = IncStatus; // Status
wkInc.RecordTypeId = tempRecordType; // RecordType
wkInc.Change_InstallationCustomerName__c = wkCSRCorderMaster.HAIS_SK_NAME__c; // Installation phone
wkInc.Change_InstallationAddress__c = wkAddressMaster.JUSHO_KNJ__c + ' ' + wkCSRCorderMaster.HAIS_SK_ADD2__c + ' ' + wkCSRCorderMaster.HAIS_SK_ADD2__c;
wkInc.MID__c = InstallationMaster.MACHINE_ID__c; // MachineID
wkInc.Change_InstallationPhone__c = wkCSRCorderMaster.HAIS_SK_TEL__c; // Installation phone
wkInc.Subject__c = 'iCare発送済み：'+ wkItemMaster.MAKTX2__c; // Incident subject
// Add wkInc to List
wkIncList.add(wkInc);
System.debug('▼ [CSRCUpdate] add incident to list.');
// delete wkCSRCorderMaster;
} // End for
try{
insert wkIncList;
System.debug('▼ [CSRCUpdate] insert completed.');
}catch(system.DmlException e){
System.debug('Cannot insert new Incident because following exception has occurred:' + e.getMessage() + e.getStackTraceString());
}
} // End constructor
// Method to get company name
public static string GetRecordType(String MachineID){
String wkRecordType;
//String wkCompany;
String MachineName;
try{
// Get MachineName from MachineID
List<BMCServiceDesk__BMC_BaseElement__c> tempListMachineName = [SELECT Name
FROM BMCServiceDesk__BMC_BaseElement__c
WHERE Id =: MachineID];
if(tempListMachineName.size()>0){
MachineName = [SELECT Name
FROM BMCServiceDesk__BMC_BaseElement__c
WHERE Id =: MachineID Limit 1].Name;
} else{
System.debug('There is no MachineName related to MachineID');
}
if(MachineName.startsWith('NU')){
//wkCompany = 'UNISYS';
wkRecordType = [SELECT Id FROM RecordType WHERE Name = :'BJ-INC-UNISYS'].Id;
}else{
//wkCompany = 'ANA';
wkRecordType = [SELECT Id FROM RecordType WHERE Name = :'BJ-INC-ANA'].Id;
}
// Return incident RecordType
return wkRecordType;
}catch(System.DmlException e){
return e.getMessage();
}
}// End method
} // End class
