[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_ANL_LOAD_PKG
Source
1 package body IGS_PS_ANL_LOAD_PKG AS
2 /* $Header: IGSPI04B.pls 115.5 2003/02/05 10:24:07 sarakshi ship $ */
3
4
5 l_rowid VARCHAR2(25);
6 old_references IGS_PS_ANL_LOAD%RowType;
7 new_references IGS_PS_ANL_LOAD%RowType;
8
9 PROCEDURE Set_Column_Values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2 DEFAULT NULL,
12 x_course_cd IN VARCHAR2 DEFAULT NULL,
13 x_version_number IN NUMBER DEFAULT NULL,
14 x_yr_num IN NUMBER DEFAULT NULL,
15 x_effective_start_dt IN DATE DEFAULT NULL,
16 x_effective_end_dt IN DATE DEFAULT NULL,
17 x_annual_load_val IN NUMBER DEFAULT NULL,
18 x_creation_date IN DATE DEFAULT NULL,
19 x_created_by IN NUMBER DEFAULT NULL,
20 x_last_update_date IN DATE DEFAULT NULL,
21 x_last_updated_by IN NUMBER DEFAULT NULL,
22 x_last_update_login IN NUMBER DEFAULT NULL
23 ) AS
24
25 CURSOR cur_old_ref_values IS
26 SELECT *
27 FROM IGS_PS_ANL_LOAD
28 WHERE rowid = x_rowid;
29
30 BEGIN
31
32 l_rowid := x_rowid;
33
34 -- Code for setting the Old and New Reference Values.
35 -- Populate Old Values.
36 Open cur_old_ref_values;
37 Fetch cur_old_ref_values INTO old_references;
38 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
39 Close cur_old_ref_values;
40 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
41 IGS_GE_MSG_STACK.ADD;
42 App_Exception.Raise_Exception;
43 Return;
44 END IF;
45 Close cur_old_ref_values;
46
47 -- Populate New Values.
48 new_references.course_cd := x_course_cd;
49 new_references.version_number := x_version_number;
50 new_references.yr_num := x_yr_num;
51 new_references.effective_start_dt := x_effective_start_dt;
52 new_references.effective_end_dt := x_effective_end_dt;
53 new_references.annual_load_val := x_annual_load_val;
54 IF (p_action = 'UPDATE') THEN
55 new_references.creation_date := old_references.creation_date;
56 new_references.created_by := old_references.created_by;
57 ELSE
58 new_references.creation_date := x_creation_date;
59 new_references.created_by := x_created_by;
60 END IF;
61 new_references.last_update_date := x_last_update_date;
62 new_references.last_updated_by := x_last_updated_by;
63 new_references.last_update_login := x_last_update_login;
64
65 END Set_Column_Values;
66
67 -- Trigger description :-
68 -- "OSS_TST".trg_cal_br_iud
69 -- BEFORE INSERT OR DELETE OR UPDATE
70 -- ON IGS_PS_ANL_LOAD
71 -- FOR EACH ROW
72
73
74 FUNCTION validate_overlapping(p_course_cd igs_ps_anl_load.course_cd%TYPE,
75 p_version_number igs_ps_anl_load.version_number%TYPE,
76 p_yr_num igs_ps_anl_load.yr_num%TYPE,
77 p_start_dt igs_ps_anl_load.effective_start_dt%TYPE,
78 p_end_dt igs_ps_anl_load.effective_end_dt%TYPE,
79 p_row_id VARCHAR2)
80 RETURN BOOLEAN AS
81 /*
82 || Created By : sarakshi
83 || Created On : 04-Feb-2002
84 || Purpose : Validates the overlapping of the effective dates.
85 || Known limitations, enhancements or remarks :
86 || Change History :
87 || Who When What
88 || (reverse chronological order - newest change first)
89 */
90
91 CURSOR cur_overlapping_u(cp_course_cd igs_ps_anl_load.course_cd%TYPE ,
92 cp_version_number igs_ps_anl_load.version_number%TYPE ,
93 cp_yr_num igs_ps_anl_load.yr_num%TYPE,
94 cp_start_dt igs_ps_anl_load.effective_start_dt%TYPE,
95 cp_row_id VARCHAR2 ) IS
96 SELECT 'X'
97 FROM igs_ps_anl_load
98 WHERE course_cd=cp_course_cd
99 AND version_number=cp_version_number
100 AND yr_num = cp_yr_num
101 AND cp_start_dt >= effective_start_dt AND cp_start_dt <= NVL(effective_end_dt,cp_start_dt)
102 AND (rowid <> cp_row_id OR (cp_row_id IS NULL));
103
104 CURSOR cur_overlapping_u1(cp_course_cd igs_ps_anl_load.course_cd%TYPE ,
105 cp_version_number igs_ps_anl_load.version_number%TYPE ,
106 cp_yr_num igs_ps_anl_load.yr_num%TYPE,
107 cp_start_dt igs_ps_anl_load.effective_start_dt%TYPE,
108 cp_end_dt igs_ps_anl_load.effective_end_dt%TYPE,
109 cp_row_id VARCHAR2 ) IS
110 SELECT 'X'
111 FROM igs_ps_anl_load
112 WHERE course_cd=cp_course_cd
113 AND version_number=cp_version_number
114 AND yr_num = cp_yr_num
115 AND (cp_end_dt >= effective_start_dt OR (cp_end_dt IS NULL)) AND cp_start_dt <= effective_start_dt
116 AND (rowid <> cp_row_id OR (cp_row_id IS NULL));
117
118 l_temp VARCHAR2(1);
119
120 BEGIN
121 --Validating if effective dates are not overlapping
122
123 -- start date overlapping
124 OPEN cur_overlapping_u(p_course_cd,p_version_number,p_yr_num,p_start_dt,p_row_id);
125 FETCH cur_overlapping_u INTO l_temp;
126 IF cur_overlapping_u%FOUND THEN
127 CLOSE cur_overlapping_u;
128 RETURN FALSE;
129 END IF;
130 CLOSE cur_overlapping_u;
131
132 --end date overlapping
133 OPEN cur_overlapping_u1(p_course_cd,p_version_number,p_yr_num,p_start_dt,p_end_dt,p_row_id);
134 FETCH cur_overlapping_u1 INTO l_temp;
135 IF cur_overlapping_u1%FOUND THEN
136 CLOSE cur_overlapping_u1;
137 RETURN FALSE;
138 END IF;
139 CLOSE cur_overlapping_u1;
140
141 RETURN TRUE;
142
143 END validate_overlapping;
144
145
146 PROCEDURE BeforeRowInsertUpdateDelete1(
147 p_inserting IN BOOLEAN DEFAULT FALSE,
148 p_updating IN BOOLEAN DEFAULT FALSE,
149 p_deleting IN BOOLEAN DEFAULT FALSE
150 ) AS
151 v_message_name varchar2(30);
152 v_course_cd IGS_PS_ANL_LOAD.course_cd%TYPE;
153 v_version_number IGS_PS_ANL_LOAD.version_number%TYPE;
154 BEGIN
155 -- Set variables.
156 IF p_deleting THEN
157 v_course_cd := old_references.course_cd;
158 v_version_number := old_references.version_number;
159 ELSE -- p_inserting or p_updating
160 v_course_cd := new_references.course_cd;
161 v_version_number := new_references.version_number;
162 END IF;
163 -- Validate the insert/update/delete.
164 IF IGS_PS_VAL_CRS.crsp_val_iud_crv_dtl (
165 v_course_cd,
166 v_version_number,
167 v_message_name) = FALSE THEN
168 Fnd_Message.Set_Name('IGS',v_message_name);
169 IGS_GE_MSG_STACK.ADD;
170 App_Exception.Raise_Exception;
171 END IF;
172 -- Validate effective start date and effective end date.
173 IF p_inserting OR p_updating THEN
174 -- Because start date is part of the key it will be set and
175 -- is not updateable, so only need to check the end date.
176 IF ( new_references.effective_end_dt IS NOT NULL AND
177 (NVL(substr(new_references.effective_end_dt,1,10),'1990/01/01') <>
178 NVL(substr(old_references.effective_end_dt,1,10),'1900/01/01'))) THEN
179 IF igs_ad_val_edtl.genp_val_strt_end_dt (
180 new_references.effective_start_dt,
181 new_references.effective_end_dt,
182 v_message_name) = FALSE THEN
183 Fnd_Message.Set_Name('IGS',v_message_name);
184 IGS_GE_MSG_STACK.ADD;
185 App_Exception.Raise_Exception;
186 END IF;
187 END IF;
188 END IF;
189
190
191 END BeforeRowInsertUpdateDelete1;
192
193 -- Trigger description :-
194 -- "OSS_TST".trg_cal_ar_iu
195 -- AFTER INSERT OR UPDATE
196 -- ON IGS_PS_ANL_LOAD
197 -- FOR EACH ROW
198
199 PROCEDURE AfterRowInsertUpdate2(
200 p_inserting IN BOOLEAN DEFAULT FALSE,
201 p_updating IN BOOLEAN DEFAULT FALSE,
202 p_deleting IN BOOLEAN DEFAULT FALSE
203 ) AS
204 v_message_name varchar2(30);
205 BEGIN
206 -- Validate IGS_PS_COURSE annual load end date.
207 IF new_references.effective_end_dt IS NULL THEN
208 -- Cannot call crsp_val_cal_end_dt because trigger will be mutating.
209 -- Save the rowid of the current row.
210 IF IGS_PS_VAL_CAL.crsp_val_cal_end_dt (
211 NEW_REFERENCES.course_cd,
212 NEW_REFERENCES.version_number,
213 NEW_REFERENCES.yr_num,
214 NEW_REFERENCES.effective_start_dt,
215 v_message_name) = FALSE THEN
216 Fnd_Message.Set_Name('IGS',v_message_name);
217 IGS_GE_MSG_STACK.ADD;
218 App_Exception.Raise_Exception;
219 END IF;
220 END IF;
221
222
223 END AfterRowInsertUpdate2;
224
225 PROCEDURE Check_Constraints (
226 Column_Name IN VARCHAR2 DEFAULT NULL,
227 Column_Value IN VARCHAR2 DEFAULT NULL
228 )
229 AS
230 BEGIN
231
232 IF column_name is null then
233 NULL;
234 ELSIF upper(Column_name) = 'COURSE_CD' then
235 new_references.course_cd := column_value;
236 ELSIF upper(Column_name) = 'YR_NUM' then
237 new_references.yr_num := igs_ge_number.to_num(column_value);
238 ELSIF upper(Column_name) = 'ANNUAL_LOAD_VAL' then
239 new_references.annual_load_val := igs_ge_number.to_num(column_value);
240 END IF;
241
242 IF upper(column_name) = 'COURSE_CD' OR
243 column_name is null Then
244 IF ( new_references.course_cd <> UPPER(new_references.course_cd) ) Then
245 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
246 IGS_GE_MSG_STACK.ADD;
247 App_Exception.Raise_Exception;
248 END IF;
249 END IF;
250
251 IF upper(column_name) = 'YR_NUM' OR
252 column_name is null Then
253 IF ( new_references.yr_num < 0 OR new_references.yr_num > 999 ) Then
254 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
255 IGS_GE_MSG_STACK.ADD;
256 App_Exception.Raise_Exception;
257 END IF;
258 END IF;
259
260 IF upper(column_name) = 'ANNUAL_LOAD_VAL' OR
261 column_name is null Then
262 IF ( new_references.annual_load_val < 0 OR new_references.annual_load_val > 9999.999 ) Then
263 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
264 IGS_GE_MSG_STACK.ADD;
265 App_Exception.Raise_Exception;
266 END IF;
267 END IF;
268 END Check_Constraints;
269
270 PROCEDURE Check_Parent_Existance AS
271 BEGIN
272
273 IF (((old_references.course_cd = new_references.course_cd) AND
274 (old_references.version_number = new_references.version_number)) OR
275 ((new_references.course_cd IS NULL) OR
276 (new_references.version_number IS NULL))) THEN
277 NULL;
278 ELSE
279 IF NOT IGS_PS_VER_PKG.Get_PK_For_Validation (
280 new_references.course_cd,
281 new_references.version_number
282 ) THEN
283 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
284 IGS_GE_MSG_STACK.ADD;
285 App_Exception.Raise_Exception;
286 END IF;
287 END IF;
288
289 END Check_Parent_Existance;
290
291 PROCEDURE Check_Child_Existance AS
292 BEGIN
293
294 IGS_PS_ANL_LOAD_U_LN_PKG.GET_FK_IGS_PS_ANL_LOAD (
295 old_references.course_cd,
296 old_references.version_number,
297 old_references.yr_num,
298 old_references.effective_start_dt
299 );
300
301 END Check_Child_Existance;
302
303 FUNCTION Get_PK_For_Validation (
304 x_course_cd IN VARCHAR2,
305 x_version_number IN NUMBER,
306 x_yr_num IN NUMBER,
307 x_effective_start_dt IN DATE
308 )
309 RETURN BOOLEAN AS
310
311 CURSOR cur_rowid IS
312 SELECT rowid
313 FROM IGS_PS_ANL_LOAD
314 WHERE course_cd = x_course_cd
315 AND version_number = x_version_number
316 AND yr_num = x_yr_num
317 AND effective_start_dt = x_effective_start_dt
318 FOR UPDATE NOWAIT;
319
320 lv_rowid cur_rowid%RowType;
321
322 BEGIN
323
324 Open cur_rowid;
325 Fetch cur_rowid INTO lv_rowid;
326 IF (cur_rowid%FOUND) THEN
327 Close cur_rowid;
328 Return (TRUE);
329 ELSE
330 Close cur_rowid;
331 Return (FALSE);
332 END IF;
333
334 END Get_PK_For_Validation;
335
336 PROCEDURE GET_FK_IGS_PS_VER (
337 x_course_cd IN VARCHAR2,
338 x_version_number IN NUMBER
339 ) AS
340
341 CURSOR cur_rowid IS
342 SELECT rowid
343 FROM IGS_PS_ANL_LOAD
344 WHERE course_cd = x_course_cd
345 AND version_number = x_version_number ;
346
347 lv_rowid cur_rowid%RowType;
348
349 BEGIN
350
351 Open cur_rowid;
352 Fetch cur_rowid INTO lv_rowid;
353 IF (cur_rowid%FOUND) THEN
354 Close cur_rowid;
355 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CAL_CRV_FK');
356 IGS_GE_MSG_STACK.ADD;
357 App_Exception.Raise_Exception;
358 Return;
359 END IF;
360 Close cur_rowid;
361
362 END GET_FK_IGS_PS_VER;
363
364 PROCEDURE Before_DML (
365 p_action IN VARCHAR2,
366 x_rowid IN VARCHAR2 DEFAULT NULL,
367 x_course_cd IN VARCHAR2 DEFAULT NULL,
368 x_version_number IN NUMBER DEFAULT NULL,
369 x_yr_num IN NUMBER DEFAULT NULL,
370 x_effective_start_dt IN DATE DEFAULT NULL,
371 x_effective_end_dt IN DATE DEFAULT NULL,
372 x_annual_load_val IN NUMBER DEFAULT NULL,
373 x_creation_date IN DATE DEFAULT NULL,
374 x_created_by IN NUMBER DEFAULT NULL,
375 x_last_update_date IN DATE DEFAULT NULL,
376 x_last_updated_by IN NUMBER DEFAULT NULL,
377 x_last_update_login IN NUMBER DEFAULT NULL
378 ) AS
379 BEGIN
380
381 Set_Column_Values (
382 p_action,
383 x_rowid,
384 x_course_cd,
385 x_version_number,
386 x_yr_num,
387 x_effective_start_dt,
388 x_effective_end_dt,
389 x_annual_load_val,
390 x_creation_date,
391 x_created_by,
392 x_last_update_date,
393 x_last_updated_by,
394 x_last_update_login
395 );
396
397 --Added by sarakshi bug#2473015, to validate the logic of dates overlap
398 IF p_action IN ( 'INSERT', 'VALIDATE_INSERT','UPDATE','VALIDATE_UPDATE') THEN
399 IF NOT validate_overlapping(x_course_cd , x_version_number,x_yr_num ,
400 x_effective_start_dt , x_effective_end_dt,x_rowid ) THEN
401 fnd_message.set_name('IGS','IGS_PS_OVERLAP_PERIODS');
402 igs_ge_msg_stack.add;
403 app_exception.raise_exception;
404 END IF;
405 END IF;
406
407
408 IF (p_action = 'INSERT') THEN
409 -- Call all the procedures related to Before Insert.
410 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
411 IF Get_PK_For_Validation (
412 new_references.course_cd,
413 new_references.version_number,
414 new_references.yr_num,
415 new_references.effective_start_dt) THEN
416 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
417 IGS_GE_MSG_STACK.ADD;
418 App_Exception.Raise_Exception;
419 END IF;
420 Check_Constraints;
421 Check_Parent_Existance;
422 ELSIF (p_action = 'UPDATE') THEN
423 -- Call all the procedures related to Before Update.
424 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
425 Check_Constraints;
426 Check_Parent_Existance;
427 ELSIF (p_action = 'DELETE') THEN
428 -- Call all the procedures related to Before Delete.
429 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
430 Check_Child_Existance;
431 ELSIF (p_action = 'VALIDATE_INSERT') THEN
432 IF Get_PK_For_Validation (
433 new_references.course_cd,
434 new_references.version_number,
435 new_references.yr_num,
436 new_references.effective_start_dt) THEN
437 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
438 IGS_GE_MSG_STACK.ADD;
439 App_Exception.Raise_Exception;
440 END IF;
441 Check_Constraints;
442 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
443 Check_Constraints;
444 ELSIF (p_action = 'VALIDATE_DELETE') THEN
445 Check_Child_Existance;
446 END IF;
447
448 END Before_DML;
449
450 PROCEDURE After_DML (
451 p_action IN VARCHAR2,
452 x_rowid IN VARCHAR2
453 ) AS
454 BEGIN
455
456 l_rowid := x_rowid;
457
458 END After_DML;
459
460 procedure INSERT_ROW (
461 X_ROWID in out NOCOPY VARCHAR2,
462 X_VERSION_NUMBER in NUMBER,
463 X_COURSE_CD in VARCHAR2,
464 X_YR_NUM in NUMBER,
465 X_EFFECTIVE_START_DT in DATE,
466 X_EFFECTIVE_END_DT in DATE,
467 X_ANNUAL_LOAD_VAL in NUMBER,
468 X_MODE in VARCHAR2 default 'R'
469 ) AS
470 cursor C is select ROWID from IGS_PS_ANL_LOAD
471 where VERSION_NUMBER = X_VERSION_NUMBER
472 and COURSE_CD = X_COURSE_CD
473 and YR_NUM = X_YR_NUM
474 and EFFECTIVE_START_DT = X_EFFECTIVE_START_DT;
475 X_LAST_UPDATE_DATE DATE;
476 X_LAST_UPDATED_BY NUMBER;
477 X_LAST_UPDATE_LOGIN NUMBER;
478 begin
479 X_LAST_UPDATE_DATE := SYSDATE;
480 if(X_MODE = 'I') then
481 X_LAST_UPDATED_BY := 1;
482 X_LAST_UPDATE_LOGIN := 0;
483 elsif (X_MODE = 'R') then
484 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
485 if X_LAST_UPDATED_BY is NULL then
486 X_LAST_UPDATED_BY := -1;
487 end if;
488 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
489 if X_LAST_UPDATE_LOGIN is NULL then
490 X_LAST_UPDATE_LOGIN := -1;
491 end if;
492 else
493 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
494 IGS_GE_MSG_STACK.ADD;
495 app_exception.raise_exception;
496 end if;
497
498 Before_DML (
499 p_action => 'INSERT',
500 x_rowid => X_ROWID,
501 x_course_cd => X_COURSE_CD,
502 x_version_number => X_VERSION_NUMBER,
503 x_yr_num => X_YR_NUM,
504 x_effective_start_dt => X_EFFECTIVE_START_DT,
505 x_effective_end_dt => X_EFFECTIVE_END_DT ,
506 x_annual_load_val => X_ANNUAL_LOAD_VAL ,
507 x_creation_date => X_LAST_UPDATE_DATE ,
508 x_created_by => X_LAST_UPDATED_BY ,
509 x_last_update_date => X_LAST_UPDATE_DATE ,
510 x_last_updated_by => X_LAST_UPDATED_BY ,
511 x_last_update_login => X_LAST_UPDATE_LOGIN
512 );
513
514 insert into IGS_PS_ANL_LOAD (
515 VERSION_NUMBER,
516 COURSE_CD,
517 YR_NUM,
518 EFFECTIVE_START_DT,
519 EFFECTIVE_END_DT,
520 ANNUAL_LOAD_VAL,
521 CREATION_DATE,
522 CREATED_BY,
523 LAST_UPDATE_DATE,
524 LAST_UPDATED_BY,
525 LAST_UPDATE_LOGIN
526 ) values (
527 NEW_REFERENCES.VERSION_NUMBER,
528 NEW_REFERENCES.COURSE_CD,
529 NEW_REFERENCES.YR_NUM,
530 NEW_REFERENCES.EFFECTIVE_START_DT,
531 NEW_REFERENCES.EFFECTIVE_END_DT,
532 NEW_REFERENCES.ANNUAL_LOAD_VAL,
533 X_LAST_UPDATE_DATE,
534 X_LAST_UPDATED_BY,
535 X_LAST_UPDATE_DATE,
536 X_LAST_UPDATED_BY,
537 X_LAST_UPDATE_LOGIN
538 );
539
540 open c;
541 fetch c into X_ROWID;
542 if (c%notfound) then
543 close c;
544 raise no_data_found;
545 end if;
546 close c;
547
548 After_DML (
549 p_action => 'INSERT',
550 x_rowid => X_ROWID
551 );
552
553 end INSERT_ROW;
554
555 procedure LOCK_ROW (
556 X_ROWID IN VARCHAR2,
557 X_VERSION_NUMBER in NUMBER,
558 X_COURSE_CD in VARCHAR2,
559 X_YR_NUM in NUMBER,
560 X_EFFECTIVE_START_DT in DATE,
561 X_EFFECTIVE_END_DT in DATE,
562 X_ANNUAL_LOAD_VAL in NUMBER
563 ) AS
564 cursor c1 is select
565 EFFECTIVE_END_DT,
566 ANNUAL_LOAD_VAL
567 from IGS_PS_ANL_LOAD
568 where ROWID = X_ROWID
569 for update nowait;
570 tlinfo c1%rowtype;
571
572 begin
573 open c1;
574 fetch c1 into tlinfo;
575 if (c1%notfound) then
576 close c1;
577 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
578 IGS_GE_MSG_STACK.ADD;
579 app_exception.raise_exception;
580 return;
581 end if;
582 close c1;
583
584 if ( ((tlinfo.EFFECTIVE_END_DT = X_EFFECTIVE_END_DT)
585 OR ((tlinfo.EFFECTIVE_END_DT is null)
586 AND (X_EFFECTIVE_END_DT is null)))
587 AND (tlinfo.ANNUAL_LOAD_VAL = X_ANNUAL_LOAD_VAL)
588 ) then
589 null;
590 else
591 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
592 IGS_GE_MSG_STACK.ADD;
593 app_exception.raise_exception;
594 end if;
595 return;
596 end LOCK_ROW;
597
598 procedure UPDATE_ROW (
599 X_ROWID IN VARCHAR2,
600 X_VERSION_NUMBER in NUMBER,
601 X_COURSE_CD in VARCHAR2,
602 X_YR_NUM in NUMBER,
603 X_EFFECTIVE_START_DT in DATE,
604 X_EFFECTIVE_END_DT in DATE,
605 X_ANNUAL_LOAD_VAL in NUMBER,
606 X_MODE in VARCHAR2 default 'R'
607 ) AS
608 X_LAST_UPDATE_DATE DATE;
609 X_LAST_UPDATED_BY NUMBER;
610 X_LAST_UPDATE_LOGIN NUMBER;
611 begin
612 X_LAST_UPDATE_DATE := SYSDATE;
613 if(X_MODE = 'I') then
614 X_LAST_UPDATED_BY := 1;
615 X_LAST_UPDATE_LOGIN := 0;
616 elsif (X_MODE = 'R') then
617 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
618 if X_LAST_UPDATED_BY is NULL then
619 X_LAST_UPDATED_BY := -1;
620 end if;
621 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
622 if X_LAST_UPDATE_LOGIN is NULL then
623 X_LAST_UPDATE_LOGIN := -1;
624 end if;
625 else
626 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
627 IGS_GE_MSG_STACK.ADD;
628 app_exception.raise_exception;
629 end if;
630
631 Before_DML (
632 p_action => 'UPDATE',
633 x_rowid => X_ROWID,
634 x_course_cd => X_COURSE_CD,
635 x_version_number => X_VERSION_NUMBER,
636 x_yr_num => X_YR_NUM,
637 x_effective_start_dt => X_EFFECTIVE_START_DT,
638 x_effective_end_dt => X_EFFECTIVE_END_DT ,
639 x_annual_load_val => X_ANNUAL_LOAD_VAL ,
640 x_creation_date => X_LAST_UPDATE_DATE ,
641 x_created_by => X_LAST_UPDATED_BY ,
642 x_last_update_date => X_LAST_UPDATE_DATE ,
643 x_last_updated_by => X_LAST_UPDATED_BY ,
644 x_last_update_login => X_LAST_UPDATE_LOGIN
645 );
646
647 update IGS_PS_ANL_LOAD set
648 EFFECTIVE_END_DT = NEW_REFERENCES.EFFECTIVE_END_DT,
649 ANNUAL_LOAD_VAL = NEW_REFERENCES.ANNUAL_LOAD_VAL,
650 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
651 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
652 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
653 where ROWID = X_ROWID;
654
655 if (sql%notfound) then
656 raise no_data_found;
657 end if;
658
659 After_DML (
660 p_action => 'UPDATE',
661 x_rowid => X_ROWID
662 );
663
664 end UPDATE_ROW;
665
666 procedure ADD_ROW (
667 X_ROWID in out NOCOPY VARCHAR2,
668 X_VERSION_NUMBER in NUMBER,
669 X_COURSE_CD in VARCHAR2,
670 X_YR_NUM in NUMBER,
671 X_EFFECTIVE_START_DT in DATE,
672 X_EFFECTIVE_END_DT in DATE,
673 X_ANNUAL_LOAD_VAL in NUMBER,
674 X_MODE in VARCHAR2 default 'R'
675 ) AS
676 cursor c1 is select rowid from IGS_PS_ANL_LOAD
677 where VERSION_NUMBER = X_VERSION_NUMBER
678 and COURSE_CD = X_COURSE_CD
679 and YR_NUM = X_YR_NUM
680 and EFFECTIVE_START_DT = X_EFFECTIVE_START_DT
681 ;
682 begin
683 open c1;
684 fetch c1 into X_ROWID;
685 if (c1%notfound) then
686 close c1;
687 INSERT_ROW (
688 X_ROWID,
689 X_VERSION_NUMBER,
690 X_COURSE_CD,
691 X_YR_NUM,
692 X_EFFECTIVE_START_DT,
693 X_EFFECTIVE_END_DT,
694 X_ANNUAL_LOAD_VAL,
695 X_MODE);
696 return;
697 end if;
698 close c1;
699 UPDATE_ROW (
700 X_ROWID,
701 X_VERSION_NUMBER,
702 X_COURSE_CD,
703 X_YR_NUM,
704 X_EFFECTIVE_START_DT,
705 X_EFFECTIVE_END_DT,
706 X_ANNUAL_LOAD_VAL,
707 X_MODE);
708 end ADD_ROW;
709
710 procedure DELETE_ROW (
711 X_ROWID in VARCHAR2
712 ) AS
713 begin
714 Before_DML (
715 p_action => 'DELETE',
716 x_rowid => X_ROWID
717 );
718
719 delete from IGS_PS_ANL_LOAD
720 where ROWID = X_ROWID;
721 if (sql%notfound) then
722 raise no_data_found;
723 end if;
724
725 After_DML (
726 p_action => 'DELETE',
727 x_rowid => X_ROWID
728 );
729
730 end DELETE_ROW;
731
732 end IGS_PS_ANL_LOAD_PKG;