SET QUOTED_IDENTIFIER OFF
IF OBJECT_ID('dbo.sp_fco_crea_iss_mm') IS NOT NULL
    DROP PROCEDURE dbo.sp_fco_crea_iss_mm
GO
CREATE PROCEDURE dbo.sp_fco_crea_iss_mm
(
   @pvc_compania	varchar(4),
   @pvc_anio	varchar(4),
   @pvc_mes	varchar(2),
   @pvc_cod_suc	varchar(10)
)

/**********************************************************************/
/*** Modulo:      Modulo Fiscal Colombiano Version FCO - ADAM V.3   ***/
/*** Descripcion: Crea archivo plano para el ISS Formato SIGA       ***/
/*** Autor:       Juan Carlos Leguizamon                            ***/
/*** Fecha MF:    Junio 22 de 2001                                  ***/
/*** Fecha FCO:   Abril 01 de 2006                                  ***/
/**********************************************************************/
AS

/* VARIABLES ESTRUCTURA MEDIO MAGNETICO */
   DECLARE 	@CI01_06 char(58),
		@CI07_18 char(148),
		@CI19_30 char(113),
		@CI31	 char(5),
		@CI32	 char(5),
		@REN_31  char(116),
		@REN_32  char(116),
		@REN_33  char(116),
		@REN_34  char(116),
		@REN_35  char(116),
		@REN_36  char(116),
		@REN_37  char(116),
		@REN_38  char(116),
		@REN_39  char(116),
		@R31C4   numeric(10,0),
		@R31C8   numeric(10,0),
		@R31C9   numeric(10,0),
		@R31C13  numeric(10,0),
		@R35C4   numeric(10,0),
		@R35C8   numeric(10,0),
		@R35C9   numeric(10,0),
		@R31C10  numeric(10,0),
		@R31C11  numeric(10,0),
		@R31C12  numeric(10,0),
		@R35C13  numeric(10,0),
		@R36C4   numeric(10,0),
		@R36C8   numeric(10,0),
		@R36C9   numeric(10,0),
		@R36C13  numeric(10,0),
		@R37C4   numeric(10,0),
		@R37C8   numeric(10,0),
		@R37C9   numeric(10,0),
		@R37C13  numeric(10,0),
		@R38C4   numeric(10,0),
		@R38C8   numeric(10,0),
		@R38C9   numeric(10,0),
		@R38C13  numeric(10,0),
		@R39C4   numeric(10,0),
		@R39C8   numeric(10,0),
		@R39C9   numeric(10,0),
		@R39C13  numeric(10,0)

/* VARIABLES AUXILIARES */
   /* Para armar sentencias dinamicamente */
   DECLARE	@sql1 	varchar(3000),
		@sql2 	varchar(3000),
		@sql3 	varchar(3000)
   /* Para controlar puntos y rayas en el registro_fiscal */
   DECLARE 	@nit	char(9)
   /* Para hallar el periodo cotizado a salud (ste) */
   DECLARE	@per_ste char(6),
 		@pvc_mes_ste numeric(2,0)
   /* Para hallar la forma de presentación */
   DECLARE	@forma_pres char(1)
   /* Para recuperar datos del ISS */
   DECLARE	@codigo_ISS char(3)
   /* Para manejar la sucursal */
   DECLARE 	@w_cod_SUC  char(10),
		@w_control_SUC varchar(10)
   /* Tabla donde estan los datos */
   DECLARE 	@tabla  varchar(30)

   /* Variables para descontar incapacidades */
   DECLARE      @nro_aut_eg  char(10),
                @vlr_aut_eg  numeric(10,0),
                @nro_aut_lm  char(10),
                @vlr_aut_lm  numeric(10,0),
                @nro_meses   char(10),
                @vlr_mor_pe  numeric(10,0),
                @vlr_mor_fs  numeric(10,0),
                @vlr_mor_sa  numeric(10,0),
                @nro_rad_saf char(10),
                @vlr_saf_pe  numeric(10,0),
                @vlr_saf_fs  numeric(10,0),
                @vlr_saf_sa  numeric(10,0)

