[Home] [Help]
PACKAGE BODY: APPS.PA_PAGE_LAYOUT_UTILS
Source
1 PACKAGE BODY PA_Page_layout_Utils AS
2 /* $Header: PAPGLUTB.pls 120.1.12010000.4 2010/04/22 20:51:31 snizam ship $ */
3
4 PROCEDURE VALIDATE_PARAMETERS ( p_object_type IN VARCHAR2 ,
5 P_object_id_from IN number ,
6 P_object_id_to IN number ,
7 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
8 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
9 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
10 IS
11 CURSOR c_project_exists (p_project_id in number)
12 IS
13 SELECT project_id
14 FROM pa_projects_all
15 WHERE project_id = p_project_id;
16 l_dummy number;
17
18 BEGIN
19 x_return_status:=fnd_api.g_ret_sts_success;
20
21 IF (p_object_type = 'PA_PROJECTS') THEN
22 -- Validate project_id_from
23 OPEN c_project_exists (p_object_id_from);
24 FETCH c_project_exists INTO l_dummy;
25 IF(c_project_exists%NOTFOUND) THEN
26 PA_UTILS.Add_Message( p_app_short_name => 'PA'
27 ,p_msg_name => 'PA_INVALID_PROJECT_ID');
28 x_return_status := FND_API.G_RET_STS_ERROR;
29 --x_ret_code:= fnd_api.g_false;
30 x_msg_count := x_msg_count + 1;
31 END IF;
32 CLOSE c_project_exists;
33
34 -- Validate project_id_to
35 OPEN c_project_exists (p_object_id_to);
36 FETCH c_project_exists INTO l_dummy;
37 IF(c_project_exists%NOTFOUND) THEN
38 PA_UTILS.Add_Message( p_app_short_name => 'PA'
39 ,p_msg_name => 'PA_INVALID_PROJECT_ID');
40 x_return_status := FND_API.G_RET_STS_ERROR;
41 --x_ret_code:= fnd_api.g_false;
42 x_msg_count := x_msg_count + 1;
43 END IF;
44 CLOSE c_project_exists;
45 END IF;
46 EXCEPTION
47 WHEN OTHERS THEN
48 IF ( c_project_exists%ISOPEN ) THEN
49 close c_project_exists;
50 END IF;
51 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PAGE_LAYOUT_UTILS'
52 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
53 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
54 --x_ret_code:= fnd_api.g_false;
55 END VALIDATE_PARAMETERS;
56
57
58 PROCEDURE COPY_OBJECT_PAGE_LAYOUTS(
59 p_object_type IN VARCHAR2,
60 P_object_id_from IN number ,
61 P_object_id_to IN number ,
62 -- p_function_name IN VARCHAR2, Bug 3665562.
63 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
64 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
65 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
66 )
67 IS
68
69 l_object_page_layout_id NUMBER;
70 v_proj_start_date date; /* Bug8732869 */
71
72
73 CURSOR obj_page_layout
74 IS
75 SELECT
76 page_type_code
77 , page_id
78 , approval_required
79 , reporting_cycle_id
80 , report_offset_days
81 , reminder_days
82 , reminder_days_type
83 , initial_progress_status
84 , final_progress_status
85 , rollup_progress_status
86 , report_type_id
87 , approver_source_id
88 , approver_source_type
89 , effective_from
90 , effective_to
91 , object_page_layout_id
92 , pers_function_name
93 FROM pa_object_page_layouts
94 WHERE object_type = p_object_type
95 AND object_id = p_object_id_from;
96
97 CURSOR obj_regions
98 IS
99 SELECT
100 placeholder_reg_code
101 , replacement_reg_code
102 FROM pa_object_regions
103 where object_type = p_object_type
104 and object_id = p_object_id_from;
105
106 l_commit_flag varchar2(1) := 'Y';
107 l_next_reporting_date Date ; -- Added for Bug 3026572
108 l_rep_start_date Date; -- Added for Bug 3026572
109
110 BEGIN
111 PA_DEBUG.init_err_stack('PA_PAGE_LAYOUT_UTILS.copy_project_page_layouts');
112 --x_ret_code:= fnd_api.g_true;
113 x_return_status:=fnd_api.g_ret_sts_success;
114 savepoint copy_object_page_layouts;
115
116
117
118 VALIDATE_PARAMETERS( p_object_type => p_object_type ,
119 P_object_id_from => P_object_id_from ,
120 P_object_id_to => P_object_id_to ,
121 x_return_status => x_return_status ,
122 x_msg_count => x_msg_count ,
123 x_msg_data => x_msg_data
124 );
125
126 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
127 -- Insert the object page layouts for the new object ID
128
129 FOR obj_page_layout_rec in obj_page_layout LOOP
130 /* Bug 3026572 Begin */
131
132 -- This call is made to simulate the same effect as user is trying to update the reporting cycle and
133 -- PA_PROGRESS_REPORT_PVT.define_progress_report_setup is getting called
134
135 IF obj_page_layout_rec.page_type_code = 'PPR' AND obj_page_layout_rec.reporting_cycle_id IS NOT NULL THEN
136
137 -- Note that in p_object_type intentionally null is passed instead of actual object type,
138 -- so that it does not return the From Project's next reporting date but the actual next
139 -- reporting date for the To project
140
141 PA_PROGRESS_REPORT_UTILS.GET_REPORT_START_END_DATES(
142 p_Object_Type => null ,
143 p_Object_Id => p_object_id_from ,
144 p_report_type_id => obj_page_layout_rec.report_type_id ,
145 p_Reporting_Cycle_Id => obj_page_layout_rec.reporting_cycle_id ,
146 p_Reporting_Offset_Days => obj_page_layout_rec.report_offset_days ,
147 p_Publish_Report => 'N' ,
148 p_report_effective_from => obj_page_layout_rec.effective_from ,
149 x_Report_Start_Date => l_rep_start_date ,
150 x_Report_End_Date => l_next_reporting_date
151 );
152
153
154 ELSE
155 l_next_reporting_date := null;
156 END IF;
157 /* Bug 3026572 End */
158 select start_date into v_proj_start_date from pa_projects_all where project_id=P_object_id_to; /* Bug8732869 */
159
160
161
162 PA_PROGRESS_REPORT_PKG.INSERT_OBJECT_PAGE_LAYOUT_ROW (
163 P_OBJECT_ID => p_object_id_to ,
164 P_OBJECT_TYPE => p_object_type ,
165 P_PAGE_ID => obj_page_layout_rec.page_id ,
166 P_PAGE_TYPE_CODE => obj_page_layout_rec.page_type_code ,
167 P_APPROVAL_REQUIRED => obj_page_layout_rec.approval_required ,
168 P_REPORTING_CYCLE_ID => obj_page_layout_rec.reporting_cycle_id ,
169 P_REPORTING_OFFSET_DAYS => obj_page_layout_rec.report_offset_days ,
170 P_NEXT_REPORTING_DATE => l_next_reporting_date , -- to_date(null), Bug 3026572
171 P_REMINDER_DAYS => obj_page_layout_rec.reminder_days ,
172 P_REMINDER_DAYS_TYPE => obj_page_layout_rec.REMINDER_DAYS_TYPE ,
173 P_INITIAL_PROGRESS_STATUS => obj_page_layout_rec.INITIAL_PROGRESS_STATUS ,
174 P_FINAL_PROGRESS_STATUS => obj_page_layout_rec.FINAL_PROGRESS_STATUS ,
175 P_ROLLUP_PROGRESS_STATUS => obj_page_layout_rec.ROLLUP_PROGRESS_STATUS ,
176 P_REPORT_TYPE_ID => obj_page_layout_rec.report_type_id ,
177 P_APPROVER_SOURCE_ID => obj_page_layout_rec.approver_source_id ,
178 P_APPROVER_SOURCE_TYPE => obj_page_layout_rec.approver_source_type ,
179 P_EFFECTIVE_FROM => nvl(v_proj_start_date, obj_page_layout_rec.effective_from) , /* Bug8732869 */
180 P_EFFECTIVE_TO => obj_page_layout_rec.effective_to ,
181 --P_FUNCTION_NAME => obj_page_layout_rec.effective_to , Bug 3665562 Incorrect value passed.
182 P_FUNCTION_NAME => obj_page_layout_rec.pers_function_name , -- Pass the correct value.
183 X_OBJECT_PAGE_LAYOUT_ID => l_object_page_layout_id ,
184 X_RETURN_STATUS => x_return_status ,
185 X_MSG_COUNT => x_msg_count ,
186 X_MSG_DATA => x_msg_data
187 );
188
189 PA_DISTRIBUTION_LIST_UTILS.COPY_DIST_LIST(
190 p_object_type_from => 'PA_OBJECT_PAGE_LAYOUT' ,
191 p_object_id_from => obj_page_layout_rec.object_page_layout_id,
192 p_object_type_to => 'PA_OBJECT_PAGE_LAYOUT' ,
193 p_object_id_to => l_object_page_layout_id ,
194 x_return_status => x_return_status ,
195 x_msg_count => x_msg_count ,
196 x_msg_data => x_msg_data
197 );
198
199 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
200 l_commit_flag := 'N';
201 END IF;
202 END LOOP;
203
204 /* Copy the object_regions */
205
206 FOR obj_regions_rec in obj_regions LOOP
207 PA_OBJECT_REGIONS_PKG.INSERT_ROW (
208 P_OBJECT_ID => p_object_id_to ,
209 P_OBJECT_TYPE => p_object_type ,
210 P_PLACEHOLDER_REG_CODE => obj_regions_rec.PLACEHOLDER_REG_CODE ,
211 P_REPLACEMENT_REG_CODE => obj_regions_rec.REPLACEMENT_REG_CODE ,
212 P_CREATION_DATE => sysdate ,
213 P_CREATED_BY => fnd_global.user_id ,
214 P_LAST_UPDATE_DATE => sysdate ,
215 P_LAST_UPDATED_BY => fnd_global.user_id ,
216 P_LAST_UPDATE_LOGIN => fnd_global.user_id
217 );
218 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
219 l_commit_flag := 'N';
220 END IF;
221 END LOOP;
222
223 IF (l_commit_flag = 'N') THEN
224 ROLLBACK TO copy_object_page_layouts;
225 END IF;
226 END IF;
227
228 PA_DEBUG.Reset_Err_Stack;
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 ROLLBACK TO copy_object_page_layouts;
233 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_page_layout_utils.copy_object_page_layouts',
234 p_procedure_name => PA_DEBUG.G_Err_Stack );
235
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
237 END COPY_OBJECT_PAGE_LAYOUTS;
238
239 /* This is the function to check customer and project value columns
240 exists in the project header at the project level. This is for the
241 temporary solution to fix the project Header section in progress report
242 based on the Header section shown at the project level */
243
244 PROCEDURE CHECK_COLS_IN_PROJ_HEADER(
245 p_object_type IN VARCHAR2 ,
246 p_object_id IN number ,
247 x_customer_exists OUT NOCOPY varchar2 , --File.Sql.39 bug 4440895
248 x_proj_val_exists OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
249 )
250 IS
251 CURSOR C IS
252 SELECT replacement_reg_code
253 FROM pa_object_regions
254 WHERE object_type = p_object_type
255 AND object_id = P_object_id
256 --AND placeholder_reg_code = 'PA_PROJECT_INFO'; --Bug 3745737
257 AND placeholder_reg_code = '/oracle/apps/pa/project/webui/ProjectInfoRN';
258
259 l_replacement_reg_code pa_object_regions.replacement_reg_code%TYPE := null;
260
261 BEGIN
262 -- Initialize the out parameters
263 x_customer_exists := 'N';
264 x_proj_val_exists := 'N';
265
266 OPEN C;
267 FETCH C INTO l_replacement_reg_code;
268 IF (C%NOTFOUND) THEN
269 CLOSE C;
270 return;
271 END IF;
272 CLOSE C;
273
274 --IF ( l_replacement_reg_code = 'PA_PROJECT_INFO_2') THEN --Bug 3745737
275 IF ( l_replacement_reg_code = '/oracle/apps/pa/project/webui/ProjectInfo2RN') THEN
276 x_customer_exists := 'Y';
277 x_proj_val_exists := 'Y';
278 END IF;
279
280 EXCEPTION
281 WHEN OTHERS THEN
282 IF(C%ISOPEN) THEN
283 CLOSE C;
284 END IF;
285 x_customer_exists := 'N';
286 x_proj_val_exists := 'N';
287 END CHECK_COLS_IN_PROJ_HEADER;
288
289
290 FUNCTION GET_AK_REGION_CODE
291 (
292 p_region_name IN VARCHAR2,
293 p_application_id IN NUMBER
294 ) RETURN VARCHAR2
295 IS
296
297 l_region_code VARCHAR2(30);
298
299 /*
300 CURSOR get_region_code is
301 SELECT region_code
302 FROM ak_regions_vl
303 WHERE name = p_region_name
304 AND region_application_id = p_application_id;
305 */
306
307 BEGIN
308 /*
309 OPEN get_region_code;
310 FETCH get_region_code INTO l_region_code;
311 IF get_region_code%notfound THEN
312 CLOSE get_region_code;
313 RETURN NULL;
314 END IF;
315 CLOSE get_region_code;
316
317 RETURN l_region_code;
318 */
319 RETURN NULL;
320
321 END GET_AK_REGION_CODE;
322
323
324 FUNCTION GET_REGION_SOURCE_CODE
325 (
326 p_region_source_name IN VARCHAR2 ,
327 p_region_source_type IN VARCHAR2 ,
328 p_application_id IN NUMBER ,
329 p_flex_name IN VARCHAR2
330 ) RETURN VARCHAR2
331 IS
332
333 l_region_source_code VARCHAR2(30);
334
335 /* CURSOR get_region_code is
336 SELECT region_code
337 FROM ak_regions_vl
338 WHERE name = p_region_source_name
339 AND region_application_id = p_application_id;
340 */
341
342
343 CURSOR get_flex_code is
344 SELECT
345 descriptive_flex_context_code
346 FROM fnd_descr_flex_contexts_vl
347 WHERE application_id = p_application_id
348 AND descriptive_flexfield_name = p_flex_name
349 AND descriptive_flex_context_name = p_region_source_name;
350
351 BEGIN
352 IF p_region_source_type = 'STD' THEN
353
354 /* OPEN get_region_code;
355 FETCH get_region_code INTO l_region_source_code;
356 IF get_region_code%notfound THEN
357 CLOSE get_region_code;
358 RETURN NULL;
359 END IF;
360 CLOSE get_region_code;
361
362 RETURN l_region_source_code;*/
363 RETURN NULL;
364
365 ELSIF p_region_source_type = 'DFF' THEN
366
367 OPEN get_flex_code;
368 FETCH get_flex_code INTO l_region_source_code;
369 IF get_flex_code%notfound THEN
370 CLOSE get_flex_code;
371 RETURN NULL;
372 END IF;
373 CLOSE get_flex_code;
374
375 RETURN l_region_source_code;
376 ELSE
377 RETURN NULL;
378 END IF;
379
380 END get_region_source_code;
381
382
383 FUNCTION IS_PAGE_TYPE_REGION_DELETABLE(
384 p_page_type_code IN VARCHAR2 ,
385 p_region_source_type IN VARCHAR2,
386 p_region_source_code IN VARCHAR2)
387
388 RETURN VARCHAR2
389 IS
390
391 CURSOR get_page_layout
392 IS
393 SELECT ppl.page_id FROM
394 pa_page_layouts ppl, pa_page_layout_regions pplr
395 WHERE ppl.page_id = pplr.page_id
396 AND ppl.page_type_code = p_page_type_code
397 AND pplr.region_source_type = p_region_source_type
398 AND pplr.region_source_code = p_region_source_code;
399 l_page_id NUMBER;
400
401 l_ret VARCHAR2(1) := 'Y';
402 BEGIN
403 OPEN get_page_layout;
404 FETCH get_page_layout INTO l_page_id;
405
406 IF (get_page_layout%notfound) THEN
407 l_ret := 'Y';
408 ELSE
409 l_ret := 'N';
410 END IF;
411
412 CLOSE get_page_layout;
413
414 RETURN l_ret;
415
416 END is_page_type_region_deletable;
417
418
419 FUNCTION GET_CONTEXT_NAME(
420 p_context_code IN VARCHAR2
421 )
422 RETURN VARCHAR2
423 IS
424
425 CURSOR get_name IS
426 SELECT descriptive_flex_context_name
427 FROM fnd_descr_flex_contexts_vl
428 WHERE descriptive_flexfield_name = 'PA_STATUS_REPORT_DESC_FLEX'
429 AND descriptive_flex_context_code = p_context_code
430 AND application_id = fnd_global.resp_appl_id; /* Added for for Bug 2634995 */
431
432 l_context_name VARCHAR2(80); --Bug 9585564 Increased the size
433
434
435 BEGIN
436 OPEN get_name;
437 FETCH get_name INTO l_context_name;
438 CLOSE get_name;
439
440 RETURN l_context_name;
441
442 END GET_CONTEXT_NAME;
443
444
445 PROCEDURE CHECK_PAGELAYOUT_NAME_OR_ID (
446 p_pagelayout_name IN VARCHAR2 :=FND_API.G_MISS_CHAR ,
447 p_pagetype_code IN VARCHAR2 :=FND_API.G_MISS_CHAR ,
448 p_check_id_flag IN VARCHAR2 := 'A' ,
449 x_pagelayout_id IN OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
450 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
451 x_error_message_code OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
452 IS
453
454 BEGIN
455
456 IF (x_pagelayout_id IS NOT NULL) THEN
457 IF (x_pagelayout_id >0 AND p_check_id_flag = 'Y') THEN
458 SELECT page_id
459 INTO x_pagelayout_id
460 FROM pa_page_layouts
461 WHERE page_id = x_pagelayout_id;
462 ELSIF (p_check_id_flag = 'N') THEN
463 -- No ID validation is required
464 x_pagelayout_id := x_pagelayout_id;
465 ELSIF(p_check_id_flag = 'A') THEN
466 IF (p_pagelayout_name is null) THEN
467 x_pagelayout_id := null;
468 ELSE
469 --Find the Id for the name
470 SELECT page_id
471 INTO x_pagelayout_id
472 FROM pa_page_layouts
473 WHERE page_name = p_pagelayout_name
474 AND page_type_code = p_pagetype_code;
475
476 END IF;
477 END IF;
478 ELSE
479 IF (p_pagelayout_name is not null) THEN
480 SELECT page_id
481 INTO x_pagelayout_id
482 FROM pa_page_layouts
483 WHERE page_name = p_pagelayout_name
484 AND page_type_code = p_pagetype_code;
485 ELSE
486 x_pagelayout_id := null;
487 END IF;
488 END IF;
489
490 x_return_status := FND_API.G_RET_STS_SUCCESS;
491
492 EXCEPTION
493 WHEN NO_DATA_FOUND THEN
494 x_return_status := FND_API.G_RET_STS_ERROR;
495 x_error_message_code := 'PA_INV_PAGE_NAME';
496 WHEN TOO_MANY_ROWS THEN
497 x_return_status := FND_API.G_RET_STS_ERROR;
498 x_error_message_code := 'PA_INV_PAGE_NAME';
499 WHEN OTHERS THEN
500 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
501
502 END;
503
504
505 FUNCTION CHECK_PAGE_LAYOUT_DELETABLE (p_page_id NUMBER)
506 RETURN VARCHAR2
507 IS
508 CURSOR c_object_layout_referenced
509 IS
510 SELECT 'X'
511 FROM pa_object_page_layouts
512 WHERE page_id = p_page_id;
513
514 -- Bug 3454743 : Added the below cursor
515 CURSOR c_report_type_referenced
516 IS
517 Select 'X'
518 FROM pa_report_types
519 WHERE page_id = p_page_id;
520
521 CURSOR c_page_type IS
522 SELECT page_type_code
523 FROM pa_page_layouts
524 WHERE page_id = p_page_id;
525
526 /*
527 CURSOR c_profile_refernce IS
528 SELECT 'X'
529 FROM fnd_profile_options po,
530 fnd_profile_option_values pov
531 WHERE po.application_id = 275
532 AND po.profile_option_name = 'PA_TEAM_HOME_PAGELAYOUT'
533 AND po.profile_option_id = pov.profile_option_id
534 AND pov.application_id = 275
535 AND to_number(pov.profile_option_value) = p_page_id;
536 */
537
538 l_dummy varchar2(1);
539 l_deletable_flag varchar2(1) := 'Y';
540 l_page_type_code pa_page_layouts.page_type_code%TYPE;
541
542 BEGIN
543 -- The seeded page layout is not deletable
544 IF (p_page_id < 1000) THEN
545 l_deletable_flag := 'N';
546 return l_deletable_flag;
547 END IF;
548
549 OPEN c_page_type;
550 FETCH c_page_type into l_page_type_code;
551 CLOSE c_page_type;
552
553 OPEN c_object_layout_referenced;
554 FETCH c_object_layout_referenced into l_dummy;
555 IF (c_object_layout_referenced%FOUND) THEN
556 --l_deletable_flag := 'N';
557 IF (l_page_type_code = 'PPR') THEN
558 l_deletable_flag := 'N';
559 END IF;
560 CLOSE c_object_layout_referenced;
561 return l_deletable_flag;
562 END IF;
563 CLOSE c_object_layout_referenced;
564
565 -- Bug 3454743 : Added the below check
566 OPEN c_report_type_referenced;
567 FETCH c_report_type_referenced into l_dummy;
568 IF (c_report_type_referenced%FOUND) THEN
569 l_deletable_flag := 'N';
570 CLOSE c_report_type_referenced;
571 return l_deletable_flag;
572 END IF;
573 CLOSE c_report_type_referenced;
574
575 /*
576 -- If deletable check for reference in pa_task_types
577 IF(pa_task_type_utils.check_page_layout_referenced(p_page_id)) THEN
578 l_deletable_flag := 'N';
579 END IF;
580
581 -- Added for Project Team member home stored in profiles
582 IF (l_deletable_flag = 'Y') THEN
583 -- get the page type code
584 OPEN c_page_type;
585 FETCH c_page_type into l_page_type_code;
586 CLOSE c_page_type;
587 -- Proceed forward only if page type is Team Home
588 IF (l_page_type_code = 'TM') THEN
589 OPEN c_profile_refernce;
590 FETCH c_profile_refernce INTO l_dummy;
591 IF (c_profile_refernce%FOUND) THEN
592 l_deletable_flag := 'N';
593 END IF;
594 CLOSE c_profile_refernce;
595 END IF;
596 END IF;
597 */
598 return l_deletable_flag;
599 END CHECK_PAGE_LAYOUT_DELETABLE;
600
601 FUNCTION GET_PAGE_ID_FROM_FUNCTION(
602 p_page_type_code IN VARCHAR2,
603 p_pers_function_name IN VARCHAR2
604 )
605 return NUMBER
606 IS
607 Cursor c_get_page_id(v_page_type_code VARCHAR2, v_function_name VARCHAR2)
608 Is
609 Select page_id
610 From pa_page_layouts
611 Where page_type_code = v_page_type_code
612 And pers_function_name = v_function_name;
613
614 l_page_id NUMBER;
615 BEGIN
616 IF((p_page_type_code IS NULL)or(p_pers_function_name is null)) THEN
617 return null;
618 END IF;
619
620 open c_get_page_id(p_page_type_code,p_pers_function_name);
621 fetch c_get_page_id into l_page_id;
622 close c_get_page_id;
623 return l_page_id;
624
625 END GET_PAGE_ID_FROM_FUNCTION;
626
627 PROCEDURE POPULATE_PERS_FUNCTIONS (p_page_type_code_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
628 p_function_name_tbl IN SYSTEM.PA_VARCHAR2_30_TBL_TYPE,
629 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
630 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
631 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
632 )
633 IS
634 len NUMBER;
635 i NUMBER := 0;
636 l_debug_mode VARCHAR2(1);
637 g_module_name VARCHAR2(100) := 'pa.plsql.POPULATE_PERS_FUNCTIONS';
638 l_debug_level5 CONSTANT NUMBER := 5;
639 BEGIN
640 x_msg_count := 0;
641 x_return_status:=fnd_api.g_ret_sts_success;
642 savepoint populate_pers_functions;
643
644 l_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'),'N');
645
646 IF l_debug_mode = 'Y' THEN
647 pa_debug.set_curr_function( p_function => 'PERS_FUNCTIONS',
648 p_debug_mode => l_debug_mode );
649 END IF;
650
651 DELETE FROM PA_PAGE_LAYOUTS_TMP;
652 len := p_page_type_code_tbl.count;
653 FORALL i in 1..len
654 INSERT INTO PA_PAGE_LAYOUTS_TMP VALUES(p_page_type_code_tbl(i),p_function_name_tbl(i));
655
656 EXCEPTION
657 WHEN OTHERS THEN
658 ROLLBACK TO populate_pers_functions;
659 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
660 x_msg_count := 1;
661 x_msg_data := SQLERRM;
662 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_page_layout_utils.populate_pers_functions',
663 p_procedure_name => PA_DEBUG.G_Err_Stack );
664
665 IF l_debug_mode = 'Y' THEN
666 pa_debug.g_err_stage:= 'Unexpected Error'||x_msg_data;
667 pa_debug.write(g_module_name,pa_debug.g_err_stage,
668 l_debug_level5);
669 pa_debug.reset_curr_function;
670 END IF;
671 RAISE;
672
673 END POPULATE_PERS_FUNCTIONS;
674
675 END PA_PAGE_LAYOUT_UTILS;