Tax by line item

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).

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.

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.

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.

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.

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.

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.

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.

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.

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.