CREATE OR REPLACE PACKAGE ltr AS

  --Repopulates the loading_totals table for all oytbound flights.
  PROCEDURE maintain_loading_totals;
  
  --Returns the date of the end of the week that the passed in date is in.
  FUNCTION week_end(any_date IN DATE DEFAULT SYSDATE) 
  RETURN DATE;
  
  /*returns the last day of the month the passed in date is in, or the last
  day of a subsequent month if months > 0.
  So if any_date is 02-MAR-2007 you'll get the following results:
  months = 0 -> 31-MAR-2007
  months = 1 -> 30-APR-2007
  months = 5 -> 31-AUG-2007*/
  FUNCTION month_end(any_date IN DATE        DEFAULT SYSDATE,
                     months   IN PLS_INTEGER DEFAULT 0) 
  RETURN DATE;
                     
  /*Returns passed in date if passed in date >= sysdate -5.
  Otherwise returns passed in date plus a year.
  Current_date parameter is not used*/
  FUNCTION correct_year (any_date     IN DATE,
                         current_date IN DATE DEFAULT SYSDATE) 
  RETURN DATE;
  
  
  PRAGMA RESTRICT_REFERENCES (week_end, WNDS, WNPS);
  PRAGMA RESTRICT_REFERENCES (month_end, WNDS, WNPS);
  
END ltr;
/
CREATE OR REPLACE PACKAGE BODY ltr AS

-- CR 15698 change suspect reserved word 'day' to 'p_day' or 'v_day'
FUNCTION valid_week_end (p_day IN CHAR) RETURN BOOLEAN IS
BEGIN
  
  RETURN (p_day IN ('1', '2', '3', '4', '5', '6', '7'));

END valid_week_end;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
FUNCTION week_end_day RETURN PLS_INTEGER IS
  
  v_day CHAR(1) DEFAULT NULL;
  
BEGIN

  SELECT SUBSTR(db_value,1,1)
  INTO v_day
  FROM db_config
  WHERE db_item = 'WEEK_END_DAY';

  IF valid_week_end(v_day) THEN
    RETURN (TO_NUMBER(v_day));
  ELSE
    RETURN 1;
  END IF;
  
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN 1;
    
END week_end_day;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
FUNCTION day_of_week (any_date IN DATE DEFAULT SYSDATE) RETURN PLS_INTEGER IS
BEGIN

  RETURN TO_NUMBER(TO_CHAR(any_date, 'D'));
  
END day_of_week;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
FUNCTION days_offset (any_date IN DATE DEFAULT SYSDATE) RETURN PLS_INTEGER IS
BEGIN

  RETURN (MOD(7 + (week_end_day - day_of_week(any_date)),7));
  
END days_offset;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
FUNCTION week_end (any_date IN DATE DEFAULT SYSDATE) RETURN DATE IS
BEGIN

  RETURN (any_date + days_offset(any_date));
  
END week_end;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
FUNCTION month_end(any_date IN DATE        DEFAULT SYSDATE,
                   months   IN PLS_INTEGER DEFAULT 0) 
RETURN DATE IS
  
  l_ret_date     DATE;
  l_months_added NUMBER := 1 + nvl(months,0);
  
BEGIN

  /*trunc sets the date to 1st day of month passed in. Use add months to take 
  to the 1st day of the following month (or later if the months parameter is 
  grater than 0) and then subtract 1 day to take it to the end of the current month.*/
  l_ret_date := (add_months(trunc(any_date, 'month'), l_months_added)) -1;
  
  RETURN l_ret_date;

END month_end;

------------------------------------------------------------------------------
------------------------------------------------------------------------------

PROCEDURE bags_loaded_count (flight_id_in IN     NUMBER,
                             rush            OUT NUMBER,
                             gate            OUT NUMBER,
                             crew            OUT NUMBER,
                             local           OUT NUMBER,
                             transfer        OUT NUMBER,
                             other           OUT NUMBER,
                             deleted         OUT NUMBER,
                             mail            OUT NUMBER,
                             dipl            OUT NUMBER) AS

  CURSOR loaded_bags IS
  SELECT b.delete_marker,
         bag_exception,
         pax_status,
         bag_source_indicator
  FROM bag b,
       pax p
  WHERE flight_id = flight_id_in
  AND b.pax_id = p.pax_id
  AND loaded_status = 'Y';

