rem
rem	Script:		c_paralloc_pb.sql
rem	Author:		Jonathan Lewis
rem	Dated:		21-Sep-1998
rem	Purpose:	Parallel activity allocation package body
rem

create or replace package body parallel_allocation as

c_awaiting_processing	varchar2(1) := 'N';
c_being_processed	varchar2(1) := 'A';
c_processing_completed	varchar2(1) := 'C';
c_errored_out		varchar2(1) := 'E';

--	dup_val_on_index	exception;
--	no_data_found		exception;
--	too_many_rows		exception;

nowait_failed	exception;
pragma EXCEPTION_INIT(nowait_failed, -0054);

/*******************************

	Private Procedure

********************************/

procedure change_state(
		i_driving_task		in	varchar2,
		i_payload		in	varchar2,
		i_initial_state		in	varchar2,
		i_needed_state		in	varchar2,
		io_return_code		in out	number,
		io_error_message	in out	varchar2
) is
	m_rowid		rowid;
begin
	io_return_code := c_success;
	io_error_message := null;

	begin
		select	rowid
		into	m_rowid
		from
			parallel_allocation_list 
		where
			driving_task = i_driving_task
		and	payload = i_payload
		and	status = i_initial_state
		for update of status nowait;

	exception
		when no_data_found then
			io_return_code := c_row_not_found;
			return;
		when nowait_failed then
			io_return_code := c_row_locked;
			return;
		when others then
			io_return_code := c_general_error;
			io_error_message := sqlerrm;
			return;
	end;

	update parallel_allocation_list 
	set status = i_needed_state
	where rowid = m_rowid;

	commit;

end change_state;

/************************************

	Public Procedures

*************************************/

procedure create_driver(
		i_driving_task		in	varchar2,
		io_return_code		in out	number,
		io_error_message	in out	varchar2
) is

m_count	number := 0;

begin
	io_return_code := c_success;
	io_error_message := null;

	select	count(*) 
	into	m_count
	from	parallel_allocation_list
	where
		driving_task = i_driving_task
	;

	if (m_count != 0) then
		io_return_code := c_table_exists;
		io_error_message := 'Number of rows existing: ' || m_count;
	end if;
	commit;
	
end create_driver;

/*********************************************************************/

procedure drop_driver(
		i_driving_task		in	varchar2,
		io_return_code		in out	number,
		io_error_message	in out	varchar2
) is

m_rowid		rowid;
begin
	io_return_code := c_success;
	io_error_message := null;

	begin
		for r1 in (
			select	rowid 
			from	parallel_allocation_list
			where	driving_task = i_driving_task
			for update of status nowait
			)
		loop
			delete from parallel_allocation_list
			where rowid = r1.rowid;
		end loop;

	exception
		when nowait_failed then
			io_return_code := c_row_locked;
		when others then
			io_return_code := c_general_error;
			io_error_message := sqlerrm;
	end;
	commit;

end drop_driver;

/*********************************************************************/

procedure populate_driver(
		i_driving_task		in	varchar2,
		i_payload		in	varchar2,
		io_return_code		in out	number,
		io_error_message	in out	varchar2
) is
begin
	io_return_code := c_success;
	io_error_message := null;

	begin
		insert into parallel_allocation_list (
			driving_task,
			payload,
			status
		)
		values (
			i_driving_task,
			i_payload,
			c_awaiting_processing
		);
	exception
		when dup_val_on_index then
			io_return_code := c_duplicate_row;
		when others then
			io_return_code := c_general_error;
			io_error_message := sqlerrm;
	end;
	commit;
end populate_driver;

/*********************************************************************/

procedure allocate_target_item(
		i_driving_task		in	varchar2,
		o_payload		   out	varchar2,
		io_return_code		in out	number,
		io_error_message	in out	varchar2
) is

m_rowid		rowid;
m_payload	varchar2(40);
m_loop		number;

begin
	io_return_code := c_success;
	io_error_message := null;
	o_payload := null;
	commit;

	for m_loop in 1..5 loop
	begin
		select	rowid, payload
		into	m_rowid, m_payload
		from	parallel_allocation_list
		where	driving_task = i_driving_task
		and	status = c_awaiting_processing
		and	rownum = 1
		for update of status nowait;

	exception
		when no_data_found then
			io_return_code := c_no_rows_left;
			io_error_message := sqlerrm;
			return;

		when nowait_failed then 
			if m_loop = 5 then
				io_return_code := c_row_locked;
				io_error_message := sqlerrm;
				return;
			else
				sys.dbms_lock.sleep(1);
			end if;

		when others then
			io_return_code := c_general_error;
			io_error_message := sqlerrm;
			return;
	end;
	end loop;

	update parallel_allocation_list 
	set status = c_being_processed
	where rowid = m_rowid;

	o_payload := m_payload;
	commit;

