Causion with Describe Methods (Winter 18 Regression)

After Winter 18 release I encounter issue, that can demonstrate the risks when using describe methods improperly for dynamically looping over object fields.

Common usage for describe method can be as follow:

 Map<String, Schema.SObjectType> schemaMap = Schema.getGlobalDescribe();  
 Schema.SObjectType OppSchema = schemaMap.get('Opportunity');  
 map<String, Schema.SObjectField> OppfieldMap = OppSchema.getDescribe().fields.getMap();  
   
 for(String field: OppfieldMap.keySet()){  
      //Do something  
 }  

The customer develop customize sync process between Quote & Opportunity.
His process uses describe method to get all the Quote and Opportunity fields and synchronize any field which have the same API name.
This process start causing errors or unexpected behaviour after winter 18 release.

After investigating the issue, we found that the main change that break the process is that salesforce introduce/expose in Quote object the field ownerId, which is populated by the quote creator and cannot be updated.
Generally, quote is related to Opportunity in Master-Detail relation. In such case, normally the child (quote) doesn't have owner. The owner of the parent (opportunity) is also the owner of child (quote).

But seems SF might break this normal behaviour for Quote object. It is not documented in the release note, but it is probably related to the pilot Quotes Without Opportunities Pilot - Winter '18, which contains below feature and make sense to have the field ownerId in Quote.
    1. Create new quotes without opportunities
    2. Update or remove opportunities from existing quotes
    3. Control access on old and new quotes
    4. Change owner on old and new quotes


Anyway, back to the customize sync process. After winter 18, when running the sync process, if trying to sync the fields from Opportunity to Quote the process was failing with exception, as it try to update the opportunity owner to the quote owner, and the field is not writable.
If trying to sync the other direction - Quote to opportunity - the process success, but it wasn't the expected result - opportunity owner was override with the quote owner (which is the quote creator and is not always the same person).

As fix, we exclude hard-coded the field ownerId from the process. It solve the issue for now.
For future, their process does need to be amended to sync only explicit list of fields.

Some insights I took from this case:
1.should be careful when using describe methods and working with the entire fields. Even when retrieving the fields dynamically, it's recommended to limits the result with other setup -e.g. fieldSet, Custom Settings.... Actually, in many case you might use only the former setup (fieldSet, Custom Settings) to control the fields you need, instead of going over all object fields.

2.When working with standard Object should be carefull when override standard functionality.
You should use dynamic process, which allow you to have changes in future without changing the code, but must take under consideration also SF changes for their standard.

3.Run full tests after the release changes are in sandbox. Recommended full sandbox with integration. It not 100% insurance, but might help to detect regression issue in advance.

Using Salesforce History Data

There is build-in functionality in Salesforce for tracking history on specific fields, which can save you development efforts.

Per each object you can enable the tracking history feature, and select the fields you would like track. In each page layout you may add the history related list, that will show all changes for this record. All this feature is build-in and can be done with setup only.

I had once issue, with main object that have several telated childs objects. When user view the history for the object he doesn't want to dive into each child record, but  rather to see all the changes (main object + childs) in one place.
Therefore I develop kind of history page which show all changes together.
At first this page was design for the specific custom object. Lately, with some modification it was amended to work completely generic. Means, it can work on any custom object.
Its show only changes for custom objects, as SF objects sometimes have different names for their history table.

The relevant components are visualforce page and controller, usage is by the URL:
/apex/HistoryPage?ids=<recordID>

The URL should get as parameter: ids. The Id of the record you want to view its history.

First should select the objects (master/child) to view their changes, press 'Get Changes' and view the result.

I created for example custom object: "Parent Object", and 2 child custom object.

Controller Code:

public class vf_HistoryPage {  

 public Id objID {get; set;}        //main object id (should get as URL parameter)   
 public String objName {get; set;}     //main object name (should get as URL parameter)   

 public List<HistoryChange> historyChangeLst {get; set;}     //list with all changes   

 public list<String> childObjLst {get; set;}                       //list of all child objects   
 public map<String, Boolean> obj_showBol_map {get; set;}               //for each object if need to show its changes   
 public map<String, String> objLabel_API_map {get; set;}               //map from Label to API   

 private map<String, String> objAPI_objLabel=new map<String, String>();     //for each object its label name   
 private map<String, String> objAPI_fieldName_map=new map<String, String>();   //for each object the field name of his parent   

 private map<String, Set<String>> tableFieldAdd_Map=new Map<String, Set<String>>();   //used to prevent from same change return twice   

