[Home] [Help]
PACKAGE BODY: APPS.PSB_SET_RELATION_PVT
Source
1 PACKAGE BODY PSB_Set_Relation_PVT AS
2 /* $Header: PSBVSTRB.pls 120.2 2004/11/30 12:39:41 shtripat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_Set_Relation_PVT';
5
6
7 /*=========================================================================+
8 | PROCEDURE Insert_Row |
9 +=========================================================================*/
10 PROCEDURE Insert_Row
11 (
12 p_api_version IN NUMBER,
13 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
14 p_commit IN VARCHAR2 := FND_API.G_FALSE,
15 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
16 p_return_status OUT NOCOPY VARCHAR2,
17 p_msg_count OUT NOCOPY NUMBER,
18 p_msg_data OUT NOCOPY VARCHAR2,
19 --
20 p_row_id IN OUT NOCOPY VARCHAR2,
21 p_set_relation_id IN OUT NOCOPY NUMBER,
22 p_account_position_set_id IN NUMBER,
23 p_allocation_Rule_id IN NUMBER,
24 p_budget_group_id IN NUMBER,
25 p_budget_workflow_rule_id IN NUMBER,
26 p_constraint_id IN NUMBER,
27 p_default_Rule_id IN NUMBER,
28 p_Parameter_Id IN NUMBER,
29 p_position_set_group_id IN NUMBER,
30 p_gl_budget_id IN NUMBER := FND_API.G_MISS_NUM,
31 /* Budget Revision Rules Enhancement Start */
32 p_rule_id IN VARCHAR2,
33 p_apply_balance_flag IN VARCHAR2,
34 /* Budget Revision Rules Enhancement End */
35 p_effective_start_date IN DATE,
36 p_effective_end_date IN DATE,
37 p_last_update_date IN DATE,
38 p_last_updated_by IN NUMBER,
39 p_last_update_login IN NUMBER,
40 p_created_by IN NUMBER,
41 p_creation_date IN DATE
42 )
43 IS
44 --
45 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
46 l_api_version CONSTANT NUMBER := 1.0;
47 --
48 CURSOR C IS
49 SELECT rowid
50 FROM psb_set_relations
51 WHERE set_relation_id = p_set_relation_id;
52
53 CURSOR C2 IS
54 SELECT psb_set_relations_s.nextval
55 FROM dual;
56 --
57 l_last_update_date DATE ;
58 l_last_Updated_by NUMBER ;
59 l_last_update_login NUMBER ;
60 l_created_by NUMBER ;
61 l_creation_date DATE ;
62 --
63 l_gl_budget_id psb_gl_budgets.gl_budget_id%TYPE ;
64 --
65 BEGIN
66 --
67 SAVEPOINT Insert_Row_Pvt ;
68 --
69 IF NOT FND_API.Compatible_API_Call ( l_api_version,
70 p_api_version,
71 l_api_name,
72 G_PKG_NAME )
73 THEN
74 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
75 END IF;
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 -- Resolve the defaulted parameters.
85 IF p_gl_budget_id = FND_API.G_MISS_NUM THEN
86 l_gl_budget_id := NULL ;
87 ELSE
88 l_gl_budget_id := p_gl_budget_id ;
89 END IF;
90 -- End resolving defaulted parameters.
91
92 IF (p_set_relation_id IS NULL) THEN
93 OPEN C2;
94 FETCH C2 INTO p_set_relation_id;
95 CLOSE C2;
96 END IF;
97
98 --
99 -- Set Global fields.
100 --
101 l_last_update_date := SYSDATE ;
102 --
103 l_last_Updated_by := FND_GLOBAL.User_Id;
104 IF l_last_Updated_by IS NULL THEN
105 l_last_Updated_by := -1;
106 END IF ;
107 --
108 l_last_update_login := FND_GLOBAL.Login_Id ;
109 IF l_last_update_login IS NULL THEN
110 l_last_update_login := -1;
111 END IF;
112 --
113 l_created_by := l_last_Updated_by ;
114 l_creation_date := l_last_update_date ;
115 --
116
117 INSERT INTO psb_set_relations(
118 set_relation_id,
119 account_position_set_id,
120 allocation_rule_id,
121 budget_group_id,
122 budget_workflow_rule_id,
123 constraint_id,
124 default_rule_id,
125 parameter_id,
126 position_set_group_id,
127 gl_budget_id,
128 /* Budget Revision Rules Enhancement Start */
129 rule_id,
130 apply_balance_flag,
131 /* Budget Revision Rules Enhancement End */
132 effective_start_date,
133 effective_end_date,
134 last_update_date,
135 last_updated_by,
136 last_update_login,
137 created_by,
138 creation_date)
139 VALUES (
140 p_set_relation_id,
141 p_account_position_set_id,
142 p_allocation_Rule_id,
143 p_budget_group_id,
144 p_budget_workflow_rule_id,
145 p_constraint_id,
146 p_default_Rule_id,
147 p_Parameter_Id,
148 p_position_set_group_id,
149 l_gl_budget_id,
150 /* Budget Revision Rules Enhancement Start */
151 p_rule_id,
152 p_apply_balance_flag,
153 /* Budget Revision Rules Enhancement End */
154 p_effective_start_date,
155 p_effective_end_date,
156 l_last_update_date,
157 l_last_Updated_by,
158 l_last_update_login,
159 l_created_by,
160 l_creation_date
161 ) ;
162
163 OPEN C;
164 FETCH C INTO p_row_id;
165 --
166 IF (C%NOTFOUND) THEN
167 CLOSE C;
168 RAISE FND_API.G_EXC_ERROR ;
169 END IF;
170 --
171 CLOSE C;
172 --
173 IF FND_API.To_Boolean ( p_commit ) THEN
174 COMMIT WORK;
175 END iF;
176 --
177 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
178 p_data => p_msg_data );
179 --
180 EXCEPTION
181 --
182 WHEN FND_API.G_EXC_ERROR THEN
183 --
184 ROLLBACK TO Insert_Row_Pvt ;
185 p_return_status := FND_API.G_RET_STS_ERROR;
186 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
187 p_data => p_msg_data );
188 --
189 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
190 --
191 ROLLBACK TO Insert_Row_Pvt ;
192 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
193 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
194 p_data => p_msg_data );
195 --
196 WHEN OTHERS THEN
197 --
198 ROLLBACK TO Insert_Row_Pvt ;
199 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
200 --
201 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
202 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
203 l_api_name);
204 END if;
205 --
206 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
207 p_data => p_msg_data );
208 --
209 END Insert_Row;
210 /* ----------------------------------------------------------------------- */
211
212
213
214 /*=========================================================================+
215 | PROCEDURE Lock_Row |
216 +=========================================================================*/
217 PROCEDURE Lock_Row
218 (
219 p_api_version IN NUMBER,
220 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
221 p_commit IN VARCHAR2 := FND_API.G_FALSE,
222 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
223 p_return_status OUT NOCOPY VARCHAR2,
224 p_msg_count OUT NOCOPY NUMBER,
225 p_msg_data OUT NOCOPY VARCHAR2,
226 --
227 p_row_id IN VARCHAR2,
228 p_set_relation_id IN NUMBER,
229 p_account_position_set_id IN NUMBER,
230 p_allocation_Rule_id IN NUMBER,
231 p_budget_group_id IN NUMBER,
232 p_budget_workflow_rule_id IN NUMBER,
233 p_constraint_id IN NUMBER,
234 p_default_Rule_id IN NUMBER,
235 p_Parameter_Id IN NUMBER,
236 p_position_set_group_id IN NUMBER,
237 p_gl_budget_id IN NUMBER := FND_API.G_MISS_NUM,
238 /* Budget Revision Rules Enhancement Start */
239 p_rule_id IN VARCHAR2,
240 p_apply_balance_flag IN VARCHAR2,
241 /* Budget Revision Rules Enhancement End */
242 p_effective_start_date IN DATE,
243 p_effective_end_date IN DATE,
244 --
245 p_row_locked OUT NOCOPY VARCHAR2
246 )
247 IS
248 --
249 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
250 l_api_version CONSTANT NUMBER := 1.0;
251 --
252 CURSOR C IS
253 SELECT *
254 FROM psb_set_relations
255 WHERE rowid = p_row_id
256 FOR UPDATE of set_relation_id NOWAIT;
257 Recinfo C%ROWTYPE;
258 --
259 l_gl_budget_id psb_gl_budgets.gl_budget_id%TYPE ;
260 --
261 BEGIN
262 --
263 SAVEPOINT Lock_Row_Pvt ;
264 --
265 IF NOT FND_API.Compatible_API_Call ( l_api_version,
266 p_api_version,
267 l_api_name,
268 G_PKG_NAME )
269 THEN
270 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
271 END IF;
272 --
273
274 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
275 FND_MSG_PUB.initialize ;
276 END IF;
277 --
278 p_return_status := FND_API.G_RET_STS_SUCCESS ;
279 p_row_locked := FND_API.G_TRUE ;
280
281 -- Resolve the defaulted parameters.
282 IF p_gl_budget_id = FND_API.G_MISS_NUM THEN
283 l_gl_budget_id := NULL ;
284 ELSE
285 l_gl_budget_id := p_gl_budget_id ;
286 END IF;
287 -- End resolving defaulted parameters.
288
289 OPEN C;
290 FETCH C INTO Recinfo;
291 --
292 IF (C%NOTFOUND) THEN
293 CLOSE C;
294 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
295 FND_MSG_PUB.Add;
296 RAISE FND_API.G_EXC_ERROR ;
297 END IF;
298 --
299 CLOSE C;
300 --
301 IF
302 (
303 (Recinfo.set_relation_id = p_set_relation_id)
304
305 AND (Recinfo.account_position_set_id = p_account_position_set_id)
306
307 AND ( (Recinfo.allocation_rule_id = p_allocation_Rule_id)
308 OR ( (Recinfo.allocation_rule_id IS NULL)
309 AND (p_allocation_Rule_id IS NULL)))
310
311 AND ( (Recinfo.budget_group_id = p_budget_group_id)
312 OR ( (Recinfo.budget_group_id IS NULL)
313 AND (p_budget_group_id IS NULL)))
314
315 AND ( (Recinfo.budget_workflow_rule_id = p_budget_workflow_rule_id)
316 OR ( (Recinfo.budget_workflow_rule_id IS NULL)
317 AND (p_budget_workflow_rule_id IS NULL)))
318
319 AND ( (Recinfo.constraint_id = p_constraint_id)
320 OR ( (Recinfo.constraint_id IS NULL)
321 AND (p_constraint_id IS NULL)))
322
323 AND ( (Recinfo.default_rule_id = p_default_Rule_id)
324 OR ( (Recinfo.default_rule_id IS NULL)
325 AND (p_default_Rule_id IS NULL)))
326
327 AND ( (Recinfo.parameter_id = p_Parameter_Id)
328 OR ( (Recinfo.parameter_id IS NULL)
329 AND (p_Parameter_Id IS NULL)))
330
331 AND ( (Recinfo.position_set_group_id = p_position_set_group_id)
332 OR ( (Recinfo.position_set_group_id IS NULL)
333 AND (p_position_set_group_id IS NULL)))
334
335 AND ( (Recinfo.gl_budget_id = l_gl_budget_id)
336 OR ( (Recinfo.gl_budget_id IS NULL)
337 AND (l_gl_budget_id IS NULL)))
338
339 /* Budget Revision Rules Enhancement Start */
340 AND ( (Recinfo.rule_id = p_rule_id)
341 OR ( (Recinfo.rule_id IS NULL)
342 AND (p_rule_id IS NULL)))
343
344 AND ( (Recinfo.apply_balance_flag = p_apply_balance_flag)
345 OR ( (Recinfo.apply_balance_flag IS NULL)
346 AND (p_apply_balance_flag IS NULL)))
347 /* Budget Revision Rules Enhancement End */
348
349 AND ( (Recinfo.effective_start_date = p_effective_start_date)
350 OR ( (Recinfo.effective_start_date IS NULL)
351 AND (p_effective_start_date IS NULL)))
352
353 AND ( (Recinfo.effective_end_date = p_effective_end_date)
354 OR ( (Recinfo.effective_end_date IS NULL)
355 AND (p_effective_end_date IS NULL)))
356 )
357 THEN
358 Null;
359 ELSE
360 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
361 FND_MSG_PUB.Add;
362 RAISE FND_API.G_EXC_ERROR ;
363 END IF;
364 --
365 IF FND_API.To_Boolean ( p_commit ) THEN
366 COMMIT WORK;
367 END iF;
368 --
369 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
370 p_data => p_msg_data );
371 --
372 EXCEPTION
373 --
374 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
375 --
376 ROLLBACK TO Lock_Row_Pvt ;
377 p_row_locked := FND_API.G_FALSE;
378 p_return_status := FND_API.G_RET_STS_ERROR;
379 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
380 p_data => p_msg_data );
381 --
382 WHEN FND_API.G_EXC_ERROR THEN
383 --
384 ROLLBACK TO Lock_Row_Pvt ;
385 p_return_status := FND_API.G_RET_STS_ERROR;
386 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
387 p_data => p_msg_data );
388 --
389 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
390 --
391 ROLLBACK TO Lock_Row_Pvt ;
392 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
393 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
394 p_data => p_msg_data );
395 --
396 WHEN OTHERS THEN
397 --
398 ROLLBACK TO Lock_Row_Pvt ;
399 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
400 --
401 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
402 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
403 l_api_name);
404 END if;
405 --
406 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
407 p_data => p_msg_data );
408 --
409 END Lock_Row;
410 /* ----------------------------------------------------------------------- */
411
412
413
414 /*=========================================================================+
415 | PROCEDURE Update_Row |
416 +=========================================================================*/
417 PROCEDURE Update_Row
418 (
419 p_api_version IN NUMBER,
420 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
421 p_commit IN VARCHAR2 := FND_API.G_FALSE,
422 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
423 p_return_status OUT NOCOPY VARCHAR2,
424 p_msg_count OUT NOCOPY NUMBER,
425 p_msg_data OUT NOCOPY VARCHAR2,
426 --
427 p_row_id IN VARCHAR2,
428 p_set_relation_id IN NUMBER,
429 p_account_position_set_id IN NUMBER,
430 p_allocation_Rule_id IN NUMBER,
431 p_budget_group_id IN NUMBER,
432 p_budget_workflow_rule_id IN NUMBER,
433 p_constraint_id IN NUMBER,
434 p_default_Rule_id IN NUMBER,
435 p_Parameter_Id IN NUMBER,
439 p_rule_id IN VARCHAR2,
436 p_position_set_group_id IN NUMBER,
437 p_gl_budget_id IN NUMBER := FND_API.G_MISS_NUM,
438 /* Budget Revision Rules Enhancement Start */
440 p_apply_balance_flag IN VARCHAR2,
441 /* Budget Revision Rules Enhancement End */
442 p_effective_start_date IN DATE,
443 p_effective_end_date IN DATE,
444 p_last_update_date IN DATE,
445 p_last_updated_by IN NUMBER,
446 p_last_update_login IN NUMBER
447 )
448 IS
449 --
450 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
451 l_api_version CONSTANT NUMBER := 1.0;
452 --
453 l_last_update_date DATE ;
454 l_last_Updated_by NUMBER ;
455 l_last_update_login NUMBER ;
456 --
457 l_gl_budget_id psb_gl_budgets.gl_budget_id%TYPE ;
458 --
459 BEGIN
460 --
461 SAVEPOINT Update_Row_Pvt ;
462 --
463 IF NOT FND_API.Compatible_API_Call ( l_api_version,
464 p_api_version,
465 l_api_name,
466 G_PKG_NAME )
467 THEN
468 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
469 END IF;
470 --
471
472 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
473 FND_MSG_PUB.initialize ;
474 END IF;
475 --
476 p_return_status := FND_API.G_RET_STS_SUCCESS ;
477
478 -- Resolve the defaulted parameters.
479 IF p_gl_budget_id = FND_API.G_MISS_NUM THEN
480 l_gl_budget_id := NULL ;
481 ELSE
482 l_gl_budget_id := p_gl_budget_id ;
483 END IF;
484 -- End resolving defaulted parameters.
485
486 --
487 -- Set Global fields.
488 --
489 l_last_update_date := SYSDATE ;
490 --
491 l_last_Updated_by := FND_GLOBAL.User_Id;
492 IF l_last_Updated_by IS NULL THEN
493 l_last_Updated_by := -1;
494 END IF ;
495 --
496 l_last_update_login := FND_GLOBAL.Login_Id ;
497 IF l_last_update_login IS NULL THEN
498 l_last_update_login := -1;
499 END IF;
500 --
501
502 UPDATE psb_set_relations
503 SET
504 set_relation_id = p_set_relation_id,
505 account_position_set_id = p_account_position_set_id,
506 allocation_rule_id = p_allocation_Rule_id,
507 budget_group_id = p_budget_group_id,
508 budget_workflow_rule_id = p_budget_workflow_rule_id,
509 constraint_id = p_constraint_id,
510 default_rule_id = p_default_Rule_id,
511 parameter_id = p_Parameter_Id,
512 position_set_group_id = p_position_set_group_id,
513 gl_budget_id = l_gl_budget_id,
514 /* Budget Revision Rules Enhancement Start */
515 rule_id = p_rule_id,
516 apply_balance_flag = p_apply_balance_flag,
517 /* Budget Revision Rules Enhancement End */
518 effective_start_date = p_effective_start_date,
519 effective_end_date = p_effective_end_date,
520 last_update_date = l_last_update_date,
521 last_updated_by = l_last_Updated_by,
522 last_update_login = l_last_update_login
523 WHERE rowid = p_row_id;
524
525 IF (SQL%NOTFOUND) THEN
526 RAISE NO_DATA_FOUND ;
527 END IF;
528 --
529 IF FND_API.To_Boolean ( p_commit ) THEN
530 COMMIT WORK;
531 END iF;
532 --
533 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
534 p_data => p_msg_data );
535 --
536 EXCEPTION
537 --
538 WHEN FND_API.G_EXC_ERROR THEN
539 --
540 ROLLBACK TO Update_Row_Pvt ;
541 p_return_status := FND_API.G_RET_STS_ERROR;
542 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
543 p_data => p_msg_data );
544 --
545 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
546 --
547 ROLLBACK TO Update_Row_Pvt ;
548 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
549 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
550 p_data => p_msg_data );
551 --
552 WHEN OTHERS THEN
553 --
554 ROLLBACK TO Update_Row_Pvt ;
555 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
556 --
557 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
558 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
559 l_api_name);
560 END if;
561 --
562 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
563 p_data => p_msg_data );
564 --
565 END Update_Row;
566 /* ----------------------------------------------------------------------- */
567
568
569
570 /*=========================================================================+
571 | PROCEDURE Delete_Row |
572 +=========================================================================*/
573 PROCEDURE Delete_Row
574 ( p_api_version IN NUMBER,
575 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
579 p_msg_count OUT NOCOPY NUMBER,
576 p_commit IN VARCHAR2 := FND_API.G_FALSE,
577 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
578 p_return_status OUT NOCOPY VARCHAR2,
580 p_msg_data OUT NOCOPY VARCHAR2,
581 --
582 p_row_id IN VARCHAR2
583 )
584 IS
585 --
586 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
587 l_api_version CONSTANT NUMBER := 1.0;
588 --
589 BEGIN
590 --
591 SAVEPOINT Delete_Row_Pvt ;
592 --
593 IF NOT FND_API.Compatible_API_Call ( l_api_version,
594 p_api_version,
595 l_api_name,
596 G_PKG_NAME )
597 THEN
598 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
599 END IF;
600 --
601
602 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
603 FND_MSG_PUB.initialize ;
604 END IF;
605 --
606 p_return_status := FND_API.G_RET_STS_SUCCESS ;
607 --
608
609 DELETE psb_set_relations
610 WHERE rowid = p_row_id;
611
612 IF (SQL%NOTFOUND) THEN
613 RAISE NO_DATA_FOUND ;
614 END IF;
615
616 --
617 IF FND_API.To_Boolean ( p_commit ) THEN
618 COMMIT WORK;
619 END iF;
620 --
621 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
622 p_data => p_msg_data );
623
624 EXCEPTION
625 --
626 WHEN FND_API.G_EXC_ERROR THEN
627 --
628 ROLLBACK TO Delete_Row_Pvt ;
629 p_return_status := FND_API.G_RET_STS_ERROR;
630 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
631 p_data => p_msg_data );
632 --
633 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
634 --
635 ROLLBACK TO Delete_Row_Pvt ;
636 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
637 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
638 p_data => p_msg_data );
639 --
640 WHEN OTHERS THEN
641 --
642 ROLLBACK TO Delete_Row_Pvt ;
643 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
644 --
645 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
646 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
647 l_api_name);
648 END if;
649 --
650 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
651 p_data => p_msg_data );
652 --
653 END Delete_Row;
654 /* ----------------------------------------------------------------------- */
655
656
657
658 /*=========================================================================+
659 | PROCEDURE Delete_Entity_Relation |
660 +=========================================================================*/
661 PROCEDURE Delete_Entity_Relation
662 (
663 p_api_version IN NUMBER,
664 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
665 p_commit IN VARCHAR2 := FND_API.G_FALSE,
666 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
667 p_return_status OUT NOCOPY VARCHAR2,
668 p_msg_count OUT NOCOPY NUMBER,
669 p_msg_data OUT NOCOPY VARCHAR2,
670 --
671 p_entity_type IN VARCHAR2,
672 p_entity_id IN NUMBER
673 )
674 IS
675 --
676 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Entity_Relation';
677 l_api_version CONSTANT NUMBER := 1.0;
678 --
679 BEGIN
680 --
681 SAVEPOINT Delete_Entity_Relation_Pvt ;
682 --
683 IF NOT FND_API.Compatible_API_Call ( l_api_version,
684 p_api_version,
685 l_api_name,
686 G_PKG_NAME )
687 THEN
688 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
689 END IF;
690 --
691
692 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
693 FND_MSG_PUB.initialize ;
694 END IF;
695 --
696 p_return_status := FND_API.G_RET_STS_SUCCESS ;
697 --
698
699 --
700 -- Finding all the sets for the given p_entity_id
701 --
702 FOR l_relations_rec IN
703 (
704 SELECT account_position_set_id ,
705 account_or_position_type ,
706 global_or_local_type ,
707 set_relation_id
708 FROM psb_set_relations_v
709 WHERE DECODE( p_entity_type,
710 'AR', allocation_rule_id,
711 'BG', budget_group_id,
712 'BWR', budget_workflow_rule_id,
713 'C', constraint_id,
714 'DR', default_rule_id,
715 'P', parameter_id,
716 'PSG', position_set_group_id,
717 'GBS', gl_budget_id,
718 /* Budget Revision Rules Enhancement Start */
719 'BRR', rule_id
720 /* Budget Revision Rules Enhancement End */
721 ) = p_entity_id
722 )
723 LOOP
724
725 IF l_relations_rec.global_or_local_type = 'L' OR p_entity_type = 'BG' THEN
726
727 --
728 -- Delete all the set line values for position set related set lines.
729 --
733 WHERE line_sequence_id IN
730 IF l_relations_rec.account_or_position_type = 'P' THEN
731
732 DELETE psb_position_set_line_values
734 (
735 SELECT line_sequence_id
736 FROM psb_account_position_set_lines
737 WHERE account_position_set_id =
738 l_relations_rec.account_position_set_id
739 ) ;
740
741 END IF ;
742
743 --
744 -- Delete all the set lines for Local sets.
745 --
746 DELETE psb_account_position_set_lines
747 WHERE account_position_set_id =
748 l_relations_rec.account_position_set_id ;
749
750 --
751 -- Delete the set.
752 --
753 DELETE psb_account_position_sets
754 WHERE account_position_set_id = l_relations_rec.account_position_set_id ;
755 --
756 END IF;
757
758 --
759 -- Delete the relation.
760 --
761 DELETE psb_set_relations
762 WHERE set_relation_id = l_relations_rec.set_relation_id;
763
764 END LOOP;
765
766 /* Bug 1308558 Start */
767 -- There is no need for this check as above are implicit cursors
768 -- and the following condition will always become true and so will
769 -- raise the error message.
770 /*IF (SQL%NOTFOUND) THEN
771 RAISE FND_API.G_EXC_ERROR ;
772 END IF;*/
773 /* Bug 1308558 End */
774
775
776 --
777 IF FND_API.To_Boolean ( p_commit ) THEN
778 COMMIT WORK;
779 END iF;
780 --
781 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
782 p_data => p_msg_data );
783 --
784 EXCEPTION
785 --
786 WHEN FND_API.G_EXC_ERROR THEN
787 --
788 ROLLBACK TO Delete_Entity_Relation_Pvt ;
789 p_return_status := FND_API.G_RET_STS_ERROR;
790 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
791 p_data => p_msg_data );
792 --
793 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
794 --
795 ROLLBACK TO Delete_Entity_Relation_Pvt ;
796 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
797 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
798 p_data => p_msg_data );
799 --
800 WHEN OTHERS THEN
801 --
802 ROLLBACK TO Delete_Entity_Relation_Pvt ;
803 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
804 --
805 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
806 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
807 l_api_name);
808 END if;
809 --
810 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
811 p_data => p_msg_data );
812 --
813
814
815 END Delete_Entity_Relation;
816 /* ----------------------------------------------------------------------- */
817
818
819
820 /*=========================================================================+
821 | PROCEDURE Check_Unique |
822 +=========================================================================*/
823 --
824 -- This procedure is called to check duplicate global sets for a given
825 -- entity.
826 --
827 PROCEDURE Check_Unique
828 (
829 p_api_version IN NUMBER,
830 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
831 p_commit IN VARCHAR2 := FND_API.G_FALSE,
832 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
833 p_return_status OUT NOCOPY VARCHAR2,
834 p_msg_count OUT NOCOPY NUMBER,
835 p_msg_data OUT NOCOPY VARCHAR2,
836 --
837 p_row_id IN VARCHAR2,
838 p_account_position_set_id IN NUMBER,
839 p_account_or_position_Type IN VARCHAR2,
840 p_entity_Type IN VARCHAR2,
841 p_entity_Id IN NUMBER,
842 /* Bug No 2131841 Start */
843 p_apply_balance_flag IN VARCHAR2,
844 /* Bug No 2131841 End */
845 p_return_value IN OUT NOCOPY VARCHAR2
846 )
847 IS
848 --
849 l_api_name CONSTANT VARCHAR2(30) := 'Check_Unique';
850 l_api_version CONSTANT NUMBER := 1.0;
851 --
852 l_tmp VARCHAR2(1);
853
854 CURSOR c IS
855 SELECT '1'
856 FROM psb_set_relations_v
857 WHERE account_or_position_type = p_account_or_position_type
858 AND DECODE( p_entity_type,
859 'AR', allocation_rule_id,
860 'BG', budget_group_id,
861 'BWR', budget_workflow_rule_id,
862 'C', constraint_id,
863 'DR', default_rule_id,
864 'P', parameter_id,
865 'PSG', position_set_group_id,
866 'GBS', gl_budget_id,
867 /* Budget Revision Rules Enhancement Start */
868 'BRR', rule_id
869 /* Budget Revision Rules Enhancement End */
870 ) = p_entity_id
871
872 AND account_position_set_id = p_account_position_set_id
873 AND ( (p_row_id IS NULL)
874 OR (Row_Id <> p_row_id) );
875
876 /* Bug No 2131841 Start */
877 CURSOR c1 IS
878 SELECT '1'
879 FROM psb_set_relations_v
880 WHERE account_or_position_type = p_account_or_position_type
881 AND DECODE( p_entity_type,
882 'AR', allocation_rule_id,
883 'BG', budget_group_id,
884 'BWR', budget_workflow_rule_id,
885 'C', constraint_id,
886 'DR', default_rule_id,
887 'P', parameter_id,
888 'PSG', position_set_group_id,
889 'GBS', gl_budget_id,
890 'BRR', rule_id
891 ) = p_entity_id
892
893 AND account_position_set_id = p_account_position_set_id
894 AND ( (p_row_id IS NULL)
895 OR (Row_Id <> p_row_id) )
896 AND apply_balance_flag = p_apply_balance_flag;
897 /* Bug No 2131841 End */
898
899 BEGIN
900 --
901 SAVEPOINT Check_Unique_Pvt ;
902 --
903 IF NOT FND_API.Compatible_API_Call ( l_api_version,
904 p_api_version,
905 l_api_name,
906 G_PKG_NAME )
907 THEN
908 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
909 END IF;
910 --
911
912 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
913 FND_MSG_PUB.initialize ;
914 END IF;
915 --
916 p_return_status := FND_API.G_RET_STS_SUCCESS ;
917 --
918
919
920 -- Checking the Psb_set_relations table for references.
921 /* Bug No 2131841 Start */
922 IF p_entity_type = 'BRR' THEN
923 OPEN c1;
924 FETCH c1 INTO l_tmp;
925 ELSE
926 OPEN c;
927 FETCH c INTO l_tmp;
928 END IF;
929 /* BUG NO 2131841 END */
930
931 -- p_return_value tells whether references exist or not.
932 IF l_tmp IS NULL THEN
933 p_return_value := 'FALSE';
934 ELSE
935 p_return_value := 'TRUE';
936 END IF;
937
938 /* Bug No 2131841 Start */
939 IF p_entity_type = 'BRR' THEN
940 CLOSE c1;
941 ELSE
942 CLOSE c;
943 END IF;
944 /* Bug No 2131841 End */
945
946 --
947 IF FND_API.To_Boolean ( p_commit ) THEN
948 COMMIT WORK;
949 END iF;
950 --
951 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
952 p_data => p_msg_data );
953 --
954 EXCEPTION
955 --
956 WHEN FND_API.G_EXC_ERROR THEN
957 --
958 ROLLBACK TO Check_Unique_Pvt ;
959 p_return_status := FND_API.G_RET_STS_ERROR;
960 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
961 p_data => p_msg_data );
962 --
963 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
964 --
965 ROLLBACK TO Check_Unique_Pvt ;
966 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
967 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
968 p_data => p_msg_data );
969 --
970 WHEN OTHERS THEN
971 --
972 ROLLBACK TO Check_Unique_Pvt ;
973 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
974 --
975 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
976 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
977 l_api_name);
978 END if;
979 --
980 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
981 p_data => p_msg_data );
982 --
983 END Check_Unique;
984 /* ----------------------------------------------------------------------- */
985
986
987 END PSB_Set_Relation_PVT;