[Home] [Help]
PACKAGE BODY: APPS.PSB_WORKSHEET_CONSOLIDATE_PVT
Source
1 PACKAGE BODY PSB_WORKSHEET_CONSOLIDATE_PVT AS
2 /* $Header: PSBPWCDB.pls 120.2 2005/07/13 11:22:58 shtripat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_WORKSHEET_CONSOLIDATE_PVT';
5
6 /* ----------------------------------------------------------------------- */
7
8 PROCEDURE Consolidate_Worksheets
9 ( p_api_version IN NUMBER,
10 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
11 p_commit IN VARCHAR2 := FND_API.G_FALSE,
12 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
13 p_return_status OUT NOCOPY VARCHAR2,
14 p_msg_count OUT NOCOPY NUMBER,
15 p_msg_data OUT NOCOPY VARCHAR2,
16 p_global_worksheet_id IN NUMBER
17 ) IS
18
19 l_api_name CONSTANT VARCHAR2(30) := 'Consolidate_Worksheets';
20 l_api_version CONSTANT NUMBER := 1.0;
21
22 BEGIN
23
24 -- Standard Start of API savepoint
25
26 SAVEPOINT Consolidate_Worksheets_Pvt;
27
28
29 -- Standard call to check for call compatibility.
30
31 if not FND_API.Compatible_API_Call (l_api_version,
32 p_api_version,
33 l_api_name,
34 G_PKG_NAME)
35 then
36 raise FND_API.G_EXC_UNEXPECTED_ERROR;
37 end if;
38
39
40 -- Initialize message list if p_init_msg_list is set to TRUE.
41
42 if FND_API.to_Boolean (p_init_msg_list) then
43 FND_MSG_PUB.initialize;
44 end if;
45
46
47 -- Call Private Function
48
49 PSB_WORKSHEET_CONSOLIDATE.Consolidate_Worksheets
50 (p_api_version => 1.0,
51 p_return_status => p_return_status,
52 p_global_worksheet_id => p_global_worksheet_id);
53
54 -- Standard check of p_commit.
55
56 if FND_API.to_Boolean (p_commit) then
57 commit work;
58 end if;
59
60
61 -- Standard call to get message count and if count is 1, get message info.
62
63 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
64 p_data => p_msg_data);
65
66 EXCEPTION
67
68 when FND_API.G_EXC_ERROR then
69 rollback to Consolidate_Worksheets_Pvt;
70 p_return_status := FND_API.G_RET_STS_ERROR;
71
72 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
73 p_data => p_msg_data);
74
75 when FND_API.G_EXC_UNEXPECTED_ERROR then
76 rollback to Consolidate_Worksheets_Pvt;
77 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
78
79 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
80 p_data => p_msg_data);
81
82 when OTHERS then
83 rollback to Consolidate_Worksheets_Pvt;
84 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
85
86 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
87 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
88 l_api_name);
89 end if;
90
91 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
92 p_data => p_msg_data);
93
94 END Consolidate_Worksheets;
95
96 /* ----------------------------------------------------------------------- */
97
98 PROCEDURE Validate_Consolidation
99 ( p_api_version IN NUMBER,
100 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
101 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
102 p_return_status OUT NOCOPY VARCHAR2,
103 p_msg_count OUT NOCOPY NUMBER,
104 p_msg_data OUT NOCOPY VARCHAR2,
105 p_global_worksheet_id IN NUMBER
106 ) IS
107
108 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Consolidation';
109 l_api_version CONSTANT NUMBER := 1.0;
110
111 BEGIN
112
113 -- Standard call to check for call compatibility.
114
115 if not FND_API.Compatible_API_Call (l_api_version,
116 p_api_version,
117 l_api_name,
118 G_PKG_NAME)
119 then
120 raise FND_API.G_EXC_UNEXPECTED_ERROR;
121 end if;
122
123
124 -- Initialize message list if p_init_msg_list is set to TRUE.
125
126 if FND_API.to_Boolean (p_init_msg_list) then
127 FND_MSG_PUB.initialize;
128 end if;
129
130
131 -- Call Private Function
132
133 PSB_WORKSHEET_CONSOLIDATE.Validate_Consolidation
134 (p_api_version => 1.0,
135 p_return_status => p_return_status,
136 p_global_worksheet_id => p_global_worksheet_id);
137
138 -- Standard call to get message count and if count is 1, get message info.
139
140 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
141 p_data => p_msg_data);
142
143 EXCEPTION
144
145 when FND_API.G_EXC_ERROR then
146 p_return_status := FND_API.G_RET_STS_ERROR;
147
148 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
149 p_data => p_msg_data);
150
151 when FND_API.G_EXC_UNEXPECTED_ERROR then
152 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
153
154 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
155 p_data => p_msg_data);
156
157 when OTHERS then
158 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
159
160 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
161 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
162 l_api_name);
163 end if;
164
165 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
166 p_data => p_msg_data);
167
168 END Validate_Consolidation;
169
170 /* ----------------------------------------------------------------------- */
171
172 PROCEDURE Insert_Row
173 (
174 p_api_version IN NUMBER,
175 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
176 p_commit IN VARCHAR2 := FND_API.G_FALSE,
177 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
178 p_return_status OUT NOCOPY VARCHAR2,
179 p_msg_count OUT NOCOPY NUMBER,
180 p_msg_data OUT NOCOPY VARCHAR2,
181
182 p_row_id IN OUT NOCOPY VARCHAR2,
183 p_global_worksheet_id IN NUMBER,
184 p_local_worksheet_id IN NUMBER,
185 p_last_update_date IN DATE,
186 p_last_updated_by IN NUMBER,
187 p_last_update_login IN NUMBER,
188 p_created_by IN NUMBER,
189 p_creation_date IN DATE,
190 p_attribute1 IN VARCHAR2,
191 p_attribute2 IN VARCHAR2,
192 p_attribute3 IN VARCHAR2,
193 p_attribute4 IN VARCHAR2,
194 p_attribute5 IN VARCHAR2,
195 p_attribute6 IN VARCHAR2,
196 p_attribute7 IN VARCHAR2,
197 p_attribute8 IN VARCHAR2,
198 p_attribute9 IN VARCHAR2,
199 p_attribute10 IN VARCHAR2,
200 p_context IN VARCHAR2
201 )
202 IS
203
204 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
205 l_api_version CONSTANT NUMBER := 1.0;
206
207 CURSOR C IS
208 SELECT rowid
209 FROM psb_ws_consolidation_details
210 WHERE global_worksheet_id = p_global_worksheet_id
211 AND local_worksheet_id = p_local_worksheet_id;
212
213 BEGIN
214
215 SAVEPOINT Insert_Row_Pvt ;
216
217 IF NOT FND_API.Compatible_API_Call ( l_api_version,
218 p_api_version,
219 l_api_name,
220 G_PKG_NAME )
221 THEN
222 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
223 END IF;
224
225
226 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
227 FND_MSG_PUB.initialize ;
228 END IF;
229
230 p_return_status := FND_API.G_RET_STS_SUCCESS ;
231
232 INSERT INTO psb_ws_consolidation_details(
233 global_worksheet_id,
234 local_worksheet_id,
235 last_update_date ,
236 last_updated_by,
237 last_update_login,
238 created_by,
239 creation_date,
240 attribute1,
241 attribute2,
242 attribute3,
243 attribute4,
244 attribute5,
245 attribute6,
246 attribute7,
247 attribute8,
248 attribute9,
249 attribute10,
250 context)
251 VALUES
252 (
253 p_global_worksheet_id,
254 p_local_worksheet_id,
255 p_last_update_date ,
256 p_last_updated_by,
257 p_last_update_login,
258 p_created_by,
259 p_creation_date,
260 p_attribute1,
261 p_attribute2,
262 p_attribute3,
263 p_attribute4,
264 p_attribute5,
265 p_attribute6,
266 p_attribute7,
267 p_attribute8,
268 p_attribute9,
269 p_attribute10,
270 p_context);
271
272 OPEN C;
273 FETCH C INTO p_row_id;
274 IF (C%NOTFOUND) THEN
275 CLOSE C;
276 RAISE FND_API.G_EXC_ERROR ;
277 END IF;
278 CLOSE C;
279
280
281 IF FND_API.To_Boolean ( p_commit ) THEN
282 COMMIT WORK;
283 END iF;
284
285 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
286 p_data => p_msg_data );
287
288 EXCEPTION
289
290 WHEN FND_API.G_EXC_ERROR THEN
291
292 ROLLBACK TO Insert_Row_Pvt ;
293 p_return_status := FND_API.G_RET_STS_ERROR;
294 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
295 p_data => p_msg_data );
296
297 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
298
299 ROLLBACK TO Insert_Row_Pvt ;
300 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
301 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
302 p_data => p_msg_data );
303
304 WHEN OTHERS THEN
305
306 ROLLBACK TO Insert_Row_Pvt ;
307 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
308
309 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
310 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
311 l_api_name);
312 END if;
313
314 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
315 p_data => p_msg_data );
316
317 END Insert_Row;
318
319
320 PROCEDURE Lock_Row
321 (
322 p_api_version IN NUMBER,
323 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
324 p_commit IN VARCHAR2 := FND_API.G_FALSE,
325 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
326 p_return_status OUT NOCOPY VARCHAR2,
327 p_msg_count OUT NOCOPY NUMBER,
328 p_msg_data OUT NOCOPY VARCHAR2,
329
330 p_row_id IN VARCHAR2,
331 p_global_worksheet_id IN NUMBER,
332 p_local_worksheet_id IN NUMBER,
333 p_attribute1 IN VARCHAR2,
334 p_attribute2 IN VARCHAR2,
335 p_attribute3 IN VARCHAR2,
336 p_attribute4 IN VARCHAR2,
337 p_attribute5 IN VARCHAR2,
338 p_attribute6 IN VARCHAR2,
339 p_attribute7 IN VARCHAR2,
340 p_attribute8 IN VARCHAR2,
341 p_attribute9 IN VARCHAR2,
342 p_attribute10 IN VARCHAR2,
343 p_context IN VARCHAR2,
344
345 p_row_locked OUT NOCOPY VARCHAR2
346 )
347 IS
348
349 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
350 l_api_version CONSTANT NUMBER := 1.0;
351
352 Counter NUMBER;
353 CURSOR C IS
354 SELECT *
355 FROM psb_ws_consolidation_details
356 WHERE rowid = p_row_id
357 FOR UPDATE NOWAIT;
358 Recinfo C%ROWTYPE;
359
360 BEGIN
361
362 SAVEPOINT Lock_Row_Pvt ;
363
364 IF NOT FND_API.Compatible_API_Call ( l_api_version,
365 p_api_version,
366 l_api_name,
367 G_PKG_NAME )
368 THEN
369 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
370 END IF;
371
372
373 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
374 FND_MSG_PUB.initialize ;
375 END IF;
376
377 p_return_status := FND_API.G_RET_STS_SUCCESS ;
378 p_row_locked := FND_API.G_TRUE ;
379
380 OPEN C;
381
382 FETCH C INTO Recinfo;
383 IF (C%NOTFOUND) then
384 CLOSE C;
385 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
386 FND_MSG_PUB.Add;
387 RAISE FND_API.G_EXC_ERROR ;
388 END IF;
389 CLOSE C;
390 IF
391 (
392 (Recinfo.global_worksheet_id = p_global_worksheet_id) AND
393 (Recinfo.local_worksheet_id = p_local_worksheet_id)
394
395 AND ( (Recinfo.attribute1 = p_attribute1)
396 OR ( (Recinfo.attribute1 IS NULL)
397 AND (p_attribute1 IS NULL)))
398
399 AND ( (Recinfo.attribute2 = p_attribute2)
400 OR ( (Recinfo.attribute2 IS NULL)
401 AND (p_attribute2 IS NULL)))
402
403 AND ( (Recinfo.attribute3 = p_attribute3)
404 OR ( (Recinfo.attribute3 IS NULL)
405 AND (p_attribute3 IS NULL)))
406
407 AND ( (Recinfo.attribute4 = p_attribute4)
408 OR ( (Recinfo.attribute4 IS NULL)
409 AND (p_attribute4 IS NULL)))
410
411 AND ( (Recinfo.attribute5 = p_attribute5)
412 OR ( (Recinfo.attribute5 IS NULL)
413 AND (p_attribute5 IS NULL)))
414
415 AND ( (Recinfo.attribute6 = p_attribute6)
416 OR ( (Recinfo.attribute6 IS NULL)
417 AND (p_attribute6 IS NULL)))
418
419 AND ( (Recinfo.attribute7 = p_attribute7)
420 OR ( (Recinfo.attribute7 IS NULL)
421 AND (p_attribute7 IS NULL)))
422
423 AND ( (Recinfo.attribute8 = p_attribute8)
424 OR ( (Recinfo.attribute8 IS NULL)
425 AND (p_attribute8 IS NULL)))
426
427 AND ( (Recinfo.attribute9 = p_attribute9)
428 OR ( (Recinfo.attribute9 IS NULL)
429 AND (p_attribute9 IS NULL)))
430
431 AND ( (Recinfo.attribute10 = p_attribute10)
432 OR ( (Recinfo.attribute10 IS NULL)
433 AND (p_attribute10 IS NULL)))
434 AND ( (Recinfo.context = p_context)
435 OR ( (Recinfo.context IS NULL)
436 AND (p_context IS NULL)))
437 )
438
439 THEN
440 Null;
441 ELSE
442 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
443 FND_MSG_PUB.Add;
444 RAISE FND_API.G_EXC_ERROR ;
445 END IF;
446
447
448 IF FND_API.To_Boolean ( p_commit ) THEN
449 COMMIT WORK;
450 END iF;
451
452 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
453 p_data => p_msg_data );
454
455 EXCEPTION
456
457 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
458
459 ROLLBACK TO Lock_Row_Pvt ;
460 p_row_locked := FND_API.G_FALSE;
461 p_return_status := FND_API.G_RET_STS_ERROR;
462 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
463 p_data => p_msg_data );
464
465 WHEN FND_API.G_EXC_ERROR THEN
466
467 ROLLBACK TO Lock_Row_Pvt ;
468 p_return_status := FND_API.G_RET_STS_ERROR;
469 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
470 p_data => p_msg_data );
471
472 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
473
474 ROLLBACK TO Lock_Row_Pvt ;
475 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
479 WHEN OTHERS THEN
476 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
477 p_data => p_msg_data );
478
480
481 ROLLBACK TO Lock_Row_Pvt ;
482 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
483
484 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
485 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
486 l_api_name);
487 END if;
488
489 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
490 p_data => p_msg_data );
491
492 END Lock_Row;
493
494
495 PROCEDURE Update_Row
496 (
497 p_api_version IN NUMBER,
498 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
499 p_commit IN VARCHAR2 := FND_API.G_FALSE,
500 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
501 p_return_status OUT NOCOPY VARCHAR2,
502 p_msg_count OUT NOCOPY NUMBER,
503 p_msg_data OUT NOCOPY VARCHAR2,
504
505 p_row_id IN VARCHAR2,
506 p_global_worksheet_id IN NUMBER,
507 p_local_worksheet_id IN NUMBER,
508 p_last_update_date IN DATE,
509 p_last_updated_by IN NUMBER,
510 p_last_update_login IN NUMBER,
511 p_attribute1 IN VARCHAR2,
512 p_attribute2 IN VARCHAR2,
513 p_attribute3 IN VARCHAR2,
514 p_attribute4 IN VARCHAR2,
515 p_attribute5 IN VARCHAR2,
516 p_attribute6 IN VARCHAR2,
517 p_attribute7 IN VARCHAR2,
518 p_attribute8 IN VARCHAR2,
519 p_attribute9 IN VARCHAR2,
520 p_attribute10 IN VARCHAR2,
521 p_context IN VARCHAR2
522 )
523 IS
524
525 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
526 l_api_version CONSTANT NUMBER := 1.0;
527
528 BEGIN
529
530 SAVEPOINT Update_Row_Pvt ;
531
532 IF NOT FND_API.Compatible_API_Call ( l_api_version,
533 p_api_version,
534 l_api_name,
535 G_PKG_NAME )
536 THEN
537 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
538 END IF;
539
540
541 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
542 FND_MSG_PUB.initialize ;
543 END IF;
544
545 p_return_status := FND_API.G_RET_STS_SUCCESS ;
546
547
548 UPDATE psb_ws_consolidation_details
549 SET
550 global_worksheet_id = p_global_worksheet_id ,
551 local_worksheet_id = p_local_worksheet_id ,
552 last_update_date = p_last_update_date ,
553 last_updated_by = p_last_updated_by ,
554 last_update_login = p_last_update_login ,
555 attribute1 = p_attribute1 ,
556 attribute2 = p_attribute2 ,
557 attribute3 = p_attribute3 ,
558 attribute4 = p_attribute4 ,
559 attribute5 = p_attribute5 ,
560 attribute6 = p_attribute6 ,
561 attribute7 = p_attribute7 ,
562 attribute8 = p_attribute8 ,
563 attribute9 = p_attribute9 ,
564 attribute10 = p_attribute10,
565 context = p_context
566 WHERE rowid = p_row_id;
567
568 IF (SQL%NOTFOUND) THEN
569 RAISE NO_DATA_FOUND ;
570 END IF;
571
572
573 IF FND_API.To_Boolean ( p_commit ) THEN
574 COMMIT WORK;
575 END iF;
576
577 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
578 p_data => p_msg_data );
579
580 EXCEPTION
581
582 WHEN FND_API.G_EXC_ERROR THEN
583
584 ROLLBACK TO Update_Row_Pvt ;
585 p_return_status := FND_API.G_RET_STS_ERROR;
586 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
587 p_data => p_msg_data );
588
589 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
590
591 ROLLBACK TO Update_Row_Pvt ;
592 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
593 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
594 p_data => p_msg_data );
595
596 WHEN OTHERS THEN
597
598 ROLLBACK TO Update_Row_Pvt ;
599 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
600
601 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
602 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
603 l_api_name);
604 END if;
605
606 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
607 p_data => p_msg_data );
608
609 END Update_Row;
610
611
612 PROCEDURE Delete_Row
613 (
614 p_api_version IN NUMBER,
615 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
616 p_commit IN VARCHAR2 := FND_API.G_FALSE,
617 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
618 p_return_status OUT NOCOPY VARCHAR2,
622 p_row_id IN VARCHAR2
619 p_msg_count OUT NOCOPY NUMBER,
620 p_msg_data OUT NOCOPY VARCHAR2,
621
623 )
624 IS
625
626 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
627 l_api_version CONSTANT NUMBER := 1.0;
628
629 l_return_status VARCHAR2(1) ;
630 l_msg_count NUMBER ;
631 l_msg_data VARCHAR2(2000) ;
632
633 BEGIN
634
635 SAVEPOINT Delete_Row_Pvt ;
636
637 IF NOT FND_API.Compatible_API_Call ( l_api_version,
638 p_api_version,
639 l_api_name,
640 G_PKG_NAME )
641 THEN
642 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
643 END IF;
644
645
646 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
647 FND_MSG_PUB.initialize ;
648 END IF ;
649
650 p_return_status := FND_API.G_RET_STS_SUCCESS ;
651
652
653 DELETE FROM psb_ws_consolidation_details
654 WHERE rowid = p_row_id;
655
656 IF (SQL%NOTFOUND) THEN
657 RAISE NO_DATA_FOUND ;
658 END IF;
659
660
661 IF FND_API.To_Boolean ( p_commit ) THEN
662 COMMIT WORK;
663 END iF;
664
665 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
666 p_data => p_msg_data );
667
668 EXCEPTION
669
670 WHEN FND_API.G_EXC_ERROR THEN
671
672 ROLLBACK TO Delete_Row_Pvt ;
673 p_return_status := FND_API.G_RET_STS_ERROR;
674 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
675 p_data => p_msg_data );
676
677 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
678
679 ROLLBACK TO Delete_Row_Pvt ;
680 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
681 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
682 p_data => p_msg_data );
683
684 WHEN OTHERS THEN
685
686 ROLLBACK TO Delete_Row_Pvt ;
687 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
688
689 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
690 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
691 l_api_name);
692 END if;
693
694 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
695 p_data => p_msg_data );
696
697 END Delete_Row;
698
699
700 PROCEDURE Check_Unique
701 (
702 p_api_version IN NUMBER,
703 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
704 p_commit IN VARCHAR2 := FND_API.G_FALSE,
705 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
706 p_return_status OUT NOCOPY VARCHAR2,
707 p_msg_count OUT NOCOPY NUMBER,
708 p_msg_data OUT NOCOPY VARCHAR2,
709 --
710 p_row_id IN VARCHAR2,
711 p_global_worksheet_id IN NUMBER,
712 p_local_worksheet_id IN NUMBER,
713 p_return_value IN OUT NOCOPY VARCHAR2
714 )
715 IS
716
717 l_api_name CONSTANT VARCHAR2(30) := 'Check_Unique';
718 l_api_version CONSTANT NUMBER := 1.0;
719
720 l_tmp VARCHAR2(1);
721
722 CURSOR c IS
723 SELECT '1'
724 FROM psb_ws_consolidation_details
725 WHERE global_worksheet_id = p_global_worksheet_id
726 AND local_worksheet_id = p_local_worksheet_id;
727
728 BEGIN
729
730 SAVEPOINT Check_Unique_Pvt ;
731
732 IF NOT FND_API.Compatible_API_Call ( l_api_version,
733 p_api_version,
734 l_api_name,
735 G_PKG_NAME )
736 THEN
737 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
738 END IF;
739
740
741 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
742 FND_MSG_PUB.initialize ;
743 END IF;
744
745 p_return_status := FND_API.G_RET_STS_SUCCESS ;
746
747 -- Checking the Psb_ws_consolidations table for references.
748 OPEN c;
749 FETCH c INTO l_tmp;
750
751 -- p_Return_Value specifies whether unique value exists or not.
752 IF l_tmp IS NULL THEN
753 p_Return_Value := 'FALSE';
754 ELSE
755 p_Return_Value := 'TRUE';
756 END IF;
757
758 CLOSE c;
759
760 IF FND_API.To_Boolean ( p_commit ) THEN
761 COMMIT WORK;
762 END iF;
763
764 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
765 p_data => p_msg_data );
766
767 EXCEPTION
768
769 WHEN FND_API.G_EXC_ERROR THEN
770
771 ROLLBACK TO Check_Unique_Pvt ;
772 p_return_status := FND_API.G_RET_STS_ERROR;
773 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
774 p_data => p_msg_data );
775
776 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
777
778 ROLLBACK TO Check_Unique_Pvt ;
779 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
780 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
781 p_data => p_msg_data );
782
783 WHEN OTHERS THEN
784
785 ROLLBACK TO Check_Unique_Pvt ;
786 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
787
788 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
789 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
790 l_api_name);
791 END if;
792
793 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
794 p_data => p_msg_data );
795
796 END Check_Unique;
797
798
799 /*===========================================================================+
800 | PROCEDURE Worksheet_Consolidate_CP |
801 +===========================================================================*/
802 --
803 -- This is the execution file for the concurrent program 'Worksheet_Consolidate'
804 --
805 PROCEDURE Worksheet_Consolidate_CP
806 (
807 errbuf OUT NOCOPY VARCHAR2 ,
808 retcode OUT NOCOPY VARCHAR2 ,
809 --
810 p_worksheet_id IN NUMBER
811 )
812 IS
813 l_api_name CONSTANT VARCHAR2(30) := 'Worksheet_Consolidate_CP';
814 l_api_version CONSTANT NUMBER := 1.0 ;
815 --
816 l_error_api_name VARCHAR2(2000);
817 l_return_status VARCHAR2(1) ;
818 l_msg_count NUMBER ;
819 l_msg_data VARCHAR2(2000) ;
820 l_msg_index_out NUMBER;
821
822 BEGIN
823
824 PSB_WORKSHEET_CONSOLIDATE_PVT.Validate_Consolidation
825 (p_api_version => 1.0,
826 p_init_msg_list => FND_API.G_TRUE,
827 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
828 p_return_status => l_return_status,
829 p_msg_count => l_msg_count,
830 p_msg_data => l_msg_data,
831 p_global_worksheet_id => p_worksheet_id
832 );
833
834 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
835 raise FND_API.G_EXC_ERROR;
836 end if;
837
838 PSB_WORKSHEET_CONSOLIDATE_PVT.Consolidate_Worksheets
839 (p_api_version => 1.0,
840 p_init_msg_list => FND_API.G_TRUE,
841 p_commit => FND_API.G_TRUE,
842 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
843 p_return_status => l_return_status,
844 p_msg_count => l_msg_count,
845 p_msg_data => l_msg_data,
846 p_global_worksheet_id => p_worksheet_id
847 );
848
849 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
850 raise FND_API.G_EXC_ERROR;
851 end if;
852
853 PSB_MESSAGE_S.Print_Success;
854 retcode := 0 ;
855
856 EXCEPTION
857
858 WHEN FND_API.G_EXC_ERROR THEN
859 --
860 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
861 p_print_header => FND_API.G_TRUE );
862 retcode := 2 ;
863 COMMIT WORK ;
864 --
865 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
866 --
867 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
868 p_print_header => FND_API.G_TRUE );
869 retcode := 2 ;
870 COMMIT WORK ;
871 --
872 WHEN OTHERS THEN
873 --
874 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
875 --
876 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
877 l_api_name ) ;
878 END IF ;
879 --
880 l_return_status := FND_API.G_RET_STS_ERROR;
881
882 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
883 p_print_header => FND_API.G_TRUE );
884 --
885 retcode := 2 ;
886 COMMIT WORK ;
887 --
888 END Worksheet_Consolidate_CP;
889 /* ----------------------------------------------------------------------- */
890
891
892 END PSB_WORKSHEET_CONSOLIDATE_PVT;