1 PACKAGE BODY igs_uc_cond_details_pkg AS
2 /* $Header: IGSXI13B.pls 115.7 2003/02/28 07:46:47 bayadav noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_uc_cond_details%ROWTYPE;
6 new_references igs_uc_cond_details%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_condition_category IN VARCHAR2 ,
12 x_condition_name IN VARCHAR2 ,
13 x_condition_line IN NUMBER ,
14 x_abbreviation IN VARCHAR2 ,
15 x_grade_mark IN VARCHAR2 ,
16 x_points IN VARCHAR2 ,
17 x_subject IN VARCHAR2 ,
18 x_condition_text IN VARCHAR2,
19 x_creation_date IN DATE ,
20 x_created_by IN NUMBER ,
21 x_last_update_date IN DATE ,
22 x_last_updated_by IN NUMBER ,
23 x_last_update_login IN NUMBER
24 ) AS
25 /*
26 || Created By : rgopalan
27 || Created On : 01-OCT-2001
28 || Purpose : Initialises the Old and New references for the columns of the table.
29 || Known limitations, enhancements or remarks :
30 || Change History :
31 || Who When What
32 || (reverse chronological order - newest change first)
33 */
34
35 CURSOR cur_old_ref_values IS
36 SELECT *
37 FROM IGS_UC_COND_DETAILS
38 WHERE rowid = x_rowid;
39
40 BEGIN
41
42 l_rowid := x_rowid;
43
44 -- Code for setting the Old and New Reference Values.
45 -- Populate Old Values.
46 OPEN cur_old_ref_values;
47 FETCH cur_old_ref_values INTO old_references;
48 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
49 CLOSE cur_old_ref_values;
50 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
51 igs_ge_msg_stack.add;
52 app_exception.raise_exception;
53 RETURN;
54 END IF;
55 CLOSE cur_old_ref_values;
56
57 -- Populate New Values.
58 new_references.condition_category := x_condition_category;
59 new_references.condition_name := x_condition_name;
60 new_references.condition_line := x_condition_line;
61 new_references.abbreviation := x_abbreviation;
62 new_references.grade_mark := x_grade_mark;
63 new_references.points := x_points;
64 new_references.subject := x_subject;
65 new_references.condition_text := x_condition_text;
66
67
68 IF (p_action = 'UPDATE') THEN
69 new_references.creation_date := old_references.creation_date;
70 new_references.created_by := old_references.created_by;
71 ELSE
72 new_references.creation_date := x_creation_date;
73 new_references.created_by := x_created_by;
74 END IF;
75
76 new_references.last_update_date := x_last_update_date;
77 new_references.last_updated_by := x_last_updated_by;
78 new_references.last_update_login := x_last_update_login;
79
80 END set_column_values;
81
82
83 PROCEDURE check_parent_existance AS
84 /*
85 || Created By : rgopalan
86 || Created On : 01-OCT-2001
87 || Purpose : Checks for the existance of Parent records.
88 || Known limitations, enhancements or remarks :
89 || Change History :
90 || Who When What
91 || (reverse chronological order - newest change first)
92 */
93 BEGIN
94
95 IF (((old_references.condition_category = new_references.condition_category) AND
96 (old_references.condition_name = new_references.condition_name)) OR
97 ((new_references.condition_category IS NULL) OR
98 (new_references.condition_name IS NULL))) THEN
99 NULL;
100 ELSIF NOT igs_uc_offer_conds_pkg.get_pk_for_validation (
101 new_references.condition_category,
102 new_references.condition_name
103 ) THEN
104 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
105 igs_ge_msg_stack.add;
106 app_exception.raise_exception;
107 END IF;
108
109 IF (((old_references.abbreviation = new_references.abbreviation)) OR
110 ((new_references.abbreviation IS NULL))) THEN
111 NULL;
112 ELSIF NOT igs_uc_ref_off_abrv_pkg.get_pk_for_validation (
113 new_references.abbreviation
114 ) THEN
115 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
116 igs_ge_msg_stack.add;
117 app_exception.raise_exception;
118 END IF;
119
120 END check_parent_existance;
121
122
123 FUNCTION get_pk_for_validation (
124 x_condition_category IN VARCHAR2,
125 x_condition_name IN VARCHAR2,
126 x_condition_line IN NUMBER
127 ) RETURN BOOLEAN AS
128 /*
129 || Created By : rgopalan
130 || Created On : 01-OCT-2001
131 || Purpose : Validates the Primary Key of the table.
132 || Known limitations, enhancements or remarks :
133 || Change History :
134 || Who When What
135 || (reverse chronological order - newest change first)
136 */
137 CURSOR cur_rowid IS
138 SELECT rowid
139 FROM igs_uc_cond_details
140 WHERE condition_category = x_condition_category
141 AND condition_name = x_condition_name
142 AND condition_line = x_condition_line ;
143
144 lv_rowid cur_rowid%RowType;
145
146 BEGIN
147
148 OPEN cur_rowid;
149 FETCH cur_rowid INTO lv_rowid;
150 IF (cur_rowid%FOUND) THEN
151 CLOSE cur_rowid;
152 RETURN(TRUE);
153 ELSE
154 CLOSE cur_rowid;
155 RETURN(FALSE);
156 END IF;
157
158 END get_pk_for_validation;
159
160
161 PROCEDURE get_fk_igs_uc_offer_conds (
162 x_condition_category IN VARCHAR2,
163 x_condition_name IN VARCHAR2
164 ) AS
165 /*
166 || Created By : rgopalan
167 || Created On : 01-OCT-2001
168 || Purpose : Validates the Foreign Keys for the table.
169 || Known limitations, enhancements or remarks :
170 || Change History :
171 || Who When What
172 || (reverse chronological order - newest change first)
173 */
174 CURSOR cur_rowid IS
175 SELECT rowid
176 FROM igs_uc_cond_details
177 WHERE ((condition_category = x_condition_category) AND
178 (condition_name = x_condition_name));
179
180 lv_rowid cur_rowid%RowType;
181
182 BEGIN
183
184 OPEN cur_rowid;
185 FETCH cur_rowid INTO lv_rowid;
186 IF (cur_rowid%FOUND) THEN
187 CLOSE cur_rowid;
188 fnd_message.set_name ('IGS', 'IGS_UC_UCOCDT_UCOC_FK');
189 igs_ge_msg_stack.add;
190 app_exception.raise_exception;
191 RETURN;
192 END IF;
193 CLOSE cur_rowid;
194
195 END get_fk_igs_uc_offer_conds;
196
197 PROCEDURE get_fk_igs_uc_ref_off_abrv (
198 x_abbreviation IN VARCHAR2
199 ) AS
200 /*
201 || Created By : RBEZAWAD
202 || Created On : 17-DEC-2002
203 || Purpose : Validates the Foreign Keys for the table.
204 || Known limitations, enhancements or remarks :
205 || Change History :
206 || Who When What
207 || (reverse chronological order - newest change first)
208 */
209 CURSOR cur_rowid IS
210 SELECT rowid
211 FROM igs_uc_cond_details
212 WHERE ((abbreviation = x_abbreviation));
213
214 lv_rowid cur_rowid%RowType;
215
216 BEGIN
217
218 OPEN cur_rowid;
219 FETCH cur_rowid INTO lv_rowid;
220 IF (cur_rowid%FOUND) THEN
221 CLOSE cur_rowid;
222 fnd_message.set_name ('IGS', 'IGS_UC_UCCD_UROA_FK');
223 igs_ge_msg_stack.add;
224 app_exception.raise_exception;
225 RETURN;
226 END IF;
227 CLOSE cur_rowid;
228
229 END get_fk_igs_uc_ref_off_abrv;
230
231 PROCEDURE before_dml (
232 p_action IN VARCHAR2,
233 x_rowid IN VARCHAR2 ,
234 x_condition_category IN VARCHAR2 ,
235 x_condition_name IN VARCHAR2 ,
236 x_condition_line IN NUMBER ,
237 x_abbreviation IN VARCHAR2 ,
238 x_grade_mark IN VARCHAR2 ,
239 x_points IN VARCHAR2 ,
240 x_subject IN VARCHAR2 ,
241 x_condition_text IN VARCHAR2,
242 x_creation_date IN DATE ,
243 x_created_by IN NUMBER ,
244 x_last_update_date IN DATE ,
245 x_last_updated_by IN NUMBER ,
246 x_last_update_login IN NUMBER
247 ) AS
248 /*
249 || Created By : rgopalan
250 || Created On : 01-OCT-2001
251 || Purpose : Initialises the columns, Checks Constraints, Calls the
252 || Trigger Handlers for the table, before any DML operation.
253 || Known limitations, enhancements or remarks :
254 || Change History :
255 || Who When What
256 || (reverse chronological order - newest change first)
257 */
258 BEGIN
259
260 set_column_values (
261 p_action,
262 x_rowid,
263 x_condition_category,
264 x_condition_name,
265 x_condition_line,
266 x_abbreviation,
267 x_grade_mark,
268 x_points,
269 x_subject,
270 x_condition_text ,
271 x_creation_date,
272 x_created_by,
273 x_last_update_date,
274 x_last_updated_by,
275 x_last_update_login
276 );
277
278 IF (p_action = 'INSERT') THEN
279 -- Call all the procedures related to Before Insert.
280 IF ( get_pk_for_validation(
281 new_references.condition_category,
282 new_references.condition_name,
283 new_references.condition_line
284 )
285 ) THEN
286 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
287 igs_ge_msg_stack.add;
288 app_exception.raise_exception;
289 END IF;
290 check_parent_existance;
291 ELSIF (p_action = 'UPDATE') THEN
292 -- Call all the procedures related to Before Update.
293 check_parent_existance;
294 ELSIF (p_action = 'VALIDATE_INSERT') THEN
295 -- Call all the procedures related to Before Insert.
296 IF ( get_pk_for_validation (
297 new_references.condition_category,
298 new_references.condition_name,
299 new_references.condition_line
300 )
301 ) THEN
302 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
303 igs_ge_msg_stack.add;
304 app_exception.raise_exception;
305 END IF;
306 END IF;
307
308 END before_dml;
309
310
311 PROCEDURE insert_row (
312 x_rowid IN OUT NOCOPY VARCHAR2,
313 x_condition_category IN VARCHAR2,
314 x_condition_name IN VARCHAR2,
315 x_condition_line IN NUMBER,
316 x_abbreviation IN VARCHAR2,
317 x_grade_mark IN VARCHAR2,
318 x_points IN VARCHAR2,
319 x_subject IN VARCHAR2,
320 x_condition_text IN VARCHAR2,
321 x_mode IN VARCHAR2
322 ) AS
323 /*
324 || Created By : rgopalan
325 || Created On : 01-OCT-2001
326 || Purpose : Handles the INSERT DML logic for the table.
327 || Known limitations, enhancements or remarks :
328 || Change History :
329 || Who When What
330 || (reverse chronological order - newest change first)
331 */
332 CURSOR c IS
333 SELECT rowid
334 FROM igs_uc_cond_details
335 WHERE condition_category = x_condition_category
336 AND condition_name = x_condition_name
340 x_last_updated_by NUMBER;
337 AND condition_line = x_condition_line;
338
339 x_last_update_date DATE;
341 x_last_update_login NUMBER;
342
343 BEGIN
344
345 x_last_update_date := SYSDATE;
346 IF (x_mode = 'I') THEN
347 x_last_updated_by := 1;
348 x_last_update_login := 0;
349 ELSIF (x_mode = 'R') THEN
350 x_last_updated_by := fnd_global.user_id;
351 IF (x_last_updated_by IS NULL) THEN
352 x_last_updated_by := -1;
353 END IF;
354 x_last_update_login := fnd_global.login_id;
355 IF (x_last_update_login IS NULL) THEN
356 x_last_update_login := -1;
357 END IF;
358 ELSE
359 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
360 igs_ge_msg_stack.add;
361 app_exception.raise_exception;
362 END IF;
363
364 before_dml(
365 p_action => 'INSERT',
366 x_rowid => x_rowid,
367 x_condition_category => x_condition_category,
368 x_condition_name => x_condition_name,
369 x_condition_line => x_condition_line,
370 x_abbreviation => x_abbreviation,
371 x_grade_mark => x_grade_mark,
372 x_points => x_points,
373 x_subject => x_subject,
374 x_condition_text => x_condition_text,
375 x_creation_date => x_last_update_date,
376 x_created_by => x_last_updated_by,
377 x_last_update_date => x_last_update_date,
378 x_last_updated_by => x_last_updated_by,
379 x_last_update_login => x_last_update_login
380 );
381
382 INSERT INTO igs_uc_cond_details (
383 condition_category,
384 condition_name,
385 condition_line,
386 abbreviation,
387 grade_mark,
388 points,
389 subject,
390 condition_text ,
391 creation_date,
392 created_by,
393 last_update_date,
394 last_updated_by,
395 last_update_login
396 ) VALUES (
397 new_references.condition_category,
398 new_references.condition_name,
399 new_references.condition_line,
400 new_references.abbreviation,
401 new_references.grade_mark,
402 new_references.points,
403 new_references.subject,
404 new_references.condition_text ,
405 x_last_update_date,
406 x_last_updated_by,
407 x_last_update_date,
408 x_last_updated_by,
409 x_last_update_login
410 );
411
412 OPEN c;
413 FETCH c INTO x_rowid;
414 IF (c%NOTFOUND) THEN
415 CLOSE c;
416 RAISE NO_DATA_FOUND;
417 END IF;
418 CLOSE c;
419
420 END insert_row;
421
422
423 PROCEDURE lock_row (
424 x_rowid IN VARCHAR2,
425 x_condition_category IN VARCHAR2,
426 x_condition_name IN VARCHAR2,
427 x_condition_line IN NUMBER,
428 x_abbreviation IN VARCHAR2,
429 x_grade_mark IN VARCHAR2,
430 x_points IN VARCHAR2,
431 x_subject IN VARCHAR2,
432 x_condition_text IN VARCHAR2
433 ) AS
434 /*
435 || Created By : rgopalan
436 || Created On : 01-OCT-2001
437 || Purpose : Handles the LOCK mechanism for the table.
438 || Known limitations, enhancements or remarks :
439 || Change History :
440 || Who When What
441 || (reverse chronological order - newest change first)
442 */
443 CURSOR c1 IS
444 SELECT
445 abbreviation,
446 grade_mark,
447 points,
448 subject,
449 condition_text
450 FROM igs_uc_cond_details
451 WHERE rowid = x_rowid
452 FOR UPDATE NOWAIT;
453
454 tlinfo c1%ROWTYPE;
455
456 BEGIN
457
458 OPEN c1;
459 FETCH c1 INTO tlinfo;
460 IF (c1%notfound) THEN
461 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
462 igs_ge_msg_stack.add;
463 CLOSE c1;
464 app_exception.raise_exception;
465 RETURN;
466 END IF;
467 CLOSE c1;
468
469 IF (
470 ((tlinfo.abbreviation = x_abbreviation) OR ((tlinfo.abbreviation IS NULL) AND (X_abbreviation IS NULL)))
471 AND ((tlinfo.grade_mark = x_grade_mark) OR ((tlinfo.grade_mark IS NULL) AND (X_grade_mark IS NULL)))
472 AND ((tlinfo.points = x_points) OR ((tlinfo.points IS NULL) AND (X_points IS NULL)))
473 AND ((tlinfo.subject = x_subject) OR ((tlinfo.subject IS NULL) AND (X_subject IS NULL)))
474 AND ((tlinfo.condition_text = x_condition_text) OR ((tlinfo.condition_text IS NULL) AND (X_condition_text IS NULL)))
475 ) THEN
476 NULL;
477 ELSE
481 END IF;
478 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
479 igs_ge_msg_stack.add;
480 app_exception.raise_exception;
482
483 RETURN;
484
485 END lock_row;
486
487
488 PROCEDURE update_row (
489 x_rowid IN VARCHAR2,
490 x_condition_category IN VARCHAR2,
491 x_condition_name IN VARCHAR2,
492 x_condition_line IN NUMBER,
493 x_abbreviation IN VARCHAR2,
494 x_grade_mark IN VARCHAR2,
495 x_points IN VARCHAR2,
496 x_subject IN VARCHAR2,
497 x_condition_text IN VARCHAR2,
498 x_mode IN VARCHAR2
499 ) AS
500 /*
501 || Created By : rgopalan
502 || Created On : 01-OCT-2001
503 || Purpose : Handles the UPDATE DML logic for the table.
504 || Known limitations, enhancements or remarks :
505 || Change History :
506 || Who When What
507 || (reverse chronological order - newest change first)
508 */
509 x_last_update_date DATE ;
510 x_last_updated_by NUMBER;
511 x_last_update_login NUMBER;
512
513 BEGIN
514
515 x_last_update_date := SYSDATE;
516 IF (X_MODE = 'I') THEN
517 x_last_updated_by := 1;
518 x_last_update_login := 0;
519 ELSIF (x_mode = 'R') THEN
520 x_last_updated_by := fnd_global.user_id;
521 IF x_last_updated_by IS NULL THEN
522 x_last_updated_by := -1;
523 END IF;
524 x_last_update_login := fnd_global.login_id;
525 IF (x_last_update_login IS NULL) THEN
526 x_last_update_login := -1;
527 END IF;
528 ELSE
529 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
530 igs_ge_msg_stack.add;
531 app_exception.raise_exception;
532 END IF;
533
534 before_dml(
535 p_action => 'UPDATE',
536 x_rowid => x_rowid,
537 x_condition_category => x_condition_category,
538 x_condition_name => x_condition_name,
539 x_condition_line => x_condition_line,
540 x_abbreviation => x_abbreviation,
541 x_grade_mark => x_grade_mark,
542 x_points => x_points,
543 x_subject => x_subject,
544 x_condition_text => x_condition_text,
545 x_creation_date => x_last_update_date,
546 x_created_by => x_last_updated_by,
547 x_last_update_date => x_last_update_date,
548 x_last_updated_by => x_last_updated_by,
549 x_last_update_login => x_last_update_login
550 );
551
552 UPDATE igs_uc_cond_details
553 SET
554 abbreviation = new_references.abbreviation,
555 grade_mark = new_references.grade_mark,
556 points = new_references.points,
557 subject = new_references.subject,
558 condition_text = new_references.condition_text,
559 last_update_date = x_last_update_date,
560 last_updated_by = x_last_updated_by,
561 last_update_login = x_last_update_login
562 WHERE rowid = x_rowid;
563
564 IF (SQL%NOTFOUND) THEN
565 RAISE NO_DATA_FOUND;
566 END IF;
567
568 END update_row;
569
570
571 PROCEDURE add_row (
572 x_rowid IN OUT NOCOPY VARCHAR2,
573 x_condition_category IN VARCHAR2,
574 x_condition_name IN VARCHAR2,
575 x_condition_line IN NUMBER,
576 x_abbreviation IN VARCHAR2,
577 x_grade_mark IN VARCHAR2,
578 x_points IN VARCHAR2,
579 x_subject IN VARCHAR2,
580 x_condition_text IN VARCHAR2,
581 x_mode IN VARCHAR2
582 ) AS
583 /*
584 || Created By : rgopalan
585 || Created On : 01-OCT-2001
586 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
587 || Known limitations, enhancements or remarks :
588 || Change History :
589 || Who When What
590 || (reverse chronological order - newest change first)
591 */
592 CURSOR c1 IS
593 SELECT rowid
594 FROM igs_uc_cond_details
595 WHERE condition_category = x_condition_category
596 AND condition_name = x_condition_name
597 AND condition_line = x_condition_line;
598
599 BEGIN
600
601 OPEN c1;
602 FETCH c1 INTO x_rowid;
603 IF (c1%NOTFOUND) THEN
604 CLOSE c1;
605
606 insert_row (
607 x_rowid,
608 x_condition_category,
609 x_condition_name,
610 x_condition_line,
611 x_abbreviation,
612 x_grade_mark,
613 x_points,
614 x_subject,
615 x_condition_text ,
616 x_mode
617 );
618 RETURN;
619 END IF;
620 CLOSE c1;
621
622 update_row (
623 x_rowid,
624 x_condition_category,
625 x_condition_name,
626 x_condition_line,
627 x_abbreviation,
628 x_grade_mark,
629 x_points,
630 x_subject,
631 x_condition_text ,
632 x_mode
633 );
634
635 END add_row;
636
637
638 PROCEDURE delete_row (
639 x_rowid IN VARCHAR2
640 ) AS
641 /*
642 || Created By : rgopalan
643 || Created On : 01-OCT-2001
644 || Purpose : Handles the DELETE DML logic for the table.
645 || Known limitations, enhancements or remarks :
646 || Change History :
647 || Who When What
648 || (reverse chronological order - newest change first)
649 */
650 BEGIN
651
652 before_dml (
653 p_action => 'DELETE',
654 x_rowid => x_rowid
655 );
656
657 DELETE FROM igs_uc_cond_details
658 WHERE rowid = x_rowid;
659
660 IF (SQL%NOTFOUND) THEN
661 RAISE NO_DATA_FOUND;
662 END IF;
663
664 END delete_row;
665
666
667 END igs_uc_cond_details_pkg;
[Home] [Help]