Sum of Cardex entries must equal Item Location totals

15 Feb

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



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: