[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_SS_DISP_STPS_PKG
Source
1 PACKAGE BODY igs_en_ss_disp_stps_pkg AS
2 /* $Header: IGSEI77B.pls 120.2 2005/06/15 05:06:15 appldev $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_en_ss_disp_stps%ROWTYPE;
6 new_references igs_en_ss_disp_stps%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_ss_display_id IN NUMBER,
12 x_academic_year_flag IN VARCHAR2,
13 x_core_req_ind IN VARCHAR2,
14 x_creation_date IN DATE,
15 x_created_by IN NUMBER,
16 x_last_update_date IN DATE,
17 x_last_updated_by IN NUMBER,
18 x_last_update_login IN NUMBER
19 ) AS
20 /*
21 || Created By : [email protected]
22 || Created On : 29-OCT-2003
23 || Purpose : Initialises the Old and New references for the columns of the table.
24 || Known limitations, enhancements or remarks :
25 || Change History :
26 || Who When What
27 || (reverse chronological order - newest change first)
28 || ctyagi 18-May-2005 Add the parameter x_core_req_ind
29 */
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM igs_en_ss_disp_stps
34 WHERE rowid = x_rowid;
35
36 BEGIN
37
38 l_rowid := x_rowid;
39
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.ss_display_id := x_ss_display_id;
55 new_references.academic_year_flag := x_academic_year_flag;
56 new_references.CORE_REQ_IND := x_core_req_ind;
57
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
66 new_references.last_update_date := x_last_update_date;
67 new_references.last_updated_by := x_last_updated_by;
68 new_references.last_update_login := x_last_update_login;
69
70 END set_column_values;
71
72
73 FUNCTION get_pk_for_validation (
74 x_ss_display_id IN NUMBER
75 ) RETURN BOOLEAN AS
76 /*
77 || Created By : [email protected]
78 || Created On : 29-OCT-2003
79 || Purpose : Validates the Primary Key of the table.
80 || Known limitations, enhancements or remarks :
81 || Change History :
82 || Who When What
83 || (reverse chronological order - newest change first)
84
85 */
86 CURSOR cur_rowid IS
87 SELECT rowid
88 FROM igs_en_ss_disp_stps
89 WHERE ss_display_id = x_ss_display_id
90 FOR UPDATE NOWAIT;
91
92 lv_rowid cur_rowid%RowType;
93
94 BEGIN
95
96 OPEN cur_rowid;
97 FETCH cur_rowid INTO lv_rowid;
98 IF (cur_rowid%FOUND) THEN
99 CLOSE cur_rowid;
100 RETURN(TRUE);
101 ELSE
102 CLOSE cur_rowid;
103 RETURN(FALSE);
104 END IF;
105
106 END get_pk_for_validation;
107
108
109 PROCEDURE before_dml (
110 p_action IN VARCHAR2,
111 x_rowid IN VARCHAR2,
112 x_ss_display_id IN NUMBER,
113 x_academic_year_flag IN VARCHAR2,
114 x_core_req_ind IN VARCHAR2,
115 x_creation_date IN DATE,
116 x_created_by IN NUMBER,
117 x_last_update_date IN DATE,
118 x_last_updated_by IN NUMBER,
119 x_last_update_login IN NUMBER
120 ) AS
121 /*
122 || Created By : [email protected]
123 || Created On : 29-OCT-2003
124 || Purpose : Initialises the columns, Checks Constraints, Calls the
125 || Trigger Handlers for the table, before any DML operation.
126 || Known limitations, enhancements or remarks :
127 || Change History :
128 || Who When What
129 || (reverse chronological order - newest change first)
130 || ctyagi 18-May-2005 Add the parameter x_core_req_ind
131 */
132 BEGIN
133
134 set_column_values (
135 p_action,
136 x_rowid,
137 x_ss_display_id,
138 x_academic_year_flag,
139 x_core_req_ind,
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
147 IF (p_action = 'INSERT') THEN
148 -- Call all the procedures related to Before Insert.
149 IF ( get_pk_for_validation(
150 new_references.ss_display_id
151 )
152 ) 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 ELSIF (p_action = 'VALIDATE_INSERT') THEN
158 -- Call all the procedures related to Before Insert.
159 IF ( get_pk_for_validation (
160 new_references.ss_display_id
161 )
162 ) THEN
163 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
164 igs_ge_msg_stack.add;
165 app_exception.raise_exception;
166 END IF;
167 END IF;
168
169 END before_dml;
170
171
172 PROCEDURE insert_row (
173 x_rowid IN OUT NOCOPY VARCHAR2,
174 x_ss_display_id IN NUMBER,
175 x_academic_year_flag IN VARCHAR2,
176 x_core_req_ind IN VARCHAR2,
177 x_mode IN VARCHAR2
178 ) AS
179 /*
180 || Created By : [email protected]
181 || Created On : 29-OCT-2003
182 || Purpose : Handles the INSERT DML logic for the table.
183 || Known limitations, enhancements or remarks :
184 || Change History :
185 || Who When What
186 || (reverse chronological order - newest change first)
187 || ctyagi 18-May-2005 Add the parameter x_core_req_ind
188 */
189
190 x_last_update_date DATE;
191 x_last_updated_by NUMBER;
192 x_last_update_login NUMBER;
193
194 BEGIN
195
196 x_last_update_date := SYSDATE;
197 IF (x_mode = 'I') THEN
198 x_last_updated_by := 1;
199 x_last_update_login := 0;
200 ELSIF (x_mode = 'R') THEN
201 x_last_updated_by := fnd_global.user_id;
202 IF (x_last_updated_by IS NULL) THEN
203 x_last_updated_by := -1;
204 END IF;
205 x_last_update_login := fnd_global.login_id;
206 IF (x_last_update_login IS NULL) THEN
207 x_last_update_login := -1;
208 END IF;
209 ELSE
210 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
211 fnd_message.set_token ('ROUTINE', 'IGS_EN_SS_DISP_STPS_PKG.INSERT_ROW');
212 igs_ge_msg_stack.add;
213 app_exception.raise_exception;
214 END IF;
215
216 before_dml(
217 p_action => 'INSERT',
218 x_rowid => x_rowid,
219 x_ss_display_id => x_ss_display_id,
220 x_academic_year_flag => x_academic_year_flag,
221 x_core_req_ind => x_core_req_ind,
222 x_creation_date => x_last_update_date,
223 x_created_by => x_last_updated_by,
224 x_last_update_date => x_last_update_date,
225 x_last_updated_by => x_last_updated_by,
226 x_last_update_login => x_last_update_login
227 );
228
229 INSERT INTO igs_en_ss_disp_stps (
230 ss_display_id,
231 academic_year_flag,
232 CORE_REQ_IND,
233 creation_date,
234 created_by,
235 last_update_date,
236 last_updated_by,
237 last_update_login
238 ) VALUES (
239 new_references.ss_display_id,
240 new_references.academic_year_flag,
241 new_references.CORE_REQ_IND,
242 x_last_update_date,
243 x_last_updated_by,
244 x_last_update_date,
245 x_last_updated_by,
246 x_last_update_login
247 ) RETURNING ROWID INTO x_rowid;
248
249 END insert_row;
250
251
252 PROCEDURE lock_row (
253 x_rowid IN VARCHAR2,
254 x_ss_display_id IN NUMBER,
255 x_academic_year_flag IN VARCHAR2,
256 x_core_req_ind IN VARCHAR2
257 ) AS
258 /*
259 || Created By : [email protected]
260 || Created On : 29-OCT-2003
261 || Purpose : Handles the LOCK mechanism for the table.
262 || Known limitations, enhancements or remarks :
263 || Change History :
264 || Who When What
265 || (reverse chronological order - newest change first)
266 || ctyagi 18-May-2005 Add the parameter x_core_req_ind
267 || ctyagi 10-jun-2005 Add x_core_req_ind to lock condition
268 */
269 CURSOR c1 IS
270 SELECT
271 academic_year_flag,core_req_ind
272 FROM igs_en_ss_disp_stps
273 WHERE rowid = x_rowid
274 FOR UPDATE NOWAIT;
275
276 tlinfo c1%ROWTYPE;
277
278 BEGIN
279
280 OPEN c1;
281 FETCH c1 INTO tlinfo;
282 IF (c1%notfound) THEN
283 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
284 igs_ge_msg_stack.add;
285 CLOSE c1;
286 app_exception.raise_exception;
287 RETURN;
288 END IF;
289 CLOSE c1;
290
291 IF (
292 (tlinfo.academic_year_flag = x_academic_year_flag)
293 AND ((tlinfo.core_req_ind = x_core_req_ind)
294 OR ((tlinfo.core_req_ind is null)
295 AND (x_core_req_ind is null)))
296 )
297 THEN
298 NULL;
299 ELSE
300 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
301 igs_ge_msg_stack.add;
302 app_exception.raise_exception;
303 END IF;
304
305 RETURN;
306
307 END lock_row;
308
309
310 PROCEDURE update_row (
311 x_rowid IN VARCHAR2,
312 x_ss_display_id IN NUMBER,
313 x_academic_year_flag IN VARCHAR2,
314 x_core_req_ind IN VARCHAR2,
315 x_mode IN VARCHAR2
316 ) AS
317 /*
318 || Created By : [email protected]
319 || Created On : 29-OCT-2003
320 || Purpose : Handles the UPDATE DML logic for the table.
321 || Known limitations, enhancements or remarks :
322 || Change History :
323 || Who When What
324 || (reverse chronological order - newest change first)
325 || ctyagi 18-May-2005 Add the parameter x_core_req_ind
326 */
327 x_last_update_date DATE ;
328 x_last_updated_by NUMBER;
329 x_last_update_login NUMBER;
330
331 BEGIN
332
333 x_last_update_date := SYSDATE;
334 IF (X_MODE = 'I') THEN
335 x_last_updated_by := 1;
336 x_last_update_login := 0;
337 ELSIF (x_mode = 'R') THEN
338 x_last_updated_by := fnd_global.user_id;
339 IF x_last_updated_by IS NULL THEN
340 x_last_updated_by := -1;
341 END IF;
342 x_last_update_login := fnd_global.login_id;
343 IF (x_last_update_login IS NULL) THEN
344 x_last_update_login := -1;
345 END IF;
346 ELSE
347 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
348 fnd_message.set_token ('ROUTINE', 'IGS_EN_SS_DISP_STPS_PKG.UPDATE_ROW');
349 igs_ge_msg_stack.add;
350 app_exception.raise_exception;
351 END IF;
352
353 before_dml(
354 p_action => 'UPDATE',
355 x_rowid => x_rowid,
356 x_ss_display_id => x_ss_display_id,
357 x_academic_year_flag => x_academic_year_flag,
358 x_core_req_ind => x_core_req_ind,
359 x_creation_date => x_last_update_date,
360 x_created_by => x_last_updated_by,
361 x_last_update_date => x_last_update_date,
362 x_last_updated_by => x_last_updated_by,
363 x_last_update_login => x_last_update_login
364 );
365
366 UPDATE igs_en_ss_disp_stps
367 SET
368 academic_year_flag = new_references.academic_year_flag,
369 CORE_REQ_IND = new_references.CORE_REQ_IND,
370 last_update_date = x_last_update_date,
371 last_updated_by = x_last_updated_by,
372 last_update_login = x_last_update_login
373 WHERE rowid = x_rowid;
374
375 IF (SQL%NOTFOUND) THEN
376 RAISE NO_DATA_FOUND;
377 END IF;
378
379 END update_row;
380
381
382 PROCEDURE add_row (
383 x_rowid IN OUT NOCOPY VARCHAR2,
384 x_ss_display_id IN NUMBER,
385 x_academic_year_flag IN VARCHAR2,
386 x_core_req_ind IN VARCHAR2,
387 x_mode IN VARCHAR2
388 ) AS
389 /*
390 || Created By : [email protected]
391 || Created On : 29-OCT-2003
392 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
393 || Known limitations, enhancements or remarks :
394 || Change History :
395 || Who When What
396 || (reverse chronological order - newest change first)
397 || ctyagi 18-May-2005 Add the parameter x_core_req_ind
398 */
399 CURSOR c1 IS
400 SELECT rowid
401 FROM igs_en_ss_disp_stps
402 WHERE ss_display_id = x_ss_display_id;
403
404 BEGIN
405
406 OPEN c1;
407 FETCH c1 INTO x_rowid;
408 IF (c1%NOTFOUND) THEN
409 CLOSE c1;
410
411 insert_row (
412 x_rowid,
413 x_ss_display_id,
414 x_academic_year_flag,
415 x_core_req_ind,
416 x_mode
417 );
418 RETURN;
419 END IF;
420 CLOSE c1;
421
422 update_row (
423 x_rowid,
424 x_ss_display_id,
425 x_academic_year_flag,
426 x_core_req_ind,
427 x_mode
428 );
429
430 END add_row;
431
432
433 PROCEDURE delete_row (
434 x_rowid IN VARCHAR2
435 ) AS
436 /*
437 || Created By : [email protected]
438 || Created On : 29-OCT-2003
439 || Purpose : Handles the DELETE DML logic for the table.
440 || Known limitations, enhancements or remarks :
441 || Change History :
442 || Who When What
443 || (reverse chronological order - newest change first)
444 */
445 BEGIN
446
447 before_dml (
448 p_action => 'DELETE',
449 x_rowid => x_rowid
450 );
451
452 DELETE FROM igs_en_ss_disp_stps
453 WHERE rowid = x_rowid;
454
455 IF (SQL%NOTFOUND) THEN
456 RAISE NO_DATA_FOUND;
457 END IF;
458
459 END delete_row;
460
461
462 END igs_en_ss_disp_stps_pkg;