1 PACKAGE BODY igs_uc_ref_awrdbdy_pkg AS
2 /* $Header: IGSXI25B.pls 115.5 2003/02/28 07:48:41 bayadav noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_uc_ref_awrdbdy%ROWTYPE;
6 new_references igs_uc_ref_awrdbdy%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_year IN NUMBER ,
12 x_sitting IN VARCHAR2,
13 x_awarding_body IN VARCHAR2,
14 x_body_name IN VARCHAR2,
15 x_body_abbrev IN VARCHAR2,
16 x_imported IN VARCHAR2,
17 x_creation_date IN DATE ,
18 x_created_by IN NUMBER ,
19 x_last_update_date IN DATE ,
20 x_last_updated_by IN NUMBER ,
21 x_last_update_login IN NUMBER
22 ) AS
23 /*
24 || Created By : rgopalan
25 || Created On : 01-OCT-2001
26 || Purpose : Initialises the Old and New references for the columns of the table.
27 || Known limitations, enhancements or remarks :
28 || Change History :
29 || Who When What
30 || (reverse chronological order - newest change first)
31 */
32
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM IGS_UC_REF_AWRDBDY
36 WHERE rowid = x_rowid;
37
38 BEGIN
39
40 l_rowid := x_rowid;
41
42 -- Code for setting the Old and New Reference Values.
43 -- Populate Old Values.
44 OPEN cur_old_ref_values;
45 FETCH cur_old_ref_values INTO old_references;
46 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
47 CLOSE cur_old_ref_values;
48 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
49 igs_ge_msg_stack.add;
50 app_exception.raise_exception;
51 RETURN;
52 END IF;
53 CLOSE cur_old_ref_values;
54
55 -- Populate New Values.
56 new_references.year := x_year;
57 new_references.sitting := x_sitting;
58 new_references.awarding_body := x_awarding_body;
59 new_references.body_name := x_body_name;
60 new_references.body_abbrev := x_body_abbrev;
61 new_references.imported := x_imported;
62
63 IF (p_action = 'UPDATE') THEN
64 new_references.creation_date := old_references.creation_date;
65 new_references.created_by := old_references.created_by;
66 ELSE
67 new_references.creation_date := x_creation_date;
68 new_references.created_by := x_created_by;
69 END IF;
70
71 new_references.last_update_date := x_last_update_date;
72 new_references.last_updated_by := x_last_updated_by;
73 new_references.last_update_login := x_last_update_login;
74
75 END set_column_values;
76
77
78 PROCEDURE check_child_existance IS
79 /*
80 || Created By : rgopalan
81 || Created On : 01-OCT-2001
82 || Purpose : Checks for the existance of Child records.
83 || Known limitations, enhancements or remarks :
84 || Change History :
85 || Who When What
86 || (reverse chronological order - newest change first)
87 */
88 BEGIN
89
90 igs_uc_com_ebl_subj_pkg.get_fk_igs_uc_ref_awrdbdy (
91 old_references.awarding_body,
92 old_references.sitting,
93 old_references.year
94 );
95
96 END check_child_existance;
97
98
99 FUNCTION get_pk_for_validation (
100 x_awarding_body IN VARCHAR2,
101 x_sitting IN VARCHAR2,
102 x_year IN NUMBER
103 ) RETURN BOOLEAN AS
104 /*
105 || Created By : rgopalan
106 || Created On : 01-OCT-2001
107 || Purpose : Validates the Primary Key of the table.
108 || Known limitations, enhancements or remarks :
109 || Change History :
110 || Who When What
111 || (reverse chronological order - newest change first)
112 */
113 CURSOR cur_rowid IS
114 SELECT rowid
115 FROM igs_uc_ref_awrdbdy
116 WHERE awarding_body = x_awarding_body
117 AND sitting = x_sitting
118 AND year = x_year ;
119
120 lv_rowid cur_rowid%RowType;
121
122 BEGIN
123
124 OPEN cur_rowid;
125 FETCH cur_rowid INTO lv_rowid;
126 IF (cur_rowid%FOUND) THEN
127 CLOSE cur_rowid;
128 RETURN(TRUE);
129 ELSE
130 CLOSE cur_rowid;
131 RETURN(FALSE);
132 END IF;
133
134 END get_pk_for_validation;
135
136
137 PROCEDURE before_dml (
138 p_action IN VARCHAR2,
139 x_rowid IN VARCHAR2,
140 x_year IN NUMBER ,
141 x_sitting IN VARCHAR2,
142 x_awarding_body IN VARCHAR2,
143 x_body_name IN VARCHAR2,
144 x_body_abbrev IN VARCHAR2,
145 x_imported IN VARCHAR2,
146 x_creation_date IN DATE ,
147 x_created_by IN NUMBER ,
148 x_last_update_date IN DATE ,
149 x_last_updated_by IN NUMBER ,
150 x_last_update_login IN NUMBER
151 ) AS
152 /*
153 || Created By : rgopalan
154 || Created On : 01-OCT-2001
155 || Purpose : Initialises the columns, Checks Constraints, Calls the
156 || Trigger Handlers for the table, before any DML operation.
157 || Known limitations, enhancements or remarks :
158 || Change History :
159 || Who When What
160 || (reverse chronological order - newest change first)
161 */
162 BEGIN
163
164 set_column_values (
165 p_action,
166 x_rowid,
167 x_year,
168 x_sitting,
169 x_awarding_body,
170 x_body_name,
171 x_body_abbrev,
172 x_imported,
173 x_creation_date,
174 x_created_by,
175 x_last_update_date,
176 x_last_updated_by,
177 x_last_update_login
178 );
179
180 IF (p_action = 'INSERT') THEN
181 -- Call all the procedures related to Before Insert.
182 IF ( get_pk_for_validation(
183 new_references.awarding_body,
184 new_references.sitting,
185 new_references.year
186 )
187 ) THEN
188 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
189 igs_ge_msg_stack.add;
190 app_exception.raise_exception;
191 END IF;
192 ELSIF (p_action = 'DELETE') THEN
193 -- Call all the procedures related to Before Delete.
194 check_child_existance;
195 ELSIF (p_action = 'VALIDATE_INSERT') THEN
196 -- Call all the procedures related to Before Insert.
197 IF ( get_pk_for_validation (
198 new_references.awarding_body,
199 new_references.sitting,
200 new_references.year
201 )
202 ) THEN
203 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
204 igs_ge_msg_stack.add;
205 app_exception.raise_exception;
206 END IF;
207 ELSIF (p_action = 'VALIDATE_DELETE') THEN
208 check_child_existance;
209 END IF;
210
211 END before_dml;
212
213
214 PROCEDURE insert_row (
215 x_rowid IN OUT NOCOPY VARCHAR2,
216 x_year IN NUMBER,
217 x_sitting IN VARCHAR2,
218 x_awarding_body IN VARCHAR2,
219 x_body_name IN VARCHAR2,
220 x_body_abbrev IN VARCHAR2,
221 x_imported IN VARCHAR2,
222 x_mode IN VARCHAR2
223 ) AS
224 /*
225 || Created By : rgopalan
226 || Created On : 01-OCT-2001
227 || Purpose : Handles the INSERT DML logic for the table.
228 || Known limitations, enhancements or remarks :
229 || Change History :
230 || Who When What
231 || (reverse chronological order - newest change first)
232 */
233 CURSOR c IS
234 SELECT rowid
235 FROM igs_uc_ref_awrdbdy
236 WHERE awarding_body = x_awarding_body
237 AND sitting = x_sitting
238 AND year = x_year;
239
240 x_last_update_date DATE;
241 x_last_updated_by NUMBER;
242 x_last_update_login NUMBER;
243
244 BEGIN
245
246 x_last_update_date := SYSDATE;
247 IF (x_mode = 'I') THEN
248 x_last_updated_by := 1;
249 x_last_update_login := 0;
250 ELSIF (x_mode = 'R') THEN
251 x_last_updated_by := fnd_global.user_id;
252 IF (x_last_updated_by IS NULL) THEN
253 x_last_updated_by := -1;
254 END IF;
255 x_last_update_login := fnd_global.login_id;
256 IF (x_last_update_login IS NULL) THEN
257 x_last_update_login := -1;
258 END IF;
259 ELSE
260 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
261 igs_ge_msg_stack.add;
262 app_exception.raise_exception;
263 END IF;
264
265 before_dml(
266 p_action => 'INSERT',
267 x_rowid => x_rowid,
268 x_year => x_year,
269 x_sitting => x_sitting,
270 x_awarding_body => x_awarding_body,
271 x_body_name => x_body_name,
272 x_body_abbrev => x_body_abbrev,
273 x_imported => x_imported,
274 x_creation_date => x_last_update_date,
275 x_created_by => x_last_updated_by,
276 x_last_update_date => x_last_update_date,
277 x_last_updated_by => x_last_updated_by,
278 x_last_update_login => x_last_update_login
279 );
280
281 INSERT INTO igs_uc_ref_awrdbdy (
282 year,
283 sitting,
284 awarding_body,
285 body_name,
286 body_abbrev,
287 imported,
288 creation_date,
289 created_by,
290 last_update_date,
291 last_updated_by,
292 last_update_login
293 ) VALUES (
294 new_references.year,
295 new_references.sitting,
296 new_references.awarding_body,
297 new_references.body_name,
298 new_references.body_abbrev,
299 new_references.imported,
300 x_last_update_date,
301 x_last_updated_by,
302 x_last_update_date,
303 x_last_updated_by,
304 x_last_update_login
305 );
306
307 OPEN c;
308 FETCH c INTO x_rowid;
309 IF (c%NOTFOUND) THEN
310 CLOSE c;
311 RAISE NO_DATA_FOUND;
312 END IF;
313 CLOSE c;
314
315 END insert_row;
316
317
318 PROCEDURE lock_row (
319 x_rowid IN VARCHAR2,
320 x_year IN NUMBER,
321 x_sitting IN VARCHAR2,
322 x_awarding_body IN VARCHAR2,
323 x_body_name IN VARCHAR2,
324 x_body_abbrev IN VARCHAR2,
325 x_imported IN VARCHAR2
326 ) AS
327 /*
328 || Created By : rgopalan
329 || Created On : 01-OCT-2001
330 || Purpose : Handles the LOCK mechanism for the table.
331 || Known limitations, enhancements or remarks :
332 || Change History :
333 || Who When What
334 || (reverse chronological order - newest change first)
335 */
336 CURSOR c1 IS
337 SELECT
338 body_name,
339 body_abbrev,
340 imported
341 FROM igs_uc_ref_awrdbdy
342 WHERE rowid = x_rowid
343 FOR UPDATE NOWAIT;
344
345 tlinfo c1%ROWTYPE;
346
347 BEGIN
348
349 OPEN c1;
350 FETCH c1 INTO tlinfo;
351 IF (c1%notfound) THEN
352 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
353 igs_ge_msg_stack.add;
354 CLOSE c1;
355 app_exception.raise_exception;
356 RETURN;
357 END IF;
358 CLOSE c1;
359
360 IF (
361 ((tlinfo.body_name = x_body_name) OR ((tlinfo.body_name IS NULL) AND (X_body_name IS NULL)))
362 AND ((tlinfo.body_abbrev = x_body_abbrev) OR ((tlinfo.body_abbrev IS NULL) AND (X_body_abbrev IS NULL)))
363 AND (tlinfo.imported = x_imported)
364 ) THEN
365 NULL;
366 ELSE
367 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
368 igs_ge_msg_stack.add;
369 app_exception.raise_exception;
370 END IF;
371
372 RETURN;
373
374 END lock_row;
375
376
377 PROCEDURE update_row (
378 x_rowid IN VARCHAR2,
379 x_year IN NUMBER,
380 x_sitting IN VARCHAR2,
381 x_awarding_body IN VARCHAR2,
382 x_body_name IN VARCHAR2,
383 x_body_abbrev IN VARCHAR2,
384 x_imported IN VARCHAR2,
385 x_mode IN VARCHAR2
386 ) AS
387 /*
388 || Created By : rgopalan
389 || Created On : 01-OCT-2001
390 || Purpose : Handles the UPDATE DML logic for the table.
391 || Known limitations, enhancements or remarks :
392 || Change History :
393 || Who When What
394 || (reverse chronological order - newest change first)
395 */
396 x_last_update_date DATE ;
397 x_last_updated_by NUMBER;
398 x_last_update_login NUMBER;
399
400 BEGIN
401
402 x_last_update_date := SYSDATE;
403 IF (X_MODE = 'I') THEN
404 x_last_updated_by := 1;
405 x_last_update_login := 0;
406 ELSIF (x_mode = 'R') THEN
407 x_last_updated_by := fnd_global.user_id;
408 IF x_last_updated_by IS NULL THEN
409 x_last_updated_by := -1;
410 END IF;
411 x_last_update_login := fnd_global.login_id;
412 IF (x_last_update_login IS NULL) THEN
413 x_last_update_login := -1;
414 END IF;
415 ELSE
416 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
417 igs_ge_msg_stack.add;
418 app_exception.raise_exception;
419 END IF;
420
421 before_dml(
422 p_action => 'UPDATE',
423 x_rowid => x_rowid,
424 x_year => x_year,
425 x_sitting => x_sitting,
426 x_awarding_body => x_awarding_body,
427 x_body_name => x_body_name,
428 x_body_abbrev => x_body_abbrev,
429 x_imported => x_imported,
430 x_creation_date => x_last_update_date,
431 x_created_by => x_last_updated_by,
432 x_last_update_date => x_last_update_date,
433 x_last_updated_by => x_last_updated_by,
434 x_last_update_login => x_last_update_login
435 );
436
437 UPDATE igs_uc_ref_awrdbdy
438 SET
439 body_name = new_references.body_name,
440 body_abbrev = new_references.body_abbrev,
441 imported = new_references.imported,
442 last_update_date = x_last_update_date,
443 last_updated_by = x_last_updated_by,
444 last_update_login = x_last_update_login
445 WHERE rowid = x_rowid;
446
447 IF (SQL%NOTFOUND) THEN
448 RAISE NO_DATA_FOUND;
449 END IF;
450
451 END update_row;
452
453
454 PROCEDURE add_row (
455 x_rowid IN OUT NOCOPY VARCHAR2,
456 x_year IN NUMBER,
457 x_sitting IN VARCHAR2,
458 x_awarding_body IN VARCHAR2,
459 x_body_name IN VARCHAR2,
460 x_body_abbrev IN VARCHAR2,
461 x_imported IN VARCHAR2,
462 x_mode IN VARCHAR2
463 ) AS
464 /*
465 || Created By : rgopalan
466 || Created On : 01-OCT-2001
467 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
468 || Known limitations, enhancements or remarks :
469 || Change History :
470 || Who When What
471 || (reverse chronological order - newest change first)
472 */
473 CURSOR c1 IS
474 SELECT rowid
475 FROM igs_uc_ref_awrdbdy
476 WHERE awarding_body = x_awarding_body
477 AND sitting = x_sitting
478 AND year = x_year;
479
480 BEGIN
481
482 OPEN c1;
483 FETCH c1 INTO x_rowid;
484 IF (c1%NOTFOUND) THEN
485 CLOSE c1;
486
487 insert_row (
488 x_rowid,
489 x_year,
490 x_sitting,
491 x_awarding_body,
492 x_body_name,
493 x_body_abbrev,
494 x_imported,
495 x_mode
496 );
497 RETURN;
498 END IF;
499 CLOSE c1;
500
501 update_row (
502 x_rowid,
503 x_year,
504 x_sitting,
505 x_awarding_body,
506 x_body_name,
507 x_body_abbrev,
508 x_imported,
509 x_mode
510 );
511
512 END add_row;
513
514
515 PROCEDURE delete_row (
516 x_rowid IN VARCHAR2
517 ) AS
518 /*
519 || Created By : rgopalan
520 || Created On : 01-OCT-2001
521 || Purpose : Handles the DELETE DML logic for the table.
522 || Known limitations, enhancements or remarks :
523 || Change History :
524 || Who When What
525 || (reverse chronological order - newest change first)
526 */
527 BEGIN
528
529 before_dml (
530 p_action => 'DELETE',
531 x_rowid => x_rowid
532 );
533
534 DELETE FROM igs_uc_ref_awrdbdy
535 WHERE rowid = x_rowid;
536
537 IF (SQL%NOTFOUND) THEN
538 RAISE NO_DATA_FOUND;
539 END IF;
540
541 END delete_row;
542
543
544 END igs_uc_ref_awrdbdy_pkg;
[Home] [Help]