[Home] [Help]
PACKAGE BODY: APPS.PA_PAGE_LAYOUT_PVT
Source
1 package body PA_PAGE_LAYOUT_PVT as
2 /* $Header: PAPRPLVB.pls 120.1 2005/08/19 16:44:50 mwasowic noship $ */
3
4 --History
5 -- 16-Feb-2004 svenketa - Modified, Added a parameter p_function_name for create and update.
6 -- 17-Feb-2004 svenketa - Modified the DELETE_PAGE_LAYOUT api.
7
8
9 PROCEDURE Create_Page_Layout
10 (
11 p_api_version IN NUMBER := 1.0,
12 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
13 p_commit IN VARCHAR2 := FND_API.g_false,
14 p_validate_only IN VARCHAR2 := FND_API.g_true,
15 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
16
17 p_page_name IN VARCHAR2 := FND_API.g_miss_char,
18
19 p_page_type IN VARCHAR2 := FND_API.g_miss_char,
20
21 p_description IN VARCHAR2 := FND_API.g_miss_char,
22
23 p_start_date IN date,
24
25 p_end_date IN date,
26 p_shortcut_menu_id IN number,
27 p_shortcut_menu_name IN VARCHAR2,
28 p_function_name IN VARCHAR2,
29 x_page_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
30 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
31 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
32 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
33 ) IS
34
35 l_dummy VARCHAR2(1);
36
37 CURSOR get_template_type
38 is
39 SELECT meaning FROM pa_lookups pl
40 WHERE lookup_type = 'PA_PAGE_TYPES'
41 AND lookup_code = p_page_type;
42
43 CURSOR is_menu_id_required
44 IS
45 select 'Y' from dual
46 where exists(
47 select 'Required'
48 from pa_lookups l
49 where l.lookup_type = 'PA_PAGE_TYPES'
50 and l.lookup_code = p_page_type
51 AND L.attribute2 = 'Y'
52 );
53
54 CURSOR get_menu_id
55 IS
56 SELECT MENU_ID
57 FROM FND_MENUS_VL
58 where user_menu_name = p_shortcut_menu_name;
59
60 cursor get_menu_name
61 is
62 select page_id
63 from pa_page_layouts
64 where page_name = p_page_name and
65 page_type_code = p_page_type;
66
67 l_type VARCHAR2(250);
68 l_menu_id NUMBER;
69 l_page_id number;
70
71
72 BEGIN
73 -- Initialize the Error Stack
74 PA_DEBUG.init_err_stack('PA_PAGE_LAYOUT_PVT.Create_Page_Layout');
75
76 -- Initialize the return status to success
77 x_return_status := FND_API.G_RET_STS_SUCCESS;
78
79 -- Issue API savepoint if the transaction is to be committed
80 IF p_commit = FND_API.G_TRUE THEN
81 SAVEPOINT PAGE_LAYOUT_PVT_CREATE;
82 END IF;
83
84 OPEN get_template_type;
85 FETCH get_template_type INTO l_type;
86 CLOSE get_template_type;
87
88
89 IF p_shortcut_menu_id IS NOT NULL AND p_shortcut_menu_id <> -99 THEN
90 l_menu_id := p_shortcut_menu_id;
91 ELSE
92
93 OPEN is_menu_id_required;
94 FETCH is_menu_id_required INTO l_dummy;
95 IF is_menu_id_required%notfound THEN
96 CLOSE is_menu_id_required;
97 l_menu_id := p_shortcut_menu_id;
98 ELSE
99 CLOSE is_menu_id_required;
100
101 OPEN get_menu_id;
102 FETCH get_menu_id INTO l_menu_id;
103
104 IF get_menu_id%notfound THEN
105 CLOSE get_menu_id;
106 PA_UTILS.Add_Message( p_app_short_name => 'PA'
107 ,p_msg_name => 'PA_MENU_NAME_INV'
108 );
109 x_return_status := FND_API.G_RET_STS_ERROR;
110 RETURN;
111 ELSE
112 CLOSE get_menu_id;
113 END IF;
114
115 END IF;
116 END IF;
117
118
119 -- check the mandatory page_name
120 IF (p_page_name IS NULL OR p_page_name = FND_API.g_miss_char) then
121 PA_UTILS.Add_Message( p_app_short_name => 'PA'
122 ,p_msg_name => 'PA_PAGE_NAME_INV'
123 , p_token1 => 'TEMPLATE_TYPE'
124 , p_value1 => l_type);
125 x_return_status := FND_API.G_RET_STS_ERROR;
126 ELSE
127
128 -- added by syao check if the name already exists
129 open get_menu_name;
130 fetch get_menu_name into l_page_id;
131
132
133 IF get_menu_name%found THEN
134 CLOSE get_menu_name;
135 PA_UTILS.Add_Message( p_app_short_name => 'PA'
136 ,p_msg_name => 'PA_PAGE_NAME_NOT_UNIQUE'
137 );
138 x_return_status := FND_API.G_RET_STS_ERROR;
139 RETURN;
140 end if;
141 close get_menu_name;
142 -- end
143
144
145 -- check the page type is not null
146 IF (p_page_type IS NULL OR p_page_type = FND_API.g_miss_char)THEN
147 PA_UTILS.Add_Message( p_app_short_name => 'PA'
148 ,p_msg_name => 'PA_PAGE_TYPE_INV'
149 , p_token1 => 'TEMPLATE_TYPE'
150 , p_value1 => l_type);
151 x_return_status := FND_API.G_RET_STS_ERROR;
152 END IF;
153
154 -- check the end date and start date
155
156
157 IF (p_end_date IS NOT NULL AND p_end_date < p_start_date) THEN
158 PA_UTILS.Add_Message( p_app_short_name => 'PA'
159 ,p_msg_name => 'PA_EFFECTIVE_ED_DATE_INV');
160
161 x_return_status := FND_API.G_RET_STS_ERROR;
162 END IF;
163
164
165 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
166
167 --debug_msg ('shore menu id = ' || to_char(p_shortcut_menu_id));
168 pa_page_layout_pkg.Insert_page_layout_Row
169 (
170 p_page_name => p_page_name,
171 p_page_type => p_page_type,
172 p_description => p_description,
173 p_start_date => p_start_date,
174 p_end_date => p_end_date,
175 p_shortcut_menu_id => l_menu_id,
176 p_function_name => p_function_name,
177 x_page_id => x_page_id,
178 x_return_status => x_return_status,
179 x_msg_count => x_msg_count,
180 x_msg_data => x_msg_data
181 );
182
183 END IF;
184
185
186 -- Commit if the flag is set and there is no error
187 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
188 COMMIT;
189 END IF;
190
191 END IF;
192
193 -- Reset the error stack when returning to the calling program
194 PA_DEBUG.Reset_Err_Stack;
195
196
197 EXCEPTION
198 WHEN OTHERS THEN
199 IF p_commit = FND_API.G_TRUE THEN
200 ROLLBACK TO PAGE_LAYOUT_PVT_CREATE;
201 END IF;
202 --
203 -- Set the excetption Message and the stack
204 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PAGE_LAYOUT_PVT.Create_Page_Layout'
205 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
206 --
207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
208 RAISE; -- This is optional depending on the needs
209
210 END create_page_layout;
211
212
213
214 PROCEDURE Update_Page_Layout
215 (
216 p_api_version IN NUMBER := 1.0,
217 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
218 p_commit IN VARCHAR2 := FND_API.g_false,
219 p_validate_only IN VARCHAR2 := FND_API.g_true,
220 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
221
222 p_page_id IN number,
223
224 p_page_name IN VARCHAR2 := FND_API.g_miss_char,
225
226 -- p_page_type IN VARCHAR2 := FND_API.g_miss_char,
227
228 p_description IN VARCHAR2 := FND_API.g_miss_char,
229
230 p_start_date IN date,
231
232 p_end_date IN date,
233 p_shortcut_menu_id IN number,
234 p_shortcut_menu_name IN VARCHAR2,
235 p_record_version_number IN NUMBER,
236 p_function_name IN VARCHAR2,
237 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
238 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
239 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
240 ) IS
241
242 l_name_exists VARCHAR(20) := 'N';
243
244 l_req VARCHAR2(1);
245
246 CURSOR is_menu_id_required
247 IS
248 select 'Y' from dual
249 where exists(
250 select 'Required'
251 from pa_lookups l, pa_page_layouts ppl
252 where l.lookup_type = 'PA_PAGE_TYPES'
253 and l.lookup_code = ppl.page_type_code
254 AND L.attribute2 = 'Y'
255 and ppl.page_id = p_page_id
256 );
257
258 CURSOR get_menu_id
259 IS
260 SELECT MENU_ID
261 FROM FND_MENUS_VL
262 where user_menu_name = p_shortcut_menu_name;
263
264 CURSOR check_record_version IS
265 SELECT ROWID
266 FROM pa_page_layouts
267 WHERE page_id = p_page_id
268 AND record_version_number = p_record_version_number;
269 l_page_layout_row_id ROWID;
270
271 CURSOR get_template_type
272 is
273 SELECT meaning FROM pa_lookups pl, pa_page_layouts ppl
274 WHERE pl.lookup_type = 'PA_PAGE_TYPES'
275 AND pl.lookup_code = ppl.PAGE_TYPE_CODE
276 AND ppl.page_id = p_page_id;
277
278 l_menu_id NUMBER;
279 l_dummy VARCHAR2(10) := 'Y';
280 l_type VARCHAR2(250);
281
282 BEGIN
283
284 -- Initialize the Error Stack
285 PA_DEBUG.init_err_stack('PA_PAGE_LAYOUT_PVT.Create_Page_Layout');
286
287 -- Initialize the return status to success
288 x_return_status := FND_API.G_RET_STS_SUCCESS;
289
290 -- Issue API savepoint if the transaction is to be committed
291 IF p_commit = FND_API.G_TRUE THEN
292 SAVEPOINT PAGE_LAYOUT_PVT_UPDATE;
293 END IF;
294
295 IF p_shortcut_menu_id IS NOT NULL AND p_shortcut_menu_id <> -99 THEN
296 l_menu_id := p_shortcut_menu_id;
297 ELSE
298
299 OPEN is_menu_id_required;
300 FETCH is_menu_id_required INTO l_req;
301 IF is_menu_id_required%notfound THEN
302 CLOSE is_menu_id_required;
303 l_menu_id := p_shortcut_menu_id;
304 ELSE
305 CLOSE is_menu_id_required;
306
307 OPEN get_menu_id;
308 FETCH get_menu_id INTO l_menu_id;
309
310 IF get_menu_id%notfound THEN
311 CLOSE get_menu_id;
312 PA_UTILS.Add_Message( p_app_short_name => 'PA'
313 ,p_msg_name => 'PA_MENU_NAME_INV'
314 );
315 x_return_status := FND_API.G_RET_STS_ERROR;
316 RETURN;
317 ELSE
318 CLOSE get_menu_id;
319 END IF;
320 END IF;
321
322 END IF;
323
324 OPEN get_template_type;
325 FETCH get_template_type INTO l_type;
326 CLOSE get_template_type;
327
328 -- check the page id is not null
329 IF (p_page_id IS NULL OR p_page_id = FND_API.g_miss_num)THEN
330 PA_UTILS.Add_Message( p_app_short_name => 'PA'
331 ,p_msg_name => 'PA_PAGE_ID_INV'
332 , p_token1 => 'TEMPLATE_TYPE'
333 , p_value1 => l_type);
334 x_return_status := FND_API.G_RET_STS_ERROR;
335 END IF;
336
337 -- check the mandatory page name is not null
338 IF (p_page_name IS NULL OR p_page_name = FND_API.g_miss_char)THEN
339 PA_UTILS.Add_Message( p_app_short_name => 'PA'
340 ,p_msg_name => 'PA_PAGE_NAME_INV'
341 , p_token1 => 'TEMPLATE_TYPE'
342 , p_value1 => l_type
343 );
344 x_return_status := FND_API.G_RET_STS_ERROR;
345 --ELSE
346 -- check the page name is unique
347 -- OPEN check_page_name;
348 -- FETCH check_page_name INTO l_name_exists;
349 --CLOSE check_page_name;
350
351 --IF l_names_exists = 'Y' THEN
352 --PA_UTILS.Add_Message( p_app_short_name => 'PA'
353 -- ,p_msg_name => 'PA_PAGE_NAME_EXISTS');
354 --x_return_status := FND_API.G_RET_STS_ERROR;
355 --END IF;
356 END IF;
357
358 IF (p_end_date IS NOT NULL AND p_end_date < p_start_date) THEN
359 PA_UTILS.Add_Message( p_app_short_name => 'PA'
360 ,p_msg_name => 'PA_EFFECTIVE_ED_DATE_INV');
361
362 x_return_status := FND_API.G_RET_STS_ERROR;
363 END IF;
364
365
366 -- check the page type is not null
367 -- IF (p_page_type IS NULL OR p_page_type = FND_API.g_miss_char)THEN
368 -- PA_UTILS.Add_Message( p_app_short_name => 'PA'
369 -- ,p_msg_name => 'PA_PAGE_TYPE_INV');
370 --x_return_status := FND_API.G_RET_STS_ERROR;
371 --END IF;
372
373 IF x_return_status = FND_API.g_ret_sts_success then
374 -- check the record version number
375 OPEN check_record_version;
376
377 FETCH check_record_version INTO l_page_layout_row_id;
378
379 IF check_record_version%NOTFOUND THEN
380 CLOSE check_record_version;
381 PA_UTILS.Add_Message( p_app_short_name => 'PA'
382 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
383
384 x_return_status := FND_API.G_RET_STS_ERROR;
385
386 ELSE
387 CLOSE check_record_version;
388 pa_page_layout_pkg.Update_page_layout_Row
389 (
390 p_page_id => p_page_id,
391 p_page_name => p_page_name,
392 p_page_type => null,
393 p_description => p_description,
394 p_start_date => p_start_date,
395 p_end_date => p_end_date,
396 p_shortcut_menu_id => l_menu_id,
397 P_RECORD_VERSION_NUMBER => p_record_version_number,
398 p_function_name => p_function_name,
399 x_return_status => x_return_status,
400 x_msg_count => x_msg_count,
401 x_msg_data => x_msg_data
402 );
403 END IF;
404
405 END IF;
406
407 -- Commit if the flag is set and there is no error
408 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success) THEN
409 COMMIT;
410 END IF;
411
412 -- Reset the error stack when returning to the calling program
413 PA_DEBUG.Reset_Err_Stack;
414
415
416 EXCEPTION
417 WHEN OTHERS THEN
418 IF p_commit = FND_API.G_TRUE THEN
419 ROLLBACK TO PAGE_LAYOUT_PVT_UPDATE;
420 END IF;
421 --
422 -- Set the excetption Message and the stack
423 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PAGE_LAYOUT_PVT.Update_Page_Layout'
424 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
425 --
426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
427 RAISE; -- This is optional depending on the needs
428
429 END update_page_layout;
430
431
432
433 PROCEDURE Delete_Page_Layout
434 (
435 p_api_version IN NUMBER := 1.0,
436 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
437 p_commit IN VARCHAR2 := FND_API.g_false,
438 p_validate_only IN VARCHAR2 := FND_API.g_true,
439 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
440
441 p_page_id IN number,
442 p_record_version_number IN NUMBER ,
443
444 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
445 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
446 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
447 ) IS
448
449 CURSOR check_page_layout IS
450 SELECT page_id
451 FROM pa_page_layouts
452 WHERE (page_id = p_page_id AND p_page_id IS NOT NULL
453 AND record_version_number = Nvl(p_record_version_number, record_version_number));
454
455
456 CURSOR check_object_page_versions IS
457 SELECT page_id
458 FROM pa_object_page_layouts
459 WHERE (page_id = p_page_id AND p_page_id IS NOT NULL);
460
461 CURSOR get_template_type
462 is
463 SELECT meaning FROM pa_lookups pl, pa_page_layouts ppl
464 WHERE pl.lookup_type = 'PA_PAGE_TYPES'
465 AND pl.lookup_code = ppl.page_type_code
466 AND ppl.page_id = p_page_id;
467
468 CURSOR get_page_type_code IS
469 SELECT page_type_code from pa_page_layouts
470 WHERE page_id = p_page_id;
471
472 l_type VARCHAR2(250);
473
474 l_page_id NUMBER;
475 l_page_type_code pa_page_layouts.page_type_code%TYPE;
476 BEGIN
477
478 -- Initialize the Error Stack
479 PA_DEBUG.init_err_stack('PA_PAGE_LAYOUT_PVT.Delete_Page_Layout');
480
481
482 x_return_status := FND_API.G_RET_STS_SUCCESS;
483
484 -- Issue API savepoint if the transaction is to be committed
485 IF p_commit = FND_API.G_TRUE THEN
486 SAVEPOINT PAGE_LAYOUT_PVT_DELETE;
487 END IF;
488
489 --debug_msg('before delete');
490
491 OPEN get_template_type;
492 FETCH get_template_type INTO l_type;
493 CLOSE get_template_type;
494
495 OPEN check_page_layout;
496
497 FETCH check_page_layout INTO l_page_id;
498
499 IF check_page_layout%NOTFOUND THEN
500
501 --debug_msg('before delete2');
502 CLOSE check_page_layout;
503
504 PA_UTILS.Add_Message( p_app_short_name => 'PA'
505 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
506 x_return_status := FND_API.G_RET_STS_ERROR;
507
508 ELSE
509
510 --debug_msg('before delete3');
511
512 CLOSE check_page_layout;
513
514 OPEN check_object_page_versions;
515
516 FETCH check_object_page_versions INTO l_page_id;
517
518 IF check_object_page_versions%NOTFOUND THEN
519 -- we can delete if this page is not used yet
520 CLOSE check_object_page_versions;
521
522 IF (p_validate_only = FND_API.g_false) THEN
523
524
525 --debug_msg('before delete 4');
526 PA_PAGE_LAYOUT_PKG.Delete_page_layout_Row
527 (
528 p_page_id => p_page_id
529 ,p_record_version_number => p_record_version_number
530 ,x_return_status => x_return_status
531 ,x_msg_count => x_msg_count
532 ,x_msg_data => x_msg_data
533 );
534 END IF;
535
536 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
537
538 -- DELETE FROM pa_object_page_layouts
539 -- WHERE page_id = p_page_id;
540
541 -- if (sql%notfound) then
542 -- RAISE no_data_found;
543 -- end if;
544
545
546 DELETE FROM pa_page_layout_regions
547 WHERE page_id = p_page_id;
548
549 END IF;
550 ELSE --Bug#3302984 added this part of code.
551 OPEN get_page_type_code;
552 FETCH get_page_type_code INTO l_page_type_code;
553 CLOSE get_page_type_code;
554
555 IF (l_page_type_code = 'PPR') THEN
556 CLOSE check_object_page_versions;
557
558 PA_UTILS.Add_Message( p_app_short_name => 'PA'
559 ,p_msg_name => 'PA_PAGE_IN_USE'
560 , p_token1 => 'TEMPLATE_TYPE'
561 , p_value1 => l_type);
562 x_return_status := FND_API.G_RET_STS_ERROR;
563 ELSE
564 PA_PAGE_LAYOUT_PKG.Delete_page_layout_Row
565 (
566 p_page_id => p_page_id
567 ,p_record_version_number => p_record_version_number
568 ,x_return_status => x_return_status
569 ,x_msg_count => x_msg_count
570 ,x_msg_data => x_msg_data
571 );
572
573 DELETE FROM pa_page_layout_regions
574 WHERE page_id = p_page_id;
575 END IF;
576
577 CLOSE check_object_page_versions;
578 END IF;
579
580
581 END IF;
582
583
584
585 -- Commit if the flag is set and there is no error
586 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
587 COMMIT;
588 END IF;
589
590 -- Reset the error stack when returning to the calling program
591 PA_DEBUG.Reset_Err_Stack;
592
593
594 EXCEPTION
595 WHEN OTHERS THEN
596 IF p_commit = FND_API.G_TRUE THEN
597 ROLLBACK TO PAGE_LAYOUT_PVT_DELETE;
598 END IF;
599
600 -- Set the excetption Message and the stack
601 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PAGE_LAYOUT_PVT.Delete_Page_Layout'
602 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
603 --
604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
605 RAISE; -- This is optional depending on the needs
606
607 END delete_page_layout;
608
609
610 procedure ADD_PAGE_REGION (
611 p_api_version IN NUMBER := 1.0,
612 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
613 p_commit IN VARCHAR2 := FND_API.g_false,
614 p_validate_only IN VARCHAR2 := FND_API.g_true,
615 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
616 P_PAGE_ID in NUMBER,
617 P_REGION_SOURCE_TYPE in VARCHAR2,
618 P_REGION_SOURCE_CODE in VARCHAR2,
619 P_VIEW_REGION_CODE in VARCHAR2,
620 P_EDIT_REGION_CODE in VARCHAR2,
621 P_REGION_STYLE in VARCHAR2,
622 P_DISPLAY_ORDER in NUMBER,
623 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
624 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
625 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
626 ) IS
627
628 l_view_region_code VARCHAR2(250);
629 l_edit_region_code VARCHAR2(250);
630
631 CURSOR get_edit_region_code
632 IS
633 SELECT view_region_code, edit_region_code
634 FROM pa_page_type_regions pptr, pa_page_layouts ppl
635 WHERE pptr.page_type_code = ppl.page_type_code
636 AND ppl.page_id = p_page_id
637 AND pptr.region_source_type = P_REGION_SOURCE_TYPE
638 AND pptr.view_region_code = P_VIEW_REGION_CODE;
639
640 CURSOR get_template_type
641 is
642 SELECT meaning FROM pa_lookups pl, pa_page_layouts ppl
643 WHERE pl.lookup_type = 'PA_PAGE_TYPES'
644 AND pl.lookup_code = ppl.page_type_code
645 AND ppl.page_id = p_page_id;
646
647 l_type VARCHAR2(250);
648
649 BEGIN
650
651 -- Initialize the Error Stack
652 PA_DEBUG.init_err_stack('PA_PAGE_LAYOUT_PVT.Add_Page_Region');
653
654 -- Initialize the return status to success
655 x_return_status := FND_API.G_RET_STS_SUCCESS;
656
657 -- Issue API savepoint if the transaction is to be committed
658 IF p_commit = FND_API.G_TRUE THEN
659 SAVEPOINT PVT_ADD_PAGE_REGION;
660 END IF;
661
662 -- check the mandatory page_id
663 IF (p_page_id IS NULL) then
664 PA_UTILS.Add_Message( p_app_short_name => 'PA'
665 ,p_msg_name => 'PA_PAGE_ID_INV'
666 , p_token1 => 'TEMPLATE_TYPE'
667 , p_value1 => l_type);
668 x_return_status := FND_API.G_RET_STS_ERROR;
669 END IF;
670
671 IF (p_region_source_code IS null) then
672 PA_UTILS.Add_Message( p_app_short_name => 'PA'
673 ,p_msg_name => 'PA_REGION_CODE_INV'
674 , p_token1 => 'TEMPLATE_TYPE'
675 , p_value1 => l_type);
676 x_return_status := FND_API.G_RET_STS_ERROR;
677 END IF;
678
679 IF (p_edit_region_code IS NULL OR p_view_region_code IS NULL) THEN
680
681 OPEN get_edit_region_code;
682 FETCH get_edit_region_code INTO l_view_region_code, l_edit_region_code;
683
684 /*
685 IF (get_edit_region_code%notfound ) THEN
686 PA_UTILS.Add_Message( p_app_short_name => 'PA'
687 ,p_msg_name => 'PA_REGION_REF_INV');
688 x_return_status := FND_API.G_RET_STS_ERROR;
689
690 ELSE
691
692 NULL;
693 END IF;*/
694
695 CLOSE get_edit_region_code;
696 ELSE
697 l_view_region_code := p_view_region_code;
698 l_edit_region_code := p_edit_region_code;
699 END IF;
700
701
702 IF (p_display_order IS NULL) then
703 PA_UTILS.Add_Message( p_app_short_name => 'PA'
704 ,p_msg_name => 'PA_DISPLAY_ORDER_INV');
705 x_return_status := FND_API.G_RET_STS_ERROR;
706 END IF;
707
708
709 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
710
711 pa_page_layout_pkg.Insert_page_region_Row
712 (
713 p_page_id => p_page_id,
714 p_region_source_type => p_region_source_type,
715 p_region_source_code => p_region_source_code,
716 p_view_region_code => p_view_region_code,
717 p_edit_region_code => l_edit_region_code,
718 p_region_style => p_region_style,
719 p_display_order => p_display_order,
720 x_return_status => x_return_status,
721 x_msg_count => x_msg_count,
722 x_msg_data => x_msg_data
723 );
724
725
726 -- Commit if the flag is set and there is no error
727 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.g_ret_sts_success )THEN
728 COMMIT;
729 END IF;
730
731 END IF;
732
733 -- Reset the error stack when returning to the calling program
734 PA_DEBUG.Reset_Err_Stack;
735
736
737 EXCEPTION
738 WHEN OTHERS THEN
739 IF p_commit = FND_API.G_TRUE THEN
740 ROLLBACK TO PVT_ADD_PAGE_REGION;
741 END IF;
742 --
743 -- Set the excetption Message and the stack
744 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PAGE_LAYOUT_PVT.Add_Page_Region'
745 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
746 --
747 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
748 RAISE; -- This is option
749
750 END add_page_region;
751
752
753 PROCEDURE Delete_Page_Region
754 (
755 p_api_version IN NUMBER := 1.0,
756 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
757 p_commit IN VARCHAR2 := FND_API.g_false,
758 p_validate_only IN VARCHAR2 := FND_API.g_true,
759 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
760
761 p_page_id IN number,
762 p_region_source_type IN VARCHAR2,
763 p_region_source_code IN VARCHAR2,
764 p_record_version_number IN NUMBER ,
765
766 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
767 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
768 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
769 ) IS
770
771 l_row_id ROWID;
772
773 CURSOR check_page_region IS
774 SELECT rowid
775 FROM pa_page_layout_regions
776 WHERE (page_id = p_page_id
777 AND region_source_type = p_region_source_type
778 AND region_source_code = p_region_source_code
779 AND record_version_number = Nvl(p_record_version_number, record_version_number));
780
781
782 BEGIN
783
784 -- Initialize the Error Stack
785 PA_DEBUG.init_err_stack('PA_PAGE_LAYOUT_PVT.Delete_Page_Region');
786
787
788 x_return_status := FND_API.G_RET_STS_SUCCESS;
789
790 -- Issue API savepoint if the transaction is to be committed
791 IF p_commit = FND_API.G_TRUE THEN
792 SAVEPOINT PVT_DELETE_PAGE_REGION;
793 END IF;
794
795
796 OPEN check_page_region;
797
798 FETCH check_page_region INTO l_row_id;
799
800 IF check_page_region%NOTFOUND THEN
801
802 CLOSE check_page_region;
803
804 PA_UTILS.Add_Message( p_app_short_name => 'PA'
805 ,p_msg_name => 'PA_XC_RECORD_CHANGED');
806 x_return_status := FND_API.G_RET_STS_ERROR;
807
808 ELSE
809
810 CLOSE check_page_region;
811
812 IF (p_validate_only = FND_API.g_false) THEN
813
814
815 PA_PAGE_LAYOUT_PKG.Delete_page_region_Row
816 (
817 p_page_id => p_page_id
818 ,p_region_source_type => p_region_source_type
819 ,p_region_source_code => p_region_source_code
820 ,p_record_version_number => p_record_version_number
821 ,x_return_status => x_return_status
822 ,x_msg_count => x_msg_count
823 ,x_msg_data => x_msg_data
824 );
825 END IF;
826
827 END IF;
828
829
830 -- Commit if the flag is set and there is no error
831 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
832 COMMIT;
833 END IF;
834
835 -- Reset the error stack when returning to the calling program
836 PA_DEBUG.Reset_Err_Stack;
837
838
839 EXCEPTION
840 WHEN OTHERS THEN
841 IF p_commit = FND_API.G_TRUE THEN
842 ROLLBACK TO PVT_DELETE_PAGE_REGION;
843 END IF;
844
845 -- Set the excetption Message and the stack
846 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PAGE_LAYOUT_PVT.Delete_Page_Region'
847 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
848 --
849 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
850 RAISE; -- This is optional depending on the needs
851
852 END delete_page_region;
853
854
855 PROCEDURE Delete_All_Page_Region
856 (
857 p_api_version IN NUMBER := 1.0,
858 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
859 p_commit IN VARCHAR2 := FND_API.g_false,
860 p_validate_only IN VARCHAR2 := FND_API.g_true,
861 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
862
863 p_page_id IN NUMBER := null,
864 p_region_position IN VARCHAR2 := null,
865
866 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
867 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
868 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
869 ) IS
870
871 CURSOR get_region IS
872 SELECT distinct pplr.page_id
873 , pplr.region_source_type
874 , pplr.region_source_code
875 , pplr.record_version_number
876 FROM pa_page_layout_regions pplr
877 , pa_page_layouts ppl
878 , pa_page_type_regions pptr
879 WHERE pplr.page_id = p_page_id
880 and ppl.page_id = pplr.page_id
881 and ppl.page_type_code = pptr.page_type_code
882 and pplr.region_source_type = pptr.region_source_type
883 and pplr.view_region_code = pptr.view_region_code
884 and nvl(pptr.default_region_position, 'L')
885 = nvl('L', nvl(pptr.default_region_position, 'L'))
886 AND Nvl(pplr.region_style, 'N') <> 'LINK';
887
888 l_msg_index_out NUMBER;
889 BEGIN
890
891 -- Initialize the Error Stack
892 PA_DEBUG.init_err_stack('PA_PAGE_LAYOUT_PUB.Delete_Page_Region');
893
894 -- Initialize the return status to success
895 x_return_status := FND_API.G_RET_STS_SUCCESS;
896
897 --Clear the global PL/SQL message table
898 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
899 FND_MSG_PUB.initialize;
900 END IF;
901
902 FOR reg_rec IN get_region loop
903 PA_PAGE_LAYOUT_PKG.DELETE_PAGE_REGION_ROW
904 (
905 p_page_id => p_page_id,
906 p_region_source_type => reg_rec.region_source_type,
907 p_region_source_code => reg_rec.region_source_code,
908 p_record_version_number => reg_rec.record_version_number,
909
910 x_return_status => x_return_status,
911 x_msg_count => x_msg_count,
912 x_msg_data => x_msg_data
913 );
914
915
916
917 --
918 -- IF the number of messaages is 1 then fetch the message code from the
919 -- stack and return its text
920 --
921 x_msg_count := FND_MSG_PUB.Count_Msg;
922 IF x_msg_count = 1 THEN
923 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
924 ,p_msg_index => 1
925 ,p_data => x_msg_data
926 ,p_msg_index_out => l_msg_index_out
927 );
928 -- break out of the loop;
929 EXIT;
930 END IF;
931
932 END LOOP;
933
934 -- Reset the error stack when returning to the calling program
935 PA_DEBUG.Reset_Err_Stack;
936
937
938 -- Put any message text from message stack into the Message ARRAY
939 EXCEPTION
940 WHEN OTHERS THEN
941
942 -- Set the exception Message and the stack
943 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PAGE_LAYOUT_PUB.Delete_Page_Region'
944 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
945 --
946 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
947 RAISE; -- This is optional depending on the needs
948 --
949
950 END delete_all_page_region;
951
952 PROCEDURE Delete_All_link_Page_Region
953 (
954 p_api_version IN NUMBER := 1.0,
955 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
956 p_commit IN VARCHAR2 := FND_API.g_false,
957 p_validate_only IN VARCHAR2 := FND_API.g_true,
958 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
959
960 p_page_id IN NUMBER := null,
961 p_region_position IN VARCHAR2 := null,
962
963 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
964 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
965 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
966 ) IS
967
968 CURSOR get_region is
969 SELECT distinct pplr.page_id
970 , pplr.region_source_type
971 , pplr.region_source_code
972 , pplr.record_version_number
973 FROM pa_page_layout_regions pplr
974 WHERE pplr.page_id = p_page_id
975 AND Nvl(pplr.region_style, 'N') = 'LINK';
976 --and pptr.default_region_position =
977 --nvl(p_region_position, pptr.default_region_position);
978
979 l_msg_index_out NUMBER;
980 BEGIN
981
982 -- Initialize the Error Stack
983 PA_DEBUG.init_err_stack('PA_PAGE_LAYOUT_PUB.Delete_All_link_Page_Region');
984
985 -- Initialize the return status to success
986 x_return_status := FND_API.G_RET_STS_SUCCESS;
987
988 --Clear the global PL/SQL message table
989 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
990 FND_MSG_PUB.initialize;
991 END IF;
992
993 FOR reg_rec IN get_region loop
994 PA_PAGE_LAYOUT_PKG.DELETE_PAGE_REGION_ROW
995 (
996 p_page_id => p_page_id,
997 p_region_source_type => reg_rec.region_source_type,
998 p_region_source_code => reg_rec.region_source_code,
999 p_record_version_number => reg_rec.record_version_number,
1000
1001 x_return_status => x_return_status,
1002 x_msg_count => x_msg_count,
1003 x_msg_data => x_msg_data
1004 );
1005
1006
1007
1008 --
1009 -- IF the number of messaages is 1 then fetch the message code from the
1010 -- stack and return its text
1011 --
1012 x_msg_count := FND_MSG_PUB.Count_Msg;
1013 IF x_msg_count = 1 THEN
1014 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
1015 ,p_msg_index => 1
1016 ,p_data => x_msg_data
1017 ,p_msg_index_out => l_msg_index_out
1018 );
1019 -- break out of the loop;
1020 EXIT;
1021 END IF;
1022
1023 END LOOP;
1024
1025 -- Reset the error stack when returning to the calling program
1026 PA_DEBUG.Reset_Err_Stack;
1027
1028
1029 -- Put any message text from message stack into the Message ARRAY
1030 EXCEPTION
1031 WHEN OTHERS THEN
1032
1033 -- Set the exception Message and the stack
1034 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PAGE_LAYOUT_PUB.Delete_All_link_Page_Region'
1035 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
1036 --
1037 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1038 RAISE; -- This is optional depending on the needs
1039 --
1040
1041 END delete_all_link_page_region;
1042
1043 FUNCTION IS_REGION_DELETE_OK(p_region_source_type in varchar2,
1044 p_region_source_code in varchar2) return varchar2 IS
1045 l_ret varchar2(1) := 'N';
1046 BEGIN
1047 select 'N'
1048 into l_ret
1049 from pa_page_layout_regions
1050 where region_source_type = p_region_source_type
1051 and region_source_code = p_region_source_code;
1052 return l_ret;
1053 exception
1054 when no_data_found then
1055 return 'Y';
1056 when others then
1057 return 'N';
1058 END IS_REGION_DELETE_OK;
1059
1060 END PA_PAGE_LAYOUT_PVT;
1061