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