/*****************************************************************************
*
* Filename         - $Workfile:   modify_pax.sql  $
*
*                     Name              Date      Version     CR Number
*                     Martin Helliwell 25/9/02      1.12        15681
* 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
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   modify_pax.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:   /shared/pvcs/aisarch/db_ora/db/source/live/procedures/modify_pax.sqv  $
*
*    Rev 1.12   26 Sep 2002 11:13:58   tagtrak
* OR:
* CR: 15681
* String too short in modify pax
*
*    Rev 1.11   12 Sep 2002 17:25:18   tagtrak
* OR:
* CR: 15644
* DESC: BA BRS Segregation changes
*
*    Rev 1.9   31 Jan 2000 14:49:32   tagtrak
*
*
*    Rev 1.8   20 Sep 1999 14:41:46   tagtrak
*
*
*    Rev 1.7   08 Apr 1999 15:23:14   tagtrak
* OR: 13206
* CR:
* REV:
* Andrew Walker
* (branch build revision copied to trunk.)
* Neil Bishop
*
*    Rev 1.6.1.0   25 Feb 1999 15:24:56   tagtrak
* OR: 13206
* CR:
* REV:
* Andrew Walker
*
*    Rev 1.6   25 Sep 1998 10:04:38   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.5.2.0   18 Aug 1998 09:36:04   tagtrak
* OR: 12484
* CR: 12484
* REV:
* Merging Vienna (0.3.1E) and Core Product (0.2.5E).
* Andrew Walker
*
*    Rev 1.5.1.0   04 Jun 1998 15:32:50   tagtrak
* OR:
* CR: 12098
* REV:
* Vienna Modifications
* Andrew Walker
*
*    Rev 1.5   02 Apr 1998 19:37:16   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
*
*    Rev 1.4   15 Jan 1998 12:13:44   tagtrak
* OR: OR11101, TN13, TN15
* CR:
* REV:
* Phase 2C to Phase 3 upgrade plus code review actions and resolving some technical isses.
*
*    Rev 1.3   24 Oct 1997 11:17:54   tagtrak
* Added PVCS header information
*
****************************************************************************/

PROMPT CREATE OR REPLACE PROCEDURE modify_pax

CREATE OR REPLACE PROCEDURE modify_pax
(
   fltid              IN NUMBER,
   codeshare_fid      IN NUMBER,
   init               IN CHAR,
   surn               IN CHAR,
   sts                IN CHAR,
   nobags             IN NUMBER,
   nosurn             IN CHAR,
   dest               IN CHAR,
   seat               IN CHAR,
   class              IN CHAR,
   seqno              IN NUMBER,
   dcs_seq_no         IN CHAR,
   secno              IN CHAR,
   pnr                IN CHAR,
   secsts             IN CHAR,
   depair             IN CHAR,
   ifltno             IN CHAR,
   ifltdate           IN CHAR,
   ibdg               IN CHAR,
   atl                IN CHAR,
   pax_full_inits     IN CHAR,
   onward_transfer    IN NUMBER,
   conn_type          IN CHAR,
   onwd_flt           IN     onward_flight_defs.onwd_flt_t,
   freq_flyer         IN VARCHAR2,
   paxid              OUT NUMBER,
   notnewflag         OUT NUMBER
) AS
   dm                   NUMBER(10);
   tsts                 CHAR(1);
   tnobags              NUMBER(10);
   tnosurn              CHAR(2);
   tseat                CHAR(3);
   tclass               CHAR(1);
   tsecsts              CHAR(1);
   tatl                 CHAR(1);
   tfreq_flyer          pax.freq_flyer%TYPE;
   mk_chg               NUMBER(10);
   tdest                CHAR(3);
   tinit                CHAR(4);
   tsurn                CHAR(27);
   tpax_full_inits      CHAR(60);
   tdcs_seq             pax.pax_dcs_sequence_no%TYPE;
   tpaxseqno            pax.pax_sequence_no%TYPE;
   tpaxseqno_standby    pax.pax_sequence_no%TYPE;
   tifltno              CHAR(8);
   tifltdate            CHAR(5);
   tibdg                CHAR(3);
   con_type             pax.connection_type%TYPE; -- CR 15681
   tonward_transfer     pax.onward_transfer%TYPE;
   v_dcs_boarding       carrier_lookup.dcs_boarding%TYPE ;
   v_new_pax_status     pax.pax_status%TYPE;
   bag_exception	bag.bag_exception%TYPE;
   embassy		pax.embassy%TYPE;

