1 PACKAGE BODY IGP_VW_GEN_002_PKG AS
2 /* $Header: IGSPVWBB.pls 120.2 2006/01/27 01:56:00 bmerugu 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 removal WF |
14 | |
15 | NOTES |
16 | |
17 | HISTORY |
18 | 04-Mar-2004 ssawhney Created |
19 | 17-Mar-2004 ssawhney l_viewer_id was not assigned in Author event
20 13-Apr-2004 ssawhney removed 9i declaration dependancies.
21 +=======================================================================+ */
22
23 PROCEDURE Create_CC_Role(
24 itemtype IN VARCHAR2,
25 itemkey IN VARCHAR2
26 ) AS
27
28 /*--------------------------------------------------------------------------
29 -- Created by : ssawhney
30 -- Purpose : Get individual FND users holding Career Center Resp from the DB.
31 -- Form an Adhoc Role and Add each fnd user as a Role User.
32 --
33 -- History
34 ---------------------------------------------------------------------------*/
35
36 l_error_message VARCHAR2(500);
37
38 CURSOR c_career_center(cp_app_id IN NUMBER,cp_resp_key IN VARCHAR) IS
39 SELECT fnd.user_name
40 FROM fnd_user fnd, fnd_responsibility_vl resp , fnd_user_resp_groups_direct rg
41 WHERE rg.user_id = fnd.user_id AND
42 rg.responsibility_id = resp.responsibility_id AND
43 rg.responsibility_application_id = cp_app_id AND
44 resp.responsibility_key = cp_resp_key ;
45
46
47 rec_career_center c_career_center%ROWTYPE;
48 l_role_name VARCHAR2(100);
49 l_role_display_name VARCHAR2(100);
50
51
52 BEGIN
53
54 fnd_message.set_name('IGS','IGP_VW_CC_TITLE'); -- Fetch the display name 'Career Administrator' from this message.
55 l_role_display_name := fnd_message.get;
56 l_role_name := 'IGPVW1VW'||itemkey;
57
58 -- create adhoc role which will have all the fnd_users who have been assigned career center resp.
59 -- so loop through the data and keep on adding them..
60
61 Wf_Directory.CreateAdHocRole (
62 role_name => l_role_name,
63 role_display_name => l_role_display_name );
64
65 FOR rec_career_center IN c_career_center(8405,'IGP_CAREER_CENTRE')
66 LOOP
67 -- get fnd user name, the Adhoc Role will send mail to FND USER.
68 Wf_Directory.AddUsersToAdHocRole (
69 role_name => l_role_name,
70 role_users => rec_career_center.user_name );
71 END LOOP;
72
73 -- Set WF attrib for Role here.
74
75 Wf_Engine.SetItemAttrText(
76 ItemType => itemtype,
77 ItemKey => itemkey,
78 aname => 'P_CC_ROLE',
79 avalue => l_role_name );
80
81
82 EXCEPTION
83 WHEN others THEN
84 l_error_message := sqlerrm;
85 wf_core.context('igp_wf_gen_002_pkg','Create_CC_Role',itemtype,itemkey ,l_error_message);
86 RAISE;
87
88
89 END Create_CC_Role;
90
91
92
93
94 PROCEDURE Write_CC_Message
95 (
96 document_id IN VARCHAR2,
97 display_type IN VARCHAR2,
98 document IN OUT NOCOPY CLOB,
99 document_type IN OUT NOCOPY VARCHAR2
100 ) AS
101 /*--------------------------------------------------------------------------
102 -- Created by : ssawhney
103 -- Purpose : Proc written in a specific format to handle CLOB item attrib loading
104 --
105 -- History
106 ---------------------------------------------------------------------------*/
107 l_item_type VARCHAR2(300);
108 l_item_key VARCHAR2(300);
109 l_item VARCHAR2(32000);
110 l_message VARCHAR2(32000);
111 l_portfolio_ids VARCHAR2(32000);
112
113 -- Start Local Procedure..
114
115 PROCEDURE Create_CC_Message_Html
116 ( p_portfolio_ids IN VARCHAR2,
117 p_message_text OUT NOCOPY VARCHAR2
118 )
119 IS
120 /*--------------------------------------------------------------------------
121 -- Created by : ssawhney
122 -- Purpose : Proc written to generate the HTML message, generating the message outside the WriteCCMessage
123 -- was not working, so we had to move the code here.
124 -- History
125 ---------------------------------------------------------------------------*/
126 l_start_pos NUMBER(10);
127 l_end_pos NUMBER(10);
128 l_cur_pos NUMBER(10);
129 l_count NUMBER(10);
130 l_portfolio_ids VARCHAR2(32000);
131 l_basic_text VARCHAR2(32000);
132 l_port_id NUMBER(10);
133
134
135 nbsp VARCHAR2(10) ;
136 CURSOR c_port_info(cp_port_ids IN NUMBER) IS
137 SELECT '<td align="center">'||(RPAD(NVL(port.portfolio_name, nbsp), 80))||'</td>' name,
138 '<td align="center">'||(RPAD(NVL(DECODE(port.pub_end_date,to_date('31/12/4172','dd/mm/yyyy'),NULL,port.pub_end_date),nbsp), 13))||'</td>' pub_end_date,
139 '<td align="center">'||(RPAD(NVL((hz.person_last_name||','||hz.person_first_name),nbsp), 360))||'</td>' AUTHOR
140 FROM igp_us_portfolios port, igp_ac_accounts acc, hz_parties hz
141 WHERE port.account_id = acc.account_id AND
142 acc.party_id = hz.party_id AND
143 port.portfolio_id = cp_port_ids ;
144 rec_port_info c_port_info%ROWTYPE;
145
146 BEGIN
147 l_basic_text :=' ';
148 nbsp := fnd_global.local_chr(38) || 'nbsp;';
149 l_portfolio_ids := p_portfolio_ids;
150
151 -- Get details of All Portfolios passed in the string.
152 -- LOOP and keep on adding into the HTML message
153
154 IF (l_portfolio_ids IS NOT NULL) THEN
155
156 l_start_pos := 1;
157 l_end_pos := LENGTH(l_portfolio_ids);
158 l_cur_pos := 1;
159
160 LOOP
161 l_cur_pos := INSTR(l_portfolio_ids,',',l_start_pos,1);
162 IF (l_cur_pos = 0) THEN
163 l_port_id := SUBSTR(l_portfolio_ids, l_start_pos, l_end_pos - l_start_pos + 1);
164 ELSE
165 l_port_id := SUBSTR(l_portfolio_ids, l_start_pos, l_cur_pos - l_start_pos);
166 END IF;
167
168 OPEN c_port_info(l_port_id) ;
169 FETCH c_port_info INTO rec_port_info;
170 CLOSE c_port_info;
171
172 l_basic_text := l_basic_text ||
173 '<tr>' ||
174 rec_port_info.name ||
175 rec_port_info.author ||
176 rec_port_info.pub_end_date ||
177 '</tr>';
178
179 IF (l_cur_pos = 0) THEN
180 EXIT; -- Exit loop
181 END IF;
182
183 l_start_pos := l_cur_pos + 1;
184
185 END LOOP;
186 -- close the HTML syntax for table.
187 l_basic_text := l_basic_text ||'</table>';
188
189 END IF;
190
191 p_message_text :=l_basic_text;
192
193 -- exception case when no portfolio passed,
194 IF l_basic_text IS NULL THEN
195 l_basic_text := '<tr'||'<\tr>'||'</table>';
196 p_message_text := l_basic_text;
197 END IF;
198
199
200 EXCEPTION
201 WHEN OTHERS THEN
202 IF l_basic_text IS NULL THEN
203 l_basic_text := '<tr'||'<\tr>'||'</table>';
204 p_message_text := l_basic_text;
205 END IF;
206
207 END Create_CC_Message_html;
208
209 -- End Local Procedure..
210
211
212 BEGIN
213 IF document_id IS NOT NULL THEN
214
215 --Fetch the item Type, Item Key and Item Name from the passed Document ID.
216 l_item_type := substr(document_id, 1, instr(document_id,':')-1);
217 l_item_key := substr (document_id, INSTR(document_id, ':') +1, (INSTR(document_id, '*') - INSTR(document_id, ':'))-1) ;
218 l_item := substr(document_id, instr(document_id,'*')+1);
219 l_message := NULL;
220 IF l_item IS NOT NULL THEN
221 --
222 -- If the Item Name is not null then get the value of the Portfolios to form the message again.
223 --
224 l_portfolio_ids := wf_engine.GetItemAttrText( itemtype => l_item_type,
225 itemkey => l_item_key,
226 aname => 'P_PORTFOLIO_IDS');
227 END IF;
228
229
230 END IF;
231
232 -- call local procedure to generate the HTML message.
233
234 Create_CC_Message_html(l_portfolio_ids,l_message);
235
236
237 -- Write the header doc into CLOB variable
238 WF_NOTIFICATION.WriteToClob(document, l_message);
239 EXCEPTION
240 WHEN OTHERS THEN
241 l_message := sqlerrm;
242 wf_core.context('igp_wf_gen_002_pkg','Write_CC_Message',l_item_type,l_item_key ,l_message);
243 RAISE;
244
245 END Write_CC_Message;
246
247
248 PROCEDURE Get_Inform_Det (
249 itemtype IN VARCHAR2,
250 itemkey IN VARCHAR2,
251 actid IN NUMBER ,
252 funcmode IN VARCHAR2,
253 resultout OUT NOCOPY VARCHAR2) AS
254 /*--------------------------------------------------------------------------
255 -- Created by : ssawhney
256 -- Purpose : Workflow function call, this would handle the setting of all wf attributes.
257 --
258 -- History
259 ---------------------------------------------------------------------------*/
260 l_error_message VARCHAR2(500);
261 l_sysdate DATE ;
262
263 BEGIN
264
265 IF (funcmode = 'RUN') THEN
266
267 l_sysdate := SYSDATE+1;
268 Create_CC_Role ( itemtype => itemtype,
269 itemkey => itemkey );
270
271 -- standard way to call PLSQLCLOB. Dont modify.
272 wf_engine.setitemattrtext(ItemType => itemtype,
273 ItemKey => itemkey,
274 aname => 'P_CC_MESSAGE',
275 avalue => 'PLSQLCLOB:igp_vw_gen_002_pkg.write_cc_message/'||itemtype||':'||itemkey||'*P_CC_MESSAGE');
276
277 wf_engine.setitemattrtext(ItemType => itemtype,
278 ItemKey => itemkey,
279 aname => 'P_SYSDATE',
280 avalue => l_sysdate);
281
282
283 resultout := 'COMPLETE';
284
285 RETURN;
286 END IF;
287
288 EXCEPTION
289 -- trap exceptions while setting workflow attribs..
290 WHEN OTHERS THEN
291 l_error_message := sqlerrm;
292 wf_core.context('igp_wf_gen_002_pkg','Get_Viewer_Det',itemtype,itemkey ,l_error_message);
293 RAISE;
294
295 END Get_Inform_Det;
296
297
298
299
300 PROCEDURE Raise_Removal_Event_CC
301 (
302 p_viewer_id IN varchar2,
303 p_portfolio_ids IN varchar2,
304 p_CC_user_name IN varchar2
305 ) AS
306 /*--------------------------------------------------------------------------
307 -- Created by : ssawhney
308 -- Purpose : Get individual Authors of the portfolio from the portfolios's list.
309 -- Get Viewers details.
310 -- Formulate a html message text to be sent to carer center for portfolio assignments removed from a viewer.
311 -- History
312 -- nsidana 4/22/2004 Populating the WF param for displaying the name of the CC while sending
313 -- access removal notification to CC / Author.
314 --
315 ---------------------------------------------------------------------------*/
316 l_event_t wf_event_t;
317 l_viewer_event VARCHAR2(50);
318 l_seq_val_vw VARCHAR2(100) ;
319 l_parameter_list_t wf_parameter_list_t;
320 l_error_message VARCHAR2(500);
321
322 CURSOR c_viewer_info(cp_viewer_id IN NUMBER) IS
323 SELECT (hz.person_last_name||','||hz.person_first_name) VIEWER , us.user_name
324 FROM hz_parties hz, fnd_user us, igp_ac_accounts ac
325 WHERE ac.party_id = hz.party_id AND
326 us.user_id=ac.user_id AND
327 hz.party_id = cp_viewer_id;
328
329 -- Get the details of CC name.
330 CURSOR c_get_CC_name(cp_CC_user_name VARCHAR2)
331 IS
332 SELECT (hz.person_last_name||','||hz.person_first_name) CC_NAME
333 FROM hz_parties hz,fnd_user fu
334 WHERE fu.person_party_id=hz.party_id
335 AND fu.user_name = cp_CC_user_name;
336
337 rec_viewer_info c_viewer_info%ROWTYPE;
338 l_cc_name hz_parties.party_name%TYPE;
339
340 BEGIN
341
342 l_seq_val_vw := 'IGPVW2CC'||to_char(SYSDATE,'YYYYMMDDHH24MISS');
343 l_viewer_event := 'oracle.apps.igs.igp.vw.inform_cc';
344 --
345 -- initialize the wf_event_t object
346 --
347 wf_event_t.initialize(l_event_t);
348
349 --
350 -- Adding the parameters to the parameter list
351 --
352 OPEN c_viewer_info(p_viewer_id) ;
353 FETCH c_viewer_info INTO rec_viewer_info;
354 CLOSE c_viewer_info;
355
356 OPEN c_get_CC_name(p_CC_user_name);
357 FETCH c_get_CC_name INTO l_cc_name;
358 CLOSE c_get_CC_name;
359
360 wf_event.addparametertolist( p_name => 'P_VIEWER_NAME',
361 p_value => rec_viewer_info.viewer,
362 p_parameterlist => l_parameter_list_t);
363 wf_event.addparametertolist( p_name => 'FROM',
364 p_value => rec_viewer_info.user_name,
365 p_parameterlist => l_parameter_list_t);
366
367 wf_event.addparametertolist( p_name => 'P_VIEWER_ID',
368 p_value => p_viewer_id ,
372 p_value => p_portfolio_ids,
369 p_parameterlist => l_parameter_list_t);
370
371 wf_event.addparametertolist( p_name => 'P_PORTFOLIO_IDS',
373 p_parameterlist => l_parameter_list_t);
374
375 wf_event.addparametertolist( p_name => 'P_ACTION',
376 p_value => 'R',
377 p_parameterlist => l_parameter_list_t);
378
379 wf_event.addparametertolist( p_name => 'P_SOURCE',
380 p_value => 'CC',
381 p_parameterlist => l_parameter_list_t);
382
383 wf_event.addparametertolist( p_name => 'P_CC_NAME', -- nsidana 4/22/2004 : This param added to the WF to display the CC name when the notification for removal is sent to Author / CC.
384 p_value => l_cc_name,
385 p_parameterlist => l_parameter_list_t);
386
387 --Raise the events...
388 wf_event.raise (p_event_name => l_viewer_event,
389 p_event_key => l_seq_val_vw,
390 p_parameters => l_parameter_list_t);
391
392 --
393 -- Deleting the Parameter list after the event is raised
394 --
395 l_parameter_list_t.delete;
396
397 END Raise_Removal_Event_CC;
398
399 PROCEDURE Raise_Inform_Author_Event
400 (
401 p_viewer_id IN varchar2,
402 p_portfolio_ids IN varchar2,
403 p_CC_user_name IN varchar2
404 ) AS
405 /*------------------------------------------------------------------------------------------------------------
406 -- Created by : ssawhney
407 -- Purpose : Get individual Authors of the portfolio from the portfolios's list.
408 -- Get Viewers details.
409 -- Formulate a html message text to be sent to author for all portfolio removals.
410 -- History
411 --
412 -- nsidana 4/21/2004 Added a param to indicate if the workflow was called from
413 -- career center or viewer. This will send different messages
414 -- accordingly. This procedure is called from CC page,
415 -- so setting the WF param P_SOURCE as 'CC'
416 --
417 -- nsidana 4/22/2004 Populating the WF param for displaying the name of the CC while sending
418 -- access removal notification to CC / Author.
419 --
420 --------------------------------------------------------------------------------------------------------------*/
421 l_event_t wf_event_t;
422 l_author_event VARCHAR2(50);
423 l_seq_val_au VARCHAR2(100);
424 l_parameter_list_t wf_parameter_list_t;
425 l_message_text VARCHAR2(32000);
426
427 l_start_pos NUMBER(10);
428 l_end_pos NUMBER(10);
429 l_cur_pos NUMBER(10);
430 l_count NUMBER(10);
431 l_viewer_ids VARCHAR2(32000);
432 l_viewer_id hz_parties.party_id%TYPE;
433 l_portfolio_ids VARCHAR2(32000);
434 l_basic_text VARCHAR2(32000);
435
436 nbsp VARCHAR2(10);
437
438 CURSOR c_port_info(cp_port_ids IN NUMBER) IS
439 SELECT port.portfolio_name, fnd.user_name
440 FROM igp_us_portfolios port, igp_ac_accounts acc, fnd_user fnd
441 WHERE port.account_id = acc.account_id AND
442 fnd.user_id = acc.user_id AND
443 port.portfolio_id = cp_port_ids;
444
445 CURSOR c_viewer_info(cp_viewer_id IN NUMBER) IS
446 SELECT (hz.person_last_name||','||hz.person_first_name) VIEWER , us.user_name,us.email_address
447 FROM hz_parties hz, fnd_user us, igp_ac_accounts ac
448 WHERE ac.party_id = hz.party_id AND
449 us.user_id=ac.user_id AND
450 hz.party_id = cp_viewer_id;
451
452 -- Get the details of CC name.
453 CURSOR c_get_CC_name(cp_CC_user_name VARCHAR2)
454 IS
455 SELECT (hz.person_last_name||','||hz.person_first_name) CC_NAME
456 FROM hz_parties hz,fnd_user fu
457 WHERE fu.person_party_id=hz.party_id
458 AND fu.user_name = cp_CC_user_name;
459
460
461 rec_viewer_info c_viewer_info%ROWTYPE;
462 rec_port_info c_port_info%ROWTYPE;
463 l_stmt varchar2(32000);
464 l_port_id NUMBER(15);
465 l_cc_name hz_parties.party_name%TYPE;
466
467 BEGIN
468
469 l_seq_val_au := 'IGPVW2AU'||to_char(SYSDATE,'YYYYMMDDHH24MISS');
470 l_basic_text :=null;
471 nbsp := fnd_global.local_chr(38) || 'nbsp;';
472 l_author_event := 'oracle.apps.igs.igp.vw.inform_author_rem';
473 --
474 -- initialize the wf_event_t object
475 --
476 wf_event_t.initialize(l_event_t);
477
478 --
479 -- Adding the parameters to the parameter list
480 --
481 -- call the package to make a static message.
482 --
483
484 l_viewer_id := p_viewer_id;
485 OPEN c_viewer_info(l_viewer_id) ;
486 FETCH c_viewer_info INTO rec_viewer_info;
487 CLOSE c_viewer_info;
488
489 OPEN c_get_CC_name(p_CC_user_name);
490 FETCH c_get_CC_name INTO l_cc_name;
491 CLOSE c_get_CC_name;
492
493
494 wf_event.addparametertolist( p_name => 'P_VIEWER_NAME',
495 p_value => rec_viewer_info.viewer,
496 p_parameterlist => l_parameter_list_t);
497 wf_event.addparametertolist( p_name => 'P_VIEWER_EMAIL',
498 p_value => rec_viewer_info.email_address,
499 p_parameterlist => l_parameter_list_t);
500 wf_event.addparametertolist( p_name => 'FROM',
501 p_value => rec_viewer_info.user_name,
502 p_parameterlist => l_parameter_list_t);
503
504
508
505 -- loop for items in the Portfolio List...get individual author and the portfolio name.
506 -- raise multiple BEs, cause we want to show author and portfolio information both
507 -- hence we can not have the ADHOC role logic here.
509 l_portfolio_ids:= p_portfolio_ids;
510
511 l_start_pos := 1;
512 l_end_pos := LENGTH(l_portfolio_ids);
513 l_cur_pos := 1;
514
515 IF (l_portfolio_ids IS NOT NULL) THEN
516 LOOP
517
518 l_cur_pos := INSTR(l_portfolio_ids,',',l_start_pos,1);
519 IF (l_cur_pos = 0) THEN
520 l_port_id := SUBSTR(l_portfolio_ids, l_start_pos, l_end_pos - l_start_pos + 1);
521 ELSE
522 l_port_id := SUBSTR(l_portfolio_ids, l_start_pos, l_cur_pos - l_start_pos);
523 END IF;
524
525 OPEN c_port_info(l_port_id);
526 FETCH c_port_info INTO rec_port_info;
527 CLOSE c_port_info;
528
529 wf_event.addparametertolist( p_name => 'P_PORT_NAME',
530 p_value => rec_port_info.portfolio_name,
531 p_parameterlist => l_parameter_list_t);
532
533 wf_event.addparametertolist( p_name => 'P_AUTHOR_ROLE',
534 p_value => rec_port_info.user_name,
535 p_parameterlist => l_parameter_list_t);
536
537 wf_event.addparametertolist( p_name => 'P_SOURCE', -- nsidana 4/21/2004 : This param P_SOURCE indicates if the Viewer / Career Center removed the Portfolio Access. As this procedure
538 p_value => 'CC', -- is called only from the SS page, I am defaulting this value to 'CC'. The viewer page does not call this procedure. It directly raises the
539 p_parameterlist => l_parameter_list_t); -- BE, so we have set this param there in the Java code. (File : AssignPortfolioSearchAMImpl.java
540
541 wf_event.addparametertolist( p_name => 'P_CC_NAME', -- nsidana 4/22/2004 : This param added to the WF to display the CC name when the notification for removal is sent to Author / CC.
542 p_value => l_cc_name,
543 p_parameterlist => l_parameter_list_t);
544
545 --Raise the event...
546 wf_event.raise ( p_event_name => l_author_event,
547 p_event_key => l_seq_val_au,
548 p_parameters => l_parameter_list_t);
549
550 IF (l_cur_pos = 0) THEN
551 EXIT; -- Exit loop
552 END IF;
553
554 l_start_pos := l_cur_pos + 1;
555 END LOOP;
556 END IF;
557 --
558 -- Deleting the Parameter list after the event is raised
559 --
560 l_parameter_list_t.delete;
561
562
563 END Raise_Inform_Author_Event;
564
565
566
567 PROCEDURE Get_Author_Det (
568 itemtype IN VARCHAR2,
569 itemkey IN VARCHAR2,
570 actid IN NUMBER ,
571 funcmode IN VARCHAR2,
572 resultout OUT NOCOPY VARCHAR2) AS
573 /*--------------------------------------------------------------------------
574 -- Created by : ssawhney
575 -- Purpose : Workflow function call, this would handle the setting of all wf attributes. for the Author BE.
576 --
577 -- History
578 ---------------------------------------------------------------------------*/
579 l_message VARCHAR2(500);
580 l_sysdate DATE ;
581
582 BEGIN
583
584 IF (funcmode = 'RUN') THEN
585 l_sysdate:= SYSDATE+1;
586
587 wf_engine.setitemattrtext(ItemType => itemtype,
588 ItemKey => itemkey,
589 aname => 'P_SYSDATE',
590 avalue => l_sysdate);
591 resultout := 'COMPLETE';
592
593 RETURN;
594 END IF;
595
596 EXCEPTION
597
598 WHEN OTHERS THEN
599 l_message := sqlerrm;
600 wf_core.context('igp_wf_gen_002_pkg','Get_Author_Det',itemtype,itemkey ,l_message);
601 RAISE;
602
603 END Get_Author_Det;
604
605
606 PROCEDURE chk_action (
607 itemtype IN VARCHAR2,
608 itemkey IN VARCHAR2,
609 actid IN NUMBER ,
610 funcmode IN VARCHAR2,
611 resultout OUT NOCOPY VARCHAR2)
612 AS
613
614 l_action VARCHAR2(1);
615
616 BEGIN
617 l_action :=null;
618 l_action :=null;
619 l_action := Wf_Engine.GetItemAttrText(itemtype,itemkey,'P_ACTION');
620 IF (l_action = 'A' ) THEN
621 resultout := 'COMPLETE:A';
622 ELSIF (l_action = 'R' ) THEN
623 resultout := 'COMPLETE:R';
624 END IF;
625 RETURN;
626 EXCEPTION
627 WHEN OTHERS THEN
628 NULL;
629 END chk_action;
630
631 -- this raise the inform CC for invalid Assignments.
632 PROCEDURE Raise_invalid_assign_Event_CC(
633 p_invalid_assignments IN varchar2
634 )
635 AS
636
637 l_event_t wf_event_t;
638 l_viewer_event VARCHAR2(50);
639 l_seq_val_vw VARCHAR2(100);
640 l_parameter_list_t wf_parameter_list_t;
641 l_error_message VARCHAR2(500);
642 BEGIN
643 l_viewer_event := 'oracle.apps.igs.igp.vw.inform_cc';
644 l_seq_val_vw := 'IGPVW2CC'||to_char(SYSDATE,'YYYYMMDDHH24MISS');
645 --
646 -- initialize the wf_event_t object
647 --
648 wf_event_t.initialize(l_event_t);
649
650 wf_event.addparametertolist( p_name => 'P_INVALID_ASSIGN_LIST',
651 p_value => p_invalid_assignments,
652 p_parameterlist => l_parameter_list_t);
653
657
654 wf_event.addparametertolist( p_name => 'P_ACTION',
655 p_value => 'A',
656 p_parameterlist => l_parameter_list_t);
658 --Raise the events...
659 wf_event.raise (p_event_name => l_viewer_event,
660 p_event_key => l_seq_val_vw,
661 p_parameters => l_parameter_list_t);
662
663 --
664 -- Deleting the Parameter list after the event is raised
665 --
666 l_parameter_list_t.delete;
667
668 END Raise_invalid_assign_Event_CC;
669
670 PROCEDURE Write_CC_invalid_assign_Mes
671 (
672 document_id IN VARCHAR2,
673 display_type IN VARCHAR2,
674 document IN OUT NOCOPY CLOB,
675 document_type IN OUT NOCOPY VARCHAR2
676 ) AS
677 /*--------------------------------------------------------------------------
678 -- Created by : nsidana
679 -- Purpose : Proc written in a specific format to handle CLOB item attrib loading
680 --
681 -- History
682 ---------------------------------------------------------------------------*/
683 l_item_type VARCHAR2(300);
684 l_item_key VARCHAR2(300);
685 l_item VARCHAR2(32000);
686 l_message VARCHAR2(32000);
687 l_str VARCHAR2(32000);
688
689 -- Start Local Procedure..
690
691 PROCEDURE build_invalid_assign_message
692 ( p_invalid_list IN VARCHAR2,
693 p_message_text OUT NOCOPY VARCHAR2
694 )
695 IS
696 -- Get the details of portfolio.
697 CURSOR c_get_port_name(cp_port_id VARCHAR2) IS
698 SELECT portfolio_name
699 FROM igp_us_portfolios
700 WHERE portfolio_id=to_number(cp_port_id);
701 -- Get the name of the viewer.
702 CURSOR c_get_viewer_name(cp_party_id VARCHAR2) IS
703 SELECT person_last_name||', '||person_first_name
704 FROM hz_parties
705 WHERE party_id=to_number(cp_party_id);
706
707 l_str varchar2(2000);
708 l_length number;
709 l_substr varchar2(200);
710 l_pos number;
711 l_viewer varchar2(200);
712 l_port varchar2(200);
713 l_line varchar2(2000);
714 l_message_text varchar2(32000);
715 l_port_name varchar2(50);
716 l_v_name varchar2(50);
717 l_err varchar2(2000);
718 BEGIN
719 l_str:=p_invalid_list;
720 l_message_text :='';
721 LOOP
722 l_length := length(l_str);
723 l_pos:=0;
724 l_pos := INSTR(l_str,'#',1);
725
726 IF (l_pos = 0) THEN
727 EXIT;
728 END IF;
729
730 l_substr := substr(l_str,1,l_pos-1);
731 l_port :=substr(l_substr,1,(instr(l_substr,',',1)-1));
732 l_viewer :=substr(l_substr,instr(l_substr,',',1)+1);
733
734 OPEN c_get_viewer_name(l_viewer);
735 FETCH c_get_viewer_name INTO l_v_name;
736 CLOSE c_get_viewer_name;
737
738 OPEN c_get_port_name(l_port);
739 FETCH c_get_port_name INTO l_port_name;
740 CLOSE c_get_port_name;
741
742 l_line:=null;
743 l_line:='<tr><td align=center> '||l_v_name ||'</td><td align=center>'||l_port_name||'</td></tr>';
744 l_message_text:=l_message_text||l_line;
745 l_str := substr(l_str,l_pos+1,length(l_str));
746 END LOOP;
747
748 l_port :=substr(l_str,1,(instr(l_str,',',1)-1));
749 l_viewer :=substr(l_str,instr(l_str,',',1)+1);
750
751 OPEN c_get_viewer_name(l_viewer);
752 FETCH c_get_viewer_name INTO l_v_name;
753 CLOSE c_get_viewer_name;
754
755 OPEN c_get_port_name(l_port);
756 FETCH c_get_port_name INTO l_port_name;
757 CLOSE c_get_port_name;
758 l_line:=null;
759 l_line:='<tr><td align=center> '||l_v_name ||'</td><td align=center>'||l_port_name||'</td></tr>';
760 l_message_text:=l_message_text||l_line;
761
762 p_message_text := l_message_text;
763 EXCEPTION
764 WHEN OTHERS THEN
765 l_err :=sqlerrm;
766 wf_core.context('igp_wf_gen_002_pkg','Write_CC_invalid_assign_Mes',l_item_type,l_item_key ,l_err);
767
768
769 END build_invalid_assign_message;
770 -- End Local Procedure..
771 BEGIN
772 IF document_id IS NOT NULL THEN
773 --Fetch the item Type, Item Key and Item Name from the passed Document ID.
774 l_item_type := substr(document_id, 1, instr(document_id,':')-1);
775 l_item_key := substr (document_id, INSTR(document_id, ':') +1, (INSTR(document_id, '*') - INSTR(document_id, ':'))-1) ;
776 l_item := substr(document_id, instr(document_id,'*')+1);
777 l_message := NULL;
778 IF l_item IS NOT NULL THEN
779 --
780 -- If the Item Name is not null then get the value of the invalid assignments to form the message again.
781 --
782 l_str := wf_engine.GetItemAttrText( itemtype => l_item_type, itemkey => l_item_key, aname => 'P_INVALID_ASSIGN_LIST');
783 END IF;
784 END IF;
785 -- call local procedure to generate the HTML message.
786 build_invalid_assign_message(l_str,l_message);
787 -- Write the header doc into CLOB variable
788 WF_NOTIFICATION.WriteToClob(document, l_message);
789 EXCEPTION
790 WHEN OTHERS THEN
791 l_message := sqlerrm;
792 wf_core.context('igp_wf_gen_002_pkg','Write_CC_invalid_assign_Mes',l_item_type,l_item_key ,l_message);
793 RAISE;
794
795 END Write_CC_invalid_assign_Mes;
796
797 PROCEDURE Get_invalid_assign_Det (
798 itemtype IN VARCHAR2,
799 itemkey IN VARCHAR2,
800 actid IN NUMBER ,
804 -- Created by : nsidana
801 funcmode IN VARCHAR2,
802 resultout OUT NOCOPY VARCHAR2) AS
803 /*--------------------------------------------------------------------------
805 -- Purpose : Workflow function call, this would handle the setting of all wf attributes.
806 --
807 -- History
808 ---------------------------------------------------------------------------*/
809 l_error_message VARCHAR2(500);
810 l_sysdate DATE;
811
812 BEGIN
813
814 IF (funcmode = 'RUN') THEN
815 l_sysdate := SYSDATE;
816 Create_CC_Role ( itemtype => itemtype,
817 itemkey => itemkey );
818
819
820 -- standard way to call PLSQLCLOB. Dont modify.
821 wf_engine.setitemattrtext(ItemType => itemtype,
822 ItemKey => itemkey,
823 aname => 'P_MESSAGE_TEXT',
824 avalue => 'PLSQLCLOB:igp_vw_gen_002_pkg.Write_CC_invalid_assign_Mes/'||itemtype||':'||itemkey||'*P_MESSAGE_TEXT');
825 resultout := 'COMPLETE';
826
827 RETURN;
828 END IF;
829
830 EXCEPTION
831 -- trap exceptions while setting workflow attribs..
832 WHEN OTHERS THEN
833 l_error_message := sqlerrm;
834 wf_core.context('igp_wf_gen_002_pkg','Get_Viewer_Det',itemtype,itemkey ,l_error_message);
835 RAISE;
836
837 END Get_invalid_assign_Det;
838
839 PROCEDURE CHK_SOURCE (itemtype IN VARCHAR2,
840 itemkey IN VARCHAR2,
841 actid IN NUMBER ,
842 funcmode IN VARCHAR2,
843 resultout OUT NOCOPY VARCHAR2)
844 AS
845 l_source VARCHAR2(2); -- This will just hold either the value CC or VW.
846 l_error_message VARCHAR2(5000);
847
848 BEGIN
849 IF (funcmode = 'RUN') THEN
850 l_source := null;
851 l_source := wf_engine.GetItemAttrText( itemtype => itemtype,itemkey => itemkey,aname => 'P_SOURCE');
852 IF (l_source = 'CC') THEN
853 resultout := 'COMPLETE:CC'; -- Path 1 : Career Center removed the access.
854 ELSIF (l_source = 'VW') THEN
855 resultout := 'COMPLETE:VW'; -- Path 2 : Viewer removed the access.
856 END IF;
857 END IF;
858 EXCEPTION
859 WHEN OTHERS THEN
860 l_error_message := sqlerrm;
861 wf_core.context('igp_wf_gen_002_pkg','chk_source',itemtype,itemkey ,l_error_message);
862 RAISE;
863 END CHK_SOURCE;
864
865 END IGP_VW_GEN_002_PKG;
[Home] [Help]