How can I add custom accounting rules to NorthScope?

Owned by Janice Dannenberg (Unlicensed)
Last updated: Nov 19, 2020 by Emma Greenwood
4 min read

NorthScope has the ability to recognize and execute custom logic for accounting rules. Using SQL code, NorthScope can handle complicated logic that our UI would otherwise not support.

The journal entry posting process runs each time we post a transaction or open the Journal Details window. We’ve added code inside this process to detect if a specific custom stored procedure exists. The custom stored procedure should be named CSPx_JournalDetailCustomAccountingRule and once created, can be used to add accounting rules, as it will interact with the existing posting process to change the accounts being used based on the specific requirements. For example, you might want to replace a segment of the GL Account with the ship from site ID so that your sales will post to site specific GL accounts.

The following columns will be returned from the FNx_GetTransactionAccounts() function:

  • TransactionHeaderSK: The unique identifier for the transaction.

  • TransactionLineSK: The unique identifier for each line item on the transaction.

  • ExtendedParameterType: Any additional effect on the General Ledger outside of the transaction effect, such as Manual Freight, Sales Program type, Grower Accounting Charge, Grower Accounting Accrual, Company Tax.

  • ExtendedParameterSK: The unique identifier of the Extended Parameter Type.

  • HeaderSettlementAccrualAccount: Identifies the control account of the transaction such as Fisherman Sale, Tender Resale, Fisherman Accounting Payment Receipt.

  • HeaderAccountsPayableAccount: Identifies the Control account of all transactions involved in Purchasing, such as Invoices, Credit Memo, Grower Accounting Tickets and any discounts associated with Invoices and Credit Memos.

  • LineSettlementAccrualAccount: Identifies the Fisherman settlement accrual account that is distributed on line items in transactions such as Invoices, Credit Memos and Transfer To Fisherman.

  • FromLineSettlementAccrualAccount: Identifies the settlement accrual account that is used on Transfer transactions.

  • HeaderCheckbookAccount: Identifies the checkbook cash account used on payment transactions such as checks and ACH.

  • CashCheckbookAccount: Identifies the checkbook cash account that is used on Employee Sale transactions where immediate payments are made via cash (no credit offered).

  • CheckCheckbookAccount: Identifies the checkbook cash account that is used on Employee Sale transactions where immediate payments are made via check (no credit offered).

  • CardCheckbookAccount: Identifies the checkbook cash account that is used on Employee Sale transactions where immediate payments are made via credit card (no credit offered).

  • HeaderAccountsReceivableAccount: Identifies the Control account of all transactions involved in Sales Orders, Sales Returns, Price Adjustments, Quick Sales and Discounts and Write Offs associated with applications.

  • LineAccountsReceivableAccount: This is the quick reference to the Control account for any extended parameter on a Sales Order or when distributing to an Employee on an AP Invoice.

  • LineAccount: Identifies the account associated with a transaction line item for all transactions within NorthScope. Each transaction type retrieves a different account based on the transaction type, for example, AP uses the Purchasing account.

  • TaxAccount: Identifies the account for tax expenses associated with transactions such as Quick Sales, Fish Tickets, Fisherman Sales and Tender Resales.

  • PremiumAccount: Identifies the account for premiums associated with Fish Ticket transactions.

  • ChargeAccount: Identifies the account used for fees associated with the cost of Harvesting, Hauling, dockage, and damaged/down grade product used on Grower Accounting tickets as Charges.

  • DebitAccrualAccount: Identifies the account used for accrued expenses used on transactions such as Sales Programs and Grower Accounting tickets.

  • CreditAccrualAccount: Identifies the account used for accrued liabilities used on transactions such as Sales Programs and Grower Accounting tickets.

  • ExtendedParameterExpenseAccount: Identifies any additional expenses that is needed for the transaction such as, FreightExpense, Sales Programs, Fish Ticket Taxes & Premiums.

  • ExtendedParameterAccrualAccount: Identifies any additional accruals that is needed for the transaction such as, FreightAccrual, Sales Programs, Fish Ticket Taxes & Premiums.

  • LineRevenueDebitAccount: Deprecated.

  • LineRevenueCreditAccount: Identifies the Items Freight Revenue account.

  • LineWriteoffAccount: Identifies the account used for non-cash expenses where amounts are written off and used when applying or settling transactions, such as Sales Programs and applying Payment Receipts to an order for example.

