1 PACKAGE BODY igs_ps_unit_fld_stdy_pkg AS
2 /* $Header: IGSPI0GB.pls 115.7 2003/03/21 07:58:28 sarakshi ship $ */
3 /*************************************************************
4 Created By : kdande@in
5 Date Created By :2000/05/11
6 Purpose :
7 Know limitations, enhancements or remarks
8 Change History
9 Who When What
10
11 (reverse chronological order - newest change first)
12 ***************************************************************/
13 l_rowid VARCHAR2(25);
14 old_references igs_ps_unit_fld_stdy%RowType;
15 new_references igs_ps_unit_fld_stdy%RowType;
16
17 PROCEDURE Set_Column_Values (
18 p_action IN VARCHAR2,
19 x_rowid IN VARCHAR2 DEFAULT NULL,
20 x_unit_field_of_study_id IN NUMBER DEFAULT NULL,
21 x_unit_code IN VARCHAR2 DEFAULT NULL,
22 x_version_number IN NUMBER DEFAULT NULL,
23 x_field_of_study IN VARCHAR2 DEFAULT NULL,
24 x_creation_date IN DATE DEFAULT NULL,
25 x_created_by IN NUMBER DEFAULT NULL,
26 x_last_update_date IN DATE DEFAULT NULL,
27 x_last_updated_by IN NUMBER DEFAULT NULL,
28 x_last_update_login IN NUMBER DEFAULT NULL
29 ) AS
30 /*************************************************************
31 Created By : kdande@in
32 Date Created By :2000/05/11
33 Purpose :
34 Know limitations, enhancements or remarks
35 Change History
36 Who When What
37
38 (reverse chronological order - newest change first)
39 ***************************************************************/
40
41 CURSOR cur_old_ref_values IS
42 SELECT *
43 FROM IGS_PS_UNIT_FLD_STDY
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 Close cur_old_ref_values;
56 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
57 IGS_GE_MSG_STACK.ADD;
58 App_Exception.Raise_Exception;
59 Return;
60 END IF;
61 Close cur_old_ref_values;
62
63 -- Populate New Values.
64 new_references.unit_field_of_study_id := x_unit_field_of_study_id;
65 new_references.unit_code := x_unit_code;
66 new_references.version_number := x_version_number;
67 new_references.field_of_study := x_field_of_study;
68 IF (p_action = 'UPDATE') THEN
69 new_references.creation_date := old_references.creation_date;
70 new_references.created_by := old_references.created_by;
71 ELSE
72 new_references.creation_date := x_creation_date;
73 new_references.created_by := x_created_by;
74 END IF;
75 new_references.last_update_date := x_last_update_date;
76 new_references.last_updated_by := x_last_updated_by;
77 new_references.last_update_login := x_last_update_login;
78
79 END Set_Column_Values;
80
81 PROCEDURE Check_Uniqueness AS
82 /*************************************************************
83 Created By : kdande@in
84 Date Created By :2000/05/11
85 Purpose :
86 Know limitations, enhancements or remarks
87 Change History
88 Who When What
89
90 (reverse chronological order - newest change first)
91 ***************************************************************/
92 BEGIN
93 IF Get_Uk_For_Validation (
94 new_references.field_of_study,
95 new_references.unit_code,
96 new_references.version_number
97 ) THEN
98 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
99 IGS_GE_MSG_STACK.ADD;
100 APP_EXCEPTION.RAISE_EXCEPTION;
101 END IF;
102 END Check_Uniqueness ;
103
104 PROCEDURE Check_Parent_Existance AS
105 /*************************************************************
106 Created By : kdande@in
107 Date Created By :2000/05/11
108 Purpose :
109 Know limitations, enhancements or remarks
110 Change History
111 Who When What
112
113 (reverse chronological order - newest change first)
114 ***************************************************************/
115 BEGIN
116
117 IF (((old_references.unit_code = new_references.unit_code) AND
118 (old_references.version_number = new_references.version_number)) OR
119 ((new_references.unit_code IS NULL) OR
120 (new_references.version_number IS NULL))) THEN
121 NULL;
122 ELSIF NOT Igs_Ps_Unit_Ver_Pkg.Get_PK_For_Validation (
123 new_references.unit_code,
124 new_references.version_number
125 ) THEN
126 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
127 IGS_GE_MSG_STACK.ADD;
128 App_Exception.Raise_Exception;
129 END IF;
130
131 END Check_Parent_Existance;
132
133 FUNCTION Get_PK_For_Validation (
134 x_unit_field_of_study_id IN NUMBER
135 ) RETURN BOOLEAN AS
136 /*************************************************************
137 Created By : kdande@in
138 Date Created By :2000/05/11
139 Purpose :
140 Know limitations, enhancements or remarks
141 Change History
142 Who When What
143
144 (reverse chronological order - newest change first)
145 ***************************************************************/
146
147 CURSOR cur_rowid IS
148 SELECT rowid
149 FROM igs_ps_unit_fld_stdy
150 WHERE unit_field_of_study_id = x_unit_field_of_study_id
151 FOR UPDATE NOWAIT;
152
153 lv_rowid cur_rowid%RowType;
154
155 BEGIN
156
157 Open cur_rowid;
158 Fetch cur_rowid INTO lv_rowid;
159 IF (cur_rowid%FOUND) THEN
160 Close cur_rowid;
161 Return(TRUE);
162 ELSE
163 Close cur_rowid;
164 Return(FALSE);
165 END IF;
166 END Get_PK_For_Validation;
167
168 FUNCTION Get_UK_For_Validation (
169 x_field_of_study IN VARCHAR2,
170 x_unit_code IN VARCHAR2,
171 x_version_number IN NUMBER
172 ) RETURN BOOLEAN AS
173 /*************************************************************
174 Created By : kdande@in
175 Date Created By :2000/05/11
176 Purpose :
177 Know limitations, enhancements or remarks
178 Change History
179 Who When What
180
181 (reverse chronological order - newest change first)
182 ***************************************************************/
183
184 CURSOR cur_rowid IS
185 SELECT rowid
186 FROM igs_ps_unit_fld_stdy
187 WHERE field_of_study = x_field_of_study
188 AND unit_code = x_unit_code
189 AND version_number = x_version_number
190 AND ((l_rowid is null) or (rowid <> l_rowid));
191
192 lv_rowid cur_rowid%RowType;
193
194 BEGIN
195
196 Open cur_rowid;
197 Fetch cur_rowid INTO lv_rowid;
198 IF (cur_rowid%FOUND) THEN
199 Close cur_rowid;
200 return (true);
201 ELSE
202 close cur_rowid;
203 return(false);
204 END IF;
205 END Get_UK_For_Validation;
206
207 PROCEDURE Get_FK_Igs_Ps_Fld_Of_Study (
208 x_field_of_study IN VARCHAR2
209 ) AS
210 /*************************************************************
211 Created By : kdande@in
212 Date Created By :2000/05/11
213 Purpose :
214 Know limitations, enhancements or remarks
215 Change History
216 Who When What
217
218 (reverse chronological order - newest change first)
219 ***************************************************************/
220
221 CURSOR cur_rowid IS
222 SELECT rowid
223 FROM igs_ps_unit_fld_stdy
224 WHERE field_of_study = x_field_of_study;
225
226 lv_rowid cur_rowid%RowType;
227
228 BEGIN
229
230 Open cur_rowid;
231 Fetch cur_rowid INTO lv_rowid;
232 IF (cur_rowid%FOUND) THEN
233 Close cur_rowid;
234 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UFS_FOS_FK');
235 IGS_GE_MSG_STACK.ADD;
236 App_Exception.Raise_Exception;
237 Return;
238 END IF;
239 Close cur_rowid;
240
241 END Get_FK_Igs_Ps_Fld_Of_Study;
242
243 PROCEDURE Get_FK_Igs_Ps_Unit_Ver (
244 x_unit_cd IN VARCHAR2,
245 x_version_number IN NUMBER
246 ) AS
247 /*************************************************************
248 Created By : kdande@in
249 Date Created By :2000/05/11
250 Purpose :
251 Know limitations, enhancements or remarks
252 Change History
253 Who When What
254
255 (reverse chronological order - newest change first)
256 ***************************************************************/
257
258 CURSOR cur_rowid IS
259 SELECT rowid
260 FROM igs_ps_unit_fld_stdy
261 WHERE unit_code = x_unit_cd
262 AND version_number = x_version_number ;
263
264 lv_rowid cur_rowid%RowType;
265
266 BEGIN
267
268 Open cur_rowid;
269 Fetch cur_rowid INTO lv_rowid;
270 IF (cur_rowid%FOUND) THEN
271 Close cur_rowid;
272 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UFS_UV_FK');
273 IGS_GE_MSG_STACK.ADD;
274 App_Exception.Raise_Exception;
275 Return;
276 END IF;
277 Close cur_rowid;
278
279 END Get_FK_Igs_Ps_Unit_Ver;
280
281 PROCEDURE Before_DML (
282 p_action IN VARCHAR2,
283 x_rowid IN VARCHAR2 DEFAULT NULL,
284 x_unit_field_of_study_id IN NUMBER DEFAULT NULL,
285 x_unit_code IN VARCHAR2 DEFAULT NULL,
286 x_version_number IN NUMBER DEFAULT NULL,
287 x_field_of_study IN VARCHAR2 DEFAULT NULL,
288 x_creation_date IN DATE DEFAULT NULL,
289 x_created_by IN NUMBER DEFAULT NULL,
290 x_last_update_date IN DATE DEFAULT NULL,
291 x_last_updated_by IN NUMBER DEFAULT NULL,
292 x_last_update_login IN NUMBER DEFAULT NULL
293 ) AS
294 /*************************************************************
295 Created By : kdande@in
296 Date Created By :2000/05/11
297 Purpose :
298 Know limitations, enhancements or remarks
299 Change History
300 Who When What
301
302 (reverse chronological order - newest change first)
303 ***************************************************************/
304 BEGIN
305
306 Set_Column_Values (
307 p_action,
308 x_rowid,
309 x_unit_field_of_study_id,
310 x_unit_code,
311 x_version_number,
312 x_field_of_study,
313 x_creation_date,
314 x_created_by,
315 x_last_update_date,
316 x_last_updated_by,
317 x_last_update_login
318 );
319
320 IF (p_action = 'INSERT') THEN
321 -- Call all the procedures related to Before Insert.
322 IF Get_Pk_For_Validation(
323 new_references.unit_field_of_study_id) THEN
324 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
325 IGS_GE_MSG_STACK.ADD;
326 App_Exception.Raise_Exception;
327 END IF;
328 Check_Uniqueness;
329 Check_Parent_Existance;
330 ELSIF (p_action = 'UPDATE') THEN
331 -- Call all the procedures related to Before Update.
332 Check_Uniqueness;
333 Check_Parent_Existance;
334 ELSIF (p_action = 'DELETE') THEN
335 -- Call all the procedures related to Before Delete.
336 Null;
337 ELSIF (p_action = 'VALIDATE_INSERT') THEN
338 -- Call all the procedures related to Before Insert.
339 IF Get_PK_For_Validation (
340 new_references.unit_field_of_study_id) THEN
341 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
342 IGS_GE_MSG_STACK.ADD;
343 App_Exception.Raise_Exception;
344 END IF;
345 Check_Uniqueness;
346 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
347 Check_Uniqueness;
348 ELSIF (p_action = 'VALIDATE_DELETE') THEN
349 Null;
350 END IF;
351
352 l_rowid:=NULL;
353
354 END Before_DML;
355
356 PROCEDURE After_DML (
357 p_action IN VARCHAR2,
358 x_rowid IN VARCHAR2
359 ) IS
360 /*************************************************************
361 Created By : kdande@in
362 Date Created By :2000/05/11
363 Purpose :
364 Know limitations, enhancements or remarks
365 Change History
366 Who When What
367
368 (reverse chronological order - newest change first)
369 ***************************************************************/
370 BEGIN
371
372 l_rowid := x_rowid;
373
374 IF (p_action = 'INSERT') THEN
375 -- Call all the procedures related to After Insert.
376 Null;
377 ELSIF (p_action = 'UPDATE') THEN
378 -- Call all the procedures related to After Update.
379 Null;
380 ELSIF (p_action = 'DELETE') THEN
381 -- Call all the procedures related to After Delete.
382 Null;
383
384 END IF;
385
386 l_rowid:=NULL;
387
388 END After_DML;
389
390 procedure INSERT_ROW (
391 X_ROWID in out NOCOPY VARCHAR2,
392 x_UNIT_FIELD_OF_STUDY_ID IN OUT NOCOPY NUMBER,
393 x_UNIT_CODE IN VARCHAR2,
394 x_VERSION_NUMBER IN NUMBER,
395 x_FIELD_OF_STUDY IN VARCHAR2,
396 X_MODE in VARCHAR2 default 'R'
397 ) AS
398 /*************************************************************
399 Created By : kdande@in
400 Date Created By :2000/05/11
401 Purpose :
402 Know limitations, enhancements or remarks
403 Change History
404 Who When What
405
406 (reverse chronological order - newest change first)
407 ***************************************************************/
408
409 cursor C is select ROWID from IGS_PS_UNIT_FLD_STDY
410 where UNIT_FIELD_OF_STUDY_ID= X_UNIT_FIELD_OF_STUDY_ID;
411 X_LAST_UPDATE_DATE DATE ;
412 X_LAST_UPDATED_BY NUMBER ;
413 X_LAST_UPDATE_LOGIN NUMBER ;
414 begin
415 X_LAST_UPDATE_DATE := SYSDATE;
416 if(X_MODE = 'I') then
417 X_LAST_UPDATED_BY := 1;
418 X_LAST_UPDATE_LOGIN := 0;
419 elsif (X_MODE = 'R') then
420 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
421 if X_LAST_UPDATED_BY is NULL then
422 X_LAST_UPDATED_BY := -1;
423 end if;
424 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
425 if X_LAST_UPDATE_LOGIN is NULL then
426 X_LAST_UPDATE_LOGIN := -1;
427 end if;
428 else
429 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
430 IGS_GE_MSG_STACK.ADD;
431 app_exception.raise_exception;
432 end if;
433 SELECT igs_ps_unit_fld_stdy_s.nextval
434 INTO x_unit_field_of_study_id
435 FROM dual;
436 Before_DML(
437 p_action=>'INSERT',
438 x_rowid=>X_ROWID,
439 x_unit_field_of_study_id=>X_UNIT_FIELD_OF_STUDY_ID,
440 x_unit_code=>X_UNIT_CODE,
441 x_version_number=>X_VERSION_NUMBER,
442 x_field_of_study=>X_FIELD_OF_STUDY,
443 x_creation_date=>X_LAST_UPDATE_DATE,
444 x_created_by=>X_LAST_UPDATED_BY,
445 x_last_update_date=>X_LAST_UPDATE_DATE,
446 x_last_updated_by=>X_LAST_UPDATED_BY,
447 x_last_update_login=>X_LAST_UPDATE_LOGIN);
448 insert into IGS_PS_UNIT_FLD_STDY (
449 UNIT_FIELD_OF_STUDY_ID
450 ,UNIT_CODE
451 ,VERSION_NUMBER
452 ,FIELD_OF_STUDY
453 ,CREATION_DATE
454 ,CREATED_BY
455 ,LAST_UPDATE_DATE
456 ,LAST_UPDATED_BY
457 ,LAST_UPDATE_LOGIN
458 ) values (
459 NEW_REFERENCES.UNIT_FIELD_OF_STUDY_ID
460 ,NEW_REFERENCES.UNIT_CODE
461 ,NEW_REFERENCES.VERSION_NUMBER
462 ,NEW_REFERENCES.FIELD_OF_STUDY
463 ,X_LAST_UPDATE_DATE
464 ,X_LAST_UPDATED_BY
465 ,X_LAST_UPDATE_DATE
466 ,X_LAST_UPDATED_BY
467 ,X_LAST_UPDATE_LOGIN
468 );
469 open c;
470 fetch c into X_ROWID;
471 if (c%notfound) then
472 close c;
473 raise no_data_found;
474 end if;
475 close c;
476 After_DML (
477 p_action => 'INSERT' ,
478 x_rowid => X_ROWID );
479 END INSERT_ROW;
480 PROCEDURE LOCK_ROW (
481 X_ROWID in VARCHAR2,
482 x_UNIT_FIELD_OF_STUDY_ID IN NUMBER,
483 x_UNIT_CODE IN VARCHAR2,
484 x_VERSION_NUMBER IN NUMBER,
485 x_FIELD_OF_STUDY IN VARCHAR2 ) AS
486 cursor c1 is select
487 UNIT_CODE
488 , VERSION_NUMBER
489 , FIELD_OF_STUDY
490 from IGS_PS_UNIT_FLD_STDY
491 where ROWID = X_ROWID
492 for update nowait;
493 tlinfo c1%rowtype;
494 begin
495 open c1;
496 fetch c1 into tlinfo;
497 if (c1%notfound) then
498 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
499 IGS_GE_MSG_STACK.ADD;
500 close c1;
501 app_exception.raise_exception;
502 return;
503 end if;
504 close c1;
505 if ( ( tlinfo.UNIT_CODE = X_UNIT_CODE)
506 AND (tlinfo.VERSION_NUMBER = X_VERSION_NUMBER)
507 AND (tlinfo.FIELD_OF_STUDY = X_FIELD_OF_STUDY)
508 ) then
509 null;
510 else
511 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
512 IGS_GE_MSG_STACK.ADD;
513 app_exception.raise_exception;
514 end if;
515 return;
516 end LOCK_ROW;
517
518 Procedure UPDATE_ROW (
519 X_ROWID in VARCHAR2,
520 x_UNIT_FIELD_OF_STUDY_ID IN NUMBER,
521 x_UNIT_CODE IN VARCHAR2,
522 x_VERSION_NUMBER IN NUMBER,
523 x_FIELD_OF_STUDY IN VARCHAR2,
524 X_MODE in VARCHAR2 default 'R'
525 ) AS
526 /*************************************************************
527 Created By : kdande@in
528 Date Created By :2000/05/11
529 Purpose :
530 Know limitations, enhancements or remarks
531 Change History
532 Who When What
533
534 (reverse chronological order - newest change first)
535 ***************************************************************/
536 X_LAST_UPDATE_DATE DATE ;
537 X_LAST_UPDATED_BY NUMBER ;
538 X_LAST_UPDATE_LOGIN NUMBER ;
539 begin
540 X_LAST_UPDATE_DATE := SYSDATE;
541 if(X_MODE = 'I') then
542 X_LAST_UPDATED_BY := 1;
543 X_LAST_UPDATE_LOGIN := 0;
544 elsif (X_MODE = 'R') then
545 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
546 if X_LAST_UPDATED_BY is NULL then
547 X_LAST_UPDATED_BY := -1;
548 end if;
549 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
550 if X_LAST_UPDATE_LOGIN is NULL then
551 X_LAST_UPDATE_LOGIN := -1;
552 end if;
553 else
554 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
555 IGS_GE_MSG_STACK.ADD;
556 app_exception.raise_exception;
557 end if;
558 Before_DML(
559 p_action=>'UPDATE',
560 x_rowid=>X_ROWID,
561 x_unit_field_of_study_id=>X_UNIT_FIELD_OF_STUDY_ID,
562 x_unit_code=>X_UNIT_CODE,
563 x_version_number=>X_VERSION_NUMBER,
564 x_field_of_study=>X_FIELD_OF_STUDY,
565 x_creation_date=>X_LAST_UPDATE_DATE,
566 x_created_by=>X_LAST_UPDATED_BY,
567 x_last_update_date=>X_LAST_UPDATE_DATE,
568 x_last_updated_by=>X_LAST_UPDATED_BY,
569 x_last_update_login=>X_LAST_UPDATE_LOGIN);
570 update IGS_PS_UNIT_FLD_STDY set
571 UNIT_CODE = NEW_REFERENCES.UNIT_CODE,
572 VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
573 FIELD_OF_STUDY = NEW_REFERENCES.FIELD_OF_STUDY,
574 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
575 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
576 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
577 where ROWID = X_ROWID;
578 if (sql%notfound) then
579 raise no_data_found;
580 end if;
581
582 After_DML (
583 p_action => 'UPDATE' ,
584 x_rowid => X_ROWID
585 );
586 end UPDATE_ROW;
587
588 procedure ADD_ROW (
589 X_ROWID in out NOCOPY VARCHAR2,
590 x_UNIT_FIELD_OF_STUDY_ID IN OUT NOCOPY NUMBER,
591 x_UNIT_CODE IN VARCHAR2,
592 x_VERSION_NUMBER IN NUMBER,
593 x_FIELD_OF_STUDY IN VARCHAR2,
594 X_MODE in VARCHAR2 default 'R'
595 ) AS
596 /*************************************************************
597 Created By : kdande@in
598 Date Created By :2000/05/11
599 Purpose :
600 Know limitations, enhancements or remarks
601 Change History
602 Who When What
603
604 (reverse chronological order - newest change first)
605 ***************************************************************/
606 cursor c1 is select ROWID from IGS_PS_UNIT_FLD_STDY
607 where UNIT_FIELD_OF_STUDY_ID= X_UNIT_FIELD_OF_STUDY_ID ;
608 begin
609 open c1;
610 fetch c1 into X_ROWID;
611 if (c1%notfound) then
612 close c1;
613 INSERT_ROW (
614 X_ROWID,
615 X_UNIT_FIELD_OF_STUDY_ID,
616 X_UNIT_CODE,
617 X_VERSION_NUMBER,
618 X_FIELD_OF_STUDY,
619 X_MODE );
620 return;
621 end if;
622 close c1;
623 UPDATE_ROW (
624 X_ROWID,
625 X_UNIT_FIELD_OF_STUDY_ID,
626 X_UNIT_CODE,
627 X_VERSION_NUMBER,
628 X_FIELD_OF_STUDY,
629 X_MODE );
630 end ADD_ROW;
631
632 procedure DELETE_ROW (
633 X_ROWID in VARCHAR2
634 ) AS
635 /*************************************************************
636 Created By : kdande@in
637 Date Created By :2000/05/11
638 Purpose :
639 Know limitations, enhancements or remarks
640 Change History
641 Who When What
642
643 (reverse chronological order - newest change first)
644 ***************************************************************/
645 begin
646 Before_DML (
647 p_action => 'DELETE',
648 x_rowid => X_ROWID
649 );
650 delete from IGS_PS_UNIT_FLD_STDY
651 where ROWID = X_ROWID;
652 if (sql%notfound) then
653 raise no_data_found;
654 end if;
655 After_DML (
656 p_action => 'DELETE',
657 x_rowid => X_ROWID
658 );
659 END DELETE_ROW;
660
661 END igs_ps_unit_fld_stdy_pkg;
[Home] [Help]