Need help understanding why this query is producing these results

Question

Consider the database entries below. The distinguishing identifier is the "short" item number, 89721.

Data:

F3003

IRKIT   IRKITL       IRMMCU  IRMCU  IREFFF      IREFFT      IRAN8    IRTRT   IRUOM
89721   7N74N050046  B20     NXT    12/06/2015  12/31/2040  200038   M       SY
89721   7N74N050046  B70     NXT    07/28/2021  12/31/2040  200038   M       SY

F0101

ABAN8   ABALPH           ABAC01
200038  Company XYZ      CON

F4101

IMITM   IMDSC1
89721   TWIN RIB N05 ONYX HS-SY 46.5"

F41021

LIITM   LIPBIN   LIPQOH
89721   S        256
...

[a total of 99 "S" entries where LIPQOH sums to 16554]

F4211

SDITM   SDNXTR   SDDCTO   SDUORG
89721   540      SO       4700.00

SQL:

SELECT F3003.IRAN8              AS CUST_NO,
       F3003.IRKIT              AS SHORT_ITEM,
       F3003.IRKITL             AS ITEM_NO,
       F3003.IRUOM              AS UOM,
       F3003.IRMCU              AS WC,
       F0101.ABALPH             AS CUST_NAME,
       F4101.IMDSC1             AS ITEM_DESC,
       SUM(F41021.LIPQOH / 100) AS ON_HAND
FROM   PROD2DTA.F3003 AS F3003
       INNER JOIN PROD2DTA.F0101 AS F0101
         ON F3003.IRAN8 = F0101.ABAN8
       INNER JOIN PROD2DTA.F4101 AS F4101
         ON F3003.IRKIT = F4101.IMITM
       INNER JOIN PROD2DTA.F41021 AS F41021
         ON F3003.IRKIT = F41021.LIITM
WHERE  F3003.IRMCU LIKE '%NXT'
       AND F3003.IRTRT = 'M'
       AND F0101.ABAC01 = 'CON'
       AND F41021.LIPBIN = 'S'
       AND CURRENT_DATE BETWEEN DATE(CONCAT(CAST(F3003.IREFFF / 1000 AS INT) + 1900, RIGHT(MOD(F3003.IREFFF, 1000) + 1000, 3))) AND DATE(CONCAT(CAST(F3003.IREFFT / 1000 AS INT) + 1900, RIGHT(MOD(F3003.IREFFT, 1000) + 1000, 3)))
GROUP  BY F3003.IRAN8,
          F3003.IRKIT,
          F3003.IRKITL,
          F3003.IRUOM,
          F3003.IRMCU,
          F3003.IRDSC1,
          F0101.ABALPH,
          F4101.IMDSC1
HAVING SUM(F41021.LIPQOH / 100) > 0

When I run the query above, I get the following results:

CUST NO   CUST NAME           ITEM NO       ITEM DESC                       UOM   ON HAND
200038    Company XYZ         7N74N050046   TWIN RIB N05 ONYX HS-SY 46.5"   SY    16,554.00

This is correct based on comparison with JD Edwards applications.

Note that in the F3003 table above, there are two entries that meet the filtering criteria in the query. I have provided the IRMMCU column to show a source of multiple results, even though I'm not selecting it in the SQL. This represents a plant location where this item may be manufactured. The dates also may contribute to multiple results, because they both meet the filter critera, but are not selected in the SQL.

Now, consider the following query, which uses the one above as a subquery:

SELECT CUST_SPEC_ON_HAND.CUST_NO    AS CUST_NO,
       CUST_SPEC_ON_HAND.SHORT_ITEM AS SHORT_ITEM,
       CUST_SPEC_ON_HAND.ITEM_NO    AS ITEM_NO,
       CUST_SPEC_ON_HAND.UOM        AS UOM,
       CUST_SPEC_ON_HAND.WC         AS WC,
       CUST_SPEC_ON_HAND.CUST_NAME  AS CUST_NAME,
       CUST_SPEC_ON_HAND.ITEM_DESC  AS ITEM_DESC,
       CUST_SPEC_ON_HAND.ON_HAND    AS ON_HAND,
       SUM(F4211.SDUORG / 100)      AS OPEN_ORDER
FROM   (SELECT F3003.IRAN8              AS CUST_NO,
               F3003.IRKIT              AS SHORT_ITEM,
               F3003.IRKITL             AS ITEM_NO,
               F3003.IRUOM              AS UOM,
               F3003.IRMCU              AS WC,
               F0101.ABALPH             AS CUST_NAME,
               F4101.IMDSC1             AS ITEM_DESC,
               SUM(F41021.LIPQOH / 100) AS ON_HAND
        FROM   PROD2DTA.F3003 AS F3003
               INNER JOIN PROD2DTA.F0101 AS F0101
                 ON F3003.IRAN8 = F0101.ABAN8
               INNER JOIN PROD2DTA.F4101 AS F4101
                 ON F3003.IRKIT = F4101.IMITM
               INNER JOIN PROD2DTA.F41021 AS F41021
                 ON F3003.IRKIT = F41021.LIITM
        WHERE  F3003.IRMCU LIKE '%NXT'
               AND F3003.IRTRT = 'M'
               AND F0101.ABAC01 = 'CON'
               AND F41021.LIPBIN = 'S'
               AND CURRENT_DATE BETWEEN DATE(CONCAT(CAST(F3003.IREFFF / 1000 AS INT) + 1900, RIGHT(MOD(F3003.IREFFF, 1000) + 1000, 3))) AND DATE(CONCAT(CAST(F3003.IREFFT / 1000 AS INT) + 1900, RIGHT(MOD(F3003.IREFFT, 1000) + 1000, 3)))
        GROUP  BY F3003.IRAN8,
                  F3003.IRKIT,
                  F3003.IRKITL,
                  F3003.IRUOM,
                  F3003.IRMCU,
                  F0101.ABALPH,
                  F4101.IMDSC1
        HAVING SUM(F41021.LIPQOH / 100) > 0) CUST_SPEC_ON_HAND
       LEFT JOIN PROD2DTA.F4211 AS F4211
         ON CUST_SPEC_ON_HAND.SHORT_ITEM = F4211.SDITM
            AND F4211.SDDCTO IN ( 'SO', 'SM', 'S2' )
            AND F4211.SDNXTR < 999