 public vf_HistoryPage() {  

  //record ID should be as parameters in the URL   
  objID=ApexPages.currentPage().getParameters().get('ids');   
  objName=objID.getsobjecttype().getDescribe().getName();   

  //DescribeResult of the main object   
  Schema.DescribeSObjectResult objectResult = Schema.getGlobalDescribe().get(objName).getDescribe();   

  //intialize maps   
  objLabel_API_map=new map<String, String>();   
  childObjLst=new list<String>{objectResult.getLabel()};   
  obj_showBol_map=new map<String, Boolean>();   

  //adding the main object   
  obj_showBol_map.put(objName, true);   
  objAPI_objLabel.put(objName, objectResult.getLabel());   
  objLabel_API_map.put(objectResult.getLabel(), objName);   
  objAPI_fieldName_map.put(objName, 'id');   

  for(Schema.ChildRelationship child : objectResult.getChildRelationships()) {  

   Schema.DescribeSObjectResult objRes=child.getChildSObject().getDescribe();   

   if(objRes.isCustom()) {  
    childObjLst.add(objRes.getLabel());   

    obj_showBol_map.put(objRes.getName(), true);   
    objAPI_objLabel.put(objRes.getName(), objRes.getLabel());   
    objLabel_API_map.put(objRes.getLabel(), objRes.getName());   
    objAPI_fieldName_map.put(objRes.getName(), child.getField().getDescribe().getName());   
   }   
  }   
  historyChangeLst=new List<HistoryChange>();   
 }   

 //Get all the changes   
 public PageReference getChanges() {  

  //clear list of changes   
  historyChangeLst.clear();   

  for(String childObjName : obj_showBol_map.KeySet()) {  
   if(obj_showBol_map.get(childObjName)) {  

    //reset this list for the specific object   
    tableFieldAdd_Map.put(childObjName, new Set<String>());   
 
    //collect all the childs IDs   
    Set<ID> childObjIDSet=new Set<ID>();   

    for(sObject childObj : Database.query('SELECT id FROM ' + childObjName + ' WHERE ' + objAPI_fieldName_map.get(childObjName) + ' =\'' + objID +'\'')) {   
     childObjIDSet.add(childObj.id);   
    }  

    //add all the object changes to list of all changes      
    if(!childObjIDSet.isEmpty()) {  
     addAllChanges(childObjIDSet,    
      objAPI_objLabel.get(childObjName),   
      childObjName,   
      childObjName.endsWith('__c') ? childObjName.replace('__c', '__History') : childObjName + 'History');   
    }  
   }   
  }   
  historyChangeLst.sort();   

  return ApexPages.currentPage();   
 }   

 public void addAllChanges(   
  Set<Id> idLst,   
  String objectName,   
  String tableName,   
  String tableHistoryName) {  

  String idConcatenateLst='';   
  String dynamicSQLHistory;   

  for(String objID : idLst) {  
   idConcatenateLst+= idConcatenateLst!='' ? ', \''+objID+'\'' : '\''+objID+'\'';   
  }  

  dynamicSQLHistory='SELECT id, oldvalue, parentid, newvalue, field, createdDate, CreatedBy.LastName, CreatedBy.FirstName';    
  dynamicSQLHistory+=' FROM ' + tableHistoryName + ' WHERE parentId IN ( ' + idConcatenateLst + ')';   
  dynamicSQLHistory+= ' order by createdDate';   

  try {   
   List<sObject> objLst=Database.query(dynamicSQLHistory);   
   sObject userObj;   
   String firstName, lastName;   

   for(sObject histObj : objLst) {  

    userObj=histObj.getSObject('CreatedBy');   
    firstName=(String)(userObj.get('FirstName'));   
    lastName=(String)(userObj.get('LastName'));   

    addChange(tableName,   
     (String)histObj.get('field'),   
     histObj.get('oldvalue'),   
     histObj.get('newValue'),   
     (DateTime)histObj.get('createdDate'),   
     (firstName==null ? '' : (firstName + ' '))+ (lastName==null ? '' : lastName),   
     objectName,   
     (String)histObj.get('parentid'));   
   }   
  }   
  catch(System.QueryException sqe) {  
   System.debug('##err: ' + sqe);   //possible error: object not support history   
   System.debug('##DYNAMIC SQL: ' + dynamicSQLHistory);   
  }   
  catch(Exception e) {  
   ApexPages.addMessage(new ApexPages.Message(ApexPages.severity.ERROR, ' Error with ' + tableName + '. ' + e));   
  }   
 }   

