PROMPT CREATE OR REPLACE PACKAGE uld_cards

CREATE OR REPLACE PACKAGE uld_cards AS
/******************************************************************************
 *
 * Public Type Definitions
 *
 ******************************************************************************/

   -- Define the types of record sets (for the workstation)
   TYPE flight_no_type          IS TABLE OF uld_card_templates.flight_no%TYPE         INDEX BY BINARY_INTEGER;
   TYPE flight_date_type        IS TABLE OF uld_card_templates.flight_date%TYPE       INDEX BY BINARY_INTEGER;
   TYPE bar_codes_type          IS TABLE OF uld_card_templates.bar_codes%TYPE         INDEX BY BINARY_INTEGER;
   TYPE card_id_type            IS TABLE OF uld_card_details.card_id%TYPE             INDEX BY BINARY_INTEGER;
   TYPE dest_list_type          IS TABLE OF uld_card_details.dest1%TYPE               INDEX BY BINARY_INTEGER;
   TYPE class_list_type         IS TABLE OF uld_card_details.class1%TYPE              INDEX BY BINARY_INTEGER;
   TYPE onward_flight_no_type   IS TABLE OF uld_card_details.onward_flight_no1%TYPE   INDEX BY BINARY_INTEGER;
   TYPE onward_flight_date_type IS TABLE OF uld_card_details.onward_flight_date1%TYPE INDEX BY BINARY_INTEGER;
   TYPE dest1_type              IS TABLE OF uld_card_details.dest1%TYPE               INDEX BY BINARY_INTEGER;
   TYPE dest2_type              IS TABLE OF uld_card_details.dest2%TYPE               INDEX BY BINARY_INTEGER;
   TYPE dest3_type              IS TABLE OF uld_card_details.dest3%TYPE               INDEX BY BINARY_INTEGER;
   TYPE dest4_type              IS TABLE OF uld_card_details.dest4%TYPE               INDEX BY BINARY_INTEGER;
   TYPE dest5_type              IS TABLE OF uld_card_details.dest5%TYPE               INDEX BY BINARY_INTEGER;
   TYPE dest6_type              IS TABLE OF uld_card_details.dest6%TYPE               INDEX BY BINARY_INTEGER;
   TYPE class1_type             IS TABLE OF uld_card_details.class1%TYPE              INDEX BY BINARY_INTEGER;
   TYPE class2_type             IS TABLE OF uld_card_details.class2%TYPE              INDEX BY BINARY_INTEGER;
   TYPE class3_type             IS TABLE OF uld_card_details.class3%TYPE              INDEX BY BINARY_INTEGER;
   TYPE class4_type             IS TABLE OF uld_card_details.class4%TYPE              INDEX BY BINARY_INTEGER;
   TYPE class5_type             IS TABLE OF uld_card_details.class5%TYPE              INDEX BY BINARY_INTEGER;
   TYPE class6_type             IS TABLE OF uld_card_details.class5%TYPE              INDEX BY BINARY_INTEGER;
   TYPE loaded_at_type          IS TABLE OF uld_card_details.loaded_at%TYPE           INDEX BY BINARY_INTEGER;
   TYPE transfer_at_type        IS TABLE OF uld_card_details.transfer_at%TYPE         INDEX BY BINARY_INTEGER;
   TYPE uld_category_type       IS TABLE OF uld_card_details.uld_category%TYPE        INDEX BY BINARY_INTEGER;
   TYPE uld_type_type           IS TABLE OF uld_card_details.uld_type%TYPE            INDEX BY BINARY_INTEGER;
   TYPE onward_flight_no1_type   IS TABLE OF uld_card_details.onward_flight_no1%TYPE   INDEX BY BINARY_INTEGER;
   TYPE onward_flight_date1_type IS TABLE OF uld_card_details.onward_flight_date1%TYPE INDEX BY BINARY_INTEGER;
   TYPE onward_flight_no2_type   IS TABLE OF uld_card_details.onward_flight_no2%TYPE   INDEX BY BINARY_INTEGER;
   TYPE onward_flight_date2_type IS TABLE OF uld_card_details.onward_flight_date2%TYPE INDEX BY BINARY_INTEGER;
   TYPE onward_flight_no3_type   IS TABLE OF uld_card_details.onward_flight_no3%TYPE   INDEX BY BINARY_INTEGER;
   TYPE onward_flight_date3_type IS TABLE OF uld_card_details.onward_flight_date3%TYPE INDEX BY BINARY_INTEGER;
   TYPE onward_flight_no4_type   IS TABLE OF uld_card_details.onward_flight_no4%TYPE   INDEX BY BINARY_INTEGER;
   TYPE onward_flight_date4_type IS TABLE OF uld_card_details.onward_flight_date4%TYPE INDEX BY BINARY_INTEGER;
   TYPE onward_flight_no5_type   IS TABLE OF uld_card_details.onward_flight_no5%TYPE   INDEX BY BINARY_INTEGER;
   TYPE onward_flight_date5_type IS TABLE OF uld_card_details.onward_flight_date5%TYPE INDEX BY BINARY_INTEGER;
   TYPE onward_flight_no6_type   IS TABLE OF uld_card_details.onward_flight_no6%TYPE   INDEX BY BINARY_INTEGER;
   TYPE onward_flight_date6_type IS TABLE OF uld_card_details.onward_flight_date6%TYPE INDEX BY BINARY_INTEGER;
   TYPE contents_remarks_type   IS TABLE OF uld_card_details.contents_remarks%TYPE    INDEX BY BINARY_INTEGER;
   TYPE remarks_type            IS TABLE OF uld_card_details.remarks%TYPE             INDEX BY BINARY_INTEGER;
   TYPE quantity_type           IS TABLE OF uld_card_details.quantity%TYPE            INDEX BY BINARY_INTEGER;

