Sum of Cardex entries must equal Item Location totals

On JD Edwards (JDE), the sum of cardex entries must equal item location totals.  The SQL query below will find exceptions to this rule.  These exceptions are usually due to failed transactions, so you’ll want daily monitoring.

with LOCN as (SELECT distinct limcu, liitm, sum(lipqoh) as LOCNtot , lilocn FROM library.f41021  group by limcu, liitm, lilocn order by limcu, liitm, lilocn),

LDGR as (select ilmcu, ilitm, illitm, illocn, sum(iltrqt) as Ldgtot FROM library.f4111 group by ilmcu, ilitm, illitm, illocn)

select trim(limcu), liitm, trim(substr(illitm,1,16)), lilocn, LOCNTOT, LDGTOT, LOCNtot – Ldgtot DIFF from locn inner join ldgr on limcu = ilmcu and liitm = ilitm and lilocn = illocn where LOCNtot <> ldgtot order by ilmcu, illitm, illocn

Item ledger (cardex) = F4111

Item location = F41021

Tags: cardex, JDE, SQL