Problem

The organization needs consolidated visibility into billed products & invoiced products data across purchase and sales operations for specific company.

Key challenges include:

• Invoice data scattered across multiple tables (invoices, order lines, orders, warehouses).
• Difficulty tracking which invoices relate to specific purchase/sales orders.
• Need for warehouse-level invoice analysis.
• Complex joins required for basic reporting queries.
• Performance issues from repeated complex queries.

Business Requirements

• Track incoming vendor invoices with purchase order context.
• Track outgoing customer invoices with sales order context.
• Associate invoices with specific warehouses for location-based analysis.
• Filter for posted invoices only.
• Enable product-level invoice analysis.

Solution Provided

Overview

Two PostgreSQL database views that consolidate invoice data with related order and warehouse information for streamlined reporting and analysis.

View 1: Purchase Invoice View (Billed Products)

Purpose: Consolidates products details from vendor invoice data with purchase orders and warehouses.

Data Sources:

• account_move_line – Invoice line items
• account_move – Invoice headers
• purchase_order_line – Purchase order lines
• purchase_order – Purchase orders
• stock_picking_type – Picking/operation types
• stock_warehouse – Warehouse locations

Key Features:

• Tracks quantities and unit prices from vendors.
• Links invoices to originating purchase orders.
• Identifies receiving warehouse locations.

Filters Applied:

• Move type: in_invoice (vendor bills)
• Only posted invoices.
• Products must be specified.
• Company ID: 5

View 2: Sales Invoice View (Invoiced Products)

Purpose: Consolidates products details from customer invoice data with sales orders and warehouses.

Data Sources:

• account_move_line – Invoice line items
• account_move – Invoice headers
• sale_order_line – Sales order lines
• sale_order – Sales orders
• stock_warehouse – Warehouse locations

Key Features:

• Tracks sales quantities and prices to customers.
• Links invoices to originating sales orders.
• Identifies fulfillment warehouse locations.

Filters Applied:

• Move type: out_invoice (customer invoices)
• Only posted invoices.
• Products must be specified.
• Partners must be specified.
• Company ID: 5

Technical Implementation

Technology Stack

• Method: SQL Database Views
• Why: Transforms complex 5-6 table joins into a single queryable table, improving performance and simplifying invoice reporting across the organization.

View Creation Process

1. Drop existing view if present
2. Execute CREATE OR REPLACE VIEW statement
3. Generate unique row IDs using ROW_NUMBER()
4. Join related tables using LEFT JOINs
5. Apply business filters in WHERE clause

Benefits

• Performance: Pre-joined data eliminates repeated complex queries
• Simplicity: Single table interface for complex multi-table data
• Consistency: Standardized data access across reports
• Maintainability: Business logic centralized in view definition
• Security: Can apply role-based access at view level

Use Cases

1. Financial Analysis: Track revenue and costs by warehouse location
2. Inventory Management: Link invoice data to warehouse operations
3. Purchase Analysis: Analyze vendor pricing and order patterns
4. Sales Reporting: Customer invoice trends by product and location
5. Audit Trail: Posted invoices with complete order history

Data Fields

Common Fields (Both Views)

• id – Sequential row identifier
• invoice_id – Reference to invoice
• invoice_date – Date of invoice
• partner_id – Vendor (purchase) or Customer (sales)
• product_id – Product reference
• quantity – Quantity invoiced
• price_unit – Unit price
• currency_id – Transaction currency
• warehouse_id – Associated warehouse

Unique Fields

• Purchase View: purchase_id – Purchase order reference
• Sales View: sale_id – Sales order reference

Notes

• Views are recreated on module initialization
• Only includes Company ID 5 data
• Only posted (finalized) invoices included
• LEFT JOINs allow invoices without order references (though uncommon)
• ROW_NUMBER ensures unique IDs for Odoo model compatibility