TIBCO Scribe® Online Connector For Microsoft Dynamics GP

The TIBCO Scribe® Online Connector for Microsoft Dynamics GP works with Microsoft Dynamics GP 2013 R2, 2015, 2015 R2, 2016, 2016 R2, 2018, or 2019 On-Premise as either a source or a target with TIBCO Scribe® Online. This Connector provides support for single-tenant or multi-tenant Dynamics GP environments.

Connector Specifications

	Supported
Agent Types
On Premise	X
Cloud
Replication Services
Source
Target
Integration Services
Source	X
Target	X
Migration Services
Source	X
Target	X
Maps
Integration	X
Request-Reply	X
Message

Supported Entities

Dynamics GP Entities fall into several categories:

• Accounting Entities — Payables, receivables, and transactions
• Customer And Vendor Entities — Names and addresses
• Inventory Entities — Currency, price, and unit of measure
• Sales Order Entities — Discounts, history, and lines
• Invoice Entities — History and lines

Some Entities will be replaced in future releases, see Read-Only Entities To Be Replaced.

For a list of additional operations by Entity, see Special Operations.

Standard Operations

Accounting Entities

Entity	Query	Create	Update	Delete
Batch		X
GetNextPayablesVoucherNumber	X
GetNextReceivablesDocumentNumber	X
GetNextSalesInvoiceNumber	X
GetNextSalesOrderNumber	X
GLPostingAccount	X	X
GLTransaction	X	X
GLTransactionLine	X	X
GLUnitAccount	X	X
PayablesCreditMemo	X	X
PayablesDistribution	X	X
PayablesFinanceCharge	X	X
PayablesInvoice	X	X
PayablesMiscellaneousCharge	X	X
PayablesReturn	X	X
PayablesTax	X	X
ReceivablesCreditMemo	X	X
ReceivablesDebitMemo	X	X
ReceivablesDistribution	X	X
ReceivablesFinanceCharge	X	X
ReceivablesInvoice	X	X
ReceivablesReturn	X	X
ReceivablesServiceRepair	X	X
ReceivablesTax	X	X
ReceivablesWarranty	X	X
SalesPayment	X

Back to Entity Categories

Customer And Vendor Entities

Entity	Query	Create	Update	Delete
Customer	X	X	X
CustomerAddress	X	X	X
Vendor	X	X	X
VendorAddresses	X	X	X

Back to Entity Categories

Inventory Entities

Entity	Query	Create	Update	Delete
ItemClass	X
ItemCurrency	X
ItemCurrencyMaster	X	X
ItemMaster	X
ItemPriceList	X
ItemPriceListLine	X
ItemUnitPriceByMethod	X
ItemVendor	X	X
PriceLevel	X
SalesItem	X	X
SalesItemWarehouse	X
SalesItemWarehouseBin	X
UnitOfMeasureSchedule	X
UnitOfMeasureScheduleDetail	X

Back to Entity Categories

Sales Order Entities

Entity	Query	Create	Update	Delete
SalesOrder	X	X	X	X
SalesOrderDetails	X	X		X
SalesOrderDiscounts		X
SalesOrderHistory	X
SalesOrderHistoryLine	X
SalesOrderLine	X	X

Back to Entity Categories

Invoice Entities

Entity	Query	Create	Update	Delete
Invoice	X
InvoiceHistory	X
InvoiceHistoryLine	X
InvoiceLine	X
SalesInvoice	X	X	X	X
SalesInvoiceDetails	X	X		X
SalesInvoiceDiscounts		X
SalesInvoiceLine	X	X		X

Special Operations

Entity	Operations
SalesOrder	-Void

Using Invoice And Inventory Entities

Dynamics GP Connector uses new Entities that support both source and target operations. The new Entities, listed below, supersede their existing counterparts, which had limited, source-only support. TIBCO recommends you begin using the new Entities immediately. The original Entities will remain available temporarily to give you time to update existing Maps. However, they will be dropped in a future release of this Connector.

The original Entities and their replacements are:

Original Entity	Replacement Entity
Invoice	SalesInvoice
InvoiceLine	SalesInvoiceLine
ItemCurrency	ItemCurrencyMaster
ItemMaster	SalesItem

Setup Considerations

The design of Dynamics GP requires that you establish:

• A SQL Server database Connection to use Dynamics GP for Source operations, such as Query and Lookup.
• A Dynamics GP Web Service Connection, as well as a SQL Server Connection, to use Dynamics GP for Target operations, such as Create, Update, Update/Insert, Delete, and Void.

Selecting An Agent Type For Dynamics GP

Refer to TIBCO Scribe® Online Agents for information on available Agent types and how to select the best Agent for your Solution.

