brandize_bi/sql/tables/overview.md

Odoo PostgreSQL Table Overview

This document provides an overview of the important Odoo tables used in the Brandize BI Dashboard.

Main Transaction Tables

brandize_datacenter_amazon_vat_transaction

Model: brandize.datacenter.amazon.vat.transaction
App: brandize_datacenter
Description: Auftrag (Order/Transaction) - Primary table for Amazon VAT transaction data

This is the main transaction table containing all VAT-related transaction data from Amazon. It serves as the foundation for all BI reports including:

• Revenue per departure country (Summe_je_Abgangsland.sql)
• VAT summary (VAT_Summary.sql)
• Invoice reports (Alle_Rechnungen.sql, Rechnungsexport.sql, Quartal_Schweiz.sql)
• Activity period management (Delete_Activity_Period.sql)

---

brandize_datacenter_amazon_inventory_summary

Model: brandize.datacenter.amazon.inventory.summary
App: brandize_datacenter
Description: Amazon Inventory Ledger Summary - Inventory ledger and stock level tracking

This table stores Amazon inventory ledger data, tracking inventory movements including shipments, returns, damages, and warehouse transfers. It provides visibility into inventory levels, movements, and valuation.

Key Characteristics:

• Type: Basisobjekt (Base object)
• Has Email Thread: No
• Has Email Activity: No
• Has Email Blacklist: No
• Transition Model: No

Related Documentation:

• Full field description

---

brandize_datacenter_amazon_inventory_detailed

Model: brandize.datacenter.amazon.inventory.detailed
App: brandize_datacenter
Description: Amazon Inventory Ledger Detailed - Detailed inventory ledger entries

This table stores detailed inventory ledger entries for Amazon FBA. Each record represents a specific inventory event or transaction, providing a granular view of inventory movements.

Key Characteristics:

• Type: Basisobjekt (Base object)
• Has Email Thread: No
• Has Email Activity: No
• Has Email Blacklist: No
• Transition Model: No

Related Documentation:

• Full field description

---

product_product

Model: product.product
App: product (with many integrations)
Description: Produktvariante (Product Variant) - Odoo's core product/product variant management

This is Odoo's standard product variant table, storing detailed product information including variants, pricing, inventory settings, and descriptions. It serves as the central product catalog for all Odoo modules.

Important: The default_code field is used as the SKU (Stock Keeping Unit) throughout the system, including in Amazon integration and inventory tracking.

Key Characteristics:

• Type: Basisobjekt (Base object)
• Has Email Thread: Yes
• Has Email Activity: Yes
• Has Email Blacklist: Yes
• Transition Model: No

Related Documentation:

• Full field description

---

Reference Tables

res_country

Model: res.country
App: base
Description: Country master data table

Used for country code lookups and translated country names in reports.

Key Fields:

• id - Primary key
• code - ISO country code (e.g., 'DE', 'AT', 'CH')
• name - Country name (multi-language support via JSONB)

Usage:

• Joined in VAT_Summary.sql to display country names in the report

---

account_move

Model: account.move
App: account
Description: Kundenrechnungen / Gutschriften (Customer invoices / credit notes)

Odoo's standard accounting module table for managing invoices and credit notes.

Key Fields:

• id - Primary key
• name - Invoice number
• move_type - Invoice type (out_invoice, out_refund, etc.)
• date - Invoice date
• partner_id - Customer reference
• amount_total - Total amount
• state - Status (posted, draft, cancel)

Usage:

• Queried in Alle_Rechnungen.sql and Quartal_Schweiz.sql for ODOO invoice data
• Combined with Amazon transaction data via UNION ALL

---

Table Structure Summary

Table	Model	Description	Key Fields
brandize_datacenter_amazon_vat_transaction	brandize.datacenter.amazon.vat.transaction	Amazon VAT transaction records	ID, transaction_type, departure_country, arrival_country, amount fields
brandize_datacenter_amazon_inventory_summary	brandize.datacenter.amazon.inventory.summary	Amazon inventory ledger summary	ID, asin, fnsku, customer_shipments, receipts, damaged, disposed
brandize_datacenter_amazon_inventory_detailed	brandize.datacenter.amazon.inventory.detailed	Amazon inventory ledger detailed	ID, asin, fnsku, event_type, quantity, reconciled_quantity, fulfillment_center
product_product	product.product	Product variants	id, default_code (SKU), categ_id, list_price, cost_method
res_country	res.country	Country master data	id, code, name
account_move	account.move	Customer invoices / credit notes	id, name, move_type, date, partner_id, amount_total, state

---

Notes

• All monetary values in the database are stored in the company's currency
• Date fields track creation (create_date), updates (write_date), and transaction-specific dates
• The table supports multi-currency transactions with exchange rate tracking
• Tax reporting includes multiple jurisdiction levels for VAT calculation
• Country names in reports use multi-language support (de_DE, en_US) from res_country.name JSONB field
• The BI dashboard queries Amazon transaction data (brandize_datacenter_amazon_vat_transaction), inventory data (brandize_datacenter_amazon_inventory_summary, brandize_datacenter_amazon_inventory_detailed), product data (product_product), and Odoo invoice data (account.move) for comprehensive reporting