[Home] [Help]
PACKAGE BODY: APPS.PSB_GL_BUDGET_SET_PVT
Source
1 PACKAGE BODY PSB_GL_Budget_Set_Pvt AS
2 /* $Header: PSBVGBSB.pls 115.12 2002/11/29 10:16:56 vbellur ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_GL_Budget_Set_Pvt';
5
6 -- The flag determines whether to print debug information or not.
7 g_debug_flag VARCHAR2(1) := 'N' ;
8
9
10 /* ---------------------- Private Routine prototypes -----------------------*/
11
12 PROCEDURE pd
13 (
14 p_message IN VARCHAR2
15 ) ;
16
17 /* ------------------ End Private Routines prototypes ----------------------*/
18
19
20
21 /*=======================================================================+
22 | PROCEDURE Insert_Row |
23 +=======================================================================*/
24 PROCEDURE Insert_Row
25 (
26 p_api_version IN NUMBER,
27 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
28 p_commit IN VARCHAR2 := FND_API.G_FALSE,
29 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
30 p_return_status OUT NOCOPY VARCHAR2,
31 p_msg_count OUT NOCOPY NUMBER,
32 p_msg_data OUT NOCOPY VARCHAR2,
33 --
34 p_row_id IN OUT NOCOPY VARCHAR2,
35 p_gl_budget_set_id IN OUT NOCOPY NUMBER,
36 p_gl_budget_set_name IN VARCHAR2,
37 p_set_of_books_id IN NUMBER,
38 p_last_update_date IN DATE,
39 p_last_updated_by IN NUMBER,
40 p_last_update_login IN NUMBER,
41 p_created_by IN NUMBER,
42 p_creation_date IN DATE ,
43 p_context IN VARCHAR2,
44 p_attribute1 IN VARCHAR2,
45 p_attribute2 IN VARCHAR2,
46 p_attribute3 IN VARCHAR2,
47 p_attribute4 IN VARCHAR2,
48 p_attribute5 IN VARCHAR2,
49 p_attribute6 IN VARCHAR2,
50 p_attribute7 IN VARCHAR2,
51 p_attribute8 IN VARCHAR2,
52 p_attribute9 IN VARCHAR2,
53 p_attribute10 IN VARCHAR2
54 )
55 IS
56 --
57 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
58 l_api_version CONSTANT NUMBER := 1.0;
59 --
60 CURSOR C IS
61 SELECT rowid
62 FROM psb_gl_budget_sets
63 WHERE gl_budget_set_id = p_gl_budget_set_id ;
64
65 CURSOR C2 IS
66 SELECT psb_gl_budget_sets_s.NEXTVAL
67 FROM dual ;
68 --
69 BEGIN
70 --
71 SAVEPOINT Insert_Row_Pvt ;
72 --
73 IF NOT FND_API.Compatible_API_Call ( l_api_version,
74 p_api_version,
75 l_api_name,
76 G_PKG_NAME )
77 THEN
78 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
79 END IF;
80 --
81
82 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
83 FND_MSG_PUB.initialize ;
84 END IF;
85 --
86 p_return_status := FND_API.G_RET_STS_SUCCESS ;
87 --
88
89 IF ( p_gl_budget_set_id IS NULL ) THEN
90 OPEN C2;
91
92 FETCH C2 INTO p_gl_budget_set_id ;
93 CLOSE C2;
94 END IF;
95
96 INSERT INTO psb_gl_budget_sets
97 (
98 gl_budget_set_id ,
99 gl_budget_set_name ,
100 set_of_books_id ,
101 last_update_date ,
102 last_updated_by ,
103 last_update_login ,
104 created_by ,
105 creation_date ,
106 context ,
107 attribute1 ,
108 attribute2 ,
109 attribute3 ,
110 attribute4 ,
111 attribute5 ,
112 attribute6 ,
113 attribute7 ,
114 attribute8 ,
115 attribute9 ,
116 attribute10 )
117 VALUES
118 (
119 p_gl_budget_set_id ,
120 p_gl_budget_set_name ,
121 p_set_of_books_id ,
122 p_last_update_date ,
123 p_last_updated_by ,
124 p_last_update_login ,
125 p_created_by ,
126 p_creation_date ,
127 p_context ,
128 p_attribute1 ,
129 p_attribute2 ,
130 p_attribute3 ,
131 p_attribute4 ,
132 p_attribute5 ,
133 p_attribute6 ,
134 p_attribute7 ,
135 p_attribute8 ,
136 p_attribute9 ,
137 p_attribute10
138 );
139 OPEN C;
140 FETCH C INTO p_row_id;
141 IF (C%NOTFOUND) THEN
142 CLOSE C;
143 RAISE FND_API.G_EXC_ERROR ;
144 END IF;
145 CLOSE C;
146 --
147
148 --
149 IF FND_API.To_Boolean ( p_commit ) THEN
150 COMMIT WORK;
151 END iF;
152 --
153 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
154 p_data => p_msg_data );
155 --
156 EXCEPTION
157 --
158 WHEN FND_API.G_EXC_ERROR THEN
159 --
160 ROLLBACK TO Insert_Row_Pvt ;
161 p_return_status := FND_API.G_RET_STS_ERROR;
162 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
163 p_data => p_msg_data );
164 --
165 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
166 --
167 ROLLBACK TO Insert_Row_Pvt ;
168 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
169 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
170 p_data => p_msg_data );
171 --
172 WHEN OTHERS THEN
173 --
174 ROLLBACK TO Insert_Row_Pvt ;
175 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
176 --
177 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
178 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
179 l_api_name);
180 END if;
181 --
182 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
183 p_data => p_msg_data );
184 --
185 END Insert_Row;
186 /*-------------------------------------------------------------------------*/
187
188
189
190 /*==========================================================================+
191 | PROCEDURE Lock_Row |
192 +==========================================================================*/
193 PROCEDURE Lock_Row
194 (
195 p_api_version IN NUMBER,
196 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
197 p_commit IN VARCHAR2 := FND_API.G_FALSE,
198 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
199 p_return_status OUT NOCOPY VARCHAR2,
200 p_msg_count OUT NOCOPY NUMBER,
201 p_msg_data OUT NOCOPY VARCHAR2,
202 --
203 p_row_id IN VARCHAR2,
204 p_gl_budget_set_id IN NUMBER,
205 p_gl_budget_set_name IN VARCHAR2,
206 p_set_of_books_id IN NUMBER,
207 p_context IN VARCHAR2,
208 p_attribute1 IN VARCHAR2,
209 p_attribute2 IN VARCHAR2,
210 p_attribute3 IN VARCHAR2,
211 p_attribute4 IN VARCHAR2,
212 p_attribute5 IN VARCHAR2,
213 p_attribute6 IN VARCHAR2,
214 p_attribute7 IN VARCHAR2,
215 p_attribute8 IN VARCHAR2,
216 p_attribute9 IN VARCHAR2,
217 p_attribute10 IN VARCHAR2,
218 --
219 p_row_locked OUT NOCOPY VARCHAR2
220 )
221 IS
222 --
223 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
224 l_api_version CONSTANT NUMBER := 1.0;
225 --
226 Counter NUMBER;
227 CURSOR C IS
228 SELECT *
229 FROM psb_gl_budget_sets
230 WHERE rowid = p_row_id
231 FOR UPDATE of gl_budget_set_id NOWAIT;
232 Recinfo C%ROWTYPE;
233 --
234 BEGIN
235 --
236 SAVEPOINT Lock_Row_Pvt ;
237 --
238 IF NOT FND_API.Compatible_API_Call ( l_api_version,
239 p_api_version,
240 l_api_name,
241 G_PKG_NAME )
242 THEN
243 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
244 END IF;
245 --
246
247 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
248 FND_MSG_PUB.initialize ;
249 END IF;
250 --
251 p_return_status := FND_API.G_RET_STS_SUCCESS ;
252 p_row_locked := FND_API.G_TRUE ;
253 --
254 OPEN C;
255 --
256 FETCH C INTO Recinfo;
257 IF (C%NOTFOUND) then
258 CLOSE C;
259 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
260 FND_MSG_PUB.Add;
261 RAISE FND_API.G_EXC_ERROR ;
262 END IF;
263 CLOSE C;
264 IF
265 (
266 (Recinfo.gl_budget_set_id = p_gl_budget_set_id)
267
268 AND ( (Recinfo.gl_budget_set_name = p_gl_budget_set_name)
269 OR ( (Recinfo.gl_budget_set_name IS NULL)
270 AND (p_gl_budget_set_name IS NULL)))
271
272 AND ( (Recinfo.set_of_books_id = p_set_of_books_id)
273 OR ( (Recinfo.set_of_books_id IS NULL)
274 AND (p_set_of_books_id IS NULL)))
275
276 AND ( (Recinfo.context = p_context)
277 OR ( (Recinfo.context IS NULL)
278 AND (p_context IS NULL)))
279
280 AND ( (Recinfo.attribute1 = p_attribute1)
281 OR ( (Recinfo.attribute1 IS NULL)
282 AND (p_attribute1 IS NULL)))
283
284 AND ( (Recinfo.attribute2 = p_attribute2)
285 OR ( (Recinfo.attribute2 IS NULL)
286 AND (p_attribute2 IS NULL)))
287
288 AND ( (Recinfo.attribute3 = p_attribute3)
289 OR ( (Recinfo.attribute3 IS NULL)
290 AND (p_attribute3 IS NULL)))
291
292 AND ( (Recinfo.attribute4 = p_attribute4)
293 OR ( (Recinfo.attribute4 IS NULL)
294 AND (p_attribute4 IS NULL)))
295
296 AND ( (Recinfo.attribute5 = p_attribute5)
297 OR ( (Recinfo.attribute5 IS NULL)
298 AND (p_attribute5 IS NULL)))
299
300 AND ( (Recinfo.attribute6 = p_attribute6)
301 OR ( (Recinfo.attribute6 IS NULL)
302 AND (p_attribute6 IS NULL)))
303
304 AND ( (Recinfo.attribute7 = p_attribute7)
305 OR ( (Recinfo.attribute7 IS NULL)
306 AND (p_attribute7 IS NULL)))
307
308 AND ( (Recinfo.attribute8 = p_attribute8)
309 OR ( (Recinfo.attribute8 IS NULL)
310 AND (p_attribute8 IS NULL)))
311
312 AND ( (Recinfo.attribute9 = p_attribute9)
313 OR ( (Recinfo.attribute9 IS NULL)
314 AND (p_attribute9 IS NULL)))
315
316 AND ( (Recinfo.attribute10 = p_attribute10)
317 OR ( (Recinfo.attribute10 IS NULL)
318 AND (p_attribute10 IS NULL)))
319 )
320 THEN
321 Null;
322 ELSE
323 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
324 FND_MSG_PUB.Add;
325 RAISE FND_API.G_EXC_ERROR ;
326 END IF;
327
328 --
329 IF FND_API.To_Boolean ( p_commit ) THEN
330 COMMIT WORK;
331 END iF;
332 --
333 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
334 p_data => p_msg_data );
335 --
336 EXCEPTION
337 --
338 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
339 --
340 ROLLBACK TO Lock_Row_Pvt ;
341 p_row_locked := FND_API.G_FALSE;
342 p_return_status := FND_API.G_RET_STS_ERROR;
343 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
344 p_data => p_msg_data );
345 --
346 WHEN FND_API.G_EXC_ERROR THEN
347 --
348 ROLLBACK TO Lock_Row_Pvt ;
349 p_return_status := FND_API.G_RET_STS_ERROR;
350 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
351 p_data => p_msg_data );
352 --
353 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
354 --
355 ROLLBACK TO Lock_Row_Pvt ;
356 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
357 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
358 p_data => p_msg_data );
359 --
360 WHEN OTHERS THEN
361 --
362 ROLLBACK TO Lock_Row_Pvt ;
363 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
364 --
365 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
366 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
367 l_api_name);
368 END if;
369 --
370 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
371 p_data => p_msg_data );
372 --
373 END Lock_Row;
374 /* ----------------------------------------------------------------------- */
375
376
377
378 /*==========================================================================+
379 | PROCEDURE Update_Row |
380 +==========================================================================*/
381 PROCEDURE Update_Row
382 (
383 p_api_version IN NUMBER,
384 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
385 p_commit IN VARCHAR2 := FND_API.G_FALSE,
386 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
387 p_return_status OUT NOCOPY VARCHAR2,
388 p_msg_count OUT NOCOPY NUMBER,
389 p_msg_data OUT NOCOPY VARCHAR2,
390 --
391 p_row_id IN VARCHAR2,
392 p_gl_budget_set_name IN VARCHAR2,
393 p_set_of_books_id IN NUMBER,
394 p_last_update_date IN DATE,
395 p_last_updated_by IN NUMBER,
396 p_last_update_login IN NUMBER,
397 p_context IN VARCHAR2,
398 p_attribute1 IN VARCHAR2,
399 p_attribute2 IN VARCHAR2,
400 p_attribute3 IN VARCHAR2,
401 p_attribute4 IN VARCHAR2,
402 p_attribute5 IN VARCHAR2,
403 p_attribute6 IN VARCHAR2,
404 p_attribute7 IN VARCHAR2,
405 p_attribute8 IN VARCHAR2,
406 p_attribute9 IN VARCHAR2,
407 p_attribute10 IN VARCHAR2
408 )
409 IS
410 --
411 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
412 l_api_version CONSTANT NUMBER := 1.0;
413 --
414 BEGIN
415 --
416 SAVEPOINT Update_Row_Pvt ;
417 --
418 IF NOT FND_API.Compatible_API_Call ( l_api_version,
419 p_api_version,
420 l_api_name,
421 G_PKG_NAME )
422 THEN
423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
424 END IF;
425 --
426
427 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
428 FND_MSG_PUB.initialize ;
429 END IF;
430 --
431 p_return_status := FND_API.G_RET_STS_SUCCESS ;
432 --
433
434 UPDATE psb_gl_budget_sets
435 SET gl_budget_set_name = p_gl_budget_set_name ,
436 set_of_books_id = p_set_of_books_id ,
437 last_update_date = p_last_update_date ,
438 last_updated_by = p_last_updated_by ,
439 last_update_login = p_last_update_login ,
440 context = p_Context ,
441 attribute1 = p_Attribute1 ,
442 attribute2 = p_Attribute2 ,
443 attribute3 = p_Attribute3 ,
444 attribute4 = p_Attribute4 ,
445 attribute5 = p_Attribute5 ,
446 attribute6 = p_Attribute6 ,
447 attribute7 = p_Attribute7 ,
448 attribute8 = p_Attribute8 ,
449 attribute9 = p_Attribute9 ,
450 attribute10 = p_Attribute10
451 WHERE rowid = p_row_id;
452
453 IF (SQL%NOTFOUND) THEN
454 RAISE NO_DATA_FOUND ;
455 END IF;
456
457 --
458 IF FND_API.To_Boolean ( p_commit ) THEN
459 COMMIT WORK;
460 END iF;
461 --
462 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
463 p_data => p_msg_data );
464 --
465 EXCEPTION
466 --
467 WHEN FND_API.G_EXC_ERROR THEN
468 --
469 ROLLBACK TO Update_Row_Pvt ;
470 p_return_status := FND_API.G_RET_STS_ERROR;
471 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
472 p_data => p_msg_data );
473 --
474 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
475 --
476 ROLLBACK TO Update_Row_Pvt ;
477 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
478 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
479 p_data => p_msg_data );
480 --
481 WHEN OTHERS THEN
482 --
483 ROLLBACK TO Update_Row_Pvt ;
484 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
485 --
486 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
487 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
488 l_api_name);
489 END if;
490 --
491 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
492 p_data => p_msg_data );
493 --
494 END Update_Row;
495 /* ----------------------------------------------------------------------- */
496
497
498
499 /*==========================================================================+
500 | PROCEDURE Delete_Row |
501 +==========================================================================*/
502 PROCEDURE Delete_Row
503 (
504 p_api_version IN NUMBER,
505 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
506 p_commit IN VARCHAR2 := FND_API.G_FALSE,
507 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
508 p_return_status OUT NOCOPY VARCHAR2,
509 p_msg_count OUT NOCOPY NUMBER,
510 p_msg_data OUT NOCOPY VARCHAR2,
511 --
512 p_row_id IN VARCHAR2
513 )
514 IS
515 --
516 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
517 l_api_version CONSTANT NUMBER := 1.0;
518 --
519 l_return_status VARCHAR2(1) ;
520 l_msg_count NUMBER ;
521 l_msg_data VARCHAR2(2000) ;
522 --
523 l_gl_budget_set_id psb_gl_budget_sets.gl_budget_set_id%TYPE;
524 --
525 BEGIN
526 --
527 SAVEPOINT Delete_Row_Pvt ;
528 --
529 IF NOT FND_API.Compatible_API_Call ( l_api_version,
530 p_api_version,
531 l_api_name,
532 G_PKG_NAME )
533 THEN
534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
535 END IF;
536 --
537
538 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
539 FND_MSG_PUB.initialize ;
540 END IF ;
541 --
542 p_return_status := FND_API.G_RET_STS_SUCCESS ;
543 --
544
545 --
546 -- Deleting dependent detail records from psb_gl_budgets.
547 -- ( To maintain ISOLATED master-detail form relation also. )
548 --
549
550 SELECT gl_budget_set_id INTO l_gl_budget_set_id
551 FROM psb_gl_budget_sets
552 WHERE rowid = p_row_id ;
553
554 --
555 -- Delete all the related GL Budgets and associated set information.
556 --
557 FOR l_budget_rec IN
558 (
559 SELECT ROWID
560 FROM psb_gl_budgets
561 WHERE gl_budget_set_id = l_gl_budget_set_id
562 )
563 LOOP
564 --
565 PSB_GL_Budget_Pvt.Delete_Row
566 (
567 p_api_version => 1.0 ,
568 p_init_msg_list => FND_API.G_FALSE,
569 p_commit => FND_API.G_FALSE,
570 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
571 p_return_status => l_return_status,
572 p_msg_count => l_msg_count,
573 p_msg_data => l_msg_data,
574 --
575 p_row_id => l_budget_rec.rowid
576 );
577 --
578 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
580 END IF ;
581 --
582 END LOOP ;
583 --
584 -- End deleting GL Budgets related information.
585 --
586
587
588 -- Deleting the record in psb_gl_budget_sets.
589 DELETE psb_gl_budget_sets
590 WHERE rowid = p_row_id;
591
592 IF (SQL%NOTFOUND) THEN
593 RAISE NO_DATA_FOUND ;
594 END IF;
595
596 --
597 IF FND_API.To_Boolean ( p_commit ) THEN
598 COMMIT WORK;
599 END iF;
600 --
601 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
602 p_data => p_msg_data );
603
604 EXCEPTION
605 --
606 WHEN FND_API.G_EXC_ERROR THEN
607 --
608 ROLLBACK TO Delete_Row_Pvt ;
609 p_return_status := FND_API.G_RET_STS_ERROR;
610 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
611 p_data => p_msg_data );
612 --
613 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
614 --
615 ROLLBACK TO Delete_Row_Pvt ;
616 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
617 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
618 p_data => p_msg_data );
619 --
620 WHEN OTHERS THEN
621 --
622 ROLLBACK TO Delete_Row_Pvt ;
623 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
624 --
625 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
626 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
627 l_api_name);
628 END if;
629 --
630 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
631 p_data => p_msg_data );
632 --
633 END Delete_Row;
634 /* ----------------------------------------------------------------------- */
635
636
637
638 /*==========================================================================+
639 | PROCEDURE Check_Unique |
640 +==========================================================================*/
641 PROCEDURE Check_Unique
642 (
643 p_api_version IN NUMBER,
644 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
645 p_commit IN VARCHAR2 := FND_API.G_FALSE,
646 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
647 p_return_status OUT NOCOPY VARCHAR2,
648 p_msg_count OUT NOCOPY NUMBER,
649 p_msg_data OUT NOCOPY VARCHAR2,
650 --
651 p_row_id IN VARCHAR2,
652 p_gl_budget_set_name IN VARCHAR2,
653 p_set_of_books_id IN NUMBER,
654 p_return_value IN OUT NOCOPY VARCHAR2
655 )
656 IS
657 --
658 l_api_name CONSTANT VARCHAR2(30) := 'Check_Unique';
659 l_api_version CONSTANT NUMBER := 1.0;
660 --
661 l_tmp VARCHAR2(1);
662 --
663 CURSOR c IS
664 SELECT '1'
665 FROM psb_gl_budget_sets
666 WHERE gl_budget_set_name = p_gl_budget_set_name
667 AND set_of_books_id = p_set_of_books_id
668 AND (
669 p_row_id IS NULL
670 OR
671 rowid <> p_row_id
672 );
673 --
674 BEGIN
675 --
676 SAVEPOINT Check_Unique_Pvt ;
677 --
678 IF NOT FND_API.Compatible_API_Call ( l_api_version,
679 p_api_version,
680 l_api_name,
681 G_PKG_NAME )
682 THEN
683 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
684 END IF;
685 --
686
687 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
688 FND_MSG_PUB.initialize ;
689 END IF;
690 --
691 p_return_status := FND_API.G_RET_STS_SUCCESS ;
692 --
693
694 -- Checking the Psb_set_relations table for references.
695 OPEN c;
696 FETCH c INTO l_tmp;
697
698 -- p_Return_Value specifies whether unique value exists or not.
699 IF l_tmp IS NULL THEN
700 p_Return_Value := 'FALSE';
701 ELSE
702 p_Return_Value := 'TRUE';
703 END IF;
704
705 CLOSE c;
706 --
707 IF FND_API.To_Boolean ( p_commit ) THEN
708 COMMIT WORK;
709 END iF;
710 --
711 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
712 p_data => p_msg_data );
713 --
714 EXCEPTION
715 --
716 WHEN FND_API.G_EXC_ERROR THEN
717 --
718 ROLLBACK TO Check_Unique_Pvt ;
719 p_return_status := FND_API.G_RET_STS_ERROR;
720 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
721 p_data => p_msg_data );
722 --
723 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
724 --
725 ROLLBACK TO Check_Unique_Pvt ;
726 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
727 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
728 p_data => p_msg_data );
729 --
730 WHEN OTHERS THEN
731 --
732 ROLLBACK TO Check_Unique_Pvt ;
733 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
734 --
735 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
736 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
737 l_api_name);
738 END if;
739 --
740 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
741 p_data => p_msg_data );
742 --
743 END Check_Unique;
744 /* ----------------------------------------------------------------------- */
745
746
747
748 /*==========================================================================+
749 | PROCEDURE Validate_Account_Overlap |
750 +==========================================================================*/
751 PROCEDURE Validate_Account_Overlap
752 (
753 p_api_version IN NUMBER,
754 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
755 p_commit IN VARCHAR2 := FND_API.G_FALSE,
756 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
757 p_return_status OUT NOCOPY VARCHAR2,
758 p_msg_count OUT NOCOPY NUMBER,
759 p_msg_data OUT NOCOPY VARCHAR2,
760 --
761 p_gl_budget_set_id IN NUMBER,
762 p_validation_status IN OUT NOCOPY VARCHAR2
763 )
764 IS
765 --
766 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
767 l_api_version CONSTANT NUMBER := 1.0;
768 --
769 l_return_status VARCHAR2(1) ;
770 l_msg_count NUMBER ;
771 l_msg_data VARCHAR2(2000) ;
772 --
773 l_gl_budget_set_id psb_gl_budget_sets.gl_budget_set_id%TYPE;
774 l_start_date DATE ;
775 l_end_date DATE ;
776 --
777 l_first_time_flag VARCHAR2(1) ;
778 BEGIN
779 --
780 SAVEPOINT Validate_Account_Overlap_Pvt ;
781 --
782 IF NOT FND_API.Compatible_API_Call ( l_api_version,
783 p_api_version,
784 l_api_name,
785 G_PKG_NAME )
786 THEN
787 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
788 END IF;
789 --
790
791 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
792 FND_MSG_PUB.initialize ;
793 END IF ;
794 --
795 p_return_status := FND_API.G_RET_STS_SUCCESS ;
796 --
797 p_validation_status := FND_API.G_RET_STS_SUCCESS ;
798 --
799 -- Scan all the GL budgets to check for the overlap one at a time.
800 --
801 FOR l_gl_budget_rec IN
802 (
803 SELECT gl_budget_id ,
804 gl_budget_name ,
805 start_date ,
806 end_date ,
807 dual_posting_type
808 FROM psb_gl_budgets_v
809 WHERE gl_budget_set_id = p_gl_budget_set_id
810 )
811 LOOP
812
813 l_start_date := l_gl_budget_rec.start_date ;
814 l_end_date := l_gl_budget_rec.end_date ;
815
816 -- Set up the flag for budget name printing.
817 l_first_time_flag := 'Y' ;
818
819 pd('Budget id : ' || l_gl_budget_rec.gl_budget_id ) ;
820
821 -- Find all the CCIDs pertaining to the current gl_budget_id.
822 FOR l_ccid_rec IN
823 (
824 SELECT pba.account_position_set_id,
825 pba.code_combination_id
826 FROM psb_set_relations rel ,
827 psb_budget_accounts pba
828 WHERE rel.gl_budget_id = l_gl_budget_rec.gl_budget_id
829 AND pba.account_position_set_id = rel.account_position_set_id
830 )
831 LOOP
832
833 -- Check whether current CCID for the current gl_budget_id belongs to
834 -- any other gl_budget_id for the same period and same dual_posting_type
835 -- Note that if dual_posting_type is NULL, it is equivant to being
836 -- Permanent.
837 -- If yes, we have found an overlap. This is to be
838
839 FOR l_dup_gl_budget_id IN
840 (
841 SELECT gb.gl_budget_name
842 FROM psb_gl_budgets_v gb ,
843 psb_set_relations rel ,
844 psb_budget_accounts pba
845 WHERE gb.gl_budget_set_id = p_gl_budget_set_id
846 AND gb.gl_budget_id <> l_gl_budget_rec.gl_budget_id
847 AND rel.gl_budget_id = gb.gl_budget_id
848 AND pba.account_position_set_id = rel.account_position_set_id
849 AND pba.code_combination_id = l_ccid_rec.code_combination_id
850 AND (
851 ( l_start_date BETWEEN gb.start_date AND gb.end_date )
852 OR
853 ( l_end_date BETWEEN gb.start_date AND gb.end_date )
854 OR
855 (
856 l_start_date < gb.start_date
857 AND
858 l_end_date > gb.end_date
859 )
860 )
861 AND NVL( l_gl_budget_rec.dual_posting_type, 'P' ) =
862 NVL( gb.dual_posting_type, 'P' )
863 )
864 LOOP
865
866 p_validation_status := FND_API.G_RET_STS_ERROR;
867
868 -- Set the budget name being validated on the stack. To be done
869 -- only one.
870 IF l_first_time_flag = 'Y' THEN
871 --
872 l_first_time_flag := 'N' ;
873 --
874 FND_MESSAGE.SET_NAME ('PSB', 'PSB_GL_BUDGET_NAME_FOR_OVERLAP');
875 FND_MESSAGE.SET_TOKEN('BUDGET_NAME' ,
876 l_gl_budget_rec.gl_budget_name);
877 FND_MSG_PUB.Add;
878 END IF;
879
880 -- Setup the error message for the current code_combination_id.
881 FND_MESSAGE.SET_NAME ('PSB', 'PSB_GBS_OVERLAP_ACCOUNTS');
882 FND_MESSAGE.SET_TOKEN('CCID' ,
883 l_ccid_rec.code_combination_id);
884 FND_MESSAGE.SET_TOKEN('ACCOUNT_SET' ,
885 l_ccid_rec.account_position_set_id);
886 FND_MESSAGE.SET_TOKEN('BUDGET_NAME' ,
887 l_dup_gl_budget_id.gl_budget_name);
888 FND_MSG_PUB.Add;
889
890 pd('CCID ' || l_ccid_rec.code_combination_id || ' found ' ||
891 'Budget : ' || l_dup_gl_budget_id.gl_budget_name ) ;
892
893 END LOOP ;
894 --
895
896 END LOOP ; -- End processing all the CCID for the current GL Budget ID.
897 --
898 END LOOP ; -- End processing GL budgets related to p_gl_budget_set_id.
899
900 --
901 IF FND_API.To_Boolean ( p_commit ) THEN
902 COMMIT WORK;
903 END iF;
904 --
905 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
906 p_data => p_msg_data );
907
908 EXCEPTION
909 --
910 WHEN FND_API.G_EXC_ERROR THEN
911 --
912 ROLLBACK TO Validate_Account_Overlap_Pvt ;
913 p_return_status := FND_API.G_RET_STS_ERROR;
914 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
915 p_data => p_msg_data );
916 --
917 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
918 --
919 ROLLBACK TO Validate_Account_Overlap_Pvt ;
920 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
921 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
922 p_data => p_msg_data );
923 --
924 --
925 WHEN OTHERS THEN
926 --
927 ROLLBACK TO Validate_Account_Overlap_Pvt ;
928 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
929 --
930 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
931 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
932 l_api_name);
933 END if;
934 --
935 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
936 p_data => p_msg_data );
937 --
938 END Validate_Account_Overlap ;
939 /* ----------------------------------------------------------------------- */
940
941
942
943 /*===========================================================================+
944 | PROCEDURE Validate_Account_Overlap_CP |
945 +===========================================================================*/
946 --
947 -- This is the execution file for the concurrent program 'Validate Account
948 -- Overlap for GL Budget Set'.
949 --
950 PROCEDURE Validate_Account_Overlap_CP
951 (
952 errbuf OUT NOCOPY VARCHAR2,
953 retcode OUT NOCOPY VARCHAR2,
954 --
955 p_gl_budget_set_id IN NUMBER
956 )
957 IS
958 --
959 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Account_Overlap_CP' ;
960 l_api_version CONSTANT NUMBER := 1.0 ;
961 --
962 l_return_status VARCHAR2(1) ;
963 l_msg_count NUMBER ;
964 l_msg_data VARCHAR2(2000) ;
965 --
966 l_validation_status VARCHAR2(1) ;
967 --
968 BEGIN
969 --
970 PSB_GL_Budget_Set_Pvt.Validate_Account_Overlap
971 (
972 p_api_version => 1.0 ,
973 p_init_msg_list => FND_API.G_TRUE,
974 p_commit => FND_API.G_FALSE,
975 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
976 p_return_status => l_return_status,
977 p_msg_count => l_msg_count,
978 p_msg_data => l_msg_data,
979 --
980 p_gl_budget_set_id => p_gl_budget_set_id,
981 p_validation_status => l_validation_status
982 );
983 --
984 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
985 RAISE FND_API.G_EXC_ERROR;
986 END IF;
987 --
988
989 --
990 -- Check whether the API performed the overlap successfully or not. If not
991 -- we will fail the concurrent program so that the user can fix it.
992 --
993 IF l_validation_status <> FND_API.G_RET_STS_SUCCESS THEN
994
995 -- Print error on the OUTPUT file.
996 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.OUTPUT ,
997 p_print_header => FND_API.G_TRUE ) ;
998 --
999 retcode := 2 ;
1000 --
1001 ELSE
1002 --
1003 retcode := 0 ;
1004 --
1005 END IF;
1006 --
1007 COMMIT WORK;
1008 --
1009 EXCEPTION
1010 --
1011 WHEN FND_API.G_EXC_ERROR THEN
1012 --
1013 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1014 p_print_header => FND_API.G_TRUE ) ;
1015 retcode := 2 ;
1016 --
1017 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1018 --
1019 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1020 p_print_header => FND_API.G_TRUE ) ;
1021 retcode := 2 ;
1022 --
1023 WHEN OTHERS THEN
1024 --
1025 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1026 --
1027 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
1028 l_api_name ) ;
1029 END IF ;
1030 --
1031 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1032 p_print_header => FND_API.G_TRUE ) ;
1033 retcode := 2 ;
1034 --
1035 END Validate_Account_Overlap_CP ;
1036 /*---------------------------------------------------------------------------*/
1037
1038 /* Bug No 2564791 Start */
1039
1040 PROCEDURE Check_References
1041 (
1042 p_api_version IN NUMBER,
1043 p_init_msg_list IN VARCHAR2,
1044 p_commit IN VARCHAR2,
1045 p_validation_level IN NUMBER,
1046 p_return_status OUT NOCOPY VARCHAR2,
1047 p_msg_count OUT NOCOPY NUMBER,
1048 p_msg_data OUT NOCOPY VARCHAR2,
1049 --
1050 p_gl_budget_set_id IN NUMBER
1051 )
1052 IS
1053 --
1054 l_api_name CONSTANT VARCHAR2(30) := 'Check_References';
1055 l_api_version CONSTANT NUMBER := 1.0;
1056 --
1057 l_return_status VARCHAR2(1);
1058 --
1059 CURSOR l_check_references_br_csr IS
1060 SELECT 1
1061 FROM dual where exists(
1062 SELECT 1 FROM PSB_BUDGET_REVISIONS
1063 WHERE gl_budget_set_id = p_gl_budget_set_id);
1064
1065 CURSOR l_check_references_ws_csr IS
1066 SELECT 1
1067 FROM dual where exists(
1068 SELECT 1 FROM PSB_WORKSHEETS
1069 WHERE gl_budget_set_id = p_gl_budget_set_id);
1070 BEGIN
1071
1072 SAVEPOINT Check_References_Pvt ;
1073 --
1074 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1075 p_api_version,
1076 l_api_name,
1077 G_PKG_NAME )
1078 THEN
1079 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1080 END IF;
1081 --
1082
1083 IF ( p_init_msg_list='T') THEN
1084 FND_MSG_PUB.initialize ;
1085 END IF;
1086 --
1087 l_return_status := FND_API.G_RET_STS_SUCCESS ;
1088 --
1089
1090 -- Start Checking References
1091
1092 --
1093 for l_check_references_br_csr_rec in l_check_references_br_csr loop
1094 l_return_status:='T';
1095 END LOOP;
1096 --
1097 --
1098 for l_check_references_ws_csr_rec in l_check_references_ws_csr loop
1099 l_return_status:='T';
1100 END LOOP;
1101
1102 -- End Checking References
1103 IF ( p_commit='T' ) THEN
1104 COMMIT WORK;
1105 END IF;
1106 --
1107 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1108 p_data => p_msg_data );
1109 p_return_status:=l_return_status;
1110 --
1111 EXCEPTION
1112 --
1113 WHEN FND_API.G_EXC_ERROR THEN
1114 --
1115 ROLLBACK TO Check_References_Pvt ;
1116 p_return_status := FND_API.G_RET_STS_ERROR;
1117 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1118 p_data => p_msg_data );
1119 --
1120 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1121 --
1122 ROLLBACK TO Check_References_Pvt ;
1123 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1124 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1125 p_data => p_msg_data );
1126 --
1127 --
1128 WHEN OTHERS THEN
1129 --
1130 ROLLBACK TO Check_References_Pvt ;
1131 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1132 --
1133 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1134 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1135 l_api_name);
1136 END IF;
1137 --
1138 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1139 p_data => p_msg_data );
1140 --
1141 END Check_References;
1142
1143 /* Bug No. 2564791 End */
1144
1145
1146 /*===========================================================================+
1147 | PROCEDURE pd (Private) |
1148 +===========================================================================*/
1149 --
1150 -- Private procedure to print debug info. The name is tried to keep as
1151 -- short as possible for better documentaion.
1152 --
1153 PROCEDURE pd
1154 (
1155 p_message IN VARCHAR2
1156 )
1157 IS
1158 --
1159 BEGIN
1160
1161 IF g_debug_flag = 'Y' THEN
1162 NULL;
1163 -- dbms_output.put_line(p_message) ;
1164 END IF;
1165
1166 END pd ;
1167 /*---------------------------------------------------------------------------*/
1168
1169
1170 END PSB_GL_Budget_Set_Pvt;