--Record Type used by ULD_Cards_Report.
TYPE uld_cards_rep_rec IS RECORD
(uld_id_code           uld_recorded_id_codes.uld_id_code%TYPE,
 uld_id_code_bc        VARCHAR2(50),
 uld_no                uld_recorded_id_codes.uld_no%TYPE,
 dest                  uld_recorded_id_codes.dest1%TYPE,
 dest_bc               VARCHAR2(50),
 loaded_at             uld_card_details.loaded_at%TYPE,
 loaded_at_flight      uld_recorded_id_codes.flight_no%TYPE,
 loaded_at_flight_bc   VARCHAR2(50),
 transfer_at           uld_card_details.transfer_at%TYPE,
 transfer_at_bc        VARCHAR2(50),
 transfer_at_flight    uld_recorded_id_codes.onward_flight_no1%TYPE,
 transfer_at_flight_bc VARCHAR2(50),
 uld_class             uld_recorded_id_codes.class1%TYPE,
 uld_category          uld_recorded_id_codes.uld_category%TYPE,
 uld_type              uld_recorded_id_codes.uld_type%TYPE,
 flight_date           uld_recorded_id_codes.flight_date%TYPE,
 contents_remarks      uld_recorded_id_codes.contents_remarks%TYPE,
 remarks               uld_recorded_id_codes.remarks%TYPE,
 onward_flight_no_1    uld_recorded_id_codes.onward_flight_no1%TYPE,
 onward_flight_dest_1  uld_recorded_id_codes.dest1%TYPE,
 onward_flight_no_2    uld_recorded_id_codes.onward_flight_no2%TYPE,
 onward_flight_dest_2  uld_recorded_id_codes.dest2%TYPE,
 onward_flight_no_3    uld_recorded_id_codes.onward_flight_no3%TYPE,
 onward_flight_dest_3  uld_recorded_id_codes.dest3%TYPE,
 onward_flight_no_4    uld_recorded_id_codes.onward_flight_no4%TYPE,
 onward_flight_dest_4  uld_recorded_id_codes.dest4%TYPE,
 onward_flight_no_5    uld_recorded_id_codes.onward_flight_no5%TYPE,
 onward_flight_dest_5  uld_recorded_id_codes.dest5%TYPE,
 onward_flight_no_6    uld_recorded_id_codes.onward_flight_no6%TYPE,
 onward_flight_dest_6  uld_recorded_id_codes.dest6%TYPE,
 uld_class1            VARCHAR2(1),
 uld_class2            VARCHAR2(1),
 uld_class3            VARCHAR2(1),
 uld_class4            VARCHAR2(1),
 uld_class5            VARCHAR2(1),
 uld_class_desc1       VARCHAR2(50),
 uld_class_desc2       VARCHAR2(50),
 uld_class_desc3       VARCHAR2(50),
 uld_class_desc4       VARCHAR2(50),
 uld_class_desc5       VARCHAR2(50),
 type_desc             VARCHAR2(50),
 sorting_destination   flight_info_extras.box%TYPE);

--Ref Cursor Used By ULD_Cards_Report.
TYPE uld_cards_ref_cur IS REF CURSOR RETURN uld_cards_rep_rec;

   abort             EXCEPTION;


/******************************************************************************
 *
 * PROCEDURE RECALL_ULD_CARD_TEMPLATE
 *
 * Description:
 *   This procedure gets and returns the ULD_CARD_TEMPLATES data from the
 *   database for a given template name and agent.
 *
 * Parameters:
 *   p_template_name      - Name given to the ULD card template.
 *   p_agent              - Name of the agent that owns the ULD card template.
 *   p_flight_no          - Flight name for the departing flight (e.g. QF 123).
 *   p_flight_date        - Scheduled date/time of the flight.
 *   p_bar_codes          - Determines whether bar codes are to be printed.
 *
 * Errors raised:
 *   -20000 : Recalled template does not exist.
 *
 ******************************************************************************/

PROCEDURE recall_uld_card_template
   (p_template_name      IN  uld_card_templates.template_name%TYPE,
    p_agent              IN  uld_card_templates.agent%TYPE,
    p_flight_no          OUT uld_card_templates.flight_no%TYPE,
    p_flight_date        OUT uld_card_templates.flight_date%TYPE,
    p_bar_codes          OUT uld_card_templates.bar_codes%TYPE);

/******************************************************************************
 *
 * PROCEDURE RECALL_ULD_CARD_DETAILS
 *
 * Description:
 *   This procedure gets and returns the ULD_CARD_TEMPLATES data from the
 *   database for a given template name and agent.
 *
 * Parameters:
 *   p_template_name      - Name given to the ULD card template.
 *   p_agent              - Name of the agent that owns the ULD card template.
 *   p_uld_category       - IATA standard describing the contents of the ULD.
 *   p_uld_type           - UltraTrak's ULD type (L, T or M).
 *   p_quantity           - Number of cards to print.
 *   p_dest1              - IATA code of the final destination.
 *   p_class1             - Class of the ULD for destination 1.
 *   p_dest2              - IATA code for the ULD airport 2.
 *   p_class2             - Class of the ULD for destination 2.
 *   p_dest3              - IATA code for the ULD airport 3.
 *   p_class3             - Class of the ULD for destination 3.
 *   p_dest4              - IATA code for the ULD airport 4.
 *   p_class4             - Class of the ULD for destination 4.
 *   p_dest5              - IATA code for the ULD airport 5.
 *   p_class5             - Class of the ULD for destination 5.
 *   p_dest6              - IATA code for the ULD airport 6
 *   p_class6             - Class of the ULD for destination 6.
 *   p_loaded_at          - IATA code for the airport where the ULD was loaded.
 *   p_transfer_at        - IATA code for the transfer airport (if any).
 *   p_card_id            - Internal ID (sequence) of the card.
 *   p_contents_remarks   - Additional free format details (e.g. "PRIORITY").
 *   p_remarks            - Additional details or remarks.
 *   p_onward_flight_no1  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date1- Onward scheduled date/time of the flight.
 *   p_onward_flight_no2  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date2- Onward scheduled date/time of the flight.
 *   p_onward_flight_no3  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date3- Onward scheduled date/time of the flight.
 *   p_onward_flight_no4  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date4- Onward scheduled date/time of the flight.
 *   p_onward_flight_no5  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date5- Onward scheduled date/time of the flight.
 *   p_onward_flight_no6  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date6- Onward scheduled date/time of the flight.
 *
 * Errors raised:
 *   -20000 : Recalled template does not exist.
 *
 ******************************************************************************/

PROCEDURE recall_uld_card_details
   (p_template_name      IN  uld_card_templates.template_name%TYPE,
    p_agent              IN  uld_card_templates.agent%TYPE,
    p_uld_category       OUT uld_category_type,
    p_uld_type           OUT uld_type_type,
    p_quantity           OUT quantity_type,
    p_dest1              OUT dest1_type,
    p_class1             OUT class1_type,
    p_dest2              OUT dest2_type,
    p_class2             OUT class2_type,
    p_dest3              OUT dest3_type,
    p_class3             OUT class3_type,
    p_dest4              OUT dest4_type,
    p_class4             OUT class4_type,
    p_dest5              OUT dest5_type,
    p_class5             OUT class5_type,
    p_dest6              OUT dest6_type,
    p_class6             OUT class6_type,
    p_loaded_at          OUT loaded_at_type,
    p_transfer_at        OUT transfer_at_type,
    p_card_id            OUT card_id_type,
    p_contents_remarks   OUT contents_remarks_type,
    p_remarks            OUT remarks_type,
    p_onward_flight_no1   OUT onward_flight_no1_type,
    p_onward_flight_date1 OUT onward_flight_date1_type,
    p_onward_flight_no2   OUT onward_flight_no2_type,
    p_onward_flight_date2 OUT onward_flight_date2_type,
    p_onward_flight_no3   OUT onward_flight_no3_type,
    p_onward_flight_date3 OUT onward_flight_date3_type,
    p_onward_flight_no4   OUT onward_flight_no4_type,
    p_onward_flight_date4 OUT onward_flight_date4_type,
    p_onward_flight_no5   OUT onward_flight_no5_type,
    p_onward_flight_date5 OUT onward_flight_date5_type,
    p_onward_flight_no6   OUT onward_flight_no6_type,
    p_onward_flight_date6 OUT onward_flight_date6_type
);

