/*****************************************************************************
*
* Filename         - $Workfile:   uld_destination.sql  $
*
*                     Name              Date      Version     CR Number
*                Martin Helliwell     26/06/01      1.0        CR15526
* 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 Package
*
* Language         - Oracle PL/SQL script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2001
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   uld_destination.sql  $  $Revision:   1.0  $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
* $Log:   /pvcsarch/db_ora/db/source/live/procedures/uld_destination.svl  $
* 
*    Rev 1.0   31 Oct 2001 15:47:14   tagtrak
* OR:
* CR: 15526
* REV: 1.0
* DESC: List of uld_dests
* 
*****************************************************************************/
PROMPT CREATE OR REPLACE FUNCTION uld_destination 

CREATE OR REPLACE
FUNCTION uld_destination 
  (p_uld_no IN ulds.uld_no%TYPE
  ,p_flight_id IN flight.flight_id%TYPE) RETURN VARCHAR2 AS

  -- cursor to get all the destinations for a uld on a flight

  CURSOR c_uld_dests (p_uld_no ulds.uld_no%TYPE, p_flight_id flight.flight_id%TYPE) IS
    SELECT uld_destination
    FROM uld_dests
    WHERE uld_no = p_uld_no
    AND flight_id = p_flight_id;

  separator CHAR(2) DEFAULT NULL;

  p_uld_destination VARCHAR2(200);    -- 11/05/04  Nigel  Changed from 24 to 200 (UAS/FALCP/CA/00702)
  BEGIN

		-- loop to concatenate uld destinations into a string separated by commas and spaces
       
    FOR d IN c_uld_dests(p_uld_no, p_flight_id) LOOP   
      p_uld_destination := p_uld_destination || separator || d.uld_destination;
      separator := ', ';
    END LOOP;

    RETURN p_uld_destination;

  END uld_destination;
/

