WareHouse Parts FIFO Pick

[henry...@hotmail.com]

Good Day,

Location Table

LOCNO..ITEMNO..LOTNO...QTY

A1...........X01........24-02....10

B2...........X01........24-01..... 6

C6...........X01........24-03....20

(FIFO = The Lowest LOTNO)

If i sell  <= 6 pieces of X01 i should pick the items from B2 Location(FIFO)

Requested Query Result should be

LOCNO..ITEMNO..LOTNO...QTY

B2...........X01........24-01..... 6

If i sell  10 pieces of X01 then

      first i should pick 6 pieces from B2 Location(FIFO)

      second i should pick 4 pieces from A1 Location(FIFO)

Requested Query Result should be

LOCNO..ITEMNO..LOTNO...QTY

B2...........X01........24-01......6

A1...........X01........24-02..... 4

If i sell  20 pieces of X01 part then

      first i should pick 6 pieces from B2 Location(FIFO)

      second i should pick 10 pieces from A1 Location(FIFO)

      third i should pick 4 pieces from C6 Location(FIFO)

Requested Query Result should be

LOCNO..ITEMNO..LOTNO...QTY

B2...........X01........24-01...... 6

A1...........X01........24-02.....10

C6...........X01........24-03...... 4

Could someone please help ?

Thank You

[henry...@hotmail.com]

I'm using FB3

[liviuslivius]

Hi

But what help you need? 

Simly write stored procedure which go record by record. Select records with lock and go until provided quantity reached. Thats it.

Regards,

Karol Bieniaszewski

-------- Oryginalna wiadomość --------

Od: "henry...@hotmail.com" <henry...@hotmail.com>

Data: 30.06.2024 05:41 (GMT+01:00)

Do: firebird-support <firebird...@googlegroups.com>

Temat: [firebird-support] WareHouse Parts FIFO Pick

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/9b64845f-c7df-40d2-aca3-5d6a71be8cb8n%40googlegroups.com.

[henry...@hotmail.com]

Good Day

I prepared below S.Proc

i get unknown isc error msg.

so i dont know if s.proc result is correct or wrong

I also updated my first question below for better and simpler understanding

Location Table

LOCNO..ITEMNO..QTY
A1...........X01.........10

B2...........X01.......... 6

C6...........X01.........22

If below S.Proc  PQTY value = 10 then the result should be

LOCNO...QTY
A1.............10

If below S.Proc  PQTY value = 20 then the result should be

LOCNO...QTY
A1............10

B2............. 6

C6............. 4  (even though C6 Qty = 22, i just need 4 to complete PQTY value which is 20)

Thank you

SET TERM ^ ;

CREATE PROCEDURE  TEST (ITMNO VARCHAR(20), PQTY NUMERIC(12,2))
RETURNS
(
 LOCNO VARCHAR(20),
 QTY NUMERIC(12,2)
)
AS
BEGIN  
    WHILE (LD.QTY <= :PQTY) DO
    BEGIN
        FOR SELECT LD.LOCNO, QTY
        FROM LOCDETAIL LD
       JOIN LOCATION L ON LD.RNO=L.RNO
       JOIN ITEMS IT ON IT.ITEMNO=LD.ITEMNO
       WHERE LD.ITEMNO= :ITMNO

       GROUP BY LD.LOCNO 
       INTO :LOCNO, :QTY
 
       DO
       SUSPEND;
       QTY= :PQTY - :QTY;

   END
END

^
SET TERM ; ^

[Mark Rotteveel]

On 07/07/2024 17:49, henry...@hotmail.com wrote:
> I prepared below S.Proc
> i get unknown isc error msg.

What is the exact error, because if it reports an unknown error, it
usually includes the unknown error code. That error BTW means that your
fbclient.dll is either outdated or reads a firebird.msg that is outdated
(compared to the Firebird server used)

Mark
--
Mark Rotteveel

[Svein Erling Tysvær]

Unknown ISC error message often indicates that you have outdated fbclient or firebird.msg and you ought to fix this to get sensible error messages. However, your stored procedure contains many errors that are easy to spot. You are referring to LD.QTY outside of scope, you cannot possibly use LD outside of your FOR SELECT. The only thing you do within your loop is to call SUSPEND and then you modify the QTY with what could be an infinite loop if it hadn't been for 20 being less than 22. You never modify PQTY and you select QTY without it neither being grouped by or being a summary field. If you just fixed the syntax errors of your SP, I would expect the result to be 10, 6, 22 and not 10, 6, 4.

You seriously need to rethink your procedure. Below is what I think you may be looking for, although with the number of errors in your original procedure it wouldn't surprise me if there are more (e.g. are LOCATION.RNO and ITEMS.ITEMNO unique?).

CREATE PROCEDURE  TEST (ITMNO VARCHAR(20), PQTY NUMERIC(12,2))

RETURNS
(
 LOCNO VARCHAR(20),
 QTY NUMERIC(12,2)
)
AS
BEGIN

  FOR SELECT LD.LOCNO, SUM(LD.QTY)

  FROM LOCDETAIL LD
  JOIN LOCATION L ON LD.RNO=L.RNO
  JOIN ITEMS IT ON IT.ITEMNO=LD.ITEMNO
  WHERE LD.ITEMNO= :ITMNO
  GROUP BY LD.LOCNO
  INTO :LOCNO, :QTY DO

  BEGIN
    IF (PQTY < QTY) THEN
      QTY = PQTY;
    PQTY = PQTY - QTY;
    SUSPEND;
    IF (PQTY <= 0) THEN LEAVE;
  END
END
^

HTH,

Set

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/9cb4280c-d16b-4f87-bcd0-27fe4b842a1cn%40googlegroups.com.

[henry...@hotmail.com]

Thank you so much Set,  your code work perfectly