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