BEGIN

  rush := 0;
  gate := 0;
  crew := 0;
  mail := 0;
  dipl := 0;
  local := 0;
  transfer := 0;
  other := 0;
  deleted := 0;

  FOR loaded_bag_rec IN loaded_bags LOOP

    IF loaded_bag_rec.delete_marker = 0 THEN

      IF loaded_bag_rec.pax_status IN ('C', 'B') THEN

        IF loaded_bag_rec.bag_exception = 'RUSH' THEN
          rush := rush + 1;
        ELSIF loaded_bag_rec.bag_exception = 'GATE' THEN
          gate := gate + 1;
        ELSIF loaded_bag_rec.bag_exception = 'CREW' THEN
          crew := crew + 1;
        ELSIF loaded_bag_rec.bag_exception = 'MAIL' THEN
          mail := mail + 1;
        ELSIF loaded_bag_rec.bag_exception = 'DIPL' THEN
          dipl := dipl + 1;
        ELSE
        
          IF loaded_bag_rec.bag_source_indicator = 'L' THEN
            local := local + 1;
          END IF;
        
          IF loaded_bag_rec.bag_source_indicator = 'T' THEN
            transfer := transfer + 1;
          END IF;
        
        END IF;
        
      ELSE
        other := other + 1;
      END IF;
      
    ELSE
      deleted := deleted + 1;
    END IF;
    
  END LOOP;
  
END bags_loaded_count;

------------------------------------------------------------------------------
------------------------------------------------------------------------------

FUNCTION correct_year (any_date IN DATE,
                       current_date IN DATE DEFAULT SYSDATE)
  RETURN DATE IS

  today          DATE;
  check_date     DATE;
  previous_days  INTEGER := 5;

BEGIN

  today      := SYSDATE;
  check_date := today - previous_days;

  IF any_date < check_date THEN

    RETURN(ADD_MONTHS(any_date, +12));

  ELSE

    RETURN any_date;

  END IF;

END correct_year;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
PROCEDURE insert_loading_totals (flight_id_in    IN NUMBER,
                                 flight_no_in    IN CHAR,
                                 flight_date_in  IN CHAR) IS

  dummy          NUMBER(38) DEFAULT NULL;
  bags_loaded    NUMBER(38) DEFAULT 0;
  bags_short     NUMBER(38) DEFAULT 0;
  --bags_normal    NUMBER(38) DEFAULT 0;
  bags_rush      NUMBER(38) DEFAULT 0;
  bags_gate      NUMBER(38) DEFAULT 0;
  bags_crew      NUMBER(38) DEFAULT 0;
  bags_local     NUMBER(38) DEFAULT 0;
  bags_transfer  NUMBER(38) DEFAULT 0;
  bags_other     NUMBER(38) DEFAULT 0;
  bags_deleted   NUMBER(38) DEFAULT 0;
  bags_mail      NUMBER(38) DEFAULT 0;
  bags_dipl      NUMBER(38) DEFAULT 0;

BEGIN

  pax_and_bag_count(flight_id_in,
                    dummy, dummy, dummy, dummy,
                    bags_loaded,
                    bags_short,
                    dummy, dummy, dummy, dummy, dummy, dummy,
                    dummy, dummy, dummy, dummy, dummy, dummy);
                    
  bags_loaded_count(flight_id_in,
                    bags_rush,
                    bags_gate,
                    bags_crew,
                    bags_local,
                    bags_transfer,
                    bags_other,
                    bags_deleted,
                    bags_mail,
                    bags_dipl);
                    
  INSERT INTO loading_totals
            (flight_date
            ,carrier
            ,alpha_code
            ,flight_no
            ,loaded
            ,transfer
            ,local
            ,rush
            ,other
            ,short
            ,deleted
            )
  VALUES
            (TRUNC(ltr.correct_year(TO_DATE(flight_date_in, 'DDMON')))
            ,SUBSTR(carrier_name(flight_no_in),1,24)
            ,SUBSTR(flight_no_in,1,2)
            ,flight_no_in
            ,bags_loaded
            ,bags_transfer
            ,bags_local
            ,bags_rush
            ,nvl(bags_gate,0) + nvl(bags_crew,0) + nvl(bags_other,0) + nvl(bags_mail,0) + nvl(bags_dipl,0)
            ,bags_short
            ,bags_deleted
            );
  
END insert_loading_totals;

------------------------------------------------------------------------------
------------------------------------------------------------------------------

PROCEDURE maintain_loading_totals IS

  CURSOR flights IS
  SELECT f.flight_id,
         f.flight_no,
         f.flight_date
  FROM flight f
  WHERE f.flight_id > 0
  -- CA/2764 - don't process flights that already exist in ARCH
  AND NOT EXISTS 
  ( 
    SELECT * 
    FROM arch.flight f2
    WHERE f2.flight_no=f.flight_no
    AND f2.flight_date=f.flight_date
    AND substrb(f2.departure_time, 1, 8) = substrb(f.departure_time, 1, 8)
  ) ;

BEGIN

  FOR flight_rec IN flights LOOP
  
    DELETE FROM loading_totals
    WHERE TRUNC(ltr.correct_year(TO_DATE(flight_rec.flight_date, 'DDMON'))) = TRUNC(flight_date)
    AND flight_rec.flight_no = flight_no;

    insert_loading_totals(flight_rec.flight_id,
                          flight_rec.flight_no,
                          flight_rec.flight_date);
                          
  END LOOP;
  
END maintain_loading_totals;
------------------------------------------------------------------------------
------------------------------------------------------------------------------
END ltr;
/
