Au Pied Levé - À Main Levée
I-3.2.6. OSMOSE SQL CREATE_BDD
par , 01/04/2020 à 10h30 (288 Affichages)
■ CREATE_BDDAPL-AML est une monographie fragmentée en plusieurs billets pour des raisons de volume.
Un billet SYNOPSIS et un billet SOMMAIRE agrègent tous les billets du blog via des liens hypertextes.
■ ■ ■ SOMMAIRE DU BILLET ■ ■ ■
- CREATE_BDD
sql de création des 93 tables essentielles de la BDD "OSMOSE".
L’application gérant un historique de 4 années et l’année n+1, certaines tables existent en 6 exemplaires. Le sql complet compte 205 tables, soit environ 168.000 caractères. Mais un billet étant limité à 65.536 caractères, le présent sql se limite aux 93 tables es essentielles.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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
336
337
338
339
340
341
342
343
344
345
346
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
548
549
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
919
920
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
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
1265
1266
1267
1268
1269
1270
1271
1272
1273
1274
1275
1276
1277
1278
1279
1280
1281
1282
1283
1284
1285
1286
1287
1288
1289
1290
1291
1292
1293
1294
1295
1296
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
1512
1513
1514
1515
1516
1517
1518
1519
1520
1521
1522
1523
1524
1525
1526
1527
1528
1529
1530
1531
1532
1533
1534
1535
1536
1537
1538
1539
1540
1541
1542
1543
1544
1545
1546
1547
1548
1549
1550
1551
1552
1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
1595
1596
1597
1598
1599
1600
1601
1602
1603
1604
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
1691
1692
1693
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703
1704
1705
1706
1707
1708
1709
1710
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
1769
1770
1771
1772
1773
1774
1775
1776
1777
1778
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
1804
1805
1806
1807
1808
1809
1810
1811
1812
1813
1814
1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
1862
1863
1864
1865
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
1898
1899
1900
1901
1902
1903
1904
1905
1906
1907
1908
1909
1910
1911
1912
1913
1914
1915
1916
1917
1918
1919
1920
1921
1922
1923
1924
1925
1926
1927
1928
1929
1930
1931
1932
1933
1934
1935
1936
1937
1938
1939
1940
1941
1942
1943
1944
1945
1946
1947
1948
1949
1950
1951
1952
1953
1954
1955
1956
1957
1958
1959
1960
1961
1962
1963
1964
1965
1966
1967
1968
1969
1970
1971
1972
1973
1974
1975
1976
1977
1978
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
2038
2039
2040
2041
2042
2043
2044
2045
2046
2047
2048
2049
2050
2051
2052
2053
2054
2055
2056
2057
2058
2059
2060
2061
2062
2063
2064
2065
2066
2067
2068
2069
2070
2071
2072
2073
2074
2075
2076
2077
2078
2079
2080
2081
2082
2083
2084
2085
2086
2087
2088
2089
2090
2091
2092
2093
2094
2095
2096
2097
2098
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
2140
2141
2142
2143
2144
2145
2146
2147
2148
2149
2150
2151
2152
2153
2154
2155
2156
2157
2158
2159
2160
2161
2162
2163
2164
2165
2166
2167
2168
2169
2170
2171
2172
2173
2174
2175
2176
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189
2190
2191
2192
2193
2194
2195
2196
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
2213
2214
2215
2216
2217
2218
2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
2259
2260
2261
2262
2263
2264
2265
2266
2267
2268
2269
2270
2271
2272
2273
2274
2275
2276
2277
2278
2279
2280
2281
2282
2283
2284
2285
2286
2287
2288
2289
2290
2291
2292
2293
2294
2295
2296
2297
2298
2299
2300
2301
2302
2303
2304
2305
2306
2307
2308
2309
2310
2311
2312
2313
2314
2315
2316
2317
2318
2319
2320
2321
2322
2323
2324
2325
2326
2327
2328
2329
2330
2331
2332
2333
2334
2335
2336
2337
2338
2339
2340
2341
2342
2343
2344
2345
2346
2347
2348
2349
2350
2351
2352
2353
2354
2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
2370
2371
2372
2373
2374
2375
2376
2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
2400
2401
2402
2403
2404
2405
2406
2407
2408
2409
2410
2411
2412
2413
2414
2415
2416
2417
2418
2419
2420
2421
2422
2423
2424
2425
2426
2427
2428
2429
2430
2431
2432
2433
2434
2435
2436
2437
2438
2439
2440
2441
2442
2443
2444
2445
2446
2447
2448
2449
2450
2451
2452
2453
2454
2455
2456
2457
2458
2459
2460
2461
2462
2463
2464
2465
2466
2467
2468
2469
2470
2471
2472
2473
2474
2475
2476
2477
2478
2479
2480
2481
2482
2483
2484
2485
2486
2487
2488
2489
2490
2491
2492
2493
2494
2495
2496
2497
2498
2499
2500
2501
2502
2503
2504
2505
2506
2507
2508
2509
2510
2511
2512
2513
2514
2515
2516
2517
2518
2519
2520
2521
2522
2523
2524
2525
2526
2527
2528
2529
2530
2531
2532
2533
2534
2535
2536
2537
2538
2539
2540
2541
2542
2543
2544
2545
2546
2547
2548
2549
2550
2551
2552
2553
2554
2555
2556
2557
2558
2559
2560
2561
2562
2563
2564
2565
2566
2567
2568
2569
2570
2571
2572
2573
2574
2575
2576
2577
2578
2579
2580
2581
2582
2583
2584
2585
2586
2587
2588
2589
2590
2591
2592
2593
2594
2595
2596
2597
2598
2599
2600
2601
2602
2603
2604
2605
2606
2607
2608
2609
2610
2611
2612
2613
2614
2615
2616
2617
2618
2619
2620
2621
2622
2623
2624
2625
2626
2627
2628
2629
2630
2631
2632
2633
2634
2635
2636
2637
2638
2639
2640
2641
2642
2643
2644
2645
2646
2647
2648
2649
2650
2651
2652
2653
2654
2655
2656
2657
2658
2659
2660
2661
2662
2663
2664
2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679
2680
2681
2682
2683
2684
2685
2686
2687
2688
2689
2690
2691
2692
2693
2694
2695
2696
2697
2698
2699
2700
2701
2702
2703
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
2732
2733
2734
2735
2736
2737
2738
2739
2740
2741
2742
2743
2744
2745
2746
2747
2748
2749
2750
2751
2752
2753
2754
2755
2756
2757
2758
2759
2760
2761
2762
2763
2764
2765
2766
2767
2768
2769
2770
2771
2772
2773
2774
2775
2776
2777
2778
2779
2780
2781
2782
2783
2784
2785
2786
2787
2788
2789
2790
2791
2792
2793
2794
2795
2796
2797
2798
2799
2800
2801
2802
2803
2804
2805
2806
2807
2808
2809
2810
2811
2812
2813
2814
2815
2816
2817
2818
2819
2820
2821
2822
2823
2824
2825
2826
2827
2828
2829
2830
2831
2832
2833
2834
2835
2836
2837
2838
2839
2840
2841
2842
2843
2844
2845
2846
2847
2848
2849
2850
2851
2852
2853
2854
2855
2856
2857
2858
2859
2860
2861
2862
2863
2864
2865
2866
2867
2868
2869
2870
2871
2872
2873
2874
2875
2876
2877
2878
2879
2880
2881
2882
2883
2884
2885
2886
2887
2888
2889
2890
2891
2892
2893
2894
2895
2896
2897
2898
2899
2900
2901
2902
2903
2904
2905
2906
2907{------------------------------------------------------------------------------} { CREATE_BDD } {------------------------------------------------------------------------------} { sql de création de toutes les tables de la BDD "osmose" (205 tables) Les attributs les plus courants sont codifiés par un radical suivi du nom de la table à laquelle ils appartiennent ou du nom de leur table référente. Les attributs retenus sont les suivants : +------------+----+------------------------------------------------------------+ | Numéro | n_ | l'attribut "n_pf" se lit "Numéro de la table pf" | | Code | c_ | l'attribut "c_sf" se lit "Code de la table sf" | | Type | t_ | etc. | | Libellé | l_ | | | Mnémonique | m_ | | +------------+----+------------------------------------------------------------+ } {------------------------------------------------------------------------------} { sql de précaution en cas de run accidentel } {------------------------------------------------------------------------------} select rien from rien; {------------------------------------------------------------------------------} { ac (académies) -----------------------------------------------------------} { act (actions de formation KHEOPS) -----------------------------------------} { af (actions de formation) ------------------------------------------------} { am (agglomérations multi-communales) -------------------------------------} { ca (certificats administratifs) ------------------------------------------} { caf (cellule académique formation) ----------------------------------------} { (extraction FILE MAKER pour MàJ des codes GAIA de la table "af") ------} { (objectif -> Bilans de formation DESCO) -------------------------------} { cde (commande) ------------------------------------------------------------} { cde_l (commande ligne) ------------------------------------------------------} { cdf (congés de formation) -------------------------------------------------} { cf (candidatures formation) ----------------------------------------------} { ci (candidatures individualisées) ----------------------------------------} { cl (communes limitrophes) ------------------------------------------------} { cm (communes) ------------------------------------------------------------} { cp (codes postaux) -------------------------------------------------------} { dc (décisions candidatures) ----------------------------------------------} { df (disciplines de formation) --------------------------------------------} { dg (districts géographiques) ---------------------------------------------} { di_m (disciplines poste/recrutement) ---------------------------------------} { (extraction disciplines EPP pour mise à jour de la table "drdp") } { div (divisions) -----------------------------------------------------------} { dl (dates et lieux) ------------------------------------------------------} { dm (disciplines-matières) ------------------------------------------------} { doc (documentation OSMOSE : views, per, ace, shl, sql) --------------------} { dp (départements) --------------------------------------------------------} { drdp (disciplines de recrutement/disciplines de poste) ---------------------} { ds (discipline-spécialité - idem table nnsp) -----------------------------} { et (établissements) ------------------------------------------------------} { et_di (établissements DI) ---------------------------------------------------} { et_nt (établissements néo-titulaires) ---------------------------------------} { fd (fonctions-disciplines EPP) -------------------------------------------} { ff (fiches formateurs) ---------------------------------------------------} { fo (finalités-objectifs) -------------------------------------------------} { gaia (candidatures GAIA) ---------------------------------------------------} { gf (grades/fonctions) ----------------------------------------------------} { gr (grades) --------------------------------------------------------------} { gr_m (grades) --------------------------------------------------------------} { (extraction grades EPP pour mise à jour de la table "gr") } { gs (grades statutaires) --------------------------------------------------} { hbt (habilitations basse tension) -----------------------------------------} { hsa (heures supplémentaires effectives) -----------------------------------} { hse (heures supplémentaires effectives) -----------------------------------} { if (interventions de formation) ------------------------------------------} { ik (interventions khéops) ------------------------------------------------} { (interface pour création d'interventions KHEOPS depuis la table "if_0")} { (voir ifTOik_0.ace, el_ik_0.ace) --------------------------------------} { km (distancier kilométrique) ---------------------------------------------} { ln (logname) -------------------------------------------------------------} { lp (line printer) --------------------------------------------------------} { mf (macro-formations) ----------------------------------------------------} { mp (média/post) ----------------------------------------------------------} { na (nomenclature analytique) ---------------------------------------------} { nb (nomenclature budgétaire) ---------------------------------------------} { ne (natures établissements) ----------------------------------------------} { neo (néo-titulaires) ------------------------------------------------------} { nnco (nomenclature nationale : contenus de formation) ----------------------} { nndc (nomenclature nationale : décisions candidatures) ---------------------} { nndo (nomenclature nationale : domaines de formation) ----------------------} { nnfo (nomenclature nationale : forme de l'organisation) --------------------} { nnmo (nomenclature nationale : modalité de l'organisation) -----------------} { nnni (nomenclature nationale : niveau d'initiative) ------------------------} { nnob (nomenclature nationale : objectifs de formation) ---------------------} { nnpc (nomenclature nationale : public cible) -------------------------------} { nnsp (nomenclature nationale : spécialités - idem table ds) ----------------} { nntc (nomenclature nationale : type de candidature) ------------------------} { nntf (nomenclature nationale : type de formation) --------------------------} { nntmp (nomenclature nationale : temporaire) ---------------------------------} { omp (OM permanents) -------------------------------------------------------} { pa (priorités académiques) -----------------------------------------------} { pdf (parcours de formation) -----------------------------------------------} { pep4 (pep_4) ---------------------------------------------------------------} { pf (personnel formation) -------------------------------------------------} { piaf (procédure d'inscription aux actions de formation) --------------------} { pn (priorités nationales) ------------------------------------------------} { rp (répartitions pédagogiques des certificats administratifs) ------------} { sa (situations administratives -------------------------------------------} { ser (services) ------------------------------------------------------------} { sf (stages de formation) -------------------------------------------------} { sg (services gestionnaires) ----------------------------------------------} { sh (suivi historique) ----------------------------------------------------} { sys_c (colonnes) ------------------------------------------------------------} { sys_t (tables) --------------------------------------------------------------} { sysmenus (system catalog) -----------------------------------------------} { sysmenuitems (system catalog) -----------------------------------------------} { t_cf (temporaire candidatures formation) -----------------------------------} { t_cm (typologie communes) --------------------------------------------------} { t_ne (typologie nature établissement) --------------------------------------} { tbb (temporaire bilans budgétaires) ---------------------------------------} { tmp (temporaire) ----------------------------------------------------------} { ts (transports-séjours) --------------------------------------------------} { ts_t (transports-séjours temporaire) ---------------------------------------} { tsa (transport-séjour animateur) ------------------------------------------} { tsdp (transport-séjour dépense prévisionnelle) -----------------------------} { tss_t (transport-séjour stagiaire temporaire) -------------------------------} { uaa (table nationale des établissements) ----------------------------------} { uf (unités de formation) -------------------------------------------------} { ut (unités de traitement) ------------------------------------------------} { zep (zone d'éducation prioritaire) ----------------------------------------} {==============================================================================} { ac (académies) -----------------------------------------------------------} create table ac ( c_ra char(2), c_ac char(2) not null, l_ac char(20) not null, division char(38), c_et char(8), c_et_iufm char(8), ordre_paiement char(1) ) ; grant all on ac to osmose; grant select on ac to public; grant update on ac to public; grant insert on ac to public; grant delete on ac to public; { act (actions de formation KHEOPS) -----------------------------------------} create table act ( anges datetime year to year not null, staco char(10) not null, gaiaco char(1) not null, li30 char(30) not null, lieu char(25) not null, listaco char(1), dtdeb date not null, dtfin date not null, forco char(1) not null, natac char(1), minis char(3) not null, chap char(4), artx char(2), loc char(1), { loc : A = Académie } flag char(1) { B = Bobigny } { C = Créteil } { D = Melun } grant all on act to osmose; grant select on act to public; grant update on act to public; grant insert on act to public; grant delete on act to public; { af (actions de formation) ------------------------------------------------} create table af ( rentree datetime year to year not null, rentree_yy char(2) not null, sortie datetime year to year not null, sortie_yy char(2) not null, logname char(8), c_bdf char(1) not null, c_bdd char(1) not null, c_df char(3) not null, c_mf char(5) not null, c_af char(7) not null, c_af_1 char(7), c_af_2 char(7), l_af_1 char(50) not null, l_af_2 char(50), l_af_3 char(50), c_kheops char(10), d_kheops date, d_debut date, d_fin date, c_fo char(2), c_pn char(2), c_nnco char(3), c_nnmo char(1), c_nnni char(1), c_nnob char(2), c_nnpc char(2), c_nnsp char(3), initiative char(1) not null, c_ac char(2), origine char(10), t_af char(1), t_cf char(1) not null, chapitre char(4), article char(2), sessions smallint, c_pdf char(2), minimum smallint, maximum smallint, duree_h smallint, duree_j decimal(4,1), suivi char(5), taupe char(45), n_pf_sp integer, { Suivi Pédagogique } n_pf_so integer, { Suivi Opérationnel } p_vacations_ct decimal(6,2), { Prévision vacation cours théorique } p_vacations_hs decimal(6,2), { Prévision vacation heures supplémentaires } p_vacations_cc decimal(6,2), { Prévision vacation cours complémentaire } p_vacations_tp decimal(6,2), { Prévision vacation travaux pratiques } p_hse decimal(6,2), { Prévision hse } p_hse_iufm decimal(6,2), { Prévision hse IUFM } p0_pedagogie decimal(10,2), { Prévision 09-12 pédagogie } p0_conventions decimal(10,2), { Prévision 09-12 conventions } p0_vacations_ct decimal(10,2), { Prévision 09-12 vac. cours théorique } p0_vacations_hs decimal(10,2), { Prévision 09-12 vac. heures supplémentaires } p0_vacations_cc decimal(10,2), { Prévision 09-12 vac. cours complémentaire } p0_vacations_tp decimal(10,2), { Prévision 09-12 vac. travaux pratiques } p0_transport_a decimal(10,2), { Prévision 09-12 transport animateurs } p0_sejour_a decimal(10,2), { Prévision 09-12 séjour animateurs } p0_transport_s decimal(10,2), { Prévision 09-12 transport stagiaires } p0_sejour_s decimal(10,2), { Prévision 09-12 séjour stagiaires } p1_pedagogie decimal(10,2), { Prévision 01-07 pédagogie } p1_conventions decimal(10,2), { Prévision 01-07 conventions } p1_vacations_ct decimal(10,2), { Prévision 01-07 vac. cours théorique } p1_vacations_hs decimal(10,2), { Prévision 01-07 vac. heures supplémentaires } p1_vacations_cc decimal(10,2), { Prévision 01-07 vac. chargé de cours } p1_vacations_tp decimal(10,2), { Prévision 01-07 vac. cours complémentaire } p1_transport_a decimal(10,2), { Prévision 01-07 transport animateurs } p1_sejour_a decimal(10,2), { Prévision 01-07 séjour animateurs } p1_transport_s decimal(10,2), { Prévision 01-07 transport stagiaires } p1_sejour_s decimal(10,2), { Prévision 01-07 séjour stagiaires } r_vacations_ct decimal(6,2), { Réalisation vacation cours théorique } r_vacations_hs decimal(6,2), { Réalisation vacation heures supplémentaires } r_vacations_cc decimal(6,2), { Réalisation vacation cours complémentaire } r_vacations_tp decimal(6,2), { Réalisation vacation travaux pratiques } r_hse decimal(6,2), { Réalisation hse } r_hse_iufm decimal(6,2), { Réalisation hse IUFM } r0_pedagogie decimal(10,2), { Réalisation 09-12 pédagogie } r0_conventions decimal(10,2), { Réalisation 09-12 conventions } r0_vacations_ct decimal(10,2), { Réalisation 09-12 vac. cours théorique } r0_vacations_hs decimal(10,2), { Réalisation 09-12 vac. heures supplémentaires } r0_vacations_cc decimal(10,2), { Réalisation 09-12 vac. cours complémentaire } r0_vacations_tp decimal(10,2), { Réalisation 09-12 vac. travaux pratiques } r0_transport_a decimal(10,2), { Réalisation 09-12 transport animateurs } r0_sejour_a decimal(10,2), { Réalisation 09-12 séjour animateurs } r0_transport_s decimal(10,2), { Réalisation 09-12 transport stagiaires } r0_sejour_s decimal(10,2), { Réalisation 09-12 séjour stagiaires } r1_pedagogie decimal(10,2), { Réalisation 01-07 pédagogie } r1_conventions decimal(10,2), { Réalisation 01-07 conventions } r1_vacations_ct decimal(10,2), { Réalisation 01-07 vac. cours théorique } r1_vacations_hs decimal(10,2), { Réalisation 01-07 vac. heures supplémentaires } r1_vacations_cc decimal(10,2), { Réalisation 01-07 vac. chargé de cours } r1_vacations_tp decimal(10,2), { Réalisation 01-07 vac. cours complémentaire } r1_transport_a decimal(10,2), { Réalisation 01-07 transport animateurs } r1_sejour_a decimal(10,2), { Réalisation 01-07 séjour animateurs } r1_transport_s decimal(10,2), { Réalisation 01-07 transport stagiaires } r1_sejour_s decimal(10,2), { Réalisation 01-07 séjour stagiaires } collectif smallint, { candidatures collectives } designe smallint, { candidatures public désigné } reserve smallint, { candidatures public réservé } voeu_1 smallint, { candidatures voeu 1 } voeu_2 smallint, { candidatures voeu 2 } voeu_3 smallint, { candidatures voeu 3 } inscrits smallint, { candidats inscrits } presents smallint, { candidats présents } assidus smallint, { candidats assidus } absents smallint, { candidats absents } excuses smallint, { candidats excusés } ad smallint, { décision : Avis Défavorable du Chef Etab. } ae smallint, { décision : Action d'Etablissement } ai smallint, { décision : Affectation Inconnue } am smallint, { décision : Arrêt Maladie } as smallint, { décision : Absence à un Stage } ca smallint, { décision : Candidature Acceptée } ci smallint, { décision : Candidature Inexploitable } dc smallint, { décision : Désistement du Candidat } ha smallint, { décision : Hors Académie } hd smallint, { décision : Hors Délai } hp smallint, { décision : Hors Public (grade incompatible...) } la smallint, { décision : Liste d'Attente } ls smallint, { décision : Limitation de Stages } nr smallint, { décision : Non Retenue (Niveau Requis) } pd smallint, { décision : action à Public Désigné } pr smallint, { décision : action à Public Réservé } rs smallint, { décision : Raison de Service } sa smallint, { décision : Stage en Attente } sc smallint, { décision : Stage Complet } sf smallint, { décision : Stage Fermé } si smallint, { décision : Stage Incompatible autre stage } tp smallint, { décision : tests de Positionnement négatifs } a_hommes_a smallint, { candidats administratifs catégorie A } a_hommes_b smallint, { candidats administratifs catégorie B } a_hommes_c smallint, { candidats administratifs catégorie C } a_hommes_d smallint, { candidats administratifs catégorie D } a_femmes_a smallint, { candidates administratifs catégorie A } a_femmes_b smallint, { candidates administratifs catégorie B } a_femmes_c smallint, { candidates administratifs catégorie C } a_femmes_d smallint, { candidates administratifs catégorie D } e_hommes_a smallint, { candidats enseignants catégorie A } e_hommes_b smallint, { candidats enseignants catégorie B } e_hommes_c smallint, { candidats enseignants catégorie C } e_hommes_d smallint, { candidats enseignants catégorie D } e_femmes_a smallint, { candidates enseignants catégorie A } e_femmes_b smallint, { candidates enseignants catégorie B } e_femmes_c smallint, { candidates enseignants catégorie C } e_femmes_d smallint, { candidates enseignants catégorie D } mois_add datetime month to month, mois_upd datetime month to month, flag char(1) ) ; grant all on af to osmose; grant select on af to public; grant update on af to public; grant insert on af to public; grant delete on af to public; { am (agglomérations multi-communales) -------------------------------------} create table am ( c_am char(5) not null, l_am char(30) not null ) ; grant all on am to osmose; grant select on am to public; grant update on am to public; grant insert on am to public; grant delete on am to public; { bb (bilans budgétaires) --------------------------------------------------} create table bb ( exercice datetime year to year not null, rentree datetime year to year not null, c_bdd char(1) not null, c_df char(3), c_af char(7), c_sf char(10), pedagogie decimal(10,2), conventions decimal(10,2), vacations decimal(10,2), transport_a decimal(10,2), sejour_a decimal(10,2), transport_s decimal(10,2), sejour_s decimal(10,2) ) ; grant all on bb to osmose; grant select on bb to public; grant update on bb to public; grant insert on bb to public; grant delete on bb to public; { ca (certificats administratifs) ------------------------------------------} create table ca ( n_ca serial not null, rentree_yy char(2) not null, c_bdd char(1), c_df char(3), c_af char(7), c_sf char(10), exercice datetime year to year, chapitre char(5), article char(2), paragraphe char(2), c_na char(2), n_facture char(10), d_facture date, c_et char(8), n_ref char(10), d_ca date, montant_e decimal(10,2), montant_f decimal(10,2) ) ; grant all on ca to osmose; grant select on ca to public; grant update on ca to public; grant insert on ca to public; grant delete on ca to public; { caf (cellule académique formation) ----------------------------------------} { (extraction FILE MAKER pour MàJ des codes GAIA de la table "af") ------} { (objectif -> Bilans de formation DESCO) -------------------------------} create table caf ( c_af char(7), c_sf char(10), c_nnpn char(2), c_nnof char(2), c_nnni char(1), c_nndf char(1), c_nncf char(3), c_nnpc char(2), c_nnpc_bis char(2), c_nntf char(1), c_nnfo char(1), c_nnmo char(1) ) ; grant all on caf to osmose; grant select on caf to public; grant update on caf to public; grant insert on caf to public; grant delete on caf to public; { cde (commande) ------------------------------------------------------------} create table cde ( rentree_yy char(2) not null, n_cde char(8) not null, d_cde date not null, montant_e decimal(10,2) not null, montant decimal(10,2) not null, c_et char(8) not null ) ; grant all on cde to osmose; grant select on cde to public; grant update on cde to public; grant insert on cde to public; grant delete on cde to public; { cde_l (commande ligne) ------------------------------------------------------} create table cde_l ( n_cde char(8) not null, reference char(17) not null, pu_ht decimal(10,2) not null, quantite smallint not null, designation char(38) not null, designation_1 char(38), designation_2 char(38), designation_3 char(38), designation_4 char(38), designation_5 char(38), designation_6 char(38), designation_7 char(38), designation_8 char(38), designation_9 char(38) ) ; grant all on cde_l to osmose; grant select on cde_l to public; grant update on cde_l to public; grant insert on cde_l to public; grant delete on cde_l to public; { cdf (congés de formation --------------------------------------------------} create table cdf ( n_pf integer not null, d_debut date not null, d_fin date not null ) ; grant all on cdf to osmose; grant select on cdf to public; grant update on cdf to public; grant insert on cdf to public; grant delete on cdf to public; { cf (candidatures formation) ----------------------------------------------} create table cf ( nom_cf char(5) not null, n_pf integer not null, c_bdf char(1) not null, t_cf char(1) not null, rang char(1) not null, c_uf char(10), c_sf char(10) not null, avis_ce char(1), c_decision char(2), d_mailing date, emargement char(1), presence smallint, exercice datetime year to year, transport_s decimal(7,2), sejour_s decimal(7,2), c_bdd char(1) not null, mois_add datetime month to month, mois_upd datetime month to month ) ; grant all on cf to osmose; grant select on cf to public; grant update on cf to public; grant insert on cf to public; grant delete on cf to public; { ci (candidatures individualisées) ----------------------------------------} create table ci ( n_pf integer not null, c_sf char(10) not null, d_debut date not null, groupe char(3), date_1 char(38), date_2 char(38), date_3 char(38), date_4 char(38), date_5 char(38), date_6 char(38), c_et char(8), l_et char(38), adresse char(38), lieu char(38), lieu_dit char(38), c_cp char(5), l_ld char(30), c_cm char(5) ) ; grant all on ci to osmose; grant select on ci to public; grant update on ci to public; grant insert on ci to public; grant delete on ci to public; { cl (communes limitrophes) ------------------------------------------------} create table cm ( c_cm char(5) not null, l_cm char(30) not null, c_cl char(5), l_cl char(30) ) ; grant all on cl to osmose; grant select on cl to public; grant update on cl to public; grant insert on cl to public; grant delete on cl to public; { cm (communes) ------------------------------------------------------------} create table cm ( c_dp char(2) not null, c_am char(5), c_dg char(2), c_cm char(5) not null, l_cm char(38) not null t_cm char(5), t_cm_ff char(1) ) ; grant all on cm to osmose; grant select on cm to public; grant update on cm to public; grant insert on cm to public; grant delete on cm to public; { cp (codes postaux) -------------------------------------------------------} create table cp ( c_cp char(5) not null, c_cm char(5) not null, l_ld char(30) not null ) ; grant all on cp to osmose; grant select on cp to public; grant update on cp to public; grant insert on cp to public; grant delete on cp to public; { dc (décisions candidatures) ----------------------------------------------} create table dc ( c_decision char(2) not null, l_decision char(40) not null, c_dc char(2) not null, l_dc char(50) not null, nbre_cf smallint ) ; grant all on dc to osmose; grant select on dc to public; grant update on dc to public; grant insert on dc to public; grant delete on dc to public; { df (disciplines de formation) --------------------------------------------} create table df ( rentree datetime year to year not null, rentree_yy char(2) not null, sortie datetime year to year not null, sortie_yy char(2) not null, c_bdf char(1), c_bdd char(1) not null, c_df char(3) not null, l_df char(50) not null, t_df char(2), t_df_l_1 char(50), t_df_l_2 char(50), logname char(8), n_charge char(1) not null, suivi char(5), taupe char(45), p_pf_sp integer, p_pf_so integer, suivi_aa char(5), taupe_aa char(45), n_pf_ap integer, n_pf_ao integer, sessions smallint, p_vacations_ct decimal(6,2), p_vacations_hs decimal(6,2), p_vacations_cc decimal(6,2), p_vacations_tp decimal(6,2), p_hse decimal(6,2), p_hse_iufm decimal(6,2), p0_pedagogie decimal(10,2), p0_conventions decimal(10,2), p0_vacations_ct decimal(10,2), p0_vacations_hs decimal(10,2), p0_vacations_cc decimal(10,2), p0_vacations_tp decimal(10,2), p0_transport_a decimal(10,2), p0_sejour_a decimal(10,2), p0_transport_s decimal(10,2), p0_sejour_s decimal(10,2), p1_pedagogie decimal(10,2), p1_conventions decimal(10,2), p1_vacations_ct decimal(10,2), p1_vacations_hs decimal(10,2), p1_vacations_cc decimal(10,2), p1_vacations_tp decimal(10,2), p1_transport_a decimal(10,2), p1_sejour_a decimal(10,2), p1_transport_s decimal(10,2), p1_sejour_s decimal(10,2), r_vacations_ct decimal(6,2), r_vacations_hs decimal(6,2), r_vacations_cc decimal(6,2), r_vacations_tp decimal(6,2), r_hse decimal(6,2), r_hse_iufm decimal(6,2), r0_pedagogie decimal(10,2), r0_conventions decimal(10,2), r0_vacations_ct decimal(10,2), r0_vacations_hs decimal(10,2), r0_vacations_cc decimal(10,2), r0_vacations_tp decimal(10,2), r0_transport_a decimal(10,2), r0_sejour_a decimal(10,2), r0_transport_s decimal(10,2), r0_sejour_s decimal(10,2), r1_pedagogie decimal(10,2), r1_conventions decimal(10,2), r1_vacations_ct decimal(10,2), r1_vacations_hs decimal(10,2), r1_vacations_cc decimal(10,2), r1_vacations_tp decimal(10,2), r1_transport_a decimal(10,2), r1_sejour_a decimal(10,2), r1_transport_s decimal(10,2), r1_sejour_s decimal(10,2), collectif smallint, designe smallint, reserve smallint, voeu_1 smallint, voeu_2 smallint, voeu_3 smallint, inscrits smallint, presents smallint, assidus smallint, absents smallint, excuses smallint, ad smallint, ae smallint, ai smallint, am smallint, as smallint, ca smallint, ci smallint, dc smallint, ha smallint, hd smallint, hp smallint, la smallint, ls smallint, nr smallint, pd smallint, pr smallint, rs smallint, sa smallint, sc smallint, sf smallint, si smallint, tp smallint, a_hommes_a smallint, a_hommes_b smallint, a_hommes_c smallint, a_hommes_d smallint, a_femmes_a smallint, a_femmes_b smallint, a_femmes_c smallint, a_femmes_d smallint, e_hommes_a smallint, e_hommes_b smallint, e_hommes_c smallint, e_hommes_d smallint, e_femmes_a smallint, e_femmes_b smallint, e_femmes_c smallint, e_femmes_d smallint, d_count date, flag char(1) ) ; grant all on df to osmose; grant select on df to public; grant update on df to public; grant insert on df to public; grant delete on df to public; { dg (districts géographiques) ---------------------------------------------} create table dg ( c_dp char(2) not null, c_dg char(2) not null, l_dg char(20) not null ) ; grant all on dg to osmose; grant select on dg to public; grant update on dg to public; grant insert on dg to public; grant delete on dg to public; { di_m (disciplines poste/recrutement) ---------------------------------------} { (extraction disciplines EPP pour mise à jour de la table "drdp") } create table di_m ( c_di char(5) not null, m_di char(10), l_di char(40), bivalence char(2), d_debut date, d_fin date, c_fonction char(3) ) ; grant all on di_m to osmose; grant select on di_m to public; grant update on di_m to public; grant insert on di_m to public; grant delete on di_m to public; { div (divisions) -----------------------------------------------------------} create table div ( c_et char(8), c_div char(2) not null, l_div char(30) not null ) ; grant all on div to osmose; grant select on div to public; grant update on div to public; grant insert on div to public; grant delete on div to public; { dl (dates et lieux) ------------------------------------------------------} create table dl ( c_sf char(10) not null, c_et char(8), lieu char(38), d_debut date, jours smallint, demi_j smallint, edition char(1), date_1 char(38), date_2 char(38), date_3 char(38), date_4 char(38), date_5 char(38), date_6 char(38), mois_add datetime month to month, mois_upd datetime month to month ) ; grant all on dl to osmose; grant select on dl to public; grant update on dl to public; grant insert on dl to public; grant delete on dl to public; { dm (disciplines-matières) ------------------------------------------------} create table dm ( c_dm char(2) not null, m_dm char(10), l_dm char(30), l_categorie char(30), flag char(1) ) ; grant all on dm to osmose; grant select on dm to public; grant update on dm to public; grant insert on dm to public; { doc (documentation OSMOSE : views, per, ace, shl, sql) --------------------} create table doc ( processus char(8), c_ut char(20), suffixe char(1), cd char(8), wc smallint, ls char(20), flag char(1) ) ; grant all on doc to osmose; grant select on doc to public; grant update on doc to public; grant insert on doc to public; { dp (départements) --------------------------------------------------------} create table dp ( c_dp char(2) not null, c_dt char(1) not null, l_dp char(30) not null, chef_lieu char(20), c_et char(8), c_ac char(2), c_academie char(2) not null, km_fd smalmint ) ; grant all on dp to osmose; grant select on dp to public; grant update on dp to public; grant insert on dp to public; grant delete on dp to public; { ds (discipline-spécialité - idem table nnsp) -----------------------------} create table ds ( c_ds char(3) not null, l_ds char(50) not null, l_ds_30 char(30) not null ) ; grant all on ds to osmose; grant select on ds to public; grant update on ds to public; grant insert on ds to public; { drdp (disciplines de recrutement/disciplines de poste) ---------------------} create table drdp ( c_bdd char(1) not null, t_drdp char(1) not null, c_drdp char(5) not null, m_drdp char(10) not null, l_drdp char(30) not null, l_drdp_maj char(38) not null, l_drdp_min char(50) not null, d_debut date, d_fin date, c_fd char(3), c_dm char(2), c_gf char(2), c_ds char(3), { c_ds = code discipline-spécialité = c_nnsp } c_ds_old char(3), flag char(1) ) ; grant all on drdp to osmose; grant select on drdp to public; grant update on drdp to public; grant insert on drdp to public; grant delete on drdp to public; { et (établissements) ------------------------------------------------------} create table et ( c_ac char(2) not null, c_et char(8) not null, c_dp char(2) not null, c_dt char(1) not null, c_ne char(3) not null, t_et char(2), zone char(3), sigle char(10), l_et char(38) not null, adresse char(38), lieu_dit char(38), c_cp char(5) not null, c_cm char(5), l_ld char(30) not null, n_siret char(14), pep_1 char(1), pep_4 char(1), mode_paiement char(2), l_et_compte char(38), c_et_compte integer, c_guichet integer, n_compte char(11), cle char(2), taux_is decimal(4,2), km_fd smallint, plan char(1), telephone char(14), fax char(14), e_mail_ok char(2), e_mail char(30), chef char(38), adjoint char(38), intendant char(38), comptable char(38), gestionnaire char(38), flag char(1) ) ; grant all on et to osmose; grant select on et to public; grant update on et to public; grant insert on et to public; grant delete on et to public; { et_di (établissements DI) ---------------------------------------------------} create table et_di c_dp char(2) not null, { dpt } c_et char(8) not null, { rne } sigle char(10), { sigle } l_et char(38) not null, { deno } adresse char(38), { addr } c_cp char(5) not null, { cp } l_ld char(30) not null, { ville } telephone char(14), { tel } fax char(14), { fax } e_mail char(30), { mail } bassin char(30), { bassin_district } rne_segpa_1 char(8), { rne_rattache1 } civ_segpa_1 char(3), { civ_dir_segpa1 } nom_segpa_1 char(20), { nom_dir_segpa1 } pre_segpa_1 char(20), { pren_dir_segpa1 } rne_segpa_2 char(8), { rne_rattache2 } civ_segpa_2 char(3), { civ_dir_segpa2 } nom_segpa_2 char(20), { nom_dir_segpa2 } pre_segpa_2 char(20), { pren_dir_segpa2 } civ_ce char(3), { civcheta } nom_ce char(20), { nomcheta } pre_ce char(20), { prencheta } civ_ce_1 char(3), { civcheta1 } nom_ce_1 char(20), { nomcheta1 } pre_ce_1 char(20), { prencheta1 } civ_ce_2 char(3), { civcheta2 } nom_ce_2 char(20), { nomcheta2 } pre_ce_2 char(20), { prencheta2 } civ_ac char(3), { civac } nom_ac char(20), { nomac } pre_ac char(20), { prenac } civ_ges char(3), { civges } nom_ges char(20), { nomges } pre_ges char(20), { prenges } rne_ac char(8), { rne_agence_compta } l_cm char(30), { commune } chef char(38), { } adjoint char(38), { } intendant char(38), { } comptable char(38), { } gestionnaire char(38), { } flag char(1) { } ) ; grant all on et_di to osmose; grant select on et_di to public; grant update on et_di to public; grant insert on et_di to public; grant delete on et_di to public; { et_nt (établissements néo-titulaires) ---------------------------------------} create table et_nt ( c_et char(8) not null, sigle char(10) not null, l_et char(38) not null, l_cm char(30) not null, district char(2) not null, bassin char(1) not null, liste char(1) not null, c_dp char(2) not null ) ; grant all on et to osmose; grant select on et to public; grant update on et to public; grant insert on et to public; grant delete on et to public; { fd (fonctions-disciplines EPP) -------------------------------------------} create table fd ( c_fd char(3) not null, m_fd char(10) not null, l_fd char(50) not null, d_debut date, d_fin date, flag char(1) ) ; grant all on fd to osmose; grant select on fd to public; grant update on fd to public; grant insert on fd to public; grant delete on fd to public; { ff (fiches formateurs) ---------------------------------------------------} create table ff ( n_pf integer not null, insee decimal(13,0), insee_cle decimal(2,0), c_gr char(4), decharge decimal(4,2), c_df char(3), solidarite char(1) not null, csg char(1) not null, qualite char(38), tel_pf char(18), tel_et char(18), E_mail char(38), c_et char(8) not null, c_et_old char(8), mode_paiement char(2), l_et_compte char(38), c_et_compte char(5), c_guichet char(5), n_compte char(11), n_compte_cle char(2) ) ; grant all on ff to osmose; grant select on ff to public; grant update on ff to public; grant insert on ff to public; grant delete on ff to public; { fo (finalités-objectifs) -------------------------------------------------} create table fo ( t_fo char(1) not null, t_fo_a char(1) not null, t_fo_e char(1) not null, c_fo char(2) not null, l_fo char(50) not null ) ; grant all on fo to osmose; grant select on fo to public; grant update on fo to public; grant insert on fo to public; grant delete on fo to public; { gaia (candidatures GAIA) ---------------------------------------------------} create table gaia ( n_pf integer, numen char(13) not null, c_bdd char(1), c_et char(8), voeu_1 char(10), avis_ce_1 char(1), voeu_2 char(10), avis_ce_2 char(1), voeu_3 char(10), avis_ce_3 char(1), limite_ce char(1), flag char(1) ) ; grant all on gaia to osmose; grant select on gaia to public; grant update on gaia to public; grant insert on gaia to public; grant delete on gaia to public; { gf (grades/fonctions) ----------------------------------------------------} create table gf ( c_gf char(2) not null, m_gf char(10), l_gf char(30), t_gf char(1), t_gf_a char(2), l_gf_a char(30), t_gf_e char(2), l_gf_e char(30), c_dm char(2), sous_couvert char(1), c_bdd char(1), flag char(1) ) ; grant all on gf to osmose; grant select on gf to public; grant update on gf to public; grant insert on gf to public; grant delete on gf to public; { gr (grades) --------------------------------------------------------------} create table gr ( c_gr char(4) not null, { code grade } m_gr char(10) not null, { mnémonique grade } l_gr char(30) not null, { libellé grade } l_gr_maj char(38) not null, { libellé grade en majuscules } l_gr_min char(50) not null, { libellé grade en minuscules } c_bdd char(1), { code BDD [A]dministratifs/[E]nseignants } ordre_paiement char(1) not null, { [O]/[N] } sous_couvert char(1) not null, { null/[N]on } c_sa char(2), { code situation administrative } categorie char(1), { [A]/[B]/[C] } c_gf_a char(2), { code grade-fonction administratifs } c_gf_e char(2), { code grade-fonction enseignants } t_gr char(1), { typologie grades } t_pf char(1), { typologie personnels } flag char(1) { null/[-] } ) ; grant all on gr to osmose; grant select on gr to public; grant update on gr to public; grant insert on gr to public; grant delete on gr to public; { gr_m (grades) --------------------------------------------------------------} { (extraction grades EPP pour mise à jour de la table "gr") } create table gr_m ( n_gr serial not null, c_gr char(4) not null, d_debut date, d_fin date, m_gr char(10), l_gr char(40), categorie char(1), t_pf char(1), t_gr char(1), ors decimal(4,2), t_ors char(1), ph char(1), cf char(3), seuilp char(6), ec char(1) ) ; grant all on gr_m to osmose; grant select on gr_m to public; grant update on gr_m to public; grant insert on gr_m to public; grant delete on gr_m to public; { gs (grades statutaires) --------------------------------------------------} { Table crée pour les bilans (1999-2000) CAFA - Intégrée à la table "gf" } create table gs ( c_gs char(2) not null, m_gs char(10) not null, l_gs char(30) not null, categorie_gs char(1) not null ) ; grant all on gs to osmose; grant select on gs to public; grant update on gs to public; grant insert on gs to public; grant delete on gs to public; { hbt (habilitations basse tensuin) -----------------------------------------} create table hbt ( n_pf integer not null, d_debut date not null, d_fin date not null ) ; grant all on hbt to osmose; grant select on hbt to public; grant update on hbt to public; grant insert on hbt to public; grant delete on hbt to public; { hsa (heures supplémentaires années) ---------------------------------------} create table hsa ( n_pf integer not null, mm_hsa datetime month to month, aa_hsa datetime year to year, c_af char(7) not null, nb_hsa decimal(5,2) not null, c_ac char(7) not null, initiales char(4) not null, n_bordereau smallint ) ; grant all on hsa to osmose; grant select on hsa to public; grant update on hsa to public; grant insert on hsa to public; grant delete on hsa to public; { hse (heures supplémentaires effectives) -----------------------------------} create table hse ( n_pf integer not null, mm_hse datetime month to month, aa_hse datetime year to year, c_sf char(10) not null, nb_hse decimal(5,2) not null, c_ac char(2) not null, initiales char(4) not null, n_bordereau smallint ) ; grant all on hse to osmose; grant select on hse to public; grant update on hse to public; grant insert on hse to public; grant delete on hse to public; { if (interventions de formation) ------------------------------------------} create table if ( n_pf integer not null, c_sf char(10) not null, c_et char(8) not null, d_debut date not null, h_debut decimal(4,2), d_fin date, h_fin decimal(4,2), jours smallint, demi_j smallint, am_debut decimal(4,2), am_fin decimal(4,2), pm_debut decimal(4,2), pm_fin decimal(4,2), objet char(1), frais char(1), repas smallint, nuits smallint, deplacement smallint, distance smallint, c_cm_pf char(5), c_cm_et char(5), c_cm_if char(5), lieu char(38), edition char(1), nota_1 char(38), nota_2 char(38), nota_3 char(38), om_permanent char(1) not null, d_mailing date, ordre_paiement char(1), annee_if datetime year to year, exercice datetime year to year, chapitre char(5), article char(2), paragraphe char(2), animateurs decimal(1), hh_cours smallint, mn_cours smallint, tx_cours decimal(5,2), hh_tp smallint, mn_tp smallint, tx_tp decimal(5,2), total_brut decimal(7,2), d_liquide date ) ; grant all on if to osmose; grant select on if to public; grant update on if to public; grant insert on if to public; grant delete on if to public; { ik (interventions khéops) ------------------------------------------------} { (interface pour création d'interventions KHEOPS depuis la table "if_0")} { (voir ifTOik_0.ace, el_ik_0.ace) --------------------------------------} create table ik ( n_pf integer not null, c_sf char(10) not null, c_et char(8) not null, d_debut date not null, h_debut decimal(4,2), d_fin date, h_fin decimal(4,2), jours smallint, demi_j smallint, am_debut decimal(4,2), am_fin decimal(4,2), pm_debut decimal(4,2), pm_fin decimal(4,2), objet char(1), frais char(1), repas smallint, nuits smallint, deplacement smallint, distance smallint, lieu char(38), edition char(1), nota_1 char(38), nota_2 char(38), nota_3 char(38), om_permanent char(1) not null, d_mailing date, ordre_paiement char(1), annee_ik datetime year to year, exercice datetime year to year, chapitre char(5), article char(2), paragraphe char(2), animateurs decimal(1), hh_cours smallint, mn_cours smallint, tx_cours decimal(5,2), hh_tp smallint, mn_tp smallint, tx_tp decimal(5,2), total_brut decimal(7,2), d_liquide date ) ; grant all on ik to osmose; grant select on ik to public; grant update on ik to public; grant insert on ik to public; grant delete on ik to public; { km (distancier kilométrique) ---------------------------------------------} create table km ( c_cm_1 char(5), l_cm_1 char(40), c_cm_2 char(5), l_cm_2 char(40), km smallint ) ; grant all on km to osmose; grant select on km to public; grant update on km to public; grant insert on km to public; grant delete on km to public; { ln (logname) -------------------------------------------------------------} create table ln ( service char(8) not null, bureau char(3), logname char(8) not null, suivi char(45) not null, suivi_fax char(45), suivi_mel char(45), suivi_nom char(32), suivi_tel char(32), initiales char(4) not null, n_pf integer, lpdest char(8) not null, pre_imprime char(4) not null, papier_blanc char(4) not null, bac_bm char(4) not null, bac_a3 char(4), destination char(4) not null, recto_verso char(4) ) ; grant all on ln to osmose; grant select on ln to public; grant update on ln to public; grant insert on ln to public; grant delete on ln to public; { lp (line printer) --------------------------------------------------------} create table lp ( bureau char(3) not null, file_attente char(6) not null, logname char(8) not null, pre_imprime char(4) not null, papier_blanc char(4) not null, bac_bm char(4) not null, destination char(4) not null ) ; grant all on lp to osmose; grant select on lp to public; grant update on lp to public; grant insert on lp to public; grant delete on lp to public; { mp (média/post) ----------------------------------------------------------} create table mp ( c_cm char(5), t_cm char(1), l_cm char(32), c_cp char(5), t_ld char(1), l_ld char(26), c_pc char(5) ) ; grant all on mp to osmose; grant select on mp to public; grant update on mp to public; grant insert on mp to public; grant delete on mp to public; { na (nomenclature analytique) ---------------------------------------------} create table na ( c_na char(2) not null, l_na char(50) not null ) ; grant all on na to osmose; grant select on na to public; grant update on na to public; grant insert on na to public; grant delete on na to public; { nb (nomenclature budgétaire) ---------------------------------------------} create table nb ( exercice datetime year to year not null, destination char(9), programme char(3), action char(2), sous_action char(2), chapitre char(5) not null, article char(2) not null, paragraphe char(2), categorie char(2), compte_pce char(7), t_nb char(3), c_na char(2), l_chapitre_1 char(50), l_article_1 char(50), l_article_2 char(50), l_article_3 char(50), l_article_4 char(50), l_paragraphe_1 char(50), l_paragraphe_2 char(50), l_paragraphe_3 char(50), l_paragraphe_4 char(50), budgete decimal(10,2), liquide decimal(10,2), mandate decimal(10,2) ) ; grant all on nb to osmose; grant select on nb to public; grant update on nb to public; grant insert on nb to public; grant delete on nb to public; { ne (natures établissements) ----------------------------------------------} create table ne ( c_ne char(3) not null, t_ne_a char(1), t_ne_e char(1), sous_fichier char(1), groupe char(1), sigle char(10), m_ne char(10), l_ne_1 char(38), l_ne_2 char(38), titre_1 char(38) not null, titre_2 char(38), courrier char(1), e_mail_ok char(2), lettre char(1), flag char(1) ) ; grant all on ne to osmose; grant select on ne to public; grant update on ne to public; grant insert on ne to public; grant delete on ne to public; { mf (macro-formations) ----------------------------------------------------} create table mf ( rentree datetime year to year not null, rentree_yy char(2) not null, sortie datetime year to year not null, sortie_yy char(2) not null, c_bdf char(1) not null, c_bdd char(1) not null, c_df char(3) not null, c_mf char(5) not null, l_mf char(50), l_mf_1 char(50), l_mf_2 char(50), l_mf_3 char(50), l_oa_1 char(50), l_oa_2 char(50), l_oa_3 char(50), c_nnob char(2), c_nnni char(1), c_nntc char(1), t_cf char(1), initiative char(1), origine char(4), chapitre char(4), article char(2), sessions smallint, places smallint, duree_h smallint, duree_j decimal(4,1), flag char(1) ) ; grant all on mf to osmose; grant select on mf to public; grant update on mf to public; grant insert on mf to public; grant delete on mf to public; { neo (néo-titulaires) ------------------------------------------------------} create table neo ( n_serial integer, n_pf integer, numen char(13), civilite char(3), nom char(20), prenom char(20), prenom_2eme char(20), d_naissance date, c_gr char(4), c_gf_a char(2), c_gf_e char(2), c_dm_recru char(5), c_dm_poste char(5), c_dm char(2), sigle char(8), c_et_poste char(8), c_et char(8), c_dm char(2), flag char(1) ) ; grant all on neo to osmose; grant select on neo to public; { nnco (nomenclature nationale : contenus de formation) ----------------------} create table nnco ( c_nndo char(1) not null, c_nnco char(3) not null, l_nnco char(25) not null, l_nnco_memo char(50) not null, c_nnsp_a char(3), c_nnsp_e char(3) ) ; grant all on nnco to osmose; grant select on nnco to public; grant update on nnco to public; grant insert on nnco to public; grant delete on nnco to public; { nndc (nomenclature nationale : décisions candidatures) ---------------------} create table nndc ( c_dc char(2) not null, l_dc char(50) not null ) ; grant all on nndc to osmose; grant select on nndc to public; grant update on nndc to public; grant insert on nndc to public; grant delete on nndc to public; { nnd0 (nomenclature nationale : domaines de formation) ----------------------} create table nndo ( c_nndo char(3) not null, l_nndo char(25) not null, l_nndo_memo char(50) not null ) ; grant all on nndo to osmose; grant select on nndo to public; grant update on nndo to public; grant insert on nndo to public; grant delete on nndo to public; { nnfo (nomenclature nationale : forme de l'organisation) --------------------} create table nnfo ( c_nnfo char(1) not null, l_nnfo char(25) not null, l_nnfo_memo char(50) not null ) ; grant all on nnfo to osmose; grant select on nnfo to public; grant update on nnfo to public; grant insert on nnfo to public; grant delete on nnfo to public; { nnmo (nomenclature nationale : modalité de l'organisation) -----------------} create table nnmo ( c_nnmo char(1) not null, l_nnmo char(25) not null, l_nnmo_memo char(50) not null ) ; grant all on nnmo to osmose; grant select on nnmo to public; grant update on nnmo to public; grant insert on nnmo to public; { nnni (nomenclature nationale : niveau d'initiative) ------------------------} create table nnni ( c_nnni char(1) not null, l_nnni char(25) not null, l_nnni_memo char(50) not null ) ; grant all on nnni to osmose; grant select on nnni to public; grant update on nnni to public; grant insert on nnni to public; { nnob (nomenclature nationale : objectifs de formation) ---------------------} create table nnob ( c_nnob char(2) not null, l_nnob char(25) not null, l_nnob_memo char(50) not null ) ; grant all on nnob to osmose; grant select on nnob to public; grant update on nnob to public; grant insert on nnob to public; grant delete on nnob to public; { nnpc (nomenclature nationale : public cible) -------------------------------} create table nnpc ( c_nnpc char(2) not null, l_nnpc char(25) not null, l_nnpc_memo char(50) not null ) ; grant all on nnpc to osmose; grant select on nnpc to public; grant update on nnpc to public; grant insert on nnpc to public; grant delete on nnpc to public; { nnsp (nomenclature nationale : spécialités - idem table ds) ----------------} create table nnsp ( c_nnsp char(3) not null, l_nnsp char(50) not null, l_nnsp_30 char(30) not null ) ; grant all on nnsp to osmose; grant select on nnsp to public; grant update on nnsp to public; grant insert on nnsp to public; { nntc (nomenclature nationale : type de candidature) ------------------------} create table nntc ( c_nntc char(1) not null, l_nntc char(25) not null, l_nntc_memo char(50) not null ) ; grant all on nntc to osmose; grant select on nntc to public; grant update on nntc to public; grant insert on nntc to public; { nntf (nomenclature nationale : type de formation) --------------------------} create table nntf ( c_nntf char(1) not null, l_nntf char(25) not null, l_nntf_memo char(50) not null ) ; grant all on nntf to osmose; grant select on nntf to public; grant update on nntf to public; grant insert on nntf to public; grant delete on nntf to public; { nntmp (nomenclature nationale : temporaire) ---------------------------------} create table nntmp ( c_mf char(5) not null, { code macro-formation } l_mf char(150) not null, { libellé macro-formation } c_nnob char(2), { code objectif } c_nnni char(1), { code initiative } c_nntc char(1), { code type de candidature } c_af char(7) not null, { code action de formation } l_af char(150) not null, { libellé daction de formation } c_nndo char(1), { code domaine } c_nnco char(3), { code contenu } c_nnpn char(2), { code priorité nationale } c_sf char(10) not null, { code stage de formation } c_nnpc char(2), { code public cible } c_nntf char(1), { code type de formation } c_nnfo char(1), { code forme } c_nnmo char(1) { code madalité } ) ; grant all on nntmp to osmose; grant select on nntmp to public; grant update on nntmp to public; grant insert on nntmp to public; grant delete on nntmp to public; { omp (OM permanents) -------------------------------------------------------} create table omp ( n_pf integer not null, d_debut date not null, d_fin date not null fc char(2), { fc = [FC] = Formation Continue } aa char(2) { aa = [AA] = Animation Académique } ) ; grant all on omp to osmose; grant select on omp to public; grant update on omp to public; grant insert on omp to public; grant delete on omp to public; { pa (priorités académiques) -----------------------------------------------} create table pa ( c_pa char(2), l_pa char(50) ) ; grant all on pa to osmose; grant select on pa to public; grant update on pa to public; grant insert on pa to public; grant delete on pa to public; { pdf (parcours de formation) -----------------------------------------------} create table pdf ( c_pdf char(2), l_pdf char(50), c_pdf_1 char(2) ) ; grant all on pdf to osmose; grant select on pdf to public; grant update on pdf to public; grant insert on pdf to public; grant delete on pdf to public; { pep4 (pep_4) ---------------------------------------------------------------} create table pep4 ( n_pep4 integer, c_sa char(2), modalite char(3), civilite char(3), aa_naissance daitetime year to year, numen char(13), nom char(20), prenom char(20), l_ac char(20), c_gr char(4), m_gr char(10), c_gf_e char(2), c_dm_recru char(5), c_dm_poste char(5), l_dm_poste char(50), sigle char(10), c_et char(8), l_sigle char(38), l_et char(38), l_ld char(30), p_s char(1), quotite decimal(4,2), site char(4), doublon smallint, c_et_bis char(8), envoi char(1), d_maj_et date, c_bdd char(1), flag char(1) ) ; grant all on pep4 to osmose; grant select on pep4 to public; grant update on pep4 to public; grant insert on pep4 to public; grant delete on pep4 to public; { pf (personnel formation) -------------------------------------------------} create table pf ( nom_cf char(5) not null, { 5 premiers caractères du nom } n_pf serial not null, { Numéro d'identification de la personne } n_pf_bis integer, { Numéro d'une table d'origine extérieure } numen char(13), { numen } civilite char(3) not null, { [M.] [MME] } nom char(20) not null, { Nom } nom_2 char(20), { Nom d'EPP ou AGORA si orthographe diff. } prenom char(20), { Prénom } prenom_2 char(20), { Prénom } d_naissance date, { Date de naissance } adresse char(38), { Adresse } lieu_dit char(38), { Libellé Lieu-dit } c_cp char(5), { Code "Code Postal" } l_ld char(30), { Lieu distribué } c_gf char(2), { Code Grade-Fonction (caduque) } c_gf_a char(2), { Code Grade-Fonction [A]dministratif } c_gf_a_1 char(2), { Code Grade-Fonction [A]dministratif } c_gf_a_2 char(2), { Code Grade-Fonction [A]dministratif } c_gf_a_3 char(2), { Code Grade-Fonction [A]dministratif } c_gf_a_4 char(2), { Code Grade-Fonction [A]dministratif } c_gf_e char(2), { Code Grade-Fonction [E]nseignant } c_gf_e_1 char(2), { Code Grade-Fonction [E]nseignant } c_gf_e_2 char(2), { Code Grade-Fonction [E]nseignant } c_gf_e_3 char(2), { Code Grade-Fonction [E]nseignant } c_gf_e_4 char(2), { Code Grade-Fonction [E]nseignant } c_gr char(4), { Code Grade } c_gr_1 char(4), { Code Grade } c_gr_2 char(4), { Code Grade } c_gr_3 char(4), { Code Grade } c_gr_4 char(4), { Code Grade } c_dm char(2), { Code Discipline-Matière "OSMOSE" } c_dm_recru char(5), { Code Discipline-Matière de recrutement } c_dm_poste char(5), { Code Discipline-Matière de poste } c_sa char(2), { Code Situation Administrative } c_sa_1 char(2), { Code Situation Administrative rentrée -1 } c_sa_2 char(2), { Code Situation Administrative rentrée -1 } c_sa_3 char(2), { Code Situation Administrative rentrée -1 } c_sa_4 char(2), { Code Situation Administrative rentrée -1 } c_et char(8) not null, { Code établissement } c_et_1 char(8), { Code établissement n -1 } c_et_2 char(8), { Code établissement n -2 } c_et_3 char(8), { Code établissement n -3 } c_et_4 char(8), { Code établissement n -4 } e_mail char(50), { E-mail } envoi char(1) not null, { [E]tablissement [P]ersonnel } d_maj_et date not null, { Date de mise-à-jour de l'établissement } d_affectation date, { Date d'affectation (début) } division char(38), { Division ou Service } limite_ce char(1), { Limite du nbre de stages du C.E. } pf_0 char(2), { Personnel de l'Académie rentrée n } pf_1 char(2), { Personnel de l'Académie rentrée n -1 } pf_2 char(2), { Personnel de l'Académie rentrée n -2 } pf_3 char(2), { Personnel de l'Académie rentrée n -3 } pf_4 char(2), { Personnel de l'Académie rentrée n -4 } cf_0 char(2), { Candidature[s] formation rentrée n } cf_1 char(2), { Candidature[s] formation rentrée n -1 } cf_2 char(2), { Candidature[s] formation rentrée n -2 } cf_3 char(2), { Candidature[s] formation rentrée n -3 } cf_4 char(2), { Candidature[s] formation rentrée n -4 } if_0 char(2), { Intervention(s)formateur rentrée n } if_1 char(2), { Intervention(s)formateur rentrée n -1 } if_2 char(2), { Intervention(s)formateur rentrée n -2 } if_3 char(2), { Intervention(s)formateur rentrée n -3 } if_4 char(2), { Intervention(s)formateur rentrée n -4 } c_bdd char(1), { Code Base de données [E]nseignant [A]dm. } flag char(1) { Flag } ) ; grant all on pf to osmose; grant select on pf to public; grant update on pf to public; grant insert on pf to public; grant delete on pf to public; { piaf (procédure d'inscription aux actions de formation) --------------------} create table piaf ( n_pf integer, numen char(13), civilite char(4), nom char(20), prenom char(20), d_naissance date, m_gr char(10), m_dm char(10), c_bdd char(1), c_et char(8), t_affectation char(3), voeu_1 char(10), avis_ce_1 char(1), voeu_2 char(10), avis_ce_2 char(1), voeu_3 char(10), avis_ce_3 char(1), limite_ce char(1), limite_cf char(1), d_item char(255), motif_ce char(150), flag char(1) ) ; grant all on piaf to osmose; grant select on piaf to public; grant update on piaf to public; grant insert on piaf to public; grant delete on piaf to public; { pn (priorités nationales) ------------------------------------------------} create table pn ( direction char(5) not null, c_pn char(2) not null, l_pn char(50) not null ) ; grant all on pn to osmose; grant select on pn to public; grant update on pn to public; grant insert on pn to public; grant delete on pn to public; { rp (répartitions pédagogiques des certificats administratifs) ------------} create table rp ( n_ca integer not null, rentree_yy char(2) not null, c_sf char(10) not null, montant_e decimal(10,2) not null, montant_f decimal(10,2) not null ) ; grant all on rp to osmose; grant select on rp to public; grant update on rp to public; grant insert on rp to public; grant delete on rp to public; { sa (situations administratives -------------------------------------------} create table sa ( c_sa char(2) not null, l_sa char(30) not null, c_bdd char(1) ) ; grant all on sa to osmose; grant select on sa to public; grant update on sa to public; grant insert on sa to public; grant delete on sa to public; { ser (services) ------------------------------------------------------------} create table ser ( c_div char(2), c_ser char(2) not null, l_ser char(30) not null ) ; grant all on ser to osmose; grant select on ser to public; grant update on ser to public; grant insert on ser to public; grant delete on ser to public; { sf (stages de formation) -------------------------------------------------} create table sf ( c_bdf char(1) not null, c_bdd char(1) not null, c_sf char(10) not null, c_af char(7) not null, c_ma char(1), c_sm smallint, c_af_1 char(7), c_ma_1 char(1), c_af_2 char(7), c_ma_2 char(1), etat char(1) not null, l_sf char(50), initiative char(1) not null, c_nnmo char(1) not null, t_sf char(1) not null, t_cf char(1) not null, c_nnpc char(2), c_nnfo char(1), minimum smallint, maximum smallint, duree_h smallint, duree_j_0 decimal(4,1), duree_j_1 decimal(4,1), d_valide date, d_mailing_cf date, t_mailing_cf datetime hour to second, d_mailing_if date, d_mailing_et date, d_debut date, d_fin date, d_convoc date, d_om date, n_pf_so integer, frais_sncf_1 char(2), frais_sncf_2 char(2), frais_ratp char(2), frais_avion char(2), nb_cf_1 char(77), nb_cf_2 char(77), nb_cf_3 char(77), nb_om_1 char(77), nb_om_2 char(77), nb_om_3 char(77), nb_cb_1 char(77), nb_cb_2 char(77), nb_cb_3 char(77), p_vacations_ct decimal(6,2), p_vacations_hs decimal(6,2), p_vacations_cc decimal(6,2), p_vacations_tp decimal(6,2), p_hse decimal(6,2), p_hse_iufm decimal(6,2), p0_pedagogie decimal(10,2), p0_conventions decimal(10,2), p0_vacations_ct decimal(10,2), p0_vacations_hs decimal(10,2), p0_vacations_cc decimal(10,2), p0_vacations_tp decimal(10,2), p0_transport_a decimal(10,2), p0_sejour_a decimal(10,2), p0_transport_s decimal(10,2), p0_sejour_s decimal(10,2), p1_pedagogie decimal(10,2), p1_conventions decimal(10,2), p1_vacations_ct decimal(10,2), p1_vacations_hs decimal(10,2), p1_vacations_cc decimal(10,2), p1_vacations_tp decimal(10,2), p1_transport_a decimal(10,2), p1_sejour_a decimal(10,2), p1_transport_s decimal(10,2), p1_sejour_s decimal(10,2), r_vacations_ct decimal(6,2), r_vacations_hs decimal(6,2), r_vacations_cc decimal(6,2), r_vacations_tp decimal(6,2), r_hse decimal(6,2), r_hse_iufm decimal(6,2), r0_pedagogie decimal(10,2), r0_conventions decimal(10,2), r0_vacations_ct decimal(10,2), r0_vacations_hs decimal(10,2), r0_vacations_cc decimal(10,2), r0_vacations_tp decimal(10,2), r0_transport_a decimal(10,2), r0_sejour_a decimal(10,2), r0_transport_s decimal(10,2), r0_sejour_s decimal(10,2), r1_pedagogie decimal(10,2), r1_conventions decimal(10,2), r1_vacations_ct decimal(10,2), r1_vacations_hs decimal(10,2), r1_vacations_cc decimal(10,2), r1_vacations_tp decimal(10,2), r1_transport_a decimal(10,2), r1_sejour_a decimal(10,2), r1_transport_s decimal(10,2), r1_sejour_s decimal(10,2), collectif smallint, designe smallint, reserve smallint, voeu_1 smallint, voeu_2 smallint, voeu_3 smallint, inscrits smallint, presents smallint, assidus smallint, absents smallint, excuses smallint, ad smallint, ae smallint, ai smallint, am smallint, as smallint, ca smallint, ci smallint, dc smallint, ha smallint, hd smallint, hp smallint, la smallint, ls smallint, nr smallint, pd smallint, pr smallint, rs smallint, sa smallint, sc smallint, sf smallint, si smallint, tp smallint, a_hommes_a smallint, a_hommes_b smallint, a_hommes_c smallint, a_hommes_d smallint, a_femmes_a smallint, a_femmes_b smallint, a_femmes_c smallint, a_femmes_d smallint, e_hommes_a smallint, e_hommes_b smallint, e_hommes_c smallint, e_hommes_d smallint, e_femmes_a smallint, e_femmes_b smallint, e_femmes_c smallint, e_femmes_d smallint, mois_add datetime month to month, mois_upd datetime month to month, flag char(1) ) ; grant all on sf to osmose; grant select on sf to public; grant update on sf to public; grant insert on sf to public; grant delete on sf to public; { sg (services gestionnaires) ----------------------------------------------} create table sg ( c_bdd char(1) not null, service char(8) not null, logname_rp char(8) not null, logname_rs char(8) not null, c_ac char(2) not null, c_et_0 char(8) not null, c_et_1 char(8), tarif money(5,2), timbre_0 char(20), timbre_1 char(50), timbre_2 char(50), timbre_3 char(50), timbre_4 char(50), en_tete_00 char(34), en_tete_01 char(34), en_tete_02 char(34), en_tete_03 char(34), en_tete_04 char(34), en_tete_05 char(34), en_tete_06 char(34), en_tete_07 char(34), en_tete_08 char(34), en_tete_09 char(34), en_tete_10 char(34), en_tete_11 char(34), en_tete_12 char(34), en_tete_13 char(34), en_tete_14 char(34), en_tete_15 char(34), cachet_11 char(38), cachet_12 char(38), cachet_13 char(38), cachet_14 char(38), cachet_15 char(38), cachet_21 char(38), cachet_22 char(38), cachet_23 char(38), cachet_24 char(38), cachet_25 char(38) ) ; grant all on sg to osmose; grant select on sg to public; grant update on sg to public; grant insert on sg to public; grant delete on sg to public; { sh (suivi historique) ----------------------------------------------------} create table sh ( c_bdd char(1) not null, c_sf char(10) not null, t_sh char(2), d_valide date, d_mailing date, t_mailing datetime hour to second, traitement char(1), ca smallint, prevues smallint not null, traitees smallint not null, envoyees smallint not null, abortees smallint not null, postees smallint, n_pf integer not null, c_et char(8), pj char(1), logname char(8) not null ) ; grant all on sh to osmose; grant select on sh to public; grant update on sh to public; grant insert on sh to public; grant delete on sh to public; { sys_c (colonnes) ------------------------------------------------------------} create table sys_c ( colname char(18), tabid integer, colno smallint, coltype smallint, collength smallint, type_length char(30), attention char(1), collib_0 char(38), collib_1 char(77), collib_2 char(77), collib_3 char(77), collib_4 char(77), collib_5 char(77) ) ; grant all on sys_c to public; { sys_t (tables) --------------------------------------------------------------} create table sys_t ( tabid integer, tabname char(18), tabname_2 char(18), tablibe char(60) ) ; grant all on sys_t to public; { sysmenus (system catalog) ---------------------------------------------------} { create table sysmenus ( menuname char(18), title char(60) ) ; grant all on sysmenus to public; } { sysmenuitems (system catalog) -----------------------------------------------} { create table sysmenuitems ( imenuname char(18), itemnum integer, mtext char(60), mtype char(1), progname char(60) ) ; grant all on sysmenuitems to public; } { t_cf (temporaire candidatures formation) -----------------------------------} create table t_cf ( n_pf integer not null, c_sf char(10) not null emargement char(1), presence smallint, duree_h smallint ) ; grant all on t_cf to public; { t_cm (typologie communes) --------------------------------------------------} create table t_cm ( t_cm char(5) not null, t_cm_l char(30) not null ) ; grant all on t_cm to public; { t_ne (typologie nature établissement) --------------------------------------} create table t_ne ( t_ne char(2) not null, t_ne_a char(1), t_ne_a_l char(30), t_ne_e char(1), t_ne_e_l char(30) ) ; grant all on t_ne to public; { tbb (temporaire bilans budgétaires) ---------------------------------------} create table tbb ( c_df char(3), c_af char(7), c_sf char(10), r_vacations_ct decimal(6,2), r_vacations_hs decimal(6,2), r_vacations_cc decimal(6,2), r_vacations_tp decimal(6,2), r_hse decimal(6,2), r_hse_iufm decimal(6,2), r0_pedagogie decimal(10,2), r0_conventions decimal(10,2), r0_vacations_ct decimal(10,2), r0_vacations_hs decimal(10,2), r0_vacations_cc decimal(10,2), r0_vacations_tp decimal(10,2), r0_transport_a decimal(10,2), r0_sejour_a decimal(10,2), r0_transport_s decimal(10,2), r0_sejour_s decimal(10,2), r1_pedagogie decimal(10,2), r1_conventions decimal(10,2), r1_vacations_ct decimal(10,2), r1_vacations_tp decimal(10,2), r1_vacations_hs decimal(10,2), r1_vacations_cc decimal(10,2), r1_transport_a decimal(10,2), r1_sejour_a decimal(10,2), r1_transport_s decimal(10,2), r1_sejour_s decimal(10,2) ) ; grant all on tbb to osmose; grant select on tbb to public; grant update on tbb to public; grant insert on tbb to public; grant delete on tbb to public; { tmp (temporaire) ----------------------------------------------------------} create table tmp ( c_df char(3), c_af char(7), c_sf char(10), collectif smallint, designe smallint, reserve smallint, voeu_1 smallint, voeu_2 smallint, voeu_3 smallint, inscrits smallint, presents smallint, assidus smallint, absents smallint, excuses smallint, ad smallint, ae smallint, ai smallint, am smallint, as smallint, ca smallint, ci smallint, dc smallint, ha smallint, hd smallint, hp smallint, la smallint, ls smallint, nr smallint, pd smallint, pr smallint, rs smallint, sa smallint, sc smallint, sf smallint, si smallint, tp smallint, a_hommes_a smallint, a_hommes_b smallint, a_hommes_c smallint, a_hommes_d smallint, a_femmes_a smallint, a_femmes_b smallint, a_femmes_c smallint, a_femmes_d smallint, e_hommes_a smallint, e_hommes_b smallint, e_hommes_c smallint, e_hommes_d smallint, e_femmes_a smallint, e_femmes_b smallint, e_femmes_c smallint, e_femmes_d smallint ) ; grant all on tmp to osmose; grant select on tmp to public; grant update on tmp to public; grant insert on tmp to public; grant delete on tmp to public; { ts (transports-séjours) --------------------------------------------------} create table ts ( n_pf integer, numen char(13), civilite char(3), nom char(20), prenom char(20), c_bdf char(1), c_bdd char(1), c_af char(7), c_sf char(10), c_kheops char(10), l_kheops char(15), rentree datetime year to year, exercice datetime year to year, ministere char(3), chapitre char(5), article char(2), paragraphe char(2), n_piece integer, c_sg char(3), t_ts char(1), usage_vp char(1), transport decimal(7,2), sejour decimal(7,2), objet char(69), flag char(1) ) ; grant all on ts to osmose; grant select on ts to public; grant update on ts to public; grant insert on ts to public; grant delete on ts to public; { ts_t (transports-séjours temporaire) ---------------------------------------} create table ts_t ( c_bdf char(1), c_bdd char(1), rentree datetime year to year, c_af char(7), c_sf char(10), n_pf integer, paragraphe char(2), l_kheops char(25), exercice datetime year to year, ministere char(3), chapitre char(5), article char(2), numen char(13), civilite char(3), nom char(20), prenom char(20), c_sg char(3), n_piece integer, t_ts char(1), objet char(69), transport decimal(7,2), usage_vp char(1), sejour decimal(7,2), c_kheops char(10) ) ; grant all on ts_t to osmose; grant select on ts_t to public; grant update on ts_t to public; grant insert on ts_t to public; grant delete on ts_t to public; { tsa (transport-séjour animateur) ------------------------------------------} create table tsa ( n_pf integer not null, c_sf char(10) not null, exercice datetime year to year, transport_a decimal(7,2), sejour_a decimal(7,2), flag char(1) ) ; grant all on tsa to osmose; grant select on tsa to public; grant update on tsa to public; grant insert on tsa to public; grant delete on tsa to public; { tsdp (transport-séjour dépense prévisionnelle) -----------------------------} create table tsdp ( c_sf char(10), P0_transport_a decimal(10,2), P0_sejour_a decimal(10,2), P0_transport_s decimal(10,2), P0_sejour_s decimal(10,2), P1_transport_a decimal(10,2), P1_sejour_a decimal(10,2), P1_transport_s decimal(10,2), P1_sejour_s decimal(10,2) ) ; grant all on tsdp to osmose; grant select on tsdp to public; grant update on tsdp to public; grant insert on tsdp to public; grant delete on tsdp to public; { tss_t (transport-séjour stagiaire temporaire) -------------------------------} create table tss_t ( n_pf integer not null, c_sf char(10) not null, exercice datetime year to year, transport_s decimal(7,2), sejour_s decimal(7,2) ) ; grant all on tss_t to osmose; grant select on tss_t to public; grant update on tss_t to public; grant insert on tss_t to public; grant delete on tss_t to public; { uaa (table nationale des établissements - voir UAA ci-après) --------------} { create table uaa ( c_et char(8), { rne char(8), } c_ne char(3), { nat char(3), } etprco char(4), { etprco char(4), } c_ac char(3), { acadco char(3), } minsco char(2), { minsco char(2), } sigle char(6), { sig char(6), } sigle_long char(14), { sgl char(14), } l_et char(38), { l_et char(38), } dnp char(30), { dnp char(30), } dnc char(30), { dnc char(30), } mdi char(32), { mdi char(32), } adresse char(32), { adr char(32), } lieu_dit char(26), { lds char(26), } bpt char(7), { bpt char(7), } dex char(2), { dex char(2), } c_cp char(5), { cpl char(5), } l_ld char(26), { bds char(26), } telephone char(20), { tel char(20), } c_cm char(6), { rad char(6), } c_et_rattache char(8), { rat char(8), } mer char(8), { mer char(8), } d_ouverture date, { dd date, } d_fermeture date, { df date, } t_et char(2), { sta char(2), } c_tg char(8), { cpo char(8), } l_tg char(20), { lpo char(20), } n_zone char(5), { lno char(5), } bgp char(6), { bgp char(6), } sec char(1) { sec char(1) } ) ; grant all on uaa to osmose; grant select on uaa to public; grant update on uaa to public; grant insert on uaa to public; grant delete on uaa to public; } { uf (unités de formation) -------------------------------------------------} create table uf ( c_af char(7) not null, c_uf char(10) not null, c_uf_ptr char(10) not null, c_sf char(10) not null, alias char(1) ) ; grant all on uf to osmose; grant select on uf to public; grant update on uf to public; grant insert on uf to public; grant delete on uf to public; { ut (unités de traitement) ------------------------------------------------} create table ut ( processus char(28, c_ut char(20), objet char(76), objet_1 char(76), objet_2 char(76), objet_3 char(76), objet_4 char(76), objet_5 char(76) ) ; grant all on ut to osmose; grant select on ut to public; grant update on ut to public; grant insert on ut to public; { zep (zone d'éducation prioritaire) ----------------------------------------} create table zep ( c_dp char(2) not null, c_df char(3), c_dm char(2), collectif smallint, designe smallint, reserve smallint, voeu_1 smallint, voeu_2 smallint, voeu_3 smallint, inscrits smallint, presents smallint, assidus smallint, absents smallint, excuses smallint, ad smallint, ae smallint, ai smallint, am smallint, as smallint, ca smallint, ci smallint, dc smallint, ha smallint, hd smallint, hp smallint, la smallint, ls smallint, nr smallint, pd smallint, pr smallint, rs smallint, sa smallint, sc smallint, sf smallint, si smallint, tp smallint, a_hommes_a smallint, a_hommes_b smallint, a_hommes_c smallint, a_hommes_d smallint, a_femmes_a smallint, a_femmes_b smallint, a_femmes_c smallint, a_femmes_d smallint, e_hommes_a smallint, e_hommes_b smallint, e_hommes_c smallint, e_hommes_d smallint, e_femmes_a smallint, e_femmes_b smallint, e_femmes_c smallint, e_femmes_d smallint ) ; grant all on zep to osmose; grant select on zep to public; grant update on zep to public; grant insert on zep to public; grant delete on zep to public; {------------------------------------- FIN ------------------------------------}
I-3. Annexes
▲ I-3.2.5. OSMOSE Shells
► I-3.2.6. OSMOSE Sql CREATE_BDD
▼ I-3.2.7. OSMOSE Sql CREATE_IDX
Mis à jour 25/02/2024 à 21h11 par APL-AML
Tags:
informix,
méthodologie,
sgbd,
sql
- Catégories
- ■ APL-AML , I- L’ART , I-3. Annexes , I-3.2. BDD Formation continue
Consulter le profil
Marquer ce blog comme lu
