/*****************************************************************************
*
* Filename         - stop_reasons.sql
*
* Project Name     - UltraTrak
*
* Contents         - Live Schema Stored Package
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2008
*
*****************************************************************************/

create or replace package Stop_Reason as

   function Last_Reason(p_for_bag in bag.bag_id%type)
                                    return bag_trace.stop_reason%type;

    function Last_Reason (p_flight    in flight.flight_id%type,
                          p_bag_tag2a in bag.bag_tag2a%type,
                          p_bag_tag2b in bag.bag_tag2b%type,
                          p_bag_tag3  in bag.bag_tag3%type)
                                    return bag_trace.stop_reason%type;

end Stop_Reason;
/

create or replace package body Stop_Reason as

   function Last_Reason(p_for_bag in bag.bag_id%type)
                                    return bag_trace.stop_reason%type is

        l_bag_tag2a bag.bag_tag2a%type;
        l_bag_tag2b bag.bag_tag2b%type;
        l_bag_tag3 bag.bag_tag3%type;

        l_flight_id flight.flight_id%type;

        l_reason bag_trace.stop_reason%type;

    begin

        select f.flight_id into l_flight_id
            from flight f, bag b, pax p
            where b.bag_id = p_for_bag and
                  p.pax_id = b.pax_id and
                  p.flight_id = f.flight_id;

        select bag_tag2a, bag_tag2b, bag_tag3
            into l_bag_tag2a, l_bag_tag2b, l_bag_tag3
            from bag
            where bag_id = p_for_bag;

        return Last_Reason(l_flight_id, l_bag_tag2a, l_bag_tag2b, l_bag_tag3);

    exception
        when NO_DATA_FOUND then
            return null;

    end Last_Reason;


    function Last_Reason (p_flight    in flight.flight_id%type,
                          p_bag_tag2a in bag.bag_tag2a%type,
                          p_bag_tag2b in bag.bag_tag2b%type,
                          p_bag_tag3  in bag.bag_tag3%type)
                                    return bag_trace.stop_reason%type is

        l_reason bag_trace.stop_reason%type;

    begin

        select stop_reason into l_reason from
            (select *
                from bag_trace
                where bag_tag3 = p_bag_tag3 and
                      (bag_tag2b = p_bag_tag2b or bag_tag2a = p_bag_tag2a) and
                      trace_action = 'STOP' and
                      flight_id_scan = p_flight
                order by action_time desc) where rownum = 1;

        return l_reason;

    exception
        when NO_DATA_FOUND then
            return null;

    end Last_Reason;

end Stop_Reason;
/
