[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_WF
Source
1 PACKAGE BODY pa_project_wf AS
2 /* $Header: PAWFPRVB.pls 120.2.12010000.3 2008/11/24 07:30:59 bifernan ship $ */
3
4 -- ==================================================
5 --
6 --Name: Select_Project_Approver
7 --Type: Procedure
8 --Description: This procedure calls a client extension
9 -- that returns the correct ID of the
10 -- project approver.
11 --
12 --
13 --Called subprograms: PA_CLIENT_EXTN_PROJECT_WF.Select_Project_Approver
14 --
15 --
16 --
17 --
18 --History:
19 -- 24-FEB-1997 L. de Werker Created
20 -- 06-OCT-97 jwhite - Updated as required per
21 -- unit testing.
22 -- 26-NOV-97 jwhite - Replaced calls to Set_Global_Info
23 -- with Set_Global_Attr to
24 -- drop AMG linkage.
25 --
26 -- IN
27 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
28 -- itemkey - A string generated from the application object's primary key.
29 -- actid - The notification process activity(instance id).
30 -- funcmode - Run/Cancel
31 -- OUT
32 -- Resultout - Approver User ID
33 --
34 PROCEDURE Select_Project_Approver ( itemtype in varchar2,
35 itemkey in varchar2,
36 actid in number,
37 funcmode in varchar2,
38 resultout out NOCOPY varchar2 ) --File.Sql.39 bug 4440895
39 IS
40 --
41 CURSOR l_approver_user_csr( p_approver_id NUMBER ) IS
42 SELECT f.user_id
43 , f.user_name
44 , e.first_name||' '||e.last_name
45 FROM fnd_user f
46 ,pa_employees e
47 WHERE f.employee_id = p_approver_id
48 AND f.employee_id = e.person_id;
49
50 l_err_code NUMBER := 0;
51 l_resp_id NUMBER;
52 l_project_id NUMBER;
53 l_workflow_started_by_id NUMBER;
54 l_approver_employee_id NUMBER;
55 l_approver_user_id NUMBER;
56 l_approver_user_name VARCHAR2(240);
57 l_approver_full_name VARCHAR2(400);/*UTF8-changed from varchsr2(240) to varchar2(400)*/
58 l_msg_count NUMBER;
59 l_msg_data VARCHAR(2000);
60 l_return_status VARCHAR2(1);
61 l_data VARCHAR(2000);
62 l_msg_index_out NUMBER;
63 l_api_version_number NUMBER := 1.0;
64
65
66
67 --
68 BEGIN
69 --
70 -- Return if WF Not Running
71 --
72 IF (funcmode <> wf_engine.eng_run) THEN
73 --
74 resultout := wf_engine.eng_null;
75 RETURN;
76 --
77 END IF;
78 --
79
80 l_resp_id := wf_engine.GetItemAttrNumber
81 (itemtype => itemtype,
82 itemkey => itemkey,
83 aname => 'RESPONSIBILITY_ID' );
84
85 l_project_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
86 itemkey => itemkey,
87 aname => 'PROJECT_ID' );
88 l_workflow_started_by_id :=
89 wf_engine.GetItemAttrNumber(itemtype => itemtype,
90 itemkey => itemkey,
91 aname => 'WORKFLOW_STARTED_BY_ID' );
92
93 -- Based on the Responsibility, Intialize the Application
94 PA_WORKFLOW_UTILS.Set_Global_Attr
95 (p_item_type => itemtype
96 , p_item_key => itemkey
97 , p_err_code => l_err_code);
98
99
100 PA_CLIENT_EXTN_PROJECT_WF.Select_Project_Approver
101 (p_project_id => l_project_id
102 , p_workflow_started_by_id => l_workflow_started_by_id
103 , p_project_approver_id => l_approver_employee_id
104 );
105
106
107 --ISSUE: a employee can have several users attached to it!!
108 -- Return True if an approver can be found
109 -- Else Return False
110
111 IF (l_approver_employee_id IS NOT NULL )
112 THEN
113 OPEN l_approver_user_csr( l_approver_employee_id );
114 FETCH l_approver_user_csr INTO
115 l_approver_user_id
116 ,l_approver_user_name
117 ,l_approver_full_name;
118 IF (l_approver_user_csr%FOUND)
119 THEN
120 CLOSE l_approver_user_csr;
121 wf_engine.SetItemAttrNumber
122 (itemtype => itemtype,
123 itemkey => itemkey,
124 aname => 'PROJECT_APPROVER_ID',
125 avalue => l_approver_user_id );
126 wf_engine.SetItemAttrText
127 (itemtype => itemtype,
128 itemkey => itemkey,
129 aname => 'PROJECT_APPROVER_NAME',
130 avalue => l_approver_user_name);
131 wf_engine.SetItemAttrText
132 (itemtype => itemtype,
133 itemkey => itemkey,
134 aname => 'PROJECT_APPROVER_FULL_NAME',
135 avalue => l_approver_full_name
136 );
137 resultout := wf_engine.eng_completed||':'||'T';
138 ELSE
139 CLOSE l_approver_user_csr;
140 resultout := wf_engine.eng_completed||':'||'F';
141 END IF;
142 ELSE
143 resultout := wf_engine.eng_completed||':'||'F';
144 END IF;
145
146 EXCEPTION
147
148 WHEN FND_API.G_EXC_ERROR
149 THEN
150 WF_CORE.CONTEXT('PA_PROJECT_WF','SELECT_PROJECT_APPROVER',itemtype, itemkey, to_char(actid), funcmode);
151 RAISE;
152
153 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
154 THEN
155 WF_CORE.CONTEXT('PA_PROJECT_WF','SELECT_PROJECT_APPROVER',itemtype, itemkey, to_char(actid), funcmode);
156 RAISE;
157
158 WHEN OTHERS
159 THEN
160 WF_CORE.CONTEXT('PA_PROJECT_WF','SELECT_PROJECT_APPROVER',itemtype, itemkey, to_char(actid), funcmode);
161 RAISE;
162
163
164
165 END Select_Project_Approver;
166
167 -- ====================================================
168
169 PROCEDURE Start_Project_Wf (p_project_id IN NUMBER,
170 p_err_stack IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
171 p_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
172 p_err_code OUT NOCOPY NUMBER) IS --File.Sql.39 bug 4440895
173
174 CURSOR l_get_projinfo_csr (l_project_id IN NUMBER) IS
175 SELECT
176 ps.workflow_item_type,
177 ps.workflow_process
178 FROM pa_projects_all pap, -- Bug#3807805 : Modified pa_projects to pa_projects_all
179 pa_project_statuses ps
180 WHERE pap.project_id = l_project_id
181 AND pap.project_status_code = ps.project_status_code;
182
183 l_get_projinfo_rec l_get_projinfo_csr%ROWTYPE;
184 l_out_item_key VARCHAR2(100);
185 l_err_code NUMBER := 0;
186 l_err_stack VARCHAR2(2000);
187 l_err_stage VARCHAR2(2000);
188
189 BEGIN
190
191 --dbms_output.put_line('START_PROJECT_WF - INSIDE');
192
193 OPEN l_get_projinfo_csr (p_project_id) ;
194 FETCH l_get_projinfo_csr INTO l_get_projinfo_rec;
195 IF l_get_projinfo_csr%NOTFOUND THEN
196 p_err_code := 10;
197 p_err_stage := 'PA_PROJECT_STATUS_INVALID';
198 CLOSE l_get_projinfo_csr;
199 END IF;
200 CLOSE l_get_projinfo_csr;
201
202 --dbms_output.put_line('CALL EXTN START_PROJECT_WF');
203
204 Pa_Client_Extn_Project_Wf.Start_Project_Wf
205 (p_project_id => p_project_id,
206 p_item_type => l_get_projinfo_rec.workflow_item_type,
207 p_process => l_get_projinfo_rec.workflow_process,
208 p_out_item_key => l_out_item_key,
209 p_err_stack => l_err_stack,
210 p_err_stage => l_err_stage,
211 p_err_code => l_err_code
212 );
213
214 --dbms_output.put_line('AFTER EXTN - l_err_code: '||to_char(l_err_code));
215
216 IF l_err_code = 0 THEN
217 PA_WORKFLOW_UTILS.Insert_WF_Processes
218 (p_wf_type_code => 'PROJECT'
219 ,p_item_type => l_get_projinfo_rec.workflow_item_type
220 ,p_item_key => l_out_item_key
221 ,p_entity_key1 => to_char(p_project_id)
222 ,p_description => NULL
223 ,p_err_code => l_err_code
224 ,p_err_stage => l_err_stage
225 ,p_err_stack => l_err_stack
226 );
227 END IF;
228
229 --dbms_output.put_line('AFTER INSERT_WF_PROCESSES');
230
231 EXCEPTION
232 WHEN OTHERS
233 THEN
234 p_err_code := SQLCODE;
235 WF_CORE.CONTEXT('PA_PROJECT_WF','START_PROJECT_WF');
236 RAISE;
237
238
239 END Start_Project_Wf;
240
241 -- ====================================================
242 --
243 -- History
244 -- 02-DEC-97 jwhite - For Set_Success_Status added
245 -- clauses to the update that
246 -- occurs if the verify was OK.
247 --
248
249 PROCEDURE Set_Success_status
250 (itemtype IN VARCHAR2
251 ,itemkey IN VARCHAR2
252 ,actid IN NUMBER
253 ,funcmode IN VARCHAR2
254 ,resultout OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
255 IS
256 l_err_code NUMBER := 0;
257 l_err_stage VARCHAR2(2000);
258 l_err_stack VARCHAR2(2000);
259 l_success_status_code VARCHAR2(30);
260 l_failure_status_code VARCHAR2(30);
261 l_project_id NUMBER := 0;
262 l_wf_enabled_flag VARCHAR2(1);
263 l_verify_ok_flag VARCHAR2(1);
264
265 /* Bug 2345889 Part 1- Begin*/
266 l_resp_id NUMBER ;
267 l_project_approver_id NUMBER ;
268 l_proj_stus_code PA_PROJECT_STATUSES.PROJECT_STATUS_CODE%TYPE;
269 l_proj_system_status_code PA_PROJECT_STATUSES.PROJECT_SYSTEM_STATUS_CODE%TYPE;
270 l_proj_success_sys_sts_code PA_PROJECT_STATUSES.PROJECT_SYSTEM_STATUS_CODE%TYPE;
271
272 CURSOR l_proj_system_status_csr(x_project_status_code PA_PROJECT_STATUSES.PROJECT_STATUS_CODE%TYPE) IS
273 SELECT project_system_status_code
274 FROM pa_project_statuses
275 WHERE project_status_code = x_project_status_code;
276 /* Bug 2345889 Part 1- End*/
277
278 l_msg_count NUMBER :=0; /*Bug 3611598*/
279 l_return_status VARCHAR2(2000);
280
281 -- 3671408 added cursor to retrieve project number
282
283 CURSOR c_project_details(p_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE) IS
284 SELECT segment1
285 FROM PA_PROJECTS_ALL
286 WHERE PROJECT_ID = p_project_id;
287
288 l_project_number pa_projects_all.segment1%type;
289
290 BEGIN
291
292 --
293 -- Return if WF Not Running
294 --
295 IF (funcmode <> wf_engine.eng_run) THEN
296 --
297 resultout := wf_engine.eng_null;
298 RETURN;
299 END IF;
300
301 Get_proj_status_attributes (x_item_type => itemtype,
302 x_item_key => itemkey,
303 x_success_proj_stus_code =>l_success_status_code,
304 x_failure_proj_stus_code =>l_failure_status_code,
305 x_err_code => l_err_code,
306 x_err_stage => l_err_stage
307 );
308
309 IF (l_err_code < 0)
310 THEN
311 WF_CORE.CONTEXT('PA_PROJECT_WF','GET_PROJ_STATUS_ATTRIBUTES',itemtype, itemkey, to_char(actid), funcmode);
312 RAISE FND_API.G_EXC_ERROR;
313 ELSIF (l_err_code > 0)
314 THEN
315 resultout := wf_engine.eng_completed||':'||'F';
316 WF_CORE.CONTEXT('PA_PROJECT_WF','GET_PROJ_STATUS_ATTRIBUTES',itemtype, itemkey, to_char(actid), funcmode);
317 PA_WORKFLOW_UTILS.Set_Notification_Messages
318 (p_item_type => itemtype
319 , p_item_key => itemkey
320 );
321 RETURN;
322 END IF;
323
324 /* Bug 2345889 Part 2- Begin */
325
326 l_resp_id := wf_engine.GetItemAttrNumber
327 (itemtype => itemtype,
328 itemkey => itemkey,
329 aname => 'RESPONSIBILITY_ID' );
330
331 l_project_approver_id := wf_engine.GetItemAttrNumber
332 (itemtype => itemtype,
333 itemkey => itemkey,
334 aname => 'PROJECT_APPROVER_ID');
335
336 l_proj_stus_code := wf_engine.GetItemAttrText
337 ( itemtype => itemtype,
338 itemkey => itemkey,
339 aname => 'PROJECT_STATUS_CODE' );
340
341 /* Adding the following check so that if approver is not null then user_id
342 should be set as the approver_id and not as workflow_started_by_id */
343 IF l_project_approver_id is NOT NULL THEN
344 FND_GLOBAL.Apps_Initialize
345 ( user_id => l_project_approver_id
346 , resp_id => l_resp_id
347 , resp_appl_id => pa_workflow_utils.get_application_id(l_resp_id)
348 );
349 ELSE
350 Pa_workflow_utils.Set_Global_Attr (p_item_type => itemtype,
351 p_item_key => itemkey,
352 p_err_code => l_err_code);
353 END IF;
354
355 OPEN l_proj_system_status_csr(l_proj_stus_code);
356 FETCH l_proj_system_status_csr INTO l_proj_system_status_code;
357 CLOSE l_proj_system_status_csr;
358
359 /* Note that for the above cursor call, no need of checking cursor
360 NOT FOUND or catching any exceptions because it is already
361 checked in Get_proj_status_attributes. If any error comes then
362 execution will not come to this point */
363
364 OPEN l_proj_system_status_csr(l_success_status_code);
365 FETCH l_proj_system_status_csr INTO l_proj_success_sys_sts_code;
366 CLOSE l_proj_system_status_csr;
367
368 /* l_success_status_code can be null, so no need of checking cursor NOT FOUND */
369
370 /* Bug 2345889 Part 2- End*/
371
372 l_project_id := wf_engine.GetItemAttrNumber
373 ( itemtype => itemtype,
374 itemkey => itemkey,
375 aname => 'PROJECT_ID');
376
377 Validate_Changes (x_project_id => l_project_id,
378 x_success_status_code => l_success_status_code,
379 x_err_code => l_err_code,
380 x_err_stage => l_err_stage,
381 x_wf_enabled_flag => l_wf_enabled_flag,
382 x_verify_ok_flag => l_verify_ok_flag );
383
384 -- Bug 7534431
385 IF l_proj_system_status_code = l_proj_success_sys_sts_code THEN
386 l_wf_enabled_flag := 'N';
387 END IF;
388
389 IF l_verify_ok_flag = 'Y' THEN
390 resultout := wf_engine.eng_completed||':'||'T';
391 IF l_wf_enabled_flag = 'Y' THEN
392
393 /* Bug 2345889 Part-3 Begin*/
394 /* Commenting out this code and adding the code below instead*/
395 /*
396 -- -------------------------------------------------------------------------------------
397 -- 02-DEC-97, jwhite: added wf_status_code and where clause
398 -- to this update.
399
400 UPDATE pa_projects
401 SET project_status_code = l_success_status_code,
402 wf_status_code = 'IN_ROUTE'
403 WHERE project_id = l_project_id;
404 -- -------------------------------------------------------------------------------------
405 */
406
407 IF l_proj_system_status_code <> 'CLOSED' THEN
408 IF l_proj_success_sys_sts_code = 'CLOSED' THEN
409 UPDATE pa_projects_all /* Bug#6367069 replaced PA_PROJECTS with PA_PROJECTS_ALL */
410 SET project_status_code = l_success_status_code,
411 wf_status_code = 'IN_ROUTE',
412 closed_date = sysdate,
413 last_update_date = sysdate,
414 last_updated_by = fnd_global.user_id,
415 last_update_login = fnd_global.login_id
416 WHERE project_id = l_project_id;
417 ELSE
418 UPDATE pa_projects_all /* Bug#6367069 replaced PA_PROJECTS with PA_PROJECTS_ALL */
419 SET project_status_code = l_success_status_code,
420 wf_status_code = 'IN_ROUTE',
421 last_update_date = sysdate,
422 last_updated_by = fnd_global.user_id,
423 last_update_login = fnd_global.login_id
424 WHERE project_id = l_project_id;
425 END IF;
426 ELSE
427 IF l_proj_success_sys_sts_code = 'CLOSED' THEN
428 UPDATE pa_projects_all /* Bug#6367069 replaced PA_PROJECTS with PA_PROJECTS_ALL */
429 SET project_status_code = l_success_status_code,
430 wf_status_code = 'IN_ROUTE',
431 last_update_date = sysdate,
432 last_updated_by = fnd_global.user_id,
433 last_update_login = fnd_global.login_id
434 WHERE project_id = l_project_id;
435 ELSE
436 UPDATE pa_projects_all /* Bug#6367069 replaced PA_PROJECTS with PA_PROJECTS_ALL */
437 SET project_status_code = l_success_status_code,
438 wf_status_code = 'IN_ROUTE',
439 closed_date = null,
440 last_update_date = sysdate,
441 last_updated_by = fnd_global.user_id,
442 last_update_login = fnd_global.login_id
443 WHERE project_id = l_project_id;
444 END IF;
445 END IF;
446 /* Bug 2345889 Part-3 End*/
447
448 Start_Project_Wf (p_project_id => l_project_id,
449 p_err_stack => l_err_stack,
450 p_err_stage => l_err_stage,
451 p_err_code => l_err_code );
452 ELSE
453 resultout := wf_engine.eng_completed||':'||'T';
454
455 /* Bug 2345889 Part-4 Begin*/
456 /* Commenting out this code and adding the code below instead*/
457 /*
458 UPDATE pa_projects
459 SET project_status_code = l_success_status_code,
460 wf_status_code = NULL
461 WHERE project_id = l_project_id;
462 */
463 IF l_proj_system_status_code <> 'CLOSED' THEN
464 IF l_proj_success_sys_sts_code = 'CLOSED' THEN
465 UPDATE pa_projects_all /* Bug#6367069 replaced PA_PROJECTS with PA_PROJECTS_ALL */
466 SET project_status_code = l_success_status_code,
467 wf_status_code = NULL,
468 closed_date = sysdate,
469 last_update_date = sysdate,
470 last_updated_by = fnd_global.user_id,
471 last_update_login = fnd_global.login_id
472 WHERE project_id = l_project_id;
473 ELSE
474 UPDATE pa_projects_all /* Bug#6367069 replaced PA_PROJECTS with PA_PROJECTS_ALL */
475 SET project_status_code = l_success_status_code,
476 wf_status_code = NULL,
477 last_update_date = sysdate,
478 last_updated_by = fnd_global.user_id,
479 last_update_login = fnd_global.login_id
480 WHERE project_id = l_project_id;
481 END IF;
482 ELSE
483 IF l_proj_success_sys_sts_code = 'CLOSED' THEN
484 UPDATE pa_projects_all /* Bug#6367069 replaced PA_PROJECTS with PA_PROJECTS_ALL */
485 SET project_status_code = l_success_status_code,
486 wf_status_code = NULL,
487 last_update_date = sysdate,
488 last_updated_by = fnd_global.user_id,
489 last_update_login = fnd_global.login_id
490 WHERE project_id = l_project_id;
491 ELSE
492 UPDATE pa_projects_all /* Bug#6367069 replaced PA_PROJECTS with PA_PROJECTS_ALL */
493 SET project_status_code = l_success_status_code,
494 wf_status_code = NULL,
495 closed_date = null,
496 last_update_date = sysdate,
497 last_updated_by = fnd_global.user_id,
498 last_update_login = fnd_global.login_id
499 WHERE project_id = l_project_id;
500 END IF;
501 END IF;
502 /* Bug 2345889 Part-4 End*/
503 END IF;
504
505 /* Stubbed out Auto-Initiate Demand On Project Approval Functionality
506 Bug 3819086 -Hence Commenting Out Following Code */
507
508 /* Bug 3611598 Place call to the wrapper API which will invoke Concurrent Process to Initiate Demand ,If the Project Status is Approved*/
509 /*
510 IF nvl(l_proj_system_status_code,'-99') = 'APPROVED' THEN
511
512 -- 3671408 Added below code to retrieve project number
513 OPEN c_project_details(l_project_id) ;
514 FETCH c_project_details INTO l_project_number ;
515 CLOSE c_project_details;
516
517 PA_Actions_Pub.RUN_ACTION_CONC_PROCESS_WRP
518 (
519 p_project_id => l_project_id
520 -- 3671408 added p_project_number IN parameter and passing retrieved project number
521 ,p_project_number => l_project_number
522 ,x_msg_count => l_msg_count
523 ,x_msg_data => l_err_stack
524 ,x_return_status => l_return_status
525 );
526 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
527 RAISE FND_API.G_EXC_ERROR;
528 END IF;
529 END IF;
530 End Commenting for Bug 3819086 */
531
532 /* Code Added for bug 7299466*/
533 DECLARE
534 l_rowid VARCHAR2(255);
535 l_obj_status_change_id NUMBER;
536 l_old_sys_status VARCHAR2(30);
537 l_new_sys_status VARCHAR2(30);
538 l_note VARCHAR2(2000);
539
540 CURSOR cur_get_system_status(c_status_code IN VARCHAR2) IS
541 SELECT pps.project_system_status_code
542 FROM pa_project_statuses pps
543 WHERE pps.project_status_code = nvl(c_status_code,' ');
544
545 BEGIN
546
547 l_note := wf_engine.GetItemAttrText( itemtype => itemtype,
548 itemkey => itemkey,
549 aname => 'NOTE' );
550
551 SELECT pa_obj_status_changes_s.NEXTVAL INTO l_obj_status_change_id
552 FROM dual;
553
554 OPEN cur_get_system_status(l_success_status_code);
555 FETCH cur_get_system_status INTO l_new_sys_status;
556 CLOSE cur_get_system_status;
557
558 OPEN cur_get_system_status(l_proj_stus_code);
559 FETCH cur_get_system_status INTO l_old_sys_status;
560 CLOSE cur_get_system_status;
561
562 --For inserting status change comment into the status history table
563 PA_OBJ_STATUS_CHANGES_PKG.INSERT_ROW
564 ( X_ROWID => l_rowid,
565 X_OBJ_STATUS_CHANGE_ID => l_obj_status_change_id,
566 X_OBJECT_TYPE => 'PA_PROJECTS',
567 X_OBJECT_ID => l_project_id,
568 X_STATUS_TYPE => 'PROJECT',
569 X_NEW_PROJECT_STATUS_CODE => l_success_status_code,
570 X_NEW_PROJECT_SYSTEM_STATUS_CO => l_new_sys_status,
571 X_OLD_PROJECT_STATUS_CODE => l_proj_stus_code,
572 X_OLD_PROJECT_SYSTEM_STATUS_CO => l_old_sys_status,
573 X_CHANGE_COMMENT => l_note,
574 X_LAST_UPDATED_BY => fnd_global.user_id,
575 X_CREATED_BY => fnd_global.user_id,
576 X_CREATION_DATE => sysdate,
577 X_LAST_UPDATE_DATE => sysdate,
578 X_LAST_UPDATE_LOGIN => fnd_global.user_id);
579
580 END;
581 /* End of code for 7299466*/
582
583 ELSE
584 resultout := wf_engine.eng_completed||':'||'F';
585 Wf_Status_failure (x_project_id => l_project_id,
586 x_failure_status_code => l_failure_status_code,
587 x_item_type => itemtype,
588 x_item_key => itemkey,
589 x_update_db_YN => 'Y',
590 x_populate_msg_yn => 'Y',
591 x_err_code => l_err_code );
592 END IF;
593
594
595 PA_WORKFLOW_UTILS.Set_Notification_Messages
596 (p_item_type => itemtype
597 , p_item_key => itemkey
598 );
599
600
601 EXCEPTION
602
603 WHEN FND_API.G_EXC_ERROR
604 THEN
605 WF_CORE.CONTEXT('PA_PROJECT_WF','SET_SUCCESS_STATUS',itemtype, itemkey, to_char(actid), funcmode);
606 RAISE;
607
608 WHEN OTHERS
609 THEN
610 WF_CORE.CONTEXT('PA_PROJECT_WF','SET_SUCCESS_STATUS',itemtype, itemkey, to_char(actid), funcmode);
611 RAISE;
612
613 END Set_Success_status;
614
615 PROCEDURE Set_Failure_status
616 (itemtype IN VARCHAR2
617 ,itemkey IN VARCHAR2
618 ,actid IN NUMBER
619 ,funcmode IN VARCHAR2
620 ,resultout OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
621
622 l_err_code NUMBER := 0;
623 l_success_status_code VARCHAR2(30);
624 l_failure_status_code VARCHAR2(30);
625 l_err_stage VARCHAR2(2000);
626 l_project_id NUMBER := 0;
627 BEGIN
628 -- Return if WF Not Running
629 --
630 IF(funcmode <> wf_engine.eng_run) THEN
631 --
632 resultout := wf_engine.eng_null;
633 RETURN;
634 END IF;
635 Get_proj_status_attributes (x_item_type => itemtype,
636 x_item_key => itemkey,
637 x_success_proj_stus_code =>l_success_status_code,
638 x_failure_proj_stus_code =>l_failure_status_code,
639 x_err_code => l_err_code,
640 x_err_stage => l_err_stage );
641
642 IF (l_err_code < 0)
643 -- Don't Check for positive error codes because there isn't any notification to display the
644 -- business rule messages.
645 THEN
646 WF_CORE.CONTEXT('PA_PROJECT_WF','GET_PROJ_STATUS_ATTRIBUTES',itemtype, itemkey, to_char(actid), funcmode);
647 RAISE FND_API.G_EXC_ERROR;
648 END IF;
649
650 Pa_workflow_utils.Set_Global_Attr (p_item_type => itemtype,
651 p_item_key => itemkey,
652 p_err_code => l_err_code);
653 l_project_id := wf_engine.GetItemAttrNumber
654 ( itemtype => itemtype,
655 itemkey => itemkey,
656 aname => 'PROJECT_ID');
657 -- Call wf status failure.We only need to update the database
658 -- No need to populate the message notifications
659
660 Wf_Status_failure (x_project_id => l_project_id,
661 x_failure_status_code => l_failure_status_code,
662 x_item_type => itemtype,
663 x_item_key => itemkey,
664 x_update_db_YN => 'Y',
665 x_populate_msg_yn => 'N',
666 x_err_code => l_err_code );
667 resultout := wf_engine.eng_completed||':'||'F';
668
669 /* Code Added for bug 7299466*/
670 DECLARE
671 l_rowid VARCHAR2(255);
672 l_obj_status_change_id NUMBER;
673 l_old_sys_status VARCHAR2(30);
674 l_new_sys_status VARCHAR2(30);
675 l_note VARCHAR2(2000);
676 l_proj_stus_code VARCHAR2(30);
677
678 CURSOR cur_get_system_status(c_status_code IN VARCHAR2) IS
679 SELECT pps.project_system_status_code
680 FROM pa_project_statuses pps
681 WHERE pps.project_status_code = nvl(c_status_code,' ');
682
683 BEGIN
684
685 l_note := wf_engine.GetItemAttrText( itemtype => itemtype,
686 itemkey => itemkey,
687 aname => 'NOTE' );
688
689 l_proj_stus_code := wf_engine.GetItemAttrText( itemtype => itemtype,
690 itemkey => itemkey,
691 aname => 'PROJECT_STATUS_CODE' );
692
693 SELECT pa_obj_status_changes_s.NEXTVAL INTO l_obj_status_change_id
694 FROM dual;
695
696 OPEN cur_get_system_status(l_failure_status_code);
697 FETCH cur_get_system_status INTO l_new_sys_status;
698 CLOSE cur_get_system_status;
699
700 OPEN cur_get_system_status(l_proj_stus_code);
701 FETCH cur_get_system_status INTO l_old_sys_status;
702 CLOSE cur_get_system_status;
703
704 --For inserting status change comment into the status history table
705 PA_OBJ_STATUS_CHANGES_PKG.INSERT_ROW
706 ( X_ROWID => l_rowid,
707 X_OBJ_STATUS_CHANGE_ID => l_obj_status_change_id,
708 X_OBJECT_TYPE => 'PA_PROJECTS',
709 X_OBJECT_ID => l_project_id,
710 X_STATUS_TYPE => 'PROJECT',
711 X_NEW_PROJECT_STATUS_CODE => l_failure_status_code,
712 X_NEW_PROJECT_SYSTEM_STATUS_CO => l_new_sys_status,
713 X_OLD_PROJECT_STATUS_CODE => l_proj_stus_code,
714 X_OLD_PROJECT_SYSTEM_STATUS_CO => l_old_sys_status,
715 X_CHANGE_COMMENT => l_note,
716 X_LAST_UPDATED_BY => fnd_global.user_id,
717 X_CREATED_BY => fnd_global.user_id,
718 X_CREATION_DATE => sysdate,
719 X_LAST_UPDATE_DATE => sysdate,
720 X_LAST_UPDATE_LOGIN => fnd_global.user_id);
721
722 END;
723 /* End of code for 7299466*/
724
725 EXCEPTION
726
727 WHEN FND_API.G_EXC_ERROR
728 THEN
729 WF_CORE.CONTEXT('PA_PROJECT_WF','SET_FAILURE_STATUS',itemtype, itemkey, to_char(actid), funcmode);
730 RAISE;
731
732 WHEN OTHERS
733 THEN
734 WF_CORE.CONTEXT('PA_PROJECT_WF','SET_FAILURE_STATUS',itemtype, itemkey, to_char(actid), funcmode);
735 RAISE;
736
737 END Set_Failure_status;
738
739 PROCEDURE Verify_status_change_rules
740 (itemtype IN VARCHAR2
741 ,itemkey IN VARCHAR2
742 ,actid IN NUMBER
743 ,funcmode IN VARCHAR2
744 ,resultout OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
745 l_err_code NUMBER := 0;
746 l_success_status_code VARCHAR2(30);
747 l_failure_status_code VARCHAR2(30);
748 l_err_stage VARCHAR2(2000);
749 l_project_id NUMBER := 0;
750 l_wf_enabled_flag VARCHAR2(1);
751 l_verify_ok_flag VARCHAR2(1);
752
753 BEGIN
754 -- Return if WF Not Running
755 --
756 IF(funcmode <> wf_engine.eng_run) THEN
757 --
758 resultout := wf_engine.eng_null;
759 RETURN;
760 --
761 END IF;
762
763 Get_proj_status_attributes (x_item_type => itemtype,
764 x_item_key => itemkey,
765 x_success_proj_stus_code =>l_success_status_code,
766 x_failure_proj_stus_code =>l_failure_status_code,
767 x_err_code => l_err_code,
768 x_err_stage => l_err_stage );
769
770 IF (l_err_code < 0)
771 THEN
772 WF_CORE.CONTEXT('PA_PROJECT_WF','GET_PROJ_STATUS_ATTRIBUTES',itemtype, itemkey, to_char(actid), funcmode);
773 RAISE FND_API.G_EXC_ERROR;
774 ELSIF (l_err_code > 0)
775 THEN
776 resultout := wf_engine.eng_completed||':'||'F';
777 WF_CORE.CONTEXT('PA_PROJECT_WF','GET_PROJ_STATUS_ATTRIBUTES',itemtype, itemkey, to_char(actid), funcmode);
778 PA_WORKFLOW_UTILS.Set_Notification_Messages
779 (p_item_type => itemtype
780 , p_item_key => itemkey
781 );
782 RETURN;
783 END IF;
784
785 Pa_workflow_utils.Set_Global_Attr (p_item_type => itemtype,
786 p_item_key => itemkey,
787 p_err_code => l_err_code);
788 l_project_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
789 itemkey => itemkey,
790 aname => 'PROJECT_ID'
791 );
792
793 Validate_Changes (x_project_id => l_project_id,
794 x_success_status_code => l_success_status_code,
795 x_err_code => l_err_code,
796 x_err_stage => l_err_stage,
797 x_wf_enabled_flag => l_wf_enabled_flag,
798 x_verify_ok_flag => l_verify_ok_flag );
799
800 IF l_verify_ok_flag = 'Y' THEN
801 resultout := wf_engine.eng_completed||':'||'T';
802 ELSE
803 resultout := wf_engine.eng_completed||':'||'F';
804 Wf_Status_failure (x_project_id => l_project_id,
805 x_failure_status_code => l_failure_status_code,
806 x_item_type => itemtype,
807 x_item_key => itemkey,
808 x_update_db_YN => 'N',
809 x_populate_msg_yn => 'Y',
810 x_err_code => l_err_code );
811 END IF;
812
813 EXCEPTION
814
815 WHEN FND_API.G_EXC_ERROR
816 THEN
817 WF_CORE.CONTEXT('PA_PROJECT_WF','VERIFY_STATUS_CHANGE_RULES',itemtype, itemkey, to_char(actid), funcmode);
818 RAISE;
819
820 WHEN OTHERS THEN
821 WF_CORE.CONTEXT('PA_PROJECT_WF','VERIFY_STATUS_CHANGE_RULES',itemtype, itemkey, to_char(actid), funcmode);
822 RAISE;
823
824 END Verify_status_change_rules;
825
826 PROCEDURE Wf_Status_failure (x_project_id IN NUMBER,
827 x_failure_status_code IN VARCHAR2,
828 x_item_type IN VARCHAR2,
829 x_item_key IN VARCHAR2,
830 x_populate_msg_yn IN VARCHAR2,
831 x_update_db_yn IN VARCHAR2,
832 x_err_code OUT NOCOPY NUMBER ) IS --File.Sql.39 bug 4440895
833 BEGIN
834 x_err_code := 0;
835 IF x_update_db_yn = 'Y' THEN
836 UPDATE pa_projects_all /* Bug#6367069 replaced PA_PROJECTS with PA_PROJECTS_ALL */
837 SET project_status_code = x_failure_status_code,
838 Wf_Status_code = NULL
839 WHERE project_id = x_project_id;
840
841 END IF;
842 IF x_populate_msg_yn = 'Y' THEN
843 pa_workflow_utils.set_notification_messages
844 (p_item_type => x_item_type,
845 p_item_key => x_item_key );
846 END IF; -- x_populate_msg_yn = 'Y'
847
848 EXCEPTION
849 WHEN OTHERS
850 THEN
851 WF_CORE.CONTEXT('PA_PROJECT_WF','WF_STATUS_FAILURE', x_item_type, x_item_key);
852 RAISE;
853
854 END Wf_Status_failure;
855
856
857 PROCEDURE Get_proj_status_attributes (x_item_type IN VARCHAR2,
858 x_item_key IN VARCHAR2,
859 x_success_proj_stus_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
860 x_failure_proj_stus_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
861 x_err_code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
862 x_err_stage OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
863 l_proj_stus_code VARCHAR2(30);
864
865 CURSOR l_proj_stus_csr IS
866 SELECT wf_success_status_code,
867 wf_failure_status_code
868 FROM pa_project_statuses
869 WHERE project_status_code = l_proj_stus_code;
870 BEGIN
871 x_err_code := 0;
872 x_success_proj_stus_code := NULL;
873 x_failure_proj_stus_code := NULL;
874 l_proj_stus_code := wf_engine.GetItemAttrText
875 ( itemtype => x_item_type,
876 itemkey => x_item_key,
877 aname => 'PROJECT_STATUS_CODE' );
878
879 IF l_proj_stus_code IS NOT NULL THEN
880 OPEN l_proj_stus_csr;
881 FETCH l_proj_stus_csr INTO
882 x_success_proj_stus_code,
883 x_failure_proj_stus_code;
884 IF l_proj_stus_csr%NOTFOUND THEN
885 CLOSE l_proj_stus_csr;
886 x_err_code := 10;
887 x_err_stage := 'PA_PROJECT_STATUS_INVALID';
888 PA_UTILS.Add_Message
889 ( p_app_short_name => 'PA'
890 , p_msg_name => 'PA_PROJECT_STATUS_INVALID'
891 );
892 RETURN;
893 ELSE
894 CLOSE l_proj_stus_csr;
895 END IF ;
896 ELSE
897 x_err_code := 10;
898 x_err_stage := 'PA_ITEM_ATTR_NOT_SET';
899 PA_UTILS.Add_Message
900 ( p_app_short_name => 'PA'
901 , p_msg_name => 'PA_ITEM_ATTR_NOT_SET'
902 );
903 RETURN;
904 END IF;
905
906 EXCEPTION
907 WHEN OTHERS
908 THEN
909 x_err_code := SQLCODE;
910 WF_CORE.CONTEXT('PA_PROJECT_WF','GET_PROJ_STATUS_ATTRIBUTES', x_item_type, x_item_key);
911 RAISE;
912
913 END Get_proj_status_attributes;
914
915 PROCEDURE validate_changes (x_project_id IN NUMBER,
916 x_success_status_code IN VARCHAR2,
917 x_err_code OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
918 x_err_stage OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
919 x_wf_enabled_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
920 x_verify_ok_flag OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
921
922 l_verify_ok_flag VARCHAR2(1);
923 l_wf_enabled_flag VARCHAR2(1);
924 l_err_stack VARCHAR2(2000);
925 l_err_msg_count NUMBER := 0;
926 l_warnings_only_flag VARCHAR2(1);
927 l_err_code NUMBER;
928 l_err_stage VARCHAR2(2000);
929
930
931 CURSOR l_project_csr IS
932 SELECT * FROM pa_projects_all /* Bug#6367069 replaced PA_PROJECTS with PA_PROJECTS_ALL */
933 WHERE project_id = x_project_id;
934 l_project_rec l_project_csr%ROWTYPE;
935
936 BEGIN
937 --dbms_output.put_line('VALIDATE_CHANGES - INSIDE');
938
939 OPEN l_project_csr;
940 FETCH l_project_csr INTO l_project_rec;
941 IF l_project_csr%NOTFOUND THEN
942 CLOSE l_project_csr;
943 x_err_code := 10;
944 x_err_stage := 'PA_PROJECT_ID_INVALID';
945 RETURN;
946 END IF;
947
948 CLOSE l_project_csr;
949
950 --dbms_output.put_line('CALL HANDLE_PROJECT_STATUS_CHANGE');
951
952
953 Pa_project_stus_utils.Handle_Project_Status_Change
954 (x_calling_module => 'PAPROJWF'
955 ,X_project_id => x_project_id
956 ,X_old_proj_status_code => l_project_rec.project_status_code
957 ,X_new_proj_status_code => x_success_status_code
958 ,X_project_type => l_project_rec.project_type
959 ,X_project_start_date => l_project_rec.start_date
960 ,X_project_end_date => l_project_rec.completion_date
961 ,X_public_sector_flag => l_project_rec.public_sector_flag
962 ,X_attribute_category => l_project_rec.attribute_category
963 ,X_attribute1 => l_project_rec.attribute1
964 ,X_attribute2 => l_project_rec.attribute2
965 ,X_attribute3 => l_project_rec.attribute3
966 ,X_attribute4 => l_project_rec.attribute4
967 ,X_attribute5 => l_project_rec.attribute5
968 ,X_attribute6 => l_project_rec.attribute6
969 ,X_attribute7 => l_project_rec.attribute7
970 ,X_attribute8 => l_project_rec.attribute8
971 ,X_attribute9 => l_project_rec.attribute9
972 ,X_attribute10 => l_project_rec.attribute10
973 ,X_pm_product_code => l_project_rec.pm_product_code
974 ,x_init_msg => 'Y'
975 ,x_verify_ok_flag => l_verify_ok_flag
976 ,x_wf_enabled_flag => l_wf_enabled_flag
977 ,X_err_stage => l_err_stage
978 ,X_err_stack => l_err_stack
979 ,x_err_msg_count => l_err_msg_count
980 ,x_warnings_only_flag => l_warnings_only_flag
981 );
982
983 --dbms_output.put_line('AFTER HANDLE_PROJECT_STATUS_CHANGE');
984
985
986 x_verify_ok_flag := l_verify_ok_flag;
987 x_wf_enabled_flag := l_wf_enabled_flag;
988
989 --dbms_output.put_line('LAST LINE OF VALIDATE_CHANGES');
990
991
992 EXCEPTION
993 WHEN OTHERS THEN
994 x_err_code := SQLCODE ;
995 WF_CORE.CONTEXT('PA_PROJECT_WF ','VALIDATE_CHANGES');
996 RAISE;
997
998 END Validate_Changes;
999
1000 END pa_project_wf;