CREATE OR REPLACE PROCEDURE sp_fco_crea_pension_mm
(
    pch_compania    char,
    pch_anio        char,
    pch_mes         char,
    pch_sucursal    char,
    pch_FPO         char
)
/**************************************************************************/
/*** Modulo:      Modulo Fiscal Colombiano ADAM V.3			***/
/*** Descripcion: Genera el Medio Magnetico de acuerdo con las 		***/
/***		  especificaciones SIGA - Formato 01			***/
/*** Tablas:	  Administradoras, MF_FPO_CIA_año_mes, trabajadores_gra ***/
/*** Vistas:	  vw_fco_administradoras, vw_companias                      ***/ 
/*** Autor:	  Juan Carlos Leguizamon				***/
/*** Fecha:	  Junio de 2001 					***/
/*** Revision: 	  Febrero 28 de 2002					***/
/*** Motor:	  SQL Server 7.0	Version interna: 1.00		***/
/**************************************************************************/

AS
   /* VARIABLES ESTRUCTURA MEDIO MAGNETICO */
    vch_CI01_06    char(58);
    vch_CI07_18    char(148);
    vch_CI19_27    char(57);
    vch_CI28       char(5);

    vnm_R31C4	   number(10,0);
    vnm_R31C5	   number(10,0);
    vnm_R31C6	   number(10,0);
    vnm_R31C7	   number(10,0);
    vnm_R31C8	   number(10,0);

    vnm_R36C3	   number(10,0);
    vnm_R36C7	   number(10,0);
    vnm_R36C8	   number(10,0);

    vch_Ren31	   char(66);
    vch_Ren36	   char(66);
    vch_Ren39	   char(66);

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

   /* Para armar sentencias dinamicamente */
    vvc_sql      varchar2(3000);

   /* Para hallar la forma de presentación y la sucursal */
    vch_forma_pres    char(1);
    vch_sucursal      char(10);
    vvc_control_suc   varchar2(10);

   /* Tabla donde estan los datos */
    vvc_tabla    varchar2(30);

   /* Para controlar puntos y rayas en el registro_fiscal */
    vch_nit	 char(9);

    vin_id_cursor  integer;
    vin_rows	   integer;

    vnm_total      number(5,0);

