/*****************************************************************************
*
* Filename         - onward_reports.sql
*
* Project Name     - UltraTrak
*
* Contents         - Live Schema Stored Package
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2007
*
*****************************************************************************/

create or replace package Onward_Reports as

    function Get_Destinations (p_pax_id in pax.pax_id%type) return varchar2;

end Onward_Reports;
/

create or replace package body Onward_Reports as

    function Get_Destinations (p_pax_id in pax.pax_id%type) return varchar2 is

	l_dests varchar2(256);
	l_count number;

	cursor l_curs is
	    select * from onward_flight
		where pax_id = p_pax_id order by dest_order;

    begin

    	select count(*) into l_count from onward_flight where pax_id = p_pax_id;

	-- Construct "1st, 2nd, 3rd(..more), last"
	--
	for l_rec in l_curs loop

	    if l_rec.dest_order = 1 then
	    
	    	l_dests := l_rec.dest;

	    elsif l_rec.dest_order = 3 and l_count > 4 then
	    
	    	l_dests := l_dests || ', ' || l_rec.dest || '..(more)';

	    elsif l_rec.dest_order < 4 or l_rec.dest_order = l_count then

	    	l_dests := l_dests || ', ' || l_rec.dest;

	    end if;

	end loop;

	return l_dests;

    end Get_Destinations;

end Onward_Reports;
/
