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