IF OBJECT_ID('dbo.sp_fco_crea_pension_mm') IS NOT NULL
    DROP PROCEDURE dbo.sp_fco_crea_pension_mm
GO
CREATE PROCEDURE dbo.sp_fco_crea_pension_mm
(
   @pvc_compania varchar(4),
   @pvc_anio varchar(4),
   @pvc_mes varchar(2),
   @pvc_cod_suc varchar(10),
   @pvc_fpo	varchar(10)
)
/**********************************************************************/
/*** Modulo:      Modulo Fiscal Colombiano Version FCO - ADAM V.3   ***/
/*** Descripcion: Crea archivo plano para Pensión                   ***/
/*** Autor:       Juan Carlos Leguizamon                            ***/
/*** Fecha MF:    Junio 30 de 2001                                  ***/
/*** Fecha FCO:   Marzo 31 de 2006                                  ***/
/**********************************************************************/
AS
 /* VARIABLES ESTRUCTURA MEDIO MAGNETICO */
DECLARE
   @CI01_06	char(58),
   @CI07_18 	char(148),
   @CI19_27 	char(57),
   @CI28	char(5),

   @R31C4	Numeric(10,0),
   @R31C5	Numeric(10,0),
   @R31C6	Numeric(10,0),
   @R31C7	Numeric(10,0),
   @R31C8	Numeric(10,0),

   @R36C3	Numeric(10,0),
   @R36C7	Numeric(10,0),
   @R36C8	Numeric(10,0),

   @REN31	char(66),   
   @REN36	char(66),  
   @REN39	char(66)

   /* VARIABLES AUXILIARES (o de trabajo W) */

   /* Para armar sentencias dinamicamente */
   DECLARE @sql  varchar(3000),
    	   @sql2 varchar(3000),
	       @sql3 varchar(3000)

   /* Para hallar la forma de presentación y la sucursal */
   DECLARE @forma_pres	char(1),
   	   @cod_SUC	char(10),
	   @control_SUC varchar(10)

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

   /* Para controlar puntos y rayas en el registro_fiscal */
   DECLARE @nit	char(9)

