1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
DECLARE
a1 number(10);
CURSOR cur IS
select distinct clink.CONTACT_ID_FROM
from ae6contactlink clink
where (clink.CONTACT_ROLE='FILIALE_DE' OR clink.CONTACT_ROLE='GROUPE_DE');
cursor c_detail( contactID ae6contact.contact_id%TYPE ) is
SELECT id, name, LEVEL, parent, name2
FROM ( select co.contact_id as id, co.name, cl.CONTACT_ID_TO as parent, co2.NAME as name2
from ae6contact co, ae6contactlink cl, ae6contact co2
where co.contact_group='ENTITE'
and co.contact_id = cl.contact_id_from
and cl.contact_role='FILIALE_DE'
AND cl.status='N'
AND co2.contact_id=cl.CONTACT_ID_TO)
START WITH id = contactID
CONNECT BY id = PRIOR parent
--ORDER BY LEVEL DESC
UNION
select null, '', null, co.contact_id, co.name from ae6contact co where co.contact_id=contactID;
BEGIN
for rec in cur loop
a1 :=1;
for machin in c_detail( rec.CONTACT_ID_FROM ) loop -- liste de variables locales
insert into AEMTOTO values (machin.id,machin.name,machin.parent,machin.name2, rec.CONTACT_ID_FROM, a1);
a1 := a1 + 1;
end loop;
END LOOP;
END; |
