How can I tell if a product has an entry for the standard pricebook?

Question

I have VF page from where i i trying to add products to a pricebook. IF the standard pricebook of the product not active then i am getting an error 'DML exception: Standard price not defined'. So how can i chank the standard price of a product is active or not OR how can i get only products whose standard price is active. Please guide me.

Answer 1

Anu --

1. In order to have a price for a product on a custom pricebook, there must be a price for that Product on the standard pricebook for that currency
2. This relationship is defined on a PricebookEntry SObject which is a junction between Pricebook2 and Product2. PricebookEntry defines the product's list price for a given currency on that pricebook. That is, for a given product and currency, there will be 1+ PricebookEntries - one for the standard pricebook and additional ones for each custom pricebook containing that product.

3. Your controller will need to a) locate the ID of the standard Pricebook2 (there is only one) and then b) query the PricebookEntry table using as search filters : standard pricebook ID, Product2 ID, and currencyIsoCode (note: a and b could be combined in one query)

   select id from PricebookEntry where 
     product2Id = 'your product2 id' and
     currencyisoCode = 'your currency' and
     isActive = true and 
     pricebook2Id IN (select id from Pricebook2 
                       where isStandard = true)
   

   if this finds a record, then there is an active standard price for that currency/product

A useful reference is the SFDC Objects doc - http://www.salesforce.com/us/developer/docs/object_reference/index.htm

With this as background, you should be able to adjust your controller to only display products that exist on the standard pricebook, or show better error messages, or whatever your application needs

Answer 2

Here's some code that will lay it out correctly:

Account a = new Account();
    a.name = 'test Account';
    insert a;

Pricebook2 pb = [SELECT Id, IsActive FROM PriceBook2 WHERE IsStandard=True];
    Product2 p2 = new Product2();
    p2.name = 'test p2';
    insert p2;

PricebookEntry pbe = new PricebookEntry();
    pbe.UnitPrice = 1;
    pbe.Pricebook2Id = pb.id;
    pbe.Product2Id = p2.id;
    pbe.IsActive = true;
    insert pbe;

Opportunity o = new Opportunity();
    o.AccountId = a.Id;
    o.Name = 'test';
    o.StageName = 'Prospecting';
    o.CloseDate = date.today();
    insert o;

OpportunityLineItem ol = new OpportunityLineItem();
    ol.OpportunityId = o.Id;
    ol.Quantity = 1;
    ol.UnitPrice = 2.00;
    ol.PricebookEntryId = pbe.Id;
    ol.Converted_to_Asset__c = false;
    ol.Description = 'testing testing';
    insert ol;

This is just a test class that lays everything out the way it needs to to successfully perform the insert and association of Opportunity Line Items.

Thanks!

Brad