[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_GRD_SCH_TRN_PKG
Source
1 package body IGS_AS_GRD_SCH_TRN_PKG AS
2 /* $Header: IGSDI15B.pls 115.6 2003/12/09 11:29:51 ijeddy ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_AS_GRD_SCH_TRN_ALL%RowType;
5 new_references IGS_AS_GRD_SCH_TRN_ALL%RowType;
6 PROCEDURE Set_Column_Values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2 DEFAULT NULL,
9 x_org_id IN NUMBER DEFAULT NULL,
10 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
11 x_version_number IN NUMBER DEFAULT NULL,
12 x_grade IN VARCHAR2 DEFAULT NULL,
13 x_to_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
14 x_to_version_number IN NUMBER DEFAULT NULL,
15 x_to_grade IN VARCHAR2 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 CURSOR cur_old_ref_values IS
23 SELECT *
24 FROM IGS_AS_GRD_SCH_TRN_ALL
25 WHERE rowid = x_rowid;
26 BEGIN
27 l_rowid := x_rowid;
28 -- Code for setting the Old and New Reference Values.
29 -- Populate Old Values.
30 Open cur_old_ref_values;
31 Fetch cur_old_ref_values INTO old_references;
32 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
33 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
34 IGS_GE_MSG_STACK.ADD;
35 Close cur_old_ref_values;
36 APP_EXCEPTION.RAISE_EXCEPTION;
37
38 Return;
39 END IF;
40 Close cur_old_ref_values;
41 -- Populate New Values.
42 new_references.org_id := x_org_id;
43 new_references.grading_schema_cd := x_grading_schema_cd;
44 new_references.version_number := x_version_number;
45 new_references.grade := x_grade;
46 new_references.to_grading_schema_cd := x_to_grading_schema_cd;
47 new_references.to_version_number := x_to_version_number;
48 new_references.to_grade := x_to_grade;
49 IF (p_action = 'UPDATE') THEN
50 new_references.creation_date := old_references.creation_date;
51 new_references.created_by := old_references.created_by;
52 ELSE
53 new_references.creation_date := x_creation_date;
54 new_references.created_by := x_created_by;
55 END IF;
56 new_references.last_update_date := x_last_update_date;
57 new_references.last_updated_by := x_last_updated_by;
58 new_references.last_update_login := x_last_update_login;
59 END Set_Column_Values;
60 PROCEDURE BeforeRowInsertUpdate1(
61 p_inserting IN BOOLEAN DEFAULT FALSE,
62 p_updating IN BOOLEAN DEFAULT FALSE,
63 p_deleting IN BOOLEAN DEFAULT FALSE
64 ) AS
65 v_message_name VARCHAR2(30);
66 BEGIN
67 -- Validate that inserts/updates are allowed
68 IF p_inserting OR p_updating THEN
69 --<GSGT0002>
70 -- Validate grade may not be translated against another grade in same ver
71 IF IGS_AS_VAL_GSGT.assp_val_gsgt_gs_gs (
72 new_references.grading_schema_cd,
73 new_references.version_number,
74 new_references.to_grading_schema_cd,
75 new_references.to_version_number,
76 v_message_name) = FALSE THEN
77 FND_MESSAGE.SET_NAME('IGS',v_message_name);
78 IGS_GE_MSG_STACK.ADD;
79 APP_EXCEPTION.RAISE_EXCEPTION;
80 END IF;
81 --<GSGT0004>
82 -- Validate rslt type for grade is same as rslt type for xlation grade
83 --ijeddy, this is now treated as a warning instead of an error, bug no 3216979
84 -- IF IGS_AS_VAL_GSGT.assp_val_gsgt_result (
85 -- new_references.grading_schema_cd,
86 -- new_references.version_number,
87 -- new_references.grade,
88 -- new_references.to_grading_schema_cd,
89 -- new_references.to_version_number,
90 -- new_references.to_grade,
91 -- v_message_name) = FALSE THEN
92 -- FND_MESSAGE.SET_NAME('IGS',v_message_name);
93 -- IGS_GE_MSG_STACK.ADD;
94 -- APP_EXCEPTION.RAISE_EXCEPTION;
95 -- END IF;
96 END IF;
97 END BeforeRowInsertUpdate1;
98 -- Trigger description :-
99 -- "OSS_TST".trg_gsgt_ar_iu
100 -- AFTER INSERT OR UPDATE
101 -- ON IGS_AS_GRD_SCH_TRN
102 -- FOR EACH ROW
103 PROCEDURE AfterRowInsertUpdate2(
104 p_inserting IN BOOLEAN DEFAULT FALSE,
105 p_updating IN BOOLEAN DEFAULT FALSE,
106 p_deleting IN BOOLEAN DEFAULT FALSE
107 ) AS
108 v_message_name VARCHAR2(30);
109 BEGIN
110 IF p_inserting OR p_updating THEN
111 IF IGS_AS_VAL_GSGT.assp_val_gsgt_multi (
112 new_references.grading_schema_cd,
113 new_references.version_number,
114 new_references.grade,
115 new_references.to_grading_schema_cd,
116 new_references.to_version_number,
117 new_references.to_grade,
118 v_message_name) = FALSE THEN
119 FND_MESSAGE.SET_NAME('IGS',v_message_name);
120 IGS_GE_MSG_STACK.ADD;
121 APP_EXCEPTION.RAISE_EXCEPTION;
122 END IF;
123 END IF;
124 END AfterRowInsertUpdate2;
125 -- Trigger description :-
126 -- "OSS_TST".trg_gsgt_as_iu
127 -- AFTER INSERT OR UPDATE
128 -- ON IGS_AS_GRD_SCH_TRN
129 PROCEDURE Check_Parent_Existance AS
130 BEGIN
131 IF (((old_references.grading_schema_cd = new_references.grading_schema_cd) AND
132 (old_references.version_number = new_references.version_number) AND
133 (old_references.grade = new_references.grade)) OR
134 ((new_references.grading_schema_cd IS NULL) OR
135 (new_references.version_number IS NULL) OR
136 (new_references.grade IS NULL))) THEN
137 NULL;
138 ELSIF NOT IGS_AS_GRD_SCH_GRADE_PKG.Get_PK_For_Validation (
139 new_references.grading_schema_cd,
140 new_references.version_number,
141 new_references.grade
142 ) THEN
143 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
144 IGS_GE_MSG_STACK.ADD;
145 APP_EXCEPTION.RAISE_EXCEPTION;
146 END IF;
147 IF (((old_references.to_grading_schema_cd = new_references.to_grading_schema_cd) AND
148 (old_references.to_version_number = new_references.to_version_number) OR
149 (old_references.to_grade = new_references.to_grade)) OR
150 ((new_references.to_grading_schema_cd IS NULL) OR
151 (new_references.to_version_number IS NULL) OR
152 (new_references.to_grade IS NULL))) THEN
153 NULL;
154 ELSIF NOT IGS_AS_GRD_SCH_GRADE_PKG.Get_PK_For_Validation (
155 new_references.to_grading_schema_cd,
156 new_references.to_version_number,
157 new_references.to_grade
158 ) THEN
159 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
160 IGS_GE_MSG_STACK.ADD;
161 APP_EXCEPTION.RAISE_EXCEPTION;
162 END IF;
163 END Check_Parent_Existance;
164 FUNCTION Get_PK_For_Validation (
165 x_grading_schema_cd IN VARCHAR2,
166 x_version_number IN NUMBER,
167 x_grade IN VARCHAR2,
168 x_to_grading_schema_cd IN VARCHAR2,
169 x_to_version_number IN NUMBER,
170 x_to_grade IN VARCHAR2
171 ) RETURN BOOLEAN AS
172 CURSOR cur_rowid IS
173 SELECT rowid
174 FROM IGS_AS_GRD_SCH_TRN_ALL
175 WHERE grading_schema_cd = x_grading_schema_cd
176 AND version_number = x_version_number
177 AND grade = x_grade
178 AND to_grading_schema_cd = x_to_grading_schema_cd
179 AND to_version_number = x_to_version_number
180 AND to_grade = x_to_grade
181 FOR UPDATE NOWAIT;
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 PROCEDURE GET_FK_IGS_AS_GRD_SCH_GRADE (
195 x_grading_schema_cd IN VARCHAR2,
196 x_version_number IN NUMBER,
197 x_grade IN VARCHAR2
198 ) AS
199 CURSOR cur_rowid IS
200 SELECT rowid
201 FROM IGS_AS_GRD_SCH_TRN_ALL
202 WHERE to_grading_schema_cd = x_grading_schema_cd
203 AND to_version_number = x_version_number
204 AND to_grade = x_grade OR
205 (grading_schema_cd = x_grading_schema_cd
206 AND version_number = x_version_number
207 AND grade = x_grade ) ;
208 lv_rowid cur_rowid%RowType;
209 BEGIN
210 Open cur_rowid;
211 Fetch cur_rowid INTO lv_rowid;
212 IF (cur_rowid%FOUND) THEN
213 Fnd_Message.Set_Name ('IGS', 'IGS_AS_GSGT_GSG_FK');
214 IGS_GE_MSG_STACK.ADD;
215 Close cur_rowid;
216 APP_EXCEPTION.RAISE_EXCEPTION;
217
218 Return;
219 END IF;
220 Close cur_rowid;
221 END GET_FK_IGS_AS_GRD_SCH_GRADE;
222 PROCEDURE Before_DML (
223 p_action IN VARCHAR2,
224 x_rowid IN VARCHAR2 DEFAULT NULL,
225 x_org_id IN NUMBER DEFAULT NULL,
226 x_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
227 x_version_number IN NUMBER DEFAULT NULL,
228 x_grade IN VARCHAR2 DEFAULT NULL,
229 x_to_grading_schema_cd IN VARCHAR2 DEFAULT NULL,
230 x_to_version_number IN NUMBER DEFAULT NULL,
231 x_to_grade IN VARCHAR2 DEFAULT NULL,
232 x_creation_date IN DATE DEFAULT NULL,
233 x_created_by IN NUMBER DEFAULT NULL,
234 x_last_update_date IN DATE DEFAULT NULL,
235 x_last_updated_by IN NUMBER DEFAULT NULL,
236 x_last_update_login IN NUMBER DEFAULT NULL
237 ) AS
238 BEGIN
239 Set_Column_Values (
240 p_action,
241 x_rowid,
242 x_org_id,
243 x_grading_schema_cd,
244 x_version_number,
245 x_grade,
246 x_to_grading_schema_cd,
247 x_to_version_number,
248 x_to_grade,
249 x_creation_date,
250 x_created_by,
251 x_last_update_date,
252 x_last_updated_by,
253 x_last_update_login
254 );
255 IF (p_action = 'INSERT') THEN
256 -- Call all the procedures related to Before Insert.
257 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
258 IF Get_PK_For_Validation (
259 NEW_REFERENCES.grading_schema_cd ,
260 NEW_REFERENCES.version_number ,
261 NEW_REFERENCES.grade ,
262 NEW_REFERENCES.to_grading_schema_cd ,
263 NEW_REFERENCES.to_version_number,
264 NEW_REFERENCES.to_grade) THEN
265 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
266 IGS_GE_MSG_STACK.ADD;
267 APP_EXCEPTION.RAISE_EXCEPTION;
268 END IF;
269
270 Check_Constraints;
271
272
273 Check_Parent_Existance;
274 ELSIF (p_action = 'UPDATE') THEN
275 -- Call all the procedures related to Before Update.
276 BeforeRowInsertUpdate1 ( p_updating => TRUE );
277 Check_Constraints;
278 Check_Parent_Existance;
279
280
281 ELSIF (p_action = 'VALIDATE_INSERT') THEN
282 IF Get_PK_For_Validation (
283 NEW_REFERENCES.grading_schema_cd ,
284 NEW_REFERENCES.version_number ,
285 NEW_REFERENCES.grade ,
286 NEW_REFERENCES.to_grading_schema_cd ,
287 NEW_REFERENCES.to_version_number,
288 NEW_REFERENCES.to_grade) THEN
289 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
290 IGS_GE_MSG_STACK.ADD;
291 APP_EXCEPTION.RAISE_EXCEPTION;
292 END IF;
293
294 Check_Constraints;
295 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
296
297 Check_Constraints;
298
299
300 END IF;
301 END Before_DML;
302
303 procedure INSERT_ROW (
304 X_ROWID in out NOCOPY VARCHAR2,
305 X_ORG_ID in NUMBER,
306 X_GRADING_SCHEMA_CD in VARCHAR2,
307 X_VERSION_NUMBER in NUMBER,
308 X_GRADE in VARCHAR2,
309 X_TO_GRADING_SCHEMA_CD in VARCHAR2,
310 X_TO_VERSION_NUMBER in NUMBER,
311 X_TO_GRADE in VARCHAR2,
312 X_MODE in VARCHAR2 default 'R'
313 ) AS
314 cursor C is select ROWID from IGS_AS_GRD_SCH_TRN_ALL
315 where GRADING_SCHEMA_CD = X_GRADING_SCHEMA_CD
316 and VERSION_NUMBER = X_VERSION_NUMBER
317 and GRADE = X_GRADE
318 and TO_GRADING_SCHEMA_CD = X_TO_GRADING_SCHEMA_CD
319 and TO_VERSION_NUMBER = X_TO_VERSION_NUMBER
320 and TO_GRADE = X_TO_GRADE;
321 X_LAST_UPDATE_DATE DATE;
322 X_LAST_UPDATED_BY NUMBER;
323 X_LAST_UPDATE_LOGIN NUMBER;
324 begin
325 X_LAST_UPDATE_DATE := SYSDATE;
326 if(X_MODE = 'I') then
327 X_LAST_UPDATED_BY := 1;
328 X_LAST_UPDATE_LOGIN := 0;
329 elsif (X_MODE = 'R') then
330 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
331 if X_LAST_UPDATED_BY is NULL then
332 X_LAST_UPDATED_BY := -1;
333 end if;
334 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
335 if X_LAST_UPDATE_LOGIN is NULL then
336 X_LAST_UPDATE_LOGIN := -1;
337 end if;
338 else
339 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
340 IGS_GE_MSG_STACK.ADD;
341 APP_EXCEPTION.RAISE_EXCEPTION;
342 end if;
343 Before_DML(
344 p_action=>'INSERT',
345 x_rowid=>X_ROWID,
346 x_org_id => igs_ge_gen_003.get_org_id,
347 x_grade=>X_GRADE,
348 x_grading_schema_cd=>X_GRADING_SCHEMA_CD,
349 x_to_grade=>X_TO_GRADE,
350 x_to_grading_schema_cd=>X_TO_GRADING_SCHEMA_CD,
351 x_to_version_number=>X_TO_VERSION_NUMBER,
352 x_version_number=>X_VERSION_NUMBER,
353 x_creation_date=>X_LAST_UPDATE_DATE,
354 x_created_by=>X_LAST_UPDATED_BY,
355 x_last_update_date=>X_LAST_UPDATE_DATE,
356 x_last_updated_by=>X_LAST_UPDATED_BY,
357 x_last_update_login=>X_LAST_UPDATE_LOGIN
358 );
359 insert into IGS_AS_GRD_SCH_TRN_ALL (
360 ORG_ID,
361 GRADING_SCHEMA_CD,
362 VERSION_NUMBER,
363 GRADE,
364 TO_GRADING_SCHEMA_CD,
365 TO_VERSION_NUMBER,
366 TO_GRADE,
367 CREATION_DATE,
368 CREATED_BY,
369 LAST_UPDATE_DATE,
370 LAST_UPDATED_BY,
371 LAST_UPDATE_LOGIN
372 ) values (
373 NEW_REFERENCES.ORG_ID,
374 NEW_REFERENCES.GRADING_SCHEMA_CD,
375 NEW_REFERENCES.VERSION_NUMBER,
376 NEW_REFERENCES.GRADE,
377 NEW_REFERENCES.TO_GRADING_SCHEMA_CD,
378 NEW_REFERENCES.TO_VERSION_NUMBER,
379 NEW_REFERENCES.TO_GRADE,
380 X_LAST_UPDATE_DATE,
381 X_LAST_UPDATED_BY,
382 X_LAST_UPDATE_DATE,
383 X_LAST_UPDATED_BY,
384 X_LAST_UPDATE_LOGIN
385 );
386 open c;
387 fetch c into X_ROWID;
388 if (c%notfound) then
389 close c;
390 raise no_data_found;
391 end if;
392 close c;
393
394 end INSERT_ROW;
395 procedure LOCK_ROW (
396 X_ROWID in VARCHAR2,
397 X_GRADING_SCHEMA_CD in VARCHAR2,
398 X_VERSION_NUMBER in NUMBER,
399 X_GRADE in VARCHAR2,
400 X_TO_GRADING_SCHEMA_CD in VARCHAR2,
401 X_TO_VERSION_NUMBER in NUMBER,
402 X_TO_GRADE in VARCHAR2
403 ) AS
404 cursor c1 is select
405 ROWID
406 from IGS_AS_GRD_SCH_TRN_ALL
407 where ROWID = X_ROWID for update nowait;
408 tlinfo c1%rowtype;
409 begin
410 open c1;
411 fetch c1 into tlinfo;
412 if (c1%notfound) then
413 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
414 IGS_GE_MSG_STACK.ADD;
415 APP_EXCEPTION.RAISE_EXCEPTION;
416 close c1;
417 return;
418 end if;
419 close c1;
420 return;
421 end LOCK_ROW;
422 procedure DELETE_ROW (
423 X_ROWID in VARCHAR2) AS
424 begin
425 Before_DML(
426 p_action => 'DELETE',
427 x_rowid => X_ROWID
428 );
429 delete from IGS_AS_GRD_SCH_TRN_ALL
430 where ROWID = X_ROWID;
431 if (sql%notfound) then
432 raise no_data_found;
433 end if;
434
435 end DELETE_ROW;
436 PROCEDURE Check_Constraints (
437 Column_Name IN VARCHAR2 DEFAULT NULL,
438 Column_Value IN VARCHAR2 DEFAULT NULL
439 )
440 AS
441 BEGIN
442 IF column_name is null then
443 NULL;
444 ELSIF upper(Column_name) = 'GRADE' THEN
445 new_references.GRADE :=COLUMN_VALUE;
446 ELSIF upper(Column_name) = 'GRADING_SCHEMA_CD' then
447 new_references.GRADING_SCHEMA_CD := column_value;
448 ELSIF upper(Column_name) = 'TO_GRADE' then
449 new_references.TO_GRADE := column_value;
450 ELSIF upper(Column_name) = 'TO_GRADING_SCHEMA_CD' then
451 new_references.TO_GRADING_SCHEMA_CD := column_value;
452 END IF ;
453
454 IF upper(column_name) = 'GRADE' OR
455 column_name is null Then
456 IF new_references.GRADE <> UPPER(new_references.GRADE) Then
457 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
458 IGS_GE_MSG_STACK.ADD;
459 APP_EXCEPTION.RAISE_EXCEPTION;
460 END IF;
461 END IF;
462
463 IF upper(column_name) = 'GRADING_SCHEMA_CD' OR
464 column_name is null Then
465 IF new_references.GRADING_SCHEMA_CD <> UPPER(new_references.GRADING_SCHEMA_CD) Then
466 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
467 IGS_GE_MSG_STACK.ADD;
468 APP_EXCEPTION.RAISE_EXCEPTION;
469 END IF;
470 END IF;
471
472 IF upper(column_name) = 'TO_GRADE' OR
473 column_name is null Then
474 IF new_references.TO_GRADE <> UPPER(new_references.TO_GRADE) Then
475 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
476 IGS_GE_MSG_STACK.ADD;
477 APP_EXCEPTION.RAISE_EXCEPTION;
478 END IF;
479 END IF;
480
481 IF upper(column_name) = 'TO_GRADING_SCHEMA_CD' OR
482 column_name is null Then
483 IF new_references.TO_GRADING_SCHEMA_CD <> UPPER(new_references.TO_GRADING_SCHEMA_CD) Then
484 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
485 IGS_GE_MSG_STACK.ADD;
486 APP_EXCEPTION.RAISE_EXCEPTION;
487 END IF;
488 END IF;
489 END Check_Constraints;
490
491
492
493
494 end IGS_AS_GRD_SCH_TRN_PKG;