Connecteurs OSE.sql 62.1 KB
Newer Older
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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335

  ---------------------------------------------------------------
  --|                 *** Connecteurs OSE ***                 |--
  ---------------------------------------------------------------
  --|                                                         |--
  --|  Auteur : Laurent Lécluse (laurent.lecluse@unicaen.fr)  |--
  --|  Actualisé le 14 février 2018                           |--
  --|                                                         |--
  ---------------------------------------------------------------



--
-- Informations diverses.
-- ______________________
--
-- Le module Import de OSE se charge se faire le lien avec la base de données du logiciel.
-- Pour cela, il génère des vues différentielles.
-- Ces dernières permettent de déterminer les différences entre les données fournies par les vues sources et les tables correspondantes.
-- Il gérère également des procédures de mise à jour qui vont se baser sur les vues différentielles pour mettre à jour OSE.
-- En cas de modification d'une vue source, il faut donc procéder à la mise à jour des vues et procédures d'import.
-- Une interface d'administration (menu Administration / Import) vous permettra de :
-- - visualiser le différentiel des données entre vos sources et OSE, et de mettre à jour l'application au cas par cas
-- - gérer vos différentes sources de données
-- - visualiser (page Branchement) les tables synchronisables de OSE et leurs spécifications (utile de nouveaux connecteurs une l'adaptation de ceux existants)
-- - mettre à jour les vues et les procédures d'import
--
-- Le présent fichier s'organise en plusieurs parties:
-- 1 : RH et divers avec HARPEGE
-- 2 : l'offre de formation avec APOGEE et FCA MANAGER.
-- 3 : comptabilité analytique avec SIFAC
--
-- Chaque requête est à adapter selon vos besoins.
-- Les connecteurs ne seront pas "écrasés" ou impactés par les futures mises à jour de OSE (sauf évolution de l'architecture
-- du logiciel, auquel cas vous serez prévenu et invité à adapter votre connecteur avant tout nouveau déploiement).
--
-- Certaines tables peuvent avoir plusieurs sources de données. Par exemple, pour l'offre de formation, des éléments
-- peuvent aussi bien venir d'Apogée que de FCA Manager, mais aussi être créées en local dans OSE.
-- Certaines tables contiennent des données calculées sur la base d'autres données présentes dans OSE.
-- La source 'Calcul' a été créée pour identifier ces données.
-- C'est par exemple le cas pour les tables TYPE_INTERVENTION_EP, TYPE_MODULATEUR_EP et CENTRE_COUT_STRUCTURE
-- Les données partent donc de OSE pour retourner vers OSE, après transformation
-- Source de données à ne pas confondre avec la source OSE qui concerne les données saisies directement dans les tables
-- correspondantes. Ces données ne sont jamais synchronisées puisques saisies localement.
--
--
--
-- Informations sur l'architecture des connecteurs.
-- ________________________________________________
--
-- Un connecteur est composé d'au moins deux parties :
-- 1 : la requête qui va permettre de remonter les données selon le schéma OSE
--     Cette requête peut s'apppuyer le cas échéant sur d'autres dispositifs (vues matérialisées, scripts de peuplement de tables, etc)
--     Pour les identifiants, si le champ fait référence à une autre table, alors on pourra fournir une valeur qui permettra de retrouver ensuite l'identifiant OSE.
--     On utilisera donc pour convention z_ + nom du champ pour signaler que la données transmise n'est pas celle attendue.
--     Cette requête peut éventuellement être intégrée directement dans la vue source.
--
-- 2 : la vue source, qui fournit à OSE les données nécessaires.
--     Si des champs z_* existent, il convient alors de les exploiter pour retrouver l'identifiant OSE correspondant à leur contenu.
--     Cela se fait le plus souvent à l'aide d'une jointure.
--     Par exemple, on donne U10 dans z_structure_id. Or U10 est le code de la composante IAE.
--     Donc on retourne structure.id si structure.source_code = z_structure_id à l'aide d'une jointure à gauche.
--
--
-- Informations sur le connecteur Apogée.
-- ______________________________________
--
-- Le connecteur pour l'offre de formation est composé de trois parties :
-- 1 : la partie Apogée, avec un script et des tables spécifiques fournis séparément
-- 2 : la partie FCA manager, avec des vues à créer dans FCA Manager et fournies séparément
-- 3 : la partie OSE du connecteur qui est fournie ci-dessous et qui repose sur les deux parties précédentes.


-- Création des sources de données (à adapter à vos besoins)
Insert into SOURCE (ID,CODE,LIBELLE,IMPORTABLE) values (source_id_seq.nextval,'Harpege','Harpège','1');
Insert into SOURCE (ID,CODE,LIBELLE,IMPORTABLE) values (source_id_seq.nextval,'Apogee','Apogée','1');
Insert into SOURCE (ID,CODE,LIBELLE,IMPORTABLE) values (source_id_seq.nextval,'Calcul','Calculée','1');
Insert into SOURCE (ID,CODE,LIBELLE,IMPORTABLE) values (source_id_seq.nextval,'SIFAC','SIFAC','1');
Insert into SOURCE (ID,CODE,LIBELLE,IMPORTABLE) values (source_id_seq.nextval,'FCAManager','FCA Manager','1');



-----------------------------------------------------
-- Pour la partie RH et divers avec HARPEGE
-----------------------------------------------------

-- Vues matérialisées diverses
-- Ces vues matérialisées seront exploitées ensuite par les vues sources
-- Leur but est de rapatrier des donnée en amont dans OSE pour que les vues sources s'exécutent ensuite plus rapidement

-- Pour les intervenants
CREATE MATERIALIZED VIEW MV_INTERVENANT AS
WITH
i AS (
  SELECT -- permet de fusionner les données pour ne conserver qu'une des tuples (code,statut) sans doublons
    code,
    statut,
    MAX(z_discipline_id_cnu)      z_discipline_id_cnu,
    MAX(z_discipline_id_sous_cnu) z_discipline_id_sous_cnu,
    MAX(z_discipline_id_spe_cnu)  z_discipline_id_spe_cnu,
    MAX(z_discipline_id_dis2deg)  z_discipline_id_dis2deg,
    MAX(date_fin) date_fin
  FROM
  (
    SELECT
      i.*, -- permet de ne sélectionner que les données (contrats, etc) se terminant le plus tard possible ou bien sans date de fin
      CASE WHEN COUNT(*) OVER (PARTITION BY code,statut) > 1 THEN
        CASE WHEN COALESCE(date_fin,SYSDATE) = MAX(COALESCE(date_fin,SYSDATE)) OVER (PARTITION BY code,statut) THEN 1 ELSE 0 END
      ELSE 1 END ok2,
      COUNT(*) OVER (PARTITION BY code,statut,date_fin) dc
    FROM
    (
      SELECT
        i.*,
        CASE -- permet de supprimer les données obsolètes ou futures s'il y en a des actuelles (contrat en cours, etc)
          WHEN
            COUNT(*) OVER (PARTITION BY i.code) > 1
            AND MAX(i.actuel) OVER (PARTITION BY i.code) = 1
            AND i.actuel = 0
          THEN 0 ELSE 1 END ok
      FROM
      (
        SELECT
          ca.no_dossier_pers                                 code,
          CASE -- lien entre le contrat de travail Harpège et le statut d'intervenant OSE
            WHEN ct.c_type_contrat_trav IN ('MC','MA')                THEN 'ASS_MI_TPS'
            WHEN ct.c_type_contrat_trav IN ('AT')                     THEN 'ATER'
            WHEN ct.c_type_contrat_trav IN ('AX')                     THEN 'ATER_MI_TPS'
            WHEN ct.c_type_contrat_trav IN ('DO')                     THEN 'DOCTOR'
            WHEN ct.c_type_contrat_trav IN ('GI','PN','ED')           THEN 'ENS_CONTRACT'
            WHEN ct.c_type_contrat_trav IN ('LT','LB')                THEN 'LECTEUR'
            WHEN ct.c_type_contrat_trav IN ('MB','MP')                THEN 'MAITRE_LANG'
            WHEN ct.c_type_contrat_trav IN ('PT')                     THEN 'HOSPITALO_UNIV'
            WHEN ct.c_type_contrat_trav IN ('C3','CA','CB','CD','CS','HA','HD','HS','MA','S3','SX','SW','SY','SZ','VA') THEN 'BIATSS'
            WHEN ct.c_type_contrat_trav IN ('CU','AH','CG','MM','PM','IN','DN','ET') THEN 'NON_AUTORISE'
            ELSE 'AUTRES'
          END                                                statut,
          ca.c_section_cnu                                   z_discipline_id_cnu,
          ca.c_sous_section_cnu                              z_discipline_id_sous_cnu,
          ca.c_specialite_cnu                                z_discipline_id_spe_cnu,
          ca.c_disc_second_degre                             z_discipline_id_dis2deg,
          COALESCE(ca.d_fin_execution,ca.d_fin_contrat_trav) date_fin,
          CASE WHEN
            SYSDATE BETWEEN ca.d_deb_contrat_trav-1 AND COALESCE(ca.d_fin_execution,ca.d_fin_contrat_trav,SYSDATE)+1
          THEN 1 ELSE 0 END                                  actuel
        FROM
          contrat_avenant@harpprod ca
          JOIN contrat_travail@harpprod ct ON ct.no_dossier_pers = ca.no_dossier_pers AND ct.no_contrat_travail = ca.no_contrat_travail
        WHERE -- on sélectionne les données même 6 mois avant et 6 mois après
          SYSDATE BETWEEN ca.d_deb_contrat_trav-184 AND COALESCE(ca.d_fin_execution,ca.d_fin_contrat_trav,SYSDATE)+184

        UNION

        SELECT
          a.no_dossier_pers                                  code,
          CASE -- lien entre le type de population Harpège et le statut d'intervenant OSE
            WHEN c.c_type_population IN ('DA','OA','DC')              THEN 'ENS_2ND_DEG'
            WHEN c.c_type_population IN ('SA')                        THEN 'ENS_CH'
            WHEN c.c_type_population IN ('AA','AC','BA','IA','MA')    THEN 'BIATSS'
            WHEN c.c_type_population IN ('MG','SB')                   THEN 'HOSPITALO_UNIV'
            ELSE 'AUTRES'
          END                                                statut,
          psc.c_section_cnu                                  z_discipline_id_cnu,
          psc.c_sous_section_cnu                             z_discipline_id_sous_cnu,
          psc.c_specialite_cnu                               z_discipline_id_spe_cnu,
          pss.c_disc_second_degre                            z_discipline_id_dis2deg,
          a.d_fin_affectation                                date_fin,
          CASE WHEN
            SYSDATE BETWEEN a.d_deb_affectation-1 AND COALESCE(a.d_fin_affectation,SYSDATE)+1
          THEN 1 ELSE 0 END                                  actuel
        FROM
          affectation@harpprod a
          LEFT JOIN carriere@harpprod c ON c.no_dossier_pers = a.no_dossier_pers AND c.no_seq_carriere = a.no_seq_carriere
          LEFT JOIN periodes_sp_cnu@harpprod    psc                ON psc.no_dossier_pers = a.no_dossier_pers AND psc.no_seq_carriere = a.no_seq_carriere AND COALESCE(a.d_fin_affectation,SYSDATE) BETWEEN COALESCE(psc.d_deb,SYSDATE) AND COALESCE(psc.d_fin,SYSDATE)
          LEFT JOIN periodes_sp_sd_deg@harpprod pss                ON pss.no_dossier_pers = a.no_dossier_pers AND pss.no_seq_carriere = a.no_seq_carriere AND COALESCE(a.d_fin_affectation,SYSDATE) BETWEEN COALESCE(pss.d_deb,SYSDATE) AND COALESCE(pss.d_fin,SYSDATE)
        WHERE -- on sélectionne les données même 6 mois avant et 6 mois après
          SYSDATE BETWEEN a.d_deb_affectation-184 AND COALESCE(a.d_fin_affectation,SYSDATE)+184

        UNION

        SELECT
          ch.no_individu                                     code,
          'AUTRES'                                           statut, -- pas de statut de défini ici
          ch.c_section_cnu                                   z_discipline_id_cnu,
          ch.c_sous_section_cnu                              z_discipline_id_sous_cnu,
          NULL                                               z_discipline_id_spe_cnu,
          ch.c_disc_second_degre                             z_discipline_id_dis2deg,
          ch.d_fin_str_trav                                  date_fin,
          CASE WHEN
            SYSDATE BETWEEN ch.d_deb_str_trav-1 AND COALESCE(ch.d_fin_str_trav,SYSDATE)+1
          THEN 1 ELSE 0 END                                  actuel
        FROM
          chercheur@harpprod ch
        WHERE -- on sélectionne les données même 6 mois avant et 6 mois après
          SYSDATE BETWEEN ch.d_deb_str_trav-184 AND COALESCE(ch.d_fin_str_trav,SYSDATE)+184
      ) i
    ) i WHERE ok = 1
  )i WHERE ok2 = 1 GROUP BY code,statut
),
comptes (no_individu, rank_compte, nombre_comptes, IBAN, BIC) AS (
  SELECT -- récupération des comptes en banque
    i.no_dossier_pers no_individu,
    dense_rank() over(partition by i.no_dossier_pers order by d_creation) rank_compte,
    count(*) over(partition by i.no_dossier_pers)                   nombre_comptes,
    CASE WHEN i.no_dossier_pers IS NOT NULL THEN
      trim( NVL(i.c_pays_iso || i.cle_controle,'FR00') || ' ' ||
      substr(i.c_banque,0,4) || ' ' ||
      substr(i.c_banque,5,1) || substr(i.c_guichet,0,3) || ' ' ||
      substr(i.c_guichet,4,2) || substr(i.no_compte,0,2) || ' ' ||
      substr(i.no_compte,3,4) || ' ' ||
      substr(i.no_compte,7,4) || ' ' ||
      substr(i.no_compte,11) || i.cle_rib) ELSE NULL END            IBAN,
    CASE WHEN i.no_dossier_pers IS NOT NULL THEN i.c_banque_bic || ' ' || i.c_pays_bic || ' ' || i.c_emplacement || ' ' || i.c_branche ELSE NULL END BIC
  from
    individu_banque@harpprod i
)
SELECT
  ltrim(TO_CHAR(individu.no_individu,'99999999'))             code,
  CASE individu.c_civilite WHEN 'M.' THEN 'M.' ELSE 'Mme' END z_civilite_id,
  initcap(individu.nom_usuel)                                 nom_usuel,
  initcap(individu.prenom)                                    prenom,
  initcap(individu.nom_patronymique)                          nom_patronymique,
  individu.d_naissance                                        date_naissance,
  individu.c_pays_naissance                                   z_pays_naissance_id,
  individu.c_dept_naissance                                   z_dep_naissance_id,
  individu.c_commune_naissance                                ville_naissance_code_insee,
  COALESCE(commune.libelle_commune,individu.ville_de_naissance) ville_naissance_libelle,
  individu.c_pays_nationnalite                                z_pays_nationalite_id,
  individu_telephone.no_telephone                             tel_pro,
  individu.no_tel_portable                                    tel_mobile,
  CASE -- Si le mail n'est pas renseigné dans Harpège, alors on va le chercher dans notre LDAP
    WHEN INDIVIDU_E_MAIL.NO_E_MAIL IS NULL THEN
      UCBN_LDAP.hid2mail(individu.no_individu) -- (à adapter en fonction de l'établissement)
    ELSE
      INDIVIDU_E_MAIL.NO_E_MAIL
  END                                                         email,
  i.statut                                                    z_statut_id,
  sc.c_structure_n2                                           z_structure_id,
  ltrim(TO_CHAR(individu.no_individu,'99999999'))             source_code,
  code_insee.no_insee                                         numero_insee,
  TO_CHAR(code_insee.cle_insee)                               numero_insee_cle,
  CASE WHEN code_insee.no_insee IS NULL THEN NULL ELSE 0 END  numero_insee_provisoire,
  comptes.iban                                                iban,
  comptes.bic                                                 bic,
  pbs_divers__cicg.c_grade@harpprod(individu.no_individu, COALESCE(i.date_fin,SYSDATE) ) z_grade_id,
  i.z_discipline_id_cnu                                       z_discipline_id_cnu,
  i.z_discipline_id_sous_cnu                                  z_discipline_id_sous_cnu,
  i.z_discipline_id_spe_cnu                                   z_discipline_id_spe_cnu,
  i.z_discipline_id_dis2deg                                   z_discipline_id_dis2deg,
  utl_raw.cast_to_varchar2((nlssort(to_char(individu.nom_usuel || ' ' || individu.nom_patronymique || ' ' || individu.prenom), 'nls_sort=binary_ai'))) critere_recherche,
  i.date_fin
FROM
                                        i
       JOIN individu@harpprod           individu           ON individu.no_individu           = i.code
  LEFT JOIN MV_UNICAEN_STRUCTURE_CODES  sc                 ON sc.c_structure                 = pbs_divers__cicg.c_structure_globale@harpprod(individu.no_individu, COALESCE(i.date_fin,SYSDATE) )
  LEFT JOIN commune@harpprod            commune            ON individu.c_commune_naissance   = commune.c_commune
  LEFT JOIN individu_e_mail@harpprod    individu_e_mail    ON individu_e_mail.no_individu    = i.code
  LEFT JOIN individu_telephone@harpprod individu_telephone ON individu_telephone.no_individu = i.code AND individu_telephone.tem_tel_principal='O' AND individu_telephone.tem_tel='O'
  LEFT JOIN code_insee@harpprod         code_insee         ON code_insee.no_dossier_pers     = i.code
  LEFT JOIN                             comptes            ON comptes.no_individu            = i.code AND comptes.rank_compte = comptes.nombre_comptes;



-- AFFECTATIONS
CREATE MATERIALIZED VIEW MV_AFFECTATION AS
WITH tmp AS (

  SELECT
    i.nom_usuel || ' ' || INITCAP(i.prenom)         display_name,
    UCBN_LDAP.HID2MAIL(i.no_individu)               email,
    'ldap'                                          password,
    1                                               state,
    UCBN_LDAP.HID2ALIAS(i.no_individu)              username,

    CASE WHEN c_structure = 'UNIV' THEN NULL ELSE c_structure END z_structure_id,
    CASE
      WHEN lc_fonction LIKE '_D30%' OR t.lc_fonction LIKE '_P71%' THEN 'directeur-composante'
      WHEN lc_fonction LIKE '_R00'  OR t.lc_fonction LIKE '_R40%' THEN 'responsable-composante'
      WHEN lc_fonction LIKE '_R00c' OR t.lc_fonction LIKE '_R40%' THEN 'responsable-recherche-labo'
      WHEN c_structure = 'UNIV' AND t.lc_fonction = '_P00' OR t.lc_fonction LIKE '_P10%' OR t.lc_fonction LIKE '_P50%' THEN 'superviseur-etablissement'
      ELSE NULL
    END z_role_id,
    t.c_structure || '_' || t.no_individu || '_' || t.lc_fonction source_code,

    lc_fonction,
    nom_complet, lc_structure, ll_fonction, t.*
  FROM
         ucbn_d2a_respons_struct@harpprod t
    JOIN individu@harpprod                i ON i.no_individu = t.no_individu
  WHERE
    niveau_structure <= 2
    AND SYSDATE BETWEEN t.date_deb_exerc_resp AND NVL(t.date_fin_exerc_resp + 1,SYSDATE)

)
SELECT DISTINCT
  display_name,
  email,
  password,
  state,
  username,
  z_structure_id,
  z_role_id,
  'Harpege' z_source_id,
  MIN( source_code ) source_code
FROM
  tmp
WHERE
  tmp.z_role_id IS NOT NULL
GROUP BY
  display_name,
  email,
  password,
  state,
  username,
  z_structure_id,
  z_role_id;



-- Structures
-- Cette vue matérialisée sert à établir la correspondance entre n'importe qu'elle structure Harpège et sa correspondante de niveau 2 ou 1
-- On "applatit" ainsi les structures pour n'avoir que du niveau 2
CREATE MATERIALIZED VIEW MV_UNICAEN_STRUCTURE_CODES AS
SELECT
  s9.c_structure c_structure,
  COALESCE(s4.c_structure, s5.c_structure, s6.c_structure, s7.c_structure, s8.c_structure, s9.c_structure) c_structure_n2
FROM
  structure@harpprod s9
  LEFT JOIN structure@harpprod s8 ON s8.c_structure = s9.c_structure_pere AND s8.c_structure <> 'UNIV'
  LEFT JOIN structure@harpprod s7 ON s7.c_structure = s8.c_structure_pere AND s7.c_structure <> 'UNIV'
  LEFT JOIN structure@harpprod s6 ON s6.c_structure = s7.c_structure_pere AND s6.c_structure <> 'UNIV'
  LEFT JOIN structure@harpprod s5 ON s5.c_structure = s6.c_structure_pere AND s5.c_structure <> 'UNIV'
  LEFT JOIN structure@harpprod s4 ON s4.c_structure = s5.c_structure_pere AND s4.c_structure <> 'UNIV';


336
337
338
339
340
341
342
343
344
345
346
-- Pour pouvoir associer des disciplines aux éléments pédagogiques dans OSE
CREATE TABLE unicaen_element_discipline (
  element_source_code      VARCHAR2(30 CHAR) NOT NULL,
  discipline_source_code   VARCHAR2(30 CHAR) NOT NULL
)
LOGGING;

ALTER TABLE unicaen_element_discipline
  ADD CONSTRAINT unicaen_element_discipline_pk
  PRIMARY KEY ( element_source_code, discipline_source_code );

347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547


-- SRC_ADRESSE_INTERVENANT
CREATE OR REPLACE FORCE VIEW SRC_ADRESSE_INTERVENANT AS
WITH harpege_query AS (
  SELECT
    LTRIM(to_char(no_individu,'99999999'))                  z_intervenant_id,
    TRIM(telephone_domicile)                                tel_domicile,
    TRIM(UPPER(habitant_chez))                              mention_complementaire,
    no_voie || CASE bis_ter
      WHEN 'B' THEN ' BIS'
      WHEN 'T' THEN ' TER'
      WHEN 'Q' THEN ' QUATER'
      WHEN 'C' THEN ' QUINQUIES'
      ELSE ''
    END                                                     no_voie,
    UPPER(TRIM(v.l_voie) || ' ' || TRIM(nom_voie))          nom_voie,
    localite                                                localite,
    coalesce( cp_etranger, code_postal )                    code_postal,
    trim(ville)                                             ville,
    pays.c_pays                                             pays_code_insee,
    pays.ll_pays                                            pays_libelle,
    'Harpege'                                               z_source_id,
    to_char(id_adresse_perso)                               source_code
  FROM
              adresse_personnelle@harpprod adresse
    LEFT JOIN pays@harpprod                   pays ON pays.c_pays = adresse.c_pays
    LEFT JOIN voirie@harpprod                    v ON v.c_voie = adresse.c_voie
  WHERE
    adresse.d_creation <= sysdate
    AND tem_adr_pers_princ = 'O' -- on n'importe que les adresses principales
)
SELECT
  i.id                                                      intervenant_id,
  hq.tel_domicile                                           tel_domicile,
  hq.mention_complementaire                                 mention_complementaire,
  hq.no_voie                                                no_voie,
  hq.nom_voie                                               nom_voie,
  hq.localite                                               localite,
  hq.code_postal                                            code_postal,
  hq.ville                                                  ville,
  hq.pays_code_insee                                        pays_code_insee,
  hq.pays_libelle                                           pays_libelle,
  src.id                                                    source_id,
  hq.source_code || '_' || unicaen_import.get_current_annee source_code
FROM
            harpege_query  hq
       JOIN source        src ON src.code = hq.z_source_id
  LEFT JOIN intervenant     i ON i.source_code = hq.z_intervenant_id
                             AND i.annee_id = unicaen_import.get_current_annee;



-- SRC_ADRESSE_STRUCTURE
CREATE OR REPLACE FORCE VIEW SRC_ADRESSE_STRUCTURE AS
WITH harpege_query AS (
  SELECT
    z_structure_id,
    principale,
    telephone,
    no_voie,
    nom_voie,
    localite,
    code_postal,
    ville,
    pays_code_insee,
    pays_libelle,
    'Harpege' z_source_id,
    source_code
  FROM (

    SELECT DISTINCT
      ls.c_structure                                                  z_structure_id,
      CASE ls.tem_local_principal WHEN 'O' THEN 1 ELSE 0 END          principale,
      ls.no_telephone                                                 telephone,
      no_voie_a || CASE bis_ter_a
        WHEN 'B' THEN ' BIS'
        WHEN 'T' THEN ' TER'
        WHEN 'Q' THEN ' QUATER'
        WHEN 'C' THEN ' QUINQUIES'
        ELSE ''
      END                                                             no_voie,
      UPPER(TRIM(V.l_voie) || ' ' || TRIM(nom_voie_a))                nom_voie,
      localite_a                                                      localite,
      COALESCE( cp_etranger_admin, code_postal_a )                    code_postal,
      TRIM(ville_a)                                                   ville,
      pays.c_pays                                                     pays_code_insee,
      pays.ll_pays                                                    pays_libelle,
      to_char(aa.id_adresse_admin) || '_' || ls.c_structure           source_code,
      COUNT(*) OVER(PARTITION BY aa.id_adresse_admin,ls.c_structure)  doublons
    FROM
                adresse_administrat@harpprod    aa
           JOIN local@harpprod                   l ON l.id_adresse_admin = aa.id_adresse_admin
           JOIN localisation_structure@harpprod ls ON ls.c_local = l.c_local
      LEFT JOIN pays@harpprod                 pays ON pays.c_pays = aa.c_pays
      LEFT JOIN voirie@harpprod                  v ON v.c_voie = aa.c_voie
    WHERE
      SYSDATE BETWEEN COALESCE(aa.d_deb_val, SYSDATE) AND COALESCE(aa.d_fin_val, SYSDATE)
    ) tmp1

  WHERE
    doublons = 1 OR principale = 1
)
SELECT
  s.id                structure_id,
  hq.principale       principale,
  hq.telephone        telephone,
  hq.no_voie          no_voie,
  hq.nom_voie         nom_voie,
  hq.localite         localite,
  hq.code_postal      code_postal,
  hq.ville            ville,
  hq.pays_code_insee  pays_code_insee,
  hq.pays_libelle     pays_libelle,
  src.id              source_id,
  hq.source_code      source_code
FROM
       harpege_query hq
  JOIN source       src ON src.code = hq.z_source_id
  JOIN structure      s ON s.source_code = hq.z_structure_id;



-- SRC_AFFECTATION
CREATE OR REPLACE FORCE VIEW SRC_AFFECTATION AS
SELECT
  s.id          structure_id,
  u.id          utilisateur_id,
  r.id          role_id,
  src.id        source_id,
  a.source_code source_code
FROM
            mv_affectation a
       JOIN source       src ON src.code = a.z_source_id
  LEFT JOIN utilisateur    u ON u.username = a.username
  LEFT JOIN structure      s ON s.source_code = a.z_structure_id
  LEFT JOIN role           r ON r.code = a.z_role_id
WHERE
  s.id IS NULL -- rôle global
  OR (
    (
      EXISTS (SELECT * FROM element_pedagogique ep WHERE ep.structure_id = s.id) -- soit une resp. dans une composante d'enseignement
      OR a.z_role_id IN ('responsable-recherche-labo')                           -- soit un responsable de labo
    )
  );



-- SRC_AFFECTATION_RECHERCHE
CREATE OR REPLACE FORCE VIEW SRC_AFFECTATION_RECHERCHE AS
WITH harpege_query AS (
  SELECT
    to_char(ar.no_dossier_pers)  z_intervenant_id,
    ar.c_structure               z_structure_id,
    'Harpege'                    z_source_id,
    to_char(ar.no_seq_affe_rech) source_code
  FROM
    affectation_recherche@harpprod ar
  WHERE
    SYSDATE BETWEEN ar.d_deb_affe_rech AND COALESCE(ar.d_fin_affe_rech + 1,SYSDATE)
)
SELECT
  i.id                                                      intervenant_id,
  s.id                                                      structure_id,
  src.id                                                    source_id,
  hq.source_code || '_' || unicaen_import.get_current_annee source_code
FROM
            harpege_query              hq
       JOIN source                    src ON src.code = 'Harpege'
  LEFT JOIN intervenant                 i ON i.source_code = hq.z_intervenant_id
                                         AND i.annee_id = unicaen_import.get_current_annee
  LEFT JOIN mv_unicaen_structure_codes sc ON sc.c_structure = hq.z_structure_id
  LEFT JOIN structure                   s ON s.source_code = sc.c_structure_n2;



-- SRC_CORPS
CREATE OR REPLACE FORCE VIEW SRC_CORPS AS
WITH harpege_query AS (
  SELECT
    c.ll_corps  libelle_long,
    c.lc_corps  libelle_court,
    'Harpege'   z_source_id,
    c.c_corps   source_code
  FROM
    corps@harpprod c
  WHERE
    SYSDATE BETWEEN COALESCE(c.d_ouverture_corps,SYSDATE) AND COALESCE(c.d_fermeture_corps+1,SYSDATE)
)
SELECT
  hq.libelle_long  libelle_long,
  hq.libelle_court libelle_court,
  s.id             source_id,
  hq.source_code   source_code
FROM
       harpege_query hq
  JOIN source         s ON s.code = hq.z_source_id;



-- SRC_DEPARTEMENT
548
549
-- Attention : les codes des départements doivent avoir 3 chiffres.
-- Par exemple 014 pour le Calvados, pour Paris 075, etc.
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
CREATE OR REPLACE FORCE VIEW SRC_DEPARTEMENT AS
WITH harpege_query AS (
  SELECT
    c_departement  code,
    ll_departement libelle_long,
    lc_departement libelle_court,
    'Harpege'      z_source_id,
    c_departement  source_code
  FROM
    departement@harpprod d
)
SELECT
  hq.code          code,
  hq.libelle_long  libelle_long,
  hq.libelle_court libelle_court,
  s.id             source_id,
  hq.source_code   source_code
FROM
       harpege_query hq
  JOIN source         s ON s.code = hq.z_source_id;



-- SRC_GRADE
CREATE OR REPLACE FORCE VIEW SRC_GRADE AS
WITH harpege_query AS (
  SELECT
    g.ll_grade  libelle_long,
    g.lc_grade  libelle_court,
    'Harpege'   z_source_id,
    g.c_grade   source_code,
    g.echelle   echelle,
    g.c_corps   z_corps_id
  FROM
    grade@harpprod g
  WHERE
    SYSDATE BETWEEN COALESCE(g.d_ouverture,SYSDATE) AND COALESCE(g.d_fermeture+1,SYSDATE)
)
SELECT
  hq.libelle_long   libelle_long,
  hq.libelle_court  libelle_court,
  s.id              source_id,
  hq.source_code    source_code,
  hq.echelle        echelle,
  c.id              corps_id
FROM
       harpege_query hq
  JOIN source         s ON s.code        = hq.z_source_id
  JOIN corps          c ON c.source_code = hq.z_corps_id;



-- SRC_INTERVENANT
-- Liste de tous les intervenants pouvant potentiellement saisir des services dans OSE
-- La table "chercheur" est parcourus car chez nous les comptes d'accès au système d'information sont listés dans cette table.
-- Nous retrouvons donc ici tous les comptes d'accès au système d'information valides hormis des comptes invités pour usages spécifiques
-- car tout le monde peut potentiellement déclarer des services.
-- Dans cette vue, on synchronise toutes les données des intervenants de l'année en cours, et la plupart des onnées des intervenants de l'année prédédente
CREATE OR REPLACE FORCE VIEW SRC_INTERVENANT AS
WITH srci as (
SELECT
  i.code,
  c.id civilite_id,
  i.nom_usuel, i.prenom, i.nom_patronymique,
  COALESCE(i.date_naissance,TO_DATE('2099-01-01','YYYY-MM-DD')) date_naissance,
  pnaiss.id pays_naissance_id,
  dep.id dep_naissance_id,
  i.ville_naissance_code_insee,  i.ville_naissance_libelle,
  pnat.id pays_nationalite_id,
  i.tel_pro, i.tel_mobile, i.email,
  si.id statut_id, si.source_code statut_code,
  s.id structure_id,
  src.id source_id, i.source_code,
  i.numero_insee, i.numero_insee_cle, i.numero_insee_provisoire,
  i.iban, i.bic,
  g.id grade_id,
  NVL( d.id, d99.id ) discipline_id,
  i.critere_recherche,
  COALESCE (si.ordre,99999) ordre,
  MIN(COALESCE (si.ordre,99999)) OVER (PARTITION BY i.source_code) min_ordre
FROM
            mv_intervenant i
       JOIN source        src ON src.code = 'Harpege'
  LEFT JOIN civilite        c ON c.libelle_court = i.z_civilite_id
  LEFT JOIN structure       s ON s.source_code = i.z_structure_id
  LEFT JOIN statut_intervenant si ON si.source_code = i.z_statut_id
  LEFT JOIN grade           g ON g.source_code = i.z_grade_id
  LEFT JOIN pays       pnaiss ON pnaiss.source_code = i.z_pays_naissance_id
  LEFT JOIN pays         pnat ON pnat.source_code = i.z_pays_nationalite_id
  LEFT JOIN departement   dep ON dep.source_code = i.z_dep_naissance_id
  LEFT JOIN discipline d99 ON d99.source_code = '99'
  LEFT JOIN discipline d ON
    d.histo_destruction IS NULL
    AND 1 = CASE WHEN -- si rien n'ac été défini

      COALESCE( i.z_discipline_id_cnu, i.z_discipline_id_sous_cnu, i.z_discipline_id_spe_cnu, i.z_discipline_id_dis2deg ) IS NULL
      AND d.source_code = '00'

    THEN 1 WHEN -- si une CNU ou une spécialité a été définie...

      COALESCE( i.z_discipline_id_cnu, i.z_discipline_id_sous_cnu, z_discipline_id_spe_cnu ) IS NOT NULL

    THEN CASE WHEN -- alors on teste par les sections CNU et spécialités

      (
           ',' || d.CODES_CORRESP_2 || ',' LIKE '%,' || i.z_discipline_id_cnu || NVL(i.z_discipline_id_sous_cnu,'') || ',%'
        OR ',' || d.CODES_CORRESP_2 || ',' LIKE '%,' || i.z_discipline_id_cnu || NVL(i.z_discipline_id_sous_cnu,'00') || ',%'
      )
      AND ',' || NVL(d.CODES_CORRESP_3,'000') || ',' LIKE  '%,' || NVL(CASE WHEN d.CODES_CORRESP_3 IS NOT NULL THEN z_discipline_id_spe_cnu ELSE NULL END,'000') || ',%'

    THEN 1 ELSE 0 END ELSE CASE WHEN -- sinon on teste par les disciplines du 2nd degré

      i.z_discipline_id_dis2deg IS NOT NULL
      AND ',' || NVL(d.CODES_CORRESP_4,'') || ',' LIKE  '%,' || i.z_discipline_id_dis2deg || ',%'

    THEN 1 ELSE 0 END END -- fin du test
)
SELECT
  i.code code, lpad(i.code, 8, '0') utilisateur_code,
  i.civilite_id,
  i.nom_usuel, i.prenom, i.nom_patronymique,
  i.date_naissance,
  i.pays_naissance_id,
  i.dep_naissance_id,
  i.ville_naissance_code_insee,  i.ville_naissance_libelle,
  i.pays_nationalite_id,
  i.tel_pro, i.tel_mobile, i.email,
  COALESCE(
    isai.statut_id,
    CASE WHEN i.statut_code = 'AUTRES' AND d.statut_id IS NOT NULL THEN d.statut_id ELSE i.statut_id END
  ) statut_id,
  i. structure_id,
  i.source_id, i.source_code,
  i.numero_insee, i.numero_insee_cle, i.numero_insee_provisoire,
  i.iban, i.bic,
  i.grade_id,
  i.discipline_id,
  unicaen_import.get_current_annee annee_id,
  i.critere_recherche
FROM
  srci i
  LEFT JOIN intervenant           i2 ON i2.source_code = i.source_code AND i2.annee_id = unicaen_import.get_current_annee
  LEFT JOIN intervenant_saisie  isai ON isai.intervenant_id = i2.id
  LEFT JOIN dossier               d  ON d.intervenant_id = i2.id AND d.histo_destruction IS NULL
WHERE
  i.ordre = i.min_ordre

UNION ALL

SELECT
  i.code code, lpad(i.code, 8, '0') utilisateur_code,
  i.civilite_id,
  i.nom_usuel, i.prenom, i.nom_patronymique,
  i.date_naissance,
  i.pays_naissance_id,
  i.dep_naissance_id,
  i.ville_naissance_code_insee,  i.ville_naissance_libelle,
  i.pays_nationalite_id,
  i.tel_pro, i.tel_mobile, i.email,
  COALESCE(i2.statut_id,i.statut_id) statut_id,
  COALESCE(i2.structure_id,i.structure_id) structure_id,
  i.source_id, i.source_code,
  i.numero_insee, i.numero_insee_cle, i.numero_insee_provisoire,
  i.iban, i.bic,
  i.grade_id,
  i.discipline_id,
  unicaen_import.get_current_annee - 1 annee_id,
  i.critere_recherche
FROM
  srci i
  LEFT JOIN intervenant           i2 ON i2.source_code = i.source_code AND i2.annee_id = unicaen_import.get_current_annee - 1
WHERE
  i.ordre = i.min_ordre;



-- SRC_PAYS
CREATE OR REPLACE FORCE VIEW SRC_PAYS AS
SELECT
  ll_pays                                                 libelle_long,
  coalesce(lc_pays,ll_pays)                               libelle_court,
  coalesce(d_deb_val, TO_DATE('1900/01/01','YYYY/MM/DD')) validite_debut,
  d_fin_val                                               validite_fin,
  decode(tem_ue, 'O', 1, 0)                               temoin_ue,
  s.id                                                    source_id,
  c_pays                                                  source_code
FROM
  pays@harpprod p
  JOIN source s ON s.code = 'Harpege';



-- SRC_STRUCTURE
CREATE OR REPLACE FORCE VIEW SRC_STRUCTURE AS
WITH harpege_query AS (
  SELECT
    str.c_structure  code,
    str.lc_structure libelle_court,
    str.ll_structure libelle_long,
    'Harpege'        z_source_id,
    str.c_structure  source_code
  FROM
    structure@harpprod str
  WHERE
    SYSDATE BETWEEN str.date_ouverture AND COALESCE( str.date_fermeture, SYSDATE )
    AND (str.c_structure = 'UNIV' OR str.c_structure_pere = 'UNIV') -- UNIV = structure "Université" de niveau 1
)
SELECT
  hq.code          code,
  hq.libelle_court libelle_court,
  hq.libelle_long  libelle_long,
  src.id           source_id,
  hq.source_code   source_code
FROM
       harpege_query hq
  JOIN source       src ON src.code = hq.z_source_id;





-----------------------------------------------------
-- Pour l'offre de formation avec APOGEE et FCA MANAGER
-----------------------------------------------------

-- SRC_CHEMIN_PEDAGOGIQUE
CREATE OR REPLACE FORCE VIEW SRC_CHEMIN_PEDAGOGIQUE AS
SELECT
  elp.id                                                               element_pedagogique_id,
  etp.id                                                               etape_id,
  ROW_NUMBER() OVER (PARTITION BY etp.id, aq.annee_id ORDER BY ROWNUM) ordre,
  s.id                                                                 source_id,
  aq.source_code || '_' || aq.annee_id                                 source_code
FROM
            ose_chemin_pedagogique@apoprod aq
       JOIN source                          s ON s.code = 'Apogee'
  LEFT JOIN element_pedagogique           elp ON elp.source_code = aq.z_element_pedagogique_id
                                             AND elp.annee_id = TO_NUMBER(aq.annee_id)
  LEFT JOIN etape                         etp ON etp.source_code = aq.z_etape_id
                                             AND etp.annee_id = TO_NUMBER(aq.annee_id)

UNION

SELECT
  elp.id                                                               element_pedagogique_id,
  etp.id                                                               etape_id,
  ROW_NUMBER() OVER (PARTITION BY etp.id, fq.annee_id ORDER BY ROWNUM) ordre,
  s.id                                                                 source_id,
  fq.source_code || '_' || fq.annee_id                                 source_code
FROM
            fca.ose_chemin_pedagogique@fcaprod fq
       JOIN source                              s ON s.code = 'FCAManager'
  LEFT JOIN element_pedagogique               elp ON elp.source_code = fq.z_element_pedagogique_id
                                                 AND elp.annee_id = TO_NUMBER(fq.annee_id)
  LEFT JOIN etape                             etp ON etp.source_code = fq.z_etape_id
                                                 AND etp.annee_id = TO_NUMBER(fq.annee_id);



-- SRC_EFFECTIFS
CREATE OR REPLACE FORCE VIEW "SRC_EFFECTIFS" ("ELEMENT_PEDAGOGIQUE_ID", "ANNEE_ID", "FI", "FC", "FA", "SOURCE_ID", "SOURCE_CODE") AS
SELECT
  ep.id                                           element_pedagogique_id,
  to_number(e.annee_id)                           annee_id,
  e.effectif_fi                                   fi,
  e.effectif_fc                                   fc,
  e.effectif_fa                                   fa,
  s.id                                            source_id,
  e.annee_id || '-' || e.z_element_pedagogique_id source_code
FROM
       ose_element_effectifs@apoprod e
  JOIN source                        s ON s.code = 'Apogee'
  LEFT JOIN element_pedagogique     ep ON ep.source_code = e.z_element_pedagogique_id
                                      AND ep.annee_id = to_number(e.annee_id);



-- SRC_ELEMENT_PEDAGOGIQUE
CREATE OR REPLACE FORCE VIEW SRC_ELEMENT_PEDAGOGIQUE AS
WITH apogee_query AS (
  SELECT
    ep.source_code code,
    ep.libelle,
    ep.z_etape_id,
    ep.z_structure_id,
    ep.z_periode_id,
    CASE WHEN ep.fi+ep.fa+ep.fc=0 THEN 1 ELSE ep.fi END fi,
    ep.fc,
    ep.fa,
    ep.taux_foad,
    'Apogee' z_source_id,
    ep.source_code,
    TO_NUMBER(ep.annee_id) annee_id,
    ep.z_discipline_id
  FROM
    ose_element_pedagogique@apoprod ep
)
SELECT
  aq.code,
  aq.libelle,
  etp.id etape_id,
  str.id structure_id,
  per.id periode_id,
  CASE
    WHEN etr.id IS NOT NULL
    THEN ose_divers.calcul_taux_fi( etr.taux_fi, etr.taux_fc, etr.taux_fa, aq.fi, aq.fc, aq.fa )
    ELSE ose_divers.calcul_taux_fi( aq.fi, aq.fc, aq.fa, aq.fi, aq.fc, aq.fa )
  END taux_fi,
  CASE
    WHEN etr.id IS NOT NULL
    THEN ose_divers.calcul_taux_fc( etr.taux_fi, etr.taux_fc, etr.taux_fa, aq.fi, aq.fc, aq.fa )
    ELSE ose_divers.calcul_taux_fc( aq.fi, aq.fc, aq.fa, aq.fi, aq.fc, aq.fa )
  END taux_fc,
  CASE
    WHEN etr.id IS NOT NULL
    THEN ose_divers.calcul_taux_fa( etr.taux_fi, etr.taux_fc, etr.taux_fa, aq.fi, aq.fc, aq.fa )
    ELSE ose_divers.calcul_taux_fa( aq.fi, aq.fc, aq.fa, aq.fi, aq.fc, aq.fa )
  END taux_fa,
  aq.taux_foad,
  aq.fc,
  aq.fi,
  aq.fa,
  s.id source_id,
  aq.source_code,
  aq.annee_id,
  NVL( d.id, d99.id ) discipline_id
FROM
            apogee_query aq
       JOIN source                      s ON s.code                     = aq.z_source_id
  LEFT JOIN etape                     etp ON etp.source_code            = aq.z_etape_id
                                         AND etp.annee_id               = aq.annee_id
  LEFT JOIN mv_unicaen_structure_codes sc ON sc.c_structure             = aq.z_structure_id
  LEFT JOIN structure                 str ON str.source_code            = sc.c_structure_n2
  LEFT JOIN periode                   per ON per.libelle_court          = aq.z_periode_id
  LEFT JOIN element_pedagogique        ep ON ep.source_code             = aq.source_code
                                         AND ep.annee_id                = aq.annee_id
  LEFT JOIN element_taux_regimes      etr ON etr.element_pedagogique_id = ep.id
                                         AND etr.histo_destruction      IS NULL
  LEFT JOIN discipline                d99 ON d99.source_code            = '99'
  LEFT JOIN discipline                  d ON ',' || d.CODES_CORRESP_1 || ',' LIKE '%,' || NVL(aq.z_discipline_id,'00') || ',%'
                                         AND d.histo_destruction        IS NULL

UNION

SELECT
  ep.code,
  ep.libelle,
  etp.id etape_id,
  str.id structure_id,
  per.id periode_id,
  ep.taux_fi taux_fi,
  ep.taux_fc taux_fc,
  ep.taux_fa taux_fa,
  ep.taux_foad,
  ep.fc,
  ep.fi,
  ep.fa,
  s.id,
  ep.source_code,
  TO_NUMBER(ep.annee_id) annee_id,
  d99.id discipline_id
FROM
            FCA.OSE_element_pedagogique@fcaprod ep
       JOIN source                               s ON s.code            = 'FCAManager'
  LEFT JOIN etape                              etp ON etp.source_code   = ep.z_etape_id
                                                  AND etp.annee_id      = ep.annee_id
  LEFT JOIN MV_UNICAEN_STRUCTURE_CODES          sc ON sc.c_structure    = ep.z_structure_id
  LEFT JOIN structure                          str ON str.source_code   = sc.c_structure_n2
  LEFT JOIN periode                            per ON per.libelle_court = ep.z_periode_id
919
920
  LEFT JOIN unicaen_element_discipline         ued ON ued.element_source_code = ep.source_code
  LEFT JOIN discipline                         d99 ON d99.source_code   = COALESCE( ued.discipline_source_code,'99');
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000



-- SRC_ELEMENT_TAUX_REGIMES
CREATE OR REPLACE FORCE VIEW SRC_ELEMENT_TAUX_REGIMES AS
WITH apogee_query AS (
  SELECT
    e.z_element_pedagogique_id  z_element_pedagogique_id,
    to_number(e.annee_id) + 1   annee_id,
    e.effectif_fi               effectif_fi,
    e.effectif_fc               effectif_fc,
    e.effectif_fa               effectif_fa,
    'Apogee'                    z_source_id,
    TO_NUMBER(e.annee_id) + 1 || '-' || e.z_element_pedagogique_id source_code
  FROM
    ose_element_effectifs@apoprod e
  WHERE
    (e.effectif_fi + e.effectif_fc + e.effectif_fa) > 0
)
SELECT
  ep.id           element_pedagogique_id,
  aq.annee_id     annee_id,
  OSE_DIVERS.CALCUL_TAUX_FI( aq.effectif_fi, aq.effectif_fc, aq.effectif_fa, ep.fi, ep.fc, ep.fa ) taux_fi,
  OSE_DIVERS.CALCUL_TAUX_FC( aq.effectif_fi, aq.effectif_fc, aq.effectif_fa, ep.fi, ep.fc, ep.fa ) taux_fc,
  OSE_DIVERS.CALCUL_TAUX_FA( aq.effectif_fi, aq.effectif_fc, aq.effectif_fa, ep.fi, ep.fc, ep.fa ) taux_fa,
  s.id           source_id,
  aq.source_code source_code
FROM
       apogee_query aq
  JOIN source s ON s.code = aq.z_source_id
  JOIN ELEMENT_PEDAGOGIQUE ep ON ep.source_code = aq.z_element_pedagogique_id AND ep.annee_id = aq.annee_id
WHERE
  NOT EXISTS( -- on évite de remonter des données issus d'autres sources pour le pas risquer de les écraser!!
    SELECT * FROM element_taux_regimes aq_tbl WHERE
      aq_tbl.element_pedagogique_id = ep.id
      AND aq_tbl.source_id <> s.id
  );



-- SRC_ETABLISSEMENT
CREATE OR REPLACE FORCE VIEW SRC_ETABLISSEMENT AS
WITH apogee_query AS (
  SELECT
    e.lib_off_etb libelle,
    e.lic_etb     localisation,
    e.cod_dep     departement,
    'Apogee'      z_source_id,
    e.cod_etb     source_code
  FROM
    etablissement@apoprod e
)
SELECT
  aq.libelle      libelle,
  aq.localisation localisation,
  aq.departement  departement,
  s.id            source_id,
  aq.source_code  source_code
FROM
       apogee_query aq
  JOIN source        s ON s.code = aq.z_source_id;



-- SRC_ETAPE
CREATE OR REPLACE FORCE VIEW SRC_ETAPE AS
SELECT
  e.cod_etp || '_' || e.cod_vrs_vet   code,
  e.libelle                           libelle,
  to_number(e.annee_id)               annee_id,
  tf.id                               type_formation_id,
  to_number(e.niveau)                 niveau,
  e.specifique_echanges               specifique_echanges,
  s.id                                structure_id,
  src.id                              source_id,
  e.source_code                       source_code,
  df.id                               domaine_fonctionnel_id
FROM
            ose_etape@apoprod           e
       JOIN source                    src ON src.code       = 'Apogee'
For faster browsing, not all history is shown. View entire blame