Hi all

I have a Stored Procedure as below. I know it is my handling of the calculated field bQtyOut which is wrong and that is where the error in the returned data set is. Can someone kindly point out to me where I am going wrong here?

This is the line executed
SELECT * FROM FIFOVALUATIONTRANSACTIONS(2759, 36)


This is the returned set
ITPBID ITPBNO TTPBDT ISPBIID TBQTY TBRATE TBQTYOUT
732 468 00:00:00.000 2556 1.000000 35.630000 0.000000
712 445 00:00:00.000 2420 5.000000 35.630000 1.000000
708 412 00:00:00.000 2374 30.000000 35.630000 6.000000

In the last row TBQTY should have been 29 (as that is the max. qty of that purchasebill) instead of 30, so the set should have been as below, also note the expected tbQtyOut values
ITPBID ITPBNO TTPBDT ISPBIID TBQTY TBRATE TBQTYOUT
732 468 00:00:00.000 2556 1.000000 35.630000 1.000000
712 445 00:00:00.000 2420 5.000000 35.630000 6.000000
708 412 00:00:00.000 2374 29.000000 35.630000 35.000000
705 353 00:00:00.000 2339 1.000000 35.630000 36.000000

Please advise

Thanks and regards
Bhavbhuti


SET TERM ^ ;
ALTER PROCEDURE FIFOVALUATIONTRANSACTIONS (
TIITEMID Integer,
TBCURRSTOCK Double precision )
RETURNS (
ITPBID Integer,
ITPBNO Integer,
TTPBDT Time,
ISPBIID Integer,
TBQTY Double precision,
TBRATE Double precision,
TBQTYOUT Double precision )
AS
--DECLARE VARIABLE variable_name < datatype>;
BEGIN
FOR SELECT itPBID
, itPBNo
, ttPBDt
, isPBIID
, bQty
, bRate
, bQtyOut
FROM (SELECT itPBID
, itPBNo
, ttPBDt
, isPBIID
, IIF(bQty + bQtyOut >= :tbCurrStock /*current stock */
, (:tbCurrStock /*current stock */ - bQtyOut)
, bQty) as bQty
, bRate
, bQtyOut
FROM (SELECT tPurchaseBill.iID AS itPBID
, tPurchaseBill.iNo AS itPBNo
, tPurchaseBill.tDt AS ttPBDt
, sPurchaseBillItem.iID AS isPBIID
, 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 = :tiItemID /* item id */))
, 0) AS bQtyOut
FROM tPurchaseBill
JOIN sPurchaseBillItem
ON sPurchaseBillItem.iPID = tPurchaseBill.iID
AND sPurchaseBillItem.iItemID = :tiItemID /* item id */
ORDER BY tPurchaseBill.tDt DESCENDING, tPurchaseBill.cBk DESCENDING, tPurchaseBill.iNo DESCENDING) AS PurchaseBillsIntermediate
WHERE (bQtyOut <= :tbCurrStock /*current stock */)) AS PurchaseBillsFIFO
INTO :itPBID, :itPBNo, :ttPBDt, :isPBIID, :tbQty, :tbRate, :tbQtyOut

DO SUSPEND;
END^
SET TERM ; ^

GRANT EXECUTE
ON PROCEDURE FIFOVALUATIONTRANSACTIONS TO SYSDBA;