SQL level verification of Integrities

[scotto72]

I've checked the archives and come up with not a lot of information, and am looking for some recommendations.

We have some very "interesting" situations in our financial tables (F0011/F0411/F0413/F0414/F0902/F0911) and I am working on developing some SQL (MS-SQL 7.0) level scripts to do independant integrities as opposed to the standard JDE level integrities. Has anyone ever done this before and have some recommendations? Not being a SQL guru (yet, but after this project I will be!) some of the table joins and process flows are a bit "circuitous" to put it mildly in terms of how tables get updated as the transactions flow through the vouchering process all the way through the check run/post. Any help is greatly appreciated.

TIA! (Thanks in advance!)

- Scotto

B7332 sp10.1 SQL7.0 NT 4.0 sp5

 

[david_mallory]

Scotto,

These are the table joins for F0011, F0411, F0413, F0414, F0911, F0902.

voucher to check detail F0411 DOC, SFX F0414 voucher num and line
num one to one

check detail to check F0414 PYID F0413 often many to one (pay 5
vouchers on one check)
PYID is surrogate for check num

check to GL entry F0413 DCT DOC F0911 one to one doc type
and doc num
This gives you the GL entry WITHOUT the AE (from the AAI) side of the entry.
Not both debit and credit. AE side is a summarized entry (all checks in the
run).

GL transaction to GL balance F0911 AID F0902 a zillion to one
F0902 has balance thru last posted (R09801 updates F0902) GL transaction in
F0911.

For F0011, batches, it is ICU batch num and ICUT batch type, to any
transaction record.

In general, watch for leading zeros and blanks. Not always the same,
transaction table to F0911 GL. Example: MCU, or DOCO.

Yes, you will be well served by doing your own integrity reports. On target.

Dave Mallory Denver Water OW 7.332 SP 15.1 Oracle 8.1722 NT 4.0

 

[scotto72]

Re: RE: SQL level verification of Integrities

Thanks alot for the help! I just wanted to make sure I was on the right track, great advice. I was having problems with the spacing issues, I think that may be one of those things. I'll let the list know later how things go. Thanks again!

- Scotto

B7332 sp10.1 SQL7.0 NT 4.0 sp6a
ESU's: All prerequisites, JD5550