 //add change   
 private void addChange(   
  String table_Str,   
  String field_Str,   
  Object oldValue_Obj,   
  Object newValue_Obj,   
  DateTime createDate,   
  String user,   
  String objectName,   
  String objID) {  

  String fieldLabel_Str, name_Str='';   
  String result_Str='';   
  Boolean creation=false;   

  if(!tableFieldAdd_Map.get(table_Str).contains(field_Str+createDate+objID)) {  

   tableFieldAdd_Map.get(table_Str).add(field_Str+createDate+objID);   
   if(field_Str=='created')   {   
    result_Str='Record created';   
    creation=true;   
   }   
   else   {   
    if(oldValue_Obj != null && newValue_Obj != null) {  
     result_Str+=' changed from ' + (oldValue_Obj==null ? '' : oldValue_Obj) + ' to ' + (newValue_Obj==null ? '' : newValue_Obj);   
    }  
    else if(oldValue_Obj == null && newValue_Obj != null) {  
     result_Str+=' added ' + (newValue_Obj==null ? '' : newValue_Obj);   
    }  
    else if(oldValue_Obj != null && newValue_Obj == null) {  
     result_Str+=' deleted ' + (oldValue_Obj==null ? '' : oldValue_Obj);   
    }  
    else {  
     result_Str+=' Field has been changed';   //for long text field SF doesn't store the values   
    }  
   }   

   if(result_Str!=null && result_Str.trim() !='') {  
    historyChangeLst.add(new HistoryChange(result_Str, user, createDate, objectName, objID, table_Str, field_Str=='RecordType' ? 'RecordTypeID' : field_Str, creation));   
   }  
  }   
 }   

 //Object HistoryChange - represent history record   
 public class HistoryChange implements Comparable {  

  public String changeStr {get; set;}   
  public DateTime changeDate {get; set;}   
  public String userStr {get; set;}   
  public String objectName {get; set;}   
  public String objectID {get; set;}   
  public String objectAPI {get; set;}   
  public String fieldAPI {get; set;}   
  public Boolean creationChange {get; set;}   

  public HistoryChange(String pChange, String pUser, DateTime pChangeDate, String pObjectName, String pObjectID,   
   String pObjectAPI, String pFieldAPI, Boolean pCreationChange) {  
   changeStr=pChange;   
   changeDate=pChangeDate;   
   userStr=pUser;   
   objectName=pObjectName;   
   objectID=pObjectID;   
   objectAPI=pObjectAPI;   
   fieldAPI=pFieldAPI;   
   creationChange=pCreationChange;   
  }   

  public Integer compareTo(Object historyRec) {   
   HistoryChange compareToHC = (HistoryChange)historyRec;   
   if(changeDate!=compareToHC.changeDate) {  
    return changeDate >= compareToHC.changeDate ? 0 : 1;   
   }  
   else {  
    return creationChange ? 1 : 0;   
   }  
  }   
 }   
}  

Visualforce Page:

<apex:page controller="vf_HistoryPage">   
 <apex:pageMessages />   
 <apex:form id="frm">   
  <apex:pageBlock mode="edit" id="mainblock" title="Object Selection">   
   <apex:pageBlockButtons id="buttons" location="bottom">   
    <apex:commandButton value="Get Changes" action="{!getChanges}"/>   
   </apex:pageBlockButtons>   
   <apex:pageBlockSection id="filters" columns="1" collapsible="true">   
    <apex:repeat value="{!childObjLst}" var="obj">   
     <apex:pageBlockSectionItem >   
      <apex:outputText value="{!obj}"/>   
      <apex:inputCheckbox value="{!obj_showBol_map[objLabel_API_map[obj]]}"/>   
     </apex:pageBlockSectionItem>   
    </apex:repeat>   
   </apex:pageBlockSection>   
  </apex:pageBlock>   
  
  <apex:pageBlock mode="edit" id="results">   
   <apex:pageBlockTable value="{!historyChangeLst}" var="change">   
    <apex:column headerValue="Date">   
     <apex:outputText value="{0,date,dd'/'MM'/'yyyy HH:mm:ss}">   
      <apex:param value="{!change.changeDate}" id="datefromid"/>   
     </apex:outputText>   
    </apex:column>   
    <apex:column headerValue="User">   
     <apex:outputText value="{!change.userStr}"/>   
    </apex:column>   
    <apex:column headerValue="Object">   
     <apex:outputLink target="_blank" value="/{!change.objectID}" id="eds"> {!change.objectName}</apex:outputLink>   
    </apex:column>   
    <apex:column headerValue="Change">   
     <apex:outputPanel rendered="{!NOT change.creationChange}">   
      {!$ObjectType[change.objectAPI].fields[change.fieldAPI].Label}   
     </apex:outputPanel>   
     {!change.changeStr}   
    </apex:column>   
   </apex:pageBlockTable>   
  </apex:pageBlock>   
 </apex:form>   
</apex:page>  

Block Reassign in Approval Process

I have seen this issue in several cases - standard functionality of salesforce approval process is that the current approver may reassign the approval to any other user. There is no option in the system to have validation on this action, or even to remove completely the reassign button.

The main issue is that the reassign process, doesn't update the record in approval, it's only update the internal approval record - object: ProcessInstanceWorkItem - and of course, you cannot have trigger/workflow/validation on this object.

