Hi,

  I wrote a trigger to insert opportunity and opportunityline items.  There are too many SOQL queries inside for loop how to modify please suggest. 

Hi,

  I wrote a trigger to insert opportunity and opportunityline items.  There are too many SOQL queries inside for loop how to modify please suggest.

trigger Temp_Asset2_Opp on Temp_Assets__c (after insert )
{
    List<OpportunityLineItem> OppLineItems = new List<OpportunityLineItem>();
  
    List<Temp_Assets__c> CurrentAsset = [Select Id, Name, Serial_Number__c, AccountId__c, Product__c, Service_Start_Date__c,
                                              Service_End_Date__c ,Install_Date__c ,Reseller__c,Distributor__c ,
                                              Incumbent_Reseller__c,Education__c,Expiry_Date__c,Existing_Opportunity__c,
                                              New_Opportunity__c,Expiry_Term__c,Bundle_Support__c, X5_Year_SKU_Code__c, X3_Year_SKU_Code__c,
                                              X1_Year_SKU_Code__c, Monthly_SKU_Code__c, X5_year_SKU__c ,X3_year_SKU__c, X1_year_SKU__c,
                                              Total_in_Months__c, Support_Only_5_Year_SKU__c, Support_Only_3_Year_SKU__c, Support_Only_1_Year_SKU__c,
                                              Support_Only_Monthly_SKU__c
                                         From Temp_Assets__c
                                         Where CreatedById = :userinfo.getUserId()];
  
    Pricebook2 prBook= [select id from Pricebook2 where Name=: 'NAM Price Book'];

    for ( Temp_Assets__c TA : CurrentAsset )
    {
         // If New Opportunity is Created
         if ( TA.Existing_Opportunity__c == NULL && TA.New_Opportunity__c != NULL )  
          {
             // Insert Opportunity with all mandatory fields
             Opportunity Opp = new Opportunity();
             Opp.Name      = TA.New_Opportunity__c;           
             Opp.Type      = 'Existing Customer';
             Opp.AccountId = TA.AccountId__c;
             Opp.CloseDate = TA.Expiry_Date__c;
             Opp.Government_Contract__c = 'None';
             Opp.Renewal_Opportunity__c = 'Yes';
             Opp.StageName = 'Renewal';
             Opp.Lost_Reason__c = 'Other';
             Opp.Primary_Competitor__c = 'No Competitor';
             Opp.ForecastCategoryName = 'Pipeline';
             Opp.LeadSource = 'Renewal';
             Opp.Primary_Reseller__c    = TA.Reseller__c;
             Opp.Primary_Distributor__c = TA.Distributor__c;
             Opp.Renewal_Incumbant_Reseller__c = TA.Incumbent_Reseller__c;
             Opp.Renewal_K_12__c   =  TA.Education__c;
           
             Insert Opp;
           //Check if the Renewals need 5 years renewals
             if (TA.X5_year_SKU__c > 0 && (TA.Support_Only_5_Year_SKU__c!=null || TA.X5_Year_SKU_Code__c!=null)) {
 
                //Insert into Opportunity Lines  
                OpportunityLineItem OppL_5yr = new OpportunityLineItem();
                    OppL_5yr.OpportunityId = Opp.Id;
                    //check if the Renew is for Bundle Support or Support only
                    if (TA.Bundle_Support__c == true && TA.X5_Year_SKU_Code__c!= null) {
                        //Retrieve the priceBookEntryId for Bundle support 5 years Renewals SKU Code
                        OppL_5yr.PricebookEntryId = [Select id from PriceBookEntry where product2.Name =:TA.X5_Year_SKU_Code__c AND priceBook2Id=:prBook.id].Id;
                    }
                    else if (TA.Bundle_Support__c != true && TA.Support_Only_5_Year_SKU__c!= null) {
                        //Retrieve the priceBookEntryId for CO or SO support 5 years Renewals SKU Code
                        OppL_5yr.PricebookEntryId = [Select id from PriceBookEntry where product2.Name =:TA.Support_Only_5_Year_SKU__c AND priceBook2Id=:prBook.id].Id;
                    }                 
                    //OppL_5yr.PricebookEntryId = prBookEntry.Id;
                    OppL_5yr.Quantity = TA.X5_year_SKU__c;              
                
                //Adding to the list
                OppLineItems.add(OppL_5yr);
               
                 //Insert OppL_5yr; 
              }     
            if (TA.X3_year_SKU__c > 0 && (TA.Support_Only_3_Year_SKU__c!=null || TA.X3_Year_SKU_Code__c!=null)) {
              
                //Insert into Opportunity Lines
                OpportunityLineItem OppL_3yr = new OpportunityLineItem();
                OppL_3yr.OpportunityId = Opp.Id;
                //check if the Renew is for Bundle Support or Support only
                if (TA.Bundle_Support__c == true && TA.X3_Year_SKU_Code__c!= null) {
                    //Retrieve the priceBookEntryId for Bundle support 3 years Renewals SKU Code
                    OppL_3yr.PricebookEntryId = [Select id from PriceBookEntry where product2.Name =:TA.X3_Year_SKU_Code__c AND priceBook2Id=:prBook.id].Id;
                }
                else if (TA.Bundle_Support__c != true && TA.Support_Only_3_Year_SKU__c!= null) {
                    //Retrieve the priceBookEntryId for CO or SO support 3 years Renewals SKU Code
                    OppL_3yr.PricebookEntryId = [Select id from PriceBookEntry where product2.Name =:TA.Support_Only_3_Year_SKU__c AND priceBook2Id=:prBook.id].Id;
                }
                //OppL_3yr.PricebookEntryId = prBookEntry.Id;
                OppL_3yr.Quantity=TA.X3_year_SKU__c;
                  
                
                OppLineItems.add(OppL_3yr);
                //Insert OppL_3yr; 
            }
            if (TA.X1_year_SKU__c > 0 && (TA.Support_Only_1_Year_SKU__c!=null || TA.X1_Year_SKU_Code__c!=null)) {

                //Insert into Opportunity Lines
                OpportunityLineItem OppL_1yr = new OpportunityLineItem();
                OppL_1yr.OpportunityId = Opp.Id;
                //check if the Renew is for Bundle Support or Support only
                if (TA.Bundle_Support__c == true && TA.X1_Year_SKU_Code__c!= null) {
                    //Retrieve the priceBookEntryId for Bundle support 1 year Renewals SKU Code
                    OppL_1yr.PricebookEntryId = [Select id from PriceBookEntry where product2.Name =:TA.X1_Year_SKU_Code__c AND priceBook2Id=:prBook.id].Id;
                }
                else if (TA.Bundle_Support__c != true && TA.Support_Only_1_Year_SKU__c!= null) {
                    //Retrieve the priceBookEntryId for CO or SO support 1 years Renewals SKU Code
                    OppL_1yr.PricebookEntryId = [Select id from PriceBookEntry where product2.Name =:TA.Support_Only_1_Year_SKU__c AND priceBook2Id=:prBook.id].Id;
                }
                //OppL_1yr.PricebookEntryId = prBookEntry.Id;
                OppL_1yr.Quantity=TA.X1_year_SKU__c;
              
              
                OppLineItems.add(OppL_1yr);
                //Insert OppL_1yr; 
            }
            if (TA.Total_in_Months__c > 0 && (TA.Support_Only_Monthly_SKU__c!=null || TA.Monthly_SKU_Code__c!=null)) {

                //Insert into Opportunity Lines
                OpportunityLineItem OppL_Months = new OpportunityLineItem();
                OppL_Months.OpportunityId = Opp.Id;
                //check if the Renew is for Bundle Support or Support only
                if (TA.Bundle_Support__c == true && TA.Monthly_SKU_Code__c!= null) {
                    //Retrieve the priceBookEntryId for Bundle support Monthly Renewals SKU Code
                    OppL_Months.PricebookEntryId = [Select id from PriceBookEntry where product2.Name =:TA.Monthly_SKU_Code__c AND priceBook2Id=:prBook.id].Id;
                }
                else if (TA.Bundle_Support__c != true && TA.Support_Only_Monthly_SKU__c!= null) {
                    //Retrieve the priceBookEntryId for CO or SO support Monthly Renewals SKU Code
                    OppL_Months.PricebookEntryId = [Select id from PriceBookEntry where product2.Name =:TA.Support_Only_Monthly_SKU__c AND priceBook2Id=:prBook.id].Id;
                }
                //OppL_Months.PricebookEntryId = prBookEntry.Id;
                OppL_Months.Quantity=TA.Total_in_Months__c;        
              
                OppLineItems.add(OppL_Months);
                //Insert OppL_Months; 
            }          
        }
          
       
  List<Temp_Assets__c> TempAsset = [SELECT Id From Temp_Assets__c Where CreatedById = :userinfo.getUserId()];
  delete TempAsset ;

}

Thanks
Sudhir