
/*****************************************************************************
*
* Filename              - $Workfile$
*
* Project Name          - UltraTrak Database
*
* Source Code Identifier        - 0/3/300/8010/112
*
* Description           - Database proc to freeze a flight by removing its agents
*                        No error codes are returned so it is easy for 
*                         a support engineer to cal from sqlplus
*                         eg  SQL> execute freeze_flight ('qf    1228','30sep','syd','fzsyd');
*
*
* Language              - Oracle PL*SQL
*
* File History          - For the development history run the Show History report in PVCS
*
* (c) Ultra Electronics Limited, 2001
*
*****************************************************************************/

PROMPT create or replace procedure freeze_flight

create or replace procedure freeze_flight(  p_Flight_no in flight.flight_no%type,
                                          p_flight_date  in flight.flight_date%type,
                                          p_airport_code in site_info.airport_code%type,
                                          p_restricted_agent IN agents.agent_code%TYPE
)

is
  fltid                flight.flight_id%type;
  l_Flight_no          flight.flight_no%type;
  p_error_message      mws_web.error_msg_type;
  p_error_number       mws_web.error_num_type;
  p_agent_code         agents.agent_code%TYPE;
BEGIN


    -- Normalise flight number
    BEGIN 
        l_flight_no := normalise_flight_no ( upper(p_flight_no) );


        fltid := 0;

        select
                flight_id
        into
                fltid
        from
                flight_outbound_v1 f ,site_info
        where
                flight_no   =  l_flight_no and
                flight_date = upper(p_flight_date) and
                f.site_id     =  site_info.site_id and
                site_info.airport_code = upper(p_airport_code) ;


        /*  find one of the flights agents which is required by mws_web */
        /*  if there are no agent with access flight is already hidden  */
        select
          agent_code
        into
          p_agent_code
        from
          flight_access
        where
                flight_id = fltid and rownum =1;

       exception WHEN NO_DATA_FOUND then
               raise_application_error (-20000,' Flight not found '||l_flight_no||' '||upper(p_flight_date) );
               dbms_output.put_line (' Flight not found '||p_flight_no||' '||p_flight_date);
               return;
    END;


    BEGIN
        dbms_output.put_line ('Freezing flight '||l_flight_no ||'  '||upper(p_flight_date)||
                                   '  to agent '||upper(p_restricted_agent) );

        mws_web.flight_restriction (fltid
                                   ,p_agent_code
                                   ,upper(p_restricted_agent)
                                   ,0 -- user_id
                                   ,0 -- kit_id
                                   ,p_error_message
                                   ,p_error_number);

        --dbms_output.put_line (p_error_message||' '||p_error_number);
        if (p_error_number  != 0 ) then
           p_error_message := 'Error='||p_error_number||'   '||p_error_message;
           p_error_number := -20999 ;
        --dbms_output.put_line (p_error_message||' '||p_error_number);
          raise_application_error (p_error_number,p_error_message);
        end if;

        exception WHEN NO_DATA_FOUND then
           raise_application_error (-20000,' Flight not found '||l_flight_no||' '||upper(p_flight_date) );
           dbms_output.put_line (' NO Flight ');
               -- NULL;
    END;


     dbms_output.put_line ( 'Flight '||l_flight_no ||'  '||upper(p_flight_date)||
                              ' restricted to users of agent '||upper(p_restricted_agent) );


end freeze_flight ;
/
