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

 

Advertisements

Leave a Reply

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

WordPress.com Logo

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

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s

%d bloggers like this: