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