[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_WF_EVENT_PKG
Source
1 PACKAGE BODY IGS_PS_WF_EVENT_PKG AS
2 /* $Header: IGSPS82B.pls 120.2 2006/01/09 06:32:48 sommukhe ship $ */
3
4 ------------------------------------------------------------------------------------
5 --Created by : smanglm ( Oracle IDC)
6 --Date created: 19-JUL-2001
7 --
8 --Purpose: Created as part of the build for DLD Unit Section Enrollment Information
9 -- This package deals with raising of Business Event. This package has the
10 -- following procedure:
11 -- i) wf_create_event - Raises the event
12 --
13 --
14 --Known limitations/enhancements and/or remarks:
15 --
16 --Change History:
17 --Who When What
18 --jbegum 26-Apr-2003 For Enh Bug# 2833850
19 -- Added columns preferred_region_code and no_set_day_ind to
20 -- the the call to igs_ps_usec_occurs_pkg.update_row
21 -------------------------------------------------------------------------------------
22
23 PROCEDURE wf_create_event(
24 p_uoo_id IN NUMBER,
25 p_usec_occur_id IN NUMBER DEFAULT NULL,
26 p_event_type IN VARCHAR2,
27 p_message OUT NOCOPY VARCHAR2
28 )
29 IS
30 l_wf_event_t WF_EVENT_T;
31 l_wf_parameter_list_t WF_PARAMETER_LIST_T;
32
33 --
34 -- cursor to fetch the usec_occur_id
35 --
36 CURSOR c_usec_occur_id (cp_uoo_id igs_ps_usec_occurs.uoo_id%TYPE) IS
37 SELECT unit_section_occurrence_id
38 FROM igs_ps_usec_occurs
39 WHERE uoo_id = cp_uoo_id;
40
41 --
42 -- procedure to define_event
43 --
44 PROCEDURE raise_event (p_event_name IN VARCHAR2,
45 p_event_key IN VARCHAR2,
46 p_event_type IN VARCHAR2,
47 p_uoo_id IN NUMBER,
48 p_usec_occur_id IN NUMBER)
49 ------------------------------------------------------------------------------------
50 --Created by : smanglm ( Oracle IDC)
51 --Date created: 19-JUL-2001
52 --
53 --Purpose: local procedure to raise_event
54 -- local to wf_create_event
55 --
56 --
57 --Known limitations/enhancements and/or remarks:
58 --
59 --Change History:
60 --Who When What
61 -------------------------------------------------------------------------------------
62 IS
63 l_key NUMBER;
64 BEGIN
65 --
66 -- initialize the wf_event_t object
67 --
68 WF_EVENT_T.Initialize(l_wf_event_t);
69 --
70 -- set the event name
71 --
72 l_wf_event_t.setEventName( pEventName => p_event_name);
73 --
74 -- set the event key but before the select a number from sequenec
75 --
76 SELECT IGS_PS_USEC_WF_ITEM_KEY_S.NEXTVAL INTO l_key FROM dual;
77 l_wf_event_t.setEventKey ( pEventKey => p_event_key||l_key );
78 --
79 -- set the parameter list
80 --
81 l_wf_event_t.setParameterList ( pParameterList => l_wf_parameter_list_t );
82 --
83 -- now add the parameters to the parameter list
84 --
85 l_wf_event_t.AddParameterToList ( pName => 'ORG_ID', pValue => FND_PROFILE.VALUE('ORG_ID'));
86 l_wf_event_t.AddParameterToList ( pName => 'USER_ID', pValue => FND_PROFILE.VALUE('USER_ID'));
87 l_wf_event_t.AddParameterToList ( pName => 'RESP_ID', pValue => FND_PROFILE.VALUE('RESP_ID'));
88 l_wf_event_t.AddParameterToList ( pName => 'RESP_APPL_ID', pValue => FND_PROFILE.VALUE('RESP_APPL_ID'));
89 l_wf_event_t.AddParameterToList ( pName => 'UOO_ID', pValue => p_uoo_id);
90 l_wf_event_t.AddParameterToList ( pName => 'USEC_OCCUR_ID', pValue => p_usec_occur_id);
91 l_wf_event_t.AddParameterToList ( pName => 'EVENT_TYPE', pValue => p_event_type);
92
93 --
94 -- raise the event
95 --
96 WF_EVENT.RAISE (p_event_name => p_event_name,
97 p_event_key => p_event_key||l_key,
98 p_event_data => NULL,
99 p_parameters => l_wf_parameter_list_t);
100 END raise_event;
101
102 --
103 -- procedure to update igs_ps_usec_occurs
104 --
105 PROCEDURE update_usec_occurs (p_usec_occur_id IN NUMBER)
106 ------------------------------------------------------------------------------------
107 --Created by : smanglm ( Oracle IDC)
108 --Date created: 19-JUL-2001
109 --
110 --Purpose: local procedure update_usec_occurs for updating notify_status
111 -- local to wf_create_event
112 --
113 --
114 --Known limitations/enhancements and/or remarks:
115 --
116 --Change History:
117 --Who When What
118 --smvk 25-jun-2003 Enh bug#2918094. Added column cancel_flag.
119 -------------------------------------------------------------------------------------
120
121 IS
122 --
123 -- cursor to get the usec details
124 --
125 CURSOR c_usec_details (cp_usec_occur_id igs_ps_usec_occurs.unit_section_occurrence_id%TYPE) IS
126 SELECT *
127 FROM igs_ps_usec_occurs
128 WHERE unit_section_occurrence_id = cp_usec_occur_id;
129
130 l_cst_complete CONSTANT VARCHAR2(10) DEFAULT 'COMPLETE';
131 BEGIN
132 --
133 -- open the cursor and call update row of
134 --
135 FOR rec_usec_details IN c_usec_details (p_usec_occur_id)
136 LOOP
137 igs_ps_usec_occurs_pkg.update_row
138 (
139 X_ROWID => rec_usec_details.ROW_ID,
140 X_UNIT_SECTION_OCCURRENCE_ID => rec_usec_details.UNIT_SECTION_OCCURRENCE_ID,
141 X_UOO_ID => rec_usec_details.UOO_ID,
142 X_MONDAY => rec_usec_details.MONDAY,
143 X_TUESDAY => rec_usec_details.TUESDAY,
144 X_WEDNESDAY => rec_usec_details.WEDNESDAY,
145 X_THURSDAY => rec_usec_details.THURSDAY,
146 X_FRIDAY => rec_usec_details.FRIDAY,
147 X_SATURDAY => rec_usec_details.SATURDAY,
148 X_SUNDAY => rec_usec_details.SUNDAY,
149 X_START_TIME => rec_usec_details.START_TIME,
150 X_END_TIME => rec_usec_details.END_TIME,
151 X_BUILDING_CODE => rec_usec_details.BUILDING_CODE,
152 X_ROOM_CODE => rec_usec_details.ROOM_CODE,
153 X_SCHEDULE_STATUS => rec_usec_details.SCHEDULE_STATUS,
154 X_STATUS_LAST_UPDATED => rec_usec_details.STATUS_LAST_UPDATED,
155 X_INSTRUCTOR_ID => rec_usec_details.INSTRUCTOR_ID,
156 X_ATTRIBUTE_CATEGORY => rec_usec_details.ATTRIBUTE_CATEGORY,
157 X_ATTRIBUTE1 => rec_usec_details.ATTRIBUTE1,
158 X_ATTRIBUTE2 => rec_usec_details.ATTRIBUTE2,
159 X_ATTRIBUTE3 => rec_usec_details.ATTRIBUTE3,
160 X_ATTRIBUTE4 => rec_usec_details.ATTRIBUTE4,
161 X_ATTRIBUTE5 => rec_usec_details.ATTRIBUTE5,
162 X_ATTRIBUTE6 => rec_usec_details.ATTRIBUTE6,
163 X_ATTRIBUTE7 => rec_usec_details.ATTRIBUTE7,
164 X_ATTRIBUTE8 => rec_usec_details.ATTRIBUTE8,
165 X_ATTRIBUTE9 => rec_usec_details.ATTRIBUTE9,
166 X_ATTRIBUTE10 => rec_usec_details.ATTRIBUTE10,
167 X_ATTRIBUTE11 => rec_usec_details.ATTRIBUTE11,
168 X_ATTRIBUTE12 => rec_usec_details.ATTRIBUTE12,
169 X_ATTRIBUTE13 => rec_usec_details.ATTRIBUTE13,
170 X_ATTRIBUTE14 => rec_usec_details.ATTRIBUTE14,
171 X_ATTRIBUTE15 => rec_usec_details.ATTRIBUTE15,
172 X_ATTRIBUTE16 => rec_usec_details.ATTRIBUTE16,
173 X_ATTRIBUTE17 => rec_usec_details.ATTRIBUTE17,
174 X_ATTRIBUTE18 => rec_usec_details.ATTRIBUTE18,
175 X_ATTRIBUTE19 => rec_usec_details.ATTRIBUTE19,
176 X_ATTRIBUTE20 => rec_usec_details.ATTRIBUTE20,
177 X_ERROR_TEXT => rec_usec_details.ERROR_TEXT,
178 X_MODE => 'R',
179 X_START_DATE => rec_usec_details.START_DATE,
180 X_END_DATE => rec_usec_details.END_DATE,
181 X_TO_BE_ANNOUNCED => rec_usec_details.TO_BE_ANNOUNCED,
182 X_INST_NOTIFY_IND => rec_usec_details.INST_NOTIFY_IND,
183 X_NOTIFY_STATUS => l_cst_complete,
184 X_DEDICATED_BUILDING_CODE => rec_usec_details.DEDICATED_BUILDING_CODE,
185 X_DEDICATED_ROOM_CODE => rec_usec_details.DEDICATED_ROOM_CODE,
186 X_PREFERRED_BUILDING_CODE => rec_usec_details.PREFERRED_BUILDING_CODE,
187 X_PREFERRED_ROOM_CODE => rec_usec_details.PREFERRED_ROOM_CODE,
188 X_PREFERRED_REGION_CODE => rec_usec_details.PREFERRED_REGION_CODE,
189 X_NO_SET_DAY_IND => rec_usec_details.NO_SET_DAY_IND,
190 X_cancel_flag => rec_usec_details.cancel_flag,
191 x_occurrence_identifier => rec_usec_details.occurrence_identifier,
192 x_abort_flag => rec_usec_details.abort_flag
193
194 );
195 --
196 -- now nullify the column in the shadow table igs_ps_sh_usec_occurs
197 -- direct update statement is used as there is no TBH for shadow tables
198 --
199 UPDATE igs_ps_sh_usec_occurs SET
200 monday = NULL,
201 tuesday = NULL,
202 wednesday = NULL,
203 thursday = NULL,
204 friday = NULL,
205 saturday = NULL,
206 sunday = NULL,
207 room_code = NULL,
208 building_code = NULL,
209 start_time = NULL,
210 end_time = NULL,
211 instructor_id = NULL
212 WHERE unit_section_occurrence_id = p_usec_occur_id;
213
214 END LOOP;
215 END update_usec_occurs;
216
217 --
218 -- main begin
219 --
220 BEGIN
221 --
222 -- check for the parameters, if both p_uoo_id and p_usec_occur_id are null, return
223 -- with error message
224 --
225 IF p_uoo_id IS NULL AND p_usec_occur_id IS NULL THEN
226 p_message := 'IGS_GE_NOT_ENGH_PARAM';
227 RETURN;
228 END IF;
229
230 --
231 -- check for the value of p_event_type
232 -- if it is MOD, the proc is called from IGSPS084 and there has been some
233 -- change in the uoo_id details, the event to be raised in this case is
234 -- oracle.apps.igs.ps.wfus_md
235 -- if it is CNCL, the proc is called from the backend when the unit section
236 -- status has been changed to CANCELLED. In this case the event raised will
237 -- be oracle.apps.igs.ps.wfus_cn
238 --
239
240 --
241 -- check if p_usec_occur_id is null,
242 -- raise the event for all the usec_occur_id available for the passed uoo_id
243 --
244 IF p_usec_occur_id IS NULL THEN
245 --
246 -- fetch all the usec_occur_id for the passed uoo_id
247 --
248 FOR rec_usec_occur_id IN c_usec_occur_id (p_uoo_id)
249 LOOP
250 IF p_event_type = 'MOD' THEN
251 --
252 -- raise oracle.apps.igs.ps.wfus_md
253 --
254 raise_event (p_event_name => 'oracle.apps.igs.ps.wfus_md',
255 p_event_key => 'wfus_md',
256 p_event_type => 'MOD',
257 p_uoo_id => p_uoo_id,
258 p_usec_occur_id => rec_usec_occur_id.unit_section_occurrence_id);
259 ELSIF p_event_type = 'CNCL' THEN
260 --
261 -- raise oracle.apps.igs.ps.wfus_cn
262 --
263 raise_event (p_event_name => 'oracle.apps.igs.ps.wfus_cn',
264 p_event_key => 'wfus_cn',
265 p_event_type => 'CNCL',
266 p_uoo_id => p_uoo_id,
267 p_usec_occur_id => rec_usec_occur_id.unit_section_occurrence_id);
268 END IF;
269 --
270 -- on successful raising of events update notify status to COMPLETE
271 -- in igs_ps_usec_occurs and change field values to null in the shadow
272 -- table
273 --
274 update_usec_occurs (p_usec_occur_id => rec_usec_occur_id.unit_section_occurrence_id);
275 END LOOP;
276 ELSE
277 IF p_event_type = 'MOD' THEN
278 --
279 -- raise oracle.apps.igs.ps.wfus_md
280 --
281 raise_event (p_event_name => 'oracle.apps.igs.ps.wfus_md',
282 p_event_key => 'wfus_md',
283 p_event_type => 'MOD',
284 p_uoo_id => p_uoo_id,
285 p_usec_occur_id => p_usec_occur_id);
286 ELSIF p_event_type = 'CNCL' THEN
287 --
288 -- raise oracle.apps.igs.ps.wfus_cn
289 --
290 raise_event (p_event_name => 'oracle.apps.igs.ps.wfus_cn',
291 p_event_key => 'wfus_cn',
292 p_event_type => 'CNCL',
293 p_uoo_id => p_uoo_id,
294 p_usec_occur_id => p_usec_occur_id);
295 END IF;
296 --
297 -- on successful raising of events update notify status to COMPLETE
298 -- in igs_ps_usec_occurs and change field values to null in the shadow
299 -- table
300 --
301 update_usec_occurs (p_usec_occur_id => p_usec_occur_id);
302 END IF; -- end of check for p_usec_occur_id
303 END wf_create_event;
304
305 PROCEDURE fac_exceed_wl_event(errbuf OUT NOCOPY VARCHAR2,
306 retcode OUT NOCOPY NUMBER,
307 p_c_cal_inst IN VARCHAR2)
308 ------------------------------------------------------------------------------------
309 --Created by : jdeekoll ( Oracle IDC)
310 --Date created: 06-May-2003
311 --
312 --Purpose: HR Integration build(# 2833853)
313 --
314 --Known limitations/enhancements and/or remarks:
315 --
316 --Change History:
317 --Who When What
318 --sommukhe 9-JAN-2006 Bug# 4869737,included call to igs_ge_gen_003.set_org_id.
319 -------------------------------------------------------------------------------------
320 AS
321 l_n_key NUMBER;
322 l_wf_event_t WF_EVENT_T;
323 l_wf_parameter_list_t WF_PARAMETER_LIST_T;
324
325 l_c_user_name fnd_user.user_name%TYPE:=fnd_global.user_name;
326 l_c_cal_type igs_ca_inst.cal_type%TYPE;
327 l_n_cal_seq_num igs_ca_inst.sequence_number%TYPE;
328
329 /* Cursor to find the setup in the Employment Category*/
330
331 CURSOR c_emp_cat_setup IS
332 SELECT 'x' FROM igs_ps_emp_cats_wl
333 WHERE rownum = 1;
334
335 /* Cursor for Sequence */
336
337 CURSOR c_seq IS
338 SELECT IGS_PS_EXCEED_FAC_WL_S.NEXTVAL
339 FROM DUAL;
340
341 l_c_emp_cat_setup VARCHAR2(1);
342
343 BEGIN
344
345 igs_ge_gen_003.set_org_id (NULL);
346 -- Set the default status as success
347 retcode := 0;
348
349 /* Workload setup done or not */
350
351 OPEN c_emp_cat_setup;
352 FETCH c_emp_cat_setup INTO l_c_emp_cat_setup;
353 IF c_emp_cat_setup%NOTFOUND THEN
354 fnd_message.set_name('IGS','IGS_PS_NO_EMP_CAT_SETUP');
355 fnd_file.put_line(fnd_file.log,fnd_message.get);
356 close c_emp_cat_setup;
357 RETURN;
358 END IF;
359 close c_emp_cat_setup;
360
361 -- Get the calendar sequence number and calendar type
362
363 l_c_cal_type := RTRIM(SUBSTR(p_c_cal_inst,1,10));
364 l_n_cal_seq_num := TO_NUMBER(RTRIM(SUBSTR(p_c_cal_inst,14,19)));
365
366 -- initialize the wf_event_t object
367 --
368 WF_EVENT_T.Initialize(l_wf_event_t);
369 --
370 -- set the event name
371 --
372 l_wf_event_t.setEventName( pEventName => 'oracle.apps.igs.ps.exceed.fac_workload');
373 --
374 -- event key to identify uniquely
375 --
376 OPEN c_seq;
377 FETCH c_seq INTO l_n_key;
378 CLOSE c_seq;
379 --
380 -- set the parameter list
381 --
382 l_wf_event_t.setParameterList ( pParameterList => l_wf_parameter_list_t );
383 --
384 -- now add the parameters to the parameter list
385
386 wf_event.AddParameterToList ( p_name => 'IA_USER', p_value =>l_c_user_name , p_parameterlist => l_wf_parameter_list_t);
387 wf_event.AddParameterToList ( p_name => 'IA_CAL_TYPE', p_value =>l_c_cal_type, p_parameterlist => l_wf_parameter_list_t);
388 wf_event.AddParameterToList ( p_name => 'IA_CAL_SEQ_NUM', p_value =>l_n_cal_seq_num, p_parameterlist => l_wf_parameter_list_t);
389 --
390 -- raise the event
391
392 wf_event.raise (
393 p_event_name => 'oracle.apps.igs.ps.fac_workload.exceed',
394 p_event_key => 'FACEXCEEDWL'||l_n_key,
395 p_parameters => l_wf_parameter_list_t
396 );
397 EXCEPTION
398 WHEN OTHERS THEN
399 ROLLBACK;
400 retcode:=2;
401 fnd_file.put_line(fnd_file.log,sqlerrm);
402 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION') ;
403 igs_ge_msg_stack.conc_exception_hndl;
404 END fac_exceed_wl_event;
405
406 PROCEDURE generate_faculty_list(itemtype in varchar2,
407 itemkey in varchar2,
408 actid in number,
409 funcmode in varchar2,
410 resultout out NOCOPY varchar2
411 )
412 ------------------------------------------------------------------------------------
413 --Created by : jdeekoll ( Oracle IDC)
414 --Date created: 06-May-2003
415 --
416 --Purpose: HR Integration build(# 2833853)
417 --
418 --Known limitations/enhancements and/or remarks:
419 --
420 --Change History:
421 --Who When What
422 -------------------------------------------------------------------------------------
423 AS
424 BEGIN
425
426 IF (funcmode = 'RUN') THEN
427
428 wf_engine.SetItemAttrText(itemtype => itemtype,
429 itemkey => itemkey,
430 aname => 'IA_FAC_HEADER',
431 avalue => 'PLSQLCLOB:igs_ps_wf_event_pkg.generate_faculty_header/'|| itemtype || ':' || itemkey);
432
433 wf_engine.SetItemAttrText(itemtype => itemtype,
434 itemkey => itemkey,
435 aname => 'IA_FAC_BODY',
436 avalue => 'PLSQLCLOB:igs_ps_wf_event_pkg.generate_faculty_body/'|| itemtype || ':' || itemkey);
437
438
439 Resultout:= 'COMPLETE:';
440 RETURN;
441 END IF;
442
443 END generate_faculty_list;
444
445 PROCEDURE generate_faculty_header(document_id in varchar2,
446 display_type in Varchar2,
447 document in out NOCOPY clob,
448 document_type in out NOCOPY varchar2
449 )
450 ------------------------------------------------------------------------------------
451 --Created by : jdeekoll ( Oracle IDC)
452 --Date created: 06-May-2003
453 --
454 --Purpose: HR Integration build(# 2833853) - Header for faculty list
455 --
456 --Known limitations/enhancements and/or remarks:
457 --
458 --Change History:
459 --Who When What
460 -------------------------------------------------------------------------------------
461 AS
462
463 l_c_document VARCHAR2(400);
464
465 BEGIN
466
467 /* Header in HTML format */
468
469 l_c_document := '<table BORDER COLS=3 WIDTH="100%"><tr>'||'<th width=80%>Name</th>'||'<th width=10%>Expected/Override Workload</th>'||'<th width=10%>Actual Workload</th></tr>';
470
471 /* Write the header doc into CLOB variable */
472
473 WF_NOTIFICATION.WriteToClob(document, l_c_document);
474
475 END generate_faculty_header;
476
477 PROCEDURE generate_faculty_body(document_id in varchar2,
478 display_type in Varchar2,
479 document in out NOCOPY clob,
480 document_type in out NOCOPY varchar2
481 )
482 ------------------------------------------------------------------------------------
483 --Created by : jdeekoll ( Oracle IDC)
484 --Date created: 06-May-2003
485 --
486 --Purpose: HR Integration build(# 2833853)
487 --
488 --Known limitations/enhancements and/or remarks:
489 --
490 --Change History:
491 --Who When What
492 -------------------------------------------------------------------------------------
493 AS
494 l_c_document VARCHAR2(32000);
495 l_c_itemtype VARCHAR2(100);
496 l_c_itemkey WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE;
497 l_c_cal_type igs_ca_inst.cal_type%TYPE;
498 l_n_cal_seq_num igs_ca_inst.sequence_number%TYPE;
499 l_n_tot_fac_wl NUMBER:=0;
500 l_n_exp_wl NUMBER(10,2):=0;
501 l_n_cntr NUMBER(5):=0;
502
503 /* Cursor to get the list of faculty/Staff */
504
505 /* Due to performance issues with igs_pe_typ_instances view, broke the view in 2 different views as shown below */
506
507 CURSOR c_igs_person IS
508 SELECT pti.person_id
509 FROM igs_pe_typ_instances_all pti, igs_pe_person_types pt
510 WHERE NVL (pti.org_id, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
511 = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
512 AND pt.person_type_code = pti.person_type_code
513 AND pt.system_type IN ('STAFF','FACULTY')
514 AND SYSDATE BETWEEN pti.start_date AND NVL(pti.end_date,SYSDATE);
515
516 CURSOR c_hr_person IS
517 SELECT peo.party_id
518 FROM per_person_type_usages_f usg,per_people_f peo,
519 igs_pe_per_type_map map
520 WHERE usg.person_id = peo.person_id AND
521 usg.person_type_id = map.per_person_type_id AND
522 SYSDATE BETWEEN usg.effective_start_date and usg.effective_end_date AND
523 TRUNC(SYSDATE) BETWEEN peo.effective_start_date AND peo.effective_end_date;
524
525 l_n_person_id hz_parties.party_id%TYPE;
526
527 PROCEDURE print_fac_list(p_n_person_id hz_parties.party_id%TYPE,p_c_cal_type igs_ca_inst.cal_type%TYPE,p_n_cal_seq_num igs_ca_inst.sequence_number%TYPE) AS
528
529 /* Cursor to get the person name */
530
531 CURSOR c_person_name(cp_n_person_id hz_parties.party_id%TYPE) IS
532 SELECT hz.party_name
533 FROM hz_parties hz
534 WHERE party_id = cp_n_person_id AND
535 hz.status = 'A';
536
537 l_c_person_name hz_parties.party_name%TYPE;
538
539 BEGIN
540
541 /* Getting Person name */
542
543 OPEN c_person_name(p_n_person_id);
544 FETCH c_person_name INTO l_c_person_name;
545 CLOSE c_person_name;
546
547 /* Check for Faculty workload exceeded expected workload */
548
549 IF igs_ps_gen_001.fac_exceed_exp_wl(
550 p_c_cal_type,
551 p_n_cal_seq_num,
552 p_n_person_id,
553 0,
554 l_n_tot_fac_wl,
555 l_n_exp_wl
556 )THEN
557
558 IF l_n_exp_wl = 0 THEN
559 fnd_message.set_name('IGS', 'IGS_PS_NO_SETUP_FAC_EXCEED');
560 l_c_document := l_c_document||'<TR><TD>'||l_c_person_name ||'</TD><TD colspan=2>'||fnd_message.get||'</TD></TR>';
561 ELSE
562 l_c_document := l_c_document||'<TR><TD>'||l_c_person_name||'</TD><TD>'|| l_n_exp_wl ||'</TD><TD>'|| l_n_tot_fac_wl ||'</TD></TR>';
563 END IF;
564 l_n_cntr := l_n_cntr + 1;
565 END IF;
566
567 IF l_n_cntr = 100 THEN
568 WF_NOTIFICATION.WriteToClob(document, l_c_document);
569 l_c_document := null;
570 l_n_cntr := 1;
571 END IF;
572
573
574 END print_fac_list;
575
576 BEGIN
577
578 /* Get item type and item key */
579
580 l_c_itemtype := SUBSTR(document_id, 1, instr(document_id, ':') - 1);
581 l_c_itemkey := SUBSTR(document_id, instr(document_id, ':') + 1, length(document_id));
582
583 /* Get the cal type and sequence number from attributes i.e. being passed from concurrent job */
584
585 l_c_cal_type := wf_engine.GetItemAttrText (itemtype => l_c_itemtype,
586 itemkey => l_c_itemkey,
587 aname => 'IA_CAL_TYPE'
588 );
589 l_n_cal_seq_num := wf_engine.GetItemAttrNumber (itemtype => l_c_itemtype,
590 itemkey => l_c_itemkey,
591 aname => 'IA_CAL_SEQ_NUM'
592 );
593
594
595
596 /* Process HR cursor */
597
598 l_n_person_id := NULL;
599
600 OPEN c_hr_person;
601 LOOP
602 FETCH c_hr_person INTO l_n_person_id;
603 EXIT WHEN c_hr_person%NOTFOUND;
604 print_fac_list(l_n_person_id,l_c_cal_type,l_n_cal_seq_num);
605 END LOOP;
606 CLOSE c_hr_person;
607
608 /* Process OSS cursor, that are not present in HR */
609
610 OPEN c_igs_person;
611 LOOP
612 FETCH c_igs_person INTO l_n_person_id;
613 EXIT WHEN c_igs_person%NOTFOUND;
614 print_fac_list(l_n_person_id,l_c_cal_type,l_n_cal_seq_num);
615 END LOOP;
616 CLOSE c_igs_person;
617
618 /* If no records exist, then print No data found message */
619
620 IF l_n_cntr = 0 THEN
621 fnd_message.set_name('IGS','IGS_GE_NO_DATA_FOUND');
622 l_c_document := '<TR><TD>'||fnd_message.get||'</TD></TR>';
623 END IF;
624
625 l_c_document := l_c_document||'</table>';
626 WF_NOTIFICATION.WriteToClob(document, l_c_document);
627
628 END generate_faculty_body;
629
630 END IGS_PS_WF_EVENT_PKG;