CREATE OR REPLACE PROCEDURE sp_fco_crea_riesgos_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 SIGA - Formato 02			***/
/*** Tablas:	  Administradoras, MF_ARP_CIA_año_mes, trabajadores_gra	***/
/*** Vistas:	  vw_entidades_admin, vw_companias                      ***/ 
/*** Autor:	  Juan Carlos Leguizamon				***/
/*** Fecha:	  Julio 10 de 2001 					***/
/*** Revision: 	  Marzo 5 de 2002					***/
/*** Motor:	  Oracle 8i        	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_Ren_31     char(26);
    vch_Ren_32     char(26);
    vch_Ren_34     char(26);
    vch_Ren_35     char(26);
    vch_Ren_36     char(26);
    vch_Ren_37     char(26);
    vch_Ren_38     char(26);
    vch_Ren_39     char(26);

    vnm_C4R31      number(10,0);
    vnm_C4R32      number(10,0);
    vnm_C4R35      number(10,0);
    vnm_C4R37      number(10,0);
    vnm_C4R39      number(10,0);

    /* VARIABLES AUXILIARES */
    vvc_sql        varchar2(3000);

    /* Para controlar puntos y rayas en el registro_fiscal */
    vch_nit        char(9);
    /* Forma de Presentacion de la Autoliquidacion para ARP */
    vch_forma_pres char(1);
    /* Sucursal */
    vch_sucursal   char(10);
    vvc_control_suc varchar(10);

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

    /* Variables para descontar incapacidades, saldos a favor y pagar intereses por mora */
    vch_nro_aut_in  char(10);
    vnm_vlr_aut_in  number(10,0);
    vnm_vlr_otr_rp  number(10,0);
    vch_nro_meses   char(10);
    vnm_vlr_mor_rp  number(10,0);
    vch_nro_rad_saf char(10);
    vnm_vlr_saf_rp  number(10,0);

    vin_id_cursor  integer;
    vin_rows	   integer;

    vnm_total      number(5,0);

