probleme de bind variable

code appelé :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
CREATE OR REPLACE PACKAGE BODY Pkg_Loc_Facturation
AS
/************************************************************************/
/* AUT : Sylvain MOUTON / Aptus                                   */
/* BRA : LOC                                                            */
/* NOM : Pkg_loc_facturation                                             */
/* DAT : 02/02/2006                                                     */
/* ROL : Permet l'insertion d'un facture et la mise à jour de la resa associée   */
/************************************************************************/
   PROCEDURE ps_loc_insert_facture (
      par_fl_dte            IN   FACTURE_LABS.fl_dte%TYPE,
      par_fl_mntttc         IN   FACTURE_LABS.fl_mntttc%TYPE,
      par_fl_mnttva         IN   FACTURE_LABS.fl_mnttva%TYPE,
      par_fl_mntht          IN   FACTURE_LABS.fl_mntht%TYPE,
      par_fl_txtva          IN   FACTURE_LABS.fl_txtva%TYPE,
      par_fl_loc_id         IN   FACTURE_LABS.fl_loc_id%TYPE,
      par_fl_acq_id         IN   FACTURE_LABS.fl_acq_id%TYPE,
      par_fl_utl_id         IN   FACTURE_LABS.fl_utl_id%TYPE,
      par_fl_al_id          IN   FACTURE_LABS.fl_al_id%TYPE,
      par_fl_tf_id          IN   FACTURE_LABS.fl_tf_id%TYPE,
      par_fl_lib            IN   FACTURE_LABS.fl_lib%TYPE,
      par_fl_mntmodif       IN   FACTURE_LABS.fl_mntmodif%TYPE,
      par_fl_commodif       IN   FACTURE_LABS.fl_commodif%TYPE,
      par_lfl_lot_id        IN   LIGNE_FACTURE_LABS.lfl_lot_id%TYPE,
      par_lfl_resa_id       IN   LIGNE_FACTURE_LABS.lfl_resa_id%TYPE,
      par_lfl_mntttc        IN   LIGNE_FACTURE_LABS.lfl_mntttc%TYPE,
      par_lfl_mntht         IN   LIGNE_FACTURE_LABS.lfl_mntht%TYPE,
      par_lfl_lib           IN   LIGNE_FACTURE_LABS.lfl_lib%TYPE,
      par_pgm_id            IN   LIGNE_FACTURE_LABS.lfl_pgm_id%TYPE,
      par_avl_fl_mntttc     IN   FACTURE_LABS.fl_mntttc%TYPE,
      par_avl_fl_mnttva     IN   FACTURE_LABS.fl_mnttva%TYPE,
      par_avl_fl_mntht      IN   FACTURE_LABS.fl_mntht%TYPE,
      par_avl_fl_tf_id      IN   FACTURE_LABS.fl_tf_id%TYPE,
      par_avl_fl_lib        IN   FACTURE_LABS.fl_lib%TYPE,
      par_avl_fl_mntmodif   IN   FACTURE_LABS.fl_mntmodif%TYPE,
      par_avl_fl_commodif   IN   FACTURE_LABS.fl_commodif%TYPE,
      par_avl_lfl_mntttc    IN   FACTURE_LABS.fl_mntmodif%TYPE,
      par_avl_lfl_mntht     IN   LIGNE_FACTURE_LABS.lfl_mntht%TYPE,
      par_avl_fl_fl_id		IN   FACTURE_LABS.fl_fl_id%TYPE,
      par_avb_fl_mntttc     IN   FACTURE_LABS.fl_mntttc%TYPE,
      par_avb_fl_mnttva     IN   FACTURE_LABS.fl_mnttva%TYPE,
      par_avb_fl_mntht      IN   FACTURE_LABS.fl_mntht%TYPE,
      par_avb_fl_tf_id      IN   FACTURE_LABS.fl_tf_id%TYPE,
      par_avb_fl_lib        IN   FACTURE_LABS.fl_lib%TYPE,
      par_avb_fl_mntmodif   IN   FACTURE_LABS.fl_mntmodif%TYPE,
      par_avb_fl_commodif   IN   FACTURE_LABS.fl_commodif%TYPE,
      par_avb_lfl_mntttc    IN   FACTURE_LABS.fl_mntttc%TYPE,
      par_avb_lfl_mntht     IN   LIGNE_FACTURE_LABS.lfl_mntht%TYPE,
      par_avb_fl_fl_id		IN   FACTURE_LABS.fl_fl_id%TYPE,
	  par_date_loc		VARCHAR2,
   	  par_return IN OUT NUMBER
   )
   IS
