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