Below is an example of how the stored procedure CSPx_JournalDetailCustomAccountingRule can be used to handle this scenario. Please note that while this approach can be very flexible and helpful, it also can be dangerous so testing is always strongly encouraged and only qualified users should be given rights to make these types of changes.

Some technical knowledge of NorthScope and the various NS transaction types are required to use this feature and NLP staff can assist with this process.

 

CREATE PROCEDURE CSPx_JournalDetailCustomAccountingRule AS BEGIN SET TRAN ISOLATION LEVEL READ UNCOMMITTED /*First test to make sure the table we expect exists and was created by the parent account stored procedure.*/ IF OBJECT_ID('tempdb..#TMP_Accounts') IS NOT NULL BEGIN /* Update our account table by looking up related information for our sales order lines and replacing the last segment of the GLAccount with the Site ID for the site we are shipping from. This assumes a GL Account format of XXXX-XXX-XX We use the STUFF function to replace the last segment with the SiteID. */ UPDATE #tmp_Accounts SET LineAccount = GLA2.AccountSK FROM #tmp_Accounts a INNER JOIN dbo.ERPx_SOOrderItem OI ON OI.OrderItemSK = a.TransactionLineSK INNER JOIN dbo.ERPx_LMLoadTransactionItem LTI ON LTI.OrderItemSK = OI.OrderItemSK AND LTI.TransactionModuleSK = 4 /*Sales Order*/ INNER JOIN dbo.ERPx_LMLoadHeader LH ON LH.LoadHeaderSK = LTI.LoadHeaderSK INNER JOIN dbo.ERPx_MFSite S ON S.SiteSK = LH.SiteSk INNER JOIN dbo.ERPx_GLAccount GLA ON GLA.AccountSK = a.LineAccount INNER JOIN dbo.ERPx_GLAccount GLA2 ON GLA2.AccountNumber = STUFF(GLA.AccountNumber, 10, 2, S.SiteID) WHERE a.ModuleSK = 4 /*Sales*/ AND TransactionTypeSK = 4; /*Order*/ END; END

 

/*Reference for table structure of #TMP_Accounts*/ CREATE TABLE [#tmp_Accounts] ( [TransactionHeaderSK] BIGINT, [TransactionLineSK] BIGINT, [ExtendedParameterType] VARCHAR(100), [ExtendedParameterSK] BIGINT, [TransactionTypeSK] BIGINT, [ModuleSK] BIGINT, [HeaderSettlementAccrualAccount] BIGINT, [HeaderAccountsPayableAccount] BIGINT, [LineSettlementAccrualAccount] BIGINT, [FromLineSettlementAccrualAccount] BIGINT, [HeaderCheckbookAccount] BIGINT, [CashCheckbookAccount] BIGINT, [CheckCheckbookAccount] BIGINT, [CardCheckbookAccount] BIGINT, [HeaderAccountsReceivableAccount] BIGINT, [LineAccountsReceivableAccount] BIGINT, [LineAccount] BIGINT, [TaxAccount] BIGINT, [PremiumAccount] BIGINT, [ChargeAccount] BIGINT, [DebitAccrualAccount] BIGINT, [CreditAccrualAccount] BIGINT, [ExtendedParameterExpenseAccount] BIGINT, [ExtendedParameterAccrualAccount] BIGINT, [LineRevenueDebitAccount] BIGINT, [LineDiscountAccount], [LineRevenueCreditAccount] BIGINT, [LineWriteoffAccount] BIGINT )