[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_COURSE_EXCL_PKG
Source
1 package body IGS_PE_COURSE_EXCL_PKG AS
2 /* $Header: IGSNI13B.pls 115.3 2002/11/29 01:17:09 nsidana ship $ */
3
4
5
6 l_rowid VARCHAR2(25);
7 old_references IGS_PE_COURSE_EXCL%RowType;
8 new_references IGS_PE_COURSE_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_cd IN VARCHAR2 DEFAULT NULL,
20 x_pce_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_COURSE_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_cd := x_course_cd;
59 new_references.pce_start_dt := x_pce_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 -- Set audit details.
82
83 -- Validate that start date is not less than the current date.
84 IF (new_references.pce_start_dt IS NOT NULL) AND
85 (p_inserting OR (p_updating AND
86 (NVL(old_references.pce_start_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
87 <> new_references.pce_start_dt)))
88 THEN
89 IF IGS_EN_VAL_PCE.enrp_val_encmb_dt (
90 new_references.pce_start_dt,
91 v_message_name) = FALSE THEN
92 Fnd_Message.Set_Name('IGS', v_message_name);
93 IGS_GE_MSG_STACK.ADD;
94 App_Exception.Raise_Exception;
95 END IF;
96 END IF;
97 -- Validate that start date is not less than the parent IGS_PE_PERSON
98 -- Encumbrance Effect start date.
99 IF p_inserting THEN
100 IF IGS_EN_VAL_PCE.enrp_val_encmb_dts (
101 new_references.pee_start_dt,
102 new_references.pce_start_dt,
103 v_message_name) = FALSE THEN
104 Fnd_Message.Set_Name('IGS', v_message_name);
105 IGS_GE_MSG_STACK.ADD;
106 App_Exception.Raise_Exception;
107 END IF;
108 END IF;
109 -- Validate that if expiry date is specified, then expiry date is not
110 -- less than the start date.
111 IF (new_references.expiry_dt IS NOT NULL) AND
112 (p_inserting OR (p_updating AND
113 (NVL(old_references.expiry_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
114 <> new_references.expiry_dt)))
115 THEN
116 IF IGS_EN_VAL_PCE.enrp_val_strt_exp_dt (
117 new_references.pce_start_dt,
118 new_references.expiry_dt,
119 v_message_name) = FALSE THEN
120 Fnd_Message.Set_Name('IGS', v_message_name);
121 IGS_GE_MSG_STACK.ADD;
122 App_Exception.Raise_Exception;
123 END IF;
124 IF IGS_EN_VAL_PCE.enrp_val_encmb_dt (
125 new_references.expiry_dt,
126 v_message_name) = FALSE THEN
127 Fnd_Message.Set_Name('IGS', v_message_name);
128 IGS_GE_MSG_STACK.ADD;
129 App_Exception.Raise_Exception;
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_COURSE_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_pce_ar_iu
150 -- AFTER INSERT OR UPDATE
151 -- ON IGS_PE_COURSE_EXCL
152 -- FOR EACH ROW
153
154 PROCEDURE AfterRowInsertUpdate2(
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 IGS_PS_COURSE exclusion records.
163 IF new_references.expiry_dt IS NULL THEN
164 -- Validate for open ended person_crs_exclusion records.
165 IF new_references.expiry_dt IS NULL THEN
166 IF IGS_EN_VAL_PCE.enrp_val_pce_open (
167 new_references.person_id,
168 new_references.encumbrance_type,
169 new_references.pen_start_dt,
170 new_references.s_encmb_effect_type,
171 new_references.pee_start_dt,
172 new_references.course_cd,
173 new_references.pce_start_dt,
174 v_message_name) = FALSE THEN
175 Fnd_Message.Set_Name('IGS', v_message_name);
176 IGS_GE_MSG_STACK.ADD;
177 App_Exception.Raise_Exception;
178 END IF;
179 END IF;
180
181 END IF;
182
183
184 END AfterRowInsertUpdate2;
185
186 -- Trigger description :-
187 -- "OSS_TST".trg_pce_as_iu
188 -- AFTER INSERT OR UPDATE
189 -- ON IGS_PE_COURSE_EXCL
190
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_CD' then
202 new_references.course_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_CD' OR
210 column_name is null Then
211 IF new_references.course_cd <> UPPER(new_references.course_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 PROCEDURE Check_Parent_Existance AS
242 BEGIN
243
244 IF (((old_references.course_cd = new_references.course_cd)) OR
245 ((new_references.course_cd IS NULL))) THEN
246 NULL;
247 ELSE
248
249 IF NOT IGS_PS_COURSE_PKG.Get_PK_For_Validation (
250 new_references.course_cd
251 ) THEN
252 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
253 IGS_GE_MSG_STACK.ADD;
254 App_Exception.Raise_Exception;
255 END IF;
256 END IF;
257
258 IF (((old_references.person_id = new_references.person_id) AND
259 (old_references.encumbrance_type = new_references.encumbrance_type) AND
260 (old_references.pen_start_dt = new_references.pen_start_dt) AND
261 (old_references.s_encmb_effect_type = new_references.s_encmb_effect_type) AND
262 (old_references.pee_start_dt = new_references.pee_start_dt) AND
263 (old_references.pee_sequence_number = new_references.pee_sequence_number)) OR
264 ((new_references.person_id IS NULL) OR
265 (new_references.encumbrance_type IS NULL) OR
266 (new_references.pen_start_dt IS NULL) OR
267 (new_references.s_encmb_effect_type IS NULL) OR
268 (new_references.pee_start_dt IS NULL) OR
269 (new_references.pee_sequence_number IS NULL))) THEN
270 NULL;
271 ELSE
272
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_cd IN VARCHAR2,
296 x_pce_start_dt IN DATE
297 ) RETURN BOOLEAN AS
298
299 CURSOR cur_rowid IS
300 SELECT rowid
301 FROM IGS_PE_COURSE_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_cd = x_course_cd
309 AND pce_start_dt = x_pce_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
326 END Get_PK_For_Validation;
327
328 PROCEDURE GET_FK_IGS_PS_COURSE (
329 x_course_cd IN VARCHAR2
330 ) AS
331
332 CURSOR cur_rowid IS
333 SELECT rowid
334 FROM IGS_PE_COURSE_EXCL
335 WHERE course_cd = x_course_cd ;
336
337 lv_rowid cur_rowid%RowType;
338
339 BEGIN
340
341 Open cur_rowid;
342 Fetch cur_rowid INTO lv_rowid;
343 IF (cur_rowid%FOUND) THEN
344 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PCE_CRS_FK');
345 IGS_GE_MSG_STACK.ADD;
346 Close cur_rowid;
347 App_Exception.Raise_Exception;
348 Return;
349 END IF;
350 Close cur_rowid;
351
352 END GET_FK_IGS_PS_COURSE;
353
354 PROCEDURE GET_FK_IGS_PE_PERSENC_EFFCT (
355 x_person_id IN NUMBER,
356 x_encumbrance_type IN VARCHAR2,
357 x_pen_start_dt IN DATE,
358 x_s_encmb_effect_type IN VARCHAR2,
359 x_pee_start_dt IN DATE,
360 x_pee_sequence_number IN number
361 ) AS
362
363 CURSOR cur_rowid IS
364 SELECT rowid
365 FROM IGS_PE_COURSE_EXCL
366 WHERE person_id = x_person_id
367 AND encumbrance_type = x_encumbrance_type
368 AND pen_start_dt = x_pen_start_dt
369 AND s_encmb_effect_type = x_s_encmb_effect_type
370 AND pee_start_dt = x_pee_start_dt
371 AND pee_sequence_number = x_pee_sequence_number ;
372
373 lv_rowid cur_rowid%RowType;
374
375 BEGIN
376
377 Open cur_rowid;
378 Fetch cur_rowid INTO lv_rowid;
379 IF (cur_rowid%FOUND) THEN
380 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PCE_PEE_FK');
381 IGS_GE_MSG_STACK.ADD;
382 Close cur_rowid;
383 App_Exception.Raise_Exception;
384 Return;
385 END IF;
386 Close cur_rowid;
387
388 END GET_FK_IGS_PE_PERSENC_EFFCT;
389
390 PROCEDURE Before_DML (
391 p_action IN VARCHAR2,
392 x_rowid IN VARCHAR2 DEFAULT NULL,
393 x_person_id IN NUMBER DEFAULT NULL,
394 x_encumbrance_type IN VARCHAR2 DEFAULT NULL,
395 x_pen_start_dt IN DATE DEFAULT NULL,
396 x_s_encmb_effect_type IN VARCHAR2 DEFAULT NULL,
397 x_pee_start_dt IN DATE DEFAULT NULL,
398 x_pee_sequence_number IN NUMBER DEFAULT NULL,
399 x_course_cd IN VARCHAR2 DEFAULT NULL,
400 x_pce_start_dt IN DATE DEFAULT NULL,
401 x_expiry_dt IN DATE DEFAULT NULL,
402 x_creation_date IN DATE DEFAULT NULL,
403 x_created_by IN NUMBER DEFAULT NULL,
404 x_last_update_date IN DATE DEFAULT NULL,
405 x_last_updated_by IN NUMBER DEFAULT NULL,
406 x_last_update_login IN NUMBER DEFAULT NULL
407 ) AS
408 BEGIN
409
410 Set_Column_Values (
411 p_action,
412 x_rowid,
413 x_person_id,
414 x_encumbrance_type,
415 x_pen_start_dt,
416 x_s_encmb_effect_type,
417 x_pee_start_dt,
418 x_pee_sequence_number,
419 x_course_cd,
420 x_pce_start_dt,
421 x_expiry_dt,
422 x_creation_date,
423 x_created_by,
424 x_last_update_date,
425 x_last_updated_by,
426 x_last_update_login
427 );
428
429 IF (p_action = 'INSERT') THEN
430 -- Call all the procedures related to Before Insert.
431 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
432 IF Get_PK_For_Validation (
433 new_references.person_id ,
434 new_references.encumbrance_type ,
435 new_references.pen_start_dt ,
436 new_references.s_encmb_effect_type ,
437 new_references.pee_start_dt ,
438 new_references.pee_sequence_number ,
439 new_references.course_cd ,
440 new_references.pce_start_dt ) THEN
441 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
442 IGS_GE_MSG_STACK.ADD;
443 App_Exception.Raise_Exception;
444 END IF;
445 Check_Constraints; -- if procedure present
446 Check_Parent_Existance; -- if procedure present
447 ELSIF (p_action = 'UPDATE') THEN
448 -- Call all the procedures related to Before Update.
449 BeforeRowInsertUpdate1 ( p_updating => TRUE );
450
451 Check_Constraints; -- if procedure present
452 Check_Parent_Existance; -- if procedure present
453 ELSIF (p_action = 'DELETE') THEN
454 -- Call all the procedures related to Before Delete.
455
456 NULL;
457 ELSIF (p_action = 'VALIDATE_INSERT') THEN
458 IF Get_PK_For_Validation (
459 new_references.person_id ,
460 new_references.encumbrance_type ,
461 new_references.pen_start_dt ,
462 new_references.s_encmb_effect_type ,
463
464 new_references.pee_start_dt ,
465 new_references.pee_sequence_number ,
466 new_references.course_cd ,
467 new_references.pce_start_dt ) THEN
468 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
469 IGS_GE_MSG_STACK.ADD;
470 App_Exception.Raise_Exception;
471 END IF;
472
473 Check_Constraints; -- if procedure present
474 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
475 Check_Constraints; -- if procedure present
476 ELSIF (p_action = 'VALIDATE_DELETE') THEN
477 NULL;
478 END IF;
479
480
481 END Before_DML;
482
483 PROCEDURE After_DML (
484 p_action IN VARCHAR2,
485 x_rowid IN VARCHAR2
486 ) AS
487 BEGIN
488
489 l_rowid := x_rowid;
490
491 IF (p_action = 'INSERT') THEN
492 -- Call all the procedures related to After Insert.
493 AfterRowInsertUpdate2 ( p_inserting => TRUE );
494
495 ELSIF (p_action = 'UPDATE') THEN
496 -- Call all the procedures related to After Update.
497 AfterRowInsertUpdate2 ( p_updating => TRUE );
498
499 ELSIF (p_action = 'DELETE') THEN
500 -- Call all the procedures related to After Delete.
501 Null;
502 END IF;
503
504 END After_DML;
505
506 procedure INSERT_ROW (
507 X_ROWID in out NOCOPY VARCHAR2,
508 X_PERSON_ID in NUMBER,
509 X_ENCUMBRANCE_TYPE in VARCHAR2,
510 X_PEN_START_DT in DATE,
511 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
512 X_PEE_START_DT in DATE,
513 X_PEE_SEQUENCE_NUMBER in NUMBER,
514 X_COURSE_CD in VARCHAR2,
515 X_PCE_START_DT in DATE,
516 X_EXPIRY_DT in DATE,
517 X_MODE in VARCHAR2 default 'R'
518 ) AS
519 cursor C is select ROWID from IGS_PE_COURSE_EXCL
520 where PERSON_ID = X_PERSON_ID
521 and ENCUMBRANCE_TYPE = X_ENCUMBRANCE_TYPE
522 and PEN_START_DT = X_PEN_START_DT
523 and S_ENCMB_EFFECT_TYPE = X_S_ENCMB_EFFECT_TYPE
524 and PEE_START_DT = X_PEE_START_DT
525 and PEE_SEQUENCE_NUMBER = X_PEE_SEQUENCE_NUMBER
526 and COURSE_CD = X_COURSE_CD
527 and PCE_START_DT = X_PCE_START_DT;
528 X_LAST_UPDATE_DATE DATE;
529 X_LAST_UPDATED_BY NUMBER;
530 X_LAST_UPDATE_LOGIN NUMBER;
531 begin
532 X_LAST_UPDATE_DATE := SYSDATE;
533 if(X_MODE = 'I') then
534 X_LAST_UPDATED_BY := 1;
535 X_LAST_UPDATE_LOGIN := 0;
536 elsif (X_MODE = 'R') then
537 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
538 if X_LAST_UPDATED_BY is NULL then
539 X_LAST_UPDATED_BY := -1;
540 end if;
541 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
542 if X_LAST_UPDATE_LOGIN is NULL then
543 X_LAST_UPDATE_LOGIN := -1;
544 end if;
545 else
546 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
547 IGS_GE_MSG_STACK.ADD;
548 app_exception.raise_exception;
549 end if;
550 Before_DML(
551 p_action=>'INSERT',
552 x_rowid=>X_ROWID,
553 x_course_cd=>X_COURSE_CD,
554 x_encumbrance_type=>X_ENCUMBRANCE_TYPE,
555 x_expiry_dt=>X_EXPIRY_DT,
556 x_pce_start_dt=>X_PCE_START_DT,
557 x_pee_sequence_number=>X_PEE_SEQUENCE_NUMBER,
558 x_pee_start_dt=>X_PEE_START_DT,
559 x_pen_start_dt=>X_PEN_START_DT,
560 x_person_id=>X_PERSON_ID,
561 x_s_encmb_effect_type=>X_S_ENCMB_EFFECT_TYPE,
562 x_creation_date=>X_LAST_UPDATE_DATE,
563 x_created_by=>X_LAST_UPDATED_BY,
564 x_last_update_date=>X_LAST_UPDATE_DATE,
565 x_last_updated_by=>X_LAST_UPDATED_BY,
566 x_last_update_login=>X_LAST_UPDATE_LOGIN
567 );
568 insert into IGS_PE_COURSE_EXCL (
569 PERSON_ID,
570 ENCUMBRANCE_TYPE,
571 PEN_START_DT,
572 S_ENCMB_EFFECT_TYPE,
573 PEE_START_DT,
574 PEE_SEQUENCE_NUMBER,
575 COURSE_CD,
576 PCE_START_DT,
577 EXPIRY_DT,
578 CREATION_DATE,
579 CREATED_BY,
580 LAST_UPDATE_DATE,
581 LAST_UPDATED_BY,
582 LAST_UPDATE_LOGIN
583 ) values (
584 NEW_REFERENCES.PERSON_ID,
585 NEW_REFERENCES.ENCUMBRANCE_TYPE,
586 NEW_REFERENCES.PEN_START_DT,
587 NEW_REFERENCES.S_ENCMB_EFFECT_TYPE,
588 NEW_REFERENCES.PEE_START_DT,
589 NEW_REFERENCES.PEE_SEQUENCE_NUMBER,
590 NEW_REFERENCES.COURSE_CD,
591 NEW_REFERENCES.PCE_START_DT,
592 NEW_REFERENCES.EXPIRY_DT,
593 X_LAST_UPDATE_DATE,
594 X_LAST_UPDATED_BY,
595 X_LAST_UPDATE_DATE,
596 X_LAST_UPDATED_BY,
597 X_LAST_UPDATE_LOGIN
598 );
599
600 open c;
601 fetch c into X_ROWID;
602 if (c%notfound) then
603 close c;
604 raise no_data_found;
605 end if;
606 close c;
607
608 After_DML(
609 p_action => 'INSERT',
610 x_rowid => X_ROWID
611 );
612
613 end INSERT_ROW;
614
615 procedure LOCK_ROW (
616 X_ROWID in VARCHAR2,
617 X_PERSON_ID in NUMBER,
618 X_ENCUMBRANCE_TYPE in VARCHAR2,
619 X_PEN_START_DT in DATE,
620 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
621 X_PEE_START_DT in DATE,
622 X_PEE_SEQUENCE_NUMBER in NUMBER,
623 X_COURSE_CD in VARCHAR2,
624 X_PCE_START_DT in DATE,
625 X_EXPIRY_DT in DATE
626 ) AS
627 cursor c1 is select
628 EXPIRY_DT
629 from IGS_PE_COURSE_EXCL
630 where ROWID = X_ROWID
631 for update nowait;
632 tlinfo c1%rowtype;
633
634 begin
635 open c1;
636 fetch c1 into tlinfo;
637 if (c1%notfound) then
638 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
639
640 close c1;
641 App_Exception.Raise_Exception;
642 return;
643 end if;
644 close c1;
645
646 if ( ((tlinfo.EXPIRY_DT = X_EXPIRY_DT)
647 OR ((tlinfo.EXPIRY_DT is null)
648 AND (X_EXPIRY_DT is null)))
649 ) then
650 null;
651 else
652 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
653 app_exception.raise_exception;
654 end if;
655 return;
656 end LOCK_ROW;
657
658 procedure UPDATE_ROW (
659 X_ROWID in VARCHAR2,
660 X_PERSON_ID in NUMBER,
661 X_ENCUMBRANCE_TYPE in VARCHAR2,
662 X_PEN_START_DT in DATE,
663 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
664 X_PEE_START_DT in DATE,
665 X_PEE_SEQUENCE_NUMBER in NUMBER,
666 X_COURSE_CD in VARCHAR2,
667 X_PCE_START_DT in DATE,
668 X_EXPIRY_DT in DATE,
669 X_MODE in VARCHAR2 default 'R'
670 ) AS
671 X_LAST_UPDATE_DATE DATE;
672 X_LAST_UPDATED_BY NUMBER;
673 X_LAST_UPDATE_LOGIN NUMBER;
674 begin
675 X_LAST_UPDATE_DATE := SYSDATE;
676 if(X_MODE = 'I') then
677 X_LAST_UPDATED_BY := 1;
678 X_LAST_UPDATE_LOGIN := 0;
679 elsif (X_MODE = 'R') then
680 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
681 if X_LAST_UPDATED_BY is NULL then
682 X_LAST_UPDATED_BY := -1;
683 end if;
684 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
685 if X_LAST_UPDATE_LOGIN is NULL then
686 X_LAST_UPDATE_LOGIN := -1;
687 end if;
688 else
689 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
690 IGS_GE_MSG_STACK.ADD;
691 app_exception.raise_exception;
692 end if;
693 Before_DML(
694 p_action=>'UPDATE',
695 x_rowid=>X_ROWID,
696 x_course_cd=>X_COURSE_CD,
697 x_encumbrance_type=>X_ENCUMBRANCE_TYPE,
698 x_expiry_dt=>X_EXPIRY_DT,
699 x_pce_start_dt=>X_PCE_START_DT,
700 x_pee_sequence_number=>X_PEE_SEQUENCE_NUMBER,
701 x_pee_start_dt=>X_PEE_START_DT,
702 x_pen_start_dt=>X_PEN_START_DT,
703 x_person_id=>X_PERSON_ID,
704 x_s_encmb_effect_type=>X_S_ENCMB_EFFECT_TYPE,
705 x_creation_date=>X_LAST_UPDATE_DATE,
706 x_created_by=>X_LAST_UPDATED_BY,
707 x_last_update_date=>X_LAST_UPDATE_DATE,
708 x_last_updated_by=>X_LAST_UPDATED_BY,
709 x_last_update_login=>X_LAST_UPDATE_LOGIN
710 );
711 update IGS_PE_COURSE_EXCL set
712 EXPIRY_DT = NEW_REFERENCES.EXPIRY_DT,
713 LAST_UPDATE_DATE = x_LAST_UPDATE_DATE,
714 LAST_UPDATED_BY = x_LAST_UPDATED_BY,
715 LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN
716 where ROWID = X_ROWID
717 ;
718 if (sql%notfound) then
719 raise no_data_found;
720 end if;
721 After_DML(
722 p_action => 'UPDATE',
723 x_rowid => X_ROWID
724 );
725 end UPDATE_ROW;
726
727 procedure ADD_ROW (
728 X_ROWID in out NOCOPY VARCHAR2,
729 X_PERSON_ID in NUMBER,
730 X_ENCUMBRANCE_TYPE in VARCHAR2,
731 X_PEN_START_DT in DATE,
732 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
733 X_PEE_START_DT in DATE,
734 X_PEE_SEQUENCE_NUMBER in NUMBER,
735 X_COURSE_CD in VARCHAR2,
736 X_PCE_START_DT in DATE,
737 X_EXPIRY_DT in DATE,
738 X_MODE in VARCHAR2 default 'R'
739 ) AS
740 cursor c1 is select rowid from IGS_PE_COURSE_EXCL
741 where PERSON_ID = X_PERSON_ID
742 and ENCUMBRANCE_TYPE = X_ENCUMBRANCE_TYPE
743 and PEN_START_DT = X_PEN_START_DT
744 and S_ENCMB_EFFECT_TYPE = X_S_ENCMB_EFFECT_TYPE
745 and PEE_START_DT = X_PEE_START_DT
746 and PEE_SEQUENCE_NUMBER = X_PEE_SEQUENCE_NUMBER
747 and COURSE_CD = X_COURSE_CD
748 and PCE_START_DT = X_PCE_START_DT
749 ;
750
751 begin
752 open c1;
753 fetch c1 into X_ROWID;
754 if (c1%notfound) then
755 close c1;
756 INSERT_ROW (
757 X_ROWID,
758 X_PERSON_ID,
759 X_ENCUMBRANCE_TYPE,
760 X_PEN_START_DT,
761 X_S_ENCMB_EFFECT_TYPE,
762 X_PEE_START_DT,
763 X_PEE_SEQUENCE_NUMBER,
764 X_COURSE_CD,
765 X_PCE_START_DT,
766 X_EXPIRY_DT,
767 X_MODE);
768 return;
769 end if;
770 close c1;
771 UPDATE_ROW (
772 X_ROWID,
773 X_PERSON_ID,
774 X_ENCUMBRANCE_TYPE,
775 X_PEN_START_DT,
776 X_S_ENCMB_EFFECT_TYPE,
777 X_PEE_START_DT,
778 X_PEE_SEQUENCE_NUMBER,
779 X_COURSE_CD,
780 X_PCE_START_DT,
781 X_EXPIRY_DT,
782 X_MODE);
783 end ADD_ROW;
784
785 procedure DELETE_ROW (
786 X_ROWID in VARCHAR2
787 ) AS
788 begin
789 Before_DML(
790 p_action => 'DELETE',
791 x_rowid => X_ROWID
792 );
793 delete from IGS_PE_COURSE_EXCL
794 where ROWID = X_ROWID;
795 if (sql%notfound) then
796 raise no_data_found;
797 end if;
798 After_DML(
799 p_action => 'DELETE',
800 x_rowid => X_ROWID
801 );
802 end DELETE_ROW;
803
804 end IGS_PE_COURSE_EXCL_PKG;