Opportunity validation using DateTime in apex code

Issue: We have the requirement add validation on Opportunity where Stage is above 2 and Maintain ace attached file ‘Yes’ than there should be Support Product in product list.

Description:

Requirement is if Opportunity having Stage is 2 and above Maintain ace attachment ‘Yes’ than there should be Support Product in related list of Product. Otherwise user get error message like first add the Product in product list.

Solution:

As above requirement we had written validation code in before update trigger. We have also tried to created validation rule however we can not access product variable in validation rule because of much level relationship.

We have faced another issue of Data clean up because given below condition Oppty Stage and Maintain ace Attachment. So we have added one more condition this validation would be only when Oppty created date is 15-June-2011.

SOQL:

Select id,name from Opportunity where StageName!=’01 – Pre-Qualified’ and Maintenance_Attach__c = ‘Yes’ and Product_of_Interest__c!=’Support Product’ and CreatedDate>=2011-06-15T00:00:00Z

Trigger Validation Code:

trigger OpporunityBeforeUpdate on Opportunity (before update) {

for(Integer i=0;i<Trigger.new.size();i++){

Datetime myDate =datetime.valueOf(‘2011-06-15 00:00:00’);

if(Trigger.new[i].StageName!=’01 – Pre-Qualified’ && Trigger.new[i].Maintenance_Attach__c == ‘Yes’ && Trigger.new[i].CreatedDate>=myDate)

{

List products = [Select p.Monthly_Rate__c, p.Months__c, p.Opportunity__c, p.Product_Family__c from Products__c p where Opportunity__c=:Trigger.new[0].Id and Product_Family__c=:’Support Product’];

if(products.size() == 0){

trigger.new[i].addError(‘First add Support Product in related list of Product.’);

}