[ELMA3] Особенности разработки для Oracle

create or replace function NOMENCLATURETREE
(p_YEARNOMEN integer)
RETURN NOMENCLATURETREE_tbl PIPELINED
is
	temp NOMENCLATURETREE_row;
begin
	temp := NOMENCLATURETREE_row(null,null,null,null,null);
	FOR i IN (
		select
			dg."Id" as "Id",
			case
				when (rpi."Id" is not null) then rp."Name"
				when (dl."Id" is not null) then dr2."Name"
				else dg."Name"
			end as "Name", 
			dg.parentgroup, 
			case 
			  when (dl."Id" is not null) then dl.DEPOSITORY
			else dg."Id" end as LinkId, 
			dg.typeuid as nodetypeuid  
		from depositorygroup dg
			left join depositorygroup dr on dr."Id" = dg."Id"
			left join registrationplaceitem rpi on rpi."Id" = dr."Id"
			left join REGISTRATIONPLACE rp on rp."Id" = rpi.registrationplace
			left join depositorylink dl on dl."Id" = dr."Id"
			left join depositorygroup dr2 on dl.depository = dr2."Id"
		start with 1=1
			and dg.parentgroup is null
			and exists
			(select 1 from registrationplaceitem regpli, accountingyear ay
			where 1=1
			and dg."Id" = regpli."Id"
			and ay."Id" = regpli.ACCOUNTINGYEAR
			and ay."Year" = p_YEARNOMEN)
		connect by nocycle prior dg."Id" = dg.parentgroup
	)
	LOOP
		temp."Id" := i."Id";
		temp."Name" := i."Name";
		temp.PARENTGROUP := i.PARENTGROUP;
		temp.LinkId := i.LinkId;
		temp.nodetypeuid := i.nodetypeuid;
		PIPE ROW (temp);
	END LOOP;
	RETURN;
end NOMENCLATURETREE;

Рекурсивные процедуры