[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_FIELD_STUDY_PKG
Source
1 package body IGS_PS_FIELD_STUDY_PKG AS
2 /* $Header: IGSPI13B.pls 120.1 2006/07/25 15:11:10 sommukhe noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_FIELD_STUDY%RowType;
6 new_references IGS_PS_FIELD_STUDY%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_course_cd IN VARCHAR2 DEFAULT NULL,
12 x_version_number IN NUMBER DEFAULT NULL,
13 x_field_of_study IN VARCHAR2 DEFAULT NULL,
14 x_major_field_ind IN VARCHAR2 DEFAULT NULL,
15 x_percentage IN NUMBER DEFAULT NULL,
16 x_creation_date IN DATE DEFAULT NULL,
17 x_created_by IN NUMBER DEFAULT NULL,
18 x_last_update_date IN DATE DEFAULT NULL,
19 x_last_updated_by IN NUMBER DEFAULT NULL,
20 x_last_update_login IN NUMBER DEFAULT NULL
21 ) AS
22
23 CURSOR cur_old_ref_values IS
24 SELECT *
25 FROM IGS_PS_FIELD_STUDY
26 WHERE rowid = x_rowid;
27
28 BEGIN
29
30 l_rowid := x_rowid;
31
32 -- Code for setting the Old and New Reference Values.
33 -- Populate Old Values.
34 Open cur_old_ref_values;
35 Fetch cur_old_ref_values INTO old_references;
36 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
37 Close cur_old_ref_values;
38 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
39 IGS_GE_MSG_STACK.ADD;
40 App_Exception.Raise_Exception;
41 Return;
42 END IF;
43 Close cur_old_ref_values;
44
45 -- Populate New Values.
46 new_references.course_cd := x_course_cd;
47 new_references.version_number := x_version_number;
48 new_references.field_of_study := x_field_of_study;
49 new_references.major_field_ind := x_major_field_ind;
50 new_references.percentage := x_percentage;
51 IF (p_action = 'UPDATE') THEN
52 new_references.creation_date := old_references.creation_date;
53 new_references.created_by := old_references.created_by;
54 ELSE
55 new_references.creation_date := x_creation_date;
56 new_references.created_by := x_created_by;
57 END IF;
58 new_references.last_update_date := x_last_update_date;
59 new_references.last_updated_by := x_last_updated_by;
60 new_references.last_update_login := x_last_update_login;
61
62 END Set_Column_Values;
63
64 -- Trigger description :-
65 -- "OSS_TST".trg_cfos_br_iud
66 -- BEFORE INSERT OR DELETE OR UPDATE
67 -- ON IGS_PS_FIELD_STUDY
68 -- FOR EACH ROW
69
70 PROCEDURE BeforeRowInsertUpdateDelete1(
71 p_inserting IN BOOLEAN DEFAULT FALSE,
72 p_updating IN BOOLEAN DEFAULT FALSE,
73 p_deleting IN BOOLEAN DEFAULT FALSE
74 ) AS
75 v_message_name varchar2(30);
76 v_course_cd IGS_PS_FIELD_STUDY.course_cd%TYPE;
77 v_version_number IGS_PS_FIELD_STUDY.version_number%TYPE;
78 v_major_field_ind IGS_PS_FIELD_STUDY.major_field_ind%TYPE;
79 v_percentage IGS_PS_FIELD_STUDY.percentage%TYPE;
80 BEGIN
81
82 -- Set variables.
83 IF p_deleting THEN
84 v_course_cd := old_references.course_cd;
85 v_version_number := old_references.version_number;
86 ELSE -- p_inserting or p_updating
87 v_course_cd := new_references.course_cd;
88 v_version_number := new_references.version_number;
89 END IF;
90 -- Validate the insert/update/delete.
91 IF IGS_PS_VAL_CRS.crsp_val_iud_crv_dtl (
92 v_course_cd,
93 v_version_number,
94 v_message_name) = FALSE THEN
95 Fnd_Message.Set_Name('IGS',v_message_name);
96 IGS_GE_MSG_STACK.ADD;
97 App_Exception.Raise_Exception;
98 END IF;
99 -- Validate field of study. Field of study is not updateable.
100 IF p_inserting THEN
101 IF IGS_PS_VAL_CFOS.crsp_val_cfos_fos (
102 new_references.field_of_study,
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 -- Insert history record on update.
110 IF p_updating THEN
111 IF old_references.percentage <> new_references.percentage OR
112 old_references.major_field_ind <> new_references.major_field_ind THEN
113 SELECT DECODE(old_references.percentage,new_references.percentage,NULL,old_references.percentage),
114 DECODE(old_references.major_field_ind,new_references.major_field_ind,NULL,old_references.major_field_ind)
115 INTO v_percentage,
116 v_major_field_ind
117 FROM dual;
118 -- Create history record for update
119 IGS_PS_GEN_002.CRSP_INS_CFOS_HIST(
120 old_references.course_cd,
121 old_references.version_number,
122 old_references.field_of_study,
123 old_references.last_update_date,
124 new_references.last_update_date,
125 old_references.last_updated_by,
126 v_percentage,
127 v_major_field_ind);
128 END IF;
129 END IF;
130 IF p_deleting THEN
131 -- Create history record for deletion
132 IGS_PS_GEN_002.CRSP_INS_CFOS_HIST(
133 old_references.course_cd,
134 old_references.version_number,
135 old_references.field_of_study,
136 old_references.last_update_date,
137 SYSDATE,
138 old_references.last_updated_by,
139 old_references.percentage,
140 old_references.major_field_ind);
141 END IF;
142
143
144 END BeforeRowInsertUpdateDelete1;
145
146 PROCEDURE Check_Constraints (
147 Column_Name IN VARCHAR2 DEFAULT NULL,
148 Column_Value IN VARCHAR2 DEFAULT NULL
149 )
150 AS
151 BEGIN
152
153 IF column_name is null then
154 NULL;
155 ELSIF upper(Column_name) = 'COURSE_CD' then
156 new_references.course_cd := column_value;
157 ELSIF upper(Column_name) = 'FIELD_OF_STUDY' then
158 new_references.field_of_study := column_value;
159 ELSIF upper(Column_name) = 'MAJOR_FIELD_IND' then
160 new_references.major_field_ind := column_value;
161 ELSIF upper(Column_name) = 'PERCENTAGE' then
162 new_references.percentage := IGS_GE_NUMBER.TO_NUM(column_value);
163 END IF;
164
165 END Check_Constraints;
166
167 PROCEDURE Check_Parent_Existance AS
168 BEGIN
169
170 IF (((old_references.course_cd = new_references.course_cd) AND
171 (old_references.version_number = new_references.version_number)) OR
172 ((new_references.course_cd IS NULL) OR
173 (new_references.version_number IS NULL))) THEN
174 NULL;
175 ELSE
176 IF NOT IGS_PS_VER_PKG.Get_PK_For_Validation (
177 new_references.course_cd,
178 new_references.version_number
179 ) THEN
180 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
181 IGS_GE_MSG_STACK.ADD;
182 App_Exception.Raise_Exception;
183 END IF;
184 END IF;
185
186 IF (((old_references.field_of_study = new_references.field_of_study)) OR
187 ((new_references.field_of_study IS NULL))) THEN
188 NULL;
189 ELSE
190 IF NOT IGS_PS_FLD_OF_STUDY_PKG.Get_PK_For_Validation (
191 new_references.field_of_study
192 ) THEN
193 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
194 IGS_GE_MSG_STACK.ADD;
195 App_Exception.Raise_Exception;
196 END IF;
197 END IF;
198
199 END Check_Parent_Existance;
200
201 FUNCTION Get_PK_For_Validation (
202 x_course_cd IN VARCHAR2,
203 x_version_number IN NUMBER,
204 x_field_of_study IN VARCHAR2
205 )
206 RETURN BOOLEAN AS
207
208 CURSOR cur_rowid IS
209 SELECT rowid
210 FROM IGS_PS_FIELD_STUDY
211 WHERE course_cd = x_course_cd
212 AND version_number = x_version_number
213 AND field_of_study = x_field_of_study
214 FOR UPDATE NOWAIT;
215 lv_rowid cur_rowid%RowType;
216
217 BEGIN
218
219 Open cur_rowid;
220 Fetch cur_rowid INTO lv_rowid;
221 IF (cur_rowid%FOUND) THEN
222 Close cur_rowid;
223 Return (TRUE);
224 ELSE
225 Close cur_rowid;
226 Return (FALSE);
227 END IF;
228
229 END Get_PK_For_Validation;
230
231 PROCEDURE GET_FK_IGS_PS_VER (
232 x_course_cd IN VARCHAR2,
233 x_version_number IN NUMBER
234 ) AS
235
236 CURSOR cur_rowid IS
237 SELECT rowid
238 FROM IGS_PS_FIELD_STUDY
239 WHERE course_cd = x_course_cd
240 AND version_number = x_version_number ;
241
242 lv_rowid cur_rowid%RowType;
243
244 BEGIN
245
246 Open cur_rowid;
247 Fetch cur_rowid INTO lv_rowid;
248 IF (cur_rowid%FOUND) THEN
249 Close cur_rowid;
250 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CFOS_CRV_FK');
251 IGS_GE_MSG_STACK.ADD;
252 App_Exception.Raise_Exception;
253 Return;
254 END IF;
255 Close cur_rowid;
256
257 END GET_FK_IGS_PS_VER;
258
259 PROCEDURE Before_DML (
260 p_action IN VARCHAR2,
261 x_rowid IN VARCHAR2 DEFAULT NULL,
262 x_course_cd IN VARCHAR2 DEFAULT NULL,
263 x_version_number IN NUMBER DEFAULT NULL,
264 x_field_of_study IN VARCHAR2 DEFAULT NULL,
265 x_major_field_ind IN VARCHAR2 DEFAULT NULL,
266 x_percentage IN NUMBER DEFAULT NULL,
267 x_creation_date IN DATE DEFAULT NULL,
268 x_created_by IN NUMBER DEFAULT NULL,
269 x_last_update_date IN DATE DEFAULT NULL,
270 x_last_updated_by IN NUMBER DEFAULT NULL,
271 x_last_update_login IN NUMBER DEFAULT NULL
272 ) AS
273 BEGIN
274
275 Set_Column_Values (
276 p_action,
277 x_rowid,
278 x_course_cd,
279 x_version_number,
280 x_field_of_study,
281 x_major_field_ind,
282 x_percentage,
283 x_creation_date,
284 x_created_by,
285 x_last_update_date,
286 x_last_updated_by,
287 x_last_update_login
288 );
289
290 IF (p_action = 'INSERT') THEN
291 -- Call all the procedures related to Before Insert.
292 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
293 IF Get_PK_For_Validation (
294 new_references.course_cd ,
295 new_references.version_number ,
296 new_references.field_of_study) THEN
297 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
298 IGS_GE_MSG_STACK.ADD;
299 App_Exception.Raise_Exception;
300 END IF;
301 Check_Constraints;
302 Check_Parent_Existance;
303 ELSIF (p_action = 'UPDATE') THEN
304 -- Call all the procedures related to Before Update.
305 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
306 Check_Constraints;
307 Check_Parent_Existance;
308 ELSIF (p_action = 'DELETE') THEN
309 -- Call all the procedures related to Before Delete.
310 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
311 ELSIF (p_action = 'VALIDATE_INSERT') THEN
312 IF Get_PK_For_Validation (
313 new_references.course_cd ,
314 new_references.version_number ,
315 new_references.field_of_study) THEN
316 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
317 IGS_GE_MSG_STACK.ADD;
318 App_Exception.Raise_Exception;
319 END IF;
320 Check_Constraints;
321 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
322 Check_Constraints;
323 END IF;
324
325 END Before_DML;
326
327 PROCEDURE After_DML (
328 p_action IN VARCHAR2,
329 x_rowid IN VARCHAR2
330 ) AS
331 BEGIN
332
333 l_rowid := x_rowid;
334
335 END After_DML;
336
337 procedure INSERT_ROW (
338 X_ROWID in out NOCOPY VARCHAR2,
339 X_COURSE_CD in VARCHAR2,
340 X_FIELD_OF_STUDY in VARCHAR2,
341 X_VERSION_NUMBER in NUMBER,
342 X_MAJOR_FIELD_IND in VARCHAR2,
343 X_PERCENTAGE in NUMBER,
344 X_MODE in VARCHAR2 default 'R'
345 ) AS
346 cursor C is select ROWID from IGS_PS_FIELD_STUDY
347 where COURSE_CD = X_COURSE_CD
348 and FIELD_OF_STUDY = X_FIELD_OF_STUDY
349 and VERSION_NUMBER = X_VERSION_NUMBER;
350 X_LAST_UPDATE_DATE DATE;
351 X_LAST_UPDATED_BY NUMBER;
352 X_LAST_UPDATE_LOGIN NUMBER;
353 begin
354 X_LAST_UPDATE_DATE := SYSDATE;
355 if(X_MODE = 'I') then
356 X_LAST_UPDATED_BY := 1;
357 X_LAST_UPDATE_LOGIN := 0;
358 elsif (X_MODE = 'R') then
359 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
360 if X_LAST_UPDATED_BY is NULL then
361 X_LAST_UPDATED_BY := -1;
362 end if;
363 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
364 if X_LAST_UPDATE_LOGIN is NULL then
365 X_LAST_UPDATE_LOGIN := -1;
366 end if;
367 else
368 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
369 IGS_GE_MSG_STACK.ADD;
370 app_exception.raise_exception;
371 end if;
372 Before_DML (
373 p_action => 'INSERT',
374 x_rowid => X_ROWID,
375 x_course_cd => X_COURSE_CD,
376 x_version_number => X_VERSION_NUMBER,
377 x_field_of_study => X_FIELD_OF_STUDY,
378 x_major_field_ind => NVL(X_MAJOR_FIELD_IND,'Y') ,
379 x_percentage => X_PERCENTAGE ,
380 x_creation_date => X_LAST_UPDATE_DATE ,
381 x_created_by => X_LAST_UPDATED_BY ,
382 x_last_update_date => X_LAST_UPDATE_DATE ,
383 x_last_updated_by => X_LAST_UPDATED_BY ,
384 x_last_update_login => X_LAST_UPDATE_LOGIN
385 );
386
387 insert into IGS_PS_FIELD_STUDY (
388 COURSE_CD,
389 VERSION_NUMBER,
390 FIELD_OF_STUDY,
391 MAJOR_FIELD_IND,
392 PERCENTAGE,
393 CREATION_DATE,
394 CREATED_BY,
395 LAST_UPDATE_DATE,
396 LAST_UPDATED_BY,
397 LAST_UPDATE_LOGIN
398 ) values (
399 NEW_REFERENCES.COURSE_CD,
400 NEW_REFERENCES.VERSION_NUMBER,
401 NEW_REFERENCES.FIELD_OF_STUDY,
402 NEW_REFERENCES.MAJOR_FIELD_IND,
403 NEW_REFERENCES.PERCENTAGE,
404 X_LAST_UPDATE_DATE,
405 X_LAST_UPDATED_BY,
406 X_LAST_UPDATE_DATE,
407 X_LAST_UPDATED_BY,
408 X_LAST_UPDATE_LOGIN
409 );
410
411 open c;
412 fetch c into X_ROWID;
413 if (c%notfound) then
414 close c;
415 raise no_data_found;
416 end if;
417 close c;
418 After_DML (
419 p_action => 'INSERT',
420 x_rowid => X_ROWID
421 );
422 end INSERT_ROW;
423
424 procedure LOCK_ROW (
425 X_ROWID IN VARCHAR2,
426 X_COURSE_CD in VARCHAR2,
427 X_FIELD_OF_STUDY in VARCHAR2,
428 X_VERSION_NUMBER in NUMBER,
429 X_MAJOR_FIELD_IND in VARCHAR2,
430 X_PERCENTAGE in NUMBER
431 ) AS
432 cursor c1 is select
433 MAJOR_FIELD_IND,
434 PERCENTAGE
435 from IGS_PS_FIELD_STUDY
436 where ROWID = X_ROWID
437 for update nowait;
438 tlinfo c1%rowtype;
439
440 begin
441 open c1;
442 fetch c1 into tlinfo;
443 if (c1%notfound) then
444 close c1;
445 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
446 IGS_GE_MSG_STACK.ADD;
447 app_exception.raise_exception;
448 return;
449 end if;
450 close c1;
451
452 if ( (tlinfo.MAJOR_FIELD_IND = X_MAJOR_FIELD_IND)
453 AND(tlinfo.PERCENTAGE IS NULL OR tlinfo.PERCENTAGE = X_PERCENTAGE)
454 ) then
455 null;
456 else
457 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
458 IGS_GE_MSG_STACK.ADD;
459 app_exception.raise_exception;
460 end if;
461 return;
462 end LOCK_ROW;
463
464 procedure UPDATE_ROW (
465 X_ROWID IN VARCHAR2,
466 X_COURSE_CD in VARCHAR2,
467 X_FIELD_OF_STUDY in VARCHAR2,
468 X_VERSION_NUMBER in NUMBER,
469 X_MAJOR_FIELD_IND in VARCHAR2,
470 X_PERCENTAGE in NUMBER,
471 X_MODE in VARCHAR2 default 'R'
472 ) AS
473 X_LAST_UPDATE_DATE DATE;
474 X_LAST_UPDATED_BY NUMBER;
475 X_LAST_UPDATE_LOGIN NUMBER;
476 begin
477 X_LAST_UPDATE_DATE := SYSDATE;
478 if(X_MODE = 'I') then
479 X_LAST_UPDATED_BY := 1;
480 X_LAST_UPDATE_LOGIN := 0;
481 elsif (X_MODE = 'R') then
482 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
483 if X_LAST_UPDATED_BY is NULL then
484 X_LAST_UPDATED_BY := -1;
485 end if;
486 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
487 if X_LAST_UPDATE_LOGIN is NULL then
488 X_LAST_UPDATE_LOGIN := -1;
489 end if;
490 else
491 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
492 IGS_GE_MSG_STACK.ADD;
493 app_exception.raise_exception;
494 end if;
495 Before_DML (
496 p_action => 'UPDATE',
497 x_rowid => X_ROWID,
498 x_course_cd => X_COURSE_CD,
499 x_version_number => X_VERSION_NUMBER,
500 x_field_of_study => X_FIELD_OF_STUDY,
501 x_major_field_ind => X_MAJOR_FIELD_IND ,
502 x_percentage => X_PERCENTAGE ,
503 x_creation_date => X_LAST_UPDATE_DATE ,
504 x_created_by => X_LAST_UPDATED_BY ,
505 x_last_update_date => X_LAST_UPDATE_DATE ,
506 x_last_updated_by => X_LAST_UPDATED_BY ,
507 x_last_update_login => X_LAST_UPDATE_LOGIN
508 );
509
510 update IGS_PS_FIELD_STUDY set
511 MAJOR_FIELD_IND = NEW_REFERENCES.MAJOR_FIELD_IND,
512 PERCENTAGE = NEW_REFERENCES.PERCENTAGE,
513 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
514 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
515 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
516 where ROWID = X_ROWID
517 ;
518 if (sql%notfound) then
519 raise no_data_found;
520 end if;
521 After_DML (
522 p_action => 'UPDATE',
523 x_rowid => X_ROWID
524 );
525 end UPDATE_ROW;
526
527 procedure ADD_ROW (
528 X_ROWID in out NOCOPY VARCHAR2,
529 X_COURSE_CD in VARCHAR2,
530 X_FIELD_OF_STUDY in VARCHAR2,
531 X_VERSION_NUMBER in NUMBER,
532 X_MAJOR_FIELD_IND in VARCHAR2,
533 X_PERCENTAGE in NUMBER,
534 X_MODE in VARCHAR2 default 'R'
535 ) AS
536 cursor c1 is select rowid from IGS_PS_FIELD_STUDY
537 where COURSE_CD = X_COURSE_CD
538 and FIELD_OF_STUDY = X_FIELD_OF_STUDY
539 and VERSION_NUMBER = X_VERSION_NUMBER
540 ;
541 begin
542 open c1;
543 fetch c1 into X_ROWID;
544 if (c1%notfound) then
545 close c1;
546 INSERT_ROW (
547 X_ROWID,
548 X_COURSE_CD,
549 X_FIELD_OF_STUDY,
550 X_VERSION_NUMBER,
551 X_MAJOR_FIELD_IND,
552 X_PERCENTAGE,
553 X_MODE);
554 return;
555 end if;
556 close c1;
557 UPDATE_ROW (
558 X_ROWID,
559 X_COURSE_CD,
560 X_FIELD_OF_STUDY,
561 X_VERSION_NUMBER,
562 X_MAJOR_FIELD_IND,
563 X_PERCENTAGE,
564 X_MODE);
565 end ADD_ROW;
566
567 procedure DELETE_ROW (
568 X_ROWID in VARCHAR2
569 ) AS
570 begin
571 Before_DML (
572 p_action => 'DELETE',
573 x_rowid => X_ROWID
574 );
575 delete from IGS_PS_FIELD_STUDY
576 where ROWID = X_ROWID;
577 if (sql%notfound) then
578 raise no_data_found;
579 end if;
580 After_DML (
581 p_action => 'DELETE',
582 x_rowid => X_ROWID
583 );
584 end DELETE_ROW;
585
586 end IGS_PS_FIELD_STUDY_PKG;