1 PACKAGE BODY PSB_Budget_Position_Pvt AS
2 /* $Header: PSBVMBPB.pls 120.3 2005/10/14 17:05:06 matthoma ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30 ):= 'PSB_Budget_Position_Pvt';
5
6
7 /*-------------------- Global variables and declarations --------------------*/
8
9 -- The flag determines whether to print debug information or not.
10 g_debug_flag VARCHAR2(1) := 'N' ;
11
12 -- Record type to store a position_set_line and a position.
13 TYPE position_rec_type IS RECORD
14 ( line_sequence_id NUMBER,
15 position_id NUMBER
16 );
17
18 -- Table type to store position_set_lines and positions for a position set.
19 TYPE position_set_tbl_type IS TABLE OF position_rec_type
20 INDEX BY BINARY_INTEGER;
21
22 -- Global Table to store position_set_lines and positions for a position set.
23 l_position_set_tbl position_set_tbl_type ;
24
25 -- Table type to store positions.
26 TYPE position_tbl_type IS TABLE OF NUMBER
27 INDEX BY BINARY_INTEGER;
28
29 -- To store current position set id.
30 g_position_set_id
31 psb_account_position_sets.account_position_set_id%TYPE;
32
33 --
34 -- WHO columns variables
35 --
36 g_current_date DATE := sysdate ;
37 g_current_user_id NUMBER := NVL(Fnd_Global.User_Id , 0) ;
38 g_current_login_id NUMBER := NVL(Fnd_Global.Login_Id, 0) ;
39
40 /*----------------------- End Private variables -----------------------------*/
41
42
43
44 /* ---------------------- Private Routine prototypes -----------------------*/
45
46 PROCEDURE Init;
47 --
48 FUNCTION Populate_Budget_Position_Set
49 (
50 p_position_set_id IN NUMBER ,
51 p_attribute_selection_type IN VARCHAR2
52 )
53 RETURN BOOLEAN ;
54
55 PROCEDURE pd
56 (
57 p_message IN VARCHAR2
58 ) ;
59
60 /* ------------------ End Private Routines prototypes ----------------------*/
61
62
63
64 /*===========================================================================+
65 | PROCEDURE Populate_Budget_Positions |
66 +===========================================================================*/
67 --
68 -- The Public API to maintain positions for position sets.
69 --
70 PROCEDURE Populate_Budget_Positions
71 (
72 p_api_version IN NUMBER ,
73 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
74 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
75 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
76 p_return_status OUT NOCOPY VARCHAR2 ,
77 p_msg_count OUT NOCOPY NUMBER ,
78 p_msg_data OUT NOCOPY VARCHAR2 ,
79 --
80 p_position_set_id IN psb_account_position_sets.account_position_set_id%TYPE
81 := FND_API.G_MISS_NUM ,
82
83 p_data_extract_id IN psb_data_extracts.data_extract_id%TYPE
84 := FND_API.G_MISS_NUM
85 )
86 IS
87 --
88 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Budget_Positions';
89 l_api_version CONSTANT NUMBER := 1.0;
90 --
91 l_attribute_selection_type
92 psb_account_position_sets.attribute_selection_type%TYPE ;
93 l_data_extract_id psb_data_extracts.data_extract_id%TYPE ;
94 --
95 BEGIN
96 --
97 SAVEPOINT Populate_Budget_Positions_Pvt ;
98 --
99 IF NOT FND_API.Compatible_API_Call ( l_api_version,
100 p_api_version,
101 l_api_name,
102 G_PKG_NAME )
103 THEN
104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
105 END IF;
106 --
107
108 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
109 FND_MSG_PUB.initialize ;
110 END IF;
111 --
112 p_return_status := FND_API.G_RET_STS_SUCCESS ;
113 --
114
115 --
116 -- As FND_API.G_MISS_NUM is bigger than NUMBERR(15), now using a local
117 -- variable to fis the bug #655442.
118 --
119 IF p_data_extract_id = FND_API.G_MISS_NUM THEN
120 l_data_extract_id := NULL;
121 ELSE
122 l_data_extract_id := p_data_extract_id ;
123 END IF;
124
125 IF ( p_position_set_id = FND_API.G_MISS_NUM ) OR ( p_position_set_id IS NULL)
126 THEN
127 --
128 -- As no parameter is supplied, we have to populate all the position
129 -- sets in psb_account_position_sets table.
130 --
131 FOR l_set_rec IN
132 (
133 SELECT account_position_set_id ,
134 attribute_selection_type
135 FROM psb_account_position_sets
136 WHERE account_or_position_type = 'P'
137 AND data_extract_id = NVL( l_data_extract_id, data_extract_id )
138 )
139 LOOP
140 --
141 -- Perform initilization. To be done for each position set.
142 --
143 Init;
144
145 --
146 -- Call the Populate_Budget_Position_Set routine for each position set.
147 --
148 IF Populate_Budget_Position_Set
149 (
150 l_set_rec.account_position_set_id ,
151 l_set_rec.attribute_selection_type
152 )
153 THEN
154 --
155 -- The concurrent program is the only one which calls the API
156 -- without any argument. We need to release lock as soon as an
157 -- position set is exploded. Committing will also ensure that
158 -- rollback segments do not go out of bounds.
159 --
160 COMMIT WORK;
161 --
162 -- Re-establish the savepoint after the commit.
163 SAVEPOINT Populate_Budget_Positions_Pvt ;
164 --
165 ELSE
166 --
167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
168 --
169 END IF;
170 --
171 END LOOP;
172 --
173 ELSE
174 --
175 -- Only the passed position set will be populated.
176 -- Perform initilization for this set.
177 --
178 Init;
179
180
181 --
182 -- Find attribute_selection_type for the given set.
183 --
184 SELECT attribute_selection_type INTO l_attribute_selection_type
185 FROM psb_account_position_sets
186 WHERE account_position_set_id = p_position_set_id ;
187
188 --
189 -- Call Populate_Budget_Position_Set only for the given position set.
190 --
191 IF NOT Populate_Budget_Position_Set
192 (
193 p_position_set_id ,
194 l_attribute_selection_type
195 )
196 THEN
197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
198 END IF;
199 END IF;
200
201 --
202 IF FND_API.To_Boolean ( p_commit ) THEN
203 COMMIT WORK;
204 END IF;
205 --
206 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
207 p_data => p_msg_data );
208 --
209 EXCEPTION
210 --
211 WHEN FND_API.G_EXC_ERROR THEN
212 --
213 ROLLBACK TO Populate_Budget_Positions_Pvt ;
214 p_return_status := FND_API.G_RET_STS_ERROR;
215 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
216 p_data => p_msg_data );
217 --
218 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
219 --
220 ROLLBACK TO Populate_Budget_Positions_Pvt ;
221 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
222 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
223 p_data => p_msg_data );
224 --
225 WHEN OTHERS THEN
226 --
227 ROLLBACK TO Populate_Budget_Positions_Pvt ;
228 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
229 --
230 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
231 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
232 l_api_name);
233 END if;
234 --
235 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
236 p_data => p_msg_data );
237 --
238 END Populate_Budget_Positions ;
239 /*---------------------------------------------------------------------------*/
240
241
242
243 /*===========================================================================+
244 | PROCEDURE Add_Position_To_Position_Sets |
245 +===========================================================================*/
246 --
247 -- This API finds all the position sets a position belongs to, and the adds
248 -- this information in psb_budget_positions table.
249 --
250 PROCEDURE Add_Position_To_Position_Sets
251 (
252 p_api_version IN NUMBER ,
253 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
254 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
255 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL ,
256 p_return_status OUT NOCOPY VARCHAR2 ,
257 p_msg_count OUT NOCOPY NUMBER ,
258 p_msg_data OUT NOCOPY VARCHAR2 ,
259 --
260 p_position_id IN psb_positions.position_id%TYPE,
261 p_worksheet_id IN NUMBER
262 )
263 IS
264 --
265 l_api_name CONSTANT VARCHAR2(30):='Add_Position_To_Position_Sets';
266 l_api_version CONSTANT NUMBER := 1.0;
267 --
268 l_data_extract_id psb_positions.data_extract_id%TYPE ;
269 l_business_group_id psb_positions.data_extract_id%TYPE ;
270 l_match_found_in_set_flag VARCHAR2(1) ;
271 l_matching_attributes_count NUMBER ;
272 --
273 BEGIN
274 --
275 SAVEPOINT Add_Position_To_Pos_Sets_Pvt ;
276 --
277 IF NOT FND_API.Compatible_API_Call ( l_api_version,
278 p_api_version,
279 l_api_name,
280 G_PKG_NAME )
281 THEN
282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
283 END IF;
284 --
285
286 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
287 FND_MSG_PUB.initialize ;
288 END IF;
289 --
290
291 p_return_status := FND_API.G_RET_STS_SUCCESS ;
292
293 --
294 -- Find information about the position.
295 --
296 SELECT data_extract_id ,
297 business_group_id
298 INTO
299 l_data_extract_id ,
300 l_business_group_id
301 FROM psb_positions
302 WHERE position_id = p_position_id ;
303
304 -- Scan all the position sets to check which one the position belongs to.
305 FOR l_set_rec IN
306 (
307 SELECT account_position_set_id ,
308 attribute_selection_type
309 FROM psb_account_position_sets
310 WHERE account_or_position_type = 'P'
311 AND data_extract_id = l_data_extract_id
312 )
313 LOOP
314
315 pd('The current set ' || l_set_rec.account_position_set_id );
316
317 --
318 -- Re-intilialize the flag for each set.
319 -- ( Based on the set type and the processing logic )
320 --
321 IF l_set_rec.attribute_selection_type = 'A' THEN
322
323 l_match_found_in_set_flag := 'Y' ;
324
325 ELSIF l_set_rec.attribute_selection_type = 'O' THEN
326
327 l_match_found_in_set_flag := 'N' ;
328
329 END IF;
330
331 -- Get all the set lines for the current set. We have to evaluate each
332 -- set line and their associated values for the attribute_selection_type.
333 FOR l_set_lines_rec IN
334 (
335 SELECT line_sequence_id, attribute_id
336 FROM psb_account_position_set_lines
337 WHERE account_position_set_id = l_set_rec.account_position_set_id
338 )
339 LOOP
340
341 pd('Attribute id ' || l_set_lines_rec.attribute_id );
342
343 -- Check whether the position belongs to the current set.
344 SELECT COUNT(*) INTO l_matching_attributes_count
345 FROM psb_position_assignments asgn ,
346 psb_position_set_line_values vals
347 WHERE vals.line_sequence_id = l_set_lines_rec.line_sequence_id
348 AND asgn.position_id = p_position_id
349 AND asgn.attribute_id = l_set_lines_rec.attribute_id
350 AND (
351 asgn.attribute_value_id = vals.attribute_value_id
352 OR
353 asgn.attribute_value = vals.attribute_value
354 )
355 /* Bug 4545909 Start */
356 AND ((worksheet_id IS NULL AND NOT EXISTS
357 (SELECT 1 FROM psb_position_assignments
358 WHERE worksheet_id = p_worksheet_id
359 AND attribute_id = asgn.attribute_id
360 AND position_id = asgn.position_id))
361 OR worksheet_id = p_worksheet_id
362 OR (worksheet_id IS NULL AND p_worksheet_id IS NULL))
363 AND ROWNUM < 2 ;
364 /* Bug 4545909 End */
365
366 IF l_set_rec.attribute_selection_type = 'A' THEN
367
368 IF l_matching_attributes_count = 0 THEN
369
370 -- The position does not belong to the current position set.
371 -- ( The position needs to belong to every set line. )
372 l_match_found_in_set_flag := 'N';
373 EXIT;
374 END IF;
375
376 ELSIF l_set_rec.attribute_selection_type = 'O' THEN
377
378 IF l_matching_attributes_count > 0 THEN
379
383 EXIT;
380 -- The position belongs to the current position set.
381 -- ( The position needs to belong to any set line. )
382 l_match_found_in_set_flag := 'Y';
384
385 END IF;
386
387 END IF;
388
389 END LOOP; -- End processing the current account set.
390
391 pd('Found flag ' || l_match_found_in_set_flag );
392
393 -- If match found, put the information in psb_budget_positions table.
394 IF l_match_found_in_set_flag = 'Y' THEN
395 INSERT INTO psb_budget_positions
396 (
397 account_position_set_id ,
398 position_id ,
399 data_extract_id ,
400 business_group_id ,
401 last_update_date ,
402 last_update_login ,
403 last_updated_by ,
404 created_by ,
405 creation_date
406 )
407 SELECT l_set_rec.account_position_set_id ,
408 p_position_id ,
409 l_data_extract_id ,
410 l_business_group_id ,
411 g_current_date ,
412 g_current_login_id ,
413 g_current_user_id ,
414 g_current_user_id ,
415 g_current_date
416 FROM dual
417 WHERE NOT EXISTS
418 ( SELECT '1'
419 FROM psb_budget_positions
420 WHERE account_position_set_id
421 = l_set_rec.account_position_set_id
422 AND position_id = p_position_id
423 ) ;
424 --
425 END IF;
426
427 END LOOP; -- End processing all the sets.
428
429
430 --
431 IF FND_API.To_Boolean ( p_commit ) THEN
432 COMMIT WORK;
433 END IF;
434 --
435 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
436 p_data => p_msg_data );
437 --
438 EXCEPTION
439 --
440 WHEN FND_API.G_EXC_ERROR THEN
441 --
442 ROLLBACK TO Add_Position_To_Pos_Sets_Pvt ;
443 p_return_status := FND_API.G_RET_STS_ERROR;
444 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
445 p_data => p_msg_data );
446 --
447 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
448 --
449 ROLLBACK TO Add_Position_To_Pos_Sets_Pvt ;
450 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
451 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
452 p_data => p_msg_data );
453 --
454 WHEN OTHERS THEN
455 --
456 ROLLBACK TO Add_Position_To_Pos_Sets_Pvt ;
457 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
458 --
459 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
460 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
461 l_api_name);
462 END if;
463 --
464 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
465 p_data => p_msg_data );
466 --
467 END Add_Position_To_Position_Sets ;
468 /*---------------------------------------------------------------------------*/
469
470
471
472 /*===========================================================================+
473 | PROCEDURE Init (Private) |
474 +===========================================================================*/
475 --
476 -- Private procedure to perform variable initilization.
477 --
478 PROCEDURE Init
479 IS
480 --
481 BEGIN
482 --
483 -- Re-initialize dates as the concurrent program may be run for days.
484 --
485 g_current_date := sysdate ;
486
487 END Init;
488 /*---------------------------------------------------------------------------*/
489
490
491
492 /*===========================================================================+
493 | FUNCTION Populate_Budget_Position_Set (Private) |
494 +===========================================================================*/
495 --
496 -- This Private function is to populate a given position set.
497 --
498 FUNCTION Populate_Budget_Position_Set
499 (
500 p_position_set_id IN NUMBER ,
501 p_attribute_selection_type IN VARCHAR2
502 )
503 RETURN BOOLEAN
504 --
505 IS
506
507 -- Table to store position_set_lines and positions for a position set.
508 l_position_set_tbl position_set_tbl_type ;
509 --
510 l_data_extract_id NUMBER ;
511 l_business_group_id NUMBER ;
512 --
513 l_attribute_value_id NUMBER ;
514 l_attribute_value VARCHAR2(2000) ;
515 --
516 l_first_line_sequence_id NUMBER ;
517 l_second_set_line_index NUMBER ;
518 l_position_set_tbl_index NUMBER ;
519 --
520 l_position_input_tbl position_tbl_type ;
521 l_input_tbl_index NUMBER ;
522 l_position_output_tbl position_tbl_type ;
523 l_output_tbl_index NUMBER ;
524 --
525 l_position_exists_in_line_flag VARCHAR2(1);
529 l_position_set_index NUMBER ;
526 l_position_exists_in_set_flag VARCHAR2(1);
527 l_current_position_id NUMBER ;
528 l_current_set_line_id NUMBER ;
530 l_tmp_index NUMBER ;
531 l_count_set_line_positions NUMBER ;
532 l_tmp_count NUMBER ;
533 --
534 l_last_maintained_date DATE;
535 l_last_update_date DATE;
536 --
537 BEGIN
538
539 pd('The current set ' || p_position_set_id);
540
541
542 -- Populate the global variable.
543 g_position_set_id := p_position_set_id;
544
545 -- Get various information for the position set.
546 SELECT data_extract_id ,
547 business_group_id
548 INTO
549 l_data_extract_id ,
550 l_business_group_id
551 FROM psb_account_position_sets
552 WHERE account_position_set_id = p_position_set_id ;
553
554 --
555 -- Lock psb_account_position_sets table to prevent modifications.
556 -- Also set maintain_status to 'C' so that database trigger wont fire.
557 --
558 UPDATE psb_account_position_sets
559 SET maintain_status = 'C'
560 WHERE account_position_set_id = p_position_set_id ;
561
562 --
563 -- Delete from psb_budget_positions. You must delete as everytime you run
564 -- the program, some positions may have been created, modified or deleted.
565 --
566 DELETE psb_budget_positions
567 WHERE account_position_set_id = p_position_set_id ;
568
569
570 -- Reset the table.
571 l_position_set_tbl_index := 0 ;
572 l_position_set_tbl.DELETE ;
573
574 pd('DE ' || l_data_extract_id );
575
576 --
577 -- Get set_lines info for the given position set.
578 --
579 --
580 FOR l_set_line_rec IN
581 (
582 SELECT line_sequence_id ,
583 attribute_id ,
584 attribute_value_table_flag
585 FROM psb_acct_position_set_lines_v lines
586 WHERE account_position_set_id = p_position_set_id
587 ORDER BY lines.line_sequence_id
588 )
589 LOOP
590
591 --
592 -- Reset variable. This variable stores total number of positions in
593 -- the current set_line.
594 --
595 l_count_set_line_positions := 0 ;
596
597 -- Check whether the attribute has been assigned values or not.
598 SELECT COUNT(*) INTO l_tmp_count
599 FROM psb_position_set_line_values
600 WHERE line_sequence_id = l_set_line_rec.line_sequence_id ;
601
602 IF l_tmp_count = 0 THEN
603
604 --
605 -- It means the attribute has not been assigned any values. We will
606 -- consider all the positions which are not associated with the attribute.
607 -- Because it is like the positions are assigned the attribute with a
608 -- null value. ( See details in the enhancement bug#661975.)
609 --
610 pd('Set Line attribute_id : ' || l_set_line_rec.attribute_id) ;
611
612 FOR l_position_rec IN
613 (
614 SELECT position_id
615 FROM psb_positions
616 WHERE data_extract_id = l_data_extract_id
617 MINUS
618 SELECT position_id
619 FROM psb_position_assignments
620 WHERE data_extract_id = l_data_extract_id
621 AND attribute_id = l_set_line_rec.attribute_id
622 )
623 LOOP
624
625 pd('Pos without attr assignment ' || l_position_rec.position_id );
626
627 --
628 l_count_set_line_positions := l_count_set_line_positions + 1 ;
629 --
630 l_position_set_tbl_index := l_position_set_tbl_index + 1 ;
631 --
632 l_position_set_tbl(l_position_set_tbl_index).line_sequence_id :=
633 l_set_line_rec.line_sequence_id ;
634 --
635 l_position_set_tbl(l_position_set_tbl_index).position_id :=
636 l_position_rec.position_id ;
637 --
638 END LOOP;
639 --
640
641 ELSE
642
643 --
644 -- The attribute has been assigned values. We will pick up positions
645 -- having the corresponding assignments.
646 --
647 FOR l_val_rec IN
648 (
649 SELECT attribute_value_id ,
650 attribute_value
651 FROM psb_position_set_line_values vals
652 WHERE line_sequence_id = l_set_line_rec.line_sequence_id
653 )
654 LOOP
655
656 l_attribute_value_id := l_val_rec.attribute_value_id ;
657 l_attribute_value := l_val_rec.attribute_value ;
658
659 pd('Line Val Id ' || l_val_rec.attribute_value_id );
660 pd('Line Val ' || l_val_rec.attribute_value ) ;
661 pd('Att id ' || l_set_line_rec.attribute_id );
662 pd('Line id to put in main table:' || l_set_line_rec.line_sequence_id );
663
664 --
665 -- Find all the matching positions for the attribute values.
666 -- ( Consider only base positions.)
667 --
668 FOR l_position_rec IN
669 (
670 SELECT position_id
671 FROM psb_position_assignments
672 WHERE attribute_id = l_set_line_rec.attribute_id
673 AND data_extract_id = l_data_extract_id
674 AND ( attribute_value_id = l_attribute_value_id
675 OR
679 LOOP
676 attribute_value = l_attribute_value
677 )
678 )
680
681 pd('Pos ' || l_position_rec.position_id );
682
683 --
684 l_count_set_line_positions := l_count_set_line_positions + 1 ;
685 --
686 l_position_set_tbl_index := l_position_set_tbl_index + 1 ;
687 --
688 l_position_set_tbl(l_position_set_tbl_index).line_sequence_id :=
689 l_set_line_rec.line_sequence_id ;
690 --
691 l_position_set_tbl(l_position_set_tbl_index).position_id :=
692 l_position_rec.position_id ;
693 --
694 END LOOP; /* To get matching positions for the current attribute_value
695 or attribute_value_id in the current set_line */
696
697 END LOOP; /* To get attribute values for the current set_line */
698
699 END IF ;
700
701 --
702 -- If attribute selection type is 'A', we select only those positions
703 -- which are present in each set line. The following condition states
704 -- the intersection of positions in all the set_line will be null.
705 --
706 IF l_count_set_line_positions = 0 AND p_attribute_selection_type = 'A'
707 THEN
708
709 pd('Found a set_line with no positions');
710
711 RETURN (TRUE);
712
713 END IF ;
714
715 END LOOP ; /* To get all the set_lines for the position set */
716
717
718 pd('---------------------');
719 pd('Sel Type ' || p_attribute_selection_type );
720
721
722 --
723 -- Process the l_position_set_tbl table as per the Attribute Selection
724 -- method defined for the set.
725 --
726
727 IF p_attribute_selection_type = 'O' THEN
728 --
729 -- The p_attribute_selection_type 'O' means pick up all the positions
730 -- matching at least one criteria. That means take union of positions
731 -- in l_position_set_tbl table.
732 --
733 FOR i IN 1..l_position_set_tbl.COUNT
734 LOOP
735 --
736 INSERT INTO psb_budget_positions
737 (
738 account_position_set_id ,
739 position_id ,
740 data_extract_id ,
741 business_group_id ,
742 last_update_date ,
743 last_update_login ,
744 last_updated_by ,
745 created_by ,
746 creation_date
747 )
748 SELECT g_position_set_id ,
749 l_position_set_tbl(i).position_id ,
750 l_data_extract_id ,
751 l_business_group_id ,
752 g_current_date ,
753 g_current_login_id ,
754 g_current_user_id ,
755 g_current_user_id ,
756 g_current_date
757 FROM dual
758 WHERE NOT EXISTS
759 ( SELECT '1'
760 FROM psb_budget_positions
761 WHERE account_position_set_id = g_position_set_id
762 AND position_id =
763 l_position_set_tbl(i).position_id
764 ) ;
765 --
766 END LOOP;
767 --
768 ELSIF p_attribute_selection_type = 'A' THEN
769 --
770 -- The p_attribute_selection_type 'A' means pick up only those positions
771 -- matching all the criteria. That means take intersection of positions
772 -- in l_position_set_tbl table with respect to a set line.
773 --
774
775 --
776 -- Find all the positions in the first set_line.
777 --
778
779 IF l_position_set_tbl.EXISTS(1) THEN
780 l_first_line_sequence_id := l_position_set_tbl(1).line_sequence_id ;
781 ELSE
782 -- No set lines found. No assignments can be made.
783 RETURN (TRUE) ;
784 END IF;
785
786 -- Reset table which stores all the positions found in l_position_set_tbl.
787 l_input_tbl_index := 0 ;
788 l_position_input_tbl.DELETE ;
789
790 --
791 -- Finding all the positions in the very first set_line ( To implement
792 -- intersection of positions in all the set_lines. )
793 --
794 FOR i IN 1..l_position_set_tbl.COUNT
795 LOOP
796 --
797 IF l_position_set_tbl(i).line_sequence_id <> l_first_line_sequence_id
798 THEN
799 EXIT;
800 ELSE
801 l_input_tbl_index := l_input_tbl_index + 1 ;
802 l_position_input_tbl(i) := l_position_set_tbl(i).position_id ;
803
804 pd('Line:' || l_position_set_tbl(i).line_sequence_id ||
805 ' Pos:' || l_position_input_tbl(i) );
806
807 END IF ;
808 --
809 END LOOP ;
810
811 --
812 -- Set variable which points to second line in l_position_set_tbl table.
813 -- ( It may not exists though and that check it there.
814 --
815 l_second_set_line_index := l_input_tbl_index + 1 ;
816
817 -- Reset table which stores positions found in all the set lines.
818 l_output_tbl_index := 0 ;
819 l_position_output_tbl.DELETE ;
820
821 --
822 -- Process each position in l_input_tbl_index table and find whether
826 FOR i IN 1..l_position_input_tbl.COUNT
823 -- the position is present in each set_line or not. Of course we will
824 -- start from second set_line pointed to by l_position_set_index.
825 --
827 LOOP /* To process all the position in l_position_input_tbl */
828
829 --
830 -- Reset l_position_set_index so that it points to second set line
831 -- in l_position_set_tbl table. We do it for each position.
832 --
833 l_position_set_index := l_second_set_line_index ;
834
835
836 pd('Proc l_position_input_tbl Pos :' || l_position_input_tbl(i));
837
838 -- Store the position being processed.
839 l_current_position_id := l_position_input_tbl(i) ;
840
841 -- Flag specifies whether the current position exist in all the lines
842 -- ( hence set ) or not.
843 l_position_exists_in_set_flag := 'Y' ;
844
845 LOOP /* Process all the set_lines to find the current position */
846
847 IF l_position_set_tbl.EXISTS(l_position_set_index) THEN
848
849 -- l_current_set_line_id stores the current set_line.
850 l_current_set_line_id :=
851 l_position_set_tbl(l_position_set_index).line_sequence_id;
852
853 pd('Current Line :' || l_current_set_line_id );
854
855 -- Flag specifies whether the current position exists in the current
856 -- set_line or not.
857 l_position_exists_in_line_flag := 'N' ;
858
859 -- Process all the positions coming under the current set_line.
860 FOR j IN l_position_set_index..l_position_set_tbl.COUNT
861 LOOP
862
863 IF l_position_set_tbl(j).line_sequence_id <>
864 l_current_set_line_id
865 THEN
866 --
867 -- It means we all the positions in the current set_line
868 -- have been processed. Update l_position_set_index and exit.
869 --
870 l_position_set_index := j + 1 ;
871 EXIT ;
872 END IF ;
873
874 pd('Line:' || l_position_set_tbl(j).line_sequence_id||
875 ' Pos:' || l_position_set_tbl(j).position_id );
876
877 IF l_position_set_tbl(j).position_id = l_current_position_id THEN
878
879 -- We have found the current position in the current set_line.
880 l_position_exists_in_line_flag := 'Y' ;
881
882 --
883 -- First forward l_position_set_index so that it points to the
884 -- next set_line and then exit the loop.
885 --
886
887 -- Store the current value of the l_position_set_index.
888 l_tmp_index := j ;
889
890 WHILE l_position_set_tbl.EXISTS(l_tmp_index)
891 LOOP
892 --
893 IF l_position_set_tbl(l_tmp_index).line_sequence_id <>
894 l_current_set_line_id
895 THEN
896 EXIT ;
897 ELSE
898 l_tmp_index := l_tmp_index + 1 ;
899 END IF ;
900 --
901 END LOOP; /* To forward l_position_set_index to next set_line */
902
903 -- Set l_position_set_index index for l_position_set_tbl.
904 l_position_set_index := l_tmp_index ;
905
906 -- Exit the loop now.
907 EXIT ;
908
909 END IF ;
910 --
911 END LOOP; /* To process positions in the current set_line */
912
913 pd('line flag:' || l_position_exists_in_line_flag);
914
915 --
916 -- If the current position is not found in the current set_line,
917 -- do not process rest of the lines as the current position need
918 -- to be there in every single set_line. Just exit the loop.
919 --
920 IF l_position_exists_in_line_flag <> 'Y' THEN
921 l_position_exists_in_set_flag := 'N' ;
922 EXIT ;
923 END IF ;
924 --
925 ELSE
926
927 -- All the set_lines have been processed, so exit.
928 EXIT ;
929
930 END IF ; /* End of EXISTS clause for set_lines. */
931 --
932 END LOOP ; /* To process all the set_lines for the current position */
933
934 --
935 -- Make assignment if the current position was found in all set_lines.
936 --
937 IF l_position_exists_in_set_flag = 'Y' THEN
938 --
939 l_output_tbl_index := l_output_tbl_index + 1 ;
940 l_position_output_tbl(l_output_tbl_index) := l_current_position_id ;
941 --
942
943 pd('insert output tbl Pos:'||l_current_position_id);
944
945 END IF ;
946
947 END LOOP ; /* To process all the position in l_position_input_tbl */
948
949 --
950 -- Insert positions into psb_budget_positions from l_position_output_tbl.
951 --
952 FOR i in 1..l_position_output_tbl.COUNT
953 LOOP
954 --
955 INSERT INTO psb_budget_positions
956 (
957 account_position_set_id ,
958 position_id ,
959 data_extract_id ,
960 business_group_id ,
961 last_update_date ,
962 last_update_login ,
963 last_updated_by ,
964 created_by ,
965 creation_date
966 )
967 SELECT g_position_set_id ,
971 g_current_date ,
968 l_position_output_tbl(i) ,
969 l_data_extract_id ,
970 l_business_group_id ,
972 g_current_login_id ,
973 g_current_user_id ,
974 g_current_user_id ,
975 g_current_date
976 FROM dual
977 WHERE NOT EXISTS
978 ( SELECT '1'
979 FROM psb_budget_positions
980 WHERE account_position_set_id = g_position_set_id
981 AND position_id =
982 l_position_output_tbl(i)
983 ) ;
984 --
985 END LOOP;
986 --
987 END IF; /* End of p_attribute_selection_type clause */
988
989 --
990 -- Update last_maintained_date column. Set maintain_status to 'C'
991 -- ( meaning updated from PSBVMBAB module) so now the database trigger
992 -- will not fire.
993 --
994 UPDATE psb_account_position_sets
995 SET maintain_status = 'C' ,
996 last_maintained_date = g_current_date
997 WHERE account_position_set_id = p_position_set_id;
998 --
999 RETURN (TRUE);
1000
1001 EXCEPTION
1002 WHEN OTHERS THEN
1003 --
1004 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1005 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1006 'Populate_Budget_Position_Set' );
1007 END if;
1008 --
1009 RETURN (FALSE);
1010 --
1011 END Populate_Budget_Position_Set ;
1012 /*---------------------------------------------------------------------------*/
1013
1014
1015
1016 /*===========================================================================+
1017 | PROCEDURE Populate_Budget_Positions_CP |
1018 +===========================================================================*/
1019 --
1020 -- This is the execution file for the concurrent program 'Maintain Budget
1021 -- Account Codes'.
1022 --
1023 PROCEDURE Populate_Budget_Positions_CP
1024 (
1025 errbuf OUT NOCOPY VARCHAR2 ,
1026 retcode OUT NOCOPY VARCHAR2 ,
1027 --
1028 p_data_extract_id IN NUMBER := FND_API.G_MISS_NUM ,
1029 p_position_set_id IN NUMBER := FND_API.G_MISS_NUM
1030 )
1031 IS
1032 --
1033 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Budget_Positions_CP' ;
1034 l_api_version CONSTANT NUMBER := 1.0 ;
1035 --
1036 l_return_status VARCHAR2(1) ;
1037 l_msg_count NUMBER ;
1038 l_msg_data VARCHAR2(2000) ;
1039 --
1040 BEGIN
1041 --
1042 SAVEPOINT Populate_Budget_Pos_CP_Pvt ;
1043 --
1044 IF ( p_data_extract_id = FND_API.G_MISS_NUM OR p_data_extract_id IS NULL )
1045 THEN
1046 FND_FILE.Put_Line( FND_FILE.OUTPUT, 'Processing all the position sets.');
1047 ELSE
1048 --
1049 IF ( p_position_set_id = FND_API.G_MISS_NUM OR p_position_set_id IS NULL )
1050 THEN
1051 --
1052 FND_FILE.Put_Line( FND_FILE.OUTPUT,
1053 'Processing position sets for data extract id : ' ||
1054 p_data_extract_id );
1055 --
1056 ELSE
1057 --
1058 FND_FILE.Put_Line( FND_FILE.OUTPUT,
1059 'Processing the given position set id : ' ||
1060 p_position_set_id );
1061 --
1062 END IF;
1063 --
1064 END IF;
1065
1066 --
1067 PSB_Budget_Position_Pvt.Populate_Budget_Positions
1068 (
1069 p_api_version => 1.0 ,
1070 p_init_msg_list => FND_API.G_TRUE ,
1071 p_commit => FND_API.G_FALSE ,
1072 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
1073 p_return_status => l_return_status ,
1074 p_msg_count => l_msg_count ,
1075 p_msg_data => l_msg_data ,
1076 p_data_extract_id => p_data_extract_id ,
1077 p_position_set_id => p_position_set_id
1078 );
1079
1080 --
1081 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1082 RAISE FND_API.G_EXC_ERROR;
1083 END IF;
1084 --
1085
1086 --
1087 /* Start Bug No. 2322856 */
1088 -- PSB_MESSAGE_S.Print_Success ;
1089 /* End Bug No. 2322856 */
1090 retcode := 0 ;
1091 --
1092 COMMIT WORK;
1093 --
1094 EXCEPTION
1095 --
1096 WHEN FND_API.G_EXC_ERROR THEN
1097 --
1098 ROLLBACK TO Populate_Budget_Pos_CP_Pvt ;
1099 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1100 p_print_header => FND_API.G_TRUE ) ;
1101 retcode := 2 ;
1102 --
1103 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1104 --
1105 ROLLBACK TO Populate_Budget_Pos_CP_Pvt ;
1106 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1107 p_print_header => FND_API.G_TRUE ) ;
1108 retcode := 2 ;
1109 --
1110 WHEN OTHERS THEN
1111 --
1112 ROLLBACK TO Populate_Budget_Pos_CP_Pvt ;
1113 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1114 --
1115 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
1116 l_api_name ) ;
1117 END IF ;
1118 --
1119 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
1120 p_print_header => FND_API.G_TRUE ) ;
1121 retcode := 2 ;
1122 --
1123 END Populate_Budget_Positions_CP ;
1124 /*---------------------------------------------------------------------------*/
1125
1126
1127
1128 /*===========================================================================+
1129 | PROCEDURE pd (Private) |
1130 +===========================================================================*/
1131 --
1132 -- Private procedure to print debug info. The name is tried to keep as
1133 -- short as possible for better documentaion.
1134 --
1135 PROCEDURE pd
1136 (
1137 p_message IN VARCHAR2
1138 )
1139 IS
1140 --
1141 BEGIN
1142
1143 IF g_debug_flag = 'Y' THEN
1144 NULL;
1145 -- dbms_output.put_line(p_message) ;
1146 END IF;
1147
1148 END pd ;
1149 /*---------------------------------------------------------------------------*/
1150
1151
1152 END PSB_Budget_Position_Pvt;
[Home] [Help]