/*****************************************************************************
*
* Filename         - $Workfile:   uld_ldg_prog.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 Package
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   uld_ldg_prog.sql  $  $Revision:   1.12  $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
* $Log:   /pvcsarch/db_ora/db/source/live/procedures/uld_ldg_prog.sqv  $
* 
*    Rev 1.12   06 Jun 2000 09:48:20   tagtrak
*  
* 
*    Rev 1.10   20 Sep 1999 14:42:12   tagtrak
*  
* 
*    Rev 1.9   25 Sep 1998 10:05:28   tagtrak
* OR: 12484
* CR: 12484
* REV:
* Merged Vienna (0.3.1E) and Core Product (0.2.5E) -
* branch ATS revision moved to trunk.
* Andrew Walker
* 
*    Rev 1.8.3.0   18 Aug 1998 09:42:22   tagtrak
* OR: 12484
* CR: 12484
* REV:
* Merging Vienna (0.3.1E) and Core Product (0.2.5E).
* Andrew Walker
* 
*    Rev 1.8.1.0   04 Jun 1998 15:33:08   tagtrak
* OR:
* CR: 12098
* REV:
* Vienna Modifications
* Andrew Walker
* 
*    Rev 1.7   15 Jan 1998 12:14:10   tagtrak
* OR: OR11101, TN13, TN15
* CR:
* REV:
* Phase 2C to Phase 3 upgrade plus code review actions and resolving some technical isses.
* 
*    Rev 1.6   24 Oct 1997 11:21:02   tagtrak
* Added PVCS header information
*
****************************************************************************/

PROMPT create or replace package uld_ldg_prog_pack

create or replace package uld_ldg_prog_pack
as

        /*
         * - Declare Variables and procedure declerations
         */

  type char1 is table of char(1) index by binary_integer;
  type char3 is table of char(3) index by binary_integer;
  type char5 is table of char(5) index by binary_integer;
  type char6 is table of char(6) index by binary_integer;
  type char8 is table of char(8) index by binary_integer;
  type char9 is table of char(9) index by binary_integer;
  type char10 is table of char(10) index by binary_integer;
  type tnumber is table of number index by binary_integer;
  type varchar5 is table of varchar(5) index by binary_integer;

 -- Procedure Declaration
        procedure uld_ldg_prog (
                fid        in out number,
                fno        in     char,
                fdt        in     char,
                agtcode    in     char,
                acode     out number,
                fltno     out char8,
                fltdt     out char5,
                udest     out char3,
                ucls      out char10,
                ucategory out varchar5,
                utype     out char1,
                uno       out char10,
                ubags     out tnumber,
                uhold     out char6,
                utime     out char5,
                fbtime    out char5,
                lbtime    out char5,
                lttime    out char5,
                uldweight         out   tnumber
        );
end uld_ldg_prog_pack;
/

