Cleansing the Chart of Accounts

[DBohner-(db)]

I'm working with a client to cleans the Chart of Accounts from Non-Activity.

Using sequel statements, I'm mimicking the R09814 (Delete Account Master) - only, I'm deleting records (yet). I am using a Category Code to tag F0901 records that 'truly' do have activity in the corresponding transaction tables....

As part of the process - I'm trying to determine if there is a process that I can determine which Level of Detail Accounts are Valid. Being that LoD records are 'not' supposed to have underlying transactions associated with them - I'm not sure how I can tag them in such a way that they either do or do not have underlying Transaction Detail under them.

One thought (guess??) would be to create a global Financial Report and run it for each LoD. If the LoD has a non-zero balance, It has transactions. However - if the Account / LoD does not have a balance, I can't really confirm it doesn't have any underlying transactions.

Ok - enough Developer ramblings on the Applications side of the list. Does anyone know a simple solution to identify which LoD records should be Kept and which can be Deleted - when cleansing the Chart of Accounts?

(db)

 

[peterbruce]

Dan,

If you have already used a Category Code to tag F0901 records that 'truly' do have activity, then why can't you use that code to do your check. You may have to work "backwards" ie order the accounts to decend on the object value, wthin the business unit value, breaking on the LoD change.

You would probably need an activity flag for each LoD value (3 to 9). If the account is marked as having activity, set the flag for it's LoD. If the LoD decreases, and the previous LoD flag is set, set the new (lower) LoD flag and update the category code for that (LoD) account, then unset the previous (higher) LoD Flag. If the LoD increases, do nothing.

 

[DBohner-(db)]

Peter,

Basically, the suggestion is a follows:
Sort F0901 by CO.MCU.OBJ
- Flag = Active, if there are any transactions
If the Level of Detail Changes, and the Flag says Active, mark the Header/LoD as Active.
Else
Mark as Suspect Inactive.

I was trying to scheme an SQL process - but, Oracle doesn't seem to have a means to measure Headers Records for Activity, aside from the Sequence thang.

Thanks!

(db)

 

[peterbruce]

Dan,

It could be easily done using an Oracle Database Procedure.

 

[peterbruce]

Dan,

An SQL process huh ...

Have a look at the attachment ... I think it will work.

Should I charge you (and everyone else who downloads it)?

... Nah, this one's on me.