CREATE OR REPLACE PROCEDURE sp_fco_crea_salud_mm
(
   pch_compania    char,
   pch_anio	   char,
   pch_mes         char,
   pch_sucursal    char,
   pch_EPS         char
)
/**************************************************************************/
/*** Modulo:      Modulo Fiscal Colombiano ADAM V.3			***/
/*** Descripcion: Genera el Medio Magnetico de acuerdo con las 		***/
/***		  especificaciones SIGA - Formato 03			***/
/*** Tablas:	  Administradoras, MF_EPS_CIA_año_mes, trabajadores_gra ***/
/*** Vistas:	  vw_FCO_ADMINISTRADORAS, vw_companias                	***/ 
/*** Autor:	  Juan Carlos Leguizamon				***/
/*** Fecha:	  Julio 3 de 2001 					***/
/*** Revision: 	  Junio 11 de 2002					***/
/*** Motor:	  Oracle 8i        	Version interna: 1.6		***/
/**************************************************************************/

AS
    vch_CI01_06    char(58);
    vch_CI07_18    char(148);
    vch_CI19_29    char(102);
    vch_CI30       char(5);
    vnm_R31C3      number(10,0);
    vnm_R31C4      number(10,0);
    vnm_R31C5      number(10,0);
    vnm_R31C6      number(10,0);
    vnm_R31C7      number(10,0);
    vnm_R32C4      number(10,0);
    vnm_R33C4      number(10,0);
    vnm_R35C4      number(10,0);
    vnm_R35C7      number(10,0);
    vnm_R36C4      number(10,0);
    vnm_R36C7      number(10,0);
    vnm_R37C4      number(10,0);
    vnm_R37C7      number(10,0);
    vnm_R38C4      number(10,0);
    vnm_R38C7      number(10,0);
    vch_Ren31      char(56);   
    vch_Ren32      char(56);  
    vch_Ren33      char(56);  
    vch_Ren35      char(56);  
    vch_Ren36      char(56);  
    vch_Ren37      char(56);  
    vch_Ren38      char(56);  
    vch_Ren39      char(56);
                    
    vvc_sql        varchar2(3000);

    vin_id_cursor  integer;
    vin_rows	   integer;

    vnm_total      number(5,0);
                    
    /* Para hallar el periodo cotizado a salud (ste) */
    vch_per_ste    char(6);
    vnm_mes_ste    number(2,0);
                    
    /* 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);

    /* Variables para descontar incapacidades, saldos a favor y pagar intereses por mora */
    vch_num_aut_eg    char(10);
    vnm_val_aut_eg    number(10,0);
    vch_num_aut_lm    char(10);
    vnm_val_aut_lm    number(10,0);
    vch_num_meses     char(10);
    vnm_mor_sa        number(10,0);
    vch_num_rad_saf   char(10);
    vnm_val_saf_sa    number(10,0);