/******************************************************************************
 *
 * PROCEDURE SAVE_ULD_CARD_TEMPLATE
 *
 * Description:
 *   This procedure saves the ULD_CARD_TEMPLATES data in the database. It
 *   first attempts an UPDATE statement then an INSERT statement (if the update
 *   fails).
 *
 * Parameters:
 *   p_template_name      - Name given to the ULD card template.
 *   p_agent              - Name of the agent that owns the ULD card template.
 *   p_flight_no          - Flight name for the departing flight (e.g. QF 123).
 *   p_flight_date        - Scheduled date/time of the flight.
 *   p_bar_codes          - Determines whether bar codes are to be printed.
 *
 * Errors raised:
 *   None.
 *
 ******************************************************************************/

PROCEDURE save_uld_card_template
   (p_template_name      IN uld_card_templates.template_name%TYPE,
    p_agent              IN uld_card_templates.agent%TYPE,
    p_flight_no          IN uld_card_templates.flight_no%TYPE,
    p_flight_date        IN uld_card_templates.flight_date%TYPE,
    p_bar_codes          IN uld_card_templates.bar_codes%TYPE);

/******************************************************************************
 *
 * PROCEDURE SAVE_ULD_CARD_DETAILS
 *
 * Description:
 *   This procedure saves the ULD_CARD_DETAILS data in the database. It
 *   first attempts an UPDATE statement then an INSERT statement (if the update
 *   fails).
 *
 * Parameters:
 *   p_template_name      - Name given to the ULD card template.
 *   p_agent              - Name of the agent that owns the ULD card template.
 *   p_uld_category       - IATA standard describing the contents of the ULD.
 *   p_uld_type           - UltraTrak's ULD type (L, T or M).
 *   p_dest1              - IATA code of the final destination.
 *   p_class1             - Class of the ULD for destination 1.
 *   p_dest2              - IATA code for the ULD airport 2.
 *   p_class2             - Class of the ULD for destination 2.
 *   p_dest3              - IATA code for the ULD airport 3.
 *   p_class3             - Class of the ULD for destination 3.
 *   p_dest4              - IATA code for the ULD airport 4.
 *   p_class4             - Class of the ULD for destination 4.
 *   p_dest5              - IATA code for the ULD airport 5.
 *   p_class5             - Class of the ULD for destination 5.
 *   p_dest6              - IATA code for the ULD airport 6.
 *   p_class6             - Class of the ULD for destination 6.
 *   p_quantity           - Number of cards to print.
 *   p_loaded_at          - IATA code for the airport where the ULD was loaded.
 *   p_transfer_at        - IATA code for the transfer airport (if any).
 *   p_card_id            - Internal ID (sequence) of the card.
 *   p_contents_remarks   - Additional free format details (e.g. "PRIORITY").
 *   p_remarks            - Additional details or remarks.
 *   p_onward_flight_no1  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date1- Onward scheduled date/time of the flight.
 *   p_onward_flight_no2  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date2- Onward scheduled date/time of the flight.
 *   p_onward_flight_no3  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date3- Onward scheduled date/time of the flight.
 *   p_onward_flight_no4  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date4- Onward scheduled date/time of the flight.
 *   p_onward_flight_no5  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date5- Onward scheduled date/time of the flight.
 *   p_onward_flight_no6  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date6- Onward scheduled date/time of the flight.
 *
 * Errors raised:
 *   None.
 *
 ******************************************************************************/

PROCEDURE save_uld_card_details
   (p_template_name      IN uld_card_details.template_name%TYPE,
    p_agent              IN uld_card_details.agent%TYPE,
    p_uld_category       IN uld_card_details.uld_category%TYPE,
    p_uld_type           IN uld_card_details.uld_type%TYPE,
    p_dest1              IN uld_card_details.dest1%TYPE,
    p_class1             IN uld_card_details.class1%TYPE,
    p_dest2              IN uld_card_details.dest2%TYPE,
    p_class2             IN uld_card_details.class2%TYPE,
    p_dest3              IN uld_card_details.dest3%TYPE,
    p_class3             IN uld_card_details.class3%TYPE,
    p_dest4              IN uld_card_details.dest4%TYPE,
    p_class4             IN uld_card_details.class4%TYPE,
    p_dest5              IN uld_card_details.dest5%TYPE,
    p_class5             IN uld_card_details.class5%TYPE,
    p_dest6              IN uld_card_details.dest6%TYPE,
    p_class6             IN uld_card_details.class6%TYPE,
    p_quantity           IN uld_card_details.quantity%TYPE,
    p_loaded_at          IN uld_card_details.loaded_at%TYPE,
    p_transfer_at        IN uld_card_details.transfer_at%TYPE,
    p_card_id            IN uld_card_details.card_id%TYPE,
    p_contents_remarks   IN uld_card_details.contents_remarks%TYPE,
    p_remarks            IN uld_card_details.remarks%TYPE,
    p_onward_flight_no1   IN uld_card_details.onward_flight_no1%TYPE,
    p_onward_flight_date1 IN uld_card_details.onward_flight_date1%TYPE,
    p_onward_flight_no2   IN uld_card_details.onward_flight_no2%TYPE,
    p_onward_flight_date2 IN uld_card_details.onward_flight_date2%TYPE,
    p_onward_flight_no3   IN uld_card_details.onward_flight_no3%TYPE,
    p_onward_flight_date3 IN uld_card_details.onward_flight_date3%TYPE,
    p_onward_flight_no4   IN uld_card_details.onward_flight_no4%TYPE,
    p_onward_flight_date4 IN uld_card_details.onward_flight_date4%TYPE,
    p_onward_flight_no5   IN uld_card_details.onward_flight_no5%TYPE,
    p_onward_flight_date5 IN uld_card_details.onward_flight_date5%TYPE,
    p_onward_flight_no6   IN uld_card_details.onward_flight_no6%TYPE,
    p_onward_flight_date6 IN uld_card_details.onward_flight_date6%TYPE
);

