[Home] [Help]
PACKAGE BODY: APPS.OKC_DELIVERABLE_WF_PVT
Source
1 PACKAGE BODY OKC_DELIVERABLE_WF_PVT AS
2 /* $Header: OKCVDELWFB.pls 120.2.12020000.8 2013/04/16 19:26:44 nbingi ship $ */
3 ---------------------------------------------------------------------------
4 -- Procedures and Functions
5 ---------------------------------------------------------------------------
6 -------------------------------------------------------------------------------
7 --Start of Comments
8 --Name: get_subject_text
9 --Function:
10 -- This is a callback procedure for a PL/SQL Document attribute in workflow. It is used to populate the subject text of a deliverable notification.
11 --
12 --Parameters:
13 --document_id : of the form 'MESSAGE_CODE:DELIVERABLE_ID', where message_code comes from FND_NEW_MESSAGES, and deliverable_id will be used to query OKC_DELIVERABLES and populate the tokens of the message.
14 --Testing:
15 --
16 --End of Comments
17 -------------------------------------------------------------------------------
18 -- package variables
19 ---------------------------------------------------------------------------
20 g_module VARCHAR2(250) := 'okc.plsql.'||g_pkg_name||'.';
21 ---------------------------------------------------------------------------
22
23 PROCEDURE get_subject_text (document_id in varchar2,
24 display_type in varchar2,
25 document in out NOCOPY varchar2,
26 document_type in out NOCOPY varchar2)
27 IS
28
29 l_divider_index NUMBER;
30 l_del_id NUMBER;
31 l_msg_code VARCHAR2(30);
32 l_uom_text VARCHAR2(80);
33
34 CURSOR deliverable_tokens_cursor IS
35 SELECT busDocTL.name,
36 busDocTL.document_type,
37 deliverable.business_document_number,
38 deliverable.deliverable_name,
39 deliverable.notify_prior_due_date_value,
40 deliverable.notify_prior_due_date_uom
41 FROM okc_bus_doc_types_tl busDocTL, okc_deliverables deliverable
42 WHERE deliverable.deliverable_id = l_del_id
43 AND deliverable.business_document_type = busDocTL.document_type
44 AND busDocTL.language = userenv('LANG');
45 del_tokens_rec deliverable_tokens_cursor%ROWTYPE;
46
47
48 --Acq Plan Message Cleanup
49 l_resolved_msg_name VARCHAR2(30);
50 l_resolved_token VARCHAR2(100);
51
52 begin
53 --document_id is of the form MSG_CODE:DEL_ID
54 l_divider_index := instr(document_id,':');
55 l_msg_code := substr(document_id,1,l_divider_index-1);
56 l_del_id := substr(document_id,l_divider_index+1,length(document_id));
57
58 --set message code
59 fnd_message.clear;
60 fnd_message.set_name(APPLICATION=>'OKC',NAME=>l_msg_code);
61
62 --set tokens
63 OPEN deliverable_tokens_cursor;
64 FETCH deliverable_tokens_cursor INTO del_tokens_rec;
65 IF deliverable_tokens_cursor%FOUND THEN
66
67 --Acq Plan Message Cleanup
68 l_resolved_msg_name := OKC_API.resolve_message(l_msg_code, del_tokens_rec.document_type);
69 l_resolved_token := OKC_API.resolve_del_token(del_tokens_rec.document_type);
70
71 fnd_message.set_name(APPLICATION=>'OKC', NAME=>l_resolved_msg_name);
72 fnd_message.set_token(TOKEN => 'DEL_TOKEN',VALUE => l_resolved_token);
73 fnd_message.set_token(TOKEN => 'DELIVERABLENAME',VALUE => del_tokens_rec.deliverable_name);
74 fnd_message.set_token(TOKEN => 'BUSDOCTYPE',VALUE => del_tokens_rec.name);
75 fnd_message.set_token(TOKEN => 'BUSDOCNUM',VALUE => del_tokens_rec.business_document_number);
76
77 --Before due may have extra tokens
78 --if l_msg_code = 'OKC_DEL_BEFOREDUE_NTF_SUBJECT' THEN
79 if l_msg_code = 'OKC_DEL_BEFOREDUE_NTF_SUBJECT' THEN
80
81 fnd_message.set_token(TOKEN => 'AMOUNT',VALUE => del_tokens_rec.notify_prior_due_date_value);
82
83 select meaning into l_uom_text from fnd_lookups
84 where lookup_type = 'OKC_DELIVERABLE_TIME_UNITS'
85 and lookup_code = del_tokens_rec.notify_prior_due_date_uom;
86 fnd_message.set_token(TOKEN => 'UNITS' ,VALUE => l_uom_text);
87 end if; --end setting extra tokens for okc_del_beforedue_ntf_subject
88 END IF; --deliverable_tokens_cursor%FOUND
89 CLOSE deliverable_tokens_cursor;
90
91
92 document:=fnd_message.get;
93
94
95 end get_subject_text;
96
97
98 -------------------------------------------------------------------------------
99 --Start of Comments
100 --Name: get_internal_user_role
101 --Function:
102 -- Function returns the role name of the employee with ID p_employee_id
103 -- If the employee has no FND_USER entry, it will create an adhoc role with the
104 -- employee's email address and return that adhoc role name.
105 --Parameters:
106 --p_employee_id : PERSON ID from PER_PEOPLE_F hr_employees_current_v
107 --Testing:
108 --
109 --End of Comments
110 -------------------------------------------------------------------------------
111
112 FUNCTION get_internal_user_role(p_employee_id IN NUMBER,
113 x_role_display_name OUT NOCOPY VARCHAR2) RETURN VARCHAR2
114
115 IS
116 --bug#4694703 replaced hr_employees_current_v with PER_PEOPLE_F
117 cursor C_user_email(x_employee_id NUMBER) is
118 select email_address, full_name from PER_PEOPLE_F where person_id = x_employee_id;
119
120 l_role_name wf_roles.name%TYPE;
121 l_role_display_name wf_roles.display_name%TYPE;
122
123 l_email per_people_f.email_address%TYPE;
124 l_full_name per_people_f.full_name%TYPE;
125
126 l_api_name CONSTANT VARCHAR2(30) :='get_internal_user_role';
127
128 BEGIN
129
130 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
131 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Entered INTO get_internal_user_role');
132 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
133 ,'101: p_employee_id = '||p_employee_id);
134
135 END IF;
136
137 IF p_employee_id IS NULL THEN
138 return NULL;
139 END IF;
140
141 -- for given employee id, get WF role_name and role_display_name from wf_local_roles
142 WF_DIRECTORY.GetUserName (p_orig_system => 'PER',
143 p_orig_system_id => p_employee_id,
144 p_name => l_role_name,
145 p_display_name => l_role_display_name);
146
147 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
148 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
149 ,'102: Found Role Name = '||l_role_name);
150 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
151 ,'103: Found Role Display Name = '||l_role_display_name);
152 END IF;
153
154 -- if role name is still NULL, creat an adhoc role to send email notification
155 -- to the employee's email address
156 IF l_role_name is NULL THEN
157
158 OPEN C_user_email(p_employee_id);
159 FETCH C_user_email into l_email, l_full_name;
160 CLOSE C_user_email;
161
162 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
163 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
164 ,'104: Employee Email = '||l_email);
165 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
166 ,'105: Employee Full Name = '||l_full_name);
167 END IF;
168
169 IF l_email IS NOT NULL AND l_full_name IS NOT NULL THEN
170
171 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
172 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
173 ,'106: Calling createAdHocRole');
174 END IF;
175
176 -- set role display name as Employee's Full Name
177 l_role_display_name := l_full_name;
178 WF_DIRECTORY.createAdHocRole(role_name=>l_role_name,
179 role_display_name=>l_role_display_name,
180 language=>null,
181 territory=>null,
182 role_description=>'Deliverables Ad hoc role',
183 notification_preference=>'MAILHTML',
184 email_address=>l_email,
185 status=>'ACTIVE',
186 expiration_date=>SYSDATE+1);
187
188 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
189 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
190 ,'107: DONE createAdHocRole');
191 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
192 ,'108: Got Role Name = '||l_role_name);
193 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
194 ,'109: Got Role Display Name = '||l_role_display_name);
195 END IF;
196
197 END IF; -- IF l_email IS NOT NULL AND l_full_name IS NOT NULL
198 END IF; -- IF l_role_name is NULL
199
200 -- set x_role_display_name as l_role_display_name
201 x_role_display_name := l_role_display_name;
202
203 return l_role_name;
204
205 EXCEPTION
206 WHEN OTHERS THEN
207 IF C_user_email%ISOPEN THEN
208 CLOSE C_user_email;
209 END IF;
210 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
211 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name
212 ,'110: IN EXCEPTION '||substr(sqlerrm,1,200));
213 END IF;
214
215 return l_role_name;
216
217 END get_internal_user_role;
218
219 --------------------------------------------------------------------------
220 --Start of Comments
221 --Name: deliverables_notifier
222 --Function:
223 -- This procedure sends notifications by starting a workflow process. The
224 -- notification will be sent to either the internal or external contacts or both depending
225 -- on the type of notification. The notifications will have embedded OA regions
226 -- describing the business context and the deliverable details.
227 --Parameters:
228 --IN:
229 --p_msg_code - one of G_COMPLETE_NTF_CODE, G_OVERDUE_NTF_CODE,G_BEFOREDUE_NTF_CODE,G_ESCALATE_NTF_CODE
230 --OUT:
231 -- x_notification_id - the notification id of the notification sent if only 1 was sent
232 -- or the id of the one sent to the responsible party if 2 were sent.
233 --Notes:
234 -- We select column external_userlist_proc from the OKC_BUS_DOC_TYPES_V view. This
235 -- value, if not null, must carry the following parameters:
236 --'begin '||l_external_function_name||'(
237 -- p_api_version=>:p_api_version,
238 -- p_document_id=>:p_document_id,
239 -- p_document_type=>:p_document_type,
240 -- x_return_status=>:x_return_status,
241 -- x_msg_count=>:x_msg_count,
242 -- x_msg_data=>:x_msg_data,
243 -- x_external_userlist=>:x_external_userlist);
244 -- Testing:
245 --
246 --End of Comments
247 --------------------------------------------0-----------------------------------
248 PROCEDURE deliverables_notifier(
249
250 p_api_version IN NUMBER:=1.0,
251 p_init_msg_list IN VARCHAR2:=FND_API.G_FALSE,
252 p_deliverable_id IN NUMBER,
253 p_deliverable_name IN VARCHAR2,
254 p_deliverable_type IN VARCHAR2,
255 p_business_document_id IN NUMBER,
256 p_business_document_version IN NUMBER,
257 p_business_document_type IN VARCHAR2,
258 p_business_document_number IN VARCHAR2,
259 p_resp_party IN VARCHAR2,
260 p_external_contact IN NUMBER,
261 p_internal_contact IN NUMBER,
262 p_requester_id IN NUMBER default null,
263 p_notify_prior_due_date_value IN VARCHAR2 default null,
264 p_notify_prior_due_date_uom IN VARCHAR2 default null,
265 p_msg_code IN VARCHAR2,
266 x_notification_id OUT NOCOPY NUMBER,
267 x_msg_data OUT NOCOPY VARCHAR2,
268 x_msg_count OUT NOCOPY NUMBER,
269 x_return_status OUT NOCOPY VARCHAR2) IS
270
271 l_item_key number;
272 l_msg_key varchar2(30);
273 l_item_type varchar2(30):='OKCDELWF';
274 l_process_name varchar2(30):='OKCDELNOTIFY';
275 l_notif_id number;
276
277 l_busdoctype_meaning varchar2(150);
278
279 l_internal_role_name varchar2(320);
280 l_external_role_name varchar2(320); -- could be either from fnd_user or ad hoc role
281 l_requester_role_name varchar2(320);
282 l_external_email varchar2(2000);
283 l_internal_email varchar2(240);
284 l_role_desc varchar2(500);
285 l_role_desc2 varchar2(500);
286 l_email varchar2(500);
287
288 l_ext_users varchar2(1000);
289 l_ext_contact_error varchar2(1);
290 l_subject_text varchar2(200);
291 l_error_msg varchar2(4000);
292 l_ui_region varchar2(1000);
293
294 l_header_function_name varchar2(2000);
295
296 l_api_name CONSTANT VARCHAR2(30) := 'deliverables_notifier';
297 l_sql_string varchar2(1000);
298 l_doc_class varchar2(30);
299 l_external_function_name varchar2(50);
300
301 l_external_is_fyi VARCHAR2(1) := 'N';
302 l_internal_only VARCHAR2(1) := 'N';
303
304 CURSOR BUSDOC_TYPE IS
305 SELECT tl.name,b.document_type_class,
306 b.external_userlist_proc,b.notification_header_function
307 FROM okc_bus_doc_types_b b, okc_bus_doc_types_tl tl
308 WHERE b.document_type = tl.document_type
309 AND tl.language = userenv('LANG')
310 AND b.document_type = p_business_document_type;
311 busdoc_type_rec busdoc_type%ROWTYPE;
312
313 -- Bug # 4292616 FND_USER.CUSTOMER_ID TO STORE RELATIONSHIP_PARTY_ID
314 -- Bug # 5149752 replaced user_name with user_id
315 cursor ext_user is
316 select user_id
317 from fnd_user
318 where person_party_id=p_external_contact;
319
320 -- updated cursor for bug#4069955
321 CURSOR delTypeInternalFlag is
322 select delType.internal_flag
323 from okc_deliverable_types_b delType,
324 okc_bus_doc_types_b docType,
325 okc_del_bus_doc_combxns delComb
326 where delType.deliverable_type_code = p_deliverable_type
327 and docType.document_type = p_business_document_type
328 and docType.document_type_class = delComb.document_type_class
329 and delType.deliverable_type_code = delComb.deliverable_type_code;
330
331 l_internal_deliverable_type varchar2(1) :='N';
332
333 CURSOR getRespPartyCode IS
334 select resp_party_code
335 from
336 okc_resp_parties_b delrsp
337 ,okc_bus_doc_types_b docType
338 where delrsp.resp_party_code = p_resp_party
339 and doctype.document_type = p_business_document_type
340 and delrsp.document_type_class = docType.document_type_class
341 and delrsp.intent = docType.intent;
342
343 l_resp_party_code VARCHAR2(30);
344
345 CURSOR del_cur IS
346 select external_party_id, external_party_role
347 from okc_deliverables
348 where deliverable_id = p_deliverable_id;
349 del_rec del_cur%ROWTYPE;
350 l_external_party_id okc_deliverables.external_party_id%TYPE;
351 l_external_party_role okc_deliverables.external_party_role%TYPE;
352 l_id_for_email NUMBER;
353
354 --bug#4145213
355 CURSOR getRelationId IS
356 select relation.party_id party_id
357 from hz_parties relation
358 ,hz_parties person
359 ,hz_relationships hz
360 where relation.party_id = hz.party_id
361 and person.party_id = hz.subject_id
362 and hz.subject_type = 'PERSON'
363 and hz.object_type = 'ORGANIZATION'
364 and hz.subject_table_name ='HZ_PARTIES'
365 and hz.object_table_name ='HZ_PARTIES'
366 and person.party_id = p_external_contact;
367
368 CURSOR ext_user_email(x NUMBER) IS
369 SELECT hc.email_address
370 FROM hz_contact_points hc
371 WHERE hc.owner_table_name = 'HZ_PARTIES'
372 AND hc.primary_flag = 'Y'
373 AND hc.contact_point_type = 'EMAIL'
374 AND hc.owner_table_id = x;
375
376 /*cursor ext_user_email is
377 select email_address
378 from hz_parties
379 where party_id=p_external_contact;*/
380
381 CURSOR c_del_type(p_del_id IN NUMBER) IS
382 SELECT name
383 FROM okc_deliverable_types_tl oktl, okc_deliverables okd
384 WHERE oktl.deliverable_type_code =okd.DELIVERABLE_TYPE
385 AND okd.deliverable_id=p_del_id
386 AND oktl.language = userenv('LANG') ;
387
388 l_del_type_name okc_deliverable_types_tl.name%TYPE;
389
390 CURSOR c_resp_party_name(p_resp_party IN VARCHAR2) IS
391 SELECT name
392 FROM okc_resp_parties_tl
393 WHERE RESP_PARTY_CODE = p_resp_party
394 AND language = userenv('LANG');
395
396 l_resp_party_name okc_resp_parties_tl.name%TYPE;
397
398 CURSOR c_contact_name(p_contact IN varchar2) IS
399 SELECT full_name
400 FROM PER_ALL_PEOPLE_F
401 WHERE person_id=p_contact ;
402
403 l_ext_contact_name PER_ALL_PEOPLE_F.full_name%TYPE;
404 l_int_contact_name PER_ALL_PEOPLE_F.full_name%TYPE;
405
406 l_int_role_display_name wf_roles.display_name%TYPE;
407 l_req_role_display_name wf_roles.display_name%TYPE;
408 l_ext_role_display_name wf_roles.display_name%TYPE;
409 l_fnd_user_id fnd_user.user_id%TYPE;
410
411 TYPE l_user_email_list IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
412 l_user_tbl l_user_email_list;
413
414 i PLS_INTEGER := 0;
415 j PLS_INTEGER := 0;
416 k PLS_INTEGER := 0;
417 tmp_email_list VARCHAR2(8000);
418 l_user_list VARCHAR2(4000);
419
420 --Acq Plan Message Cleanup
421 l_resolved_msg_name1 VARCHAR2(30);
422 l_resolved_msg_name2 VARCHAR2(30);
423 l_resolved_msg_name3 VARCHAR2(30);
424 l_resolved_msg_name4 VARCHAR2(30);
425 l_resolved_msg_name5 VARCHAR2(30);
426 l_resolved_msg_name6 VARCHAR2(30);
427 l_resolved_msg_name7 VARCHAR2(30);
428 l_resolved_msg_name8 VARCHAR2(30);
429 l_resolved_msg_name VARCHAR2(30);
430 l_resolved_token VARCHAR2(100);
431 l_mode VARCHAR2(40);
432 BEGIN
433
434 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
435 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'100: Entering deliverables_notifier');
436 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'101: Notification type - '||p_msg_code);
437 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'102: Deliverable id - '||p_deliverable_id);
438 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'103: ExtContactId - '||p_external_contact);
439 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'104: IntContactId - '||p_internal_contact);
440 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'105: BusDocId:'||p_business_document_id);
441 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'106: BusDocType:'||p_business_document_type);
442 END IF;
443
444 OKC_API.init_msg_list(p_init_msg_list=>p_init_msg_list);
445
446 --Get item key from sequence
447 select to_char(okc_wf_notify_s1.nextval) into l_item_key from dual;
448
449 --get RESP_PARTY_CODE
450 OPEN getRespPartyCode;
451 FETCH getRespPartyCode INTO l_resp_party_code;
452 CLOSE getRespPartyCode;
453
454 -- populate busdoc type attributes
455 OPEN BUSDOC_TYPE;
456 FETCH BUSDOC_TYPE INTO busdoc_type_rec;
457 IF BUSDOC_TYPE%FOUND THEN
458 l_busdoctype_meaning := busdoc_type_rec.name;
459 l_doc_class := busdoc_type_rec.document_type_class;
460 l_external_function_name := busdoc_type_rec.external_userlist_proc;
461 l_header_function_name := busdoc_type_rec.notification_header_function;
462 END IF;
463 CLOSE BUSDOC_TYPE;
464
465 --Get DeliverableType Internal_flag
466 OPEN delTypeInternalFlag;
467 FETCH delTypeInternalFlag into l_internal_deliverable_type;
468 IF delTypeInternalFlag%NOTFOUND then
469 l_internal_deliverable_type := 'N';
470 END IF;
471 CLOSE delTypeInternalFlag;
472
473 /*--Repository Changes: Deliverable Type Internal flag obtained now from above cursor--
474 --check if we need to send to supplier
475 IF instr(p_deliverable_type,'INTERNAL')<>0 then
476 l_internal_deliverable_type := 'Y';
477 END IF; --internal deliv type
478 ------------------------------------------------------------------------------------*/
479 l_resolved_msg_name1 := OKC_API.resolve_message('OKC_DEL_ESCALATE_NTF_SUBJECT',p_business_document_type);
480 l_resolved_msg_name2 := OKC_API.resolve_message('OKC_DEL_BEFOREDUE_NTF_SUBJECT',p_business_document_type);
481
482 IF l_internal_deliverable_type = 'Y' OR
483 --p_msg_code = 'OKC_DEL_ESCALATE_NTF_SUBJECT' OR
484 -- (p_msg_code = 'OKC_DEL_BEFOREDUE_NTF_SUBJECT' and l_resp_party_code = 'INTERNAL_ORG') then
485 p_msg_code = 'OKC_DEL_ESCALATE_NTF_SUBJECT' OR p_msg_code = l_resolved_msg_name1 or
486 ((p_msg_code = 'OKC_DEL_BEFOREDUE_NTF_SUBJECT' OR p_msg_code = l_resolved_msg_name2) and l_resp_party_code = 'INTERNAL_ORG') then
487 l_internal_only := 'Y';
488 END IF;
489
490 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
491 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'107: Send to internal users only: '||l_internal_only);
492 END IF;
493
494 l_resolved_msg_name := OKC_API.resolve_message('OKC_DEL_COMPLETE_NTF_SUBJECT',p_business_document_type);
495 l_resolved_msg_name6 := OKC_API.resolve_message('OKC_DEL_CANCEL_NTF_SUBJECT',p_business_document_type);
496 l_resolved_msg_name7 := OKC_API.resolve_message('OKC_DEL_FAILED_NTF_SUBJECT',p_business_document_type);
497 l_resolved_msg_name8 := OKC_API.resolve_message('OKC_DEL_SUBMIT_NTF_SUBJECT',p_business_document_type);
498
499 --check if supplier gets FYI (he cant respond)
500 IF l_internal_only = 'N' and
501 (p_msg_code = 'OKC_DEL_COMPLETE_NTF_SUBJECT' OR
502 p_msg_code = l_resolved_msg_name OR
503 --p_msg_code = 'OKC_DEL_CANCEL_NTF_SUBJECT' OR
504 p_msg_code = 'OKC_DEL_CANCEL_NTF_SUBJECT' OR p_msg_code = l_resolved_msg_name6 or
505 --p_msg_code = 'OKC_DEL_FAILED_NTF_SUBJECT' OR
506 p_msg_code = 'OKC_DEL_FAILED_NTF_SUBJECT' OR p_msg_code = l_resolved_msg_name7 or
507 -- p_msg_code = 'OKC_DEL_SUBMIT_NTF_SUBJECT') then
508 p_msg_code = 'OKC_DEL_SUBMIT_NTF_SUBJECT' OR p_msg_code = l_resolved_msg_name8) then
509 l_external_is_fyi := 'Y';
510 END IF;
511
512 --Find the rolenames for the internal and external user whether they be adhoc or fnd_users
513 --only need to process external user in some cases
514 IF l_internal_only='N' then
515
516 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
517 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'108: Start, Role name for External contact ');
518 END IF;
519
520 -- if external contact found on Deliverable
521 IF p_external_contact IS NOT NULL THEN
522
523 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
524 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'109: Found External contact on Deliverable = '||p_external_contact);
525 END IF;
526
527 -- find user id for the given external contact
528 OPEN ext_user;
529 FETCH ext_user into l_fnd_user_id;
530 CLOSE ext_user;
531
532 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
533 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'110: External contact IS A FND USER = '||l_fnd_user_id);
534 END IF;
535
536 IF l_fnd_user_id IS NOT NULL THEN
537
538 -- fetch wf role name and display name
539 WF_DIRECTORY.GetUserName (p_orig_system => 'FND_USR',
540 p_orig_system_id => l_fnd_user_id,
541 p_name => l_external_role_name,
542 p_display_name => l_ext_role_display_name);
543
544 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
545 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'111: External Role Name = '||l_external_role_name);
546 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'112: External Role Display Name = '||l_ext_role_display_name);
547 END IF;
548
549 ELSE
550
551 -- fetch wf role name and display name, when contact is not registered as FND_USER
552 WF_DIRECTORY.GetUserName (p_orig_system => 'HZ_PARTY',
553 p_orig_system_id => p_external_contact,
554 p_name => l_external_role_name,
555 p_display_name => l_ext_role_display_name);
556
557 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
558 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'113: External Role Name = '||l_external_role_name);
559 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'114: External Role Display Name = '||l_ext_role_display_name);
560 END IF;
561
562 END IF; --IF l_fnd_user_id IS NOT NULL
563
564 ELSIF (p_external_contact IS NULL AND l_external_function_name is not null) THEN
565
566 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
567 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'115: External Contact is NULL, External List Function = '||l_external_function_name);
568 END IF;
569
570 -- fetch party contact and party role from Deliverable
571 OPEN del_cur;
572 FETCH del_cur INTO del_rec;
573 l_external_party_id := del_rec.external_party_id;
574 l_external_party_role := del_rec.external_party_role;
575 CLOSE del_cur;
576
577 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
578 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'116: External Party Id = '||l_external_party_id);
579 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'117: External Party Role = '||l_external_party_role);
580 END IF;
581
582 Begin--start procedure execution to get userlist
583
584 IF l_doc_class = 'REPOSITORY' THEN
585 l_sql_string := 'begin '||l_external_function_name|| '(p_api_version=>:p_api_version
586 , p_init_msg_list=>:p_init_msg_list, p_document_id=>:p_document_id
587 , p_document_type=>:p_document_type, p_external_party_id => :p_external_party_id
588 , p_external_party_role => :p_external_party_role
589 , x_return_status=>:x_return_status, x_msg_count=>:x_msg_count
590 , x_msg_data=>:x_msg_data, x_external_userlist=>:x_external_userlist); end;';
591
592 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
593 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'118: Calling RESPOSITORY FUNCTION');
594 END IF;
595
596 EXECUTE IMMEDIATE l_sql_string using
597 in p_api_version,
598 in p_init_msg_list,
599 in p_business_document_id,
600 in p_business_document_type,
601 in l_external_party_id,
602 in l_external_party_role,
603 out x_return_status,
604 out x_msg_count,
605 out x_msg_data,
606 out l_ext_users;
607
608 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
609 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'119: DONE RESPOSITORY FUNCTION, with list = '||l_ext_users);
610 END IF;
611
612 ELSE -- if class is not REPOSITORY
613
614 l_sql_string := 'begin '||l_external_function_name||'(p_api_version=>:p_api_version,
615 p_document_id=>:p_document_id,
616 p_document_type=>:p_document_type,
617 p_external_contact_id => :p_external_contact_id,
618 x_return_status=>:x_return_status,
619 x_msg_count=>:x_msg_count,
620 x_msg_data=>:x_msg_data,
621 x_external_userlist=>:x_external_userlist); end;';
622
623 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
624 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'120: Calling OTHER DOC FUNCTION');
625 END IF;
626
627 EXECUTE IMMEDIATE l_sql_string using
628 in p_api_version,
629 in p_business_document_id,
630 in p_business_document_type,
631 in p_external_contact,
632 out x_return_status,
633 out x_msg_count,
634 out x_msg_data,
635 out l_ext_users;
636
637 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
638 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'121: DONE OTHER DOC FUNCTION, with list = '||l_ext_users);
639 END IF;
640
641 END IF; -- IF l_doc_class = 'REPOSITORY'
642
643 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
644 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'122: Got External Users list = '||l_ext_users);
645 END IF;
646
647 IF x_return_status <> G_RET_STS_SUCCESS OR l_ext_users IS NULL then
648 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
649 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'123: ERROR finding External Users List');
650 END IF;
651 l_ext_contact_error :='Y';
652
653 Okc_Api.Set_Message(p_app_name=>G_APP_NAME,
654 p_msg_name=>'OKC_DEL_NTF_EXT_USER_NOT_FOUND',
655 p_token1 => 'BUSDOCTYPE',
656 p_token1_value => l_busdoctype_meaning,
657 p_token2 => 'BUSDOCNUM',
658 p_token2_value => p_business_document_number);
659 x_return_status := G_RET_STS_ERROR;
660
661 ELSE
662
663 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
664 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'124: Create Adhoc Role');
665 END IF;
666
667 IF l_doc_class = 'REPOSITORY' THEN
668 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
669 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'125: For REPOSITORY, create adhoc role using list of users Email addresses');
670 END IF;
671
672 -- copy list of email addresses to temp list
673 tmp_email_list := l_ext_users;
674 LOOP
675 i := INSTR(tmp_email_list,',');
676 IF i > 0 THEN
677 -- comma found
678 l_user_tbl(j) := SUBSTR(tmp_email_list,1,i-1);
679 tmp_email_list := SUBSTR(tmp_email_list,i+1, length(tmp_email_list) - i);
680 j := j + 1;
681 ELSE
682 -- no comma found i.e last contract id
683 l_user_tbl(j) := tmp_email_list;
684 EXIT;
685 END IF;
686 END LOOP;
687 -- for each email create a adhoc user
688 FOR k IN NVL(l_user_tbl.FIRST,0)..NVL(l_user_tbl.LAST,-1)
689 LOOP
690 l_external_role_name := '';
691 BEGIN
692 WF_DIRECTORY.CreateAdHocUser(
693 name => l_external_role_name,
694 display_name => l_external_role_name,
695 language => null,
696 territory => null,
697 description => 'Deliverables Ad hoc user',
698 notification_preference => 'MAILHTML',
699 email_address => l_user_tbl(k),
700 status => 'ACTIVE',
701 expiration_date => SYSDATE+1 ,
702 parent_orig_system => null,
703 parent_orig_system_id => null);
704 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
705 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'127: Role Name = '||l_external_role_name);
706 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'128: Role Display Name = '||l_ext_role_display_name);
707 END IF;
708 -- build concatinated list of user name for adhoc role
709 l_user_list := l_user_list||','||l_external_role_name;
710 EXCEPTION
711 WHEN OTHERS THEN
712 FND_MESSAGE.SET_NAME('OKC', 'OKC_CREATE_ADHOC_USER_FAILED');
713 FND_MESSAGE.set_token('USER_NAME',l_user_tbl(k));
714 FND_MESSAGE.set_token('SQL_ERROR',SQLERRM);
715 FND_MSG_PUB.add;
716 RAISE FND_API.G_EXC_ERROR;
717 END;
718 END LOOP;
719
720 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
721 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'128a: Concantenated List of Adhoc users = '||l_user_list);
722 END IF;
723
724 -- call wf api to create the adhoc role
725 BEGIN
726 l_external_role_name := '';
727 l_ext_role_display_name := '';
728 Wf_Directory.CreateAdHocRole
729 (
730 role_name => l_external_role_name,
731 role_display_name => l_ext_role_display_name,
732 language => null,
733 territory => null,
734 role_description => 'Deliverables Ad hoc role',
735 notification_preference => 'MAILHTML',
736 role_users => l_user_list,
737 status => 'ACTIVE',
738 expiration_date => SYSDATE+1,
739 parent_orig_system => null,
740 parent_orig_system_id => null,
741 owner_tag => null
742 );
743 EXCEPTION
744 WHEN OTHERS THEN
745 FND_MESSAGE.SET_NAME('OKC','OKC_CREATE_ADHOC_ROLE_FAILED');
746 FND_MESSAGE.set_token('ROLE_NAME',l_external_role_name);
747 FND_MESSAGE.set_token('SQL_ERROR',SQLERRM);
748 FND_MSG_PUB.add;
749 RAISE FND_API.G_EXC_ERROR;
750 END;
751
752 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
753 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'128b: DONE adhoc role creation');
754 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'128c: Role Name = '||l_external_role_name);
755 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'128d: Role Display Name = '||l_ext_role_display_name);
756 END IF;
757
758 ELSIF (instr(l_ext_users,',')=0) THEN
759 /* only 1 user, no need to create role */
760 l_external_role_name := l_ext_users;
761 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
762 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'129: NOT REPOSITORY CLASS and got only 1 User from the List = '||l_external_role_name);
763 END IF;
764 ELSE
765 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
766 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'130: NOT REPOSITORY CLASS and got multiple Users');
767 END IF;
768
769 BEGIN
770 l_external_role_name := '';
771 l_ext_role_display_name := '';
772
773 /* create the ad hoc role using the users obtained from the userlist procedure */
774 WF_DIRECTORY.createAdHocRole(role_name =>l_external_role_name,
775 role_display_name =>l_ext_role_display_name,
776 language =>null,
777 territory =>null,
778 role_description =>'Deliverables Ad hoc role',
779 notification_preference =>'MAILHTML',
780 role_users =>l_ext_users,
781 status => 'ACTIVE',
782 expiration_date => SYSDATE+1,
783 parent_orig_system => null,
784 parent_orig_system_id => null,
785 owner_tag => null);
786
787 EXCEPTION
788 WHEN OTHERS THEN
789 FND_MESSAGE.SET_NAME('OKC','OKC_CREATE_ADHOC_ROLE_FAILED');
790 FND_MESSAGE.set_token('ROLE_NAME',l_external_role_name);
791 FND_MESSAGE.set_token('SQL_ERROR',SQLERRM);
792 FND_MSG_PUB.add;
793 RAISE FND_API.G_EXC_ERROR;
794 END;
795
796 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
797 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'131: DONE adhoc role creation');
798 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'132: Role Name = '||l_external_role_name);
799 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'133: Role Display Name = '||l_ext_role_display_name);
800 END IF;
801
802 END IF; -- IF l_doc_class = 'REPOSITORY'
803 END IF; --IF x_return_status <> G_RET_STS_SUCCESS OR l_ext_users IS NULL
804 End;--End of procedure execution to get userlist
805
806 ELSIF (p_external_contact IS NULL AND l_external_function_name is null) THEN
807 l_ext_contact_error :='Y';
808
809 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
810 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'134: External Contact and External List Function BOTH are NULL, Error = '||l_ext_contact_error);
811 END IF;
812
813 Okc_Api.Set_Message(p_app_name=>G_APP_NAME,
814 p_msg_name=>'OKC_DEL_NTF_EXT_USER_NOT_FOUND',
815 p_token1 => 'BUSDOCTYPE',
816 p_token1_value => l_busdoctype_meaning,
817 p_token2 => 'BUSDOCNUM',
818 p_token2_value => p_business_document_number);
819 x_return_status := G_RET_STS_ERROR;
820
821 END IF; -- p_external_contact IS NULL AND l_external_function_name is not null
822
823 END IF; -- l_internal_only = N end process external user check
824
825 -- Now Create Internal Roles
826 l_internal_role_name := get_internal_user_role(p_internal_contact, l_int_role_display_name);
827 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
828 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'135: Internal Contact Role Name = '||l_internal_role_name);
829 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'136: Internal Contact Role Display Name = '||l_int_role_display_name);
830 END IF;
831
832 IF l_internal_role_name IS NULL THEN
833 Okc_Api.Set_Message(G_APP_NAME,'OKC_DEL_NTF_INT_USER_NO_EMAIL');
834 x_return_status := G_RET_STS_ERROR;
835 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
836 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'137: Invalid Internal contact Id (no email), raising error');
837 END IF;
838
839 RAISE OKC_API.G_EXCEPTION_ERROR;
840 END IF; --end internal user not found
841
842 l_requester_role_name := get_internal_user_role(p_requester_id, l_req_role_display_name);
843 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
844 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'138: Requestor Contact Role Name = '||l_requester_role_name);
845 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'139: Requestor Contact Role Display Name = '||l_req_role_display_name);
846 END IF;
847
848
849 --Acq Plan Message Cleanup
850 l_resolved_msg_name1 := OKC_API.resolve_message('OKC_DEL_OVERDUE_NTF_SUBJECT',p_business_document_type);
851 l_resolved_msg_name2 := OKC_API.resolve_message('OKC_DEL_BEFOREDUE_NTF_SUBJECT',p_business_document_type);
852 l_resolved_msg_name3 := OKC_API.resolve_message('OKC_DEL_ESCALATE_NTF_SUBJECT',p_business_document_type);
853 l_resolved_msg_name4 := OKC_API.resolve_message('OKC_DEL_REOPEN_NTF_SUBJECT',p_business_document_type);
854 l_resolved_msg_name5 := OKC_API.resolve_message('OKC_DEL_REJECT_NTF_SUBJECT',p_business_document_type);
855
856 --Logic to determine which workflow process to choose
857 if l_internal_deliverable_type = 'Y' OR l_ext_contact_error = 'Y' OR
858 -- p_msg_code = 'OKC_DEL_ESCALATE_NTF_SUBJECT' then
859 p_msg_code = 'OKC_DEL_ESCALATE_NTF_SUBJECT' OR p_msg_code = l_resolved_msg_name3 then
860 --Internal Deliverables and missing ext contacts
861 l_process_name := 'OKCDELNOTIFYINTERNAL';
862 --elsif ((p_msg_code='OKC_DEL_REOPEN_NTF_SUBJECT' or
863 elsif ((p_msg_code='OKC_DEL_REOPEN_NTF_SUBJECT' OR p_msg_code = l_resolved_msg_name4 OR
864 --p_msg_code='OKC_DEL_REJECT_NTF_SUBJECT' OR
865 p_msg_code='OKC_DEL_REJECT_NTF_SUBJECT' OR p_msg_code = l_resolved_msg_name5 or
866 --Acq Plan Messages Cleanup
867 --p_msg_code='OKC_DEL_OVERDUE_NTF_SUBJECT') and
868 p_msg_code='OKC_DEL_OVERDUE_NTF_SUBJECT' OR p_msg_code = l_resolved_msg_name1 ) and
869 l_resp_party_code <> 'INTERNAL_ORG') then
870 --Deliverables where external user can submit thru notification
871 l_process_name :='OKCDELNOTIFYBOTH';
872 --elsif (p_msg_code='OKC_DEL_BEFOREDUE_NTF_SUBJECT') then
873 elsif (p_msg_code='OKC_DEL_BEFOREDUE_NTF_SUBJECT' OR p_msg_code=l_resolved_msg_name2) then
874 --Only responsible party
875 l_process_name :='OKCDELNOTIFYRESPPARTY';
876 else
877 --FYI to all parties
878 l_process_name := 'OKCDELNOTIFYFYI';
879 end if;
880
881 --Create the process
882 wf_engine.CreateProcess(itemtype => l_item_type,
883 itemkey => l_item_key,
884 process => l_process_name);
885 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
886 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'140: Creating process:'||l_item_type||':'
887 ||l_item_key||':'||l_process_name);
888 END IF;
889
890 wf_engine.SetItemUserKey (itemtype => l_item_type,
891 itemkey => l_item_key,
892 userkey => l_item_key);
893 wf_engine.SetItemOwner (itemtype => l_item_type,
894 itemkey => l_item_key,
895 owner => fnd_global.user_name);
896 -- Set global attributes
897 wf_engine.SetItemAttrNumber (itemtype => l_item_type,
898 itemkey => l_item_key,
899 aname => 'OKCDELID',
900 avalue => p_deliverable_id);
901
902 wf_engine.SetItemAttrText (itemtype => l_item_type,
903 itemkey => l_item_key,
904 aname => 'OKCDELINTUSERROLE',
905 avalue => l_internal_role_name);
906
907 wf_engine.SetItemAttrText (itemtype => l_item_type,
908 itemkey => l_item_key,
909 aname => 'OKCDELSUBJECT',
910 avalue => p_msg_code||':'||p_deliverable_id);
911
912 IF l_requester_role_name IS NOT NULL THEN
913 wf_engine.SetItemAttrText (itemtype => l_item_type,
914 itemkey => l_item_key,
915 aname => 'OKCDELREQUESTOR',
916 avalue => l_requester_role_name);
917
918 wf_engine.SetItemAttrText (itemtype => l_item_type,
919 itemkey => l_item_key,
920 aname => 'OKCDELREQUESTEREXISTS',
921 avalue => 'Y');
922 END IF;
923
924 wf_engine.SetItemAttrText (itemtype => l_item_type,
925 itemkey => l_item_key,
926 aname => 'OKCDELEXTUSERROLE',
927 avalue => l_external_role_name);
928 /*
929 l_ui_region := 'JSP:/OA_HTML/OA.jsp?OAFunc=OKC_DEL_NOTIF_EMBEDDED_RN&BUSDOCCONTEXT_REGION_PATH='
930 ||l_header_function_name||'&documentHeaderId='
931 ||p_business_document_id||'&OKC_DOCUMENT_TYPE='||p_business_document_type
932 ||'&OKC_DOC_VER_NUM='||p_business_document_version
933 ||'&_MANAGE_MODE=Y&_UPDATE_STATUS_MODE=N&_HIDE_NTF=Y&OKC_DEL_HIDE_ATTACHMENTS=Y&OKC_DEL_HIDE_STATUS_DISC=Y&_FLEX_DISPLAY=N&_DELIVERABLE_ID='||p_deliverable_id||'&OKC_DEL_NO_ENCRYPT=Y';
934 wf_engine.SetItemAttrText (itemtype => l_item_type,
935 itemkey => l_item_key,
936 aname => 'OKCDELBUSDOCDETAILSRN',
937 avalue => l_ui_region);
938 */
939
940 OPEN c_del_type(p_deliverable_id);
941 FETCH c_del_type INTO l_del_type_name;
942 CLOSE c_del_type;
943
944 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
945 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'l_del_type_name = '||l_del_type_name);
946 END IF;
947
948 OPEN c_resp_party_name(p_resp_party);
949 FETCH c_resp_party_name INTO l_resp_party_name;
950 CLOSE c_resp_party_name;
951
952
953 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
954 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'l_resp_party_name = '||l_resp_party_name);
955 END IF;
956
957 OPEN c_contact_name(p_external_contact);
958 FETCH c_contact_name INTO l_ext_contact_name;
959 CLOSE c_contact_name;
960
961 OPEN c_contact_name(p_internal_contact);
962 FETCH c_contact_name INTO l_int_contact_name;
963 CLOSE c_contact_name;
964
965
966 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
967 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'l_ext_contact_name = '||l_ext_contact_name||' l_int_contact_name= '||l_int_contact_name);
968 END IF;
969
970
971 wf_engine.setitemattrtext(itemtype => l_item_type,
972 itemkey => l_item_key,
973 aname => 'OKCDELMSGBODY',
974 avalue => 'PLSQLCLOB:OKC_DELIVERABLE_WF_PVT.get_notfn_body/'||p_business_document_type||':'||p_deliverable_name||':'||l_del_type_name||':'||
975 l_resp_party_name||':'||l_int_contact_name||':'||l_ext_contact_name);
976
977 l_mode:='EDIT_DOC_DETAILS';
978 /*skuchima*/
979 l_ui_region := 'JSP:/OA_HTML/OA.jsp?page=/oracle/apps/okc/deliverables/webui/NotificationDelRN&deliverableId='
980 ||p_deliverable_id||'&deliverableName='||p_deliverable_name||'&delBusDocNum='||p_business_document_number
981 ||'&delBusDocVersion='||p_business_document_version||'&delBusDocTypeCode='||p_business_document_type||'&busDocId='||p_business_document_id||'&docViewDetailsFunction='||l_header_function_name ||'&pmode='||l_mode||'&docClass='||l_doc_class;
982
983 wf_engine.SetItemAttrText (itemtype => l_item_type,
984 itemkey => l_item_key,
985 aname => 'OKCDELDOCDTLSEDITRN',
986 avalue => l_ui_region);
987
988
989 l_mode:='VIEW_DOC_DETAILS';
990 /*skuchima*/
991 l_ui_region := 'JSP:/OA_HTML/OA.jsp?page=/oracle/apps/okc/deliverables/webui/NotificationDelRN&deliverableId='
992 ||p_deliverable_id||'&deliverableName='||p_deliverable_name||'&delBusDocNum='||p_business_document_number
993 ||'&delBusDocVersion='||p_business_document_version||'&delBusDocTypeCode='||p_business_document_type||'&busDocId='||p_business_document_id||'&docViewDetailsFunction='||l_header_function_name ||'&pmode='||l_mode||'&docClass='||l_doc_class;
994
995 wf_engine.SetItemAttrText (itemtype => l_item_type,
996 itemkey => l_item_key,
997 aname => 'OKCDELDOCDTLSVIEWRN',
998 avalue => l_ui_region);
999
1000 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1001 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'141: Setting workflow attributes.');
1002 END IF;
1003
1004 wf_engine.SetItemAttrText (itemtype => l_item_type,
1005 itemkey => l_item_key,
1006 aname => 'FROMROLE',
1007 avalue => l_internal_role_name);
1008
1009 wf_engine.SetItemAttrText (itemtype => l_item_type,
1010 itemkey => l_item_key,
1011 aname => 'OKCDELRESPPARTY',
1012 avalue => l_resp_party_code);
1013
1014 wf_engine.StartProcess(itemtype => l_item_type,
1015 itemkey => l_item_key);
1016
1017 --Even though we've sent notifications already, if there was an error with the external contact, we want to send an error notification.
1018 IF l_ext_contact_error = 'Y' THEN
1019 RAISE OKC_API.G_EXCEPTION_ERROR;
1020 END IF;
1021
1022 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1023 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'142: Subject of notification:'||fnd_message.get);
1024 END IF;
1025
1026 --logic to get the notification id for the OUT parameter
1027 IF l_internal_only = 'Y' THEN
1028 x_notification_id:=wf_engine.GetItemAttrNumber (itemtype => l_item_type,
1029 itemkey => l_item_key,
1030 aname =>'OKCDELINTNOTIFID');
1031 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1032 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'143: Notification ID is for internal user:'||x_notification_id);
1033 END IF;
1034
1035 ELSE
1036 IF l_external_is_fyi = 'Y' THEN
1037 x_notification_id:=wf_engine.GetItemAttrNumber (itemtype => l_item_type,
1038 itemkey => l_item_key,
1039 aname => 'OKCDELEXTNOTIFID');
1040 ELSE
1041 select notification_id into x_notification_id from wf_item_activity_statuses
1042 where item_type = l_item_type and
1043 item_key = l_item_key and
1044 assigned_user = l_external_role_name;
1045 END IF;
1046
1047 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1048 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'144: Getting Notification ID for ext user from attribute:'
1049 ||x_notification_id);
1050 END IF;
1051 END IF; --end internal notifications
1052
1053 IF x_notification_id IS NOT NULL THEN
1054 x_return_status:= G_RET_STS_SUCCESS;
1055
1056 FND_FILE.PUT_LINE(FND_FILE.LOG,'Returning notification Id: '||to_char(x_notification_id));
1057 FND_FILE.PUT_LINE(FND_FILE.LOG,'Notifier returning status: '||x_return_status);
1058 ELSE
1059 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1060 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1061 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module||l_api_name,'145: null notification ID');
1062 END IF;
1063 END IF; --end check notification id
1064
1065 EXCEPTION
1066 WHEN OTHERS THEN
1067 If delTypeInternalFlag%ISOPEN then
1068 CLOSE delTypeInternalFlag;
1069 End If;
1070 If getRespPartyCode%ISOPEN then
1071 CLOSE getRespPartyCode;
1072 End If;
1073 If BUSDOC_TYPE%ISOPEN then
1074 CLOSE BUSDOC_TYPE;
1075 End If;
1076 If del_cur%ISOPEN then
1077 CLOSE del_cur;
1078 End If;
1079 If ext_user%ISOPEN then
1080 CLOSE ext_user;
1081 End If;
1082 If ext_user_email%ISOPEN then
1083 CLOSE ext_user_email;
1084 End If;
1085
1086 FND_MSG_PUB.Count_And_Get(p_encoded=>'F'
1087 , p_count => x_msg_count
1088 , p_data => x_msg_data );
1089
1090 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error message count: '||to_char(x_msg_count));
1091
1092 FOR i IN 1..x_msg_count LOOP
1093 FND_FILE.PUT_LINE(FND_FILE.LOG,
1094 'Error message number '||i||': '||FND_MSG_PUB.get(p_msg_index => i,p_encoded => 'F'));
1095 END LOOP;
1096
1097 if x_msg_count > 1 then
1098 x_msg_data := substr(FND_MSG_PUB.get(p_msg_index => x_msg_count,
1099 p_encoded => 'F'),1,250);
1100
1101 x_msg_count := 1;
1102 end if;
1103
1104 l_error_msg := x_msg_data;
1105
1106 -- send notification to the logged in user that workflow notification
1107 -- could not be sent
1108 x_notification_id := WF_NOTIFICATION.Send(role => fnd_global.user_name,
1109 msg_type => 'OKCDELWF',
1110 msg_name => 'OKCDELFAILEDTOSENDMSG');
1111 WF_NOTIFICATION.SetAttrText(nid=>x_notification_id,
1112 aname=>'DELIVERABLENAME',
1113 avalue=>p_deliverable_name);
1114 WF_NOTIFICATION.SetAttrText(nid=>x_notification_id,
1115 aname=>'SUBJECT',
1116 avalue=>l_subject_text);
1117 WF_NOTIFICATION.SetAttrText(nid=>x_notification_id,
1118 aname=>'ERRORMSG',
1119 avalue=>l_error_msg);
1120 WF_NOTIFICATION.SetAttrText(nid=>x_notification_id,
1121 aname=>'#FROM_ROLE',
1122 avalue=> fnd_global.user_name);
1123
1124 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error notification Id: '||to_char(x_notification_id));
1125 FND_FILE.PUT_LINE(FND_FILE.LOG,'Notifier returning status: '||x_return_status);
1126
1127 x_return_status := G_RET_STS_UNEXP_ERROR ;
1128
1129 /* commented to avoid display problem in OA page if there are more than
1130 one message in the stack.
1131 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1132 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1133 END IF;*/
1134 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1135 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module,'700: Leaving Deliverable Notifier with G_RET_STS_UNEXP_ERROR');
1136 END IF;
1137
1138 END deliverables_notifier;
1139
1140
1141 -------------------------------------------------------------------------------
1142 --Start of Comments
1143 --Name: set_int_notif_id
1144 --Function:
1145 -- This function determines sets a workflow attribute for either internal user the notification id of the notification that was just sent.
1146 --Testing:
1147 --
1148 --End of Comments
1149 -------------------------------------------------------------------------------
1150
1151 procedure set_int_notif_id (itemtype in varchar2,
1152 itemkey in varchar2,
1153 actid in number,
1154 funcmode in varchar2,
1155 resultout out nocopy varchar2 )
1156
1157 IS
1158 l_requestor VARCHAR2(100);
1159 l_internal_role_name VARCHAR2(100);
1160
1161 BEGIN
1162
1163 if ( funcmode = 'RUN' ) then
1164
1165 --Store the internal users nid in case we need to return it
1166 wf_engine.SetItemAttrNumber (itemtype => itemtype,
1167 itemkey => itemkey,
1168 aname => 'OKCDELINTNOTIFID',
1169 avalue => wf_Engine.g_nid);
1170
1171 resultout:='COMPLETE';
1172 return;
1173 end if;
1174
1175
1176 if ( funcmode = 'CANCEL' ) then
1177
1178 resultout := 'COMPLETE';
1179 return;
1180 end if;
1181 if ( funcmode = 'RESPOND') then
1182 resultout := 'COMPLETE';
1183 return;
1184 end if;
1185 if ( funcmode = 'FORWARD') then
1186 resultout := 'COMPLETE';
1187 return;
1188 end if;
1189 if ( funcmode = 'TRANSFER') then
1190 resultout := 'COMPLETE';
1191 return;
1192 end if;
1193 if ( funcmode = 'TIMEOUT' ) then
1194 resultout := 'COMPLETE';
1195 else
1196 resultout := wf_engine.eng_timedout;
1197 return;
1198 end if;
1199
1200 exception
1201 when others then
1202 WF_CORE.CONTEXT ('OKCDELWF', 'set_notif_id', itemtype, itemkey,actid,funcmode);
1203 resultout := 'ERROR';
1204 raise;
1205
1206
1207
1208 end set_int_notif_id;
1209
1210 -------------------------------------------------------------------------------
1211 --Start of Comments
1212 --Name: set_ext_notif_id
1213 --Function:
1214 -- This function determines sets a workflow attribute for the external user the notification id of the notification that was just sent.
1215 --Testing:
1216 --
1217 --End of Comments
1218 -------------------------------------------------------------------------------
1219
1220 procedure set_ext_notif_id (itemtype in varchar2,
1221 itemkey in varchar2,
1222 actid in number,
1223 funcmode in varchar2,
1224 resultout out nocopy varchar2 )
1225
1226 IS
1227
1228 BEGIN
1229
1230 if ( funcmode = 'RUN' ) then
1231
1232 --Store the internal users nid in case we need to return it
1233 wf_engine.SetItemAttrNumber (itemtype => itemtype,
1234 itemkey => itemkey,
1235 aname => 'OKCDELEXTNOTIFID',
1236 avalue => wf_Engine.g_nid);
1237
1238 resultout:='COMPLETE';
1239 return;
1240 end if;
1241
1242
1243 if ( funcmode = 'CANCEL' ) then
1244
1245 resultout := 'COMPLETE';
1246 return;
1247 end if;
1248 if ( funcmode = 'RESPOND') then
1249 resultout := 'COMPLETE';
1250 return;
1251 end if;
1252 if ( funcmode = 'FORWARD') then
1253 resultout := 'COMPLETE';
1254 return;
1255 end if;
1256 if ( funcmode = 'TRANSFER') then
1257 resultout := 'COMPLETE';
1258 return;
1259 end if;
1260 if ( funcmode = 'TIMEOUT' ) then
1261 resultout := 'COMPLETE';
1262 else
1263 resultout := wf_engine.eng_timedout;
1264 return;
1265 end if;
1266
1267 exception
1268 when others then
1269 WF_CORE.CONTEXT ('OKCDELWF', 'set_ext_notif_id', itemtype, itemkey,actid,funcmode);
1270 resultout := 'ERROR';
1271 raise;
1272
1273 end set_ext_notif_id;
1274
1275
1276 -------------------------------------------------------------------------------
1277 --Start of Comments
1278 --Name: update_status
1279 --Function:
1280 -- This function is called after an external user clicks the 'SUBMIT DELIVERABLE'
1281 -- button on a notification. The function updates that deliverable's status to SUBMITTED,
1282 -- and creates an entry in the status history table.
1283 --Testing:
1284 --
1285 --End of Comments
1286 -------------------------------------------------------------------------------
1287
1288 procedure update_status (itemtype in varchar2,
1289 itemkey in varchar2,
1290 actid in number,
1291 funcmode in varchar2,
1292 resultout out nocopy varchar2 )
1293
1294
1295 IS
1296 l_old_status VARCHAR2(100);
1297 l_notes VARCHAR2(2000);
1298 l_deliverable_id NUMBER;
1299 l_new_status VARCHAR2(100);
1300 l_temp VARCHAR2(1);
1301
1302
1303 cursor update_allowed is
1304 select 'X' -- removed into l_temp for 8174 compatability bug#3288934
1305 from okc_deliverables d,okc_del_status_combxns s where
1306 d.deliverable_id=l_deliverable_id and
1307 s.current_status_code=d.deliverable_status and
1308 s.allowable_status_code=l_new_status and
1309 s.status_changed_by='EXTERNAL' and
1310 d.manage_yn = 'Y'; --from bug 3696869
1311
1312 BEGIN
1313
1314 l_notes := wf_engine.GetItemAttrText (itemtype => itemtype,
1315 itemkey => itemkey,
1316 aname => 'NOTES2',ignore_notfound=>true);
1317
1318 l_deliverable_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
1319 itemkey => itemkey,
1320 aname => 'OKCDELID');
1321
1322 l_new_status := WF_ENGINE.GetItemAttrText(itemtype,itemkey,'RESULT');
1323
1324 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1325 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module,'100: Entering update status for deliverable id:'||l_deliverable_id);
1326 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module,'100: New status will be:'||l_new_status);
1327
1328 end if;
1329
1330
1331 if l_new_status IS NOT NULL then
1332
1333 open update_allowed;
1334 fetch update_allowed into l_temp;
1335 if update_allowed%NOTFOUND then
1336 resultout:='COMPLETE';
1337 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1338 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module,'200: Update is not allow from current status to '
1339 ||l_new_status||' by external user. Exitting');
1340 end if;
1341
1342 else
1343 update okc_deliverables set deliverable_status = l_new_status, status_change_notes=l_notes
1344 where deliverable_id = l_deliverable_id;
1345
1346 insert into okc_del_status_history(deliverable_id,
1347 deliverable_status,
1348 status_change_date,
1349 status_change_notes,
1350 object_version_number,
1351 created_by,
1352 creation_date,
1353 last_updated_by,
1354 last_update_date,
1355 last_update_login,
1356 status_changed_by)
1357 values (l_deliverable_id,
1358 l_new_status,
1359 sysdate,
1360 l_notes,
1361 1,
1362 fnd_global.user_id,
1363 sysdate,
1364 fnd_global.user_id,
1365 sysdate,
1366 fnd_global.login_id,
1367 fnd_global.user_id);
1368 IF ( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1369 FND_LOG.STRING( FND_LOG.LEVEL_PROCEDURE ,g_module,'200: Deliverable updated and row inserted into status history');
1370 end if;
1371
1372
1373 end if; -- end updating status
1374 close update_allowed;
1375 end if;
1376
1377 if ( funcmode = 'RUN' ) then
1378
1379 resultout:='COMPLETE';
1380
1381 return;
1382 end if;
1383
1384
1385 if ( funcmode = 'CANCEL' ) then
1386
1387 resultout := 'COMPLETE';
1388 return;
1389 end if;
1390 if ( funcmode = 'RESPOND') then
1391
1392 resultout := 'COMPLETE';
1393 return;
1394 end if;
1395 if ( funcmode = 'FORWARD') then
1396 resultout := 'COMPLETE';
1397 return;
1398 end if;
1399 if ( funcmode = 'TRANSFER') then
1400 resultout := 'COMPLETE';
1401 return;
1402 end if;
1403 if ( funcmode = 'TIMEOUT' ) then
1404 resultout := 'COMPLETE';
1405 else
1406 resultout := wf_engine.eng_timedout;
1407 return;
1408 end if;
1409
1410 exception
1411 when others then
1412 WF_CORE.CONTEXT ('OKCDELWF', 'update_status', itemtype, itemkey,actid,funcmode);
1413 resultout := 'ERROR';
1414 raise;
1415
1416 end update_status;
1417
1418
1419
1420 -------------------------------------------------------------------------------
1421 --Start of Comments
1422 --Name: send_notification_bus_event
1423 --Function:
1424 -- This function is called from an business event subscription. The two parameters are DELIVERABLE_ID and MSG_CODE. It will query all the rest of the required info and call deliverables_notifier.
1425 -- It will set the sent notification's id in the OKC_DELIVERABLES table
1426 --Testing:
1427 --
1428 --End of Comments
1429 -------------------------------------------------------------------------------
1430
1431 function send_notification_bus_event (p_subscription_guid in raw,
1432 p_event in out nocopy WF_EVENT_T) return varchar2 is
1433
1434
1435
1436 l_deliverable_id NUMBER;
1437 l_msg_code VARCHAR2(30);
1438 l_notification_id NUMBER;
1439 l_msg_data VARCHAR2(2000);
1440 l_msg_count NUMBER;
1441 l_return_status VARCHAR2(1);
1442 l_event_status VARCHAR2(10);
1443
1444 CURSOR del_cur IS
1445 SELECT *
1446 FROM okc_deliverables
1447 where deliverable_id = l_deliverable_id;
1448
1449 l_del_rec del_cur%ROWTYPE;
1450
1451
1452 begin
1453
1454 l_deliverable_id := p_event.GetValueForParameter('DELIVERABLE_ID');
1455 l_msg_code := p_event.GetValueForParameter('MSG_CODE');
1456
1457 open del_cur;
1458 fetch del_cur into l_del_rec;
1459 close del_cur;
1460
1461 okc_deliverable_wf_pvt.deliverables_notifier(
1462 p_api_version => 1.0,
1463 p_init_msg_list => FND_API.G_TRUE,
1464 p_deliverable_id => l_del_rec.deliverable_id,
1465 p_deliverable_name => l_del_rec.deliverable_name,
1466 p_deliverable_type => l_del_rec.deliverable_type,
1467 p_business_document_id => l_del_rec.business_document_id,
1468 p_business_document_version => l_del_rec.business_document_version,
1469 p_business_document_type => l_del_rec.business_document_type,
1470 p_business_document_number => l_del_rec.business_document_number,
1471 p_resp_party => l_del_rec.responsible_party,
1472 p_external_contact => l_del_rec.external_party_contact_id,
1473 p_internal_contact => l_del_rec.internal_party_contact_id,
1474 p_requester_id => l_del_rec.requester_id,
1475 p_msg_code => l_msg_code,
1476 x_notification_id => l_notification_id,
1477 x_msg_data => l_msg_data,
1478 x_msg_count => l_msg_count,
1479 x_return_status => l_return_status);
1480
1481 if (l_notification_id IS NOT NULL AND l_return_status = 'S') then
1482 update okc_deliverables set completed_notification_id = l_notification_id where deliverable_id = l_deliverable_id;
1483 l_event_status := 'SUCCESS';
1484 elsif l_notification_id IS NOT NULL then
1485 l_event_status :='WARNING';
1486 p_event.setErrorMessage(l_msg_data);
1487 else
1488 l_event_status := 'ERROR';
1489 p_event.setErrorMessage(l_msg_data);
1490 end if;
1491 commit;
1492 return l_event_status;
1493
1494 exception
1495
1496 when others then
1497
1498 WF_CORE.CONTEXT('OKC_DELIVERABLE_WF_PVT', 'send_notification_bus_event',
1499
1500 p_event.getEventName( ),
1501 p_subscription_guid);
1502
1503 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1504
1505 return 'ERROR';
1506
1507 end send_notification_bus_event;
1508
1509 --Procedure to build the message body
1510
1511 PROCEDURE get_notfn_body(document_id IN VARCHAR2,
1512 display_type IN VARCHAR2,
1513 document IN OUT NOCOPY CLOB,
1514 document_type IN OUT NOCOPY VARCHAR2) IS
1515
1516 l_msgbody VARCHAR2(32000);
1517
1518 l_bus_doc_type okc_deliverables.business_document_type%TYPE;
1519 l_del_name okc_deliverables.deliverable_name%TYPE;
1520 l_del_type okc_deliverable_types_tl.name%TYPE;
1521 l_resp_party okc_resp_parties_tl.name%TYPE;
1522 l_int_contact_name per_all_people_f.full_name%TYPE;
1523 l_ext_contact_name per_all_people_f.full_name%TYPE;
1524
1525 l_firstcolon NUMBER;
1526 l_secondcolon NUMBER;
1527 l_thirdcolon NUMBER;
1528 l_fourthcolon NUMBER;
1529 l_fifthcolon NUMBER;
1530
1531 l_del_name_msg VARCHAR2(2000);
1532 l_del_type_msg VARCHAR2(2000);
1533 l_resp_party_msg VARCHAR2(2000);
1534 l_int_contact_msg VARCHAR2(2000);
1535 l_ext_contact_msg VARCHAR2(2000);
1536
1537 l_resolved_msg_name VARCHAR2(30);
1538 l_resolved_del_token VARCHAR2(100);
1539
1540 BEGIN
1541
1542 l_firstcolon := instr(document_id, ':');
1543 l_secondcolon := instr(document_id, ':', 1, 2);
1544 l_thirdcolon := instr(document_id, ':', 1, 3);
1545 l_fourthcolon := instr(document_id, ':', 1, 4);
1546 l_fifthcolon := instr(document_id, ':', 1, 5);
1547
1548 --document_id value will be passed in this format
1549 --business_document_type:deliverable_name:deliverable_type:resp_party_name:internal_contact_name:external_contact_name
1550 --getting deliverable details
1551 l_bus_doc_type := substr(document_id, 1, l_firstcolon - 1);
1552 l_del_name := substr(document_id, l_firstcolon + 1, l_secondcolon - l_firstcolon - 1);
1553 l_del_type := substr(document_id, l_secondcolon + 1, l_thirdcolon - l_secondcolon - 1);
1554 l_resp_party := substr(document_id, l_thirdcolon + 1, l_fourthcolon - l_thirdcolon - 1);
1555 l_int_contact_name := substr(document_id, l_fourthcolon + 1, l_fifthcolon - l_fourthcolon - 1);
1556 l_ext_contact_name := substr(document_id, l_fifthcolon + 1, length(document_id) - l_fifthcolon);
1557
1558 --getting messages to print the prompts of the deliverable details
1559 l_resolved_del_token := OKC_API.resolve_del_token(l_bus_doc_type);
1560
1561 --Deliverable Name
1562 l_resolved_msg_name := OKC_API.resolve_message('OKC_REP_WF_DEL_NAME', l_bus_doc_type);
1563 l_del_name_msg := OKC_TERMS_UTIL_PVT.Get_Message(p_app_name => 'OKC',
1564 p_msg_name => l_resolved_msg_name,
1565 p_token1 => 'DEL_TOKEN',
1566 p_token1_value => l_resolved_del_token);
1567
1568 --Deliverable Type
1569 l_resolved_msg_name := OKC_API.resolve_message('OKC_REP_WF_DEL_TYPE', l_bus_doc_type);
1570 l_del_type_msg := OKC_TERMS_UTIL_PVT.Get_Message(p_app_name => 'OKC',
1571 p_msg_name => l_resolved_msg_name,
1572 p_token1 => 'DEL_TOKEN',
1573 p_token1_value => l_resolved_del_token);
1574
1575 --Responsible Party
1576 l_resolved_msg_name := OKC_API.resolve_message('OKC_REP_WF_RESP_PARTY', l_bus_doc_type);
1577 l_resp_party_msg := OKC_TERMS_UTIL_PVT.Get_Message(p_app_name => 'OKC',p_msg_name => l_resolved_msg_name);
1578
1579 --Internal Contact Name
1580 l_resolved_msg_name := OKC_API.resolve_message('OKC_REP_WF_INT_CONTACT', l_bus_doc_type);
1581 l_int_contact_msg := OKC_TERMS_UTIL_PVT.Get_Message(p_app_name => 'OKC', p_msg_name => l_resolved_msg_name);
1582
1583 --External Contact Name
1584 l_resolved_msg_name := OKC_API.resolve_message('OKC_REP_WF_EXT_CONTACT', l_bus_doc_type);
1585 l_ext_contact_msg := OKC_TERMS_UTIL_PVT.Get_Message(p_app_name => 'OKC', p_msg_name => l_resolved_msg_name);
1586
1587 l_msgbody := '<html>
1588 <style> .tableHeaderCell { font-family: Arial; font-size: 10pt;} .tableDataCell { font-family: Arial; font-size: 10pt; font-weight: bold; }</style>
1589 <body>
1590 <table> <tr> </tr>
1591 <tr> <td height="10"> </td> </tr>
1592 <tr><td class="tableHeaderCell" align="left">'|| l_del_name_msg ||'</td> <td class="tableDataCell">'||l_del_name||'</td> </tr>
1593 <tr><td class="tableHeaderCell" align="left">'|| l_del_type_msg ||'</td> <td class="tableDataCell">'||l_del_type||'</td> </tr>
1594 <tr><td class="tableHeaderCell" align="left">'|| l_resp_party_msg ||'</td> <td class="tableDataCell">'||l_resp_party||'</td> </tr>
1595 <tr><td class="tableHeaderCell" align="left">'|| l_int_contact_msg ||'</td> <td class="tableDataCell">'||l_int_contact_name||'</td> </tr>
1596 <tr><td class="tableHeaderCell" align="left">'|| l_ext_contact_msg ||'</td> <td class="tableDataCell">'||l_ext_contact_name||'</td> </tr>
1597 </table>
1598 </body>
1599 </html>';
1600
1601 WF_NOTIFICATION.WriteToClob(document, l_msgbody);
1602
1603 EXCEPTION
1604 WHEN OTHERS THEN
1605 RAISE;
1606
1607 END get_notfn_body;
1608
1609 END OKC_DELIVERABLE_WF_PVT;