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