function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
SFDC Admin & AnalystSFDC Admin & Analyst 

Issue with Aggergate query summing in USD for CAD opportunity

Hi I have an issue in code with Aggregate query.

 

I have an object Deal Summary which sums up fields in Opportunity products and displays as related list in opportunity.

 

This is done by an after update trigger on oppportunity. But here if the currency of an Opportunity product is CAD, It shows up the deal Summary in USD; because the aggregate query is aggregating everything in USD.

 

Here is my trigger

 

Trigger SGSFA_NA_UPDATE_DEAL_SUMMARY on Opportunity (After Update) {
If(!OpptyDS_LoopFollowUp.hasAlreadyRun()){
    OpptyDS_LoopFollowUp.setAlreadyRun();
    
    

    List<ID> O_Id = New List<ID>{};
    System.Debug('Opportuniy Ids List here'+O_Id); 
    
    
    List<Deal_Credit__c> DSummaryUpsertList = New  List<Deal_Credit__c> {};  
    System.Debug('DealSummaryUpsertList'+DSummaryUpsertList);  
    
    Map<String,ID> DSummaryMap = New Map<String,ID>();
    System.Debug('DSummaryMap'+DSummaryMap);
    
    List<OpportunityLineItem> oli_Update = New List<OpportunityLineItem>();
    System.Debug('oli_Update'+oli_Update);
    
    Decimal CRR = 0, GCR = 0, OTF = 0, MRR = 0, MRR_C=0;                           
    
    For(Opportunity O: Trigger.New){  If(O.Record_Type_Name__c.indexOf('NA') != -1) {  O_Id.add(O.ID);}}
    
   

    List<OpportunityLineItem> oli = [SELECT ID, OpportunityID, deal_summary__c, schedule_id__c,CurrencyIsoCode FROM OpportunityLineItem 
                                     WHERE OpportunityID in :O_Id];
                                    
                                    
    System.Debug('oli'+oli);                                         
                   
                                     
    If(!oli.isEmpty()){ //Needed for opportunity with no products created yet but when it's updated
        //Delete existing records
        For(Deal_Credit__c DS: [select ID, Name from Deal_Credit__c WHERE Opportunity__c in :O_Id]){DSummaryMap.put(DS.Name,DS.Id);}
    

        For(AggregateResult ar : [SELECT SUM(Current_Run_Rate__c) CRR, SUM(UnitPrice) MRR, SUM(Incremental_GCR__c) GCR, 
                                        SUM(OTF__c) OTF, SUM(Gross_MRR__c) MRR_Credit, Schedule_ID__c, OpportunityID
                                        FROM OpportunityLineItem WHERE OpportunityID in :O_Id GROUP BY OpportunityID, Schedule_ID__c]){
                                        
                                        System.Debug('Agg Results'+ar);
            CRR = 0; GCR = 0; OTF = 0; MRR = 0; MRR_C = 0; 
            If(ar.get('MRR')+ '' != '' && ar.get('MRR')!=null) MRR =  (Decimal.valueOf(ar.get('MRR')+''));
            If(ar.get('GCR')+ '' != '' && ar.get('GCR') != null) GCR=  Decimal.valueOf(ar.get('GCR')+'');
            If(ar.get('CRR')+ '' != '' && ar.get('CRR')!= null ) CRR=  Decimal.valueOf(ar.get('CRR')+'');
            If(ar.get('OTF')+ '' != '' &&  ar.get('OTF')!= null ) OTF=  Decimal.valueOf(ar.get('OTF')+'');
            If(ar.get('MRR_Credit')+ '' != ''&&  ar.get('MRR_Credit')!= null ) MRR_C=  Decimal.valueOf(ar.get('MRR_Credit')+'');
            If(ar.get('Schedule_ID__c')!= null){
                If(!DSummaryMap.containsKey(ar.get('Schedule_ID__c')+ '')){
                
                
                    DSummaryUpsertList.add(new Deal_Credit__c(Name = ar.get('Schedule_ID__c')+ '', Opportunity__c = ar.get('OpportunityID')+ '',
                                        Incremental_MRR__c = MRR, Current_Run_Rate__c = CRR, Incremental_GCR__c = GCR, 
                                        One_Time_Fee__c = OTF,MRR_Credit__c = MRR_C)); // Needs revision
                                        System.Debug('In If loop'+DSummaryUpsertList);
                } Else {
                    DSummaryUpsertList.add(new Deal_Credit__c(Name = ar.get('Schedule_ID__c')+ '', Opportunity__c = ar.get('OpportunityID')+ '',
                                        Incremental_MRR__c = MRR, Current_Run_Rate__c = CRR, Incremental_GCR__c = GCR, //CurrencyIsoCode = String.valueof(ar.get('CurrencyIsoCode')),
                                        One_Time_Fee__c = OTF,MRR_Credit__c = MRR_C, Id = DSummaryMap.get(ar.get('Schedule_ID__c')+ ''))); // Updates old record
                                        System.Debug('In Else loop'+DSummaryUpsertList);
                }
            }
        }
        If(!DSummaryUpsertList.isEmpty()){Upsert DSummaryUpsertList;}// If Schedule Ids are null from above
        // Upsert will updates old ones and inserts new records
        System.Debug('DSummaryUpsertList'+DSummaryUpsertList);
        For(Deal_Credit__c dc: DSummaryUpsertList){
            For(OpportunityLineItem ol: oli){
                If((dc.name == ol.schedule_id__c && dc.Opportunity__c == ol.OpportunityID) 
                    && (ol.deal_summary__c == Null || ol.deal_summary__c != dc.id)){  
                    ol.deal_summary__c = dc.id;  
                    oli_Update.add(ol); // To update only for product with new deal summary value
                }
                
                
            }
        }
        If(!oli_Update.isEmpty()){ Update oli_Update;} // When all products have deal summary field set
    }
    
  
}  

    
       
    
   
}

 

Please help. I am stuck here. How do aggregate in Canadian Dollars.

craigmhcraigmh

Unfortunately, you can't:

 

"If a query includes aGROUP BYorHAVINGclause, any currency data returned by using an aggregate function, such asSUM()orMAX(), is in the organization's default currency. You cannot convert the result of an aggregate function into the user's currency by calling theconvertCurrency()function."

 

http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_calls_soql_querying_currency_fields.htm