BEGIN

    /*** Establecer el Nombre de la Tabla ***/
    vvc_tabla := 'FCO_FPO_' || RTrim(pch_compania) || '_' || LTrim(pch_anio) || '_' || LTrim(pch_mes);

    DELETE FCO_FPO_MM;
    COMMIT;

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

    SELECT '00000101' || 
	RPad(Substr(descripcion,1,40),40,' ') ||
        LPad(Substr(registro_fiscal,1,9),9,'0') ||
        RPad(Substr(digito_verificacion,1,1)||' ',1,' ')
    INTO vch_CI01_06
    FROM vw_fco_administradoras
    WHERE compania = pch_compania AND tipo='AFP' AND RTRIM(codigo)=pch_FPO;

    SELECT LPad(RTrim(fn_fco_tratar_NIT(pch_compania)), 9, '0')
    INTO vch_nit
    FROM vw_fco_companias
    WHERE compania = pch_compania;

    /* Control del codigo de la sucursal */
    vvc_control_suc := Nvl(RTrim(pch_sucursal),'NA');
    IF Nvl(Length(RTrim(pch_sucursal)),0) = 0 THEN
        vvc_control_suc := 'NA';
    END IF;

    IF vvc_control_suc = 'NA' THEN
        SELECT
            RPad(Substr(C.nombre_cia,1,40),40,' ') || 
            'N' || vch_nit ||
            Substr(C.registro_fiscal, Length(RTrim(C.registro_fiscal)),1) ||
            RPad(Substr(C.clase_aportante,1,1),1,' ') || 
            RPad(Substr(C.domicilio,1,40),40,' ') ||
            RPad(Substr(M.nombre,1,15),15,' ') || 
            LPad(Substr(LTrim(M.codigo),1,4),4,'0') || 
            RPad(Substr(D.nombre,1,15),15,' ') || 
            LPad(Substr(LTrim(D.codigo),1,2),2,'0') ||
            RPad(Substr(C.telefono,1,10),10,' ') || 
            RPad(Substr(C.fax,1,10),10,' ')
        INTO
            vch_CI07_18
        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 = pch_compania;
    ELSE
        SELECT
	    RPad(Substr(C.nombre_cia,1,40),40,' ') || 
            'N' || vch_nit ||
            Substr(C.registro_fiscal, Length(RTrim(C.registro_fiscal)),1) ||
            RPad(Substr(C.clase_aportante,1,1),1,' ') || 
            RPad(Substr(SUC.direccion,1,40),40,' ') ||
            RPad(Substr(M.nombre,1,15),15,' ') ||
            LPad(Substr(LTrim(M.codigo),1,4),4,'0') ||
            RPad(Substr(D.nombre,1,15),15,' ') || 
            LPad(Substr(LTrim(D.codigo),1,2),2,'0') ||
            RPad(Substr(SUC.telefono,1,10),10,' ') || 
            RPad(Substr(SUC.fax,1,10),10,' ')
        INTO
            vch_CI07_18
        FROM
            vw_fco_companias C,
            fco_sucursales SUC, 
            fco_ciudades M,
            fco_departamentos D
        WHERE  	
            SUC.compania = C.compania And
            M.codigo   = SUC.cod_ciudad And
            D.codigo   = SUC.cod_dpto And
            M.cod_dpto = D.codigo And
            C.compania = pch_compania And
            RTrim(SUC.sucursal) = RTrim(pch_sucursal);
    END IF;

    /* Forma de Presentación de la Autoliquidación de Aportes */
    IF vvc_control_suc = 'NA' THEN
        SELECT Nvl(forma_presentacion,'U')
        INTO vch_forma_pres
	FROM vw_fco_companias
	WHERE compania = pch_compania;
        vch_sucursal := ' ';
    ELSE
	vch_forma_pres := 'S';
	vch_sucursal := vvc_control_suc;
    END IF;

    /* Numero de trabajadores */
    vnm_total := 0;
    SELECT   
        ' SELECT Count(*)' ||
        ' FROM ' || vvc_tabla ||
        ' WHERE COD_TRAB IN (SELECT trabajador FROM trabajadores_grales ' ||
                            'WHERE compania = ''' || pch_compania || ''' And ' ||
                                  'sit_trabajador = 1)' ||
        Decode(RTrim(vvc_control_suc),'NA','',' AND RTrim(COD_SUC)=''' || RTrim(pch_sucursal) || '''')
    INTO vvc_sql
    FROM DUAL;
    BEGIN
        vin_id_cursor := dbms_sql.open_cursor;
        dbms_sql.parse(vin_id_cursor, vvc_sql, dbms_sql.native);
        dbms_sql.define_column(vin_id_cursor, 1, vnm_total);
        vin_rows := dbms_sql.execute(vin_id_cursor);
        IF dbms_sql.fetch_rows(vin_id_cursor) > 0 THEN
            dbms_sql.column_value(vin_id_cursor, 1, vnm_total);
        END IF;
        commit;
        dbms_sql.close_cursor(vin_id_cursor);
    EXCEPTION
        WHEN OTHERS THEN
            Raise_Application_Error (-20001, 'Error al recuperar el nro de trabajadores');
    END;

    vch_CI19_27 :=
	pch_anio || LPad(RTRim(pch_mes),2,'0') || RPad(' ',35,' ') || vch_forma_pres || vch_sucursal ||
	LPad(RTrim(To_Char(vnm_total)), 5, '0');
    
    /* Numero de Afiliados a esta FPO */
    vnm_total := 0;
    SELECT   
        ' SELECT Count(*)' ||
        ' FROM ' || vvc_tabla ||
        ' WHERE RTrim(COD_FPO) = ''' || pch_FPO || '''' || 
        Decode(RTrim(vvc_control_suc),'NA','',' AND RTrim(COD_SUC)=''' || RTrim(pch_sucursal) || '''') ||
	' AND (C19A > 0 OR C13H = ''X'')'
    INTO vvc_sql
    FROM DUAL;
    BEGIN
        vin_id_cursor := dbms_sql.open_cursor;
        dbms_sql.parse(vin_id_cursor, vvc_sql, dbms_sql.native);
        dbms_sql.define_column(vin_id_cursor, 1, vnm_total);
        vin_rows := dbms_sql.execute(vin_id_cursor);
        IF dbms_sql.fetch_rows(vin_id_cursor) > 0 THEN
            dbms_sql.column_value(vin_id_cursor, 1, vnm_total);
        END IF;
        commit;
        dbms_sql.close_cursor(vin_id_cursor);
    EXCEPTION
        WHEN OTHERS THEN
            Raise_Application_Error (-20001, 'Error al recuperar el nro de afiliados');
    END;

    vch_CI28 := LPad(RTrim(To_Char(vnm_total)), 5, '0');

    INSERT INTO FCO_FPO_MM VALUES (0, vch_CI01_06 || vch_CI07_18 || vch_CI19_27 || vch_CI28);
    COMMIT;

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

   SELECT 
   ' INSERT INTO FCO_FPO_MM' ||
   ' SELECT RowNum,' ||
	' LPad(RowNum, 5, ''0'') ||' ||
	' ''2''||RPad(SubStr(C11A,1,1),1,'' '') ||' ||
   	' LPad(RTrim(SubStr(C11B,1,12)), 12, ''0'') ||' ||
   	' fn_Separar_Nombre(C12ABC,15,15,15,15) ||' ||
   	' Decode(Nvl(Length(RTrim(C13A)),0),0,'' '',C13A) ||' ||
   	' Decode(Nvl(Length(RTrim(C13B)),0),0,'' '',C13B) ||' ||
   	' Decode(Nvl(Length(RTrim(C13C)),0),0,'' '',C13C) ||' ||
   	' Decode(Nvl(Length(RTrim(C13D)),0),0,'' '',C13D) ||' ||
   	' Decode(Nvl(Length(RTrim(C13E)),0),0,'' '',C13E) ||' ||
   	' Decode(Nvl(Length(RTrim(C13F)),0),0,'' '',C13F) ||' ||
   	' Decode(Nvl(Length(RTrim(C13G)),0),0,'' '',C13G) ||' ||
   	' Decode(Nvl(Length(RTrim(C13H)),0),0,'' '',C13H) ||' ||
   	' Decode(Nvl(Length(RTrim(C13I)),0),0,'' '',C13I) ||' ||
   	' Decode(Nvl(Length(RTrim(C13J)),0),0,'' '',C13J) ||' ||
   	' Decode(Nvl(Length(RTrim(C13K)),0),0,'' '',C13K) ||' ||
   	' Decode(Nvl(Length(RTrim(C13L)),0),0,'' '',C13L) ||' ||
	' LPad(To_Char(Floor(Nvl(C14A,0))),2,''0'') ||' ||
	' LPad(To_Char(Floor(Nvl(C15A,0))),9,''0'') ||' ||
	' LPad(''0'', 9, ''0'') ||' ||
	' LPad(To_Char(Floor(Nvl(C17A,0))),9,''0'') ||' ||
	' ''0'' ||  RPad(To_Char(Nvl(C18A,0)),5,''0'') ||' ||
	' LPad(To_Char(Floor(Nvl(C19A,0))),9,''0'') ||' ||
	' LPad(To_Char(Floor(Nvl(C20A,0))),9,''0'') ||' ||
	' LPad(To_Char(Floor(Nvl(C20B,0))),9,''0'') ||' ||
	' LPad(To_Char(Floor(Nvl(C19A+C20A+C20B,0))),9,''0'') ||' ||
	' LPad(To_Char(Floor(Nvl(C22A,0))),9,''0'') ||' ||
	' LPad(To_Char(Floor(Nvl(C23A,0))),9,''0'')' ||
        ' FROM '||vvc_tabla||
        ' WHERE CIA=' || '''' || pch_compania || '''' ||
        ' AND RTrim(COD_FPO)=' ||''''||RTrim(pch_FPO)||'''' ||
        Decode(RTrim(vvc_control_suc),'NA','',' AND RTrim(COD_SUC)=''' || RTrim(pch_sucursal) || ''')') ||
        ' AND (C19A >0 OR C13H = ''X'')'
    INTO vvc_sql
    FROM DUAL; 

    BEGIN
        vin_id_cursor := dbms_sql.open_cursor;
        dbms_sql.parse(vin_id_cursor, vvc_sql, dbms_sql.native);
        vin_rows := dbms_sql.execute(vin_id_cursor);
        dbms_sql.close_cursor(vin_id_cursor);
    EXCEPTION
        WHEN OTHERS THEN
            Raise_Application_Error (-20001, 'Error al llenar la tabla FCO_FPO_MM con los registros Tipo 2');
    END;

    COMMIT;

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

    /* Totales para los campos del Renglon 31 */
   SELECT
	' SELECT  Nvl(Floor(Sum(C19A)),0), Nvl(Floor(Sum(C20A)),0),' ||
		' Nvl(Floor(Sum(C20B)),0), Nvl(Floor(Sum(C19A+C20A+C20B)),0),' ||
		' Nvl(Floor(Sum(C22A)),0)' ||
	' FROM '||vvc_tabla||
        ' WHERE CIA = '''|| pch_compania || '''' ||
        '   AND RTRIM(COD_FPO)='''|| pch_FPO || '''' ||
        Decode(RTrim(vvc_control_suc),'NA','',' AND RTrim(COD_SUC)=''' || RTrim(pch_sucursal) || '''')
    INTO vvc_sql
    FROM dual;
   BEGIN
      vin_id_cursor := dbms_sql.open_cursor;
      dbms_sql.parse(vin_id_cursor, vvc_sql, dbms_sql.native);
      dbms_sql.define_column(vin_id_cursor, 1, vnm_R31C4);
      dbms_sql.define_column(vin_id_cursor, 2, vnm_R31C5);
      dbms_sql.define_column(vin_id_cursor, 3, vnm_R31C6);
      dbms_sql.define_column(vin_id_cursor, 4, vnm_R31C7);
      dbms_sql.define_column(vin_id_cursor, 5, vnm_R31C8);
      vin_rows := dbms_sql.execute(vin_id_cursor);
      IF dbms_sql.fetch_rows(vin_id_cursor) > 0 THEN
	 dbms_sql.column_value(vin_id_cursor, 1, vnm_R31C4);
	 dbms_sql.column_value(vin_id_cursor, 2, vnm_R31C5);
	 dbms_sql.column_value(vin_id_cursor, 3, vnm_R31C6);
	 dbms_sql.column_value(vin_id_cursor, 4, vnm_R31C7);
	 dbms_sql.column_value(vin_id_cursor, 5, vnm_R31C8);
      END IF;
      commit;
      dbms_sql.close_cursor(vin_id_cursor);
    EXCEPTION
        WHEN OTHERS THEN
            Raise_Application_Error (-20001, 'Error al calcular los totales del renglon 31');
    END;

    /*******	RENGLON 31	******/
    vch_Ren31 := '000313' || LPad('0',10,'0') ||
        LPad(To_Char(vnm_R31C4), 10, '0') ||
        LPad(To_Char(vnm_R31C5), 10, '0') ||
        LPad(To_Char(vnm_R31C6), 10, '0') ||
        LPad(To_Char(vnm_R31C7), 10, '0') ||
        LPad(To_Char(vnm_R31C8), 10, '0');

    INSERT INTO FCO_FPO_MM VALUES (999931, vch_Ren31);
    COMMIT;

   /*******	RENGLON 36	******/
    vnm_R36C3 := 0;
    vnm_R36C7 := 0;
    vnm_R36C8 := 0;
    vch_Ren36 := '000363' || 
        LPad(To_Char(vnm_R36C3), 10, '0') ||
	LPad('0',10,'0') || LPad('0',10,'0') || LPad('0',10,'0') ||
        LPad(To_Char(vnm_R36C7), 10, '0') ||
        LPad(To_Char(vnm_R36C8), 10, '0');

    INSERT INTO FCO_FPO_MM VALUES (999936, vch_Ren36);
    COMMIT;

    /*******	RENGLON 39	******/
    vch_Ren39 := '000393' || LPad('0',10,'0') ||
	LPad('0',10,'0') || LPad('0',10,'0') || LPad('0',10,'0') ||
        LPad(To_Char(vnm_R31C7 + vnm_R36C7), 10, '0') ||
        LPad(To_Char(vnm_R31C8 + vnm_R36C8), 10, '0');

    INSERT INTO FCO_FPO_MM VALUES (999939, vch_Ren39);
    COMMIT;

   /***********************************************************************/
   /*****          CONSTRUCCION DEL REGISTRO TIPO IV                  *****/
   /***********************************************************************/
   INSERT INTO FCO_FPO_MM VALUES (999940, '999994');
   COMMIT WORK;
END;
/
CREATE PUBLIC SYNONYM sp_fco_crea_pension_mm FOR ADAM.sp_fco_crea_pension_mm;
GRANT EXECUTE ON sp_fco_crea_pension_mm TO adama;

