1 PACKAGE BODY pa_project_stus_utils AS
2 -- $Header: PAPSUTLB.pls 120.3 2007/02/08 12:50:38 sukhanna ship $
3
4 l_pkg_name VARCHAR2(30) := 'PA_PROJECT_STUS_UTILS';
5 /* Bug 3059344: this function is commented, please see eof for the function code
6 FUNCTION Is_Project_Closed
7 (x_project_id IN NUMBER ) return VARCHAR2 IS
8
9 CURSOR l_proj_csr IS
10 SELECT project_status_code
11 FROM pa_projects_all
12 WHERE project_id = x_project_id;
13 l_proj_status_code VARCHAR2(30);
14 l_closed VARCHAR2(1) := 'N';
15 BEGIN
16 OPEN l_proj_csr;
17 FETCH l_proj_csr INTO l_proj_status_code;
18 IF l_proj_csr%NOTFOUND THEN
19 CLOSE l_proj_csr;
20 RETURN 'N';
21 END IF;
22 CLOSE l_proj_csr;
23
24 l_closed := Is_Project_Status_Closed (l_proj_status_code);
25 RETURN NVL(l_closed,'N');
26
27 EXCEPTION
28 WHEN OTHERS THEN
29 RETURN 'N';
30 END Is_Project_Closed;
31 */
32
33 -- STATUS MODEL changes - the following function is for status_type PROJECT
34 -- only since it calls pa_utils2.IsProjectClosed which compares Project
35 -- related system statuses only. The filer for status_type has been added
36 -- only to make it apparent that this function is for PROJECT only.
37 /* Bug 3059344: this function is commented, please see eof for the function code
38 FUNCTION Is_Project_Status_Closed
39 (x_project_status_code IN VARCHAR2 ) return VARCHAR2 IS
40 CURSOR l_stus_csr IS
41 SELECT project_system_status_code
42 FROM pa_project_statuses
43 WHERE project_status_code = x_project_status_code
44 and status_type = 'PROJECT';
45
46 l_system_stus_code VARCHAR2(30) := 'N';
47
48 BEGIN
49 OPEN l_stus_csr;
50 FETCH l_stus_csr INTO l_system_stus_code;
51 IF l_stus_csr%NOTFOUND THEN
52 CLOSE l_stus_csr;
53 RETURN 'N';
54 END IF;
55 CLOSE l_stus_csr;
56 IF pa_utils2.IsProjectClosed(l_system_stus_code) = 'Y' THEN
57 RETURN 'Y';
58 ELSE
59 RETURN 'N';
60 END IF;
61 EXCEPTION
62 WHEN OTHERS THEN
63 RETURN 'N';
64
65 END Is_Project_Status_Closed;
66 */
67
68 /* Bug#2431718 The function Is_ARPR_Project_Status_Closed is created to get the
69 closed and partially purged projects only */
70
71 FUNCTION Is_ARPR_Project_Status_Closed
72 (x_project_status_code IN VARCHAR2 ) return VARCHAR2 IS
73 CURSOR l_arpr_stus_csr IS
74 SELECT project_system_status_code
75 FROM pa_project_statuses
76 WHERE project_status_code = x_project_status_code
77 and status_type = 'PROJECT';
78
79 l_system_status_code VARCHAR2(30) := 'N';
80
81 BEGIN
82
83 OPEN l_arpr_stus_csr;
84 FETCH l_arpr_stus_csr INTO l_system_status_code;
85
86 IF l_arpr_stus_csr%NOTFOUND THEN
87 CLOSE l_arpr_stus_csr;
88 RETURN 'N';
89 END IF;
90 CLOSE l_arpr_stus_csr;
91
92 if l_system_status_code in ( 'CLOSED',
93 'PARTIALLY_PURGED') then
94 RETURN ( 'Y');
95 else
96 RETURN ( 'N');
97 end if;
98
99 EXCEPTION
100 WHEN OTHERS THEN
101 RETURN 'N';
102
103 END Is_ARPR_Project_Status_Closed;
104
105
106 -- STATUS MODEL changes - the following function is for status_type PROJECT
107 -- only since it calls pa_utils2.IsProjectInPurgeStatus which compares Project
108 -- related system statuses only. The filer for status_type has been added
109 -- only to make it apparent that this function is for PROJECT only.
110 FUNCTION Is_Project_In_Purge_Status
111 (x_project_status_code IN VARCHAR2 ) return VARCHAR2 IS
112 CURSOR l_stus_csr IS
113 SELECT project_system_status_code
114 FROM pa_project_statuses
115 WHERE project_status_code = x_project_status_code
116 and status_type = 'PROJECT';
117
118 l_system_stus_code VARCHAR2(30) := 'N';
119
120 BEGIN
121 OPEN l_stus_csr;
122 FETCH l_stus_csr INTO l_system_stus_code;
123 IF l_stus_csr%NOTFOUND THEN
124 CLOSE l_stus_csr;
125 RETURN 'N';
126 END IF;
127 CLOSE l_stus_csr;
128 IF pa_utils2.IsProjectInPurgeStatus(l_system_stus_code) = 'Y' THEN
129 RETURN 'Y';
130 ELSE
131 RETURN 'N';
132 END IF;
133 EXCEPTION
134 WHEN OTHERS THEN
135 RETURN 'N';
136
137 END Is_Project_In_Purge_Status;
138
139 Procedure Handle_Project_Status_Change
140 (x_calling_module IN VARCHAR2
141 ,X_project_id IN NUMBER
142 ,X_old_proj_status_code IN VARCHAR2
143 ,X_new_proj_status_code IN VARCHAR2
144 ,X_project_type IN VARCHAR2
145 ,X_project_start_date IN DATE
146 ,X_project_end_date IN DATE
147 ,X_public_sector_flag IN VARCHAR2
148 ,X_attribute_category IN VARCHAR2
149 ,X_attribute1 IN VARCHAR2
150 ,X_attribute2 IN VARCHAR2
151 ,X_attribute3 IN VARCHAR2
152 ,X_attribute4 IN VARCHAR2
153 ,X_attribute5 IN VARCHAR2
154 ,X_attribute6 IN VARCHAR2
155 ,X_attribute7 IN VARCHAR2
156 ,X_attribute8 IN VARCHAR2
157 ,X_attribute9 IN VARCHAR2
158 ,X_attribute10 IN VARCHAR2
159 ,X_pm_product_code IN VARCHAR2
160 ,x_init_msg IN VARCHAR2 := 'Y'
161 ,x_verify_ok_flag OUT NOCOPY VARCHAR2 /* Added the nocopy check for Bug 4537865 */
162 ,x_wf_enabled_flag OUT NOCOPY VARCHAR2 /* Added the nocopy check for Bug 4537865 */
163 ,X_err_stage IN OUT NOCOPY varchar2 /* Added the nocopy check for Bug 4537865 */
164 ,X_err_stack IN OUT NOCOPY varchar2 /* Added the nocopy check for Bug 4537865 */
165 ,x_err_msg_count OUT NOCOPY Number /* Added the nocopy check for Bug 4537865 */
166 ,x_warnings_only_flag OUT NOCOPY VARCHAR2 /* Added the nocopy check for Bug 4537865 */
167 ) IS
168
169 l_msg_count NUMBER;
170 l_err_code NUMBER;
171 l_item_type VARCHAR2(30);
172 l_wf_process VARCHAR2(30);
173 l_wf_enabled_flag VARCHAR2(1);
174 l_msg_data VARCHAR2(2000);
175 l_api_name VARCHAR2(30) := 'Handle_Project_Status_Change';
176 l_org_func_security VARCHAR2(1); /*bug#1968394 */
177 l_warnings_only_flag varchar2(1); --bug3134205
178 BEGIN
179 IF x_init_msg = 'Y' THEN
180 FND_MSG_PUB.Initialize;
181 END IF;
182 x_wf_enabled_flag := NULL;
183 x_verify_ok_flag := 'Y';
184
185 -- Code Added for the bug#1968394
186 -- Test the function security for Org changes
187 --
188 IF (fnd_function.test('PA_PAXPREPR_UPDATE_ORG') = TRUE) THEN
189 l_org_func_security := 'Y';
190 ELSE
191 l_org_func_security := 'N';
192 END IF;
193
194 -- validate the status
195 pa_project_utils2.validate_attribute_change(
196 X_Context => 'PROJECT_STATUS_CHANGE'
197 , X_insert_update_mode => NULL
198 , X_calling_module => x_calling_module
199 , X_project_id => x_project_id
200 , X_task_id => NULL
201 , X_old_value => X_old_proj_status_code
202 , X_new_value => X_new_proj_status_code
203 , X_project_type => X_project_type
204 , X_project_start_date => X_project_start_date
205 , X_project_end_date => X_project_end_date
206 , X_public_sector_flag => X_public_sector_flag
207 , X_task_manager_person_id => NULL
208 , X_Service_type => NULL
209 , X_task_start_date => NULL
210 , X_task_end_date => NULL
211 , X_entered_by_user_id => FND_GLOBAL.USER_ID
212 , X_attribute_category => X_attribute_category
213 , X_attribute1 => X_attribute1
214 , X_attribute2 => X_attribute2
215 , X_attribute3 => X_attribute3
216 , X_attribute4 => X_attribute4
217 , X_attribute5 => X_attribute5
218 , X_attribute6 => X_attribute6
219 , X_attribute7 => X_attribute7
220 , X_attribute8 => X_attribute8
221 , X_attribute9 => X_attribute9
222 , X_attribute10 => X_attribute10
223 , X_pm_product_code => X_pm_product_code
224 , X_pm_project_reference => NULL
225 , X_pm_task_reference => NULL
226 -- , X_functional_security_flag => NULL /* bug#1968394 */
227 , X_functional_security_flag => l_org_func_security /* bug#1968394 */
228 , x_warnings_only_flag => l_warnings_only_flag --bug3134205
229 , X_err_code => l_err_code
230 , X_err_stage => x_err_stage
231 , X_err_stack => x_err_stack );
232
233 x_warnings_only_flag := l_warnings_only_flag; --bug3134205
234
235 IF l_err_code <> 0 THEN
236 x_err_msg_count := FND_MSG_PUB.Count_msg;
237 x_wf_enabled_flag := NULL;
238 x_verify_ok_flag := 'N';
239 RETURN;
240 ELSE
241 x_verify_ok_flag := 'Y';
242
243 Check_Wf_Enabled (x_project_status_code => X_new_proj_status_code,
244 x_project_type => x_project_type,
245 x_project_id => x_project_id,
246 x_wf_item_type => l_item_type,
247 x_wf_process => l_wf_process,
248 x_wf_enabled_flag => l_wf_enabled_flag,
249 x_err_code => l_err_code
250 );
251
252 -- 30-DEC-97, jwhite ---------------------------------------
253 -- Comment related to code change in
254 -- Check_Wf_Enabled procedure:
255 -- Unlike Create_Project API, it is NOT necessary
256 -- to check for x_err_code here. The Check_Wf_Enabled
257 -- procedure overrides the x_wf_enabled_flag (sets to 'N')
258 -- if x_err_code is <> 0.
259 -- Also, WF not coupled to changing statuses.
260 -- So, x_err_code is only meaningful if ORA error. If
261 -- ORA error, then the RAISE in Check_Wf_Enabled
262 -- will automatically rollback this and all higher
263 -- procedures.
264 --
265
266 x_wf_enabled_flag := NVL(l_wf_enabled_flag,'N');
267
268 -- -------------------------------------------------------------------
269
270 END IF;
271
272
273 EXCEPTION
274 WHEN OTHERS THEN
275
276 -- 4537865
277 x_verify_ok_flag := NULL ;
278 x_wf_enabled_flag := 'N' ; -- As per logic in this API
279 X_err_stage := 'In WHEN OTHERS Block of Handle_Project_Status_Change API';
280 x_warnings_only_flag := NULL ;
281
282 FND_MSG_PUB.add_exc_msg
283 ( p_pkg_name => l_pkg_name
284 , p_procedure_name => l_api_name );
285 FND_MSG_PUB.Count_And_Get
286 (p_count => x_err_msg_count ,
287 p_data => X_err_stack);
288 WF_CORE.CONTEXT('PA_PROJECT_STUS_UTILS','HANDLE_PROJECT_STATUS_CHANGE') ;
289 RAISE;
290
291 END Handle_Project_Status_Change;
292
293 --
294 -- History
295 -- 29-DEC-97 jwhite - Populated x_err_code on
296 -- Check_Wf_Enabled procedure.
297 -- 30-DEC-97 jwhite - Override the returned
298 -- x_wf_enabled_flag IF the
299 -- returned x_err_code <>0
300 --
301
302 Procedure Check_Wf_Enabled (x_project_status_code IN VARCHAR2,
303 x_project_type IN VARCHAR2,
304 x_project_id IN NUMBER,
305 x_wf_item_type OUT NOCOPY VARCHAR2, /* Added the nocopy check for Bug 4537865 */
306 x_wf_process OUT NOCOPY VARCHAR2, /* Added the nocopy check for Bug 4537865 */
307 x_wf_enabled_flag OUT NOCOPY VARCHAR2, /* Added the nocopy check for Bug 4537865 */
308 x_err_code OUT NOCOPY NUMBER ) /* Added the nocopy check for Bug 4537865 */
309 IS
310 l_item_type VARCHAR2(30) ;
311 l_wf_process VARCHAR2(30);
312 l_wf_enabled_flag VARCHAR2(1);
313 l_err_code NUMBER;
314 l_msg_count NUMBER;
315 l_msg_data VARCHAR2(2000);
316 l_api_name VARCHAR2(30) := 'Check_Wf_Enabled';
317 x_status_type VARCHAR2(30);
318
319 CURSOR l_get_wf_details_csr (l_project_status_code IN VARCHAR2) IS
320 SELECT workflow_item_type,workflow_process
321 FROM pa_project_statuses
322 WHERE project_status_code = l_project_status_code;
323 BEGIN
324 x_wf_enabled_flag := 'N';
325 x_wf_item_type := NULL;
326 x_wf_process := NULL;
327
328 -- Get the status_type for the given status_code
329 SELECT status_type
330 INTO x_status_type
331 FROM pa_project_statuses
332 WHERE project_status_code=x_project_status_code;
333
334 -- Call the client extn that determines whether workflow is enabled or not
335
336 pa_client_extn_proj_status.Check_wf_enabled
337 (x_project_status_code => x_project_status_code,
338 x_project_type => x_project_type,
339 x_project_id => x_project_id,
340 x_wf_enabled_flag => l_wf_enabled_flag,
341 x_err_code => l_err_code,
342 x_status_type => x_status_type
343 );
344
345 -- 29-DEC-97, jwhite ----------------------------------
346 -- Populate OUT-parameter because
347 -- Create_Project API checks BOTH
348 -- the x_wf_enabled_flag and the
349 -- x_err_code OUT-parameters.
350 -- The Handle_Project_Status_Changes
351 -- procedure of the Update_Project
352 -- API only tests the x_wf_enabled_flag.
353 --
354 x_err_code := l_err_code;
355 -- ----------------------------------------------------------
356
357 -- 30-DEC-97, jwhite ----------------------------------
358 -- Override the returned x_wf_enabled_flag IF the
359 -- returned x_err_code <>0 (meaning either
360 -- business error or SQL error).
361 -- The default value of 'N' will be returned
362 -- to the calling procedure.
363 --
364
365 IF ( (l_wf_enabled_flag = 'Y') AND
366 (l_err_code = 0) )
367 THEN
368 OPEN l_get_wf_details_csr (x_project_status_code);
369 FETCH l_get_wf_details_csr INTO l_item_type,l_wf_process;
370 IF l_get_wf_details_csr%NOTFOUND OR
371 l_item_type IS NULL OR
372 l_wf_process is NULL THEN
373 x_wf_enabled_flag := 'N';
374 CLOSE l_get_wf_details_csr;
375 RETURN;
376 END IF;
377 CLOSE l_get_wf_details_csr;
378 x_wf_enabled_flag := 'Y';
379 x_wf_item_type := l_item_type;
380 x_wf_process := l_wf_process;
381 END IF;
382
383
384 EXCEPTION
385 WHEN OTHERS THEN
386 FND_MSG_PUB.add_exc_msg
387 ( p_pkg_name => l_pkg_name
388 , p_procedure_name => l_api_name );
389 FND_MSG_PUB.Count_And_Get
390 (p_count => l_msg_count ,
391 p_data => l_msg_data );
392 WF_CORE.CONTEXT('PA_PROJECT_STUS_UTILS','CHECK_WF_ENABLED');
393
394 -- 4537865 : RESET OUT params
395
396 x_wf_item_type := NULL ;
397 x_wf_process := NULL ;
398 x_wf_enabled_flag := 'N' ;
399 x_err_code := SQLCODE;
400 RAISE;
401
402 END Check_Wf_Enabled;
403
404 FUNCTION Is_Starting_Status (x_project_status_code IN VARCHAR2) RETURN VARCHAR2
405
406 -- This function checks whether a given project status is a starting
407 -- status for any project type
408 IS
409 CURSOR l_chk_starting_stus_csr IS
410 SELECT 'Y'
411 FROM pa_project_statuses
412 WHERE project_status_code = x_project_status_code
413 AND trunc(sysdate) BETWEEN start_date_active and
414 nvl(end_date_active,trunc(sysdate))
415 AND starting_status_flag='Y';
416
417 l_dummy VARCHAR2(1);
418
419 BEGIN
420 OPEN l_chk_starting_stus_csr;
421 FETCH l_chk_starting_stus_csr INTO l_dummy;
422 IF l_chk_starting_stus_csr%NOTFOUND THEN
423 l_dummy := 'N';
424 END IF;
425 CLOSE l_chk_starting_stus_csr;
426 RETURN NVL(l_dummy,'N');
427
428 END Is_Starting_Status;
429
430 FUNCTION Get_Default_Starting_Status (x_project_type IN VARCHAR2)
431 RETURN VARCHAR2 IS
432
433 CURSOR l_get_def_stus_csr IS
434 SELECT def_start_proj_status_code
435 FROM pa_project_types
436 WHERE project_type = x_project_type;
437
438 l_project_type VARCHAR2(30);
439
440 BEGIN
441
442 OPEN l_get_def_stus_csr;
443 FETCH l_get_def_stus_csr INTO l_project_type;
444 IF l_get_def_stus_csr%NOTFOUND THEN
445 l_project_type := NULL;
446 END IF;
447 CLOSE l_get_def_stus_csr;
448 RETURN l_project_type;
449
450 END Get_Default_Starting_Status;
451
452 -- STATUS MODEL addition --PROCEDURE Allow_Status_Deletion
453 -- This procedure checks the current status to see if it is used anywhere
454 -- The following procedure was moved from forms to server side
455 -- so as to make the form code generic for all the different status types
456 -- and let a server side procedure handles the specifics
457 -- The parameters are:
458 -- p_project_status_code : the status code as in
459 -- PA_PROJECT_STATUSES.project_status_code
460 -- p_status_type : the status type of the entiry as in
461 -- PA_PROJECT_STATUSES.status_type
462 -- x_err_code : an error code which indicates the results of the check
463 -- x_err_stage :
464 -- x_err_stack :
465 -- x_allow_deletion_flag : 'Y' for deletable and 'N' for non-deletable
466 --
467 PROCEDURE Allow_Status_Deletion(
468 p_project_status_code IN VARCHAR2
469 , p_status_type IN VARCHAR2
470 , x_err_code OUT NOCOPY NUMBER /* Added the nocopy check for Bug 4537865 */
471 , x_err_stage OUT NOCOPY VARCHAR2 /* Added the nocopy check for Bug 4537865 */
472 , x_err_stack OUT NOCOPY VARCHAR2 /* Added the nocopy check for Bug 4537865 */
473 , x_allow_deletion_flag OUT NOCOPY VARCHAR2) /* Added the nocopy check for Bug 4537865 */
474 IS
475 old_stack varchar2(630);
476 x_in_use_flag varchar2(1);
477 x_return_status varchar2(255);
478 x_error_message_code varchar2(255);
479
480 --Bug: 5635429
481 l_status_used_in_proj_type varchar2(1);
482
483 BEGIN
484 -- hr_utility.trace_on(null, 'RMDEL');
485 -- hr_utility.trace('Start Allow_Status_Deletion');
486 x_err_code := 0;
487 old_stack := x_err_stack;
488 x_err_stack := x_err_stack||'PA_PROJECT_STUS_UTILS.Allow_Status_Deletion';
489 x_err_stage := 'IS_DELETABLE';
490 x_allow_deletion_flag := 'N';
491
492 -- Added for lifecycle support
493 -- Calling procedure check_delete_phase_Ok to check for the constraints available for phase type status code before deleting
494
495 IF (p_status_type='PHASE') THEN
496 Begin
497 check_delete_phase_ok( p_project_status_code => p_project_status_code,
498 x_err_code =>x_err_code,
499 x_err_stage =>x_err_stage,
500 x_err_stack =>x_err_stack,
501 x_allow_deletion_flag =>x_allow_deletion_flag);
502 if (x_allow_deletion_flag='N') then
503 return;
504 end if;
505 exception when no_data_found then
506 null;
507 end;
508 END IF;
509
510
511 IF (p_status_type='PROJECT') THEN
512 -- First (keep it FIRST) check if it is a default starting status
513
514 --Bug 5635429. Replaced the select statement below with a function call
515 --so that the same can be used in multiple places. The function will be
516 --used in PAXSUDPS.fmb
517 l_status_used_in_proj_type :=
518 pa_project_stus_utils.is_status_used_in_proj_type(p_project_status_code => p_project_status_code);
519
520 IF l_status_used_in_proj_type = 'Y' THEN
521
522 x_err_stage := 'PA_STATUS_CHECK_PRJSTRT';
523 x_allow_deletion_flag := 'N';
524
525 END IF;
526 begin
527 /*Select 'PA_STATUS_CHECK_PRJSTRT','N'
528 Into x_err_stage,x_allow_deletion_flag
529 From dual
530 Where exists
531 (select '1'
532 from PA_PROJECT_TYPES_ALL T
533 where T.def_start_proj_status_code=p_project_status_code);*/
534 --Bug 5635429
535 if (x_allow_deletion_flag='N' AND
536 x_err_stage = 'PA_STATUS_CHECK_PRJSTRT') then
537 return;
538 end if;
539 exception when no_data_found then
540 null;
541 end;
542 -- Second check if it is a status in use in Projects
543 begin
544 Select 'PA_STATUS_CHECK_PRJUSED','N'
545 Into x_err_stage,x_allow_deletion_flag
546 From dual
547 Where exists
548 (select '1'
549 from PA_PROJECTS_ALL P
550 where P.project_status_code=p_project_status_code);
551 if (x_allow_deletion_flag='N') then
552 return;
553 end if;
554 exception when no_data_found then
555 null;
556 end;
557
558 -- Third check if it is a status in use in Projects Role Status security
559
560 -- hr_utility.trace('before check_status_is_in_use');
561 pa_role_status_menu_utils.check_status_is_in_use(
562 p_status_code => p_project_status_code
563 ,p_in_use_flag => x_in_use_flag
564 ,p_return_status => x_return_status
565 ,p_error_message_code => x_error_message_code);
566
567 -- hr_utility.trace('after check_status_is_in_use');
568 -- hr_utility.trace('x_in_use_flag is :' || x_in_use_flag);
569 IF x_in_use_flag = 'Y' THEN
570 x_allow_deletion_flag := 'N';
571 x_err_stage := x_error_message_code;
572 return;
573 ELSE
574 x_allow_deletion_flag := 'Y';
575 END IF;
576
577 ELSE -- if a PRM related status_type
578 PA_ASSIGNMENT_UTILS.check_status_is_in_use(
579 p_project_status_code
580 , x_in_use_flag
581 , x_return_status
582 ,x_error_message_code
583 );
584 if x_in_use_flag='Y' then
585 x_allow_deletion_flag:='N';
586 x_err_stage:=x_error_message_code;
587 else
588 x_allow_deletion_flag:='Y';
589 end if;
590 END IF;
591
592 -- Third check if it is a pre-defined status
593 begin
594 Select 'PA_STATUS_CHECK_PRE_DEF','N'
595 Into x_err_stage,x_allow_deletion_flag
596 From dual
597 Where exists
598 (select '1'
599 from PA_PROJECT_STATUSES S
600 where S.project_status_code=p_project_status_code
601 and S.predefined_flag='Y');
602 if (x_allow_deletion_flag='N') then
603 return;
604 end if;
605 exception when no_data_found then
606 null;
607 end;
608
609 -- Fourth check if it is a next allowable status
610 begin
611 Select 'PA_STATUS_CHECK_NEXT','N'
612 Into x_err_stage,x_allow_deletion_flag
613 From dual
614 Where exists
615 (select '1'
616 from PA_NEXT_ALLOW_STATUSES N
617 where N.next_allowable_status_code=p_project_status_code);
618 if (x_allow_deletion_flag='N') then
619 return;
620 end if;
621 exception when no_data_found then
622 null;
623 end;
624
625 -- Fifth check if it is a workflow status
626 begin
627 Select 'PA_STATUS_CHECK_WF_USED','N'
628 Into x_err_stage,x_allow_deletion_flag
629 From dual
630 Where exists
631 (select '1'
632 from PA_PROJECT_STATUSES S
633 where S.wf_success_status_code=p_project_status_code
634 OR S.wf_failure_status_code=p_project_status_code);
635 if (x_allow_deletion_flag='N') then
636 return;
637 end if;
638 exception when no_data_found then
639 null;
640 end;
641
642 -- Sixth check if referenced in progress tables. Added in FP.K
643 if(pa_progress_utils.check_status_referenced(p_project_status_code) ) then
644 x_allow_deletion_flag := 'N';
645 x_err_stage := 'PA_STATUS_CHECK_TSKUSED';
646 return;
647 end if;
648
649 x_allow_deletion_flag := 'Y';
650 x_err_stack := old_stack;
651
652 EXCEPTION
653 WHEN OTHERS THEN
654 x_err_code := SQLCODE;
655 x_err_stack := x_err_stack||' ->in exception of ALLOW_STATUS_DELETION';
656 x_allow_deletion_flag := 'N' ; -- 4537865
657 End Allow_Status_Deletion;
658
659
660
661 -- STATUS MODEL addition
662 -- The following function was added to check if changing of status
663 -- for the given two statuses is allowed. This function could be used
664 -- by any status type
665 FUNCTION Allow_Status_Change (o_status_code IN VARCHAR2
666 ,n_status_code IN VARCHAR2)
667 RETURN VARCHAR2 IS
668
669 CURSOR c_allow_status_change IS
670 SELECT STATUS_CODE, NEXT_ALLOWABLE_STATUS_CODE
671 FROM pa_next_allow_statuses
672 WHERE STATUS_CODE=o_status_code
673 -- AND NEXT_ALLOWABLE_STATUS_CODE=n_status_code
674 ;
675 c_rec c_allow_status_change%ROWTYPE;
676
677 old_status_code VARCHAR2(30);
678 new_status_code VARCHAR2(30);
679 v_change_allowed VARCHAR2(1):='N';
680 v_next_allowable_status_flag VARCHAR2(1);
681
682 BEGIN
683 Select next_allowable_status_flag
684 into v_next_allowable_status_flag
685 from pa_project_statuses
686 where project_status_code = o_status_code;
687
688 IF (v_next_allowable_status_flag='A') THEN
689 v_change_allowed:='Y';
690 RETURN v_change_allowed;
691 ELSIF (v_next_allowable_status_flag='N') THEN
692 v_change_allowed:='N';
693 RETURN v_change_allowed;
694 ELSIF (v_next_allowable_status_flag='U') THEN
695 FOR c_rec in c_allow_status_change LOOP
696 IF (c_rec.NEXT_ALLOWABLE_STATUS_CODE = n_status_code) THEN
697 v_change_allowed := 'Y';
698 RETURN v_change_allowed;
699 ELSE
700 v_change_allowed := 'N';
701 END IF;
702 END LOOP;
703 RETURN v_change_allowed;
704 ELSIF (v_next_allowable_status_flag='S') THEN
705 FOR c_rec in c_allow_status_change LOOP
706 begin
707 Select 'Y'
708 Into v_change_allowed
709 /*
710 From PA_PROJECT_STATUSES
711 Where PROJECT_SYSTEM_STATUS_CODE=c_rec.NEXT_ALLOWABLE_STATUS_CODE
712 and PROJECT_STATUS_CODE=n_status_code;
713 */
714 From dual
715 Where exists(
716 select 'x'
717 from PA_PROJECT_STATUSES
718 where PROJECT_SYSTEM_STATUS_CODE=c_rec.NEXT_ALLOWABLE_STATUS_CODE
719 and PROJECT_STATUS_CODE=n_status_code);
720 IF (v_change_allowed = 'Y') THEN
721 RETURN v_change_allowed;
722 END IF;
723 exception
724 when no_data_found then
725 null; -- to continue through the LOOP
726 end;
727 END LOOP;
728 RETURN v_change_allowed;
729 ELSE
730 v_change_allowed:='N';
731 RETURN v_change_allowed;
732 END IF;
733 EXCEPTION
734 WHEN NO_DATA_FOUND THEN
735 v_change_allowed:='N';
736 RETURN v_change_allowed;
737 END Allow_Status_Change;
738
739 -- STATUS MODEL addition
740 -- The following procedure was added to delete from PA_NEXT_ALLOW_STATUSES
741 -- This procedure deletes all the rows which are not for the
742 --- current next_allow_status_flag
743 PROCEDURE Delete_from_Next_Status (p_current_status_code IN VARCHAR2) IS
744
745 BEGIN
746
747 Delete from PA_NEXT_ALLOW_STATUSES N
748 where N.status_code = p_current_status_code
749 ;
750 END Delete_from_Next_Status;
751
752
753 -- STATUS MODEL addition
754 -- The following procedure was added to insert into PA_NEXT_ALLOW_STATUSES.
755 -- This procedure inserts the status_code and the next_allowable_status_code
756 -- passed.
757 PROCEDURE Insert_into_Next_Status(
758 p_current_status_code IN VARCHAR2
759 , p_next_status_code IN VARCHAR2) IS
760 cursor c_check1 IS
761 select status_code
762 from PA_NEXT_ALLOW_STATUSES
763 where STATUS_CODE = p_current_status_code
764 and NEXT_ALLOWABLE_STATUS_CODE = p_next_status_code;
765
766 v_check1 VARCHAR2(30);
767
768 BEGIN
769
770 OPEN c_check1;
771 FETCH c_check1 INTO v_check1;
772 IF c_check1%NOTFOUND THEN
773 CLOSE c_check1;
774 Insert into PA_NEXT_ALLOW_STATUSES(
775 STATUS_CODE
776 , NEXT_ALLOWABLE_STATUS_CODE
777 , LAST_UPDATE_DATE
778 , LAST_UPDATED_BY
779 , CREATION_DATE
780 , CREATED_BY
781 , LAST_UPDATE_LOGIN
782 )
783 Values(
784 p_current_status_code
785 , p_next_status_code
786 , sysdate
787 , 1
788 , sysdate
789 , 1
790 , 1
791 );
792 ELSE
793 CLOSE c_check1;
794 END IF;
795 END Insert_into_Next_Status;
796
797
798 -- STATUS MODEL addition
799 -- The following procedure was added for the PRM team to be able to
800 -- check either the name or code of a status given the other
801 PROCEDURE Check_Status_Name_or_Code(
802 p_status_code IN VARCHAR2
803 ,p_status_name IN VARCHAR2
804 ,p_status_type IN VARCHAR2
805 ,p_check_id_flag IN VARCHAR2
806 ,x_status_code OUT NOCOPY VARCHAR2 /* Added the nocopy check for Bug 4537865 */
807 ,x_return_status OUT NOCOPY VARCHAR2 /* Added the nocopy check for Bug 4537865 */
808 ,x_error_message_code OUT NOCOPY VARCHAR2) IS /* Added the nocopy check for Bug 4537865 */
809 BEGIN
810 IF p_status_code IS NOT NULL THEN
811 if p_check_id_flag = 'Y' then
812 SELECT project_status_code
813 INTO x_status_code
814 FROM pa_project_statuses
815 WHERE project_status_code = p_status_code
816 AND status_type = p_status_type
817 AND trunc(sysdate) between trunc(start_date_active) and trunc(nvl(end_date_active,sysdate));
818 else x_status_code := p_status_code;
819 end if;
820 ELSE
821 SELECT project_status_code
822 INTO x_status_code
823 FROM pa_project_statuses
824 WHERE project_status_name = p_status_name
825 AND status_type = p_status_type
826 AND trunc(sysdate) between trunc(start_date_active) and trunc(nvl(end_date_active,sysdate));
827 END IF;
828 x_return_status := FND_API.G_RET_STS_SUCCESS;
829
830 EXCEPTION
831 WHEN NO_DATA_FOUND THEN
832 x_return_status := FND_API.G_RET_STS_ERROR;
833 x_error_message_code := 'PA_STATUS_INVALID_AMBIGUOUS';
834 WHEN TOO_MANY_ROWS THEN
835 x_return_status := FND_API.G_RET_STS_ERROR;
836 x_error_message_code := 'PA_STATUS_INVALID_AMBIGUOUS';
837 WHEN OTHERS THEN
838 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
839 x_error_message_code := SQLERRM; -- 4537865
840 x_status_code := NULL ; -- 4537865
841 RAISE;
842 END Check_Status_Name_or_Code;
843
844
845 -- this procedure returns the wf success and failure statuses for a given
846 -- status. If the values are not specified, it returns the same
847 -- status
848 PROCEDURE get_wf_success_failure_status
849 (p_status_code IN VARCHAR2
850 ,p_status_type IN VARCHAR2
851 ,x_wf_success_status_code OUT NOCOPY VARCHAR2 /* Added the nocopy check for Bug 4537865 */
852 ,x_wf_failure_status_code OUT NOCOPY VARCHAR2 /* Added the nocopy check for Bug 4537865 */
853 ,x_return_status OUT NOCOPY VARCHAR2 /* Added the nocopy check for Bug 4537865 */
854 ,x_error_message_code OUT NOCOPY VARCHAR2) /* Added the nocopy check for Bug 4537865 */
855 IS
856 BEGIN
857 SELECT NVL(wf_success_status_code,project_status_code),
858 NVL(wf_failure_status_code,project_status_code)
859 INTO x_wf_success_status_code,x_wf_failure_status_code
860 FROM pa_project_statuses
861 WHERE project_status_code = p_status_code
862 AND status_type = p_status_type;
863
864 x_return_status := FND_API.G_RET_STS_SUCCESS;
865
866 EXCEPTION
867 WHEN NO_DATA_FOUND THEN
868 x_return_status := FND_API.G_RET_STS_ERROR;
869 x_error_message_code := 'PA_STATUS_INVALID_AMBIGUOUS';
870 -- 4537865
871 x_wf_success_status_code := NULL ;
872 x_wf_failure_status_code := NULL ;
873 WHEN TOO_MANY_ROWS THEN
874 x_return_status := FND_API.G_RET_STS_ERROR;
875 x_error_message_code := 'PA_STATUS_INVALID_AMBIGUOUS';
876 -- 4537865
877 x_wf_success_status_code := NULL ;
878 x_wf_failure_status_code := NULL ;
879 WHEN OTHERS THEN
880 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
881 -- 4537865
882 x_wf_success_status_code := NULL ;
883 x_wf_failure_status_code := NULL ;
884 x_error_message_code := SQLERRM;
885 RAISE;
886 END get_wf_success_failure_status;
887
888 -- Added for lifecycle support
889 -- This procedure will check for the constraints available for phase type status code before deleting
890 /*-----------------------------------------------------------+
891 | For Details/Comments Refer Package Specification Comments |
892 +-----------------------------------------------------------*/
893
894 Procedure check_delete_phase_ok(
895 p_project_status_code IN VARCHAR2
896 , x_err_code OUT NOCOPY NUMBER /* Added the nocopy check for Bug 4537865 */
897 , x_err_stage OUT NOCOPY VARCHAR2 /* Added the nocopy check for Bug 4537865 */
898 , x_err_stack OUT NOCOPY VARCHAR2 /* Added the nocopy check for Bug 4537865 */
899 , x_allow_deletion_flag OUT NOCOPY VARCHAR2) /* Added the nocopy check for Bug 4537865 */
900 IS
901 BEGIN
902 -- checking whether the phase to be deleted has been used by a lifecycle
903 Select 'PA_PHASE_LIFECYCLE_USED','N'
904 Into x_err_stage,x_allow_deletion_flag
905 From dual
906 Where exists(Select 'XYZ' from pa_proj_elements
907 where project_id= 0 and object_type = 'PA_TASKS'
908 and phase_code= p_project_status_code);
909 -- check for PLM needs to be added
910 -- 4537865
911 EXCEPTION
912 WHEN OTHERS THEN
913 x_err_code := SQLCODE ;
914 x_err_stage := 'PA_PHASE_LIFECYCLE_USED' ;
915 x_err_stack := SUBSTRB(SQLERRM ,1,100);
916 x_allow_deletion_flag := 'N' ;
917
918 -- NO RAISE as per the usage of this API
919 End check_delete_phase_ok;
920
921 --start Bug 3059344
922 FUNCTION Is_Project_Closed(x_project_id IN NUMBER ) return VARCHAR2 IS
923 l_Found BOOLEAN := FALSE;
924 l_proj_status_code pa_projects_all.project_status_code%type;
925 X_PROJ_CLOSED VARCHAR2(1);
926 Begin
927 -- Check if there are any records in the pl/sql table.
928 If G_ProjID_Tab.COUNT > 0 Then
929 --Dbms_Output.Put_Line('count > 0');
930
931 Begin
932 X_PROJ_CLOSED := G_ProjID_Tab(x_project_id);
933 l_Found := TRUE;
934 --Dbms_Output.Put_Line('l_found TRUE');
935 Exception
936 When No_Data_Found Then
937 l_Found := FALSE;
938 When Others Then
939 Raise;
940 End;
941
942 End If;
943
944 If Not l_Found Then
945 --Dbms_Output.Put_Line('l_found FALSE');
946
947 If G_ProjID_Tab.COUNT > 999 Then
948 --Dbms_Output.Put_Line('count > 199');
949 G_ProjID_Tab.Delete;
950 End If;
951
952 Begin
953 --Dbms_Output.Put_Line('select');
954 SELECT project_status_code
955 into l_proj_status_code
956 FROM pa_projects_all
957 WHERE project_id = x_project_id;
958
959 X_PROJ_CLOSED := Is_Project_Status_Closed (l_proj_status_code);
960
961 G_ProjID_Tab(x_project_ID) := X_PROJ_CLOSED;
962 --Dbms_Output.Put_Line('after select');
963 Exception
964 When No_Data_Found Then
965 --Dbms_Output.Put_Line('wndf ');
966 X_PROJ_CLOSED := 'N';
967 G_ProjID_Tab(x_project_id) := 'N';
968 End;
969
970 End If;
971
972 Return X_PROJ_CLOSED;
973
974 EXCEPTION
975 WHEN OTHERS THEN
976 RETURN 'N';
977 END Is_Project_Closed;
978
979 -- STATUS MODEL changes - the following function is for status_type PROJECT
980 -- only since it calls pa_utils2.IsProjectClosed which compares Project
981 -- related system statuses only. The filer for status_type has been added
982 -- only to make it apparent that this function is for PROJECT only.
983 FUNCTION Is_Project_Status_Closed (x_project_status_code IN VARCHAR2 ) return VARCHAR2 IS
984
985 l_system_status_code pa_projects_all.project_status_code%type;
986 Begin
987
988 If x_project_status_code = g_project_status_code Then
989 RETURN g_proj_sts_closed;
990 Else
991 Begin
992 --Dbms_Output.Put_Line('select');
993 SELECT project_system_status_code
994 INTO l_system_status_code
995 FROM pa_project_statuses
996 WHERE project_status_code = x_project_status_code
997 and status_type = 'PROJECT';
998
999 g_project_status_code := x_project_status_code;
1000 G_PROJ_STS_CLOSED := pa_utils2.IsProjectClosed(l_system_status_code);
1001
1002 --Dbms_Output.Put_Line('after select');
1003 Exception
1004 When No_Data_Found Then
1005 --Dbms_Output.Put_Line('wndf ');
1006 g_project_status_code := x_project_status_code;
1007 G_PROJ_STS_CLOSED := 'N';
1008 End;
1009
1010 End If;
1011
1012 Return G_PROJ_STS_CLOSED;
1013
1014 EXCEPTION
1015 WHEN OTHERS THEN
1016 RETURN 'N';
1017
1018 END Is_Project_Status_Closed;
1019 --End Bug 3059344
1020
1021
1022 --Bug 5635429. This function will return Y if the project status is being
1023 --used in project types setup. N will be returned otherwise
1024 FUNCTION is_status_used_in_proj_type(p_project_status_code IN VARCHAR2)
1025 RETURN VARCHAR2
1026 AS
1027 l_dummy VARCHAR2(1);
1028 BEGIN
1029
1030 SELECT 'Y'
1031 INTO l_dummy
1032 FROM dual
1033 WHERE EXISTS
1034 (SELECT '1'
1035 FROM pa_project_types_all t
1036 WHERE t.def_start_proj_status_code=p_project_status_code
1037 AND project_type <> 'AWARD_PROJECT' /* Bug 5718627 */
1038 );
1039
1040 RETURN 'Y';
1041 EXCEPTION WHEN NO_DATA_FOUND THEN
1042
1043 RETURN 'N';
1044 END;
1045
1046
1047 END PA_PROJECT_STUS_UTILS;
1048
1049
[Home] [Help]