---
id: varejo/fiscal-por-item
industry: Retail and e-commerce
usage: common
effort: high
grain: Receipt or invoice line item (store + POS + receipt + date + sequence, or access key + item)
---

# Tax by line item

Every line item of NFC-e (consumer e-receipt) and NF-e (e-invoice) with ICMS, PIS, COFINS and the new reform taxes, the CFOP and the SEFAZ status, matching the day's fiscal total and without counting stock transfers, kits or cancellations as sales.

## Executive summary

**For:** Tax, accounting and data teams at store chains and e-commerce businesses that need the tax on every item sold.

**What it solves**

- Tax per item, invoice and store comes from a single base that matches the day's fiscal total.
- Canceled, rejected and contingency receipts are kept apart from authorized ones.
- Stock transfers, branch transfers, giveaways and kit components stop counting as sales.

**Why it matters:** The tax team works with millions of items a year spread across POS logs, ERP invoices and SEFAZ (state tax authority) responses, each with its own key. A wrong join mixes items from receipts years apart, a misread cancellation removes valid sales, and the invoice total repeated on each item inflates tax. With the Brazilian tax reform, IBS and CBS start to coexist with ICMS, PIS and COFINS, and the simulation is only reliable if the item is right.

**Business questions it answers**

- How much ICMS, PIS and COFINS do we pay by store and by month, and what is the burden on sales?
- How many distinct NFC-e did each CNPJ issue per year?
- Which items are under tax review and why?
- Do each store's fiscal sales match the day's total on the POS?
- How much would IBS and CBS represent on each store's sales?
- Which products are missing NCM, CEST or CST in the product master?

**What you need**

- POS receipt, item, NFC-e and event tables, or ERP NF-e, connected to Nekt
- Product master with NCM, CEST and CST, and store master with state, CNPJ and tax regime
- The list of sales CFOPs and the meaning of the cancellation codes, validated with the tax team

**What you get**

- A table per item with values, taxes, CFOP, access key and fiscal status
- Daily check by store against the day's fiscal total, with differences listed
- Monthly report by CNPJ with tax by type and tax burden
- Review list with rejected items, atypical cancellations and products missing tax data

## Detailed

### The problem

The POS records the sold item in one table, the item's tax in another, the NFC-e header and the SEFAZ response in a third, and the receipt number restarts after a while. Cancellation flags have more than two values and the SEFAZ response code does not always tell the whole story. In the ERP, invoices have kit items without a CFOP, stock transfer invoices among the sales ones and tax totals in the header. Whoever joins by receipt number, counts every cancellation as a loss and adds the header tax on each item delivers a report the tax team cannot use.

### Business rules

