DECLARE 
	l_query varchar2( 10000 );
begin	
  go_block('BRANCH');
  Clear_Block(no_validate);
	l_query:='(select id_depart as office_id , shortname as name 
                           from (select d.id_depart, d.shortname
                                ,nvl(brg.branch_goup,d.id_parent) id_parent
                                from asta.depart d
                                left join asta.branch_group brg
                                on d.id_depart=brg.branch
                                 and (brg.activitykind=2 or brg.activitykind is null)
                                 and (brg.dbegin <= sysdate or brg.dbegin is null) 
                                 and (brg.dend is null or brg.dend >=  sysdate )
                                where d.activitykind=2
                                 and (d.date_create <= sysdate or d.date_create is null) 
                                 and (d.date_reorganization is null or d.date_reorganization >=  sysdate )
                                ) ww 
                            start with id_depart=1
                            connect by prior id_depart=id_parent)';
set_block_property('BRANCH',query_data_source_name,l_query)	;

go_block('BRANCH');
execute_query;

end;