DROP TABLE adam.fco_fantasmas;
CREATE TABLE ADAM.fco_fantasmas
(
    compania    char(4) NOT NULL,
    trabajador  char(10) NOT NULL,
    anio        number(5) NOT NULL,
    mes         number(3) NOT NULL,
    sucursal    char(10) NOT NULL,
    tipo_id     char(2) NOT NULL,
    nro_id      varchar(16) NOT NULL,
    tipo_cotizante number(2) NULL,
    primer_apellido varchar2(20) NOT NULL,
    segundo_apellido varchar2(30) NOT NULL,
    primer_nombre varchar2(20) NOT NULL,
    segundo_nombre varchar2(30) NOT NULL,
    AFP         char(10) NOT NULL,
    EPS         char(10) NOT NULL,
    CCF         char(10) NOT NULL,
    CTR         char(10) NOT NULL,    
    dias_AFP    number(2) NOT NULL,
    dias_EPS    number(2) NOT NULL,
    dias_ARP    number(2) NOT NULL,
    dias_CCF    number(2) NOT NULL,
    salario     number(9,0) NOT NULL,
    IBC_AFP     number(9,0) NOT NULL,
    IBC_EPS     number(9,0) NOT NULL,
    IBC_ARP     number(9,0) NOT NULL,
    IBC_CCF     number(9,0) NOT NULL,
    aporte_AFP  number(9,0) NOT NULL,
    aporte_FSP_sol  number(9,0) NOT NULL,
    aporte_FSP_sub  number(9,0) NOT NULL,
    aporte_EPS  number(9,0) NOT NULL,
    UPC_adicional number(9,0) NOT NULL,
    tarifa_ARP number(9,8) NOT NULL,
    aporte_ARP  number(9,0) NOT NULL,
    aporte_CCF	number(9,0) NOT NULL,
    aporte_SENA	number(9,0) NOT NULL,
    aporte_ICBF	number(9,0) NOT NULL,
    aporte_ESAP	number(9,0) NOT NULL,
    aporte_MEdu	number(9,0) NOT NULL,
    extranjero char(1) NOT NULL,
    residente_exterior char(1) NOT NULL,
    departamento char(2) NOT NULL,
    municipio char(3) NOT NULL,
    planilla_pens number(1) NOT NULL,
    CONSTRAINT PK_fco_fantasmas PRIMARY KEY (compania, trabajador)
    USING INDEX TABLESPACE TS_ADAMINDICES
)
TABLESPACE TS_ADAMDATOS;

CREATE PUBLIC SYNONYM fco_fantasmas FOR ADAM.fco_fantasmas;
GRANT SELECT, INSERT, UPDATE, DELETE ON fco_fantasmas TO ADAMA;
DROP TABLE ADAM.fco_fantasmas_aux;
CREATE TABLE ADAM.fco_fantasmas_aux
(
    random number(5) NOT NULL,
    AFP_Aporte number(9) NOT NULL,
    FSP_Aporte_sol number(9) NOT NULL,
    FSP_Aporte_sub number(9) NOT NULL,
    EPS_Aporte number(9) NOT NULL,
    ARP_Aporte number(9) NOT NULL,
    CCF_Aporte number(9) NOT NULL,
    SENA_Aporte number(9) NOT NULL,
    ICBF_Aporte number(9) NOT NULL,
    CONSTRAINT pk_fco_fantasmas_aux PRIMARY KEY (random)
    USING INDEX TABLESPACE TS_ADAMINDICES    
)
TABLESPACE TS_ADAMDATOS;

CREATE PUBLIC SYNONYM fco_fantasmas_aux FOR ADAM.fco_fantasmas_aux;
GRANT SELECT, INSERT, UPDATE, DELETE ON fco_fantasmas_aux TO ADAMA;
CREATE VIEW ADAM.vw_fco_fantasmas
AS
SELECT 
    compania, trabajador, 
    primer_apellido || ' ' || segundo_apellido || ' ' || primer_nombre || ' ' || segundo_nombre AS nombre
FROM fco_fantasmas;

