[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_SYSTEM_OPTIONS_PKG
Source
1 PACKAGE BODY IGC_CC_SYSTEM_OPTIONS_PKG AS
2 /*$Header: IGCSYSPB.pls 120.0.12000000.1 2007/10/25 04:56:08 mbremkum noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IGC_CC_SYSTEM_OPTIONS_PKG';
5
6 -- The flag determines whether to print debug information or not.
7 g_debug_flag VARCHAR2(1) := 'N' ;
8
9 -- Variables for ATG Central Logging
10
11 l_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
12 l_state_level number := FND_LOG.LEVEL_STATEMENT;
13 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
14 l_event_level number := FND_LOG.LEVEL_EVENT;
15 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
16 l_error_level number := FND_LOG.LEVEL_ERROR;
17 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
18 l_path VARCHAR2(255) := 'IGC.PLSQL.IGCSYSPB.IGC_CC_SYSTEM_OPTIONS_PKG';
19
20 /*=======================================================================+
21 | PROCEDURE Insert_Row |
22 +=======================================================================*/
23 PROCEDURE Insert_Row
24 (
25 p_api_version IN NUMBER,
26 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
27 p_commit IN VARCHAR2 := FND_API.G_FALSE,
28 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
29 x_return_status OUT NOCOPY VARCHAR2,
30 x_msg_count OUT NOCOPY NUMBER,
31 x_msg_data OUT NOCOPY VARCHAR2,
32 p_row_id IN OUT NOCOPY VARCHAR2,
33 p_org_id NUMBER,
34 p_cc_num_method VARCHAR2,
35 p_cc_num_datatype VARCHAR2,
36 p_cc_next_num NUMBER,
37 p_cc_prefix VARCHAR2,
38 p_default_rate_type VARCHAR2,
39 p_enforce_vendor_hold_flag VARCHAR2,
40 p_last_update_date DATE,
41 p_last_updated_by NUMBER,
42 p_last_update_login NUMBER,
43 p_created_by NUMBER,
44 p_creation_date DATE
45 )
46 IS
47
48 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
49 l_api_version CONSTANT NUMBER := 1.0;
50
51 CURSOR cur_sys_options IS
52 SELECT ROWID
53 FROM IGC_CC_SYSTEM_OPTIONS_ALL
54 WHERE org_id = p_org_id;
55
56 BEGIN
57
58 SAVEPOINT Insert_Row_Pvt;
59
60 IF NOT FND_API.Compatible_API_Call ( l_api_version,
61 p_api_version,
62 l_api_name,
63 G_PKG_NAME )
64 THEN
65 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
66 END IF;
67
68
69 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
70 FND_MSG_PUB.initialize ;
71 END IF;
72
73 x_return_status := FND_API.G_RET_STS_SUCCESS;
74
75 INSERT INTO IGC_CC_SYSTEM_OPTIONS_ALL
76 (
77 org_id,
78 cc_num_method,
79 cc_num_datatype,
80 cc_next_num,
81 cc_prefix,
82 default_rate_type,
83 enforce_vendor_hold_flag,
84 last_update_date,
85 last_updated_by,
86 last_update_login,
87 creation_date,
88 created_by
89 )
90 VALUES
91 (
92 p_org_id,
93 p_cc_num_method,
94 p_cc_num_datatype,
95 p_cc_next_num,
96 p_cc_prefix,
97 p_default_rate_type,
98 p_enforce_vendor_hold_flag,
99 p_last_update_date,
100 p_last_updated_by,
101 p_last_update_login,
102 p_creation_date,
103 p_created_by
104 );
105
106 OPEN cur_sys_options;
107 FETCH cur_sys_options INTO p_row_id;
108 IF (cur_sys_options%NOTFOUND) THEN
109 CLOSE cur_sys_options;
110 RAISE FND_API.G_EXC_ERROR;
111 END IF;
112 CLOSE cur_sys_options;
113
114
115 IF FND_API.To_Boolean ( p_commit ) THEN
116 COMMIT WORK;
117 END iF;
118
119 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
120 p_data => x_msg_data );
121
122 EXCEPTION
123
124 WHEN FND_API.G_EXC_ERROR THEN
125
126 ROLLBACK TO Insert_Row_Pvt ;
127 x_return_status := FND_API.G_RET_STS_ERROR;
128 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
129 p_data => x_msg_data );
130
131 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
132
133 ROLLBACK TO Insert_Row_Pvt ;
134 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
135 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
136 p_data => x_msg_data );
137
138 WHEN OTHERS THEN
139
140 ROLLBACK TO Insert_Row_Pvt ;
141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
142
143 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
144 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
145 l_api_name);
146 END if;
147
148 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
149 p_data => x_msg_data );
150
151 END Insert_Row;
152
153
154 /*==========================================================================+
155 | PROCEDURE Lock_Row |
156 +==========================================================================*/
157 PROCEDURE Lock_Row
158 (
159 p_api_version IN NUMBER,
160 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
161 p_commit IN VARCHAR2 := FND_API.G_FALSE,
162 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
163 x_return_status OUT NOCOPY VARCHAR2,
164 x_msg_count OUT NOCOPY NUMBER,
165 x_msg_data OUT NOCOPY VARCHAR2,
166 p_row_id IN OUT NOCOPY VARCHAR2,
167 p_org_id NUMBER,
168 p_cc_num_method VARCHAR2,
169 p_cc_num_datatype VARCHAR2,
170 p_cc_next_num NUMBER,
171 p_cc_prefix VARCHAR2,
172 p_default_rate_type VARCHAR2,
173 p_enforce_vendor_hold_flag VARCHAR2,
174 p_row_locked OUT NOCOPY VARCHAR2
175 )
176 IS
177
178 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
179 l_api_version CONSTANT NUMBER := 1.0;
180
181 Counter NUMBER;
182
183 CURSOR cur_sys_options IS
184 SELECT *
185 FROM IGC_CC_SYSTEM_OPTIONS_ALL
186 WHERE ROWID = p_row_id
187 FOR UPDATE NOWAIT;
188
189 Recinfo cur_sys_options%ROWTYPE;
190
191 BEGIN
192
193 SAVEPOINT Lock_Row_Pvt;
194
195 IF NOT FND_API.Compatible_API_Call ( l_api_version,
196 p_api_version,
197 l_api_name,
198 G_PKG_NAME )
199 THEN
200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
201 END IF;
202
203
204 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
205 FND_MSG_PUB.initialize;
206 END IF;
207
208 x_return_status := FND_API.G_RET_STS_SUCCESS;
209 p_row_locked := FND_API.G_TRUE;
210
211 OPEN cur_sys_options;
212
213 FETCH cur_sys_options INTO Recinfo;
214 IF (cur_sys_options%NOTFOUND) THEN
215 CLOSE cur_sys_options;
216 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
217 FND_MSG_PUB.Add;
218 RAISE FND_API.G_EXC_ERROR;
219 END IF;
220
221 CLOSE cur_sys_options;
222
223 IF ( ( (Recinfo.org_id = p_org_id)
224 OR ( (Recinfo.org_id IS NULL)
225 AND (p_org_id IS NULL)))
226 AND ( (Recinfo.cc_num_method = p_cc_num_method)
227 OR ( (Recinfo.cc_num_method IS NULL)
228 AND (p_cc_num_method IS NULL)))
229 AND ( (Recinfo.cc_num_datatype = p_cc_num_datatype)
230 OR ( (Recinfo.cc_num_datatype IS NULL)
231 AND (p_cc_num_datatype IS NULL)))
232 AND ( (Recinfo.cc_next_num = p_cc_next_num)
233 OR ( (Recinfo.cc_next_num IS NULL)
234 AND (p_cc_next_num IS NULL)))
235 AND ( (Recinfo.cc_prefix = p_cc_prefix)
236 OR ( (Recinfo.cc_prefix IS NULL)
237 AND (p_cc_prefix IS NULL)))
238 AND ( (Recinfo.default_rate_type = p_default_rate_type)
239 OR ( (Recinfo.default_rate_type IS NULL)
240 AND (p_default_rate_type IS NULL)))
241 AND ( (Recinfo.enforce_vendor_hold_flag = p_enforce_vendor_hold_flag)
242 OR ( (Recinfo.enforce_vendor_hold_flag IS NULL)
243 AND (p_enforce_vendor_hold_flag IS NULL)))
244 ) THEN
245 Null;
246 ELSE
247 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
248 FND_MSG_PUB.Add;
249 RAISE FND_API.G_EXC_ERROR;
250 END IF;
251
252 IF FND_API.To_Boolean ( p_commit ) THEN
253 COMMIT WORK;
254 END iF;
255
256 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
257 p_data => x_msg_data );
258
259 EXCEPTION
260
261 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
262
263 ROLLBACK TO Lock_Row_Pvt;
264 p_row_locked := FND_API.G_FALSE;
265 x_return_status := FND_API.G_RET_STS_ERROR;
266 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
267 p_data => x_msg_data );
268
269 WHEN FND_API.G_EXC_ERROR THEN
270
271 ROLLBACK TO Lock_Row_Pvt;
272 x_return_status := FND_API.G_RET_STS_ERROR;
273 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
274 p_data => x_msg_data );
275
276 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
277
278 ROLLBACK TO Lock_Row_Pvt;
279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
280 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
281 p_data => x_msg_data );
282
283 WHEN OTHERS THEN
284
285 ROLLBACK TO Lock_Row_Pvt ;
286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
287
288 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
289 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
290 l_api_name);
291 END if;
292
293 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
294 p_data => x_msg_data );
295
296 END Lock_Row;
297
298
299 /*==========================================================================+
300 | PROCEDURE Update_Row |
301 +==========================================================================*/
302 PROCEDURE Update_Row
303 (
304 p_api_version IN NUMBER,
305 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
306 p_commit IN VARCHAR2 := FND_API.G_FALSE,
307 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
308 x_return_status OUT NOCOPY VARCHAR2,
309 x_msg_count OUT NOCOPY NUMBER,
310 x_msg_data OUT NOCOPY VARCHAR2,
311 p_row_id IN OUT NOCOPY VARCHAR2,
312 p_org_id NUMBER,
313 p_cc_num_method VARCHAR2,
314 p_cc_num_datatype VARCHAR2,
315 p_cc_next_num NUMBER,
316 p_cc_prefix VARCHAR2,
317 p_default_rate_type VARCHAR2,
318 p_enforce_vendor_hold_flag VARCHAR2,
319 p_last_update_date DATE,
320 p_last_updated_by NUMBER,
321 p_last_update_login NUMBER
322 )
323 IS
324
325 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
326 l_api_version CONSTANT NUMBER := 1.0;
327
328 BEGIN
329
330 SAVEPOINT Update_Row_Pvt;
331
332 IF NOT FND_API.Compatible_API_Call ( l_api_version,
333 p_api_version,
334 l_api_name,
335 G_PKG_NAME )
336 THEN
337 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
338 END IF;
339
340
341 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
342 FND_MSG_PUB.initialize;
343 END IF;
344
345 x_return_status := FND_API.G_RET_STS_SUCCESS;
346
347 UPDATE IGC_CC_SYSTEM_OPTIONS_ALL
348 SET
349 org_id = p_org_id,
350 cc_num_method = p_cc_num_method,
351 cc_num_datatype = p_cc_num_datatype,
352 cc_next_num = p_cc_next_num,
353 cc_prefix = p_cc_prefix,
354 default_rate_type = p_default_rate_type,
355 enforce_vendor_hold_flag = p_enforce_vendor_hold_flag,
356 last_update_date = p_last_update_date,
357 last_updated_by = p_last_updated_by,
358 last_update_login = p_last_update_login
359 WHERE ROWID = p_row_id;
360
361 IF (SQL%NOTFOUND) THEN
362 RAISE NO_DATA_FOUND;
363 END IF;
364
365 IF FND_API.To_Boolean ( p_commit ) THEN
366 COMMIT WORK;
367 END iF;
368
369 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
370 p_data => x_msg_data );
371
372 EXCEPTION
373
374 WHEN FND_API.G_EXC_ERROR THEN
375
376 ROLLBACK TO Update_Row_Pvt ;
377 x_return_status := FND_API.G_RET_STS_ERROR;
378 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
379 p_data => x_msg_data );
380
381 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
382
383 ROLLBACK TO Update_Row_Pvt ;
384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
385 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
386 p_data => x_msg_data );
387
388 WHEN OTHERS THEN
389
390 ROLLBACK TO Update_Row_Pvt ;
391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
392
393 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
394 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
395 l_api_name);
396 END if;
397
398 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
399 p_data => x_msg_data );
400
401 END Update_Row;
402 /* ----------------------------------------------------------------------- */
403
404 /*==========================================================================+
405 | PROCEDURE Delete_Row |
406 +==========================================================================*/
407 PROCEDURE Delete_Row
408 (
409 p_api_version IN NUMBER,
410 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
411 p_commit IN VARCHAR2 := FND_API.G_FALSE,
412 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
413 x_return_status OUT NOCOPY VARCHAR2,
414 x_msg_count OUT NOCOPY NUMBER,
415 x_msg_data OUT NOCOPY VARCHAR2,
416 p_row_id IN VARCHAR2
417 )
418 IS
419
420 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
421 l_api_version CONSTANT NUMBER := 1.0;
422
423 BEGIN
424
425 SAVEPOINT Delete_Row_Pvt;
426
427 IF NOT FND_API.Compatible_API_Call ( l_api_version,
428 p_api_version,
429 l_api_name,
430 G_PKG_NAME )
431 THEN
432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
433 END IF;
434
435 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
436 FND_MSG_PUB.initialize;
437 END IF;
438
439 x_return_status := FND_API.G_RET_STS_SUCCESS;
440
441 -- Deleting the record in igc_cc_system_options_all.
442
443 DELETE FROM IGC_CC_SYSTEM_OPTIONS_ALL
444 WHERE ROWID = p_row_id;
445
446 IF (SQL%NOTFOUND) THEN
447 RAISE NO_DATA_FOUND;
448 END IF;
449
450 IF FND_API.To_Boolean ( p_commit ) THEN
451 COMMIT WORK;
452 END IF;
453
454 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
455 p_data => x_msg_data );
456
457 EXCEPTION
458
459 WHEN FND_API.G_EXC_ERROR THEN
460
461 ROLLBACK TO Delete_Row_Pvt;
462 x_return_status := FND_API.G_RET_STS_ERROR;
463 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
464 p_data => x_msg_data );
465
466 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
467
468 ROLLBACK TO Delete_Row_Pvt;
469 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
470 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
471 p_data => x_msg_data );
472
473 WHEN OTHERS THEN
474
475 ROLLBACK TO Delete_Row_Pvt;
476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
477
478 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
479 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
480 l_api_name);
481 END if;
482
483 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
484 p_data => x_msg_data );
485
486 END Delete_Row;
487 /* ----------------------------------------------------------------------- */
488
489 /*==========================================================================+
490 | PROCEDURE Check_Unique |
491 +==========================================================================*/
492 PROCEDURE Check_Unique
493 (
494 p_api_version IN NUMBER,
495 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
496 p_commit IN VARCHAR2 := FND_API.G_FALSE,
497 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
498 x_return_status OUT NOCOPY VARCHAR2,
499 x_msg_count OUT NOCOPY NUMBER,
500 x_msg_data OUT NOCOPY VARCHAR2,
501 p_row_id IN OUT NOCOPY VARCHAR2,
502 p_org_id NUMBER,
503 p_return_value IN OUT NOCOPY VARCHAR2
504 )
505 IS
506
507 l_api_name CONSTANT VARCHAR2(30) := 'Check_Unique';
508 l_api_version CONSTANT NUMBER := 1.0;
509
510 l_tmp VARCHAR2(1);
511
512 CURSOR c IS
513 SELECT '1'
514 FROM igc_cc_system_options_all
515 WHERE org_id = p_org_id
516 AND (
517 p_row_id IS NULL
518 OR
519 rowid <> p_row_id
520 );
521
522 BEGIN
523
524 SAVEPOINT Check_Unique_Pvt ;
525
526 IF NOT FND_API.Compatible_API_Call ( l_api_version,
527 p_api_version,
528 l_api_name,
529 G_PKG_NAME )
530 THEN
531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
532 END IF;
533
534
535 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
536 FND_MSG_PUB.initialize ;
537 END IF;
538
539 x_return_status := FND_API.G_RET_STS_SUCCESS ;
540
541 -- Checking the igc_cc_number_methods table for uniqueness.
542 OPEN c;
543 FETCH c INTO l_tmp;
544
545 -- p_Return_Value specifies whether unique value exists or not.
546 IF l_tmp IS NULL THEN
547 p_Return_Value := 'FALSE';
548 ELSE
549 p_Return_Value := 'TRUE';
550 END IF;
551
552 CLOSE c;
553
554 IF FND_API.To_Boolean ( p_commit ) THEN
555 COMMIT WORK;
556 END iF;
557
558 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
559 p_data => x_msg_data );
560
561 EXCEPTION
562
563 WHEN FND_API.G_EXC_ERROR THEN
564
565 ROLLBACK TO Check_Unique_Pvt ;
566 x_return_status := FND_API.G_RET_STS_ERROR;
567 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
568 p_data => x_msg_data );
569
570 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
571
572 ROLLBACK TO Check_Unique_Pvt ;
573 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
574 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
575 p_data => x_msg_data );
576
577 WHEN OTHERS THEN
578
579 ROLLBACK TO Check_Unique_Pvt ;
580 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
581
582 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
583 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
584 l_api_name);
585 END if;
586
587 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
588 p_data => x_msg_data );
589
590 END Check_Unique;
591
592
593
594 /*==========================================================================+
595 | PROCEDURE Create_Auto_CC_Num |
596 +==========================================================================*/
597
598 PROCEDURE Create_Auto_CC_Num
599 (
600 p_api_version IN NUMBER,
601 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
602 p_commit IN VARCHAR2 := FND_API.G_FALSE,
603 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
604 x_return_status OUT NOCOPY VARCHAR2,
605 x_msg_count OUT NOCOPY NUMBER,
606 x_msg_data OUT NOCOPY VARCHAR2,
607 p_org_id IN igc_cc_headers.org_id%TYPE,
608 p_sob_id IN igc_cc_headers.set_of_books_id%TYPE,
609 x_cc_num OUT NOCOPY igc_cc_headers.cc_num%TYPE
610 ) IS
611 PRAGMA AUTONOMOUS_TRANSACTION; -- Added for bug 3329666
612
613 l_api_name CONSTANT VARCHAR2(30) := 'Create_Auto_CC_Num';
614 l_api_version CONSTANT NUMBER := 1.0;
615
616 -- mh: define variables
617 x_cc_prefix igc_cc_system_options_all.cc_prefix%TYPE;
618 x_cc_num_exists NUMBER;
619 x_po_num_exists NUMBER;
620 -- sb: x_cc_next_num should be a number
621 -- x_cc_next_num igc_cc_headers.cc_num%TYPE;
622 x_cc_next_num igc_cc_system_options_all.cc_next_num%TYPE;
623 -- sb: end
624
625 -- mh: define cursor to get cc_prefix and cc_num from igc_cc_number_methods
626 CURSOR c_cc_num(l_org_id NUMBER) IS
627 SELECT cc_prefix, cc_next_num
628 FROM igc_cc_system_options_all
629 WHERE org_id = l_org_id
630 FOR UPDATE NOWAIT; -- Added for bug 3329666
631
632 -- sb: define exceptions
633 e_no_number_setup EXCEPTION;
634 e_no_prefix_setup EXCEPTION;
635 -- sb: end
636
637 l_cbc_po_enable VARCHAR2(1);
638 e_row_locked EXCEPTION;
639 PRAGMA EXCEPTION_INIT (e_row_locked, -54);
640
641 BEGIN
642
643 -- SAVEPOINT Create_Auto_CC_Num_Pvt;
644
645 IF NOT FND_API.Compatible_API_Call ( l_api_version,
646 p_api_version,
647 l_api_name,
648 G_PKG_NAME )
649 THEN
650 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
651 END IF;
652
653
654 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
655 FND_MSG_PUB.initialize;
656 END IF;
657
658 x_return_status := FND_API.G_RET_STS_SUCCESS;
659 -- sb: x_cc_num is not a number, so set to null not 0
660 -- x_cc_num := 0;
661 x_cc_num := '';
662 -- sb: end
663 x_cc_num_exists := 0;
664 x_po_num_exists := 0;
665
666 -- ---------------------------------------------------------------
667 -- mh start 1: get cc_prefix and cc_num from numbering table
668 -- loop
669 -- concatenate to get the new cc number
670 -- check if this number already exists in CC or PO
671 -- if yes increase cc_num by 1, check again
672 -- if no, update igc_cc_number_methods.cc_num to cc_num+1
673 -- end loop
674 -- return the new cc number
675 -- ---------------------------------------------------------------
676 BEGIN
677 OPEN c_cc_num(p_org_id);
678 FETCH c_cc_num INTO x_cc_prefix, x_cc_next_num;
679
680 IF (c_cc_num%NOTFOUND) THEN
681 CLOSE c_cc_num;
682 RAISE e_no_number_setup;
683 END IF;
684 EXCEPTION -- Added for bug 3329666
685 WHEN e_row_locked
686 THEN
687 fnd_message.set_name('IGC','IGC_CC_NUM_LOCK');
688 fnd_msg_pub.add;
689 IF( FND_LOG.TEST(FND_LOG.LEVEL_ERROR,
690 'IGC.PLSQL.Igc_Cc_Numbers_Pkg.Create_Auto_CC_Num.Lock1'))
691 THEN
692 if (l_error_level >= l_debug_level) then
693 FND_LOG.MESSAGE(l_error_level,
694 'IGC.PLSQL.Igc_Cc_Numbers_Pkg.Create_Auto_CC_Num.Lock1'
695 , FALSE);
696 end if;
697 END IF;
698 ROLLBACK;
699 fnd_msg_pub.count_and_get(p_count => x_msg_count,
700 p_data => x_msg_data);
701 RETURN;
702 END ;
703
704 -- sb: need to close the cursor
705 IF (c_cc_num%ISOPEN) THEN
706 CLOSE c_cc_num;
707 END IF;
708
709 /* Commented we are no longer using cc_prefix. But left it in
710 here just in case we change our minds again.
711 Bidisha S, 16 Sept 2002
712 -- sb: need to add check to ensure prefix exists
713 IF (x_cc_prefix is null) THEN
714 -- Bidisha S, raise the exception only if cbc po is enabled
715 SELECT Nvl(cbc_po_enable, 'N')
716 INTO l_cbc_po_enable
717 FROM igc_cc_bc_enable
718 WHERE set_of_books_id = p_sob_id;
719
720 IF l_cbc_po_enable = 'Y'
721 THEN
722 RAISE e_no_prefix_setup;
723 END IF;
724 END IF;
725 -- sb: end
726
727 */
728
729 -- keep looping until a number that is unique across both CC and
730 -- PO is generated
731
732 LOOP
733
734 -- create the cc number
735 -- also, need to do a to_char on x_cc_next_num
736
737 IF (x_cc_prefix IS NOT NULL) THEN
738 x_cc_num := x_cc_prefix||to_char(x_cc_next_num);
739 ELSE
740 x_cc_num := x_cc_next_num;
741 END IF;
742
743
744 -- validate number is unique across both CC and PO
745 SELECT count(*)
746 INTO x_cc_num_exists
747 FROM igc_cc_headers
748 WHERE cc_num = x_cc_num;
749
750 SELECT count(*)
751 INTO x_po_num_exists
752 FROM po_headers
753 WHERE segment1 = x_cc_num;
754
755 IF ((x_cc_num_exists > 0) OR (x_po_num_exists > 0)) THEN
756 -- sb: logic is wrong here. It should be
757 -- incrementing x_cc_next_num NOT x_cc_num
758
759 --x_cc_num := x_cc_num + 1;
760 x_cc_next_num := x_cc_next_num + 1;
761 -- sb: end
762
763 ELSE
764 -- ---------------------------------------------------------------
765 -- Update the numbering scheme first for the org_id given
766 -- ---------------------------------------------------------------
767 UPDATE igc_cc_system_options_all
768 SET cc_next_num = x_cc_next_num + 1
769 WHERE org_id = p_org_id;
770 -- ---------------------------------------------------------------
771 -- Make sure that ONLY one row has been updated.
772 -- ---------------------------------------------------------------
773 IF (SQL%ROWCOUNT <> 1) THEN
774
775 ROLLBACK;
776 -- sb: x_cc_num is not a number, so set to null not 0
777 --x_cc_num := 0;
778 x_cc_num := '';
779 -- sb: end
780
781 END IF;
782 -- exit from loop
783 EXIT;
784 END IF;
785 END LOOP;
786
787 -- ---------------------------------------------------------------
788 -- mh end 1
789 -- ---------------------------------------------------------------
790 -- ----------------------------------------------------------------
791 -- If the CC Number was generated successfully then commit if
792 -- the caller has indicated to do so.
793 -- ----------------------------------------------------------------
794
795 COMMIT;
796
797 RETURN;
798
799 EXCEPTION
800
801 -- sb: define the exceptions
802 WHEN e_no_number_setup THEN
803 x_return_status := FND_API.G_RET_STS_ERROR;
804 fnd_message.set_name('IGC','IGC_NO_NUMBERING_SETUP');
805 fnd_msg_pub.add;
806 fnd_msg_pub.count_and_get(p_count => x_msg_count,
807 p_data => x_msg_data);
808 ROLLBACK; -- Added for bug 3329666
809
810 WHEN e_no_prefix_setup THEN
811 x_return_status := FND_API.G_RET_STS_ERROR;
812 fnd_message.set_name('IGC','IGC_CC_PREFIX_REQD');
813 fnd_msg_pub.add;
814 fnd_msg_pub.count_and_get(p_count => x_msg_count,
815 p_data => x_msg_data);
816 -- sb: end
817 ROLLBACK; -- Added for bug 3329666
818
819 WHEN FND_API.G_EXC_ERROR THEN
820
821 x_return_status := FND_API.G_RET_STS_ERROR;
822 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
823 p_data => x_msg_data );
824 ROLLBACK; -- Added for bug 3329666
825
826 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
827
828 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
829 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
830 p_data => x_msg_data );
831
832 ROLLBACK; -- Added for bug 3329666
833
834 WHEN OTHERS THEN
835
836 ROLLBACK ;
837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
838
839 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
840 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
841 l_api_name);
842 END if;
843
844 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
845 p_data => x_msg_data );
846
847 END Create_Auto_CC_Num;
848
849
850 /*==========================================================================+
851 | PROCEDURE Validate_Numeric_CC_Num |
852 +==========================================================================*/
853 PROCEDURE Validate_Numeric_CC_Num
854 (
855 p_api_version IN NUMBER,
856 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
857 p_commit IN VARCHAR2 := FND_API.G_FALSE,
858 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
859 x_return_status OUT NOCOPY VARCHAR2,
860 x_msg_count OUT NOCOPY NUMBER,
861 x_msg_data OUT NOCOPY VARCHAR2,
862 p_cc_num IN igc_cc_headers.cc_num%TYPE
863 )
864 IS
865
866 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Numeric_CC_Num';
867 l_api_version CONSTANT NUMBER := 1.0;
868 l_temp_num NUMBER;
869
870 BEGIN
871
872 SAVEPOINT Validate_Numeric_CC_Num_Pvt;
873
874 IF NOT FND_API.Compatible_API_Call ( l_api_version,
875 p_api_version,
876 l_api_name,
877 G_PKG_NAME )
878 THEN
879 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
880 END IF;
881
882 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
883 FND_MSG_PUB.initialize;
884 END IF;
885
886 x_return_status := FND_API.G_RET_STS_SUCCESS;
887
888 -- --------------------------------------------------------------------
889 -- Create seperate block for exception to be caught seperately
890 -- --------------------------------------------------------------------
891 BEGIN
892 l_temp_num := TO_NUMBER (p_cc_num);
893
894 EXCEPTION
895
896 WHEN OTHERS THEN
897 x_return_status := FND_API.G_RET_STS_ERROR;
898 END;
899
900 IF FND_API.To_Boolean ( p_commit ) THEN
901 COMMIT WORK;
902 END IF;
903
904 RETURN;
905
906 EXCEPTION
907 WHEN OTHERS THEN
908 ROLLBACK TO Validate_Numeric_CC_Num_Pvt;
909 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
910
911 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
912 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
913 l_api_name);
914 END if;
915
916 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
917 p_data => x_msg_data );
918
919 END Validate_Numeric_CC_Num;
920 /* ----------------------------------------------------------------------- */
921
922 END IGC_CC_SYSTEM_OPTIONS_PKG;