Entity Relationship

Hi I’m new here as well as to Navision.

I was asked to produce an entity relationship diagram for IT audit purposes.

eg.

in standard Navision, when post Invoice from Sales Order (form42) vs Post Invoice from Invoice (form43), what are the tables that are affected

I was also asked to document the accounting entries of such a transaction.

Doing trial & errors testing takes too long & gets very complicated…

Where can I find such documentation?

Thanks in advance.

Good old manual Labour.

You’ll spend hours trying to find “Everywhere” a piece of data gets modified from the post routine, and the information is not made avaliable from MS.

If you really need it, you’ll have to do it yourself.

Sorry

/TH

Thanks for the reply.

Any idea how companies convince IT audit that the system is working according to expectations if MS does not provide such information?

By manual labour? Or just mention it’s by MS?

I’ve been around for a few Audit’s and never been asked for an Entity Diagram of the system.

As long as the figures add up with the documentation, then they are normally happy.

I’m assuming your talking about an external Audit by the accountants?

Reminds me of a time we have an auditor in the warehouse for a stock check.

He decided he wanted to confirm the count of a few pallets in bulk stock. So we happily got the pallets down and left him to it.

Funnily enough 6 months later when he was back, he wanted to confirm the stock count of a pick face bin instead.

From what I know my accountant is just took over, so he cannot explain certain things or figures…

eg What function does Direct Cost Applied serve in my company’s operations & why is it in the Chart of Accounts…

He’s thinking of providing something like the ER diagram to the IT audit & let them sort it out…

Sounds more like the accountant needs a days training to understand the system, the posting routines and transactional investigation.

An ER diagram is not going to make somebody understand cost posting. In fact it’s only going to confuse them more. The IT department should only be concerned with making the app available for the users, not explain accounting to the accountant.

I think in our case it’s going this way

IT audit needs ER diagram

Accounts Dept needs accounting entries

IT Dept needs to provide these to IT audit & Accounts Dept

So now:

Accounting entries: not provided

ER Diagram: not available

IT Dept: lots of work to do

My first job as a freelancer, was for a client that had been refused an audit, by this I mean their accounting company refused to sign off the accounts because they could not prove the numbers. Key to it was the Cost Of Goods Sold. Basically without the sign off they were in for a huge tax bill, and it went back three years, so there were also going to be some stiff fines if they could not prove the numbers. The company had ben audited before by a small accounting company, but now they had grown to the point where they had to have an audit signed off by one of the Big 7.

It took me about 9 weeks, of very long days to do the job. Working at nights, and updating live data during the night as I corrected cost issues. But ultimately what the Auditors needed was a print out of every GL entry in the COGS account, and an audit trail of why it was there, and justification that it was a valid COGS.

Eventually I had the report, we bought two new big printers, and shut down on Friday afternoon and started printing through the weekend. The company had one guy working the weekend whose sole job was to feed the printers. Basically form the shut down, the company were not allowed to touch Navision until the auditors had signed off. We finished printing late Sunday night, and put the report in those vertical Document holders, with the from to date marked on the outside. The final printout was just short of 4 meters long/high which is a lot of paper.

Monday in came the Auditors, the head Lady iin charge, and 3 of her juniors. She told them to grab one page each at random. She took the three pages, glanced at them and said “yes this looks fine, you can restart the system now”.

When I asked her latter, she said that basically if we went to all this work, and we trust the numbers, then all she needs to figure out is if she can trust us. Which now she did.

The point is that these AUditors are not stupid, most of them have seen all the tricks, they just need to know that you understand your system, and are able to work with it to get numbers you trust.

You just need to get your people to understand Navision. And in any case an entity diagram wont help in the tiniest bit. Instead, learn Navision, learn the system, and when they ask you questions know how to work out the correct answer. If you trust your system, then you just need to get them to trust you.

For my case it more than just a routine audit if you get what I mean…a normal audit does not require ER diagrams…

ER diagrams are used as proof that the data was populating the intended tables, also as a fast way to track the starting point of entry.

In my eg earlier a Sales Invoice could be posted from Sales Order form or Sales Invoice form so the transactions or entries or documents generated in the system are not the same.

There could be a bug or a feature which is customized or was it standard, coupled with different versions or SP, with the ER diagram things could be a lot more easier.

Believe me, a company with a potential half million dollar tax bill and a big 7 audit company about to dump them, is by no means a routine audit. [:|]

As to your example, first ignore forms, there is no business logic processed on forms. And in any case you concept just fell to pieces, since an entity relationship, will show that there is no difference between a sales oder and a sales invoice, since they are the same tables. Since 3.xx version of Navision, Order and Invoice FORMS post the same, there was a slight difference in previous versions in the way some document numbers were generated, but if you are running on software that old, then you can forget any compliance issues anyway.

Just learn how the system works and train the people.

My company is focusing on system or IT audit rather than financial audit. Since there is no difference posting from Sales Order or Sales Invoice, I need to prove this to the audit.

Side track a bit, we have just proven that why some items are not billed when discounts are involved. Navision rounds discounts first instead of amounts…

Try here:

http://navitools.com/

Oh yeah! Thanks!

Moved to End User Forum.