5

I wrote this trigger to per-form roll up operation on Child (Order line )to Parent (Order) Object related using Lookup field. Basically it sums up the currency amount of all order line records that are associated with a particular order and displays that value on order page based on some condition.

This codes is working perfectly for small amount of records but when I am updating large no of records via dataloader this code is not working at all.

I guess it need to be bulkified. could anyone please guide me with the correct problem and fixing that. Below is the code -

trigger PFE_DE_YearToDateSales on Order_Line_vod__c (after insert, after update) 
{
    Set<String> olStatus = new Set<String>{'Delivered','Invoiced','Open Order','Backordered'};
    Set<Id> updateOrderIds = new set<Id>();
    List<Order_Line_vod__c> Orderllist = new List<Order_Line_vod__c>(); 
    List<Order_vod__c> odList = new List<Order_vod__c>();
    List<Order_vod__c> odListPrev = new List<Order_vod__c>();
    Set<String> oOStatus = new Set<String>{'Sent','Ready for Fulfillment'};
    Integer curr_year = System.today().year();
    Integer prev_year = System.today().year()-1;
    system.debug('pREV yEAR'+prev_year);

    Double Sum1 = 0.0;
    Double Sum2 = 0.0;

    Map<Id,Order_Line_vod__c> oliMap = new Map<Id,Order_Line_vod__c>();
    Map<Id,Decimal> oliMapNA = new Map<Id,Decimal>(); 
    Map<Id,Decimal> oliMapNAPrev = new Map<Id,Decimal>(); 

    Map<Id,Order_vod__c> ordMap = new Map<Id,Order_vod__c>(); 
    Map<Id,Order_vod__c> ordMapPrev = new Map<Id,Order_vod__c>(); 

    Set<Id> oliId = new Set<Id>();
    Set<Id> ordrId = new Set<Id>();

    Set<Id> ordId = new Set<Id>();
    Set<Id> ordIdPrev = new Set<Id>();

    Map<Id,Decimal> ordMapNA = new Map<Id,Decimal>(); 

    Map<Id,Id> ord2oliMap = new Map<Id,Id>();

    User loggedinUser = [SELECT Id
                                , PFE_Country__c 
                            FROM User 
                            WHERE Id =: UserInfo.getUserId()];   

    If(loggedinUser.PFE_Country__c == 'DE' || loggedinUser.PFE_Country__c == 'HQ')   
    {
        // 1st block for Order Line Item - Order 
        for (Order_Line_vod__c oli : [SELECT id
                                            , Order_vod__c
                                            , Net_Amount_vod__c
                                            , PFE_Status__c
                                        FROM Order_Line_vod__c 
                                        WHERE Id IN :trigger.new 
                                        AND PFE_Status__c IN :olStatus])
        {
            oliId.add(oli.id);
            ord2oliMap.put(oli.id,oli.Order_vod__c);
            ordrId.add(ord2oliMap.get(oli.id));
            system.debug('++++++++'+ordrId); 
            system.debug('__________'+oliId);
        }   

        for (Order_vod__c ord : [SELECT id
                                        , Net_Amount_vod__c
                                        , Status_vod__c
                                    FROM Order_vod__c 
                                    WHERE Id IN :ordrId
                                        AND PFE_Order_Status__c IN :oOStatus])
        {
            if(ord.Status_vod__c.contains('Submitted_vod'))
            {
                ordrId.add(ord.id);
            }
        }

        List<Order_Line_vod__c> oliList = new List<Order_Line_vod__c>([SELECT Id
                                                                            , Name
                                                                            , CurrencyIsoCode
                                                                            , PFE_Order_Status__c
                                                                            , Net_Amount_vod__c
                                                                            , Order_vod__c
                                                                            , PFE_Status__c 
                                                                        FROM Order_Line_vod__c
                                                                        WHERE Id IN :oliId]);
        system.debug('***********'+oliList); 

        //Current year OrderList
        List<Order_vod__c> ordList = new List <Order_vod__c>([SELECT id
                                                                    , CurrencyIsoCode
                                                                    , Order_Date_vod__c
                                                                    , (SELECT id
                                                                            , Net_Amount_vod__c
                                                                            , PFE_Actual_Delivery_Date__c 
                                                                        FROM Order_Lines_vod__r) 
                                                                FROM Order_vod__c 
                                                                WHERE id IN :ordrId 
                                                                    AND CALENDAR_YEAR(Order_Date_vod__c) = :curr_year]);

        //previous year OrderList       
        List<Order_vod__c> ordListPrev = new List <Order_vod__c>([SELECT id
                                                                        , CurrencyIsoCode
                                                                        , Order_Date_vod__c
                                                                        , (SELECT id
                                                                                , Net_Amount_vod__c
                                                                                , PFE_Actual_Delivery_Date__c 
                                                                            FROM Order_Lines_vod__r) 
                                                                    FROM Order_vod__c 
                                                                    WHERE id IN :ordrId 
                                                                        AND CALENDAR_YEAR(Order_Date_vod__c) = :prev_year]);


        system.debug('tHE lIST oF cURRENT oRDERS'+ordList); 
        system.debug('tHE lIST oF lAST oRDERS '+ordListPrev);

        for(Order_Line_vod__c oli : oliList)
        {
            oliMap.put(oli.Id,oli); 
        }
        //Current year Order
        for(Order_vod__c ord : ordList)
        {
            ordMap.put(ord.Id,ord);
        }

        //Previous year Order
        for(Order_vod__c ord : ordListPrev)
        {
            ordMapPrev.put(ord.Id,ord);
        }

        system.debug('@@@@@@@@@@'+oliMap); 
        system.debug('##########'+ordMap); 
        system.debug('oRD_mAP_pREV'+ordMapPrev);

        ordId =  ordMap.keySet();
        ordIdPrev =  ordMapPrev.keySet();

        system.debug('oRD_iD'+ordId); 
        system.debug('oRD_iD_pREV'+ordIdPrev); 


        //Current year Order - Order Line Item 
        for(Order_Line_vod__c OrdLineItem : [SELECT Id
                                                    , Name
                                                    , CurrencyIsoCode
                                                    , Order_vod__c
                                                    , PFE_Order_Status__c
                                                    , Net_Amount_vod__c
                                                    , PFE_Status__c 
                                            FROM Order_Line_vod__c  
                                            WHERE Order_vod__c  IN :ordId 
                                                AND PFE_Status__c IN :olStatus])
        {
            if(!oliMapNA.ContainsKey(OrdLineItem.Order_vod__c))
            {
                oliMapNA.Put(OrdLineItem.Order_vod__c,OrdLineItem.Net_Amount_vod__c); //If the Order Id does not exist then add it to Map
                system.debug('oLI_mAP_NA_cURRENT_iF'+oliMapNA);
            }
            else 
            {
                if (OrdLineItem.Net_Amount_vod__c > 0){
                    Sum1 = oliMapNA.get(OrdLineItem.Order_vod__c) + OrdLineItem.Net_Amount_vod__c;
                    oliMapNA.Put(OrdLineItem.Order_vod__c,Sum1);
                    system.debug('oLI_mAP_NA_cURRENT_eLSE'+oliMapNA);
                    system.debug('(((((((((('+Sum1 ); 
                }
            }
        }

        for(Id ids: oliMapNA.KeySet())
        {
            Order_vod__c ordUpd= new Order_vod__c(Id = ids);
            ordUpd.PFE_DE_Current_YTD_Sales__c= oliMapNA.get(ids);
            odList.add(ordUpd);    
            system.debug('oLI_mAP_NA_cURRENT_iN_fOR'+oliMapNA);
        }

        update odList;

        //Previous year Order - Order Line Item 
        for(Order_Line_vod__c OrdLineItemPrev : [SELECT Id
                                                        , Name
                                                        , CurrencyIsoCode
                                                        , Order_vod__c
                                                        , PFE_Order_Status__c
                                                        , Net_Amount_vod__c
                                                        , PFE_Status__c 
                                                    FROM Order_Line_vod__c  
                                                    WHERE Order_vod__c  IN :ordIdPrev 
                                                        AND PFE_Status__c IN :olStatus])
        {
            if(!oliMapNAPrev.ContainsKey(OrdLineItemPrev.Order_vod__c))
            {
                oliMapNAPrev.Put(OrdLineItemPrev.Order_vod__c,OrdLineItemPrev.Net_Amount_vod__c); //If the Order Id does not exist then add it to Map
                system.debug('oLI_mAP_NA_lAST_iF'+oliMapNAPrev);
            }
            else 
            {
                if (OrdLineItemPrev.Net_Amount_vod__c > 0){
                    Sum2 = oliMapNAPrev.get(OrdLineItemPrev.Order_vod__c) + OrdLineItemPrev.Net_Amount_vod__c;
                    oliMapNAPrev.Put(OrdLineItemPrev.Order_vod__c,Sum2);
                    system.debug('oLI_mAP_NA_lAST_eLSE'+oliMapNAPrev);
                }
            }
        }

        for(Id ids: oliMapNAPrev.KeySet())
        {
            Order_vod__c ordUpdPrev= new Order_vod__c(Id = ids);
            ordUpdPrev.PFE_DE_Last_YTD_Sales__c= oliMapNAPrev.get(ids);
            odListPrev.add(ordUpdPrev);    
            system.debug('oLI_mAP_NA_lAST_in_fOR'+oliMapNAPrev);
        }
        update odListPrev;
    }
}
  • When you say 'not working at all', what do you mean. Are you getting any errors? Is it running ok, just not making the updates? – Chris Duncombe Dec 10 '13 at 18:35
  • it is running..bt is not updating i.e not calculating the currency field on parent object. – user4644 Dec 10 '13 at 18:44
  • Hmmm and when you say works for small amounts but not for big amounts, how many is small and how many is big. i.e it works for 500, but doesnt work for 5,000 – Chris Duncombe Dec 10 '13 at 18:46
  • it works for < 100 and does not works for > 100... – user4644 Dec 10 '13 at 19:21
  • 1
    How's your test code? Have you simulated/verified trying to update a lot of records (>1000)? The test shall set you free – Scott Pelak Dec 10 '13 at 19:55
