[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_CRS_GRP_EXCL_PKG
Source
1 package body IGS_PE_CRS_GRP_EXCL_PKG AS
2 /* $Header: IGSNI14B.pls 115.5 2003/02/20 10:29:33 shtatiko ship $ */
3
4
5
6 l_rowid VARCHAR2(25);
7 old_references IGS_PE_CRS_GRP_EXCL%RowType;
8 new_references IGS_PE_CRS_GRP_EXCL%RowType;
9
10 PROCEDURE Set_Column_Values (
11 p_action IN VARCHAR2,
12 x_rowid IN VARCHAR2 DEFAULT NULL,
13 x_person_id IN NUMBER DEFAULT NULL,
14 x_encumbrance_type IN VARCHAR2 DEFAULT NULL,
15 x_pen_start_dt IN DATE DEFAULT NULL,
16 x_s_encmb_effect_type IN VARCHAR2 DEFAULT NULL,
17 x_pee_start_dt IN DATE DEFAULT NULL,
18 x_pee_sequence_number IN NUMBER DEFAULT NULL,
19 x_course_group_cd IN VARCHAR2 DEFAULT NULL,
20 x_pcge_start_dt IN DATE DEFAULT NULL,
21 x_expiry_dt IN DATE DEFAULT NULL,
22 x_creation_date IN DATE DEFAULT NULL,
23 x_created_by IN NUMBER DEFAULT NULL,
24 x_last_update_date IN DATE DEFAULT NULL,
25 x_last_updated_by IN NUMBER DEFAULT NULL,
26 x_last_update_login IN NUMBER DEFAULT NULL
27 ) AS
28
29 CURSOR cur_old_ref_values IS
30 SELECT *
31 FROM IGS_PE_CRS_GRP_EXCL
32 WHERE rowid = x_rowid;
33
34 BEGIN
35
36 l_rowid := x_rowid;
37
38 -- Code for setting the Old and New Reference Values.
39 -- Populate Old Values.
40 Open cur_old_ref_values;
41 Fetch cur_old_ref_values INTO old_references;
42 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
43 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
44 IGS_GE_MSG_STACK.ADD;
45 Close cur_old_ref_values;
46 App_Exception.Raise_Exception;
47 Return;
48 END IF;
49 Close cur_old_ref_values;
50
51 -- Populate New Values.
52 new_references.person_id := x_person_id;
53 new_references.encumbrance_type := x_encumbrance_type;
54 new_references.pen_start_dt := x_pen_start_dt;
55 new_references.s_encmb_effect_type := x_s_encmb_effect_type;
56 new_references.pee_start_dt := x_pee_start_dt;
57 new_references.pee_sequence_number := x_pee_sequence_number;
58 new_references.course_group_cd := x_course_group_cd;
59 new_references.pcge_start_dt := x_pcge_start_dt;
60 new_references.expiry_dt := x_expiry_dt;
61 IF (p_action = 'UPDATE') THEN
62 new_references.creation_date := old_references.creation_date;
63 new_references.created_by := old_references.created_by;
64 ELSE
65 new_references.creation_date := x_creation_date;
66 new_references.created_by := x_created_by;
67 END IF;
68 new_references.last_update_date := x_last_update_date;
69 new_references.last_updated_by := x_last_updated_by;
70 new_references.last_update_login := x_last_update_login;
71
72 END Set_Column_Values;
73
74 PROCEDURE BeforeRowInsertUpdate1(
75 p_inserting IN BOOLEAN DEFAULT FALSE,
76 p_updating IN BOOLEAN DEFAULT FALSE,
77 p_deleting IN BOOLEAN DEFAULT FALSE
78 ) AS
79 v_message_name varchar2(30);
80 BEGIN
81 -- Validate that start date is not less than the current date.
82 IF (new_references.pcge_start_dt IS NOT NULL) THEN
83 IF p_inserting OR (p_updating AND
84 (NVL(old_references.pcge_start_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
85 <> new_references.pcge_start_dt))
86 THEN
87 IF IGS_EN_VAL_PCE.enrp_val_encmb_dt (
88 new_references.pcge_start_dt,
89 v_message_name) = FALSE THEN
90 Fnd_Message.Set_Name('IGS', v_message_name);
91 IGS_GE_MSG_STACK.ADD;
92 App_Exception.Raise_Exception;
93 END IF;
94 END IF;
95 END IF;
96 -- Validate that start date is not less than the parent IGS_PE_PERSON
97 -- Encumbrance Effect start date.
98 IF p_inserting THEN
99 IF IGS_EN_VAL_PCE.enrp_val_encmb_dts (
100 new_references.pee_start_dt,
101 new_references.pcge_start_dt,
102 v_message_name) = FALSE THEN
103 Fnd_Message.Set_Name('IGS', v_message_name);
104 IGS_GE_MSG_STACK.ADD;
105 App_Exception.Raise_Exception;
106 END IF;
107 END IF;
108 -- Validate that if expiry date is specified, then expiry date is not
109 -- less than the start date.
110 IF (new_references.expiry_dt IS NOT NULL) THEN
111 IF p_inserting OR (p_updating AND
112 (NVL(old_references.expiry_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
113 <> new_references.expiry_dt))
114 THEN
115 IF IGS_EN_VAL_PCE.enrp_val_strt_exp_dt (
116 new_references.pcge_start_dt,
117 new_references.expiry_dt,
118 v_message_name) = FALSE THEN
119 Fnd_Message.Set_Name('IGS', v_message_name);
120 IGS_GE_MSG_STACK.ADD;
121 App_Exception.Raise_Exception;
122 END IF;
123 IF IGS_EN_VAL_PCE.enrp_val_encmb_dt (
124 new_references.expiry_dt,
125 v_message_name) = FALSE THEN
126 Fnd_Message.Set_Name('IGS', v_message_name);
127 IGS_GE_MSG_STACK.ADD;
128 App_Exception.Raise_Exception;
129 END IF;
130 END IF;
131 END IF;
132 -- Validate that records for this table can be created for the encumbrance
133 -- effect type.
134 IF p_inserting THEN
135 IF IGS_EN_VAL_PCE.enrp_val_pee_table (
136 new_references.s_encmb_effect_type,
137 'IGS_PE_CRS_GRP_EXCL',
138 v_message_name) = FALSE THEN
139 Fnd_Message.Set_Name('IGS', v_message_name);
140 IGS_GE_MSG_STACK.ADD;
141 App_Exception.Raise_Exception;
142 END IF;
143 END IF;
144
145
146 END BeforeRowInsertUpdate1;
147
148 -- Trigger description :-
149 -- "OSS_TST".trg_pcge_ar_iu
150 -- AFTER INSERT OR DELETE OR UPDATE
151 -- ON IGS_PE_CRS_GRP_EXCL
152 -- FOR EACH ROW
153
154 PROCEDURE AfterRowInsertUpdateDelete2(
155 p_inserting IN BOOLEAN DEFAULT FALSE,
156 p_updating IN BOOLEAN DEFAULT FALSE,
157 p_deleting IN BOOLEAN DEFAULT FALSE
158 ) AS
159 v_message_name varchar2(30);
160 v_rowid_saved BOOLEAN := FALSE;
161 BEGIN
162 -- Validate for open ended IGS_PE_PERSON crs grp exclusion records.
163 IF new_references.expiry_dt IS NULL THEN
164 -- Save the rowid of the current row.
165 IF IGS_EN_VAL_PCGE.enrp_val_pcge_open (
166 new_references.person_id,
167 new_references.encumbrance_type,
168 new_references.pen_start_dt,
169 new_references.s_encmb_effect_type,
170 new_references.pee_start_dt,
171 new_references.course_group_cd,
172 new_references.pcge_start_dt,
173 v_message_name) = FALSE THEN
174 Fnd_Message.Set_Name('IGS', v_message_name);
175 IGS_GE_MSG_STACK.ADD;
176 App_Exception.Raise_Exception;
177 END IF;
178
179
180 v_rowid_saved := TRUE;
181 -- Cannot call enrp_val_pcge_open because trigger will be mutating.
182 END IF;
183
184
185 END AfterRowInsertUpdateDelete2;
186
187 -- Trigger description :-
188 -- "OSS_TST".trg_pcge_as_iu
189 -- AFTER INSERT OR UPDATE
190 -- ON IGS_PE_CRS_GRP_EXCL
191
192
193 PROCEDURE Check_Constraints (
194 Column_Name IN VARCHAR2 DEFAULT NULL,
195 Column_Value IN VARCHAR2 DEFAULT NULL
196 )
197 AS
198 BEGIN
199 IF column_name is null then
200 NULL;
201 ELSIF upper(Column_name) = 'COURSE_GROUP_CD' then
202 new_references.course_group_cd:= column_value;
203 ELSIF upper(Column_name) = 'ENCUMBRANCE_TYPE' then
204 new_references.encumbrance_type:= column_value;
205 ELSIF upper(Column_name) = 'S_ENCMB_EFFECT_TYPE' then
206 new_references.s_encmb_effect_type:= column_value;
207 END IF;
208
209 IF upper(column_name) = 'COURSE_GROUP_CD' OR
210 column_name is null Then
211 IF new_references.course_group_cd <> UPPER(new_references.course_group_cd) Then
212 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
213 IGS_GE_MSG_STACK.ADD;
214 App_Exception.Raise_Exception;
215 END IF;
216 END IF;
217
218 IF upper(column_name) = 'ENCUMBRANCE_TYPE' OR
219 column_name is null Then
220 IF new_references.encumbrance_type <>
221 UPPER(new_references.encumbrance_type) Then
222 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
223 IGS_GE_MSG_STACK.ADD;
224 App_Exception.Raise_Exception;
225 END IF;
226 END IF;
227 IF upper(column_name) = 'S_ENCMB_EFFECT_TYPE' OR
228 column_name is null Then
229 IF new_references.s_encmb_effect_type<>
230 UPPER(new_references.s_encmb_effect_type) Then
231 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
232 IGS_GE_MSG_STACK.ADD;
233 App_Exception.Raise_Exception;
234 END IF;
235 END IF;
236
237
238 END Check_Constraints;
239
240
241
242 PROCEDURE Check_Parent_Existance AS
243 BEGIN
244
245 IF (((old_references.course_group_cd = new_references.course_group_cd)) OR
246 ((new_references.course_group_cd IS NULL))) THEN
247 NULL;
248 ELSE
249
250 IF NOT IGS_PS_GRP_PKG.Get_PK_For_Validation (
251 new_references.course_group_cd
252 ) THEN
253 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
254 IGS_GE_MSG_STACK.ADD;
255 App_Exception.Raise_Exception;
256 END IF;
257 END IF;
258
259 IF (((old_references.person_id = new_references.person_id) AND
260 (old_references.encumbrance_type = new_references.encumbrance_type ) AND
261 (old_references.pen_start_dt = new_references.pen_start_dt) AND
262 (old_references.s_encmb_effect_type = new_references.s_encmb_effect_type) AND
263 (old_references.pee_start_dt = new_references.pee_start_dt) AND
264 (old_references.pee_sequence_number = new_references.pee_sequence_number)) OR
265 ((new_references.person_id IS NULL) OR
266 (new_references.encumbrance_type IS NULL) OR
267 (new_references.pen_start_dt IS NULL) OR
268 (new_references.s_encmb_effect_type IS NULL) OR
269 (new_references.pee_start_dt IS NULL) OR
270 (new_references.pee_sequence_number IS NULL))) THEN
271 NULL;
272 ELSE
273 IF NOT IGS_PE_PERSENC_EFFCT_PKG.Get_PK_For_Validation (
274 new_references.person_id,
275 new_references.encumbrance_type,
276 new_references.pen_start_dt,
277 new_references.s_encmb_effect_type,
278 new_references.pee_start_dt,
279 new_references.pee_sequence_number ) THEN
280 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
281 IGS_GE_MSG_STACK.ADD;
282 App_Exception.Raise_Exception;
283 END IF;
284 END IF;
285
286 END Check_Parent_Existance;
287
288 FUNCTION Get_PK_For_Validation (
289 x_person_id IN NUMBER,
290 x_encumbrance_type IN VARCHAR2,
291 x_pen_start_dt IN DATE,
292 x_s_encmb_effect_type IN VARCHAR2,
293 x_pee_start_dt IN DATE,
294 x_pee_sequence_number IN NUMBER,
295 x_course_group_cd IN VARCHAR2,
296 x_pcge_start_dt IN DATE
297 ) RETURN BOOLEAN AS
298
299 CURSOR cur_rowid IS
300 SELECT rowid
301 FROM IGS_PE_CRS_GRP_EXCL
302 WHERE person_id = x_person_id
303 AND encumbrance_type = x_encumbrance_type
304 AND pen_start_dt = x_pen_start_dt
305 AND s_encmb_effect_type = x_s_encmb_effect_type
306 AND pee_start_dt = x_pee_start_dt
307 AND pee_sequence_number = x_pee_sequence_number
308 AND course_group_cd = x_course_group_cd
309 AND pcge_start_dt = x_pcge_start_dt
310 FOR UPDATE NOWAIT;
311
312 lv_rowid cur_rowid%RowType;
313
314 BEGIN
315
316 Open cur_rowid;
317 Fetch cur_rowid INTO lv_rowid;
318 IF (cur_rowid%FOUND) THEN
319 Close cur_rowid;
320 Return (TRUE);
321 ELSE
322 Close cur_rowid;
323 Return (FALSE);
324 END IF;
325 END Get_PK_For_Validation;
326
327 PROCEDURE GET_FK_IGS_PE_PERSENC_EFFCT (
328 x_person_id IN NUMBER,
329 x_encumbrance_type IN VARCHAR2,
330 x_pen_start_dt IN DATE,
331 x_s_encmb_effect_type IN VARCHAR2,
332 x_pee_start_dt IN DATE,
333 x_pee_sequence_number IN NUMBER
334 ) AS
335
336 CURSOR cur_rowid IS
337 SELECT rowid
338 FROM IGS_PE_CRS_GRP_EXCL
339 WHERE person_id = x_person_id
340 AND encumbrance_type = x_encumbrance_type
341 AND pen_start_dt = x_pen_start_dt
342 AND s_encmb_effect_type = x_s_encmb_effect_type
343 AND pee_start_dt = x_pee_start_dt
344 AND pee_sequence_number = x_pee_sequence_number ;
345
346 lv_rowid cur_rowid%RowType;
347
348 BEGIN
349
350 Open cur_rowid;
351 Fetch cur_rowid INTO lv_rowid;
352 IF (cur_rowid%FOUND) THEN
353 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PCGE_PEE_FK');
354 IGS_GE_MSG_STACK.ADD;
355 Close cur_rowid;
356 App_Exception.Raise_Exception;
357 Return;
358 END IF;
359 Close cur_rowid;
360
361 END GET_FK_IGS_PE_PERSENC_EFFCT;
362
363 PROCEDURE Before_DML (
364 p_action IN VARCHAR2,
365 x_rowid IN VARCHAR2 DEFAULT NULL,
366 x_person_id IN NUMBER DEFAULT NULL,
367 x_encumbrance_type IN VARCHAR2 DEFAULT NULL,
368 x_pen_start_dt IN DATE DEFAULT NULL,
369 x_s_encmb_effect_type IN VARCHAR2 DEFAULT NULL,
370 x_pee_start_dt IN DATE DEFAULT NULL,
371 x_pee_sequence_number IN NUMBER DEFAULT NULL,
372 x_course_group_cd IN VARCHAR2 DEFAULT NULL,
373 x_pcge_start_dt IN DATE DEFAULT NULL,
374 x_expiry_dt IN DATE DEFAULT NULL,
375 x_creation_date IN DATE DEFAULT NULL,
376 x_created_by IN NUMBER DEFAULT NULL,
377 x_last_update_date IN DATE DEFAULT NULL,
378 x_last_updated_by IN NUMBER DEFAULT NULL,
379 x_last_update_login IN NUMBER DEFAULT NULL
380 ) AS
381 BEGIN
382
383 Set_Column_Values (
384 p_action,
385 x_rowid,
386 x_person_id,
387 x_encumbrance_type,
388 x_pen_start_dt,
389 x_s_encmb_effect_type,
390 x_pee_start_dt,
391 x_pee_sequence_number,
392 x_course_group_cd,
393 x_pcge_start_dt,
394 x_expiry_dt,
395 x_creation_date,
396 x_created_by,
397 x_last_update_date,
398 x_last_updated_by,
399 x_last_update_login
400 );
401
402 IF (p_action = 'INSERT') THEN
403 -- Call all the procedures related to Before Insert.
404 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
405 IF Get_PK_For_Validation (
406 new_references.person_id,
407 new_references.encumbrance_type ,
408 new_references.pen_start_dt ,
409 new_references.s_encmb_effect_type,
410 new_references.pee_start_dt ,
411 new_references.pee_sequence_number,
412 new_references.course_group_cd,
413 new_references.pcge_start_dt) THEN
414 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
415 IGS_GE_MSG_STACK.ADD;
416 App_Exception.Raise_Exception;
417 END IF;
418
419 Check_Constraints; -- if procedure present
420 Check_Parent_Existance; -- if procedure present
421 ELSIF (p_action = 'UPDATE') THEN
422 -- Call all the procedures related to Before Update.
423 BeforeRowInsertUpdate1 ( p_updating => TRUE );
424
425 Check_Constraints; -- if procedure present
426 Check_Parent_Existance; -- if procedure present
427
428 ELSIF (p_action = 'DELETE') THEN
429 -- Call all the procedures related to Before Delete.
430
431 NULL;
432 ELSIF (p_action = 'VALIDATE_INSERT') THEN
433 IF Get_PK_For_Validation (
434 new_references.person_id,
435 new_references.encumbrance_type ,
436 new_references.pen_start_dt ,
437 new_references.s_encmb_effect_type,
438 new_references.pee_start_dt ,
439 new_references.pee_sequence_number,
440 new_references.course_group_cd,
441 new_references.pcge_start_dt) THEN
442 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
443 IGS_GE_MSG_STACK.ADD;
444 App_Exception.Raise_Exception;
445 END IF;
446 Check_Constraints; -- if procedure present
447
448
449 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
450 Check_Constraints; -- if procedure present
451
452 ELSIF (p_action = 'VALIDATE_DELETE') THEN
453 NULL;
454 END IF;
455
456 END Before_DML;
457
458 PROCEDURE After_DML (
459 p_action IN VARCHAR2,
460 x_rowid IN VARCHAR2
461 ) AS
462 BEGIN
463
464 l_rowid := x_rowid;
465
466 IF (p_action = 'INSERT') THEN
467 -- Call all the procedures related to After Insert.
468 AfterRowInsertUpdateDelete2 ( p_inserting => TRUE );
469 ELSIF (p_action = 'UPDATE') THEN
470 -- Call all the procedures related to After Update.
471 AfterRowInsertUpdateDelete2 ( p_updating => TRUE );
472
473 ELSIF (p_action = 'DELETE') THEN
474 -- Call all the procedures related to After Delete.
475 AfterRowInsertUpdateDelete2 ( p_deleting => TRUE );
476 END IF;
477
478 END After_DML;
479
480 procedure INSERT_ROW (
481 X_ROWID in out NOCOPY VARCHAR2,
482 X_PERSON_ID in NUMBER,
483 X_ENCUMBRANCE_TYPE in VARCHAR2,
484 X_PEN_START_DT in DATE,
485 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
486 X_PEE_START_DT in DATE,
487 X_PEE_SEQUENCE_NUMBER in NUMBER,
488 X_COURSE_GROUP_CD in VARCHAR2,
489 X_PCGE_START_DT in DATE,
490 X_EXPIRY_DT in DATE,
491 X_MODE in VARCHAR2 default 'R'
492 ) AS
493 cursor C is select ROWID from IGS_PE_CRS_GRP_EXCL
494 where PERSON_ID = X_PERSON_ID
495 and ENCUMBRANCE_TYPE = X_ENCUMBRANCE_TYPE
496 and PEN_START_DT = X_PEN_START_DT
497 and S_ENCMB_EFFECT_TYPE = X_S_ENCMB_EFFECT_TYPE
498 and PEE_START_DT = X_PEE_START_DT
499 and PEE_SEQUENCE_NUMBER = X_PEE_SEQUENCE_NUMBER
500 and COURSE_GROUP_CD = X_COURSE_GROUP_CD
501 and PCGE_START_DT = X_PCGE_START_DT;
502 X_LAST_UPDATE_DATE DATE;
503 X_LAST_UPDATED_BY NUMBER;
504 X_LAST_UPDATE_LOGIN NUMBER;
505 begin
506 X_LAST_UPDATE_DATE := SYSDATE;
507 if(X_MODE = 'I') then
508 X_LAST_UPDATED_BY := 1;
509 X_LAST_UPDATE_LOGIN := 0;
510 elsif (X_MODE = 'R') then
511 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
512 if X_LAST_UPDATED_BY is NULL then
513 X_LAST_UPDATED_BY := -1;
514 end if;
515 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
516 if X_LAST_UPDATE_LOGIN is NULL then
517 X_LAST_UPDATE_LOGIN := -1;
518 end if;
519 else
520 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
521 IGS_GE_MSG_STACK.ADD;
522 app_exception.raise_exception;
523 end if;
524
525 Before_DML(
526 p_action=>'INSERT',
527 x_rowid=>X_ROWID,
528 x_course_group_cd=>X_COURSE_GROUP_CD,
529 x_encumbrance_type=>X_ENCUMBRANCE_TYPE,
530 x_expiry_dt=>X_EXPIRY_DT,
531 x_pcge_start_dt=>X_PCGE_START_DT,
532 x_pee_sequence_number=>X_PEE_SEQUENCE_NUMBER,
533 x_pee_start_dt=>X_PEE_START_DT,
534 x_pen_start_dt=>X_PEN_START_DT,
535 x_person_id=>X_PERSON_ID,
536 x_s_encmb_effect_type=>X_S_ENCMB_EFFECT_TYPE,
537 x_creation_date=>X_LAST_UPDATE_DATE,
538 x_created_by=>X_LAST_UPDATED_BY,
539 x_last_update_date=>X_LAST_UPDATE_DATE,
540 x_last_updated_by=>X_LAST_UPDATED_BY,
541 x_last_update_login=>X_LAST_UPDATE_LOGIN
542 );
543
544 insert into IGS_PE_CRS_GRP_EXCL (
545 PERSON_ID,
546 ENCUMBRANCE_TYPE,
547 PEN_START_DT,
548 S_ENCMB_EFFECT_TYPE,
549 PEE_START_DT,
550 PEE_SEQUENCE_NUMBER,
551 COURSE_GROUP_CD,
552 PCGE_START_DT,
553 EXPIRY_DT,
554 CREATION_DATE,
555 CREATED_BY,
556 LAST_UPDATE_DATE,
557 LAST_UPDATED_BY,
558 LAST_UPDATE_LOGIN
559 ) values (
560 NEW_REFERENCES.PERSON_ID,
561 NEW_REFERENCES.ENCUMBRANCE_TYPE,
562 NEW_REFERENCES.PEN_START_DT,
563 NEW_REFERENCES.S_ENCMB_EFFECT_TYPE,
564 NEW_REFERENCES.PEE_START_DT,
565 NEW_REFERENCES.PEE_SEQUENCE_NUMBER,
566 NEW_REFERENCES.COURSE_GROUP_CD,
567 NEW_REFERENCES.PCGE_START_DT,
568 NEW_REFERENCES.EXPIRY_DT,
569 X_LAST_UPDATE_DATE,
570 X_LAST_UPDATED_BY,
571 X_LAST_UPDATE_DATE,
572 X_LAST_UPDATED_BY,
573 X_LAST_UPDATE_LOGIN
574 );
575
576 open c;
577 fetch c into X_ROWID;
578 if (c%notfound) then
579 close c;
580 raise no_data_found;
581 end if;
582 close c;
583 After_DML(
584 p_action => 'INSERT',
585 x_rowid => X_ROWID
586 );
587 end INSERT_ROW;
588
589 procedure LOCK_ROW (
590 X_ROWID in VARCHAR2,
591 X_PERSON_ID in NUMBER,
592 X_ENCUMBRANCE_TYPE in VARCHAR2,
593 X_PEN_START_DT in DATE,
594 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
595 X_PEE_START_DT in DATE,
596 X_PEE_SEQUENCE_NUMBER in NUMBER,
597 X_COURSE_GROUP_CD in VARCHAR2,
598 X_PCGE_START_DT in DATE,
599 X_EXPIRY_DT in DATE
600 ) AS
601 cursor c1 is select
602 EXPIRY_DT
603 from IGS_PE_CRS_GRP_EXCL
604 where ROWID = X_ROWID
605 for update nowait;
606 tlinfo c1%rowtype;
607
608 begin
609 open c1;
610 fetch c1 into tlinfo;
611 if (c1%notfound) then
612 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
613
614 close c1;
615 App_Exception.Raise_Exception;
616 return;
617 end if;
618 close c1;
619
620 if ( ((tlinfo.EXPIRY_DT = X_EXPIRY_DT)
621 OR ((tlinfo.EXPIRY_DT is null)
622 AND (X_EXPIRY_DT is null)))
623 ) then
624 null;
625 else
626 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
627 app_exception.raise_exception;
628 end if;
629 return;
630 end LOCK_ROW;
631
632 procedure UPDATE_ROW (
633 X_ROWID in VARCHAR2,
634 X_PERSON_ID in NUMBER,
635 X_ENCUMBRANCE_TYPE in VARCHAR2,
636 X_PEN_START_DT in DATE,
637 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
638 X_PEE_START_DT in DATE,
639 X_PEE_SEQUENCE_NUMBER in NUMBER,
640 X_COURSE_GROUP_CD in VARCHAR2,
641 X_PCGE_START_DT in DATE,
642 X_EXPIRY_DT in DATE,
643 X_MODE in VARCHAR2 default 'R'
644 ) AS
645 X_LAST_UPDATE_DATE DATE;
646 X_LAST_UPDATED_BY NUMBER;
647 X_LAST_UPDATE_LOGIN NUMBER;
648 begin
649 X_LAST_UPDATE_DATE := SYSDATE;
650 if(X_MODE = 'I') then
651 X_LAST_UPDATED_BY := 1;
652 X_LAST_UPDATE_LOGIN := 0;
653 elsif (X_MODE = 'R') then
654 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
655 if X_LAST_UPDATED_BY is NULL then
656 X_LAST_UPDATED_BY := -1;
657 end if;
658 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
659 if X_LAST_UPDATE_LOGIN is NULL then
660 X_LAST_UPDATE_LOGIN := -1;
661 end if;
662 else
663 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
664 IGS_GE_MSG_STACK.ADD;
665 app_exception.raise_exception;
666 end if;
667 Before_DML(
668 p_action=>'UPDATE',
669 x_rowid=>X_ROWID,
670 x_course_group_cd=>X_COURSE_GROUP_CD,
671 x_encumbrance_type=>X_ENCUMBRANCE_TYPE,
672 x_expiry_dt=>X_EXPIRY_DT,
673 x_pcge_start_dt=>X_PCGE_START_DT,
674 x_pee_sequence_number=>X_PEE_SEQUENCE_NUMBER,
675 x_pee_start_dt=>X_PEE_START_DT,
676 x_pen_start_dt=>X_PEN_START_DT,
677 x_person_id=>X_PERSON_ID,
678 x_s_encmb_effect_type=>X_S_ENCMB_EFFECT_TYPE,
679 x_creation_date=>X_LAST_UPDATE_DATE,
680 x_created_by=>X_LAST_UPDATED_BY,
681 x_last_update_date=>X_LAST_UPDATE_DATE,
682 x_last_updated_by=>X_LAST_UPDATED_BY,
683 x_last_update_login=>X_LAST_UPDATE_LOGIN
684 );
685 update IGS_PE_CRS_GRP_EXCL set
686 EXPIRY_DT = NEW_REFERENCES.EXPIRY_DT,
687 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
688 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
689 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
690 where ROWID = X_ROWID
691 ;
692 if (sql%notfound) then
693 raise no_data_found;
694 end if;
695 After_DML(
696 p_action => 'UPDATE',
697 x_rowid => X_ROWID
698 );
699 end UPDATE_ROW;
700
701 procedure ADD_ROW (
702 X_ROWID in out NOCOPY VARCHAR2,
703 X_PERSON_ID in NUMBER,
704 X_ENCUMBRANCE_TYPE in VARCHAR2,
705 X_PEN_START_DT in DATE,
706 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
707 X_PEE_START_DT in DATE,
708 X_PEE_SEQUENCE_NUMBER in NUMBER,
709 X_COURSE_GROUP_CD in VARCHAR2,
710 X_PCGE_START_DT in DATE,
711 X_EXPIRY_DT in DATE,
712 X_MODE in VARCHAR2 default 'R'
713 ) AS
714 cursor c1 is select rowid from IGS_PE_CRS_GRP_EXCL
715 where PERSON_ID = X_PERSON_ID
716 and ENCUMBRANCE_TYPE = X_ENCUMBRANCE_TYPE
717 and PEN_START_DT = X_PEN_START_DT
718 and S_ENCMB_EFFECT_TYPE = X_S_ENCMB_EFFECT_TYPE
719 and PEE_START_DT = X_PEE_START_DT
720 and PEE_SEQUENCE_NUMBER = X_PEE_SEQUENCE_NUMBER
721 and COURSE_GROUP_CD = X_COURSE_GROUP_CD
722 and PCGE_START_DT = X_PCGE_START_DT
723 ;
724
725 begin
726 open c1;
727 fetch c1 into X_ROWID;
728 if (c1%notfound) then
729 close c1;
730 INSERT_ROW (
731 X_ROWID,
732 X_PERSON_ID,
733 X_ENCUMBRANCE_TYPE,
734 X_PEN_START_DT,
735 X_S_ENCMB_EFFECT_TYPE,
736 X_PEE_START_DT,
737 X_PEE_SEQUENCE_NUMBER,
738 X_COURSE_GROUP_CD,
739 X_PCGE_START_DT,
740 X_EXPIRY_DT,
741 X_MODE);
742 return;
743 end if;
744 close c1;
745 UPDATE_ROW (
746 X_ROWID,
747 X_PERSON_ID,
748 X_ENCUMBRANCE_TYPE,
749 X_PEN_START_DT,
750 X_S_ENCMB_EFFECT_TYPE,
751 X_PEE_START_DT,
752 X_PEE_SEQUENCE_NUMBER,
753 X_COURSE_GROUP_CD,
754 X_PCGE_START_DT,
755 X_EXPIRY_DT,
756 X_MODE);
757 end ADD_ROW;
758
759 procedure DELETE_ROW (
760 X_ROWID in VARCHAR2
761 ) AS
762 begin
763 Before_DML(
764 p_action => 'DELETE',
765 x_rowid => X_ROWID
766 );
767 delete from IGS_PE_CRS_GRP_EXCL
768 where ROWID = X_ROWID;
769 if (sql%notfound) then
770 raise no_data_found;
771 end if;
772 After_DML(
773 p_action => 'DELETE',
774 x_rowid => X_ROWID
775 );
776 end DELETE_ROW;
777
778 end IGS_PE_CRS_GRP_EXCL_PKG;