[Home] [Help]
PACKAGE BODY: APPS.PSB_ACCOUNT_POSITION_SET_PVT
Source
1 PACKAGE BODY PSB_Account_Position_Set_Pvt AS
2 /* $Header: PSBVSETB.pls 115.10 2002/11/12 11:18:13 msuram ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_Account_Position_Set_Pvt';
5
6 -- The flag determines whether to print debug information or not.
7 g_debug_flag VARCHAR2(1) := 'N' ;
8
9
10 /* ---------------------- Private Routine prototypes -----------------------*/
11
12 PROCEDURE pd
13 (
14 p_message IN VARCHAR2
15 ) ;
16
17 /* ------------------ End Private Routines prototypes ----------------------*/
18
19
20
21 /*=======================================================================+
22 | PROCEDURE Insert_Row |
23 +=======================================================================*/
24 PROCEDURE Insert_Row
25 (
26 p_api_version IN NUMBER,
27 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
28 p_commit IN VARCHAR2 := FND_API.G_FALSE,
29 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
30 p_return_status OUT NOCOPY VARCHAR2,
31 p_msg_count OUT NOCOPY NUMBER,
32 p_msg_data OUT NOCOPY VARCHAR2,
33 --
34 p_row_id IN OUT NOCOPY VARCHAR2,
35 p_account_position_set_id IN OUT NOCOPY NUMBER,
36 p_name IN VARCHAR2,
37 p_set_of_books_id IN NUMBER,
38 p_use_in_budget_group_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
39 p_data_extract_id IN NUMBER,
40 p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
41 p_global_or_local_type IN VARCHAR2,
42 p_account_or_position_type IN VARCHAR2,
43 p_attribute_selection_type IN VARCHAR2,
44 p_business_group_id IN NUMBER,
45 p_last_update_date IN DATE,
46 p_last_updated_by IN NUMBER,
47 p_last_update_login IN NUMBER,
48 p_created_by IN NUMBER,
49 p_creation_date IN DATE
50 )
51 IS
52 --
53 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
54 l_api_version CONSTANT NUMBER := 1.0;
55 --
56 CURSOR C IS
57 SELECT rowid
58 FROM psb_account_position_sets
59 WHERE account_position_set_id = p_account_position_set_id;
60
61 CURSOR C2 IS
62 SELECT psb_account_position_sets_s.nextval
63 FROM dual;
64 BEGIN
65 --
66 SAVEPOINT Insert_Row_Pvt ;
67 --
68 IF NOT FND_API.Compatible_API_Call ( l_api_version,
69 p_api_version,
70 l_api_name,
71 G_PKG_NAME )
72 THEN
73 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
74 END IF;
75 --
76
77 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
78 FND_MSG_PUB.initialize ;
79 END IF;
80 --
81 p_return_status := FND_API.G_RET_STS_SUCCESS ;
82 --
83
84 IF (p_account_position_set_id is NULL) THEN
85 OPEN C2;
86
87 FETCH C2 INTO p_account_position_set_id;
88 CLOSE C2;
89 END IF;
90
91 INSERT INTO psb_account_position_sets(
92 account_position_set_id,
93 name,
94 set_of_books_id,
95 use_in_budget_group_flag,
96 data_extract_id,
97 budget_group_id,
98 global_or_local_type,
99 account_or_position_type,
100 attribute_selection_type,
101 business_group_id,
102 last_update_date,
103 last_updated_by,
104 last_update_login,
105 created_by,
106 creation_date )
107 VALUES
108 (
109 p_account_position_set_id,
110 p_name,
111 p_set_of_books_id,
112 DECODE(p_use_in_budget_group_flag,
113 FND_API.G_MISS_CHAR, NULL,
114 p_use_in_budget_group_flag),
115 p_data_extract_id,
116 DECODE(p_budget_group_id,FND_API.G_MISS_NUM,null,p_budget_group_id),
117 p_global_or_local_Type,
118 p_account_or_position_Type,
119 p_attribute_selection_Type,
120 p_business_group_Id,
121 p_last_update_date,
122 p_last_updated_by,
123 p_last_update_login,
124 p_created_by,
125 p_creation_date
126 );
127 OPEN C;
128 FETCH C INTO p_row_id;
129 IF (C%NOTFOUND) THEN
130 CLOSE C;
131 RAISE FND_API.G_EXC_ERROR ;
132 END IF;
133 CLOSE C;
134 --
135
136 --
137 IF FND_API.To_Boolean ( p_commit ) THEN
138 COMMIT WORK;
139 END iF;
140 --
141 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
142 p_data => p_msg_data );
143 --
144 EXCEPTION
145 --
146 WHEN FND_API.G_EXC_ERROR THEN
147 --
148 ROLLBACK TO Insert_Row_Pvt ;
149 p_return_status := FND_API.G_RET_STS_ERROR;
150 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
151 p_data => p_msg_data );
152 --
153 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
154 --
155 ROLLBACK TO Insert_Row_Pvt ;
156 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
157 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
158 p_data => p_msg_data );
159 --
160 WHEN OTHERS THEN
161 --
162 ROLLBACK TO Insert_Row_Pvt ;
163 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
164 --
165 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
166 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
167 l_api_name);
168 END if;
169 --
170 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
171 p_data => p_msg_data );
172 --
173 END Insert_Row;
174 /*-------------------------------------------------------------------------*/
175
176
177
178 /*==========================================================================+
179 | PROCEDURE Lock_Row |
180 +==========================================================================*/
181 PROCEDURE Lock_Row
182 (
183 p_api_version IN NUMBER,
184 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
185 p_commit IN VARCHAR2 := FND_API.G_FALSE,
186 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
187 p_return_status OUT NOCOPY VARCHAR2,
188 p_msg_count OUT NOCOPY NUMBER,
189 p_msg_data OUT NOCOPY VARCHAR2,
190 --
191 p_row_id IN VARCHAR2,
192 p_account_position_set_id IN NUMBER,
193 p_name IN VARCHAR2,
194 p_set_of_books_id IN NUMBER,
195 p_use_in_budget_group_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
196 p_data_extract_id IN NUMBER,
197 p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
198 p_global_or_local_type IN VARCHAR2,
199 p_account_or_position_type IN VARCHAR2,
200 p_attribute_selection_type IN VARCHAR2,
201 p_business_group_id IN NUMBER,
202 --
203 p_row_locked OUT NOCOPY VARCHAR2
204 )
205 IS
206 --
207 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
208 l_api_version CONSTANT NUMBER := 1.0;
209 --
210 Counter NUMBER;
211 CURSOR C IS
212 SELECT *
213 FROM psb_account_position_sets
214 WHERE rowid = p_row_id
215 FOR UPDATE of Account_Position_Set_Id NOWAIT;
216 Recinfo C%ROWTYPE;
217
218 BEGIN
219 --
220 SAVEPOINT Lock_Row_Pvt ;
221 --
222 IF NOT FND_API.Compatible_API_Call ( l_api_version,
223 p_api_version,
224 l_api_name,
225 G_PKG_NAME )
226 THEN
227 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
228 END IF;
229 --
230
231 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
232 FND_MSG_PUB.initialize ;
233 END IF;
234 --
235 p_return_status := FND_API.G_RET_STS_SUCCESS ;
236 p_row_locked := FND_API.G_TRUE ;
237 --
238 OPEN C;
239 --
240 FETCH C INTO Recinfo;
241 IF (C%NOTFOUND) then
242 CLOSE C;
243 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
244 FND_MSG_PUB.Add;
245 RAISE FND_API.G_EXC_ERROR ;
246 END IF;
247 CLOSE C;
248
249 IF
250 (
251 (Recinfo.account_position_set_id = p_account_position_set_id)
252
253 AND ( (Recinfo.name = p_name)
254 OR ( (Recinfo.name IS NULL)
255 AND (p_name IS NULL)))
256
257 AND ( (Recinfo.set_of_books_id = p_set_of_books_id)
258 OR ( (Recinfo.set_of_books_id IS NULL)
259 AND (p_set_of_books_id IS NULL)))
260
261 AND ( (Recinfo.use_in_budget_group_flag = p_use_in_budget_group_flag)
262 OR ( (Recinfo.use_in_budget_group_flag IS NULL)
263 AND (p_use_in_budget_group_flag IS NULL))
264 OR ((Recinfo.use_in_budget_group_flag is NULL)
265 AND (p_use_in_budget_group_flag = FND_API.G_MISS_NUM )))
266
267 AND ( (Recinfo.data_extract_id = p_data_extract_id)
268 OR ( (Recinfo.data_extract_id IS NULL)
269 AND (p_data_extract_id IS NULL)))
270
271 AND ( (Recinfo.budget_group_id = p_budget_group_id)
272 OR ( (Recinfo.budget_group_id IS NULL)
273 AND (p_budget_group_id IS NULL))
274 OR ((Recinfo.budget_group_id is null)
275 AND (p_budget_group_id = FND_API.G_MISS_NUM )))
276
277 AND ( (Recinfo.global_or_local_type = p_global_or_local_type)
278 OR ( (Recinfo.global_or_local_type IS NULL)
279 AND (p_global_or_local_type IS NULL)))
280
281 AND ( (Recinfo.account_or_position_type = p_account_or_position_type)
282 OR ( (Recinfo.account_or_position_type IS NULL)
283 AND (p_account_or_position_type IS NULL)))
284
285 AND ( (Recinfo.attribute_selection_type = p_attribute_selection_type)
286 OR ( (Recinfo.attribute_selection_type IS NULL)
287 AND (p_attribute_selection_type IS NULL)))
288
289 AND ( (Recinfo.business_group_id = p_business_group_id)
290 OR (Recinfo.business_group_id IS NULL)
291 AND (p_business_group_id IS NULL)))
292
293 THEN
294 Null;
295 ELSE
296 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
297 FND_MSG_PUB.Add;
298 RAISE FND_API.G_EXC_ERROR ;
299 END IF;
300
301 --
302 IF FND_API.To_Boolean ( p_commit ) THEN
303 COMMIT WORK;
304 END iF;
305 --
306 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
307 p_data => p_msg_data );
308 --
309 EXCEPTION
310 --
311 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
312 --
313 ROLLBACK TO Lock_Row_Pvt ;
314 p_row_locked := FND_API.G_FALSE;
315 p_return_status := FND_API.G_RET_STS_ERROR;
316 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
317 p_data => p_msg_data );
318 --
319 WHEN FND_API.G_EXC_ERROR THEN
320 --
321 ROLLBACK TO Lock_Row_Pvt ;
322 p_return_status := FND_API.G_RET_STS_ERROR;
323 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
324 p_data => p_msg_data );
325 --
326 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
327 --
328 ROLLBACK TO Lock_Row_Pvt ;
329 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
330 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
331 p_data => p_msg_data );
332 --
333 WHEN OTHERS THEN
334 --
335 ROLLBACK TO Lock_Row_Pvt ;
336 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
337 --
338 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
339 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
340 l_api_name);
341 END if;
342 --
343 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
344 p_data => p_msg_data );
345 --
346 END Lock_Row;
347 /* ----------------------------------------------------------------------- */
348
349
350
351 /*==========================================================================+
352 | PROCEDURE Update_Row |
353 +==========================================================================*/
354 PROCEDURE Update_Row
355 (
356 p_api_version IN NUMBER,
357 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
358 p_commit IN VARCHAR2 := FND_API.G_FALSE,
359 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
360 p_return_status OUT NOCOPY VARCHAR2,
361 p_msg_count OUT NOCOPY NUMBER,
362 p_msg_data OUT NOCOPY VARCHAR2,
363 --
364 p_row_id IN VARCHAR2,
365 p_account_position_set_id IN NUMBER,
366 p_name IN VARCHAR2,
367 p_set_of_books_id IN NUMBER,
368 p_use_in_budget_group_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
369 p_data_extract_id IN NUMBER,
370 p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
371 p_global_or_local_type IN VARCHAR2,
372 p_account_or_position_type IN VARCHAR2,
373 p_attribute_selection_type IN VARCHAR2,
374 p_business_group_id IN NUMBER,
375 p_last_update_date IN DATE,
376 p_last_updated_by IN NUMBER,
377 p_last_update_login IN NUMBER
378 )
379 IS
380 --
381 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
382 l_api_version CONSTANT NUMBER := 1.0;
383 --
384 BEGIN
385 --
386 SAVEPOINT Update_Row_Pvt ;
387 --
388 IF NOT FND_API.Compatible_API_Call ( l_api_version,
389 p_api_version,
390 l_api_name,
391 G_PKG_NAME )
392 THEN
393 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
394 END IF;
395 --
396
397 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
398 FND_MSG_PUB.initialize ;
399 END IF;
400 --
401 p_return_status := FND_API.G_RET_STS_SUCCESS ;
402 --
403
404 UPDATE psb_account_position_sets
405 SET
406 account_position_set_id = p_account_position_set_id,
407 name = p_name,
408 set_of_books_id = p_set_of_books_id,
409 use_in_budget_group_flag = DECODE( p_use_in_budget_group_flag,
410 FND_API.G_MISS_CHAR, NULL,
411 p_use_in_budget_group_flag),
412 data_extract_id = p_data_extract_id,
413 budget_group_id = DECODE( p_budget_group_id,
414 FND_API.G_MISS_NUM,null,
415 p_budget_group_id),
419 business_group_id = p_business_group_id,
416 global_or_local_type = p_global_or_local_type,
417 account_or_position_type = p_account_or_position_type,
418 attribute_selection_type = p_attribute_selection_type,
420 last_update_date = p_last_update_date,
421 last_updated_by = p_last_updated_by,
422 last_update_login = p_last_update_login
423 WHERE rowid = p_row_id;
424
425 IF (SQL%NOTFOUND) THEN
426 RAISE NO_DATA_FOUND ;
427 END IF;
428
429 --
430 IF FND_API.To_Boolean ( p_commit ) THEN
431 COMMIT WORK;
432 END iF;
433 --
434 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
435 p_data => p_msg_data );
436 --
437 EXCEPTION
438 --
439 WHEN FND_API.G_EXC_ERROR THEN
440 --
441 ROLLBACK TO Update_Row_Pvt ;
442 p_return_status := FND_API.G_RET_STS_ERROR;
443 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
444 p_data => p_msg_data );
445 --
446 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
447 --
448 ROLLBACK TO Update_Row_Pvt ;
449 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
450 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
451 p_data => p_msg_data );
452 --
453 WHEN OTHERS THEN
454 --
455 ROLLBACK TO Update_Row_Pvt ;
456 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
457 --
458 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
459 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
460 l_api_name);
461 END if;
462 --
463 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
464 p_data => p_msg_data );
465 --
466 END Update_Row;
467 /* ----------------------------------------------------------------------- */
468
469
470
471 /*==========================================================================+
472 | PROCEDURE Delete_Row |
473 +==========================================================================*/
474 PROCEDURE Delete_Row
475 (
476 p_api_version IN NUMBER,
477 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
478 p_commit IN VARCHAR2 := FND_API.G_FALSE,
479 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
480 p_return_status OUT NOCOPY VARCHAR2,
481 p_msg_count OUT NOCOPY NUMBER,
482 p_msg_data OUT NOCOPY VARCHAR2,
483 --
484 p_row_id IN VARCHAR2
485 )
486 IS
487 --
488 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
489 l_api_version CONSTANT NUMBER := 1.0;
490 --
491 l_return_status VARCHAR2(1) ;
492 l_msg_count NUMBER ;
493 l_msg_data VARCHAR2(2000) ;
494 --
495 l_account_position_set_id
496 psb_account_position_set_lines.account_position_set_id%TYPE;
497 --
498 BEGIN
499 --
500 SAVEPOINT Delete_Row_Pvt ;
501 --
502 IF NOT FND_API.Compatible_API_Call ( l_api_version,
503 p_api_version,
504 l_api_name,
505 G_PKG_NAME )
506 THEN
507 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
508 END IF;
509 --
510
511 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
512 FND_MSG_PUB.initialize ;
513 END IF ;
514 --
515 p_return_status := FND_API.G_RET_STS_SUCCESS ;
516 --
517
518 --
519 -- Deleting dependent detail records from psb_account_position_set_lines.
520 -- ( To maintain ISOLATED master-detail form relation also. )
521 --
522
523 SELECT account_position_set_id INTO l_account_position_set_id
524 FROM psb_account_position_sets
525 WHERE rowid = p_row_id ;
526
527 --
528 -- When we delete a set line, we also need to delete all records associated
529 -- with Psb_position_set_line_values table. The Delete_Row API deletes
530 -- not only the line_id but related child records as well.
531 --
532 FOR l_lines_rec IN
533 (
534 SELECT rowid
535 FROM psb_account_position_set_lines
536 WHERE account_position_set_id = l_account_position_set_id
537 )
538 LOOP
539 --
540 PSB_Acct_Position_Set_Line_Pvt.Delete_Row
541 (
542 p_api_version => 1.0 ,
543 p_init_msg_list => FND_API.G_FALSE,
544 p_commit => FND_API.G_FALSE,
545 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
546 p_return_status => l_return_status,
547 p_msg_count => l_msg_count,
548 p_msg_data => l_msg_data,
549 --
550 p_row_id => l_lines_rec.rowid
551 );
552 --
553 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
554 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
555 END IF ;
556 --
557 END LOOP ;
558
559 --
560 -- Deleting the record in psb_account_position_sets.
561 --
562 DELETE FROM psb_account_position_sets
563 WHERE rowid = p_row_id;
567 END IF;
564
565 IF (SQL%NOTFOUND) THEN
566 RAISE NO_DATA_FOUND ;
568
569 --
570 IF FND_API.To_Boolean ( p_commit ) THEN
571 COMMIT WORK;
572 END iF;
573 --
574 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
575 p_data => p_msg_data );
576
577 EXCEPTION
578 --
579 WHEN FND_API.G_EXC_ERROR THEN
580 --
581 ROLLBACK TO Delete_Row_Pvt ;
582 p_return_status := FND_API.G_RET_STS_ERROR;
583 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
584 p_data => p_msg_data );
585 --
586 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
587 --
588 ROLLBACK TO Delete_Row_Pvt ;
589 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
590 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
591 p_data => p_msg_data );
592 --
593 WHEN OTHERS THEN
594 --
595 ROLLBACK TO Delete_Row_Pvt ;
596 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
597 --
598 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
599 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
600 l_api_name);
601 END if;
602 --
603 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
604 p_data => p_msg_data );
605 --
606 END Delete_Row;
607 /* ----------------------------------------------------------------------- */
608
609
610
611 /*==========================================================================+
612 | PROCEDURE Check_Unique |
613 +==========================================================================*/
614 PROCEDURE Check_Unique
615 (
616 p_api_version IN NUMBER,
617 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
618 p_commit IN VARCHAR2 := FND_API.G_FALSE,
619 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
620 p_return_status OUT NOCOPY VARCHAR2,
621 p_msg_count OUT NOCOPY NUMBER,
622 p_msg_data OUT NOCOPY VARCHAR2,
623 --
624 p_row_id IN VARCHAR2,
625 p_name IN VARCHAR2,
626 p_account_or_position_type IN VARCHAR2,
627 p_data_extract_id IN NUMBER,
628 p_return_value IN OUT NOCOPY VARCHAR2
629 )
630 IS
631 --
632 l_api_name CONSTANT VARCHAR2(30) := 'Check_Unique';
633 l_api_version CONSTANT NUMBER := 1.0;
634 --
635 l_tmp VARCHAR2(1);
636
637 CURSOR c IS
638 SELECT '1'
639 FROM psb_account_position_sets
640 WHERE name = p_name
641 AND account_or_position_type = p_account_or_position_type
642 AND ( p_data_extract_id IS NULL
643 OR
644 data_extract_id = p_data_extract_id
645 )
646 AND (
647 p_row_id IS NULL
648 OR
649 rowid <> p_row_id
650 );
651 --
652 BEGIN
653 --
654 SAVEPOINT Check_Unique_Pvt ;
655 --
656 IF NOT FND_API.Compatible_API_Call ( l_api_version,
657 p_api_version,
658 l_api_name,
659 G_PKG_NAME )
660 THEN
661 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
662 END IF;
663 --
664
665 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
666 FND_MSG_PUB.initialize ;
667 END IF;
668 --
669 p_return_status := FND_API.G_RET_STS_SUCCESS ;
670 --
671
672 -- Checking the Psb_set_relations table for references.
673 OPEN c;
674 FETCH c INTO l_tmp;
675
676 -- p_Return_Value specifies whether unique value exists or not.
677 IF l_tmp IS NULL THEN
678 p_Return_Value := 'FALSE';
679 ELSE
680 p_Return_Value := 'TRUE';
681 END IF;
682
683 CLOSE c;
684 --
685 IF FND_API.To_Boolean ( p_commit ) THEN
686 COMMIT WORK;
687 END iF;
688 --
689 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
690 p_data => p_msg_data );
691 --
692 EXCEPTION
693 --
694 WHEN FND_API.G_EXC_ERROR THEN
695 --
696 ROLLBACK TO Check_Unique_Pvt ;
697 p_return_status := FND_API.G_RET_STS_ERROR;
698 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
699 p_data => p_msg_data );
700 --
701 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
702 --
703 ROLLBACK TO Check_Unique_Pvt ;
704 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
705 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
706 p_data => p_msg_data );
707 --
708 WHEN OTHERS THEN
709 --
710 ROLLBACK TO Check_Unique_Pvt ;
711 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
712 --
713 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
714 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
715 l_api_name);
716 END if;
717 --
718 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
719 p_data => p_msg_data );
720 --
721 END Check_Unique;
725
722 /* ----------------------------------------------------------------------- */
723
724
726 /*==========================================================================+
727 | PROCEDURE Check_References |
728 +==========================================================================*/
729 PROCEDURE Check_References
730 (
731 p_api_version IN NUMBER,
732 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
733 p_commit IN VARCHAR2 := FND_API.G_FALSE,
734 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
735 p_return_status OUT NOCOPY VARCHAR2,
736 p_msg_count OUT NOCOPY NUMBER,
737 p_msg_data OUT NOCOPY VARCHAR2,
738 --
739 p_account_position_set_id IN NUMBER,
740 p_return_value IN OUT NOCOPY VARCHAR2,
741 p_frozen_bg_reference IN OUT NOCOPY VARCHAR2
742 )
743 IS
744 --
745 l_api_name CONSTANT VARCHAR2(30) := 'Check_References';
746 l_api_version CONSTANT NUMBER := 1.0;
747 --
748 l_tmp VARCHAR2(1);
749 l_use_in_budget_group_flag VARCHAR2(1);
750 l_freeze_hierarchy_flag VARCHAR2(1);
751 --
752 CURSOR l_check_set_relation_csr IS
753 SELECT '1'
754 FROM psb_set_relations
755 WHERE account_position_set_id = p_account_position_set_id;
756
757 CURSOR l_check_budget_group_csr IS
758 SELECT '1'
759 FROM psb_budget_groups
760 WHERE root_budget_group = 'Y'
761 AND budget_group_type = 'R'
762 AND ( ps_account_position_set_id = p_account_position_set_id
763 OR
764 nps_account_position_set_id = p_account_position_set_id
765 ) ;
766
767 CURSOR l_check_frozen_bg_csr IS
768 SELECT '1'
769 FROM psb_budget_groups
770 WHERE root_budget_group = 'Y'
771 AND budget_group_type = 'R'
772 AND NVL(freeze_hierarchy_flag, 'N') = 'Y'
773 AND ( ps_account_position_set_id = p_account_position_set_id
774 OR
775 nps_account_position_set_id = p_account_position_set_id
776 ) ;
777 BEGIN
778
779 /*
780 p_return_value returns if the set is referenced by any entity.
781 p_frozen_bg_reference returns if the set is referenced by a frozen bg.
782 */
783
784 SAVEPOINT Check_References_Pvt ;
785 --
786 IF NOT FND_API.Compatible_API_Call ( l_api_version,
787 p_api_version,
788 l_api_name,
789 G_PKG_NAME )
790 THEN
791 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
792 END IF;
793 --
794
795 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
796 FND_MSG_PUB.initialize ;
797 END IF;
798 --
799 p_return_status := FND_API.G_RET_STS_SUCCESS ;
800 --
801
802 -- First check whether it has been referenced by any entity.
803 OPEN l_check_set_relation_csr;
804 FETCH l_check_set_relation_csr INTO l_tmp;
805 CLOSE l_check_set_relation_csr;
806
807 IF l_tmp IS NULL THEN
808 p_return_value := 'FALSE';
809 ELSE
810 p_return_value := 'TRUE';
811 END IF;
812
813 -- Now check if budget group top region references it.
814 IF p_return_value = 'FALSE' THEN
815
816 -- reset flag
817 l_tmp := NULL;
818
819 OPEN l_check_budget_group_csr;
820 FETCH l_check_budget_group_csr INTO l_tmp;
821 CLOSE l_check_budget_group_csr;
822
823 IF l_tmp IS NOT NULL THEN
824 p_return_value := 'TRUE';
825 END IF;
826
827 END IF;
828
829 -- Retrive use_in_budget_group_flag to return additional information through
830 -- p_frozen_bg_reference parameter.
831 SELECT NVL(use_in_budget_group_flag, 'N') INTO l_use_in_budget_group_flag
832 FROM psb_account_position_sets
833 WHERE account_position_set_id = p_account_position_set_id;
834
835 IF l_use_in_budget_group_flag = 'Y' THEN
836
837 -- Need to check whether referenced by any frozen budget group. If yes,
838 -- the set cannot be modified, otherwise the set can be performed only
839 -- update operations.
840
841 -- reset flag
842 l_tmp := NULL;
843
844 -- First check if any frozen budget group references it in the top region.
845 OPEN l_check_frozen_bg_csr;
846 FETCH l_check_frozen_bg_csr INTO l_tmp;
847 CLOSE l_check_frozen_bg_csr;
848
849 IF l_tmp IS NULL THEN
850 p_frozen_bg_reference := 'FALSE';
851 ELSE
852 p_frozen_bg_reference := 'TRUE';
853 END IF;
854
855 -- Now check if any frozen budget group references it in the account region.
856 IF p_frozen_bg_reference = 'FALSE' THEN
857
858 FOR l_budget_group_csr IN
859 (
860 SELECT DECODE( bg.root_budget_group, 'Y', bg.budget_group_id,
861 bg.root_budget_group_id ) as root_budget_group_id
862 FROM psb_set_relations rel,
863 psb_budget_groups bg
864 WHERE rel.account_position_set_id = p_account_position_set_id
865 AND bg.budget_group_type = 'R'
869
866 AND bg.budget_group_id = rel.budget_group_id
867 )
868 LOOP
870 SELECT NVL(freeze_hierarchy_flag, 'N') into l_freeze_hierarchy_flag
871 FROM psb_budget_groups
872 WHERE budget_group_id = l_budget_group_csr.root_budget_group_id;
873
874 -- Check if any referenced budget group is frozen.
875 IF l_freeze_hierarchy_flag = 'Y' THEN
876 p_frozen_bg_reference := 'TRUE';
877 EXIT ;
878 END IF;
879
880 END LOOP ;
881
882 END IF;
883
884 END IF;
885 -- End Checking references.
886
887 --
888 IF FND_API.To_Boolean ( p_commit ) THEN
889 COMMIT WORK;
890 END iF;
891 --
892 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
893 p_data => p_msg_data );
894
895 EXCEPTION
896 --
897 WHEN FND_API.G_EXC_ERROR THEN
898 --
899 ROLLBACK TO Check_References_Pvt ;
900 p_return_status := FND_API.G_RET_STS_ERROR;
901 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
902 p_data => p_msg_data );
903 --
904 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
905 --
906 ROLLBACK TO Check_References_Pvt ;
907 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
908 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
909 p_data => p_msg_data );
910 --
911 WHEN OTHERS THEN
912 --
913 ROLLBACK TO Check_References_Pvt ;
914 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
915 --
916 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
917 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
918 l_api_name);
919 END if;
920 --
921 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
922 p_data => p_msg_data );
923 --
924 END Check_References;
925 /*-------------------------------------------------------------------------*/
926
927
928
929 /*==========================================================================+
930 | PROCEDURE Copy_Position_Sets |
931 +==========================================================================*/
932 --
933 -- This API copies position sets from a source data extract to a target data
934 -- extract.
935 --
936 PROCEDURE Copy_Position_Sets
937 (
938 p_api_version IN NUMBER,
939 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
940 p_commit IN VARCHAR2 := FND_API.G_FALSE,
941 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
942 p_return_status OUT NOCOPY VARCHAR2,
943 p_msg_count OUT NOCOPY NUMBER,
944 p_msg_data OUT NOCOPY VARCHAR2,
945 --
946 p_source_data_extract_id IN NUMBER,
947 p_target_data_extract_id IN NUMBER,
948 p_entity_table IN PSB_Account_Position_Set_Pvt.Entity_Tbl_Type
949 )
950 IS
951 --
952 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Position_Sets';
953 l_api_version CONSTANT NUMBER := 1.0;
954 --
955 l_return_status VARCHAR2(1) ;
956 l_msg_count NUMBER ;
957 l_msg_data VARCHAR2(2000) ;
958 --
959 l_source_business_group_id psb_data_extracts.business_group_id%TYPE ;
960 l_target_business_group_id psb_data_extracts.business_group_id%TYPE ;
961 l_new_position_set_id
962 psb_account_position_sets.account_position_set_id%TYPE ;
963 --
964 CURSOR l_source_data_extract_csr IS
965 SELECT business_group_id
966 FROM psb_data_extracts
967 WHERE data_extract_id = p_source_data_extract_id ;
968 --
969 CURSOR l_target_data_extract_csr IS
970 SELECT business_group_id
971 FROM psb_data_extracts
972 WHERE data_extract_id = p_target_data_extract_id ;
973 --
974 BEGIN
975 --
976 SAVEPOINT Copy_Position_Sets_Pvt ;
977 --
978 IF NOT FND_API.Compatible_API_Call ( l_api_version,
979 p_api_version,
980 l_api_name,
981 G_PKG_NAME )
982 THEN
983 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
984 END IF;
985 --
986
987 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
988 FND_MSG_PUB.initialize ;
989 END IF;
990 --
991 p_return_status := FND_API.G_RET_STS_SUCCESS ;
992 --
993
994 --
995 -- Validate the p_entity_table.
996 --
997 FOR i IN 1..p_entity_table.COUNT
998 LOOP
999
1000 IF p_entity_table(i) NOT IN ( 'BWR', 'C', 'DR', 'E', 'P', 'PSG' ) THEN
1001 --
1002 Fnd_Message.Set_Name ('PSB', 'PSB_INVALID_ENTITY_TYPE') ;
1003 Fnd_Message.Set_Token('ENTITY_TYPE', p_entity_table(i) ) ;
1004 FND_MSG_PUB.Add;
1005 RAISE FND_API.G_EXC_ERROR ;
1006 --
1007 END IF;
1008
1009 END LOOP;
1010
1011 --
1012 -- Validate the source data extract.
1013 --
1014 OPEN l_source_data_extract_csr ;
1015 FETCH l_source_data_extract_csr INTO l_source_business_group_id ;
1016
1017 IF ( l_source_data_extract_csr%NOTFOUND ) THEN
1018 --
1019 Fnd_Message.Set_Name ('PSB', 'PSB_INVALID_DATA_EXTRACT') ;
1023 --
1020 Fnd_Message.Set_Token('DATA_EXTRACT', p_source_data_extract_id ) ;
1021 FND_MSG_PUB.Add;
1022 RAISE FND_API.G_EXC_ERROR ;
1024 END IF ;
1025
1026 --
1027 -- Validate the target data extract.
1028 --
1029 OPEN l_target_data_extract_csr ;
1030 FETCH l_target_data_extract_csr INTO l_target_business_group_id ;
1031
1032 IF ( l_target_data_extract_csr%NOTFOUND ) THEN
1033 --
1034 Fnd_Message.Set_Name ('PSB', 'PSB_INVALID_DATA_EXTRACT') ;
1035 Fnd_Message.Set_Token('DATA_EXTRACT', p_target_data_extract_id ) ;
1036 FND_MSG_PUB.Add;
1037 RAISE FND_API.G_EXC_ERROR ;
1038 --
1039 END IF ;
1040
1041
1042 -- First copy all the global position sets.
1043 FOR l_global_sets_rec IN
1044 (
1045 SELECT *
1046 FROM psb_account_position_sets
1047 WHERE account_or_position_type = 'P'
1048 AND global_or_local_type = 'G'
1049 AND data_extract_id = p_source_data_extract_id
1050 )
1051 LOOP
1052
1053 Copy_Position_Set
1054 (
1055 p_api_version => 1.0,
1056 p_init_msg_list => FND_API.G_TRUE,
1057 p_commit => FND_API.G_FALSE,
1058 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1059 p_return_status => l_return_status,
1060 p_msg_count => l_msg_count,
1061 p_msg_data => l_msg_data,
1062 --
1063 p_source_position_set_id => l_global_sets_rec.account_position_set_id ,
1064 p_source_data_extract_id => p_source_data_extract_id,
1065 p_target_data_extract_id => p_target_data_extract_id,
1066 p_target_business_group_id => l_target_business_group_id,
1067 p_new_position_set_id => l_new_position_set_id
1068 ) ;
1069 --
1070 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1071 RAISE FND_API.G_EXC_ERROR ;
1072 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1073 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1074 END IF;
1075 --
1076
1077 END LOOP ;
1078
1079
1080 --
1081 -- Now copy the local position sets as per the entities speficied in the
1082 -- PL/SQL table.
1083 --
1084 FOR i IN 1..p_entity_table.COUNT
1085 LOOP
1086
1087 pd( 'Entity type :' || p_entity_table(i) ) ;
1088
1089 FOR l_local_sets_rec IN
1090 (
1091 SELECT sets.account_position_set_id
1092 FROM psb_set_relations rels ,
1093 psb_account_position_sets sets
1094 WHERE sets.account_position_set_id = rels.account_position_set_id
1095 AND sets.account_or_position_type = 'P'
1096 AND sets.global_or_local_type = 'L'
1097 AND sets.data_extract_id = p_source_data_extract_id
1098 AND DECODE( p_entity_table(i) ,
1099 'BWR', budget_workflow_rule_id,
1100 'C', constraint_id,
1101 'DR', default_rule_id,
1102 'P', parameter_id,
1103 'PSG', position_set_group_id
1104 ) IS NOT NULL
1105 )
1106 LOOP
1107
1108 --
1109 Copy_Position_Set
1110 (
1111 p_api_version => 1.0,
1112 p_init_msg_list => FND_API.G_TRUE,
1113 p_commit => FND_API.G_FALSE,
1114 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1115 p_return_status => l_return_status,
1116 p_msg_count => l_msg_count,
1117 p_msg_data => l_msg_data,
1118 --
1119 p_source_position_set_id => l_local_sets_rec.account_position_set_id,
1120 p_source_data_extract_id => p_source_data_extract_id,
1121 p_target_data_extract_id => p_target_data_extract_id,
1122 p_target_business_group_id => l_target_business_group_id,
1123 p_new_position_set_id => l_new_position_set_id
1124 ) ;
1125 --
1126 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1127 RAISE FND_API.G_EXC_ERROR ;
1128 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1130 END IF;
1131 --
1132
1133 END LOOP ; -- End local sets.
1134
1135 END LOOP ; -- End p_entity_table table.
1136
1137 --
1138 IF FND_API.To_Boolean ( p_commit ) THEN
1139 COMMIT WORK;
1140 END iF;
1141 --
1142 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1143 p_data => p_msg_data );
1144
1145 --
1146 EXCEPTION
1147 --
1148 WHEN FND_API.G_EXC_ERROR THEN
1149 --
1150 ROLLBACK TO Copy_Position_Sets_Pvt ;
1151 p_return_status := FND_API.G_RET_STS_ERROR;
1152 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1153 p_data => p_msg_data );
1154 --
1155 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1156 --
1157 ROLLBACK TO Copy_Position_Sets_Pvt ;
1158 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1159 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1160 p_data => p_msg_data );
1161 --
1162 WHEN OTHERS THEN
1163 --
1164 ROLLBACK TO Copy_Position_Sets_Pvt ;
1165 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1166 --
1170 END if;
1167 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1168 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1169 l_api_name);
1171 --
1172 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1173 p_data => p_msg_data );
1174 --
1175 END Copy_Position_Sets ;
1176 /*-------------------------------------------------------------------------*/
1177
1178
1179
1180 /*===========================================================================+
1181 | PROCEDURE Copy_Position_Set |
1182 +===========================================================================*/
1183 --
1184 -- This API copies a given position set.
1185 --
1186 PROCEDURE Copy_Position_Set
1187 (
1188 p_api_version IN NUMBER,
1189 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1190 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1191 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1192 p_return_status OUT NOCOPY VARCHAR2,
1193 p_msg_count OUT NOCOPY NUMBER,
1194 p_msg_data OUT NOCOPY VARCHAR2,
1195 --
1196 p_source_position_set_id IN NUMBER ,
1197 p_source_data_extract_id IN NUMBER ,
1198 p_target_data_extract_id IN NUMBER ,
1199 p_target_business_group_id IN NUMBER ,
1200 p_new_position_set_id OUT NOCOPY NUMBER
1201 )
1202 IS
1203 --
1204 l_api_name CONSTANT VARCHAR2(30) := 'Copy_Position_Set' ;
1205 l_api_version CONSTANT NUMBER := 1.0 ;
1206 --
1207 l_return_status VARCHAR2(1) ;
1208 l_msg_count NUMBER ;
1209 l_msg_data VARCHAR2(2000) ;
1210 --
1211 l_current_date DATE := SYSDATE ;
1212 l_current_user_id NUMBER := NVL( Fnd_Global.User_Id , 0) ;
1213 l_current_login_id NUMBER := NVL( Fnd_Global.Login_Id , 0) ;
1214 --
1215 l_count NUMBER ;
1216 l_row_id VARCHAR2(50) ;
1217 l_account_position_set_id
1218 psb_account_position_sets.account_position_set_id%TYPE;
1219
1220 l_line_sequence_id psb_account_position_set_lines.line_sequence_id%TYPE ;
1221 l_value_sequence_id psb_position_set_line_values.value_sequence_id%TYPE ;
1222 l_target_attribute_id psb_account_position_set_lines.attribute_id%TYPE ;
1223
1224 l_target_attribute_value_id
1225 psb_position_set_line_values.attribute_value_id%TYPE ;
1226 --
1227 CURSOR l_sets_csr IS
1228 SELECT *
1229 FROM psb_account_position_sets
1230 WHERE account_position_set_id = p_source_position_set_id ;
1231 --
1232 CURSOR l_find_matching_attribute_csr
1233 (
1234 c_name psb_attributes_VL.name%TYPE
1235 )
1236 IS
1237 SELECT attribute_id
1238 FROM psb_attributes_VL
1239 WHERE business_group_id = p_target_business_group_id
1240 AND name = c_name
1241 AND allow_in_position_set_flag = 'Y' ;
1242 --
1243 CURSOR l_find_matching_value_csr
1244 (
1245 c_attribute_value psb_attribute_values.attribute_value%TYPE
1246 )
1247 IS
1248 SELECT attribute_value_id
1249 FROM psb_attribute_values
1250 WHERE data_extract_id = p_target_data_extract_id
1251 AND attribute_id = l_target_attribute_id
1252 AND attribute_value = c_attribute_value ;
1253 --
1254 l_sets_rec l_sets_csr%ROWTYPE ;
1255 --
1256 BEGIN
1257 --
1258 SAVEPOINT Copy_Position_Set_Pvt ;
1259 --
1260 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1261 p_api_version,
1262 l_api_name,
1263 G_PKG_NAME )
1264 THEN
1265 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1266 END IF;
1267 --
1268
1269 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
1270 FND_MSG_PUB.initialize ;
1271 END IF;
1272 --
1273 p_return_status := FND_API.G_RET_STS_SUCCESS ;
1274
1275 pd( 'position set id :' || p_source_position_set_id ) ;
1276
1277 --
1278 -- Copy the set.
1279 --
1280
1281 OPEN l_sets_csr ;
1282 FETCH l_sets_csr INTO l_sets_rec ;
1283 CLOSE l_sets_csr ;
1284
1285 --
1286 -- Check whether the current position set already exists. If exists, then the
1287 -- set cannot be copied and a message is placed in the message stack.
1288 --
1289 SELECT count(*) INTO l_count
1290 FROM psb_account_position_sets
1291 WHERE account_position_set_id <> l_sets_rec.account_position_set_id
1292 AND name = l_sets_rec.name
1293 AND data_extract_id = p_target_data_extract_id ;
1294
1295 IF l_count <> 0 THEN
1296
1297 pd( 'Cannot copy as set exists :' || l_sets_rec.name ) ;
1298
1299 --
1300 Fnd_Message.Set_Name ('PSB', 'PSB_SET_CANNOT_BE_COPIED') ;
1301 Fnd_Message.Set_Token('SET_NAME', l_sets_rec.name ) ;
1302 FND_MSG_PUB.Add;
1303 RETURN;
1304 --
1305 END IF;
1306
1307 PSB_Account_Position_Set_Pvt.Insert_Row
1308 (
1309 p_api_version => 1.0,
1310 p_init_msg_list => FND_API.G_TRUE,
1311 p_commit => FND_API.G_FALSE,
1315 p_msg_data => l_msg_data,
1312 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1313 p_return_status => l_return_status,
1314 p_msg_count => l_msg_count,
1316 --
1317 p_row_id => l_row_id,
1318 p_account_position_set_id => l_account_position_set_id,
1319 p_name => l_sets_rec.name,
1320 p_set_of_books_id => l_sets_rec.set_of_books_id,
1321 p_data_extract_id => p_target_data_extract_id,
1322 p_budget_group_id => l_sets_rec.budget_group_id,
1323 p_global_or_local_type => l_sets_rec.global_or_local_type,
1324 p_account_or_position_type => l_sets_rec.account_or_position_type,
1325 p_attribute_selection_type => l_sets_rec.attribute_selection_type,
1326 p_business_group_id => p_target_business_group_id,
1327 p_last_update_date => l_current_date,
1328 p_last_updated_by => l_current_user_id,
1329 p_last_update_login => l_current_login_id,
1330 p_created_by => l_current_user_id,
1331 p_creation_date => l_current_date
1332 );
1333 --
1334 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1335 RAISE FND_API.G_EXC_ERROR ;
1336 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1337 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1338 END IF;
1339 --
1340
1341 -- Populate the new position id field.
1342 p_new_position_set_id := l_account_position_set_id ;
1343
1344 --
1345 -- Copy the set lines.
1346 --
1347
1348 FOR l_lines_rec IN
1349 (
1350 SELECT *
1351 FROM psb_acct_position_set_lines_v
1352 WHERE account_position_set_id = p_source_position_set_id
1353 )
1354 LOOP
1355
1356
1357 -- Find the matching attribute in the target data extract.
1358 OPEN l_find_matching_attribute_csr ( l_lines_rec.attribute_name ) ;
1359 FETCH l_find_matching_attribute_csr INTO l_target_attribute_id ;
1360
1361 IF l_find_matching_attribute_csr%NOTFOUND THEN
1362
1363 -- Skip this set line and process the next one.
1364 CLOSE l_find_matching_attribute_csr ;
1365 GOTO end_lines_loop ; -- PL/SQL lacks CONTINUE statement.
1366
1367 END IF;
1368 CLOSE l_find_matching_attribute_csr ;
1369
1370 -- Reset l_line_sequence_id variable as new values are created from
1371 -- the sequence.
1372 l_line_sequence_id := NULL;
1373
1374 PSB_Acct_Position_Set_Line_Pvt.Insert_Row
1375 (
1376 p_api_version => 1.0,
1377 p_init_msg_list => FND_API.G_TRUE,
1378 p_commit => FND_API.G_FALSE,
1379 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1380 p_return_status => l_return_status,
1381 p_msg_count => l_msg_count,
1382 p_msg_data => l_msg_data,
1383 --
1384 p_row_id => l_row_id,
1385 p_line_sequence_id => l_line_sequence_id,
1386 p_account_position_set_id => l_account_position_set_id,
1387 p_description => l_lines_rec.description,
1388 p_business_group_id => p_target_business_group_id,
1389 p_attribute_id => l_target_attribute_id,
1390 p_include_or_exclude_type => l_lines_rec.include_or_exclude_type,
1391 p_segment1_low => l_lines_rec.segment1_low,
1392 p_segment2_low => l_lines_rec.segment2_low,
1393 p_segment3_low => l_lines_rec.segment3_low,
1394 p_segment4_low => l_lines_rec.segment4_low,
1395 p_segment5_low => l_lines_rec.segment5_low,
1396 p_segment6_low => l_lines_rec.segment6_low,
1397 p_segment7_low => l_lines_rec.segment7_low,
1398 p_segment8_low => l_lines_rec.segment8_low,
1399 p_segment9_low => l_lines_rec.segment9_low,
1400 p_segment10_low => l_lines_rec.segment10_low,
1401 p_segment11_low => l_lines_rec.segment11_low,
1402 p_segment12_low => l_lines_rec.segment12_low,
1403 p_segment13_low => l_lines_rec.segment13_low,
1404 p_segment14_low => l_lines_rec.segment14_low,
1405 p_segment15_low => l_lines_rec.segment15_low,
1406 p_segment16_low => l_lines_rec.segment16_low,
1407 p_segment17_low => l_lines_rec.segment17_low,
1408 p_segment18_low => l_lines_rec.segment18_low,
1409 p_segment19_low => l_lines_rec.segment19_low,
1410 p_segment20_low => l_lines_rec.segment20_low,
1411 p_segment21_low => l_lines_rec.segment21_low,
1412 p_segment22_low => l_lines_rec.segment22_low,
1413 p_segment23_low => l_lines_rec.segment23_low,
1414 p_segment24_low => l_lines_rec.segment24_low,
1415 p_segment25_low => l_lines_rec.segment25_low,
1416 p_segment26_low => l_lines_rec.segment26_low,
1417 p_segment27_low => l_lines_rec.segment27_low,
1418 p_segment28_low => l_lines_rec.segment28_low,
1419 p_segment29_low => l_lines_rec.segment29_low,
1420 p_segment30_low => l_lines_rec.segment30_low,
1421 p_segment1_high => l_lines_rec.segment1_high,
1425 p_segment5_high => l_lines_rec.segment5_high,
1422 p_segment2_high => l_lines_rec.segment2_high,
1423 p_segment3_high => l_lines_rec.segment3_high,
1424 p_segment4_high => l_lines_rec.segment4_high,
1426 p_segment6_high => l_lines_rec.segment6_high,
1427 p_segment7_high => l_lines_rec.segment7_high,
1428 p_segment8_high => l_lines_rec.segment8_high,
1429 p_segment9_high => l_lines_rec.segment9_high,
1430 p_segment10_high => l_lines_rec.segment10_high,
1431 p_segment11_high => l_lines_rec.segment11_high,
1432 p_segment12_high => l_lines_rec.segment12_high,
1433 p_segment13_high => l_lines_rec.segment13_high,
1434 p_segment14_high => l_lines_rec.segment14_high,
1435 p_segment15_high => l_lines_rec.segment15_high,
1436 p_segment16_high => l_lines_rec.segment16_high,
1437 p_segment17_high => l_lines_rec.segment17_high,
1438 p_segment18_high => l_lines_rec.segment18_high,
1439 p_segment19_high => l_lines_rec.segment19_high,
1440 p_segment20_high => l_lines_rec.segment20_high,
1441 p_segment21_high => l_lines_rec.segment21_high,
1442 p_segment22_high => l_lines_rec.segment22_high,
1443 p_segment23_high => l_lines_rec.segment23_high,
1444 p_segment24_high => l_lines_rec.segment24_high,
1445 p_segment25_high => l_lines_rec.segment25_high,
1446 p_segment26_high => l_lines_rec.segment26_high,
1447 p_segment27_high => l_lines_rec.segment27_high,
1448 p_segment28_high => l_lines_rec.segment28_high,
1449 p_segment29_high => l_lines_rec.segment29_high,
1450 p_segment30_high => l_lines_rec.segment30_high,
1451 p_context => l_lines_rec.context,
1452 p_attribute1 => l_lines_rec.attribute1,
1453 p_attribute2 => l_lines_rec.attribute2,
1454 p_attribute3 => l_lines_rec.attribute3,
1455 p_attribute4 => l_lines_rec.attribute4,
1456 p_attribute5 => l_lines_rec.attribute5,
1457 p_attribute6 => l_lines_rec.attribute6,
1458 p_attribute7 => l_lines_rec.attribute7,
1459 p_attribute8 => l_lines_rec.attribute8,
1460 p_attribute9 => l_lines_rec.attribute9,
1461 p_attribute10 => l_lines_rec.attribute10,
1462 p_last_update_date => l_current_date,
1463 p_last_updated_by => l_current_user_id,
1464 p_last_update_login => l_current_login_id,
1465 p_created_by => l_current_user_id,
1466 p_creation_date => l_current_date
1467 );
1468 --
1469 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1470 RAISE FND_API.G_EXC_ERROR ;
1471 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1473 END IF;
1474 --
1475
1476 pd( 'Created set line:' || l_line_sequence_id ) ;
1477
1478 --
1479 -- Copy the set line values.
1480 --
1481
1482 FOR l_values_rec IN
1483 (
1484 SELECT *
1485 FROM psb_position_set_line_values_v
1486 WHERE line_sequence_id = l_lines_rec.line_sequence_id
1487 )
1488 LOOP
1489
1490 --
1491 -- We need to find matching attribute_value_id only when value_table
1492 -- flag is 'Y', otherwise every value is good.
1493 --
1494 IF l_values_rec.attribute_value_table_flag = 'Y' THEN
1495
1496 -- Find the matching attribute_value_id.
1497 -- ( The l_values_rec.attribute_value will be null. )
1498 OPEN l_find_matching_value_csr ( l_values_rec.attribute_table_value );
1499 FETCH l_find_matching_value_csr INTO l_target_attribute_value_id ;
1500
1501 IF l_find_matching_value_csr%NOTFOUND THEN
1502
1503 -- Skip this value line and process the next one.
1504 CLOSE l_find_matching_value_csr ;
1505 GOTO end_values_loop ; -- PL/SQL lacks CONTINUE statement.
1506
1507 END IF;
1508 CLOSE l_find_matching_value_csr ;
1509
1510 ELSE
1511
1512 -- The l_values_rec.attribute_value will not be null.
1513 l_target_attribute_value_id := NULL ;
1514
1515 END IF;
1516
1517 /* Bug No 2579818 Start */
1518 l_value_sequence_id := NULL;
1519 /* Bug No 2579818 End */
1520
1521 PSB_Pos_Set_Line_Values_Pvt.Insert_Row
1522 (
1523 p_api_version => 1.0,
1524 p_init_msg_list => FND_API.G_TRUE,
1525 p_commit => FND_API.G_FALSE,
1526 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1527 p_return_status => l_return_status,
1528 p_msg_count => l_msg_count,
1529 p_msg_data => l_msg_data,
1530 --
1531 p_row_id => l_row_id,
1532 p_value_sequence_id => l_value_sequence_id,
1533 p_line_sequence_id => l_line_sequence_id,
1534 p_attribute_value_id => l_target_attribute_value_id,
1535 p_attribute_value => l_values_rec.attribute_value,
1536 p_last_update_date => l_current_date,
1537 p_last_updated_by => l_current_user_id,
1538 p_last_update_login => l_current_login_id,
1539 p_created_by => l_current_user_id,
1543 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1540 p_creation_date => l_current_date
1541 );
1542 --
1544 RAISE FND_API.G_EXC_ERROR ;
1545 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1546 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1547 END IF;
1548 --
1549
1550 pd( 'Created val :' || l_line_sequence_id || '-' || l_value_sequence_id);
1551
1552 <<end_values_loop>>
1553 NULL;
1554 END LOOP ;
1555
1556 <<end_lines_loop>>
1557 NULL;
1558 END LOOP ;
1559
1560 --
1561 IF FND_API.To_Boolean ( p_commit ) THEN
1562 COMMIT WORK;
1563 END iF;
1564 --
1565 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1566 p_data => p_msg_data );
1567 --
1568 EXCEPTION
1569 --
1570 WHEN FND_API.G_EXC_ERROR THEN
1571 --
1572 ROLLBACK TO Copy_Position_Set_Pvt ;
1573 p_return_status := FND_API.G_RET_STS_ERROR ;
1574 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1575 p_data => p_msg_data );
1576 --
1577 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1578 --
1579 ROLLBACK TO Copy_Position_Set_Pvt ;
1580 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1581 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1582 p_data => p_msg_data );
1583 --
1584 WHEN OTHERS THEN
1585 --
1586 ROLLBACK TO Copy_Position_Set_Pvt ;
1587 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1588 --
1589 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1590 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1591 l_api_name);
1592 END if;
1593 --
1594 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
1595 p_data => p_msg_data );
1596 --
1597 END Copy_Position_Set ;
1598 /*---------------------------------------------------------------------------*/
1599
1600
1601
1602 /*===========================================================================+
1603 | PROCEDURE pd (Private) |
1604 +===========================================================================*/
1605 --
1606 -- Private procedure to print debug info. The name is tried to keep as
1607 -- short as possible for better documentaion.
1608 --
1609 PROCEDURE pd
1610 (
1611 p_message IN VARCHAR2
1612 )
1613 IS
1614 --
1615 BEGIN
1616
1617 IF g_debug_flag = 'Y' THEN
1618 NULL;
1619 -- dbms_output.put_line(p_message) ;
1620 END IF;
1621
1622 END pd ;
1623 /*---------------------------------------------------------------------------*/
1624
1625
1626 END PSB_Account_Position_Set_Pvt;