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
|
DECLARE
/* Declaration du curseur retournant tous les comptes de type SHARE*/
cursor c1 is
SELECT a.account,
a.descr,
a.eff_status,
a.account_type,
a.ledtyp,
b.cptact,
b.cptpas,
b.cptactias,
b.cptpasias
FROM tgl_act_tbl_syr a,
tacxrefsyr b
WHERE a.setid = 'SHARE' AND
a.setid = b.setid AND
a.ledtyp ='FRA' AND
a.account = b.account ;
lv_account tgl_act_tbl_syr.account%TYPE;
lv_descr tgl_act_tbl_syr.descr%TYPE;
lv_eff_status tgl_act_tbl_syr.eff_status%TYPE;
lv_account_type tgl_act_tbl_syr.account_type%TYPE;
lv_ledtyp tgl_act_tbl_syr.ledtyp%TYPE;
lv_cptact tacxrefsyr.cptact%TYPE;
lv_cptpas tacxrefsyr.cptpas%TYPE;
lv_cptactias tacxrefsyr.cptactias%TYPE;
lv_cptpasias tacxrefsyr.cptpasias%TYPE;
BEGIN
OPEN c1;
FETCH c1 INTO lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias;
LOOP
/*On sort du curseur quand il n'y a plus de lignes*/
EXIT WHEN c1%NOT FOUND;
/* Compte de bilan actif A */
IF (lv_account_type ='A' AND NOT(lv_cptact LIKE 'A%' AND lv_cptpas IS NULL AND (lv_cptactias IS NULL OR lv_cptactias LIKE 'IA%') AND (lv_cptpasias IS NULL OR lv_cptpasias LIKE 'IP%')))
THEN
INSERT INTO t_ano_syr VALUES (lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias);
/* Compte de bilan passif C ou L */
ELSIF (lv_account_type = 'C' OR lv_account_type = 'L') AND NOT(lv_cptact IS NULL AND lv_cptpas LIKE 'P%'AND (lv_cptactias IS NULL OR lv_cptactias LIKE 'IA%') AND (lv_cptpasias IS NULL OR lv_cptpasias LIKE 'IP%')))
THEN
INSERT INTO t_ano_syr VALUES (lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias);
/* Compte de bilan mixte D */
ELSIF (lv_account_type ='D' AND NOT(lv_cptact LIKE 'A%' AND lv_cptpas LIKE 'P%' AND (lv_cptactias IS NULL OR lv_cptactias LIKE 'IA%') AND (lv_cptpasias IS NULL OR lv_cptpasias LIKE 'IP%')))
THEN
INSERT INTO t_ano_syr VALUES (lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias);
/* Compte hors bilan actif H*/
ELSIF (lv_account_type ='H' AND NOT(lv_cptact LIKE 'H%' AND lv_cptpas IS NULL AND (lv_cptactias IS NULL OR lv_cptactias LIKE 'IH%') AND (lv_cptpasias IS NULL OR lv_cptpasias LIKE 'II%')))
THEN
INSERT INTO t_ano_syr VALUES (lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias);
/* Compte hors bilan passif I*/
ELSIF (lv_account_type ='I' AND NOT(lv_cptact IS NULL' AND lv_cptpas LIKE 'I%' AND (lv_cptactias IS NULL OR lv_cptactias LIKE 'IH%') AND (lv_cptpasias IS NULL OR lv_cptpasias LIKE 'II%')))
THEN
INSERT INTO t_ano_syr VALUES (lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias);
/* COmpte hors bilan mixte J*/
ELSIF lv_account_type ='J' AND NOT(lv_cptact LIKE 'H%' AND lv_cptpas LIKE 'I%' AND (lv_cptactias IS NULL OR lv_cptactias LIKE 'IH%') AND (lv_cptpasias IS NULL OR lv_cptpasias LIKE 'II%')))
THEN
INSERT INTO t_ano_syr VALUES (lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias);
/* Compte de P&L de charge 'E'*/
ELSIF lv_account_type ='E' AND NOT(lv_cptact IS NULL AND lv_cptactias IS NULL AND (lv_cptpas LIKE '6%') AND (lv_cptpasias LIKE 'I6%' OR lv_cptpasias LIKE 'I7%')))
THEN
INSERT INTO t_ano_syr VALUES (lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias);
/* Compte de P&L de produit 'R'*/
ELSIF lv_account_type ='R' AND NOT(lv_cptact IS NULL AND lv_cptactias IS NULL AND (lv_cptpas LIKE '7%') AND (lv_cptpasias LIKE 'I6%' OR lv_cptpasias LIKE 'I7%')))
THEN
INSERT INTO t_ano_syr VALUES (lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias);
END IF;
FETCH c1 INTO lv_account, lv_descr, lv_eff_status, lv_account_type, lv_ledtyp, lv_cptact, lv_cptpas, lv_cptactias, lv_cptpasias;
END LOOP;
CLOSE c1;
END;
SELECT * FROM t_ano_syr;
Exit; |
