SET QUOTED_IDENTIFIER OFF
IF OBJECT_ID('dbo.sp_fco_crea_autolx_mm') IS NOT NULL
    DROP PROCEDURE dbo.sp_fco_crea_autolx_mm
GO
CREATE PROCEDURE dbo.sp_fco_crea_autolx_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:    Julio 10 de 2001                                  ***/
/*** Fecha FCO:   Abril 01 de 2006                                  ***/
/**********************************************************************/
AS

/* VARIABLES ESTRUCTURA MEDIO MAGNETICO */
   DECLARE 	@T3C1_34 Char(226),
		@T3C35	 char(6),
		@T3C36	 char(6),
		@T3C37	 char(6),
		@T35C18	 Numeric(14,0),
		@T35C19	 Numeric(14,0),
		@T35C20	 Numeric(14,0),
		@T35C21	 Numeric(14,0)

/* VARIABLES AUXILIARES */
   /* Para armar sentencias dinamicamente */
   DECLARE	@sql 	VarChar(3000),
		@sql2	VarChar(3000)

   /* Para controlar puntos y rayas en el registro_fiscal */
   DECLARE 	@nit	Char(9),
   		@dv     Char(1)

   /* Para hallar el Ciclo de liquidacion */
   DECLARE	@periodo Char(6)

   /* 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  varchar(3),
		@w_SUC_001  Char(3)

   /* Tabla donde estan los datos */
   DECLARE 	@tabla  varchar(30)
BEGIN

   /*** Establecer el Nombre de la Tabla ***/
  SET @tabla = 'FCO_VW_ISS_' + RTrim(@pvc_compania) + '_' + RTrim(@pvc_anio) + '_' + RTrim(@pvc_mes)

