[Home] [Help]
PACKAGE BODY: APPS.IGS_PR_COHORT_INST_PKG
Source
1 PACKAGE BODY igs_pr_cohort_inst_pkg AS
2 /* $Header: IGSQI42B.pls 115.5 2002/11/29 03:25:42 nsidana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pr_cohort_inst%ROWTYPE;
6 new_references igs_pr_cohort_inst%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_cohort_name IN VARCHAR2,
12 x_load_cal_type IN VARCHAR2,
13 x_load_ci_sequence_number IN NUMBER,
14 x_cohort_status IN VARCHAR2,
15 x_rank_status IN VARCHAR2,
16 x_run_date IN DATE,
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 : [email protected]
25 || Created On : 30-OCT-2002
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_pr_cohort_inst
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.cohort_name := x_cohort_name;
57 new_references.load_cal_type := x_load_cal_type;
58 new_references.load_ci_sequence_number := x_load_ci_sequence_number;
59 new_references.cohort_status := x_cohort_status;
60 new_references.rank_status := x_rank_status;
61 new_references.run_date := x_run_date;
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 PROCEDURE check_parent_existance AS
78 /*
79 || Created By : [email protected]
80 || Created On : 30-OCT-2002
81 || Purpose : Checks for the existance of Parent records.
82 || Known limitations, enhancements or remarks :
83 || Change History :
84 || Who When What
85 || (reverse chronological order - newest change first)
86 */
87 BEGIN
88
89 IF (((old_references.cohort_name = new_references.cohort_name)) OR
90 ((new_references.cohort_name IS NULL))) THEN
91 NULL;
92 ELSIF NOT igs_pr_cohort_pkg.get_pk_for_validation (
93 new_references.cohort_name
94 ) THEN
95 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
96 igs_ge_msg_stack.add;
97 app_exception.raise_exception;
98 END IF;
99
100 IF (((old_references.load_cal_type = new_references.load_cal_type) AND
101 (old_references.load_ci_sequence_number = new_references.load_ci_sequence_number)) OR
102 ((new_references.load_cal_type IS NULL) OR
103 (new_references.load_ci_sequence_number IS NULL))) THEN
104 NULL;
105 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
106 new_references.load_cal_type,
107 new_references.load_ci_sequence_number
108 ) THEN
109 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
110 igs_ge_msg_stack.add;
111 app_exception.raise_exception;
112 END IF;
113
114 END check_parent_existance;
115
116
117 PROCEDURE check_child_existance AS
118 /*
119 || Created By : [email protected]
120 || Created On : 30-OCT-2002
121 || Purpose : Checks for the existance of Child records.
122 || Known limitations, enhancements or remarks :
123 || Change History :
124 || Who When What
125 || (reverse chronological order - newest change first)
126 */
127 BEGIN
128
129 igs_pr_cohinst_rank_pkg.get_fk_igs_pr_cohort_inst (
130 old_references.cohort_name,
131 old_references.load_cal_type,
132 old_references.load_ci_sequence_number
133 );
134
135 END check_child_existance;
136
137
138 FUNCTION get_pk_for_validation (
139 x_cohort_name IN VARCHAR2,
140 x_load_cal_type IN VARCHAR2,
141 x_load_ci_sequence_number IN NUMBER
142 ) RETURN BOOLEAN AS
143 /*
144 || Created By : [email protected]
145 || Created On : 30-OCT-2002
146 || Purpose : Validates the Primary Key of the table.
147 || Known limitations, enhancements or remarks :
148 || Change History :
149 || Who When What
150 || (reverse chronological order - newest change first)
151 */
152 CURSOR cur_rowid IS
153 SELECT rowid
154 FROM igs_pr_cohort_inst
155 WHERE cohort_name = x_cohort_name
156 AND load_cal_type = x_load_cal_type
157 AND load_ci_sequence_number = x_load_ci_sequence_number
158 FOR UPDATE NOWAIT;
159
160 lv_rowid cur_rowid%RowType;
161
162 BEGIN
163
164 OPEN cur_rowid;
165 FETCH cur_rowid INTO lv_rowid;
166 IF (cur_rowid%FOUND) THEN
167 CLOSE cur_rowid;
168 RETURN(TRUE);
169 ELSE
170 CLOSE cur_rowid;
171 RETURN(FALSE);
172 END IF;
173
174 END get_pk_for_validation;
175
176 PROCEDURE get_fk_igs_pr_cohort (
177 x_cohort_name IN VARCHAR2
178 ) AS
179 /*
180 || Created By : [email protected]
181 || Created On : 30-OCT-2002
182 || Purpose : Validates the Foreign Keys for the table.
183 || Known limitations, enhancements or remarks :
184 || Change History :
185 || Who When What
186 || (reverse chronological order - newest change first)
187 */
188 CURSOR cur_rowid IS
189 SELECT rowid
190 FROM igs_pr_cohort_inst
191 WHERE ((cohort_name = x_cohort_name));
192
193 lv_rowid cur_rowid%RowType;
194
195 BEGIN
196
197 OPEN cur_rowid;
198 FETCH cur_rowid INTO lv_rowid;
199 IF (cur_rowid%FOUND) THEN
200 CLOSE cur_rowid;
201 fnd_message.set_name ('IGS', 'IGS_PR_COH_COHI_FK');
202 igs_ge_msg_stack.add;
203 app_exception.raise_exception;
204 RETURN;
205 END IF;
206 CLOSE cur_rowid;
207
208 END get_fk_igs_pr_cohort;
209
210
211 PROCEDURE get_fk_igs_ca_inst (
212 x_cal_type IN VARCHAR2,
213 x_sequence_number IN NUMBER
214 ) AS
215 /*
216 || Created By : [email protected]
217 || Created On : 30-OCT-2002
218 || Purpose : Validates the Foreign Keys for the table.
219 || Known limitations, enhancements or remarks :
220 || Change History :
221 || Who When What
222 || (reverse chronological order - newest change first)
223 */
224 CURSOR cur_rowid IS
225 SELECT rowid
226 FROM igs_pr_cohort_inst
227 WHERE ((load_cal_type = x_cal_type) AND
228 (load_ci_sequence_number = x_sequence_number));
229
230 lv_rowid cur_rowid%RowType;
231
232 BEGIN
233
234 OPEN cur_rowid;
235 FETCH cur_rowid INTO lv_rowid;
236 IF (cur_rowid%FOUND) THEN
237 CLOSE cur_rowid;
238 fnd_message.set_name ('IGS', 'IGS_PR_COHI_CI_FK');
239 igs_ge_msg_stack.add;
240 app_exception.raise_exception;
241 RETURN;
242 END IF;
243 CLOSE cur_rowid;
244
245 END get_fk_igs_ca_inst;
246
247
248 PROCEDURE before_dml (
249 p_action IN VARCHAR2,
250 x_rowid IN VARCHAR2,
251 x_cohort_name IN VARCHAR2,
252 x_load_cal_type IN VARCHAR2,
253 x_load_ci_sequence_number IN NUMBER,
254 x_cohort_status IN VARCHAR2,
255 x_rank_status IN VARCHAR2,
256 x_run_date IN DATE,
257 x_creation_date IN DATE,
258 x_created_by IN NUMBER,
259 x_last_update_date IN DATE,
260 x_last_updated_by IN NUMBER,
261 x_last_update_login IN NUMBER
262 ) AS
263 /*
264 || Created By : [email protected]
265 || Created On : 30-OCT-2002
266 || Purpose : Initialises the columns, Checks Constraints, Calls the
267 || Trigger Handlers for the table, before any DML operation.
268 || Known limitations, enhancements or remarks :
269 || Change History :
270 || Who When What
271 || (reverse chronological order - newest change first)
272 */
273 BEGIN
274
275 set_column_values (
276 p_action,
277 x_rowid,
278 x_cohort_name,
279 x_load_cal_type,
280 x_load_ci_sequence_number,
281 x_cohort_status,
282 x_rank_status,
283 x_run_date,
284 x_creation_date,
285 x_created_by,
286 x_last_update_date,
287 x_last_updated_by,
288 x_last_update_login
289 );
290
291 IF (p_action = 'INSERT') THEN
292 -- Call all the procedures related to Before Insert.
293 IF ( get_pk_for_validation(
294 new_references.cohort_name,
295 new_references.load_cal_type,
296 new_references.load_ci_sequence_number
297 )
298 ) THEN
299 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
300 igs_ge_msg_stack.add;
301 app_exception.raise_exception;
302 END IF;
303 check_parent_existance;
304 ELSIF (p_action = 'UPDATE') THEN
305 -- Call all the procedures related to Before Update.
306 check_parent_existance;
307 ELSIF (p_action = 'DELETE') THEN
308 -- Call all the procedures related to Before Delete.
309 check_child_existance;
310 ELSIF (p_action = 'VALIDATE_INSERT') THEN
311 -- Call all the procedures related to Before Insert.
312 IF ( get_pk_for_validation (
313 new_references.cohort_name,
314 new_references.load_cal_type,
315 new_references.load_ci_sequence_number
316 )
317 ) THEN
318 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
319 igs_ge_msg_stack.add;
320 app_exception.raise_exception;
321 END IF;
322 ELSIF (p_action = 'VALIDATE_DELETE') THEN
323 check_child_existance;
324 END IF;
325
326 END before_dml;
327
328
329 PROCEDURE insert_row (
330 x_rowid IN OUT NOCOPY VARCHAR2,
331 x_cohort_name IN VARCHAR2,
332 x_load_cal_type IN VARCHAR2,
333 x_load_ci_sequence_number IN NUMBER,
334 x_cohort_status IN VARCHAR2,
335 x_rank_status IN VARCHAR2,
336 x_run_date IN DATE,
337 x_mode IN VARCHAR2
338 ) AS
339 /*
340 || Created By : [email protected]
341 || Created On : 30-OCT-2002
342 || Purpose : Handles the INSERT DML logic for the table.
343 || Known limitations, enhancements or remarks :
344 || Change History :
345 || Who When What
346 || (reverse chronological order - newest change first)
347 */
348
349 x_last_update_date DATE;
350 x_last_updated_by NUMBER;
351 x_last_update_login NUMBER;
352 x_request_id NUMBER;
353 x_program_id NUMBER;
354 x_program_application_id NUMBER;
355 x_program_update_date DATE;
356
357 BEGIN
358
359 x_last_update_date := SYSDATE;
360 IF (x_mode = 'I') THEN
361 x_last_updated_by := 1;
362 x_last_update_login := 0;
363 ELSIF (x_mode = 'R') THEN
364 x_last_updated_by := fnd_global.user_id;
365 IF (x_last_updated_by IS NULL) THEN
366 x_last_updated_by := -1;
367 END IF;
368 x_last_update_login := fnd_global.login_id;
369 IF (x_last_update_login IS NULL) THEN
370 x_last_update_login := -1;
371 END IF;
372 x_request_id := fnd_global.conc_request_id;
373 x_program_id := fnd_global.conc_program_id;
374 x_program_application_id := fnd_global.prog_appl_id;
375
376 IF (x_request_id = -1) THEN
377 x_request_id := NULL;
378 x_program_id := NULL;
379 x_program_application_id := NULL;
380 x_program_update_date := NULL;
381 ELSE
382 x_program_update_date := SYSDATE;
383 END IF;
384 ELSE
385 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
386 igs_ge_msg_stack.add;
387 app_exception.raise_exception;
388 END IF;
389
390 before_dml(
391 p_action => 'INSERT',
392 x_rowid => x_rowid,
393 x_cohort_name => x_cohort_name,
394 x_load_cal_type => x_load_cal_type,
395 x_load_ci_sequence_number => x_load_ci_sequence_number,
396 x_cohort_status => x_cohort_status,
397 x_rank_status => x_rank_status,
398 x_run_date => x_run_date,
399 x_creation_date => x_last_update_date,
400 x_created_by => x_last_updated_by,
401 x_last_update_date => x_last_update_date,
402 x_last_updated_by => x_last_updated_by,
403 x_last_update_login => x_last_update_login
404 );
405
406 INSERT INTO igs_pr_cohort_inst (
407 cohort_name,
408 load_cal_type,
409 load_ci_sequence_number,
410 cohort_status,
411 rank_status,
412 run_date,
413 creation_date,
414 created_by,
415 last_update_date,
416 last_updated_by,
417 last_update_login,
418 request_id,
419 program_id,
420 program_application_id,
421 program_update_date
422 ) VALUES (
423 new_references.cohort_name,
424 new_references.load_cal_type,
425 new_references.load_ci_sequence_number,
426 new_references.cohort_status,
427 new_references.rank_status,
428 new_references.run_date,
429 x_last_update_date,
430 x_last_updated_by,
431 x_last_update_date,
432 x_last_updated_by,
433 x_last_update_login ,
434 x_request_id,
435 x_program_id,
436 x_program_application_id,
437 x_program_update_date
438 ) RETURNING ROWID INTO x_rowid;
439
440 END insert_row;
441
442
443 PROCEDURE lock_row (
444 x_rowid IN VARCHAR2,
445 x_cohort_name IN VARCHAR2,
446 x_load_cal_type IN VARCHAR2,
447 x_load_ci_sequence_number IN NUMBER,
448 x_cohort_status IN VARCHAR2,
449 x_rank_status IN VARCHAR2,
450 x_run_date IN DATE
451 ) AS
452 /*
453 || Created By : [email protected]
454 || Created On : 30-OCT-2002
455 || Purpose : Handles the LOCK mechanism for the table.
456 || Known limitations, enhancements or remarks :
457 || Change History :
458 || Who When What
459 || (reverse chronological order - newest change first)
460 */
461 CURSOR c1 IS
462 SELECT
463 cohort_status,
464 rank_status,
465 run_date
466 FROM igs_pr_cohort_inst
467 WHERE rowid = x_rowid
468 FOR UPDATE NOWAIT;
469
470 tlinfo c1%ROWTYPE;
471
472 BEGIN
473
474 OPEN c1;
475 FETCH c1 INTO tlinfo;
476 IF (c1%notfound) THEN
477 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
478 igs_ge_msg_stack.add;
479 CLOSE c1;
480 app_exception.raise_exception;
481 RETURN;
482 END IF;
483 CLOSE c1;
484
485 IF (
486 (tlinfo.cohort_status = x_cohort_status)
487 AND (tlinfo.rank_status = x_rank_status)
488 AND (TRUNC(tlinfo.run_date) = TRUNC(x_run_date) OR (tlinfo.run_date IS NULL AND x_run_date IS NULL))
489 ) THEN
490 NULL;
491 ELSE
492 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
493 igs_ge_msg_stack.add;
494 app_exception.raise_exception;
495 END IF;
496
497 RETURN;
498
499 END lock_row;
500
501
502 PROCEDURE update_row (
503 x_rowid IN VARCHAR2,
504 x_cohort_name IN VARCHAR2,
505 x_load_cal_type IN VARCHAR2,
506 x_load_ci_sequence_number IN NUMBER,
507 x_cohort_status IN VARCHAR2,
508 x_rank_status IN VARCHAR2,
509 x_run_date IN DATE,
510 x_mode IN VARCHAR2
511 ) AS
512 /*
513 || Created By : [email protected]
514 || Created On : 30-OCT-2002
515 || Purpose : Handles the UPDATE DML logic for the table.
516 || Known limitations, enhancements or remarks :
517 || Change History :
518 || Who When What
519 || (reverse chronological order - newest change first)
520 */
521 x_last_update_date DATE ;
522 x_last_updated_by NUMBER;
523 x_last_update_login NUMBER;
524 x_request_id NUMBER;
525 x_program_id NUMBER;
526 x_program_application_id NUMBER;
527 x_program_update_date DATE;
528
529 BEGIN
530
531 x_last_update_date := SYSDATE;
532 IF (X_MODE = 'I') THEN
533 x_last_updated_by := 1;
534 x_last_update_login := 0;
535 ELSIF (x_mode = 'R') THEN
536 x_last_updated_by := fnd_global.user_id;
537 IF x_last_updated_by IS NULL THEN
538 x_last_updated_by := -1;
539 END IF;
540 x_last_update_login := fnd_global.login_id;
541 IF (x_last_update_login IS NULL) THEN
542 x_last_update_login := -1;
543 END IF;
544 ELSE
545 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
546 igs_ge_msg_stack.add;
547 app_exception.raise_exception;
548 END IF;
549
550 before_dml(
551 p_action => 'UPDATE',
552 x_rowid => x_rowid,
553 x_cohort_name => x_cohort_name,
554 x_load_cal_type => x_load_cal_type,
555 x_load_ci_sequence_number => x_load_ci_sequence_number,
556 x_cohort_status => x_cohort_status,
557 x_rank_status => x_rank_status,
558 x_run_date => x_run_date,
559 x_creation_date => x_last_update_date,
560 x_created_by => x_last_updated_by,
561 x_last_update_date => x_last_update_date,
562 x_last_updated_by => x_last_updated_by,
563 x_last_update_login => x_last_update_login
564 );
565
566 IF (x_mode = 'R') THEN
567 x_request_id := fnd_global.conc_request_id;
568 x_program_id := fnd_global.conc_program_id;
569 x_program_application_id := fnd_global.prog_appl_id;
570 IF (x_request_id = -1) THEN
571 x_request_id := old_references.request_id;
572 x_program_id := old_references.program_id;
573 x_program_application_id := old_references.program_application_id;
574 x_program_update_date := old_references.program_update_date;
575 ELSE
576 x_program_update_date := SYSDATE;
577 END IF;
578 END IF;
579
580 UPDATE igs_pr_cohort_inst
581 SET
582 cohort_status = new_references.cohort_status,
583 rank_status = new_references.rank_status,
584 run_date = new_references.run_date,
585 last_update_date = x_last_update_date,
586 last_updated_by = x_last_updated_by,
587 last_update_login = x_last_update_login ,
588 request_id = x_request_id,
589 program_id = x_program_id,
590 program_application_id = x_program_application_id,
591 program_update_date = x_program_update_date
592 WHERE rowid = x_rowid;
593
594 IF (SQL%NOTFOUND) THEN
595 RAISE NO_DATA_FOUND;
596 END IF;
597 -- raise business event in case of change in cohort/rank status
598 IF new_references.cohort_status <> old_references.cohort_status OR
599 new_references.rank_status <> old_references.rank_status THEN
600 -- raise the business event for change in status of cohort instnace
601 IGS_PR_CLASS_RANK.RAISE_CLSRANK_BE_CR001 (
602 P_COHORT_NAME => new_references.cohort_name,
603 P_COHORT_INSTANCE => rpad(new_references.load_cal_type,10)||
604 rpad(new_references.load_ci_sequence_number,6),
605 P_NEW_COHORT_STATUS => new_references.cohort_status,
606 P_NEW_RANK_STATUS => new_references.rank_status );
607 END IF;
608
609 END update_row;
610
611
612 PROCEDURE add_row (
613 x_rowid IN OUT NOCOPY VARCHAR2,
614 x_cohort_name IN VARCHAR2,
615 x_load_cal_type IN VARCHAR2,
616 x_load_ci_sequence_number IN NUMBER,
617 x_cohort_status IN VARCHAR2,
618 x_rank_status IN VARCHAR2,
619 x_run_date IN DATE,
620 x_mode IN VARCHAR2
621 ) AS
622 /*
623 || Created By : [email protected]
624 || Created On : 30-OCT-2002
625 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
626 || Known limitations, enhancements or remarks :
627 || Change History :
628 || Who When What
629 || (reverse chronological order - newest change first)
630 */
631 CURSOR c1 IS
632 SELECT rowid
633 FROM igs_pr_cohort_inst
634 WHERE cohort_name = x_cohort_name
635 AND load_cal_type = x_load_cal_type
636 AND load_ci_sequence_number = x_load_ci_sequence_number;
637
638 BEGIN
639
640 OPEN c1;
641 FETCH c1 INTO x_rowid;
642 IF (c1%NOTFOUND) THEN
643 CLOSE c1;
644
645 insert_row (
646 x_rowid,
647 x_cohort_name,
648 x_load_cal_type,
649 x_load_ci_sequence_number,
650 x_cohort_status,
651 x_rank_status,
652 x_run_date,
653 x_mode
654 );
655 RETURN;
656 END IF;
657 CLOSE c1;
658
659 update_row (
660 x_rowid,
661 x_cohort_name,
662 x_load_cal_type,
663 x_load_ci_sequence_number,
664 x_cohort_status,
665 x_rank_status,
666 x_run_date,
667 x_mode
668 );
669
670 END add_row;
671
672
673 PROCEDURE delete_row (
674 x_rowid IN VARCHAR2
675 ) AS
676 /*
677 || Created By : [email protected]
678 || Created On : 30-OCT-2002
679 || Purpose : Handles the DELETE DML logic for the table.
680 || Known limitations, enhancements or remarks :
681 || Change History :
682 || Who When What
683 || (reverse chronological order - newest change first)
684 */
685 BEGIN
686
687 before_dml (
688 p_action => 'DELETE',
689 x_rowid => x_rowid
690 );
691
692 DELETE FROM igs_pr_cohort_inst
693 WHERE rowid = x_rowid;
694
695 IF (SQL%NOTFOUND) THEN
696 RAISE NO_DATA_FOUND;
697 END IF;
698
699 END delete_row;
700
701
702 END igs_pr_cohort_inst_pkg;