Automate Loan Account Entries using Entity Substitution Tutorial

In this tutorial, we will configure Inter-Entity Transactions between several Sage 300 databases using Entity Substitution, where the target entity is selected in an optional field.

Users select the optional field when processing the transaction (in the sub-ledger and flows to the G/L, or directly in the G/L).

This is used when entities are separate company databases and the Expense accounts for the target entities do not exist in the source database.

We will process an AP Invoice entered in ORCLTD, 22% to be allocated to Entity 1, 33% to be allocated to Entity 2, and 44% to be allocated to Entity 3.

When the relevant G/L batch is posted in ORCLTD, Inter-Entity transactions automatically creates the loan account entries between Entity 1 and Entity 2 and Entity 1 and Entity 3.

Note: Only required where you have many entities (e.g. 50) and many expense / revenue accounts (e.g. 100), and you do not want to have all the target expense / revenue accounts in the source database (i.e. in this example 5,000 additional accounts)

Requirement! Inter-Entity Transactions Multi Edition

Scenario

The entry we want to process in ORCLTD

Entry 1: We will enter an A/P Invoice for $900 - $200 to an expense account for Entity 1, $300 for Entity 2 by selecting an optional field and $400 for an expense account for Entity 3 (by selecting an optional field).

Entry 2: Inter-Entity substitutes the optional field and creates entries in the Clearing accounts

Entry 3: Inter-Entity Transactions balances the entities

Entry 4: Inter-Entity Reverses those transactions which belong to other databases (if auto-reversal is selected in IET Options)

Entry 5 and 6: Inter-Entity creates those transactions which belong to other databases.

 

Expense - 1
1. AP Inv Entity 1 $200
   
1. AP Inv Entity 2 $300
2. IET Substitution $300
1. AP Inv Entity 3 $400
2. IET Substitution $400

 

 

AP Vendor Control Account
   
1. AP Invoice $900

 

  Clearing- 2
 
2. IET Substitution $300
4, IET Reversal

$300

 

  Clearing- 3
 
2. IET Substitution $400
4. IET Reversal

$400

 

 

 
 
4. IET Reversal $300
3. Due to 1 $300

 

Due From Entity 2 (-1 asset)
3. Due From 2 $300
   

 

 
 
4. IET Reversal $400
3. Due to 1 $400

 

Due From Entity 3 (-1 asset)
3. Due From 3 $400
   

 

In the target entities

In Entity 2 (ORCIN2)

 

 

  Expense - 2
 
4. AP Invoice $300
   

 

 
 
   
4. Due to 1 $300

 

 

In Entity 3 (ORCIN3)

 

 

  Expense - 3
 
5. AP Invoice $400
   

 

 
 
   
5. Due to 1 $400

 

Visual process flow for setup

Tip: Assign the Visual Process Flow in Administrative Services

General Ledger Setup

This has been done in the ORCLTD, ORCIN2 and ORCIN3 databases in Orchid Sample data.

Create a G/L segment to define the entity

Setting up the entities in G/L > Segment Codes

Set up the loan accounts in G/L in the source and the target databases

In this example, Due to 3 and Due from 3, Due to 2 and Due from 2 in the source database (ORCLTD) 

Due to 1 and Due from 1 in the target databases (ORCIN2 and ORCIN3)

Note: You don't need to create all Income statement accounts for the target entities.

Set up the source codes in G/L

In this tutorial, AP-IY will be used for transactions generated by Inter-Entity Transactions, AP-IR for Reversal entries, AP-IS for substitution entries.

Optional Fields Setup

You need to configure an Optional Field in Common Services to define the entity the transactions relate to. Then configure the optional fields in the relevant sub-ledgers in the source company and in the General Ledger as a Transaction Optional Field.

 

Inter - Entity Options Setup

For details on each option, refer to the detailed screen guide. Inter-Entity Options Screen Guide and Field List.

Tip: You only need to activate Inter-Entity in the companies where Source transactions are entered. In this example, ORCLTD.

Define the Entity segment/s to be used by Inter-Entity

In this tutorial, the Entity segment is segment 4 and Entity 1 is the default entity.

Default Entity is used for complex transactions, where Inter-Entity cannot determine which route(s) to use. In cases like this balancing takes place via the default entity.

Inter-Entity Options > Processing Tab

When entities are in separate Sage 300 database, you need to tick "Use Inter-Entity Processing" and "Allow Inter-database processing" and select a Source Type for the entries created by Inter-Entity and for reversal entries. In most cases, select "Perform auto-reversals".

For details on other configuration options, refer to the detailed screen guide. Inter-Entity Options Screen Guide and Field List.

Tick "Use IET Triggers" and select the source type and optional field defining the entity.

Define the Transactions Routes

Define one route between 1 and 2 and one between 1 and 3.

You only need to define one route between 1 and 2 or 2 and 1, not both.

Define your linked accounts

This is only required to run the IET Account Balance Inquiry and to populate mirror accounts in the Transaction Routes screen. It isn't used for ongoing transaction processing.

Define Entity database mapping

This is used to configure log on details to create transactions in target databases.

Configure G/L Trigger - Entity Substitution

Select one or a range of accounts that are substitutable, the related account in the target entities and the transaction optional field that will define the entity.

Tip: The target entity in the G/L Trigger setup screen is an example and will be replaced by the value of the optional field when transactions are processed by Inter-Entity.

Example:

In the example below, all accounts starting with 6 can be substituted with an optional field called SUBSTENTITY.

Process transactions

Enter an A/P Invoice and allocate the expense to 3 journal lines for an Expense account starting with 6.

Leave the first one with Entity 1 - Leave the optional field default value to 1

select Entity 2 in the 2nd line Optional field SUBSTENTITY and Entity 3 for the 3rd line.

Tip: Use Orchid Extender to display and edit the optional fields in the grid. This requires a licence for Extender Deverlop separate from Inter-Entity License.

What it looks like in the General Ledger

When you post the G/L batch created by the sub-ledger, Inter-Entity creates the Reversal entries, and Due to and Due from entries with the AP-IY Source code in the target databases. The Inter-Entity Audit log is populated with details of the transactions created.

Note: If the transaction does not balance by entity and a G/L Error batch is created, review the G/L Posting Journal > Posting journal errors for details, amend the error batch created by G/L and post again.
Note: If Inter-Entity cannot create the entries in the target databases, detailed error message can be found in the IET Audit Inquiry.

In the target database (Entity 2 )

IET Audit Inquiry Inquiry buttonInquiry button

Tip: If you want to see what Inter-Entity will do with a transactions but you don't want to post it, then go to Inter-Entity Transactions > IET Transactions > Generate IET Entries, select the range or batches you would like to check and click Process. Inter-Entity Transactions will create the balancing entries for you but won't post the batches. Great for testing.