Shared Transactions Setting up the Allocation Validation Query
USE CASE: Especially when applying templates or 1:1 allocations to transactions in mass, either via CSV import or 3rd party integration, it is important to ensure that no errors are stopping the allocation from processing. Errors, such as incorrect segment combinations, e.g. specific departments not being enabled in certain subsidiaries, can stop the transaction from being allocated. Luckily, Shared Transactions has the ability to create and run custom queries, to catch these kinds of errors. Below you can find instructions on how to set up two custom queries. We recommend Shared Transactions users run these queries monthly to catch transactions that are missing their allocations. Once the transactions are identified, users can fix the issues, and in turn allow the transactions to allocate.
Before you can run these queries, they have to be configured. Please follow the steps below to setup your two custom Shared Transactions queries. Once the setup process is complete, follow the instructions in this article to learn how to run and better understand the results of the queries.
ONE-TIME SETUP PROCESS:
- First, ensure your users have the correct permissions to view and edit dashboards by granting them permission to the Custom Record permission NetInsight Dashboard
- For more information on the other Shared Transaction's permissions, please refer to this article
- Next, navigate to Netgain > Setup > Manage NetInsight Dashboards > New
- Once the new "NetInsight Dashboard" record loads, fill in the following required fields:
| Field Name | Example | Details |
| Name | NetInsight | Shared Transactions | Enter the name you would like to give this dashboard |
| Dashboard Group | NetInsight | Executive Dashboard | Select the dashboard group you would like to add your dashboard to. Choose any from the dropdown. |
| Row 2 - Container 1 Label | Transactions Missing Allocations | Name your first query |
| Row 2 - Container 1 SQL | SELECT transaction.tranid, transaction.type, transaction.trandate, period.periodName FROM transaction JOIN TransactionLine ON (TransactionLine.Transaction = transaction.ID) LEFT OUTER JOIN transaction as allocationTransaction ON transaction.id = allocationTransaction.custbody_nta_allocation_source Join accountingperiod as period on transaction.postingperiod = period.id WHERE ( transaction.custbody_nta_allocation_template_body IS NOT NULL Or TransactionLine.custcol_nta_allocation_template_line IS NOT NULL Or TransactionLine.custcol_nta_allocation_location IS NOT NULL Or TransactionLine.custcol_nta_allocation_department IS NOT NULL Or TransactionLine.custcol_nta_allocation_class IS NOT NULL Or TransactionLine.custcol_nta_allocation_subsidiary IS NOT NULL ) AND allocationTransaction.ID IS NULL GROUP BY transaction.id, transaction.tranid, transaction.type, period.periodName, transaction.trandate ORDER BY transaction.trandate DESC | This is the SQL code driving your query |
| Row 2 - Container 1 Type | Table | Indicate how the data should be displayed |
| Row 3 - Container 3 Label | Missing Allocations - Transaction Summary | Name your second query |
| Row 3 - Container 3 SQL | SELECT count (distinct transaction.id) as Transaction_Count, transaction.type, BUILTIN.DF(Transaction.approvalStatus) as Approval_Status, BUILTIN.DF(Transaction.Status) as Status FROM transaction JOIN TransactionLine ON (TransactionLine.Transaction = transaction.ID) LEFT OUTER JOIN transaction as allocationTransaction ON transaction.id = allocationTransaction.custbody_nta_allocation_source Join accountingperiod as period on transaction.postingperiod = period.id WHERE ( transaction.custbody_nta_allocation_template_body IS NOT NULL Or TransactionLine.custcol_nta_allocation_template_line IS NOT NULL Or TransactionLine.custcol_nta_allocation_location IS NOT NULL Or TransactionLine.custcol_nta_allocation_department IS NOT NULL Or TransactionLine.custcol_nta_allocation_class IS NOT NULL Or TransactionLine.custcol_nta_allocation_subsidiary IS NOT NULL ) AND allocationTransaction.ID IS NULL GROUP BY transaction.type, BUILTIN.DF(Transaction.approvalStatus), BUILTIN.DF(Transaction.Status) ORDER BY transaction.type | This is the SQL code driving your query |
| Row 3 - Container 3 Type | Table | Indicate how the data should be displayed |
- Once these fields have been configured, save your changes
- Read this article to learn how to run your new query
Custom Transaction Line Fields:
If you configured custom transaction line fields as part of your Shared Transactions implementation, please update the queries to include the transaction column IDs of those custom transaction line fields. This is to ensure all allocation fields are captured by the queries. If these are not added, your queries will not display accurate information.
Custom transaction line fields are commonly found in 3rd party integrations with Shared Transactions, or when custom sourcing has been configured for allocations based on custom segments.
3rd Party Integration
If custom transaction line fields were created as part of an integration with a 3rd party, and the integration was configured according to this article, you will need to add the following lines to the WHERE section of your queries:
Or TransactionLine.custcol_nta_ng_location IS NOT NULL
Or TransactionLine.custcol_nta_ng_department IS NOT NULL
Or TransactionLine.custcol_nta_ng_class IS NOT NULL
Or TransactionLine.custcol_nta_ng_subsidiary IS NOT NULL
Custom Sourcing
If you configured custom sourcing as part of your Shared Transactions implementation in order to allocate transaction based on custom segments, your queries will need to include these custom transaction line fields as well. For every custom transaction line field you created, add the following line to your queries' WHERE section, where XYZ represented the transaction column ID:
Or TransactionLine.custcol_XYZ IS NOT NULL