/******************************************************************************
 *
 * PROCEDURE SAVE_ULD_CARD_DETAILS_2
 *
 * Description:
 *   This procedure saves the ULD_CARD_DETAILS data in the database. It
 *   first attempts an UPDATE statement then an INSERT statement (if the update
 *   fails).
 *
 * Parameters:
 *   p_template_name      - Name given to the ULD card template.
 *   p_agent              - Name of the agent that owns the ULD card template.
 *   p_uld_category       - IATA standard describing the contents of the ULD.
 *   p_uld_type           - UltraTrak's ULD type (L, T or M).
 *   p_dest1              - IATA code of the final destination.
 *   p_class1             - Class of the ULD for destination 1.
 *   p_dest2              - IATA code for the ULD airport 2.
 *   p_class2             - Class of the ULD for destination 2.
 *   p_dest3              - IATA code for the ULD airport 3.
 *   p_class3             - Class of the ULD for destination 3.
 *   p_dest4              - IATA code for the ULD airport 4.
 *   p_class4             - Class of the ULD for destination 4.
 *   p_dest5              - IATA code for the ULD airport 5.
 *   p_class5             - Class of the ULD for destination 5.
 *   p_dest6              - IATA code for the ULD airport 6.
 *   p_class6             - Class of the ULD for destination 6.
 *   p_quantity           - Number of cards to print.
 *   p_loaded_at          - IATA code for the airport where the ULD was loaded.
 *   p_transfer_at        - IATA code for the transfer airport (if any).
 *   p_card_id            - Internal ID (sequence) of the card.
 *   p_contents_remarks   - Additional free format details (e.g. "PRIORITY").
 *   p_remarks            - Additional details or remarks.
 *   p_onward_flight_no1  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date1- Onward scheduled date/time of the flight.
 *   p_onward_flight_no2  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date2- Onward scheduled date/time of the flight.
 *   p_onward_flight_no3  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date3- Onward scheduled date/time of the flight.
 *   p_onward_flight_no4  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date4- Onward scheduled date/time of the flight.
 *   p_onward_flight_no5  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date5- Onward scheduled date/time of the flight.
 *   p_onward_flight_no6  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date6- Onward scheduled date/time of the flight.
 *   p_date_offset_1      - Date offset of the first onward flight
 *   p_date_offset_2      - Date offset of the second onward flight
 *   p_date_offset_3      - Date offset of the third onward flight
 *   p_date_offset_4      - Date offset of the fourth onward flight
 *   p_date_offset_5      - Date offset of the fifth onward flight
 *   p_date_offset_6      - Date offset of the sixth onward flight
 *
 * Errors raised:
 *   None.
 *
 ******************************************************************************/

PROCEDURE save_uld_card_details_2
   (p_template_name      IN uld_card_details.template_name%TYPE,
    p_agent              IN uld_card_details.agent%TYPE,
    p_uld_category       IN uld_card_details.uld_category%TYPE,
    p_uld_type           IN uld_card_details.uld_type%TYPE,
    p_dest1              IN uld_card_details.dest1%TYPE,
    p_class1             IN uld_card_details.class1%TYPE,
    p_dest2              IN uld_card_details.dest2%TYPE,
    p_class2             IN uld_card_details.class2%TYPE,
    p_dest3              IN uld_card_details.dest3%TYPE,
    p_class3             IN uld_card_details.class3%TYPE,
    p_dest4              IN uld_card_details.dest4%TYPE,
    p_class4             IN uld_card_details.class4%TYPE,
    p_dest5              IN uld_card_details.dest5%TYPE,
    p_class5             IN uld_card_details.class5%TYPE,
    p_dest6              IN uld_card_details.dest6%TYPE,
    p_class6             IN uld_card_details.class6%TYPE,
    p_quantity           IN uld_card_details.quantity%TYPE,
    p_loaded_at          IN uld_card_details.loaded_at%TYPE,
    p_transfer_at        IN uld_card_details.transfer_at%TYPE,
    p_card_id            IN uld_card_details.card_id%TYPE,
    p_contents_remarks   IN uld_card_details.contents_remarks%TYPE,
    p_remarks            IN uld_card_details.remarks%TYPE,
    p_onward_flight_no1   IN uld_card_details.onward_flight_no1%TYPE,
    p_onward_flight_date1 IN uld_card_details.onward_flight_date1%TYPE,
    p_onward_flight_no2   IN uld_card_details.onward_flight_no2%TYPE,
    p_onward_flight_date2 IN uld_card_details.onward_flight_date2%TYPE,
    p_onward_flight_no3   IN uld_card_details.onward_flight_no3%TYPE,
    p_onward_flight_date3 IN uld_card_details.onward_flight_date3%TYPE,
    p_onward_flight_no4   IN uld_card_details.onward_flight_no4%TYPE,
    p_onward_flight_date4 IN uld_card_details.onward_flight_date4%TYPE,
    p_onward_flight_no5   IN uld_card_details.onward_flight_no5%TYPE,
    p_onward_flight_date5 IN uld_card_details.onward_flight_date5%TYPE,
    p_onward_flight_no6   IN uld_card_details.onward_flight_no6%TYPE,
    p_onward_flight_date6 IN uld_card_details.onward_flight_date6%TYPE,
    p_date_offset_1       IN uld_card_details.date_offset_1%TYPE,
    p_date_offset_2       IN uld_card_details.date_offset_2%TYPE,
    p_date_offset_3       IN uld_card_details.date_offset_3%TYPE,
    p_date_offset_4       IN uld_card_details.date_offset_4%TYPE,
    p_date_offset_5       IN uld_card_details.date_offset_5%TYPE,
    p_date_offset_6       IN uld_card_details.date_offset_6%TYPE
);

/******************************************************************************
 *
 * PROCEDURE SAVE_AS_ULD_TEMPLATE
 *
 * Description:
 *   This procedure saves the ULD_CARD_TEMPLATES data in the database. It
 *   first attempts an UPDATE statement then an INSERT statement (if the update
 *   fails).
 *
 * Parameters:
 *   p_template_name      - Name given to the ULD card template.
 *   p_agent              - Name of the agent that owns the ULD card template.
 *   p_uld_category       - IATA standard describing the contents of the ULD.
 *   p_uld_type           - UltraTrak's ULD type (L, T or M).
 *   p_dest1              - IATA code of the final destination.
 *   p_class1             - Class of the ULD for destination 1.
 *   p_dest2              - IATA code for the ULD airport 2.
 *   p_class2             - Class of the ULD for destination 2.
 *   p_dest3              - IATA code for the ULD airport 3.
 *   p_class3             - Class of the ULD for destination 3.
 *   p_dest4              - IATA code for the ULD airport 4.
 *   p_class4             - Class of the ULD for destination 4.
 *   p_dest5              - IATA code for the ULD airport 5.
 *   p_class5             - Class of the ULD for destination 5.
 *   p_dest6              - IATA code for the ULD airport 6.
 *   p_class6             - Class of the ULD for destination 6.
 *   p_quantity           - Number of cards to print.
 *   p_loaded_at          - IATA code for the airport where the ULD was loaded.
 *   p_transfer_at        - IATA code for the transfer airport (if any).
 *   p_flight_no          - Flight name for the departing flight (e.g. QF 123).
 *   p_flight_date        - Scheduled date/time of the flight.
 *   p_bar_codes          - Determines whether bar codes are to be printed.
 *   p_card_id            - Internal ID (sequence) of the card.
 *   p_contents_remarks   - Additional free format details (e.g. "PRIORITY").
 *   p_remarks            - Additional details or remarks.
 *   p_onward_flight_no1  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date1- Onward scheduled date/time of the flight.
 *   p_onward_flight_no2  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date2- Onward scheduled date/time of the flight.
 *   p_onward_flight_no3  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date3- Onward scheduled date/time of the flight.
 *   p_onward_flight_no4  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date4- Onward scheduled date/time of the flight.
 *   p_onward_flight_no5  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date5- Onward scheduled date/time of the flight.
 *   p_onward_flight_no6  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date6- Onward scheduled date/time of the flight.
 *
 * Errors raised:
 *   None.
 *
 ******************************************************************************/

