[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_SPI_RCONDS_PKG
Source
1 PACKAGE BODY igs_en_spi_rconds_pkg AS
2 /* $Header: IGSEI81B.pls 120.1 2006/04/16 23:48:24 smaddali noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_en_spi_rconds%ROWTYPE;
6 new_references igs_en_spi_rconds%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_person_id IN NUMBER,
12 x_course_cd IN VARCHAR2,
13 x_start_dt IN DATE,
14 x_logical_delete_date IN DATE,
15 x_return_condition IN VARCHAR2,
16 x_status_code IN VARCHAR2,
17 x_approved_dt IN DATE,
18 x_approved_by IN NUMBER,
19 x_creation_date IN DATE,
20 x_created_by IN NUMBER,
21 x_last_update_date IN DATE,
22 x_last_updated_by IN NUMBER,
23 x_last_update_login IN NUMBER
24 ) AS
25 /*
26 || Created By : Susmitha Tutta
27 || Created On : 12-MAR-2006
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_spi_rconds
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.person_id := x_person_id;
59 new_references.course_cd := x_course_cd;
60 new_references.start_dt := x_start_dt;
61 new_references.logical_delete_date := x_logical_delete_date;
62 new_references.return_condition := x_return_condition;
63 new_references.status_code := NVL(x_status_code,'PENDING');
64 new_references.approved_dt := x_approved_dt;
65 new_references.approved_by := x_approved_by;
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_parent_existance AS
83 /*
84 || Created By : Susmitha Tutta
85 || Created On : 12-MAR-2006
86 || Purpose : Checks for the existance of Parent records.
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 (((old_references.return_condition = new_references.return_condition)) OR
95 ((new_references.return_condition IS NULL))) THEN
96 NULL;
97 ELSIF NOT igs_en_intm_rconds_pkg.get_pk_for_validation (
98 new_references.return_condition
99 ) THEN
100 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
101 igs_ge_msg_stack.add;
102 app_exception.raise_exception;
103 END IF;
104
105 IF (
106 (
107 (old_references.person_id = new_references.person_id) AND
108 (old_references.course_cd = new_references.course_cd) AND
109 (old_references.start_dt = new_references.start_dt) AND
110 (old_references.logical_delete_date = new_references.logical_delete_date)
111 ) OR
112 (
113 (new_references.person_id IS NULL) OR
114 (new_references.course_cd IS NULL) OR
115 (new_references.start_dt IS NULL) OR
116 (new_references.logical_delete_date IS NULL)
117 )
118 ) THEN
119 NULL;
120 ELSIF NOT igs_en_stdnt_ps_intm_pkg.get_pk_for_validation (
121 new_references.person_id,
122 new_references.course_cd,
123 new_references.start_dt,
124 new_references.logical_delete_date
125 ) THEN
126 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
127 igs_ge_msg_stack.add;
128 app_exception.raise_exception;
129 END IF;
130
131 END check_parent_existance;
132
133
134 FUNCTION get_pk_for_validation (
135 x_person_id IN NUMBER,
136 x_course_cd IN VARCHAR2,
137 x_start_dt IN DATE,
138 x_logical_delete_date IN DATE,
139 x_return_condition IN VARCHAR2
140 ) RETURN BOOLEAN AS
141 /*
142 || Created By : Susmitha Tutta
143 || Created On : 12-MAR-2006
144 || Purpose : Validates the Primary Key of the table.
145 || Known limitations, enhancements or remarks :
146 || Change History :
147 || Who When What
148 || (reverse chronological order - newest change first)
149 */
150 CURSOR cur_rowid IS
151 SELECT rowid
152 FROM igs_en_spi_rconds
153 WHERE person_id = x_person_id
154 AND course_cd = x_course_cd
155 AND start_dt = x_start_dt
156 AND logical_delete_date = x_logical_delete_date
157 AND return_condition = x_return_condition
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
177 PROCEDURE get_fk_igs_en_stdnt_ps_intm
178 ( x_person_id IN NUMBER,
179 x_course_cd IN VARCHAR2,
180 x_start_dt IN DATE,
181 x_logical_delete_date IN DATE
182 ) AS
183 -- Get the details of
184 CURSOR cur_rowid IS
185 SELECT rowid
186 FROM igs_en_spi_rconds
187 WHERE person_id = x_person_id
188 AND course_cd = x_course_cd
189 AND start_dt = x_start_dt
190 AND logical_delete_date = x_logical_delete_date;
191 lv_rowid ROWID;
192 BEGIN
193 OPEN cur_rowid;
194 FETCH cur_rowid INTO lv_rowid;
195 IF (cur_rowid%FOUND) THEN
196 CLOSE cur_rowid;
197 fnd_message.set_name ('IGS', 'IGS_EN_INTM_CHLD_EXST');
198 igs_ge_msg_stack.add;
199 app_exception.raise_exception;
200 RETURN;
201 END IF;
202 CLOSE cur_rowid;
203 END;
204
205
206 PROCEDURE before_dml (
207 p_action IN VARCHAR2,
208 x_rowid IN VARCHAR2,
209 x_person_id IN NUMBER,
210 x_course_cd IN VARCHAR2,
211 x_start_dt IN DATE,
212 x_logical_delete_date IN DATE,
213 x_return_condition IN VARCHAR2,
214 x_status_code IN VARCHAR2,
215 x_approved_dt IN DATE,
216 x_approved_by IN NUMBER,
217 x_creation_date IN DATE,
218 x_created_by IN NUMBER,
219 x_last_update_date IN DATE,
220 x_last_updated_by IN NUMBER,
221 x_last_update_login IN NUMBER
222 ) AS
223 /*
224 || Created By : Susmitha Tutta
225 || Created On : 12-MAR-2006
226 || Purpose : Initialises the columns, Checks Constraints, Calls the
227 || Trigger Handlers for the table, before any DML operation.
228 || Known limitations, enhancements or remarks :
229 || Change History :
230 || Who When What
231 || (reverse chronological order - newest change first)
232 */
233 BEGIN
234
235 set_column_values (
236 p_action,
237 x_rowid,
238 x_person_id,
239 x_course_cd,
240 x_start_dt,
241 x_logical_delete_date,
242 x_return_condition,
243 x_status_code,
244 x_approved_dt,
245 x_approved_by,
246 x_creation_date,
247 x_created_by,
248 x_last_update_date,
249 x_last_updated_by,
250 x_last_update_login
251 );
252
253 IF (p_action = 'INSERT') THEN
254 -- Call all the procedures related to Before Insert.
255 IF ( get_pk_for_validation(
256 new_references.person_id,
257 new_references.course_cd,
258 new_references.start_dt,
259 new_references.logical_delete_date,
260 new_references.return_condition
261 )
262 ) THEN
263 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
264 igs_ge_msg_stack.add;
265 app_exception.raise_exception;
266 END IF;
267 check_parent_existance;
268 ELSIF (p_action = 'UPDATE') THEN
269 -- Call all the procedures related to Before Update.
270 check_parent_existance;
271 ELSIF (p_action = 'VALIDATE_INSERT') THEN
272 -- Call all the procedures related to Before Insert.
273 IF ( get_pk_for_validation (
274 new_references.person_id,
275 new_references.course_cd,
276 new_references.start_dt,
277 new_references.logical_delete_date,
278 new_references.return_condition
279 )
280 ) THEN
281 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
282 igs_ge_msg_stack.add;
283 app_exception.raise_exception;
284 END IF;
285 END IF;
286
287 END before_dml;
288
289
290 PROCEDURE insert_row (
291 x_rowid IN OUT NOCOPY VARCHAR2,
292 x_person_id IN NUMBER,
293 x_course_cd IN VARCHAR2,
294 x_start_dt IN DATE,
295 x_logical_delete_date IN DATE,
296 x_return_condition IN VARCHAR2,
297 x_status_code IN VARCHAR2,
298 x_approved_dt IN DATE,
299 x_approved_by IN NUMBER,
300 x_mode IN VARCHAR2
301 ) AS
302 /*
303 || Created By : Susmitha Tutta
304 || Created On : 12-MAR-2006
305 || Purpose : Handles the INSERT DML logic for the table.
306 || Known limitations, enhancements or remarks :
307 || Change History :
308 || Who When What
309 || (reverse chronological order - newest change first)
310 */
311
312 x_last_update_date DATE;
313 x_last_updated_by NUMBER;
314 x_last_update_login NUMBER;
315
316 BEGIN
317
318 x_last_update_date := SYSDATE;
319 IF (x_mode = 'I') THEN
320 x_last_updated_by := 1;
321 x_last_update_login := 0;
322 ELSIF (x_mode = 'R') THEN
323 x_last_updated_by := fnd_global.user_id;
324 IF (x_last_updated_by IS NULL) THEN
325 x_last_updated_by := -1;
326 END IF;
327 x_last_update_login := fnd_global.login_id;
328 IF (x_last_update_login IS NULL) THEN
329 x_last_update_login := -1;
330 END IF;
331 ELSE
332 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
333 fnd_message.set_token ('ROUTINE', 'IGS_EN_SPI_RCONDS_PKG.INSERT_ROW');
334 igs_ge_msg_stack.add;
335 app_exception.raise_exception;
336 END IF;
337
338
339 before_dml(
340 p_action => 'INSERT',
341 x_rowid => x_rowid,
342 x_person_id => x_person_id,
343 x_course_cd => x_course_cd,
344 x_start_dt => x_start_dt,
345 x_logical_delete_date => x_logical_delete_date,
346 x_return_condition => x_return_condition,
347 x_status_code => x_status_code,
348 x_approved_dt => x_approved_dt,
349 x_approved_by => x_approved_by,
350 x_creation_date => x_last_update_date,
351 x_created_by => x_last_updated_by,
352 x_last_update_date => x_last_update_date,
353 x_last_updated_by => x_last_updated_by,
354 x_last_update_login => x_last_update_login
355 );
356
357 INSERT INTO igs_en_spi_rconds (
358 person_id,
359 course_cd,
360 start_dt,
361 logical_delete_date,
362 return_condition,
363 status_code,
364 approved_dt,
365 approved_by,
366 creation_date,
367 created_by,
368 last_update_date,
369 last_updated_by,
370 last_update_login
371 ) VALUES (
372 new_references.person_id,
373 new_references.course_cd,
374 new_references.start_dt,
375 new_references.logical_delete_date,
376 new_references.return_condition,
377 new_references.status_code,
378 new_references.approved_dt,
379 new_references.approved_by,
380 x_last_update_date,
381 x_last_updated_by,
382 x_last_update_date,
383 x_last_updated_by,
384 x_last_update_login
385 ) RETURNING ROWID INTO x_rowid;
386
387 END insert_row;
388
389
390 PROCEDURE lock_row (
391 x_rowid IN VARCHAR2,
392 x_person_id IN NUMBER,
393 x_course_cd IN VARCHAR2,
394 x_start_dt IN DATE,
395 x_logical_delete_date IN DATE,
396 x_return_condition IN VARCHAR2,
397 x_status_code IN VARCHAR2,
398 x_approved_dt IN DATE,
399 x_approved_by IN NUMBER
400 ) AS
401 /*
402 || Created By : Susmitha Tutta
403 || Created On : 12-MAR-2006
404 || Purpose : Handles the LOCK mechanism for the table.
405 || Known limitations, enhancements or remarks :
406 || Change History :
407 || Who When What
408 || (reverse chronological order - newest change first)
409 */
410 CURSOR c1 IS
411 SELECT
412 status_code,
413 approved_dt,
414 approved_by
415 FROM igs_en_spi_rconds
416 WHERE rowid = x_rowid
417 FOR UPDATE NOWAIT;
418
419 tlinfo c1%ROWTYPE;
420
421 BEGIN
422
423 OPEN c1;
424 FETCH c1 INTO tlinfo;
425 IF (c1%notfound) THEN
426 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
427 igs_ge_msg_stack.add;
428 CLOSE c1;
429 app_exception.raise_exception;
430 RETURN;
431 END IF;
432 CLOSE c1;
433
434 IF (
435 ((tlinfo.status_code = x_status_code) OR ((tlinfo.status_code IS NULL) AND (X_status_code IS NULL)))
436 AND ((tlinfo.approved_dt = x_approved_dt) OR ((tlinfo.approved_dt IS NULL) AND (X_approved_dt IS NULL)))
437 AND ((tlinfo.approved_by = x_approved_by) OR ((tlinfo.approved_by IS NULL) AND (X_approved_by IS NULL)))
438 ) THEN
439 NULL;
440 ELSE
441 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
442 igs_ge_msg_stack.add;
443 app_exception.raise_exception;
444 END IF;
445
446 RETURN;
447
448 END lock_row;
452 x_rowid IN VARCHAR2,
449
450
451 PROCEDURE update_row (
453 x_person_id IN NUMBER,
454 x_course_cd IN VARCHAR2,
455 x_start_dt IN DATE,
456 x_logical_delete_date IN DATE,
457 x_return_condition IN VARCHAR2,
458 x_status_code IN VARCHAR2,
459 x_approved_dt IN DATE,
460 x_approved_by IN NUMBER,
461 x_mode IN VARCHAR2
462 ) AS
463 /*
464 || Created By : Susmitha Tutta
465 || Created On : 12-MAR-2006
466 || Purpose : Handles the UPDATE DML logic for the table.
467 || Known limitations, enhancements or remarks :
468 || Change History :
469 || Who When What
470 || (reverse chronological order - newest change first)
471 */
472 x_last_update_date DATE ;
473 x_last_updated_by NUMBER;
474 x_last_update_login NUMBER;
475
476 BEGIN
477
478 x_last_update_date := SYSDATE;
479 IF (X_MODE = 'I') THEN
480 x_last_updated_by := 1;
481 x_last_update_login := 0;
482 ELSIF (x_mode = 'R') THEN
483 x_last_updated_by := fnd_global.user_id;
484 IF x_last_updated_by IS NULL THEN
485 x_last_updated_by := -1;
486 END IF;
487 x_last_update_login := fnd_global.login_id;
488 IF (x_last_update_login IS NULL) THEN
489 x_last_update_login := -1;
490 END IF;
491 ELSE
492 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
493 fnd_message.set_token ('ROUTINE', 'IGS_EN_SPI_RCONDS_PKG.UPDATE_ROW');
494 igs_ge_msg_stack.add;
495 app_exception.raise_exception;
496 END IF;
497
498 before_dml(
499 p_action => 'UPDATE',
500 x_rowid => x_rowid,
501 x_person_id => x_person_id,
502 x_course_cd => x_course_cd,
503 x_start_dt => x_start_dt,
504 x_logical_delete_date => x_logical_delete_date,
505 x_return_condition => x_return_condition,
506 x_status_code => x_status_code,
507 x_approved_dt => x_approved_dt,
508 x_approved_by => x_approved_by,
509 x_creation_date => x_last_update_date,
510 x_created_by => x_last_updated_by,
511 x_last_update_date => x_last_update_date,
512 x_last_updated_by => x_last_updated_by,
513 x_last_update_login => x_last_update_login
514 );
515
516 UPDATE igs_en_spi_rconds
517 SET
518 logical_delete_date = new_references.logical_delete_date,
519 status_code = new_references.status_code,
520 approved_dt = new_references.approved_dt,
521 approved_by = new_references.approved_by,
522 last_update_date = x_last_update_date,
523 last_updated_by = x_last_updated_by,
524 last_update_login = x_last_update_login
525 WHERE rowid = x_rowid;
526
527 IF (SQL%NOTFOUND) THEN
528 RAISE NO_DATA_FOUND;
529 END IF;
530
531 END update_row;
532
533
534 PROCEDURE add_row (
535 x_rowid IN OUT NOCOPY VARCHAR2,
536 x_person_id IN NUMBER,
537 x_course_cd IN VARCHAR2,
538 x_start_dt IN DATE,
539 x_logical_delete_date IN DATE,
540 x_return_condition IN VARCHAR2,
541 x_status_code IN VARCHAR2,
542 x_approved_dt IN DATE,
543 x_approved_by IN NUMBER,
544 x_mode IN VARCHAR2
545 ) AS
546 /*
547 || Created By : Susmitha Tutta
548 || Created On : 12-MAR-2006
549 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
550 || Known limitations, enhancements or remarks :
551 || Change History :
552 || Who When What
553 || (reverse chronological order - newest change first)
554 */
555 CURSOR c1 IS
556 SELECT rowid
557 FROM igs_en_spi_rconds
558 WHERE person_id = x_person_id
559 AND course_cd = x_course_cd
560 AND start_dt = x_start_dt
561 AND logical_delete_date = x_logical_delete_date
562 AND return_condition = x_return_condition;
563
564 BEGIN
565
566 OPEN c1;
567 FETCH c1 INTO x_rowid;
568 IF (c1%NOTFOUND) THEN
569 CLOSE c1;
570
571 insert_row (
572 x_rowid,
573 x_person_id,
574 x_course_cd,
575 x_start_dt,
576 x_logical_delete_date,
577 x_return_condition,
578 x_status_code,
579 x_approved_dt,
580 x_approved_by,
581 x_mode
582 );
583 RETURN;
584 END IF;
585 CLOSE c1;
586
587 update_row (
588 x_rowid,
589 x_person_id,
590 x_course_cd,
591 x_start_dt,
592 x_logical_delete_date,
593 x_return_condition,
594 x_status_code,
595 x_approved_dt,
596 x_approved_by,
597 x_mode
598 );
599
600 END add_row;
601
602
603 PROCEDURE delete_row (
604 x_rowid IN VARCHAR2
605 ) AS
606 /*
607 || Created By : Susmitha Tutta
608 || Created On : 12-MAR-2006
609 || Purpose : Handles the DELETE DML logic for the table.
610 || Known limitations, enhancements or remarks :
611 || Change History :
612 || Who When What
613 || (reverse chronological order - newest change first)
614 */
615 BEGIN
616
617 before_dml (
618 p_action => 'DELETE',
619 x_rowid => x_rowid
620 );
621
622 DELETE FROM igs_en_spi_rconds
623 WHERE rowid = x_rowid;
624
625 IF (SQL%NOTFOUND) THEN
626 RAISE NO_DATA_FOUND;
627 END IF;
628
629 END delete_row;
630
631
632 END igs_en_spi_rconds_pkg;