IF OBJECT_ID('dbo.vw_fco_info_pensionados') IS NOT NULL
    DROP VIEW dbo.vw_fco_info_pensionados
GO
CREATE VIEW dbo.vw_fco_info_pensionados
AS
SELECT 
    p.compania, p.trabajador, 
    tipo_pension = isnull(CASE WHEN IsNumeric(Left(dato_06, 2)) = 1 THEN Convert(tinyint, Left(dato_06, 2)) ELSE 1 END, 1),
    compartida = isnull(CASE WHEN UPPER(LEFT(dato_04, 1)) = 'S' THEN 'S' ELSE 'N' END,'N'),
    caus_ape1 = isnull(dato_07, ''),
    caus_ape2 = isnull(dato_08, ''),
    caus_nom1 = isnull(dato_09, ''),
    caus_nom2 = isnull(dato_10, ''),
    caus_tdoc = isnull(LEFT(dato_03, 2), ''),
    caus_id = isnull(LEFT(RTRIM(FLOOR(dato_11)), 16), 0),
    tipo_pensionado = isnull(CASE WHEN IsNumeric(Left(dato_05, 2)) = 1 THEN Convert(tinyint, Left(dato_05, 2)) ELSE 1 END,1),
    residente_en_exterior = isnull(CASE WHEN UPPER(LEFT(dato_02, 1)) = 'S' THEN 'S' ELSE 'N' END,'N'),
    situa_en_exterior = isnull(CASE WHEN UPPER(LEFT(dato_01, 1)) = 'S' THEN 'S' ELSE 'N' END,'N')
FROM vw_fco_pensionados p 
LEFT JOIN inf_soc_trabajador i ON i.trabajador = p.trabajador AND i.indice_inf_soc = 'PENSIONADO' AND i.secuencia = 1
GO
GRANT SELECT ON dbo.vw_fco_info_pensionados TO PUBLIC
GO

