SELECT  A.id AS medicineid,
        A.default_code AS productcode,
        REPLACE(A.name->>'en_US', '''', ' ') AS productname,
        A.l10n_in_hsn_code AS hsncode,
        A.mrp AS mrp,
	    pp.barcode AS productbarcode,
        B.name AS medicinegroup,
        C.name AS medicinecategory,
        F.Name->>'en_US' AS purchaseuom,
        G.Name->>'en_US' AS salesuom,
        coalesce(A.purchase_ratio,1) AS purchaseratio,
        coalesce(A.sals_ratio,1) AS salesRatio,
        A.active,
        P.amount AS taxpercentage, P.name->>'en_US' AS taxname,
        coalesce(A.manufactureby,'')AS manufactureby,
        E.name AS schedule,
        A.generic_name AS genericname,
        coalesce(A.is_scheme_eligible,false) AS schemeeligible,
        coalesce(product_discount,0) AS mm_disc,                   
		coalesce((CASE WHEN A.parent = 'grocery'
                THEN (select mrp 
                 from purchase_order_line 
                 where product_id = (select id from product_product where product_tmpl_id = A.id) 
                        and state = 'purchase' 
                 order by id desc limit 1)
                ELSE coalesce((CASE WHEN A.parent = 'grocery'
                THEN (select mrp 
                 from stock_lot 
                 where product_id = (select id from product_product where product_tmpl_id = A.id)
                 order by id desc limit 1)
                ELSE 0
        END),0)
        END),0)AS productmrp,   
		coalesce((CASE WHEN A.parent = 'grocery'
                THEN (select mrp 
                 from stock_lot 
                 where product_id = (select id from product_product where product_tmpl_id = A.id)
                 order by id desc limit 1)
                ELSE 0
        END),0)AS open_mrp,
        coalesce((CASE WHEN A.parent = 'grocery'
                THEN (select price_unit 
                 from purchase_order_line 
                 where product_id = (select id from product_product where product_tmpl_id = A.id) 
                        and state = 'purchase' 
                 order by id desc limit 1)
                ELSE 0 END
        ),0)AS salesrate
FROM    product_template A 
	    LEFT JOIN product_product  pp 
ON      A.id=pp.product_tmpl_id
        LEFT JOIN public.division  B 
ON      B.id=A.division
        LEFT JOIN product_category C 
ON      C.id=A.categ_id   
        LEFT JOIN product_schedule E 
ON      E.id=A.schedule_id 
        LEFT JOIN UOM_uom F
ON      F.Id=uom_po_id
         LEFT JOIN UOM_uom G
ON      G.Id=uom_id
        LEFT JOIN product_taxes_rel H
ON       H.prod_id=A.id   
        LEFT JOIN account_tax P
ON      P.id=H.tax_id
WHERE 	A.default_code!=''	
	AND A.parent = 'grocery'	
	AND P.amount IS NOT NULL
    AND A.active = 't'
ORDER BY A.id DESC ;