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