[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_WF_GEN
Source
1 PACKAGE BODY IGS_PE_WF_GEN AS
2 /* $Header: IGSPE07B.pls 120.9 2006/05/26 05:39:45 vskumar ship $ */
3
4 /******************************************************************
5 Created By : Vinay Chappidi
6 Date Created By : 20-Sep-2001
7 Purpose : Workflow General package for Person Module
8 remarks :
9 Change History
10 Who When What
11 sarakshi 23-Jan-2006 Bug#4938278, created TYPE t_addr_chg_persons and three procedures process_addr_sync,write_addr_sync_message and addr_bulk_synchronization
12 nsidana 9/9/2003 New functions added as part of SWSCR01-02-04
13 address_create,address_update,primary_address_ind_update
14 old function change_address stubbed.
15 asbala 01-09-2003 Reference: Build SWCR01,02,04
16 Business Event triggered
17 gmaheswa 3-Nov-2004 Created a procedure change_housing_status for raising an event in case of insert/update of housing status
18 pkpatel 9-Nov-2004 Bug 3993967 (Modified signature of procedure CHANGE_RESIDENCE. Modified process_residency as per new
19 Notification message. Stubbed the procedure get_res_details.)
20 pkpatel 19=Sep-2005 Bug 4618459 (Removed the reference of HZ_PARAM_TAB. Commented the procedure get_address_dtls.
21 stubbed the procedures address_update and primary_address_ind_update)
22 pkpatel 21-Feb-2006 Bug 4938278 (Added the cursor in the create_address to retrieve the Dates from igs_pe_hz_pty_sites)
23 vskumar 24-May-2006 Bug 5211157 Added two procdeures specs raise_acad_intent_event and process_acad_intent
24 ******************************************************************/
25
26
27 PROCEDURE change_residence( p_resident_details_id IN NUMBER,
28 p_old_res_status IN VARCHAR2,
29 p_old_evaluator IN VARCHAR2,
30 p_old_evaluation_date IN VARCHAR2,
31 p_old_comment IN VARCHAR2,
32 p_action IN VARCHAR2) AS
33 /******************************************************************
34 Created By : Vinay Chappidi
35 Date Created By : 20-Sep-2001
36 Purpose : Procedure for sending workflow mail notification when the
37 residency status or class is changed, to inform Fee Asess user(STUDENT_FIN User)
38 remarks :
39 Change History
40 Who When What
41 asbala 01-09-2003 Reference: Build SWCR01,02,04
42 ******************************************************************/
43
44
45 CURSOR c_seq_num IS
46 SELECT IGS_PE_PE003_WF_S.nextval
47 FROM DUAL;
48 ln_seq_val NUMBER;
49 l_event_t wf_event_t;
50 l_parameter_list_t wf_parameter_list_t;
51 BEGIN
52
53 -- initialize the parameter list.
54 wf_event_t.Initialize(l_event_t);
55
56 -- set the parameters.
57 wf_event.AddParameterToList ( p_name => 'RES_DTLS_ID', p_value => p_resident_details_id, p_parameterlist => l_parameter_list_t);
58 wf_event.AddParameterToList ( p_name => 'OLD_RES_STATUS', p_value => p_old_res_status, p_parameterlist => l_parameter_list_t);
59 wf_event.AddParameterToList ( p_Name => 'OLD_EVALUATOR', p_Value => p_old_evaluator, p_ParameterList => l_parameter_list_t);
60 wf_event.AddParameterToList ( p_Name => 'OLD_EVAL_DATE', p_Value => p_old_evaluation_date, p_ParameterList => l_parameter_list_t);
61 wf_event.AddParameterToList ( p_Name => 'OLD_COMMENTS', p_Value => p_old_comment, p_ParameterList => l_parameter_list_t);
62 wf_event.AddParameterToList ( p_Name => 'ACTION', p_Value => p_action, p_ParameterList => l_parameter_list_t);
63 -- get the sequence value to be added to EVENT KEY to make it unique.
64 OPEN c_seq_num;
65 FETCH c_seq_num INTO ln_seq_val ;
66 CLOSE c_seq_num ;
67
68 -- raise event
69 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.pe.residency_change',
70 p_event_key => 'IGSPE001'||ln_seq_val,
71 p_parameters => l_parameter_list_t
72 );
73 END change_residence;
74
75
76 PROCEDURE change_address( p_person_number IN VARCHAR2, p_full_name IN VARCHAR2) AS
77 /******************************************************************
78 Created By : Vinay Chappidi
79 Date Created By : 20-Sep-2001
80 Purpose : Procedure for sending workflow mail notification when the
81 address is changed, to inform Responsible Person(RES_PERSON user)
82 Remarks :
83 Change History
84 Who When What
85 nsidana 9/9/2003 Stubbing the procedure as new procedures are in place below.
86 ******************************************************************/
87 BEGIN
88 NULL;
89 END change_address;
90
91
92 PROCEDURE process_residency(itemtype IN VARCHAR2,
93 itemkey IN VARCHAR2,
94 actid IN NUMBER,
95 funcmode IN VARCHAR2,
96 resultout OUT NOCOPY VARCHAR2) IS
97 /******************************************************************
98 Created By : Ashwini Bala
99 Date Created By : 01-Sep-2003
100 Purpose : To trigger the Business Event
101 Remarks :
102 Change History
103 Who When What
104 ******************************************************************/
105
106 CURSOR c_get_person_name(cp_person_id NUMBER) IS
107 SELECT full_name person_name,person_number
108 FROM igs_pe_person_base_v
109 WHERE person_id = cp_person_id;
110
111 CURSOR res_dtl_cur(cp_resident_details_id NUMBER) IS
112 SELECT person_id, cal_type, sequence_number, residency_class, residency_class_desc, residency_status_desc,
113 calendar_desc, evaluation_date, evaluator, comments, last_updated_by
114 FROM igs_pe_res_dtls_v
115 WHERE resident_details_id = cp_resident_details_id;
116
117 CURSOR res_status_cur (cp_lookup_type VARCHAR2, cp_lookup_code VARCHAR2) IS
118 SELECT meaning
119 FROM igs_lookup_values
120 WHERE lookup_type = cp_lookup_type AND
121 lookup_code = cp_lookup_code;
122
123 CURSOR updated_by_cur(cp_user_id NUMBER) IS
124 SELECT user_name
125 FROM fnd_user
126 WHERE user_id = cp_user_id;
127
128 l_res_dtls_id igs_pe_res_dtls_all.resident_details_id%TYPE;
129 l_old_res_status igs_pe_res_dtls_all.residency_status_cd%TYPE;
130 l_old_eval_date DATE;
131 l_old_evaluator igs_pe_res_dtls_all.evaluator%TYPE;
132 l_old_comments igs_pe_res_dtls_all.comments%TYPE;
133 l_res_status_desc igs_lookup_values.meaning%TYPE;
134 l_user_name fnd_user.user_name%TYPE;
135 l_date DATE;
136 l_action VARCHAR2(30);
137 l_name hz_person_profiles.person_name%TYPE;
138 l_person_number hz_parties.party_number%TYPE;
139 l_message VARCHAR2(2000);
140 res_dtl_rec res_dtl_cur%ROWTYPE;
141 BEGIN
142
143 fnd_message.set_name('IGS','IGS_PE_RES_CHG_MES_SUBJ');
144 l_message := fnd_message.get;
145
146 -- Getting the paremeter values from the workflow
147
148 l_res_dtls_id := Wf_Engine.GetItemAttrText(itemtype,itemkey,'RES_DTLS_ID');
149 l_action := Wf_Engine.GetItemAttrText(itemtype,itemkey,'ACTION');
150 l_date := Wf_Engine.GetItemAttrText(itemtype,itemkey,'OLD_EVAL_DATE');
151
152 OPEN res_dtl_cur(l_res_dtls_id);
153 FETCH res_dtl_cur INTO res_dtl_rec;
154 CLOSE res_dtl_cur;
155
156 -- getting the person_number and name for the required person
157 OPEN c_get_person_name(res_dtl_rec.person_id);
158 FETCH c_get_person_name INTO l_name,l_person_number;
159 CLOSE c_get_person_name;
160
161 Wf_Engine.SetItemAttrText(
162 ItemType => itemtype,
163 ItemKey => itemkey,
164 aname => 'PERSON_ID',
165 avalue => res_dtl_rec.person_id
166 );
167
168 Wf_Engine.SetItemAttrText(
169 ItemType => itemtype,
170 ItemKey => itemkey,
171 aname => 'RES_CLASS',
172 avalue => res_dtl_rec.residency_class
173 );
174
175 Wf_Engine.SetItemAttrText(
176 ItemType => itemtype,
177 ItemKey => itemkey,
178 aname => 'NEW_RES_STATUS',
179 avalue => res_dtl_rec.residency_status_desc
180 );
181
182 Wf_Engine.SetItemAttrText(
183 ItemType => itemtype,
184 ItemKey => itemkey,
185 aname => 'NEW_EVAL_DATE',
186 avalue => res_dtl_rec.evaluation_date
187 );
188
189 Wf_Engine.SetItemAttrText(
190 ItemType => itemtype,
191 ItemKey => itemkey,
192 aname => 'CALENDAR_DESC',
193 avalue => res_dtl_rec.calendar_desc
194 );
195
196 Wf_Engine.SetItemAttrText(
197 ItemType => itemtype,
198 ItemKey => itemkey,
199 aname => 'NEW_EVALUATOR',
200 avalue => res_dtl_rec.evaluator
201 );
202 Wf_Engine.SetItemAttrText(
203 ItemType => itemtype,
204 ItemKey => itemkey,
205 aname => 'PERSON_NUMBER',
206 avalue => l_person_number
207 );
208 Wf_Engine.SetItemAttrText(
209 ItemType => itemtype,
210 ItemKey => itemkey,
211 aname => 'NAME',
212 avalue => l_name
213 );
214
215 Wf_Engine.SetItemAttrText(
216 ItemType => itemtype,
217 ItemKey => itemkey,
218 aname => 'RES_CLASS_DESC',
219 avalue => res_dtl_rec.residency_class_desc
220 );
221
222 Wf_Engine.SetItemAttrText(
223 ItemType => itemtype,
224 ItemKey => itemkey,
225 aname => 'NEW_COMMENTS',
226 avalue => res_dtl_rec.comments
227 );
228 Wf_Engine.SetItemAttrText(
229 ItemType => itemtype,
230 ItemKey => itemkey,
231 aname => 'SUBJECT',
232 avalue => l_message
233 );
234
235 OPEN updated_by_cur(res_dtl_rec.last_updated_by);
236 FETCH updated_by_cur INTO l_user_name;
237 CLOSE updated_by_cur;
238
239 Wf_Engine.SetItemAttrText(
240 ItemType => itemtype,
241 ItemKey => itemkey,
242 aname => 'UPDATED_BY',
243 avalue => l_user_name
244 );
245
246 IF l_action='U' THEN
247
248 l_old_res_status := Wf_Engine.GetItemAttrText(itemtype,itemkey,'OLD_RES_STATUS');
249
250 OPEN res_status_cur('PE_RES_STATUS', l_old_res_status);
251 FETCH res_status_cur INTO l_res_status_desc;
252 CLOSE res_status_cur;
253
254 Wf_Engine.SetItemAttrText(
255 ItemType => itemtype,
256 ItemKey => itemkey,
257 aname => 'OLD_RES_STATUS',
258 avalue => l_res_status_desc
259 );
260
261 resultout := 'COMPLETE:U';
262 ELSIF l_action='I' THEN
263 resultout := 'COMPLETE:I';
264 END IF;
265
266 END process_residency;
267
268
269 PROCEDURE get_res_details(
270 p_person_id IN NUMBER,
271 p_res_class IN VARCHAR2,
272 p_res_dtls_rec OUT NOCOPY igs_pe_res_dtls_v%ROWTYPE,
273 p_ind IN VARCHAR2 DEFAULT 'NEW' ) IS
274
275 /******************************************************************
276 Created By : Ashwini Bala
277 Date Created By : 1-Sep-2003
278 Purpose : To get the details from the database
279 Remarks :
280 Change History
281 Who When What
282 ******************************************************************/
283 BEGIN
284 NULL;
285 END get_res_details;
286
287 PROCEDURE address_create(itemtype IN VARCHAR2,
288 itemkey IN VARCHAR2,
289 actid IN NUMBER,
290 funcmode IN VARCHAR2,
291 resultout OUT NOCOPY VARCHAR2)
292
293 /******************************************************************
294 Created By : Navin Sidana.
295 Date Created By : 9/9/2003
296 Purpose : Function to process party site create event
297 raised from HZ.
298 remarks :
299 Change History
300 Who When What
301 ******************************************************************/
302 IS
303
304 CURSOR cur_get_party_id(cp_party_site_id NUMBER) IS
305 SELECT party_number, party_name, location_id,hp.party_type,hp.party_id
306 FROM hz_parties hp, hz_party_sites hps
307 WHERE hp.party_id = hps.party_id AND
308 (hp.party_type = 'PERSON' OR hp.party_type = 'ORGANIZATION') AND
309 hp.status = 'A' AND
310 hps.party_site_id = cp_party_site_id;
311
312 CURSOR get_location_dets(cp_party_site_id NUMBER) IS
313 SELECT hzl.address1, hzl.address2, hzl.address3, hzl.address4, hzl.city, hzl.province, hzl.state ,
314 hzl.county, hzl.postal_code, hzl.country, hzl.delivery_point_code ,hzp.party_number,
315 hzp.party_name,hps.identifying_address_flag
316 FROM
317 hz_party_sites hps,
318 hz_locations hzl,
319 hz_parties hzp
320 WHERE
321 hps.party_site_id=cp_party_site_id AND
322 hps.location_id=hzl.location_id AND
323 hzp.party_id=hps.party_id;
324
325
326 CURSOR chk_oss_party(cp_party_id NUMBER) IS
327 SELECT inst_org_ind
328 FROM
329 igs_pe_hz_parties
330 WHERE
331 party_id = cp_party_id;
332
333
334 CURSOR get_country(cp_country VARCHAR2) IS
335 SELECT territory_short_name
336 FROM fnd_territories_vl
337 WHERE territory_code = cp_country;
338
339 CURSOR get_lkup_meaning(cp_lk_code VARCHAR2) IS
340 SELECT meaning
341 FROM igs_lookup_values
342 WHERE
343 lookup_type='YES_NO' AND
344 lookup_code=cp_lk_code;
345
346 CURSOR get_effective_dates_cur (cp_party_site_id NUMBER) IS
347 SELECT start_date, end_date
348 FROM igs_pe_hz_pty_sites
349 WHERE party_site_id = cp_party_site_id;
350
351 cur_get_party_id_rec cur_get_party_id%ROWTYPE;
352 get_location_dets_rec get_location_dets%ROWTYPE;
353 chk_oss_party_rec chk_oss_party%ROWTYPE;
354 get_country_rec get_country%ROWTYPE;
355 l_lkup_meaning VARCHAR2(100);
356 l_party_site_id VARCHAR2(100);
357 l_message VARCHAR2(200);
358 l_country_desc VARCHAR2(100);
359 get_effective_dates_rec get_effective_dates_cur%ROWTYPE;
360
361 BEGIN
362 -- get the party side ID from the business event generated.
363 l_party_site_id := wf_engine.getitemattrtext(itemtype,itemkey,'PARTY_SITE_ID');
364 -- check if this ID is for any PERSON. If not set the outcome status to FAIL otherwise process.
365 OPEN cur_get_party_id(l_party_site_id);
366 FETCH cur_get_party_id INTO cur_get_party_id_rec;
367 IF (cur_get_party_id%FOUND) THEN
368 -- ID is for a PERSON, so proceed further.
369 CLOSE cur_get_party_id;
370
371 -- check if its an OSS party.
372 OPEN chk_oss_party(cur_get_party_id_rec.party_id);
373 FETCH chk_oss_party INTO chk_oss_party_rec;
374 IF (chk_oss_party%FOUND)
375 THEN
376 CLOSE chk_oss_party;
377
378 -- OSS party, proceed further.
379 OPEN get_location_dets(l_party_site_id);
380 FETCH get_location_dets INTO get_location_dets_rec;
381 IF (get_location_dets%FOUND) THEN
382
383 -- set the workflow parameters.
384
385 fnd_message.set_name('IGS','IGS_PE_ADDR_CHG_MES_SUBJ');
386 l_message := fnd_message.get;
387
388 Wf_Engine.SetItemAttrText(
389 ItemType => itemtype,
390 ItemKey => itemkey,
391 aname => 'PERSON_NUMBER',
392 avalue => get_location_dets_rec.party_number
393 );
394
395 Wf_Engine.SetItemAttrText(
396 ItemType => itemtype,
397 ItemKey => itemkey,
398 aname => 'NAME',
399 avalue => get_location_dets_rec.party_name
400 );
401
402 Wf_Engine.SetItemAttrText(
403 ItemType => itemtype,
404 ItemKey => itemkey,
405 aname => 'NEW_ADDRESS1',
406 avalue => get_location_dets_rec.address1
407 );
408 Wf_Engine.SetItemAttrText(
409 ItemType => itemtype,
410 ItemKey => itemkey,
411 aname => 'NEW_ADDRESS2',
412 avalue => get_location_dets_rec.address2
413 );
414 Wf_Engine.SetItemAttrText(
415 ItemType => itemtype,
416 ItemKey => itemkey,
417 aname => 'NEW_ADDRESS3',
418 avalue => get_location_dets_rec.address3
419 );
420 Wf_Engine.SetItemAttrText(
421 ItemType => itemtype,
422 ItemKey => itemkey,
423 aname => 'NEW_ADDRESS4',
424 avalue => get_location_dets_rec.address4
425 );
426 Wf_Engine.SetItemAttrText(
427 ItemType => itemtype,
428 ItemKey => itemkey,
429 aname => 'NEW_CITY',
430 avalue => get_location_dets_rec.city
431 );
432 Wf_Engine.SetItemAttrText(
433 ItemType => itemtype,
434 ItemKey => itemkey,
435 aname => 'NEW_STATE',
436 avalue => get_location_dets_rec.state
437 );
438 Wf_Engine.SetItemAttrText(
439 ItemType => itemtype,
440 ItemKey => itemkey,
441 aname => 'NEW_COUNTY',
442 avalue => get_location_dets_rec.county
443 );
444 Wf_Engine.SetItemAttrText(
445 ItemType => itemtype,
446 ItemKey => itemkey,
447 aname => 'NEW_POSTAL_CODE',
448 avalue => get_location_dets_rec.postal_code
449 );
450
451 Wf_Engine.SetItemAttrText(
452 ItemType => itemtype,
453 ItemKey => itemkey,
454 aname => 'NEW_PROVINCE',
455 avalue => get_location_dets_rec.province
456 );
457
458 OPEN get_country(get_location_dets_rec.country);
459 FETCH get_country INTO l_country_desc;
460 CLOSE get_country;
461
462 Wf_Engine.SetItemAttrText(
463 ItemType => itemtype,
464 ItemKey => itemkey,
465 aname => 'NEW_COUNTRY',
466 avalue => l_country_desc
467 );
468 Wf_Engine.SetItemAttrText(
469 ItemType => itemtype,
470 ItemKey => itemkey,
471 aname => 'NEW_DEL_POINT',
472 avalue => get_location_dets_rec.delivery_point_code
473 );
474
475 OPEN get_effective_dates_cur(l_party_site_id);
476 FETCH get_effective_dates_cur INTO get_effective_dates_rec;
477 CLOSE get_effective_dates_cur;
478
479 Wf_Engine.SetItemAttrText(
480 ItemType => itemtype,
481 ItemKey => itemkey,
482 aname => 'NEW_FROM_DATE',
483 avalue => get_effective_dates_rec.start_date
484 );
485 Wf_Engine.SetItemAttrText(
486 ItemType => itemtype,
487 ItemKey => itemkey,
488 aname => 'NEW_TO_DATE',
489 avalue => get_effective_dates_rec.end_date
490 );
491
492 OPEN get_lkup_meaning(get_location_dets_rec.identifying_address_flag);
493 FETCH get_lkup_meaning INTO l_lkup_meaning;
494 CLOSE get_lkup_meaning;
495
496 Wf_Engine.SetItemAttrText(
497 ItemType => itemtype,
498 ItemKey => itemkey,
499 aname => 'NEW_PRIMARY_ADDRESS',
500 avalue => l_lkup_meaning
501 );
502 Wf_Engine.SetItemAttrText(
503 ItemType => itemtype,
504 ItemKey => itemkey,
505 aname => 'SUBJECT',
506 avalue => l_message
507 );
508 IF (cur_get_party_id_rec.party_type='PERSON') THEN
509 l_message:=null;
510 fnd_message.set_name('IGS','IGS_PR_PERSON_ID');
511 l_message := fnd_message.get;
512
513 ELSE
514 IF ((cur_get_party_id_rec.party_type='ORGANIZATION') AND (chk_oss_party_rec.inst_org_ind='I'))THEN
515 l_message:=null;
516 fnd_message.set_name('IGS','IGS_OR_INSTITUTION_CODE');
517 l_message := fnd_message.get;
518
519 ELSE
520 IF ((cur_get_party_id_rec.party_type='ORGANIZATION') AND (chk_oss_party_rec.inst_org_ind='O')) THEN
521 l_message:=null;
522 fnd_message.set_name('IGS','IGS_RE_ORG_UNIT_CD');
523 l_message := fnd_message.get;
524
525 END IF;
526 END IF;
527 END IF;
528
529 Wf_Engine.SetItemAttrText(
530 ItemType => itemtype,
531 ItemKey => itemkey,
532 aname => 'PARTY_TYPE',
533 avalue => l_message
534 );
535
536 resultout := 'SUCCESS';
537 END IF;
538 CLOSE get_location_dets;
539 ELSE
540 -- not an OSS party, set the status to FAIL.
541 resultout := 'FAIL';
542 CLOSE chk_oss_party;
543 END IF;
544 ELSE
545 -- ID is not for a PERSON.
546 resultout := 'FAIL';
547 CLOSE cur_get_party_id;
548 END IF;
549
550 END address_create;
551
552 PROCEDURE address_update(itemtype IN VARCHAR2,
553 itemkey IN VARCHAR2,
554 actid IN NUMBER,
555 funcmode IN VARCHAR2,
556 resultout OUT NOCOPY VARCHAR2)
557 /******************************************************************
558 Created By : Navin Sidana.
559 Date Created By : 9/9/2003
560 Purpose : Function to process location update event
561 raised from HZ.
562 remarks :
563 Change History
564 Who When What
565 gmaheswa 17-Jan-1006 4938278: Modified complete logic as per R12 Business Events Mandate Build.
566 ******************************************************************/
567 IS
568
569 CURSOR chk_oss_party(cp_party_site_id NUMBER) IS
570 SELECT hp.party_number, hp.party_name, hps.location_id,
571 php.inst_org_ind, hps.identifying_address_flag, php.oss_org_unit_cd
572 FROM hz_parties hp, hz_party_sites hps, igs_pe_hz_parties php
573 WHERE hp.party_id = hps.party_id
574 AND hp.party_id = php.party_id
575 AND hp.status = 'A'
576 AND hps.status = 'A'
577 AND hps.party_site_id = cp_party_site_id;
578
579 CURSOR get_location_dtls(cp_location_id NUMBER) IS
580 SELECT address1, address2, address3, address4, city, province, state ,
581 county, postal_code, country, delivery_point_code
582 FROM hz_locations
583 WHERE location_id = cp_location_id;
584
585 CURSOR get_effective_dates (cp_party_site_id NUMBER) IS
586 SELECT start_date, end_date
587 FROM igs_pe_hz_pty_sites
588 WHERE party_site_id = cp_party_site_id;
589
590 CURSOR get_old_addr_dtls(cp_location_id NUMBER) IS
591 SELECT address1, address2, address3, address4, city,
592 prov_state_admin_code, county, postal_code, country
593 FROM hz_location_profiles
594 WHERE location_id = cp_location_id
595 AND SYSDATE NOT BETWEEN effective_start_date AND NVL(effective_end_date,SYSDATE)
596 ORDER BY location_profile_id DESC;
597
598 CURSOR get_country(cp_country VARCHAR2) IS
599 SELECT territory_short_name
600 FROM fnd_territories_vl
601 WHERE territory_code = cp_country;
602
603 rec_chk_oss_party chk_oss_party%ROWTYPE;
604 rec_location_dtls get_location_dtls%ROWTYPE;
605 rec_effective_dates get_effective_dates%ROWTYPE;
606 rec_old_addr_dtls get_old_addr_dtls%ROWTYPE;
607
608 l_party_site_id NUMBER;
609 l_country_desc VARCHAR2(360);
610 l_new_country_desc VARCHAR2(360);
611 l_message VARCHAR2(2000);
612 BEGIN
613
614 --get Party Site ID from the business event that was raised.
615
616 l_party_site_id := wf_engine.getitemattrtext(itemtype,itemkey,'PARTY_SITE_ID');
617
618 --Check whether the party site is of an OSS party. If not then set the result to FAIL and stop processing.
619 OPEN chk_oss_party(l_party_site_id);
620 FETCH chk_oss_party INTO rec_chk_oss_party;
621
622 IF (chk_oss_party%NOTFOUND) THEN
623 CLOSE chk_oss_party;
624 resultout :='FAIL';
625 RETURN;
626 ELSE
627
628 CLOSE chk_oss_party;
629 --If the identifying_address_flag = 'Y' then it's a primary address. Set the parameter P_PRIM_ADDR_MSG with the message IGS_PE_PRIM_ADDR_MSG
630 IF rec_chk_oss_party.identifying_address_flag = 'Y' THEN
631 fnd_message.set_name('IGS','IGS_PE_PRIM_ADDR_MSG');
632 l_message := fnd_message.get;
633
634 Wf_Engine.SetItemAttrText(
635 ItemType => itemtype,
636 ItemKey => itemkey,
637 aname => 'P_PRIM_ADDR_MSG',
638 avalue => l_message
639 );
640 END IF;
641
642 Wf_Engine.SetItemAttrText(
643 ItemType => itemtype,
644 ItemKey => itemkey,
645 aname => 'NAME',
646 avalue => rec_chk_oss_party.party_name
647 );
648
649 --Get the current details of the Address record from HZ_LOCATIONS table and set the respective workflow NEW_ parameters.
650 OPEN get_location_dtls(rec_chk_oss_party.location_id);
651 FETCH get_location_dtls INTO rec_location_dtls;
652 CLOSE get_location_dtls;
653
654 Wf_Engine.SetItemAttrText(
655 ItemType => itemtype,
656 ItemKey => itemkey,
657 aname => 'NEW_ADDRESS1',
658 avalue => rec_location_dtls.ADDRESS1
659 );
660 Wf_Engine.SetItemAttrText(
661 ItemType => itemtype,
662 ItemKey => itemkey,
663 aname => 'NEW_ADDRESS2',
664 avalue => rec_location_dtls.ADDRESS2
665 );
666 Wf_Engine.SetItemAttrText(
667 ItemType => itemtype,
668 ItemKey => itemkey,
669 aname => 'NEW_ADDRESS3',
670 avalue => rec_location_dtls.ADDRESS3
671 );
672 Wf_Engine.SetItemAttrText(
673 ItemType => itemtype,
674 ItemKey => itemkey,
675 aname => 'NEW_ADDRESS4',
676 avalue => rec_location_dtls.ADDRESS4
677 );
678 Wf_Engine.SetItemAttrText(
679 ItemType => itemtype,
680 ItemKey => itemkey,
681 aname => 'NEW_CITY',
682 avalue => rec_location_dtls.CITY
683 );
684 Wf_Engine.SetItemAttrText(
685 ItemType => itemtype,
686 ItemKey => itemkey,
687 aname => 'NEW_STATE',
688 avalue => rec_location_dtls.STATE
689 );
690 Wf_Engine.SetItemAttrText(
691 ItemType => itemtype,
692 ItemKey => itemkey,
693 aname => 'NEW_COUNTY',
694 avalue => rec_location_dtls.COUNTY
695 );
696 Wf_Engine.SetItemAttrText(
697 ItemType => itemtype,
698 ItemKey => itemkey,
699 aname => 'NEW_POSTAL_CODE',
700 avalue => rec_location_dtls.POSTAL_CODE
701 );
702 Wf_Engine.SetItemAttrText(
703 ItemType => itemtype,
704 ItemKey => itemkey,
705 aname => 'NEW_PROVINCE',
706 avalue => rec_location_dtls.PROVINCE
707 );
708 --For Country show the Description and set in the item attribute.
709 OPEN get_country(rec_location_dtls.country);
710 FETCH get_country INTO l_new_country_desc;
711 CLOSE get_country;
712
713 Wf_Engine.SetItemAttrText(
714 ItemType => itemtype,
715 ItemKey => itemkey,
716 aname => 'NEW_COUNTRY',
717 avalue => l_new_country_desc
718 );
719 Wf_Engine.SetItemAttrText(
720 ItemType => itemtype,
721 ItemKey => itemkey,
722 aname => 'NEW_DEL_POINT',
723 avalue => rec_location_dtls.DELIVERY_POINT_CODE
724 );
725
726 --Get the start and end date of the Address from the IGS_PE_HZ_PTY_SITES table and set the M_NEW_ start and end date parameters.
727 OPEN get_effective_dates(l_party_site_id);
728 FETCH get_effective_dates INTO rec_effective_dates;
729 CLOSE get_effective_dates;
730
731 Wf_Engine.SetItemAttrText(
732 ItemType => itemtype,
733 ItemKey => itemkey,
734 aname => 'NEW_FROM_DATE',
735 avalue => rec_effective_dates.start_date
736 );
737 Wf_Engine.SetItemAttrText(
738 ItemType => itemtype,
739 ItemKey => itemkey,
740 aname => 'NEW_TO_DATE',
741 avalue => rec_effective_dates.end_date
742 );
743
744 --Get the previous details of the Address record from the HZ_LOCATION_PROFILES table. And set the respective OLD_ workflow parameters.
745 OPEN get_old_addr_dtls(rec_chk_oss_party.location_id);
746 FETCH get_old_addr_dtls INTO rec_old_addr_dtls;
747 CLOSE get_old_addr_dtls;
748
749 Wf_Engine.SetItemAttrText(
750 ItemType => itemtype,
751 ItemKey => itemkey,
752 aname => 'OLD_ADDRESS1',
753 avalue => rec_old_addr_dtls.ADDRESS1
754 );
755 Wf_Engine.SetItemAttrText(
756 ItemType => itemtype,
757 ItemKey => itemkey,
758 aname => 'OLD_ADDRESS2',
759 avalue => rec_old_addr_dtls.ADDRESS2
760 );
761 Wf_Engine.SetItemAttrText(
762 ItemType => itemtype,
763 ItemKey => itemkey,
764 aname => 'OLD_ADDRESS3',
765 avalue => rec_old_addr_dtls.ADDRESS3
766 );
767 Wf_Engine.SetItemAttrText(
768 ItemType => itemtype,
769 ItemKey => itemkey,
770 aname => 'OLD_ADDRESS4',
771 avalue => rec_old_addr_dtls.ADDRESS4
772 );
773 Wf_Engine.SetItemAttrText(
774 ItemType => itemtype,
775 ItemKey => itemkey,
776 aname => 'OLD_CITY',
777 avalue => rec_old_addr_dtls.CITY
778 );
779 Wf_Engine.SetItemAttrText(
780 ItemType => itemtype,
781 ItemKey => itemkey,
782 aname => 'OLD_STATE',
783 avalue => rec_old_addr_dtls.PROV_STATE_ADMIN_CODE
784 );
785 Wf_Engine.SetItemAttrText(
786 ItemType => itemtype,
787 ItemKey => itemkey,
788 aname => 'OLD_COUNTY',
789 avalue => rec_old_addr_dtls.COUNTY
790 );
791
792 Wf_Engine.SetItemAttrText(
793 ItemType => itemtype,
794 ItemKey => itemkey,
795 aname => 'OLD_POSTAL_CODE',
796 avalue => rec_old_addr_dtls.POSTAL_CODE
797 );
798 -- Set the SUBJECT with message IGS_PE_ADDR_CHG_MES_SUBJ -Address Change Notification
799 fnd_message.set_name('IGS','IGS_PE_ADDR_CHG_MES_SUBJ');
800 l_message := fnd_message.get;
801 Wf_Engine.SetItemAttrText(
802 ItemType => itemtype,
803 ItemKey => itemkey,
804 aname => 'SUBJECT',
805 avalue => l_message
806 );
807
808 --For Country show the Description and set in the item attribute.
809 OPEN get_country(rec_old_addr_dtls.country);
810 FETCH get_country INTO l_country_desc;
811 CLOSE get_country;
812
813 Wf_Engine.SetItemAttrText(
814 ItemType => itemtype,
815 ItemKey => itemkey,
816 aname => 'OLD_COUNTRY',
817 avalue => l_country_desc
818 );
819
820 --Set PARTY_TYPE and PERSON_NUMBER as per the INST_ORG_IND. INST_ORG_IND is null for PERSON. INST_ORG_IND is 'I' for INSTITUTION. INST_ORG_IND is 'O' for ORGANIZATION.
821 IF rec_chk_oss_party.inst_org_ind IS NULL THEN
822
823 fnd_message.set_name('IGS','IGS_PR_PERSON_ID');
824 l_message := fnd_message.get;
825 Wf_Engine.SetItemAttrText(
826 ItemType => itemtype,
827 ItemKey => itemkey,
828 aname => 'PARTY_TYPE',
829 avalue => l_message
830 );
831 Wf_Engine.SetItemAttrText(
832 ItemType => itemtype,
833 ItemKey => itemkey,
834 aname => 'PERSON_NUMBER',
835 avalue => rec_chk_oss_party.PARTY_NUMBER
836 );
837 ELSIF rec_chk_oss_party.inst_org_ind = 'I' THEN
838 fnd_message.set_name('IGS','IGS_OR_INSTITUTION_CODE');
839 l_message := fnd_message.get;
840
841 Wf_Engine.SetItemAttrText(
842 ItemType => itemtype,
843 ItemKey => itemkey,
844 aname => 'PARTY_TYPE',
845 avalue => l_message
846 );
847 Wf_Engine.SetItemAttrText(
848 ItemType => itemtype,
849 ItemKey => itemkey,
850 aname => 'PERSON_NUMBER',
851 avalue => rec_chk_oss_party.OSS_ORG_UNIT_CD
852 );
853 ELSIF rec_chk_oss_party.inst_org_ind = 'O' THEN
854 fnd_message.set_name('IGS','IGS_RE_ORG_UNIT_CD');
855 l_message := fnd_message.get;
856
857 Wf_Engine.SetItemAttrText(
858 ItemType => itemtype,
859 ItemKey => itemkey,
860 aname => 'PARTY_TYPE',
861 avalue => l_message
862 );
863 Wf_Engine.SetItemAttrText(
864 ItemType => itemtype,
865 ItemKey => itemkey,
866 aname => 'PERSON_NUMBER',
867 avalue => rec_chk_oss_party.OSS_ORG_UNIT_CD
868 );
869 END IF;
870
871 resultout := 'SUCCESS';
872
873 END IF;
874
875 END address_update;
876
877
878 PROCEDURE primary_address_ind_update(itemtype IN VARCHAR2,
879 itemkey IN VARCHAR2,
880 actid IN NUMBER,
881 funcmode IN VARCHAR2,
882 resultout OUT NOCOPY VARCHAR2)
883 /******************************************************************
884 Created By : Navin Sidana.
885 Date Created By : 9/9/2003
886 Purpose : Function to process party site update event
887 raised from HZ.
888 remarks :
889 Change History
890 Who When What
891 gmaheswa 17-Jan-06 4938278: Stubbed.
892 ******************************************************************/
893 IS
894
895 BEGIN
896 NULL;
897 END primary_address_ind_update;
898
899 PROCEDURE change_housing_status(p_person_id IN NUMBER,
900 p_housing_status IN VARCHAR2,
901 P_CALENDER_TYPE IN VARCHAR2,
902 P_CAL_SEQ_NUM IN NUMBER,
903 P_TEACHING_PERIOD_ID IN NUMBER,
904 P_ACTION IN VARCHAR2 ) IS
905 /******************************************************************
906 Created By : Uma Maheswari
907 Date Created By : 3-Nov-2004
908 Purpose : Procedure for raising an event when the
909 Housing status is changed.
910 remarks :
911 Change History
912 Who When What
913 ******************************************************************/
914
915
916 CURSOR c_seq_num IS
917 SELECT IGS_PE_CHG_HOUSING_STAT_S.nextval
918 FROM DUAL;
919 ln_seq_val NUMBER;
920 l_event_t wf_event_t;
921 l_parameter_list_t wf_parameter_list_t;
922 BEGIN
923
924 -- initialize the parameter list.
925 wf_event_t.Initialize(l_event_t);
926
927 -- set the parameters.
928 wf_event.AddParameterToList ( p_name => 'PERSON_ID' , p_value => p_person_id , p_parameterlist => l_parameter_list_t);
929 wf_event.AddParameterToList ( p_name => 'HOUSING_STATUS' , p_value => p_housing_status , p_parameterlist => l_parameter_list_t);
930 wf_event.AddParameterToList ( p_Name => 'CALENDER_TYPE' , p_Value => P_CALENDER_TYPE , p_ParameterList => l_parameter_list_t);
931 wf_event.AddParameterToList ( p_Name => 'CAL_SEQUENCE_NUMBER' , p_Value => P_CAL_SEQ_NUM , p_ParameterList => l_parameter_list_t);
932 wf_event.AddParameterToList ( p_Name => 'TEACHING_PERIOD_ID' , p_Value => P_TEACHING_PERIOD_ID , p_ParameterList => l_parameter_list_t);
933 wf_event.AddParameterToList ( p_Name => 'ACTION' , p_Value => p_action , p_ParameterList => l_parameter_list_t);
934
935 -- get the sequence value to be added to EVENT KEY to make it unique.
936 OPEN c_seq_num;
937 FETCH c_seq_num INTO ln_seq_val ;
938 CLOSE c_seq_num ;
939 -- raise event
940 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.pe.housing_status.change',
941 p_event_key => 'HOUSING_STATUS'||ln_seq_val,
942 p_parameters => l_parameter_list_t
943 );
944 END change_housing_status;
945
946
947
948 PROCEDURE process_addr_sync(itemtype in varchar2,
949 itemkey in varchar2,
950 actid in number,
951 funcmode in varchar2,
952 resultout out NOCOPY varchar2
953 )
954 ------------------------------------------------------------------------------------
955 --Created by : sarakshi ( Oracle IDC)
956 --Date created: 20-Jan-2006
957 --
958 --Purpose: Generate the body of the message .
959 --
960 --Known limitations/enhancements and/or remarks:
961 --
962 --Change History:
963 --Who When What
964 -------------------------------------------------------------------------------------
965 AS
966 CURSOR cur_party_type (cp_lookup_type igs_lookup_values.lookup_type%TYPE,
967 cp_lookup_code igs_lookup_values.lookup_code%TYPE) IS
968 SELECT meaning
969 FROM igs_lookup_values
970 WHERE lookup_type=cp_lookup_type
971 AND lookup_code=cp_lookup_code;
972 l_party_type igs_lookup_values.meaning%TYPE;
973
974 CURSOR cur_message_text (cp_message_name fnd_new_messages.message_name%TYPE) IS
975 SELECT message_text
976 FROM fnd_new_messages
977 WHERE message_name=cp_message_name
978 AND application_id = 8405
979 AND LANGUAGE_CODE = USERENV('LANG');
980 l_message_text fnd_new_messages.message_text%TYPE;
981
982 l_c_per_org VARCHAR2(1);
983
984 BEGIN
985
986 IF (funcmode = 'RUN') THEN
987
988 l_c_per_org := wf_engine.GetItemAttrText (
989 itemtype => itemtype,
990 itemkey => itemkey,
991 aname => 'P_BULK_PROC_CONTEXT');
992
993
994 IF l_c_per_org IS NULL THEN
995 OPEN cur_party_type('IGS_PE_HOLDS','PERSON');
996 FETCH cur_party_type INTO l_party_type;
997 CLOSE cur_party_type;
998
999 OPEN cur_message_text('IGS_PR_PERSON_ID');
1000 FETCH cur_message_text INTO l_message_text;
1001 CLOSE cur_message_text;
1002 ELSE
1003 OPEN cur_party_type('ORG_STRUCTURE_TYPE','INSTITUTE');
1004 FETCH cur_party_type INTO l_party_type;
1005 CLOSE cur_party_type;
1006
1007 OPEN cur_message_text('IGS_OR_INSTITUTION_CODE');
1008 FETCH cur_message_text INTO l_message_text;
1009 CLOSE cur_message_text;
1010 END IF;
1011
1012 wf_engine.SetItemAttrText (itemtype => itemtype,
1013 itemkey => itemkey,
1014 aname => 'P_PARTY_TYPE_TITLE',
1015 avalue => l_party_type);
1016
1017
1018
1019 wf_engine.SetItemAttrText (itemtype => itemtype,
1020 itemkey => itemkey,
1021 aname => 'PARTY_TYPE',
1022 avalue => l_message_text);
1023
1024 wf_engine.SetItemAttrText(itemtype => itemtype,
1025 itemkey => itemkey,
1026 aname => 'P_ADDR_SYNC_MSG_TEXT',
1027 avalue => 'PLSQLCLOB:igs_pe_wf_gen.write_addr_sync_message/'|| itemtype || ':' || itemkey);
1028
1029
1030 Resultout:= 'COMPLETE:';
1031 RETURN;
1032 END IF;
1033
1034 END process_addr_sync;
1035
1036 PROCEDURE write_addr_sync_message(document_id IN VARCHAR2,
1037 display_type IN VARCHAR2,
1038 document IN OUT NOCOPY CLOB,
1039 document_type IN OUT NOCOPY VARCHAR2
1040 )
1041 ------------------------------------------------------------------------------------
1042 --Created by : sarakshi ( Oracle IDC)
1043 --Date created: 20-Jan-2006
1044 --
1045 --Purpose: Generate the body of the message .
1046 --
1047 --Known limitations/enhancements and/or remarks:
1048 --
1049 --Change History:
1050 --Who When What
1051 -------------------------------------------------------------------------------------
1052 AS
1053
1054 l_c_document VARCHAR2(32000);
1055 l_c_itemtype VARCHAR2(100);
1056 l_c_itemkey WF_ITEM_ATTRIBUTE_VALUES.ITEM_KEY%TYPE;
1057
1058 CURSOR c_person(cp_person_id hz_parties.party_id%TYPE) IS
1059 SELECT '<tr><td>'||party_number||'<br></td><td>'|| person_last_name||', '||person_first_name|| '<br></td></tr>' person_record
1060 FROM hz_parties
1061 WHERE party_id = cp_person_id;
1062 l_person_record VARCHAR2(2000);
1063
1064 CURSOR c_inst(cp_party_id igs_or_inst_org_base_v.party_id%TYPE) IS
1065 SELECT '<tr><td>'||party_number||'<br></td><td>'|| party_name|| '<br></td></tr>' person_record
1066 FROM igs_or_inst_org_base_v
1067 WHERE party_id = cp_party_id
1068 AND inst_org_ind='I';
1069
1070 TYPE person_id IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
1071 person_id_tbl person_id;
1072 l_n_cntr NUMBER;
1073
1074 i_event wf_event_t;
1075 l_clob_data CLOB;
1076 l_c_per_org VARCHAR2(1);
1077
1078 PROCEDURE populate_table (p_c_person_str VARCHAR2) IS
1079 l_c_length NUMBER;
1080 l_c_start_position NUMBER;
1081 l_c_end_position NUMBER;
1082 l_c_value VARCHAR2(15);
1083
1084 BEGIN
1085 --Create the pl-sql table, containing all the persons
1086
1087 l_c_length := NVL(LENGTH(LTRIM(RTRIM(p_c_person_str))),0);
1088 l_c_start_position:=1;
1089 WHILE (l_c_length >= l_c_start_position) LOOP
1090
1091 l_c_end_position:=INSTR(p_c_person_str,',',l_c_start_position);
1092
1093 IF l_c_end_position =0 THEN
1094 l_c_value:=SUBSTR(p_c_person_str,l_c_start_position,l_c_length - l_c_start_position + 1);
1095 ELSE
1096 l_c_value:=SUBSTR(p_c_person_str,l_c_start_position,l_c_end_position - l_c_start_position);
1097 l_c_start_position:=l_c_end_position + 1;
1098 END IF;
1099
1100 l_n_cntr := l_n_cntr+1;
1101 person_id_tbl(l_n_cntr):= TO_NUMBER(l_c_value);
1102 IF l_c_end_position = 0 THEN
1103 EXIT;
1104 END IF;
1105 END LOOP;
1106
1107 END populate_table;
1108
1109 PROCEDURE prepare_plsql_table(p_clob_object IN OUT NOCOPY CLOB) IS
1110 l_rawBuffer VARCHAR2(32767);
1111 l_amount BINARY_INTEGER;
1112 l_totalLen INTEGER;
1113 l_offset INTEGER ;
1114 l_c_data VARCHAR2(32767);
1115
1116 BEGIN
1117 -- Get the data from the clob into a variable in the chunk of 32767 characters.
1118 -- Note same chunk size was used to create the clob.
1119 l_amount := 32767;
1120 l_offset := 1;
1121
1122 l_totalLen := DBMS_LOB.GETLENGTH(p_clob_object);
1123 l_n_cntr :=0;
1124 WHILE l_totalLen >= l_amount LOOP
1125 DBMS_LOB.READ(p_clob_object, l_amount, l_offset, l_rawBuffer);
1126 l_c_data := l_rawBuffer;
1127 populate_table(RTRIM(l_c_data));
1128 l_totalLen := l_totalLen - l_amount;
1129 l_offset := l_offset + l_amount;
1130 END LOOP;
1131
1132 IF l_totalLen > 0 THEN
1133 DBMS_LOB.READ(p_clob_object, l_totalLen, l_offset, l_rawBuffer);
1134 l_c_data := l_rawBuffer;
1135 populate_table(RTRIM(l_c_data));
1136 END IF;
1137
1138 END prepare_plsql_table;
1139
1140 BEGIN
1141 /* Get item type and item key */
1142
1143 l_c_itemtype := SUBSTR(document_id, 1, instr(document_id, ':') - 1);
1144 l_c_itemkey := SUBSTR(document_id, instr(document_id, ':') + 1, length(document_id));
1145
1146
1147 -- get the handle of the event instance, and get the value of the event data.
1148 i_event := Wf_Engine.GetItemAttrEvent(l_c_itemtype, l_c_itemkey, 'P_EVENT_DATA');
1149 l_clob_data := i_event.event_data;
1150 --Prepare the pl-sql table from the clob object
1151 prepare_plsql_table(l_clob_data);
1152
1153
1154
1155 l_c_per_org := wf_engine.GetItemAttrText (
1156 itemtype => l_c_itemtype,
1157 itemkey => l_c_itemkey,
1158 aname => 'P_BULK_PROC_CONTEXT');
1159
1160 -- write to the CLOB object from the above pl-sql table
1161 IF person_id_tbl.EXISTS(1) THEN
1162
1163 l_n_cntr :=0;
1164 FOR i IN 1..person_id_tbl.last LOOP
1165
1166 IF l_c_per_org IS NULL THEN
1167 OPEN c_person(person_id_tbl(i));
1168 FETCH c_person INTO l_person_record;
1169 CLOSE c_person;
1170 ELSE
1171 OPEN c_inst(person_id_tbl(i));
1172 FETCH c_inst INTO l_person_record;
1173 CLOSE c_inst;
1174 END IF;
1175
1176
1177 l_c_document := l_c_document||l_person_record;
1178 l_n_cntr := l_n_cntr+1;
1179 IF l_n_cntr = 90 THEN
1180 /* Write the header doc into CLOB variable */
1181 WF_NOTIFICATION.WriteToClob(document, l_c_document);
1182 l_c_document := NULL;
1183 l_n_cntr := 0;
1184 END IF;
1185
1186 END LOOP;
1187 IF l_n_cntr <>0 THEN
1188 WF_NOTIFICATION.WriteToClob(document, l_c_document);
1189 END IF;
1190 END IF;
1191
1192 person_id_tbl.DELETE;
1193
1194 END write_addr_sync_message;
1195
1196 PROCEDURE addr_bulk_synchronization (p_persons_processes IN OUT NOCOPY t_addr_chg_persons) IS
1197
1198
1199 /* Cursor for Sequence */
1200 CURSOR c_seq IS
1201 SELECT IGS_PE_PE002_WF_S.NEXTVAL
1202 FROM DUAL;
1203 l_n_key NUMBER;
1204
1205 l_wf_event_t WF_EVENT_T;
1206 l_wf_parameter_list_t WF_PARAMETER_LIST_T;
1207 l_eventdata CLOB;
1208 l_str VARCHAR2(32767);
1209
1210 CURSOR cur_inst_per (cp_party_id igs_pe_hz_parties.party_id%TYPE) IS
1211 SELECT inst_org_ind
1212 FROM igs_pe_hz_parties
1213 WHERE party_id=cp_party_id;
1214 l_c_per_org VARCHAR2(1);
1215
1216 BEGIN
1217
1218 IF p_persons_processes.EXISTS(1) THEN
1219
1220 WF_EVENT_T.Initialize(l_wf_event_t);
1221 --
1222 -- set the event name
1223 --
1224 l_wf_event_t.setEventName( pEventName => 'oracle.apps.igs.pe.addr_bulk_sync');
1225 --
1226 -- event key to identify uniquely
1227 --
1228 OPEN c_seq;
1229 FETCH c_seq INTO l_n_key;
1230 CLOSE c_seq;
1231 --
1232 --
1233 -- set the parameter list
1234 --
1235 l_wf_event_t.setParameterList ( pParameterList => l_wf_parameter_list_t );
1236 --
1237 OPEN cur_inst_per(p_persons_processes(1));
1238 FETCH cur_inst_per INTO l_c_per_org;
1239 CLOSE cur_inst_per;
1240
1241 wf_event.AddParameterToList ( p_name => 'P_BULK_PROC_CONTEXT', p_value => l_c_per_org,p_parameterlist => l_wf_parameter_list_t);
1242 -- Write the clob
1243 dbms_lob.createtemporary(l_eventdata, FALSE,DBMS_LOB.CALL);
1244
1245
1246 FOR i IN 1..p_persons_processes.last LOOP
1247
1248 IF l_str IS NULL THEN
1249 l_str:=TO_CHAR(p_persons_processes(i));
1250 ELSE
1251 l_str:=l_str||','||TO_CHAR(p_persons_processes(i));
1252 END IF;
1253
1254 IF LENGTH(l_str) >= (32767-15) THEN
1255 --Make a string of length 32767 , after the last person it is space.
1256 l_str:=RPAD(l_str,32767);
1257 dbms_lob.writeappend(l_eventdata, length(l_str),l_str);
1258 l_str:=NULL;
1259 END IF;
1260 END LOOP;
1261
1262 IF LENGTH(l_str)> 0 THEN
1263 --Make a string of length 32767 , after the last person it is space.
1264 l_str:=RPAD(l_str,32767);
1265 dbms_lob.writeappend(l_eventdata, length(l_str),l_str);
1266 l_str:=NULL;
1267 END IF;
1268
1269 --
1270 -- raise the event
1271 wf_event.raise (
1272 p_event_name => 'oracle.apps.igs.pe.addr_bulk_sync',
1273 p_event_key => 'IGSPE002ADDRBULK'||l_n_key,
1274 p_parameters => l_wf_parameter_list_t,
1275 p_event_data => l_eventdata
1276 );
1277
1278 END IF;
1279 p_persons_processes.DELETE;
1280
1281 END addr_bulk_synchronization;
1282 PROCEDURE raise_acad_intent_event(P_ACAD_INTENT_ID IN NUMBER,
1283 P_PERSON_ID IN NUMBER,
1284 P_CAL_TYPE IN VARCHAR2,
1285 P_CAL_SEQ_NUMBER IN NUMBER,
1286 P_ACAD_INTENT_CODE IN VARCHAR2,
1287 P_OLD_ACAD_INTENT_CODE IN VARCHAR2 )
1288 IS
1289 /******************************************************************
1290 Created By : Uma Maheswari
1291 Date Created By : 3-Nov-2004
1292 Purpose : Procedure for raising an event when the
1293 Housing status is changed.
1294 remarks :
1295 Change History
1296 Who When What
1297 vskumar 23-May-2006 bug 5211157 forward port the procedure from bug 2882588.
1298 ******************************************************************/
1299
1300 l_event_t wf_event_t;
1301 l_parameter_list_t wf_parameter_list_t;
1302 BEGIN
1303
1304 -- initialize the parameter list.
1305 wf_event_t.Initialize(l_event_t);
1306
1307 -- set the parameters.
1308 wf_event.AddParameterToList ( p_name => 'P_PERSON_ID', p_value => p_person_id, p_parameterlist => l_parameter_list_t);
1309 wf_event.AddParameterToList ( p_Name => 'P_CAL_TYPE', p_Value => P_CAL_TYPE, p_ParameterList => l_parameter_list_t);
1310 wf_event.AddParameterToList ( p_Name => 'P_CAL_SEQ_NUMBER', p_Value => P_CAL_SEQ_NUMBER, p_ParameterList => l_parameter_list_t);
1311 wf_event.AddParameterToList ( p_Name => 'P_ACAD_INTENT_CODE' , p_Value => P_ACAD_INTENT_CODE, p_ParameterList => l_parameter_list_t);
1312 wf_event.AddParameterToList ( p_Name => 'P_OLD_ACAD_INTENT_CODE', p_Value => P_OLD_ACAD_INTENT_CODE, p_ParameterList => l_parameter_list_t);
1313
1314 -- raise event
1315 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.pe.acad_intent.change',
1316 p_event_key => 'ACAD_INTENT'||P_ACAD_INTENT_ID,
1317 p_parameters => l_parameter_list_t
1318 );
1319
1320 l_parameter_list_t.DELETE;
1321 END raise_acad_intent_event;
1322
1323 PROCEDURE process_acad_intent(itemtype IN VARCHAR2, itemkey IN VARCHAR2, actid IN NUMBER,
1324 funcmode IN VARCHAR2, resultout OUT NOCOPY VARCHAR2)
1325 IS
1326 /******************************************************************
1327 Created By : Uma Maheswari
1328 Date Created By : 3-Nov-2004
1329 Purpose : Procedure for raising an event when the
1330 Housing status is changed.
1331 remarks :
1332 Change History
1333 Who When What
1334 vskumar 23-May-2006 bug 5211157 forward port the procedure from bug 2882588.
1335 ******************************************************************/
1336 CURSOR person_dtl_cur(cp_person_id NUMBER) IS
1337 SELECT person_number, full_name
1338 FROM igs_pe_person_base_v
1339 WHERE person_id = cp_person_id;
1340
1341 CURSOR acad_intent_dtl_cur (cp_lookup_code VARCHAR2, cp_lookup_type VARCHAR2) IS
1342 SELECT meaning
1343 FROM igs_lookup_values
1344 WHERE lookup_type = cp_lookup_type
1345 AND lookup_code = cp_lookup_code;
1346
1347 CURSOR term_desc_cur (cp_cal_type VARCHAR2, cp_sequence_number NUMBER) IS
1348 SELECT description
1349 FROM igs_ca_inst_all
1350 WHERE cal_type = cp_cal_type
1351 AND sequence_number = cp_sequence_number;
1352
1353 l_person_id NUMBER;
1354
1355 l_acad_intent_code VARCHAR2(30);
1356 l_old_acad_intent_code VARCHAR2(30);
1357 l_acad_intent_desc VARCHAR2(80);
1358 l_old_acad_intent_desc VARCHAR2(80);
1359
1360 l_term_desc igs_ca_inst_all.description%TYPE;
1361 l_cal_type igs_ca_inst_all.cal_type%TYPE;
1362 l_cal_seq_num igs_ca_inst_all.sequence_number%TYPE;
1363
1364 person_dtl_rec person_dtl_cur%ROWTYPE;
1365
1366 BEGIN
1367
1368 l_person_id := wf_engine.getitemattrnumber(itemtype,itemkey,'P_PERSON_ID');
1369 l_acad_intent_code := wf_engine.getitemattrtext(itemtype,itemkey,'P_ACAD_INTENT_CODE');
1370 l_old_acad_intent_code := wf_engine.getitemattrtext(itemtype,itemkey,'P_OLD_ACAD_INTENT_CODE');
1371 l_cal_type := wf_engine.getitemattrtext(itemtype,itemkey,'P_CAL_TYPE');
1372 l_cal_seq_num := wf_engine.getitemattrnumber(itemtype,itemkey,'P_CAL_SEQ_NUMBER');
1373
1374 OPEN person_dtl_cur(l_person_id);
1375 FETCH person_dtl_cur INTO person_dtl_rec;
1376 CLOSE person_dtl_cur;
1377
1378 Wf_Engine.SetItemAttrText(
1379 ItemType => itemtype,
1380 ItemKey => itemkey,
1381 aname => 'P_PERSON_NUMBER',
1382 avalue => person_dtl_rec.person_number
1383 );
1384
1385 Wf_Engine.SetItemAttrText(
1386 ItemType => itemtype,
1387 ItemKey => itemkey,
1388 aname => 'P_PERSON_NAME',
1389 avalue => person_dtl_rec.full_name
1390 );
1391
1392 OPEN acad_intent_dtl_cur(l_acad_intent_code,'PE_ACAD_INTENTS');
1393 FETCH acad_intent_dtl_cur INTO l_acad_intent_desc;
1394 CLOSE acad_intent_dtl_cur;
1395
1396 Wf_Engine.SetItemAttrText(
1397 ItemType => itemtype,
1398 ItemKey => itemkey,
1399 aname => 'P_ACAD_INTENT_DESC',
1400 avalue => l_acad_intent_desc
1401 );
1402
1403 IF l_old_acad_intent_code IS NOT NULL THEN
1404 OPEN acad_intent_dtl_cur(l_old_acad_intent_code, 'PE_ACAD_INTENTS');
1405 FETCH acad_intent_dtl_cur INTO l_old_acad_intent_desc;
1406 CLOSE acad_intent_dtl_cur;
1407
1408 Wf_Engine.SetItemAttrText(
1409 ItemType => itemtype,
1410 ItemKey => itemkey,
1411 aname => 'P_OLD_ACAD_INTENT_DESC',
1412 avalue => l_old_acad_intent_desc
1413 );
1414 END IF;
1415
1416 OPEN term_desc_cur(l_cal_type, l_cal_seq_num);
1417 FETCH term_desc_cur INTO l_term_desc;
1418 CLOSE term_desc_cur;
1419
1420 Wf_Engine.SetItemAttrText(
1421 ItemType => itemtype,
1422 ItemKey => itemkey,
1423 aname => 'P_CAL_DESC',
1424 avalue => l_term_desc
1425 );
1426
1427 END process_acad_intent;
1428 END igs_pe_wf_gen;