1 PACKAGE BODY igs_pe_gen_002 AS
2 /* $Header: IGSPE14B.pls 120.1 2006/01/18 22:32:51 skpandey noship $ */
3 /* Change Hisotry
4 Who When What
5 kumma 11-MAR-2003 2841566, Removed the statement close c_user from procedure Receive_External_Hold when the p_admin is passed null
6 asbala 22-AUG-2003 3071111: GSCC FILE.DATE.5 Compliance
7 ssawhney 17 Aug 3690580, for perf reasons changed cursor hr_record, in get_hr_installed.
8 */
9 PROCEDURE apply_admin_hold
10 /*
11 || Created By : ssawhney
12 || Created On : 17-feb-2003
13 || Purpose : This Procedure will apply admin holds on a person. There were 3 steps while applying admin hold and not just a call to the TBH
14 || Hence created an API that all the 3 process are kept together and can be used
15 || The API will return DECODED messages.
16 || Known limitations, enhancements or remarks :
17 || Change History :
18 || Who When What
19 || (reverse chronological order - newest change first)
20 */
21
22 (P_PERSON_ID IN hz_parties.party_id%TYPE,
23 P_ENCUMBRANCE_TYPE IN igs_pe_pers_encumb.encumbrance_type%TYPE,
24 P_START_DT IN Date,
25 P_END_DT IN Date,
26 P_AUTHORISING_PERSON_ID IN hz_parties.party_id%TYPE,
27 P_COMMENTS IN igs_pe_pers_encumb.comments%TYPE,
28 P_SPO_COURSE_CD IN igs_pe_pers_encumb.spo_course_cd%TYPE,
29 P_SPO_SEQUENCE_NUMBER IN igs_pe_pers_encumb.spo_sequence_number%TYPE,
30 P_CAL_TYPE IN igs_pe_pers_encumb.cal_type%TYPE,
31 P_SEQUENCE_NUMBER IN igs_pe_pers_encumb.sequence_number%TYPE,
32 P_AUTH_RESP_ID IN igs_pe_pers_encumb.auth_resp_id%TYPE,
33 P_EXTERNAL_REFERENCE IN igs_pe_pers_encumb.external_reference%TYPE,
34 P_MESSAGE_NAME OUT NOCOPY varchar2,
35 P_MESSAGE_STRING OUT NOCOPY Varchar2
36 ) IS
37
38 l_rowid varchar2(30);
39 l_message_name varchar2(2000);
40 l_message_string varchar2(2000);
41 l_err_raised BOOLEAN;
42 l_app fnd_new_messages.application_id%TYPE;
43 ln_msg_index number;
44
45 BEGIN
46
47 l_message_name := NULL;
48 l_message_string := NULL;
49 l_err_raised :=FALSE;
50 BEGIN
51
52 SAVEPOINT hold_insert;
53
54 -- issue a savepoint and start the insertion directly.
55
56 igs_pe_pers_encumb_pkg.insert_row
57 (
58 x_mode => 'R' ,
59 x_rowid => l_rowid ,
60 x_person_id => p_person_id ,
61 x_encumbrance_type => p_encumbrance_type ,
62 x_start_dt => p_start_dt ,
63 x_expiry_dt => p_end_dt ,
64 x_authorising_person_id => p_authorising_person_id, -- let the tbh handle this,
65 x_comments => p_comments ,
66 x_spo_course_cd => p_spo_course_cd ,
67 x_spo_sequence_number => p_spo_sequence_number ,
68 x_cal_type => p_cal_type ,
69 x_sequence_number => p_sequence_number ,
70 x_auth_resp_id => p_auth_resp_id,
71 x_external_reference => p_external_reference
72 ) ;
73
74 EXCEPTION
75 WHEN OTHERS THEN
76 ROLLBACK to hold_insert;
77 l_err_raised := TRUE ;
78
79 -- get the exception raised from the STACK
80 IGS_GE_MSG_STACK.GET(-1, 'F', l_message_name, ln_msg_index);
81
82 IF l_message_name is NOT NULL THEN
83 p_message_name := l_message_name;
84 p_message_string := null;
85 RETURN;
86 END IF;
87 END ;
88
89 IF NOT (l_err_raised) THEN
90
91 -- if exception is not raised continue on applying the default effects.
92
93 BEGIN
94 --check if the encumbrance has effects which require that the active
95 -- enrolments be dicontinued , validate that SCA'S are inactive
96 IF igs_en_val_pen.finp_val_encmb_eff ( p_person_id ,
97 p_encumbrance_type ,
98 p_start_dt ,
99 NULL ,
100 l_message_name
101 ) = FALSE
102 THEN
103 -- get the exception raised from the STACK
104 IGS_GE_MSG_STACK.GET(-1, 'F', l_message_name, ln_msg_index);
105 p_message_name := l_message_name;
106 p_message_string := null;
107 RETURN;
108 END IF;
109
110 -- call the procedure which creates the default effects for the encumbrance type .
111 igs_en_gen_009.enrp_ins_dflt_effect ( p_person_id ,
112 p_encumbrance_type ,
113 p_start_dt ,
114 NULL ,
115 NULL ,
116 l_message_name ,
117 l_message_string
118 ) ;
119 IF l_message_name IS NOT NULL THEN
120
121 -- get the exception raised from the STACK
122 IGS_GE_MSG_STACK.GET(-1, 'F', l_message_name, ln_msg_index);
123 p_message_name := l_message_name;
124 p_message_string := l_message_string;
125 RETURN;
126 END IF;
127
128 -- do not trap any exception, let them get raised directly.
129
130 END ; --inside BEGIN
131 END IF;
132 END apply_admin_hold;
133
134 PROCEDURE raise_success_event
135 (
136 p_person_number IN VARCHAR2,
137 p_hold_type IN VARCHAR2,
138 p_start_dt IN VARCHAR2,
139 p_end_dt IN VARCHAR2
140 ) IS
141 /*
142 || Created By : kumma
143 || Created On : 17-feb-2003
144 || Purpose : This is a local procedure called when event has to be raised.
145 || Known limitations, enhancements or remarks :
146 || Change History :
147 || Who When What
148 || (reverse chronological order - newest change first)
149 */
150 CURSOR c_seq_num IS
151 SELECT
152 IGS_PE_PE003_WF_S.nextval
153 FROM
154 DUAL;
155
156 ln_seq_val NUMBER;
157 l_event_t wf_event_t;
158 l_parameter_list_t wf_parameter_list_t;
159
160 BEGIN
161
162 -- initialize the parameter list.
163 wf_event_t.Initialize(l_event_t);
164
165 -- set the parameters.
166 wf_event.AddParameterToList ( p_name => 'P_PERSON_NUMBER' , p_value => p_person_number , p_parameterlist => l_parameter_list_t);
167 wf_event.AddParameterToList ( p_Name => 'P_HOLD_TYPE' , p_Value => p_hold_type , p_ParameterList => l_parameter_list_t);
168 wf_event.AddParameterToList ( p_Name => 'P_START_DT' , p_Value => p_start_dt , p_ParameterList => l_parameter_list_t);
169 wf_event.AddParameterToList ( p_Name => 'P_END_DT' , p_Value => p_end_dt , p_ParameterList => l_parameter_list_t);
170
171 -- get the sequence value to be added to EVENT KEY to make it unique.
172 OPEN c_seq_num;
173 FETCH c_seq_num INTO ln_seq_val ;
174 CLOSE c_seq_num ;
175
176
177 WF_EVENT.RAISE (p_event_name => 'oracle.apps.igs.pe.extholdss',
178 p_event_key => 'PE003C'||ln_seq_val,
179 p_parameters => l_parameter_list_t
180 );
181 END raise_success_event;
182
183 PROCEDURE Receive_External_Hold (
184 itemtype IN VARCHAR2,
185 itemkey IN VARCHAR2,
186 actid IN NUMBER,
187 funcmode IN VARCHAR2,
188 resultout OUT NOCOPY VARCHAR2
189 )IS
190 /*
191 || Created By : kumma
192 || Created On : 17-feb-2003
193 || Purpose : This Procedure will be called from WF, IGSPE003. This will be doing the processing of external holds.
194 || and will raise failure events/notifications if required.
195 || Known limitations, enhancements or remarks :
196 || Change History :
197 || Who When What
198 || (reverse chronological order - newest change first)
199 || ssawhney Incorrect check while cheking for SUSPENSION effect.
200 || BUG : 2832973. message stack initialzied and record already exists added
201 || check for 'IGS_EN_ENCMBR_HAS_SPECIFIED' also added explicity
202 || kumma 11-MAR-2003 2841566, Removed the close c_user statement from the else clause when the p_admin is null
203 || ssaleem 13-Apr-2005 Bug 4293911 Fnd User customer Id replaced with person
204 || party id
205 */
206
207
208
209 -- fnd user check cursor.
210 CURSOR c_user (cp_user fnd_user.user_name%TYPE) IS
211 SELECT
212 'X'
213 FROM
214 fnd_user
215 WHERE
216 user_name = cp_user AND
217 email_address IS NOT NULL AND
218 person_party_id IS NOT NULL;
219
220 -- student check cursor
221 CURSOR c_student_chk (cp_person_number igs_pe_person_base_v.person_number%TYPE) IS
222 SELECT
223 person_id,NVL(full_name,person_number) full_name
224 FROM
225 igs_pe_person_base_v
226 WHERE
227 person_number = cp_person_number;
228
229 -- hold type exists check cursor
230 CURSOR c_hold_typ_exst(cp_encumbrance_type igs_fi_encmb_type.encumbrance_type%TYPE) IS
231 SELECT
232 'X'
233 FROM
234 igs_fi_encmb_type
235 WHERE
236 encumbrance_type = cp_encumbrance_type AND
237 s_encumbrance_cat = 'ADMIN';
238
239 -- check whether release hold should be called or apply hold should be called
240 -- change view IGS_PE_PERS_ENCUMB from igs_pe_pers_pen_encumb_v
241 CURSOR c_hold_alrdy_exst(cp_person_id igs_pe_person_base_v.person_id%TYPE,
242 cp_encumbrance_type igs_pe_pers_encumb_v.encumbrance_type%TYPE,
243 cp_start_dt DATE) IS
244 SELECT
245 expiry_dt
246 FROM
247 IGS_PE_PERS_ENCUMB
248 WHERE
249 person_id = cp_person_id AND
250 encumbrance_type = cp_encumbrance_type AND
251 start_dt = cp_start_dt;
252
253 -- check for mismatch of external ref passed
254 -- remove reference to the view.
255 CURSOR c_external_hold_exst(cp_person_id igs_pe_person_base_v.person_id%TYPE,
256 cp_encumbrance_type igs_pe_pers_encumb_v.encumbrance_type%TYPE,
257 cp_start_dt DATE,
258 cp_external_reference igs_pe_pers_encumb_v.external_reference%TYPE) IS
259 SELECT
260 'X'
261 FROM
262 igs_pe_pers_encumb
263 WHERE
264 person_id = cp_person_id AND
265 encumbrance_type = cp_encumbrance_type AND
266 start_dt = cp_start_dt AND
267 external_reference = cp_external_reference;
268
269 -- cursor to check if the hold has an effect of SUS_COURSE.
270 CURSOR c_hold_eff(cp_hold_type igs_pe_pers_encumb_v.encumbrance_type%TYPE) IS
271 SELECT 'X'
272 FROM igs_fi_enc_dflt_eft
273 WHERE encumbrance_type = cp_hold_type AND
274 s_encmb_effect_type = 'SUS_COURSE';
275
276
277
278 l_exist_exp_dt DATE;
279 l_exist VARCHAR2(1);
280 ln_msg_index NUMBER;
281 l_person_number VARCHAR2(30);
282 l_hold_type igs_pe_pers_encumb.encumbrance_type%TYPE;
283 l_start_dt VARCHAR2(30);
284 l_d_start_dt DATE := NULL;
285
286 l_expiry_date VARCHAR2(30);
287 l_d_expiry_date DATE := NULL;
288
289 l_external_ref igs_pe_pers_encumb.external_reference%TYPE;
290 l_admin fnd_user.user_name%TYPE;
291 l_person_name igs_pe_person_base_v.full_name%TYPE;
292
293
294 l_student_rec c_student_chk%ROWTYPE;
295 l_message_name VARCHAR2(2000);
296 l_message_string VARCHAR2(2000);
297
298 l_role_name VARCHAR2(320);
299 l_role_display_name VARCHAR2(320);
300
301 l_error VARCHAR2(30);
302 l_app fnd_new_messages.application_id%TYPE;
303 v_message_name VARCHAR2(30);
304
305
306 PROCEDURE Message_Get(
307 p_message_name IN VARCHAR2,
308 p_message_text OUT NOCOPY VARCHAR2) IS
309
310 /* local procedure to get the text of the message.
311 || Change History :
312 || Who When What
313 || (reverse chronological order - newest change first)
314 asbala 22-AUG-2003 3071111: GSCC FILE.DATE.5 Compliance
315 */
316
317 CURSOR c_text (cp_message_name fnd_new_messages.message_name%TYPE) IS
318 SELECT message_text
319 FROM fnd_new_messages
320 WHERE message_name = cp_message_name AND
321 application_id=8405 AND
322 LANGUAGE_CODE = USERENV('LANG');
323
324 l_text fnd_new_messages.message_text%TYPE;
325 BEGIN
326
327 OPEN c_text (p_message_name);
328 FETCH c_text into l_text;
329 CLOSE c_text;
330
331 IF l_text IS NOT NULL THEN
332 p_message_text := l_text;
333 RETURN;
334 END IF;
335
336 p_message_text := '';
337 EXCEPTION
338 WHEN OTHERS THEN
339 IF c_text%ISOPEN THEN
340 CLOSE c_text;
341 p_message_text :='Null';
342 RETURN;
343 END IF;
344 END Message_Get;
345
346 BEGIN
347
348 IF funcmode = 'RUN' THEN
349
350 l_role_display_name := 'Adhoc Role for External Holds IGSPE003';
351
352 -- initalize the message stacks and the variables.
353 l_message_name := null;
354 l_message_string := null;
355 FND_MSG_PUB.initialize;
356
357 -- Fetch all the parameters from Event
358 l_person_number := wf_engine.GetItemAttrText(itemtype,itemkey,'P_PERSON_NUMBER' );
359 l_hold_type := wf_engine.GetItemAttrText(itemtype,itemkey,'P_HOLD_TYPE');
360 l_start_dt := wf_engine.GetItemAttrText(itemtype,itemkey,'P_START_DT' );
361 l_expiry_date := wf_engine.GetItemAttrText(itemtype,itemkey,'P_EXPIRATION_DATE' );
362 l_external_ref := wf_engine.GetItemAttrText(itemtype,itemkey,'P_EXTERNAL_REFERENCE' );
363 l_admin := wf_engine.GetItemAttrText(itemtype,itemkey,'P_ADMIN' );
364
365
366 --Check that l_admin is not null, if it is not null then create the role else return with 'E'
367 IF l_admin IS NOT NULL THEN
368 OPEN c_user(l_admin);
369 FETCH c_user INTO l_exist;
370
371 -- set error
372 l_error :='IGS_PE_FND_USR_INCOMP';
373 message_get(l_error,l_message_name);
374 IF c_user%NOTFOUND THEN
375 Wf_Engine.SetItemAttrText(
376 ItemType => itemtype,
377 ItemKey => itemkey,
378 aname => 'P_ERROR',
379 avalue => l_message_name
380 );
381
382 resultout := 'COMPLETE:E';
383 CLOSE c_user;
384 return;
385 ELSE
386 CLOSE c_user;
387 --Create the role
388 l_role_name := 'IGS'||itemkey;
389
390 Wf_Directory.CreateAdHocRole (
391 role_name => l_role_name,
392 role_display_name => l_role_display_name
393 );
394
395 Wf_Directory.AddUsersToAdHocRole (
396 role_name => l_role_name,
397 role_users => l_admin
398 );
399
400 Wf_Engine.SetItemAttrText(
401 ItemType => itemtype,
402 ItemKey => itemkey,
403 aname => 'P_ADMIN',
404 avalue => l_role_name
405 );
406
407 END IF;
408 ELSE
409
410 -- set error
411 l_error :='IGS_FI_PARAMETER_NULL';
412 message_get(l_error,l_message_name);
413 Wf_Engine.SetItemAttrText(
414 ItemType => itemtype,
415 ItemKey => itemkey,
416 aname => 'P_ERROR',
417 avalue => l_message_name
418 );
419
420 resultout := 'COMPLETE:E';
421 --kumma,2841566, removed the line "CLOSE c_user" Commented the following line as this cursor will not get open for this case.
422 return;
423 END IF;
424
425
426 --Validate that none of the paramater except expiry_date and person_name is null
427 IF l_person_number IS NULL OR l_hold_type IS NULL OR
428 l_start_dt IS NULL OR l_external_ref IS NULL THEN
429 -- set error
430 l_error :='IGS_FI_PARAMETER_NULL';
431 message_get(l_error,l_message_name);
432 Wf_Engine.SetItemAttrText(
433 ItemType => itemtype,
434 ItemKey => itemkey,
435 aname => 'P_ERROR',
436 avalue => l_message_name
437 );
438
439 resultout := 'COMPLETE:F';
440 return;
441 END IF;
442
443 --check if the person is a student
444 OPEN c_student_chk(l_person_number);
445 FETCH c_student_chk INTO l_student_rec;
446
447 IF c_student_chk%NOTFOUND THEN
448 CLOSE c_student_chk;
449 -- set error
450 l_error :='IGS_GE_INVALID_PERSON_NUMBER';
451 message_get(l_error,l_message_name);
452 Wf_Engine.SetItemAttrText(
453 ItemType => itemtype,
454 ItemKey => itemkey,
455 aname => 'P_ERROR',
456 avalue => l_message_name
457 );
458 resultout := 'COMPLETE:F';
459 return;
460 ELSE
461 CLOSE c_student_chk;
462 Wf_Engine.SetItemAttrText(
463 ItemType => itemtype,
464 ItemKey => itemkey,
465 aname => 'P_PERSON_NAME',
466 avalue => l_student_rec.full_name
467 );
468
469 l_person_name := wf_engine.GetItemAttrText(itemtype,itemkey,'P_PERSON_NAME' );
470 END IF;
471
472
473 --check if the hold type exists
474 OPEN c_hold_typ_exst(l_hold_type);
475 FETCH c_hold_typ_exst INTO l_exist;
476
477 IF c_hold_typ_exst%NOTFOUND THEN
478 CLOSE c_hold_typ_exst;
479
480 l_error := 'IGS_PE_INVALID_HOLD';
481 message_get(l_error,l_message_name);
482 Wf_Engine.SetItemAttrText(
483 ItemType => itemtype,
484 ItemKey => itemkey,
485 aname => 'P_ERROR',
486 avalue => l_message_name
487 );
488
489 resultout := 'COMPLETE:F';
490 return;
491 ELSE
492 CLOSE c_hold_typ_exst;
493 l_exist := NULL;
494 -- this means hold exists, now check if it has an effect of SUS_COURSE
495 OPEN c_hold_eff(l_hold_type);
496 FETCH c_hold_eff INTO l_exist;
497 -- if suspension effect exists then raise the error else do nothing.
498 IF c_hold_eff%FOUND THEN
499 CLOSE c_hold_eff;
500 l_error := 'IGS_PE_INVALID_HOLD';
501 message_get(l_error,l_message_name);
502 Wf_Engine.SetItemAttrText(
503 ItemType => itemtype,
504 ItemKey => itemkey,
505 aname => 'P_ERROR',
506 avalue => l_message_name
507 );
508
509 resultout := 'COMPLETE:F';
510 return;
511 ELSE
512 CLOSE c_hold_eff;
513 END IF;
514
515 END IF;
516
517
518
519 --check the date format of the start date
520 DECLARE
521 CURSOR c_dt_format(cp_date VARCHAR2) IS
522 SELECT igs_ge_date.igsdate(igs_ge_date.igschar(cp_date))
523 FROM DUAL;
524
525
526 BEGIN
527
528 OPEN c_dt_format(l_start_dt);
529 FETCH c_dt_format INTO l_d_start_dt;
530 CLOSE c_dt_format;
531
532 IF l_expiry_date IS NOT NULL THEN
533 OPEN c_dt_format(l_expiry_date);
534 FETCH c_dt_format INTO l_d_expiry_date;
535 CLOSE c_dt_format;
536 END IF;
537 EXCEPTION
538 WHEN OTHERS THEN
539 IF c_dt_format%ISOPEN THEN
540 CLOSE c_dt_format;
541 END IF;
542 l_error := 'IGS_PE_INVLD_DATE_FRMT';
543 message_get(l_error,l_message_name);
544 Wf_Engine.SetItemAttrText(
545 ItemType => itemtype,
546 ItemKey => itemkey,
547 aname => 'P_ERROR',
548 avalue => l_message_name
549 );
550 resultout := 'COMPLETE:F';
551 return;
552 END;
553
554 --Check whether the hold already exists on the student
555 OPEN c_hold_alrdy_exst(l_student_rec.person_id,l_hold_type, igs_ge_date.igsdate(igs_ge_date.igschar(l_start_dt)));
556 FETCH c_hold_alrdy_exst INTO l_exist_exp_dt;
557
558 IF c_hold_alrdy_exst%NOTFOUND THEN
559 CLOSE c_hold_alrdy_exst;
560 --apply the hold
561
562 -- ELSIF (( l_exist_exp_dt IS NULL) AND (l_expiry_date IS NOT NULL)) THEN
563 -- this is the case where there exists a hold of the same type and is NOT end date
564 -- and the user is trying to apply the similar hold on a different dates and is providing an exipry for the new hold.
565 -- this is NOT allowed.
566 -- in the TBH this error is trapped only when inserting and exp_dt null, but explicitly trapped in the form
567 -- hence trapping it here...SSAWHNEY
568
569 IF IGS_EN_VAL_PEN.enrp_val_pen_open (
570 l_student_rec.person_id,
571 l_hold_type,
572 igs_ge_date.igsdate(igs_ge_date.igschar(l_start_dt)),
573 v_message_name) = FALSE THEN
574
575 IF v_message_name IS NOT NULL then
576
577
578 l_error :='IGS_EN_ENCMBR_HAS_SPECIFIED';
579 message_get(l_error,l_message_name);
580 Wf_Engine.SetItemAttrText(
581 ItemType => itemtype,
582 ItemKey => itemkey,
583 aname => 'P_ERROR',
584 avalue => l_message_name
585 );
586 resultout := 'COMPLETE:F';
587 return;
588 END IF;
589 END IF;
590
591 -- initialize message variables.
592 l_message_name := NULL;
593 l_message_string := NULL;
594 igs_pe_gen_002.apply_admin_hold
595 (
596 p_person_id => l_student_rec.person_id,
597 p_encumbrance_type => l_hold_type,
598 p_start_dt => igs_ge_date.igsdate(igs_ge_date.igschar(l_start_dt)),
599 p_end_dt => l_d_expiry_date,
600 p_authorising_person_id => NULL,
601 p_comments => NULL,
602 p_spo_course_cd => NULL,
603 p_spo_sequence_number => NULL,
604 p_cal_type => NULL,
605 p_sequence_number => NULL,
606 p_auth_resp_id => NULL,
607 p_external_reference => l_external_ref,
608 p_message_name => l_message_name,
609 p_message_string => l_message_string
610 );
611
612 IF (l_message_name IS NULL AND l_message_string IS NULL ) THEN
613 --Hold was applied successfully
614
615 resultout := 'COMPLETE:S';
616
617 --raise the event to communicate it to the external system
618 raise_success_event(l_person_number, l_hold_type, l_start_dt, l_expiry_date);
619
620 return;
621
622 ELSE
623
624 --Hold was NOT applied successfully
625 Wf_Engine.SetItemAttrText(
626 ItemType => itemtype,
627 ItemKey => itemkey,
628 aname => 'P_ERROR',
629 avalue => l_message_name || ',' || l_message_string
630 );
631 resultout := 'COMPLETE:F';
632 return;
633 END IF;
634
635 ELSE
636 CLOSE c_hold_alrdy_exst;
637 --release the hold
638
639 -- ELSIF (( l_exist_exp_dt IS NULL) AND (l_expiry_date IS NOT NULL)) THEN
640 -- this is the case where there exists a hold of the same type and is NOT end date
641 -- and the user is trying to apply the similar hold on a different dates and is providing an exipry for the new hold.
642 -- this is NOT allowed.
643 -- in the TBH this error is trapped only when inserting and exp_dt null, but explicitly trapped in the form
644 -- hence trapping it here...SSAWHNEY
645
646 IF IGS_EN_VAL_PEN.enrp_val_pen_open (
647 l_student_rec.person_id,
648 l_hold_type,
649 igs_ge_date.igsdate(igs_ge_date.igschar(l_start_dt)),
650 v_message_name) = FALSE THEN
651
652 IF v_message_name IS NOT NULL then
653
654
655 l_error :='IGS_EN_ENCMBR_HAS_SPECIFIED';
656 message_get(l_error,l_message_name);
657 Wf_Engine.SetItemAttrText(
658 ItemType => itemtype,
659 ItemKey => itemkey,
660 aname => 'P_ERROR',
661 avalue => l_message_name
662 );
663 resultout := 'COMPLETE:F';
664 return;
665 END IF;
666 END IF;
667
668
669
670 --Before releasing check the end date with the sysdate
671 IF l_expiry_date IS NOT NULL THEN
672 IF igs_ge_date.igsdate(igs_ge_date.igschar(l_expiry_date)) < TRUNC(SYSDATE) THEN
673 l_error :='IGS_EN_DT_NOT_LT_CURR_DT';
674 message_get(l_error,l_message_name);
675 Wf_Engine.SetItemAttrText(
676 ItemType => itemtype,
677 ItemKey => itemkey,
678 aname => 'P_ERROR',
679 avalue => l_message_name
680 );
681 resultout := 'COMPLETE:F';
682 return;
683 ELSIF igs_ge_date.igsdate(igs_ge_date.igschar(l_expiry_date)) < igs_ge_date.igsdate(igs_ge_date.igschar(l_start_dt)) THEN
684 l_error := 'IGS_EN_EXPDT_GE_STDT';
685 message_get(l_error,l_message_name);
686 Wf_Engine.SetItemAttrText(
687 ItemType => itemtype,
688 ItemKey => itemkey,
689 aname => 'P_ERROR',
690 avalue => l_message_name
691 );
692 resultout := 'COMPLETE:F';
693 return;
694 END IF;
695
696 -- else trying to make the expiry date of a hold already set to NULL
697 ELSIF l_exist_exp_dt IS NOT NULL THEN
698 l_error :='IGS_PE_EXP_DATE_NT_NULL';
699 message_get(l_error,l_message_name);
700 Wf_Engine.SetItemAttrText(
701 ItemType => itemtype,
702 ItemKey => itemkey,
703 aname => 'P_ERROR',
704 avalue => l_message_name
705 );
706 resultout := 'COMPLETE:F';
707 return;
708 -- ie, the expiry date is passed BUT there exists a similar hold on the student with same start date AND OPEN.
709 -- need to trap this here, as it was by passing all and going into release hold, which raises an error invalid parameter combo
710 -- which will not make much sense for the user.
711
712 ELSIF (( l_exist_exp_dt IS NULL) AND (l_expiry_date IS NULL)) THEN
713 l_error :='IGS_GE_RECORD_ALREADY_EXISTS';
714 message_get(l_error,l_message_name);
715 Wf_Engine.SetItemAttrText(
716 ItemType => itemtype,
717 ItemKey => itemkey,
718 aname => 'P_ERROR',
719 avalue => l_message_name
720 );
721 resultout := 'COMPLETE:F';
722 return;
723
724
725 END IF;
726
727 OPEN c_external_hold_exst(l_student_rec.person_id,l_hold_type,igs_ge_date.igsdate(igs_ge_date.igschar(l_start_dt)),l_external_ref);
728 FETCH c_external_hold_exst INTO l_exist;
729
730 IF c_external_hold_exst%NOTFOUND THEN
731 -- External reference does not match
732 l_error := 'IGS_PE_EXT_REF_MISMATCH';
733 message_get(l_error,l_message_name);
734 CLOSE c_external_hold_exst;
735 Wf_Engine.SetItemAttrText(
736 ItemType => itemtype,
737 ItemKey => itemkey,
738 aname => 'P_ERROR',
739 avalue => l_message_name
740 );
741 resultout := 'COMPLETE:F';
742 return;
743 ELSE
744 -- External reference matches
745
746 CLOSE c_external_hold_exst;
747 END IF;
748
749
750 BEGIN
751 l_message_name := NULL;
752
753 igs_pe_gen_001.release_hold
754 (
755 p_resp_id => NULL,
756 p_fnd_user_id => NULL,
757 p_person_id => l_student_rec.person_id,
758 p_encumbrance_type => l_hold_type,
759 p_start_dt => igs_ge_date.igsdate(igs_ge_date.igschar(l_start_dt)),
760 p_expiry_dt => igs_ge_date.igsdate(igs_ge_date.igschar(l_expiry_date)),
761 p_override_resp => 'X',
762 p_message_name => l_message_name
763 );
764
765 IF l_message_name IS NULL THEN
766 --commit;
767 --Hold was released successfully
768 resultout := 'COMPLETE:S';
769
770 --raise the event to communicate it to the external system
771 raise_success_event(l_person_number, l_hold_type, l_start_dt, l_expiry_date);
772
773 return;
774 END IF;
775 EXCEPTION
776
777 WHEN OTHERS THEN
778 --always overriding the value of the l_message_name as it will always be present on the stack
779 -- any TBH excpetion will be present on the stack so get it from there.
780 IGS_GE_MSG_STACK.GET(-1, 'F', l_message_name, ln_msg_index);
781
782 IF l_message_name IS NOT NULL THEN
783
784 --Hold was NOT released successfully
785 Wf_Engine.SetItemAttrText(
786 ItemType => itemtype,
787 ItemKey => itemkey,
788 aname => 'P_ERROR',
789 avalue => l_message_name
790 );
791 resultout := 'COMPLETE:F';
792 return;
793 ELSE
794 l_error := 'IGS_GE_UNHANDLED_EXCEPTION';
795 message_get(l_error,l_message_name);
796 Wf_Engine.SetItemAttrText(
797 ItemType => itemtype,
798 ItemKey => itemkey,
799 aname => 'P_ERROR',
800 avalue => l_message_name
801 );
802 resultout := 'COMPLETE:F';
803 return;
804 END IF;
805
806 END;
807 END IF;
808 END IF; -- funcmode is RUN
809
810 IF funcmode = 'CANCEL' THEN
811
812 resultout := 'COMPLETE' ;
813 return;
814 END IF;
815
816
817 EXCEPTION
818 WHEN OTHERS THEN
819 wf_core.context('IGS_PE_GEN_002', 'IGSPE003' , itemtype, itemkey, to_char(actid), funcmode,'ERROR');
820 RAISE ;
821 END Receive_External_Hold;
822
823 FUNCTION get_hr_installed
824 RETURN VARCHAR2 AS
825 /*************************************************************
826 Created By :npalanis
827 Date Created By :10-JUN-2003
828 Purpose :
829 Know limitations, enhancements or remarks
830 Change History
831 Who When What
832 (reverse chronological order - newest change first)
833 skpandey 12-JAN-2006 Bug#4937960
834 Changed hr_record cursor definition to optimize query
835 ssawhney 17 Aug 3690580, for perf reasons changed cursor hr_record.
836 ***************************************************************/
837 CURSOR hr_status IS
838 SELECT STATUS FROM
839 FND_PRODUCT_INSTALLATIONS
840 WHERE APPLICATION_ID = 800;
841
842 -- ssawhney for perf reasons, using pk>1 rather than rownum
843 CURSOR hr_record IS
844 SELECT 'Y' FROM PER_ALL_ASSIGNMENTS_F WHERE assignment_id > 1 AND ROWNUM = 1;
845
846 l_status FND_PRODUCT_INSTALLATIONS.STATUS%TYPE;
847 l_var VARCHAR2(1);
848 BEGIN
849
850 OPEN hr_status;
851 FETCH hr_status INTO l_status;
852 CLOSE hr_status;
853
854 IF l_status = 'I' THEN
855 OPEN hr_record;
856 FETCH hr_record INTO l_var;
857 IF hr_record%FOUND THEN
858 CLOSE hr_record;
859 RETURN 'Y';
860 ELSE
861 CLOSE hr_record;
862 RETURN 'N';
863 END IF;
864 ELSE
865 RETURN 'N';
866 END IF;
867 END get_hr_installed;
868
869 FUNCTION get_active_emp_cat(P_PERSON_ID IN IGS_PE_TYP_INSTANCES_ALL.PERSON_ID%TYPE)
870 RETURN VARCHAR2 IS
871 /*************************************************************
872 Created By :npalanis
873 Date Created By :10-JUN-2003
874 Purpose :
875 Know limitations, enhancements or remarks
876 Change History
877 Who When What
878 (reverse chronological order - newest change first)
879 ***************************************************************/
880 CURSOR hr_emp_cat(cp_person_id igs_pe_typ_instances_all.person_id%TYPE) IS
881 SELECT emplmnt_category_code FROM
882 IGS_PE_HR_EMP_CAT_V
883 WHERE person_id = cp_person_id;
884
885 CURSOR typ_emp_cat(cp_person_id igs_pe_typ_instances_all.person_id%TYPE) IS
886 SELECT emplmnt_category_code FROM
887 IGS_PE_TYP_EMP_CAT_V
888 WHERE person_id = cp_person_id;
889
890 l_emplmnt_category_code igs_pe_typ_emp_cat_v.emplmnt_category_code%TYPE;
891 BEGIN
892 IF IGS_PE_GEN_002.GET_HR_INSTALLED = 'Y' THEN
893 OPEN hr_emp_cat(p_person_id);
894 FETCH hr_emp_cat INTO l_emplmnt_category_code;
895 CLOSE hr_emp_cat;
896 RETURN l_emplmnt_category_code;
897 ELSIF IGS_PE_GEN_002.GET_HR_INSTALLED = 'N' THEN
898 OPEN typ_emp_cat(p_person_id);
899 FETCH typ_emp_cat INTO l_emplmnt_category_code;
900 CLOSE typ_emp_cat;
901 RETURN l_emplmnt_category_code;
902 END IF;
903 END get_active_emp_cat;
904
905 END igs_pe_gen_002;
[Home] [Help]