/*****************************************************************************
*
* Filename         - $Workfile:   TSS_UPD_PAX.sql  $
*
*                     Name         Date       Version     CR Number
*                     Peter Charalambous 01/12/2003             
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 
*
* Design Spec
* Section          -  FAS/DUBRS/SD/245
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
PROMPT CREATE OR REPLACE procedure TSS_UPD_PAX 

CREATE OR REPLACE procedure TSS_UPD_PAX AS
  /* This script needs to be run for the CLP
   * to take affect, since pax_bags_class does
   * not contain any values intially
   * and so WRONG CLASS errors would result
   * since check_bag_for_load has been
   * changed to use pax_bags_class to check
   * whether uld_class and pax_class match
   */
   -- Constant declarations
  l_process_name    oracle_error.process_name%TYPE     := 'TSS_UPD_PAX';
  l_error_msg       oracle_error.errror_msg%TYPE       := NULL;
  l_error_time      oracle_error.error_time%TYPE       := NULL;
  l_error_code      oracle_error.error_code%TYPE       := NULL;

   -- Constant declarations
  DEBUG  CONSTANT VARCHAR2(1)                   := 'Y';
  VALID  CONSTANT VARCHAR2(1)                   := 'Y';

  -- Table types
  TYPE pax_id_tab_type IS TABLE OF pax.pax_id%TYPE
     INDEX BY BINARY_INTEGER;

  TYPE flight_id_tab_type IS TABLE OF pax.flight_id%TYPE
     INDEX BY BINARY_INTEGER;

  TYPE pax_class_tab_type IS TABLE OF pax.pax_class%TYPE
     INDEX BY BINARY_INTEGER;

  TYPE freq_flyer_tab_type IS  TABLE OF pax.freq_flyer%TYPE
     INDEX BY BINARY_INTEGER;

  -- Declare table to contain values
  t_pax_id      pax_id_tab_type;
  t_flight_id   flight_id_tab_type;
  t_pax_class   pax_class_tab_type;
  t_freq_flyer  freq_flyer_tab_type;

  -- Cursor for pax
  CURSOR c_get_pax IS
       SELECT pax.pax_id
             ,pax.flight_id
             ,pax.pax_class
             ,pax.freq_flyer
       FROM  pax
       FOR UPDATE OF pax.pax_bags_class;

BEGIN
  -- update pax set pax_bags_class = null where freq_flyer is null;

  -- do this in bulk
  OPEN c_get_pax;

  -- Loop around until all data has been collected
  LOOP
  FETCH c_get_pax
        BULK COLLECT INTO t_pax_id,
                          t_flight_id,t_pax_class,
                          t_freq_flyer;
  EXIT WHEN c_get_pax%NOTFOUND;
  END LOOP;

  -- Close cursor
  CLOSE c_get_pax;

  -- Update pax
  FORALL indx IN t_pax_id.FIRST..t_pax_id.LAST
      UPDATE pax p
      SET p.pax_bags_class = t_pax_class(indx)
      WHERE p.pax_id = t_pax_id(indx)
      AND   p.flight_id = t_flight_id(indx);

EXCEPTION
   WHEN OTHERS THEN
   /* General catch all error, inserts into Oracle_error table */
       l_error_msg  := SQLERRM;
       l_error_code := SQLCODE;
       l_error_time := SYSDATE;
       INSERT INTO oracle_error( process_name
                                ,errror_msg
                                ,error_code
                                ,error_time )
                         VALUES( l_process_name
                                ,l_error_msg
                                ,l_error_code
                                ,l_error_time);
       IF ( DEBUG = VALID ) THEN
         dbms_output.put_line('------------------------------------');
         dbms_output.put_line('Error Update Not Done : '||l_error_code||'-'||l_error_msg);
       END IF;
     
END tss_upd_pax;
/

