PROMPT CREATE OR REPLACE PACKAGE wan_msg

CREATE OR REPLACE PACKAGE wan_msg AS

PROCEDURE close_open_ulds
   (   fid        IN  number
     , uldno      IN  char
     , pin        IN  number
     , action     IN  number
     , loc        IN  char
     , volume     IN  number
     , retcode    OUT number
   );

FUNCTION get_uld_close_flag
   (   uldno  IN ulds.uld_no%type
     , fid    IN flight.flight_id%type
   ) RETURN NUMBER;
END wan_msg;
/

PROMPT CREATE OR REPLACE PACKAGE BODY WAN_MSG
CREATE OR REPLACE PACKAGE BODY WAN_MSG AS

FUNCTION initiate_wan
 (
    flightid  IN flight.flight_id%type
  , pin       IN number
  , uldnum    IN ulds.uld_no%type
 )RETURN NUMBER IS

  PRAGMA         AUTONOMOUS_TRANSACTION; -- directive to compiler
 
  retcode        number := 0;
  counter        number := 0;
  hostname       varchar2(20) ;
  wan_port       number(10) := -1;
  uld_tab        uld_details_tab;
  uld_obj        uld_object;
  fda            flight.flight_date%type;
  fno            flight.flight_no%type;
  siteid         site_info.site_id%type;
  sitename       site_info.site_name%type;
  remote_s       wan_config.remote_server%type;
  remote_p       wan_config.port%type;
  err_msg        varchar2(300);
  msg            varchar2(300);
  err_code       number(10);
  user_msg       constant varchar2(20) := 'USER MESSAGE : ';
  default_port   db_config.db_value%type;
  msgid          varchar2(50);
  id             number(10);
  uld_nob        number(10);
  

  cursor get_uld_details is
  select bag_tag tag,
         BAG_SEQUENCE_NO seq,
         rtrim(PAX_SURNAME) sur,
         rtrim(PAX_INITIALS)  init
  from   bag_v2
  where  flight_id = flightid
    and  uld_no    = uldnum;
BEGIN
    
    hostname     := rtrim(config.GET_VALUE('RFS_HOST'));
    default_port := config.GET_VALUE('RFS_PORT');
    wan_port     := to_number(default_port);

    IF(hostname is null ) THEN
      hostname := 'XXX';
    END IF;

    IF(wan_port is null ) THEN
      wan_port := 0;
    END IF;

    BEGIN
      select flight_no, flight_date, flight.site_id, site_name
        into fno, fda, siteid, sitename
        from flight, site_info
       where flight.site_id = site_info.site_id
         and flight_id = flightid;
    EXCEPTION
      WHEN no_data_found THEN
         fno      := 'XXX';
         fda      := 'XXX';
         sitename := 'XXX';
    END;

    BEGIN
      select remote_server, port
        into remote_s, remote_p
        from wan_config
       where site_id = siteid;
    EXCEPTION
      WHEN no_data_found THEN
        remote_s := 'XXX';
        remote_p := 0;
    END;

    uld_tab := uld_details_tab();
    uld_tab.DELETE;

    FOR i IN get_uld_details LOOP
        counter := counter + 1;
        uld_tab.EXTEND(1);
        uld_tab(counter):=  uld_details(i.tag,i.seq,i.sur,i.init);
    END LOOP;

    select  wan_msg_id.nextval into id from dual;
    msgid   := to_char(id)||uldnum||sitename;
    uld_nob := uld_tab.COUNT;

    uld_obj := uld_object(uldnum,fno,fda,uld_tab.COUNT,sitename,remote_s,remote_p,wan_port,hostname,uld_tab,msgid);
    retcode := CallWriteUldData.sendwandata(uld_obj);

    IF (retcode = 1) THEN
       msg := 'UNKNOWN HOST FOR WAN PROCESS :' ||' '||hostname;
    ELSIF (retcode = 2) THEN
       msg := 'UNABLE TO CREATE SOCKET CONNECTION TO :'||' '||hostname||':'||wan_port;
    ELSIF (retcode IN (500,600)) THEN
       msg := 'APPLICATION ERROR CHECK DB UDUMP FOR SPECIFIC ERROR MSG';
    END IF;

    IF( retcode != 0) THEN
      err_msg := user_msg ||''|| msg;
      err_msg := err_msg  ||' * '||' ULD NO IS '||uldnum||' '||'FLIGHT_ID IS '||flightid;
      add_app_msg.log_msg(   retcode
                           , 'CLOSE ULD FAILED FOR MSG_ID '||' '||msgid
                           , 'RFS'
                           , 'CallWriteUldData'
                           , err_msg
                         ); 
    ELSE
       insert into rfs_log(msg_id,flight_id,uld_no,no_of_bag,log_time)
          values(msgid,
                 flightid,
                 uldnum,
                 uld_nob,
                 sysdate + get_offset.AIRPORT_CODE(multi_site.GET_USER_LOC(pin))
                );   
    END IF;
    COMMIT;
    RETURN retcode;
