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