CREATE OR REPLACE PROCEDURE sp_fco_crea_autolx_mm
(
    pch_compania    char,
    pch_anio        char,
    pch_mes         char,
    pch_sucursal    char
)
/**********************************************************************************/
/*** Modulo:      Modulo Fiscal Colombiano ADAM V.3				***/
/*** Descripcion: Genera el Medio Magnetico de acuerdo con las especificaciones ***/
/***		  GALA - Consolidado - Autol					***/
/*** Tablas:	  Administradoras, VW_ISS_CIA_año_mes				***/
/*** Vistas:	  vw_companias                                                  ***/ 
/*** Autor:	  Juan Carlos Leguizamon					***/
/*** Fecha:	  Julio 10 de 2001 						***/
/*** Revision: 	  Febrero 25 de 2002						***/
/*** Motor:	  Oracle 8i                Version interna: 1.00		***/
/**********************************************************************************/

AS
    vch_T3C1_34    char(226);
    vch_T3C35	   char(6);
    vch_T3C36	   char(6);
    vch_T3C37	   char(6);
    vnm_T35C18     number(14,0);
    vnm_T35C19     number(14,0);
    vnm_T35C20     number(14,0);
    vnm_T35C21     number(14,0);

    vvc_sql        varchar2(3000);
    vvc_sql2	   varchar2(3000);
    vin_id_cursor  integer;
    vin_rows	   integer;
                    
    vch_nit	   char(12);
    vch_periodo    char(6);
    vch_forma_pres char(1);
    vch_cod_ISS    char(3);
    vch_sucursal   char(3);
    vch_suc_001    char(3);
    vch_nds        char(15);
    vvc_tabla      varchar2(30);
    vnm_total      number(5,0);
