[Home] [Help]
PACKAGE BODY: APPS.AMW_PROCCERT_REMINDER_PKG
Source
1 PACKAGE BODY AMW_PROCCERT_REMINDER_PKG AS
2 /* $Header: amwpsrmb.pls 120.0.12000000.2 2007/03/29 16:04:13 hyuen ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMW_PROCCERT_REMINDER_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15 --G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMW_PROCCERT_REMINDER_PKG';
16 --G_FILE_NAME CONSTANT VARCHAR2(12) := 'amwpsrmb.pls';
17
18 TYPE t_proc_owner_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
19 g_proc_owner_tbl t_proc_owner_tbl;
20
21 TYPE t_owner_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
22 g_ownerlist_tbl t_owner_tbl;
23
24 FUNCTION get_proc_owner(
25 p_certification_id IN NUMBER,
26 p_organization_id IN NUMBER,
27 p_process_id IN NUMBER)
28 RETURN NUMBER;
29
30
31 FUNCTION get_proc_owner(
32 p_certification_id IN NUMBER,
33 p_organization_id IN NUMBER,
34 p_process_id IN NUMBER)
35 RETURN NUMBER
36 IS
37 CURSOR C_Proc_Owner IS
38 select TO_NUMBER(REPLACE(grants.grantee_key,'HZ_PARTY:',''))
39 from fnd_grants grants,
40 fnd_objects obj,
41 fnd_menus granted_menu
42 where obj.obj_name = 'AMW_PROCESS_ORGANIZATION'
43 AND grants.object_id = obj.object_id
44 AND grants.grantee_type ='USER'
45 AND grants.instance_type = 'INSTANCE'
46 AND grants.instance_pk1_value = to_char(p_ORGANIZATION_ID)
47 AND grants.instance_pk2_value = to_char(p_PROCESS_ID)
48 AND grants.grantee_key like 'HZ_PARTY%'
49 AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
50 AND grants.menu_id = granted_menu.menu_id
51 and granted_menu.menu_name = 'AMW_ORG_PROC_OWNER_ROLE';
52
53 CURSOR C_Parent_Proc IS
54 select parent_process_id
55 from amw_execution_scope
56 where entity_type = 'BUSIPROC_CERTIFICATION'
57 and entity_id = p_certification_id
58 and organization_id = p_organization_id
59 and process_id = p_process_id;
60
61 CURSOR C_Org_Owner IS
62 select TO_NUMBER(REPLACE(grants.grantee_key,'HZ_PARTY:',''))
63 from fnd_grants grants,
64 fnd_objects obj,
65 fnd_menus granted_menu
66 where obj.obj_name = 'AMW_ORGANIZATION'
67 AND grants.object_id = obj.object_id
68 AND grants.grantee_type ='USER'
69 AND grants.instance_type = 'INSTANCE'
70 AND grants.instance_pk1_value = to_char(p_ORGANIZATION_ID)
71 AND grants.grantee_key like 'HZ_PARTY%'
72 AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
73 AND grants.menu_id = granted_menu.menu_id
74 and granted_menu.menu_name = 'AMW_ORG_MANAGER_ROLE';
75
76 l_proc_owner NUMBER;
77 l_parent_proc_id NUMBER;
78 BEGIN
79 IF g_proc_owner_tbl.exists(p_process_id) THEN
80 return g_proc_owner_tbl(p_process_id);
81 END IF;
82
83 OPEN C_Proc_Owner;
84 FETCH C_Proc_Owner INTO l_proc_owner;
85 CLOSE C_Proc_Owner;
86
87 IF l_proc_owner IS null THEN
88 OPEN C_Parent_Proc;
89 FETCH C_Parent_Proc INTO l_parent_proc_id;
90 CLOSE C_Parent_Proc;
91
92 IF l_parent_proc_id = -1 OR l_parent_proc_id IS NULL THEN
93 OPEN C_Org_Owner;
94 FETCH C_Org_Owner INTO l_proc_owner;
95 CLOSE C_Org_Owner;
96 ELSE
97 l_proc_owner := Get_Proc_Owner (
98 p_certification_id => p_certification_id,
99 p_organization_id => p_organization_id,
100 p_process_id => l_parent_proc_id);
101 END IF;
102 END IF;
103
104 g_proc_owner_tbl(p_process_id) := l_proc_owner;
105
106 return l_proc_owner;
107
108 END Get_Proc_Owner;
109
110 /* hyuen start Bug 5098058 */
111 PROCEDURE add_owner_to_list(p_owner_id IN NUMBER) IS
112 l_exists boolean;
113 BEGIN
114
115 IF p_owner_id IS NOT NULL THEN
116 l_exists := FALSE;
117 FOR i IN 1 .. g_ownerlist_tbl.COUNT
118 LOOP
119
120 IF g_ownerlist_tbl(i) = p_owner_id THEN
121 l_exists := TRUE;
122 EXIT;
123 END IF;
124
125 END LOOP;
126
127 IF NOT l_exists THEN
128 g_ownerlist_tbl(g_ownerlist_tbl.COUNT + 1) := p_owner_id;
129 END IF;
130
131 END IF;
132
133 END add_owner_to_list;
134
135 PROCEDURE get_proc_ownerlist(p_certification_id IN NUMBER, p_organization_id IN NUMBER, p_process_id IN NUMBER) IS
136 CURSOR c_proc_owner IS
137 SELECT to_number(REPLACE(grants.grantee_key, 'HZ_PARTY:', '')) process_owner_id
138 FROM fnd_grants grants,
139 fnd_objects obj,
140 fnd_menus granted_menu
141 WHERE obj.obj_name = 'AMW_PROCESS_ORGANIZATION'
142 AND grants.object_id = obj.object_id
143 AND grants.grantee_type = 'USER'
144 AND grants.instance_type = 'INSTANCE'
145 AND grants.instance_pk1_value = to_char(p_organization_id)
146 AND grants.instance_pk2_value = to_char(p_process_id)
147 AND grants.grantee_key LIKE 'HZ_PARTY%'
148 AND nvl(grants.end_date, sysdate + 1) >= TRUNC(sysdate)
149 AND grants.menu_id = granted_menu.menu_id
150 AND granted_menu.menu_name = 'AMW_ORG_PROC_OWNER_ROLE';
151
152 CURSOR c_parent_proc IS
153 SELECT parent_process_id
154 FROM amw_execution_scope
155 WHERE entity_type = 'BUSIPROC_CERTIFICATION'
156 AND entity_id = p_certification_id
157 AND organization_id = p_organization_id
158 AND process_id = p_process_id;
159
160 CURSOR c_org_owner IS
161 SELECT to_number(REPLACE(grants.grantee_key, 'HZ_PARTY:', '')) org_owner_id
162 FROM fnd_grants grants,
163 fnd_objects obj,
164 fnd_menus granted_menu
165 WHERE obj.obj_name = 'AMW_ORGANIZATION'
166 AND grants.object_id = obj.object_id
167 AND grants.grantee_type = 'USER'
168 AND grants.instance_type = 'INSTANCE'
169 AND grants.instance_pk1_value = to_char(p_organization_id)
170 AND grants.grantee_key LIKE 'HZ_PARTY%'
171 AND nvl(grants.end_date, sysdate + 1) >= TRUNC(sysdate)
172 AND grants.menu_id = granted_menu.menu_id
173 AND granted_menu.menu_name = 'AMW_ORG_MANAGER_ROLE';
174
175 l_proc_owner NUMBER;
176 l_parent_proc_id NUMBER;
177 l_exists boolean;
178 BEGIN
179 FOR procowner_rec IN c_proc_owner
180 LOOP
181 add_owner_to_list( procowner_rec.process_owner_id );
182 END LOOP;
183
184 IF g_ownerlist_tbl.COUNT = 0 THEN
185 OPEN c_parent_proc;
186 FETCH c_parent_proc INTO l_parent_proc_id;
187 CLOSE c_parent_proc;
188
189 IF l_parent_proc_id = -1 OR l_parent_proc_id IS NULL THEN
190
191 FOR orgowner_rec IN c_org_owner
192 LOOP
193 add_owner_to_list( orgowner_rec.org_owner_id );
194 END LOOP;
195 ELSE
196 get_proc_ownerList(p_certification_id => p_certification_id,
197 p_organization_id => p_organization_id,
198 p_process_id => l_parent_proc_id);
199 END IF;
200
201 END IF;
202 END get_proc_ownerlist;
203
204 /* hyuen end Bug 5098058 */
205
206
207 PROCEDURE send_reminder_to_all_owners(errbuf OUT NOCOPY VARCHAR2,retcode OUT NOCOPY VARCHAR2)
208 is
209 --- GET ALL certiifcatios for which reminders is due -------
210 cursor Get_Certi_for_Reminders is
211 select certification_id
212 from amw_certification_b
213 where OBJECT_TYPE='PROCESS'
214 and (LAST_REMINDER_DATE is null
215 OR (trunc(LAST_REMINDER_DATE) + CERTIFICATION_REMINDER <= trunc(SYSDATE)))
216 and CERTIFICATION_STATUS = 'ACTIVE';
217
218 cursor Get_Org_To_Notify(c_cert_id NUMBER) is
219 select distinct organization_id
220 from amw_execution_scope exscope
221 where exscope.ENTITY_TYPE = 'BUSIPROC_CERTIFICATION'
222 and exscope.ENTITY_ID = c_cert_id
223 and exscope.level_id > 3
224 and not exists (select 'Y'
225 from amw_opinions_v opinion
226 where opinion.PK1_VALUE = exscope.ENTITY_ID
227 and opinion.PK2_VALUE = exscope.ORGANIZATION_ID
228 and opinion.PK3_VALUE = exscope.PROCESS_ID
229 and opinion.object_name = 'AMW_ORG_PROCESS'
230 and opinion.OPINION_TYPE_CODE = 'CERTIFICATION');
231
232
233 cursor Get_Org_Proc_To_Notify(c_cert_id NUMBER, c_org_id NUMBER) is
234 select distinct process_id
235 from amw_execution_scope exscope
236 where exscope.ENTITY_TYPE = 'BUSIPROC_CERTIFICATION'
237 and exscope.ENTITY_ID = c_cert_id
238 and exscope.organization_id = c_org_id
239 and exscope.level_id > 3
240 and not exists (select 'Y'
241 from amw_opinions_v opinion
242 where opinion.PK1_VALUE = exscope.ENTITY_ID
243 and opinion.PK2_VALUE = exscope.ORGANIZATION_ID
244 and opinion.PK3_VALUE = exscope.PROCESS_ID
245 and opinion.object_name = 'AMW_ORG_PROCESS'
246 and opinion.OPINION_TYPE_CODE = 'CERTIFICATION');
247
248 x_return_status VARCHAR2(30);
249
250 l_owner_id NUMBER;
251 l_owner_tbl t_owner_tbl;
252 l_exists BOOLEAN;
253
254 begin
255
256 fnd_file.put_line(fnd_file.LOG, 'Starting to Fetch all Certification which are active and a reminder need to send from cursor Get_Certi_for_Reminders');
257
258
259 --------------------------------------------
260 -- Main cursor loop
261 --------------------------------------------
262
263 for cert_rec in Get_Certi_for_Reminders loop
264 fnd_file.put_line(fnd_file.LOG,
265 'Fetching Certification ID: '||to_number(cert_rec.certification_id));
266
267 l_owner_tbl.delete;
268 g_ownerlist_tbl.delete; /* hyuen bug 5098058 */
269 for org_rec in Get_Org_To_Notify(cert_rec.certification_id) loop
270 g_proc_owner_tbl.delete;
271
272 for proc_rec in Get_Org_Proc_to_Notify(cert_rec.certification_id, org_rec.organization_id) loop
273 /* hyuen start bug 5098058
274 l_owner_id := get_proc_owner(
275 p_certification_id => cert_rec.certification_id,
276 p_organization_id => org_rec.organization_id,
277 p_process_id => proc_rec.process_id);
278
279 IF l_owner_id IS NOT NULL THEN
280 l_exists := false;
281 FOR i IN 1..l_owner_tbl.count LOOP
282 IF l_owner_tbl(i) = l_owner_id THEN
283 l_exists := true;
284 EXIT;
285 END IF;
286 END LOOP;
287 IF NOT l_exists THEN
288 l_owner_tbl(l_owner_tbl.count+1) := l_owner_id;
289 END IF;
290 END IF; */
291 get_proc_ownerList(p_certification_id => cert_rec.certification_id,
292 p_organization_id => org_rec.organization_id,
293 p_process_id => proc_rec.process_id);
294 /* hyuen end bug 5098058 */
295 end loop;
296 end loop;
297
298 /* hyuen start bug 5098058
299 FOR i IN 1..l_owner_tbl.count LOOP
300 AMW_PROCCERT_REMINDER_PKG.send_reminder_to_owner
301 ( p_certification_id => cert_rec.certification_id,
302 p_process_owner_id => l_owner_tbl(i),
303 x_return_status => x_return_status);
304 END LOOP; */
305
306 FOR i IN 1 .. g_ownerList_tbl.COUNT LOOP
307 amw_proccert_reminder_pkg.send_reminder_to_owner
308 ( p_certification_id => cert_rec.certification_id,
309 p_process_owner_id => g_ownerList_tbl(i),
310 x_return_status => x_return_status);
311 END LOOP;
312 /* hyuen end bug 5098058 */
313
314 AMW_PROCCERT_REMINDER_PKG.update_lastreminder_date
315 (p_certificaion_id => cert_rec.certification_id ,
316 x_return_status => x_return_status);
317 COMMIT;
318 end loop;
319
320 EXCEPTION
321 WHEN OTHERS THEN
322 fnd_file.put_line(fnd_file.LOG,
323 'unexpected error in send_reminder_to_all_owners: '||sqlerrm);
324 END send_reminder_to_all_owners;
325
326
327 -- =================== ************************** ===================================== --
328
329
330 PROCEDURE update_lastreminder_date(p_certificaion_id IN number, x_return_status OUT NOCOPY VARCHAR2)
331 is
332 begin
333 fnd_file.put_line(fnd_file.LOG,
334 'Going to update LAST_REMINDER_DATE for '||p_certificaion_id);
335
336 update amw_certification_b
337 set LAST_REMINDER_DATE= sysdate
338 where CERTIFICATION_ID = p_certificaion_id;
339
340 x_return_status := FND_API.G_RET_STS_SUCCESS;
341
342 fnd_file.put_line(fnd_file.LOG,
343 'Updates LAST_REMINDER_DATE for '||p_certificaion_id);
344 EXCEPTION
345 WHEN OTHERS THEN
346 fnd_file.put_line(fnd_file.LOG,
347 'unexpected error in update_lastreminder_date: '||sqlerrm);
348 END update_lastreminder_date;
349
350
351 -- =================== ************************** ===================================== --
352
353 PROCEDURE send_reminder_to_owner(
354 p_item_type IN VARCHAR2 := 'AMWNOTIF',
355 p_message_name IN VARCHAR2 := 'PROCESSCERTIFICATIONREMINDER',
356 p_certification_id in NUMBER,
357 p_process_owner_id in NUMBER,
358 p_organization_id in NUMBER := null,
359 p_process_id in NUMBER := null,
360 x_return_status OUT NOCOPY VARCHAR2)
361 is
362 cursor c_certification (c_cert_id NUMBER) is
363 select certification_name
364 from amw_certification_vl
365 where certification_id=c_cert_id;
366
367 cursor c_person (c_party_id NUMBER) is
368 select employee_id
369 from amw_employees_current_v
370 where party_id = c_party_id;
371
372 l_cert_name VARCHAR2(240);
373 l_to_role_name VARCHAR2(100);
374 l_from_role_name VARCHAR2(100);
375 l_display_role_name VARCHAR2(240);
376 l_notif_id NUMBER;
377 l_subject VARCHAR2(2000);
378 l_to_emp_id NUMBER;
379 l_from_emp_id NUMBER := FND_GLOBAL.employee_id;
380
381 begin
382
383 fnd_file.put_line(fnd_file.LOG, 'send_reminder_to_owner begin');
384 fnd_file.put_line(fnd_file.LOG, 'certification_id:'||to_char(p_certification_id));
385 fnd_file.put_line(fnd_file.LOG, 'process_owner_id:'||to_char(p_process_owner_id));
386 fnd_file.put_line(fnd_file.LOG, 'organization_id:'||p_organization_id);
387 fnd_file.put_line(fnd_file.LOG, 'process_id:'||p_process_id);
388
389 x_return_status := FND_API.G_RET_STS_SUCCESS;
390
391 open c_certification(p_certification_id);
392 fetch c_certification into l_cert_name;
393 close c_certification;
394
395 open c_person(p_process_owner_id);
396 fetch c_person into l_to_emp_id;
397 close c_person;
398
399
400 FND_MESSAGE.set_name('AMW', 'AMW_PROCESSOWNER_REMINDER_SUBJ');
401 FND_MESSAGE.set_token('CERTIFICATION_NAME', l_cert_name, TRUE);
402 FND_MSG_PUB.add;
403 l_subject := fnd_msg_pub.get(
404 p_msg_index => fnd_msg_pub.G_LAST,
405 p_encoded => fnd_api.g_false);
406
407
408
409 WF_DIRECTORY.getrolename
410 (p_orig_system => 'PER',
411 p_orig_system_id => l_to_emp_id,
412 p_name => l_to_role_name,
413 p_display_name => l_display_role_name );
414
415
416 IF l_to_role_name IS NULL THEN
417 x_return_status := FND_API.G_RET_STS_ERROR;
418 FND_MESSAGE.set_name('AMW','AMW_APPR_INVALID_ROLE');
419 FND_MSG_PUB.ADD;
420 fnd_file.put_line(fnd_file.LOG, 'to_role is null');
421 ELSE
422
423 WF_DIRECTORY.getrolename
424 (p_orig_system => 'PER',
425 p_orig_system_id => l_from_emp_id ,
426 p_name => l_from_role_name,
427 p_display_name => l_display_role_name);
428
429 l_notif_id := WF_NOTIFICATION.send
430 (role => l_to_role_name,
431 msg_type => p_item_type,
432 msg_name => p_message_name);
433
434 fnd_file.put_line(fnd_file.LOG, 'notification_id:'||l_notif_id||'--'||l_subject);
435
436 WF_NOTIFICATION.SetAttrText
437 (l_notif_id,
438 'MSG_SUBJECT',
439 l_subject);
440
441 WF_NOTIFICATION.setattrtext
442 (l_notif_id,
443 '#FROM_ROLE',
444 l_from_role_name);
445
446 WF_NOTIFICATION.setattrnumber
447 (l_notif_id,
448 'CERTIFICATION_ID',
449 p_certification_id);
450
451 WF_NOTIFICATION.setattrnumber
452 (l_notif_id,
453 'PROCESS_OWNER_ID',
454 p_process_owner_id);
455
456 if p_process_id is not null then
457 WF_NOTIFICATION.setattrnumber
458 (l_notif_id,
459 'ORGANIZATION_ID',
460 p_organization_id);
461
462 WF_NOTIFICATION.setattrnumber
463 (l_notif_id,
464 'PROCESS_ID',
465 p_process_id);
466
467 end if;
468 end if;
469
470 EXCEPTION
471 WHEN OTHERS THEN
472 fnd_file.put_line(fnd_file.LOG,
473 'unexpected error in send_reminder_to_owner: '||sqlerrm);
474 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
475 END send_reminder_to_owner;
476
477 -------------------------- ********************************************** ---------------------
478 -- Sends reminder to all Process Owners in Procecss Hierarchy based on a selction in UI by user
479 -----------------------------------------------------------------------------------------------
480 PROCEDURE send_reminder_selected_procs(
481 p_organization_id IN Number,
482 p_entity_id IN Number,
483 p_process_id In Number,
484 x_return_status OUT NOCOPY VARCHAR2)
485 is
486 CURSOR Get_Pending_Cert_Proc IS
487 SELECT distinct scp.organization_id, scp.PROCESS_ID
488 FROM amw_execution_scope scp
489 where not exists (select 'Y'
490 from amw_opinions_v opinion
491 where opinion.PK1_VALUE = scp.ENTITY_ID
492 and opinion.PK2_VALUE = scp.ORGANIZATION_ID
493 and opinion.PK3_VALUE = scp.PROCESS_ID
494 and opinion.object_name = 'AMW_ORG_PROCESS'
495 and opinion.OPINION_TYPE_CODE = 'CERTIFICATION')
496 start with scp.ENTITY_ID=p_entity_id
497 and scp.ENTITY_TYPE='BUSIPROC_CERTIFICATION'
498 and scp.PROCESS_ID=p_process_id
499 and scp.ORGANIZATION_ID=p_organization_id
500 connect by PRIOR scp.PROCESS_ID=scp.PARENT_PROCESS_ID
501 and PRIOR scp.ENTITY_ID=scp.ENTITY_ID
502 AND PRIOR scp.ORGANIZATION_ID=scp.ORGANIZATION_ID
503 and PRIOR scp.ENTITY_TYPE=scp.ENTITY_TYPE;
504
505
506 cursor Get_Proc_Owner_To_Notify is
507 select distinct
508 TO_NUMBER(REPLACE(grants.grantee_key,'HZ_PARTY:',''))
509 process_owner_id
510 from amw_execution_scope exscope,
511 fnd_grants grants,
512 fnd_objects obj,
513 fnd_menus granted_menu
514 where exscope.ENTITY_TYPE = 'BUSIPROC_CERTIFICATION'
515 and exscope.ENTITY_ID = p_entity_id
516 and obj.obj_name = 'AMW_PROCESS_ORGANIZATION'
517 AND grants.object_id = obj.object_id
518 AND grants.grantee_type ='USER'
519 AND grants.instance_type = 'INSTANCE'
520 AND grants.instance_pk1_value = to_char(exscope.ORGANIZATION_ID)
521 AND grants.instance_pk2_value = to_char(exscope.PROCESS_ID)
522 AND grants.grantee_key like 'HZ_PARTY%'
523 AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
524 AND grants.menu_id = granted_menu.menu_id
525 and granted_menu.menu_name = 'AMW_ORG_PROC_OWNER_ROLE'
526 and not exists (select 'Y'
527 from amw_opinions_v opinion
528 where opinion.PK1_VALUE = exscope.ENTITY_ID
529 and opinion.PK2_VALUE = exscope.ORGANIZATION_ID
530 and opinion.PK3_VALUE = exscope.PROCESS_ID
531 and opinion.object_name = 'AMW_ORG_PROCESS'
532 and opinion.OPINION_TYPE_CODE = 'CERTIFICATION')
533 and (exscope.organization_id, exscope.PROCESS_ID) in
534 (select exscopeB.organization_id, exscopeB.PROCESS_ID
535 from amw_execution_scope exscopeB
536 start with exscopeB.ENTITY_ID=p_entity_id
537 and exscopeB.ENTITY_TYPE='BUSIPROC_CERTIFICATION'
538 and exscopeB.PROCESS_ID=p_process_id
539 and exscopeB.ORGANIZATION_ID=p_organization_id
540 connect by
541 PRIOR exscopeB.PROCESS_ID=exscopeB.PARENT_PROCESS_ID
542 and PRIOR exscopeB.ENTITY_ID=exscopeB.ENTITY_ID
543 AND PRIOR exscopeB.ORGANIZATION_ID=exscopeB.ORGANIZATION_ID
544 and PRIOR exscopeB.ENTITY_TYPE=exscopeB.ENTITY_TYPE);
545
546 lx_return_status varchar2(30);
547
548 l_owner_id NUMBER;
549 l_owner_tbl t_owner_tbl;
550 l_exists BOOLEAN;
551
552 begin
553 fnd_file.put_line(fnd_file.LOG, 'Getting Data from Cursor Get_Proc_Owner_To_Notify ');
554
555 x_return_status := FND_API.G_RET_STS_SUCCESS;
556
557 g_proc_owner_tbl.delete;
558 g_ownerlist_tbl.delete; /* hyuen bug 5098058 */
559
560 FOR proc_rec in Get_Pending_Cert_Proc LOOP
561 /* hyuen start bug 5098058
562 l_owner_id := get_proc_owner(
563 p_certification_id => p_entity_id,
564 p_organization_id => p_organization_id,
565 p_process_id => proc_rec.process_id);
566
567 IF l_owner_id IS NOT NULL THEN
568 l_exists := false;
569 FOR i IN 1..l_owner_tbl.count LOOP
570 IF l_owner_tbl(i) = l_owner_id THEN
571 l_exists := true;
572 EXIT;
573 END IF;
574 END LOOP;
575 IF NOT l_exists THEN
576 l_owner_tbl(l_owner_tbl.count+1) := l_owner_id;
577 END IF;
578 END IF; */
579 get_proc_ownerList(p_certification_id => p_entity_id,
580 p_organization_id => p_organization_id,
581 p_process_id => proc_rec.process_id);
582 /* hyuen end bug 5098058 */
583 END LOOP;
584
585 /* hyuen start bug 5098058
586 FOR i IN 1..l_owner_tbl.count LOOP
587 AMW_PROCCERT_REMINDER_PKG.send_reminder_to_owner
588 ( p_certification_id => p_entity_id,
589 p_process_owner_id => l_owner_tbl(i),
590 p_organization_id => p_organization_id,
591 p_process_id => p_process_id,
592 x_return_status => lx_return_status);
593 END LOOP; */
594 FOR i IN 1 .. g_ownerList_tbl.COUNT LOOP
595 amw_proccert_reminder_pkg.send_reminder_to_owner
596 (p_certification_id => p_entity_id,
597 p_process_owner_id => g_ownerList_tbl(i),
598 p_organization_id => p_organization_id,
599 p_process_id => p_process_id,
600 x_return_status => lx_return_status);
601 END LOOP;
602 /* hyuen end bug 5098058 */
603
604 commit;
605
606 EXCEPTION
607 WHEN OTHERS THEN
608 fnd_file.put_line(fnd_file.LOG,
609 'unexpected error in send_reminder_to_owner: '||sqlerrm);
610 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
611 END send_reminder_selected_procs;
612
613
614 END AMW_PROCCERT_REMINDER_PKG ;