CREATE OR REPLACE PROCEDURE sp_fco_cambio_ala_datos_adi
(
	i_tabla 	varchar2,
	i_sgss	 	number,
	i_cod_adm	char,
	i_cod_suc	char,
	i_item		smallint,
	i_referencia	varchar2,
	i_valor_1	number,
	i_valor_2 	number,
	i_valor_3	number
)
AS
	w_item 		smallint;
	vnm_existe	number(1);
BEGIN
	w_item := i_item;
	vnm_existe := 0;
   	IF i_item IS Null THEN 
		w_item := 0;
	END IF;

	BEGIN
		SELECT 1 INTO vnm_existe 
		FROM DUAL
		WHERE EXISTS (SELECT 1 FROM fco_ala_datos_adi
			WHERE 	RTrim(tabla) = RTrim(i_tabla) AND
			sgss = i_sgss AND
			RTrim(cod_adm) = RTrim(i_cod_adm) AND
			Nvl(RTrim(cod_suc)||'0','0') = Nvl(RTrim(i_cod_suc)||'0','0') AND
			item = w_item);
	EXCEPTION
		WHEN OTHERS THEN vnm_existe := 0;	
	END;

	IF vnm_existe = 1 THEN	
		UPDATE fco_ala_datos_adi
		SET referencia=i_referencia, valor_1 = i_valor_1, 
	    	valor_2 = i_valor_2, valor_3=i_valor_3
		WHERE 	RTrim(tabla) = RTrim(i_tabla) AND
		sgss = i_sgss AND
		RTrim(cod_adm) = RTrim(i_cod_adm) AND
		Nvl(RTrim(cod_suc)||'0','0') = Nvl(RTrim(i_cod_suc)||'0','0') AND
		item = w_item;

		commit;
   	ELSE
		INSERT INTO fco_ala_datos_adi VALUES (
			i_tabla,
			i_sgss,
			i_cod_adm,
			i_cod_suc,
			w_item,
			i_referencia,
			i_valor_1, 
			i_valor_2,
			i_valor_3);
	END IF;
	COMMIT WORK;
END;
/
CREATE PUBLIC SYNONYM sp_fco_cambio_ala_datos_adi FOR ADAM.sp_fco_cambio_ala_datos_adi;
GRANT EXECUTE ON sp_fco_cambio_ala_datos_adi TO adama;
