148 lines
5.9 KiB
Markdown
148 lines
5.9 KiB
Markdown
# 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_summary.md)
|
|
|
|
---
|
|
|
|
### `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](brandize_datacenter_amazon_inventory_detailed.md)
|
|
|
|
---
|
|
|
|
### `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](product_product.md)
|
|
|
|
---
|
|
|
|
## 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
|