/*****************************************************************************
*
* Filename         - $Workfile:   uld_loading_progress.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, 2000
*
*****************************************************************************/
PROMPT CREATE OR REPLACE PACKAGE uld_loading_progress

CREATE OR REPLACE
PACKAGE uld_loading_progress IS
        time    CHAR(5);
FUNCTION f_check_flight_access
        (p_flight_id            IN      flight.flight_id%TYPE
        ,p_agent_code           IN      agents.agent_code%TYPE
        ) RETURN CHAR;

FUNCTION f_get_first_bag_load1
        (p_uld_no                               IN      ulds.uld_no%TYPE
        ,p_flight_id            IN      flight.flight_id%TYPE
        ) RETURN time%TYPE;

FUNCTION f_get_last_bag_load1
        (p_uld_no                               IN      ulds.uld_no%TYPE
        ,p_flight_id            IN      flight.flight_id%TYPE
        ) RETURN time%TYPE;

FUNCTION f_get_last_bld_trace
        (p_uld_no                               IN      ulds.uld_no%TYPE
        ,p_flight_id            IN      flight.flight_id%TYPE
        ) RETURN time%TYPE;

FUNCTION f_rush_bags
  (p_uld_no       IN  ulds.uld_no%TYPE
  ,p_flight_id    IN  flight.flight_id%TYPE
  ) RETURN NUMBER;

        PRAGMA RESTRICT_REFERENCES(f_check_flight_access, WNDS, WNPS);
        PRAGMA RESTRICT_REFERENCES(f_get_first_bag_load1, WNDS, WNPS);
        PRAGMA RESTRICT_REFERENCES(f_get_last_bag_load1, WNDS, WNPS);
        PRAGMA RESTRICT_REFERENCES(f_get_last_bld_trace, WNDS, WNPS);
        PRAGMA RESTRICT_REFERENCES(f_rush_bags, WNDS, WNPS);
  
END uld_loading_progress;
/
PROMPT CREATE OR REPLACE PACKAGE BODY uld_loading_progress 

CREATE OR REPLACE
PACKAGE BODY uld_loading_progress IS

FUNCTION f_check_flight_access
        (p_flight_id            IN      flight.flight_id%TYPE
        ,p_agent_code           IN      agents.agent_code%TYPE
        ) RETURN CHAR IS
        v_return_code           NUMBER DEFAULT NULL;
        BEGIN
                check_flight_access(p_flight_id, p_agent_code, v_return_code);
                IF v_return_code = 0 THEN
                        RETURN 'Y';
                ELSE
                        RETURN 'N';
                END IF;
        END f_check_flight_access;

FUNCTION f_get_first_bag_load1
        (p_uld_no                               IN      ulds.uld_no%TYPE
        ,p_flight_id            IN      flight.flight_id%TYPE
        ) RETURN time%TYPE IS
        v_time  time%TYPE DEFAULT NULL;
        BEGIN
                get_first_bag_load1(p_uld_no, p_flight_id, v_time);
                RETURN v_time;  
        END f_get_first_bag_load1;

FUNCTION f_get_last_bag_load1
        (p_uld_no                               IN      ulds.uld_no%TYPE
        ,p_flight_id            IN      flight.flight_id%TYPE
        ) RETURN time%TYPE IS
        v_time  time%TYPE DEFAULT NULL;
        BEGIN
                get_last_bag_load1(p_uld_no, p_flight_id, v_time);
                RETURN v_time;  
        END f_get_last_bag_load1;

FUNCTION f_get_last_bld_trace
        (p_uld_no                               IN      ulds.uld_no%TYPE
        ,p_flight_id            IN      flight.flight_id%TYPE
        ) RETURN time%TYPE IS
        v_time  time%TYPE DEFAULT NULL;
        BEGIN
                get_last_bld_trace(p_uld_no, p_flight_id, v_time);
                RETURN v_time;  
        END f_get_last_bld_trace;

-- CA00463 - count all the RUSH bags in a ULD on a flight 
FUNCTION f_rush_bags
  (p_uld_no       IN  ulds.uld_no%TYPE
  ,p_flight_id    IN  flight.flight_id%TYPE
  ) RETURN NUMBER IS
  v_rush_bags NUMBER DEFAULT NULL;
  BEGIN
    SELECT NVL(SUM(DECODE(bag_exception,'RUSH',1,0)),0)
    INTO v_rush_bags
    FROM bag_v2
    WHERE uld_no = p_uld_no
    AND flight_id = p_flight_id;
    
    RETURN v_rush_bags;  
  END f_rush_bags;

END uld_loading_progress;
/