CREATE PUBLIC SYNONYM vw_fco_fantasmas FOR ADAM.vw_fco_fantasmas;
GRANT ALL ON vw_fco_fantasmas TO ADAMA;
CREATE OR REPLACE FUNCTION fn_fco_var_nom
(
    pch_compania char,
    pch_var_nro char,
    pbi_porcentaje number DEFAULT 0
)
RETURN number
-- JCL. Junio 22/05.
-- Retorna un valor de variables que contienen porcentaje
-- El valor devuelto va entre cero y uno, o NULL
--OCT-01-05: DCR, Pasada a Oracle de SQL
IS
  vdc_tar_Aux number(16,6); -- Variable Auxiliar para interpretar tarifas
BEGIN
    vdc_tar_Aux := NULL;

    SELECT real INTO vdc_tar_Aux FROM parametros_rh
    WHERE compania = pch_compania AND parametro_rh = 'var_nom_' || pch_var_nro;

    IF pbi_porcentaje = 0 THEN
        IF vdc_tar_Aux >= 1 THEN 
           vdc_tar_Aux := (vdc_tar_Aux / 100);
        END IF;
        IF vdc_tar_Aux > 1 THEN
           vdc_tar_Aux := NULL;
        END IF;
    END IF;

    RETURN (vdc_tar_Aux);
END;
/
CREATE PUBLIC SYNONYM fn_fco_var_nom FOR ADAM.fn_fco_var_nom;
GRANT ALL ON fn_fco_var_nom TO ADAMA;
CREATE OR REPLACE PROCEDURE sp_fco_baja_fantasmas
(
    i_compania		char,
    i_trabajador  char
)
AS
	vnm_existe	number(1);
BEGIN
  
	vnm_existe := 0; 
	BEGIN
  	SELECT 1 INTO vnm_existe 
		FROM fco_fantasmas
    WHERE compania = i_compania 
		AND TRIM(trabajador) = TRIM(i_trabajador);
	EXCEPTION
		WHEN OTHERS THEN vnm_existe := 0;
	END;

	IF vnm_existe = 0 THEN
		raise_application_error(-20001, 'El registro a borrar en [fco_fantasmas] no existe');
		RETURN;
	END IF;

	BEGIN
    DELETE FROM fco_fantasmas
    WHERE compania = i_compania 
		AND TRIM(trabajador) = TRIM(i_trabajador);
	EXCEPTION
		WHEN OTHERS THEN 
		raise_application_error(-20001, 'Error al borrar de la tabla [fco_fantasmas]');
		RETURN;
	END;
	COMMIT WORK;
END;
/
CREATE PUBLIC SYNONYM sp_fco_baja_fantasmas FOR ADAM.sp_fco_baja_fantasmas;
GRANT EXECUTE ON sp_fco_baja_fantasmas TO adama;
CREATE OR REPLACE PROCEDURE sp_fco_calcula_aportes
(
    psi_random number,
    pch_compania char,
    pdc_AFP_IBC decimal,
    pti_AFP_dias number,
    pdc_EPS_IBC decimal,
    pti_EPS_dias number,
    pdc_ARP_IBC decimal,
    pti_ARP_dias number,
    pdc_ARP_tarifa number,
    pdc_Par_IBC number,
    pti_CCF_dias number
)
/*************************************************************************/
-- Modulo Fiscal Colombiano 2.0 ADAM V3
-- Descripcion: Calcula los aportes en la sesión Aportantes por Fuera de Nómina
--              (fantasmas) según los parámetros indicados por el usuario
--              y teniendo en cuenta los porcentajes definidos en ADAM
-- Autor: Juan Carlos Leguizamon
-- Fecha: Junio 2005
-- Revisiones:
/*************************************************************************/
--OCT-01-05: DCR, Pasada a Oracle de SQL
AS
  vch_var_SaT char(2);      -- Variable Salud Trabajador
  vch_var_SaE char(2);      -- Variable Salud Empleador
  vch_var_PeT char(2);      -- Variable Pensión Trabajador
  vch_var_PeE char(2);      -- Variable Pensión Empleador
  vch_var_FSP char(2);      -- Variable Aporte FSP
  vch_var_FSP_Tope char(2); -- Variable Aporte FSP
  vch_var_CCF char(2);      -- Variable Aporte CCF
  vch_var_SENA char(2);     -- Variable Aporte SENA
  vch_var_ICBF char(2);     -- Variable Aporte ICBF
  vdc_tar_EPS number(6,5); -- Tarifa Salud
  vdc_tar_AFP number(6,5); -- Tarifa Pensión
  vdc_tar_FSP number(6,5); -- Tarifa Fondo de Solidaridad Pensional
  vdc_tar_FSP_Tope number(6,5); -- Tope Salarios para FSP
  vdc_tar_CCF number(6,5); -- Tarifa Cajas de Compensación
  vdc_tar_SENA number(6,5);-- Tarifa SENA
  vdc_tar_ICBF number(6,5);-- Tarifa ICBF
  vdc_sal_min number(19,6); -- Salario Mínimo
  vnu_FSP_Aporte number(19,6);
