Customizing Your Report Schema
Tax Reports need two files to be completed for you to be able to generate your report using Tax Reporting Framework.
The following are the files you need to customize:
-
Summary file: TAF_SEARCH.json or TAF_SQUITEQL.json. This contains attributes that would determine how TRF handles your main report, whether the report is VAT, TAF, ESL, Intrastat or Supplementary report. See TAF_SEARCH.json or TAF_SQUITEQL.json file in the CTR Project folder for a sample. See Customizing the Summary File for more information.
Contexts for Summary
The following are the filters available in the Report Generation UI that will be used as filters in the 'source':
There are 3 required nodes under context.report, these are:
-
postingperiod
-
subsidiary
-
nexus
All of these have corresponding sources from the reportparam object. You can also add other fields as needed.
-
Source for Summary
There are 6 types of sources that Tax Reporting Framework supports:
|
Type |
Description |
|---|---|
|
record |
Uses N/record.load to load NetSuite or Custom Record, use this type if you are expecting only 1 result, this can be filtered by recordid. |
|
config |
Uses N/config to load account configuration like Company Information. |
|
savedsearch |
Uses N/search, use this type if you are expecting > 1 result. |
|
searchtask |
Uses a combination of N/task and N/search, use this type if you are expecting large amount of data, this performs faster than savedsearch type. |
|
query |
Uses N/query, use this query if you are expecting > 1 result. |
|
querytask |
Uses a combination of N/task and N/query, use this type if you are expecting large amount of data, this performs faster than savedsearch type. |
There are two required record type sources: subsidiary and company. These sources are used to load company information.
Sample Code for Source
The following is sample code for source:
{
"id": "subsidiary",
"type": "record",
"recordtype": "subsidiary",
"recordid": "context.report.subsidiary",
"fields": ["name", "federalidnumber"],
"features": ["SUBSIDIARIES"],
"sublistfields": [
{
"name": "taxregistration",
"fields": [
"nexuscountry",
"nexus",
"taxregistrationnumber",
"taxengine",
"effectivefrom",
"validuntil"
]
}
]
},
{
"id": "company",
"type": "config",
"recordtype": "companyinformation",
"fields": ["employerid", "companyname"],
"sublistfields": [
{
"name": "taxregistration",
"fields": [
"nexuscountry",
"nexus",
"taxregistrationnumber",
"taxengine",
"effectivefrom",
"validuntil"
]
}
]
}
Sample Code for VAT Reports
{
"id": "vat_salesdetails",
"type": "savedsearch",
"internalid": "customsearch_str_salesbytaxcode_sum",
"filters": "STR_BASE_TRANSACTION_FILTERS",
"processor": {
"type": "scheduled",
"script": "SuiteApps/com.netsuite.suitetaxreports/src/app/processor/VATSearchProcessor",
"limits": {
"searchresult": 100000,
"period": "monthly"
},
"params": { "type": "SALE" }
}
},
{
"id": "vat_purchasedetails",
"type": "savedsearch",
"internalid": "customsearch_str_purchasesbytaxcode_sum",
"filters": "STR_BASE_TRANSACTION_FILTERS",
"processor": {
"type": "scheduled",
"script": "SuiteApps/com.netsuite.suitetaxreports/src/app/processor/VATSearchProcessor",
"limits": {
"searchresult": 100000,
"period": "monthly"
},
"params": { "type": "PURCHASE" }
}
},
{
"id": "vat_journaldetails",
"type": "savedsearch",
"internalid": "customsearch_str_journalsbytaxcode_sum",
"filters": "STR_BASE_TRANSACTION_FILTERS",
"processor": {
"type": "scheduled",
"script": "SuiteApps/com.netsuite.suitetaxreports/src/app/processor/VATJournalSearchProcessor",
"limits": {
"searchresult": 100000,
"period": "monthly"
}
}
},
{
"id": "vat_accountdrivendetails",
"type": "savedsearch",
"internalid": "customsearch_str_accountdriventrans_sum",
"filters": "STR_VAT_ACCOUNT_DRIVEN_FILTERS",
"processor": {
"type": "scheduled",
"script": "SuiteApps/com.netsuite.suitetaxreports/src/app/processor/TransactionSearchProcessor",
"limits": {
"searchresult": 100000,
"period": "monthly"
}
}
}
Sample Code for Tax Audit Files
The following is sample code for Tax Audit Files:
{
"id": "transactions",
"type": "savedsearch",
"internalid": "customsearch_str_taf_fr_fec",
"filters": [
{
"name": "subsidiary",
"operator": "is",
"values": "context.report.subsidiary",
"features": ["SUBSIDIARIES"]
},
{
"name": "internalid",
"operator": "anyof",
"join": "accountingperiod",
"values": "context.report.postingperiod"
},
{
"join": "account",
"name": "accountingcontext",
"operator": "is",
"values": "context.report.accountingcontext.usercontext",
"features": ["SUBSIDIARIES"]
},
{
"join": "account",
"name": "locale",
"operator": "is",
"values": "context.report.accountingcontext.userlocale"
}
],
"processor": {
"type": "scheduled",
"script": "SuiteApps/com.netsuite.suitetaxauditfiles/src/processors/pre/FR/FECSearchProcessor"
},
"validator": {
"script": "SuiteApps/com.netsuite.suitetaxreports/src/app/service/GLAuditNumberingService",
"method": "checkGLNumbering"
}
}
Your Data in Summary
For Tax Audit Files, fields defined here are used in the final output of the report.
Sample Code for VAT Reports
Your data nodes from both Summary and Detail schema files should have the same selectors. However, Summary data have groupings under their field nodes.
{
"id": "box1",
"source": ["vat_salesdetails"],
"field": [
{
"id": "netamount",
"value": "netamount",
"summarytype": "sum"
},
{
"id": "taxamount",
"value": "taxamount",
"summarytype": "sum"
}
],
"selector": {
"$and": [
{ "taxcode": { "$in": ["US_SR", "US_RR"] } },
{ "txnType": { "$eq": "Domestic Sales" } },
{ "type": { "$in": ["CustInvc", "CashSale", "CustCred"] } }
]
}
}
The Summary has a required DataFieldDefinition with ID report_data of type DERIVED. The report_data data field will be used as the data source for your template.
{
"id": "report_data",
"type": "DERIVED",
"field": [
{
"id": "box1",
"value": "box1.netamount + box1.taxamount"
},
{
"id": "box1_netamount",//box[x]_[netamount|taxamount] are required by STR Drilldown
"value": "box1.netamount"
},
{
"id": "box1_taxamount",
"value": "box1.taxamount"
},
{
"id": "box2",
"value": "box2.netamount + box2.taxamount"
},
{
"id": "box3",
"value": "abs(box1.netamount - box2.netamount)"
},
{
"id": "box4",
"value": "round(box1.taxamount - box2.taxamount, 2)"
},
{
"id": "box5",
"value": "IF(this.box4 > 0, this.box4, 0)"
}
]
}
Sample Code for Tax Audit Files
The following is sample code for report_data DataFieldDefinition node for Tax Audit Files:
{
"id": "transactions",
"source": ["transactions"],
"field": [
{
"id": "type",
"value": "type",
"type": "string"
},
{
"id": "typetext",
"value": "typetext",
"type": "string"
},
{
"id": "glnumber",
"value": "glnumber",
"type": "string"
},
{
"id": "glnumberdate",
"value": "glnumberdate",
"type": "date"
},
{
"id": "accnumber",
"value": "accnumber",
"type": "string"
},
{
"id": "accname",
"value": "accname",
"type": "string"
},
{
"id": "entityId",
"value": "entityId",
"type": "string"
},
{
"id": "entityName",
"value": "entityName",
"type": "string"
},
{
"id": "type",
"value": "type",
"type": "string"
},
{
"id": "tranid",
"value": "tranid",
"type": "string"
},
{
"id": "docdate",
"value": "docdate",
"type": "date"
},
{
"id": "memo",
"value": "memo",
"type": "string"
},
{
"id": "debit",
"value": "debit",
"type": "number"
},
{
"id": "credit",
"value": "credit",
"type": "number"
},
{
"id": "trandate",
"value": "trandate",
"type": "date"
},
{
"id": "fxamount",
"value": "fxamount",
"type": "number"
},
{
"id": "currencytext",
"value": "currencytext",
"type": "string"
}
]
}
Meta file: <report type>_<name>_META.json. This contains attributes that would determine how TRF handles report generation, configuration, preference, and export processes. See the TAF_SEARCH.json or TAF_SQUITEQL.json file in the CTR Project folder for a sample. See Customizing the Meta File for more information.
These files are part of the project files that are copied from the CTR ZIP file. The sample files have sample code that you can follow in customizing these files for your report. You should make copies of these files from the CTR ZIP file.
Details Schema Type for VAT Reports
This contains attributes that determine how TRF generates your VAT Drilldown reports.
Context
This should be the same as the node in the Summary schema file.
Source
There are 4 built-in sources for Details, each of them corresponding to each entry in the Summary sources for VAT reports.
{
"id": "vat_salesdetails_drilldown",
"type": "savedsearch",
"internalid": "customsearch_str_salesbytaxcode_details",
"filters": "STR_BASE_TRANSACTION_FILTERS",
"processor": {
"type": "scheduled",
"script": "SuiteApps/com.netsuite.suitetaxreports/src/app/processor/VATDetailsSearchProcessor",
"limits": {
"searchresult": 100000,
"period": "monthly"
},
"params": { "type": "SALE" }
}
},
{
"id": "vat_purchasedetails_drilldown",
"type": "savedsearch",
"internalid": "customsearch_str_purcbytaxcode_details",
"filters": "STR_BASE_TRANSACTION_FILTERS",
"processor": {
"type": "scheduled",
"script": "SuiteApps/com.netsuite.suitetaxreports/src/app/processor/VATDetailsSearchProcessor",
"limits": {
"searchresult": 100000,
"period": "monthly"
},
"params": { "type": "PURCHASE" }
}
},
{
"id": "vat_journaldetails_drilldown",
"type": "savedsearch",
"internalid": "customsearch_str_jrnlsbytaxcode_detail",
"filters": "STR_BASE_TRANSACTION_FILTERS",
"processor": {
"type": "scheduled",
"script": "SuiteApps/com.netsuite.suitetaxreports/src/app/processor/VATJournalDetailsSearchProcessor",
"limits": {
"searchresult": 100000,
"period": "monthly"
}
}
},
{
"id": "vat_accountdrivendetails_drilldown",
"type": "savedsearch",
"internalid": "customsearch_str_accountdriventrans_det",
"filters": "STR_VAT_ACCOUNT_DRIVEN_FILTERS",
"processor": {
"type": "scheduled",
"script": "SuiteApps/com.netsuite.suitetaxreports/src/app/processor/TransactionDetailsSearchProcessor",
"limits": {
"searchresult": 100000,
"period": "monthly"
}
}
}
Data
Details data nodes should have the same selector as their corresponding summary data nodes. For combined boxes, you can also use the CONCAT type data node.
{
"id": "box1",
"source": ["vat_salesdetails_drilldown"],
"selector": {
"$and": [
{
"taxcode": {
"$in": ["US_RR"]
}
},
{ "txnType": { "$eq": "Domestic Sales" } },
{ "type": { "$in": ["CustInvc", "CashSale", "CustCred"] } }
]
}
},
{
"id": "box2",
"source": ["vat_purchasedetails_drilldown"],
"selector": {
"$and": [
{ "taxcode": { "$eq": "US_SR" } },
{ "txnType": { "$eq": "Domestic Sales" } },
{ "type": { "$in": ["VendBill", "VendCred"] } }
]
},
"sort": "VAT_DRILLDOWN_SORT"
},
{
"id": "box3",
"type": "CONCAT",
"data": ["box1", "box2"],
"sort": "VAT_DRILLDOWN_SORT"
}
Related Topics
- Updating and Modifying the Country Tax Report
- Setting a UUID for your Tax Report
- Customizing the Report Schema in Your Tax Report
- Customizing Your Tax Report Using Saved Searches
- Customizing Your Tax Report Using SuiteQL
- Customizing the Summary File
- Customizing the Meta File
- Setting Up Your Registry Records
- Customizing Your Report Processors
- Customizing Your Tax Report Template