BEGIN

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

    DELETE FCO_EPS_MM;
    COMMIT;

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

    SELECT '00000103' || 
	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='EPS' AND RTRIM(codigo)=pch_EPS;

    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 Length(RTrim(pch_sucursal)) = 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;

    /* Calculo del periodo cotizado a Salud */
    vnm_mes_ste := To_Number(pch_mes) + 1;
    IF vnm_mes_ste = 13 THEN
        vch_per_ste := RTrim(LTrim(To_Number(pch_anio) + 1)) || '01';
    ELSE
        vch_per_ste := pch_anio || LPad(To_Char(vnm_mes_ste),2,'0');
    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_29 :=
	vch_per_ste || RPad(' ',80,' ') || vch_forma_pres || vch_sucursal ||
	LPad(RTrim(To_Char(vnm_total)), 5, '0');

   /* Numero de Afiliados a esta EPS */
    vnm_total := 0;
    SELECT   
        ' SELECT Count(*)' ||
        ' FROM ' || vvc_tabla ||
        ' WHERE RTrim(COD_EPS) = ''' || pch_EPS || '''' || 
        Decode(RTrim(vvc_control_suc),'NA','',' AND RTrim(COD_SUC)=''' || RTrim(pch_sucursal) || '''') ||
	' AND C24A > 0'
    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_CI30 := LPad(RTrim(To_Char(vnm_total)), 5, '0');

    INSERT INTO FCO_EPS_MM VALUES (0, vch_CI01_06 || vch_CI07_18 || vch_CI19_29 || vch_CI30);
    COMMIT;

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

    SELECT 
        ' INSERT INTO FCO_EPS_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) ||' ||
            ' LPad(To_Char(Floor(Nvl(C14A,0))), 2, ''0'') ||' ||
            ' LPad(To_Char(Floor(Nvl(C15A,0))), 9, ''0'') ||' ||
            ' LPad(To_Char(Floor(Nvl(C16A,0))), 9, ''0'') ||' ||
            ' LPad(To_Char(Floor(Nvl(C17A,0))), 9, ''0'') ||' ||
            ' LPad(To_Char(Floor(Nvl(C24A,0))), 9, ''0'') ||' ||
            ' RPad(SubStr(C25A,1,9)||'' '', 9, '' '') ||' ||
            ' LPad(To_Char(Floor(Nvl(C25B,0))), 9, ''0'') ||' ||
            ' RPad(SubStr(C26A,1,9)||'' '', 9, '' '') ||' ||
            ' LPad(To_Char(Floor(Nvl(C26B,0))), 9, ''0'') ||' ||
            ' LPad(To_Char(Floor(Nvl(C27A,0))), 9, ''0'')' ||
        ' FROM '||vvc_tabla||
        ' WHERE CIA=' || '''' || pch_compania || '''' ||
        ' AND RTrim(COD_EPS)=' ||''''||RTrim(pch_EPS)||'''' ||
        Decode(RTrim(vvc_control_suc),'NA','',' AND RTrim(COD_SUC)=''' || RTrim(pch_sucursal) || ''')') ||
        ' AND C24A >0'
    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_EPS_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(C17A)),0), Nvl(Floor(Sum(C24A)),0),' ||
                ' Nvl(Floor(Sum(C27A)),0)' ||
        ' FROM '||vvc_tabla||
        ' WHERE CIA = '''|| pch_compania || '''' ||
        '   AND RTRIM(COD_EPS)='''|| pch_EPS || '''' ||
        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_R31C3);
        dbms_sql.define_column(vin_id_cursor, 2, vnm_R31C4);
        dbms_sql.define_column(vin_id_cursor, 3, vnm_R31C7);
        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_R31C3);
            dbms_sql.column_value(vin_id_cursor, 2, vnm_R31C4);
            dbms_sql.column_value(vin_id_cursor, 3, vnm_R31C7);
        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 32	******/
    vch_num_aut_eg := ' ';
    vnm_val_aut_eg := 0;

    IF vvc_control_suc = 'NA' THEN
        BEGIN
            SELECT Nvl(A.referencia, ' '), Nvl(A.valor_1,0)
            INTO vch_num_aut_eg, vnm_val_aut_eg
            FROM fco_ala_datos_adi A, fco_rel_sgss_da B
            WHERE A.sgss = B.sgss
              AND A.item = B.ala_datos_adi
              AND RTrim(A.tabla) = RTrim(vvc_tabla)
              AND RTrim(A.cod_adm) = RTrim(pch_EPS)
              AND b.secuencia = 1;
        EXCEPTION
            WHEN OTHERS THEN vnm_val_aut_eg := 0;
        END;
    ELSE
        BEGIN
            SELECT Nvl(A.referencia, ' '), Nvl(A.valor_1,0)
            INTO vch_num_aut_eg, vnm_val_aut_eg
            FROM fco_ala_datos_adi A, fco_rel_sgss_da B
            WHERE A.sgss = B.sgss 
              AND A.item = B.ala_datos_adi
              AND RTrim(A.tabla) = RTrim(vvc_tabla)
              AND RTrim(A.cod_adm) = RTrim(pch_EPS)
              AND b.secuencia = 1
              AND RTrim(cod_suc) = RTrim(pch_sucursal);
        EXCEPTION
            WHEN OTHERS THEN vnm_val_aut_eg := 0;
        END;
    END IF;

    vnm_R31C5 := vnm_val_aut_eg;
    vnm_R32C4 := vnm_R31C5;

    vch_Ren32 := '000323' || vch_num_aut_eg || LPad(To_Char(vnm_R32C4),10,'0') ||
        LPad('0',10,'0') || LPad('0',10,'0') || LPad('0',10,'0');

    INSERT INTO FCO_EPS_MM VALUES (999932, vch_Ren32);
    COMMIT;

    /*******	RENGLON 33	******/
    vch_num_aut_lm := ' ';
    vnm_val_aut_lm := 0;

    IF vvc_control_suc = 'NA' THEN
        BEGIN
            SELECT Nvl(A.referencia, ' '), Nvl(A.valor_1,0)
            INTO vch_num_aut_lm, vnm_val_aut_lm
            FROM fco_ala_datos_adi A, fco_rel_sgss_da B
            WHERE A.sgss = B.sgss 
              AND A.item = B.ala_datos_adi
              AND RTrim(A.tabla) = RTrim(vvc_tabla)
              AND RTrim(A.cod_adm) = RTrim(pch_EPS)
              AND b.secuencia = 2;
        EXCEPTION
            WHEN OTHERS THEN vnm_val_aut_lm := 0;
        END;
    ELSE
        BEGIN
            SELECT Nvl(A.referencia, ' '), Nvl(A.valor_1,0)
            INTO vch_num_aut_lm, vnm_val_aut_lm
            FROM fco_ala_datos_adi A, fco_rel_sgss_da B
            WHERE A.sgss = B.sgss 
              AND A.item = B.ala_datos_adi
              AND RTrim(A.tabla) = RTrim(vvc_tabla)
              AND RTrim(A.cod_adm) = RTrim(pch_EPS)
              AND b.secuencia = 2
              AND RTrim(cod_suc) = RTrim(pch_sucursal);
        EXCEPTION
            WHEN OTHERS THEN vnm_val_aut_lm := 0;
        END;
    END IF;

    vnm_R31C6 := vnm_val_aut_lm;
    vnm_R33C4 := vnm_R31C6;

    vch_Ren33 := '000333' || vch_num_aut_lm || LPad(To_Char(vnm_R33C4),10,'0') ||
        LPad('0',10,'0') || LPad('0',10,'0') || LPad('0',10,'0');

    INSERT INTO FCO_EPS_MM VALUES (999933, vch_Ren33);
    COMMIT;


   /*******	RENGLON 31	******/
    vch_Ren31 := '000313' || 
        LPad(To_Char(vnm_R31C3), 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');

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


    /*******	RENGLON 35	******/
    vnm_R35C4 := vnm_R31C4 - vnm_R32C4 - vnm_R33C4;
    vnm_R35C7 := vnm_R31C7;
    vch_Ren35 := '000353' || LPad('0',10,'0') ||
        LPad(To_Char(vnm_R35C4),10,'0') ||
	LPad('0',10,'0') || LPad('0',10,'0') ||
        LPad(To_Char(vnm_R35C7),10,'0');

    INSERT INTO FCO_EPS_MM VALUES (999935, vch_Ren35);
    COMMIT;

    /*******	RENGLON 36	******/
    vch_num_meses := LPad('0',10,'0');
    vnm_mor_sa := 0;
 
    IF vvc_control_suc = 'NA' THEN
        BEGIN
            SELECT Nvl(A.referencia, ' '), Nvl(A.valor_1, 0)
            INTO vch_num_meses, vnm_mor_sa
            FROM fco_ala_datos_adi A, fco_rel_sgss_da B
            WHERE A.sgss = B.sgss 
              AND A.item = B.ala_datos_adi
              AND RTrim(A.tabla) = RTrim(vvc_tabla)
              AND RTrim(A.cod_adm) = RTrim(pch_EPS)
              AND b.secuencia = 3;
        EXCEPTION
            WHEN OTHERS THEN vch_num_meses := LPad('0',10,'0');
        END;
    ELSE
        BEGIN
            SELECT Nvl(A.referencia, ' '), Nvl(A.valor_1, 0)
            INTO vch_num_meses, vnm_mor_sa
            FROM fco_ala_datos_adi A, fco_rel_sgss_da B
            WHERE A.sgss = B.sgss 
              AND A.item = B.ala_datos_adi
              AND RTrim(A.tabla) = RTrim(vvc_tabla)
              AND RTrim(A.cod_adm) = RTrim(pch_EPS)
              AND b.secuencia = 3
              AND RTrim(cod_suc) = RTrim(pch_sucursal);
        EXCEPTION
            WHEN OTHERS THEN vch_num_meses := LPad('0',10,'0');
        END;
    END IF;

    vnm_R36C4 := vnm_mor_sa;
    vnm_R36C7 := 0;
    vch_Ren36 := '000363' || vch_num_meses ||
        LPad(To_Char(vnm_R36C4),10,'0') ||
	LPad('0',10,'0') || LPad('0',10,'0') ||
        LPad(To_Char(vnm_R36C7),10,'0');

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

    /*******	RENGLON 37	******/
    vnm_R37C4 := vnm_R35C4 + vnm_R36C4;
    vnm_R37C7 := vnm_R35C7 + vnm_R36C7;

    vch_Ren37 := '000373' || LPad('0',10,'0') ||
        LPad(To_Char(vnm_R37C4),10,'0') ||
	LPad('0',10,'0') || LPad('0',10,'0') ||
        LPad(To_Char(vnm_R37C7),10,'0');

    INSERT INTO FCO_EPS_MM VALUES (999937, vch_Ren37);
    COMMIT;

    /*******	RENGLON 38	******/
    vch_num_rad_saf := ' ';
    vnm_val_saf_sa := 0;

    IF vvc_control_suc = 'NA' THEN
        BEGIN
            SELECT Nvl(A.referencia, ' '), Nvl(A.valor_1,0)
            INTO vch_num_rad_saf, vnm_val_saf_sa
            FROM fco_ala_datos_adi A, fco_rel_sgss_da B
            WHERE A.sgss = B.sgss 
              AND A.item = B.ala_datos_adi
              AND RTrim(A.tabla) = RTrim(vvc_tabla)
              AND RTrim(A.cod_adm) = RTrim(pch_EPS)
              AND b.secuencia = 4;
        EXCEPTION
            WHEN OTHERS THEN vnm_val_saf_sa := 0;
        END;
    ELSE
        BEGIN
            SELECT Nvl(A.referencia, ' '), Nvl(A.valor_1,0)
            INTO vch_num_rad_saf, vnm_val_saf_sa
            FROM fco_ala_datos_adi A, fco_rel_sgss_da B
            WHERE A.sgss = B.sgss 
              AND A.item = B.ala_datos_adi
              AND RTrim(A.tabla) = RTrim(vvc_tabla)
              AND RTrim(A.cod_adm) = RTrim(pch_EPS)
              AND b.secuencia = 4
              AND RTrim(cod_suc) = RTrim(pch_sucursal);
        EXCEPTION
            WHEN OTHERS THEN vnm_val_saf_sa := 0;
        END;
    END IF;

    vnm_R38C4 := vnm_val_saf_sa;
    vnm_R38C7 := 0;

    vch_Ren38 := '000383' || vch_num_rad_saf ||
        LPad(To_Char(vnm_R38C4),10,'0') ||
	LPad('0',10,'0') || LPad('0',10,'0') ||
        LPad(To_Char(vnm_R38C7),10,'0');

    INSERT INTO FCO_EPS_MM VALUES (999938, vch_Ren38);
    COMMIT;

    /*******	RENGLON 39	******/
    vch_Ren39 := '000393' || LPad('0',10,'0') ||
        LPad(To_Char(vnm_R37C4-vnm_R38C4),10,'0') ||
	LPad('0',10,'0') || LPad('0',10,'0') ||
        LPad(To_Char(vnm_R37C7-vnm_R38C7),10,'0');

    INSERT INTO FCO_EPS_MM VALUES (999939, vch_Ren39);
    COMMIT WORK;

END;
/
CREATE PUBLIC SYNONYM sp_fco_crea_salud_mm FOR ADAM.sp_fco_crea_salud_mm;
GRANT EXECUTE ON sp_fco_crea_salud_mm TO adama;