BEGIN
    SELECT dato INTO vch_var_SaT FROM vw_fdt_parametros WHERE pais = 'COL' AND compania = pch_compania AND parametro = 'autoliq' AND secuencia = 25;
    SELECT dato INTO vch_var_SaE FROM vw_fdt_parametros WHERE pais = 'COL' AND compania = pch_compania AND parametro = 'autoliq' AND secuencia = 26;
    SELECT dato INTO vch_var_PeT FROM vw_fdt_parametros WHERE pais = 'COL' AND compania = pch_compania AND parametro = 'autoliq' AND secuencia = 27;
    SELECT dato INTO vch_var_PeE FROM vw_fdt_parametros WHERE pais = 'COL' AND compania = pch_compania AND parametro = 'autoliq' AND secuencia = 28;
    SELECT dato INTO vch_var_FSP FROM vw_fdt_parametros WHERE pais = 'COL' AND compania = pch_compania AND parametro = 'autoliq' AND secuencia = 39;
    SELECT dato INTO vch_var_FSP_Tope FROM vw_fdt_parametros WHERE pais = 'COL' AND compania = pch_compania AND parametro = 'autoliq' AND secuencia = 38;
    SELECT dato INTO vch_var_CCF FROM vw_fdt_parametros WHERE pais = 'COL' AND compania = pch_compania AND parametro = 'parafiscal' AND secuencia = 9;
    SELECT dato INTO vch_var_SENA FROM vw_fdt_parametros WHERE pais = 'COL' AND compania = pch_compania AND parametro = 'parafiscal' AND secuencia = 10;
    SELECT dato INTO vch_var_ICBF FROM vw_fdt_parametros WHERE pais = 'COL' AND compania = pch_compania AND parametro = 'parafiscal' AND secuencia = 11;
    SELECT real INTO vdc_sal_min FROM parametros_rh WHERE compania = pch_compania AND parametro_rh = 'Sal_Minimo' AND secuencia_parametro = 1;

    vdc_tar_EPS := NVL(fn_fco_var_nom(pch_compania, vch_var_SaT, 0) + fn_fco_var_nom(pch_compania, vch_var_SaE, 0), 0.12);
    vdc_tar_AFP := NVL(fn_fco_var_nom(pch_compania, vch_var_PeT, 0) + fn_fco_var_nom(pch_compania, vch_var_PeE, 0), 0.15);
    vdc_tar_FSP := NVL(fn_fco_var_nom(pch_compania, vch_var_FSP, 0), 0.01);

    vdc_tar_FSP_Tope := 4;
    vdc_tar_CCF := NVL(fn_fco_var_nom(pch_compania, vch_var_CCF, 0), 0.04);
    vdc_tar_SENA := NVL(fn_fco_var_nom(pch_compania, vch_var_SENA, 0), 0.02);
    vdc_tar_ICBF := NVL(fn_fco_var_nom(pch_compania, vch_var_ICBF, 0), 0.03);

    DELETE fco_fantasmas_aux WHERE random = psi_random;

    vnu_FSP_Aporte := 0;
    IF pdc_AFP_IBC >= (vdc_sal_min * vdc_tar_FSP_Tope) THEN
       vnu_FSP_Aporte := (Round((pdc_AFP_IBC * vdc_tar_FSP * 0.5 * (pti_AFP_dias/30.00000) - 1)/100,0) * 100);
    END IF;

    INSERT INTO fco_fantasmas_aux
    (random, AFP_Aporte, FSP_Aporte_sol, FSP_Aporte_sub, EPS_Aporte, ARP_Aporte, CCF_Aporte, SENA_Aporte, ICBF_Aporte)
    SELECT
    psi_random,
    Round((pdc_AFP_IBC * vdc_tar_AFP * (pti_AFP_dias/30.00000) - 1)/100,0) * 100,
    vnu_FSP_Aporte, vnu_FSP_Aporte,
    Round((pdc_EPS_IBC * vdc_tar_EPS * (pti_EPS_dias/30.00000) - 1)/100,0) * 100,
    Round((pdc_ARP_IBC * pdc_ARP_tarifa * (pti_ARP_dias/30.00000) - 1)/100,0) * 100,
    Round((pdc_Par_IBC * vdc_tar_CCF * (pti_CCF_dias/30.00000) - 1)/100,0) * 100,
    Round((pdc_Par_IBC * vdc_tar_SENA * (pti_CCF_dias/30.00000) - 1)/100,0) * 100,
    Round((pdc_Par_IBC * vdc_tar_ICBF * (pti_CCF_dias/30.00000) - 1)/100,0) * 100
    FROM DUAL;

    COMMIT WORK;

