CREATE OR REPLACE PROCEDURE sp_fco_crea_riesgos
(
    pch_compania    char,
    psm_anio        smallint,
    psm_mes         smallint,
    pch_sucursal    char,
    pbl_generar_mm  boolean,
    pbl_regenerar   boolean
)
/**********************************************************************************/
/*** Modulo:      Modulo Fiscal Colombiano ADAM V.3                             ***/
/*** Descripcion: Crea tabla base para autoliquidacion de aportes a Riesgos     ***/
/*** Tablas:      Parametros_MF, Trabajadores, Acumulados_Mensuales, MF_ARP,    ***/
/*** Vistas:      vw_trabajadores_cia, vw_div_trabajador,                       ***/
/***              vw_sueldos, vw_hist_traslados_EPS, vw_hist_traslados_FPO      ***/
/*** Autor:       Juan Carlos Leguizamon                                        ***/
/*** Fecha:       Julio 10 de 2001                                              ***/
/*** Revision:    Febrero 24 de 2002                                            ***/
/*** Motor:       Oracle 8i                    Version interna: 1.00            ***/
/**********************************************************************************/
AS
    vch_acum_imp    char(15);
    vch_acum_tie    char(15);
    vch_periodo     char(8);
    vch_agr_08      char(5);
    vch_agr_09      char(5);
    vch_agr_10      char(5);
    vch_agr_11      char(5);
    vch_agr_12      char(5);
    vch_agr_17      char(5);
    vch_agr_18      char(5);
    vch_agr_20      char(5);
    vvc_tabla       varchar2(30);
    vbl_existe      boolean;
    vnm_existe      number(1); -- cosas de oracle
    vvc_sql         varchar2(3000);
    cvc_num_aut     varchar2(10);
    cnm_vlr_aut     number(9,0);
    cch_sucursal    char(10);
    vvc_condicion0  varchar(255); --WHERE
    vvc_condicion1  varchar(255); --AND
    vvc_condicion2  varchar(255); --AND
    vvc_condicion3  varchar(255); --AND
    vin_id_cursor   integer;
    vin_rows        integer;
    
    --DCRP, Jul18/2005.
    vnu_porc_arp number(16,6);
    
    CURSOR cr_autorizaciones IS
    SELECT
        sucursal, Max(numero) NRO, Sum(valor_total) VLR
    FROM
        fco_autorizaciones
    WHERE
        compania = pch_compania And
        periodo = Substr(vch_periodo,2,6) And  -- JCL Dic.19/04
	--	periodo = vch_periodo And  --DCR Jun2/2004
        tipo_entidad = 1
    GROUP BY
        sucursal
    HAVING Sum(valor_total) > 0;