--   SET @codigo_ISS="ISS"
   DELETE fco_iss_mm

   /* Control del codigo de la sucursal */
   IF @pvc_cod_suc is null
      SET @w_cod_SUC = "''"
   ELSE
      SET @w_cod_SUC = SubString(LTrim(RTrim(@pvc_cod_suc)),1,3)

   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),
	     @dv =SubString(registro_fiscal, Len(RTrim(registro_fiscal)),1)
      FROM vw_fco_companias WHERE compania=@pvc_compania 
   END
   ELSE
   BEGIN
      SELECT @nit=SubString(registro_fiscal,1,9),
	     @dv =SubString(registro_fiscal, Len(RTrim(registro_fiscal)),1)
      FROM vw_fco_companias WHERE compania=@pvc_compania 
   END
   
   /* Periodo de Cotizacion (Ciclo de Liquidacion) */
   SET @periodo=LTrim(RTrim(@pvc_anio)) + Replicate('0',2-Len(LTrim(RTrim(@pvc_mes)))) + LTrim(RTrim(@pvc_mes))

   /* Forma de Presentación de la Autoliquidación de Aportes */
   SET @forma_pres = 'C'
   IF Len(@w_cod_SUC) > 0
   BEGIN
	SET @forma_pres='S';
	SET @w_SUC_001 = @w_cod_SUC + Space(3 - Len(@w_cod_SUC))
   END
   ELSE
   BEGIN
	SELECT @forma_pres = IsNull(forma_presentacion, 'U')
	FROM   vw_fco_companias
	WHERE  compania = @pvc_compania

        SET @w_SUC_001 = '001'
   END

   /***********************************************************************/
   /*****          CONSTRUCCION DEL REGISTRO TIPO 1                   *****/
   /***********************************************************************/

    INSERT INTO fco_iss_mm VALUES (16, '0005000' +
	'1' + Replicate('0',16) +
	Replicate('0',11-Len(LTrim(RTrim(@nit)))) + LTrim(RTrim(@nit)) + @dv + @w_SUC_001 +
	Replicate('0',15) + Replicate('0',173) + Space(99))

   /***********************************************************************/
   /*****          CONSTRUCCION DEL REGISTRO TIPO 2                   *****/
   /***********************************************************************/

    INSERT INTO fco_iss_mm VALUES (17, '0005000' +
	'2' + '0000' + '000001' + '000' + '000' + 
	Replicate('0',11-Len(LTrim(RTrim(@nit)))) + LTrim(RTrim(@nit)) + @dv + @w_SUC_001 +
	Replicate('0',15) + Replicate('0',36) + '001' + '001' + Replicate('0',14) +
	'N' + Replicate('0',150) + Space(65) + Space(4))


   /***********************************************************************/
   /*****          CONSTRUCCION DEL REGISTRO TIPO 3.0                 *****/
   /***********************************************************************/
    SELECT @T3C1_34 =
	'0005000' +
	'3' + '0000' + '000001' + '000' + '001' +
	Replicate('0',11-Len(LTrim(RTrim(@nit)))) + LTrim(RTrim(@nit)) + @dv + @w_SUC_001 +
	@periodo + '01' + '0' + '000000' + Replicate('0', 21)  + '00' +
	'N' + 
        Replicate('0',11-Len(LTrim(RTrim(@nit)))) + LTrim(RTrim(@nit)) +
        @dv + @w_SUC_001 + 
	LTrim(RTrim(SubString(nombre_cia,1,35))) + Space(35-Len(LTrim(RTrim(SubString(nombre_cia,1,35))))) + 
	@periodo + '01' +
	LTrim(RTrim(SubString(domicilio,1,45))) + Space(45-Len(LTrim(RTrim(SubString(domicilio,1,45))))) + 
	Replicate('0',3-Len(LTrim(RTrim(SubString(LTrim(cod_ciudad),1,3))))) + LTrim(RTrim(SubString(LTrim(cod_ciudad),1,3))) +
	Replicate('0',2-Len(LTrim(RTrim(SubString(LTrim(cod_dpto),1,2))))) + LTrim(RTrim(SubString(LTrim(cod_dpto),1,2))) +
	Replicate('0',7-Len(LTrim(RTrim(SubString(LTrim(telefono),1,7))))) + LTrim(RTrim(SubString(LTrim(telefono),1,7))) +
	Replicate('0',14) + '0' + '1' + 
	Replicate('0',11-Len(LTrim(RTrim(SubString(LTrim(nro_patronal),1,11))))) + LTrim(RTrim(SubString(LTrim(nro_patronal),1,11))) +
	'000000'
   FROM vw_fco_companias
   WHERE compania = @pvc_compania 
	
   /* Numero de Afiliados al ISS en Pensiones */
   SET @sql=
   " SELECT Total = Count(*)" +
   " INTO ##tmp_Afiliados" +
   " FROM " +
   " (SELECT Distinct COD_TRAB FROM " + @tabla +
   " WHERE COD_FPO='ISS'" +
   Case Len(@w_cod_SUC) WHEN 0 THEN "" ELSE " AND COD_SUC='" + @w_cod_SUC + "'" END +
   " GROUP BY COD_TRAB" +
   " HAVING SUM(C14A)>0) Afiliados"

   EXECUTE (@sql)

   SELECT @T3C35 = Replicate('0',6-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 @sql=
   " SELECT Total = Count(*)" +
   " INTO ##tmp_Afiliados" +
   " FROM " +
   " (SELECT Distinct COD_TRAB FROM " + @tabla +
   " WHERE COD_EPS='ISS'" +
   Case Len(@w_cod_SUC) WHEN 0 THEN "" ELSE " AND COD_SUC='" + @w_cod_SUC + "'" END +
   " GROUP BY COD_TRAB" +
   " HAVING SUM(C14C)>0) Afiliados"

   EXECUTE (@sql)

   SELECT @T3C36 = Replicate('0',6-Len(RTrim(LTrim(Convert(Char,Total))))) + RTrim(LTrim(Convert(Char,Total))) From ##tmp_Afiliados
   DROP TABLE ##tmp_Afiliados

   /* Numero de Renglones diligenciados */
   SET @sql=
   " SELECT Total = Count(*)" +
   " INTO ##tmp_Afiliados" +
   " FROM " +
   " (SELECT Distinct COD_TRAB FROM " + @tabla +
   Case Len(@w_cod_SUC) WHEN 0 THEN "" ELSE " WHERE COD_SUC='" + @w_cod_SUC + "'" END +
   " GROUP BY COD_TRAB" +
   " HAVING SUM(C14A+C14C)>0) Afiliados"

   EXECUTE (@sql)

   SELECT @T3C37 = Replicate('0',6-Len(RTrim(LTrim(Convert(Char,Total))))) + RTrim(LTrim(Convert(Char,Total))) From ##tmp_Afiliados
   DROP TABLE ##tmp_Afiliados


   INSERT INTO fco_iss_mm VALUES (18, @T3C1_34 + @T3C35 + @T3C36 + @T3C37 + 
	'2' + '2' + 'N' + Space(40) + Space(38) + ' ')


   /***********************************************************************/
   /*****          CONSTRUCCION DEL REGISTRO TIPO 3.3                 *****/
   /***********************************************************************/

   SET @sql=
	"SELECT Identity(Int,1,1) AS Sec, " +
	"LTrim(RTrim(SubString(C10A,1,1))) + Space(1-Len(LTrim(RTrim(SubString(C10A,1,1))))) + " +
	"Replicate('0',11 - Len(LTrim(RTrim(Convert(Char(11),C10B))))) + LTrim(RTrim(Convert(Char(11),C10B))) +" +
	"'0'+" + 
	"RTrim(LTrim(Substring(RTrim(LTrim(SubString(C10C, 1, CharIndex('/', C10C)-1))) + ' ' + RTrim(LTrim(SubString(Reverse(SubString(Reverse(C10C),1,CharIndex('/', Reverse(C10C))-1)), 1, Case When CharIndex(' ', Reverse(SubString(Reverse(C10C),1,CharIndex('/', Reverse(C10C))-1)))>0 Then CharIndex(' ', Reverse(SubString(Reverse(C10C),1,CharIndex('/', Reverse(C10C))-1))) Else Len(Reverse(SubString(Reverse(C10C),1,CharIndex('/', Reverse(C10C))-1))) End))) + ' ' + Case When CharIndex(' ', Reverse(SubString(Reverse(C10C),1,CharIndex('/', Reverse(C10C))-1))) = 0 Then Space(15-Len('')) Else RTrim(LTrim(SubString(Reverse(SubString(Reverse(C10C),1,CharIndex('/', Reverse(C10C))-1)), CharIndex(' ', Reverse(SubString(Reverse(C10C),1,CharIndex('/', Reverse(C10C))-1))) + 1, Len(Reverse(SubString(Reverse(C10C),1,CharIndex('/', Reverse(C10C))-1))) - CharIndex(' ', Reverse(LTrim(RTrim(SubString(Reverse(C10C),1,CharIndex('/', Reverse(C10C))-1)))))))) End,1,25))) +" +
	"Space(25-Len(RTrim(Ltrim(Substring(RTrim(LTrim(SubString(C10C, 1, CharIndex('/', C10C)-1))) + ' ' + RTrim(LTrim(SubString(Reverse(SubString(Reverse(C10C),1,CharIndex('/', Reverse(C10C))-1)), 1, Case When CharIndex(' ', Reverse(SubString(Reverse(C10C),1,CharIndex('/', Reverse(C10C))-1)))>0 Then CharIndex(' ', Reverse(SubString(Reverse(C10C),1,CharIndex('/', Reverse(C10C))-1))) Else Len(Reverse(SubString(Reverse(C10C),1,CharIndex('/', Reverse(C10C))-1))) End))) + ' ' + Case When CharIndex(' ', Reverse(SubString(Reverse(C10C),1,CharIndex('/', Reverse(C10C))-1))) = 0 Then Space(15-Len('')) Else RTrim(LTrim(SubString(Reverse(SubString(Reverse(C10C),1,CharIndex('/', Reverse(C10C))-1)), CharIndex(' ', Reverse(SubString(Reverse(C10C),1,CharIndex('/', Reverse(C10C))-1))) + 1, Len(Reverse(SubString(Reverse(C10C),1,CharIndex('/', Reverse(C10C))-1))) - CharIndex(' ', Reverse(LTrim(RTrim(SubString(Reverse(C10C),1,CharIndex('/', Reverse(C10C))-1)))))))) End,1,25))))) + " +
	"Case When RTrim(LTrim(IsNull(C11A,''))) = '' Then Space(1) Else C11A End +" +
	"Case When RTrim(LTrim(IsNull(C11B,''))) = '' Then Space(1) Else C11B End +" +
	"Case When RTrim(LTrim(IsNull(C11C,''))) = '' Then Space(1) Else C11C End +" +
	"Replicate('0',2 - Len(LTrim(RTrim(Convert(Char(2),Floor(C11D)))))) + LTrim(RTrim(Convert(Char(2),Floor(C11D)))) +" +
	"Replicate('0',2 - Len(LTrim(RTrim(Convert(Char(2),Floor(C11E)))))) + LTrim(RTrim(Convert(Char(2),Floor(C11E)))) +" +	
	"Replicate('0',2 - Len(LTrim(RTrim(Convert(Char(2),Floor(C12A)))))) + LTrim(RTrim(Convert(Char(2),Floor(C12A)))) +" +
	"Replicate('0',12 - Len(LTrim(RTrim(Convert(Char(12),Floor(C13A)))))) + LTrim(RTrim(Convert(Char(12),Floor(C13A)))) +"
   SET @sql2 =
	"Replicate('0',10 - Len(LTrim(RTrim(Convert(Char(10),Floor(C14A)))))) + LTrim(RTrim(Convert(Char(10),Floor(C14A)))) +" +
	"Replicate('0',10 - Len(LTrim(RTrim(Convert(Char(10),Floor(C14B)))))) + LTrim(RTrim(Convert(Char(10),Floor(C14B)))) +" +
	"Replicate('0',10 - Len(LTrim(RTrim(Convert(Char(10),Floor(C14C)))))) + LTrim(RTrim(Convert(Char(10),Floor(C14C)))) Registros" +
	" INTO ##TMP_DETALLE_ISS" +
	" FROM " + @tabla +
	" WHERE CIA='" + @pvc_compania + "'" +
   	Case Len(@w_cod_SUC) WHEN 0 THEN "" ELSE " AND COD_SUC='" + @w_cod_SUC + "'" END +
	" AND (C13A+C14A+C14C)>0"

   EXECUTE (@sql + @sql2)

   INSERT INTO fco_iss_mm
   SELECT 19+Sec,
	'0005000' + '3' + '0000' + '000001' + '000' + '001' +
	Replicate('0',11-Len(LTrim(RTrim(@nit)))) + LTrim(RTrim(@nit)) + @dv + @w_SUC_001 +
	@periodo + '01' + '3' +
	Replicate('0', 6- Len(RTrim(LTrim(Convert(Char(6),Sec))))) + RTrim(LTrim(Convert(Char(6),Sec))) + 
	Replicate('0',15) + Registros +
	Space(30) + Space(30) + Space(107) + ' '
   FROM ##TMP_DETALLE_ISS

   DROP TABLE ##TMP_DETALLE_ISS

   /***********************************************************************/
   /*****          CONSTRUCCION DEL REGISTRO TIPO 3.5                 *****/
   /***********************************************************************/
   /* Totales para los campos 18,19,20 y 21 */
   SELECT @sql = 
	" Select Coalesce(Floor(Sum(C13A)),0) As C13A, Coalesce(Floor(Sum(C14A)),0) As C14A, " +
	"  	 Coalesce(Floor(Sum(C14B)),0) As C14B, Coalesce(Floor(Sum(C14C)),0) As C14C" +
	" INTO ##TMP_TOTALES_ALA" +
	" From " + @tabla +
	" Where CIA='" + @pvc_compania + "'" +
	Case Len(@pvc_cod_suc) WHEN 0 THEN "" ELSE " AND COD_SUC='" + @pvc_cod_suc + "'" END
   EXECUTE (@sql)

   SELECT @T35C18 = C13A, @T35C19 = C14A, @T35C20 = C14B, @T35C21 = C14C
   FROM ##TMP_TOTALES_ALA

   DROP TABLE ##TMP_TOTALES_ALA

   INSERT INTO fco_iss_mm VALUES (999920, 
	'0005000' + '3' + '0000' + '000001' + '000' + '001' +
	Replicate('0',11-Len(LTrim(RTrim(@nit)))) + LTrim(RTrim(@nit)) + @dv + @w_SUC_001 +
	@periodo + '01' + '5' + '000000' + Replicate('0',14) + '0' +
	Replicate('0', 12 - Len(LTrim(RTrim(Convert(Char,@T35C18))))) + LTrim(RTrim(Convert(Char,@T35C18))) +
	Replicate('0', 10 - Len(LTrim(RTrim(Convert(Char,@T35C19))))) + LTrim(RTrim(Convert(Char,@T35C19))) +
	Replicate('0', 10 - Len(LTrim(RTrim(Convert(Char,@T35C20))))) + LTrim(RTrim(Convert(Char,@T35C20))) +
	Replicate('0', 10 - Len(LTrim(RTrim(Convert(Char,@T35C21))))) + LTrim(RTrim(Convert(Char,@T35C21))) +
	Space(21) + Space(21) +
	Replicate('0',12) + Replicate('0',10) + Replicate('0',10) + Replicate('0',10) +
	Space(131))

   /***********************************************************************/
   /*****          CONSTRUCCION DEL REGISTRO TIPO 7                   *****/
   /***********************************************************************/

   INSERT INTO fco_iss_mm VALUES (999921, 
	'0005000' + '7' + '0000' + '000001' + '000' + '000' +
	Replicate('0',11-Len(LTrim(RTrim(@nit)))) + LTrim(RTrim(@nit)) + @dv + @w_SUC_001 +
	'000000' + '00' + '0' + '000000' + '000001' + @T3C37 +
	Replicate('0', 14 - Len(LTrim(RTrim(Convert(Char,@T35C18))))) + LTrim(RTrim(Convert(Char,@T35C18))) +
	Replicate('0', 14 - Len(LTrim(RTrim(Convert(Char,@T35C19))))) + LTrim(RTrim(Convert(Char,@T35C19))) +
	Replicate('0', 14 - Len(LTrim(RTrim(Convert(Char,@T35C20))))) + LTrim(RTrim(Convert(Char,@T35C20))) +
	Replicate('0', 14 - Len(LTrim(RTrim(Convert(Char,@T35C21))))) + LTrim(RTrim(Convert(Char,@T35C21))) +
	Replicate('0',14) + Replicate('0',14) + Replicate('0',14) + Replicate('0',14) + 
	Replicate('0',14) + Replicate('0',14) + Replicate('0',14) + Replicate('0',14) + 
	Replicate('0', 14 - Len(LTrim(RTrim(Convert(Char,Floor(@T35C21/12)))))) + LTrim(RTrim(Convert(Char,Floor(@T35C21/12)))) +
	Space(78))


   /***********************************************************************/
   /*****          CONSTRUCCION DEL REGISTRO TIPO 8                   *****/
   /***********************************************************************/


   INSERT INTO fco_iss_mm VALUES (999922, 
	'0005000' + '8' + '0000' + '000000' + '000' + '000' +
	Replicate('0',11-Len(LTrim(RTrim(@nit)))) + LTrim(RTrim(@nit)) + @dv + @w_SUC_001 +
	'000000' + '00' + '0' + '000000' + '000001' + @T3C37 +
	Replicate('0', 14 - Len(LTrim(RTrim(Convert(Char,@T35C18))))) + LTrim(RTrim(Convert(Char,@T35C18))) +
	Replicate('0', 14 - Len(LTrim(RTrim(Convert(Char,@T35C19))))) + LTrim(RTrim(Convert(Char,@T35C19))) +
	Replicate('0', 14 - Len(LTrim(RTrim(Convert(Char,@T35C20))))) + LTrim(RTrim(Convert(Char,@T35C20))) +
	Replicate('0', 14 - Len(LTrim(RTrim(Convert(Char,@T35C21))))) + LTrim(RTrim(Convert(Char,@T35C21))) +
	Replicate('0',14) + Replicate('0',14) + Replicate('0',14) + Replicate('0',14) + 
	Replicate('0',14) + Replicate('0',14) + Replicate('0',14) + Replicate('0',14) + 
	Replicate('0', 14 - Len(LTrim(RTrim(Convert(Char,Floor(@T35C21/12)))))) + LTrim(RTrim(Convert(Char,Floor(@T35C21/12)))) +
	Space(78))

END

GO
GRANT EXECUTE ON dbo.sp_fco_crea_autolx_mm TO PUBLIC
GO