END;
/
CREATE PUBLIC SYNONYM sp_fco_calcula_aportes FOR ADAM.sp_fco_calcula_aportes;
GRANT EXECUTE ON sp_fco_calcula_aportes TO adama;
CREATE OR REPLACE PROCEDURE ADAM.sp_fco_cambio_fantasmas
(
    pch_compania char, 
    pch_trabajador char,
    psi_anio number,
    pti_mes number,
    pch_sucursal char,
    pch_tipo_id char,
    pvc_nro_id varchar2,
    pti_tipo_cotizante number,
    pvc_primer_apellido varchar2, 
    pvc_segundo_apellido varchar2, 
    pvc_primer_nombre varchar2, 
    pvc_segundo_nombre varchar2, 
    pch_AFP char,
    pch_EPS char,
    pch_CCF char,
    pch_CTR char,
    pti_dias_AFP number,
    pti_dias_EPS number,
    pti_dias_ARP number,
    pti_dias_CCF number,
    pdc_salario number,
    pdc_IBC_AFP number,
    pdc_IBC_EPS number,
    pdc_IBC_ARP number,
    pdc_IBC_CCF number,
    pdc_aporte_AFP number,
    pdc_aporte_FSP_sol number,
    pdc_aporte_FSP_sub number,
    pdc_aporte_EPS number,
    pdc_UPC_adicional number,
    pdc_tarifa_ARP number,
    pdc_aporte_ARP number,
    pdc_aporte_CCF number,
    pdc_aporte_SENA number,
    pdc_aporte_ICBF number,
    pdc_aporte_ESAP number,
    pdc_aporte_MEdu number,
    pch_extranjero char,
    pch_reside_ext char,
    pch_departamento char,
    pch_municipio char,
    pbi_planilla_pens number
)
AS
	vnm_existe	number(1);
BEGIN
	vnm_existe := 0;
	BEGIN

    SELECT 1 INTO vnm_existe
    FROM fco_fantasmas
    WHERE compania = pch_compania 
		And trabajador = pch_trabajador;
	EXCEPTION
		WHEN OTHERS THEN vnm_existe := 0;
	END;

	IF vnm_existe = 0 THEN
		raise_application_error(-20001, 'El registro a actualizar en [fco_fantasmas] no existe');
		return;
	END IF;


