Shared Transactions Allocation Validation Query

Overview

When you apply templates or 1:1 allocations in bulk - through CSV import or a 3rd party integration - a single error can quietly stop a transaction from allocating. The most common cause is an invalid segment combination, such as a department that isn't enabled in the subsidiary you're allocating to.

Shared Transactions lets you build custom queries to catch these transactions before they slip through. This article covers two: one that lists every transaction missing its allocation, and one that summarizes them by type and status. You set them up once, then run them monthly - once a transaction turns up, fix the underlying error and it will allocate.

Prerequisites

Custom Dashboards must be enabled on your NetInsight license.
1. To check if it's enabled, navigate to Netgain > Setup > System Setup > NetInsight License > Custom Dashboards. If not enabled, reach out to Netgain Support (support@netgain.tech) to request it be enabled.
Ensure your users have the correct permissions to view and edit dashboards by granting them the Custom Record permission NetInsight Dashboard.
For more information on the other Shared Transactions permissions, please refer to this article.

One-time setup: configuring the queries

Before you can run these queries, they have to be configured. Follow the steps below to set up your two custom Shared Transactions queries.

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

3. Once these fields have been configured, save your changes.

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 transactions 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 represents the transaction column ID:

Or TransactionLine.custcol_XYZ IS NOT NULL

Running the queries

Once setup is complete, run the queries each month as follows:

Navigate to Netgain > Dashboards and open the Dashboard you saved your query to during setup.
Find and select your query from the list of queries on the left-hand side of the screen.
If configured correctly, you should now see two separate queries:

Transactions Missing Allocations query

This query shows a list of all transactions where any Shared Transactions fields have been filled out, but an allocation is missing
Users should work through the list of these transactions and investigate why the allocation did not occur
The most common reasons why allocations do not occur are the following:
1. The transaction is pending approval (allocation journals do not run until the source transaction is approved)
2. The segmentation combination of the allocation is incorrect, e.g. specific departments not being enabled in certain subsidiaries (Please note that when entered in the NetSuite interface, Shared Transactions will issue a warning when there is an incorrect segment combination. However, when allocation information is entered on a 3rd party's interface, Netgain cannot guarantee that the 3rd party will issue warning for incorrect segment combinations.)
Users can also make use of the "System Information" subtab on the transaction record, which can offer insight into why the allocation failed

Missing Allocations - Transaction Summary query

This query summarizes the transactions missing allocations by grouping them by transaction type, approval status, and transaction status
By reviewing this query, users can better understand and identify the most common occurrences and groups of transactions not allocating

Note

Certain transactions by themselves, such as purchase orders and item receipts, will not trigger a Shared Transactions allocation journal. This is because these transaction types do not post an amount to your ledger, so there is no GL impact to allocate. However, Shared Transactions still allows you to fill out the Shared Transactions fields on purchase orders. This is so that when the bill is created from the item receipt, the allocation information can automatically flow from the purchase order to the bill. The allocation journal will then be tagged to and visible on the bill.

Was this article helpful?