1 package body PA_WORKPLAN_WORKFLOW_CLIENT as
2 /*$Header: PAXSTWCB.pls 120.8.12010000.2 2008/10/13 07:11:12 vgovvala ship $*/
3
4 /*=================================================================
5
6 Name: START_WORKFLOW
7 Type: Procedure
8 Description: This API has been created for initialization.
9 This is a Client Extension provided to the customer
10 to add new variables and initialize them before
11 the workflow process begins.
12
13 IN:
14 p_item_type --The internal name for the item type. Item types
15 are defined in the Oracle Workflow Builder.
16 p_item_key --A string that represents a primary key generated
17 by the workflow-enabled application for the item
18 type. The string uniquely identifies the item
19 within an item type.
20 actid --The ID number of the activity from which this
21 procedure is called.
22 funcmode --The execution mode of the activity. If the activity
23 is a function activity, the mode is either 'RUN' or
24 'CANCEL'. If the activity is a notification activity,
25 with a post-notification function, then the mode can
26 be 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT', or
27 'RUN'. Other execution modes may be added in the
28 future.
29
30 OUT:
31 resultout --If a result type is specified in the Activities
32 properties page for the activity in the Oracle
33 Workflow Builder, this parameter represents the
34 expected result that is returned when the procedure
35 completes.
36 =================================================================*/
37 procedure START_WORKFLOW
38 (
39 p_item_type IN VARCHAR2
40 ,p_item_key IN VARCHAR2
41 ,p_process_name IN VARCHAR2
42 ,p_structure_version_id IN NUMBER
43 ,p_responsibility_id IN NUMBER
44 ,p_user_id IN NUMBER
45 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
46 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
47 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
48 )
49 IS
50 l_structure_ver_name VARCHAR2(240);
51 l_structure_ver_num NUMBER;
52 l_structure_ver_desc VARCHAR2(250);
53 l_locked_by_person_id NUMBER;
54 l_project_id NUMBER;
55 l_project_name VARCHAR2(30);
56 l_project_num VARCHAR2(25);
57 l_record_version_num NUMBER;
58 l_submitter_role VARCHAR2(30);
59 l_submitter_role_users VARCHAR2(300);
60
61 display_name VARCHAR2(2000);
62 email_address VARCHAR2(2000);
63 notification_preference VARCHAR2(2000);
64 language VARCHAR2(2000);
65 territory VARCHAR2(2000);
66 -- Added for Bug fix: 4537865
67 l_new_submitter_role VARCHAR2(30);
68 -- Added for Bug fix: 4537865
69
70 CURSOR getStructureVerInfo IS
71 select a.VERSION_NUMBER, a.NAME, a.PROJECT_ID, a.LOCKED_BY_PERSON_ID,
72 a.record_version_number, a.description
73 from pa_proj_elem_ver_structure a,
74 pa_proj_element_versions b
75 where p_structure_version_id = b.element_version_id
76 and b.project_id = a.project_id
77 and b.element_version_id = a.element_version_id;
78
79 CURSOR getProjectInfo(c_project_id NUMBER) IS
80 select a.name, a.segment1
81 from pa_projects_all a
82 where c_project_id = a.project_id;
83
84 --Cursor for selecting submitter
85 -- 4586987 customer_id changed to person_party_id
86 /*
87 CURSOR getSubmitter IS
88 select usr.user_id, usr.customer_id, usr.user_name, papf.email_address,
89 papf.full_name person_name
90 from per_all_people_f papf, fnd_user usr
91 where papf.person_id = usr.employee_id
92 and trunc(sysdate) between papf.effective_start_date
93 and nvl(papf.effective_end_date, sysdate+1)
94 and trunc(sysdate) between usr.start_date
95 and nvl(usr.end_date, sysdate+1)
96 and usr.user_id = l_locked_by_person_id;
97 */
98
99 -- Bug 6826760 - Replaced l_locked_by_person_id with p_user_id.
100 CURSOR getSubmitter IS
101 SELECT usr.user_id, usr.person_party_id, usr.user_name, papf.email_address, --customer_id is replaced with person_party_id
102 papf.full_name person_name
103 FROM per_all_people_f papf, fnd_user usr
104 WHERE papf.person_id = usr.employee_id
105 AND trunc(sysdate) between papf.effective_start_date
106 AND nvl(papf.effective_end_date, sysdate+1)
107 AND trunc(sysdate) between usr.start_date
108 AND nvl(usr.end_date, sysdate+1)
109 AND usr.user_id = p_user_id;
110
111 -- End of bug Number 4586987
112
113 BEGIN
114 x_return_status := FND_API.G_RET_STS_SUCCESS;
115
116 OPEN getStructureVerInfo;
117 FETCH getStructureVerInfo INTO l_structure_ver_num, l_structure_ver_name, l_project_id, l_locked_by_person_id, l_record_version_num, l_structure_ver_desc;
118 CLOSE getStructureVerInfo;
119
120 --set structure_ver_name
121 wf_engine.SetItemAttrText(p_item_type, p_item_key,
122 'STRUCTURE_VER_NAME',l_structure_ver_name);
123
124 --set structure_ver_number
125 wf_engine.SetItemAttrText(p_item_type, p_item_key,
126 'STRUCTURE_VER_NUM', to_char(l_structure_ver_num));
127
128 --set structure_ver_id
129 wf_engine.SetItemAttrNumber(p_item_type, p_item_key,
130 'STRUCTURE_VER_ID',p_structure_version_id);
131 wf_engine.SetItemAttrText(p_item_type, p_item_key,
132 'STRUCTURE_VER_ID_T',to_char(p_structure_version_id));
133
134 --set structure_ver_name
135 wf_engine.SetItemAttrText(p_item_type, p_item_key,
136 'STRUCTURE_VER_DESC',l_structure_ver_desc);
137
138 --set record_version_number
139 wf_engine.SetItemAttrNumber(p_item_type, p_item_key,
140 'RECORD_VERSION_NUMBER',l_record_version_num);
141
142
143 OPEN getProjectInfo(l_project_id);
144 FETCH getProjectInfo into l_project_name, l_project_num;
145 CLOSE getProjectInfo;
146
147 --set project_name
148 wf_engine.SetItemAttrText(p_item_type, p_item_key,
149 'PROJECT_NAME', l_project_name);
150 --set project_num
151 wf_engine.SetItemAttrText(p_item_type, p_item_key,
152 'PROJECT_NUM', l_project_num);
153 --set project_id
154 wf_engine.SetItemAttrNumber(p_item_type, p_item_key,
155 'PROJECT_ID', l_project_id);
156
157 --set responsibility_id
158 wf_engine.SetItemAttrNUMBER(p_item_type, p_item_key,
159 'RESPONSIBILITY_ID', p_responsibility_id);
160
161 --set user_id
162 wf_engine.SetItemAttrNUMBER(p_item_type, p_item_key,
163 'USER_ID', p_user_id);
164
165 --set workplan_submitter
166 l_submitter_role := 'SUBMITBY_'||p_item_type||p_item_key;
167
168 -- Bug 6826760 (Initialize l_new_submitter_role)
169 l_new_submitter_role := l_submitter_role;
170
171 WF_DIRECTORY.CreateAdHocRole(role_name => l_submitter_role
172 --,role_display_name => l_submitter_role * commented for Bug Fix: 4537865
173 ,role_display_name => l_new_submitter_role -- added for Bug: 4537865
174 ,expiration_date => sysdate+1); -- Set an expiration date for bug#5962410
175 -- added for Bug: 4537865
176 l_submitter_role := l_new_submitter_role;
177 -- added for Bug: 4537865
178
179 l_submitter_role_users := NULL;
180 FOR v_submitter in getSubmitter LOOP
181 IF (l_submitter_role_users IS NOT NULL) THEN
182 l_submitter_role_users := l_submitter_role_users||',';
183 END IF;
184
185 WF_DIRECTORY.GetRoleInfo(v_submitter.user_name,
186 display_name,
187 email_address,
188 notification_preference,
189 language,
190 territory);
191 IF display_name is NULL then
192
193 WF_DIRECTORY.CreateAdHocUser(name => v_submitter.user_name
194 ,display_name => v_submitter.person_name
195 ,email_address => v_submitter.email_address);
196 END IF;
197 -- Bug 6826760
198 l_submitter_role_users := l_submitter_role_users || v_submitter.user_name;
199
200 END LOOP;
201
202 IF (l_submitter_role_users IS NOT NULL) THEN
203 wf_directory.addUsersToAdHocRole(l_submitter_role,
204 l_submitter_role_users);
205 wf_engine.setItemAttrText(p_item_type
206 ,p_item_key
207 ,'WORKPLAN_SUBMITTER'
208 ,l_submitter_role);
209 END IF;
210 EXCEPTION
211 WHEN OTHERS THEN
212 x_msg_count :=1;
213 x_msg_data:= substr(SQLERRM, 1, 2000);
214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
215 RAISE;
216 END START_WORKFLOW;
217
218
219 /*=================================================================
220
221 Name: SELECT_APPROVER
222 Type: Procedure
223 Description: This API has been created for selecting an approver.
224 This is a Client Extension provided to the customer
225 to modify the default approver when the approver is
226 not specified.
227
228 IN:
229 p_item_type --The internal name for the item type. Item types
230 are defined in the Oracle Workflow Builder.
231 p_item_key --A string that represents a primary key generated
232 by the workflow-enabled application for the item
233 type. The string uniquely identifies the item
234 within an item type.
235 actid --The ID number of the activity from which this
236 procedure is called.
237 funcmode --The execution mode of the activity. If the activity
238 is a function activity, the mode is either 'RUN' or
239 'CANCEL'. If the activity is a notification activity,
240 with a post-notification function, then the mode can
241 be 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT', or
242 'RUN'. Other execution modes may be added in the
243 future.
244
245 OUT:
246 resultout --If a result type is specified in the Activities
247 properties page for the activity in the Oracle
248 Workflow Builder, this parameter represents the
249 expected result that is returned when the procedure
250 completes.
251 =================================================================*/
252
253 procedure SELECT_APPROVER
254 (
255 p_item_type IN VARCHAR2
256 ,p_item_key IN VARCHAR2
257 ,actid IN NUMBER
258 ,funcmode IN VARCHAR2
259 ,resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
260 )
261 IS
262 l_project_id NUMBER;
263 l_submitted_by_id NUMBER;
264 l_approver_source_id NUMBER;
265 l_approval_role VARCHAR2(30);
266 l_approval_role_users VARCHAR2(300);
267 l_source_id NUMBER;
268 l_source_type NUMBER;
269
270 /* Commented the following cursor and modified as below for bug# 3732090
271 CURSOR getApprover IS
272 select wp_approver_source_id, wp_approver_source_type
273 from pa_proj_workplan_attr
274 where project_id = l_project_id; */
275
276 CURSOR getApprover IS
277 select wp.wp_approver_source_id, wp.wp_approver_source_type, p1.full_name
278 from pa_proj_workplan_attr wp, per_all_people_f p1
279 where wp.project_id = l_project_id
280 and p1.person_id = wp.wp_approver_source_id
281 and trunc(sysdate) between trunc(p1.effective_start_date)
282 and NVL(p1.effective_end_date, sysdate);
283 /* Changes ended for bug# 3732090*/
284
285 /* Modify this cursor to select the default approver when
286 the approver is not specified */
287 CURSOR getProjectManagerHR IS
288 select fu.user_name, p1.supervisor_id person_id, p2.full_name person_name,
289 p2.email_address
290 from per_assignments_f p1, per_all_people_f p2,
291 fnd_user fu, pa_project_parties p
292 where p.project_id = l_project_id
293 and p.project_role_id = 1
294 and TRUNC(sysdate) between p.START_DATE_ACTIVE
295 and NVL(p.END_DATE_ACTIVE, sysdate+1)
296 -- and p1.assignment_type = 'E' /* Bug#2911451 */ -- Commented by avaithia for Bug 3448680
297 and p1.assignment_type in ('E','C') -- Included By avaithia for Bug 3448680
298 and p1.primary_flag = 'Y' /* Bug#2911451 */
299 and p.resource_source_id = p1.person_id
300 and p1.supervisor_id = p2.person_id
301 and trunc(sysdate) between p1.effective_start_date
302 and p1.effective_end_date
303 and trunc(sysdate) between p2.effective_start_date
304 and NVL(p2.effective_end_date, sysdate)
305 and fu.employee_id = p1.supervisor_id;
306
307 CURSOR l_approver_csr_person IS
308 select distinct fu.user_id, fu.user_name, papf.email_address,
309 papf.full_name person_name
310 from fnd_user fu, per_all_people_f papf
311 where fu.employee_id = l_source_id
312 and papf.person_id = fu.employee_id
313 and trunc(sysdate) between papf.EFFECTIVE_START_DATE
314 and Nvl(papf.effective_end_date, Sysdate + 1)
315 and trunc(sysdate) between fu.START_DATE and nvl(fu.END_DATE, sysdate+1);
316
317 -- 4586987 customer_id changed to person_party_id
318 /*
319 CURSOR l_approver_csr_party IS
320 select distinct fu.user_id, fu.user_name, papf.email_address,
321 papf.full_name person_name
322 from fnd_user fu,per_all_people_f papf
323 where fu.customer_id = l_source_id
324 and papf.person_id = fu.employee_id
325 and trunc(sysdate) between papf.EFFECTIVE_START_DATE
326 and Nvl(papf.effective_end_date, Sysdate + 1)
327 and trunc(sysdate) between fu.START_DATE
328 and nvl(fu.END_DATE, sysdate+1);
329 */
330 CURSOR l_approver_csr_party IS
331 select distinct fu.user_id, fu.user_name, papf.email_address,
332 papf.full_name person_name
333 from fnd_user fu,per_all_people_f papf
334 where fu.person_party_id = l_source_id --reference is changed from customer_id to person_party_id
335 and papf.person_id = fu.employee_id
336 and trunc(sysdate) between papf.EFFECTIVE_START_DATE
337 and Nvl(papf.effective_end_date, Sysdate + 1)
338 and trunc(sysdate) between fu.START_DATE
339 and nvl(fu.END_DATE, sysdate+1);
340
341 -- 4586987 end
342
343 display_name VARCHAR2(2000);
344 email_address VARCHAR2(2000);
345 notification_preference VARCHAR2(2000);
346 language VARCHAR2(2000);
347 territory VARCHAR2(2000);
348 l_approver_name per_all_people_f.full_name%type; -- Added for bug# 3732090
349 BEGIN
350 /* get stored values */
351 l_project_id := wf_engine.getItemAttrNumber(
352 itemtype => p_item_type
353 ,itemkey => p_item_key
354 ,aname => 'PROJECT_ID');
355
356 l_submitted_by_id := wf_engine.getItemAttrNumber(
357 itemtype => p_item_type
358 ,itemkey => p_item_key
359 ,aname => 'SUBMITTED_BY_ID');
360
361 l_approval_role := 'APVR_'||p_item_type||p_item_key;
362
363 /* Create role */
364 /* Commented the following call to CreateAdHocRole and moved down with modifications for bug# 3732090
365 wf_directory.CreateAdHocRole(role_name => l_approval_role
366 ,role_display_name => l_approval_role
367 ,expiration_date => NULL);
368 */
369
370 /* get approver */
371 OPEN getApprover;
372 /* Commented the following fetch statement and modified as below for bug# 3732090
373 FETCH getApprover into l_source_id, l_source_type;*/
374 FETCH getApprover into l_source_id, l_source_type, l_approver_name;
375 /* Changes ended for bug# 3732090 */
376 IF (l_source_id IS NULL) or (l_source_type IS NULL) or getApprover%NOTFOUND THEN
377 --No approver has been specified.
378 --Default cursor will obtain the project manager's HR manager
379 --Modify getProjectManagerHR cursor to obtain desired user as
380 --default approver.
381 l_approval_role_users := NULL;
382
383 For v_approvers in getProjectManagerHR LOOP
384 IF (l_approval_role_users IS NOT NULL) THEN
385 l_approval_role_users := l_approval_role_users || ',';
386 END IF;
387 WF_DIRECTORY.GetRoleInfo(v_approvers.user_name,
388 display_name,
389 email_address,
390 notification_preference,
391 language,
392 territory);
393 IF display_name is NULL THEN
394 --Add user to directory
395 WF_DIRECTORY.createAdHocUser(name => v_approvers.user_name
396 ,display_name => v_approvers.person_name
397 ,email_address => v_approvers.email_address);
398 END IF;
399 l_approval_role_users := l_approval_role_users ||v_approvers.user_name;
400 l_approver_name := v_approvers.person_name; -- Added for bug# 3732090
401 END LOOP;
402 ELSE
403 --Approver has been specified.
404 l_approval_role_users := NULL;
405 --get approver
406 IF (l_source_type = 101) THEN
407 --Get internal users
408 FOR v_approvers IN l_approver_csr_person LOOP
409 IF (l_approval_role_users IS NOT NULL) THEN
410 l_approval_role_users := l_approval_role_users || ',';
411 END IF;
412 WF_DIRECTORY.GetRoleInfo(v_approvers.user_name,
413 display_name,
414 email_address,
415 notification_preference,
416 language,
417 territory);
418
419 IF display_name IS NULL THEN
420 --Add user to directory
421 WF_DIRECTORY.createAdHocUser(name => v_approvers.user_name
422 ,display_name => v_approvers.person_name
423 ,email_address => v_approvers.email_address);
424 END IF;
425 l_approval_role_users := l_approval_role_users ||v_approvers.user_name;
426 END LOOP;
427 ELSIF (l_source_type = 112) THEN
428 --Get external users
429 FOR v_approvers IN l_approver_csr_party LOOP
430 IF (l_approval_role_users IS NOT NULL) THEN
431 l_approval_role_users := l_approval_role_users || ',';
432 END IF;
433 WF_DIRECTORY.GetRoleInfo(v_approvers.user_name,
434 display_name,
435 email_address,
436 notification_preference,
437 language,
438 territory);
439 IF display_name IS NULL THEN
440 --Add user to directory
441 WF_DIRECTORY.createAdHocUser(name => v_approvers.user_name
442 ,display_name => v_approvers.person_name
443 ,email_address => v_approvers.email_address);
444 END IF;
445 l_approval_role_users := l_approval_role_users ||v_approvers.user_name;
446 END LOOP;
447 END IF;
448 END IF;
449 CLOSE getApprover;
450 /* Added the following call to CreateAdHocRole for bug# 3732090 */
451 wf_directory.CreateAdHocRole(role_name => l_approval_role
452 ,role_display_name => l_approver_name -- Modifed the parameter from l_approval_role to l_approver_name bug#3732090
453 ,expiration_date => sysdate+1); -- Set an expiration date for bug#5962410
454
455 IF (l_approval_role_users IS NOT NULL) THEN
456 --Add the selected user(s) to the role
457 WF_DIRECTORY.ADDUSERSTOADHOCROLE(l_approval_role,
458 l_approval_role_users);
459 WF_engine.setItemAttrText(p_item_type,
460 p_item_key,
461 'WORKPLAN_APPROVER',
462 l_approval_role);
463 resultout := wf_engine.eng_completed||':'||'T';
464 ELSE
465 resultout := wf_engine.eng_completed||':'||'F';
466 END IF;
467 EXCEPTION
468 WHEN OTHERS THEN
469 RAISE;
470 END SELECT_APPROVER;
471
472 /*=================================================================
473
474 Name: SET_NOTIFICATION_PARTY
475 Type: Procedure
476 Description: This API has been created for selecting the notifying
477 party when the workplan has been approved or rejected.
478 This is a Client Extension provided to the customer
479 to modify the default receiver of the notifications
480
481 IN:
482 p_item_type --The internal name for the item type. Item types
483 are defined in the Oracle Workflow Builder.
484 p_item_key --A string that represents a primary key generated
485 by the workflow-enabled application for the item
486 type. The string uniquely identifies the item
487 within an item type.
488 actid --The ID number of the activity from which this
489 procedure is called.
490 funcmode --The execution mode of the activity. If the activity
491 is a function activity, the mode is either 'RUN' or
492 'CANCEL'. If the activity is a notification activity,
493 with a post-notification function, then the mode can
494 be 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT', or
495 'RUN'. Other execution modes may be added in the
496 future.
497
498 OUT:
499 resultout --If a result type is specified in the Activities
500 properties page for the activity in the Oracle
501 Workflow Builder, this parameter represents the
502 expected result that is returned when the procedure
503 completes.
504 =================================================================*/
505 procedure set_notification_party
506 (
507 p_item_type IN VARCHAR2
508 ,p_item_key IN VARCHAR2
509 ,p_status_code IN VARCHAR2
510 ,actid IN NUMBER
511 ,funcmode IN VARCHAR2
512 ,resultout OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
513 )
514 IS
515 l_project_id NUMBER;
516 l_structure_version_id NUMBER;
517 --This cursor is used when the workplan is approved or rejected.
518 --This cursor returns all project members who has the edit privilege.
519 --This cursor returns internal users.
520 CURSOR getApprovedRejectedPerson IS
521 select distinct fu.user_id, fu.user_name, papf.email_address,
522 papf.full_name person_name
523 from fnd_user fu, per_all_people_f papf, pa_project_parties ppp,
524 pa_project_role_types pprt
525 where object_type = 'PA_PROJECTS'
526 --and object_id = l_project_id Bug 4562762
527 -- Bug 4562762 : Added logic to select program too
528 and object_id IN (select ver.project_id
529 from pa_object_relationships obj
530 , pa_proj_element_versions ver
531 where obj.object_id_to1=l_structure_version_id
532 and obj.relationship_type = 'LW'
533 and obj.object_id_from1=ver.element_version_id
534 union
535 select l_project_id
536 from dual)
537 and ppp.resource_type_id = 101
538 and ppp.project_role_id = pprt.project_role_id
539 and ppp.resource_source_id = fu.employee_id
540 and papf.person_id = fu.employee_id
541 and trunc(sysdate) between papf.effective_start_date
542 and nvl(papf.effective_end_date, sysdate+1)
543 and trunc(sysdate) between fu.start_date
544 and nvl(fu.end_date, sysdate+1)
545 and trunc(sysdate) between ppp.start_date_active
546 and nvl(ppp.end_date_active, sysdate+1)
547 and pprt.menu_id IN (select f1.menu_id
548 from fnd_compiled_menu_functions f1, fnd_form_functions f2
549 where (f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR')
550 and f2.function_id = f1.function_id)
551 UNION /*Added this clause for Approver 4291185 */
552 select distinct fu.user_id, fu.user_name, papf.email_address,
553 papf.full_name person_name
554 from fnd_user fu, per_all_people_f papf
555 where fu.user_id = fnd_global.USER_ID
556 and papf.person_id = fu.employee_id
557 and trunc(sysdate) between papf.effective_start_date
558 and nvl(papf.effective_end_date, sysdate+1)
559 and trunc(sysdate) between fu.start_date
560 and nvl(fu.end_date, sysdate+1) ;
561
562 --This cursor is used when the workplan is approved or rejected.
563 --This cursor returns all project members who has the edit privilege.
564 --This cursor returns external users or companies.
565 -- 4586987 customer_id changed to person_party_id
566 /*
567 CURSOR getApprovedRejectedParty IS
568 select distinct fu.user_id, fu.user_name, papf.email_address,
569 papf.full_name person_name
570 from fnd_user fu, per_all_people_f papf, pa_project_parties ppp,
571 pa_project_role_types pprt
572 where object_type = 'PA_PROJECTS'
573 and object_id = l_project_id
574 and ppp.resource_type_id = 112
575 and ppp.project_role_id = pprt.project_role_id
576 and ppp.resource_id = fu.customer_id
577 and papf.person_id = fu.employee_id
578 and trunc(sysdate) between papf.effective_start_date
579 and nvl(papf.effective_end_date, sysdate+1)
580 and trunc(sysdate) between fu.start_date
581 and nvl(fu.end_date, sysdate+1)
582 and trunc(sysdate) between ppp.start_date_active
583 and nvl(ppp.end_date_active, sysdate+1)
584 and pprt.menu_id IN (select f1.menu_id
585 from fnd_compiled_menu_functions f1, fnd_form_functions f2
586 where (f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR')
587 and f2.function_id = f1.function_id);
588 */
589
590 CURSOR getApprovedRejectedParty IS
591 select distinct fu.user_id, fu.user_name, papf.email_address,
592 papf.full_name person_name
593 from fnd_user fu, per_all_people_f papf, pa_project_parties ppp,
594 pa_project_role_types pprt
595 where object_type = 'PA_PROJECTS'
596 and object_id = l_project_id
597 and ppp.resource_type_id = 112
598 and ppp.project_role_id = pprt.project_role_id
599 and ppp.resource_id = fu.person_party_id -- customer_id is changed to person_party_id
600 and papf.person_id = fu.employee_id
601 and trunc(sysdate) between papf.effective_start_date
602 and nvl(papf.effective_end_date, sysdate+1)
603 and trunc(sysdate) between fu.start_date
604 and nvl(fu.end_date, sysdate+1)
605 and trunc(sysdate) between ppp.start_date_active
606 and nvl(ppp.end_date_active, sysdate+1)
607 and pprt.menu_id IN (select f1.menu_id
608 from fnd_compiled_menu_functions f1, fnd_form_functions f2
609 where (f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR')
610 and f2.function_id = f1.function_id);
611
612 -- 4586987 end
613
614 --This cursor is used when the workplan is published.
615 --This cursor returns all project members and task managers
616 --who has the view privilege.
617 --This cursor returns internal users.
618 --Bug No 3695601 Performance Fix Using EXISTS instead of IN
619 /* CURSOR getWorkplanViewerPerson IS
620 select distinct fu.user_id, fu.user_name, papf.email_address,
621 papf.full_name person_name
622 from fnd_user fu, per_all_people_f papf, pa_project_parties ppp,
623 pa_project_role_types pprt
624 where object_type = 'PA_PROJECTS'
625 and object_id = l_project_id
626 and ppp.resource_type_id = 101
627 and ppp.project_role_id = pprt.project_role_id
628 and ppp.resource_source_id = fu.employee_id
629 and papf.person_id = fu.employee_id
630 and trunc(sysdate) between papf.effective_start_date
631 and nvl(papf.effective_end_date, sysdate+1)
632 and trunc(sysdate) between fu.start_date
633 and nvl(fu.end_date, sysdate+1)
634 and trunc(sysdate) between ppp.start_date_active
635 and nvl(ppp.end_date_active, sysdate+1)
636 and pprt.menu_id IN (select f1.menu_id
637 from fnd_compiled_menu_functions f1, fnd_form_functions f2
638 where (f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR'
639 or f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR_V')
640 and f2.function_id = f1.function_id)
641 UNION
642 select distinct fu.user_id, fu.user_name, papf.email_address,
643 papf.full_name person_name
644 from fnd_user fu, per_all_people_f papf, pa_proj_elements ele
645 where ele.project_id = l_project_id
646 and ele.MANAGER_PERSON_ID = fu.employee_id
647 and papf.person_id = fu.employee_id
648 and trunc(sysdate) between papf.effective_start_date
649 and nvl(papf.effective_end_date, sysdate+1)
650 and trunc(sysdate) between fu.start_date
651 and nvl(fu.end_date, sysdate+1);*/
652
653 CURSOR getWorkplanViewerPerson IS
654 select distinct fu.user_id, fu.user_name, papf.email_address,
655 papf.full_name person_name
656 from fnd_user fu, per_all_people_f papf, pa_project_parties ppp,
657 pa_project_role_types pprt
658 where object_type = 'PA_PROJECTS'
659 and object_id = l_project_id
660 and ppp.resource_type_id = 101
661 and ppp.project_role_id = pprt.project_role_id
662 and ppp.resource_source_id = fu.employee_id
663 and papf.person_id = fu.employee_id
664 and trunc(sysdate) between papf.effective_start_date
665 and nvl(papf.effective_end_date, sysdate+1)
666 and trunc(sysdate) between fu.start_date
667 and nvl(fu.end_date, sysdate+1)
668 and trunc(sysdate) between ppp.start_date_active
669 and nvl(ppp.end_date_active, sysdate+1)
670 and EXISTS (select f1.menu_id
671 from fnd_compiled_menu_functions f1, fnd_form_functions f2
672 where (f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR'
673 or f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR_V')
674 and f2.function_id = f1.function_id)
675 UNION
676 select distinct fu.user_id, fu.user_name, papf.email_address,
677 papf.full_name person_name
678 from fnd_user fu, per_all_people_f papf, pa_proj_elements ele
679 where ele.project_id = l_project_id
680 and ele.MANAGER_PERSON_ID = fu.employee_id
681 and papf.person_id = fu.employee_id
682 and trunc(sysdate) between papf.effective_start_date
683 and nvl(papf.effective_end_date, sysdate+1)
684 and trunc(sysdate) between fu.start_date
685 and nvl(fu.end_date, sysdate+1)
686 UNION
687 select distinct fu.user_id, fu.user_name, papf.email_address,
688 papf.full_name person_name
689 from fnd_user fu, per_all_people_f papf
690 where fu.user_id = fnd_global.USER_ID
691 and papf.person_id = fu.employee_id
692 and trunc(sysdate) between papf.effective_start_date
693 and nvl(papf.effective_end_date, sysdate+1)
694 and trunc(sysdate) between fu.start_date
695 and nvl(fu.end_date, sysdate+1);
696
697
698 --This cursor is used when the workplan is published.
699 --This cursor returns all project members who has the view privilege.
700 --This cursor returns external users or companies.
701 -- 4586987 customer_id changed to person_party_id in fnd_user table
702 /*
703 CURSOR getWorkplanViewerParty IS
704 select distinct fu.user_id, fu.user_name, papf.email_address,
705 papf.full_name person_name
706 from fnd_user fu, per_all_people_f papf, pa_project_parties ppp,
707 pa_project_role_types pprt
708 where object_type = 'PA_PROJECTS'
709 and object_id = l_project_id
710 and ppp.resource_type_id = 112
711 and ppp.project_role_id = pprt.project_role_id
712 and ppp.resource_id = fu.customer_id
713 and papf.person_id = fu.employee_id
714 and trunc(sysdate) between papf.effective_start_date
715 and nvl(papf.effective_end_date, sysdate+1)
716 and trunc(sysdate) between fu.start_date
717 and nvl(fu.end_date, sysdate+1)
718 and trunc(sysdate) between ppp.start_date_active
719 and nvl(ppp.end_date_active, sysdate+1)
720 and pprt.menu_id IN (select f1.menu_id
721 from fnd_compiled_menu_functions f1, fnd_form_functions f2
722 where (f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR'
723 or f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR_V')
724 and f2.function_id = f1.function_id);
725 */
726
727 CURSOR getWorkplanViewerParty IS
728 select distinct fu.user_id, fu.user_name, papf.email_address,
729 papf.full_name person_name
730 from fnd_user fu, per_all_people_f papf, pa_project_parties ppp,
731 pa_project_role_types pprt
732 where object_type = 'PA_PROJECTS'
733 and object_id = l_project_id
734 and ppp.resource_type_id = 112
735 and ppp.project_role_id = pprt.project_role_id
736 and ppp.resource_id = fu.person_party_id -- customer_id changed to person_party_id in fnd_user table
737 and papf.person_id = fu.employee_id
738 and trunc(sysdate) between papf.effective_start_date
739 and nvl(papf.effective_end_date, sysdate+1)
740 and trunc(sysdate) between fu.start_date
741 and nvl(fu.end_date, sysdate+1)
742 and trunc(sysdate) between ppp.start_date_active
743 and nvl(ppp.end_date_active, sysdate+1)
744 and pprt.menu_id IN (select f1.menu_id
745 from fnd_compiled_menu_functions f1, fnd_form_functions f2
746 where (f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR'
747 or f2.function_name = 'PA_PAXPREPR_OPT_WORKPLAN_STR_V')
748 and f2.function_id = f1.function_id);
749 -- 4586987 end
750
751 l_appr_rej_role VARCHAR2(30);
752 l_appr_rej_role_usr VARCHAR2(300);
753 l_wp_viewer_role VARCHAR2(30);
754 -- Commented for Bug 5561154 (Base bug 5559025) l_wp_viewer_role_usr VARCHAR2(300);
755 -- Added for Bug 5561154 (Base bug 5559025)
756 l_wp_viewer_role_usr VARCHAR2(4000);
757
758 display_name VARCHAR2(2000);
759 email_address VARCHAR2(2000);
760 notification_preference VARCHAR2(2000);
761 language VARCHAR2(2000);
762 territory VARCHAR2(2000);
763 BEGIN
764 --Need to set the followings
765 -- APPROVED_REJECTED_RECEIVER
766 -- WORKPLAN_VIEWER
767 l_project_id := wf_engine.GetItemAttrNumber(
768 itemtype => p_item_type,
769 itemkey => p_item_key,
770 aname => 'PROJECT_ID');
771
772 l_structure_version_id := wf_engine.GetItemAttrNumber(
773 itemtype => p_item_type,
774 itemkey => p_item_key,
775 aname => 'STRUCTURE_VER_ID');
776
777 l_appr_rej_role := 'APRJ_'||p_item_type||p_item_key;
778 WF_DIRECTORY.CREATEADHOCROLE(role_name => l_appr_rej_role
779 ,role_display_name => l_appr_rej_role
780 ,expiration_date => sysdate+1); -- Set an expiration date for bug#5962410
781
782 --For approved_rejected_receiver
783 --Get internal users
784 l_appr_rej_role_usr := NULL;
785
786 FOR v_appr_rej IN getApprovedRejectedPerson LOOP
787 IF (l_appr_rej_role_usr IS NOT NULL) THEN
788 l_appr_rej_role_usr := l_appr_rej_role_usr||',';
789 END IF;
790 WF_DIRECTORY.GetRoleInfo(v_appr_rej.user_name,
791 display_name,
792 email_address,
793 notification_preference,
794 language,
795 territory);
796
797 IF display_name IS NULL THEN
798 --Add user to directory
799 wf_directory.createadhocuser( name => v_appr_rej.user_name
800 ,display_name => v_appr_rej.person_name
801 ,email_address => v_appr_rej.email_address);
802 END IF;
803 l_appr_rej_role_usr := l_appr_rej_role_usr||v_appr_rej.user_name;
804 END LOOP;
805
806 --Get external user
807 FOR v_appr_rej IN getApprovedRejectedParty LOOP
808 IF (l_appr_rej_role_usr IS NOT NULL) THEN
809 l_appr_rej_role_usr := l_appr_rej_role_usr||',';
810 END IF;
811 WF_DIRECTORY.GetRoleInfo(v_appr_rej.user_name,
812 display_name,
813 email_address,
814 notification_preference,
815 language,
816 territory);
817
818 IF display_name IS NULL THEN
819 --Add user to directory
820 wf_directory.createadhocuser( name => v_appr_rej.user_name
821 ,display_name => v_appr_rej.person_name
822 ,email_address => v_appr_rej.email_address);
823 END IF;
824 l_appr_rej_role_usr := l_appr_rej_role_usr||v_appr_rej.user_name;
825 END LOOP;
826
827 IF (l_appr_rej_role_usr IS NOT NULL) THEN
828 --Add the selected user(s) to the role
829 wf_directory.adduserstoadhocRole(l_appr_rej_role
830 ,l_appr_rej_role_usr);
831 wf_engine.setitemattrtext(p_item_type
832 ,p_item_key
833 ,'APPROVED_REJECTED_RECEIVER'
834 ,l_appr_rej_role);
835 END IF;
836
837 --For workplan_viewer
838 --Get internal user
839 l_wp_viewer_role := 'WPVR_'||p_item_type||p_item_key;
840 WF_DIRECTORY.CREATEADHOCROLE(role_name => l_wp_viewer_role
841 ,role_display_name => l_wp_viewer_role
842 ,expiration_date => sysdate+1); -- Set an expiration date for bug#5962410
843
844 l_wp_viewer_role_usr := NULL;
845 FOR v_wp_vr IN getWorkplanViewerPerson LOOP
846 IF (l_wp_viewer_role_usr IS NOT NULL) THEN
847 l_wp_viewer_role_usr := l_wp_viewer_role_usr||',';
848 END IF;
849 WF_DIRECTORY.GetRoleInfo(v_wp_vr.user_name,
850 display_name,
851 email_address,
852 notification_preference,
853 language,
854 territory);
855
856 IF display_name IS NULL THEN
857 --Add user to directory
858 wf_directory.createadhocuser( name => v_wp_vr.user_name
859 ,display_name => v_wp_vr.person_name
860 ,email_address => v_wp_vr.email_address);
861 END IF;
862 l_wp_viewer_role_usr := l_wp_viewer_role_usr||v_wp_vr.user_name;
863 END LOOP;
864
865 --Get external user
866 FOR v_wp_vr IN getWorkplanViewerParty LOOP
867 IF (l_wp_viewer_role_usr IS NOT NULL) THEN
868 l_wp_viewer_role_usr := l_wp_viewer_role_usr||',';
869 END IF;
870 WF_DIRECTORY.GetRoleInfo(v_wp_vr.user_name,
871 display_name,
872 email_address,
873 notification_preference,
874 language,
875 territory);
876
877 IF display_name IS NULL THEN
878 --Add user to directory
879 wf_directory.createadhocuser( name => v_wp_vr.user_name
880 ,display_name => v_wp_vr.person_name
881 ,email_address => v_wp_vr.email_address);
882 END IF;
883 l_wp_viewer_role_usr := l_wp_viewer_role_usr||v_wp_vr.user_name;
884 END LOOP;
885
886 IF (l_wp_viewer_role_usr IS NOT NULL) THEN
887 --Add the selected user(s) to the role
888 wf_directory.adduserstoadhocRole(l_wp_viewer_role
889 ,l_wp_viewer_role_usr);
890 wf_engine.setitemattrtext(p_item_type
891 ,p_item_key
892 ,'WORKPLAN_VIEWER'
893 ,l_wp_viewer_role);
894 END IF;
895
896 END set_notification_party;
897
898
899 procedure show_workplan_preview
900 (document_id IN VARCHAR2,
901 display_type IN VARCHAR2,
902 document IN OUT NOCOPY clob, --File.Sql.39 bug 4440895
903 document_type IN OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
904
905 IS
906
907 l_content clob;
908
909 CURSOR get_workplan_preview_info IS
910 SELECT page_content
911 FROM PA_PAGE_CONTENTS
912 WHERE pk1_value = document_id
913 AND object_type = 'PA_STRUCTURES'
914 AND pk2_value IS NULL;
915
916
917 l_size number;
918
919 l_chunk_size pls_integer:=10000;
920 l_copy_size int;
921 l_pos int := 0;
922
923 l_line varchar2(30000) := '' ; -- Changed the length from 10000 to 30000 for bug 3795807
924
925 -- Bug 3861540
926 l_return_status varchar2(1);
927 l_msg_count number;
928 l_msg_data varchar2(2000);
929
930 BEGIN
931
932 open get_workplan_preview_info;
933 fetch get_workplan_preview_info into l_content;
934 IF (get_workplan_preview_info%FOUND) THEN
935 close get_workplan_preview_info;
936
937 -- parse the retrieved clob data
938 l_size := dbms_lob.getlength(l_content);
939
940
941 l_pos := 1;
942 l_copy_size := 0;
943
944 while (l_copy_size < l_size) loop
945
946 dbms_lob.read(l_content,l_chunk_size,l_pos,l_line);
947
948 dbms_lob.write(document,l_chunk_size,l_pos,l_line);
949 l_copy_size := l_copy_size + l_chunk_size;
950 l_pos := l_pos + l_chunk_size;
951 end loop;
952
953 --Bug 3861540
954 pa_workflow_utils.modify_wf_clob_content(
955 p_document => document
956 ,x_return_status => l_return_status
957 ,x_msg_count => l_msg_count
958 ,x_msg_data => l_msg_data
959 );
960
961 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
962 WF_NOTIFICATION.WriteToClob(document, 'Content Generation failed');
963 dbms_lob.writeappend(document, 255, substr(Sqlerrm, 255));
964 end if;
965
966
967 --End of Changes Bug 3861540
968 ELSE
969 close get_workplan_preview_info;
970 END IF;
971
972 document_type := 'text/html';
973
974 EXCEPTION
975 WHEN OTHERS THEN
976 WF_NOTIFICATION.WriteToClob(document, 'Content Generation failed');
977 dbms_lob.writeappend(document, 255, substrb(Sqlerrm, 255)); -- changed substr to substrb for bug 3795807
978 NULL;
979 END show_workplan_preview;
980 /*=================================================================
981
982 Name: SET_LEAD_DAYS
983 Type: Procedure
984 Description: This API has been created for giving a flexibility
985 to implementing organization to define the task
986 execution lead time before which task execution
987 workflow process should be started . The project
988 identifier,task identifier and the task execution
989 workflow item type will be passed to client extension
990 so that task execution lead time can be set per task.
991
992 IN:
993 p_item_type --The internal name for the item type. Item types
994 are defined in the Oracle Workflow Builder.
995 p_task_number -- Unique identifier of the task for which lead
996 time needs to be set .
997 p_project_number-- Unique identifier of the project.
998 x_lead_days -- Lead Days
999 =================================================================*/
1000 PROCEDURE SET_LEAD_DAYS
1001 (
1002 p_item_type IN VARCHAR2 :='PATSKEX'
1003 ,p_task_number IN VARCHAR2
1004 ,p_project_number IN VARCHAR2
1005 ,x_lead_days OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
1006 )
1007 IS
1008 /* Important Note : 3961136
1009 The Task Number and the Project Number are Case-Sensitive.
1010 The Customer needs to take care of this while Coding.
1011 */
1012 CURSOR get_lead_days
1013 IS
1014 SELECT ppe.wf_start_lead_days
1015 FROM pa_proj_elements ppe,
1016 pa_projects_all pa
1017 WHERE pa.segment1=p_project_number --Removed Upper() from both sides for Performance Bug Fix 3961136
1018 AND ppe.element_number = p_task_number --Removed Upper() from both sides for Performance Bug Fix 3961136
1019 AND pa.project_id = ppe.project_id ;
1020
1021 BEGIN
1022 OPEN get_lead_days ;
1023 FETCH get_lead_days INTO x_lead_days ;
1024 CLOSE get_lead_days ;
1025 EXCEPTION
1026 WHEN OTHERS THEN
1027 RAISE;
1028 END SET_LEAD_DAYS;
1029
1030 end PA_WORKPLAN_WORKFLOW_CLIENT;
[Home] [Help]