/*****************************************************************************
*
* Filename         - $Workfile:   unload_entire_uld.sql  $
*
*                     Name  Yomi Kogbe     Date 03/05/00     Version 1    CR Number 15066
* 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 Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
PROMPT CREATE OR REPLACE procedure unload_entire_uld

CREATE OR REPLACE procedure unload_entire_uld
(
    v_retval         out number
  , v_flight_id      in  flight.flight_id%type
  , v_uld_no         in  ulds.uld_no%type
  , v_user_id        in  bag_trace.user_id%type
  , v_loc            in  bag_trace.location_code%type
  , new_flight_id    in  flight.flight_id%type
  , p_bpmid          in  bpm_data.bpmid%TYPE DEFAULT 0

) is
                abort EXCEPTION;
      Type tabrec is Record
      (b_bag_tag         bag.bag_tag%type,
       b_bag_tag1        bag.bag_tag1%type,
       b_bag_tag2a       bag.bag_tag2a%type,
       b_bag_tag2b       bag.bag_tag2b%type,
       b_bag_tag3        bag.bag_tag3%type,
       b_pax_id          pax.pax_id%type,
       b_uld_no          ulds.uld_no%type,
       b_conn_info       bag.connection_type%type,
       b_deliv_instr     bag.delivery_instr%TYPE,
       b_kg_weight       bag.kg_weight%TYPE
      );


      Type tabtyp is table of tabrec
           index by Binary_integer;

      vtabtyp    tabtyp;

      counter   number(10) := 0;

      v_uld_id_code           ulds.uld_id_code%TYPE;
      v_uld_category          ulds.uld_category%type;
      v_uld_type              ulds.uld_type%type;
      v_new_flight_id         flight.flight_id%type;
      v_fid                   flight.flight_id%type;
      v_flight_dest           flight_dests.flight_destination%type;
      v_uld_class_list        uld_dests.uld_class_list%type;
      v_uld_dest              uld_dests.uld_destination%type;
      v_onward_flight_no      uld_dests.onward_flight_no%TYPE;
      v_onward_flight_date    uld_dests.onward_flight_date%TYPE;
      v_uld                   ulds.uld_no%type;
      v_uld2                  ulds.uld_no%type;
      v_uld3                  ulds.uld_no%type;
      v_uld_no_of_bags        ulds.uld_no_of_bags%type;
      v_atl_dcs               bag.authority_to_load_dcs%type := 'Y';
      v_process               flight.process_name%type;
      v_unit_name             flight.unit_name%type;
      v_dcsid                 flight.dcsid%type;
      v_subunit_name          flight.subunit_name%type;
      l_loc                   bag_trace.location_code%type;
      l_user_id               bag_trace.user_id%type;
      l_fid                   flight.flight_id%type;
      l_count                 ulds.uld_no_of_bags%type;
      retval                  number(10);
      retval1                 number(10);
      retval2                 number(10);
      retval3                 number(10);
      retval4                 number(10);
      conn_type               ulds.connection_type%type;
      v_btype                 Bag.bag_type%TYPE DEFAULT NULL;
          c_action            CONSTANT bag_trace.trace_action%TYPE := 'OFFLOAD';

      offload_bags_error      exception;
      uld_error               exception;
      bag_alert               exception;

      cursor c1 is
      select    b.bag_tag         tag,
                b.bag_tag1        tag1,
                b.bag_tag2a       tag2a,
                b.bag_tag2b       tag2b,
                b.bag_tag3        tag3,
                b.pax_id          pax_id,
                b.uld_no          uld_no,
                b.connection_type conntype,
                b.delivery_instr  del_instr,
                b.kg_weight       kg_weight,
                u.uld_no_of_bags  uld_no_of_bags
        from    bag b, pax p, ulds u
       where    p.pax_id    = b.pax_id
         and    b.uld_no    = u.uld_no
         and    u.flight_id = p.flight_id
         and    b.loaded_status = 'Y'
         and    b.uld_no  = v_uld_no
         and    p.flight_id = v_flight_id
         for update;

       cursor c3 is
          select a.uld_no,
                 a.uld_no_of_bags,
                 a.uld_category,
                 a.uld_type,
                 a.connection_type,
                 a.uld_id_code,
                 b.uld_class_list,
                 b.uld_destination,
                 b.onward_flight_no,
                 b.onward_flight_date
            from ulds a, uld_dests b
           where a.uld_no = b.uld_no
             and a.flight_id = b.flight_id
             and a.uld_no = v_uld_no
             and a.flight_id = v_flight_id;

BEGIN
              /*Assign parameters to variables*/
                v_retval   := 0;
                l_fid      := v_flight_id;
                l_user_id  := v_user_id;
                l_loc      := v_loc;
                v_new_flight_id   := new_flight_id;
                v_uld3 := v_uld_no;

                IF new_flight_id != 0 then
                           BEGIN
                               SELECT FLIGHT_ID
                                 into v_fid
                                 FROM FLIGHT
                                WHERE FLIGHT_ID = NEW_FLIGHT_ID;
                           EXCEPTION

                              WHEN NO_DATA_FOUND THEN

                                 V_RETVAL := -851;
                                 RAISE abort;
                           END;

                           open c3;
                           loop
                           fetch c3 into v_uld,
                                         v_uld_no_of_bags,
                                         v_uld_category,
                                         v_uld_type,
                                         conn_type,
                                         v_uld_id_code,
                                         v_uld_class_list,
                                         v_uld_dest,
                                         v_onward_flight_no,
                                         v_onward_flight_date;
                           exit when c3%notfound;
                           end loop;
                           close c3;

                           For c1_rec IN c1
                           Loop

                           counter := counter + 1;
                           v_uld_no_of_bags := c1_rec.uld_no_of_bags;

                           vtabtyp(counter).b_bag_tag   := c1_rec.tag;
                           vtabtyp(counter).b_bag_tag1  := c1_rec.tag1;
                           vtabtyp(counter).b_bag_tag2a := c1_rec.tag2a;
                           vtabtyp(counter).b_bag_tag2b := c1_rec.tag2b;
                           vtabtyp(counter).b_bag_tag3  := c1_rec.tag3;
                           vtabtyp(counter).b_pax_id    := c1_rec.pax_id;
                           vtabtyp(counter).b_uld_no    := c1_rec.uld_no;
                           vtabtyp(counter).b_conn_info := c1_rec.conntype;
                           vtabtyp(counter).b_deliv_instr := c1_rec.del_instr;
                           vtabtyp(counter).b_kg_weight := c1_rec.kg_weight;

                           check_alert( c1_rec.tag2a, c1_rec.tag2b, c1_rec.tag3, c1_rec.pax_id, retval4);

                                      IF  retval4 < 0 then
                                          v_retval := -1064;
                                          RAISE abort;
                                      End if;

                           offload_bag(c1_rec.tag2a,
                                       c1_rec.tag2b,
                                       c1_rec.tag3,
                                       c1_rec.pax_id,
                                       c1_rec.uld_no,
                                       l_loc,
                                       '0',
                                       l_fid,
                                       retval
                                       );

                           add_bag_trace_record(l_user_id,
                                                l_loc,
                                                0,
                                                c1_rec.tag,
                                                c1_rec.tag1,
                                                c1_rec.tag2a,
                                                c1_rec.tag2b,
                                                c1_rec.tag3,
                                                'OFFLOAD',
                                                '0',
                                                l_fid,
                                                c1_rec.pax_id,
                                                c1_rec.uld_no    -- pass ULD number (Nigel, 31/03/04, CA598)
                                                );
                                                        

                            IF mws_web.store_bpm1( l_loc
                                                   ,l_user_id
                                                   ,l_fid
                                                   ,c1_rec.pax_id
                                                   ,c1_rec.tag
                                                   ,mws_web.f_pax_sequence_no(c1_rec.pax_id, l_fid)
                                                   ,c_action
                                                  ) THEN
			    	NULL;
                            END IF;

                            IF retval  < 0
                            THEN
                                 v_retval := retval;
                                 RAISE abort;
                            END IF;


                            End loop;


                            IF v_uld_no_of_bags =  counter then      
          
                                deassign_uld(v_uld3, l_user_id, l_loc, retval1);

                                            IF retval1 = -1100 then
                                                 begin
                                                     select  uld_no
                                                       into  v_uld2
                                                       from  ulds
                                                      where  uld_no    = v_uld3
                                                        and  flight_id = v_new_flight_id;
                                                 exception
                                                     when no_data_found then
                                                      add_uld_record ( v_uld3, v_new_flight_id, v_uld_category, v_uld_type, l_user_id, l_loc, conn_type, v_uld_id_code);
                                                      add_uld_dests_record ( v_uld3, v_uld_dest, v_uld_class_list, v_new_flight_id, v_onward_flight_no, v_onward_flight_date );
                                                 end;

                                             ELSE
                                                      add_uld_record ( v_uld3, v_new_flight_id, v_uld_category, v_uld_type, l_user_id, l_loc, conn_type, v_uld_id_code);
                                                      add_uld_dests_record ( v_uld3, v_uld_dest, v_uld_class_list, v_new_flight_id, v_onward_flight_no, v_onward_flight_date );
                                             END IF;
                            END IF;


                            IF counter != 0 then


                            For j IN 1..vtabtyp.count
                            Loop
                                exception_bags.add_unnamed_pax_and_excep_bag (
                                         v_new_flight_id,
                                         'RUSH',                -- All bags are RUSH
                                         vtabtyp(j).b_bag_tag,
                                         vtabtyp(j).b_bag_tag1,
                                         vtabtyp(j).b_bag_tag2a,
                                         vtabtyp(j).b_bag_tag2b,
                                         vtabtyp(j).b_bag_tag3,
                                         1,
                                         v_atl_dcs,
                                         v_uld_dest,
                                                                                                                                                                 'L',
                                         v_process,
                                         v_unit_name,
                                         v_dcsid,
                                         v_subunit_name,
                                         vtabtyp(j).b_bag_tag,
                                         vtabtyp(j).b_conn_info,
                                         v_btype,
                                         l_loc,
                                         l_user_id,
                                         NULL,
                                         vtabtyp(j).b_deliv_instr,
                                         NULL, /* World Tracer Reference */
                                         vtabtyp(j).b_kg_weight,
                                         NULL,
                                         retval2);  -- BVIS Sub-header

                                load_bag(vtabtyp(j).b_bag_tag2a,
                                      vtabtyp(j).b_bag_tag2b,
                                      vtabtyp(j).b_bag_tag3,
                                      retval2,
                                      v_uld3,
                                      l_loc,
                                      '0',
                                      v_new_flight_id,
                                      retval3
                                     );


                                     IF retval3  < 0 then
                                        v_retval := retval3;
                                        RAISE abort;
                                     END IF;


                              add_bag_trace_record(l_user_id,
                                                   l_loc,
                                                   0,
                                                   vtabtyp(j).b_bag_tag,
                                                   vtabtyp(j).b_bag_tag1,
                                                   vtabtyp(j).b_bag_tag2a,
                                                   vtabtyp(j).b_bag_tag2b,
                                                   vtabtyp(j).b_bag_tag3,
                                                   'ADD RUSH',
                                                   '0',
                                                   v_new_flight_id,
                                                   retval3,
                                                   vtabtyp(j).b_uld_no    -- pass ULD number (Nigel, 31/03/04, CA598)
                                                   );
                                                          
                             End Loop;

                             END IF;

                ELSE

                              open c3;
                              loop
                              fetch c3 into v_uld,
                                            v_uld_no_of_bags,
                                            v_uld_category,
                                            v_uld_type,
                                            conn_type,
                                            v_uld_id_code,
                                            v_uld_class_list,
                                            v_uld_dest,
                                            v_onward_flight_no,
                                            v_onward_flight_date;
                              exit when c3%notfound;
                              end loop;
                              close c3;

                              For c1_rec IN c1
                              Loop

                              counter := counter + 1;
                              v_uld_no_of_bags := c1_rec.uld_no_of_bags;

                              vtabtyp(counter).b_bag_tag   := c1_rec.tag;
                              vtabtyp(counter).b_bag_tag1  := c1_rec.tag1;
                              vtabtyp(counter).b_bag_tag2a := c1_rec.tag2a;
                              vtabtyp(counter).b_bag_tag2b := c1_rec.tag2b;
                              vtabtyp(counter).b_bag_tag3  := c1_rec.tag3;
                              vtabtyp(counter).b_pax_id    := c1_rec.pax_id;
                              vtabtyp(counter).b_uld_no    := c1_rec.uld_no;

                              offload_bag(c1_rec.tag2a,
                                          c1_rec.tag2b,
                                          c1_rec.tag3,
                                          c1_rec.pax_id,
                                          c1_rec.uld_no,
                                          l_loc,
                                          '0',
                                          l_fid,
                                          retval
                                          );


                              add_bag_trace_record(l_user_id,
                                                   l_loc,
                                                   0,
                                                   c1_rec.tag,
                                                   c1_rec.tag1,
                                                   c1_rec.tag2a,
                                                   c1_rec.tag2b,
                                                   c1_rec.tag3,
                                                   'OFFLOAD',
                                                   '0',
                                                   l_fid,
                                                   c1_rec.pax_id,
                                                   c1_rec.uld_no    -- pass ULD number (Nigel, 31/03/04, CA598)
                                                   );


                              IF mws_web.store_bpm1( l_loc
                                                     ,l_user_id
                                                     ,l_fid
                                                     ,c1_rec.pax_id
                                                     ,c1_rec.tag
                                                     ,mws_web.f_pax_sequence_no(c1_rec.pax_id, l_fid)
                                                     ,c_action
                                                    ) THEN
			      	NULL;
                              END IF;

                              IF retval  < 0
                              THEN
                                    v_retval := retval;
                                    RAISE abort;
                              END IF;

                              End loop;

                              IF v_uld_no_of_bags =  counter then

                                deassign_uld(v_uld3, l_user_id, l_loc, retval1);


                                IF retval1 = -1100 then
                                         null;
                                END IF;

                              END IF;

                END IF;

EXCEPTION
                WHEN abort THEN
                        NULL;
                        
    when others then
                v_retval := -1062;

end unload_entire_uld;
/
show errors
