/*****************************************************************************
*
* Filename         - $Workfile:   first_successful_load.sql  $
*
*                     Name              Date
* Author           -  Rob Cooke         11/01/2007
*
* Project Name     - FALCP
*
* Source Code
* Identifier       -
*
* Design Spec
* Section          - UAS/FALCP/CA/02135
*
* Contents         - This function will return the date/time for the first
*                    sucessful load action (ie error_id is 0) for a particular 
*                    bag from bag_trace
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2007
*
*****************************************************************************/

PROMPT create or replace function first_successful_load

CREATE OR REPLACE FUNCTION first_successful_load (p_pax_id      bag_trace.pax_id%TYPE,
                                                 p_bag_tag_2a  bag_trace.bag_tag2a%TYPE,
                                                 p_bag_tag_2b  bag_trace.bag_tag2b%TYPE,
                                                 p_bag_tag_3   bag_trace.bag_tag3%TYPE)
RETURN DATE AS
--Function to get the date/time when a particular bag was first sucessfully loaded.

  t_retval  DATE;

BEGIN

  SELECT min(bag_trace_time)
  INTO t_retval
  FROM bag_trace
  WHERE pax_id = p_pax_id
  AND (bag_tag2a = p_bag_tag_2a OR bag_tag2b = p_bag_tag_2b)
  AND bag_tag3 = p_bag_tag_3
  AND error_id = 0
  AND trace_action = 'LOAD';

  RETURN t_retval;

END first_successful_load;
/