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