PROCEDURE save_as_uld_template
   (p_template_name      IN uld_card_templates.template_name%TYPE,
    p_agent              IN uld_card_templates.agent%TYPE,
    p_uld_category       IN uld_category_type,
    p_uld_type           IN uld_type_type,
    p_dest1              IN dest1_type,
    p_class1             IN class1_type,
    p_dest2              IN dest2_type,
    p_class2             IN class2_type,
    p_dest3              IN dest3_type,
    p_class3             IN class3_type,
    p_dest4              IN dest4_type,
    p_class4             IN class4_type,
    p_dest5              IN dest5_type,
    p_class5             IN class5_type,
    p_dest6              IN dest6_type,
    p_class6             IN class6_type,
    p_quantity           IN quantity_type,
    p_loaded_at          IN loaded_at_type,
    p_transfer_at        IN transfer_at_type,
    p_flight_no          IN uld_card_templates.flight_no%TYPE,
    p_flight_date        IN uld_card_templates.flight_date%TYPE,
    p_bar_codes          IN uld_card_templates.bar_codes%TYPE,
    p_card_id            IN card_id_type,
    p_contents_remarks   IN contents_remarks_type,
    p_remarks            IN remarks_type,
    p_onward_flight_no1   IN onward_flight_no1_type,
    p_onward_flight_date1 IN onward_flight_date1_type,
    p_onward_flight_no2   IN onward_flight_no2_type,
    p_onward_flight_date2 IN onward_flight_date2_type,
    p_onward_flight_no3   IN onward_flight_no3_type,
    p_onward_flight_date3 IN onward_flight_date3_type,
    p_onward_flight_no4   IN onward_flight_no4_type,
    p_onward_flight_date4 IN onward_flight_date4_type,
    p_onward_flight_no5   IN onward_flight_no5_type,
    p_onward_flight_date5 IN onward_flight_date5_type,
    p_onward_flight_no6   IN onward_flight_no6_type,
    p_onward_flight_date6 IN onward_flight_date6_type
);

/******************************************************************************
 *
 * PROCEDURE DELETE_ULD_TEMPLATE
 *
 * Description:
 *   This procedure deletes a record from the ULD_CARD_TEMPLATES table and all
 *   the associated recorded from the ULD_CARDDETAILS table for a given
 *   template name and agent.
 *
 * Parameters:
 *   p_template_name - Name given to the ULD card template.
 *   p_agent         - Name of the agent that owns the ULD card template.
 *
 * Errors raised:
 *   None.
 *
 ******************************************************************************/

PROCEDURE delete_uld_template
   (p_template_name IN uld_card_templates.template_name%TYPE,
    p_agent         IN uld_card_templates.agent%TYPE);

/******************************************************************************
 *
 * PROCEDURE DELETE_ULD_DETAILS
 *
 * Description:
 *   This procedure deletes a record from the ULD_CARD_DETAILS table for a
 *   given template name and agent.
 *
 * Parameters:
 *   p_template_name - Name given to the ULD card template.
 *   p_agent         - Name of the agent that owns the ULD card template.
 *   p_card_id       - Internal ID (sequence) of the card.
 *
 * Errors raised:
 *   None.
 *
 ******************************************************************************/

PROCEDURE delete_uld_details
   (p_template_name IN uld_card_details.template_name%TYPE,
    p_agent         IN uld_card_details.agent%TYPE,
    p_card_id       IN uld_card_details.card_id%TYPE);

/******************************************************************************
 *
 * PROCEDURE RECORD_ULD_TEMPLATE
 *
 * Description:
 *   This procedure inserts ULD information in the ULD_RECORDED_ID_CODES table
 *   ready for assigning. The procedure assigns a unique ULD ID code to bind
 *   the inserted record to the ULD card.
 *
 *   It passes back to the calling process the ULD ID code that was assigned to
 *   the inserted record.
 *
 * Parameters:
 *   p_template_name     - Name given to the ULD card template.
 *   p_agent             - Name of the agent that owns the ULD card template.
 *   p_uld_category      - IATA standard describing the contents of the ULD.
 *   p_uld_type          - UltraTrak's ULD type (L, T or M).
 *   p_dest1             - IATA code of the final destination.
 *   p_class1            - Class of the ULD for destination 1.
 *   p_dest2             - IATA code for the ULD airport 2.
 *   p_class2            - Class of the ULD for destination 2.
 *   p_dest3             - IATA code for the ULD airport 3.
 *   p_class3            - Class of the ULD for destination 3.
 *   p_dest4             - IATA code for the ULD airport 4.
 *   p_class4            - Class of the ULD for destination 4.
 *   p_dest5             - IATA code for the ULD airport 5.
 *   p_class5            - Class of the ULD for destination 5.
 *   p_dest6             - IATA code for the ULD airport 6.
 *   p_class6            - Class of the ULD for destination 6.
 *   p_flight_no         - Flight name for the departing flight (e.g. QF 123).
 *   p_flight_date       - Scheduled date/time of the flight.
 *   p_contents_remarks  - Additional free format details (e.g. "PRIORITY").
 *   p_remarks           - Additional details or remarks.
 *   p_onward_flight_no1  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date1- Onward scheduled date/time of the flight.
 *   p_onward_flight_no2  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date2- Onward scheduled date/time of the flight.
 *   p_onward_flight_no3  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date3- Onward scheduled date/time of the flight.
 *   p_onward_flight_no4  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date4- Onward scheduled date/time of the flight.
 *   p_onward_flight_no5  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date5- Onward scheduled date/time of the flight.
 *   p_onward_flight_no6  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date6- Onward scheduled date/time of the flight.
 *   p_uld_id_code       - The ULD cards assigned unique ULD ID code.
 *
 * Errors raised:
 *   None.
 *
 ******************************************************************************/