BEGIN

   paxid  := 0;
   mk_chg := 0;
   bag_exception := NULL;
   embassy := NULL;

   BEGIN
      SELECT pax_id,
             delete_marker
        INTO paxid,
             dm
        FROM pax
       WHERE pax_sequence_no = seqno
         AND booked_flight_id = codeshare_fid;

      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            paxid := 0;
   END;

   IF (paxid = 0) THEN
      notnewflag := 0;

      /* add the pax */

      add_pax (
         fltid,
         codeshare_fid,
         init,
         surn,
         sts,
         nobags,
         nosurn,
         dest,
         seat,
         class,
         seqno,
         dcs_seq_no,
         secno,
         pnr,
         secsts,
         depair,
         ifltno,
         ifltdate,
         ibdg,
         atl,
         pax_full_inits,
         onward_transfer,
         conn_type,
         onwd_flt,
         freq_flyer,
	 bag_exception,
	 embassy,
         paxid);

   ELSIF (dm = 1) THEN

      /* was deleted - so we can reuse them */

      notnewflag := 0;

      /* reuse the pax */

      reuse_pax
        (fltid,
         codeshare_fid,
         init,
         surn,
         sts,
         nobags,
         nosurn,
         dest,
         seat,
         class,
         seqno,
         dcs_seq_no,
         secno,
         secsts,
	 pnr, -- CA 118
         depair,
         ifltno,
         ifltdate,
         ibdg,
         atl,
         pax_full_inits,
         onward_transfer,
         conn_type,
         onwd_flt,
         freq_flyer,
         paxid);

   ELSE
      notnewflag := 1;

      /* get existing values */

      SELECT pax_status,
             no_of_bags,
             TO_CHAR(no_same_surname),
             pax_seat_no,
             pax_class,
             pax_security_status,
             authority_to_load_dcs,
             pax_destination,
             pax_initials,
             pax_surname,
             pax_full_inits,
             connection_type,
	     onward_transfer,
             freq_flyer,
             pax_dcs_sequence_no,
             pax_security_no,
             pax_sequence_no,
             inbound_flight_no,
             inbound_date,
             inbound_boarding_point
        INTO tsts,
             tnobags,
             tnosurn,
             tseat,
             tclass,
             tsecsts,
             tatl,
             tdest,
             tinit,
             tsurn,
             tpax_full_inits,
             con_type,
             tonward_transfer,
             tfreq_flyer,
             tdcs_seq,
             tpaxseqno,
             tpaxseqno_standby,
             tifltno,
             tifltdate,
             tibdg
        FROM pax
       WHERE pax_id =  paxid;

			v_new_pax_status := sts ; /* save sts to avoid changing the interface */

			/*
			 * If dcs is not boarding then pax must be updated with new status of C rather than B.
			 */

			if ( v_new_pax_status = 'B' ) then   /* { */
			begin
				select
					dcs_boarding
				into
					v_dcs_boarding
				from
					carrier_lookup cl, flight f
				where
					cl.alpha_code = substr(f.flight_no, 1, 3) and
					f.flight_id = fltid ;
				exception
					when too_many_rows then
						v_dcs_boarding := 'Y' ;
					when no_data_found then
						v_dcs_boarding := 'Y' ;
			end ;

			if (v_dcs_boarding = 'N' ) then   /* { */
				v_new_pax_status := 'C' ;
			end if ;   /* } */

			/*
			* If dcs is not boarding then pax must not be updated with new status of C
			* when existing status is B.
			*/
			/* } */
			elsif ( v_new_pax_status = 'C' AND tsts = 'B' ) then   /* { */
				begin
					select
						dcs_boarding
					into
						v_dcs_boarding
					from
						carrier_lookup cl, flight f
					where
						cl.alpha_code = substr(f.flight_no, 1, 3) and
						f.flight_id = fltid ;
					exception
						when too_many_rows then
							v_dcs_boarding := 'Y' ;
						when no_data_found then
							v_dcs_boarding := 'Y' ;
				end ;

				if (v_dcs_boarding = 'N' ) then   /* { */
					v_new_pax_status := 'B' ;
				end if ;   /* } */

			end if ;   /* } */


      /* pick up any new values */

      IF (pnr IS NOT NULL) THEN
         mk_chg := 1;
      END IF;


      IF (nobags >= 0) THEN
         tnobags := nobags;
         mk_chg  := 1;
      END IF;

      IF (nosurn != '-') THEN
         tnosurn := nosurn;
         mk_chg  := 1;
      END IF;

      IF (seat != '-') THEN
         tseat  := seat;
         mk_chg := 1;
      END IF;

      IF (class != '-') THEN
         tclass := class;
         mk_chg := 1;
      END IF;

      IF (secsts != '-') THEN
         tsecsts := secsts;
         mk_chg  := 1;
      END IF;

      IF (atl != '-') THEN
         tatl   := atl;
         mk_chg := 1;
      END IF;

      IF (dest != '-') THEN
         tdest  := dest;
         mk_chg := 1;
      END IF;

      IF (init != '-') THEN
         tinit  := init;
         mk_chg := 1;
      END IF;

      IF (surn != '-') THEN
         tsurn  := surn;
         mk_chg := 1;
      END IF;

      IF (conn_type != '-') THEN
         con_type  := conn_type;
         mk_chg := 1;
      END IF;

      IF ( freq_flyer != tfreq_flyer) OR
           (freq_flyer IS NULL AND tfreq_flyer IS NOT NULL) OR
           (tfreq_flyer IS NULL AND freq_flyer IS NOT NULL) THEN
         tfreq_flyer := freq_flyer;
         mk_chg := 1;
      END IF;

      IF (pax_full_inits != '-') THEN
         tpax_full_inits := pax_full_inits;
         mk_chg          := 1;
      END IF;

      IF (onward_transfer != tonward_transfer) THEN
         tonward_transfer := onward_transfer;
         mk_chg          := 1;
      END IF;

      IF (dcs_seq_no != '-') THEN
         tdcs_seq := dcs_seq_no;
         mk_chg   := 1;
      END IF;

      IF (seqno >=0) THEN
         tpaxseqno := seqno;
         mk_chg    := 1;
      END IF;

      IF (ifltno != '-') THEN
         tifltno := ifltno;
         mk_chg    := 1;
      END IF;

      IF (ifltdate != '-') THEN
         tifltdate := ifltdate;
         mk_chg    := 1;
      END IF;

      IF (ibdg != '-') THEN
         tibdg := ibdg;
         mk_chg    := 1;
      END IF;


      /*  If the pax is checked in make sure
       *  that the pax sequence no has the
       *  correct value otherwise standby pax will retail their
       *  system generated sequence number.
       *  Generally, if the new status is a Checked in and the pax_sequence_no
       *  is that of a Standby Pax then change the pax_sequenec_no to that
       *  not being > 100000.
       */

      IF ( sts = 'C' AND ( tpaxseqno_standby > 100000 OR tsts = 'S')) THEN
				 IF dcs_seq_no NOT IN ('0','-') THEN
				  	tpaxseqno := TO_NUMBER(dcs_seq_no);
            mk_chg   := 1;
				 ELSIF secno NOT IN ('0','-') THEN
				  	tpaxseqno := TO_NUMBER(secno);
            mk_chg   := 1;
				 ELSIF seqno >=  0 THEN
				  	tpaxseqno := seqno;
            mk_chg   := 1;
				 END IF;
      END IF;

      /* update to the new pax_class */
      IF (v_new_pax_status != '-') THEN
         tsts   := v_new_pax_status;
         mk_chg := 1;
      END IF;

      IF (mk_chg = 1) THEN

         /* modify record */

         UPDATE pax
            SET pax_status             = tsts,
                no_of_bags             = tnobags,
                no_same_surname        = TO_NUMBER(tnosurn),
                pax_seat_no            = tseat,
                pax_class              = tclass,
                pax_security_status    = tsecsts,
                authority_to_load_dcs  = tatl,
                pax_destination        = tdest,
                pax_initials           = tinit,
                pax_surname            = tsurn,
                pax_full_inits         = tpax_full_inits,
                pax_dcs_sequence_no    = tdcs_seq,       /* CA 229 */
                connection_type        = con_type,
                onward_transfer        = tonward_transfer,
                pax_name_record        = NVL(pnr,pax_name_record), /* CA 118 */
                freq_flyer             = tfreq_flyer,
                pax_sequence_no        = tpaxseqno,
                inbound_flight_no      = tifltno,
                inbound_date           = tifltdate,
                inbound_boarding_point = tibdg
          WHERE pax_id                 = paxid;

       -- update the pax_bags_class incase the .Y field changed
         tss_upd_pax_bags_class(p_pax_id    =>  paxid
                                ,p_flight_id =>  fltid);

      END IF;

			/*  add new onwd flight data, deleting old flight data first */
			/*  so that onward flight info relates to the last chg bsm sent */
			add_onward_flight(paxid, onwd_flt);

   END IF;


   --  If the pax is in the inbound_pax table update the inbound pax status
   update inbound_pax
          set RESULT_PAX_STATUS = 'AMENDED'
          where RESULT_PAX_ID  = paxid
            and RESULT_PAX_STATUS = 'CREATED';
  


END modify_pax;
/
