CREATE OR REPLACE PACKAGE ulds_pkg AS

  type cur_get_ulds_details_rec_type is record (
		uld_dest    char(3),
		class_list  char(10),
		no_of_bags  number,
                onwards_flt varchar2(8),
                onwards_date varchar2(5)
	);

  type cur_get_ulds_details_type is ref cursor return
		cur_get_ulds_details_rec_type;

/******************************************************************************
 *
 * PROCEDURE get_ulds_info
 *
 * Description:
 *   This procedure returns uld informnation for the supplied uld number or 
 *   code 
 *  
 * Parameters:
 *   p_uld_no       - ULD number   
 *   p_uld_id_code  - ULD code
 *   p_user_id      - the user requesting the information
 *   p_flight_id    - flight identifier
 *   p_flight_no    - flight number
 *   p_flight_date  - flight date
 *   p_uld_category - uld category
 *   p_uld_type     - uld type
 *   p_dest1        - first destination
 *   p_class1       - first destination class
 *   p_dest2        - second destination
 *   p_class2       - second destination class
 *   p_dest3        - third destination
 *   p_class3       - third destination class
 *   p_dest4        - fourth destination
 *   p_class4       - fourth destination class
 *   p_dest5        - fifth destination
 *   p_class5       - fifth destination class
 *   p_dest6        - sixth destination
 *   p_class6       - sicth destination class
 *   p_uld_status   - uld status
 *   p_retcode      - return code (0 ==> no error)
 *
 ******************************************************************************/

PROCEDURE get_uld_info
   (p_uld_no       IN OUT uld_recorded_id_codes.uld_no%TYPE,
    p_uld_id_code  IN OUT uld_recorded_id_codes.uld_id_code%TYPE,
    p_user_id      IN     ulds.user_id%TYPE,
    p_flight_id       OUT ulds.flight_id%TYPE,
    p_flight_no       OUT flight.flight_no%TYPE,
    p_flight_date     OUT uld_recorded_id_codes.flight_date%TYPE,
    p_uld_category    OUT uld_recorded_id_codes.uld_category%TYPE,
    p_uld_type        OUT uld_recorded_id_codes.uld_type%TYPE,
    p_dest1           OUT uld_recorded_id_codes.dest1%TYPE,
    p_class1          OUT uld_recorded_id_codes.class1%TYPE,
    p_dest2           OUT uld_recorded_id_codes.dest2%TYPE,
    p_class2          OUT uld_recorded_id_codes.class2%TYPE,
    p_dest3           OUT uld_recorded_id_codes.dest3%TYPE,
    p_class3          OUT uld_recorded_id_codes.class3%TYPE,
    p_dest4           OUT uld_recorded_id_codes.dest4%TYPE,
    p_class4          OUT uld_recorded_id_codes.class4%TYPE,
    p_dest5           OUT uld_recorded_id_codes.dest5%TYPE,
    p_class5          OUT uld_recorded_id_codes.class5%TYPE,
    p_dest6           OUT uld_recorded_id_codes.dest6%TYPE,
    p_class6          OUT uld_recorded_id_codes.class6%TYPE,
    p_uld_status      OUT INTEGER,
    p_retcode         OUT INTEGER);

/******************************************************************************
 *
 * PROCEDURE get_ulds_details
 *
 * Description:
 *   This procedure returns details for the supplied ULD on the supplid flight 
 *   including destination, class, number of bags and onward flight information.
 *  
 * Parameters:
 *    p_uld_no - ULD number
 *    fltid    - flight identifier
 *    cur_0    - cursor used to seelct the information
 *
 ******************************************************************************/

PROCEDURE get_ulds_details ( p_uld_no IN OUT char,
	                     fltid    IN OUT number,
	                     cur_0    IN OUT cur_get_ulds_details_type
);

/******************************************************************************
 *
 * FUNCTION first_dest
 *
 * Description:
 *   Gets the first destination for a given ULD
 *
 * Parameters:
 *   p_flight_id - flight id of flight ULD is on
 *   p_uld_no - ULD Number.
 *
 * Returns: uld destination
 *
 ******************************************************************************/
FUNCTION first_dest(p_flight_id  ulds.flight_id%TYPE,
                    p_uld_no     ulds.uld_no%TYPE)