Connecting To Dynamics GP

1. From the Connections page select Add to open the Add a New Connection dialog.
2. From the Connections page select Add to open the Add a New Connection dialog.
3. Select the Connector from the drop-down list in the Connection Type field, and then enter the following information for this Connection: 
   • Name — This can be any meaningful name, up to 25 characters.
   • Alias — An alias for this Connection name. The alias is generated from the Connection name, and can be up to 25 characters. The Connection alias can include letters, numbers, and underscores. Spaces and special characters are not accepted. You can change the alias. For more information, see Connection Alias.
   • Server Name — Name of the Dynamics GP Server on which your Dynamics GP Company resides.
   • Database Name —Name of the SQL Server database that contains the data for your Dynamics GP Company.
   • Authentication — Account to use to authenticate the database Connection. Authentication modes are:
     • SQL Server — Select if using a SQL login account.
     • Windows — Select if using a Windows user account.

       For Windows Authentication, the user name must include the fully qualified domain name in one of the following formats:

       • domain\Username
       • Username@domain.com

     Note: For information about which authentication mode to use, see your System Administrator.

   • Login or User Name/Password — The login for SQL Server Authentication or user name for Windows Authentication and password for this Dynamics GP Company.
     

     Note: This user must have dbowner privileges and be a member of the GP Dynamics SQL DYNGRP on both the DYNAMICS database and the Dynamics GP Company database.

4. If you use Create, Update, Update/Insert, or Void operations, select Allow Web Service Target Connections.

   The Dynamics GP Web Service Connection is used to include write operations on your Dynamics GP data. If you do not select Allow Web Service Target Connections, only read Blocks, such as Query and Lookup, display in the palette for your Dynamics GP Connection. To use target operations with the Dynamics GP Connector, Dynamics GP Web Services must be installed and configured for the company into which you are integrating data.

5. If you have enabled Allow Web Service Target Connections, select the Web Service tab and configure the following options:
   • URL — Address of the Dynamics GP Web Service. For example:

     http://<servername>:<portnumber >/Dynamics/GPService/GPService

     Where:

     • servername — Name of the server where the Dynamics GP Web Service is installed.
     • portnumber — Port number for this Web Service. The default is 48620. It may be different for your site.

       Note: See your Dynamics GP Administrator for information about the server name and port number.

       HTTPS Connections are supported only for single tenant installations and require that your Dynamics GP Server and Dynamics GP Web Services be configured for HTTPS. See your Dynamics GP Administrator for more information.

   • User/Password — User name and password of the Dynamics GP Service Host.
   • Company Name — Name of your company as defined in Dynamics GP.
   • Tenant ID — Name of the Dynamics GP Tenant. If multi-tenancy configuration is enabled on the server, this field is required.

     Note: For both single-tenant and multi-tenant Connections, you can only use the account running the Dynamics GP web service for target Connections.

6. Select Test to ensure that the Agent can connect to your database. Be sure to test the Connection against all Agents that use this Connection. See Testing Connections.
7. Select OK/Save to save the Connection.

Metadata Notes

Picklists

String picklist values are case sensitive. For example, for the FinanceCharge_ChoiceType field in the Customer Entity, picklist values must be entered as MoneyAmount or Percent. Picklist values are shown in the field information for a field. See Viewing Field Details.

Updating Related Data

In Dynamics GP, some data is made up of multiple related fields. For example, credit card information is stored in the database as the following three fields with the same prefix:

When updating data in Dynamics GP, if you map one of these related fields, the remaining fields are set to null. To avoid this issue, map all of the related fields

Dynamics GP As A Source

Filtering

When a datetime is configured on the Query Block on the Block Properties Net Change Tab to query for new and updated records, that configuration is treated as an additional filter. The Net Change datetime filter is applied as an AND after any other filters specified on the Block Properties Filter Tab. TIBCO Scribe® Online builds a query combining both the Net Change filter and the filters on the Filter tab. See Net Change And Filters for an example.

Some Connectors for TIBCO Scribe® Online only support one filter. For those Connectors you can use either Net Change or one filter on the Filter tab, not both.

Tracking Last Modified Date

TIBCO provides the following custom fields for the Dynamics GP Connector that you can use with on the Query Block Net Change tab with the Process only records created or updated since last run and the Most Recent Record Processed features to query for records based on the last time they were updated:

• LastModifiedDateTime — Similar to the Dynamics GP ModifiedDate field, but this field also includes date and time used to schedule integrations more frequently than once a day.
• LastModifiedBy — Contains the name of the user who last made changes. Use this field to filter out records changed by the designated Ignore User.

For information about using these fields to determine the most recent record processed, see Block Properties Net Change Tab.