1. **The item has a full key.** On receipts, the grain is store + POS + receipt number + movement date + item sequence. The receipt number is a cyclic counter that restarts per store and POS over the years, and other event fields repeat the same cycle. Validate the key with a distinct count over the whole table before using it. On NF-e, the grain is access key + item number. Every deduplication keeps the latest log record (highest event NSU). (Avoid: Joining item and header by store + receipt number.)
2. **The access key tells who issued it and the model.** The 44-digit key carries the state (positions 1 and 2), year and month (3 to 6), the issuer's CNPJ (company tax ID, 7 to 20), the model (21 and 22: 55 for NF-e, 65 for NFC-e), the series (23 to 25) and the number (26 to 34). Use the key's CNPJ and model to count distinct invoices per establishment and year, and the store's CNPJ in the master only as a check. (Avoid: Counting invoices by invoice number or by item rows.)
3. **Conservative fiscal status, in two classes.** Authorized = receipt and item with no cancellation mark and a SEFAZ response with no sign of rejection in the text. Everything else goes to Requires tax review: partial cancellation, denied, voided, contingency and rejection. The cancellation flag may have four values and is not boolean, and the success response code may come together with rejection text, so read the text. Keep the raw fields in the fact table for investigation and only open new classes once the tax team validates each code. (Avoid: Treating the cancellation flag as yes or no and trusting only the response code.)
4. **Three different cancellations.** A whole receipt canceled at the POS (receipt grain), an item canceled within a valid receipt (item grain) and an attempt to cancel the NFC-e rejected by SEFAZ for missing the legal deadline (response 501, invoice grain) are different facts. Most rejected attempts are receipts that remained valid and are sales. Each one goes to its own table or column. (Avoid: Counting every cancellation attempt as a canceled receipt.)
5. **CFOP separates sales from everything else.** Flag each item with a sales CFOP (tax operation code) flag based on a list maintained with the tax team. Shipment to a marketplace warehouse (5.949), simple invoicing for future delivery (5.922), transfer between branches (5.152) and giveaway (5.910) stay out of sales. For future delivery, count one of the two invoices, never both: in real cases, the simple invoicing note stays out and the delivery note (5.116, 5.117, 6.116, 6.117) goes in. (Avoid: Adding all outbound invoices as sales.)
6. **Kits carry the CFOP on the component.** On kit orders and invoices, the parent item comes without a CFOP and the components carry the CFOP and the value. Adding all items doubles the kit's value. Fiscal sales add only items with a sales CFOP. (Avoid: Adding the document total or all kit items.)
7. **Tax comes from the item, never from a repeated invoice.** Use item-level tax values: ICMS (state VAT, with CST and base), PIS and COFINS (federal contributions, with CST and base), and, from the reform, state IBS, municipal IBS, CBS and the Selective Tax, each in its own column with the IBS and CBS CST. Invoice header totals are only for checking: the sum of items must match them. DIFAL (interstate rate differential) stays in its own column, outside general ICMS. (Avoid: Carrying the header tax total onto each item.)
8. **The day's fiscal total is the authority.** For each store and day, compare the sum of authorized items with the POS fiscal day total (the Z report). Small differences are expected when item discounts sit in a separate column of the receipt log. Before adding any POS view, check its grain: some views repeat the same row several times, and a difference that is an integer multiple is a sign of duplicated rows in the join. (Avoid: Adding POS views directly and publishing without checking against the day total.)
9. **Store and product carry the tax context.** The store dimension carries state, CNPJ, state registration and tax regime (CRT); the state is needed to split state from municipal IBS. The product dimension carries NCM (product tax classification), CEST, category and the IBS, CBS and Selective Tax CSTs, one row per product by the latest change. A product sold without NCM or CST goes to the review list, never to a default. (Avoid: Assuming the head office's state for all stores and filling empty NCM with a default.)
10. **Product fix only on the defect's signature.** When the POS records the barcode instead of the product's internal code, resolve it through the source's automation mapping, and only when the row has the exact signature of the defect (zeroed automation codes) and a match exists. Flag the item as product corrected. Any other product without a master record stays null for review. (Avoid: Matching every product not found against the barcode base.)

### How each CFOP enters fiscal sales

Starting list from real cases; the tax team validates and completes it.

- **5.101, 5.102, 6.101, 6.102** → Sale of own production or resale (`cfop_de_venda = true`)
- **5.401, 5.405, 6.401, 6.404** → Sale under tax substitution (ST) (`cfop_de_venda = true`)
- **6.107, 6.108** → Interstate sale to a non-taxpayer (`cfop_de_venda = true`)
- **5.116, 5.117, 6.116, 6.117** → Delivery of a future sale, counts as a sale (`cfop_de_venda = true`)
- **5.922** → Simple invoicing for future delivery, out (`cfop_de_venda = false`)
- **5.949** → Shipment to a marketplace warehouse, out (`cfop_de_venda = false`)
- **5.152** → Transfer between branches, out (`cfop_de_venda = false`)
- **5.910** → Bonus or giveaway, out (`cfop_de_venda = false`)
- **Kit parent item without CFOP** → Out; the component carries the sale (`null cfop`)

### Definitions

| Metric | Definition |
|---|---|
| Fiscal sales | sum of total price minus discount of authorized items with a sales CFOP, by store (CNPJ) and day or month |
| ICMS for the period | sum of ICMS on authorized items with a sales CFOP, by CNPJ and month; DIFAL in a separate column |
| PIS and COFINS for the period | sum of PIS and COFINS on authorized items with a sales CFOP, by CNPJ and month |
| IBS and CBS | sum of state IBS, municipal IBS and CBS on items, each in its own column, by CNPJ and month |
| Tax burden | sum of taxes in the slice / fiscal sales in the slice, recomputed from the sums |
| Distinct invoices | distinct count of access keys by issuer CNPJ, model (55 or 65) and period |
| Items under review | items with status Requires tax review / total items, by store and day |
| Difference to the day total | fiscal sales of authorized items minus the POS fiscal day total, by store and day |

### Pitfalls

- **Items from different years in the same receipt.** The base joined items and headers by store and receipt number. Since the number restarts, items from receipts years apart were merged. Switching to event fields did not solve it, because they recycle the same way. How to avoid: Key with POS and movement date, validated by distinct count over the whole table.
- **Success with rejection in the text.** Thousands of rows carried the success response code together with a SEFAZ rejection text. Classifying only by the code marked those invoices as authorized. How to avoid: Read the response text and send any sign of rejection to review.
- **A cancellation that never happened.** Attempts to cancel NFC-e past the legal deadline, rejected by SEFAZ, were counted as canceled receipts. In most cases the receipt stayed valid and the sale disappeared from the report. How to avoid: Separate the rejected attempt from the actual cancellation.
- **Kits counted twice.** The value of kit orders came in twice, because the parent item, without a CFOP, was added together with the components. How to avoid: Add only items with a sales CFOP.
- **Discount multiplied.** A POS item discount view repeated the same row up to five times, and adding it directly inflated discounts by about 2.5 times. How to avoid: Deduplicate by item grain and check against the day's fiscal total.
- **A missing product that existed.** On a single day and store, the POS recorded the barcode instead of the internal code on a few hundred items, and active products showed up as unregistered in the tax report. How to avoid: Fix through the automation mapping only when the row has the defect's signature, and flag the item.

### Acceptance test

- [ ] The distinct count of (store, POS, receipt, date, sequence) equals the number of rows in the items table.
- [ ] For each store and day of a closed month, fiscal sales of authorized items match the POS fiscal day total within the declared tolerance, and every difference above it is listed.
- [ ] The number of distinct invoices by CNPJ, model and year matches the tax bookkeeping or SEFAZ report.
- [ ] For a sample of NF-e, the sum of item ICMS, PIS and COFINS equals the header tax total.
- [ ] No item with a CFOP outside the sales list, and no kit parent item, enters fiscal sales.

### Context document

```text
Tax by line item (retail)
- Grain: receipt item (store + POS + receipt + date + sequence) or NF-e item (access key + item). The receipt number recycles: never join without POS and date.
- Access key: positions 7 to 20 = issuer CNPJ; 21 to 22 = model (55 NF-e, 65 NFC-e). Count invoices by distinct key.
- Fiscal status: 1 = Authorized (no cancellation on receipt and item and no sign of rejection in the SEFAZ text); 99 = Requires tax review (everything else). Do not decode values 1, 2 and 3 of the cancellation flag without tax team validation.
- Receipt cancellation, item cancellation and attempt rejected by SEFAZ (response 501) are different things. A rejected attempt is not a cancellation.
- Fiscal sales = authorized items with cfop_de_venda = true. Stock shipments, transfers, giveaways and simple invoicing stay out. Kit parent items have no CFOP and do not add.
- Tax comes from the item (ICMS, PIS, COFINS, state IBS, municipal IBS, CBS, IS). Never carry the header total to the item. DIFAL separate.
- The day's fiscal total (Z report) is the value reference by store and day.
- Cancellation data identifies the cashier: do not expose it outside the authorized scope.
Client exceptions:
- <client>: list of sales CFOPs validated on <date>: <list>.
- <client>: history before <date> has no product in the POS log; use <alternative source>.
- <client>: channel <marketplace> invoiced in NF-e series <series>.
```

### Variations

- **Inbound invoices:** Supplier NF-e and CT-e (freight e-invoices), through a fiscal documents connector, for ICMS credit, freight cost and purchase checks.
- **ST recovery:** Distinct key count and items under tax substitution by CNPJ and year, as a temporary base for the recovery claim.
- **Margin with tax:** Gross profit per item = sales minus cost minus the item's ICMS, PIS and COFINS, as in the parts and service results of after-sales.