--	BEGIN
    UPDATE fco_fantasmas
    SET anio = psi_anio, 
        mes = pti_mes, 
        sucursal = NVL(pch_sucursal, ' '),
        tipo_id = NVL(pch_tipo_id, ' '),
        nro_id = NVL(pvc_nro_id, 0),
        tipo_cotizante = NVL(pti_tipo_cotizante, 1),
        primer_apellido = NVL(pvc_primer_apellido, ' '),
        segundo_apellido = NVL(pvc_segundo_apellido, ' '),
        primer_nombre = NVL(pvc_primer_nombre, ' '),
        segundo_nombre = NVL(pvc_segundo_nombre, ' '),
        AFP = NVL(pch_AFP, ' '), 
        EPS = NVL(pch_EPS, ' '),
        CCF = NVL(pch_CCF, ' '),
        CTR = NVL(pch_CTR, ' '),
        dias_AFP = NVL(pti_dias_AFP, 0),
        dias_EPS = NVL(pti_dias_EPS, 0),
        dias_ARP = NVL(pti_dias_ARP, 0),
        dias_CCF = NVL(pti_dias_CCF, 0),
        salario = NVL(pdc_salario, 0),
        IBC_AFP = NVL(pdc_IBC_AFP, 0),
        IBC_EPS = NVL(pdc_IBC_EPS, 0),
        IBC_ARP = NVL(pdc_IBC_ARP, 0),
        IBC_CCF = NVL(pdc_IBC_CCF, 0),
        aporte_AFP = NVL(pdc_aporte_AFP, 0),
        aporte_FSP_sol = NVL(pdc_aporte_FSP_sol, 0), 
        aporte_FSP_sub = NVL(pdc_aporte_FSP_sub, 0),
        aporte_EPS = NVL(pdc_aporte_EPS, 0),
        UPC_adicional = NVL(pdc_UPC_adicional, 0),
        tarifa_ARP = NVL(pdc_tarifa_ARP, 0),
        aporte_ARP = NVL(pdc_aporte_ARP, 0),
        aporte_CCF = NVL(pdc_aporte_CCF, 0),
        aporte_SENA = NVL(pdc_aporte_SENA, 0),
        aporte_ICBF = NVL(pdc_aporte_ICBF, 0),
        aporte_ESAP = NVL(pdc_aporte_ESAP, 0),
        aporte_MEdu = NVL(pdc_aporte_MEdu, 0),
        extranjero = NVL(UPPER(pch_extranjero), 'N'),
        residente_exterior = NVL(UPPER(pch_reside_ext), 'N'),
        departamento = NVL(pch_departamento, ''),
        municipio = NVL(pch_municipio, ''),
        planilla_pens = NVL(pbi_planilla_pens, 0)        
    WHERE compania = pch_compania
     AND RTRIM(LTRIM(trabajador)) = RTRIM(LTRIM(pch_trabajador));
/*
	EXCEPTION
		WHEN OTHERS THEN
		raise_application_error(-20001, 'Error al actualizar la tabla [fco_Fantasmas]');
		return;
	END;
*/
	COMMIT WORK;
END;
/
CREATE PUBLIC SYNONYM sp_fco_cambio_fantasmas FOR ADAM.sp_fco_cambio_fantasmas;
GRANT EXECUTE ON sp_fco_cambio_fantasmas TO adama;
CREATE OR REPLACE PROCEDURE ADAM.sp_fco_alta_fantasmas
(
    pch_compania char, 
    pch_trabajador char,
    psi_anio number,
    pti_mes number,
    pch_sucursal char,
    pch_tipo_id char,
    pvc_nro_id varchar2,
    pti_tipo_cotizante number,
    pvc_primer_apellido varchar2, 
    pvc_segundo_apellido varchar2, 
    pvc_primer_nombre varchar2, 
    pvc_segundo_nombre varchar2, 
    pch_AFP char,
    pch_EPS char,
    pch_CCF char,
    pch_CTR char,
    pti_dias_AFP number,
    pti_dias_EPS number,
    pti_dias_ARP number,
    pti_dias_CCF number,
    pdc_salario number,
    pdc_IBC_AFP number,
    pdc_IBC_EPS number,
    pdc_IBC_ARP number,
    pdc_IBC_CCF number,
    pdc_aporte_AFP number,
    pdc_aporte_FSP_sol number,
    pdc_aporte_FSP_sub number,
    pdc_aporte_EPS number,
    pdc_UPC_adicional number,
    pdc_tarifa_ARP number,
    pdc_aporte_ARP number,
    pdc_aporte_CCF number,
    pdc_aporte_SENA number,
    pdc_aporte_ICBF number,
    pdc_aporte_ESAP number,
    pdc_aporte_MEdu number,
    pch_extranjero char,
    pch_reside_ext char,
    pch_departamento char,
    pch_municipio char,
    pbi_planilla_pens number
)
AS
    vsm_existe	number(1);
