/*****************************************************************************
*
* Filename         - $Workfile:   hot_connection.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          -
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2000
*
*****************************************************************************/
PROMPT CREATE OR REPLACE FUNCTION hot_connection

CREATE OR REPLACE
FUNCTION hot_connection(p_arrival_time IN DATE , p_departure_time IN DATE) RETURN NUMBER IS
	FUNCTION min_turnaround_time RETURN NUMBER IS
		v_min_turnaround_time			NUMBER;
		BEGIN
			SELECT TO_NUMBER(db_value) / (24 * 60)
			INTO v_min_turnaround_time
			FROM db_config
			WHERE db_item = 'MIN_TURNAROUND_TIME';

			RETURN (v_min_turnaround_time);
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
				RETURN (20 / (24 * 60));
		END min_turnaround_time;

	FUNCTION default_time(p_date	IN DATE) RETURN BOOLEAN IS
		BEGIN
			RETURN (p_date = TRUNC(p_date));
		END default_time;

	BEGIN
		IF p_arrival_time + min_turnaround_time > p_departure_time 
		AND NOT default_time(p_departure_time) 
		AND NOT default_time(p_arrival_time) THEN
			RETURN (p_arrival_time + min_turnaround_time - p_departure_time);   
		ELSE
			RETURN (0);
		END IF;
	END hot_connection;
/
