Dynamics GP eConnect has a "feature" called Transaction Requester that, among other things, allows it to compile a list of all records or transactions that have been inserted, updated, or deleted in Dynamics GP.
eConnect Requester fundamentally consists of SQL Server database triggers that monitor changes to the Dynamics GP database tables and record any inserts, updates, or deletes that are made to that table. When the triggers detect a change, they write a record to the eConnect_Out table.
If you want, you can also use the eConnect Outgoing Service, which can output XML files or send XML to a queue in MSMQ.
eConnect Requester is a feature that is very valuable for integrations where Dynamics GP is the system of record and data needs to be sent from Dynamics GP to an external system. For instance, if an external system needs the ability to create purchase requisitions, you may want to automatically send all new or updated vendor records and inventory items from GP to that system so that they only need to be maintained in GP. Or if you use a third-party logistics vendor or fulfillment center, you may need to send purchase orders or sales invoices to those facilities so that they can receive and ship your products.
This all sounds great, and in concept it is, but there is one significant fundamental limitation to eConnect Requester. If you look at the eConnect_Out database table, you will see that it really only tells you which table was updated, the index value (document number) for the record, and whether the record was inserted, updated, or deleted.
This approach typically works fine for two types of database records: Master records, such as customers, vendors, items, GL accounts, etc., and transaction records that are posted and fully committed, such as posted GL journal entries, posted vouchers, posted receivables transactions, etc.
This means that eConnect Requester has some drawbacks with transactions that are not posted, such as Purchase Orders. Purchase Orders are somewhat unique because they can be created, saved, edited, approved, printed, released, changed, printed again, voided, and deleted. This is a challenge because eConnect Requester doesn't track these higher level document "states" or status--it only tells you whether a new record has been inserted into a table, whether a record has been updated, or whether a record has been deleted.
This is a limitation / challenge / problem when you have to export purchase orders. Let's say that you are sending purchase orders to an external 3PL warehouse that will be receiving your inventory. The warehouse needs to receive any new purchase orders that have been entered into Dynamics GP. But not every PO. You probably don't want to send unapproved POs. And you probably don't want to send unreleased POs. So when your export process sees a PO record in the eConnect_Out, you can't just assume that it is ready to be exported. You have to check the status first and determine whether the PO has been approved and released.
So what if the PO is then changed? Maybe a line is added, another line is cancelled, and one or more quantities are updated. Your 3PL warehouse needs to know about these changes so that they can receive against accurate purchase orders. The warehouse doesn't want the entire PO sent to them after every change--they only want the changes sent to them.
Quickly you will learn that eConnect Requester can't tell you about any of those things. Even though Purchase Order Line (POP10110) is an option in the Requester Setup, it turns out that the Requester only tells you that a PO record in the POP10100 table has been updated. You have to figure out what changed on that purchase order and whether that change needs to be sent to the 3PL warehouse. And unfortunately, eConnect requester offers no help there.
One approach is to store a copy of every PO line that has been exported. That way you essentially have a full copy of the PO and all of its lines and quantities, so if anything changes on the PO, you can compare the old values to the current values to determine what has changed. This may seem like a fair amount of overhead just to export POs, but it isn't difficult, works well, and has the benefit of serving as an audit trail for the export process, allowing you to verify that records were exported properly. But, you have to write the queries and code that will perform all of the comparisons, and handle all of the logic to determine what to export when a PO is changed. Trust me when I say that this can get very, very tricky for reasons you would least expect.
Although this "limitation" does seem burdensome, eConnect Requester can't fulfill all requirements, so it fulfills the fundamental and basic ones, and you are free to develop a solution that meets your specific business requirements.
Overall, eConnect Transaction Requester is a great, reliable resource for automating the exporting new data from Dynamics GP--just make sure that you are aware of the limitations before you jump into a project that requires it.
Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles. He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.
http://www.precipioservices.com
No comments:
Post a Comment