
rem *********************************************************************

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

rem *********************************************************************

rem *                                                                   *

rem * Filename:             pkg_bag_loading.sql                         *

rem * Module Name:          pkg_bag_loading                             *

rem * Subsystem Name:       stored_procs                                *

rem * Originating Author:   James Creegan                               *

rem * Design Reference:     FAS/BBTRK/SD/225                            *

rem *                                                                   *

rem *********************************************************************

rem *                                                                   *

rem * Description:                                                      *

rem *  Copy of functionality used in existing file bag_load.pc          *

rem *********************************************************************

rem *                                                                   *

rem * Program Identification Array:                                     *

rem *                                                                   *

rem * "%Z%%M% VERSION %I% %E% %U%"

rem *                                                                   *

rem *********************************************************************

rem *                                                                   *

rem * Modification History:                                             *

rem *                                                                   *

rem * Rel Date/Time      Proj/Id   CA Ref/Details Of Change             *

rem * --- -------------- --------  ------------------------------------ *

rem * 1.0 28 May 03                File initially checked in            *

rem *********************************************************************/
PROMPT CREATE OR REPLACE PACKAGE BODY pkg_bag_loading

CREATE OR REPLACE 
PACKAGE BODY pkg_bag_loading
IS

PROCEDURE bag_load (
          -- diag IN OUT LONG, * Not Needed In SQL
          -- samfid IN OUT NUMBER, * Not Needed In SQL
          tagno IN OUT     VARCHAR2,
          fltid IN OUT     NUMBER,
          uldno IN OUT     VARCHAR2,
          lstrat1 IN       VARCHAR2,
          fltno IN         VARCHAR2,
          sno IN OUT       NUMBER,         -- return the new sequence no in case it changes to the loadall pax
          uid IN OUT       NUMBER,
          locn IN OUT      VARCHAR2,
          -- bdata IN OUT  RECORD, * struct used in PC implementation to return the results. Not needed here
          override1 IN     NUMBER,         -- 0; no override, 1; override uld class
          ufid IN OUT      NUMBER,         -- usually set to the same as fltid
          siteid IN        NUMBER,
          pax_id OUT       NUMBER,       
          return_code IN OUT NUMBER)
IS
   failed             EXCEPTION;
   retry_count        NUMBER:=0; 
   details_op_status  NUMBER := CONTINUE;
   bagtrace_op_status NUMBER := CONTINUE;
   load_op_status     NUMBER := CONTINUE;
   rv                 NUMBER := 0;
   rc                 NUMBER := 0;
   errid              NUMBER := 0;
   pid                NUMBER := 0;
   fid                NUMBER := 0;
   no_ldd             NUMBER := 0;
   no_chkd            NUMBER := 0;
   opfltid            NUMBER := 0;
   i                  NUMBER ;   /* type of bag_tag for call to split_bags */
   bag_count          NUMBER             ;
   uld_count          NUMBER             ;
   tag3               NUMBER             ;
   psno               NUMBER             ;
   bpmid              NUMBER             ;
   is_to_hold         NUMBER             ;
   ret                NUMBER             := 0;

   bpmsts  VARCHAR2(1)     := '';
   tag1    bag.bag_tag1%TYPE; 
   tag2    bag.bag_tag2a%TYPE;
   tag2a   bag.bag_tag2a%TYPE;
   tag2b   bag.bag_tag2b%TYPE;
   tag3s   VARCHAR2(6)     ;
   temp_bagtag    VARCHAR2(10)    ;
   cls            VARCHAR2(1)     ;
   udest          bag.bag_destination%TYPE;
   uclass         VARCHAR2(10)    ;
   lstrat_cd      ldg_strat.ldg_strat_name%TYPE ;
   bpm_type       VARCHAR2(2)     ;
   pname          bag.process_name%TYPE;
   uname          bag.unit_name%TYPE;
   did            VARCHAR2(7)     ;
   subname        VARCHAR2(4)     ;
   bag_except     bag.bag_exception%TYPE;
   opfltno        VARCHAR2(8)     ;
   opfltdate      VARCHAR2(5)     ;
   security_pax   NUMBER          ;
   selectee       NUMBER          ;
   flight_stat    NUMBER          ;
   dummy_uld      ULDS.ULD_NO%TYPE;