end allocate_target_item;

/*********************************************************************/

procedure free_target_item(
		i_driving_task		in	varchar2,
		i_payload		in	varchar2,
		io_return_code		in out	number,
		io_error_message	in out	varchar2
) is
begin
	io_return_code := c_success;
	io_error_message := null;

	change_state(
		i_driving_task		=> i_driving_task,
		i_payload		=> i_payload,
		i_initial_state		=> c_being_processed,
		i_needed_state		=> c_awaiting_processing,
		io_return_code		=> io_return_code,
		io_error_message	=> io_error_message
	);

end free_target_item;

/*********************************************************************/

procedure complete_target_item(
		i_driving_task		in	varchar2,
		i_payload		in	varchar2,
		io_return_code		in out	number,
		io_error_message	in out	varchar2
) is
begin
	io_return_code := c_success;
	io_error_message := null;

	change_state(
		i_driving_task,
		i_payload,
		c_being_processed,
		c_processing_completed,
		io_return_code,
		io_error_message
	);
	
end complete_target_item;

/*********************************************************************/

procedure error_target_item(
		i_driving_task		in	varchar2,
		i_payload		in	varchar2,
		io_return_code		in out	number,
		io_error_message	in out	varchar2
) is
begin
	io_return_code := c_success;
	io_error_message := null;

	change_state(
		i_driving_task,
		i_payload,
		c_being_processed,
		c_errored_out,
		io_return_code,
		io_error_message
	);
	
end error_target_item;

/*********************************************************************/

procedure clear_error_for_target_item(
		i_driving_task		in	varchar2,
		i_payload		in	varchar2,
		io_return_code		in out	number,
		io_error_message	in out	varchar2
) is
begin
	io_return_code := c_success;
	io_error_message := null;

	change_state(
		i_driving_task,
		i_payload,
		c_errored_out,
		c_awaiting_processing,
		io_return_code,
		io_error_message
	);

end clear_error_for_target_item;

/*********************************************************************/

procedure reset_target_item(
		i_driving_task		in	varchar2,
		i_payload		in	varchar2,
		io_return_code		in out	number,
		io_error_message	in out	varchar2
) is
begin
	io_return_code := c_success;
	io_error_message := null;

	change_state(
		i_driving_task,
		i_payload,
		c_processing_completed,
		c_awaiting_processing,
		io_return_code,
		io_error_message
	);

end reset_target_item;

/*********************************************************************/

procedure reset_all_targets(
		i_driving_task		in	varchar2,
		io_return_code		in out	number,
		io_error_message	in out	varchar2
) is
begin
	io_return_code := c_success;
	io_error_message := null;

	begin
		for r1 in (
			select	rowid 
			from	parallel_allocation_list
			where	driving_task = i_driving_task
			and	status != c_awaiting_processing
			for update of status nowait
			)
		loop
			update parallel_allocation_list
			set status = c_awaiting_processing 
			where rowid = r1.rowid;
		end loop;

	exception
		when nowait_failed then
			io_return_code := c_row_locked;
		when others then
			io_return_code := c_general_error;
			io_error_message := sqlerrm;
	end;
	commit;
end reset_all_targets;

/*********************************************************************/

procedure clear_all_errors(
		i_driving_task		in	varchar2,
		io_return_code		in out	number,
		io_error_message	in out	varchar2
) is
begin
	io_return_code := c_success;
	io_error_message := null;

	begin
		for r1 in (
			select	rowid 
			from	parallel_allocation_list
			where	driving_task = i_driving_task
			and	status = c_errored_out
			for update of status nowait
			)
		loop
			update parallel_allocation_list
			set status = c_awaiting_processing 
			where rowid = r1.rowid;
		end loop;

	exception
		when nowait_failed then
			io_return_code := c_row_locked;
		when others then
			io_return_code := c_general_error;
			io_error_message := sqlerrm;
	end;
	commit;
end clear_all_errors;

end parallel_allocation;
.
/