BEGIN

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

    DELETE FCO_ARP_MM;
    COMMIT;

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

    SELECT '00000102' || 
        RPad(Substr(nombre_ARP,1,40),40,' ') || 
        LPad(RTrim(Substr(nit_ARP,1,9)),9,'0') ||
        Substr(nit_ARP,11,1)
    INTO vch_CI01_06
    FROM vw_fco_companias
    WHERE compania = pch_compania;

    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');

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

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

    SELECT
    ' INSERT INTO FCO_ARP_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(C13K,0))),2,''0'') ||' ||
        ' LPad(To_Char(Floor(Nvl(C14A,0))),2,''0'') ||' ||
        ' LPad(To_Char(Floor(Nvl(C15A,0))),9,''0'') ||' ||
        ' ''000000000'' ||' ||
        ' LPad(To_Char(Floor(Nvl(C17A,0))),9,''0'') ||' ||
        ' ''0'' || RPAD(REPLACE(LTRIM(TO_CHAR(C28A/1000,''99.999'')),''.'',''''),8,''0'') ||' ||
        ' LPad(RTrim(SubStr(C28B,1,9)),9,''0'') ||' ||
        ' LPad(To_Char(Floor(Nvl(C29A,0))),9,''0'') ||' ||
        ' RPad(SubStr(C30A,1,5),5,'' '') ||' ||
        ' RPad(SubStr(C30B,1,5),5,'' '')' ||
    ' FROM '||vvc_tabla||
    ' WHERE CIA=' || '''' || pch_compania || '''' ||
    Decode(RTrim(vvc_control_suc),'NA','',' AND RTrim(COD_SUC)=''' || RTrim(pch_sucursal) || ''')') ||
    ' AND (C29A >0 OR C13F = ''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_ARP_MM con los registros Tipo 2');
    END;

    COMMIT;


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

    /* Totales para los campos 4 de los renglones 31 y 32*/
    SELECT
        ' SELECT Nvl(Floor(Sum(C29A)),0), Nvl(Floor(Sum(C32B)),0)' ||
        ' FROM '||vvc_tabla||
        ' WHERE CIA=''' || pch_compania || '''' ||
        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_C4R31);
        dbms_sql.define_column(vin_id_cursor, 2, vnm_C4R32);
        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_C4R31);
            dbms_sql.column_value(vin_id_cursor, 2, vnm_C4R32);
        END IF;
        dbms_sql.close_cursor(vin_id_cursor);
        EXCEPTION
            WHEN OTHERS THEN
                raise_application_error (-20001, '20001-Error al calcular los totales para el renglon 31 y 32');
    END;

    /*******	RENGLON 31	******/
    vch_Ren_31 := '000313' || LPad('0',10,'0') || LPad(To_Char(vnm_C4R31),10,'0');
    INSERT INTO FCO_ARP_MM VALUES (999931, vch_Ren_31);
    COMMIT;

    /*******	RENGLON 32	******/
    vch_nro_aut_in := ' ';
    vnm_vlr_aut_in := 0;

    IF vnm_C4R32 <= 0 THEN
        IF vvc_control_suc = 'NA' THEN
            BEGIN
                SELECT Nvl(A.referencia, ' '), Nvl(A.valor_1,0)
                INTO vch_nro_aut_in, vnm_vlr_aut_in
                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) = 'ARP'
                  AND b.secuencia = 1;
            EXCEPTION
                WHEN OTHERS THEN vnm_vlr_aut_in := 0;
            END;
        ELSE
            BEGIN
                SELECT Nvl(A.referencia, ' '), Nvl(A.valor_1,0)
                INTO vch_nro_aut_in, vnm_vlr_aut_in
                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) = 'ARP'
                  AND b.secuencia = 1
                  AND RTrim(cod_suc) = RTrim(pch_sucursal);
            EXCEPTION
                WHEN OTHERS THEN vnm_vlr_aut_in := 0;
            END;
        END IF;

        vnm_C4R32 := vnm_vlr_aut_in;
    END IF;

    vch_Ren_32 := '000323' || vch_nro_aut_in || LPad(To_Char(vnm_C4R32),10,'0');
    INSERT INTO FCO_ARP_MM VALUES (999932,vch_Ren_32);
    COMMIT;

    /*******	RENGLON 34	******/
    vnm_vlr_otr_rp := 0;

    IF vvc_control_suc = 'NA' THEN
        BEGIN
            SELECT Nvl(A.valor_1,0)
            INTO vnm_vlr_otr_rp
            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) = 'ARP'
              AND b.secuencia = 2;
        EXCEPTION
            WHEN OTHERS THEN vnm_vlr_otr_rp := 0;
        END;
    ELSE
        BEGIN
            SELECT Nvl(A.valor_1,0)
            INTO vnm_vlr_otr_rp
            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) = 'ARP'
              AND b.secuencia = 2
              AND RTrim(cod_suc) = RTrim(pch_sucursal);
        EXCEPTION
            WHEN OTHERS THEN vnm_vlr_otr_rp := 0;
        END;
    END IF;

    vch_Ren_34 := '000343' || LPad('0',10,'0') || LPad(To_Char(vnm_vlr_otr_rp),10,'0');
    INSERT INTO FCO_ARP_MM VALUES (999934,vch_Ren_34);
    COMMIT;

    /*******	RENGLON 35	******/
    vnm_C4R35 := vnm_C4R31 - vnm_C4R32 - vnm_vlr_otr_rp;

    vch_Ren_35 := '000353' || LPad('0',10,'0') || LPad(To_Char(vnm_C4R35),10,'0');
    INSERT INTO FCO_ARP_MM VALUES (999935,vch_Ren_35);
    COMMIT;
 
    /*******	RENGLON 36	******/
    vch_nro_meses := ' ';
    vnm_vlr_mor_rp := 0;

    IF vvc_control_suc = 'NA' THEN
        BEGIN
            SELECT Nvl(A.referencia, ' '), Nvl(A.valor_1,0)
            INTO vch_nro_meses, vnm_vlr_mor_rp
            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) = 'ARP'
              AND b.secuencia = 3;
        EXCEPTION
            WHEN OTHERS THEN vnm_vlr_aut_in := 0;
        END;
    ELSE
        BEGIN
            SELECT Nvl(A.referencia, ' '), Nvl(A.valor_1,0)
            INTO vch_nro_meses, vnm_vlr_mor_rp
            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) = 'ARP'
              AND b.secuencia = 3
              AND RTrim(cod_suc) = RTrim(pch_sucursal);
        EXCEPTION
            WHEN OTHERS THEN vnm_vlr_mor_rp := 0;
        END;
    END IF;
    vch_Ren_36 := '000363' || LPad('0',10,'0') || LPad(To_Char(vnm_vlr_mor_rp),10,'0');
    INSERT INTO FCO_ARP_MM VALUES (999936,vch_Ren_36);
    COMMIT;

    /*******	RENGLON 37	******/
    vnm_C4R37 := vnm_C4R35 + vnm_vlr_mor_rp;

    vch_Ren_37 := '000373' || LPad('0'||RTrim(vch_nro_meses),10,'0') || LPad(To_Char(vnm_C4R37),10,'0');
    INSERT INTO FCO_ARP_MM VALUES (999937,vch_Ren_37);
    COMMIT;

    /*******	RENGLON 38	******/
    vch_nro_rad_saf := ' ';
    vnm_vlr_saf_rp := 0;

    IF vvc_control_suc = 'NA' THEN
        BEGIN
            SELECT Nvl(A.referencia, ' '), Nvl(A.valor_1,0)
            INTO vch_nro_rad_saf, vnm_vlr_saf_rp
            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) = 'ARP'
              AND b.secuencia = 4;
        EXCEPTION
            WHEN OTHERS THEN vnm_vlr_aut_in := 0;
        END;
    ELSE
        BEGIN
            SELECT Nvl(A.referencia, ' '), Nvl(A.valor_1,0)
            INTO vch_nro_rad_saf, vnm_vlr_saf_rp
            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) = 'ARP'
              AND b.secuencia = 4
              AND RTrim(cod_suc) = RTrim(pch_sucursal);
        EXCEPTION
            WHEN OTHERS THEN vnm_vlr_saf_rp := 0;
        END;
    END IF;

    vch_Ren_38 := '000383' || vch_nro_rad_saf || LPad(To_Char(vnm_vlr_saf_rp),10,'0');
    INSERT INTO FCO_ARP_MM VALUES (999938,vch_Ren_38);
    COMMIT;

    /*******	RENGLON 39	******/
    vnm_C4R39 := vnm_C4R37 - vnm_vlr_saf_rp;
    vch_Ren_39 := '000393' || LPad('0',10,'0') || LPad(To_Char(vnm_C4R39),10,'0');
    INSERT INTO FCO_ARP_MM VALUES (999939,vch_Ren_39);
    COMMIT;

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