3rd party invoice package integration

jhpg

I work for a company with a vessel operation system for ship owners and operators. From an accounting point of view it could be regarded as an invoicing system. It also generates some general ledger journal type transactions as well as invoices. We would like to offer a standard integration to Navision as an option for clients who want to replace their accounting system or haven't got one at all.

Our system runs on SQL Server. We have already integrated to various other accounting systems.

Which documents should I read for information on posting sales and purchase invoices and general ledger journals into Navision from another system and where can I get the documents?

Thanks in advance.

DenSter

There's no standard document that I know of. This would be custom development, probably sending the invoice information into MSMQ as an XML document and have NAS monitor the message queue. I've also done some integration work using SQL views, but I personally don't really like that solution.

Note that an invoice in Navision must have valid data in the lines, like an Item number or a Resource number or a G/L Account number, and these numbers must represent valid data in the corresponding master tables. Just sending in invoice information is probably not enough, you would also need some sort of synchronization between the two systems regarding master data.

jhpg

Thanks. I thought there'd be some work to do with GL accounts, counterpart accounts, maybe currency codes, etc. In fact, vessel operators like to account on a voyage by voyage basis so there probably needs to be a subset of GL accounts automatically created for each vessel and voyage.

Anyway, that's detail. What I was hoping for was some sort of event driven direct SQL to SQL work. I've got outbound interface tables with all the transactions (subject to account translation and other data massaging) just waiting to be posted.

DenSter

Nothing event driven that I can think of. You will need to process these transactions through journals and have Navision take care of the business logic by posting these journals. You really do not want to get into duplicating ledger entries directly in the Navision SQL tables.

What could maybe work is an instance of NAS (this is a Navision session that runs as a service) that periodically checks those transaction tables and maps records into the journals. I have a manual process working that runs off of views into an external database, and that could be moved into a NAS like that.

jhpg

Sounds like the right kind of thing. I usually write updateable views on the interface tables that allow transactions to be flagged with processing results on the other side of the interface so they can be batch processed if required. The view just shows unprocessed transactions.

Is there some documentation that would get me started? The NAS doesn't matter much at this point. Getting transactions in would be a start and help me find my way around the development environment.

I've only got a demo copy at the moment - do I get all the tools I need with a licensed copy?

By the way - thanks very much for your help and your time ...

DenSter

I should write a how-to about this....

Read the section about linked tables in the application designer's guide, most info is in there. Study it, and study again and then one more time.

Some pointers:
- make sure none of the fields in your database allow null values. Navision chokes on null values.
- I have not been able to make an auto-increment identity field work in a linked view in Navision, so try to avoid that
- create views in the Navision SQL database into your transaction database
- create a table in Navision:
---- the table name must match the view name
---- all the fields must match in name and data type
---- SQL type Text does NOT map into Navision type Text, use varchar instead
---- set the datapercompany property of the Navision table to No. It is supposedly possible to do datapercompany, but then you'd have to have multiple views, and it gets complicated after that.
---- Primary key must match
---- LinkedObject property = Yes
---- LinkedInTransaction = No

Oh and all users must be db_owner in both databases. I have not been able to make this work any other way. As soon as you take away db_owner you get messages that the user is not a valid user in either database.

With the demo license you should be able to create a few tables in the 50000 range, so you should be good.