[Home] [Help]
PACKAGE BODY: APPS.IGS_CO_OU_CO_REF_PKG
Source
1 PACKAGE BODY igs_co_ou_co_ref_pkg AS
2 /* $Header: IGSLI15B.pls 115.8 2002/11/29 01:06:06 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_co_ou_co_ref_all%ROWTYPE;
6 new_references igs_co_ou_co_ref_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_org_id IN NUMBER DEFAULT NULL,
12 x_person_id IN NUMBER DEFAULT NULL,
13 x_correspondence_type IN VARCHAR2 DEFAULT NULL,
14 x_reference_number IN NUMBER DEFAULT NULL,
15 x_issue_dt IN DATE DEFAULT NULL,
16 x_sequence_number IN NUMBER DEFAULT NULL,
17 x_cal_type IN VARCHAR2 DEFAULT NULL,
18 x_ci_sequence_number IN NUMBER DEFAULT NULL,
19 x_course_cd IN VARCHAR2 DEFAULT NULL,
20 x_cv_version_number IN NUMBER DEFAULT NULL,
21 x_unit_cd IN VARCHAR2 DEFAULT NULL,
22 x_uv_version_number IN NUMBER DEFAULT NULL,
23 x_s_other_reference_type IN VARCHAR2 DEFAULT NULL,
24 x_other_reference IN VARCHAR2 DEFAULT NULL,
25 x_creation_date IN DATE DEFAULT NULL,
26 x_created_by IN NUMBER DEFAULT NULL,
27 x_last_update_date IN DATE DEFAULT NULL,
28 x_last_updated_by IN NUMBER DEFAULT NULL,
29 x_last_update_login IN NUMBER DEFAULT NULL
30 ) AS
31 /*
32 || Created By : [email protected]
33 || Created On : 14-DEC-2000
34 || Purpose : Initialises the Old and New references for the columns of the table.
35 || Known limitations, enhancements or remarks :
36 || Change History :
37 || Who When What
38 || (reverse chronological order - newest change first)
39 */
40
41 CURSOR cur_old_ref_values IS
42 SELECT *
43 FROM IGS_CO_OU_CO_REF_ALL
44 WHERE rowid = x_rowid;
45
46 BEGIN
47
48 l_rowid := x_rowid;
49
50 -- Code for setting the Old and New Reference Values.
51 -- Populate Old Values.
52 OPEN cur_old_ref_values;
53 FETCH cur_old_ref_values INTO old_references;
54 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
55 CLOSE cur_old_ref_values;
56 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
57 igs_ge_msg_stack.add;
58 app_exception.raise_exception;
59 RETURN;
60 END IF;
61 CLOSE cur_old_ref_values;
62
63 -- Populate New Values.
64 new_references.org_id := x_org_id;
65 new_references.person_id := x_person_id;
66 new_references.correspondence_type := x_correspondence_type;
67 new_references.reference_number := x_reference_number;
68 new_references.issue_dt := x_issue_dt;
69 new_references.sequence_number := x_sequence_number;
70 new_references.cal_type := x_cal_type;
71 new_references.ci_sequence_number := x_ci_sequence_number;
72 new_references.course_cd := x_course_cd;
73 new_references.cv_version_number := x_cv_version_number;
74 new_references.unit_cd := x_unit_cd;
75 new_references.uv_version_number := x_uv_version_number;
76 new_references.s_other_reference_type := x_s_other_reference_type;
77 new_references.other_reference := x_other_reference;
78
79 IF (p_action = 'UPDATE') THEN
80 new_references.creation_date := old_references.creation_date;
81 new_references.created_by := old_references.created_by;
82 ELSE
83 new_references.creation_date := x_creation_date;
84 new_references.created_by := x_created_by;
85 END IF;
86
87 new_references.last_update_date := x_last_update_date;
88 new_references.last_updated_by := x_last_updated_by;
89 new_references.last_update_login := x_last_update_login;
90
91 END set_column_values;
92
93
94 PROCEDURE check_constraints (
95 column_name IN VARCHAR2 DEFAULT NULL,
96 column_value IN VARCHAR2 DEFAULT NULL
97 ) AS
98 /*
99 || Created By : [email protected]
100 || Created On : 14-DEC-2000
101 || Purpose : Handles the Check Constraint logic for the the columns.
102 || Known limitations, enhancements or remarks :
103 || Change History :
104 || Who When What
105 || (reverse chronological order - newest change first)
106 */
107 BEGIN
108
109 IF (column_name IS NULL) THEN
110 NULL;
111 ELSIF (UPPER(column_name) = 'REFERENCE_NUMBER') THEN
112 new_references.reference_number := igs_ge_number.to_num (column_value);
113 ELSIF (UPPER(column_name) = 'SEQUENCE_NUMBER') THEN
114 new_references.sequence_number := igs_ge_number.to_num (column_value);
115 ELSIF (UPPER(column_name) = 'CI_SEQUENCE_NUMBER') THEN
116 new_references.ci_sequence_number := igs_ge_number.to_num (column_value);
117 END IF;
118
119 IF (UPPER(column_name) = 'REFERENCE_NUMBER' OR column_name IS NULL) THEN
120 IF NOT (new_references.reference_number BETWEEN 1
121 AND 999999) THEN
122 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
123 igs_ge_msg_stack.add;
124 app_exception.raise_exception;
125 END IF;
126 END IF;
127
128 IF (UPPER(column_name) = 'SEQUENCE_NUMBER' OR column_name IS NULL) THEN
129 IF NOT (new_references.sequence_number BETWEEN 1
130 AND 999999) THEN
131 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
132 igs_ge_msg_stack.add;
133 app_exception.raise_exception;
134 END IF;
135 END IF;
136
137 IF (UPPER(column_name) = 'CI_SEQUENCE_NUMBER' OR column_name IS NULL) THEN
138 IF NOT (new_references.ci_sequence_number BETWEEN 1
139 AND 999999) THEN
140 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
141 igs_ge_msg_stack.add;
142 app_exception.raise_exception;
143 END IF;
144 END IF;
145
146 END check_constraints;
147
148
149 PROCEDURE check_parent_existance AS
150 /*
151 || Created By : [email protected]
152 || Created On : 14-DEC-2000
153 || Purpose : Checks for the existance of Parent records.
154 || Known limitations, enhancements or remarks :
155 || Change History :
156 || Who When What
157 || (reverse chronological order - newest change first)
158 */
159 BEGIN
160
161 IF (((old_references.cal_type = new_references.cal_type) AND
162 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
163 ((new_references.cal_type IS NULL) OR
164 (new_references.ci_sequence_number IS NULL))) THEN
165 NULL;
166 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
167 new_references.cal_type,
168 new_references.ci_sequence_number
169 ) THEN
170 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
171 igs_ge_msg_stack.add;
172 app_exception.raise_exception;
173 END IF;
174
175 IF (((old_references.unit_cd = new_references.unit_cd) AND
176 (old_references.uv_version_number = new_references.uv_version_number)) OR
177 ((new_references.unit_cd IS NULL) OR
178 (new_references.uv_version_number IS NULL))) THEN
179 NULL;
180 ELSIF NOT igs_ps_unit_ver_pkg.get_pk_for_validation (
181 new_references.unit_cd,
182 new_references.uv_version_number
183 ) THEN
184 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
185 igs_ge_msg_stack.add;
186 app_exception.raise_exception;
187 END IF;
188
189 IF (((old_references.course_cd = new_references.course_cd) AND
190 (old_references.cv_version_number = new_references.cv_version_number)) OR
191 ((new_references.course_cd IS NULL) OR
192 (new_references.cv_version_number IS NULL))) THEN
193 NULL;
194 ELSIF NOT igs_ps_ver_pkg.get_pk_for_validation (
195 new_references.course_cd,
196 new_references.cv_version_number
197 ) THEN
198 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
199 igs_ge_msg_stack.add;
200 app_exception.raise_exception;
201 END IF;
202
203 IF (((old_references.person_id = new_references.person_id) AND
204 (old_references.correspondence_type = new_references.correspondence_type) AND
205 (old_references.reference_number = new_references.reference_number) AND
206 (old_references.issue_dt = new_references.issue_dt)) OR
207 ((new_references.person_id IS NULL) OR
208 (new_references.correspondence_type IS NULL) OR
209 (new_references.reference_number IS NULL) OR
210 (new_references.issue_dt IS NULL))) THEN
211 NULL;
212 ELSIF NOT igs_co_ou_co_pkg.get_pk_for_validation (
213 new_references.person_id,
214 new_references.correspondence_type,
215 new_references.reference_number,
216 new_references.issue_dt
217 ) THEN
218 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
219 igs_ge_msg_stack.add;
220 app_exception.raise_exception;
221 END IF;
222
223 END check_parent_existance;
224
225
226 FUNCTION get_pk_for_validation (
227 x_person_id IN NUMBER,
228 x_correspondence_type IN VARCHAR2,
229 x_reference_number IN NUMBER,
230 x_issue_dt IN DATE,
231 x_sequence_number IN NUMBER
232 ) RETURN BOOLEAN AS
233 /*
234 || Created By : [email protected]
235 || Created On : 14-DEC-2000
236 || Purpose : Validates the Primary Key of the table.
237 || Known limitations, enhancements or remarks :
238 || Change History :
239 || Who When What
240 || (reverse chronological order - newest change first)
241 */
242 CURSOR cur_rowid IS
243 SELECT rowid
244 FROM igs_co_ou_co_ref_all
245 WHERE person_id = x_person_id
246 AND correspondence_type = x_correspondence_type
247 AND reference_number = x_reference_number
248 AND issue_dt = x_issue_dt
249 AND sequence_number = x_sequence_number
250 FOR UPDATE NOWAIT;
251
252 lv_rowid cur_rowid%RowType;
253
254 BEGIN
255
256 OPEN cur_rowid;
257 FETCH cur_rowid INTO lv_rowid;
258 IF (cur_rowid%FOUND) THEN
259 CLOSE cur_rowid;
260 RETURN(TRUE);
261 ELSE
262 CLOSE cur_rowid;
263 RETURN(FALSE);
264 END IF;
265
266 END get_pk_for_validation;
267
268
269 PROCEDURE get_fk_igs_ca_inst (
270 x_cal_type IN VARCHAR2,
271 x_sequence_number IN NUMBER
272 ) AS
273 /*
274 || Created By : [email protected]
275 || Created On : 14-DEC-2000
276 || Purpose : Validates the Foreign Keys for the table.
277 || Known limitations, enhancements or remarks :
278 || Change History :
279 || Who When What
280 || (reverse chronological order - newest change first)
281 */
282 CURSOR cur_rowid IS
283 SELECT rowid
284 FROM igs_co_ou_co_ref_all
285 WHERE ((cal_type = x_cal_type) AND
286 (ci_sequence_number = x_sequence_number));
287
288 lv_rowid cur_rowid%RowType;
289
290 BEGIN
291
292 OPEN cur_rowid;
293 FETCH cur_rowid INTO lv_rowid;
294 IF (cur_rowid%FOUND) THEN
295 CLOSE cur_rowid;
296 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
297 igs_ge_msg_stack.add;
298 app_exception.raise_exception;
299 RETURN;
300 END IF;
301 CLOSE cur_rowid;
302
303 END get_fk_igs_ca_inst;
304
305
306 PROCEDURE get_fk_igs_ps_unit_ver (
307 x_unit_cd IN VARCHAR2,
308 x_version_number IN NUMBER
309 ) AS
310 /*
311 || Created By : [email protected]
312 || Created On : 14-DEC-2000
313 || Purpose : Validates the Foreign Keys for the table.
317 || (reverse chronological order - newest change first)
314 || Known limitations, enhancements or remarks :
315 || Change History :
316 || Who When What
318 */
319 CURSOR cur_rowid IS
320 SELECT rowid
321 FROM igs_co_ou_co_ref_all
322 WHERE ((unit_cd = x_unit_cd) AND
323 (uv_version_number = x_version_number));
324
325 lv_rowid cur_rowid%RowType;
326
327 BEGIN
328
329 OPEN cur_rowid;
330 FETCH cur_rowid INTO lv_rowid;
331 IF (cur_rowid%FOUND) THEN
332 CLOSE cur_rowid;
333 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
334 igs_ge_msg_stack.add;
335 app_exception.raise_exception;
336 RETURN;
337 END IF;
338 CLOSE cur_rowid;
339
340 END get_fk_igs_ps_unit_ver;
341
342
343 PROCEDURE get_fk_igs_ps_ver (
344 x_course_cd IN VARCHAR2,
345 x_version_number IN NUMBER
346 ) AS
347 /*
348 || Created By : [email protected]
349 || Created On : 14-DEC-2000
350 || Purpose : Validates the Foreign Keys for the table.
351 || Known limitations, enhancements or remarks :
352 || Change History :
353 || Who When What
354 || (reverse chronological order - newest change first)
355 */
356 CURSOR cur_rowid IS
357 SELECT rowid
358 FROM igs_co_ou_co_ref_all
359 WHERE ((course_cd = x_course_cd) AND
360 (cv_version_number = x_version_number));
361
362 lv_rowid cur_rowid%RowType;
363
364 BEGIN
365
366 OPEN cur_rowid;
367 FETCH cur_rowid INTO lv_rowid;
368 IF (cur_rowid%FOUND) THEN
369 CLOSE cur_rowid;
370 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
371 igs_ge_msg_stack.add;
372 app_exception.raise_exception;
373 RETURN;
374 END IF;
375 CLOSE cur_rowid;
376
377 END get_fk_igs_ps_ver;
378
379
380 PROCEDURE get_fk_igs_co_ou_co (
381 x_person_id IN NUMBER,
382 x_correspondence_type IN VARCHAR2,
383 x_reference_number IN NUMBER,
384 x_issue_dt IN DATE
385 ) AS
386 /*
387 || Created By : [email protected]
388 || Created On : 14-DEC-2000
389 || Purpose : Validates the Foreign Keys for the table.
390 || Known limitations, enhancements or remarks :
391 || Change History :
392 || Who When What
393 || (reverse chronological order - newest change first)
394 */
395 CURSOR cur_rowid IS
396 SELECT rowid
397 FROM igs_co_ou_co_ref_all
398 WHERE ((person_id = x_person_id) AND
399 (correspondence_type = x_correspondence_type) AND
400 (reference_number = x_reference_number) AND
401 (issue_dt = x_issue_dt));
402
403 lv_rowid cur_rowid%RowType;
404
405 BEGIN
406
407 OPEN cur_rowid;
408 FETCH cur_rowid INTO lv_rowid;
409 IF (cur_rowid%FOUND) THEN
410 CLOSE cur_rowid;
411 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
412 igs_ge_msg_stack.add;
413 app_exception.raise_exception;
414 RETURN;
415 END IF;
416 CLOSE cur_rowid;
417
418 END get_fk_igs_co_ou_co;
419
420
421 PROCEDURE before_dml (
422 p_action IN VARCHAR2,
423 x_rowid IN VARCHAR2 DEFAULT NULL,
424 x_org_id IN NUMBER DEFAULT NULL,
425 x_person_id IN NUMBER DEFAULT NULL,
426 x_correspondence_type IN VARCHAR2 DEFAULT NULL,
427 x_reference_number IN NUMBER DEFAULT NULL,
428 x_issue_dt IN DATE DEFAULT NULL,
429 x_sequence_number IN NUMBER DEFAULT NULL,
430 x_cal_type IN VARCHAR2 DEFAULT NULL,
431 x_ci_sequence_number IN NUMBER DEFAULT NULL,
432 x_course_cd IN VARCHAR2 DEFAULT NULL,
433 x_cv_version_number IN NUMBER DEFAULT NULL,
434 x_unit_cd IN VARCHAR2 DEFAULT NULL,
435 x_uv_version_number IN NUMBER DEFAULT NULL,
436 x_s_other_reference_type IN VARCHAR2 DEFAULT NULL,
437 x_other_reference IN VARCHAR2 DEFAULT NULL,
438 x_creation_date IN DATE DEFAULT NULL,
439 x_created_by IN NUMBER DEFAULT NULL,
440 x_last_update_date IN DATE DEFAULT NULL,
441 x_last_updated_by IN NUMBER DEFAULT NULL,
442 x_last_update_login IN NUMBER DEFAULT NULL
443 ) AS
444 /*
445 || Created By : [email protected]
446 || Created On : 14-DEC-2000
447 || Purpose : Initialises the columns, Checks Constraints, Calls the
448 || Trigger Handlers for the table, before any DML operation.
452 || (reverse chronological order - newest change first)
449 || Known limitations, enhancements or remarks :
450 || Change History :
451 || Who When What
453 */
454 BEGIN
455
456 set_column_values (
457 p_action,
458 x_rowid,
459 x_org_id,
460 x_person_id,
461 x_correspondence_type,
462 x_reference_number,
463 x_issue_dt,
464 x_sequence_number,
465 x_cal_type,
466 x_ci_sequence_number,
467 x_course_cd,
468 x_cv_version_number,
469 x_unit_cd,
470 x_uv_version_number,
471 x_s_other_reference_type,
472 x_other_reference,
473 x_creation_date,
474 x_created_by,
475 x_last_update_date,
476 x_last_updated_by,
477 x_last_update_login
478 );
479
480 IF (p_action = 'INSERT') THEN
481 -- Call all the procedures related to Before Insert.
482 IF ( get_pk_for_validation(
483 new_references.person_id,
484 new_references.correspondence_type,
485 new_references.reference_number,
486 new_references.issue_dt,
487 new_references.sequence_number
488 )
489 ) THEN
490 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
491 igs_ge_msg_stack.add;
492 app_exception.raise_exception;
493 END IF;
494 check_constraints;
495 check_parent_existance;
496 ELSIF (p_action = 'UPDATE') THEN
497 -- Call all the procedures related to Before Update.
498 check_constraints;
499 check_parent_existance;
500 ELSIF (p_action = 'VALIDATE_INSERT') THEN
501 -- Call all the procedures related to Before Insert.
502 IF ( get_pk_for_validation (
503 new_references.person_id,
504 new_references.correspondence_type,
505 new_references.reference_number,
506 new_references.issue_dt,
507 new_references.sequence_number
508 )
509 ) THEN
510 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
511 igs_ge_msg_stack.add;
512 app_exception.raise_exception;
513 END IF;
514 check_constraints;
515 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
516 check_constraints;
517 END IF;
518
519 END before_dml;
520
521
522 PROCEDURE insert_row (
523 x_rowid IN OUT NOCOPY VARCHAR2,
524 x_org_id IN NUMBER,
525 x_person_id IN NUMBER,
526 x_correspondence_type IN VARCHAR2,
527 x_reference_number IN NUMBER,
528 x_issue_dt IN OUT NOCOPY DATE,
529 x_sequence_number IN NUMBER,
530 x_cal_type IN VARCHAR2,
531 x_ci_sequence_number IN NUMBER,
532 x_course_cd IN VARCHAR2,
533 x_cv_version_number IN NUMBER,
534 x_unit_cd IN VARCHAR2,
535 x_uv_version_number IN NUMBER,
536 x_s_other_reference_type IN VARCHAR2,
537 x_other_reference IN VARCHAR2,
538 x_mode IN VARCHAR2 DEFAULT 'R'
539 ) AS
540 /*
541 || Created By : [email protected]
542 || Created On : 14-DEC-2000
543 || Purpose : Handles the INSERT DML logic for the table.
544 || Known limitations, enhancements or remarks :
545 || Change History :
546 || Who When What
547 || (reverse chronological order - newest change first)
548 */
549 CURSOR c IS
550 SELECT rowid
551 FROM igs_co_ou_co_ref_all
552 WHERE person_id = x_person_id
553 AND correspondence_type = x_correspondence_type
554 AND reference_number = x_reference_number
555 AND issue_dt = new_references.issue_dt
556 AND sequence_number = x_sequence_number;
557
558 x_last_update_date DATE;
559 x_last_updated_by NUMBER;
560 x_last_update_login NUMBER;
561 x_request_id NUMBER;
562 x_program_id NUMBER;
563 x_program_application_id NUMBER;
564 x_program_update_date DATE;
565
566 BEGIN
567
568 x_last_update_date := SYSDATE;
569 IF (x_mode = 'I') THEN
570 x_last_updated_by := 1;
571 x_last_update_login := 0;
572 ELSIF (x_mode = 'R') THEN
573 x_last_updated_by := fnd_global.user_id;
574 IF (x_last_updated_by IS NULL) THEN
575 x_last_updated_by := -1;
576 END IF;
577 x_last_update_login := fnd_global.login_id;
578 IF (x_last_update_login IS NULL) THEN
579 x_last_update_login := -1;
580 END IF;
584
581 x_request_id := fnd_global.conc_request_id;
582 x_program_id := fnd_global.conc_program_id;
583 x_program_application_id := fnd_global.prog_appl_id;
585 IF (x_request_id = -1) THEN
586 x_request_id := NULL;
587 x_program_id := NULL;
588 x_program_application_id := NULL;
589 x_program_update_date := NULL;
590 ELSE
591 x_program_update_date := SYSDATE;
592 END IF;
593 ELSE
594 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
595 igs_ge_msg_stack.add;
596 app_exception.raise_exception;
597 END IF;
598
599 before_dml(
600 p_action => 'INSERT',
601 x_rowid => x_rowid,
602 x_org_id => igs_ge_gen_003.get_org_id,
603 x_person_id => x_person_id,
604 x_correspondence_type => x_correspondence_type,
605 x_reference_number => x_reference_number,
606 x_issue_dt => NVL (x_issue_dt,sysdate ),
607 x_sequence_number => x_sequence_number,
608 x_cal_type => x_cal_type,
609 x_ci_sequence_number => x_ci_sequence_number,
610 x_course_cd => x_course_cd,
611 x_cv_version_number => x_cv_version_number,
612 x_unit_cd => x_unit_cd,
613 x_uv_version_number => x_uv_version_number,
614 x_s_other_reference_type => x_s_other_reference_type,
615 x_other_reference => x_other_reference,
616 x_creation_date => x_last_update_date,
617 x_created_by => x_last_updated_by,
618 x_last_update_date => x_last_update_date,
619 x_last_updated_by => x_last_updated_by,
620 x_last_update_login => x_last_update_login
621 );
622
623 INSERT INTO igs_co_ou_co_ref_all (
624 org_id,
625 person_id,
626 correspondence_type,
627 reference_number,
628 issue_dt,
629 sequence_number,
630 cal_type,
631 ci_sequence_number,
632 course_cd,
633 cv_version_number,
634 unit_cd,
635 uv_version_number,
636 s_other_reference_type,
637 other_reference,
638 creation_date,
639 created_by,
640 last_update_date,
641 last_updated_by,
642 last_update_login,
643 request_id,
644 program_id,
645 program_application_id,
646 program_update_date
647 ) VALUES (
648 new_references.org_id,
649 new_references.person_id,
650 new_references.correspondence_type,
651 new_references.reference_number,
652 new_references.issue_dt,
653 new_references.sequence_number,
654 new_references.cal_type,
655 new_references.ci_sequence_number,
656 new_references.course_cd,
657 new_references.cv_version_number,
658 new_references.unit_cd,
659 new_references.uv_version_number,
660 new_references.s_other_reference_type,
661 new_references.other_reference,
662 x_last_update_date,
663 x_last_updated_by,
664 x_last_update_date,
665 x_last_updated_by,
666 x_last_update_login ,
667 x_request_id,
668 x_program_id,
669 x_program_application_id,
670 x_program_update_date
671 );
672
673 OPEN c;
674 FETCH c INTO x_rowid;
675 IF (c%NOTFOUND) THEN
676 CLOSE c;
677 RAISE NO_DATA_FOUND;
678 END IF;
679 CLOSE c;
680
681 END insert_row;
682
683
684 PROCEDURE lock_row (
685 x_rowid IN VARCHAR2,
686 x_person_id IN NUMBER,
687 x_correspondence_type IN VARCHAR2,
688 x_reference_number IN NUMBER,
689 x_issue_dt IN DATE,
690 x_sequence_number IN NUMBER,
691 x_cal_type IN VARCHAR2,
692 x_ci_sequence_number IN NUMBER,
693 x_course_cd IN VARCHAR2,
694 x_cv_version_number IN NUMBER,
695 x_unit_cd IN VARCHAR2,
696 x_uv_version_number IN NUMBER,
697 x_s_other_reference_type IN VARCHAR2,
698 x_other_reference IN VARCHAR2
699 ) AS
700 /*
701 || Created By : [email protected]
702 || Created On : 14-DEC-2000
703 || Purpose : Handles the LOCK mechanism for the table.
704 || Known limitations, enhancements or remarks :
705 || Change History :
706 || Who When What
707 || (reverse chronological order - newest change first)
708 */
709 CURSOR c1 IS
710 SELECT
711 cal_type,
712 ci_sequence_number,
713 course_cd,
717 s_other_reference_type,
714 cv_version_number,
715 unit_cd,
716 uv_version_number,
718 other_reference
719 FROM igs_co_ou_co_ref_all
720 WHERE rowid = x_rowid
721 FOR UPDATE NOWAIT;
722
723 tlinfo c1%ROWTYPE;
724
725 BEGIN
726
727 OPEN c1;
728 FETCH c1 INTO tlinfo;
729 IF (c1%notfound) THEN
730 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
731 igs_ge_msg_stack.add;
732 CLOSE c1;
733 app_exception.raise_exception;
734 RETURN;
735 END IF;
736 CLOSE c1;
737
738 IF (
739 ((tlinfo.cal_type = x_cal_type) OR ((tlinfo.cal_type IS NULL) AND (X_cal_type IS NULL)))
740 AND ((tlinfo.ci_sequence_number = x_ci_sequence_number) OR ((tlinfo.ci_sequence_number IS NULL) AND (X_ci_sequence_number IS NULL)))
741 AND ((tlinfo.course_cd = x_course_cd) OR ((tlinfo.course_cd IS NULL) AND (X_course_cd IS NULL)))
742 AND ((tlinfo.cv_version_number = x_cv_version_number) OR ((tlinfo.cv_version_number IS NULL) AND (X_cv_version_number IS NULL)))
743 AND ((tlinfo.unit_cd = x_unit_cd) OR ((tlinfo.unit_cd IS NULL) AND (X_unit_cd IS NULL)))
744 AND ((tlinfo.uv_version_number = x_uv_version_number) OR ((tlinfo.uv_version_number IS NULL) AND (X_uv_version_number IS NULL)))
745 AND ((tlinfo.s_other_reference_type = x_s_other_reference_type) OR ((tlinfo.s_other_reference_type IS NULL) AND (X_s_other_reference_type IS NULL)))
746 AND ((tlinfo.other_reference = x_other_reference) OR ((tlinfo.other_reference IS NULL) AND (X_other_reference IS NULL)))
747 ) THEN
748 NULL;
749 ELSE
750 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
751 igs_ge_msg_stack.add;
752 app_exception.raise_exception;
753 END IF;
754
755 RETURN;
756
757 END lock_row;
758
759
760 PROCEDURE update_row (
761 x_rowid IN VARCHAR2,
762 x_person_id IN NUMBER,
763 x_correspondence_type IN VARCHAR2,
764 x_reference_number IN NUMBER,
765 x_issue_dt IN DATE,
766 x_sequence_number IN NUMBER,
767 x_cal_type IN VARCHAR2,
768 x_ci_sequence_number IN NUMBER,
769 x_course_cd IN VARCHAR2,
770 x_cv_version_number IN NUMBER,
771 x_unit_cd IN VARCHAR2,
772 x_uv_version_number IN NUMBER,
773 x_s_other_reference_type IN VARCHAR2,
774 x_other_reference IN VARCHAR2,
775 x_mode IN VARCHAR2 DEFAULT 'R'
776 ) AS
777 /*
778 || Created By : [email protected]
779 || Created On : 14-DEC-2000
780 || Purpose : Handles the UPDATE DML logic for the table.
781 || Known limitations, enhancements or remarks :
782 || Change History :
783 || Who When What
784 || (reverse chronological order - newest change first)
785 */
786 x_last_update_date DATE ;
787 x_last_updated_by NUMBER;
788 x_last_update_login NUMBER;
789 x_request_id NUMBER;
790 x_program_id NUMBER;
791 x_program_application_id NUMBER;
792 x_program_update_date DATE;
793
794 BEGIN
795
796 x_last_update_date := SYSDATE;
797 IF (X_MODE = 'I') THEN
798 x_last_updated_by := 1;
799 x_last_update_login := 0;
800 ELSIF (x_mode = 'R') THEN
801 x_last_updated_by := fnd_global.user_id;
802 IF x_last_updated_by IS NULL THEN
803 x_last_updated_by := -1;
804 END IF;
805 x_last_update_login := fnd_global.login_id;
806 IF (x_last_update_login IS NULL) THEN
807 x_last_update_login := -1;
808 END IF;
809 ELSE
810 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
811 igs_ge_msg_stack.add;
812 app_exception.raise_exception;
813 END IF;
814
815 before_dml(
816 p_action => 'UPDATE',
817 x_rowid => x_rowid,
818 x_person_id => x_person_id,
819 x_correspondence_type => x_correspondence_type,
820 x_reference_number => x_reference_number,
821 x_issue_dt => NVL (x_issue_dt,sysdate ),
822 x_sequence_number => x_sequence_number,
823 x_cal_type => x_cal_type,
824 x_ci_sequence_number => x_ci_sequence_number,
825 x_course_cd => x_course_cd,
826 x_cv_version_number => x_cv_version_number,
827 x_unit_cd => x_unit_cd,
828 x_uv_version_number => x_uv_version_number,
829 x_s_other_reference_type => x_s_other_reference_type,
830 x_other_reference => x_other_reference,
831 x_creation_date => x_last_update_date,
832 x_created_by => x_last_updated_by,
833 x_last_update_date => x_last_update_date,
834 x_last_updated_by => x_last_updated_by,
835 x_last_update_login => x_last_update_login
836 );
837
838 IF (x_mode = 'R') THEN
839 x_request_id := fnd_global.conc_request_id;
840 x_program_id := fnd_global.conc_program_id;
841 x_program_application_id := fnd_global.prog_appl_id;
842 IF (x_request_id = -1) THEN
843 x_request_id := old_references.request_id;
844 x_program_id := old_references.program_id;
845 x_program_application_id := old_references.program_application_id;
846 x_program_update_date := old_references.program_update_date;
847 ELSE
848 x_program_update_date := SYSDATE;
849 END IF;
850 END IF;
851
852 UPDATE igs_co_ou_co_ref_all
853 SET
854 cal_type = new_references.cal_type,
855 ci_sequence_number = new_references.ci_sequence_number,
856 course_cd = new_references.course_cd,
857 cv_version_number = new_references.cv_version_number,
858 unit_cd = new_references.unit_cd,
859 uv_version_number = new_references.uv_version_number,
860 s_other_reference_type = new_references.s_other_reference_type,
861 other_reference = new_references.other_reference,
862 last_update_date = x_last_update_date,
863 last_updated_by = x_last_updated_by,
864 last_update_login = x_last_update_login ,
865 request_id = x_request_id,
866 program_id = x_program_id,
867 program_application_id = x_program_application_id,
868 program_update_date = x_program_update_date
869 WHERE rowid = x_rowid;
870
871 IF (SQL%NOTFOUND) THEN
872 RAISE NO_DATA_FOUND;
873 END IF;
874
875 END update_row;
876
877
878 PROCEDURE add_row (
879 x_rowid IN OUT NOCOPY VARCHAR2,
880 x_org_id IN NUMBER,
881 x_person_id IN NUMBER,
882 x_correspondence_type IN VARCHAR2,
883 x_reference_number IN NUMBER,
884 x_issue_dt IN OUT NOCOPY DATE,
885 x_sequence_number IN NUMBER,
886 x_cal_type IN VARCHAR2,
887 x_ci_sequence_number IN NUMBER,
888 x_course_cd IN VARCHAR2,
889 x_cv_version_number IN NUMBER,
890 x_unit_cd IN VARCHAR2,
891 x_uv_version_number IN NUMBER,
892 x_s_other_reference_type IN VARCHAR2,
893 x_other_reference IN VARCHAR2,
894 x_mode IN VARCHAR2 DEFAULT 'R'
895 ) AS
896 /*
897 || Created By : [email protected]
898 || Created On : 14-DEC-2000
899 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
900 || Known limitations, enhancements or remarks :
901 || Change History :
902 || Who When What
903 || (reverse chronological order - newest change first)
904 */
905 CURSOR c1 IS
906 SELECT rowid
907 FROM igs_co_ou_co_ref_all
908 WHERE person_id = x_person_id
909 AND correspondence_type = x_correspondence_type
910 AND reference_number = x_reference_number
911 AND issue_dt = NVL (x_issue_dt,SYSDATE)
912 AND sequence_number = x_sequence_number;
913
914 BEGIN
915
916 OPEN c1;
917 FETCH c1 INTO x_rowid;
918 IF (c1%NOTFOUND) THEN
919 CLOSE c1;
920
921 insert_row (
922 x_rowid,
923 x_org_id,
924 x_person_id,
925 x_correspondence_type,
926 x_reference_number,
927 x_issue_dt,
928 x_sequence_number,
929 x_cal_type,
930 x_ci_sequence_number,
931 x_course_cd,
932 x_cv_version_number,
933 x_unit_cd,
934 x_uv_version_number,
935 x_s_other_reference_type,
936 x_other_reference,
937 x_mode
938 );
939 RETURN;
940 END IF;
941 CLOSE c1;
942
943 update_row (
944 x_rowid,
945 x_person_id,
946 x_correspondence_type,
947 x_reference_number,
948 x_issue_dt,
949 x_sequence_number,
950 x_cal_type,
951 x_ci_sequence_number,
952 x_course_cd,
953 x_cv_version_number,
954 x_unit_cd,
955 x_uv_version_number,
956 x_s_other_reference_type,
957 x_other_reference,
958 x_mode
959 );
960
961 END add_row;
962
963
964 PROCEDURE delete_row (
965 x_rowid IN VARCHAR2
966 ) AS
967 /*
968 || Created By : [email protected]
969 || Created On : 14-DEC-2000
970 || Purpose : Handles the DELETE DML logic for the table.
971 || Known limitations, enhancements or remarks :
972 || Change History :
973 || Who When What
974 || (reverse chronological order - newest change first)
975 */
976 BEGIN
977
978 before_dml (
979 p_action => 'DELETE',
980 x_rowid => x_rowid
981 );
982
983 DELETE FROM igs_co_ou_co_ref_all
984 WHERE rowid = x_rowid;
985
986 IF (SQL%NOTFOUND) THEN
987 RAISE NO_DATA_FOUND;
988 END IF;
989
990 END delete_row;
991
992
993 END igs_co_ou_co_ref_pkg;