These fields are stored in the Dynamics GP database in the ScribeNetChange table. A new record is created each time a Dynamics GP record is created or modified. Over time, the ScribeNetChange table could grow to be so large that Queries using Net Change time out while scanning the table and attempting to determine which records to include in the query.

To work around this issue, work with your Dynamics GP Database Administrator to remove unnecessary records from the ScribeNetChange table as follows:

• Remove records that are not part of your integrations. For example, if you are not using the Customer entity in any integrations, you can safely remove all Customer records from the ScribeNetChange table.
• Remove records that are no longer required to determine which records have been created or updated but not integrated. For example, if you run your Solutions every day, you could remove records that are more than 30 days old.

These processes could be automated by creating a Map to update the table using the Microsoft SQL Server Connector ensuring that the table remains manageable.

Updating The LastModifiedDateTimeField

If you are currently using the Most recent record processed feature with the Dynamics GP  ModifiedDate field, TIBCO suggests that you update your Maps to use LastModifiedDateTime:

• Depending on the type of Map, select Process only records created or updated since last run on the General tab of the Query Block.
• If the ModifiedDate field is currently specified:
  1. Select ModifiedDate and click Reset.
  2. Copy the date that displays for Most recent record processed.

  

  2. Select LastModifiedDateTime field from the drop-down list.
  3. Select Edit and paste the value you copied back into Most recent record processed:

  

  4. When you next run the Solution, TIBCO Scribe® Online uses the LastModifiedDateTime field to query for the records to update.

  Note: For records that have not been modified, the LastModifiedDateTime defaults to 1/1/1900 12:00 AM in Dynamics GP.

Dynamics GP As A Target

Update Operation

When updating an Entity, required fields are not marked in bold on the Block Properties Fields Tab.

Voiding Sales Orders

For the Connector for Dynamics GP, the Void Block is available when the Dynamics GP Web Service is configured. Use the Void Block to void data from the SalesOrder Entity.

Dynamics GP Web Service

When you use Dynamics GP as a target:

• Security Policies — You should be familiar with Dynamics GP security policies and how to set them from the Dynamics Security Console, as described in the Microsoft Dynamics GP 2013 Web Services Installation and Administration Guide.

  Note: If you do not know how the Dynamics GP Web Service is configured, consult your Dynamics GP System Administrator.

• Error Handling — When Dynamics GP is a target, Web Service errors, such as unhandled exception errors, are recorded in the Dynamics GP Web Service Exception Management Console.

Renaming The Dynamics GP Database

The Dynamics GP Connector uses SQL scripts to install the data views required to perform many source and target operations. Some of these scripts contain a reference to the Dynamics GP application configuration database.

• If you have connected to the source with the Dynamics GP 2013 Connector, use the RemoveGPMetadataObjects.sql script to drop the existing Views, if they exist. This script is located in the ..\Program Files (x86)\Scribe Software\TIBCO Scribe® Online Agent\Connectors\Scribe.Connector.GP folder.
• If you rename the Dynamics GP database, reset the metadata associated with this Connector. See Resetting Metadata.

Integrating Tax Details With AP/AR Systems

When creating an Accounts Payable or Accounts Receivable transaction, the Create Block for the specific transaction generates the document number if the field is left unlinked. However, if the transaction includes tax details, before calling any of the Create Blocks you must:

• Insert the tax details before the payables or receivables Create Block.
• Generate the document number, required by the PayablesTax or ReceivablesTax Entities, using either the GetNextPayablesVoucherNumber or the GetNextReceivablesDocumentNumber Entity:
  1. Create a Lookup Block using the appropriate GetNext Entity for what you are creating, either a payable or a receivable in Dynamics GP.
  2. In the Block’s Lookup Criteria, specify the type of payable or receivable that you are creating.
  3. Use the value returned in either the VoucherNumber field or the DocumentNumber field of the Lookup Block in:
     • Your Tax Detail Create Block.
     • The specific payables or receivables Create Blocks.

Standard Entities

Batch

• When creating a Batch, the Key_ID is truncated at 15 characters.
• If you create a SalesInvoice using a BatchKey_ID that does not exist, a new Batch is created using default Batch data. Batches cannot be updated or deleted using the Dynamics GP Connector.

Customer

Key_Id Fields

When you use the Dynamics GP Customer Entity, TIBCO Scribe® Online enables you to generate and insert a Key_Id field when you insert Customer records into Dynamics GP.

For Create Blocks and Insert operations from an Update/Insert Block, you can either map to the Key_Id field or generate a unique Key_Id by mapping to the following fields:

