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