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