1 PACKAGE BODY PA_Progress_Report_Utils AS
2 /* $Header: PAPRUTLB.pls 120.1 2005/08/19 16:45:25 mwasowic noship $ */
3 PROCEDURE GET_REPORT_START_END_DATES(
4 p_Object_Type IN Varchar2,
5 p_Object_Id IN Number,
6 p_report_type_id IN Number,
7 p_Reporting_Cycle_Id IN Number,
8 p_Reporting_Offset_Days IN Number,
9 p_Publish_Report IN Varchar2,
10 p_report_effective_from IN Date := NULL,
11 x_Report_Start_Date OUT NOCOPY Date, --File.Sql.39 bug 4440895
12 x_Report_End_Date OUT NOCOPY Date) --File.Sql.39 bug 4440895
13
14 IS
15
16 Object_Type Varchar2(30) := p_Object_Type;
17 Object_Id Number := p_Object_Id;
18 Reporting_Cycle_Id Number := p_Reporting_Cycle_Id;
19 Reporting_Offset_Days Number := p_Reporting_Offset_Days;
20 Publish_Report_Flag Varchar2(1):= p_Publish_Report;
21 l_Last_Report_End_Date Date;
22 l_Report_End_Date Date;
23 l_Last_End_Date Date;
24 l_Project_Start_Date Date;
25 l_Report_Start_Date Date;
26
27 Cursor C is
28 select (MAX(Report_End_Date))
29 FROM PA_PROGRESS_REPORT_VERS
30 WHERE Object_Id = P_Object_Id
31 AND Object_Type = P_Object_Type
32 AND Report_Status_Code = decode(Publish_Report_Flag,'Y','PROGRESS_REPORT_PUBLISHED',Report_Status_Code);
33
34 Cursor C1 is
35 select (MAX(Report_End_Date))
36 FROM PA_PROGRESS_REPORT_VERS
37 WHERE Object_Id = P_Object_Id
38 AND Object_Type = P_Object_Type
39 AND report_type_id = p_report_type_id
40 AND Report_Status_Code = decode(Publish_Report_Flag,'Y','PROGRESS_REPORT_PUBLISHED',Report_Status_Code);
41
42 BEGIN
43 select NVL(Start_date, Creation_date)
44 INTO l_Project_Start_Date
45 FROM PA_PROJECTS_ALL
46 WHERE Project_Id = Object_Id;
47
48 if (p_report_type_id is null) then
49 open C;
50 fetch C into l_Last_Report_End_Date;
51 close C;
52 else
53 open C1;
54 fetch C1 into l_Last_Report_End_Date;
55 close C1;
56 end if;
57
58
59 IF l_Last_Report_End_Date IS NULL THEN
60 if p_report_effective_from is not null then
61 l_Report_Start_Date := p_report_effective_from;
62 l_Report_End_Date := PA_Billing_Cycles_Pkg.Get_Billing_Date(Object_Id
63 ,l_Report_Start_Date
64 ,p_Reporting_Cycle_Id
65 ,sysdate
66 ,l_Last_Report_End_Date);
67 elsif Object_Type = 'PA_PROJECTS' THEN
68
69 l_Report_Start_Date := l_Project_Start_Date;
70 l_Report_End_Date := NVL(l_Project_Start_Date, SYSDATE) + Reporting_Offset_Days;
71
72 END IF;
73 ELSE
74 l_Report_Start_Date := l_Last_Report_End_Date +1;
75 l_Report_End_Date := PA_Billing_Cycles_Pkg.Get_Billing_Date(Object_Id
76 ,l_Report_Start_Date
77 ,p_Reporting_Cycle_Id
78 ,sysdate
79 ,l_Last_Report_End_Date);
80 END IF;
81
82 x_Report_Start_Date := l_Report_Start_Date;
83 x_Report_End_Date := l_Report_End_Date;
84
85 END GET_REPORT_START_END_DATES;
86
87 /* This function detremines the whether a particular action
88 ia allowed or not on the progress report, based on the
89 system status of the progres report
90 IN PARAMETERS p_current_rep_status - Current user status code of the report
91 p_action - Action the user wants to perform.Possible values are
92 - 'REWORK'
93 - 'EDIT'
94 - 'SUBMIT'
95 - 'PUBLISH'
96 - 'CANCEL'
97 p_version_id - Version_id of the progress report
98 OUT PARAMETERS x_ret_code - Y ; if action allowed, N- Action not allowed
99 x_retun_status - Success or Failure status
100 x_msg_count - Exception message count
101 x_msg_data - Exception message
102 */
103
104 Function check_action_allowed
105 (
106 p_current_rep_status IN VARCHAR2,
107 p_action_code IN VARCHAR2,
108 p_version_id IN NUMBER ) return VARCHAR2 IS
109
110 Cursor C is
111 Select project_system_status_code
112 from pa_project_statuses
113 where project_status_code = p_current_rep_status;
114 l_project_system_status_code pa_project_statuses.project_system_status_code%TYPE;
115
116 Cursor C1 is
117 Select nvl(approval_required,'N') approval_required
118 -- ,nvl(auto_publish,'N') auto_publish
119 from pa_object_page_layouts pop
120 ,pa_progress_report_vers prv
121 where pop.object_type = prv.object_type
122 and pop.object_id = prv.object_id
123 and pop.report_type_id = prv.report_type_id ---report_type_id will be there for PPR
124 and pop.page_type_code = prv.page_type_code
125 and prv.version_id = p_version_id;
126
127 l_approval_required pa_object_page_layouts.approval_required%TYPE;
128 l_auto_publish VARCHAR2(1);
129 x_ret_code VARCHAR2(1); --FND_API.G_TRUE%TYPE;
130 x_return_status VARCHAR2(1); --FND_API.G_RET_STS_SUCCESS%TYPE;
131 BEGIN
132 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_UTILS_PKG.check_action_allowed');
133 x_ret_code:= fnd_api.g_true;
134 x_return_status:=fnd_api.g_ret_sts_success;
135 -- Initialize the Error Stack
136 PA_DEBUG.init_err_stack('PA_PROJECT_SUBTEAMS_PVT.Create_Subteam');
137
138 -- Validation the INPUT parameters
139 open C;
140 fetch C into l_project_system_status_code;
141 if (C%NOTFOUND) then
142 PA_UTILS.Add_Message( p_app_short_name => 'PA'
143 ,p_msg_name => 'PA_PJX_INV_PRJ_REP_STATUS');
144 x_return_status := FND_API.G_RET_STS_ERROR;
145 --x_ret_code:= fnd_api.g_false;
146 --return x_ret_code;
147 --x_msg_count := x_msg_count + 1;
148 end if;
149 close C;
150 open C1;
151 fetch C1 into l_approval_required; --,l_auto_publish;
152 if (C1%NOTFOUND) then
153 PA_UTILS.Add_Message( p_app_short_name => 'PA'
154 ,p_msg_name => 'PA_PJX_INV_REP_VERSION');
155 x_return_status := FND_API.G_RET_STS_ERROR;
156 --x_ret_code:= fnd_api.g_false;
157 --x_msg_count := x_msg_count + 1;
158 end if;
159 close C1;
160 if (l_approval_required = 'A') then
161 l_auto_publish := 'Y';
162 else
163 l_auto_publish := 'N';
164 end if;
165 if (p_action_code NOT IN ('REWORK','EDIT','SUBMIT','PUBLISH','CANCEL')) then
166 PA_UTILS.Add_Message( p_app_short_name => 'PA'
167 ,p_msg_name => 'PA_PJX_INV_ACTION_CODE');
168 x_return_status := FND_API.G_RET_STS_ERROR;
169 --x_msg_count := x_msg_count + 1;
170 end if;
171 /* Return False if any parameter validation fails */
172 IF (x_return_status = FND_API.G_RET_STS_ERROR) then
173 x_ret_code:= fnd_api.g_false;
174 RETURN x_ret_code;
175 END IF;
176
177 IF (l_project_system_status_code = 'PROGRESS_REPORT_WORKING') then
178 IF ( (p_action_code = 'EDIT') OR
179 (p_action_code ='SUBMIT' AND l_approval_required = 'Y') OR
180 (p_action_code ='PUBLISH' AND l_approval_required = 'N') ) then
181 x_ret_code:= fnd_api.g_true;
182 ELSE
183 x_ret_code:= fnd_api.g_false;
184 END IF;
185 ELSIF (l_project_system_status_code = 'PROGRESS_REPORT_SUBMITTED' OR
186 l_project_system_status_code = 'PROGRESS_REPORT_REJECTED') then
187 IF ( p_action_code = 'REWORK' ) THEN
188 x_ret_code:= fnd_api.g_true;
189 ELSE
190 x_ret_code:= fnd_api.g_false;
191 END IF;
192 ELSIF (l_project_system_status_code = 'PROGRESS_REPORT_APPROVED') THEN
193 IF ( (p_action_code ='REWORK' ) OR
194 (p_action_code ='PUBLISH' AND l_auto_publish = 'N') ) then
195 x_ret_code:= fnd_api.g_true;
196 ELSE
197 x_ret_code:= fnd_api.g_false;
198 END IF;
199 ELSIF (l_project_system_status_code = 'PROGRESS_REPORT_PUBLISHED') THEN
200 IF ( p_action_code ='CANCEL') then
201 x_ret_code:= fnd_api.g_true;
202 ELSE
203 x_ret_code:= fnd_api.g_false;
204 END IF;
205 ELSIF (l_project_system_status_code = 'PROGRESS_REPORT_CANCELED') THEN
206 x_ret_code:= fnd_api.g_false;
207 END IF;
208 return x_ret_code;
209 EXCEPTION
210 WHEN OTHERS THEN
211 -- Set the excetption Message and the stack
212 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PROGRESS_REPORT_UTILS_PKG'
213 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
214 --
215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
216 x_ret_code:= fnd_api.g_false;
217 return x_ret_code;
218 --RAISE; -- This is optional depending on the needs
219 END check_action_allowed;
220
221 procedure Validate_Prog_Proj_Dates (p_project_id IN Number,
222 p_scheduled_st_date IN Date,
223 p_scheduled_ed_date IN Date,
224 p_estimated_st_date IN Date,
225 p_estimated_ed_date IN Date,
226 p_actual_st_date IN Date,
227 p_actual_ed_date IN Date,
228 p_percent_complete IN Number,
229 p_est_to_complete IN Number,
230 x_return_status OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
231 x_msg_count OUT NOCOPY Number, --File.Sql.39 bug 4440895
232 x_msg_data OUT NOCOPY Varchar2) is --File.Sql.39 bug 4440895
233 l_msg_index_out Number;
234
235 begin
236 x_return_status := FND_API.G_RET_STS_SUCCESS;
237 x_msg_count := 0;
238 FND_MSG_PUB.initialize;
239
240 if p_estimated_ed_date < p_estimated_st_date then
241 PA_UTILS.Add_Message( p_app_short_name => 'PA'
242 ,p_msg_name => 'PA_EST_DATES_INV');
243 x_return_status := FND_API.G_RET_STS_ERROR;
244 end if;
245
246 if p_actual_ed_date < p_actual_st_date then
247 PA_UTILS.Add_Message( p_app_short_name => 'PA'
248 ,p_msg_name => 'PA_ACT_DATES_INV');
249 x_return_status := FND_API.G_RET_STS_ERROR;
250 end if;
251
252 -- if actual dates are given estimated are not required
253
254 /* if p_estimated_st_date > get_earliest_task_st_date(p_project_id) then
255 PA_UTILS.Add_Message( p_app_short_name => 'PA'
256 ,p_msg_name => 'PA_EST_ST_DATE_INV');
257 x_return_status := FND_API.G_RET_STS_ERROR;
258 end if;
259
260 if p_estimated_ed_date < get_latest_task_ed_date(p_project_id) then
261 PA_UTILS.Add_Message( p_app_short_name => 'PA'
262 ,p_msg_name => 'PA_EST_ED_DATE_INV');
263 x_return_status := FND_API.G_RET_STS_ERROR;
264 end if;
265
266 if p_actual_st_date > get_earliest_task_st_date(p_project_id) then
267 PA_UTILS.Add_Message( p_app_short_name => 'PA'
268 ,p_msg_name => 'PA_ACT_ST_DATE_INV');
269 x_return_status := FND_API.G_RET_STS_ERROR;
270 end if;
271
272 if p_actual_ed_date < get_latest_task_ed_date(p_project_id) then
273 PA_UTILS.Add_Message( p_app_short_name => 'PA'
274 ,p_msg_name => 'PA_ACT_ED_DATE_INV');
275 x_return_status := FND_API.G_RET_STS_ERROR;
276 end if;
277 */
278
279 if not (p_percent_complete between 0 and 100) then
280 PA_UTILS.Add_Message( p_app_short_name => 'PA'
281 ,p_msg_name => 'PA_PERC_COMP_INV');
282 x_return_status := FND_API.G_RET_STS_ERROR;
283 end if;
284
285 if (p_est_to_complete < 0) then
286 PA_UTILS.Add_Message( p_app_short_name => 'PA'
287 ,p_msg_name => 'PA_EST_TO_COMP_INV');
288 x_return_status := FND_API.G_RET_STS_ERROR;
289
290 end if;
291
292 x_msg_count := FND_MSG_PUB.Count_Msg;
293 IF x_msg_count = 1 THEN
294 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
295 ,p_msg_index => 1
296 ,p_data => x_msg_data
297 ,p_msg_index_out => l_msg_index_out
298 );
299 END IF;
300
301 fnd_msg_pub.count_and_get(p_count => x_msg_count,
302 p_data => x_msg_data);
303 end validate_prog_proj_dates;
304
305
306 PROCEDURE is_template_editable
307 (
308 p_page_id NUMBER,
309
310 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
311 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
312 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
313 ) IS
314
315 Cursor c_page_type
316 is select page_type_code
317 from pa_object_page_layouts
318 where page_id = p_page_id;
319
320 CURSOR get_page_type_code
321 IS
322 SELECT page_type_code
323 FROM pa_page_layouts
324 WHERE page_id = p_page_id;
325
326 CURSOR check_update_report_ok
327 IS SELECT 'N' FROM
328 dual
329 WHERE exists
330 (SELECT * FROM
331 pa_progress_report_vers
332 WHERE page_id = p_page_id
333 AND (report_status_code = 'PROGRESS_REPORT_PUBLISHED' OR
334 report_status_code = 'PROGRESS_REPORT_SUBMITTED' OR
335 report_status_code = 'PROGRESS_REPORT_APPROVED'));
336
337 l_dummy VARCHAR2(1);
338 l_ok_to_delete VARCHAR2(1):= 'Y';
339 l_msg_index_out Number;
340 l_page_type_code varchar2(30);
341 l_ret VARCHAR2(1);
342
343 BEGIN
344
345 --Clear the global PL/SQL message table
346 IF FND_API.TO_BOOLEAN( fnd_api.g_true ) THEN
347 FND_MSG_PUB.initialize;
348 END IF;
349
350 x_return_status := FND_API.G_RET_STS_SUCCESS;
351 -- Check it is a seeded template
352 if (p_page_id < 1000) then
353 l_ok_to_delete := 'N';
354
355 --Bug 3684164. We would allow updation of status report page layouts even if
356 --the page layout is associated to a project / report type.
357 /*
358 else
359 --Bug#3302984 ,If page type code is not PPR and association exist, set l_ok_to_delete as Y
360 open c_page_type;
361 fetch c_page_type into l_page_type_code;
362 if (c_page_type%found) then
363 if(l_page_type_code <> 'PPR') then
364 l_ok_to_delete := 'Y';
365 else
366 OPEN check_update_report_ok;
367 FETCH check_update_report_ok INTO l_dummy;
368 IF (check_update_report_ok%found) THEN
369 l_ok_to_delete := 'N';
370 END IF;
371 CLOSE check_update_report_ok;
372 end if;
373 end if;
374 close c_page_type;
375 */
376 end if;
377
378 --Bug 3684164.
379 --Commenting out the code as currently we cannot update only the seeded page layouts.
380 /*
381 --- check if a page is attached to a report type
382 if (pa_report_Types_utils.page_used_by_report_type(p_page_id) = 'Y') then
383 l_ok_to_delete := 'N';
384 end if;
385
386
387
388 -- add for non-PPR report types
389 OPEN get_page_type_code;
390 FETCH get_page_type_code INTO l_page_type_code;
391 CLOSE get_page_type_code;
392
393 IF (l_page_type_code <> 'PPR') then
394 l_ret := pa_page_layout_utils.check_page_layout_deletable(p_page_id);
395 IF l_ret = 'N' THEN
396 l_ok_to_delete := 'N';
397 END IF;
398 END IF;
399 */
400
401 IF (l_ok_to_delete = 'N') then
402 PA_UTILS.Add_Message( p_app_short_name => 'PA'
403 ,p_msg_name => 'PA_EDIT_TEMPLATE_INV');
404 x_return_status := FND_API.G_RET_STS_ERROR;
405 x_msg_count := FND_MSG_PUB.Count_Msg;
406 IF x_msg_count = 1 then
407 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
408 ,p_msg_index => 1
409 ,p_data => x_msg_data
410 ,p_msg_index_out => l_msg_index_out
411 );
412 END IF;
413 END IF;
414
415
416
417 END is_template_editable;
418
419
420 PROCEDURE update_perccomplete
421 (
422 p_object_id NUMBER,
423 p_object_type VARCHAR2,
424 p_percent_complete NUMBER,
425 p_asof_date DATE,
426
427 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
428 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
429 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
430 ) IS
431
432 l_project_id NUMBER;
433 l_task_id NUMBER;
434
435 CURSOR get_project_id IS
436 SELECT project_id FROM pa_tasks
437 WHERE task_id = l_task_id;
438 BEGIN
439
440 -- Initialize the Error Stack
441 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_UTILS.update_perccomplete');
442
443 -- Initialize the return status to success
444 x_return_status := FND_API.G_RET_STS_SUCCESS;
445
446 SAVEPOINT update_perccomplete;
447
448 --debug_msg( 'p_object_id' || To_char(p_object_id));
449 --debug_msg('p_object_type' || p_object_type);
450 --debug_msg('p_percent_complete' || To_char(p_percent_complete));
451
452 IF p_object_type = 'PA_PROJECTS' THEN
453 l_project_id := p_object_id;
454 l_task_id := 0;
455 ELSE
456 l_task_id := p_object_id;
457
458 OPEN get_project_id;
459 fetch get_project_id INTO l_project_id;
460 CLOSE get_project_id;
461 END IF;
462
463
464
465 IF (p_percent_complete > 100 or p_percent_complete< 0 )THEN
466 PA_UTILS.Add_Message( p_app_short_name => 'PA'
467 ,p_msg_name => 'PA_PR_PERCENT_COMPLETE_INV'); x_return_status := FND_API.G_RET_STS_ERROR;
468
469 ELSE
470 -- todo
471
472 --debug_msg ('before get_percent_complete insert' || To_char(l_project_id) );
473 --debug_msg ('before get_percent_complete insert' || To_char(l_task_id) );
474 --debug_msg ('before get_percent_complete insert' || To_char(p_percent_complete) );
475
476
477 pa_percent_complete_pkg.insert_row
478 (
479 l_project_id,
480 l_task_id,
481 p_percent_complete,
482 p_asof_date,
483 NULL,
484 Sysdate,
485 fnd_global.user_id,
486 Sysdate,
487 fnd_global.user_id,
488 fnd_global.user_id,
489 x_return_status,
490 x_msg_data
491 );
492
493 --debug_msg ('before get_percent_complete 3' );
494 END IF;
495
496 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
497
498 ROLLBACK TO update_perccomplete;
499 RETURN;
500 END IF;
501
502 EXCEPTION
503 WHEN OTHERS THEN
504 ROLLBACK TO update_perccomplete;
505
506 --
507 -- Set the excetption Message and the stack
508 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_Progress_Report_Utils.update_perccomplete'
509 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
510 --
511 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
512 RAISE; -- This is optional depending on the needs
513
514
515 END update_perccomplete;
516
517
518 FUNCTION progress_report_exists
519 (
520 p_object_id NUMBER,
521 p_object_type VARCHAR2
522 ) RETURN BOOLEAN
523
524 IS
525
526 CURSOR get_progress_report
527 IS SELECT 'Y'
528 FROM dual
529 WHERE exists(
530 SELECT version_id
531 FROM pa_progress_report_vers
532 WHERE object_id = p_object_id
533 AND object_type = p_object_type
534 );
535
536 l_result VARCHAR2(1);
537
538 BEGIN
539 OPEN get_progress_report;
540 FETCH get_progress_report INTO l_result;
541
542 IF get_progress_report%notfound THEN
543 CLOSE get_progress_report;
544 RETURN FALSE;
545 ELSE
546 CLOSE get_progress_report;
547 RETURN TRUE;
548 END IF;
549
550 END progress_report_exists;
551
552 FUNCTION pagelayout_exists
553 (
554 p_object_id NUMBER,
555 p_object_type VARCHAR2
556 ) RETURN BOOLEAN
557
558 IS
559
560 CURSOR get_page_layout
561 IS SELECT 'Y'
562 FROM dual
563 WHERE exists(
564 SELECT page_id
565 FROM pa_object_page_layouts
566 WHERE object_id = p_object_id
567 AND object_type = p_object_type
568 );
569
570 CURSOR get_obj_region
571 IS SELECT 'Y'
572 FROM dual
573 WHERE exists(
574 SELECT placeholder_reg_code
575 FROM pa_object_regions
576 WHERE object_id = p_object_id
577 AND object_type = p_object_type
578 );
579 l_result VARCHAR2(1);
580
581 BEGIN
582 OPEN get_page_layout;
583 FETCH get_page_layout INTO l_result;
584
585 IF get_page_layout%notfound THEN
586 CLOSE get_page_layout;
587 RETURN FALSE;
588 ELSE
589 CLOSE get_page_layout;
590
591 OPEN get_obj_region;
592 FETCH get_obj_region INTO l_result;
593 IF get_obj_region%notfound THEN
594 CLOSE get_obj_region;
595 RETURN FALSE;
596 END IF;
597 RETURN TRUE;
598 END IF;
599
600 END pagelayout_exists;
601
602 PROCEDURE remove_progress_report_setup
603 (
604 p_object_id IN NUMBER := NULL,
605 p_object_type IN VARCHAR2 := NULL,
606
607 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
608 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
609 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
610 ) IS
611
612 BEGIN
613
614 -- Initialize the Error Stack
615 PA_DEBUG.init_err_stack('PA_PROGRESS_REPORT_UTILS.remove_progress_report_setup');
616
617 -- Initialize the return status to success
618 x_return_status := FND_API.G_RET_STS_SUCCESS;
619
620 SAVEPOINT remove_progress_report_setup;
621
622 pa_progress_report_pkg.delete_object_page_layouts
623 (
624 p_object_id,
625 p_object_type,
626
627 x_return_status,
628 x_msg_count,
629 x_msg_data
630 );
631
632 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
633
634 ROLLBACK TO remove_progress_report_setup;
635 RETURN;
636 END IF;
637
638 EXCEPTION
639 WHEN OTHERS THEN
640 ROLLBACK TO remove_progress_report_setup;
641
642 --
643 -- Set the excetption Message and the stack
644 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_Progress_Report_Utils.remove_progress_report_setup'
645 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
646 --
647 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
648 RAISE; -- This is optional depending on the needs
649
650 END remove_progress_report_setup;
651
652 /* This is the function to get the page_id for the specified objcet of given
653 page type.The function will try to get the page id for the object in the
654 following order.
655 1. Find any page_id associted to the object_id.
656 Association exists at object level?
657 Yes - Use the page id associted at the object level
658 No -
659 2. Find the page associated at the project type level
660 Association exists?
661 Yes - Use the association at Project Type level.
662 No ?
663 3. Get the default page associated at the page type level
664 This is stored in attribute3 in fnd_lookup_values of
665 lookup_code='PA_PAGE_TYPES' and lookup_code = page_type_code
666 Each page type owners must seed a default layout for the page
667 and populate the attribute3 with that value.
668 If the defaulting logic is going to be different, plrease use your own
669 method to derive the page_id for the object.
670 */
671 FUNCTION get_object_page_id (
672 p_page_type_code IN varchar2,
673 p_object_type IN varchar2,
674 p_object_id IN NUMBER,
675 p_report_Type_id IN NUMBER := null)
676 return number
677 IS
678 l_page_id NUMBER := null;
679 l_page_id_tmp VARCHAR2(100) := null;
680 l_model VARCHAR2(10) := null;
681 l_page_id_s VARCHAR2(10) := null;
682 cursor C1
683 is
684 select page_id from pa_object_page_layouts
685 where page_type_code = p_page_type_code
686 and object_type = p_object_type
687 and object_id = p_object_id;
688
689 cursor C3
690 is
691 select page_id from pa_object_page_layouts
692 where object_id = p_object_id
693 and object_type = p_object_type
694 and report_type_id = p_report_Type_id
695 and page_type_code = p_page_type_code;
696
697 /* Cursor to get the default page layout associated with the page type */
698 Cursor C2 is
699 select to_number(attribute3)
700 from pa_lookups
701 where lookup_type = 'PA_PAGE_TYPES'
702 and lookup_code = p_page_type_code;
703
704 CURSOR get_ai_page_id IS
705 select task_progress_entry_page_id
706 from pa_proj_elements ppe
707 where ppe.proj_element_id = p_object_id
708 and ppe.object_type = 'PA_TASKS';
709
710 CURSOR task_type_page_id IS
711 select ptt.task_progress_entry_page_id
712 from pa_proj_elements ppe,
713 pa_task_types ptt
714 where ppe.type_id = ptt.task_type_id
715 and ppe.proj_element_id = p_object_id
716 and ppe.object_type = 'PA_TASKS';
717
718 BEGIN
719
720
721 if (p_report_Type_id is null) then
722 open C1;
723 fetch C1 into l_page_id;
724 close C1;
725 else
726 open C3;
727 fetch C3 into l_page_id;
728 close C3;
729 end if;
730 if (l_page_id is null) then ---------(c1%NOTFOUND) then
731 if (PA_INSTALL.is_prm_licensed = 'Y'
732 AND p_object_type = 'PA_PROJECTS'
733 AND p_object_id IS NOT null
734 and PA_PROJECT_UTILS.Is_Admin_Project(p_object_id)='N'
735 and p_page_type_code = 'PH'
736 ) then
737 l_page_id := 10;
738 ELSE
739
740 --Bug#3302984
741
742 IF p_page_type_code = 'TM' THEN
743 fnd_profile.get('PA_TEAM_HOME_PAGELAYOUT',l_page_id_tmp);
744 IF l_page_id_tmp IS NULL THEN
745 open C2;
746 fetch C2 into l_page_id;
747 close C2;
748 ELSE
749 -- Bug 3875716. handle the case where the profile value
750 --doesnot contain PAGE prefix.
751 if instr(l_page_id_tmp,':',1,1) = 0 then
752 l_page_id := to_number(l_page_id_tmp);
753 else
754 IF (substr(l_page_id_tmp,1,4)='PAGE') THEN
755 l_page_id := to_number(substr(l_page_id_tmp,6));
756 else
757 --This case should not arise. To be on safer side let us
758 --return the default page id in this case.
759 open C2;
760 fetch C2 into l_page_id;
761 close C2;
762 END IF;
763 end if;
764 /*
765 select substr(l_page_id_tmp,1,4) into l_model from dual;
766 IF (l_model='PAGE') THEN
767 select substr(l_page_id_tmp,6) into l_page_id_s from dual;
768 l_page_id := to_number(l_page_id_s);
769 END IF;
770 */
771 END IF;
772
773 ELSIF p_page_type_code = 'AI' THEN
774
775 /*
776 the query to get page_id for this page type should try to
777 get from pa_task_types if it is null in pa_proj_elements. If it is null in
778 pa_task_types then it should get the default.
779 */
780 open get_ai_page_id;
781 fetch get_ai_page_id into l_page_id;
782 close get_ai_page_id;
783
784 IF l_page_id IS NULL THEN
785 open task_type_page_id;
786 fetch task_type_page_id into l_page_id;
787 close task_type_page_id;
788 END IF;
789
790 IF l_page_id IS NULL THEN
791 open C2;
792 fetch C2 into l_page_id;
793 close C2;
794 END IF;
795
796 ELSE
797
798 open C2;
799 fetch C2 into l_page_id;
800 close C2;
801 END IF;
802
803
804 end if;
805 end if;
806 return l_page_id;
807 END get_object_page_id;
808
809 FUNCTION get_object_region (
810 p_object_type IN varchar2,
811 p_object_id IN NUMBER ,
812 p_placeholder_reg_code varchar2)
813 return varchar2
814 is
815 l_return_reg_code varchar2(250) := null;
816 Cursor C is
817 select replacement_reg_code
818 from pa_object_regions
819 where object_type = p_object_type
820 and object_id = p_object_id
821 and placeholder_reg_code = p_placeholder_reg_code;
822 Begin
823 Open C;
824 fetch C into l_return_reg_code;
825 if (C%NOTFOUND) then
826 l_return_reg_code := p_placeholder_reg_code;
827 end if;
828 close C;
829 return l_return_reg_code;
830 Exception
831 when others then
832 l_return_reg_code := p_placeholder_reg_code;
833 return l_return_reg_code;
834 End get_object_region;
835
836 FUNCTION is_delete_page_layout_ok(
837 p_page_type_code IN varchar2,
838 p_object_type IN varchar2,
839 p_object_id IN NUMBER,
840 p_report_type_id IN NUMBER
841 )
842 RETURN VARCHAR2 is
843
844 -- can not delere the ppr pagelayout when there is any report
845 -- which is not published nor cancelled
846
847 CURSOR get_ppr_pagelayout_delete_ok IS
848 select 'N'
849 from dual
850 where exists
851 (
852 select version_id
853 FROM PA_PROGRESS_REPORT_VERS
854 WHERE Object_Id = p_object_id
855 AND Object_Type = p_object_type
856 AND report_Type_id = p_report_Type_id ); --- report_Type_id will be there
857 --------- AND Report_Status_Code <> 'PROGRESS_REPORT_PUBLISHED'
858 --------- and Report_Status_Code <> 'PROGRESS_REPORT_CANCELED');
859 l_return VARCHAR2 (1) := 'N';
860 BEGIN
861 -- check if there is any non published or obsoleted report available
862 IF p_page_type_code = 'PPR' then
863 OPEN get_ppr_pagelayout_delete_ok;
864 FETCH get_ppr_pagelayout_delete_ok INTO l_return;
865
866 IF get_ppr_pagelayout_delete_ok%notfound THEN
867 l_return := 'Y';
868 END IF;
869
870 CLOSE get_ppr_pagelayout_delete_ok;
871
872 END IF;
873
874 RETURN l_return;
875
876 END is_delete_page_layout_ok;
877
878 FUNCTION is_edit_page_layout_ok(
879 p_page_type_code IN varchar2,
880 p_object_type IN varchar2,
881 p_object_id IN NUMBER,
882 p_report_type_id IN NUMBER
883 )
884 RETURN VARCHAR2 is
885
886 -- can not delere the ppr pagelayout when there is any report
887 -- which is not published nor cancelled
888
889 CURSOR get_ppr_pagelayout_edit_ok IS
890 select 'N'
891 from dual
892 where exists
893 (
894 select version_id
895 FROM PA_PROGRESS_REPORT_VERS
896 WHERE Object_Id = p_object_id
897 AND Object_Type = p_object_type
898 AND report_Type_id = p_report_Type_id
899 AND Report_Status_Code <> 'PROGRESS_REPORT_PUBLISHED'
900 and Report_Status_Code <> 'PROGRESS_REPORT_CANCELED');
901 l_return VARCHAR2 (1) := 'N';
902 BEGIN
903 -- check if there is any non published or obsoleted report available
904 IF p_page_type_code = 'PPR' then
905 OPEN get_ppr_pagelayout_edit_ok;
906 FETCH get_ppr_pagelayout_edit_ok INTO l_return;
907
908 IF get_ppr_pagelayout_edit_ok%notfound THEN
909 l_return := 'Y';
910 END IF;
911
912 CLOSE get_ppr_pagelayout_edit_ok;
913
914 END IF;
915
916 RETURN l_return;
917
918 END is_edit_page_layout_ok;
919
920 /* Bug 2798485 - Following function has been fixed to check security
921 in the following order.
922 1> Based in user's privilege PA_PROGRESS_REPORT_EDIT (return 2 if true)
923 2> Based on user's privilege in the ACCESS_LIST (return if 1 or 2 i.e. user has view or edit privilege)
924 3> Based in user's privilege PA_PROGRESS_REPORT_VIEW (return 1 if true) */
925
926 /* Old Function */
927 /* Function Check_Security_For_ProgRep(p_object_Type IN VARCHAR2,
928 p_object_Id IN NUMBER,
929 p_report_type_id IN NUMBER) return NUMBER Is
930
931 l_object_page_layout_id number;
932 x_return_code varchar2(2000);
933 x_return_status varchar2(1);
934 x_msg_count number;
935 x_msg_data varchar2(2000);
936 Begin
937 pa_security_pvt.check_user_privilege(x_ret_code => x_return_code,
938 x_return_status => x_return_status,
939 x_msg_count => x_msg_count,
940 x_msg_data => x_msg_data,
941 p_privilege => 'PA_PROGRESS_REPORT_EDIT',
942 p_object_name => 'PA_PROJECTS',
943 p_object_key => p_object_id);
944 if (x_return_code = 'T') then
945 return 2;
946 else
947 pa_security_pvt.check_user_privilege(x_ret_code => x_return_code,
948 x_return_status => x_return_status,
949 x_msg_count => x_msg_count,
950 x_msg_data => x_msg_data,
951 p_privilege => 'PA_PROGRESS_REPORT_VIEW',
952 p_object_name => 'PA_PROJECTS',
953 p_object_key => p_object_id);
954 if (x_return_code = 'T') then
955 return 1;
956 end if;
957 end if;
958
959 begin
960 select object_page_layout_id
961 into l_object_page_layout_id
962 from pa_object_page_layouts
963 where object_id = p_object_id and
964 object_type = p_object_type and
965 report_Type_id = p_report_Type_id;
966 exception when others then
967 return 0;
968 end;
969
970 return pa_distribution_list_utils.get_access_level(p_object_Type => 'PA_OBJECT_PAGE_LAYOUT',
971 p_object_id => l_object_page_layout_id,
972 x_return_status => x_return_status,
973 x_msg_count => x_msg_count,
974 x_msg_data => x_msg_data);
975 End Check_Security_For_ProgRep; */
976
977
978 /* New Function */
979
980 Function Check_Security_For_ProgRep(p_object_Type IN VARCHAR2,
981 p_object_Id IN NUMBER,
982 p_report_type_id IN NUMBER) return NUMBER Is
983
984 l_object_page_layout_id number;
985 access_level number;
986 x_return_code varchar2(2000);
987 x_return_status varchar2(1);
988 x_msg_count number;
989 x_msg_data varchar2(2000);
990
991
992 Begin
993
994 begin
995 select object_page_layout_id
996 into l_object_page_layout_id
997 from pa_object_page_layouts
998 where object_id = p_object_id and
999 object_type = p_object_type and
1000 report_Type_id = p_report_Type_id;
1001 exception when others then
1002 l_object_page_layout_id := -9999;
1003 end;
1004
1005 pa_security_pvt.check_user_privilege(x_ret_code => x_return_code,
1006 x_return_status => x_return_status,
1007 x_msg_count => x_msg_count,
1008 x_msg_data => x_msg_data,
1009 p_privilege => 'PA_PROGRESS_REPORT_EDIT',
1010 p_object_name => 'PA_PROJECTS',
1011 p_object_key => p_object_id);
1012 if (x_return_code = 'T') then
1013 return 2;
1014 else
1015 if (l_object_page_layout_id <> -9999) then
1016 access_level := pa_distribution_list_utils.get_access_level(p_object_Type => 'PA_OBJECT_PAGE_LAYOUT',
1017 p_object_id => l_object_page_layout_id,
1018 x_return_status => x_return_status,
1019 x_msg_count => x_msg_count,
1020 x_msg_data => x_msg_data);
1021 else
1022 access_level := 0;
1023 end if;
1024 if ( access_level <> 0 ) then
1025 return access_level;
1026 else
1027
1028 pa_security_pvt.check_user_privilege(x_ret_code => x_return_code,
1029 x_return_status => x_return_status,
1030 x_msg_count => x_msg_count,
1031 x_msg_data => x_msg_data,
1032 p_privilege => 'PA_PROGRESS_REPORT_VIEW',
1033 p_object_name => 'PA_PROJECTS',
1034 p_object_key => p_object_id);
1035 if (x_return_code = 'T') then
1036 return 1;
1037 end if;
1038 end if;
1039 end if;
1040
1041 return 0;
1042
1043
1044 End Check_Security_For_ProgRep;
1045
1046 Function Check_Security_For_ProgRep(p_object_Type IN VARCHAR2,
1047 p_object_Id IN NUMBER,
1048 p_report_type_id IN NUMBER,
1049 p_Action IN VARCHAR) return VARCHAR2 Is
1050 ret number;
1051 privilege varchar2(30);
1052 x_return_code varchar2(2000);
1053 x_return_status varchar2(1);
1054 x_msg_count number;
1055 x_msg_data varchar2(2000);
1056
1057 Begin
1058
1059 /* if (p_action = 'EDIT') then
1060 privilege := 'PA_PROGRESS_REPORT_EDIT';
1061 else
1062 privilege := 'PA_PROGRESS_REPORT_VIEW';
1063 end if;
1064 pa_security_pvt.check_user_privilege(x_ret_code => x_return_code,
1065 x_return_status => x_return_status,
1066 x_msg_count => x_msg_count,
1067 x_msg_data => x_msg_data,
1068 p_privilege => privilege,
1069 p_object_name => 'PA_PROJECTS',
1070 p_object_key => p_object_id);
1071 if (x_return_code = 'T') then
1072 return x_return_code;
1073 else */
1074
1075 ret := Check_Security_For_ProgRep(p_object_type => p_object_type,
1076 p_object_id => p_object_id,
1077 p_report_Type_id => p_report_Type_id);
1078
1079 if (p_action = 'EDIT' and ret = 2) then
1080 return 'T';
1081 elsif (p_action = 'VIEW' and (ret = 1 or ret = 2)) then
1082 return 'T';
1083 else
1084 return 'F';
1085 end if;
1086 ---end if;
1087
1088 return 'F';
1089
1090 End Check_Security_For_ProgRep;
1091
1092 Function is_cycle_ok_to_delete(p_reporting_cycle_id IN NUMBER) return varchar2
1093 IS
1094 cursor rep_cycle is
1095 select 'N'
1096 from pa_object_page_layouts
1097 where reporting_cycle_id = p_reporting_cycle_id;
1098
1099 retval varchar2(1);
1100 l_rep_cycle rep_cycle%rowtype;
1101
1102 Begin
1103 open rep_cycle;
1104 fetch rep_cycle into l_rep_cycle;
1105 if rep_cycle%found then
1106 close rep_cycle;
1107 return 'N';
1108 else
1109 close rep_cycle;
1110 return 'Y';
1111 end if;
1112 End is_cycle_ok_to_delete;
1113
1114 Function get_latest_working_report_id(p_object_Type IN VARCHAR2,
1115 p_object_Id IN NUMBER,
1116 p_report_type_id IN NUMBER) return NUMBER IS
1117 l_version_id number;
1118 BEGIN
1119 select version_id
1120 into l_version_id
1121 from pa_progress_report_vers
1122 where object_id = p_object_id
1123 and object_Type = p_object_Type
1124 and report_Type_id = p_report_Type_id
1125 and report_status_code = 'PROGRESS_REPORT_WORKING'
1126 and (report_end_date, last_update_date) = (select max(report_end_Date), max(last_update_date)
1127 from pa_progress_report_vers
1128 where object_id = p_object_id
1129 and object_Type = p_object_Type
1130 and report_Type_id = p_report_Type_id
1131 and report_status_code = 'PROGRESS_REPORT_WORKING');
1132 return l_version_id;
1133
1134 exception when others then
1135 return -999;
1136
1137 END get_latest_working_report_id;
1138
1139 FUNCTION get_tab_menu_name(p_project_id IN NUMBER) RETURN VARCHAR2
1140 IS
1141 l_menu_name VARCHAR2(100);
1142 BEGIN
1143 SELECT menu_name
1144 INTO l_menu_name
1145 FROM fnd_menus m, pa_object_page_layouts o
1146 WHERE m.menu_id=o.page_id
1147 AND o.page_type_code='TAB_MENU'
1148 AND o.object_type='PA_PROJECTS'
1149 AND o.object_id=p_project_id;
1150
1151 RETURN l_menu_name;
1152 EXCEPTION
1153 WHEN OTHERS THEN
1154 RETURN 'PA_SINGLE_TAB_MENU';
1155 END get_tab_menu_name;
1156
1157 -- Notes: Due to bug 3620190 this api is no longer in used. The tab setup is
1158 -- already copied as part of pa_object_page_layouts table.
1159 PROCEDURE copy_project_tab_menu(
1160 p_src_project_id IN NUMBER,
1161 p_dest_project_id IN NUMBER,
1162 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1163 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1164 x_return_status OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
1165 IS
1166 BEGIN
1167 x_return_status := FND_API.G_RET_STS_SUCCESS;
1168 x_msg_count := 0;
1169 x_msg_data := NULL;
1170 END copy_project_tab_menu;
1171
1172 END PA_Progress_Report_Utils;
[Home] [Help]