1 PACKAGE BODY igs_ad_hz_extracurr_act_pkg AS
2 /* $Header: IGSAIB9B.pls 120.1 2005/06/28 04:33:16 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ad_hz_extracurr_act%ROWTYPE;
6 new_references igs_ad_hz_extracurr_act%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_hz_extracurr_act_id IN NUMBER DEFAULT NULL,
12 x_person_interest_id IN NUMBER DEFAULT NULL,
13 x_end_date IN DATE DEFAULT NULL,
14 x_hours_per_week IN NUMBER DEFAULT NULL,
15 x_weeks_per_year IN NUMBER DEFAULT NULL,
16 x_activity_source_cd IN VARCHAR2 DEFAULT NULL,
17 x_creation_date IN DATE DEFAULT NULL,
18 x_created_by IN NUMBER DEFAULT NULL,
19 x_last_update_date IN DATE DEFAULT NULL,
20 x_last_updated_by IN NUMBER DEFAULT NULL,
21 x_last_update_login IN NUMBER DEFAULT NULL
22 ) AS
23 /*
24 || Created By : [email protected]
25 || Created On : 29-AUG-2000
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_AD_HZ_EXTRACURR_ACT
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.hz_extracurr_act_id := x_hz_extracurr_act_id;
57 new_references.person_interest_id := x_person_interest_id;
58 new_references.end_date := TRUNC(x_end_date);
59 new_references.hours_per_week := x_hours_per_week;
60 new_references.weeks_per_year := x_weeks_per_year;
61 new_references.activity_source_cd := x_activity_source_cd;
62
63
64 IF (p_action = 'UPDATE') THEN
65 new_references.creation_date := old_references.creation_date;
66 new_references.created_by := old_references.created_by;
67 ELSE
68 new_references.creation_date := x_creation_date;
69 new_references.created_by := x_created_by;
70 END IF;
71
72 new_references.last_update_date := x_last_update_date;
73 new_references.last_updated_by := x_last_updated_by;
74 new_references.last_update_login := x_last_update_login;
75
76 END set_column_values;
77
78 PROCEDURE check_parent_existance AS
79 CURSOR cur_rowid IS
80 SELECT ROWID
81 FROM hz_person_interest
82 WHERE person_interest_id = new_references.person_interest_id ;
83 lv_rowid cur_rowid%ROWTYPE;
84 /*
85 || Created By : [email protected]
86 || Created On : 29-AUG-2000
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.person_interest_id = new_references.person_interest_id)) OR
96 ((new_references.person_interest_id IS NULL))) THEN
97 NULL;
98 ELSE
99 OPEN cur_rowid;
100 FETCH cur_rowid INTO lv_rowid;
101 IF (cur_rowid%NOTFOUND) THEN
102 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
103 IGS_GE_MSG_STACK.ADD;
104 App_Exception.Raise_Exception;
105 END IF;
106 CLOSE cur_rowid;
107
108 END IF;
109
110 IF (((old_references.activity_source_cd = new_references.activity_source_cd)) OR
111 ((new_references.activity_source_cd IS NULL))) THEN
112 NULL;
113 ELSIF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation(
114 'ACTIVITY_SOURCE',
115 new_references.activity_source_cd
116 )THEN
117 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
118 IGS_GE_MSG_STACK.ADD;
119 App_Exception.Raise_Exception;
120 END IF;
121
122 END check_parent_existance;
123
124 FUNCTION get_pk_for_validation (
125 x_hz_extracurr_act_id IN NUMBER
126 ) RETURN BOOLEAN AS
127 /*
128 || Created By : [email protected]
129 || Created On : 29-AUG-2000
130 || Purpose : Validates the Primary Key of the table.
131 || Known limitations, enhancements or remarks :
132 || Change History :
133 || Who When What
134 || (reverse chronological order - newest change first)
135 */
136 CURSOR cur_rowid IS
137 SELECT ROWID
138 FROM igs_ad_hz_extracurr_act
139 WHERE hz_extracurr_act_id = x_hz_extracurr_act_id
140 FOR UPDATE NOWAIT;
141
142 lv_rowid cur_rowid%ROWTYPE;
143
144 BEGIN
145
146 OPEN cur_rowid;
147 FETCH cur_rowid INTO lv_rowid;
148 IF (cur_rowid%FOUND) THEN
149 CLOSE cur_rowid;
150 RETURN(TRUE);
151 ELSE
152 CLOSE cur_rowid;
153 RETURN(FALSE);
154 END IF;
155
156 END get_pk_for_validation;
157
158 PROCEDURE get_fk_hz_person_interest (
159 x_person_interest_id IN NUMBER
160 ) AS
161 /*
162 || Created By : [email protected]
163 || Created On : 29-AUG-2000
164 || Purpose : Validates the Foreign Keys for the table.
165 || Known limitations, enhancements or remarks :
166 || Change History :
167 || Who When What
168 || (reverse chronological order - newest change first)
169 */
170 CURSOR cur_rowid IS
171 SELECT ROWID
172 FROM igs_ad_hz_extracurr_act
173 WHERE ((person_interest_id = x_person_interest_id ));
174
175 lv_rowid cur_rowid%ROWTYPE;
176
177 BEGIN
178
179 OPEN cur_rowid;
180 FETCH cur_rowid INTO lv_rowid;
181 IF (cur_rowid%FOUND) THEN
182 CLOSE cur_rowid;
183 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
184 igs_ge_msg_stack.ADD;
185 app_exception.raise_exception;
186 RETURN;
187 END IF;
188 CLOSE cur_rowid;
189
190 END get_fk_hz_person_interest;
191
192
193 PROCEDURE before_dml (
194 p_action IN VARCHAR2,
195 x_rowid IN VARCHAR2 DEFAULT NULL,
196 x_hz_extracurr_act_id IN NUMBER DEFAULT NULL,
197 x_person_interest_id IN NUMBER DEFAULT NULL,
198 x_end_date IN DATE DEFAULT NULL,
199 x_hours_per_week IN NUMBER DEFAULT NULL,
200 x_weeks_per_year IN NUMBER DEFAULT NULL,
201 x_activity_source_cd IN VARCHAR2 DEFAULT NULL,
202 x_creation_date IN DATE DEFAULT NULL,
203 x_created_by IN NUMBER DEFAULT NULL,
204 x_last_update_date IN DATE DEFAULT NULL,
205 x_last_updated_by IN NUMBER DEFAULT NULL,
206 x_last_update_login IN NUMBER DEFAULT NULL
207 ) AS
208 /*
209 || Created By : [email protected]
210 || Created On : 29-AUG-2000
211 || Purpose : Initialises the columns, Checks Constraints, Calls the
212 || Trigger Handlers for the table, before any DML operation.
213 || Known limitations, enhancements or remarks :
214 || Change History :
215 || Who When What
216 || (reverse chronological order - newest change first)
217 */
218 BEGIN
219
220 set_column_values (
221 p_action,
222 x_rowid,
223 x_hz_extracurr_act_id,
224 x_person_interest_id,
225 x_end_date,
226 x_hours_per_week,
227 x_weeks_per_year,
228 x_activity_source_cd,
229 x_creation_date,
230 x_created_by,
231 x_last_update_date,
232 x_last_updated_by,
233 x_last_update_login
234 );
235
236 IF (p_action = 'INSERT') THEN
237 -- Call all the procedures related to Before Insert.
238 IF ( get_pk_for_validation(
239 new_references.hz_extracurr_act_id
240 )
241 ) THEN
242 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
243 igs_ge_msg_stack.ADD;
244 app_exception.raise_exception;
245 END IF;
246 check_constraints;
247 check_parent_existance;
248 ELSIF (p_action = 'UPDATE') THEN
249 -- Call all the procedures related to Before Update.
250 check_constraints;
251 check_parent_existance;
252 ELSIF (p_action = 'DELETE') THEN
253 NULL;
254 -- Call all the procedures related to Before Delete.
255 -- check_child_existance;
256 ELSIF (p_action = 'VALIDATE_INSERT') THEN
257 -- Call all the procedures related to Before Insert.
258 IF ( get_pk_for_validation (
259 new_references.hz_extracurr_act_id
260 )
261 ) THEN
262 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
263 igs_ge_msg_stack.ADD;
264 app_exception.raise_exception;
265 END IF;
266 check_constraints;
267 END IF;
268
269 END before_dml;
270
271
272 PROCEDURE insert_row (
273 x_rowid IN OUT NOCOPY VARCHAR2,
274 x_hz_extracurr_act_id IN OUT NOCOPY NUMBER,
275 x_person_interest_id IN NUMBER,
276 x_end_date IN DATE,
277 x_hours_per_week IN NUMBER,
278 x_weeks_per_year IN NUMBER,
279 x_activity_source_cd IN VARCHAR2 DEFAULT NULL,
280 x_mode IN VARCHAR2 DEFAULT 'R'
281 ) AS
282 /*
283 || Created By : [email protected]
284 || Created On : 29-AUG-2000
285 || Purpose : Handles the INSERT DML logic for the table.
286 || Known limitations, enhancements or remarks :
287 || Change History :
288 || Who When What
289 || (reverse chronological order - newest change first)
290 */
291 CURSOR c IS
292 SELECT ROWID
293 FROM igs_ad_hz_extracurr_act
294 WHERE hz_extracurr_act_id = x_hz_extracurr_act_id;
295
296 x_last_update_date DATE;
297 x_last_updated_by NUMBER;
298 x_last_update_login NUMBER;
299 x_request_id NUMBER;
300 x_program_id NUMBER;
301 x_program_application_id NUMBER;
302 x_program_update_date DATE;
303
304 BEGIN
305
306 x_last_update_date := SYSDATE;
307 IF (x_mode = 'I') THEN
308 x_last_updated_by := 1;
309 x_last_update_login := 0;
310 ELSIF (X_MODE IN ('R', 'S')) THEN
311 x_last_updated_by := fnd_global.user_id;
312 IF (x_last_updated_by IS NULL) THEN
313 x_last_updated_by := -1;
314 END IF;
315 x_last_update_login := fnd_global.login_id;
316 IF (x_last_update_login IS NULL) THEN
317 x_last_update_login := -1;
318 END IF;
319 x_request_id:=FND_GLOBAL.CONC_REQUEST_ID;
320 x_program_id:=FND_GLOBAL.CONC_PROGRAM_ID;
321 x_program_application_id:=FND_GLOBAL.PROG_APPL_ID;
322 IF (x_request_id = -1 ) THEN
323 x_request_id:=NULL;
324 x_program_id:=NULL;
325 x_program_application_id:=NULL;
326 x_program_update_date:=NULL;
327 ELSE
328 x_program_update_date:=SYSDATE;
329 END IF;
330 ELSE
331 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
332 igs_ge_msg_stack.ADD;
333 app_exception.raise_exception;
334 END IF;
335
336 X_HZ_EXTRACURR_ACT_ID := -1;
337 before_dml(
338 p_action => 'INSERT',
339 x_rowid => x_rowid,
340 x_hz_extracurr_act_id => x_hz_extracurr_act_id,
341 x_person_interest_id => x_person_interest_id,
342 x_end_date => x_end_date,
343 x_hours_per_week => x_hours_per_week,
344 x_weeks_per_year => x_weeks_per_year,
345 x_activity_source_cd => x_activity_source_cd,
346 x_creation_date => x_last_update_date,
347 x_created_by => x_last_updated_by,
348 x_last_update_date => x_last_update_date,
349 x_last_updated_by => x_last_updated_by,
350 x_last_update_login => x_last_update_login
351 );
352
353 IF (x_mode = 'S') THEN
354 igs_sc_gen_001.set_ctx('R');
355 END IF;
356 INSERT INTO igs_ad_hz_extracurr_act (
357 hz_extracurr_act_id,
358 person_interest_id,
359 end_date,
360 hours_per_week,
361 weeks_per_year,
362 activity_source_cd,
363 creation_date,
364 created_by,
365 last_update_date,
366 last_updated_by,
367 last_update_login,
368 request_id,
369 program_application_id,
370 program_update_date,
371 program_id
372 ) VALUES (
373 IGS_AD_HZ_EXTRACURR_ACT_S.NEXTVAL,
374 new_references.person_interest_id,
375 new_references.end_date,
376 new_references.hours_per_week,
377 new_references.weeks_per_year,
378 new_references.activity_source_cd,
379 x_last_update_date,
380 x_last_updated_by,
381 x_last_update_date,
382 x_last_updated_by,
383 x_last_update_login,
384 x_request_id,
385 x_program_application_id,
386 x_program_update_date,
387 x_program_id
388 )RETURNING HZ_EXTRACURR_ACT_ID INTO X_HZ_EXTRACURR_ACT_ID;
389 IF (x_mode = 'S') THEN
390 igs_sc_gen_001.unset_ctx('R');
391 END IF;
392
393
394 OPEN c;
395 FETCH c INTO x_rowid;
396 IF (c%NOTFOUND) THEN
397 CLOSE c;
398 RAISE NO_DATA_FOUND;
399 END IF;
400 CLOSE c;
401
402
403 EXCEPTION
404 WHEN OTHERS THEN
405 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
406 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
407 fnd_message.set_token ('ERR_CD', SQLCODE);
408 igs_ge_msg_stack.add;
409 igs_sc_gen_001.unset_ctx('R');
410 app_exception.raise_exception;
411 ELSE
412 igs_sc_gen_001.unset_ctx('R');
416
413 RAISE;
414 END IF;
415 END insert_row;
417
418 PROCEDURE lock_row (
419 x_rowid IN VARCHAR2,
420 x_hz_extracurr_act_id IN NUMBER,
421 x_person_interest_id IN NUMBER,
422 x_end_date IN DATE,
423 x_hours_per_week IN NUMBER,
424 x_weeks_per_year IN NUMBER,
425 x_activity_source_cd IN VARCHAR2 DEFAULT NULL
426 ) AS
427 /*
428 || Created By : [email protected]
429 || Created On : 29-AUG-2000
430 || Purpose : Handles the LOCK mechanism for the table.
431 || Known limitations, enhancements or remarks :
432 || Change History :
433 || Who When What
434 || (reverse chronological order - newest change first)
435 */
436 CURSOR c1 IS
437 SELECT
438 hz_extracurr_act_id,
439 person_interest_id,
440 end_date,
441 hours_per_week,
442 weeks_per_year,
443 activity_source_cd
444 FROM igs_ad_hz_extracurr_act
445 WHERE ROWID = x_rowid
446 FOR UPDATE NOWAIT;
447
448 tlinfo c1%ROWTYPE;
449
450 BEGIN
451
452 OPEN c1;
453 FETCH c1 INTO tlinfo;
454 IF (c1%NOTFOUND) THEN
455 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
456 igs_ge_msg_stack.ADD;
457 CLOSE c1;
458 app_exception.raise_exception;
459 RETURN;
460 END IF;
461 CLOSE c1;
462
463 IF (
464 (tlinfo.hz_extracurr_act_id = x_hz_extracurr_act_id)
465 AND (tlinfo.person_interest_id = x_person_interest_id)
466 AND ((TRUNC(tlinfo.end_date) = TRUNC(x_end_date)) OR ((tlinfo.end_date IS NULL) AND (X_end_date IS NULL)))
467 AND ((tlinfo.hours_per_week = x_hours_per_week) OR ((tlinfo.hours_per_week IS NULL) AND (X_hours_per_week IS NULL)))
468 AND ((tlinfo.weeks_per_year = x_weeks_per_year) OR ((tlinfo.weeks_per_year IS NULL) AND (X_weeks_per_year IS NULL)))
469 AND ((tlinfo.activity_source_cd = x_activity_source_cd) OR ((tlinfo.activity_source_cd IS NULL) AND (x_activity_source_cd IS NULL)))
470 ) THEN
471 NULL;
472 ELSE
473 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
474 igs_ge_msg_stack.ADD;
475 app_exception.raise_exception;
476 END IF;
477
478 RETURN;
479
480 END lock_row;
481
482
483 PROCEDURE update_row (
484 x_rowid IN VARCHAR2,
485 x_hz_extracurr_act_id IN NUMBER,
486 x_person_interest_id IN NUMBER,
487 x_end_date IN DATE,
488 x_hours_per_week IN NUMBER,
489 x_weeks_per_year IN NUMBER,
490 x_activity_source_cd IN VARCHAR2 DEFAULT NULL,
491 x_mode IN VARCHAR2 DEFAULT 'R'
492 ) AS
493 /*
494 || Created By : [email protected]
495 || Created On : 29-AUG-2000
496 || Purpose : Handles the UPDATE DML logic for the table.
497 || Known limitations, enhancements or remarks :
498 || Change History :
499 || Who When What
500 || (reverse chronological order - newest change first)
501 */
502 x_last_update_date DATE ;
503 x_last_updated_by NUMBER;
504 x_last_update_login NUMBER;
505 x_request_id NUMBER;
506 x_program_id NUMBER;
507 x_program_application_id NUMBER;
508 x_program_update_date DATE;
509
510 BEGIN
511
512 x_last_update_date := SYSDATE;
513 IF (X_MODE = 'I') THEN
514 x_last_updated_by := 1;
515 x_last_update_login := 0;
516 ELSIF (X_MODE IN ('R', 'S')) THEN
517 x_last_updated_by := fnd_global.user_id;
518 IF x_last_updated_by IS NULL THEN
519 x_last_updated_by := -1;
520 END IF;
521 x_last_update_login := fnd_global.login_id;
522 IF (x_last_update_login IS NULL) THEN
523 x_last_update_login := -1;
524 END IF;
525 x_last_update_login := fnd_global.login_id;
526 IF (x_last_update_login IS NULL) THEN
527 x_last_update_login := -1;
528 END IF;
529 x_request_id:=FND_GLOBAL.CONC_REQUEST_ID;
530 x_program_id:=FND_GLOBAL.CONC_PROGRAM_ID;
531 x_program_application_id:=FND_GLOBAL.PROG_APPL_ID;
532 IF (x_request_id = -1 ) THEN
533 x_request_id:=NULL;
534 x_program_id:=NULL;
535 x_program_application_id:=NULL;
536 x_program_update_date:=NULL;
537 ELSE
538 x_program_update_date:=SYSDATE;
539 END IF;
540 ELSE
541 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
542 igs_ge_msg_stack.ADD;
543 app_exception.raise_exception;
544 END IF;
545
546 before_dml(
547 p_action => 'UPDATE',
548 x_rowid => x_rowid,
549 x_hz_extracurr_act_id => x_hz_extracurr_act_id,
553 x_weeks_per_year => x_weeks_per_year,
550 x_person_interest_id => x_person_interest_id,
551 x_end_date => x_end_date,
552 x_hours_per_week => x_hours_per_week,
554 x_activity_source_cd => x_activity_source_cd,
555 x_creation_date => x_last_update_date,
556 x_created_by => x_last_updated_by,
557 x_last_update_date => x_last_update_date,
558 x_last_updated_by => x_last_updated_by,
559 x_last_update_login => x_last_update_login
560 );
561
562 IF (x_mode = 'S') THEN
563 igs_sc_gen_001.set_ctx('R');
564 END IF;
565 UPDATE igs_ad_hz_extracurr_act
566 SET
567 hz_extracurr_act_id = new_references.hz_extracurr_act_id,
568 person_interest_id = new_references.person_interest_id,
569 end_date = new_references.end_date,
570 hours_per_week = new_references.hours_per_week,
571 weeks_per_year = new_references.weeks_per_year,
572 activity_source_cd = new_references.activity_source_cd,
573 last_update_date = x_last_update_date,
574 last_updated_by = x_last_updated_by,
575 last_update_login = x_last_update_login,
576 request_id = x_request_id,
577 program_application_id = x_program_application_id,
578 program_update_date = x_program_update_date,
579 program_id = x_program_id
580 WHERE ROWID = x_rowid;
581
582 IF (SQL%NOTFOUND) THEN
583 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
584 igs_ge_msg_stack.add;
585 igs_sc_gen_001.unset_ctx('R');
586 app_exception.raise_exception;
587 END IF;
588 IF (x_mode = 'S') THEN
589 igs_sc_gen_001.unset_ctx('R');
590 END IF;
591
592
593
594 EXCEPTION
595 WHEN OTHERS THEN
596 IF (SQLCODE = (-28115)) THEN
597 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
598 fnd_message.set_token ('ERR_CD', SQLCODE);
599 igs_ge_msg_stack.add;
600 igs_sc_gen_001.unset_ctx('R');
601 app_exception.raise_exception;
602 ELSE
603 igs_sc_gen_001.unset_ctx('R');
604 RAISE;
605 END IF;
606 END update_row;
607
608
609 PROCEDURE add_row (
610 x_rowid IN OUT NOCOPY VARCHAR2,
611 x_hz_extracurr_act_id IN OUT NOCOPY NUMBER,
612 x_person_interest_id IN NUMBER,
613 x_end_date IN DATE,
614 x_hours_per_week IN NUMBER,
615 x_weeks_per_year IN NUMBER,
616 x_activity_source_cd IN VARCHAR2 DEFAULT NULL,
617 x_mode IN VARCHAR2 DEFAULT 'R'
618 ) AS
619 /*
620 || Created By : [email protected]
621 || Created On : 29-AUG-2000
622 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
623 || Known limitations, enhancements or remarks :
624 || Change History :
625 || Who When What
626 || (reverse chronological order - newest change first)
627 */
628 CURSOR c1 IS
629 SELECT ROWID
630 FROM igs_ad_hz_extracurr_act
631 WHERE hz_extracurr_act_id = x_hz_extracurr_act_id;
632
633 BEGIN
634
635 OPEN c1;
636 FETCH c1 INTO x_rowid;
637 IF (c1%NOTFOUND) THEN
638 CLOSE c1;
639
640 insert_row (
641 x_rowid,
642 x_hz_extracurr_act_id,
643 x_person_interest_id,
644 x_end_date,
645 x_hours_per_week,
646 x_weeks_per_year,
647 x_activity_source_cd ,
648 x_mode
649 );
650 RETURN;
651 END IF;
652 CLOSE c1;
653
654 update_row (
655 x_rowid,
656 x_hz_extracurr_act_id,
657 x_person_interest_id,
658 x_end_date,
659 x_hours_per_week,
660 x_weeks_per_year,
661 x_activity_source_cd,
662 x_mode
663 );
664
665 END add_row;
666
667
668 PROCEDURE delete_row (
669 x_rowid IN VARCHAR2,
670 x_mode IN VARCHAR2
671 ) AS
672 /*
673 || Created By : [email protected]
674 || Created On : 29-AUG-2000
675 || Purpose : Handles the DELETE DML logic for the table.
676 || Known limitations, enhancements or remarks :
677 || Change History :
678 || Who When What
679 || (reverse chronological order - newest change first)
680 */
681 BEGIN
682
683 before_dml (
684 p_action => 'DELETE',
685 x_rowid => x_rowid
686 );
687
688 IF (x_mode = 'S') THEN
689 igs_sc_gen_001.set_ctx('R');
690 END IF;
691 DELETE FROM igs_ad_hz_extracurr_act
692 WHERE ROWID = x_rowid;
693
694 IF (SQL%NOTFOUND) THEN
695 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
696 igs_ge_msg_stack.add;
697 igs_sc_gen_001.unset_ctx('R');
698 app_exception.raise_exception;
699 END IF;
700 IF (x_mode = 'S') THEN
701 igs_sc_gen_001.unset_ctx('R');
702 END IF;
703
704
705 END delete_row;
706
707 PROCEDURE Check_Constraints (
708 Column_Name IN VARCHAR2 DEFAULT NULL,
709 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
710 /*************************************************************
711 Created By :amuthu
712 Date Created By :03-Jul-2001
713 Purpose : This has been added during the ID prospective applicant
714 part 2 of build. This peice of code was missing and has been
715 copied from an older version of the TBH and slighlty modidifed
716 to remove the reference to the item that do not exist in the table
717 anymore (start_date removed but present in HZ_PERSON_INTEREST)
718 The validation for end_date with out NOCOPY the start_date is not possible
719 hence removed that too.
720
721 Know limitations, enhancements or remarks
722 Change History
723 Who When What
724
725 (reverse chronological order - newest change first)
726 ***************************************************************/
727
728 BEGIN
729
730 IF column_name IS NULL THEN
731 NULL;
732 ELSIF UPPER(column_name) = 'HOURS_PER_WEEK' THEN
733 new_references.hours_per_week := IGS_GE_NUMBER.TO_NUM(column_value);
734 ELSIF UPPER(column_name) = 'WEEKS_PER_YEAR' THEN
735 new_references.weeks_per_year := IGS_GE_NUMBER.TO_NUM(column_value);
736 END IF;
737
738
739 -- The following code checks for check constraints on the Columns.
740 IF UPPER(Column_Name) = 'HOURS_PER_WEEK' OR
741 Column_Name IS NULL THEN
742 IF NOT (new_references.hours_per_week >= 0
743 AND new_references.hours_per_week <= 168 ) THEN
744 Fnd_Message.Set_Name('IGS','IGS_AD_HRS_PER_WEEK');
745 IGS_GE_MSG_STACK.ADD;
746 App_Exception.Raise_Exception;
747 END IF;
748 END IF;
749
750 -- The following code checks for check constraints on the Columns.
751 IF UPPER(Column_Name) = 'WEEKS_PER_YEAR' OR
752 Column_Name IS NULL THEN
753 IF NOT (new_references.weeks_per_year >= 0
754 AND new_references.weeks_per_year <= 52 ) THEN
755 Fnd_Message.Set_Name('IGS','IGS_AD_WKS_PER_YEAR');
756 IGS_GE_MSG_STACK.ADD;
757 App_Exception.Raise_Exception;
758 END IF;
759 END IF;
760
761 END Check_Constraints;
762
763
764 END igs_ad_hz_extracurr_act_pkg;
[Home] [Help]