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:

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
)