PROMPT  create or replace package body uld_ldg_prog_pack

        create or replace package body uld_ldg_prog_pack
        as
                procedure uld_ldg_prog (
                        fid        in out number,
                        fno        in     char,
                        fdt        in     char,
                        agtcode    in     char,
                        acode    out number,
                        fltno    out char8,
                        fltdt    out char5,
                        udest    out char3,
                        ucls     out char10,
                        ucategory out varchar5,
                        utype    out char1,
                        uno      out char10,
                        ubags    out tnumber,
                        uhold    out char6,
                        utime    out char5,
                        fbtime   out char5,
                        lbtime   out char5,
                        lttime   out char5,
                  uldweight out tnumber
                )
                is
                        vfltno            char(8);
                        vfltdt            char(5);
                        weight            number;
                        rtcode            number;
                        no_flight_access  exception;
                        depcode        site_info.airport_code%type;
                        psiteid        site_info.site_id%type;
                        ret            number(10);
             
                begin
                        depcode := multi_site.get_agent_loc(agtcode);
                        psiteid := multi_site.get_site_id(depcode);

                        /*
                         * - IF the fid parameter is equal to 0 (ie. not specified) then
                         *   Call the flight_id2 procedure
                         *   IF an error occured from the flight_id2 procedure call then
                         *     Raise an error
                         * - ELSE
                         *   Call the flight_no procedure
                         * - Declare a cursor which selects variables from the uld table
                         * - Declare and initialise variable i.
                         * - LOOP around the cursor
                         *   Call the get_first_bag_load1 procedure.  If no data found, then
                         *    assign '' (blank) to the fbtime element of the cursor.
                         *   Call the get_last_bag_load1 procedure.  If no data found, then
                         *    assign '' (blank) to the lbtime element of the cursor.
                         *   Call the get_last_bld_trace procedure.  If no data found, then
                         *    assign '' (blank) to the lttime element of the cursor.
                         *   Assign the cursor variables to the relevant variables
                         *   Increment i
                         * - END LOOP
                         */

                        /* get fid if require */
                        
                          if (fid = 0) then 
                                flight_id2 (
                                        fno,
                                        fdt,
                                        psiteid,
                                        fid
                                );


                                /* Flight Id (fid) can not be found because of invalid flight no. */
                                /* and or flight date */ 
                                if (fid = -1 or fid = -2) then 
                                        raise_application_error (-20029, 'Invalid Flight No. or Date');
                                end if;
                        else 
                                
                            check_flight_access(fid, agtcode, rtcode);

                                  if rtcode = -1 then
                                       raise no_flight_access;
                                  end if;

                        flight_no (
                                        fid ,
                                        vfltno,
                                        vfltdt
                                );
                        end if;



                        /* find weight as there may no be a weight for this flight */
                        begin
                        select w.weight 
                                into  weight

                                from weights w,
                                                        flight f
                                where
                                                fid        = f.flight_id and
                                                w.flight_no = f.flight_no ;

                                exception
                                        when no_data_found then
                                                weight := 0;
                        end ;

                        

                        /* get data from uld table */

                        declare
                                cursor csr_0 is
                                        select
                                                d.uld_destination udest,
                                                d.uld_class_list ucls,
                                                u.uld_category ucategory,
                                                u.uld_type utype,
                                                u.uld_no uno,
                                                u.uld_no_of_bags ubags,
                                                u.uld_hold_no uhold,
                                                to_char(u.uld_load_time,'HH24:MI') utime
                                        from
                                                ulds u,
                                                uld_dests d
                                        where
                                                u.uld_no    = d.uld_no    and
                                                u.flight_id = d.flight_id and
                                                u.flight_id = fid
                                        order by u.uld_no, d.uld_destination;


                        i binary_integer :=1;
                        begin
                                for csr_rec_0 in csr_0 loop
                                        /* run procs above */
                                        begin

                                                get_first_bag_load1 (
                                                        csr_rec_0.uno,
                                                        fid,
                                                        fbtime(i)
                                                );
                                                exception
                                                        when no_data_found then
                                                                fbtime(i) := '';
                                        end;

                                        begin
                                                get_last_bag_load1 (
                                                        csr_rec_0.uno,
                                                        fid,
                                                        lbtime(i)
                                                );
                                                exception
                                                        when no_data_found then
                                                                lbtime(i) := '';
                                        end;


                                        begin
                                                get_last_bld_trace (
                                                        csr_rec_0.uno,
                                                        fid,
                                                        lttime(i)
                                                );
                                                exception
                                                        when no_data_found then
                                                                lttime(i) := '';
                                        end;

                                        -- This is an insert in to PL-SQL Table NULL;
                                        udest(i) := csr_rec_0.udest;
                                        ucls(i)  := csr_rec_0.ucls;
                                        ucategory(i) := csr_rec_0.ucategory;
                                        utype(i) := csr_rec_0.utype;
                                        uno(i)   := csr_rec_0.uno;
                                        ubags(i) := csr_rec_0.ubags;
                                        uhold(i) := csr_rec_0.uhold;
                                        utime(i) := csr_rec_0.utime;
                                        fltno(i) := vfltno;
                                        fltdt(i) := vfltdt;
                                        uldweight(i) := csr_rec_0.ubags*weight;

                                        i:=i+1;
                                end loop;
                        end;
exception
 
   when no_flight_access then
      acode := -1;


end uld_ldg_prog;

end uld_ldg_prog_pack;
/
