Background
If you were using Recurrency for our reporting product and are now using us for our Demand Planning or Purchasing Automation products, you'll need to update the dedicated Recurrency database user to have write permissions
💡 Direct SQL database access: this guide assumes that you have direct access to the SQL server hosting your ERP data. If you are relying on a third party to manage your Prophet 21 instance, you may need their assistance with these steps.
If you are using Epicor’s cloud hosted P21 solution, we have a different version of this guide which will be more helpful. Additionally, if you are using a public or private cloud to host your Prophet 21 instance, there may be alternative ways to establish a secure connection between our systems. Reach out to [email protected] if you have questions.
Integration Steps
The integration requires two piecs:
Updating the Recurrency SQL user account to have write permissions to your database
Enabling Change Tracking to improve integration performance and stability
Upgrading to Write Access
With an Admin account, login to your P21 MSSQL database
In the P21 database (i.e not the master database), run the following query:
-- grant write access
exec sp_addrolemember 'db_datawriter', 'recurrencyuser';
GRANT EXECUTE TO recurrencyuser;
You should see a success message.
To confirm that the user has the right permissions, you can run the following query:
SELECT
pr.principal_id,
pr.name AS UserName,
pr.type_desc AS PrincipalType,
pe.permission_name, pe.state_desc AS PermissionState,
OBJECT_NAME(pe.major_id) AS ObjectName
FROM sys.database_principals pr
LEFT JOIN sys.database_permissions pe
ON pr.principal_id = pe.grantee_principal_id
WHERE pr.name = 'recurrencyuser';
This will return results like the following:
database CONNECT
database SELECT
database INSERT
database UPDATE
database DELETE
database EXECUTE
Enabling Change Tracking
For full details on change tracking and why we recommend enabling it, you can review our full article.
In summary, Recurrency's integration by default relies on P21 to correctly make updates to date_last_modified values across the database. It is not uncommon for P21 to not update this value which prevents Recurrency from detecting a change. This means users can make suboptimal decisions off of stale data.
To prevent this, we recommend enabling Change Tracking, which is a Microsoft supported feature at the database level. Change Tracking eliminates nearly all sync issues we have seen with our P21 integrations.
To be clear, if you chose not to enable Change Tracking, you are much more likely to run into scenarios where your data is out of sync with Recurrency. Debugging these sync issues is time consuming and frustrating for both you and for us. In most cases where we have these issues, our answer and recommendation will be to enable Change Tracking.
This is a highly recommended and encouraged configuration step. |
Doing this requires running the following query to enable Change Tracking for a specific subset of tables that we have found are the most critical and often to get out of sync due to P21 not correctly maintaining a date_last_modified value.
-- enable CT on the database
ALTER DATABASE {database_name} SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON);
-- Enable CT for a specific set of tables for Recurrency sync
ALTER TABLE invoice_hdr_salesrep ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE oe_hdr_salesrep ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE inv_period_usage ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE inventory_supplier_x_loc ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE item_lead_time ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE invoice_line ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE inv_loc ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE oe_line ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE invoice_hdr ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE oe_pick_ticket_detail ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE oe_hdr ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE oe_pick_ticket ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
ALTER TABLE ship_to_salesrep ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);