1 PACKAGE BODY Igs_Co_Interac_Hist_Pkg AS
2 /* $Header: IGSLI28B.pls 120.0 2005/06/01 19:36:59 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_co_interac_hist%ROWTYPE;
5 new_references igs_co_interac_hist%ROWTYPE;
6
7 PROCEDURE set_column_values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,
10 x_student_id IN NUMBER ,
11 x_request_id IN NUMBER ,
12 x_document_id IN NUMBER ,
13 x_document_type IN VARCHAR2 ,
14 x_sys_ltr_code IN VARCHAR2 ,
15 x_adm_application_number IN NUMBER ,
16 x_nominated_course_cd IN VARCHAR2 ,
17 x_sequence_number IN NUMBER ,
18 x_cal_type IN VARCHAR2 ,
19 x_ci_sequence_number IN NUMBER ,
20 x_requested_date IN DATE ,
21 x_delivery_type IN VARCHAR2 ,
22 x_creation_date IN DATE ,
23 x_created_by IN NUMBER ,
24 x_last_update_date IN DATE ,
25 x_last_updated_by IN NUMBER ,
26 x_last_update_login IN NUMBER ,
27 x_version_id IN NUMBER
28 ) AS
29 /*
30 || Created By : svenkata
31 || Created On : 08-FEB-2002
32 || Purpose : Initialises the Old and New references for the columns of the table.
33 || Known limitations, enhancements or remarks :
34 || Change History :
35 || Who When What
36 || (reverse chronological order - newest change first)
37 */
38 CURSOR cur_old_ref_values IS
39 SELECT *
40 FROM igs_co_interac_hist
41 WHERE ROWID = x_rowid;
42 BEGIN
43 l_rowid := x_rowid;
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 -- Populate New Values.
57 new_references.student_id := x_student_id;
58 new_references.request_id := x_request_id;
59 new_references.document_id := x_document_id;
60 new_references.document_type := x_document_type;
61 new_references.sys_ltr_code := x_sys_ltr_code;
62 new_references.adm_application_number := x_adm_application_number;
63 new_references.nominated_course_cd := x_nominated_course_cd;
64 new_references.sequence_number := x_sequence_number;
65 new_references.cal_type := x_cal_type;
66 new_references.ci_sequence_number := x_ci_sequence_number;
67 new_references.requested_date := trunc(x_requested_date);
68 new_references.delivery_type := x_delivery_type;
69 new_references.version_id := x_version_id;
70 IF (p_action = 'UPDATE') THEN
71 new_references.creation_date := old_references.creation_date;
72 new_references.created_by := old_references.created_by;
73 ELSE
74 new_references.creation_date := x_creation_date;
75 new_references.created_by := x_created_by;
76 END IF;
77 new_references.last_update_date := x_last_update_date;
78 new_references.last_updated_by := x_last_updated_by;
79 new_references.last_update_login := x_last_update_login;
80 END set_column_values;
81
82 PROCEDURE check_parent_existance AS
83 /*
84 || Created By : svenkata
85 || Created On : 08-FEB-2002
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 IF (((old_references.student_id = new_references.student_id)) OR
94 ((new_references.student_id IS NULL))) THEN
95 NULL;
96 ELSIF NOT Igs_Pe_Person_Pkg.get_pk_for_validation (
97 new_references.student_id
98 ) THEN
99 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
100 igs_ge_msg_stack.ADD;
101 app_exception.raise_exception;
102 END IF;
103 END check_parent_existance;
104
105 FUNCTION get_pk_for_validation (
106 x_request_id IN NUMBER
107 ) RETURN BOOLEAN AS
108 /*
109 || Created By : svenkata
110 || Created On : 08-FEB-2002
111 || Purpose : Validates the Primary Key of the table.
112 || Known limitations, enhancements or remarks :
113 || Change History :
114 || Who When What
115 || (reverse chronological order - newest change first)
116 */
117 CURSOR cur_rowid IS
118 SELECT ROWID
119 FROM igs_co_interac_hist
120 WHERE request_id = x_request_id
121 FOR UPDATE NOWAIT;
122 lv_rowid cur_rowid%ROWTYPE;
123 BEGIN
124 OPEN cur_rowid;
125 FETCH cur_rowid INTO lv_rowid;
126 IF (cur_rowid%FOUND) THEN
127 CLOSE cur_rowid;
128 RETURN(TRUE);
129 ELSE
130 CLOSE cur_rowid;
131 RETURN(FALSE);
132 END IF;
133 END get_pk_for_validation;
134
135 PROCEDURE before_dml (
136 p_action IN VARCHAR2,
137 x_rowid IN VARCHAR2 ,
138 x_student_id IN NUMBER ,
139 x_request_id IN NUMBER ,
140 x_document_id IN NUMBER ,
141 x_document_type IN VARCHAR2 ,
142 x_sys_ltr_code IN VARCHAR2 ,
143 x_adm_application_number IN NUMBER ,
144 x_nominated_course_cd IN VARCHAR2 ,
145 x_sequence_number IN NUMBER ,
146 x_cal_type IN VARCHAR2 ,
147 x_ci_sequence_number IN NUMBER ,
148 x_requested_date IN DATE ,
149 x_delivery_type IN VARCHAR2 ,
150 x_creation_date IN DATE ,
151 x_created_by IN NUMBER ,
152 x_last_update_date IN DATE ,
153 x_last_updated_by IN NUMBER ,
154 x_last_update_login IN NUMBER ,
155 x_version_id IN NUMBER
156 ) AS
157 /*
158 || Created By : svenkata
159 || Created On : 08-FEB-2002
160 || Purpose : Initialises the columns, Checks Constraints, Calls the
161 || Trigger Handlers for the table, before any DML operation.
162 || Known limitations, enhancements or remarks :
163 || Change History :
164 || Who When What
165 || (reverse chronological order - newest change first)
166 */
167 BEGIN
168 set_column_values (
169 p_action,
170 x_rowid,
171 x_student_id,
172 x_request_id,
173 x_document_id,
174 x_document_type,
175 x_sys_ltr_code,
176 x_adm_application_number,
177 x_nominated_course_cd,
178 x_sequence_number,
179 x_cal_type,
180 x_ci_sequence_number,
181 x_requested_date,
182 x_delivery_type,
183 x_creation_date,
184 x_created_by,
185 x_last_update_date,
186 x_last_updated_by,
187 x_last_update_login ,
188 x_version_id
189 );
190 IF (p_action = 'INSERT') THEN
191 -- Call all the procedures related to Before Insert.
192 IF ( get_pk_for_validation(
193 new_references.request_id
194 )
195 ) THEN
196 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
197 igs_ge_msg_stack.ADD;
198 app_exception.raise_exception;
199 END IF;
200 check_parent_existance;
201 ELSIF (p_action = 'UPDATE') THEN
202 -- Call all the procedures related to Before Update.
203 check_parent_existance;
204 ELSIF (p_action = 'VALIDATE_INSERT') THEN
205 -- Call all the procedures related to Before Insert.
206 IF ( get_pk_for_validation (
207 new_references.request_id
208 )
209 ) THEN
210 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
211 igs_ge_msg_stack.ADD;
212 app_exception.raise_exception;
213 END IF;
214 END IF;
215 END before_dml;
216
217 PROCEDURE insert_row (
218 x_rowid IN OUT NOCOPY VARCHAR2,
219 x_student_id IN NUMBER,
220 x_request_id IN NUMBER,
221 x_document_id IN NUMBER,
222 x_document_type IN VARCHAR2,
223 x_sys_ltr_code IN VARCHAR2,
224 x_adm_application_number IN NUMBER,
225 x_nominated_course_cd IN VARCHAR2,
226 x_sequence_number IN NUMBER,
227 x_cal_type IN VARCHAR2,
228 x_ci_sequence_number IN NUMBER,
229 x_requested_date IN DATE,
230 x_delivery_type IN VARCHAR2,
231 x_mode IN VARCHAR2,
232 x_version_id IN NUMBER
233 )AS
234 /*
235 || Created By : svenkata
236 || Created On : 08-FEB-2002
237 || Purpose : Handles the INSERT DML logic for the table.
238 || Known limitations, enhancements or remarks :
239 || Change History :
240 || Who When What
241 || (reverse chronological order - newest change first)
242 */
243 CURSOR c IS
244 SELECT ROWID
245 FROM igs_co_interac_hist
246 WHERE request_id = x_request_id;
247 x_last_update_date DATE;
248 x_last_updated_by NUMBER;
249 x_last_update_login NUMBER;
250 BEGIN
251 x_last_update_date := SYSDATE;
252 IF (x_mode = 'I') THEN
253 x_last_updated_by := 1;
254 x_last_update_login := 0;
255 ELSIF (x_mode = 'R') THEN
256 x_last_updated_by := fnd_global.user_id;
257 IF (x_last_updated_by IS NULL) THEN
258 x_last_updated_by := -1;
259 END IF;
260 x_last_update_login := fnd_global.login_id;
261 IF (x_last_update_login IS NULL) THEN
262 x_last_update_login := -1;
263 END IF;
264 END IF ;
265 before_dml(
266 p_action => 'INSERT',
267 x_rowid => x_rowid,
268 x_student_id => x_student_id,
269 x_request_id => x_request_id,
270 x_document_id => x_document_id,
271 x_document_type => x_document_type,
272 x_sys_ltr_code => x_sys_ltr_code,
273 x_adm_application_number => x_adm_application_number,
274 x_nominated_course_cd => x_nominated_course_cd,
275 x_sequence_number => x_sequence_number,
276 x_cal_type => x_cal_type,
277 x_ci_sequence_number => x_ci_sequence_number,
278 x_requested_date => x_requested_date,
279 x_delivery_type => x_delivery_type,
280 x_creation_date => x_last_update_date,
281 x_created_by => x_last_updated_by,
282 x_last_update_date => x_last_update_date,
283 x_last_updated_by => x_last_updated_by,
284 x_last_update_login => x_last_update_login ,
285 x_version_id => x_version_id
286 );
287 INSERT INTO igs_co_interac_hist (
288 student_id,
289 request_id,
290 document_id,
291 document_type,
292 sys_ltr_code,
293 adm_application_number,
294 nominated_course_cd,
295 sequence_number,
296 cal_type,
297 ci_sequence_number,
298 requested_date,
299 delivery_type,
300 creation_date,
301 created_by,
302 last_update_date,
303 last_updated_by,
304 last_update_login,
305 version_id
306 ) VALUES (
307 new_references.student_id,
308 new_references.request_id,
309 new_references.document_id,
310 new_references.document_type,
311 new_references.sys_ltr_code,
312 new_references.adm_application_number,
313 new_references.nominated_course_cd,
314 new_references.sequence_number,
315 new_references.cal_type,
316 new_references.ci_sequence_number,
317 new_references.requested_date,
318 new_references.delivery_type,
319 x_last_update_date,
320 x_last_updated_by,
321 x_last_update_date,
322 x_last_updated_by,
323 x_last_update_login ,
324 new_references.version_id
325 );
326 OPEN c;
327 FETCH c INTO x_rowid;
328 IF (c%NOTFOUND) THEN
329 CLOSE c;
330 RAISE NO_DATA_FOUND;
331 END IF;
332 CLOSE c;
333 END insert_row;
334
335 PROCEDURE lock_row (
336 x_rowid IN VARCHAR2,
337 x_student_id IN NUMBER,
338 x_request_id IN NUMBER,
339 x_document_id IN NUMBER,
340 x_document_type IN VARCHAR2,
341 x_sys_ltr_code IN VARCHAR2,
342 x_adm_application_number IN NUMBER,
343 x_nominated_course_cd IN VARCHAR2,
344 x_sequence_number IN NUMBER,
345 x_cal_type IN VARCHAR2,
346 x_ci_sequence_number IN NUMBER,
347 x_requested_date IN DATE,
348 x_delivery_type IN VARCHAR2,
349 x_version_id IN NUMBER
350 ) AS
351 /*
352 || Created By : svenkata
353 || Created On : 08-FEB-2002
354 || Purpose : Handles the LOCK mechanism for the table.
355 || Known limitations, enhancements or remarks :
356 || Change History :
357 || Who When What
358 || (reverse chronological order - newest change first)
359 */
360 CURSOR c1 IS
361 SELECT
362 student_id,
363 request_id,
364 document_id,
365 document_type,
366 sys_ltr_code,
367 adm_application_number,
368 nominated_course_cd,
369 sequence_number,
370 cal_type,
371 ci_sequence_number,
372 requested_date,
373 delivery_type,
374 version_id
375 FROM igs_co_interac_hist
376 WHERE ROWID = x_rowid
377 FOR UPDATE NOWAIT;
378 tlinfo c1%ROWTYPE;
379 BEGIN
380 OPEN c1;
381 FETCH c1 INTO tlinfo;
382 IF (c1%NOTFOUND) THEN
386 app_exception.raise_exception;
383 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
384 igs_ge_msg_stack.ADD;
385 CLOSE c1;
387 RETURN;
388 END IF;
389 CLOSE c1;
390 IF (
391 (tlinfo.student_id = x_student_id)
392 AND (tlinfo.request_id = x_request_id)
393 AND (tlinfo.document_id = x_document_id)
394 AND (tlinfo.document_type = x_document_type)
395 AND (tlinfo.sys_ltr_code = x_sys_ltr_code)
396 AND ((tlinfo.adm_application_number = x_adm_application_number) OR ((tlinfo.adm_application_number IS NULL) AND (X_adm_application_number IS NULL)))
397 AND ((tlinfo.nominated_course_cd = x_nominated_course_cd) OR ((tlinfo.nominated_course_cd IS NULL) AND (X_nominated_course_cd IS NULL)))
398 AND ((tlinfo.sequence_number = x_sequence_number) OR ((tlinfo.sequence_number IS NULL) AND (X_sequence_number IS NULL)))
399 AND ((tlinfo.cal_type = x_cal_type) OR ((tlinfo.cal_type IS NULL) AND (X_cal_type IS NULL)))
400 AND ((tlinfo.ci_sequence_number = x_ci_sequence_number) OR ((tlinfo.ci_sequence_number IS NULL) AND (X_ci_sequence_number IS NULL)))
401 AND (tlinfo.requested_date = x_requested_date)
402 AND (tlinfo.delivery_type = x_delivery_type)
403 AND ((tlinfo.version_id = x_version_id) OR ((tlinfo.version_id IS NULL) AND (X_version_id IS NULL)))
404 ) THEN
405 NULL;
406 ELSE
407 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
408 igs_ge_msg_stack.ADD;
409 app_exception.raise_exception;
410 END IF;
411 RETURN;
412 END lock_row;
413
414 PROCEDURE update_row (
415 x_rowid IN VARCHAR2,
416 x_student_id IN NUMBER,
417 x_request_id IN NUMBER,
418 x_document_id IN NUMBER,
419 x_document_type IN VARCHAR2,
420 x_sys_ltr_code IN VARCHAR2,
421 x_adm_application_number IN NUMBER,
422 x_nominated_course_cd IN VARCHAR2,
423 x_sequence_number IN NUMBER,
424 x_cal_type IN VARCHAR2,
425 x_ci_sequence_number IN NUMBER,
426 x_requested_date IN DATE,
427 x_delivery_type IN VARCHAR2,
428 x_mode IN VARCHAR2,
429 x_version_id IN NUMBER
430 ) AS
431 /*
432 || Created By : svenkata
433 || Created On : 08-FEB-2002
434 || Purpose : Handles the UPDATE DML logic for the table.
435 || Known limitations, enhancements or remarks :
436 || Change History :
437 || Who When What
438 || (reverse chronological order - newest change first)
439 */
440 x_last_update_date DATE ;
441 x_last_updated_by NUMBER;
442 x_last_update_login NUMBER;
443 BEGIN
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 before_dml(
463 p_action => 'UPDATE',
464 x_rowid => x_rowid,
465 x_student_id => x_student_id,
466 x_request_id => x_request_id,
467 x_document_id => x_document_id,
468 x_document_type => x_document_type,
469 x_sys_ltr_code => x_sys_ltr_code,
470 x_adm_application_number => x_adm_application_number,
471 x_nominated_course_cd => x_nominated_course_cd,
472 x_sequence_number => x_sequence_number,
473 x_cal_type => x_cal_type,
474 x_ci_sequence_number => x_ci_sequence_number,
475 x_requested_date => x_requested_date,
476 x_delivery_type => x_delivery_type,
477 x_creation_date => x_last_update_date,
478 x_created_by => x_last_updated_by,
479 x_last_update_date => x_last_update_date,
480 x_last_updated_by => x_last_updated_by,
481 x_last_update_login => x_last_update_login ,
482 x_version_id => x_version_id
483 );
484 UPDATE igs_co_interac_hist
485 SET
486 student_id = new_references.student_id,
487 request_id = new_references.request_id,
488 document_id = new_references.document_id,
489 document_type = new_references.document_type,
490 sys_ltr_code = new_references.sys_ltr_code,
491 adm_application_number = new_references.adm_application_number,
492 nominated_course_cd = new_references.nominated_course_cd,
493 sequence_number = new_references.sequence_number,
494 cal_type = new_references.cal_type,
495 ci_sequence_number = new_references.ci_sequence_number,
496 requested_date = new_references.requested_date,
497 delivery_type = new_references.delivery_type,
498 last_update_date = x_last_update_date,
499 last_updated_by = x_last_updated_by,
500 last_update_login = x_last_update_login,
501 version_id = new_references.version_id
502 WHERE ROWID = x_rowid;
503 IF (SQL%NOTFOUND) THEN
504 RAISE NO_DATA_FOUND;
505 END IF;
506 END update_row;
507
508 PROCEDURE add_row (
509 x_rowid IN OUT NOCOPY VARCHAR2,
510 x_student_id IN NUMBER,
511 x_request_id IN NUMBER,
512 x_document_id IN NUMBER,
513 x_document_type IN VARCHAR2,
514 x_sys_ltr_code IN VARCHAR2,
515 x_adm_application_number IN NUMBER,
516 x_nominated_course_cd IN VARCHAR2,
517 x_sequence_number IN NUMBER,
518 x_cal_type IN VARCHAR2,
519 x_ci_sequence_number IN NUMBER,
520 x_requested_date IN DATE,
521 x_delivery_type IN VARCHAR2,
522 x_mode IN VARCHAR2 ,
523 x_version_id IN NUMBER
524 ) AS
525 /*
526 || Created By : svenkata
527 || Created On : 08-FEB-2002
528 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
529 || Known limitations, enhancements or remarks :
530 || Change History :
531 || Who When What
532 || (reverse chronological order - newest change first)
533 */
534 CURSOR c1 IS
535 SELECT ROWID
536 FROM igs_co_interac_hist
537 WHERE request_id = x_request_id;
538 BEGIN
539 OPEN c1;
540 FETCH c1 INTO x_rowid;
541 IF (c1%NOTFOUND) THEN
542 CLOSE c1;
543 insert_row (
544 x_rowid,
545 x_student_id,
546 x_request_id,
547 x_document_id,
548 x_document_type,
549 x_sys_ltr_code,
550 x_adm_application_number,
551 x_nominated_course_cd,
552 x_sequence_number,
553 x_cal_type,
554 x_ci_sequence_number,
555 x_requested_date,
556 x_delivery_type,
557 x_mode ,
558 x_version_id
559 );
560 RETURN;
561 END IF;
562 CLOSE c1;
563 update_row (
564 x_rowid,
565 x_student_id,
566 x_request_id,
567 x_document_id,
568 x_document_type,
569 x_sys_ltr_code,
570 x_adm_application_number,
571 x_nominated_course_cd,
572 x_sequence_number,
573 x_cal_type,
574 x_ci_sequence_number,
575 x_requested_date,
576 x_delivery_type,
577 x_mode ,
578 x_version_id
579 );
580 END add_row;
581 PROCEDURE delete_row (
582 x_rowid IN VARCHAR2
583 ) AS
584 /*
585 || Created By : svenkata
586 || Created On : 08-FEB-2002
587 || Purpose : Handles the DELETE DML logic for the table.
588 || Known limitations, enhancements or remarks :
589 || Change History :
590 || Who When What
591 || (reverse chronological order - newest change first)
592 */
593 BEGIN
594 before_dml (
595 p_action => 'DELETE',
596 x_rowid => x_rowid
597 );
598 DELETE FROM igs_co_interac_hist
599 WHERE ROWID = x_rowid;
600 IF (SQL%NOTFOUND) THEN
601 RAISE NO_DATA_FOUND;
602 END IF;
603 END delete_row;
604 END Igs_Co_Interac_Hist_Pkg;
[Home] [Help]