[Home] [Help]
PACKAGE BODY: APPS.PSB_COMMITMENTS_PVT
Source
1 PACKAGE BODY PSB_COMMITMENTS_PVT AS
2 /* $Header: PSBVWCLB.pls 120.2 2005/07/13 11:30:35 shtripat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_COMMITMENTS_PVT';
5 g_dbug VARCHAR2(2000);
6
7 TYPE TokNameArray IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
8
9 -- TokValArray contains values for all tokens
10
11 TYPE TokValArray IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
12 -- Number of Message Tokens
13
14 no_msg_tokens NUMBER := 0;
15
16 -- Message Token Name
17
18 msg_tok_names TokNameArray;
19
20 -- Message Token Value
21
22 msg_tok_val TokValArray;
23
24 /*==========================================================================+
25 | Private Procedures |
26 +==========================================================================*/
27 PROCEDURE message_token
28 ( tokname IN VARCHAR2,
29 tokval IN VARCHAR2
30 );
31
32 PROCEDURE add_message
33 ( appname IN VARCHAR2,
34 msgname IN VARCHAR2
35 );
36
37 /*==========================================================================+
38 | PROCEDURE Create_Commitment_Line_Items |
39 +==========================================================================*/
40
41 PROCEDURE Create_Commitment_Line_Items
42 ( p_api_version IN NUMBER,
43 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
44 p_commit IN VARCHAR2 := FND_API.G_FALSE,
45 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
46 p_return_status OUT NOCOPY VARCHAR2,
47 p_msg_count OUT NOCOPY NUMBER,
48 p_msg_data OUT NOCOPY VARCHAR2,
49 p_worksheet_id IN NUMBER
50 ) IS
51
52 l_api_name CONSTANT VARCHAR2(30) := 'Create_Commitment_Line_Items';
53 l_api_version CONSTANT NUMBER := 1.0;
54 l_plsql_block VARCHAR2(200);
55 l_init_msg_list VARCHAR2(30) := FND_API.G_FALSE;
56 l_commit VARCHAR2(30) := FND_API.G_FALSE;
57 l_validation_level VARCHAR2(30) := FND_API.G_VALID_LEVEL_FULL;
58 l_return_status VARCHAR2(1);
59 l_msg_count NUMBER;
60 l_msg_data VARCHAR2(2000);
61 l_set_of_books_id NUMBER;
62 l_cbc_enabled BOOLEAN;
63
64 CURSOR C_Root_Set_Of_Books IS
65 SELECT nvl(pbg.root_set_of_books_id, pbg.set_of_books_id)
66 FROM psb_budget_groups_v pbg,
67 psb_worksheets pw
68 WHERE pbg.budget_group_id = pw.budget_group_id
69 AND pw.worksheet_id = p_worksheet_id;
70
71 BEGIN
72
73 -- Standard Start of API savepoint
74
75 SAVEPOINT Create_Commit_Line_Items_Pvt;
76
77 -- Standard call to check for call compatibility.
78
79 IF NOT FND_API.Compatible_API_Call (l_api_version,
80 p_api_version,
81 l_api_name,
82 G_PKG_NAME)
83 THEN
84 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
85 END IF;
86
87 -- Initialize message list if p_init_msg_list is set to TRUE.
88
89 IF FND_API.to_Boolean (p_init_msg_list) THEN
90 FND_MSG_PUB.initialize;
91 END IF;
92
93 OPEN C_Root_Set_Of_Books;
94 FETCH C_Root_Set_Of_Books INTO l_set_of_books_id;
95 CLOSE C_Root_Set_Of_Books;
96
97 --Determine if CBC is enabled
98
99 l_cbc_enabled := IS_CBC_Enabled(l_set_of_books_id);
100
101 --Dynamic sql call to the IGC package API to determine if CBC is
102
103 IF l_cbc_enabled THEN
104 BEGIN
105
106 l_plsql_block :='BEGIN IGC_PSB_Commitments_Pvt.Create_Commitment_Line_Items (:l_api_version, :l_init_msg_list, :l_commit, :l_validation_level, :l_return_status, :l_msg_count, :l_msg_data, :l_worksheet_id); END;';
107
108 EXECUTE IMMEDIATE l_plsql_block USING IN l_api_version, IN l_init_msg_list,
109 IN l_commit, IN l_validation_level,
110 OUT l_return_status, OUT l_msg_count,
111 OUT l_msg_data, IN p_worksheet_id;
112
113 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
114 RAISE FND_API.G_EXC_ERROR;
115 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
117 END IF;
118
119 END;
120 END IF;
121
122 -- Initialize API return status to success
123
124 p_return_status := FND_API.G_RET_STS_SUCCESS;
125
126 -- Standard check of p_commit.
127
128 IF FND_API.to_Boolean (p_commit) THEN
129 COMMIT WORK;
130 END IF;
131
132 -- Standard call to get message count and if count is 1, get message info.
133
134 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
135 p_data => p_msg_data);
136
137 EXCEPTION
138
139 WHEN FND_API.G_EXC_ERROR THEN
140 ROLLBACK to Create_Commit_Line_Items_Pvt;
141 p_return_status := FND_API.G_RET_STS_ERROR;
142
143 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
144 p_data => p_msg_data);
145
146 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
147 ROLLBACK to Create_Commit_Line_Items_Pvt;
148 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
149
150 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
151 p_data => p_msg_data);
152
153 WHEN OTHERS THEN
154 ROLLBACK to Create_Commit_Line_Items_Pvt;
155 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
156
157 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
158 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
159 l_api_name);
160 END IF;
161
162 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
163 p_data => p_msg_data);
164
165 END Create_Commitment_Line_Items;
166
167 /*==========================================================================+
168 | PROCEDURE Post_Commitment_Worksheet |
169 +==========================================================================*/
170
171 PROCEDURE Post_Commitment_Worksheet
172 ( p_api_version IN NUMBER,
173 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
174 p_commit IN VARCHAR2 := FND_API.G_FALSE,
175 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
176 p_return_status OUT NOCOPY VARCHAR2,
177 p_msg_count OUT NOCOPY NUMBER,
178 p_msg_data OUT NOCOPY VARCHAR2,
179 p_worksheet_id IN NUMBER
180 ) IS
181
182 l_api_name CONSTANT VARCHAR2(30) := 'Post_Commitment_Worksheet';
183 l_api_version CONSTANT NUMBER := 1.0;
184 l_plsql_block VARCHAR2(200);
185 l_init_msg_list VARCHAR2(30) := FND_API.G_FALSE;
186 l_commit VARCHAR2(30) := FND_API.G_FALSE;
187 l_validation_level VARCHAR2(30) := FND_API.G_VALID_LEVEL_FULL;
188 l_return_status VARCHAR2(1);
189 l_msg_count NUMBER;
190 l_msg_data VARCHAR2(2000);
191 l_set_of_books_id NUMBER;
192 l_cbc_enabled BOOLEAN;
193
194 CURSOR C_Root_Set_Of_Books IS
195 SELECT nvl(pbg.root_set_of_books_id, pbg.set_of_books_id)
196 FROM psb_budget_groups_v pbg,
197 psb_worksheets pw
198 WHERE pbg.budget_group_id = pw.budget_group_id
199 AND pw.worksheet_id = p_worksheet_id;
200
201 BEGIN
202
203 -- Standard Start of API savepoint
204
205 SAVEPOINT Post_Commitment_Worksheet_Pvt;
206
207
208 -- Standard call to check for call compatibility.
209
210 IF NOT FND_API.Compatible_API_Call (l_api_version,
211 p_api_version,
212 l_api_name,
213 G_PKG_NAME)
214 THEN
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 END IF;
217
218 -- Initialize message list if p_init_msg_list is set to TRUE.
219
220 IF FND_API.to_Boolean (p_init_msg_list) THEN
221 FND_MSG_PUB.initialize;
222 END IF;
223
224 OPEN C_Root_Set_Of_Books;
225 FETCH C_Root_Set_Of_Books INTO l_set_of_books_id;
226 CLOSE C_Root_Set_Of_Books;
227
228 --Determine if CBC is enabled
229
230 l_cbc_enabled := IS_CBC_Enabled(l_set_of_books_id);
231
232 --Dynamic sql call to the IGC package API to determine if CBC is
233
234 IF l_cbc_enabled THEN
235 BEGIN
236
237 l_plsql_block := 'BEGIN IGC_PSB_Commitments_Pvt.Post_Commitment_Worksheet (:l_api_version, :l_init_msg_list, :l_commit, :l_validation_level, :l_return_status, :l_msg_count, :l_msg_data, :l_worksheet_id); END;';
238
239 EXECUTE IMMEDIATE l_plsql_block USING IN l_api_version, IN l_init_msg_list,
240 IN l_commit, IN l_validation_level,
241 OUT l_return_status, OUT l_msg_count,
242 OUT l_msg_data, IN p_worksheet_id;
243
244 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
245 RAISE FND_API.G_EXC_ERROR;
246 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
248 END IF;
249 --
250
251 END;
252 END IF;
253
254 -- Initialize API return status to success
255
256 p_return_status := FND_API.G_RET_STS_SUCCESS;
257
258 -- Standard check of p_commit.
259
260 IF FND_API.to_Boolean (p_commit) THEN
261 COMMIT WORK;
262 END IF;
263
264 -- Standard call to get message count and if count is 1, get message info.
265
266 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
267 p_data => p_msg_data);
268
269 EXCEPTION
270
271 WHEN FND_API.G_EXC_ERROR THEN
272 ROLLBACK to Post_Commitment_Worksheet_Pvt;
273 p_return_status := FND_API.G_RET_STS_ERROR;
274
275 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
276 p_data => p_msg_data);
277
278 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
279 ROLLBACK to Post_Commitment_Worksheet_Pvt;
280 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
281
282 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
283 p_data => p_msg_data);
284
285 WHEN OTHERS THEN
286 ROLLBACK to Post_Commitment_Worksheet_Pvt;
287 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
288
289 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
290 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
291 l_api_name);
292 END IF;
293
294 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
295 p_data => p_msg_data);
296
297 END Post_Commitment_Worksheet;
298
299 /*==========================================================================+
300 | PROCEDURE Create_Commitment_Revisions |
301 +==========================================================================*/
302
303 PROCEDURE Create_Commitment_Revisions
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 p_return_status OUT NOCOPY VARCHAR2,
309 p_msg_count OUT NOCOPY NUMBER,
310 p_msg_data OUT NOCOPY VARCHAR2,
311 p_budget_revision_id IN NUMBER
312 ) IS
313
314 l_api_name CONSTANT VARCHAR2(30) := 'Create_Commitment_Revisions';
315 l_api_version CONSTANT NUMBER := 1.0;
316 l_plsql_block VARCHAR2(200);
317 l_init_msg_list VARCHAR2(30) := FND_API.G_FALSE;
318 l_commit VARCHAR2(30) := FND_API.G_FALSE;
319 l_validation_level VARCHAR2(30) := FND_API.G_VALID_LEVEL_FULL;
320 l_return_status VARCHAR2(1);
321 l_msg_count NUMBER;
322 l_msg_data VARCHAR2(2000);
323 l_set_of_books_id NUMBER;
324 l_cbc_enabled BOOLEAN;
325
326 CURSOR C_Root_Set_Of_Books IS
327 SELECT nvl(pbg.root_set_of_books_id, pbg.set_of_books_id)
328 FROM psb_budget_groups_v pbg,
329 psb_budget_revisions pbr
330 WHERE pbg.budget_group_id = pbr.budget_group_id
331 AND pbr.budget_revision_id = p_budget_revision_id;
332
333 BEGIN
334
335 -- Standard Start of API savepoint
336
337 SAVEPOINT Create_Revisions_Pvt;
338
339
340 -- Standard call to check for call compatibility.
341
342 IF not FND_API.Compatible_API_Call (l_api_version,
343 p_api_version,
344 l_api_name,
345 G_PKG_NAME)
346 THEN
347 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
348 END IF;
349
350 -- Initialize message list if p_init_msg_list is set to TRUE.
351
352 IF FND_API.to_Boolean (p_init_msg_list) THEN
353 FND_MSG_PUB.initialize;
354 END IF;
355
356 OPEN C_Root_Set_Of_Books;
357 FETCH C_Root_Set_Of_Books INTO l_set_of_books_id;
358 CLOSE C_Root_Set_Of_Books;
359
360 --Determine if CBC is enabled
361
362 l_cbc_enabled := IS_CBC_Enabled(l_set_of_books_id);
363
364 --Dynamic sql call to the IGC package API to determine if CBC is
365 IF l_cbc_enabled THEN
366 BEGIN
367
368 l_plsql_block := 'BEGIN IGC_PSB_Commitments_Pvt.Create_Commitment_Revisions (:l_api_version, :l_init_msg_list, :l_commit, :l_validation_level, :l_return_status, :l_msg_count, :l_msg_data, :l_budget_revision_id); END;';
369
370 EXECUTE IMMEDIATE l_plsql_block USING IN l_api_version, IN l_init_msg_list,
371 IN l_commit, IN l_validation_level,
372 OUT l_return_status, OUT l_msg_count,
373 OUT l_msg_data, IN p_budget_revision_id;
374
375 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
376 RAISE FND_API.G_EXC_ERROR;
377 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
378 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
379 END IF;
380 --
381 END;
382 END IF;
383
384 -- Initialize API return status to success
385
386 p_return_status := FND_API.G_RET_STS_SUCCESS;
387
388 -- Standard check of p_commit.
389
390 IF FND_API.to_Boolean (p_commit) THEN
391 COMMIT WORK;
392 END IF;
393
394 -- Standard call to get message count and if count is 1, get message info.
395
396 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
397 p_data => p_msg_data);
398
399
400 EXCEPTION
401
402 WHEN FND_API.G_EXC_ERROR THEN
403 ROLLBACK to Create_Revisions_Pvt;
404 p_return_status := FND_API.G_RET_STS_ERROR;
405
406 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
407 p_data => p_msg_data);
408
409 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
410 ROLLBACK to Create_Revisions_Pvt;
411 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
412
413 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
414 p_data => p_msg_data);
415
416 WHEN OTHERS THEN
417 ROLLBACK to Create_Revisions_Pvt;
418 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
419
420 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
421
422 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
423 l_api_name);
424 END IF;
425
426 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
427 p_data => p_msg_data);
428 END Create_Commitment_Revisions;
429
430 /*==========================================================================+
431 | PROCEDURE Post_Commitment_Revisions |
432 +==========================================================================*/
433
434 PROCEDURE Post_Commitment_Revisions
438 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
435 ( p_api_version IN NUMBER,
436 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
437 p_commit IN VARCHAR2 := FND_API.G_FALSE,
439 p_return_status OUT NOCOPY VARCHAR2,
440 p_msg_count OUT NOCOPY NUMBER,
441 p_msg_data OUT NOCOPY VARCHAR2,
442 p_budget_revision_id IN NUMBER
443 ) IS
444
445 l_api_name CONSTANT VARCHAR2(30) := 'Post_Commitment_Revisions';
446 l_api_version CONSTANT NUMBER := 1.0;
447 l_plsql_block VARCHAR2(200);
448 l_init_msg_list VARCHAR2(30) := FND_API.G_FALSE;
449 l_commit VARCHAR2(30) := FND_API.G_FALSE;
450 l_validation_level VARCHAR2(30) := FND_API.G_VALID_LEVEL_FULL;
451 l_return_status VARCHAR2(1);
452 l_msg_count NUMBER;
453 l_msg_data VARCHAR2(2000);
454 l_set_of_books_id NUMBER;
455 l_cbc_enabled BOOLEAN;
456
457 CURSOR C_Root_Set_Of_Books IS
458 SELECT nvl(pbg.root_set_of_books_id, pbg.set_of_books_id)
459 FROM psb_budget_groups_v pbg,
460 psb_budget_revisions pbr
461 WHERE pbg.budget_group_id = pbr.budget_group_id
462 AND pbr.budget_revision_id = p_budget_revision_id;
463
464 BEGIN
465
466 -- Standard Start of API savepoint
467
468 SAVEPOINT Post_Commitment_Revisions_Pvt;
469
470
471 -- Standard call to check for call compatibility.
472
473 IF not FND_API.Compatible_API_Call (l_api_version,
474 p_api_version,
475 l_api_name,
476 G_PKG_NAME)
477 THEN
478 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
479 END IF;
480
481 -- Initialize message list if p_init_msg_list is set to TRUE.
482
483 IF FND_API.to_Boolean (p_init_msg_list) THEN
484 FND_MSG_PUB.initialize;
485 END IF;
486
487 OPEN C_Root_Set_Of_Books;
488 FETCH C_Root_Set_Of_Books INTO l_set_of_books_id;
489 CLOSE C_Root_Set_Of_Books;
490
491 --Determine if CBC is enabled
492
493 l_cbc_enabled := IS_CBC_Enabled(l_set_of_books_id);
494
495 --Dynamic sql call to the IGC package API to determine if CBC is
496 IF l_cbc_enabled THEN
497 BEGIN
498
499 l_plsql_block := 'BEGIN IGC_PSB_Commitments_Pvt.Post_Commitment_Revisions (:l_api_version, :l_init_msg_list, :l_commit, :l_validation_level, :l_return_status, :l_msg_count, :l_msg_data, :l_budget_revision_id); END;';
500
501 EXECUTE IMMEDIATE l_plsql_block USING IN l_api_version, IN l_init_msg_list,
502 IN l_commit, IN l_validation_level,
503 OUT l_return_status, OUT l_msg_count,
504 OUT l_msg_data, IN p_budget_revision_id;
505
506 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
507 RAISE FND_API.G_EXC_ERROR;
508 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
510 END IF;
511
512 END;
513 END IF;
514
515 -- Initialize API return status to success
516
517 p_return_status := FND_API.G_RET_STS_SUCCESS;
518
519 -- Standard check of p_commit.
520
521 IF FND_API.to_Boolean (p_commit) THEN
522 COMMIT WORK;
523 END IF;
524
525 -- Standard call to get message count and if count is 1, get message info.
526
527 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
528 p_data => p_msg_data);
529
530
531 EXCEPTION
532
533 WHEN FND_API.G_EXC_ERROR THEN
534 ROLLBACK to Post_Commitment_Revisions_Pvt;
535 p_return_status := FND_API.G_RET_STS_ERROR;
536
537 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
538 p_data => p_msg_data);
539
540 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
541 ROLLBACK to Post_Commitment_Revisions_Pvt;
542 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
543
544 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
545 p_data => p_msg_data);
546
547 WHEN OTHERS THEN
548 ROLLBACK to Post_Commitment_Revisions_Pvt;
549 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
550
551 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
552
553 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
554 l_api_name);
555 END IF;
556
557 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
558 p_data => p_msg_data);
559
560 END Post_Commitment_Revisions;
561
562 /*==========================================================================+
563 | PROCEDURE Commitment_Funds_Check |
564 +==========================================================================*/
565
566 PROCEDURE Commitment_Funds_Check
567 ( p_api_version IN NUMBER,
568 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
569 p_commit IN VARCHAR2 := FND_API.G_FALSE,
570 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
571 p_return_status OUT NOCOPY VARCHAR2,
572 p_msg_count OUT NOCOPY NUMBER,
573 p_msg_data OUT NOCOPY VARCHAR2,
574 p_budget_revision_id IN NUMBER
575 ) IS
579 l_plsql_block VARCHAR2(200);
576
577 l_api_name CONSTANT VARCHAR2(30) := 'Commitment_Funds_Check';
578 l_api_version CONSTANT NUMBER := 1.0;
580 l_init_msg_list VARCHAR2(30) := FND_API.G_FALSE;
581 l_commit VARCHAR2(30) := FND_API.G_FALSE;
582 l_validation_level VARCHAR2(30) := FND_API.G_VALID_LEVEL_FULL;
583 l_return_status VARCHAR2(1);
584 l_msg_count NUMBER;
585 l_msg_data VARCHAR2(2000);
586 l_set_of_books_id NUMBER;
587 l_cbc_enabled BOOLEAN;
588
589 CURSOR C_Root_Set_Of_Books IS
590 SELECT nvl(pbg.root_set_of_books_id, pbg.set_of_books_id)
591 FROM psb_budget_groups_v pbg,
592 psb_budget_revisions pbr
593 WHERE pbg.budget_group_id = pbr.budget_group_id
594 AND pbr.budget_revision_id = p_budget_revision_id;
595
596 BEGIN
597
598 -- Standard Start of API savepoint
599
600 SAVEPOINT Commitment_Funds_Check_Pvt;
601
602
603 -- Standard call to check for call compatibility.
604
605 IF not FND_API.Compatible_API_Call (l_api_version,
606 p_api_version,
607 l_api_name,
608 G_PKG_NAME)
609 THEN
610 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
611 END IF;
612
613 -- Initialize message list if p_init_msg_list is set to TRUE.
614
615 IF FND_API.to_Boolean (p_init_msg_list) THEN
616 FND_MSG_PUB.initialize;
617 END IF;
618
619 OPEN C_Root_Set_Of_Books;
620 FETCH C_Root_Set_Of_Books INTO l_set_of_books_id;
621 CLOSE C_Root_Set_Of_Books;
622
623 --Determine if CBC is enabled
624
625 l_cbc_enabled := IS_CBC_Enabled(l_set_of_books_id);
626
627 --Dynamic sql call to the IGC package API to determine if CBC is
628 IF l_cbc_enabled THEN
629 BEGIN
630
631 l_plsql_block := 'BEGIN IGC_PSB_Commitments_Pvt.Commitment_Funds_Check (:l_api_version, :l_init_msg_list, :l_commit, :l_validation_level, :l_return_status, :l_msg_count, :l_msg_data, :l_budget_revision_id); END;';
632
633 EXECUTE IMMEDIATE l_plsql_block USING IN l_api_version, IN l_init_msg_list,
634 IN l_commit, IN l_validation_level,
635 OUT l_return_status, OUT l_msg_count,
636 OUT l_msg_data, IN p_budget_revision_id;
637
638 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
639 RAISE FND_API.G_EXC_ERROR;
640 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
642 END IF;
643 --
644 END;
645 END IF;
646
647 -- Initialize API return status to success
648
649 p_return_status := FND_API.G_RET_STS_SUCCESS;
650
651 -- Standard check of p_commit.
652
653 IF FND_API.to_Boolean (p_commit) THEN
654 COMMIT WORK;
655 END IF;
656
657 -- Standard call to get message count and if count is 1, get message info.
658
659 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
660 p_data => p_msg_data);
661
662
663 EXCEPTION
664
665 WHEN FND_API.G_EXC_ERROR THEN
666 ROLLBACK to Commitment_Funds_Check_Pvt;
667 p_return_status := FND_API.G_RET_STS_ERROR;
668
669 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
670 p_data => p_msg_data);
671
672
673 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
674 ROLLBACK to Commitment_Funds_Check_Pvt;
675 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
676
677 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
678 p_data => p_msg_data);
679
680 WHEN OTHERS THEN
681 ROLLBACK to Commitment_Funds_Check_Pvt;
682 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
683
684 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
685
686 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
687 l_api_name);
688 END IF;
689
690 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
691 p_data => p_msg_data);
692
693 END Commitment_Funds_Check;
694
695 /*-------------------------------------------------------------------------*/
696
697 FUNCTION Is_Cbc_Enabled
698 ( p_set_of_books_id IN NUMBER
699 ) RETURN BOOLEAN
700 IS
701 l_plsql_block VARCHAR2(100);
702 l_cbc_enabled VARCHAR2(1);
703
704 BEGIN
705
706 --Dynamic sql statement to find whether CBC is enabled
707
708 l_plsql_block := 'BEGIN :cbc_enabled := IGC_PSB_COMMITMENTS_PVT.IS_Cbc_Enabled(:sob); END;';
709 EXECUTE IMMEDIATE l_plsql_block USING OUT l_cbc_enabled, IN p_set_of_books_id;
710
711 if FND_API.to_Boolean(l_cbc_enabled) then
712 return TRUE;
713 else
714 return FALSE;
715 end if;
716
717 EXCEPTION
718 WHEN OTHERS THEN
719 Add_Message('PSB', 'PSB_CBC_ENABLED_STATUS');
720 RETURN FALSE;
721
722 END Is_Cbc_Enabled;
723
724 /* ----------------------------------------------------------------------- */
725
726 -- Add Token and Value to the Message Token array
727
728 PROCEDURE message_token(tokname IN VARCHAR2,
729 tokval IN VARCHAR2) IS
730
731 BEGIN
732
733 if no_msg_tokens is null then
734 no_msg_tokens := 1;
735 else
736 no_msg_tokens := no_msg_tokens + 1;
737 end if;
738
739 msg_tok_names(no_msg_tokens) := tokname;
740 msg_tok_val(no_msg_tokens) := tokval;
741
742 END message_token;
743
744 /* ----------------------------------------------------------------------- */
745
746 -- Define a Message Token with a Value and set the Message Name
747
748 -- Calls FND_MESSAGE server package to set the Message Stack. This message is
749 -- retrieved by the calling program.
750
751 PROCEDURE add_message(appname IN VARCHAR2,
752 msgname IN VARCHAR2) IS
753
754 i BINARY_INTEGER;
755
756 BEGIN
757
758 if ((appname is not null) and
759 (msgname is not null)) then
760
761 FND_MESSAGE.SET_NAME(appname, msgname);
762
763 if no_msg_tokens is not null then
764 for i in 1..no_msg_tokens loop
765 FND_MESSAGE.SET_TOKEN(msg_tok_names(i), msg_tok_val(i));
766 end loop;
767 end if;
768
769 FND_MSG_PUB.Add;
770
771 end if;
772
773 -- Clear Message Token stack
774
775 no_msg_tokens := 0;
776
777 END add_message;
778
779 /* ----------------------------------------------------------------------- */
780
781 END PSB_COMMITMENTS_PVT;