1 PACKAGE BODY igs_pe_pers_encumb_pkg AS
2 /* $Header: IGSNI18B.pls 120.0 2005/06/02 04:27:22 appldev noship $ */
3
4 ------------------------------------------------------------------
5 -- Change History
6 --
7 -- Bug ID : 2000408
8 -- who when what
9 -- CDCRUZ Sep 24,2002 New Col's added for
10 -- Person DLD / cal_type , sequence_number added
11
12 ------------------------------------------------------------------
13
14 -- Bug 1956374 msrinivi Repointed genp_val_prsn_id
15 l_rowid VARCHAR2(25);
16 old_references IGS_PE_PERS_ENCUMB%RowType;
17 new_references IGS_PE_PERS_ENCUMB%RowType;
18
19 PROCEDURE Set_Column_Values (
20 p_action IN VARCHAR2,
21 x_rowid IN VARCHAR2,
22 x_comments IN VARCHAR2,
23 x_person_id IN NUMBER,
24 x_encumbrance_type IN VARCHAR2,
25 x_start_dt IN DATE,
26 x_expiry_dt IN DATE,
27 x_authorising_person_id IN NUMBER,
28 x_spo_course_cd IN VARCHAR2,
29 x_spo_sequence_number IN NUMBER,
30 x_cal_type IN VARCHAR2,
31 x_sequence_number IN NUMBER,
32 x_auth_resp_id IN NUMBER,
33 x_external_reference IN VARCHAR2,
34 x_creation_date IN DATE,
35 x_created_by IN NUMBER,
36 x_last_update_date IN DATE,
37 x_last_updated_by IN NUMBER,
38 x_last_update_login IN NUMBER
39 ) AS
40
41 CURSOR cur_old_ref_values IS
42 SELECT *
43 FROM igs_pe_pers_encumb
44 WHERE rowid = x_rowid;
45
46 BEGIN
47
48 l_rowid := x_rowid;
49
50 -- Code for setting the Old and New Reference Values.
51 -- Populate Old Values.
52 Open cur_old_ref_values;
53 Fetch cur_old_ref_values INTO old_references;
54 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
55 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
56 IGS_GE_MSG_STACK.ADD;
57 Close cur_old_ref_values;
58 App_Exception.Raise_Exception;
59 Return;
60 END IF;
61 Close cur_old_ref_values;
62
63 -- Populate New Values.
64 new_references.comments := x_comments;
65 new_references.person_id := x_person_id;
66 new_references.encumbrance_type := x_encumbrance_type;
67 new_references.start_dt := x_start_dt;
68 new_references.expiry_dt := x_expiry_dt;
69 new_references.authorising_person_id := x_authorising_person_id;
70 new_references.spo_course_cd := x_spo_course_cd;
71 new_references.spo_sequence_number := x_spo_sequence_number;
72 new_references.cal_type := x_cal_type ;
73 new_references.sequence_number := x_sequence_number ;
74 new_references.auth_resp_id := x_auth_resp_id ;
75 new_references.external_reference := x_external_reference;
76
77 IF (p_action = 'UPDATE') THEN
78 new_references.creation_date := old_references.creation_date;
79 new_references.created_by := old_references.created_by;
80 ELSE
81 new_references.creation_date := x_creation_date;
82 new_references.created_by := x_created_by;
83 END IF;
84 new_references.last_update_date := x_last_update_date;
85 new_references.last_updated_by := x_last_updated_by;
86 new_references.last_update_login := x_last_update_login;
87
88 END Set_Column_Values;
89
90 PROCEDURE BeforeRowInsertUpdate1(
91 p_inserting IN BOOLEAN,
92 p_updating IN BOOLEAN,
93 p_deleting IN BOOLEAN
94 ) AS
95 /*------------------------------------------------------------------
96 Change History
97
98 Bug ID : 2000408
99 who when what
100 PKPATEL 8-APR-2003 Bug No: 2804863, Added the check with igs_pe_gen_001.g_hold_validation for calling
101 igs_pe_gen_001.get_hold_auth
102 Added system_type_rec.s_encumbrance_cat = 'ACADEMIC' check to validate staff and person ID.
103 vkarthik 16-Jul-2004 Added validation on hold start date and hold expiry date as part of Bug 3771317
104 ------------------------------------------------------------------*/
105 CURSOR cur_hold_ovr IS
106 SELECT hold_old_end_dt
107 FROM igs_pe_hold_rel_ovr hovr, igs_en_elgb_ovr_all ovr
108 WHERE ovr.elgb_override_id =hovr.elgb_override_id AND
109 ovr.person_id = new_references.person_id AND new_references.start_dt = hovr.start_date
110 AND new_references.encumbrance_type = hovr.hold_type;
111
112 CURSOR system_type_cur(cp_encumbrance_type igs_fi_encmb_type.encumbrance_type%TYPE) IS
113 SELECT s_encumbrance_cat
114 FROM igs_fi_encmb_type
115 WHERE encumbrance_type = cp_encumbrance_type;
116
117 system_type_rec system_type_cur%ROWTYPE;
118
119 l_person_id hz_parties.party_id%TYPE;
120 l_person_number hz_parties.party_number%TYPE;
121 l_person_name hz_person_profiles.person_name%TYPE;
122 l_fnd_user_id fnd_user.user_id%TYPE;
123
124 l_hold_old_end_date DATE;
125 v_message_name VARCHAR2(30);
126 BEGIN
127 -- Validate ENCUMBRANCE TYPE.
128 -- Closed indicator.
129
130 l_fnd_user_id := FND_GLOBAL.USER_ID;
131 IF new_references.encumbrance_type IS NOT NULL AND
132 (NVL(old_references.encumbrance_type , 'NULL') <> new_references.encumbrance_type ) THEN
133
134 IF igs_en_val_etde.enrp_val_et_closed (
135 new_references.encumbrance_type ,
136 v_message_name) = FALSE THEN
137
138 Fnd_Message.Set_Name('IGS', v_message_name);
139 IGS_GE_MSG_STACK.ADD;
140 APP_EXCEPTION.RAISE_EXCEPTION;
141 END IF;
142 END IF;
143 -- expiry dt, start dt validation added as part of Bug 3771317
144 IF new_references.start_dt IS NOT NULL AND
145 new_references.expiry_dt IS NOT NULL AND
146 new_references.expiry_dt < new_references.start_dt THEN
147 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_EXPDT_GE_STDT');
148 IGS_GE_MSG_STACK.ADD;
149 APP_EXCEPTION.RAISE_EXCEPTION;
150 END IF;
151 IF p_inserting THEN
152
153 IF igs_en_val_pen.enrp_val_prsn_encmb (
154 new_references.person_id,
155 new_references.encumbrance_type ,
156 new_references.start_dt,
157 new_references.expiry_dt,
158 v_message_name) = FALSE THEN
159
160 FND_MESSAGE.SET_NAME('IGS', v_message_name);
161 IGS_GE_MSG_STACK.ADD;
162 APP_EXCEPTION.RAISE_EXCEPTION;
163 END IF;
164
165 OPEN system_type_cur(new_references.encumbrance_type);
166 FETCH system_type_cur INTO system_type_rec;
167 CLOSE system_type_cur;
168
169
170 --KUMMA, 2758856, Added the condititon to check for the external_reference also.
171 IF system_type_rec.s_encumbrance_cat = 'ADMIN' AND new_references.external_reference IS NULL THEN
172
173 IF igs_pe_gen_001.g_hold_validation = 'Y' THEN
174
175 igs_pe_gen_001.get_hold_auth(l_fnd_user_id,
176 l_person_id,
177 l_person_number,
178 l_person_name,
179 v_message_name);
180
181 IF v_message_name IS NOT NULL THEN
182
183 FND_MESSAGE.SET_NAME('IGS',v_message_name);
184 IGS_GE_MSG_STACK.ADD;
185 APP_EXCEPTION.RAISE_EXCEPTION;
186 ELSE
187
188 new_references.authorising_person_id := l_person_id;
189 new_references.auth_resp_id := FND_GLOBAL.RESP_ID;
190 END IF;
191 END IF;
192 END IF;
193
194 END IF;
195
196 -- Validate that start date is not less than the current date.
197 IF (new_references.start_dt IS NOT NULL) AND
198 (p_inserting OR (p_updating AND
199 (NVL(old_references.start_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
200 <> new_references.start_dt)))
201 THEN
202
203 IF IGS_EN_VAL_PCE.enrp_val_encmb_dt (
204 new_references.start_dt,
205 v_message_name) = FALSE THEN
206
207 Fnd_Message.Set_Name('IGS', v_message_name);
208 IGS_GE_MSG_STACK.ADD;
209 App_Exception.Raise_Exception;
210 END IF;
211
212 END IF;
213 -- Validate that if expiry date is specified, then expiry date is not
214 -- less than the start date or less than the current date.
215
216 OPEN cur_hold_ovr;
217 FETCH cur_hold_ovr INTO l_hold_old_end_date;
218
219 IF cur_hold_ovr%NOTFOUND THEN
220 l_hold_old_end_date := new_references.expiry_dt+1;
221 END IF;
222 CLOSE cur_hold_ovr;
223 IF new_references.expiry_dt <> l_hold_old_end_date THEN
224 IF (new_references.expiry_dt IS NOT NULL) AND
225 (p_inserting OR (p_updating AND
226 (NVL(old_references.expiry_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
227 <> new_references.expiry_dt)))
228 THEN
229 IF IGS_EN_VAL_PCE.enrp_val_strt_exp_dt (
230 new_references.start_dt,
231 new_references.expiry_dt,
232 v_message_name) = FALSE THEN
233 Fnd_Message.Set_Name('IGS', v_message_name);
234 IGS_GE_MSG_STACK.ADD;
235 App_Exception.Raise_Exception;
236 END IF;
237 IF IGS_EN_VAL_PCE.enrp_val_encmb_dt (
238 new_references.expiry_dt,
239 v_message_name) = FALSE THEN
240 Fnd_Message.Set_Name('IGS', v_message_name);
241 IGS_GE_MSG_STACK.ADD;
242 App_Exception.Raise_Exception;
243 END IF;
244 END IF;
245 END IF;
246
247 -- Validate Encumbrance Authorising IGS_PE_PERSON Id.
248 -- Validate that the authorising person_id is valid and is a staff member.
249 -- Validation is done only for ACADEMIC holds. ADMIN hold this validation is done in the igs_pe_gen_001.get_hold_auth procedure.
250 IF system_type_rec.s_encumbrance_cat = 'ACADEMIC' AND
251 (new_references.authorising_person_id IS NOT NULL) AND
252 (p_inserting OR
253 ( p_updating AND (new_references.authorising_person_id <> NVL(old_references.authorising_person_id,-1))))
254 THEN
255
256 IF IGS_CO_VAL_OC.genp_val_prsn_id (
257 new_references.authorising_person_id,
258 v_message_name) = FALSE THEN
259 FND_MESSAGE.SET_NAME('IGS', v_message_name);
260 IGS_GE_MSG_STACK.ADD;
261 APP_EXCEPTION.RAISE_EXCEPTION;
262 END IF;
263
264 IF igs_ad_val_acai.genp_val_staff_prsn (
265 new_references.authorising_person_id,
266 v_message_name) = FALSE THEN
267 FND_MESSAGE.SET_NAME('IGS', v_message_name);
268 IGS_GE_MSG_STACK.ADD;
269 APP_EXCEPTION.RAISE_EXCEPTION;
270 END IF;
271 END IF;
272
273
274 END BeforeRowInsertUpdate1;
275
276 -- Trigger description :-
277 -- "OSS_TST".trg_pen_ar_iu
278 -- AFTER INSERT OR UPDATE
279 -- ON IGS_PE_PERS_ENCUMB
280 -- FOR EACH ROW
281
282 PROCEDURE AfterRowInsertUpdate2(
283 p_inserting IN BOOLEAN,
284 p_updating IN BOOLEAN,
285 p_deleting IN BOOLEAN
286 ) AS
287 v_message_name varchar2(30);
288 v_rowid_saved BOOLEAN := FALSE;
289 l_message_name varchar2(2000);
290 l_app number ;
291 ln_msg_index number;
292 BEGIN
293 -- Validate for open ended IGS_PE_PERSON encumbrance records.
294 IF new_references.expiry_dt IS NULL THEN
295
296 -- Save the rowid of the current row.
297 v_rowid_saved := TRUE;
298 -- Cannot call enrp_val_pen_open because trigger will be mutating.
299 END IF;
300 IF p_inserting THEN
301 -- Cannot call IGS_EN_GEN_009.ENRP_INS_DFLT_EFFECT because trigger will be mutating.
302 -- Save the rowid of the current row.
303 IF v_rowid_saved = FALSE THEN
304
305 v_rowid_saved := TRUE;
306 END IF;
307 END IF;
308 IF p_updating AND
309 (NVL(old_references.expiry_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
310 NVL(new_references.expiry_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))) THEN
311 -- Cannot call enrp_set_expiry_dts because trigger will be mutating.
312 -- Save the rowid of the current row.
313 IF v_rowid_saved = FALSE THEN
314
315 v_rowid_saved := TRUE;
316 END IF;
317 END IF;
318 IF v_rowid_saved = TRUE THEN
319 --Validate the records
320 -- Validate for open ended IGS_PE_PERS_ENCUMB records.
321 IF new_references.expiry_dt IS NULL THEN
322 IF IGS_EN_VAL_PEN.enrp_val_pen_open (
323 new_references.person_id,
324 new_references.encumbrance_type,
325 new_references.start_dt,
326 v_message_name) = FALSE THEN
327
328 Fnd_Message.Set_Name('IGS', v_message_name);
329 IGS_GE_MSG_STACK.ADD;
330 App_Exception.Raise_Exception;
331 END IF;
332 END IF;
333 -- Insert the default effects for the encumbrance type.
334 -- Removed as the message needs to be a warning not an error. ie processing
335 -- needs to proceed.
336
337 -- Validate that insert will not cause invalid effect combinations.
338 IF p_inserting THEN
339 IF IGS_EN_VAL_PEN.enrp_val_prsn_encmb (new_references.person_id,
340 new_references.encumbrance_type,
341 new_references.start_dt,
342 new_references.expiry_dt,
343 v_message_name) = FALSE THEN
344 Fnd_Message.Set_Name('IGS', v_message_name);
345 IGS_GE_MSG_STACK.ADD;
346 App_Exception.Raise_Exception;
347 END IF;
348 END IF;
349 -- Set the expiry date of all child records if the expiry date has been
350 -- updated.
351 IF p_updating AND
352 (new_references.expiry_dt IS NOT NULL) THEN
353
354 initialised := 'E';
355
356 IGS_EN_GEN_012.ENRP_UPD_EXPIRY_DTS (new_references.person_id,
357 new_references.encumbrance_type,
358 new_references.start_dt,
359 new_references.expiry_dt,
360 v_message_name);
361
362 initialised := NULL;
363
364 IF v_message_name <> 0 THEN
365 FND_MESSAGE.SET_NAME('IGS', v_message_name);
366 IGS_GE_MSG_STACK.ADD;
367 APP_EXCEPTION.RAISE_EXCEPTION;
368 END IF;
369 END IF;
370 END IF;
371
372 END AfterRowInsertUpdate2;
373
374 -- Trigger description :-
375 -- "OSS_TST".trg_pen_as_iu
376 -- AFTER INSERT OR UPDATE
377 -- ON IGS_PE_PERS_ENCUMB
378
379
380
381 PROCEDURE Check_Constraints (
382 Column_Name IN VARCHAR2,
383 Column_Value IN VARCHAR2
384 )
385 AS
386 BEGIN
387 IF column_name is null then
388 NULL;
389 ELSIF upper(Column_name) = 'ENCUMBRANCE_TYPE' then
390 new_references.encumbrance_type:= column_value;
391 END IF;
392 IF upper(column_name) = 'ENCUMBRANCE_TYPE' OR
393 column_name is null THEN
394 IF new_references.encumbrance_type <>
395 UPPER(new_references.encumbrance_type) THEN
396 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
397 IGS_GE_MSG_STACK.ADD;
398 App_Exception.Raise_Exception;
399 END IF;
400 END IF;
401
402 END Check_Constraints;
403
404 PROCEDURE Check_Parent_Existance AS
405 -- pkpatel,Bug 4163187 (Modified the cursors to remove the hard cording of application id to 8405/8406)
406 CURSOR check_resp_id_cur (cp_appl_id fnd_responsibility.application_id%TYPE,
407 cp_resp_id fnd_responsibility.responsibility_id%TYPE) IS
408 SELECT 'X'
409 FROM fnd_responsibility
410 WHERE application_id = cp_appl_id AND
411 responsibility_id = cp_resp_id;
412
413 CURSOR check_applresp_id_cur (cp_resp_id fnd_responsibility.responsibility_id%TYPE) IS
414 SELECT 'X'
415 FROM fnd_responsibility resp, fnd_application appl
416 WHERE resp.application_id = appl.application_id AND
417 responsibility_id = cp_resp_id;
418
419 --kumma, 2758856, added the following cursor
420 CURSOR check_ext_reference (p_lookup_type fnd_lookup_values_vl.lookup_type%TYPE,
421 p_lookup_code fnd_lookup_values_vl.lookup_code%TYPE,
422 p_view_application_id fnd_lookup_values_vl.view_application_id%TYPE,
423 p_security_group_id fnd_lookup_values_vl.security_group_id%TYPE) IS
424 SELECT 'X'
425 FROM fnd_lookup_values_vl
426 WHERE lookup_code = p_lookup_code AND
427 lookup_type = p_lookup_type AND
428 view_application_id = p_view_application_id AND
429 security_group_id = p_security_group_id AND
430 NVL(enabled_flag,'Y') = 'Y';
431
432 l_var VARCHAR2(1);
433 l_appl_id fnd_responsibility.application_id%TYPE;
434 BEGIN
435
436 IF (((old_references.auth_resp_id = new_references.auth_resp_id )) OR
437 ((new_references.AUTH_RESP_ID IS NULL))) THEN
438 NULL;
439 ELSE
440 l_appl_id := FND_GLOBAL.RESP_APPL_ID;
441
442 IF l_appl_id <> -1 THEN
443 OPEN check_resp_id_cur(l_appl_id,new_references.auth_resp_id);
444 FETCH check_resp_id_cur INTO l_var;
445 IF check_resp_id_cur%NOTFOUND THEN
446 CLOSE check_resp_id_cur;
447 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
448 IGS_GE_MSG_STACK.ADD;
449 APP_EXCEPTION.RAISE_EXCEPTION;
450 END IF;
451 CLOSE check_resp_id_cur;
452 ELSE
453 OPEN check_applresp_id_cur(new_references.auth_resp_id);
454 FETCH check_applresp_id_cur INTO l_var;
455 IF check_applresp_id_cur%NOTFOUND THEN
456 CLOSE check_applresp_id_cur;
457 FND_MESSAGE.SET_NAME ('FND', 'FORM_RECORD_DELETED');
458 IGS_GE_MSG_STACK.ADD;
459 APP_EXCEPTION.RAISE_EXCEPTION;
460 END IF;
461 CLOSE check_applresp_id_cur;
462 END IF;
463 END IF;
464
465 --kumma, 2758856, added the following cursor
466 IF (((old_references.external_reference = new_references.external_reference)) OR
467 ((new_references.external_reference IS NULL))) THEN
468 NULL;
469 ELSE
470 OPEN check_ext_reference('PE_EXT_REF',new_references.external_reference,8405,0);
471 FETCH check_ext_reference INTO l_var;
472 IF check_ext_reference%NOTFOUND THEN
473 CLOSE check_ext_reference;
474 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PE_INVLD_EXT_REF_VAL');
475 IGS_GE_MSG_STACK.ADD;
476 APP_EXCEPTION.RAISE_EXCEPTION;
477 END IF;
478 CLOSE check_ext_reference;
479 END IF;
480
481
482 IF (((old_references.encumbrance_type = new_references.encumbrance_type )) OR
483 ((new_references.encumbrance_type IS NULL))) THEN
484 NULL;
485 ELSE
486 IF NOT IGS_FI_ENCMB_TYPE_PKG.Get_PK_For_Validation (
487 new_references.encumbrance_type ) THEN
488 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
489 IGS_GE_MSG_STACK.ADD;
490 App_Exception.Raise_Exception;
491 END IF;
492 END IF;
493
494
495 IF (((old_references.authorising_person_id = new_references.authorising_person_id)) OR
496 ((new_references.authorising_person_id IS NULL))) THEN
497 NULL;
498 ELSE
499 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
500 new_references.authorising_person_id ) THEN
501 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
502 IGS_GE_MSG_STACK.ADD;
503 App_Exception.Raise_Exception;
504 END IF;
505 END IF;
506
507 IF (((old_references.person_id = new_references.person_id)) OR
508 ((new_references.person_id IS NULL))) THEN
509 NULL;
510 ELSE
511 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
512 new_references.person_id ) THEN
513 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
514 IGS_GE_MSG_STACK.ADD;
515 App_Exception.Raise_Exception;
516 END IF;
517 END IF;
518
519 IF (((old_references.cal_type = new_references.cal_type) AND
520 (old_references.sequence_number = new_references.sequence_number)) OR
521 ((new_references.cal_type IS NULL) OR
522 (new_references.sequence_number IS NULL))) THEN
523 NULL;
524 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
525 new_references.cal_type,
526 new_references.sequence_number
527 ) THEN
528
529 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
530 igs_ge_msg_stack.add;
531 app_exception.raise_exception;
532 END IF;
533
534 IF ((
535 old_references.person_id = new_references.person_id
536 AND old_references.spo_course_cd = new_references.spo_course_cd
537 AND old_references.spo_sequence_number = new_references.spo_sequence_number )
538 OR ((new_references.cal_type IS NULL) OR (new_references.spo_course_cd IS NULL) OR (new_references.spo_sequence_number IS NULL))
539 )
540 THEN
541 NULL;
542 ELSIF NOT IGS_PR_STDNT_PR_OU_PKG.get_pk_for_validation (
543 new_references.person_id ,
544 new_references.spo_course_cd,
545 new_references.spo_sequence_number
546 ) THEN
547
548 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
549 igs_ge_msg_stack.add;
550 app_exception.raise_exception;
551 END IF;
552 END Check_Parent_Existance;
553
554 PROCEDURE Check_Child_Existance AS
555 BEGIN
556
557 IGS_PE_PERSENC_EFFCT_PKG.GET_FK_IGS_PE_PERS_ENCUMB (
558 old_references.person_id,
559 old_references.encumbrance_type ,
560 old_references.start_dt
561 );
562
563 END Check_Child_Existance;
564
565 FUNCTION Get_PK_For_Validation (
566 x_person_id IN NUMBER,
567 x_encumbrance_type IN VARCHAR2,
568 x_start_dt IN DATE
569 ) RETURN BOOLEAN AS
570
571 CURSOR cur_rowid IS
572 SELECT rowid
573 FROM IGS_PE_PERS_ENCUMB
574 WHERE person_id = x_person_id
575 AND encumbrance_type = x_encumbrance_type
576 AND start_dt = x_start_dt
577 FOR UPDATE NOWAIT;
578
579 lv_rowid cur_rowid%RowType;
580
581 BEGIN
582
583 OPEN cur_rowid;
584 FETCH cur_rowid INTO lv_rowid;
585 IF (cur_rowid%FOUND) THEN
586 CLOSE cur_rowid;
587 RETURN (TRUE);
588 ELSE
589 CLOSE cur_rowid;
590 RETURN (FALSE);
591 END IF;
592
593 END Get_PK_For_Validation;
594
595 PROCEDURE GET_FK_IGS_CA_INST (
596 x_cal_type IN VARCHAR2,
597 x_ci_sequence_number IN NUMBER
598 ) AS
599 CURSOR cur_rowid IS
600 SELECT rowid
601 FROM IGS_PE_PERS_ENCUMB
602 WHERE (
603 CAL_TYPE = X_CAL_TYPE AND SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
604 );
605
606 lv_rowid cur_rowid%RowType;
607 BEGIN
608 Open cur_rowid;
609 Fetch cur_rowid INTO lv_rowid;
610 IF (cur_rowid%FOUND) THEN
611 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PEN_CI_FK');
612 Igs_Ge_Msg_Stack.Add;
613 Close cur_rowid;
614 App_Exception.Raise_Exception;
615 Return;
616 END IF;
617 Close cur_rowid;
618 END GET_FK_IGS_CA_INST;
619
620 PROCEDURE GET_FK_IGS_FI_ENCMB_TYPE (
621 x_encumbrance_type IN VARCHAR2
622 ) AS
623
624 CURSOR cur_rowid IS
625 SELECT rowid
626 FROM IGS_PE_PERS_ENCUMB
627 WHERE encumbrance_type = x_encumbrance_type ;
628
629 lv_rowid cur_rowid%RowType;
630
631 BEGIN
632
633 OPEN cur_rowid;
634 FETCH cur_rowid INTO lv_rowid;
635 IF (cur_rowid%FOUND) THEN
636 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PEN_ET_FK');
637 IGS_GE_MSG_STACK.ADD;
638 Close cur_rowid;
639 App_Exception.Raise_Exception;
640 RETURN;
641 END IF;
642 Close cur_rowid;
643
644 END GET_FK_IGS_FI_ENCMB_TYPE;
645
646 PROCEDURE GET_FK_IGS_PE_PERSON (
647 x_person_id IN NUMBER
648 ) AS
649
650 CURSOR cur_rowid IS
651 SELECT rowid
652 FROM IGS_PE_PERS_ENCUMB
653 WHERE authorising_person_id = x_person_id OR
654 person_id = x_person_id ;
655
656 lv_rowid cur_rowid%RowType;
657
658 BEGIN
659
660 OPEN cur_rowid;
661 FETCH cur_rowid INTO lv_rowid;
662 IF (cur_rowid%FOUND) THEN
663 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PEN_PE_AUTHORISED_BY_FK');
664 IGS_GE_MSG_STACK.ADD;
665 CLOSE cur_rowid;
666 App_Exception.Raise_Exception;
667 RETURN;
668 END IF;
669 Close cur_rowid;
670
671 END GET_FK_IGS_PE_PERSON;
672
673 PROCEDURE Before_DML (
674 p_action IN VARCHAR2,
675 x_rowid IN VARCHAR2,
676 x_comments IN VARCHAR2,
677 x_person_id IN NUMBER,
678 x_encumbrance_type IN VARCHAR2,
679 x_start_dt IN DATE,
680 x_expiry_dt IN DATE,
681 x_authorising_person_id IN NUMBER,
682 x_spo_course_cd IN VARCHAR2,
683 x_spo_sequence_number IN NUMBER,
684 X_CAL_TYPE IN VARCHAR2,
685 X_SEQUENCE_NUMBER IN NUMBER,
686 x_auth_resp_id IN NUMBER,
687 X_EXTERNAL_REFERENCE IN VARCHAR2 ,
688 x_creation_date IN DATE,
689 x_created_by IN NUMBER,
690 x_last_update_date IN DATE,
691 x_last_updated_by IN NUMBER,
692 x_last_update_login IN NUMBER
693 ) AS
694 BEGIN
695 Set_Column_Values (
696 p_action,
697 x_rowid,
698 x_comments,
699 x_person_id,
700 x_encumbrance_type,
701 x_start_dt,
702 x_expiry_dt,
703 x_authorising_person_id,
704 x_spo_course_cd,
705 x_spo_sequence_number,
706 x_cal_type,
707 x_sequence_number,
708 x_auth_resp_id,
709 x_external_reference,
710 x_creation_date,
711 x_created_by,
712 x_last_update_date,
713 x_last_updated_by,
714 x_last_update_login
715 );
716
717 IF (p_action = 'INSERT') THEN
718 -- Call all the procedures related to Before Insert.
719
720 BeforeRowInsertUpdate1 (
721 p_inserting => TRUE,
722 p_updating => FALSE,
723 p_deleting => FALSE);
724
725 IF Get_PK_For_Validation (
726 new_references.person_id,
727 new_references.encumbrance_type ,
728 new_references.start_dt) THEN
729 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
730 IGS_GE_MSG_STACK.ADD;
731 App_Exception.Raise_Exception;
732 END IF;
733
734 Check_Constraints; -- if procedure present
735
736 Check_Parent_Existance; -- if procedure present
737
738 ELSIF (p_action = 'UPDATE') THEN
739
740 -- Call all the procedures related to Before Update.
741 BeforeRowInsertUpdate1 (
742 p_inserting => FALSE,
743 p_updating => TRUE,
744 p_deleting => FALSE );
745
746 Check_Constraints; -- if procedure present
747
748 Check_Parent_Existance; -- if procedure present
749
750 ELSIF (p_action = 'DELETE') THEN
751 -- Call all the procedures related to Before Delete.
752
753 Check_Child_Existance; -- if procedure present
754 ELSIF (p_action = 'VALIDATE_INSERT') THEN
755
756 IF Get_PK_For_Validation (
757 new_references.person_id,
758 new_references.encumbrance_type ,
759 new_references.start_dt) THEN
760 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
761 IGS_GE_MSG_STACK.ADD;
762 App_Exception.Raise_Exception;
763 END IF;
764
765 Check_Constraints; -- if procedure present
766 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
767 Check_Constraints; -- if procedure present
768
769 ELSIF (p_action = 'VALIDATE_DELETE') THEN
770 Check_Child_Existance; -- if procedure present
771 END IF;
772
773 END Before_DML;
774
775 PROCEDURE After_DML (
776 p_action IN VARCHAR2,
777 x_rowid IN VARCHAR2
778 ) AS
779 BEGIN
780
781 l_rowid := x_rowid;
782
783 IF (p_action = 'INSERT') THEN
784 -- Call all the procedures related to After Insert.
785
786 AfterRowInsertUpdate2 (
787 p_inserting => TRUE,
788 p_updating => FALSE,
789 p_deleting => FALSE);
790
791
792 ELSIF (p_action = 'UPDATE') THEN
793 -- Call all the procedures related to After Update.
794 AfterRowInsertUpdate2 (
795 p_inserting => FALSE,
796 p_updating => TRUE,
797 p_deleting => FALSE );
798
799 ELSIF (p_action = 'DELETE') THEN
800 -- Call all the procedures related to After Delete.
801 Null;
802 END IF;
803
804 END After_DML;
805
806 procedure INSERT_ROW (
807 X_ROWID in out NOCOPY VARCHAR2,
808 X_PERSON_ID in NUMBER,
809 X_ENCUMBRANCE_TYPE in VARCHAR2,
810 X_START_DT in DATE,
811 X_EXPIRY_DT in DATE,
812 X_AUTHORISING_PERSON_ID in NUMBER,
813 X_COMMENTS in VARCHAR2,
814 X_SPO_COURSE_CD in VARCHAR2,
815 X_SPO_SEQUENCE_NUMBER in NUMBER,
816 X_CAL_TYPE IN VARCHAR2,
817 X_SEQUENCE_NUMBER IN NUMBER,
818 x_auth_resp_id IN NUMBER,
819 x_external_reference IN VARCHAR2 ,
820 X_MODE IN VARCHAR2
821 ) AS
822 CURSOR C (cp_start_dt igs_pe_pers_encumb.start_dt%TYPE) IS
823 SELECT ROWID FROM igs_pe_pers_encumb
824 WHERE person_id = x_person_id
825 AND encumbrance_type = x_encumbrance_type
826 AND start_dt = cp_start_dt;
827
828 CURSOR system_type_cur(cp_encumbrance_type igs_fi_encmb_type.encumbrance_type%TYPE) IS
829 SELECT s_encumbrance_cat
830 FROM igs_fi_encmb_type
831 WHERE encumbrance_type = cp_encumbrance_type;
832 system_type_rec system_type_cur%ROWTYPE;
833
834 X_LAST_UPDATE_DATE DATE;
835 X_LAST_UPDATED_BY NUMBER;
836 X_LAST_UPDATE_LOGIN NUMBER;
837 BEGIN
838 x_last_update_date := SYSDATE;
839 IF(x_mode = 'I') THEN
840 x_last_updated_by := 1;
841 x_last_update_login := 0;
842 ELSIF (x_mode = 'R') THEN
843 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
844 IF x_last_updated_by IS NULL THEN
845 X_LAST_UPDATED_BY := -1;
846 END IF;
847 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
848 if X_LAST_UPDATE_LOGIN is NULL then
849 X_LAST_UPDATE_LOGIN := -1;
850 end if;
851 else
852 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
853 IGS_GE_MSG_STACK.ADD;
854 app_exception.raise_exception;
855 end if;
856 OPEN system_type_cur(x_encumbrance_type);
857 FETCH system_type_cur INTO system_type_rec;
858 CLOSE system_type_cur;
859 IF system_type_rec.s_encumbrance_cat = 'ADMIN' THEN
860 /* asbala: 3446073 - ACAD type holds will have the time component in the start and end dates,
861 ADMIN type holds will not. */
862 Before_DML(
863 p_action=>'INSERT',
864 x_rowid=>X_ROWID,
865 x_authorising_person_id=>X_AUTHORISING_PERSON_ID,
866 x_comments=>X_COMMENTS,
867 x_encumbrance_type=>X_ENCUMBRANCE_TYPE,
868 x_expiry_dt=>TRUNC(X_EXPIRY_DT),
869 x_person_id=>X_PERSON_ID,
870 x_start_dt=>TRUNC(X_START_DT),
871 x_spo_course_cd=>X_SPO_COURSE_CD,
872 x_spo_sequence_number=>X_SPO_SEQUENCE_NUMBER,
873 X_CAL_TYPE => X_CAL_TYPE ,
874 X_SEQUENCE_NUMBER => X_SEQUENCE_NUMBER ,
875 x_auth_resp_id => x_auth_resp_id ,
876 x_external_reference => x_external_reference,
877 x_creation_date=>X_LAST_UPDATE_DATE,
878 x_created_by=>X_LAST_UPDATED_BY,
879 x_last_update_date=>X_LAST_UPDATE_DATE,
880 x_last_updated_by=>X_LAST_UPDATED_BY,
881 x_last_update_login=>X_LAST_UPDATE_LOGIN
882 );
883 ELSE
884 Before_DML(
885 p_action=>'INSERT',
886 x_rowid=>X_ROWID,
887 x_authorising_person_id=>X_AUTHORISING_PERSON_ID,
888 x_comments=>X_COMMENTS,
889 x_encumbrance_type=>X_ENCUMBRANCE_TYPE,
890 x_expiry_dt=>X_EXPIRY_DT,
891 x_person_id=>X_PERSON_ID,
892 x_start_dt=>X_START_DT,
893 x_spo_course_cd=>X_SPO_COURSE_CD,
894 x_spo_sequence_number=>X_SPO_SEQUENCE_NUMBER,
895 X_CAL_TYPE => X_CAL_TYPE ,
896 X_SEQUENCE_NUMBER => X_SEQUENCE_NUMBER ,
897 x_auth_resp_id => x_auth_resp_id ,
898 x_external_reference => x_external_reference,
899 x_creation_date=>X_LAST_UPDATE_DATE,
900 x_created_by=>X_LAST_UPDATED_BY,
901 x_last_update_date=>X_LAST_UPDATE_DATE,
902 x_last_updated_by=>X_LAST_UPDATED_BY,
903 x_last_update_login=>X_LAST_UPDATE_LOGIN
904 );
905 END IF;
906 INSERT INTO igs_pe_pers_encumb (
907 person_id,
908 encumbrance_type,
909 start_dt,
910 expiry_dt,
911 authorising_person_id,
912 comments,
913 spo_course_cd,
914 spo_sequence_number,
915 cal_type ,
916 sequence_number ,
917 auth_resp_id,
918 external_reference,
919 creation_date,
920 created_by,
921 last_update_date,
922 last_updated_by,
923 last_update_login
924 ) VALUES (
925 new_references.person_id,
926 new_references.encumbrance_type,
927 new_references.start_dt,
928 new_references.expiry_dt,
929 new_references.authorising_person_id,
930 new_references.comments,
931 new_references.spo_course_cd,
932 new_references.spo_sequence_number,
933 new_references.cal_type,
934 new_references.sequence_number ,
935 new_references.auth_resp_id ,
936 new_references.external_reference,
937 x_last_update_date,
938 x_last_updated_by,
939 x_last_update_date,
940 x_last_updated_by,
941 x_last_update_login
942 );
943
944 OPEN c(new_references.start_dt);
945 FETCH c INTO x_rowid;
946 IF (c%NOTFOUND) THEN
947 CLOSE c;
948 RAISE no_data_found;
949 END IF;
950 CLOSE c;
951
952 After_DML(
953 p_action => 'INSERT',
954 x_rowid => X_ROWID
955 );
956 END insert_row;
957
958 PROCEDURE LOCK_ROW (
959 X_ROWID in VARCHAR2,
960 X_PERSON_ID in NUMBER,
961 X_ENCUMBRANCE_TYPE in VARCHAR2,
962 X_START_DT in DATE,
963 X_EXPIRY_DT in DATE,
964 X_AUTHORISING_PERSON_ID in NUMBER,
965 x_spo_course_cd in VARCHAR2,
966 x_spo_sequence_number in NUMBER,
967 X_COMMENTS in VARCHAR2,
968 X_CAL_TYPE IN VARCHAR2,
969 X_SEQUENCE_NUMBER IN NUMBER,
970 x_auth_resp_id IN NUMBER,
971 X_EXTERNAL_REFERENCE IN VARCHAR2
972 ) AS
973
974 CURSOR c1 IS SELECT
975 expiry_dt,
976 authorising_person_id,
977 comments
978 FROM igs_pe_pers_encumb
979 WHERE ROWID = X_ROWID
980 FOR UPDATE NOWAIT;
981 tlinfo c1%ROWTYPE;
982
983 BEGIN
984 OPEN c1;
985 FETCH c1 INTO tlinfo;
986 IF (c1%NOTFOUND) THEN
987 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
988 CLOSE c1;
989 App_Exception.Raise_Exception;
990 RETURN;
991 END IF;
992 CLOSE c1;
993
994 IF ( ((tlinfo.EXPIRY_DT = X_EXPIRY_DT)
995 OR ((tlinfo.EXPIRY_DT IS NULL)
996 AND (X_EXPIRY_DT IS NULL)))
997 AND ((tlinfo.AUTHORISING_PERSON_ID = X_AUTHORISING_PERSON_ID)
998 OR ((tlinfo.AUTHORISING_PERSON_ID IS NULL)
999 AND (X_AUTHORISING_PERSON_ID IS NULL)))
1000 AND ((tlinfo.COMMENTS = X_COMMENTS)
1001 OR ((tlinfo.COMMENTS IS NULL)
1002 AND (X_COMMENTS IS NULL)))
1003 ) THEN
1004 NULL;
1005 ELSE
1006 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1007 app_exception.raise_exception;
1008 END IF;
1009 RETURN;
1010 END lock_row;
1011
1012 PROCEDURE UPDATE_ROW (
1013 X_ROWID in VARCHAR2,
1014 X_PERSON_ID in NUMBER,
1015 X_ENCUMBRANCE_TYPE in VARCHAR2,
1016 X_START_DT in DATE,
1017 X_EXPIRY_DT in DATE,
1018 X_AUTHORISING_PERSON_ID in NUMBER,
1019 X_COMMENTS in VARCHAR2,
1020 x_spo_course_cd in VARCHAR2,
1021 x_spo_sequence_number in NUMBER,
1022 X_CAL_TYPE IN VARCHAR2,
1023 X_SEQUENCE_NUMBER IN NUMBER,
1024 x_auth_resp_id IN NUMBER,
1025 X_EXTERNAL_REFERENCE IN VARCHAR2 ,
1026 X_MODE in VARCHAR2
1027 ) AS
1028 X_LAST_UPDATE_DATE DATE;
1029 X_LAST_UPDATED_BY NUMBER;
1030 X_LAST_UPDATE_LOGIN NUMBER;
1031 CURSOR system_type_cur(cp_encumbrance_type igs_fi_encmb_type.encumbrance_type%TYPE) IS
1032 SELECT s_encumbrance_cat
1033 FROM igs_fi_encmb_type
1034 WHERE encumbrance_type = cp_encumbrance_type;
1035 system_type_rec system_type_cur%ROWTYPE;
1036
1037 BEGIN
1038 X_LAST_UPDATE_DATE := SYSDATE;
1039 if(X_MODE = 'I') then
1040 X_LAST_UPDATED_BY := 1;
1041 X_LAST_UPDATE_LOGIN := 0;
1042 elsif (X_MODE = 'R') then
1043 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1044 if X_LAST_UPDATED_BY is NULL then
1045 X_LAST_UPDATED_BY := -1;
1046 end if;
1047 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1048 if X_LAST_UPDATE_LOGIN is NULL then
1049 X_LAST_UPDATE_LOGIN := -1;
1050 end if;
1051 else
1052 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1053 IGS_GE_MSG_STACK.ADD;
1054 app_exception.raise_exception;
1055 end if;
1056 OPEN system_type_cur(x_encumbrance_type);
1057 FETCH system_type_cur INTO system_type_rec;
1058 CLOSE system_type_cur;
1059 IF system_type_rec.s_encumbrance_cat = 'ADMIN' THEN
1060 /* asbala: 3446073 - ACAD type holds will have the time component in the start and end dates,
1061 ADMIN type holds will not. */
1062 Before_DML(
1063 p_action=>'UPDATE',
1064 x_rowid=>X_ROWID,
1065 x_authorising_person_id=>X_AUTHORISING_PERSON_ID,
1066 x_comments=>X_COMMENTS,
1067 x_encumbrance_type=>X_ENCUMBRANCE_TYPE,
1068 x_expiry_dt=>TRUNC(X_EXPIRY_DT),
1069 x_person_id=>X_PERSON_ID,
1070 x_start_dt=>TRUNC(X_START_DT),
1071 x_spo_course_cd => x_spo_course_cd,
1072 x_spo_sequence_number => x_spo_sequence_number,
1073 X_CAL_TYPE => X_CAL_TYPE ,
1074 X_SEQUENCE_NUMBER => X_SEQUENCE_NUMBER ,
1075 x_auth_resp_id => x_auth_resp_id ,
1076 X_EXTERNAL_REFERENCE => X_EXTERNAL_REFERENCE,
1077 x_creation_date=>X_LAST_UPDATE_DATE,
1078 x_created_by=>X_LAST_UPDATED_BY,
1079 x_last_update_date=>X_LAST_UPDATE_DATE,
1080 x_last_updated_by=>X_LAST_UPDATED_BY,
1081 x_last_update_login=>X_LAST_UPDATE_LOGIN
1082 );
1083 ELSE
1084 Before_DML(
1085 p_action=>'UPDATE',
1086 x_rowid=>X_ROWID,
1087 x_authorising_person_id=>X_AUTHORISING_PERSON_ID,
1088 x_comments=>X_COMMENTS,
1089 x_encumbrance_type=>X_ENCUMBRANCE_TYPE,
1090 x_expiry_dt=>X_EXPIRY_DT,
1091 x_person_id=>X_PERSON_ID,
1092 x_start_dt=>X_START_DT,
1093 x_spo_course_cd => x_spo_course_cd,
1094 x_spo_sequence_number => x_spo_sequence_number,
1095 X_CAL_TYPE => X_CAL_TYPE ,
1096 X_SEQUENCE_NUMBER => X_SEQUENCE_NUMBER ,
1097 x_auth_resp_id => x_auth_resp_id ,
1098 X_EXTERNAL_REFERENCE => X_EXTERNAL_REFERENCE,
1099 x_creation_date=>X_LAST_UPDATE_DATE,
1100 x_created_by=>X_LAST_UPDATED_BY,
1101 x_last_update_date=>X_LAST_UPDATE_DATE,
1102 x_last_updated_by=>X_LAST_UPDATED_BY,
1103 x_last_update_login=>X_LAST_UPDATE_LOGIN
1104 );
1105 END IF;
1106 UPDATE IGS_PE_PERS_ENCUMB SET
1107 expiry_dt = new_references.expiry_dt,
1108 authorising_person_id = new_references.authorising_person_id,
1109 comments = new_references.comments,
1110 cal_type = new_references.cal_type ,
1111 sequence_number = new_references.sequence_number ,
1112 auth_resp_id = new_references.auth_resp_id ,
1113 external_reference = new_references.external_reference,
1114 last_update_date = x_last_update_date,
1115 last_updated_by = x_last_updated_by,
1116 last_update_login = x_last_update_login
1117 WHERE ROWID = x_rowid;
1118 IF (SQL%NOTFOUND) THEN
1119 RAISE NO_DATA_FOUND;
1120 END IF;
1121 After_DML(
1122 p_action => 'UPDATE',
1123 x_rowid => X_ROWID
1124 );
1125 END UPDATE_ROW;
1126
1127 PROCEDURE ADD_ROW (
1128 x_rowid IN OUT NOCOPY VARCHAR2,
1129 x_person_id IN NUMBER,
1130 x_encumbrance_type IN VARCHAR2,
1131 x_start_dt IN DATE,
1132 x_expiry_dt IN DATE,
1133 x_authorising_person_id IN NUMBER,
1134 x_comments IN VARCHAR2,
1135 x_spo_course_cd IN VARCHAR2,
1136 x_spo_sequence_number IN NUMBER,
1137 x_cal_type IN VARCHAR2,
1138 x_sequence_number IN NUMBER,
1139 x_auth_resp_id IN NUMBER,
1140 x_external_reference IN VARCHAR2 ,
1141 x_mode IN VARCHAR2
1142 ) AS
1143 CURSOR c1(cp_start_dt igs_pe_pers_encumb.start_dt%TYPE) IS
1144 SELECT ROWID FROM igs_pe_pers_encumb
1145 WHERE person_id = x_person_id
1146 AND encumbrance_type = x_encumbrance_type
1147 AND start_dt = cp_start_dt ;
1148
1149 CURSOR system_type_cur(cp_encumbrance_type igs_fi_encmb_type.encumbrance_type%TYPE) IS
1150 SELECT s_encumbrance_cat
1151 FROM igs_fi_encmb_type
1152 WHERE encumbrance_type = cp_encumbrance_type;
1153 system_type_rec system_type_cur%ROWTYPE;
1154 l_start_dt igs_pe_pers_encumb.start_dt%TYPE;
1155 BEGIN
1156
1157 OPEN system_type_cur(x_encumbrance_type);
1158 FETCH system_type_cur INTO system_type_rec;
1159 CLOSE system_type_cur;
1160 IF system_type_rec.s_encumbrance_cat = 'ADMIN' THEN
1161 /* asbala: 3446073 - ACAD type holds will have the time component in the start and end dates,
1162 ADMIN type holds will not. */
1163 l_start_dt := TRUNC(x_start_dt);
1164 ELSE
1165 l_start_dt := x_start_dt;
1166 END IF;
1167 OPEN c1(l_start_dt);
1168 FETCH c1 INTO x_rowid;
1169 IF (c1%NOTFOUND) THEN
1170 CLOSE c1;
1171 INSERT_ROW (
1172 X_ROWID,
1173 X_PERSON_ID,
1174 X_ENCUMBRANCE_TYPE,
1175 X_START_DT,
1176 X_EXPIRY_DT,
1177 X_AUTHORISING_PERSON_ID,
1178 X_COMMENTS,
1179 x_spo_course_cd,
1180 x_spo_sequence_number,
1181 X_CAL_TYPE ,
1182 X_SEQUENCE_NUMBER ,
1183 x_auth_resp_id,
1184 x_external_reference,
1185 X_MODE);
1186 RETURN;
1187 END IF;
1188 CLOSE c1;
1189 UPDATE_ROW (
1190 X_ROWID,
1191 X_PERSON_ID,
1192 X_ENCUMBRANCE_TYPE,
1193 X_START_DT,
1194 X_EXPIRY_DT,
1195 X_AUTHORISING_PERSON_ID,
1196 X_COMMENTS,
1197 x_spo_course_cd,
1198 x_spo_sequence_number,
1199 X_CAL_TYPE ,
1200 X_SEQUENCE_NUMBER ,
1201 x_auth_resp_id,
1202 x_external_reference,
1203 X_MODE);
1204 END ADD_ROW;
1205
1206 procedure DELETE_ROW (
1207 X_ROWID IN VARCHAR2
1208 ) AS
1209 BEGIN
1210 Before_DML(
1211 p_action => 'DELETE',
1212 x_rowid => X_ROWID
1213 );
1214 DELETE FROM IGS_PE_PERS_ENCUMB
1215 WHERE ROWID = X_ROWID;
1216 IF (SQL%NOTFOUND) THEN
1217 RAISE no_data_found;
1218 END IF;
1219 After_DML(
1220 p_action => 'DELETE',
1221 x_rowid => X_ROWID
1222 );
1223 END DELETE_ROW;
1224 END IGS_PE_PERS_ENCUMB_PKG;
[Home] [Help]