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