BEGIN
    /* Campos que interesan de la tabla Acumulados Mensuales (importe y tiempo) */
    SELECT fn_fco_imp_mes(psm_mes), fn_fco_tie_mes(psm_mes)
    INTO vch_acum_imp, vch_acum_tie FROM dual;
    /* Periodo de acumulados (y de cotizacion) */
    vch_periodo := '''' || LTrim(RTrim(psm_anio)) || SubStr(vch_acum_imp,14,2) || '''';
    /*** Recuperar Agrupaciones de Conceptos ***/
    /* Vacaciones */
    SELECT '''' || Nvl(dato, 'S08') || '''' INTO vch_agr_08 FROM vw_fdt_parametros
    WHERE compania = pch_compania AND parametro = 'autoliq' AND secuencia = 8;
    /* Cotizacion Obligatoria Riesgos Profesionales */
    SELECT '''' || Nvl(dato, 'S09') || '''' INTO vch_agr_09 FROM vw_fdt_parametros
    WHERE compania = pch_compania AND parametro = 'autoliq' AND secuencia = 9;
    /* Licencia de Maternidad */
    SELECT '''' || Nvl(dato, 'S10') || ''''  INTO vch_agr_10 FROM vw_fdt_parametros
    WHERE compania = pch_compania AND parametro = 'autoliq' AND secuencia = 10;
    /* Incapacidad Enfermedad General */
    SELECT '''' || Nvl(dato, 'S11') || ''''  INTO vch_agr_11 FROM vw_fdt_parametros
    WHERE compania = pch_compania AND parametro = 'autoliq' AND secuencia = 11;
    /* Incapacidad por Accidente de Trabajo */
    SELECT '''' || Nvl(dato, 'S12') || ''''  INTO vch_agr_12 FROM vw_fdt_parametros
    WHERE compania = pch_compania AND parametro = 'autoliq' AND secuencia = 12;
    /* IBC Riesgos Profesionales */
    SELECT '''' || Nvl(dato, 'S17') || ''''  INTO vch_agr_17 FROM vw_fdt_parametros
    WHERE compania = pch_compania AND parametro = 'autoliq' AND secuencia = 17;
    /* Licencia No Remunerada y Sanciones (Suspension temporal del contrato) */
    SELECT '''' || Nvl(dato, 'S18') || ''''  INTO vch_agr_18 FROM vw_fdt_parametros
    WHERE compania = pch_compania AND parametro = 'autoliq' AND secuencia = 18;
    /* Ingresos o Aporte Sin Sueldo Basico (Variacion Temporal del Salario) */
    SELECT '''' || Nvl(dato, 'S20') || ''''  INTO vch_agr_20 FROM vw_fdt_parametros
    WHERE compania = pch_compania AND parametro = 'autoliq' AND secuencia = 20;
    /*** Fin de Recuperar Agrupaciones de Conceptos ***/
    /*** Establecer el Nombre de la Tabla ***/
     vvc_tabla := 'FCO_ARP_' || RTrim(pch_compania) || '_' || LTrim(psm_anio) || '_' || LTrim(psm_mes);
    /*** Verificar Existencia de la Tabla ***/
    vnm_existe := 0;
    vbl_existe := False;
    BEGIN
        SELECT 1 INTO vnm_existe
        FROM all_objects
        WHERE
            object_name = UPPER(vvc_tabla) And
            object_type = 'TABLE';
    EXCEPTION
        WHEN OTHERS THEN vnm_existe := 0;
    END;
    IF vnm_existe = 1 THEN
        vbl_existe := True;
    END IF;
   /*** Borrar datos de la tabla modelo ***/
   DELETE FCO_ARP;
   COMMIT;
    /*** Crear y dar permisos a la tabla***/
    IF NOT(vbl_existe) THEN
        sp_fco_crea_tablas (vvc_tabla);
    END IF;

    IF Nvl(Length(pch_sucursal),0) = 0 THEN
       vvc_condicion0 := ' '; -- DELETE e INSERT en depurada
              vvc_condicion1 := ' '; -- Insert General
              vvc_condicion2 := ' '; -- Novedades
              vvc_condicion3 := ' '; -- Fantasmas
    ELSE
       vvc_condicion0 := ' WHERE RTrim(COD_SUC) = ''' || RTrim(pch_sucursal) || ''''; -- DELETE e INSERT en depurada
              vvc_condicion1 := ' AND RTrim(SUC.dato) = ''' || RTrim(pch_sucursal) || ''''; -- Insert General
              vvc_condicion2 := ' AND RTrim(COD_SUC) = ''' || RTrim(pch_sucursal) || ''''; -- Novedades
              vvc_condicion3 := ' AND RTrim(SUC) = ''' || RTrim(pch_sucursal) || '''';  -- Fantasmas
    END IF;

    IF pbl_regenerar THEN
        vvc_sql := 'DELETE FROM ' || vvc_tabla || vvc_condicion0;
        BEGIN
            vin_id_cursor := dbms_sql.open_cursor;
            dbms_sql.parse(vin_id_cursor, vvc_sql, dbms_sql.v7);
            vin_rows := dbms_sql.execute(vin_id_cursor);
            dbms_sql.close_cursor(vin_id_cursor);
        EXCEPTION
            WHEN OTHERS THEN
                raise_application_error (-20001, '20001-Error al eliminar la informacion existente');
        END;
    ELSE
        vvc_condicion0 := ' ';
        vvc_condicion1 := ' ';
        vvc_condicion2 := ' ';
        vvc_condicion3 := ' ';                
    END IF;
    
    IF NOT(vbl_existe) OR pbl_regenerar THEN
        /* Insertar los valores No Depurados a la tabla modelo (MF_FPO) */
        vvc_sql :=
            ' INSERT INTO FCO_ARP' ||
            ' SELECT AM.compania As CIA, TG.trabajador As COD_TRAB,' ||
                ' SUC.dato As COD_SUC, ''C'' As C11A,' ||
                ' T.clave_unica As C11B, SUBSTR(T.nombre,1,60) As C12ABC,' ||
                ' '' '' As C13A, '' '' As C13B, '' '' As C13C, '' '' As C13D,'||
                ' Decode(AM.agr_conceptos,' || vch_agr_20 || ', Decode(' || vch_acum_imp || ',0,'' '',''X''),'' '') As C13E,' ||
                ' Decode(AM.agr_conceptos,' || vch_agr_18 || ', Decode(' || vch_acum_imp || ',0,'' '',''X''),'' '') As C13F,' ||
                ' Decode(AM.agr_conceptos,' || vch_agr_11 || ', Decode(' || vch_acum_imp || ',0,'' '',''X''),'' '') As C13G,' ||
                ' Decode(AM.agr_conceptos,' || vch_agr_10 || ', Decode(' || vch_acum_imp || ',0,'' '',''X''),'' '') As C13H,' ||
                ' Decode(AM.agr_conceptos,' || vch_agr_08 || ', Decode(' || vch_acum_imp || ',0,'' '',''X''),'' '') As C13I,' ||
                ' '' '' As C13J,' ||
                ' Decode(AM.agr_conceptos,' || vch_agr_12 || ', ' || vch_acum_tie || ',0) As C13K,' ||
                ' Decode(AM.agr_conceptos,' || vch_agr_17 || ', ' || vch_acum_tie || ',0) As C14A,' ||
                ' Round((SU.salario),0) As C15A,' ||
                ' Decode(AM.agr_conceptos,' || vch_agr_17 || ', Round((' || vch_acum_imp || '),0),0) As C17A,' ||
                ' 0 As C28A, 0 As C28B,' ||
                ' Decode(AM.agr_conceptos,' || vch_agr_09 || ', Round((' || vch_acum_imp || '-1)/100,0)*100,0) As C29A,' ||
                ' '' '' C30A, '' '' C30B,' ||
                ' Decode(AM.agr_conceptos,' || vch_agr_12 || ', ' || vch_acum_imp || ',0) As C32B' ||
            ' FROM trabajadores T, vw_fco_trabajadores_cia TG,'||
                ' vw_fco_div_trabajador SUC, acumulados_mensuales AM, vw_fco_sueldos SU'||
            ' WHERE  T.trabajador = TG.trabajador AND' ||
                ' SUC.compania = TG.compania AND SUC.trabajador = TG.trabajador AND' ||
                ' SU.compania  = TG.compania AND SU.trabajador  = TG.trabajador AND' ||
                ' AM.compania  = TG.compania AND AM.trabajador  = TG.trabajador AND' ||
                ' AM.compania = ''' || pch_compania || ''' AND' ||
                ' AM.anio =' || To_char(psm_anio)|| ' AND' ||
                ' AM.agr_conceptos IN ('||vch_agr_08||',' ||
                vch_agr_09||','||vch_agr_10||','||vch_agr_11||','||vch_agr_12||',' ||
                vch_agr_17||','||vch_agr_18||','||vch_agr_20||')' 
                || ' AND instr(T.clave_unica, ''S'') = 0' --solo para porvenir, al parecer en la clave unica a algunos les agregaron un S                
                || vvc_condicion1;
        BEGIN
            vin_id_cursor := dbms_sql.open_cursor;
            dbms_sql.parse(vin_id_cursor, vvc_sql, dbms_sql.v7);
            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 temporal.');
        END;
        COMMIT;
        /* inserta los valores (Depurados) a la tabla */
        vvc_sql :=
            ' INSERT INTO ' || vvc_tabla ||
            ' SELECT CIA, COD_TRAB, COD_SUC, C11A, C11B, C12ABC,' ||
                ' '' '' C13A, '' '' C13B, '' '' C13C, '' '' C13D, Max(C13E), Max(C13F), Max(C13G),' ||
                ' Max(C13H), Max(C13I), '' '' C13J, Max(C13K), Max(C14A), Max(C15A), Max(C17A),' ||
                ' 0 C28A, 0 C28B, Max(C29A), '' '' C30A, '' '' C30B, Max(C32B)' ||
            ' FROM FCO_ARP' || vvc_condicion0 ||
            ' GROUP BY CIA, C12ABC, C11B, COD_TRAB, COD_SUC, C11A' ||
            ' ORDER BY CIA, C12ABC';
        BEGIN
            vin_id_cursor := dbms_sql.open_cursor;
            dbms_sql.parse(vin_id_cursor, vvc_sql, dbms_sql.v7);
            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 depurada.');
        END;
        COMMIT;
		/***Actualizacion del tipo de documento. DCRP-Mayo5/2004 ***/
        BEGIN
            vvc_sql := ' UPDATE ' || vvc_tabla ||
                       ' SET C11A = ''T''' ||
                       ' WHERE CIA =''' || pch_compania || ''' AND' ||
                       ' EXISTS (SELECT 1 ' ||
                       ' FROM rel_trab_agr' ||
                       ' WHERE compania = cia AND ' ||
                       ' trabajador = cod_trab AND' ||
                       ' agrupacion = ''NIT'' AND' ||
                       ' dato = ''TI'''||')';
            BEGIN
                vin_id_cursor := dbms_sql.open_cursor;
                dbms_sql.parse(vin_id_cursor, vvc_sql, dbms_sql.v7);
                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 actualizar Tipo de documento [TI]');
            END;
            COMMIT;
        END;
        BEGIN
            vvc_sql := ' UPDATE ' || vvc_tabla ||
                       ' SET C11A = ''E''' ||
                       ' WHERE CIA =''' || pch_compania || ''' AND' ||
                       ' EXISTS (SELECT 1 ' ||
                       ' FROM rel_trab_agr' ||
                       ' WHERE compania = cia AND ' ||
                       ' trabajador = cod_trab AND' ||
                       ' agrupacion = ''NIT'' AND' ||
                       ' dato = ''CE'''||')';
            BEGIN
                vin_id_cursor := dbms_sql.open_cursor;
                dbms_sql.parse(vin_id_cursor, vvc_sql, dbms_sql.v7);
                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 actualizar Tipo de documento [CE]');
            END;
            COMMIT;
        END;
        /*** NOVEDADES ***/
        BEGIN
            /* ING: INGRESO A LA COMPANIA */
            vvc_sql := ' UPDATE ' || vvc_tabla ||
                       ' SET C13A = ''X''' ||
                       ' WHERE CIA =''' || pch_compania || ''' AND' ||
                       ' EXISTS (SELECT 1 ' ||
                       ' FROM vw_fco_trabajadores_cia' ||
                       ' WHERE compania = cia AND ' ||
                       ' trabajador = cod_trab AND' ||
                       ' Substr(To_Char(fecha_ingreso,''yyyymmdd''),1,6)='||vch_periodo||')' ||
                       vvc_condicion2;
            BEGIN
                vin_id_cursor := dbms_sql.open_cursor;
                dbms_sql.parse(vin_id_cursor, vvc_sql, dbms_sql.v7);
                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 insertar Novedad ING');
            END;
            COMMIT;
        END;
        BEGIN
            /* RET: RETIRO DE LA COMPANIA */
            vvc_sql := ' UPDATE ' || vvc_tabla ||
                       ' SET C13B = ''X''' ||
                       ' WHERE CIA =''' || pch_compania || ''' AND' ||
                       ' EXISTS (SELECT 1 ' ||
                       ' FROM vw_fco_trabajadores_cia' ||
                       ' WHERE compania = cia AND ' ||
                       ' trabajador = cod_trab AND' ||
                       ' Substr(To_Char(fecha_baja,''yyyymmdd''),1,6)='||vch_periodo||')' ||
                       vvc_condicion2;
            BEGIN
                vin_id_cursor := dbms_sql.open_cursor;
                dbms_sql.parse(vin_id_cursor, vvc_sql, dbms_sql.v7);
                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 insertar Novedad RET');
            END;
            COMMIT;
        END;
        /* VSP: CAMBIO PERMANENTE DE SUELDO */
        BEGIN
            vvc_sql := ' UPDATE ' || vvc_tabla ||
                       ' SET C13C = ''X''' ||
                       ' WHERE CIA =''' || pch_compania || ''' AND' ||
                       ' C13A <> ''X'' AND ' ||
                       ' EXISTS (SELECT 1 ' ||
                       ' FROM sueldos' ||
                       ' WHERE compania = cia AND ' ||
                       ' trabajador = cod_trab AND' ||
                       ' Substr(To_Char(fecha,''yyyymmdd''),1,6)='||vch_periodo||')' ||
                       vvc_condicion2;
            BEGIN
                vin_id_cursor := dbms_sql.open_cursor;
                dbms_sql.parse(vin_id_cursor, vvc_sql, dbms_sql.v7);
                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 insertar Novedad VSP');
            END;
            COMMIT;
        END;
        /* CODIGO NUEVA ADMINISTRADORA */
        /* EPS */
        BEGIN
            vvc_sql := ' UPDATE ' || vvc_tabla ||
                       ' SET C30A = (SELECT A.cod_super ' ||
                                   ' FROM fco_administradoras A, fco_traslados_eps T' ||
                                   ' WHERE A.codigo = T.EPS_nueva AND' ||
                                   ' A.tipo = ''EPS'' AND' ||
                                   ' RTrim(CIA) = RTrim(T.compania) AND' ||
                                   ' COD_TRAB = T.trabajador AND' ||
                                   ' Substr(To_Char(fecha_cambio,''yyyymmdd''),1,6)='||vch_periodo||')' ||
                       ' WHERE CIA =''' || pch_compania || ''' AND' ||
                       ' EXISTS (SELECT 1 ' ||
                               ' FROM fco_administradoras A, fco_traslados_eps T' ||
                               ' WHERE A.codigo = T.EPS_nueva AND' ||
                               ' A.tipo = ''EPS'' AND' ||
                               ' RTrim(CIA) = RTrim(T.compania) AND' ||
                               ' COD_TRAB = T.trabajador AND' ||
                               ' Substr(To_Char(fecha_cambio,''yyyymmdd''),1,6)='||vch_periodo||')' ||
                       vvc_condicion2;
            BEGIN
                vin_id_cursor := dbms_sql.open_cursor;
                dbms_sql.parse(vin_id_cursor, vvc_sql, dbms_sql.v7);
                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 insertar Codigo Nueva EPS');
            END;
            COMMIT;
        END;
        /* AFP */
        BEGIN
            vvc_sql := ' UPDATE ' || vvc_tabla ||
                       ' SET C30B = (SELECT A.cod_super ' ||
                                   ' FROM fco_administradoras A, fco_traslados_fpo T' ||
                                   ' WHERE A.codigo = T.FPO_nueva AND' ||
                                   ' A.tipo = ''AFP'' AND' ||
                                   ' RTrim(CIA) = RTrim(T.compania) AND' ||
                                   ' COD_TRAB = T.trabajador AND' ||
                                   ' Substr(To_Char(fecha_cambio,''yyyymmdd''),1,6)='||vch_periodo||')' ||
                       ' WHERE CIA =''' || pch_compania || ''' AND' ||
                       ' EXISTS (SELECT 1 ' ||
                               ' FROM fco_administradoras A, fco_traslados_fpo T' ||
                               ' WHERE A.codigo = T.FPO_nueva AND' ||
                               ' A.tipo = ''AFP'' AND' ||
                               ' RTrim(CIA) = RTrim(T.compania) AND' ||
                               ' COD_TRAB = T.trabajador AND' ||
                               ' Substr(To_Char(fecha_cambio,''yyyymmdd''),1,6)='||vch_periodo||')' ||
                       vvc_condicion2;
            BEGIN
                vin_id_cursor := dbms_sql.open_cursor;
                dbms_sql.parse(vin_id_cursor, vvc_sql, dbms_sql.v7);
                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 insertar Codigo Nueva FPO');
            END;
            COMMIT;
        END;
        /* INSERCION DE LAS AUTORIZACIONES POR EP y AT EN LA TABLA DEL PIE DE REPORTE */
        OPEN cr_autorizaciones;
        LOOP
            FETCH cr_autorizaciones INTO cch_sucursal, cvc_num_aut, cnm_vlr_aut;
            EXIT WHEN cr_autorizaciones%NOTFOUND;
            IF NOT(vbl_existe) OR (pbl_regenerar AND Rtrim(cch_sucursal) = RTrim(pch_sucursal)) Then
                sp_fco_cambio_ala_datos_adi (vvc_tabla, 2, 'ARP', cch_sucursal, 2, cvc_num_aut, cnm_vlr_aut, null, null);
            END IF;
        END LOOP;
        CLOSE cr_autorizaciones;
        COMMIT WORK;
    END IF;
    
    --insercion del % de riesgos, C28A
    vnu_porc_arp := 0;
    BEGIN
         SELECT real
         INTO vnu_porc_arp
         FROM parametros_rh
         WHERE compania = pch_compania
         AND parametro_rh = 'var_nom_38';
    EXCEPTION
         WHEN OTHERS THEN vnu_porc_arp := 0;
    END;
    
    vnu_porc_arp := vnu_porc_arp * 1000;
    BEGIN
        vvc_sql := ' UPDATE ' || vvc_tabla ||
                   ' SET C28A = ' || vnu_porc_arp ||
                   ' WHERE CIA =''' || pch_compania || '''';
        BEGIN
            vin_id_cursor := dbms_sql.open_cursor;
            dbms_sql.parse(vin_id_cursor, vvc_sql, dbms_sql.v7);
            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 insertar Codigo Nueva FPO');
        END;
        COMMIT;
    END;
    --fin insercion % riesgo
        
    BEGIN
        IF pbl_generar_mm THEN
            sp_fco_crea_riesgos_mm (pch_compania, psm_anio, psm_mes, pch_sucursal);
        END IF;
    END;
    
   /*** Borrar datos de la tabla modelo ***/
   DELETE FCO_ARP;
   COMMIT;
    
END;
/
CREATE PUBLIC SYNONYM sp_fco_crea_riesgos FOR ADAM.sp_fco_crea_riesgos;
GRANT EXECUTE ON sp_fco_crea_riesgos TO adama;