GROUP  BY CUST_SPEC_ON_HAND.CUST_NO,
          CUST_SPEC_ON_HAND.SHORT_ITEM,
          CUST_SPEC_ON_HAND.ITEM_NO,
          CUST_SPEC_ON_HAND.UOM,
          CUST_SPEC_ON_HAND.WC,
          CUST_SPEC_ON_HAND.CUST_NAME,
          CUST_SPEC_ON_HAND.ITEM_DESC,
          CUST_SPEC_ON_HAND.ON_HAND

When I run this query for the same "short" item number 89721, I get the following:

CUST #  CUSTOMER NAME       ITEM #        ITEM DESCRIPTION               UOM    ON HAND     OPEN ORDERS
200038  Company XYZ         7N74N050046   TWIN RIB N05 ONYX HS-SY 46.5"  SY     33,108.00   4,700.00

Notice that the on-hand quantity is now twice what it was from the first query.

I can't figure out why this is happening. It does not happen for all data in the result set. From what I can tell, the items with an inflated on-hand quantity are those items in the F3003 table with entries for multiple manufacturing plants (F3003.IRMMCU). But why would it work correctly for the simpler query, and not for the second query that is using the first query?

Below are CREATE TABLE statements to create the tables shown above. Note: The actual JDE tables contain many more columns than what is shown here.

You should also note that JDE stores dates as an integer in a JDE "Julian" date format. The format is as follows: CYYDDD, where C is the century, YY is the year and DDD is the day of the year. For example, April 28, 2022 is represented as 122118.

CREATE TABLE F3003
(
IRTRT NCHAR(3) NULL, -- Type of Routing
IRKIT INT NULL, -- Parent (short) Item Number
IRKITL NCHAR(25) NULL, -- Kit - 2nd Item Number
IRMMCU NCHAR(12) NULL, -- Branch
IRMCU NCHAR(12) NULL, -- Business Unit
IREFFF NUMERIC(6) NULL, -- Effective - From Date
IREFFT NUMERIC(6) NULL, -- Effective - Thru Date
IRUOM NCHAR(2) NULL, -- Unit of Measure as Input
IRAN8 INT NULL, -- Address Number
);

CREATE TABLE F0101
(
ABAN8 INT NULL, -- Address Number
ABALPH NCHAR(40) NULL, -- Name - Alpha
ABAC01 NCHAR(3) NULL, -- Category Code - Address Book 01
);

CREATE TABLE F4101
(
IMITM INT NULL, -- Item Number - Short
IMDSC1 NCHAR(30) NULL, -- Description
);

CREATE TABLE F41021
(
LIITM INT NULL, -- Item Number - Short
LIPBIN NCHAR(1) NULL, -- Primary Location (P/S)
LIPQOH INT NULL, -- Quantity on Hand - Primary units
);

CREATE TABLE F4211
(
SDDCTO NCHAR(2) NULL, -- Order Type
SDITM INT NULL, -- Item Number - Short
SDNXTR NCHAR(3) NULL, -- Status Code - Next
SDUORG INT NULL, -- Units - Order/Transaction Quantity
);

If you really want some information overload, you can find all sorts of information about the JDE data ecosystem by going to http://www.jdetables.com/.

INSERT statements:

Note that the dates are in the JDE Julian date format.

INSERT INTO F3003 (IRKIT,IRKITL,IRMMCU,IRMCU,IREFFF,IREFFT,IRAN8,IRTRT,IRUOM) VALUES (89721,'7N74N050046','B20','NXT',115340,140366,200038,'M','SY');
INSERT INTO F3003 (IRKIT,IRKITL,IRMMCU,IRMCU,IREFFF,IREFFT,IRAN8,IRTRT,IRUOM) VALUES (89721,'7N74N050046','B70','NXT',121209,140366,200038,'M','SY');

INSERT INTO F0101 (ABAN8,ABALPH,ABAC01) VALUES (200038,'Company XYZ','CON');

INSERT INTO F4101 (IMITM,IMDSC1) VALUES (89721,'TWIN RIB N05 ONYX HS-SY 46.5"');

Note that the on-hand quantity (LIPQOH) is stored with 2 places after the decimal point. To get the actual value, it must be divided by 100. Also, I have just one insert statement to replicate the on-hand quantity, rather than inserting 99 lines.

INSERT INTO F41021 (LIITM,LIPBIN,LIPQOH) VALUES (89721,'S',1655400);

Note that the ordered quantity (SDUORG) is stored with 2 places after the decimal point. To get the actual value, it must be divided by 100.

INSERT INTO F4211 (SDITM,SDNXTR,SDDCTO,SDUORG) VALUES (89721,540,'SO',470000);