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