[Home] [Help]
PACKAGE BODY: APPS.ENG_WORKFLOW_NTF_UTIL
Source
1 PACKAGE BODY Eng_Workflow_Ntf_Util AS
2 /* $Header: ENGUNTFB.pls 120.2 2006/02/18 20:08:46 mkimizuk noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'Eng_Workflow_Ntf_Util' ;
5
6 -- Seeded workflow notification messages
7 G_REQUEST_COMMENT_MSG CONSTANT VARCHAR2(30) := 'REQUEST_COMMENT_MSG' ;
8 G_ASSIGN_TO_MSG CONSTANT VARCHAR2(30) := 'ASSIGN_TO_MSG' ;
9 G_PRIORITY_CHANGE_MSG CONSTANT VARCHAR2(30) := 'PRIORITY_CHANGE_MSG' ;
10 G_STATUS_CHANGE_MSG CONSTANT VARCHAR2(30) := 'STATUS_CHANGE_MSG' ;
11 G_REASSIGNMENT_MSG CONSTANT VARCHAR2(30) := 'REASSIGNMENT_MSG';
12 G_FYI_NEW_CHANGE_MSG CONSTANT VARCHAR2(30) := 'FYI_NEW_CHANGE_MSG';
13 G_APPR_STATUS_CHANGE_MSG CONSTANT VARCHAR2(30) := 'APPR_STATUS_CHANGE_MSG' ;
14 G_FYI_CHANGE_MSG CONSTANT VARCHAR2(30) := 'FYI_CHANGE_MSG';
15 G_REQUEST_APPROVAL_MSG CONSTANT VARCHAR2(30) := 'REQUEST_APPROVAL_MSG' ;
16 G_ABORT_ROUTE_MSG CONSTANT VARCHAR2(30) := 'ABORT_ROUTE_MSG';
17 G_ABORT_STEP_MSG CONSTANT VARCHAR2(30) := 'ABORT_STEP_MSG';
18
19 /********************************************************************
20 * API Type : Local APIs
21 * Purpose : Those APIs are private
22 *********************************************************************/
23 FUNCTION GetWFMessageName ( p_nid IN NUMBER )
24 RETURN VARCHAR2
25 IS
26
27 CURSOR c_msg (p_nid NUMBER)
28 IS
29
30 SELECT message_name
31 FROM WF_NOTIFICATIONS
32 WHERE notification_id = p_nid ;
33
34 l_msg_name VARCHAR2(30) ;
35
36 BEGIN
37
38 FOR l_msg_rec IN c_msg (p_nid)
39 LOOP
40
41 l_msg_name := l_msg_rec.message_name ;
42
43 END LOOP ;
44
45 RETURN l_msg_name ;
46
47 END GetWFMessageName ;
48
49
50 PROCEDURE GetMessageMapping
51 ( p_wf_msg_name IN VARCHAR2
52 , x_subject_msg_name OUT NOCOPY VARCHAR2
53 , x_text_body_msg_name OUT NOCOPY VARCHAR2
54 , x_html_body_msg_name OUT NOCOPY VARCHAR2
55 )
56 IS
57
58 BEGIN
59
60
61 IF p_wf_msg_name = G_FYI_NEW_CHANGE_MSG
62 THEN
63
64 x_subject_msg_name := 'ENG_FYI_NEW_CHANGE_MSG_S' ;
65 x_text_body_msg_name := 'ENG_FYI_NEW_CHANGE_MSG_TB' ;
66 x_html_body_msg_name := 'ENG_FYI_NEW_CHANGE_MSG_HB' ;
67
68
69 ELSIF p_wf_msg_name = G_REQUEST_COMMENT_MSG
70 THEN
71
72 x_subject_msg_name := 'ENG_REQUEST_COMMENT_MSG_S' ;
73 x_text_body_msg_name := 'ENG_REQUEST_COMMENT_MSG_TB' ;
74 x_html_body_msg_name := 'ENG_REQUEST_COMMENT_MSG_HB' ;
75
76
77 ELSIF p_wf_msg_name = G_ASSIGN_TO_MSG
78 THEN
79
80 x_subject_msg_name := 'ENG_ASSIGN_TO_MSG_S' ;
81 x_text_body_msg_name := 'ENG_ASSIGN_TO_MSG_TB' ;
82 x_html_body_msg_name := 'ENG_ASSIGN_TO_MSG_HB' ;
83
84 ELSIF p_wf_msg_name = G_PRIORITY_CHANGE_MSG
85 THEN
86
87 x_subject_msg_name := 'ENG_PRIORITY_CHANGE_MSG_S' ;
88 x_text_body_msg_name := 'ENG_PRIORITY_CHANGE_MSG_TB' ;
89 x_html_body_msg_name := 'ENG_PRIORITY_CHANGE_MSG_HB' ;
90
91 ELSIF p_wf_msg_name = G_STATUS_CHANGE_MSG
92 THEN
93
94 x_subject_msg_name := 'ENG_STATUS_CHANGE_MSG_S' ;
95 x_text_body_msg_name := 'ENG_STATUS_CHANGE_MSG_TB' ;
96 x_html_body_msg_name := 'ENG_STATUS_CHANGE_MSG_HB' ;
97
98 ELSIF p_wf_msg_name = G_REASSIGNMENT_MSG
99 THEN
100
101 x_subject_msg_name := 'ENG_REASSIGNMENT_MSG_S' ;
102 x_text_body_msg_name := 'ENG_REASSIGNMENT_MSG_TB' ;
103 x_html_body_msg_name := 'ENG_REASSIGNMENT_MSG_HB' ;
104
105 END IF ;
106
107
108 END GetMessageMapping ;
109
110
111 FUNCTION CheckToDoNtf ( p_nid IN NUMBER )
112 RETURN BOOLEAN
113 IS
114
115 CURSOR c_ntf (p_nid NUMBER)
116 IS
117
118 SELECT 'This is To Do Ntf'
119 FROM WF_NOTIFICATIONS wfn
120 WHERE EXISTS (SELECT null
121 FROM WF_MESSAGE_ATTRIBUTES wfma
122 WHERE wfma.subtype = 'RESPOND'
123 AND wfma.message_name = wfn.message_name
124 AND wfma.message_type = wfn.message_type
125 )
126 AND wfn.notification_id = p_nid ;
127
128 To_Do_Ntf BOOLEAN := FALSE ;
129
130 BEGIN
131
132 FOR l_ntf_rec IN c_ntf (p_nid)
133 LOOP
134
135 To_Do_Ntf := TRUE ;
136
137 END LOOP ;
138
139 RETURN To_Do_Ntf ;
140
141 END CheckToDoNtf ;
142
143
144 FUNCTION WrapText ( p_text IN VARCHAR2)
145 RETURN VARCHAR2
146 IS
147
148 WRAP_LENGTH CONSTANT NUMBER := 120 ;
149 BR_TAG CONSTANT VARCHAR2(4) := '<BR>' ;
150
151 l_start_point NUMBER ;
152 l_text_length NUMBER ;
153 l_br_idx NUMBER ;
154 l_prev_text VARCHAR2(32000) ;
155 l_rest_text VARCHAR2(32000) ;
156 x_text VARCHAR2(32000) ;
157
158
159 BEGIN
160 -- Initialize
161 l_start_point := 1 ;
162 x_text := p_text ;
163
164 -- if p_text is null, return
165 IF ( x_text IS NULL OR
166 LENGTH(x_text) <= WRAP_LENGTH )
167 THEN
168 return x_text ;
169 END IF;
170
171 -- Wrap Text by WRAP_LENGTH
172 LOOP
173
174 -- Get current text length and find <BR>
175 l_text_length := LENGTH(x_text) ;
176 l_br_idx := INSTR(UPPER(x_text), '<BR>' , l_start_point ) - 1 ;
177
178 IF (l_br_idx < 0 ) THEN
179 l_br_idx := l_text_length ;
180 END IF ;
181
182 EXIT WHEN (l_text_length - l_start_point)<= WRAP_LENGTH ;
183
184 IF l_br_idx > ( l_start_point + WRAP_LENGTH - 1 ) THEN
185
186 l_prev_text := SUBSTR( x_text, 1, l_start_point + WRAP_LENGTH - 1) ;
187
188 l_rest_text := SUBSTR( x_text
189 , l_start_point + WRAP_LENGTH
190 , l_text_length - l_start_point + WRAP_LENGTH ) ;
191 l_start_point := LENGTH( (l_prev_text || BR_TAG ) ) + 1 ;
192
193 -- 32000 is Max
194 x_text := substrb( (l_prev_text || BR_TAG || l_rest_text), 1, 32000) ;
195
196 ELSE
197
198 l_start_point := l_br_idx + LENGTH(BR_TAG) + 1 ;
199
200 END IF ;
201
202 END LOOP ;
203
204 return x_text ;
205
206 END WrapText ;
207
208
209 FUNCTION ConvertText ( p_text IN VARCHAR2)
210 RETURN VARCHAR2
211 IS
212
213 l_start_point NUMBER ;
214 l_httpidx NUMBER ;
215 l_bridx NUMBER ;
216 l_spaceidx NUMBER ;
217 l_text_length NUMBER ;
218
219 x_text VARCHAR2(32000) ;
220 l_prev_text VARCHAR2(32000) ;
221 l_conv_html VARCHAR2(32000) ;
222 l_rest_text VARCHAR2(32000) ;
223
224 BEGIN
225
226 -- Initialize
227 l_start_point := 1 ;
228 x_text := p_text ;
229
230 -- if p_text is null, return
231 IF x_text IS NULL THEN
232 return x_text ;
233 END IF;
234
235 -- Replace New Line to <br> Tag
236 x_text := REPLACE(x_Text, FND_GLOBAL.NEWLINE, '<br>') ;
237
238 -- Convert_Http_Tag
239 LOOP
240 -- Get current text length and find http
241 l_text_length := LENGTH(x_text) ;
242 l_httpidx := INSTR(UPPER(x_text), 'HTTP://' , l_start_point ) - 1 ;
243
244 IF (l_httpidx < 0 ) THEN
245 l_httpidx := INSTR(UPPER(x_text), 'HTTPS://' , l_start_point ) - 1 ;
246 END IF ;
247
248 -- Exists when no http or https is found after start_point
249 EXIT WHEN l_httpidx < 0 ;
250
251
252 l_bridx := INSTR(UPPER(x_text), '<BR>' , l_httpidx) - 1 ;
253 l_spaceidx := INSTR(UPPER(x_text), ' ' , l_httpidx) - 1 ;
254
255 IF (l_bridx > l_httpidx AND l_bridx < l_spaceidx) THEN
256 l_spaceidx := l_bridx ;
257
258 ELSIF (l_spaceidx < l_httpidx AND l_bridx > l_httpidx ) THEN
259 l_spaceidx := l_bridx;
260
261 ELSIF (l_spaceidx < l_httpidx AND l_bridx < l_httpidx ) THEN
262 l_spaceidx := l_text_length ;
263
264 END IF ;
265
266 l_prev_text := SUBSTR( x_text, 1, l_httpidx ) ;
267
268 l_conv_html := '<br><a href="'
269 || SUBSTR( x_text, l_httpidx + 1, l_spaceidx - l_httpidx )
270 || '">' || WrapText(SUBSTR( x_text, l_httpidx + 1, l_spaceidx - l_httpidx)) || '</a>' ;
271 l_rest_text := SUBSTR( x_text, l_spaceidx + 1, l_text_length - l_spaceidx ) ;
272
273
274 l_start_point := LENGTH( (l_prev_text || l_conv_html) ) + 1 ;
275
276 -- 32000 is max
277 x_text := substrb( (l_prev_text || l_conv_html || l_rest_text), 1, 32000) ;
278
279 END LOOP ;
280
281 return x_text ;
282
283 END ConvertText ;
284
285
286 PROCEDURE GetMessageAttributes
287 ( document_id IN VARCHAR2
288 , display_type IN VARCHAR2
289 , x_mesg_attribute_rec OUT NOCOPY Change_Mesg_Attribute_Rec_Type
290 )
291 IS
292
293 l_index1 NUMBER;
294 l_index2 NUMBER;
295
296 l_subject_msg_name VARCHAR2(30) ;
297 l_text_body_msg_name VARCHAR2(30) ;
298 l_html_body_msg_name VARCHAR2(30) ;
299
300 l_persondetail_url VARCHAR2(480) ;
301
302 /*
303 l_headline_msg VARCHAR2(2000) ;
304 l_content_msg VARCHAR2(2000) ;
305 l_how_to_respond_msg VARCHAR2(2000) ;
306 l_detail_link_msg VARCHAR2(2000) ;
307 l_fyi_only_msg VARCHAR2(2000) ;
308 l_thankyou_msg VARCHAR2(2000) ;
309 l_ntf_detail_url VARCHAR2(2000) ;
310 l_detail_url VARCHAR2(1000);
311 */
312
313 BEGIN
314
315 --
316 -- Get item_type, item_key, and notification id
317 -- Format is <ITEMTYPE>:<ITEMKEY>:<NTF_ID>
318 --
319 l_index1 := instr(document_id, ':');
320 l_index2 := instr(document_id, ':', 1, 2);
321
322 x_mesg_attribute_rec.item_type := substr(document_id, 1, l_index1 - 1);
323 x_mesg_attribute_rec.item_key := substr(document_id, l_index1 + 1, l_index2 - l_index1 -1);
324
325 x_mesg_attribute_rec.notification_id := TO_NUMBER(substr(document_id, l_index2 + 1));
326
327
328 -- Get Change Object Identifier
329 Eng_Workflow_Util.GetChangeObject
330 ( p_item_type => x_mesg_attribute_rec.item_type
331 , p_item_key => x_mesg_attribute_rec.item_key
332 , x_change_id => x_mesg_attribute_rec.change_id
333 , x_change_notice => x_mesg_attribute_rec.change_notice
334 , x_organization_id => x_mesg_attribute_rec.organization_id
335 ) ;
336
337
338 begin
339 -- Get Change Line Object Identifier
340 Eng_Workflow_Util.GetChangeLineObject
341 ( p_item_type => x_mesg_attribute_rec.item_type
342 , p_item_key => x_mesg_attribute_rec.item_key
343 , x_change_line_id => x_mesg_attribute_rec.change_line_id
344 ) ;
345 exception
346 when others then
347 null ;
348 end ;
349
350 --
351 -- Get Workflow Message Name for this notification
352 --
353 x_mesg_attribute_rec.wf_msg_name := GetWFMessageName(p_nid => x_mesg_attribute_rec.notification_id) ;
354
355 --
356 -- Get FND Message Names for HTML Body associated with Eng Workflow Message Name
357 --
358 GetMessageMapping( p_wf_msg_name => x_mesg_attribute_rec.wf_msg_name
359 , x_subject_msg_name => l_subject_msg_name
360 , x_text_body_msg_name => l_text_body_msg_name
361 , x_html_body_msg_name => l_html_body_msg_name
362 ) ;
363
364 -- Get Host URL
365 Eng_Workflow_Util.GetHostURL
366 ( p_item_type => x_mesg_attribute_rec.item_type
367 , p_item_key => x_mesg_attribute_rec.item_key
368 , x_host_url => x_mesg_attribute_rec.host_url
369 ) ;
370
371 -- Get Style Sheet
372 Eng_Workflow_Util.GetStyleSheet
373 ( p_item_type => x_mesg_attribute_rec.item_type
374 , p_item_key => x_mesg_attribute_rec.item_key
375 , x_style_sheet => x_mesg_attribute_rec.style_sheet
376 ) ;
377
378
379 -- Get Workflow Change Object Info
380 Eng_Workflow_Util.GetWFChangeObjectInfo
381 ( p_item_type => x_mesg_attribute_rec.item_type
382 , p_item_key => x_mesg_attribute_rec.item_key
383 , x_change_name => x_mesg_attribute_rec.change_name
384 , x_description => x_mesg_attribute_rec.description
385 , x_change_status => x_mesg_attribute_rec.change_status
386 , x_approval_status => x_mesg_attribute_rec.approval_status
387 , x_priority => x_mesg_attribute_rec.priority
388 , x_reason => x_mesg_attribute_rec.reason
389 , x_change_managemtent_type => x_mesg_attribute_rec.change_management_type
390 , x_change_order_type => x_mesg_attribute_rec.change_order_type
391 , x_eco_department => x_mesg_attribute_rec.eco_department
392 , x_assignee => x_mesg_attribute_rec.assignee
393 , x_assignee_company => x_mesg_attribute_rec.assignee_company
394 ) ;
395
396
397 IF x_mesg_attribute_rec.change_line_id IS NOT NULL AND
398 x_mesg_attribute_rec.change_line_id > 0
399 THEN
400
401 -- Get Change Line Object Info
402 Eng_Workflow_Util.GetWFChangeLineObjectInfo
403 ( p_item_type => x_mesg_attribute_rec.item_type
404 , p_item_key => x_mesg_attribute_rec.item_key
405 , x_line_sequence_number => x_mesg_attribute_rec.line_sequence_number
406 , x_line_name => x_mesg_attribute_rec.line_name
407 , x_line_description => x_mesg_attribute_rec.line_description
408 , x_line_status => x_mesg_attribute_rec.line_status
409 , x_line_assignee => x_mesg_attribute_rec.line_assignee
410 , x_line_assignee_company => x_mesg_attribute_rec.line_assignee_company
411 ) ;
412
413 Eng_Workflow_Util.GetChangeLineItemSubjectInfo
414 ( p_change_id => x_mesg_attribute_rec.change_id
415 , p_change_line_id => x_mesg_attribute_rec.change_line_id
416 , x_organization_id => x_mesg_attribute_rec.item_organization_id
417 , x_item_id => x_mesg_attribute_rec.item_id
418 , x_item_name => x_mesg_attribute_rec.item_name
419 , x_item_revision_id => x_mesg_attribute_rec.item_revision_id
420 , x_item_revision => x_mesg_attribute_rec.item_revision
421 , x_item_revision_label => x_mesg_attribute_rec.item_revision_label
422 ) ;
423
424 ELSE
425
426 Eng_Workflow_Util.GetChangeItemSubjectInfo
427 ( p_change_id => x_mesg_attribute_rec.change_id
428 , x_organization_id => x_mesg_attribute_rec.item_organization_id
429 , x_item_id => x_mesg_attribute_rec.item_id
430 , x_item_name => x_mesg_attribute_rec.item_name
431 , x_item_revision_id => x_mesg_attribute_rec.item_revision_id
432 , x_item_revision => x_mesg_attribute_rec.item_revision
433 , x_item_revision_label => x_mesg_attribute_rec.item_revision_label
434 ) ;
435
436
437 END IF ;
438
439 begin
440
441 -- Get Action Id
442 Eng_Workflow_Util.GetActionId
443 ( p_item_type => x_mesg_attribute_rec.item_type
444 , p_item_key => x_mesg_attribute_rec.item_key
445 , x_action_id => x_mesg_attribute_rec.action_id
446 ) ;
447
448 exception
449 when others then
450 null ;
451 end ;
452
453
454 IF x_mesg_attribute_rec.action_id IS NOT NULL THEN
455
456 Eng_Workflow_Util.GetActionInfo
457 ( p_action_id => x_mesg_attribute_rec.action_id
458 , x_action_desc => x_mesg_attribute_rec.action_desc
459 , x_action_party_id => x_mesg_attribute_rec.action_party_id
460 , x_action_party_name => x_mesg_attribute_rec.action_party_name
461 , x_action_party_company_name => x_mesg_attribute_rec.action_party_company
462 ) ;
463
464
465 l_persondetail_url := '/ego/party/EgoPersonDetail.jsp?partyPersonId='
466 || to_char(x_mesg_attribute_rec.action_party_id)
467 || '&subTabPos=0&app=proddev' ;
468
469 END IF ;
470
471
472 begin
473
474 -- Get Route Id
475 Eng_Workflow_Util.GetRouteId
476 ( p_item_type => x_mesg_attribute_rec.item_type
477 , p_item_key => x_mesg_attribute_rec.item_key
478 , x_route_id => x_mesg_attribute_rec.route_id
479 ) ;
480
481 exception
482 when others then
483 null ;
484 end ;
485
486
487
488 IF x_mesg_attribute_rec.route_id IS NOT NULL THEN
489
490 NULL ;
491 --
492 -- Eng_Workflow_Util.GetRouteInfo
493 -- ( p_route_id => x_mesg_attribute_rec.route_id
494 -- , x_XXXX => x_mesg_attribute_rec.XXX
495 -- ) ;
496 --
497
498 END IF ;
499
500 begin
501
502 -- Get Step Id
503 Eng_Workflow_Util.GetRouteStepId
504 ( p_item_type => x_mesg_attribute_rec.item_type
505 , p_item_key => x_mesg_attribute_rec.item_key
506 , x_route_step_id => x_mesg_attribute_rec.step_id
507 ) ;
508
509 exception
510 when others then
511 null ;
512 end ;
513
514
515 IF x_mesg_attribute_rec.step_id IS NOT NULL THEN
516
517 Eng_Workflow_Util.GetRouteStepInfo
518 ( p_route_step_id => x_mesg_attribute_rec.step_id
519 , x_step_seq_num => x_mesg_attribute_rec.step_seq_num
520 , x_required_date => x_mesg_attribute_rec.required_date
521 , x_condition_type => x_mesg_attribute_rec.condition_type
522 , x_step_instrunction => x_mesg_attribute_rec.step_instruction
523 ) ;
524
525
526 END IF ;
527
528
529 /************************************************************************
530 -- HTML Message
531 -- Not supporting this because WF New Notification Detail Page Std
532 -- Get Html Body Message
533 -- IF l_html_body_msg_name IS NOT NULL THEN
534
535 -- Headline Message
536 FND_MESSAGE.SET_NAME('ENG', l_subject_msg_name) ;
537 FND_MESSAGE.SET_TOKEN('CHANGE_MGMT_TYPE', x_change_management_type) ;
538 FND_MESSAGE.SET_TOKEN('CHANGE_NOTICE', x_change_notice ) ;
539 FND_MESSAGE.SET_TOKEN('CHANGE_NAME', x_change_name) ;
540
541 IF x_wf_msg_name = G_PRIORITY_CHANGE_MSG THEN
542
543 FND_MESSAGE.SET_TOKEN('PRIORITY', x_priority) ;
544
545 ELSIF x_wf_msg_name = G_STATUS_CHANGE_MSG THEN
546
547 FND_MESSAGE.SET_TOKEN('STATUS', x_change_status ) ;
548
549 ELSIF x_wf_msg_name = G_REASSIGNMENT_MSG THEN
550
551 FND_MESSAGE.SET_TOKEN('ASSIGNEE_PARTY_NAME', x_assignee ) ;
552
553 END IF ;
554
555 l_headline_msg := FND_MESSAGE.GET ;
556
557 -- Content Message
558 FND_MESSAGE.SET_NAME('ENG', l_html_body_msg_name) ;
559 FND_MESSAGE.SET_TOKEN('CHANGE_MGMT_TYPE', x_change_management_type) ;
560 FND_MESSAGE.SET_TOKEN('CHANGE_NOTICE', x_change_notice ) ;
561 FND_MESSAGE.SET_TOKEN('CHANGE_NAME', x_change_name) ;
562 FND_MESSAGE.SET_TOKEN('BY_PERSON_NAME', x_action_party_name) ;
563 FND_MESSAGE.SET_TOKEN('BY_COMPANY_NAME', x_action_party_company) ;
564
565 IF x_wf_msg_name = G_PRIORITY_CHANGE_MSG THEN
566
567 FND_MESSAGE.SET_TOKEN('PRIORITY', x_priority) ;
568
569 ELSIF x_wf_msg_name = G_STATUS_CHANGE_MSG THEN
570
571 FND_MESSAGE.SET_TOKEN('STATUS', x_change_status ) ;
572
573 ELSIF x_wf_msg_name = G_REASSIGNMENT_MSG THEN
574
575 FND_MESSAGE.SET_TOKEN('ASSIGNEE_PARTY_NAME', x_assignee ) ;
576 FND_MESSAGE.SET_TOKEN('ASSIGNEE_COMPANY_NAME', x_assignee_company) ;
577
578 END IF ;
579
580 l_content_msg := FND_MESSAGE.GET ;
581
582 -- How to respond or FYI instruction
583 IF CheckToDoNtf(p_nid => x_nid ) THEN
584
585 -- l_how_to_respond_msg
586 -- This message is put on the ToDo Notifications
587 -- TO DO Notifications :
588 --
589 -- Message : ENG_NTF_HOW_TO_RESPOND_NTF
590 -- 'To respond to this notification by email, please select
591 -- the link at the bottom of this email message.'
592
593 FND_MESSAGE.SET_NAME('ENG', 'ENG_NTF_HOW_TO_RESP_HTML_MSG') ;
594 FND_MESSAGE.SET_TOKEN('NTF_DETAIL_URL', x_ntf_detail_url) ;
595 l_how_to_respond_msg := FND_MESSAGE.GET ;
596
597 ELSE
598
599 FND_MESSAGE.SET_NAME('ENG', 'ENG_NTF_FYI_INSTRUCTION') ;
600 l_fyi_only_msg := FND_MESSAGE.GET ;
601
602 END IF ;
603
604 -- Detail Page Link Message
605 -- l_detail_link_msg
606 -- Message : ENG_CM_SEE_BELOW_DETAILS_NTF
607 -- This message is put on every CM Workflow notification
608 -- with detail page link
609 -- 'You can go to the following URL to view the Change Request details.
610
611 l_detail_url := wf_engine.GetItemAttrText
612 ( x_item_type
613 , x_item_key
614 , 'CHANGE_DETAIL_PAGE_URL'
615 ) ;
616
617 FND_MESSAGE.SET_NAME('ENG', 'ENG_NTF_DETAIL_LINK_HTML_MSG') ;
618 FND_MESSAGE.SET_TOKEN('CHANGE_MGMT_TYPE', x_change_management_type) ;
619 FND_MESSAGE.SET_TOKEN('DETAIL_URL', l_detail_url) ;
620 l_detail_link_msg := FND_MESSAGE.GET ;
621
622 -- Thank you message
623 -- l_thankyou_msg
624 --
625 -- message ENG_NTF_THANK_YOU
626 -- 'Thank You.'
627 FND_MESSAGE.SET_NAME('ENG', 'ENG_NTF_THANK_YOU') ;
628 l_thankyou_msg := FND_MESSAGE.GET ;
629
630 END IF ;
631 *************************************************************************/
632
633
634
635 /************************************************************************
636 -- TEXT Message
637 -- Not supporting this because WF New Notification Detail Page Std
638 -- Get Html Body Message
639 -- IF l_text_body_msg_name_body_msg_name IS NOT NULL THEN
640
641 -- Headline Message
642 FND_MESSAGE.SET_NAME('ENG', l_subject_msg_name) ;
643 FND_MESSAGE.SET_TOKEN('CHANGE_MGMT_TYPE', l_change_management_type) ;
644 FND_MESSAGE.SET_TOKEN('CHANGE_NOTICE', l_change_notice ) ;
645 FND_MESSAGE.SET_TOKEN('CHANGE_NAME', l_change_name) ;
646
647 IF l_wf_msg_name = G_PRIORITY_CHANGE_MSG THEN
648
649 FND_MESSAGE.SET_TOKEN('PRIORITY', l_priority) ;
650
651 ELSIF l_wf_msg_name = G_STATUS_CHANGE_MSG THEN
652
653 FND_MESSAGE.SET_TOKEN('STATUS', l_change_status ) ;
654
655 ELSIF l_wf_msg_name = G_REASSIGNMENT_MSG THEN
656
657 FND_MESSAGE.SET_TOKEN('ASSIGNEE_PARTY_NAME', l_assignee ) ;
658
659 END IF ;
660
661 l_headline_msg := FND_MESSAGE.GET ;
662
663 -- Content Message
664 FND_MESSAGE.SET_NAME('ENG', l_text_body_msg_name) ;
665 FND_MESSAGE.SET_TOKEN('CHANGE_MGMT_TYPE', l_change_management_type) ;
666 FND_MESSAGE.SET_TOKEN('CHANGE_NOTICE', l_change_notice ) ;
667 FND_MESSAGE.SET_TOKEN('CHANGE_NAME', l_change_name) ;
668 FND_MESSAGE.SET_TOKEN('BY_PERSON_NAME', l_action_party_name) ;
669 FND_MESSAGE.SET_TOKEN('BY_COMPANY_NAME', l_action_party_company) ;
670 FND_MESSAGE.SET_TOKEN('ACTION_DESC', l_action_desc) ;
671
672 IF l_wf_msg_name = G_PRIORITY_CHANGE_MSG THEN
673
674 FND_MESSAGE.SET_TOKEN('PRIORITY', l_priority) ;
675
676 ELSIF l_wf_msg_name = G_STATUS_CHANGE_MSG THEN
677
678 FND_MESSAGE.SET_TOKEN('STATUS', l_change_status ) ;
679
680 ELSIF l_wf_msg_name = G_REASSIGNMENT_MSG THEN
681
682 FND_MESSAGE.SET_TOKEN('ASSIGNEE_PARTY_NAME', l_assignee ) ;
683 FND_MESSAGE.SET_TOKEN('ASSIGNEE_COMPANY_NAME', l_assignee_company) ;
684
685 END IF ;
686
687 l_content_msg := FND_MESSAGE.GET ;
688
689 -- How to respond or FYI instruction
690 IF CheckToDoNtf(p_nid => l_nid ) THEN
691
692 -- l_how_to_respond_msg
693 -- This message is put on the ToDo Notifications
694 -- TO DO Notifications :
695 --
696 -- Message : ENG_NTF_HOW_TO_RESPOND_NTF
697 -- 'To respond to this notification by email, please select
698 -- the link at the bottom of this email message.'
699
700 FND_MESSAGE.SET_NAME('ENG', 'ENG_NTF_HOW_TO_RESP_MSG') ;
701 FND_MESSAGE.SET_TOKEN('NTF_DETAIL_URL', l_ntf_detail_url) ;
702 l_how_to_respond_msg := FND_MESSAGE.GET ;
703
704 ELSE
705
706 FND_MESSAGE.SET_NAME('ENG', 'ENG_NTF_FYI_INSTRUCTION') ;
707 l_fyi_only_msg := FND_MESSAGE.GET ;
708
709 END IF ;
710
711 -- Detail Page Link Message
712 -- l_detail_link_msg
713 -- Message : ENG_CM_SEE_BELOW_DETAILS_NTF
714 -- This message is put on every CM Workflow notification
715 -- with detail page link
716 -- 'You can go to the following URL to view the Change Request details.
717
718 l_detail_url := wf_engine.GetItemAttrText
719 ( l_item_type
720 , l_item_key
721 , 'CHANGE_DETAIL_PAGE_URL'
722 ) ;
723
724 FND_MESSAGE.SET_NAME('ENG', 'ENG_NTF_DETAIL_LINK_TEXT_MSG') ;
725 FND_MESSAGE.SET_TOKEN('CHANGE_MGMT_TYPE', l_change_management_type) ;
726 FND_MESSAGE.SET_TOKEN('DETAIL_URL', l_detail_url) ;
727 l_detail_link_msg := FND_MESSAGE.GET ;
728
729 -- Thank you message
730 -- l_thankyou_msg
731 --
732 -- message ENG_NTF_THANK_YOU
733 -- 'Thank You.'
734 FND_MESSAGE.SET_NAME('ENG', 'ENG_NTF_THANK_YOU') ;
735 l_thankyou_msg := FND_MESSAGE.GET ;
736 END IF ;
737 *************************************************************************/
738
739
740
741
742 END GetMessageAttributes ;
743
744 FUNCTION GetChangeURLType
745 ( p_change_id IN NUMBER) RETURN VARCHAR2
746 IS
747 -- return SUMMARY or DETAIL
748 l_change_url_type VARCHAR2(20) ;
749
750 CURSOR c_change_url_type (p_change_id NUMBER)
751 IS
752 SELECT EngineeringChangeEO.change_id,
753 EngineeringChangeEO.change_mgmt_type_code ,
754 ChangeCategory.BASE_CHANGE_MGMT_TYPE_CODE
755 FROM ENG_ENGINEERING_CHANGES EngineeringChangeEO,
756 ENG_CHANGE_ORDER_TYPES ChangeCategory
757 WHERE ( ChangeCategory.BASE_CHANGE_MGMT_TYPE_CODE = 'ATTACHMENT_APPROVAL'
758 OR ChangeCategory.BASE_CHANGE_MGMT_TYPE_CODE = 'ATTACHMENT_REVIEW'
759 OR NOT EXISTS (select null
760 from eng_change_type_applications type_appl
761 where type_appl.change_type_id = ChangeCategory.change_order_type_id
762 and type_appl.application_id = 431)
763 )
764 AND ChangeCategory.type_classification = 'CATEGORY'
765 AND ChangeCategory.change_mgmt_type_code = EngineeringChangeEO.change_mgmt_type_code
766 AND EngineeringChangeEO.change_id = p_change_id ;
767
768 BEGIN
769
770 FOR l_rec IN c_change_url_type (p_change_id => p_change_id)
771 LOOP
772 l_change_url_type := 'SUMMARY' ;
773 END LOOP ;
774
775 RETURN l_change_url_type ;
776
777 END GetChangeURLType ;
778
779
780
781 /********************************************************************
782 * API Type : Private APIs
783 * Purpose : Those APIs are private
784 *********************************************************************/
785
786 -- API name : GetMessageTextBody
787 -- Type : Private
788 -- Pre-reqs : None.
789 -- Function : Workflow PL/SQL CLOB Document API to get ntf text message body
790 -- Parameters : p_document_id IN VARCHAR2 Required
791 -- Format:
792 -- <wf item type>:<wf item key>:<&#NID>
793 --
794 PROCEDURE GetMessageTextBody
795 ( document_id IN VARCHAR2
796 , display_type IN VARCHAR2
797 , document IN OUT NOCOPY CLOB
798 , document_type IN OUT NOCOPY VARCHAR2
799 )
800 IS
801 l_mesg_attribute_rec Change_Mesg_Attribute_Rec_Type ;
802 l_index1 NUMBER;
803 l_index2 NUMBER;
804
805 l_doc VARCHAR2(32000) ;
806
807 /*
808 l_item_type VARCHAR2(8);
809 l_item_key VARCHAR2(240) ;
810 l_nid NUMBER;
811 l_change_id NUMBER ;
812 l_change_notice VARCHAR2(10) ;
813 l_organization_id NUMBER ;
814 l_organization_code VARCHAR2(3) ;
815 l_change_management_type VARCHAR2(40) ;
816 l_change_name VARCHAR2(240) ;
817 l_description VARCHAR2(2000) ;
818 l_change_order_type VARCHAR2(10) ;
819 l_organization_name VARCHAR2(60) ;
820 l_eco_department VARCHAR2(60) ;
821 l_change_status VARCHAR2(80) ;
822 l_approval_status VARCHAR2(80) ;
823 l_priority VARCHAR2(50) ;
824 l_reason VARCHAR2(50) ;
825 l_assignee VARCHAR2(360) ;
826 l_assignee_company VARCHAR2(360) ;
827
828 l_action_id NUMBER ;
829 l_action_party_id NUMBER ;
830 l_action_party_name VARCHAR2(360) ;
831 l_action_party_company VARCHAR2(360) ;
832 l_action_desc VARCHAR2(5000) ;
833
834 l_route_id NUMBER ;
835 l_step_id NUMBER ;
836 l_step_seq_num NUMBER ;
837 l_required_date DATE ;
838 l_condition_type VARCHAR2(80) ;
839 l_step_instruction VARCHAR2(5000) ;
840
841 l_wf_msg_name VARCHAR2(30) ;
842 l_subject_msg_name VARCHAR2(30) ;
843 l_text_body_msg_name VARCHAR2(30) ;
844 l_html_body_msg_name VARCHAR2(30) ;
845
846 l_host_url VARCHAR2(480) ;
847 l_style_sheet VARCHAR2(100) ;
848
849 */
850
851 /*
852 l_headline_msg VARCHAR2(2000) ;
853 l_content_msg VARCHAR2(2000) ;
854 l_how_to_respond_msg VARCHAR2(2000) ;
855 l_detail_link_msg VARCHAR2(2000) ;
856 l_fyi_only_msg VARCHAR2(2000) ;
857 l_thankyou_msg VARCHAR2(2000) ;
858 l_persondetail_url VARCHAR2(480) ;
859 l_ntf_detail_url VARCHAR2(2000) ;
860 l_detail_url VARCHAR2(1000);
861 */
862
863 NL VARCHAR2(1) ;
864
865 BEGIN
866
867 -- Init Var
868 NL := FND_GLOBAL.NEWLINE;
869
870
871 -- For Test/Debug
872 -- Eng_Workflow_Util.Open_Debug_Session( '/sqlcom/log/plm115d' , 'GetMessageTextBody' ) ;
873 -- Eng_Workflow_Util.Write_Debug('document id ' || document_id );
874 -- Eng_Workflow_Util.Write_Debug('display_type ' || display_type);
875 -- Eng_Workflow_Util.Write_Debug('document_type ' || document_type);
876
877
878 -- Call GetMessageHTMLBody if display type is text/plain
879 IF (display_type = WF_NOTIFICATION.DOC_HTML ) THEN
880
881 GetMessageHTMLBody
882 ( document_id => document_id
883 , display_type => display_type
884 , document => document
885 , document_type => document_type
886 ) ;
887
888 RETURN ;
889
890 END IF;
891
892 GetMessageAttributes
893 ( document_id => document_id
894 , display_type => display_type
895 , x_mesg_attribute_rec => l_mesg_attribute_rec
896 ) ;
897
898
899 -- Eng_Workflow_Util.Close_Debug_Session;
900
901 -- We are not supporting text message notification by default
902 -- WF_NOTIFICATION.WriteToClob( document , l_doc);
903
904 -- Executing Custom Hook: Eng_Workflow_Ext.GetCustomMessageSubject. . .');
905 Eng_Workflow_Ext.GetCustomMessageBody
906 ( document_id => document_id
907 , display_type => display_type
908 , document => document
909 , document_type => document_type
910 ) ;
911
912 END GetMessageTextBody ;
913
914
915 -- API name : GetMessageHTMLBody
916 -- Type : Private
917 -- Pre-reqs : None.
918 -- Function : Workflow PL/SQL CLOB Document API to get ntf HTML message body
919 -- Parameters : p_document_id IN VARCHAR2 Required
920 -- Format:
921 -- <wf item type>:<wf item key>:<&#NID>
922 --
923 PROCEDURE GetMessageHTMLBody
924 ( document_id IN VARCHAR2
925 , display_type IN VARCHAR2
926 , document IN OUT NOCOPY CLOB
927 , document_type IN OUT NOCOPY VARCHAR2
928 )
929 IS
930
931 l_index1 NUMBER;
932 l_index2 NUMBER;
933
934 l_doc VARCHAR2(32000) ;
935
936 l_mesg_attribute_rec Change_Mesg_Attribute_Rec_Type ;
937
938 l_persondetail_url VARCHAR2(480) ;
939
940 l_item_url VARCHAR2(480) ;
941 l_item_revision_url VARCHAR2(480) ;
942 l_change_url_type VARCHAR2(20) ;
943 l_change_detail_url VARCHAR2(480) ;
944 l_change_detail_link VARCHAR2(200) ;
945
946 l_change_name VARCHAR2(240) ;
947 l_change_description VARCHAR2(5000) ;
948
949 -- Sub Header
950 l_additional_info_subh VARCHAR2(80) ;
951
952 -- Message prompt
953 l_change_name_p VARCHAR2(80) ;
954 l_change_desc_p VARCHAR2(80) ;
955 l_item_p VARCHAR2(80) ;
956 l_item_revision_p VARCHAR2(80) ;
957 l_instrunctions_p VARCHAR2(80) ;
958 l_action_p VARCHAR2(80) ;
959
960 l_item_revision_label VARCHAR2(400) ;
961
962 NL VARCHAR2(1) ;
963
964 -- Html Tag Constant Variables
965 SPACE_JAVA_SCRIPT CONSTANT VARCHAR2(2000)
966 := '<!-- Space Script --> '|| NL
967 || '<script>function t(width,height){document.write(''<img src="/OA_HTML/cabo/images/t.gif"'');'
968 || 'if (width!=void 0)document.write('' width="'' + width + ''"''); '
969 || 'if (height!=void 0)document.write('' height="'' + height + ''"'');document.write(''>'');}'
970 || '</script>' || NL ;
971
972 OPEN_TAB_100 CONSTANT VARCHAR2(200)
973 := '<table width="100%" bgcolor="" border="0" cellspacing="0" cellpadding="0">' ;
974
975 OPEN_TAB CONSTANT VARCHAR2(200)
976 := '<table bgcolor="" border="0" cellspacing="0" cellpadding="0">' ;
977
978 CLOSE_TAB CONSTANT VARCHAR2(10)
979 := '</table>' ;
980
981 OPEN_TR CONSTANT VARCHAR2(5)
982 := '<tr>' ;
983
984 CLOSE_TR CONSTANT VARCHAR2(8)
985 := '</tr>' ;
986
987 OPEN_TD CONSTANT VARCHAR2(100)
988 := '<td>' ;
989
990 OPEN_PROMPT_TD CONSTANT VARCHAR2(80)
991 := '<td align="right" valign="top" nowrap>' ;
992
993 CLOSE_TD CONSTANT VARCHAR2(8)
994 := '</td>' ;
995
996 CELL_SPACE CONSTANT VARCHAR2(100)
997 := '<td width="12"><script>t(''12'')</script></td>' ;
998
999 TREE_ROW_SPACE CONSTANT VARCHAR2(100)
1000 := '<tr><td height="3"></td><td></td><td></td></tr>' ;
1001
1002
1003 FUNCTION CLOSE_PROMPT_TD( p_prompt_text IN VARCHAR2)
1004 RETURN VARCHAR2
1005 IS
1006
1007 BEGIN
1008
1009 RETURN '<span class="OraPromptText">' || p_prompt_text || '</span></td>' ;
1010
1011 END CLOSE_PROMPT_TD ;
1012
1013
1014 FUNCTION CLOSE_SINGLE_DATA_TD( p_data_text IN VARCHAR2)
1015 RETURN VARCHAR2
1016 IS
1017
1018 BEGIN
1019
1020 RETURN '<span class="OraDataText">' || p_data_text || '</span></td>' ;
1021
1022 END CLOSE_SINGLE_DATA_TD ;
1023
1024
1025
1026 FUNCTION HREF_URL( p_URL IN VARCHAR2
1027 , p_display_text IN VARCHAR2)
1028 RETURN VARCHAR2
1029 IS
1030
1031 BEGIN
1032
1033 RETURN '<a href="' || p_URL || '">' || p_display_text || '</a>' ;
1034
1035 END HREF_URL;
1036
1037
1038 FUNCTION SPACE_TR ( p_height IN NUMBER )
1039 RETURN VARCHAR2
1040 IS
1041
1042 BEGIN
1043
1044 RETURN '<tr><td height="' || TO_CHAR(p_height) || '"></td></tr>';
1045
1046 END SPACE_TR ;
1047
1048
1049 FUNCTION SUBHEADER ( p_subheader IN VARCHAR2)
1050 RETURN VARCHAR2
1051 IS
1052
1053 BEGIN
1054
1055 RETURN '<tr><td rowspan="3" width="20"><IMG src="/OA_HTML/cabo/images/t.gif" width="20" height="1"></td>' ||
1056 '<td><table cellpadding="0" cellspacing="0" border="0" width="100%">' ||
1057 '<tr><td width="100%" class="OraHeaderSub">' || p_subheader || '</td></tr>'||
1058 '<tr><td class="OraBGAccentDark"><img src="/OA_HTML/cabo/images/t.gif"></td></tr>' ||
1059 '</table>' ||
1060 '</td></tr>' ;
1061
1062 END SUBHEADER ;
1063
1064
1065 BEGIN
1066
1067 -- Init Var
1068 NL := FND_GLOBAL.NEWLINE;
1069
1070 -- For Test/Debug
1071 -- Eng_Workflow_Util.Open_Debug_Session( '/sqlcom/log/plm115d' , 'GetMessageHTMLBody' ) ;
1072 -- Eng_Workflow_Util.Write_Debug('document id ' || document_id );
1073 -- Eng_Workflow_Util.Write_Debug('display_type ' || display_type);
1074 -- Eng_Workflow_Util.Write_Debug('document_type ' || document_type);
1075
1076
1077 /* Not supporting text
1078 -- Call GetMessageHTMLBody if display type is text/plain
1079 IF (display_type = WF_NOTIFICATION.DOC_TEXT ) THEN
1080
1081 GetMessageTextBody
1082 ( document_id => document_id
1083 , display_type => display_type
1084 , document => document
1085 , document_type => document_type
1086 ) ;
1087
1088 RETURN ;
1089
1090 END IF;
1091 */
1092
1093
1094 GetMessageAttributes
1095 ( document_id => document_id
1096 , display_type => display_type
1097 , x_mesg_attribute_rec => l_mesg_attribute_rec
1098 ) ;
1099
1100 -- Convert the Text to HTML Text
1101 l_mesg_attribute_rec.description := ConvertText(l_mesg_attribute_rec.description) ;
1102 l_mesg_attribute_rec.line_description := ConvertText(l_mesg_attribute_rec.line_description) ;
1103 l_mesg_attribute_rec.step_instruction := ConvertText(l_mesg_attribute_rec.step_instruction) ;
1104 l_mesg_attribute_rec.action_desc := ConvertText(l_mesg_attribute_rec.action_desc) ;
1105
1106
1107
1108 -- Get Field Prompt
1109 FND_MESSAGE.SET_NAME('ENG', 'ENG_CHANGE_NAME') ;
1110 l_change_name_p := FND_MESSAGE.GET ;
1111
1112 FND_MESSAGE.SET_NAME('ENG', 'ENG_CHANGE_DESCRIPTION') ;
1113 l_change_desc_p := FND_MESSAGE.GET ;
1114
1115
1116 IF l_mesg_attribute_rec.item_id IS NOT NULL THEN
1117
1118 FND_MESSAGE.SET_NAME('ENG', 'ENG_SUBJECT_ITEM') ;
1119 l_item_p := FND_MESSAGE.GET ;
1120
1121 FND_MESSAGE.SET_NAME('ENG', 'ENG_SUBJECT_ITEM_REVISION') ;
1122 l_item_revision_p := FND_MESSAGE.GET ;
1123
1124
1125 -- if item revision label is not null
1126 -- revision field value should be 'revision - revision_lable'
1127 -- defined in message ENG_CHANGE_ITEM_REV_LABEL
1128 IF l_mesg_attribute_rec.item_revision_label IS NOT NULL
1129 THEN
1130
1131 FND_MESSAGE.SET_NAME('ENG', 'ENG_CHANGE_ITEM_REV_LABEL') ;
1132 FND_MESSAGE.SET_TOKEN('REVISION_CODE', l_mesg_attribute_rec.item_revision) ;
1133 FND_MESSAGE.SET_TOKEN('REVISION_LABEL', l_mesg_attribute_rec.item_revision_label) ;
1134 l_item_revision_label := FND_MESSAGE.GET ;
1135
1136 ELSE
1137
1138 l_item_revision_label := l_mesg_attribute_rec.item_revision ;
1139
1140 END IF ;
1141
1142
1143 -- Item Detail URL
1144 -- Ex) /OA_HTML/OA.jsp?OAFunc=EGO_ITEM_OVERVIEW&inventoryItemId=999&organizationId=999
1145 l_item_url := '/OA_HTML/'
1146 || Eng_Workflow_Util.GetFunctionWebHTMLCall
1147 (p_function_name => 'EGO_ITEM_OVERVIEW' )
1148 || '&inventoryItemId='
1149 || TO_CHAR(l_mesg_attribute_rec.item_id)
1150 || '&organizationId='
1151 || TO_CHAR(l_mesg_attribute_rec.item_organization_id)
1152 || '&OAFunc=EGO_ITEM_OVERVIEW' ;
1153
1154 -- Item Revision Detail URL
1155 -- Ex) /OA_HTML/OA.jsp?OAFunc=EGO_ITEM_REVISIONS&inventoryItemId=999&organizationId=999&revisionCode=A
1156 l_item_revision_url := '/OA_HTML/'
1157 || Eng_Workflow_Util.GetFunctionWebHTMLCall
1158 (p_function_name => 'EGO_ITEM_REVISIONS' )
1159 || '&inventoryItemId='
1160 || TO_CHAR(l_mesg_attribute_rec.item_id)
1161 || '&organizationId='
1162 || TO_CHAR(l_mesg_attribute_rec.item_organization_id)
1163 || '&revisionCode='
1164 || l_mesg_attribute_rec.item_revision
1165 || '&OAFunc=EGO_ITEM_REVISIONS' ;
1166
1167
1168 END IF ;
1169
1170
1171
1172 IF l_mesg_attribute_rec.step_id IS NOT NULL AND
1173 l_mesg_attribute_rec.item_type = Eng_Workflow_Util.G_CHANGE_ROUTE_STEP_ITEM_TYPE AND
1174 l_mesg_attribute_rec.wf_msg_name <> G_ABORT_STEP_MSG
1175 THEN
1176
1177 FND_MESSAGE.SET_NAME('ENG', 'ENG_STEP_INSTRUCTIONS') ;
1178 l_instrunctions_p := FND_MESSAGE.GET ;
1179
1180 END IF ;
1181
1182 IF l_mesg_attribute_rec.action_id IS NOT NULL AND
1183 ( l_mesg_attribute_rec.item_type = Eng_Workflow_Util.G_CHANGE_ACTION_ITEM_TYPE
1184 OR l_mesg_attribute_rec.item_type = Eng_Workflow_Util.G_CHANGE_LINE_ACTION_ITEM_TYPE )
1185 THEN
1186
1187 IF l_mesg_attribute_rec.wf_msg_name = G_REQUEST_COMMENT_MSG
1188 THEN
1189
1190 FND_MESSAGE.SET_NAME('ENG', 'ENG_SUBJECT') ;
1191 l_action_p := FND_MESSAGE.GET ;
1192
1193 ELSE
1194
1195 FND_MESSAGE.SET_NAME('ENG', 'ENG_COMMENT') ;
1196 l_action_p := FND_MESSAGE.GET ;
1197
1198 END IF ;
1199
1200 END IF ;
1201
1202
1203 IF l_mesg_attribute_rec.change_id IS NOT NULL AND
1204 l_mesg_attribute_rec.change_line_id IS NULL
1205 THEN
1206
1207 l_change_name := l_mesg_attribute_rec.change_name ;
1208 l_change_description := l_mesg_attribute_rec.description ;
1209
1210
1211 l_change_url_type := GetChangeURLType(p_change_Id => l_mesg_attribute_rec.change_id ) ;
1212
1213 IF l_change_url_type = 'SUMMARY'
1214 THEN
1215
1216 l_change_detail_url := '/OA_HTML/'
1217 || Eng_Workflow_Util.GetFunctionWebHTMLCall
1218 (p_function_name => 'ENG_CHANGE_SUMMARY_PAGE' )
1219 || '&changeId='
1220 || TO_CHAR(l_mesg_attribute_rec.change_id)
1221 || '&OAFunc=ENG_CHANGE_SUMMARY_PAGE' ;
1222
1223
1224 ELSE
1225
1226 l_change_detail_url := '/OA_HTML/'
1227 || Eng_Workflow_Util.GetFunctionWebHTMLCall
1228 (p_function_name => 'ENG_CHANGE_DETAIL_PAGE' )
1229 || '&changeId='
1230 || TO_CHAR(l_mesg_attribute_rec.change_id)
1231 || '&OAFunc=ENG_CHANGE_DETAIL_PAGE' ;
1232
1233 END IF ;
1234
1235 -- Get Sub Header
1236 FND_MESSAGE.SET_NAME('ENG', 'ENG_CHANGE_SUMMARY') ;
1237 FND_MESSAGE.SET_TOKEN('CHANGE_MGMT_TYPE', l_mesg_attribute_rec.change_management_type) ;
1238 l_additional_info_subh := FND_MESSAGE.GET ;
1239
1240 -- Get Detail Url Link
1241 FND_MESSAGE.SET_NAME('ENG', 'ENG_CHANGE_DETAIL_LINK_NTF') ;
1242 FND_MESSAGE.SET_TOKEN('CHANGE_MGMT_TYPE', l_mesg_attribute_rec.change_management_type) ;
1243 l_change_detail_link := FND_MESSAGE.GET ;
1244
1245 ELSIF l_mesg_attribute_rec.change_line_id IS NOT NULL
1246 THEN
1247 l_change_name := l_mesg_attribute_rec.line_name ;
1248 l_change_description := l_mesg_attribute_rec.line_description ;
1249 l_change_detail_url := '/OA_HTML/'
1250 || Eng_Workflow_Util.GetFunctionWebHTMLCall
1251 (p_function_name => 'ENG_CHANGE_LINE_DETAIL_PAGE' )
1252 || '&changeLineId='
1253 || TO_CHAR(l_mesg_attribute_rec.change_line_id)
1254 || '&OAFunc=ENG_CHANGE_LINE_DETAIL_PAGE' ;
1255
1256 -- Get Sub Header
1257 FND_MESSAGE.SET_NAME('ENG', 'ENG_CHANGE_LINE_SUMMARY') ;
1258 FND_MESSAGE.SET_TOKEN('CHANGE_MGMT_TYPE', l_mesg_attribute_rec.change_management_type) ;
1259 l_additional_info_subh := FND_MESSAGE.GET ;
1260
1261
1262 -- Get Detail Url Link
1263 FND_MESSAGE.SET_NAME('ENG', 'ENG_LINE_DETAIL_LINK_NTF') ;
1264 FND_MESSAGE.SET_TOKEN('CHANGE_MGMT_TYPE', l_mesg_attribute_rec.change_management_type) ;
1265 l_change_detail_link := FND_MESSAGE.GET ;
1266
1267
1268 END IF ;
1269
1270
1271 --
1272 -- Generating CM HTML Notification
1273 -- this contents is put on under 115i9 Workflow Ntf Header
1274 --
1275 l_doc := l_doc || '<!-- Base Href URL -->' || NL;
1276 l_doc := l_doc || '<base href="' || l_mesg_attribute_rec.host_url || '"> ' || NL;
1277 l_doc := l_doc || '<!-- Style Sheet Link -->' || NL;
1278 l_doc := l_doc || '<link rel="stylesheet" href="' || l_mesg_attribute_rec.style_sheet || '" type="text/css">' || NL;
1279 -- We mihgt need charset="UTF-8" in this tag
1280
1281 l_doc := l_doc || SPACE_JAVA_SCRIPT || NL;
1282
1283 -- begin of additional info master table
1284 l_doc := l_doc || OPEN_TAB_100 || NL ;
1285 l_doc := l_doc || SPACE_TR(17) || NL ;
1286 l_doc := l_doc || SUBHEADER(l_additional_info_subh) || NL ;
1287 l_doc := l_doc || SPACE_TR(2) || NL ;
1288
1289 -- begin of content record
1290 l_doc := l_doc || OPEN_TR || OPEN_TD || NL ;
1291
1292 -- begin of content table
1293 l_doc := l_doc || ' '|| OPEN_TAB_100 || NL ;
1294 l_doc := l_doc || ' '|| OPEN_TR || OPEN_TD || NL ;
1295
1296 -- begin of content child table
1297 l_doc := l_doc || ' '|| OPEN_TAB_100 || NL ;
1298 l_doc := l_doc || ' '|| OPEN_TR || NL ;
1299
1300 -- Left side intenstion
1301 l_doc := l_doc || ' '|| CELL_SPACE || NL ;
1302
1303 -- Right side content td
1304 l_doc := l_doc || ' '|| '<TD VALIGN="top">'|| NL ;
1305 l_doc := l_doc || ' '|| OPEN_TAB || NL ;
1306
1307 l_doc := l_doc || ' <!-- Change Object/Line Name --> ' || NL ;
1308 l_doc := l_doc || ' '|| OPEN_TR || NL ;
1309 l_doc := l_doc || ' ' || OPEN_PROMPT_TD || NL ;
1310 l_doc := l_doc || ' ' || CLOSE_PROMPT_TD(l_change_name_p) || NL ;
1311 l_doc := l_doc || ' ' || CELL_SPACE || NL ;
1312 l_doc := l_doc || ' ' || OPEN_TD || NL ;
1313 l_doc := l_doc || ' ' || CLOSE_SINGLE_DATA_TD(l_change_name) || NL ;
1314 l_doc := l_doc || ' '|| CLOSE_TR || NL ;
1315
1316 l_doc := l_doc || ' '|| TREE_ROW_SPACE || NL ;
1317 l_doc := l_doc || ' <!-- Change Object/Line Description --> ' || NL ;
1318 l_doc := l_doc || ' '|| OPEN_TR || NL ;
1319 l_doc := l_doc || ' ' || OPEN_PROMPT_TD || NL ;
1320 l_doc := l_doc || ' ' || CLOSE_PROMPT_TD(l_change_desc_p) || NL ;
1321 l_doc := l_doc || ' ' || CELL_SPACE || NL ;
1322 l_doc := l_doc || ' ' || OPEN_TD || NL ;
1323 l_doc := l_doc || ' ' || CLOSE_SINGLE_DATA_TD(l_change_description) || NL ;
1324 l_doc := l_doc || ' '|| CLOSE_TR || NL ;
1325
1326 IF l_mesg_attribute_rec.item_id IS NOT NULL THEN
1327
1328 l_doc := l_doc || ' '|| TREE_ROW_SPACE || NL ;
1329 l_doc := l_doc || ' <!-- Change Object/Line Item Subject Info --> ' || NL ;
1330 l_doc := l_doc || ' '|| OPEN_TR || NL ;
1331 l_doc := l_doc || ' ' || OPEN_PROMPT_TD || NL ;
1332 l_doc := l_doc || ' ' || CLOSE_PROMPT_TD(l_item_p) || NL ;
1333 l_doc := l_doc || ' ' || CELL_SPACE || NL ;
1334 l_doc := l_doc || ' ' || OPEN_TD || NL ;
1335 l_doc := l_doc || ' ' || OPEN_TAB || NL ;
1336 l_doc := l_doc || ' ' || OPEN_TR || NL ;
1337 l_doc := l_doc || ' ' || '<td width="10%" nowrap>' || HREF_URL(l_item_url, l_mesg_attribute_rec.item_name) || NL;
1338 l_doc := l_doc || ' ' || CLOSE_TD || NL ;
1339 l_doc := l_doc || ' ' || CELL_SPACE || NL ;
1340 l_doc := l_doc || ' ' || '<td align="left" valign="top">' || NL ;
1341 l_doc := l_doc || ' ' || OPEN_TAB || OPEN_TR || NL ;
1342 l_doc := l_doc || ' ' || OPEN_PROMPT_TD || NL ;
1343 l_doc := l_doc || ' ' || CLOSE_PROMPT_TD(l_item_revision_p) || NL ;
1344 l_doc := l_doc || ' ' || CELL_SPACE || NL ;
1345 l_doc := l_doc || ' ' || OPEN_TD || HREF_URL(l_item_revision_url, l_item_revision_label ) || NL;
1346 l_doc := l_doc || ' ' || CLOSE_TD || NL ;
1347 l_doc := l_doc || ' ' || CLOSE_TR || CLOSE_TAB || NL ;
1348 l_doc := l_doc || ' ' || CLOSE_TD || NL ;
1349 l_doc := l_doc || ' ' || CLOSE_TR || NL ;
1350 l_doc := l_doc || ' ' || CLOSE_TAB || NL ;
1351 l_doc := l_doc || ' ' || CLOSE_TD || NL ;
1352 l_doc := l_doc || ' '|| CLOSE_TR || NL ;
1353
1354 END IF ;
1355
1356 IF l_instrunctions_p IS NOT NULL THEN
1357
1358 l_doc := l_doc || ' '|| TREE_ROW_SPACE || NL ;
1359 l_doc := l_doc || ' <!-- Instrunction Subject info --> ' || NL ;
1360 l_doc := l_doc || ' '|| OPEN_TR || NL ;
1361 l_doc := l_doc || ' ' || OPEN_PROMPT_TD || NL ;
1362 l_doc := l_doc || ' ' || CLOSE_PROMPT_TD(l_instrunctions_p) || NL ;
1363 l_doc := l_doc || ' ' || CELL_SPACE || NL ;
1364 l_doc := l_doc || ' ' || OPEN_TD || NL ;
1365 l_doc := l_doc || ' ' || CLOSE_SINGLE_DATA_TD(l_mesg_attribute_rec.step_instruction) || NL ;
1366 l_doc := l_doc || ' '|| CLOSE_TR || NL ;
1367
1368 END IF ;
1369
1370
1371 IF l_action_p IS NOT NULL AND l_mesg_attribute_rec.action_desc IS NOT NULL THEN
1372
1373 l_doc := l_doc || ' '|| TREE_ROW_SPACE || NL ;
1374 l_doc := l_doc || ' <!-- Comment Request Subject info --> ' || NL ;
1375 l_doc := l_doc || ' '|| OPEN_TR || NL ;
1376 l_doc := l_doc || ' ' || OPEN_PROMPT_TD || NL ;
1377 l_doc := l_doc || ' ' || CLOSE_PROMPT_TD(l_action_p) || NL ;
1378 l_doc := l_doc || ' ' || CELL_SPACE || NL ;
1379 l_doc := l_doc || ' ' || OPEN_TD || NL ;
1380 l_doc := l_doc || ' ' || CLOSE_SINGLE_DATA_TD(l_mesg_attribute_rec.action_desc) || NL ;
1381 l_doc := l_doc || ' '|| CLOSE_TR || NL ;
1382
1383 END IF ;
1384
1385
1386 -- end of right side content table
1387 l_doc := l_doc || ' '|| CLOSE_TAB || NL ;
1388 l_doc := l_doc || ' '|| CLOSE_TD || NL ;
1389
1390 -- end of content child table
1391 l_doc := l_doc || ' '|| CLOSE_TR || NL ;
1392 l_doc := l_doc || ' '|| CLOSE_TAB || NL ;
1393
1394 -- end of content table
1395 l_doc := l_doc || CLOSE_TD || CLOSE_TR || NL ;
1396 l_doc := l_doc || CLOSE_TAB || NL ;
1397
1398 -- end of content record
1399 l_doc := l_doc || CLOSE_TR || CLOSE_TD || NL ;
1400
1401 -- end of additional info master table
1402 l_doc := l_doc || CLOSE_TAB || NL ;
1403
1404 --
1405 -- begin of detail link table
1406 l_doc := l_doc || OPEN_TAB_100 || NL ;
1407 l_doc := l_doc || SPACE_TR(7) || NL ;
1408 l_doc := l_doc || OPEN_TR || NL ;
1409 l_doc := l_doc || OPEN_TD || NL ;
1410
1411 -- begin of detail Page Link table
1412 l_doc := l_doc || ' <!-- Change Object Detail Page Link -->' || NL ;
1413 l_doc := l_doc || ' '|| OPEN_TAB_100 || NL ;
1414 l_doc := l_doc || ' '|| OPEN_TR || NL ;
1415 l_doc := l_doc || ' <td align="right">' || NL ;
1416 L_doc := l_doc || ' <a href="' || l_change_detail_url || '">' || l_change_detail_link || '</a></td>' || NL ;
1417 l_doc := l_doc || ' '|| CLOSE_TR || NL ;
1418 l_doc := l_doc || ' '|| CLOSE_TAB || NL ;
1419 -- end of detail Page Link table
1420
1421 -- end of detail link table
1422 l_doc := l_doc || CLOSE_TD || NL ;
1423 l_doc := l_doc || CLOSE_TR || NL ;
1424 l_doc := l_doc || CLOSE_TAB || NL ;
1425
1426
1427 -- Eng_Workflow_Util.Close_Debug_Session;
1428
1429 WF_NOTIFICATION.WriteToClob( document , l_doc);
1430
1431 -- Executing Custom Hook: Eng_Workflow_Ext.GetCustomMessageSubject
1432 Eng_Workflow_Ext.GetCustomMessageBody
1433 ( document_id => document_id
1434 , display_type => display_type
1435 , document => document
1436 , document_type => document_type
1437 ) ;
1438
1439
1440 END GetMessageHTMLBody ;
1441
1442
1443
1444
1445
1446
1447
1448 FUNCTION GetRunFuncURL
1449 ( p_function_name IN VARCHAR2
1450 , p_resp_appl_id IN NUMBER DEFAULT NULL
1451 , p_resp_id IN NUMBER DEFAULT NULL
1452 , p_security_group_id IN NUMBER DEFAULT NULL
1453 , p_parameters IN VARCHAR2 DEFAULT NULL
1454 ) RETURN VARCHAR2
1455 IS
1456
1457 l_function_id NUMBER ;
1458 l_resp_appl_id NUMBER ;
1459 l_resp_id NUMBER ;
1460 l_security_group_id NUMBER ;
1461
1462 BEGIN
1463
1464 l_function_id := fnd_function.get_function_id(p_function_name) ;
1465
1466
1467 IF p_resp_appl_id IS NULL THEN
1468 l_resp_appl_id := -1 ;
1469 END IF ;
1470
1471
1472 IF p_resp_id IS NULL THEN
1473 l_resp_id := -1 ;
1474 END IF ;
1475
1476 IF p_security_group_id IS NULL THEN
1477 l_security_group_id := -1 ;
1478 END IF ;
1479
1480 -- Call Fnd API
1481 RETURN fnd_run_function.get_run_function_url
1482 ( p_function_id => l_function_id
1483 , p_resp_appl_id => l_resp_appl_id
1484 , p_resp_id => l_resp_id
1485 , p_security_group_id => l_security_group_id
1486 , p_parameters => p_parameters ) ;
1487
1488
1489 END GetRunFuncURL ;
1490
1491
1492
1493 FUNCTION GetChangeRunFuncURL
1494 ( p_change_id IN NUMBER)
1495 RETURN VARCHAR2
1496 IS
1497
1498 BEGIN
1499
1500 RETURN GetRunFuncURL
1501 ( p_function_name => 'ENG_CHANGE_DETAIL_PAGE'
1502 , p_parameters => '&changeId=' || TO_CHAR(p_change_id) ) ;
1503
1504 END GetChangeRunFuncURL ;
1505
1506
1507
1508 FUNCTION GetChangeSummaryRunFuncURL
1509 ( p_change_id IN NUMBER)
1510 RETURN VARCHAR2
1511 IS
1512
1513 BEGIN
1514
1515 RETURN GetRunFuncURL
1516 ( p_function_name => 'ENG_CHANGE_SUMMARY_PAGE'
1517 , p_parameters => '&changeId=' || TO_CHAR(p_change_id) ) ;
1518
1519 END GetChangeSummaryRunFuncURL ;
1520
1521
1522 PROCEDURE GetNtfRecipient
1523 ( p_notification_id IN NUMBER
1524 , x_party_id OUT NOCOPY NUMBER
1525 , x_party_name OUT NOCOPY VARCHAR2
1526 , x_user_id OUT NOCOPY NUMBER
1527 , x_user_name OUT NOCOPY VARCHAR2
1528 )
1529 IS
1530
1531 CURSOR c_ntf_party (c_ntf_id NUMBER)
1532 IS
1533 SELECT u.USER_NAME
1534 , u.USER_ID
1535 , u.PARTY_ID
1536 , u.PARTY_NAME
1537 FROM EGO_USER_V u
1538 , WF_NOTIFICATIONS wn
1539 WHERE u.USER_NAME = wn.RECIPIENT_ROLE
1540 AND wn.NOTIFICATION_ID = c_ntf_id ;
1541
1542 BEGIN
1543
1544 FOR person_rec IN c_ntf_party(c_ntf_id => p_notification_id)
1545 LOOP
1546 x_user_name := person_rec.USER_NAME ;
1547 x_user_id := person_rec.USER_ID ;
1548 x_party_name := person_rec.PARTY_NAME ;
1549 x_party_id := person_rec.PARTY_ID ;
1550 END LOOP ;
1551
1552 EXCEPTION
1553 WHEN NO_DATA_FOUND THEN
1554 null ;
1555 END GetNtfRecipient ;
1556
1557
1558
1559 /********************************************************************
1560 * API Type : Public APIs
1561 * Purpose : Those APIs are public
1562 *********************************************************************/
1563 -- None
1564
1565 END Eng_Workflow_Ntf_Util ;