Adding Analytics Distribution to a Payables Transaction
Josh asked 6 years ago
I am failing to get any of the analytic distributions to integrate. The Payables transaction is integrating fine. The Create Payables transaction is creating the voucher correctly.I have successfully created the payables transaction distribution correctly. To do so I had to follow the instructions to turn off the Create distributions setting and a second node for the distributions so I could create the Payable line and the purchase line manually.I cannot get the analytics distribution to show up at all though and I don\’t get any error messages. The integration always runs successfully but there are no analytic distributions even though they are requried on the purchase account. �I have tried connecting the Document Number in the Analytics Distribution Mapping to the Voucher Number and the Invoice Document Number. I have tried entering the DocType and leaving it blank. I have tried entering the amount, the percentage, and both. I\’ve had coded the sequence number on both the account distribution and the analytic distribution to force them to match. I\’ve tried with and without the account number string, I am entering the transaction Dimension and the Dimension code. I\’ve tried entering a subledger assign lookup ID and not entering one. I\’ve tried having the update if exists set to false and set to True (as well as checking and unchecking the update existing checkbox in the header). �I\’m just trying to import one line from a file to get this to work once, and I can\’t seem to make it happen. �Any other ideas of things to try? I\’m out of ideas and making no headway.
Answers
Josh answered 6 years ago
We were finally able to work this out. We first had to reorder the nodes using a SQL statement to make sure the xml would appear in the right order (see below). Then I also had an error that the Document Type for the analytic distribution needed to be 0 even though the document itself and the accounting distribution lines had a different document type. Last issue was that we could not use the integrations we had created before running the SQL statement, we had to start from scratch. Once we did those three things we were able to make the analytic distribution appear correctly.
Here is the SQL we ran but please note that we added a custom node to ours named “Add PAY distribution” so this SQL is unlikely to work for anyone else but might be helpful in understanding what was done to make ours work.
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1000 WHERE DisplayName=’Create/update vendor’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1001 WHERE DisplayName=’Add vendor address’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1002 WHERE DisplayName=’Add tax detail’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1003 WHERE DisplayName=’Add PAY distribution’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1004 WHERE DisplayName=’Add distribution’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1005 WHERE DisplayName=’Analytics Distribution’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1006 WHERE DisplayName=’Create payables transaction’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1007 WHERE DisplayName=’Update MDA’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1008 WHERE DisplayName=’eOne AP Intercompany Header’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1009 WHERE DisplayName=’eOne AP Intercompany Line’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1 WHERE DisplayName=’Create/update vendor’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=2 WHERE DisplayName=’Add vendor address’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=3 WHERE DisplayName=’Add tax detail’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=4 WHERE DisplayName=’Add PAY distribution’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=5 WHERE DisplayName=’Add distribution’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=6 WHERE DisplayName=’Analytics Distribution’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=7 WHERE DisplayName=’Create payables transaction’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=8 WHERE DisplayName=’Update MDA’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=9 WHERE DisplayName=’eOne AP Intercompany Header’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=10 WHERE DisplayName=’eOne AP Intercompany Line’ AND NodeType=’PMTransactionType’
GO
Here is the SQL we ran but please note that we added a custom node to ours named “Add PAY distribution” so this SQL is unlikely to work for anyone else but might be helpful in understanding what was done to make ours work.
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1000 WHERE DisplayName=’Create/update vendor’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1001 WHERE DisplayName=’Add vendor address’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1002 WHERE DisplayName=’Add tax detail’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1003 WHERE DisplayName=’Add PAY distribution’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1004 WHERE DisplayName=’Add distribution’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1005 WHERE DisplayName=’Analytics Distribution’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1006 WHERE DisplayName=’Create payables transaction’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1007 WHERE DisplayName=’Update MDA’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1008 WHERE DisplayName=’eOne AP Intercompany Header’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1009 WHERE DisplayName=’eOne AP Intercompany Line’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=1 WHERE DisplayName=’Create/update vendor’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=2 WHERE DisplayName=’Add vendor address’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=3 WHERE DisplayName=’Add tax detail’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=4 WHERE DisplayName=’Add PAY distribution’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=5 WHERE DisplayName=’Add distribution’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=6 WHERE DisplayName=’Analytics Distribution’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=7 WHERE DisplayName=’Create payables transaction’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=8 WHERE DisplayName=’Update MDA’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=9 WHERE DisplayName=’eOne AP Intercompany Header’ AND NodeType=’PMTransactionType’
GO
UPDATE [SmartConnect].[dbo].[NodeToNodeTypeAssociationName] SET Sequence=10 WHERE DisplayName=’eOne AP Intercompany Line’ AND NodeType=’PMTransactionType’
GO
Josh
I’m doing the same integration right now and having issues as well. I’ll let you know if I get anything to resolve.