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