[Home] [Help]
PACKAGE BODY: APPS.IGS_DA_CNFG_REQ_TYP_PKG
Source
1 PACKAGE BODY igs_da_cnfg_req_typ_pkg AS
2 /* $Header: IGSKI46B.pls 120.0 2005/07/05 12:55:39 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_da_cnfg_req_typ%ROWTYPE;
6 new_references igs_da_cnfg_req_typ%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_request_type_id IN NUMBER,
12 x_request_name IN VARCHAR2,
13 x_request_type IN VARCHAR2,
14 x_request_mode IN VARCHAR2,
15 x_closed_ind IN VARCHAR2,
16 x_purgable_ind IN VARCHAR2,
17 x_request_type_comment IN VARCHAR2,
18 x_wif_ind IN VARCHAR2,
19 x_wif_program_code IN VARCHAR2,
20 x_wif_program_mod_ind IN VARCHAR2,
21 x_wif_catalog_cal_type IN VARCHAR2,
22 x_wif_catalog_ci_seq_num IN NUMBER,
23 x_wif_catalog_mod_ind IN VARCHAR2,
24 x_special_ind IN VARCHAR2,
25 x_special_program_code IN VARCHAR2,
26 x_special_program_mod_ind IN VARCHAR2,
27 x_special_catalog IN VARCHAR2,
28 x_special_catalog_mod_ind IN VARCHAR2,
29 x_enrolled_ind IN VARCHAR2,
30 x_attribute_category IN VARCHAR2,
31 x_attribute1 IN VARCHAR2,
32 x_attribute2 IN VARCHAR2,
33 x_attribute3 IN VARCHAR2,
34 x_attribute4 IN VARCHAR2,
35 x_attribute5 IN VARCHAR2,
36 x_attribute6 IN VARCHAR2,
37 x_attribute7 IN VARCHAR2,
38 x_attribute8 IN VARCHAR2,
39 x_attribute9 IN VARCHAR2,
40 x_attribute10 IN VARCHAR2,
41 x_attribute11 IN VARCHAR2,
42 x_attribute12 IN VARCHAR2,
43 x_attribute13 IN VARCHAR2,
44 x_attribute14 IN VARCHAR2,
45 x_attribute15 IN VARCHAR2,
46 x_attribute16 IN VARCHAR2,
47 x_attribute17 IN VARCHAR2,
48 x_attribute18 IN VARCHAR2,
49 x_attribute19 IN VARCHAR2,
50 x_attribute20 IN VARCHAR2,
51 x_spa_complete_ind IN VARCHAR2,
52 x_susa_complete_ind IN VARCHAR2,
53 x_creation_date IN DATE,
54 x_created_by IN NUMBER,
55 x_last_update_date IN DATE,
56 x_last_updated_by IN NUMBER,
57 x_last_update_login IN NUMBER
58 ) AS
59 /*
60 || Created By :
61 || Created On : 19-MAR-2003
62 || Purpose : Initialises the Old and New references for the columns of the table.
63 || Known limitations, enhancements or remarks :
64 || Change History :
65 || Who When What
66 || (reverse chronological order - newest change first)
67 */
68
69 CURSOR cur_old_ref_values IS
70 SELECT *
71 FROM igs_da_cnfg_req_typ
72 WHERE rowid = x_rowid;
73
74 BEGIN
75
76 l_rowid := x_rowid;
77
78 -- Code for setting the Old and New Reference Values.
79 -- Populate Old Values.
80 OPEN cur_old_ref_values;
81 FETCH cur_old_ref_values INTO old_references;
82 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
83 CLOSE cur_old_ref_values;
84 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
85 igs_ge_msg_stack.add;
86 app_exception.raise_exception;
87 RETURN;
88 END IF;
89 CLOSE cur_old_ref_values;
90
91 -- Populate New Values.
92 new_references.request_type_id := x_request_type_id;
93 new_references.request_name := x_request_name;
94 new_references.request_type := x_request_type;
95 new_references.request_mode := x_request_mode;
96 new_references.closed_ind := x_closed_ind;
97 new_references.purgable_ind := x_purgable_ind;
98 new_references.request_type_comment := x_request_type_comment;
99 new_references.wif_ind := x_wif_ind;
100 new_references.wif_program_code := x_wif_program_code;
101 new_references.wif_program_mod_ind := x_wif_program_mod_ind;
102 new_references.wif_catalog_cal_type := x_wif_catalog_cal_type;
103 new_references.wif_catalog_ci_seq_num := x_wif_catalog_ci_seq_num;
104 new_references.wif_catalog_mod_ind := x_wif_catalog_mod_ind;
105 new_references.special_ind := x_special_ind;
106 new_references.special_program_code := x_special_program_code;
107 new_references.special_program_mod_ind := x_special_program_mod_ind;
108 new_references.special_catalog := x_special_catalog;
109 new_references.special_catalog_mod_ind := x_special_catalog_mod_ind;
110 new_references.enrolled_ind := x_enrolled_ind;
111 new_references.attribute_category := x_attribute_category;
112 new_references.attribute1 := x_attribute1;
113 new_references.attribute2 := x_attribute2;
114 new_references.attribute3 := x_attribute3;
115 new_references.attribute4 := x_attribute4;
116 new_references.attribute5 := x_attribute5;
117 new_references.attribute6 := x_attribute6;
118 new_references.attribute7 := x_attribute7;
119 new_references.attribute8 := x_attribute8;
120 new_references.attribute9 := x_attribute9;
121 new_references.attribute10 := x_attribute10;
122 new_references.attribute11 := x_attribute11;
123 new_references.attribute12 := x_attribute12;
124 new_references.attribute13 := x_attribute13;
125 new_references.attribute14 := x_attribute14;
126 new_references.attribute15 := x_attribute15;
127 new_references.attribute16 := x_attribute16;
128 new_references.attribute17 := x_attribute17;
129 new_references.attribute18 := x_attribute18;
130 new_references.attribute19 := x_attribute19;
131 new_references.attribute20 := x_attribute20;
132 new_references.spa_complete_ind := x_spa_complete_ind;
133 new_references.susa_complete_ind := x_susa_complete_ind;
134
135 IF (p_action = 'UPDATE') THEN
136 new_references.creation_date := old_references.creation_date;
137 new_references.created_by := old_references.created_by;
138 ELSE
139 new_references.creation_date := x_creation_date;
140 new_references.created_by := x_created_by;
141 END IF;
142
143 new_references.last_update_date := x_last_update_date;
144 new_references.last_updated_by := x_last_updated_by;
145 new_references.last_update_login := x_last_update_login;
146
147 END set_column_values;
148
149
150
151 FUNCTION get_uk_for_validation (
152 x_request_name IN VARCHAR2,
153 x_request_type IN VARCHAR2,
154 x_request_mode IN VARCHAR2
155 ) RETURN BOOLEAN AS
156 /*
157 || Created By : ddey
158 || Created On : 04-Apr-2003
159 || Purpose : Validates the Unique Keys of the table.
160 || Known limitations, enhancements or remarks :
161 || Change History :
162 || Who When What
163 || (reverse chronological order - newest change first)
164 */
165 CURSOR cur_rowid IS
166 SELECT rowid
167 FROM igs_da_cnfg_req_typ
168 WHERE request_name = x_request_name
169 AND request_type = x_request_type
170 AND request_mode = x_request_mode
171 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
172
173 lv_rowid cur_rowid%RowType;
174
175 BEGIN
176
177 OPEN cur_rowid;
178 FETCH cur_rowid INTO lv_rowid;
179 IF (cur_rowid%FOUND) THEN
180 CLOSE cur_rowid;
181 RETURN (true);
182 ELSE
183 CLOSE cur_rowid;
184 RETURN(FALSE);
185 END IF;
186
187 END get_uk_for_validation ;
188
189
190 PROCEDURE check_uniqueness AS
191 /*
192 || Created By : ddey
193 || Created On : 04-Apr-2003
194 || Purpose : Handles the Unique Constraint logic defined for the columns.
195 || Known limitations, enhancements or remarks :
196 || Change History :
197 || Who When What
198 || (reverse chronological order - newest change first)
199 */
200 BEGIN
201
202 IF ( get_uk_for_validation (
203 new_references.request_name ,
204 new_references.request_type ,
205 new_references.request_mode
206 )
207 ) THEN
208 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
209 igs_ge_msg_stack.add;
210 app_exception.raise_exception;
211 END IF;
212
213 END check_uniqueness;
214
215 PROCEDURE check_parent_existance AS
216 /*
217 || Created By :
218 || Created On : 19-MAR-2003
219 || Purpose : Checks for the existance of Parent records.
220 || Known limitations, enhancements or remarks :
221 || Change History :
222 || Who When What
223 || (reverse chronological order - newest change first)
224 */
225 BEGIN
226
227 IF (((old_references.wif_program_code = new_references.wif_program_code)) OR
228 ((new_references.wif_program_code IS NULL))) THEN
229 NULL;
230 ELSIF NOT igs_ps_course_pkg.get_pk_for_validation (
231 new_references.wif_program_code
232 ) THEN
233 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
234 igs_ge_msg_stack.add;
235 app_exception.raise_exception;
236 END IF;
237
238 IF (((old_references.wif_catalog_cal_type = new_references.wif_catalog_cal_type) AND
239 (old_references.wif_catalog_ci_seq_num = new_references.wif_catalog_ci_seq_num)) OR
240 ((new_references.wif_catalog_cal_type IS NULL) OR
241 (new_references.wif_catalog_ci_seq_num IS NULL))) THEN
242 NULL;
243 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
244 new_references.wif_catalog_cal_type,
245 new_references.wif_catalog_ci_seq_num
246 ) THEN
247 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
248 igs_ge_msg_stack.add;
249 app_exception.raise_exception;
250 END IF;
251
252 END check_parent_existance;
253
254
255 PROCEDURE check_child_existance AS
256 /*
257 || Created By :
258 || Created On : 19-MAR-2003
259 || Purpose : Checks for the existance of Child records.
260 || Known limitations, enhancements or remarks :
261 || Change History :
262 || Who When What
263 || (reverse chronological order - newest change first)
264 */
265 BEGIN
266
267 igs_da_cnfg_pkg.get_fk_igs_da_cnfg_req_typ (
268 old_references.request_type_id
269 );
270
271 igs_da_cnfg_ftr_pkg.get_fk_igs_da_cnfg_req_typ (
272 old_references.request_type_id
273 );
274
275 igs_da_cnfg_stat_pkg.get_fk_igs_da_cnfg_req_typ (
276 old_references.request_type_id
277 );
278
279 igs_da_rqst_pkg.get_fk_igs_da_cnfg_req_typ (
280 old_references.request_type_id );
281
282
283 END check_child_existance;
284
285
286 FUNCTION get_pk_for_validation (
287 x_request_type_id IN NUMBER
288 ) RETURN BOOLEAN AS
289 /*
290 || Created By :
291 || Created On : 19-MAR-2003
292 || Purpose : Validates the Primary Key of the table.
293 || Known limitations, enhancements or remarks :
294 || Change History :
295 || Who When What
296 || (reverse chronological order - newest change first)
297 */
298 CURSOR cur_rowid IS
299 SELECT rowid
300 FROM igs_da_cnfg_req_typ
301 WHERE request_type_id = x_request_type_id
302 FOR UPDATE NOWAIT;
303
304 lv_rowid cur_rowid%RowType;
305
306 BEGIN
307
308 OPEN cur_rowid;
309 FETCH cur_rowid INTO lv_rowid;
310 IF (cur_rowid%FOUND) THEN
311 CLOSE cur_rowid;
312 RETURN(TRUE);
313 ELSE
314 CLOSE cur_rowid;
315 RETURN(FALSE);
316 END IF;
317
318 END get_pk_for_validation;
319
320
321 PROCEDURE get_fk_igs_ps_course (
322 x_course_cd IN VARCHAR2
323 ) AS
324 /*
325 || Created By :
326 || Created On : 19-MAR-2003
327 || Purpose : Validates the Foreign Keys for the table.
328 || Known limitations, enhancements or remarks :
329 || Change History :
330 || Who When What
331 || (reverse chronological order - newest change first)
332 */
333 CURSOR cur_rowid IS
334 SELECT rowid
335 FROM igs_da_cnfg_req_typ
336 WHERE ((wif_program_code = x_course_cd));
337
338 lv_rowid cur_rowid%RowType;
339
340 BEGIN
341
342 OPEN cur_rowid;
343 FETCH cur_rowid INTO lv_rowid;
344 IF (cur_rowid%FOUND) THEN
345 CLOSE cur_rowid;
346 fnd_message.set_name ('IGS', 'IGS_DA_CNFG_PSC_FK');
347 igs_ge_msg_stack.add;
348 app_exception.raise_exception;
349 RETURN;
350 END IF;
351 CLOSE cur_rowid;
352
353 END get_fk_igs_ps_course;
354
355
356 PROCEDURE get_fk_igs_ca_inst (
357 x_cal_type IN VARCHAR2,
358 x_sequence_number IN NUMBER
359 ) AS
360 /*
361 || Created By :
362 || Created On : 19-MAR-2003
363 || Purpose : Validates the Foreign Keys for the table.
364 || Known limitations, enhancements or remarks :
365 || Change History :
366 || Who When What
367 || (reverse chronological order - newest change first)
368 */
369 CURSOR cur_rowid IS
370 SELECT rowid
371 FROM igs_da_cnfg_req_typ
372 WHERE ((wif_catalog_cal_type = x_cal_type) AND
373 (wif_catalog_ci_seq_num = x_sequence_number));
374
375 lv_rowid cur_rowid%RowType;
376
377 BEGIN
378
379 OPEN cur_rowid;
380 FETCH cur_rowid INTO lv_rowid;
381 IF (cur_rowid%FOUND) THEN
382 CLOSE cur_rowid;
383 fnd_message.set_name ('IGS', 'IGS_DA_CNFG_CAI_FK');
384 igs_ge_msg_stack.add;
385 app_exception.raise_exception;
386 RETURN;
387 END IF;
388 CLOSE cur_rowid;
389
390 END get_fk_igs_ca_inst;
391
392
393 PROCEDURE before_dml (
394 p_action IN VARCHAR2,
395 x_rowid IN VARCHAR2,
396 x_request_type_id IN NUMBER,
397 x_request_name IN VARCHAR2,
398 x_request_type IN VARCHAR2,
399 x_request_mode IN VARCHAR2,
400 x_closed_ind IN VARCHAR2,
401 x_purgable_ind IN VARCHAR2,
402 x_request_type_comment IN VARCHAR2,
403 x_wif_ind IN VARCHAR2,
404 x_wif_program_code IN VARCHAR2,
405 x_wif_program_mod_ind IN VARCHAR2,
406 x_wif_catalog_cal_type IN VARCHAR2,
407 x_wif_catalog_ci_seq_num IN NUMBER,
408 x_wif_catalog_mod_ind IN VARCHAR2,
409 x_special_ind IN VARCHAR2,
410 x_special_program_code IN VARCHAR2,
411 x_special_program_mod_ind IN VARCHAR2,
412 x_special_catalog IN VARCHAR2,
413 x_special_catalog_mod_ind IN VARCHAR2,
414 x_enrolled_ind IN VARCHAR2,
415 x_attribute_category IN VARCHAR2,
416 x_attribute1 IN VARCHAR2,
417 x_attribute2 IN VARCHAR2,
418 x_attribute3 IN VARCHAR2,
419 x_attribute4 IN VARCHAR2,
420 x_attribute5 IN VARCHAR2,
421 x_attribute6 IN VARCHAR2,
422 x_attribute7 IN VARCHAR2,
423 x_attribute8 IN VARCHAR2,
424 x_attribute9 IN VARCHAR2,
425 x_attribute10 IN VARCHAR2,
426 x_attribute11 IN VARCHAR2,
427 x_attribute12 IN VARCHAR2,
428 x_attribute13 IN VARCHAR2,
429 x_attribute14 IN VARCHAR2,
430 x_attribute15 IN VARCHAR2,
431 x_attribute16 IN VARCHAR2,
432 x_attribute17 IN VARCHAR2,
433 x_attribute18 IN VARCHAR2,
434 x_attribute19 IN VARCHAR2,
435 x_attribute20 IN VARCHAR2,
436 x_spa_complete_ind IN VARCHAR2,
437 x_susa_complete_ind IN VARCHAR2,
438 x_creation_date IN DATE,
439 x_created_by IN NUMBER,
440 x_last_update_date IN DATE,
441 x_last_updated_by IN NUMBER,
442 x_last_update_login IN NUMBER
443 ) AS
444 /*
445 || Created By :
446 || Created On : 19-MAR-2003
447 || Purpose : Initialises the columns, Checks Constraints, Calls the
448 || Trigger Handlers for the table, before any DML operation.
449 || Known limitations, enhancements or remarks :
450 || Change History :
451 || Who When What
452 || (reverse chronological order - newest change first)
453 */
454 BEGIN
455
456 set_column_values (
457 p_action,
458 x_rowid,
459 x_request_type_id,
460 x_request_name,
461 x_request_type,
462 x_request_mode,
463 x_closed_ind,
464 x_purgable_ind,
465 x_request_type_comment,
466 x_wif_ind,
467 x_wif_program_code,
468 x_wif_program_mod_ind,
469 x_wif_catalog_cal_type,
470 x_wif_catalog_ci_seq_num,
471 x_wif_catalog_mod_ind,
472 x_special_ind,
473 x_special_program_code,
474 x_special_program_mod_ind,
475 x_special_catalog,
476 x_special_catalog_mod_ind,
477 x_enrolled_ind,
478 x_attribute_category,
479 x_attribute1,
480 x_attribute2,
481 x_attribute3,
482 x_attribute4,
483 x_attribute5,
484 x_attribute6,
485 x_attribute7,
486 x_attribute8,
487 x_attribute9,
488 x_attribute10,
489 x_attribute11,
490 x_attribute12,
491 x_attribute13,
492 x_attribute14,
493 x_attribute15,
494 x_attribute16,
495 x_attribute17,
496 x_attribute18,
497 x_attribute19,
498 x_attribute20,
499 x_spa_complete_ind,
500 x_susa_complete_ind,
501 x_creation_date,
502 x_created_by,
503 x_last_update_date,
504 x_last_updated_by,
505 x_last_update_login
506 );
507
508 IF (p_action = 'INSERT') THEN
509 -- Call all the procedures related to Before Insert.
510 IF ( get_pk_for_validation(
511 new_references.request_type_id
512 )
513 ) THEN
514 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
515 igs_ge_msg_stack.add;
516 app_exception.raise_exception;
517 END IF;
518 check_uniqueness;
519 check_parent_existance;
520 ELSIF (p_action = 'UPDATE') THEN
521 -- Call all the procedures related to Before Update.
522 check_uniqueness;
523 check_parent_existance;
524 ELSIF (p_action = 'DELETE') THEN
525 -- Call all the procedures related to Before Delete.
526 check_child_existance;
527 ELSIF (p_action = 'VALIDATE_INSERT') THEN
528 -- Call all the procedures related to Before Insert.
529 IF ( get_pk_for_validation (
530 new_references.request_type_id
531 )
532 ) THEN
533 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
534 igs_ge_msg_stack.add;
535 app_exception.raise_exception;
536 END IF;
537 check_uniqueness;
538 ELSIF (p_action = 'VALIDATE_DELETE') THEN
539 check_child_existance;
540 END IF;
541
542 END before_dml;
543
544
545 PROCEDURE insert_row (
546 x_rowid IN OUT NOCOPY VARCHAR2,
547 x_request_type_id IN OUT NOCOPY NUMBER,
548 x_request_name IN VARCHAR2,
549 x_request_type IN VARCHAR2,
550 x_request_mode IN VARCHAR2,
551 x_closed_ind IN VARCHAR2,
552 x_purgable_ind IN VARCHAR2,
553 x_request_type_comment IN VARCHAR2,
554 x_wif_ind IN VARCHAR2,
555 x_wif_program_code IN VARCHAR2,
556 x_wif_program_mod_ind IN VARCHAR2,
557 x_wif_catalog_cal_type IN VARCHAR2,
558 x_wif_catalog_ci_seq_num IN NUMBER,
559 x_wif_catalog_mod_ind IN VARCHAR2,
560 x_special_ind IN VARCHAR2,
561 x_special_program_code IN VARCHAR2,
562 x_special_program_mod_ind IN VARCHAR2,
563 x_special_catalog IN VARCHAR2,
564 x_special_catalog_mod_ind IN VARCHAR2,
565 x_enrolled_ind IN VARCHAR2,
566 x_attribute_category IN VARCHAR2,
567 x_attribute1 IN VARCHAR2,
568 x_attribute2 IN VARCHAR2,
569 x_attribute3 IN VARCHAR2,
570 x_attribute4 IN VARCHAR2,
571 x_attribute5 IN VARCHAR2,
572 x_attribute6 IN VARCHAR2,
573 x_attribute7 IN VARCHAR2,
574 x_attribute8 IN VARCHAR2,
575 x_attribute9 IN VARCHAR2,
576 x_attribute10 IN VARCHAR2,
577 x_attribute11 IN VARCHAR2,
578 x_attribute12 IN VARCHAR2,
579 x_attribute13 IN VARCHAR2,
580 x_attribute14 IN VARCHAR2,
581 x_attribute15 IN VARCHAR2,
582 x_attribute16 IN VARCHAR2,
583 x_attribute17 IN VARCHAR2,
584 x_attribute18 IN VARCHAR2,
585 x_attribute19 IN VARCHAR2,
586 x_attribute20 IN VARCHAR2,
587 x_spa_complete_ind IN VARCHAR2,
588 x_susa_complete_ind IN VARCHAR2,
589 x_mode IN VARCHAR2
590 ) AS
591 /*
592 || Created By :
593 || Created On : 19-MAR-2003
594 || Purpose : Handles the INSERT DML logic for the table.
595 || Known limitations, enhancements or remarks :
596 || Change History :
597 || Who When What
598 || (reverse chronological order - newest change first)
599 */
600
601 x_last_update_date DATE;
602 x_last_updated_by NUMBER;
603 x_last_update_login NUMBER;
604
605 BEGIN
606
607 x_last_update_date := SYSDATE;
608 IF (x_mode = 'I') THEN
609 x_last_updated_by := 1;
610 x_last_update_login := 0;
611 ELSIF (x_mode = 'R') THEN
612 x_last_updated_by := fnd_global.user_id;
613 IF (x_last_updated_by IS NULL) THEN
614 x_last_updated_by := -1;
615 END IF;
616 x_last_update_login := fnd_global.login_id;
617 IF (x_last_update_login IS NULL) THEN
618 x_last_update_login := -1;
619 END IF;
620 ELSE
621 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
622 fnd_message.set_token ('ROUTINE', 'IGS_DA_CNFG_REQ_TYP_PKG.INSERT_ROW');
623 igs_ge_msg_stack.add;
624 app_exception.raise_exception;
625 END IF;
626
627 -- x_request_type_id := NULL; -- Commented by Deep. Need to be verified
628
629 before_dml(
630 p_action => 'INSERT',
631 x_rowid => x_rowid,
632 x_request_type_id => x_request_type_id,
633 x_request_name => x_request_name,
634 x_request_type => x_request_type,
635 x_request_mode => x_request_mode,
636 x_closed_ind => x_closed_ind,
637 x_purgable_ind => x_purgable_ind,
638 x_request_type_comment => x_request_type_comment,
639 x_wif_ind => x_wif_ind,
640 x_wif_program_code => x_wif_program_code,
641 x_wif_program_mod_ind => x_wif_program_mod_ind,
642 x_wif_catalog_cal_type => x_wif_catalog_cal_type,
643 x_wif_catalog_ci_seq_num => x_wif_catalog_ci_seq_num,
644 x_wif_catalog_mod_ind => x_wif_catalog_mod_ind,
645 x_special_ind => x_special_ind,
646 x_special_program_code => x_special_program_code,
647 x_special_program_mod_ind => x_special_program_mod_ind,
648 x_special_catalog => x_special_catalog,
649 x_special_catalog_mod_ind => x_special_catalog_mod_ind,
650 x_enrolled_ind => x_enrolled_ind,
651 x_attribute_category => x_attribute_category,
652 x_attribute1 => x_attribute1,
653 x_attribute2 => x_attribute2,
654 x_attribute3 => x_attribute3,
655 x_attribute4 => x_attribute4,
656 x_attribute5 => x_attribute5,
657 x_attribute6 => x_attribute6,
658 x_attribute7 => x_attribute7,
659 x_attribute8 => x_attribute8,
660 x_attribute9 => x_attribute9,
661 x_attribute10 => x_attribute10,
662 x_attribute11 => x_attribute11,
663 x_attribute12 => x_attribute12,
664 x_attribute13 => x_attribute13,
665 x_attribute14 => x_attribute14,
666 x_attribute15 => x_attribute15,
667 x_attribute16 => x_attribute16,
668 x_attribute17 => x_attribute17,
669 x_attribute18 => x_attribute18,
670 x_attribute19 => x_attribute19,
671 x_attribute20 => x_attribute20,
672 x_spa_complete_ind => x_spa_complete_ind,
673 x_susa_complete_ind => x_susa_complete_ind,
674 x_creation_date => x_last_update_date,
675 x_created_by => x_last_updated_by,
676 x_last_update_date => x_last_update_date,
677 x_last_updated_by => x_last_updated_by,
678 x_last_update_login => x_last_update_login
679 );
680
681 INSERT INTO igs_da_cnfg_req_typ (
682 request_type_id,
683 request_name,
684 request_type,
685 request_mode,
686 closed_ind,
687 purgable_ind,
688 request_type_comment,
689 wif_ind,
690 wif_program_code,
691 wif_program_mod_ind,
692 wif_catalog_cal_type,
693 wif_catalog_ci_seq_num,
694 wif_catalog_mod_ind,
695 special_ind,
696 special_program_code,
697 special_program_mod_ind,
698 special_catalog,
699 special_catalog_mod_ind,
700 enrolled_ind,
701 attribute_category,
702 attribute1,
703 attribute2,
704 attribute3,
705 attribute4,
706 attribute5,
707 attribute6,
708 attribute7,
709 attribute8,
710 attribute9,
711 attribute10,
712 attribute11,
713 attribute12,
714 attribute13,
715 attribute14,
716 attribute15,
717 attribute16,
718 attribute17,
719 attribute18,
720 attribute19,
721 attribute20,
722 spa_complete_ind,
723 susa_complete_ind,
724 creation_date,
725 created_by,
726 last_update_date,
727 last_updated_by,
728 last_update_login
729 ) VALUES (
730 igs_da_cnfg_req_typ_s.NEXTVAL,
731 new_references.request_name,
732 new_references.request_type,
733 new_references.request_mode,
734 new_references.closed_ind,
735 new_references.purgable_ind,
736 new_references.request_type_comment,
737 new_references.wif_ind,
738 new_references.wif_program_code,
739 new_references.wif_program_mod_ind,
740 new_references.wif_catalog_cal_type,
741 new_references.wif_catalog_ci_seq_num,
742 new_references.wif_catalog_mod_ind,
743 new_references.special_ind,
744 new_references.special_program_code,
745 new_references.special_program_mod_ind,
746 new_references.special_catalog,
747 new_references.special_catalog_mod_ind,
748 new_references.enrolled_ind,
749 new_references.attribute_category,
750 new_references.attribute1,
751 new_references.attribute2,
752 new_references.attribute3,
753 new_references.attribute4,
754 new_references.attribute5,
755 new_references.attribute6,
756 new_references.attribute7,
757 new_references.attribute8,
758 new_references.attribute9,
759 new_references.attribute10,
760 new_references.attribute11,
761 new_references.attribute12,
762 new_references.attribute13,
763 new_references.attribute14,
764 new_references.attribute15,
765 new_references.attribute16,
766 new_references.attribute17,
767 new_references.attribute18,
768 new_references.attribute19,
769 new_references.attribute20,
770 new_references.spa_complete_ind,
771 new_references.susa_complete_ind,
772 x_last_update_date,
773 x_last_updated_by,
774 x_last_update_date,
775 x_last_updated_by,
776 x_last_update_login
777 ) RETURNING ROWID, request_type_id INTO x_rowid, x_request_type_id;
778
779 END insert_row;
780
781
782 PROCEDURE lock_row (
783 x_rowid IN VARCHAR2,
784 x_request_type_id IN NUMBER,
785 x_request_name IN VARCHAR2,
786 x_request_type IN VARCHAR2,
787 x_request_mode IN VARCHAR2,
788 x_closed_ind IN VARCHAR2,
789 x_purgable_ind IN VARCHAR2,
790 x_request_type_comment IN VARCHAR2,
791 x_wif_ind IN VARCHAR2,
792 x_wif_program_code IN VARCHAR2,
793 x_wif_program_mod_ind IN VARCHAR2,
794 x_wif_catalog_cal_type IN VARCHAR2,
795 x_wif_catalog_ci_seq_num IN NUMBER,
796 x_wif_catalog_mod_ind IN VARCHAR2,
797 x_special_ind IN VARCHAR2,
798 x_special_program_code IN VARCHAR2,
799 x_special_program_mod_ind IN VARCHAR2,
800 x_special_catalog IN VARCHAR2,
801 x_special_catalog_mod_ind IN VARCHAR2,
802 x_enrolled_ind IN VARCHAR2,
803 x_attribute_category IN VARCHAR2,
804 x_attribute1 IN VARCHAR2,
805 x_attribute2 IN VARCHAR2,
806 x_attribute3 IN VARCHAR2,
807 x_attribute4 IN VARCHAR2,
808 x_attribute5 IN VARCHAR2,
809 x_attribute6 IN VARCHAR2,
810 x_attribute7 IN VARCHAR2,
811 x_attribute8 IN VARCHAR2,
812 x_attribute9 IN VARCHAR2,
813 x_attribute10 IN VARCHAR2,
814 x_attribute11 IN VARCHAR2,
815 x_attribute12 IN VARCHAR2,
816 x_attribute13 IN VARCHAR2,
817 x_attribute14 IN VARCHAR2,
818 x_attribute15 IN VARCHAR2,
819 x_attribute16 IN VARCHAR2,
820 x_attribute17 IN VARCHAR2,
821 x_attribute18 IN VARCHAR2,
822 x_attribute19 IN VARCHAR2,
823 x_attribute20 IN VARCHAR2,
824 x_spa_complete_ind IN VARCHAR2,
825 x_susa_complete_ind IN VARCHAR2
826 ) AS
827 /*
828 || Created By :
829 || Created On : 19-MAR-2003
830 || Purpose : Handles the LOCK mechanism for the table.
831 || Known limitations, enhancements or remarks :
832 || Change History :
833 || Who When What
834 || (reverse chronological order - newest change first)
835 */
836 CURSOR c1 IS
837 SELECT
838 request_name,
839 request_type,
840 request_mode,
841 closed_ind,
842 purgable_ind,
843 request_type_comment,
844 wif_ind,
845 wif_program_code,
846 wif_program_mod_ind,
847 wif_catalog_cal_type,
848 wif_catalog_ci_seq_num,
849 wif_catalog_mod_ind,
850 special_ind,
851 special_program_code,
852 special_program_mod_ind,
853 special_catalog,
854 special_catalog_mod_ind,
855 enrolled_ind,
856 attribute_category,
857 attribute1,
858 attribute2,
859 attribute3,
860 attribute4,
861 attribute5,
862 attribute6,
863 attribute7,
864 attribute8,
865 attribute9,
866 attribute10,
867 attribute11,
868 attribute12,
869 attribute13,
870 attribute14,
871 attribute15,
872 attribute16,
873 attribute17,
874 attribute18,
875 attribute19,
876 attribute20,
877 spa_complete_ind,
878 susa_complete_ind
879 FROM igs_da_cnfg_req_typ
880 WHERE rowid = x_rowid
881 FOR UPDATE NOWAIT;
882
883 tlinfo c1%ROWTYPE;
884
885 BEGIN
886
887 OPEN c1;
888 FETCH c1 INTO tlinfo;
889 IF (c1%notfound) THEN
890 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
891 igs_ge_msg_stack.add;
892 CLOSE c1;
893 app_exception.raise_exception;
894 RETURN;
895 END IF;
896 CLOSE c1;
897
898 IF (
899 (tlinfo.request_name = x_request_name)
900 AND (tlinfo.request_type = x_request_type)
901 AND (tlinfo.request_mode = x_request_mode)
902 AND (tlinfo.closed_ind = x_closed_ind)
903 AND (tlinfo.purgable_ind = x_purgable_ind)
904 AND ((tlinfo.request_type_comment = x_request_type_comment) OR ((tlinfo.request_type_comment IS NULL) AND (X_request_type_comment IS NULL)))
905 AND (tlinfo.wif_ind = x_wif_ind)
906 AND ((tlinfo.wif_program_code = x_wif_program_code) OR ((tlinfo.wif_program_code IS NULL) AND (X_wif_program_code IS NULL)))
907 AND (tlinfo.wif_program_mod_ind = x_wif_program_mod_ind)
908 AND ((tlinfo.wif_catalog_cal_type = x_wif_catalog_cal_type) OR ((tlinfo.wif_catalog_cal_type IS NULL) AND (X_wif_catalog_cal_type IS NULL)))
909 AND ((tlinfo.wif_catalog_ci_seq_num = x_wif_catalog_ci_seq_num) OR ((tlinfo.wif_catalog_ci_seq_num IS NULL) AND (X_wif_catalog_ci_seq_num IS NULL)))
910 AND (tlinfo.wif_catalog_mod_ind = x_wif_catalog_mod_ind)
911 AND (tlinfo.special_ind = x_special_ind)
912 AND ((tlinfo.special_program_code = x_special_program_code) OR ((tlinfo.special_program_code IS NULL) AND (X_special_program_code IS NULL)))
913 AND (tlinfo.special_program_mod_ind = x_special_program_mod_ind)
914 AND ((tlinfo.special_catalog = x_special_catalog) OR ((tlinfo.special_catalog IS NULL) AND (X_special_catalog IS NULL)))
915 AND (tlinfo.special_catalog_mod_ind = x_special_catalog_mod_ind)
916 AND (tlinfo.enrolled_ind = x_enrolled_ind)
917 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
918 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
919 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
920 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
921 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
922 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
923 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
924 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
925 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
926 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
927 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
928 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
929 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
930 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
931 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
932 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
933 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
934 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
935 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
936 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
937 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
938 AND ((tlinfo.spa_complete_ind = x_spa_complete_ind) OR ((tlinfo.spa_complete_ind IS NULL) AND (X_spa_complete_ind IS NULL)))
939 AND ((tlinfo.susa_complete_ind = x_susa_complete_ind) OR ((tlinfo.susa_complete_ind IS NULL) AND (X_susa_complete_ind IS NULL)))
940 ) THEN
941 NULL;
942 ELSE
943 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
944 igs_ge_msg_stack.add;
945 app_exception.raise_exception;
946 END IF;
947
948 RETURN;
949
950 END lock_row;
951
952
953 PROCEDURE update_row (
954 x_rowid IN VARCHAR2,
955 x_request_type_id IN NUMBER,
956 x_request_name IN VARCHAR2,
957 x_request_type IN VARCHAR2,
958 x_request_mode IN VARCHAR2,
959 x_closed_ind IN VARCHAR2,
960 x_purgable_ind IN VARCHAR2,
961 x_request_type_comment IN VARCHAR2,
962 x_wif_ind IN VARCHAR2,
963 x_wif_program_code IN VARCHAR2,
964 x_wif_program_mod_ind IN VARCHAR2,
965 x_wif_catalog_cal_type IN VARCHAR2,
966 x_wif_catalog_ci_seq_num IN NUMBER,
967 x_wif_catalog_mod_ind IN VARCHAR2,
968 x_special_ind IN VARCHAR2,
969 x_special_program_code IN VARCHAR2,
970 x_special_program_mod_ind IN VARCHAR2,
971 x_special_catalog IN VARCHAR2,
972 x_special_catalog_mod_ind IN VARCHAR2,
973 x_enrolled_ind IN VARCHAR2,
974 x_attribute_category IN VARCHAR2,
975 x_attribute1 IN VARCHAR2,
976 x_attribute2 IN VARCHAR2,
977 x_attribute3 IN VARCHAR2,
978 x_attribute4 IN VARCHAR2,
979 x_attribute5 IN VARCHAR2,
980 x_attribute6 IN VARCHAR2,
981 x_attribute7 IN VARCHAR2,
982 x_attribute8 IN VARCHAR2,
983 x_attribute9 IN VARCHAR2,
984 x_attribute10 IN VARCHAR2,
985 x_attribute11 IN VARCHAR2,
986 x_attribute12 IN VARCHAR2,
987 x_attribute13 IN VARCHAR2,
988 x_attribute14 IN VARCHAR2,
989 x_attribute15 IN VARCHAR2,
990 x_attribute16 IN VARCHAR2,
991 x_attribute17 IN VARCHAR2,
992 x_attribute18 IN VARCHAR2,
993 x_attribute19 IN VARCHAR2,
994 x_attribute20 IN VARCHAR2,
995 x_spa_complete_ind IN VARCHAR2,
996 x_susa_complete_ind IN VARCHAR2,
997 x_mode IN VARCHAR2
998 ) AS
999 /*
1000 || Created By :
1001 || Created On : 19-MAR-2003
1002 || Purpose : Handles the UPDATE DML logic for the table.
1003 || Known limitations, enhancements or remarks :
1004 || Change History :
1005 || Who When What
1006 || (reverse chronological order - newest change first)
1007 */
1008 x_last_update_date DATE ;
1009 x_last_updated_by NUMBER;
1010 x_last_update_login NUMBER;
1011
1012 BEGIN
1013
1014 x_last_update_date := SYSDATE;
1015 IF (X_MODE = 'I') THEN
1016 x_last_updated_by := 1;
1017 x_last_update_login := 0;
1018 ELSIF (x_mode = 'R') THEN
1019 x_last_updated_by := fnd_global.user_id;
1020 IF x_last_updated_by IS NULL THEN
1021 x_last_updated_by := -1;
1022 END IF;
1023 x_last_update_login := fnd_global.login_id;
1024 IF (x_last_update_login IS NULL) THEN
1025 x_last_update_login := -1;
1026 END IF;
1027 ELSE
1028 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1029 fnd_message.set_token ('ROUTINE', 'IGS_DA_CNFG_REQ_TYP_PKG.UPDATE_ROW');
1030 igs_ge_msg_stack.add;
1031 app_exception.raise_exception;
1032 END IF;
1033
1034 -- x_request_type_id := NULL; -- Commented by Deep. Need to be verified
1035
1036 before_dml(
1037 p_action => 'UPDATE',
1038 x_rowid => x_rowid,
1039 x_request_type_id => x_request_type_id,
1040 x_request_name => x_request_name,
1041 x_request_type => x_request_type,
1042 x_request_mode => x_request_mode,
1043 x_closed_ind => x_closed_ind,
1044 x_purgable_ind => x_purgable_ind,
1045 x_request_type_comment => x_request_type_comment,
1046 x_wif_ind => x_wif_ind,
1047 x_wif_program_code => x_wif_program_code,
1048 x_wif_program_mod_ind => x_wif_program_mod_ind,
1049 x_wif_catalog_cal_type => x_wif_catalog_cal_type,
1050 x_wif_catalog_ci_seq_num => x_wif_catalog_ci_seq_num,
1051 x_wif_catalog_mod_ind => x_wif_catalog_mod_ind,
1052 x_special_ind => x_special_ind,
1053 x_special_program_code => x_special_program_code,
1054 x_special_program_mod_ind => x_special_program_mod_ind,
1055 x_special_catalog => x_special_catalog,
1056 x_special_catalog_mod_ind => x_special_catalog_mod_ind,
1057 x_enrolled_ind => x_enrolled_ind,
1058 x_attribute_category => x_attribute_category,
1059 x_attribute1 => x_attribute1,
1060 x_attribute2 => x_attribute2,
1061 x_attribute3 => x_attribute3,
1062 x_attribute4 => x_attribute4,
1063 x_attribute5 => x_attribute5,
1064 x_attribute6 => x_attribute6,
1065 x_attribute7 => x_attribute7,
1066 x_attribute8 => x_attribute8,
1067 x_attribute9 => x_attribute9,
1068 x_attribute10 => x_attribute10,
1069 x_attribute11 => x_attribute11,
1070 x_attribute12 => x_attribute12,
1071 x_attribute13 => x_attribute13,
1072 x_attribute14 => x_attribute14,
1073 x_attribute15 => x_attribute15,
1074 x_attribute16 => x_attribute16,
1075 x_attribute17 => x_attribute17,
1076 x_attribute18 => x_attribute18,
1077 x_attribute19 => x_attribute19,
1078 x_attribute20 => x_attribute20,
1079 x_spa_complete_ind => x_spa_complete_ind,
1080 x_susa_complete_ind => x_susa_complete_ind,
1081 x_creation_date => x_last_update_date,
1082 x_created_by => x_last_updated_by,
1083 x_last_update_date => x_last_update_date,
1084 x_last_updated_by => x_last_updated_by,
1085 x_last_update_login => x_last_update_login
1086 );
1087
1088 UPDATE igs_da_cnfg_req_typ
1089 SET
1090 request_name = new_references.request_name,
1091 request_type = new_references.request_type,
1092 request_mode = new_references.request_mode,
1093 closed_ind = new_references.closed_ind,
1094 purgable_ind = new_references.purgable_ind,
1095 request_type_comment = new_references.request_type_comment,
1096 wif_ind = new_references.wif_ind,
1097 wif_program_code = new_references.wif_program_code,
1098 wif_program_mod_ind = new_references.wif_program_mod_ind,
1099 wif_catalog_cal_type = new_references.wif_catalog_cal_type,
1100 wif_catalog_ci_seq_num = new_references.wif_catalog_ci_seq_num,
1101 wif_catalog_mod_ind = new_references.wif_catalog_mod_ind,
1102 special_ind = new_references.special_ind,
1103 special_program_code = new_references.special_program_code,
1104 special_program_mod_ind = new_references.special_program_mod_ind,
1105 special_catalog = new_references.special_catalog,
1106 special_catalog_mod_ind = new_references.special_catalog_mod_ind,
1107 enrolled_ind = new_references.enrolled_ind,
1108 attribute_category = new_references.attribute_category,
1109 attribute1 = new_references.attribute1,
1110 attribute2 = new_references.attribute2,
1111 attribute3 = new_references.attribute3,
1112 attribute4 = new_references.attribute4,
1113 attribute5 = new_references.attribute5,
1114 attribute6 = new_references.attribute6,
1115 attribute7 = new_references.attribute7,
1116 attribute8 = new_references.attribute8,
1117 attribute9 = new_references.attribute9,
1118 attribute10 = new_references.attribute10,
1119 attribute11 = new_references.attribute11,
1120 attribute12 = new_references.attribute12,
1121 attribute13 = new_references.attribute13,
1122 attribute14 = new_references.attribute14,
1123 attribute15 = new_references.attribute15,
1124 attribute16 = new_references.attribute16,
1125 attribute17 = new_references.attribute17,
1126 attribute18 = new_references.attribute18,
1127 attribute19 = new_references.attribute19,
1128 attribute20 = new_references.attribute20,
1129 spa_complete_ind = new_references.spa_complete_ind,
1130 susa_complete_ind = new_references.susa_complete_ind,
1131 last_update_date = x_last_update_date,
1132 last_updated_by = x_last_updated_by,
1133 last_update_login = x_last_update_login
1134 WHERE rowid = x_rowid;
1135
1136 IF (SQL%NOTFOUND) THEN
1137 RAISE NO_DATA_FOUND;
1138 END IF;
1139
1140 END update_row;
1141
1142
1143 PROCEDURE add_row (
1144 x_rowid IN OUT NOCOPY VARCHAR2,
1145 x_request_type_id IN OUT NOCOPY NUMBER,
1146 x_request_name IN VARCHAR2,
1147 x_request_type IN VARCHAR2,
1148 x_request_mode IN VARCHAR2,
1149 x_closed_ind IN VARCHAR2,
1150 x_purgable_ind IN VARCHAR2,
1151 x_request_type_comment IN VARCHAR2,
1152 x_wif_ind IN VARCHAR2,
1153 x_wif_program_code IN VARCHAR2,
1154 x_wif_program_mod_ind IN VARCHAR2,
1155 x_wif_catalog_cal_type IN VARCHAR2,
1156 x_wif_catalog_ci_seq_num IN NUMBER,
1157 x_wif_catalog_mod_ind IN VARCHAR2,
1158 x_special_ind IN VARCHAR2,
1159 x_special_program_code IN VARCHAR2,
1160 x_special_program_mod_ind IN VARCHAR2,
1161 x_special_catalog IN VARCHAR2,
1162 x_special_catalog_mod_ind IN VARCHAR2,
1163 x_enrolled_ind IN VARCHAR2,
1164 x_attribute_category IN VARCHAR2,
1165 x_attribute1 IN VARCHAR2,
1166 x_attribute2 IN VARCHAR2,
1167 x_attribute3 IN VARCHAR2,
1168 x_attribute4 IN VARCHAR2,
1169 x_attribute5 IN VARCHAR2,
1170 x_attribute6 IN VARCHAR2,
1171 x_attribute7 IN VARCHAR2,
1172 x_attribute8 IN VARCHAR2,
1173 x_attribute9 IN VARCHAR2,
1174 x_attribute10 IN VARCHAR2,
1175 x_attribute11 IN VARCHAR2,
1176 x_attribute12 IN VARCHAR2,
1177 x_attribute13 IN VARCHAR2,
1178 x_attribute14 IN VARCHAR2,
1179 x_attribute15 IN VARCHAR2,
1180 x_attribute16 IN VARCHAR2,
1181 x_attribute17 IN VARCHAR2,
1182 x_attribute18 IN VARCHAR2,
1183 x_attribute19 IN VARCHAR2,
1184 x_attribute20 IN VARCHAR2,
1185 x_spa_complete_ind IN VARCHAR2,
1186 x_susa_complete_ind IN VARCHAR2,
1187 x_mode IN VARCHAR2
1188 ) AS
1189 /*
1190 || Created By :
1191 || Created On : 19-MAR-2003
1192 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1193 || Known limitations, enhancements or remarks :
1194 || Change History :
1195 || Who When What
1196 || (reverse chronological order - newest change first)
1197 */
1198 CURSOR c1 IS
1199 SELECT rowid
1200 FROM igs_da_cnfg_req_typ
1201 WHERE request_type_id = x_request_type_id;
1202
1203 BEGIN
1204
1205 OPEN c1;
1206 FETCH c1 INTO x_rowid;
1207 IF (c1%NOTFOUND) THEN
1208 CLOSE c1;
1209
1210 insert_row (
1211 x_rowid,
1212 x_request_type_id,
1213 x_request_name,
1214 x_request_type,
1215 x_request_mode,
1216 x_closed_ind,
1217 x_purgable_ind,
1218 x_request_type_comment,
1219 x_wif_ind,
1220 x_wif_program_code,
1221 x_wif_program_mod_ind,
1222 x_wif_catalog_cal_type,
1223 x_wif_catalog_ci_seq_num,
1224 x_wif_catalog_mod_ind,
1225 x_special_ind,
1226 x_special_program_code,
1227 x_special_program_mod_ind,
1228 x_special_catalog,
1229 x_special_catalog_mod_ind,
1230 x_enrolled_ind,
1231 x_attribute_category,
1232 x_attribute1,
1233 x_attribute2,
1234 x_attribute3,
1235 x_attribute4,
1236 x_attribute5,
1237 x_attribute6,
1238 x_attribute7,
1239 x_attribute8,
1240 x_attribute9,
1241 x_attribute10,
1242 x_attribute11,
1243 x_attribute12,
1244 x_attribute13,
1245 x_attribute14,
1246 x_attribute15,
1247 x_attribute16,
1248 x_attribute17,
1249 x_attribute18,
1250 x_attribute19,
1251 x_attribute20,
1252 x_spa_complete_ind,
1253 x_susa_complete_ind,
1254 x_mode
1255 );
1256 RETURN;
1257 END IF;
1258 CLOSE c1;
1259
1260 update_row (
1261 x_rowid,
1262 x_request_type_id,
1263 x_request_name,
1264 x_request_type,
1265 x_request_mode,
1266 x_closed_ind,
1267 x_purgable_ind,
1268 x_request_type_comment,
1269 x_wif_ind,
1270 x_wif_program_code,
1271 x_wif_program_mod_ind,
1272 x_wif_catalog_cal_type,
1273 x_wif_catalog_ci_seq_num,
1274 x_wif_catalog_mod_ind,
1275 x_special_ind,
1276 x_special_program_code,
1277 x_special_program_mod_ind,
1278 x_special_catalog,
1279 x_special_catalog_mod_ind,
1280 x_enrolled_ind,
1281 x_attribute_category,
1282 x_attribute1,
1283 x_attribute2,
1284 x_attribute3,
1285 x_attribute4,
1286 x_attribute5,
1287 x_attribute6,
1288 x_attribute7,
1289 x_attribute8,
1290 x_attribute9,
1291 x_attribute10,
1292 x_attribute11,
1293 x_attribute12,
1294 x_attribute13,
1295 x_attribute14,
1296 x_attribute15,
1297 x_attribute16,
1298 x_attribute17,
1299 x_attribute18,
1300 x_attribute19,
1301 x_attribute20,
1302 x_spa_complete_ind,
1303 x_susa_complete_ind,
1304 x_mode
1305 );
1306
1307 END add_row;
1308
1309
1310 PROCEDURE delete_row (
1311 x_rowid IN VARCHAR2
1312 ) AS
1313 /*
1314 || Created By :
1315 || Created On : 19-MAR-2003
1316 || Purpose : Handles the DELETE DML logic for the table.
1317 || Known limitations, enhancements or remarks :
1318 || Change History :
1319 || Who When What
1320 || (reverse chronological order - newest change first)
1321 */
1322
1323 CURSOR cur_parent_table IS
1324 SELECT
1325 request_type_id
1326 FROM igs_da_cnfg_req_typ_v
1327 WHERE row_id = x_rowid;
1328
1329 CURSOR cur_igs_da_cnfg_ftr (cp_request_type_id igs_da_cnfg_ftr.request_type_id%TYPE) IS
1330 SELECT
1331 rowid
1332 FROM igs_da_cnfg_ftr
1333 WHERE request_type_id = cp_request_type_id;
1334
1335
1336 CURSOR cur_igs_da_cnfg_stat (cp_request_type_id igs_da_cnfg_ftr.request_type_id%TYPE) IS
1337 SELECT
1338 rowid
1339 FROM igs_da_cnfg_stat
1340 WHERE request_type_id = cp_request_type_id;
1341
1342 CURSOR cur_igs_da_cnfg (cp_request_type_id igs_da_cnfg_ftr.request_type_id%TYPE) IS
1343 SELECT
1344 rowid
1345 FROM igs_da_cnfg
1346 WHERE request_type_id = cp_request_type_id;
1347
1348
1349 l_cur_parent_table cur_parent_table%ROWTYPE;
1350 l_cur_igs_da_cnfg_ftr cur_igs_da_cnfg_ftr%ROWTYPE;
1351 l_cur_igs_da_cnfg_stat cur_igs_da_cnfg_stat%ROWTYPE;
1352 l_cur_igs_da_cnfg cur_igs_da_cnfg%ROWTYPE;
1353
1354
1355 BEGIN
1356
1357 -- The Functionality in the form requires a CASCADE DELETE , hence the child would be deleted once the parent
1358 -- record is deleted.
1359
1360 /* before_dml (
1361 p_action => 'DELETE',
1362 x_rowid => x_rowid
1363 );
1364 */
1365
1366 OPEN cur_parent_table;
1367 FETCH cur_parent_table INTO l_cur_parent_table;
1368 CLOSE cur_parent_table;
1369
1370
1371 FOR l_cur_igs_da_cnfg_ftr IN cur_igs_da_cnfg_ftr(l_cur_parent_table.request_type_id) LOOP
1372
1373
1374 igs_da_cnfg_ftr_pkg.delete_row (
1375 x_rowid => l_cur_igs_da_cnfg_ftr.rowid
1376 );
1377 END LOOP;
1378
1379
1380
1381 FOR l_cur_igs_da_cnfg_stat IN cur_igs_da_cnfg_stat(l_cur_parent_table.request_type_id) LOOP
1382
1383 igs_da_cnfg_stat_pkg.delete_row (
1384 x_rowid => l_cur_igs_da_cnfg_stat.rowid
1385 );
1386 END LOOP;
1387
1388
1389 FOR l_cur_igs_da_cnfg IN cur_igs_da_cnfg(l_cur_parent_table.request_type_id) LOOP
1390
1391
1392 igs_da_cnfg_pkg.delete_row (
1393 x_rowid => l_cur_igs_da_cnfg.rowid
1394 );
1395
1396 END LOOP;
1397
1398
1399 DELETE FROM igs_da_cnfg_req_typ
1400 WHERE rowid = x_rowid;
1401
1402
1403 IF (SQL%NOTFOUND) THEN
1404 RAISE NO_DATA_FOUND;
1405 END IF;
1406
1407
1408
1409 END delete_row;
1410
1411
1412 END igs_da_cnfg_req_typ_pkg;