[Home] [Help]
PACKAGE BODY: APPS.PA_CLIENT_EXTN_PROJECT_WF
Source
1 PACKAGE BODY pa_client_extn_project_wf AS
2 /* $Header: PAWFPCEB.pls 120.2.12010000.2 2008/09/17 06:56:41 sugupta ship $ */
3
4 -- ===================================================
5 --
6 --Name: Select_Project_Approver
7 --Type: Procedure
8 --Description: This client extension returns the project_approver ID
9 -- to the calling PA_PROJECT_WF Select_Project_Approver
10 -- procedure.
11 --
12 --
13 --Called subprograms: none.
14 --
15 --
16 --
17 --History:
18 -- 24-FEB-1997 L. de Werker - Created
19 -- 06-OCT-97 jwhite - Updated as required per
20 -- unit testing.
21 --
22 -- IN
23 -- p_project_id - unique identifier for the project that needs approval
24 -- p_workflow_started_by_id - identifies the user that triggered the workflow
25 --
26 -- OUT
27 -- p_project_approver_id - unique identifier of the employee
28 -- (employee_id in per_people_f table)
29 -- that must approve this project
30 --
31 PROCEDURE Select_Project_Approver
32 (p_project_id IN NUMBER
33 , p_workflow_started_by_id IN NUMBER
34 , p_project_approver_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
35 )
36 --
37 IS
38 /*
39 You can use this procedure to add any additional rules to determine
40 who can approve a project. This procedure is being used by the
41 Workflow APIs and determine who the approver for a project
42 should be. By default this procedure fetches the supervisor of the
43 person who initiated the workflow as the approver.
44 */
45 --
46 l_employee_id NUMBER;
47 --
48 CURSOR l_employee_csr IS
49 SELECT employee_id
50 FROM fnd_user
51 WHERE user_id = p_workflow_started_by_id;
52
53 CURSOR l_approver_csr IS
54 SELECT supervisor_id
55 FROM per_assignments_f
56 WHERE person_id = l_employee_id
57 /*AND Assignment_type ='E' -- Added this condition for bug 2911451*/ --Commented by avaithia Bug # 3448680
58 AND Assignment_type IN ('E','C') --Included 'C' also in Assignment_type Bug # 3448680
59 AND Primary_flag ='Y' -- Added this condition for bug 2911451
60 AND TRUNC(sysdate) BETWEEN EFFECTIVE_START_DATE
61 AND NVL(EFFECTIVE_END_DATE, sysdate);
62
63 BEGIN
64
65 OPEN l_employee_csr;
66 FETCH l_employee_csr INTO l_employee_id;
67 CLOSE l_employee_csr;
68 IF l_employee_id IS NOT NULL THEN
69 OPEN l_approver_csr;
70 FETCH l_approver_csr INTO p_project_approver_id;
71 CLOSE l_approver_csr;
72 END IF;
73
74
75 --
76 --The following algorithm can be used to handle known error conditions
77 --When this code is used the arguments and there values will be displayed
78 --in the error message that is send by workflow.
79 --
80 --IF <error condition>
81 --THEN
82 -- WF_CORE.TOKEN('ARG1', arg1);
83 -- WF_CORE.TOKEN('ARGn', argn);
84 -- WF_CORE.RAISE('ERROR_NAME');
85 --END IF;
86
87 EXCEPTION
88
89 WHEN OTHERS THEN
90 WF_CORE.CONTEXT('PA_CLIENT_EXTN_PROJECT_WF ','SELECT_PROJECT_APPROVER');
91 RAISE;
92
93 END Select_Project_Approver;
94
95 -- ===================================================
96 --Name: Start_Project_WF
97 --Type: Procedure
98 --Description: This procedure instantiates the Project workflow.
99 --
100 --
101 --Called Subprograms: none.
102 --
103 -- Notes:
104 --
105
106 -- History:
107 -- XX-AUTUMN-97rkrishna - Created.
108 --
109 -- 28-OCT-97 jwhite - Updated as per latest WF
110 -- standards.
111 -- 30-OCT-97 jwhite - Added project_status_successs_code
112 -- as a WF attribute.
113 -- 03-NOV-97 jwhite - Added workflow-started-date
114 -- to Start_Project_WF procedure.
115 -- 25-NOV-97 jwhite - Replaced call to set_global_info
116 -- with FND_GLOBAL.Apps_Initialize
117 -- to drop linkage to AMG license
118 -- Did NOT replace with Set_Global_Attr
119 -- because WF NOT exist yet.
120 -- 17-NOV-08 sugupta - Bug 6720288 : Added handling for the
121 -- item attribute NOTE which corresponds
122 -- to the change comment
123 --
124
125
126 PROCEDURE Start_Project_Wf (p_project_id IN NUMBER
127 , p_item_type IN VARCHAR2
128 , p_process IN VARCHAR2
129 , p_out_item_key OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
130 , p_err_stack OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
131 , p_err_stage OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
132 , p_err_code OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
133 , p_status_type IN VARCHAR2 DEFAULT 'PROJECT') IS
134
135
136 -- !!!THIS EXTENSION IS CALLED FROM either a
137 -- straight PL/SQL package or WORKFLOW!!!
138 --
139 --
140 -- This procedure starts the workflow process for project status changes
141 -- Do not add/delete/modify the parameters to this procedure
142 -- If you need to use a different item_type and process to start the
143 -- workflow, please use the Project Statuses form to specify that information
144 -- against the relevant project status
145 -- Any new item type attributes you may add,need to be populated
146 -- using SetItemAttr calls. Make sure that you have a thorough understanding
147 -- of the Oracle Workflow product and how to use PL/SQL with Workflow.
148
149 CURSOR l_project_csr ( l_project_id NUMBER )
150 IS
151 SELECT pm_project_reference
152 , segment1
153 , name
154 , description
155 , project_type
156 , pm_product_code
157 , carrying_out_organization_id
158 , project_status_code
159 , template_flag -- Bug 6875403
160 FROM pa_projects
161 WHERE project_id = l_project_id;
162 --
163 CURSOR l_organization_csr ( l_carrying_out_organization_id NUMBER )
164 IS
165 SELECT name
166 FROM hr_organization_units
167 WHERE organization_id = l_carrying_out_organization_id;
168 --
169 CURSOR l_project_type_class( l_project_type VARCHAR2)
170 IS
171 SELECT project_type_class_code
172 FROM pa_project_types
173 WHERE project_type = l_project_type;
174 --
175 CURSOR l_starter_name_csr( l_starter_user_id NUMBER )
176 IS
177 SELECT user_name
178 FROM fnd_user
179 WHERE user_id = l_starter_user_id;
180
181 -- 5078716 For R12 Performance Fix, changed cursor select query
182 -- Removed pa_employee view usage with base tables , the query
183 -- to retrieve name is taken from function
184 -- PA_RESOURCE_UTILS.get_person_name_no_date
185 /*
186 CURSOR l_starter_full_name_csr(l_starter_user_id NUMBER )
187 IS
188 SELECT e.first_name||' '||e.last_name
189 FROM fnd_user f, pa_employees e
190 WHERE f.user_id = l_starter_user_id
191 AND f.employee_id = e.person_id;
192 */
193
194 CURSOR l_starter_full_name_csr(l_starter_user_id NUMBER )
195 IS
196 SELECT e.first_name||' '||e.last_name
197 FROM fnd_user f, per_all_people_f e
198 WHERE f.user_id = l_starter_user_id
199 AND f.employee_id = e.person_id
200 and e.effective_end_date = ( SELECT
201 MAX(papf.effective_end_date)
202 FROM per_all_people_f papf
203 WHERE papf.person_id = e.person_id);
204
205 -- 5078716 end
206
207 -- Project Status Codes and Name Cursors ----------------------------------------
208
209 -- Get Success and Failure Status codes for Current Project Status Code
210 CURSOR l_project_status_csr(l_project_status_code VARCHAR2)
211 IS
212 SELECT project_status_name
213 , wf_success_status_code
214 , wf_failure_status_code
215 FROM pa_project_statuses
216 WHERE project_status_code = l_project_status_code;
217
218 -- Get Success Status Name
219 CURSOR l_wf_success_status_name_csr(l_wf_success_status_code VARCHAR2)
220 IS
221 SELECT project_status_name
222 FROM pa_project_statuses
223 WHERE project_status_code = l_wf_success_status_code;
224
225 -- Get Failure Status Name
226 CURSOR l_wf_failure_status_name_csr(l_wf_failure_status_code VARCHAR2)
227 IS
228 SELECT project_status_name
229 FROM pa_project_statuses
230 WHERE project_status_code = l_wf_failure_status_code;
231 -- -------------------------------------------------------------------------------------
232
233 -- Get System Date for Worflow-Started-Date
234 CURSOR l_wf_started_date_csr
235 IS
236 SELECT sysdate
237 FROM sys.dual;
238
239 -- Get the change comment
240 CURSOR l_change_comment_csr(l_project_id NUMBER)
241 IS
242 SELECT change_comment FROM (
243 SELECT change_comment
244 FROM pa_obj_status_changes
245 WHERE object_type = 'PA_PROJECTS'
246 AND object_id = l_project_id
247 AND new_project_status_code =
248 (SELECT project_status_code
249 FROM pa_projects_all
250 WHERE project_id = l_project_id)
251 ORDER BY obj_status_change_id DESC
252 )
253 WHERE rownum = 1;
254
255
256 --
257
258 --
259 ItemKey varchar2(30);
260 ItemType varchar2(30);
261
262 l_pm_project_reference varchar2(30);
263 l_pa_project_number varchar2(30);
264 l_project_name varchar2(30);
265 l_description varchar2(250);
266 l_project_type varchar2(20);
267 l_pm_product_code varchar2(30);
268 l_carrying_out_org_id number;
269 /* Bug No:- 2487147, UTF8 change : changed l_carrying_out_org_name to %TYPE */
270 /* l_carrying_out_org_name varchar2(60); */
271 l_carrying_out_org_name hr_organization_units.name%TYPE;
272 l_project_type_class_code varchar2(30);
273
274 l_project_status_code pa_project_statuses.project_status_code%TYPE;
275 l_wf_success_status_code pa_project_statuses.project_status_code%TYPE;
276 l_wf_failure_status_code pa_project_statuses.project_status_code%TYPE;
277
278 l_project_status_name pa_project_statuses.project_status_name%TYPE;
279 l_wf_success_status_name pa_project_statuses.project_status_name%TYPE;
280 l_wf_failure_status_name pa_project_statuses.project_status_name%TYPE;
281
282 l_wf_started_date DATE;
283
284
285 l_workflow_started_by_id number;
286 l_user_full_name varchar(400); /* Bug no. 2487147:- UTF8 changes: changed the length of l_user_full_name from 240 to 400 */
287 l_user_name varchar(240);
288 l_resp_id number;
289
290 l_msg_count NUMBER;
291 l_msg_data VARCHAR(2000);
292 l_return_status VARCHAR2(1);
293 l_api_version_number NUMBER := 1.0;
294 l_data VARCHAR2(2000);
295 l_msg_index_out NUMBER;
296
297 l_change_comment pa_obj_status_changes.change_comment%TYPE;
298
299 --
300 --
301
302 l_url VARCHAR2(2000); --Bug 6875403
303 l_template_flag VARCHAR2(1); --Bug 6875403
304
305 BEGIN
306
307 p_err_code := 0;
308 --get the unique identifier for this specific workflow
309
310 ItemType := p_item_type;
311
312 SELECT pa_workflow_itemkey_s.nextval
313 INTO itemkey
314 from dual;
315
316 -- Need this to populate the attribute information in Workflow
317 l_workflow_started_by_id := FND_GLOBAL.user_id;
318 l_resp_id := FND_GLOBAL.resp_id;
319
320 --dbms_output.put_line('set_global_info');
321
322 -- Based on the Responsibility, Intialize the Application
323 -- Cannot use Set_Global_Attr here because the WF
324 -- Does NOT exits yet.
325 FND_GLOBAL.Apps_Initialize
326 (user_id => l_workflow_started_by_id
327 , resp_id => l_resp_id
328 , resp_appl_id => fnd_global.resp_appl_id
329 );
330
331 -- Create the workflow process
332 --dbms_output.put_line('wf_engine.CreateProcess');
333
334 wf_engine.CreateProcess( ItemType => ItemType,
335 ItemKey => ItemKey,
336 process => p_process
337 );
338
339 p_out_item_key := ItemKey;
340 OPEN l_starter_name_csr(l_workflow_started_by_id );
341 FETCH l_starter_name_csr INTO l_user_name;
342 CLOSE l_starter_name_csr;
343
344 OPEN l_starter_full_name_csr(l_workflow_started_by_id );
345 FETCH l_starter_full_name_csr INTO l_user_full_name;
346 CLOSE l_starter_full_name_csr;
347
348 OPEN l_project_csr(p_project_id);
349 FETCH l_project_csr INTO l_pm_project_reference
350 ,l_pa_project_number
351 ,l_project_name
352 ,l_description
353 ,l_project_type
354 ,l_pm_product_code
355 ,l_carrying_out_org_id
356 ,l_project_status_code
357 ,l_template_flag; -- Bug 6875403
358 CLOSE l_project_csr;
359
360 OPEN l_organization_csr( l_carrying_out_org_id );
361 FETCH l_organization_csr INTO l_carrying_out_org_name;
362 CLOSE l_organization_csr;
363
364 OPEN l_project_type_class( l_project_type );
365 FETCH l_project_type_class INTO l_project_type_class_code;
366 CLOSE l_project_type_class;
367
368 OPEN l_project_status_csr(l_project_status_code);
369 FETCH l_project_status_csr INTO l_project_status_name
370 , l_wf_success_status_code
371 , l_wf_failure_status_code;
372 CLOSE l_project_status_csr;
373
374 OPEN l_wf_success_status_name_csr(l_wf_success_status_code);
375 FETCH l_wf_success_status_name_csr INTO l_wf_success_status_name;
376 CLOSE l_wf_success_status_name_csr;
377
378 OPEN l_wf_failure_status_name_csr(l_wf_failure_status_code);
379 FETCH l_wf_failure_status_name_csr INTO l_wf_failure_status_name;
380 CLOSE l_wf_failure_status_name_csr;
381
382 OPEN l_wf_started_date_csr;
383 FETCH l_wf_started_date_csr INTO l_wf_started_date;
384 CLOSE l_wf_started_date_csr;
385
386
387 OPEN l_change_comment_csr(p_project_id);
388 FETCH l_change_comment_csr INTO l_change_comment;
389 CLOSE l_change_comment_csr;
390
391 -- -----------------------------------------------------------------------------------
392 -- Initialize Workflow Item Attributes
393 -- -----------------------------------------------------------------------------------
394
395 wf_engine.SetItemAttrNumber (itemtype => itemtype,
396 itemkey => itemkey,
397 aname => 'PROJECT_ID',
398 avalue => p_project_id
399 );
400
401 wf_engine.SetItemAttrText (itemtype => itemtype,
402 itemkey => itemkey,
403 aname => 'PM_PROJECT_REFERENCE',
404 avalue => l_pm_project_reference
405 );
406
407 wf_engine.SetItemAttrText (itemtype => itemtype,
408 itemkey => itemkey,
409 aname => 'PA_PROJECT_NUMBER',
410 avalue => l_pa_project_number
411 );
412
413 wf_engine.SetItemAttrText (itemtype => itemtype,
414 itemkey => itemkey,
415 aname => 'PROJECT_NAME',
416 avalue => l_project_name
417 );
418
419 wf_engine.SetItemAttrText (itemtype => itemtype,
420 itemkey => itemkey,
421 aname => 'PROJECT_DESCRIPTION',
422 avalue => l_description
423 );
424
425 wf_engine.SetItemAttrText (itemtype => itemtype,
426 itemkey => itemkey,
427 aname => 'PROJECT_TYPE',
428 avalue => l_project_type
429 );
430
431 wf_engine.SetItemAttrText (itemtype => itemtype,
432 itemkey => itemkey,
433 aname => 'PM_PROJECT_PRODUCT_CODE',
434 avalue => l_pm_product_code
435 );
436
437 wf_engine.SetItemAttrNumber (itemtype => itemtype,
438 itemkey => itemkey,
439 aname => 'CARRYING_OUT_ORG_ID',
440 avalue => l_carrying_out_org_id
441 );
442
443 wf_engine.SetItemAttrText (itemtype => itemtype,
444 itemkey => itemkey,
445 aname => 'CARRYING_OUT_ORG_NAME',
446 avalue => l_carrying_out_org_name
447 );
448
449 wf_engine.SetItemAttrText (itemtype => itemtype,
450 itemkey => itemkey,
451 aname => 'PROJECT_TYPE_CLASS_CODE',
452 avalue => l_project_type_class_code
453 );
454
455 wf_engine.SetItemAttrText (itemtype => itemtype,
456 itemkey => itemkey,
457 aname => 'NOTE',
458 avalue => l_change_comment
459 );
460
461 wf_engine.SetItemAttrNumber (itemtype => itemtype,
462 itemkey => itemkey,
463 aname => 'WORKFLOW_STARTED_BY_ID',
464 avalue => l_workflow_started_by_id
465 );
466
467 wf_engine.SetItemAttrText (itemtype => itemtype,
468 itemkey => itemkey,
469 aname => 'WORKFLOW_STARTED_BY_NAME',
470 avalue => l_user_name
471 );
472
473 wf_engine.SetItemAttrText (itemtype => itemtype,
474 itemkey => itemkey,
475 aname =>
476 'WORKFLOW_STARTED_BY_FULL_NAME',
477 avalue => l_user_full_name
478 );
479
480 wf_engine.SetItemAttrNumber (itemtype => itemtype,
481 itemkey => itemkey,
482 aname => 'RESPONSIBILITY_ID',
483 avalue => l_resp_id
484 );
485
486 wf_engine.SetItemAttrText (itemtype => itemtype,
487 itemkey => itemkey,
488 aname => 'PROJECT_STATUS_CODE',
489 avalue => l_project_status_code
490 );
491
492 wf_engine.SetItemAttrText (itemtype => itemtype,
493 itemkey => itemkey,
494 aname => 'PROJECT_STATUS_NAME',
495 avalue => l_project_status_name
496 );
497
498 wf_engine.SetItemAttrText (itemtype => itemtype,
499 itemkey => itemkey,
500 aname => 'WF_SUCCESS_STATUS_CODE',
501 avalue => l_wf_success_status_code
502 );
503
504
505 wf_engine.SetItemAttrText (itemtype => itemtype,
506 itemkey => itemkey,
507 aname => 'WF_SUCCESS_STATUS_NAME',
508 avalue => l_wf_success_status_name
509 );
510
511 wf_engine.SetItemAttrText (itemtype => itemtype,
512 itemkey => itemkey,
513 aname => 'WF_FAILURE_STATUS_CODE',
514 avalue => l_wf_failure_status_code
515 );
516
517
518 wf_engine.SetItemAttrText (itemtype => itemtype,
519 itemkey => itemkey,
520 aname => 'WF_FAILURE_STATUS_NAME',
521 avalue => l_wf_failure_status_name
522 );
523
524 wf_engine.SetItemAttrText (itemtype => itemtype,
525 itemkey => itemkey,
526 aname => 'WF_STARTED_DATE',
527 avalue => l_wf_started_date
528 );
529
530
531 --Bug 6875403
532 If (nvl(l_template_flag,'N') = 'N') Then
533 l_url := 'JSP:/OA_HTML/OA.jsp?OAFunc=PA_PROJ_HOME'
534 ||'&'||'paProjectId='||p_project_id
535 ||'&'||'addBreadCrumb=Y';
536 Else
537 l_url := 'JSP:/OA_HTML/OA.jsp?OAFunc=PA_PAXPREPR_TEMPLATE_SS'
538 ||'&'||'paProjectId='||p_project_id
539 ||'&'||'addBreadCrumb=Y';
540 End If;
541
542
543 wf_engine.SetItemAttrText( itemtype
544 , itemkey
545 , 'PROJECT_SSWA_URL'
546 , l_url
547 );
548
549 --Bug 6875403
550
551
552 --dbms_output.put_line('wf_engine.StartProcess');
553 --
554 wf_engine.StartProcess( itemtype => itemtype,
555 itemkey => itemkey );
556 --
557
558 EXCEPTION
559
560 WHEN FND_API.G_EXC_ERROR
561 THEN
562 WF_CORE.CONTEXT('PA_CLIENT_EXTN_PROJECT_WF ','START_PROJECT_WF', itemtype, itemkey);
563 RAISE;
564
565
566 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
567 THEN
568 p_err_code := SQLCODE;
569 WF_CORE.CONTEXT('PA_CLIENT_EXTN_PROJECT_WF ','START_PROJECT_WF', itemtype, itemkey);
570 RAISE;
571
572 WHEN OTHERS
573 THEN
574 p_err_code := SQLCODE;
575 WF_CORE.CONTEXT('PA_CLIENT_EXTN_PROJECT_WF ','START_PROJECT_WF', itemtype, itemkey);
576 RAISE;
577
578 END Start_Project_Wf;
579 -- ====================================================
580 END pa_client_extn_project_wf;