[Home] [Help]
PACKAGE BODY: APPS.IGP_VW_GEN_001_PKG
Source
1 PACKAGE BODY IGP_VW_GEN_001_PKG AS
2 /* $Header: IGSPVWAB.pls 120.0 2005/06/02 04:25:27 appldev noship $ */
3
4 /* +=======================================================================+
5 | Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | NAME |
9 | IGPVW01B.pls |
10 | |
11 | DESCRIPTION |
12 | This package provides service functions and procedures to |
13 | support Portfolio Assignments WF |
14 | |
15 | NOTES |
16 | |
17 | HISTORY |
18 | 04-APR-2001 ssawhney Created |
19 | ssawhney removed 9i declaration dependancies.
20 +=======================================================================+ */
21
22 PROCEDURE Create_Viewers_Role(
23 itemtype IN VARCHAR2,
24 itemkey IN VARCHAR2,
25 p_viewer_ids IN varchar2,
26 p_portfolio_ids IN varchar2 ) AS
27
28 /*--------------------------------------------------------------------------
29 -- Created by : ssawhney
30 -- Purpose : Get individual Viewers from the viewer's list.
31 -- Form an Adhoc Role and Add each viewer as a Role User.
32 --
33 -- History
34 ---------------------------------------------------------------------------*/
35
36 l_start_pos NUMBER(10);
37 l_end_pos NUMBER(10);
38 l_cur_pos NUMBER(10);
39 l_count NUMBER(10);
40 l_viewer_ids VARCHAR2(32000);
41 l_viewer_id hz_parties.party_id%TYPE;
42 l_status VARCHAR2(1) ;
43 l_port_name VARCHAR2(80);
44 l_author VARCHAR2(360);
45 l_pub_end_date VARCHAR2(20);
46 l_error_message VARCHAR2(500);
47
48 CURSOR c_viewer(cp_viewer IN NUMBER) IS
49 SELECT fnd.user_name VIEWER
50 FROM fnd_user fnd, igp_ac_accounts acc
51 WHERE acc.user_id = fnd.user_id AND acc.party_id = cp_viewer;
52
53 rec_viewer c_viewer%ROWTYPE;
54 l_stmt varchar2(32000);
55 l_port_id NUMBER(15);
56 l_role_name VARCHAR2(100);
57 l_role_display_name VARCHAR2(100) ;
58 l_cc_users VARCHAR2(100);
59
60 BEGIN
61 l_role_display_name := 'Adhoc Role for Viewer of Portfolios';
62 l_cc_users := 'SYSADMIN';
63 l_status :='S';
64 l_viewer_ids :=p_viewer_ids;
65
66 l_start_pos := 1;
67 l_end_pos := LENGTH(l_viewer_ids);
68 l_cur_pos := 1;
69
70 l_role_name := 'IGPVW1VW'||itemkey;
71
72 -- create adhoc role which will have all the viewers who have been assigned.
73 -- so loop through the viewers list and keep on adding them..
74
75 Wf_Directory.CreateAdHocRole (
76 role_name => l_role_name,
77 role_display_name => l_role_display_name );
78
79
80 IF (l_viewer_ids IS NOT NULL) THEN
81
82 LOOP
83
84 l_cur_pos := INSTR(l_viewer_ids,',',l_start_pos,1);
85 IF (l_cur_pos = 0) THEN
86 l_viewer_id := SUBSTR(l_viewer_ids, l_start_pos, l_end_pos - l_start_pos + 1);
87 ELSE
88 l_viewer_id := SUBSTR(l_viewer_ids, l_start_pos, l_cur_pos - l_start_pos);
89 END IF;
90
91 -- get fnd user name, the Adhoc Role will send mail to FND USER.
92
93 OPEN c_viewer(l_viewer_id);
94 FETCH c_viewer INTO rec_viewer;
95 EXIT WHEN c_viewer%NOTFOUND;
96 CLOSE c_viewer;
97
98 Wf_Directory.AddUsersToAdHocRole (
99 role_name => l_role_name,
100 role_users => rec_viewer.viewer );
101
102
103
104 IF (l_cur_pos = 0) THEN
105 EXIT; -- Exit loop
106 END IF;
107
108 l_start_pos := l_cur_pos + 1;
109
110
111 END LOOP;
112
113 -- Set WF attrib for Role here.
114
115 Wf_Engine.SetItemAttrText(
116 ItemType => itemtype,
117 ItemKey => itemkey,
118 aname => 'P_VIEWER_ROLE',
119 avalue => l_role_name );
120 Wf_Engine.SetItemAttrText(
121 ItemType => itemtype,
122 ItemKey => itemkey,
123 aname => 'FROM',
124 avalue => l_cc_users );
125
126 END IF;
127
128
129 EXCEPTION
130 WHEN others THEN
131 l_error_message := sqlerrm;
132 wf_core.context('igp_wf_gen_001_pkg','Create_Viewers_Role',itemtype,itemkey ,l_error_message);
133 RAISE;
134
135
136 END Create_Viewers_Role;
137
138
139
140
141 PROCEDURE Write_Viewer_Message
142 (
143 document_id IN VARCHAR2,
144 display_type IN VARCHAR2,
145 document IN OUT NOCOPY CLOB,
146 document_type IN OUT NOCOPY VARCHAR2
147 ) AS
148 /*--------------------------------------------------------------------------
149 -- Created by : ssawhney
150 -- Purpose : Proc written in a specific format to handle CLOB item attrib loading
151 --
152 -- History
153 ---------------------------------------------------------------------------*/
154 l_item_type VARCHAR2(300);
155 l_item_key VARCHAR2(300);
156 l_item VARCHAR2(300);
157 l_message VARCHAR2(32000);
158 l_portfolio_ids VARCHAR2(32000);
159 l_exp_date VARCHAR2(20);
160 -- Start Local Procedure..
161
162 PROCEDURE Create_Viewers_Message_Html
163 ( p_portfolio_ids IN VARCHAR2,
164 p_expdate IN VARCHAR2,
165 p_message_text OUT NOCOPY VARCHAR2
166 )
167 IS
168 /*--------------------------------------------------------------------------
169 -- Created by : ssawhney
170 -- Purpose : Proc written to generate the HTML message, generating the message outside the WriteAuthMessage
171 -- was not working, so we had to move the code here.
172 -- History
173 ---------------------------------------------------------------------------*/
174 l_start_pos NUMBER(10);
175 l_end_pos NUMBER(10);
176 l_cur_pos NUMBER(10);
177 l_count NUMBER(10);
178 l_portfolio_ids VARCHAR2(32000);
179 l_basic_text VARCHAR2(32000);
180 l_port_id NUMBER(10);
181
182
183 -- if the expiry date is passed, then dont query for it and add it in the table html syntax in the query itself.
184 nbsp VARCHAR2(10) ;
185 CURSOR c_port_info(cp_port_ids IN NUMBER) IS
186 SELECT '<td align="center">'||(RPAD(NVL(port.portfolio_name, nbsp), 80))||'</td>' name,
187 '<td align="center">'||(RPAD(NVL(NVL(p_expdate,' '),nbsp), 13))||'</td>' pub_end_date,
188 '<td align="center">'||(RPAD(NVL((hz.person_last_name||','||hz.person_first_name),nbsp), 360))||'</td>' AUTHOR
189 FROM igp_us_portfolios port, igp_ac_accounts acc, hz_parties hz
190 WHERE port.account_id = acc.account_id AND
191 acc.party_id = hz.party_id AND
192 port.portfolio_id = cp_port_ids ;
193 rec_port_info c_port_info%ROWTYPE;
194
195 BEGIN
196 l_basic_text :=' ';
197 nbsp := fnd_global.local_chr(38) || 'nbsp;';
198 l_portfolio_ids := p_portfolio_ids;
199
200 -- Get details of All Portfolios passed in the string.
201 -- LOOP and keep on adding into the HTML message
202
203 IF (l_portfolio_ids IS NOT NULL) THEN
204
205 l_start_pos := 1;
206 l_end_pos := LENGTH(l_portfolio_ids);
207 l_cur_pos := 1;
208
209 LOOP
210 l_cur_pos := INSTR(l_portfolio_ids,',',l_start_pos,1);
211 IF (l_cur_pos = 0) THEN
212 l_port_id := SUBSTR(l_portfolio_ids, l_start_pos, l_end_pos - l_start_pos + 1);
213 ELSE
214 l_port_id := SUBSTR(l_portfolio_ids, l_start_pos, l_cur_pos - l_start_pos);
215 END IF;
216
217 OPEN c_port_info(l_port_id) ;
218 FETCH c_port_info INTO rec_port_info;
219 CLOSE c_port_info;
220
221
222 l_basic_text := l_basic_text ||
223 '<tr>' ||
224 rec_port_info.name ||
225 rec_port_info.author ||
226 rec_port_info.pub_end_date
227 ||'</tr>';
228
229 IF (l_cur_pos = 0) THEN
230 EXIT; -- Exit loop
231 END IF;
232
233 l_start_pos := l_cur_pos + 1;
234
235 END LOOP;
236 -- close the HTML syntax for table.
237 l_basic_text := l_basic_text ||'</table>';
238
239 END IF;
240
241 p_message_text :=l_basic_text;
242
243 -- exception case when no portfolio passed,
244 IF l_basic_text IS NULL THEN
245 l_basic_text := '<tr'||'<\tr>'||'</table>';
246 p_message_text := l_basic_text;
247 END IF;
248
249
250 EXCEPTION
251 WHEN OTHERS THEN
252 IF l_basic_text IS NULL THEN
253 l_basic_text := '<tr'||'<\tr>'||'</table>';
254 p_message_text := l_basic_text;
255 END IF;
256
257 END Create_Viewers_Message_html;
258
259 -- End Local Procedure..
260
261
262 BEGIN
263 IF document_id IS NOT NULL THEN
264
265 --Fetch the item Type, Item Key and Item Name from the passed Document ID.
266 l_item_type := substr(document_id, 1, instr(document_id,':')-1);
267 l_item_key := substr (document_id, INSTR(document_id, ':') +1, (INSTR(document_id, '*') - INSTR(document_id, ':'))-1) ;
268 l_item := substr(document_id, instr(document_id,'*')+1);
269 l_message := NULL;
270 IF l_item IS NOT NULL THEN
271 --
272 -- If the Item Name is not null then get the value of the Portfolios to form the message again.
273 --
274 l_portfolio_ids := wf_engine.GetItemAttrText( itemtype => l_item_type,
275 itemkey => l_item_key,
276 aname => 'P_PORTFOLIO_IDS');
277 -- if the event is raised when the author assigns the end date, then p_expdate parameter will be recieved.
278 -- else if the access is given by the Career Center then the p_expdate will be NULL.
279
280 l_exp_date := wf_engine.GetItemAttrText( itemtype => l_item_type,
281 itemkey => l_item_key,
282 aname => 'P_EXPDATE');
283 END IF;
284
285
286 END IF;
287
288 -- call local procedure to generate the HTML message.
289
290 Create_Viewers_Message_html(l_portfolio_ids,l_exp_date,l_message);
291
292
293 -- Write the header doc into CLOB variable
294 WF_NOTIFICATION.WriteToClob(document, l_message);
295 EXCEPTION
296 WHEN OTHERS THEN
297 l_message := sqlerrm;
298 wf_core.context('igp_wf_gen_001_pkg','Write_Viewer_Message',l_item_type,l_item_key ,l_message);
299 RAISE;
300
301 END Write_Viewer_Message;
302
303
304 PROCEDURE Get_Viewer_Inform_Det (
305 itemtype IN VARCHAR2,
306 itemkey IN VARCHAR2,
307 actid IN NUMBER ,
308 funcmode IN VARCHAR2,
309 resultout OUT NOCOPY VARCHAR2) AS
310 /*--------------------------------------------------------------------------
311 -- Created by : ssawhney
312 -- Purpose : Workflow function call, this would handle the setting of all wf attributes.
313 --
314 -- History
315 ---------------------------------------------------------------------------*/
316 l_error_message VARCHAR2(500);
317 l_sysdate DATE;
318
319 BEGIN
320 l_sysdate := SYSDATE;
321 IF (funcmode = 'RUN') THEN
322
323 Create_Viewers_Role (itemtype => itemtype,
324 itemkey => itemkey,
325 p_viewer_ids => wf_engine.getitemattrtext(itemtype,itemkey,'P_VIEWER_IDS'),
326 p_portfolio_ids => wf_engine.getitemattrtext(itemtype,itemkey,'P_PORTFOLIO_IDS') );
327
328 -- standard way to call PLSQLCLOB. Dont modify.
329 wf_engine.setitemattrtext(ItemType => itemtype,
330 ItemKey => itemkey,
331 aname => 'P_VIEWER_MESSAGE',
332 avalue => 'PLSQLCLOB:igp_vw_gen_001_pkg.write_viewer_message/'||itemtype||':'||itemkey||'*P_VIEWER_MESSAGE');
333
334 wf_engine.setitemattrtext(ItemType => itemtype,
335 ItemKey => itemkey,
336 aname => 'P_SYSDATE',
337 avalue => l_sysdate);
338
339 resultout := 'COMPLETE';
340
341 RETURN;
342 END IF;
343
344 EXCEPTION
345 -- trap exceptions while setting workflow attribs..
346 WHEN OTHERS THEN
347 l_error_message := sqlerrm;
348 wf_core.context('igp_wf_gen_001_pkg','Get_Viewer_Inform_Det',itemtype,itemkey ,l_error_message);
349 RAISE;
350
351 END Get_Viewer_Inform_Det;
352
353
354
355
356 PROCEDURE Raise_Assign_Event
357 (
358 p_viewer_ids IN varchar2,
359 p_portfolio_ids IN varchar2,
360 p_access_exp_date IN varchar2
361 ) AS
362 /*--------------------------------------------------------------------------
363 -- Created by : ssawhney
364 -- Purpose : Get individual Authors of the portfolio from the portfolios's list.
365 -- Get Viewers details.
366 -- Formulate a html message text to be sent to author for all viewers that got assigned to his portfolio.
367 -- History
368 ---------------------------------------------------------------------------*/
369
370 CURSOR c_get_seq_val
371 IS
372 select igp_ac_bus_events_s.nextval
373 from dual;
374
375 l_event_t wf_event_t;
376 l_viewer_event VARCHAR2(50);
377 l_seq_val_vw VARCHAR2(100);
378 l_parameter_list_t wf_parameter_list_t;
379 l_error_message VARCHAR2(500);
380 l_seq_val number;
381
382 BEGIN
383
384 l_viewer_event := 'oracle.apps.igs.igp.vw.informviewer';
385 OPEN c_get_seq_val;
386 FETCH c_get_seq_val INTO l_seq_val;
387 CLOSE c_get_seq_val;
388 l_seq_val_vw:=to_char(l_seq_val);
389 --
390 -- initialize the wf_event_t object
391 --
392 wf_event_t.initialize(l_event_t);
393
394 --
395 -- Adding the parameters to the parameter list
396 --
397 wf_event.addparametertolist( p_name => 'P_VIEWER_IDS',
398 p_value => p_viewer_ids ,
399 p_parameterlist => l_parameter_list_t);
400
401 wf_event.addparametertolist( p_name => 'P_PORTFOLIO_IDS',
402 p_value => p_portfolio_ids,
403 p_parameterlist => l_parameter_list_t);
404
405 wf_event.addparametertolist( p_name => 'P_EXPDATE',
406 p_value => p_access_exp_date,
407 p_parameterlist => l_parameter_list_t);
408
409 --Raise the events...
413
410 wf_event.raise (p_event_name => l_viewer_event,
411 p_event_key => l_seq_val_vw,
412 p_parameters => l_parameter_list_t);
414 --
415 -- Deleting the Parameter list after the event is raised
416 --
417 l_parameter_list_t.delete;
418
419
420 END Raise_Assign_Event;
421
422
423
424
425 PROCEDURE Create_Author_Message
426 ( p_viewer_ids IN VARCHAR2,
427 p_message_text OUT NOCOPY VARCHAR) AS
428 /*--------------------------------------------------------------------------
429 -- Created by : ssawhney
430 -- Purpose : Create a Static HTML message for AUTHOR.
431 --
432 -- History
433 ---------------------------------------------------------------------------*/
434 l_start_pos NUMBER(10);
435 l_end_pos NUMBER(10);
436 l_cur_pos NUMBER(10);
437 l_count NUMBER(10);
438 l_viewer_ids VARCHAR2(32000);
439 l_viewer_id hz_parties.party_id%TYPE;
440 l_basic_text VARCHAR2(32000) ;
441
442 nbsp VARCHAR2(10) ;
443
444 CURSOR c_viewer_info(cp_viewer IN NUMBER) IS
445 SELECT '<td align="left">'||(RPAD(NVL((hz.person_last_name||','||hz.person_first_name),nbsp), 360))||'</td>' VIEWER,
446 '<td align="left"><a href=mailto:'|| fu.email_address||'>'||fu.email_address||'</a></td>' EMAIL
447 FROM hz_parties hz,
448 fnd_user fu,
449 igp_ac_accounts acc
450 WHERE hz.party_id = cp_viewer
451 AND acc.party_id=hz.party_id
452 AND acc.user_id = fu.user_id;
453
454 rec_viewer_info c_viewer_info%ROWTYPE;
455
456 BEGIN
457
458 nbsp := fnd_global.local_chr(38) || 'nbsp;';
459
460 l_viewer_ids :=p_viewer_ids;
461
462 -- LOOP through all viewer passed in the contacte-nated string. get details of individual and form a HTML text.
463 IF (l_viewer_ids IS NOT NULL) THEN
464
465 l_start_pos := 1;
466 l_end_pos := LENGTH(l_viewer_ids);
467 l_cur_pos := 1;
468
469 LOOP
470
471 l_cur_pos := INSTR(l_viewer_ids,',',l_start_pos,1);
472
473 IF (l_cur_pos = 0) THEN
474 l_viewer_id := SUBSTR(l_viewer_ids, l_start_pos, l_end_pos - l_start_pos + 1);
475 ELSE
476 l_viewer_id := SUBSTR(l_viewer_ids, l_start_pos, l_cur_pos - l_start_pos);
477 END IF;
478
479 OPEN c_viewer_info(l_viewer_id) ;
480 FETCH c_viewer_info INTO rec_viewer_info;
481 CLOSE c_viewer_info;
482
483 l_basic_text := l_basic_text ||'<tr>' || rec_viewer_info.viewer||rec_viewer_info.email ||'</tr>';
484
485 IF (l_cur_pos = 0) THEN
486 EXIT; -- Exit loop
487 END IF;
488
489 l_start_pos := l_cur_pos + 1;
490
491 END LOOP;
492
493 -- close the HTML syntax for table.
494 l_basic_text := l_basic_text ||'</table>';
495 p_message_text := l_basic_text;
496 END IF;
497
498 IF l_basic_text IS NULL THEN
499 l_basic_text := '<tr>'||'<\tr>'||'</table>';
500 p_message_text := l_basic_text;
501 END IF;
502
503 EXCEPTION
504 WHEN OTHERS THEN
505 IF l_basic_text IS NULL THEN
506 l_basic_text := '<tr'||'<\tr>'||'</table>';
507 p_message_text := l_basic_text;
508 END IF;
509
510 END Create_Author_Message;
511
512
513 PROCEDURE Raise_Inform_Author_Event
514 (
515 p_viewer_ids IN varchar2,
516 p_portfolio_ids IN varchar2
517 ) AS
518 /*--------------------------------------------------------------------------
519 -- Created by : ssawhney
520 -- Purpose : Get individual Authors of the portfolio from the portfolios's list.
521 -- Get Viewers details.
522 -- Formulate a html message text to be sent to author for all viewers that got assigned to his portfolio.
523 -- History
524 ---------------------------------------------------------------------------*/
525
526 CURSOR c_get_seq_val
527 IS
528 select igp_ac_bus_events_s.nextval
529 from dual;
530
531 l_event_t wf_event_t;
532 l_author_event VARCHAR2(50);
533 l_seq_val_au VARCHAR2(100);
534 l_parameter_list_t wf_parameter_list_t;
535 l_message_text VARCHAR2(32000);
536
537 l_start_pos NUMBER(10);
538 l_end_pos NUMBER(10);
539 l_cur_pos NUMBER(10);
540 l_count NUMBER(10);
541 l_viewer_ids VARCHAR2(32000);
542 l_viewer_id hz_parties.party_id%TYPE;
543 l_portfolio_ids VARCHAR2(32000);
544 l_basic_text VARCHAR2(32000);
545 l_seq_num number;
546
547 nbsp VARCHAR2(10);
548
549 CURSOR c_port_info(cp_port_ids IN NUMBER) IS
550 SELECT port.portfolio_name, fnd.user_name
554 port.portfolio_id = cp_port_ids ;
551 FROM igp_us_portfolios port, igp_ac_accounts acc, fnd_user fnd
552 WHERE port.account_id = acc.account_id AND
553 fnd.user_id = acc.user_id AND
555
556 rec_port_info c_port_info%ROWTYPE;
557 l_stmt varchar2(32000);
558 l_port_id NUMBER(15);
559
560 BEGIN
561 nbsp := fnd_global.local_chr(38) || 'nbsp;';
562
563 l_author_event := 'oracle.apps.igs.igp.vw.informauthor';
564
565 OPEN c_get_seq_val;
566 FETCH c_get_seq_val INTO l_seq_num;
567 CLOSE c_get_seq_val;
568 l_seq_val_au:=to_char(l_seq_num);
569
570 --
571 -- initialize the wf_event_t object
572 --
573 wf_event_t.initialize(l_event_t);
574
575 --
576 -- Adding the parameters to the parameter list
577 --
578 -- call the package to make a static message.
579 --
580
581 Create_Author_Message(p_viewer_ids,l_message_text);
582
583 wf_event.addparametertolist( p_name => 'P_MESSAGE_TEXT',
584 p_value => l_message_text,
585 p_parameterlist => l_parameter_list_t);
586
587 -- loop for items in the Portfolio List...get individual author and the portfolio name.
588 -- raise multiple BEs, cause we want to show author and portfolio information both
589 -- hence we can not have the ADHOC role logic here.
590
591 l_portfolio_ids:= p_portfolio_ids;
592
593 l_start_pos := 1;
594 l_end_pos := LENGTH(l_portfolio_ids);
595 l_cur_pos := 1;
596
597 IF (l_portfolio_ids IS NOT NULL) THEN
598 LOOP
599
600 l_cur_pos := INSTR(l_portfolio_ids,',',l_start_pos,1);
601 IF (l_cur_pos = 0) THEN
602 l_port_id := SUBSTR(l_portfolio_ids, l_start_pos, l_end_pos - l_start_pos + 1);
603 ELSE
604 l_port_id := SUBSTR(l_portfolio_ids, l_start_pos, l_cur_pos - l_start_pos);
605 END IF;
606
607 OPEN c_port_info(l_port_id);
608 FETCH c_port_info INTO rec_port_info;
609 CLOSE c_port_info;
610
611 wf_event.addparametertolist( p_name => 'P_PORT_NAME',
612 p_value => rec_port_info.portfolio_name,
613 p_parameterlist => l_parameter_list_t);
614
615 wf_event.addparametertolist( p_name => 'P_AUTHOR_ROLE',
616 p_value => rec_port_info.user_name,
617 p_parameterlist => l_parameter_list_t);
618
619 --Raise the event...
620 wf_event.raise ( p_event_name => l_author_event,
621 p_event_key => l_seq_val_au,
622 p_parameters => l_parameter_list_t);
623
624 IF (l_cur_pos = 0) THEN
625 EXIT; -- Exit loop
626 END IF;
627
628 l_start_pos := l_cur_pos + 1;
629 END LOOP;
630 END IF;
631 --
632 -- Deleting the Parameter list after the event is raised
633 --
634 l_parameter_list_t.delete;
635
636
637 END Raise_Inform_Author_Event;
638
639
640
641 PROCEDURE Write_Author_Message
642 (
643 document_id IN VARCHAR2,
644 display_type IN VARCHAR2,
645 document IN OUT NOCOPY CLOB,
646 document_type IN OUT NOCOPY VARCHAR2
647 ) AS
648 /*--------------------------------------------------------------------------
649 -- Created by : ssawhney
650 -- Purpose : Standard format of proc, to be used to write CLOB into Attribute to be picked by Notification.
651 -- See workflow standards for more details
652 -- History
653 ---------------------------------------------------------------------------*/
657 l_message_text VARCHAR2(32000);
654 l_item_type VARCHAR2(100);
655 l_item_key VARCHAR2(100);
656 l_item VARCHAR2(100);
658 BEGIN
659
660 l_item_type := substr(document_id, 1, instr(document_id,':')-1);
661 l_item_key := substr (document_id, INSTR(document_id, ':') +1, (INSTR(document_id, '*') - INSTR(document_id, ':'))-1) ;
662 l_item := substr(document_id, instr(document_id,'*')+1);
663
664 l_message_text := wf_engine.GetItemAttrText( itemtype => l_item_type,
665 itemkey => l_item_key,
666 aname => 'P_MESSAGE_TEXT');
667 -- Write the header doc into CLOB variable
668 WF_NOTIFICATION.WriteToClob(document, l_message_text);
669
670 EXCEPTION
671 WHEN OTHERS THEN
672 l_message_text := sqlerrm;
673 wf_core.context('igp_wf_gen_001_pkg','Write_Author_Message',l_item_type,l_item_key ,l_message_text);
674 RAISE;
675 END Write_Author_Message;
676
677
678 PROCEDURE Get_Author_Det (
679 itemtype IN VARCHAR2,
680 itemkey IN VARCHAR2,
681 actid IN NUMBER ,
682 funcmode IN VARCHAR2,
683 resultout OUT NOCOPY VARCHAR2) AS
684 /*--------------------------------------------------------------------------
685 -- Created by : ssawhney
686 -- Purpose : Workflow function call, this would handle the setting of all wf attributes. for the Author BE.
687 --
688 -- History
689 ---------------------------------------------------------------------------*/
690 l_message VARCHAR2(500);
691 l_sysdate DATE ;
692 l_users VARCHAR2(30);
693 BEGIN
694
695 l_sysdate := SYSDATE;
696 l_users :='SYSADMIN';
697
698 IF (funcmode = 'RUN') THEN
699
700 -- standard way to write a CLOB in workflow. dont change.
701 wf_engine.setitemattrtext(ItemType => itemtype,
702 ItemKey => itemkey,
703 aname => 'P_AUTHOR_MESSAGE',
704 avalue => 'PLSQLCLOB:igp_vw_gen_001_pkg.write_author_message/'||itemtype||':'||itemkey||'*P_AUTHOR_MESSAGE');
705
706 wf_engine.setitemattrtext(ItemType => itemtype,
707 ItemKey => itemkey,
708 aname => 'P_SYSDATE',
709 avalue => l_sysdate);
710 Wf_Engine.SetItemAttrText(
711 ItemType => itemtype,
712 ItemKey => itemkey,
713 aname => 'FROM',
714 avalue => l_users );
715
716
717
718 resultout := 'COMPLETE';
719
720 RETURN;
721 END IF;
722
723 EXCEPTION
724
725 WHEN OTHERS THEN
726 l_message := sqlerrm;
727 wf_core.context('igp_wf_gen_001_pkg','Get_Author_Det',itemtype,itemkey ,l_message);
728 RAISE;
729
730 END Get_Author_Det;
731
732
733
734 END IGP_VW_GEN_001_PKG;