/*****************************************************************************
*
* Filename         - $Workfile:   add_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:   add_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/add_pax.sqv  $
* 
*    Rev 1.9   12 Sep 2002 17:25:18   tagtrak
* OR:
* CR: 15644
* DESC: BA BRS Segregation changes
* 
*    Rev 1.8   01 Aug 2002 12:29:52   yomi
* DESC: Timezone change
* CR:   15508
* 
*    Rev 1.7   31 Jan 2000 14:49:22   tagtrak
* OR:
* CR: 14402
* REV: 
* San Francisco Core Product Merge
* Neil Hilier
* 
*    Rev 1.6   08 Apr 1999 15:21:06   tagtrak
* OR: 13206
* CR:  
* REV:
* Andrew Walker
* (branch build revision copied to trunk.)
* Neil Bishop
* 
*    Rev 1.5.1.0   25 Feb 1999 15:22:38   tagtrak
* OR: 13206
* CR:  
* REV:
* Andrew Walker
* 
*    Rev 1.5   25 Sep 1998 10:02:04   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.2.0   18 Aug 1998 09:34:04   tagtrak
* OR: 12484
* CR: 12484
* REV:
* Merging Vienna (0.3.1E) and Core Product (0.2.5E).
* Andrew Walker
* 
*    Rev 1.4.1.0   10 Jun 1998 17:31:58   tagtrak
* OR:
* CR: 12098
* REV:
* Vienna Mods
* 
*    Rev 1.4   02 Apr 1998 19:31:34   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.3   15 Jan 1998 12:10:56   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 10:59:44   tagtrak
* Added PVCS header information
*
****************************************************************************/
PROMPT CREATE OR REPLACE PROCEDURE add_pax

CREATE OR REPLACE PROCEDURE add_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,
   dcsseqno        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,
   con_type        IN   CHAR,
   onwd_flt        IN   onward_flight_defs.onwd_flt_t,
   freq_flyer      IN   pax.freq_flyer%TYPE, 
	 BAG_EXCEPTION IN   bag.bag_exception%TYPE,
	 embassy       IN   pax.embassy%TYPE,
   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;
   v_pid    pax.pax_id%type;
   offset   number;
   dep      pax.departure_airport_code%type := null;
   inbound_fltid flight.flight_id%type;

BEGIN

      dep := get_offset.find_airport_code(fltid,v_pid);
      offset := get_offset.airport_code(dep);

   /*
		* - Control pax status if dcs is not boarding
    * - Insert a record into the pax table using the parameters supplied
    * - call the pax_id procedure
    */

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

	/*
	 * 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 ; /* } */

    INSERT
      INTO pax
            (pax_id,
             flight_id,
             booked_flight_id,
             pax_initials,
             pax_surname,
             pax_status,
             time_pax_stored,
             no_of_bags,
             no_same_surname,
             pax_destination,
             pax_seat_no,
             pax_class,
             pax_sequence_no,
             pax_dcs_sequence_no,
             pax_security_no,
             pax_name_record,
             pax_security_status,
             departure_airport_code,
             inbound_flight_no,
             inbound_date,
             inbound_boarding_point,
             authority_to_load_dcs,
             pax_full_inits,
             onward_transfer,
             connection_type,
             freq_flyer,
             embassy,
             exception_type_id)
    VALUES (0,
           fltid,
           codeshare_fid,
           init,
           surn,
           v_pax_status,
           SYSDATE + offset,
           nobags,
           nosurn,
           dest,
           seat,
           class,
           seqno,
           dcsseqno,
           secno,
           pnr,
           secsts,
           depair,
           ifltno,
           ifltdate,
           ibdg,
           atl,
           pax_full_inits,
           onward_transfer,
           con_type,
           freq_flyer,  
           embassy,
           exception_bags.get_bag_exception_type_id(bag_exception) );

   pax_id2 (fltid, seqno, NVL(codeshare_fid, 0), 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_onward_flight(v_paxid, onwd_flt); 

  -- if an inbound flight specified for this pax create the flight record with status=IB and no dests
   inbound_flight_utils.add_inbound_flight (fltno => ifltno,
                         fltdate => ifltdate,
                         ibdg => ibdg,
                         outbound_flight_id => fltid,
                         inbound_fltid => inbound_fltid);
 
END add_pax;
/