BEGIN

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

    /* Codigo del ISS en la compania -Cambiar si es necesario - */
    vch_cod_ISS := 'ISS';

    DELETE FCO_ISS_MM;
    COMMIT;

    /* Control del codigo de la sucursal */
    IF Nvl(RTrim(pch_sucursal)||' ', ' ') = ' ' THEN
      vch_sucursal := 'NA';
    ELSE
      vch_sucursal := SubStr(LTrim(RTrim(pch_sucursal)),1,3);
    END IF;

    /* Nit y Digito de Verificacion */
    SELECT
        LPad(LTrim(RTrim(fn_fco_tratar_NIT(pch_compania))), 11, '0') ||
        SubStr(registro_fiscal, Length(RTrim(registro_fiscal)),1)
    INTO vch_nit
    FROM vw_fco_companias
    WHERE compania = pch_compania;
   
    /* Periodo de Cotizacion (Ciclo de Liquidacion) */
    vch_periodo := RTrim(pch_anio) || LPad(Rtrim(pch_mes), 2, '0');

    /* Forma de Presentación de la Autoliquidación de Aportes */
    vch_forma_pres := 'C'; -- Forma por defecto
    IF RTrim(vch_sucursal) <> 'NA' THEN
        vch_forma_pres := 'S';

        SELECT 
            SubStr(C.nombre_cia,1,35) || 
            vch_periodo || '01' ||
            RPad(SubStr(SUC.direccion,1,45), 45, ' ') || 
    	    LPad(RTrim(SubStr(SUC.cod_ciudad,1,3)),3,'0') || 
	    LPad(RTrim(SubStr(SUC.cod_dpto,1,2)),2,'0') || 
	    LPad(RTrim(SubStr(SUC.telefono,1,7)),7,'0') || 
            LPad('0',14,'0') || '0' || '1' || 
	    LPad('0',11,'0') || '000000', LPad(RTrim(SUC.codigo_iss), 3, '0')
        INTO
            vch_T3C1_34, vch_suc_001
        FROM
            vw_fco_companias C,
            fco_sucursales SUC
        WHERE  	
            SUC.compania = C.compania And
            C.compania = pch_compania And
            RTrim(SUC.sucursal) = RTrim(pch_sucursal);

    ELSE
	SELECT Nvl(forma_presentacion, 'U')
        INTO vch_forma_pres
	FROM  vw_fco_companias
	WHERE compania = pch_compania;

        SELECT 
            RPad(SubStr(nombre_cia,1,35), 35, ' ') || 
            vch_periodo || '01' ||
            RPad(SubStr(domicilio,1,45), 45, ' ') || 
    	    LPad(RTrim(SubStr(cod_ciudad,1,3)),3,'0') || 
	    LPad(RTrim(SubStr(cod_dpto,1,2)),2,'0') || 
	    LPad(RTrim(SubStr(telefono,1,7)),7,'0') || 
            LPad('0',14,'0') || '0' || '1' || 
	    LPad('0',11,'0') || '000000'
        INTO
            vch_T3C1_34
        FROM vw_fco_companias
        WHERE compania = pch_compania;

        vch_suc_001 := '001';
    END IF;

    vch_nds := vch_nit || vch_suc_001;

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

    INSERT INTO FCO_ISS_MM VALUES (16, '0005000' ||
	'1' || LPad('0',16,'0') || vch_nds ||
	LPad('0',15,'0') || LPad('0',173,'0') || RPad(' ',99,' '));
    COMMIT;

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

    INSERT INTO FCO_ISS_MM VALUES (17, '0005000' ||
	'2' || '0000' || '000001' || '000' || '000' || 
	vch_nds || LPad('0',15,'0') || LPad('0',36,'0') || 
        '001' || '001' || LPad('0',14,'0') ||
	'N' || LPad('0',150,'0') || RPad(' ',65,' '));
    COMMIT;

    /***********************************************************************/
    /*****          CONSTRUCCION DEL REGISTRO TIPO 3.0                 *****/
    /***********************************************************************/

    vch_T3C1_34 := '0005000' || '3' || '0000' || '000001' || '000' || '001' ||
            vch_nds || vch_periodo || '01' || '0' || '000000' || 
            LPad('0', 23,'0') || 'N' || vch_nds || RTrim(vch_T3C1_34);

    /* Numero de Afiliados al ISS en Pensiones */
    vnm_total := 0;
    SELECT   
        ' SELECT count(*)' ||
        ' FROM ' || vvc_tabla ||
        ' WHERE COD_FPO = ''' || vch_cod_ISS || '''' ||
        Decode(RTrim(vch_sucursal),'NA','',' AND RTrim(COD_SUC)=''' || RTrim(pch_sucursal) || '''') ||
        ' AND C14A > 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 en pensiones');
    END;

    vch_T3C35 := LPad(RTrim(To_Char(vnm_total)), 6, '0');

    /* Numero de Afiliados al ISS en Salud */
    vnm_total := 0;
    SELECT   
        ' SELECT count(*)' ||
        ' FROM ' || vvc_tabla ||
        ' WHERE COD_EPS = ''' || vch_cod_ISS || '''' ||
        Decode(RTrim(vch_sucursal),'NA','',' AND RTrim(COD_SUC)=''' || RTrim(pch_sucursal) || '''') ||
        ' AND C14C > 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 en pensiones');
    END;

    vch_T3C36 := LPad(RTrim(To_Char(vnm_total)), 6, '0');


   /* Numero de Renglones diligenciados */
    vnm_total := 0;
    SELECT   
        ' SELECT count(*)' ||
        ' FROM ' || vvc_tabla ||
        ' WHERE (C14A + C14C) > 0' ||
        Decode(RTrim(vch_sucursal),'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 afiliados en pensiones');
    END;

    vch_T3C37 := LPad(RTrim(To_Char(vnm_total)), 6, '0');


    INSERT INTO FCO_ISS_MM VALUES (18, vch_T3C1_34 || vch_T3C35 || vch_T3C36 || vch_T3C37 || 
	'22N' || RPad(' ',40,' ') || RPad(' ',38,' ') || ' ');
    COMMIT;

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

    SELECT
    ' INSERT INTO FCO_ISS_MM' ||
    ' SELECT 19 + RowNum,' ||
	'''000500030000000001000001'' || ''' || 
	vch_nds || vch_periodo || '013'' ||' ||
	'LPad(RowNum, 6, ''0'') ||' ||
	'LPad(''0'', 15, ''0'') ||' ||
	'RPad(RTrim(SubStr(C10A,1,1)) || '' '',1,'' '') ||' ||
   	'LPad(RTrim(SubStr(C10B,1,11)), 11, ''0'') ||' ||
   	'''0'' ||' ||
	'RPad(fn_Nombre_ISS(C10C), 25, '' '') ||' ||
   	'Nvl(Decode(Length(RTrim(C11A)),0,'' '',C11A),'' '') ||' ||
   	'Nvl(Decode(Length(RTrim(C11B)),0,'' '',C11B),'' '') ||' ||
   	'Nvl(Decode(Length(RTrim(C11C)),0,'' '',C11C),'' '') ||' ||
   	'LPad(To_Char(Floor(Nvl(C11D,0))), 2, ''0'') ||' ||
	'LPad(To_Char(Floor(Nvl(C11E,0))), 2, ''0'') ||' ||
	'LPad(To_Char(Floor(Nvl(C12A,0))), 2, ''0'') ||' ||
	'LPad(To_Char(Floor(Nvl(C13A,0))), 12, ''0'') ||' ||
	'LPad(To_Char(Floor(Nvl(C14A,0))), 10, ''0'') ||' ||
	'LPad(To_Char(Floor(Nvl(C14B,0))), 10, ''0'') ||' ||
	'LPad(To_Char(Floor(Nvl(C14C,0))), 10, ''0'') ||' ||
	'RPad('' '',168,'' '') ' ||
    ' FROM ' || vvc_tabla ||
    ' WHERE CIA=' || '''' || pch_compania || '''' ||
    Decode(RTrim(vch_sucursal),'NA','',' AND RTrim(COD_SUC)='''||RTrim(pch_sucursal)||'''') ||
    ' AND (C14A + C14C) > 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 registros 3.3');
    END;
    COMMIT;

    /***********************************************************************/
    /*****          CONSTRUCCION DEL REGISTRO TIPO 3.5                 *****/
    /***********************************************************************/
    /* Totales para los campos 18,19,20 y 21 */
    SELECT
	' SELECT  Nvl(Floor(Sum(C13A)),0), Nvl(Floor(Sum(C14A)),0),' ||
		' Nvl(Floor(Sum(C14B)),0), Nvl(Floor(Sum(C14C)),0) ' ||
	' FROM ' || vvc_tabla ||
	' WHERE CIA='||''''||pch_compania||'''' ||
        Decode(RTrim(vch_sucursal),'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_T35C18);
        dbms_sql.define_column(vin_id_cursor, 2, vnm_T35C19);
        dbms_sql.define_column(vin_id_cursor, 3, vnm_T35C20);
        dbms_sql.define_column(vin_id_cursor, 4, vnm_T35C21);
        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_T35C18);
	    dbms_sql.column_value(vin_id_cursor, 2, vnm_T35C19);
	    dbms_sql.column_value(vin_id_cursor, 3, vnm_T35C20);
            dbms_sql.column_value(vin_id_cursor, 4, vnm_T35C21);
        END IF;
        dbms_sql.close_cursor(vin_id_cursor);
        EXCEPTION
            WHEN OTHERS THEN
            Raise_Application_Error (-20001, 'Error al calcular los totales para los campos 18, 19, 20 y 21');
    END ;
    COMMIT;

    vnm_T35C18 := Nvl(vnm_T35C18,0);
    vnm_T35C19 := Nvl(vnm_T35C19,0);
    vnm_T35C20 := Nvl(vnm_T35C20,0);
    vnm_T35C21 := Nvl(vnm_T35C21,0);
     
    INSERT INTO FCO_ISS_MM VALUES (999920, 
	'0005000' || '3' || '0000' || '000001' || '000' || '001' ||
	vch_nds || vch_periodo || '01' || '5' || '000000' || 
        LPad('0',14,'0') || '0' ||
	LPad(To_Char(vnm_T35C18),12,'0') || 
	LPad(To_Char(vnm_T35C19),10,'0') || 
	LPad(To_Char(vnm_T35C20),10,'0') || 
	LPad(To_Char(vnm_T35C21),10,'0') || 
	RPad(' ',21,' ') || RPad(' ',21,' ') ||
	LPad('0',12,'0') || LPad('0',10,'0') || LPad('0',10,'0') || LPad('0',10,'0') ||
	RPad(' ',131,' '));
    COMMIT;

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

    INSERT INTO FCO_ISS_MM VALUES (999921, 
	'0005000' || '7' || '0000' || '000001' || '000' || '000' ||
	vch_nds || LPad('0',15,'0') || 
	'000001' || vch_T3C37 ||
	LPad(To_Char(vnm_T35C18),14,'0') || 
	LPad(To_Char(vnm_T35C19),14,'0') || 
	LPad(To_Char(vnm_T35C20),14,'0') || 
	LPad(To_Char(vnm_T35C21),14,'0') || 
	LPad('0',98,'0') || LPad('0',14,'0') ||
	LPad(To_Char(Floor(vnm_T35C21/12)),14,0) || 
	RPad(' ',78,' '));
    COMMIT;

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


    INSERT INTO FCO_ISS_MM VALUES (999922, 
	'0005000' || '8' || '0000' || '000000' || '000' || '000' ||
	vch_nds || LPad('0',15,'0') || 
	'000001' || vch_T3C37 ||
	LPad(To_Char(vnm_T35C18),14,'0') || 
	LPad(To_Char(vnm_T35C19),14,'0') || 
	LPad(To_Char(vnm_T35C20),14,'0') || 
	LPad(To_Char(vnm_T35C21),14,'0') || 
	LPad('0',98,'0') || LPad('0',14,'0') ||
	LPad(To_Char(Floor(vnm_T35C21/12)),14,0) || 
	RPad(' ',78,' '));

    COMMIT WORK;
END;
/
CREATE PUBLIC SYNONYM sp_fco_crea_autolx_mm FOR ADAM.sp_fco_crea_autolx_mm;
GRANT EXECUTE ON sp_fco_crea_autolx_mm TO adama;
