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 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113
| declare
LC$NB_LIAISON_PERIODE binary_integer;
LC$NB_LIAISON_TOTALE number;
-- Récupère le nombre de liaison pour une période donnée
Cursor C_PARTNER_PER is
select p.partner_code,p.full_name,count(*) as nb_liaison
from partners p, partner_groups_maintenance pgm,partners_group pg
where p.partner_code=pgm.partner_code
and pgm.group_uid=pg.group_uid
and pg.group_type='LEGALGRP'
and ((extract(year from pgm.date_stamp)=2009 and extract(month from pgm.date_stamp)=04)
or (extract(year from pgm.crpgm_delete_date)=2009 and extract(month from pgm.crpgm_delete_date)=04))
group by p.partner_code,p.full_name;
-- Récupère le nombre de liaison totale
Cursor C_PARTNER_TOT (LC$partner_code varchar2)is
select count(*) as nb_liaison
from partners p, partner_groups_maintenance pgm,partners_group pg
where p.partner_code=pgm.partner_code
and pgm.group_uid=pg.group_uid
and pg.group_type='LEGALGRP'
and pgm.partner_code=LC$partner_code;
--Lors du "move to", récupère l'ancien UO
Cursor C_MOVE_FROM(LC$partner_code varchar2) is
select pg.group_code,pg.description,pgm.crpgm_delete_user,pgm.crpgm_delete_date--,pg.description
from partner_groups_maintenance pgm,partners_group pg
where pgm.group_uid=pg.group_uid
and pgm.crpgm_delete_flag='Y'
and pg.group_type='LEGALGRP'
and pgm.partner_code=LC$partner_code
and pgm.crpgm_delete_date= (select max(pgm1.crpgm_delete_date)
from partner_groups_maintenance pgm1,partners_group pg1
where pgm1.group_uid=pg1.group_uid
and pgm1.crpgm_delete_flag='Y'
and pg1.group_type='LEGALGRP'
and pgm1.partner_code=LC$partner_code);
-- Lors du "move to", récupère le nouvel UO
Cursor C_MOVE_TO(LC$partner_code varchar2) is
select pg.group_code,pg.description,pgm.user_id,pgm.date_stamp--,pg.description
from partner_groups_maintenance pgm,partners_group pg
where pgm.group_uid=pg.group_uid
and pgm.crpgm_delete_flag='N'
and pg.group_type='LEGALGRP'
and pgm.partner_code=LC$partner_code;
--------------------------------------------------------------------------------------------------------------------------------------------
-- Début du programme
--------------------------------------------------------------------------------------------------------------------------------------------
begin
dbms_output.put_line('action;partner_code;partner_name;user;date_action;UO_from;UO_description;UO_to;UO_description');
-- parcours de tous les partners de la table partner_groups_maintenance et calcul du nb de liaison pour une période
for part_periode in C_PARTNER_PER loop
-- calcul pour chacun de ces partners du nb total de liaison
declare
LC$FROM varchar2(50);
LC$DESC_FROM varchar2(50);
LC$USER_FROM varchar2(50);
DC$DATE_FROM date;
LC$TO varchar2(50):=null;
LC$DESC_TO varchar2(50):=null;
LC$USER_TO varchar2(50):=null;
DC$DATE_TO date;
begin
open C_PARTNER_TOT(part_periode.partner_code);
fetch C_PARTNER_TOT into LC$NB_LIAISON_TOTALE;
close C_PARTNER_TOT;
-- ouverture du curseur pour touver le to
open C_MOVE_TO(part_periode.partner_code);
fetch C_MOVE_TO into LC$TO,LC$DESC_TO,LC$USER_TO,DC$DATE_TO;
close C_MOVE_TO;
-- ouverture du curseur pour touver le from
open C_MOVE_FROM(part_periode.partner_code);
fetch C_MOVE_FROM into LC$FROM,LC$DESC_FROM,LC$USER_FROM,DC$DATE_FROM;
close C_MOVE_FROM;
-- si le nb de liaison sur la période est égale au nb de liaison total et qu'il est égal à 1, "première liaison"
case
when (LC$NB_LIAISON_TOTALE=1 and LC$TO is not null) then
dbms_output.put_line('première liaison;'||part_periode.partner_code||';'||part_periode.full_name||';'
||LC$USER_TO||';'||DC$DATE_TO||';;;'||LC$TO||';'||LC$DESC_TO||';'||LC$NB_LIAISON_TOTALE);
-- si différents (donc supérieur à 1)
when (LC$TO is null) then
dbms_output.put_line('partner dégroupé;'||part_periode.partner_code||';'||part_periode.full_name||';'
||LC$USER_FROM||';'||DC$DATE_FROM||';'||LC$FROM||';'||LC$DESC_FROM);
when (LC$NB_LIAISON_TOTALE>1 and LC$TO is not null)then
dbms_output.put_line('move to,'||part_periode.partner_code||';'||part_periode.full_name||';'||LC$USER_TO||';'||DC$DATE_TO
||';'||LC$FROM||';'||LC$DESC_FROM||';'||LC$TO||';'||LC$DESC_TO||';'||LC$NB_LIAISON_TOTALE);
end case;
end;
end loop;
end; |
Partager