[Home] [Help]
PACKAGE BODY: APPS.PSB_GL_BUDGET_PVT
Source
1 PACKAGE BODY PSB_GL_Budget_Pvt AS
2 /* $Header: PSBVGBDB.pls 120.2 2005/07/13 11:26:29 shtripat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_GL_Budget_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_id IN OUT NOCOPY NUMBER,
36 p_gl_budget_set_id IN NUMBER,
37 p_gl_budget_version_id IN NUMBER,
38 p_start_period IN VARCHAR2,
39 p_end_period IN VARCHAR2,
40 p_start_date IN DATE,
41 p_end_date IN DATE,
42 p_dual_posting_type IN VARCHAR2,
43 p_last_update_date IN DATE,
44 p_last_updated_by IN NUMBER,
45 p_last_update_login IN NUMBER,
46 p_created_by IN NUMBER,
47 p_creation_date IN DATE
48 )
49 IS
50 --
51 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
52 l_api_version CONSTANT NUMBER := 1.0;
53 --
54 CURSOR C IS
55 SELECT rowid
56 FROM psb_gl_budgets
57 WHERE gl_budget_id = p_gl_budget_id ;
58
59 CURSOR C2 IS
60 SELECT psb_gl_budgets_s.NEXTVAL
61 FROM dual ;
62 --
63 BEGIN
64 --
65 SAVEPOINT Insert_Row_Pvt ;
66 --
67 IF NOT FND_API.Compatible_API_Call ( l_api_version,
68 p_api_version,
69 l_api_name,
70 G_PKG_NAME )
71 THEN
72 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
73 END IF;
74 --
75
76 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
77 FND_MSG_PUB.initialize ;
78 END IF;
79 --
80 p_return_status := FND_API.G_RET_STS_SUCCESS ;
81 --
82
83 IF ( p_gl_budget_id IS NULL ) THEN
84 OPEN C2 ;
85 FETCH C2 INTO p_gl_budget_id ;
86 CLOSE C2 ;
87 END IF;
88
89 INSERT INTO psb_gl_budgets
90 (
91 gl_budget_id ,
92 gl_budget_set_id ,
93 gl_budget_version_id ,
94 start_period ,
95 end_period ,
96 start_date ,
97 end_date ,
98 dual_posting_type ,
99 last_update_date ,
100 last_updated_by ,
101 last_update_login ,
102 created_by ,
103 creation_date )
104 VALUES
105 (
106 p_gl_budget_id ,
107 p_gl_budget_set_id ,
108 p_gl_budget_version_id ,
109 p_start_period ,
110 p_end_period ,
111 p_start_date ,
112 p_end_date ,
113 p_dual_posting_type ,
114 p_last_update_date ,
115 p_last_updated_by ,
116 p_last_update_login ,
117 p_created_by ,
118 p_creation_date
119 );
120 OPEN C;
121 FETCH C INTO p_row_id;
122 IF (C%NOTFOUND) THEN
123 CLOSE C;
124 RAISE FND_API.G_EXC_ERROR ;
125 END IF;
126 CLOSE C;
127 --
128
129 --
130 IF FND_API.To_Boolean ( p_commit ) THEN
131 COMMIT WORK;
132 END iF;
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 --
141 ROLLBACK TO Insert_Row_Pvt ;
142 p_return_status := FND_API.G_RET_STS_ERROR;
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 --
148 ROLLBACK TO Insert_Row_Pvt ;
149 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
150 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
151 p_data => p_msg_data );
152 --
153 WHEN OTHERS THEN
154 --
155 ROLLBACK TO Insert_Row_Pvt ;
156 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
157 --
158 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
159 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
160 l_api_name);
161 END if;
162 --
163 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
164 p_data => p_msg_data );
165 --
166 END Insert_Row;
167 /*-------------------------------------------------------------------------*/
168
169
170
171 /*==========================================================================+
172 | PROCEDURE Lock_Row |
173 +==========================================================================*/
174 PROCEDURE Lock_Row
175 (
176 p_api_version IN NUMBER,
177 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
178 p_commit IN VARCHAR2 := FND_API.G_FALSE,
179 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
180 p_return_status OUT NOCOPY VARCHAR2,
181 p_msg_count OUT NOCOPY NUMBER,
182 p_msg_data OUT NOCOPY VARCHAR2,
183 --
184 p_row_id IN VARCHAR2,
185 p_gl_budget_id IN NUMBER,
186 p_gl_budget_set_id IN NUMBER,
187 p_gl_budget_version_id IN NUMBER,
188 p_start_period IN VARCHAR2,
189 p_end_period IN VARCHAR2,
190 p_start_date IN DATE,
191 p_end_date IN DATE,
192 p_dual_posting_type IN VARCHAR2,
193 --
194 p_row_locked OUT NOCOPY VARCHAR2
195 )
196 IS
197 --
198 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
199 l_api_version CONSTANT NUMBER := 1.0;
200 --
201 Counter NUMBER;
202 CURSOR C IS
203 SELECT *
204 FROM psb_gl_budgets
205 WHERE rowid = p_row_id
206 FOR UPDATE OF gl_budget_id NOWAIT;
207 --
208 Recinfo C%ROWTYPE;
209 --
210 BEGIN
211 --
212 SAVEPOINT Lock_Row_Pvt ;
213 --
214 IF NOT FND_API.Compatible_API_Call ( l_api_version,
215 p_api_version,
216 l_api_name,
217 G_PKG_NAME )
218 THEN
219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
220 END IF;
221 --
222
223 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
224 FND_MSG_PUB.initialize ;
225 END IF;
226 --
227 p_return_status := FND_API.G_RET_STS_SUCCESS ;
228 p_row_locked := FND_API.G_TRUE ;
229 --
230 OPEN C;
231 --
232 FETCH C INTO Recinfo;
233 IF (C%NOTFOUND) then
234 CLOSE C;
235 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
236 FND_MSG_PUB.Add;
237 RAISE FND_API.G_EXC_ERROR ;
238 END IF;
239 CLOSE C;
240 IF
241 (
242 (Recinfo.gl_budget_id = p_gl_budget_id)
243
244
245 AND ( (Recinfo.gl_budget_set_id = p_gl_budget_set_id)
246 OR ( (Recinfo.gl_budget_set_id IS NULL)
247 AND (p_gl_budget_set_id IS NULL)))
248
249 AND ( (Recinfo.gl_budget_version_id = p_gl_budget_version_id)
250 OR ( (Recinfo.gl_budget_version_id IS NULL)
251 AND (p_gl_budget_version_id IS NULL)))
252
253 AND ( (Recinfo.start_period = p_start_period)
254 OR ( (Recinfo.start_period IS NULL)
255 AND (p_start_period IS NULL)))
256
257 AND ( (Recinfo.end_period = p_end_period)
258 OR ( (Recinfo.end_period IS NULL)
259 AND (p_end_period IS NULL)))
260
261 AND ( (Recinfo.start_date = p_start_date)
262 OR ( (Recinfo.start_date IS NULL)
263 AND (p_start_date IS NULL)))
264
265 AND ( (Recinfo.end_date = p_end_date)
266 OR ( (Recinfo.end_date IS NULL)
267 AND (p_end_date IS NULL)))
268
269 AND ( (Recinfo.dual_posting_type = p_dual_posting_type)
270 OR ( (Recinfo.dual_posting_type IS NULL)
271 AND (p_dual_posting_type IS NULL)))
272 )
273 THEN
274 Null;
275 ELSE
276 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
277 FND_MSG_PUB.Add;
278 RAISE FND_API.G_EXC_ERROR ;
279 END IF;
280
281 --
282 IF FND_API.To_Boolean ( p_commit ) THEN
283 COMMIT WORK;
284 END iF;
285 --
286 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
287 p_data => p_msg_data );
288 --
289 EXCEPTION
290 --
291 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
292 --
293 ROLLBACK TO Lock_Row_Pvt ;
294 p_row_locked := FND_API.G_FALSE;
295 p_return_status := FND_API.G_RET_STS_ERROR;
296 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
297 p_data => p_msg_data );
298 --
299 WHEN FND_API.G_EXC_ERROR THEN
300 --
301 ROLLBACK TO Lock_Row_Pvt ;
302 p_return_status := FND_API.G_RET_STS_ERROR;
303 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
304 p_data => p_msg_data );
305 --
306 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
307 --
308 ROLLBACK TO Lock_Row_Pvt ;
309 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
310 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
311 p_data => p_msg_data );
312 --
313 WHEN OTHERS THEN
314 --
315 ROLLBACK TO Lock_Row_Pvt ;
316 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
317 --
318 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
319 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
320 l_api_name);
321 END if;
322 --
323 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
324 p_data => p_msg_data );
325 --
326 END Lock_Row;
327 /* ----------------------------------------------------------------------- */
328
329
330
331 /*==========================================================================+
332 | PROCEDURE Update_Row |
333 +==========================================================================*/
334 PROCEDURE Update_Row
335 (
336 p_api_version IN NUMBER,
337 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
338 p_commit IN VARCHAR2 := FND_API.G_FALSE,
339 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
340 p_return_status OUT NOCOPY VARCHAR2,
341 p_msg_count OUT NOCOPY NUMBER,
342 p_msg_data OUT NOCOPY VARCHAR2,
343 --
344 p_row_id IN VARCHAR2,
345 p_gl_budget_set_id IN NUMBER,
346 p_gl_budget_version_id IN NUMBER,
347 p_start_period IN VARCHAR2,
348 p_end_period IN VARCHAR2,
349 p_start_date IN DATE,
350 p_end_date IN DATE,
351 p_dual_posting_type IN VARCHAR2,
352 p_last_update_date IN DATE,
353 p_last_updated_by IN NUMBER,
354 p_last_update_login IN NUMBER
355 )
356 IS
357 --
358 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
359 l_api_version CONSTANT NUMBER := 1.0;
360 --
361 BEGIN
362 --
363 SAVEPOINT Update_Row_Pvt ;
364 --
365 IF NOT FND_API.Compatible_API_Call ( l_api_version,
366 p_api_version,
367 l_api_name,
368 G_PKG_NAME )
369 THEN
370 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
371 END IF;
372 --
373
374 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
375 FND_MSG_PUB.initialize ;
376 END IF;
377 --
378 p_return_status := FND_API.G_RET_STS_SUCCESS ;
379 --
380
381 UPDATE psb_gl_budgets
382 SET gl_budget_set_id = p_gl_budget_set_id ,
383 gl_budget_version_id = p_gl_budget_version_id ,
384 start_period = p_start_period ,
385 end_period = p_end_period ,
386 start_date = p_start_date ,
387 end_date = p_end_date ,
388 dual_posting_type = p_dual_posting_type ,
389 last_update_date = p_last_update_date ,
390 last_updated_by = p_last_updated_by ,
391 last_update_login = p_last_update_login
392 WHERE rowid = p_row_id;
393
394 IF (SQL%NOTFOUND) THEN
395 RAISE NO_DATA_FOUND ;
396 END IF;
397
398 --
399 IF FND_API.To_Boolean ( p_commit ) THEN
400 COMMIT WORK;
401 END iF;
402 --
403 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
404 p_data => p_msg_data );
405 --
406 EXCEPTION
407 --
408 WHEN FND_API.G_EXC_ERROR THEN
409 --
410 ROLLBACK TO Update_Row_Pvt ;
411 p_return_status := FND_API.G_RET_STS_ERROR;
412 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
413 p_data => p_msg_data );
414 --
415 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
416 --
417 ROLLBACK TO Update_Row_Pvt ;
418 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
419 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
423 --
420 p_data => p_msg_data );
421 --
422 WHEN OTHERS THEN
424 ROLLBACK TO Update_Row_Pvt ;
425 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
426 --
427 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
428 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
429 l_api_name);
430 END if;
431 --
432 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
433 p_data => p_msg_data );
434 --
435 END Update_Row;
436 /* ----------------------------------------------------------------------- */
437
438
439
440 /*==========================================================================+
441 | PROCEDURE Delete_Row |
442 +==========================================================================*/
443 PROCEDURE Delete_Row
444 (
445 p_api_version IN NUMBER,
446 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
447 p_commit IN VARCHAR2 := FND_API.G_FALSE,
448 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
449 p_return_status OUT NOCOPY VARCHAR2,
450 p_msg_count OUT NOCOPY NUMBER,
451 p_msg_data OUT NOCOPY VARCHAR2,
452 --
453 p_row_id IN VARCHAR2
454 )
455 IS
456 --
457 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
458 l_api_version CONSTANT NUMBER := 1.0;
459 --
460 l_return_status VARCHAR2(1) ;
461 l_msg_count NUMBER ;
462 l_msg_data VARCHAR2(2000) ;
463 --
464 l_gl_budget_id psb_gl_budgets.gl_budget_id%TYPE;
465 --
466 BEGIN
467 --
468 SAVEPOINT Delete_Row_Pvt ;
469 --
470 IF NOT FND_API.Compatible_API_Call ( l_api_version,
471 p_api_version,
472 l_api_name,
473 G_PKG_NAME )
474 THEN
475 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
476 END IF;
477 --
478
479 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
480 FND_MSG_PUB.initialize ;
481 END IF ;
482 --
483 p_return_status := FND_API.G_RET_STS_SUCCESS ;
484 --
485
486 --
487 -- Deleting dependent detail records from psb_account_position_set_lines.
488 -- ( To maintain ISOLATED master-detail form relation also. )
489 --
490
491 --
492 -- First delete all the set related information.
493 --
494
495 SELECT gl_budget_id INTO l_gl_budget_id
496 FROM psb_gl_budgets
497 WHERE rowid = p_row_id ;
498
499 PSB_Set_Relation_PVT.Delete_Entity_Relation
500 (
501 p_api_version => 1.0 ,
502 p_init_msg_list => FND_API.G_FALSE,
503 p_commit => FND_API.G_FALSE,
504 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
505 p_return_status => l_return_status,
506 p_msg_count => l_msg_count,
507 p_msg_data => l_msg_data,
508 --
509 p_entity_type => 'GBS' ,
510 p_entity_id => l_gl_budget_id
511 );
512 --
513 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
514 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
515 END IF ;
516
517 --
518 -- End deleting set related information.
519 --
520
521 --
522 -- Deleting the record in psb_gl_budgets.
523 --
524 DELETE psb_gl_budgets
525 WHERE rowid = p_row_id;
526
527 IF (SQL%NOTFOUND) THEN
528 RAISE NO_DATA_FOUND ;
529 END IF;
530
531 --
532 IF FND_API.To_Boolean ( p_commit ) THEN
533 COMMIT WORK;
534 END iF;
535 --
536 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
537 p_data => p_msg_data );
538
539 EXCEPTION
540 --
541 WHEN FND_API.G_EXC_ERROR THEN
542 --
543 ROLLBACK TO Delete_Row_Pvt ;
544 p_return_status := FND_API.G_RET_STS_ERROR;
545 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
546 p_data => p_msg_data );
547 --
548 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
549 --
550 ROLLBACK TO Delete_Row_Pvt ;
551 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
552 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
553 p_data => p_msg_data );
554 --
555 WHEN OTHERS THEN
556 --
557 ROLLBACK TO Delete_Row_Pvt ;
558 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
559 --
560 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
561 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
562 l_api_name);
563 END if;
564 --
565 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
566 p_data => p_msg_data );
567 --
568 END Delete_Row;
569 /* ----------------------------------------------------------------------- */
570
571
572
573 /*===========================================================================+
574 | PROCEDURE Find_GL_Budget |
578 -- Code combination id.
575 +===========================================================================*/
576 --
577 -- This API finds the name of the GL Budget for a given GL Budget Set and a
579 --
580 PROCEDURE Find_GL_Budget
581 (
582 p_api_version IN NUMBER,
583 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
584 p_commit IN VARCHAR2 := FND_API.G_FALSE,
585 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
586 p_return_status OUT NOCOPY VARCHAR2,
587 p_msg_count OUT NOCOPY NUMBER,
588 p_msg_data OUT NOCOPY VARCHAR2,
589 --
590 p_gl_budget_set_id IN NUMBER,
591 p_code_combination_id IN NUMBER,
592 p_start_date IN DATE,
593 p_dual_posting_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
594 --
595 p_gl_budget_version_id OUT NOCOPY NUMBER
596 )
597 IS
598 --
599 l_api_name CONSTANT VARCHAR2(30) := 'Find_GL_Budget' ;
600 l_api_version CONSTANT NUMBER := 1.0 ;
601 --
602 CURSOR l_find_ccid_csr
603 (
604 c_gl_budget_id psb_gl_budgets.gl_budget_id%TYPE ,
605 c_code_combination_id psb_budget_accounts.code_combination_id%TYPE
606 )
607 IS
608 SELECT '1'
609 FROM psb_set_relations rel ,
610 psb_budget_accounts pba
611 WHERE rel.gl_budget_id = c_gl_budget_id
612 AND pba.account_position_set_id = rel.account_position_set_id
613 AND pba.code_combination_id = c_code_combination_id ;
614 --
615 l_tmp VARCHAR2(1) ;
616 l_budget_found_flag VARCHAR2(1) := NULL ;
617 l_dual_posting_type VARCHAR2(1) ;
618 --
619 BEGIN
620 --
621 IF NOT FND_API.Compatible_API_Call ( l_api_version,
622 p_api_version,
623 l_api_name,
624 G_PKG_NAME )
625 THEN
626 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
627 END IF;
628 --
629
630 IF FND_API.To_Boolean ( p_init_msg_list ) THEN
631 FND_MSG_PUB.initialize ;
632 END IF;
633 --
634 p_return_status := FND_API.G_RET_STS_SUCCESS ;
635 --
636
637 --
638 -- Missing p_dual_posting_type is equivalent to 'P' (Permanent).
639 --
640 IF ( p_dual_posting_type = FND_API.G_MISS_CHAR) OR
641 ( p_dual_posting_type IS NULL)
642 THEN
643 l_dual_posting_type := 'P' ;
644 ELSE
645 l_dual_posting_type := p_dual_posting_type ;
646 END IF;
647
648 --
649 -- Validate the parameters.
650 --
651
652 IF l_dual_posting_type NOT IN ( 'A', 'P' ) THEN
653 Fnd_Message.Set_Name ('PSB', 'PSB_INVALID_ARGUMENT') ;
654 Fnd_Message.Set_Token('ROUTINE', l_api_name ) ;
655 FND_MSG_PUB.Add;
656 RAISE FND_API.G_EXC_ERROR ;
657 END IF;
658
659
660 BEGIN
661
662 SELECT '1' INTO l_tmp
663 FROM psb_gl_budget_sets
664 WHERE gl_budget_set_id = p_gl_budget_set_id ;
665
666 EXCEPTION
667 WHEN no_data_found THEN
668 Fnd_Message.Set_Name ('PSB', 'PSB_INVALID_ARGUMENT') ;
669 Fnd_Message.Set_Token('ROUTINE', l_api_name ) ;
670 FND_MSG_PUB.Add;
671 RAISE FND_API.G_EXC_ERROR ;
672 END ;
673
674 --
675 -- End validatiing parameters.
676 --
677
678 --
679 -- Scan all the GL budgets to find out which one contains the given CCID.
680 -- Only one GL Budget (GL_BUDGET_ID) can contain it for a given period
681 -- and a given dual_posting_type. The dual_posting_type being NULL is
682 -- equivalent to being 'P'.
683 --
684 FOR l_gl_budget_rec IN
685 (
686 SELECT gl_budget_id ,
687 gl_budget_version_id
688 FROM psb_gl_budgets
689 WHERE gl_budget_set_id = p_gl_budget_set_id
690 AND p_start_date BETWEEN start_date AND end_date
691 AND NVL( dual_posting_type, 'P' ) = l_dual_posting_type
692
693 )
694 LOOP
695
696 -- pd('Budget id : ' || l_gl_budget_rec.gl_budget_id ) ;
697
698 l_budget_found_flag := NULL;
699
700 -- Check whether the CCID belongs to this GL Budget ot not.
701 OPEN l_find_ccid_csr
702 ( l_gl_budget_rec.gl_budget_id,
703 p_code_combination_id
704 );
705 FETCH l_find_ccid_csr INTO l_budget_found_flag ;
706 CLOSE l_find_ccid_csr;
707
708 IF l_budget_found_flag IS NOT NULL THEN
709
710 -- It means the CCID belongs to the current GL Budget Id.
711 p_gl_budget_version_id := l_gl_budget_rec.gl_budget_version_id ;
712
713 -- Exit the loop now.
714 EXIT ;
715
716 END IF;
717
718 END LOOP ; -- End processing GL budgets related to p_gl_budget_set_id.
719
720 -- Assign NULL to out parameters if GL Budget is not found.
721 IF l_budget_found_flag IS NULL THEN
722 p_gl_budget_version_id := NULL ;
723 END IF ;
724
725 --
726 IF FND_API.To_Boolean ( p_commit ) THEN
727 COMMIT WORK;
731 p_data => p_msg_data );
728 END IF;
729 --
730 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
732 --
733 EXCEPTION
734 --
735 WHEN FND_API.G_EXC_ERROR THEN
736 --
737 p_return_status := FND_API.G_RET_STS_ERROR;
738 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
739 p_data => p_msg_data );
740 --
741 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
742 --
743 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
744 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
745 p_data => p_msg_data );
746 --
747 WHEN OTHERS THEN
748 --
749 IF ( l_find_ccid_csr%ISOPEN ) THEN
750 CLOSE l_find_ccid_csr ;
751 END IF ;
752 --
753 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
754 --
755 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
756 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
757 l_api_name);
758 END if;
759 --
760 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
761 p_data => p_msg_data );
762 --
763 END Find_GL_Budget ;
764 /*---------------------------------------------------------------------------*/
765
766
767
768 /*===========================================================================+
769 | PROCEDURE pd (Private) |
770 +===========================================================================*/
771 --
772 -- Private procedure to print debug info. The name is tried to keep as
773 -- short as possible for better documentaion.
774 --
775 PROCEDURE pd
776 (
777 p_message IN VARCHAR2
778 )
779 IS
780 --
781 BEGIN
782
783 IF g_debug_flag = 'Y' THEN
784 NULL;
785 -- dbms_output.put_line(p_message) ;
786 END IF;
787
788 END pd ;
789 /*---------------------------------------------------------------------------*/
790
791
792 END PSB_GL_Budget_Pvt ;