eOne’s SmartConnect
We are contracted with, often, to write complex integrations. Though a Journal Entry should not be considered ‘complex’ the need to bring in an Inter-Company JE adds a level of complexity to even the simplest integrations with SmartConnect.
SmartConnect is an awesome tool by eOne Solutions, if you are unfamiliar with it, it is a tool that is often used to build out integrations to and from Dynamics GP and is a front-end for eConnect, making the coding of econnect integrations much easier! It is a software that you can rent temporarily or long-term or purchase out-right.
We usually end up putting SmartConnect in at all our clients simply because it is such a flexible and valuable tool.
Let us get down to it: How to write Inter-Company Journal Entries in Dynamics GP using eOne’s Smartconnect! Bear in mind that inter-company transactions in Payables and Receivables work MUCH the same way!
Source Data Format:
1) It’s 100% your choice as to the data source, but there are some needs in the source data, specifically if you have a mix of intra-company and inter-company distribution lines.
1) My data is usually formatted as such:
i. Column 1: Batch ID
ii. Column 2: JE Date
iii. Column 3: Sequence (HIGHLY IMPORTANT)
1. This will tie the distribution line to a different node for intercompany
2. This will also control the order in which the data appears in the JE
iv. Column 4: Distribution Reference (up to you)
v. Column 5: Is it Intercompany? 0 is ‘No’ and 1 Is ‘Yes’
1. Also a required field if you have a mix of inter and intra company distribution lines in the same file
vi. Column 6: The Inter Company ID for the line
vii. Column 7: The ‘Holding Account’
1. This is also an eOne requirement for how the code works in the background of SmartConnect in order to perform the inter-company transactions.
2. It needs a ‘temp account’ that exists in the ORIGINATING Company because GP’s regular Distribution Line Node will not recognize the DESTINATION companies account number in that node
a. Therefore, use a pass-through account – I usually use all 9’s so you do not get an error on the account not existing in the regular GP Distribution line Node.
3. This one CAN be hard-coded into fields and even in the calculations we’ll see later so you do not necessarily need it in the SOURCE document/query.
viii. Column 8: The Account Number
1. These should exist in the destination company(ies) and will be used in the eOne node for intercompany.
2)
Mapping the Source Data Overview
1) Choose the GP Node Group (General Ledger)
2) Choose ‘Transactions’ as the type
3) For Inter-Company JE’s you will need to map at least 4 of the 6 Nodes that appear:
i. Add distribution (This is the GP Regular distr line node)
1. Usually want to group this on both the Key Field and
2. The SEQUENCE Number
ii. Create Journal
1. Usually group on the Key Fields Here even though that is not always required
iii. eOne GL Intercompany Header
1. Usually group on the Key Fields Here even though that is not always required
iv. eOne GL Intercompany Line
1. Usually group on the Key Fields AND
2. The SEQUENCE Number
4)
Mapping the Source Data By Node
1) ADD DISTRIBUTION:
a. Map the required fields as usual
b. Map other fields EXCEPT the Account String as usual
c. Use a CALCULATION for the Account String
i. You want SmartConnect to replace the Account String from your SOURCE data with the pass-through account (all 9’s for mine) but only WHEN your line in the SOURCE is for a DESTINATION Company.
ii. Therefore the calculation for MY source looks like this:
1.
iii. Again, group this on the key field(s) AND the Sequence Number
1. Use the ‘Preview’ at the top of the Node Mapping Window to see how the data looks as it is mapped in that Node….this is also a GREAT way to troubleshoot groupings on all maps.
2) CREATE JOURNAL:
a. You can map this one the same as always
i. The eOne GL Intercompany Header node will have the checkbox field for InterCompany that you see when logged in to the GP Application, so no need to try to look for that in THIS node.
3) EONE GL INTERCOMPANY HEADER:
a. The Batch Number, Source and JE should all be the same as they are in the CREATE JOURNAL node
b. IC TRX
i. This filed I have mapped to my SOURCE data where 1 means it IS an intercompany transaction and 0 means it is not.
ii. THE ISSUE IS: You only want lines that are, INDEED, intercompany to even pass through this node so use a RESTRICTION of this sort:
1. IF it is marked as intercompany in the SOURCE with a ‘1’ then use this node, if not then don’t even ‘see’ the other lines
a. I LOVE using restrictions in complex integrations, they are awesome!
2. Example:
a.
c. You’ll need to enter a 1 as a Local Constant in “Update if exists” and at the top, check off Update Existing.
d. You’ll also want to group by the key fields on this node and no other fields.
4) EONE GL INTERCOMPANY LINE
a. Journal Entry, Sequence Line should both be mapped the same way you have the ADD DISTRIBUTION node mapped.
b. InterCompany ID should be coming from the SOURCE data
c. Holding Account (this is the pass-through account you created in the ORIGINATING company and THIS is the account SmartConnect will recognize in THIS integration as the pass-through account.
i. I usually hard-code this one
d. Account String: Map this to the source data because it WILL read the DESTINATION company accounts
e. BEWARE: You also have to put the restriction on this node to ONLY look at lines that are marked as intercompany, see 3.b.a above.
f. Do group this on the Key Field AND the SEQUENCE number in order for it to read the data right. This grouping should be the EXACT same as the ADD DISTRIBUTION node is mapped.
Good luck with your integration project and if you ever need SmartConnect assistance or would like to explore your options with SmartConnect then please reach out to Aisling Dynamics.