[Home] [Help]
PACKAGE BODY: APPS.PSB_CONCURRENCY_CONTROL_PVT
Source
1 PACKAGE BODY PSB_CONCURRENCY_CONTROL_PVT AS
2 /* $Header: PSBVCCLB.pls 120.3 2005/08/01 09:48:04 sudagarw ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_CONCURRENCY_CONTROL_PVT';
5
6 TYPE TokNameArray IS TABLE OF VARCHAR2(100)
7 INDEX BY BINARY_INTEGER;
8
9 TYPE TokValArray IS TABLE OF VARCHAR2(1000)
10 INDEX BY BINARY_INTEGER;
11
12 -- Number of Message Tokens
13
14 no_msg_tokens NUMBER := 0;
15
16 -- Message Token Name
17
18 msg_tok_names TokNameArray;
19
20 -- Message Token Value
21
22 msg_tok_val TokValArray;
23
24 g_dbug VARCHAR2(1000);
25
26
27
28 /* ----------------------------------------------------------------------- */
29 /* */
30 /* Private Function Definition */
31 /* */
32 /* ----------------------------------------------------------------------- */
33
34 PROCEDURE message_token
35 ( tokname IN VARCHAR2,
36 tokval IN VARCHAR2
37 );
38
39 PROCEDURE add_message
40 ( appname IN VARCHAR2,
41 msgname IN VARCHAR2
42 );
43
44 /* ----------------------------------------------------------------------- */
45
46 PROCEDURE Enforce_Concurrency_Control
47 ( p_api_version IN NUMBER,
48 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
49 p_return_status OUT NOCOPY VARCHAR2,
50 p_concurrency_class IN VARCHAR2,
51 p_concurrency_entity_name IN VARCHAR2,
52 p_concurrency_entity_id IN NUMBER
53 ) IS
54
55 l_ws_lock VARCHAR2(128);
56 l_ws_handle VARCHAR2(128);
57 l_ws_status INTEGER;
58
59 l_bg_lock VARCHAR2(128);
60 l_bg_handle VARCHAR2(128);
61 l_bg_status INTEGER;
62
63 l_bc_lock VARCHAR2(128);
64 l_bc_handle VARCHAR2(128);
65 l_bc_status INTEGER;
66
67 l_ps_lock VARCHAR2(128);
68 l_ps_handle VARCHAR2(128);
69 l_ps_status INTEGER;
70
71 l_cs_lock VARCHAR2(128);
72 l_cs_handle VARCHAR2(128);
73 l_cs_status INTEGER;
74
75 l_ar_lock VARCHAR2(128);
76 l_ar_handle VARCHAR2(128);
77 l_ar_status INTEGER;
78
79 l_de_lock VARCHAR2(128);
80 l_de_handle VARCHAR2(128);
81 l_de_status INTEGER;
82
83 l_br_lock VARCHAR2(128);
84 l_br_handle VARCHAR2(128);
85 l_br_status INTEGER;
86
87 l_pa_lock VARCHAR2(128);
88 l_pa_handle VARCHAR2(128);
89 l_pa_status INTEGER;
90
91 l_api_name CONSTANT VARCHAR2(30) := 'Enforce_Concurrency_Control';
92 l_api_version CONSTANT NUMBER := 1.0;
93
94 cursor c_Worksheet is
95 select worksheet_id,
96 budget_group_id,
97 budget_calendar_id,
98 nvl(parameter_set_id, global_parameter_set_id) parameter_set_id,
99 nvl(constraint_set_id, global_constraint_set_id) constraint_set_id,
100 nvl(allocrule_set_id, global_allocrule_set_id) allocrule_set_id,
101 nvl(data_extract_id, global_data_extract_id) data_extract_id
102 from PSB_WORKSHEETS_V
103 where worksheet_id = p_concurrency_entity_id;
104
105 cursor c_Budget_Revision is
106 select budget_revision_id,
107 budget_group_id,
108 parameter_set_id,
109 constraint_set_id
110 from psb_budget_revisions_v
111 where budget_revision_id = p_concurrency_entity_id;
112
113 BEGIN
114
115 -- Standard call to check for call compatibility
116
117 if not FND_API.Compatible_API_Call (l_api_version,
118 p_api_version,
119 l_api_name,
120 G_PKG_NAME)
121 then
122 raise FND_API.G_EXC_UNEXPECTED_ERROR;
123 end if;
124
125 if p_concurrency_class = 'WORKSHEET_CREATION' then
126 begin
127
128 if p_concurrency_entity_name = 'WORKSHEET' then
129 begin
130
131 for c_Worksheet_Rec in c_Worksheet loop
132
133 l_ws_lock := 'PSB%WS' || c_Worksheet_Rec.worksheet_id;
134 l_bg_lock := 'PSB%BG' || c_Worksheet_Rec.budget_group_id;
135 l_bc_lock := 'PSB%BC' || c_Worksheet_Rec.budget_calendar_id;
136 l_ps_lock := 'PSB%PS' || c_Worksheet_Rec.parameter_set_id;
137
138 if c_Worksheet_Rec.constraint_set_id is not null then
139 l_cs_lock := 'PSB%CS' || c_Worksheet_Rec.constraint_set_id;
140 end if;
141
142 if c_Worksheet_Rec.allocrule_set_id is not null then
143 l_ar_lock := 'PSB%AR' || c_Worksheet_Rec.allocrule_set_id;
144 end if;
145
146 if c_Worksheet_Rec.data_extract_id is not null then
147 l_de_lock := 'PSB%DE' || c_Worksheet_Rec.data_extract_id;
148 end if;
149
150 end loop;
151
152 dbms_lock.allocate_unique (lockname => l_ws_lock,
153 lockhandle => l_ws_handle,
154 expiration_secs => 86400);
155
156 l_ws_status := dbms_lock.request (lockhandle => l_ws_handle,
157 lockmode => dbms_lock.s_mode,
158 timeout => 300);
159
160 if l_ws_status <> 0 then
161 raise FND_API.G_EXC_ERROR;
162 end if;
163
164 dbms_lock.allocate_unique (lockname => l_bg_lock,
165 lockhandle => l_bg_handle,
166 expiration_secs => 86400);
167
168 l_bg_status := dbms_lock.request (lockhandle => l_bg_handle,
169 lockmode => dbms_lock.s_mode,
170 timeout => 300);
171
172 if l_bg_status <> 0 then
173 raise FND_API.G_EXC_ERROR;
174 end if;
175
176 dbms_lock.allocate_unique (lockname => l_bc_lock,
177 lockhandle => l_bc_handle,
178 expiration_secs => 86400);
179
180 l_bc_status := dbms_lock.request (lockhandle => l_bc_handle,
181 lockmode => dbms_lock.s_mode,
182 timeout => 300);
183
184 if l_bc_status <> 0 then
185 raise FND_API.G_EXC_ERROR;
186 end if;
187
188 dbms_lock.allocate_unique (lockname => l_ps_lock,
189 lockhandle => l_ps_handle,
190 expiration_secs => 86400);
191
192 l_ps_status := dbms_lock.request (lockhandle => l_ps_handle,
193 lockmode => dbms_lock.s_mode,
194 timeout => 300);
195
196 if l_ps_status <> 0 then
197 raise FND_API.G_EXC_ERROR;
198 end if;
199
200 if l_cs_lock is not null then
201 begin
202
203 dbms_lock.allocate_unique (lockname => l_cs_lock,
204 lockhandle => l_cs_handle,
205 expiration_secs => 86400);
206
207 l_cs_status := dbms_lock.request (lockhandle => l_cs_handle,
208 lockmode => dbms_lock.s_mode,
209 timeout => 300);
210
211 if l_cs_status <> 0 then
212 raise FND_API.G_EXC_ERROR;
213 end if;
214
215 end;
216 end if;
217
218 if l_ar_lock is not null then
219 begin
220
221 dbms_lock.allocate_unique (lockname => l_ar_lock,
222 lockhandle => l_ar_handle,
223 expiration_secs => 86400);
224
225 l_ar_status := dbms_lock.request (lockhandle => l_ar_handle,
226 lockmode => dbms_lock.s_mode,
227 timeout => 300);
228
229 if l_ar_status <> 0 then
230 raise FND_API.G_EXC_ERROR;
231 end if;
232
233 end;
234 end if;
235
236 if l_de_lock is not null then
237 begin
238
239 dbms_lock.allocate_unique (lockname => l_de_lock,
240 lockhandle => l_de_handle,
241 expiration_secs => 86400);
242
243 l_de_status := dbms_lock.request (lockhandle => l_de_handle,
244 lockmode => dbms_lock.s_mode,
245 timeout => 300);
246
247 if l_de_status <> 0 then
248 raise FND_API.G_EXC_ERROR;
249 end if;
250
251 end;
252 end if;
253
254 end;
255 end if;
256
257 end;
258 elsif p_concurrency_class = 'BUDGET_REVISION_CREATION' then
259 begin
260
261 if p_concurrency_entity_name = 'BUDGET_REVISION' then
262 begin
263
264 for c_Revision_Rec in c_Budget_Revision loop
265
266 l_br_lock := 'PSB%BR' || c_Revision_Rec.budget_revision_id;
267 l_bg_lock := 'PSB%BG' || c_Revision_Rec.budget_group_id;
268
269 if c_Revision_Rec.parameter_set_id is not null then
270 l_ps_lock := 'PSB%PS' || c_Revision_Rec.parameter_set_id;
271 end if;
272
273 if c_Revision_Rec.constraint_set_id is not null then
274 l_cs_lock := 'PSB%CS' || c_Revision_Rec.constraint_set_id;
275 end if;
276
277 end loop;
278
279 dbms_lock.allocate_unique (lockname => l_br_lock,
280 lockhandle => l_br_handle,
281 expiration_secs => 86400);
282
283 l_br_status := dbms_lock.request (lockhandle => l_br_handle,
284 lockmode => dbms_lock.s_mode,
285 timeout => 300);
286
287 if l_br_status <> 0 then
288 raise FND_API.G_EXC_ERROR;
289 end if;
290
291 dbms_lock.allocate_unique (lockname => l_bg_lock,
292 lockhandle => l_bg_handle,
293 expiration_secs => 86400);
294
295 l_bg_status := dbms_lock.request (lockhandle => l_bg_handle,
296 lockmode => dbms_lock.s_mode,
297 timeout => 300);
298
299 if l_bg_status <> 0 then
300 raise FND_API.G_EXC_ERROR;
301 end if;
302
303 if l_ps_lock is not null then
304 begin
305 dbms_lock.allocate_unique (lockname => l_ps_lock,
306 lockhandle => l_ps_handle,
307 expiration_secs => 86400);
308
309 l_ps_status := dbms_lock.request (lockhandle => l_ps_handle,
310 lockmode => dbms_lock.s_mode,
311 timeout => 300);
312
313 if l_ps_status <> 0 then
314 raise FND_API.G_EXC_ERROR;
315 end if;
316 end;
317 end if;
318
319 if l_cs_lock is not null then
320 begin
321
322 dbms_lock.allocate_unique (lockname => l_cs_lock,
323 lockhandle => l_cs_handle,
324 expiration_secs => 86400);
325
326 l_cs_status := dbms_lock.request (lockhandle => l_cs_handle,
327 lockmode => dbms_lock.s_mode,
328 timeout => 300);
329
330 if l_cs_status <> 0 then
331 raise FND_API.G_EXC_ERROR;
332 end if;
333
334 end;
335 end if;
336
337 end;
338 end if;
339
340 end;
341 elsif p_concurrency_class = 'DATAEXTRACT_CREATION' then
342 begin
343
344 if p_concurrency_entity_name = 'DATA_EXTRACT' then
345 begin
346
347 l_de_lock := 'PSB%DE' || p_concurrency_entity_id;
348
349 dbms_lock.allocate_unique (lockname => l_de_lock,
350 lockhandle => l_de_handle,
351 expiration_secs => 86400);
352
353 l_de_status := dbms_lock.request (lockhandle => l_de_handle,
354 lockmode => dbms_lock.s_mode,
355 timeout => 300);
356
357 if l_de_status <> 0 then
358 raise FND_API.G_EXC_ERROR;
359 end if;
360
361 end;
362 end if;
363
364 end;
365 elsif p_concurrency_class = 'WORKSHEET_CONSOLIDATION' then
366 begin
367
368 if p_concurrency_entity_name = 'WORKSHEET' then
369 begin
370
371 l_ws_lock := 'PSB%WS' || p_concurrency_entity_id;
372
373 dbms_lock.allocate_unique (lockname => l_ws_lock,
374 lockhandle => l_ws_handle,
375 expiration_secs => 86400);
376
377 l_ws_status := dbms_lock.request (lockhandle => l_ws_handle,
378 timeout => 300,
379 release_on_commit => TRUE);
380
381 if l_ws_status <> 0 then
382 raise FND_API.G_EXC_ERROR;
383 end if;
384
385 end;
386 elsif p_concurrency_entity_name = 'DATA_EXTRACT' then
387 begin
388
389 l_de_lock := 'PSB%DE' || p_concurrency_entity_id;
390
391 dbms_lock.allocate_unique (lockname => l_de_lock,
392 lockhandle => l_de_handle,
393 expiration_secs => 86400);
394
395 l_de_status := dbms_lock.request (lockhandle => l_de_handle,
396 timeout => 300,
397 release_on_commit => TRUE);
398
399 if l_de_status <> 0 then
400 raise FND_API.G_EXC_ERROR;
401 end if;
402
403 end;
404 end if;
405
406 end;
407 elsif p_concurrency_class = 'PROJECTIONS' then
408 begin
409
410 if p_concurrency_entity_name = 'PARAMETER' then
411 begin
412
413 l_pa_lock := 'PSB%PA' || p_concurrency_entity_id;
414
415 dbms_lock.allocate_unique (lockname => l_pa_lock,
416 lockhandle => l_pa_handle,
417 expiration_secs => 86400);
418
419 l_pa_status := dbms_lock.request (lockhandle => l_pa_handle,
420 timeout => 300,
421 release_on_commit => TRUE);
422
423 if l_pa_status <> 0 then
424 raise FND_API.G_EXC_ERROR;
425 end if;
426
427 end;
428 end if;
429 end;
430
431 /* For Bug 4337768 Start */
432
433 elsif p_concurrency_class = 'MODIFY_WORKSHEET' then
434 begin
435
436 if p_concurrency_entity_name = 'WORKSHEET' then
437 begin
438
439 l_ws_lock := 'PSB%WS' || p_concurrency_entity_id;
440
441 dbms_lock.allocate_unique (lockname => l_ws_lock,
442 lockhandle => l_ws_handle,
443 expiration_secs => 86400);
444
445 l_ws_status := dbms_lock.request (lockhandle => l_ws_handle,
446 lockmode => dbms_lock.s_mode,
447 timeout => 1,
448 release_on_commit => TRUE);
449
450 if l_ws_status <> 0 then
451 raise FND_API.G_EXC_ERROR;
452 end if;
453
454 end;
455 end if;
456 end;
457
458 /* For Bug 4337768 End */
459
460 else
461 begin
462
463 if p_concurrency_entity_name = 'WORKSHEET' then
464 begin
465
466 l_ws_lock := 'PSB%WS' || p_concurrency_entity_id;
467
468 dbms_lock.allocate_unique (lockname => l_ws_lock,
469 lockhandle => l_ws_handle,
470 expiration_secs => 86400);
471
475
472 l_ws_status := dbms_lock.request (lockhandle => l_ws_handle,
473 timeout => 1,
474 release_on_commit => TRUE);
476 if l_ws_status <> 0 then
477 raise FND_API.G_EXC_ERROR;
478 end if;
479
480 end;
481 elsif p_concurrency_entity_name = 'BUDGET_REVISION' then
482 begin
483 l_br_lock := 'PSB%BR' || p_concurrency_entity_id;
484
485 dbms_lock.allocate_unique (lockname => l_br_lock,
486 lockhandle => l_br_handle,
487 expiration_secs => 86400);
488
489 l_br_status := dbms_lock.request (lockhandle => l_br_handle,
490 timeout => 1,
491 release_on_commit => TRUE);
492
493 if l_br_status <> 0 then
494 raise FND_API.G_EXC_ERROR;
495 end if;
496
497 end;
498 elsif p_concurrency_entity_name = 'BUDGET_GROUP' then
499 begin
500
501 l_bg_lock := 'PSB%BG' || p_concurrency_entity_id;
502
503 dbms_lock.allocate_unique (lockname => l_bg_lock,
504 lockhandle => l_bg_handle,
505 expiration_secs => 86400);
506
507 l_bg_status := dbms_lock.request (lockhandle => l_bg_handle,
508 timeout => 1,
509 release_on_commit => TRUE);
510
511 if l_bg_status <> 0 then
512 raise FND_API.G_EXC_ERROR;
513 end if;
514
515 end;
516 elsif p_concurrency_entity_name = 'BUDGET_CALENDAR' then
517 begin
518
519 l_bc_lock := 'PSB%BC' || p_concurrency_entity_id;
520
521 dbms_lock.allocate_unique (lockname => l_bc_lock,
522 lockhandle => l_bc_handle,
523 expiration_secs => 86400);
524
525 l_bc_status := dbms_lock.request (lockhandle => l_bc_handle,
526 timeout => 1,
527 release_on_commit => TRUE);
528
529 if l_bc_status <> 0 then
530 raise FND_API.G_EXC_ERROR;
531 end if;
532
533 end;
534 elsif p_concurrency_entity_name = 'PARAMETER_SET' then
535 begin
536
537 l_ps_lock := 'PSB%PS' || p_concurrency_entity_id;
538
539 dbms_lock.allocate_unique (lockname => l_ps_lock,
540 lockhandle => l_ps_handle,
541 expiration_secs => 86400);
542
543 l_ps_status := dbms_lock.request (lockhandle => l_ps_handle,
544 timeout => 1,
545 release_on_commit => TRUE);
546
547 if l_ps_status <> 0 then
548 raise FND_API.G_EXC_ERROR;
549 end if;
550
551 end;
552 elsif p_concurrency_entity_name = 'CONSTRAINT_SET' then
553 begin
554
555 l_cs_lock := 'PSB%CS' || p_concurrency_entity_id;
556
557 dbms_lock.allocate_unique (lockname => l_cs_lock,
558 lockhandle => l_cs_handle,
559 expiration_secs => 86400);
560
561 l_cs_status := dbms_lock.request (lockhandle => l_cs_handle,
562 timeout => 1,
563 release_on_commit => TRUE);
564
565 if l_cs_status <> 0 then
566 raise FND_API.G_EXC_ERROR;
567 end if;
568
569 end;
570 elsif p_concurrency_entity_name = 'ALLOCRULE_SET' then
571 begin
572
573 l_ar_lock := 'PSB%AR' || p_concurrency_entity_id;
574
575 dbms_lock.allocate_unique (lockname => l_ar_lock,
576 lockhandle => l_ar_handle,
577 expiration_secs => 86400);
578
579 l_ar_status := dbms_lock.request (lockhandle => l_ar_handle,
580 timeout => 1,
581 release_on_commit => TRUE);
582
583 if l_ar_status <> 0 then
584 raise FND_API.G_EXC_ERROR;
585 end if;
586
587 end;
588 elsif p_concurrency_entity_name = 'DATA_EXTRACT' then
589 begin
590
591 l_de_lock := 'PSB%DE' || p_concurrency_entity_id;
592
593 dbms_lock.allocate_unique (lockname => l_de_lock,
594 lockhandle => l_de_handle,
595 expiration_secs => 86400);
596
597 l_de_status := dbms_lock.request (lockhandle => l_de_handle,
598 timeout => 1,
599 release_on_commit => TRUE);
600
601 if l_de_status <> 0 then
602 raise FND_API.G_EXC_ERROR;
603 end if;
604
605 end;
606 end if;
607
608 end;
609 end if;
610
611 p_return_status := FND_API.G_RET_STS_SUCCESS;
612
613
614 EXCEPTION
615
616 when FND_API.G_EXC_ERROR then
617 add_message('PSB', 'PSB_CONC_LOCK');
618 p_return_status := FND_API.G_RET_STS_ERROR;
619
620 when FND_API.G_EXC_UNEXPECTED_ERROR then
621 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
622
623 when OTHERS then
624
625 if FND_MSG_PUB.Check_Msg_Level
626 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
627
628 FND_MSG_PUB.Add_Exc_Msg
629 (p_pkg_name => G_PKG_NAME,
630 p_procedure_name => l_api_name);
631
632 end if;
633
634 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
635
636 END Enforce_Concurrency_Control;
637
638 /* ----------------------------------------------------------------------- */
642 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
639
640 PROCEDURE Release_Concurrency_Control
641 ( p_api_version IN NUMBER,
643 p_return_status OUT NOCOPY VARCHAR2,
644 p_concurrency_class IN VARCHAR2,
645 p_concurrency_entity_name IN VARCHAR2,
646 p_concurrency_entity_id IN NUMBER
647 ) IS
648
649 l_ws_lock VARCHAR2(128);
650 l_ws_handle VARCHAR2(128);
651 l_ws_status INTEGER;
652
653 l_bg_lock VARCHAR2(128);
654 l_bg_handle VARCHAR2(128);
655 l_bg_status INTEGER;
656
657 l_bc_lock VARCHAR2(128);
658 l_bc_handle VARCHAR2(128);
659 l_bc_status INTEGER;
660
661 l_ps_lock VARCHAR2(128);
662 l_ps_handle VARCHAR2(128);
663 l_ps_status INTEGER;
664
665 l_cs_lock VARCHAR2(128);
666 l_cs_handle VARCHAR2(128);
667 l_cs_status INTEGER;
668
669 l_ar_lock VARCHAR2(128);
670 l_ar_handle VARCHAR2(128);
671 l_ar_status INTEGER;
672
673 l_de_lock VARCHAR2(128);
674 l_de_handle VARCHAR2(128);
675 l_de_status INTEGER;
676
677 l_api_name CONSTANT VARCHAR2(30) := 'Release_Concurrency_Control';
678 l_api_version CONSTANT NUMBER := 1.0;
679
680 cursor c_Worksheet is
681 select worksheet_id,
682 budget_group_id,
683 budget_calendar_id,
684 nvl(parameter_set_id, global_parameter_set_id) parameter_set_id,
685 nvl(constraint_set_id, global_constraint_set_id) constraint_set_id,
686 nvl(allocrule_set_id, global_allocrule_set_id) allocrule_set_id,
687 nvl(data_extract_id, global_data_extract_id) data_extract_id
688 from PSB_WORKSHEETS_V
689 where worksheet_id = p_concurrency_entity_id;
690
691 BEGIN
692
693 -- Standard call to check for call compatibility
694
695 if not FND_API.Compatible_API_Call (l_api_version,
696 p_api_version,
697 l_api_name,
698 G_PKG_NAME)
699 then
700 raise FND_API.G_EXC_UNEXPECTED_ERROR;
701 end if;
702
703 if p_concurrency_class = 'WORKSHEET_CREATION' then
704 begin
705
706 if p_concurrency_entity_name = 'WORKSHEET' then
707 begin
708
709 for c_Worksheet_Rec in c_Worksheet loop
710
711 l_ws_lock := 'PSB%WS' || c_Worksheet_Rec.worksheet_id;
712 l_bg_lock := 'PSB%BG' || c_Worksheet_Rec.budget_group_id;
713 l_bc_lock := 'PSB%BC' || c_Worksheet_Rec.budget_calendar_id;
714 l_ps_lock := 'PSB%PS' || c_Worksheet_Rec.parameter_set_id;
715
716 if c_Worksheet_Rec.constraint_set_id is not null then
717 l_cs_lock := 'PSB%CS' || c_Worksheet_Rec.constraint_set_id;
718 end if;
719
720 if c_Worksheet_Rec.allocrule_set_id is not null then
721 l_ar_lock := 'PSB%AR' || c_Worksheet_Rec.allocrule_set_id;
722 end if;
723
724 if c_Worksheet_Rec.data_extract_id is not null then
725 l_de_lock := 'PSB%DE' || c_Worksheet_Rec.data_extract_id;
726 end if;
727
728 end loop;
729
730 dbms_lock.allocate_unique (lockname => l_ws_lock,
731 lockhandle => l_ws_handle,
732 expiration_secs => 86400);
733
734 l_ws_status := DBMS_LOCK.RELEASE(lockhandle => l_ws_handle);
735
736 if l_ws_status <> 0 then
737 raise FND_API.G_EXC_ERROR;
738 end if;
739
740 dbms_lock.allocate_unique (lockname => l_bg_lock,
741 lockhandle => l_bg_handle,
742 expiration_secs => 86400);
743
744 l_bg_status := DBMS_LOCK.RELEASE(lockhandle => l_bg_handle);
745
746 if l_bg_status <> 0 then
747 raise FND_API.G_EXC_ERROR;
748 end if;
749
750 dbms_lock.allocate_unique (lockname => l_bc_lock,
751 lockhandle => l_bc_handle,
752 expiration_secs => 86400);
753
754 l_bc_status := DBMS_LOCK.RELEASE(lockhandle => l_bc_handle);
755
756 if l_bc_status <> 0 then
757 raise FND_API.G_EXC_ERROR;
758 end if;
759
760 dbms_lock.allocate_unique (lockname => l_ps_lock,
761 lockhandle => l_ps_handle,
762 expiration_secs => 86400);
763
764 l_ps_status := DBMS_LOCK.RELEASE(lockhandle => l_ps_handle);
765
766 if l_ps_status <> 0 then
767 raise FND_API.G_EXC_ERROR;
768 end if;
769
770 if l_cs_lock is not null then
771 begin
772
773 dbms_lock.allocate_unique (lockname => l_cs_lock,
774 lockhandle => l_cs_handle,
775 expiration_secs => 86400);
776
777 l_cs_status := DBMS_LOCK.RELEASE(lockhandle => l_cs_handle);
778
779 if l_cs_status <> 0 then
780 raise FND_API.G_EXC_ERROR;
781 end if;
782
783 end;
784 end if;
785
786 if l_ar_lock is not null then
787 begin
788
792
789 dbms_lock.allocate_unique (lockname => l_ar_lock,
790 lockhandle => l_ar_handle,
791 expiration_secs => 86400);
793 l_ar_status := DBMS_LOCK.RELEASE(lockhandle => l_ar_handle);
794
795 if l_ar_status <> 0 then
796 raise FND_API.G_EXC_ERROR;
797 end if;
798
799 end;
800 end if;
801
802 if l_de_lock is not null then
803 begin
804
805 dbms_lock.allocate_unique (lockname => l_de_lock,
806 lockhandle => l_de_handle,
807 expiration_secs => 86400);
808
809 l_de_status := DBMS_LOCK.RELEASE(lockhandle => l_de_handle);
810
811 if l_de_status <> 0 then
812 raise FND_API.G_EXC_ERROR;
813 end if;
814
815 end;
816 end if;
817
818 end;
819 end if;
820
821 end;
822 elsif p_concurrency_class = 'DATAEXTRACT_CREATION' then
823 begin
824
825 if p_concurrency_entity_name = 'DATA_EXTRACT' then
826 begin
827
828 l_de_lock := 'PSB%DE' || p_concurrency_entity_id;
829
830 dbms_lock.allocate_unique (lockname => l_de_lock,
831 lockhandle => l_de_handle,
832 expiration_secs => 86400);
833
834 l_de_status := DBMS_LOCK.RELEASE(lockhandle => l_de_handle);
835
836 if l_de_status <> 0 then
837 raise FND_API.G_EXC_ERROR;
838 end if;
839
840 end;
841 end if;
842
843 end;
844 elsif p_concurrency_class = 'WORKSHEET_CONSOLIDATION' then
845 begin
846
847 if p_concurrency_entity_name = 'WORKSHEET' then
848 begin
849
850 l_ws_lock := 'PSB%WS' || p_concurrency_entity_id;
851
852 dbms_lock.allocate_unique (lockname => l_ws_lock,
853 lockhandle => l_ws_handle,
854 expiration_secs => 86400);
855
856 l_ws_status := DBMS_LOCK.RELEASE(lockhandle => l_ws_handle);
857
858 if l_ws_status <> 0 then
859 raise FND_API.G_EXC_ERROR;
860 end if;
861
862 end;
863 end if;
864 end;
865
866 /* For Bug 4337768 Start*/
867
868 elsif p_concurrency_class = 'MAINTENANCE' then
869 begin
870
871 if p_concurrency_entity_name = 'WORKSHEET' then
872 begin
873
874 l_ws_lock := 'PSB%WS' || p_concurrency_entity_id;
875
876 dbms_lock.allocate_unique (lockname => l_ws_lock,
877 lockhandle => l_ws_handle,
878 expiration_secs => 86400);
879
880 l_ws_status := DBMS_LOCK.RELEASE(lockhandle => l_ws_handle);
881
882 -- For locks requested with release_on_commit = true
883 -- l_ws_status will be 4 since allocate_unique will
884 -- commit and release the lock. 4 is for 'do not own
885 -- a lock specified by id or lockhandle'.
886
887 if l_ws_status <> 0 and l_ws_status <> 4 then
888 raise FND_API.G_EXC_ERROR;
889 end if;
890
891 end;
892 end if;
893 end;
894
895 /* For Bug 4337768 End */
896
897 end if;
898 p_return_status := FND_API.G_RET_STS_SUCCESS;
899
900
901 EXCEPTION
902
903 when FND_API.G_EXC_ERROR then
904 p_return_status := FND_API.G_RET_STS_ERROR;
905
906 when FND_API.G_EXC_UNEXPECTED_ERROR then
907 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
908
909 when OTHERS then
910
911 if FND_MSG_PUB.Check_Msg_Level
912 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
913
914 FND_MSG_PUB.Add_Exc_Msg
915 (p_pkg_name => G_PKG_NAME,
916 p_procedure_name => l_api_name);
917
918 end if;
919
920 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
921
922 END Release_Concurrency_Control;
923
924 /* ----------------------------------------------------------------------- */
925
926 -- Add Token and Value to the Message Token array
927
928 PROCEDURE message_token(tokname IN VARCHAR2,
929 tokval IN VARCHAR2) IS
930
931 BEGIN
932
933 if no_msg_tokens is null then
934 no_msg_tokens := 1;
935 else
936 no_msg_tokens := no_msg_tokens + 1;
937 end if;
938
939 msg_tok_names(no_msg_tokens) := tokname;
940 msg_tok_val(no_msg_tokens) := tokval;
941
942 END message_token;
943
944 /* ----------------------------------------------------------------------- */
945
946 -- Define a Message Token with a Value and set the Message Name
947
948 -- Calls FND_MESSAGE server package to set the Message Stack. This message is
949 -- retrieved by the calling program.
950
951 PROCEDURE add_message(appname IN VARCHAR2,
952 msgname IN VARCHAR2) IS
953
954 i BINARY_INTEGER;
955
956 BEGIN
957
958 if ((appname is not null) and
959 (msgname is not null)) then
960
961 FND_MESSAGE.SET_NAME(appname, msgname);
962
963 if no_msg_tokens is not null then
964 for i in 1..no_msg_tokens loop
965 FND_MESSAGE.SET_TOKEN(msg_tok_names(i), msg_tok_val(i));
966 end loop;
967 end if;
968
969 FND_MSG_PUB.Add;
970
971 end if;
972
973 -- Clear Message Token stack
974
975 no_msg_tokens := 0;
976
977 END add_message;
978
979 /* ----------------------------------------------------------------------- */
980
981 -- Get Debug Information
982
983 -- This Module is used to retrieve Debug Information for this Package. It
984 -- prints Debug Information when run as a Batch Process from SQL*Plus. For
985 -- the Debug Information to be printed on the Screen, the SQL*Plus parameter
986 -- 'Serveroutput' should be set to 'ON'
987
988 FUNCTION Get_Debug RETURN VARCHAR2 IS
989
990 BEGIN
991
992 return(g_dbug);
993
994 END Get_Debug;
995
996 /* ----------------------------------------------------------------------- */
997
998 END PSB_CONCURRENCY_CONTROL_PVT;