Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement