16 Tax Burden Display at POS and Sales NF

This chapter covers the following topics:

Overview

In Brazil, new legislation related to the consumer protection code was released determining that the tax burden included in products and services sold to end consumer must be highlighted in the fiscal documents or fiscal tickets in a specific field. The purpose of this law is to provide transparency to consumers on the amount of taxes included in retail prices.

In that way, the law determines that the total amount of federal, state and municipal taxes levied on the sale of goods or services need to be stated in the fiscal documents. Companies must inform "approximate amount of taxes", per item or service sold in a specific field. Also, the header of the fiscal document should include the total amount of taxes, considering all items and services sold.

These changes impact the NF-e layout, as well as the DANFE report (NFE report).

As the rate cannot be calculated or determined by calculations in the system, it must be uploaded from an external source. It will be necessary to have the average tax rate coming from authorized entities, approved to provide such information. One of the authorized entities that publish the average tax rate for all types of products is the IBPT - Brazilian Institute of Tax Planning.

IBPT provides via downloadable file, the rates for the determination of the approximate values of taxes to the consumer, making it possible for businesses issuing fiscal documents or POS fiscal tickets the full compliance with the law, exempting them from any responsibility for the calculation of this tax, since the source is acknowledged in the documents.

Tax Burden Induction Framework

In order to support the use case presented above, the Tax Burden Induction framework was created. This framework provides the flexibility to upload data through an open document spreadsheet (".ods" extension) formatted in a predefined template.

Note:

For information of how to upload the data into ORFM, see Oracle Retail Fiscal Management User Guide.

Manual Upload

An upload from a spreadsheet is initiated by user request through the RFM Tax Burden Induction screen. The request includes the source file location and the country state that the data represents. On submission of the upload request, RFM will process the request and perform relevant validation and will import the data into destination tables.

Template Maintenance

The authorized entities that provide the tax burden data may change the data layout throughout the time. If that happens there are two available options in order to upload data successfully into ORFM.

Modify the data spreadsheet to be compliance with previous template, already available in database.
Modify the process template (l10n_template_config.ods file).

The below section describes how to modify the template document. The template is found in "Localization/s9t_templates/ l10n_template_config.ods”.

Note:

ORFM does provide a script that can be used to upload template configurations from a spreadsheet. For more information on this function, see Oracle Retail Merchandising System with Brazil Localization Installation Guide.

Template Header

The template header (S9T_TEMPLATE sheet) table captures the template ID, description, as well as, a template type that aids in the classification of templates.

The key fields in the header are described as follows:

Table 16-1 Key Fields in the Template Header

Configuration Table Column	Description
TEMPLATE_KEY	This is a unique identifier of a template in RMS and should be unique for each new template added. Do not modify this column. Valid value is only FM_APPROX_TAX_BURDEN_SOURCE for this process.
TEMPLATE_NAME	This column contains the name of the template and will be the displayed name available in the Tax Burden Induction screen when requesting an upload of data.
TEMPLATE_DESC	Description of the template.
FILE_ID	Do not fill this column.
TEMPLATE_TYPE	It indicates if IRL Monitor allows multisite treatment. Do not modify this column. Valid value is only L10NT for this process.

Worksheet Definition

The worksheet definition table lists all tables that should be considered for inclusion as part of a given template. For Tax Burden Induction process only FM_APPROX_TAX_BURDEN_SOURCE table is available.

The key fields in the header are described as follows:

Table 16-2 Worksheet Definition Tables

Configuration Table Column	Description
WKSHT_KEY	Do not modify this column. Valid value is only FM_TAX_RATES.
WKSHT_NAME	This contains the user-facing name that will appear on the tab in a spreadsheet workbook for this table. The description also supports translation for cases where users have different primary languages. It should be noted that the worksheet name is also used to map the worksheets in the spreadsheet to the tables in the staging area when uploading data. Therefore, it should not be changed by users. Valid value example IBPT (see figure Fig x - worksheet name example).
MANDATORY	The table should be designated as mandatory if you want it to be displayed in the spreadsheet generated by this template or to indicate that it will be present in the upload. Do not modify this column.

Figure 16-1 Worksheet Name Example

Surrounding text describes Figure 16-1 .

Note:

Do not use spaces if you are using Microsoft Excel for modifying worksheets or worksheet names.

Column Definition

The column definition table will contain information on the columns to be included in the template for each table defined as part of worksheet definition. Just as the tables included in a template get mapped to worksheets in the spreadsheet, columns indicated as mandatory in the column definition table are mapped to the columns in the worksheet corresponding to that table.

The key fields in the column definition table are described as follows:

Table 16-3 Column Definition Table

Configuration Table Column	Description
COLUMN_KEY	This should be the database name of the column in the impacted table. The columns used for Tax Burden process are (all columns should be in the template): NCM_NBS_CODE - Classification code EX_IPI - Extension of Classification code ITEM_TYPE - Item type DESCRIPTION - Description of Classification TAX_RATE_DOMESTIC_FEDERAL - Domestic Federal Tax Rate TAX_RATE_IMPORTED_FEDERAL - Imported Federal Tax Rate TAX_RATE_STATE - State Rate TAX_RATE_MUNICIPAL - Municipal Rate BEGIN_DATE - Begin Date of the Rate END_DATE - End Date of the Rate KEY_CODE - Code of the spreadsheet (usually provided by the authorized entity) VERSION_CODE - Version of the spreadsheet (usually provided by the authorized entity) SOURCE_CODE - Source of the information (example, IBPT) ACTION - Action to be taken by the process (valid values are NEW to insert the record, MOD to modify an existing record or DEL to delete a record). This column is not mandatory and if its not informed default value will be "NEW".
COLUMN_NAME	This contains the name that you want to have displayed in the spreadsheet column header for this template, for example, codigo. The description will also support translation for cases where users have different primary languages. Like the worksheet name, the column name is also used to map the worksheet columns to the columns in the table. It is assumed that the column names are not edited by the business users while creating or updating data.
MANDATORY	The column should be designated as mandatory if you want it to indicate that the column is mandatory to be present in the upload.
DEFAULT	This contains a default value for the column in case there is no value informed in the column during upload.
REQUIRED_VISUAL_IND	Not used in this process. Leave it blank.

Spreadsheet Formatting

Given that the spreadsheet is intended to ease the process of data entry and enrichment, certain usability features have been incorporated in the downloaded spreadsheet. However, because the use of spreadsheets to maintain data is so flexible, there are opportunities for users to make changes that may render the data invalid from a Tax Burden Induction perspective. Hence, some key assumptions have been made that dictate how the Tax Burden induction framework processes the data.

The columns marked as mandatory in the column definition tables will form the columns in the respective worksheets. The order in which columns appear in a sheet is not defined as part of template configuration, but users can rearrange columns in an individual sheet for ease of use. This will not impact data upload.

The templates are not specific to the action being taken for the data, which means that users can create, update, and delete information all in a single spreadsheet. The action to be performed with the data on each row (Create, Update, or Delete) will be indicated on the first column of each row on the spreadsheet for data uploads.

Figure 16-2 Sample Spreadsheet Format

Surrounding text describes Figure 16-2 .

Template Definition Examples

See the following sections for template definition examples.

IBPT Source Template Example

This section illustrates the set up of a simple template Tax Burden Induction process.

The worksheet definition table has records for each of the three tables to be included with the mandatory flag set and the sequence of ordering of worksheets specified by the sequence number column.

Table 16-4 S9T_TEMPLATE

TEMPLATE_KEY	TEMPLATE_NAME	TEMPLATE_DESC	FILE_ID	TEMPLATE_TYPE
FM_APPROX_TAX_BURDEN_SOURCE	Tax Burden Information	Tax Tax Burden Information		L10NT

Table 16-5 S9T_TMPL_WKSHT_DEF

TEMPLATE_KEY	WKSHT_KEY	WKSHT_NAME	MANDATORY
FM_APPROX_TAX_BURDEN_SOURCE	FM_TAX_RATES	IBPT	Y

Table 16-6 S9T_TMPL_COLS_DEF

TEMPLATE_KEY	WKSHT_KEY	COLUMN_KEY	COLUMN_NAME	MANDATORY	DEFAULT	REQUIRED_VISUAL_IND
FM_APPROX_TAX_BURDEN_SOURCE	FM_TAX_RATES	NCM_NBS_CODE	codigo	Y		Y
FM_APPROX_TAX_BURDEN_SOURCE	FM_TAX_RATES	EX_IPI	ex	N
FM_APPROX_TAX_BURDEN_SOURCE	FM_TAX_RATES	ITEM_TYPE	tipo	N
FM_APPROX_TAX_BURDEN_SOURCE	FM_TAX_RATES	DESCRIPTION	descricao	N
FM_APPROX_TAX_BURDEN_SOURCE	FM_TAX_RATES	TAX_RATE_DOMESTIC_FEDERAL	nacionalfederal	Y
FM_APPROX_TAX_BURDEN_SOURCE	FM_TAX_RATES	TAX_RATE_IMPORTED_FEDERAL	importadosfederal	Y
FM_APPROX_TAX_BURDEN_SOURCE	FM_TAX_RATES	TAX_RATE_STATE	estadual	Y
FM_APPROX_TAX_BURDEN_SOURCE	FM_TAX_RATES	TAX_RATE_MUNICIPAL	municipal	Y
FM_APPROX_TAX_BURDEN_SOURCE	FM_TAX_RATES	BEGIN_DATE	vigenciainicio	Y
FM_APPROX_TAX_BURDEN_SOURCE	FM_TAX_RATES	END_DATE	vigenciafim	Y
FM_APPROX_TAX_BURDEN_SOURCE	FM_TAX_RATES	KEY_CODE	chave	N
FM_APPROX_TAX_BURDEN_SOURCE	FM_TAX_RATES	VERSION_CODE	versao	N
FM_APPROX_TAX_BURDEN_SOURCE	FM_TAX_RATES	SOURCE_CODE	fonte	N
FM_APPROX_TAX_BURDEN_SOURCE	FM_TAX_RATES	ACTION	Action	N	NEW