• Key_Prefix — Specify in the Formula Editor a string to search for in the first part of Key_Id field.
• Key_NumberOfDigits — Specify the length of the numeric portion of the Key_Id field. Make sure that the length is large enough to contain a number for each record in your run. For example, if there are 20 records for the same Company in your Customer database, you must set Key_NumberOfDigits to at least 2. If the number of digits is exceeded, for example higher than SCRIBE99, TIBCO Scribe® Online generates record errors for all of the remaining records.

When a Solution runs, for each record, TIBCO Scribe® Online determines if an existing key meets the Key_Prefix and Key_NumberOfDigits conditions set in their respective formulas:

• If a key exists:
  • Determine the highest number across all keys with the same Prefix, such as SCRIBE001, SCRIBE002, ... SCRIBE763
  • Increment that maximum value of the number by one
  • Concatenate the number with the result of the Key_Prefix formula
  • Insert the customer into Dynamics GP using this key
• If a matching key does not exist:
  • Concatenate the first number in the series, such as 001, onto the result of the Key_Prefix formula
  • Insert the customer into Dynamics GP using this key

GetNextSalesInvoiceNumber And GetNextSalesOrderNumber

If you query a GetNext Entity and you do not use the results in the Create operation for a SalesInvoice or SalesOrder, the following occurs:

• The Query retains the number.
• The retained number is skipped when the SalesInvoice or SalesOrder is created because it is being used by the Query.
• The next number is assigned.

For example, if the Query returns number 405 and you do not use the 405 in the Create operation, the SalesOrder or SalesInvoice record created is assigned number 406, and 405 is never used.

When To Use GetNextSalesInvoiceNumber And GetNextSalesOrderNumber

Dynamics GP automatically assigns a SalesInvoice or SalesOrder a number when a new record is created. You do not need to use a GetNext Entity to get the next number when creating single SalesInvoice or SalesOrder records with associated child records.

If you create SalesInvoice or SalesOrder records with Batch Processing enabled, the record number generated by the Create operation is not available in the operation results when adding associated SalesInvoiceDetails and SalesOrderDetails child records. Use a GetNext Entity to get the next number and use it to associate the SalesInvoiceDetails and SalesOrderDetails child records with the correct SalesInvoice or SalesOrder parent record.

SalesInvoice

• When you update a SalesInvoice record, the following fields are required:
  • Batch_Key_ID
  • Date — Requires a valid future date or errors are generated.
• Create or Update SalesInvoiceLine — A Dynamics GPSalesItem typically has a default tax schedule set. If a tax schedule is set in the SalesInvoiceLine Entity, but there is no default tax schedule set for the SalesItem itself, taxes are not calculated for the SalesInvoice.
• If a SalesInvoice has an associated default tax schedule, that tax schedule is used for the entire invoice. Tax schedules associated with a SalesItem or SalesInvoiceLine records are ignored.
• The Update operation does not support updating the child Entity SalesInvoiceDetails, when updating the parent SalesInvoice Entity. Delete the SalesInvoiceDetails record and create it again.

SalesOrder

• Create or Update SalesOrderLine — A Dynamics GPSalesItem typically has a default tax schedule set. If a tax schedule is set in the SalesOrderLine Entity, but there is no default tax schedule set for the SalesItem itself, taxes are not calculated for the SalesOrder.
• The Update operation does not support updating the child Entity SalesOrderDetails, when updating the parent SalesOrder Entity. Delete the SalesOrderDetails record and create it again.

TIBCO Scribe® Online API Considerations

To create Connections with the TIBCO Scribe® Online API, the Microsoft Dynamics GP Connector requires the following information:

Connector Name	Microsoft Dynamics GP
Connector ID	B5743AF6-3EC6-4C5F-8EBA-A41CEAC837D1

TIBCO Scribe® Online Connection Properties

In addition, this Connector uses the Connection properties shown in the following table.

Name	Data Type	Required	Secured	Usage
DatabaseServerName	String	Yes	No	GP Database server Name
DatabaseName	String	Yes	No	GP Company Database Name
DatabaseAuthentication	String	Yes	No	Auth Type
DatabaseUserName	String	Yes	No	SQL username or active directory user name with access to the company database.
DatabasePassword	String	Yes	No	SQL or active directory password.
EnableTargetConnection	Bool	Yes	No	True if target operations are required, and Dynamics GP web service is installed and available.
WebserviceUrl	String	No	No	URL for the Dynamics GP web service
WebserviceUserName	String	No	No	Active directory user name granted web service access rights. This must be the user running the web service.
WebservicePassword	String	No	No	Active directory password.
CompanyName	String	No	No	Name of the company to connect to.
TenantId	String	No	No	Tenant ID Required on Dynamics GP web servers configured for multi-tenant.

More Information

For additional information on this Connector, refer to the Knowledge Base and Discussions in the TIBCO Community.