BEGIN

    /*** Establecer el Nombre de la Tabla ***/
    SET @tabla = 'FCO_ISS_' + RTrim(@pvc_compania) + '_' + RTrim(@pvc_anio) + '_' + RTrim(@pvc_mes)
    
   SET @codigo_ISS = 'ISS'
   DELETE fco_iss_mm

   /***********************************************************************/
   /*****          CONSTRUCCION DEL REGISTRO TIPO I                   *****/
   /***********************************************************************/


   SET @CI01_06 = '00000104INSTITUTO DE SEGUROS SOCIALES           8600138161'

   SELECT @nit=registro_fiscal
   FROM vw_fco_companias
   WHERE compania=@pvc_compania

   IF charIndex('.', @nit)>0
   BEGIN
      SELECT @nit=SubString(registro_fiscal,1,3) + SubString(registro_fiscal,5,3) + SubString(registro_fiscal,9,3)
      FROM vw_fco_companias WHERE compania=@pvc_compania 
   END
   ELSE
   BEGIN
      SELECT @nit=SubString(registro_fiscal,1,9)
      FROM vw_fco_companias WHERE compania=@pvc_compania 
   END

   /* Control del codigo de la sucursal */
   SET @w_control_SUC = IsNull(@pvc_cod_suc,'0')
   IF LEN(@w_control_SUC)=0
	SET @w_control_SUC='0'
   IF @w_control_SUC = '0'
   BEGIN
       SELECT @CI07_18 = 
	LTrim(RTrim(SubString(C.nombre_cia,1,40))) + Space(40-Len(LTrim(RTrim(SubString(C.nombre_cia,1,40))))) + 
	'N' + 
   	Replicate('0',9-Len(LTrim(RTrim(@nit)))) + LTrim(RTrim(@nit)) +
   	SubString(C.registro_fiscal, Len(RTrim(C.registro_fiscal)),1) +
	SubString(C.clase_aportante,1,1) + Space(1-Len(SubString(C.clase_aportante,1,1))) + 
	LTrim(RTrim(SubString(C.domicilio,1,40))) + Space(40-Len(LTrim(RTrim(SubString(C.domicilio,1,40))))) + 
	LTrim(RTrim(SubString(M.nombre,1,15))) + Space(15-Len(LTrim(RTrim(SubString(M.nombre,1,15))))) + 
	Replicate('0',4-Len(LTrim(RTrim(SubString(LTrim(M.codigo),1,4))))) + LTrim(RTrim(SubString(LTrim(M.codigo),1,4))) +
	LTrim(RTrim(SubString(D.nombre,1,15))) + Space(15-Len(LTrim(RTrim(SubString(D.nombre,1,15))))) + 
	Replicate('0',2-Len(LTrim(RTrim(SubString(LTrim(D.codigo),1,2))))) + LTrim(RTrim(SubString(LTrim(D.codigo),1,2))) +
	LTrim(RTrim(SubString(C.telefono,1,10))) + Space(10-Len(LTrim(RTrim(SubString(C.telefono,1,10))))) + 
	LTrim(RTrim(SubString(C.fax,1,10))) + Space(10-Len(LTrim(RTrim(SubString(C.fax,1,10)))))
       FROM	
	vw_fco_companias C,
	fco_CIUDADES M,
	fco_DEPARTAMENTOS D
       WHERE 
	M.codigo   = C.cod_ciudad And
	D.codigo   = C.cod_dpto And
	M.cod_dpto = D.codigo And
	C.compania = @pvc_compania 
   END
   ELSE
   BEGIN
       SELECT @CI07_18 = 
	LTrim(RTrim(SubString(C.nombre_cia,1,40))) + Space(40-Len(LTrim(RTrim(SubString(C.nombre_cia,1,40))))) + 
	'N' + 
   	Replicate('0',9-Len(LTrim(RTrim(@nit)))) + LTrim(RTrim(@nit)) +
   	SubString(C.registro_fiscal, Len(RTrim(C.registro_fiscal)),1) +
	SubString(C.clase_aportante,1,1) + Space(1-Len(SubString(C.clase_aportante,1,1))) + 
	LTrim(RTrim(SubString(RSP.direccion,1,40))) + Space(40-Len(LTrim(RTrim(SubString(RSP.direccion,1,40))))) + 
	LTrim(RTrim(SubString(M.nombre,1,15))) + Space(15-Len(LTrim(RTrim(SubString(M.nombre,1,15))))) + 
	Replicate('0',4-Len(LTrim(RTrim(SubString(LTrim(M.codigo),1,4))))) + LTrim(RTrim(SubString(LTrim(M.codigo),1,4))) +
	LTrim(RTrim(SubString(D.nombre,1,15))) + Space(15-Len(LTrim(RTrim(SubString(D.nombre,1,15))))) + 
	Replicate('0',2-Len(LTrim(RTrim(SubString(LTrim(D.codigo),1,2))))) + LTrim(RTrim(SubString(LTrim(D.codigo),1,2))) +
	LTrim(RTrim(SubString(RSP.telefono,1,10))) + Space(10-Len(LTrim(RTrim(SubString(RSP.telefono,1,10))))) + 
	LTrim(RTrim(SubString(RSP.fax,1,10))) + Space(10-Len(LTrim(RTrim(SubString(RSP.fax,1,10)))))
       FROM	
	vw_fco_companias C,
        fco_sucursales RSP, 
	fco_CIUDADES M,
	fco_DEPARTAMENTOS D
       WHERE 
         RSP.compania = C.compania And
	 M.codigo   = RSP.cod_ciudad And
	 D.codigo   = RSP.cod_dpto   And
	 M.cod_dpto = D.codigo 	   And
	 C.compania = @pvc_compania  And
         RSP.sucursal = @pvc_cod_suc
   END
   /* Calculo del periodo cotizado a Salud */
   SET @pvc_mes_ste = Convert(numeric, @pvc_mes) + 1
   SET @per_ste = @pvc_anio + Replicate('0',2-Len(Rtrim(LTrim(@pvc_mes_ste)))) + Rtrim(LTrim(@pvc_mes_ste))
   IF @pvc_mes_ste = 13 
	SET @per_ste = RTrim(LTrim(Convert(numeric,@pvc_anio) + 1)) + '01'

   /* Forma de Presentación de la Autoliquidación de Aportes */
   IF @w_control_SUC = '0'
   BEGIN
	SELECT @forma_pres = IsNull(forma_presentacion,'U')
	FROM   vw_fco_companias
	WHERE  compania = @pvc_compania

        SET @w_cod_SUC = ' '
   END
   ELSE
   BEGIN
	SET @forma_pres='S';
	SET @w_cod_SUC = @w_control_SUC
   END

    /* Numero de trabajadores */
   SET @sql1=
   " SELECT Total = Count(COD_TRAB)" +
   " INTO ##tmp_trabajadores" +
   " FROM " + @tabla +
   " WHERE COD_TRAB IN" +
   " (SELECT trabajador FROM trabajadores_grales" + 
   " WHERE compania = '" + @pvc_compania + "' AND sit_trabajador = 1" +
   Case @w_control_SUC WHEN '0' THEN "" ELSE " AND COD_SUC='" + @w_control_SUC + "'" END + ")"

   EXECUTE (@sql1)

   SELECT @CI19_30 = LTrim(RTrim(@pvc_anio)) + Replicate('0',2-Len(LTrim(RTrim(@pvc_mes)))) + LTrim(RTrim(@pvc_mes)) +
		@per_ste + Space(40) + Space(5) + 
		' ' + Space(6) + Space(8) +
		Space(10) + Space(15) + @forma_pres + @w_cod_SUC +
		Replicate('0',5-Len(LTrim(RTrim(Convert(char, Total))))) + LTrim(RTrim(Convert(char, Total)))
   FROM ##tmp_trabajadores
   DROP TABLE ##tmp_trabajadores

   /* Numero de Afiliados al ISS en Pensiones */
   SET @sql1=
   " SELECT Total = Count(*)" +
   " INTO ##tmp_Afiliados" +
   " FROM " +
   " (SELECT Distinct COD_TRAB" +
   " FROM " + @tabla +
   " WHERE COD_FPO='" + @codigo_ISS + "'" +
   Case @w_control_SUC WHEN '0' THEN "" ELSE " AND COD_SUC='" + @w_control_SUC + "'" END +
   " GROUP BY COD_TRAB" +
   " HAVING SUM(C32)>0) Afiliados"

   EXECUTE (@sql1)

   SELECT @CI31 = Replicate('0',5-Len(RTrim(LTrim(Convert(char,Total))))) + RTrim(LTrim(Convert(char,Total))) From ##tmp_Afiliados
   DROP TABLE ##tmp_Afiliados

   /* Numero de Afiliados al ISS en Salud */
   SET @sql1=
   " SELECT Total = Count(*)" +
   " INTO ##tmp_Afiliados" +
   " FROM " +
   " (SELECT Distinct COD_TRAB" +
   " FROM " + @tabla +
   " WHERE COD_EPS='" + @codigo_ISS + "'" +
   Case @w_control_SUC WHEN '0' THEN "" ELSE " AND COD_SUC='" + @w_control_SUC + "'" END +
   " GROUP BY COD_TRAB" +
   " HAVING SUM(C38)>0) Afiliados"

   EXECUTE (@sql1)

   SELECT @CI32 = Replicate('0',5-Len(RTrim(LTrim(Convert(char,Total))))) + RTrim(LTrim(Convert(char,Total))) From ##tmp_Afiliados
   DROP TABLE ##tmp_Afiliados

   INSERT INTO fco_iss_mm VALUES (0, @CI01_06 + @CI07_18 + @CI19_30 + @CI31 + @CI32)

   /***********************************************************************/
   /*****          CONSTRUCCION DEL REGISTRO TIPO II                  *****/
   /***********************************************************************/

   SET @sql1 =
        " UPDATE " + @tabla +
        " SET C09 = IsNull(C09, ''), C10 = IsNull(C10, ''), C11 = IsNull(C11, '')," +
            " C12 = IsNull(C12, ''), C13 = IsNull(C13, ''), C14 = IsNull(C14, '')," +
            " C15 = IsNull(C15, ''), C16 = IsNull(C16, ''), C17 = IsNull(C17, '')," +
            " C18 = IsNull(C18, ''), C19 = IsNull(C19, ''), C20 = IsNull(C20, '')," +
            " C21 = IsNull(C21, '')"
   EXECUTE (@sql1)

   SET @sql1 =
        "SELECT Identity(Int,1,1) AS Sec, " +
	"'2' + LTrim(RTrim(SubString(C03,1,1))) + Space(1-Len(LTrim(RTrim(SubString(C03,1,1))))) + " +
	"Replicate('0',12 - Len(LTrim(RTrim(Convert(char(12),C04))))) + LTrim(RTrim(Convert(char(12),C04))) +" +
	"RTrim(LTrim(SubString(C05, 1, charIndex('/', C05)-1))) + Space(15-Len(RTrim(LTrim(SubString(C05, 1, charIndex('/', C05)-1))))) +" +
	"RTrim(LTrim(SubString(C05, charIndex('/', C05) + 1, charIndex('/', C05, charIndex('/', C05)+1) - charIndex('/', C05)-1))) + Space(15-Len(RTrim(LTrim(SubString(C05, charIndex('/', C05) + 1, charIndex('/', C05, charIndex('/', C05)+1) - charIndex('/', C05)-1))))) +" +
	"RTrim(LTrim(SubString(Reverse(SubString(Reverse(C05),1,charIndex('/', Reverse(C05))-1)), 1, Case When charIndex(' ', Reverse(SubString(Reverse(C05),1,charIndex('/', Reverse(C05))-1)))>0 Then charIndex(' ', Reverse(SubString(Reverse(C05),1,charIndex('/', Reverse(C05))-1))) Else Len(Reverse(SubString(Reverse(C05),1,charIndex('/', Reverse(C05))-1))) End))) + Space(15-Len(RTrim(LTrim(SubString(Reverse(SubString(Reverse(C05),1,charIndex('/', Reverse(C05))-1)), 1, Case When charIndex(' ', Reverse(SubString(Reverse(C05),1,charIndex('/', Reverse(C05))-1)))>0 Then charIndex(' ', Reverse(SubString(Reverse(C05),1,charIndex('/', Reverse(C05))-1))) Else Len(Reverse(SubString(Reverse(C05),1,charIndex('/', Reverse(C05))-1))) End))))) +" +
	"Case When charIndex(' ', Reverse(SubString(Reverse(C05),1,charIndex('/', Reverse(C05))-1))) = 0 Then space(15-Len('')) Else RTrim(LTrim(SubString(Reverse(SubString(Reverse(C05),1,charIndex('/', Reverse(C05))-1)), charIndex(' ', Reverse(SubString(Reverse(C05),1,charIndex('/', Reverse(C05))-1))) + 1, Len(Reverse(SubString(Reverse(C05),1,charIndex('/', Reverse(C05))-1))) - charIndex(' ', Reverse(LTrim(RTrim(SubString(Reverse(C05),1,charIndex('/', Reverse(C05))-1)))))))) + Space(15-Len(RTrim(LTrim(SubString(Reverse(SubString(Reverse(C05),1,charIndex('/', Reverse(C05))-1)), charIndex(' ', Reverse(SubString(Reverse(C05),1,charIndex('/', Reverse(C05))-1))) + 1, Len(Reverse(SubString(Reverse(C05),1,charIndex('/', Reverse(C05))-1))) - charIndex(' ', Reverse(LTrim(RTrim(SubString(Reverse(C05),1,charIndex('/', Reverse(C05))-1)))))))))) End +" +
	"Case When RTrim(LTrim(C09)) = '' Then ' ' Else C09 End +" +
	"Case When RTrim(LTrim(C10)) = '' Then ' ' Else C10 End +" +
	"Case When RTrim(LTrim(C11)) = '' Then ' ' Else C11 End +" +
	"Case When RTrim(LTrim(C12)) = '' Then ' ' Else C12 End +" +
	"Case When RTrim(LTrim(C13)) = '' Then ' ' Else C13 End +" +
	"Case When RTrim(LTrim(C14)) = '' Then ' ' Else C14 End +"
   SET @sql2 =
	"Case When RTrim(LTrim(C15)) = '' Then ' ' Else C15 End +" +
	"Case When RTrim(LTrim(C16)) = '' Then ' ' Else C16 End +" +
	"Case When RTrim(LTrim(C17)) = '' Then ' ' Else C17 End +" +
	"Case When RTrim(LTrim(C18)) = '' Then ' ' Else C18 End +" +
	"Case When RTrim(LTrim(C19)) = '' Then ' ' Else C19 End +" +
	"Case When RTrim(LTrim(C20)) = '' Then ' ' Else C20 End +" +
	"Case When RTrim(LTrim(C21)) = '' Then ' ' Else C21 End +" +
	"Replicate('0',2 - Len(LTrim(RTrim(Convert(char(2),Floor(C22)))))) + LTrim(RTrim(Convert(char(2),Floor(C22)))) +" +
	"Replicate('0',2 - Len(LTrim(RTrim(Convert(char(2),Floor(C23)))))) + LTrim(RTrim(Convert(char(2),Floor(C23)))) +" +	
	"Replicate('0',2 - Len(LTrim(RTrim(Convert(char(2),Floor(C24)))))) + LTrim(RTrim(Convert(char(2),Floor(C24)))) +" +
	"Replicate('0',2 - Len(LTrim(RTrim(Convert(char(2),Floor(C25)))))) + LTrim(RTrim(Convert(char(2),Floor(C25)))) +" +	
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C26)))))) + LTrim(RTrim(Convert(char(9),Floor(C26)))) +" +
	"Replicate('0',9) +" +
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C28)))))) + LTrim(RTrim(Convert(char(9),Floor(C28)))) +" +
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C29)))))) + LTrim(RTrim(Convert(char(9),Floor(C29)))) +" +
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C30)))))) + LTrim(RTrim(Convert(char(9),Floor(C30)))) +"
   SET @sql3=	
        "Replicate('0',6 - Len(LTrim(RTrim(Convert(char(6),C31))))) + LTrim(RTrim(Convert(char(6),C31))) +" +	
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C32)))))) + LTrim(RTrim(Convert(char(9),Floor(C32)))) +" +
	"Replicate('0',9) +" +
	"Replicate('0',9) +" +
	"Replicate('0',9) +" +
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C36)))))) + LTrim(RTrim(Convert(char(9),Floor(C36)))) +" +
	"Replicate('0',9) +" +
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C38)))))) + LTrim(RTrim(Convert(char(9),Floor(C38)))) +" +
	"RTrim(LTrim(SubString(C39,1,10))) + Space(10-Len(LTrim(RTrim(SubString(C39,1,10))))) + " +
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C40)))))) + LTrim(RTrim(Convert(char(9),Floor(C40)))) +" +
	"RTrim(LTrim(SubString(C41,1,10))) + Space(10-Len(LTrim(RTrim(SubString(C41,1,10))))) + " +
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C42)))))) + LTrim(RTrim(Convert(char(9),Floor(C42)))) +" +
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C43)))))) + LTrim(RTrim(Convert(char(9),Floor(C43)))) +" +
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C44)))))) + LTrim(RTrim(Convert(char(9),Floor(C44)))) +" +
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C45)))))) + LTrim(RTrim(Convert(char(9),Floor(C45)))) +" +
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C46)))))) + LTrim(RTrim(Convert(char(9),Floor(C46)))) +" +
	"RTrim(LTrim(SubString(C47,1,5))) + Space(5-Len(LTrim(RTrim(SubString(C47,1,5))))) + " +
	"RTrim(LTrim(SubString(C48,1,5))) + Space(5-Len(LTrim(RTrim(SubString(C48,1,5))))) Registro2" +
	" INTO ##TMP_DETALLE_ISS" +
	" FROM " + @tabla +
	" WHERE CIA='" + @pvc_compania + "'" +
   	Case @w_control_SUC WHEN '0' THEN "" ELSE " AND COD_SUC='" + @w_control_SUC + "'" END +
	" AND (C32+C38)>0"

   EXECUTE (@sql1 + @sql2 + @sql3)

   INSERT INTO fco_iss_mm
   SELECT Sec, Replicate('0', 5- Len(RTrim(LTrim(Convert(char(5),Sec))))) + RTrim(LTrim(Convert(char(5),Sec))) + Registro2
   FROM ##TMP_DETALLE_ISS

   DROP TABLE ##TMP_DETALLE_ISS

   /***********************************************************************/
   /*****          CONSTRUCCION DEL REGISTRO TIPO III                 *****/
   /***********************************************************************/
   CREATE TABLE #TMP_TOTALES_INTEG
   (C4 numeric(10,0), C8 numeric(10,0), C9 numeric(10,0), C10 numeric(10,0),
    C11 numeric(10,0), C12 numeric(10,0), C13 numeric(10,0))
   SELECT @sql1 = " INSERT INTO #TMP_TOTALES_INTEG" +
 		 " Select IsNull(Floor(SUM(C32)),0), IsNull(Floor(SUM(C36)),0), " +
		 " IsNull(Floor(SUM(C38)),0), IsNull(Floor(SUM(C40)),0), " +
		 " IsNull(Floor(SUM(C42)),0), IsNull(Floor(SUM(C43)),0), " +
		 " IsNull(Floor(SUM(C46)),0) " +
		 " From " + @tabla +
		 " Where CIA='" + @pvc_compania + "'" +
	   	 Case @w_control_SUC WHEN '0' THEN "" ELSE " AND COD_SUC='" + @w_control_SUC + "'" END
   EXECUTE (@sql1)

   SELECT @R31C4 = C4, @R31C8 = C8, @R31C9 = C9, @R31C10 = C10, @R31C11 = C11, @R31C12 = C12, @R31C13 = C13
   FROM #TMP_TOTALES_INTEG

   DROP TABLE #TMP_TOTALES_INTEG

   /*******	RENGLON 31	******/
   SET @REN_31 = '000313' + Replicate('0', 10) + 
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R31C4))))) + LTrim(RTrim(Convert(char(10), @R31C4))) +
	Replicate('0', 10) + Replicate('0', 10) + Replicate('0', 10) + 
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R31C8))))) + LTrim(RTrim(Convert(char(10), @R31C8))) +
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R31C9))))) + LTrim(RTrim(Convert(char(10), @R31C9))) +
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R31C10))))) + LTrim(RTrim(Convert(char(10), @R31C10))) +
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R31C11))))) + LTrim(RTrim(Convert(char(10), @R31C11))) +
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R31C12))))) + LTrim(RTrim(Convert(char(10), @R31C12))) +
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R31C13))))) + LTrim(RTrim(Convert(char(10), @R31C13)))

   INSERT INTO fco_iss_mm VALUES (999931, @REN_31)

   /*******	RENGLON 32	******/
   SET @nro_aut_eg = Space(10)
   SET @vlr_aut_eg = 0

   IF @w_control_SUC = '0' 
       SELECT @nro_aut_eg = IsNull(A.referencia, ' '), @vlr_aut_eg = IsNull(A.valor_2, 0)
       FROM fco_ala_datos_adi A, fco_rel_sgss_da B
       WHERE A.sgss = B.sgss 
         AND A.item = B.ala_datos_adi
         AND A.tabla = @tabla
         AND A.cod_adm = 'ISS'
         AND b.secuencia = 1
    ELSE
       SELECT @nro_aut_eg = IsNull(A.referencia, ' '), @vlr_aut_eg = IsNull(A.valor_2, 0)
       FROM fco_ala_datos_adi A, fco_rel_sgss_da B
       WHERE A.sgss = B.sgss 
         AND A.item = B.ala_datos_adi
         AND A.tabla = @tabla
         AND A.cod_adm = 'ISS'
         AND b.secuencia = 1
         AND cod_suc = @w_control_SUC

   SET @REN_32 = '000323' + @nro_aut_eg + Replicate('0',10) + Replicate('0',10) + 
	Replicate('0',10) + Replicate('0',10) + Replicate('0',10) + 
        Replicate('0',10 - Len(Convert(varchar, Floor(@vlr_aut_eg)))) + Convert(varchar,Floor(@vlr_aut_eg)) +
	Replicate('0',10) + Replicate('0',10) + Replicate('0',10) + Replicate('0',10)

   INSERT INTO fco_iss_mm VALUES (999932, @REN_32)

   /*******	RENGLON 33	******/
   SET @nro_aut_lm = Space(10)
   SET @vlr_aut_lm = 0

   IF @w_control_SUC = '0' 
       SELECT @nro_aut_lm = IsNull(A.referencia, ' '), @vlr_aut_lm = IsNull(A.valor_2, 0)
       FROM fco_ala_datos_adi A, fco_rel_sgss_da B
       WHERE A.sgss = B.sgss 
         AND A.item = B.ala_datos_adi
         AND A.tabla = @tabla
         AND A.cod_adm = 'ISS'
         AND b.secuencia = 2
    ELSE
       SELECT @nro_aut_lm = IsNull(A.referencia, ' '), @vlr_aut_lm = IsNull(A.valor_2, 0)
       FROM fco_ala_datos_adi A, fco_rel_sgss_da B
       WHERE A.sgss = B.sgss 
         AND A.item = B.ala_datos_adi
         AND A.tabla = @tabla
         AND A.cod_adm = 'ISS'
         AND b.secuencia = 2
         AND cod_suc = @w_control_SUC

   SET @REN_33 = '000333' + @nro_aut_lm + Replicate('0',10) + Replicate('0',10) + 
	Replicate('0',10) + Replicate('0',10) + Replicate('0',10) + 
        Replicate('0',10 - Len(Convert(varchar, Floor(@vlr_aut_lm)))) + Convert(varchar,Floor(@vlr_aut_lm)) +
	Replicate('0',10) + Replicate('0',10) + Replicate('0',10) + Replicate('0',10)

   INSERT INTO fco_iss_mm VALUES (999933, @REN_33)

   /*******	RENGLON 34	******/
   SET @REN_34 = '000343' + Replicate('0',10) + Replicate('0',10) + Replicate('0',10) + 
	Replicate('0',10) + Replicate('0',10) + Replicate('0',10) + Replicate('0',10) +
	Replicate('0',10) + Replicate('0',10) + Replicate('0',10) + Replicate('0',10)
   INSERT INTO fco_iss_mm VALUES (999934, @REN_34)

   /*******	RENGLON 35	******/
   SET @R35C4 = @R31C4
   SET @R35C8 = @R31C8
   SET @R35C9 = @R31C9 - @vlr_aut_eg - @vlr_aut_lm
   SET @R35C13 = @R31C13

   SET @REN_35 = '000353' + Replicate('0',10) + 
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R35C4))))) + LTrim(RTrim(Convert(char(10), @R35C4))) +
	Replicate('0',10) + Replicate('0',10) + Replicate('0',10) +
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R35C8))))) + LTrim(RTrim(Convert(char(10), @R35C8))) +
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R35C9))))) + LTrim(RTrim(Convert(char(10), @R35C9))) +
	Replicate('0',10) + Replicate('0',10) + Replicate('0',10) +
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R35C13))))) + LTrim(RTrim(Convert(char(10), @R35C13)))
	
   INSERT INTO fco_iss_mm VALUES (999935, @REN_35)

   /*******	RENGLON 36	******/
   SET @nro_meses = Space(10)
   SET @vlr_mor_pe = 0
   SET @vlr_mor_fs = 0
   SET @vlr_mor_sa = 0

   IF @w_control_SUC = '0' 
       SELECT @nro_meses = IsNull(A.referencia, ' '), @vlr_mor_pe = IsNull(A.valor_1, 0), @vlr_mor_sa = IsNull(A.valor_2,0)
       FROM fco_ala_datos_adi A, fco_rel_sgss_da B
       WHERE A.sgss = B.sgss 
         AND A.item = B.ala_datos_adi
         AND A.tabla = @tabla
         AND A.cod_adm = 'ISS'
         AND b.secuencia = 3
    ELSE
       SELECT @nro_meses = IsNull(A.referencia, ' '), @vlr_mor_pe = IsNull(A.valor_1,0), @vlr_mor_sa = IsNull(A.valor_2,0)
       FROM fco_ala_datos_adi A, fco_rel_sgss_da B
       WHERE A.sgss = B.sgss 
         AND A.item = B.ala_datos_adi
         AND A.tabla = @tabla
         AND A.cod_adm = 'ISS'
         AND b.secuencia = 3
         AND cod_suc = @w_control_SUC

   SET @R36C4 = @vlr_mor_pe
   SET @R36C8 = @vlr_mor_fs
   SET @R36C9 = @vlr_mor_sa
   SET @R36C13 = 0
   SET @REN_36 = '000363' + Replicate('0',10) + 
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R36C4))))) + LTrim(RTrim(Convert(char(10), @R36C4))) +
	Replicate('0',10) + Replicate('0',10) + Replicate('0',10) +
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R36C8))))) + LTrim(RTrim(Convert(char(10), @R36C8))) +
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R36C9))))) + LTrim(RTrim(Convert(char(10), @R36C9))) +
	Replicate('0',10) + Replicate('0',10) + Replicate('0',10) +
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R36C13))))) + LTrim(RTrim(Convert(char(10), @R36C13)))
   INSERT INTO fco_iss_mm VALUES (999936, @REN_36)

   /*******	RENGLON 37	******/
   SET @R37C4 = @R35C4 + @R36C4
   SET @R37C8 = @R35C8 + @R36C8
   SET @R37C9 = @R35C9 + @R36C9
   SET @R37C13 = @R35C13 + @R36C13
   SET @REN_37 = '000373' + @nro_meses + 
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R37C4))))) + LTrim(RTrim(Convert(char(10), @R37C4))) +
	Replicate('0',10) + Replicate('0',10) + Replicate('0',10) +
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R37C8))))) + LTrim(RTrim(Convert(char(10), @R37C8))) +
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R37C9))))) + LTrim(RTrim(Convert(char(10), @R37C9))) +
	Replicate('0',10) + Replicate('0',10) + Replicate('0',10) +
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R37C13))))) + LTrim(RTrim(Convert(char(10), @R37C13)))
   INSERT INTO fco_iss_mm VALUES (999937, @REN_37)

   /*******	RENGLON 38	******/
   SET @nro_rad_saf = Space(10)
   SET @vlr_saf_pe = 0
   SET @vlr_saf_fs = 0
   SET @vlr_saf_sa = 0

   IF @w_control_SUC = '0' 
       SELECT @nro_rad_saf = IsNull(A.referencia, ' '), @vlr_saf_pe = IsNull(A.valor_1,0), @vlr_saf_sa = IsNull(A.valor_2,0)
       FROM fco_ala_datos_adi A, fco_rel_sgss_da B
       WHERE A.sgss = B.sgss 
         AND A.item = B.ala_datos_adi
         AND A.tabla = @tabla
         AND A.cod_adm = 'ISS'
         AND b.secuencia = 4
    ELSE
       SELECT @nro_rad_saf = IsNull(A.referencia, ' '), @vlr_saf_pe = IsNull(A.valor_1,0), @vlr_saf_sa = IsNull(A.valor_2,0)
       FROM fco_ala_datos_adi A, fco_rel_sgss_da B
       WHERE A.sgss = B.sgss 
         AND A.item = B.ala_datos_adi
         AND A.tabla = @tabla
         AND A.cod_adm = 'ISS'
         AND b.secuencia = 4
         AND cod_suc = @w_control_SUC

   SET @R38C4 = @vlr_saf_pe
   SET @R38C8 = @vlr_saf_fs
   SET @R38C9 = @vlr_saf_sa
   SET @R38C13 = 0
   SET @REN_38 = '000383' + @nro_rad_saf +
        Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R38C4))))) + LTrim(RTrim(Convert(char(10), @R38C4))) +
        Replicate('0',10) + Replicate('0',10) + Replicate('0',10) + 
        Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R38C8))))) + LTrim(RTrim(Convert(char(10), @R38C8))) +
        Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R38C9))))) + LTrim(RTrim(Convert(char(10), @R38C9))) +
        Replicate('0',10) + Replicate('0',10) + Replicate('0',10) + Replicate('0',10)
   INSERT INTO fco_iss_mm VALUES (999938, @REN_38)

   /*******	RENGLON 39	******/
   SET @R39C4 = @R37C4 - @R38C4
   SET @R39C8 = @R37C8 - @R38C8
   SET @R39C9 = @R37C9 - @R38C9
   SET @R39C13 = @R37C13 - @R38C13
   SET @REN_39 = '000393' + Replicate('0',10) + 
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R39C4))))) + LTrim(RTrim(Convert(char(10), @R39C4))) +
	Replicate('0',10) + Replicate('0',10) + Replicate('0',10) +
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R39C8))))) + LTrim(RTrim(Convert(char(10), @R39C8))) +
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R39C9))))) + LTrim(RTrim(Convert(char(10), @R39C9))) +
	Replicate('0',10) + Replicate('0',10) + Replicate('0',10) +
	Replicate('0', 10-Len(LTrim(RTrim(Convert(char(10), @R39C13))))) + LTrim(RTrim(Convert(char(10), @R39C13))) + char(13)

   INSERT INTO fco_iss_mm VALUES (999939, @REN_39)


   /***********************************************************************/
   /*****          CONSTRUCCION DEL REGISTRO TIPO IV                  *****/
   /***********************************************************************/

   INSERT INTO fco_iss_mm VALUES (999940, '999994')

END
GO
GRANT EXECUTE ON dbo.sp_fco_crea_iss_mm TO Public
GO