BEGIN

  SET @tabla = 'FCO_FPO_' + RTrim(@pvc_compania) + '_' + RTrim(@pvc_anio) + '_' + RTrim(@pvc_mes)
   DELETE fco_fpo_mm

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

   SELECT @CI01_06 = '00000101' + 
	LTrim(RTrim(SubString(descripcion,1,40))) + Space(40-Len(LTrim(RTrim(SubString(descripcion,1,40))))) +
	Replicate('0', 9-Len(LTrim(RTrim(SubString(registro_fiscal,1,9))))) + LTrim(RTrim(SubString(registro_fiscal,1,9))) +
	LTrim(RTrim(SubString(digito_verificacion,1,1))) + Space(1 - Len(LTrim(RTrim(SubString(digito_verificacion,1,1)))))
   FROM vw_fco_administradoras
   WHERE compania = @pvc_compania AND tipo = 'AFP' AND codigo = @pvc_fpo

   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 @control_SUC = IsNull(@pvc_cod_suc,'0')
   IF Len(@control_SUC)=0
	SET @control_SUC='0'

   IF @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(@nit)) + @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(@nit)) + @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

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

        SET @cod_SUC = ' '
   END
   ELSE
   BEGIN
	SET @forma_pres='S';
	SET @cod_SUC = @control_SUC
   END

    /* Numero de trabajadores */
   SET @sql=
   " 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 @control_SUC WHEN '0' THEN "" ELSE " AND COD_SUC='" + @control_SUC + "'" END + ")"
   EXECUTE (@sql)

   SELECT @CI19_27 =
	Replicate('0',4-Len(LTrim(RTrim(Convert(char,@pvc_anio))))) + LTrim(RTrim(Convert(char,@pvc_anio))) + 
	Replicate('0',2-Len(LTrim(RTrim(Convert(char,@pvc_mes))))) + LTrim(RTrim(Convert(char,@pvc_mes))) + 
	Space(1) + Space(6) + Space(8) +
	Space(10) + Space(10) + @forma_pres + @cod_SUC +
	Replicate('0',5-Len(RTrim(LTrim(Convert(char,Total))))) + RTrim(LTrim(Convert(char,Total)))
   FROM ##tmp_trabajadores
   DROP TABLE ##tmp_trabajadores
    
   /* Numero de Afiliados a este fondo */
   SET @sql=
   " SELECT Total = Count(*)" +
   " INTO ##tmp_Afiliados" +
   " FROM " +
   " (SELECT Distinct COD_TRAB" +
   " FROM " + @tabla +
   " WHERE COD_FPO='" + @pvc_fpo + "'" +
   Case @control_SUC WHEN '0' THEN "" ELSE " AND COD_SUC='" + @control_SUC + "'" END +
   " GROUP BY COD_TRAB" +
   " HAVING SUM(C19A)>0) Afiliados"
   EXECUTE (@sql)

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

   INSERT INTO fco_fpo_mm VALUES (0, @CI01_06 + @CI07_18 + @CI19_27 + @CI28)

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

   SET @sql =
	"SELECT Identity(Int,1,1) AS Sec, " +
	"'2' + LTrim(RTrim(SubString(C11A,1,1))) + Space(1-Len(LTrim(RTrim(SubString(C11A,1,1))))) + " +
	"Replicate('0',12 - Len(LTrim(RTrim(Convert(char(12),C11B))))) + LTrim(RTrim(Convert(char(12),C11B))) +" +
	"RTrim(LTrim(SubString(SubString(C12ABC, 1, charIndex('/', C12ABC)-1),1,15))) + Space(15-Len(RTrim(LTrim(SubString(SubString(C12ABC, 1, charIndex('/', C12ABC)-1),1,15))))) +" +
	"RTrim(LTrim(SubString(SubString(C12ABC, charIndex('/', C12ABC) + 1, charIndex('/', C12ABC, charIndex('/', C12ABC)+1) - charIndex('/', C12ABC)-1),1,15))) + Space(15-Len(RTrim(LTrim(SubString(SubString(C12ABC, charIndex('/', C12ABC) + 1, charIndex('/', C12ABC, charIndex('/', C12ABC)+1) - charIndex('/', C12ABC)-1),1,15))))) +" +
	"RTrim(LTrim(SubString(SubString(Reverse(SubString(Reverse(C12ABC),1,charIndex('/', Reverse(C12ABC))-1)), 1, Case When charIndex(' ', Reverse(SubString(Reverse(C12ABC),1,charIndex('/', Reverse(C12ABC))-1)))>0 Then charIndex(' ', Reverse(SubString(Reverse(C12ABC),1,charIndex('/', Reverse(C12ABC))-1))) Else Len(Reverse(SubString(Reverse(C12ABC),1,charIndex('/', Reverse(C12ABC))-1))) End),1,15))) + Space(15-Len(RTrim(LTrim(SubString(SubString(Reverse(SubString(Reverse(C12ABC),1,charIndex('/', Reverse(C12ABC))-1)), 1, Case When charIndex(' ', Reverse(SubString(Reverse(C12ABC),1,charIndex('/', Reverse(C12ABC))-1)))>0 Then charIndex(' ', Reverse(SubString(Reverse(C12ABC),1,charIndex('/', Reverse(C12ABC))-1))) Else Len(Reverse(SubString(Reverse(C12ABC),1,charIndex('/', Reverse(C12ABC))-1))) End),1,15))))) +" +
	"Case When charIndex(' ', Reverse(SubString(Reverse(C12ABC),1,charIndex('/', Reverse(C12ABC))-1))) = 0 Then space(15-Len('')) Else RTrim(LTrim(SubString(SubString(Reverse(SubString(Reverse(C12ABC),1,charIndex('/', Reverse(C12ABC))-1)), charIndex(' ', Reverse(SubString(Reverse(C12ABC),1,charIndex('/', Reverse(C12ABC))-1))) + 1, Len(Reverse(SubString(Reverse(C12ABC),1,charIndex('/', Reverse(C12ABC))-1))) - charIndex(' ', Reverse(LTrim(RTrim(SubString(Reverse(C12ABC),1,charIndex('/', Reverse(C12ABC))-1)))))),1,15))) + Space(15-Len(RTrim(LTrim(SubString(SubString(Reverse(SubString(Reverse(C12ABC),1,charIndex('/', Reverse(C12ABC))-1)), charIndex(' ', Reverse(SubString(Reverse(C12ABC),1,charIndex('/', Reverse(C12ABC))-1))) + 1, Len(Reverse(SubString(Reverse(C12ABC),1,charIndex('/', Reverse(C12ABC))-1))) - charIndex(' ', Reverse(LTrim(RTrim(SubString(Reverse(C12ABC),1,charIndex('/', Reverse(C12ABC))-1)))))),1,15))))) End +" +
	"Case When RTrim(LTrim(IsNull(C13A,''))) = '' Then ' ' Else C13A End +" +
	"Case When RTrim(LTrim(IsNull(C13B,''))) = '' Then ' ' Else C13B End +" +
	"Case When RTrim(LTrim(IsNull(C13C,''))) = '' Then ' ' Else C13C End +" +
	"Case When RTrim(LTrim(IsNull(C13D,''))) = '' Then ' ' Else C13D End +"
   SET @sql2 =
	"Case When RTrim(LTrim(IsNull(C13E,''))) = '' Then ' ' Else C13E End +" +
	"Case When RTrim(LTrim(IsNull(C13F,''))) = '' Then ' ' Else C13F End +" +
	"Case When RTrim(LTrim(IsNull(C13G,''))) = '' Then ' ' Else C13G End +" +
	"Case When RTrim(LTrim(IsNull(C13H,''))) = '' Then ' ' Else C13H End +" +
	"Case When RTrim(LTrim(IsNull(C13I,''))) = '' Then ' ' Else C13I End +" +
	"Case When RTrim(LTrim(IsNull(C13J,''))) = '' Then ' ' Else C13J End +" +
	"Case When RTrim(LTrim(IsNull(C13K,''))) = '' Then ' ' Else C13K End +" +
	"Case When (C20A + C20B) = 0 Then ' ' Else 'X' End +" +
	"Replicate('0',2 - Len(LTrim(RTrim(Convert(char(2),Floor(C14A)))))) + LTrim(RTrim(Convert(char(2),Floor(C14A)))) +" +
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C15A)))))) + LTrim(RTrim(Convert(char(9),Floor(C15A)))) +" +
	"Replicate('0',9) +"
   SET @sql3 =
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C17A)))))) + LTrim(RTrim(Convert(char(9),Floor(C17A)))) +" +
	"Replicate('0',6 - Len(LTrim(RTrim(Convert(char(6),C18A))))) + LTrim(RTrim(Convert(char(6),C18A))) +" +
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C19A)))))) + LTrim(RTrim(Convert(char(9),Floor(C19A)))) +" +
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C20A)))))) + LTrim(RTrim(Convert(char(9),Floor(C20A)))) +" +
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C20B)))))) + LTrim(RTrim(Convert(char(9),Floor(C20B)))) +" +
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C19A+C20A+C20B)))))) + LTrim(RTrim(Convert(char(9),Floor(C19A+C20A+C20B)))) +" +
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C22A)))))) + LTrim(RTrim(Convert(char(9),Floor(C22A)))) +" +
	"Replicate('0',9 - Len(LTrim(RTrim(Convert(char(9),Floor(C23A)))))) + LTrim(RTrim(Convert(char(9),Floor(C23A)))) As Registro2" +
	" INTO ##TMP_DETALLE_FPO" +
	" FROM " + @tabla +
	" WHERE CIA='" + @pvc_compania + "'" +
	" And COD_FPO='" + @pvc_fpo + "'" +
   	Case @control_SUC WHEN '0' THEN "" ELSE " AND COD_SUC='" + @control_SUC + "'" END +
	" AND C19A>0"
   EXECUTE (@sql + @sql2 + @sql3)

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

   DROP TABLE ##TMP_DETALLE_FPO

   /***********************************************************************/
   /*****          CONSTRUCCION DEL REGISTRO TIPO III                 *****/
   /***********************************************************************/

   /* Totales para los campos del Renglon 31 */
   SELECT @sql = 
	" Select IsNull(Floor(Sum(C19A)),0) As C19A, IsNull(Floor(Sum(C20A)),0) As C20A, " +
	"  	 IsNull(Floor(Sum(C20B)),0) As C20B, IsNull(Floor(Sum(C19A+C20A+C20B)),0) As C21A, " +
	"  	 IsNull(Floor(Sum(C22A)),0) As C22A" +
	" INTO ##TMP_TOTALES_F01" +
	" From " + @tabla +
	" Where CIA='" + @pvc_compania + "'" +
	" And COD_FPO='" + @pvc_fpo + "'" +
	Case @control_SUC WHEN '0' THEN "" ELSE " AND COD_SUC='" + @control_SUC + "'" END
   EXECUTE (@sql)

   SELECT @R31C4 = C19A, @R31C5 = C20A, @R31C6 = C20B, @R31C7 = C21A, @R31C8 = C22A
   FROM ##TMP_TOTALES_F01

   DROP TABLE ##TMP_TOTALES_F01

   /*******	RENGLON 31	******/
   SET @REN31 = '000313' + Replicate('0',10) +	Replicate('0', 10 - Len(LTrim(RTrim(Convert(char,@R31C4))))) + LTrim(RTrim(Convert(char,@R31C4))) +
	Replicate('0', 10 - Len(LTrim(RTrim(Convert(char,@R31C5))))) + LTrim(RTrim(Convert(char,@R31C5))) +
	Replicate('0', 10 - Len(LTrim(RTrim(Convert(char,@R31C6))))) + LTrim(RTrim(Convert(char,@R31C6))) +
	Replicate('0', 10 - Len(LTrim(RTrim(Convert(char,@R31C7))))) + LTrim(RTrim(Convert(char,@R31C7))) +
	Replicate('0', 10 - Len(LTrim(RTrim(Convert(char,@R31C8))))) + LTrim(RTrim(Convert(char,@R31C8)))
   INSERT INTO fco_fpo_mm VALUES (999931, @REN31)


   /*******	RENGLON 36	******/
   SET @R36C3=0
   SET @R36C7=0
   SET @R36C8=0
   SET @REN36 = '000363' + 
	Replicate('0', 10 - Len(LTrim(RTrim(Convert(char,@R36C3))))) + LTrim(RTrim(Convert(char,@R36C3))) +
	Replicate('0', 10) + Replicate('0', 10) + Replicate('0', 10) +
	Replicate('0', 10 - Len(LTrim(RTrim(Convert(char,@R36C7))))) + LTrim(RTrim(Convert(char,@R36C7))) +
	Replicate('0', 10 - Len(LTrim(RTrim(Convert(char,@R36C8))))) + LTrim(RTrim(Convert(char,@R36C8)))
   INSERT INTO fco_fpo_mm VALUES (999936, @REN36)


   /*******	RENGLON 39	******/
   SET @REN39 = '000393' + Replicate('0', 10) +
	Replicate('0', 10) + Replicate('0', 10) + Replicate('0', 10) +
	Replicate('0', 10 - Len(LTrim(RTrim(Convert(char,@R31C7+@R36C7))))) + LTrim(RTrim(Convert(char,@R31C7+@R36C7))) +
	Replicate('0', 10 - Len(LTrim(RTrim(Convert(char,@R31C8+@R36C8))))) + LTrim(RTrim(Convert(char,@R31C8+@R36C8)))


   INSERT INTO fco_fpo_mm VALUES (999939, @REN39)

   /***********************************************************************/
   /*****          CONSTRUCCION DEL REGISTRO TIPO IV                  *****/
   /***********************************************************************/
   INSERT INTO fco_fpo_mm VALUES (999940, '999994')

END
GO
GRANT EXECUTE ON dbo.sp_fco_crea_pension_mm TO PUBLIC
GO
