IBM DB2 SQL code example to pull Quantus indemted BOM.
CREATE PROCEDURE TEST_BOM_EXTRACTION (IN @BRANCH VARCHAR(8),
IN @ITEMNUM VARCHAR(1000)
)
SPECIFIC BOM_EXTRACTION
DYNAMIC RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_ITEMNUMS (
LINENUM INTEGER,
SEQSNUM INTEGER,
ITEMNUM VARCHAR(32)
)
ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_BOMS (
BRANCH VARCHAR(8),
PARENTITEMNUM VARCHAR(23),
OPSEQUENCE VARCHAR(8),
OPCODE VARCHAR(4),
ITEMNUM VARCHAR(32),
DESCRIPTION VARCHAR(32),
UOFM VARCHAR(4),
ITEMQTY DOUBLE,
UNITTIME DOUBLE,
LABOURTIMEUNIT CHAR,
SETUPTIME DOUBLE,
SETUPTIMEUNIT CHAR
)
ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;
BEGIN ATOMIC
DECLARE intLINENUM INTEGER DEFAULT 0;
DECLARE intSEQSNUM INTEGER DEFAULT 0;
DECLARE prevSEQSNUM INTEGER DEFAULT 0;
DECLARE intCOUNT INTEGER DEFAULT 0;
DECLARE newSEQS BOOLEAN;
DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
SELECT * FROM SESSION.TEMP_BOMS;
SET intCOUNT = QUERIES.TEST_BOM_COUNT(@BRANCH, @ITEMNUM);
IF intCOUNT > 0 THEN
INSERT INTO SESSION.TEMP_ITEMNUMS
VALUES(intLINENUM, intSEQSNUM, @ITEMNUM);
SET intLINENUM = intLINENUM + 1;
-- loop1: FOR ROW1 AS
-- SELECT
-- QUANTUS.ASSEMBLY_OPTION.ITEMNUM
-- FROM QUANTUS.ASSEMBLY_OPTION
-- INNER JOIN QUANTUS.INVENTORY ON QUANTUS.ASSEMBLY_OPTION.BRANCH = QUANTUS.INVENTORY.BRANCH
-- AND QUANTUS.ASSEMBLY_OPTION.PARENTITEMNUM = QUANTUS.INVENTORY.ITEMNUM
-- WHERE
-- LTRIM(QUANTUS.ASSEMBLY_OPTION.BRANCH) = @BRANCH AND
-- UPPER(LTRIM(QUANTUS.ASSEMBLY_OPTION.PARENTITEMNUM)) = UPPER(@ITEMNUM) AND
-- QUANTUS.ASSEMBLY_OPTION.OPCODE = 'BOM' AND
-- QUANTUS.ASSEMBLY_OPTION.TYPE = 'W'
-- ORDER BY QUANTUS.ASSEMBLY_OPTION.BRANCH, QUANTUS.ASSEMBLY_OPTION.PARENTITEMNUM
-- DO
-- INSERT INTO SESSION.TEMP_ITEMNUMS
-- VALUES(intLINENUM, intSEQSNUM, ROW1.ITEMNUM);
-- SET intLINENUM = intLINENUM + 1;
-- END FOR loop1;
SET newSEQS = TRUE;
WHILE newSEQS = TRUE DO
SET newSEQS = FALSE;
SET prevSEQSNUM = intSEQSNUM;
SET intSEQSNUM = intSEQSNUM + 1;
loop2: FOR ROW2 AS
SELECT * FROM SESSION.TEMP_ITEMNUMS WHERE SESSION.TEMP_ITEMNUMS.SEQSNUM = prevSEQSNUM ORDER BY LINENUM
DO
SET intCOUNT = 0;
SET intCOUNT = QUERIES.TEST_BOM_COUNT(@BRANCH, ROW2.ITEMNUM);
IF intCOUNT > 0 THEN
SET newSEQS = TRUE;
END IF;
IF intCOUNT > 0 THEN
loop3: FOR ROW3 AS
SELECT
QUANTUS.ASSEMBLY_OPTION.ITEMNUM
FROM QUANTUS.ASSEMBLY_OPTION
INNER JOIN QUANTUS.INVENTORY ON QUANTUS.ASSEMBLY_OPTION.BRANCH = QUANTUS.INVENTORY.BRANCH
AND QUANTUS.ASSEMBLY_OPTION.PARENTITEMNUM = QUANTUS.INVENTORY.ITEMNUM
WHERE
LTRIM(QUANTUS.ASSEMBLY_OPTION.BRANCH) = @BRANCH AND
UPPER(LTRIM(QUANTUS.ASSEMBLY_OPTION.PARENTITEMNUM)) = UPPER(LTRIM(ROW2.ITEMNUM)) AND
QUANTUS.ASSEMBLY_OPTION.OPCODE = 'BOM' AND
QUANTUS.ASSEMBLY_OPTION.TYPE = 'W'
ORDER BY QUANTUS.ASSEMBLY_OPTION.BRANCH, QUANTUS.ASSEMBLY_OPTION.PARENTITEMNUM
DO
INSERT INTO SESSION.TEMP_ITEMNUMS
VALUES(intLINENUM, intSEQSNUM, ROW3.ITEMNUM);
SET intLINENUM = intLINENUM + 1;
END FOR loop3;
END IF;
END FOR loop2;
END WHILE;
loop4: FOR ROW4 AS
SELECT * FROM SESSION.TEMP_ITEMNUMS ORDER BY LINENUM
DO
loop5: FOR ROW5 AS
SELECT
TRIM(QUANTUS.ASSEMBLY_OPTION.BRANCH) AS BRANCH,
LTRIM(QUANTUS.ASSEMBLY_OPTION.PARENTITEMNUM) AS PARENTITEMNUM,
LTRIM(QUANTUS.ASSEMBLY_OPTION.OPSEQUENCE) AS OPSEQUENCE,
QUANTUS.ASSEMBLY_OPTION.OPCODE,
LTRIM(QUANTUS.ASSEMBLY_OPTION.ITEMNUM) AS ITEMNUM,
RTRIM(QUANTUS.ASSEMBLY_OPTION.ALTITEMDESC) AS DESCRIPTION,
RTRIM(QUANTUS.ASSEMBLY_OPTION.UOFM) AS UOFM,
QUANTUS.ASSEMBLY_OPTION.ITEMQTY,
QUANTUS.ASSEMBLY_OPTION.UNITTIME,
QUANTUS.ASSEMBLY_OPTION.LABOURTIMEUNIT,
QUANTUS.ASSEMBLY_OPTION.SETUPTIME,
QUANTUS.ASSEMBLY_OPTION.SETUPTIMEUNIT
FROM QUANTUS.ASSEMBLY_OPTION
INNER JOIN QUANTUS.INVENTORY ON QUANTUS.ASSEMBLY_OPTION.BRANCH = QUANTUS.INVENTORY.BRANCH
AND QUANTUS.ASSEMBLY_OPTION.PARENTITEMNUM = QUANTUS.INVENTORY.ITEMNUM
WHERE
LTRIM(QUANTUS.ASSEMBLY_OPTION.BRANCH) = @BRANCH AND
UPPER(LTRIM(QUANTUS.ASSEMBLY_OPTION.PARENTITEMNUM)) = UPPER(LTRIM(ROW4.ITEMNUM))AND
QUANTUS.ASSEMBLY_OPTION.TYPE = 'W'
ORDER BY QUANTUS.ASSEMBLY_OPTION.BRANCH, QUANTUS.ASSEMBLY_OPTION.PARENTITEMNUM
DO
INSERT INTO SESSION.TEMP_BOMS
VALUES(
ROW5.BRANCH,
ROW5.PARENTITEMNUM,
ROW5.OPSEQUENCE,
ROW5.OPCODE,
ROW5.ITEMNUM,
ROW5.DESCRIPTION,
ROW5.UOFM,
ROW5.ITEMQTY,
ROW5.UNITTIME,
ROW5.LABOURTIMEUNIT,
ROW5.SETUPTIME,
ROW5.SETUPTIMEUNIT
);
END FOR loop5;
END FOR loop4;
END IF;
OPEN cursor1;
END;
END P1