Re: [firebird-support] Re: First-In-First-Out (FIFO) stock valuation

 

Hi all

Sorry for resurrecting this very old thread but I have come across a situation (hopefully Jose would be able to answer).  I have found a bug in the bQty column it was not able to reduce itself to the current stock, eg. 3, and still it returns two row with a qty of 3+2 = 5 qty is valued which is wrong

SELECT qty, bRate, (qty * bRate) AS bValuation
FROM (SELECT IIF(bQty + qtyOut >= 3 /*current stock */
, (3 /*current stock */ - qtyOut)
, bQty) as qty
, bRate
FROM (SELECT sPurchaseBillItem.bQty
, sPurchaseBillItem.bRate
, COALESCE((SELECT SUM(PurchaseBillItemInner.bQty)
FROM sPurchaseBillItem AS
PurchaseBillItemInner
JOIN tPurchaseBill AS
PurchaseBillInner
ON
PurchaseBillInner.iID = PurchaseBillItemInner.iPID
WHERE (PurchaseBillInner.tDt >
tPurchaseBill.tDt AND PurchaseBillItemInner.iItemID = 342 /* item id */))
, 0) as qtyOut
FROM tPurchaseBill
JOIN sPurchaseBillItem
ON sPurchaseBillItem.iPID = tPurchaseBill.iID
AND sPurchaseBillItem.iItemID = 342 /*item id */) AS PurchaseBillsIntermediate
WHERE (qtyOut <= 3 /*current stock */)) AS PurchaseBillsFIFO

returns
3.000000    67.190000    201.570000
2.000000    67.190000    134.380000

the expected would have been
1.000000    67.190000    67.190000
2.000000    67.190000    134.380000

the original bqty in the top row is 11 but now only 1 of it remains, yet the above.  The only reason this might be happening is that both the rows belongs to the same tPurchaseBill parent record, because the same purchase bill had stock coming in from different medicine batches, thus the user needed to enter the same item in the same purchase bill twice but with different qty and batch nos.

Please advise.

Thanks and regards
Bhavbhuti

__._,_.___

---

Posted by: venussoftop@gmail.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (8)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? The Yahoo Mail app is fast, beautiful and intuitive. Try it today!

---

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit Your Group

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___