EXCEPTION
   WHEN OTHERS THEN
       err_msg  := SQLERRM;
       err_code := SQLCODE;
       INSERT INTO ORACLE_ERROR (PROCESS_NAME,ERRROR_MSG,ERROR_CODE,ERROR_TIME)
          VALUES ('WAN_MSG.CLOSE_OPEN_ULDS.INITIATE_WAN', err_msg,err_code,SYSDATE);
       COMMIT;
       return -502;
END initiate_wan;

PROCEDURE close_open_ulds
(      fid        IN  number
     , uldno      IN  char
     , pin        IN  number
     , action     IN  number
     , loc        IN  char
     , volume     IN  number
     , retcode    OUT number
) IS

 PRAGMA         AUTONOMOUS_TRANSACTION; -- directive to compiler
 
 err_msg        varchar2(300);
 err_code       number(10);
 found_flag     number(10) := 0;
 nob            ulds.ULD_NO_OF_BAGS%type := 0;
 flst           flight.flight_status%type;
 wan_flag       db_config.DB_VALUE%type;
 trace_time     char(5);
 l_uld_no       ulds.uld_no%TYPE := uldno;
 l_user_id      tagtrak_users.user_id%TYPE := pin;
 l_can_close_empty_ulds db_config.db_value%TYPE;

 l_need_volume boolean := false;

