2011 lines
66 KiB
Markdown
2011 lines
66 KiB
Markdown
# DDL-SPEC: Schemas purchasing_management & inventory_management
|
|
|
|
## Identificacion
|
|
|
|
| Campo | Valor |
|
|
|-------|-------|
|
|
| **Schemas** | purchasing_management, inventory_management |
|
|
| **Modulo** | MAI-004 - Compras e Inventarios |
|
|
| **Vertical** | Construccion |
|
|
| **Version** | 1.0 |
|
|
| **Estado** | En Diseno |
|
|
| **Autor** | Database-Agent |
|
|
| **Fecha** | 2025-12-06 |
|
|
|
|
---
|
|
|
|
## Descripcion General
|
|
|
|
Los schemas `purchasing_management` e `inventory_management` gestionan el ciclo completo de compras e inventarios para proyectos de construccion: desde requisiciones de materiales, cotizaciones, ordenes de compra, hasta el control de almacenes, movimientos de inventario y valoracion PEPS (Primeras Entradas, Primeras Salidas).
|
|
|
|
### Alcance
|
|
|
|
- Gestion de requisiciones de materiales con flujo de aprobacion multi-nivel
|
|
- Solicitudes de cotizacion (RFQ) y recepcion de cotizaciones
|
|
- Generacion y seguimiento de ordenes de compra
|
|
- Control de almacenes (generales, por proyecto, temporales)
|
|
- Movimientos de inventario (entradas, salidas, traspasos, ajustes)
|
|
- Valoracion de inventario mediante metodo PEPS
|
|
- Niveles de stock en tiempo real con reservas
|
|
- Inventarios fisicos y ajustes
|
|
|
|
### RF Cubiertos
|
|
|
|
| RF | Titulo | Tablas |
|
|
|----|--------|--------|
|
|
| RF-COMP-001 | Requisiciones de Materiales | requisitions |
|
|
| RF-COMP-002 | Cotizaciones y RFQs | quotations, rfqs |
|
|
| RF-COMP-003 | Ordenes de Compra | purchase_orders, po_receipts |
|
|
| RF-INV-001 | Gestion de Almacenes | warehouses, warehouse_locations |
|
|
| RF-INV-002 | Movimientos de Inventario | inventory_movements |
|
|
| RF-INV-003 | Niveles de Stock | stock_levels, inventory_lots |
|
|
|
|
---
|
|
|
|
## Diagrama Entidad-Relacion
|
|
|
|
```mermaid
|
|
erDiagram
|
|
%% Purchasing Management
|
|
requisitions ||--o{ purchase_orders : "genera"
|
|
requisitions ||--o{ rfqs : "origina"
|
|
rfqs ||--o{ quotations : "recibe"
|
|
quotations ||--o| purchase_orders : "convierte_en"
|
|
purchase_orders ||--o{ po_receipts : "recibe_material"
|
|
|
|
%% Inventory Management
|
|
warehouses ||--o{ warehouse_locations : "tiene_ubicaciones"
|
|
warehouses ||--o{ inventory_movements : "registra_movimientos"
|
|
warehouses ||--o{ stock_levels : "mantiene_stock"
|
|
warehouses ||--o{ inventory_lots : "almacena_lotes"
|
|
inventory_movements ||--o{ inventory_lots : "consume_lotes"
|
|
po_receipts }o--|| warehouses : "destino"
|
|
stock_levels ||--o{ inventory_lots : "compuesto_por"
|
|
|
|
requisitions {
|
|
uuid id PK
|
|
uuid tenant_id FK
|
|
varchar code UK
|
|
uuid project_id FK
|
|
uuid requested_by FK
|
|
date required_date
|
|
requisition_status status
|
|
jsonb items
|
|
decimal estimated_total
|
|
jsonb approval_flow
|
|
text rejected_reason
|
|
timestamptz created_at
|
|
timestamptz updated_at
|
|
uuid created_by FK
|
|
uuid updated_by FK
|
|
boolean is_active
|
|
timestamptz deleted_at
|
|
uuid deleted_by FK
|
|
}
|
|
|
|
rfqs {
|
|
uuid id PK
|
|
uuid tenant_id FK
|
|
varchar code UK
|
|
uuid requisition_id FK
|
|
uuid project_id FK
|
|
date issue_date
|
|
date closing_date
|
|
rfq_status status
|
|
jsonb items
|
|
jsonb invited_suppliers
|
|
text terms_conditions
|
|
timestamptz created_at
|
|
timestamptz updated_at
|
|
uuid created_by FK
|
|
uuid updated_by FK
|
|
boolean is_active
|
|
}
|
|
|
|
quotations {
|
|
uuid id PK
|
|
uuid tenant_id FK
|
|
varchar code UK
|
|
uuid rfq_id FK
|
|
uuid supplier_id FK
|
|
date quote_date
|
|
date valid_until
|
|
quotation_status status
|
|
jsonb items
|
|
decimal subtotal
|
|
decimal tax
|
|
decimal total
|
|
int delivery_days
|
|
varchar payment_terms
|
|
decimal payment_discount
|
|
text notes
|
|
timestamptz created_at
|
|
timestamptz updated_at
|
|
uuid created_by FK
|
|
uuid updated_by FK
|
|
boolean is_active
|
|
}
|
|
|
|
purchase_orders {
|
|
uuid id PK
|
|
uuid tenant_id FK
|
|
varchar code UK
|
|
uuid supplier_id FK
|
|
uuid project_id FK
|
|
uuid requisition_id FK
|
|
uuid rfq_id FK
|
|
uuid quotation_id FK
|
|
date order_date
|
|
date delivery_date
|
|
text delivery_address
|
|
jsonb items
|
|
decimal subtotal
|
|
decimal tax
|
|
decimal total
|
|
po_status status
|
|
varchar payment_terms
|
|
int payment_terms_days
|
|
decimal early_payment_discount
|
|
boolean requires_advance
|
|
decimal advance_percentage
|
|
int warranty_days
|
|
text special_conditions
|
|
uuid approved_by FK
|
|
timestamptz approved_at
|
|
timestamptz sent_at
|
|
timestamptz created_at
|
|
timestamptz updated_at
|
|
uuid created_by FK
|
|
uuid updated_by FK
|
|
boolean is_active
|
|
}
|
|
|
|
po_receipts {
|
|
uuid id PK
|
|
uuid tenant_id FK
|
|
varchar code UK
|
|
uuid purchase_order_id FK
|
|
uuid warehouse_id FK
|
|
date receipt_date
|
|
uuid received_by FK
|
|
jsonb items
|
|
varchar delivery_note
|
|
varchar transport_company
|
|
text notes
|
|
text[] attachments
|
|
timestamptz created_at
|
|
uuid created_by FK
|
|
}
|
|
|
|
warehouses {
|
|
uuid id PK
|
|
uuid tenant_id FK
|
|
varchar code UK
|
|
varchar name
|
|
warehouse_type type
|
|
uuid project_id FK
|
|
text address
|
|
varchar city
|
|
varchar state
|
|
uuid managed_by FK
|
|
decimal total_area
|
|
decimal covered_area
|
|
jsonb settings
|
|
timestamptz created_at
|
|
timestamptz updated_at
|
|
uuid created_by FK
|
|
uuid updated_by FK
|
|
boolean is_active
|
|
timestamptz deleted_at
|
|
uuid deleted_by FK
|
|
}
|
|
|
|
warehouse_locations {
|
|
uuid id PK
|
|
uuid tenant_id FK
|
|
uuid warehouse_id FK
|
|
varchar zone
|
|
varchar position
|
|
varchar code UK
|
|
varchar description
|
|
decimal capacity_m3
|
|
timestamptz created_at
|
|
boolean is_active
|
|
}
|
|
|
|
inventory_movements {
|
|
uuid id PK
|
|
uuid tenant_id FK
|
|
varchar code UK
|
|
uuid warehouse_id FK
|
|
movement_type type
|
|
date movement_date
|
|
movement_source source_type
|
|
uuid source_id
|
|
uuid project_id FK
|
|
uuid budget_item_id FK
|
|
uuid transfer_warehouse_id FK
|
|
jsonb items
|
|
decimal total_value
|
|
text notes
|
|
uuid authorized_by FK
|
|
uuid recorded_by FK
|
|
transfer_status transfer_status
|
|
timestamptz received_at
|
|
timestamptz created_at
|
|
uuid created_by FK
|
|
}
|
|
|
|
stock_levels {
|
|
uuid id PK
|
|
uuid tenant_id FK
|
|
uuid warehouse_id FK
|
|
uuid material_id FK
|
|
uuid location_id FK
|
|
decimal quantity
|
|
decimal reserved_quantity
|
|
decimal available_quantity
|
|
decimal average_cost
|
|
decimal total_value
|
|
date last_movement_date
|
|
date last_entry_date
|
|
date last_exit_date
|
|
timestamptz updated_at
|
|
}
|
|
|
|
inventory_lots {
|
|
uuid id PK
|
|
uuid tenant_id FK
|
|
uuid warehouse_id FK
|
|
uuid material_id FK
|
|
varchar lot_number
|
|
date entry_date
|
|
decimal quantity
|
|
decimal remaining_quantity
|
|
decimal unit_cost
|
|
movement_source source_type
|
|
uuid source_id
|
|
boolean is_depleted
|
|
timestamptz created_at
|
|
}
|
|
```
|
|
|
|
---
|
|
|
|
## Schema: purchasing_management
|
|
|
|
### Extensiones Necesarias
|
|
|
|
```sql
|
|
-- Extensiones ya creadas en core
|
|
-- uuid-ossp, pg_trgm, btree_gist, pgcrypto
|
|
```
|
|
|
|
### 1. ENUMs
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- ENUMs - Purchasing Management
|
|
-- ============================================================================
|
|
|
|
-- Estado de requisiciones
|
|
CREATE TYPE purchasing_management.requisition_status AS ENUM (
|
|
'draft', -- Borrador
|
|
'pending', -- Pendiente de aprobacion
|
|
'approved', -- Aprobada
|
|
'rejected', -- Rechazada
|
|
'quoted', -- En proceso de cotizacion
|
|
'ordered', -- Convertida a OC
|
|
'cancelled' -- Cancelada
|
|
);
|
|
|
|
COMMENT ON TYPE purchasing_management.requisition_status IS 'Estados del ciclo de vida de una requisicion';
|
|
|
|
-- Estado de RFQs
|
|
CREATE TYPE purchasing_management.rfq_status AS ENUM (
|
|
'draft', -- Borrador
|
|
'sent', -- Enviada a proveedores
|
|
'receiving', -- Recibiendo cotizaciones
|
|
'evaluating', -- En evaluacion
|
|
'awarded', -- Adjudicada
|
|
'closed', -- Cerrada
|
|
'cancelled' -- Cancelada
|
|
);
|
|
|
|
COMMENT ON TYPE purchasing_management.rfq_status IS 'Estados de solicitud de cotizacion';
|
|
|
|
-- Estado de cotizaciones
|
|
CREATE TYPE purchasing_management.quotation_status AS ENUM (
|
|
'received', -- Recibida
|
|
'under_review', -- En revision
|
|
'accepted', -- Aceptada
|
|
'rejected', -- Rechazada
|
|
'expired' -- Vencida
|
|
);
|
|
|
|
COMMENT ON TYPE purchasing_management.quotation_status IS 'Estados de cotizacion de proveedor';
|
|
|
|
-- Estado de ordenes de compra
|
|
CREATE TYPE purchasing_management.po_status AS ENUM (
|
|
'draft', -- Borrador
|
|
'pending', -- Pendiente de aprobacion
|
|
'approved', -- Aprobada
|
|
'sent', -- Enviada a proveedor
|
|
'partially_received', -- Parcialmente recibida
|
|
'received', -- Recibida completamente
|
|
'cancelled', -- Cancelada
|
|
'closed' -- Cerrada
|
|
);
|
|
|
|
COMMENT ON TYPE purchasing_management.po_status IS 'Estados del ciclo de vida de orden de compra';
|
|
```
|
|
|
|
### 2. Tabla: requisitions
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- Schema: purchasing_management
|
|
-- Tabla: requisitions
|
|
-- Descripcion: Requisiciones de materiales para proyectos
|
|
-- Modulo: MAI-004
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS purchasing_management.requisitions (
|
|
-- Primary Key
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
|
|
-- Multi-tenant
|
|
tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Identificacion
|
|
code VARCHAR(30) NOT NULL,
|
|
|
|
-- Relaciones
|
|
project_id UUID NOT NULL, -- FK a projects schema
|
|
requested_by UUID NOT NULL REFERENCES core_users.users(id),
|
|
|
|
-- Datos principales
|
|
required_date DATE NOT NULL,
|
|
urgency VARCHAR(20) NOT NULL DEFAULT 'normal',
|
|
|
|
-- Items (JSONB estructura flexible)
|
|
items JSONB NOT NULL,
|
|
/* Estructura:
|
|
[{
|
|
materialId: UUID,
|
|
materialCode: string,
|
|
description: string,
|
|
quantity: number,
|
|
unit: string,
|
|
budgetedPrice: number,
|
|
budgetItemId: UUID,
|
|
notes: string
|
|
}]
|
|
*/
|
|
|
|
justification TEXT,
|
|
estimated_total DECIMAL(15,2) NOT NULL,
|
|
|
|
-- Estado y flujo de aprobacion
|
|
status purchasing_management.requisition_status NOT NULL DEFAULT 'draft',
|
|
approval_flow JSONB,
|
|
/* Estructura:
|
|
[{
|
|
level: number,
|
|
approverRole: string,
|
|
approverId: UUID,
|
|
approverName: string,
|
|
status: 'pending'|'approved'|'rejected',
|
|
comments: string,
|
|
approvedAt: timestamp
|
|
}]
|
|
*/
|
|
|
|
rejected_reason TEXT,
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_by UUID REFERENCES core_users.users(id),
|
|
updated_by UUID REFERENCES core_users.users(id),
|
|
|
|
-- Soft delete
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
deleted_at TIMESTAMPTZ,
|
|
deleted_by UUID REFERENCES core_users.users(id),
|
|
|
|
-- Constraints
|
|
CONSTRAINT chk_requisitions_urgency CHECK (urgency IN ('normal', 'urgent', 'emergency')),
|
|
CONSTRAINT chk_requisitions_estimated_total CHECK (estimated_total >= 0),
|
|
CONSTRAINT uq_requisitions_code_tenant UNIQUE (tenant_id, code)
|
|
);
|
|
|
|
-- Comentarios
|
|
COMMENT ON TABLE purchasing_management.requisitions IS 'Requisiciones de materiales para proyectos de construccion';
|
|
COMMENT ON COLUMN purchasing_management.requisitions.tenant_id IS 'ID del tenant (constructora)';
|
|
COMMENT ON COLUMN purchasing_management.requisitions.code IS 'Codigo unico de requisicion (ej: REQ-2025-00001)';
|
|
COMMENT ON COLUMN purchasing_management.requisitions.items IS 'Items solicitados en formato JSONB';
|
|
COMMENT ON COLUMN purchasing_management.requisitions.approval_flow IS 'Flujo de aprobacion multi-nivel segun monto';
|
|
|
|
-- Indices
|
|
CREATE INDEX idx_requisitions_tenant_id ON purchasing_management.requisitions(tenant_id);
|
|
CREATE INDEX idx_requisitions_project_id ON purchasing_management.requisitions(project_id);
|
|
CREATE INDEX idx_requisitions_status ON purchasing_management.requisitions(status) WHERE is_active = true;
|
|
CREATE INDEX idx_requisitions_requested_by ON purchasing_management.requisitions(requested_by);
|
|
CREATE INDEX idx_requisitions_created_at ON purchasing_management.requisitions(created_at DESC);
|
|
CREATE INDEX idx_requisitions_required_date ON purchasing_management.requisitions(required_date);
|
|
|
|
-- Indice GIN para busqueda en JSONB
|
|
CREATE INDEX idx_requisitions_items_gin ON purchasing_management.requisitions USING gin (items jsonb_path_ops);
|
|
|
|
-- RLS Policies
|
|
ALTER TABLE purchasing_management.requisitions ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY tenant_isolation_select ON purchasing_management.requisitions
|
|
FOR SELECT
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_insert ON purchasing_management.requisitions
|
|
FOR INSERT
|
|
WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_update ON purchasing_management.requisitions
|
|
FOR UPDATE
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_delete ON purchasing_management.requisitions
|
|
FOR DELETE
|
|
USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
```
|
|
|
|
### 3. Tabla: rfqs (Request for Quotation)
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- Schema: purchasing_management
|
|
-- Tabla: rfqs
|
|
-- Descripcion: Solicitudes de cotizacion a proveedores
|
|
-- Modulo: MAI-004
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS purchasing_management.rfqs (
|
|
-- Primary Key
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
|
|
-- Multi-tenant
|
|
tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Identificacion
|
|
code VARCHAR(30) NOT NULL,
|
|
|
|
-- Relaciones
|
|
requisition_id UUID REFERENCES purchasing_management.requisitions(id),
|
|
project_id UUID NOT NULL,
|
|
|
|
-- Datos principales
|
|
issue_date DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
closing_date DATE NOT NULL,
|
|
|
|
-- Items a cotizar
|
|
items JSONB NOT NULL,
|
|
/* Estructura:
|
|
[{
|
|
materialId: UUID,
|
|
materialCode: string,
|
|
description: string,
|
|
quantity: number,
|
|
unit: string,
|
|
technicalSpecs: string
|
|
}]
|
|
*/
|
|
|
|
-- Proveedores invitados
|
|
invited_suppliers JSONB NOT NULL,
|
|
/* Estructura:
|
|
[{
|
|
supplierId: UUID,
|
|
supplierName: string,
|
|
contactEmail: string,
|
|
invitedAt: timestamp,
|
|
notifiedAt: timestamp
|
|
}]
|
|
*/
|
|
|
|
-- Estado
|
|
status purchasing_management.rfq_status NOT NULL DEFAULT 'draft',
|
|
|
|
-- Terminos y condiciones
|
|
terms_conditions TEXT,
|
|
delivery_address TEXT,
|
|
payment_terms VARCHAR(100),
|
|
|
|
-- Configuracion
|
|
metadata JSONB DEFAULT '{}',
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_by UUID REFERENCES core_users.users(id),
|
|
updated_by UUID REFERENCES core_users.users(id),
|
|
|
|
-- Soft delete
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
deleted_at TIMESTAMPTZ,
|
|
deleted_by UUID REFERENCES core_users.users(id),
|
|
|
|
-- Constraints
|
|
CONSTRAINT chk_rfqs_closing_date CHECK (closing_date >= issue_date),
|
|
CONSTRAINT uq_rfqs_code_tenant UNIQUE (tenant_id, code)
|
|
);
|
|
|
|
COMMENT ON TABLE purchasing_management.rfqs IS 'Solicitudes de cotizacion (RFQ) a proveedores';
|
|
COMMENT ON COLUMN purchasing_management.rfqs.code IS 'Codigo unico RFQ (ej: RFQ-2025-00001)';
|
|
|
|
-- Indices
|
|
CREATE INDEX idx_rfqs_tenant_id ON purchasing_management.rfqs(tenant_id);
|
|
CREATE INDEX idx_rfqs_requisition_id ON purchasing_management.rfqs(requisition_id);
|
|
CREATE INDEX idx_rfqs_project_id ON purchasing_management.rfqs(project_id);
|
|
CREATE INDEX idx_rfqs_status ON purchasing_management.rfqs(status) WHERE is_active = true;
|
|
CREATE INDEX idx_rfqs_closing_date ON purchasing_management.rfqs(closing_date);
|
|
|
|
-- RLS Policies
|
|
ALTER TABLE purchasing_management.rfqs ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY tenant_isolation_select ON purchasing_management.rfqs
|
|
FOR SELECT USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_insert ON purchasing_management.rfqs
|
|
FOR INSERT WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_update ON purchasing_management.rfqs
|
|
FOR UPDATE USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_delete ON purchasing_management.rfqs
|
|
FOR DELETE USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
```
|
|
|
|
### 4. Tabla: quotations
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- Schema: purchasing_management
|
|
-- Tabla: quotations
|
|
-- Descripcion: Cotizaciones recibidas de proveedores
|
|
-- Modulo: MAI-004
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS purchasing_management.quotations (
|
|
-- Primary Key
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
|
|
-- Multi-tenant
|
|
tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Identificacion
|
|
code VARCHAR(30) NOT NULL,
|
|
|
|
-- Relaciones
|
|
rfq_id UUID NOT NULL REFERENCES purchasing_management.rfqs(id),
|
|
supplier_id UUID NOT NULL, -- FK a core_catalogs.contacts
|
|
|
|
-- Datos de cotizacion
|
|
quote_date DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
valid_until DATE NOT NULL,
|
|
quote_number VARCHAR(50), -- Numero de cotizacion del proveedor
|
|
|
|
-- Items cotizados
|
|
items JSONB NOT NULL,
|
|
/* Estructura:
|
|
[{
|
|
rfqItemId: string,
|
|
materialId: UUID,
|
|
description: string,
|
|
quantity: number,
|
|
unit: string,
|
|
unitPrice: number,
|
|
subtotal: number,
|
|
deliveryDays: number,
|
|
brand: string,
|
|
notes: string
|
|
}]
|
|
*/
|
|
|
|
-- Totales
|
|
subtotal DECIMAL(15,2) NOT NULL,
|
|
tax DECIMAL(15,2) NOT NULL DEFAULT 0,
|
|
total DECIMAL(15,2) NOT NULL,
|
|
|
|
-- Condiciones
|
|
delivery_days INT NOT NULL,
|
|
payment_terms VARCHAR(100),
|
|
payment_discount DECIMAL(5,2) DEFAULT 0,
|
|
warranty_days INT DEFAULT 30,
|
|
includes_delivery BOOLEAN DEFAULT true,
|
|
|
|
-- Estado
|
|
status purchasing_management.quotation_status NOT NULL DEFAULT 'received',
|
|
|
|
-- Evaluacion
|
|
evaluation_score DECIMAL(5,2), -- Score de evaluacion (0-100)
|
|
evaluation_notes TEXT,
|
|
evaluated_by UUID REFERENCES core_users.users(id),
|
|
evaluated_at TIMESTAMPTZ,
|
|
|
|
-- Adjuntos
|
|
attachments TEXT[],
|
|
|
|
notes TEXT,
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_by UUID REFERENCES core_users.users(id),
|
|
updated_by UUID REFERENCES core_users.users(id),
|
|
|
|
-- Soft delete
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
deleted_at TIMESTAMPTZ,
|
|
deleted_by UUID REFERENCES core_users.users(id),
|
|
|
|
-- Constraints
|
|
CONSTRAINT chk_quotations_totals CHECK (total = subtotal + tax),
|
|
CONSTRAINT chk_quotations_valid_until CHECK (valid_until >= quote_date),
|
|
CONSTRAINT chk_quotations_evaluation_score CHECK (evaluation_score IS NULL OR (evaluation_score >= 0 AND evaluation_score <= 100)),
|
|
CONSTRAINT uq_quotations_code_tenant UNIQUE (tenant_id, code)
|
|
);
|
|
|
|
COMMENT ON TABLE purchasing_management.quotations IS 'Cotizaciones recibidas de proveedores';
|
|
COMMENT ON COLUMN purchasing_management.quotations.evaluation_score IS 'Score de evaluacion 0-100 basado en precio, tiempo, calidad';
|
|
|
|
-- Indices
|
|
CREATE INDEX idx_quotations_tenant_id ON purchasing_management.quotations(tenant_id);
|
|
CREATE INDEX idx_quotations_rfq_id ON purchasing_management.quotations(rfq_id);
|
|
CREATE INDEX idx_quotations_supplier_id ON purchasing_management.quotations(supplier_id);
|
|
CREATE INDEX idx_quotations_status ON purchasing_management.quotations(status) WHERE is_active = true;
|
|
CREATE INDEX idx_quotations_valid_until ON purchasing_management.quotations(valid_until);
|
|
|
|
-- RLS Policies
|
|
ALTER TABLE purchasing_management.quotations ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY tenant_isolation_select ON purchasing_management.quotations
|
|
FOR SELECT USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_insert ON purchasing_management.quotations
|
|
FOR INSERT WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_update ON purchasing_management.quotations
|
|
FOR UPDATE USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_delete ON purchasing_management.quotations
|
|
FOR DELETE USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
```
|
|
|
|
### 5. Tabla: purchase_orders
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- Schema: purchasing_management
|
|
-- Tabla: purchase_orders
|
|
-- Descripcion: Ordenes de compra a proveedores
|
|
-- Modulo: MAI-004
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS purchasing_management.purchase_orders (
|
|
-- Primary Key
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
|
|
-- Multi-tenant
|
|
tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Identificacion
|
|
code VARCHAR(30) NOT NULL,
|
|
|
|
-- Relaciones
|
|
supplier_id UUID NOT NULL, -- FK a core_catalogs.contacts
|
|
project_id UUID NOT NULL,
|
|
requisition_id UUID REFERENCES purchasing_management.requisitions(id),
|
|
rfq_id UUID REFERENCES purchasing_management.rfqs(id),
|
|
quotation_id UUID REFERENCES purchasing_management.quotations(id),
|
|
|
|
-- Fechas
|
|
order_date DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
delivery_date DATE NOT NULL,
|
|
|
|
-- Entrega
|
|
delivery_address TEXT NOT NULL,
|
|
delivery_contact VARCHAR(100),
|
|
delivery_phone VARCHAR(20),
|
|
|
|
-- Items
|
|
items JSONB NOT NULL,
|
|
/* Estructura:
|
|
[{
|
|
materialId: UUID,
|
|
materialCode: string,
|
|
description: string,
|
|
quantity: number,
|
|
unit: string,
|
|
unitPrice: number,
|
|
subtotal: number,
|
|
budgetItemId: UUID,
|
|
notes: string
|
|
}]
|
|
*/
|
|
|
|
-- Totales
|
|
subtotal DECIMAL(15,2) NOT NULL,
|
|
tax DECIMAL(15,2) NOT NULL DEFAULT 0,
|
|
total DECIMAL(15,2) NOT NULL,
|
|
|
|
-- Estado
|
|
status purchasing_management.po_status NOT NULL DEFAULT 'draft',
|
|
|
|
-- Condiciones de pago
|
|
payment_terms VARCHAR(100),
|
|
payment_terms_days INT DEFAULT 30,
|
|
early_payment_discount DECIMAL(5,2) DEFAULT 0,
|
|
requires_advance BOOLEAN DEFAULT false,
|
|
advance_percentage DECIMAL(5,2),
|
|
|
|
-- Otras condiciones
|
|
warranty_days INT DEFAULT 30,
|
|
includes_delivery BOOLEAN DEFAULT true,
|
|
special_conditions TEXT,
|
|
|
|
-- Aprobacion
|
|
approved_by UUID REFERENCES core_users.users(id),
|
|
approved_at TIMESTAMPTZ,
|
|
|
|
-- Envio
|
|
sent_at TIMESTAMPTZ,
|
|
sent_by UUID REFERENCES core_users.users(id),
|
|
|
|
-- Adjuntos
|
|
attachments TEXT[],
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_by UUID REFERENCES core_users.users(id),
|
|
updated_by UUID REFERENCES core_users.users(id),
|
|
|
|
-- Soft delete
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
deleted_at TIMESTAMPTZ,
|
|
deleted_by UUID REFERENCES core_users.users(id),
|
|
|
|
-- Constraints
|
|
CONSTRAINT chk_purchase_orders_totals CHECK (total = subtotal + tax),
|
|
CONSTRAINT chk_purchase_orders_delivery_date CHECK (delivery_date >= order_date),
|
|
CONSTRAINT chk_purchase_orders_advance CHECK (
|
|
(requires_advance = false AND advance_percentage IS NULL) OR
|
|
(requires_advance = true AND advance_percentage > 0 AND advance_percentage <= 100)
|
|
),
|
|
CONSTRAINT uq_purchase_orders_code_tenant UNIQUE (tenant_id, code)
|
|
);
|
|
|
|
COMMENT ON TABLE purchasing_management.purchase_orders IS 'Ordenes de compra a proveedores';
|
|
COMMENT ON COLUMN purchasing_management.purchase_orders.code IS 'Codigo unico OC (ej: OC-2025-00001)';
|
|
|
|
-- Indices
|
|
CREATE INDEX idx_purchase_orders_tenant_id ON purchasing_management.purchase_orders(tenant_id);
|
|
CREATE INDEX idx_purchase_orders_supplier_id ON purchasing_management.purchase_orders(supplier_id);
|
|
CREATE INDEX idx_purchase_orders_project_id ON purchasing_management.purchase_orders(project_id);
|
|
CREATE INDEX idx_purchase_orders_status ON purchasing_management.purchase_orders(status) WHERE is_active = true;
|
|
CREATE INDEX idx_purchase_orders_delivery_date ON purchasing_management.purchase_orders(delivery_date);
|
|
CREATE INDEX idx_purchase_orders_order_date ON purchasing_management.purchase_orders(order_date DESC);
|
|
|
|
-- RLS Policies
|
|
ALTER TABLE purchasing_management.purchase_orders ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY tenant_isolation_select ON purchasing_management.purchase_orders
|
|
FOR SELECT USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_insert ON purchasing_management.purchase_orders
|
|
FOR INSERT WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_update ON purchasing_management.purchase_orders
|
|
FOR UPDATE USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_delete ON purchasing_management.purchase_orders
|
|
FOR DELETE USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
```
|
|
|
|
### 6. Tabla: po_receipts
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- Schema: purchasing_management
|
|
-- Tabla: po_receipts
|
|
-- Descripcion: Recepciones de material de ordenes de compra
|
|
-- Modulo: MAI-004
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS purchasing_management.po_receipts (
|
|
-- Primary Key
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
|
|
-- Multi-tenant
|
|
tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Identificacion
|
|
code VARCHAR(30) NOT NULL,
|
|
|
|
-- Relaciones
|
|
purchase_order_id UUID NOT NULL REFERENCES purchasing_management.purchase_orders(id),
|
|
warehouse_id UUID NOT NULL, -- FK a inventory_management.warehouses
|
|
|
|
-- Datos de recepcion
|
|
receipt_date DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
received_by UUID NOT NULL REFERENCES core_users.users(id),
|
|
|
|
-- Items recibidos
|
|
items JSONB NOT NULL,
|
|
/* Estructura:
|
|
[{
|
|
poItemId: string,
|
|
materialId: UUID,
|
|
materialCode: string,
|
|
description: string,
|
|
orderedQuantity: number,
|
|
receivedQuantity: number,
|
|
acceptedQuantity: number,
|
|
rejectedQuantity: number,
|
|
unit: string,
|
|
unitCost: number,
|
|
rejectionReason: string
|
|
}]
|
|
*/
|
|
|
|
-- Informacion de entrega
|
|
delivery_note VARCHAR(50),
|
|
transport_company VARCHAR(100),
|
|
driver_name VARCHAR(100),
|
|
vehicle_plates VARCHAR(20),
|
|
|
|
notes TEXT,
|
|
attachments TEXT[],
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_by UUID REFERENCES core_users.users(id),
|
|
|
|
-- Constraints
|
|
CONSTRAINT uq_po_receipts_code_tenant UNIQUE (tenant_id, code)
|
|
);
|
|
|
|
COMMENT ON TABLE purchasing_management.po_receipts IS 'Recepciones de material de ordenes de compra';
|
|
COMMENT ON COLUMN purchasing_management.po_receipts.code IS 'Codigo unico recepcion (ej: REC-2025-00001)';
|
|
|
|
-- Indices
|
|
CREATE INDEX idx_po_receipts_tenant_id ON purchasing_management.po_receipts(tenant_id);
|
|
CREATE INDEX idx_po_receipts_purchase_order_id ON purchasing_management.po_receipts(purchase_order_id);
|
|
CREATE INDEX idx_po_receipts_warehouse_id ON purchasing_management.po_receipts(warehouse_id);
|
|
CREATE INDEX idx_po_receipts_receipt_date ON purchasing_management.po_receipts(receipt_date DESC);
|
|
|
|
-- RLS Policies
|
|
ALTER TABLE purchasing_management.po_receipts ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY tenant_isolation_select ON purchasing_management.po_receipts
|
|
FOR SELECT USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_insert ON purchasing_management.po_receipts
|
|
FOR INSERT WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_update ON purchasing_management.po_receipts
|
|
FOR UPDATE USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_delete ON purchasing_management.po_receipts
|
|
FOR DELETE USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
```
|
|
|
|
---
|
|
|
|
## Schema: inventory_management
|
|
|
|
### 1. ENUMs
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- ENUMs - Inventory Management
|
|
-- ============================================================================
|
|
|
|
-- Tipo de almacen
|
|
CREATE TYPE inventory_management.warehouse_type AS ENUM (
|
|
'general', -- Almacen general corporativo
|
|
'project', -- Almacen de proyecto especifico
|
|
'temporary' -- Almacen temporal
|
|
);
|
|
|
|
COMMENT ON TYPE inventory_management.warehouse_type IS 'Tipos de almacen';
|
|
|
|
-- Tipo de movimiento
|
|
CREATE TYPE inventory_management.movement_type AS ENUM (
|
|
'entry', -- Entrada de material
|
|
'exit', -- Salida de material
|
|
'transfer_out', -- Traspaso salida
|
|
'transfer_in', -- Traspaso entrada
|
|
'adjustment' -- Ajuste de inventario
|
|
);
|
|
|
|
COMMENT ON TYPE inventory_management.movement_type IS 'Tipos de movimiento de inventario';
|
|
|
|
-- Origen del movimiento
|
|
CREATE TYPE inventory_management.movement_source AS ENUM (
|
|
'purchase_order', -- Orden de compra
|
|
'transfer', -- Traspaso entre almacenes
|
|
'return', -- Devolucion
|
|
'adjustment', -- Ajuste
|
|
'production', -- Produccion
|
|
'consumption' -- Consumo en proyecto
|
|
);
|
|
|
|
COMMENT ON TYPE inventory_management.movement_source IS 'Origen del movimiento de inventario';
|
|
|
|
-- Estado de traspaso
|
|
CREATE TYPE inventory_management.transfer_status AS ENUM (
|
|
'pending', -- Pendiente
|
|
'in_transit', -- En transito
|
|
'received', -- Recibido
|
|
'cancelled' -- Cancelado
|
|
);
|
|
|
|
COMMENT ON TYPE inventory_management.transfer_status IS 'Estados de traspaso entre almacenes';
|
|
```
|
|
|
|
### 2. Tabla: warehouses
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- Schema: inventory_management
|
|
-- Tabla: warehouses
|
|
-- Descripcion: Almacenes generales, de proyecto y temporales
|
|
-- Modulo: MAI-004
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS inventory_management.warehouses (
|
|
-- Primary Key
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
|
|
-- Multi-tenant
|
|
tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Identificacion
|
|
code VARCHAR(20) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
|
|
-- Tipo
|
|
type inventory_management.warehouse_type NOT NULL DEFAULT 'general',
|
|
|
|
-- Relacion con proyecto (solo si type = 'project')
|
|
project_id UUID,
|
|
|
|
-- Ubicacion
|
|
address TEXT,
|
|
city VARCHAR(100),
|
|
state VARCHAR(100),
|
|
postal_code VARCHAR(10),
|
|
|
|
-- Responsable
|
|
managed_by UUID REFERENCES core_users.users(id),
|
|
|
|
-- Dimensiones
|
|
total_area DECIMAL(10,2),
|
|
covered_area DECIMAL(10,2),
|
|
|
|
-- Configuracion
|
|
settings JSONB DEFAULT '{}',
|
|
/* Estructura:
|
|
{
|
|
allowNegativeStock: boolean,
|
|
requiresLocationTracking: boolean,
|
|
defaultLocationId: UUID,
|
|
autoGenerateLocations: boolean
|
|
}
|
|
*/
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_by UUID REFERENCES core_users.users(id),
|
|
updated_by UUID REFERENCES core_users.users(id),
|
|
|
|
-- Soft delete
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
deleted_at TIMESTAMPTZ,
|
|
deleted_by UUID REFERENCES core_users.users(id),
|
|
|
|
-- Constraints
|
|
CONSTRAINT chk_warehouses_project CHECK (
|
|
(type = 'project' AND project_id IS NOT NULL) OR
|
|
(type != 'project')
|
|
),
|
|
CONSTRAINT uq_warehouses_code_tenant UNIQUE (tenant_id, code)
|
|
);
|
|
|
|
COMMENT ON TABLE inventory_management.warehouses IS 'Almacenes generales, de proyecto y temporales';
|
|
COMMENT ON COLUMN inventory_management.warehouses.type IS 'Tipo: general, project, temporary';
|
|
|
|
-- Indices
|
|
CREATE INDEX idx_warehouses_tenant_id ON inventory_management.warehouses(tenant_id);
|
|
CREATE INDEX idx_warehouses_type ON inventory_management.warehouses(type) WHERE is_active = true;
|
|
CREATE INDEX idx_warehouses_project_id ON inventory_management.warehouses(project_id) WHERE project_id IS NOT NULL;
|
|
CREATE INDEX idx_warehouses_managed_by ON inventory_management.warehouses(managed_by);
|
|
|
|
-- RLS Policies
|
|
ALTER TABLE inventory_management.warehouses ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY tenant_isolation_select ON inventory_management.warehouses
|
|
FOR SELECT USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_insert ON inventory_management.warehouses
|
|
FOR INSERT WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_update ON inventory_management.warehouses
|
|
FOR UPDATE USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_delete ON inventory_management.warehouses
|
|
FOR DELETE USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
```
|
|
|
|
### 3. Tabla: warehouse_locations
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- Schema: inventory_management
|
|
-- Tabla: warehouse_locations
|
|
-- Descripcion: Ubicaciones dentro de almacenes (ej: A-01, B-03)
|
|
-- Modulo: MAI-004
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS inventory_management.warehouse_locations (
|
|
-- Primary Key
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
|
|
-- Multi-tenant
|
|
tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Relacion
|
|
warehouse_id UUID NOT NULL REFERENCES inventory_management.warehouses(id) ON DELETE CASCADE,
|
|
|
|
-- Identificacion
|
|
zone VARCHAR(10) NOT NULL, -- A, B, C, D
|
|
position VARCHAR(10) NOT NULL, -- 01, 02, 03
|
|
code VARCHAR(20) NOT NULL, -- A-01, B-03
|
|
|
|
description VARCHAR(255),
|
|
capacity_m3 DECIMAL(10,2),
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
|
|
-- Constraints
|
|
CONSTRAINT uq_warehouse_locations_code UNIQUE (warehouse_id, code)
|
|
);
|
|
|
|
COMMENT ON TABLE inventory_management.warehouse_locations IS 'Ubicaciones fisicas dentro de almacenes';
|
|
COMMENT ON COLUMN inventory_management.warehouse_locations.code IS 'Codigo de ubicacion (ej: A-01, B-03)';
|
|
|
|
-- Indices
|
|
CREATE INDEX idx_warehouse_locations_tenant_id ON inventory_management.warehouse_locations(tenant_id);
|
|
CREATE INDEX idx_warehouse_locations_warehouse_id ON inventory_management.warehouse_locations(warehouse_id);
|
|
CREATE INDEX idx_warehouse_locations_code ON inventory_management.warehouse_locations(code);
|
|
|
|
-- RLS Policies
|
|
ALTER TABLE inventory_management.warehouse_locations ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY tenant_isolation_select ON inventory_management.warehouse_locations
|
|
FOR SELECT USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_insert ON inventory_management.warehouse_locations
|
|
FOR INSERT WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_update ON inventory_management.warehouse_locations
|
|
FOR UPDATE USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_delete ON inventory_management.warehouse_locations
|
|
FOR DELETE USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
```
|
|
|
|
### 4. Tabla: inventory_movements
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- Schema: inventory_management
|
|
-- Tabla: inventory_movements
|
|
-- Descripcion: Movimientos de inventario (entradas, salidas, traspasos)
|
|
-- Modulo: MAI-004
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS inventory_management.inventory_movements (
|
|
-- Primary Key
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
|
|
-- Multi-tenant
|
|
tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Identificacion
|
|
code VARCHAR(30) NOT NULL,
|
|
|
|
-- Almacen
|
|
warehouse_id UUID NOT NULL REFERENCES inventory_management.warehouses(id),
|
|
|
|
-- Tipo de movimiento
|
|
type inventory_management.movement_type NOT NULL,
|
|
movement_date DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
|
|
-- Origen del movimiento
|
|
source_type inventory_management.movement_source,
|
|
source_id UUID,
|
|
|
|
-- Para salidas a proyecto
|
|
project_id UUID,
|
|
budget_item_id UUID,
|
|
|
|
-- Para traspasos
|
|
transfer_warehouse_id UUID REFERENCES inventory_management.warehouses(id),
|
|
transfer_status inventory_management.transfer_status,
|
|
received_at TIMESTAMPTZ,
|
|
|
|
-- Items
|
|
items JSONB NOT NULL,
|
|
/* Estructura:
|
|
[{
|
|
materialId: UUID,
|
|
materialCode: string,
|
|
description: string,
|
|
quantity: number,
|
|
unit: string,
|
|
unitCost: number,
|
|
totalCost: number,
|
|
lotId: UUID,
|
|
locationId: UUID
|
|
}]
|
|
*/
|
|
|
|
total_value DECIMAL(15,2) NOT NULL,
|
|
|
|
notes TEXT,
|
|
attachments TEXT[],
|
|
|
|
-- Autorizacion
|
|
authorized_by UUID REFERENCES core_users.users(id),
|
|
recorded_by UUID NOT NULL REFERENCES core_users.users(id),
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_by UUID REFERENCES core_users.users(id),
|
|
|
|
-- Constraints
|
|
CONSTRAINT chk_inventory_movements_transfer CHECK (
|
|
(type IN ('transfer_out', 'transfer_in') AND transfer_warehouse_id IS NOT NULL AND transfer_status IS NOT NULL) OR
|
|
(type NOT IN ('transfer_out', 'transfer_in'))
|
|
),
|
|
CONSTRAINT chk_inventory_movements_exit_project CHECK (
|
|
(type = 'exit' AND source_type = 'consumption' AND project_id IS NOT NULL) OR
|
|
(type != 'exit' OR source_type != 'consumption')
|
|
),
|
|
CONSTRAINT uq_inventory_movements_code_tenant UNIQUE (tenant_id, code)
|
|
);
|
|
|
|
COMMENT ON TABLE inventory_management.inventory_movements IS 'Registro de todos los movimientos de inventario';
|
|
COMMENT ON COLUMN inventory_management.inventory_movements.type IS 'Tipo: entry, exit, transfer_out, transfer_in, adjustment';
|
|
COMMENT ON COLUMN inventory_management.inventory_movements.source_type IS 'Origen: purchase_order, transfer, return, adjustment, production';
|
|
|
|
-- Indices
|
|
CREATE INDEX idx_inventory_movements_tenant_id ON inventory_management.inventory_movements(tenant_id);
|
|
CREATE INDEX idx_inventory_movements_warehouse_id ON inventory_management.inventory_movements(warehouse_id);
|
|
CREATE INDEX idx_inventory_movements_type ON inventory_management.inventory_movements(type);
|
|
CREATE INDEX idx_inventory_movements_date ON inventory_management.inventory_movements(movement_date DESC);
|
|
CREATE INDEX idx_inventory_movements_project_id ON inventory_management.inventory_movements(project_id) WHERE project_id IS NOT NULL;
|
|
CREATE INDEX idx_inventory_movements_source ON inventory_management.inventory_movements(source_type, source_id);
|
|
|
|
-- Indice GIN para busqueda en items
|
|
CREATE INDEX idx_inventory_movements_items_gin ON inventory_management.inventory_movements USING gin (items jsonb_path_ops);
|
|
|
|
-- RLS Policies
|
|
ALTER TABLE inventory_management.inventory_movements ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY tenant_isolation_select ON inventory_management.inventory_movements
|
|
FOR SELECT USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_insert ON inventory_management.inventory_movements
|
|
FOR INSERT WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_update ON inventory_management.inventory_movements
|
|
FOR UPDATE USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_delete ON inventory_management.inventory_movements
|
|
FOR DELETE USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
```
|
|
|
|
### 5. Tabla: stock_levels
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- Schema: inventory_management
|
|
-- Tabla: stock_levels
|
|
-- Descripcion: Niveles de stock actuales por almacen y material
|
|
-- Modulo: MAI-004
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS inventory_management.stock_levels (
|
|
-- Primary Key
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
|
|
-- Multi-tenant
|
|
tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Relaciones
|
|
warehouse_id UUID NOT NULL REFERENCES inventory_management.warehouses(id),
|
|
material_id UUID NOT NULL, -- FK a core_catalogs.contacts o materials
|
|
location_id UUID REFERENCES inventory_management.warehouse_locations(id),
|
|
|
|
-- Cantidades
|
|
quantity DECIMAL(12,4) NOT NULL DEFAULT 0,
|
|
reserved_quantity DECIMAL(12,4) NOT NULL DEFAULT 0,
|
|
available_quantity DECIMAL(12,4) GENERATED ALWAYS AS (quantity - reserved_quantity) STORED,
|
|
|
|
-- Valoracion
|
|
average_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
|
|
total_value DECIMAL(15,2) GENERATED ALWAYS AS (quantity * average_cost) STORED,
|
|
|
|
-- Fechas de ultimo movimiento
|
|
last_movement_date DATE,
|
|
last_entry_date DATE,
|
|
last_exit_date DATE,
|
|
|
|
-- Auditoria
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
-- Constraints
|
|
CONSTRAINT chk_stock_levels_quantity CHECK (quantity >= 0),
|
|
CONSTRAINT chk_stock_levels_reserved CHECK (reserved_quantity >= 0 AND reserved_quantity <= quantity),
|
|
CONSTRAINT uq_stock_levels_warehouse_material UNIQUE (warehouse_id, material_id)
|
|
);
|
|
|
|
COMMENT ON TABLE inventory_management.stock_levels IS 'Niveles de stock actuales por almacen y material';
|
|
COMMENT ON COLUMN inventory_management.stock_levels.quantity IS 'Cantidad total en almacen';
|
|
COMMENT ON COLUMN inventory_management.stock_levels.reserved_quantity IS 'Cantidad reservada para salidas';
|
|
COMMENT ON COLUMN inventory_management.stock_levels.available_quantity IS 'Cantidad disponible (quantity - reserved_quantity)';
|
|
COMMENT ON COLUMN inventory_management.stock_levels.average_cost IS 'Costo promedio ponderado';
|
|
|
|
-- Indices
|
|
CREATE INDEX idx_stock_levels_tenant_id ON inventory_management.stock_levels(tenant_id);
|
|
CREATE INDEX idx_stock_levels_warehouse_id ON inventory_management.stock_levels(warehouse_id);
|
|
CREATE INDEX idx_stock_levels_material_id ON inventory_management.stock_levels(material_id);
|
|
CREATE INDEX idx_stock_levels_location_id ON inventory_management.stock_levels(location_id) WHERE location_id IS NOT NULL;
|
|
CREATE INDEX idx_stock_levels_last_movement ON inventory_management.stock_levels(last_movement_date DESC);
|
|
|
|
-- Indice para buscar stock bajo
|
|
CREATE INDEX idx_stock_levels_quantity ON inventory_management.stock_levels(quantity) WHERE quantity > 0;
|
|
|
|
-- RLS Policies
|
|
ALTER TABLE inventory_management.stock_levels ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY tenant_isolation_select ON inventory_management.stock_levels
|
|
FOR SELECT USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_insert ON inventory_management.stock_levels
|
|
FOR INSERT WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_update ON inventory_management.stock_levels
|
|
FOR UPDATE USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_delete ON inventory_management.stock_levels
|
|
FOR DELETE USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
```
|
|
|
|
### 6. Tabla: inventory_lots
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- Schema: inventory_management
|
|
-- Tabla: inventory_lots
|
|
-- Descripcion: Lotes de inventario para valoracion PEPS
|
|
-- Modulo: MAI-004
|
|
-- ============================================================================
|
|
|
|
CREATE TABLE IF NOT EXISTS inventory_management.inventory_lots (
|
|
-- Primary Key
|
|
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
|
|
|
-- Multi-tenant
|
|
tenant_id UUID NOT NULL REFERENCES core_tenants.tenants(id) ON DELETE CASCADE,
|
|
|
|
-- Relaciones
|
|
warehouse_id UUID NOT NULL REFERENCES inventory_management.warehouses(id),
|
|
material_id UUID NOT NULL,
|
|
|
|
-- Identificacion del lote
|
|
lot_number VARCHAR(50),
|
|
entry_date DATE NOT NULL DEFAULT CURRENT_DATE,
|
|
|
|
-- Cantidades
|
|
quantity DECIMAL(12,4) NOT NULL,
|
|
remaining_quantity DECIMAL(12,4) NOT NULL,
|
|
unit_cost DECIMAL(12,2) NOT NULL,
|
|
|
|
-- Origen
|
|
source_type inventory_management.movement_source NOT NULL,
|
|
source_id UUID NOT NULL,
|
|
|
|
-- Estado
|
|
is_depleted BOOLEAN GENERATED ALWAYS AS (remaining_quantity <= 0.0001) STORED,
|
|
|
|
-- Auditoria
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
-- Constraints
|
|
CONSTRAINT chk_inventory_lots_remaining CHECK (remaining_quantity >= 0 AND remaining_quantity <= quantity),
|
|
CONSTRAINT chk_inventory_lots_unit_cost CHECK (unit_cost >= 0)
|
|
);
|
|
|
|
COMMENT ON TABLE inventory_management.inventory_lots IS 'Lotes de inventario para valoracion PEPS (Primeras Entradas, Primeras Salidas)';
|
|
COMMENT ON COLUMN inventory_management.inventory_lots.lot_number IS 'Numero de lote del proveedor (opcional)';
|
|
COMMENT ON COLUMN inventory_management.inventory_lots.remaining_quantity IS 'Cantidad restante del lote';
|
|
COMMENT ON COLUMN inventory_management.inventory_lots.is_depleted IS 'Si el lote se agoto completamente';
|
|
|
|
-- Indices
|
|
CREATE INDEX idx_inventory_lots_tenant_id ON inventory_management.inventory_lots(tenant_id);
|
|
CREATE INDEX idx_inventory_lots_warehouse_material ON inventory_management.inventory_lots(warehouse_id, material_id, entry_date);
|
|
CREATE INDEX idx_inventory_lots_not_depleted ON inventory_management.inventory_lots(warehouse_id, material_id, entry_date)
|
|
WHERE is_depleted = false;
|
|
CREATE INDEX idx_inventory_lots_source ON inventory_management.inventory_lots(source_type, source_id);
|
|
|
|
-- RLS Policies
|
|
ALTER TABLE inventory_management.inventory_lots ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY tenant_isolation_select ON inventory_management.inventory_lots
|
|
FOR SELECT USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_insert ON inventory_management.inventory_lots
|
|
FOR INSERT WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_update ON inventory_management.inventory_lots
|
|
FOR UPDATE USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
|
|
CREATE POLICY tenant_isolation_delete ON inventory_management.inventory_lots
|
|
FOR DELETE USING (tenant_id = current_setting('app.current_tenant_id', true)::uuid);
|
|
```
|
|
|
|
---
|
|
|
|
## Funciones y Triggers
|
|
|
|
### 1. Trigger: updated_at
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- TRIGGER: Actualizar updated_at automaticamente
|
|
-- ============================================================================
|
|
|
|
-- Aplicar a tablas con updated_at
|
|
CREATE TRIGGER trg_requisitions_update_timestamp
|
|
BEFORE UPDATE ON purchasing_management.requisitions
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION core_shared.set_updated_at();
|
|
|
|
CREATE TRIGGER trg_rfqs_update_timestamp
|
|
BEFORE UPDATE ON purchasing_management.rfqs
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION core_shared.set_updated_at();
|
|
|
|
CREATE TRIGGER trg_quotations_update_timestamp
|
|
BEFORE UPDATE ON purchasing_management.quotations
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION core_shared.set_updated_at();
|
|
|
|
CREATE TRIGGER trg_purchase_orders_update_timestamp
|
|
BEFORE UPDATE ON purchasing_management.purchase_orders
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION core_shared.set_updated_at();
|
|
|
|
CREATE TRIGGER trg_warehouses_update_timestamp
|
|
BEFORE UPDATE ON inventory_management.warehouses
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION core_shared.set_updated_at();
|
|
|
|
CREATE TRIGGER trg_stock_levels_update_timestamp
|
|
BEFORE UPDATE ON inventory_management.stock_levels
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION core_shared.set_updated_at();
|
|
```
|
|
|
|
### 2. Trigger: Actualizar estado de OC al recibir material
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- TRIGGER: Actualizar estado de orden de compra segun recepciones
|
|
-- ============================================================================
|
|
|
|
CREATE OR REPLACE FUNCTION purchasing_management.update_po_status_on_receipt()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
v_total_ordered DECIMAL(15,4);
|
|
v_total_received DECIMAL(15,4);
|
|
v_po_item JSONB;
|
|
v_receipt_item JSONB;
|
|
BEGIN
|
|
-- Calcular total ordenado
|
|
SELECT COALESCE(SUM((item->>'quantity')::DECIMAL), 0)
|
|
INTO v_total_ordered
|
|
FROM purchasing_management.purchase_orders po,
|
|
JSONB_ARRAY_ELEMENTS(po.items) AS item
|
|
WHERE po.id = NEW.purchase_order_id;
|
|
|
|
-- Calcular total recibido (aceptado) de todas las recepciones
|
|
SELECT COALESCE(SUM((item->>'acceptedQuantity')::DECIMAL), 0)
|
|
INTO v_total_received
|
|
FROM purchasing_management.po_receipts pr,
|
|
JSONB_ARRAY_ELEMENTS(pr.items) AS item
|
|
WHERE pr.purchase_order_id = NEW.purchase_order_id;
|
|
|
|
-- Actualizar estado de la OC
|
|
IF v_total_received >= v_total_ordered THEN
|
|
UPDATE purchasing_management.purchase_orders
|
|
SET status = 'received',
|
|
updated_at = now()
|
|
WHERE id = NEW.purchase_order_id
|
|
AND status != 'received';
|
|
ELSIF v_total_received > 0 THEN
|
|
UPDATE purchasing_management.purchase_orders
|
|
SET status = 'partially_received',
|
|
updated_at = now()
|
|
WHERE id = NEW.purchase_order_id
|
|
AND status NOT IN ('received', 'partially_received');
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_po_receipts_update_po_status
|
|
AFTER INSERT ON purchasing_management.po_receipts
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION purchasing_management.update_po_status_on_receipt();
|
|
```
|
|
|
|
### 3. Funcion: Actualizacion de stock y creacion de lotes
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- FUNCION: Actualizar stock y crear lotes PEPS al recibir material
|
|
-- ============================================================================
|
|
|
|
CREATE OR REPLACE FUNCTION inventory_management.update_stock_on_entry(
|
|
p_tenant_id UUID,
|
|
p_warehouse_id UUID,
|
|
p_material_id UUID,
|
|
p_quantity DECIMAL(12,4),
|
|
p_unit_cost DECIMAL(12,2),
|
|
p_source_type inventory_management.movement_source,
|
|
p_source_id UUID,
|
|
p_lot_number VARCHAR(50) DEFAULT NULL
|
|
)
|
|
RETURNS VOID AS $$
|
|
DECLARE
|
|
v_current_quantity DECIMAL(12,4);
|
|
v_current_value DECIMAL(15,2);
|
|
v_new_quantity DECIMAL(12,4);
|
|
v_new_average_cost DECIMAL(12,2);
|
|
BEGIN
|
|
-- Obtener stock actual
|
|
SELECT quantity, total_value
|
|
INTO v_current_quantity, v_current_value
|
|
FROM inventory_management.stock_levels
|
|
WHERE warehouse_id = p_warehouse_id
|
|
AND material_id = p_material_id;
|
|
|
|
-- Si no existe, crear registro de stock
|
|
IF NOT FOUND THEN
|
|
INSERT INTO inventory_management.stock_levels (
|
|
tenant_id, warehouse_id, material_id,
|
|
quantity, reserved_quantity, average_cost,
|
|
last_entry_date, last_movement_date
|
|
) VALUES (
|
|
p_tenant_id, p_warehouse_id, p_material_id,
|
|
p_quantity, 0, p_unit_cost,
|
|
CURRENT_DATE, CURRENT_DATE
|
|
);
|
|
ELSE
|
|
-- Calcular nueva cantidad y costo promedio
|
|
v_new_quantity := v_current_quantity + p_quantity;
|
|
v_new_average_cost := (v_current_value + (p_quantity * p_unit_cost)) / v_new_quantity;
|
|
|
|
-- Actualizar stock
|
|
UPDATE inventory_management.stock_levels
|
|
SET quantity = v_new_quantity,
|
|
average_cost = v_new_average_cost,
|
|
last_entry_date = CURRENT_DATE,
|
|
last_movement_date = CURRENT_DATE,
|
|
updated_at = now()
|
|
WHERE warehouse_id = p_warehouse_id
|
|
AND material_id = p_material_id;
|
|
END IF;
|
|
|
|
-- Crear lote PEPS
|
|
INSERT INTO inventory_management.inventory_lots (
|
|
tenant_id, warehouse_id, material_id,
|
|
lot_number, entry_date,
|
|
quantity, remaining_quantity, unit_cost,
|
|
source_type, source_id
|
|
) VALUES (
|
|
p_tenant_id, p_warehouse_id, p_material_id,
|
|
p_lot_number, CURRENT_DATE,
|
|
p_quantity, p_quantity, p_unit_cost,
|
|
p_source_type, p_source_id
|
|
);
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION inventory_management.update_stock_on_entry IS 'Actualiza stock y crea lote PEPS al recibir material';
|
|
```
|
|
|
|
### 4. Funcion: Valoracion PEPS en salidas
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- FUNCION: Calcular costo PEPS en salidas de inventario
|
|
-- ============================================================================
|
|
|
|
CREATE OR REPLACE FUNCTION inventory_management.calculate_fifo_cost(
|
|
p_warehouse_id UUID,
|
|
p_material_id UUID,
|
|
p_quantity DECIMAL(12,4)
|
|
)
|
|
RETURNS TABLE (
|
|
lot_id UUID,
|
|
quantity_consumed DECIMAL(12,4),
|
|
unit_cost DECIMAL(12,2),
|
|
total_cost DECIMAL(15,2)
|
|
) AS $$
|
|
DECLARE
|
|
v_remaining DECIMAL(12,4) := p_quantity;
|
|
v_lot RECORD;
|
|
v_consume_qty DECIMAL(12,4);
|
|
BEGIN
|
|
-- Iterar lotes en orden FIFO (primero el mas antiguo)
|
|
FOR v_lot IN
|
|
SELECT id, remaining_quantity, unit_cost
|
|
FROM inventory_management.inventory_lots
|
|
WHERE warehouse_id = p_warehouse_id
|
|
AND material_id = p_material_id
|
|
AND is_depleted = false
|
|
ORDER BY entry_date ASC, created_at ASC
|
|
LOOP
|
|
EXIT WHEN v_remaining <= 0;
|
|
|
|
-- Calcular cuanto consumir de este lote
|
|
v_consume_qty := LEAST(v_lot.remaining_quantity, v_remaining);
|
|
|
|
-- Retornar informacion del consumo
|
|
lot_id := v_lot.id;
|
|
quantity_consumed := v_consume_qty;
|
|
unit_cost := v_lot.unit_cost;
|
|
total_cost := v_consume_qty * v_lot.unit_cost;
|
|
|
|
RETURN NEXT;
|
|
|
|
v_remaining := v_remaining - v_consume_qty;
|
|
END LOOP;
|
|
|
|
-- Verificar si hay stock suficiente
|
|
IF v_remaining > 0.0001 THEN
|
|
RAISE EXCEPTION 'Stock insuficiente. Faltan % unidades', v_remaining;
|
|
END IF;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION inventory_management.calculate_fifo_cost IS 'Calcula costo PEPS y distribucion por lotes para una salida';
|
|
```
|
|
|
|
### 5. Funcion: Salida de inventario con PEPS
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- FUNCION: Procesar salida de inventario con valoracion PEPS
|
|
-- ============================================================================
|
|
|
|
CREATE OR REPLACE FUNCTION inventory_management.update_stock_on_exit(
|
|
p_tenant_id UUID,
|
|
p_warehouse_id UUID,
|
|
p_material_id UUID,
|
|
p_quantity DECIMAL(12,4)
|
|
)
|
|
RETURNS TABLE (
|
|
total_cost DECIMAL(15,2),
|
|
average_cost DECIMAL(12,2)
|
|
) AS $$
|
|
DECLARE
|
|
v_fifo_record RECORD;
|
|
v_total_cost DECIMAL(15,2) := 0;
|
|
v_avg_cost DECIMAL(12,2);
|
|
BEGIN
|
|
-- Verificar stock disponible
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM inventory_management.stock_levels
|
|
WHERE warehouse_id = p_warehouse_id
|
|
AND material_id = p_material_id
|
|
AND available_quantity >= p_quantity
|
|
) THEN
|
|
RAISE EXCEPTION 'Stock insuficiente disponible para material %', p_material_id;
|
|
END IF;
|
|
|
|
-- Consumir lotes PEPS
|
|
FOR v_fifo_record IN
|
|
SELECT *
|
|
FROM inventory_management.calculate_fifo_cost(p_warehouse_id, p_material_id, p_quantity)
|
|
LOOP
|
|
-- Actualizar lote
|
|
UPDATE inventory_management.inventory_lots
|
|
SET remaining_quantity = remaining_quantity - v_fifo_record.quantity_consumed
|
|
WHERE id = v_fifo_record.lot_id;
|
|
|
|
v_total_cost := v_total_cost + v_fifo_record.total_cost;
|
|
END LOOP;
|
|
|
|
-- Calcular costo promedio
|
|
v_avg_cost := v_total_cost / p_quantity;
|
|
|
|
-- Actualizar stock
|
|
UPDATE inventory_management.stock_levels
|
|
SET quantity = quantity - p_quantity,
|
|
last_exit_date = CURRENT_DATE,
|
|
last_movement_date = CURRENT_DATE,
|
|
updated_at = now()
|
|
WHERE warehouse_id = p_warehouse_id
|
|
AND material_id = p_material_id;
|
|
|
|
total_cost := v_total_cost;
|
|
average_cost := v_avg_cost;
|
|
|
|
RETURN NEXT;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
COMMENT ON FUNCTION inventory_management.update_stock_on_exit IS 'Procesa salida de inventario con valoracion PEPS';
|
|
```
|
|
|
|
### 6. Funcion: Generar codigo de requisicion
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- FUNCION: Generar codigo automatico de requisicion
|
|
-- ============================================================================
|
|
|
|
CREATE OR REPLACE FUNCTION purchasing_management.generate_requisition_code()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
v_year VARCHAR(4);
|
|
v_sequence INT;
|
|
v_code VARCHAR(30);
|
|
BEGIN
|
|
IF NEW.code IS NOT NULL AND NEW.code != '' THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
v_year := TO_CHAR(CURRENT_DATE, 'YYYY');
|
|
|
|
SELECT COALESCE(MAX(SUBSTRING(code FROM 10)::INT), 0) + 1
|
|
INTO v_sequence
|
|
FROM purchasing_management.requisitions
|
|
WHERE tenant_id = NEW.tenant_id
|
|
AND code LIKE 'REQ-' || v_year || '-%';
|
|
|
|
v_code := 'REQ-' || v_year || '-' || LPAD(v_sequence::TEXT, 5, '0');
|
|
|
|
NEW.code := v_code;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_requisitions_generate_code
|
|
BEFORE INSERT ON purchasing_management.requisitions
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION purchasing_management.generate_requisition_code();
|
|
```
|
|
|
|
### 7. Funcion: Generar codigos para otras entidades
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- FUNCIONES: Generar codigos automaticos
|
|
-- ============================================================================
|
|
|
|
-- RFQ
|
|
CREATE OR REPLACE FUNCTION purchasing_management.generate_rfq_code()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
v_year VARCHAR(4);
|
|
v_sequence INT;
|
|
BEGIN
|
|
IF NEW.code IS NOT NULL AND NEW.code != '' THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
v_year := TO_CHAR(CURRENT_DATE, 'YYYY');
|
|
|
|
SELECT COALESCE(MAX(SUBSTRING(code FROM 10)::INT), 0) + 1
|
|
INTO v_sequence
|
|
FROM purchasing_management.rfqs
|
|
WHERE tenant_id = NEW.tenant_id
|
|
AND code LIKE 'RFQ-' || v_year || '-%';
|
|
|
|
NEW.code := 'RFQ-' || v_year || '-' || LPAD(v_sequence::TEXT, 5, '0');
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_rfqs_generate_code
|
|
BEFORE INSERT ON purchasing_management.rfqs
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION purchasing_management.generate_rfq_code();
|
|
|
|
-- Quotation
|
|
CREATE OR REPLACE FUNCTION purchasing_management.generate_quotation_code()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
v_year VARCHAR(4);
|
|
v_sequence INT;
|
|
BEGIN
|
|
IF NEW.code IS NOT NULL AND NEW.code != '' THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
v_year := TO_CHAR(CURRENT_DATE, 'YYYY');
|
|
|
|
SELECT COALESCE(MAX(SUBSTRING(code FROM 11)::INT), 0) + 1
|
|
INTO v_sequence
|
|
FROM purchasing_management.quotations
|
|
WHERE tenant_id = NEW.tenant_id
|
|
AND code LIKE 'QUOT-' || v_year || '-%';
|
|
|
|
NEW.code := 'QUOT-' || v_year || '-' || LPAD(v_sequence::TEXT, 5, '0');
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_quotations_generate_code
|
|
BEFORE INSERT ON purchasing_management.quotations
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION purchasing_management.generate_quotation_code();
|
|
|
|
-- Purchase Order
|
|
CREATE OR REPLACE FUNCTION purchasing_management.generate_po_code()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
v_year VARCHAR(4);
|
|
v_sequence INT;
|
|
BEGIN
|
|
IF NEW.code IS NOT NULL AND NEW.code != '' THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
v_year := TO_CHAR(CURRENT_DATE, 'YYYY');
|
|
|
|
SELECT COALESCE(MAX(SUBSTRING(code FROM 9)::INT), 0) + 1
|
|
INTO v_sequence
|
|
FROM purchasing_management.purchase_orders
|
|
WHERE tenant_id = NEW.tenant_id
|
|
AND code LIKE 'OC-' || v_year || '-%';
|
|
|
|
NEW.code := 'OC-' || v_year || '-' || LPAD(v_sequence::TEXT, 5, '0');
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_purchase_orders_generate_code
|
|
BEFORE INSERT ON purchasing_management.purchase_orders
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION purchasing_management.generate_po_code();
|
|
|
|
-- PO Receipt
|
|
CREATE OR REPLACE FUNCTION purchasing_management.generate_po_receipt_code()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
v_year VARCHAR(4);
|
|
v_sequence INT;
|
|
BEGIN
|
|
IF NEW.code IS NOT NULL AND NEW.code != '' THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
v_year := TO_CHAR(CURRENT_DATE, 'YYYY');
|
|
|
|
SELECT COALESCE(MAX(SUBSTRING(code FROM 10)::INT), 0) + 1
|
|
INTO v_sequence
|
|
FROM purchasing_management.po_receipts
|
|
WHERE tenant_id = NEW.tenant_id
|
|
AND code LIKE 'REC-' || v_year || '-%';
|
|
|
|
NEW.code := 'REC-' || v_year || '-' || LPAD(v_sequence::TEXT, 5, '0');
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_po_receipts_generate_code
|
|
BEFORE INSERT ON purchasing_management.po_receipts
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION purchasing_management.generate_po_receipt_code();
|
|
|
|
-- Inventory Movement
|
|
CREATE OR REPLACE FUNCTION inventory_management.generate_movement_code()
|
|
RETURNS TRIGGER AS $$
|
|
DECLARE
|
|
v_year VARCHAR(4);
|
|
v_sequence INT;
|
|
v_prefix VARCHAR(5);
|
|
BEGIN
|
|
IF NEW.code IS NOT NULL AND NEW.code != '' THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
v_year := TO_CHAR(CURRENT_DATE, 'YYYY');
|
|
|
|
-- Prefijo segun tipo de movimiento
|
|
v_prefix := CASE NEW.type
|
|
WHEN 'entry' THEN 'ENT'
|
|
WHEN 'exit' THEN 'SAL'
|
|
WHEN 'transfer_out' THEN 'TSO'
|
|
WHEN 'transfer_in' THEN 'TSI'
|
|
WHEN 'adjustment' THEN 'AJU'
|
|
ELSE 'MOV'
|
|
END;
|
|
|
|
SELECT COALESCE(MAX(SUBSTRING(code FROM LENGTH(v_prefix) + 7)::INT), 0) + 1
|
|
INTO v_sequence
|
|
FROM inventory_management.inventory_movements
|
|
WHERE tenant_id = NEW.tenant_id
|
|
AND code LIKE v_prefix || '-' || v_year || '-%';
|
|
|
|
NEW.code := v_prefix || '-' || v_year || '-' || LPAD(v_sequence::TEXT, 5, '0');
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_inventory_movements_generate_code
|
|
BEFORE INSERT ON inventory_management.inventory_movements
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION inventory_management.generate_movement_code();
|
|
```
|
|
|
|
---
|
|
|
|
## Vistas Utiles
|
|
|
|
### 1. Vista: Stock consolidado por material
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- VISTA: Stock consolidado por material (todos los almacenes)
|
|
-- ============================================================================
|
|
|
|
CREATE OR REPLACE VIEW inventory_management.v_stock_consolidated AS
|
|
SELECT
|
|
tenant_id,
|
|
material_id,
|
|
SUM(quantity) AS total_quantity,
|
|
SUM(reserved_quantity) AS total_reserved,
|
|
SUM(available_quantity) AS total_available,
|
|
AVG(average_cost) AS weighted_average_cost,
|
|
SUM(total_value) AS total_value,
|
|
COUNT(DISTINCT warehouse_id) AS warehouses_count,
|
|
MAX(last_movement_date) AS last_movement_date
|
|
FROM inventory_management.stock_levels
|
|
WHERE quantity > 0
|
|
GROUP BY tenant_id, material_id;
|
|
|
|
COMMENT ON VIEW inventory_management.v_stock_consolidated IS 'Stock consolidado por material sumando todos los almacenes';
|
|
```
|
|
|
|
### 2. Vista: Ordenes de compra pendientes de recepcion
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- VISTA: Ordenes de compra pendientes de recepcion
|
|
-- ============================================================================
|
|
|
|
CREATE OR REPLACE VIEW purchasing_management.v_pending_receipts AS
|
|
SELECT
|
|
po.id,
|
|
po.code,
|
|
po.tenant_id,
|
|
po.supplier_id,
|
|
po.project_id,
|
|
po.order_date,
|
|
po.delivery_date,
|
|
po.status,
|
|
po.total,
|
|
CURRENT_DATE - po.delivery_date AS days_overdue,
|
|
CASE
|
|
WHEN CURRENT_DATE > po.delivery_date THEN true
|
|
ELSE false
|
|
END AS is_overdue
|
|
FROM purchasing_management.purchase_orders po
|
|
WHERE po.status IN ('sent', 'partially_received')
|
|
AND po.is_active = true
|
|
ORDER BY po.delivery_date ASC;
|
|
|
|
COMMENT ON VIEW purchasing_management.v_pending_receipts IS 'Ordenes de compra pendientes de recibir (total o parcialmente)';
|
|
```
|
|
|
|
---
|
|
|
|
## Scripts de Inicializacion
|
|
|
|
### Schema creation
|
|
|
|
```sql
|
|
-- ============================================================================
|
|
-- CREAR SCHEMAS
|
|
-- ============================================================================
|
|
|
|
CREATE SCHEMA IF NOT EXISTS purchasing_management;
|
|
CREATE SCHEMA IF NOT EXISTS inventory_management;
|
|
|
|
COMMENT ON SCHEMA purchasing_management IS 'Gestion de compras: requisiciones, cotizaciones, ordenes';
|
|
COMMENT ON SCHEMA inventory_management IS 'Gestion de inventarios: almacenes, movimientos, stock';
|
|
```
|
|
|
|
---
|
|
|
|
## Notas de Implementacion
|
|
|
|
### Orden de Creacion
|
|
|
|
1. Crear schemas
|
|
2. Crear ENUMs
|
|
3. Crear tablas en orden:
|
|
- purchasing_management.requisitions
|
|
- purchasing_management.rfqs
|
|
- purchasing_management.quotations
|
|
- purchasing_management.purchase_orders
|
|
- purchasing_management.po_receipts
|
|
- inventory_management.warehouses
|
|
- inventory_management.warehouse_locations
|
|
- inventory_management.inventory_movements
|
|
- inventory_management.stock_levels
|
|
- inventory_management.inventory_lots
|
|
4. Crear indices
|
|
5. Habilitar RLS y crear policies
|
|
6. Crear funciones
|
|
7. Crear triggers
|
|
8. Crear vistas
|
|
|
|
### Dependencias Externas
|
|
|
|
- `core_tenants.tenants` - Tabla de tenants
|
|
- `core_users.users` - Tabla de usuarios
|
|
- `core_shared.set_updated_at()` - Funcion compartida para updated_at
|
|
- Schema `projects` - Para relacion con proyectos
|
|
- Schema `core_catalogs` - Para materiales y proveedores
|
|
|
|
### Consideraciones de Rendimiento
|
|
|
|
- Indices GIN en columnas JSONB para busquedas eficientes
|
|
- Indices parciales en columnas de estado para queries frecuentes
|
|
- Columnas generadas (STORED) para calculos frecuentes
|
|
- Particionamiento futuro en `inventory_movements` por fecha si crece mucho
|
|
|
|
### Migraciones
|
|
|
|
- Crear migracion TypeORM siguiendo nomenclatura: `{timestamp}-create-purchasing-inventory-schemas.ts`
|
|
- Separar en multiples archivos si es muy grande
|
|
- Incluir rollback (down) para todas las operaciones
|
|
|
|
---
|
|
|
|
**Estado:** En Diseno
|
|
**Proximos Pasos:** Revision y aprobacion para implementacion
|
|
**Fecha Creacion:** 2025-12-06
|