/************************************************************************/
/* AUT : Sylvain MOUTON / Aptus                                   */
/* BRA : LOC                                                            */
/* NOM : Pkg_loc_facturation                                             */
/* DAT : 02/02/2006                                                     */
/* ROL : Permet l'insertion d'un facture et la mise à jour de la resa associée   */
/************************************************************************/
      par_fl_cod_num   	   NUMBER;
      par_av_cod_num   	   NUMBER;
      par_fl_id_l   	   FACTURE_LABS.fl_id%TYPE;
      par_fl_id_b   	   FACTURE_LABS.fl_id%TYPE;
	  par_fl_cod_l   	   FACTURE_LABS.fl_cod%TYPE;
  	  par_fl_cod_b   	   FACTURE_LABS.fl_cod%TYPE;
	  par_av_cod_l   	   FACTURE_LABS.fl_cod%TYPE;
	  par_av_cod_b   	   FACTURE_LABS.fl_cod%TYPE;
 
   BEGIN   
 
      SELECT MAX (SUBSTR (fl_cod, 3, 6))
        INTO par_fl_cod_num
        FROM FACTURE_LABS
       WHERE fl_cod LIKE 'FL%';
 
 
	  IF (par_fl_cod_num > 99999) THEN
	  	  par_fl_cod_l := SUBSTR('000000' || TO_CHAR (par_fl_cod_num + 1), LENGTH('000000' || TO_CHAR (par_fl_cod_num + 1)) - 5);
		  par_fl_cod_b := SUBSTR('000000' || TO_CHAR (par_fl_cod_num + 2), LENGTH('000000' || TO_CHAR (par_fl_cod_num + 2)) - 5);
	  ELSE
	  	  par_fl_cod_l := SUBSTR('00000' || TO_CHAR (par_fl_cod_num + 1), LENGTH('00000' || TO_CHAR (par_fl_cod_num + 1)) - 4);
  	  	  par_fl_cod_b := SUBSTR('00000' || TO_CHAR (par_fl_cod_num + 2), LENGTH('00000' || TO_CHAR (par_fl_cod_num + 2)) - 4);
	  END IF;
      SELECT MAX (SUBSTR (fl_cod, 3, 6))
       INTO par_av_cod_num
       FROM FACTURE_LABS
      WHERE fl_cod LIKE 'AV%';
	  IF (par_av_cod_num > 99999) THEN
	  	  par_av_cod_l := SUBSTR('000000' || TO_CHAR (par_av_cod_num + 1), LENGTH('000000' || TO_CHAR (par_av_cod_num + 1)) - 5);
		  par_av_cod_b := SUBSTR('000000' || TO_CHAR (par_av_cod_num + 2), LENGTH('000000' || TO_CHAR (par_av_cod_num + 2)) - 5);
	  ELSE
	  	  par_av_cod_l := SUBSTR('00000' || TO_CHAR (par_av_cod_num + 1), LENGTH('00000' || TO_CHAR (par_av_cod_num + 1)) - 4);
		  par_av_cod_b := SUBSTR('00000' || TO_CHAR (par_av_cod_num + 2), LENGTH('00000' || TO_CHAR (par_av_cod_num + 2)) - 4);
	  END IF;
 
      -- Insertion facture locataire
      INSERT INTO FACTURE_LABS
                  (fl_cod, fl_dte,
                   fl_txtva, fl_loc_id, fl_acq_id, fl_al_id,
                   fl_tf_id, fl_lib, fl_mntttc, fl_fl_id, fl_tier_id,
                   fl_commodif, fl_typreglement, fl_flgimprimee,
                   fl_dtepaiement, fl_libdomic, fl_mntht, fl_mnttva,
                   fl_mntmodif, fl_numpiece, fl_flgfaccompta, fl_flgpayee,
                   fl_utl_id
                  )
           VALUES ('FL' || par_fl_cod_l, par_fl_dte,
                   par_fl_txtva, par_fl_loc_id, NULL, par_fl_al_id,
                   par_fl_tf_id, par_fl_lib, par_fl_mntttc, NULL, NULL,
                   par_fl_commodif, NULL, -1,
                   NULL, NULL, par_fl_mntht, par_fl_mnttva,
                   par_fl_mntmodif, NULL, 0, 0,
                   par_fl_utl_id
                  );
 
      -- Insertion facture bailleur
      INSERT INTO FACTURE_LABS
                  (fl_cod, fl_dte,
                   fl_txtva, fl_loc_id, fl_acq_id, fl_al_id,
                   fl_tf_id, fl_lib, fl_mntttc, fl_fl_id, fl_tier_id,
                   fl_commodif, fl_typreglement, fl_flgimprimee,
                   fl_dtepaiement, fl_libdomic, fl_mntht, fl_mnttva,
                   fl_mntmodif, fl_numpiece, fl_flgfaccompta, fl_flgpayee,
                   fl_utl_id
                  )
           VALUES ('FL' || par_fl_cod_b, par_fl_dte,
                   par_fl_txtva, NULL, par_fl_acq_id, par_fl_al_id,
                   par_fl_tf_id, par_fl_lib, par_fl_mntttc, NULL, NULL,
                   par_fl_commodif, NULL, -1,
                   NULL, NULL, par_fl_mntht, par_fl_mnttva,
                   par_fl_mntmodif, NULL, 0, 0,
                   par_fl_utl_id
                  );
 
 
     SELECT fl_id
        INTO par_fl_id_l
     FROM FACTURE_LABS
     WHERE fl_cod = 'FL' || par_fl_cod_l;
 
      SELECT fl_id
        INTO par_fl_id_b
        FROM FACTURE_LABS
       WHERE fl_cod = 'FL' || par_fl_cod_b;
 
      -- Insertion de la ligne associée à la facture pour le locataire
	    INSERT INTO LIGNE_FACTURE_LABS
                  (lfl_lot_id, lfl_fl_id, lfl_resa_id,
                   lfl_mntttc, lfl_mntht, lfl_txtva, lfl_lib,
                   lfl_pgm_id, lfl_pl_id, lfl_liv_id, lfl_edl_id, lfl_bai_id,
                   lfl_cod, lfl_libctranaly, lfl_com, lfl_nbrut
                  )
           VALUES (par_lfl_lot_id, par_fl_id_l, par_lfl_resa_id,
                   par_lfl_mntttc, par_lfl_mntht, par_fl_txtva, par_lfl_lib,
                   par_pgm_id, NULL, NULL, NULL, NULL,
                   NULL, NULL, NULL, NULL
                  );
 
      -- Insertion de la ligne associée à la facture pour le bailleur
      INSERT INTO LIGNE_FACTURE_LABS
                  (lfl_lot_id, lfl_fl_id, lfl_resa_id,
                   lfl_mntttc, lfl_mntht, lfl_txtva, lfl_lib,
                   lfl_pgm_id, lfl_pl_id, lfl_liv_id, lfl_edl_id, lfl_bai_id,
                   lfl_cod, lfl_libctranaly, lfl_com, lfl_nbrut
                  )
           VALUES (par_lfl_lot_id, par_fl_id_b, par_lfl_resa_id,
                   par_lfl_mntttc, par_lfl_mntht, par_fl_txtva, par_lfl_lib,
                   par_pgm_id, NULL, NULL, NULL, NULL,
                   NULL, NULL, NULL, NULL
                  );
 
      -- Mise à jour du champ facturer de la table reservations
      UPDATE RESERVATIONS
         SET resa_flgfacture = -1, RESA_DTESIGNBAIL = TO_DATE(par_date_loc,'DD/MM/YYYY')
       WHERE resa_id = par_lfl_resa_id;
 
      IF (par_avl_fl_mntttc <> -1)
      THEN
 
         -- Insertion avoir Locataires
         INSERT INTO FACTURE_LABS
                     (fl_cod, fl_dte,
                      fl_txtva, fl_loc_id, fl_acq_id, fl_al_id,
                      fl_tf_id, fl_lib, fl_mntttc,
                      fl_fl_id, fl_tier_id, fl_commodif, fl_typreglement,
                      fl_flgimprimee, fl_dtepaiement, fl_libdomic, fl_mntht,
                      fl_mnttva, fl_mntmodif, fl_numpiece, fl_flgfaccompta,
                      fl_flgpayee, fl_utl_id
                     )
              VALUES ('AV' || par_av_cod_l, par_fl_dte,
                      par_fl_txtva, par_fl_loc_id, NULL, par_fl_al_id,
                      par_avl_fl_tf_id, par_avl_fl_lib, par_avl_fl_mntttc,
                      par_avl_fl_fl_id, NULL, par_avl_fl_commodif, NULL,
                      -1, NULL, NULL, par_avl_fl_mntht,
                      par_avl_fl_mnttva, par_avl_fl_mntmodif, NULL, 0,
                      0, par_fl_utl_id
                     );
 
	      SELECT fl_id
	        INTO par_fl_id_l
	        FROM FACTURE_LABS
	       WHERE fl_cod = 'AV' || par_av_cod_l;
 
         -- Insertion de la ligne associée à l'avoir pour le locataire
         INSERT INTO LIGNE_FACTURE_LABS
                     (lfl_lot_id, lfl_fl_id, lfl_resa_id,
                      lfl_mntttc, lfl_mntht, lfl_txtva,
                      lfl_lib, lfl_pgm_id, lfl_pl_id, lfl_liv_id, lfl_edl_id,
                      lfl_bai_id, lfl_cod, lfl_libctranaly, lfl_com, lfl_nbrut
                     )
              VALUES (par_lfl_lot_id, par_fl_id_l, par_lfl_resa_id,
                      par_avl_lfl_mntttc, par_avl_lfl_mntht, par_fl_txtva,
                      par_avl_fl_lib, par_pgm_id, NULL, NULL, NULL,
                      NULL, NULL, NULL, NULL, NULL
                     );
 
      END IF;
 
      IF (par_avb_fl_mntttc <> -1)
      THEN
 
         -- Insertion avoir Bailleur
         INSERT INTO FACTURE_LABS
                     (fl_cod, fl_dte,
                      fl_txtva, fl_loc_id, fl_acq_id, fl_al_id,
                      fl_tf_id, fl_lib, fl_mntttc,
                      fl_fl_id, fl_tier_id, fl_commodif, fl_typreglement,
                      fl_flgimprimee, fl_dtepaiement, fl_libdomic, fl_mntht,
                      fl_mnttva, fl_mntmodif, fl_numpiece, fl_flgfaccompta,
                      fl_flgpayee, fl_utl_id
                     )
              VALUES ('AV' || par_av_cod_b, par_fl_dte,
                      par_fl_txtva, NULL, par_fl_acq_id, par_fl_al_id,
                      par_avb_fl_tf_id, par_avb_fl_lib, par_avb_fl_mntttc,
                      par_avb_fl_fl_id, NULL, par_avb_fl_commodif, NULL,
                      -1, NULL, NULL, par_avb_fl_mntht,
                      par_avb_fl_mnttva, par_avb_fl_mntmodif, NULL, 0,
                      0, par_fl_utl_id
                     );
 
	      SELECT fl_id
	        INTO par_fl_id_b
	        FROM FACTURE_LABS
	       WHERE fl_cod = 'AV' || par_av_cod_b;
 
         -- Insertion de la ligne associée à l'avoir pour le bailleur
         INSERT INTO LIGNE_FACTURE_LABS
                     (lfl_lot_id, lfl_fl_id, lfl_resa_id,
                      lfl_mntttc, lfl_mntht, lfl_txtva,
                      lfl_lib, lfl_pgm_id, lfl_pl_id, lfl_liv_id, lfl_edl_id,
                      lfl_bai_id, lfl_cod, lfl_libctranaly, lfl_com, lfl_nbrut
                     )
              VALUES (par_lfl_lot_id, par_fl_id_b, par_lfl_resa_id,
                      par_avb_lfl_mntttc, par_avb_lfl_mntht, par_fl_txtva,
                      par_avb_fl_lib, par_pgm_id, NULL, NULL, NULL,
                      NULL, NULL, NULL, NULL, NULL
                     );
      END IF;
 
      COMMIT;
	  par_return := -1;
 
   EXCEPTION
      WHEN OTHERS
      THEN
         ROLLBACK;	
		 par_return := 0;  	
   END ps_loc_insert_facture;
 
END Pkg_Loc_Facturation;
/

code appelant :

Code:

call Pkg_Loc_Facturation.PS_loc_insert_facture(TO_DATE('22/02/2006','DD/MM/YYYY'),409.96,67.18,342.78 ,0.196 ,12301 ,11657 ,1108 ,56 ,1 ,'Honoraires et frais de location' ,0 ,'' ,20812 ,119310 ,409.96 ,342.78 ,'Honoraires et frais de location' ,343 ,-1 ,0 ,0 ,0 ,'' ,0 , '' ,0 ,0 ,0 ,-1 ,0 ,0 ,0 ,'' ,0 ,'' ,0 ,0 ,0 ,'15/02/2006',2)