Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Recursive Proc.


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 0
Melissa Torn

Posts: 143
Registered: 4/30/09
Recursive Proc.  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 7, 2017 4:00 AM
My Items Table
ITEMNO..ITEMTYPE...STOCK
AAA.........Main Assy........0
AAA-1......Sub Assy.........2
BBB.........Part..................0
CCC........Part..................0

My BillOfMaterial (BOM) Table
ITEMNO..CHILDITEMNO...USAGEQTY
AAA.........AAA-1......................1
AAA........BBB..........................1
AAA-1.....CCC.........................1

I got 10 pieces AAA order and
before production ─░ want to check my stock levels
so that if needed i also will order to my vendors.
I want o get below result
CHILDITEMNO...STOCK...USAGEQTY...NEEDQTY
AAA-1.....................2.................1...............8
CCC........................0.................1...............8 (we have 2 pieces AAA-1 subassy in stock)
BBB........................0.................1..............10

Here below my Recursive Proc.

ALTER PROCEDURE "BOMS"
(
"ITMNO" VARCHAR(20)
)
RETURNS
(
"ITEMNO" VARCHAR(20),
"CHILDITEMNO" VARCHAR(20),
"QTY" NUMERIC(15, 2),
"ITEMTYPE" VARCHAR(12),
"ITEMNAME" VARCHAR(40),
"UOM" VARCHAR(5),
"STOCK" NUMERIC(18, 2),
"FIRSTPRODUCTION" VARCHAR(5)
)
AS
BEGIN
FOR SELECT B.ITEMNO,B.CHILDITEMNO,B.NETQTY QTY, I.ITEMTYPE,
I.ITEMNAME,U.UOM,I.STOCK,B.FIRSTPRODUCTION
FROM BOM B
JOIN ITEMS I ON I.ITEMNO=B.CHILDITEMNO
JOIN UOM U ON U.RNO=I.UOMRNO
WHERE B.ITEMNO= :"ITMNO"
ORDER BY B.CHILDITEMNO
INTO :"ITEMNO",:"CHILDITEMNO", :"QTY",:"ITEMTYPE",
:"ITEMNAME",UOM,:"STOCK",:"FIRSTPRODUCTION"
DO
BEGIN
SUSPEND;
FOR SELECT B1.ITEMNO,B1.CHILDITEMNO,B1.QTY,B1.ITEMTYPE,
B1.ITEMNAME,B1.UOM,B1.STOCK,B1.FIRSTPRODUCTION
FROM BOMS(:"CHILDITEMNO") B1
JOIN ITEMS I ON I.ITEMNO=B1.CHILDITEMNO
INTO :"ITEMNO",:"CHILDITEMNO", :"QTY",:"ITEMTYPE",
:"ITEMNAME",:"UOM",:"STOCK",:"FIRSTPRODUCTION"
DO
BEGIN
SUSPEND;
END
END
END;

Thank you
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02