USE [Sis2000]
GO

-- Eliminar vista si existe
IF OBJECT_ID('vw_inconsistencias_canales', 'V') IS NOT NULL
    DROP VIEW vw_inconsistencias_canales;
GO

-- Crear vista para verificar inconsistencias de canales
CREATE VIEW vw_inconsistencias_canales AS
SELECT 
    'API_SIN_CLIENTE' as tipo_inconsistencia,
    ma.ccanalalt,
    m.xcanalalt,
    ma.cci_rif,
    ma.ctipocanal,
    ma.iestado as api_estado,
    NULL as xcliente,
    NULL as itipopers,
    'Canal tiene API pero no existe en maclient' as descripcion,
    (SELECT COUNT(*) FROM tatarlot L WHERE L.ccanalalt = ma.ccanalalt) as lotes_afectados
FROM maclient_api ma
JOIN macanalalt m ON m.ccanalalt = ma.ccanalalt 
LEFT JOIN maclient mc ON mc.cci_rif = ma.cci_rif
WHERE mc.cci_rif IS NULL

UNION ALL

SELECT 
    'SIN_CANAL' as tipo_inconsistencia,
    L.ccanalalt,
    NULL as xcanalalt,
    NULL as cci_rif,
    NULL as ctipocanal,
    NULL as api_estado,
    NULL as xcliente,
    NULL as itipopers,
    'Lote sin canal asignado' as descripcion,
    COUNT(*) as lotes_afectados
FROM tatarlot L
WHERE L.ccanalalt IS NULL OR L.ccanalalt = ''
GROUP BY L.ccanalalt;
GO

-- Query para ver los resultados
SELECT * FROM vw_inconsistencias_canales 
ORDER BY tipo_inconsistencia, lotes_afectados DESC;
GO