BEGIN
   retcode := 0;
   wan_flag               := rtrim(config.get_value('RFS_PROCESS'));
   l_can_close_empty_ulds := rtrim(config.get_value('CAN_CLOSE_EMPTY_ULDS'));

   -- If closing, check for volume requirements
   --
   if action = 1 then
	declare
	    l_config_value carrier_site_config.config_value%type;
	begin
	    select c.config_value into l_config_value
	    	from carrier_site_config c, flight f
	    	where f.flight_id = fid and
		      c.alpha_code = f.carrier_alpha_code and
		      f.site_id = c.site_id and
		      c.config_item = 'ULD_VOLUME_ON_CLOSE';

	    l_need_volume := (l_config_value = 'Y');
	exception
	    when NO_DATA_FOUND then
	    	null;
	end;

	if l_need_volume and nvl(volume, -1) = -1 then
	    retcode := -20800;
	    return;
	end if;
   end if;

   IF (action in (1,0)) THEN
         IF (action = 1) THEN --close uld
           BEGIN
              select uld_no_of_bags
                into nob
                from ulds
               where uld_no     = uldno
                 and flight_id  = fid
                 and pd_marker  = 'N';
                 found_flag := 1;
           EXCEPTION
            when no_data_found then
                 found_flag := 0;
           END;
           
	   -- Can close ULD if it contains > 0 bags, or if the system parameter
	   -- CAN_CLOSE_EMPTY_ULDS is set to 'Y'
           IF (found_flag = 1) THEN
               IF (nob = 0) AND (rtrim(NVL(l_can_close_empty_ulds,'?')) != 'Y') THEN
                 retcode := -220;
                 return; 
               END IF;
           END IF;
              
           update ulds
              set close_flag = 'Y', 
	          volume_code = volume
            where uld_no     = uldno
              and flight_id  = fid
              and pd_marker  = 'N';
           IF (SQL%FOUND) THEN
                  retcode := 0;
                  found_flag := 1;
                  BEGIN
                  
                      add_uld_trace_record(l_uld_no, fid, l_user_id, '0', loc, trace_time, 'CLOSED', 'Y');
                      
                  IF (wan_flag = 'Y') THEN
                       retcode := initiate_wan(fid,pin,uldno);
                  END IF;
                  EXCEPTION
                      WHEN OTHERS THEN
                      err_msg  := SQLERRM;
                      err_code := SQLCODE;
                      INSERT INTO ORACLE_ERROR (PROCESS_NAME,ERRROR_MSG,ERROR_CODE,ERROR_TIME)
                      VALUES ('WAN_MSG.CLOSE_OPEN_ULDS.CLOSE', err_msg,err_code,SYSDATE);
                      retcode := -502;
                  END;
           END IF;
          ELSIF(action = 0) THEN -- open uld
          
                    select flight_status
                      into flst
                      from flight 
                     where flight_id = fid;
                        
                    IF (flst IN ('FC','PD','FF')) THEN
                        retcode := -1081;
                        return;
                    END IF;
                    
                    update ulds
                       set close_flag = 'N'
                     where uld_no     = uldno
                       and flight_id  = fid
                       and pd_marker  = 'N';
                    
                    IF (SQL%FOUND) THEN
                     BEGIN
                      retcode := 0;
                      found_flag := 1;
                      
                      add_uld_trace_record(l_uld_no, fid, l_user_id, '0', loc, trace_time, 'OPENED', 'Y');

                     EXCEPTION
                      WHEN OTHERS THEN
                      err_msg  := SQLERRM;
                      err_code := SQLCODE;
                      INSERT INTO ORACLE_ERROR (PROCESS_NAME,ERRROR_MSG,ERROR_CODE,ERROR_TIME)
                      VALUES ('WAN_MSG.CLOSE_OPEN_ULDS.OPEN', err_msg,err_code,SYSDATE);
                      retcode := 0;
                     END;
                    END IF;
          END IF;
   END IF;
   IF (found_flag = 0 ) THEN
      retcode := -1001;
   END IF;
COMMIT;
EXCEPTION
  WHEN OTHERS THEN
            err_msg  := SQLERRM;
            err_code := SQLCODE;
            INSERT INTO ORACLE_ERROR (PROCESS_NAME,ERRROR_MSG,ERROR_CODE,ERROR_TIME)
            VALUES ('WAN_MSG.CLOSE_OPEN_ULDS', err_msg,err_code,SYSDATE);
            retcode := -502;
            COMMIT;
END close_open_ulds;

FUNCTION get_uld_close_flag
   (   uldno  IN ulds.uld_no%type
     , fid    IN flight.flight_id%type
   ) RETURN NUMBER IS
   
   PRAGMA         AUTONOMOUS_TRANSACTION;  -- directive to compiler
   
   flag  ulds.CLOSE_FLAG%type;
   retcode        number;
   err_msg        varchar2(300);
   err_code       number(10);

BEGIN
      retcode := 0;
      select close_flag
        into flag
        from ulds
      where  flight_id = fid
        and  uld_no    = uldno
        and  pd_marker = 'N';

        IF (flag = 'Y' ) THEN
          retcode := -1099;
        END IF;
        RETURN retcode;
EXCEPTION
   when no_data_found then
        retcode := 0 ;     /* Let the calling application deal with it */
        RETURN retcode;
   WHEN OTHERS THEN
            err_msg  := SQLERRM;
            err_code := SQLCODE;
            INSERT INTO ORACLE_ERROR (PROCESS_NAME,ERRROR_MSG,ERROR_CODE,ERROR_TIME)
            VALUES ('WAN_MSG.GET_ULD_CLOSE_FLAG', err_msg,err_code,SYSDATE);
            retcode := -502;
            COMMIT;
            RETURN retcode;
end get_uld_close_flag;
END wan_msg;
/

