/*****************************************************************************
*
* Filename         - $Workfile:   reuse_pax.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 Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   reuse_pax.sql  $  $Revision:   1.9  $
*
* 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/reuse_pax.sqv  $
* 
*    Rev 1.9   12 Sep 2002 17:25:22   tagtrak
* OR:
* CR: 15644
* DESC: BA BRS Segregation changes
* 
*    Rev 1.8   01 Aug 2002 13:37:08   yomi
* DESC: Timezone change
* CR:   15508
* 
*    Rev 1.8   01 Aug 2002 13:31:18   yomi
* DESC: Timezone change
* CR:   15508
* 
*    Rev 1.7   31 Jan 2000 14:49:32   tagtrak
*  
* 
*    Rev 1.6   08 Apr 1999 15:23:54   tagtrak
* OR: 13206
* CR:  
* REV:
* Andrew Walker
* (branch build revision copied to trunk.)
* Neil Bishop
* 
*    Rev 1.5.1.0   25 Feb 1999 15:25:24   tagtrak
* OR: 13206
* CR:  
* REV:
* Andrew Walker
* 
*    Rev 1.5   25 Sep 1998 10:05:12   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.4.3.0   18 Aug 1998 09:36:16   tagtrak
* OR: 12484
* CR: 12484
* REV:
* Merging Vienna (0.3.1E) and Core Product (0.2.5E).
* Andrew Walker
* 
*    Rev 1.4.1.0   04 Jun 1998 15:33:02   tagtrak
* OR:
* CR: 12098
* REV:
* Vienna Modifications
* Andrew Walker
* 
*    Rev 1.4   02 Apr 1998 19:37:56   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.3   15 Jan 1998 12:14:06   tagtrak
* OR: OR11101, TN13, TN15
* CR:
* REV:
* Phase 2C to Phase 3 upgrade plus code review actions and resolving some technical isses.
* 
*    Rev 1.2   24 Oct 1997 11:20:16   tagtrak
* Added PVCS header information
*
****************************************************************************/

PROMPT CREATE OR REPLACE PROCEDURE reuse_pax

CREATE OR REPLACE PROCEDURE reuse_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,
   secsts            IN  CHAR,
   pnr               IN  CHAR,  -- CA 118
   depair            IN  CHAR,
   ifltno            IN  CHAR,
   ifltdate          IN  CHAR,
   ibdg              IN  CHAR,
   atl               IN  CHAR,
   paxfullinits      IN  CHAR,
   onwardtransfer    IN  NUMBER,
   conn_type         IN  CHAR,
   onwd_flt          IN  onward_flight_defs.onwd_flt_t,
   freq_flyer        IN  VARCHAR2,
   paxid             OUT NUMBER
)

AS  
	 v_dcs_boarding       carrier_lookup.dcs_boarding%TYPE ;
   v_paxid 							pax.pax_id%TYPE;
	 v_pax_status     		pax.pax_status%TYPE;
   depcode              pax.departure_airport_code%type := null;

BEGIN
  
  depcode := get_offset.find_airport_code(fltid, v_paxid);

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

	/*
	 * This procedure is called to reuse a pax record where the delete_marker is set, so any
	 * control of the pax_status must behave like add_pax rather than modify_pax.
	 *
	 * If dcs is not boarding then pax must be created with status of C rather than B.
	 */

	if ( v_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_pax_status := 'C' ;
    end if ;   /* } */

   end if ; /* } */


   UPDATE pax
      SET pax_initials             = init,
          pax_surname              = surn,
          pax_status               = v_pax_status,
          time_pax_stored          = SYSDATE + get_offset.airport_code(depcode),
          no_of_bags               = nobags,
          no_same_surname          = TO_NUMBER(nosurn),
          pax_destination          = dest,
          pax_seat_no              = seat,
          pax_class                = class,
		  pax_bags_class		   = class,
          pax_dcs_sequence_no      = dcs_seq_no,
          pax_security_no          = secno,
          pax_security_status      = secsts,
          pax_name_record          = pnr,
          departure_airport_code   = depair,
          inbound_flight_no        = ifltno,
          inbound_date             = ifltdate,
          inbound_boarding_point   = ibdg,
          authority_to_load_dcs    = atl,
          delete_marker            = 0,
          pax_full_inits           = paxfullinits,
          onward_transfer          = onwardtransfer,
          connection_type          = conn_type,
          freq_flyer               = freq_flyer
    WHERE flight_id        = fltid
      AND booked_flight_id = codeshare_fid
      AND pax_sequence_no  = seqno; 

   pax_id2 (fltid, seqno, codeshare_fid, v_paxid);

   paxid := v_paxid;

   /* Updates the pax_bags_class based on the cust_loyalty_program *
    * rules. So pax_bags_class is either equal to pax_class        *
    * or the pax_class has been upgraded to next class level       *
    * as depicted in the cust_loyalty_program set of rules.        */
      tss_upd_pax_bags_class( p_pax_id    => v_paxid
                             ,p_flight_id => fltid);
							 
   /*  add new onwd flight data, deleting old flight data first */
   add_onward_flight(v_paxid, onwd_flt);
 
END reuse_pax;
/
