James: you asked to see my final code. The client told me that 3 levels
was plenty for now.
Table structure (CustPartUpperLevel)
CustPartUpper CustPart ULMultiplier
100 200 1
100 201 2
201 300 2
What the multiplier is: If we sold 50 of part 100, then part 200 is
reduced
by 50, part 201 would be reduced by 100 because of the multiplier 2.
And then part 300 would be reduced by 200 because of 2 * 2.
I left out a bunch of processing code and one of the cursors, but here's
the
shell of how I did the cursor. "fCustPart" is the part sold on the
invoice,
which would be 100 in the above example.
SELECT qtyshipx INTO vCompShipQty FROM invoice WHERE invoice = .vInv
SET VAR vCompPart TEXT = NULL, vCompPart2 TEXT = NULL
DROP CURSOR cCInv
DROP CURSOR cCInv2
DECLARE cCInv CURSOR FOR SELECT custpart, ulmultiplier FROM
CustPartUpperLevel +
WHERE CustPartUpper = .fCustPart
DECLARE cCInv2 CURSOR FOR SELECT custpart, ulmultiplier FROM
CustPartUpperLevel +
WHERE CustPartUpper = .vCompPart
OPEN cCInv
WHILE 1 = 1 THEN
FETCH cCInv INTO vCompPart, vMultiply
IF SQLCODE = 100 THEN
BREAK
ENDIF
SET VAR vCompInv = (.vCompShipQty * .vMultiply)
INSERT INTO InvTransacts (custpart, invout, invdate ) +
VALUES .vCompPart, .vCompInv, .#DATE
OPEN cCInv2 RESET
WHILE 1 = 1 THEN
FETCH cCInv2 INTO vCompPart2, vMultiply2
IF SQLCODE = 100 THEN
BREAK
ENDIF
SET VAR vCompInv2 = (.vCompShipQty * (.vMultiply * .vMultiply2) )
INSERT INTO InvTransacts (custpart, invout, invdate ) +
VALUES .vCompPart2, .vCompInv2, .#DATE
ENDWHILE
ENDWHILE
DROP CURSOR cCInv
DROP CURSOR cCInv2
Karen
In a message dated 3/15/2012 3:43:56 PM Central Daylight Time,
[email protected] writes:
> This has been an interesting discussion and I would like to see how you
> set up the table to do your cursors.
>
> You may come up with a simpler way of doing what we do now.
