[Home] [Help]
PACKAGE BODY: APPS.PSB_BUDGET_ACCOUNT_PVT
Source
1 PACKAGE BODY PSB_Budget_Account_PVT AS
2 /* $Header: PSBVMBAB.pls 120.8 2006/01/09 06:07:03 maniskum ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_Budget_Account_PVT';
5
6
7 /*--------------------------- Global variables -----------------------------*/
8
9
10 -- The concurrent program executes the execution file in the same session.
11 -- Still it is safe to use these global variables here as each execution
12 -- of the program, re-populated the global variables first.
13
14 -- The flag determines whether to print debug information or not.
15 g_debug_flag VARCHAR2(1) := 'N' ;
16
17 -- Bug 3458191: To store chart of account id to caching active segments
18 g_cached_chart_of_account_id NUMBER :=0;
19
20 -- Table to store active segments for a given chart of accounts.
21 TYPE Active_Segments_tbl_type IS TABLE OF VARCHAR2(10)
22 INDEX BY BINARY_INTEGER;
23
24 -- To store set of books id for the current set.
25 g_set_of_books_id psb_account_position_sets.set_of_books_id%TYPE;
26
27 -- To store chart of accounts id for the current set.
28 g_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
29
30 -- To store total number of active segments for the current chart of
31 -- accounts..
32 g_total_active_segments NUMBER := 0;
33
34 -- To store all active segments for the chart of accounts.
35 g_active_segments_tbl Active_Segments_tbl_type;
36
37 -- To store maximum code combination id for a chart of accounts.
38 g_max_code_combination_id
39 psb_account_position_sets.max_code_combination_id%TYPE;
40
41 -- To store current account set id.
42 g_account_set_id
43 psb_account_position_sets.account_position_set_id%TYPE;
44
45 --
46 -- WHO columns variables
47 --
48
49 g_current_date DATE := sysdate ;
50 g_current_user_id NUMBER := NVL(Fnd_Global.User_Id , 0) ;
51 g_current_login_id NUMBER := NVL(Fnd_Global.Login_Id, 0) ;
52
53 /*----------------------- End Private variables -----------------------------*/
54
55
56
57
58
59 /* ---------------------- Private Routine prototypes -----------------------*/
60
61 PROCEDURE Init;
62 --
63 --
64 FUNCTION Populate_Budget_Account_Set ( p_account_set_id IN NUMBER ,
65 -- bug no 3573740
66 p_full_maintainence_flag IN VARCHAR2 := 'N')
67 RETURN BOOLEAN;
68 --
69 --
70 FUNCTION Get_Active_Segments ( p_chart_of_accounts_id IN NUMBER )
71 RETURN BOOLEAN;
72
73 --
74 --
75 FUNCTION Make_Account_Assignments
76 (
77 p_line_sequence_id IN NUMBER ,
78 p_include_or_exclude_type IN VARCHAR2
79 )
80 RETURN BOOLEAN;
81
82 PROCEDURE pd
83 (
84 p_message IN VARCHAR2
85 ) ;
86
87 /* ------------------ End Private Routines prototypes ----------------------*/
88
89
90
91 /*===========================================================================+
92 | PROCEDURE PSB_Budget_Account_PVT |
93 +===========================================================================*/
94 --
95 -- The Public API to populate account codes for account sets.
96 --
97
98 PROCEDURE Populate_Budget_Accounts
99 (
100 p_api_version IN NUMBER ,
101 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
102 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
103 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
104 p_return_status OUT NOCOPY VARCHAR2 ,
105 p_msg_count OUT NOCOPY NUMBER ,
106 p_msg_data OUT NOCOPY VARCHAR2 ,
107 --
108 p_set_of_books_id IN NUMBER := FND_API.G_MISS_NUM ,
109 p_account_set_id IN NUMBER := FND_API.G_MISS_NUM ,
110 -- bug no 3573740
111 p_full_maintainence_flag IN VARCHAR2 := 'N'
112 )
113 IS
114 --
115 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Budget_Accounts' ;
116 l_api_version CONSTANT NUMBER := 1.0;
117 --
118 l_set_of_books_id psb_account_position_sets.set_of_books_id%TYPE ;
119 l_number NUMBER;
120 --
121 BEGIN
122 --
123 SAVEPOINT Populate_Budget_Accounts_Pvt ;
124 --
125 IF NOT FND_API.Compatible_API_Call ( l_api_version,
126 p_api_version,
127 l_api_name,
128 G_PKG_NAME )
129 THEN
130 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
131 END IF;
132 --
133
134 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
135 FND_MSG_PUB.initialize ;
136 END IF;
137 --
138 p_return_status := FND_API.G_RET_STS_SUCCESS ;
139 --
140
141 --
142 -- As FND_API.G_MISS_NUM is bigger than NUMBERR(15), Using a local variable.
143 --
144 IF p_set_of_books_id = FND_API.G_MISS_NUM THEN
145 l_set_of_books_id := NULL;
146 ELSE
147 l_set_of_books_id := p_set_of_books_id ;
148 END IF;
149
150
151 IF ( p_account_set_id = FND_API.G_MISS_NUM ) OR ( p_account_set_id IS NULL)
152 THEN
153 --
154 -- As no parameter is supplied, we have to populate all the account
155 -- sets in psb_account_position_sets table.
156 --
157 FOR l_set_rec IN
158 (
159 SELECT account_position_set_id
160 FROM psb_account_position_sets
161 WHERE account_or_position_type = 'A'
162 AND set_of_books_id = NVL( l_set_of_books_id, set_of_books_id )
163 -- Bug 3458191: Add order by to taking advantage of caching
164 ORDER BY set_of_books_id
165 )
166 LOOP
167 --
168 -- Perform initilization. To be done for each account set.
169 --
170 Init;
171
172 --
173 -- Call the Populate_Budget_Account_Set routine for each account set.
174 --
175 -- bug no 3573740 (added parameter p_full_maintainence_flag)
176 IF Populate_Budget_Account_Set( l_set_rec.account_position_set_id ,
177 p_full_maintainence_flag ) THEN
178 --
179 -- The concurrent program is the only one which calls the API
180 -- without any argument. We need to release lock as soon as an
181 -- account_set_id is exploded. Committing will also ensure
182 -- that rollback segments do not go out of bounds.
183 --
184 COMMIT WORK;
185 --
186 -- Re-establish the savepoint after the commit.
187 SAVEPOINT Populate_Budget_Accounts_Pvt ;
188 --
189 ELSE
190 --
191 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
192 --
193 END IF;
194 END LOOP;
195 --
196 ELSE
197 --
198 -- Only the passed account set will be populated.
199 -- Perform initilization for this set.
200 --
201 Init;
202
203 --
204 -- Call Populate_Budget_Account_Set only for the given account set.
205 --
206 -- bug no 3573740 (added parameter p_full_maintainence_flag)
207 IF NOT ( Populate_Budget_Account_Set( p_account_set_id ,
208 p_full_maintainence_flag) ) THEN
209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
210 END IF;
211 END IF;
212
213
214 --
215 IF FND_API.To_Boolean ( p_commit ) THEN
216 pd('Final Commiting');
217 COMMIT WORK;
218 END IF;
219 --
220 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
221 p_data => p_msg_data );
222 --
223
224 EXCEPTION
225 --
226 WHEN FND_API.G_EXC_ERROR THEN
227 --
228 ROLLBACK TO Populate_Budget_Accounts_Pvt ;
229 p_return_status := FND_API.G_RET_STS_ERROR;
230 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
231 p_data => p_msg_data );
232 --
233 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
234 --
235 ROLLBACK TO Populate_Budget_Accounts_Pvt ;
236 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
237 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
238 p_data => p_msg_data );
239 --
240 WHEN OTHERS THEN
241 --
242 ROLLBACK TO Populate_Budget_Accounts_Pvt ;
243 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
244 --
245 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
246 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
247 l_api_name);
248 END if;
249 --
250 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
251 p_data => p_msg_data );
252 --
253 END Populate_Budget_Accounts;
254 /*---------------------------------------------------------------------------*/
255
256
257
258 /*===========================================================================+
259 | PROCEDURE Init (Private) |
260 +===========================================================================*/
261 --
262 -- Private procedure to perform variable initilization.
263 --
264
265 PROCEDURE Init
266 IS
267 --
268 BEGIN
269 --
270 -- Date needs to be re-initialized because the concurrent program
271 -- may run for days in backgroud.
272 --
273 g_current_date := sysdate;
274 --
275 END Init;
276 /*---------------------------------------------------------------------------*/
277
278
279
280
281 /*===========================================================================+
282 | FUNCTION Populate_Budget_Account_Set (Private) |
283 +===========================================================================*/
284 --
285 -- This Private function is to populate a given account set.
286 --
287
288 FUNCTION Populate_Budget_Account_Set( p_account_set_id IN NUMBER ,
289 -- bug no 3573740
290 p_full_maintainence_flag IN VARCHAR2 := 'N')
291 RETURN BOOLEAN
292 IS
293 -- Local variables
294 l_last_maintained_date DATE ;
295 l_last_update_date DATE ;
296
297 BEGIN
298 -- Populate the global variable.
299 g_account_set_id := p_account_set_id;
300
301 -- Get various information for the account_set_id.
302 SELECT set_of_books_id,
303 NVL( max_code_combination_id, 0 ),
304 NVL( last_maintained_date, last_update_date - 1 ),
305 last_update_date
306 INTO g_set_of_books_id,
307 g_max_code_combination_id,
308 l_last_maintained_date,
309 l_last_update_date
310 FROM psb_account_position_sets
311 WHERE account_position_set_id = p_account_set_id ;
312
313 -- Get the chart of accounts for the set_of_books_id.
314 SELECT chart_of_accounts_id INTO g_chart_of_accounts_id
315 FROM gl_sets_of_books
316 WHERE set_of_books_id = g_set_of_books_id;
317
318 -- Bug 3458191: Introduce the following condition to avoiding extra queries.
319 IF g_chart_of_accounts_id <> NVL(g_cached_chart_of_account_id, -99)
320 THEN
321 -- Finding active segments.
322 IF NOT Get_Active_Segments( g_chart_of_accounts_id ) THEN
323 RETURN (FALSE);
324 END IF;
325 END IF;
326
327 --
328 -- Lock psb_account_position_sets table to prevent modifications.
329 -- Set maintain_status to 'P' (meaning Processing).
330 --
331 UPDATE psb_account_position_sets
332 SET maintain_status = 'C'
333 WHERE account_position_set_id = p_account_set_id;
334
335 --
336 -- Check whether the account_set has been modified since last maintenance.
337 -- If Yes, you need to rebuild the psb_budget_accounts table for this
338 -- account set.
339 --
340 IF l_last_update_date > l_last_maintained_date THEN
341
342 -- Delete from psb_budget_accounts
343 DELETE psb_budget_accounts
344 WHERE account_position_set_id = p_account_set_id ;
345
346 -- Reset g_max_code_combination_id as you have to rebuild.
347 g_max_code_combination_id := 0 ;
348 END IF;
349
350 --
351 --
352 /* start bug 3573740 */
353 IF p_full_maintainence_flag = 'Y' THEN
354 g_max_code_combination_id := 0;
355 END IF;
356 /* end bug 3573740 */
357 -- Get account ranges in the account set.
358 -- We must process Included ranges before Excluded ones.
359 --
360 FOR l_line_rec IN
361 (
362 SELECT line_sequence_id, include_or_exclude_type
363 FROM psb_account_position_set_lines
364 WHERE account_position_set_id = p_account_set_id
365 ORDER BY include_or_exclude_type DESC
366 )
367 LOOP
368 --
369 -- Get the account codes falling in each range represented by
370 -- line_sequence_id and put them im psb_budget_accounts table.
371 --
372 IF NOT Make_Account_Assignments
373 (
374 l_line_rec.line_sequence_id ,
375 l_line_rec.include_or_exclude_type
376 )
377 THEN
378 RETURN (FALSE);
379 END IF;
380 END LOOP;
381
382 --
383 -- Update max_code_combination_id info in psb_account_position_sets.
384 -- Set maintain_status to 'C' (meaning updated from PSBVMBAB module).
385 --
386 UPDATE psb_account_position_sets
387 SET maintain_status = 'C' ,
388 last_maintained_date = g_current_date ,
389 max_code_combination_id =
390 ( SELECT max(code_combination_id)
391 FROM gl_code_combinations )
392 WHERE account_position_set_id = p_account_set_id;
393 --
394 RETURN (TRUE);
395
396 EXCEPTION
397 WHEN OTHERS THEN
398 --
399 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
400 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
401 'Populate_Budget_Account_Set' );
402 END if;
403 --
404 RETURN (FALSE);
405 --
406 END Populate_Budget_Account_Set;
407 /*---------------------------------------------------------------------------*/
408
409
410
411
412 /*===========================================================================+
413 | FUNCTION Get_Active_Segments (Private) |
414 +===========================================================================*/
415 --
416 -- This Private function finds active segments in gl_code_combinations table
417 -- and stores those in a global table g_active_segments_tab.
418 --
419
420 FUNCTION Get_Active_Segments( p_chart_of_accounts_id IN NUMBER )
421 RETURN BOOLEAN
422 IS
423 /* Start bug #4924031 */
424 l_id_flex_code fnd_id_flex_structures.id_flex_code%TYPE;
425 l_application_id fnd_id_flex_structures.application_id%TYPE;
426 l_yes_flag VARCHAR2(1);
427 /* End bug #4924031 */
428
429 BEGIN
430
431 --
432 -- Initialize for each chart of accounts.
433 --
434 g_total_active_segments := 0;
435
436 /* Start bug #4924031 */
437 l_id_flex_code := 'GL#';
438 l_application_id := 101;
439 l_yes_flag := 'Y';
440 /* End bug #4924031 */
441
442 FOR l_flex_rec IN
443 (
444 SELECT seg.application_column_name
445 FROM fnd_id_flex_structures str, fnd_id_flex_segments seg
446 WHERE str.application_id = l_application_id -- bug #4924031
447 AND str.id_flex_code = l_id_flex_code -- bug #4924031
448 AND str.id_flex_num = p_chart_of_accounts_id
449 AND str.id_flex_code = seg.id_flex_code
450 AND str.id_flex_num = seg.id_flex_num
451 AND seg.enabled_flag = l_yes_flag -- bug #4924031
452 AND seg.application_id = str.application_id -- bug #4924031
453 )
454 LOOP
455 g_total_active_segments := g_total_active_segments + 1;
456 --
457 g_active_segments_tbl(g_total_active_segments) :=
458 l_flex_rec.application_column_name;
459 --
460 END LOOP;
461 --
462 pd('L' || g_active_segments_tbl(g_total_active_segments));
463
464 -- Bug 3458191
465 g_cached_chart_of_account_id := p_chart_of_accounts_id;
466
467 RETURN (TRUE);
468 --
469 EXCEPTION
470 WHEN OTHERS THEN
471 --
472 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
473 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
474 'Get_Active_Segments' );
475 END if;
476 --
477 RETURN (FALSE);
478 --
479 END Get_Active_Segments;
480 /*---------------------------------------------------------------------------*/
481
482
483
484
485
486 /*===========================================================================+
487 | FUNCTION Make_Account_Assignments (Private) |
488 +===========================================================================*/
489 --
490 -- Private function to get active account codes for a range and put them
491 -- in psb_budget_accounts.
492 --
493
494 FUNCTION Make_Account_Assignments
495 (
496 p_line_sequence_id IN NUMBER ,
497 p_include_or_exclude_type IN VARCHAR2
498 )
499 RETURN BOOLEAN
500 IS
501 -- Local variables
502 i NUMBER;
503 l_cursor_id NUMBER;
504 l_rows_processed NUMBER;
505
506 -- To store dynamic SQL statement.
507 l_sql_insert VARCHAR2(4000);
508 l_sql_delete VARCHAR2(4000);
509 l_sql_tmp VARCHAR2(3000);
510 --
511 BEGIN
512 pd('Make_Account_Assignments Starting');
513
514 IF p_include_or_exclude_type = 'I' THEN
515
516 pd('Building Insert statement');
517
518 --
519 -- Insert the account codes falling in the range.
520 --
521 l_sql_insert := ' INSERT INTO psb_budget_accounts(' ||
522 ' account_position_set_id,' ||
523 ' set_of_books_id,' ||
524 ' code_combination_id,' ||
525 ' last_update_date,' ||
526 ' last_updated_by,' ||
527 ' last_update_login,' ||
528 ' created_by,' ||
529 ' creation_date)' ||
530 ' SELECT :account_set_id,' ||
531 ' :set_of_books_id,' ||
532 ' code_combination_id,' ||
533 ' :last_update_date,' ||
534 ' :last_updated_by,' ||
535 ' :last_update_login,' ||
536 ' :created_by,' ||
537 ' :creation_date';
538 ELSIF p_include_or_exclude_type = 'E' THEN
539
540 pd('Building Delete statement');
541 --
542 -- Delete the account codes falling in the range.
543 --
544 l_sql_delete := ' DELETE psb_budget_accounts' ||
545 ' WHERE account_position_set_id =' ||
546 ' :account_set_id' ||
547 ' AND code_combination_id IN' ||
548 ' ( SELECT code_combination_id';
549
550 END IF;
551 --
552 l_sql_tmp := ' FROM gl_code_combinations glcc,' ||
553 ' psb_account_position_set_lines apsl' ||
554 ' WHERE glcc.code_combination_id >' ||
555 ' :max_code_combination_id' ||
556 ' AND glcc.chart_of_accounts_id = :chart_of_accounts_id' ||
557 ' AND apsl.line_sequence_id = :line_sequence_id';
558
559
560 FOR i in 1..g_total_active_segments
561 LOOP
562 --
563 l_sql_tmp := l_sql_tmp ||
564 ' AND glcc.' || g_active_segments_tbl(i) ||
565 ' BETWEEN apsl.' || g_active_segments_tbl(i) ||
566 '_low AND apsl.' || g_active_segments_tbl(i) ||
567 '_high';
568 END LOOP;
569 --
570 l_sql_tmp := l_sql_tmp ||
571 ' AND glcc.template_id IS NULL' ||
572 ' AND glcc.summary_flag = ''N''';
573
574
575 /* ( SRawat : 30-APR-1998 )
576 Commenting as the follow is not required. The validation will be done
577 at the time worksheet creation.
578 ' AND glcc.enabled_flag = ''Y''' ||
579 ' AND glcc.detail_budgeting_allowed_flag = ''Y''' ||
580 */
581
582 l_cursor_id := dbms_sql.open_cursor;
583
584 IF p_include_or_exclude_type = 'I' THEN
585 --
586 l_sql_insert := l_sql_insert ||
587 l_sql_tmp ||
588 ' AND NOT EXISTS ' ||
589 ' ( SELECT account_position_set_id,' ||
590 ' code_combination_id' ||
591 ' FROM psb_budget_accounts' ||
592 ' WHERE account_position_set_id = :account_set_id' ||
593 ' AND code_combination_id = glcc.code_combination_id )' ;
594
595 -- Parsing the statement.
596 dbms_sql.parse(l_cursor_id, l_sql_insert, dbms_sql.v7);
597
598 -- Binding variables.
602 dbms_sql.bind_variable(l_cursor_id, ':set_of_books_id',
599 dbms_sql.bind_variable(l_cursor_id, ':account_set_id',
600 g_account_set_id);
601 --
603 g_set_of_books_id);
604 --
605 dbms_sql.bind_variable(l_cursor_id, ':last_update_date',
606 g_current_date);
607 --
608 dbms_sql.bind_variable(l_cursor_id, ':last_updated_by',
609 g_current_user_id);
610 --
611 dbms_sql.bind_variable(l_cursor_id, ':last_update_login',
612 g_current_login_id);
613 --
614 dbms_sql.bind_variable(l_cursor_id, ':created_by',
615 g_current_user_id);
616 --
617 dbms_sql.bind_variable(l_cursor_id, ':creation_date',
618 g_current_date);
619 --
620 pd('Max Code = ' || to_char(g_max_code_combination_id));
621 --
622 dbms_sql.bind_variable(l_cursor_id, ':max_code_combination_id',
623 g_max_code_combination_id);
624 --
625 dbms_sql.bind_variable(l_cursor_id, ':chart_of_accounts_id',
626 g_chart_of_accounts_id);
627 --
628 dbms_sql.bind_variable(l_cursor_id, ':line_sequence_id',
629 p_line_sequence_id);
630 ELSE
631 --
632 l_sql_delete := l_sql_delete || l_sql_tmp || ')';
633
634 -- Parsing the statement.
635 dbms_sql.parse(l_cursor_id, l_sql_delete, dbms_sql.v7);
636
637 -- Binding variables.
638 dbms_sql.bind_variable(l_cursor_id, ':account_set_id',
639 g_account_set_id);
640 --
641 dbms_sql.bind_variable(l_cursor_id, ':max_code_combination_id',
642 g_max_code_combination_id);
643 --
644 dbms_sql.bind_variable(l_cursor_id, ':chart_of_accounts_id',
645 g_chart_of_accounts_id);
646 --
647 dbms_sql.bind_variable(l_cursor_id, ':line_sequence_id',
648 p_line_sequence_id);
649 --
650 END IF;
651 --
652 l_rows_processed := dbms_sql.execute(l_cursor_id);
653 --
654
655 /* Used during debugging
656 pd( 'Set = ' || g_account_set_id || ' Line = ' ||
657 p_line_sequence_id || ' Type = ' ||
658 p_include_or_exclude_type || ' Rows = ' ||
659 l_rows_processed );
660 */
661
662 pd( 'Processing Account Set = ' || g_account_set_id );
663 dbms_sql.close_cursor(l_cursor_id);
664
665 pd('Make_Account_Assignments Done (T)');
666 RETURN (TRUE);
667
668 EXCEPTION
669 WHEN OTHERS THEN
670 IF dbms_sql.is_open(l_cursor_id) THEN
671 dbms_sql.close_cursor(l_cursor_id);
672 END IF;
673 --
674 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
675 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
676 'Make_Account_Assignments' );
677 END if;
678 --
679 RETURN (FALSE);
680
681 END Make_Account_Assignments;
682 /*---------------------------------------------------------------------------*/
683
684
685
686 /*===========================================================================+
687 | PROCEDURE Populate_Budget_Accounts_CP |
688 +===========================================================================*/
689 --
690 -- This is the execution file for the concurrent program 'Maintain Budget
691 -- Account Codes'.
692 --
693 PROCEDURE Populate_Budget_Accounts_CP
694 (
695 errbuf OUT NOCOPY VARCHAR2 ,
696 retcode OUT NOCOPY VARCHAR2 ,
697 --
698 p_set_of_books_id IN NUMBER := FND_API.G_MISS_NUM ,
699 p_account_set_id IN NUMBER := FND_API.G_MISS_NUM ,
700 -- bug no 3573740
701 p_full_maintainence_flag IN VARCHAR2 := 'N'
702 )
703 IS
704 --
705 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Budget_Accounts_CP';
706 l_api_version CONSTANT NUMBER := 1.0 ;
707 --
708 l_error_api_name VARCHAR2(2000);
709 l_return_status VARCHAR2(1) ;
710 l_msg_count NUMBER ;
711 l_msg_data VARCHAR2(2000) ;
712 l_msg_index_out NUMBER;
713 --
714 l_set_of_books_name gl_sets_of_books.name%TYPE;
715 l_account_set_name psb_account_position_sets.name%TYPE;
716 --
717 BEGIN
718 --
719 -- SAVEPOINT Populate_Budget_Acct_CP_Pvt ;
720 --
721 IF ( p_set_of_books_id = FND_API.G_MISS_NUM ) OR ( p_set_of_books_id IS NULL)
722 THEN
723 FND_FILE.Put_Line( FND_FILE.OUTPUT,
724 'Set of books Name : ALL');
725 ELSE
726 --
727 SELECT name INTO l_set_of_books_name
728 FROM gl_sets_of_books
729 WHERE set_of_books_id = p_set_of_books_id ;
730 --
731 FND_FILE.Put_Line( FND_FILE.OUTPUT,
732 'Set of books name : ' || l_set_of_books_name );
733 --
734 END IF;
735 --
736
737 --
738 IF ( p_account_set_id = FND_API.G_MISS_NUM ) OR ( p_account_set_id IS NULL)
739 THEN
740 FND_FILE.Put_Line( FND_FILE.OUTPUT, 'Account set name : ALL');
741 ELSE
742 --
743 SELECT name INTO l_account_set_name
744 FROM psb_account_position_sets
745 WHERE account_position_set_id = p_account_set_id ;
749 --
746 --
747 FND_FILE.Put_Line( FND_FILE.OUTPUT,
748 'Account set name : ' || l_account_set_name );
750 END IF;
751 --
752
753 PSB_Budget_Account_PVT.Populate_Budget_Accounts
754 (
755 p_api_version => 1.0 ,
756 p_init_msg_list => FND_API.G_TRUE ,
757 p_commit => FND_API.G_FALSE ,
758 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
759 p_return_status => l_return_status ,
760 p_msg_count => l_msg_count ,
761 p_msg_data => l_msg_data ,
762 p_set_of_books_id => p_set_of_books_id ,
763 p_account_set_id => p_account_set_id ,
764 -- bug no 3573740
765 p_full_maintainence_flag => p_full_maintainence_flag
766 );
767 --
768 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
769 RAISE FND_API.G_EXC_ERROR;
770 END IF;
771 --
772
773 PSB_MESSAGE_S.Print_Success ;
774 retcode := 0 ;
775 --
776 COMMIT WORK;
777 --
778 EXCEPTION
779 --
780 WHEN FND_API.G_EXC_ERROR THEN
781 --
782 -- ROLLBACK TO Populate_Budget_Acct_CP_Pvt ;
783 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
784 p_print_header => FND_API.G_TRUE ) ;
785 retcode := 2 ;
786 --
787 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
788 --
789 -- ROLLBACK TO Populate_Budget_Acct_CP_Pvt ;
790 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
791 p_print_header => FND_API.G_TRUE ) ;
792 retcode := 2 ;
793 --
794 WHEN OTHERS THEN
795 --
796 -- ROLLBACK TO Populate_Budget_Acct_CP_Pvt ;
797 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
798 --
799 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
800 l_api_name ) ;
801 END IF ;
802 --
803 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
804 p_print_header => FND_API.G_TRUE ) ;
805 retcode := 2 ;
806 --
807 END Populate_Budget_Accounts_CP ;
808 /*---------------------------------------------------------------------------*/
809
810
811
812 /*===========================================================================+
813 | PROCEDURE pd (Private) |
814 +===========================================================================*/
815 --
816 -- Private procedure to print debug info. The name is tried to keep as
817 -- short as possible for better documentaion.
818 --
819 PROCEDURE pd
820 (
821 p_message IN VARCHAR2
822 )
823 IS
824 --
825 BEGIN
826
827 IF g_debug_flag = 'Y' THEN
828 NULL;
829 -- DBMS_OUTPUT.Put_Line(p_message) ;
830 END IF;
831
832 END pd ;
833 /*---------------------------------------------------------------------------*/
834
835
836
837 /*===========================================================================+
838 | PROCEDURE Validate_Worksheet_CP |
839 +===========================================================================*/
840
841 -- This is the execution file for the concurrent program 'Validate_Worksheet'
842 -- Created this api for Worksheet Exception report. Bug 3247574
843
844 PROCEDURE Validate_Worksheet_CP
845 (
846 errbuf OUT NOCOPY VARCHAR2,
847 retcode OUT NOCOPY VARCHAR2,
848 p_worksheet_id IN NUMBER
849 ) IS
850
851 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Worksheet_CP';
852 l_api_version CONSTANT NUMBER := 1.0 ;
853 l_return_status VARCHAR2(1);
854 l_msg_count NUMBER;
855 l_msg_data VARCHAR2(2000);
856
857 BEGIN
858
859 PSB_Budget_Account_PVT.Validate_Worksheet
860 (p_api_version => 1.0,
861 p_init_msg_list => FND_API.G_TRUE,
862 p_commit => FND_API.G_FALSE,
863 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
864 p_return_status => l_return_status,
865 p_msg_count => l_msg_count,
866 p_msg_data => l_msg_data,
867 p_worksheet_id => p_worksheet_id,
868 p_msg_wrt_mode => 'OUT');
869
870 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
871 RAISE FND_API.G_EXC_ERROR;
872 END IF;
873
874 PSB_MESSAGE_S.Print_Success;
875 retcode := 0 ;
876 COMMIT WORK;
877
878 EXCEPTION
879
880 WHEN FND_API.G_EXC_ERROR THEN
881
882 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
883 p_print_header => FND_API.G_TRUE );
884 retcode := 2 ;
885 COMMIT WORK ;
886
887 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
888
889 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
890 p_print_header => FND_API.G_TRUE );
891 retcode := 2 ;
892 COMMIT WORK ;
893
894 WHEN OTHERS THEN
895
896 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
897 --
898 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
899 l_api_name ) ;
900 END IF ;
901 --
902 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
903 p_print_header => FND_API.G_TRUE );
904 --
905 retcode := 2 ;
906 COMMIT WORK ;
907 --
908
909 END Validate_Worksheet_CP;
910 /*---------------------------------------------------------------------------*/
911
912
913
914 /*===========================================================================+
915 | PROCEDURE Validate_Worksheet |
916 +===========================================================================*/
917
918 -- This procedure calls all the account and position validation apis
919 -- Created this api for Worksheet Exception report. Bug 3247574
920
921
922 Procedure Validate_Worksheet (
923 p_api_version IN NUMBER,
924 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
925 p_commit IN VARCHAR2 := fnd_api.g_false,
926 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
927 p_return_status OUT NOCOPY VARCHAR2,
928 p_msg_count OUT NOCOPY NUMBER,
929 p_msg_data OUT NOCOPY VARCHAR2,
930 p_worksheet_id IN NUMBER,
931 p_msg_wrt_mode IN VARCHAR2)
932 Is
933
934 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Worksheet';
935 l_api_version CONSTANT NUMBER := 1.0;
936 l_root_budget_group_id NUMBER;
937 l_return_status VARCHAR2(1);
938 l_return_status2 VARCHAR2(1);
939 l_account_set_id NUMBER;
940 l_msg_count NUMBER;
941 l_msg_data VARCHAR2(2000) ;
942 l_validation_status VARCHAR2(1);
943 l_rep_req_id NUMBER;
944 l_reqid NUMBER;
945 l_data_extract_id NUMBER;
946
947 Begin
948
949
950 SAVEPOINT Validate_Worksheet_Pvt;
951
952
953 -- Standard call to check for call compatibility.
954 IF NOT FND_API.Compatible_API_Call (l_api_version,
955 p_api_version,
956 l_api_name,
957 G_PKG_NAME)
958 THEN
959 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
960 END IF;
961
962 -- Initialize message list if p_init_msg_list is set to TRUE
963 IF FND_API.to_Boolean (p_init_msg_list) THEN
964 FND_MSG_PUB.initialize;
965 END IF;
966
967 FOR c_ws_rec in(SELECT NVL(budget_by_position,'N') budget_by_position,
968 data_extract_id
969 FROM psb_worksheets
970 WHERE worksheet_id = p_worksheet_id)
971 LOOP
972
973 l_data_extract_id := c_ws_rec.data_extract_id;
974
975 IF c_ws_rec.budget_by_position = 'Y' THEN
976
977 PSB_Budget_Position_Pvt.Populate_Budget_Positions
978 (
979 p_api_version => 1.0 ,
980 p_init_msg_list => FND_API.G_TRUE ,
981 p_commit => FND_API.G_FALSE ,
982 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
983 p_return_status => l_return_status ,
984 p_msg_count => l_msg_count ,
985 p_msg_data => l_msg_data ,
986 p_data_extract_id => l_data_extract_id
987 );
988
989 FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_LINE');
990 FND_MSG_PUB.ADD;
991 FND_MESSAGE.SET_NAME('PSB', 'PSB_POSITION_VALIDATIONS_DUMMY');
992 FND_MSG_PUB.ADD;
993 FND_MESSAGE.SET_NAME('PSB', 'PSB_VAL_LINE');
994 FND_MSG_PUB.ADD;
995
996 -- position validations
997 PSB_POSITIONS_PVT.Position_WS_Validation
998 (p_api_version => 1.0,
999 p_return_status => l_return_status2,
1000 p_msg_count => l_msg_count,
1001 p_msg_data => l_msg_data,
1002 p_worksheet_id => p_worksheet_id,
1003 p_validation_status => l_validation_status,
1004 p_validation_mode => 'STANDALONE'
1005 );
1006 END IF;
1007
1008 END LOOP;
1009
1010
1011 l_reqid := FND_GLOBAL.CONC_REQUEST_ID;
1012
1013 -- calls the report.
1014 l_rep_req_id := Fnd_Request.Submit_Request
1015 (application => 'PSB',
1016 program => 'PSBRPERR',
1017 description => 'Position Worksheet Exception Report',
1018 start_time => NULL,
1019 sub_request => FALSE,
1020 argument1 => 'POSITION_WORKSHEET_EXCEPTION',
1021 argument2 => p_worksheet_id,
1022 argument3 => l_reqid
1023 );
1024
1025 IF l_rep_req_id = 0 THEN
1026
1027 FND_MESSAGE.SET_NAME('PSB', 'PSB_FAIL_TO_SUBMIT_REQUEST');
1028 FND_MSG_PUB.ADD;
1029 RAISE FND_API.G_EXC_ERROR;
1030
1031 END IF;
1032
1033 EXCEPTION
1034
1035 WHEN FND_API.G_EXC_ERROR THEN
1036 ROLLBACK TO Validate_Worksheet_Pvt;
1037 p_return_status := FND_API.G_RET_STS_ERROR ;
1038 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1039 p_data => p_msg_data);
1040
1041 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1042 ROLLBACK TO Validate_Worksheet_Pvt;
1043 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1044 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1045 p_data => p_msg_data);
1046
1047 WHEN OTHERS THEN
1048 ROLLBACK TO Validate_Worksheet_Pvt;
1049 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1050
1051 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
1052 p_data => p_msg_data);
1053
1054
1055 END Validate_Worksheet;
1056 /*---------------------------------------------------------------------------*/
1057
1058 END PSB_Budget_Account_PVT;