1 PACKAGE BODY PA_CONTROL_ITEMS_UTILS AS
2 --$Header: PACICIUB.pls 120.12.12010000.2 2008/08/22 16:08:05 mumohan ship $
3 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
4
5 G_user_id NUMBER:=-999;
6 G_party_id NUMBER:=-999;
7 G_party_name VARCHAR2(360):= NULL ;
8
9 G_status_type VARCHAR2(100) := NULL;
10 G_status_code VARCHAR2(100) := NULL;
11 G_action_code VARCHAR2(100) := NULL;
12 G_action_allowed VARCHAR2(1) := NULL;
13
14 function GET_OBJECT_NAME(
15 p_project_id IN NUMBER
16 ,p_object_id IN NUMBER := NULL
17 ,p_object_type IN VARCHAR2 := NULL
18 ) RETURN VARCHAR2
19
20 IS
21
22 l_rowid ROWID;
23 l_object_name VARCHAR2(250) := NULL;
24
25 -- cursor c_task_name is select 'Wait for fix from Sakthi' from dual;
26
27 -- cursor c_task_name is select element_name from PA_LATEST_PUB_STRUC_TASKS_V
28 cursor c_task_name is select element_name from PA_FIN_LATEST_PUB_TASKS_V
29 where proj_element_id = p_object_id
30 and project_id = p_project_id;
31 --and object_type = p_object_type;
32
33 BEGIN
34 if p_object_type is NULL or p_object_type <> 'PA_TASKS' then
35 return NULL;
36 end if;
37 OPEN c_task_name;
38 FETCH c_task_name INTO l_object_name;
39 close c_task_name;
40 RETURN l_object_name;
41
42 EXCEPTION
43
44 WHEN NO_DATA_FOUND THEN
45 RETURN NULL ;
46 WHEN OTHERS THEN
47 RETURN NULL;
48
49 end GET_OBJECT_NAME;
50
51
52 function GET_INITIAL_CI_STATUS(
53 p_ci_type_id IN NUMBER := NULL
54 ) RETURN VARCHAR2
55 IS
56 BEGIN
57 return 'CI_WORKING';
58
59 end GET_INITIAL_CI_STATUS;
60
61 -- has been replaced by get_party_id in PA_UTILS
62 -- Function get_party_id (
63 -- p_resource_id in number,
64 -- p_resource_type_id in number )
65 -- return number
66 -- IS
67 -- Cursor external is
68 -- select customer_id from fnd_user
69 --
70 -- where user_id = p_resource_id;
71
72 -- Cursor internal is
73 -- select h.party_id
74 -- from hz_parties h
75 -- where h.orig_system_reference = CONCAT('PER:',p_resource_id);
76
77 -- l_party_id number;
78
79 -- Begin
80 -- if(p_resource_type_id = 101) then
81 -- Open internal;
82 -- fetch internal into l_party_id;
83 -- close internal;
84 -- end if;
85 -- if(p_resource_type_id = 112) then
86 -- Open external;
87 -- fetch external into l_party_id;
88 -- close external;
89 -- end if;
90 -- return l_party_id;
91 -- End get_party_id;
92
93 Function IsImpactOkToInclude(p_ci_type_id_1 IN NUMBER,
94 p_ci_type_id_2 IN NUMBER,
95 p_ci_id_2 IN NUMBER) return VARCHAR2
96 IS
97
98 p_ci_type_id number;
99 p_ci_id number;
100
101 cursor ci_type_impact is
102 select impact_Type_code
103 from pa_ci_impact_type_usage
104 where ci_type_id = p_ci_type_id;
105
106 cursor ci_impact is
107 select impact_type_code
108 from pa_ci_impacts
109 where ci_id = p_ci_id;
110
111 citypeimpact ci_type_impact%rowtype;
112 ciimpact ci_impact%rowtype;
113 impact_1 varchar2(1):= 'Y';
114 temp varchar2(1);
115
116 Begin
117 p_ci_type_id := p_ci_type_id_1;
118 open ci_type_impact;
119 fetch ci_type_impact into citypeimpact;
120 if (ci_type_impact%notfound) then
121 impact_1 := 'N';
122 else
123 impact_1 := 'Y';
124 end if;
125 close ci_type_impact;
126
127 if (p_ci_type_id_2 is not null) then
128 p_ci_type_id := p_ci_type_id_2;
129 open ci_type_impact;
130 fetch ci_type_impact into citypeimpact;
131 if (ci_type_impact%notfound) then
132 return 'Y';
133 elsif (ci_type_impact%found and impact_1 = 'N') then
134 return 'N';
135 elsif (ci_type_impact%found and impact_1 = 'Y') then
136 begin
137 select 'Y'
138 into temp
139 from pa_ci_impact_type_usage
140 where ci_type_id = p_ci_type_id_2
141 and impact_type_code <> 'FINPLAN' /* Bug# 3724520 */
142 and impact_Type_code not in (select impact_Type_code
143 from pa_ci_impact_type_usage
144 where ci_type_id = p_ci_type_id_1
145 and impact_type_code <> 'FINPLAN'); /* Bug# 3724520 */
146 return 'N';
147 exception when no_data_found then
148 return 'Y';
149 when others then
150 return 'N';
151 end;
152 end if;
153 close ci_type_impact;
154 elsif (p_ci_id_2 is not null) then
155 p_ci_id := p_ci_id_2;
156 open ci_impact;
157 fetch ci_impact into ciimpact;
158 if (ci_impact%notfound) then
159 return 'Y';
160 elsif (ci_impact%found and impact_1 = 'N') then
161 return 'N';
162 elsif (ci_impact%found and impact_1 = 'Y') then
163 begin
164 select 'Y'
165 into temp
166 from pa_ci_impacts
167 where ci_id = p_ci_id_2
168 and impact_type_code <> 'FINPLAN' /* Bug# 3724520 */
169 and impact_Type_code not in (select impact_Type_code
170 from pa_ci_impact_type_usage
171 where ci_type_id = p_ci_type_id_1
172 and impact_type_code <> 'FINPLAN'); /* Bug# 3724520 */
173 return 'N';
174 exception when no_data_found then
175 return 'Y';
176 when others then
177 return 'N';
178 end;
179 end if;
180 close ci_impact;
181 end if;
182 return 'N';
183 End IsImpactOkToInclude;
184
185 Function CheckCIActionAllowed(p_status_type IN VARCHAR2 default null,
186 p_status_code IN VARCHAR2 default null,
187 p_action_code IN VARCHAR2 default null,
188 p_ci_id IN NUMBER default null) return VARCHAR2
189 IS
190 l_status_type VARCHAR2(30);
191 l_status_code VARCHAR2(30);
192
193
194 Cursor C_USER_CONTROL is
195 Select enabled_flag
196 from pa_project_status_controls
197 where status_type = l_status_type
198 and project_status_code = l_status_code
199 and action_code = p_action_code;
200
201 Cursor C_SYS_CONTROL is
202 Select enabled_flag
203 from pa_project_status_controls sc
204 ,pa_project_statuses ps
205 where ps.project_status_code = l_status_code
206 and ps.project_system_status_code = sc.project_system_status_code
207 and sc.status_type = l_status_type
208 and sc.action_code = p_action_code;
209
210 CURSOR get_current_status
211 IS
212 SELECT status_code
213 FROM pa_control_items
214 WHERE ci_id = p_ci_id;
215
216
217 BEGIN
218
219 l_status_type := p_status_type;
220 l_status_code := p_status_code;
221
222 IF p_ci_id IS NOT NULL THEN
223 -- get the current status
224 l_status_type := 'CONTROL_ITEM';
225 OPEN get_current_status ;
226 FETCH get_current_status INTO l_status_code;
227 IF get_current_status%notfound THEN
228 RETURN 'N';
229 END IF;
230
231 CLOSE get_current_status;
232
233 END IF;
234
235 IF G_status_code = l_status_code AND
236 G_status_type = l_status_type AND
237 G_action_code = p_action_code THEN
238 RETURN G_action_allowed;
239 END IF;
240
241 open C_USER_CONTROL;
242 fetch C_USER_CONTROL into G_action_allowed;
243 if (C_USER_CONTROL%NOTFOUND) then
244 open C_SYS_CONTROL;
245 fetch C_SYS_CONTROL into G_action_allowed;
246 close C_SYS_CONTROL;
247 end if;
248 close C_USER_CONTROL;
249
250 G_status_code := l_status_code;
251 G_status_type := l_status_type;
252 G_action_code := p_action_code;
253
254 return G_action_allowed;
255 Exception
256 when others then
257 return 'N';
258 End CheckCIActionAllowed;
259
260 Function CheckValidNextCIStatus( p_ci_id in Number
261 ,p_next_status in varchar2)
262 return Boolean
263 is
264 Cursor C_CI is
265 select
266 ci_id
267 ,ci.ci_type_id
268 ,status_code
269 ,ps.project_system_status_code system_status_code
270 ,cit.ci_type_class_code ci_type_class
271 ,cit.approval_required_flag approval_required_flag
272 from pa_control_items ci
273 ,pa_ci_types_b cit
274 ,pa_project_statuses ps
275 where ci.ci_id = p_ci_id
276 and ci.ci_type_id = cit.ci_type_id
277 and ci.status_code = ps.project_status_code;
278
279 CI_REC C_CI%ROWTYPE;
280 Cursor C_NEXT_SYS_STAT is
281 Select project_system_status_code
282 from pa_project_statuses
283 where project_status_code = p_next_status;
284
285 l_return_value boolean := false;
286 l_curr_sys_status PA_PROJECT_STATUSES.PROJECT_SYSTEM_STATUS_CODE%TYPE;
287 l_next_sys_status PA_PROJECT_STATUSES.PROJECT_SYSTEM_STATUS_CODE%TYPE;
288
289 Begin
290 open C_CI;
291 fetch C_CI into CI_REC;
292 close C_CI;
293
294 open C_NEXT_SYS_STAT;
295 fetch C_NEXT_SYS_STAT into l_next_sys_status;
296 close C_NEXT_SYS_STAT;
297
298 l_return_value := CheckValidNextCISysStatus
299 ( p_curr_sys_status => ci_rec.system_status_code
300 ,p_next_sys_status => l_next_sys_status
301 ,p_ci_type_class => ci_rec.ci_type_class
302 ,p_approval_req_flag => ci_rec.approval_required_flag );
303
304
305 return l_return_value;
306
307 End CheckValidNextCIStatus;
308
309 Function CheckValidNextCISysStatus( p_curr_sys_status in varchar2
310 ,p_next_sys_status in varchar2
311 ,p_ci_type_class in varchar2
312 ,p_approval_req_flag in varchar2)
313 return Boolean
314 is
315 l_return_value boolean := false;
316
317 BEGIN
318
319
320 IF (p_curr_sys_status = p_next_sys_status) THEN
321 l_return_value := TRUE;
322 elsIf (p_curr_sys_status = 'CI_DRAFT') then
323 if (p_next_sys_status = 'CI_WORKING') then
324 l_return_value := true;
325 end if;
326 elsif (p_curr_sys_status = 'CI_WORKING') then
327 if (p_next_sys_status = 'CI_SUBMITTED') then
328 if( (p_ci_type_class in ('CHANGE_ORDER','CHANGE_REQUEST'))
329 OR (p_ci_type_class = 'ISSUE' AND p_approval_req_flag = 'Y')
330 ) then
331 l_return_value := true;
332 end if;
333 elsif (p_next_sys_status in ('CI_CANCELED','CI_CLOSED')) then
334 l_return_value := true;
335 end if;
336 elsif (p_curr_sys_status = 'CI_SUBMITTED') then
337 if (p_next_sys_status in ('CI_APPROVED','CI_REJECTED','CI_WORKING')) then
338 l_return_value := true;
339 end if;
340 elsif (p_curr_sys_status = 'CI_APPROVED') then
341 if (p_next_sys_status in ('CI_CLOSED','CI_CANCELED','CI_WORKING')) then
342 /* Is it different for Included CRs? */
343 l_return_value := true;
344 end if;
345 elsif (p_curr_sys_status = 'CI_REJECTED') then
346 if (p_next_sys_status in ('CI_CANCELED','CI_WORKING')) then
347 l_return_value := true;
348 end if;
349 elsif (p_curr_sys_status = 'CI_CLOSED') then
350 if ( (p_next_sys_status = 'CI_APPROVED') AND
351 (p_ci_type_class in ('CHANGE_ORDER','CHANGE_REQUEST'))
352 ) then
353 l_return_value := true;
354 ELSIF ( (p_next_sys_status = 'CI_WORKING') AND
355 (p_ci_type_class in ('ISSUE'))
356 ) then
357 l_return_value := true;
358 end if;
359
360 end if;
361
362
363
364 return l_return_value;
365 End CheckValidNextCISysStatus;
366
367 /*----------------------------------------------------------------------------
368 Function to return status of control Item
369 -----------------------------------------------------------------------------*/
370 FUNCTION getCIStatus ( p_CI_id IN NUMBER)
371 return VARCHAR2
372 is
373 l_ci_status varchar2(30) := NULL ;
374
375 BEGIN
376 if p_ci_id is NULL then
377 return NULL;
378 end if;
379
380 IF g_CI_id is NULL or g_CI_id <> p_ci_id then
381 select status_code
382 into l_ci_status
383 from pa_control_items
384 where ci_id = p_ci_id ;
385 g_ci_status := l_ci_status ;
386 END IF ;
387
388 return g_ci_status ;
389
390 EXCEPTION
391 WHEN NO_DATA_FOUND THEN
392 RETURN NULL ;
393 WHEN OTHERS THEN RAISE ;
394
395 END getCIStatus ;
396
397 /*-----------------------------------------------------------------------------
398 Function to check whether CI type has any impact
399 -----------------------------------------------------------------------------*/
400 FUNCTION isCITypehasimpact ( p_ci_id IN NUMBER)
401 return VARCHAR2
402 is
403 l_ci_type_has_impact varchar2(1) := 'N';
404 BEGIN
405 IF p_ci_id is null then
406 return NULL;
407 END IF;
408
409 IF g_ci_id is null or g_CI_id <> p_ci_id then
410 select 'Y'
411 into l_ci_type_has_impact
412 from dual
413 where exists ( select '1'
414 from pa_ci_impact_type_usage CIIU
415 ,pa_control_items CI
416 where CIIU.ci_type_id= CI.ci_type_id
417 and CI.CI_id = p_ci_id ) ;
418 g_ci_type_has_impact := l_ci_type_has_impact ;
419 END IF ;
420
421
422 return g_ci_type_has_impact ;
423 Exception
424 WHEN NO_DATA_FOUND THEN
425 RETURN NULL ;
426 When OTHERS THEN
427 RAISE ;
428 END isCITypehasimpact ;
429
430 /*-----------------------------------------------------------------------------
431 Function to check whether a CI type has any impact
432 This one checks impact flag for a given TYPE ID (not ci_id)
433 -----------------------------------------------------------------------------*/
434 FUNCTION TypeHasImpact ( p_ci_type_id IN NUMBER)
435 return VARCHAR2
436 is
437 l_type_has_impact varchar2(1) := 'N';
438 BEGIN
439 IF p_ci_type_id is null then
440 return NULL;
441 END IF;
442
443 IF g_ci_type_id is null or g_ci_type_id <> p_ci_type_id then
444 select 'Y'
445 into l_type_has_impact
446 from dual
447 where exists ( select '1'
448 from pa_ci_impact_type_usage CIIU
449 where CIIU.ci_type_id = p_ci_type_id
450 ) ;
451 g_type_has_impact := l_type_has_impact ;
452 END IF ;
453 return g_type_has_impact ;
454
455 EXCEPTION
456 WHEN NO_DATA_FOUND THEN
457 RETURN NULL ;
458 When OTHERS THEN
459 RAISE ;
460 END TypeHasImpact ;
461
462 /*-------------------------------------------------------------------------------------
463 !!! NOT USED, replaced with CheckNextPageValid
464 Function Name: CheckValidNextPage
465 Usage: Used with with lookup type to determine valid list of next pages to navigate
466 Rules: 1. If CI id is null it is the create page. Allow all Next page
467 2. Exclude the current page from the next page list.
468 ---------------------------------------------------------------------------------------*/
469 /*
470 FUNCTION CheckValidNextPage( p_ci_id IN NUMBER
471 ,p_type_id IN VARCHAR2
472 ,p_status_control IN VARCHAR2
473 ,p_page_code IN VARCHAR2
474 ,p_currpage_code IN VARCHAR2
475 ,p_action_list IN VARCHAR2 := 'N')
476
477 return VARCHAR2
478 is
479
480
481 l_ci_status varchar2(30):= NULL ;
482 l_ci_type_has_impact varchar2(1) := 'N';
483 l_check_update_access varchar2(1) := 'F';
484 l_stat_change varchar2(1) := 'F';
485
486 BEGIN
487 IF p_page_code is NULL or p_currpage_code is NULL then
488 return 'N' ;
489 END IF ;
490
491 IF p_page_code = p_currpage_code then
492 return 'N' ;
493 END IF ;
494
495 -- In Review page, don't show "Review and Submit" or "Review"
496 IF substr(p_currpage_code,0,6) = 'REVIEW' then
497 if substr(p_page_code,0,6) = 'REVIEW'then
498 return 'N' ;
499 end if;
500 END IF ;
501
502 -- Always show "Return to List" OR "Return to Actions List" - but never both
503 if p_page_code = 'RETURN_TO_ACT_LIST' then
504 return p_action_list;
505 end if;
506
507 if p_page_code = 'RETURN_TO_LIST' then
508 if p_action_list = 'Y' then
509 return 'N';
510 else
511 return 'Y';
512 end if;
513 end if;
514
515
516 -- "ADD ANOTHER" only in CI Create and Action Create page
517 IF p_page_code = 'ADD_ANOTHER' then
518 if p_currpage_code = 'CREATE_ACTION'
519 OR p_currpage_code = 'CREATE_PAGE' THEN
520 return 'Y';
521 else
522 return 'N';
523 end if;
524 END IF;
525
526 -- "Stay in this page" only in Update pages
527 IF p_page_code = 'SAME_PAGE' then
528 if (p_currpage_code = 'CI_DETAILS'
529 OR p_currpage_code = 'UPDATE_STATUS_OVERVIEW'
530 OR p_currpage_code = 'IMPACT_DETAILS'
531 OR p_currpage_code = 'UPDATE_RESOLUTION' ) THEN
532 return 'Y';
533 else
534 return 'N';
535 end if;
536 END IF;
537
538 --return 'Y';
539
540 IF p_ci_id is NULL then -- Create page.
541 if p_page_code = 'REVIEW_CI' then -- show "Review and Submit" in Create page
542 return 'N';
543 end if;
544 END IF ;
545
546
547 IF p_ci_id is NOT NULL
548 AND p_page_code = 'CREATE_ACTION' THEN
549 l_check_update_access := nvl(pa_ci_security_pkg.check_create_action(p_ci_id),'F');
550 if l_check_update_access <> 'T' then
551 return 'N';
552 end if;
553 END IF;
554
555
556 --l_check_view_access := pa_ci_security_pkg.check_view_access(p_ci_id);
557
558 -- Following pages require UPDATE access
559 -- if p_page_code = 'ADD_COMMENT'
560 if p_ci_id is NOT NULL
561 AND ( p_page_code = 'CI_DETAILS'
562 ---OR p_page_code = 'CREATE_ACTION'
563 OR p_page_code = 'INCLUDE_CHANGE_REQUEST'
564 OR p_page_code = 'UPDATE_RESOLUTION'
565 OR p_page_code = 'UPDATE_STATUS_OVERVIEW'
566 OR p_page_code = 'RELATED_ITEM'
567 OR p_page_code = 'ADD_ATTACHMENTS')
568 THEN
569 l_check_update_access := nvl(pa_ci_security_pkg.check_update_access(p_ci_id),'F');
570
571 if l_check_update_access <> 'T' then
572 return 'N';
573 end if;
574 end if;
575
576 -- Only show either "Review" or "Review and Submit" selection (never both)
577 -- based on status and security
578 if p_ci_id is NOT NULL
579 AND ( p_page_code = 'REVIEW_AND_SUB_CI' OR p_page_code = 'REVIEW_CI') then
580 if submitAllowed (NULL,NULL,NULL,getCISystemStatus(p_ci_id)) = 'Y' then
581 l_stat_change := nvl(pa_ci_security_pkg.check_change_status_access(p_ci_id),'F');
582
583 if l_stat_change = 'T' then
584 if p_page_code = 'REVIEW_CI' then
585 return 'N'; -- don't show "Review", allowed to change status
586 end if;
587 else
588 if p_page_code = 'REVIEW_AND_SUB_CI' then
589 return 'N'; --don't show "Review and Submit", user not allowed to change status
590 end if;
591 end if;
592 else
593 if p_page_code = 'REVIEW_AND_SUB_CI' then
594 return 'N'; --don't show "Review and Submit", user not allowed to change status
595 end if;
596 end if;
597 end if;
598
599 -- this logic executed for ALL pages, including CREATE page
600 If ( p_page_code = 'IMPLEMENT_IMPACT'
601 OR p_page_code ='IMPACT_DETAILS') then
602 if (p_type_id is NULL) then
603 l_ci_type_has_impact := nvl(isCITypehasimpact(p_ci_id ),'N');
604 else
605 l_ci_type_has_impact := nvl(TypeHasImpact(p_type_id ),'N');
606 end if;
607
608 if l_ci_type_has_impact ='Y' then
609 if ( p_page_code = 'IMPLEMENT_IMPACT') then
610 if pa_ci_security_pkg.check_implement_impact_access(p_ci_id) <> 'Y' THEN
611 return 'N';
612 end if;
613 end if;
614 else
615 return 'N';
616 end if;
617 END IF ;
618
619 -- this logic NOT executed for CREATE page
620 if p_ci_id is NOT NULL
621 AND p_status_control is not null then
622 l_ci_status := getCIStatus(p_ci_id) ;
623 if pa_control_items_utils.CheckCIActionAllowed(
624 'CONTROL_ITEM',
625 l_ci_status ,
626 p_status_control ) <> 'Y' then
627 return 'N';
628 end if;
629 END IF;
630
631
632 RETURN 'Y' ;
633
634 EXCEPTION
635 WHEN OTHERS THEN
636 RAISE ;
637 END CheckValidNextPage ;*/
638
639 PROCEDURE checkandstartworkflow
640 (
641 p_api_version IN NUMBER := 1.0,
642 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
643 p_commit IN VARCHAR2 := FND_API.g_false,
644 p_validate_only IN VARCHAR2 := FND_API.g_true,
645 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
646
647 p_ci_id in NUMBER,
648 p_status_code IN VARCHAR2,
649
650 x_msg_count out NOCOPY NUMBER,
651 x_msg_data out NOCOPY VARCHAR2,
652 x_return_status OUT NOCOPY VARCHAR2
653 ) is
654
655 CURSOR get_wf_info
656 IS
657 SELECT workflow_item_type, workflow_process, enable_wf_flag
658 FROM pa_project_statuses
659 WHERE status_type = 'CONTROL_ITEM'
660 AND project_status_code = p_status_code;
661
662 l_wf_item_type VARCHAR2(30);
663 l_wf_process_name VARCHAR2(30);
664 l_wf_enable VARCHAR2(1);
665 l_item_key VARCHAR2(240);
666
667 BEGIN
668
669 -- Initialize the return status to success
670 x_return_status := FND_API.G_RET_STS_SUCCESS;
671
672 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
673 OPEN get_wf_info;
674 FETCH get_wf_info INTO l_wf_item_type, l_wf_process_name, l_wf_enable;
675
676 --debug_msg_s1 ('start workflow ' || p_status_code);
677 IF get_wf_info%found AND l_wf_enable = 'Y'
678 AND l_wf_item_type IS NOT NULL AND
679 l_wf_process_name IS NOT NULL THEN
680
681 --debug_msg_s1 ('start workflow ' || l_wf_item_type || ':' || l_wf_process_name);
682
683 pa_control_items_workflow.start_workflow
684 (
685 l_wf_item_type
686 , l_wf_process_name
687 , p_ci_id
688 , l_item_key
689 , x_msg_count
690 , x_msg_data
691 , x_return_status
692 );
693
694 --debug_msg_s1 ('after start workflow ' || l_wf_item_type || ':' || l_wf_process_name);
695
696 END IF;
697
698 CLOSE get_wf_info;
699
700 END IF;
701
702 END;
703
704
705 PROCEDURE CancelWorkflow
706 (
707 p_api_version IN NUMBER := 1.0,
708 p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
709 p_commit IN VARCHAR2 := FND_API.g_false,
710 p_validate_only IN VARCHAR2 := FND_API.g_true,
711 p_max_msg_count IN NUMBER := FND_API.g_miss_num,
712
713 p_ci_id in NUMBER,
714
715 x_msg_count out NOCOPY NUMBER,
716 x_msg_data out NOCOPY VARCHAR2,
717 x_return_status OUT NOCOPY VARCHAR2
718 ) is
719
720
721
722 l_wf_item_type VARCHAR2(30);
723 l_item_key VARCHAR2(240);
724 l_wf_status VARCHAR2(30);
725 l_project_id NUMBER;
726
727
728 CURSOR get_item_key IS
729 SELECT MAX(pwp.item_key), max(pwp.item_type)
730 from pa_wf_processes pwp, pa_project_statuses pps
731 where pwp.item_type = pps.WORKFLOW_ITEM_TYPE
732 and pps.status_type = 'CONTROL_ITEM'
733 and pps.project_status_code = 'CI_SUBMITTED'
734 AND entity_key2 = p_ci_id
735 AND pwp.wf_type_code = 'Control Item'
736 AND pwp.entity_key1 = l_project_id;
737
738
739 CURSOR get_wf_status IS
740 select 'Y' FROM dual
741 WHERE exists
742 (SELECT *
743 from wf_item_activity_statuses wias, pa_project_statuses pps
744 WHERE wias.item_type = pps.WORKFLOW_ITEM_TYPE
745 AND wias.item_key = l_item_key
746 AND wias.activity_status = 'ACTIVE'
747 AND pps.status_type = 'CONTROL_ITEM'
748 AND pps.project_status_code = 'CI_SUBMITTED');
749
750 CURSOR get_project_id
751 IS
752 SELECT project_id
753 FROM pa_control_items
754 WHERE ci_id = p_ci_id;
755
756 BEGIN
757
758 -- Initialize the return status to success
759 x_return_status := FND_API.G_RET_STS_SUCCESS;
760
761 OPEN get_project_id;
762 FETCH get_project_id INTO l_project_id;
763 CLOSE get_project_id;
764
765
766 IF (p_validate_only <>FND_API.g_true AND x_return_status = FND_API.g_ret_sts_success) then
767 OPEN get_item_key;
768 FETCH get_item_key INTO l_item_key, l_wf_item_type;
769
770 IF get_item_key%found THEN
771 OPEN get_wf_status;
772 FETCH get_wf_status INTO l_wf_status;
773
774 IF (get_wf_status%notfound or
775 l_wf_status <> 'Y' ) THEN
776 NULL;
777
778 else
779
780 --debug_msg_s1 ('b4 canceling workflow ' || x_return_status);
781 pa_control_items_workflow.cancel_workflow
782 (
783 l_wf_item_type
784 , l_item_key
785 , x_msg_count
786 , x_msg_data
787 , x_return_status
788 );
789
790
791
792 END IF;
793 close get_wf_status; /*Bug 3876221.Closing the cursor get_wf_status */
794
795 END IF;
796
797 CLOSE get_item_key;
798
799 --debug_msg_s1 ('after canceling workflow ' || x_return_status);
800
801 END IF;
802
803 END;
804
805 PROCEDURE ChangeCIStatus (
806 p_init_msg_list IN VARCHAR2 := fnd_api.g_true
807 ,p_commit IN VARCHAR2 := FND_API.g_false
808 ,p_validate_only IN VARCHAR2 := FND_API.g_true
809 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
810 ,p_ci_id in number
811 ,p_status in varchar2
812 ,p_comment in VARCHAR2 := null
813 ,p_enforce_security in Varchar2 DEFAULT 'Y'
814 ,p_record_version_number IN NUMBER
815 ,x_num_of_actions OUT NOCOPY NUMBER
816 ,x_return_status OUT NOCOPY VARCHAR2
817 ,x_msg_count OUT NOCOPY NUMBER
818 ,x_msg_data OUT NOCOPY VARCHAR2 )
819 IS
820 CURSOR get_ci_info
821 IS
822 SELECT pci.status_code, pci.project_id
823 FROM pa_control_items pci
824 WHERE ci_id = p_ci_id;
825
826 CURSOR get_status_name(l_code varchar2)
827 IS SELECT meaning
828 FROM pa_lookups
829 WHERE lookup_type = 'CONTROL_ITEM_SYSTEM_STATUS'
830 AND lookup_code = l_code;
831
832 CURSOR get_control_item_type
833 IS
834 SELECT pl.meaning,pl.lookup_code
835 FROM pa_lookups pl, pa_control_items pci, pa_ci_types_b pcit
836 WHERE
837 pl.lookup_type = 'PA_CI_TYPE_CLASSES'
838 and pci.ci_type_id = pcit.ci_type_id
839 and pl.lookup_code = pcit.ci_type_class_code
840 AND pci.ci_id = p_ci_id;
841
842 l_tp VARCHAR2(1);
843 l_project_id NUMBER;
844
845 CURSOR check_if_fin_impact_exists
846 is
847 SELECT 'Y' FROM dual
848 WHERE exists
849 (
850 SELECT * FROM pa_ci_impacts
851 WHERE ci_id = p_ci_id
852 AND impact_type_code = 'FINPLAN'
853 );
854
855 CURSOR c_submit_status(p_project_status_code varchar2) is
856 select 'Y', wf_success_status_code from pa_project_statuses
857 where project_status_code = p_project_status_code
858 and enable_wf_flag = 'Y'
859 and workflow_item_type is not null
860 and workflow_process is not null
861 and wf_success_status_code is not null
862 and wf_failure_status_code is not null;
863
864
865 l_curr_status VARCHAR2(30);
866 l_new_status VARCHAR2(30);
867 l_ret boolean;
868 l_msg_index_out NUMBER;
869 l_approval_required VARCHAR2(1);
870 l_type VARCHAR2(30);
871 l_temp VARCHAR2(1);
872 l_t1 VARCHAR2(80);
873 l_t2 VARCHAR2(80);
874 l_t3 VARCHAR2(200);
875 l_type_code VARCHAR2(80);
876 l_start_wf VARCHAR2(1) := 'Y';
877
878 l_curr_sys_status VARCHAR2(30);
879 l_next_sys_status VARCHAR2(30);
880 l_submit_status VARCHAR2(30);
881 l_submit_status_flag VARCHAR2(1);
882 l_resolution_req VARCHAR2(1);
883 l_resolution_req_cls VARCHAR2(1);
884
885
886
887
888 BEGIN
889
890 IF p_init_msg_list = FND_API.G_TRUE THEN
891 fnd_msg_pub.initialize;
892 END IF;
893
894 x_msg_count := 0;
895 x_msg_data := '';
896
897 -- Initialize the Error Stack
898 IF P_PA_DEBUG_MODE = 'Y' THEN
899 PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_UTILS.ChangeCIStatus');
900 END IF;
901
902 pa_debug.write_file('ChangeCiStatus: p_pa_debug_mode :'||p_pa_debug_mode);
903
904 -- Initialize the return status to success
905 x_return_status := FND_API.G_RET_STS_SUCCESS;
906
907 OPEN get_ci_info;
908 FETCH get_ci_info INTO l_curr_status, l_project_id;
909 CLOSE get_ci_info;
910
911 l_new_status := p_status;
912
913 ChangeCIStatusValidate (
914 p_init_msg_list => p_init_msg_list
915 ,p_commit => p_commit
916 ,p_validate_only => p_validate_only
917 ,p_max_msg_count => p_max_msg_count
918 ,p_ci_id => p_ci_id
919 ,p_status => p_status
920 ,p_enforce_security => p_enforce_security
921 ,x_resolution_req => l_resolution_req
922 ,x_resolution_req_cls => l_resolution_req_cls
923 ,x_start_wf => l_start_wf
924 ,x_new_status => l_new_status
925 ,x_num_of_actions => x_num_of_actions
926 ,x_return_status => x_return_status
927 ,x_msg_count => x_msg_count
928 ,x_msg_data => x_msg_data);
929
930
931 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
932 -- debug_msg_s1 ('6 ' || x_return_status);
933
934 IF P_PA_DEBUG_MODE = 'Y' THEN
935 pa_debug.write_file('ChangeCiStatus: before call to pa_control_items_pvt.UPDATE_CONTROL_ITEM_STATUS');
936 END IF;
937
938 pa_control_items_pvt.UPDATE_CONTROL_ITEM_STATUS (
939 1.0,
940 p_init_msg_list,
941 p_commit,
942 p_validate_only,
943 p_max_msg_count,
944 p_ci_id,
945 l_new_status,
946 p_record_version_number,
947 x_return_status,
948 x_msg_count,
949 x_msg_data
950 );
951 IF P_PA_DEBUG_MODE = 'Y' THEN
952 pa_debug.write_file('ChangeCiStatus: after call to pa_control_items_pvt.UPDATE_CONTROL_ITEM_STATUS');
953 END IF;
954
955
956 --Bug # 4618856 - if statement is added to check the return status
957 IF (x_return_status = 'S') THEN
958
959 /* Bug#3297238: call the insert table handlers of pa_obj_status_changes and pa_ci_comments here */
960 IF P_PA_DEBUG_MODE = 'Y' THEN
961 pa_debug.write_file('ChangeCiStatus: before call to ADD_STATUS_CHANGE_COMMENT');
962 END IF;
963
964 DBMS_LOCK.SLEEP(1); -- Bug 7022037
965
966 ADD_STATUS_CHANGE_COMMENT( p_object_type => 'PA_CI_TYPES'
967 ,p_object_id => p_ci_id
968 ,p_type_code => 'CHANGE_STATUS'
969 ,p_status_type => 'CONTROL_ITEM'
970 ,p_new_project_status => l_new_status
971 ,p_old_project_status => l_curr_status
972 ,p_comment => p_comment
973 ,x_return_status => x_return_status
974 ,x_msg_count => x_msg_count
975 ,x_msg_data => x_msg_data );
976
977 IF P_PA_DEBUG_MODE = 'Y' THEN
978 pa_debug.write_file('ChangeCiStatus: after call to ADD_STATUS_CHANGE_COMMENT');
979 END IF;
980 END IF;
981
982 END IF;
983
984
985 --debug_msg_s1 ('after change the status ' || x_return_status);
986
987 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
988
989 PostChangeCIStatus (
990 p_init_msg_list
991 ,p_commit
992 ,p_validate_only
993 ,p_max_msg_count
994 ,p_ci_id
995 ,l_curr_status
996 ,l_new_status
997 ,l_start_wf
998 ,p_enforce_security
999 ,x_num_of_actions
1000 ,x_return_status
1001 ,x_msg_count
1002 ,x_msg_data );
1003
1004
1005
1006 END IF;
1007
1008 -- Commit if the flag is set and there is no error
1009 IF (p_commit = FND_API.G_TRUE AND x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1010
1011 IF P_PA_DEBUG_MODE = 'Y' THEN
1012 pa_debug.write_file('ChangeCiStatus: before COMMIT');
1013 END IF;
1014
1015 COMMIT;
1016
1017 END IF;
1018
1019 x_msg_count := FND_MSG_PUB.Count_Msg;
1020
1021 IF x_msg_count = 1 THEN
1022 pa_interface_utils_pub.get_messages ( p_encoded => FND_API.G_TRUE
1023 ,p_msg_index => 1
1024 ,p_data => x_msg_data
1025 ,p_msg_index_out => l_msg_index_out
1026 );
1027 END IF;
1028
1029 Pa_Debug.Reset_Err_Stack;
1030
1031 EXCEPTION
1032 WHEN OTHERS THEN
1033
1034 IF P_PA_DEBUG_MODE = 'Y' THEN
1035 pa_debug.write_file('ChangeCiStatus: in when others exception');
1036 END IF;
1037
1038 ROLLBACK;
1039
1040 x_return_status := 'U';
1041 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_UTILS',
1042 p_procedure_name => 'ChangeCIStatus',
1043 p_error_text => SUBSTRB(SQLERRM,1,240));
1044
1045 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1046 p_data => x_msg_data);
1047
1048 END;
1049
1050
1051
1052 /*-------------------------------------------------------------------------------------
1053 This function returns hz_parties.party_name for fnd_user.user_id (IN parameter)
1054 -------------------------------------------------------------------------------------*/
1055 FUNCTION GetUserName( p_user_id in Number)
1056 return Varchar2
1057 is
1058 l_emp_id NUMBER;
1059 l_party_id NUMBER;
1060 BEGIN
1061 if p_user_id is NULL then
1062 return NULL;
1063 end if;
1064
1065 IF G_user_id=p_user_id AND
1066 G_party_name IS NOT NULL THEN
1067 RETURN G_party_name;
1068 END IF;
1069
1070
1071 /*Addition for bug 3729296 starts*/
1072
1073 -- SELECT employee_id, NVL(customer_id, supplier_id) Commented for Bug 4527617
1074 SELECT employee_id, NVL(person_party_id, supplier_id) -- For Bug 4527617
1075 INTO l_emp_id, l_party_id
1076 FROM fnd_user
1077 WHERE user_id = p_user_id;
1078
1079
1080 IF l_emp_id>0 THEN
1081 SELECT full_name
1082 INTO G_party_name
1083 FROM per_all_people_f
1084 WHERE person_id = l_emp_id
1085 and trunc(sysdate) between trunc(EFFECTIVE_START_DATE) and trunc(EFFECTIVE_END_DATE)
1086 and (CURRENT_EMPLOYEE_FLAG = 'Y' or CURRENT_NPW_FLAG = 'Y'); /* Added OR condition for bug 7132968 */
1087 ELSIF (l_party_id > 0) THEN
1088 SELECT party_name
1089 INTO G_party_name
1090 FROM hz_parties
1091 WHERE party_id = l_party_id;
1092 END IF;
1093 /*Addition for bug 3729296 ends*/
1094
1095 G_user_id:=p_user_id;
1096 G_party_id := -999; --Resetting G_party_id to -999 value for bug#5676456
1097 RETURN G_party_name;
1098 EXCEPTION
1099
1100 WHEN TOO_MANY_ROWS THEN
1101 RETURN NULL ;
1102 WHEN NO_DATA_FOUND THEN
1103 RETURN NULL ;
1104 WHEN OTHERS THEN
1105 RETURN NULL;
1106
1107
1108 END GetUserName;
1109
1110 /*-------------------------------------------------------------------------------------
1111 This function returns hz_parties.party_id for fnd_user.user_id (IN parameter)
1112 -------------------------------------------------------------------------------------*/
1113 FUNCTION GetPartyId( p_user_id in Number)
1114 return NUMBER
1115 is
1116 l_emp_id NUMBER:=-999;
1117 BEGIN
1118 IF p_user_id IS NULL THEN
1119 RETURN NULL;
1120 END IF;
1121
1122 IF G_user_id=p_user_id AND G_party_id <> -999 THEN --added second condition for bug#5676456
1123 RETURN G_party_id;
1124 END IF;
1125
1126 -- SELECT employee_id, NVL(customer_id, supplier_id) Commented for Bug 4527617
1127 SELECT employee_id, NVL(person_party_id, supplier_id) -- For Bug 4527617
1128 INTO l_emp_id, G_party_id
1129 FROM fnd_user
1130 WHERE user_id = p_user_id;
1131
1132 IF l_emp_id>0 THEN
1133 SELECT party_id
1134 INTO G_party_id
1135 FROM per_all_people_f
1136 WHERE person_id = l_emp_id
1137 AND ROWNUM=1;
1138 END IF;
1139
1140 G_user_id:=p_user_id;
1141 G_party_name:=NULL;
1142
1143 RETURN G_party_id;
1144
1145
1146 EXCEPTION
1147 WHEN OTHERS THEN
1148 G_user_id:=-999;
1149 RETURN NULL;
1150
1151 END GetPartyId;
1152
1153
1154
1155
1156 FUNCTION CheckApprovalRequired(p_ci_id in Number)
1157 return Varchar2
1158 is
1159 l_approval_required varchar2(1) := 'N';
1160 l_type VARCHAR2(30);
1161
1162 BEGIN
1163 if p_ci_id is NULL then
1164 return NULL;
1165 end if;
1166
1167 SELECT ci_type_class_code, approval_required_flag
1168 INTO l_type, l_approval_required
1169 FROM pa_ci_types_b cit,
1170 pa_control_items ci
1171 WHERE ci.ci_id = p_ci_id
1172 and cit.ci_type_id = ci.ci_type_id;
1173
1174 IF l_type IN ('CHANGE_ORDER', 'CHANGE_REQUEST') THEN
1175 -- for change request and change order the approval_required flag means
1176 -- auto approval flag
1177 IF l_approval_required = 'N' THEN
1178 l_approval_required := 'Y';
1179 ELSE
1180 l_approval_required := 'A';
1181 END IF;
1182 END IF;
1183
1184 return l_approval_required;
1185
1186 EXCEPTION
1187 WHEN OTHERS THEN
1188 RETURN NULL;
1189
1190 END CheckApprovalRequired;
1191
1192
1193
1194 FUNCTION CheckResolutionRequired(p_ci_id in Number)
1195 return Varchar2
1196 is
1197 l_resolution_required varchar2(1) := 'N';
1198 BEGIN
1199 if p_ci_id is NULL then
1200 return NULL;
1201 end if;
1202
1203 select
1204 resolution_required_flag into l_resolution_required
1205 from pa_ci_types_b cit
1206 ,pa_control_items ci
1207 where ci.ci_id = p_ci_id
1208 and cit.ci_type_id = ci.ci_type_id;
1209
1210 return l_resolution_required;
1211
1212 EXCEPTION
1213 WHEN OTHERS THEN
1214 RETURN NULL;
1215
1216 END CheckResolutionRequired;
1217
1218 FUNCTION CheckHasResolution(p_ci_id in Number)
1219 return Varchar2
1220 is
1221 cursor get_resolution_info is
1222 select
1223 resolution_required_flag, resolution_code_id, resolution
1224 from pa_ci_types_b cit
1225 ,pa_control_items ci
1226 where ci.ci_id = p_ci_id
1227 and cit.ci_type_id = ci.ci_type_id;
1228
1229 l_has_resolution varchar2(1) := 'Y';
1230 l_res_required pa_ci_types_vl.resolution_required_flag%TYPE;
1231 l_res_code pa_control_items.resolution_code_id%TYPE;
1232 l_res_comment pa_control_items.resolution%TYPE;
1233 BEGIN
1234 if p_ci_id is NULL then
1235 return NULL;
1236 end if;
1237
1238 OPEN get_resolution_info;
1239 FETCH get_resolution_info INTO l_res_required, l_res_code, l_res_comment;
1240
1241 IF get_resolution_info%found AND l_res_required = 'Y'THEN
1242 if l_res_code is NULL or l_res_comment is NULL then
1243 l_has_resolution := 'N';
1244 end if;
1245 END IF;
1246 CLOSE get_resolution_info;
1247
1248 return l_has_resolution;
1249
1250 END CheckHasResolution;
1251
1252 FUNCTION GetCITypeClassCode(p_ci_id in Number)
1253 return Varchar2
1254 is
1255 l_type_class_code pa_ci_types_vl.ci_type_class_code%TYPE;
1256 BEGIN
1257 if p_ci_id is NULL then
1258 return NULL;
1259 end if;
1260
1261 select
1262 ci_type_class_code into l_type_class_code
1263 from pa_ci_types_b cit
1264 ,pa_control_items ci
1265 where ci.ci_id = p_ci_id
1266 and cit.ci_type_id = ci.ci_type_id;
1267
1268 return l_type_class_code;
1269
1270 EXCEPTION
1271 WHEN OTHERS THEN
1272 RETURN NULL;
1273
1274 END GetCITypeClassCode;
1275
1276 /*----------------------------------------------------------------------------
1277 Function to return SYSYEM status of control Item
1278 -----------------------------------------------------------------------------*/
1279 FUNCTION getCISystemStatus ( p_CI_id IN NUMBER)
1280 return VARCHAR2
1281 is
1282 l_ci_system_status pa_project_statuses.project_system_status_code%TYPE := NULL ;
1283
1284 BEGIN
1285 IF p_CI_id is not NULL then
1286 select ps.project_system_status_code
1287 into l_ci_system_status
1288 from pa_control_items ci
1289 ,pa_project_statuses ps
1290 where ci_id = p_ci_id
1291 and ps.project_status_code = nvl(ci.status_code,' ');
1292 END IF ;
1293
1294 return l_ci_system_status;
1295
1296 EXCEPTION
1297 WHEN NO_DATA_FOUND THEN
1298 RETURN NULL ;
1299 WHEN OTHERS THEN RAISE ;
1300
1301 END getCISystemStatus ;
1302
1303 /*----------------------------------------------------------------------------
1304 Function to return SYSYEM status for a ci statu_code
1305 -----------------------------------------------------------------------------*/
1306 FUNCTION getSystemStatus ( p_status_code IN VARCHAR2)
1307 return VARCHAR2
1308 is
1309 l_ci_system_status pa_project_statuses.project_system_status_code%TYPE := NULL ;
1310
1311 BEGIN
1312 IF p_status_code is not NULL then
1313 select ps.project_system_status_code
1314 into l_ci_system_status
1315 from
1316 pa_project_statuses ps
1317 where ps.project_status_code = nvl(p_status_code,' ');
1318 END IF ;
1319
1320 return l_ci_system_status;
1321
1322 EXCEPTION
1323 WHEN NO_DATA_FOUND THEN
1324 RETURN NULL ;
1325 WHEN OTHERS THEN RAISE ;
1326
1327 END getSystemStatus ;
1328
1329 FUNCTION deleteAllowed ( p_ci_id IN NUMBER := NULL
1330 ,p_owner_id IN NUMBER := NULL
1331 ,p_created_by_id IN NUMBER := NULL
1332 ,p_system_status IN VARCHAR2 := NULL)
1333 return VARCHAR2
1334 is
1335 l_system_status pa_project_statuses.project_system_status_code%TYPE := NULL ;
1336 BEGIN
1337 IF (p_system_status is not NULL AND p_system_status = 'CI_DRAFT') then
1338 return 'Y';
1339 END IF ;
1340
1341 IF (p_ci_id is not null) then
1342 l_system_status := getCISystemStatus(p_ci_id);
1343 IF l_system_status is not NULL AND l_system_status = 'CI_DRAFT' then
1344 return 'Y';
1345 END IF ;
1346 END IF;
1347
1348 return 'N';
1349
1350 EXCEPTION
1351 WHEN OTHERS THEN RAISE ;
1352 END deleteAllowed;
1353
1354
1355 FUNCTION closeAllowed ( p_ci_id IN NUMBER := NULL
1356 ,p_owner_id IN NUMBER := NULL
1357 ,p_created_by_id IN NUMBER := NULL
1358 ,p_system_status IN VARCHAR2 := NULL)
1359 return VARCHAR2
1360 is
1361
1362 l_type VARCHAR2(30) := NULL;
1363 l_approval VARCHAR2(1) := 'Y';
1364
1365 BEGIN
1366
1367 l_type := getcitypeclasscode(p_ci_id);
1368
1369 -- Change Requests cannot be closed
1370 IF l_type is not null and l_type = 'CHANGE_REQUEST' THEN
1371 return 'N';
1372 END IF ;
1373
1374 --approved Change Orders and Issues may be closed
1375 IF p_system_status is not NULL AND p_system_status = 'CI_APPROVED' then
1376 return 'Y';
1377 END IF ;
1378
1379 --"WORKING" Issues may be closed when approval not required
1380 IF l_type is not null and l_type = 'ISSUE'
1381 AND p_system_status is not NULL
1382 AND p_system_status = 'CI_WORKING' THEN
1383 l_approval := CheckApprovalRequired(p_ci_id);
1384 IF l_approval is NOT NULL AND l_approval = 'N' THEN
1385 return 'Y';
1386 END IF;
1387 END IF;
1388 return 'N';
1389
1390 EXCEPTION
1391 WHEN OTHERS THEN RAISE ;
1392 END closeAllowed;
1393
1394
1395 FUNCTION submitAllowed ( p_ci_id IN NUMBER := NULL
1396 ,p_owner_id IN NUMBER := NULL
1397 ,p_created_by_id IN NUMBER := NULL
1398 ,p_system_status IN VARCHAR2 := NULL)
1399
1400 return VARCHAR2
1401 is
1402
1403 l_approval VARCHAR2(1) := 'N';
1404
1405 BEGIN
1406
1407 if p_ci_id is NULL then
1408 return 'N';
1409 end if;
1410
1411 l_approval := CheckApprovalRequired(p_ci_id);
1412 IF l_approval is not NULL and l_approval <> 'N' THEN
1413 IF p_system_status is not NULL AND p_system_status = 'CI_WORKING' then
1414 return 'Y';
1415 END IF ;
1416 END IF;
1417 return 'N';
1418
1419 EXCEPTION
1420 WHEN OTHERS THEN RAISE ;
1421 END submitAllowed;
1422
1423
1424 /*----------------------------------------------------------------------------
1425 Function returns Y when there are non-DRAFT control items in a project.
1426 returns N when there are NO control items OR all all project control
1427 items are in DRAFT status, i.e. may be deleted.
1428 NOTE: this function returns NULL when IN parm, p_project_id, is NULL.
1429 -----------------------------------------------------------------------------*/
1430
1431 FUNCTION CheckNonDraftCI(p_project_id in Number)
1432 return Varchar2
1433
1434 is
1435 cursor c_non_draft_ci is
1436 select
1437 ci_id
1438 from pa_control_items
1439 ,pa_project_statuses
1440 where pa_control_items.project_id = p_project_id
1441 and pa_control_items.status_code = pa_project_statuses.project_status_code
1442 and pa_project_statuses.project_system_status_code <> 'CI_DRAFT';
1443
1444 publishedCI c_non_draft_ci%rowtype;
1445 hasNonDraftCI VARCHAR2(1) := 'Y';
1446
1447 BEGIN
1448 if p_project_id is NULL then
1449 return NULL;
1450 end if;
1451 open c_non_draft_ci;
1452 fetch c_non_draft_ci into publishedCI;
1453 if (c_non_draft_ci%NOTFOUND) then
1454 hasNonDraftCI := 'N';
1455 end if;
1456 close c_non_draft_ci;
1457
1458 return hasNonDraftCI;
1459
1460 EXCEPTION
1461
1462 WHEN NO_DATA_FOUND THEN
1463 RETURN NULL ;
1464 WHEN OTHERS THEN
1465 RETURN NULL;
1466
1467 END CheckNonDraftCI;
1468
1469 /*----------------------------------------------------------------------
1470 This function retrieves hz_party.party_id for a given name
1471 Obsoleted by MTHAI
1472 ----------------------------------------------------------------------*/
1473 function GET_PARTY_ID_FROM_NAME(p_name IN VARCHAR2
1474 ) return NUMBER is
1475 BEGIN
1476 return -999;
1477 END GET_PARTY_ID_FROM_NAME;
1478
1479
1480 FUNCTION check_control_item_exists(
1481 p_project_id IN NUMBER,
1482 p_task_id IN NUMBER default NULL)
1483 RETURN NUMBER
1484 IS
1485 tmp NUMBER;
1486 BEGIN
1487 SELECT 1
1488 INTO tmp
1489 FROM pa_control_items
1490 WHERE project_id = p_project_id
1491 AND ( ( p_task_id IS NOT NULL
1492 AND object_type='PA_TASKS'
1493 AND object_id=p_task_id)
1494 OR p_task_id IS NULL)
1495 AND ROWNUM = 1;
1496
1497 RETURN 1;
1498 EXCEPTION
1499 WHEN NO_DATA_FOUND THEN
1500 RETURN 0;
1501 END check_control_item_exists;
1502
1503 FUNCTION check_class_category_in_use(
1504 p_class_category IN VARCHAR2)
1505 RETURN NUMBER
1506 IS
1507 tmp NUMBER;
1508 BEGIN
1509 SELECT 1
1510 INTO tmp
1511 FROM pa_ci_types_b
1512 WHERE ( classification_category = p_class_category
1513 OR reason_category = p_class_category
1514 OR resolution_category = p_class_category)
1515 AND ROWNUM = 1;
1516
1517 RETURN 1;
1518 EXCEPTION
1519 WHEN NO_DATA_FOUND THEN
1520 RETURN 0;
1521 END check_class_category_in_use;
1522
1523 FUNCTION check_class_code_in_use(
1524 p_class_category IN VARCHAR2,
1525 p_class_code IN VARCHAR2)
1526 RETURN NUMBER
1527 IS
1528 tmp NUMBER;
1529 BEGIN
1530 SELECT 1
1531 INTO tmp
1532 FROM pa_control_items ci,
1533 pa_class_codes cc
1534 WHERE cc.class_category = p_class_category
1535 AND cc.class_code = p_class_code
1536 AND ( ci.classification_code_id = cc.class_code_id
1537 OR ci.reason_code_id = cc.class_code_id
1538 OR ci.resolution_code_id = cc.class_code_id)
1539 AND ROWNUM = 1;
1540
1541 RETURN 1;
1542 EXCEPTION
1543 WHEN NO_DATA_FOUND THEN
1544 RETURN 0;
1545 END check_class_code_in_use;
1546
1547 FUNCTION check_role_in_use(
1548 p_project_role_id IN NUMBER)
1549 RETURN NUMBER
1550 IS
1551 tmp NUMBER;
1552 BEGIN
1553 SELECT 1
1554 INTO tmp
1555 FROM pa_ci_types_b cit,
1556 pa_object_dist_lists odl,
1557 pa_dist_list_items dli
1558 WHERE odl.object_type = 'PA_CI_TYPES'
1559 AND odl.object_id = cit.ci_type_id
1560 AND dli.list_id = odl.list_id
1561 AND dli.recipient_type = 'PROJECT_ROLE'
1562 AND dli.recipient_id = p_project_role_id
1563 AND ROWNUM = 1;
1564
1565 RETURN 1;
1566 EXCEPTION
1567 WHEN NO_DATA_FOUND THEN
1568 RETURN 0;
1569 END check_role_in_use;
1570
1571 FUNCTION check_project_type_in_use(
1572 p_project_type_id IN NUMBER)
1573 RETURN NUMBER
1574 IS
1575 tmp NUMBER;
1576 BEGIN
1577 SELECT 1
1578 INTO tmp
1579 FROM pa_ci_type_usage
1580 WHERE project_type_id = p_project_type_id
1581 AND ROWNUM = 1;
1582
1583 RETURN 1;
1584 EXCEPTION
1585 WHEN NO_DATA_FOUND THEN
1586 RETURN 0;
1587 END check_project_type_in_use;
1588
1589 /*-------------------------------------------------------------------------------------
1590 Function Name: CheckNextPageValid
1591 Usage: Used with with lookup type to determine valid list of next pages to navigate
1592 Rules: 1. If CI id is null it is the create page.
1593 2. Exclude the current page from the next page list.
1594 ---------------------------------------------------------------------------------------*/
1595
1596 FUNCTION CheckNextPageValid( p_ci_id IN NUMBER := NULL
1597 ,p_type_id IN VARCHAR2
1598 ,p_status_control IN VARCHAR2
1599 ,p_page_code IN VARCHAR2
1600 ,p_currpage_code IN VARCHAR2
1601 ,p_type_class_code IN VARCHAR2)
1602
1603
1604 return VARCHAR2
1605 is
1606
1607
1608 l_ci_status varchar2(30):= NULL ;
1609 l_ci_type_has_impact varchar2(1) := 'N';
1610 l_check_update_access varchar2(1) := 'F';
1611 --l_stat_change varchar2(1) := 'F';
1612
1613 BEGIN
1614 IF p_page_code is NULL or p_currpage_code is NULL then
1615 return 'N' ;
1616 END IF ;
1617
1618 IF p_page_code = p_currpage_code then
1619 return 'N' ;
1620 END IF ;
1621
1622 -- "ADD ANOTHER" only in CI Create page
1623 IF p_page_code = 'ADD_ANOTHER' THEN
1624 -- requirement change: we now have a button (not a drop-down selection)
1625 -- in CREATE page to Add Another
1626 -- if p_currpage_code = 'CREATE_PAGE' then
1627 -- return 'Y';
1628 -- else
1629 return 'N';
1630 -- end if;
1631 END IF;
1632
1633 IF p_currpage_code = 'CREATE_PAGE' THEN
1634 if p_page_code = 'RESOLUTION'
1635 OR p_page_code = 'PROGRESS_OVERVIEW'
1636 OR p_page_code = 'INTERACTION_HISTORY' then
1637 return 'N';
1638 end if;
1639 END IF;
1640
1641
1642 -- Following pages require UPDATE access
1643 IF (p_page_code = 'CO_DETAILS'
1644 OR p_page_code = 'ISSUE_DETAILS'
1645 OR p_page_code = 'CR_DETAILS'
1646 ) THEN
1647 if p_currpage_code = 'CI_DETAILS' then
1648 return 'N';
1649 end if;
1650 if p_type_class_code is NULL then
1651 return 'N';
1652 end if;
1653 if (p_page_code = 'CO_DETAILS' and p_type_class_code <> 'CHANGE_ORDER')
1654 or (p_page_code = 'CR_DETAILS' and p_type_class_code <> 'CHANGE_REQUEST')
1655 or (p_page_code = 'ISSUE_DETAILS' and p_type_class_code <> 'ISSUE') then
1656 return 'N';
1657 end if;
1658
1659 if p_ci_id is NOT NULL then
1660 l_check_update_access := nvl(pa_ci_security_pkg.check_update_access(p_ci_id),'F');
1661 if l_check_update_access <> 'T' then
1662 return 'N';
1663 end if;
1664 end if;
1665
1666 end if;
1667
1668 IF p_page_code ='IMPACT_DETAILS' then
1669 if p_currpage_code = 'IMPLEMENT_IMPACT' then
1670 return 'N';
1671 end if;
1672
1673 if (p_type_id is NULL) then
1674 l_ci_type_has_impact := nvl(isCITypehasimpact(p_ci_id ),'N');
1675 else
1676 l_ci_type_has_impact := nvl(TypeHasImpact(p_type_id ),'N');
1677 end if;
1678
1679 if l_ci_type_has_impact ='Y' then
1680 return 'Y';
1681 else
1682 return 'N';
1683 end if;
1684 END IF ;
1685 -- this logic NOT executed for CREATE or LIST page
1686 if p_ci_id is NOT NULL
1687 AND p_status_control is not null then
1688 l_ci_status := getCIStatus(p_ci_id) ;
1689 if pa_control_items_utils.CheckCIActionAllowed(
1690 'CONTROL_ITEM',
1691 l_ci_status ,
1692 p_status_control ) <> 'Y' then
1693 return 'N';
1694 end if;
1695 END IF;
1696
1697 RETURN 'Y' ;
1698
1699 EXCEPTION
1700 WHEN OTHERS THEN
1701 RAISE ;
1702 END CheckNextPageValid ;
1703
1704 function get_open_control_items(p_project_id IN NUMBER,
1705 p_object_type IN VARCHAR2,
1706 p_object_id IN NUMBER,
1707 p_item_type IN VARCHAR2) return number is
1708
1709 tot_num NUMBER;
1710 begin
1711
1712 /*code changes for bug 5611926 starts here
1713 merged the similar select for change order/request/issue using the bind variable p_item_type*/
1714 if (p_item_type = 'ISSUE' or p_item_type = 'CHANGE_ORDER' or p_item_type = 'CHANGE_REQUEST') then
1715 select count(*)
1716 into tot_num
1717 from pa_control_items pci, pa_ci_types_b pctb
1718 where pci.project_id = p_project_id
1719 and pci.object_type = p_object_type
1720 and pci.object_id = p_object_id
1721 and pci.ci_type_id = pctb.ci_type_id
1722 and pctb.ci_type_class_Code = p_item_type --'ISSUE'
1723 and pci.status_code not in (select project_status_code /* changes start for Bug 5050836 */
1724 from pa_project_statuses
1725 where status_type = 'CONTROL_ITEM'
1726 and project_system_status_code
1727 in ('CI_DRAFT','CI_CLOSED','CI_CANCELED')); /* changes end for Bug 5050836 */
1728
1729 elsif (p_item_type = 'CHANGE') then
1730 select count(*)
1731 into tot_num
1732 from pa_control_items pci, pa_ci_types_b pctb
1733 where pci.project_id = p_project_id
1734 and pci.object_type = p_object_type
1735 and pci.object_id = p_object_id
1736 and pci.ci_type_id = pctb.ci_type_id
1737 and pctb.ci_type_class_Code in ('CHANGE_ORDER','CHANGE_REQUEST')
1738 and pci.status_code not in (select project_status_code /* changes start for Bug 5050836 */
1739 from pa_project_statuses
1740 where status_type = 'CONTROL_ITEM'
1741 and project_system_status_code
1742 in ('CI_DRAFT','CI_CLOSED','CI_CANCELED')); /* changes end for Bug 5050836 */
1743
1744 -- elsif (p_item_type = 'CHANGE_ORDER') then
1745 -- select count(*)
1746 -- into tot_num
1747 -- from pa_control_items pci, pa_ci_types_b pctb
1748 -- where pci.project_id = p_project_id
1749 -- and pci.object_type = p_object_type
1750 -- and pci.object_id = p_object_id
1751 -- and pci.ci_type_id = pctb.ci_type_id
1752 -- and pctb.ci_type_class_Code = 'CHANGE_ORDER'
1753 -- and pci.status_code not in (select project_status_code /* changes start for Bug 5050836 */
1754 -- from pa_project_statuses
1755 -- where status_type = 'CONTROL_ITEM'
1756 -- and project_system_status_code
1757 -- in ('CI_DRAFT','CI_CLOSED','CI_CANCELED')); /* changes end for Bug 5050836 */
1758
1759 -- elsif (p_item_type = 'CHANGE_REQUEST') then
1760 -- select count(*)
1761 -- into tot_num
1762 -- from pa_control_items pci, pa_ci_types_b pctb
1763 -- where pci.project_id = p_project_id
1764 -- and pci.object_type = p_object_type
1765 -- and pci.object_id = p_object_id
1766 -- and pci.ci_type_id = pctb.ci_type_id
1767 -- and pctb.ci_type_class_Code = 'CHANGE_REQUEST'
1768 -- and pci.status_code not in (select project_status_code /* changes start for Bug 5050836 */
1769 -- from pa_project_statuses
1770 -- where status_type = 'CONTROL_ITEM'
1771 -- and project_system_status_code
1772 -- in ('CI_DRAFT','CI_CLOSED','CI_CANCELED')); /* changes end for Bug 5050836 */
1773 /*code changes for bug 5611926 ends here*/
1774 end if;
1775
1776 return tot_num;
1777
1778 exception when others then
1779 return 0;
1780 end get_open_control_items;
1781
1782
1783 PROCEDURE GetDiagramUrl(p_project_id IN NUMBER,
1784 p_ci_id IN NUMBER,
1785 x_diagramurl OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1786 x_return_status OUT NOCOPY VARCHAR2,
1787 x_msg_count OUT NOCOPY NUMBER,
1788 x_msg_data OUT NOCOPY VARCHAR2)
1789 IS
1790
1791 cursor c_wf_type is
1792 SELECT ps.workflow_item_type,
1793 ps.workflow_process
1794 FROM pa_project_statuses ps,
1795 pa_control_items ci
1796 WHERE ci.ci_id = p_ci_id
1797 and ci.status_code = ps.project_status_code
1798 and ps.enable_wf_flag = 'Y'
1799 and ps.wf_success_status_code is NOT NULL
1800 and ps.wf_failure_status_code is NOT NULL;
1801
1802 CURSOR get_last_workflow_info(p_wf_item_type IN VARCHAR2, p_wf_process IN VARCHAR2) IS
1803 SELECT MAX(item_key)
1804 FROM pa_wf_processes
1805 WHERE item_type = p_wf_item_type
1806 AND description = p_wf_process
1807 AND entity_key2 = p_ci_id
1808 AND entity_key1 = p_project_id
1809 AND wf_type_code = 'Control Item';
1810
1811 l_diagramUrl VARCHAR2(2000);
1812 l_wf_item_type pa_project_statuses.workflow_item_type%TYPE;
1813 l_wf_process pa_project_statuses.workflow_process%TYPE;
1814 l_item_key pa_wf_processes.item_key%TYPE;
1815
1816 BEGIN
1817
1818 OPEN c_wf_type;
1819 FETCH c_wf_type INTO l_wf_item_type, l_wf_process;
1820 CLOSE c_wf_type;
1821
1822 OPEN get_last_workflow_info(l_wf_item_type, l_wf_process);
1823 FETCH get_last_workflow_info INTO l_item_key;
1824 CLOSE get_last_workflow_info;
1825
1826 l_diagramUrl := WF_MONITOR.GetDiagramURL
1827 (x_agent => WF_CORE.TRANSLATE('WF_WEB_AGENT'),
1828 x_item_type => l_wf_item_type,
1829 x_item_key => l_item_key);
1830 x_diagramUrl := l_diagramUrl;
1831
1832 EXCEPTION
1833 WHEN OTHERS THEN
1834
1835 x_return_status := 'U';
1836 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_UTILS',
1837 p_procedure_name => 'GetDiagramUrl',
1838 p_error_text => SUBSTRB(SQLERRM,1,240));
1839
1840 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1841 p_data => x_msg_data);
1842 END GetDiagramUrl;
1843
1844
1845 PROCEDURE AbortWorkflow(p_project_id IN NUMBER,
1846 p_ci_id IN NUMBER,
1847 p_record_version_number IN NUMBER,
1848 x_return_status OUT NOCOPY VARCHAR2,
1849 x_msg_count OUT NOCOPY NUMBER,
1850 x_msg_data OUT NOCOPY VARCHAR2)
1851 IS
1852 cursor c_wf_type is
1853 SELECT ps.workflow_item_type,
1854 ps.workflow_process
1855 FROM pa_project_statuses ps,
1856 pa_control_items ci
1857 WHERE ci.ci_id = p_ci_id
1858 and ci.status_code = ps.project_status_code
1859 and ps.enable_wf_flag = 'Y'
1860 and ps.wf_success_status_code is NOT NULL
1861 and ps.wf_failure_status_code is NOT NULL;
1862
1863 CURSOR get_last_workflow_info(p_wf_item_type IN VARCHAR2, p_wf_process IN VARCHAR2) IS
1864 SELECT MAX(item_key)
1865 FROM pa_wf_processes
1866 WHERE item_type = p_wf_item_type
1867 AND description = p_wf_process
1868 AND entity_key2 = p_ci_id
1869 AND entity_key1 = p_project_id
1870 AND wf_type_code = 'Control Item';
1871
1872 CURSOR get_prev_status(p_ci_id IN VARCHAR2) is
1873 select a.old_project_status_code, a.new_project_status_code
1874 from (select obj_status_change_id,
1875 old_project_status_code,
1876 new_project_status_code
1877 from pa_obj_status_changes
1878 where object_type = 'PA_CI_TYPES'
1879 and object_id = p_ci_id
1880 order by obj_status_change_id desc) a
1881 where rownum = 1;
1882
1883 l_diagramUrl VARCHAR2(2000);
1884 l_wf_item_type pa_project_statuses.workflow_item_type%TYPE;
1885 l_wf_process pa_project_statuses.workflow_process%TYPE;
1886 l_item_key pa_wf_processes.item_key%TYPE;
1887 l_prev_status pa_obj_status_changes.old_project_status_code%TYPE;
1888 l_curr_status pa_obj_status_changes.new_project_status_code%TYPE;
1889 l_comment pa_ci_comments.comment_text%TYPE;
1890 BEGIN
1891
1892 OPEN c_wf_type;
1893 FETCH c_wf_type INTO l_wf_item_type, l_wf_process;
1894 CLOSE c_wf_type;
1895
1896 OPEN get_last_workflow_info(l_wf_item_type, l_wf_process);
1897 FETCH get_last_workflow_info INTO l_item_key;
1898 CLOSE get_last_workflow_info;
1899
1900 OPEN get_prev_status(p_ci_id);
1901 FETCH get_prev_status INTO l_prev_status, l_curr_status;
1902 CLOSE get_prev_status;
1903
1904 pa_control_items_workflow.cancel_workflow
1905 (l_wf_item_type,
1906 l_item_key,
1907 x_msg_count,
1908 x_msg_data,
1909 x_return_status);
1910
1911 /* call pa_control_items_utils.changecistatus api to revert the status;
1912 PA_CONTROL_ITEMS_UTILS.ChangeCIStatus (
1913 p_init_msg_list => FND_API.G_TRUE
1914 ,p_validate_only => FND_API.G_FALSE
1915 ,p_ci_id => p_ci_id
1916 ,p_status => l_prev_status
1917 ,p_record_version_number => p_record_version_number
1918 ,x_num_of_actions => l_open_actions_num
1919 ,x_return_status => x_return_status
1920 ,x_msg_count => x_msg_count
1921 ,x_msg_data => x_msg_data);
1922 */
1923
1924 pa_control_items_pvt.UPDATE_CONTROL_ITEM_STATUS (
1925 p_api_version => 1.0
1926 ,p_init_msg_list => FND_API.G_TRUE
1927 ,p_validate_only => FND_API.G_FALSE
1928 ,p_ci_id => p_ci_id
1929 ,p_status_code => l_prev_status
1930 ,p_record_version_number => p_record_version_number
1931 ,x_return_status => x_return_status
1932 ,x_msg_count => x_msg_count
1933 ,x_msg_data => x_msg_data);
1934
1935 /* Bug#3297238: call the insert table handlers of pa_obj_status_changes and pa_ci_comments here */
1936
1937 fnd_message.set_name('PA', 'PA_CI_ABORT_WF_COMMENT');
1938 l_comment := fnd_message.get;
1939
1940 ADD_STATUS_CHANGE_COMMENT( p_object_type => 'PA_CI_TYPES'
1941 ,p_object_id => p_ci_id
1942 ,p_type_code => 'CHANGE_STATUS'
1943 ,p_status_type => 'CONTROL_ITEM'
1944 ,p_new_project_status => l_prev_status
1945 ,p_old_project_status => l_curr_status
1946 ,p_comment => l_comment
1947 ,x_return_status => x_return_status
1948 ,x_msg_count => x_msg_count
1949 ,x_msg_data => x_msg_data );
1950
1951
1952 EXCEPTION
1953 WHEN OTHERS THEN
1954
1955 x_return_status := 'U';
1956 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_UTILS',
1957 p_procedure_name => 'AbortWorkflow',
1958 p_error_text => SUBSTRB(SQLERRM,1,240));
1959
1960 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1961 p_data => x_msg_data);
1962 END AbortWorkflow;
1963
1964
1965 PROCEDURE ADD_STATUS_CHANGE_COMMENT (
1966 p_object_type IN VARCHAR2
1967 ,p_object_id IN NUMBER
1968 ,p_type_code IN VARCHAR2
1969 ,p_status_type IN VARCHAR2
1970 ,p_new_project_status IN VARCHAR2
1971 ,p_old_project_status IN VARCHAR2
1972 ,p_comment IN VARCHAR2 := null
1973 ,P_CREATED_BY IN NUMBER default fnd_global.user_id
1974 ,P_CREATION_DATE IN DATE default sysdate
1975 ,P_LAST_UPDATED_BY IN NUMBER default fnd_global.user_id
1976 ,P_LAST_UPDATE_DATE IN DATE default sysdate
1977 ,P_LAST_UPDATE_LOGIN IN NUMBER default fnd_global.user_id
1978 ,x_return_status OUT NOCOPY VARCHAR2
1979 ,x_msg_count OUT NOCOPY NUMBER
1980 ,x_msg_data OUT NOCOPY VARCHAR2 )
1981 IS
1982
1983 cursor c_status_name(p_status VARCHAR2) is
1984 select project_status_name
1985 from pa_project_statuses
1986 where status_type = 'CONTROL_ITEM'
1987 and project_status_code = p_status;
1988
1989 l_error_msg_code varchar2(30);
1990 l_ci_comment_id NUMBER;
1991 l_ci_action_id NUMBER;
1992 l_rowid VARCHAR2(255);
1993 l_new_sysstatus pa_project_statuses.project_system_status_code%TYPE;
1994 l_old_sysstatus pa_project_statuses.project_system_status_code%TYPE;
1995 l_comment_text pa_ci_comments.comment_text%TYPE;
1996 l_obj_status_change_id NUMBER;
1997 l_new_status_name pa_project_statuses.project_status_name%TYPE;
1998 l_old_status_name pa_project_statuses.project_status_name%TYPE;
1999
2000 BEGIN
2001 x_return_status := fnd_api.g_ret_sts_success;
2002 x_msg_data := 0;
2003
2004 l_new_sysstatus := getSystemStatus(p_new_project_status);
2005 l_old_sysstatus := getSystemStatus(p_old_project_status);
2006
2007 open c_status_name(p_new_project_status);
2008 fetch c_status_name into l_new_status_name;
2009 close c_status_name;
2010
2011 open c_status_name(p_old_project_status);
2012 fetch c_status_name into l_old_status_name;
2013 close c_status_name;
2014
2015 fnd_message.set_name('PA', 'PA_CI_LOG_STATUS_CHANGE');
2016 fnd_message.set_token('OLD_STATUS', l_old_status_name);
2017 fnd_message.set_token('NEW_STATUS', l_new_status_name);
2018 fnd_message.set_token('COMMENT', p_comment);
2019 l_comment_text := fnd_message.get;
2020
2021 SELECT pa_obj_status_changes_s.NEXTVAL
2022 INTO l_obj_status_change_id
2023 FROM dual;
2024
2025 PA_OBJ_STATUS_CHANGES_PKG.INSERT_ROW (
2026 X_ROWID => l_rowid,
2027 X_OBJ_STATUS_CHANGE_ID => l_obj_status_change_id,
2028 X_OBJECT_TYPE => p_object_type,
2029 X_OBJECT_ID => p_object_id,
2030 X_STATUS_TYPE => p_status_type,
2031 X_NEW_PROJECT_STATUS_CODE => p_new_project_status,
2032 X_NEW_PROJECT_SYSTEM_STATUS_CO => l_new_sysstatus,
2033 X_OLD_PROJECT_STATUS_CODE => p_old_project_status,
2034 X_OLD_PROJECT_SYSTEM_STATUS_CO => l_old_sysstatus,
2035 X_CHANGE_COMMENT => p_comment,
2036 X_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
2037 X_CREATED_BY => P_CREATED_BY,
2038 X_CREATION_DATE => P_CREATION_DATE,
2039 X_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
2040 X_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN);
2041
2042 PA_CI_COMMENTS_PKG.INSERT_ROW(
2043 P_CI_COMMENT_ID => L_CI_COMMENT_ID,
2044 P_CI_ID => P_OBJECT_ID,
2045 P_TYPE_CODE => P_TYPE_CODE,
2046 P_COMMENT_TEXT => L_COMMENT_TEXT,
2047 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
2048 P_CREATED_BY => P_CREATED_BY,
2049 P_CREATION_DATE => P_CREATION_DATE,
2050 P_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
2051 P_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
2052 P_CI_ACTION_ID => L_CI_ACTION_ID);
2053
2054 EXCEPTION
2055 WHEN FND_API.G_EXC_ERROR THEN
2056 x_return_status := 'E';
2057 WHEN OTHERS THEN
2058 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2059 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_UTILS',
2060 p_procedure_name => 'ADD_STATUS_CHANGE_COMMENT',
2061 p_error_text => SUBSTRB(SQLERRM,1,240));
2062 RAISE;
2063 END ADD_STATUS_CHANGE_COMMENT;
2064
2065 --Bug 4716789 Added an API to delete the data from pa_obj_status_changes
2066 PROCEDURE DELETE_OBJ_STATUS_CHANGES(
2067 p_object_type IN VARCHAR2
2068 ,p_object_id IN NUMBER
2069 ,x_return_status OUT NOCOPY VARCHAR2
2070 ,x_msg_count OUT NOCOPY NUMBER
2071 ,x_msg_data OUT NOCOPY VARCHAR2 )
2072 IS
2073 BEGIN
2074 x_return_status := FND_API.G_RET_STS_SUCCESS;
2075
2076 DELETE FROM pa_obj_status_changes
2077 WHERE object_type = p_object_type
2078 AND object_id = p_object_id;
2079
2080 EXCEPTION
2081 WHEN OTHERS THEN
2082 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2083 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_UTILS',
2084 p_procedure_name => 'DELETE_OBJ_STATUS_CHANGES',
2085 p_error_text => SUBSTRB(SQLERRM,1,240));
2086 RAISE;
2087 END DELETE_OBJ_STATUS_CHANGES;
2088
2089 PROCEDURE ChangeCIStatusValidate (
2090 p_init_msg_list IN VARCHAR2 := fnd_api.g_true
2091 ,p_commit IN VARCHAR2 := FND_API.g_false
2092 ,p_validate_only IN VARCHAR2 := FND_API.g_true
2093 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
2094 ,p_ci_id IN NUMBER
2095 ,p_status IN VARCHAR2
2096 ,p_enforce_security IN VARCHAR2 DEFAULT 'Y'
2097 ,p_resolution_check IN VARCHAR2 DEFAULT 'UI'
2098 ,x_resolution_req OUT NOCOPY VARCHAR2
2099 ,x_resolution_req_cls OUT NOCOPY VARCHAR2
2100 ,x_start_wf OUT NOCOPY VARCHAR2
2101 ,x_new_status OUT NOCOPY VARCHAR2
2102 ,x_num_of_actions OUT NOCOPY NUMBER
2103 ,x_return_status OUT NOCOPY VARCHAR2
2104 ,x_msg_count OUT NOCOPY NUMBER
2105 ,x_msg_data OUT NOCOPY VARCHAR2 )
2106 IS
2107 CURSOR get_ci_info
2108 IS
2109 SELECT pci.status_code, pci.project_id
2110 FROM pa_control_items pci
2111 WHERE ci_id = p_ci_id;
2112
2113
2114 CURSOR get_status_name(l_code varchar2)
2115 IS SELECT meaning
2116 FROM pa_lookups
2117 WHERE lookup_type = 'CONTROL_ITEM_SYSTEM_STATUS'
2118 AND lookup_code = l_code;
2119
2120 CURSOR get_control_item_type
2121 IS
2122 SELECT pl.meaning,pl.lookup_code
2123 FROM pa_lookups pl, pa_control_items pci, pa_ci_types_b pcit
2124 WHERE
2125 pl.lookup_type = 'PA_CI_TYPE_CLASSES'
2126 and pci.ci_type_id = pcit.ci_type_id
2127 and pl.lookup_code = pcit.ci_type_class_code
2128 AND pci.ci_id = p_ci_id;
2129
2130 l_tp VARCHAR2(1);
2131 l_project_id NUMBER;
2132
2133 CURSOR check_if_fin_impact_exists
2134 is
2135 SELECT 'Y' FROM dual
2136 WHERE exists
2137 (
2138 SELECT * FROM pa_ci_impacts
2139 WHERE ci_id = p_ci_id
2140 AND impact_type_code like 'FINPLAN%'
2141 );
2142
2143 CURSOR c_submit_status(p_project_status_code varchar2) is
2144 select 'Y', wf_success_status_code from pa_project_statuses
2145 where project_status_code = p_project_status_code
2146 and enable_wf_flag = 'Y'
2147 and workflow_item_type is not null
2148 and workflow_process is not null
2149 and wf_success_status_code is not null
2150 and wf_failure_status_code is not null;
2151
2152
2153 l_status VARCHAR2(30);
2154 l_new_status VARCHAR2(30);
2155 l_ret boolean;
2156 l_msg_index_out NUMBER;
2157 l_approval_required VARCHAR2(1);
2158 l_type VARCHAR2(30);
2159 l_temp VARCHAR2(1);
2160 l_t1 VARCHAR2(80);
2161 l_t2 VARCHAR2(80);
2162 l_t3 VARCHAR2(200);
2163 l_type_code VARCHAR2(80);
2164 l_start_wf VARCHAR2(1) := 'Y';
2165
2166 l_curr_sys_status VARCHAR2(30);
2167 l_next_sys_status VARCHAR2(30);
2168 l_submit_status VARCHAR2(30);
2169 l_submit_status_flag VARCHAR2(1);
2170
2171
2172
2173 BEGIN
2174
2175 IF p_init_msg_list = FND_API.G_TRUE THEN
2176 fnd_msg_pub.initialize;
2177 END IF;
2178
2179 x_msg_count := 0;
2180 x_msg_data := '';
2181 x_start_wf := l_start_wf;
2182
2183 -- Initialize the Error Stack
2184 IF P_PA_DEBUG_MODE = 'Y' THEN
2185 PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_UTILS.ChangeCIStatus');
2186 END IF;
2187
2188 pa_debug.write_file('ChangeCiStatusValidate: p_pa_debug_mode :'||p_pa_debug_mode);
2189
2190 -- Initialize the return status to success
2191 x_return_status := FND_API.G_RET_STS_SUCCESS;
2192
2193 OPEN get_ci_info;
2194 FETCH get_ci_info INTO l_status, l_project_id;
2195 CLOSE get_ci_info;
2196
2197 if l_status is NOT NULL and
2198 p_status is NOT NULL and
2199 p_status = l_status then
2200 return;
2201 end if;
2202
2203 l_curr_sys_status := getSystemStatus(l_status);
2204 l_next_sys_status := getSystemStatus(p_status);
2205
2206 OPEN get_control_item_type;
2207 FETCH get_control_item_type INTO l_t3, l_type_code;
2208 CLOSE get_control_item_type;
2209
2210 l_new_status := p_status;
2211 x_new_status := l_new_status;
2212
2213
2214 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
2215 -- Check Security Access for Update
2216 -- bug fix
2217 -- do not call secruity check for CR when from APPROVED to CLOSED
2218 -- or from CLOSED to APPROVED (added by MSU)
2219
2220 IF ( l_type_code = 'CHANGE_REQUEST'
2221 AND ( (l_next_sys_status = 'CI_CLOSED' AND l_curr_sys_status = 'CI_APPROVED')
2222 OR (l_curr_sys_status = 'CI_CLOSED' AND l_next_sys_status = 'CI_APPROVED') )
2223 ) then
2224 NULL;
2225 ELSE
2226
2227 l_temp := pa_ci_security_pkg.check_change_status_access(p_ci_id,
2228 fnd_global.user_id);
2229
2230 IF P_PA_DEBUG_MODE = 'Y' THEN
2231 pa_debug.write_file('ChangeCiStatusValidate: pa_ci_security_pkg.check_change_status_access :'||l_temp);
2232 END IF;
2233
2234 IF (l_temp <> 'T') THEN
2235 IF P_PA_DEBUG_MODE = 'Y' THEN
2236 pa_debug.write_file('ChangeCiStatusValidate: PA_CI_STATUS_UPDATE_INV');
2237 END IF;
2238 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2239 ,p_msg_name => 'PA_CI_STATUS_UPDATE_INV');
2240 x_return_status := FND_API.G_RET_STS_ERROR;
2241 END IF;
2242 END IF;
2243 END IF;
2244
2245
2246 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
2247
2248 OPEN c_submit_status(p_status);
2249 FETCH c_submit_status INTO l_submit_status_flag, l_submit_status;
2250 CLOSE c_submit_status;
2251
2252 IF P_PA_DEBUG_MODE = 'Y' THEN
2253 pa_debug.write_file('ChangeCiStatusValidate: l_submit_status_flag :'||l_submit_status_flag);
2254 pa_debug.write_file('ChangeCiStatusValidate: l_submit_status:'||l_submit_status);
2255 END IF;
2256
2257 IF l_submit_status_flag = 'Y' THEN
2258
2259 l_approval_required := checkapprovalrequired (p_ci_id);
2260 IF P_PA_DEBUG_MODE = 'Y' THEN
2261 pa_debug.write_file('ChangeCiStatusValidate: l_approval_required :'||l_approval_required);
2262 END IF;
2263
2264 IF l_approval_required = 'Y' or l_approval_required = 'A' THEN
2265 -- check the type is CR, CO, ISSUE
2266 l_type := getcitypeclasscode(p_ci_id);
2267
2268 IF P_PA_DEBUG_MODE = 'Y' THEN
2269 pa_debug.write_file('ChangeCiStatusValidate: l_type :'||l_type);
2270 END IF;
2271
2272 IF l_type = 'CHANGE_ORDER' OR l_type = 'CHANGE_REQUEST' OR l_type = 'ISSUE' THEN
2273
2274 IF (l_type = 'CHANGE_ORDER' OR l_type = 'CHANGE_REQUEST') AND l_approval_required = 'A' THEN
2275 OPEN c_submit_status(p_status);
2276 FETCH c_submit_status INTO l_submit_status_flag, l_submit_status;
2277 CLOSE c_submit_status;
2278
2279 l_new_status := l_submit_status;
2280 l_start_wf := 'N';
2281 x_start_wf := l_start_wf;
2282 x_new_status := l_new_status;
2283
2284 IF P_PA_DEBUG_MODE = 'Y' THEN
2285 pa_debug.write_file('ChangeCiStatusValidate: l_new_status :'||l_new_status);
2286 pa_debug.write_file('ChangeCiStatusValidate: l_start_wf :'||l_start_wf);
2287 END IF;
2288
2289 END IF;
2290
2291 l_temp := pa_ci_actions_util.check_open_actions_exist (p_ci_id);
2292
2293 IF P_PA_DEBUG_MODE = 'Y' THEN
2294 pa_debug.write_file('ChangeCiStatusValidate: pa_ci_actions_util.check_open_actions_exist :'||l_temp);
2295
2296 END IF;
2297
2298 IF l_temp = 'Y' THEN
2299 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2300 ,p_msg_name => 'PA_CI_OPEN_ACTION_EXISTS');
2301 IF P_PA_DEBUG_MODE = 'Y' THEN
2302 pa_debug.write_file('ChangeCiStatus: PA_CI_OPEN_ACTION_EXIST');
2303 END IF;
2304 x_return_status := FND_API.G_RET_STS_ERROR;
2305 ELSE
2306 -- check resolution is provided
2307 IF p_resolution_check = 'UI' THEN
2308 IF P_PA_DEBUG_MODE = 'Y' THEN
2309 pa_debug.write_file('ChangeCiStatus: checkresolutionrequired :'||checkresolutionrequired(p_ci_id));
2310 END IF;
2311 IF checkresolutionrequired(p_ci_id) = 'Y' THEN
2312
2313 IF P_PA_DEBUG_MODE = 'Y' THEN
2314 pa_debug.write_file('ChangeCiStatusValidate: checkhasresolution :'||checkhasresolution (p_ci_id));
2315 END IF;
2316 IF checkhasresolution (p_ci_id) = 'Y' THEN
2317 IF l_type = 'CHANGE_ORDER' OR l_type = 'CHANGE_REQUEST' OR l_type = 'ISSUE' THEN
2318 NULL;
2319 END IF;
2320 ELSE
2321 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2322 ,p_msg_name => 'PA_CI_RESOLUTION_OPEN');
2323 IF P_PA_DEBUG_MODE = 'Y' THEN
2324 pa_debug.write_file('ChangeCiStatus: PA_CI_RESOLUTION_OPEN');
2325 END IF;
2326 x_return_status := FND_API.G_RET_STS_ERROR;
2327 END IF;
2328 END IF;
2329 ELSIF p_resolution_check = 'AMG' THEN
2330 IF P_PA_DEBUG_MODE = 'Y' THEN
2331 pa_debug.write_file('ChangeCiStatus: AMG checkresolutionrequired :'||checkresolutionrequired(p_ci_id));
2332 END IF;
2333 IF checkresolutionrequired(p_ci_id) = 'Y' THEN
2334 x_resolution_req := 'Y';
2335 IF P_PA_DEBUG_MODE = 'Y' THEN
2336 pa_debug.write_file('ChangeCiStatusValidate: AMG checkhasresolution :'||checkhasresolution (p_ci_id));
2337 END IF;
2338 END IF;
2339 END IF; /* IF p_resolution_check = 'UI' */
2340 END IF; /* if l_temp = 'Y' */
2341
2342 ELSE
2343 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2344 ,p_msg_name => 'PA_CI_SUBMIT_INV');
2345 IF P_PA_DEBUG_MODE = 'Y' THEN
2346 pa_debug.write_file('ChangeCiStatus: PA_CI_SUBMIT_INV');
2347 END IF;
2348 x_return_status := FND_API.G_RET_STS_ERROR;
2349 END IF;
2350
2351 ELSE
2352 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2353 ,p_msg_name => 'PA_CI_APPROVAL_NOT_REQ');
2354 IF P_PA_DEBUG_MODE = 'Y' THEN
2355 pa_debug.write_file('ChangeCiStatusValidate: PA_CI_APPROVAL_NOT_REQ');
2356 END IF;
2357 x_return_status := FND_API.G_RET_STS_ERROR;
2358 END IF; /* IF l_approval_required = 'Y' or l_approval_required = 'A' THEN */
2359
2360 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2361 -- check if submit is OK for finacial impact
2362
2363 OPEN check_if_fin_impact_exists;
2364 FETCH check_if_fin_impact_exists INTO l_tp;
2365 CLOSE check_if_fin_impact_exists;
2366
2367 IF P_PA_DEBUG_MODE = 'Y' THEN
2368 pa_debug.write_file('ChangeCiStatusValidate: check_if_fin_impact_exists: '||l_tp);
2369 END IF;
2370
2371 IF l_tp = 'Y' THEN
2372
2373 IF P_PA_DEBUG_MODE = 'Y' THEN
2374 pa_debug.write_file('ChangeCiStatus: before call to Pa_Fp_Control_Items_Utils.Fp_Ci_Impact_Submit_Chk');
2375
2376 END IF;
2377
2378 Pa_Fp_Control_Items_Utils.Fp_Ci_Impact_Submit_Chk
2379 ( p_project_id => l_project_id
2380 ,p_ci_id => p_ci_id
2381 ,x_return_status => x_return_status
2382 ,x_msg_count => x_msg_count
2383 ,x_msg_data => x_msg_data) ;
2384
2385 IF P_PA_DEBUG_MODE = 'Y' THEN
2386 pa_debug.write_file('ChangeCiStatusValidate: after call to Pa_Fp_Control_Items_Utils.Fp_Ci_Impact_Submit_Chk');
2387
2388 END IF;
2389
2390 END IF;
2391 END IF;
2392
2393 END IF; /* IF l_submit_status_flag = 'Y' THEN */
2394
2395 IF P_PA_DEBUG_MODE = 'Y' THEN
2396 pa_debug.write_file('ChangeCiStatusValidate: end of <IF l_submit_status_flag = Y > ');
2397 END IF;
2398
2399 IF l_next_sys_status = 'CI_CANCELED' THEN
2400
2401 IF P_PA_DEBUG_MODE = 'Y' THEN
2402 pa_debug.write_file('ChangeCiStatusValidate: in <l_next_sys_status = CI_CANCELED> ');
2403 END IF;
2404 l_type := getcitypeclasscode(p_ci_id);
2405
2406 IF l_type = 'CHANGE_ORDER' OR l_type = 'CHANGE_REQUEST' THEN
2407 -- check if any impact is implemented
2408 l_ret := pa_ci_impacts_util.is_any_impact_implemented(p_ci_id);
2409
2410
2411 IF (l_ret ) THEN
2412 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2413 ,p_msg_name => 'PA_CI_CANCEL_INV_IMP');
2414 IF P_PA_DEBUG_MODE = 'Y' THEN
2415 pa_debug.write_file('ChangeCiStatusValidate: in <l_next_sys_status = CI_CANCELED> PA_CI_CANCEL_INV_IMP');
2416
2417 END IF;
2418 x_return_status := FND_API.G_RET_STS_ERROR;
2419 END IF;
2420 END IF;
2421
2422 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
2423 -- cancel workflow
2424 IF P_PA_DEBUG_MODE = 'Y' THEN
2425 pa_debug.write_file('ChangeCiStatusValidate: in <l_next_sys_status = CI_CANCELED> before cancelworkflow');
2426
2427 END IF;
2428 cancelworkflow
2429 (
2430 1.0,
2431 p_init_msg_list ,
2432 p_commit ,
2433 p_validate_only ,
2434 p_max_msg_count ,
2435 p_ci_id ,
2436 x_msg_count ,
2437 x_msg_data ,
2438 x_return_status
2439 );
2440 IF P_PA_DEBUG_MODE = 'Y' THEN
2441 pa_debug.write_file('ChangeCiStatusValidate: in <l_next_sys_status = CI_CANCELED> after cancelworkflow');
2442
2443 END IF;
2444
2445 END IF;
2446
2447 ELSIF l_next_sys_status = 'CI_CLOSED' THEN
2448
2449 IF P_PA_DEBUG_MODE = 'Y' THEN
2450 pa_debug.write_file('ChangeCiStatusValidate: in <l_next_sys_status = CI_CLOSED>');
2451 END IF;
2452
2453 l_type := getcitypeclasscode(p_ci_id);
2454
2455 IF l_type = 'CHANGE_ORDER' THEN
2456 -- check if any impact is implemented
2457
2458 l_ret := pa_ci_impacts_util.is_all_impact_implemented(p_ci_id);
2459
2460 IF (NOT l_ret ) THEN
2461 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2462 ,p_msg_name => 'PA_CI_CLOSE_INV_IMP');
2463 IF P_PA_DEBUG_MODE = 'Y' THEN
2464 pa_debug.write_file('ChangeCiStatusValidate: in <l_next_sys_status = CI_CLOSED> PA_CI_CLOSE_INV_IMP');
2465
2466 END IF;
2467 x_return_status := FND_API.G_RET_STS_ERROR;
2468 END IF;
2469 END IF;
2470
2471 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
2472 l_temp := pa_ci_actions_util.check_open_actions_exist (p_ci_id);
2473
2474 IF l_temp = 'Y' THEN
2475 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2476 ,p_msg_name => 'PA_CI_CLOSE_INV_ACT');
2477 IF P_PA_DEBUG_MODE = 'Y' THEN
2478 pa_debug.write_file('ChangeCiStatusValidate: in <l_next_sys_status = CI_CLOSED> PA_CI_CLOSE_INV_ACT');
2479
2480 END IF;
2481 x_return_status := FND_API.G_RET_STS_ERROR;
2482 END IF;
2483
2484 IF p_resolution_check = 'UI' THEN
2485 IF checkresolutionrequired(p_ci_id) = 'Y' THEN
2486 IF checkhasresolution (p_ci_id) = 'Y' THEN
2487 NULL;
2488 ELSE
2489 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2490 ,p_msg_name => 'PA_CI_CLOSE_INV_RES');
2491 IF P_PA_DEBUG_MODE = 'Y' THEN
2492 pa_debug.write_file('ChangeCiStatus: in <l_next_sys_status = CI_CLOSED> PA_CI_CLOSE_INV_RES');
2493 END IF;
2494 x_return_status := FND_API.G_RET_STS_ERROR;
2495 END IF;
2496 END IF;
2497 ELSIF p_resolution_check = 'AMG' THEN
2498 IF checkresolutionrequired(p_ci_id) = 'Y' THEN
2499 x_resolution_req_cls := 'Y';
2500 END IF;
2501 END IF;
2502
2503 END IF;
2504
2505
2506 END IF;
2507
2508 IF l_curr_sys_status = 'CI_APPROVED' THEN
2509
2510 IF P_PA_DEBUG_MODE = 'Y' THEN
2511 pa_debug.write_file('ChangeCiStatus: in <l_curr_sys_status = CI_APPROVED> and');
2512 END IF;
2513
2514 IF l_next_sys_status = 'CI_WORKING' THEN
2515 -- check no impact is implemented
2516 l_ret := pa_ci_impacts_util.is_any_impact_implemented(p_ci_id);
2517
2518 IF (l_ret ) THEN
2519 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2520 ,p_msg_name => 'PA_CI_WORKING_INV_IMP');
2521 IF P_PA_DEBUG_MODE = 'Y' THEN
2522 pa_debug.write_file('ChangeCiStatus: in <l_next_sys_status = CI_WORKING> PA_CI_WORKING_INV_IMP');
2523
2524
2525 END IF;
2526 x_return_status := FND_API.G_RET_STS_ERROR;
2527 END IF;
2528
2529 ELSIF l_next_sys_status = 'CI_CLOSED' THEN
2530
2531 l_type := getcitypeclasscode(p_ci_id);
2532
2533 -- check all impact are implemented
2534 IF l_type = 'CHANGE_ORDER' then
2535 l_ret := pa_ci_impacts_util.is_all_impact_implemented(p_ci_id);
2536
2537 IF (NOT l_ret ) THEN
2538 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2539 ,p_msg_name => 'PA_CI_CLOSE_INV_IMP');
2540 IF P_PA_DEBUG_MODE = 'Y' THEN
2541 pa_debug.write_file('ChangeCiStatus: in <l_next_sys_status = CI_CLOSED> PA_CI_CLOSE_INV_IMP');
2542 END IF;
2543 x_return_status := FND_API.G_RET_STS_ERROR;
2544 END IF;
2545 END IF;
2546
2547 ELSIF l_next_sys_status = 'CI_CANCELED' THEN
2548 -- check no impact is implemented
2549
2550 l_ret := pa_ci_impacts_util.is_any_impact_implemented
2551 (p_ci_id);
2552
2553 IF (l_ret ) THEN
2554 PA_UTILS.Add_Message( p_app_short_name => 'PA'
2555 ,p_msg_name => 'PA_CI_CANCEL_INV_IMP');
2556 IF P_PA_DEBUG_MODE = 'Y' THEN
2557 pa_debug.write_file('ChangeCiStatus: in <l_next_sys_status = CI_CANCELED> PA_CI_CANCEL_INV_IMP');
2558 END IF;
2559 x_return_status := FND_API.G_RET_STS_ERROR;
2560 END IF;
2561 END IF;
2562
2563 END IF; /* IF l_curr_sys_status = 'CI_APPROVED' THEN */
2564
2565 END if;
2566
2567 EXCEPTION
2568 WHEN OTHERS THEN
2569
2570 IF P_PA_DEBUG_MODE = 'Y' THEN
2571 pa_debug.write_file('ChangeCiStatus: in when others exception');
2572 END IF;
2573
2574 ROLLBACK;
2575
2576 x_return_status := 'U';
2577 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_UTILS',
2578 p_procedure_name => 'ChangeCIStatus',
2579 p_error_text => SUBSTRB(SQLERRM,1,240));
2580
2581 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2582 p_data => x_msg_data);
2583
2584 END ChangeCIStatusValidate;
2585
2586
2587 PROCEDURE PostChangeCIStatus (
2588 p_init_msg_list IN VARCHAR2 := fnd_api.g_true
2589 ,p_commit IN VARCHAR2 := FND_API.g_false
2590 ,p_validate_only IN VARCHAR2 := FND_API.g_true
2591 ,p_max_msg_count IN NUMBER := FND_API.g_miss_num
2592 ,p_ci_id in number
2593 ,p_curr_status in varchar2
2594 ,p_new_status in varchar2
2595 ,p_start_wf in VARCHAR2
2596 ,p_enforce_security in Varchar2 DEFAULT 'Y'
2597 ,x_num_of_actions OUT NOCOPY NUMBER
2598 ,x_return_status OUT NOCOPY VARCHAR2
2599 ,x_msg_count OUT NOCOPY NUMBER
2600 ,x_msg_data OUT NOCOPY VARCHAR2 )
2601 IS
2602
2603 l_start_wf VARCHAR2(1) := 'Y';
2604 l_curr_sys_status VARCHAR2(30);
2605
2606 l_next_sys_status VARCHAR2(30);
2607
2608
2609 BEGIN
2610
2611 l_start_wf := p_start_wf;
2612 l_curr_sys_status := getSystemStatus(p_curr_status);
2613 l_next_sys_status := getSystemStatus(p_new_status);
2614
2615
2616 IF p_init_msg_list = FND_API.G_TRUE THEN
2617 fnd_msg_pub.initialize;
2618 END IF;
2619
2620 x_msg_count := 0;
2621 x_msg_data := '';
2622
2623 -- Initialize the Error Stack
2624 IF P_PA_DEBUG_MODE = 'Y' THEN
2625 PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_UTILS.ChangeCIStatus');
2626 END IF;
2627
2628 pa_debug.write_file('ChangeCiStatusValidate: p_pa_debug_mode :'||p_pa_debug_mode);
2629
2630 -- Initialize the return status to success
2631 x_return_status := FND_API.G_RET_STS_SUCCESS;
2632
2633 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
2634
2635 IF l_curr_sys_status = 'CI_APPROVED' or l_curr_sys_status = 'CI_SUBMITTED' or l_curr_sys_status = 'CI_WORKING' THEN
2636
2637 IF l_next_sys_status = 'CI_CANCELED' THEN
2638 -- set included CR status to APPROVED
2639
2640 IF P_PA_DEBUG_MODE = 'Y' THEN
2641 pa_debug.write_file('ChangeCiStatus: before call to pa_control_items_pvt.change_included_cr_status');
2642 END IF;
2643
2644 pa_control_items_pvt.change_included_cr_status
2645 (p_ci_id,
2646 x_return_status,
2647 x_msg_count,
2648 x_msg_data
2649 );
2650
2651 IF P_PA_DEBUG_MODE = 'Y' THEN
2652 pa_debug.write_file('ChangeCiStatus: after call to pa_control_items_pvt.change_included_cr_status');
2653 END IF;
2654
2655 ----- call delete included items api here
2656
2657 IF P_PA_DEBUG_MODE = 'Y' THEN
2658 pa_debug.write_file('ChangeCiStatus: before call to pa_control_items_pvt.delete_all_included_crs');
2659 END IF;
2660
2661 pa_control_items_pvt.delete_all_included_crs
2662 (p_validate_only => 'F',
2663 p_init_msg_list => 'F',
2664 p_ci_id => p_ci_id,
2665 x_return_status => x_return_status,
2666 x_msg_count => x_msg_count,
2667 x_msg_data => x_msg_data);
2668
2669 IF P_PA_DEBUG_MODE = 'Y' THEN
2670 pa_debug.write_file('ChangeCiStatus: after call to pa_control_items_pvt.delete_all_included_crs');
2671 END IF;
2672
2673 -- cancel open actions
2674 IF (l_curr_sys_status = 'CI_WORKING' and p_validate_only <> fnd_api.g_true AND x_return_status = 'S') then
2675
2676 pa_ci_actions_pvt.cancel_all_actions
2677 (
2678 p_init_msg_list=> p_init_msg_list,
2679 p_commit => p_commit,
2680 p_validate_only => p_validate_only,
2681 p_max_msg_count => p_max_msg_count,
2682 p_ci_id =>p_ci_id,
2683 p_cancel_comment => FND_MESSAGE.GET_STRING('PA','PA_CI_CANCELED'),
2684 x_return_status => x_return_status,
2685 x_msg_count => x_msg_count,
2686 x_msg_data =>x_msg_data
2687 );
2688
2689 END if;
2690
2691 END IF;
2692
2693 END IF;
2694 END IF;
2695
2696 IF (p_validate_only <> fnd_api.g_true AND x_return_status = 'S') THEN
2697
2698 IF P_PA_DEBUG_MODE = 'Y' THEN
2699 pa_debug.write_file('ChangeCiStatus: l_start_wf :'||l_start_wf);
2700 END IF;
2701
2702 IF (l_start_wf = 'Y') then
2703
2704 IF P_PA_DEBUG_MODE = 'Y' THEN
2705
2706 pa_debug.write_file('ChangeCiStatus: before call to checkandstartworkflow');
2707 END IF;
2708
2709 checkandstartworkflow
2710 (
2711 1.0 ,
2712 p_init_msg_list ,
2713 p_commit ,
2714 p_validate_only ,
2715 p_max_msg_count ,
2716 p_ci_id ,
2717 p_new_status,
2718 x_msg_count ,
2719 x_msg_data ,
2720 x_return_status
2721 );
2722
2723 IF P_PA_DEBUG_MODE = 'Y' THEN
2724 pa_debug.write_file('ChangeCiStatus: after call to checkandstartworkflow');
2725 END IF;
2726
2727 END IF;
2728
2729 END if;
2730
2731
2732
2733 EXCEPTION
2734 WHEN OTHERS THEN
2735
2736 IF P_PA_DEBUG_MODE = 'Y' THEN
2737 pa_debug.write_file('ChangeCiStatus: in when others exception');
2738 END IF;
2739
2740 ROLLBACK;
2741
2742 x_return_status := 'U';
2743 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_CONTROL_ITEMS_UTILS',
2744 p_procedure_name => 'ChangeCIStatus',
2745 p_error_text => SUBSTRB(SQLERRM,1,240));
2746
2747 fnd_msg_pub.count_and_get(p_count => x_msg_count,
2748 p_data => x_msg_data);
2749
2750 END PostChangeCIStatus;
2751
2752
2753 END PA_CONTROL_ITEMS_UTILS;
[Home] [Help]