CREATE OR REPLACE PROCEDURE sp_fco_crea_pension
(
    pch_compania    char,
    psm_anio	    smallint,
    psm_mes  	    smallint,
    pch_sucursal    char,
    pch_FPO         char,
    pbl_generar_mm  boolean,
    pbl_regenerar   boolean
)

/**********************************************************************************/
/*** Modulo:      Modulo Fiscal Colombiano ADAM V.3				***/
/*** Descripcion: Crea tabla base para autoliquidacion de aportes a Pension.	***/
/*** Tablas:	  Parametros_MF, Trabajadores, Acumulados_Mensuales, MF_FPO,	***/
/***		  Traslados_FPO							***/
/*** Vistas:	  vw_trabajadores_cia, vw_FPO_trabajador, vw_div_trabajador, 	***/ 
/*** 		  vw_sueldos, vw_hist_traslados_FPO				***/
/*** Autor:	  Juan Carlos Leguizamon					***/
/*** Fecha:	  Julio 5 de 2001						***/
/*** Revision: 	  Abril 1 de 2002						***/
/*** Motor:	  Oracle 8i	Version interna: 1.00			        ***/
/**********************************************************************************/

AS
    vch_acum_imp 	char(15);
    vch_acum_tie 	char(15);

    vvc_tabla		varchar2(30);
    vbl_existe	        boolean;
    vnm_existe          number(1); -- cosas de oracle
    vvc_sql		varchar2(3000);

    vch_agr_02		char(5);
    vch_agr_03		char(5);
    vch_agr_04		char(5);
    vch_agr_08		char(5);
    vch_agr_10		char(5);
    vch_agr_11		char(5);
    vch_agr_13		char(5);
    vch_agr_14		char(5);
    vch_agr_18		char(5);
    vch_agr_19		char(5);
    vch_agr_20		char(5);

    vch_periodo 	char(8);

    vvc_condicion0      varchar2(255); --WHERE
    vvc_condicion1      varchar2(255); --AND
    vvc_condicion2      varchar2(255); --AND
    vvc_condicion3      varchar2(255); --AND

    vin_id_cursor integer;
    vin_rows	  integer;
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 Cotizacion */
    vch_periodo := '''' || LTrim(RTrim(psm_anio)) || Substr(vch_acum_imp,14,2) || '''';

    /*** Recuperar Agrupaciones de Conceptos ***/

    /* IBC de Pension */
    SELECT '''' || Nvl(dato, 'S02') || '''' INTO vch_agr_02 FROM vw_fdt_parametros 
    WHERE compania = pch_compania AND parametro = 'autoliq' AND secuencia = 2;

    /* Cotizacion Obligatoria de Pension */
    SELECT '''' || Nvl(dato, 'S03') || '''' INTO vch_agr_03 FROM vw_fdt_parametros 
    WHERE compania = pch_compania AND parametro = 'autoliq' AND secuencia = 3;

    /* Fondo de Solidaridad Pensional */
    SELECT '''' || Nvl(dato, 'S04') || '''' INTO vch_agr_04 FROM vw_fdt_parametros 
    WHERE compania = pch_compania AND parametro = 'autoliq' AND secuencia = 4;

    /* Vacaciones */
    SELECT '''' || Nvl(dato, 'S08') || '''' INTO vch_agr_08 FROM vw_fdt_parametros 
    WHERE compania = pch_compania AND parametro = 'autoliq' AND secuencia = 8;

    /* 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;

    /* Cotizacion Voluntaria al FPO por parte del Empleado */
    SELECT '''' || Nvl(dato, 'S13') || ''''  INTO vch_agr_13 FROM vw_fdt_parametros 
    WHERE compania = pch_compania AND parametro = 'autoliq' AND secuencia = 13;

    /* Cotizacion Voluntaria al FPO por parte del Patrono */
    SELECT '''' || Nvl(dato, 'S14') || ''''  INTO vch_agr_14 FROM vw_fdt_parametros 
    WHERE compania = pch_compania AND parametro = 'autoliq' AND secuencia = 14;

    /* 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;

    /* Valor No Retenido por Aportes Voluntarios al FPO */
    SELECT '''' || Nvl(dato, 'S19') || ''''  INTO vch_agr_19 FROM vw_fdt_parametros 
    WHERE compania = pch_compania AND parametro = 'autoliq' AND secuencia = 19;

    /* 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_FPO_' || 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_FPO;
   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 := ' WHERE RTrim(COD_FPO) =''' || RTrim(pch_FPO) || ''''; -- DELETE e INSERT en depurada
        vvc_condicion1 := ' AND RTrim(FPO.dato) =''' || RTrim(pch_FPO) || ''''; -- Insert General
        vvc_condicion2 := ' AND RTrim(COD_FPO) =''' || RTrim(pch_FPO) || ''''; -- Novedades
        vvc_condicion3 := ' AND RTrim(FPO) = ''' || RTrim(pch_FPO) || '''';     -- Fantasmas
  	ELSE
        vvc_condicion0 := ' WHERE RTrim(COD_SUC) = ''' || RTrim(pch_sucursal) || ''' AND RTrim(COD_FPO) =''' || RTrim(pch_FPO) || ''''; -- DELETE e INSERT en depurada
        vvc_condicion1 := ' AND RTrim(SUC.dato) = ''' || RTrim(pch_sucursal) || ''' AND RTrim(FPO.dato) =''' || RTrim(pch_FPO) || ''''; -- Insert General
        vvc_condicion2 := ' AND RTrim(COD_SUC) = ''' || RTrim(pch_sucursal) || ''' AND RTrim(COD_FPO) =''' || RTrim(pch_FPO) || ''''; -- Novedades
        vvc_condicion3 := ' AND RTrim(SUC) = ''' || RTrim(pch_sucursal) || ''' AND RTrim(FPO) = ''' || RTrim(pch_FPO) || '''';     -- 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 (FCO_FPO) */
        vvc_sql :=
        ' INSERT INTO FCO_FPO' ||
        ' SELECT AM.compania As CIA,  TG.trabajador As COD_TRAB, ' ||
        ' FPO.dato As COD_FPO, 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, '' '' As C13E, '' '' As C13F,'||
        ' Decode(AM.agr_conceptos,' || vch_agr_20 || ', Decode(' || vch_acum_imp || ',0,'' '',''X''), '' '') As C13G,' ||
        ' Decode(AM.agr_conceptos,' || vch_agr_18 || ', Decode(' || vch_acum_imp || ',0,'' '',''X''), '' '') As C13H,' ||
        ' Decode(AM.agr_conceptos,' || vch_agr_11 || ', Decode(' || vch_acum_imp || ',0,'' '',''X''), '' '') As C13I,' ||
        ' Decode(AM.agr_conceptos,' || vch_agr_10 || ', Decode(' || vch_acum_imp || ',0,'' '',''X''), '' '') As C13J,' ||
        ' Decode(AM.agr_conceptos,' || vch_agr_08 || ', Decode(' || vch_acum_imp || ',0,'' '',''X''), '' '') As C13K,' ||
        ' '' '' C13L,' ||
        ' Decode(AM.agr_conceptos,' || vch_agr_02 || ', ' || vch_acum_tie || ',0) As C14A,' ||
        ' Round((SU.salario),0) C15A,' ||
        ' Decode(AM.agr_conceptos,' || vch_agr_02 || ', Round((' || vch_acum_imp || '),0),0) As C17A,' ||
        ' 0.1450 As C18A,' ||
        ' Decode(AM.agr_conceptos,' || vch_agr_03 || ', Round((' || vch_acum_imp || '-1)/100,0)*100,0) As C19A,' ||
        ' Decode(AM.agr_conceptos,' || vch_agr_13 || ', ' || vch_acum_imp || ',0) As C20A,' ||
        ' Decode(AM.agr_conceptos,' || vch_agr_14 || ', ' || vch_acum_imp || ',0) As C20B,' ||
        ' Decode(AM.agr_conceptos,' || vch_agr_04 || ', Round((' || vch_acum_imp || '-1)/100,0)*100,0) As C22A,' ||
        ' Decode(AM.agr_conceptos,' || vch_agr_19 || ', ' || vch_acum_imp || ',0) As C23A' ||
        ' FROM trabajadores T, vw_fco_trabajadores_cia TG,'||
        ' vw_fco_fpo_trabajador FPO, vw_fco_div_trabajador SUC,' ||
        ' acumulados_mensuales AM, vw_fco_sueldos SU '||
        ' WHERE  T.trabajador = TG.trabajador AND' ||
        ' FPO.compania = TG.compania AND FPO.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_02 || ',' || vch_agr_03 || ',' || 
        vch_agr_04 || ',' || vch_agr_08 || ',' || vch_agr_10 || ',' || vch_agr_11 || ',' || 
        vch_agr_13 || ',' || vch_agr_14 || ',' || vch_agr_18 || ',' || vch_agr_19 || ',' ||
        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_FPO, COD_SUC, C11A, C11B, C12ABC,' ||
      ' '' '' C13A, '' '' C13B, '' '' C13C, '' '' C13D, '' '' C13E, '' '' C13F, MAX(C13G),' ||
      ' MAX(C13H), MAX(C13I), MAX(C13J), MAX(C13K), '' '' C13L, MAX(C14A), MAX(C15A),' ||
      ' MAX(C17A), MAX(C18A), MAX(C19A), MAX(C20A), MAX(C20B), MAX(C22A), MAX(C23A)' ||
      ' FROM FCO_FPO' || vvc_condicion0 ||
      ' GROUP BY CIA, C11B, C12ABC, COD_TRAB, COD_FPO, COD_SUC, C11A';
        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;


        /*** 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;


        /* TDA: INGRESO A ESTE FONDO DE PENSIONES */
        BEGIN
            vvc_sql := ' UPDATE ' || vvc_tabla ||
                       ' SET C13C = ''X''' ||
                       ' WHERE CIA =''' || pch_compania || ''' AND' ||
                       ' C13A <> ''X'' AND ' ||
                       ' EXISTS (SELECT 1 ' ||
                       ' FROM vw_fco_hist_traslados_FPO' ||
                       ' WHERE compania = cia AND ' ||
                       ' trabajador = cod_trab AND' ||
                       ' Substr(To_Char(fecha_alta,''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 TDA');
            END;
            COMMIT;
        END;

        /* TAA: RETIRO DE ESTE FONDO DE PENSIONES */
        BEGIN
            vvc_sql := ' UPDATE ' || vvc_tabla ||
                       ' SET C13D = ''X''' ||
                       ' WHERE CIA =''' || pch_compania || ''' AND' ||
                       ' EXISTS (SELECT 1 ' ||
                       ' FROM fco_traslados_FPO' ||
                       ' WHERE RTrim(compania) = RTrim(cia) AND ' ||
                       ' trabajador = cod_trab 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 Novedad TAA');
            END;
            COMMIT;
        END;

        /* VSP: CAMBIO PERMANENTE DE SUELDO */
        BEGIN    
            vvc_sql := ' UPDATE ' || vvc_tabla ||
                       ' SET C13E = ''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;

        /* VST: VARIACION TRANSITORIA DEL SALARIO */
        BEGIN    
            vvc_sql := ' UPDATE ' || vvc_tabla ||
                       ' SET C13G = '' ''' ||
                       ' WHERE CIA =''' || pch_compania || ''' AND' ||
                       ' (C13A = ''X'' OR C15A=C17A) ' || 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 actualizar Novedad VST');
            END;
            COMMIT;
        END;

    END IF;

    IF pbl_generar_mm THEN
        sp_fco_crea_pension_mm (pch_compania, psm_anio,psm_mes,pch_sucursal,pch_FPO);
    END IF;

   /*** Borrar datos de la tabla modelo ***/
   DELETE FCO_FPO;
   COMMIT;
    
END;
/
CREATE PUBLIC SYNONYM sp_fco_crea_pension FOR ADAM.sp_fco_crea_pension;
GRANT EXECUTE ON sp_fco_crea_pension TO adama;