It was raised as Idea, and 1 option to solve this issue is by completely replacing the standard Approval History related list with visualforce page. The advantage of such solution is to have full flexibility on any of the approval actions, the disadvantage is that you are breaking big part of the standard functionality, this will raise your maintainace effort and in case Salesforce will add new functionality for the process it might break your code.

Alternative approach that I found is to leave the Reassign functionality as is, and instead block the Approval/Reject of non-authorize user.

This is quite easy to implement:

Step 1:
Create 2 fields on the relevant object that the approval related to (Case object in this example).
1.Last Approval Action Date ((Date/Time)
2.Last Approver User Id (Text)

Step 2:
Create 2 Fields Update:
1.Update Last Approval Action Date
Value: Now()

2.Update Last Approver User Id
Value: $User.Id

Step 3:
Add those 2 fields update on every approve/reject step in your approval process. Meaning, after any approval/rejection step the process will update the Date/Time and the user Id who runs the action.

Step 4:
Write trigger on the object that related to the approval process.
The trigger will block the approval/rejection if the user who run the process is not the original approver for the record

trigger CaseTrigger on Case (after update) {  

 //Per each case the user who run the approval/rejection  
 map<Id, Id> m_caseLastUser = new map<id, Id>();  

 for(case caseRecord : trigger.new){  

  //Relevant records are the one that Last Approval Action Date was changed  
  if(caseRecord.Last_Approval_Action_Date__c != trigger.oldMap.get(caseRecord.id).Last_Approval_Action_Date__c){  
   m_caseLastUser.put(caseRecord.Id, caseRecord.Last_Approval_User_Id__c);  
  }  
 }  

 if(!m_caseLastUser.isEmpty()){  

  //Loop over the ProcessInstanceWorkedItem of the relevant records  
  for( ProcessInstance pi : [SELECT Id, TargetObjectId , ( SELECT Id, StepStatus, Comments, ActorId, Actor.name,  
         OriginalActorId , OriginalActor.name FROM StepsAndWorkitems)  
         FROM ProcessInstance where TargetObjectId IN :m_caseLastUser.keySet()]){  

   for(ProcessInstanceHistory pih : pi.StepsAndWorkitems){  

    //If the Approver (Actor) on the record is the same as the Last Approval User from Case  
    //and the Approver is not the OriginalActor, raise on error  
    if(pih.ActorId == m_caseLastUser.get(pi.TargetObjectId)  
     && pih.ActorId != pih.OriginalActorId){  

     trigger.newMap.get(pi.TargetObjectId).addError('You are not the original approver. Please reassign it back to: '+ pih.OriginalActor.name);  
    }  
   }  
  }  
 }  
}  

Note, this logic can be enhance further per requirement.
For example, it's possible in addition to send the approval back to the original actor automatically by updating the ProcessInstanceWorkedItem record.
Other option is to run a scheduler every hour that will find any approval that the actual approver is no the original approver - in such cases if the record is still pending it can simply reassign it back, if it's already approver it can re-submit the record and assign it again to the user who reassign his approval at first.

Approval Process reminders

2022--> Might want to check the updated solution: https://lc169.blogspot.com/2022/05/approval-process-reminders.html

Following tool may be used for sending email reminders for pending approval in approval process in specific 
interval. The reminders will be keep sending until the related record be approved.
Note: the tool use Salesforce standard scheduling system and it scheduled to run every hour. 
This means that the reminders might not be accurate. E.g. If record was submitted for approval 
at 9:30 AM, and you setup reminders after 24 Hours, the reminder will be send at 10:00 AM (next day).
If you need it to be accurate you can expose it at service and call it with external tool.

Can install as manage package from here or download the code from git repository


Custom objects:

1. Approval Process Reminder:
Setup for sending reminders for specific approval process

Related Fields:

• Approval Process Reminder Name- Name of the process
• Related Approval Process- Related approval process Name
• Related Object- Related object of the approval process (should be object API name)
• Reminder After (H) - After how many hours send each reminder
• Business Hours - Lookup for Business Hours in Salesforce. This will indicate which days/hours count for this process.

Additional recipients for the reminders, other then the approver.

• Additional Recipient 1 - used to reference user field from the related record (e.g. owner).
• Additional Recipient 2 -  used to reference user field from the related record.
• Additional Recipient 3 -  Reference for specific user.
• Additional Recipient 4 - Reference for specific user.
• Additional Recipient 5 - Email Address.
• Additional Recipient 6 - Email Address.
• Alert Recipient X From Level - Those set of field indicate from which level of reminders send to each of the additional recipients. First alert is level 1, Second is level 2, and so on...

Schedule/Abort the process:

Go to the Approval Process Reminder tab and press the button Schedule Reminders Process, then click Schedule or Stop button

2. Approval Process Record
Relation for approver in specific approval. 
This record shouldn't be created manually, it will be created and maintain by the process.


Related Fields:

• Approval Process Reminder - Lookup for the parent approval process reminder setup
• Status- Status of this approval. Pending/Approved
• Approver - Lookup to the approver user
• Approver Name- Formula. Approver Name
• Additional Recipient 1 - Lookups to the additional recipients 
• Additional Recipient 2
• Additional Recipient 3
• Additional Recipient 4
• Additional Recipient 5
• Additional Recipient 6
• Alerts Sent- Number alerts that was sent
• Interval- Formula. After how many hours to send alert
• Pending Hours- Number of hours the record is pending approval
• ProcessInstance- Id of the processInstance of the approval in salesforce.
• Record Id- Id of the record in approval process
• Record Link- Link to the record in approval process
• Record Name - Record Type + Name of the record in approval process (e.g.: Opportunity: Test Opportunity)

Main class:

Apex class: ApprovalReminderUtils
This class receive as parameter list of approval process names and search for pending approval records.
It is invoked from the batch class (see full classes for more details.

global class ApprovalReminderUtils {
    
    
    public static void ApprovalProcessReminderMain(list<String> l_approvalNames){
        try{
        
            //Retrieve the relevant approval process reminder setup
            list<Approval_Process_Reminder__c> l_reminderSetup =    
                        [select id, Related_Object__c, Related_Approval_Process__c, Reminder_After__c, Business_Hours__c,
                            Additional_Recipient_1__c, Additional_Recipient_2__c,Additional_Recipient_3__c, Additional_Recipient_4__c, Additional_Recipient_5__c, Additional_Recipient_6__c,
                            Alert_Recipient_1_From_Level__c, Alert_Recipient_2_From_Level__c, Alert_Recipient_3_From_Level__c, Alert_Recipient_4_From_Level__c, Alert_Recipient_5_From_Level__c, Alert_Recipient_6_From_Level__c,
                            (select id, Status__c, Record_Id__c, ProcessInstance_Id__c, Alerts_Sent__c,Approver__c from Approval_Process_Records__r where status__c = 'Pending')
                        from Approval_Process_Reminder__c
                        where Related_Approval_Process__c In :l_approvalNames 
                            and Active__c = true];
            
            //Map - per each approval process, some related properties
            map<String, ApprovalProcessProperty> m_approvalProp = new map<String, ApprovalProcessProperty>();
            
            //list of APR recors for insert
            list<Approval_Process_Record__c> l_newAPR = new list<Approval_Process_Record__c>();    
            
            //list of APR record for update
            list<Approval_Process_Record__c> l_updAPR = new list<Approval_Process_Record__c>();    
            
            //Set of APR records that was increased by 1
            set<Id> s_aprId = new set<Id>();
            
            //set of users that exists as actor in the workItems 
            set<Id> s_userIds = new set<Id>();
            
            //per each key (record Id from approval + processInstanceId + approverId) its Approval Process Record (if exists)
                map<String, Approval_Process_Record__c> m_recId_APR = new map<String, Approval_Process_Record__c>();
                
            for(Approval_Process_Reminder__c reminderSetup : l_reminderSetup){
                
                System.debug('####' + reminderSetup.Related_Approval_Process__c);
                
                m_approvalProp.put(
                    reminderSetup.Related_Approval_Process__c, new ApprovalProcessProperty(reminderSetup));
                
                //list of records Id in approval process
                list<Id> l_TargetObjId = new list<Id>();
            
                for(Approval_Process_Record__c apr : reminderSetup.Approval_Process_Records__r){
                    m_recId_APR.put(apr.Record_ID__c + '_' + apr.ProcessInstance_Id__c + '_' + apr.Approver__c, apr);
                }
            }
            
            if(Test.isRunningTest()){
                //Create mock data
                Account acc = [select Id from Account limit 1];
                
                ProcessInstanceRecord pi = new ProcessInstanceRecord('testId', 'Pending', acc.Id, System.Now() - 1);
                
                ProcessInstanceItemRecord processInstanceItemRec = new ProcessInstanceItemRecord(UserInfo.getUserId(), 'Pending', System.Now()-3);
                        
                s_userIds.add(UserInfo.getUserId());
                        
                pi.addItem(processInstanceItemRec);
                
                m_approvalProp.get(l_reminderSetup.get(0).Related_Approval_Process__c).addRecordId(acc.Id);
                m_approvalProp.get(l_reminderSetup.get(0).Related_Approval_Process__c).addProcessInstance(pi);
            }
            else{
                System.debug('####' + m_approvalProp.keySet());
                
                for ( ProcessInstance pi:   [   SELECT Id, Status, TargetObjectId, LastModifiedDate, ProcessDefinition.DeveloperName, 
                                                    (SELECT Id, ActorId, StepStatus, CreatedDate FROM StepsAndWorkitems where StepStatus='Pending')
                                                FROM ProcessInstance 
                                                where Status IN ('Pending','Hold','Reassigned','NoResponse')
                                                    and ProcessDefinition.DeveloperName IN :m_approvalProp.keySet()]) {
                                                
                    ProcessInstanceRecord processInstanceRec = new ProcessInstanceRecord(pi.Id, pi.Status, pi.TargetObjectId, pi.LastModifiedDate);
                    
                    for (ProcessInstanceHistory pih : pi.StepsAndWorkItems) {
                        s_userIds.add(pih.ActorId);
                        
                        ProcessInstanceItemRecord processInstanceItemRec = new ProcessInstanceItemRecord(pih.ActorId, pih.StepStatus, pih.CreatedDate);
                        
                        processInstanceRec.addItem(processInstanceItemRec);
                    }
                    
                    m_approvalProp.get(pi.ProcessDefinition.DeveloperName).addRecordId(pi.TargetObjectId);
                    m_approvalProp.get(pi.ProcessDefinition.DeveloperName).addProcessInstance(processInstanceRec);
                }
            }
            
            if(! s_userIds.isEmpty()){
            
                //In each approval process properties initialaze the related object with users fields
                for(String approvalKey : m_approvalProp.keySet()){
                    m_approvalProp.get(approvalKey).queryRelatedObjects();
                    
                    //Loop over approval process instance
                    for(ProcessInstanceRecord pi : m_approvalProp.get(approvalKey).l_instanceRecords){
                    
                        //Per each pending approver
                        for (ProcessInstanceItemRecord pih : pi.items) {
                            
                            //Calculate the hours difference from time processinstance was created until now
                            Double pendingHours = (Double) BusinessHours.diff(
                                m_approvalProp.get(approvalKey).approvalProcessReminder.Business_Hours__c, pih.CreatedDate, system.now())/1000/60/60;
                                
                            system.debug('###pendingHours:' + pendingHours);
                            
                            //If target object id inside the map, means alert was already sent for this record before
                            if(m_recId_APR.containsKey(pi.targetObjectId + '_' + pi.id + '_' + pih.ActorId)){
                                //calculate if hours that approval is pending divded by the alerts that were sent is greater than current number of alerts
                                //If criteria aply - means it time to send another reminder
                                if(math.Floor(pendingHours/m_approvalProp.get(approvalKey).approvalProcessReminder.Reminder_After__c) > m_recId_APR.get(pi.targetObjectId + '_' + pi.id + '_' + pih.ActorId).Alerts_Sent__c){
                                
                                    //Increase the Alert Send by 1, this will run the workflow rule
                                    m_recId_APR.get(pi.targetObjectId + '_' + pi.id + '_' + pih.ActorId).Alerts_Sent__c ++;
                                
                                    m_recId_APR.get(pi.targetObjectId + '_' + pi.id + '_' + pih.ActorId).Pending_Hours__c = pendingHours;
                                
                                    //Populate the new recipients if needed
                                    integer alertCounter = Integer.valueOf(m_recId_APR.get(pi.targetObjectId + '_' + pi.id + '_' + pih.ActorId).Alerts_Sent__c);
                                    
                                    m_recId_APR.get(pi.targetObjectId + '_' + pi.id + '_' + pih.ActorId).Additional_Recipient_1__c = m_approvalProp.get(approvalKey).getUser(pi.targetObjectId, 1, alertCounter); 
                                    m_recId_APR.get(pi.targetObjectId + '_' + pi.id + '_' + pih.ActorId).Additional_Recipient_2__c = m_approvalProp.get(approvalKey).getUser(pi.targetObjectId, 2, alertCounter); 
                                    m_recId_APR.get(pi.targetObjectId + '_' + pi.id + '_' + pih.ActorId).Additional_Recipient_3__c = m_approvalProp.get(approvalKey).getUser(pi.targetObjectId, 3, alertCounter); 
                                    m_recId_APR.get(pi.targetObjectId + '_' + pi.id + '_' + pih.ActorId).Additional_Recipient_4__c = m_approvalProp.get(approvalKey).getUser(pi.targetObjectId, 4, alertCounter); 
                                    m_recId_APR.get(pi.targetObjectId + '_' + pi.id + '_' + pih.ActorId).Additional_Recipient_5__c = m_approvalProp.get(approvalKey).getUser(pi.targetObjectId, 5, alertCounter); 
                                    m_recId_APR.get(pi.targetObjectId + '_' + pi.id + '_' + pih.ActorId).Additional_Recipient_6__c = m_approvalProp.get(approvalKey).getUser(pi.targetObjectId, 6, alertCounter); 
                                
                                    //This will be used later. Need to know which Approval Process Records was increased. Other
                                    //records will be updated with Status = 'Approved'
                                    s_aprId.add(m_recId_APR.get(pi.targetObjectId + '_' + pi.id + '_' + pih.ActorId).id);
                                }
                            }
                            else{   //No previous alert was sent for this record
                            
                                //Pending hour is more than the setup, should send first alert
                                if(pendingHours > m_approvalProp.get(approvalKey).approvalProcessReminder.Reminder_After__c){ 
                                    //Add new Approval Process Record        
                                    l_newAPR.add(new Approval_Process_Record__c(Approval_Process_Reminder__c = m_approvalProp.get(approvalKey).approvalProcessReminder.id,
                                                                                Status__c = 'Pending', 
                                                                                Record_Id__c = pi.targetObjectId, 
                                                                                ProcessInstance_Id__c = pi.id, 
                                                                                Alerts_Sent__c = 1,
                                                                                Pending_Hours__c = pendingHours,
                                                                                Approver__c = pih.ActorId,
                                                                                Additional_Recipient_1__c = m_approvalProp.get(approvalKey).getUser(pi.targetObjectId, 1, 1),
                                                                                Additional_Recipient_2__c = m_approvalProp.get(approvalKey).getUser(pi.targetObjectId, 2, 1),
                                                                                Additional_Recipient_3__c = m_approvalProp.get(approvalKey).getUser(pi.targetObjectId, 3, 1),
                                                                                Additional_Recipient_4__c = m_approvalProp.get(approvalKey).getUser(pi.targetObjectId, 4, 1),
                                                                                Additional_Recipient_5__c = m_approvalProp.get(approvalKey).getUser(pi.targetObjectId, 5, 1),
                                                                                Additional_Recipient_6__c = m_approvalProp.get(approvalKey).getUser(pi.targetObjectId, 6, 1)));
                                }
                            }
                        }
                    }
                    
                    //Evaluate old APR records that are no longer in the approval process. Should update their status to 'Approved'
                    for(Approval_Process_Record__c apr : m_approvalProp.get(approvalKey).approvalProcessReminder.Approval_Process_Records__r){
                        //if not update earlier
                        if(!s_aprId.contains(apr.id)){
                            apr.Status__c = 'Approved';
                        }
                    }
                    
                    l_updAPR.addAll(m_approvalProp.get(approvalKey).approvalProcessReminder.Approval_Process_Records__r);
                }
                    
                //New Approval Process Records for insert
                if(! l_newAPR.isEmpty()){
                    insert l_newAPR;
                }
                
                //Update all existing Approval Process Records. This list contain 3 type of records:
                //1.Records that Alert_Sent was increased, and now another reminder will be send for them
                //2.Records that are no longer found in the pending ProcessInstances, therefore will be upadated to status 'Approved'
                //3.Records without any change in this run
                if(!l_updAPR.isEmpty()){
                    update l_updAPR; 
                }
            }
        }
        catch(Exception e){
            //Might want to sent email alert to admin user, and notify the issue
            system.debug('###' + e);
        }
    }
    

    public class ApprovalProcessProperty{
        public Approval_Process_Reminder__c approvalProcessReminder;
        public list<String> l_recordsIds;
        public list<ProcessInstanceRecord> l_instanceRecords;
        
        map<String, SObject> m_relatedRecords;
        
        public ApprovalProcessProperty(Approval_Process_Reminder__c pApprovalProcessReminder){
            approvalProcessReminder = pApprovalProcessReminder;
            
            l_recordsIds = new list<String>();
            l_instanceRecords = new list<ProcessInstanceRecord>();
            m_relatedRecords = new map<String, SObject>();
        }
        
        public void addRecordId(String recordId){
            l_recordsIds.add(recordId);
        }
        
        public void addProcessInstance(ProcessInstanceRecord processRecord){
            l_instanceRecords.add(processRecord);
        }
        
        public void queryRelatedObjects(){
            if(!l_recordsIds.isEmpty() 
               && (approvalProcessReminder.Additional_Recipient_1__c != null || approvalProcessReminder.Additional_Recipient_2__c != null)){
                
                //Should collect all relevant records Ids which currently in approval process, and query their related users (According to the setup in approval reminder)
                String sql = 'select id';
                
                if(approvalProcessReminder.Additional_Recipient_1__c != null
                    && approvalProcessReminder.Additional_Recipient_1__c != 'None'){
                    sql += ',' + approvalProcessReminder.Additional_Recipient_1__c;
                }
                if(approvalProcessReminder.Additional_Recipient_2__c != null
                    && approvalProcessReminder.Additional_Recipient_2__c != 'None'){
                    sql += ',' + approvalProcessReminder.Additional_Recipient_2__c;
                }
                
                for(sObject obj : Database.query(sql + ' from ' + approvalProcessReminder.Related_Object__c + ' where id in :l_recordsIds')){
                    m_relatedRecords.put(obj.id, obj);
                }
            }
        }
        
        public String getUser(String recordId, integer recipiantNum, integer approvalLevel){
            if(recipiantNum == 1 
                && approvalProcessReminder.Alert_Recipient_1_From_Level__c <= approvalLevel
                && approvalProcessReminder.Additional_Recipient_1__c != null
                && approvalProcessReminder.Additional_Recipient_1__c != 'None'){
                return (String) m_relatedRecords.get(recordId).get(approvalProcessReminder.Additional_Recipient_1__c);
            }
            else if(recipiantNum == 2 
                && approvalProcessReminder.Alert_Recipient_2_From_Level__c <= approvalLevel
                && approvalProcessReminder.Additional_Recipient_2__c != null
                && approvalProcessReminder.Additional_Recipient_2__c != 'None'){
                return (String) m_relatedRecords.get(recordId).get(approvalProcessReminder.Additional_Recipient_2__c);
            }
            else if(recipiantNum == 3 
                && approvalProcessReminder.Alert_Recipient_3_From_Level__c <= approvalLevel
                && approvalProcessReminder.Additional_Recipient_3__c != null){
                return approvalProcessReminder.Additional_Recipient_3__c;
            }
            else if(recipiantNum == 4 
                && approvalProcessReminder.Alert_Recipient_4_From_Level__c <= approvalLevel
                && approvalProcessReminder.Additional_Recipient_4__c != null){
                return approvalProcessReminder.Additional_Recipient_4__c;
            }
            else if(recipiantNum == 5 
                && approvalProcessReminder.Alert_Recipient_5_From_Level__c <= approvalLevel
                && approvalProcessReminder.Additional_Recipient_5__c != null){
                return approvalProcessReminder.Additional_Recipient_5__c;
            }
            else if(recipiantNum == 6 
                && approvalProcessReminder.Alert_Recipient_6_From_Level__c <= approvalLevel
                && approvalProcessReminder.Additional_Recipient_6__c != null){
                return approvalProcessReminder.Additional_Recipient_6__c;
            }
            
            return null;
        }
    }
    
    //Wrap the standard salesfore ProcessInstance/InstanceItems
    public class ProcessInstanceRecord{
        
        public String id;
        public String status;
        public String targetObjectId;
        public DateTime lastModifiedDate;
        
        public list<ProcessInstanceItemRecord> items;
        
        public ProcessInstanceRecord(String pId, String pStatus, String pTargetObjectId, DateTime pDateTime){
            id = pId;
            status = pStatus;
            targetObjectId = pTargetObjectId;
            lastModifiedDate = pDateTime;
            
            items = new list<ProcessInstanceItemRecord>();
        }
        
        public void addItem(ProcessInstanceItemRecord itemRecord){
            items.add(itemRecord);
        }
    }
    
    
    public class ProcessInstanceItemRecord{
        public String actorId;
        public String stepStatus;
        public DateTime createdDate;
        
        public ProcessInstanceItemRecord(String pActorId, String pStepStatus, Datetime pCreatedDate){
            actorId = pActorId;
            stepStatus = pStepStatus;
            createdDate = pCreatedDate;
        }
    }
    
    
    //###########################################
    //### Schedule job to run
    //###########################################   
    webservice static String scheduleProcess(){
        String retMsg;
        
        try{
            list<CronTrigger> l_cr = [SELECT id, CronExpression, TimesTriggered, CronJobDetail.Name FROM CronTrigger WHERE CronJobDetail.Name = 'ApprovalProcessReminders' limit 1];
        
            if(l_cr.isEmpty()){
                String jobID = system.schedule('ApprovalProcessReminders', '0 0 * ? * *', new ScheduleApprovalReminders());

                retMsg = 'Process was scheduled';
            }
            else{
                retMSg = 'Process is already schduled.';
            }
        }
        catch(Exception e){
            retMsg = 'Error: ' + e.getMessage();
        }
        
        return retMsg;
    }

    //###########################################
    //### Abord job that is running
    //###########################################   
    webservice static String abortProcess(){
        string retMsg;
        try{
            list<CronTrigger> l_cr = [SELECT id, CronExpression, TimesTriggered, CronJobDetail.Name FROM CronTrigger WHERE CronJobDetail.Name = 'ApprovalProcessReminders' limit 1];
        
            if(l_cr.isEmpty())
                retMsg = 'Process is not schedule';
            else{
                System.abortJob(l_cr.get(0).id);
            
                retMSg = 'Process was aborted';
            }
        }
        catch(Exception e){
            retMsg = 'Error: ' + e.getMessage();
        }
            
        return retMsg;
    }
}