with cte as(
SELECT g4.Productid,g4.Product,g4.SaleUnit ,g4.Packing,g4.ConvFact,g4.PTR,g4.MRP, g4.PRate,g4.PTR1,g4.PTR2,g4.Location,0 As ManufId,g7.PatentId,g7.Patent,g7.PatentCd,g15.TaxId,g15.TaxName,g15.TaxType,g15.TaxRate,g15.TxOMRP,''::text As Manufacture,0 AS PurchQty, 0 AS SRetQty,0 AS PerInQty, 0 AS SaleQty,
0 AS StockInQty,0 AS StockOutQty, 0 AS SaleAmt, 0 AS DamageQty, 0 AS PRetQty, 0 AS PerOutQty,g4.LandCost,g5.PTR AS BatchPTR,g5.MRP AS BatchMRP,
g5.PRate AS BatchPRate,g4.StdPack,g5.BatchId,0 AS FreeSaleQty,0 AS ReplSaleQty,''::text As Manufacture
 ,CASE WHEN EXISTS (SELECT gtab10.Productid FROM gtab10 INNER JOIN gtab09 using(TranId) WHERE  gtab10.Productid = g4.Productid AND gtab10.BatchId =g5.BatchId AND gtab09.acyrid = 7 limit 1) THEN 1 ELSE 0 END AS StkEntered
,(SELECT SUM(gtab10.qty) FROM gtab10 INNER JOIN gtab09 using(TranId) WHERE gtab10.Productid = g4.Productid AND gtab10.BatchId = g5.BatchId AND gtab09.vrid = 6 AND gtab09.acyrid = 7) +(SELECT SUM(gtab10.qty) FROM gtab10  INNER JOIN gtab09 using(TranId)  WHERE gtab10.Productid = g4.Productid AND gtab10.BatchId = g5.BatchId AND gtab09.vrid in (10,11,23,42,35) AND gtab09.trdate < '2014-06-01' AND gtab09.acyrid = 7) AS OpeningInQty
,(SELECT SUM(gtab10.qty)FROM gtab10 INNER JOIN gtab09 using(TranId)  WHERE g4.Productid = gtab10.Productid AND gtab10.BatchId = g5.BatchId AND gtab09.vrid in( 12,32,33,44 ,45 ,46, 47 ,48 , 49 ,18 , 34 ,25,27 ,15,26,24 , 43 ,36) AND gtab09.trdate < '2014-06-01'  AND gtab09.acyrid = 7) AS OpeningOutQty
,(SELECT SUM(gtab10.qty) FROM gtab10 INNER JOIN gtab09 using(TranId ) WHERE g4.Productid = gtab10.Productid AND gtab10.BatchId = g5.BatchId AND gtab09.vrid in (12 ,32 ,33 ,44 ,45 ,46 ,47,48 ,49) AND (gtab09.trdate BETWEEN '2014-05-01' AND '2014-05-31')
) AS PrMthSaleQty
 ,(SELECT (SUM(gtab10.qty * gtab10.ptr) ) FROM gtab10 INNER JOIN gtab09 using(TranId) WHERE g4.Productid = gtab10.Productid AND gtab10.BatchId = g5.BatchId AND gtab09.vrid in( 12, 32 , 33 , 44 ,45 ,46 , 47 ,48 , 49) AND (FreeOrRpl = 0 OR FreeOrRpl = 2) AND (gtab09.trdate BETWEEN '2014-05-01' AND '2014-05-31')
 ) AS PrMthSaleAmt
,case when (g5.Fqty-g5.FIQty)>0 then (g5.Fqty-g5.FIQty) else 0 end as IssdFreeQty
,(date_part('day',  (Select Min(Expiry) From gtab05 WHERE  gtab05.Productid = g4.Productid And gtab05.Qty > gtab05.IQty)-'2014-06-20'::timestamp) )AS ExpDys FROM gtab04 g4 INNER JOIN  gtab15 g15 using(taxid)  INNER JOIN  gtab07 g7 using(Patentid) left join gtab05 g5 using(productid) WHERE (g4.Masked = False AND g4.Banned = false)  AND  g4.patentid in  (select patentid from gtab07) ORDER BY Patent, g4.Product
),cte1 as (
     SELECT AcYrId,VrId,TrDate,qty,FreeOrRpl,cte.BatchId, gtab10.Amt  FROM gtab09
     inner join gtab10 using (tranid) inner join cte using(BatchId)
     WHERE  gtab09.trdate BETWEEN Cast('2014-05-01' AS timestamp) AND Cast('2014-05-31' AS timestamp) AND gtab09.acyrid = 7
 ),update_cte as (
    UPDATE cte SET FreeSaleQty = (SELECT SUM(QTY) FROM cte1 WHERE FreeOrRpl IN (1,3,4) AND vrid In (12,32,33,44,45,46,47,48,49 ) AND cte1.BatchId=
    cte.BatchId)
)
   select * from cte