1

Since I'm not intimately familiar with your code and org, the best I can do is offer a few suggestions. The first would be wherever possible to combine any of your queries to where you have one large query with subqueries that you can sort on. That should reduce your DML and hopefully make your queries more efficient. For example, I have to wonder if this query couldn't somehow be combined with the one above it or rewritten:

  for (Order_vod__c ord : [SELECT id
                           , Net_Amount_vod__c
                           , Status_vod__c
                            FROM Order_vod__c 
                            WHERE Id IN :ordrId
                            AND PFE_Order_Status__c IN :oOStatus ] 
  // perhaps add the following bind condition: AND Status_vod__c =: 'Submitted_vod' ?
    {

By combining them you could directly get your order Id's without first collecting all of your line items. It seems as though there 'ought' to be a way to combine the two queries and if you can also do what I've put in the comments, you directly have your list. To do this, you may need to bulk first, then run your queries rather than using SOQL for loops (which may be at the root of your issue). Again, not familiar enough with what you're doing.

    //Current year Order - Order Line Item 
    for(Order_Line_vod__c OrdLineItem : [SELECT Id
                                                , Name
                                                , CurrencyIsoCode
                                                , Order_vod__c
                                                , PFE_Order_Status__c
                                                , Net_Amount_vod__c
                                                , PFE_Status__c 
                                        FROM Order_Line_vod__c  
                                        WHERE Order_vod__c  IN :ordId 
                                            AND PFE_Status__c IN :olStatus])
    {
        if(!oliMapNA.ContainsKey(OrdLineItem.Order_vod__c))
        {
            oliMapNA.Put(OrdLineItem.Order_vod__c,OrdLineItem.Net_Amount_vod__c); //If the Order Id does not exist then add it to Map
            system.debug('oLI_mAP_NA_cURRENT_iF'+oliMapNA);
        }
        else 
        {
            if (OrdLineItem.Net_Amount_vod__c > 0){
                Sum1 = oliMapNA.get(OrdLineItem.Order_vod__c) + OrdLineItem.Net_Amount_vod__c;
                oliMapNA.Put(OrdLineItem.Order_vod__c,Sum1);
                system.debug('oLI_mAP_NA_cURRENT_eLSE'+oliMapNA);
                system.debug('(((((((((('+Sum1 ); 
            }
        }
    }

Why not try just doing an aggregate query here to get your roll-up numbers and save yourself the trouble of the the if/else statements? You could do the same for the previous year as well.

Those are the only suggestions I'd have for you to consider trying that might make the code run a bit more smoothly with the bulk data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy