[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_TYPGOV_SCORMP_PKG
Source
1 package body IGS_AS_TYPGOV_SCORMP_PKG AS
2 /* $Header: IGSDI24B.pls 115.4 2003/10/30 13:27:46 rghosh ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_AS_TYPGOV_SCORMP%RowType;
6 new_references IGS_AS_TYPGOV_SCORMP%RowType;
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_scndry_edu_ass_type IN VARCHAR2 DEFAULT NULL,
11 x_result_obtained_yr IN NUMBER DEFAULT NULL,
12 x_institution_score IN NUMBER DEFAULT NULL,
13 x_govt_score 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 CURSOR cur_old_ref_values IS
21 SELECT *
22 FROM IGS_AS_TYPGOV_SCORMP
23 WHERE rowid = x_rowid;
24 BEGIN
25 l_rowid := x_rowid;
26 -- Code for setting the Old and New Reference Values.
27 -- Populate Old Values.
28 Open cur_old_ref_values;
29 Fetch cur_old_ref_values INTO old_references;
30 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
31 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
32 IGS_GE_MSG_STACK.ADD;
33 Close cur_old_ref_values;
34 APP_EXCEPTION.RAISE_EXCEPTION;
35
36 Return;
37 END IF;
38 Close cur_old_ref_values;
39 -- Populate New Values.
40 new_references.scndry_edu_ass_type := x_scndry_edu_ass_type;
41 new_references.result_obtained_yr := x_result_obtained_yr;
42 new_references.institution_score := x_institution_score;
43 new_references.govt_score := x_govt_score;
44 IF (p_action = 'UPDATE') THEN
45 new_references.creation_date := old_references.creation_date;
46 new_references.created_by := old_references.created_by;
47 ELSE
48 new_references.creation_date := x_creation_date;
49 new_references.created_by := x_created_by;
50 END IF;
51 new_references.last_update_date := x_last_update_date;
52 new_references.last_updated_by := x_last_updated_by;
53 new_references.last_update_login := x_last_update_login;
54 END Set_Column_Values;
55
56 PROCEDURE Check_Parent_Existance AS
57 BEGIN
58 IF (((old_references.scndry_edu_ass_type = new_references.scndry_edu_ass_type)) OR
59 ((new_references.scndry_edu_ass_type IS NULL))) THEN
60 NULL;
61 ELSIF NOT IGS_AD_AUSE_ED_AS_TY_PKG.Get_PK_For_Validation (
62 new_references.scndry_edu_ass_type ,
63 'N'
64 ) THEN
65 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
66 IGS_GE_MSG_STACK.ADD;
67 APP_EXCEPTION.RAISE_EXCEPTION;
68
69 END IF;
70 END Check_Parent_Existance;
71
72 FUNCTION Get_PK_For_Validation (
73 x_scndry_edu_ass_type IN VARCHAR2,
74 x_result_obtained_yr IN NUMBER,
75 x_institution_score IN NUMBER
76 ) RETURN BOOLEAN AS
77 CURSOR cur_rowid IS
78 SELECT rowid
79 FROM IGS_AS_TYPGOV_SCORMP
80 WHERE scndry_edu_ass_type = x_scndry_edu_ass_type
81 AND result_obtained_yr = x_result_obtained_yr
82 AND institution_score = x_institution_score
83 FOR UPDATE NOWAIT;
84 lv_rowid cur_rowid%RowType;
85 BEGIN
86 Open cur_rowid;
87 Fetch cur_rowid INTO lv_rowid;
88 IF (cur_rowid%FOUND) THEN
89 Close cur_rowid;
90 Return (TRUE);
91 ELSE
92 Close cur_rowid;
93 Return (FALSE);
94 END IF; END Get_PK_For_Validation;
95
96
97
98 PROCEDURE GET_FK_IGS_AD_AUSE_ED_AS_TY (
99 x_aus_scndry_edu_ass_type IN VARCHAR2
100 ) AS
101 CURSOR cur_rowid IS
102 SELECT rowid
103 FROM IGS_AS_TYPGOV_SCORMP
104 WHERE scndry_edu_ass_type = x_aus_scndry_edu_ass_type ;
105 lv_rowid cur_rowid%RowType;
106 BEGIN
107 Open cur_rowid;
108 Fetch cur_rowid INTO lv_rowid;
109 IF (cur_rowid%FOUND) THEN
110 Fnd_Message.Set_Name ('IGS', 'IGS_AS_ATGSM_ASEAT_FK');
111 IGS_GE_MSG_STACK.ADD;
112 Close cur_rowid;
113 APP_EXCEPTION.RAISE_EXCEPTION;
114
115 Return;
116 END IF;
117 Close cur_rowid;
118 END GET_FK_IGS_AD_AUSE_ED_AS_TY;
119 PROCEDURE Before_DML (
120 p_action IN VARCHAR2,
121 x_rowid IN VARCHAR2 DEFAULT NULL,
122 x_scndry_edu_ass_type IN VARCHAR2 DEFAULT NULL,
123 x_result_obtained_yr IN NUMBER DEFAULT NULL,
124 x_institution_score IN NUMBER DEFAULT NULL,
125 x_govt_score IN NUMBER DEFAULT NULL,
126 x_creation_date IN DATE DEFAULT NULL,
127 x_created_by IN NUMBER DEFAULT NULL,
128 x_last_update_date IN DATE DEFAULT NULL,
129 x_last_updated_by IN NUMBER DEFAULT NULL,
130 x_last_update_login IN NUMBER DEFAULT NULL
131 ) AS
132 BEGIN
133 Set_Column_Values (
134 p_action,
135 x_rowid,
136 x_scndry_edu_ass_type,
137 x_result_obtained_yr,
138 x_institution_score,
139 x_govt_score,
140 x_creation_date,
141 x_created_by,
142 x_last_update_date,
143 x_last_updated_by,
144 x_last_update_login
145 );
146 IF (p_action = 'INSERT') THEN
147 -- Call all the procedures related to Before Insert.
148
149 IF Get_PK_For_Validation (
150 NEW_REFERENCES.scndry_edu_ass_type,
151 NEW_REFERENCES.result_obtained_yr ,
152 NEW_REFERENCES.institution_score) THEN
153 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
154 IGS_GE_MSG_STACK.ADD;
155 APP_EXCEPTION.RAISE_EXCEPTION;
156 END IF;
157
158 Check_Constraints;
159
160 Check_Parent_Existance;
161 ELSIF (p_action = 'UPDATE') THEN
162 -- Call all the procedures related to Before Update.
163
164 Check_Constraints;
165 Check_Parent_Existance;
166
167 ELSIF (p_action = 'VALIDATE_INSERT') THEN
168 IF Get_PK_For_Validation (
169 NEW_REFERENCES.scndry_edu_ass_type,
170 NEW_REFERENCES.result_obtained_yr ,
171 NEW_REFERENCES.institution_score) THEN
172 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
173 IGS_GE_MSG_STACK.ADD;
174 APP_EXCEPTION.RAISE_EXCEPTION;
175 END IF;
176
177 Check_Constraints;
178 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
179
180 Check_Constraints;
181
182
183
184
185 END IF;
186 END Before_DML;
187
188 procedure INSERT_ROW (
189 X_ROWID in out NOCOPY VARCHAR2,
190 X_SCNDRY_EDU_ASS_TYPE in VARCHAR2,
191 X_RESULT_OBTAINED_YR in NUMBER,
192 X_INSTITUTION_SCORE in NUMBER,
193 X_GOVT_SCORE in NUMBER,
194 X_MODE in VARCHAR2 default 'R'
195 ) AS
196 cursor C is select ROWID from IGS_AS_TYPGOV_SCORMP
197 where SCNDRY_EDU_ASS_TYPE = X_SCNDRY_EDU_ASS_TYPE
198 and RESULT_OBTAINED_YR = X_RESULT_OBTAINED_YR
199 and INSTITUTION_SCORE = X_INSTITUTION_SCORE;
200 X_LAST_UPDATE_DATE DATE;
201 X_LAST_UPDATED_BY NUMBER;
202 X_LAST_UPDATE_LOGIN NUMBER;
203 begin
204 X_LAST_UPDATE_DATE := SYSDATE;
205 if(X_MODE = 'I') then
206 X_LAST_UPDATED_BY := 1;
207 X_LAST_UPDATE_LOGIN := 0;
208 elsif (X_MODE = 'R') then
209 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
210 if X_LAST_UPDATED_BY is NULL then
211 X_LAST_UPDATED_BY := -1;
212 end if;
213 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
214 if X_LAST_UPDATE_LOGIN is NULL then
215 X_LAST_UPDATE_LOGIN := -1;
216 end if;
217 else
218 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
219 IGS_GE_MSG_STACK.ADD;
220 APP_EXCEPTION.RAISE_EXCEPTION;
221 end if;
222 Before_DML(
223 p_action=>'INSERT',
224 x_rowid=>X_ROWID,
225 x_govt_score=>X_GOVT_SCORE,
226 x_institution_score=>X_INSTITUTION_SCORE,
227 x_result_obtained_yr=>X_RESULT_OBTAINED_YR,
228 x_scndry_edu_ass_type=>X_SCNDRY_EDU_ASS_TYPE,
229 x_creation_date=>X_LAST_UPDATE_DATE,
230 x_created_by=>X_LAST_UPDATED_BY,
231 x_last_update_date=>X_LAST_UPDATE_DATE,
232 x_last_updated_by=>X_LAST_UPDATED_BY,
233 x_last_update_login=>X_LAST_UPDATE_LOGIN
234 );
235 insert into IGS_AS_TYPGOV_SCORMP (
236 SCNDRY_EDU_ASS_TYPE,
237 RESULT_OBTAINED_YR,
238 INSTITUTION_SCORE,
239 GOVT_SCORE,
240 CREATION_DATE,
241 CREATED_BY,
242 LAST_UPDATE_DATE,
243 LAST_UPDATED_BY,
244 LAST_UPDATE_LOGIN
245 ) values (
246 NEW_REFERENCES.SCNDRY_EDU_ASS_TYPE,
247 NEW_REFERENCES.RESULT_OBTAINED_YR,
248 NEW_REFERENCES.INSTITUTION_SCORE,
249 NEW_REFERENCES.GOVT_SCORE,
250 X_LAST_UPDATE_DATE,
251 X_LAST_UPDATED_BY,
252 X_LAST_UPDATE_DATE,
253 X_LAST_UPDATED_BY,
254 X_LAST_UPDATE_LOGIN
255 );
256 open c;
257 fetch c into X_ROWID;
258 if (c%notfound) then
259 close c;
260 raise no_data_found;
261 end if;
262 close c;
263
264 end INSERT_ROW;
265 procedure LOCK_ROW (
266 X_ROWID in VARCHAR2,
267 X_SCNDRY_EDU_ASS_TYPE in VARCHAR2,
268 X_RESULT_OBTAINED_YR in NUMBER,
269 X_INSTITUTION_SCORE in NUMBER,
270 X_GOVT_SCORE in NUMBER
271 ) AS
272 cursor c1 is select
273 GOVT_SCORE
274 from IGS_AS_TYPGOV_SCORMP
275 where ROWID = X_ROWID for update nowait;
276 tlinfo c1%rowtype;
277 begin
278 open c1;
279 fetch c1 into tlinfo;
280 if (c1%notfound) then
281 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
282 IGS_GE_MSG_STACK.ADD;
283 APP_EXCEPTION.RAISE_EXCEPTION;
284 close c1;
285 return;
286 end if;
287 close c1;
288 if ( (tlinfo.GOVT_SCORE = X_GOVT_SCORE)
289 ) then
290 null;
291 else
292 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
293 IGS_GE_MSG_STACK.ADD;
294 APP_EXCEPTION.RAISE_EXCEPTION;
295 end if;
296 return;
297 end LOCK_ROW;
298 procedure UPDATE_ROW (
299 X_ROWID in VARCHAR2,
300 X_SCNDRY_EDU_ASS_TYPE in VARCHAR2,
301 X_RESULT_OBTAINED_YR in NUMBER,
302 X_INSTITUTION_SCORE in NUMBER,
303 X_GOVT_SCORE in NUMBER,
304 X_MODE in VARCHAR2 default 'R'
305 ) AS
306 X_LAST_UPDATE_DATE DATE;
307 X_LAST_UPDATED_BY NUMBER;
308 X_LAST_UPDATE_LOGIN NUMBER;
309 begin
310 X_LAST_UPDATE_DATE := SYSDATE;
311 if(X_MODE = 'I') then
312 X_LAST_UPDATED_BY := 1;
313 X_LAST_UPDATE_LOGIN := 0;
314 elsif (X_MODE = 'R') then
315 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
316 if X_LAST_UPDATED_BY is NULL then
317 X_LAST_UPDATED_BY := -1;
318 end if;
319 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
320 if X_LAST_UPDATE_LOGIN is NULL then
321 X_LAST_UPDATE_LOGIN := -1;
322 end if;
323 else
324 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
325 IGS_GE_MSG_STACK.ADD;
326 APP_EXCEPTION.RAISE_EXCEPTION;
327 end if;
328 Before_DML(
329 p_action=>'UPDATE',
330 x_rowid=>X_ROWID,
331 x_govt_score=>X_GOVT_SCORE,
332 x_institution_score=>X_INSTITUTION_SCORE,
333 x_result_obtained_yr=>X_RESULT_OBTAINED_YR,
334 x_scndry_edu_ass_type=>X_SCNDRY_EDU_ASS_TYPE,
335 x_creation_date=>X_LAST_UPDATE_DATE,
336 x_created_by=>X_LAST_UPDATED_BY,
337 x_last_update_date=>X_LAST_UPDATE_DATE,
338 x_last_updated_by=>X_LAST_UPDATED_BY,
339 x_last_update_login=>X_LAST_UPDATE_LOGIN
340 );
341 update IGS_AS_TYPGOV_SCORMP set
342 GOVT_SCORE = NEW_REFERENCES.GOVT_SCORE,
343 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
344 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
345 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
346 where ROWID = X_ROWID;
347 if (sql%notfound) then
348 raise no_data_found;
349 end if;
350
351 end UPDATE_ROW;
352 procedure ADD_ROW (
353 X_ROWID in out NOCOPY VARCHAR2,
354 X_SCNDRY_EDU_ASS_TYPE in VARCHAR2,
355 X_RESULT_OBTAINED_YR in NUMBER,
356 X_INSTITUTION_SCORE in NUMBER,
357 X_GOVT_SCORE in NUMBER,
358 X_MODE in VARCHAR2 default 'R'
359 ) AS
360 cursor c1 is select rowid from IGS_AS_TYPGOV_SCORMP
361 where SCNDRY_EDU_ASS_TYPE = X_SCNDRY_EDU_ASS_TYPE
362 and RESULT_OBTAINED_YR = X_RESULT_OBTAINED_YR
363 and INSTITUTION_SCORE = X_INSTITUTION_SCORE
364 ;
365 begin
366 open c1;
367 fetch c1 into X_ROWID;
368 if (c1%notfound) then
369 close c1;
370 INSERT_ROW (
371 X_ROWID,
372 X_SCNDRY_EDU_ASS_TYPE,
373 X_RESULT_OBTAINED_YR,
374 X_INSTITUTION_SCORE,
375 X_GOVT_SCORE,
376 X_MODE);
377 return;
378 end if;
379 close c1;
380 UPDATE_ROW (
381 X_ROWID,
382 X_SCNDRY_EDU_ASS_TYPE,
383 X_RESULT_OBTAINED_YR,
384 X_INSTITUTION_SCORE,
385 X_GOVT_SCORE,
386 X_MODE);
387 end ADD_ROW;
388 procedure DELETE_ROW (
389 X_ROWID in VARCHAR2) AS
390 begin
391 Before_DML(
392 p_action => 'DELETE',
393 x_rowid => X_ROWID
394 );
395 delete from IGS_AS_TYPGOV_SCORMP
396 where ROWID = X_ROWID;
397
398 if (sql%notfound) then
399 raise no_data_found;
400 end if;
401 end DELETE_ROW;
402 PROCEDURE Check_Constraints (
403 Column_Name IN VARCHAR2 DEFAULT NULL,
404 Column_Value IN VARCHAR2 DEFAULT NULL
405 )
406 AS
407 BEGIN
408 IF column_name is null then
409 NULL;
410 ELSIF upper(Column_name) = 'SCNDRY_EDU_ASS_TYPE' then
411 new_references.SCNDRY_EDU_ASS_TYPE := column_value;
412 END IF;
413
414
415 IF upper(column_name) = 'SCNDRY_EDU_ASS_TYPE' OR
416 column_name is null Then
417 IF new_references.SCNDRY_EDU_ASS_TYPE <> UPPER(new_references.SCNDRY_EDU_ASS_TYPE) Then
418 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
419 IGS_GE_MSG_STACK.ADD;
420 APP_EXCEPTION.RAISE_EXCEPTION;
421 END IF;
422 END IF;
423
424
425 END Check_Constraints;
426
427 end IGS_AS_TYPGOV_SCORMP_PKG;