[Home] [Help]
PACKAGE BODY: APPS.JMF_GTA_BATCH_NUMBER_UTIL
Source
1 PACKAGE BODY JMF_GTA_BATCH_NUMBER_UTIL AS
2 --$Header: JMFGBNUB.pls 120.4 2006/06/28 10:50:54 dwang noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JMFGBNUB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package is a collection of the util procedure |
13 --| or function for auto batch numbering. |
14 --| |
15 --| PROCEDURE LIST |
16 --| PROCEDURE Create_Seq |
17 --| PROCEDURE Set_Nextval |
18 --| FUNCTION Next_Value |
19 --| PROCEDURE Drop_Seq |
20 --| FUNCTION Is_Number |
21 --| FUNCTION Verify_Next_Bacth_Number |
22 --| FUNCTION Is_Exist |
23 --| FUNCTION Current_Value |
24 --| |
25 --| HISTORY |
26 --| 20-APR-2005: Qiang Li Created |
27 --| 19-Jan_2006: Qiang Li Updated |
28 --| 19-Jun-2006: Qiang.Li Fix bug 5291644 |
29 --| 27-Jun-2006: Qiang Li Fix Bug 5291644 |
30 --+======================================================================*/
31
32 --==========================================================================
33 -- PROCEDURE NAME:
34 --
35 -- Create_Seq Public
36 --
37 -- DESCRIPTION:
38 --
39 -- This procedure create a new sequence for a given operating unit
40 --
41 -- PARAMETERS:
42 -- In: p_org_id the operating unit id for the new sequence
43 -- p_next_value the start value of the sequence
44 -- Out: x_return_status the return value to indicate the status
45 --
46 -- DESIGN REFERENCES:
47 --
48 --
49 -- CHANGE HISTORY:
50 --
51 -- 30-APR-2005: qiang.li Created.
52 -- 19-JAN-2006: qiang.li Insert value into columns last_update_date
53 -- last_updated_by,creation_date,created_by,
54 -- last_update_login
55 --===========================================================================
56
57 PROCEDURE Create_Seq
58 ( p_org_id IN NUMBER
59 , p_next_value IN NUMBER
60 , x_return_status OUT NOCOPY VARCHAR2
61 )
62 IS
63 l_count NUMBER;
64 l_next_value NUMBER;
65 l_procedure_name VARCHAR2(30) := 'Create_Seq';
66 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
67 l_proc_level NUMBER := fnd_log.level_procedure;
68
69 CURSOR c_chk_seq_exist IS
70 SELECT
71 COUNT(*)
72 FROM jmf_gta_batch_numbering
73 WHERE org_id = p_org_id;
74
75 BEGIN
76 --logging for debug
77 IF (l_proc_level >= l_dbg_level)
78 THEN
79 fnd_log.STRING(l_proc_level
80 ,g_module_prefix || l_procedure_name || '.begin'
81 ,'enter procedure');
82 END IF;
83
84 OPEN c_chk_seq_exist;
85 FETCH c_chk_seq_exist INTO l_count;
86 CLOSE c_chk_seq_exist;
87
88 IF l_count = 0
89 THEN
90 IF p_next_value IS NOT NULL
91 THEN
92 l_next_value := p_next_value;
93 ELSE
94 l_next_value := 1;
95 END IF;
96
97 INSERT INTO jmf_gta_batch_numbering
98 ( org_id
99 , next_value
100 , last_update_date
101 , last_updated_by
102 , creation_date
103 , created_by
104 , last_update_login
105 )
106 VALUES
107 ( p_org_id
108 , l_next_value
109 , SYSDATE
110 , fnd_global.LOGIN_ID()
111 , SYSDATE
112 , fnd_global.LOGIN_ID()
113 , fnd_global.LOGIN_ID()
114 );
115
116 x_return_status := 'S';
117 ELSIF l_count >= 1
118 THEN
119 x_return_status := 'F';
120 END IF;
121
122 --logging for debug
123 IF (l_proc_level >= l_dbg_level)
124 THEN
125 fnd_log.STRING(l_proc_level
126 ,g_module_prefix || l_procedure_name || '.end'
127 ,'end procedure');
128 END IF;
129 END Create_Seq;
130
131 --==========================================================================
132 -- PROCEDURE NAME:
133 --
134 -- Set_Nextval Public
135 --
136 -- DESCRIPTION:
137 --
138 -- This procedure set the sequence's next value for a given operating unit
139 --
140 -- PARAMETERS:
141 -- In: p_org_id the operating unit id for the new sequence
142 -- p_next_value the start value of the sequence
143 -- Out: x_return_status the return value to indicate the status
144 --
145 -- DESIGN REFERENCES:
146 --
147 --
148 -- CHANGE HISTORY:
149 --
150 -- 30-APR-2005: qiang.li Created.
151 -- 19-JAN-2006: qiang.li Update columns last_update_date
152 -- last_updated_by and last_update_login,
153 -- when update next_value
154 --
155 --===========================================================================
156 PROCEDURE Set_Nextval
157 ( p_org_id IN NUMBER
158 , p_next_value IN NUMBER
159 , x_return_status OUT NOCOPY VARCHAR2
160 )
161 IS
162 l_count NUMBER;
163 l_procedure_name VARCHAR2(30) := 'Set_Nextval';
164 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
165 l_proc_level NUMBER := fnd_log.level_procedure;
166
167 CURSOR c_chk_seq_exist IS
168 SELECT COUNT(*)
169 FROM jmf_gta_batch_numbering
170 WHERE org_id = p_org_id;
171
172 BEGIN
173 --logging for debug
174 IF (l_proc_level >= l_dbg_level)
175 THEN
176 fnd_log.STRING(l_proc_level
177 ,g_module_prefix || l_procedure_name || '.begin'
178 ,'enter procedure');
179 END IF;
180
181 OPEN c_chk_seq_exist;
182 FETCH c_chk_seq_exist INTO l_count;
183 CLOSE c_chk_seq_exist;
184
185 IF l_count = 1
186 THEN
187 UPDATE jmf_gta_batch_numbering
188 SET next_value = p_next_value
189 , last_update_date = SYSDATE
190 , last_updated_by = fnd_global.LOGIN_ID()
191 , last_update_login = fnd_global.LOGIN_ID()
192 WHERE org_id = p_org_id;
193
194 x_return_status := 'S';
195 ELSE
196 x_return_status := 'F';
197 END IF;
198
199 --logging for debug
200 IF (l_proc_level >= l_dbg_level)
201 THEN
202 fnd_log.STRING(l_proc_level
203 ,g_module_prefix || l_procedure_name || '.end'
204 ,'end procedure');
205 END IF;
206 END Set_Nextval;
207
208 --==========================================================================
209 -- FUNCTION NAME:
210 --
211 -- Next_Value Public
212 --
213 -- DESCRIPTION:
214 --
215 -- This function get the sequence's current value and then increase it
216 --
217 -- PARAMETERS:
218 -- In: p_org_id Identifier of operating unit
219 --
220 --
221 -- Return: NUMBER
222 --
223 -- DESIGN REFERENCES:
224 -- GTA-System-Options-Form-TD.doc
225 --
226 -- CHANGE HISTORY:
227 --
228 -- 30-APR-2005: qiang.li Created.
229 -- 19-JAN-2006: qiang.li Update columns last_update_date
230 -- last_updated_by and last_update_login,
231 -- when update next_value
232 --===========================================================================
233 FUNCTION Next_Value
234 (p_org_id IN NUMBER
235 )
236 RETURN NUMBER
237 IS
238 PRAGMA AUTONOMOUS_TRANSACTION;
239
240 l_batch_flag VARCHAR2(1);
241 l_next_val NUMBER;
242 l_procedure_name VARCHAR2(30) := 'next_value';
243 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
244 l_proc_level NUMBER := fnd_log.level_procedure;
245
246 CURSOR c_autobatch IS
247 SELECT auto_batch_numbering_flag
248 FROM jmf_gta_system_parameters
249 WHERE org_id = p_org_id;
250
251 CURSOR c_next_val IS
252 SELECT next_value
253 FROM jmf_gta_batch_numbering
254 WHERE org_id = p_org_id
255 FOR UPDATE OF next_value NOWAIT;
256
257 BEGIN
258 --logging for debug
259 IF (l_proc_level >= l_dbg_level)
260 THEN
261 fnd_log.STRING(l_proc_level
262 ,g_module_prefix || l_procedure_name || '.begin'
263 ,'enter function');
264 END IF;
265
266 OPEN c_autobatch;
267 FETCH c_autobatch INTO l_batch_flag;
268 CLOSE c_autobatch;
269
270 IF (l_batch_flag = 'M')
271 OR (l_batch_flag IS NULL)
272 THEN
273 RETURN NULL;
274 END IF;
275
276 OPEN c_next_val;
277 FETCH c_next_val INTO l_next_val;
278
279 IF c_next_val%FOUND
280 THEN
281 UPDATE jmf_gta_batch_numbering
282 SET next_value = l_next_val + 1
283 , last_update_date = SYSDATE
284 , last_updated_by = fnd_global.LOGIN_ID()
285 , last_update_login = fnd_global.LOGIN_ID()
286 WHERE CURRENT OF c_next_val;
287
288 COMMIT;
289 CLOSE c_next_val;
290 RETURN(l_next_val);
291 ELSE
292 CLOSE c_next_val;
293 RETURN(0);
294 END IF;
295
296 --logging for debug
297 IF (l_proc_level >= l_dbg_level)
298 THEN
299 fnd_log.STRING(l_proc_level
300 ,g_module_prefix || l_procedure_name || '.end'
301 ,'end function');
302 END IF;
303 END Next_Value;
304 --==========================================================================
305 -- PROCEDURE NAME:
306 --
307 -- Drop_Seq Public
308 --
309 -- DESCRIPTION:
310 --
311 -- This procedure drop the sequence of a given operating unit
312 --
313 -- PARAMETERS:
314 -- In: p_org_id the operating unit id for the new sequence
315 --
316 -- Out: x_return_status the return value to indicate the status
317 --
318 -- DESIGN REFERENCES:
319 -- GTA-System-Options-Form-TD.doc
320 --
321 -- CHANGE HISTORY:
322 --
323 -- 30-APR-2005: qiang.li Created.
324 --
325 --===========================================================================
326 PROCEDURE Drop_Seq
327 ( p_org_id IN NUMBER
328 , x_return_status OUT NOCOPY VARCHAR2
329 )
330 IS
331 l_count NUMBER;
332 l_procedure_name VARCHAR2(30) := 'next_value';
333 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
334 l_proc_level NUMBER := fnd_log.level_procedure;
335 CURSOR c_chk_seq_exist IS
336 SELECT COUNT(*)
337 FROM jmf_gta_batch_numbering
338 WHERE org_id = p_org_id;
339 BEGIN
340
341 --logging for debug
342 IF (l_proc_level >= l_dbg_level)
343 THEN
344 fnd_log.STRING(l_proc_level
345 ,g_module_prefix || l_procedure_name || '.begin'
346 ,'enter procedure');
347 END IF;
348
349 OPEN c_chk_seq_exist;
350 FETCH c_chk_seq_exist INTO l_count;
351 CLOSE c_chk_seq_exist;
352
353 IF l_count = 1
354 THEN
355 DELETE FROM jmf_gta_batch_numbering
356 WHERE org_id = p_org_id;
357
358 COMMIT;
359 x_return_status := 'S';
360 ELSE
361 x_return_status := 'F';
362 END IF;
363
364 --logging for debug
365 IF (l_proc_level >= l_dbg_level)
366 THEN
367 fnd_log.STRING(l_proc_level
368 ,g_module_prefix || l_procedure_name || '.end'
369 ,'end procedure');
370 END IF;
371 END Drop_Seq;
372
373 --==========================================================================
374 -- FUNCTION NAME:
375 --
376 -- Is_Number Public
377 --
378 -- DESCRIPTION:
379 --
380 -- This function check the input value to see whether it is a number
381 --
382 -- PARAMETERS:
383 -- In: p_value input value to check
384 --
385 --
386 -- Return: NUMBER
387 --
388 -- DESIGN REFERENCES:
389 -- GTA-System-Options-Form-TD.doc
390 --
391 -- CHANGE HISTORY:
392 --
393 -- 30-APR-2005: qiang.li Created.
394 --
395 --===========================================================================
396 FUNCTION Is_Number
397 (p_value IN VARCHAR2
398 )
399 RETURN NUMBER
400 IS
401 l_number_value NUMBER;
402 l_procedure_name VARCHAR2(30) := 'next_value';
403 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
404 l_proc_level NUMBER := fnd_log.level_procedure;
405 BEGIN
406
407 --logging for debug
408 IF (l_proc_level >= l_dbg_level)
409 THEN
410 fnd_log.STRING(l_proc_level
411 ,g_module_prefix || l_procedure_name || '.begin'
412 ,'enter function');
413 END IF;
414
415 SELECT
416 to_number(p_value)
417 INTO
418 l_number_value
419 FROM
420 dual;
421
422 --logging for debug
423 IF (l_proc_level >= l_dbg_level)
424 THEN
425 fnd_log.STRING(l_proc_level
426 ,g_module_prefix || l_procedure_name || '.end'
427 ,'end function');
428 END IF;
429
430 RETURN(l_number_value);
431 EXCEPTION
432 WHEN OTHERS THEN
433 RETURN(-1);
434 END Is_Number;
435
436 --==========================================================================
437 -- FUNCTION NAME:
438 --
439 -- Verify_Next_Batch_Bumber Public
440 --
441 -- DESCRIPTION:
442 --
443 -- This function verify the given next value for a operating unit to
444 -- see whether the next value is bigger than the exist batch number
445 -- PARAMETERS:
446 -- In: p_org_id the operating unit id
447 -- p_next_value the next value to verify
448 -- Return: VARCHAR2
449 --
450 -- DESIGN REFERENCES:
451 -- GTA-System-Options-Form-TD.doc
452 --
453 -- CHANGE HISTORY:
454 --
455 -- 30-APR-2005: qiang.li Created.
456 -- 19-Jun-2006: Qiang.Li Fix bug 5291644
457 -- 27-Jun-2006: Qiang.Li Fix bug 5291644,next batch number can
458 -- not equal to existing batch numbers
459 --===========================================================================
460 FUNCTION Verify_Next_Batch_Number
461 ( p_org_id IN NUMBER
462 , p_next_value IN NUMBER
463 )
464 RETURN VARCHAR2
465 IS
466 l_count NUMBER;
467 l_procedure_name VARCHAR2(30) := 'next_value';
468 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
469 l_proc_level NUMBER := fnd_log.level_procedure;
470
471 -- Fix bug 5291644
472 -- next batch number must large than any existing batch number
473 CURSOR c_gta_batch_number IS
474 SELECT
475 COUNT(*)
476 FROM jmf_gta_trx_headers_all
477 WHERE org_id = p_org_id
478 AND is_number(gta_batch_number) >= p_next_value
479 AND instr(gta_batch_number,'-') <= 0;
480
481 BEGIN
482 --logging for debug
483 IF (l_proc_level >= l_dbg_level)
484 THEN
485 fnd_log.STRING(l_proc_level
486 ,g_module_prefix || l_procedure_name || '.begin'
487 ,'enter function');
488 END IF;
489
490 OPEN c_gta_batch_number;
491 FETCH c_gta_batch_number INTO l_count;
492 CLOSE c_gta_batch_number;
493
494 --logging for debug
495 IF (l_proc_level >= l_dbg_level)
496 THEN
497 fnd_log.STRING(l_proc_level
498 ,g_module_prefix || l_procedure_name || '.end'
499 ,'end function');
500 END IF;
501
502 IF l_count > 0
503 THEN
504 RETURN('F');
505 ELSE
506 RETURN('P');
507 END IF;
508 END Verify_Next_Batch_Number;
509
510 --==========================================================================
511 -- FUNCTION NAME:
512 --
513 -- Is_Exist Public
514 --
515 -- DESCRIPTION:
516 --
517 -- This function is used to check whether the given org_id has a sequence
518 -- in the database
519 -- PARAMETERS:
520 -- In: p_org_id the operating unit id
521 --
522 -- Return: VARCHAR2
523 --
524 -- DESIGN REFERENCES:
525 -- GTA-System-Options-Form-TD.doc
526 --
527 -- CHANGE HISTORY:
528 --
529 -- 30-APR-2005: qiang.li Created.
530 --
531 --===========================================================================
532 FUNCTION Is_Exist
533 (p_org_id IN NUMBER
534 )
535 RETURN VARCHAR2
536 IS
537 l_count NUMBER;
538 l_procedure_name VARCHAR2(30) := 'next_value';
539 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
540 l_proc_level NUMBER := fnd_log.level_procedure;
541 CURSOR c_chk_seq_exist IS
542 SELECT
543 COUNT(*)
544 FROM
545 jmf_gta_batch_numbering
546 WHERE org_id = p_org_id;
547 BEGIN
548 --logging for debug
549 IF (l_proc_level >= l_dbg_level)
550 THEN
551 fnd_log.STRING(l_proc_level
552 ,g_module_prefix || l_procedure_name || '.begin'
553 ,'enter function');
554 END IF;
555
556 OPEN c_chk_seq_exist;
557 FETCH c_chk_seq_exist INTO l_count;
558 CLOSE c_chk_seq_exist;
559
560 --logging for debug
561 IF (l_proc_level >= l_dbg_level)
562 THEN
563 fnd_log.STRING(l_proc_level
564 ,g_module_prefix || l_procedure_name || '.end'
565 ,'end function');
566 END IF;
567
568 IF l_count > 0
569 THEN
570 RETURN('Y');
571 ELSE
572 RETURN('N');
573 END IF;
574 END Is_Exist;
575
576 --==========================================================================
577 -- FUNCTION NAME:
578 --
579 -- Current_Value Public
580 --
581 -- DESCRIPTION:
582 --
583 -- This function is used to get the current value of a sequence
584 -- PARAMETERS:
585 -- In: p_org_id the operating unit id
586 --
587 -- Return: VARCHAR2
588 --
589 -- DESIGN REFERENCES:
590 -- GTA-System-Options-Form-TD.doc
591 --
592 -- CHANGE HISTORY:
593 --
594 -- 30-APR-2005: qiang.li Created.
595 --
596 --===========================================================================
597 FUNCTION Current_Value
598 ( p_org_id IN NUMBER
599 )
600 RETURN NUMBER
601 IS
602 l_number NUMBER := -1;
603 l_procedure_name VARCHAR2(30) := 'next_value';
604 l_dbg_level NUMBER := fnd_log.g_current_runtime_level;
605 l_proc_level NUMBER := fnd_log.level_procedure;
606 CURSOR c_seq IS
607 SELECT
608 next_value
609 FROM
610 jmf_gta_batch_numbering
611 WHERE org_id = p_org_id;
612 BEGIN
613 --logging for debug
614 IF (l_proc_level >= l_dbg_level)
615 THEN
616 fnd_log.STRING(l_proc_level
617 ,g_module_prefix || l_procedure_name || '.begin'
618 ,'enter function');
619 END IF;
620
621 OPEN c_seq;
622 FETCH c_seq INTO l_number;
623 CLOSE c_seq;
624
625 --logging for debug
626 IF (l_proc_level >= l_dbg_level)
627 THEN
628 fnd_log.STRING(l_proc_level
629 ,g_module_prefix || l_procedure_name || '.end'
630 ,'end function');
631 END IF;
632
633 RETURN l_number;
634
635 END Current_Value;
636
637 END JMF_GTA_BATCH_NUMBER_UTIL;