PROCEDURE record_uld_template
   (p_template_name       IN  uld_recorded_id_codes.template_name%TYPE,
    p_agent               IN  uld_recorded_id_codes.agent%TYPE,
    p_card_id             IN  uld_recorded_id_codes.card_id%TYPE,
    p_uld_category        IN  uld_recorded_id_codes.uld_category%TYPE,
    p_uld_type            IN  uld_recorded_id_codes.uld_type%TYPE,
    p_dest1               IN  uld_recorded_id_codes.dest1%TYPE,
    p_class1              IN  uld_recorded_id_codes.class1%TYPE,
    p_dest2               IN  uld_recorded_id_codes.dest2%TYPE,
    p_class2              IN  uld_recorded_id_codes.class2%TYPE,
    p_dest3               IN  uld_recorded_id_codes.dest3%TYPE,
    p_class3              IN  uld_recorded_id_codes.class3%TYPE,
    p_dest4               IN  uld_recorded_id_codes.dest4%TYPE,
    p_class4              IN  uld_recorded_id_codes.class4%TYPE,
    p_dest5               IN  uld_recorded_id_codes.dest5%TYPE,
    p_class5              IN  uld_recorded_id_codes.class5%TYPE,
    p_dest6               IN  uld_recorded_id_codes.dest6%TYPE,
    p_class6              IN  uld_recorded_id_codes.class6%TYPE,
    p_flight_no           IN  uld_recorded_id_codes.flight_no%TYPE,
    p_flight_date         IN  uld_recorded_id_codes.flight_date%TYPE,
    p_departing_airport   IN  uld_recorded_id_codes.dest1%TYPE,
    p_contents_remarks    IN  uld_recorded_id_codes.contents_remarks%TYPE,
    p_remarks             IN  uld_recorded_id_codes.remarks%TYPE,
    p_onward_flight_no1   IN  uld_recorded_id_codes.onward_flight_no1%TYPE,
    p_onward_flight_date1 IN  uld_recorded_id_codes.onward_flight_date1%TYPE,
    p_onward_flight_no2   IN  uld_recorded_id_codes.onward_flight_no2%TYPE,
    p_onward_flight_date2 IN  uld_recorded_id_codes.onward_flight_date2%TYPE,
    p_onward_flight_no3   IN  uld_recorded_id_codes.onward_flight_no3%TYPE,
    p_onward_flight_date3 IN  uld_recorded_id_codes.onward_flight_date3%TYPE,
    p_onward_flight_no4   IN  uld_recorded_id_codes.onward_flight_no4%TYPE,
    p_onward_flight_date4 IN  uld_recorded_id_codes.onward_flight_date4%TYPE,
    p_onward_flight_no5   IN  uld_recorded_id_codes.onward_flight_no5%TYPE,
    p_onward_flight_date5 IN  uld_recorded_id_codes.onward_flight_date5%TYPE,
    p_onward_flight_no6   IN  uld_recorded_id_codes.onward_flight_no6%TYPE,
    p_onward_flight_date6 IN  uld_recorded_id_codes.onward_flight_date6%TYPE,
    p_uld_id_code         OUT uld_recorded_id_codes.uld_id_code%TYPE);


/******************************************************************************
 *
 * PROCEDURE RECORD_REGISTER_ULD
 *
 * Description:
 *   1. This procedure inserts ULD information in the ULD_RECORDED_ID_CODES table
 *      ready for assigning. The procedure assigns a unique ULD ID code to bind
 *      the inserted record to the ULD card.
 *   2. Associate ULD_ID_CODE to ULD_NO
 *   3. Register ULD to FLIGHT
 *
 *   It passes back to the calling process the ULD ID code that was assigned to
 *   the inserted record and a return code signifying the status of for this process.
 *
 * Parameters:
 *   p_template_name     - Name given to the ULD card template.
 *   p_agent             - Name of the agent that owns the ULD card template.
 *   p_card_id           - Card ID for  ULD Template
 *   p_uld_category      - IATA standard describing the contents of the ULD.
 *   p_uld_type          - UltraTrak's ULD type (L, T or M).
 *   p_dest1             - IATA code of the final destination.
 *   p_class1            - Class of the ULD for destination 1.
 *   p_dest2             - IATA code for the ULD airport 2.
 *   p_class2            - Class of the ULD for destination 2.
 *   p_dest3             - IATA code for the ULD airport 3.
 *   p_class3            - Class of the ULD for destination 3.
 *   p_dest4             - IATA code for the ULD airport 4.
 *   p_class4            - Class of the ULD for destination 4.
 *   p_dest5             - IATA code for the ULD airport 5.
 *   p_class5            - Class of the ULD for destination 5.
 *   p_dest6             - IATA code for the ULD airport 6.
 *   p_class6            - Class of the ULD for destination 6.
 *   p_flight_no         - Flight name for the departing flight (e.g. QF 123).
 *   p_flight_date       - Scheduled date/time of the flight.
 *   p_departing_airport - The airport
 *   p_uld_no            - ULD_NO FOR FLIGHT
 *   p_contents_remarks  - Additional free format details (e.g. "PRIORITY").
 *   p_remarks           - Additional details or remarks.
 *   p_onward_flight_no1  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date1- Onward scheduled date/time of the flight.
 *   p_onward_flight_no2  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date2- Onward scheduled date/time of the flight.
 *   p_onward_flight_no3  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date3- Onward scheduled date/time of the flight.
 *   p_onward_flight_no4  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date4- Onward scheduled date/time of the flight.
 *   p_onward_flight_no5  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date5- Onward scheduled date/time of the flight.
 *   p_onward_flight_no6  - Onward flight name for the flight (e.g. QF 456).
 *   p_onward_flight_date6- Onward scheduled date/time of the flight.
 *   p_uld_id_code       - The ULD cards assigned unique ULD ID code.
 *   retcode             - Error code indicating status of process
 * Errors raised:
 *   None.
 *
 ******************************************************************************/

