1 PACKAGE BODY igs_pe_athletic_prg_pkg AS
2 /* $Header: IGSNI86B.pls 120.1 2005/06/28 05:11:24 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_athletic_prg%ROWTYPE;
6 new_references igs_pe_athletic_prg%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_athletic_prg_id IN NUMBER ,
12 x_person_id IN NUMBER ,
13 x_athletic_prg_code IN VARCHAR2 ,
14 x_rating IN VARCHAR2 ,
15 x_start_date IN DATE ,
16 x_end_date IN DATE ,
17 x_recruited_ind IN VARCHAR2 ,
18 x_participating_ind IN VARCHAR2 ,
19 x_last_update_dt IN DATE ,
20 x_creation_date IN DATE ,
21 x_created_by IN NUMBER ,
22 x_last_update_date IN DATE ,
23 x_last_updated_by IN NUMBER ,
24 x_last_update_login IN NUMBER
25 ) AS
26 /*
27 || Created By : cdcruz
28 || Created On : 21-SEP-2001
29 || Purpose : Initialises the Old and New references for the columns of the table.
30 || Known limitations, enhancements or remarks :
31 || Change History :
32 || Who When What
33 || (reverse chronological order - newest change first)
34 */
35
36 CURSOR cur_old_ref_values IS
37 SELECT *
38 FROM igs_pe_athletic_prg
39 WHERE rowid = x_rowid;
40
41 BEGIN
42
43 l_rowid := x_rowid;
44
45 -- Code for setting the Old and New Reference Values.
46 -- Populate Old Values.
47 OPEN cur_old_ref_values;
48 FETCH cur_old_ref_values INTO old_references;
49 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
50 CLOSE cur_old_ref_values;
51 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
52 igs_ge_msg_stack.add;
53 app_exception.raise_exception;
54 RETURN;
55 END IF;
56 CLOSE cur_old_ref_values;
57
58 -- Populate New Values.
59 new_references.athletic_prg_id := x_athletic_prg_id;
60 new_references.person_id := x_person_id;
61 new_references.athletic_prg_code := x_athletic_prg_code;
62 new_references.rating := x_rating;
63 new_references.start_date := x_start_date;
64 new_references.end_date := x_end_date;
65 new_references.recruited_ind := x_recruited_ind;
66 new_references.participating_ind := x_participating_ind;
67 new_references.last_update_dt := x_last_update_dt;
68
69 IF (p_action = 'UPDATE') THEN
70 new_references.creation_date := old_references.creation_date;
71 new_references.created_by := old_references.created_by;
72 ELSE
73 new_references.creation_date := x_creation_date;
74 new_references.created_by := x_created_by;
75 END IF;
76
77 new_references.last_update_date := x_last_update_date;
78 new_references.last_updated_by := x_last_updated_by;
79 new_references.last_update_login := x_last_update_login;
80
81 END set_column_values;
82
83
84 PROCEDURE check_uniqueness AS
85 /*
86 || Created By : cdcruz
87 || Created On : 21-SEP-2001
88 || Purpose : Handles the Unique Constraint logic defined for the columns.
89 || Known limitations, enhancements or remarks :
90 || Change History :
91 || Who When What
92 || (reverse chronological order - newest change first)
93 */
94 BEGIN
95
96 IF ( get_uk_for_validation (
97 new_references.person_id,
98 new_references.athletic_prg_code,
99 new_references.start_date
100 )
101 ) THEN
102 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
103 igs_ge_msg_stack.add;
104 app_exception.raise_exception;
105 END IF;
106
107 END check_uniqueness;
108
109
110 PROCEDURE check_parent_existance AS
111 /*
112 || Created By : cdcruz
113 || Created On : 21-SEP-2001
114 || Purpose : Checks for the existance of Parent records.
115 || Known limitations, enhancements or remarks :
116 || Change History :
117 || Who When What
118 || (reverse chronological order - newest change first)
119 */
120 BEGIN
121
122 IF (((old_references.athletic_prg_code = new_references.athletic_prg_code)) OR
123 ((new_references.athletic_prg_code IS NULL))) THEN
124 NULL;
125
126 --kumma, 2608360 replaced igs_ad_code_classes_pkg with igs_lookups_view_pkg
127 ELSIF NOT IGS_LOOKUPS_view_Pkg.Get_PK_For_Validation (
128 'PE_ATH_PRG_TYPE',
129 new_references.athletic_prg_code
130 ) THEN
131 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
132 igs_ge_msg_stack.add;
133 app_exception.raise_exception;
134 END IF;
135
136 IF (((old_references.person_id = new_references.person_id)) OR
137 ((new_references.person_id IS NULL))) THEN
138 NULL;
139 ELSIF NOT igs_pe_person_pkg.get_pk_for_validation (
140 new_references.person_id
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
147 END check_parent_existance;
148
149
150 FUNCTION get_pk_for_validation (
151 x_athletic_prg_id IN NUMBER
152 ) RETURN BOOLEAN AS
153 /*
154 || Created By : cdcruz
155 || Created On : 21-SEP-2001
156 || Purpose : Validates the Primary Key of the table.
157 || Known limitations, enhancements or remarks :
158 || Change History :
159 || Who When What
160 || (reverse chronological order - newest change first)
161 */
162 CURSOR cur_rowid IS
163 SELECT rowid
164 FROM igs_pe_athletic_prg
165 WHERE athletic_prg_id = x_athletic_prg_id
166 FOR UPDATE NOWAIT;
167
168 lv_rowid cur_rowid%RowType;
169
170 BEGIN
171
172 OPEN cur_rowid;
173 FETCH cur_rowid INTO lv_rowid;
174 IF (cur_rowid%FOUND) THEN
175 CLOSE cur_rowid;
176 RETURN(TRUE);
177 ELSE
178 CLOSE cur_rowid;
179 RETURN(FALSE);
180 END IF;
181
182 END get_pk_for_validation;
183
184
185 FUNCTION get_uk_for_validation (
186 x_person_id IN NUMBER,
187 x_athletic_prg_code IN VARCHAR2,
188 x_start_date IN DATE
189 ) RETURN BOOLEAN AS
190 /*
191 || Created By : cdcruz
192 || Created On : 21-SEP-2001
193 || Purpose : Validates the Unique Keys of the table.
194 || Known limitations, enhancements or remarks :
195 || Change History :
196 || Who When What
197 || (reverse chronological order - newest change first)
198 */
199 CURSOR cur_rowid IS
200 SELECT rowid
201 FROM igs_pe_athletic_prg
202 WHERE person_id = x_person_id
203 AND athletic_prg_code = x_athletic_prg_code
204 AND start_date = x_start_date
205 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
206
207 lv_rowid cur_rowid%RowType;
208
209 BEGIN
210
211 OPEN cur_rowid;
212 FETCH cur_rowid INTO lv_rowid;
213 IF (cur_rowid%FOUND) THEN
214 CLOSE cur_rowid;
215 RETURN (true);
216 ELSE
217 CLOSE cur_rowid;
218 RETURN(FALSE);
219 END IF;
220
221 END get_uk_for_validation ;
222
223
224 PROCEDURE get_fk_igs_ad_code_classes (
225 x_code_id IN VARCHAR2
226 ) AS
227 /*
228 || Created By : cdcruz
229 || Created On : 21-SEP-2001
230 || Purpose : Validates the Foreign Keys for the table.
231 || Known limitations, enhancements or remarks :
232 || Change History :
233 || Who When What
234 || (reverse chronological order - newest change first)
235 */
236 CURSOR cur_rowid IS
237 SELECT rowid
238 FROM igs_pe_athletic_prg
239 WHERE ((athletic_prg_code = x_code_id));
240
241 lv_rowid cur_rowid%RowType;
242
243 BEGIN
244
245 OPEN cur_rowid;
246 FETCH cur_rowid INTO lv_rowid;
247 IF (cur_rowid%FOUND) THEN
248 CLOSE cur_rowid;
249 fnd_message.set_name ('IGS', 'IGS_PE_PAP_ADCC_FK');
250 igs_ge_msg_stack.add;
251 app_exception.raise_exception;
252 RETURN;
253 END IF;
254 CLOSE cur_rowid;
255
256 END get_fk_igs_ad_code_classes;
257
258
259 PROCEDURE get_fk_hz_parties (
260 x_party_id IN NUMBER
261 ) AS
262 /*
263 || Created By : cdcruz
264 || Created On : 21-SEP-2001
265 || Purpose : Validates the Foreign Keys for the table.
266 || Known limitations, enhancements or remarks :
267 || Change History :
268 || Who When What
269 || (reverse chronological order - newest change first)
270 */
271 CURSOR cur_rowid IS
272 SELECT rowid
273 FROM igs_pe_athletic_prg
274 WHERE ((person_id = x_party_id));
275
276 lv_rowid cur_rowid%RowType;
277
278 BEGIN
279
280 OPEN cur_rowid;
281 FETCH cur_rowid INTO lv_rowid;
282 IF (cur_rowid%FOUND) THEN
283 CLOSE cur_rowid;
284 fnd_message.set_name ('IGS', 'IGS_PE_PAP_HZ_FK');
285 igs_ge_msg_stack.add;
286 app_exception.raise_exception;
287 RETURN;
288 END IF;
289 CLOSE cur_rowid;
290
291 END get_fk_hz_parties;
292
293
294 PROCEDURE before_dml (
295 p_action IN VARCHAR2,
296 x_rowid IN VARCHAR2 ,
297 x_athletic_prg_id IN NUMBER ,
298 x_person_id IN NUMBER ,
299 x_athletic_prg_code IN VARCHAR2 ,
300 x_rating IN VARCHAR2 ,
301 x_start_date IN DATE ,
302 x_end_date IN DATE ,
303 x_recruited_ind IN VARCHAR2 ,
304 x_participating_ind IN VARCHAR2 ,
305 x_last_update_dt IN DATE ,
306 x_creation_date IN DATE ,
307 x_created_by IN NUMBER ,
308 x_last_update_date IN DATE ,
309 x_last_updated_by IN NUMBER ,
310 x_last_update_login IN NUMBER
311 ) AS
312 /*
313 || Created By : cdcruz
314 || Created On : 21-SEP-2001
315 || Purpose : Initialises the columns, Checks Constraints, Calls the
316 || Trigger Handlers for the table, before any DML operation.
317 || Known limitations, enhancements or remarks :
318 || Change History :
319 || Who When What
320 || (reverse chronological order - newest change first)
321 */
322 BEGIN
323
324 set_column_values (
325 p_action,
326 x_rowid,
327 x_athletic_prg_id,
328 x_person_id,
329 x_athletic_prg_code,
330 x_rating,
331 x_start_date,
332 x_end_date,
333 x_recruited_ind,
334 x_participating_ind,
335 x_last_update_dt,
336 x_creation_date,
337 x_created_by,
338 x_last_update_date,
339 x_last_updated_by,
340 x_last_update_login
341 );
342
343 IF (p_action = 'INSERT') THEN
344 -- Call all the procedures related to Before Insert.
345 IF ( get_pk_for_validation(
346 new_references.athletic_prg_id
347 )
348 ) THEN
349 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
350 igs_ge_msg_stack.add;
351 app_exception.raise_exception;
352 END IF;
353 check_uniqueness;
354 check_parent_existance;
355 ELSIF (p_action = 'UPDATE') THEN
356 -- Call all the procedures related to Before Update.
357 check_uniqueness;
358 check_parent_existance;
359 ELSIF (p_action = 'VALIDATE_INSERT') THEN
360 -- Call all the procedures related to Before Insert.
361 IF ( get_pk_for_validation (
362 new_references.athletic_prg_id
363 )
364 ) THEN
365 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
366 igs_ge_msg_stack.add;
367 app_exception.raise_exception;
368 END IF;
369 check_uniqueness;
370 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
371 check_uniqueness;
372 END IF;
373
374 END before_dml;
375
376
377 PROCEDURE insert_row (
378 x_rowid IN OUT NOCOPY VARCHAR2,
379 x_athletic_prg_id IN OUT NOCOPY NUMBER,
380 x_person_id IN NUMBER,
381 x_athletic_prg_code IN VARCHAR2,
382 x_rating IN VARCHAR2,
383 x_start_date IN DATE,
384 x_end_date IN DATE,
385 x_recruited_ind IN VARCHAR2,
386 x_participating_ind IN VARCHAR2,
387 x_last_update_dt IN DATE,
388 x_mode IN VARCHAR2
389 ) AS
390 /*
391 || Created By : cdcruz
392 || Created On : 21-SEP-2001
393 || Purpose : Handles the INSERT DML logic for the table.
394 || Known limitations, enhancements or remarks :
395 || Change History :
396 || Who When What
397 || (reverse chronological order - newest change first)
398 */
399 CURSOR c IS
400 SELECT rowid
401 FROM igs_pe_athletic_prg
402 WHERE athletic_prg_id = x_athletic_prg_id;
403
404 x_last_update_date DATE;
405 x_last_updated_by NUMBER;
406 x_last_update_login NUMBER;
407
408 BEGIN
409
410 x_last_update_date := SYSDATE;
411 IF (x_mode = 'I') THEN
412 x_last_updated_by := 1;
413 x_last_update_login := 0;
414 ELSIF (X_MODE IN ('R', 'S')) THEN
415 x_last_updated_by := fnd_global.user_id;
416 IF (x_last_updated_by IS NULL) THEN
417 x_last_updated_by := -1;
418 END IF;
419 x_last_update_login := fnd_global.login_id;
420 IF (x_last_update_login IS NULL) THEN
421 x_last_update_login := -1;
422 END IF;
423 ELSE
424 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
425 igs_ge_msg_stack.add;
426 app_exception.raise_exception;
427 END IF;
428
429 SELECT igs_pe_athletic_prg_s.NEXTVAL
430 INTO x_athletic_prg_id
434 p_action => 'INSERT',
431 FROM dual;
432
433 before_dml(
435 x_rowid => x_rowid,
436 x_athletic_prg_id => x_athletic_prg_id,
437 x_person_id => x_person_id,
438 x_athletic_prg_code => x_athletic_prg_code,
439 x_rating => x_rating,
440 x_start_date => x_start_date,
441 x_end_date => x_end_date,
442 x_recruited_ind => x_recruited_ind,
443 x_participating_ind => x_participating_ind,
444 x_last_update_dt => x_last_update_dt,
445 x_creation_date => x_last_update_date,
446 x_created_by => x_last_updated_by,
447 x_last_update_date => x_last_update_date,
448 x_last_updated_by => x_last_updated_by,
449 x_last_update_login => x_last_update_login
450 );
451
452 IF (x_mode = 'S') THEN
453 igs_sc_gen_001.set_ctx('R');
454 END IF;
455 INSERT INTO igs_pe_athletic_prg (
456 athletic_prg_id,
457 person_id,
458 athletic_prg_code,
459 rating,
460 start_date,
461 end_date,
462 recruited_ind,
463 participating_ind,
464 last_update_dt,
465 creation_date,
466 created_by,
467 last_update_date,
468 last_updated_by,
469 last_update_login
470 ) VALUES (
471 new_references.athletic_prg_id,
472 new_references.person_id,
473 new_references.athletic_prg_code,
474 new_references.rating,
475 new_references.start_date,
476 new_references.end_date,
477 new_references.recruited_ind,
478 new_references.participating_ind,
479 new_references.last_update_dt,
480 x_last_update_date,
481 x_last_updated_by,
482 x_last_update_date,
483 x_last_updated_by,
484 x_last_update_login
485 );
486 IF (x_mode = 'S') THEN
487 igs_sc_gen_001.unset_ctx('R');
488 END IF;
489
490
491 OPEN c;
492 FETCH c INTO x_rowid;
493 IF (c%NOTFOUND) THEN
494 CLOSE c;
495 RAISE NO_DATA_FOUND;
496 END IF;
497 CLOSE c;
498
499
500 EXCEPTION
501 WHEN OTHERS THEN
502 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
503 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
504 fnd_message.set_token ('ERR_CD', SQLCODE);
505 igs_ge_msg_stack.add;
506 igs_sc_gen_001.unset_ctx('R');
507 app_exception.raise_exception;
508 ELSE
509 igs_sc_gen_001.unset_ctx('R');
510 RAISE;
511 END IF;
512 END insert_row;
513
514
515 PROCEDURE lock_row (
516 x_rowid IN VARCHAR2,
517 x_athletic_prg_id IN NUMBER,
518 x_person_id IN NUMBER,
519 x_athletic_prg_code IN VARCHAR2,
520 x_rating IN VARCHAR2,
521 x_start_date IN DATE,
522 x_end_date IN DATE,
523 x_recruited_ind IN VARCHAR2,
524 x_participating_ind IN VARCHAR2,
525 x_last_update_dt IN DATE
526 ) AS
527 /*
528 || Created By : cdcruz
529 || Created On : 21-SEP-2001
530 || Purpose : Handles the LOCK mechanism for the table.
531 || Known limitations, enhancements or remarks :
532 || Change History :
533 || Who When What
534 || (reverse chronological order - newest change first)
535 */
536 CURSOR c1 IS
537 SELECT
538 person_id,
539 athletic_prg_code,
540 rating,
541 start_date,
542 end_date,
543 recruited_ind,
544 participating_ind,
545 last_update_dt
546 FROM igs_pe_athletic_prg
547 WHERE rowid = x_rowid
548 FOR UPDATE NOWAIT;
549
550 tlinfo c1%ROWTYPE;
551
552 BEGIN
553
554 OPEN c1;
555 FETCH c1 INTO tlinfo;
556 IF (c1%notfound) THEN
557 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
558 igs_ge_msg_stack.add;
559 CLOSE c1;
560 app_exception.raise_exception;
561 RETURN;
562 END IF;
563 CLOSE c1;
564
565 IF (
566 (tlinfo.person_id = x_person_id)
567 AND (tlinfo.athletic_prg_code = x_athletic_prg_code)
568 AND ((tlinfo.rating = x_rating) OR ((tlinfo.rating IS NULL) AND (X_rating IS NULL)))
569 AND (tlinfo.start_date = x_start_date)
570 AND ((tlinfo.end_date = x_end_date) OR ((tlinfo.end_date IS NULL) AND (X_end_date IS NULL)))
571 AND (tlinfo.recruited_ind = x_recruited_ind)
572 AND (tlinfo.participating_ind = x_participating_ind)
573 AND ((tlinfo.last_update_dt = x_last_update_dt) OR ((tlinfo.last_update_dt IS NULL) AND (X_last_update_dt IS NULL)))
574 ) THEN
575 NULL;
576 ELSE
577 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
578 igs_ge_msg_stack.add;
579 app_exception.raise_exception;
580 END IF;
584 END lock_row;
581
582 RETURN;
583
585
586
587 PROCEDURE update_row (
588 x_rowid IN VARCHAR2,
589 x_athletic_prg_id IN NUMBER,
590 x_person_id IN NUMBER,
591 x_athletic_prg_code IN VARCHAR2,
592 x_rating IN VARCHAR2,
593 x_start_date IN DATE,
594 x_end_date IN DATE,
595 x_recruited_ind IN VARCHAR2,
596 x_participating_ind IN VARCHAR2,
597 x_last_update_dt IN DATE,
598 x_mode IN VARCHAR2
599 ) AS
600 /*
601 || Created By : cdcruz
602 || Created On : 21-SEP-2001
603 || Purpose : Handles the UPDATE DML logic for the table.
604 || Known limitations, enhancements or remarks :
605 || Change History :
606 || Who When What
607 || (reverse chronological order - newest change first)
608 */
609 x_last_update_date DATE ;
610 x_last_updated_by NUMBER;
611 x_last_update_login NUMBER;
612
613 BEGIN
614
615 x_last_update_date := SYSDATE;
616 IF (X_MODE = 'I') THEN
617 x_last_updated_by := 1;
618 x_last_update_login := 0;
619 ELSIF (X_MODE IN ('R', 'S')) THEN
620 x_last_updated_by := fnd_global.user_id;
621 IF x_last_updated_by IS NULL THEN
622 x_last_updated_by := -1;
623 END IF;
624 x_last_update_login := fnd_global.login_id;
625 IF (x_last_update_login IS NULL) THEN
626 x_last_update_login := -1;
627 END IF;
628 ELSE
629 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
630 igs_ge_msg_stack.add;
631 app_exception.raise_exception;
632 END IF;
633
634 before_dml(
635 p_action => 'UPDATE',
636 x_rowid => x_rowid,
637 x_athletic_prg_id => x_athletic_prg_id,
638 x_person_id => x_person_id,
639 x_athletic_prg_code => x_athletic_prg_code,
640 x_rating => x_rating,
641 x_start_date => x_start_date,
642 x_end_date => x_end_date,
643 x_recruited_ind => x_recruited_ind,
644 x_participating_ind => x_participating_ind,
645 x_last_update_dt => x_last_update_dt,
646 x_creation_date => x_last_update_date,
647 x_created_by => x_last_updated_by,
648 x_last_update_date => x_last_update_date,
649 x_last_updated_by => x_last_updated_by,
650 x_last_update_login => x_last_update_login
651 );
652
653 IF (x_mode = 'S') THEN
654 igs_sc_gen_001.set_ctx('R');
655 END IF;
656 UPDATE igs_pe_athletic_prg
657 SET
658 person_id = new_references.person_id,
659 athletic_prg_code = new_references.athletic_prg_code,
660 rating = new_references.rating,
661 start_date = new_references.start_date,
662 end_date = new_references.end_date,
663 recruited_ind = new_references.recruited_ind,
664 participating_ind = new_references.participating_ind,
665 last_update_dt = new_references.last_update_dt,
666 last_update_date = x_last_update_date,
667 last_updated_by = x_last_updated_by,
668 last_update_login = x_last_update_login
669 WHERE rowid = x_rowid;
670
671 IF (SQL%NOTFOUND) THEN
672 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
673 igs_ge_msg_stack.add;
674 igs_sc_gen_001.unset_ctx('R');
675 app_exception.raise_exception;
676 END IF;
677 IF (x_mode = 'S') THEN
678 igs_sc_gen_001.unset_ctx('R');
679 END IF;
680
681
682
683 EXCEPTION
684 WHEN OTHERS THEN
685 IF (SQLCODE = (-28115)) THEN
686 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
687 fnd_message.set_token ('ERR_CD', SQLCODE);
688 igs_ge_msg_stack.add;
689 igs_sc_gen_001.unset_ctx('R');
690 app_exception.raise_exception;
691 ELSE
692 igs_sc_gen_001.unset_ctx('R');
693 RAISE;
694 END IF;
695 END update_row;
696
697
698 PROCEDURE add_row (
699 x_rowid IN OUT NOCOPY VARCHAR2,
700 x_athletic_prg_id IN OUT NOCOPY NUMBER,
701 x_person_id IN NUMBER,
702 x_athletic_prg_code IN VARCHAR2,
703 x_rating IN VARCHAR2,
704 x_start_date IN DATE,
705 x_end_date IN DATE,
706 x_recruited_ind IN VARCHAR2,
707 x_participating_ind IN VARCHAR2,
708 x_last_update_dt IN DATE,
709 x_mode IN VARCHAR2
710 ) AS
711 /*
712 || Created By : cdcruz
713 || Created On : 21-SEP-2001
714 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
715 || Known limitations, enhancements or remarks :
716 || Change History :
717 || Who When What
718 || (reverse chronological order - newest change first)
719 */
720 CURSOR c1 IS
721 SELECT rowid
722 FROM igs_pe_athletic_prg
723 WHERE athletic_prg_id = x_athletic_prg_id;
724
725 BEGIN
726
727 OPEN c1;
728 FETCH c1 INTO x_rowid;
729 IF (c1%NOTFOUND) THEN
730 CLOSE c1;
731
732 insert_row (
733 x_rowid,
734 x_athletic_prg_id,
735 x_person_id,
736 x_athletic_prg_code,
737 x_rating,
738 x_start_date,
739 x_end_date,
740 x_recruited_ind,
741 x_participating_ind,
742 x_last_update_dt,
743 x_mode
744 );
745 RETURN;
746 END IF;
747 CLOSE c1;
748
749 update_row (
750 x_rowid,
751 x_athletic_prg_id,
752 x_person_id,
753 x_athletic_prg_code,
754 x_rating,
755 x_start_date,
756 x_end_date,
757 x_recruited_ind,
758 x_participating_ind,
759 x_last_update_dt,
760 x_mode
761 );
762
763 END add_row;
764
765
766 PROCEDURE delete_row (
767 x_rowid IN VARCHAR2,
768 x_mode IN VARCHAR2
769 ) AS
770 /*
771 || Created By : cdcruz
772 || Created On : 21-SEP-2001
773 || Purpose : Handles the DELETE DML logic for the table.
774 || Known limitations, enhancements or remarks :
775 || Change History :
776 || Who When What
777 || (reverse chronological order - newest change first)
778 */
779 BEGIN
780
781 before_dml (
782 p_action => 'DELETE',
783 x_rowid => x_rowid
784 );
785
786 IF (x_mode = 'S') THEN
787 igs_sc_gen_001.set_ctx('R');
788 END IF;
789 DELETE FROM igs_pe_athletic_prg
790 WHERE rowid = x_rowid;
791
792 IF (SQL%NOTFOUND) THEN
793 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
794 igs_ge_msg_stack.add;
795 igs_sc_gen_001.unset_ctx('R');
796 app_exception.raise_exception;
797 END IF;
798 IF (x_mode = 'S') THEN
799 igs_sc_gen_001.unset_ctx('R');
800 END IF;
801
802
803 END delete_row;
804
805
806 END igs_pe_athletic_prg_pkg;
[Home] [Help]