02ab2caf26
- 00-auth-base.sql: Extracted auth.tenants+users from recreate-database.sh - 03b-core-companies.sql: DDL for auth.companies entity - 21b-inventory-extended.sql: 7 new tables for inventory entities without DDL - 24-invoices.sql: billing→operations schema to resolve duplication - 27/28/29-cfdi: Track existing CFDI DDL files - recreate-database.sh: Updated ddl_files array (17→43 entries) Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
479 lines
21 KiB
SQL
479 lines
21 KiB
SQL
-- =============================================================
|
|
-- ARCHIVO: 21b-inventory-extended.sql
|
|
-- DESCRIPCION: Tablas extendidas de inventario (Odoo-style)
|
|
-- Incluye: locations, products (inventory-specific), stock_quants,
|
|
-- stock_moves, stock_valuation_layers, inventory_adjustments,
|
|
-- inventory_adjustment_lines
|
|
-- VERSION: 1.0.0
|
|
-- PROYECTO: ERP-Core V2
|
|
-- FECHA: 2026-02-05
|
|
-- DEPENDE DE: 18-warehouses.sql, 21-inventory.sql
|
|
-- COHERENCIA: Entidades TypeORM sin DDL previo
|
|
-- =============================================================
|
|
|
|
-- =====================
|
|
-- SCHEMA: inventory (ya creado en 18-warehouses.sql)
|
|
-- =====================
|
|
|
|
-- =====================
|
|
-- TIPO ENUM: location_type
|
|
-- Tipos de ubicacion Odoo-style
|
|
-- =====================
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'location_type_enum') THEN
|
|
CREATE TYPE inventory.location_type_enum AS ENUM (
|
|
'internal',
|
|
'supplier',
|
|
'customer',
|
|
'inventory',
|
|
'production',
|
|
'transit'
|
|
);
|
|
END IF;
|
|
END$$;
|
|
|
|
-- =====================
|
|
-- TIPO ENUM: product_type
|
|
-- Tipos de producto para inventario
|
|
-- =====================
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'inv_product_type_enum') THEN
|
|
CREATE TYPE inventory.inv_product_type_enum AS ENUM (
|
|
'storable',
|
|
'consumable',
|
|
'service'
|
|
);
|
|
END IF;
|
|
END$$;
|
|
|
|
-- =====================
|
|
-- TIPO ENUM: tracking_type
|
|
-- Tipo de seguimiento de producto
|
|
-- =====================
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'tracking_type_enum') THEN
|
|
CREATE TYPE inventory.tracking_type_enum AS ENUM (
|
|
'none',
|
|
'lot',
|
|
'serial'
|
|
);
|
|
END IF;
|
|
END$$;
|
|
|
|
-- =====================
|
|
-- TIPO ENUM: valuation_method
|
|
-- Metodo de valuacion de inventario
|
|
-- =====================
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'valuation_method_enum') THEN
|
|
CREATE TYPE inventory.valuation_method_enum AS ENUM (
|
|
'standard',
|
|
'fifo',
|
|
'average'
|
|
);
|
|
END IF;
|
|
END$$;
|
|
|
|
-- =====================
|
|
-- TIPO ENUM: adjustment_status
|
|
-- Estado de ajuste de inventario
|
|
-- =====================
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'adjustment_status_enum') THEN
|
|
CREATE TYPE inventory.adjustment_status_enum AS ENUM (
|
|
'draft',
|
|
'confirmed',
|
|
'done',
|
|
'cancelled'
|
|
);
|
|
END IF;
|
|
END$$;
|
|
|
|
-- =====================
|
|
-- TABLA: locations
|
|
-- Ubicaciones logicas de inventario (Odoo-style)
|
|
-- Complementa warehouse_locations con modelo Odoo de ubicaciones virtuales
|
|
-- (supplier, customer, inventory loss, production, transit)
|
|
-- Coherencia: location.entity.ts
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS inventory.locations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
warehouse_id UUID REFERENCES inventory.warehouses(id) ON DELETE SET NULL,
|
|
|
|
-- Identificacion
|
|
name VARCHAR(255) NOT NULL,
|
|
complete_name VARCHAR(500),
|
|
|
|
-- Tipo de ubicacion
|
|
location_type inventory.location_type_enum NOT NULL,
|
|
|
|
-- Jerarquia (auto-referencia)
|
|
parent_id UUID REFERENCES inventory.locations(id) ON DELETE SET NULL,
|
|
|
|
-- Flags especiales
|
|
is_scrap_location BOOLEAN NOT NULL DEFAULT FALSE,
|
|
is_return_location BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
-- Estado
|
|
active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id)
|
|
);
|
|
|
|
-- Indices para locations
|
|
CREATE INDEX IF NOT EXISTS idx_locations_tenant_id ON inventory.locations(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_locations_warehouse_id ON inventory.locations(warehouse_id) WHERE warehouse_id IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_locations_parent_id ON inventory.locations(parent_id) WHERE parent_id IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_locations_type ON inventory.locations(location_type);
|
|
CREATE INDEX IF NOT EXISTS idx_locations_active ON inventory.locations(active) WHERE active = TRUE;
|
|
CREATE INDEX IF NOT EXISTS idx_locations_scrap ON inventory.locations(is_scrap_location) WHERE is_scrap_location = TRUE;
|
|
CREATE INDEX IF NOT EXISTS idx_locations_return ON inventory.locations(is_return_location) WHERE is_return_location = TRUE;
|
|
|
|
-- =====================
|
|
-- TABLA: products
|
|
-- Productos especificos de inventario (Odoo-style)
|
|
-- Diferente de products.products: este es para gestion de almacen,
|
|
-- valuacion, tracking por lote/serie, mientras products.products
|
|
-- es para comercio/retail con codigos SAT, impuestos, etc.
|
|
-- Coherencia: product.entity.ts (inventory module)
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS inventory.products (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Identificacion
|
|
name VARCHAR(255) NOT NULL,
|
|
code VARCHAR(100),
|
|
barcode VARCHAR(100),
|
|
description TEXT,
|
|
|
|
-- Clasificacion
|
|
product_type inventory.inv_product_type_enum NOT NULL DEFAULT 'storable',
|
|
tracking inventory.tracking_type_enum NOT NULL DEFAULT 'none',
|
|
category_id UUID,
|
|
|
|
-- Unidades de medida
|
|
uom_id UUID NOT NULL,
|
|
purchase_uom_id UUID,
|
|
|
|
-- Precios y costo
|
|
cost_price DECIMAL(12, 2) NOT NULL DEFAULT 0,
|
|
list_price DECIMAL(12, 2) NOT NULL DEFAULT 0,
|
|
|
|
-- Valuacion
|
|
valuation_method inventory.valuation_method_enum NOT NULL DEFAULT 'fifo',
|
|
|
|
-- Flags
|
|
is_storable BOOLEAN NOT NULL DEFAULT TRUE,
|
|
can_be_sold BOOLEAN NOT NULL DEFAULT TRUE,
|
|
can_be_purchased BOOLEAN NOT NULL DEFAULT TRUE,
|
|
active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
|
|
-- Medidas fisicas
|
|
weight DECIMAL(12, 4),
|
|
volume DECIMAL(12, 4),
|
|
|
|
-- Imagen
|
|
image_url VARCHAR(500),
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id),
|
|
deleted_at TIMESTAMPTZ,
|
|
deleted_by UUID REFERENCES auth.users(id)
|
|
);
|
|
|
|
-- Indices para products
|
|
CREATE INDEX IF NOT EXISTS idx_inv_products_tenant_id ON inventory.products(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_inv_products_code ON inventory.products(code) WHERE deleted_at IS NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_inv_products_barcode ON inventory.products(barcode) WHERE deleted_at IS NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_inv_products_category_id ON inventory.products(category_id) WHERE category_id IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_inv_products_active ON inventory.products(active) WHERE deleted_at IS NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_inv_products_type ON inventory.products(product_type);
|
|
CREATE INDEX IF NOT EXISTS idx_inv_products_tracking ON inventory.products(tracking) WHERE tracking != 'none';
|
|
CREATE INDEX IF NOT EXISTS idx_inv_products_valuation ON inventory.products(valuation_method);
|
|
|
|
-- Unique code per tenant (soft-delete aware)
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_inv_products_tenant_code
|
|
ON inventory.products(tenant_id, code) WHERE deleted_at IS NULL AND code IS NOT NULL;
|
|
|
|
-- =====================
|
|
-- TABLA: stock_quants
|
|
-- Cantidades de stock por producto/ubicacion/lote (modelo Odoo)
|
|
-- Representa la cantidad real de un producto en una ubicacion especifica,
|
|
-- opcionalmente separada por lote
|
|
-- Coherencia: stock-quant.entity.ts
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS inventory.stock_quants (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Producto y ubicacion
|
|
product_id UUID NOT NULL REFERENCES inventory.products(id) ON DELETE CASCADE,
|
|
location_id UUID NOT NULL REFERENCES inventory.locations(id) ON DELETE CASCADE,
|
|
|
|
-- Lote (opcional)
|
|
lot_id UUID REFERENCES inventory.lots(id) ON DELETE SET NULL,
|
|
|
|
-- Cantidades
|
|
quantity DECIMAL(16, 4) NOT NULL DEFAULT 0,
|
|
reserved_quantity DECIMAL(16, 4) NOT NULL DEFAULT 0,
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
-- Unicidad: un solo registro por combinacion producto+ubicacion+lote
|
|
UNIQUE(product_id, location_id, COALESCE(lot_id, '00000000-0000-0000-0000-000000000000'::UUID))
|
|
);
|
|
|
|
-- Indices para stock_quants
|
|
CREATE INDEX IF NOT EXISTS idx_stock_quants_tenant_id ON inventory.stock_quants(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_stock_quants_product_id ON inventory.stock_quants(product_id);
|
|
CREATE INDEX IF NOT EXISTS idx_stock_quants_location_id ON inventory.stock_quants(location_id);
|
|
CREATE INDEX IF NOT EXISTS idx_stock_quants_lot_id ON inventory.stock_quants(lot_id) WHERE lot_id IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_stock_quants_quantity ON inventory.stock_quants(quantity) WHERE quantity != 0;
|
|
CREATE INDEX IF NOT EXISTS idx_stock_quants_reserved ON inventory.stock_quants(reserved_quantity) WHERE reserved_quantity != 0;
|
|
|
|
-- =====================
|
|
-- TABLA: stock_moves
|
|
-- Movimientos individuales de stock vinculados a un picking (Odoo-style)
|
|
-- Cada linea de un picking genera un stock_move
|
|
-- Coherencia: stock-move.entity.ts
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS inventory.stock_moves (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Picking padre
|
|
picking_id UUID NOT NULL REFERENCES inventory.pickings(id) ON DELETE CASCADE,
|
|
|
|
-- Producto
|
|
product_id UUID NOT NULL REFERENCES inventory.products(id) ON DELETE RESTRICT,
|
|
product_uom_id UUID NOT NULL,
|
|
|
|
-- Ubicaciones origen y destino
|
|
location_id UUID NOT NULL REFERENCES inventory.locations(id),
|
|
location_dest_id UUID NOT NULL REFERENCES inventory.locations(id),
|
|
|
|
-- Cantidades
|
|
product_qty DECIMAL(16, 4) NOT NULL,
|
|
quantity_done DECIMAL(16, 4) NOT NULL DEFAULT 0,
|
|
|
|
-- Lote (opcional)
|
|
lot_id UUID REFERENCES inventory.lots(id) ON DELETE SET NULL,
|
|
|
|
-- Estado (reutiliza move_status_enum de 21-inventory.sql)
|
|
status inventory.move_status_enum NOT NULL DEFAULT 'draft',
|
|
|
|
-- Fecha del movimiento
|
|
date TIMESTAMPTZ,
|
|
|
|
-- Documento origen
|
|
origin VARCHAR(255),
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id)
|
|
);
|
|
|
|
-- Indices para stock_moves
|
|
CREATE INDEX IF NOT EXISTS idx_stock_moves_tenant_id ON inventory.stock_moves(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_stock_moves_picking_id ON inventory.stock_moves(picking_id);
|
|
CREATE INDEX IF NOT EXISTS idx_stock_moves_product_id ON inventory.stock_moves(product_id);
|
|
CREATE INDEX IF NOT EXISTS idx_stock_moves_status ON inventory.stock_moves(status);
|
|
CREATE INDEX IF NOT EXISTS idx_stock_moves_date ON inventory.stock_moves(date) WHERE date IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_stock_moves_location ON inventory.stock_moves(location_id);
|
|
CREATE INDEX IF NOT EXISTS idx_stock_moves_location_dest ON inventory.stock_moves(location_dest_id);
|
|
CREATE INDEX IF NOT EXISTS idx_stock_moves_lot_id ON inventory.stock_moves(lot_id) WHERE lot_id IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_stock_moves_origin ON inventory.stock_moves(origin) WHERE origin IS NOT NULL;
|
|
|
|
-- =====================
|
|
-- TABLA: stock_valuation_layers
|
|
-- Capas de valuacion de inventario (FIFO/AVCO/Standard)
|
|
-- Cada movimiento de stock genera una capa de valuacion para
|
|
-- rastrear el costo historico del inventario
|
|
-- Coherencia: stock-valuation-layer.entity.ts
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS inventory.stock_valuation_layers (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Producto y empresa
|
|
product_id UUID NOT NULL REFERENCES inventory.products(id) ON DELETE RESTRICT,
|
|
company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,
|
|
|
|
-- Cantidades y valores
|
|
quantity DECIMAL(16, 4) NOT NULL,
|
|
unit_cost DECIMAL(12, 2) NOT NULL,
|
|
value DECIMAL(16, 2) NOT NULL,
|
|
|
|
-- Remanente (para FIFO)
|
|
remaining_qty DECIMAL(16, 4) NOT NULL,
|
|
remaining_value DECIMAL(16, 2) NOT NULL,
|
|
|
|
-- Movimiento de stock origen
|
|
stock_move_id UUID REFERENCES inventory.stock_moves(id) ON DELETE SET NULL,
|
|
|
|
-- Descripcion
|
|
description VARCHAR(255),
|
|
|
|
-- Contabilidad (referencia a asientos contables)
|
|
account_move_id UUID,
|
|
journal_entry_id UUID,
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id)
|
|
);
|
|
|
|
-- Indices para stock_valuation_layers
|
|
CREATE INDEX IF NOT EXISTS idx_valuation_layers_tenant_id ON inventory.stock_valuation_layers(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_valuation_layers_product_id ON inventory.stock_valuation_layers(product_id);
|
|
CREATE INDEX IF NOT EXISTS idx_valuation_layers_company_id ON inventory.stock_valuation_layers(company_id);
|
|
CREATE INDEX IF NOT EXISTS idx_valuation_layers_stock_move_id ON inventory.stock_valuation_layers(stock_move_id) WHERE stock_move_id IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_valuation_layers_remaining_qty ON inventory.stock_valuation_layers(remaining_qty) WHERE remaining_qty > 0;
|
|
CREATE INDEX IF NOT EXISTS idx_valuation_layers_account_move ON inventory.stock_valuation_layers(account_move_id) WHERE account_move_id IS NOT NULL;
|
|
|
|
-- =====================
|
|
-- TABLA: inventory_adjustments
|
|
-- Ajustes de inventario (conteos y correcciones)
|
|
-- Agrupa lineas de ajuste para una ubicacion y fecha especifica
|
|
-- Coherencia: inventory-adjustment.entity.ts
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS inventory.inventory_adjustments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
company_id UUID NOT NULL REFERENCES auth.companies(id) ON DELETE CASCADE,
|
|
|
|
-- Identificacion
|
|
name VARCHAR(100) NOT NULL,
|
|
|
|
-- Ubicacion de ajuste
|
|
location_id UUID NOT NULL REFERENCES inventory.locations(id),
|
|
|
|
-- Fecha del ajuste
|
|
date DATE NOT NULL,
|
|
|
|
-- Estado
|
|
status inventory.adjustment_status_enum NOT NULL DEFAULT 'draft',
|
|
|
|
-- Notas
|
|
notes TEXT,
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_by UUID REFERENCES auth.users(id)
|
|
);
|
|
|
|
-- Indices para inventory_adjustments
|
|
CREATE INDEX IF NOT EXISTS idx_adjustments_tenant_id ON inventory.inventory_adjustments(tenant_id);
|
|
CREATE INDEX IF NOT EXISTS idx_adjustments_company_id ON inventory.inventory_adjustments(company_id);
|
|
CREATE INDEX IF NOT EXISTS idx_adjustments_status ON inventory.inventory_adjustments(status);
|
|
CREATE INDEX IF NOT EXISTS idx_adjustments_date ON inventory.inventory_adjustments(date);
|
|
CREATE INDEX IF NOT EXISTS idx_adjustments_location ON inventory.inventory_adjustments(location_id);
|
|
|
|
-- =====================
|
|
-- TABLA: inventory_adjustment_lines
|
|
-- Lineas individuales de ajuste de inventario
|
|
-- Cada linea compara cantidad teorica vs contada para un producto
|
|
-- Coherencia: inventory-adjustment-line.entity.ts
|
|
-- =====================
|
|
CREATE TABLE IF NOT EXISTS inventory.inventory_adjustment_lines (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
adjustment_id UUID NOT NULL REFERENCES inventory.inventory_adjustments(id) ON DELETE CASCADE,
|
|
tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Producto y ubicacion
|
|
product_id UUID NOT NULL REFERENCES inventory.products(id) ON DELETE RESTRICT,
|
|
location_id UUID NOT NULL REFERENCES inventory.locations(id),
|
|
|
|
-- Lote (opcional)
|
|
lot_id UUID REFERENCES inventory.lots(id) ON DELETE SET NULL,
|
|
|
|
-- Cantidades
|
|
theoretical_qty DECIMAL(16, 4) NOT NULL DEFAULT 0,
|
|
counted_qty DECIMAL(16, 4) NOT NULL DEFAULT 0,
|
|
difference_qty DECIMAL(16, 4) GENERATED ALWAYS AS (counted_qty - theoretical_qty) STORED,
|
|
|
|
-- Unidad de medida
|
|
uom_id UUID,
|
|
|
|
-- Notas
|
|
notes TEXT,
|
|
|
|
-- Metadata
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Indices para inventory_adjustment_lines
|
|
CREATE INDEX IF NOT EXISTS idx_adjustment_lines_adjustment_id ON inventory.inventory_adjustment_lines(adjustment_id);
|
|
CREATE INDEX IF NOT EXISTS idx_adjustment_lines_product_id ON inventory.inventory_adjustment_lines(product_id);
|
|
CREATE INDEX IF NOT EXISTS idx_adjustment_lines_location_id ON inventory.inventory_adjustment_lines(location_id);
|
|
CREATE INDEX IF NOT EXISTS idx_adjustment_lines_lot_id ON inventory.inventory_adjustment_lines(lot_id) WHERE lot_id IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_adjustment_lines_tenant_id ON inventory.inventory_adjustment_lines(tenant_id);
|
|
|
|
-- =====================
|
|
-- COMENTARIOS
|
|
-- =====================
|
|
|
|
-- locations
|
|
COMMENT ON TABLE inventory.locations IS 'Ubicaciones logicas de inventario (Odoo-style). Incluye ubicaciones virtuales: supplier, customer, inventory loss, production, transit';
|
|
COMMENT ON COLUMN inventory.locations.location_type IS 'Tipo: internal (almacen), supplier (proveedor virtual), customer (cliente virtual), inventory (perdida/ajuste), production (produccion), transit (en transito)';
|
|
COMMENT ON COLUMN inventory.locations.complete_name IS 'Nombre completo con jerarquia (ej: WH/Stock/Zone-A)';
|
|
COMMENT ON COLUMN inventory.locations.is_scrap_location IS 'Si es TRUE, productos movidos aqui se consideran scrap/desperdicio';
|
|
COMMENT ON COLUMN inventory.locations.is_return_location IS 'Si es TRUE, esta ubicacion se usa para devoluciones';
|
|
|
|
-- products (inventory)
|
|
COMMENT ON TABLE inventory.products IS 'Productos para gestion de inventario (Odoo-style). Diferente de products.products que es para comercio/retail';
|
|
COMMENT ON COLUMN inventory.products.product_type IS 'Tipo: storable (se almacena), consumable (se consume sin tracking), service (no se almacena)';
|
|
COMMENT ON COLUMN inventory.products.tracking IS 'Seguimiento: none, lot (por lote), serial (numero de serie unico)';
|
|
COMMENT ON COLUMN inventory.products.valuation_method IS 'Metodo de valuacion: standard (costo fijo), fifo (primero en entrar), average (costo promedio)';
|
|
COMMENT ON COLUMN inventory.products.is_storable IS 'Flag derivado de product_type=storable. Facilita filtros directos';
|
|
|
|
-- stock_quants
|
|
COMMENT ON TABLE inventory.stock_quants IS 'Cantidades reales de stock por producto/ubicacion/lote (modelo Odoo quant)';
|
|
COMMENT ON COLUMN inventory.stock_quants.quantity IS 'Cantidad total disponible en esta ubicacion';
|
|
COMMENT ON COLUMN inventory.stock_quants.reserved_quantity IS 'Cantidad reservada para pickings pendientes';
|
|
|
|
-- stock_moves
|
|
COMMENT ON TABLE inventory.stock_moves IS 'Movimientos individuales de stock vinculados a un picking. Cada linea de picking genera un move';
|
|
COMMENT ON COLUMN inventory.stock_moves.product_qty IS 'Cantidad solicitada del movimiento';
|
|
COMMENT ON COLUMN inventory.stock_moves.quantity_done IS 'Cantidad realmente procesada';
|
|
COMMENT ON COLUMN inventory.stock_moves.status IS 'Estado: draft, waiting, confirmed, assigned, done, cancelled';
|
|
COMMENT ON COLUMN inventory.stock_moves.origin IS 'Documento origen (ej: PO-2026-001, SO-2026-001)';
|
|
|
|
-- stock_valuation_layers
|
|
COMMENT ON TABLE inventory.stock_valuation_layers IS 'Capas de valuacion de inventario para FIFO/AVCO/Standard. Cada movimiento crea una capa';
|
|
COMMENT ON COLUMN inventory.stock_valuation_layers.remaining_qty IS 'Cantidad restante en esta capa (para FIFO: se consume de las capas mas antiguas)';
|
|
COMMENT ON COLUMN inventory.stock_valuation_layers.remaining_value IS 'Valor monetario restante en esta capa';
|
|
COMMENT ON COLUMN inventory.stock_valuation_layers.account_move_id IS 'Referencia al asiento contable generado (si aplica)';
|
|
|
|
-- inventory_adjustments
|
|
COMMENT ON TABLE inventory.inventory_adjustments IS 'Ajustes de inventario para corregir diferencias entre stock teorico y real';
|
|
COMMENT ON COLUMN inventory.inventory_adjustments.status IS 'Estado: draft (borrador), confirmed (confirmado), done (aplicado), cancelled';
|
|
|
|
-- inventory_adjustment_lines
|
|
COMMENT ON TABLE inventory.inventory_adjustment_lines IS 'Lineas de ajuste: cada una compara cantidad teorica vs contada para un producto en una ubicacion';
|
|
COMMENT ON COLUMN inventory.inventory_adjustment_lines.theoretical_qty IS 'Cantidad segun el sistema antes del conteo';
|
|
COMMENT ON COLUMN inventory.inventory_adjustment_lines.counted_qty IS 'Cantidad real contada fisicamente';
|
|
COMMENT ON COLUMN inventory.inventory_adjustment_lines.difference_qty IS 'Diferencia calculada: counted_qty - theoretical_qty (columna generada)';
|