-- Added a simple function to attach a file to a ticket 
-- (From Jun Yamog) Ported to Oracle by vinodk
--
-- @author vinod@kurup.com
-- @created 2002-03-19

create or replace function ttl__create_file (
	p_package_id		   in apm_packages.package_id%TYPE, 
	p_user_id			   in users.user_id%TYPE, 
	p_name				   in cr_items.name%TYPE, 
	p_parent_id			   in cr_items.parent_id%TYPE, 
	p_mime_type			   in cr_mime_types.mime_type%TYPE,
	p_file_extension	   in cr_mime_types.file_extension%TYPE
) return cr_revisions.revision_id%TYPE
as
	v_item_id			cr_items.item_id%TYPE;
	v_revision_id		cr_revisions.revision_id%TYPE;
	v_mime_type			cr_mime_types.mime_type%TYPE;
begin

	begin
		-- detect mime type if mime is not yet in cr_mime_type and add it
		select mime_type into v_mime_type from cr_mime_types
		  where  mime_type = p_mime_type;
 
		exception
		  when NO_DATA_FOUND then
			insert into cr_mime_types 
			(mime_type, file_extension) 
			values 
			(p_mime_type, p_file_extension);
	end;

	-- if the file already exists then nuke it. this become essentially write
	select max(item_id) into v_item_id from cr_items where name = p_name;

	if v_item_id is not null then
	   content_item.delete(v_item_id);
	end if;

	-- vinodk: this needs to be rewritten to use CR API
	-- oracle - use lob instead of file

	-- create a new cr_item
	v_item_id := acs_object.new (
		null, 
		'content_item', 
		sysdate, 
		p_user_id, 
		null, 
		p_package_id
	);

	insert into cr_items 
	(item_id, parent_id, name, content_type, storage_type) 
	values 
	(v_item_id, p_parent_id, p_name, 'content_revision', 'lob');

	-- create a new cr_revision
	v_revision_id := acs_object.new (
		null, 
		'content_revision', 
		sysdate, 
		p_user_id, 
		null, 
		null
	);

	insert into cr_revisions 
	(revision_id, item_id, mime_type) 
	values 
	(v_revision_id, v_item_id, p_mime_type);

	return v_revision_id;
end;
/
show errors;