select prod_id,company_price, prod_name, friendly_name, prod_cat_name, products.cat_id, prod_maincat_name,brand_name,
sku, image_path, prod_shipping_cost, products.date_added,
price, avail_status , products.sort_order , prod_desc,
upper(products.is_active) is_active,
upper(products.is_featured) is_featured,
pc.prod_cat_friendly_name , prod_maincat_friendly_name,
product_rating, product_rating_count,
products.quantity_in_stock, is_offer, offer_discount_percent, offer_start_date, offer_end_date,
if ( products.offer_start_date <= now() , if( products.offer_end_date >= now() , 1, 0 ) , 0 ) is_valid_offer,
is_offer_active, get_prod_mrp( prod_id ) prod_mrp
from product_categories pc, product_maincategories pmc , products
left join product_brands pb on products.brand_id = pb.brand_id
where products.cat_id = pc.prod_cat_id and avail_status = 1
and upper(products.is_active) = 'Y'
and pc.pc_maincat_id = pmc.prod_maincat_id
and pc.is_active = 'Y'
and pmc.prod_maincat_is_active = 'Y'
AND ( pc.prod_cat_friendly_name = 'plate'
or pc.prod_cat_name = 'plate' ) AND ( pc.prod_cat_friendly_name = 'plate'
or pc.prod_cat_name = 'plate' ) and pmc.prod_maincat_friendly_name = 'crockery' order by sort_order