PROCEDURE record_register_uld
   (
    p_template_name       IN  uld_card_templates.template_name%TYPE,
    p_agent               IN  uld_card_templates.agent%TYPE,
    p_card_id             IN  uld_recorded_id_codes.card_id%TYPE,
    p_uld_category        IN  uld_recorded_id_codes.uld_category%TYPE,
    p_uld_type            IN  uld_recorded_id_codes.uld_type%TYPE,
    p_dest1               IN  uld_recorded_id_codes.dest1%TYPE,
    p_class1              IN  uld_recorded_id_codes.class1%TYPE,
    p_dest2               IN  uld_recorded_id_codes.dest2%TYPE,
    p_class2              IN  uld_recorded_id_codes.class2%TYPE,
    p_dest3               IN  uld_recorded_id_codes.dest3%TYPE,
    p_class3              IN  uld_recorded_id_codes.class3%TYPE,
    p_dest4               IN  uld_recorded_id_codes.dest4%TYPE,
    p_class4              IN  uld_recorded_id_codes.class4%TYPE,
    p_dest5               IN  uld_recorded_id_codes.dest5%TYPE,
    p_class5              IN  uld_recorded_id_codes.class5%TYPE,
    p_dest6               IN  uld_recorded_id_codes.dest6%TYPE,
    p_class6              IN  uld_recorded_id_codes.class6%TYPE,
    p_flight_no           IN  uld_recorded_id_codes.flight_no%TYPE,
    p_flight_date         IN  uld_recorded_id_codes.flight_date%TYPE,
    p_departing_airport   IN  uld_recorded_id_codes.dest1%TYPE,
    p_uld_no              IN  uld_recorded_id_codes.uld_no%TYPE,
    p_conn_type           IN  ulds.connection_type%TYPE,
    p_userid              IN  tagtrak_users.user_id%TYPE,
    p_loc                 IN  location.location_code%type,
    p_contents_remarks    IN  uld_recorded_id_codes.contents_remarks%TYPE,
    p_remarks             IN  uld_recorded_id_codes.remarks%TYPE,
    p_onward_flight_no1   IN  uld_recorded_id_codes.onward_flight_no1%TYPE,
    p_onward_flight_date1 IN  uld_recorded_id_codes.onward_flight_date1%TYPE,
    p_onward_flight_no2   IN  uld_recorded_id_codes.onward_flight_no2%TYPE,
    p_onward_flight_date2 IN  uld_recorded_id_codes.onward_flight_date2%TYPE,
    p_onward_flight_no3   IN  uld_recorded_id_codes.onward_flight_no3%TYPE,
    p_onward_flight_date3 IN  uld_recorded_id_codes.onward_flight_date3%TYPE,
    p_onward_flight_no4   IN  uld_recorded_id_codes.onward_flight_no4%TYPE,
    p_onward_flight_date4 IN  uld_recorded_id_codes.onward_flight_date4%TYPE,
    p_onward_flight_no5   IN  uld_recorded_id_codes.onward_flight_no5%TYPE,
    p_onward_flight_date5 IN  uld_recorded_id_codes.onward_flight_date5%TYPE,
    p_onward_flight_no6   IN  uld_recorded_id_codes.onward_flight_no6%TYPE,
    p_onward_flight_date6 IN  uld_recorded_id_codes.onward_flight_date6%TYPE,
    p_uld_id_code         OUT uld_recorded_id_codes.uld_id_code%TYPE,
    p_retcode             OUT NUMBER
    );

/******************************************************************************
 *
 * FUNCTION VALIDATE_ULDNO
 *
 * Description:
 *   This function validate the uld no format.
 *
 * Parameters:
 *   uld no -
 *
 * Return Value:
 *    BOOLEAN
 *
 * Errors raised:
 *   None.
 *
 ******************************************************************************/

FUNCTION validate_uldno
   (
     p_uldno IN ulds.uld_no%type
    ) RETURN BOOLEAN;

/******************************************************************************
 *
 * PROCEDURE RECORD_ULD
 *
 ******************************************************************************/

PROCEDURE record_uld
   (p_template_name       IN  uld_recorded_id_codes.template_name%TYPE,
    p_agent               IN  uld_recorded_id_codes.agent%TYPE,
    p_card_id             IN  uld_recorded_id_codes.card_id%TYPE,
    p_uld_category        IN  uld_recorded_id_codes.uld_category%TYPE,
    p_uld_type            IN  uld_recorded_id_codes.uld_type%TYPE,
    p_dest1               IN  uld_recorded_id_codes.dest1%TYPE,
    p_class1              IN  uld_recorded_id_codes.class1%TYPE,
    p_dest2               IN  uld_recorded_id_codes.dest2%TYPE,
    p_class2              IN  uld_recorded_id_codes.class2%TYPE,
    p_dest3               IN  uld_recorded_id_codes.dest3%TYPE,
    p_class3              IN  uld_recorded_id_codes.class3%TYPE,
    p_dest4               IN  uld_recorded_id_codes.dest4%TYPE,
    p_class4              IN  uld_recorded_id_codes.class4%TYPE,
    p_dest5               IN  uld_recorded_id_codes.dest5%TYPE,
    p_class5              IN  uld_recorded_id_codes.class5%TYPE,
    p_dest6               IN  uld_recorded_id_codes.dest6%TYPE,
    p_class6              IN  uld_recorded_id_codes.class6%TYPE,
    p_flight_no           IN  uld_recorded_id_codes.flight_no%TYPE,
    p_flight_date         IN  uld_recorded_id_codes.flight_date%TYPE,
    p_departing_airport   IN  uld_recorded_id_codes.dest1%TYPE,
    p_contents_remarks    IN  uld_recorded_id_codes.contents_remarks%TYPE,
    p_remarks             IN  uld_recorded_id_codes.remarks%TYPE,
    p_onward_flight_no1   IN  uld_recorded_id_codes.onward_flight_no1%TYPE,
    p_onward_flight_date1 IN  uld_recorded_id_codes.onward_flight_date1%TYPE,
    p_onward_flight_no2   IN  uld_recorded_id_codes.onward_flight_no2%TYPE,
    p_onward_flight_date2 IN  uld_recorded_id_codes.onward_flight_date2%TYPE,
    p_onward_flight_no3   IN  uld_recorded_id_codes.onward_flight_no3%TYPE,
    p_onward_flight_date3 IN  uld_recorded_id_codes.onward_flight_date3%TYPE,
    p_onward_flight_no4   IN  uld_recorded_id_codes.onward_flight_no4%TYPE,
    p_onward_flight_date4 IN  uld_recorded_id_codes.onward_flight_date4%TYPE,
    p_onward_flight_no5   IN  uld_recorded_id_codes.onward_flight_no5%TYPE,
    p_onward_flight_date5 IN  uld_recorded_id_codes.onward_flight_date5%TYPE,
    p_onward_flight_no6   IN  uld_recorded_id_codes.onward_flight_no6%TYPE,
    p_onward_flight_date6 IN  uld_recorded_id_codes.onward_flight_date6%TYPE,
    p_uld_id_code         IN  uld_recorded_id_codes.uld_id_code%TYPE,
    p_allow_update        IN  BOOLEAN
    );

/******************************************************************************
 *
 * PROCEDURE REGISTER_ULD
 *
 ******************************************************************************/

