JD Edwards World F0011 Batch Status SQL Generator

-- JD Edwards World, Db2 for i: F0011 batch control headers
SELECT B.ICICUT AS BATCH_TYPE,
       B.ICICU  AS BATCH_NUMBER,
       B.ICIST  AS BATCH_STATUS,
       COALESCE(U.DRDL01, 'Not found in UDC 98/IC') AS STATUS_DESC,
       B.ICUSER AS USER_ID,
       B.ICDICJ AS BATCH_DATE_CYYDDD,
       CASE WHEN B.ICDICJ > 0
            THEN DATE(DIGITS(DECIMAL(B.ICDICJ + 1900000, 7, 0)))
       END AS BATCH_DATE,
       B.ICPOB  AS POSTED_OUT_OF_BALANCE
FROM JDFDATA.F0011 B
LEFT JOIN JDFDATA.F0005 U
       ON U.DRSY = '98'
      AND U.DRRT = 'IC'
      AND TRIM(U.DRKY) = TRIM(B.ICIST)
WHERE B.ICICUT = 'G'
  AND B.ICIST <> 'D'
  AND B.ICUSER LIKE '%'
  AND B.ICDICJ >= 126001
ORDER BY B.ICDICJ, B.ICICU;

-- Count F0911 lines not yet posted (GLPOST <> 'P') for each batch
SELECT B.ICICUT AS BATCH_TYPE,
       B.ICICU  AS BATCH_NUMBER,
       B.ICIST  AS BATCH_STATUS,
       B.ICUSER AS USER_ID,
       G.GLLT   AS LEDGER_TYPE,
       COUNT(*) AS UNPOSTED_LINES,
       SUM(G.GLAA) AS NET_AMOUNT_STORED
FROM JDFDATA.F0011 B
JOIN JDFDATA.F0911 G
  ON G.GLICU  = B.ICICU
 AND G.GLICUT = B.ICICUT
WHERE G.GLPOST <> 'P'
  AND B.ICICUT = 'G'
  AND B.ICDICJ >= 126001
GROUP BY B.ICICUT, B.ICICU, B.ICIST, B.ICUSER, G.GLLT
ORDER BY UNPOSTED_LINES DESC;

-- EnterpriseOne uses the same F0011 and F0911 column names.
-- On E1 with Db2 for i, change the libraries, for example:
--   JDFDATA -> PRODDTA (business data)
--   JDFDATA  -> PRODCTL (control tables, where F0005 lives in E1)
-- On E1 with SQL Server or Oracle, keep the column names and filters
-- but replace the DIGITS/DATE conversion with that database's date functions.