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