RETURN uld_dests.uld_destination%TYPE;

/******************************************************************************
 *
 * FUNCTION first_class
 *
 * Description:
 *   Gets the class list at the first destination for a given ULD
 *
 * Parameters: as first_dest above
 *
 * Returns: uld class list
 *
 ******************************************************************************/

FUNCTION first_class(p_flight_id  ulds.flight_id%TYPE,
                     p_uld_no     ulds.uld_no%TYPE)
RETURN uld_dests.uld_class_list%TYPE;

/******************************************************************************
 *
 * FUNCTION first_dest
 *
 * Description:
 *   Gets the first destination for a given ULD that doesn't correspond to a
 *   destination for the flight the ULD is currently on.
 *
 * Parameters: as first_dest above
 *
 * Returns: uld destination
 *
 ******************************************************************************/

FUNCTION first_onward_dest(p_flight_id  ulds.flight_id%TYPE,
                           p_uld_no     ulds.uld_no%TYPE)
RETURN uld_dests.uld_destination%TYPE;

/******************************************************************************
 *
 * PROCEDURE close_uld
 *
 * Description:
 *   Closes a ULD and updates the IATA Unit Load volume Code if the carrier
 *   is set up to use it.
 *
 * In parameters:
 *   p_flight_id          - the id of the flight to which the ULD is assigned.
 *   p_uld_no             - the ULD number of the ULD to close.
 *   p_loc                - the user's current location.
 *   p_volume             - the IATA Unit Load Volume Code (0-3)
 *   p_user_id            - the id of the user closing the ULD.
 *
 * Out parameters:
 *   p_retcode            - return code (0 if procedure exectued without error)
 *
 ******************************************************************************/
PROCEDURE close_uld
(      p_flight_id   IN  number
     , p_uld_no      IN  char
     , p_loc         IN  char
     , p_volume      IN  number
     , p_user_id     IN  tagtrak_users.user_id%TYPE
     , p_retcode     OUT number
);

/******************************************************************************
 *
 * PROCEDURE reopen_uld
 *
 * Description:
 *   Opens a ULD.
 *
 * In parameters:
 *   p_flight_id          - the id of the flight to which the ULD is assigned.
 *   p_uld_no             - the ULD number of the ULD to close.
 *   p_loc                - the user's current location.
 *   p_user_id            - the id of the user closing the ULD.
 *
 * Out parameters:
 *   retcode              - return code (0 if procedure exectued without error)
 *
 ******************************************************************************/
PROCEDURE reopen_uld
(      p_flight_id   IN  number
     , p_uld_no      IN  char
     , p_loc         IN  char
     , p_user_id     IN  tagtrak_users.user_id%TYPE
     , retcode       OUT number
);

/******************************************************************************
 *
 * FUNCTION get_onward_uld_dest
 *
 * Description:
 *   Gets the onward destination for a given ULD
 *
 * Parameters:
 *   p_flight_id - flight id of flight ULD is on
 *   p_uld_no - ULD Number.
 *   p_dest_order - the destination out of the sequence of destinations.
 *
 * Returns: uld destination
 *
 ******************************************************************************/
FUNCTION get_onward_uld_dest(p_uld_no IN ulds.uld_no%TYPE
                            ,p_flight_id IN flight.flight_id%TYPE
                            ,p_dest_order IN uld_dests.dest_order%TYPE)
RETURN VARCHAR2; 

/******************************************************************************
 *
 * FUNCTION get_onward_uld_flight_no
 *
 * Description:
 *   Gets the onward flight number for a given ULD
 *
 * Parameters:
 *   p_flight_id - flight id of flight ULD is on
 *   p_uld_no - ULD Number.
 *   p_dest_order - the flight number out of the sequence of onward flight numbers.
 *
 * Returns: uld onward flight number
 *
 ******************************************************************************/
FUNCTION get_onward_uld_flight_no(p_uld_no IN ulds.uld_no%TYPE
                                 ,p_flight_id IN flight.flight_id%TYPE
                                 ,p_dest_order IN uld_dests.dest_order%TYPE)
RETURN VARCHAR2; 

END ulds_pkg;
/

