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