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
harleysavage 