BEGIN

  return_code := 0;
  security_pax := get_carrier_cred(fltid);

  IF (fltid > 0 and security_pax = CONTINUE) then
    SELECT DECODE(pax_security_status, 'Y', 1, 0)
    INTO selectee
    FROM bag b, pax p
    WHERE p.pax_id        = b.pax_id
      AND p.flight_id     = fltid
      AND b.delete_marker = 0
      AND b.dcs_bag_tag   = tagno;
          
    IF (selectee = SELECT_PAX) then
      return_code := SELECTEE_BAG;
      RAISE failed;
    ELSE
      /* continue */
      null;
    END IF; 
  END IF;     
    
  IF LTRIM(fltid) IS NOT NULL THEN
    SELECT DECODE(flight_status, 'FC', 2,
                                 'PD', 1, 
                                       0)
    INTO flight_stat
    FROM flight
    WHERE flight_id = fltid;

    /* If post_departed (PD) or closed (FC) */
    IF (flight_stat = 2 or flight_stat = 1) then
      return_code:=CLOSED_FLIGHT;
      RAISE failed;
    ELSE
      /* continue */
      NULL;
    END IF;
   
    IF loading_prohibited(fltid) THEN
      return_code := -1081;
      RAISE failed;
    END IF;
  END IF;

  /*  lock the ulds to stop simultaneous loads */
  /*  unfortunately the liberal use off rollbacks */ 
  /*  and commits can  nullify this attempt    */
   
  IF ltrim(fltid) IS NOT NULL THEN
     IF v_debug = 1 THEN
       dbms_output.put_line (' Before ULD lock = errid='||errid||' tagno='||tagno||' tag1='||tag1||' tag2a='||tag2a||' tag2b='||tag2b||' tag3s='||tag3s||' tagno='||tagno||' tag3='||tag3||' sno='||sno||' fid='||fid||', pid='||pid||' errid='||errid||' return_code='||return_code);
     END IF;
     SELECT uld_no
     /* Must select the data into something, but we don't actually need it */
     INTO dummy_uld
     FROM ulds
     WHERE uld_no = uldno
     AND flight_id = fltid
     FOR UPDATE;
  ELSE
     /* We'll have to lock all of the ULDS with this number */
     SELECT uld_no
     /* Must select the data into something, but we don't actually need it */
     INTO dummy_uld
     FROM ulds
     WHERE uld_no = uldno
     FOR UPDATE;
  END IF;

  /* Get the bag_tag fields in order */
  split_tags( tagno, tag1, tag2a, tag2b, tag3s, temp_bagtag, i);
  tag3 := TO_NUMBER(tag3s);
  tagno:= temp_bagtag;  
  IF v_debug = 1 THEN
    dbms_output.put_line ('After splitting the tags, errid='||errid||' tagno='||tagno||' tag1='||tag1||' tag2a='||tag2a||' tag2b='||tag2b||' tag3s='||tag3s||' tagno='||tagno||' tag3='||tag3||' sno='||sno||' fid='||fid||', pid='||pid||' errid='||errid||' return_code='||return_code);
  END IF;
  IF v_ldg_strat = 1 THEN
    /* Check if this loading strategy really exists */
    /* note that the loading strategy name, lstrat_cd, is never used */
    get_ldg_strat(lstrat1, rv, lstrat_cd);

    /* if unknown or blank loading strategy return error */
    IF (rv < 0) THEN
      return_code := UNKNOWN_LDG_STRAT;
    END IF;
  END IF;

  /* Copy the temporary loading strat into the proper one              */
  /* NB This has been ported for completeness from the PC version but  */
  /* is no longer needed because the call to get_ldg_strat has changed */
  /* lstrat1:=lstrat_cd; */

  /* If we have an unknown fltid, or no fltid at all */
  IF fltid <=0 THEN 

    /** RESOLVE_FLIGHT:- call flight_id2 and return the correct error code if unsuccesful */

    /* look for flights with this flightno for D0 and the next few days */
    flight_id2(fltno, '-', siteid, fltid);

    /* if flight id still not resolved */
    IF (fltid <= 0) THEN

       /* return the code depending on why the id is not resolved */
      IF fltid = -1 THEN
        return_code := UNKNOWN_FLIGHT;
      ELSIF fltid = -2 THEN
        return_code := DUPLICATE_FLIGHT; 
      END IF;

    END IF;  -- fltid <=0 
  ELSE
    fid := fltid;
  END IF;
    
  SELECT COUNT (hold_no)
  INTO is_to_hold
  FROM holds
  WHERE hold_no = uldno;

  IF is_to_hold = 1 AND fid = 0 THEN
    return_code := -1001;
  END IF;

  IF return_code = 0 THEN
    IF is_to_hold = 1 THEN
      add_hold_uld (fid, uldno, rv);
      IF rv < 0 THEN
        return_code := rv;
      END IF;
    END IF;
  END IF;  

  IF return_code = 0 THEN

    /* now do the checks */

    IF v_debug = 1 THEN
      dbms_output.put_line ('Before check_bag_for_load, errid='||errid||' tagno='||tagno||' tag1='||tag1||' tag2a='||tag2a||' tag2b='||tag2b||' tag3='||tag3||'  sno='||sno||' fid='||fid||', pid='||pid||' errid='||errid||' return_code='||return_code);
    END IF;
 
    check_bag_for_load (uldno,
                        lstrat1,
                        tag1,
                        tag2a,
                        tag2b,
                        tag3,
                        sno,
                        fid,
                        ufid,
                        return_code,
                        udest,
                        uclass,
                        pid,
                        fid,
                        psno);
    sno := psno;      -- we have no interest in what the seq number was, only was it is
    IF v_debug = 1 THEN
      dbms_output.put_line ('After check_bag_for_load, errid='||errid||' tagno='||tagno||' tag1='||tag1||' tag2a='||tag2a||' tag2b='||tag2b||' tag3='||tag3||'  sno='||sno||' fid='||fid||', pid='||pid||' errid='||errid||' return_code='||return_code);
    END IF;
  END IF;
  /* do the override */
  IF return_code = WRONG_CLASS AND override1 = 1 THEN
    return_code := 0;
  /* if no passenger row exists, check_bag_for_load will return an error code */
  /* Add processing to recover from the error and raise a bag_alert           */
  ELSIF return_code = -1010 /* UNKNOWN_PAX */ 
   AND lstrat1 IN ('L','A') THEN
    /* get pax_id of loadall pax for this flight */
    BEGIN
      SELECT
        pax_id
      INTO
        pid
      FROM
        pax
      WHERE
        flight_id       = fid AND
        pax_sequence_no = -1  AND
        delete_marker   = 0;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        pid := 0;     /* No loadall, it will be added below */
    END;
    sno         :=  -1;
    pid         :=   0;
    return_code :=   1;
             
  END IF;

  IF return_code = 0 THEN  
    IF tag2b != '-' THEN
      SELECT b.process_name,
             b.unit_name,
             b.dcsid,
             b.subunit_name,
             b.bag_exception
      INTO pname,
           uname,
           did,
           subname,
           bag_except
      FROM bag b
      WHERE b.pax_id    = pid   AND
            b.bag_tag2b = tag2b AND
            b.bag_tag3  = tag3;
    ELSE
      SELECT b.process_name,
             b.unit_name,
             b.dcsid,
             b.subunit_name,
             b.bag_exception
      INTO pname,
           uname,
           did,
           subname,
           bag_except
      FROM bag b
      WHERE b.pax_id    = pid AND
            b.bag_tag2a = tag2a AND
            b.bag_tag3  = tag3;
    END IF; 

    get_bpm_status (pname,
                    uname,
                    did,
                    subname,
                    bag_except,
                    bpmsts);

  END IF;  -- return_code = 0

  IF return_code>=0 THEN      -- "loading of bags"
    IF return_code=0 THEN
      load_bag (tag2a,
                tag2b,
                tag3,
                pid,
                uldno,
                locn,   
                '0',
                fid,
                return_code);
    ELSE                        -- need to add a loadall pax
      /* get a class */
      cls:='*';/* use "wildcard" */

      /* call proc to add_bag */
      add_loaded_bag
         (uldno,
          tagno,
          tag1,
          tag2a,
          tag2b,
          tag3,
          pid,
          fid,
          udest,
          cls,
          locn,
          '0',
          return_code);
      IF v_debug = 1 THEN
        dbms_output.put_line ('After adding loaded bag, errid='||errid||' tagno='||tagno||' tag1='||tag1||' tag2a='||tag2a||' tag2b='||tag2b||' tag3='||tag3||'  sno='||sno||' fid='||fid||', pid='||pid||' errid='||errid||' return_code='||return_code);
      END IF;
    END IF;   -- IF return_code = 0

    IF return_code > 0 THEN  
      /* return_code must be a pid */
      pid := return_code;
      return_code := 0;
    END IF;

    IF bpmsts = 'Y' THEN 
      IF return_code >= 0 THEN -- doubt if this line is necessary, included from bag_load.pc for completeness
        generate_bpmid (bpmid);
      END IF;
      IF sno = 999 THEN
        bpm_type := '2';
      ELSE
        bpm_type := '1';
      END IF;

      mail_bpm1 (fid,
                 pid,
                 tagno,
                 bpm_type,
                 bpmid,
                 pname,
                 uname,
                 did,
                 subname,
                 rv);
    ELSE  -- bmpsts !='Y'
      bpmid := 0;
    END IF; 

    IF (rv < 0) THEN
      return_code := rv;
    END IF;

    errid := return_code;

    IF bpmsts = 'Y' THEN
      IF return_code >= 0 THEN
        store_bpm_user (bpmid,
                        uid,
                        locn);
      END IF;
    END IF;

    /* if fail end update trans */
    IF return_code < 0 THEN
      rollback;  
    END IF;

  END IF;  -- return_code = 0 ("loading of bags")
 
  IF sno = -1 THEN 
  /* the bag has been added to the LOADALL passenger */
  /* raise an UNKNOWN alert                          */
    INSERT INTO bag_alert
      ( pax_id, 
        bag_tag,
        bag_tag1,
        bag_tag2a,
        bag_tag2b,
        bag_tag3,
        alert_on_action,
        flight_id,
        last_time_stamp,
        no_warnings)
    VALUES
      ( pid,
        tagno,
        tag1,
        tag2a,
        tag2b,
        tag3,
       'UNKNOWN',
        fid,
        sysdate,
        1);
  END IF;                         

  add_bag_trace_record(
             uid,
             locn,
             errid,
             tagno,
             tag1,
             tag2a,
             tag2b,
             tag3,
             'LOAD',
             '0',
             fid,
             pid);
  /* no count_uld_loaded_bags in the SQL code as we're not calling the same logging functions as load_bag.pc */
  pax_id := pid;
EXCEPTION
  WHEN failed THEN
    /* There was a problem somewhere along the line, the return */
    /* code will give us the definition of what the problem was */                                                    
    raise_application_error (-200000, 'Error whilst running bag_load return_code='||return_code||'');
  WHEN others THEN
    IF v_debug = 1 THEN
      dbms_output.put_line ('Final return code='||return_code);
    END IF;
    raise;
END bag_load;

END pkg_bag_loading;
/