BEGIN
	vsm_existe := 0;
	BEGIN    
		SELECT 1 INTO vsm_existe FROM fco_Fantasmas
    WHERE compania = pch_compania 
		And trabajador = pch_trabajador 
		And anio = psi_anio 
		And mes = pti_mes;
	EXCEPTION
		WHEN OTHERS THEN vsm_existe := 0;
	END;

	IF vsm_existe = 1 THEN
		raise_application_error(-20001, 'El registro a insertar en [fco_fantasmas] ya existe');
		return;
	END IF;

--	BEGIN
    INSERT INTO fco_fantasmas
    (compania, trabajador, anio, mes, sucursal, tipo_id, nro_id, tipo_cotizante, primer_apellido, segundo_apellido, 
     primer_nombre, segundo_nombre, AFP, EPS, CCF, CTR, dias_AFP, dias_EPS, dias_ARP, dias_CCF, salario, IBC_AFP, IBC_EPS, 
     IBC_ARP, IBC_CCF, aporte_AFP, aporte_FSP_sol, aporte_FSP_sub, aporte_EPS, UPC_adicional, tarifa_ARP, aporte_ARP, 
     aporte_CCF, aporte_SENA, aporte_ICBF, aporte_ESAP, aporte_MEdu, extranjero, residente_exterior, departamento, municipio, planilla_pens)
    SELECT 
     pch_compania, pch_trabajador, NVL(psi_anio, 0), NVL(pti_mes, 12), 
     nvl(pch_sucursal, ' '), NVL(pch_tipo_id, ' '), NVL(pvc_nro_id, 0), NVL(pti_tipo_cotizante, 1), 
     NVL(pvc_primer_apellido, ' '), NVL(pvc_segundo_apellido, ' '), 
     NVL(pvc_primer_nombre, ' '), NVL(pvc_segundo_nombre, ' '),
     nvl(pch_AFP, ' '), nvl(pch_EPS, ' '), nvl(pch_CCF, ' '), nvl(pch_CTR, ' '), 
     NVL(pti_dias_AFP, 0), NVL(pti_dias_EPS, 0), NVL(pti_dias_ARP, 0), NVL(pti_dias_CCF, 0), 
     NVL(pdc_salario, 0), NVL(pdc_IBC_AFP, 0), NVL(pdc_IBC_EPS, 0), NVL(pdc_IBC_ARP, 0), 
     NVL(pdc_IBC_CCF, 0), NVL(pdc_aporte_AFP, 0), NVL(pdc_aporte_FSP_sol, 0), NVL(pdc_aporte_FSP_sub, 0), 
     NVL(pdc_aporte_EPS, 0), NVL(pdc_UPC_adicional, 0), NVL(pdc_tarifa_ARP, 0), NVL(pdc_aporte_ARP, 0), 
     NVL(pdc_aporte_CCF, 0), NVL(pdc_aporte_SENA, 0), NVL(pdc_aporte_ICBF, 0), 
     NVL(pdc_aporte_ESAP, 0), NVL(pdc_aporte_MEdu, 0), NVL(pch_extranjero, ' '), NVL(pch_reside_ext, ' '), 
     NVL(pch_departamento, ' '), NVL(pch_municipio, ' '), NVL(pbi_planilla_pens, 0)
     FROM DUAL;
--	EXCEPTION
	--	WHEN OTHERS THEN
	--	raise_application_error(-20001, 'Error al tratar de insertar en la tabla [fco_fantasmas]');
	--	return;
--	END;
	COMMIT WORK;
END;
/
CREATE PUBLIC SYNONYM sp_fco_alta_fantasmas FOR ADAM.sp_fco_alta_fantasmas;
GRANT EXECUTE ON sp_fco_alta_fantasmas TO adama;
