Summary
This release summarizes the customization which encompass the redirection of account posted in GL to some target account based on user discretion.
Custom record setup and field creation
CUSTOM RECORDS SCHEMA/GUIDE:
1. Inbound-Outbound Account Map
DESCRIPTION
ID
TYPE
LIST/RECORD
MANDATORY
custrecord_grw_016_rule_id
Integer Number
Yes
custrecord_grw_016_subsidiary
List/Record
Subsidiary
Yes
custrecord_grw_016_inbound_tran_type
List/Record
Transaction Type
Yes
custrecord_grw_016_outbound_tran_type
List/Record
Transaction Type
Yes
custrecord_grw_016_trigger_account
List/Record
Account
Yes
custrecord_grw_016_var_target_acc
List/Record
Account
Yes
custrecord_grw_016_var_dept
List/Record
Cost Center
Yes
custrecord_grw_016_var_class
List/Record
Class
Yes
custrecord_grw_016_var_loc
List/Record
Location
Yes
2. Target Account Weight Allocation
DESCRIPTION
ID
TYPE
LIST/RECORD
MANDATORY
custrecord_grw_016_parent
List/Record
Inbound-Outbound Account Map
No
custrecord_grw_016_select_account
List/Record
Account
Yes
custrecord_grw_016_debit_or_credit_type
List/Record
Debit/Credit Type
Yes
custrecord_grw_016_weight
Decimal Number
Yes
custrecord_grw_016_memo
Free-Form Text
Yes
custrecord_grw_016_tax_code
List/Record
Tax Code
Yes
custrecord_grw_016_tax_target_account
List/Record
Account
Yes
custrecord_grw_016_department
List/Record
Cost Center
Yes
custrecord_grw_016_class
List/Record
Class
Yes
custrecord_grw_016_location
List/Record
Location
Yes
custrecord_grw_016_cs_segment_1_type_id
Free-Form Text
No
Custom Segment 1 – Selection Type
custrecord_grw_016_cs_segment_1_type
List/Record
Custom Segment Selection Type
No
custrecord_grw_016_cs_segment_1
Free-Form Text
No
custrecord_grw_016_cs_segment_2_type_id
Free-Form Text
No
Custom Segment 2 – Selection Type
custrecord_grw_016_cs_segment_2_type
List/Record
Custom Segment Selection Type
No
custrecord_grw_016_cs_segment_2
Free-Form Text
No
custrecord_grw_016_cs_segment_3_type_id
Free-Form Text
No
Custom Segment 3 – Selection Type
custrecord_grw_016_cs_segment_3_type
List/Record
Custom Segment Selection Type
No
custrecord_grw_016_cs_segment_3
Free-Form Text
No
custrecord_grw_016_cs_segment_4_type_id
Free-Form Text
No
Custom Segment 4 – Selection Type
custrecord_grw_016_cs_segment_4_type
List/Record
Custom Segment Selection Type
No
custrecord_grw_016_cs_segment_4
Free-Form Text
No
custrecord_grw_016_cs_segment_5_type_id
Free-Form Text
No
Custom Segment 5 – Selection Type
custrecord_grw_016_cs_segment_5_type
List/Record
Custom Segment Selection Type
No
custrecord_grw_016_cs_segment_5
Free-Form Text
No
DESCRIPTION
ID
TYPE
LIST/RECORD
MANDATORY
custrecord_grw_016_inb_tran_link
List/Record
Transaction
Yes
custrecord_grw_016_error_code
Text Area
Yes
TRANSACTION FIELDS:
FIELDS
ID
TYPE
LIST
TAB
custbody_grw_016_created_from_ib_tran
List/Record
Transaction
Related Records
custbody_grw_016_gl_query
Free-Form Text
Custom
2. Data population and Data Validations In Custom Record
In the custom record we will add a client-side script which will perform the weight validations in the sublist and drive the custom segment selection options.
Additionally, there will be a server-side script. This is for dynamic addition of custom segments. Account Admin must enter script parameter values – insert the values of the source list ids (RECORD IDs) of the custom segments. Based on the number of parameters field, we will add that number of custom segment virtual (script) column field sets (selection type and segment selection pairs) in the custom record. The drawback of the virtual field is that we can only access these set of fields in the edit context. So, in order record the values entered we will add a static standard column field (free-form text type) sets of same number as virtual fields, to register and show the entered custom segment combinations in view mode.
Testing Steps:
- Navigate to Customization –> Lists, Records & Fields –> Record Types.
- Open Inbound-Outbound Account Map Custom record
- Inbound-Outbound Account Map
1: When the specific value is selected and the corresponding value is not selected, then the alert will be displayed when the line is committed.
2. If a value is selected for the Inherit from Outbound, then value from the corresponding segment field will be cleared
3: If the Debit and credit percentage values are not balanced alert will be displayed and will restrict save of the record.
- When the record is saved after correcting values.
- On view of the record, table will be displayed displaying the values.
- On clicking the view link, will open the record
- On Edit of the custom record, standard child record sublist will be open.
- On view mode of the page, an HTML table will be visible with required data instead of sublist.
Notes:
- Values are added to segment columns only if the segment selection type is specific value, otherwise value will get cleared in segment column.
- Only considering scenarios when adding sublist values through parent record : Inbound Outbound Account Map record.
- No other validations are added to the custom record or custom record fields other than mentioned in above scenarios..
- Segment searches are performed based on the script parameter value added to the user event script GRW_016 UE In Out Cust Segment Setting. Results of the saved search : Custom Segment Search Grw_016 will be used for retrieving the segments that is to be added in the custom record.
3. Transaction Posting & Journal Entry automation
- Create/Edit an item Receipt (inbound transaction record).
- Go through Actions > GL Impact.
- Refer the GL Impact of the transaction – For each posted credit and debit accounts in the inbound transaction, it will find the matching custom record comparing the following fields and adds up as credit and debit line in a Journal.
- Subsidiary,
- Inbound Record Type and
- Trigger account
- Inbound transaction will be linked to a custom field “INBOUND TRANSACTION #” on the Journal thus created.
- To register the latest GL impact query results to a custom field in the Journal thus created, so that we can compare the recent changes occurred in the inbound transaction with the previous. (To overcome Charactor count limitation, we will store that as files and so the file id will be set to the field as shown below.)
- The journal thus created will be explicitly based on the matching custom record schema. Entity will be taken from the inbound recor. However for the Tax and its calculations it will be foresynched with Netsuite standard behaviour ie. Entity, Memo, Tax etc.
Note:
- Since there is amount rounding – which is fixed to 2 decimal places, in the Debit and credit amount lines, most of the time the Journal become not balanced. Thus a variance line will be added additionally, for negating the difference between credit and debit. For applying this variance amount, the following fields in the custom record will be taken:
- Variance Account – for choosing a specific account. We use this account – “Rounding Gain/Loss” for variance.
- Variance Department – for choosing a specific Department.
- Variance Location – for choosing a specific Location.
- Variance Class – for choosing a specific Class.
- Other fields will be left as blank.
- And Note – We will only add variance up to 0.10
Once the Journal entry is created, it needs to go for the custom approval routing. We will consider this part in phase 2.
So, now the Journal Created will have the default status. And upon copy action we will clear all the reference fields that are populated as part of this customization.
4. Error Handling on Journal Automation
- If Journal Creation fails, as a fall back we can create custom record for tracking errors.
- If it is a failure, it will hold the corresponding Inbound transaction link and the error code.
- Also, we will create a saved search, which will list the journals with the Inbound Transaction field filled. This can be used for reporting purpose of successful entries.
Assumptions
The proposed solution is based on the following assumptions:
- We don’t need to apply any filtering to the standard lists sourced in the custom record fields. User should setup all entries in the record with much attention on all the factors/dependencies.
- Any changes made to the custom records, will not be updated to the earlier generated journals.
- In this we have only considered IR. That too not voided and Closed ones.
- Later we will check what all other posting transitions and has to extend the script.
Risks
- User Event script governance limit – 1,000 usage units per script. Script will fail if the governance limit exceeds.
- User should enter values of accounts weights/percentage via sublist only.
- If any Journal line count limit is set for the account, auto-journal creation will fail if the limit exceeds.
- Mandatory fields in Journal should be mandatorily filled in custom record as well. If any compulsory fields in Journal Specifically related to the Credit or debit lines, should not be missing in the custom record (Select Account, Debit/Credit, percentage etc should be filled). Otherwise, Journal Entry Creation will fail.
- Journal Creation script will fail creating journal If any standard validations in Journal is not satisfied. So, user should carefully enter values to the custom record and its Soley user risk.
- Once a Journal Entry is created, then if any edits made to the transaction, then script must delete and recreate a new Journal Entry for the new GL impact. But this will lose the document number sequencing. Closed period Journals cannot be deleted/updated.
- There should be one-one mapping, which means no duplicate custom record entries should be there for the same “Trigger Account”.
- In the custom record type set up, user will define those fields that need to be mandatory/non-mandatory. Also they will add new fields directly via the custom record setup. There won’t be any automations in adding new fields also. Relatively script need to be updated.