PROCEDURE register_uld
   (p_uld_no              IN OUT uld_recorded_id_codes.uld_no%TYPE,
    p_fid                 IN OUT flight.flight_id%TYPE,
    p_uld_category        IN OUT uld_recorded_id_codes.uld_category%TYPE,
    p_conn_type           IN OUT ulds.connection_type%TYPE,
    p_valid_dest          IN     BOOLEAN,
    p_userid              IN OUT tagtrak_users.user_id%TYPE,
    p_loc                 IN OUT location.location_code%TYPE,
    p_insert_flag         IN     NUMBER,
    p_uld_id_code         IN     uld_recorded_id_codes.uld_id_code%TYPE,
    p_dest                IN OUT dest_list_type,
    p_class               IN OUT class_list_type,
    p_onwd_flight_no      IN     onward_flight_no_type,
    p_onwd_flight_date    IN     onward_flight_date_type,
    p_uld_type            IN OUT uld_recorded_id_codes.uld_type%TYPE,
    p_flag                OUT    NUMBER
    );

/******************************************************************************
 *
 * PROCEDURE POPULATE_DEST_ARRAYS
 *
 ******************************************************************************/
PROCEDURE populate_dest_arrays
   (p_dest1               IN  uld_recorded_id_codes.dest1%TYPE,
    p_dest2               IN  uld_recorded_id_codes.dest2%TYPE,
    p_dest3               IN  uld_recorded_id_codes.dest3%TYPE,
    p_dest4               IN  uld_recorded_id_codes.dest4%TYPE,
    p_dest5               IN  uld_recorded_id_codes.dest5%TYPE,
    p_dest6               IN  uld_recorded_id_codes.dest6%TYPE,
    p_class1              IN  uld_recorded_id_codes.class1%TYPE,
    p_class2              IN  uld_recorded_id_codes.class2%TYPE,
    p_class3              IN  uld_recorded_id_codes.class3%TYPE,
    p_class4              IN  uld_recorded_id_codes.class4%TYPE,
    p_class5              IN  uld_recorded_id_codes.class5%TYPE,
    p_class6              IN  uld_recorded_id_codes.class6%TYPE,
    p_onward_flight_no1   IN  uld_recorded_id_codes.onward_flight_no1%TYPE,
    p_onward_flight_no2   IN  uld_recorded_id_codes.onward_flight_no2%TYPE,
    p_onward_flight_no3   IN  uld_recorded_id_codes.onward_flight_no3%TYPE,
    p_onward_flight_no4   IN  uld_recorded_id_codes.onward_flight_no4%TYPE,
    p_onward_flight_no5   IN  uld_recorded_id_codes.onward_flight_no5%TYPE,
    p_onward_flight_no6   IN  uld_recorded_id_codes.onward_flight_no6%TYPE,
    p_onward_flight_date1 IN  uld_recorded_id_codes.onward_flight_date1%TYPE,
    p_onward_flight_date2 IN  uld_recorded_id_codes.onward_flight_date2%TYPE,
    p_onward_flight_date3 IN  uld_recorded_id_codes.onward_flight_date3%TYPE,
    p_onward_flight_date4 IN  uld_recorded_id_codes.onward_flight_date4%TYPE,
    p_onward_flight_date5 IN  uld_recorded_id_codes.onward_flight_date5%TYPE,
    p_onward_flight_date6 IN  uld_recorded_id_codes.onward_flight_date6%TYPE,
    p_dest                OUT dest_list_type,
    p_class               OUT class_list_type,
    p_onward_flight_date  OUT onward_flight_date_type,
    p_onward_flight_no    OUT onward_flight_no_type,
    p_valid_dest          OUT BOOLEAN
    );

/******************************************************************************
 *
 * FUNCTION GET_ULD_TYPE
 *
 ******************************************************************************/
FUNCTION get_uld_type
   (p_uld_category IN uld_card_details.uld_category%TYPE
   ) RETURN uld_recorded_id_codes.uld_type%TYPE;



/******************************************************************************
 *
 * PROCEDURE ULD_CARDS_REPORT_CORE
 *
 * Description:
 *   This procedure creates a ref cursor used for generation of the ULD Cards
 *   report. ULD IDs are passed into the procedure by the report, and the
 *   details of those ULDs are returned as a sys_refcursor
 *
 * Parameters:
 *   The following parameters are passed into the function from the ULD_Cards
 *   report. Their data consists of comma delimited ULD IDs.
 *   p_ULD_str1
 *   p_ULD_str2
 *   p_ULD_str3
 *   p_ULD_str4
 *   p_ULD_str5
 *   p_ULD_str6
 *   p_ULD_str7
 *   p_ULD_str8
 *   p_ULD_str9
 *   p_ULD_str10
 *   These parameters are passed straight into the load_tab function to convert
 *   them to a columnar format.
 *
 * Out Variables
 *   For the column of ULD IDs, the procedure selects details of each ULD from
 *   the database. A sys_refcursor is returned holding details of the ULDs.
 *   These details are in the correct format to be interpreted by ULD_Cards.rpt
 *
 * Errors raised:
 *   None.
 *
 ******************************************************************************/

PROCEDURE uld_cards_report_core(p_uld_ref_cur IN OUT sys_refcursor,
                                p_ULD_str1   IN VARCHAR2,
                                p_ULD_str2   IN VARCHAR2,
                                p_ULD_str3   IN VARCHAR2,
                                p_ULD_str4   IN VARCHAR2,
                                p_ULD_str5   IN VARCHAR2,
                                p_ULD_str6   IN VARCHAR2,
                                p_ULD_str7   IN VARCHAR2,
                                p_ULD_str8   IN VARCHAR2,
                                p_ULD_str9   IN VARCHAR2,
                                p_ULD_str10  IN VARCHAR2) ;

/******************************************************************************
 *
 * PROCEDURE uld_cards_report
 *
 * Description:
 *   This procedure returns a ref cursor that forms the basis of the ULDCards report.
 *
 * Parameters:
 *   p_ref_cur - the ref cursor - as defined at the top of the spec.
 *   p_str1 through p_str10 - these parameters store lists of uld_id_codes.
 *                            Each one should be of the format:
 *                            'id_code1','id_code2','id_code3'
 *
 * Errors raised:
 *   None.
 *
 ******************************************************************************/
PROCEDURE uld_cards_report(p_ref_cur in OUT uld_cards_ref_cur,
                           p_str1           VARCHAR2,
                           p_str2           VARCHAR2,
                           p_str3           VARCHAR2,
                           p_str4           VARCHAR2,
                           p_str5           VARCHAR2,
                           p_str6           VARCHAR2,
                           p_str7           VARCHAR2,
                           p_str8           VARCHAR2,
                           p_str9           VARCHAR2,
                           p_str10          VARCHAR2);

/******************************************************************************
 *
 * FUNCTION uld_class_desc
 *
 * Description:
 *   This function returns the description for a passed in uld class.
 *
 * Parameters:
 *   p_uld_class - uld class.
 *
 * Return Value:
 *    description from cabin_classes table.
 *
 * Errors raised:
 *   None.
 *
 ******************************************************************************/
FUNCTION uld_class_desc(p_uld_class  IN cabin_classes.cabin_class%TYPE)
RETURN cabin_classes.description%TYPE;

END uld_cards;
/
