module.sql 3.08 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
CREATE TABLE SYNC_LOG
(
  ID NUMBER(*, 0) NOT NULL
, DATE_SYNC TIMESTAMP(6) NOT NULL
, MESSAGE CLOB NOT NULL
, TABLE_NAME VARCHAR2(30 CHAR)
, SOURCE_CODE VARCHAR2(200 CHAR)
, CONSTRAINT SYNC_LOG_PK PRIMARY KEY (ID)
  USING INDEX (CREATE UNIQUE INDEX SYNC_LOG_PK ON SYNC_LOG (ID ASC)) ENABLE
);

/

create or replace PACKAGE UNICAEN_IMPORT AS

  PROCEDURE set_current_user(p_current_user IN INTEGER);
  FUNCTION get_current_user return INTEGER;

  FUNCTION get_current_annee RETURN INTEGER;
  PROCEDURE set_current_annee (p_current_annee INTEGER);

  FUNCTION get_sql_criterion( table_name varchar2, sql_criterion VARCHAR2 ) RETURN CLOB;
  PROCEDURE SYNC_LOG( message CLOB, table_name VARCHAR2 DEFAULT NULL, source_code VARCHAR2 DEFAULT NULL );

  -- AUTOMATIC GENERATION --

  -- END OF AUTOMATIC GENERATION --
END UNICAEN_IMPORT;

/

create or replace PACKAGE BODY UNICAEN_IMPORT AS

  v_current_user INTEGER;
  v_current_annee INTEGER;



  FUNCTION get_current_user RETURN INTEGER IS
  BEGIN
    IF v_current_user IS NULL THEN
      v_current_user := NULL; -- A remplacer par l'utilisateur (ID de la table USER) qui sera le créateur ou le modificateur des données
    END IF;
    RETURN v_current_user;
  END get_current_user;

  PROCEDURE set_current_user (p_current_user INTEGER) is
  BEGIN
    v_current_user := p_current_user;
  END set_current_user;



  FUNCTION get_current_annee RETURN INTEGER IS
  BEGIN
    IF v_current_annee IS NULL THEN
      v_current_annee := NULL; -- A remplacer par l'année d'import souhaitée (si vous avez de l'annualisation de prévue dans votre BDD)
    END IF;
    RETURN v_current_annee;
  END get_current_annee;

  PROCEDURE set_current_annee (p_current_annee INTEGER) IS
  BEGIN
    v_current_annee := p_current_annee;
  END set_current_annee;



  FUNCTION get_sql_criterion( table_name varchar2, sql_criterion VARCHAR2 ) RETURN CLOB IS
  BEGIN
    IF sql_criterion <> '' OR sql_criterion IS NOT NULL THEN
      RETURN sql_criterion;
    END IF;
    RETURN '';
    /* Exemple d'usage :

    RETURN CASE table_name
      WHEN 'INTERVENANT' THEN -- Met à jour toutes les données sauf le statut, qui sera traité à part
        'WHERE IMPORT_ACTION IN (''delete'',''update'',''undelete'')'

      WHEN 'AFFECTATION_RECHERCHE' THEN
        'WHERE INTERVENANT_ID IS NOT NULL'

      WHEN 'ADRESSE_INTERVENANT' THEN
        'WHERE INTERVENANT_ID IS NOT NULL'

      WHEN 'ELEMENT_TAUX_REGIMES' THEN
        'WHERE IMPORT_ACTION IN (''delete'',''insert'',''undelete'')'

      ELSE
        ''
    END;*/
  END;



  PROCEDURE SYNC_LOG( message CLOB, table_name VARCHAR2 DEFAULT NULL, source_code VARCHAR2 DEFAULT NULL ) IS
  BEGIN
    INSERT INTO OSE.SYNC_LOG("ID","DATE_SYNC","MESSAGE","TABLE_NAME","SOURCE_CODE") VALUES (SYNC_LOG_ID_SEQ.NEXTVAL, SYSDATE, message,table_name,source_code);
  END SYNC_LOG;



  FUNCTION IN_COLUMN_LIST( VALEUR VARCHAR2, CHAMPS CLOB ) RETURN NUMERIC IS
  BEGIN
    IF REGEXP_LIKE(CHAMPS, '(^|,)[ \t\r\n\v\f]*' || VALEUR || '[ \t\r\n\v\f]*(,|$)') THEN RETURN 1; END IF;
    RETURN 0;
  END;


  -- AUTOMATIC GENERATION --

  -- END OF AUTOMATIC GENERATION --
END UNICAEN_IMPORT;