/*****************************************************************************
*
* Filename         - aggregate_misc_exceptions.sql
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 
*
* Design Spec
* Section          - 
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2006
*
*****************************************************************************/

prompt create or replace function Aggregate_Misc_Exceptions

create or replace function
	Aggregate_Misc_Exceptions (p_bag_id number) return varchar2
as  

    cursor exceptions_cursor is select distinct(exception_type) etype
    	from misc_bag_exceptions
	    where bag_id = p_bag_id
	    	order by exception_type;

    result varchar2(4000) := '';

begin

    -- Loop through all the exceptions that match this bag, concatenating
    -- them together comma separated.
    --
    for r in exceptions_cursor loop
	if length(result) > 0 then
	    result := result || ', ' || r.etype;
	else
	    result := r.etype;
	end if;
    end loop;

    return result;

end Aggregate_Misc_Exceptions;
/
